qimall/console/controllers/CommissionYearsBillingController.php

<?php

namespace console\controllers;


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


class CommissionYearsBillingController extends Controller
{
    //初始化
    public function actionInit()
    {
        $s = date('m-d');
        if($s!='01-01') return false;
        $res = DateHelper::lastYear();
        $start = $res['start'];
        $end = $res['end'];
        $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 FROM_UNIXTIME(created_at,'%Y') as years,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,years";
            $list = \Yii::$app->db->createCommand($sql)->queryAll();
            foreach ($list as $item) {
                CommissionYearsBilling::setData($item);
            }
            $i++;
        }

        return ExitCode::OK;

    }

    //统计每年
    public function actionIndex()
    {
        try {
            $y = date('Y', strtotime("-1 year"));
            $start_time = strtotime(date($y . '-01-01 00:00:00'));
            $end_time = strtotime(date($y . '-12-31 23:59:59'));
            $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['years'] = $y;
                    CommissionYearsBilling::setData($item);
                }
                $i++;
            }

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

        }
    }


}