dms-api/app/v1/logic/gameLog/ChannelAnalysisLogic.php

<?php

// 玩家日志逻

namespace app\v1\logic\gameLog;

use app\v1\logic\tool\ToolLogic;
use plugin\saiadmin\basic\BaseLogic;
use plugin\saiadmin\service\OpenSpoutWriter;
use support\think\Db;
use support\Request;

class channelAnalysisLogic extends BaseLogic
{
    // 分时数据
    public function getHourDataList($where){

        $params = $this->searchByAuth($where);
        // hour=>小时, reg_total=>注册数，cost=>消耗，pay_num=>付费总用户数，
        // pay_total=>付费总金额，reg_pay_num=>小时注册当天付费数，reg_pay_total=>小时注册当天付费金额
        // reg_pay_num_rg=>注册累计付费数，reg_pay_total_rg=>注册累计付费金额
        $field = "
            agent_id, thour as hour, 
            SUM(reg_total) as reg_total,
            SUM(cost) as cost,
            SUM(pay_num) as pay_num,
            SUM(pay_total) as pay_total,
            SUM(reg_pay_num) as reg_pay_num,
            SUM(reg_pay_total) as reg_pay_total,
            SUM(reg_pay_num_rg) as reg_pay_num_rg,
            SUM(reg_pay_total_rg) as reg_pay_total_rg";
        $group = 'agent_id,thour';
        $whereSql = $this->generateWhereSql($params);


        // 根据日期，连表查询
        $tableNames = ToolLogic::getMonthlyTableNames('base_total_hour_', $where['reg_date'][0], $where['reg_date'][1]);
        $sqlParts = [];
        foreach($tableNames as $tableName){
            // 安全过滤，避免 SQL 注入
            if (!preg_match('/^base_total_hour_\\d{6}$/', $tableName)) {
                continue;
            }
            $sqlParts[] = "SELECT * FROM {$tableName} WHERE 1=1 {$whereSql}";
        }
        $unionSql = implode(" UNION ALL ", $sqlParts);
        // 外层包裹分页、排序
        $finalSql = "
            SELECT {$field} FROM ( {$unionSql} ) AS all_hour
            GROUP BY {$group}
        ";

        // 按小时数据
        $hourData = Db::connect('db_data_report')->query($finalSql);

        $total=[
            'agent_id'=>'合计'
        ];

        // 计算小时消耗数据的 注册成本、付费率、
        $hourResult = [];
        foreach($hourData as $hourRow){
            $agentId = $hourRow['agent_id'];
            $hourKey = 'h'.intval($hourRow['hour']);

            if (!isset($hourResult[$agentId])) {
                $hourResult[$agentId] = [
                    'agent_id' => $agentId
                ];
            }
            unset($hourRow['agent_id'],$hourRow['hour']);
            // 注册成本
            $hourRow['reg_cost'] = ToolLogic::getRound($hourRow['cost'],$hourRow['reg_total']);
            // 付费率
            $hourRow['pay_rate'] = ToolLogic::getPercent($hourRow['reg_pay_num'],$hourRow['reg_total']);
            // 回本率
            $hourRow['roi'] = ToolLogic::getPercent($hourRow['reg_pay_total'],$hourRow['cost']);


            // 统计数据，每小时的数据

            $total[$hourKey]['reg_total'] = !empty($total[$hourKey]['reg_total']) ? ($total[$hourKey]['reg_total'] + $hourRow['reg_total']) : $hourRow['reg_total'];
            $total[$hourKey]['cost'] = !empty($total[$hourKey]['cost']) ? ($total[$hourKey]['cost'] + $hourRow['cost']) : $hourRow['cost'];
            $total[$hourKey]['reg_pay_total'] = !empty($total[$hourKey]['reg_pay_total']) ? ($total[$hourKey]['reg_pay_total'] + $hourRow['reg_pay_total']) : $hourRow['reg_pay_total'];
            $total[$hourKey]['reg_pay_num'] = !empty($total[$hourKey]['reg_pay_num']) ? ($total[$hourKey]['reg_pay_num'] + $hourRow['reg_pay_num']) : $hourRow['reg_pay_num'];

            $total['total_raw']['reg_total'] = !empty($total['total_raw']['reg_total']) ? ($total['total_raw']['reg_total'] + $hourRow['reg_total']) : $hourRow['reg_total'];
            $total['total_raw']['cost'] = !empty($total['total_raw']['cost']) ? ($total['total_raw']['cost'] + $hourRow['cost']) : $hourRow['cost'];
            $total['total_raw']['reg_pay_total'] = !empty($total['total_raw']['reg_pay_total']) ? ($total['total_raw']['reg_pay_total'] + $hourRow['reg_pay_total']) : $hourRow['reg_pay_total'];
            $total['total_raw']['reg_pay_num'] = !empty($total['total_raw']['reg_pay_num']) ? ($total['total_raw']['reg_pay_num'] + $hourRow['reg_pay_num']) : $hourRow['reg_pay_num'];


            $hourResult[$agentId][$hourKey] = $hourRow;
        }


        // 计算统计的注册成本、付费绿、回本率
        foreach ($total as $key => &$v) {
            if (!is_array($v)) continue; // 跳过 agent_id => 合计 等非数组元素
            $v['reg_cost'] = ToolLogic::getRound($v['cost'], $v['reg_total']);
            $v['pay_rate'] = ToolLogic::getPercent($v['reg_pay_num'], $v['reg_total']);
            $v['roi'] = ToolLogic::getPercent($v['reg_pay_total'], $v['cost']);
        }



        // 计算行汇总
        foreach ($hourResult as &$item){
            $item['total_raw']['cost']     = round(array_sum(array_column($item, 'cost')), 2);
            $item['total_raw']['reg_total']     = round(array_sum(array_column($item, 'reg_total')), 2);
            $item['total_raw']['reg_pay_num']     = round(array_sum(array_column($item, 'reg_pay_num')), 2);
            $item['total_raw']['reg']      = round(array_sum(array_column($item, 'reg')), 2);
            $item['total_raw']['pay_num']  = round(array_sum(array_column($item, 'pay_num')), 2);
            $item['total_raw']['pay']      = round(array_sum(array_column($item, 'pay')), 2);
            $item['total_raw']['reg_cost'] = ToolLogic::getRound($item['total_raw']['cost'], $item['total_raw']['reg'], 1);
            $item['total_raw']['pay_rate'] = ToolLogic::getPercent($item['total_raw']['pay_num'], $item['total_raw']['reg']);
            $item['total_raw']['roi'] = ToolLogic::getPercent($item['total_raw']['pay'], $item['total_raw']['cost']);
        }

        $data = array_values($hourResult);
        array_unshift($data, $total);
        return $data;

    }


