mex_api/app/Http/Controllers/Admin/WinLoseRankController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Facade\TableName;
use App\Http\Controllers\Controller;
use App\Http\helper\NumConfig;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class WinLoseRankController extends Controller
{
    public function index(Request $req)
    {
        $condition = ['DateID' => date('Ymd'), ['v.Recharge', '>', 0]];

        $q = DB::table('QPRecordDB.dbo.RecordUserDataStatisticsNew as ruds');
        
        // 排序逻辑
        if ($req->input('sortBy')) {
            $sortBy = $req->input('sortBy');
            $sortType = $req->input('sortType', 'asc');
            
            if ($sortBy == 'rtp') {
                $sort = 'CASE WHEN ruds.TotalBet > 0 THEN (ruds.TotalBet + (ruds.WinScore + ruds.LostScore))*1.0/ruds.TotalBet ELSE 0 END ' . $sortType;
                $q->orderByRaw($sort);
            } elseif ($sortBy == 'winlose') {
                $sort = '(ruds.WinScore + ruds.LostScore) ' . $sortType;
                $q->orderByRaw($sort);
            }
        }
        
        $q->select([
                'ruds.UserID',
                'ruds.WinScore',
                'ruds.LostScore',
                'ruds.TotalBet',
                DB::raw('CASE WHEN ruds.TotalBet > 0 THEN (ruds.TotalBet + (ruds.WinScore + ruds.LostScore))*1.0/ruds.TotalBet ELSE 0 END as rtp'),
                DB::raw('0 as sameRW'),
                DB::raw('-1 as KindID'), DB::raw('0 as TotalRecharge'), DB::raw('0 as TotalWithdraw'),
                DB::raw('\'\' as EmailAddress'), DB::raw('\'\' as PhoneNum'), DB::raw('0 as GameID'),
            ])
            ->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as v', 'v.UserID', '=', 'ruds.UserID')
            ->where($condition);
        
        // 在线状态筛选
        $onlineStatus = $req->input('online_status');
        if ($onlineStatus == 1) {
            // 在线：有记录
            $q->leftJoin('QPTreasureDB.dbo.GameScoreLocker as gsl', 'gsl.UserID', '=', 'ruds.UserID')
                ->whereNotNull('gsl.UserID');
        } elseif ($onlineStatus == 2) {
            // 离线：无记录
            $q->leftJoin('QPTreasureDB.dbo.GameScoreLocker as gsl', 'gsl.UserID', '=', 'ruds.UserID')
                ->whereNull('gsl.UserID');
        }
        
        $page = $q->paginate(15);

        if ($page->count() > 0) {
            $uids = array_column($page->items(), 'UserID');
            $lockers = DB::table('QPTreasureDB.dbo.GameScoreLocker')->whereIn('UserID', $uids)
                ->pluck('KindID', 'UserID')->toArray();
            $phones = DB::table('QPAccountsDB.dbo.AccountPhone as ap')->whereIn('UserID', $uids)
                ->pluck('PhoneNum', 'UserID')->toArray();
            foreach ($page->items() as $key => $item) {
                $item->PhoneNum = $phones[$item->UserID] ?? '';
            }
            $GameIDs = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')->whereIn('UserID', $uids)
                ->pluck('GameID', 'UserID')->toArray();
            foreach ($page->items() as $key => $item) {
                $item->GameID = $GameIDs[$item->UserID] ?? '';
            }
            $emails = DB::table('QPAccountsDB.dbo.AccountWithdrawInfo as awi')->whereIn('UserID', $uids)
                ->pluck('EmailAddress', 'UserID')->toArray();
            foreach ($page->items() as $key => $item) {
                $item->EmailAddress = $emails[$item->UserID] ?? '';
            }
            // 身上的钱
            $gameScores = DB::table('QPTreasureDB.dbo.GameScoreInfo')
                ->lock('with(nolock)')
                ->whereIn('UserID', $uids)
                ->pluck('Score', 'UserID')->toArray();
            // 待审核提现
            $orderWtihdraws = DB::table('QPAccountsDB.dbo.OrderWithDraw')
                ->lock('with(nolock)')
                ->whereIn('UserID', $uids)
                ->where('State', 1)
                ->selectRaw('SUM(WithDraw) as WithDraw, UserID')
                ->groupBy('UserID')
                ->pluck('WithDraw', 'UserID')->toArray();
            $totals = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics as ruts')->whereIn('UserID', $uids)
                ->get()->toArray();
            foreach ($page->items() as $key => $item) {
                $item->Score = $gameScores[$item->UserID] ?? 0;
                $item->WithDrawWaitAudit = $orderWtihdraws[$item->UserID] ?? 0;
                foreach ($totals as $total) {
                    if ($item->UserID == $total->UserID) {
                        $item->TotalRecharge = $total->Recharge;
                        $item->TotalWithdraw = $total->Withdraw;
                    }
                }
            }

            //关联用户信息
            $phones = array_filter(array_column($page->items(), 'PhoneNum'));
            $emails = array_filter(array_column($page->items(), 'EmailAddress'));
            $phoneRWs = [];
            if (count($phones) > 0) {
                $phoneUids = DB::table('QPAccountsDB.dbo.AccountPhone')->whereIn('PhoneNum', $phones)
                    ->get()->toArray();
                if (count($phoneUids) > 0) {
                    $res = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics')
                        ->whereIn('UserID', array_column($phoneUids, 'UserID'))
                        ->get();
                    foreach ($res as $row) {
                        foreach ($phoneUids as $ap) {
                            if ($ap->UserID == $row->UserID) {
                                $phone = $ap->PhoneNum;
                                if (!isset($phoneRWs[$phone])) {
                                    $phoneRWs[$phone] = [];
                                }
                                $phoneRWs[$phone][$row->UserID] = $row->Recharge - $row->Withdraw / NumConfig::NUM_VALUE;
                            }
                        }
                    }
                }
            }
            $emailRWs = [];
            if (count($emails) > 0) {
                $emailUids = DB::table('QPAccountsDB.dbo.AccountWithdrawInfo')
                    ->whereIn('EmailAddress', $emails)
                    ->get()->toArray();
                if (count($emailUids) > 0) {
                    $res = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics')
                        ->whereIn('UserID', array_column($emailUids, 'UserID'))
                        ->get();
                    foreach ($res as $row) {
                        foreach ($emailUids as $awi) {
                            if ($awi->UserID == $row->UserID) {
                                $email = $awi->EmailAddress;
                                if (!isset($emailRWs[$email])) {
                                    $emailRWs[$email] = [];
                                }
                                $emailRWs[$email][$row->UserID] = $row->Recharge - $row->Withdraw / NumConfig::NUM_VALUE;
                            }
                        }
                    }
                }
            }
            foreach ($page as $k => $v) {
                $uidRws = [];
                if ($v->EmailAddress) {
                    foreach ($emailRWs[$v->EmailAddress] ?? [] as $uid => $value) {
                        $uidRws[$uid] = $value;
                    }
                }
                if ($v->PhoneNum) {
                    foreach ($phoneRWs[$v->PhoneNum] ?? [] as $uid => $value) {
                        $uidRws[$uid] = $value;
                    }
                }
                $page[$k]->sameRW = array_sum($uidRws);
                $page[$k]->KindID = $lockers[$v->UserID] ?? -1;
            }

        }
        $gameServerNames = DB::table(TableName::QPPlatformDB() . 'RoomConfig')
            ->pluck('RoomName', 'GameID')->toArray();

        return view('admin.win_lose_rank.index', ['list' => $page, 'gameServerNames' => $gameServerNames, 'request' => $req]);
    }
}