merchant_admin/app/model/GameDailyModel.php

<?php
declare (strict_types = 1);

namespace app\model;

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

/**
 * 游戏每日数据统计模型 - 基于tp_game_bet_order表实时统计
 */
class GameDailyModel extends Model
{
    // 连接到fortue_tiger数据库
    protected $connection = 'fortue_tiger';
    
    /**
     * 获取游戏每日数据列表
     */
    public static function getGameDailyList($filters = [], $page = 1, $limit = 10)
    {
        $query = Db::connect('fortue_tiger')->name('game_bet_order');
        
        // 应用过滤条件
        if (!empty($filters['merchant_id'])) {
            $query->where('app_id', $filters['merchant_id']);
        }
        
        // 日期范围
        $startDate = $filters['start_date'] ?? date('Y-m-d', strtotime('-7 days'));
        $endDate = $filters['end_date'] ?? date('Y-m-d');
        $startTime = strtotime($startDate . ' 00:00:00');
        $endTime = strtotime($endDate . ' 23:59:59');
        
        $query->where('create_time', '>=', $startTime)
              ->where('create_time', '<=', $endTime)
              ->where('status', 1); // 只统计有效订单
        
        // 游戏ID或名称过滤
        if (!empty($filters['game_id'])) {
            $query->where('game_id', $filters['game_id']);
        }
        
        if (!empty($filters['game_name'])) {
            // 如果有游戏名称表，可以关联查询
            $query->where('game_id', 'like', '%' . $filters['game_name'] . '%');
        }
        
        // 按游戏和日期分组统计
        $sql = "SELECT 
                    DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y-%m-%d') as date,
                    game_id,
                    COUNT(DISTINCT uname) as bet_users,
                    COUNT(id) as bet_count,
                    SUM(bet) as bet_amount,
                    SUM(amount) as game_profit,
                    SUM(total_win_amount) as total_win,
                    COUNT(DISTINCT CASE WHEN action_type = 5 THEN id END) as free_game_count
                FROM tp_game_bet_order
                WHERE app_id = ? 
                    AND create_time >= ? 
                    AND create_time <= ?
                    AND status = 1";
        
        $params = [$filters['merchant_id'] ?? 0, $startTime, $endTime];
        
        if (!empty($filters['game_id'])) {
            $sql .= " AND game_id = ?";
            $params[] = $filters['game_id'];
        }
        
        $sql .= " GROUP BY date, game_id
                  ORDER BY date DESC, game_id ASC";
        
        // 获取总数（用于分页）
        $countSql = "SELECT COUNT(DISTINCT CONCAT(DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y-%m-%d'), '-', game_id)) as total
                     FROM tp_game_bet_order
                     WHERE app_id = ? 
                         AND create_time >= ? 
                         AND create_time <= ?
                         AND status = 1";
        
        $countParams = [$filters['merchant_id'] ?? 0, $startTime, $endTime];
        if (!empty($filters['game_id'])) {
            $countSql .= " AND game_id = ?";
            $countParams[] = $filters['game_id'];
        }
        
        $total = Db::connect('fortue_tiger')->query($countSql, $countParams)[0]['total'] ?? 0;
        
        // 添加分页
        $sql .= " LIMIT " . (($page - 1) * $limit) . ", " . $limit;
        
        $list = Db::connect('fortue_tiger')->query($sql, $params);
        
        // 处理每条记录
        foreach ($list as &$item) {
            // 计算RTP
            $item['game_rtp'] = 0;
            if ($item['bet_amount'] > 0) {
                $item['game_rtp'] = round(($item['total_win'] / $item['bet_amount']) * 100, 2);
            }
            
            // 获取该游戏该天的注册用户（新用户首次玩该游戏）
            $dayStart = strtotime($item['date'] . ' 00:00:00');
            $dayEnd = strtotime($item['date'] . ' 23:59:59');
            
            $registerUsers = Db::connect('fortue_tiger')
                ->name('game_bet_order')
                ->alias('gbo')
                ->join('tp_merchants_user mu', 'gbo.uname = mu.uname', 'INNER')
                ->where('gbo.app_id', $filters['merchant_id'] ?? 0)
                ->where('gbo.game_id', $item['game_id'])
                ->where('gbo.create_time', '>=', $dayStart)
                ->where('gbo.create_time', '<=', $dayEnd)
                ->where('mu.create_time', '>=', $dayStart)
                ->where('mu.create_time', '<=', $dayEnd)
                ->count('DISTINCT gbo.uname');
            
            // 获取登录用户数
            $loginUsers = Db::connect('fortue_tiger')
                ->name('game_bet_order')
                ->alias('gbo')
                ->join('tp_merchants_user mu', 'gbo.uname = mu.uname', 'INNER')
                ->where('gbo.app_id', $filters['merchant_id'] ?? 0)
                ->where('gbo.game_id', $item['game_id'])
                ->where('gbo.create_time', '>=', $dayStart)
                ->where('gbo.create_time', '<=', $dayEnd)
                ->where('mu.login_time', '>=', $dayStart)
                ->where('mu.login_time', '<=', $dayEnd)
                ->count('DISTINCT gbo.uname');
            
            // 计算抽水额度
            $item['commission_amount'] = ($item['bet_amount'] - $item['total_win']) / 100;
            
            // 格式化金额字段（转换为元）
            $item['game_profit'] = abs($item['game_profit']) / 100;
            $item['bet_amount'] = $item['bet_amount'] / 100;
            $item['total_win'] = $item['total_win'] / 100;
            
            // 添加其他字段
            $item['game_name'] = '游戏' . $item['game_id']; // 如果有游戏名称表可以关联查询
            $item['register_users'] = $registerUsers;
            $item['login_users'] = $loginUsers;
            
            // 移除不需要的字段
            unset($item['total_win']);
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取游戏汇总统计
     */
    public static function getGameSummary($merchantId, $startDate, $endDate)
    {
        $startTime = strtotime($startDate . ' 00:00:00');
        $endTime = strtotime($endDate . ' 23:59:59');
        
        $sql = "SELECT 
                    game_id,
                    COUNT(DISTINCT uname) as total_bet_users,
                    COUNT(id) as total_bet_count,
                    SUM(bet) as total_bet_amount,
                    SUM(amount) as total_game_profit,
                    SUM(total_win_amount) as total_win_amount,
                    COUNT(DISTINCT CASE WHEN action_type = 5 THEN id END) as total_free_games,
                    COUNT(DISTINCT DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y-%m-%d')) as active_days
                FROM tp_game_bet_order
                WHERE app_id = ? 
                    AND create_time >= ? 
                    AND create_time <= ?
                    AND status = 1
                GROUP BY game_id
                ORDER BY total_bet_amount DESC";
        
        $list = Db::connect('fortue_tiger')->query($sql, [$merchantId, $startTime, $endTime]);
        
        foreach ($list as &$item) {
            // 计算平均RTP
            $item['avg_rtp'] = 0;
            if ($item['total_bet_amount'] > 0) {
                $item['avg_rtp'] = round(($item['total_win_amount'] / $item['total_bet_amount']) * 100, 2);
            }
            
            // 计算总抽水
            $item['total_commission'] = ($item['total_bet_amount'] - $item['total_win_amount']) / 100;
            
            // 格式化金额字段
            $item['total_game_profit'] = abs($item['total_game_profit']) / 100;
            $item['total_bet_amount'] = $item['total_bet_amount'] / 100;
            $item['total_win_amount'] = $item['total_win_amount'] / 100;
            
            // 添加游戏名称
            $item['game_name'] = '游戏' . $item['game_id'];
            
            // 获取该游戏期间的注册和登录用户
            $item['total_register_users'] = Db::connect('fortue_tiger')
                ->name('game_bet_order')
                ->alias('gbo')
                ->join('tp_merchants_user mu', 'gbo.uname = mu.uname', 'INNER')
                ->where('gbo.app_id', $merchantId)
                ->where('gbo.game_id', $item['game_id'])
                ->where('gbo.create_time', '>=', $startTime)
                ->where('gbo.create_time', '<=', $endTime)
                ->where('mu.create_time', '>=', $startTime)
                ->where('mu.create_time', '<=', $endTime)
                ->count('DISTINCT gbo.uname');
            
            $item['total_login_users'] = Db::connect('fortue_tiger')
                ->name('game_bet_order')
                ->alias('gbo')
                ->join('tp_merchants_user mu', 'gbo.uname = mu.uname', 'INNER')
                ->where('gbo.app_id', $merchantId)
                ->where('gbo.game_id', $item['game_id'])
                ->where('gbo.create_time', '>=', $startTime)
                ->where('gbo.create_time', '<=', $endTime)
                ->where('mu.login_time', '>=', $startTime)
                ->where('mu.login_time', '<=', $endTime)
                ->count('DISTINCT gbo.uname');
        }
        
        return $list;
    }
    
    /**
     * 获取所有游戏列表
     */
    public static function getGameList($merchantId)
    {
        return Db::connect('fortue_tiger')
            ->name('game_bet_order')
            ->where('app_id', $merchantId)
            ->group('game_id')
            ->field('game_id, COUNT(id) as play_count')
            ->order('play_count', 'desc')
            ->select();
    }
}