mex_api/app/Http/Controllers/Admin/GameDataController.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 GameDataController extends Controller
{

    public function makeGameid(Request $request){


        $userid=DB::connection('write')->table('QPAccountsDB.dbo.GameIdentifier')->select("UserID")->orderByDesc("UserID")->first()->UserID;
        for($i=0;$i<1000000;$i++){
            $gameid= mt_rand(10893227,20893227);

//            echo "<br/>";
            if(!DB::connection('write')->table('QPAccountsDB.dbo.GameIdentifier')->where('GameID' , $gameid)->exists()){
                $userid++;
                DB::connection('write')->table('QPAccountsDB.dbo.GameIdentifier')->updateOrInsert(['UserID'=>$userid,'GameID' => $gameid]);
            }
        }

    }

    //实时在线
    public function userOnlineView(Request $request)
    {
        $start_date = $request->start_date ?? date('Y-m-d');
        $end_date = $request->end_date ?? date('Y-m-d');
        $game_name = DB::connection('read')->table('QPPlatformDB.dbo.GameKindItem')
            ->whereIn('GameID', config('games.openKGame'))
            ->pluck('KindName as GameName', 'GameID');//单选框数据
        return view('admin.game_data.useronline', ['start_date' => $start_date, 'end_date' => $end_date,'game_name' => $game_name]);
    }

    public function TigerBuyRecord(Request $request)
    {

        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';

        $start_time = str_replace('T', ' ', $start_time ?? "");
        $end_time = str_replace('T', ' ', $end_time ?? "");

        $GameID = $request->GameID ?: '';
        $Type = $request->Type ?: 0;
        $SortID = $request->SortID ?: -1;
        $where[] = ['KindID', 5006];
        $sortArr = ['-1' => '全部','1' => '初级场','2' => '中级场','3' => '高级场'];
        $typeArr = [0 => '全部',1 => '免费',2 => '买入'];

        !empty($start_time) && $where[] = ['CreateTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['CreateTime', '<=', $end_time];
        !empty($GameID) && $where[] = ['GameID', $GameID];
        !empty($Type) && $where[] = ['Type', $Type];
        ($SortID>=0) && $where[] = ['SortID', $SortID];


        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs as ri')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ri.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ri.*,ai.GameID,ai.NickName,Channel')
            ->orderByDesc('CreateTime')
            ->paginate(10);

        foreach ($where as $k => $v){
            if($v[0] == 'GameID'){
                unset($where[$k]);
                break;
            }
        }

        $sumData = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs')
            ->where($where)
            ->selectRaw('sum(ChangeScore) sumwin,sum(BuyScore) sumbuy')
            ->first();

        foreach ($list as $value) {
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);

            $value->BuyBase = number_float($value->BuyBase / NumConfig::NUM_VALUE);
            $value->BuyScore = $value->BuyScore;
        }
        $gameName = '金老虎全屏';
        $action = '/admin/game_data/TigerBuyRecord';
        $data = compact('list', 'Type', 'start_time', 'end_time', 'GameID', 'SortID','typeArr','sortArr','sumData','gameName','action');
        return view('admin.game_data.gate_record', $data);

    }
    public function OxBuyRecord(Request $request)
    {

        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';

        $start_time = str_replace('T', ' ', $start_time ?? "");
        $end_time = str_replace('T', ' ', $end_time ?? "");

        $GameID = $request->GameID ?: '';
        $Type = $request->Type ?: 0;
        $SortID = $request->SortID ?: -1;
        $where[] = ['KindID', 5007];
        $sortArr = ['-1' => '全部','1' => '初级场','2' => '中级场','3' => '高级场'];
        $typeArr = [0 => '全部',1 => '免费',2 => '买入'];

        !empty($start_time) && $where[] = ['CreateTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['CreateTime', '<=', $end_time];
        !empty($GameID) && $where[] = ['GameID', $GameID];
        !empty($Type) && $where[] = ['Type', $Type];
        ($SortID>=0) && $where[] = ['SortID', $SortID];


        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs as ri')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ri.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ri.*,ai.GameID,ai.NickName,Channel')
            ->orderByDesc('CreateTime')
            ->paginate(10);

        foreach ($where as $k => $v){
            if($v[0] == 'GameID'){
                unset($where[$k]);
                break;
            }
        }

        $sumData = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs')
            ->where($where)
            ->selectRaw('sum(ChangeScore) sumwin,sum(BuyScore) sumbuy')
            ->first();

        foreach ($list as $value) {
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);

            $value->BuyBase = number_float($value->BuyBase / NumConfig::NUM_VALUE);
            $value->BuyScore = $value->BuyScore;
        }
        $gameName = '金牛全屏';
        $action = '/admin/game_data/OxBuyRecord';
        $data = compact('list', 'Type', 'start_time', 'end_time', 'GameID', 'SortID','typeArr','sortArr','sumData','gameName','action');
        return view('admin.game_data.gate_record', $data);

    }
    public function RabbitBuyRecord(Request $request)
    {

        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';

        $start_time = str_replace('T', ' ', $start_time ?? "");
        $end_time = str_replace('T', ' ', $end_time ?? "");

        $GameID = $request->GameID ?: '';
        $Type = $request->Type ?: 0;
        $SortID = $request->SortID ?: -1;
        $where[] = ['KindID', 5008];
        $sortArr = ['-1' => '全部','1' => '初级场','2' => '中级场','3' => '高级场'];
        $typeArr = [0 => '全部',1 => '免费',2 => '买入'];

        !empty($start_time) && $where[] = ['CreateTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['CreateTime', '<=', $end_time];
        !empty($GameID) && $where[] = ['GameID', $GameID];
        !empty($Type) && $where[] = ['Type', $Type];
        ($SortID>=0) && $where[] = ['SortID', $SortID];


        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs as ri')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ri.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ri.*,ai.GameID,ai.NickName,Channel')
            ->orderByDesc('CreateTime')
            ->paginate(10);

        foreach ($where as $k => $v){
            if($v[0] == 'GameID'){
                unset($where[$k]);
                break;
            }
        }

        $sumData = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs')
            ->where($where)
            ->selectRaw('sum(ChangeScore) sumwin,sum(BuyScore) sumbuy')
            ->first();

        foreach ($list as $value) {
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);

            $value->BuyBase = number_float($value->BuyBase / NumConfig::NUM_VALUE);
            $value->BuyScore = $value->BuyScore;
        }
        $gameName = '金牛全屏';
        $action = '/admin/game_data/RabbitBuyRecord';
        $data = compact('list', 'Type', 'start_time', 'end_time', 'GameID', 'SortID','typeArr','sortArr','sumData','gameName','action');
        return view('admin.game_data.gate_record', $data);

    }

    /**
     * 用户保护日志
     * 数据来源：QPRecordDB.dbo.RecordProtectLogs
     * 关联：AccountsInfo（GameID / RegisterDate / LastLogonDate）、GameKindItem（KindName）
     */
    public function protectLogs(Request $request)
    {
        $gameID   = (int)($request->get('GameID') ?: 0);
        $vip      = $request->get('vip', ''); // ''=全部, '1'=付费保护, '0'=非付费
        $start_date = $request->get('start_date', '');
        $end_date   = $request->get('end_date', '');

        $query = DB::connection('read')
            ->table('QPRecordDB.dbo.RecordProtectLogs as rpl')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'rpl.UserID', '=', 'ai.UserID')
            ->leftJoin('QPPlatformDB.dbo.GameKindItem as gk', 'rpl.GameCode', '=', 'gk.KindID');

        // 会员ID筛选（GameID）
        if ($gameID) {
            $query->where('ai.GameID', $gameID);
        }

        // 付费保护筛选
        if ($vip !== '' && $vip !== null) {
            $query->where('rpl.VIP', (int)$vip);
        }

        // 日期筛选（按保护时间 CreateTime）
        if (!empty($start_date)) {
            $query->where('rpl.CreateTime', '>=', $start_date . ' 00:00:00');
        }
        if (!empty($end_date)) {
            $query->where('rpl.CreateTime', '<=', $end_date . ' 23:59:59');
        }

        $list = $query
            ->selectRaw('rpl.*, ai.GameID, ai.RegisterDate, ai.LastLogonDate, gk.KindName')
            ->orderByDesc('rpl.CreateTime')
            ->paginate(20);

        // 金额除以 100
        foreach ($list as $item) {
            $item->WinAmount = number_float($item->WinAmount / NumConfig::NUM_VALUE);
        }

        // VIP 选项
        $vipOptions = [
            ''  => '全部',
            '1' => '付费保护',
            '0' => '非付费保护',
        ];

        return view('admin.game_data.protect_logs', [
            'list'       => $list,
            'gameID'     => $gameID ?: '',
            'vip'        => (string)$vip,
            'vipOptions' => $vipOptions,
            'start_date' => $start_date,
            'end_date'   => $end_date,
        ]);
    }
    public function NewOlympusBuyRecord(Request $request)
    {

        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';

        $start_time = str_replace('T', ' ', $start_time ?? "");
        $end_time = str_replace('T', ' ', $end_time ?? "");

        $GameID = $request->GameID ?: '';
        $Type = $request->Type ?: 0;
        $SortID = $request->SortID ?: -1;
        $where[] = ['KindID', 6001];
        $sortArr = ['-1' => '全部','1' => '初级场','2' => '中级场','3' => '高级场'];
        $typeArr = [0 => '全部',1 => '免费',2 => '买入'];

        !empty($start_time) && $where[] = ['CreateTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['CreateTime', '<=', $end_time];
        !empty($GameID) && $where[] = ['GameID', $GameID];
        !empty($Type) && $where[] = ['Type', $Type];
        ($SortID>=0) && $where[] = ['SortID', $SortID];


        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs as ri')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ri.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ri.*,ai.GameID,ai.NickName,Channel')
            ->orderByDesc('CreateTime')
            ->paginate(10);

        foreach ($where as $k => $v){
            if($v[0] == 'GameID'){
                unset($where[$k]);
                break;
            }
        }

        $sumData = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs')
            ->where($where)
            ->selectRaw('sum(ChangeScore) sumwin,sum(BuyScore) sumbuy')
            ->first();

        foreach ($list as $value) {
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);

            $value->BuyBase = number_float($value->BuyBase / NumConfig::NUM_VALUE);
            $value->BuyScore = $value->BuyScore;
        }
        $gameName = '新宙斯全屏';
        $action = '/admin/game_data/NewolyBuyRecord';
        $data = compact('list', 'Type', 'start_time', 'end_time', 'GameID', 'SortID','typeArr','sortArr','sumData','gameName','action');
        return view('admin.game_data.gate_record', $data);

    }
    public function AztecBuyRecord(Request $request)
    {

        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';

        $start_time = str_replace('T', ' ', $start_time ?? "");
        $end_time = str_replace('T', ' ', $end_time ?? "");

        $GameID = $request->GameID ?: '';
        $Type = $request->Type ?: 0;
        $SortID = $request->SortID ?: -1;
        $where[] = ['KindID', 5004];
        $sortArr = ['-1' => '全部','1' => '初级场','2' => '中级场','3' => '高级场'];
        $typeArr = [0 => '全部',1 => '免费',2 => '买入'];

        !empty($start_time) && $where[] = ['CreateTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['CreateTime', '<=', $end_time];
        !empty($GameID) && $where[] = ['GameID', $GameID];
        !empty($Type) && $where[] = ['Type', $Type];
        ($SortID>=0) && $where[] = ['SortID', $SortID];


        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs as ri')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ri.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ri.*,ai.GameID,ai.NickName,Channel')
            ->orderByDesc('CreateTime')
            ->paginate(10);

        foreach ($where as $k => $v){
            if($v[0] == 'GameID'){
                unset($where[$k]);
                break;
            }
        }

        $sumData = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs')
            ->where($where)
            ->selectRaw('sum(ChangeScore) sumwin,sum(BuyScore) sumbuy')
            ->first();

        foreach ($list as $value) {
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);

            $value->BuyBase = number_float($value->BuyBase / NumConfig::NUM_VALUE);
            $value->BuyScore = $value->BuyScore;
        }
        $gameName = 'Aztec全屏';
        $action = '/admin/game_data/AztecBuyRecord';
        $data = compact('list', 'Type', 'start_time', 'end_time', 'GameID', 'SortID','typeArr','sortArr','sumData','gameName','action');
        return view('admin.game_data.gate_record', $data);

    }
    public function fishBuyRecord(Request $request)
    {

        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';

        $start_time = str_replace('T', ' ', $start_time ?? "");
        $end_time = str_replace('T', ' ', $end_time ?? "");

        $GameID = $request->GameID ?: '';
        $Type = $request->Type ?: 0;
        $SortID = $request->SortID ?: -1;
        $where[] = ['KindID', 5005];
        $sortArr = ['-1' => '全部','1' => '初级场','2' => '中级场','3' => '高级场'];
        $typeArr = [0 => '全部',1 => '免费',2 => '买入'];

        !empty($start_time) && $where[] = ['CreateTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['CreateTime', '<=', $end_time];
        !empty($GameID) && $where[] = ['GameID', $GameID];
        !empty($Type) && $where[] = ['Type', $Type];
        ($SortID>=0) && $where[] = ['SortID', $SortID];


        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs as ri')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ri.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ri.*,ai.GameID,ai.NickName,Channel')
            ->orderByDesc('CreateTime')
            ->paginate(10);

        foreach ($where as $k => $v){
            if($v[0] == 'GameID'){
                unset($where[$k]);
                break;
            }
        }

        $sumData = DB::connection('write')->table('QPRecordDB.dbo.RecordNewThunderBonueLogs')
            ->where($where)
            ->selectRaw('sum(ChangeScore) sumwin,sum(BuyScore) sumbuy')
            ->first();

        foreach ($list as $value) {
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);

            $value->BuyBase = number_float($value->BuyBase / NumConfig::NUM_VALUE);
            $value->BuyScore = $value->BuyScore;
        }
        $gameName = '钓鱼全屏';
        $action = '/admin/game_data/fishBuyRecord';
        $data = compact('list', 'Type', 'start_time', 'end_time', 'GameID', 'SortID','typeArr','sortArr','sumData','gameName','action');
        return view('admin.game_data.gate_record', $data);

    }
    //实时在线
    public function userOnline(Request $request)
    {
        $game_id = $request->game_id ?? '';
        $type = $request->type??0;

        $start_date = $request->start_date ?? date('Y-m-d');
        if($type == 0 && $start_date == date('Y-m-d')){
            $time = time();
            $where= [['created', '<=', $time],['created', '>=', $time-86400]];
            $data = DB::connection('mysql')->table('game-report')
                ->where($where)
                ->orderBy('created', 'asc')
                ->get();

            //$whereY[] = ['ldate', '=', date('Ymd', strtotime($yesterday))];
            $whereY= [['created', '<=', $time-86400],['created', '>=', $time-86400*2]];
            $ydata = DB::connection('mysql')->table('game-report')
                ->where($whereY)
                ->orderBy('created', 'asc')
                ->get();
        }else{

            $yesterday = date('Y-m-d',strtotime($start_date)-86400);
            $where[] = ['ldate', '=', date('Ymd', strtotime($start_date))];

            $data = DB::connection('mysql')->table('game-report')
                ->where($where)
                ->orderBy('created', 'asc')
                ->get();

            $whereY[] = ['ldate', '=', date('Ymd', strtotime($yesterday))];
            $ydata = DB::connection('mysql')->table('game-report')
                ->where($whereY)
                ->orderBy('created', 'asc')
                ->get();
        }


        $result = [

        ];

        foreach ($data as $key => $value) {
            $online = json_decode($value->online,true);
            $onlineNum = 0;
            if(is_array($online)){
                foreach ($online as $val){
                    if($val['KindID'] == $game_id){
                        $onlineNum = $val['game_count'];
                        break;
                    }
                    $onlineNum +=$val['game_count'];
                }
            }
            $min = date('Hi',$value->created);
            $min = 'm_'.$min;
            $result['online'][$min] = $onlineNum;//训练币消耗
//            $result['online'][$min] = $value->online;//训练币消耗
            $result['reg'][$min] = $value->register;//累计场次
            $result['active'][$min] = $value->active;//参游人数
            $result['recharge'][$min] = $value->recharge/100;//新增数
            $result['withdraw'][$min] = $value->withdraw/100;
            $result['profit'][$min] = ($value->recharge-$value->withdraw)/100;
            $result['created_at'][$min] = date('Y-m-d H:i',$value->created);

            $result['yonline'][$min] = 0;
            $result['yreg'][$min] = 0;
            $result['yactive'][$min] = 0;
            $result['yrecharge'][$min] = 0;
            $result['ywithdraw'][$min] = 0;
        }
        unset($value);
        foreach ($ydata as $key => $value) {
            $min = date('Hi',$value->created);
            $min = 'm_'.$min;
            if(isset($result['reg'][$min])){

                $online = json_decode($value->online,true);
                $onlineNum = 0;
                if(is_array($online)){
                    foreach ($online as $val){
                        if($val['KindID'] == $game_id){
                            $onlineNum = $val['game_count'];
                            break;
                        }
                        $onlineNum +=$val['game_count'];
                    }
                }

                $result['yonline'][$min] = $onlineNum;//训练币消耗
                $result['yreg'][$min] = $value->register;//累计场次
                $result['yactive'][$min] = $value->active;//参游人数
                $result['yrecharge'][$min] = $value->recharge/100;//新增数
                $result['ywithdraw'][$min] = $value->withdraw/100;
                $result['yprofit'][$min] = ($value->recharge-$value->withdraw)/100;
            }

        }
        $rs = [
            'online' => array_values($result['online']),
            'reg' => array_values($result['reg']),
            'active' => array_values($result['active']),
            'recharge' => array_values($result['recharge']),
            'withdraw' => array_values($result['withdraw']),
            'profit' => array_values($result['profit']),
            'created_at' => array_values($result['created_at']),
            'yonline' => array_values($result['yonline']),
            'yreg' => array_values($result['yreg']),
            'yactive' => array_values($result['yactive']),
            'yrecharge' => array_values($result['yrecharge']),
            'ywithdraw' => array_values($result['ywithdraw']),
            'yprofit' => array_values($result['yprofit']?? []),
        ];

        return $rs;

    }


    public function onlineList(Request $request){

        $GameID = $request->get('GameID');
        $SortID = $request->get('SortID');
        $uid = $request->get('uid');

        if($uid){
            $where = [];
            $where[] = ['ai.GameID', $uid];
        }else{
            $where = [];

            !empty($GameID) && $where[] = ['gi.GameID', $GameID];
            !empty($SortID) && $where[] = ['gi.SortID', $SortID];
        }



        $list = DB::connection('read')->table('QPTreasureDB.dbo.GameScoreLocker as gl')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'gl.UserID', 'ai.UserID')
            ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gi','gl.ServerID','gi.ServerID')
            ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gsi','gl.UserID','=','gsi.UserID')
            ->where($where)
            ->selectRaw('ai.GameID,ai.NickName,Channel,gi.ServerName,gi.SortID,gl.CollectDate,gl.UserID,gsi.Score')
