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

<?php

namespace App\Http\Controllers\Admin;

use App\Facade\TableName;
use App\Http\Controllers\Controller;
use App\Http\helper\Helper;
use App\Http\logic\admin\UserLogicController;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use App\Models\AccountsInfo;

class UserController extends Controller
{
    //用户查询
    public function userList(Request $request)
    {
        $excel = $request->get('excel');
        $user_id = $request->input('user_id');
        $game_id = $request->post('game_id');
        $nickname = $request->post('nickname');
        $mobile = $request->post('mobile');
        $page = $request->post('page');
        $url = $request->input('url');
        $start_time = $request->post('start_time');
        $end_time = $request->post('end_time');
        $goldmin = $request->post('goldmin');
        $goldmax = $request->post('goldmax');
        $cardmin = $request->post('cardmin');
        $cardmax = $request->post('cardmax');
        $where = [];
        !empty($start_time) ? $where[] = ['ai.RegisterDate', '>=', $start_time] : '';
        !empty($end_time) ? $where[] = ['ai.RegisterDate', '<=', date('Y-m-d', strtotime("$end_time+1day"))] : '';
        !empty($goldmin) ? $where[] = ['gi.Score', '>=', $goldmin] : '';
        !empty($goldmax) ? $where[] = ['gi.Score', '<=', $goldmax] : '';
        !empty($cardmin) ? $where[] = ['gi.Revenue', '>=', $cardmin] : '';
        !empty($cardmax) ? $where[] = ['gi.Revenue', '<=', $cardmax] : '';
        !empty($user_id) ? $where[] = ['ai.UserID', '=', (int)$user_id] : '';
        !empty($game_id) ? $where[] = ['ai.GameID', '=', (int)$game_id] : '';
        !empty($nickname) ? $where[] = ['ai.NickName', 'like', '%' . $nickname . '%'] : '';
        !empty($mobile) ? $where[] = ['ap.PhoneNum', '=', $mobile] : '';
        if ($excel) {
            $cellData = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')
                ->leftJoin('QPAccountsDB.dbo.AccountsInfo as aci', 'ai.SpreaderID', '=', 'aci.UserID')
                ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gi', 'ai.UserID', '=', 'gi.UserID')
                ->leftJoin('QPAccountsDB.dbo.AccountPhone as ap', 'ai.UserID', '=', 'ap.UserID')
                ->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as v', 'ai.UserID', '=', 'v.UserID')
                ->select('ai.NickName', 'v.Recharge', 'ai.LastLogonDate', 'ai.GameID', 'ai.MemberOrder', 'ai.RegisterDate', 'ai.UserID', 'gi.Score', 'ai.MemberOverDate', 'ap.PhoneNum', 'gi.BankScore', 'ai.Channel', 'ai.PassPortID', 'gi.InsureScore', 'aci.NickName as SpreaderName', 'ai.Compellation', 'gi.Revenue', 'aci.GameID as SpreaderGameID', 'ai.LastLogonMobile', 'ai.Experience', 'aci.UserID as SpreaderUserID')
                ->where($where)
                ->orderBy('ai.UserID', 'ASC')
                ->get();
            foreach ($cellData as $key => &$value) {
                $second = $value->Experience;
                $day = floor($second / (3600 * 24));
                $second = $second % (3600 * 24);//除去整天之后剩余的时间
                $hour = floor($second / 3600);
                $second = $second % 3600;//除去整小时之后剩余的时间
                $minute = floor($second / 60);
                //返回字符串
                $value->Experience = $day . '天' . $hour . '小时' . $minute . '分';
                switch ($value->Channel) {
                    case '1':
                        $value->Channel = '联运';
                        break;
                    case '2':
                        $value->Channel = '经销商';
                        break;
                    default:
                        $value->Channel = '官方';
                        break;
                }
            }

            $title = ['用户昵称', '充值总额', '最后登录时间', '游戏ID', 'VIP等级', '注册时间', '用户编号', '携带金豆', 'VIP过期时间', '手机号', '银行金豆', '推荐渠道', '身份证号', '房卡', '推荐人昵称', '姓名', '福卡', '推荐人ID', '操作系统', '游戏时长', '推荐人编号'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '用户查询' . date('YmdHis'));
        } else {
            $list = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')
                ->leftJoin('QPAccountsDB.dbo.AccountsInfo as aci', 'ai.SpreaderID', '=', 'aci.UserID')
                ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gi', 'ai.UserID', '=', 'gi.UserID')
                ->leftJoin('QPAccountsDB.dbo.AccountPhone as ap', 'ai.UserID', '=', 'ap.UserID')
                ->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as v', 'ai.UserID', '=', 'v.UserID')
                ->select('v.Recharge', 'ai.UserID', 'ai.GameID', 'ai.NickName', 'ai.SpreaderID', 'ai.LastLogonDate', 'ai.Nullity', 'ai.Channel', 'ai.PlayTimeCount', 'ai.RegisterDate', 'ai.MemberOrder', 'ai.MemberOverDate', 'ai.LastLogonMobile', 'ai.Experience', 'aci.NickName as SpreaderName', 'aci.GameID as SpreaderGameID', 'aci.UserID as SpreaderUserID', 'gi.InsureScore', 'gi.Score', 'gi.BankScore', 'gi.Revenue', 'ap.PhoneNum', 'ai.PassPortID', 'ai.Compellation')
                ->where($where)
                ->orderBy('ai.UserID', 'ASC')
                ->paginate(10);
            foreach ($list as $key => &$value) {
                $second = $value->Experience;
                $day = floor($second / (3600 * 24));
                $second = $second % (3600 * 24);//除去整天之后剩余的时间
                $hour = floor($second / 3600);
                $second = $second % 3600;//除去整小时之后剩余的时间
                $minute = floor($second / 60);
                //返回字符串
                $value->Experience = $day . '天' . $hour . '小时' . $minute . '分';
                switch ($value->Channel) {
                    case '1':
                        $value->Channel = '联运';
                        break;
                    case '2':
                        $value->Channel = '经销商';
                        break;
                    default:
                        $value->Channel = '官方';
                        break;
                }
            }
            return view('admin.user.user_list', ['list' => $list, 'url' => $url, 'user_id' => $user_id, 'game_id' => $game_id, 'nickname' => $nickname, 'mobile' => $mobile, 'start_time' => $start_time, 'end_time' => $end_time, 'goldmin' => $goldmin, 'goldmax' => $goldmax, 'cardmin' => $cardmin, 'cardmax' => $cardmax, 'page' => $page]);
        }
    }

