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

<?php
declare(strict_types=1);
namespace plugin\saiadmin\app\service;

use plugin\saiadmin\app\model\water\{WaterModel,WaterSku,WaterAccount,WaterDelivery,WaterLedger};
use plugin\saiadmin\exception\ApiException;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Writer;

/** Read model for the inventory dashboard; all mutations retain the original stock service. */
final class WaterInventory
{
    private function query(array $filters)
    {
        $keyword=trim((string)($filters['keyword']??''));
        if (mb_strlen($keyword)>120) throw new ApiException('搜索内容不能超过120个字符');
        $query=WaterSku::query();
        if ($keyword!=='') $query->where('name','like','%'.$keyword.'%');
        return $query->selectRaw("*, CASE WHEN status<>1 THEN 'disabled' WHEN available<=safety_stock THEN 'alert' ELSE 'healthy' END AS stock_status");
    }
    public function dashboard(array $filters): array
    {
        return WaterModel::transaction(function () use ($filters) {
            $summary=WaterSku::query()->selectRaw('COUNT(*) AS specs, COALESCE(SUM(available),0) AS available,
                COALESCE(SUM(locked),0) AS locked, COALESCE(SUM(transit),0) AS transit, COALESCE(SUM(empty_qty),0) AS warehouse_empty,
                COALESCE(SUM(damaged_qty),0) AS damaged, COALESCE(SUM(empty_lost_qty),0) AS empty_lost,
                COALESCE(SUM(CASE WHEN status=1 AND available<=safety_stock THEN 1 ELSE 0 END),0) AS alerts')->first()->toArray();
            $summary=array_map('intval',$summary);
            $summary['site_empty']=(int)WaterAccount::query()->sum('empty_qty');
            $summary['vehicle_empty']=(int)WaterDelivery::query()->sum('empty_in_vehicle');
            $summary['empty_assets']=$summary['warehouse_empty']+$summary['site_empty']+$summary['vehicle_empty'];
            $summary['total']=$summary['available']+$summary['locked']+$summary['transit'];
            $start=date('Y-m-d',strtotime('-6 days'));
            $movements=WaterLedger::query()->whereIn('kind',['inbound','delivery_return','depart'])
                ->whereBetween('created_at',[$start.' 00:00:00',date('Y-m-d').' 23:59:59'])
                ->selectRaw('DATE(created_at) AS day,kind,SUM(quantity) AS quantity')->groupByRaw('DATE(created_at),kind')->get();
            $trend=[];
            for ($i=0;$i<7;$i++) { $day=date('Y-m-d',strtotime($start.' +'.$i.' days')); $trend[$day]=['day'=>$day,'inbound'=>0,'outbound'=>0]; }
            foreach ($movements as $row) $trend[$row->day][$row->kind==='depart'?'outbound':'inbound']+=(int)$row->quantity;
            $list=$this->query($filters)->orderBy('id','desc')->pageResult(max(1,(int)($filters['page']??1)),max(1,min(100,(int)($filters['limit']??10))));
            return ['summary'=>$summary,'trend'=>array_values($trend),'list'=>$list,'updated_at'=>date('Y-m-d H:i:s')];
        });
    }
    public function export(array $filters): string
    {
        $rows=$this->query($filters)->orderBy('id','desc')->limit(50001)->get();
        if (count($rows)>50000) throw new ApiException('导出超过50000条，请缩小搜索范围');
        $path=tempnam(sys_get_temp_dir(),'water-stock-');
        if ($path===false) throw new ApiException('无法创建导出文件');
        $writer=new Writer(); $opened=false;
        try {
            $writer->openToFile($path); $opened=true;
            $writer->addRow(Row::fromValues(['商品规格','容量/L','可用满桶','锁定','在途','仓库空桶','安全库存','破损隔离满桶','累计空桶损失','状态']));
            foreach ($rows as $row) {
                $name=(string)$row->name;
                if (preg_match('/^[=+@\-\t\r\n]/u',$name)) $name="'".$name;
                $writer->addRow(Row::fromValues([$name,(float)$row->liters,(int)$row->available,(int)$row->locked,(int)$row->transit,
                    (int)$row->empty_qty,(int)$row->safety_stock,(int)$row->damaged_qty,(int)$row->empty_lost_qty,
                    ['disabled'=>'停用','alert'=>'预警','healthy'=>'充足'][$row->stock_status]]));
            }
            $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); } }
    }
}