laud_sec_api/modules/v1/controllers/ResultadosIndividualesController.php

<?php

namespace v1\controllers;

use common\data\Respuesta;
use common\rest\AuthController;
use yii\db\Expression;
use yii\db\Query;

class ResultadosIndividualesController extends AuthController {

  public function actionIndex() {
    $idUsuario = intval($this->req->getBodyParam("idUsuario", ""));
    $fechaInicio = trim($this->req->getBodyParam("fechaInicio", ""));
    $fechaFinal = trim($this->req->getBodyParam("fechaFinal", ""));
    $limite = intval($this->req->getBodyParam("limite", ""));
    $pagina = intval($this->req->getBodyParam("pagina", 1));

    $query = (new Query())
      ->select([
        "{{Usuario}}.nombre as nombre",
        "{{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({{Evento}}.id) - count({{Resultado}}.accion)) as cantidadPendientes",
      ])
      ->from("Evento")
      ->innerJoin("EventoGrupo", "{{EventoGrupo}}.[[idEvento]] = {{Evento}}.id")
      ->innerJoin("Grupo", "{{Grupo}}.id = {{EventoGrupo}}.[[idGrupo]]")
      ->innerJoin("UsuarioGrupo", "{{UsuarioGrupo}}.[[idGrupo]] = {{Grupo}}.id")
      ->innerJoin("Usuario", "{{Usuario}}.id = {{UsuarioGrupo}}.[[idUsuario]]")
      ->leftJoin("Resultado", "{{Resultado}}.[[idEvento]] = {{Evento}}.id and {{Resultado}}.[[idUsuario]] = {{Usuario}}.id")
      ->groupBy(["{{Usuario}}.nombre", "facebook", "facebookVerificado", "twitter", "twitterVerificado", "instagram", "instagramVerificado", "uId"]);


    if ($idUsuario > 0) {
      $query->andWhere(["{{Usuario}}.[[id]]" => $idUsuario]);
      $limite = 1;
      $pagina = 1;
    }

    if ($fechaInicio != "" && $fechaFinal != "") {
      $query->andWhere([
        "AND",
        [">=", "{{Evento}}.[[fechaInicio]]", $fechaInicio],
        ["<=", "{{Evento}}.[[fechaFinal]]", $fechaFinal],
      ]);
    }

    $query2 = (new Query())
        ->select([
          "*",
          "([[eventosFacebook]] + [[eventosTwitter]] + [[eventosInstagram]]) as [[cantidadEventos]]",
          "([[eventosFacebook]] + [[eventosTwitter]] + [[eventosInstagram]]) - [[cantidadResultados]] as [[cantidadPendientes]]"
        ])
        ->from(["t" => $query]);

    return new Respuesta($query2, $limite, $pagina, $this->ordenar);
  }
}