    //用户查询
    public function userLost(Request $request)
    {
        $excel = $request->get('excel',1);
        $page = $request->post('page');
        $start_time = $request->get('start_time',date('Y-m-d',strtotime("-7day")));
        $end_time = $request->get('end_time');
        $goldmin = 2500;
        $where = [['ai.LastLogonDate','<=',date('Y-m-d',strtotime("-4day"))]];
        !empty($start_time) ? $where[] = ['ai.RegisterDate', '>=', $start_time] : '';
        !empty($end_time) ? $where[] = ['ai.RegisterDate', '<=', date('Y-m-d', strtotime("$end_time+1day"))] : '';
        !empty($goldmin) ? $where[] = ['gi.Score', '<>', $goldmin] : '';
        if ($excel) {
            $cellData = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')
                ->leftJoin('QPAccountsDB.dbo.AccountsInfo as aci', 'ai.SpreaderID', '=', 'aci.UserID')
                ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gi', 'ai.UserID', '=', 'gi.UserID')
                ->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as v', 'ai.UserID', '=', 'v.UserID')
                ->select('ai.NickName', 'v.Recharge','gi.WinCount','gi.LostCount', 'ai.GameID','ai.LastLogonDate','ai.RegisterDate', 'ai.UserID','gi.Score')
                ->where($where)
                ->orderBy('ai.UserID', 'ASC')
                ->get();
            foreach ($cellData as $key => &$value) {
                $gameCount = DB::table(TableName::QPRecordDB() . 'RecordUserGameCount')
                    ->where('UserID',$value->UserID)
                    ->pluck('Cnt', 'GameID')->toArray();

                $value->x50 = $gameCount[3010];
                $value->x9 = $gameCount[3014];
                $value->yyl = $gameCount[3015];

                $value->wc = $gameCount[3016];
                $value->th = $gameCount[3017];
                $value->trea = $gameCount[3018];
                $value->light = $gameCount[3019];

            }

            $title = ['用户昵称', '充值总额','游戏赢的次数','游戏输的次数', '最后登录时间','注册时间', 'GameID','携带金豆','50线','9线','摇摇乐','世界杯','雷神','宝藏','闪电'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '用户查询' . date('YmdHis'));
        } else {
            $list = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')
                ->leftJoin('QPAccountsDB.dbo.AccountsInfo as aci', 'ai.SpreaderID', '=', 'aci.UserID')
                ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gi', 'ai.UserID', '=', 'gi.UserID')
                ->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as v', 'ai.UserID', '=', 'v.UserID')
                ->select('ai.NickName', 'v.Recharge','gi.WinCount','gi.LostCount', 'ai.GameID','ai.LastLogonDate','ai.RegisterDate', 'ai.UserID','gi.Score')
                ->where($where)
                ->orderBy('ai.UserID', 'ASC')
                ->get();
            foreach ($list as $key => &$value) {
                $gameCount = DB::table(TableName::QPRecordDB() . 'RecordUserGameCount')
                    ->where('UserID',$value->UserID)
                    ->pluck('Cnt', 'GameID')->toArray();

                $value->x50 = $gameCount[3010];
                $value->x9 = $gameCount[3014];
                $value->yyl = $gameCount[3015];

                $value->wc = $gameCount[3016];
                $value->th = $gameCount[3017];
                $value->trea = $gameCount[3018];
                $value->light = $gameCount[3019];

            }
            var_dump($list);
            return view('admin.user.user_lost', ['list' => $list,'start_time' => $start_time, 'end_time' => $end_time, 'page' => $page]);
        }
    }

    //用户分布
    public function userClassify(Request $request)
    {
        $player_list = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')
            ->leftJoin('QPAccountsDB.dbo.YN_VIPAccount as va', 'ai.UserID', '=', 'va.UserID')
            ->where('IsAndroid', 0)
            ->whereIn('LastLogonMobile', ['Android', 'IOS'])
            ->select('ai.LastLogonMobile', 'ai.UserID', 'va.Recharge', 'ai.LastLogonDate', 'ai.Gender')
            // ->select(DB::raw("count('ai.*') as num,ai.LastLogonMobile,sum('va.Recharge') as m"))
            // ->groupBy('LastLogonMobile')
            ->get();
        $nowtime = time();
        $android_num = $ios_num = 0;
        $android_lively_num = $ios_lively_num = 0;
        $android_recharge = $ios_recharge = 0;
        $android_recharge_num = $ios_recharge_num = 0;
        $android_man = $android_woman = $ios_man = $ios_woman = 0;
        $android_arppu = $ios_arppu = $ios_arpu = $android_arpu = $android_man_ratio = $android_woman_ratio
            = $ios_man_ratio = $ios_woman_ratio = $android_recharge_ratio = $ios_recharge_ratio = 0;
        foreach ($player_list as $key => $value) {
            if ($value->LastLogonMobile === 'Android') {
                $android_num += 1;//安卓用户数
                $time = ceil(($nowtime - strtotime($value->LastLogonDate)) / 86400);
                if ($time <= 7) {
                    $android_lively_num += 1;//安卓七日活跃用户数
                }
                if (!empty($value->Recharge)) {
                    $android_recharge += $value->Recharge;//安卓累计充值金额
                    $android_recharge_num += 1;//安卓充值用户数
                }
                if ($value->Gender == 1) {
                    $android_man += 1;
                } else {
                    $android_woman += 1;
                }
            } else {
                $ios_num += 1;//IOS用户数
                $time = ceil(($nowtime - strtotime($value->LastLogonDate)) / 86400);
                if ($time <= 7) {
                    $ios_lively_num += 1;//IOS七日活跃用户数
                }
                if (!empty($value->Recharge)) {
                    $ios_recharge += $value->Recharge;
                    $ios_recharge_num += 1;
                }
                if ($value->Gender == 1) {
                    $ios_man += 1;
                } else {
                    $ios_woman += 1;
                }
            }
        }
        $android_num ? $android_man_ratio = round(($android_man / $android_num) * 100, 2) . '%' : '0%';//安卓男生比例
        $android_num ? $android_woman_ratio = round(($android_woman / $android_num) * 100, 2) . '%' : '0%';//安卓女生比例
        $ios_num ? $ios_man_ratio = round(($ios_man / $ios_num) * 100, 2) . '%' : '0%';//ios男生比例
        $ios_num ? $ios_woman_ratio = round(($ios_woman / $ios_num) * 100, 2) . '%' : '0%';//ios女生比例
        $android_num ? $android_recharge_ratio = round(($android_recharge_num / $android_num) * 100, 2) . '%' : '0%';//安卓充值率
        $ios_num ? $ios_recharge_ratio = round(($ios_recharge_num / $ios_num) * 100, 2) . '%' : '0%';//ios充值率
        $android_lively_num ? $android_arpu = round(($android_recharge / $android_lively_num), 2) : 0;//安卓ARPU
        $ios_lively_num ? $ios_arpu = round(($ios_recharge / $ios_lively_num), 2) : 0;//ios ARPU
        $android_recharge_num ? $android_arppu = round(($android_recharge / $android_recharge_num), 2) : 0;//ios ARPU
        $ios_recharge_num ? $ios_arppu = round(($ios_recharge / $ios_recharge_num), 2) : 0;//ios ARPU
        $list = [
            [
                'type' => '安卓',
                'num' => $android_num,
                'lively_num' => $android_lively_num,
                'recharge' => number_format($android_recharge),
                'recharge_num' => $android_recharge_num,
                'recharge_ratio' => $android_recharge_ratio,
                'arpu' => $android_arpu,
                'arppu' => $android_arppu,
                'ratio' => '男' . $android_man_ratio . '女' . $android_woman_ratio,
            ],
            [
                'type' => '苹果',
                'num' => $ios_num,
                'lively_num' => $ios_lively_num,
                'recharge' => number_format($ios_recharge),
                'recharge_num' => $ios_recharge_num,
                'recharge_ratio' => $ios_recharge_ratio,
                'arpu' => $ios_arpu,
                'arppu' => $ios_arppu,
                'ratio' => '男' . $ios_man_ratio . '女' . $ios_woman_ratio,
            ]
        ];
        return view('admin.user.user_classify', ['list' => $list]);
    }

