merchant_admin/app/model/GameBetGameModel.php

<?php
declare (strict_types = 1);

namespace app\model;

use app\common\CommonMerchantUserAndBalance;
use think\Model;

/**
 * 游戏记录模型 - 基于tp_game_bet_game表
 */
class GameBetGameModel extends Model
{
    protected $name = 'game_bet_game';
    protected $connection = 'fortue_tiger';
    protected $pk = 'id';
        
    /**
     * 获取游戏记录列表（参考apiAdminBetGameList方法）
     */
    public static function getBetGameList($appId, $page = 1, $limit = 20, $filters = [])
    {
        $wheres = [];
        $wheres[] = ['action_type', '=', 1]; // 只查询下注记录
        $wheres[] = ['app_id', '=', $appId];
        
        // 时间筛选
        if (!empty($filters['start_time'])) {
            $startTime = strtotime($filters['start_time']);
            $wheres[] = ['create_time', '>=', $startTime];
        }
        
        if (!empty($filters['end_time'])) {
            $endTime = strtotime($filters['end_time']);
            $wheres[] = ['create_time', '<=', $endTime];
        }
        
        // 游戏ID筛选
        if (!empty($filters['game_id'])) {
            $wheres[] = ['game_id', '=', $filters['game_id']];
        }
        
        // 牌局编号筛选
        if (!empty($filters['third_round_id'])) {
            $wheres[] = ['third_round_id', '=', $filters['third_round_id']];
        }
        
        // 用户ID筛选
        if (!empty($filters['player_id'])) {
            $wheres[] = ['user_id', '=', $filters['user_id']];
        }
        
        // 游戏玩法类型筛选
        if (!empty($filters['bet_game_play_type'])) {
            if ($filters['bet_game_play_type'] == 2) {
                $wheres[] = ['bet_game_play_type', 'in', [1, 2]];
            } else {
                $wheres[] = ['bet_game_play_type', '=', $filters['bet_game_play_type']];
            }
        }
        
        $query = self::where($wheres);
        
        // 统计总数
        $total = $query->count();
        
        // 获取列表数据（不包含result字段）
        $list = $query->withoutField('result')
            ->order('id', 'desc')
            ->page($page, $limit)
            ->select()
            ->toArray();
        
        if (empty($list)) {
            return [
                'list' => [],
                'total' => $total,
                'page' => $page,
                'limit' => $limit
            ];
        }
                
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取赢钱榜
     */
    public static function getWinRanking($appId, $page = 1, $limit = 20, $filters = [])
    {
        $wheres = [];
        $wheres[] = ['action_type', '=', 1];
        $wheres[] = ['app_id', '=', $appId];
        
        // 时间筛选
        if (!empty($filters['date'])) {
            $startTime = strtotime($filters['date']);
            $endTime = strtotime($filters['date'] . ' 23:59:59');

            $wheres[] = ['create_time', '>=', $startTime];
            $wheres[] = ['create_time', '<=', $endTime];
        }
        
        // 使用子查询获取每个用户的统计数据
        $subQuery = self::field([
            'user_id',
            'SUM(total_win_amount) as total_win', // 总赢钱
            'COUNT(*) as bet_count', // 注单数
            'SUM(bet) as total_bet', // 总投注
        ])
        ->where($wheres)
        ->group('user_id')
        ->having('total_win > 0')
        ->buildSql();
        
        // echo $subQuery;

        // 获取总数
        $total = self::table($subQuery . ' t')->count();

        // 获取排行榜数据
        $list = self::table($subQuery . ' t')
            ->order('total_win', 'desc')
            ->page($page, $limit)
            ->select()
            ->toArray();

        // 获取用户ID列表
        $userIds = array_column($list, 'user_id');
        
        if (!empty($userIds)) {
            // 获取用户信息
            $users = CommonMerchantUserAndBalance::getMerchantUser($userIds);
            // 获取用户余额    
            $userBanlance = CommonMerchantUserAndBalance::getMerchantUserBalance($userIds);

            $users = array_column($users, null, 'user_id');
            $userBanlance = array_column($userBanlance, null, 'user_id');
 
            // 补充用户信息和计算RTP
            foreach ($list as &$item) {
                $userId = (int)$item['user_id'];
                $item['nickname'] = $users[$userId]['nickname'] ?? '未知用户';
                $item['balance'] = $userBanlance[$userId]['balance'] ?? 0;
                // 计算RTP (返还率)
                $item['rtp'] = $item['total_bet'] > 0 ? round($item['total_win'] / $item['total_bet'] * 100, 2) : 0;
            }
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取输钱榜
     */
    public static function getLoseRanking($appId, $page = 1, $limit = 20, $filters = [])
    {
        $wheres = [];
        $wheres[] = ['action_type', '=', 1];
        $wheres[] = ['app_id', '=', $appId];
        
        // 时间筛选
        if (!empty($filters['date'])) {
            $startTime = strtotime($filters['date']);
            $endTime = strtotime($filters['date'] . ' 23:59:59');

            $wheres[] = ['create_time', '>=', $startTime];
            $wheres[] = ['create_time', '<=', $endTime];
        }
        
        // 使用子查询获取每个用户的统计数据
        $subQuery = self::field([
            'user_id',
            'SUM(total_win_amount) as total_lose', // 总输钱
            'COUNT(*) as bet_count', // 注单数
            'SUM(bet) as total_bet', // 总投注
        ])
        ->where($wheres)
        ->group('user_id')
        ->having('total_lose < 0')
        ->buildSql();
        
        // 获取总数
        $total = self::table($subQuery . ' t')->count();

        // 获取排行榜数据
        $list = self::table($subQuery . ' t')
            ->order('total_lose', 'asc')
            ->page($page, $limit)
            ->select()
            ->toArray();
         
        // 获取用户ID列表
        $userIds = array_column($list, 'user_id');
        
        if (!empty($userIds)) {

            // 获取用户信息
            $users = CommonMerchantUserAndBalance::getMerchantUser($userIds);
            // 获取用户余额
            $userBanlance = CommonMerchantUserAndBalance::getMerchantUserBalance($userIds);

            $users = array_column($users, null, 'user_id');
            $userBanlance = array_column($userBanlance, null, 'user_id');
 
            // 补充用户信息和计算RTP
            foreach ($list as &$item) {
                $userId = (int)$item['user_id'];
                $item['nickname'] = $users[$userId]['nickname'] ?? '未知用户';
                $item['balance'] = $userBanlance[$userId]['balance'] ?? 0;
                // 计算RTP (返还率)
                $item['rtp'] = $item['total_bet'] > 0 ? round(abs((int)$item['total_lose']) / $item['total_bet'] * 100, 2) : 0;
            }
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取注单金额榜
     */
    public static function getBetAmountRanking($appId, $page = 1, $limit = 20, $filters = [])
    {
        $wheres = [];
        $wheres[] = ['action_type', '=', 1];
        $wheres[] = ['app_id', '=', $appId];
        
        // 时间筛选
        if (!empty($filters['date'])) {
            $startTime = strtotime($filters['date']);
            $endTime = strtotime($filters['date'] . ' 23:59:59');

            $wheres[] = ['create_time', '>=', $startTime];
            $wheres[] = ['create_time', '<=', $endTime];
        }
        
        // 使用子查询获取每个用户的统计数据
        $subQuery = self::field([
            'user_id',
            'SUM(total_win_amount) as total_win', // 总输钱
            'COUNT(*) as bet_count', // 注单数
            'SUM(bet) as total_bet', // 总投注
        ])
        ->where($wheres)
        ->group('user_id')
        ->buildSql();
        
        // 获取总数
        $total = self::table($subQuery . ' t')->count();

        // 获取排行榜数据
        $list = self::table($subQuery . ' t')
            ->order('bet_count', 'desc')
            ->page($page, $limit)
            ->select()
            ->toArray();
         
        // 获取用户ID列表
        $userIds = array_column($list, 'user_id');
        
        if (!empty($userIds)) {
            // 获取用户信息
            $users = CommonMerchantUserAndBalance::getMerchantUser($userIds);
            // 获取用户余额
            $userBanlance = CommonMerchantUserAndBalance::getMerchantUserBalance($userIds);

            $users = array_column($users, null, 'user_id');
            $userBanlance = array_column($userBanlance, null, 'user_id');
 
            // 补充用户信息和计算RTP
            foreach ($list as &$item) {
                $userId = (int)$item['user_id'];
                $item['nickname'] = $users[$userId]['nickname'] ?? '未知用户';
                $item['balance'] = $userBanlance[$userId]['balance'] ?? 0;
                // 计算RTP (返还率)
                $item['rtp'] = $item['total_bet'] > 0 ? round(abs((int)$item['total_win']) / $item['total_bet'] * 100, 2) : 0;
            }
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }

    /**
     * 获取游戏每日数据
     */
    public static function getGameDailySummary($merchantId, $filters = [])
    {
        $where = [
            ['app_id', '=', $merchantId],
            ['action_type', '=', 1],
        ];
        
        // 时间筛选
        if (!empty($filters['start_time'])) {
            $startTime = strtotime($filters['start_time']);
            $where[] = ['create_time', '>=', $startTime . ' 00:00:00'];
        }
        
        if (!empty($filters['end_time'])) {
            $endTime = strtotime($filters['end_time']);
            $where[] = ['create_time', '<=', $endTime . ' 23:59:59'];
        }

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

        $list = self::field([
                "FROM_UNIXTIME(create_time, '%Y-%m-%d') as date", // 日期
                'COUNT(DISTINCT uname) as bet_users',  // 投注用户数
                'COUNT(id) as bet_count',  // 注单数
                'SUM(bet) as bet_amount',  // 注单金额（下注分数）
                'SUM(total_win_amount) as game_profit',  // 游戏输赢（金额变化）
                'SUM(is_buy_game) as buy_free_bet_count'
        ])
        ->where($where)
        ->group('date')
        ->select()
        ->toArray();

        return $list;
    }



    public static function getBuyBetGameList($appId, $filters = []) {
        $where = [
            ['app_id', '=', $appId],
            ['is_buy_game', '=' , 1],
            ['action_type', '=' , 1]
        ];
        $where = array_merge($where, $filters);
        $query = self::where($where);
        return $query;
    }
}