merchant_admin/app/model/GameStatisModel.php

<?php
declare (strict_types = 1);

namespace app\model;

use think\Model;
use think\facade\Db;

/**
 * 游戏数据统计
 */
class GameStatisModel extends Model
{
    protected $name = 'game_bet_game';

    // 连接到fortue_tiger数据库
    protected $connection = 'fortue_tiger';
    
    /**
     * 获取游戏每日数据列表
     */
    public static function getGameDailyList($merchantId, $page = 1, $limit = 20, $filters = [])
    {
        $where = [
            ['app_id', '=', $merchantId]
        ];
        
        // 时间筛选
        if (!empty($filters['start_time'])) {
            $startTime = strtotime($filters['start_time']);
            $where[] = ['create_time', '>=', $startTime];
        }
        
        if (!empty($filters['end_time'])) {
            $endTime = strtotime($filters['end_time']);
            $where[] = ['create_time', '<=', $endTime];
        }

        if (!empty($filters['game_id'])) {
            $gameId = is_array($filters['game_id']) ? $filters['game_id'] : explode(',', $filters['game_id']);
            $where[] = ['game_id', 'in', $gameId];
        }

        // 使用子查询获取每个用户的统计数据
        $subQuery = self::field([
                "FROM_UNIXTIME(create_time, '%Y-%m-%d') as date", // 日期
                'game_id',  //游戏id
                'AVG(rtp) AS rtp', // 平均RTP
                'COUNT(DISTINCT uname) as bet_users',  // 投注用户数
                'COUNT(id) as bet_count',  // 注单数
                'SUM(bet) as bet_amount',  // 注单金额（下注分数）
                'SUM(total_win_amount) as game_profit',  // 游戏输赢（金额变化）
        ])
        ->where($where)
        ->group('date, game_id')
        ->buildSql();
        
        // 获取总数
        $total = self::table($subQuery . ' t')->count();
        
        // 获取排行榜数据
        $list = self::table($subQuery . ' t')
            ->order('date', 'desc')
            ->page($page, $limit)
            ->select()
            ->toArray();        

        // 获取登录用户、注册用户    
        $merchantsUserStatic = MerchantsUserModel::getPlayerStatistics($merchantId, $filters);
        
        foreach ($list as &$row)
        {
            $row['login_users'] = $merchantsUserStatic[$row['date']]['login_users'] ?? 0;
            $row['register_users'] = $merchantsUserStatic[$row['date']]['register_users'] ?? 0;
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取游戏输赢汇总数据
     */
    public static function getGameSummary($merchantId, $page = 1, $limit = 20, $filters = [])
    {
        $where = [
            ['app_id', '=', $merchantId],
            ['action_type', '=', 1] // 只统计下注记录
        ];
        
        // 时间筛选
        $startTime = !empty($filters['start_date']) ? strtotime($filters['start_date']) : strtotime(date('Y-m-d'));
        $endTime = !empty($filters['end_date']) ? strtotime($filters['end_date'] . ' 23:59:59') : strtotime(date('Y-m-d 23:59:59'));
        
        $where[] = ['create_time', '>=', $startTime];
        $where[] = ['create_time', '<=', $endTime];
        
        // 游戏ID筛选
        if (!empty($filters['game_id'])) {
            if (strpos($filters['game_id'], 'All') === false) {
                $gameId = is_array($filters['game_id']) ? $filters['game_id'] : explode(',', $filters['game_id']);
                $where[] = ['game_id', 'in', $gameId];
            }
        }
        
        // 统计当前时间段的数据
        $subQuery = self::field([
            'game_id',
            'COUNT(*) as bet_count', // 注单数
            'COUNT(DISTINCT user_id) as user_count', // 投注用户数
            'SUM(bet) as total_bet', // 总投注
            'SUM(total_win_amount) as total_win', // 总输赢
            'SUM(is_buy_game) as buy_game_amount', // 购买免费游戏金额
            'SUM(winning_score) as total_score', // 总赢奖
        ])
        ->where($where)
        ->group('game_id')
        ->buildSql();
        
        // 获取总数
        $total = self::table($subQuery . ' t')->count();
        
        // 获取分页数据
        $list = self::table($subQuery . ' t')
            ->order('total_bet', 'desc')
            ->page($page, $limit)
            ->select()
            ->toArray();
        
        // 获取游戏历史rtp
        $historyData = self::table('tp_game_rtp_win_bet')
        ->where([
            ['app_id', '=', $merchantId],
        ])
        ->field([
            'game_id',
            'SUM(bet_amount) as total_bet',
            'SUM(winning_score) as total_score',
        ])
        ->group('game_id')
        ->select()
        ->toArray();

        // 获取统计数据
        $summaryData = self::table('tp_game_rtp_win_bet')
        ->where([
            ['app_id', '=', $merchantId],
        ])
        ->field([
            'SUM(bet_amount) as total_bet',
            'SUM(bet_count) as total_bet_count',
            'SUM(total_win_amount) as total_win',
            'SUM(winning_score) as total_score',
            'COUNT(DISTINCT user_id) as total_user_count'
        ])
        ->find()
        ->toArray();
                    
        return [
            'list' => $list,
            'total' => $total,
            'history' => $historyData ? array_column($historyData, null, 'game_id') : [],
            'summary' => $summaryData,
        ];
    }
    
    /**
     * 获取档位押注数据
     */
    public static function getBetLevelData($merchantId, $page = 1, $limit = 20, $filters = [])
    {
        $where = [
            ['app_id', '=', $merchantId],
            ['action_type', '=', 1] // 只统计下注记录
        ];
        
        // 时间筛选
        $startTime = !empty($filters['start_date']) ? strtotime($filters['start_date']) : strtotime(date('Y-m-d'));
        $endTime = !empty($filters['end_date']) ? strtotime($filters['end_date'] . ' 23:59:59') : strtotime(date('Y-m-d 23:59:59'));
        
        $where[] = ['create_time', '>=', $startTime];
        $where[] = ['create_time', '<=', $endTime];
        
        // 游戏ID筛选
        if (!empty($filters['game_id'])) {
            if (strpos($filters['game_id'], 'All') === false && $filters['game_id'] !== '全部') {
                $gameId = is_array($filters['game_id']) ? $filters['game_id'] : explode(',', $filters['game_id']);
                $where[] = ['game_id', 'in', $gameId];
            }
        }

        // 使用子查询获取每个用户的统计数据
        $subQuery = self::where($where)
            ->field([
                'game_id',
                'bet',
                'COUNT(*) as bet_count',
                'SUM(bet) as total_bet',
                'SUM(total_win_amount) as total_win',
                'SUM(winning_score) as total_score'
            ])
            ->group('game_id, bet')
            ->buildSql();
        
        // 获取总数
        $total = self::table($subQuery . ' t')->count();

        // 获取档位数据
        $levelList = self::table($subQuery . ' t')
            ->page($page, $limit)
            ->select()
            ->toArray();        
           
        // 获取总体统计数据（用于计算占比）
        $totalStats = self::where($where)
            ->field([
                'game_id',
                'SUM(bet) as total_bet_all',
                'COUNT(*) as total_count_all',
            ])
            ->group('game_id')
            ->find();
                                
        return [
            'list' => $levelList,
            'total_stats' => $totalStats,
            'total' => $total,
            'page' => $page,
            'limit' => $limit            
        ];
    }
}