admin_api/app/Http/logic/admin/GlobalLogicController.php

<?php

namespace App\Http\logic\admin;

use App\Facade\TableName;
use App\Game\AgentUser;
use App\Game\GameCard;
use App\Game\GlobalUserInfo;
use App\Http\helper\Helper;
use App\Http\helper\NumConfig;
use App\Models\Account\UserBlacklist;
use App\Models\AccountsInfo;
use App\Models\AccountsSource;
use App\Models\Control;
use App\Models\Cpf;
use App\Models\Order;
use App\Services\Extensions;
use App\Services\GameRoomInfo;
use App\Services\GamesCount;
use App\Services\StoredProcedure;
use Illuminate\Http\Request;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class GlobalLogicController extends BaseLogicController
{
    // 定义税收值
    protected $Revenue = 100;

    // 单控系统
    public function dk($start_time, $end_time, $date)
    {

        // 今日流水，今日税收
        $todayGameInfo = DB::connection('read')->table('QPRecordDB.dbo.RecordServerDataStatistics')
            ->selectRaw('Isnull((sum(WinScore) + abs(sum(LostScore))),0) as flowing_water,Isnull(sum(Revenue),0) as Revenue')
            ->where('DateID', date('Ymd'))
            ->first();

        //今日税收
        $data['Revenue'] = isset($todayGameInfo->Revenue) ? number_float($todayGameInfo->Revenue / NumConfig::NUM_VALUE) : 0;
        //今日流水
        $data['flowing_water'] = isset($todayGameInfo->flowing_water) ? number_float(($todayGameInfo->flowing_water + $todayGameInfo->Revenue) / NumConfig::NUM_VALUE) : 0;


        //今日总充值金额
        $data['pay_sum'] = Order::today_pay()->today_pay_sum / NumConfig::NUM_VALUE ?? 0;

        // 今日总提现
        $data['today_withdrawal'] = DB::connection('read')->table("QPAccountsDB.dbo.OrderWithDraw as od")
                ->join('QPAccountsDB.dbo.AccountsRecord as ar', 'od.RecordID', 'ar.RecordID')
                ->whereDate('update_at', date('Y-m-d'))
                ->where('State', 2)
                ->selectRaw('IsNull((sum(WithDraw) + sum(ServiceFee)),0) WithDraw')
                ->first()->WithDraw / NumConfig::NUM_VALUE ?? 0;

        //今日彩金
        $data['cellData'] = DB::connection('read')->table('QPRecordDB.dbo.RecordUserDataStatisticsNew')
                ->selectRaw('Isnull(SUM(Handsel),0) as Handsel')
                ->where('DateID', date('Ymd'))
                ->first()->Handsel / NumConfig::NUM_VALUE ?? 0;

        // 今日杀率 => 被控制输的用户在总活跃用户中的占比
        // ---- 控制输的总用户
        $shu_member = DB::connection('read')->table('QPTreasureDB.dbo.UserScoreControl')
            ->where('ControlRadian', '<', 0)
            ->count();

        // ---- 总活跃用户占比
        $huoyue = DB::connection('read')->table('QPAccountsDB.dbo.accountsInfo')
            ->whereRaw("DateDiff(dd,LastLogonDate,getdate())<=7")
            ->count();

        // 今日杀率
        $data['shalv'] = number_float($shu_member / $huoyue);


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


        switch ($date) {
            case 2:
                $start_time = date("Y-m-d 00:00:00", strtotime("-1 day"));
                $end_time = date("Y-m-d 23:59:59", strtotime("-1 day"));
                break;
            case 3:
                //当前日期
                $sdefaultDate = date("Y-m-d 00:00:00");
                //$first =1 表示每周星期一为开始日期 0表示每周日为开始日期
                $first = 1;
                //获取当前周的第几天 周日是 0 周一到周六是 1 - 6
                $w = date('w', strtotime($sdefaultDate));
                $start_time = date('Y-m-d 00:00:00', strtotime("$sdefaultDate -" . ($w ? $w - $first : 6) . ' days'));
                $end_time = date('Y-m-d 23:59:59', strtotime("$start_time +6 days"));
                break;
            case 4:
                $start_time = date("Y-m-01 00:00:00");
                $end_time = date("Y-m-t 23:59:59");
                break;
        }

        /*                     游戏房间数据              */
        $gameRoomInfo = DB::connection('read')->table('QPRecordDB.dbo.RecordServerDataStatistics as rds')
            ->where('DateID', '>=', date('Ymd', strtotime($start_time)))
            ->where('DateID', '<=', date('Ymd', strtotime($end_time)))
            ->selectRaw('Isnull((sum(WinScore) + abs(sum(LostScore))),0) as flowing_water,Isnull(sum(Revenue),0) as Revenue,Isnull((sum(WinScore) + abs(sum(LostScore))),0) as win_lose')
            ->first();

        //平台输赢 目前计算的是机器人输赢
        $gameRoomInfo->win_lose = isset($gameRoomInfo->win_lose) ? number_float(-$gameRoomInfo->win_lose / NumConfig::NUM_VALUE) : 0;
        //流水
        $gameRoomInfo->flowing_water = isset($gameRoomInfo->flowing_water) ? number_float(($gameRoomInfo->flowing_water + $gameRoomInfo->Revenue) / NumConfig::NUM_VALUE) : 0;
        //税收
        $gameRoomInfo->Revenue = isset($gameRoomInfo->Revenue) ? number_float($gameRoomInfo->Revenue / NumConfig::NUM_VALUE) : 0;


        $game_list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo as gi')
            ->leftJoin('QPRecordDB.dbo.RecordServerDataStatistics as rds', 'rds.ServerID', 'gi.ServerID')
            ->where('DateID', '>=', date('Ymd', strtotime($start_time)))
            ->where('DateID', '<=', date('Ymd', strtotime($end_time)))
            ->select('gi.GameID', 'gi.ServerName', 'gi.ServerID')
            ->selectRaw('Isnull((sum(WinScore) + abs(sum(LostScore))),0) as flowing_water,Isnull(sum(Revenue),0) as Revenue,Isnull((sum(WinScore) + abs(sum(LostScore))),0) as win_lose')
            ->groupBy('gi.GameID', 'gi.ServerName', 'gi.ServerID')
            ->get();

        foreach ($game_list as &$value) {
            $value->Score = number_float(-($value->win_lose / NumConfig::NUM_VALUE));   // 平台输赢
            $value->ri_Revenue = number_float($value->Revenue / NumConfig::NUM_VALUE); // 税收
            $value->liushui = number_float(($value->flowing_water) / NumConfig::NUM_VALUE); // 流水

        }

        $start_time = Helper::timeChange($start_time);
        $end_time = Helper::timeChange($end_time);
        return compact('data', 'game_list', 'gameRoomInfo', 'start_time', 'end_time', 'date');
    }


    public function sum()
    {
        // 玩家身上总金额
        $totalAmount = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo as ai')
            ->join('QPTreasureDB.dbo.GameScoreInfo as gi', 'ai.UserID', 'gi.UserID')
            ->leftJoin('QPAccountsDB.dbo.IDWhiteUser as wu', 'ai.UserID', 'wu.UserID')
            ->where('IsAndroid', 0)
            ->where('Nullity', 0)
            ->whereNull('wu.UserID')
            ->sum('Score');

        // 官方玩家身上总金额
        $official = DB::connection('read')->table('QPAccountsDB.dbo.IDWhiteUser as wu')
            ->join('QPTreasureDB.dbo.GameScoreInfo as gi', 'wu.UserID', 'gi.UserID')
            ->sum('Score');

        // 充值用户身上总金额--排除官方用户
        $payUser = DB::connection('read')->table('QPAccountsDB.dbo.YN_VIPAccount as vip')
            ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gi', 'vip.UserID', 'gi.UserID')
            ->leftJoin('QPAccountsDB.dbo.IDWhiteUser as wu', 'vip.UserID', 'wu.UserID')
            ->whereNull('wu.UserID')
            ->selectRaw('sum(Score) as Score')
            ->first()->Score;


        // Tp群控总库存
        $TpRoomStock = GameRoomInfo::RoomStock([1005], [32]);
        // Rm群控总库存
        $RmRoomStock = GameRoomInfo::RoomStock([2030, 2050], [33, 39]);

        // 总库存
        $groupControlTotalStock = $TpRoomStock + $RmRoomStock;

        // 房间总库存 = 群控库存 +  单控库存 + 新手池库存 + 旧房间库存
        $oldStock = -2289781;
        $oldStock1 = -2088570;
        $roomTotalStock = $groupControlTotalStock + ($oldStock) + ($oldStock1);

        return compact('totalAmount', 'official', 'payUser', 'groupControlTotalStock', 'roomTotalStock', 'TpRoomStock', 'RmRoomStock', 'oldStock', 'oldStock1');
    }

    // 月卡
    public function MonthCard()
    {
        // 白银
        $SilverVIP = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '1')
            ->first();
        // 黄金
        $GoldVIP = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '2')
            ->first();
        // 钻石
        $DiamondVIP = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '3')
            ->first();

        // 累计购买金额
        $TotalPrice = $SilverVIP->TotalPrice + $GoldVIP->TotalPrice + $DiamondVIP->TotalPrice;

        // 累计领取金额
        $TotalReward = $SilverVIP->TotalReward + $GoldVIP->TotalReward + $DiamondVIP->TotalReward;

        // 额外赠送金额 --- VIP月卡累计领取金额 - VIP月卡累计购买金额=VIP月卡额外赠送金额。
        $givePrice = $TotalReward - $TotalPrice > 0 ? $TotalReward - $TotalPrice : 0;

        // 总得付费人数
        $totalPay = DB::connection('read')->table('agent.dbo.order')->where('pay_status', 1)->selectRaw('count(distinct(user_id)) count_U')->first()->count_U ?? 0;

        // 付费玩家整体月卡购买人数
        $monthCardPay = DB::connection('read')->table('QPPlatformDB.dbo.UserMonthCard')
                ->selectRaw('count(distinct(UserID)) count_U')
                ->first()->count_U ?? 0;

        // 付费玩家白银月卡购买人数
        $silverPay = DB::connection('read')->table('QPPlatformDB.dbo.UserMonthCard')
                ->where('CardID', 1)
                ->selectRaw('count(distinct(UserID)) count_U')
                ->first()->count_U ?? 0;

        // 付费玩家黄金月卡购买人数
        $goldPay = DB::table('QPPlatformDB.dbo.UserMonthCard')
                ->where('CardID', 2)
                ->selectRaw('count(distinct(UserID)) count_U')
                ->first()->count_U ?? 0;

        // 付费玩家钻石月卡购买人数
        $diamondPay = DB::connection('read')->table('QPPlatformDB.dbo.UserMonthCard')
                ->where('CardID', 3)
                ->selectRaw('count(distinct(UserID)) count_U')
                ->first()->count_U ?? 0;

        $totalRate = $totalPay > 0 ? number_float(($monthCardPay / $totalPay) * NumConfig::NUM_VALUE) . ' %' : 0;
        $silverRate = $totalPay > 0 ? number_float(($silverPay / $totalPay) * NumConfig::NUM_VALUE) . ' %' : 0;
        $diamondRate = $totalPay > 0 ? number_float(($diamondPay / $totalPay) * NumConfig::NUM_VALUE) . ' %' : 0;
        $goldRate = $totalPay > 0 ? number_float(($goldPay / $totalPay) * NumConfig::NUM_VALUE) . ' %' : 0;
        $TotalPrice = number_float($TotalPrice / NumConfig::NUM_VALUE);
        $TotalReward = number_float($TotalReward / NumConfig::NUM_VALUE);
        $givePrice = number_float($givePrice / NumConfig::NUM_VALUE);


        return compact('SilverVIP', 'GoldVIP', 'DiamondVIP', 'TotalPrice', 'TotalReward', 'givePrice', 'totalRate', 'silverRate', 'goldRate', 'diamondRate');

    }

    public function FirstRechargeGifts()
    {
        // 白银首充礼包支付金额
        $silver = DB::connection('read')
            ->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.FirstRechargeGifts as frg', 'o.GiftsID', 'frg.GiftsID')
            ->where('o.GiftsID', 1)
            ->where('o.pay_status', 1)
            ->selectRaw('IsNull(sum(amount),0) amount,IsNull(TotalCount,0) TotalCount')
            ->groupBy('TotalCount')
            ->lock('with(nolock)')
            ->first();

        // 黄金首充礼包支付金额
        $gold = DB::connection('read')
            ->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.FirstRechargeGifts as frg', 'o.GiftsID', 'frg.GiftsID')
            ->where('o.GiftsID', 2)
            ->where('o.pay_status', 1)
            ->selectRaw('IsNull(sum(amount),0) amount,IsNull(TotalCount,0) TotalCount')
            ->groupBy('TotalCount')
            ->lock('with(nolock)')
            ->first();

        // 钻石首充礼包支付金额
        $diamonds = DB::connection('read')
            ->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.FirstRechargeGifts as frg', 'o.GiftsID', 'frg.GiftsID')
            ->where('o.GiftsID', 3)
            ->where('o.pay_status', 1)
            ->selectRaw('IsNull(sum(amount),0) amount,IsNull(TotalCount,0) TotalCount')
            ->groupBy('TotalCount')
            ->lock('with(nolock)')
            ->first();
        // 注册日期--开始时间
        $date = '2021-07-30 00:00:00';

        $silverMemberCount = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')->where('RegisterDate', '>=', $date)->count();
        $goldMemberCount = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')->where('RegisterDate', '>=', $date)->count();
        $diamondsMemberCount = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')->where('RegisterDate', '>=', $date)->count();

        // 白银首充礼包购买率
        $silverBuyRate = ($silver && $silverMemberCount > 0) ? number_float(($silver->TotalCount / $silverMemberCount) * NumConfig::NUM_VALUE) . '%' : 0;
        // 黄金首充礼包购买率
        $goldBuyRate = ($gold && $goldMemberCount > 0) ? number_float(($gold->TotalCount / $goldMemberCount) * NumConfig::NUM_VALUE) . '%' : 0;
        // 钻石首充礼包购买率
        $diamondsBuyRate = ($diamonds && $diamondsMemberCount > 0) ? number_float(($diamonds->TotalCount / $diamondsMemberCount) * NumConfig::NUM_VALUE) . '%' : 0;

        // 首充礼包额外赠送总彩金
        $lottery = DB::connection('read')->table('QPRecordDB.dbo.PD_RecordScoreInfo')
            ->where('Reason', 51)
            ->sum('Score');

        $lottery = number_float($lottery / NumConfig::NUM_VALUE);

        $waitPay = DB::connection('read')
            ->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.FirstRechargeGifts as frg', 'o.GiftsID', 'frg.GiftsID')
            ->whereNull('o.pay_at')
            ->whereIn('o.GiftsID', [1, 2, 3])
            ->lock('with(nolock)')
            ->count();

        $paySuc = DB::connection('read')
            ->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.FirstRechargeGifts as frg', 'o.GiftsID', 'frg.GiftsID')
            ->where('o.pay_status', 1)
            ->whereIn('o.GiftsID', [1, 2, 3])
            ->lock('with(nolock)')
            ->count();

        // 所有首充礼包购买成功率
        $paySucRate = $waitPay > 0 ? number_float(($paySuc / $waitPay) * NumConfig::NUM_VALUE) . '%' : 0;

        if ($silver)
            $silver->amount = number_float($silver->amount / NumConfig::NUM_VALUE);

        if ($gold)
            $gold->amount = number_float($gold->amount / NumConfig::NUM_VALUE);

        if ($diamonds)
            $diamonds->amount = number_float($diamonds->amount / NumConfig::NUM_VALUE);


        return compact('silver', 'gold', 'diamonds', 'silverBuyRate', 'goldBuyRate', 'diamondsBuyRate', 'lottery', 'paySucRate');

    }

    public function WeeklyCard()
    {

        // 未支付
        $totalWaitPay = DB::connection('read')
            ->table('agent.dbo.order as o')
            ->where('o.pay_status', 0)
            ->whereIn('o.GiftsID', [104, 105, 106, 107])
            ->selectRaw('IsNull(sum(amount),0) amount')
            ->lock('with(nolock)')
            ->first();

        // 周卡VIP1
        $weekVIP1 = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '4')
            ->select('Price', 'TotalPrice', 'TotalCount', 'TotalReward')
            ->first();
        // 周卡VIP2
        $weekVIP2 = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '5')
            ->select('Price', 'TotalPrice', 'TotalCount', 'TotalReward')
            ->first();
        // 周卡VIP3
        $weekVIP3 = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '6')
            ->select('Price', 'TotalPrice', 'TotalCount', 'TotalReward')
            ->first();
        // 周卡VIP4
        $weekVIP4 = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->where('CardID', '7')
            ->select('Price', 'TotalPrice', 'TotalCount', 'TotalReward')
            ->first();

        // 累计购买金额
        $TotalPrice = $weekVIP1->TotalPrice + $weekVIP2->TotalPrice + $weekVIP3->TotalPrice + $weekVIP4->TotalPrice;

        // 累计领取金额
        $TotalReward = $weekVIP1->TotalReward + $weekVIP2->TotalReward + $weekVIP3->TotalReward + $weekVIP4->TotalReward;


        // 额外赠送金额 --- VIP月卡累计领取金额 - VIP月卡累计购买金额=VIP月卡额外赠送金额。
        //$givePrice = $TotalReward - $TotalPrice > 0 ? $TotalReward - $TotalPrice : 0;

        // 拉起的总单数
        $totalNum = DB::connection('read')->table('agent.dbo.order')
            ->whereIn('GiftsID', [104, 105, 106, 107])
            ->selectRaw('count(id) count,GiftsID')
            ->groupBy('GiftsID')
            ->pluck('count', 'GiftsID')->toArray();
        // 购买率 =  购买成功的单数 / 拉起的单数

        $VIP1Rate = isset($totalNum['104']) && $totalNum['104'] ? number_float(($weekVIP1->TotalCount / $totalNum['104']) * NumConfig::NUM_VALUE) . ' %' : 0;
        $VIP2Rate = isset($totalNum['105']) && $totalNum['105'] ? number_float(($weekVIP2->TotalCount / $totalNum['105']) * NumConfig::NUM_VALUE) . ' %' : 0;
        $VIP3Rate = isset($totalNum['106']) && $totalNum['106'] ? number_float(($weekVIP3->TotalCount / $totalNum['106'])) . ' %' : 0;
        $VIP4Rate = isset($totalNum['107']) && $totalNum['107'] ? number_float(($weekVIP4->TotalCount / $totalNum['107'])) . ' %' : 0;

        $totalWaitPay = isset($totalWaitPay->amount) ? $totalWaitPay->amount / NumConfig::NUM_VALUE : 0;
        $TotalReward = number_float($TotalReward / NumConfig::NUM_VALUE);
        return compact('VIP1Rate', 'VIP2Rate', 'VIP3Rate', 'VIP4Rate', 'totalWaitPay', 'TotalPrice', 'TotalReward', 'weekVIP1', 'weekVIP2', 'weekVIP3', 'weekVIP4');
    }

    // 房间实时数据
    public function room($list)
    {
        /*
        $WinUserCount = DB::connection('read')->table('QPRecordDB.dbo.RecordGameInfo')
            ->whereDate('CreateTime', date('Y-m-d'))
            ->selectRaw('count(distinct(UserID)) as count_u,ServerID')
            ->havingRaw('sum(Score) > ?', [0])
            ->groupBy('ServerID')
            ->pluck('count_u', 'ServerID');

        $LostUserCount = DB::connection('read')->table('QPRecordDB.dbo.RecordGameInfo')
            ->whereDate('CreateTime', date('Y-m-d'))
            ->selectRaw('count(distinct(UserID)) as count_u,ServerID')
            ->havingRaw('sum(Score) < ?', [0])
            ->groupBy('ServerID')
            ->pluck('count_u', 'ServerID');
        */
        $WinUserCount = DB::connection('read')->table('QPRecordDB.dbo.PD_RecordScoreInfo')
            ->selectRaw('count(distinct(UserID)) as count_u,ServerID')
            ->where('AtDate', '=', date('Ymd'))
            ->where('Score', '>', 0)
            ->groupBy('ServerID')
            ->pluck('count_u', 'ServerID');

        $LostUserCount = DB::connection('read')->table('QPRecordDB.dbo.PD_RecordScoreInfo')
            ->selectRaw('count(distinct(UserID)) as count_u,ServerID')
            ->where('AtDate', '=', date('Ymd'))
            ->where('Score', '<', 0)
            ->groupBy('ServerID')
            ->pluck('count_u', 'ServerID');

        foreach ($list as &$value) {

            // 库存
            $stock_toarr = explode(';', rtrim($value->RoomStock, ';'));

            if (isset($stock_toarr[0]) && !empty($stock_toarr[0])) {
                $stock = explode(':', $stock_toarr[0])[1];
            } else {
                $stock = 0;
            }


            // 当天房间胜率  ---  胜的局数 / 玩的总局数
            $value->winning_probability = $value->TotalInning > 0 ? number_float($value->WinInning / $value->TotalInning) : 0;

            $value->stock = $stock / NumConfig::NUM_VALUE;
            // 流水
            $value->flowing_water = number_float(($value->WinScore + abs($value->LostScore)) / NumConfig::NUM_VALUE);
            // 总输赢
            $value->win_lose = number_float(($value->WinScore + $value->LostScore) / NumConfig::NUM_VALUE);
            // 税收
            $value->Revenue = number_float($value->Revenue / NumConfig::NUM_VALUE);

            // 当天赢人数
            $value->WinUserCount = $WinUserCount[$value->ServerID] ?? 0;
            // 当天输人数
            $value->LostUserCount = $LostUserCount[$value->ServerID] ?? 0;
        }

        return $list;
    }

    public function teenpatti($ServerID)
    {

        $result = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
            ->where('ServerID', $ServerID)
            ->select('CellScore', 'MinEnterScore', 'MaxEnterScore', 'ServerName', 'CellScore')
            ->first();

        $result->CellScore = $result->CellScore / 100;
        $result->MinEnterScore = number_float($result->MinEnterScore / 100);
        $result->MaxEnterScore = number_float($result->MaxEnterScore / 100);

        return compact('result');
    }


    // 多选框搜索
    public function user_search($build_sql, $data)
    {

        if (!is_array($data)) {
            $data = [];
        }
        if (in_array(1, $data)) {
            $data['checked1'] = 1;
            // 游戏中用户
//            $build_sql->whereNotNull("ServerName")->whereDate('CollectDate', date('Y-m-d'));;
            $build_sql->whereRaw('datediff(hh,CollectDate,getdate())<=5');
        }
        if (in_array(2, $data)) {
            $data['checked2'] = 2;
            $build_sql->where('ai.PassPortID', '');
        }
        if (in_array(3, $data)) {
            $data['checked3'] = 3;
            $build_sql->whereNull('ap.PhoneNum');
        }
        if (in_array(4, $data)) {
            $data['checked4'] = 4;
            $IP = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->selectRaw('LastLogonIP,count(*) as count ')
                ->groupBy('LastLogonIP')
                ->havingRaw('count(*) >1')
                ->get();
            if (!empty($IP)) {
                $arr = [];
                foreach ($IP as $k => $v) {
                    if ($v->LastLogonIP != '未知') {
                        $arr[$k] = $v->LastLogonIP;
                    }
                }

                $build_sql->whereIn('ai.RegisterIP', $arr);
            }
        }

        if (in_array(5, $data)) {
            $data['checked5'] = 5;
            // 无渠道
            $build_sql->where('ai.Channel', 0);
        }
        if (in_array(6, $data)) {
            $data['checked6'] = 6;
            $VIP = DB::connection('read')->table('QPAccountsDB.dbo.YN_VIP')->select('ID')->get();
            $vip_id = [];
            if (!empty($VIP)) {
                foreach ($VIP as $k => $v) {
                    $vip_id[$k] = $v->ID;
                }
            }
            $build_sql->whereNotIn('ai.UserID', $vip_id);
        }
        if (in_array(7, $data)) {
            $data['checked7'] = 7;
            $build_sql->where('ai.SpreaderID', '0');
        }
        if (in_array(8, $data)) {
            $data['checked8'] = 8;
            $build_sql->where('ai.Nullity', '1');
        }
        if (in_array(9, $data)) {
            $data['checked9'] = 9;
            $build_sql->whereNotNull('iu.UserID');
        }

        return compact('build_sql', 'data');
    }

    public function id_find($UserID, $OpenPage)
    {
        $AccountsInfoModel = new AccountsInfo();
        //玩家信息
//        $userInfo = DB::table(TableName::QPAccountsDB() . 'AccountsInfo')
//            ->where('UserID', $UserID)
//            ->first();

        $userInfo = AccountsInfo::find($UserID);
//        dd($userInfo->gameScoreInfo);

        // 用户余额
        $score = $userInfo->gameScoreInfo->Score;

        $insureScore = $userInfo->gameScoreInfo->InsureScore;
        // 上级ID
        //$userInfo->spreaderID = $userInfo->SpreaderID;
        // 上级ID
//        $userInfo->spreaderGameId = AccountsInfo::GetUserGameID($userInfo->spreaderID);
        // 用户手机号
        $userInfo->phone = $userInfo->accountPhoneRelation->PhoneNum??"";
        // 用户游戏时长转换
        $userInfo->PlayTimeCount = $AccountsInfoModel->getUserPlayTimeCount($userInfo->PlayTimeCount);
        // 用户有没有在游戏
        $userInfo->getUserOnLine = $AccountsInfoModel->getUserOnLine($UserID);

        // 今日充值-- 今日提现-- 今日彩金 -- 总输赢
        $today = $AccountsInfoModel->accountTodayStatistics([$UserID]);

        // 全部充值 -- 提现 -- 彩金 -- 总输赢 -- 总得提现手续费
        $total = $AccountsInfoModel->accountTotalStatistics([$UserID]);

        // 未领取邮件金额
        $waitGetEmailScore = $AccountsInfoModel->waitGetEmailScore($UserID);

        // 月卡 -- 购买、已领
        [$buyMonthCard, $getMonthCard] = $AccountsInfoModel->monthCard($UserID);

        // 可提现金额
        $remainingBalance = $AccountsInfoModel->CashAble($UserID, 0, 0);

        // 关联IP数量-- 注册IP
        $userInfo->sameIpCount = $AccountsInfoModel->sameRegisterIPCount($userInfo->RegisterIP);

        $userInfo->samePhoneCount = $userInfo->phone?$AccountsInfoModel->samePhoneCount($userInfo->phone):0;

        $userInfo->sameWithDrawName = $AccountsInfoModel->sameWithDrawBankNameByUserID($UserID);
        // 关联IP数量-- 注册IP
        $userInfo->sameCpfCount = Cpf::getCpfCount($UserID);
        $rechargeCpf = Cpf::getCpf($UserID,1);
        $userInfo->rechargeCpf = $rechargeCpf?implode(',',$rechargeCpf):'';

        $userInfo->sameWithDrawMail = $AccountsInfoModel->sameWithDrawEmailByUserID($UserID);

        // 登录IP
        $userInfo->LastLogonIP = $AccountsInfoModel->sameLoginIPCount($UserID);

        // 关联银行卡数量
        $userInfo->sameBankNo = $AccountsInfoModel->sameBankNo($UserID);
        //关联设备
        $userInfo->sameMac = $AccountsInfoModel->sameLoginMacCount($UserID);
        // 充值奖金  已领
        $rechargeReward = $AccountsInfoModel->UserScoreChange($UserID, 53);
        // 充值奖金  可领
        $rechargeLastReward = (new Extensions())->recharge($UserID);

        // 推广赚金 注册已领
        $shareReward = $AccountsInfoModel->UserScoreChange($UserID, 72);
        // 推广赚金 注册可领
        $shareLastReward = (new Extensions())->register($UserID)['Register'];

        // 包名
        $userInfo->PackgeName = DB::connection('read')->table('QPRecordDB.dbo.RecordPackageName')
                ->where('UserID', $UserID)
                ->select('PackgeName')
                ->first()->PackgeName ?? '';

        // 裂变领取限制开关
        $redis = Redis::connection();
        $registerInviteSwitches = $redis->get('register_invite_switches_' . $UserID);

        // 黑名单
        $blacklist = UserBlacklist::where('UserID', $userInfo->UserID)->first();
        $userInfo->blacklist = $blacklist ? 1 : 0;

        $user = GlobalUserInfo::getGameUserInfo('UserID', $userInfo->UserID);
        $userInfo->GlobalUID = @$user->GlobalUID?:'';


        $agentUser=AgentUser::query()->where('UserID',$userInfo->UserID)->first();
        if($agentUser){
            $userInfo->spreaderID = $agentUser->Higher1ID;
        }else{
            $userInfo->spreaderID = 0;
        }
        $Record=DB::table('QPAccountsDB.dbo.OrderWithDraw')->where('UserID', $UserID)->where('State',1)->first();
        $WithDrawNow=$Record?($Record->WithDraw-$Record->ServiceFee):0;

        $AccountWithDrawInfo = DB::table(TableName::QPAccountsDB() . 'AccountWithDrawInfo')
            ->lock('WITH(NOLOCK)')
            ->where('UserID', $UserID)
            ->first();

        $data = [

            'today_cellData' => $today[0]->Handsel ?? 0,
            'total_cellData' => $total[0]->Handsel ?? 0,
            'withdraw' =>  $total[0]->Withdraw ?? 0,
            'serviceFee' => $total[0]->ServiceFee ?? 0,
            'MaxDrawBase' => $total[0]->MaxDrawBase ?? 0,
            'today_withdraw' => $today[0]->Withdraw ?? 0,
            'total_profit' => $total[0]->Score ?? 0,
            'today_profit' => $today[0]->Score ?? 0,
            'total_realpay' => -(($total[0]->Recharge??0)*NumConfig::NUM_VALUE-($total[0]->Withdraw ??0)+($total[0]->ServiceFee ??0)-$WithDrawNow),
            'today_realpay' => -(($today[0]->Recharge??0)*NumConfig::NUM_VALUE-($today[0]->Withdraw ?? 0)+($today[0]->ServiceFee ?? 0)-$WithDrawNow),
            'revenue' => $total[0]->Revenue ?? 0,
            'buyMonthCard' => $buyMonthCard,
            'getMonthCard' => $getMonthCard,
            'remainingBalance' => $remainingBalance,
            'rechargeReward' => $rechargeReward,
            'rechargeLastReward' => $rechargeLastReward,
            'shareReward' => $shareReward,
            'shareLastReward' => $shareLastReward,
            'waitGetEmailScore' => $waitGetEmailScore,
            'score' => $score,
            'insureScore' => $insureScore,
            'todayMaxScore' => $today[0]->MaxScore ?? 0,
            'todayMaxWinScore' => $today[0]->MaxWinScore ?? 0,

        ];

        foreach ($data as $key=>&$value) {
            try {
                $value = number_float($value / NumConfig::NUM_VALUE);
            }catch (\Exception $exception){
                echo $key;
                var_dump($data);
                die;
            }
        }
        $data['pix_cpf'] = $AccountWithDrawInfo?$AccountWithDrawInfo->PixNum:0;
        // 不用/100
        $data['today_cz'] = $today[0]->Recharge ?? 0;
        $data['total_cz'] = $total[0]->Recharge ??  0;
        $data['remainingBalance'] = $remainingBalance;

        // 对局
        $gameCount = (new GamesCount())->gameCount($UserID);
        // 用户来源
        $userSource = (new AccountsSource())->getUserSource($UserID);
        $platform = ['only','atmosfera','pg','pp'];
        $platformData = [];
        foreach ($platform as $pitem){
            $dkey = 'platform_' . $pitem .'_'.$UserID.'_win_'.date('Ymd');
            $key = 'platform_' . $pitem .'_'.$UserID.'_win';
            $platformData[$pitem]['total'] = (Redis::get($key)?:0)/100;
            $platformData[$pitem]['today'] = (Redis::get($dkey)?:0)/100;
        }
        // 手机型号
        $data['mobileBand'] = '';
        $res = Redis::get('user_ua_' . ($user->UserID ?? ''));
        if ($res) {
            $ua = $res ?? '';
            if (stripos($ua, 'iPhone') !== false) {
                $data['mobileBand'] = 'iPhone';
            }
            if (preg_match('/Android\s[\d\.]+;\s([^)]+)/i', $ua, $match)) {
                $data['mobileBand'] = 'Android' . trim($match[1]);
            }
            $data['mobileBand'] .= "(".mb_substr(trim($ua), 0, 25).")";
        }

        return compact('data', 'userInfo', 'registerInviteSwitches', 'gameCount', 'userSource', 'OpenPage','platformData');
    }

    public function id_find1($UserID, $OpenPage)
    {
        $AccountsInfoModel = new AccountsInfo();
        //玩家信息
        $User = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo as ai')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai1', 'ai.SpreaderID', '=', 'ai1.UserID')
            ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gi', 'ai.UserID', '=', 'gi.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountPhone as ap', 'ai.UserID', 'ap.UserID')
            ->where('ai.UserID', $UserID)
            ->select('ai.*', 'ai1.GameID as SpreaderID', 'ap.PhoneNum', 'gi.Score', 'gi.Revenue', 'gi.InsureScore', 'gi.Score as gi_Score', 'gi.InsureScore as gi_InsureScore')
            ->first();

        if (empty($User)) return apiReturnFail('用户不存在');


        //////////
        /// 游戏时长
        $second = $User->PlayTimeCount;
        $day = floor($second / (3600 * 24));
        $second = $second % (3600 * 24);//除去整天之后剩余的时间
        $hour = floor($second / 3600);
        $second = $second % 3600;//除去整小时之后剩余的时间
        $minute = floor($second / 60);
        //返回字符串
        $User->PlayGameTime = $day . '天' . $hour . '小时' . $minute . '分';


        // 有没有在房间
        $User->ServerName = DB::connection('read')->table('QPTreasureDB.dbo.GameScoreLocker as gsl')
                ->join('QPPlatformDB.dbo.GameRoomInfo as gri', 'gsl.ServerID', 'gri.ServerID')
                ->where('UserID', $UserID)
                ->whereRaw('datediff(hh,CollectDate,getdate())<=5')
                ->select('ServerName')
                ->first()->ServerName ?? '';


        // 用户标签
        $accountsTab = StoredProcedure::getUserTab($UserID);
        $TabType = $accountsTab[0]->TabType ?? '';

        $types = [];
        if (!empty($accountsTab) && $TabType == 1) {

            foreach ($accountsTab[0] as $k => $v) {

                if ($k != 'UserID' || $k != 'TabType') {
                    $types[] = $v;
                }
            }

        }

        $GetUserTabTypeEx = StoredProcedure::GetUserTabTypeEx($UserID);

        $GetUserTabTypeExType = [];
        foreach ($GetUserTabTypeEx as $val) {
            $GetUserTabTypeExType[] = $val->type;
        }

        $types = array_merge($types, $GetUserTabTypeExType);
        $explain = DB::connection('read')->table('QPAccountsDB.dbo.AccountsTabExplain')
            ->whereIn('TabID', $types)
            ->pluck('TypeName', 'TabID');

        // 走势图
        $r = DB::connection('game')->table(TableName::QPTreasureDB() . 'YN_RecordScoreInfo_' . date('Ym'))
            ->selectRaw("Isnull(SUBSTRING(CONVERT(VARCHAR(13),UpdateTime,120),12,5),0) as Stime,Isnull(sum(ChangeScore),0) as Score")
            ->where('UserID', $UserID)
            ->whereDate('UpdateTime', date("Y-m-d"))
            ->groupBy(DB::raw('CONVERT(VARCHAR(13), UpdateTime, 120)'))
            ->get()->toArray();

        $list = $this->get_str($r);

        // +---------------------------充值--------------------------------

        // 今日充值-- 今日提现-- 今日彩金 -- 总输赢
        $today = $AccountsInfoModel->accountTodayStatistics([$UserID]);

        // 全部充值 -- 提现 -- 彩金 -- 总输赢 -- 总得提现手续费
        $total = $AccountsInfoModel->accountTotalStatistics([$UserID]);

        // 未领取邮件金币
        $mail = DB::connection('read')->table('QPAccountsDB.dbo.PrivateMail')
            ->where('UserID', $UserID)
            ->where('MailStatus', '<', 3)
            ->select('UserID', 'BonusString')
            ->get();
        foreach ($mail as $val) {
            $arr = explode(',', $val->BonusString);
            if (!empty($arr) && isset($arr[0]) && $arr[0] == 30000) {
                if (isset($mailData[$val->UserID])) {
                    $mailData[$val->UserID] = $mailData[$val->UserID] + $arr[1] ?? 0;
                } else {
                    $mailData[$val->UserID] = $arr[1];
                }
            }
        }


        // 月卡 -- 购买
        $buyMonthCard = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard as mc')
                ->join('QPPlatformDB.dbo.UserMonthCard as uc', 'mc.CardID', 'uc.CardID')
                ->where('uc.UserID', $UserID)
                ->selectRaw('IsNull(sum(Price),0) TotalReward')
                ->first()->TotalReward / 100 ?? 0;

        // 月卡 -- 已领
        $getMonthCard = DB::connection('read')->table('QPPlatformDB.dbo.UserMonthCard')
                ->where('UserID', $UserID)
                ->selectRaw('IsNull(sum(TotalReward),0) Reward')
                ->first()->Reward / 100 ?? 0;

        # 保底值
        $StatusValue = 0;

        // 可提现金额
        $remainingBalance = $AccountsInfoModel->CashAble($UserID, $User->Score, $StatusValue);
        // 用户标签展示
        $Type12 = $accountsTab[0]->Type12 ?? 0;
        $first = in_array($Type12, [1301, 1302, 1303]) ? true : false;

        $AccountWithDrawInfo = DB::connection('read')->table('QPAccountsDB.dbo.AccountWithDrawInfo')
            ->where('UserID', $UserID)
            ->select('Switch')
            ->first();

        if ($first) {
            $userTab = $AccountWithDrawInfo->Switch == 0 ? '已解除' : "<span style='color: #ff0000'>已禁止</span>";
        } else {
            $userTab = '未标记';
        }

        // 关联IP数量-- 注册IP
        $Ip = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->where('RegisterIP', $User->RegisterIP)->count();

        // 登录IP
        $LastLogonIP = DB::connection('read')->table('QPRecordDB.dbo.RecordUserLogonStatistics as a')
            ->whereExists(function ($query) {
                $query->from(TableName::QPRecordDB() . 'RecordUserLogonStatistics as b')
                    ->whereRaw('a.LogonIP=b.LogonIP');
            })
            ->where('UserID', $UserID)
            ->selectRaw('count(distinct(UserID)) count_u')
            ->first()->count_u;


        // 关联银行卡数量
        $BankNo = DB::connection('read')->table('QPAccountsDB.dbo.AccountWithDrawInfo as di')
            ->join('QPAccountsDB.dbo.AccountWithDrawInfo as dif', 'di.BankNo', 'dif.BankNo')
            ->where('di.UserID', $UserID)
            ->whereNotNull('di.BankNo')
            ->where('di.BankNo', '<>', '')
            ->selectRaw('count(1) count,di.BankNo')
            ->groupBy('di.BankNo')
            ->first();

        // 充值奖金  已领
        $Reward = DB::connection('read')->table('QPRecordDB.dbo.RecordUserScoreChange')
                ->where('Reason', 53)
                ->where('UserID', $UserID)
                ->selectRaw('sum(ChangeScore) Score')
                ->first()->Score / NumConfig::NUM_VALUE ?? 0;

        // 充值奖金  可领
        $recharge = (new Extensions())->recharge($UserID);

        $rechargeScore['Balance'] = number_float($recharge / NumConfig::NUM_VALUE);
        $rechargeScore['Reward'] = $Reward;

        // 推广赚金 注册已领
        $reward = DB::connection('read')->table('QPRecordDB.dbo.RecordUserScoreChange')
                ->where('Reason', 72)
                ->where('UserID', $UserID)
                ->selectRaw('sum(ChangeScore) Score')
                ->first()->Score / NumConfig::NUM_VALUE ?? 0;

        // 推广赚金 注册可领
        $collectable = (new Extensions())->register($UserID)['Register'];

        $registerScore['collectable'] = ($collectable / NumConfig::NUM_VALUE);
        $registerScore['verify'] = 0;
        $registerScore['Reward'] = $reward;

        $todayReward = DB::connection('read')->table('QPRecordDB.dbo.RecordUserScoreStatisticsNew')
                ->whereIn('ScoreType', [53, 72])
                ->where('UserID', $UserID)
                ->selectRaw('sum(Score) Score')
                ->first()->Score / NumConfig::NUM_VALUE ?? 0;

        // 包名
        $PackgeName = DB::connection('read')->table('QPRecordDB.dbo.RecordPackageName')
                ->where('UserID', $UserID)
                ->select('PackgeName')
                ->first()->PackgeName ?? '';

        // 个人池控制：
        $RecordRechargeControl = DB::connection('read')->table('QPRecordDB.dbo.RecordRechargeControl as rc')
            ->leftJoin('QPTreasureDB.dbo.RechargeControlConfig as cnf', 'rc.State', 'cnf.ID')
            ->where('UserID', $UserID)
            ->select('ConfigName', 'State', 'Prob')
            ->first();
        isset($RecordRechargeControl->Prob) && $RecordRechargeControl->Prob = ($RecordRechargeControl->Prob / NumConfig::NUM_VALUE);


        // 反水活动
        $UserGameBet = DB::connection('write')->table('QPTreasureDB.dbo.UserGameBet')
            ->where('UserID', $UserID)
            ->selectRaw('IsNull(cast(HistoryBet  as float),0) / 100 HistoryBet,IsNull(cast(NowBet  as float),0) / 100 NowBet,RewardTime')
            ->first();

        $RewardTime = $UserGameBet->RewardTime ?? '';

        if ($UserGameBet) {
            if (!empty($RewardTime) && strtotime(date('Y-m-d H:i:s')) > strtotime($RewardTime)) {
                $UserGameBet->Collectable = number_float($UserGameBet->NowBet);
                $UserGameBet->HistoryBet = number_float($UserGameBet->HistoryBet);
                $UserGameBet->NowBet = number_float($UserGameBet->NowBet);
            } else {
                $UserGameBet->HistoryBet = number_float($UserGameBet->HistoryBet);
                $UserGameBet->NowBet = number_float($UserGameBet->NowBet);
            }
        }

        // 裂变领取限制开关
        $redis = Redis::connection();
        $register_invite_switches = $redis->get('register_invite_switches_' . $UserID);

        $data = [

            'today_cz' => isset($today[0]->Recharge) ? $today[0]->Recharge : 0,
            'total_cz' => isset($total[0]->Recharge) ? $total[0]->Recharge : 0,
            'today_cellData' => isset($today[0]->Handsel) ? $today[0]->Handsel / NumConfig::NUM_VALUE : 0,
            'total_cellData' => isset($total[0]->Handsel) ? $total[0]->Handsel / NumConfig::NUM_VALUE : 0,
            'withdraw' => isset($total[0]->Withdraw) ? $total[0]->Withdraw / NumConfig::NUM_VALUE : 0,
            'ServiceFee' => isset($total[0]->ServiceFee) ? $total[0]->ServiceFee / NumConfig::NUM_VALUE : 0,
            'today_withdraw' => isset($today[0]->Withdraw) ? $today[0]->Withdraw / NumConfig::NUM_VALUE : 0,
            'total_profit' => isset($total[0]->Score) ? $total[0]->Score / NumConfig::NUM_VALUE : 0,
            'today_profit' => isset($today[0]->Score) ? $today[0]->Score / NumConfig::NUM_VALUE : 0,
            'revenue' => isset($total[0]->Revenue) ? $total[0]->Revenue / NumConfig::NUM_VALUE : 0,
            'buyMonthCard' => $buyMonthCard,
            'getMonthCard' => $getMonthCard,
            'remainingBalance' => $remainingBalance,
            'reward' => $reward,

        ];

        foreach ($data as &$value) {
            $value = number_float($value);
        }

        $User->Score = number_float($User->Score / 100);
        $User->notReceived = isset($mailData[$User->UserID]) ? number_float($mailData[$User->UserID] / 100) : 0;

        // 对局
        $gameCount = (new GamesCount())->gameCount($UserID);
        // 用户来源
        $UserSource = (new AccountsSource())->getUserSource($UserID);
        return compact('User', 'list', 'data', 'explain', 'userTab', 'Ip', 'BankNo', 'LastLogonIP', 'PackgeName', 'RecordRechargeControl', 'UserGameBet', 'OpenPage', 'rechargeScore', 'registerScore', 'todayReward', 'register_invite_switches', 'gameCount', 'UserSource');
    }


    // 计算流水的值
    public function liushui($where = [])
    {
        $ServerIDs = [32, 33, 39];  // 过滤试玩场

        //$liushui = DB::connection('read')->table('QPRecordDB.dbo.RecordGameInfo')
        //    ->where($where)
        //    ->whereNotIn('ServerID', $ServerIDs)
        //   ->selectRaw('Isnull(sum(ABS(Score)),0) as Score')
        //    ->first()->Score;

        $liushui = DB::connection('read')->table('QPRecordDB.dbo.PD_RecordScoreInfo')
            ->where($where)
            ->whereNotIn('ServerID', $ServerIDs)
            ->selectRaw('Isnull(sum(ABS(Score)),0) as Score')
            ->first()->Score;

        return $liushui;

    }


    // 返回字符串
    protected function get_str($list)
    {
        // 定义时间内容
        $time_arr = ["00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"];

        $new_arr = [];
        foreach ($time_arr as $key => $value) {
            if (!empty($list)) {
                foreach ($list as &$v) {
                    if ($value == $v->Stime) {
//                    $temp_arr['Stime'] = $v->Stime;
                        $temp_arr['Score'] = $v->Score;

                    } else {
//                    $temp_arr['Stime'] = $value;
                        $temp_arr['Score'] = 0;
                    }
                }
            } else {
                $temp_arr['Score'] = 0;
            }
            $new_arr[] = $temp_arr;
        }

        foreach ($new_arr as $v) {
            $v = join(",", $v); //可以用implode将一维数组转换为用逗号连接的字符串，join是别名
            $temp[] = $v;
        }

        $str = "[";

        foreach ($temp as $v) {

            $str .= $v . ",";
        }

        $str = substr($str, 0, -1);

        $str .= "]";

        return $str;

    }

    // 推广员奖励报表
    public function reward($UserID, $SpreaderID, $start_time, $end_time, $Type)
    {


        $where = [];
        !empty($UserID) ? $where[] = ['ai.GameID', '=', $UserID] : $UserID = '';
        !empty($SpreaderID) ? $where[] = ['ain.GameID', '=', $SpreaderID] : $SpreaderID = '';

        !empty($start_time) && $where[] = ['SendTime', '>=', $start_time];
        !empty($end_time) && $where[] = ['SendTime', '<=', $end_time];

        !empty($Type) && $where[] = ['ua.Type', $Type];

        $list = DB::connection('read')->table('QPRecordDB.dbo.RecordUserAgent as ua')
            ->join('QPAccountsDB.dbo.accountsInfo as ai', 'ua.UserID', '=', 'ai.UserID')
            ->leftJoin('QPAccountsDB.dbo.accountsInfo as ain', 'ain.UserID', '=', 'ai.SpreaderID')
            ->select('ua.Id', 'ai.GameID', 'ain.GameID as SpreaderID', 'DirectRebate', 'SendTime', 'SourceUserID', 'OrderSn', 'ua.UserID', 'ua.Level', 'ua.Type', 'ua.orderIds')
            ->where($where)
            ->orderBy('SendTime', 'desc')
            ->paginate(10);

        // 自动审核开关
        $AutoVerify = DB::connection('write')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'AutoVerify')
            ->value('StatusValue');

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

            $SourceGameID = 1;
            switch ($value->Type) {
                case 1:     // 注册
                    $SourceGameID = DB::table(TableName::QPAccountsDB() . 'accountsInfo')
                        ->where('UserID', $value->SourceUserID)
                        ->value('GameID');
                    break;
            }


            $value->SourceGameID = $SourceGameID;
        }
        unset($value);

        $start_time = Helper::timeChange($start_time);
        $end_time = Helper::timeChange($end_time);
        return compact('list', 'UserID', 'SpreaderID', 'start_time', 'end_time', 'Type', 'AutoVerify');

    }

    public function winloser($GameID, $UniqueCode, $room, $start_time, $end_time, $excel, $id_list, $ChangeScoreMin, $ChangeScoreMax, $ChangeScoreSort, $afterScoreSort, $searchGame)
    {
        $where = [];
        $UserID = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
                ->where('GameID', $GameID)
                ->select('UserID')
                ->first()->UserID ?? '';

        if (!empty($id_list) && empty($start_time)) {
            $withDrawFinal = DB::connection('read')->table('QPAccountsDB.dbo.OrderWithDraw as ow')
                    ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ow.UserID', 'ai.UserID')
                    ->where('ow.State', 2)
                    ->where('ai.GameID', $GameID)
                    ->where('finishDate', '>=', date('Y-m-d 00:00:00'))
                    ->select('finishDate')
                    ->orderByDesc('finishDate')
                    ->first()->finishDate ?? '';
            if (!empty($withDrawFinal)) {
                $start_time = date('Y-m-d H:i:s', strtotime($withDrawFinal));
                $end_time = date('Y-m-d 23:59:59', strtotime($start_time));
            }
        }

        $start_time = $start_time ?: date('Y-m-d 00:00:00');
        $end_time = $end_time ?: date('Y-m-d 23:59:59');


        ////////////////////////
        // 查询范围需要在同一个月份，暂时不支持跨月份查询
        $from_mm = intval(date('Ym', strtotime($start_time)));
        $to_mm = intval(date('Ym', strtotime($end_time)));
        if ($from_mm != $to_mm) {
            throw new \Exception('暂不支持跨月查询，开始时间和结束时间请选择相同月份');
        }

        //确定有效表范围，从202104月开始到当前月
        if ($from_mm < 202204 || $from_mm > intval(date('Ym'))) {
            throw new \Exception('超出时间范围，开始时间应大于等于2022年4月，小于等于当前月');
        }

        $table = TableName::QPTreasureDB() . 'YN_RecordScoreInfo_' . $from_mm;
        //////////////////////


        $where[] = ['ri.UpdateTime', '>=', date('Y-m-d H:i:s', strtotime($start_time))];
        $where[] = ['ri.UpdateTime', '<=', date('Y-m-d H:i:s', strtotime($end_time))];

        !empty($GameID) && $where[] = ['ri.UserID', $UserID];

        !empty($UniqueCode) && $where[] = ['UniqueCode', 'like', $UniqueCode . '%'];
        !empty($room) && $where[] = ['ri.ServerID', '=', $room];

        if (!empty($searchGame)) {
            $roomIds = DB::table(TableName::QPPlatformDB() . 'GameRoomInfo')
                ->where('GameID', $searchGame)
                ->select('ServerID')
                ->pluck('ServerID')->toArray();

            $where[] = [function ($query) use ($roomIds) {
                $query->whereIn('ServerID', $roomIds);
            }];

        }

        !empty($ChangeScoreMax) && $ChangeScoreMax = (int)$ChangeScoreMax * NumConfig::NUM_VALUE;
        !empty($ChangeScoreMin) && $ChangeScoreMin = (int)$ChangeScoreMin * NumConfig::NUM_VALUE;


        if ((!empty($ChangeScoreMax) || $ChangeScoreMax === '0') && (!empty($ChangeScoreMin) || $ChangeScoreMin === '0')) {
            $where[] = [function ($obj) use ($ChangeScoreMin, $ChangeScoreMax) {
                $obj->whereBetween('ChangeScore', [$ChangeScoreMin, $ChangeScoreMax]);
            }];
        } elseif (!empty($ChangeScoreMin) || $ChangeScoreMin === '0') {
            $where[] = ['ChangeScore', '>=', $ChangeScoreMin];
        } elseif (!empty($ChangeScoreMax) || $ChangeScoreMax === '0') {
            $where[] = ['ChangeScore', '<=', $ChangeScoreMax];
        }


        !empty($ChangeScoreSort) && $order = "ChangeScore $ChangeScoreSort";

        !empty($afterScoreSort) && $order = "afterScore $afterScoreSort";

        !isset($order) && $order = "ri.UpdateTime desc";


        // 房间列表
        $rooms = DB::connection('read')->table(TableName::QPPlatformDB() . 'GameRoomInfo')
//            ->whereIn('GameID', config('games.openKGame'))
            ->selectRaw('ServerID,ServerName')
            ->orderByDesc('ServerID')
            ->pluck('ServerName', 'ServerID')
            ->toArray();

        $build_sql = DB::connection('game')->table($table . ' as ri')
            ->where($where);

        // 游戏列表
        $games = DB::connection('read')->table(TableName::QPPlatformDB() . 'GameKindItem')
//            ->whereIn('GameID', config('games.openKGame'))
            ->select('KindName', 'GameID')
            ->pluck('KindName', 'GameID')
            ->toArray();

        // 输赢报表
        if (!empty($excel)) {
            $list = $build_sql
                //->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gi', 'ri.ServerID', '=', 'gi.ServerID')
                ->select('UniqueCode', 'UserID', 'ri.UpdateTime', 'BeforeScore', 'ChangeScore', 'afterScore', 'Revenue', 'ServerID')
                ->selectRaw('null ServerName')
                ->orderBy('ri.UpdateTime', 'desc')->limit(10000)->get();


            // 获取用户游戏ID
            $getGameIDs = $this->getGameIDs($list);

            foreach ($list as &$value) {
                if (!empty($GameID)) {
                    $value->UserID = $GameID;
                }else{
                    $value->UserID = $getGameIDs[$value->UserID] ?? '';
                }

                $value->ServerName = $rooms[$value->ServerID] ?? '';
                $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);
                $value->Revenue = number_float($value->Revenue / NumConfig::NUM_VALUE);
                $value->afterScore = number_float($value->afterScore / NumConfig::NUM_VALUE);
                $value->BeforeScore = number_float($value->BeforeScore / NumConfig::NUM_VALUE);
                $value->UniqueCode = (string)($value->UniqueCode) . ' ';
                unset($value->ServerID);
            }
            unset($value);

            $title = ['对局编码', '会员ID', '游戏时间', '变化前金豆', '税后输赢金豆', '变化后输赢金豆', '累计税收', '房间名称'];
            $list = json_decode(json_encode($list), true);

            downloadExcel($list, $title, '输赢报表列表' . date('YmdHis'));
        } else {
            $list = $build_sql
                ->select('ri.UpdateTime', 'ri.ServerID', 'afterScore', 'UserID', 'UniqueCode', 'ChangeScore', 'Revenue', 'BeforeScore')
                ->OrderByRaw($order)
                ->paginate(50);
        }

        // 获取用户游戏ID
        $getGameIDs = $this->getGameIDs($list);

        // +----------------房间总输赢------------------+
        $total = DB::connection('game')->table($table . ' as ri')
            ->where($where)
            ->selectRaw('IsNull(sum(abs(ChangeScore)),0) as water,count(distinct(UserID)) as UserID,sum(ChangeScore) as ChangeScore,sum(Revenue) as Revenue')
            ->first();

        // 税收
        $data['Revenue'] = number_float($total->Revenue / NumConfig::NUM_VALUE);
        // 总输赢
        $data['ChangeScore'] = number_float(((-$total->ChangeScore / NumConfig::NUM_VALUE) - 0));
        // 总人数
        $data['UserID'] = $total->UserID;
        // 流水
        $data['liushui'] = number_float(($total->water) / NumConfig::NUM_VALUE);

        $query=GameCard::query()->select(['brand','title','gid'])->where('state','>',0)->get()->toArray();
        $thirdnames=[];
        foreach ($query as $k=>$v){
            $thirdnames[$v['gid']]=$v['brand'].'_'.$v['title'];
        }
        foreach ($list as &$value) {
            $value->GameID = $getGameIDs[$value->UserID] ?? '';
            $value->ServerName = $rooms[$value->ServerID] ?? '';
            $value->ChangeScore = number_float($value->ChangeScore / NumConfig::NUM_VALUE);
            $value->Revenue = number_float($value->Revenue / NumConfig::NUM_VALUE);
            $value->afterScore = number_float($value->afterScore / NumConfig::NUM_VALUE);
            $value->BeforeScore = number_float($value->BeforeScore / NumConfig::NUM_VALUE);
            if(strstr($value->UniqueCode,'|')){
                $values=explode('|',$value->UniqueCode);
//                $value->ServerID=$values[1];
                $value->thirdRoom=$thirdnames[$values[1]]??'';
//                dd($values,$thirdnames);
                $value->UniqueCode=null;
            }
        }


        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        $start_time = date('Y-m-d', $start_time) . 'T' . date('H:i:s', $start_time);
        $end_time = date('Y-m-d', $end_time) . 'T' . date('H:i:s', $end_time);
        !empty($ChangeScoreMax) && $ChangeScoreMax /= NumConfig::NUM_VALUE;
        !empty($ChangeScoreMin) && $ChangeScoreMin /= NumConfig::NUM_VALUE;


        return compact('list', 'rooms', 'games', 'searchGame', 'GameID', 'data', 'UserID', 'UniqueCode', 'room', 'start_time', 'end_time', 'id_list', 'ChangeScoreMin', 'ChangeScoreMax', 'ChangeScoreSort', 'afterScoreSort');

    }

    public function game_record($UniqueCode, $gameId, $month)
    {
        $where[] = ['UniqueCode', '=', $UniqueCode];
        $list = DB::connection('game')->table(TableName::QPTreasureDB() . 'YN_RecordScoreInfo_' . $month . ' as ri')
            ->where($where)
            ->whereNotNull('UniqueCode')
            ->orderBy('UpdateTime', 'desc')
            ->select('ri.UserID', 'ri.UpdateTime', 'UniqueCode', 'Revenue', 'AfterScore', 'ChangeScore', 'ServerID')
            ->paginate(50);


        // 获取用户游戏ID
        $getGameIDs = $this->getGameIDs($list);

        foreach ($list as &$value) {

            $value->GameID = $getGameIDs[$value->UserID] ?? '';
            $value->ServerName = $rooms[$value->ServerID] ?? '';
            $value->ChangeScore = number_float(($value->ChangeScore + $value->Revenue) / 100);
            $value->Revenue = number_float($value->Revenue / 100);
            $value->AfterScore = number_float($value->AfterScore / 100);

        }
        return compact('list', 'gameId', 'UniqueCode');
    }

    // 获取用户游戏ID
    protected function getGameIDs($list)
    {
        // 取出用户ID
        $UserIDs = [];
        foreach ($list as $value) {
            $UserIDs[] = $value->UserID;
        }
        $getGameIDs = DB::table(TableName::QPAccountsDB() . 'AccountsInfo')
            ->whereIn('UserID', $UserIDs)
            ->select('GameID', 'UserID')
            ->pluck('GameID', 'UserID')->toArray();

        return $getGameIDs;
    }

}