admin_api/app/Console/Commands/RecordUserScoreChangeStatistics.php

<?php

namespace App\Console\Commands;

use App\Facade\TableName;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;

class RecordUserScoreChangeStatistics extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'RecordUserScoreChangeStatistics';

    /**
     * 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()
    {
        $carBob = Carbon::yesterday()->subDays(0);
        $date = $carBob->format('Y-m-d');
        $dateID = $carBob->format('Ymd');

        $this->RecordUserScoreChangeStatistics($date, $dateID);
        $this->RecordUserScoreChangeTotalStatistics($date, $dateID);

        return true;
    }

    // 按用户，按天分，按类型分
    public function RecordUserScoreChangeStatistics($date, $dateID)
    {
        DB::connection('sqlsrv')->table(TableName::QPRecordDB() . 'RecordUserScoreChange as a')
            ->whereDate('UpdateTime', $date)
            ->selectRaw('sum(ChangeScore) Score,count(UserID) ScoreCount,Reason as ScoreType,UserID')
            ->orderBy('UserID')
            ->groupBy('UserID', 'Reason')
//            ->get()->each(function ($query) use ($dateID) {
//                return $query->DateID = $dateID;
//            })->map(function ($value) {
//                return (array)$value;
//            })

            ->chunk(100, function ($res) use ($dateID) {

                $list = $res->map(function ($val) use ($dateID) {
                    $array = (array)$val;
                    Arr::pull($array, 'row_num');
                    $array['DateID'] = $dateID;
                    return $array;
                })->toArray();

                DB::table(TableName::QPRecordDB() . 'RecordUserScoreChangeStatistics')->insert($list);

            });

    }

    // 按天、按类型分
    public function RecordUserScoreChangeTotalStatistics($date, $dateID)
    {
        DB::connection('sqlsrv')->table(TableName::QPRecordDB() . 'RecordUserScoreChange as a')
            ->whereDate('UpdateTime', $date)
            ->selectRaw('sum(ChangeScore) Score,count(UserID) ScoreCount,Reason as ScoreType')
            ->orderBy('Reason')
            ->groupBy('Reason')
            ->chunk(500, function ($res) use ($dateID) {

                $list = $res->map(function ($val) use ($dateID) {
                    $array = (array)$val;
                    Arr::pull($array, 'row_num');
                    $array['DateID'] = $dateID;
                    return $array;
                })->toArray();

                DB::table(TableName::QPRecordDB() . 'RecordUserScoreChangeTotalStatistics')->insert($list);

            });
//            ->get()->each(function ($query) use ($dateID) {
//                return $query->DateID = $dateID;
//            })->map(function ($value) {
//                return (array)$value;
//            })->toArray();

//        DB::table(TableName::QPRecordDB() . 'RecordUserScoreChangeTotalStatistics')
//            ->insert($list);
    }
}