controlador-descarga/controllers/ReporteController.php

<?php

namespace app\controllers;

use yii\db\Query;
use yii\web\Controller;

class ReporteController extends Controller {

  public $layout = 'bulma';

  public function actionDescarga() {
    $req = \Yii::$app->getRequest();
    $pc = trim($req->get("pc", ""));
    $st = trim($req->get("st", ""));
    $fi = trim($req->get("fi", "2021-12-01"));
    $ff = trim($req->get("ff", ""));
    $sql = intval($req->get("sql", "")) === 1;
    $json = intval($req->get("json", "")) === 1;

    $query = (new Query())
      ->select([
        "{{Estacion}}.id",
        "{{Estacion}}.clave",
        "{{Estacion}}.siglas",
        "{{Estacion}}.frecuencia",
        "ciudad",
        "pc",
        "to_char(fecha, 'YYYY-MM-DD') as fecha",
        "extract(epoch from to_char(fecha, 'YYYY-MM-DD')::DATE) as epoch",
        "count(estacion) filter (where descargado = true) as descargados",
        "count(estacion) filter (where descargado = false) as pendientes",
        "count(estacion) as total"
      ])
      ->from("Descarga")
      ->innerJoin("Estacion", "{{Estacion}}.clave = {{Descarga}}.estacion")
      ->andWhere(['not', ['pc' => null]])
      ->groupBy(["{{Estacion}}.id", "to_char(fecha, 'YYYY-MM-DD')", "ciudad", "pc"])
      ->orderBy([
        "pc" => SORT_ASC,
        "clave" => SORT_ASC,
        "fecha" => SORT_ASC,
      ]);
    
    if($fi !== "") {
      $query->andWhere([">=", "fecha", "{$fi} 00:00:00"]);
    }
  
    if($ff !== "") {
      $query->andWhere(["<=", "fecha", "{$ff} 23:59:59"]);
    }

    if($pc !== "") {
      $aux = explode(",", $pc);
      $pcs = [];
      foreach($aux as $p) {
        $v = trim($p);
        if(!empty($v)) {
          $pcs[] = $v;
        }
      }
      $query->andWhere(["pc" => $pcs]);
    }

    if($st !== "") {
      $aux = explode(",", $st);
      $sts = [];
      foreach($aux as $p) {
        $v = trim($p);
        if(!empty($v)) {
          $sts[] = $v;
        }
      }
      $query->andWhere(["estacion" => $sts]);
    }

    if($sql) {
      \Yii::$app->getResponse()->format = \yii\web\Response::FORMAT_RAW;
      return $query->createCommand()->getRawSql();
    }

    $aux = $query->all();
    $primer = null;
    $ultimo = null;
    if(!empty($aux)) {
      $tz = new \DateTimeZone("America/Mexico_City");
      $primer = \DateTime::createFromFormat("Y-m-d", $aux[0]['fecha'], $tz);
      $ultimo = \DateTime::createFromFormat("Y-m-d", $aux[count($aux) - 1]['fecha'], $tz);
      $primer->setTime(0, 0, 0);
      $ultimo->setTime(0, 0, 0);
    }
    $descargas = [];
    $estaciones = [];
    foreach($aux as $d) {
      if(!isset($estaciones[$d['pc']]) || (isset($estaciones[$d['pc']]) && !isset($estaciones[$d['pc']][$d['clave']]))) {
        $estaciones[$d['pc']][$d['clave']] = [
          "clave" => $d['clave'],
          "siglas" => $d['siglas'],
          "ciudad" => $d['ciudad'],
          "frecuencia" => $d['frecuencia']
        ];
      }
      $color = "darkgreen";
      $porcentaje = $d["descargados"] * 100 / $d["total"];
      if($d["total"] < 240) {
        $color = "#ff5733";
      } elseif($porcentaje  >= 70 && $porcentaje  < 90) {
        $color = "darkblue";
      } elseif($porcentaje  < 70) {
        $color = "blue";
      }
      $descargas[$d['pc']][$d['clave']][$d['fecha']] = [
        "descargados" => $d['descargados'],
        "pendientes" => $d['pendientes'],
        "total" => $d['total'],
        "color" => $color
      ];
    }

    if($json) {
      \Yii::$app->getResponse()->format = \yii\web\Response::FORMAT_JSON;
      return [
        "primer" => $primer,
        "ultimo" => $ultimo,
        "estaciones" => $estaciones,
        "descargas" => $descargas
      ];
    }

    return $this->render('index', [
      "estaciones" => $estaciones,
      "descargas" => $descargas,
      "primer" => $primer,
      "ultimo" => $ultimo
    ]);
  }

