controlador-descarga/commands/ReporteController.php

<?php

namespace app\commands;

use yii\base\View;
use yii\db\Query;

class ReporteController extends \yii\console\Controller {

  public $pc = null;
  public $st = null;
  public $fi = null;
  public $ff = null;

  public function options($actionID) {
    return [
      'pc',
      'st',
      'fi',
      'ff'
    ];
  }

  public function actionIndex() {
    $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($this->fi !== null) {
      $query->andWhere([">=", "fecha", "{$this->fi} 00:00:00"]);
    }
  
    if($this->ff !== null) {
      $query->andWhere(["<=", "fecha", "{$this->ff} 23:59:59"]);
    }

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

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

    $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";
      if($d["descargados"] >= 70 && $d["descargados"] < 90) {
        $color = "darkgray";
      } elseif($d["descargados"] < 70) {
        $color = "red";
      }
      $descargas[$d['pc']][$d['clave']][$d['fecha']] = [
        "descargados" => $d['descargados'],
        "pendientes" => $d['pendientes'],
        "total" => $d['total'],
        "color" => $color
      ];
    }

    
    $view = new View();

    $content = $view->render('/reporte/index', [
      "estaciones" => $estaciones,
      "descargas" => $descargas,
      "primer" => $primer,
      "ultimo" => $ultimo
    ]);

    /*
    $content = '<table border="1" width="100%" id="tabla">
      <tr>
        <th>pc</th>
        <th>siglas</th>
        <th>clave</th>
        <th>frecuencia</th>
        <th colspan="30">
          <!--table>
            <tr id="dias"></tr>
          </table -->
        </th>
      </tr>
    ';
    foreach ($estaciones as $pc => $est) {
      foreach($est as $clave => $info) {
        $content .= '<tr>
          <td>' . $pc . '</td>
          <td>' . $info['siglas'] . '</td>
          <td>' . $info['clave'] . '</td>
          <td>' . $info['frecuencia'] . '</td>';
        $dias = [];
        $fecha = $primer;
        while($fecha <= $ultimo) {
          $dias[] = $fecha->format("Y-m-d");
          $fecha->add(new \DateInterval("P1D"));
          $this->stdout("Fecha: {$fecha->format("Y-m-d")}\n");
        }
        $content .= '<td>';
        foreach($dias as $dia) {
          if(isset($descargas[$pc][$clave][$dia])) {
            $content .= '<table>
              <tr>
                <td>' . $dia . '</td>
                <td>' . $descargas[$pc][$clave][$dia]['descargados'] . '</td>
                <td>' . $descargas[$pc][$clave][$dia]['pendientes'] . '</td>
                <td>' . $descargas[$pc][$clave][$dia]['total'] . '</td>
              </tr>
            </table>';
          } else {
            $content .= '<table>
              <tr>
                <td>' . $dia . '</td>
                <td>0</td>
                <td>0</td>
                <td>0</td>
              </tr>
            </table>';
          }
        }
        $content .= '</td>';
        $content .= '</tr>';
      }
    } //*/

    $file = "reporte.html"; // \Yii::$app->getSecurity()->generateRandomString(10) . ".html";
    $base = \Yii::getAlias("@app") . "/web/assets";
    file_put_contents("{$base}/{$file}", $content);

    $this->stdout("/assets/{$file}\n");
  }

}