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

<?php

namespace App\Http\Controllers\Admin;

use App\AdminConfig;
use App\Facade\TableName;
use App\Http\Controllers\Controller;
use App\Http\helper\NumConfig;
use App\Http\logic\admin\AccountsRecordLogic;
use App\Http\logic\admin\RechargeLogic;
use App\Jobs\ClearCache;
use App\Models\Order;
use App\Models\Platform\MonthCard;
use App\Services\OrderServices;
use App\Services\PayMentService;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Validator;

class RechargeController extends Controller
{
    //充值查询
    public function rechargeList(Request $request, $history = false)
    {
        $excel = $request->get('excel');
        $start_time = $request->input('start_time');
        $end_time = $request->input('end_time');
        $create_start_time = $request->input('create_start_time');
        $create_end_time = $request->input('create_end_time');
        $register_start_time = $request->input('register_start_time');
        $register_end_time = $request->input('register_end_time');
        $order = $request->post('order');
        $type = $request->post('type', '');
        $order_sn = $request->post('order_sn') ?? "";
        $userid = (int)$request->UserID ?: "";
        $recharge_type = (int)$request->recharge_type ?: "";
        $date = $request->date ?: '';
        $Channel = $request->Channel;
        $isEmpty = $request->isEmpty ?: 0;
        $source = $request->source;
        $chargeMoney = $request->chargeMoney ?: '';

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

        $type_list = DB::table('agent.dbo.admin_configs')->where('type', 'pay')
            ->pluck('name');
//        $type_list[] = 'TestPay';
        $table = 'order as o';
        if ($history) {
            $table = 'order_back as o';
        }

        $build_sql = DB::connection('write')->table($table)
            ->leftjoin('order_goods as og', 'og.order_id', '=', 'o.id')
            ->leftjoin('goods as g', 'og.goods_id', '=', 'g.id')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', '=', 'ai.UserID')
            ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as ascore', 'o.user_id', '=', 'ascore.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountPhone as aphone', 'o.user_id', '=', 'aphone.UserID')
            ->leftJoin('QPAccountsDB.dbo.AccountsRecord as ar', function ($join) {
                $join->on('o.id', '=', 'ar.RecordID');
                $join->where('ar.type', 2);
            })
            ->leftJoin('agent.dbo.admin_users as au', 'ar.admin_id', '=', 'au.id');


        $where = [];

        $adminChannels=session('admin_channels');

        !empty($userid) && $where[] = ['ai.GameID', '=', $userid];
        !empty($order_sn) && $where[] = ['o.order_sn', $order_sn];
        !empty($type) && $where[] = ['o.payment_code', $type];
        if(!empty($Channel) || $Channel === '0') {
            $where[] = ['ai.Channel', $Channel];
        }else if(count($adminChannels)<5){
            $where[] = [function ($where) use ($adminChannels) {
                $where->whereIn('ai.Channel', $adminChannels);
            }];
        }

        // 充值来源
        if (!empty($source)) {
            if ($source == -1) {
                $where[] = ['o.GiftsID', '=', 0];
            } else {
                $where[] = ['o.GiftsID', '=', $source];
            }
        }

        // 充值档位筛选
        !empty($chargeMoney) && $where[] = ['amount', '=', ($chargeMoney * NumConfig::NUM_VALUE)];

        // 支付状态
        switch ($recharge_type) {
            case 1:
                $where[] = ['o.pay_status', '=', 1];

                break;
            case 2:
                $where[] = ['o.pay_status', '>', 1];
                break;
            case 3:
                $where[] = ['o.pay_status', '=', 0];
                break;
        }

        // 日期筛选
        if (!empty($date)) {
            switch ($date) {

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

        if ($isEmpty == 0) {
            empty($create_start_time) && $create_start_time = date('Y-m-d 00:00:00');
            empty($create_end_time) && $create_end_time = date('Y-m-d 23:59:59');
        } elseif ($isEmpty == 2) {
            empty($start_time) && $start_time = date('Y-m-d 00:00:00');
            empty($end_time) && $end_time = date('Y-m-d 23:59:59');
        }


        !empty($start_time) && $where[] = ['o.pay_at', '>=', date('Y-m-d H:i:s', strtotime($start_time))];
        !empty($end_time) && $where[] = ['o.pay_at', '<=', date('Y-m-d H:i:s', strtotime($end_time))];
        !empty($create_start_time) && $where[] = ['o.created_at', '>=', date('Y-m-d H:i:s', strtotime($create_start_time))];
        !empty($create_end_time) && $where[] = ['o.created_at', '<=', date('Y-m-d H:i:s', strtotime($create_end_time))];

        !empty($register_start_time) && $where[] = ['ai.RegisterDate', '>=', date('Y-m-d H:i:s', strtotime($register_start_time))];
        !empty($register_end_time) && $where[] = ['ai.RegisterDate', '<=', date('Y-m-d H:i:s', strtotime($register_end_time))];
        if ($request->start_time_cn) {
            $time = Carbon::createFromTimestamp(strtotime($request->start_time_cn));
            $where[] = ['o.pay_at', '>=', $time->subHours(11)->format('Y-m-d H:i:s')];
        }
        if ($request->end_time_cn) {
            $time = Carbon::createFromTimestamp(strtotime($request->end_time_cn));
            $where[] = ['o.pay_at', '<=', $time->subHours(11)->format('Y-m-d H:i:s')];
        }

        $order ? $orderby = 'g.price desc' : $orderby = 'sum(o.amount) desc';

        // 充值总金额
        $payTotalMoney = DB::connection('write')->table('agent.dbo.order as o')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', '=', 'ai.UserID')
            ->lock('with(nolock)')
            ->where($where)->selectRaw('sum(amount) as amount,count(distinct(user_id)) count_u,count(id) count_id')->first();

        $totalMoney = isset($payTotalMoney->amount) ? number_float($payTotalMoney->amount / NumConfig::NUM_VALUE) : 0;

        // 已到账
        if ($recharge_type == 1) {
            $payOverMoney = DB::connection('write')->table('agent.dbo.order as o')
                ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', '=', 'ai.UserID')
                ->where($where)
                ->lock('with(nolock)')
                ->selectRaw('sum(amount) as amount,count(distinct(user_id)) count_u,count(id) count_id')
                ->first();
        } elseif (empty($recharge_type)) {
            $payOverMoney = DB::connection('write')->table('agent.dbo.order as o')
                ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', '=', 'ai.UserID')
                ->where($where)
                ->lock('with(nolock)')
                ->where('pay_status', 1)
                ->selectRaw('sum(amount) as amount,count(distinct(user_id)) count_u,count(id) count_id')->first();
        }

        $overMoney = isset($payOverMoney->amount) ? number_float($payOverMoney->amount / NumConfig::NUM_VALUE) : 0;

        if ($excel) {
            $cellData = $build_sql
                ->selectRaw('o.order_sn,ai.GameID,o.payment_code,o.pay_status,o.amount,pay_at,o.created_at,o.after_amount,au.account,ai.Channel,ascore.Score as score,ascore.MaxScore as MaxScore,ascore.MaxWinScore as MaxWinScore')
                ->orderByDesc('o.id')
                ->where($where)
                ->lock('with(nolock)')
                ->get();

            foreach ($cellData as &$val) {
                if ($val->pay_status == 1) {
                    $val->pay_status = '已到账';
                } elseif ($val->pay_status == 0) {
                    $val->pay_status = '未支付';
                } else {
                    $val->pay_status = '充值出错';
                }
                $val->amount = number_float($val->amount / NumConfig::NUM_VALUE);
                $val->score = number_float($val->score / NumConfig::NUM_VALUE);

                $val->created_at_date = date('Y-m-d', strtotime($val->created_at));
                $val->created_at_hours = date('H:i:s', strtotime($val->created_at));
                $val->finished_at_date = !empty($val->pay_at) ? date('Y-m-d', strtotime($val->pay_at)) : '';
                $val->finished_at_hours = !empty($val->pay_at) ? date('H:i:s', strtotime($val->pay_at)) : '';
                unset($val->created_at);
                unset($val->pay_at);
            }

            $title = ['订单号', '充值玩家ID', '充值来源', '状态', '充值金额', '操作人', '玩家渠道', '订单创建日期', '订单创建时间', '订单完成日期', '订单完成日期'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '充值查询');
        } else {

            $build_sql = $build_sql
                ->selectRaw('o.*,og.goods_id,g.title,og.price as og_price,og.quantity as og_quantity,ai.GameID as ai_GameID,au.account,ar.RecordID,ar.remarks ar_remarks,ascore.Score as score,aphone.PhoneNum as Phone,ascore.MaxScore as MaxScore,ascore.MaxWinScore as MaxWinScore')
                ->lock('with(nolock)')
                ->where($where);
            if (!empty($amountSort)) {
                $build_sql->orderBy('amount', $amountSort);
            } else {
                $build_sql->orderByDesc('o.id');
            }


            $list = $build_sql->paginate(10);
            foreach ($list as &$val) {
                $val->amount = number_float($val->amount / NumConfig::NUM_VALUE);
                $val->after_amount = number_float($val->after_amount / NumConfig::NUM_VALUE);
                $val->score = number_float($val->score / NumConfig::NUM_VALUE);

                $val->MaxScore = number_float($val->MaxScore / NumConfig::NUM_VALUE);

                $val->MaxWinScore = number_float($val->MaxWinScore / NumConfig::NUM_VALUE);

                $val->created_at_date = date('Y-m-d', strtotime($val->created_at));
                $val->created_at_hours = date('H:i:s', strtotime($val->created_at));
                $val->finished_at_date = !empty($val->pay_at) ? date('Y-m-d', strtotime($val->pay_at)) : '';
                $val->finished_at_hours = !empty($val->pay_at) ? date('H:i:s', strtotime($val->pay_at)) : '';
                $val->GiftsName = Order::GiftsName($val->GiftsID);
            }

            $dates = [2 => '今日', 3 => '昨日', 4 => '本周', 5 => '本月'];
//            $allChannel = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')->where('IsAndroid', 0)->groupBy('Channel')->pluck('Channel');

            $allChannel=$adminChannels;
            // 充值来源青铜月卡，白银月卡，黄金月卡，首充礼包，周卡，商城(充值）
            //后续还要加个10卢比破冰首充礼包

            $sourceList = [
                '-1' => '商城',
                '104' => '周卡VIP1',
                '105' => '周卡VIP2',
                '106' => '周卡VIP3',
                '107' => '周卡VIP4',
                '108' => '周卡VIP5',
                '200' => '10卢比破冰首充礼包',
                '201' => '首充',
                '211' => '首充2',
                '301' => '激励1',
                '302' => '激励2',
                '303' => '激励3',
            ];
            // 充值金额 10,50，100，200,300.500,800，1000,2000,5000，10000,20000,50000,80000,100000
            $chargeMoneyList = [10,20, 50, 100, 200, 500, 1000, 5000, 10000, 30000];
            $admin = session('admin');
            $viewAll=1;
            if(!in_array($admin->roles[0]->id,[1,12,2010,2011])){
                $viewAll=0;
            }
            return view('admin.recharge.list', [
                'viewAll' => $viewAll,
                'list' => $list,
                'UserID' => $userid,
                'order_sn' => $order_sn,
                'end_time' => empty($end_time) ? '' : date('Y-m-d', strtotime($end_time)) . 'T' . date('H:i:s', strtotime($end_time)),
                'start_time' => empty($start_time) ? '' : date('Y-m-d', strtotime($start_time)) . 'T' . date('H:i:s', strtotime($start_time)),
                'create_start_time' => empty($create_start_time) ? '' : date('Y-m-d', strtotime($create_start_time)) . 'T' . date('H:i:s', strtotime($create_start_time)),
                'create_end_time' => empty($create_end_time) ? '' : date('Y-m-d', strtotime($create_end_time)) . 'T' . date('H:i:s', strtotime($create_end_time)),
                'order' => $order,
                'type' => $type,
                'type_list' => $type_list,
                'recharge_type' => $recharge_type,
                'totalMoney' => $totalMoney,
                'overMoney' => $overMoney,
                'payOverMoney' => $payOverMoney ?? 0,
                'payTotalMoney' => $payTotalMoney,
                'date' => $date,
                'dates' => $dates,
                'allChannel' => $allChannel,
                'Channel' => $Channel,
                'isEmpty' => $isEmpty,
                'register_start_time' => $register_start_time,
                'register_end_time' => $register_end_time,
                'sourceList' => $sourceList,
                'source' => $source,
                'chargeMoneyList' => $chargeMoneyList,
                'chargeMoney' => $chargeMoney,
                'amountSort' => $amountSort,
                'request' => $request,
            ]);
        }
    }

    //充值排行榜,每日&累计
    public function rechargeRank1(Request $request)
    {
        $excel = $request->get('excel');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $sort = $request->get('sort');
        $todayPayAmount = $request->get('todayPayAmount');

        $chargeMin = $request->chargeMin ?: '';
        $chargeMax = $request->chargeMax ?: '';
        $last_login_start_time = $request->last_login_start_time ?: '';
        $last_login_end_time = $request->last_login_end_time ?: '';


        $page = $_SERVER['QUERY_STRING'] ? urlencode($_SERVER['QUERY_STRING']) : 'page=1';
        $where = [];

        $where[] = ['Recharge', '>', 0];
        !empty($start_time) && $where[] = ['o.pay_at', '>=', $start_time];
        !empty($end_time) ? $where[] = ['o.pay_at', '<=', date('Y-m-d', strtotime("$end_time+1day"))] : '';

        !empty($chargeMin) && $where[] = ['Recharge', '>=', $chargeMin];
        !empty($chargeMax) && $where[] = ['Recharge', '<=', $chargeMax];
        !empty($last_login_start_time) && $where[] = ['LastLogonDate', '>=', $last_login_start_time];
        !empty($last_login_end_time) && $where[] = ['LastLogonDate', '<=', $last_login_end_time];

        if (empty($start_time)) {
            $pay_at_start_time = date('Y-m-d');
        } else {
            $pay_at_start_time = $start_time;
        }

        if (empty($end_time)) {
            $pay_at_end_time = date('Y-m-d');
        } else {
            $pay_at_end_time = $end_time;
        }

        switch ($sort) {
            case '1':
                $order_sql = 'a.Experience DESC';
                break;
            case '2':
                $order_sql = 'DATEDIFF(DAY,a.LastLogonDate,GETDATE()) DESC';
                break;
            default:
                $order_sql = 'Recharge DESC';
                break;
        }
        if ($excel) {
//            $cellData = DB::connection('write')->table('agent.dbo.order as o')
//                ->leftjoin('QPAccountsDB.dbo.AccountsInfo as a', 'o.user_id', '=', 'a.UserID')
//                ->leftjoin('QPAccountsDB.dbo.AccountPhone as i', 'o.user_id', '=', 'i.UserID')
//                ->leftjoin('agent.dbo.agent as ag', 'o.user_id', '=', 'ag.UserID')
//                ->join('QPAccountsDB.dbo.YN_VIPAccount as vip', 'o.user_id', '=', 'vip.UserID')
//                ->select(DB::raw('null as rank'), 'o.user_id', 'a.GameID', 'a.NickName', DB::raw('null as type'), 'i.PhoneNum', 'Recharge as money', DB::raw('a.Experience,a.LastLogonDate,a.RegisterDate,DATEDIFF(DAY,a.LastLogonDate,GETDATE()) as time'), 'ag.id', 'a.Channel')
//                ->whereNotNull('pay_at')
//                ->where($where)
//                ->groupBy('o.user_id', 'Recharge', 'a.GameID', 'a.NickName', 'a.LastLogonDate', 'a.RegisterDate', 'a.Experience', 'i.PhoneNum', 'ag.id', 'a.Channel')
//                ->orderByRaw($order_sql)
//                ->get();
            $cellData = DB::table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew as ds')
                ->where('Recharge', '>', 0)
                ->join(TableName::QPAccountsDB() . 'AccountsInfo as ai', 'ds.UserID', 'ai.UserID')
                ->leftjoin('QPAccountsDB.dbo.AccountPhone as ap', 'ds.UserID', 'ap.UserID')
                ->select('ai.UserID', 'ai.GameID', 'ai.NickName', 'ai.Channel', 'ap.PhoneNum', 'ds.Recharge', 'ai.PlayTimeCount', 'ai.LastLogonDate', 'ai.RegisterDate')
                ->orderByDesc('Recharge')
                ->get();

            $pyq = DB::connection('write')->table('QPAccountsDB.dbo.PyqInfo')->select('UserID')->get();
            $pyq_list = [];
            foreach ($pyq as $k => $v) {
                $pyq_list[$v->UserID] = 1;
            }
            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 . '分';

                $value->type = '玩家';

                $value->time = !empty($value->time) ? $value->time . '天' : '';
                unset($value->id);
                $value->rank = $key + 1;
            }

            $title = ['排名', '用户ID', '游戏ID', '游戏昵称', '玩家状态', '手机号', '充值总额', '游戏时长', '最后登录时间', '注册时间', '距上次登录时间', '玩家渠道'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '充值排行榜');
        } else {

            $sql = DB::connection('write')->table('agent.dbo.order as o')
                ->leftjoin('QPAccountsDB.dbo.AccountsInfo as a', 'o.user_id', '=', 'a.UserID')
                ->leftjoin('QPAccountsDB.dbo.AccountPhone as i', 'o.user_id', '=', 'i.UserID')
                ->leftjoin('QPAccountsDB.dbo.YN_VIPAccount as vip', 'o.user_id', '=', 'vip.UserID')
                ->select(DB::raw('null as rank'), 'i.PhoneNum', 'o.user_id', 'a.GameID', 'a.NickName', 'Recharge as money', DB::raw('a.Experience,a.LastLogonDate,a.RegisterDate,DATEDIFF(DAY,a.LastLogonDate,GETDATE()) as time'), 'a.Channel');

            if (!empty($todayPayAmount)) {
                $list = $sql
                    ->where('pay_at', '>=', $pay_at_start_time . ' 00:00:00')
                    ->where('pay_at', '<=', $pay_at_end_time . ' 23:59:59')
                    ->orderByRaw("sum(amount) $todayPayAmount");
            } else {
                $list = $sql
                    ->where('pay_at', '>=', $pay_at_start_time . ' 00:00:00')
                    ->where('pay_at', '<=', $pay_at_end_time . ' 23:59:59')
                    ->orderByRaw($order_sql);
            }
            $list = $list->where($where)
                ->groupBy('o.user_id', 'a.GameID', 'a.NickName', 'a.LastLogonDate', 'a.RegisterDate', 'a.Experience', 'i.PhoneNum', 'a.Channel', 'Recharge')
                ->paginate(10);

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


            $todayPay = DB::connection('write')->table('agent.dbo.order')
                ->whereIn('user_id', $UserIDs)
                ->where('pay_at', '>=', $pay_at_start_time . ' 00:00:00')
                ->where('pay_at', '<=', $pay_at_end_time . ' 23:59:59')
                ->selectRaw('IsNull(sum(amount),0) amount,user_id')
                ->groupBy('user_id')
                ->pluck('amount', 'user_id')->toArray();


            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 . '分';
                $value->type = '玩家';
                $value->rank = $key + 1;

                $value->money = number_float($value->money);
                $value->todayPayAmount = isset($todayPay[$value->user_id]) ? number_float($todayPay[$value->user_id] / NumConfig::NUM_VALUE) : 0;

            }
            $chargeMin = $request->chargeMin ?: '';
            $chargeMax = $request->chargeMax ?: '';
            $last_login_start_time = $request->last_login_start_time ?: '';
            $last_login_end_time = $request->last_login_end_time ?: '';

            $sort_list = [0 => '充值金额', 1 => '游戏时长', 2 => '距上次登录时间'];
            return view('admin.recharge.rank', [
                'list' => $list,
                'end_time' => $end_time,
                'start_time' => $start_time,
                'sort' => $sort,
                'sort_list' => $sort_list,
                'page' => '/admin/recharge/rank?' . $page,
                'todayPayAmount' => $todayPayAmount,
                'chargeMin' => $chargeMin,
                'chargeMax' => $chargeMax,
                'last_login_start_time' => $last_login_start_time,
                'last_login_end_time' => $last_login_end_time,
                'pay_at_start_time' => $pay_at_start_time,
                'pay_at_end_time' => $pay_at_end_time,
            ]);
        }
    }