//            ->orderByDesc('CreateTime')
            ->paginate(10);

        $games = DB::connection('read')->table(TableName::QPPlatformDB() . 'GameKindItem')
            ->whereIn('GameID', config('games.openKGame'))
            ->select('KindName', 'GameID')
            ->pluck('KindName', 'GameID')
            ->toArray();
        $type = [
            1 => '初级',
            2 => '中级',
            3 => '高级',
            4 => '超高级'
        ];
        $data = compact('list', 'GameID', 'SortID','uid','games','type');
        return view('admin.game_data.game_online', $data);
    }


    public function gameRankList(Request $request){

        $date = $request->date ?: date('Y-m-d');
        $dateFormat = date('Ymd',strtotime($date));
        $channels =session('admin_channels');;
        $Channel = $request->get('Channel');

        $sort = $request->sort??1;

        $where = [];
        $where[] = ['rd.DateID', $dateFormat];




        if(!empty($Channel)) {
            $where[] = ['ai.Channel', $Channel];
        }

        $rank = [1 =>'Win Rank',2 =>'Lost Rank',3 => 'MaxWin Rank',4 => 'Recharge Rank',5 => 'Withdraw Rank'];

        $order_sql = '(WinScore + LostScore) DESC';
        switch ($sort) {
            case '1':
                $order_sql = '(WinScore + LostScore) DESC';
                break;
            case '2':
                $order_sql = '(WinScore + LostScore) ASC';
                break;
            case '3':
                $order_sql = 'MaxWinScore DESC';
                break;
            case '4':
                $order_sql = 'Recharge DESC';
                break;
            case '5':
                $order_sql = 'Withdraw DESC';
                break;
        }

        $list = DB::connection('read')->table('QPRecordDB.dbo.RecordUserDataStatisticsNew as rd')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'rd.UserID', 'ai.UserID')
            ->where($where)
            ->selectRaw('ai.UserID,ai.GameID,ai.NickName,Channel,ai.LastLogonDate,ai.RegisterDate,Recharge, (WinScore + LostScore) Score,MaxScore,MaxWinScore,Withdraw,MaxDrawBase,Revenue,TotalBet')
            ->orderByRaw($order_sql)
            ->paginate(20);


        $data = compact('list', 'date','rank','sort','Channel','channels');
        return view('admin.game_data.rank', $data);
    }

    public function userDetail(\Illuminate\Http\Request $request)
    {
        $userId = $request->input('user_id');
        $startDate = $request->input('start_date', date('Y-m-d', strtotime('-6 days')));
        $endDate = $request->input('end_date', date('Y-m-d'));

        $list = [];
        if ($userId) {
            $list = \DB::connection('read')->table('QPRecordDB.dbo.RecordUserDataStatisticsNew as rd')
                ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'rd.UserID', 'ai.UserID')
                ->where('ai.GameID', $userId)
                ->whereBetween('rd.DateID', [
                    date('Ymd', strtotime($startDate)),
                    date('Ymd', strtotime($endDate))
                ])
                ->selectRaw('rd.DateID, ai.UserID, ai.GameID, ai.NickName, Channel, ai.LastLogonDate, ai.RegisterDate, Recharge, (WinScore + LostScore) Score, MaxScore, MaxWinScore, Withdraw, MaxDrawBase, Revenue, TotalBet')
                ->orderBy('rd.DateID', 'desc')
                ->paginate(20);
        }

        return view('admin.game_data.user_detail', [
            'list' => $list,
            'user_id' => $userId,
            'start_date' => $startDate,
            'end_date' => $endDate,
        ]);
    }

    public function userTotalList(\Illuminate\Http\Request $request)
    {
        $rechargeMax = $request->input('recharge_max', 300);
        $withdrawMin = $request->input('withdraw_min', 1000);

        $list = \DB::connection('read')->table('QPRecordDB.dbo.RecordUserTotalStatistics as ut')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ut.UserID', '=', 'ai.UserID')
            ->where('ut.Recharge', '<=', $rechargeMax)
            ->where('ut.Withdraw', '>', $withdrawMin * 100)
            ->selectRaw('ai.UserID, ai.GameID, ai.NickName, ai.Channel, ai.LastLogonDate, ai.RegisterDate, (ut.WinScore + ut.LostScore) as TotalScore, ut.WinScore, ut.LostScore, ut.Revenue, ut.Withdraw, ut.Recharge, ut.DrawBase, ut.TotalBet, ut.RechargeTimes')
            ->orderByDesc('ai.LastLogonDate')
            ->paginate(20);

        return view('admin.user_total.list', [
            'list' => $list,
            'recharge_max' => $rechargeMax,
            'withdraw_min' => $withdrawMin,
        ]);
    }


    /**
     * 游戏参与情况统计
     * 按照注册时间统计不同UserMedal（推荐游戏ID）对应的游戏参与情况
     */
    public function gameParticipationStatistics(Request $request)
    {
        // 获取注册时间范围，默认近3天
        $startDate = $request->input('start_date', date('Y-m-d', strtotime('-3 days')));
        $endDate = $request->input('end_date', date('Y-m-d'));

        // 转换为日期时间格式（包含时间）
        $startDateTime = $startDate . ' 00:00:00';
        $endDateTime = $endDate . ' 23:59:59';

        // 1. 统计注册人数（按UserMedal分组）
        $registerStats = DB::connection('read')
            ->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->whereBetween('ai.RegisterDate', [$startDateTime, $endDateTime])
            ->whereNotNull('ai.UserMedal')
            ->where('ai.UserMedal', '!=', '')
            ->where('ai.UserMedal', '!=', 0)
            ->where('ai.Channel', '!=', 100)
            ->selectRaw('
                ai.UserMedal as game_id,
                COUNT(DISTINCT ai.UserID) as register_count
            ')
            ->groupBy('ai.UserMedal')
            ->get()
            ->keyBy('game_id');

        // 2. 统计参与游戏的用户数（WinInning + LostInning > 0，按UserMedal分组）
        $playedStats = DB::connection('read')
            ->table('QPRecordDB.dbo.RecordUserTotalStatistics as ut')
            ->join(TableName::QPAccountsDB() . 'AccountsInfo as ai', 'ut.UserID', '=', 'ai.UserID')
            ->whereBetween('ai.RegisterDate', [$startDateTime, $endDateTime])
            ->whereNotNull('ai.UserMedal')
            ->where('ai.UserMedal', '!=', '')
            ->where('ai.UserMedal', '!=', 0)
            ->where('ai.Channel', '!=', 100)
            ->whereRaw('(ISNULL(ut.WinInning, 0) + ISNULL(ut.LostInning, 0)) > 0')
            ->selectRaw('
                ai.UserMedal as game_id,
                COUNT(DISTINCT ai.UserID) as played_count
            ')
            ->groupBy('ai.UserMedal')
            ->get()
            ->keyBy('game_id');

        // 3. 统计付费用户数（Recharge > 0，按UserMedal分组）
        $paidStats = DB::connection('read')
            ->table('QPRecordDB.dbo.RecordUserTotalStatistics as ut')
            ->join(TableName::QPAccountsDB() . 'AccountsInfo as ai', 'ut.UserID', '=', 'ai.UserID')
            ->whereBetween('ai.RegisterDate', [$startDateTime, $endDateTime])
            ->whereNotNull('ai.UserMedal')
            ->where('ai.UserMedal', '!=', '')
            ->where('ai.UserMedal', '!=', 0)
            ->where('ai.Channel', '!=', 100)
            ->where('ut.Recharge', '>', 0)
            ->selectRaw('
                ai.UserMedal as game_id,
                COUNT(DISTINCT ai.UserID) as paid_count
            ')
            ->groupBy('ai.UserMedal')
            ->get()
            ->keyBy('game_id');

        // 4. 获取所有涉及的游戏ID
        $allGameIds = collect([$registerStats, $playedStats, $paidStats])
            ->flatMap(function ($stats) {
                return $stats->pluck('game_id');
            })
            ->unique()
            ->filter()
            ->toArray();

        // 5. 获取游戏信息（从MySQL）
        $games = [];
        if (!empty($allGameIds)) {
            $gamesData = DB::connection('mysql')
                ->table('webgame.games')
                ->whereIn('id', $allGameIds)
                ->select('id', 'brand', 'title')
                ->get();

            foreach ($gamesData as $game) {
                $games[$game->id] = $game;
            }
        }

        // 6. 组装统计数据
        $statistics = [];
        foreach ($allGameIds as $gameId) {
            $registerStat = $registerStats->get($gameId);
            $registerCount = $registerStat ? (int)$registerStat->register_count : 0;
            
            $playedStat = $playedStats->get($gameId);
            $playedCount = $playedStat ? (int)$playedStat->played_count : 0;
            
            $paidStat = $paidStats->get($gameId);
            $paidCount = $paidStat ? (int)$paidStat->paid_count : 0;
            
            // 参游率 = 参与游戏人数 / 注册人数
            $participationRate = $registerCount > 0 ? round(($playedCount / $registerCount) * 100, 2) : 0;
            
            // 付费率 = 付费人数 / 注册人数
            $paidRate = $registerCount > 0 ? round(($paidCount / $registerCount) * 100, 2) : 0;
            
            // 获取游戏名称
            $gameName = '未知游戏';
            if (isset($games[$gameId])) {
                $game = $games[$gameId];
                $gameName = $game->brand . ' - ' . $game->title;
            }

            $statistics[] = [
                'game_id' => $gameId,
                'game_name' => $gameName,
                'register_count' => $registerCount,
                'played_count' => $playedCount,
                'participation_rate' => $participationRate,
                'paid_count' => $paidCount,
                'paid_rate' => $paidRate,
            ];
        }

        // 按注册人数排序（降序）
        usort($statistics, function ($a, $b) {
            return $b['register_count'] <=> $a['register_count'];
        });

        return view('admin.game_data.participation_statistics', [
            'statistics' => $statistics,
            'start_date' => $startDate,
            'end_date' => $endDate,
        ]);
    }



}