new_sdk_wu/www/new_sdk/application/service/ComplexSummaryService.php

<?php

namespace app\service;

use think\Db;

/**
 * 聚合数据统计服务类
 * 用于统一处理聚合数据统计的公共查询方法
 */
class ComplexSummaryService
{
    /**
     * 将查询结果统一转换为数组，兼容 Collection/array
     * @param mixed $result
     * @return array
     */
    protected function normalizeSelectResult($result)
    {
        if (empty($result)) {
            return [];
        }
        if (is_array($result)) {
            return $result;
        }
        return $result->toArray();
    }

    /**
     * 获取已上线游戏ID列表
     * @return array
     */
    public function getOnlineGameIds()
    {
        return model('common/Game')
            ->cache('complex:online_game_ids', 500)
            ->where('cooperation_status', 'in', [1, 2])
            ->column('id');
    }

    /**
     * 构建基础查询条件
     * @param string $day 日期 (格式: Y-m-d)
     * @return array 返回时间范围数组
     */
    public function buildTimeRange($day)
    {
        $startTime = strtotime($day);
        $endTime = strtotime($day . ' 23:59:59');
        return [$startTime, $endTime];
    }

    /**
     * 构建分组字段和GROUP BY子句
     * @param int $hasGame 是否按游戏分组 (0=否, 1=是)
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer 是否按区服分组 (0=否, 1=是)
     * @param string $gameField 游戏ID字段名（默认：gameid，用于members和pay表）
     * @param string $channelField 渠道ID字段名（默认：channel_id，用于members和pay表）
     * @return array ['field' => 字段字符串, 'group' => GROUP BY数组]
     */
    protected function buildGroupField($hasGame = 1, $hasChannel = 1, $hasServer = 0, $gameField = 'gameid', $channelField = 'channel_id')
    {
        $field = '';
        $group = [];
        
        if ($hasGame) {
            $field .= $gameField . ' as game_id,';
            $group[] = $gameField;
        } else {
            $field .= '0 as game_id,';
        }
        
        if ($hasChannel) {
            $field .= $channelField . ' as channel_id,';
            $group[] = $channelField;
        } else {
            $field .= '0 as channel_id,';
        }
        
        if ($hasServer) {
            $field .= 'server_id,';
            $group[] = 'server_id';
        } else {
            $field .= '0 as server_id,';
        }
        
        return [
            'field' => $field,
            'group' => $group
        ];
    }

