admin_api/app/Console/Commands/RecordPaidRewardDailyStatistics.php

<?php

namespace App\Console\Commands;

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

class RecordPaidRewardDailyStatistics extends Command
{
    protected $signature = 'RecordPaidRewardDailyStatistics {day?}';

    protected $description = '统计付费用户各奖励类型每日送出金额';

    public function handle()
    {
        $day = $this->argument('day') ?: Carbon::yesterday()->format('Y-m-d');

        $dateId = date('Ymd', strtotime($day));

        $stats = $this->buildStats($day);

        foreach ($stats as $item) {
            DB::table(TableName::QPRecordDB() . 'RecordPaidRewardDailyStatistics')->updateOrInsert(
                [
                    'DateID' => $dateId,
                    'StatType' => $item['StatType'],
                ],
                [
                    'TotalAmount' => $item['TotalAmount'],
                    'CreatedAt' => now()->format('Y-m-d H:i:s'),
                    'UpdatedAt' => now()->format('Y-m-d H:i:s'),
                ]
            );
        }

        $this->info("RecordPaidRewardDailyStatistics done: {$day}");

        return true;
    }

    private function buildStats(string $day): array
    {
        $stats = [];
        $signIn = $this->signInState($day);
        $stats[] = $signIn;
        $bankruptHelp = $this->bankruptHelp($day);
        $stats[] = $bankruptHelp;
        $stats[] = $this->fromSuperballPrize($day, 'superball');
        $stats[] = $this->fromChristmasWheel($day, 'christmas_gift_wheel');
        $stats[] = $this->fromScoreReason($day, 'bound_phone', 21);
        $dbStats = DB::table(TableName::QPRecordDB() . 'RecordPaidRewardDailyStatistics')
            ->lock('with(nolock)')
            ->where('DateID', date('Ymd', strtotime($day)))
            ->whereIn('StatType', ['normal_recharge_chips', 'first_recharge_gift_chips', 'bankrupt_gift_chips',
                'daily_gift_chips', 'vip_inactive_gift_chips', 'free_bonus_gift_chips', 'christmas_gift_chips', 'unknow_chips'])
            ->select('StatType', 'TotalAmount')
            ->get()->map(function ($item) {
                return json_decode(json_encode($item), true);
            })->toArray();
        $stats[] = $this->sumAll($dbStats, 'chips_all');

        return $stats;
    }

    private function sumAll(array $stats, string $type): array
    {
        $totalAmount = 0;

        foreach ($stats as $item) {
            $totalAmount += (int)($item['TotalAmount'] ?? 0);
        }

        return [
            'StatType' => $type,
            'TotalAmount' => $totalAmount,
        ];
    }

    private function fromScoreReason(string $day, string $type, int $reason): array
    {
        $start = $day . ' 00:00:00';
        $end = date('Y-m-d H:i:s', strtotime($day . ' +1 day'));

        $row = DB::connection('sqlsrv')
            ->table(TableName::QPRecordDB() . 'RecordUserScoreChange as r')
            ->join(TableName::QPAccountsDB() . 'YN_VIPAccount as va', 'va.UserID', '=', 'r.UserID')
            ->where('va.Recharge', '>', 0)
            ->where('r.Reason', $reason)
            ->where('r.UpdateTime', '>=', $start)
            ->where('r.UpdateTime', '<', $end)
            ->selectRaw('
                ISNULL(CAST(SUM(r.ChangeScore) AS BIGINT), 0) as TotalAmount
            ')
            ->first();

        return [
            'StatType' => $type,
            'TotalAmount' => (int)($row->TotalAmount ?? 0),
        ];
    }

    private function fromSuperballPrize(string $day, string $type): array
    {
        $row = DB::connection('sqlsrv')
            ->table(TableName::agent() . 'superball_prize_log as p')
            ->join(TableName::QPAccountsDB() . 'YN_VIPAccount as va', 'va.UserID', '=', 'p.user_id')
            ->where('va.Recharge', '>', 0)
            ->whereDate('p.created_at', $day)
            ->selectRaw('
                ISNULL(CAST(SUM(p.total_amount) AS BIGINT), 0) as TotalAmount
            ')
            ->first();

        return [
            'StatType' => $type,
            'TotalAmount' => (int)($row->TotalAmount ?? 0),
        ];
    }

    private function fromChristmasWheel(string $day, string $type): array
    {
        $start = $day . ' 00:00:00';
        $end = date('Y-m-d H:i:s', strtotime($day . ' +1 day'));

        $row = DB::connection('sqlsrv')
            ->table(TableName::agent() . 'christmas_wheel_history as h')
            ->join(TableName::QPAccountsDB() . 'YN_VIPAccount as va', 'va.UserID', '=', 'h.UserID')
            ->where('va.Recharge', '>', 0)
            ->where('h.created_at', '>=', $start)
            ->where('h.created_at', '<', $end)
            ->selectRaw('
                ISNULL(CAST(SUM(h.reward * 100) AS BIGINT), 0) as TotalAmount
            ')
            ->first();

        return [
            'StatType' => $type,
            'TotalAmount' => (int)($row->TotalAmount ?? 0),
        ];
    }

    private function signInState(string $day)
    {
        $res = DB::table('QPRecordDB.dbo.RecordSignIn as h')
            ->lock('with(nolock)')
            ->whereDate('SignInDate', $day)
            ->leftJoin(DB::raw('QPAccountsDB.dbo.YN_VIPAccount as va with(nolock)'),
                'va.UserID', '=', 'h.UserID')
            ->where('va.Recharge', '>', 0)
            ->selectRaw('sum(RewardScore) as TotalAmount')
            ->first();
        return [
            'StatType' => 'sign_in',
            'TotalAmount' => (int)($res->TotalAmount ?? 0),
        ];
    }

    private function bankruptHelp(string $day)
    {
        $res = DB::table('QPRecordDB.dbo.RecordUserScoreChange as rus')
            ->lock('with(nolock)')
            ->leftJoin(DB::raw('QPAccountsDB.dbo.YN_VIPAccount as va with(nolock)'),
                'va.UserID', '=', 'rus.UserID')
            ->whereDate('UpdateTime', $day)
            ->where('va.Recharge', '>', 0)
            ->where('rus.Reason', 13)
            ->selectRaw('sum(ChangeScore) as TotalAmount')
            ->first();
        return [
            'StatType' => 'bankrupt_help',
            'TotalAmount' => (int)($res->TotalAmount ?? 0),
        ];
    }
}