water/server/plugin/saiadmin/app/logic/operation/DeviceOperationLogic.php

<?php

namespace plugin\saiadmin\app\logic\operation;

use DateTimeImmutable;
use plugin\saiadmin\app\model\system\SystemUser;
use plugin\saiadmin\app\service\DeviceOperationMetrics;
use plugin\saiadmin\app\service\DeviceOperationShares;
use plugin\saiadmin\app\service\DeviceProductCatalog;
use plugin\saiadmin\basic\think\BaseLogic;
use plugin\saiadmin\exception\ApiException;
use support\think\Db;

class DeviceOperationLogic extends BaseLogic
{
    private const NET = 'CASE WHEN o.status = 5 OR o.paid_amount <= 0 THEN 0 '
        . 'ELSE GREATEST(o.paid_amount - o.refund_amount, 0) END';
    private const SETTLED = 'COALESCE(o.return_at,o.update_time,o.rent_at,o.create_time)';

    public function indexList(array $filters): array
    {
        $categories = (new DeviceProductCatalog())->categories();
        $options = $this->parseFilters($filters, $categories);
        $managerIds = $this->managerIds($options['scope']);
        $now = new DateTimeImmutable();
        $query = $this->deviceQuery($managerIds)->whereIn('d.goods_type', $options['goods_types']);
        $total = (int) (clone $query)->count();
        $rows = $this->loadDevicePage($query, $managerIds, $options, $now);
        $dailyAmounts = $this->loadDailyAmounts(array_column($rows, 'id'), $managerIds, $now);
        $shares = $this->loadShares($rows);
        foreach ($rows as &$row) {
            $row = array_merge($row, $shares->calculate($row, $options['category']));
            $row = DeviceOperationMetrics::calculate($row, $dailyAmounts[$row['id']] ?? [], $now);
            unset($row['merchant_relative_rate'], $row['profit_method'], $row['merchant_dept_level']);
        }
        unset($row);
        return [
            'data' => $rows, 'total' => $total, 'current_page' => $options['page'],
            'categories' => $categories, 'category' => $options['category'],
            'per_page' => $options['limit'], 'last_page' => max(1, (int) ceil($total / $options['limit'])),
        ];
    }

    private function parseFilters(array $filters, array $categories): array
    {
        $scope = (string) ($filters['scope'] ?? 'mine');
        $category = (string) ($filters['category'] ?? '');
        $category = $category === '' ? ($categories[0]['value'] ?? '') : $category;
        $typesByCategory = array_column($categories, 'goods_types', 'value');
        if ($categories === []) {
            throw new ApiException('请先在数据字典维护设备产品和设备类型产品映射');
        }
        if (!in_array($scope, ['mine', 'subordinate'], true) || !isset($typesByCategory[$category])) {
            throw new ApiException('请选择有效的用户类别和设备类别');
        }
        $sort = (string) ($filters['sort'] ?? '');
        $direction = (string) ($filters['order'] ?? 'desc');
        $sorts = [
            '' => 'd.id', 'total_amount' => 'total_amount', 'total_orders' => 'total_orders',
            'unit_price' => 'unit_price', 'profit_rate_value' => 'profit_rate_value',
        ];
        if (!isset($sorts[$sort]) || !in_array($direction, ['asc', 'desc'], true)) {
            throw new ApiException('排序参数错误');
        }
        $page = max(1, (int) ($filters['page'] ?? 1));
        $limit = min(100, max(1, (int) ($filters['limit'] ?? 10)));
        return compact('scope', 'category', 'page', 'limit', 'direction')
            + ['sort' => $sorts[$sort], 'goods_types' => $typesByCategory[$category]];
    }

    private function aggregateOrders(array $ids, DateTimeImmutable $now): string
    {
        $today = $now->format('Y-m-d 00:00:00');
        $tomorrow = $now->modify('+1 day')->format('Y-m-d 00:00:00');
        $yesterday = $now->modify('-1 day')->format('Y-m-d 00:00:00');
        $settled = self::SETTLED;
        $todayCondition = "$settled >= '$today' AND $settled < '$tomorrow'";
        $yesterdayCondition = "$settled >= '$yesterday' AND $settled < '$today'";
        // 数据库先聚合/排序再分页，避免只排序当前页，也不逐设备执行统计 SQL。
        return $this->orderQuery($ids, $now)->fieldRaw(implode(',', [
            'o.device_id', 'SUM(' . self::NET . ') AS total_amount', 'COUNT(*) AS total_orders',
            'SUM(CASE WHEN o.paid_amount > 0 AND o.status <> 5 THEN 1 ELSE 0 END) AS paid_orders',
            "SUM(CASE WHEN $todayCondition THEN " . self::NET . ' ELSE 0 END) AS today_amount',
            "SUM(CASE WHEN $yesterdayCondition THEN " . self::NET . ' ELSE 0 END) AS yesterday_amount',
            "SUM(CASE WHEN $todayCondition THEN 1 ELSE 0 END) AS today_orders",
            "SUM(CASE WHEN $yesterdayCondition THEN 1 ELSE 0 END) AS yesterday_orders",
        ]))->group('o.device_id')->buildSql();
    }

    private function loadDevicePage($query, array $ids, array $options, DateTimeImmutable $now): array
    {
        $stats = $this->aggregateOrders($ids, $now);
        return $query->leftJoin([$stats => 's'], 's.device_id = d.id')
            ->fieldRaw(implode(',', [
                'd.id,d.goods_type,d.goods_sn,d.device_code,d.manage_agent_id',
                'b.merchant_id,b.bind_time,m.name AS merchant_name',
                "COALESCE(NULLIF(manager.realname,''),manager.username,'') AS agent_name",
                'd.cost_price,m.agent_id AS merchant_agent_id,m.profit_rate AS merchant_relative_rate,m.profit_method',
                'merchant_dept.level AS merchant_dept_level',
                'COALESCE(s.total_amount,0) AS total_amount,COALESCE(s.total_orders,0) AS total_orders',
                'COALESCE(s.paid_orders,0) AS paid_orders',
                'COALESCE(s.today_amount,0) AS today_amount,COALESCE(s.yesterday_amount,0) AS yesterday_amount',
                'COALESCE(s.today_orders,0) AS today_orders,COALESCE(s.yesterday_orders,0) AS yesterday_orders',
                'COALESCE(s.total_amount / NULLIF(s.paid_orders,0),0) AS unit_price',
                'COALESCE(s.total_amount,0) / NULLIF(d.cost_price,0) * 100 AS profit_rate_value',
            ]))->order($options['sort'], $options['direction'])->order('d.id', 'desc')
            ->page($options['page'], $options['limit'])->select()->toArray();
    }

    private function loadDailyAmounts(array $deviceIds, array $ids, DateTimeImmutable $now): array
    {
        $dailyByDevice = [];
        if ($deviceIds !== []) {
            $daily = $this->orderQuery($ids, $now)->whereIn('o.device_id', $deviceIds)
                ->join('sa_merchant_device_binding current_binding', 'current_binding.device_id = o.device_id')
                ->whereRaw(self::SETTLED . ' >= current_binding.bind_time')
                ->whereRaw(self::SETTLED . ' <= ?', [$now->format('Y-m-d H:i:s')])
                ->fieldRaw('o.device_id,DATE(' . self::SETTLED . ') AS stat_date,SUM(' . self::NET . ') AS amount')
                ->group('o.device_id,stat_date')->order('stat_date', 'asc')->select()->toArray();
            foreach ($daily as $day) {
                $dailyByDevice[$day['device_id']][] = $day;
            }
        }
        return $dailyByDevice;
    }

    private function loadShares(array $rows): DeviceOperationShares
    {
        if ($rows === []) {
            return new DeviceOperationShares([]);
        }
        $agentIds = array_unique(array_merge(array_column($rows, 'manage_agent_id'), array_column($rows, 'merchant_agent_id')));
        $departmentIds = [];
        foreach ($rows as $row) {
            $departmentIds = array_merge($departmentIds, explode(',', $row['merchant_dept_level'] ?? ''));
        }
        $departmentIds = array_unique(array_filter(array_map('intval', $departmentIds)));
        $agents = Db::table('sa_system_user')->alias('u')->whereNull('u.delete_time')
            ->where(function ($query) use ($agentIds, $departmentIds) {
                $query->whereIn('u.id', $agentIds)->whereOr('u.dept_id', 'in', $departmentIds ?: [-1]);
            })
            ->leftJoin('sa_agent_profile profile', 'profile.agent_id = u.id')
            ->leftJoin('sa_system_dept dept', 'dept.id = u.dept_id AND dept.delete_time IS NULL')
            ->field('u.id,u.dept_id,dept.level AS dept_level,profile.profit_rates')->select()->toArray();
        return new DeviceOperationShares($agents);
    }

    private function deviceQuery(array $ids)
    {
        return Db::table('sa_device')->alias('d')->whereNull('d.delete_time')
            ->whereIn('d.manage_agent_id', $ids ?: [-1])
            ->leftJoin('sa_merchant_device_binding b', 'b.device_id = d.id')
            ->leftJoin('sa_merchant m', 'm.id = b.merchant_id AND m.delete_time IS NULL')
            ->leftJoin('sa_system_user manager', 'manager.id = d.manage_agent_id AND manager.delete_time IS NULL')
            ->leftJoin('sa_system_user merchant_agent', 'merchant_agent.id = m.agent_id AND merchant_agent.delete_time IS NULL')
            ->leftJoin('sa_system_dept merchant_dept', 'merchant_dept.id = merchant_agent.dept_id AND merchant_dept.delete_time IS NULL');
    }

    private function orderQuery(array $ids, DateTimeImmutable $now)
    {
        return Db::table('sa_rental_order')->alias('o')->whereNull('o.delete_time')
            ->whereIn('o.manage_agent_id', $ids ?: [-1])
            ->whereRaw('COALESCE(o.rent_at,o.create_time) <= ?', [$now->format('Y-m-d H:i:s')]);
    }

    /** 本人与下级互斥；下级沿用项目记录的部门后代权限，不能用归属权代替管理权。 */
    private function managerIds(string $scope): array
    {
        $id = (int) ($this->adminInfo['id'] ?? 0);
        if ($id <= 0) {
            throw new ApiException('请先登录');
        }
        if ($scope === 'mine') {
            return [$id];
        }
        if ($id === 1) {
            return array_map('intval', SystemUser::where('id', '<>', 1)->whereNull('delete_time')->column('id'));
        }
        $dept = $this->adminInfo['deptList'] ?? [];
        if (empty($dept['id']) || !isset($dept['level'])) {
            return [];
        }
        $deptIds = Db::table('sa_system_dept')->whereNull('delete_time')
            ->whereLike('level', (string) $dept['level'] . (int) $dept['id'] . ',%')->column('id');
        return $deptIds === [] ? [] : array_map('intval', SystemUser::whereIn('dept_id', $deptIds)
            ->where('id', '<>', $id)->whereNull('delete_time')->column('id'));
    }
}