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

<?php

// 素材数据总览

namespace app\v1\logic\gameLog;

use app\v1\logic\tool\ToolLogic;
use plugin\saiadmin\basic\BaseLogic;
use plugin\saiadmin\service\OpenSpoutWriter;
use support\think\Db;
use support\Request;

class MaterialLogic extends BaseLogic
{

  public function getMaterialList($where)
  {
    $params = $this->searchByAuth($where);
    $tableName = 'media_cost_material';
    $field  = "
      material_name,
      material_id,
      author_id,
      auth_id,
      media_id,
      tdate,
      SUM(money) as cost,
      SUM(ad_show) as ad_show,
      SUM(click) as click,
      SUM(active) as active,
      SUM(register) as register,
      SUM(pay_count) as pay_count,
      SUM(pay_amount) as pay_amount
    ";


    // 基础筛选
    $whereSql = $this->generateWhereSql($params);
    //  作者筛选
    if(!empty($params['author_id'])){
      $whereSql .= " AND author_id = {$params['author_id']}";
    }
    // 消耗筛选
    if(!empty($params['cost_type'])){
      switch ($params['cost_type']) {
        case '1':
          $whereSql .= " AND ori_money >= 2000";
          break;
        case '2':
          $whereSql .= " AND ori_money < 2000";
          break;
      }
    }
    // 素材名称筛选
    if(!empty($params['material_name'])){
      $whereSql .= " AND material_name LIKE '%{$params['material_name']}%'";
    }
    // 素材id筛选
    if(!empty($params['material_id'])){
      $whereSql .= " AND material_id = {$params['material_id']}";
    }
    // 分组筛选
    $group = 'material_id';
    switch ($params['group']) {
      case 1:
        $group = 'material_id';
        break;
      case 2:
        $group = 'author_id';
        break;
      default:
        $group = 'material_id,author_id';
        break;
    }


    $sql = "
      SELECT
        $field
        FROM
        $tableName
      WHERE 1=1
        $whereSql
      GROUP BY
        $group
      ";
    $data = Db::connect('db_advert')->query($sql);
            
    $totalData = [];
    foreach ($data as &$row) {

      if($params['group'] == 2){
        $row['material_name'] = '';
        $row['material_id'] = '';
      }


      $row['cost'] = round($row['cost'],2);
      $row['ad_click_rate'] = ToolLogic::getPercent($row['click'] ,$row['ad_show'],2);
      $row['reg_cost'] = ToolLogic::getRound($row['cost'],$row['register'],2);
      $row['pay_cost'] = ToolLogic::getRound($row['cost'],(int)$row['pay_count'],2);


      $totalData['cost'] = !empty($totalData['cost']) ? round($totalData['cost'] + $row['cost'],2) : $row['cost'];
      $totalData['ad_show'] = !empty($totalData['ad_show']) ? $totalData['ad_show'] + $row['ad_show'] : $row['ad_show'];
      $totalData['click'] = !empty($totalData['click']) ? $totalData['click'] + $row['click'] : $row['click'];
      $totalData['active'] = !empty($totalData['active']) ? $totalData['active'] + $row['active'] : $row['active'];
      $totalData['register'] = !empty($totalData['register']) ? $totalData['register'] + $row['register'] : $row['register'];
      $totalData['pay_count'] = !empty($totalData['pay_count']) ? $totalData['pay_count'] + $row['pay_count'] : $row['pay_count'];
      $totalData['pay_amount'] = !empty($totalData['pay_amount']) ? $totalData['pay_amount'] + $row['pay_amount'] : $row['pay_amount'];

    }

    $totalData['ad_click_rate'] = ToolLogic::getPercent($totalData['click'] ,$totalData['ad_show'],2);
    $totalData['reg_cost'] = ToolLogic::getRound($totalData['cost'],$totalData['register'],2);
    $totalData['pay_cost'] = ToolLogic::getRound($totalData['cost'],(int)$totalData['pay_count'],2);

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

   // 生成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;
    }


}