    // 留存按日
    public function getActiveDataList($where){

        $params = $this->searchByAuth($where);

        $whereSql = $this->generateWhereSql($params);

        // 1. 注册数据：$regData = 按照日期，获取每日的 注册数【reg_total】分组，base_total_day_2025

        $baseTotalDayTableNames = ToolLogic::getYearlyTableNames('base_total_day_', $params['reg_date'][0],$params['reg_date'][1]);

        $baseTotalDaySqlParts = [];
        foreach ($baseTotalDayTableNames as $tableName){
            // 安全过滤，避免 SQL 注入
            if (!preg_match('/^base_total_day_\\d{4}$/', $tableName)) {
                continue;
            }
            $baseTotalDaySqlParts[] = "SELECT * FROM {$tableName} WHERE 1=1 {$whereSql}";
        }
        $unionBaseTotalDaySql = implode(" UNION ALL ", $baseTotalDaySqlParts);

        // 按照每日，获取=>注册数据
        $regData = Db::connect('db_data_report')->query("SELECT tdate, SUM('reg_total') reg_total FROM ( {$unionBaseTotalDaySql} ) AS all_base_total_day  GROUP BY tdate order by tdate ");


        // 2. 活跃数据：$actData = 按照日期，获取每日的 活跃数 SUM(【active_total】) 活跃天数 【days】, 分组 【reg_date,days】，game_active_day_2025

        $gameActiveDayTableNames = ToolLogic::getYearlyTableNames('game_active_day_', $params['reg_date'][0],$params['reg_date'][1]);

        $gameActiveDaySqlParts = [];

        foreach ($gameActiveDayTableNames as $tableName){
            if (!preg_match('/^game_active_day_\\d{4}$/', $tableName)) {
                continue;
            }
            $gameActiveDaySqlParts[] = "SELECT * FROM {$tableName} WHERE 1=1 {$whereSql}";
        }

        $unionGameActiveDaySql = implode(" UNION ALL ", $gameActiveDaySqlParts);

        $actDataSql = "select reg_date as tdate, days, sum(active_total) as active from ({$unionGameActiveDaySql}) as all_game_active_day group by reg_date,days";
        // 替换日期字段, 因为game_active_day_表的日期字段是[reg_date]
        $actDataSql = str_replace("AND tdate", "AND reg_date", $actDataSql);

        // 日期、活跃天数、活跃数，按日期和活跃天数分组
        $actData = Db::connect('db_data_report')->query($actDataSql);

        $tdate = date('Y-m-d');
        $list = $totalData = [];
        // 遍历每日的注册数
        foreach($regData as $r){
            // 如果当日没有注册的话,则跳过
            if(!$r['reg_total']){
                continue;
            }
            // 注册日期
            $reg_date = $r['tdate'];
            $list[$reg_date]['tdate'] = $reg_date;
            // 注册数
            $list[$reg_date]['reg'] = $r['reg_total'];

            // 从注册日期 ($reg_date) 到当前日期的天数
            $days = (strtotime($tdate)-strtotime($reg_date))/86400;
            for($i=1;$i<=$days;$i++){
                $dayKey  = 'd'.$i;
                // d1...dn的reg = 记录按日的注册数
                $totalData[$dayKey]['reg'] += $r['reg_total'];
            }
            $totalData['reg'] += $r['reg_total'];
        }

        // 遍历每日的活跃数
        foreach($actData as $r){
            // 注册日期
            $reg_date = $r['tdate'];
            // 如果日期，没有注册数/活跃数， 则跳过
            if(!$list[$reg_date]['reg'] || !$r['active']){
                continue;
            }
            // 如果活跃天数超过30天，并且活跃数不在[45,60,90,119,120]数组里面， 则跳过
            if($r['days']>30 && !in_array($r['days'],[45,60,90,119,120])){
                continue;
            }
            // 活跃天数的key
            $dayKey  = 'd'.$r['days'];

            // N日留存率 = 第N天活跃数 ÷ 注册日注册数 × 100%。
            // $list['2025-07-23'][d1]['active']/$list['2025-07-23']['reg']
            // $list['2025-07-23'][d2]['active']/$list['2025-07-23']['reg']
            $list[$reg_date][$dayKey] = ToolLogic::getPercent($r['active'],$list[$reg_date]['reg']);
            // d1...dn的reg = 记录按日的活跃数
            $totalData[$dayKey]['active'] += $r['active'];
        }

        $total=[];
        foreach($totalData as $key=>$val){
            if($key == 'reg'){
                $total['reg'] = $val;
            } else {
                $total[$key] = ToolLogic::getPercent($val['active'],$val['reg']);
            }
        }

        $data = [
            'data'  => array_values($list),
            'totalRow' => $total,
        ];
        return $data;
    }

