/** * Getting exchanges in banks for selected period * @param \DateTime $today * @param type $period * @return array */ function getBankDays(\DateTime $today = null, $period = -10) { if (empty($today)) { $today = $this->assignActualDate(); } $table_exchanges = models\ExchangeRate::tableName(); $banks_table = models\Bank::tableName(); $today->modify($period . ' day'); $days = array(); for ($i = 0; $i < abs($period); ++$i) { $today->modify("+1 day"); $yesterday = clone $today; $yesterday->modify('-1 day'); $today_string = $today->format(self::DATE_FORMAT); $query = "\n\n SELECT\n\n q1.*,\n\n AVG(dollar_buy) as dollar_buy_yesterday,\n AVG(dollar_sale) as dollar_sale_yesterday,\n (AVG(q1.dollar_buy_today) - AVG(dollar_buy)) as dollar_buy_diff,\n (AVG(q1.dollar_sale_today) - AVG(dollar_sale)) as dollar_sale_diff,\n\n AVG(euro_buy) as euro_buy_yesterday,\n AVG(euro_sale) as euro_sale_yesterday,\n (AVG(q1.euro_buy_today) - AVG(euro_buy)) as euro_buy_diff,\n (AVG(q1.euro_sale_today)- AVG(euro_sale)) as euro_sale_diff\n\n FROM {$table_exchanges} as exchanges, (\n\n SELECT bank_id, \n AVG(dollar_buy) as dollar_buy_today, AVG(dollar_sale) as dollar_sale_today,\n AVG(euro_buy) as euro_buy_today, AVG(euro_sale) as euro_sale_today\n FROM {$table_exchanges} as exchanges\n JOIN {$banks_table} as banks ON banks.id = exchanges.bank_id\n WHERE `grab_date` = '{$today_string}'\n AND banks.id = exchanges.bank_id\n\n GROUP BY bank_id\n\n ) as q1\n\n WHERE q1.bank_id = exchanges.bank_id\n AND grab_date = '" . $yesterday->format(self::DATE_FORMAT) . "'\n\n GROUP BY bank_id\n\n "; $command = Yii::$app->db->createCommand($query); $res = $command->queryAll(); if (!empty($res)) { foreach ($res as $row) { $day = ['dollar_buy' => ['value' => $row['dollar_buy_today'], 'diff' => $row['dollar_buy_diff']], 'dollar_sale' => ['value' => $row['dollar_sale_today'], 'diff' => $row['dollar_sale_diff']], 'euro_buy' => ['value' => $row['euro_buy_today'], 'diff' => $row['euro_buy_diff']], 'euro_sale' => ['value' => $row['euro_sale_today'], 'diff' => $row['euro_sale_diff']]]; $days[$today->format(self::DATE_FORMAT)][$row['bank_id']] = $day; } } } return $days; }