admin_api/app/Http/Controllers/Admin/GameWinLoseRankController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Facade\TableName;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class GameWinLoseRankController extends BaseController
{
    /**
     * 游戏输赢排行榜列表页面
     */
    public function index(Request $request)
    {
        $gameId = $request->get('game_id', '');
        $date = $request->get('date', date('Y-m-d'));
        $type = $request->get('type', 'win'); // win(赢100) or lose(输100)

        // 获取游戏列表
        $games = config('games.gameName');

        // 获取输赢数据
        $rankData = [];
        if (!empty($gameId) && !empty($date)) {
            $rankData = $this->getWinLoseRank($gameId, $date, $type);

        }

        return view('admin.game_winlose_rank.index', compact('games', 'gameId', 'date', 'type', 'rankData'));
    }

    /**
     * 获取游戏输赢排行榜数据
     */
    private function getWinLoseRank($gameId, $date, $type)
    {
        // 获取游戏对应的服务器ID
        $serverIds = DB::connection('read')
            ->table(TableName::QPPlatformDB() . 'GameRoomInfo')
            ->where('GameID', $gameId)
            ->pluck('ServerID')
            ->toArray();


        if (empty($serverIds)) {
            return [];
        }

        // 计算月份表名
        $monthTable = 'YN_RecordScoreInfo_' . date('Ym', strtotime($date));



        // 构建查询条件
        $startTime = $date . ' 00:00:00';
        $endTime = $date . ' 23:59:59';

        // 根据类型设置排序和限制条件
        $orderBy = $type === 'win' ? 'total_change DESC' : 'total_change ASC';
        $havingCondition = $type === 'win' ? 'SUM(r.ChangeScore) >= 10000' : 'SUM(r.ChangeScore) <= -10000'; // 100元 = 10000分

        try {
            // 查询输赢数据并关联用户信息
            $query = DB::connection('read')
                ->table(TableName::QPTreasureDB() . $monthTable . ' as r')
                ->join(TableName::QPAccountsDB() . 'AccountsInfo as ai', 'r.UserID', '=', 'ai.UserID')
                ->whereIn('r.ServerID', $serverIds)
                ->whereBetween('r.UpdateTime', [$startTime, $endTime])
                ->select([
                    'r.UserID',
                    'ai.GameID',
                    'ai.NickName',
                    'ai.Channel',
                    'ai.RegisterDate',
                    'ai.LastLogonDate',
                    DB::raw('SUM(r.ChangeScore) as total_change')
                ])
                ->groupBy([
                    'r.UserID',
                    'ai.GameID',
                    'ai.NickName',
                    'ai.Channel',
                    'ai.RegisterDate',
                    'ai.LastLogonDate'
                ])
                ->havingRaw($havingCondition)
                ->orderByRaw($orderBy)
                ->limit(100);

            $results = $query->get();

            // 转换数据格式
            $rankData = [];
            foreach ($results as $index => $item) {
                $rankData[] = [
                    'rank' => $index + 1,
                    'user_id' => $item->UserID,
                    'game_id' => $item->GameID,
                    'nick_name' => $item->NickName,
                    'channel' => $item->Channel,
                    'register_date' => $item->RegisterDate ? date('Y-m-d H:i:s', strtotime($item->RegisterDate)) : '',
                    'last_logon_date' => $item->LastLogonDate ? date('Y-m-d H:i:s', strtotime($item->LastLogonDate)) : '',
                    'total_amount' => number_format($item->total_change / 100, 2), // 转换为元
                    'raw_amount' => $item->total_change
                ];
            }

            return $rankData;

        } catch (\Exception $e) {
            // 记录错误日志
            \Log::error('GameWinLoseRank query error: ' . $e->getMessage());
            return [];
        }
    }
}