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

<?php

namespace app\admin\controller;

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

/**
 * 聚合-游戏留存报表控制器
 */
class ComplexSummaryGameRetention 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汇总
        if ($isDateRange) {
            // 先获取所有汇总数据
            $allList = Db::table('cy_complex_summary_game_retention')
                ->where($where)
                ->fieldRaw("
                    game_id,
                    complex_id,
                    sum(reg_num) as reg_num,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(one_stay * reg_num) / sum(reg_num) END, 2) as one_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(three_stay * reg_num) / sum(reg_num) END, 2) as three_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(four_stay * reg_num) / sum(reg_num) END, 2) as four_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(five_stay * reg_num) / sum(reg_num) END, 2) as five_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(six_stay * reg_num) / sum(reg_num) END, 2) as six_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(seven_stay * reg_num) / sum(reg_num) END, 2) as seven_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(fifteen_stay * reg_num) / sum(reg_num) END, 2) as fifteen_stay,
                    ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(thirty_stay * reg_num) / sum(reg_num) END, 2) as thirty_stay
                ")
                ->group('game_id')
                ->order('game_id desc desc')
                ->select();
            
            // 转换为数组（统一处理，兼容数组和Collection对象）
            $allList = $allList ?: [];
            if (!is_array($allList) && method_exists($allList, 'toArray')) {
                $allList = $allList->toArray();
            }
            
            // 处理汇总数据，设置日期范围
            foreach ($allList as &$item) {
                $item['day'] = $startDate . ' ~ ' . $endDate;
            }
            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_retention')
                ->where($where)
                ->group('game_id')
                ->order('day desc, game_id desc')
                ->paginate(20, false, ['query' => $param]);
        }

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

        return [$list, $total ?: ['total' => 0, 'reg_total' => 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_retention');
            
            // 如果日期范围大于1天，按游戏ID和渠道ID汇总
            if ($isDateRange) {
                $sql = $query
                    ->where($where)
                    ->fieldRaw("
                        game_id,
                        complex_id,
                        sum(reg_num) as reg_num,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(one_stay * reg_num) / sum(reg_num) END, 2) as one_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(three_stay * reg_num) / sum(reg_num) END, 2) as three_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(four_stay * reg_num) / sum(reg_num) END, 2) as four_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(five_stay * reg_num) / sum(reg_num) END, 2) as five_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(six_stay * reg_num) / sum(reg_num) END, 2) as six_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(seven_stay * reg_num) / sum(reg_num) END, 2) as seven_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(fifteen_stay * reg_num) / sum(reg_num) END, 2) as fifteen_stay,
                        ROUND(CASE WHEN sum(reg_num) = 0 THEN 0 ELSE sum(thirty_stay * reg_num) / sum(reg_num) END, 2) as thirty_stay
                    ")
                    ->group('game_id, complex_id')
                    ->order('game_id desc, complex_id desc')
                    ->fetchSql(true)
                    ->select();
            } else {
                $sql = $query
                    ->where($where)
                    ->field('*')
                    ->order('day desc, game_id desc')
                    ->fetchSql(true)
                    ->select();
            }
            
            // 扩展参数，用于Go脚本处理
            $extend = [
                'is_date_range' => $isDateRange,
                'start_date' => $startDate,
                'end_date' => $endDate,
                'task_type' => 'complexSummaryGameRetentionExport',
                'file_name' => "渠道-游戏留存报表_%s.csv",
                'headers' => [
                    ['field' => 'day',           'title' => '日期'],
                    ['field' => 'game_id',     'title' => '游戏名称'],
                    ['field' => 'complex_id',  'title' => '渠道名称'],
                    ['field' => 'reg_num',       'title' => '新增玩家'],
                    ['field' => 'one_stay',      'title' => '次日留存(%)'],
                    ['field' => 'three_stay',    'title' => '3日留存(%)'],
                    ['field' => 'four_stay',     'title' => '4日留存(%)'],
                    ['field' => 'five_stay',     'title' => '5日留存(%)'],
                    ['field' => 'six_stay',      'title' => '6日留存(%)'],
                    ['field' => 'seven_stay',    'title' => '7日留存(%)'],
                    ['field' => 'fifteen_stay',  'title' => '15日留存(%)'],
                    ['field' => 'thirty_stay',   'title' => '30日留存(%)'],
                ],
                // 字段处理
                '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',
                        ],
                    ],
                ],
            ];
            
            if ((new MakeReportGo())->addTask('admin.complexSummary', $sql, session_id(), $extend)) {
                $this->jsonResult('', 1, '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            } else {
                $this->jsonResult('', 0, '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
            }
        }
    }
}