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

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Http\helper\NumConfig;
use App\Notification\TelegramBot;
use DOMDocument;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;
use App\Models\AccountsInfo;
use App\Models\Order;
use App;
use App\Util;
use Illuminate\Support\Facades\Redis;

class LiveDataController extends Controller
{
    private $gameInfoKey="livedata_gameinfo";
    //游戏概括
    public function gameInfo(Request $request)
    {
        $adminChannels = session('admin_channels');
        $Channels = session('all_channels');
        $admin = session('admin');

        // 申请人数，笔数，金额
        $applyUserCount = DB::connection('write')->table('QPAccountsDB.dbo.OrderWithDraw as ow')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'ow.UserID', 'ai.UserID')
            ->where('ow.State', '=', 1);
        if(count($adminChannels)<5)$applyUserCount=$applyUserCount->whereIn('ai.Channel', $adminChannels);
        $applyUserCount=$applyUserCount->selectRaw('count(distinct(ow.UserID)) userCount,(sum(ow.WithDraw)+sum(ow.ServiceFee)) WithDraw,count(ow.RecordID) count')
            ->lock('with(nolock)')->first();

        if (isset($applyUserCount->WithDraw)) {
            $applyUserCount->WithDraw = number_float($applyUserCount->WithDraw / NumConfig::NUM_VALUE);
        }
//        𝚌𝚊𝚛𝚕𝚊 𝚖𝚒𝚌𝚔
        //carla mick
        $message = DB::connection('write')->table('QPAccountsDB.dbo.Message as m')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai','m.GameID','=','ai.GameID')
            ->leftJoin('QPAccountsDB.dbo.Message as n','n.PID','=','m.ID')
            ->selectRaw('count(case when m.admin_id = 0 then m.admin_id else NULL end )  as count,
               count(nullif(m.admin_id, 0))  as replycount,
             count(case when m.admin_read > 0 then 1 else NULL end) as readcount');
        if(count($adminChannels)<5)$message=$message->whereIn('ai.Channel', $adminChannels);
        $message=$message->where('m.type',0)
            ->whereRaw('m.CreateAt>CONVERT(varchar(20),DATEADD(DAY,-1,GETDATE()),120)')->first();

        if(!in_array($admin->roles[0]->id,[1,12,2010,2011])){
            return view('admin.livedata.customerservice', ['applyUserCount' => $applyUserCount, 'message'=>$message,'Channels' => $Channels, 'adminChannels' => $adminChannels]);
        }



        $redis_k=$this->gameInfoKey;

        if (!Redis::exists($redis_k)) {
            $this->gameInfoReadFromDB("beigai");
        }
        $data =json_decode(Redis::get($redis_k),true);


        $ccs=DB::table('QPPlatformDB.dbo.ChannelPackageName')->select('Channel')->where('UnionSign',0)->pluck('Channel')->toArray();
        $ccs1=array_unique($ccs);

        $ccs=DB::table('QPPlatformDB.dbo.ChannelPackageName')->select('Channel')->where('UnionSign',1)->pluck('Channel')->toArray();
        $ccs2=array_unique($ccs);

        $ccsall=array_merge($ccs1,$ccs2);

        $dataGropSums=[991=>$ccsall];

        $Channels[991]="总计";
//        $Channels[992]="自营";
//        $Channels[993]="24680ARIES";






        if(isset($request->debug)){
            print_r($data);
            die;
        }
        $item=null;
        foreach ($data as $dd) {
            $item = $dd;
        }


        $ndata=[];
        foreach ($dataGropSums as $sum=>$sub){
            $sumdatas=[];
            foreach ($data as $ch=>$d){
                if(in_array($ch,$sub)){
                    $sumdatas[]=$d;
                }
            }
            $ndata=[];
            foreach ($item as $k=>$v){
                $arr=array_column($sumdatas, $k);
                $arr=array_map(function ($item){
                    if(!is_array($item)){
                        return str_replace(",","",$item);
                    }else{
                        return $item;
                    }
                },$arr);
                $ndata[$k]=array_sum($arr);
                if(in_array($k,["d0_day_pay", "yes_day_pay"])){
                    $ndata[$k]=[];
                    if(count($arr))foreach ($arr[0] as $kk=>$vv){
                        $arrarr=array_column($arr, $kk);
                        $ndata[$k][$kk]=array_sum($arrarr);
                    }
                }


            }

            $ndata['new_rhb']=$this->compute($ndata['today_register'] ?? 0, $ndata['yesterday_register_now'] ?? 0);
            $ndata['d0_day_pay_rhb']=$this->compute($ndata['d0_day_pay_count'] ?? 0, $ndata['d0_yday_pay_count_now'] ?? 0);
            $ndata['pay_sum_rhb']=$this->compute($ndata['today_pay_sum'] ?? 0, $ndata['yesterday_pay_sum_now'] ?? 0);
            $ndata['today_pay_sum_u'] = number_format($ndata['today_pay_sum']/env('USD_RATE',1));//充值金额
            $ndata['today_pay_sum']=number_format($ndata['today_pay_sum']);
            $data[$sum]=$ndata;

        }

//        $chanel_orders = [  121,101,110,125,105, 100,122,126,130,146,124,156,158,129,133,146,152,    106,123, 103,  104, 113,127, 131,141,144,148,149,150,151       ,128,132];
//        $chanel_orders=array_merge($dataGropSums[992],$dataGropSums[993]);

        $nadmins = [];
//        foreach ($chanel_orders as $channel) {
//            if (in_array($channel, $adminChannels)) {
//                array_push($nadmins, $channel);
//            }
//        }

        foreach ($adminChannels as $channel) {
            if (!in_array($channel, $nadmins)) {
                array_push($nadmins, $channel);
            }
        }

        $adminChannels = $nadmins;
        if(isset($request->debug)){
            print_r($data);
            print_r($nadmins);
            die;
        }
//        dd($data);

        return view('admin.livedata.game_info', ['alldata' => $data,'dataGropSums'=>$dataGropSums, 'applyUserCount' => $applyUserCount, 'message'=>$message,'Channels' => $Channels, 'adminChannels' => $adminChannels]);
    }
    public function d0_day_pay()
    {
        $pay = DB::connection('read')->table('agent.dbo.order as a')
            ->selectRaw('
            cast(sum(a.amount)/100 as int) as d0_pay_num ,
            count(DISTINCT a.user_id) as day_pay_count,
            a.Channel')
            ->join(DB::raw("QPAccountsDB.dbo.AccountsInfo as ai with(nolock)"),"ai.UserID","=","a.user_id")
            ->whereRaw('RegisterDate>=CONVERT(varchar(10),GETDATE(),120) and a.pay_at>CONVERT(varchar(10),GETDATE(),120)')
            ->lock('with(nolock)')
            ->groupBy('a.Channel')->get()->toArray();

        $withdraw = DB::connection('read')->table('QPAccountsDB.dbo.OrderWithDraw as ow')
            ->selectRaw('
             sum(cast(ow.WithDraw as int))/100  payout,
             sum(cast(ow.ServiceFee as int))/100 fee,
             count(ow.RecordID)               payout_count,
             count(distinct (ow.UserID))    payout_user_count,
             ai.Channel')
            ->join(DB::raw("QPAccountsDB.dbo.AccountsInfo as ai with(nolock)"),"ai.UserID","=","ow.UserID")
            ->whereRaw('RegisterDate>=CONVERT(varchar(10),GETDATE(),120) and ow.State=2')
            ->lock('with(nolock)')
            ->groupBy('ai.Channel')->get()->toArray();
        $result=[];
        foreach ($pay as $value){
            $value=(array)$value;
            $result[$value['Channel']]=$value;
        }
        foreach ($withdraw as $value){
            $value=(array)$value;
            if(isset($result[$value['Channel']])){
                $result[$value['Channel']]=array_merge($result[$value['Channel']],$value);
            }else{
                $result[$value['Channel']]=$value;
            }

        }
        foreach ($result as &$item){
            $item['payout']=$item['payout']??0;
            $item['fee']=$item['fee']??0;
            $item['payout_count']=$item['payout_count']??0;
            $item['payout_user_count']=$item['payout_user_count']??0;
            $item['d0_pay_num']=$item['d0_pay_num']??0;
            $item['day_pay_count']=$item['day_pay_count']??0;
            $item['ltv_now']=$item['d0_pay_num']-$item['payout'];

        }
        Redis::set("d0_day_pay_".date("Ymd"),json_encode($result));
        return $result;

    }
    public function yes_day_pay()
    {
        $yesday=Redis::get("d0_day_pay_".date("Ymd",time()-86400));
        if(isset($yesday)&&!empty($yesday)){
            return json_decode($yesday,true);
        }

        $pay = DB::connection('read')->table('agent.dbo.order as a')
            ->selectRaw('
            cast(sum(a.amount)/100 as int) as d0_pay_num ,
            count(DISTINCT a.user_id) as day_pay_count,
            a.Channel')
            ->leftJoin(DB::raw("QPAccountsDB.dbo.AccountsInfo as ai with(nolock)"),"ai.UserID","=","a.user_id")
            ->whereRaw('RegisterDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
            ->whereRaw('RegisterDate<CONVERT(varchar(10),GETDATE(),120)')
            ->whereRaw('a.pay_at>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
            ->whereRaw('a.pay_at<CONVERT(varchar(10),GETDATE(),120)')
            ->lock('with(nolock)')
            ->groupBy('a.Channel')->get()->toArray();

        $withdraw = DB::connection('read')->table('QPAccountsDB.dbo.OrderWithDraw as ow')
            ->selectRaw('
             sum(cast(ow.WithDraw as int))/100  payout,
             sum(cast(ow.ServiceFee as int))/100 fee,
             count(ow.RecordID)               payout_count,
             count(distinct (ow.UserID))    payout_user_count,
             ai.Channel')
            ->leftJoin(DB::raw("QPAccountsDB.dbo.AccountsInfo as ai with(nolock)"),"ai.UserID","=","ow.UserID")
            ->whereRaw('RegisterDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
            ->whereRaw('RegisterDate<CONVERT(varchar(10),GETDATE(),120)')

            ->whereRaw('ow.CreateDate>CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)')
            ->whereRaw('ow.CreateDate<CONVERT(varchar(10),GETDATE(),120)')

            ->lock('with(nolock)')
            ->groupBy('ai.Channel')->get()->toArray();
        $result=[];
        foreach ($pay as $value){
            $value=(array)$value;
            $result[$value['Channel']]=$value;
        }
        foreach ($withdraw as $value){
            $value=(array)$value;
            if(isset($result[$value['Channel']])){
                $result[$value['Channel']]=array_merge($result[$value['Channel']],$value);
            }else{
                $result[$value['Channel']]=$value;
            }

        }
        foreach ($result as &$item){
            $item['payout']=$item['payout']??0;
            $item['fee']=$item['fee']??0;
            $item['payout_count']=$item['payout_count']??0;
            $item['payout_user_count']=$item['payout_user_count']??0;
            $item['d0_pay_num']=$item['d0_pay_num']??0;
            $item['day_pay_count']=$item['day_pay_count']??0;
            $item['ltv_now']=$item['d0_pay_num']-$item['payout'];

        }
        Redis::set("d0_day_pay_".date("Ymd",time()-86400),json_encode($result));
        return $result;

    }
    public function gameInfoReadFromDB()
    {
        sleep(10);
        $Channels = DB::table('QPPlatformDB.dbo.ChannelPackageName')
            ->pluck('Remarks', 'Channel')->toArray();
        Redis::set("AllChannels",json_encode($Channels));
        Redis::expire("AllChannels", 3600);


        //今日新增
        $today_register = AccountsInfo::today_register();

        //昨日新增
        $yesterday_register = AccountsInfo::yesterday_register();

        $yesterday_register_now = AccountsInfo::yesterday_register_now();

        $today_gameplay=AccountsInfo::today_gameplay();
        $yesterday_gameplay=AccountsInfo::yesterday_gameplay();


        //日活
        $today_live = AccountsInfo::today_live();

        //昨日日活
        $yesterday_live = AccountsInfo::yesterday_live();

//        $yesterday_live_now = AccountsInfo::yesterday_live_now();
//        $new_rhb = $this->compute($today_register,$yesterday_register);

        //周活
        $week_login = AccountsInfo::week_login();

        //上周周活
        $yweek_login = AccountsInfo::yweek_login();


        //月活
        $month_login = AccountsInfo::month_login();

        //上月月活
        $ymonth_login = AccountsInfo::ymonth_login();


        //总注册用户
        $user_count = AccountsInfo::user_count_byChannel();

        //休眠用户
        $dormancy_user = AccountsInfo::dormancy_user_byChannel();
        //今日充值总额和人数
        $today_pay = Order::today_pay_byChannel();

        //昨日充值总额和人数
        $yesterday_pay = Order::yesterday_pay();
        $yesterday_pay_now = Order::yesterday_pay_now();
//        print_r($yesterday_pay);

        //今日新增付费人数
        $day_pay_count = Order::day_pay_count();

        //昨日新增付费人数
        $yday_pay_count = Order::yday_pay_count();
        $yday_pay_count_now = Order::yday_pay_count_now();

        //今日新增付费人数
        $d0_day_pay = $this->d0_day_pay();
        $yes_day_pay = $this->yes_day_pay();
        $d0_day_pay_count = [];
        foreach ($d0_day_pay as $Channel=>$item){
            $d0_day_pay_count[$Channel]=$item['day_pay_count'];
        }

        //昨日新增付费人数
        $d0_yday_pay_count = Order::d0_yday_pay_count();
        $d0_yday_pay_count_now = Order::d0_yday_pay_count_now();


        //总充值金额
        $pay_sum = Order::pay_sum()->pay_sum ?? 0;

        //总充值人数
        $pay_user_count = Order::pay_user_count();

        $monlist=DB::table('QPPlatformDB.dbo.ChannelPackageName')->selectRaw("PackageName as package,Channel as id,Remarks as name")->where('OnlineMon', 1)->get()->toArray();


        $googlePlayList = [
//
        ];
        foreach ($monlist as $value){
            $value=(array)$value;
            $value['url']='https://play.google.com/store/apps/details?id='.$value['package'];
            $value['status']=200;
            $googlePlayList[]=$value;
        }

        // 获取iOS App Store监控列表
        $ioslist = DB::connection('mysql')->table('webgame.WebChannelConfig')
            ->selectRaw("LightApk as iosurl, Channel as id, Remarks as name")
            ->whereNotNull('LightApk')
            ->where('LightApk', '!=', '')
            ->get()->toArray();

        foreach ($ioslist as $value){
            $value=(array)$value;
            // 只处理包含apple的URL
            if(!empty($value['iosurl']) && strpos($value['iosurl'], 'apple') !== false){
                $value['url'] = $value['iosurl'];
                $value['status'] = 200;
                $value['type'] = 'ios';
                $googlePlayList[]=$value;
            }
        }

        $old_list=[];
        if(Redis::exists("googleplay_list")){
            $old_list=json_decode(Redis::get("googleplay_list"),true);
        }
        foreach ($googlePlayList as &$item) {
            $url = $item['url'];

            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
//            curl_setopt($ch, CURLOPT_NOBODY, 1);
            $res = curl_exec($ch);
            $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);

            if ($code == 404) {
                $item['status'] = $code;
            }else{
                // 使用DOM解析器加载HTML
                $doc = new DOMDocument();
                @$doc->loadHTML($res); // 使用@符号抑制加载HTML时的警告

                // 获取<title>标签的内容
                $titles = $doc->getElementsByTagName("title");

                // 确保文档中有<title>标签
                if ($titles->length > 0) {
                    // 输出<title>标签的内容
                    $item['title']=$titles->item(0)->textContent;
                }
            }

            foreach($old_list as $olditem){
                if($item['url']==$olditem['url']){
                    if(isset($olditem['uptime']))$item['uptime']=$olditem['uptime'];
                    if(isset($olditem['downtime']))$item['downtime']=$olditem['downtime'];
                    if($item['status']!=$olditem['status']){
                        $telegram = new TelegramBot();
                        $env = env('APP_ENV');
                        $platform = isset($item['type']) && $item['type'] == 'ios' ? 'iOS App' : 'Google Play';
                        $telegram->sendMsg($env."在线包(".$platform.") ".$item['name']." 异常，立即通知广告".$item['status'].':'.$item['url']);
                        if(!isset($item['downtime']))$item['downtime']=time();
                    }else if($item['status']==200){
                        if(isset($item['title'])&&isset($olditem['title'])&&$item['title']!=$olditem['title']){
                            $telegram = new TelegramBot();
                            $env = env('APP_ENV');
                            $platform = isset($item['type']) && $item['type'] == 'ios' ? 'iOS App' : 'Google Play';
                            $telegram->sendMsg($env."上包通过(".$platform.") ".$item['name']." 名称更改--".$item['title']."，立即通知广告".$item['status'].':'.$item['url']);
                            if(!isset($item['uptime']))$item['uptime']=time();
                        }
                    }
                    break;
                }
            }
        }


        Redis::set("googleplay_list",json_encode($googlePlayList));


        $alldatas = [
            'today_register' => $today_register,//今日用户新增
            'yesterday_register' => $yesterday_register,//今日用户新增
            'yesterday_register_now' => $yesterday_register_now,//今日用户新增
            'today_gameplay' => $today_gameplay,//今日用户新增
            'yesterday_gameplay' => $yesterday_gameplay,//今日用户新增

            'today_live' => $today_live,//日活
            'week_login' => $week_login,//周活
            'month_login' => $month_login,//月活
            'user_count' => $user_count,//总注册用户
            'dormancy_user' => $dormancy_user,//休眠用户

            'day_pay_count' => $day_pay_count,//新增付费人数
            'yday_pay_count' => $yday_pay_count,//新增付费人数
            'd0_day_pay_count' => $d0_day_pay_count,//新增付费人数
            'd0_yday_pay_count_now' => $d0_yday_pay_count_now,//新增付费人数
            'd0_yday_pay_count' => $d0_yday_pay_count,//新增付费人数
        ];
        $data = [
            'plays' => $googlePlayList,
            'pay_sum' => number_format($pay_sum / 100),//总充值金额
            'pay_user_count' => $pay_user_count,//总充值人数

            'time' => date('Y-m-d'),
        ];


//        print_r($alldatas);die;
        foreach ($Channels as $channel => $name) {
//            if($channel==102)continue;
            if (!isset($data[$channel])) $data[$channel] = [];
            foreach ($alldatas as $key => &$arr) {
                $arr[$channel]=$arr[$channel]??0;
                $data[$channel][$key] = $arr[$channel];
            }
            $today_pay[$channel] = $today_pay[$channel] ?? (object)['today_pay_sum' => 0, 'today_pay_count' => 0];
            $yesterday_pay[$channel] = $yesterday_pay[$channel] ?? (object)['yesterday_pay_sum' => 0, 'yesterday_pay_count' => 0];
            $yesterday_pay_now[$channel] = $yesterday_pay_now[$channel] ?? (object)['yesterday_pay_sum' => 0, 'yesterday_pay_count' => 0];
            $today_gameplay[$channel]=$today_gameplay[$channel]??0;
            $yesterday_gameplay[$channel]=$yesterday_gameplay[$channel]??0;
            $today_register[$channel]=$today_register[$channel]??1;
            $yesterday_register[$channel]=$yesterday_register[$channel]??1;


            $today_play_rate = number_float((int)$today_gameplay[$channel] / $today_register[$channel]*100);
            $yesterday_play_rate = number_float((int)$yesterday_gameplay[$channel] / $yesterday_register[$channel]*100);
            $today_pay_sum = number_float((int)$today_pay[$channel]->today_pay_sum / 100);
            $yesterday_pay_sum = number_float((int)$yesterday_pay[$channel]->yesterday_pay_sum / 100);
            $yesterday_pay_sum_now = number_float((int)$yesterday_pay_now[$channel]->yesterday_pay_sum / 100);
            $today_pay_count = (int)$today_pay[$channel]->today_pay_count;
            $yesterday_pay_count = (int)$yesterday_pay[$channel]->yesterday_pay_count;
            $yesterday_pay_count_now = (int)$yesterday_pay_now[$channel]->yesterday_pay_count;
            $pay_sum_rhb = $this->compute($today_pay_sum, $yesterday_pay_sum_now);
            $pay_count_rhb = $this->compute($today_pay_count, $yesterday_pay_count_now);
            $data[$channel]['today_pay_sum'] = number_format($today_pay_sum);//充值金额
            $data[$channel]['today_pay_sum_u'] = number_format($today_pay_sum/env('USD_RATE',1));//充值金额
            $data[$channel]['yesterday_pay_sum'] = number_format($yesterday_pay_sum);//充值金额
            $data[$channel]['yesterday_pay_sum_now'] = number_format($yesterday_pay_sum_now);//充值金额
            $data[$channel]['pay_sum_rhb'] = $pay_sum_rhb;//充值金额环比
            $data[$channel]['today_pay_count'] = $today_pay_count;//付费人数
            $data[$channel]['yesterday_pay_count'] = $yesterday_pay_count;//付费人数
            $data[$channel]['pay_count_rhb'] = $pay_count_rhb;//付费人数日环比
            $data[$channel]['d0_day_pay'] = $d0_day_pay[$channel]??['pay'=>0,'payout'=>0,'payout_user_count'=>0,'d0_pay_num'=>0,'ltv_now'=>0,'fee'=>0];//d0ltv数据
            $data[$channel]['yes_day_pay'] = $yes_day_pay[$channel]??['pay'=>0,'payout'=>0,'payout_user_count'=>0,'d0_pay_num'=>0,'ltv_now'=>0,'fee'=>0];//d0ltv数据

            $new_rhb = $this->compute($today_register[$channel] ?? 0, $yesterday_register_now[$channel] ?? 0);

            $live_rhb = $this->compute($today_live[$channel] ?? 0, $yesterday_live[$channel] ?? 0);

            $play_rhb = $this->compute($today_play_rate, $yesterday_play_rate);

            //环比
            $week_rhb = $this->compute($week_login[$channel] ?? 0, $yweek_login[$channel] ?? 0);

            //环比
            $month_rhb = $this->compute($month_login[$channel] ?? 0, $ymonth_login[$channel] ?? 0);

//环比
            $d0_day_pay_rhb = $this->compute($d0_day_pay_count[$channel] ?? 0, $d0_yday_pay_count_now[$channel] ?? 0);

            //环比
            $day_pay_rhb = $this->compute($day_pay_count[$channel] ?? 0, $yday_pay_count_now[$channel] ?? 0);

            $data[$channel]['new_rhb'] = $new_rhb;//日环比
            $data[$channel]['today_play_rate'] = $today_play_rate;//日环比
            $data[$channel]['yesterday_play_rate'] = $yesterday_play_rate;//日环比
            $data[$channel]['play_rhb'] = $play_rhb;//日环比

            $data[$channel]['day_pay_rhb'] = $day_pay_rhb;//新增付费人数日环比
            $data[$channel]['d0_day_pay_rhb'] = $d0_day_pay_rhb;//新增付费人数日环比
            $data[$channel]['live_rhb'] = $live_rhb;//日活环比
            $data[$channel]['week_rhb'] = $week_rhb;//周环比
            $data[$channel]['month_rhb'] = $month_rhb;//月环比
        }
//        Cache::put('livedata_gameinfo', $data, 5);
        $redis_k=$this->gameInfoKey;
        Redis::set($redis_k,json_encode($data));
        Redis::expire($redis_k,600);
//        DB::connection('mysql')->table('log_gameinfos')->insert(['data'=>json_encode($data)]);
    }

    //金币总览
    public function goldInfo(Request $request)
    {
        if (Cache::has('livedata_goldinfo')) {
            $data = Cache::get('livedata_goldinfo');
            return view('admin.livedata.gold_info', ['data' => $data]);
        }
        //平台金币总额
        $data['platform_gold'] = DB::table('QPTreasureDB.dbo.GameScoreInfo as gsi')
            ->Join('QPAccountsDB.dbo.AccountsInfo as ai', 'gsi.UserID', '=', 'ai.UserID')
            ->selectRaw('Isnull(SUM(gsi.Score+gsi.BankScore),0) as platform_gold')
            ->where('ai.IsAndroid', 0)
            ->first()->platform_gold;
        //经销商库存
        $data['distributor_gold'] = DB::table('agent.dbo.agent')
            ->selectRaw('Isnull(SUM(score),0) as distributor_gold')
            ->where('distributor', 1)
            ->first()->distributor_gold;

        //计提总金额
        $data['revenue_gold'] = DB::table('QPAccountsDB.dbo.YN_Agent')
            ->selectRaw('Isnull(SUM(Revenue),0) as revenue_gold')
            ->where('Status', 0)
            ->first()->revenue_gold;

        $recode_game_date = DB::table('QPPlatformDB.dbo.YN_RecordGameDataDay')
            ->selectRaw('Isnull(SUM(Revenue),0) as today_revenue,Isnull(SUM(Waste),0) as robot_gold')
            ->whereRaw('RecordDate>CONVERT(varchar(10),GETDATE(),120)')
            ->first();

        //今日税收
        $data['today_revenue'] = $recode_game_date->today_revenue;

        //机器人输赢总额
        $data['robot_gold'] = $recode_game_date->robot_gold;


        //今日活跃资金量
        $data['today_active_gold'] = DB::table('QPTreasureDB.dbo.GameScoreInfo as gsi')
            ->Join('QPAccountsDB.dbo.AccountsInfo as ai', 'gsi.UserID', '=', 'ai.UserID')
            ->selectRaw('Isnull(SUM(gsi.Score+gsi.BankScore),0) as today_active_gold')
            ->whereRaw('gsi.LastLogonDate>CONVERT(varchar(10),GETDATE(),120) AND ai.IsAndroid=0')
            ->first()->today_active_gold;

        //今日回收总额
        $data['today_recovery_gold'] = $data['today_revenue'] + $data['robot_gold'];

        //今日流通量总额
        $data['today_circulate_gold'] = DB::table('QPTreasureDB.dbo.RecordDrawScore')
            ->selectRaw('Isnull(SUM(Score),0) as today_circulate_gold')
            // ->whereRaw('DATEDIFF(DAY,InsertTime,GETDATE())=0 AND Score>0')
            ->whereRaw('InsertTime>CONVERT(varchar(10),GETDATE(),120) AND Score>0')
            ->first()->today_circulate_gold;

        //新用户注册
        //$data['new_user_count'] = (AccountsInfo::today_register())*8000;
        $data['new_user_count'] = DB::table('QPRecordDB.dbo.PD_RecordScoreInfo')
            ->where('Reason', 33)
            ->whereDate('AtDate', date('Ymd'))
            ->selectRaw('Isnull(sum(Score),0) as ChangeScore')
            ->first()->ChangeScore;

        //每日签到
        $data['bonus_gold'] = DB::table('QPAccountsDB.dbo.YN_PlayerTaskLog')
            ->selectRaw('Isnull(SUM(BonusNumber),0) as bonus_gold')
            ->whereRaw('created_at>CONVERT(varchar(10),GETDATE(),120) AND BonusID=30000')
            ->first()->bonus_gold;

        //商城兑换
        $data['exchange_gold'] = DB::table('QPAccountsDB.dbo.ActivityShopRecord')
            ->selectRaw('COUNT(*)*60000 as exchange_gold')
            ->whereRaw('CreateDate>CONVERT(varchar(10),GETDATE(),120) AND GoodsID=4')
            ->first()->exchange_gold;

        //全民推广兑换
        $data['spread_gold'] = DB::table('QPAccountsDB.dbo.YN_Withdrawal')
            ->selectRaw('Isnull(SUM(Gold),0) as spread_gold')
            ->whereRaw('ApplyDate>CONVERT(varchar(10),GETDATE(),120)')
            ->first()->spread_gold;

        //充值发放
        $data['recharge_gold'] = DB::table('agent.dbo.order_goods as og')
            ->leftJoin('agent.dbo.order as o', 'og.order_id', '=', 'o.id')
            ->lock('with(nolock)')
            ->selectRaw('Isnull(SUM(og.score_quantity),0) as recharge_gold')
            ->whereRaw('o.pay_at>CONVERT(varchar(10),GETDATE(),120)')
            ->first()->recharge_gold;

        //低保破产赠送
        $data['succour_gold'] = DB::table('QPRecordDB.dbo.YN_LogProtect')
            ->selectRaw('Isnull(SUM(AddNums),0) as succour_gold')
            ->whereRaw('UpdateDate>CONVERT(varchar(10),GETDATE(),120)')
            ->first()->succour_gold;

        //任务获得
        $data['mission_gold'] = DB::table('QPRecordDB.dbo.LogProp')
            ->selectRaw('Isnull(SUM(PropNum),0) as mission_gold')
            ->whereRaw('RecordData>CONVERT(varchar(10),GETDATE(),120) AND Source=4 AND PropID=30000')
            ->first()->mission_gold;

        //周卡月卡每日发放
        $data['week_gold'] = DB::table('QPRecordDB.dbo.LogProp')
            ->selectRaw('ISNULL(SUM(PropNum),0) as week_gold')
            ->whereRaw("RecordData>CONVERT(varchar(10),GETDATE(),120) AND Source = 10")
            ->first()->week_gold;

        //每日分享
        $data['share_gold'] = DB::table('QPGameMatchDB.dbo.MatchShareRecord')
            ->selectRaw('Isnull(SUM(Gold),0) as share_gold')
            ->whereRaw('ShareDate>CONVERT(varchar(10),GETDATE(),120)')
            ->first()->share_gold;


        //今日总发放
        $data['gold_sum'] = $data['new_user_count'] + $data['bonus_gold'] + $data['exchange_gold'] + $data['spread_gold']
            + $data['recharge_gold'] + $data['succour_gold'] + $data['mission_gold'] + $data['week_gold'] + $data['share_gold'];
        foreach ($data as $key => &$value) {
            $value = number_format($value);
        }

        Cache::put('livedata_goldinfo', $data, 5);
        return view('admin.livedata.gold_info', ['data' => $data]);
    }

    //环比计算
    private function compute($numerator, $denominator = 0)
    {
        $denominator != 0 ? $result = round((($numerator - $denominator) / $denominator * 100), 2) . '%' : $result = round($numerator * 100, 2) . '%';
        return $result;
    }

    //当前玩家在线明细
    public function OnlinePlayer(Request $request)
    {
        $list = DB::connection('read')->table('QPTreasureDB.dbo.GameScoreLocker as gsl')
            ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'gsl.UserID', '=', 'ai.UserID')
            ->leftJoin('QPTreasureDB.dbo.GameScoreInfo as gsi', 'gsl.UserID', '=', 'gsi.UserID')
            ->leftJoin('QPPlatformDB.dbo.GameRoomInfo as gri', 'gsl.ServerID', '=', 'gri.ServerID')
            ->select('gsl.UserID', 'gsl.CollectDate', 'ai.GameID', 'ai.NickName', 'ai.LastLogonDate', 'gsi.Score', 'gsi.BankScore', 'gri.ServerName')
            ->paginate(10);
        foreach ($list as $key => &$value) {
            $value->Score = number_format($value->Score);
            $value->BankScore = number_format($value->BankScore);
        }
        return view('admin.livedata.online_player', ['list' => $list]);
    }

}