merchant_admin/app/model/MerchantStatisModel.php

<?php
declare (strict_types = 1);

namespace app\model;

use think\Model;
use think\facade\Db;
use app\model\MerchantsUserModel;

/**
 * 商户数据统计
 */
class MerchantStatisModel extends Model
{
    protected $name = 'game_bet_game';
    // 连接到fortue_tiger数据库
    protected $connection = 'fortue_tiger';
    
    /**
     * 获取商户每日数据列表
     * @param int $merchantId 商户ID
     * @param int $page 页码
     * @param int $limit 每页数量
     * @param array $filters 过滤条件     
     */
    public static function getMerchantDailyList($merchantId, $page = 1, $limit = 20, $filters = [])
    {
        $where = [
            ['app_id', '=', $merchantId],
            ['action_type', '=', 1],
        ];
        
        // 时间筛选
        if (!empty($filters['start_time'])) {
            $startTime = strtotime($filters['start_time'] . " 00:00:00");
            $where[] = ['create_time', '>=', $startTime];
        }
        
        if (!empty($filters['end_time'])) {
            $endTime = strtotime($filters['end_time']. " 23:59:59");
            $where[] = ['create_time', '<=', $endTime];
        }
        $query = self::where($where);
        // 统计总数
        $total = $query->count();
        // 获取列表数据
        $list = $query->field([
                    "FROM_UNIXTIME(create_time, '%Y-%m-%d') as date", // 日期
                    'AVG(rtp) AS rtp', // 平均RTP
                    'COUNT(DISTINCT uname) as bet_users',  // 投注用户数
                    'COUNT(id) as bet_count',  // 注单数
                    'SUM(bet) as bet_amount',  // 注单金额（下注分数）
                    'SUM(total_win_amount) as game_profit',  // 游戏输赢（金额变化）
                    'COUNT(DISTINCT game_id) as game_count',  // 游戏数量
                    'SUM(is_buy_game) as buy_free_bet_count'
        ])
            ->order('date', 'desc')
            ->group('date')
            ->page($page, $limit)
            ->select()
            ->toArray();
        // 获取登录用户、注册用户    
        $merchantsUserStatic = MerchantsUserModel::getPlayerStatistics($merchantId, $filters);
        
        foreach ($list as &$row)
        {
            $row['login_users'] = $merchantsUserStatic[$row['date']]['login_users'] ?? 0;
            $row['register_users'] = $merchantsUserStatic[$row['date']]['register_users'] ?? 0;
        }
        
        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }
    
    /**
     * 获取商户汇总统计
     */
    public static function getMerchantHistory($merchantId, $filters = [])
    {
        $where = [
            ['app_id', '=', $merchantId],
        ];

        $getWhere = function(string $timeField) use ($where, $filters) {
            // 时间筛选
            if (!empty($filters['start_time'])) {
                $startTime = strtotime($filters['start_time'] . " 00:00:00");
                $where[] = [$timeField, '>=', $startTime];
            }
            
            if (!empty($filters['end_time'])) {
                $endTime = strtotime($filters['end_time'] . " 23:59:59");
                $where[] = [$timeField, '<=', $endTime];
            }            
            return $where;
        };
        $tempWhere = $getWhere('create_time');
        $tempWhere[] = ['action_type', '=', 1];
        $query = self::where($tempWhere);

        // 获取列表数据
        $data = $query->field([
                    'AVG(rtp) AS rtp', // 平均RTP
                    'COUNT(DISTINCT uname) as bet_users',  // 投注用户数
                    'COUNT(id) as bet_count',  // 注单数
                    'SUM(bet) as bet_amount',  // 注单金额（下注分数）
                    'SUM(total_win_amount) as game_profit',  // 游戏输赢（金额变化）
                    'COUNT(DISTINCT game_id) as game_count',  // 游戏数量
                    'SUM(is_buy_game) as buy_free_bet_count'
        ])
            ->find()
            ->toArray();
        // 获取登录用户、注册用户 
        $data['login_users'] = MerchantsUserModel::where($getWhere('login_time'))->count(); 
        $data['register_users'] = MerchantsUserModel::where($getWhere('create_time'))->count();  
        
        return $data;
    }
}