controlador-descarga/commands/CopyController.php

<?php

namespace app\commands;

use app\models\Descarga;
use yii\console\Controller;
use yii\db\Expression;
use yii\db\Query;

class CopyController extends Controller {

  public $ciudades = [
    "03LDf61X" => [
      "SPCH96_9",
      "SPCH105_9",
      "SPCH94_1",
      "SPCH105_1",
      "PUE94_9",
      "SPCH98_7",
      "SPCH91_7",
      "PUE103_3",
      "SPCH90_1",
      "SPCH102_1",
      "SPCH92_5",
    ],
    "07TNqt6A" => [
      "MCVA94_7",
      "MCVA103_1",
      "MCVA99_5",
      "MCVA97_1",
      "MCVA91_1",
      "MCVA104_9",
      "MCVA101_1",
      "MCVA100_3",
      "MCVA98_7",
      "MCVA96_3",
      "MCVA107_1",
      "MCVA104_1",
    ],
    "13MLZC8S" => [
      "TV_CDMX_3_1",
      "TV_CDMX_1_1",
      "TV_CDMX_5_1",
      "TV_CDMX_7_1",
      "TV_CDMX_40_1",
      "TV_CDMX_4_1",
      "TV_CDMX_8_1",
      "TV_CDMX_4_1",
    ],
    "1wwBGsDq" => [
      "TV_GDL_17_1",
      "TV_GDL_44_1",
    ],
    "4IpjbpDO" => [
      "CHI90_1",
      "CHI92_5",
      "CHI97_3",
      "CHI105_3",
      "CHI106_1",
      "CHI102_5",
    ],
    "5i6lpw3D" => [
      "TV_XAL_8_1",
      "TV_XAL_26_1",
      "TV_XAL_5_1",
      "TV_XAL_9_1",
    ],
    "5T8btSQM" => [
      "CUE90_3",
      "CUE93_3",
      "CUE94_9",
      "CUE95_7",
      "CUE96_5",
      "CUE97_3",
      "CUE98_1",
      "CUE99_1",
      "CUE100_1",
      "CUE102_9",
      "CUE106_1",
      "CUE106_9",
      "CUE107_7",
    ],
    "6qIpZjMR" => [
      "TV_TUX_1_1",
      "TV_TUX_10_1",
      "TV_TUX_2_1",
      "TV_TUX_7_1",
    ],
    "7YDLvHnY" => [
      "URU100_5",
      "URU101_3",
      "URU102_1",
      "URU89_7",
      "URU91_1",
      "URU93_7",
      "URU99_7",
    ],
    "8Duwg4qe" => [
      "SNG95_7",
      "SNG92_5",
      "SNG94_9",
      "SNG90_5",
      "SNG91_7",
    ],
    "9CkyTkDk" => [
      "TV_COL_2_1",
      "TV_COL_5_1",
      "TV_COL_7_1",
      "TV_COL_3_1",
    ],
    "9NBfrirA" => [
      "TV_TEP_7_1",
      "TV_TEP_5_1",
      "TV_TEP_10_1",
    ],
    "9R9SiR7p" => [
      "MAT97_7",
      "MAT_91_3",
    ],
    "9TJwI7aV" => [
      "TV_CDMX_22_1",
      "TV_CDMX_14_2",
      "TV_CDMX_6_2",
      "TV_CDMX_6_3",
    ],
    "B3inty0" => [
      "QUE91_7",
      "QUE98_7",
      "QUE104_9",
      "QUE95_5",
      "QUE94_7",
      "QUE107_9",
      "QUE100_3",
      "QUE100_3",
    ],
    "Br0k3yZx" => [
      "101_1_GDL",
      "95_5_GDL",
    ],
    "ca1qqivn" => [
      "TV_LEON_3_1",
      "TV_LEON_5_1",
      "TV_LEO_6_1",
      "TV_LEO_2_1",
    ],
    "CqKnAoz2" => [
      "TV_VER_2_1",
      "TV_VER_12_1",
    ],
    "cXZamnSk" => [
      "TV_MOR_13_1",
      "TV_MOR_3_1",
      "TV_MOR_7_1",
      "TV_MOR_14_1",
      "TV_MOR_5_1",
    ],
    "D4nytzP" => [
      "AGU106_9",
      "AGU95_7",
      "AGU93_7",
      "AGU100_1",
      "AGU88_7",
      "AGU100_9",
      "AGU102_9",
      "AGU98_1",
      "AGU91_3",
      "AGU92_7",
      "AGU94_5",
      "AGU90_5",
      "AGU105_3",
      "AGU107_7",
    ],
    "denPJ1FV" => [
      "ODJ89_7",
      "ODJ93_7",
      "ODJ94_9",
      "ODJ95_7",
      "ODJ96_9",
      "ODJ97_7",
      "ODJ100_1",
      "ODJ101_7",
      "ODJ105_7",
      "ODJ100_9",
    ],
    "DIW67ZVD" => [
      "TUX98_5",
      "TUX96_9",
      "TUX105_9",
      "TUX93_1",
      "TUX88_3",
      "TUX90_3",
      "TUX93_9",
      "TUX96_1",
      "TUX103_5",
      "TUX102_5",
      "TUX100_1",
      "TUX101_7",
    ],
    "dvRKE2Q-" => [
      "TLA96_5",
      "TLA99_5",
      "TLA100_3",
    ],
    "eFopFzT1" => [
      "TV_VIC_7_1",
      "TV_VIC_3_1",
      "TV_VIC_2_1",
      "TV_VIC_5_1",
    ],
    "eLJmNJ5gG1H" => [
      "102_7HMO",
      "97_1HMO",
      "101_1HMO",
      "107_5HMO",
      "97_1HMO",
    ],
    "enOjw_tZ" => [
      "TV_MCVA_1_1",
      "TV_MCVA_7_1",
      "TV_MCVA_2_1",
      "TV_MCVA_5_1",
    ],
    "FTiguGXr" => [
      "TOL93_3",
      "TOL90_1",
      "TOL91_7",
      "TOL98_9",
      "TOL102_1",
      "TOL99_7",
      "TOL101_3",
    ],
    "Ga6wUhMb" => [
      "TV_TOR_5_1",
      "TV_TOR_1_1",
      "TV_TOR_7_1",
      "TV_TOR_2_1",
    ],
    "gFlzFvnx" => [
      "TV_QUE_5_1",
      "TV_QUE_3_1",
      "TV_QUE_11_1",
      "TV_QUE_9_1",
    ],
    "GSIU4U2m" => [
      "LEON107_1",
      "LEON95_5",
      "LEON101_1",
      "LEON98_3",
      "LEON104_1",
      "LEON90_3",
      "LEON92_3",
      "LEON93_9",
      "LEON99_9",
    ],
    "GwCBvAxo" => [
      "TV_CAN_2_1",
      "TV_CAN_5_1",
    ],
    "h0E6IAeR" => [
      "DUR101_3",
      "DUR95_7",
      "DUR98_9",
      "DUR105_3",
      "DUR100_5",
      "DUR102_9",
      "DUR94_1",
      "DUR92_9",
    ],
    "h4QN6Pde" => [
      "TV_REY_7_1",
      "TV_REY_1_2",
      "TV_REY_1_1",
      "TV_REY_6_2",
    ],
    "IHxtMrao" => [
      "TV_PUE_3_1",
      "TV_PUE_4_1",
      "TV_PUE_26_1",
      "TV_PUE_1_1",
      "TV_PUE_14_1",
    ],
    "IJlxRs9K" => [
      "TEP96_1",
      "TEP98_5",
      "TEP97_7",
      "TEP101_9",
      "TEP95_3",
      "TEP93_7",
      "TEP101_1",
      "TEP92_1",
      "TEP104_9",
    ],
    "iqIRfGOb" => [
      "88_9HMO",
      "91_5HMO",
    ],
    "irztrhmN" => [
      "REY101_5",
      "REY102_5",
      "REY95_3",
    ],
    "JAMI2CKo" => [
      "JUA103_5",
      "JUA100_7",
      "JUA105_1",
      "JUA107_5",
      "JUA104_3",
      "JUA106_7",
    ],
    "jElsmiSu" => [
      "TV_2_1_SON_HMO_AIR",
      "TV_9_1_SON_HMO_AIR",
      "TV_5_1_SON_HMO_AIR",
    ],
    "jLMtcEIk" => [
      "TV_CUL_2_1",
      "TV_CUL_1_1",
      "TV_ACA_7_1",
      "TV_ACA_9_1",
      "TV_ACA_5_1",
      "TV_ACA_2_1",
      "TV_CUL_5_1",
      "TV_CUL_11_1",
    ],
    "jujYqh0S" => [
      "MER105_9",
      "MER97_7",
      "MER95_3",
      "MER90_9",
      "MER98_5",
      "MER103_1",
      "MER103_9",
      "MER94_5",
      "MER105_1",
      "MER105_9",
    ],
    "jYnLHGxp" => [
      "TV_TIJ_12_1",
      "TV_TIJ_3_1",
      "TV_TIJ_21_1",
      "TV_TIJ_33_1",
    ],
    "Jz6LdVsk" => [
      "PCH98_1",
      "PCH95_7",
      "PCH106_1",
      "PCH92_5",
      "PCH104_5",
      "PCH99_7",
    ],
    "k1bOV7pD" => [
      "TV_CUE_1_1",
      "TV_CUE_9_1",
      "TV_CUE_11_1",
    ],
    "KFB8tKff" => [
      "TV_MEX_1_1",
      "TV_MEX_2_1",
      "TV_MEX_10_1",
      "TV_MEX_5_1",
    ],
    "KNbHfLHQ" => [
      "TV_MTY_5_1",
      "TV_MTY_8_1",
      "TV_SNG_2_1",
      "TV_MON_7_1"
    ],
    "kQw04CrV" => [
      "TV_CDMX_2_1",
      "TV_CDMX_34_1",
      "TV_CDMX_9_1",
      "TV_CDMX_45_1",
    ],
    "Lq2GWUH6" => [
      "MOR105_1",
      "MOR100_9",
      "MOR93_1",
      "MOR103_1",
      "MOR91_5",
      "MOR97_3",
      "MOR98_1",
      "MOR101_7",
      "MOR106_9",
      "MOR102_5",
      "MOR104_3",
    ],
    "Lq50GGwP" => [
      "VIC99_3",
      "VIC95_3",
      "VIC96_1",
      "VIC102_5",
      "VIC107_9",
      "VIC101_7",
    ],
    "LTAUmjc4" => [
      "TV_15_1_SON_HMO_AIR",
      "TV_12_1_SON_HMO_AIR",
      "TV_.7_1_SON_HMO_AIR",
      "TV_1_1_SON_HMO_AIR",
    ],
    "mGeawnyY" => [
      "LPZ99_1",
      "LPZ92_7",
      "LPZ105_5",
      "LPZ97_5",
      "LPZ98_3",
      "LPZ95_9",
      "LPZ96_7",
      "LPZ100_7",
    ],
    "MKQ1k5co" => [
      "TV_LPZ_1_2",
      "TV_LPZ_5_1",
      "TV_LPZ_2_1",
      "TV_LPZ_7_1",
    ],
    "mNMrXYUB" => [
      "ACA92_1",
      "ACA102_3",
      "ACA105_5",
      "ACA103_1",
      "ACA101_5",
      "ACA95_3",
      "ACA89_7",
      "ACA99_3",
      "ACA94_5",
      "ACA93_7",
      "ACA100_1",
    ],
    "MUiVNylI" => [
      "TV_TOL_1_1",
      "TV_TOL_3_1",
      "TV_TOL_7_1",
    ],
    "nAFZnAYr" => [
      "CUL91_9",
      "CUL104_1",
      "CUL92_9",
      "CUL101_7",
      "CUL88_7",
      "CUL94_5",
      "CUL95_3",
      "CUL103_3",
      "CUL96_9",
      "CUL102_5",
    ],
    "NPxBYVb4" => [
      "TV_CHI_1_3",
      "TV_CHI_2_1",
      "TV_CHI_3_1",
    ],
    "Ns47mTar" => [
      "TV_PCH_3_1",
      "TV_PCH_12_1",
      "TV_PCH_7_1",
    ],
    "opNQYeRG" => [
      "TV_AGU_5_1",
      "TV_AGU_9_1",
      "TV_AGU_1_1",
      "TV_AGU_7_1",
    ],
    "OWCiV9M9" => [
      "MEX_CDMX_93_7",
      "MEX_CDMX_89_7",
      "MEX_CDMX_92_9",
      "MEX_CDMX_103_3",
      "MEX_CDMX_96_9",
      "CDMX101_7",
      "MEX_CDMX_92_1",
      "MEX_CDMX_88_9",
      "MEX_CDMX_95_3",
      "MEX_CDMX_94_1",
    ],
    "P5tpQ9Fv" => [
      "SLP102_1",
      "SLP95_3",
      "SLP96_9",
      "SLP94_1",
      "SLP99_3",
      "SLP88_5",
    ],
    "pcOFIelT" => [
      "XAL104_9",
      "XAL97_7",
      "XAL91_7",
      "XAL95_5",
      "XAL98_5",
      "XAL107_7",
    ],
    "pOl60TJD" => [
      "BEN102_7",
      "BEN92_3",
      "BEN101_9",
      "BEN99_3",
      "BEN105_9",
      "BEN105_1",
      "BEN90_7",
      "BEN104_3",
      "BEN93_1",
    ],
    "R0p3Ngr" => [
      "TIJ91_1",
      "TIJ94_5",
      "TIJ107_3",
      "TIJ95_3",
      "TIJ88_7",
      "TIJ90_3",
      "TIJ104_9",
      "TIJ98_9",
      "TIJ99_3",
      "TIJ105_7",
      "TIJ92_5",
      "TIJ107_7",
      "TIJ90_7",
      "TIJ99_7",
    ],
    "Reickz4Z" => [
      "VER_106_1",
      "VER_94_1",
      "VER_90_1",
      "VER_99_7",
      "VER_102_9",
      "VER_98_1",
    ],
    "RonDKfBH" => [
      "TV_JUA_2_1",
      "TV_JUA_20_1",
      "TV_JUA_44_1",
      "TV_JUA_5_1",
    ],
    "rt-MOm8-" => [
      "2107_3_MEX",
      "MEX_CDMX_94_5",
      "MEX_CDMX_100_9",
      "105_3_MEX",
      "MEX_CDMX_90_5",
      "MEX_CDMX_96_1",
      "MEX_CDMX_104_1",
    ],
    "S1eMpr3" => [
      "CEN88_5",
      "CEN98_3",
      "CEN89_3",
      "CEN104_9",
      "CEN106_3",
      "CEN90_1",
      "CEN95_7",
      "CEN92_5",
      "CEN_94_9",
      "CEN107_3",
      "CEN97_3",
      "CEN103_3",
      "CEN104_1",
    ],
    "s3c6lpcx" => [
      "TV_MER_1_1",
      "TV_MER_5_1",
      "TV_MER_2_1",
    ],
    "S3IiFs1W" => [
      "TV_DUR_1_1",
      "TV_DUR_2_1",
      "TV_DUR_7_1",
      "TV_DUR_5_1",
    ],
    "sr0SzB2n" => [
      "TV_PDN_1_1",
      "TV_PDN_5_1",
      "TV_PDN_9_1",
      "TV_PDN_2_1",
    ],
    "TEFCS0Ix" => [
      "COL89_3",
      "COL90_5",
      "COL98_9",
      "COL98_1",
      "COL104_5",
      "COL94_9",
      "COL92_5",
    ],
    "TJITznDM" => [
      "MAZ91_3",
      "MAZ104_3",
      "MAZ88_9",
    ],
    "tKykZpfA" => [
      "COA93_1",
      "COA98_5",
      "COA103_5",
      "COA100_1",
    ],
    "Tpz5vBCW" => [
      "TV_TAB_46_1",
      "TV_TAB_13_1",
      "TV_TAB_35_1",
      "TV_TAB_1_1",
      "TV_TAB_7_1",
      "TV_TAB_2_1",
    ],
    "tQ-bDD6u" => [
      "TV_SAL_9_1",
      "TV_SAL_3_1",
      "TV_SAL_7_1",
    ],
    "Tri3mym9" => [
      "PDN101_7",
      "PDN103_7",
      "PDN96_7",
      "PDN97_9",
      "PDN107_9",
      "PDN106_3",
      "PDN105_5",
      "PDN99_9",
      "PDN99_1",
      "PDN94_5",
      "PDN104_3",
    ],
    "tu84G13a" => [
      "TOR93_1",
      "TOR100_3",
      "TOR97_9",
      "TOR95_5",
    ],
    "TzDF7cq7" => [
      "TV_MAZ_3_1",
      "TV_MAZ_7_1",
      "TV_MAZ_1_1",
      "TV_MAZ_5_1",
    ],
    "u1JB9Dmw" => [
      "MEX104_1",
      "MEX105_5",
      "MEX91_5",
      "MEX104_9",
      "MEX92_3",
      "MEX90_7",
      "MEX96_9",
      "MEX101_9",
      "MEX105_9",
      "MEX103_3",
      "MEX98_3",
    ],
    "uHVcwWWD" => [
      "IRA98_9",
      "IRA88_5",
      "IRA102_7",
      "IRA106_3",
      "IRA94_3",
      "IRA95_1",
      "IRA93_5",
      "IRA107_9",
      "IRA91_9",
    ],
    "uovj5aMC" => [
      "TV_MON_4_1",
      "TV_MON_6_1",
      "TV_MON_3_1",
      "TV_MON_28_1",
    ],
    "UP4itzbr" => [
      "MEX_CDMX_90_9",
    ],
    "V50Aia9U" => [
      "NLR95_7",
      "NLR90_9",
      "NLR91_3",
      "NLR96_1",
      "NLR97_1",
      "NLR99_3",
      "NLR101_5",
      "NLR102_3",
      "NLR107_3",
    ],
    "V6QOaSZ5" => [
      "TV_MAT_9_1",
      "TV_MAT_8_1",
      "TV_MAT_1_1",
      "TV_MAT_2_1",
    ],
    "VhTifIvKH8" => [
      "93_9HMO",
      "99_5HMO",
      "100_3HMO",
      "94_7HMO",
    ],
    "vNFU8lIw" => [
      "TV_SLP_3_1",
      "TV_SAN LUIS POTOSI_5_1",
    ],
    "w1Enz_y0" => [
      "TV_OAX_2_1",
      "TV_OAX_4_1",
      "TV_OAX_3_1",
      "TV_OAX_7_1",
    ],
    "wDbc0Qll" => [
      "TV_COA_1_1",
      "TV_COA_2_1",
      "TV_COA_3_1",
      "TV_COA_5_1",
    ],
    "x8g8AZF0" => [
      "MEX_CDMX_104_1",
      "MEX_CDMX_97_7",
      "MEX_CDMX_106_1",
      "MEX_CDMX_91_3",
      "MEX_CDMX_100_1",
      "MEX_CDMX_102_5",
      "MEX_CDMX_105_7",
      "MEX_CDMX_99_3",
      "MEX_CDMX_88_15",
      "MEX_CDMX_106_1",
      "MEX_CDMX_107_9",
      "MEX_CDMX_95_7",
    ],
    "YCe8v6ZH" => [
      "SAL88_9",
      "SAL91_3",
      "SAL104_9",
      "SAL93_5",
      "SAL99_3",
    ],
    "Yj1DLiHM" => [
      "TV_TIJ_6_1",
      "TV_TIJ_45_1",
      "TV_TIJ_1_1",
      "TV_TIJ_19_1",
    ],
    "ZXDiy5og" => [
      "94_1_MTY",
      "101_3_MTY",
      "106_1_MTY",
      "102_9_MTY",
      "107_7_MTY",
      "106_9_MTY",
      "97_3_MTY",
      "SNG102_1",
      "MTY93_3",
      "SNJ98_1",
    ],
    "S-_RzKm2" => [
      "TV_GDL_3_1",
      "TV_GDL_2_1"
    ],
    "g23wXKER" => [
      "101_9_GDL",
      "97_1_GDL",
      "102_7_GDL",
      "96_3_GDL",
      "100_3_GDL",
      "98_7_GDL",
      "105_1_GDL",
      "93_9_GDL",
      "107_5_GDL"
    ],
    "8Duwg4qe" => [
      "106_1_MTY",
      "106_9_MTY",
    ]
  ];

