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

<?php

// 素材数据总览

namespace app\v1\logic\dataReport;

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

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

        echo $whereSql;

        //  作者筛选
        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);

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

        $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']??0, $totalData['ad_show']??0, 2);
        $totalData['reg_cost'] = ToolLogic::getRound($totalData['cost']??0, $totalData['register']??0, 2);
        $totalData['pay_cost'] = ToolLogic::getRound($totalData['cost']??0, (int)$totalData['pay_count']??0, 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']}'";
        }
        // 自然量ID, auth_id=0为自然量
        if(!empty($params['nomal_game_id'])){
            for($i=0;$i<count($params['nomal_game_id']);$i++){
                $whereSql .= " OR (game_id = {$params['nomal_game_id'][$i]} AND auth_id=0)";
            }
        }

        return $whereSql;
    }
}