new_sdk_wu/www/new_sdk/application/admin/controller/SummaryV2.php

<?php

namespace app\admin\controller;

use think\Db;

/**
 * 数据统计与展示控制器 V2
 */
class SummaryV2 extends Admin
{
    /**
     * 获取可用的单端游戏列表
     */
    private function getGameList()
    {
        return Db::table('cy_game')
            ->field('id, name, type')
            ->where('cooperation_status', '<>', 0)
            ->order('id desc')
            ->select();
    }

    /**
     * 获取游戏组列表，组 ID 和名称直接取自 nw_game_band
     */
    private function getGameBandList()
    {
        $bandList = Db::table('nw_game_band')
            ->field('id, game_name as name')
            ->order('id desc')
            ->select();

        foreach ($bandList as &$band) {
            $band['name'] = trim($band['name']);
            if ($band['name'] === '') {
                $band['name'] = '未命名游戏组#' . $band['id'];
            }
        }
        unset($band);

        return $bandList;
    }

    /**
     * 获取单端游戏与游戏组列表（用于前台合并下拉选项）
     */
    private function getGameAndBandList()
    {
        return [$this->getGameList(), $this->getGameBandList()];
    }

    /**
     * 解析前端下拉框传来的复合游戏筛选值 (例如 "band_5" 或 "game_10")
     */
    private function parseGameFilter($filterValue)
    {
        $gameId = 0;
        $gameBandId = 0;

        if (!empty($filterValue)) {
            if (strpos($filterValue, 'band_') === 0) {
                $gameBandId = (int)str_replace('band_', '', $filterValue);
            } elseif (strpos($filterValue, 'game_') === 0) {
                $gameId = (int)str_replace('game_', '', $filterValue);
            }
        }

        return [$gameId, $gameBandId];
    }

    /**
     * 校验统计页面的查询时间范围。
     */
    private function validateDateRange($start, $end)
    {
        $latestDate = date('Y-m-d', strtotime('yesterday'));
        if (($start !== '' && strtotime($start) > strtotime($latestDate))
            || ($end !== '' && strtotime($end) > strtotime($latestDate))) {
            $this->error('查询时间：最晚只能选择昨日');
        }

        if ($start !== '' && $end !== '' && strtotime($start) > strtotime($end)) {
            $this->error('查询时间：开始时间 不能大于 结束时间');
        }
    }

    /**
     * 获取统计页面的默认查询时间范围：昨日往前一个月至昨日。
     */
    private function getDefaultDateRange()
    {
        $defaultEndTimestamp = strtotime('yesterday');

        return [
            date('Y-m-d', strtotime('-1 month', $defaultEndTimestamp)),
            date('Y-m-d', $defaultEndTimestamp),
        ];
    }

    /**
     * 1. 数据汇总页面（仅展示游戏组；全部=所有游戏组）
     */
    public function summary()
    {
        $bandList = $this->getGameBandList();
        $bandMap = array_column($bandList, 'name', 'id');

        list($defaultStart, $defaultEnd) = $this->getDefaultDateRange();

        $start = input('get.start', $defaultStart) ?: $defaultStart;
        $end = input('get.end', $defaultEnd) ?: $defaultEnd;
        $gameFilter = input('get.game_filter', '');
        $this->validateDateRange($start, $end);

        // 拼接查询条件：只查游戏组数据，不混入单端游戏
        $where = [];
        $where['stat_date'] = [['>=', $start], ['<=', $end]];
        $where['game_id'] = 0;

        list(, $gameBandId) = $this->parseGameFilter($gameFilter);
        if ($gameBandId > 0) {
            $where['game_band_id'] = $gameBandId;
        } else {
            // 全部 = 所有游戏组
            $where['game_band_id'] = ['>', 0];
        }

        // 分页查询 cy_summary_game_daily 表（日期倒序，最新在前）
        $list = Db::table('cy_summary_game_daily')
            ->where($where)
            ->order(['stat_date' => 'desc', 'game_band_id' => 'asc', 'id' => 'desc'])
            ->paginate(20, false, ['query' => input('get.')])
            ->each(function ($item) use ($bandMap) {
                $item['display_name'] = '[组] ' . (isset($bandMap[$item['game_band_id']]) ? $bandMap[$item['game_band_id']] : '游戏组#' . $item['game_band_id']);
                // 付费率 = 付费玩家 / 活跃玩家
                $item['pay_rate'] = $item['active_user'] > 0 ? round(($item['pay_user'] / $item['active_user']) * 100, 2) : 0.00;
                // ARPU = 实付金额 / 活跃玩家
                $item['arpu'] = $item['active_user'] > 0 ? round($item['pay_amount'] / $item['active_user'], 2) : 0.00;
                // ARPPU = 实付金额 / 付费玩家
                $item['arppu'] = $item['pay_user'] > 0 ? round($item['pay_amount'] / $item['pay_user'], 2) : 0.00;
                return $item;
            });

        // 模板数据绑定
        $this->assign('bandList', $bandList);
        $this->assign('default_start', $defaultStart);
        $this->assign('default_end', $defaultEnd);
        $this->assign('list', $list);
        $this->assign('listTotal', $list->total());
        $this->assign('page', $list->render());

        return $this->fetch('summary');
    }

