<?php
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Order;
use Excel;
use Illuminate\Support\Carbon;
class CardController extends Controller
{
    //房卡数据统计
    public function statisticsList(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];
        }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::table('QPRecordDB.dbo.RecordCardCost')
                ->select('mydate','mydate as week','LoginCount','createtimes','costcard','RegisterCount')->where($where)->orderBy('mydate','desc')->get()->toArray();
            $title = ['时间','星期','活跃用户','房卡局数','房卡消耗量','新增用户数','充值用户数','新增充值用户数','房卡充值率','充值金额','订单数','ARPU','ARPPU'];
            foreach ($cellData as $key => &$value) {
                $week = array("日","一","二","三","四","五","六");
                $value->week = $week[date('w',strtotime($value->week))];
            }
            $cellData = json_decode(json_encode($cellData),true);
            downloadExcel($cellData,$title,'房卡数据统计');
        }else{
            $list = DB::table('QPRecordDB.dbo.RecordCardCost')->where($where)->orderBy('mydate','desc')->paginate(10);
            foreach ($list as $key => &$value) {
                $week = array("日","一","二","三","四","五","六");
                $value->week = $week[date('w',strtotime($value->mydate))];
            }
            return view('admin.card.statistics_list',['list'=>$list,'end_time'=>$end_time,'start_time'=>$start_time]);
        }
    }

    //亲友圈查询
    public function friendsList(Request $request)
    {
        $nick_name = $request->input('nick_name');
        $show_id = $request->input('show_id');
        $name = $request->input('name');
        $game_id = $request->input('game_id');
        $excel = $request->get('excel');

        $where = [];
        !empty($nick_name) ? $where[] = ['pyq1.NickName','like','%'.$nick_name.'%'] : '';
        !empty($name) ? $where[] = ['pyq1.Name','like','%'.$name.'%'] : '';
        !empty($show_id) ? $where[] = ['pyq1.ShowID','=',$show_id] : '';
        !empty($game_id) ? $where[] = ['ai.GameID','=',$game_id] : '';
        if($excel){
            $cellData   = DB::table('QPAccountsDB.dbo.PyqInfo1 as pyq1')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','pyq1.UserID','=','ai.UserID')
                    ->leftJoin('QPAccountsDB.dbo.PyqInfo as pyq','pyq1.ShowID','=','pyq.ShowID')
                    ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gsi','pyq1.UserID','=','gsi.UserID')
                    ->select('pyq1.CreateDate','pyq1.NickName','pyq1.ShowID','pyq.CurCount as count','pyq1.Name','pyq1.GameName','ai.GameID','pyq1.WxAccount','pyq1.Mobile','pyq1.NumberLimit','gsi.InsureScore')
                    ->where($where)
                    ->where(['pyq1.IsDelete'=>0,'Status'=>3])
                    ->groupBy('pyq1.CreateDate','pyq1.ShowID','pyq1.NickName','pyq1.GameName','pyq1.WxAccount','pyq1.Mobile','pyq1.Name','pyq1.NumberLimit','ai.GameID','gsi.InsureScore','pyq.CurCount')
                    ->orderBy('pyq1.CreateDate','desc')
                    ->get();
            foreach ($cellData as $key => &$value) {
                $value->count = $value->count.'/'.$value->NumberLimit;
                unset($value->NumberLimit);
            }
            $title = ['成立时间','亲友圈昵称','亲友圈ID','成员数','推广员姓名','推广员戏昵称','推广员游戏ID','微信号','手机号','房卡库存'];
            $cellData = json_decode(json_encode($cellData),true);
            downloadExcel($cellData,$title,'亲友圈列表');
        }else{
            $list   = DB::table('QPAccountsDB.dbo.PyqInfo1 as pyq1')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','pyq1.UserID','=','ai.UserID')
                    ->leftJoin('QPAccountsDB.dbo.PyqInfo as pyq','pyq1.ShowID','=','pyq.ShowID')
                    ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gsi','pyq1.UserID','=','gsi.UserID')
                    ->select('pyq1.CreateDate','pyq1.NickName','pyq1.ShowID','pyq.ID','pyq.CurCount as count','pyq1.Name','pyq1.GameName','ai.GameID','pyq1.UserID','pyq1.WxAccount','pyq1.Mobile','pyq1.NumberLimit','gsi.InsureScore')
                    ->where($where)
                    ->where(['pyq1.IsDelete'=>0,'Status'=>3])
                    ->groupBy('pyq1.CreateDate','pyq1.ShowID','pyq.ID','pyq1.UserID','pyq1.NickName','pyq1.GameName','pyq1.WxAccount','pyq1.Mobile','pyq1.Name','pyq1.NumberLimit','ai.GameID','gsi.InsureScore','pyq.CurCount')
                    ->orderBy('pyq1.CreateDate','desc')
                    ->paginate(10);

            return view('admin.card.friends_list',['list'=>$list,'nick_name'=>$nick_name,'show_id'=>$show_id,'name'=>$name,'game_id'=>$game_id,]);
        }
    }

    //成员信息
    public function membersList(Request $request,$id)
    {
        $list = DB::table('QPAccountsDB.dbo.PyqMemberInfo as pmi')
            ->leftJoin('QPAccountsDB.dbo.PyqInfo as pyq','pmi.ID','=','pyq.ID')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','pmi.MemberID','=','ai.UserID')
            ->select('ai.UserID','pyq.ShowID','pyq.NickName as PyqName','ai.GameID','ai.NickName','pmi.JoinDate')
            ->where('pmi.ID',$id)
            ->groupBy('ai.UserID','pyq.ShowID','pyq.NickName','ai.GameID','ai.NickName','pmi.JoinDate')
            ->paginate(10);
        foreach ($list as $key => &$value) {
            $where = [];
            $value->UpdateTime = '';
            $value->UpdateTime = DB::table('QPRecordDB.dbo.YN_RecordPlayerNum')
                ->where('UserID',$value->UserID)
                ->where('GameType',8)
                ->orderBy('UpdateTime','desc')
                ->value('UpdateTime');
            $where[] = ['TabelMember','like',"%*".$value->UserID."*%"];
            $value->PlayCount = DB::table('QPAccountsDB.dbo.PyqRecordLog')
                ->where($where)
                ->count();
        }
        return view('admin.card.members_list',['list'=>$list,'id'=>$id]);
    }

    //亲友圈数据
    public function friendsDetail(Request $request)
    {
        $start_time = $request->input('start_time') ?? date('Y-m-d');
        $end_time = $request->input('end_time') ?? date('Y-m-d');
        $where_end_time = date('Y-m-d',strtotime("$end_time +1 day"));
        $kind_id = $request->input('kind_id') ?? 0;
        $show_id = $request->input('show_id') ?? 0;
        $game_id = $request->input('game_id') ?? 0;
        $excel = $request->get('excel');
        if($excel){
            $list = DB::select("
                SET NOCOUNT ON
                use QPAccountsDB
                DECLARE @BeginTime datetime
                DECLARE @EndTime datetime
                DECLARE @KindId INT
                DECLARE @QuanId INT
                DECLARE @GameId INT
                set @BeginTime = '$start_time'
                set @EndTime = '$where_end_time'
                set @KindId = $kind_id
                set @GameId = $game_id
                set @QuanId = $show_id
                create table #infos(date varchar(10),ShowID int,ID int,NickName nvarchar(32),CurCount int,countU int,roomcard int,Jushu int)
                DECLARE @temptime  datetime
                SET @temptime =  convert(varchar(10), @BeginTime, 120)+' 00:00:00'
                while @temptime < @EndTime
                begin
                    INSERT into #infos(date,ShowID,ID,NickName,CurCount,countU,roomcard,Jushu)
                    select convert(varchar(10), @temptime, 120) as date, a.ShowID,a.ID, a.NickName,a.CurCount,c.countU,sum(e.RoomCard) as roomcard,
                    sum(e.LowerStandard+e.UpStandard) as Jushu
                    from QPAccountsDB.dbo.PyqInfo a,(select b.PyqID,COUNT(b.UserID) as countU from (
                    select PyqID,UserID,SUM(PlayNum) as AllPlayNum from QPAccountsDB.dbo.YN_PyqRecordPlayLogSum
                    where UpdateTime >=  @temptime and UpdateTime <= DATEADD(DD,1,@temptime)
                    group by PyqID,UserID) b
                    where AllPlayNum >= 0
                    group by b.PyqID) c,YN_PyqRecordLogSum e
                    where a.ID = c.PyqID
                    and a.ID = e.PyqID
                    and e.UpdateTime >= @temptime and e.UpdateTime <= DATEADD(DD,1,@temptime)
                    group by a.ShowID,a.NickName,a.CurCount,c.countU,a.ID
                    SET @temptime = DATEADD(DD,1,@temptime)
                end
                if @KindId >0
                begin
                    DELETE  FROM #infos where ID not in(SELECT PyqID FROM QPAccountsDB.dbo.YN_PyqRankList where @KindId = KindID)
                end
                if @GameId > 0
                begin
                    DECLARE @UserId INT
                    SELECT @UserId =UserID FROM QPAccountsDB.dbo.AccountsInfo where GameID = @GameId
                    print(@UserId)
                    DELETE  FROM #infos where ID in(SELECT ID FROM PyqInfo where UserID != @UserId )
                end
                if @QuanId > 0
                begin
                    DELETE  FROM #infos where ShowID not in(@QuanId)
                end
                select date,ShowID,NickName,countU,Jushu,roomcard from #infos
                drop table #infos");
            $title = ['时间','亲友圈ID','亲友圈昵称','活跃用户','游戏局数','房卡消耗'];
            $list = json_decode(json_encode($list),true);
            downloadExcel($list,$title,'亲友圈列表');
        }else{
            $list = DB::select("
                SET NOCOUNT ON
                use QPAccountsDB
                DECLARE @BeginTime datetime
                DECLARE @EndTime datetime
                DECLARE @KindId INT
                DECLARE @QuanId INT
                DECLARE @GameId INT
                set @BeginTime = '$start_time'
                set @EndTime = '$where_end_time'
                set @KindId = $kind_id
                set @GameId = $game_id
                set @QuanId = $show_id
                create table #infos(date varchar(10),ShowID int,ID int,NickName nvarchar(32),CurCount int,countU int,roomcard int,Jushu int)
                DECLARE @temptime  datetime
                SET @temptime =  convert(varchar(10), @BeginTime, 120)+' 00:00:00'
                while @temptime < @EndTime
                begin
                    INSERT into #infos(date,ShowID,ID,NickName,CurCount,countU,roomcard,Jushu)
                    select convert(varchar(10), @temptime, 120) as date, a.ShowID,a.ID, a.NickName,a.CurCount,c.countU,sum(e.RoomCard) as roomcard,
                    sum(e.LowerStandard+e.UpStandard) as Jushu
                    from QPAccountsDB.dbo.PyqInfo a,(select b.PyqID,COUNT(b.UserID) as countU from (
                    select PyqID,UserID,SUM(PlayNum) as AllPlayNum from QPAccountsDB.dbo.YN_PyqRecordPlayLogSum
                    where UpdateTime >=  @temptime and UpdateTime <= DATEADD(DD,1,@temptime)
                    group by PyqID,UserID) b
                    where AllPlayNum >= 0
                    group by b.PyqID) c,YN_PyqRecordLogSum e
                    where a.ID = c.PyqID
                    and a.ID = e.PyqID
                    and e.UpdateTime >= @temptime and e.UpdateTime <= DATEADD(DD,1,@temptime)
                    group by a.ShowID,a.NickName,a.CurCount,c.countU,a.ID
                    SET @temptime = DATEADD(DD,1,@temptime)
                end
                if @KindId >0
                begin
                    DELETE  FROM #infos where ID not in(SELECT PyqID FROM QPAccountsDB.dbo.YN_PyqRankList where @KindId = KindID)
                end
                if @GameId > 0
                begin
                    DECLARE @UserId INT
                    SELECT @UserId =UserID FROM QPAccountsDB.dbo.AccountsInfo where GameID = @GameId
                    print(@UserId)
                    DELETE  FROM #infos where ID in(SELECT ID FROM PyqInfo where UserID != @UserId )
                end
                if @QuanId > 0
                begin
                    DELETE  FROM #infos where ShowID not in(@QuanId)
                end
                select * from #infos
                drop table #infos");
            $game_name  = DB::table('QPPlatformDB.dbo.GameGameItem as ggi')
                    ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gri','ggi.GameID','=','gri.GameID')
                    ->where('gri.ServerType',8)
                    ->pluck('ggi.GameName','ggi.GameID');//单选框数据
            return view('admin.card.friends_detail',['list'=>$list,'show_id'=>$show_id,'kind_id'=>$kind_id,'game_id'=>$game_id,'game_name'=>$game_name,'start_time'=>$start_time,'end_time'=>$end_time]);
        }
    }
    //游戏记录查询
    public function gameRecordList(Request $request)
    {
        $record_id = $request->input('record_id');
        $kind_id = $request->input('kind_id');
        $game_id = $request->input('game_id');
        $end_time = $request->input('end_time');
        $start_time = $request->input('start_time');

        $end_time = !empty($end_time) ? $end_time: date('Y-m-d');
        $start_time = !empty($start_time) ? $start_time : date('Y-m-d',strtotime('-7 days'));
        // $where[] = ['pgr.Pyq','=',1];
        $where[] = ['prl.CreateDate','>=',$start_time];
        $where[] = ['prl.CreateDate','<=',date('Y-m-d',strtotime("$end_time +1day"))];

        !empty($record_id) ? $where[] = ['prl.RecordID','=',$record_id] : '';
        !empty($kind_id) ? $where[] = ['prl.KindID','=',$kind_id] : '';
        if(!empty($game_id)){
            $user_id = DB::table('QPAccountsDB.dbo.AccountsInfo')->where('GameID',$game_id)->value('UserID');
            $where[] = ['prl.TabelMember','like','%*'.$user_id.'*%'];
        }
        $game_name  = DB::table('QPPlatformDB.dbo.GameGameItem as ggi')
                    ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gri','ggi.GameID','=','gri.GameID')
                    ->where('gri.ServerType',8)
                    ->pluck('ggi.GameName','ggi.GameID');//单选框数据
        $list = DB::table('QPAccountsDB.dbo.PyqRecordLog as prl')
                ->leftJoin('QPTreasureDB.dbo.PrivateGameRecordUserRecordID as pgr','prl.RecordID','=','pgr.RecordID')
                ->leftJoin('QPPlatformDB.dbo.GameKindItem as ki','prl.KindID','=','ki.KindID')
                ->select('prl.RecordID','prl.CreateDate','ki.KindName','prl.MemberScore','prl.TabelMember','prl.NickNames')
                ->where($where)
                ->groupBy('prl.RecordID','prl.CreateDate','ki.KindName','prl.MemberScore','prl.TabelMember','prl.NickNames')
                ->orderBy('prl.CreateDate','desc')
                ->paginate(10);
        foreach ($list as $key => &$value) {
            $names = [];
            $count = substr($value->NickNames,0,2);
            $name = substr(mb_convert_encoding($value->NickNames, 'gbk', 'utf-8'),2);
            for ($i=0; $i < $count ; $i++) {
                $lenth = substr($name,0,3);
                $nick_name = substr($name,3,(int)$lenth);
                $name = str_replace($lenth.$nick_name,'', $name);
                $names[] = mb_convert_encoding($nick_name, 'utf-8', 'gbk');
            }
            $value->score = explode('*', trim($value->MemberScore,'*'));
            $user_ids = explode('*', trim($value->TabelMember,'*'));
            $game_ids = [];
            foreach ($user_ids as $k => $v) {
                $game_ids[] = DB::table('QPAccountsDB.dbo.AccountsInfo')->where('UserID',$v)->value('GameID');
            }
            $value->GameID = $game_ids;
            $value->name = $names;
        }
        return view('admin.card.game_record_list',['list'=>$list,'record_id'=>$record_id,'kind_id'=>$kind_id,'game_name'=>$game_name,'game_id'=>$game_id,'start_time'=>$start_time,'end_time'=>$end_time]);
    }

    //玩家输赢记录
    public function gameResultList(Request $request)
    {
        $excel = $request->input('excel');
        $game_id = $request->input('game_id');
        $kind_id = $request->input('kind_id');
        $sort = $request->input('sort');
        $start_time = $request->input('start_time');
        $end_time = $request->input('end_time');

        $end_time = !empty($end_time) ? $end_time: date('Y-m-d');
        $start_time = !empty($start_time) ? $start_time : date('Y-m-d',strtotime('-7 days'));
        $where[] = ['usd.PyqID','=',0];
        $where[] = ['usd.ServerType','=',8];
        $where[] = ['usd.UpdateTime','>=',$start_time];
        $where[] = ['usd.UpdateTime','<=',$end_time];
        !empty($game_id) ? $where[] = ['ai.GameID','=',$game_id] : '';
        !empty($kind_id) ? $where[] = ['usd.KindID','=',$kind_id] : '';
        !empty($sort) ? $order = 'usd.Score asc' : $order = 'usd.Score desc';

        $game_name  = DB::table('QPPlatformDB.dbo.GameGameItem as ggi')
                    ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gri','ggi.GameID','=','gri.GameID')
                    ->where('gri.ServerType',8)
                    ->pluck('ggi.GameName','ggi.GameID');//单选框数据
        if($excel){
            $list = DB::table('QPPlatformDB.dbo.YN_UserStockDay as usd')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','usd.UserID','=','ai.UserID')
                    ->leftJoin('QPPlatformDB.dbo.GameGameItem as ggi','usd.KindID','=','ggi.GameID')
                    ->select('usd.UpdateTime','ai.GameID','ai.NickName','ggi.GameName','usd.Score')
                    ->where($where)
                    ->orderByRaw($order)
                    ->get();
            $title = ['时间','用户游戏ID','昵称','游戏名称','输赢分数'];
            $cellData = json_decode(json_encode($list),true);
            downloadExcel($cellData,$title,'玩家输赢记录'.date('YmdHis'));
        }else{
            $list = DB::table('QPPlatformDB.dbo.YN_UserStockDay as usd')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','usd.UserID','=','ai.UserID')
                    ->leftJoin('QPPlatformDB.dbo.GameGameItem as ggi','usd.KindID','=','ggi.GameID')
                    ->select('usd.UpdateTime','ai.GameID','usd.UserID','ai.NickName','usd.KindID','ggi.GameName','usd.Score')
                    ->where($where)
                    ->orderByRaw($order)
                    ->groupBy('usd.UpdateTime','usd.UserID','ai.GameID','ai.NickName','usd.KindID','ggi.GameName','usd.Score')
                    ->paginate(10);
            foreach ($list as $key => &$value) {
                $where1 = [];
                $where1[] = ['TabelMember','like','%*'.$value->UserID.'*%'];
                $where1[] = ['KindID','=',$value->KindID];
                $value->count = DB::table('QPAccountsDB.dbo.PyqRecordLog')
                        ->selectRaw('count(*) as count')
                        ->where($where1)
                        ->whereRaw("DATEDIFF(DD,CreateDate,'$value->UpdateTime') = 0")
                        ->value('count');
            }
            return view('admin.card.game_result_list',['list'=>$list,'game_id'=>$game_id,'kind_id'=>$kind_id,'game_name'=>$game_name,'start_time'=>$start_time,'end_time'=>$end_time]);
        }
    }

    //游戏输赢总排行
    public function allScoreList(Request $request)
    {
        $excel = $request->input('excel');
        $start_time = $request->input('start_time');
        $end_time = $request->input('end_time');
        $where[] = ['usd.PyqID','=',0];
        $where[] = ['usd.ServerType','=',8];
        if(empty($start_time)){
            $start_time = date('Y-m-d',strtotime("$end_time -9day"));
        }
        $where[] = ['usd.UpdateTime','>=',$start_time];
        if(empty($end_time)){
            $end_time = date('Y-m-d');
        }
        $where[] = ['usd.UpdateTime','<=',$end_time];
        if($excel){
            $list = DB::table('QPPlatformDB.dbo.YN_UserStockDay as usd')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','usd.UserID','=','ai.UserID')
                    ->leftJoin('QPPlatformDB.dbo.GameGameItem as ggi','usd.KindID','=','ggi.GameID')
                    ->select('ai.GameID','ai.NickName','ggi.GameName',DB::Raw('sum(usd.Score) as AllScore'))
                    ->where($where)
                    ->groupBy('ai.GameID','ggi.GameName','ai.NickName')
                    ->orderBy(DB::Raw('sum(usd.Score)'),'desc')
                    ->get();
            $title = ['用户游戏ID','昵称','游戏名称','输赢分数'];
            $cellData = json_decode(json_encode($list),true);
            downloadExcel($cellData,$title,'玩家输赢记录'.date('YmdHis'));
        }else{
            $list = DB::table('QPPlatformDB.dbo.YN_UserStockDay as usd')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','usd.UserID','=','ai.UserID')
                    ->leftJoin('QPPlatformDB.dbo.GameGameItem as ggi','usd.KindID','=','ggi.GameID')
                    ->select('ai.GameID','usd.UserID','ai.NickName','ggi.GameName',DB::Raw('sum(usd.Score) as AllScore'))
                    ->where($where)
                    ->groupBy('ai.GameID','usd.UserID','ggi.GameName','ai.NickName')
                    ->orderBy(DB::Raw('sum(usd.Score)'),'desc')
                    ->paginate(10);
            return view('admin.card.allscore_list',['list'=>$list,'start_time'=>$start_time,'end_time'=>$end_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');
        $where[] = ['GameType','=',8];
        if(empty($start_time)){
            $cost_where[] = ['RecordDate','>=',date('Y-m-d',strtotime('-1 week'))];
            $where[] = ['UpdateTime','>=',date('Y-m-d',strtotime('-1 week'))];
            $start_time = date('Y-m-d',strtotime('-1 week'));
        }else{
            $where[] = ['UpdateTime','>=',$start_time];
        }
        if(empty($end_time)){
            $cost_where[] = ['RecordDate','<=',date('Y-m-d',strtotime('+1 days'))];
            $where[] = ['UpdateTime','<=',date('Y-m-d',strtotime('+1 days'))];
            $end_time = date('Y-m-d');
        }else{
            $where[] = ['UpdateTime','<=',date('Y-m-d',strtotime("$end_time +1 days"))];
        }
        if($kind_id){
            $where[] = ['rp.KindID','=',$kind_id];
        }
        $kind_ids = DB::table('QPRecordDB.dbo.YN_RecordPlayerNum as rp')
            ->leftJoin('QPPlatformDB.dbo.GameGameItem as g','rp.KindID','=','g.GameID')
            ->distinct()
            ->select('KindID','GameName')
            ->where('GameType',8)
            ->get();
        if($excel){
            $list = DB::table('QPRecordDB.dbo.YN_RecordPlayerNum as rp')
                ->leftJoin('QPPlatformDB.dbo.GameGameItem as g','rp.KindID','=','g.GameID')
                ->selectRaw('CONVERT(varchar(100), UpdateTime, 23) as date, g.GameName, COUNT(*) as count, SUM(PlayCount) as PlayCount, SUM(Experience) as time')
                ->where($where)
                ->groupBy(DB::raw('CONVERT(varchar(100), UpdateTime, 23),rp.KindID, g.GameName'))
                ->orderBy(DB::raw('CONVERT(varchar(100), UpdateTime, 23)'),'desc')
                ->orderBy('PlayCount','desc')
                ->get();
            foreach ($list as $key => &$value) {
                $value->time = round($value->time/$value->count/60,0);
                $value->PlayCount = round($value->PlayCount/28,0);
            }
            $title = ['时间','游戏名称','参游人数','开桌数','平均在线时长'];
            $cellData = json_decode(json_encode($list),true);
            downloadExcel($cellData,$title,'玩家输赢记录'.date('YmdHis'));
        }else{
            $list = DB::table('QPRecordDB.dbo.YN_RecordPlayerNum as rp')
                ->leftJoin('QPPlatformDB.dbo.GameGameItem as g','rp.KindID','=','g.GameID')
                ->selectRaw('CONVERT(varchar(100), UpdateTime, 23) as date, g.GameName, COUNT(*) as count, SUM(PlayCount) as PlayCount, SUM(Experience) as time')
                ->where($where)
                ->groupBy(DB::raw('CONVERT(varchar(100), UpdateTime, 23),rp.KindID, g.GameName'))
                ->orderBy(DB::raw('CONVERT(varchar(100), UpdateTime, 23)'),'desc')
                ->orderBy('PlayCount','desc')
                ->paginate(10);

            // $cost_card = DB::table('QPRecordDB.dbo.RecordCardCostDetail')
            //     ->selectRaw('KindID,CONVERT(varchar(100), RecordDate, 23) as date,SUM(CostCard) as cost')
            //     ->where($cost_where)
            //     ->whereIn('KindID',$kind_ids)
            //     ->groupBy(DB::raw('CONVERT(varchar(100), RecordDate, 23), KindID'))
            //     ->orderBy(DB::raw('CONVERT(varchar(100), RecordDate, 23)'),'desc')
            //     ->get();
            // print_r($kind_ids);exit;
            return view('admin.card.game_log',['list'=>$list,'start_time'=>$start_time,'end_time'=>$end_time,'kind_id'=>$kind_id,'kind_ids'=>$kind_ids]);
        }
    }

    //批量转账页
    public function giveCardView(Request $request)
    {
        return view('admin.card.give');
    }

    //批量转账
    public function giveCard(Request $request)
    {
        if($request->has('text')){
            $text = json_decode($request->text,true);
            $time = Carbon::now();
            $admin = $request->session()->get('admin');
            foreach ($text as $key => $value) {
                if(!is_int($value[0]) || !is_int($value[1])){
                    return $this->json(500, "数据格式错误");
                }
                $record = [];
                $record['GameID'] = $value[0];
                $record['Amount'] = $value[1];
                $record['Reason'] = $value[2];
                $record['CreatedAt'] = $time;
                $record['Admin'] = $admin->nickname;
                $record_insert[] = $record;
                $user_id = DB::connection('write')->table('QPAccountsDB.dbo.AccountsInfo')->where('GameID',$value[0])->value('UserID');
                if(!$user_id){
                    return $this->json(500, "用户ID错误：$value[0]");
                }
                $mail = [
                    'MailType' => 2,
                    'MailStatus' => 1,
                    'UserID' => $user_id,
                    'CreateTime' => $time,
                    'TitleString' => $value[3],
                    'TextString' => $value[4],
                    'BonusString' => "10000,".$value[1],
                    'FromAgentID' => $admin->id,
                ];
                $mail_insert[] = $mail;
            }

            $result = DB::table('QPRecordDB.dbo.GiveCardRecord')
                ->insert($record_insert);

            $result = DB::table('QPAccountsDB.dbo.PrivateMail')->insert($mail_insert);
            if($result){
                return $this->json(200, "处理成功");
            }else{
                return $this->json(500, "提交失败");
            }
        }
        $list = [];
        $list = $this->addexcel($request);
        unset($list[0]);
        if(!$list){
            return view('admin.card.give');
        }
        $str = json_encode($list);
        return view('admin.card.add',['list'=>$list,'str'=>$str]);
    }

    //批量转账记录
    public function giveCardRecordView(Request $request)
    {
        $start_time = $request->input('start_time') ?? date('Y-m-d');
        $end_time = $request->input('end_time') ?? date('Y-m-d');
        $game_id = $request->input('game_id');
        $reason = $request->input('reason');
        $where[] = ['CreatedAt','>=',$start_time];
        $where[] = ['CreatedAt','<=',date('Y-m-d',strtotime("$end_time +1 days"))];
        if($game_id){
            $where[] = ['GameID','=',$game_id];
        }
        if($reason){
            $where[] = ['Reason','like','%'.$reason.'%'];
        }
        $list = DB::connection('write')->table('QPRecordDB.dbo.GiveCardRecord')
            ->where($where)
            ->orderBy('ID','desc')
            ->paginate(15);
        return view('admin.card.give_record',['list'=>$list,'start_time'=>$start_time,'end_time'=>$end_time,'game_id'=>$game_id,'reason'=>$reason]);
    }

}