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

<?php

namespace app\admin\controller;

use think\Db;
use app\common\model\ComplexChannelModel;
use app\common\library\MakeReportGo;

/**
 * 聚合-游戏区服数据控制器
 */
class ComplexSummaryGameServer extends Admin
{
    /**
     * 游戏区服数据列表
     */
    public function index()
    {
        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1], "id desc", "all");
        $gameList = [];
        foreach ($tmpGameList as $game) {
            $gameList[$game['id']] = $game;
        }
        
        $channelList = (new ComplexChannelModel())->getAllByCondition('id,name', ['flag' => 4], 'name asc');
        $channelByIdList = [];
        foreach ($channelList as $channel) {
            $channelByIdList[$channel['id']] = $channel;
        }
        
        // 设置默认时间范围（昨天）
        $defaultDate = date('Y-m-d', strtotime('-1 day'));
        $defaultStart = $defaultDate;
        $defaultEnd = $defaultDate;
        
        $this->assign('gameArr', $tmpGameList);
        $this->assign('game_list', $gameList);
        $this->assign('channel_list', $channelByIdList);
        $this->assign('default_start', $defaultStart);
        $this->assign('default_end', $defaultEnd);

        list($list, $total) = $this->getSummaryList(request()->param());
        
        // 参考Complex.php的方式处理分页数据
        // 确保 $list 是分页对象，如果不是则获取总数
        $listTotal = is_object($list) && method_exists($list, 'total') ? $list->total() : (is_array($list) ? count($list) : 0);
        $listData = is_object($list) && method_exists($list, 'toArray') ? $list->toArray()['data'] : (is_array($list) ? $list : []);
        $pageHtml = is_object($list) && method_exists($list, 'render') ? $list->render() : '';
        
        $this->assign('total', $total);
        $this->assign('list', $listData);
        $this->assign('listTotal', $listTotal); // 传递总数，供视图使用
        $this->assign('page', $pageHtml);

        return $this->fetch();
    }

    /**
     * 获取汇总列表数据
     */
    private function getSummaryList($data)
    {
        $where = [];
        $isDateRange = false;
        $startDate = '';
        $endDate = '';
        
        // 游戏筛选（多选）
        if (isset($data['game_id']) && $data['game_id']) {
            $gameIds = is_array($data['game_id']) ? $data['game_id'] : explode(',', $data['game_id']);
            $where['game_id'] = ['in', $gameIds];
        }
        
        // 渠道筛选
        if (isset($data['complex_id']) && $data['complex_id'] != '') {
            $where['complex_id'] = $data['complex_id'];
        }
   
        // 时间筛选
        if (isset($data['start']) && isset($data['end']) && $data['start'] != '' && $data['end'] != '') {
            $startDate = $data['start'];
            $endDate = $data['end'];
            // 计算日期差
            $startTimestamp = strtotime($startDate);
            $endTimestamp = strtotime($endDate);
            $daysDiff = ($endTimestamp - $startTimestamp) / 86400;
            
            // 如果日期范围大于1天，需要汇总
            if ($daysDiff >= 1) {
                $isDateRange = true;
            }
            
            $where['day'] = [
                ['>=', $data['start']],
                ['<=', $data['end']],
            ];
        } elseif (isset($data['start']) && $data['start'] != '') {
            $where['day'] = ['>=', $data['start']];
        } elseif (isset($data['end']) && $data['end'] != '') {
            $where['day'] = ['<=', $data['end']];
        } else {
            // 默认昨天
            $defaultDate = date('Y-m-d', strtotime('-1 day'));
            $startDate = $defaultDate;
            $endDate = $defaultDate;
            $where['day'] = [
                ['>=', $defaultDate],
                ['<=', $defaultDate],
            ];
            if (!isset($data['start'])) {
                $data['start'] = $defaultDate;
            }
            if (!isset($data['end'])) {
                $data['end'] = $defaultDate;
            }
        }

        $param = input('get.');
        
        // 如果日期范围大于1天，按游戏ID、渠道ID和服务器ID汇总
        if ($isDateRange) {
            // 先获取所有汇总数据
            $allList = Db::table('cy_complex_summary_game_server')
                ->where($where)
                ->field('game_id, complex_id, server_id,
                        max(server_name) as server_name,
                        max(reg_total) as reg_total,
                        sum(reg_num) as reg_num,
                        sum(reg_role_num) as reg_role_num,
                        sum(active_user_num) as active_user_num,
                        sum(pay_money) as pay_money,
                        sum(pay_amount) as pay_amount,
                        sum(pay_user_num) as pay_user_num')
                ->group('game_id, complex_id, server_id')
                ->order('game_id desc, complex_id desc, server_id desc')
                ->select();
            
            // 转换为数组（统一处理，兼容数组和Collection对象）
            $allList = $allList ?: [];
            if (!is_array($allList) && method_exists($allList, 'toArray')) {
                $allList = $allList->toArray();
            }
            
            // 处理汇总数据，设置日期范围
            foreach ($allList as &$item) {
                $item['day'] = $startDate . ' ~ ' . $endDate;

                $activeUser = (int)($item['active_user_num'] ?? 0);
                $payUser = (int)($item['pay_user_num'] ?? 0);
                $payMoney = (float)($item['pay_money'] ?? 0);

                // 计算付费率、ARPU和ARPPU（注意：数据库中存储的值已经乘以了100，所以这里需要除以100）
                $item['pay_rate_num'] = $activeUser > 0 ? round(100 * $payUser / $activeUser, 2) : 0;
                $item['arpu'] = $activeUser > 0 ? round($payMoney / $activeUser, 2) : 0;
                $item['arppu'] = $payUser > 0 ? round($payMoney / $payUser, 2) : 0;
            }
            unset($item);
            
            // 手动分页处理
            $page = intval(input('get.page', 1));
            $page = $page > 0 ? $page : 1;
            $limit = 20;
            $totalCount = count($allList);
            $offset = ($page - 1) * $limit;
            $listData = array_slice($allList, $offset, $limit);
            
            // 创建分页对象，参考Complex.php的方式
            $collection = new \think\Collection($listData);
            $list = \think\paginator\driver\Bootstrap::make($collection, $limit, $page, $totalCount, false, [
                'var_page' => 'page',
                'path' => request()->url(),
                'query' => $param,
            ]);
        } else {
            // 查询列表（原有逻辑）
            $list = Db::table('cy_complex_summary_game_server')
                ->where($where)
                ->order('day desc, game_id desc, server_id desc')
                ->paginate(20, false, ['query' => $param])->each(function($item){
                    $item['pay_rate_num'] = round($item['pay_rate_num']/100, 2);
                    $item['arpu'] = round($item['arpu']/100, 2);
                    $item['arppu'] = round($item['arppu']/100, 2);
                    return $item;
                });
        }

        // 计算汇总
        $total = Db::table('cy_complex_summary_game_server')
            ->where($where)
            ->field('count(*) as total, sum(reg_total) as reg_total, sum(reg_num) as reg_num, sum(reg_role_num) as reg_role_num, sum(pay_money) as pay_money, sum(pay_amount) as pay_amount, sum(pay_user_num) as pay_user_num')
            ->find();

        // 跨日期时，列表中的玩家总数按游戏、渠道、区服取累计最大值，
        // 底部汇总必须使用相同口径，不能把每天的累计值重复相加。
        if ($isDateRange) {
            $total['reg_total'] = array_sum(array_map(function ($item) {
                return (int)($item['reg_total'] ?? 0);
            }, $allList));
        }

        return [$list, $total ?: ['total' => 0, 'reg_total' => 0, 'reg_num' => 0, 'reg_role_num' => 0, 'pay_money' => 0, 'pay_amount' => 0, 'pay_user_num' => 0]];
    }

    /**
     * 导出数据
     */
    public function export()
    {
        if (request()->isPost()) {
            $data = input('post.');
            $where = [];
            $isDateRange = false;
            $startDate = '';
            $endDate = '';
            
            // 游戏筛选（多选）
            if (isset($data['game_id']) && $data['game_id']) {
                $gameIds = is_array($data['game_id']) ? $data['game_id'] : explode(',', $data['game_id']);
                $where['game_id'] = ['in', $gameIds];
            }
            
            // 渠道筛选
            if (isset($data['complex_id']) && $data['complex_id'] != '') {
                $where['complex_id'] = $data['complex_id'];
            }

            // 时间筛选
            if (isset($data['start']) && isset($data['end']) && $data['start'] != '' && $data['end'] != '') {
                $startDate = $data['start'];
                $endDate = $data['end'];
                // 计算日期差
                $startTimestamp = strtotime($startDate);
                $endTimestamp = strtotime($endDate);
                $daysDiff = ($endTimestamp - $startTimestamp) / 86400;
                
                // 如果日期范围大于1天，需要汇总
                if ($daysDiff >= 1) {
                    $isDateRange = true;
                }
                
                $where['day'] = [
                    ['>=', $data['start']],
                    ['<=', $data['end']],
                ];
            } elseif (isset($data['start']) && $data['start'] != '') {
                $where['day'] = ['>=', $data['start']];
            } elseif (isset($data['end']) && $data['end'] != '') {
                $where['day'] = ['<=', $data['end']];
            } else {
                // 默认昨天
                $defaultDate = date('Y-m-d', strtotime('-1 day'));
                $startDate = $defaultDate;
                $endDate = $defaultDate;
                $where['day'] = [
                    ['>=', $defaultDate],
                    ['<=', $defaultDate],
                ];
            }
            
            // 构建查询SQL
            $query = Db::table('cy_complex_summary_game_server');
            
            // 如果日期范围大于1天，按游戏ID、渠道ID和服务器ID汇总
            if ($isDateRange) {
                $sql = $query
                    ->where($where)
                    ->field('game_id, complex_id, server_id,
                            max(server_name) as server_name,
                            max(reg_total) as reg_total,
                            sum(reg_num) as reg_num,
                            sum(reg_role_num) as reg_role_num,
                            sum(active_user_num) as active_user_num,
                            sum(pay_money) as pay_money,
                            sum(pay_user_num) as pay_user_num,
                            ROUND(CASE WHEN sum(active_user_num) = 0 THEN 0 ELSE 100 * sum(pay_user_num) / sum(active_user_num) END, 2) as pay_rate_num,
                            ROUND(CASE WHEN sum(active_user_num) = 0 THEN 0 ELSE sum(pay_money) / sum(active_user_num) END, 2) as arpu,
                            ROUND(CASE WHEN sum(pay_user_num) = 0 THEN 0 ELSE sum(pay_money) / sum(pay_user_num) END, 2) as arppu')
                    ->group('game_id, complex_id, server_id')
                    ->order('game_id desc, complex_id desc, server_id desc')
                    ->fetchSql(true)
                    ->select();
            } else {
                $sql = $query
                    ->where($where)
                    ->field('*')
                    ->order('day desc, game_id desc, server_id desc')
                    ->fetchSql(true)
                    ->select();
            }
            
            // 扩展参数，用于Go脚本处理
            $extend = [
                'is_date_range' => $isDateRange,
                'start_date' => $startDate,
                'end_date' => $endDate,
                'task_type' => 'complexSummaryGameServerExport',
                'file_name' => "渠道-游戏区服数据_%s.csv",
                'headers' => [
                    ['field' => 'day',           'title' => '日期'],
                    ['field' => 'game_id',     'title' => '游戏名称'],
                    ['field' => 'complex_id',  'title' => '渠道名称'],
                    ['field' => 'server_id',   'title' => '区服名称'],
                    ['field' => 'reg_total',     'title' => '累计注册'],
                    ['field' => 'reg_num',       'title' => '新增玩家'],
                    ['field' => 'reg_role_num',  'title' => '新增角色'],
                    ['field' => 'active_user_num','title' => '活跃玩家'],
                    ['field' => 'pay_money',     'title' => '充值金额'],
                    ['field' => 'pay_user_num',  'title' => '付费人数'],
                    ['field' => 'pay_rate_num',  'title' => '付费率(%)'],
                    ['field' => 'arpu',          'title' => 'ARPU'],
                    ['field' => 'arppu',         'title' => 'ARPPU'],
                ],
                // 字段处理
                'handle_fields' => [
                    [
                        'field' => 'game_id',
                        'handler' => 'mapGameName',
                        'target_field' => 'game_name',
                        'extra' => [
                            'table_name' => 'cy_game',
                            'field_key' => 'name',
                            'where_key' => 'id',
                        ],
                    ],
                    [
                        'field' => 'complex_id',
                        'handler' => 'mapComplexName',
                        'target_field' => 'complex_name',
                        'extra' => [
                            'table_name' => 'nw_complex_channel',
                            'field_key' => 'name',
                            'where_key' => 'id',
                        ],
                    ],
                    [
                        'field' => 'server_id',
                        'handler' => 'mapServerName',
                        'target_field' => 'server_name',
                        'extra' => [
                            'table_name' => 'nw_complex_server',
                            'field_key' => 'server_name',
                            'where_key' => 'id',
                        ],
                    ],
                ],
            ];
            
            if ((new MakeReportGo())->addTask('admin.complexSummary', $sql, session_id(), $extend)) {
                $this->jsonResult('', 1, '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            } else {
                $this->jsonResult('', 0, '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
            }
        }
    }
}