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

<?php

// 运营分析
namespace app\v1\logic\dataReport;

use plugin\saiadmin\basic\BaseLogic;
use plugin\saiadmin\exception\ApiException;
use support\think\Db;

class AnalyseLogic extends BaseLogic
{
    // 注册按日
    public function getRegDayDataList($params)
    {
        $params = $this->searchByAuth($params);
        $whereRaw = $this->getCommonWhereRaw($params);
        $field = "SUM(reg_total) AS reg_total, tdate, game_id";
        $yearRange = getYearRange($params['tdate'][0], $params['tdate'][1]);
        $baseData = $this->generateUnionList('base_total_day', $yearRange, $whereRaw, $field, 'game_id,tdate');

        $data = [];
        foreach ($baseData as $row) {
            $game_id = $row['game_id'];
            $data[$game_id][$row['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 = getDateRange($params['tdate'][0], $params['tdate'][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 getRegHourDataList($params)
    {
        $params = $this->searchByAuth($params);
        $regDate = $params['tdate'];
        $params['tdate'] = [$params['tdate'], $params['tdate']];
        $field = "SUM(reg_total) AS reg_total, tdate,game_id,thour";

        // 将$regDate转化为年月格式，如202509
        $ym = date('Ym', strtotime($regDate));
        $tableName = 'base_total_hour_' . $ym;
        $whereRaw = $this->getCommonWhereRaw($params);
        $sql = "SELECT {$field} FROM {$tableName} where {$whereRaw}  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['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;
            }
        }

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

    /*
     数据总览（按月）
    */
    public function getRetentionDayDataList($where)
    {
        $params = $this->searchByAuth($where);
        // 构建whereSql
        $whereRaw = $this->getCommonWhereRaw($params);

        $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. 查询每天的注册数，根据注册时间，游戏
        $yearRange = getYearRange($params['tdate'][0], $params['tdate'][1]);
        $baseData = $this->generateUnionList('base_total_day', $yearRange, $whereRaw, $baseField, 'game_id,tdate');
        $baseData = array_column($baseData, null, 'tdate');

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

        $activeData = $this->generateUnionList('game_active_day', $yearRange, $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'] = 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'] = getPercent($row['total_active_total'], $row['total_reg_total']);
                } else {
                    $row[$key] = 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] = getPercent($row['active_total'], $row['reg_total']);

        }
        $totalRow['total'] = getPercent($totalRow['total_active_total'] ?? 0, $totalRow['total_reg_total'] ?? 0);

        // 获取两个注册日期之间的日期列表
        $dateList = getDateRange($where['tdate'][0], $where['tdate'][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);

        $whereRaw = $this->getCommonWhereRaw($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";
        $yearRange = getYearRange($params['tdate'][0], $params['tdate'][1]);

        $baseData = $this->generateUnionList('base_total_day', $yearRange, $whereRaw, $field, 'tdate');

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

        $actWhereSql = str_replace("tdate", "reg_date", $whereRaw);
        $actWhereSql .= " AND days = 1";
        $actData = $this->generateUnionList('game_active_day', $yearRange, $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'] = getRound($row['pay_total'], $row['pay_num']); // 总用户 - ARPU
            $row['pay_rate'] = getPercent($row['pay_num'], $row['login_total']); // 总用户 - 付费率

            $row['reg_pay_rate'] = getPercent($row['reg_pay_num'], $row['reg_total']); // 新用户 - 注册付费率
            $row['reg_arpu'] = getRound($row['reg_pay_total'], $row['reg_pay_num']); // 新用户 - 注册付费ARPU
            $row['act_rate'] = 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'] = getPercent($row['old_pay_num'], $row['old_login_total']); // 老用户 - 付费率
            $row['old_arpu'] = getRound($row['old_pay_total'], $row['old_pay_num']); // 老用户 - 付费ARPU

        }
        $totalRow['arpu'] = getRound($totalRow['pay_total'], $totalRow['pay_num']); // 总用户 - ARPU
        $totalRow['pay_rate'] = getPercent($totalRow['pay_num'], $totalRow['login_total']); // 总用户 - 付费率
        $totalRow['reg_pay_rate'] = getPercent($totalRow['reg_pay_num'], $totalRow['reg_total']); // 新用户 - 注册付费率
        $totalRow['reg_arpu'] = 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'] = getPercent($totalRow['old_pay_num'], $totalRow['old_login_total']); // 老用户 - 付费率
        $totalRow['old_arpu'] = getRound($totalRow['old_pay_total'], $totalRow['old_pay_num']); // 老用户 - 付费ARPU
        $totalRow['act_rate'] = 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 name = '{$params['agent_name']}'");
            $params['agent_id'] = $agentData[0]['id'] ?? 0;
        }
        $whereRaw = $this->getCommonWhereRaw($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";
        $yearRange = getYearRange($params['tdate'][0], $params['tdate'][1]);

        $baseData = $this->generateUnionList('base_total_day', $yearRange, $whereRaw, $field, 'mdate');

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

        $actWhereSql = str_replace("tdate", "reg_date", $whereRaw);
        $actWhereSql .= " AND days = 1";
        $actData = $this->generateUnionList('game_active_day', $yearRange, $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'] = getRound($row['pay_total'], $row['pay_num']); // 总用户 - ARPU
            $row['pay_rate'] = getPercent($row['pay_num'], $row['login_total']); // 总用户 - 付费率

            $row['reg_pay_rate'] = getPercent($row['reg_pay_num'], $row['reg_total']); // 新用户 - 注册付费率
            $row['reg_arpu'] = getRound($row['reg_pay_total'], $row['reg_pay_num']); // 新用户 - 注册付费ARPU
            $row['act_rate'] = 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'] = getPercent($row['old_pay_num'], $row['old_login_total']); // 老用户 - 付费率
            $row['old_arpu'] = getRound($row['old_pay_total'], $row['old_pay_num']); // 老用户 - 付费ARPU

        }
        $totalRow['arpu'] = getRound($totalRow['pay_total'], $totalRow['pay_num']); // 总用户 - ARPU
        $totalRow['pay_rate'] = getPercent($totalRow['pay_num'], $totalRow['login_total']); // 总用户 - 付费率
        $totalRow['reg_pay_rate'] = getPercent($totalRow['reg_pay_num'], $totalRow['reg_total']); // 新用户 - 注册付费率
        $totalRow['reg_arpu'] = 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'] = getPercent($totalRow['old_pay_num'], $totalRow['old_login_total']); // 老用户 - 付费率
        $totalRow['old_arpu'] = getRound($totalRow['old_pay_total'], $totalRow['old_pay_num']); // 老用户 - 付费ARPU
        $totalRow['act_rate'] = getPercent($totalRow['active'], $totalRow['reg_total']); // 次留率


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


    /**
     * 收入分析
     */
    public function getIncomeAnalysis($where)
    {
        $params = $this->searchByAuth($where);
        $tdate = $params['tdate'] ?? date('Y-m-d');
        $ldate = date('Y-m-d', strtotime($tdate . ' -1 days'));
        $wdate = date('Y-m-d', strtotime($tdate . ' -7 days'));
        unset($params['tdate']);
        $whereRaw = $this->getCommonWhereRaw($params);
        $whereRaw .= " 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 {$whereRaw} GROUP BY game_id,tdate");

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

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

        $allTotal = 0;
        $data = [];
        foreach ($baseData as &$row) {
            $gameId = $row['game_id'];
            // 查询日期数据
            if ($row['tdate'] == $tdate) {
                $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'] = 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']; // 今日登陆人数
            }
            // 查询昨天数据
            $data[$gameId]['yestoday_pay_total'] = 0;
            if ($row['tdate'] == $ldate) {
                $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天前数据
            $data[$gameId]['week_pay_total'] = 0;
            if ($row['tdate'] == $wdate) {
                $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'] = getRound($totalRow['pay_total'], $totalRow['pay_num']); // 今日充值ARPU
        $totalRow['pay_ratio'] = 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天增长

        if($data)foreach ($data as &$row) {
            $row['pay_ratio'] = 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);

        // 判断不能超过90天查询
        $days = getDateNum($where['tdate'][0], $where['tdate'][1]);
        if ($days > 90) {
            throw new ApiException('时间查询范围请勿超过90天');
        }
        $whereParams = $params;
        unset($whereParams['tdate']);
        $whereRaw = $this->getCommonWhereRaw($whereParams);

        $whereRaw = str_replace('tdate', 'reg_date', $whereRaw);

        // 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['tdate'][0]} 00:00:00' AND '{$params['tdate'][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['tdate'][1])));
        $monthRange = getMonthRange($params['tdate'][0], $medate);
        $newMonth = date("Ym");
        foreach ($monthRange as $month) {
            if($month>$newMonth){
                break;
            }
            $tableName = 'sdk_login_log_' . $month;
            $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['tdate'][0]} 00:00:00' AND '{$params['tdate'][1]} 23:59:59'
                )  AS b ON a.user_name = b.user_name
               WHERE
                  {$whereRaw} AND reg_time BETWEEN " . strtotime($params['tdate'][0] . " 00:00:00") . " AND " . strtotime($params['tdate'][1] . " 23:59:59") . "
              GROUP BY dimension
            ";
        }

        $sql = implode(" UNION ALL ", $sqlArr);
        // echo $sql . "\n";
        $remainData = Db::connect('db_game_log')->query($sql);
        $remainData = $remainData ? array_column($remainData, null, "dimension") : [];

        $regDateRange = getDateRange($params['tdate'][0], $params['tdate'][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'] ?? '';
            foreach ($remainRange as $remainNum) {
                $dimension = $regDate . "|" . ($remainNum - 1);
                $row['remain_' . $remainNum] = $remainData[$dimension]['remain_num'] ?? '';
            }
            $data[] = $row;
        }


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

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

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

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

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

        // 计算比率
        if ($params['show_type'] == 'rate') {
            if ($data) foreach ($remainRange as $remainNum) {
                $key = 'remain_' . $remainNum;
                $totalRow[$key] = $totalRow[$key] ? getPercent($totalRow[$key], $totalRow['reg_pay_num']) : '';
                foreach ($data as &$val) {
                    $val[$key] = $val[$key] ? 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['tdate'] = $params['compare_date1'];
            $whereRaw1 = $this->getCommonWhereRaw($params);
            $yearRange = getYearRange($params['compare_date1'][0], $params['compare_date1'][1]);
            $baseData1 = $this->generateUnionList('base_total_day', $yearRange, $whereRaw1, $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['tdate'] = $params['compare_date2'];
            $whereRaw2 = $this->getCommonWhereRaw($params);
            $yearRange = getYearRange($params['compare_date2'][0], $params['compare_date2'][1]);
            $baseData2 = $this->generateUnionList('base_total_day', $yearRange, $whereRaw2, $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['tdate'] = $params['compare_date1'];
            $costWhereSql1 = $this->getCommonWhereRaw($params);

            $costData1 = Db::connect('db_advert')->query("
          SELECT SUM(money) as money, tdate
          FROM media_cost
          WHERE {$costWhereSql1}
          GROUP BY tdate
        ");

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


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

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


            // 获取两个日期之间的所有日期
            $day1Date = getDateRange($params['compare_date1'][0], $params['compare_date1'][1]);
            $day2Date = getDateRange($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轴的时间
            $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['tdate'] = $params['compare_date1_date'];
            $whereRaw1 = $this->getCommonWhereRaw($params);
            $monthRange = getMonthRange($params['compare_date1'][0], $params['compare_date1'][1]);
            $baseData1 = $this->generateUnionList('base_total_hour', $monthRange, $whereRaw1, $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['tdate'] = $params['compare_date2_date'];
            $whereRaw2 = $this->getCommonWhereRaw($params);
            $monthRange2 = getMonthRange($params['compare_date2'][0], $params['compare_date2'][1]);
            $baseData2 = $this->generateUnionList('base_total_hour', $monthRange2, $whereRaw2, $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");

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

    // 支付成功率（包含对比数据）
    public function getPaySuccessRate($where)
    {
        $params = $this->searchByAuth($where);
        $whereRaw = $this->getCommonWhereRaw($params);
        $type = $params['type'] ?? 'day';

        if ($type == 'day') {
            $data = $this->getPaySuccessRateByDay($whereRaw);
            // 添加对比数据
            $data['comparison'] = $this->getDayComparisonFromPaySuccessRate($params);
            // 添加支付渠道下单比例和数量
            $data['channelData'] = $this->getChannelPaySuccessRate($params);

            return $data;
        } elseif ($type == 'week') {
            $data = $this->getPaySuccessRateByWeek($whereRaw);
            // 添加对比数据
            $data['comparison'] = $this->getWeekComparisonFromPaySuccessRate($params);
            return $data;
        }

        return [];
    }

    /**
     * 获取支付渠道下单比例和数量
     */
    private function getChannelPaySuccessRate($params)
    {
        
    }


    /**
     * 从支付成功率接口获取日对比数据（今日vs昨日）
     */
    private function getDayComparisonFromPaySuccessRate($params)
    {
        // 复用支付成功率接口的数据
        $paySuccessData = $this->getPaySuccessRateByDay($this->getCommonWhereRaw($params));
        
        // 从支付成功率数据中提取今日和昨日的数据
        $todayOrderNum = $paySuccessData['orderNum'];
        $todayPayNum = $paySuccessData['successNum'];
        $todayPaySuccessRate = $paySuccessData['paySuccessRate'];
        
        // 计算昨日数据（从表格数据中获取）
        $tableData = $paySuccessData['data'];
        $yesterdayOrderNum = 0;
        $yesterdayPayNum = 0;
        foreach ($tableData as $row) {
            $yesterdayOrderNum += $row['yesterdayOrderNum'];
            $yesterdayPayNum += $row['yesterdayPayNum'];
        }
        $yesterdayPaySuccessRate = $yesterdayOrderNum > 0 ? getRound($yesterdayPayNum, $yesterdayOrderNum) : 0;

        // 计算变化百分比和趋势
        $orderChange = $this->calculateChangePercentage($todayOrderNum, $yesterdayOrderNum);
        $payChange = $this->calculateChangePercentage($todayPayNum, $yesterdayPayNum);
        $rateChange = $this->calculateChangePercentage($todayPaySuccessRate, $yesterdayPaySuccessRate);

        return [
            'type' => 'day',
            'current' => [
                'orderNum' => $todayOrderNum,
                'payNum' => $todayPayNum,
                'paySuccessRate' => $todayPaySuccessRate,
                'label' => '今日'
            ],
            'previous' => [
                'orderNum' => $yesterdayOrderNum,
                'payNum' => $yesterdayPayNum,
                'paySuccessRate' => $yesterdayPaySuccessRate,
                'label' => '昨日'
            ],
            'changes' => [
                'orderChange' => array_merge($orderChange, ['label' => '较昨日']),
                'payChange' => array_merge($payChange, ['label' => '较昨日']),
                'rateChange' => array_merge($rateChange, ['label' => '较昨日'])
            ]
        ];
    }

    /**
     * 从支付成功率接口获取周对比数据（本周vs上周）
     */
    private function getWeekComparisonFromPaySuccessRate($params)
    {
        // 复用支付成功率接口的数据
        $paySuccessData = $this->getPaySuccessRateByWeek($this->getCommonWhereRaw($params));
        
        // 从支付成功率数据中提取本周和上周的数据
        $thisWeekOrderNum = $paySuccessData['orderNum'];
        $thisWeekPayNum = $paySuccessData['successNum'];
        $thisWeekPaySuccessRate = $paySuccessData['paySuccessRate'];
        
        // 计算上周数据（从表格数据中获取）
        $tableData = $paySuccessData['data'];
        $lastWeekOrderNum = 0;
        $lastWeekPayNum = 0;
        foreach ($tableData as $row) {
            $lastWeekOrderNum += $row['yesterdayOrderNum'];
            $lastWeekPayNum += $row['yesterdayPayNum'];
        }
        $lastWeekPaySuccessRate = $lastWeekOrderNum > 0 ? getRound($lastWeekPayNum, $lastWeekOrderNum) : 0;

        // 计算变化百分比和趋势
        $orderChange = $this->calculateChangePercentage($thisWeekOrderNum, $lastWeekOrderNum);
        $payChange = $this->calculateChangePercentage($thisWeekPayNum, $lastWeekPayNum);
        $rateChange = $this->calculateChangePercentage($thisWeekPaySuccessRate, $lastWeekPaySuccessRate);

        return [
            'type' => 'week',
            'current' => [
                'orderNum' => $thisWeekOrderNum,
                'payNum' => $thisWeekPayNum,
                'paySuccessRate' => $thisWeekPaySuccessRate,
                'label' => '本周'
            ],
            'previous' => [
                'orderNum' => $lastWeekOrderNum,
                'payNum' => $lastWeekPayNum,
                'paySuccessRate' => $lastWeekPaySuccessRate,
                'label' => '上周'
            ],
            'changes' => [
                'orderChange' => array_merge($orderChange, ['label' => '较上周']),
                'payChange' => array_merge($payChange, ['label' => '较上周']),
                'rateChange' => array_merge($rateChange, ['label' => '较上周'])
            ]
        ];
    }

    /**
     * 按天对比支付成功率（今天vs昨天，按小时分组）
     */
    private function getPaySuccessRateByDay($whereRaw)
    {
        $today = date('Y-m-d');
        $yesterday = date('Y-m-d', strtotime('-1 day'));
        
        $currentStartTime = strtotime($today . " 00:00:00");
        $currentEndTime = strtotime($today . " 23:59:59");
        $lastStartTime = strtotime($yesterday . " 00:00:00");
        $lastEndTime = strtotime($yesterday . " 23:59:59");

        // 获取当前周期和上一周期的数据
        $currentData = $this->getPayDataByTimeRange($whereRaw, $currentStartTime, $currentEndTime);


        print_r($currentData);


        $lastData = $this->getPayDataByTimeRange($whereRaw, $lastStartTime, $lastEndTime);

        // 初始化24小时数据
        $currentOrderMap = $this->initHourlyData();
        $currentPayMap = $this->initHourlyData();
        $lastOrderMap = $this->initHourlyData();
        $lastPayMap = $this->initHourlyData();

        // 填充数据（按小时）
        $this->fillDataByHour($currentData['orderData'], $currentOrderMap);
        $this->fillDataByHour($currentData['payData'], $currentPayMap);
        $this->fillDataByHour($lastData['orderData'], $lastOrderMap);
        $this->fillDataByHour($lastData['payData'], $lastPayMap);

        // 计算成功率
        $currentRateArr = $this->calculateSuccessRate($currentOrderMap, $currentPayMap);
        $lastRateArr = $this->calculateSuccessRate($lastOrderMap, $lastPayMap);

        // 生成X轴数据（小时）
        $xAxisData = $this->generateHourlyXAxisData();

        // 计算成功率变化
        $successRateChange = $this->calculateRateChange($currentRateArr, $lastRateArr);

        // 转换支付渠道下单比例和数量
        $channelData = $this->trandformListColumn($currentData['channelData'],['pay_channel']);

        // 生成表格数据
        $tableData = $this->generateDayTableData(
            $currentOrderMap, 
            $currentPayMap, 
            $currentRateArr,
            $lastOrderMap, 
            $lastPayMap, 
            $lastRateArr,
            $successRateChange,
        );

        $currentOrderNum = array_sum($currentOrderMap);
        $currentPaySuccessNum = array_sum($currentPayMap);
        $paySuccessRate = $currentOrderNum > 0 ? getRound($currentPaySuccessNum, $currentOrderNum) : 0;

        return [
            'PaySuccessRateArr' => $currentRateArr,
            'OldPaySuccessRateArr' => $lastRateArr,
            'orderNum' => $currentOrderNum,
            'successNum' => $currentPaySuccessNum,
            'paySuccessRate' => $paySuccessRate,
            'xAxisData' => $xAxisData,
            'successRateChange' => $successRateChange,
            'data' => $tableData,
            'currentChannelData' => $channelData,
        ];
    }

    /**
     * 按周对比支付成功率（本周vs上周，按天分组）
     */
    private function getPaySuccessRateByWeek($whereRaw)
    {
        // 本周：今天往前7天（包含今天）
        $currentEndTime = strtotime(date('Y-m-d') . " 23:59:59");
        $currentStartTime = strtotime(date('Y-m-d', strtotime('-6 days')) . " 00:00:00");
        
        // 上周：今天往前14天到往前7天
        $lastEndTime = strtotime(date('Y-m-d', strtotime('-7 days')) . " 23:59:59");
        $lastStartTime = strtotime(date('Y-m-d', strtotime('-13 days')) . " 00:00:00");

        // 获取当前周期和上一周期的数据
        $currentData = $this->getPayDataByTimeRange($whereRaw, $currentStartTime, $currentEndTime);
        $lastData = $this->getPayDataByTimeRange($whereRaw, $lastStartTime, $lastEndTime);

        // 初始化7天数据
        $currentOrderMap = $this->initWeeklyData($currentStartTime);
        $currentPayMap = $this->initWeeklyData($currentStartTime);
        $lastOrderMap = $this->initWeeklyData($lastStartTime);
        $lastPayMap = $this->initWeeklyData($lastStartTime);

        // 填充数据（按天）
        $this->fillDataByDay($currentData['orderData'], $currentOrderMap);
        $this->fillDataByDay($currentData['payData'], $currentPayMap);
        $this->fillDataByDay($lastData['orderData'], $lastOrderMap);
        $this->fillDataByDay($lastData['payData'], $lastPayMap);

        // 计算成功率
        $currentRateArr = $this->calculateSuccessRate($currentOrderMap, $currentPayMap);
        $lastRateArr = $this->calculateSuccessRate($lastOrderMap, $lastPayMap);

        // 生成X轴数据（星期）
        $xAxisData = $this->generateWeeklyXAxisData($currentStartTime);

        // 计算成功率变化
        $successRateChange = $this->calculateRateChange($currentRateArr, $lastRateArr);

        // 生成表格数据
        $tableData = $this->generateWeekTableData(
            $currentOrderMap,
            $currentPayMap,
            $currentRateArr,
            $lastOrderMap,
            $lastPayMap,
            $lastRateArr,
            $successRateChange,
            $xAxisData
        );

        $currentOrderNum = array_sum($currentOrderMap);
        $currentPaySuccessNum = array_sum($currentPayMap);
        $paySuccessRate = $currentOrderNum > 0 ? getRound($currentPaySuccessNum, $currentOrderNum) : 0;

        return [
            'PaySuccessRateArr' => $currentRateArr,
            'OldPaySuccessRateArr' => $lastRateArr,
            'orderNum' => $currentOrderNum,
            'successNum' => $currentPaySuccessNum,
            'paySuccessRate' => $paySuccessRate,
            'xAxisData' => $xAxisData,
            'successRateChange' => $successRateChange,
            'data' => $tableData
        ];
    }

    /**
     * 获取年表名称
     */
    private function getYearTableName($timestamp)
    {
        return 'sdk_order_' . date('Y', $timestamp);
    }

    /**
     * 获取指定时间范围内的订单和支付数据和渠道支付下单数据
     */
    private function getPayDataByTimeRange($whereRaw, $startTime, $endTime)
    {
        $timeWhereRaw = $whereRaw . " AND pay_time BETWEEN '{$startTime}' AND '{$endTime}'";
        
        // 获取时间范围内的所有年份
        $startYear = date('Y', $startTime);
        $endYear = date('Y', $endTime);
        $years = range($startYear, $endYear);
        
        $orderData = [];
        $payData = [];
        $channelData = [];
        
        // 遍历每个年份的表
        foreach ($years as $year) {
            $tableName = 'sdk_order_' . $year;
            
            // 检查表是否存在
            $tableExists = Db::connect('db_game_log')->query("SHOW TABLES LIKE '{$tableName}'");
            if (empty($tableExists)) {
                continue;
            }
            
            // 总下单量
            $yearOrderData = Db::connect('db_game_log')->query("
                SELECT Count(*) as order_num, pay_time
                FROM {$tableName}
                WHERE {$timeWhereRaw}
                GROUP BY pay_time
            ");
            
            // 总支付量
            $yearPayData = Db::connect('db_game_log')->query("
                SELECT Count(*) as pay_num, pay_time
                FROM {$tableName}
                WHERE {$timeWhereRaw} AND sync_status = 1
                GROUP BY pay_time
            ");

            // 支付渠道下单比例和数量
            $yearChannelData = Db::connect('db_game_log')->query("
                SELECT pay_channel_id, COUNT(*) as pay_num
                FROM {$tableName}
                WHERE {$timeWhereRaw} AND sync_status = 1
                GROUP BY pay_channel_id
            ");



            // 合并数据
            $orderData = array_merge($orderData, $yearOrderData);
            $payData = array_merge($payData, $yearPayData);
            $channelData = array_merge($channelData, $yearChannelData);
        }

        return [
            'orderData' => $orderData,
            'payData' => $payData,
            'channelData' => $channelData
        ];

       
    }

    /**
     * 初始化24小时数据结构
     */
    private function initHourlyData()
    {
        $data = [];
        for ($hour = 0; $hour < 24; $hour++) {
            $hourKey = str_pad($hour, 2, '0', STR_PAD_LEFT);
            $data[$hourKey] = 0;
        }
        return $data;
    }

    /**
     * 初始化7天数据结构
     */
    private function initWeeklyData($startTime)
    {
        $data = [];
        for ($day = 0; $day < 7; $day++) {
            $dateKey = date('Y-m-d', strtotime("+{$day} days", $startTime));
            $data[$dateKey] = 0;
        }
        return $data;
    }

    /**
     * 按小时填充数据
     */
    private function fillDataByHour($sourceData, &$targetMap)
    {
        foreach ($sourceData as $item) {
            $hourKey = date('H', $item['pay_time']);
            $value = $item['order_num'] ?? $item['pay_num'] ?? 0;
            $targetMap[$hourKey] = $value;
        }
    }

    /**
     * 按天填充数据
     */
    private function fillDataByDay($sourceData, &$targetMap)
    {
        foreach ($sourceData as $item) {
            $dateKey = date('Y-m-d', $item['pay_time']);
            $value = $item['order_num'] ?? $item['pay_num'] ?? 0;
            if (isset($targetMap[$dateKey])) {
                $targetMap[$dateKey] += $value;
            }
        }
    }

    /**
     * 计算成功率数组
     */
    private function calculateSuccessRate($orderMap, $payMap)
    {
        $rateArr = [];
        foreach ($orderMap as $key => $orderNum) {
            $payNum = $payMap[$key] ?? 0;
            $rateArr[] = $orderNum > 0 ? getRound($payNum, $orderNum) : 0;
        }
        return $rateArr;
    }

    /**
     * 计算成功率变化
     */
    private function calculateRateChange($currentRateArr, $lastRateArr)
    {
        $change = [];
        $count = count($currentRateArr);
        for ($i = 0; $i < $count; $i++) {
            $change[] = $currentRateArr[$i] - $lastRateArr[$i];
        }
        return $change;
    }

    /**
     * 计算变化百分比和趋势
     */
    private function calculateChangePercentage($currentValue, $previousValue)
    {
        if ($previousValue == 0) {
            $trend = $currentValue > 0 ? 'up' : 'stable';
            $percentage = $currentValue > 0 ? 100 : 0;
            return [
                'percentage' => $percentage,
                'percentageText' => $percentage . '%',
                'trend' => $trend,
                'value' => $currentValue - $previousValue,
                'icon' => $trend == 'up' ? 'icon-arrow-rise' : 'icon-arrow-stable'
            ];
        }
        
        $percentage = round((($currentValue - $previousValue) / $previousValue) * 100, 2);
        $trend = $percentage > 0 ? 'up' : ($percentage < 0 ? 'down' : 'stable');
        
        // 根据趋势设置图标
        $icon = 'icon-arrow-stable';
        if ($trend == 'up') {
            $icon = 'icon-arrow-rise';
        } elseif ($trend == 'down') {
            $icon = 'icon-arrow-fall';
        }
        
        return [
            'percentage' => abs($percentage),
            'percentageText' => abs($percentage) . '%',
            'trend' => $trend,
            'value' => $currentValue - $previousValue,
            'icon' => $icon
        ];
    }

    /**
     * 生成小时X轴数据
     */
    private function generateHourlyXAxisData()
    {
        $xAxisData = [];
        for ($hour = 0; $hour < 24; $hour++) {
            $xAxisData[] = str_pad($hour, 2, '0', STR_PAD_LEFT) . ":00-" . 
                          str_pad($hour + 1, 2, '0', STR_PAD_LEFT) . ":00";
        }
        return $xAxisData;
    }

    /**
     * 生成星期X轴数据（根据起始日期往后推7天）
     */
    private function generateWeeklyXAxisData($startTime)
    {
        $weekDays = ['周日', '周一', '周二', '周三', '周四', '周五', '周六'];
        $xAxisData = [];
        
        for ($day = 0; $day < 7; $day++) {
            $timestamp = strtotime("+{$day} days", $startTime);
            $weekIndex = date('w', $timestamp);
            $xAxisData[] = $weekDays[$weekIndex];
        }
        
        return $xAxisData;
    }

    /**
     * 生成按天的表格数据
     */
    private function generateDayTableData(
        $currentOrderMap, 
        $currentPayMap, 
        $currentRateArr,
        $lastOrderMap, 
        $lastPayMap, 
        $lastRateArr,
        $successRateChange
       
    ) {
        $tableData = [];
        $index = 0;
        
        foreach ($currentOrderMap as $hourKey => $orderNum) {
            $tableData[] = [
                'time' => $hourKey . ":00-" . str_pad(intval($hourKey) + 1, 2, '0', STR_PAD_LEFT) . ":00",
                'todayOrderNum' => $orderNum,
                'todayPayNum' => $currentPayMap[$hourKey],
                'todayPaySuccessRate' => $currentRateArr[$index],
                'yesterdayOrderNum' => array_values($lastOrderMap)[$index],
                'yesterdayPayNum' => array_values($lastPayMap)[$index],
                'yesterdayPaySuccessRate' => $lastRateArr[$index],
                'successRateChange' => $successRateChange[$index],
            ];
            $index++;
        }
        
        return $tableData;
    }

    /**
     * 生成按周的表格数据
     */
    private function generateWeekTableData(
        $currentOrderMap,
        $currentPayMap,
        $currentRateArr,
        $lastOrderMap,
        $lastPayMap,
        $lastRateArr,
        $successRateChange,
        $xAxisData
    ) {
        $tableData = [];
        $index = 0;
        
        foreach ($currentOrderMap as $dateKey => $orderNum) {
            $tableData[] = [
                'time' => $xAxisData[$index],
                'todayOrderNum' => $orderNum,
                'todayPayNum' => $currentPayMap[$dateKey],
                'todayPaySuccessRate' => $currentRateArr[$index],
                'yesterdayOrderNum' => array_values($lastOrderMap)[$index],
                'yesterdayPayNum' => array_values($lastPayMap)[$index],
                'yesterdayPaySuccessRate' => $lastRateArr[$index],
                'successRateChange' => $successRateChange[$index],
            ];
            $index++;
        }
        
        return $tableData;
    }
}