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;
    }

    /**
     * @param $where
     * @return mixed
     * @description 渠道总览
     * 支出-> media_cost表的消耗数据
     * 激活(install)、注册设备(reg_dev)、注册数(reg_total)、创角数(role_create_user) -> base_total_day_2025表的数据
     * 注册成本-> 支出/注册数
     * 创角率 ->  创角数/注册数
     * 次留数 ->  reg_date, days:1, active_total,  game_active_day_2025
     * 次留率 ->  次留数 / reg_login_total
     * 次留成本 -> 支出 / 第二天的 ld_login_total
     * 当天付费金额   -> reg_pay_total
     * 总付费人数    ->  pay_num
     * 总付费金额    ->  pay_total
     * 付费率       ->  总付费人数 / 注册数
     * 当天回本率    ->  当天付费 / 支出
     * 累计回本率    ->  总付费 / 支出
     * 实际回本率    ->  game_reg_pay_day_2025表的 addup_pay_amount / 支出
     * 7天实际回本率  ->  game_reg_pay_day_2025表的 sum(addup_pay_amount) 根据注册时间 +6day, 遍历相加 / 支出
     * 付费成本      ->  总付费人数 / 支出
     * ARPU        ->   付费总金额/ 付费总用户
     * 注册ARPU    ->   注册付费金额/注册数
     * 老用户数    ->    sum(old_login_total)
     * 负责人      ->   渠道负责人, 根据
     */
    public function getAgentDataList($where){
        $params = $this->searchByAuth($where);
        // 忽略0投入
        $igz = !empty($params['filter']) ? $params['filter'].include('igz'):false;

//        $filter = !empty($params['filter']) ? $params['filter'] : '';
        $group = !empty($params['group']) ?$params['group']:'';


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

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


        // 1.查询消耗数据, 安game_id/agent_id分组
        $costSql = "select {$type}, sum(money) as cost from media_cost WHERE 1=1 {$whereSql} group by {$type}";
        $costData = Db::connect('db_advert')->query($costSql);
        $costDataColumn = array_column($costData, 'cost', $type);

        // 2.查询按日统计数据
        $totalDayField = "
            {$type},
            sum(install) as install,
            sum(reg_pay_total) as reg_pay_total_da,
            sum(reg_dev) as reg_dev,
            sum(reg_total) as reg_total,
            sum(login_total) as login_total,
            sum(old_login_total) as old_login_total,
            sum(role_create_user) as role_total,
            sum(pay_num) as pay_num_total
        ";




        // 2. 获取基础报表数据，兼容跨年
        $baseData = $this->generateYearUnionList('base_total_day_',$params['reg_date'], $whereSql, $totalDayField, $type);



        // 3. 获取活跃数
        $activeWhereSql = $whereSql.' AND days = 1';
        $activeWhereSql = str_replace('AND tdate','AND reg_date',$activeWhereSql);
        $activeField = "
            sum(active_total) as active,
            game_id,
            agent_id";
        $activeData = $this->generateYearUnionList('game_active_day_', $params['reg_date'], $activeWhereSql, $activeField, $type);


        // 4. 注册充值统计
        $regPayWhereSql = $whereSql;
        $regPayField = "
            sum(addup_pay_num) as reg_pay_num,
            sum(addup_pay_total) as reg_pay_total,
            sum(addup_pay_amount) as reg_pay_amount,
            {$type}";
        $regPayWhereSql = str_replace('AND tdate','AND reg_date',$regPayWhereSql);

        $regPayData = $this->generateYearUnionList('game_reg_pay_day_', $params['reg_date'], $regPayWhereSql, $regPayField, $type);


        // 5. 7天回本
        $hb7dayData = $this->computedHb7days($params,$type);
        $hb7dayData = array_column($hb7dayData, 'reg_pay_amount_7', $type);

        echo '$hb7dayData';
        print_r($hb7dayData);
        if(empty($baseData)||!empty($baseData[0]['game_id'])){
            return [];
        }



        // 拼接总数据
        foreach($baseData as &$row){
            $typeId = $row[$type]; // type， 的取值， 这里根据分组， game_id | agent_id
            $cost = $costDataColumn[$typeId];

            $row['cost'] = $cost;
            // 注册成本
            $row['reg_cost'] = ToolLogic::getRound($cost,$row['reg_total'] ?? 0);
            // 创角率
            $row['role_per'] = ToolLogic::getRound($row['role_total'],$row['reg_total'] ?? 0);
            // 次留数
            $row['active'] = $activeData[$typeId]['active'] ?? 0;
            // 次留率
            $row['a_per'] = ToolLogic::getPercent( $activeData[$typeId]['active']??0,$row['reg_total'],1);
            // 次留成本
            $row['a_cost']  = ToolLogic::getRound($cost,$row['active'],1);

            // 总付费金额
            $row['reg_pay_total'] = $regPayData[$typeId]['reg_pay_total'] ?? 0;

            // 总付费人数
            $row['reg_pay_num'] = $regPayData[$typeId]['reg_pay_num'] ?? 0;

            //付费成本
            $row['p_cost']  = ToolLogic::getRound($row['cost'],$row['reg_pay_num']);

            // 付费率
            $row['p_per'] = ToolLogic::getPercent($row['reg_pay_num']??0,$row['reg_total'],1);

            // 当天回本率
            $row['da_per'] = ToolLogic::getPercent($row['reg_pay_total_da']??0,$row['cost'],1);

            // 累计回本率
            $row['re_per']  = (float)trim(ToolLogic::getPercent($row['reg_pay_total'],$row['cost']), "%");

            // 实际回本率
            $row['re_amount_per'] = ToolLogic::getPercent($row['reg_pay_amount']??0,$row['cost'],1);

            //ARPU
            $row['arpu']    = ToolLogic::getRound($row['reg_pay_total'],$row['reg_pay_num']);

            //注册ARPU
            $row['r_arpu']  = ToolLogic::getRound($row['reg_pay_total'],$row['reg_total']);

            // 7天回本率
            $row['re_amount_per_7'] = ToolLogic::getPercent($row['re_amount_per_7']??0,$row['cost'],1);

        }
        return $baseData;
    }


    /**
     * @param $where
     * @param $group
     * @return void
     * @description  计算7天回本率
     * 1. 7天的注册总充值计算出来，可以通过
     */
    public function computedHb7days($where, $type){
        $payDataAll = [];
        $dateRange = ToolLogic::getDatesBetween($where['reg_date'][0], $where['reg_date'][1]);
        foreach ($dateRange as $regDate){

            $field = "
            sum(addup_pay_amount) as reg_pay_amount_7,
            $type"; // 注册总充值
            $eDate = date("Y-m-d", strtotime($regDate . " +6 days"));
            // 如果7天后的时间，超过今天，则今天是最后一天
            if($eDate>date("Y-m-d")){
                $eDate = date("Y-m-d");
            }

            $wherePay = $where;
            unset($wherePay['reg_date']);

            $whereSql = $this->generateWhereSql($wherePay);
            $whereSql = "{$whereSql} AND reg_date = '{$regDate}' AND pay_date = '{$eDate}'";



            $payData = $this->generateYearUnionList('game_reg_pay_day_',
                 [
                    $regDate,
                    $regDate
                ]
            , $whereSql, $field, $type);

            $payDataAll = array_merge($payDataAll, $payData);
        }

        return $payDataAll;
    }


    // 生成基础日统计表的联合查询列表
    public function generateYearUnionList($namePrefix, $regDate,$whereSql='',$field='*',$group=null){

        $tableNames = ToolLogic::getYearlyTableNames($namePrefix, $regDate[0],$regDate[1]);
        $sqlParts = [];
        foreach ($tableNames as $tableName){
            $sqlParts[] = "SELECT * FROM {$tableName} WHERE 1=1 {$whereSql}";
        }
        $unionSql = implode(" UNION ALL ", $sqlParts);
        $finalSql = "
            SELECT {$field} FROM ( {$unionSql} ) AS all_total_day
        ";
        if (!empty($group)) {
            $finalSql .= " GROUP BY {$group}";
        }
echo $finalSql;
        $baseData = Db::connect('db_data_report')->query($finalSql);
        return $baseData;
    }


    // 生成wheresql
    public function generateWhereSql($params){

        $whereSql = "";
        // 游戏id
        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']}";
            }
        }
        // 媒体id
        if(!empty($params['media_id'])){
            $whereSql .= " AND media_id = {$params['media_id']}";
        }
        // 渠道id
        if(!empty($params['agent_id'])){
            $whereSql .= " AND agent_id = {$params['agent_id']}";
        }
        // 广告位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'])??null){
            $whereSql .= " AND tdate BETWEEN '{$params['reg_date'][0]}' AND '{$params['reg_date'][1]}'";
        }

        // 忽略后台录入
        if(!empty($params['filter'])){
            if(is_array($params['filter'] && $params['filter'].include('fak'))){
                $whereSql .= " AND add_type = 0";
            }
        }

        return $whereSql;
    }

}