    // 渠道总览
    public function getAgentDataList($where){
        $params = $this->searchByAuth($where);

        $filter = $params['filter'];
        $group = $params['group'];
        $type = '';

        // 如果分组按照 游戏ID||游戏组
        if($group===1 || $group==4){
            $type = 'game_id';
        }else{
            $type = 'agent_id';
        }

        $whereSql = $this->generateWhereSql($params);

        // 1.


    }




    // 生成wheresql
    public function generateWhereSql($params){
        $startDate = $params['reg_date'][0];
        $endDate = $params['reg_date'][1];
        $whereSql = "";
        if(!empty($params['game_id'])){
            if (is_array($params['game_id'])) {
                $whereSql .= " AND game_id IN(" . implode(',', $params['game_id']) . ")";
            } else {
                $whereSql .= " AND game_id = {$params['game_id']}";
            }
        }
        if(!empty($params['media_id'])){
            $whereSql .= " AND media_id = {$params['media_id']}";
        }
        if(!empty($params['agent_id'])){
            $whereSql .= " AND agent_id = {$params['agent_id']}";
        }
        if(!empty($params['site_id'])){
            if (is_array($params['site_id'])) {
                $whereSql .= " AND site_id IN(" . implode(',', $params['site_id']) . ")";
            } else {
                $whereSql .= " AND site_id = {$params['site_id']}";
            }
        }
        // 负责人
        if(!empty($params['auth_id'])){
            if (is_array($params['auth_id'])) {
                $whereSql .= " AND auth_id IN(" . implode(',', $params['auth_id']) . ")";
            } else {
                $whereSql .= " AND auth_id = {$params['auth_id']}";
            }
        }
        // 注册日期
        if(!empty($params['reg_date'])){
            $whereSql .= " AND tdate BETWEEN '{$startDate}' AND '{$endDate}'";
        }

        return $whereSql;
    }

}