yager-api/modules/v1/controllers/ReporteGlobalController.php

<?php

namespace v1\controllers;

use common\rest\AuthController;
use common\rest\JsonController;
use yii\db\Query;

class ReporteGlobalController extends JsonController {

  public function actionIndex() {
    $fechas = [
      ["2022-08-01 00:00:00", "2022-08,31 23:59:59"],
      ["2022-09-01 00:00:00", "2022-09,30 23:59:59"],
      ["2022-10-01 00:00:00", "2022-10,31 23:59:59"],
      ["2022-11-01 00:00:00", "2022-11,30 23:59:59"],
      ["2022-12-01 00:00:00", "2022-12,31 23:59:59"],
      ["2023-01-01 00:00:00", "2023-01,31 23:59:59"],
    ];
    
    $query = (new Query())
      ->select([
        "{{Usuario}}.nombre as nombre",
        "{{Usuario}}.verificado as verificado",
        "{{Usuario}}.facebook as facebook",
        "{{Usuario}}.facebookVerificado as facebookVerificado",
        "{{Usuario}}.twitter as twitter",
        "{{Usuario}}.twitterVerificado as twitterVerificado",
        "{{Usuario}}.instagram as instagram",
        "{{Usuario}}.instagramVerificado as instagramVerificado",
        "{{Usuario}}.id as uId",
        "count({{Evento}}.id) as cantidadEventos",
        "count({{Evento}}.[[redSocial]]) filter (where {{Evento}}.[[redSocial]] = 'Facebook') as eventosFacebook",
        "count({{Evento}}.[[redSocial]]) filter (where {{Evento}}.[[redSocial]] = 'Twitter') as eventosTwitter",
        "count({{Evento}}.[[redSocial]]) filter (where {{Evento}}.[[redSocial]] = 'Instagram') as eventosInstagram",
        "count({{Resultado}}.accion) as cantidadResultados",
        "count({{Resultado}}.accion) filter (where {{Evento}}.[[redSocial]] = 'Facebook') as participacionesFacebook",
        "count({{Resultado}}.accion) filter (where {{Evento}}.[[redSocial]] = 'Twitter') as participacionesTwitter",
        "count({{Resultado}}.accion) filter (where {{Evento}}.[[redSocial]] = 'Instagram') as participacionesInstagram",
        "(count({{Evento}}.id) - count({{Resultado}}.accion)) as cantidadPendientes",
      ])
      ->from("Usuario")
      ->innerJoin("UsuarioGrupo", "{{UsuarioGrupo}}.[[idUsuario]] = {{Usuario}}.id")
      ->innerJoin("Grupo", "{{Grupo}}.id = {{UsuarioGrupo}}.[[idGrupo]]")
      ->innerJoin("EventoGrupo", "{{EventoGrupo}}.[[idGrupo]] = {{Grupo}}.id")
      ->leftJoin("Resultado", "{{Resultado}}.[[idUsuario]] = {{Usuario}}.id and {{Resultado}}.[[idEvento]] = {{Evento}}.id")
      ->groupBy(["{{Usuario}}.verificado", "facebook", "facebookVerificado", "twitter", "twitterVerificado", "instagram", "instagramVerificado", "uId"]);

    $query2 = (new Query())
      ->select([
        "verificado",
        "[[eventosFacebook]]",
        "[[participacionesFacebook]]",
        "[[eventosTwitter]]",
        "[[participacionesTwitter]]",
        "[[eventosInstagram]]",
        "[[participacionesInstagram]]",
        "[[cantidadResultados]]",
        "([[eventosFacebook]] + [[eventosTwitter]] + [[eventosInstagram]]) as [[cantidadEventos]]",
        "([[eventosFacebook]] + [[eventosTwitter]] + [[eventosInstagram]]) - [[cantidadResultados]] as [[cantidadPendientes]]"
      ]);
      // ->from(["t" => $query]);
    
    $meses = [];
    foreach($fechas as $_fechas) {
      $queryClon = (clone $query)->andWhere([
        "AND",
        [">=", "{{Evento}}.[[fechaInicio]]", $_fechas[0]],
        ["<=", "{{Evento}}.[[fechaInicio]]", $_fechas[1]],
        [">=", "verificado", $_fechas[0]],
        ["<=", "verificado", $_fechas[1]],
      ]);
      $mes = (clone $query2)->from(["t" => $queryClon])->one();
      
      $meses[] = $mes;
    }

    return $meses;
  }