    /**
     * 2. 游戏留存页面（仅展示游戏组）
     */
    public function retention()
    {
        $bandList = $this->getGameBandList();
        $bandMap = array_column($bandList, 'name', 'id');

        list($defaultStart, $defaultEnd) = $this->getDefaultDateRange();

        $start = input('get.start', $defaultStart) ?: $defaultStart;
        $end = input('get.end', $defaultEnd) ?: $defaultEnd;
        $gameFilter = input('get.game_filter', '');
        $this->validateDateRange($start, $end);

        // 拼接查询条件
        $where = [];
        $where['stat_date'] = [['>=', $start], ['<=', $end]];

        list(, $gameBandId) = $this->parseGameFilter($gameFilter);
        $where['game_id'] = 0;
        if ($gameBandId > 0) {
            $where['game_band_id'] = $gameBandId;
        } else {
            // 默认查询全部游戏组，但不混入单端游戏数据
            $where['game_band_id'] = ['>', 0];
        }

        // 分页查询（日期倒序，最新在前）
        $list = Db::table('cy_summary_game_daily')
            ->where($where)
            ->order(['stat_date' => 'desc', 'game_band_id' => 'asc', 'id' => 'desc'])
            ->paginate(20, false, ['query' => input('get.')])
            ->each(function ($item) use ($bandMap) {
                $item['display_name'] = '[组] ' . (isset($bandMap[$item['game_band_id']]) ? $bandMap[$item['game_band_id']] : '游戏组#' . $item['game_band_id']);
                // N天留存率 = N天留存玩家数 / 新增玩家数
                $item['retention_d1_rate'] = $item['new_user'] > 0 ? round(($item['retention_d1'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d3_rate'] = $item['new_user'] > 0 ? round(($item['retention_d3'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d4_rate'] = $item['new_user'] > 0 ? round(($item['retention_d4'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d5_rate'] = $item['new_user'] > 0 ? round(($item['retention_d5'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d6_rate'] = $item['new_user'] > 0 ? round(($item['retention_d6'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d7_rate'] = $item['new_user'] > 0 ? round(($item['retention_d7'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d15_rate'] = $item['new_user'] > 0 ? round(($item['retention_d15'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d30_rate'] = $item['new_user'] > 0 ? round(($item['retention_d30'] / $item['new_user']) * 100, 2) : 0.00;
                return $item;
            });

        $this->assign('bandList', $bandList);
        $this->assign('default_start', $defaultStart);
        $this->assign('default_end', $defaultEnd);
        $this->assign('list', $list);
        $this->assign('listTotal', $list->total());
        $this->assign('page', $list->render());

        return $this->fetch('retention');
    }

    /**
     * 3. 区服数据页面（仅展示单端游戏筛选，默认全部）
     */
    public function serverSummary()
    {
        $games = $this->getGameList();
        $gameMap = array_column($games, 'name', 'id');

        list($defaultStart, $defaultEnd) = $this->getDefaultDateRange();

        $start = input('get.start', $defaultStart) ?: $defaultStart;
        $end = input('get.end', $defaultEnd) ?: $defaultEnd;
        $gameId = (int)input('get.game_id', 0);
        $this->validateDateRange($start, $end);

        // 获取区服 ID 对应的名称映射 (nw_game_server)
        $servers = Db::table('nw_game_server')->field('id, servername as server_name')->select();
        $serverMap = array_column($servers, 'server_name', 'id');

        // 查询条件
        $where = [];
        $where['stat_date'] = [['>=', $start], ['<=', $end]];
        if ($gameId > 0) {
            $where['game_id'] = $gameId;
        }

        $list = Db::table('cy_summary_server_daily')
            ->where($where)
            ->order(['stat_date' => 'desc', 'game_id' => 'asc', 'server_id' => 'asc'])
            ->paginate(20, false, ['query' => input('get.')])
            ->each(function ($item) use ($serverMap, $gameMap) {
                $item['game_name'] = isset($gameMap[$item['game_id']]) ? $gameMap[$item['game_id']] : '游戏#' . $item['game_id'];
                // 绑定区服名
                $item['server_name'] = isset($serverMap[$item['server_id']]) ? $serverMap[$item['server_id']] : '区服#' . $item['server_id'];
                // 付费率、ARPU、ARPPU 计算
                $item['pay_rate'] = $item['active_user'] > 0 ? round(($item['pay_user'] / $item['active_user']) * 100, 2) : 0.00;
                $item['arpu'] = $item['active_user'] > 0 ? round($item['pay_amount'] / $item['active_user'], 2) : 0.00;
                $item['arppu'] = $item['pay_user'] > 0 ? round($item['pay_amount'] / $item['pay_user'], 2) : 0.00;
                return $item;
            });

        $this->assign('games', $games);
        $this->assign('default_start', $defaultStart);
        $this->assign('default_end', $defaultEnd);
        $this->assign('list', $list);
        $this->assign('listTotal', $list->total());
        $this->assign('page', $list->render());

        return $this->fetch('server_summary');
    }

    /**
     * 4. 区服留存页面（仅展示单端游戏筛选，默认全部）
     */
    public function serverRetention()
    {
        $games = $this->getGameList();
        $gameMap = array_column($games, 'name', 'id');

        list($defaultStart, $defaultEnd) = $this->getDefaultDateRange();

        $start = input('get.start', $defaultStart) ?: $defaultStart;
        $end = input('get.end', $defaultEnd) ?: $defaultEnd;
        $gameId = (int)input('get.game_id', 0);
        $this->validateDateRange($start, $end);

        $servers = Db::table('nw_game_server')->field('id, servername as server_name')->select();
        $serverMap = array_column($servers, 'server_name', 'id');

        $where = [];
        $where['stat_date'] = [['>=', $start], ['<=', $end]];
        if ($gameId > 0) {
            $where['game_id'] = $gameId;
        }

        $list = Db::table('cy_summary_server_daily')
            ->where($where)
            ->order(['stat_date' => 'desc', 'game_id' => 'asc', 'server_id' => 'asc'])
            ->paginate(20, false, ['query' => input('get.')])
            ->each(function ($item) use ($serverMap, $gameMap) {
                $item['game_name'] = isset($gameMap[$item['game_id']]) ? $gameMap[$item['game_id']] : '游戏#' . $item['game_id'];
                $item['server_name'] = isset($serverMap[$item['server_id']]) ? $serverMap[$item['server_id']] : '区服#' . $item['server_id'];
                
                // 区服N天留存率
                $item['retention_d1_rate'] = $item['new_user'] > 0 ? round(($item['retention_d1'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d3_rate'] = $item['new_user'] > 0 ? round(($item['retention_d3'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d4_rate'] = $item['new_user'] > 0 ? round(($item['retention_d4'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d5_rate'] = $item['new_user'] > 0 ? round(($item['retention_d5'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d6_rate'] = $item['new_user'] > 0 ? round(($item['retention_d6'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d7_rate'] = $item['new_user'] > 0 ? round(($item['retention_d7'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d15_rate'] = $item['new_user'] > 0 ? round(($item['retention_d15'] / $item['new_user']) * 100, 2) : 0.00;
                $item['retention_d30_rate'] = $item['new_user'] > 0 ? round(($item['retention_d30'] / $item['new_user']) * 100, 2) : 0.00;
                return $item;
            });

        $this->assign('games', $games);
        $this->assign('default_start', $defaultStart);
        $this->assign('default_end', $defaultEnd);
        $this->assign('list', $list);
        $this->assign('listTotal', $list->total());
        $this->assign('page', $list->render());

        return $this->fetch('server_retention');
    }
}