wangdu_public_api/app/Console/Commands/Trade/NewUserStatistic.php

<?php

namespace App\Console\Commands\Trade;

use App\Modules\Trade\Models\NewUserChargeStatistic;
use App\Modules\Trade\Models\Order;
use App\Modules\User\Models\User;
use Illuminate\Console\Command;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use stdClass;

class NewUserStatistic extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'new_user_charge_statistic  {--begin_date=} {--end_date=} {--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()
    {
        print_r("======新注册用户充值统计 【任务执行开始】=====" . date("y-m-d H:i:s" . "\n"));
        Log::info("======新注册用户充值统计 【任务执行开始】=====" . date("y-m-d H:i:s" . "\n"));

        ini_set('memory_limit', '256M');
        $begin_date = $this->option('begin_date');
        $end_date = $this->option('end_date');
        if ($begin_date) {
            $date = $begin_date;
            while (strtotime($date) < strtotime($end_date)) {
                Log::info("======新注册用户充值统计 【任务执行中】===== 日期：" . $date . ' ' . date("y-m-d H:i:s" . "\n"));
                print_r("======新注册用户充值统计 【任务执行中】===== 日期：" . $date . ' ' . date("y-m-d H:i:s" . "\n"));
                $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);
        }
        print_r("======新注册用户充值统计 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
        Log::info("======新注册用户充值统计 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n"));
    }

    /**
     * 新用户数据统计
     * @param string $date 统计日期
     */
    public function statistic(string $date)
    {
        $statistic_info = $this->runSql($date);
        $collections = collect($statistic_info);
        $diff_days = [0, 1, 2, 3, 4, 5, 6];
        $days_items = $collections->whereIn('diff_day', $diff_days)->all();
        foreach ($days_items as $item) {
            $this->saveStatistic($item, $item->register_date, $item->diff_day);
        }
        $acc_days = [7, 14, 29];
        foreach ($acc_days as $day) {
            $this->saveStatisticAccumulate($collections, $day);
        }
    }

    public function runSql(string $date)
    {
        $end_date =  date('Y-m-d', strtotime('+1 days', strtotime($date)));
        return Order::join('users', 'orders.uid', 'users.id')
            ->where('orders.created_at', '>=', $date)
            ->where('orders.created_at', '<', $end_date)
            ->where('status', 'PAID')
            ->selectRaw("orders.distribution_channel_id as channel_id,sum(price) as amount,
            timestampdiff(day,DATE_FORMAT(users.created_at, '%Y-%m-%d'),DATE_FORMAT(orders.created_at, '%Y-%m-%d')) as diff_day,
            DATE_FORMAT(users.created_at, '%Y-%m-%d') as register_date,
            count(distinct uid) as users,count(orders.id) as count")
            ->groupBy('channel_id', 'register_date')
            ->get();
    }

    /**
     * 保存统计数据
     * @param string $date 统计日期
     * @param Collection $collect 统计数据
     * @param int $day 第几天
     */
    private function saveStatistic($item, string $date, int $day)
    {
        $params = [];
        switch ($day) {
            case 0:
                $params = [
                    'first_day_count' => $item->count,
                    'first_day_amount' => $item->amount,
                    'first_day_users' => $item->users,
                ];
                break;
            case 1:
                $params = [
                    'second_day_count' => $item->count,
                    'second_day_amount' => $item->amount,
                    'second_day_users' => $item->users,
                ];
                break;
            case 2:
                $params = [
                    'third_day_count' => $item->count,
                    'third_day_amount' => $item->amount,
                    'third_day_users' => $item->users,
                ];
                break;
            case 3:
                $params = [
                    'forth_day_count' => $item->count,
                    'forth_day_amount' => $item->amount,
                    'forth_day_users' => $item->users,
                ];
                break;
            case 4:
                $params = [
                    'fifth_day_count' => $item->count,
                    'fifth_day_amount' => $item->amount,
                    'fifth_day_users' => $item->users,
                ];
                break;
            case 5:
                $params = [
                    'sixth_day_count' => $item->count,
                    'sixth_day_amount' => $item->amount,
                    'sixth_day_users' => $item->users,
                ];
                break;
            case 6:
                $params = [
                    'seventh_day_count' => $item->count,
                    'seventh_day_amount' => $item->amount,
                    'seventh_day_users' => $item->users,
                ];
                break;
        }
        NewUserChargeStatistic::updateOrCreate(
            [
                'date' => $date,
                'channel_id' => $item->channel_id,
            ],
            $params
        );
    }

    /**
     * 保存累计充值统计数据（不能重复跑）
     */
    private function saveStatisticAccumulate(Collection $collections, int $day)
    {
        switch ($day) {
            case 7:
                $items = $collections->where('diff_day', '>=', 7)->where('diff_day', '<', 30);
                break;
            case 14:
            case 29:
                $items = $collections->where('diff_day', '<=', $day);
                break;
        }
        switch ($day) {
            case 7:
                $columns = [
                    'count',
                    'amount',
                    'users',
                ];
                break;
            case 14:
                $columns = [
                    '15_day_count',
                    '15_day_amount',
                    '15_day_users',
                ];
                break;
            case 29:
                $columns = [
                    '30_day_count',
                    '30_day_amount',
                    '30_day_users',
                ];
                break;
        }
        $items->each(function ($item) use ($columns) {
            $info = NewUserChargeStatistic::where(
                [
                    'date' => $item->register_date,
                    'channel_id' => $item->channel_id,
                ]
            )->select(
                'id',
                'date',
                'channel_id',
                $columns[0],
                $columns[1],
                $columns[2]
            )->first();
            if ($info) {
                $info[$columns[0]] += $item->count;
                $info[$columns[1]] += $item->amount;
                $info[$columns[2]] += $item->users;
                $info->save();
            } else {
                $data = [
                    'date' => $item->register_date,
                    'channel_id' => $item->channel_id,
                ];
                $data[$columns[0]] = $item->count;
                $data[$columns[1]] = $item->amount;
                $data[$columns[2]] = $item->users;
                NewUserChargeStatistic::create($data);
            }
        });
    }
}