    public function rechargeRank(Request $request)
    {
        $excel = $request->get('excel');
        $date = Carbon::now();
        $start_time = $request->input('start_time', $date->format('Y-m-d'));
        $end_time = $request->input('end_time', $date->format('Y-m-d'));
        $sort = $request->get('sort');
        $Channel = $request->get('Channel');


        $chargeMin = $request->chargeMin ?: '';
        $chargeMax = $request->chargeMax ?: '';

        $adminChannels = session('admin_channels');
        $Channels = session('all_channels');
        $admin = session('admin');

        $where = [];

        !empty($start_time) && $where[] = ['DateID', '>=', Carbon::parse($start_time)->format('Ymd')];
        !empty($end_time) && $where[] = ['DateID', '<', Carbon::parse($end_time)->addDay(1)->format('Ymd')];

        !empty($chargeMin) && $where[] = ['Recharge', '>=', $chargeMin];
        !empty($chargeMax) && $where[] = ['Recharge', '<=', $chargeMax];

        $where2=$where;

        $channelWhere=[];
        if(!empty($Channel) || $Channel === '0') {
            $where2[] = ['ai.Channel', $Channel];
            $channelWhere[] = ['ai.Channel', $Channel];
        }else if(count($adminChannels)<5){
            $where2[] = [function ($where) use ($adminChannels) {
                $where->whereIn('ai.Channel', $adminChannels);
            }];
            $channelWhere[] = [function ($where) use ($adminChannels) {
                $where->whereIn('ai.Channel', $adminChannels);
            }];
        }

        switch ($sort) {
            case '1':
                $order_sql = 'ai.PlayTimeCount DESC';
                break;
            case '2':
                $order_sql = 'DATEDIFF(DAY,a.LastLogonDate,GETDATE()) DESC';
                break;
            default:
                $order_sql = 'sum(ds.Recharge) DESC';
                break;
        }

        if ($excel && false) {

            $cellData = DB::table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew as ds')
                ->where('Recharge', '>', 0)
                ->where($where2)
                ->join(TableName::QPAccountsDB() . 'AccountsInfo as ai', 'ds.UserID', 'ai.UserID')
                ->leftjoin('QPAccountsDB.dbo.AccountPhone as ap', 'ds.UserID', 'ap.UserID')
                ->select('ai.UserID','ai.GameID', 'ai.NickName', 'ai.Channel', 'ap.PhoneNum', 'ai.PlayTimeCount', 'ai.LastLogonDate', 'ai.RegisterDate')
                ->lock('with(nolock)')
                ->orderByRaw($order_sql)
                ->get();

            $rechargeSum = DB::table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew as ds')
                ->where('Recharge', '>', 0)
                ->where($where)
                ->selectRaw('sum(ds.Recharge) Recharge,UserID')
                ->lock('with(nolock)')
                ->groupBy('UserID')
                ->pluck('Recharge', 'UserID')->toArray();

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


            foreach ($cellData as $key => &$value) {
                $second = $value->PlayTimeCount;
                $day = floor($second / (3600 * 24));
                $second = $second % (3600 * 24);//除去整天之后剩余的时间
                $hour = floor($second / 3600);
                $second = $second % 3600;//除去整小时之后剩余的时间
                $minute = floor($second / 60);
                //返回字符串
                $value->PlayTimeCount = $day . '天' . $hour . '小时' . $minute . '分';

                $value->type = '玩家';

                $value->money = $rechargeSum[$value->UserID] ?? 0;

                $value->time = Carbon::parse(now())->diffInDays($value->LastLogonDate, true);
            }


            $title = [ '用户ID', '游戏ID', '游戏昵称', '渠道号', '手机号','游戏时长',   '最后登录时间', '注册时间','类型','充值金额', '距上次登录时间'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '充值排行榜');
        } else {

            $channelUsers=DB::table(TableName::QPAccountsDB() . 'AccountsInfo as ai')->where($channelWhere)->select('UserID');
            $rechargeSum = DB::table(TableName::QPRecordDB() . 'RecordUserDataStatisticsNew as ds')
                ->where('Recharge', '>', 0)
                ->where($where)
                ->whereIn('UserID',$channelUsers)
                ->selectRaw('sum(ds.Recharge) RechargeAll,UserID')
                ->groupBy('UserID')
                ->lock('with(nolock)')
                ->orderByRaw($order_sql)
                ->paginate(10);


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


            $list = DB::table(TableName::QPAccountsDB() . 'AccountsInfo as ai')
                ->leftjoin('QPAccountsDB.dbo.AccountPhone as ap', 'ai.UserID', 'ap.UserID')
                ->select('ai.UserID', 'ai.GameID', 'ai.NickName', 'ai.Channel', 'ap.PhoneNum', 'ai.PlayTimeCount', 'ai.LastLogonDate', 'ai.RegisterDate')
                ->whereIn('ai.UserID',$UserIDs)
                ->lock('with(nolock)')
                ->get();

            foreach ($list as $key => &$value) {
                $second = $value->PlayTimeCount;
                $day = floor($second / (3600 * 24));
                $second = $second % (3600 * 24);//除去整天之后剩余的时间
                $hour = floor($second / 3600);
                $second = $second % 3600;//除去整小时之后剩余的时间
                $minute = floor($second / 60);

                $value2=$ReSort[$value->UserID];
                $value2->GameID=$value->GameID;
                $value2->NickName=$value->NickName;
                $value2->Channel=$value->Channel;
                $value2->PhoneNum=$value->PhoneNum;
                $value2->PlayTimeCount=$value->PlayTimeCount;
                $value2->LastLogonDate=$value->LastLogonDate;
                $value2->RegisterDate=$value->RegisterDate;
                //返回字符串
                $value2->Experience = $day . '天' . $hour . '小时' . $minute . '分';

                $value2->type = '玩家';
                $value2->rank = $key + 1;

                $value2->money = $value2->RechargeAll;//$rechargeSum[$value->UserID] ?? 0;

                $value2->time = Carbon::parse(now())->diffInDays($value->LastLogonDate, true);


            }
            $chargeMin = $request->chargeMin ?: '';
            $chargeMax = $request->chargeMax ?: '';

            $sort_list = [0 => '充值金额', 1 => '游戏时长', 2 => '距上次登录时间'];

            $channels=$adminChannels;

            return view('admin.recharge.rank', [
                'list' => $rechargeSum,
                'end_time' => $end_time,
                'start_time' => $start_time,
                'sort' => $sort,
                'sort_list' => $sort_list,
                'chargeMin' => $chargeMin,
                'chargeMax' => $chargeMax,
                'Channel'=>$Channel,
                'channels' => $channels

            ]);
        }
    }

