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

<?php

namespace app\crontab;

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

/**
 * 聚合-游戏区服数据-统计脚本
 * 统计游戏区服数据汇总
 */
class ComplexSummaryGameServer extends Command
{
    protected function configure()
    {
        $this->setName('ComplexSummaryGameServer')
            ->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}");

        try {
            // 1. 获取玩家总数（累计到当天之前）
            $memberTotalList = $this->getMemberTotalByServer($day, $service);
            $output->writeln("玩家总数统计完成: " . count($memberTotalList) . " 条");

            // 2. 获取新增玩家（按区服分组）
            $regNumList = $this->getRegNumByServer($day, $service);
            $output->writeln("新增玩家统计完成: " . count($regNumList) . " 条");

            // 3. 获取新增角色（按区服分组）
            $regRoleNumList = $service->getRegRoleNum($day, [], 1, 1);
            $output->writeln("新增角色统计完成: " . count($regRoleNumList) . " 条");

            // 4. 获取活跃玩家（按区服分组）
            $activeUserList = $service->getActiveUserByActiveLog($day, [], 1, 1);
            $output->writeln("活跃玩家统计完成: " . count($activeUserList) . " 条");

            // 5. 获取 订单金额+实付金额（按区服分组）
            $payMoneyList = $this->getPayMoneyByServer($day, $service);
            $output->writeln("充值金额统计完成: " . count($payMoneyList) . " 条");

            // 6. 获取付费人数（按区服分组）
            $payUserNumList = $this->getPayUserNumByServer($day, $service);
            $output->writeln("付费人数统计完成: " . count($payUserNumList) . " 条");
            
            // 7. 合并所有统计数据
            $allStats = $service->mergeStatsData([
                $memberTotalList,
                $regNumList,
                $regRoleNumList,
                $activeUserList,
                $payMoneyList,
                $payUserNumList
            ]);
            // dump(json_encode($allStats));

            // 8. 计算付费率、ARPU和ARPPU，并保存数据
            // 先清空当天日期的数据，避免重新抓取时数据错乱（在事务外执行，确保清空操作生效）
            Db::table('cy_complex_summary_game_server')
                ->where('day', $day)
                ->delete();
            
            Db::startTrans();
            try {
                foreach ($allStats as $stat) {
                    $gameId = $stat['game_id'] ?? 0;
                    $complexId = $stat['channel_id'] ?? 0;
                    $serverId = $stat['server_id'] ?? 0;
                    $serverName = $stat['server_name'] ?? '';
                    
                    // 过滤掉 game_id 或 server_id 为 0 或 NULL 的无效记录
                    if (empty($gameId) || empty($serverId)) {
                        continue;
                    }
                    
                    // 计算付费率、ARPU和ARPPU
                    $payMoney = floatval($stat['pay_money'] ?? 0);
                    $payAmount = floatval($stat['pay_amount'] ?? 0);
                    $activeUserNum = intval($stat['active_user_num'] ?? 0);
                    $payUserNum = intval($stat['pay_user_num'] ?? 0);
                    
                    $payRate = $activeUserNum > 0 ? round(100 * $payUserNum / $activeUserNum, 2)*100 : 0;
                    $arpu = $activeUserNum > 0 ? round($payMoney / $activeUserNum, 2)*100 : 0;
                    $arppu = $payUserNum > 0 ? round($payMoney / $payUserNum, 2)*100 : 0;
                    
                    // 准备保存的数据
                    $saveData = [
                        'day' => $day,
                        'game_id' => $gameId,
                        'complex_id' => $complexId,
                        'server_id' => $serverId,
                        'server_name' => $serverName,
                        'reg_total' => intval($stat['reg_total'] ?? 0),
                        'reg_num' => intval($stat['reg_num'] ?? 0),
                        'reg_role_num' => intval($stat['reg_role_num'] ?? 0),
                        'active_user_num' => $activeUserNum,
                        'pay_money' => $payMoney,
                        'pay_amount' => $payAmount,
                        'pay_user_num' => $payUserNum,
                        'pay_rate_num' => $payRate,
                        'arpu' => $arpu,
                        'arppu' => $arppu,
                    ];
                    // Log::write("## ComplexSummaryGameServer: ".json_encode($saveData),'notice');
                    
                    
                    // 直接插入（已清空当天数据，无需检查是否存在）
                    Db::table('cy_complex_summary_game_server')->insert($saveData);
                    
                    // 以下代码已注释，防止以后需要时可以恢复
                    // // 检查记录是否存在
                    // $exists = Db::table('cy_complex_summary_game_server')
                    //     ->where([
                    //         'day' => $day,
                    //         'game_id' => $gameId,
                    //         'complex_id' => $complexId,
                    //         'server_id' => $serverId
                    //     ])
                    //     ->find();
                    // 
                    // if ($exists) {
                    //     // 更新
                    //     Db::table('cy_complex_summary_game_server')
                    //         ->where(['id' => $exists['id']])
                    //         ->update($saveData);
                    // } else {
                    //     // 插入
                    //     Db::table('cy_complex_summary_game_server')->insert($saveData);
                    // }
                }
                
                Db::commit();
                $output->writeln("数据保存成功，共处理 " . count($allStats) . " 条记录");
                
            } 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 getMemberTotalByServer($day, $service)
    {
        $gameIds = $service->getOnlineGameIds();
        if (empty($gameIds)) {
            return [];
        }
        $endTime = strtotime($day . ' 23:59:59');

        // 直接使用角色表统计累计玩家总数（按区服分组）
        $result = Db::table('nw_complex_role')
            ->where('create_time', '<', $endTime)
            ->where('game_id', 'in', $gameIds)
            ->whereNotNull('server_id')  // 过滤掉 server_id 为 NULL 的记录
            ->field('game_id, complex_id as channel_id, server_id,
                     COUNT(DISTINCT member_id) as reg_total')
            ->group('game_id, complex_id, server_id')
            // ->fetchSql(true)
            ->select();

        // dump($result);

        // 转换为数组
        $result = $result ?: [];
        if (!is_array($result) && method_exists($result, 'toArray')) {
            $result = $result->toArray();
        }
        
        if (empty($result)) {
            return [];
        }
        
        // 收集所有的 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;
    }

    /**
     * 获取新增玩家（按区服分组）
     */
    private function getRegNumByServer($day, $service)
    {
        $timeRange = $service->buildTimeRange($day);
        list($startTime, $endTime) = $timeRange;
        $gameIds = $service->getOnlineGameIds();
        if (empty($gameIds)) {
            return [];
        }

        $result = Db::table('nw_complex_role')
            ->whereBetween('create_time', [$startTime, $endTime])
            ->where('game_id', 'in', $gameIds)
            // ->whereNotNull('r.server_id')  // 过滤掉没有角色信息的记录，避免 server_id 为 NULL
            ->field('game_id, complex_id as channel_id, server_id, COUNT(DISTINCT member_id) as reg_num')
            ->group('game_id, complex_id, server_id')
            ->select();

        // 转换为数组
        $result = $result ?: [];
        if (!is_array($result) && method_exists($result, 'toArray')) {
            $result = $result->toArray();
        }
        
        if (empty($result)) {
            return [];
        }
        
        // 收集所有的 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);

        // dump($result);
        return $result;
    }

    /**
     * 获取充值金额（按区服分组）
     */
    private function getPayMoneyByServer($day, $service)
    {
        $timeRange = $service->buildTimeRange($day);
        list($startTime, $endTime) = $timeRange;
        $gameIds = $service->getOnlineGameIds();
        if (empty($gameIds)) {
            return [];
        }
        
        // 根据订单的渠道、游戏和业务区服ID关联区服表，汇总表统一保存区服表内部ID。
        // 区服表可能存在相同业务区服的重复记录，这里固定取最小ID，避免关联后放大充值金额。
        $result = Db::table('nw_complex_pay')->alias('p')
            ->join(
                'nw_complex_server s',
                's.id = (
                    SELECT MIN(s2.id)
                    FROM nw_complex_server s2
                    WHERE s2.complex_id = p.channel_id
                      AND s2.game_id = p.gameid
                      AND s2.server_id = p.serverid
                )',
                'inner'
            )
            ->whereBetween('p.create_time', [$startTime, $endTime])
            ->where('p.status', 1)
            ->where('p.gameid', 'in', $gameIds)
            ->whereNotNull('p.gameid')
            ->whereNotNull('p.serverid')
            ->field('s.game_id, s.complex_id as channel_id, s.id as server_id,
                     SUM(p.amount) as pay_money,
                     SUM(p.pay_amount) as pay_amount,
                     s.server_name')
                     // COUNT(DISTINCT p.userid) as pay_user_num,
            ->group('s.game_id, s.complex_id, s.id, s.server_name')
            ->select();

        return $result ?: [];
    }

    /**
     * 获取付费人数（按区服分组）
     */
    private function getPayUserNumByServer($day, $service)
    {
        $timeRange = $service->buildTimeRange($day);
        list($startTime, $endTime) = $timeRange;
        $gameIds = $service->getOnlineGameIds();
        if (empty($gameIds)) {
            return [];
        }
        
        // 先获取当天有充值的用户ID
        $payUserIds = Db::table('nw_complex_pay')
            ->whereBetween('create_time', [$startTime, $endTime])
            ->where('status', 1)
            ->column('DISTINCT userid');
        
        if (empty($payUserIds)) {
            return [];
        }
        
        // 通过用户表和角色表关联获取区服信息
        $result = Db::table('nw_complex_members')
            ->alias('m')
            ->join('nw_complex_role r', 'm.id = r.member_id', 'left')
            ->where('m.id', 'in', $payUserIds)
            ->where('m.total_pay_amount', '>', 0)
            ->where('r.game_id', 'in', $gameIds)
            ->whereNotNull('r.game_id')  // 明确过滤掉没有角色信息的记录
            ->whereNotNull('r.server_id')  // 过滤掉没有区服信息的记录，避免 server_id 为 NULL
            ->field('r.game_id, r.complex_id as channel_id, r.server_id,
                     COUNT(DISTINCT m.id) as pay_user_num,
                     (SELECT server_name FROM nw_complex_server WHERE id = r.server_id LIMIT 1) as server_name')
            ->group('r.game_id, r.complex_id, r.server_id')
            ->select();
        
        return $result ?: [];
    }
}