admin_api/app/Services/GlobalUser.php

<?php


namespace App\Services;


use App\Facade\Redis;
use App\Facade\TableName;
use App\Http\helper\HttpCurl;
use App\Http\helper\NumConfig;
use App\Http\logic\admin\GlobalLogicController;
use App\Http\logic\api\BaseApiLogic;
use App\Models\AccountsInfo;
use App\Models\Control;
use App\Models\Order;
use App\Models\RecordPlatformData;
use App\Models\Withdrawal;
use App\Util;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;

class GlobalUser extends BaseApiLogic
{
    public function onLineUser($index, $count = 10)
    {
        $admin = session('admin');
        $account = $admin->account ?: '';
        $password = $admin->password ?: '';

        $url = config('transfer.stock')['url'] . 'queryOnlineByPage';

        $index = $index > 0 ? ($index - 1) * $count : 0;

        $data = [
            'accounts' => $account,
            'passworld' => $password,
            'pos' => $index,
            'count' => $count
        ];

        $build_query = $url . '?' . http_build_query($data);

        $json_decode = (new HttpCurl())->curl_get($build_query);

        if (empty($json_decode)) {
            $output = [];
        } else {
            $output = \GuzzleHttp\json_decode($json_decode, true);
        }

        $data = [];
        if (is_array($output)) {
            $toArr = explode(',', $output['data'] ?? '');

            $data['UserIDs'] = $toArr;
            $data['total'] = $output['total'];
        }
        return $data;
    }

    public function userlist($gameID, $phoneNum, $spreaderID, $nickName, $mobileModel, $startTime, $endTime, $obj, $vip, $excel, $accountsType, $channel, $user_tab, $packgeName, $quickSearch, $score,$MachineID,$startLoginTime='',$endLoginTime='')
    {
        $quickSearch = explode(',', trim($quickSearch, ','));

        $AccountsInfoM = new AccountsInfo();
        $StatusValue = DB::connection('read')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'WithDrawPoint')
            ->select('StatusValue')
            ->first()->StatusValue ?? 0;
        $adminChannels=session('admin_channels');
        $field = ['ai.GameID', 'ai.UserID', 'ai.SpreaderID', 'ai.NickName', 'ai.MemberOrder', 'ai.Nullity', 'ai.WebLogonTimes', 'ai.LastLogonIP', 'ai.LastLogonDate', 'ai.LastLogonMobile', 'ai.RegisterIP', 'ai.RegisterDate', 'ai.Channel', 'gi.Score', 'gi.InsureScore', 'gi.MaxScore', 'gi.MaxWinScore'];
        $Sql = DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->leftJoin(TableName::QPTreasureDB() . 'GameScoreInfo as gi', 'ai.UserID', 'gi.UserID')
            ->where('ai.IsAndroid', 0)
            ->select($field);
        if(count($adminChannels)<5)$Sql=$Sql->whereIn('ai.Channel', $adminChannels);

        $where = [];
        if (!empty($gameID))
            $where[] = ['ai.GameID', $gameID];

        if (!empty($MachineID))
            $where[] = ['ai.LastLogonMachine', $MachineID];

        if (!empty($nickName))
            $where[] = ['ai.NickName', $nickName];
        if (!empty($mobileModel))
            $where[] = ['ai.LastLogonMobile', '=', $mobileModel];
        if (!empty($startTime))
            $where[] = ['ai.RegisterDate', '>=', $startTime];
        if (!empty($endTime))
            $where[] = ['ai.RegisterDate', '<=', $endTime];
        if (!empty($startLoginTime))
            $where[] = ['ai.LastLogonDate', '>=', $startLoginTime];
        if (!empty($endLoginTime))
            $where[] = ['ai.LastLogonDate', '<=', $endLoginTime];
        if (!empty($vip))
            $where[] = ['ai.MemberOrder', $vip];
        if (!empty($channel))
            $where[] = ['ai.Channel', $channel];

        if (!empty($packgeName)) {
            $Sql = $Sql->join(TableName::QPRecordDB() . 'RecordPackageName as rn', 'ai.UserID')->where('PackgeName', $packgeName);
        }

        if (!empty($phoneNum)) {
            $Sql = $Sql->join(TableName::QPAccountsDB() . 'AccountPhone as ap', 'ai.UserID', 'ap.UserID')
                ->where('ap.PhoneNum', $phoneNum);
        }

        if (!empty($spreaderID)) {
            $Sql = $Sql->join(TableName::QPAccountsDB() . 'AccountsInfo as ai1', 'ai1.UserID', 'ai.SpreaderID')
                ->where('ai1.GameID', $spreaderID);
        }

        if (!empty($accountsType)) {
            if ($accountsType < 4 && empty($phoneNum)) {
                $Sql = $Sql->join(TableName::QPAccountsDB() . 'AccountPhone as ap', 'ai.UserID', 'ap.UserID');
            }
            switch ($accountsType) {
                case 1:

                    $Sql = $Sql->where('ai.WebLogonTimes', 0)->whereNull('PhoneNum');
                    break;
                case 2:
                    $Sql = $Sql->where('ai.WebLogonTimes', 1)->whereNull('PhoneNum');
                    break;
                case 3:
                    $Sql = $Sql->WhereNotNull('PhoneNum');
                    break;
                case 4:
                    $Sql = $Sql->where('ai.Channel', 100);
                    break;
            }
        }