    // 补单
    public function supplement(Request $request, $id)
    {

        try {
            if ($request->method() == 'POST') {

                $admin_id = $request->session()->get('admin')->id;

                $order = DB::table('agent.dbo.order')->where('id', $id)->first();

                if (!$order)
                    return apiReturnFail('订单不存在!');

                if ($order->pay_status == 1)
                    return apiReturnFail('订单已完成!');

                $payAmt = $order->amount / NumConfig::NUM_VALUE;
                $order_sn = $order->order_sn;

                Log::info('补单单号：' . $order_sn);
                // 成功处理回调
                $GiftsID = $order->GiftsID ?: '';
                $userID = $order->user_id ?: '';
                //$res = (new OrderService())->paySuccess($GiftsID, $userID, $payAmt, $order_sn);


                // 获取金额
                $service = new OrderServices();


                $recharge_gear = DB::connection('write')->table('agent.dbo.recharge_gear')->where('first_pay', 0)->where('money', $payAmt)->select('favorable_price', 'give')->first();
                if($recharge_gear ||  $GiftsID){
                    [$give, $favorable_price, $Recharge, $czReason, $cjReason] = $service->getPayInfo($GiftsID, $userID, $payAmt);
                }else{
                    $Recharge = $payAmt;
                    $give = 0;
                    $favorable_price = $Recharge + $give;
                    $czReason = 1;
                    $cjReason = 45;
                }


//                [$give, $favorable_price, $Recharge, $czReason, $cjReason] = $service->getPayInfo($GiftsID, $userID, $payAmt);

                // 增加充值记录
                [$Score] = $service->addRecord($userID, $payAmt, $favorable_price, $order_sn, $GiftsID, $Recharge, $czReason, $give, $cjReason, $order->AdId, $order->eventType);

                // 成功处理回调
                \App\Jobs\Order::dispatch([$userID, $payAmt, $Score, $favorable_price, $GiftsID, $order_sn]);

                // 修改订单状态
                $data = [
                    'pay_status' => 1,
                    'finished_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s'),
                    'pay_at' => date('Y-m-d H:i:s')
                ];
                $order_up = DB::table('agent.dbo.order')->where('id', $id)->update($data);

                // 添加操作记录
                (new AccountsRecordLogic())->create_record($id, $order->pay_status, 1, '', $admin_id, 2);

                if ($order_up === false) {
                    return apiReturnFail('补单失败');
                }
                return apiReturnSuc();
            }
        } catch (\Exception $exception) {
            return apiReturnFail($exception->getMessage());
        }

    }


    public function search($orderId)
    {

        $order = DB::table('agent.dbo.order')->where('id', $orderId)->first();
        if (!$order) {
            return apiReturnFail('未查询到订单信息');
        }
        $agent = $order->payment_code;
        $service = PayMentService::pay_logic($agent);
        $res = $service->pay_search($order->order_sn);

        if($res){
            return apiReturnSuc([],'','订单支付成功');
        }else{
            return apiReturnFail('订单未成功');
        }

    }


    // 充值渠道修改
    public function config($method, Request $request)
    {
        if ($request->method() == 'GET') {
            $res = (new RechargeLogic())->config($method);
            return view('admin.recharge.config', $res);
        }
        $validator = Validator::make($request->all(), [
            'config.*.sort' => 'required|int',
            'config.*.status' => 'required|in:1,-1',
        ]);
        if ($validator->fails()) {
            return apiReturnFail($validator->errors()->first());
        }
        $config = $request->input('config');
        $opened = array_filter($config, function ($item) {
            return $item['status'] == 1;
        });
        if (array_sum(array_column($opened, 'sort')) != 100) {
            return apiReturnFail('权重值分配不正确');
        }
        foreach ($config as $id => $v) {
            $v['admin_id'] = session('admin')['id'];
            $v['updated_at'] = date('Y-m-d H:i:s');
            if ($v['status'] == -1) {
                $v['sort'] = 0;
            }
            DB::table('agent.dbo.admin_configs')->where('id', $id)->update($v);
            if ($v['status'] == -1) {
                DB::table('QPPlatformDB.dbo.ChannelOpenRecharge')->where('ConfigID', $id)
                    ->update([
                        'sort' => 0,
                        'status' => 2
                ]);
            }
        }
        return apiReturnSuc();
    }

    public function methods()
    {
        $methods = DB::table('agent.dbo.admin_configs')
            ->leftJoin('agent.dbo.admin_users as u', 'admin_configs.admin_id', '=', 'u.id')
            ->select('admin_configs.*', 'u.account')
            ->where('admin_configs.type', 'pay_method')
            ->get();

        return view('admin.recharge.methods', [
            'methods' => $methods,
        ]);
    }

    // 充值渠道修改 开关
    public function switch_control(Request $request, $id)
    {
        $type = $request->type;
        $admin_id = $request->session()->get('admin')->id;

        $logic = new RechargeLogic();
        $res = $logic->switch_control($id, $type, $admin_id);
        if ($res === false) {
            return apiReturnFail($logic->getError());
        }
        ClearCache::dispatch();
        return apiReturnSuc();

    }

    // 充值档位修改
    public function gear_list()
    {
        $res = (new RechargeLogic())->gear_list();

        return view('admin.recharge.gear_list', $res);
    }


    public function channel_switch(Request $request, $id)
    {
        $type = $request->type;

        $logic = new RechargeLogic();
        $res = $logic->channel_switch($id, $type);
        if ($res === false) {
            return apiReturnFail($logic->getError());
        }
        return apiReturnSuc();
    }

    // 添加
    public function add(Request $request)
    {
        $list = DB::table('agent.dbo.admin_configs')->where('type', 'pay')->pluck('name', 'id');
        if ($request->method() == 'POST') {
            $money = (int)$request->money ?: '';
            $status = $request->status;
            $config_ids = $request->config_ids;
            $favorable_price = (int)$request->favorable_price;
            $give = (int)$request->give;
            $logic = new RechargeLogic();
            $res = $logic->add($money, $status, $config_ids, $favorable_price, $give);
            return apiReturnSuc();
        }
        return view('admin.recharge.add', ['list' => $list]);
    }

    public function update_config(Request $request, $id)
    {
        if ($request->isMethod('post')) {
            $money = (int)$request->money ?: '';
            $favorable_price = (int)$request->favorable_price;
            $give = (int)$request->give;
            $second_give = (int)$request->second_give;
            $data = [
                'money' => $money,
                'favorable_price' => $favorable_price,
                'second_give' => $second_give,
                'give' => $give
            ];
            DB::table('agent.dbo.recharge_gear')->where('id', $id)->update($data);
            return apiReturnSuc();
        } else {
            $info = DB::table('agent.dbo.recharge_gear')->where('id', $id)->first();
            return view('admin.recharge.update_config', ['info' => $info]);
        }

    }

    // 加载修改页面
    public function update(Request $request, $id)
    {
        $info = (new RechargeLogic())->update($id);
        return view('admin.recharge.update', $info);
    }

    // 修改里面的开关
    public function gear_switch(Request $request, $id)
    {
        $config_id = $request->config_id ?: '';
        $type = $request->type ?: '';

        $logic = new RechargeLogic();
        $res = $logic->gear_switch($id, $config_id, $type);
        if ($res === false) {
            return apiReturnFail($logic->getError());
        }
        ClearCache::dispatch();
        return apiReturnSuc();
    }

    // 修改排序
    public function update_sort(Request $request, $id)
    {
        $sort = $request->sort ?: 0;
        DB::connection('write')->table('agent.dbo.admin_configs')
            ->where('id', $id)
            ->update(['sort' => $sort]);
        ClearCache::dispatch();
        return apiReturnSuc();

    }

    // 添加备注
    public function remarks(Request $request, $id)
    {
        $remarks = $request->remark ?: '';

        DB::connection('write')->table('agent.dbo.admin_configs')
            ->where('id', $id)
            ->update(['remarks' => $remarks]);

        return apiReturnSuc();
    }


    // 首冲10元礼包--修改渠道
    public function firstCharge(Request $request)
    {

        $StatusValue = $request->StatusValue ?: '';
        DB::connection('write')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'FirstChargeGiftBag')
            ->update(['StatusValue' => $StatusValue]);

        return apiReturnSuc();
    }

    // 首冲10元礼包 -- 修改状态
    public function firstChargeStatus(Request $request)
    {

        $StatusValue = $request->StatusValue ?: '';
        DB::connection('write')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'FirstChargeGiftBagStatus')
            ->update(['StatusValue' => $StatusValue]);

        return apiReturnSuc();
    }

    // IP回调白名单
    public function ip_white(Request $request)
    {

        $list = DB::connection('write')->table('agent.dbo.admin_configs as ac')
            ->leftJoin('agent.dbo.recharge_ip as ri', 'ac.id', '=', 'ri.config_id')
            ->leftJoin('agent.dbo.admin_users as au', 'ri.admin_id', 'au.id')
            ->where('ac.status', 1)
            ->select('ac.id', 'ac.name', 'ac.config_value', 'ip', 'account', 'last_time', 'ac.status', 'ri.remarks')
            ->paginate(5);

        return view('admin.recharge.ip_white', compact('list'));
    }

    public function ip_white_update(Request $request, $id)
    {
        if ($request->isMethod('post')) {
            $post = $request->post();

            if (empty($post['ip'])) {
                return apiReturnFail('IP白名单不能为空');
            }
            $ip = explode(',', $post['ip']);
            foreach ($ip as $val) {
                if (!filter_var($val, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
                    return apiReturnFail('ip不合法');
                }
            }

            $post['last_time'] = date('Y-m-d H:i:s');
            $post['admin_id'] = session('admin')->id;
            DB::connection('write')->table('agent.dbo.recharge_ip')
                ->updateOrInsert(['config_id' => $id], $post);

            return apiReturnSuc();
        }
        $info = DB::connection('write')->table('agent.dbo.admin_configs as ac')
            ->leftJoin('agent.dbo.recharge_ip as ri', 'ac.id', '=', 'ri.config_id')
            ->where('ac.id', $id)
            ->select('ac.id', 'ac.name', 'ac.config_value', 'ip', 'ri.remarks')
            ->first();

        return view('admin.recharge.ip_white_update', compact('info'));
    }

    // 充值推荐档位
    public function recomendar_recharge(Request $request)
    {

        $StatusValue = $request->StatusValue ?: '';
        DB::connection('write')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'RecomendarRecharge')
            ->update(['StatusValue' => $StatusValue]);

        return apiReturnSuc();
    }

    // 修改图片地址
    public function updatePic(Request $request, $id)
    {
        $picList = config('games.recharge_pic_config');

        if ($request->isMethod('post')) {

            $pic_number = $request->post('pic_number');
            if ($pic_number < 0) {
                return apiReturnFail('请选择图片!');
            }

            $pic_url = config('games.recharge_pic.recharge_pic_url');

            $recharge_pic_url = str_replace('{num}', $picList[$pic_number], $pic_url);

            DB::table(TableName::agent() . 'admin_configs')
                ->where('id', $id)
                ->update(['config_value' => $recharge_pic_url, 'pic_num' => $pic_number]);

            return apiReturnSuc();
        }


        return view('admin.recharge.update_pic', compact('id', 'picList'));

    }

    // 轮询开关
    public function poll(Request $request)
    {
        $StatusValue = $request->StatusValue ?: '';
        DB::connection('write')->table('QPAccountsDB.dbo.SystemStatusInfo')
            ->where('StatusName', 'PollSwitch')
            ->update(['StatusValue' => $StatusValue]);

        return apiReturnSuc();
    }

    // 充值礼包
    public function gift()
    {
        $res = (new RechargeLogic())->gift();

        return view('admin.recharge.gift', $res);
    }

    public function clearCache()
    {
        ClearCache::dispatch();
        return apiReturnSuc();
    }

    public function monthCardEdit($id, Request $request) {
        $monthCard = MonthCard::findOrFail($id);
        if ($request->method() == 'GET') {
            return view('admin.recharge.month_card_edit', [
                'monthCard' => $monthCard,
            ]);
        }
        $validator = Validator::make($request->all(), [
            'Price' => 'required|min:1',
            'FirstReward' => 'required|int|min:1',
            'DayReward' => 'required|int|min:1',
        ]);
        if ($validator->fails()) {
            return apiReturnFail($validator->errors()->first());
        }
        $monthCard->Price = $request->input('Price');
        $monthCard->FirstReward = $request->input('FirstReward')*100;
        $monthCard->DayReward = $request->input('DayReward')*100;
        $monthCard->save();

        return apiReturnSuc();
    }

    public function monthCardChannelEdit($id, Request $request)
    {
        $monthCard = MonthCard::findOrFail($id);
        $gears = $monthCard->gear;
        $channels = AdminConfig::where(['type' => 'pay'])->pluck('name', 'id');
        $monthCardChannels = [];
        foreach ($channels as $id => $name) {
            $status = 0;
            foreach ($gears as $v1) {
                if ($v1['id'] == $id) {
                    $status = $v1['status'];
                    break;
                }
            }
            $monthCardChannels[] = [
                'id' => $id,
                'name' => $name,
                'status' => $status
            ];
        }

        return view('/admin/recharge/month_card_channel_edit', [
            'monthCard' => $monthCard,
            'monthCardChannels' => $monthCardChannels
        ]);
    }

    public function monthCardSwitch($CardID)
    {
        $monthCard = MonthCard::findOrFail($CardID);
        $monthCard->CardState = $monthCard->CardState ? 0 : 1;
        $monthCard->save();

        return apiReturnSuc();
    }

    public function monthCardChannelSwitch($CardID, $channel)
    {
        $monthCard = MonthCard::findOrFail($CardID);
        $gears = $monthCard->gear;
        if (in_array($channel, array_column($gears, 'id'))) {
            $status = 0;
            foreach ($gears as $k => $v) {
                if ($v['id'] == $channel) {
                    $status = $v['status'] ? 0 : 1;
                    $v['status'] = $status;
                    $gears[$k] = $v;
                    break;
                }
            }
        } else {
            $status = 1;
            $gears[] = [
                'id' => $channel,
                'status' => 1
            ];
        }
        $monthCard->gear = json_encode($gears);
        $monthCard->save();

        return apiReturnSuc();
    }
}