  public $ciudad = null;
  public $estacion = null;
  public $fi = null;
  public $ff = null;

  public function options($actionId) {
    return [
      'ciudad',
      'estacion',
      'fi',
      'ff'
    ];
  }

  public function actionIndex() {
    $dbr = \Yii::$app->dbr;
    while(true) {

      $archivos = (new Query())
        ->from("file")
        ->innerJoin("box", "box.id = file.box")
        ->andWhere([">=", "[[timestamp]] at time zone box.timezone", new Expression("now() - interval '2 days'")])
        ->orderBy(["timestamp" => SORT_ASC]);

      $condicion = ["OR"];
      foreach($this->ciudades as $ciudad => $estaciones) {
        $condicion[] = ["AND", ["box" => $ciudad], ["station" => $estaciones]];
      }
      $archivos->andWhere($condicion);

      // $this->stdout($archivos->createCommand()->getRawSql() . "\n");

      foreach($archivos->each(100, $dbr) as $archivo) {
        try {
          \Yii::$app->getDb()->createCommand()
            ->insert("file", [
              "hash" => $archivo["hash"],
              "box" => $archivo["box"],
              "station" => $archivo["station"],
              "filename" => $archivo["filename"],
              "timestamp" => $archivo["timestamp"]
            ])
            ->execute();
          $this->stdout("{$archivo["hash"]} {$archivo["box"]} {$archivo["station"]} {$archivo["timestamp"]}\n");
        } catch(\Exception $e) {
          $this->stdout("{$archivo["hash"]} {$archivo["box"]} {$archivo["station"]} {$archivo["timestamp"]} ya existe\n");
        }
      }

      $this->stdout("Durmiendo por 1 minuto\n");
      sleep(60);
    }
  }

