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


    /*
     数据总览
     1. DAU 登陆总数 login_total
     2. ARPU pay_total/pay_num
    */
    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 generateYearUnionList($namePrefix, $regDate,$whereSql='',$field='*',$group=null){

        $tableNames = ToolLogic::getYearlyTableNames($namePrefix, $regDate[0],$regDate[1]);
        $sqlParts = [];
        foreach ($tableNames as $tableName){
            $sqlParts[] = "SELECT * FROM {$tableName} WHERE 1=1 {$whereSql}";
        }
        $unionSql = implode(" UNION ALL ", $sqlParts);
        $finalSql = "
            SELECT {$field} FROM ( {$unionSql} ) AS all_total_day
        ";
        if (!empty($group)) {
            $finalSql .= " GROUP BY {$group}";
        }

        $baseData = Db::connect('db_data_report')->query($finalSql);
        return $baseData;
    }


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

        $whereSql = "";
        // 游戏id
        if(!empty($params['game_id'])){
            if (is_array($params['game_id'])) {
                $whereSql .= " AND game_id IN(" . implode(',', $params['game_id']) . ")";
            } else {
                $whereSql .= " AND game_id = {$params['game_id']}";
            }
        }
        // 媒体id
        if(!empty($params['media_id'])){
            $whereSql .= " AND media_id = {$params['media_id']}";
        }
        // 渠道id
        if(!empty($params['agent_id'])){
            $whereSql .= " AND agent_id = {$params['agent_id']}";
        }
        // 广告位id
        if(!empty($params['site_id'])){
            if (is_array($params['site_id'])) {
                $whereSql .= " AND site_id IN(" . implode(',', $params['site_id']) . ")";
            } else {
                $whereSql .= " AND site_id = {$params['site_id']}";
            }
        }
        // 负责人
        if(!empty($params['auth_id'])){
            if (is_array($params['auth_id'])) {
                $whereSql .= " AND auth_id IN(" . implode(',', $params['auth_id']) . ")";
            } else {
                $whereSql .= " AND auth_id = {$params['auth_id']}";
            }
        }
        // 注册日期
        if(!empty($params['reg_date'])??null){
            $whereSql .= " AND tdate BETWEEN '{$params['reg_date'][0]}' AND '{$params['reg_date'][1]}'";
        }

        return $whereSql;
    }
}