duanju_manage/app/Console/Commands/Statistic/OptimizerMonthCharge.php

<?php

namespace App\Console\Commands\Statistic;

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

class OptimizerMonthCharge extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'Statistic:OptimizerMonthCharge {--month= : 统计月份}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '优化师充值月统计';

    /**
     * Execute the console command.
     */
    public function handle(): void
    {
        //tj_optimizer_month_charge
        $month = $this->option('month');
        $monthStart = $month. '-01';
        $montEnd = date_sub(date_add(date_create($monthStart),
            date_interval_create_from_date_string('1 month')),
            date_interval_create_from_date_string('1 day'))->format('Y-m-d');

        $result = DB::table('tj_optimizer_day_charge')
            ->whereBetween('day_at', [$monthStart, $montEnd])
            ->select(
                'user_id', 'miniprogram_id','puser_id',
                DB::raw("sum(pay_money) as pay_money"),
                DB::raw("sum(common_pay_count) as common_pay_count"),
                DB::raw("sum(common_unpay_count) as common_unpay_count"),
                DB::raw("sum(vip_unpay_count) as vip_unpay_count"),
                DB::raw("sum(vip_pay_count) as vip_pay_count"),
            )->groupBy(['user_id', 'puser_id','miniprogram_id'])
            ->get();
        $insertData = [];
        $now = date('Y-m-d H:i:s');
        foreach ($result as $item) {
            $info = (array)$item;
            $info['created_at'] = $info['updated_at'] = $now;
            $info['month_at'] = $month;
            $insertData[] = $info;
        }

        DB::table('tj_optimizer_month_charge')
            ->insert($insertData);
    }
}