controlador-descarga/controllers/DescargaController.php

<?php

namespace app\controllers;

use app\components\data\Respuesta;
use app\components\rest\JsonController;
use yii\db\Query;
use yii\filters\AccessControl;
use yii\filters\VerbFilter;

class DescargaController extends JsonController {

  public function behaviors() {
    return [
      'access' => [
        'class' => AccessControl::className(),
        'only' => ['por-pc', 'guardar'],
        'rules' => [
          [
            'actions' => ['por-pc', 'guardar'],
            'allow' => true,
            'roles' => ['@'],
          ],
        ],
      ],
    ];
  }

  public function actionPorPc() {
    $req = \Yii::$app->getRequest();
    $pc = trim($req->get("pc", ""));
    $fi = trim($req->get("fi", ""));
    $ff = trim($req->get("ff", ""));
    $estacion = trim($req->get("estacion", ""));

    $query = (new Query())
      ->select([
        "{{Estacion}}.id",
        "{{Estacion}}.clave",
        "{{Estacion}}.siglas",
        "{{Estacion}}.frecuencia",
        "ciudad",
        "to_char(fecha, 'YYYY-MM-DD') as fecha",
        "count(estacion) filter (where descargado = true) as descargados",
        "count(estacion) filter (where descargado = false) as pendientes"
      ])
      ->from("Descarga")
      ->innerJoin("Estacion", "{{Estacion}}.clave = {{Descarga}}.estacion")
      ->andWhere(["pc" => $pc])
      ->groupBy(["{{Estacion}}.id", "to_char(fecha, 'YYYY-MM-DD')", "ciudad"])
      ->orderBy(["fecha" => SORT_DESC]);

    if($estacion !== "") {
      $query->andWhere(["estacion" => $estacion]);
    }

    if($fi !== "") {
      $query->andWhere([">=", "fecha", $fi]);
    }

    if($ff !== "") {
      $query->andWhere(["<=", "fecha", $ff]);
    }

    return (new Respuesta($query, -1));
  }

  public function actionGuardar() {
    $req = \Yii::$app->getRequest();
    $pc = trim($req->getBodyParam("idPc", ""));
    $fi = trim($req->getBodyParam("fi", ""));
    $ff = trim($req->getBodyParam("ff", ""));
    $reiniciar = $req->getBodyParam("reiniciar", false);
    $estaciones = $req->getBodyParam("estaciones", []);

    $aux = [];
    foreach($estaciones as $e) {
      if(!isset($aux[$e["idCiudad"]])) {
        $aux[$e["idCiudad"]] = [];
      }
      $aux[$e["idCiudad"]][] = $e["clave"];
    }
    $estaciones = $aux;

    try {
      $condicion = ["AND",  
        ["<=", "fecha", $ff], 
        [">=", "fecha", $fi]
      ];
      $aux = ["OR"];
      foreach($estaciones as $idCiudad => $est) {
        $aux[] = [
          "AND", 
          ["ciudad" => $idCiudad], 
          ["estacion" => $est]
        ];
      }
      $condicion[] = $aux;
      $params = ["pc" => $pc];
      if($reiniciar) {
        $params["descargado"] = false;
      }
      $resultado = \Yii::$app->getDb()->createCommand()
        ->update("Descarga", $params, $condicion)
        ->execute();

      return (new Respuesta())
        ->detalle([ "actualizados" => $resultado ])
        ->mensaje("OK");
    } catch(\Exception $e) {
      return (new Respuesta())
        ->esError()
        ->mensaje($e->getMessage());
    }
  }

}