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

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ChartController extends Controller
{
    public function dashboard(Request $request)
    {
        $start_date = '20210918'; //统计最早开始日期
        $from_date = date('Ymd',strtotime('-1 week')); //统计开始时间
        $to_date = date('Ymd'); //统计结束时间

        // 总充值金额
        $recharge_total = DB::connection('read')->table('agent.dbo.order')
            ->whereNotNull('pay_at')
            ->sum('amount');

        // 总提现金额
        $withdraw_total = DB::connection('read')->table('QPAccountsDB.dbo.OrderWithDraw')
            ->where('State', 2)
            ->sum('WithDraw');

        // 注册人数
        $register_user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->count();

        // 充值人数
        $recharge_user_count = DB::connection('read')->table('agent.dbo.order')
            ->whereNotNull('pay_at')
            ->count(DB::raw('DISTINCT(user_id)'));

        // 平台总输赢 从2021-09-18日开始
        $win_lose_total = DB::connection('read')->table('QPRecordDB.dbo.RecordUserDataStatisticsNew')
            ->where('DateID','>=','20210918')
            ->sum(DB::raw('WinScore+LostScore'));

        // 平台总流水
        $flow_total = DB::connection('read')->table('QPRecordDB.dbo.RecordUserDataStatisticsNew')
            ->where('DateID','>=','20210918')
            ->sum(DB::raw('WinScore+ABS(LostScore)+Revenue'));

        // 总税收
        $revenue_total = DB::connection('read')->table('QPRecordDB.dbo.RecordUserDataStatisticsNew')
            ->where('DateID','>=','20210918')
            ->sum('Revenue');

        // 总回收
        $recovery_total = DB::connection('read')->table('QPAccountsDB.dbo.OrderWithDraw')
            ->where('State', 4)
            ->sum('WithDraw');

        // 休眠用户
        $silent_user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())>7')
            ->where('IsAndroid', 0)
            ->count();

        // 流失用户
        $lost_user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->whereRaw('DATEDIFF(DAY, LastLogonDate, GETDATE())>30')
            ->where('IsAndroid', 0)
            ->count();

        //
        // 首充礼包相关数据
        //
        $first_recharge_count_array = DB::connection('read')->table('QPAccountsDB.dbo.FirstRechargeGifts')
            ->select('GiftsID,TotalCount')
            ->pluck('TotalCount','GiftsID')
            ->toArray();

        $first_recharge_amount_array = DB::connection('read')->table('agent.dbo.order')
            ->selectRaw('GiftsID,SUM(amount)')
            ->where('pay_status',1)
            ->groupBy('GiftsID')
            ->pluck('TotalCount','GiftsID')
            ->toArray();

        //
        // 周卡相关统计
        //
        $card_rows = DB::connection('read')->table('QPPlatformDB.dbo.MonthCard')
            ->whereIn('CardID',[4,5,6,7])
            ->get();

        //周卡累计未支付成功
        $unpaid_card_total = DB::connection('read')->table('agent.dbo.order')
            ->whereIn('GiftsID', [104, 105, 106, 107])
            ->where('o.pay_status', 0)
            ->sum('amount');

        //拉起的总单数
        $totalNum = DB::connection('read')->table('agent.dbo.order')
            ->whereIn('GiftsID', [104, 105, 106, 107])
            ->selectRaw('count(id) count,GiftsID')
            ->groupBy('GiftsID')
            ->pluck('count', 'GiftsID')
            ->toArray();

        //
        // 不同渠道用户数
        //

        // 安卓用户数
        $android_user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->where('LastLogonMobile','Android')
            ->where('RegisterDate', '>=', $start_date)
            ->count();

        // IOS用户数
        $ios_user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('IsAndroid', 0)
            ->where('LastLogonMobile','IOS')
            ->where('RegisterDate', '>=', $start_date)
            ->count();

        // 访客人数
        $guest_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('WebLogonTimes', 1)
            ->where('IsAndroid', 0)
            ->where('RegisterDate', '>=', $start_date)
            ->count();

        // 手机账号人数
        $bind_phone_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountPhone')
            ->where('BindDate', '>=', $start_date)
            ->count();

        // facebook用户数
        $facebook_user_count = DB::connection('read')->table('QPAccountsDB.dbo.AccountsInfo')
            ->where('RegisterDate', '>=', $start_date)
            ->where('IsAndroid', 0)
            ->where('WebLogonTimes', 0)
            ->count();

        // 谷歌试玩场会员人数
        $google_user_count = DB::connection('read')->table('QPTreasureDB.dbo.RecordUserInout as ri')
                ->join('QPPlatformDB.dbo.GameRoomInfo as gi', 'ri.ServerID', 'gi.ServerID')
                ->where('gi.ServerType', 2)
                ->where('GameID', 1005)
                ->where('ri.EnterTime', '>=', $start_date)
                ->count(DB::raw('distinct(ri.UserID)'));

        //
        // 不同渠道充值
        //

        // IOS总充值、充值用户数（暂无ios渠道）
        $ios_recharge_total = 0;
        $ios_recharge_user_count = 0;

        // 安卓总充值、充值用户数
        $android_order_array = DB::connection('read')->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', 'ai.UserID')
            ->where('o.pay_at', '>=', $start_date)
            ->selectRaw('COUNT(DISTINCT(o.user_id)) count, SUM(amount) amount')
            ->lock('with(nolock)')
            ->where('LastLogonMobile', 'Android')
            ->first()
            ->toArray();

        $android_recharge_total = $android_order_array['amount'];
        $android_recharge_user_count = $android_order_array['count'];

        // 谷歌渠道总充值、充值用户数
        $google_order_array = DB::connection('read')->table('agent.dbo.order as o')
            ->join('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', 'ai.UserID')
            ->where('Channel', 100)
            ->lock('with(nolock)')
            ->where('o.pay_at', '>=', $start_date)
            ->selectRaw('COUNT(DISTINCT(o.user_id)) count, SUM(amount) amount')
            ->first();

        $google_recharge_total = $google_order_array['amount'];
        $google_recharge_user_count = $google_order_array['count'];


        return view('admin.chart.dashboard',[
            'recharge_total' => $recharge_total,    //总充值
            'withdraw_total' => $withdraw_total,    //总提现
            'register_user_count' => $register_user_count, //注册用户数
            'recharge_user_count' => $recharge_user_count, //充值用户数
            'flow_total' => $flow_total,  //平台总流水
            'win_lose_total' => $win_lose_total,    //平台总输赢
            'revenue_total' => $revenue_total,   //总税收
            'recovery_total' => $recovery_total,  //总回收
        ]);
    }
}