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

<?php

// 玩家日志逻

namespace app\v1\logic\gameLog;

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

    // 生成基础日统计表的联合查询列表
    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 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;
    }


    // 生成wheresql
    public function generateWhereSql($params){

        $whereSql = "";
        // 游戏id
        if(!empty($params['game_id'])){
            if (is_array($params['game_id'])) {
                $whereSql .= " AND game_id IN(" . implode(',', $params['game_id']) . ")";
            } else {
                $whereSql .= " AND game_id = {$params['game_id']}";
            }
        }
        // 媒体id
        if(!empty($params['media_id'])){
            $whereSql .= " AND media_id = {$params['media_id']}";
        }
        // 渠道id
        if(!empty($params['agent_id'])){
            $whereSql .= " AND agent_id = {$params['agent_id']}";
        }
        // 广告位id
        if(!empty($params['site_id'])){
            if (is_array($params['site_id'])) {
                $whereSql .= " AND site_id IN(" . implode(',', $params['site_id']) . ")";
            } else {
                $whereSql .= " AND site_id = {$params['site_id']}";
            }
        }
        // 负责人
        if(!empty($params['auth_id'])){
            if (is_array($params['auth_id'])) {
                $whereSql .= " AND auth_id IN(" . implode(',', $params['auth_id']) . ")";
            } else {
                $whereSql .= " AND auth_id = {$params['auth_id']}";
            }
        }
        // 注册日期
        if(!empty($params['reg_date']) && 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;
    }
}