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