merchant_admin/app/model/TrendStatisModel.php

<?php
declare (strict_types = 1);

namespace app\model;

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

/**
 * 走势数据统计
 */
class TrendStatisModel extends Model
{
    protected $name = 'game_bet_game';

    // 连接到fortue_tiger数据库
    protected $connection = 'fortue_tiger';
    
    /**
     * 获取输赢走势数据
     */
    public static function getWinLoseTrend($appId, $filters = [])
    {
        $wheres = [];
        $wheres[] = ['action_type', '=', 1];
        $wheres[] = ['app_id', '=', $appId];
        
        // 时间筛选
        $startTime = !empty($filters['date']) ? strtotime($filters['date']) : strtotime(date('Ymd'));
        $endTime = $startTime + 86400;
        
        $wheres[] = ['create_time', '>=', $startTime];
        $wheres[] = ['create_time', '<=', $endTime];
        
        // 游戏ID筛选
        if (!empty($filters['game_id'])) {
            $filters['game_id'] = explode(',', $filters['game_id']);
            $wheres[] = ['game_id', 'IN', $filters['game_id']];
        }
        
        // 货币类型筛选
        if (!empty($filters['currency'])) {
            // 这里可能需要关联其他表来获取货币类型，暂时忽略
        }

        $query = self::where($wheres);
        // 获取列表数据
        $data = $query->field([
                    "from_unixtime(create_time, '%k') AS hour",
                    'SUM(total_win_amount) as total_win',
            ])
            ->group('hour')
            ->select()
            ->toArray();        
        
        return $data;
    }
    
    /**
     * 获取返奖倍数走势数据（RTP走势）
     */
    public static function getMultipleTrend($appId, $filters = [])
    {
        $wheres = [];
        $wheres[] = ['action_type', '=', 1];
        $wheres[] = ['app_id', '=', $appId];
        
        // 时间筛选
        $startTime = !empty($filters['date']) ? strtotime($filters['date']) : strtotime(date('Ymd'));
        $endTime = $startTime + 86400;
        
        $wheres[] = ['create_time', '>=', $startTime];
        $wheres[] = ['create_time', '<=', $endTime];
        
        // 游戏ID筛选
        if (!empty($filters['game_id'])) {
            $filters['game_id'] = explode(',', $filters['game_id']);
            $wheres[] = ['game_id', 'IN', $filters['game_id']];
        }

        $query = self::where($wheres);
        // 获取列表数据
        $data = $query->field([
                    "from_unixtime(create_time, '%k') AS hour",
                    'SUM(res_multiple) as total_multiple',
            ])
            ->group('hour')
            ->select()
            ->toArray();        
        
        return $data;
    }
    
    /**
     * 获取商户每日走势数据
     */
    public static function getMerchantDailyTrend($appId, $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', 'IN', $filters['game_id']];
        }
        
        $query = self::where($wheres);
        // 获取列表数据
        $data = $query->field([
                    "from_unixtime(create_time, '%Y%m%d') AS date",
                    'SUM(total_win_amount) as total_win',
                    'SUM(bet) as total_bet',
                    'COUNT(*) as bet_count', // 注单数
                    'COUNT(DISTINCT uname) as bet_users',  // 投注用户数
            ])
            ->group('date')
            ->select()
            ->toArray(); 
        
        return $data;
    }
}