water/server/plugin/saiadmin/app/service/WaterDeviceDashboard.php

<?php
declare(strict_types=1);

namespace plugin\saiadmin\app\service;

use Illuminate\Database\Eloquent\Builder;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Writer;
use plugin\saiadmin\app\model\water\{WaterModel, WaterDevice, WaterDeviceUsage};
use plugin\saiadmin\app\service\device\{DeviceFilters, DeviceStatusPolicy};
use plugin\saiadmin\exception\ApiException;

/** Device read model. Archive writes remain in WaterBusiness; reports remain in DeviceReport. */
final class WaterDeviceDashboard
{
    private readonly \DateTimeImmutable $now;
    private readonly DeviceStatusPolicy $status;

    public function __construct(?\DateTimeImmutable $now = null)
    {
        $this->now = $now ?? new \DateTimeImmutable('now', new \DateTimeZone('Asia/Shanghai'));
        $this->status = new DeviceStatusPolicy($this->now);
    }

    private function query(?DeviceFilters $filters = null): Builder
    {
        $query = WaterDevice::query()->alias('d')
            ->leftJoin('sa_water_enterprise as e', 'e.id', '=', 'd.enterprise_id')
            ->leftJoin('sa_water_sku as s', 's.id', '=', 'd.sku_id');
        if (!$filters) {
            return $query;
        }
        if ($filters->keyword !== '') {
            $query->matching('d.sn|d.name|e.name', '%' . $filters->keyword . '%');
        }
        if ($filters->status !== '') {
            $query->whereRaw('(' . $this->status->expression() . ') = ?', [$filters->status]);
        }
        if ($filters->region !== '') {
            $query->where('e.region', $filters->region);
        }
        if ($filters->firmware !== '') {
            $query->where('d.firmware', $filters->firmware);
        }
        return $query;
    }

    private function rows(DeviceFilters $filters): Builder
    {
        return $this->query($filters)
            ->selectRaw('d.*, e.name AS enterprise_name, e.region, s.name AS sku_name, (' . $this->status->expression() . ') AS connection_status')
            ->orderBy('d.id', 'desc');
    }

    private function present(array $row): array
    {
        $row['today_liters'] = $row['telemetry_date'] === $this->now->format('Y-m-d') && $row['today_liters'] !== null
            ? (float) $row['today_liters'] : null;
        $row['last_seen_seconds'] = $row['last_seen']
            ? max(0, $this->now->getTimestamp() - (new \DateTimeImmutable($row['last_seen'], $this->now->getTimezone()))->getTimestamp())
            : null;
        return $row;
    }

    public function dashboard(array $input): array
    {
        $filters = new DeviceFilters($input);
        return WaterModel::transaction(function () use ($filters) {
            $list = $this->rows($filters)->pageResult($filters->page, $filters->limit);
            $list['data'] = array_map($this->present(...), $list['data']);
            return [
                'summary' => $this->summary(),
                'list' => $list,
                'options' => $this->options(),
                'trend' => $this->trend(),
                'anomalies' => $this->anomalies(),
                'updated_at' => $this->now->format('Y-m-d H:i:s'),
            ];
        });
    }

    private function summary(): array
    {
        $summary = array_fill_keys(['online', 'offline', 'fault', 'unconnected'], 0);
        $groups = $this->query()->selectRaw($this->status->expression() . ' AS state, COUNT(*) AS total')
            ->groupByRaw($this->status->expression())->get();
        foreach ($groups as $group) {
            $summary[$group->state] = (int) $group->total;
        }
        $summary['total'] = array_sum($summary);
        $summary['new_month'] = WaterDevice::query()
            ->whereBetween('created_at', [$this->now->format('Y-m-01 00:00:00'), $this->now->format('Y-m-d H:i:s')])->count();
        $summary['offline_long'] = $this->query(new DeviceFilters(['status' => 'offline']))
            ->where('d.last_seen', '<', $this->now->modify('-24 hours')->format('Y-m-d H:i:s'))->count();
        $summary['online_rate'] = $summary['total'] ? round(100 * $summary['online'] / $summary['total'], 1) : 0;
        return $summary;
    }

