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

<?php

// 玩家日志逻

namespace app\v1\logic\dataReport;

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) {
            $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']) ? round($total[$hourKey]['cost'] + $hourRow['cost'], 2) : $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']) ? round($total['total_raw']['cost'] + $hourRow['cost'], 2) : $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 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;
    }

    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) {
            $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['reg_pay_amount'] = $hb7dayData[$typeId]['reg_pay_amount'] ?? 0;

            // 实际回本率
            $row['re_amount_per'] = ToolLogic::getPercent($row['reg_pay_amount'], $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($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
        ];


    }

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

    /**
     * @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;
    }

    /**
     * @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
     * @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']; // 总付费金额
            $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']; // 当天注册付费金额

        }

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


    // 生成基础日统计表的联合查询列表

    /**
     * ltv
     *
     */
    public function getLtvDataList($where)
    {
        $params = $this->searchByAuth($where);
        // 展示类型
        $dataType = $params['data_type'] ?? 'recovery';
        $nowDate = date("Y-m-d");
        $whereSql = $this->generateWhereSql($params);

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

        // 2. 获取注册数
        $baseData = $this->generateYearUnionList('base_total_day_', $params['reg_date'], $whereSql, "SUM(reg_total) as reg_total, tdate", 'tdate');
        $baseData = array_column($baseData, null, 'tdate');

        // 3. 获取注册付费金额
        $field = "
            reg_date,
            pay_date,
            SUM(pay_num) as pay_num,
            SUM(pay_total) as pay_total,
            SUM(addup_pay_num) AS addup_pay_num,
            SUM(addup_pay_total) AS addup_pay_total,
            SUM(addup_pay_amount) AS addup_pay_amount
        ";
        $regPayWhereSql = str_replace('AND tdate', 'AND reg_date', $whereSql);
        $regPayData = $this->generateYearUnionList('game_reg_pay_day_', $params['reg_date'], $regPayWhereSql, $field, 'reg_date,pay_date');

        // 如果注册付费数据为空，则返回空数据给前端
        if (empty($regPayData)) {
            return ['data' => [], 'totalRow' => []];
        }
        // 前端合计数据
        $totalRow = [
            'tdate' => '合计'
        ];

        // 前端data数据
        $data = [];

        $totalLtv = [];


        // 合计 消耗金额，注册数，注册成本
        foreach ($baseData as $key => $row) {
            $totalRow['cost'] = !empty($totalRow['cost']) ? ($totalRow['cost'] + $costData[$key] ?? 0) : $costData[$key] ?? 0;
            $totalRow['d0']['cost'] = !empty($totalRow['cost']) ? ($totalRow['cost'] + $costData[$key] ?? 0) : $costData[$key] ?? 0;
            $totalRow['reg_total'] = !empty($totalRow['reg_total']) ? ($totalRow['reg_total'] + $row['reg_total'] ?? 0) : $row['reg_total'] ?? 0;
            $totalRow['d0']['reg_total'] = !empty($totalRow['reg_total']) ? ($totalRow['reg_total'] + $row['reg_total'] ?? 0) : $row['reg_total'] ?? 0;
            $totalRow['reg_cost'] = ToolLogic::getRound($totalRow['cost'] ?? 0, $totalRow['reg_total'] ?? 0);

            $days = (strtotime($nowDate) - strtotime($key)) / 86400 + 1;
            for ($i = 1; $i <= $days; $i++) {
                $daykey = 'd' . $i;
                $totalRow[$daykey]['reg_total'] = !empty($totalRow[$daykey]['reg_total']) ? ($totalRow[$daykey]['reg_total'] + $row['reg_total'] ?? 0) : $row['reg_total'] ?? 0;
                $totalRow[$daykey]['cost'] = !empty($totalRow[$daykey]['cost']) ? ($totalRow[$daykey]['cost'] + $costData[$key] ?? 0) : $costData[$key] ?? 0;
            }
        }


        // 按照天计算LTV
        foreach ($regPayData as &$row) {
            $regDate = $row['reg_date'];
            $data[$regDate]['tdate'] = $regDate;

            // 注册数
            $data[$regDate]['reg_total'] = $baseData[$regDate]['reg_total'] ?? 0;

            // 注册成本
            $data[$regDate]['reg_cost'] = ToolLogic::getRound($costData[$regDate] ?? 0, $data[$regDate]['reg_total'] ?? 0);
            // 消耗金额
            $data[$regDate]['cost'] = $costData[$regDate] ?? 0;

            // 计算日期相差天数
            $daykey = (strtotime($row['pay_date']) - strtotime($regDate)) / 86400 + 1;
            $daykey = 'd' . $daykey;


            switch ($dataType) {
                case 'recovery':
                    $data[$regDate][$daykey] = ToolLogic::getPercent($row['addup_pay_total'], $costData[$regDate] ?? 0, 1);
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = ToolLogic::getPercent($row['addup_pay_total'], $costData[$regDate] ?? 0, 1);
                    }
                    break;

                /* 扣除分成/cost , 计算 */
                case 'remount':
                    // 按天回本率
                    $data[$regDate][$daykey] = ToolLogic::getPercent($row['addup_pay_amount'], $costData[$regDate] ?? 0, 1);
                    // 累计回本率
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = ToolLogic::getPercent($row['addup_pay_amount'], $costData[$regDate] ?? 0, 1);
                    }
                    break;

                /*  累计计费付费金额 / 注册数 */
                case 'ltv':
                    // 按天LTV
                    $data[$regDate][$daykey] = ToolLogic::getRound($row['addup_pay_total'], $baseData[$regDate]['reg_total'] ?? 0, 1);
                    // 累计LTV
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = ToolLogic::getRound($row['addup_pay_total'], $baseData[$regDate]['reg_total'] ?? 0, 1);
                    }
                    break;

                /* 后面的LTV数据， 按天LTV / 第一天LTV ， 目的是为了计算LTV增长率*/
                case 'ltvbs':
                    $totalLtv[$daykey] = ToolLogic::getRound($row['addup_pay_total'], $baseData[$regDate]['reg_total'] ?? 0, 1);
                    $fz = $totalLtv['d1'] ?? 0 != 0 ? $totalLtv['d1'] : 1;
                    $data[$regDate][$daykey] = round($totalLtv[$daykey] / $fz, 2);
                    break;

                /* 付费人数*/
                case 'pay_num':
                    $data[$regDate][$daykey] = $row['pay_num'];
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = $row['addup_pay_num'];
                    }
                    break;

                /* 付费总额 */
                case 'pay_total':
                    $data[$regDate][$daykey] = $row['pay_total'];
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = $row['addup_pay_total'];
                    }

                    break;

                /* 付费率 */
                case 'pay_rate':
                    $data[$regDate][$daykey] = ToolLogic::getPercent($row['pay_num'], $baseData[$regDate]['reg_total']);
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = ToolLogic::getPercent($row['addup_pay_num'], $baseData[$regDate]['reg_total']);
                    }
                    break;

                /* 付费ARPU */
                case 'arpu':
                    $data[$regDate][$daykey] = ToolLogic::getRound($row['pay_total'], $row['pay_num']);
                    if ($nowDate === $row['pay_date']) {
                        $data[$regDate]['d0'] = ToolLogic::getRound($row['addup_pay_total'], $row['addup_pay_num']);
                    }
                    break;
                default:
                    continue 2;
                    break;
            }
            $totalRow[$daykey]['addup_pay_total'] = $totalRow[$daykey]['addup_pay_total'] ?? 0;
            $totalRow[$daykey]['addup_pay_amount'] = $totalRow[$daykey]['addup_pay_amount'] ?? 0;
            $totalRow[$daykey]['pay_num'] = $totalRow[$daykey]['pay_num'] ?? 0;
            $totalRow[$daykey]['pay_total'] = $totalRow[$daykey]['pay_total'] ?? 0;

            $totalRow[$daykey]['addup_pay_total'] = !empty($totalRow[$daykey]['addup_pay_total']) ? $totalRow[$daykey]['addup_pay_total'] + $row['addup_pay_total'] : $row['addup_pay_total'];
            $totalRow[$daykey]['addup_pay_amount'] = !empty($totalRow[$daykey]['addup_pay_amount']) ? $totalRow[$daykey]['addup_pay_amount'] + $row['addup_pay_amount'] : $row['addup_pay_amount'];
            $totalRow[$daykey]['pay_num'] = !empty($totalRow[$daykey]['pay_num']) ? $totalRow[$daykey]['pay_num'] + $row['pay_num'] : $row['pay_num'];
            $totalRow[$daykey]['pay_total'] = !empty($totalRow[$daykey]['pay_total']) ? $totalRow[$daykey]['pay_total'] + $row['pay_total'] : $row['pay_total'];


            if ($row['pay_date'] == $regDate) {
                $pay_total = !empty($pay_total) ? $pay_total + $row['addup_pay_total'] : $row['addup_pay_total'];
                $pay_amount = !empty($pay_amount) ? $pay_amount + $row['addup_pay_amount'] : $row['addup_pay_amount'];
                $pay_num = !empty($pay_num) ? $pay_num + $row['pay_num'] : $row['pay_num'];
            }


        }

        foreach ($totalRow as $key => &$row) {
            if ($key[0] === 'd') {
                switch ($dataType) {
                    case 'recovery':
                        $totalRow[$key] = ToolLogic::getPercent($totalRow[$key]['addup_pay_total'] ?? 0, $totalRow[$key]['cost'] ?? 0, 1);
                        break;
                    case 'remount':
                        $totalRow[$key] = ToolLogic::getPercent($totalRow[$key]['addup_pay_amount'] ?? 0, $totalRow[$key]['cost'] ?? 0, 1);
                        break;
                    case 'ltv':
                        $totalRow[$key] = ToolLogic::getRound($totalRow[$key]['addup_pay_total'] ?? 0, $totalRow[$key]['reg_total'] ?? 0);
                        break;
                    case 'ltvbs':
                        $totalLtv[$key] = ToolLogic::getRound($totalRow[$key]['addup_pay_total'] ?? 0, $totalRow[$key]['reg_total'] ?? 0);
                        $fz = $totalLtv['d1'] ?? 0 != 0 ? $totalLtv['d1'] : 1;
                        $totalRow[$key] = round($totalLtv[$key] / $fz, 2);

                        break;
                    case 'pay_num':
                        $totalRow[$key] = $totalRow[$key]['pay_num'] ?? 0;
                        break;
                    case 'pay_total':
                        $totalRow[$key] = $totalRow[$key]['pay_total'] ?? 0;
                        break;
                    case 'pay_rate':
                        $totalRow[$key] = ToolLogic::getPercent($totalRow[$key]['pay_num'] ?? 0, $totalRow[$key]['reg_total'] ?? 0);
                        break;
                    case 'arpu':
                        $totalRow[$key] = ToolLogic::getRound($totalRow[$key]['pay_total'] ?? 0, $totalRow[$key]['pay_num'] ?? 0);
                        break;
                }
            }
        }

        if ($data && $totalRow) {
            switch ($dataType) {
                case 'recovery':
                    $totalRow['d0'] = ToolLogic::getPercent($pay_total, $totalRow['cost'], 1);
                    break;
                case 'remount':
                    $totalRow['d0'] = ToolLogic::getPercent($pay_amount, $totalRow['cost'], 1);
                    break;
                case 'ltv':
                    $totalRow['d0'] = ToolLogic::getRound($pay_total, $totalRow['reg_total']);
                    break;
                case 'ltvbs':
                    $totalLtv['d0'] = ToolLogic::getRound($pay_total, $totalRow['reg_total']);
                    $fz = $totalLtv['d1'] ?? 0 != 0 ? $totalLtv['d1'] : 1;
                    $totalRow['d0'] = round($totalLtv['d0'] / $fz, 2);
                    break;
                case 'pay_num':
                    $totalRow['d0'] = $pay_num;
                    break;
                case 'pay_total':
                    $totalRow['d0'] = $pay_total;
                    break;
                case 'pay_rate':
                    $totalRow['d0'] = ToolLogic::getPercent($pay_num, $totalRow['reg_total']);
                    break;
                case 'arpu':
                    $totalRow['d0'] = ToolLogic::getRound($pay_total, $pay_num);
                    break;
                default:
                    break;
            }
            $totalRow['reg_cost'] = ToolLogic::getRound($totalRow['cost'], $totalRow['reg_total']);
            $list = ToolLogic::arrSort($data, 'tdate', 'asc');
        }
        return [
            'data' => array_values($list),
            'totalRow' => $totalRow
        ];


    }


    // 生成wheresql

    /**
     * ltv按月
     */
    public function getLtvmDataList($where)
    {
        $params = $this->searchByAuth($where);
        // 展示类型
        $dataType = $params['data_type'] ?? 'recovery';
        $nowDate = date("Y-m");
        $whereSql = $this->generateWhereSql($params);

        // 1. 获取消耗金额

        // 2. 获取注册数
        $totalBaseWhereSql = str_replace('AND tdate', 'AND tmonth', $whereSql);
        $baseData = Db::connect('db_data_report')->query("SELECT tmonth,SUM(cost) as cost,SUM(reg_total) as reg_total FROM game_total_month WHERE 1=1 $totalBaseWhereSql GROUP BY tmonth");
        $baseData = array_column($baseData, null, 'tmonth');


        // 3. 获取注册付费金额
        $field = "
            reg_month,
            pay_month,
            SUM(pay_num) as pay_num,
            SUM(pay_total) as pay_total,
            SUM(addup_pay_num) AS addup_pay_num,
            SUM(addup_pay_total) AS addup_pay_total,
            SUM(addup_pay_amount) AS addup_pay_amount
        ";
        $regPayWhereSql = str_replace('AND tdate', 'AND reg_month', $whereSql);
        $regPayData = Db::connect('db_data_report')->query("SELECT $field FROM game_reg_pay_month WHERE 1=1 $regPayWhereSql GROUP BY reg_month,pay_month");

        // 如果注册付费数据为空，则返回空数据给前端
        if (empty($regPayData)) {
            return ['data' => [], 'totalRow' => []];
        }
        // 前端合计数据
        $totalRow = [
            'tmonth' => '合计'
        ];

        // 前端data数据
        $data = [];

        $pay_total = 0;
        $pay_amount = 0;
        $pay_num = 0;


        // 合计 消耗金额，注册数，注册成本
        foreach ($baseData as $key => $row) {
            $totalRow['cost'] = !empty($totalRow['cost']) ? round($totalRow['cost'] + $row['cost'] ?? 0, 2) : $row['cost'] ?? 0;
            $totalRow['reg_total'] = !empty($totalRow['reg_total']) ? ($totalRow['reg_total'] + $row['reg_total'] ?? 0) : $row['reg_total'] ?? 0;
            $totalRow['reg_cost'] = ToolLogic::getRound($totalRow['cost'] ?? 0, $totalRow['reg_total'] ?? 0);

            $days = ToolLogic::getMonthNum($key, $nowDate);
            for ($i = 1; $i <= $days; $i++) {
                $daykey = 'm' . ($i + 1);
                $totalRow[$daykey]['reg_total'] = !empty($totalRow[$daykey]['reg_total']) ? ($totalRow[$daykey]['reg_total'] + $row['reg_total'] ?? 0) : $row['reg_total'] ?? 0;
                $totalRow[$daykey]['cost'] = !empty($totalRow[$daykey]['cost']) ? ($totalRow[$daykey]['cost'] + $row['cost'] ?? 0) : $row['cost'] ?? 0;
            }
        }

        // 按照天计算LTV
        foreach ($regPayData as &$row) {
            $regDate = $row['reg_month'];

            $data[$regDate]['tmonth'] = $regDate;

            // 注册数
            $data[$regDate]['reg_total'] = $baseData[$regDate]['reg_total'] ?? 0;

            // 注册成本
            $data[$regDate]['reg_cost'] = ToolLogic::getRound($baseData[$regDate]['cost'] ?? 0, $data[$regDate]['reg_total'] ?? 0);
            // 消耗金额
            $data[$regDate]['cost'] = $baseData[$regDate]['cost'] ?? 0;

            // 计算日期相差天数
            // $daykey = (strtotime($row['pay_month'])-strtotime($regDate))/86400+1;
            $daykey = 'm' . (ToolLogic::getMonthNum($regDate, $row['pay_month']) + 1);


            switch ($dataType) {
                case 'recovery':
                    $data[$regDate][$daykey] = ToolLogic::getPercent($row['addup_pay_total'], $baseData[$regDate]['cost'] ?? 0, 1);
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = ToolLogic::getPercent($row['addup_pay_total'], $baseData[$regDate]['cost'] ?? 0, 1);
                    }
                    break;

                /* 扣除分成/cost , 计算 */
                case 'remount':
                    // 按天回本率
                    $data[$regDate][$daykey] = ToolLogic::getPercent($row['addup_pay_amount'], $baseData[$regDate]['cost'] ?? 0, 1);
                    // 累计回本率
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = ToolLogic::getPercent($row['addup_pay_amount'], $baseData[$regDate]['cost'] ?? 0, 1);
                    }
                    break;

                /*  累计计费付费金额 / 注册数 */
                case 'ltv':
                    // 按天LTV
                    $data[$regDate][$daykey] = ToolLogic::getRound($row['addup_pay_total'], $baseData[$regDate]['reg_total'] ?? 0, 1);
                    // 累计LTV
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = ToolLogic::getRound($row['addup_pay_total'], $baseData[$regDate]['reg_total'] ?? 0, 1);
                    }
                    break;

                /* 付费人数*/
                case 'pay_num':
                    $data[$regDate][$daykey] = $row['pay_num'];
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = $row['addup_pay_num'];
                    }
                    break;

                /* 付费总额 */
                case 'pay_total':
                    $data[$regDate][$daykey] = $row['pay_total'];
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = $row['addup_pay_total'];
                    }

                    break;

                /* 付费率 */
                case 'pay_rate':
                    $data[$regDate][$daykey] = ToolLogic::getPercent($row['pay_num'], $baseData[$regDate]['reg_total']);
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = ToolLogic::getPercent($row['addup_pay_num'], $baseData[$regDate]['reg_total']);
                    }
                    break;

                /* 付费ARPU */
                case 'arpu':
                    $data[$regDate][$daykey] = ToolLogic::getRound($row['pay_total'], $row['pay_num']);
                    if ($nowDate === $row['pay_month']) {
                        $data[$regDate]['m0'] = ToolLogic::getRound($row['addup_pay_total'], $row['addup_pay_num']);
                    }
                    break;
                default:
                    continue 2;
                    break;
            }
            $totalRow[$daykey]['addup_pay_total'] = $totalRow[$daykey]['addup_pay_total'] ?? 0;
            $totalRow[$daykey]['addup_pay_amount'] = $totalRow[$daykey]['addup_pay_amount'] ?? 0;
            $totalRow[$daykey]['pay_num'] = $totalRow[$daykey]['pay_num'] ?? 0;
            $totalRow[$daykey]['pay_total'] = $totalRow[$daykey]['pay_total'] ?? 0;

            $totalRow[$daykey]['addup_pay_total'] = !empty($totalRow[$daykey]['addup_pay_total']) ? $totalRow[$daykey]['addup_pay_total'] + $row['addup_pay_total'] : $row['addup_pay_total'];
            $totalRow[$daykey]['addup_pay_amount'] = !empty($totalRow[$daykey]['addup_pay_amount']) ? $totalRow[$daykey]['addup_pay_amount'] + $row['addup_pay_amount'] : $row['addup_pay_amount'];
            $totalRow[$daykey]['pay_num'] = !empty($totalRow[$daykey]['pay_num']) ? $totalRow[$daykey]['pay_num'] + $row['pay_num'] : $row['pay_num'];
            $totalRow[$daykey]['pay_total'] = !empty($totalRow[$daykey]['pay_total']) ? $totalRow[$daykey]['pay_total'] + $row['pay_total'] : $row['pay_total'];


            if ($row['pay_month'] == $regDate) {
                $pay_total = !empty($pay_total) ? $pay_total + $row['addup_pay_total'] : $row['addup_pay_total'];
                $pay_amount = !empty($pay_amount) ? $pay_amount + $row['addup_pay_amount'] : $row['addup_pay_amount'];
                $pay_num = !empty($pay_num) ? $pay_num + $row['pay_num'] : $row['pay_num'];
            }


        }

        foreach ($totalRow as $key => &$row) {
            if ($key[0] === 'm') {
                switch ($dataType) {
                    case 'recovery':
                        $totalRow[$key] = ToolLogic::getPercent($totalRow[$key]['addup_pay_total'] ?? 0, $totalRow[$key]['cost'] ?? 0, 1);
                        break;
                    case 'remount':
                        $totalRow[$key] = ToolLogic::getPercent($totalRow[$key]['addup_pay_amount'] ?? 0, $totalRow[$key]['cost'] ?? 0, 1);
                        break;
                    case 'ltv':
                        $totalRow[$key] = ToolLogic::getRound($totalRow[$key]['addup_pay_total'] ?? 0, $totalRow[$key]['reg_total'] ?? 0);
                        break;
                    case 'pay_num':
                        $totalRow[$key] = $totalRow[$key]['pay_num'] ?? 0;
                        break;
                    case 'pay_total':
                        $totalRow[$key] = $totalRow[$key]['pay_total'] ?? 0;
                        break;
                    case 'pay_rate':
                        $totalRow[$key] = ToolLogic::getPercent($totalRow[$key]['pay_num'] ?? 0, $totalRow[$key]['reg_total'] ?? 0);
                        break;
                    case 'arpu':
                        $totalRow[$key] = ToolLogic::getRound($totalRow[$key]['pay_total'] ?? 0, $totalRow[$key]['pay_num'] ?? 0);
                        break;
                }
            }
        }

        if ($data && $totalRow) {
            switch ($dataType) {
                case 'recovery':
                    $totalRow['m0'] = ToolLogic::getPercent($pay_total, $totalRow['cost'], 1);
                    break;
                case 'remount':
                    $totalRow['m0'] = ToolLogic::getPercent($pay_amount, $totalRow['cost'], 1);
                    break;
                case 'ltv':
                    $totalRow['m0'] = ToolLogic::getRound($pay_total, $totalRow['reg_total']);
                    break;

                case 'pay_num':
                    $totalRow['m0'] = $pay_num;
                    break;
                case 'pay_total':
                    $totalRow['m0'] = $pay_total;
                    break;
                case 'pay_rate':
                    $totalRow['m0'] = ToolLogic::getPercent($pay_num, $totalRow['reg_total']);
                    break;
                case 'arpu':
                    $totalRow['m0'] = ToolLogic::getRound($pay_total, $pay_num);
                    break;
                default:
                    break;
            }
            $totalRow['reg_cost'] = ToolLogic::getRound($totalRow['cost'], $totalRow['reg_total']);
            $list = ToolLogic::arrSort($data, 'tmonth', 'asc');
        }
        return [
            'data' => array_values($list),
            'totalRow' => $totalRow
        ];


    }

}