        // 快速搜索
        if (!empty($quickSearch)) {

            // 游戏中
            if (in_array(1, $quickSearch)) {
                $Sql = $Sql->join(TableName::QPTreasureDB() . 'GameScoreLocker as gsl', 'gsl.UserID', 'ai.UserID')
                    ->whereRaw('datediff(hh,CollectDate,getdate())<=5');
            }
            // 未实名
            if (in_array(2, $quickSearch)) {
                $Sql = $Sql->where('ai.PassPortID', '');
            }

            // 无手机号
            if (in_array(3, $quickSearch)) {
                $Sql = $Sql->whereNotExists(function ($query) {
                    $query->from(TableName::QPAccountsDB() . 'AccountPhone as ap')
                        ->whereRaw('ap.UserID=ai.UserID and ap.PhoneNum is not null');
                });
            }
            // 同IP
            if (in_array(4, $quickSearch)) {
                // TODO
            }
            // 无渠道
            if (in_array(5, $quickSearch)) {
                $Sql = $Sql->where('ai.Channel', 0);
            }
            // 未充值
            if (in_array(6, $quickSearch)) {
                $Sql = $Sql->whereNotExists(function ($query) {
                    $query->from(TableName::QPRecordDB() . 'RecordUserTotalStatistics AS rts')
                        ->where('Recharge', '>', 0)
                        ->whereRaw('ai.UserID=rts.UserID');
                });
            }
            // 无上级
            if (in_array(7, $quickSearch)) {
                $Sql = $Sql->whereExists(function ($query) {
                    $query->from(TableName::QPAccountsDB() . 'AccountsInfo AS ai1')
                        ->where('SpreaderID', 0)
                        ->whereRaw('ai.UserID=ai1.UserID');
                });
            }
            // 封号中
            if (in_array(8, $quickSearch)) {
                $Sql = $Sql->where('Nullity', 1);
            }
            // 官方账号
            if (in_array(9, $quickSearch)) {
                $Sql = $Sql->whereNotExists(function ($query) {
                    $query->from(TableName::QPAccountsDB() . 'IDWhiteUser AS iu')
                        ->whereRaw('ai.UserID=iu.UserID');
                });
            }

        }

        if (!empty($score)) {
            $orderBy = "Score $score";
        } else {
            $orderBy = 'ai.RegisterDate desc';
        }

        $list = $Sql->where($where)->orderByRaw($orderBy)->paginate(10);

        // 取出用户ID
        $UserIDs = [];
        foreach ($list as $value) {
            $UserIDs[] = $value->UserID;
        }

        // 用户上级ID
        $spreaderIDs = $AccountsInfoM->accountSpreaderID($UserIDs);

        // 输赢
        $winLost = $AccountsInfoM->accountWinLost($UserIDs);


        // 今日充值-- 今日提现-- 今日彩金 -- 总输赢
        $today = $AccountsInfoM->accountTodayStatistics($UserIDs);


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

        // 游戏是否在线
        $userOnLine = DB::connection('read')->table(TableName::QPTreasureDB() . 'GameScoreLocker as gsl')
            ->whereIn('UserID', $UserIDs)
            ->join(TableName::QPPlatformDB() . 'GameRoomInfo as gri', 'gsl.ServerID', 'gri.ServerID')
            ->whereRaw('datediff(hh,CollectDate,getdate())<=5')
            ->select('ServerName', 'UserID')
            ->pluck('ServerName', 'UserID')->toArray();


        // 用户手机号
        $PhoneNums = $AccountsInfoM->accountPhone($UserIDs);

