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 = ' '; foreach ($estaciones as $pc => $est) { foreach($est as $clave => $info) { $content .= ''; $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 .= ''; $content .= ''; } } //*/ $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"); } }

pc	siglas	clave	frecuencia
' . $pc . '	' . $info['siglas'] . '	' . $info['clave'] . '	' . $info['frecuencia'] . '	'; foreach($dias as $dia) { if(isset($descargas[$pc][$clave][$dia])) { $content .= ' ' . $dia . ' ' . $descargas[$pc][$clave][$dia]['descargados'] . ' ' . $descargas[$pc][$clave][$dia]['pendientes'] . ' ' . $descargas[$pc][$clave][$dia]['total'] . ' '; } else { $content .= ' ' . $dia . ' 0 0 0 '; } } $content .= '