admin_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')];

        $q = DB::table('QPRecordDB.dbo.RecordUserDataStatisticsNew as ruds');
        if ($req->input('sort')) {
            $sort = $req->input('sort') == 1 ? 'rtp asc' : 'rtp desc';
            $q->orderByRaw($sort);
        }
        $page = $q
            ->leftJoin('QPAccountsDB.dbo.AccountPhone as ap', 'ruds.UserID', '=', 'ap.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'ruds.UserID', '=', 'ai.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountWithdrawInfo as awi', 'awi.UserID', '=', 'ruds.UserID')
            ->leftJoin('QPRecordDB.dbo.RecordUserTotalStatistics as ruds_old', 'ruds.UserID', '=', 'ruds_old.UserID')
            ->select([
            'ruds.UserID',
            'ap.PhoneNum',
            'ai.GameID',
            'awi.EmailAddress',
            'ruds.WinScore',
            'ruds.LostScore',
            'ruds.TotalBet',
            'ruds_old.Recharge',
            'ruds_old.Withdraw',
            DB::raw('CASE WHEN ruds.TotalBet > 0 THEN (ruds.WinScore - ruds.LostScore)/ruds.TotalBet ELSE 0 END as rtp'),
            DB::raw('0 as sameRW'),
            DB::raw('0 as KindID'),
        ])->where($condition)
        ->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 = 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)
                    ->pluck('UserID', 'PhoneNum')->toArray();
                if (count($phoneUids) > 0) {
                    $res = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics')->whereIn('UserID', $phoneUids)
                        ->get();
                    foreach ($res as $row) {
                        $phone = array_search($row->UserID, $phoneUids);
                        $phoneRWs[$phone] = $row->Recharge - $row->Withdraw/NumConfig::NUM_VALUE;
                    }
                }
            }
            $emailRWs = [];
            if (count($emails) > 0) {
                $emailUids = DB::table('QPAccountsDB.dbo.AccountWithdrawInfo')
                    ->whereIn('EmailAddress', $emails)
                    ->pluck('UserID', 'EmailAddress')->toArray();
                if (count($emailUids) > 0) {
                    $res = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics')->whereIn('UserID', $emailUids)
                        ->get();
                    foreach ($res as $row) {
                        $email = array_search($row->UserID, $emailUids);
                        $emailRWs[$email] = $row->Recharge - $row->Withdraw/NumConfig::NUM_VALUE;
                    }
                }
            }

            foreach ($page as $k => $v) {
                $page[$k]->sameRW = ($emailRWs[$v->EmailAddress] ?? 0) + ($phoneRWs[$v->PhoneNum] ?? 0);
                $page[$k]->KindID = $lockers[$v->UserID] ?? 0;
            }
        }
        $gameServerNames = DB::table(TableName::QPPlatformDB() . 'RoomConfig')
            ->pluck('RoomName', 'GameID')->toArray();

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