merchant_admin/app/model/MerchantDailyModel.php

<?php
declare (strict_types = 1);

namespace app\model;

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

/**
 * 商户每日数据统计模型 - 基于tp_game_bet_order表实时统计
 */
class MerchantDailyModel extends Model
{
    // 连接到fortue_tiger数据库
    protected $connection = 'fortue_tiger';
    
    /**
     * 获取商户每日数据列表
     * @param int $merchantId 商户ID
     * @param string $startDate 开始日期
     * @param string $endDate 结束日期
     * @param int $page 页码
     * @param int $limit 每页数量
     */
    public static function getMerchantDailyList($merchantId, $startDate, $endDate, $page = 1, $limit = 10)
    {
        $startTime = strtotime($startDate . ' 00:00:00');
        $endTime = strtotime($endDate . ' 23:59:59');
        
        // 生成日期数组
        $dates = [];
        $current = $startTime;
        while ($current <= $endTime) {
            $dates[] = date('Y-m-d', $current);
            $current += 86400;
        }
        
        // 按日期分组统计
        $offset = ($page - 1) * $limit;
        $paginatedDates = array_slice($dates, $offset, $limit);
        
        $list = [];
        foreach ($paginatedDates as $date) {
            $dayStart = strtotime($date . ' 00:00:00');
            $dayEnd = strtotime($date . ' 23:59:59');
            
            // 获取游戏下注统计数据
            $betStats = Db::connect('fortue_tiger')
                ->name('game_bet_order')
                ->where('app_id', $merchantId)
                ->where('create_time', '>=', $dayStart)
                ->where('create_time', '<=', $dayEnd)
                ->where('status', 1) // 只统计有效订单
                ->field([
                    '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 game_id) as game_count'  // 游戏数量
                ])
                ->find();
            
            // 计算RTP（Return to Player）
            $rtp = 0;
            if ($betStats['bet_amount'] > 0) {
                // RTP = (返还给玩家的金额 / 投注总额) * 100
                $rtp = round(($betStats['total_win'] / $betStats['bet_amount']) * 100, 2);
            }
            
            // 获取注册用户数
            $registerUsers = Db::connect('fortue_tiger')
                ->name('merchants_user')
                ->where('app_id', $merchantId)
                ->where('create_time', '>=', $dayStart)
                ->where('create_time', '<=', $dayEnd)
                ->count();
            
            // 获取登录用户数
            $loginUsers = Db::connect('fortue_tiger')
                ->name('merchants_user')
                ->where('app_id', $merchantId)
                ->where('login_time', '>=', $dayStart)
                ->where('login_time', '<=', $dayEnd)
                ->count();
            
            // 计算抽水额度（平台盈利 = 下注金额 - 赢得金额）
            $commission = ($betStats['bet_amount'] ?? 0) - ($betStats['total_win'] ?? 0);
            
            // 获取免费游戏购买次数（假设action_type中某个值代表免费游戏）
            $freeGameCount = Db::connect('fortue_tiger')
                ->name('game_bet_order')
                ->where('app_id', $merchantId)
                ->where('create_time', '>=', $dayStart)
                ->where('create_time', '<=', $dayEnd)
                ->where('action_type', 5) // 假设5代表免费游戏
                ->count();
            
            // 获取平台费用（假设从转账记录表获取）
            $platformFee = 0;
            if (Db::connect('fortue_tiger')->query("SHOW TABLES LIKE 'tp_transfer_log'")) {
                $platformFee = Db::connect('fortue_tiger')
                    ->name('transfer_log')
                    ->where('merchant_id', $merchantId)
                    ->where('type', 'platform_fee')
                    ->where('create_time', '>=', $dayStart)
                    ->where('create_time', '<=', $dayEnd)
                    ->sum('amount');
            }
            
            $list[] = [
                'date' => $date,
                'merchant_id' => $merchantId,
                'game_profit' => abs($betStats['game_profit'] ?? 0) / 100,  // 转换为元
                'game_rtp' => $rtp,
                'bet_amount' => ($betStats['bet_amount'] ?? 0) / 100,  // 转换为元
                'bet_count' => $betStats['bet_count'] ?? 0,
                'bet_users' => $betStats['bet_users'] ?? 0,
                'register_users' => $registerUsers,
                'login_users' => $loginUsers,
                'commission_amount' => $commission / 100,  // 转换为元
                'platform_fee' => $platformFee / 100,  // 转换为元
                'free_game_count' => $freeGameCount,
                'game_count' => $betStats['game_count'] ?? 0
            ];
        }
        
        return [
            'list' => $list,
            'total' => count($dates),
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取商户汇总统计
     */
    public static function getMerchantSummary($merchantId, $startDate, $endDate)
    {
        $startTime = strtotime($startDate . ' 00:00:00');
        $endTime = strtotime($endDate . ' 23:59:59');
        
        // 获取汇总统计数据
        $summary = Db::connect('fortue_tiger')
            ->name('game_bet_order')
            ->where('app_id', $merchantId)
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<=', $endTime)
            ->where('status', 1)
            ->field([
                '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 game_id) as total_games',
                'COUNT(DISTINCT DATE_FORMAT(FROM_UNIXTIME(create_time), "%Y-%m-%d")) as active_days'
            ])
            ->find();
        
        // 计算平均RTP
        $avgRtp = 0;
        if ($summary['total_bet_amount'] > 0) {
            $avgRtp = round(($summary['total_win_amount'] / $summary['total_bet_amount']) * 100, 2);
        }
        
        // 获取总注册用户
        $totalRegisterUsers = Db::connect('fortue_tiger')
            ->name('merchants_user')
            ->where('app_id', $merchantId)
            ->where('create_time', '>=', $startTime)
            ->where('create_time', '<=', $endTime)
            ->count();
        
        // 获取总登录用户
        $totalLoginUsers = Db::connect('fortue_tiger')
            ->name('merchants_user')
            ->where('app_id', $merchantId)
            ->where('login_time', '>=', $startTime)
            ->where('login_time', '<=', $endTime)
            ->count();
        
        return [
            'total_bet_users' => $summary['total_bet_users'] ?? 0,
            'total_bet_count' => $summary['total_bet_count'] ?? 0,
            'total_bet_amount' => ($summary['total_bet_amount'] ?? 0) / 100,
            'total_game_profit' => abs($summary['total_game_profit'] ?? 0) / 100,
            'total_win_amount' => ($summary['total_win_amount'] ?? 0) / 100,
            'avg_rtp' => $avgRtp,
            'total_games' => $summary['total_games'] ?? 0,
            'active_days' => $summary['active_days'] ?? 0,
            'total_register_users' => $totalRegisterUsers,
            'total_login_users' => $totalLoginUsers,
            'total_commission' => (($summary['total_bet_amount'] ?? 0) - ($summary['total_win_amount'] ?? 0)) / 100
        ];
    }
}