qimall/console/controllers/CommissionMonthBillingController.php

<?php

namespace console\controllers;


use common\enums\StatusEnum;
use common\helpers\DateHelper;
use common\models\common\CommissionMonthBilling;
use common\models\user\User;
use yii\console\Controller;
use yii\console\ExitCode;


class CommissionMonthBillingController extends Controller
{
    //初始化
    public function actionInit()
    {
        $day = date('d');
        if($day!='01') return false;
        $i = 1;
        $page_size = 1000;
        $res = DateHelper::lastMonth();
        $start = $res['start'];
        $end = $res['end'];
        while ($user_list = User::find()
            ->andWhere(['status' => [StatusEnum::ENABLED, StatusEnum::DISABLED]])
            ->select('id')
            ->offset(($i - 1) * $page_size)
            ->orderBy('id asc')
            ->limit($page_size)->all()) {
            $user_ids = array_column($user_list, 'id');
            $user_id_str = implode(',', $user_ids);
            $sql = "SELECT FROM_UNIXTIME(created_at,'%Y-%m') as month,mall_id,user_id,change_type,sum(change_num) as SUM3,count(*) as COUNT4
            FROM qimall_commission_log where created_at <='{$end}' and created_at>'{$start}' and user_id in({$user_id_str}) GROUP BY mall_id,user_id,change_type,month";
            $list = \Yii::$app->db->createCommand($sql)->queryAll();
            foreach ($list as $item) {
                CommissionMonthBilling::setData($item);
            }
            $i++;
        }

        return ExitCode::OK;

    }

    //统计每个月的
    public function actionIndex()
    {
        try {
            $month = DateHelper::lastMonth();
            $day = date('d');
            $start_time = $month['start'];
            $end_time = strtotime(date("Y-m-d", strtotime(-$day . ' day')) . ' 23:59:59');
            $month = date('Y-m', $end_time);
            $i = 1;
            $page_size = 1000;
            while ($user_list = User::find()
                ->andWhere(['status' => [StatusEnum::ENABLED, StatusEnum::DISABLED]])
                ->select('id')
                ->offset(($i - 1) * $page_size)
                ->orderBy('id asc')
                ->limit($page_size)->all()) {
                $user_ids = array_column($user_list, 'id');
                $user_id_str = implode(',', $user_ids);
                $sql = "SELECT mall_id,user_id,change_type,sum(change_num) as SUM3,count(*) as COUNT4
            FROM qimall_commission_log where created_at <={$end_time} and created_at>={$start_time} and user_id in({$user_id_str}) GROUP BY mall_id,user_id,change_type";
                $list = \Yii::$app->db->createCommand($sql)->queryAll();
                foreach ($list as $item) {
                    $item['month'] = $month;
                    CommissionMonthBilling::setData($item);
                }
                $i++;
            }

            return ExitCode::OK;
        } catch (\Exception $e) {
            \Yii::error('CommissionMonthBillingController:actionIndex', $e->getMessage() . $e->getFile() . $e->getLine());
            return ExitCode::UNSPECIFIED_ERROR;
        }
    }


}