yc_douyin_capi/app/Console/DyReport/SendOrderDayStatsCommand.php

<?php
namespace App\Console\DyReport;

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

class SendOrderDayStatsCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'SendOrderDayStats {--day=} {--id=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日0点统计昨日各派单链接相关数据';

    /**
     * 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);
        $date = $this->option('day');
        $send_order_id = $this->option('id');
        if ($date) {        // 指定日期
            $day = $date;
            $start = date($date.' 00:00:00');
            $end = date($date.' 23:59:59');
        }else {             // 未指定日期则默认昨日
            $day = date('Y-m-d', strtotime('-1 day'));
            $start = date('Y-m-d 00:00:00', strtotime('-1 day'));
            $end = date('Y-m-d 23:59:59', strtotime('-1 day'));
        }

        $query= DB::table('send_orders')->where('is_enable', '1')->where('created_at', '<', $end);

        if ($send_order_id) {
            $query->where('id', $send_order_id);
        }
        $send_orders = $query->select('id', 'uv', 'cost', 'total_register_pay_amount', 'total_profit', 'total_register_num', 'total_register_pay_num')
            ->get()->map(function ($value) {
            return (array)$value;
        })->toArray();

        foreach ($send_orders as $send_order) {
            $send_order_id = getProp($send_order, 'id');
            // 获取指定日期成本
            $cost = DB::table('send_order_day_stats')->where('send_order_id', $send_order_id)->where('day', $day)->value('cost');

            // 新增注册人数
            $register_num = DB::table('users')->where('send_order_id', $send_order_id)->whereBetween('created_at', [$start, $end])->count('id');
            // 注册充值人数(只计算首充)
            $register_pay_num = DB::table('orders')->leftJoin('users', 'orders.uid', 'users.id')->where('users.send_order_id', $send_order_id)
                ->where('orders.send_order_id', $send_order_id)->whereBetween('users.created_at', [$start, $end])
                ->where('orders.status', 'PAID')->whereBetween('orders.created_at', [$start, $end])->where('orders.pay_num', 1)->count('orders.id');
            // 注册用户充值金额(包含多充)
            $register_pay_amount = DB::table('users')->leftJoin('orders', 'orders.uid', 'users.id')->where('users.send_order_id', $send_order_id)
                ->where('orders.send_order_id', $send_order_id)->whereBetween('users.created_at', [$start, $end])
                ->where('orders.status', 'PAID')->whereBetween('orders.created_at', [$start, $end])->sum('orders.price');

            // 获取日数据汇总(指定日期之前的汇总)
            $total_data = DB::table('send_order_day_stats')->where('send_order_id', $send_order_id)
                ->where('day', '<', $day)->select(
                DB::raw('sum(cost) as total_cost'),
                DB::raw('sum(register_num) as total_register_num'),
                DB::raw('sum(register_pay_amount) as total_register_pay_amount'),
                DB::raw('sum(register_pay_num) as total_register_pay_num'),
            )->first();

            // 累计成本
            $total_cost = $cost + getProp($total_data, 'total_cost', 0);
            // 累计注册人数
            $total_register_num = $register_num + getProp($total_data, 'total_register_num', 0);
            // 累计充值金额(包含多充)
            $total_register_pay_amount = $register_pay_amount + getProp($total_data, 'total_register_pay_amount', 0);
            // 累计充值人数(只计算首充)
            $total_register_pay_num = $register_pay_num + getProp($total_data, 'total_register_pay_num', 0);
            // 累计盈利
            $total_profit = $total_register_pay_amount - $total_cost;

            $data = [
                'send_order_id'                 => $send_order_id,
                'day'                           => $day,
                'register_num'                  => $register_num,
                'register_pay_num'              => $register_pay_num,
                'register_pay_amount'           => $register_pay_amount,
//                'total_cost'                    => $total_cost,
//                'total_profit'                  => $total_profit,
//                'total_register_num'            => $total_register_num,
//                'total_register_pay_num'        => $total_register_pay_num,
//                'total_register_pay_amount'     => $total_register_pay_amount,
                'created_at'                    => date('Y-m-d H:i:s'),
                'updated_at'                    => date('Y-m-d H:i:s'),
            ];

            // 更新派单数据日表
            $boolen = DB::table('send_order_day_stats')->updateOrInsert([
                'send_order_id'     => $send_order_id,
                'day'               => $day,
            ], $data);
            if (!$boolen) {
                dLog('command_logs')->info('派单链接日表统计失败: ', $data);
            }

            // 获取uv数据
            $uv = Redis::scard('send_order_uv_'.$send_order_id);
            $uv = $uv ? $uv : 0;

            $total_data = [
                'uv'                            => $uv,
                'cost'                          => $total_cost,
                'total_register_pay_amount'     => $total_register_pay_amount,
                'total_profit'                  => $total_profit,
                'total_register_num'            => $total_register_num,
                'total_register_pay_num'        => $total_register_pay_num,
                'updated_at'                    => date('Y-m-d H:i:s')
            ];

            // 更新派单数据总表
            $boolen1 = DB::table('send_orders')->where('id', $send_order_id)->update($total_data);
            if (!$boolen1) {
                $total_data['send_order_id'] = $send_order_id;
                dLog('command_logs')->info('派单链接总表统计失败: ', $total_data);
            }
        }

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