merchant_admin/app/model/MerchantHistoryModel.php

<?php
declare (strict_types = 1);

namespace app\model;

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

/**
 * 商户历史数据统计模型 - 基于tp_game_bet_order表统计所有历史数据
 */
class MerchantHistoryModel extends Model
{
    // 连接到fortue_tiger数据库
    protected $connection = 'fortue_tiger';
    
    /**
     * 获取商户历史数据统计
     * @param int $merchantId 商户ID
     * @param array $filters 过滤条件
     */
    public static function getMerchantHistoryStatistics($merchantId, $filters = [])
    {
        // 基础查询条件
        $where = [
            ['app_id', '=', $merchantId],
            ['status', '=', 1]  // 只统计有效订单
        ];
        
        // 日期范围过滤
        if (!empty($filters['start_date'])) {
            $startTime = strtotime($filters['start_date'] . ' 00:00:00');
            $where[] = ['create_time', '>=', $startTime];
        }
        
        if (!empty($filters['end_date'])) {
            $endTime = strtotime($filters['end_date'] . ' 23:59:59');
            $where[] = ['create_time', '<=', $endTime];
        }
        
        // 获取汇总统计数据
        $stats = Db::connect('fortue_tiger')
            ->name('game_bet_order')
            ->where($where)
            ->field([
                'COUNT(id) as total_orders',  // 注单数
                'SUM(bet) as total_bet',  // 注单金额（分）
                'SUM(amount) as total_profit',  // 游戏输赢（金额变化）
                'SUM(total_win_amount) as total_win',  // 总赢得金额
                'COUNT(DISTINCT uname) as bet_users',  // 投注用户数
                'COUNT(DISTINCT CASE WHEN action_type = 5 THEN id END) as free_game_count'  // 购买免费游戏次数
            ])
            ->find();
        
        // 计算RTP（Return to Player）
        $rtp = 0;
        if ($stats['total_bet'] > 0) {
            // RTP = (返还给玩家的金额 / 投注总额) * 100
            $rtp = round(($stats['total_win'] / $stats['total_bet']) * 100, 2);
        }
        
        // 获取注册用户数（根据时间范围）
        $registerQuery = Db::connect('fortue_tiger')
            ->name('merchants_user')
            ->where('app_id', $merchantId);
            
        if (!empty($filters['start_date'])) {
            $registerQuery->where('create_time', '>=', strtotime($filters['start_date'] . ' 00:00:00'));
        }
        
        if (!empty($filters['end_date'])) {
            $registerQuery->where('create_time', '<=', strtotime($filters['end_date'] . ' 23:59:59'));
        }
        
        $registerUsers = $registerQuery->count();
        
        // 获取登录用户数（根据时间范围）
        $loginQuery = Db::connect('fortue_tiger')
            ->name('merchants_user')
            ->where('app_id', $merchantId);
            
        if (!empty($filters['start_date'])) {
            $loginQuery->where('login_time', '>=', strtotime($filters['start_date'] . ' 00:00:00'));
        }
        
        if (!empty($filters['end_date'])) {
            $loginQuery->where('login_time', '<=', strtotime($filters['end_date'] . ' 23:59:59'));
        }
        
        $loginUsers = $loginQuery->where('login_time', '>', 0)->count();
        
        // 假设汇率（可以从配置或其他表获取）
        $thbToUsdtRate = 0.028;  // 1 THB = 0.028 USDT (示例汇率)
        
        // 计算抽水额度（平台盈利 = 下注金额 - 赢得金额）
        $commission = ($stats['total_bet'] ?? 0) - ($stats['total_win'] ?? 0);
        
        // 组装返回数据
        $result = [
            'rtp' => $rtp . '%',  // RTP百分比
            'total_orders' => number_format($stats['total_orders'] ?? 0),  // 注单数
            'total_bet_thb' => number_format(($stats['total_bet'] ?? 0) / 100, 2) . ' THB',  // 注单金额(THB)
            'game_profit_thb' => number_format(abs($stats['total_profit'] ?? 0) / 100, 2) . ' THB',  // 游戏输赢(THB)
            'total_bet_usdt' => number_format(($stats['total_bet'] ?? 0) / 100 * $thbToUsdtRate, 4) . ' USDT',  // 注单金额(USDT)
            'game_profit_usdt' => number_format(abs($stats['total_profit'] ?? 0) / 100 * $thbToUsdtRate, 4) . ' USDT',  // 游戏输赢(USDT)
            'login_users' => $loginUsers,  // 登录用户
            'register_users' => $registerUsers,  // 注册用户
            'bet_users' => $stats['bet_users'] ?? 0,  // 投注用户
            'commission_usdt' => number_format($commission / 100 * $thbToUsdtRate, 4) . ' USDT',  // 抽水额度(USDT)
            'free_game_count' => $stats['free_game_count'] ?? 0,  // 购买免费游戏次数
            
            // 原始数据（不带格式化）
            'raw_data' => [
                'rtp' => $rtp,
                'total_orders' => $stats['total_orders'] ?? 0,
                'total_bet' => ($stats['total_bet'] ?? 0) / 100,
                'game_profit' => abs($stats['total_profit'] ?? 0) / 100,
                'total_win' => ($stats['total_win'] ?? 0) / 100,
                'commission' => $commission / 100,
                'thb_to_usdt_rate' => $thbToUsdtRate
            ]
        ];
        
        return $result;
    }
    