    private function options(): array
    {
        return [
            'regions' => $this->query()->whereNotNull('e.region')->where('e.region', '<>', '')
                ->distinct()->orderBy('e.region')->pluck('e.region')->all(),
            'firmwares' => WaterDevice::query()->selectRaw('firmware, COUNT(*) AS total')
                ->groupBy('firmware')->orderBy('firmware')->get()->map(fn ($row) => [
                    'version' => (string) $row->firmware,
                    'total' => (int) $row->total,
                ])->all(),
        ];
    }

    private function trend(): array
    {
        $start = $this->now->modify('-29 days');
        $daily = WaterDeviceUsage::query()->whereBetween('day', [$start->format('Y-m-d'), $this->now->format('Y-m-d')])
            ->selectRaw('day, SUM(liters) AS liters, COUNT(*) AS reporting_devices')->groupBy('day')->get()->keyBy('day');
        $result = [];
        for ($i = 0; $i < 30; $i++) {
            $day = $start->modify('+' . $i . ' days')->format('Y-m-d');
            $row = $daily->get($day);
            $result[] = ['day' => $day, 'liters' => $row ? (float) $row->liters : null,
                'reporting_devices' => $row ? (int) $row->reporting_devices : 0];
        }
        return $result;
    }

    private function anomalies(): array
    {
        $offline = $this->query(new DeviceFilters(['status' => 'offline']))->count();
        $faults = $this->query(new DeviceFilters(['status' => 'fault']))
            ->selectRaw('TRIM(d.fault) AS label, COUNT(*) AS total')->groupByRaw('TRIM(d.fault)')->orderByDesc('total')->get();
        $total = $offline + (int) $faults->sum('total');
        $rows = $offline ? [['label' => '网络离线', 'total' => $offline]] : [];
        foreach ($faults as $fault) {
            $rows[] = ['label' => $fault->label, 'total' => (int) $fault->total];
        }
        return array_map(fn ($row) => $row + ['percentage' => round(100 * $row['total'] / max(1, $total), 1)], $rows);
    }

    public function export(array $input): string
    {
        $rows = $this->rows(new DeviceFilters($input))->limit(50001)->get();
        if (count($rows) > 50000) {
            throw new ApiException('导出超过50000条，请缩小筛选范围');
        }
        $path = tempnam(sys_get_temp_dir(), 'water-devices-');
        if ($path === false) {
            throw new ApiException('无法创建导出文件');
        }
        $writer = new Writer();
        $opened = false;
        try {
            $writer->openToFile($path);
            $opened = true;
            $writer->addRow(Row::fromValues(['设备名称', 'SN', '企业', '区域', '商品规格', '固件版本', '状态', '今日用水/L', '最后上报', '故障说明']));
            foreach ($rows as $model) {
                $row = $this->present($model->toArray());
                $values = [$row['name'], $row['sn'], $row['enterprise_name'], $row['region'], $row['sku_name'],
                    $row['firmware'], DeviceStatusPolicy::LABELS[$row['connection_status']], $row['today_liters'], $row['last_seen'], $row['fault']];
                $writer->addRow(Row::fromValues(array_map(static fn ($value) => is_string($value) && preg_match('/^[=+@\-\t\r\n]/u', $value) ? "'" . $value : $value, $values)));
            }
            $writer->close();
            $opened = false;
            $content = file_get_contents($path);
            if ($content === false) {
                throw new ApiException('读取设备导出失败');
            }
            return $content;
        } finally {
            try {
                if ($opened) {
                    $writer->close();
                }
            } finally {
                if (is_file($path)) {
                    unlink($path);
                }
            }
        }
    }
}