  public function actionGuardar() {
    $metodo = "";
    $fechaInicio = trim($this->req->getBodyParam("fechaInicio", ""));
    $fechaFinal = trim($this->req->getBodyParam("fechaFinal", ""));

    $fechas = [
      ["2022-08-01 00:00:00", "2022-08,31 23:59:59"],
      ["2022-09-01 00:00:00", "2022-09,30 23:59:59"],
      ["2022-10-01 00:00:00", "2022-10,31 23:59:59"],
      ["2022-11-01 00:00:00", "2022-11,30 23:59:59"],
      ["2022-12-01 00:00:00", "2022-12,31 23:59:59"],
      ["2023-01-01 00:00:00", "2023-01,31 23:59:59"],
    ];
    
    $query = (new Query())
      ->select([
        "{{Usuario}}.nombre as nombre",
        "{{Usuario}}.verificado as verificado",
        "{{Usuario}}.facebook as facebook",
        "{{Usuario}}.facebookVerificado as facebookVerificado",
        "{{Usuario}}.twitter as twitter",
        "{{Usuario}}.twitterVerificado as twitterVerificado",
        "{{Usuario}}.instagram as instagram",
        "{{Usuario}}.instagramVerificado as instagramVerificado",
        "{{Usuario}}.id as uId",
        "count({{Evento}}.id) as cantidadEventos",
        "count({{Evento}}.[[redSocial]]) filter (where {{Evento}}.[[redSocial]] = 'Facebook') as eventosFacebook",
        "count({{Evento}}.[[redSocial]]) filter (where {{Evento}}.[[redSocial]] = 'Twitter') as eventosTwitter",
        "count({{Evento}}.[[redSocial]]) filter (where {{Evento}}.[[redSocial]] = 'Instagram') as eventosInstagram",
        "count({{Resultado}}.accion) as cantidadResultados",
        "count({{Resultado}}.accion) filter (where {{Evento}}.[[redSocial]] = 'Facebook') as participacionesFacebook",
        "count({{Resultado}}.accion) filter (where {{Evento}}.[[redSocial]] = 'Twitter') as participacionesTwitter",
        "count({{Resultado}}.accion) filter (where {{Evento}}.[[redSocial]] = 'Instagram') as participacionesInstagram",
        "(count({{Evento}}.id) - count({{Resultado}}.accion)) as cantidadPendientes",
      ])
      ->from("Usuario")
      ->innerJoin("UsuarioGrupo", "{{UsuarioGrupo}}.[[idUsuario]] = {{Usuario}}.id")
      ->innerJoin("Grupo", "{{Grupo}}.id = {{UsuarioGrupo}}.[[idGrupo]]")
      ->innerJoin("EventoGrupo", "{{EventoGrupo}}.[[idGrupo]] = {{Grupo}}.id")
      ->leftJoin("Resultado", "{{Resultado}}.[[idUsuario]] = {{Usuario}}.id and {{Resultado}}.[[idEvento]] = {{Evento}}.id")
      ->groupBy(["{{Usuario}}.verificado", "facebook", "facebookVerificado", "twitter", "twitterVerificado", "instagram", "instagramVerificado", "uId"]);

    $query2 = (new Query())
      ->select([
        "verificado",
        "[[eventosFacebook]]",
        "[[participacionesFacebook]]",
        "[[eventosTwitter]]",
        "[[participacionesTwitter]]",
        "[[eventosInstagram]]",
        "[[participacionesInstagram]]",
        "[[cantidadResultados]]",
        "([[eventosFacebook]] + [[eventosTwitter]] + [[eventosInstagram]]) as [[cantidadEventos]]",
        "([[eventosFacebook]] + [[eventosTwitter]] + [[eventosInstagram]]) - [[cantidadResultados]] as [[cantidadPendientes]]"
      ]);
      // ->from(["t" => $query]);
    
    $meses = [];
    foreach($fechas as $_fechas) {
      $queryClon = (clone $query)->andWhere([
        "AND",
        [">=", "{{Evento}}.[[fechaInicio]]", $_fechas[0]],
        ["<=", "{{Evento}}.[[fechaInicio]]", $_fechas[1]],
        [">=", "verificado", $_fechas[0]],
        ["<=", "verificado", $_fechas[1]],
      ]);
      $mes = (clone $query2)->from(["t" => $queryClon])->one();
      
      $meses[] = $mes;
    }
  }
  
  public function actionEliminar() {
    return "Ejemplo de un delete";
  }
}