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

<?php
/*金币场管理*/

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Http\helper\HttpCurl;
use App\Http\helper\NumConfig;
use App\Http\logic\admin\GoldLogic;
use App\Models\GroupControlRecord;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Order;
use Excel;
use Illuminate\Support\Facades\Log;

class GoldController extends Controller
{
    //充值数据
    public function rechargeList(Request $request)
    {
        $excel = $request->get('excel');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');

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

        $where = [];
        if (empty($start_time)) {
            $where[] = ['RecordDate', '>=', date('Y-m-d', strtotime('-1 week'))];
            $start_time = date('Y-m-d', strtotime('-1 week'));
        } else {
            $where[] = ['RecordDate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['RecordDate', '<=', date('Y-m-d')];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['RecordDate', '<=', $end_time];
        }
        if ($excel) {
            $cellData = DB::connection('write')->table('QPRecordDB.dbo.LogDayRecharge as ldr')
                ->leftJoin('QPRecordDB.dbo.RecordCardCost as rcc', 'ldr.RecordDate', '=', 'rcc.mydate')
                ->select('ldr.RecordDate', 'ldr.RecordDate as week', 'ldr.TotalRechargeMoney', 'ldr.TodayUsers', 'ldr.TodayNewUsers', 'rcc.LoginCount')
                ->where($where)
                ->orderBy('RecordDate', 'Desc')
                ->get();
            foreach ($cellData as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->week = $week[date('w', strtotime($value->week))];
                $value->LoginCount == 0 ? $value->recharge_rate = 0 : $value->recharge_rate = ((round(($value->TodayUsers / $value->LoginCount), 4)) * 100) . '%';
                $value->LoginCount == 0 ? $value->arpu = 0 : $value->arpu = round(($value->TotalRechargeMoney / $value->LoginCount), 4);
                $value->TodayUsers == 0 ? $value->arppu = 0 : $value->arppu = round(($value->TotalRechargeMoney / $value->TodayUsers), 4);
                unset($value->LoginCount);
            }
            $title = ['时间', '星期', '充值收入', '今日充值用户', '新增充值用户', '充值率', 'ARPU', 'ARPPU'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '金币场充值数据');
            exit;
            array_unshift($cellData, $title);
            Excel::create('金币场充值数据', function ($excel) use ($cellData) {
                $excel->sheet('score', function ($sheet) use ($cellData) {
                    $sheet->rows($cellData);
                    $sheet->setWidth(array('A' => 15, 'B' => 15, 'C' => 15, 'D' => 15, 'E' => 15, 'F' => 15, 'G' => 15, 'H' => 15));
                });
            })->export('xls');
        } else {

            $list = DB::connection('write')->table('QPRecordDB.dbo.LogDayRecharge as ldr')
                ->leftJoin('QPRecordDB.dbo.RecordCardCost as rcc', 'ldr.RecordDate', '=', 'rcc.mydate')
                ->select('ldr.RecordDate', 'ldr.TotalRechargeMoney', 'ldr.TodayUsers', 'ldr.TodayNewUsers', 'rcc.LoginCount')
                ->where($where)
                ->orderBy('RecordDate', 'Desc')
                ->paginate(10);

            foreach ($list as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->week = $week[date('w', strtotime($value->RecordDate))];
                $value->LoginCount == 0 ? $value->recharge_rate = 0 : $value->recharge_rate = ((round(($value->TodayUsers / $value->LoginCount), 4)) * 100);
                $value->LoginCount == 0 ? $value->arpu = 0 : $value->arpu = round(($value->TotalRechargeMoney / $value->LoginCount), 4);
                $value->TodayUsers == 0 ? $value->arppu = 0 : $value->arppu = round(($value->TotalRechargeMoney / $value->TodayUsers), 4);
                $value->TotalRechargeMoney = number_float($value->TotalRechargeMoney / 100);
                $value->arpu = number_float($value->arpu / 100);
                $value->arppu = number_float($value->arppu / 100);


            }
            return view('admin.gold.recharge_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time]);
        }
    }

    //金币数据
    public function goldList(Request $request)
    {
        $excel = $request->get('excel');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $start_time = str_replace('T', ' ', $start_time);
        $end_time = str_replace('T', ' ', $end_time);

        $where = [];
        if (empty($start_time)) {
            $where[] = ['mydate', '>=', date('Y-m-d', strtotime('-1 week'))];
            $start_time = date('Y-m-d', strtotime('-1 week'));
        } else {
            $where[] = ['mydate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['mydate', '<=', date('Y-m-d')];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['mydate', '<=', $end_time];
        }
        if ($excel) {
            $cellData = DB::connection('read')->table('QPRecordDB.dbo.RecordCardCost')
                ->select('mydate', DB::raw('mydate as week'), 'AllScore', DB::raw('IsNull(Waste+Revenue-AllOutScore,0)'), 'AllOutScore', 'AllActiveUserScore', 'AllWorkingScore', 'Waste', 'Revenue', 'PlayCount')
                ->where($where)
                ->orderBy('mydate', 'desc')
                ->get();
            foreach ($cellData as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->week = $week[date('w', strtotime($value->week))];
            }
            $title = ['时间', '星期', '总金币池', '金币收入', '日发放总额', '日活跃资金量', '日流通量总额', '机器人输赢', '税收', '金币场次'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '金币场金币数据');
        } else {
            $list = DB::connection('read')->table('QPRecordDB.dbo.RecordCardCost')
                ->select('mydate', 'AllScore', DB::raw('IsNull(Waste+Revenue-AllOutScore,0) as gold'), 'AllOutScore', 'AllActiveUserScore', 'AllWorkingScore', 'Waste', 'Revenue', 'PlayCount')
                ->where($where)
                ->orderBy('mydate', 'desc')
                ->paginate(10);
            foreach ($list as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->week = $week[date('w', strtotime($value->mydate))];
                $value->AllScore = number_format($value->AllScore);
                $value->gold = number_format($value->gold);
                $value->AllOutScore = number_format($value->AllOutScore);
                $value->AllActiveUserScore = number_format($value->AllActiveUserScore);
                $value->AllWorkingScore = number_format($value->AllWorkingScore);
                $value->Waste = number_format($value->Waste);
                $value->Revenue = number_format($value->Revenue);
                $value->PlayCount = number_format($value->PlayCount);
                // $value->gold = $value->Waste + $value->Revenue - $value->AllOutScore;
            }
            return view('admin.gold.gold_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time]);
        }
    }

    //用户数据
    public function userList(Request $request)
    {
        $excel = $request->get('excel');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');

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

        $where = [];
        if (empty($start_time)) {
            $where[] = ['mydate', '>=', date('Y-m-d', strtotime('-1 week'))];
            $start_time = date('Y-m-d', strtotime('-1 week'));
        } else {
            $where[] = ['mydate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['mydate', '<=', date('Y-m-d')];
            $end_time = date('Y-m-d');
        } else {
            $where[] = ['mydate', '<=', $end_time];
        }
        if ($excel) {

            set_time_limit(0);
            $where_raw = "DATEDIFF(DD,'$start_time',ocs.InsertDateTime)>=0 AND DATEDIFF(DD,'$end_time',ocs.InsertDateTime)<=0";
            $cellData = DB::connection('read')->table('QPRecordDB.dbo.LogOnLineCountSum as ocs')
                ->leftJoin('QPRecordDB.dbo.RecordCardCost as rcc', 'rcc.mydate', '=', DB::raw('CONVERT(varchar(100), ocs.InsertDateTime, 23)'))
                ->select(DB::raw("CONVERT(varchar(100), ocs.InsertDateTime, 23) as InsertDate"), 'mydate', 'LoginCount', 'RegisterCount', DB::raw("Max(ocs.OnlineCountSum) as maxOnline"), 'SleepUserCount', DB::raw('null as lostUser'), 'WakeupUserCount', 'Retention1', 'Retention3', 'Retention7', 'Retention30', 'ARetention1', 'ARetention3', 'ARetention7', 'ARetention30')
                ->whereRaw($where_raw)
                ->groupBy('mydate', DB::raw('CONVERT(varchar(100), ocs.InsertDateTime, 23)'), 'LoginCount', 'RegisterCount', 'SleepUserCount', 'WakeupUserCount', 'Retention1', 'Retention3', 'Retention7', 'Retention30', 'ARetention1', 'ARetention3', 'ARetention7', 'ARetention30')
                ->orderBy('InsertDate', 'desc')
                ->get();
            foreach ($cellData as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->mydate = $week[date('w', strtotime($value->mydate))];
            }
            $title = ['时间', '星期', '活跃用户', '新增用户', '最高在线', '流失用户', '休眠用户', '回归用户', '新增次日留存', '新增3日留存', '新增7日留存', '新增30日留存', '活跃次日留存', '活跃3日留存', '活跃7日留存', '活跃30日留存'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '金币场用户数据');
        } else {
            $where_raw = "DATEDIFF(DD,'$start_time',ocs.InsertDateTime)>=0 AND DATEDIFF(DD,'$end_time',ocs.InsertDateTime)<=0";
            $list = DB::connection('read')->table('QPRecordDB.dbo.LogOnLineCountSum as ocs')
                ->leftJoin('QPRecordDB.dbo.RecordCardCost as rcc', 'rcc.mydate', '=', DB::raw('CONVERT(varchar(100), ocs.InsertDateTime, 23)'))
                ->select(DB::raw("CONVERT(varchar(100), ocs.InsertDateTime, 23) as InsertDate,Max(ocs.OnlineCountSum) as maxOnline"), 'LoginCount', 'RegisterCount', 'SleepUserCount', DB::raw('null as lostUser'), 'WakeupUserCount', 'Retention1', 'Retention3', 'Retention7', 'Retention30', 'ARetention1', 'ARetention3', 'ARetention7', 'ARetention30')
                ->whereRaw($where_raw)
                ->groupBy(DB::raw('CONVERT(varchar(100), ocs.InsertDateTime, 23)'), 'LoginCount', 'RegisterCount', 'SleepUserCount', 'WakeupUserCount', 'Retention1', 'Retention3', 'Retention7', 'Retention30', 'ARetention1', 'ARetention3', 'ARetention7', 'ARetention30')
                ->orderBy(DB::raw('CONVERT(varchar(100), ocs.InsertDateTime, 23)'), 'desc')
                ->paginate(10);
            foreach ($list as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->week = $week[date('w', strtotime($value->InsertDate))];
            }
            return view('admin.gold.user_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time]);
        }
    }

    //游戏日志
    public function gameLogList(Request $request)
    {

        $excel = $request->get('excel');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $kind_id = $request->input('kind_id');
        $room_type = $request->input('room_type');
        $game_level = $request->post('game_level');

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

        $room_list = [];
        if (!empty($kind_id)) {
            $room_list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
                ->where('GameID', $kind_id)
                ->select('ServerID', 'ServerName')
                ->get();
        }

        $where = [];
        if (empty($start_time)) {
            $start_time = date('Y-m-d', strtotime("$end_time -10day"));
        }
        $where[] = ['RecordDate', '>=', $start_time];
        if (empty($end_time)) {
            $end_time = date('Y-m-d');
        }


        $where[] = ['RecordDate', '<=', $end_time];
        $count_where[] = ['RecordDate', '=', date('Y-m-d', strtotime('-1 day'))];
        !empty($kind_id) ? $where[] = $count_where[] = ['KindID', '=', $kind_id] : '';

        !empty($room_type) ? $where[] = $count_where[] = ['RoomType', '=', $room_type] : '';
        //只选取斗地主 捕鱼  捞腌菜 百人斗牛 百人龙虎斗 动动三张（game_id 依次）
        $game_id = ['622', '1002', '2003', '2010', '2011', '1005'];
        //$game_name  = DB::table('QPPlatformDB.dbo.GameGameItem')->whereIn('GameId',$game_id)->pluck('GameName','GameID');//单选框数据
        $game_name = DB::connection('write')->table('QPPlatformDB.dbo.GameKindItem')->whereIn('GameID', [1005, 2030, 2050])->pluck('KindName as GameName', 'GameID');//单选框数据
        $room = ['1' => '初级场', '2' => '普通场', '4' => '中级场', '8' => '高级场', '64' => '包厢',];
        $count = DB::connection('read')->table('QPRecordDB.dbo.LogDayDataByRoom')
            ->where($count_where)
            ->count();
        $count == 0 ? $count = 1 : '';
        $start = strtotime($start_time);
        $end = strtotime($end_time);
        while ($start <= $end) {
            $date[] = date('Y-m-d', $start);
            $start = strtotime('+1 day', $start);
        }
        if ($excel) {
            $cellData = DB::connection('read')->table('QPRecordDB.dbo.LogDayDataByRoom')
                ->selectRaw("RecordDate,RecordDate as date,SUM(PlayCount) as PlayCount,SUM(PlayersCount) as PlayersCount,SUM(CONVERT(bigint,AverageCount)) as AverageCount,SUM(BankruptcyPlayersCount) as BankruptcyPlayersCount,SUM(BankruptcyRate) as BankruptcyRate,SUM(PayPlayCount) as PayPlayCount,SUM(CONVERT(bigint,PayAverageCount)) as PayAverageCount,SUM(AllLoginScore) as AllLoginScore,SUM(PayUserScore) as PayUserScore,SUM(AllWorkingScore) as AllWorkingScore,SUM(AllRevenue) as AllRevenue,SUM(CONVERT(bigint,AverageUserScore)) as AverageUserScore,SUM(CONVERT(bigint,AveragePlayScore)) as AveragePlayScore,SUM(AndroidScore) as AndroidScore")
                ->whereIn('RecordDate', $date)
                ->where($where)
                ->groupBy('RecordDate')
                ->orderBy('RecordDate', 'DESC')
                ->get();
            $title = ['时间', '星期', '游戏总局数', '登陆用户数', '人均局数', '破产用户数', '破产率', '充值用户总局数', '充值用户人均局数', '登录用户携带金币量', '付费用户携带金币量', '总流水', '总税收', '人均流水', '均局流水', '机器人输赢', '税收流水比'];
            foreach ($cellData as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->date = $week[date('w', strtotime($value->date))];
                $count == 0 ? $value->BankruptcyRate = 0 : $value->BankruptcyRate = round(($value->BankruptcyRate) / $count, 2);
                $value->AllWorkingScore == 0 ? $value->Percentage = 0 : $value->Percentage = round($value->AllRevenue / $value->AllWorkingScore * 100, 2) . '%';
            }
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '金币场游戏日志');
        } else {
            DB::connection()->enableQueryLog();
            $list = DB::connection('read')->table('QPRecordDB.dbo.LogDayDataByRoom')
                ->selectRaw('RecordDate,SUM(PlayCount) as PlayCount,SUM(PlayersCount) as PlayersCount,SUM(CONVERT(bigint,AverageCount)) as AverageCount,SUM(BankruptcyPlayersCount) as BankruptcyPlayersCount,SUM(BankruptcyRate) as BankruptcyRate,SUM(PayPlayCount) as PayPlayCount,SUM(CONVERT(bigint,PayAverageCount)) as PayAverageCount,SUM(AllLoginScore) as AllLoginScore,SUM(PayUserScore) as PayUserScore,SUM(AllWorkingScore) as AllWorkingScore,SUM(AllRevenue) as AllRevenue,SUM(CONVERT(bigint,AverageUserScore)) as AverageUserScore,SUM(CONVERT(bigint,AveragePlayScore)) as AveragePlayScore,SUM(AndroidWorkingScore) as AndroidWorkingScore,SUM(AndroidScore) as AndroidScore')
                ->whereIn('RecordDate', $date)
                ->where($where)
                ->groupBy('RecordDate')
                ->orderBy('RecordDate', 'DESC')
                ->paginate(10);

            foreach ($list as $key => &$value) {
                $week = array("日", "一", "二", "三", "四", "五", "六");
                $value->week = $week[date('w', strtotime($value->RecordDate))];
                $count == 0 ? $value->BankruptcyRate = 0 : $value->BankruptcyRate = round(($value->BankruptcyRate) / $count, 2);
                $value->AllLoginScore = number_format($value->AllLoginScore / 100, 2, '.', '');
                $value->PayUserScore = number_format($value->PayUserScore / 100, 2, '.', '');
                $value->AllWorkingScore == 0 ? $value->Percentage = 0 : $value->Percentage = round($value->AllRevenue / $value->AllWorkingScore * 100, 2) . '%';
                $value->AllWorkingScore = number_format($value->AllWorkingScore / 100, 2, '.', '');
                $value->AllRevenue = number_format($value->AllRevenue / 100, 2, '.', '');
                $value->AverageUserScore = number_format($value->AverageUserScore / 100, 2, '.', '');
                $value->AveragePlayScore = number_format($value->AveragePlayScore / 100, 2, '.', '');
                $value->AndroidScore = number_format($value->AndroidScore / 100, 2, '.', '');
            }
            return view('admin.gold.game_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time, 'room_type' => $room_type, 'game_name' => $game_name, 'kind_id' => $kind_id, 'room' => $room, 'room_list' => $room_list, 'game_level' => $game_level]);
        }
    }

    //游戏配置 -- 正式场
    public function gameConfigList(Request $request)
    {
        $kind_id = $request->post('kind_id');

        $game_level = $request->post('game_level');

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

        $room_list = [];
        $kindID_arr = config('games.openKGame');

        $ServerIDs = [32, 33, 39];  // 过滤试玩场

        if (!empty($kind_id)) {
            $where[] = ['ki.KindID', '=', $kind_id];
            $room_list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
                ->where('GameID', $kind_id)
                ->whereNotIn('ServerID', $ServerIDs)
                ->select('ServerID', 'ServerName')
                ->get();

        }

        $where = [];

        !empty($game_level) ? $where[] = ['gi.ServerID', '=', $game_level] : '';

        if (!empty($gameType)) {
            $gameType = explode(',', $gameType);

            $where[] = [function ($query) use ($gameType) {
                $query->whereIn('gi.GameID', $gameType);
            }];
        }
        $game_name = DB::connection('read')->table('QPPlatformDB.dbo.GameKindItem')
            ->whereIn('GameID', $kindID_arr)
            ->pluck('KindName as GameName', 'GameID');//单选框数据

        $list = (new GoldLogic())->gameConfigList($where, $kindID_arr);

        return view('admin.gold.game_config_list', [
            'list' => $list,
            'kind_id' => $kind_id,
            'game_level' => $game_level,
            'game_name' => $game_name,
            'room_list' => $room_list,
            'gameType' => $gameType
        ]);
    }

    //游戏配置 -- 试玩场
    public function demo_gameconfig(Request $request)
    {
        $kind_id = $request->post('kind_id');

        $game_level = $request->post('game_level');

        $room_list = [];
        $kindID_arr = [1005, 2030, 2050, 2060, 2061];

        $ServerIDs = [32, 33, 39];  // 过滤试玩场

        if (!empty($kind_id)) {
            $where[] = ['ki.KindID', '=', $kind_id];
            $room_list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
                ->where('GameID', $kind_id)
                ->whereIn('ServerID', $ServerIDs)
                ->select('ServerID', 'ServerName')
                ->get();

        }
        $where = [];

        !empty($game_level) ? $where[] = ['gi.ServerID', '=', $game_level] : '';

        $game_name = DB::connection('read')->table('QPPlatformDB.dbo.GameKindItem')
            ->whereIn('GameID', $kindID_arr)
            ->pluck('KindName as GameName', 'GameID');//单选框数据

        $list = (new GoldLogic())->gameConfigList($where, $ServerIDs, true);

        return view('admin.gold.demo_gameconfig', ['list' => $list, 'kind_id' => $kind_id, 'game_level' => $game_level, 'game_name' => $game_name, 'room_list' => $room_list, 'demo' => 1]);
    }

    //游戏配置修改页面
    public function gameConfigUpdateView(Request $request, $id)
    {

        $filed = ['gi.GameID', 'gi.ServerName', 'gi.ServerID as gi_ServerID', 'RoomStock', 'ki.KindName', 'ki.KindID', 'si.*'];
        $config = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo as gi')
            ->leftJoin('QPPlatformDB.dbo.GameKindItem as ki', 'ki.GameID', '=', 'gi.GameID')
            ->leftJoin('QPTreasureDB.dbo.StockConfigInfo as si', 'gi.ServerID', 'si.ServerID')
            ->where('gi.ServerID', $id)
            ->select($filed)
            ->first();

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

        $config->BaseScore = $config->BaseScore / 100;
        return view('admin.gold.game_config_update', ['config' => $config, 'showConfig' => $showConfig]);
    }

    //游戏配置修改
    public function gameConfigUpdate(Request $request, $id)
    {
        $params = $request->post();

        $BaseScore = (float)$params['BaseScore'] * 100;
        unset($params['BaseScore']);

        foreach ($params as $key => $val) {
            $params[$key] = (int)$val;
        }
        $params['InitScore'] = 0;
        $params['BaseScore'] = $BaseScore;

        $query = DB::connection('read')->table('QPTreasureDB.dbo.StockConfigInfo')->where('ServerID', $id)->first();
        if ($query) {
            $result = DB::table('QPTreasureDB.dbo.StockConfigInfo')->where('ServerID', $id)->update($params);
        } else {
            $params['ServerID'] = $id;
            $result = DB::table('QPTreasureDB.dbo.StockConfigInfo')->insert($params);
        }


        // 获取实时库存
        $logic = new GoldLogic();

        $res = $logic->sync_stock($id);
        $stock = $res['data']['stock'] ?? '';
        $admin_id = session('admin')->id;
        // 添加群控记录
        GroupControlRecord::add_record($id, (int)$stock, $admin_id);

        if ($result) {
            return $this->json(200, "处理成功");
        } else {
            return $this->json(500, '处理失败，请重试');
        }
    }

    //捕魚库存查询
    public function fishStock(Request $request)
    {
        $list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
            ->select('GameID', 'SortID', 'RoomStock')
            ->where('GameID', 2003)
            ->paginate(20);
        foreach ($list as $key => &$value) {
            $value->RoomStock = explode(';', chop($value->RoomStock, ';'));
            switch ($value->SortID) {
                case '1':
                    $value->SortName = '初级';
                    break;

                case '2':
                    $value->SortName = '普通';
                    break;

                case '4':
                    $value->SortName = '中级';
                    break;

                case '8':
                    $value->SortName = '高级';
                    break;

                default:
                    $value->SortName = '';
                    break;
            }
        }
        return view('admin.gold.fish_stock_list', ['list' => $list]);
    }

    //金币每日发放查询
    public function goldEverydayList(Request $request)
    {
        $excel = $request->get('excel');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $where = [];

        if (empty($start_time)) {
            $start_time = date('Y-m-d', strtotime("$end_time -9day"));
        }
        $where[] = ['mydate', '>=', $start_time];
        if (empty($end_time)) {
            $end_time = date('Y-m-d');
        }
        $where[] = ['mydate', '<=', $end_time];
        if ($excel) {
            $cellData = DB::connection('read')->table('QPRecordDB.dbo.LogDayOutScores')
                ->select('mydate', DB::raw('0 as AllScore'), 'RegisterScore', 'SignScore', 'ExchangeScore', 'SpreaderScore', 'RechargeScore', 'BankruptcyScore', 'TaskScore', 'ShareScore', 'MemberScore', 'BindPhone', 'ActivityScore')
                ->where($where)
                ->orderBy('mydate', 'desc')
                ->get();
            foreach ($cellData as $key => &$value) {
                foreach ($value as $k => $v) {
                    if ($k !== 'mydate') {
                        $value->AllScore += $v;
                    }
                }
            }
            $title = ['时间', '发放总额', '注册增送', '签到赠送', '福卡兑换', '全民推广提取', '充值发放', '破产补助', '任务获得', '每日分享', '周卡月卡发放', '比赛发放', '手机绑定发放', '活动发放'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '金币场金币每日发放查询');
        } else {
            $list = DB::connection('read')->table('QPRecordDB.dbo.LogDayOutScores')
                ->select('mydate', DB::raw('0 as AllScore'), 'RegisterScore', 'SignScore', 'ExchangeScore', 'SpreaderScore', 'RechargeScore', 'BankruptcyScore', 'TaskScore', 'ShareScore', 'MemberScore', 'BindPhone', 'ActivityScore')
                ->where($where)
                ->orderBy('mydate', 'desc')
                ->paginate(10);
            foreach ($list as $key => &$value) {
                foreach ($value as $k => &$v) {
                    if ($k !== 'mydate') {
                        $value->AllScore += $v;
                    }
                    is_numeric($v) ? $v = number_format($v) : '';
                }
                $value->AllScore = number_format($value->AllScore);
            }
            return view('admin.gold.day_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time]);
        }
    }

    //捕鱼数据统计
    public function fishList(Request $request)
    {
        $excel = $request->get('excel');
        $sort = $request->get('sort');
        $type = $request->get('type');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $where = [];
        if (empty($start_time)) {
            $where[] = ['RecordDate', '>=', date('Y-m-d', strtotime('-1 day'))];
            $start_time = date('Y-m-d', strtotime('-1 day'));
        } else {
            $where[] = ['RecordDate', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['RecordDate', '<=', date('Y-m-d', strtotime('+1 day'))];
            $end_time = date('Y-m-d', strtotime('+1 day'));
        } else {
            $where[] = ['RecordDate', '<=', date('Y-m-d', strtotime('+1 day', strtotime($end_time)))];
        }
        !empty($sort) ? $where[] = ['SortId', '=', $sort] : '';
        isset($type) ? $where[] = ['FishType', '=', $type] : '';
        if ($excel) {
            $cellData = DB::connection('write')->table('QPRecordDB.dbo.FishDotRecord')
                ->selectRaw('CONVERT(varchar(10),RecordDate,23) as RecordDate,SUM(GoldStock) as GoldStock,SUM(Revenue) as Revenue,SUM(GoldTurnover) as GoldTurnover,SUM(GoldOut) as GoldOut')
                ->where($where)
                ->orderBy('RecordDate', 'desc')
                ->groupBy(DB::raw('CONVERT(varchar(10),RecordDate,23)'))
                ->get();
            $title = ['时间', '金豆库存', '金豆税收', '金豆流水', '金豆放出'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '捕鱼数据统计');
        } else {
            $list = DB::connection('write')->table('QPRecordDB.dbo.FishDotRecord')
                ->selectRaw('CONVERT(varchar(10),RecordDate,23) as RecordDate,SUM(GoldStock) as GoldStock,SUM(Revenue) as Revenue,SUM(GoldTurnover) as GoldTurnover,SUM(GoldOut) as GoldOut')
                ->where($where)
                ->orderBy(DB::raw('CONVERT(varchar(10),RecordDate,23)'), 'desc')
                ->groupBy(DB::raw('CONVERT(varchar(10),RecordDate,23)'))
                ->paginate(10);
            $sort_name = ['1' => '初级', '2' => '普通', '4' => '中级', '8' => '高级'];
            $type_name = ['0', '1', '2', '3', '4', '5', '6', '7'];
            foreach ($list as $key => &$value) {
                foreach ($value as $k => &$v) {
                    is_numeric($v) ? $v = number_format($v) : '';
                }
            }
            return view('admin.gold.fish_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time, 'sort' => $sort, 'type' => $type, 'sort_name' => $sort_name, 'type_name' => $type_name]);
        }
    }

    //打点查看
    public function operateList(Request $request)
    {
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $excel = $request->get('excel');
        if (empty($start_time)) {
            $start_time = date('Y-m-d');
            $where[] = ['og.OperateTime', '>=', $start_time];
        } else {
            $where[] = ['og.OperateTime', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['og.OperateTime', '<=', date('Y-m-d', strtotime('+1 day'))];
            $end_time = date('Y-m-d', strtotime('+1 day'));
        } else {
            $where[] = ['og.OperateTime', '<=', date('Y-m-d', strtotime('+1 day', strtotime($end_time)))];
        }
        //只查询 商城金豆 where_group
        $where_group = ['23'];
        $data = DB::table('QPRecordDB.dbo.YN_RecordOperateGroupConfig as ogc')
            ->leftJoin('QPRecordDB.dbo.YN_RecordOperateLog as og', 'ogc.GroupID', '=', 'og.GroupID')
            ->selectRaw('IsNULL(COUNT(*),0) as count,ogc.GroupID,ogc.GroupName,og.OperateType')
            // ->where($where)
            ->whereIn('ogc.GroupID', $where_group)
            ->whereIn('OperateType', [1, 2, 3])
            ->groupBy('ogc.GroupID', 'og.OperateType', 'ogc.GroupName')
            ->orderBy('ogc.GroupID')
            ->orderBy('og.OperateType')
            ->get();
        foreach ($data as $key => $value) {
            $arr[$value->GroupID][] = json_decode(json_encode($value), true);
        }
        foreach ($arr as $k => &$v) {
            $order_arr = [];
            $operate_type = array_map('end', $v);
            if (!in_array(1, $operate_type)) {
                $v[1] = [
                    'count' => 0,
                    'GroupID' => $v[0]['GroupID'],
                    'GroupName' => $v[0]['GroupName'],
                    'OperateType' => 1,
                ];
            }
            if (!in_array(2, $operate_type)) {
                $v[2] = [
                    'count' => 0,
                    'GroupID' => $v[0]['GroupID'],
                    'GroupName' => $v[0]['GroupName'],
                    'OperateType' => 2,
                ];
            }
            if (!in_array(3, $operate_type)) {
                $v[3] = [
                    'count' => 0,
                    'GroupID' => $v[0]['GroupID'],
                    'GroupName' => $v[0]['GroupName'],
                    'OperateType' => 3,
                ];
            }
            foreach ($v as $val) {
                $order_arr[] = $val['OperateType'];
            }
            array_multisort($order_arr, SORT_ASC, SORT_NUMERIC, $v);
            $new = $excel_data = [];
            for ($i = 0; $i < count($v); $i++) {
                $new['group_id'] = $v[$i]['GroupID'];
                $new['name'] = $v[$i]['GroupName'];
                $new['count' . $i] = $v[$i]['count'];
            }
            if ($new['count0'] !== 0) {
                $new['rate1'] = number_format($new['count1'] / $new['count0'], 2, '.', '');
            } else {
                $new['rate1'] = 0;
            }
            if ($new['count1'] !== 0) {
                $new['rate2'] = number_format($new['count2'] / $new['count1'], 2, '.', '');
            } else {
                $new['rate2'] = 0;
            }
            $excel_data = [
                'name' => $new['name'],
                'count0' => $new['count0'],
                'rate1' => $new['rate1'],
                'count1' => $new['count1'],
                'rate2' => $new['rate2'],
                'count2' => $new['count2'],
            ];
            $list[] = $new;
            $cellData[] = $excel_data;
        }
        if ($excel) {
            $title = ['名称', '展现次数', '转化率', '点击次数', '转化率', '完成次数'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '打点数据统计');
        }
        return view('admin.gold.operate_list', ['list' => $list, 'end_time' => $end_time, 'start_time' => $start_time]);
    }

    //打点详情
    public function operateDetail(Request $request, $id)
    {
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');
        $excel = $request->get('excel');
        if (empty($start_time)) {
            $start_time = date('Y-m-d');
            $where[] = ['og.OperateTime', '>=', $start_time];
        } else {
            $where[] = ['og.OperateTime', '>=', $start_time];
        }
        if (empty($end_time)) {
            $where[] = ['og.OperateTime', '<=', date('Y-m-d', strtotime('+1 day'))];
            $end_time = date('Y-m-d', strtotime('+1 day'));
        } else {
            $where[] = ['og.OperateTime', '<=', date('Y-m-d', strtotime('+1 day', strtotime($end_time)))];
        }
        $operate = config('operate');
        $operate_config = $operate[$id]['goods_id'];
        $data = DB::table('QPRecordDB.dbo.YN_RecordOperateLog as rol')
            ->leftJoin('agent.dbo.goods as g', 'rol.Param', '=', 'g.id')
            ->selectRaw('g.title,count(*) as num,rol.Param,rol.OperateType')
            ->whereIn('OperateType', [1, 2, 3])
            ->whereIn('rol.Param', $operate_config)
            ->orderBy('num', 'asc')
            ->groupBy('rol.Param', 'rol.OperateType', 'g.title')
            ->get();
        foreach ($data as $key => $value) {
            $arr[$value->Param][] = json_decode(json_encode($value), true);
        }
        foreach ($arr as $k => &$v) {
            $order_arr = [];
            $operate_type = array_map('end', $v);
            if (!in_array(1, $operate_type)) {
                $v[1] = [
                    'title' => $v[0]['title'],
                    'num' => 0,
                    'Param' => $v[0]['Param'],
                    'OperateType' => 1,
                ];
            }
            if (!in_array(2, $operate_type)) {
                $v[2] = [
                    'title' => $v[0]['title'],
                    'num' => 0,
                    'Param' => $v[0]['Param'],
                    'OperateType' => 2,
                ];
            }
            if (!in_array(3, $operate_type)) {
                $v[3] = [
                    'title' => $v[0]['title'],
                    'num' => 0,
                    'Param' => $v[0]['Param'],
                    'OperateType' => 3,
                ];
            }
            foreach ($v as $val) {
                $order_arr[] = $val['OperateType'];
            }
            array_multisort($order_arr, SORT_ASC, SORT_NUMERIC, $v);
            $new = $excel_data = [];
            for ($i = 0; $i < count($v); $i++) {
                $new['name'] = $v[$i]['title'];
                $new['count' . $i] = $v[$i]['num'];
            }
            if ($new['count0'] !== 0) {
                $new['rate1'] = number_format($new['count1'] / $new['count0'], 2, '.', '');
            } else {
                $new['rate1'] = 0;
            }
            if ($new['count1'] !== 0) {
                $new['rate2'] = number_format($new['count2'] / $new['count1'], 2, '.', '');
            } else {
                $new['rate2'] = 0;
            }
            $excel_data = [
                'name' => $new['name'],
                'count0' => $new['count0'],
                'rate1' => $new['rate1'],
                'count1' => $new['count1'],
                'rate2' => $new['rate2'],
                'count2' => $new['count2'],
            ];
            $list[] = $new;
            $cellData[] = $excel_data;
        }
        if ($excel) {
            $title = ['名称', '展现次数', '转化率', '点击次数', '转化率', '完成次数'];
            $cellData = json_decode(json_encode($cellData), true);
            downloadExcel($cellData, $title, '打点数据统计');
        }
        return view('admin.gold.operate_detail', ['list' => $list, 'id' => $id]);

    }


    public function room_list(Request $request)
    {

        $ServerIDs = [32, 33, 39, 56, 57, 58];  // 过滤试玩场

        $GameID = (int)$request->post('GameID');
        $demo = $request->demo ?: 0;
        $sql = $list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
            ->where('GameID', $GameID);

        $demo == 1 ? $sql->whereIn('ServerID', $ServerIDs) : $sql->whereNotIn('ServerID', $ServerIDs);

        $list = $sql->select('ServerID', 'ServerName')->get();

        return apiReturnSuc($list);

    }

    // 更新库存
    public function sync_stock(Request $request, $id)
    {

        $logic = new GoldLogic();

        $res = $logic->sync_stock($id);
        if ($res === false) {
            return apiReturnFail($logic->getError());
        }
        return apiReturnSuc($res);
    }

    // 群控库存操作记录列表
    public function update_stock_log(Request $request)
    {
        $start_time = $request->start_time ?: '';
        $end_time = $request->end_time ?: '';
        $ServerID = $request->ServerID ?: '';
        $kind_id = $request->kind_id ?: '';

        $where = [];

        !empty($start_time) && $where[] = ['log.create_time', '>=', $start_time];
        !empty($end_time) && $where[] = ['log.create_time', '>=', $end_time];
        !empty($ServerID) && $where[] = ['log.ServerID', $ServerID];

        $kindID_arr = [1005, 2030, 2050, 2060, 2061];
        $game_name = DB::connection('read')->table('QPPlatformDB.dbo.GameKindItem')
            ->whereIn('GameID', $kindID_arr)
            ->pluck('KindName as GameName', 'GameID');//单选框数据

        $room_list = [];
        if (!empty($kind_id)) {
            $where[] = ['ki.KindID', '=', $kind_id];
            $room_list = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
                ->where('GameID', $kind_id)
                ->where('ServerType', 1)
                ->select('ServerID', 'ServerName')
                ->get();
        }

        $list = DB::connection('read')->table('agent.dbo.update_stock_log as log')
            ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gi', 'log.ServerID', 'gi.ServerID')
            ->join('agent.dbo.admin_users as au', 'log.admin_id', 'au.id')
            ->leftJoin('QPPlatformDB.dbo.GameKindItem as ki', 'ki.GameID', '=', 'gi.GameID')
            ->where($where)
            ->select('log.*', 'gi.ServerName', 'au.account')
            ->orderByDesc('log.create_time')
            ->paginate(10);

        return view('admin.gold.update_stock_log', [
            'list' => $list,
            'start_time' => $start_time,
            'end_time' => $end_time,
            'ServerID' => $ServerID,
            'game_name' => $game_name,
            'kind_id' => $kind_id,
            'room_list' => $room_list
        ]);
    }

    // 修改库存
//    public function update_stock(Request $request, $id)
//    {
//        // 展示配置
//        $showConfig = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
//            ->where('ServerID', $id)
//            ->select('CellScore', 'MinEnterScore', 'MaxEnterScore', 'ServerName', 'CellScore')
//            ->first();
//        $showConfig->CellScore = $showConfig->CellScore / 100;
//        $showConfig->MinEnterScore = number_float($showConfig->MinEnterScore / 100);
//        $showConfig->MaxEnterScore = number_float($showConfig->MaxEnterScore / 100);
//
//        if ($request->isMethod('post')) {
//
//            $stock = (int)$request->stock ?: 0;
////            if (abs($stock) > $showConfig->CellScore * 1000) {
////                return apiReturnFail('不能超过房间底分1000倍');
////            }
//            $logic = new GoldLogic();
//
//            $res = (new GoldLogic())->update_stock($id, $stock * NumConfig::NUM_VALUE);
//
//            Log::info('返回结果修改库存' . json_encode($res));
//            if ($res === false) {
//                return apiReturnFail($logic->getError());
//            }
//            $data = [
//                'ServerID' => $res['server_id'],
//                'BeforeStock' => $res['old_stock'],
//                'AfterStock' => $res['new_stock'],
//                'content' => $stock < 0 ? '减少 ' . abs($stock) : '增加 ' . $stock,
//                'admin_id' => session('admin')->id,
//            ];
//
//            DB::connection('write')->table('agent.dbo.update_stock_log')
//                ->insert($data);
//            return apiReturnSuc();
//        } else {
//            $filed = ['gi.GameID', 'gi.ServerName', 'gi.ServerID as gi_ServerID'];
//            $config = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo as gi')
//                ->where('gi.ServerID', $id)
//                ->select($filed)
//                ->first();
//            return view('admin.gold.update_stock', [
//                'showConfig' => $showConfig,
//                'config' => $config
//            ]);
//        }
//    }

    public function update_stock(Request $request, $id)
    {
        $SortID = $request->SortID;

        if ($request->isMethod('post')) {
            $Stock = $request->stock ?: 0;

            $first = DB::connection('write')->table('QPPlatformDB.dbo.RoomStockStatic')
                ->where('GameID', $id)
                ->where('SortID', $SortID)
                ->first();

            $Stock = $Stock * NumConfig::NUM_VALUE;
            if (!$first) { // 房间不存在
                DB::connection('write')->table('QPPlatformDB.dbo.RoomStockStatic')
                    ->insert(['GameID'=>$id,'SortID'=>$SortID,'Stock'=>$Stock,'Revenue'=>0]);
            }else{
                $Stock = $first->Stock + $Stock;
                DB::connection('write')->table('QPPlatformDB.dbo.RoomStockStatic')
                    ->where('GameID', $id)
                    ->where('SortID', $SortID)
                    ->update(['Stock' => $Stock]);
            }


            return apiReturnSuc();
        }
        // 展示配置
        $showConfig = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
            ->where('GameID', $id)
            ->where('SortID', $SortID)
            ->select('CellScore', 'MinEnterScore', 'MaxEnterScore', 'ServerName', 'CellScore')
            ->first();


        $ServerName = DB::connection('read')->table('QPPlatformDB.dbo.GameRoomInfo')
            ->where('GameID', $id)
            ->where('SortID', $SortID)
            ->select('ServerName')
            ->first();

        $config = DB::connection('write')->table('QPPlatformDB.dbo.RoomStockStatic')
            ->where('GameID', $id)
            ->where('SortID', $SortID)
            ->first();

//        if (!$config) {
//            return '暂无法配置';
//        }
        return view('admin.gold.update_stock', [
            'showConfig' => $showConfig,
            'config' => $config,
            'ServerName' => $ServerName,
            'GameID' => $id,
            'SortID' => $SortID
        ]);

    }
}