  public function actionUltimo() {
    $dbr = \Yii::$app->dbr;

    $ultimoArchivo = (new Query())
        ->from("file")
        ->orderBy(["timestamp" => SORT_DESC])
        ->limit(1)
        ->one($dbr);

    $this->stdout("Fecha: {$ultimoArchivo["timestamp"]}\n");
  }

  public function actionPorEstacion() {
    $dbr = \Yii::$app->dbr;
    $inicio = time();
    $archivos = (new Query())
      ->from("file")
      ->innerJoin("box", "box.id = file.box")
      ->andWhere([
        "box" => $this->ciudad,
        "station" => $this->estacion,
      ])
      ->andWhere([">=", "[[timestamp]] at time zone box.timezone", "{$this->fi} 00:00:00"])
      ->andWhere(["<", "[[timestamp]] at time zone box.timezone", "{$this->ff} 00:00:00"])
      ->orderBy(["timestamp" => SORT_ASC]);

    // $this->stdout($archivos->createCommand()->getRawSql() . "\n");
    // return;
    foreach($archivos->each(100, $dbr) as $archivo) {
      try {
        \Yii::$app->getDb()->createCommand()
          ->insert("file", [
            "hash" => $archivo["hash"],
            "box" => $archivo["box"],
            "station" => $archivo["station"],
            "filename" => $archivo["filename"],
            "timestamp" => $archivo["timestamp"]
          ])
          ->execute();
        $this->stdout("{$archivo["hash"]} {$archivo["station"]} {$archivo["box"]} {$archivo["timestamp"]}\n");
      } catch(\Exception $e) {
        $this->stdout("{$e->getMessage()}\n");
      }
    }

    $tiempo = time() - $inicio;

    $this->stdout("Proceso terminado en: {$tiempo}\n");
  }

}