  public function actionDescargaSinPc() {
    $req = \Yii::$app->getRequest();
    $pc = trim($req->get("pc", ""));
    $st = trim($req->get("st", ""));
    $fi = trim($req->get("fi", "2021-12-01"));
    $ff = trim($req->get("ff", ""));
    $sql = intval($req->get("sql", "")) === 1;
    $json = intval($req->get("json", "")) === 1;

    $query = (new Query())
      ->select([
        "{{Estacion}}.id",
        "{{Estacion}}.clave",
        "{{Estacion}}.siglas",
        "{{Estacion}}.frecuencia",
        "ciudad",
        "to_char(fecha, 'YYYY-MM-DD') as fecha",
        "extract(epoch from to_char(fecha, 'YYYY-MM-DD')::DATE) as epoch",
        "count(estacion) filter (where descargado = true) as descargados",
        "count(estacion) filter (where descargado = false) as pendientes",
        "count(estacion) as total"
      ])
      ->from("Descarga")
      ->innerJoin("Estacion", "{{Estacion}}.clave = {{Descarga}}.estacion")
      ->andWhere(['not', ['pc' => null]])
      ->groupBy(["{{Estacion}}.id", "to_char(fecha, 'YYYY-MM-DD')", "ciudad"])
      ->orderBy([
        "ciudad" => SORT_ASC,
        "clave" => SORT_ASC,
        "fecha" => SORT_ASC,
      ]);
    
    if($fi !== "") {
      $query->andWhere([">=", "fecha", "{$fi} 00:00:00"]);
    }
  
    if($ff !== "") {
      $query->andWhere(["<=", "fecha", "{$ff} 23:59:59"]);
    }

    if($pc !== "") {
      $aux = explode(",", $pc);
      $pcs = [];
      foreach($aux as $p) {
        $v = trim($p);
        if(!empty($v)) {
          $pcs[] = $v;
        }
      }
      $query->andWhere(["pc" => $pcs]);
    }

    if($st !== "") {
      $aux = explode(",", $st);
      $sts = [];
      foreach($aux as $p) {
        $v = trim($p);
        if(!empty($v)) {
          $sts[] = $v;
        }
      }
      $query->andWhere(["estacion" => $sts]);
    }

    if($sql) {
      \Yii::$app->getResponse()->format = \yii\web\Response::FORMAT_RAW;
      return $query->createCommand()->getRawSql();
    }

    $aux = $query->all();
    $primer = null;
    $ultimo = null;
    if(!empty($aux)) {
      $tz = new \DateTimeZone("America/Mexico_City");
      $primer = \DateTime::createFromFormat("Y-m-d", $aux[0]['fecha'], $tz);
      $ultimo = \DateTime::createFromFormat("Y-m-d", $aux[count($aux) - 1]['fecha'], $tz);
      $primer->setTime(0, 0, 0);
      $ultimo->setTime(0, 0, 0);
    }
    $descargas = [];
    $estaciones = [];
    foreach($aux as $d) {
      if(!isset($estaciones[$d['clave']])) {
        $estaciones[$d['clave']] = [
          "clave" => $d['clave'],
          "siglas" => $d['siglas'],
          "ciudad" => $d['ciudad'],
          "frecuencia" => $d['frecuencia']
        ];
      }
      $color = "darkgreen";
      $porcentaje = $d["descargados"] * 100 / $d["total"];
      if($d["total"] < 240) {
        $color = "#ff5733";
      } elseif($porcentaje  >= 70 && $porcentaje  < 90) {
        $color = "darkblue";
      } elseif($porcentaje  < 70) {
        $color = "blue";
      }
      $descargas[$d['clave']][$d['fecha']] = [
        "descargados" => $d['descargados'],
        "pendientes" => $d['pendientes'],
        "total" => $d['total'],
        "color" => $color
      ];
    }

    if($json) {
      \Yii::$app->getResponse()->format = \yii\web\Response::FORMAT_JSON;
      return [
        "primer" => $primer,
        "ultimo" => $ultimo,
        "estaciones" => $estaciones,
        "descargas" => $descargas
      ];
    }

    return $this->render('sin-pc', [
      "estaciones" => $estaciones,
      "descargas" => $descargas,
      "primer" => $primer,
      "ultimo" => $ultimo
    ]);
  }

}