        foreach ($list as &$value) {
            $value->spreaderID = isset($spreaderIDs[$value->UserID]) ? $spreaderIDs[$value->UserID] : '';
            $value->winLost = isset($winLost[$value->UserID]) ? $winLost[$value->UserID] : '';
            $value->ServerName = isset($userOnLine[$value->UserID]) ? $userOnLine[$value->UserID] : '';
            $value->PhoneNum = isset($PhoneNums[$value->UserID]) ? $PhoneNums[$value->UserID] : '';
            $value->Nullity = $value->Nullity == 0 ? '正常' : '封禁';
            $value->Score = $value->Score / NumConfig::NUM_VALUE;
            $value->InsureScore = $value->InsureScore / NumConfig::NUM_VALUE;
            $value->MaxScore = $value->MaxScore / NumConfig::NUM_VALUE;
            $value->MaxWinScore = $value->MaxWinScore / NumConfig::NUM_VALUE;
            // 可提现额度
            $value->CashAble = $AccountsInfoM->CashAble($value->UserID, $value->Score, $StatusValue);
            foreach ($today as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->todayWithdraw = $val->Withdraw / NumConfig::NUM_VALUE;
                    $value->todayScore = $val->Score / NumConfig::NUM_VALUE;
                    $value->todayRecharge = $val->Recharge;
                    $value->todayHandsel = $val->Handsel / NumConfig::NUM_VALUE;
                }
            }
            foreach ($total as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->totalWithdraw = $val->Withdraw / NumConfig::NUM_VALUE;
                    $value->totalScore = $val->Score / NumConfig::NUM_VALUE;
                    $value->totalRecharge = $val->Recharge;
                    $value->totalHandsel = $val->Handsel / NumConfig::NUM_VALUE;
                }
            }
        }
        return $list;
    }




    public function userNewList($gameID, $startTime, $endTime, $lstartTime, $lendTime, $channel, $minRecharge, $maxRecharge,$excel)
    {
        $field = ['ai.GameID','ai.UserID', 'ai.NickName','gi.Score', 'ai.Nullity', 'ai.LastLogonDate', 'ai.RegisterDate', 'ai.Channel', 'ap.PhoneNum','Recharge','Withdraw','LostScore','WinScore'];
        $Sql = DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->leftJoin(TableName::QPTreasureDB() . 'GameScoreInfo as gi', 'ai.UserID', 'gi.UserID')
            ->leftJoin(TableName::QPAccountsDB() . 'AccountPhone as ap', 'ai.UserID', 'ap.UserID')
            ->leftJoin(TableName::QPRecordDB().'RecordUserTotalStatistics as rut','ai.UserID', 'rut.UserID')
            ->where('ai.IsAndroid', 0)
            ->select($field);


//        return DB::connection('read')->table('QPRecordDB.dbo.RecordUserTotalStatistics')
//            ->selectRaw('Recharge,Withdraw,Handsel,(WinScore + LostScore) Score,ServiceFee,UserID,Revenue')
//            ->whereIn('UserID', $UserIDs)
//            ->get();


        $where = [];
        if (!empty($gameID))
            $where[] = ['ai.GameID', $gameID];
        if (!empty($startTime))
            $where[] = ['ai.RegisterDate', '>=', $startTime];
        if (!empty($endTime))
            $where[] = ['ai.RegisterDate', '<=', $endTime];

        if (!empty($lstartTime))
            $where[] = ['ai.LastLogonDate', '>=', $lstartTime];
        if (!empty($lendTime))
            $where[] = ['ai.LastLogonDate', '<=', $lendTime];


        if (!empty($minRecharge))
            $where[] = ['rut.Recharge', '>=', $minRecharge];
        if (!empty($maxRecharge))
            $where[] = ['rut.Recharge', '<=', $maxRecharge];



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


        $orderBy = 'ai.UserID desc';

        if($excel){
            $list = $Sql->where($where)->orderByRaw($orderBy)->limit(1000)->get();
            $list = json_decode(json_encode($list),true);
            //var_dump($list);
            //exit();
            foreach ($list as &$value) {
                $value['Nullity'] = $value['Nullity'] == 0 ? '正常' : '封禁';
                $value['Score'] = (string)round($value['Score'] / NumConfig::NUM_VALUE, 2);
                $value['Withdraw'] = (string)round(($value['Withdraw'] ?? 0) / NumConfig::NUM_VALUE, 2);
                $value['Recharge'] = (string)($value['Recharge'] ?? 0);
                $value['totalScore'] = (string)round(intval(($value['WinScore'] ?? 0) + ($value['LostScore'] ?? 0)) / NumConfig::NUM_VALUE, 2);
                unset($value['WinScore']);
                unset($value['LostScore']);
            }
            //dd($list);
            $title = ['UserID', '玩家ID', '昵称','余额', '状态', '离线时间', '注册时间', '玩家渠道', '手机号', '充值', '提现', '输赢'];
//            $cellData = json_decode(json_encode($list), true);
            downloadExcel($list, $title, '用户列表');
            return;
        }

        $list = $Sql->where($where)->orderByRaw($orderBy)->paginate(10);

        foreach ($list as &$value) {
            $value->Nullity = $value->Nullity == 0 ? '正常' : '封禁';
            $value->Score = $value->Score / NumConfig::NUM_VALUE;
            $value->totalScore = ($value->WinScore+$value->LostScore)/NumConfig::NUM_VALUE;
            $value->Withdraw  = $value->Withdraw / NumConfig::NUM_VALUE;
        }
        return $list;
    }



    public function dk_userlist($GameID, $SpreaderID, $NickName, $start_time, $end_time, $user_label, $is_line, $game_room, $UserScoreControl, $ScoreSort, $amountSort, $sort_tx, $sort_date, $ChangeScore_sort, $Channel, $label_sort, $ServerNameSort)
    {
        $AccountsInfoM = new AccountsInfo();
        $StatusValue = DB::connection('read')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'WithDrawPoint')
            ->select('StatusValue')
            ->first()->StatusValue ?? 0;
        $where = [];
        $field = ['ai.GameID', 'ai.UserID', 'ai.SpreaderID', 'ai.NickName', 'ai.MemberOrder', 'ai.Nullity', 'ai.WebLogonTimes', 'ai.LastLogonIP', 'ai.LastLogonDate', 'ai.RegisterIP', 'ai.RegisterDate', 'ai.Channel', 'gi.Score'];
        $Sql = DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->where('ai.IsAndroid', 0)
            ->leftJoin(TableName::QPTreasureDB() . 'GameScoreInfo as gi', 'ai.UserID', 'gi.UserID')
            ->select($field);

        if (!empty($GameID))
            $where[] = ['ai.GameID', $GameID];

        if (!empty($NickName))
            $where[] = ['ai.NickName', $NickName];

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

        if (!empty($start_time))
            $where[] = ['ai.RegisterDate', '>=', $start_time];
        if (!empty($end_time))
            $where[] = ['ai.RegisterDate', '<=', $end_time];

        if (!empty($channel))
            $where[] = ['ai.Channel', $channel];
        if (!empty($user_label))
            $Sql = $Sql->join(TableName::QPAccountsDB() . 'AccountLabelDanControlLabel as al', 'ai.UserID', 'al.UserID')
                ->where('type', $user_label);

        // 充值
        if (!empty($amountSort)) {
            $Sql = $Sql->join(TableName::QPAccountsDB() . 'YN_VIPAccount as vip', 'ai.UserID', 'vip.UserID')->orderByRaw("vip.Recharge $amountSort");
        }
        // 提现
        if (!empty($sort_tx)) {
            $Sql = $Sql->leftJoin(TableName::QPAccountsDB() . 'UserTabData as ud', 'ai.UserID', 'ud.UserID')->orderByRaw("TakeMoney $sort_tx");
        }

        if (!empty($ChangeScore_sort)) {
            $Sql = $Sql->leftJoin(TableName::QPRecordDB() . 'RecordUserTotalStatistics as rds', 'rds.UserID', 'ai.UserID')->orderByRaw("(WinScore + LostScore) $ChangeScore_sort");
        }


        // 受控制用户
        switch ($UserScoreControl) {
            case 1:  // 控制
                $Sql = $Sql->whereExists(function ($query) {
                    $query->from(TableName::QPTreasureDB() . 'UserScoreControl as usc')
                        ->whereRaw('usc.UserID=ai.UserID');
                });
                break;
            case 2:  // 不受控制
                $Sql = $Sql->whereNotExists(function ($query) {
                    $query->from(TableName::QPTreasureDB() . 'UserScoreControl as usc')
                        ->whereRaw('usc.UserID=ai.UserID');
                });
                break;
            case 3: // 自动控制 + 控制中
                $Sql = $Sql->whereExists(function ($query) {
                    $query->from(TableName::QPTreasureDB() . 'UserScoreControl as usc')
                        ->whereRaw('usc.UserID=ai.UserID');
                })->whereExists(function ($query) {
                    $query->from(TableName::QPRecordDB() . 'RecordRechargeControl as rc')
                        ->whereRaw('ai.UserID=rc.UserID and rc.State>0');
                });
                break;
            case 4:
                $Sql = $Sql->leftJoin('QPRecordDB.dbo.RecordRechargeControl as rc', 'ai.UserID', 'rc.UserID')
                    ->Where('rc.State', '>', 0)->whereNull('usc.UserID');
                break;
        }


        if ($is_line == 1 || !empty($game_room)) {     // 在线

            if (empty($game_room)) {
                $Sql = $Sql->whereExists(function ($query) {
                    $query->from(TableName::QPTreasureDB() . 'GameScoreLocker as gsl')
                        ->whereRaw('gsl.UserID=ai.UserID and datediff(hh,gsl.CollectDate,getdate())<=5');
                });
            } else {

                $Sql = $Sql->whereExists(function ($query) use ($game_room) {
                    $query->from(TableName::QPTreasureDB() . 'GameScoreLocker as gsl')
                        ->whereRaw("gsl.UserID=ai.UserID and datediff(hh,gsl.CollectDate,getdate())<=5 and ServerID=$game_room");
                });
            }

        } elseif ($is_line == 2) {    // 离线
            $Sql = $Sql->whereNotExists(function ($query) {
                $query->from(TableName::QPTreasureDB() . 'GameScoreLocker as gsl')
                    ->whereRaw('gsl.UserID=ai.UserID');
            });
        }

        if (!empty($spreaderID)) {
            $Sql = $Sql->join(TableName::QPAccountsDB() . 'AccountsInfo as ai1', 'ai1.UserID', 'ai.SpreaderID')
                ->where('ai1.GameID', $spreaderID);
        }


        // 金币排序
        if (!empty($ScoreSort))
            $Sql = $Sql->orderBy('Score', $ScoreSort);
        // 日期
        if (!empty($sort_date)) {
            $Sql = $Sql->orderBy('ai.RegisterDate', $sort_date);
        } else {
            $Sql = $Sql->orderBy('ai.RegisterDate', 'desc');
        }
        if (!empty($label_sort)) {
            $Sql = $Sql->orderBy('IDColor', $label_sort);
        }
        $list = $Sql->where($where)->paginate(10);

        // 取出用户ID
        $UserIDs = [];
        foreach ($list as $value) {
            $UserIDs[] = $value->UserID;
        }

        // 用户上级ID
        $spreaderIDs = $AccountsInfoM->accountSpreaderID($UserIDs);
        // 输赢
        $winLost = $AccountsInfoM->accountWinLost($UserIDs);
        // 今日充值-- 今日提现-- 今日彩金 -- 总输赢
        $today = $AccountsInfoM->accountTodayStatistics($UserIDs);
        // 全部充值 -- 提现 -- 彩金 -- 总输赢 -- 总得提现手续费
        $total = $AccountsInfoM->accountTotalStatistics($UserIDs);
        // 游戏是否在线
        $userOnLine = $AccountsInfoM->accountOnLine($UserIDs);
        // 用户手机号
        $PhoneNums = $AccountsInfoM->accountPhone($UserIDs);
        // 用户标签
        $AccountLabel = $AccountsInfoM->accountLabel($UserIDs);
        // 登录IP
        $LoginIP = $AccountsInfoM->LoginIP($UserIDs);
        // 备注
        $Remarks = $AccountsInfoM->remarks($UserIDs);
        // 未领邮件
        $mailData = $AccountsInfoM->mail($UserIDs);
        // 受控标签
        $controlLabel = $AccountsInfoM->controlLabel($UserIDs);
        // 单控
        $UserScoreControls = DB::table(TableName::QPTreasureDB() . 'UserScoreControl')
            ->whereIn('UserID', $UserIDs)
            ->select(['ControlRadian', 'ControlScore', 'EffectiveScore', 'UserID'])
            ->get();

        $redis = \Illuminate\Support\Facades\Redis::connection();
        $controlModel = new Control();
        $onLinUser = [];
        foreach ($userOnLine as $val) {
            $onLinUser[$val->UserID] = $val->KindID;
        }

        foreach ($list as &$value) {
            $value->spreaderID = isset($spreaderIDs[$value->UserID]) ? $spreaderIDs[$value->UserID] : '';
            $value->winLost = isset($winLost[$value->UserID]) ? $winLost[$value->UserID] : '';
            $value->PhoneNum = isset($PhoneNums[$value->UserID]) ? $PhoneNums[$value->UserID] : '';
            $value->Nullity = $value->Nullity == 0 ? '正常' : '封禁';
            $value->Score = $value->Score / NumConfig::NUM_VALUE;
            $value->notReceived = isset($mailData[$value->UserID]) ? number_float($mailData[$value->UserID] / NumConfig::NUM_VALUE) : 0;
            $value->RecordRechargeControl = isset($controlLabel[$value->UserID]) ? 1 : 0;
            $value->LoginIpCount = isset($LoginIP[$value->UserID]) ? $LoginIP[$value->UserID] : 0;
            $value->Remarks = isset($Remarks[$value->UserID]) ? $Remarks[$value->UserID] : '';
            // 可提现额度
            $value->CashAble = $AccountsInfoM->CashAble($value->UserID, $value->Score, $StatusValue);
            $value->control_record = $controlModel->getControlState($value->UserID, $onLinUser);

            foreach ($UserScoreControls as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->EffectiveScore = number_float($val->EffectiveScore / NumConfig::NUM_VALUE);
                    $value->ControlScore = number_float($val->ControlScore / NumConfig::NUM_VALUE);
                    $value->ControlRadian = $val->ControlRadian ?: number_float($val->ControlRadian);
                }
            }
            foreach ($userOnLine as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->ServerName = $val->ServerName;
                    // 捕鱼控制
                    if ($val->KindID == 2003) {
                        $info = $redis->hmGet("userControl_{$value->UserID}", ['controlLevel', 'controlTarget', 'curControlValue']);
                        $info['controlTarget'] /= NumConfig::NUM_VALUE;
                        $info['curControlValue'] /= NumConfig::NUM_VALUE;
                        // 控制分数
                        if (!empty($info['controlLevel'])) {
                            $value->fishControl = '捕鱼：控制等级：' . $info['controlLevel'] . '；控制分数：' . $info['controlTarget'] . '；生效分数：' . $info['curControlValue'];
                        }
                    }
                }
            }

            foreach ($today as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->todayWithdraw = $val->Withdraw / NumConfig::NUM_VALUE;
                    $value->todayScore = $val->Score / NumConfig::NUM_VALUE;
                    $value->todayRecharge = $val->Recharge;
                    $value->todayHandsel = $val->Handsel / NumConfig::NUM_VALUE;
                }
            }

            foreach ($total as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->totalWithdraw = $val->Withdraw / NumConfig::NUM_VALUE;
                    $value->totalScore = $val->Score / NumConfig::NUM_VALUE;
                    $value->totalRecharge = $val->Recharge;
                    $value->totalHandsel = $val->Handsel / NumConfig::NUM_VALUE;
                }
            }

            foreach ($AccountLabel as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->IDColor = $val->IDColor;
                    $value->user_label = $AccountsInfoM->labelType($val->type);
                }
            }
        }
        return $list;
    }

    /**
     * 充值排行榜：总充值>100 且 提现/充值<35%
     */
    public function rechargeRankList($channel)
    {
        $AccountsInfoM = new AccountsInfo();
        $minRecharge = 100;

        $field = [
            'ai.GameID', 'ai.UserID', 'ai.NickName', 'ai.Channel',
            'ai.RegisterDate', 'ai.LastLogonDate',
            'gi.Score',
            'rut.Recharge', 'rut.Withdraw',
        ];
        $Sql = DB::connection('read')->table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
            ->join(TableName::QPRecordDB() . 'RecordUserTotalStatistics as rut', 'ai.UserID', 'rut.UserID')
            ->leftJoin(TableName::QPTreasureDB() . 'GameScoreInfo as gi', 'ai.UserID', 'gi.UserID')
            ->where('ai.IsAndroid', 0)
            ->where('rut.Recharge', '>', $minRecharge)
            ->whereRaw('rut.Withdraw * 1.0 / (rut.Recharge * ' . NumConfig::NUM_VALUE . ') < 0.35')
            ->select($field);

        if (!empty($channel) || $channel === '0') {
            $Sql->where('ai.Channel', $channel);
        }

        $adminChannels = session('admin_channels');
        if (count($adminChannels) < 5) {
            $Sql->whereIn('ai.Channel', $adminChannels);
        }

        $list = $Sql->orderByRaw('rut.Recharge desc')->paginate(15);

        $UserIDs = [];
        foreach ($list as $value) {
            $UserIDs[] = $value->UserID;
        }

        $today = !empty($UserIDs) ? $AccountsInfoM->accountTodayStatistics($UserIDs) : [];

        $controlLabel = !empty($UserIDs) ? $AccountsInfoM->controlLabel($UserIDs) : [];

        $UserScoreControls = !empty($UserIDs)
            ? DB::table(TableName::QPTreasureDB() . 'UserScoreControl')
                ->whereIn('UserID', $UserIDs)
                ->select(['ControlRadian', 'ControlScore', 'EffectiveScore', 'UserID'])
                ->get()
            : collect();

        $controlModel = new Control();

        foreach ($list as &$value) {
            $value->Score = ($value->Score ?? 0) / NumConfig::NUM_VALUE;
            $recharge = $value->Recharge ?? 0;
            $withdraw = ($value->Withdraw ?? 0) / NumConfig::NUM_VALUE;
            $value->Recharge = $recharge;
            $value->Withdraw = $withdraw;
            $value->withdrawRate = $recharge > 0 ? round($withdraw / $recharge * 100, 2) : 0;

            $value->todayRecharge = 0;
            $value->todayWithdraw = 0;
            foreach ($today as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->todayRecharge = $val->Recharge;
                    $value->todayWithdraw = $val->Withdraw / NumConfig::NUM_VALUE;
                }
            }

            $value->RecordRechargeControl = isset($controlLabel[$value->UserID]) ? 1 : 0;
            $value->ControlScore = null;
            $value->EffectiveScore = null;
            $value->control_record = $controlModel->getControlState($value->UserID, []);
            foreach ($UserScoreControls as $val) {
                if ($val->UserID == $value->UserID) {
                    $value->EffectiveScore = number_float($val->EffectiveScore / NumConfig::NUM_VALUE);
                    $value->ControlScore = number_float($val->ControlScore / NumConfig::NUM_VALUE);
                }
            }
        }
        return $list;
    }

    // 全局报表 - 用户相关
    public function accounts()
    {
        $AccountsInfoModel = new AccountsInfo();
        $WithdrawalModel = new Withdrawal();
        $GlobalLogic = new GlobalLogicController();
        // 用户支付信息
        $accountsPayInfo = Order::pay_sum();
        $paySum = $accountsPayInfo->Recharge ?? 0;
        $payUserCount = $accountsPayInfo->payCount;
        // 用户所有提现
        $withdraw = $WithdrawalModel->totalWithdraw()->Withdraw / NumConfig::NUM_VALUE ?? 0;
        // 累计提现回收金额
        $withDrawRecovery = $WithdrawalModel->WithDrawRecovery();
        // 休眠用户
        $dormancyUser = $AccountsInfoModel->dormancy_user();
        // 流失用户
        $lsUser = $AccountsInfoModel->ls_user();
        // 总注册用户
        $register = $AccountsInfoModel->user_count();
        // 签到总奖励
        $totalSignIn = $AccountsInfoModel->accountSignIn();
        // 周卡购买报表
//        $weeklyCard = $GlobalLogic->WeeklyCard();
        return compact('paySum', 'payUserCount', 'withdraw', 'withDrawRecovery', 'dormancyUser', 'lsUser', 'register','totalSignIn');
    }

    // 全局报表 - 游戏相关
    // 全局报表 - 游戏相关
    public function games($StartDataID, $EndDataID)
    {

        $redis = \Illuminate\Support\Facades\Redis::connection();
        $gameRtp =$redis->get("SomeConfigSpecial");
        $gameRtp = json_decode($gameRtp,true);

        // 流水、输赢、彩金
        $gameInfo = DB::connection('read')->table('QPRecordDB.dbo.RecordUserTotalStatistics')
//                      ->selectRaw('sum(TotalBet) as flowing_water,Isnull(sum(Revenue),0) as Revenue,IsNull((sum(LostScore) + sum(WinScore)),0) Score,Isnull(SUM(Handsel),0) as ChangeScore')
            ->selectRaw('sum(TotalBet) as flowing_water_new,IsNull((abs(sum(LostScore)) + sum(WinScore)),0) flowing_water,Isnull(sum(Revenue),0) as Revenue,IsNull((sum(LostScore) + sum(WinScore)),0) Score,Isnull(SUM(Handsel),0) as ChangeScore')
            ->first();
        foreach ($gameInfo as &$value) {
            $value = number_float($value / NumConfig::NUM_VALUE);
        }

        unset($value);
        // 房间库存
        $roomStock = GameRoomInfo::RoomStock(config('games.openKGame'));

        // 游戏房间数据--汇总
//        $gameRoomInfo = DB::connection('read')->table(TableName::QPRecordDB() . 'RecordServerDataStatistics')
//                          ->where('DateID', '>=', $StartDataID)
//                          ->where('DateID', '<=', $EndDataID)
////                          ->selectRaw('sum(TotalBet) as flowing_water,Isnull(sum(Revenue),0) as Revenue,Isnull((sum(WinScore) + sum(LostScore)),0) as win_lose')
//                          ->selectRaw('sum(TotalBet) as flowing_water_new,Isnull((sum(WinScore) + abs(sum(LostScore))),0) as flowing_water,Isnull(sum(Revenue),0) as Revenue,Isnull((sum(WinScore) + sum(LostScore)),0) as win_lose')
//                          ->first();
        $gameRoomInfo=DB::table('QPRecordDB.dbo.RecordGameRoomDayInfo')
            ->whereBetween('DateID', [$StartDataID, $EndDataID])
            ->where('SortID','-1')
            ->selectRaw('sum(TotalBet) as flowing_water_new,sum(PayTotalBet) as pay_flowing_water_new,sum(GameTurnOver) as flowing_water,sum(PayGameTurnOver) as pay_flowing_water,sum(GameRevenue) as Revenue,sum(PayGameRevenue) as pay_Revenue')
            ->first();

        $gameRoomInfo2=DB::table('QPPlatformDB.dbo.RoomStockDay')
            ->whereBetween('DateID', [$StartDataID, $EndDataID])
            ->selectRaw('sum(Winlost) as win_lose,sum(PayWinlost) as pay_win_lose')->first();
        $gameRoomInfo->win_lose=$gameRoomInfo2->win_lose;
        $gameRoomInfo->pay_win_lose=$gameRoomInfo2->pay_win_lose;

        $gameRoomInfo->free_flowing_water = $gameRoomInfo->flowing_water_new-$gameRoomInfo->pay_flowing_water_new;

        $gameRoomInfo->free_win_lose = $gameRoomInfo->win_lose-$gameRoomInfo->pay_win_lose;



        // 游戏人数
        $totalGameCount = DB::table(TableName::QPRecordDB() . 'RecordUserGameDayCount')
            ->whereBetween('DateID', [$StartDataID, $EndDataID])
            ->distinct()
            ->count('UserID');
        // 游戏人数
        $pay_totalGameCount = DB::table(TableName::QPRecordDB() . 'RecordUserGameDayCount as ru')
            ->join('QPAccountsDB.dbo.YN_VIPAccount as v', 'ru.UserID', '=', 'v.UserID')
            ->whereBetween('DateID', [$StartDataID, $EndDataID])
            ->distinct()
            ->count('ru.UserID');
        foreach ($gameRoomInfo as &$value) {
            $value = number_float($value / NumConfig::NUM_VALUE);
        }
        unset($value);

        $gameRoomInfo->Rtp = @$gameRtp['g0'];
        if (!empty($gameRoomInfo)) {
            // 平台输赢 = 输赢 + 税收
//            $gameRoomInfo->win_lose = $gameRoomInfo->win_lose;
        }

        // 游戏房间数据 -- 列表
        //        $gameList = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo as gi')
        //            ->leftJoin('QPRecordDB.dbo.RecordServerDataStatistics as rds', 'rds.ServerID', 'gi.ServerID')
        //            ->leftJoin('QPRecordDB.dbo.RecordUserGameDayCount as rgc', function ($query) {
        //                $query->on('rgc.GameID', 'rds.ServerID')->on('rds.DateID', 'rgc.DateID');
        //            })
        //            ->whereBetween('rds.DateID', [$StartDataID, $EndDataID])
        //            ->select('gi.GameID', 'gi.ServerName', 'gi.ServerID')
        //            ->selectRaw('Isnull((sum(WinScore) + abs(sum(LostScore))),0) as flowing_water,Isnull(sum(rds.Revenue),0) as Revenue,Isnull((sum(WinScore) + sum(LostScore)),0) as win_lose')
        //            ->selectRaw('WinScore,LostScore,Revenue,WinUserCount,LostUserCount,gi.GameID,gi.ServerName,gi.ServerID')
        //            ->get();

        $gameList = DB::table(TableName::QPRecordDB() . 'RecordGameRoomDayInfo as ri')
            ->where('ri.SortID', '>', 0)
            ->whereBetween('ri.DateID', [$StartDataID, $EndDataID])
            ->leftJoin(TableName::QPPlatformDB() . 'RoomStockDay as rs', function ($query) {
                $query->on('ri.GameID', 'rs.GameID')->on('ri.SortID', 'rs.SortID')->on('ri.DateID', 'rs.DateID');
            })
            ->select('GamePlayer as Cnt', 'PayGamePlayer as pay_Cnt', 'GameRevenue as Revenue', 'PayGameRevenue as pay_Revenue', 'GameTurnOver as flowing_water', 'TotalBet as flowing_water_new','PayTotalBet as pay_flowing_water_new', 'Winlost as win_lose', 'PayWinlost as pay_win_lose', 'ri.GameID', 'ri.SortID','ri.DateID')
            ->get();
        // 游戏输赢 + 控制输赢
        $RoomControlStockDay = DB::table(TableName::QPPlatformDB().'RoomControlStockDay')
            ->whereBetween('DateID', [$StartDataID, $EndDataID])
            ->get();
        $gameServerNames = DB::table(TableName::QPPlatformDB() . 'RoomConfig')->get();
        $chu = $zhong = $gao = 0;
        $chu_n = $zhong_n = $gao_n = 0;
//        dd($gameList);
        $gamesorts=[];
        $revenueGames = config('games.revenueGames');

//        dd($gameRtp);
        foreach ($gameList as &$value) {
            $value->ServerName = '';
            $value->flowing_water = is_null($value->flowing_water) ? 0 : number_float($value->flowing_water / NumConfig::NUM_VALUE);
            $value->flowing_water_new = is_null($value->flowing_water_new) ? 0 : number_float($value->flowing_water_new / NumConfig::NUM_VALUE);

//            $value->Revenue = is_null($value->Revenue) ? 0 : number_float($value->Revenue / NumConfig::NUM_VALUE);

            $value->pay_flowing_water_new = is_null($value->pay_flowing_water_new) ? 0 : number_float($value->pay_flowing_water_new / NumConfig::NUM_VALUE);
//            $value->pay_Revenue = is_null($value->pay_Revenue) ? 0 : number_float($value->pay_Revenue / NumConfig::NUM_VALUE);

            $value->free_flowing_water = $value->flowing_water_new-$value->pay_flowing_water_new;

            //玩家不扣税的游戏不累计到统计里
//            if(!in_array($value->GameID,$revenueGames)){
//                $gameRoomInfo->win_lose = $gameRoomInfo->win_lose-$value->Revenue;
//                $gameRoomInfo->pay_win_lose = $gameRoomInfo->pay_win_lose-$value->pay_Revenue;
//                $gameRoomInfo->Revenue = $gameRoomInfo->Revenue-$value->Revenue; //真是税收收入
//                $gameRoomInfo->pay_Revenue = $gameRoomInfo->pay_Revenue-$value->pay_Revenue; //真是税收收入
//            }


            $value->win_lose = is_null($value->win_lose) ? 0 : number_float($value->win_lose / NumConfig::NUM_VALUE);
            $value->pay_win_lose = is_null($value->pay_win_lose) ? 0 : number_float($value->pay_win_lose / NumConfig::NUM_VALUE);

            $value->free_win_lose = $value->win_lose-$value->pay_win_lose;
            foreach ($gameServerNames as $serverName) {
                if ($value->GameID == $serverName->GameID && $value->SortID == $serverName->SortID) {
                    $value->ServerName = $serverName->RoomName;
                }
            }
            // 游戏输赢 = 控制输赢 + 库存输赢
            foreach ($RoomControlStockDay as $val){
                if ($value->GameID == $val->GameID && $value->SortID == $val->SortID && $value->DateID == $val->DateID) {
                    $value->win_lose = is_null($val->Winlost) ? $value->win_lose : $value->win_lose + ($val->Winlost / NumConfig::NUM_VALUE);
                }
            }

            if(is_array($gameRtp)){
                $value->Rtp = @$gameRtp['g'.$value->GameID]??'';
            }
//            if($value->SortID == 1){
//                $chu += $value->flowing_water;
//                $chu_n += $value->flowing_water_new;
//            }
//            if($value->SortID == 2){
//                $zhong += $value->flowing_water;
//                $zhong_n += $value->flowing_water_new;
//            }
//            if($value->SortID == 3){
//                $gao += $value->flowing_water;
//                $gao_n += $value->flowing_water_new;
//            }
            if(!isset($gamesorts[$value->GameID.'-'.$value->SortID])) {
                $gamesorts[$value->GameID.'-'.$value->SortID]=(object)['Cnt'=>0,'Revenue'=>0,'pay_Cnt'=>0,'pay_Revenue'=>0,'flowing_water'=>0,'free_win_lose' => 0,'free_flowing_water' => 0,'win_lose'=>0,'flowing_water_new'=>0,'pay_win_lose'=>0,'pay_flowing_water_new'=>0,'GameID'=>$value->GameID,'SortID'=>$value->SortID,'DateID'=>$value->DateID,'ServerName'=>$value->ServerName];
            }

            $oldvalue = &$gamesorts[$value->GameID.'-'.$value->SortID];

            $oldvalue->Cnt += $value->Cnt;
//            $oldvalue->Revenue += $value->Revenue;
            $oldvalue->flowing_water += $value->flowing_water;
            $oldvalue->flowing_water_new += $value->flowing_water_new;
            $oldvalue->win_lose += $value->win_lose;

            $oldvalue->pay_Cnt += $value->pay_Cnt;
            $oldvalue->pay_Revenue += $value->pay_Revenue;

            $oldvalue->pay_flowing_water_new += $value->pay_flowing_water_new;
            $oldvalue->pay_win_lose += $value->pay_win_lose;

            $oldvalue->free_win_lose += $value->free_win_lose;
            $oldvalue->free_flowing_water += $value->free_flowing_water;

            $oldvalue->Rtp = $value->Rtp;

        }
        $gameList=array_values($gamesorts);
        $gameList=Util::arraySort($gameList,'flowing_water_new',SORT_DESC);
        if(@$_REQUEST['nienie']){
            dd($gameList,$gameRoomInfo);
        }
        if($chu==0)$chu=1;
        $zhongRate = (ceil($zhong/$chu*100)/100);
        $gaoRate = (ceil($gao/$chu*100)/100);
        $total = 1+$zhongRate+$gaoRate;
        $zhongRate_n = (ceil($zhong/$chu*100)/100);
        $gaoRate_n = (ceil($gao/$chu*100)/100);
        $total_n = 1+$zhongRate+$gaoRate;

        $rateString = "1:$zhongRate:$gaoRate=$total";
        $rateString_n = "1:$zhongRate_n:$gaoRate_n=$total_n";

        unset($value);
        return compact('gameInfo', 'gameRoomInfo', 'gameList', 'roomStock', 'totalGameCount','pay_totalGameCount','rateString','rateString_n');
    }

    // 全局报表 - 彩金相关
    public function winnings($startTime, $endTime)
    {
        $startTime = date("Y-m-d 00:00:00", strtotime($startTime));
        $endTime = date("Y-m-d 23:59:59", strtotime($endTime));
        $ScoreType = [21, 33, 36, 44, 45, 51, 52, 53, 59, 70];
        // 21、绑定手机赠送 33、注册赠送 36、推广赚金(彩金) 42、邮件附件(充值彩金) 45、充值彩金  51、首充礼包(彩金)  52、邮件附件（群发彩金） 53、推广赚金(佣金)
        $winnings = DB::connection('read')->table(TableName::QPRecordDB() . 'RecordUserScoreChange')
            ->where('UpdateTime', '>=', $startTime)
            ->where('UpdateTime', '<=', $endTime)
            ->whereIn('Reason', $ScoreType)
            ->selectRaw('Isnull(SUM(ChangeScore),0) as Score,Reason')
            ->groupBy('Reason')
            ->pluck('Score', 'Reason')->toArray();

        foreach ($winnings as &$val) {
            $val = number_float($val / NumConfig::NUM_VALUE);
        }
        unset($val);
        // 彩金总和
        $winningsSum = array_sum($winnings);

        // 推广总奖励
        $totalReward = DB::connection('read')->table(TableName::QPAccountsDB() . 'SystemAgentReward')->select('TotalReward')->first()->TotalReward / NumConfig::NUM_VALUE;
        // 签到
        $signIn = DB::connection('read')->table(TableName::QPRecordDB() . 'RecordSignIn')
            ->where('SignInDate', '>=', $startTime)
            ->where('SignInDate', '<=', $endTime)
            ->selectRaw('IsNull(sum(RewardScore),0) RewardScore')
            ->first()->RewardScore / 100 ?? 0;
        return compact('winnings', 'winningsSum', 'totalReward', 'signIn');

    }
}