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

<?php

namespace App\Console\Commands\Trade;

use App\Modules\Trade\Models\NewUserChargeDayStatistic;
use App\Modules\Trade\Models\Order;

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

class GenNewUserChargeDayStatistic extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'new_user_charge_day_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);

        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 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 charge_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,
            DATE_FORMAT(orders.created_at, '%Y-%m-%d') as pay_date,
            count(distinct uid) as paid_user_num,
            count(orders.id) as paid_num,
            count(orders.pay_type = 1 or null) as first_order_users
            ")
            ->groupBy('channel_id', 'register_date')
            ->get();
    }

    /**
     * 保存统计数据
     * @param string $date 统计日期
     * @param Collection $collect 统计数据
     * @param int $day 第几天
     */
    private function saveStatistic($item)
    {

        try{
            //DB::connection('new_yunqi')->table('new_user_charge_day_statistic')->updateOrInsert(
            NewUserChargeDayStatistic::updateOrCreate(
                [
                    'channel_id'        => $item->channel_id,
                    'date'              => $item->register_date,
                    'day_num'           => $item->diff_day,
                ],
                [
                    'pay_date'          => $item->pay_date,
                    'amount'            => $item->charge_amount,
                    'users'             => $item->paid_user_num,
                    'count'             => $item->paid_num,
                    'first_order_users' => $item->first_order_users//首单人数
                ]
            );
        }catch (\Exception $e){
            //
        }

    }


}