kwaiAD/app/Console/Commands/DailyCostDat...

<?php

namespace App\Console\Commands;

use App\Models\JxCostDailyData;
use App\Models\JxQtaskLiveOrderList;
use App\Models\JxQtaskVideoFlowOrderList;
use App\Models\JxQtaskVideoOrderList;
use App\Models\JxStarLiveFlowOrderList;
use App\Models\JxStarLiveOrderList;
use App\Models\JxStarVideoFlowOrderList;
use App\Models\JxStarVideoOrderList;
use App\Models\Sys\SysCustomerAdver;
use App\Support\Log;
use Illuminate\Console\Command;

class DailyCostDataStat extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'DailyCostDataStat';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日消耗数据统计';
    protected $runDays     = 95;

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->info(date('H:i') . ' 开始执行');
        $this->start();
        $this->info(date('H:i') . ' 结束执行');

        return 0;
    }

    public function start()
    {
        $stDate = date('Y-m-d', strtotime("-{$this->runDays} day"));
        $enDate = date('Y-m-d', strtotime("-1 day"));

        $starVideoCostList = self::getDailyCostData(
            JxStarVideoOrderList::query(), 'star_release_time', 'real_amount', $stDate, $enDate
        );

        # 星直播
        $starLiveCostList = self::getDailyCostData(
            JxStarLiveOrderList::query(), 'live_start_time', 'real_amount', $stDate, $enDate
        );

        # 星任务-视频
        $qtaskVideoCostList = self::getDailyCostData(
            JxQtaskVideoOrderList::query(), 'star_release_time', 'amount', $stDate, $enDate
        );

        # 星任务-直播
        $qtaskLiveCostList = self::getDailyCostData(
            JxQtaskLiveOrderList::query(), 'star_release_time', 'amount', $stDate, $enDate
        );

        # 流量助推-星视频
        $starVideoFlowCostList = self::getDailyCostData(
            JxStarVideoFlowOrderList::query(), 'promotion_begin_time', 'consume_amount', $stDate, $enDate
        );

        # 流量助推-星直播
        $starLiveFlowCostList = self::getDailyCostData(
            JxStarLiveFlowOrderList::query(), 'promotion_begin_time', 'consume_amount', $stDate, $enDate
        );

        # 流量助推-星任务视频
        $qtaskVideoFlowCostList = self::getDailyCostData(
            JxQtaskVideoFlowOrderList::query(), 'promotion_begin_time', 'consume_amount', $stDate, $enDate
        );

        # 处理数据
        $advertiserIds = [];
        $dailyCostData = [];
        array_map(function (
            $starVideoCost, $starLiveCost, $qtaskVideoCost, $qtaskLiveCost,
            $starVideoFlowCost, $starLiveFlowCost, $qtaskVideoFlowCost
        ) use (&$advertiserIds, &$dailyCostData) {
            if (!empty($starVideoCost)) $this->dealDailyCostData($starVideoCost, 'star_video_cost', $advertiserIds, $dailyCostData);
            if (!empty($starLiveCost)) $this->dealDailyCostData($starLiveCost, 'star_live_cost', $advertiserIds, $dailyCostData);
            if (!empty($qtaskVideoCost)) $this->dealDailyCostData($qtaskVideoCost, 'qtask_video_cost', $advertiserIds, $dailyCostData);
            if (!empty($qtaskLiveCost)) $this->dealDailyCostData($qtaskLiveCost, 'qtask_live_cost', $advertiserIds, $dailyCostData);
            if (!empty($starVideoFlowCost)) $this->dealDailyCostData($starVideoFlowCost, 'flow_star_video_cost', $advertiserIds, $dailyCostData);
            if (!empty($starLiveFlowCost)) $this->dealDailyCostData($starLiveFlowCost, 'flow_star_live_cost', $advertiserIds, $dailyCostData);
            if (!empty($qtaskVideoFlowCost)) $this->dealDailyCostData($qtaskVideoFlowCost, 'flow_qtask_video_cost', $advertiserIds, $dailyCostData);
        },
            $starVideoCostList, $starLiveCostList, $qtaskVideoCostList, $qtaskLiveCostList,
            $starVideoFlowCostList, $starLiveFlowCostList, $qtaskVideoFlowCostList
        );

        # 数据入库
        if (!empty($dailyCostData)) {
            $customerAdverMap = SysCustomerAdver::query()
                ->whereIn('advertiser_id', $advertiserIds)
                ->where('enable', 1)
                ->pluck('customer_id', 'advertiser_id');

            ksort($dailyCostData);
            $this->indbDailyCostDate($customerAdverMap, $dailyCostData);
        }

    }

    protected function getDailyCostData($query, $timeField, $sumField, $stDate, $enDate)
    {
        $list =  $query
            ->selectRaw("advertiser_id, DATE_FORMAT({$timeField}, '%Y-%m-%d') AS day")
            ->selectRaw("SUM({$sumField}) AS cost")
            ->whereBetween($timeField, [$stDate.' 00:00:00', $enDate.' 23:59:59'])
            ->where('enable', 1)
            ->groupBy(['advertiser_id', 'day'])
            ->get();

        if ($list->isEmpty()) return [];

        return $list->toArray();
    }

    protected function dealDailyCostData($costData, $costField, &$advertiserIds, &$dailyCostData)
    {
        if (!in_array($costData['advertiser_id'], $advertiserIds)) $advertiserIds[] = $costData['advertiser_id'];

        if (!isset($dailyCostData[$costData['day']][$costData['advertiser_id']][$costField])) {
            $dailyCostData[$costData['day']][$costData['advertiser_id']][$costField] = $costData['cost'];
        } else {
            $dailyCostData[$costData['day']][$costData['advertiser_id']][$costField] += $costData['cost'];
        }
    }

    protected function indbDailyCostDate($customerAdverMap, $dailyCostData)
    {
        foreach ($dailyCostData as $day => $advData) {
            foreach ($advData as $advertiserId => $datum) {
                JxCostDailyData::query()
                    ->updateOrInsert([
                        'day'           => $day,
                        'advertiser_id' => $advertiserId
                    ], [
                        'customer_id'           => $customerAdverMap->get($advertiserId) ?? null,
                        'star_video_cost'       => $datum['star_video_cost'] ?? 0,
                        'star_live_cost'        => $datum['star_live_cost'] ?? 0,
                        'qtask_video_cost'      => $datum['qtask_video_cost'] ?? 0,
                        'qtask_live_cost'       => $datum['qtask_live_cost'] ?? 0,
                        'flow_star_video_cost'  => $datum['flow_star_video_cost'] ?? 0,
                        'flow_star_live_cost'   => $datum['flow_star_live_cost'] ?? 0,
                        'flow_qtask_video_cost' => $datum['flow_qtask_video_cost'] ?? 0,
                    ]);

//                Log::info('每日消耗数据离线更新', ['day' => $day, 'advertiser_id' => $advertiserId], 'DailyCostDataStat');
            }
        }
    }

}