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){


        // 合并表 union all
        $params = $this->searchByAuth($where);


        $orderBy =   $where['orderBy'] ?? 'id';
        $orderType = $where['orderType'] ?? 'DESC';
        $orderBy = $orderBy . ' ' . $orderType;

        // 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}
            ORDER BY {$orderBy}
        ";
        // 按小时数据
        $hourData = Db::connect('db_data_report')->query($finalSql);

        // 按天消耗数据,查询消耗表，得出每天的消耗数据,agent_id分组
//        $finalDayCostSql = "SELECT SUM(money)  as cost FROM media_cost WHERE 1=1 {$whereSql} group by agent_id";
//        $dayCostData = Db::connect('db_advert')->query($finalDayCostSql);

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




    }


    // 生成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['red_date'])){
            $whereSql .= " AND tdate BETWEEN '{$startDate}' AND '{$endDate}'";
        }

        return $whereSql;
    }

}