    /**
     * 新增玩家统计
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选，为空则统计所有已上线游戏）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @return array
     */
    public function getRegNum($day, $gameIds = [], $hasChannel = 1)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);
        $groupInfo = $this->buildGroupField(1, $hasChannel, 0);

        $sql = Db::table('nw_complex_members')->whereBetween('reg_time', [$startTime, $endTime]);

        // 只统计已上线游戏
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $sql->where('gameid', 'in', $gameIds);
        }
        
        $field = $groupInfo['field'] . 'count(DISTINCT id) as reg_num';
        $sql->field($field);
        
        if (!empty($groupInfo['group'])) {
            $sql->group(implode(',', $groupInfo['group']));
        }

        // dump($sql->fetchSql(true)->select());
        return $this->normalizeSelectResult($sql->select());
    }

    /**
     * 新增角色统计
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer 是否按区服分组 (0=否, 1=是)
     * @return array
     */
    public function getRegRoleNum($day, $gameIds = [], $hasChannel = 1, $hasServer = 0)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);
        // 角色表使用 game_id 和 complex_id
        $groupInfo = $this->buildGroupField(1, $hasChannel, $hasServer, 'game_id', 'complex_id');
        
        $sql = Db::table('nw_complex_role')->whereBetween('create_time', [$startTime, $endTime]);
        
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $sql->where('game_id', 'in', $gameIds);
        }
        
        // 如果按区服分组，确保 server_id 不为 NULL，避免多个区服的数据被合并
        // if ($hasServer) {
        //     $sql->whereNotNull('server_id');
        // }
        
        $field = $groupInfo['field'] . ' count(DISTINCT id) as reg_role_num';
        $sql->field($field);
        
        if (!empty($groupInfo['group'])) {
            $sql->group(implode(',', $groupInfo['group']));
        }

        // dump($sql->fetchSql(true)->select());
        $result = $this->normalizeSelectResult($sql->select());
        
        // 如果按区服分组，批量查询区服名称
        if ($hasServer && !empty($result)) {
            // 收集所有的 server_id
            $serverIds = array_filter(array_unique(array_column($result, 'server_id')));
            
            // 批量查询区服名称
            $serverNames = [];
            if (!empty($serverIds)) {
                $serverList = Db::table('nw_complex_server')
                    ->where('id', 'in', $serverIds)
                    ->column('server_name', 'id');
                $serverNames = $serverList ?: [];
            }
            
            // 合并区服名称到结果中
            foreach ($result as &$item) {
                $item['server_name'] = $serverNames[$item['server_id']] ?? '';
            }
            unset($item);
        }
        
        return $result;
    }

    /**
     * 充值金额和付费人数统计
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer 是否按区服分组 (0=否, 1=是)
     * @return array
     */
    public function getPayMoney($day, $gameIds = [], $hasChannel = 1, $hasServer = 0)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);
        // 支付表使用 gameid 和 channel_id
        $groupInfo = $this->buildGroupField(1, $hasChannel, $hasServer, 'gameid', 'channel_id');
        
        $sql = Db::table('nw_complex_pay')
            ->whereBetween('create_time', [$startTime, $endTime])
            ->where('status', 1); // 只统计成功订单
        
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $sql->where('gameid', 'in', $gameIds);
        }

        // , count(DISTINCT userid) as pay_user_num
        $field = $groupInfo['field'] . 'sum(amount) as pay_money, sum(pay_amount) as pay_amount_total, count(DISTINCT userid) as pay_user_num';
        $sql->field($field);
        
        if (!empty($groupInfo['group'])) {
            $sql->group(implode(',', $groupInfo['group']));
        }

        // dump($sql->fetchSql(true)->select());
        return $this->normalizeSelectResult($sql->select());
    }

    /**
     * 活跃玩家统计（基于玩家活跃日志表 nw_complex_player_active_log）
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer 是否按区服分组 (0=否, 1=是)
     * @return array
     */
    public function getActiveUserByActiveLog($day, $gameIds = [], $hasChannel = 1, $hasServer = 0)
    {
        $startTime = $day . ' 00:00:00';
        $endTime = $day . ' 23:59:59';
        $groupInfo = $this->buildGroupField(1, $hasChannel, $hasServer, 'game_id', 'complex_id');

        $sql = Db::table('nw_complex_player_active_log')
            ->whereBetween('active_hour', [$startTime, $endTime]);

        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $sql->where('game_id', 'in', $gameIds);
        }

        $field = $groupInfo['field'] . 'count(DISTINCT member_id) as active_user_num';
        $sql->field($field);

        if (!empty($groupInfo['group'])) {
            $sql->group(implode(',', $groupInfo['group']));
        }

        $result = $this->normalizeSelectResult($sql->select());

        if ($hasServer && !empty($result)) {
            $serverIds = array_filter(array_unique(array_column($result, 'server_id')));
            $serverNames = [];
            if (!empty($serverIds)) {
                $serverNames = Db::table('nw_complex_server')
                    ->where('id', 'in', $serverIds)
                    ->column('server_name', 'id') ?: [];
            }

            foreach ($result as &$item) {
                $item['server_name'] = $serverNames[$item['server_id']] ?? '';
            }
            unset($item);
        }

        return $result;
    }

    /**
     * 活跃玩家统计（基于登录日志表 nw_complex_loginlog）
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer 是否按区服分组 (0=否, 1=是)
     * @return array
     */
    public function getActiveUserByLoginLog($day, $gameIds = [], $hasChannel = 1, $hasServer = 0)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);
        // 登录日志表 nw_complex_loginlog 使用 gameid 和 channel_id；表中没有区服字段，这里不做区服分组
        $groupInfo = $this->buildGroupField(1, $hasChannel, 0, 'gameid', 'channel_id');
        
        $sql = Db::table('nw_complex_loginlog')
            ->whereBetween('login_time', [$startTime, $endTime]);
        
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $sql->where('gameid', 'in', $gameIds);
        }

        // 基于登录日志统计活跃用户数，按去重 userid 统计
        $field = $groupInfo['field'] . 'count(DISTINCT userid) as active_user_num';
        $sql->field($field);
        
        if (!empty($groupInfo['group'])) {
            $sql->group(implode(',', $groupInfo['group']));
        }
        
        // dump($sql->fetchSql(true)->select());
        return $this->normalizeSelectResult($sql->select());
    }

    /**
     * 活跃玩家统计（基于登录日志表）（用于游戏数据、渠道数据 统计）
     *
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer  是否按区服分组 (0=否, 1=是) 注：登录日志表无区服字段，此参数暂不生效
     * @param int $platform 是否按平台 (0=否, 1=是)
     * @return array
     */
    public function getActiveUserByMember($day, $gameIds = [], $hasChannel = 1)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);
        // 登录日志表使用 gameid 和 channel_id 字段，不支持区服分组（表中无 server_id 字段）
        $groupInfo = $this->buildGroupField(1, $hasChannel, 0, 'gameid', 'channel_id');
        
        $loginQuery = Db::table('nw_complex_loginlog')
            ->whereBetween('login_time', [$startTime, $endTime]);
        
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $loginQuery->where('gameid', 'in', $gameIds);
        }
        
        // 统计活跃用户数（基于去重的 userid）
        $field = $groupInfo['field'] . 'count(DISTINCT userid) as active_user_num';
        $loginQuery->field($field);
        
        if (!empty($groupInfo['group'])) {
            $loginQuery->group(implode(',', $groupInfo['group']));
        }
        
        // dump($loginQuery->fetchSql(true)->select());
        return $this->normalizeSelectResult($loginQuery->select());
    }

    /**
     * 付费人数统计
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @return array
     */
    public function getPayUserNum($day, $gameIds = [], $hasChannel = 1)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);

        // 支付表使用 gameid 和 channel_id
        $groupInfo = $this->buildGroupField(1, $hasChannel, 0, 'gameid', 'channel_id');

        $payQuery = Db::table('nw_complex_pay')
            ->whereBetween('create_time', [$startTime, $endTime])
            ->where('status', 1); // 只统计成功订单

        if (!empty($gameIds)) {
            $payQuery->where('gameid', 'in', $gameIds);
        }

        $field = $groupInfo['field'] . 'count(DISTINCT userid) as pay_user_num';
        $payQuery->field($field);

        if (!empty($groupInfo['group'])) {
            $payQuery->group(implode(',', $groupInfo['group']));
        }

        // dump($payQuery->fetchSql(true)->select());
        return $this->normalizeSelectResult($payQuery->select());
    }

    /**
     * 新用户付费人数统计（当天注册且当天有充值）
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @return array
     */
    public function getNewUserPayNum($day, $gameIds = [], $hasChannel = 1)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);

        $groupInfo = $this->buildGroupField(1, $hasChannel, 0, 'ncp.gameid', 'ncp.channel_id');
        $payQuery = Db::table('nw_complex_pay')->alias('ncp')
            ->join('nw_complex_members ncm', 'ncp.userid = ncm.id', 'left')
            ->whereBetween('ncp.create_time', [$startTime, $endTime])
            ->whereBetween('ncm.reg_time', [$startTime, $endTime])
            ->where('ncp.status', 1); // 只统计成功订单

        if (!empty($gameIds)) {
            $payQuery->where('ncp.gameid', 'in', $gameIds);
        }

        $field = $groupInfo['field'] . 'count(DISTINCT ncp.userid) as reg_pay_num';
        $payQuery->field($field);

        if (!empty($groupInfo['group'])) {
            $payQuery->group(implode(',', $groupInfo['group']));
        }

        return $this->normalizeSelectResult($payQuery->select());
    }

    /**
     * 新用户充值金额统计
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @return array
     */
    public function getNewUserPayMoney($day, $gameIds = [], $hasChannel = 1)
    {
        list($startTime, $endTime) = $this->buildTimeRange($day);

        // 1. 先从用户表中获取当天注册的新用户ID
        $memberQuery = Db::table('nw_complex_members')
            ->whereBetween('reg_time', [$startTime, $endTime]);

        if (!empty($gameIds)) {
            $memberQuery->where('gameid', 'in', $gameIds);
        }

        // 这里使用玩家在 members 表中的主键 id 作为 userid，与 nw_complex_pay.userid 对应
        $newUserIds = $memberQuery->column('DISTINCT id');

        if (empty($newUserIds)) {
            return [];
        }

        // 2. 再到支付表中，统计这些新用户在当天的充值金额
        // 支付表使用 gameid 和 channel_id
        $groupInfo = $this->buildGroupField(1, $hasChannel, 0, 'gameid', 'channel_id');

        $payQuery = Db::table('nw_complex_pay')
            ->whereBetween('create_time', [$startTime, $endTime])
            ->where('status', 1) // 只统计成功订单
            ->where('userid', 'in', $newUserIds);

        if (!empty($gameIds)) {
            $payQuery->where('gameid', 'in', $gameIds);
        }

        $field = $groupInfo['field'] . 'sum(amount) as reg_pay_money, sum(pay_amount) as reg_pay_money_total';
        $payQuery->field($field);

        if (!empty($groupInfo['group'])) {
            $payQuery->group(implode(',', $groupInfo['group']));
        }

        return $this->normalizeSelectResult($payQuery->select());
    }

    /**
     * 玩家总数统计（累计到指定日期）
     * @param string $day 日期
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @param int $hasServer 是否按区服分组 (0=否, 1=是)
     * @return array
     */
    public function getMemberTotal($day, $gameIds = [], $hasChannel = 1, $hasServer = 0)
    {
        $endTime = strtotime($day . ' 23:59:59');
        $groupInfo = $this->buildGroupField(1, $hasChannel, $hasServer);
        
        $sql = model('common/ComplexMembers')
            ->where('reg_time', '<', $endTime);
        
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $sql->where('gameid', 'in', $gameIds);
        }
        
        $field = $groupInfo['field'] . 'count(DISTINCT id) as reg_total';
        $sql->field($field);
        
        if (!empty($groupInfo['group'])) {
            $sql->group(implode(',', $groupInfo['group']));
        }
        
        return $this->normalizeSelectResult($sql->select());
    }

    /**
     * 基于注册时间的留存计算
     * @param string $targetDay 目标日（当前日期）
     * @param int $dayOffset 注册日相对目标登录日的偏移天数
     * @param array $gameIds 游戏ID列表（可选）
     * @param int $hasChannel 是否按渠道分组 (0=否, 1=是)
     * @return array
     */
    public function getRetentionByRegTime($targetDay, $dayOffset, $gameIds = [], $hasChannel = 1)
    {
        // 计算注册日：次留偏移1天，3留偏移2天，以此类推
        $baseDay = date('Y-m-d', strtotime($targetDay . " -{$dayOffset} days"));
        
        // 基准日时间范围
        $baseStartTime = strtotime($baseDay);
        $baseEndTime = strtotime($baseDay . ' 23:59:59');
        
        // 目标日活跃时间范围（玩家活跃日志使用 DATETIME）
        $targetStartTime = $targetDay . ' 00:00:00';
        $targetEndTime = $targetDay . ' 23:59:59';
        
        // 1. 获取基准日注册的所有用户ID（按游戏和渠道分组）
        $baseDayUsers = model('common/ComplexMembers')
            ->whereBetween('reg_time', [$baseStartTime, $baseEndTime]);
        
        if (empty($gameIds)) {
            $gameIds = $this->getOnlineGameIds();
        }
        if (!empty($gameIds)) {
            $baseDayUsers->where('gameid', 'in', $gameIds);
        }
        
        $baseDayUsersList = $baseDayUsers
            ->field('id, gameid as game_id, channel_id as complex_id')
            ->select();
        
        if (empty($baseDayUsersList)) {
            return [];
        }
        
        // 按游戏和渠道分组，统计基准日总用户数
        $baseStats = [];
        $userIdsByGroup = [];
        foreach ($baseDayUsersList as $user) {
            $key = $user['game_id'] . '_' . $user['complex_id'];
            if (!isset($baseStats[$key])) {
                $baseStats[$key] = [
                    'game_id' => $user['game_id'],
                    'channel_id' => $user['complex_id'],
                    'base_reg_num' => 0,
                    'user_ids' => []
                ];
            }
            $baseStats[$key]['base_reg_num']++;
            $baseStats[$key]['user_ids'][] = $user['id'];
            $userIdsByGroup[$key] = $baseStats[$key]['user_ids'];
        }
        
        // 2. 查询这些用户在目标日是否活跃（基于玩家活跃日志）
        $result = [];
        foreach ($userIdsByGroup as $key => $userIds) {
            $activeCount = Db::table('nw_complex_player_active_log')
                ->where('member_id', 'in', $userIds)
                ->where('game_id', $baseStats[$key]['game_id'])
                ->where('complex_id', $baseStats[$key]['channel_id'])
                ->whereBetween('active_hour', [$targetStartTime, $targetEndTime])
                ->count('DISTINCT member_id');
            
            $baseRegNum = $baseStats[$key]['base_reg_num'];
            $retentionRate = $baseRegNum > 0 ? round(100 * $activeCount / $baseRegNum, 2) : 0;
            $result[] = [
                'game_id' => $baseStats[$key]['game_id'],
                'channel_id' => $baseStats[$key]['channel_id'],
                'base_reg_num' => $baseRegNum,
                'retention_num' => $activeCount,
                'retention_rate' => $retentionRate
            ];
        }
        
        return $result;
    }

    /**
     * 批量计算多种留存率。
     * targetDay 是登录统计日，每项结果通过 stat_day 标记应回写的注册日。
     *
     * @param string $targetDay 登录统计日
     * @param array $gameIds 游戏ID列表
     * @param int $hasChannel 是否按渠道分组
     * @return array
     */
    public function getBatchRetention($targetDay, $gameIds = [], $hasChannel = 1)
    {
        $retentionConfig = [
            'one_stay' => 1,
            'three_stay' => 2,
            'four_stay' => 3,
            'five_stay' => 4,
            'six_stay' => 5,
            'seven_stay' => 6,
            'fifteen_stay' => 14,
            'thirty_stay' => 29,
        ];

        $result = [];
        foreach ($retentionConfig as $fieldName => $dayOffset) {
            $statDay = date('Y-m-d', strtotime($targetDay . " -{$dayOffset} days"));
            $data = $this->getRetentionByRegTime($targetDay, $dayOffset, $gameIds, $hasChannel);

            // 不同留存字段属于不同注册日，不能再按执行日合并到同一行
            foreach ($data as $item) {
                $result[] = [
                    'stat_day' => $statDay,
                    'game_id' => $item['game_id'],
                    'channel_id' => $item['channel_id'],
                    'base_reg_num' => $item['base_reg_num'],
                    'retention_num' => $item['retention_num'],
                    'retention_field' => $fieldName,
                    'retention_rate' => $item['retention_rate'],
                ];
            }
        }
        
        return $result;
    }

    /**
     * 合并统计数据（将多个统计结果按game_id和complex_id合并）
     * @param array $dataList 统计数据列表
     * @return array 合并后的数据
     */
    public function mergeStatsData($dataList)
    {
        $result = [];
        foreach ($dataList as $data) {
            foreach ($data as $item) {
                // 合并键需要包含 game_id、channel_id 和 server_id，以区分不同的区服
                $serverId = $item['server_id'] ?? 0;
                $key = ($item['game_id'] ?? 0) . '_' . ($item['channel_id'] ?? 0) . '_' . $serverId;
                if (!isset($result[$key])) {
                    $result[$key] = [
                        'game_id' => $item['game_id'] ?? 0,
                        'channel_id' => $item['channel_id'] ?? 0,
                        'server_id' => $serverId,
                    ];
                }
                // 合并其他字段
                foreach ($item as $k => $v) {
                    if (!in_array($k, ['game_id', 'channel_id', 'server_id'])) {
                        // reg_total 是累计值，应该取最大值而不是累加
                        if ($k === 'reg_total') {
                            if (isset($result[$key][$k])) {
                                $result[$key][$k] = max($result[$key][$k], $v);
                            } else {
                                $result[$key][$k] = $v;
                            }
                        } elseif ($k === 'reg_num') {
                            // reg_num 表示目标日新增玩家数，不与留存计算中的基准注册数累加
                            if (!isset($result[$key][$k])) {
                                $result[$key][$k] = $v;
                            }
                        } elseif (isset($result[$key][$k])) {
                            // 如果是数值字段，累加；如果是字符串字段（如 server_name），取最后一个非空值
                            if (is_numeric($v) && is_numeric($result[$key][$k])) {
                                $result[$key][$k] += $v;
                            } elseif (!empty($v)) {
                                $result[$key][$k] = $v;
                            }
                        } else {
                            $result[$key][$k] = $v;
                        }
                    }
                }
            }
        }
        return array_values($result);
    }
}