yc_douyin_capi/app/Console/Channel/ChannelDayStatistics.php

<?php

namespace App\Console\Channel;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class ChannelDayStatistics extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'channelDayStatistics {--channel_id=} {--day=} {--start_day=} {--end_day=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '站点统计日数据';

    private $channelUserService;

    public function __construct(
    )
    {
        parent::__construct();
    }

    public function handle()
    {
        // 获取全部站点
        $distribution_channel_ids = DB::table('distribution_channels')->orderBy('id')->select('id')->get()->pluck('id')->toArray();
        // 传参
        $option_day = trim($this->option('day'));
        if (!$option_day) {
            $day = date('Y-m-d', strtotime('-1 day'));
        }else {
            $day = $option_day;
        }
        $channel_id = trim($this->option('channel_id'));
        $start_day = trim($this->option('start_day'));
        $end_day = trim($this->option('end_day'));

        if ($channel_id) {
            if (!$start_day && !$option_day) {
                // 指定站点按站点创建日期开始统计
                $created_at = DB::table('distribution_channels')->where('id', $channel_id)->value('created_at');
                if (!$created_at) dd('该站点不存在');
                $start_day = transDate($created_at, 'Y-m-d');
            }
            $distribution_channel_ids = [$channel_id];
        }

        if ($start_day) {       // 指定开始日期统计
            if (!$end_day) {    // 未指定结束日期则默认截止到昨天
                $end_day = date('Y-m-d', strtotime('-1 day'));
            }
            $day = $start_day;
            while (true) {
                if (strtotime($day) > strtotime($end_day)) break;
                $this->runStatistics($day, $distribution_channel_ids);
                $day = date('Y-m-d', strtotime($day.' +1 day'));
            }
        }else {
            $this->runStatistics($day, $distribution_channel_ids);
        }
    }

    // 执行统计
    private function runStatistics($day, $distribution_channel_ids) {
        dLog('command_logs')->info('~~~~~~~~~~~~~~~~~~~~~~~~~~开始统计站点('.$day.')数据~~~~~~~~~~~~~~~~~~~~~~~~~~');
        $executeStart = microtime(true);
        $month = date('Ym', strtotime($day));
        $day_start = $day.' 00:00:00';
        $day_end = $day.' 23:59:59';

        foreach ($distribution_channel_ids as $channel_id) {
            $total_order_num = DB::table('orders')->where('distribution_channel_id', $channel_id)->whereBetween('created_at', [$day_start, $day_end])->count('id');
            $paid_order = DB::table('orders')->where('distribution_channel_id', $channel_id)->whereBetween('created_at', [$day_start, $day_end])->where('status', 'PAID')
                ->selectRaw("count(id) as paid_order_num, count(distinct uid) as total_pay_num, sum(price) as total_pay_amount")->first();
            $paid_order_num = getProp($paid_order, 'paid_order_num', 0);
            $total_pay_num = getProp($paid_order, 'total_pay_num', 0);
            $total_pay_amount = getProp($paid_order, 'total_pay_amount', 0);

            $register_uids = DB::table('users')->where('distribution_channel_id', $channel_id)->whereBetween('created_at', [$day_start, $day_end])->select('id')->get()->pluck('id')->toArray();
            $register_orders = DB::table('orders')->where('distribution_channel_id', $channel_id)->whereBetween('created_at', [$day_start, $day_end])
                ->whereIn('uid', $register_uids)->where('status', 'PAID')
                ->selectRaw('count(distinct uid) as register_pay_num, sum(price) as register_pay_amount')->first();
            $register_num = count($register_uids);
            $register_pay_num = getProp($register_orders, 'register_pay_num', 0);
            $register_pay_amount = getProp($register_orders, 'register_pay_amount', 0);
            $statistics = [
                'distribution_channel_id'   => $channel_id,
                'day'                       => $day,
                'month'                     => $month,
                'total_order_num'           => $total_order_num,
                'paid_order_num'            => $paid_order_num,
                'register_num'              => $register_num,
                'register_pay_num'          => $register_pay_num,
                'register_pay_amount'       => $register_pay_amount,
                'total_pay_num'             => $total_pay_num,
                'total_pay_amount'          => $total_pay_amount,
                'created_at'                => date('Y-m-d H:i:s'),
                'updated_at'                => date('Y-m-d H:i:s'),
            ];

            $boolen = DB::table('channel_day_statistics')->updateOrInsert([
                'distribution_channel_id'   => $channel_id,
                'day'                       => $day,
            ], $statistics);
            if (!$boolen) {
                Log::info('站点日统计失败, 失败记录: '.json_encode($statistics, 256));
            }
            $executeEnd = microtime(true);
            dLog('command_logs')->info('站点('.$channel_id.')--'.$day.'执行时间: ', ['execute_time'=>round(($executeEnd - $executeStart), 6).'s']);
        }
        $executeEnd = microtime(true);
        dLog('command_logs')->info('脚本运行时间: ', ['execute_time'=>round(($executeEnd - $executeStart), 6).'s']);
        dLog('command_logs')->info('~~~~~~~~~~~~~~~~~~~~~~~~~~结束统计站点('.$day.')数据~~~~~~~~~~~~~~~~~~~~~~~~~~');
    }
}