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 ? 'CASE WHEN ruds.TotalBet > 0 THEN (ruds.WinScore - ruds.LostScore)/ruds.TotalBet ELSE 0 END asc' : 'CASE WHEN ruds.TotalBet > 0 THEN (ruds.WinScore - ruds.LostScore)/ruds.TotalBet ELSE 0 END desc';
            $q->orderByRaw($sort);
        }
        $page = $q
            ->select([
            'ruds.UserID',
            'ruds.WinScore',
            'ruds.LostScore',
            'ruds.TotalBet',
            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'), DB::raw('0 as TotalRecharge'), DB::raw('0 as TotalWithdraw'),
                DB::raw('\'\' as EmailAddress'), DB::raw('\'\' as PhoneNum'), DB::raw('0 as GameID'),
        ])->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 = 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] ?? '';
            }
            $totals = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics as ruts')->whereIn('UserID', $uids)
                ->get()->toArray();
            foreach ($page->items() as $key => $item) {
                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)
                    ->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]);
    }
}