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']) && is_array($params['filter']) && in_array('igz',$params['filter']);


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


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

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


        // 1.查询消耗数据, 安game_id/agent_id分组
        // 忽略后台录入的消耗
        $costWhereSql = $whereSql;
        if( !empty($params['filter']) && is_array($params['filter']) && in_array('fak',$params['filter'])){
            $costWhereSql .= " AND add_type = 0";
        }
        $costSql = "SELECT {$type}, SUM(money) as cost from media_cost WHERE 1=1 {$costWhereSql} 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,
            auth_id,
            media_id
        ";

        // 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);
        $activeData = array_column($activeData, 'active', $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);
        $regPayData = array_column($regPayData, null, $type);


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

        $hb7dayDataSum = 0;
        foreach($hb7dayData as $row){
            $hb7dayDataSum += $row;
        }
        if(empty($baseData)){
            return [];
        }

        // 6. 所有游戏信息
        $gameData = Db::connect('db_center')->table('pf_game')->select()->toArray();

        // 7. 所有用户信息
        $authData = Db::connect('db_system')->table('sa_system_user')->select()->toArray();

        // 8. 媒体列表信息
        $mediaData = Db::connect('db_advert')->table('media_list')->select()->toArray();

        // 9. 渠道列表信息
        $agentData = Db::connect('db_advert')->table('agent_list')->select()->toArray();

        $gameData = array_column($gameData, null, 'id');
        $authData = array_column($authData, null, 'id');
        $mediaData = array_column($mediaData, null, 'id');


        // 拼接数据
        foreach($baseData as $key => &$row){

            $typeId = $row[$type]; // type， 的取值， 这里根据分组， game_id | agent_id
            $cost = $costDataColumn[$typeId]??0;
            $row['cost'] = $cost;
            // 忽略0投入的
            if($igz && $row['cost']===0){
                unset($baseData[$key]);
            }

            // 注册成本
            $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'],$row['cost'],1);


            $row['reg_pay_amount']= $hb7dayData[$typeId]['reg_pay_amount'] ?? 0;

            //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($hb7dayData[$typeId]??0,$row['cost'],1);


            // 按分组信息展示，id和name
            switch ($params['group']??''){
                case 1:
                    $row['akey'] = $row['game_id'];
                    $row['name'] = $gameData[$row['game_id']]['name'];
                    $row['auth_name'] = '-';
                    break;

                case 2:
                    $row['akey'] = $row['auth_id'];
                    $row['name'] = '-';
                    $row['auth_name'] = $authData[$row['auth_id']]['username'];

                case 3:
                    $row['akey'] = $row['media_id'];
                    $row['name'] = $mediaData[$row['media_id']]['name'];
                    $row['auth_name'] = $authData[$row['auth_id']]['username'];

                default:
                    $row['akey'] = $row['agent_id'];
                    $row['name'] = $agentData[$row['agent_id']]['name'];
                    $row['auth_name'] = '-';
                    break;
            }

        }


        //  底部合计
        $total=[
            'akey'=>'合计'
        ];
        foreach($baseData as &$row){
            $total['cost'] = !empty($total['cost']) ? ($total['cost'] + $row['cost']) : $row['cost'];

            // 激活
            $total['install'] =!empty($total['install']) ? ($total['install'] + $row['install']) : $row['install'];

            // 注册设备
            $total['reg_dev'] =!empty($total['reg_dev']) ? ($total['reg_dev'] + $row['reg_dev']) : $row['reg_dev'];

            // 注册数
            $total['reg_total'] =  !empty($total['reg_total']) ? ($total['reg_total'] + $row['reg_total']) : $row['reg_total'];

            // 创角数
            $total['role_total'] =!empty($total['role_total']) ? ($total['role_total'] + $row['role_total']) : $row['role_total'];
            // 创角率

            // 次留数
            $total['active'] =!empty($total['active']) ? ($total['active'] + $row['active']) : $row['active'];
            // 次留成本
            // 次留率

            // 当天付费
            $total['reg_pay_total_da'] =!empty($total['reg_pay_total_da']) ? ($total['reg_pay_total_da'] + $row['reg_pay_total_da']) : $row['reg_pay_total_da'];

            // 总付费人数
            $total['reg_pay_num'] = !empty($total['reg_pay_num']) ? ($total['reg_pay_num'] + $row['reg_pay_num']) : $row['reg_pay_num'];

            // 总付费金额
            $total['reg_pay_total'] = !empty($total['reg_pay_total']) ? ($total['reg_pay_total'] + $row['reg_pay_total']) : $row['reg_pay_total'];

            // 老用户
            $total['old_login_total'] = !empty($total['old_login_total']) ? ($total['old_login_total'] + $row['old_login_total']) : $row['old_login_total'];

            //注册总充值
            $total['reg_pay_amount'] = !empty($total['reg_pay_amount']) ? ($total['reg_pay_amount'] + $row['reg_pay_amount']) : $row['reg_pay_amount'];

        }

        // 注册成本
        $total['reg_cost'] = ToolLogic::getRound($total['cost'],$total['reg_total'] ?? 0);

        // 汇总创角率
        $total['role_per'] = ToolLogic::getRound($total['role_total'],$total['reg_total'] ?? 0);

        // 次留率
        $total['a_per'] = ToolLogic::getPercent( $total['active'],$total['reg_total'],1);
        // 次留成本
        $total['a_cost']  = ToolLogic::getRound($total['cost'],$total['active'],1);

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

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

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

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

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

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

        // 7天回本率

        $total['re_amount_per_7'] = ToolLogic::getPercent($hb7dayDataSum,$total['cost'],1);

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



        $totalData = $this->agentTotal($where);

        // 汇总数据
        return [
            'data' => array_merge($baseData,[$total]),
            'totalData' => $totalData
        ];


    }

    /**
     * @param $where
     * @return void
     * 广告位总览 ，根据site_id分组
     * 支出-> media_cost表的消耗数据
     */
    public function getAdSiteDataList($where){
        $params = $this->searchByAuth($where);
        // 忽略 0 投入
        $igz = !empty($params['filter']) && is_array($params['filter']) && in_array('igz',$params['filter']);

        // 根据渠道名称获取渠道ID
        if(!empty($params['site_name'])){
            $agentData = Db::connect('db_advert')->query("SELECT id,name FROM agent_list WHERE 1=1 AND name = '{$params['agent_name']}'");
            $params['agent_id'] = $agentData[0]['id'] ?? 0;
        }

        // 根据广告名称获取广告ID
        if(!empty($params['site_name'])){
            $mediaData = Db::connect('db_advert')->query("SELECT id,name FROM agent_site WHERE 1=1 AND name = '{$params['site_name']}'");
            $params['media_id'] = $mediaData[0]['id'] ?? 0;
        }

        if(empty($params['agent_id'])){
            $params['agent_id'] = 0;
        }
        $groupBy = 'site_id,media_id';
        $whereSql = $this->generateWhereSql($params);

        // 支出数据
        $costWhereSql = $whereSql;
        $costData = Db::connect('db_advert')->query("SELECT SUM(money), site_id as cost FROM media_cost WHERE 1=1 $costWhereSql GROUP BY $groupBy");
        $costData = array_column($costData, 'cost', 'site_id');

        // base统计数据（展示IP(ad_show_ip)、激活(install)、注册设备(reg_dev)、注册数(reg_total)、创角数(role_total)、老用户（old_login_total）、注册付费人数（reg_pay_num）、当天注册付费金额(reg_pay_total_da)、付费总金额（pay_total）、付费总人数（pay_num））
        $baseTotalField = "
            SUM(ad_show_ip) AS ad_show_ip,
            SUM(install) AS install,
            SUM(reg_dev) AS reg_dev,
            SUM(reg_total) AS reg_total,
            SUM(role_create_user) AS role_total,
            SUM(old_login_total) AS old_login_total,
            SUM(reg_pay_total) AS reg_pay_total_da,
            site_id,
            media_id,
            agent_id,
            auth_id";
        $baseWhereSql = $whereSql;
        $baseTotalDayData = $this->generateYearUnionList('base_total_day_', $params['reg_date'], $baseWhereSql, $baseTotalField, $groupBy);
       
        // active 活跃数，次留人数
        $activeWhereSql = $whereSql.' AND days = 1';
        $activeWhereSql = str_replace('AND tdate','AND reg_date',$activeWhereSql);
        $activeField = "
            sum(active_total) as active, 
            site_id";
        $activeData = $this->generateYearUnionList('game_active_day_', $params['reg_date'], $activeWhereSql, $activeField, $groupBy);
        $activeData = array_column($activeData, 'active', 'site_id');

        // 注册充值统计（累计充值reg_pay_total， 注册总充值(扣除分成)reg_pay_amount、注册累计付费人数(reg_pay_num)）
        $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,
            site_id";
        $regPayWhereSql = str_replace('AND tdate','AND reg_date',$regPayWhereSql);
        $regPayData = $this->generateYearUnionList('game_reg_pay_day_', $params['reg_date'], $regPayWhereSql, $regPayField, $groupBy);
        
        $regPayData = array_column($regPayData, null, 'site_id');
       

        // 拼装数据
        foreach($baseTotalDayData as $key=>&$row){

             $row['active'] = $activeData[$row['site_id']] ?? 0; // 次留数
            $row['cost'] = $costData[$row['site_id']] ?? 0; // 支出
            // 忽略0投入的
            if($igz && $row['cost']===0){
                unset($baseTotalDayData[$key]);
                
            }
            $row['reg_cost'] = ToolLogic::getRound($row['cost'],$row['reg_total'] ?? 0); // 注册成本
            $row['role_per'] = ToolLogic::getRound($row['role_total'],$row['reg_total'] ?? 0); // 创角率
            $row['a_cost']  = ToolLogic::getRound($row['cost'],$row['active'],1); // 次留成本
            $row['a_per'] = ToolLogic::getPercent( $row['active'],$row['reg_total'],1); // 次留率
            $row['reg_pay_total'] = $regPayData[$row['site_id']]['reg_pay_total'] ?? 0; // 总付费金额
            $row['reg_pay_num'] = $regPayData[$row['site_id']]['reg_pay_num'] ?? 0; // 总付费人数
            $row['reg_pay_amount'] = $regPayData[$row['site_id']]['reg_pay_amount'] ?? 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'],$row['cost'],1); // 实际回本率
            $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']); // 注册ARPU
           
        }

        

        // // 合计
        $total = [
            'site_id' => '合计',
            'media_id' => '-',
            'agent_id' => '-',
            'auth_id' => '-',
            'agent_name' => '-',
            'site_name' => '-',
            'auth_name' => '-',
        ];
        foreach($baseTotalDayData as &$row){
            $total['cost'] = !empty($total['cost']) ? ($total['cost'] + $row['cost']) : $row['cost']; // 支出
            $total['install'] = !empty($total['install']) ? ($total['install'] + $row['install']) : $row['install']; // 激活
            $total['reg_dev'] = !empty($total['reg_dev']) ? ($total['reg_dev'] + $row['reg_dev']) : $row['reg_dev']; // 注册设备
            $total['reg_total'] = !empty($total['reg_total']) ? ($total['reg_total'] + $row['reg_total']) : $row['reg_total']; // 注册数
            $total['role_total'] = !empty($total['role_total']) ? ($total['role_total'] + $row['role_total']) : $row['role_total']; // 创角数
            $total['old_login_total'] = !empty($total['old_login_total']) ? ($total['old_login_total'] + $row['old_login_total']) : $row['old_login_total']; // 老用户
            $total['reg_pay_total'] = !empty($total['reg_pay_total']) ? ($total['reg_pay_total'] + $row['reg_pay_total']) : $row['reg_pay_total']; // 总付费金额

            echo $row['reg_pay_num'];
            $total['reg_pay_num'] = !empty($total['reg_pay_num']) ? ($total['reg_pay_num'] + $row['reg_pay_num']) : $row['reg_pay_num']; // 总付费人数
            $total['active'] = !empty($total['active']) ? ($total['active'] + $row['active']) : $row['active']; // 次留数
            $total['reg_pay_amount'] = !empty($total['reg_pay_amount']) ? ($total['reg_pay_amount'] + $row['reg_pay_amount']) : $row['reg_pay_amount']; // 注册总充值
            $total['reg_pay_total_da'] = !empty($total['reg_pay_total_da']) ? ($total['reg_pay_total_da'] + $row['reg_pay_total_da']) : $row['reg_pay_total_da']; // 当天注册付费金额

        }
        // print_r($total);
        $total['reg_cost'] = ToolLogic::getRound($total['cost']??0,$total['reg_total'] ?? 0); // 注册成本
        $total['role_per'] = ToolLogic::getRound($total['role_total']??0,$total['reg_total'] ?? 0); // 创角率
        $total['a_per'] = ToolLogic::getPercent( $total['active']??0,$total['reg_total']??0,1); // 次留率
        $total['a_cost']  = ToolLogic::getRound($total['cost']??0,$total['active']??0,1); // 次留成本
        $total['p_cost']  = ToolLogic::getRound($total['reg_pay_num']??0,$total['cost']??0,1); // 付费成本
        $total['p_per'] = ToolLogic::getPercent($total['reg_pay_num']??0,$total['reg_total']??0,1); // 付费率
        $total['da_per'] = ToolLogic::getPercent($total['reg_pay_total_da']??0,$total['cost']??0,1); // 当天回本率
        $total['re_per']  = (float)trim(ToolLogic::getPercent($total['reg_pay_total']??0,$total['cost']??0), "%"); // 累计回本率
        $total['re_amount_per'] = ToolLogic::getPercent($total['reg_pay_amount']??0,$total['cost']??0,1); // 实际回本率
        $total['arpu']    = ToolLogic::getRound($total['reg_pay_total']??0,$total['reg_pay_num']??0); // ARPU
        $total['r_arpu']  = ToolLogic::getRound($total['reg_pay_total']??0,$total['reg_total']??0); // 注册ARPU

 

        $resultData = ['data'=>$baseTotalDayData,'total'=>$total];
        return $resultData;

    }


    /**
     * 渠道按日分析
     */
    public function getAgentDayDataList($where){
        $params = $this->searchByAuth($where);
        $whereSql = $this->generateWhereSql($params);

         // 根据渠道名称获取渠道ID
        if(!empty($params['site_name'])){
            $agentData = Db::connect('db_advert')->query("SELECT id,name FROM agent_list WHERE 1=1 AND name = '{$params['agent_name']}'");
            $params['agent_id'] = $agentData[0]['id'] ?? 0;
        }

        // 根据广告名称获取广告ID
        if(!empty($params['site_name'])){
            $mediaData = Db::connect('db_advert')->query("SELECT id,name FROM agent_site WHERE 1=1 AND name = '{$params['site_name']}'");
            $params['media_id'] = $mediaData[0]['id'] ?? 0;
        }

        if(empty($params['agent_id'])){
            $params['agent_id'] = 0;
        }
        $whereSql = $this->generateWhereSql($params);

        // 1. 消耗表. 获取消耗金额
        $costData = Db::connect('db_advert')->query("SELECT SUM(money) as cost FROM media_cost WHERE 1=1 $whereSql Group By tdate");
        $costData = array_column($costData, 'cost', 'tdate');
        

        // 2. base统计数据（展示IP(ad_show_ip)、激活(install)、注册设备(reg_dev)、注册数(reg_total)、创角数(role_total)、老用户（old_login_total）、注册付费人数（reg_pay_num）、当天注册付费金额(reg_pay_total_da)、付费总金额（pay_total）、付费总人数（pay_num））
        $baseTotalField = "
            SUM(ad_show_ip) AS ad_show_ip,
            SUM(install) AS install,
            SUM(reg_dev) AS reg_dev,
            SUM(reg_total) AS reg_total,
            SUM(role_create_user) AS role_total,
            SUM(old_login_total) AS old_login_total,
            SUM(reg_pay_total) AS reg_pay_total_da,
            SUM(pay_amount) AS pay_amount,
            SUM(pay_total) AS pay_total,
            tdate";
        $baseWhereSql = $whereSql;
        $baseTotalDayData = $this->generateYearUnionList('base_total_day_', $params['reg_date'], $baseWhereSql, $baseTotalField, 'tdate');
        $baseTotalDayData = array_column($baseTotalDayData, null, 'tdate');

         // 3. active 活跃数，次留人数
        $activeWhereSql = $whereSql.' AND days = 1';
        $activeWhereSql = str_replace('AND tdate','AND reg_date',$activeWhereSql);
        $activeField = "
            sum(active_total) as active, 
            reg_date astdate";
        $activeData = $this->generateYearUnionList('game_active_day_', $params['reg_date'], $activeWhereSql, $activeField, 'reg_date');
        $activeData = array_column($activeData, 'active', 'tdate');
 
        // 4. 注册充值统计（累计充值reg_pay_total， 注册总充值(扣除分成)reg_pay_amount、注册累计付费人数(reg_pay_num)）
        $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,
            reg_date as tdate";
        $regPayWhereSql = str_replace('AND tdate','AND reg_date',$regPayWhereSql);
        $regPayData = $this->generateYearUnionList('game_reg_pay_day_', $params['reg_date'], $regPayWhereSql, $regPayField, 'reg_date');
        
        $regPayData = array_column($regPayData, null, 'tdate');

        // 5. 拼装数据
        // 拼装数据
        foreach($baseTotalDayData as $key=>&$row){

             $row['active'] = $activeData[$row['tdate']] ?? 0; // 次留数
            $row['cost'] = $costData[$row['tdate']] ?? 0; // 支出
           
            $row['reg_cost'] = ToolLogic::getRound($row['cost'],$row['reg_total'] ?? 0); // 注册成本
            $row['role_per'] = ToolLogic::getRound($row['role_total'],$row['reg_total'] ?? 0); // 创角率
            $row['a_cost']  = ToolLogic::getRound($row['cost'],$row['active'],1); // 次留成本
            $row['a_per'] = ToolLogic::getPercent( $row['active'],$row['reg_total'],1); // 次留率
            $row['reg_pay_total'] = $regPayData[$row['tdate']]['reg_pay_total'] ?? 0; // 总付费金额
            $row['reg_pay_num'] = $regPayData[$row['tdate']]['reg_pay_num'] ?? 0; // 总付费人数
            $row['reg_pay_amount'] = $regPayData[$row['tdate']]['reg_pay_amount'] ?? 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'],$row['cost'],1); // 实际回本率
            $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']); // 注册ARPU
            $row['pay_cost']  = ToolLogic::getPercent($row['pay_amount']??0,$row['cost']??0); // 收支比
        
           
        }

        // 6. 合计
        $total = [
            'tdate' => '合计'
        ];
        foreach($baseTotalDayData as &$row){
            $total['cost'] = !empty($total['cost']) ? ($total['cost'] + $row['cost']) : $row['cost']; // 支出
            $total['ad_show_ip'] = !empty($total['ad_show_ip']) ? ($total['ad_show_ip'] + $row['ad_show_ip']) : $row['ad_show_ip']; // 展示IP
            $total['install'] = !empty($total['install']) ? ($total['install'] + $row['install']) : $row['install']; // 激活
            $total['reg_dev'] = !empty($total['reg_dev']) ? ($total['reg_dev'] + $row['reg_dev']) : $row['reg_dev']; // 注册设备
            $total['reg_total'] = !empty($total['reg_total']) ? ($total['reg_total'] + $row['reg_total']) : $row['reg_total']; // 注册数
            $total['role_total'] = !empty($total['role_total']) ? ($total['role_total'] + $row['role_total']) : $row['role_total']; // 创角数
            $total['active'] = !empty($total['active']) ? ($total['active'] + $row['active']) : $row['active']; // 次留数
            // 付费人数
            $total['reg_pay_num'] = !empty($total['reg_pay_num']) ? ($total['reg_pay_num'] + $row['reg_pay_num']) : $row['reg_pay_num']; // 总付费人数
            $total['reg_pay_total'] = !empty($total['reg_pay_total']) ? ($total['reg_pay_total'] + $row['reg_pay_total']) : $row['reg_pay_total']; // 总付费金额
            $total['reg_pay_amount'] = !empty($total['reg_pay_amount']) ? ($total['reg_pay_amount'] + $row['reg_pay_amount']) : $row['reg_pay_amount']; // 注册总充值
            $total['pay_total'] = !empty($total['pay_total']) ? ($total['pay_total'] + $row['pay_total']) : $row['pay_total']; // 当天收入
            $total['pay_amount'] = !empty($total['pay_amount']) ? ($total['pay_amount'] + $row['pay_amount']) : $row['pay_amount']; // 实际收入
            $total['old_login_total'] = !empty($total['old_login_total']) ? ($total['old_login_total'] + $row['old_login_total']) : $row['old_login_total']; // 老用户
            $total['reg_pay_total_da'] = !empty($total['reg_pay_total_da']) ? ($total['reg_pay_total_da'] + $row['reg_pay_total_da']) : $row['reg_pay_total_da']; // 当天注册付费金额
           
        }

        $total['reg_cost'] = ToolLogic::getRound($total['cost']??0,$total['reg_total'] ?? 0); // 注册成本
        $total['role_per'] = ToolLogic::getRound($total['role_total']??0,$total['reg_total'] ?? 0); // 创角率
        $total['a_per'] = ToolLogic::getPercent( $total['active']??0,$total['reg_total']??0,1); // 次留率
        $total['a_cost']  = ToolLogic::getRound($total['cost']??0,$total['active']??0,1); // 次留成本
        $total['p_cost']  = ToolLogic::getRound($total['reg_pay_num']??0,$total['cost']??0,1); // 付费成本
        $total['p_per'] = ToolLogic::getPercent($total['reg_pay_num']??0,$total['reg_total']??0,1); // 付费率
        $total['da_per'] = ToolLogic::getPercent($total['reg_pay_total_da']??0,$total['cost']??0,1); // 当天回本率
        $total['re_per']  = (float)trim(ToolLogic::getPercent($total['reg_pay_total']??0,$total['cost']??0), "%"); // 累计回本率
        $total['re_amount_per'] = ToolLogic::getPercent($total['reg_pay_amount']??0,$total['cost']??0,1); // 实际回本率
        $total['arpu']    = ToolLogic::getRound($total['reg_pay_total']??0,$total['reg_pay_num']??0); // ARPU
        $total['r_arpu']  = ToolLogic::getRound($total['reg_pay_total']??0,$total['reg_total']??0); // 注册ARPU
        //收支比
        $total['pay_cost']  = ToolLogic::getPercent($total['pay_amount']??0,$total['cost']??0);

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

    /**
     * @return void
     * 大盘数据（没有权限）
     * 注册成本：消耗金额/注册数
     * roi：注册付费金额 reg_pay_total /消耗 cost
     * 付费成本：总付费人数/消耗
     * 付费率：总付费人数/注册数
 */
    public function agentTotal($where){
        $whereSql = $this->generateWhereSql($where);
        $cost = 0; // 消耗金额
        $regTotal = 0; // 注册数
        $regPayTotal = 0; // 注册付费金额
        $regPayNum = 0; // 总付费人数
        // 1. 消耗表. 获取消耗金额
        $costData = Db::connect('db_advert')->query("SELECT SUM(money) as cost FROM media_cost WHERE 1=1 $whereSql");
        $cost = $costData[0]['cost'] ?? 0;
        
        // 2. base_total_day_ 获取注册数(reg_total)、注册付费金额(reg_pay_total)、
        $baseTotalField = "
        SUM(reg_total) AS reg_total,
        SUM(reg_pay_total) AS reg_pay_total";
        $baseTotalDayData = $this->generateYearUnionList('base_total_day_', $where['reg_date'], $whereSql, $baseTotalField);

        $regTotal = $baseTotalDayData[0]['reg_total'] ?? 0;
        $regPayTotal = $baseTotalDayData[0]['reg_pay_total'] ?? 0;

        // 3. 注册付费表. 获取总付费人数(reg_pay_num)
        $regPayField = "
            sum(addup_pay_num) as reg_pay_num";
        $regPayWhereSql = str_replace('AND tdate','AND reg_date',$whereSql);
        $regPayData = $this->generateYearUnionList('game_reg_pay_day_', $where['reg_date'], $regPayWhereSql, $regPayField);
        $regPayNum = $regPayData[0]['reg_pay_num'] ?? 0;

        // roi
        $roi = ToolLogic::getPercent($regPayTotal,$cost,1);

        // 付费成本
        $pCost = ToolLogic::getRound($cost,$regPayNum);

        // reg_cost 注册成本
        $regCost = ToolLogic::getRound($cost,$regTotal);

        // 付费率
        $pPer = ToolLogic::getPercent($regPayNum,$regTotal,1);

        // 返回数据
        return [
            'reg_cost' => $regCost,
            'roi' => $roi,
            'p_cost' => $pCost,
            'p_per' => $pPer,
        ];
    }


    /**
     * @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}";
        }
        $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]}'";
        }

        return $whereSql;
    }

}