    //购买记录
    public function purchaseHistoryList(Request $request, $id)
    {
        $start_time = $request->post('start_time');
        $end_time = $request->post('end_time');
        $order_title = $request->post('order_title');
        $order_sn = $request->post('order_sn');

        $where[] = ['user_id', '=', $id];
        !empty($start_time) ? $where[] = ['order.pay_at', '>=', $start_time] : '';
        !empty($end_time) ? $where[] = ['order.pay_at', '<=', date('Y-m-d', strtotime("$end_time+1day"))] : '';
        !empty($order_title) ? $where[] = ['order.order_title', 'like', '%' . $order_title . '%'] : '';
        !empty($order_sn) ? $where[] = ['order.order_sn', '=', $order_sn] : '';

        $list = DB::connection('write')->table('order')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'order.user_id', '=', 'ai.UserID')
            ->select('order.finished_at', 'order.order_sn', 'order.payment_sn', 'order.trade_sn', 'order.order_title', 'order.amount', 'ai.GameID', 'ai.NickName')
            ->where($where)
            ->orderby('order.finished_at', 'desc')
            ->WhereNotNull('pay_at')
            ->paginate(20);
        $nick_name = '';
        $game_id = '';
        !empty($list[0]->NickName) ? $nick_name = $list[0]->NickName : '';
        !empty($list[0]->GameID) ? $game_id = $list[0]->GameID : '';
        return view('admin.user.purchase', ['list' => $list, 'nick_name' => $nick_name, 'game_id' => $game_id, 'order_title' => $order_title, 'order_sn' => $order_sn, 'start_time' => $start_time, 'end_time' => $end_time, 'user_id' => $id]);
    }

    //游戏记录
    public function gameHistoryList(Request $request, $id)
    {
        $start_time = $request->post('start_time');
        $end_time = $request->post('end_time');
        $game = $request->post('game');
        $type = $request->post('type');

        $where[] = ['rs.UserID', '=', $id];
        !empty($start_time) ? $where[] = ['rs.InsertTime', '>=', $start_time] : '';
        !empty($end_time) ? $where[] = ['rs.InsertTime', '<=', date('Y-m-d', strtotime("$end_time+1day"))] : '';
        !empty($game) ? $where[] = ['gi.GameID', '=', $game] : '';
        !empty($type) ? $where[] = ['gi.SortID', '=', $type] : '';

        $list = DB::table('QPTreasureDB.dbo.RecordDrawScore as rs')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'rs.UserID', '=', 'ai.UserID')
            ->leftJoin('QPTreasureDB.dbo.RecordDrawInfo as ri', 'rs.DrawID', '=', 'ri.DrawID')
            ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gi', 'ri.ServerID', '=', 'gi.ServerID')
            ->select('rs.DrawID', 'rs.PlayTimeCount', 'rs.Score', 'rs.Revenue', 'rs.InsertTime', 'ai.NickName', 'ai.GameID', 'ri.ServerID', 'gi.ServerName', 'gi.SortID')
            ->where($where)
            ->orderBy('rs.InsertTime', 'desc')
            ->paginate(20);
        foreach ($list as $key => &$value) {
            $value->Score = number_format($value->Score);
            if (!empty($value)) {
                switch ($value->SortID) {
                    case '1':
                        $value->SortID = '初级';
                        break;
                    case '2':
                        $value->SortID = '普通';
                        break;
                    case '4':
                        $value->SortID = '中级';
                        break;
                    case '8':
                        $value->SortID = '高级';
                        break;
                    case '64':
                        $value->SortID = '包厢';
                        break;
                }
            }
        }
        $nick_name = '';
        $game_id = '';
        !empty($list[0]->NickName) ? $nick_name = $list[0]->NickName : '';
        !empty($list[0]->GameID) ? $game_id = $list[0]->GameID : '';
        $room_type = ['1' => '初级', '2' => '普通', '4' => '中级', '8' => '高级', '64' => '包厢'];
        $game_name = DB::connection('write')->table('QPPlatformDB.dbo.GameGameItem')->pluck('GameName', 'GameID');//单选框数据
        $level = ['1' => '初级', '2' => '普通', '4' => '中级', '8' => '高级', '64' => '包厢',];
        return view('admin.user.game', ['list' => $list, 'game' => $game, 'start_time' => $start_time, 'end_time' => $end_time, 'type' => $type, 'user_id' => $id, 'nick_name' => $nick_name, 'game_id' => $game_id, 'game_name' => $game_name, 'room_type' => $room_type]);
    }

    //金豆记录
    public function scoreHistoryList(Request $request, $id)
    {
        $start_time = $request->post('start_time');
        $end_time = $request->post('end_time');
        $describe = $request->post('describe');
        $game = $request->post('game');
        $type = $request->post('type');

        $where[] = ['si.UserID', '=', $id];
        !empty($start_time) ? $where[] = ['si.UpdateTime', '>=', $start_time . ' 00:00:00'] : '';
        !empty($end_time) ? $where[] = ['si.UpdateTime', '<=', $end_time . ' 23:59:59'] : '';
        !empty($describe) ? $where[] = ['Describe', 'like', '%' . trim($describe, " ") . '%'] : '';
        !empty($game) ? $where[] = ['ri.GameID', '=', $game] : '';
        !empty($type) ? $where[] = ['ri.SortID', '=', $type] : '';

        ////////////////////////
        // 查询范围需要在同一个月份，暂时不支持跨月份查询
        $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 < 202104 || $from_mm > intval(date('Ym'))) {
            $from_mm = date('Ym');
        }

        $table = 'QPTreasureDB.dbo.YN_RecordScoreInfo_' . $from_mm;
        //////////////////////

        $list = DB::table($table . ' as si')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'si.UserID', '=', 'ai.UserID')
            ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as ri', 'ri.ServerID', '=', 'si.ServerID')
            ->join('QPRecordDB.dbo.YN_RecordScoreConfig as sc', 'sc.Reason', '=', 'si.Reason')
            ->select('si.UserID', 'si.BeforeScore', 'si.ChangeScore', 'si.AfterScore', 'si.UpdateTime', 'ai.NickName', 'ai.GameID', 'sc.Describe', 'ri.ServerName', 'ri.SortID')
            ->where($where)
            ->orderBy('UpdateTime', 'desc')
            ->paginate(10);
        foreach ($list as $key => &$value) {
            $value->BeforeScore = number_format($value->BeforeScore);
            $value->ChangeScore = number_format($value->ChangeScore);
            $value->AfterScore = number_format($value->AfterScore);
            if (!empty($value)) {
                switch ($value->SortID) {
                    case '1':
                        $value->SortID = '初级';
                        break;
                    case '2':
                        $value->SortID = '普通';
                        break;
                    case '4':
                        $value->SortID = '中级';
                        break;
                    case '8':
                        $value->SortID = '高级';
                        break;
                    case '64':
                        $value->SortID = '包厢';
                        break;
                }
            }
        }
        $nick_name = '';
        $game_id = '';
        !empty($list[0]->NickName) ? $nick_name = $list[0]->NickName : '';
        !empty($list[0]->GameID) ? $game_id = $list[0]->GameID : '';
        $room_type = ['1' => '初级', '2' => '普通', '4' => '中级', '8' => '高级', '64' => '包厢'];
        $game_name = DB::connection('write')->table('QPPlatformDB.dbo.GameGameItem')->pluck('GameName', 'GameID');//单选框数据
        return view('admin.user.score', ['list' => $list, 'describe' => $describe, 'start_time' => $start_time, 'end_time' => $end_time, 'user_id' => $id, 'nick_name' => $nick_name, 'game_id' => $game_id, 'game' => $game, 'type' => $type, 'game_name' => $game_name, 'room_type' => $room_type]);
    }

    //福卡记录
    public function cardHistoryList(Request $request, $id)
    {
        $start_time = $request->post('start_time');
        $end_time = $request->post('end_time');
        $describe = $request->post('describe');

        $where[] = ['si.UserID', '=', $id];
        !empty($start_time) ? $where[] = ['si.UpdateTime', '>=', $start_time] : '';
        !empty($end_time) ? $where[] = ['si.UpdateTime', '<=', date('Y-m-d', strtotime("$end_time+1day"))] : '';
        !empty($describe) ? $where[] = ['Describe', 'like', '%' . trim($describe, " ") . '%'] : '';

        $list = DB::connection('write')->table('QPRecordDB.dbo.YN_RecordRevenueInfo as si')
            ->leftJoin('QPRecordDB.dbo.YN_RecordScoreConfig as sc', 'sc.Reason', '=', 'si.Reason')
            ->select('si.*', 'sc.Describe')
            ->where($where)
            ->orderBy('si.UpdateTime', 'desc')
            ->paginate(10);
        foreach ($list as $key => &$value) {
            $value->BeforeRevenue = number_format($value->BeforeRevenue);
            $value->ChangeRevenue = number_format($value->ChangeRevenue);
            $value->AfterRevenue = number_format($value->AfterRevenue);
        }
        $describe_list = DB::table('QPRecordDB.dbo.YN_RecordScoreConfig')->pluck('Describe');
        return view('admin.user.card', ['list' => $list, 'describe' => $describe, 'describe_list' => $describe_list, 'start_time' => $start_time, 'end_time' => $end_time, 'user_id' => $id]);
    }

    //冻结玩家
    public function freezeUser(Request $request, $id)
    {
        $result = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->where('UserID', $id)->update(['Nullity' => $request->post('nullity')]);
        if ($result) {
            return $this->json(200, "处理成功");
        } else {
            return $this->json(500, '处理失败，请重试');
        }
    }

    //VIP等级分布
    public function VipClassify(Request $request)
    {
        $all = AccountsInfo::selectRaw('MemberOrder,COUNT(*) AS VipAllCount')
            ->groupBy('MemberOrder')
            ->orderBy('MemberOrder')
            ->get()->toarray();
        $now = AccountsInfo::selectRaw('MemberOrder,COUNT(*) AS VipNowCount')
            ->whereRaw('MemberOverDate>GETDATE()')
            ->groupBy('MemberOrder')
            ->orderBy('MemberOrder')
            ->get()->toarray();
        foreach ($all as $key => &$value) {
            isset($now[$key]) ? $value['VipNowCount'] = $now[$key]['VipNowCount'] : $value['VipNowCount'] = 0;
            isset($now[$key]) ? $count = $now[$key]['VipNowCount'] : $count = 0;
            $value['VipOldCount'] = $value['VipAllCount'] - $count;
        }
        return view('admin.user.vip_classify', ['list' => $all]);
    }


    public function createHexiao(Request $request)
    {
        $params = $request->all();
        if ($params) {
            if (!$params['password'] || !$params['name'] || !$params['repwd'] || !$params['wx_account']) {
                return view('admin.user.create_hexiao', ['msg' => '缺少参数', 'status' => 1]);
            }
            if ($params['password'] !== $params['repwd']) {
                return view('admin.user.create_hexiao', ['msg' => '2次密码输入不一致', 'status' => 1]);
            }
            if (DB::table('agent.dbo.agent')->where('wx_account', $params['wx_account'])->first()) {
                return ['status' => false, 'message' => '登录账号不能重复'];
            }
            $password = bcrypt($params['password']);
            $data = [
                'role_id' => 0,
                'parent_id' => 0,
                'name' => $params['name'],
                'wx_account' => $params['wx_account'],
                'mobile' => 0,
                'id_card' => 0,
                'status' => 1,
                'password' => $password,
                'num_account' => $this->get_num_account(),
                'hexiao' => 1,
            ];
            if ($a = DB::table('agent.dbo.agent')->insert($data)) {
                //var_dump($a);
                $agent_id = DB::getPdo()->lastInsertId();
                DB::table('agent.dbo.agent_app')->insert([
                    'agent_id' => $agent_id,
                    'app_id' => 1
                ]);
                return view('admin.user.create_hexiao', ['msg' => '创建成功', 'status' => 1]);
            } else {
                return ['status' => false, 'message' => '创建失败', 'status' => 1];
            }
        }
        return view('admin.user.create_hexiao', ['msg' => '创建成功', 'status' => 0]);
    }

    private function get_num_account()
    {
        /* 选择一个随机的方案 */
        mt_srand((double)microtime() * 1000000000);
        $num_account = str_pad(mt_rand(1, 99999), 6, STR_PAD_LEFT);
        $agent = DB::table('agent.dbo.agent')->select('num_account')->where(array('num_account' => $num_account))->first();
        if (!$agent) {
            return $num_account;
        }

        /* 如果有重复的，则重新生成 */
        return $this->get_num_account();
    }

    //全民推广查询
    public function userSpreaderList(Request $request)
    {
        $game_id = $request->get('game_id');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $where = [];
        if (empty($start_time)) {
            $start_time = date('Y-m-d', strtotime("-9 days"));
            $where[] = ['RegisterDate', '>=', $start_time];
        } else {
            $where[] = ['RegisterDate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['RegisterDate', '<=', date('Y-m-d')];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['RegisterDate', '<=', $end_time];
        }
        $user_info = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->select('NickName', 'UserID')
            ->where('GameID', $game_id)
            ->first();
        if (empty($user_info)) {
            return view('admin.user.spreaders', ['list' => [], 'game_id' => $game_id, 'start_time' => $start_time, 'end_time' => $end_time]);
        }
        $user_id = $user_info->UserID;
        !empty($user_id) ? $where[] = ['SpreaderID', '=', $user_id] : '';
        //推广员姓名
        $name = DB::table('agent')->Where('UserID', $user_id)->value('name');
        //推广总用户数
        $children_count = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->where($where)
            ->count();
        //活跃用户
        $live_users = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->where($where)
            ->where('LastLogonDate', '>=', date('Y-m-d', strtotime('-7 days')))
            ->count();
        //有效用户
        $eff_user = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->where($where)
            ->where('Experience', '>=', 6000)
            ->count();
        //流失用户
        $sleep_users = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->where($where)
            ->where('LastLogonDate', '<=', date('Y-m-d', strtotime('-7 days')))
            ->count();
        //已提收益
        $gold = DB::table('QPAccountsDB.dbo.YN_Withdrawal')
            ->selectRaw('sum(Gold) as gold')
            ->where('UserID', $user_id)
            ->value('gold');
        //计提收益
        $revenue = DB::table('QPAccountsDB.dbo.YN_Agent')
            ->where('UserID', $user_id)
            ->value('Revenue');
        //总收益
        $revenue_sum = DB::table('QPTreasureDB.dbo.YN_HistoryPerformance')
            ->select(DB::raw('sum(ProfitGold0+ProfitGold1+ProfitGold2) as revenue_sum'))
            ->where('UserID', $user_id)
            ->where('BalanceDate', '>=', $start_time)
            ->where('BalanceDate', '<=', $end_time)
            ->value('revenue_sum');
        $result = [
            'user_id' => $user_id,
            'game_id' => $game_id,
            'nick_name' => $user_info->NickName,
            'name' => $name,
            'children_count' => $children_count,
            'live_users' => $live_users,
            'eff_user' => $eff_user,
            'sleep_users' => $sleep_users,
            'gold' => $gold,
            'revenue' => $revenue,
            'revenue_sum' => $revenue_sum,
        ];
        return view('admin.user.spreaders', ['list' => $result, 'game_id' => $game_id, 'start_time' => $start_time, 'end_time' => $end_time]);
    }

    //每日推广数据
    public function everydaySpreaderList(Request $request, $user_id)
    {
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $where = [];
        !empty($user_id) ? $where[] = ['SpreaderID', '=', $user_id] : '';
        if (empty($start_time)) {
            $start_time = date('Y-m-d', strtotime("-50days"));
            $where[] = ['RegisterDate', '>=', $start_time];
        } else {
            $where[] = ['RegisterDate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['RegisterDate', '<=', date('Y-m-d')];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['RegisterDate', '<=', $end_time];
        }
        $begintime = strtotime($start_time);
        $endtime = strtotime($end_time);
        for ($start = $begintime; $start <= $endtime; $start += 24 * 3600) {
            $date_arr[] = ['date' => date("Y-m-d", $start)];
        }
        $user_info = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->select('NickName', 'GameID')
            ->where('UserID', $user_id)
            ->first();
        //每日新增
        $add_users_list = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->selectRaw('CONVERT(varchar(100),RegisterDate,23) as date,count(*) as count')
            ->where($where)
            ->where('SpreaderID', $user_id)
            ->groupBy(DB::raw('CONVERT(varchar(100),RegisterDate,23)'))
            ->get();
        // print_r($add_users_list);exit;
        //每日收益
        $revenue_sum_list = DB::table('QPTreasureDB.dbo.YN_HistoryPerformance')
            ->selectRaw('BalanceDate,sum(ProfitGold0+ProfitGold1+ProfitGold2) as revenue_sum')
            ->where('BalanceDate', '>=', $start_time)
            ->where('BalanceDate', '<=', $end_time)
            ->where('UserID', $user_id)
            ->groupBy('BalanceDate')
            ->get();
        foreach ($revenue_sum_list as $key => $value) {
            $revenue_sum[$value->BalanceDate] = $value->revenue_sum;
        }
        $add_users = [];
        foreach ($add_users_list as $key => $value) {
            $add_users[$value->date] = $value->count;
        }
        $retain_users = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->select('UserID', DB::raw('CONVERT(varchar(100),RegisterDate,23) as date'))
            ->where('SpreaderID', $user_id)
            ->whereIn(DB::raw('CONVERT(varchar(100),RegisterDate,23)'), $date_arr)
            ->get();
        foreach ($date_arr as $key => &$value) {
            if (isset($add_users[$value['date']])) {
                $value['count'] = $add_users[$value['date']];
            } else {
                $value['count'] = 0;
            }
            if (isset($revenue_sum[$value['date']])) {
                $value['revenue_sum'] = $revenue_sum[$value['date']];
            } else {
                $value['revenue_sum'] = 0;
            }
            $date = $value['date'];
            $retain_users = DB::table('QPAccountsDB.dbo.AccountsInfo')
                ->select('UserID', DB::raw('CONVERT(varchar(100),RegisterDate,23) as date'))
                ->where('SpreaderID', $user_id)
                ->where(DB::raw('CONVERT(varchar(100),RegisterDate,23)'), date('Y-m-d', strtotime("$date-2 days")))
                // ->groupBy(DB::raw('CONVERT(varchar(100),RegisterDate,23)'))
                ->get()
                ->toArray();
            $count = 0;
            if ($retain_users) {
                foreach ($retain_users as $k => $v) {
                    $count += DB::table('QPAccountsDB.dbo.AccountsInfo')
                        ->where('UserID', $v->UserID)
                        ->where(DB::raw('CONVERT(varchar(100),LastLogonDate,23)'), date('Y-m-d', strtotime("$v->date+1 days")))
                        ->count();
                }
            }
            //每日流失
            $sleep_users = DB::table('QPAccountsDB.dbo.AccountsInfo')
                ->where('SpreaderID', $user_id)
                ->where('LastLogonDate', '<=', date('Y-m-d', strtotime("$date-7 days")))
                ->count();
            $value['sleep'] = $sleep_users;
            $value['retain'] = $count;
        }

        return view('admin.user.everyday_spreaders', ['list' => $date_arr, 'user_id' => $user_id, 'start_time' => $start_time, 'end_time' => $end_time, 'user_info' => $user_info]);
    }

    //下级推广数据
    public function subordinateList(Request $request, $user_id)
    {
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $where = [];
        !empty($user_id) ? $where[] = ['ai.SpreaderID', '=', $user_id] : '';
        if (empty($start_time)) {
            $start_time = date('Y-m-d', strtotime("-100days"));
            $where[] = ['ai.RegisterDate', '>=', $start_time];
        } else {
            $where[] = ['ai.RegisterDate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['ai.RegisterDate', '<=', date('Y-m-d')];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['ai.RegisterDate', '<=', $end_time];
        }
        $user_info = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->select('NickName', 'GameID')
            ->where('UserID', $user_id)
            ->first();
        $add_users_list = DB::table('QPAccountsDB.dbo.AccountsInfo as ai')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ais', 'ais.SpreaderID', '=', 'ai.UserID')
            ->selectRaw('ai.UserID,ai.NickName,count(ais.UserID) as count')
            ->where($where)
            ->groupBy('ai.UserID', 'ai.NickName')
            // ->get();
            ->paginate(10);
        $add_users_val = array_column(json_decode(json_encode($add_users_list), true)['data'], 'UserID');
        // $add_users_val = array_column(json_decode(json_encode($add_users_list),true),'UserID');
        // print_r($add_users_val);
        // 活跃用户
        $live_list = DB::table('QPAccountsDB.dbo.AccountsInfo')
            ->selectRaw('SpreaderID,count(*) as count')
            ->where('RegisterDate', '>=', $start_time)
            ->where('RegisterDate', '<=', $end_time)
            ->whereIn('SpreaderID', $add_users_val)
            ->where('LastLogonDate', '>=', date('Y-m-d', strtotime('-7 days')))
            ->groupBy('SpreaderID')
            ->get();
        $lives = [];
        foreach ($live_list as $key => $value) {
            $lives[$value->SpreaderID] = $value->count;
        }
        //收益
        $revenue_sum = DB::table('QPTreasureDB.dbo.YN_HistoryPerformance')
            ->selectRaw('UserID,sum(ProfitGold0+ProfitGold1+ProfitGold2) as revenue_sum')
            ->whereIn('UserID', $add_users_val)
            ->where('BalanceDate', '>=', $start_time)
            ->where('BalanceDate', '<=', $end_time)
            ->groupBy('UserID')
            ->get();
        $revenue = [];
        foreach ($revenue_sum as $key => $value) {
            $revenue[$value->UserID] = $value->revenue_sum;
        }
        foreach ($add_users_list as $key => &$value) {
            if (isset($lives[$value->UserID])) {
                $value->live = $lives[$value->UserID];
            } else {
                $value->live = 0;
            }
            if (isset($revenue[$value->UserID])) {
                $value->revenue = $revenue[$value->UserID];
            } else {
                $value->revenue = 0;
            }
        }
        return view('admin.user.subordinate', ['list' => $add_users_list, 'user_id' => $user_id, 'start_time' => $start_time, 'end_time' => $end_time, 'user_info' => $user_info]);
    }

    //核销查询
    public function verificationList(Request $request)
    {
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $user_id = $request->input('user_id');
        $where = [];
        $where[] = ['code.State', '=', 1];
        !empty($user_id) ? $where[] = ['code.agent_id', '=', $user_id] : '';
        if (empty($start_time)) {
            $start_time = date('Y-m-d');
            $where[] = ['code.UpdateTime', '>=', $start_time];
        } else {
            $where[] = ['code.UpdateTime', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['code.UpdateTime', '<=', date('Y-m-d', strtotime("+1 days"))];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['code.UpdateTime', '<=', $end_time];
        }

        $list = DB::table('QPAccountsDB.dbo.YN_UniqueCode as code')
            ->leftJoin('QPAccountsDB.dbo.YN_MatchProp as prop', 'code.PropID', '=', 'prop.PropID')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as account', 'code.UserID', '=', 'account.UserID')
            ->leftJoin('agent.dbo.agent as agent', 'code.agent_id', '=', 'agent.id')
            ->selectRaw('account.NickName,prop.PropName,prop.Description,agent.name,code.UpdateTime,code.agent_id')
            ->where($where)
            ->orderBy('code.UpdateTime', 'desc')
            ->paginate(10);
        return view('admin.user.verification', ['list' => $list, 'user_id' => $user_id, 'start_time' => $start_time, 'end_time' => $end_time]);
    }

    // 用户金额变化明细
    public function scoreChange(Request $request)
    {

        $start_time = $request->get('start_time');
        $end_time = $request->get('end_time');
        $describe = (int)$request->get('describe') ?: '';

        $UserID = (int)$request->get('UserID') ?: '';
        $ChangeScoreMin = $request->ChangeScoreMin;
        $ChangeScoreMax = $request->ChangeScoreMax;
        $ChangeScoreSort = $request->ChangeScoreSort ?: '';

        $r = (new UserLogicController())->scoreChange($start_time, $end_time, $describe, $UserID, $ChangeScoreMin, $ChangeScoreMax, $ChangeScoreSort);
        return view('admin.user.score_change', $r);
    }

    // 绑定关系查询
    public function bind_list(Request $request)
    {
        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';
        $Higher1ID = $request->Higher1ID ?: '';
        $Higher2ID = $request->Higher2ID ?: '';
        $SpreaderID = $request->SpreaderID ?: '';

        // 跳转页面条件
        $source = $request->source ?: 1;
        $NickName = $request->NickName ?: '';
        $GameID = $request->GameID ?: '';
        $mobile = $request->mobile ?: '';
        $Type = $request->Type ?: '';
        $Sort = $request->Sort ?: '';
        $FinalScoreSort = $request->FinalScoreSort ?: '';


        $UserID = $request->UserID ?: '';

        $result = (new UserLogicController())->bind_list(
            $start_time,
            $end_time,
            $UserID,
            $Higher1ID,
            $Higher2ID,
            $SpreaderID,
            $request
        );

        $result['source'] = $source;
        $result['NickName'] = $NickName;
        $result['GameID'] = $GameID;
        $result['mobile'] = $mobile;
        $result['Type'] = $Type;
        $result['Sort'] = $Sort;
        $result['FinalScoreSort'] = $FinalScoreSort;
        $result['request'] = $request;
        if (empty($Higher1ID) && empty($Higher2ID)) {
            return view('admin.user.bind_list', $result);
        } else {
            return view('admin.user.bind_child_list', $result);
        }
    }

    public function user_tab($tab_id)
    {

        $explain = Cache::remember($tab_id, Carbon::tomorrow(), function () use ($tab_id) {
            return $explain = DB::table('QPAccountsDB.dbo.AccountsTabExplain')
                ->where('TabID', $tab_id)
                ->value('Explain');
        });

        return apiReturnSuc($explain);
    }

    // 每日绑定查询
    public function dailyBinding(Request $request)
    {
        $start_time = str_replace('T', ' ', $request->start_time) ?: date('Y-m-d 00:00:00');
        $end_time = str_replace('T', ' ', $request->end_time) ?: date('Y-m-d 23:59:59');
        $spreaderID = $request->SpreaderID ?: '';
        $GameID = $request->GameID ?: '';
        $date = $request->date ?: '';

        switch ($date) {
            case 1:
                $start_time = date('Y-m-d 00:00:00');
                break;
            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");
                // 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'));
                break;
            case 4:
                $start_time = date("Y-m-01 00:00:00");
                break;
        }

        $where = [];

        !empty($start_time) && $where[] = ['ai.RegisterDate', '>=', $start_time];
        !empty($end_time) && $where[] = ['ai.RegisterDate', '<=', $end_time];
        !empty($spreaderID) && $where[] = ['ai1.GameID', $spreaderID];
        !empty($GameID) && $where[] = ['ai.GameID', $GameID];


        $list = DB::connection('read')->table('QPAccountsDB.dbo.UserAgent as ua')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ua.UserID', 'ai.UserID')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai1', 'ua.Higher1ID', 'ai1.UserID')
            ->select('ai.GameID', 'ai.UserID', 'ai.RegisterDate', 'ai.LastLogonDate', 'ai1.GameID as SpreaderID', 'ai1.UserID as ai1UserID')
            ->where('Higher1ID', '>', 0)
            ->where($where)
            ->orderBy('ai.RegisterDate', 'desc')
            ->paginate(10);

        $start_time = Helper::timeChange($start_time);
        $end_time = Helper::timeChange($end_time);
        $dates = [1 => '今日', 2 => '昨日', 3 => '本周', 4 => '本月'];
        $data = compact('list', 'start_time', 'end_time', 'spreaderID', 'GameID', 'date', 'dates');
        return view('admin.user.daily_binding', $data);
    }

    public function moneyDeviation(Request $request)
    {
        $query = DB::table('QPRecordDB.dbo.RecordUserTotalStatistics as ru')
            ->leftJoin(
                'QPAccountsDB.dbo.AccountsInfo as ai',
                'ai.UserID',
                '=',
                'ru.UserID'
            )
            ->lock('with(nolock)');
        if ($request->input('sort')) {
            $query->orderByRaw($request->input('sort'));
        }
        if ($request->input('GameID')) {
            $query->where('GameID', $request->input('GameID'));
        }
        if ($request->input('NickName')) {
            $query->where('NickName', $request->input('NickName'));
        }
        if ($request->input('Channel')) {
            $query->where('Channel', $request->input('Channel'));
        }
        if ($request->input('RegisterDateStart')) {
            $query->where('RegisterDate', '>=',
                \Carbon\Carbon::createFromTimestamp(strtotime($request->input('RegisterDateStart')))
                ->format('Y-m-d H:i:00'));
        }
        if ($request->input('RegisterDateEnd')) {
            $query->where('RegisterDate', '<=',
                \Carbon\Carbon::createFromTimestamp(strtotime($request->input('RegisterDateEnd')))
                    ->format('Y-m-d H:i:59.999'));
        }
        if ($request->input('LastLogonDateStart')) {
            $query->where('LastLogonDate', '>=',
                \Carbon\Carbon::createFromTimestamp(strtotime($request->input('LastLogonDateStart')))
                    ->format('Y-m-d H:i:00'));
        }
        if ($request->input('LastLogonDateEnd')) {
            $query->where('LastLogonDate', '<=',
                \Carbon\Carbon::createFromTimestamp(strtotime($request->input('LastLogonDateEnd')))
                    ->format('Y-m-d H:i:59.999'));
        }
        $kinds = DB::table('QPPlatformDB.dbo.GameKindItem')
            ->pluck('KindName', 'KindID');
        if ($request->input('export')) {
            $data = [];
            $query->selectRaw("ai.*, Recharge-Withdraw/100 as deviation, Recharge,
            Withdraw/100 as Withdraw, Handsel, DrawBase")
                ->orderBy('ai.UserID')
                ->chunk(1000, function ($items) use (&$data) {
                    $userIDs = $items->pluck('UserID');
                    $res = AccountsInfo::query()->with('gameScoreInfo', 'accountPhoneRelation', 'gameScoreLocker')
                        ->whereIn('UserID', $userIDs)
                        ->get();
                    $userInfos = [];
                    foreach ($res as $k => $v) {
                        $userInfos[$v->UserID] = $v;
                    }
                    $res1 = DB::table('QPRecordDB.dbo.RecordUserDataStatisticsNew')->whereIn('UserID', $userIDs)
                        ->where('DateID', date('Ymd'))
                        ->get();
                    $statNew = [];
                    foreach ($res1 as $k => $v) {
                        $statNew[$v->UserID] = $v;
                    }
                    $insert = [];
                    foreach ($items as $k => $v) {
                        $insert['GameID'] = $userInfos[$v->UserID]->GameID;
                        $insert['NickName'] = $userInfos[$v->UserID]->NickName;
                        $insert['Channel'] = $userInfos[$v->UserID]->Channel;
                        $insert['LastLogonDate'] = $userInfos[$v->UserID]->LastLogonDate;
                        $insert['LastLogonDateCN'] =
                            date('Y-m-d H:i:s', strtotime($userInfos[$v->UserID]->LastLogonDate . ' +11 hours'));
                        $insert['PhoneNum'] = $userInfos[$v->UserID]->accountPhoneRelation->PhoneNum ?? '';
                        $insert['Score'] = $userInfos[$v->UserID]->gameScoreInfo->Score ?? '';
                        $insert['deviation'] = $v->deviation;
                        $insert['Recharge'] = $v->Recharge . '/' . ($statNew[$v->UserID]->Recharge ?? 0);
                        $insert['Withdraw'] = $v->Withdraw . '/' . ($statNew[$v->UserID]->Withdraw ?? 0)/100;
                        $insert['Handsel'] = $v->Handsel . '/' . ($statNew[$v->UserID]->Handsel ?? 0);
                        $insert['DrawBase'] = $v->DrawBase;
                        $insert['IP'] = $userInfos[$v->UserID]->LastLogonIP;
                        $insert['Online'] = $kinds[$userInfos[$v->UserID]->gameScoreLocker->KindID] ?? '离线';
                        $insert['RegisterDate'] = $userInfos[$v->UserID]->RegisterDate;
                        $insert['RegisterDateCN'] = date(
                            'Y-m-d H:i:s',
                            strtotime($userInfos[$v->UserID]->RegisterDate . ' +11 hours')
                        );
                        $data[] = $insert;
                    }
                });

            downloadExcel($data, [__('auto.会员ID'), __('auto.昵称'), __('auto.最后登录时间（当地）'), __('auto.最后登录时间（中国）'), __('auto.手机号'), __('auto.余额'),
                                  __('auto.茶叶减咖啡（累计）'), __('auto.今日/历史充值'), __('auto.今日/历史茶叶'), __('auto.今日/历史彩金'), __('auto.可茶叶金额'), __('auto.IP'),
                                  __('auto.在线状态'), __('auto.注册日期（当地）'), __('auto.注册日期（中国）'),
            ],__('auto.提充比查询').'.xls');
        }
        $page = $query->selectRaw("ai.*, Recharge-Withdraw/100 as deviation, Recharge,
            Withdraw/100 as Withdraw, Handsel, DrawBase")
            ->whereRaw('Recharge-Withdraw/100 < 0')
            ->paginate(10);
        $userIDs = array_column($page->items(), 'UserID');
        if ($userIDs) {
            $res = AccountsInfo::query()->with('gameScoreInfo', 'accountPhoneRelation', 'gameScoreLocker')
                ->whereIn('UserID', $userIDs)
                ->get();
            $userInfos = [];
            foreach ($res as $k => $v) {
                $userInfos[$v->UserID] = $v;
            }
            $res1 = DB::table('QPRecordDB.dbo.RecordUserDataStatisticsNew')->whereIn('UserID', $userIDs)
                ->where('DateID', date('Ymd'))
                ->get();
            $statNew = [];
            foreach ($res1 as $k => $v) {
                $statNew[$v->UserID] = $v;
            }
            foreach ($page as $k => $v) {
                if(!(isset($userInfos[$v->UserID]) && @$userInfos[$v->UserID])){
                    continue;
                }
                $page[$k]->UserID = $v->UserID;
                $page[$k]->GameID = $userInfos[$v->UserID]->GameID;
                $page[$k]->NickName = $userInfos[$v->UserID]->NickName;
                $page[$k]->LastLogonDate = $userInfos[$v->UserID]->LastLogonDate;
                $page[$k]->LastLogonDateCN =
                    date('Y-m-d H:i:s', strtotime($userInfos[$v->UserID]->LastLogonDate . ' +11 hours'));
                $page[$k]->PhoneNum = $userInfos[$v->UserID]->accountPhoneRelation->PhoneNum ?? '';
                $page[$k]->Score = $userInfos[$v->UserID]->gameScoreInfo->Score ?? '';
                $page[$k]->deviation = $v->deviation;
                $page[$k]->Recharge = $v->Recharge;
                $page[$k]->RechargeToday = $statNew[$v->UserID]->Recharge ?? 0;
                $page[$k]->Withdraw = $v->Withdraw;
                $page[$k]->WithdrawToday = ($statNew[$v->UserID]->Withdraw ?? 0)/100;
                $page[$k]->Handsel = $v->Handsel;
                $page[$k]->HandselToday = $statNew[$v->UserID]->Handsel ?? 0;
                $page[$k]->DrawBase = $v->DrawBase;
                $page[$k]->IP = $userInfos[$v->UserID]->LastLogonIP;
                $page[$k]->Online = $kinds[$userInfos[$v->UserID]->gameScoreLocker->KindID] ?? '离线';
                $page[$k]->RegisterDate = $userInfos[$v->UserID]->RegisterDate;
                $page[$k]->RegisterDateCN = date(
                    'Y-m-d H:i:s',
                    strtotime($userInfos[$v->UserID]->RegisterDate . ' +11 hours')
                );
            }
        }
        $channels = [0 => '全部'] + DB::connection('read')->table('QPPlatformDB.dbo.ChannelPackageName')
            ->selectRaw('Channel')->groupBy('Channel')->pluck('Channel', 'Channel')
                ->toArray();
        $total = $query->count();
        $recharge = $query->sum('Recharge');
        $withdraw = $query->sum(DB::raw('Withdraw/100'));
        return view('admin.user.money_deviation', [
            'list' => $page,
            'request' => $request,
            'channels' => $channels,
            'total' => $total,
            'money' => "$withdraw/$recharge",
        ]);
    }
}