Created
February 9, 2023 09:01
-
-
Save denrad/cbfb5dc15dc2d3acfe768a2dcf2df045 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| namespace console\models; | |
| use common\active_records\{ | |
| AggregationConversions, | |
| Currency, | |
| Merchant, | |
| PayinTransitPackage, | |
| PaymentSystem, | |
| Transaction | |
| }; | |
| use DateTime; | |
| use yii\db\{Exception, Expression, Query}; | |
| use yii\base\Model; | |
| class ConversionAggregationModel extends Model | |
| { | |
| private const DATETIME_FORMAT = 'Y-m-d H:00:00'; | |
| public string $dateFrom; | |
| public string $dateTo; | |
| public function rules(): array | |
| { | |
| return [ | |
| [['dateFrom', 'dateTo'], 'required'], | |
| [['dateFrom', 'dateTo'], 'date', 'format' => 'php:Y-m-d H:i:s'], | |
| [['dateFrom'], 'compare', 'compareAttribute' => 'dateTo', 'operator' => '<='], | |
| ]; | |
| } | |
| protected function getPeriods(DateTime $dateFrom, DateTime $dateTo): \Generator | |
| { | |
| while ($dateFrom <= $dateTo) { | |
| yield $dateFrom->format(self::DATETIME_FORMAT); | |
| $dateFrom->modify('+1 hour'); | |
| } | |
| } | |
| /** | |
| * @throws Exception | |
| */ | |
| public function aggregate(): void | |
| { | |
| $dateFrom = (new DateTime('now')) | |
| ->modify('-1 hour') | |
| ->format(self::DATETIME_FORMAT); | |
| $dateTo = (new DateTime('now')) | |
| ->format(self::DATETIME_FORMAT); | |
| $query = (new Query()) | |
| ->select([ | |
| 'merchant_id' => 'm.id', | |
| 'payment_system_id' => 'ps.id', | |
| 'currency_code' => 'c.code', | |
| 'paid_transactions' => new Expression('COUNT(CASE t.status WHEN "paid" THEN 1 END)'), | |
| 'all_transactions' => new Expression('COUNT(*)'), | |
| 'date' => new Expression('NOW()'), | |
| ]) | |
| ->from(['t' => Transaction::tableName()]) | |
| ->innerJoin(['ptp' => PayinTransitPackage::tableName()], 't.id = ptp.transaction_id') | |
| ->leftJoin(['c' => Currency::tableName()], 't.currency_id = c.id') | |
| ->leftJoin(['m' => Merchant::tableName()], 't.merchant_id = m.id') | |
| ->leftJoin(['ps' => PaymentSystem::tableName()], 'ptp.payment_system_id = ps.id') | |
| ->where(['t.in_sandbox' => false]) | |
| ->andWhere([ | |
| 'or', | |
| [ | |
| 'and', | |
| [ | |
| '>=', | |
| 't.time_create', | |
| $dateFrom | |
| ], | |
| [ | |
| '<', | |
| 't.time_create', | |
| $dateTo | |
| ], | |
| ], | |
| [ | |
| 'and', | |
| [ | |
| '>=', | |
| 't.time_result', | |
| $dateFrom | |
| ], | |
| [ | |
| '<', | |
| 't.time_result', | |
| $dateTo | |
| ], | |
| ], | |
| ]) | |
| ->groupBy([ | |
| 'currency_code', | |
| 'merchant_id', | |
| 'payment_system_id', | |
| ]); | |
| $query->createCommand()->insert(AggregationConversions::tableName(), $query)->execute(); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment