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


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

}