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

<?php

// 玩家日志逻

namespace app\v1\logic\dataReport;

use app\v1\logic\tool\ToolLogic;
use plugin\saiadmin\basic\BaseLogic;
use plugin\saiadmin\exception\ApiException;
use support\think\Db;

class AnalyseLogic extends BaseLogic
{

    // 注册按日
    public function getRegDayDataList($where)
    {
        $params = $this->searchByAuth($where);
        $whereSql = $this->generateWhereSql($params);
        $field = "SUM(reg_total) AS reg_total, tdate,game_id";
        $baseData = $this->generateYearUnionList('base_total_day_', $where['reg_date'], $whereSql, $field, 'game_id,tdate');

        $data = [];
        foreach ($baseData as $row) {
            $tdate = $row['tdate'];
            $game_id = $row['game_id'];
            $data[$game_id][$tdate] = $row['reg_total'];
            $data[$game_id]['total'] = !empty($data[$game_id]['total']) ? $data[$game_id]['total'] + $row['reg_total'] : $row['reg_total'];
            $data[$game_id]['game_id'] = $game_id;
        }

        $list = array_values($data);

        $totalRow = ['game_id' => '合计'];
        foreach ($list as &$row) {
            $totalRow['total'] = !empty($totalRow['total']) ? $totalRow['total'] + $row['total'] : $row['total'];
            foreach ($row as $key => $value) {
                if ($key == 'total' || $key == 'game_id') {
                    continue;
                }
                $totalRow[$key] = !empty($totalRow[$key]) ? $totalRow[$key] + $value : $value;
            }
        }

        // 获取两个注册日期之间的日期列表
        $dateList = ToolLogic::getDatesBetween($where['reg_date'][0], $where['reg_date'][1]);
        $columnsData = [
            [
                'title' => '游戏ID',
                'dataIndex' => 'game_id',
                'width' => 120,
            ],
            [
                'title' => '游戏',
                'dataIndex' => 'game_name',
                'width' => 120,
            ],
            [
                'title' => '合计',
                'dataIndex' => 'total',
                'width' => 120,
            ]
        ];
        foreach ($dateList as $date) {
            $columnsData[] = [
                'title' => $date,
                'dataIndex' => $date,
                'width' => 120,
            ];
        }

        $result['data'] = $list;
        $result['totalRow'] = $totalRow;
        $result['columns'] = $columnsData;

        return $result;
    }

