new_sdk_wu/www/new_sdk/application/crontab/ComplexSummaryGameRetention.php

<?php

namespace app\crontab;

use app\service\ComplexSummaryService;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\console\Input\Argument;
use think\Db;
use think\Exception;

/**
 * 聚合-游戏留存报表-统计脚本
 * 统计游戏留存数据
 */
class ComplexSummaryGameRetention extends Command
{
    protected function configure()
    {
        $this->setName('ComplexSummaryGameRetention')
            ->setDescription('聚合-游戏留存报表-统计脚本')
            ->addArgument('day', Argument::OPTIONAL, '统计日期，格式：Y-m-d，默认为昨天');
    }

    protected function execute(Input $input, Output $output)
    {
        $service = new ComplexSummaryService();
        
        // 获取统计日期（默认昨天）
        $day = $input->getArgument('day') ?: date('Y-m-d', strtotime('-1 day'));

        $output->writeln(date('Y-m-d H:i:s') . " 开始统计日期: {$day}");

        $gameIds = $service->getOnlineGameIds();

        try {
            // 1. 获取新增玩家（当天注册）
            $regNumList = $service->getRegNum($day, $gameIds, 1);
            $output->writeln("新增玩家统计完成: " . count($regNumList) . " 条");
            
            // 2. 批量计算所有留存率
            $retentionList = $service->getBatchRetention($day, $gameIds, 1);
            $output->writeln("留存率统计完成: " . count($retentionList) . " 条");
            
            // 3. 分别保存当天新增和历史注册日留存
            Db::startTrans();
            try {
                // 重跑当天数据时只重置新增人数，保留后续日期已经补齐的留存字段
                $regResetQuery = Db::table('cy_complex_summary_game_retention')
                    ->where('day', $day);
                if (!empty($gameIds)) {
                    $regResetQuery->where('game_id', 'in', $gameIds);
                }
                $regResetQuery->update(['reg_num' => 0]);

                foreach ($regNumList as $stat) {
                    $gameId = $stat['game_id'] ?? 0;
                    $complexId = $stat['channel_id'] ?? 0;

                    $this->saveSummaryData($day, $gameId, $complexId, [
                        'reg_num' => intval($stat['reg_num'] ?? 0),
                    ]);
                }

                $retentionFields = [
                    'one_stay',
                    'three_stay',
                    'four_stay',
                    'five_stay',
                    'six_stay',
                    'seven_stay',
                    'fifteen_stay',
                    'thirty_stay',
                ];

                foreach ($retentionList as $stat) {
                    $fieldName = $stat['retention_field'] ?? '';
                    if (!in_array($fieldName, $retentionFields, true)) {
                        throw new Exception('无效的留存字段: ' . $fieldName);
                    }

                    $statDay = $stat['stat_day'] ?? '';
                    $gameId = $stat['game_id'] ?? 0;
                    $complexId = $stat['channel_id'] ?? 0;

                    // 留存率回写到对应注册日；同时校准该注册日的新增人数
                    $this->saveSummaryData($statDay, $gameId, $complexId, [
                        'reg_num' => intval($stat['base_reg_num'] ?? 0),
                        $fieldName => floatval($stat['retention_rate'] ?? 0),
                    ]);
                }
                
                Db::commit();
                $output->writeln(
                    "数据保存成功，新增 " . count($regNumList)
                    . " 条，留存 " . count($retentionList) . " 条"
                );
                
            } catch (Exception $e) {
                Db::rollback();
                $output->writeln("保存数据失败: " . $e->getMessage());
                throw $e;
            }
            
        } catch (Exception $e) {
            $output->writeln("统计失败: " . $e->getMessage() . ' - ' . $e->getFile() . ":" . $e->getLine());
            throw $e;
        }
        
        $output->writeln(date('Y-m-d H:i:s') . " 统计完成");
    }

    /**
     * 按注册日、游戏和渠道更新汇总数据；不存在时创建。
     */
    private function saveSummaryData($day, $gameId, $complexId, array $data)
    {
        $where = [
            'day' => $day,
            'game_id' => intval($gameId),
            'complex_id' => intval($complexId),
        ];

        $exists = Db::table('cy_complex_summary_game_retention')
            ->where($where)
            ->field('id')
            ->find();

        if ($exists) {
            Db::table('cy_complex_summary_game_retention')
                ->where('id', $exists['id'])
                ->update($data);
            return;
        }

        Db::table('cy_complex_summary_game_retention')
            ->insert(array_merge($where, $data));
    }
}