yc_douyin_capi/app/Console/DyReport/SendOrderRechargeDayStatsCommand.php

<?php
namespace App\Console\DyReport;

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

class SendOrderRechargeDayStatsCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'SendOrderRechargeDayStats {--date=} {--begin_date=} {--end_date=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '注册用户派单数据日统计';

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

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        dLog('command_logs')->info('~~~~~~~~~~~~~~~~~~~~~~~~~~开始统计注册用户派单日数据~~~~~~~~~~~~~~~~~~~~~~~~~~');
        $executeStart = microtime(true);
        $begin_date = $this->option('begin_date');
        $end_date = $this->option('end_date');
        if ($begin_date) {
            $date = $begin_date;
            if (!$end_date) {   // 未传截止日期则默认截止到今天0点
                $end_date = date('Y-m-d');
            }else {
                if (strtotime($end_date) > strtotime(date('Y-m-d'))) {  // 截止日期如果超过今天0点,则将截止日期重置为今天0点
                    $end_date = date('Y-m-d');
                }
            }
            while (strtotime($date) < strtotime($end_date)) {
                dLog('command_logs')->info("【任务执行中】~~~~~~ 日期：" . $date);
                $this->statistic($date);
                $date = date('Y-m-d', strtotime('+1 days', strtotime($date)));
            }
        } else {
            $date = $this->option('date');
            $date = $date ? $date : date('Y-m-d', strtotime('-1 days'));
            $this->statistic($date);
        }

        $executeEnd = microtime(true);
        dLog('command_logs')->info('脚本运行时间: ', ['execute_time'=>round(($executeEnd - $executeStart), 6).'s']);
        dLog('command_logs')->info('~~~~~~~~~~~~~~~~~~~~~~~~~~结束统计注册用户派单日数据~~~~~~~~~~~~~~~~~~~~~~~~~~');
    }

    /**
     * 新用户数据统计
     * @param string $date 统计日期
     */
    public function statistic(string $date)
    {
        $statistic_info = $this->runSql($date);

        foreach ($statistic_info as $item) {
            $this->saveStatistic($item);
        }
    }

    public function runSql(string $date)
    {
        $end_date =  date('Y-m-d', strtotime('+1 days', strtotime($date)));
        return DB::connection('mysql::write')->table('orders')->leftJoin('users', 'orders.uid', 'users.id')
            ->where('orders.created_at', '>=', $date)
            ->where('orders.created_at', '<', $end_date)
            ->where('orders.status', 'PAID')
            ->selectRaw("orders.send_order_id,DATE_FORMAT(users.created_at, '%Y-%m-%d') as register_date,
            timestampdiff(day,DATE_FORMAT(users.created_at, '%Y-%m-%d'),DATE_FORMAT(orders.created_at, '%Y-%m-%d')) as diff_day,
            DATE_FORMAT(orders.created_at, '%Y-%m-%d') as pay_date,
            sum(orders.price) as pay_amount,
            count(distinct orders.uid) as pay_num,
            count(orders.id) as order_num,
            count(orders.pay_num = 1 or null) as first_pay_num")
            ->groupBy('send_order_id', 'register_date')
            ->get()->map(function ($value) {
                return (array)$value;
            })->toArray();
    }

    private function saveStatistic($item)
    {

        try{
            DB::table('send_order_recharge_day_stats')->updateOrInsert(
                [
                    'send_order_id'     => $item['send_order_id'],  // 派单id
                    'register_date'     => $item['register_date'],  // 注册日期
                    'day_num'           => $item['diff_day'],       // 第几天
                ],
                [
                    'pay_date'          => $item['pay_date'],       // 支付日期
                    'pay_amount'        => $item['pay_amount'],     // 支付金额
                    'pay_num'           => $item['pay_num'],        // 支付人数
                    'order_num'         => $item['order_num'],      // 订单数
                    'first_pay_num'     => $item['first_pay_num'],  //首单人数
                    'created_at'        => date('Y-m-d H:i:s'),
                    'updated_at'        => date('Y-m-d H:i:s'),
                ]
            );
        }catch (\Exception $e){
            dLog('command_logs')->info("【任务执行失败】~~~~~~ 错误信息：" . $e->getMessage());
        }

    }
}