    // 注册按时

    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']) && is_array($params['reg_date'])) {
            $whereSql .= " AND tdate BETWEEN '{$params['reg_date'][0]}' AND '{$params['reg_date'][1]}'";
        }
        // 注册日期，不是数组，则认为是单个日期
        if (!empty($params['reg_date']) && !is_array($params['reg_date'])) {
            $whereSql .= " AND tdate = '{$params['reg_date']}'";
        }

        return $whereSql;
    }

    // 留存按日

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


    /*
     数据总览
    */

    public function getRegHourDataList($where)
    {
        $params = $this->searchByAuth($where);

        $field = "SUM(role_create_user) AS role_create_user, tdate,game_id,thour";
        $regDate = $where['reg_date'];
        // 将$regDate转化为年月格式，如202509
        $ym = date('Ym', strtotime($regDate));

        $tableName = 'base_total_hour_' . $ym;

        $whereSql = 'WHERE 1=1';
        if (!empty($params['game_id'])) {
            $whereSql .= " AND game_id IN(" . implode(',', $params['game_id']) . ")";
        }
        if (!empty($params['reg_date'])) {
            $whereSql .= " AND tdate = '" . $params['reg_date'] . "'";
        }
        $sql = "SELECT {$field} FROM {$tableName} {$whereSql}  GROUP BY game_id,thour,tdate";
        $baseData = Db::connect('db_data_report')->query($sql);
        $data = [];
        foreach ($baseData as &$row) {
            $game_id = $row['game_id'];
            $thour = $row['thour'];
            $data[$game_id]['h' . $thour] = $row['role_create_user'];
            $data[$game_id]['total'] = !empty($data[$game_id]['total']) ? $data[$game_id]['total'] + $row['role_create_user'] : $row['role_create_user'];
            $data[$game_id]['game_id'] = $game_id;
        }

        $list = array_values($data);

        $totalRow = ['game_id' => '合计'];
        foreach ($list as &$row) {
            $totalRow['total'] = !empty($totalRow['total']) ? $totalRow['total'] + $row['total'] : $row['total'];
            foreach ($row as $key => $value) {
                if ($key == 'total' || $key == 'game_id') {
                    continue;
                }
                $totalRow[$key] = !empty($totalRow[$key]) ? $totalRow[$key] + $value : $value;
            }
        }

        $result['data'] = $list;
        $result['totalRow'] = $totalRow;
        return $result;
    }

    /*
     数据总览（按月）
    */

    public function getRetentionDayDataList($where)
    {
        $params = $this->searchByAuth($where);
        // 构建whereSql
        $whereSql = '';
        if (!empty($params['game_id'])) {
            $whereSql .= " AND game_id IN(" . implode(',', $params['game_id']) . ")";
        }
        if (!empty($params['reg_date'])) {
            $whereSql .= " AND tdate BETWEEN '{$params['reg_date'][0]}' AND '{$params['reg_date'][1]}'";
        }

        $data = [];
        $totalRow = ['game_id' => '合计'];


        $baseField = "SUM(reg_total) AS reg_total, tdate,game_id";
        $activeField = "SUM(active_total) AS active_total, reg_date,game_id,days";

        // 1. 查询每天的注册数，根据注册时间，游戏
        $baseData = $this->generateYearUnionList('base_total_day_', $params['reg_date'], $whereSql, $baseField, 'game_id,tdate');
        $baseData = array_column($baseData, null, 'tdate');


        // 2. 查询每天的活跃数，根据注册时间，游戏，留存天数
        $activeWhereSql = str_replace("AND tdate", "AND reg_date", $whereSql);
        if (!empty($params['days'])) {
            $activeWhereSql .= " AND days = {$params['days']}";
        }

        $activeData = $this->generateYearUnionList('game_active_day_', $params['reg_date'], $activeWhereSql, $activeField, 'game_id,reg_date');
        $activeData = array_column($activeData, null, 'reg_date');

        foreach ($baseData as $row) {
            $game_id = $row['game_id'];
            $tdate = $row['tdate'];
            $data[$game_id]['game_id'] = $game_id;
            $data[$game_id][$tdate]['reg_total'] = $row['reg_total'];
            $data[$game_id][$tdate]['active_total'] = !empty($activeData[$tdate]['active_total']) ? $activeData[$tdate]['active_total'] : 0;
            $data[$game_id][$tdate]['retention_total'] = ToolLogic::getPercent($data[$game_id][$tdate]['active_total'], $data[$game_id][$tdate]['reg_total']);
            // 每日合计
            $data[$game_id]['total_reg_total'] = !empty($data[$game_id]['total_reg_total']) ? $data[$game_id]['total_reg_total'] + $data[$game_id][$tdate]['reg_total'] : $data[$game_id][$tdate]['reg_total'];
            $data[$game_id]['total_active_total'] = !empty($data[$game_id]['total_active_total']) ? $data[$game_id]['total_active_total'] + $data[$game_id][$tdate]['active_total'] : $data[$game_id][$tdate]['active_total'];


            // 底部合计
            $totalRow['total_reg_total'] = !empty($totalRow['total_reg_total']) ? $totalRow['total_reg_total'] + $data[$game_id][$tdate]['reg_total'] : $data[$game_id][$tdate]['reg_total'];
            $totalRow['total_active_total'] = !empty($totalRow['total_active_total']) ? $totalRow['total_active_total'] + $data[$game_id][$tdate]['active_total'] : $data[$game_id][$tdate]['active_total'];

            $totalRow[$tdate]['reg_total'] = !empty($totalRow[$tdate]['reg_total']) ? $totalRow[$tdate]['reg_total'] + $data[$game_id][$tdate]['reg_total'] : $data[$game_id][$tdate]['reg_total'];
            $totalRow[$tdate]['active_total'] = !empty($totalRow[$tdate]['active_total']) ? $totalRow[$tdate]['active_total'] + $data[$game_id][$tdate]['active_total'] : $data[$game_id][$tdate]['active_total'];

        }

        $data = array_values($data);
        foreach ($data as &$row) {
            foreach ($row as $key => $value) {
                if ($key == 'total_reg_total' || $key == 'game_id') {
                    continue;
                }
                if ($key == 'total_active_total') {
                    $row['total'] = ToolLogic::getPercent($row['total_active_total'], $row['total_reg_total']);
                } else {
                    $row[$key] = ToolLogic::getPercent($value['active_total'], $value['reg_total']);
                }
            }
        }

        foreach ($totalRow as $totalKey => &$row) {
            if ($totalKey == 'game_id' || $totalKey == 'total_active_total' || $totalKey == 'total_reg_total') {
                continue;
            }
            $totalRow[$totalKey] = ToolLogic::getPercent($row['active_total'], $row['reg_total']);

        }
        $totalRow['total'] = ToolLogic::getPercent($totalRow['total_active_total'], $totalRow['total_reg_total']);

        // 获取两个注册日期之间的日期列表
        $dateList = ToolLogic::getDatesBetween($where['reg_date'][0], $where['reg_date'][1]);
        $columnsData = [
            [
                'title' => '游戏ID',
                'dataIndex' => 'game_id',
                'width' => 120,
            ],
            [
                'title' => '游戏',
                'dataIndex' => 'game_name',
                'width' => 120,
            ],
            [
                'title' => '合计',
                'dataIndex' => 'total',
                'width' => 120,
            ]
        ];
        foreach ($dateList as $date) {
            $columnsData[] = [
                'title' => $date,
                'dataIndex' => $date,
                'width' => 120,
            ];
        }

        $result['data'] = $data;
        $result['totalRow'] = $totalRow;
        $result['columns'] = $columnsData;
        return $result;
    }

    public function getDataOverview($where)
    {
        $params = $this->searchByAuth($where);
        $whereSql = $this->generateWhereSql($params);
        // 基础汇总信息
        $field = "
      SUM(login_total) AS login_total,  -- 登陆总数
      SUM(reg_total) AS reg_total, -- 注册总数
      SUM(pay_total) AS pay_total, -- 付费总数
      SUM(pay_num) AS pay_num, -- 付费人数
      SUM(reg_login_total) AS reg_login_total, -- 注册登陆总数
      SUM(reg_pay_num) AS reg_pay_num, -- 注册付费数
      SUM(reg_pay_total) AS reg_pay_total, -- 注册付费金额
      SUM(old_login_total) AS old_login_total, -- 老用户登陆总数
      tdate";
        $baseData = $this->generateYearUnionList('base_total_day_', $where['reg_date'], $whereSql, $field, 'tdate');

        if (empty($baseData)) {
            return [
                'data' => [],
                'totalRow' => []
            ];
        }

        $actWhereSql = str_replace("AND tdate", "AND reg_date", $whereSql);
        $actWhereSql .= " AND days = 1";
        $actData = $this->generateYearUnionList('game_active_day_', $where['reg_date'], $actWhereSql, 'SUM(active_total) AS active_total,reg_date', 'reg_date');
        $actData = array_column($actData, null, 'reg_date');

        $totalRow = [
            'tdate' => '合计'
        ];

        foreach ($baseData as &$row) {
            $totalRow['login_total'] = !empty($totalRow['login_total']) ? $totalRow['login_total'] + $row['login_total'] : $row['login_total']; // 总用户 - 登陆总数
            $totalRow['reg_login_total'] = !empty($totalRow['reg_login_total']) ? $totalRow['reg_login_total'] + $row['reg_login_total'] : $row['reg_login_total']; // 新用户 - 注册登陆总数
            $totalRow['pay_num'] = !empty($totalRow['pay_num']) ? $totalRow['pay_num'] + $row['pay_num'] : $row['pay_num']; // 总用户 - 付费人数
            $totalRow['pay_total'] = !empty($totalRow['pay_total']) ? $totalRow['pay_total'] + $row['pay_total'] : $row['pay_total']; // 总用户 - 付费金额

            $totalRow['old_login_total'] = !empty($totalRow['old_login_total']) ? $totalRow['old_login_total'] + $row['old_login_total'] : $row['old_login_total']; // 老用户 - 登陆总数
            $totalRow['reg_total'] = !empty($totalRow['reg_total']) ? $totalRow['reg_total'] + $row['reg_total'] : $row['reg_total']; // 新用户 - 注册总数
            $totalRow['reg_pay_num'] = !empty($totalRow['reg_pay_num']) ? $totalRow['reg_pay_num'] + $row['reg_pay_num'] : $row['reg_pay_num']; // 新用户 - 注册付费数
            $totalRow['reg_pay_total'] = !empty($totalRow['reg_pay_total']) ? $totalRow['reg_pay_total'] + $row['reg_pay_total'] : $row['reg_pay_total']; // 新用户 - 注册付费金额
            $totalRow['active'] = !empty($totalRow['active']) ? $totalRow['active'] + $actData[$row['tdate']]['active_total'] ?? 0 : $actData[$row['tdate']]['active_total'] ?? 0; // 次留数


            $row['active'] = !empty($actData[$row['tdate']]['active_total']) ? $actData[$row['tdate']]['active_total'] : 0; // 次留数
            $row['arpu'] = ToolLogic::getRound($row['pay_total'], $row['pay_num']); // 总用户 - ARPU
            $row['pay_rate'] = ToolLogic::getPercent($row['pay_num'], $row['login_total']); // 总用户 - 付费率

            $row['reg_pay_rate'] = ToolLogic::getPercent($row['reg_pay_num'], $row['reg_total']); // 新用户 - 注册付费率
            $row['reg_arpu'] = ToolLogic::getRound($row['reg_pay_total'], $row['reg_pay_num']); // 新用户 - 注册付费ARPU
            $row['act_rate'] = ToolLogic::getPercent($row['active'], $row['reg_total']); // 新用户 - 次留率

            $row['old_pay_num'] = $row['pay_num'] - $row['reg_pay_num']; // 老用户 - 付费人数
            $row['old_pay_total'] = $row['pay_total'] - $row['reg_pay_total']; // 老用户 - 付费金额
            $row['old_pay_rate'] = ToolLogic::getPercent($row['old_pay_num'], $row['old_login_total']); // 老用户 - 付费率
            $row['old_arpu'] = ToolLogic::getRound($row['old_pay_total'], $row['old_pay_num']); // 老用户 - 付费ARPU

        }
        $totalRow['arpu'] = ToolLogic::getRound($totalRow['pay_total'], $totalRow['pay_num']); // 总用户 - ARPU
        $totalRow['pay_rate'] = ToolLogic::getPercent($totalRow['pay_num'], $totalRow['login_total']); // 总用户 - 付费率
        $totalRow['reg_pay_rate'] = ToolLogic::getPercent($totalRow['reg_pay_num'], $totalRow['reg_total']); // 新用户 - 注册付费率
        $totalRow['reg_arpu'] = ToolLogic::getRound($totalRow['reg_pay_total'], $totalRow['reg_pay_num']); // 新用户 - 注册付费ARPU
        $totalRow['old_pay_num'] = $totalRow['pay_num'] - $totalRow['reg_pay_num']; // 老用户 - 付费人数
        $totalRow['old_pay_total'] = $totalRow['pay_total'] - $totalRow['reg_pay_total']; // 老用户 - 付费金额
        $totalRow['old_pay_rate'] = ToolLogic::getPercent($totalRow['old_pay_num'], $totalRow['old_login_total']); // 老用户 - 付费率
        $totalRow['old_arpu'] = ToolLogic::getRound($totalRow['old_pay_total'], $totalRow['old_pay_num']); // 老用户 - 付费ARPU
        $totalRow['act_rate'] = ToolLogic::getPercent($totalRow['active'], $totalRow['reg_total']); // 次留率


        return [
            'data' => $baseData,
            'totalRow' => $totalRow
        ];
    }

    public function getDataOverviewByMonth($where)
    {
        $params = $this->searchByAuth($where);
        // 根据渠道名称获取渠道ID
        if (!empty($params['agent_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;
        }
        $whereSql = $this->generateWhereSql($params);
        // 基础汇总信息
        $field = "
      SUM(login_total) AS login_total,  -- 登陆总数
      SUM(reg_total) AS reg_total, -- 注册总数
      SUM(pay_total) AS pay_total, -- 付费总数
      SUM(pay_num) AS pay_num, -- 付费人数
      SUM(reg_login_total) AS reg_login_total, -- 注册登陆总数
      SUM(reg_pay_num) AS reg_pay_num, -- 注册付费数
      SUM(reg_pay_total) AS reg_pay_total, -- 注册付费金额
      SUM(old_login_total) AS old_login_total, -- 老用户登陆总数
      date_format(`tdate`, '%Y-%m') AS mdate";
        $baseData = $this->generateYearUnionList('base_total_day_', $where['reg_date'], $whereSql, $field, 'mdate');

        if (empty($baseData)) {
            return [
                'data' => [],
                'totalRow' => []
            ];
        }

        $actWhereSql = str_replace("AND tdate", "AND reg_date", $whereSql);
        $actWhereSql .= " AND days = 1";
        $actData = $this->generateYearUnionList('game_active_day_', $where['reg_date'], $actWhereSql, 'SUM(active_total) AS active_total,date_format(reg_date, "%Y-%m") AS mdate', 'mdate');
        $actData = array_column($actData, null, 'mdate');

        $totalRow = [
            'mdate' => '合计'
        ];

        foreach ($baseData as &$row) {
            $totalRow['login_total'] = !empty($totalRow['login_total']) ? $totalRow['login_total'] + $row['login_total'] : $row['login_total']; // 总用户 - 登陆总数
            $totalRow['reg_login_total'] = !empty($totalRow['reg_login_total']) ? $totalRow['reg_login_total'] + $row['reg_login_total'] : $row['reg_login_total']; // 新用户 - 注册登陆总数
            $totalRow['pay_num'] = !empty($totalRow['pay_num']) ? $totalRow['pay_num'] + $row['pay_num'] : $row['pay_num']; // 总用户 - 付费人数
            $totalRow['pay_total'] = !empty($totalRow['pay_total']) ? $totalRow['pay_total'] + $row['pay_total'] : $row['pay_total']; // 总用户 - 付费金额

            $totalRow['old_login_total'] = !empty($totalRow['old_login_total']) ? $totalRow['old_login_total'] + $row['old_login_total'] : $row['old_login_total']; // 老用户 - 登陆总数
            $totalRow['reg_total'] = !empty($totalRow['reg_total']) ? $totalRow['reg_total'] + $row['reg_total'] : $row['reg_total']; // 新用户 - 注册总数
            $totalRow['reg_pay_num'] = !empty($totalRow['reg_pay_num']) ? $totalRow['reg_pay_num'] + $row['reg_pay_num'] : $row['reg_pay_num']; // 新用户 - 注册付费数
            $totalRow['reg_pay_total'] = !empty($totalRow['reg_pay_total']) ? $totalRow['reg_pay_total'] + $row['reg_pay_total'] : $row['reg_pay_total']; // 新用户 - 注册付费金额
            $totalRow['active'] = !empty($totalRow['active']) ? $totalRow['active'] + $actData[$row['mdate']]['active_total'] ?? 0 : $actData[$row['mdate']]['active_total'] ?? 0; // 次留数


            $row['active'] = !empty($actData[$row['mdate']]['active_total']) ? $actData[$row['mdate']]['active_total'] : 0; // 次留数
            $row['arpu'] = ToolLogic::getRound($row['pay_total'], $row['pay_num']); // 总用户 - ARPU
            $row['pay_rate'] = ToolLogic::getPercent($row['pay_num'], $row['login_total']); // 总用户 - 付费率

            $row['reg_pay_rate'] = ToolLogic::getPercent($row['reg_pay_num'], $row['reg_total']); // 新用户 - 注册付费率
            $row['reg_arpu'] = ToolLogic::getRound($row['reg_pay_total'], $row['reg_pay_num']); // 新用户 - 注册付费ARPU
            $row['act_rate'] = ToolLogic::getPercent($row['active'], $row['reg_total']); // 新用户 - 次留率

            $row['old_pay_num'] = $row['pay_num'] - $row['reg_pay_num']; // 老用户 - 付费人数
            $row['old_pay_total'] = $row['pay_total'] - $row['reg_pay_total']; // 老用户 - 付费金额
            $row['old_pay_rate'] = ToolLogic::getPercent($row['old_pay_num'], $row['old_login_total']); // 老用户 - 付费率
            $row['old_arpu'] = ToolLogic::getRound($row['old_pay_total'], $row['old_pay_num']); // 老用户 - 付费ARPU

        }
        $totalRow['arpu'] = ToolLogic::getRound($totalRow['pay_total'], $totalRow['pay_num']); // 总用户 - ARPU
        $totalRow['pay_rate'] = ToolLogic::getPercent($totalRow['pay_num'], $totalRow['login_total']); // 总用户 - 付费率
        $totalRow['reg_pay_rate'] = ToolLogic::getPercent($totalRow['reg_pay_num'], $totalRow['reg_total']); // 新用户 - 注册付费率
        $totalRow['reg_arpu'] = ToolLogic::getRound($totalRow['reg_pay_total'], $totalRow['reg_pay_num']); // 新用户 - 注册付费ARPU
        $totalRow['old_pay_num'] = $totalRow['pay_num'] - $totalRow['reg_pay_num']; // 老用户 - 付费人数
        $totalRow['old_pay_total'] = $totalRow['pay_total'] - $totalRow['reg_pay_total']; // 老用户 - 付费金额
        $totalRow['old_pay_rate'] = ToolLogic::getPercent($totalRow['old_pay_num'], $totalRow['old_login_total']); // 老用户 - 付费率
        $totalRow['old_arpu'] = ToolLogic::getRound($totalRow['old_pay_total'], $totalRow['old_pay_num']); // 老用户 - 付费ARPU
        $totalRow['act_rate'] = ToolLogic::getPercent($totalRow['active'], $totalRow['reg_total']); // 次留率


        return [
            'data' => $baseData,
            'totalRow' => $totalRow
        ];
    }

    // 图表数据

    /**
     * 收入分析
     */
    public function getIncomeAnalysis($where)
    {
        $params = $this->searchByAuth($where);
        $tdate = $params['reg_date'] ?? date('Y-m-d');
        $ldate = date('Y-m-d', strtotime($tdate . ' -1 days'));
        $wdate = date('Y-m-d', strtotime($tdate . ' -7 days'));
        unset($params['reg_date']);
        $whereSql = $this->generateWhereSql($params);
        $whereSql .= " AND tdate IN ('{$tdate}','{$ldate}','{$wdate}')";


        $field = "
      SUM(pay_total) AS pay_total, -- 付费金额
      SUM(pay_num) AS pay_num, -- 付费人数
      SUM(login_total) AS login_total, -- 登陆总数
      game_id,
      tdate";

        $year = (int)date('Y', strtotime($tdate));
        $tableName = 'base_total_day_' . $year;

        $baseData = Db::connect('db_data_report')->query("SELECT {$field} FROM {$tableName} WHERE 1=1 {$whereSql} GROUP BY game_id,tdate");

        if (empty($baseData)) {
            return [
                'data' => [],
                'totalRow' => []
            ];
        }

        // 计算今日，所有游戏总充值
        $totalRow = [
            'game_id' => '合计',
        ];


        $allTotal = 0;
        $data = [];
        foreach ($baseData as &$row) {
            // 查询日期数据
            if ($row['tdate'] == $tdate) {
                $gameId = $row['game_id'];
                $data[$gameId]['game_id'] = $gameId;
                $data[$gameId]['login_total'] = $row['login_total']; // DAU
                $data[$gameId]['pay_total'] = $row['pay_total'] ?? 0; // 今日充值
                $data[$gameId]['pay_num'] = $row['pay_num'] ?? 0; // 今日付费人数
                $data[$gameId]['pay_arpu'] = ToolLogic::getRound($row['pay_total'], $row['pay_num']);  // 今日付费ARPU


                $allTotal += $row['pay_total'];

                $totalRow['pay_total'] = !empty($totalRow['pay_total']) ? $totalRow['pay_total'] + $row['pay_total'] : $row['pay_total']; // 今日充值
                $totalRow['pay_num'] = !empty($totalRow['pay_num']) ? $totalRow['pay_num'] + $row['pay_num'] : $row['pay_num']; // 今日付费人数
                $totalRow['login_total'] = !empty($totalRow['login_total']) ? $totalRow['login_total'] + $row['login_total'] : $row['login_total']; // 今日登陆人数
            }
            // 查询昨天数据
            if ($row['tdate'] == $ldate) {
                $gameId = $row['game_id'];
                $data[$gameId]['yestoday_pay_total'] = $row['pay_total'] ?? 0; // 昨日充值
                $totalRow['yestoday_pay_total'] = !empty($totalRow['yestoday_pay_total']) ? $totalRow['yestoday_pay_total'] + $row['pay_total'] : $row['pay_total']; // 昨日充值

            }
            // 查询7天前数据
            if ($row['tdate'] == $wdate) {
                $gameId = $row['game_id'];
                $data[$gameId]['week_pay_total'] = $row['pay_total'] ?? 0; // 7天前充值
                $totalRow['week_pay_total'] = !empty($totalRow['week_pay_total']) ? $totalRow['week_pay_total'] + $row['pay_total'] : $row['pay_total']; // 7天前充值

            }
        }

        $totalRow['pay_arpu'] = ToolLogic::getRound($totalRow['pay_total'], $totalRow['pay_num']); // 今日充值ARPU
        $totalRow['pay_ratio'] = ToolLogic::getPercent($totalRow['pay_total'], $allTotal); // 今日充值占比
        $totalRow['pay_increase_yestoday'] = $totalRow['pay_total'] - $totalRow['yestoday_pay_total']; // 昨日增长
        $totalRow['pay_increase_week'] = $totalRow['pay_total'] - $totalRow['week_pay_total']; // 7天增长

        foreach ($data as &$row) {

            $row['pay_ratio'] = ToolLogic::getPercent($row['pay_total'], $allTotal); // 今日充值占比
            $row['pay_increase_yestoday'] = $row['pay_total'] - $row['yestoday_pay_total']; // 昨日增长
            $row['pay_increase_week'] = $row['pay_total'] - $row['week_pay_total']; // 7天增长
        }


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

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

    /**
     * 付费留存
     * 1. 登录日志表：sdk_login_log_, 查找符合注册时间的用户， 获取用户id
     *
     * inner join  ON login.uid = order.uid,
     *
     * 2. 根据用户id，去订单表，筛选出，次留、2留的日期为充值日期， 从而获取充值人数
     * 3. 根据注册时间，获取总的pay_num
     */
    public function getPayRetention($where)
    {
        $params = $this->searchByAuth($where);
        // $whereSql = $this->generateWhereSql($params);
        // 判断不能超过90天查询
        $days = ToolLogic::getDays($where['reg_date'][0], $where['reg_date'][1]);
        if ($days > 90) {
            throw new ApiException('时间查询范围请勿超过90天');
        }
        $whereRaw = '1=1';
        if (!empty($params['game_id'])) {
            $whereRaw .= " AND game_id IN(" . implode(',', $params['game_id']) . ")";
        }
        if (!empty($params['media_id'])) {
            $whereRaw .= " AND media_id = ({$params['media_id']})";
        }
        if (!empty($params['agent_id'])) {
            $whereRaw .= " AND agent_id IN(" . implode(',', $params['agent_id']) . ")";
        }


        // 1. 根据注册时间，获取注册付费人数
        $payData = Db::connect('db_game_log')->query("
        SELECT 
          SUBSTRING(reg_date,1,10) as dimension,  -- 注册日期
          COUNT(DISTINCT uid) as pay_num  -- 注册付费人数
        FROM sdk_order_success
          WHERE {$whereRaw} AND reg_date BETWEEN '{$params['reg_date'][0]} 00:00:00' AND '{$params['reg_date'][1]} 23:59:59'
            GROUP BY 
              dimension
      ");
        $payDataMap = array_column($payData, null, 'dimension');

        // 2. 计算留存付费的人数
        $sqlArr = [];
        $medate = date("Y-m-d", strtotime("+ 60 day", strtotime($params['reg_date'][1])));
        $tableNames = ToolLogic::getMonthlyTableNames('sdk_login_log_', $params['reg_date'][0], $medate);
        foreach ($tableNames as $tableName) {
            $sqlArr[] = "
          SELECT  
            CONCAT(FROM_UNIXTIME(reg_time, '%Y-%m-%d'), '|', TIMESTAMPDIFF(DAY, FROM_UNIXTIME(reg_time), FROM_UNIXTIME(login_time))) AS dimension, -- 注册日期|留存天数
            FROM_UNIXTIME(reg_time, '%Y-%m-%d') AS reg_date, -- 注册日期
            TIMESTAMPDIFF(DAY, FROM_UNIXTIME(reg_time), FROM_UNIXTIME(login_time)) AS day_num, -- 留存天数
            COUNT(DISTINCT a.user_name) AS remain_num -- 留存人数
          FROM {$tableName} AS a
            INNER JOIN (
              SELECT 
                DISTINCT user_name
              FROM sdk_order_success
               WHERE 
                {$whereRaw} AND reg_date BETWEEN '{$params['reg_date'][0]} 00:00:00' AND '{$params['reg_date'][1]} 23:59:59'
            )  AS b ON a.user_name = b.user_name
           WHERE
              {$whereRaw} AND reg_time BETWEEN " . strtotime($params['reg_date'][0] . " 00:00:00") . " AND " . strtotime($params['reg_date'][1] . " 23:59:59") . "
          GROUP BY dimension
        ";
        }

        $sql = implode(" UNION ALL ", $sqlArr);


        $remainData = Db::connect('db_game_log')->query($sql);
        $remainData = $remainData ? array_column($remainData, null, "dimension") : [];

        $regDateRange = ToolLogic::getDatesBetween($params['reg_date'][0], $params['reg_date'][1]);
        $remainRange = [];
        for ($i = 2; $i <= 30; $i++) {
            $remainRange[] = $i;
        }
        $remainRange = array_merge($remainRange, [35, 40, 45, 50, 55, 60]);
        $data = [];
        foreach ($regDateRange as $regDate) {
            $row['reg_date'] = $regDate;
            $row['reg_pay_num'] = $payDataMap[$regDate]['pay_num'] ?? 0;
            foreach ($remainRange as $remainNum) {
                $dimension = $regDate . "|" . ($remainNum - 1);
                $row['remain_' . $remainNum] = $remainData[$dimension]['remain_num'] ?? 0;
            }
            $data[] = $row;
        }


        // 合计
        $totalRow = [
            'reg_date' => '合计',
        ];

        // 计算合计
        if ($data) foreach ($data as $item) {

            $totalRow['reg_pay_num'] = $totalRow['reg_pay_num'] ?? 0;
            $totalRow['reg_pay_num'] += $item['reg_pay_num'];

            foreach ($remainRange as $remainNum) {
                $key = 'remain_' . $remainNum;

                $totalRow[$key] = $totalRow[$key] ?? 0;
                $totalRow[$key] += $item[$key];
            }
        }

        // 计算比率
        if ($params['show_type'] == 'rate') {
            if ($data) foreach ($remainRange as $remainNum) {
                $key = 'remain_' . $remainNum;
                $totalRow[$key] = ToolLogic::getPercent($totalRow[$key], $totalRow['reg_pay_num']);
                foreach ($data as &$val) {
                    $val[$key] = ToolLogic::getPercent($val[$key], $val['reg_pay_num']);
                }
            }
        }

        return [
            'data' => $data,
            'totalRow' => $totalRow
        ];
    }


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

    public function getChartData($where)
    {
        $params = $this->searchByAuth($where);
        $type = $params['compare_type'] ?? 'day';

        // 如果按照天对比
        if ($type === 'day') {
            $data1 = [];
            $data2 = [];
            $data1Res = [];
            $data2Res = [];

            $field = "
          SUM(reg_total) as reg, -- 注册人数
          SUM(old_login_total) as login, -- 老用户登陆人数
          SUM(pay_total) as pay, -- 付费金额
          tdate
        ";

            // 获取对比日期1的数据, 注册、登录、充值
            $params['reg_date'] = $params['compare_date1'];
            $whereSql1 = $this->generateWhereSql($params);

            $baseData1 = $this->generateYearUnionList('base_total_day_', $params['compare_date1'], $whereSql1, $field, 'tdate');

            foreach ($baseData1 as $item) {
                $data1Res[$item['tdate']]['reg'] = $item['reg'];
                $data1Res[$item['tdate']]['pay'] = $item['pay'];
                $data1Res[$item['tdate']]['login'] = $item['login'];
            }


            // 获取对比日期2的数据, 注册、登录、充值
            $params['reg_date'] = $params['compare_date2'];
            $whereSql2 = $this->generateWhereSql($params);
            $baseData2 = $this->generateYearUnionList('base_total_day_', $params['compare_date2'], $whereSql2, $field, 'tdate');


            foreach ($baseData2 as $item) {
                $data2Res[$item['tdate']]['reg'] = $item['reg'];
                $data2Res[$item['tdate']]['pay'] = $item['pay'];
                $data2Res[$item['tdate']]['login'] = $item['login'];
            }


            // 获取消耗数据1
            $params['reg_date'] = $params['compare_date1'];
            $costWhereSql1 = $this->generateWhereSql($params);
            echo $costWhereSql1;
            $costData1 = Db::connect('db_advert')->query("
          SELECT SUM(money) as money, tdate
          FROM media_cost
          WHERE 1=1 {$costWhereSql1}
          GROUP BY tdate
        ");

            foreach ($costData1 as $item) {
                $data1Res[$item['tdate']]['cost'] = $item['money'];
            }


            // 获取消耗数据2
            $params['reg_date'] = $params['compare_date2'];
            $costWhereSql2 = $this->generateWhereSql($params);
            $costData2 = Db::connect('db_advert')->query("
          SELECT SUM(money) as money, tdate
          FROM media_cost
          WHERE 1=1 {$costWhereSql2}
          GROUP BY tdate
        ");

            foreach ($costData2 as $item) {
                $data2Res[$item['tdate']]['cost'] = $item['money'];
            }


            // 获取两个日期之间的所有日期
            $day1Date = ToolLogic::getDatesBetween($params['compare_date1'][0], $params['compare_date1'][1]);
            $day2Date = ToolLogic::getDatesBetween($params['compare_date2'][0], $params['compare_date2'][1]);


            foreach ($day1Date as $k => $d) {

                $data1['days'][$k] = $d;

                $data1['pay'][$k] = !empty($data1Res[$d]['pay']) ? round((float)$data1Res[$d]['pay'], 2) : 0;
                $data1['cost'][$k] = !empty($data1Res[$d]['cost']) ? round((float)$data1Res[$d]['cost'], 2) : 0;
                $data1['reg'][$k] = !empty($data1Res[$d]['reg']) ? (int)$data1Res[$d]['reg'] : 0;
                $data1['login'][$k] = !empty($data1Res[$d]['login']) ? (int)$data1Res[$d]['login'] : 0;
            }

            foreach ($day2Date as $k => $d) {

                $data2['days'][$k] = $d;
                $data2['pay'][$k] = !empty($data2Res[$d]['pay']) ? round((float)$data2Res[$d]['pay'], 2) : 0;
                $data2['cost'][$k] = !empty($data2Res[$d]['cost']) ? round((float)$data2Res[$d]['cost'], 2) : 0;
                $data2['reg'][$k] = !empty($data2Res[$d]['reg']) ? (int)$data2Res[$d]['reg'] : 0;
                $data2['login'][$k] = !empty($data2Res[$d]['login']) ? (int)$data2Res[$d]['login'] : 0;
            }

            // 格式化x轴的时间
            $forData = [];
            $days1 = [];
            $days2 = [];
            if (count($data1['days']) > count($data2['days'])) {
                $forData = $data1['days'];
            } else {
                $forData = $data2['days'];
            }


            for ($i = 0; $i < count($forData); $i++) {
                $d1 = !empty($data1['days'][$i]) ? date("m-d", strtotime($data1['days'][$i])) : "";
                $d2 = !empty($data2['days'][$i]) ? date("m-d", strtotime($data2['days'][$i])) : "";
                $days1[] = $d1;
                $days2[] = $d2;
            }


            $data1['series'] = "日期1";
            $data2['series'] = "日期2";


            return [
                'data1' => $data1,
                'data2' => $data2,
                'days1' => $days1,
                'days2' => $days2
            ];
        }


        // 如果按小时对比
        if ($type === 'hour') {
            $data1 = [];
            $data2 = [];
            $data1Res = [];
            $data2Res = [];

            $field = "
          SUM(reg_total) as reg, -- 注册人数
          SUM(old_login_total) as login, -- 老用户登陆人数
          SUM(pay_total) as pay, -- 付费金额
          SUM(cost) as cost, -- 消耗金额
          thour
        ";

            // 获取对比日期1的数据, 注册、登录、充值
            $params['reg_date'] = $params['compare_date1_date'];
            $whereSql1 = $this->generateWhereSql($params);
            $baseData1 = $this->generateMonthUnionList('base_total_hour_', $params['compare_date1'], $whereSql1, $field, 'thour');

            foreach ($baseData1 as $item) {
                $data1Res[$item['thour']]['reg'] = $item['reg'];
                $data1Res[$item['thour']]['pay'] = $item['pay'];
                $data1Res[$item['thour']]['login'] = $item['login'];
                $data1Res[$item['thour']]['cost'] = $item['cost'];
            }

            // 获取对比日期2的数据, 注册、登录、充值
            $params['reg_date'] = $params['compare_date2_date'];
            $whereSql2 = $this->generateWhereSql($params);
            echo $whereSql2;
            $baseData2 = $this->generateMonthUnionList('base_total_hour_', $params['compare_date2'], $whereSql2, $field, 'thour');

            foreach ($baseData2 as $item) {
                $data2Res[$item['thour']]['reg'] = $item['reg'];
                $data2Res[$item['thour']]['pay'] = $item['pay'];
                $data2Res[$item['thour']]['login'] = $item['login'];
                $data2Res[$item['thour']]['cost'] = $item['cost'];
            }


            $ndate = date("Y-m-d");
            $hour = date("H");
            print_r($data1Res);
            for ($h = 0; $h < 24; $h++) {
                if ($params['compare_date1_date'] == $ndate && $h > $hour) {
                    continue;
                }
                $data1['hour'][$h] = $h;
                $data1['pay'][$h] = !empty($data1Res[$h]) ? round((float)$data1Res[$h]['pay'] ?? 0, 2) : 0;
                $data1['cost'][$h] = !empty($data1Res[$h]) ? round((float)$data1Res[$h]['cost'] ?? 0, 2) : 0;
                $data1['reg'][$h] = !empty($data1Res[$h]) ? (int)$data1Res[$h]['reg'] ?? 0 : 0;
                $data1['login'][$h] = !empty($data1Res[$h]) ? (int)$data1Res[$h]['login'] ?? 0 : 0;
            }

            for ($h = 0; $h < 24; $h++) {
                if ($params['compare_date2_date'] == $ndate && $h > $hour) {
                    continue;
                }
                $data2['hour'][$h] = $h;
                $data2['pay'][$h] = !empty($data2Res[$h]) ? round((float)$data2Res[$h]['pay'] ?? 0, 2) : 0;
                $data2['cost'][$h] = !empty($data2Res[$h]) ? round((float)$data2Res[$h]['cost'] ?? 0, 2) : 0;
                $data2['reg'][$h] = !empty($data2Res[$h]) ? (int)$data2Res[$h]['reg'] ?? 0 : 0;
                $data2['login'][$h] = !empty($data2Res[$h]) ? (int)$data2Res[$h]['login'] ?? 0 : 0;
            }
            $hour = [];
            for ($h = 0; $h < 24; $h++) {
                $hour[] = $h;
            }

            $data1['series'] = $params['compare_date1_date'];
            $data2['series'] = $params['compare_date2_date'];

            return [
                'data1' => $data1,
                'data2' => $data2,
                'days1' => $hour,
                'days2' => $hour
            ];
        }
    }


    // 生成wheresql

    public function generateMonthUnionList($namePrefix, $regDate, $whereSql = '', $field = '*', $group = null)
    {
        $tableNames = ToolLogic::getMonthlyTableNames($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;
    }
}