    /**
     * 获取商户历史数据列表（按月份或按天统计）
     */
    public static function getMerchantHistoryList($merchantId, $groupBy = 'month', $page = 1, $limit = 10, $filters = [])
    {
        // 日期格式
        $dateFormat = $groupBy === 'month' ? '%Y-%m' : '%Y-%m-%d';
        
        // 基础SQL
        $sql = "SELECT 
                    DATE_FORMAT(FROM_UNIXTIME(create_time), '{$dateFormat}') as period,
                    COUNT(id) as total_orders,
                    SUM(bet) as total_bet,
                    SUM(amount) as game_profit,
                    SUM(total_win_amount) as total_win,
                    COUNT(DISTINCT uname) as bet_users,
                    COUNT(DISTINCT CASE WHEN action_type = 5 THEN id END) as free_game_count
                FROM tp_game_bet_order
                WHERE app_id = ? AND status = 1";
        
        $params = [$merchantId];
        
        // 日期范围
        if (!empty($filters['start_date'])) {
            $sql .= " AND create_time >= ?";
            $params[] = strtotime($filters['start_date'] . ' 00:00:00');
        }
        
        if (!empty($filters['end_date'])) {
            $sql .= " AND create_time <= ?";
            $params[] = strtotime($filters['end_date'] . ' 23:59:59');
        }
        
        $sql .= " GROUP BY period ORDER BY period DESC";
        
        // 获取总数
        $countSql = "SELECT COUNT(DISTINCT DATE_FORMAT(FROM_UNIXTIME(create_time), '{$dateFormat}')) as total
                     FROM tp_game_bet_order
                     WHERE app_id = ? AND status = 1";
        
        $countParams = [$merchantId];
        if (!empty($filters['start_date'])) {
            $countSql .= " AND create_time >= ?";
            $countParams[] = strtotime($filters['start_date'] . ' 00:00:00');
        }
        if (!empty($filters['end_date'])) {
            $countSql .= " AND create_time <= ?";
            $countParams[] = strtotime($filters['end_date'] . ' 23:59:59');
        }
        
        $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);
        
        // 假设汇率
        $thbToUsdtRate = 0.028;
        
        // 处理每条记录
        foreach ($list as &$item) {
            // 计算RTP
            $item['rtp'] = 0;
            if ($item['total_bet'] > 0) {
                $item['rtp'] = round(($item['total_win'] / $item['total_bet']) * 100, 2);
            }
            
            // 获取该时期的注册和登录用户
            if ($groupBy === 'month') {
                $periodStart = strtotime($item['period'] . '-01 00:00:00');
                $periodEnd = strtotime(date('Y-m-t', $periodStart) . ' 23:59:59');
            } else {
                $periodStart = strtotime($item['period'] . ' 00:00:00');
                $periodEnd = strtotime($item['period'] . ' 23:59:59');
            }
            
            $item['register_users'] = Db::connect('fortue_tiger')
                ->name('merchants_user')
                ->where('app_id', $merchantId)
                ->where('create_time', '>=', $periodStart)
                ->where('create_time', '<=', $periodEnd)
                ->count();
            
            $item['login_users'] = Db::connect('fortue_tiger')
                ->name('merchants_user')
                ->where('app_id', $merchantId)
                ->where('login_time', '>=', $periodStart)
                ->where('login_time', '<=', $periodEnd)
                ->count();
            
            // 计算抽水
            $commission = ($item['total_bet'] - $item['total_win']);
            
            // 格式化数据
            $item['commission_usdt'] = $commission / 100 * $thbToUsdtRate;
            $item['total_bet_thb'] = $item['total_bet'] / 100;
            $item['total_bet_usdt'] = $item['total_bet'] / 100 * $thbToUsdtRate;
            $item['game_profit_thb'] = abs($item['game_profit']) / 100;
            $item['game_profit_usdt'] = abs($item['game_profit']) / 100 * $thbToUsdtRate;
            
            // 移除原始字段
            unset($item['total_bet']);
            unset($item['game_profit']);
            unset($item['total_win']);
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
}