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

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

use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Writer;
use plugin\saiadmin\exception\ApiException;
use plugin\saiadmin\app\model\water\WaterModel;
use plugin\saiadmin\app\model\water\WaterLedger as WaterLedgerModel;

/** 库存流水列表与导出共用筛选口径。 */
class WaterLedger
{
    private const KINDS = [
        'inbound'=>'仓库入库', 'warehouse_count'=>'仓库盘点', 'site_count'=>'现场盘点',
        'exchange'=>'满桶换空桶', 'reserve'=>'配送锁定', 'assign'=>'配送派单',
        'depart'=>'配送出库', 'complete'=>'配送签收', 'cancel'=>'取消配送',
        'partial_receive'=>'部分签收/拒收', 'delivery_return'=>'退回入库/破损隔离', 'route_settle'=>'返仓空桶交接',
        'promotion_reward'=>'推广赠水权益', 'promotion_reject'=>'推广奖励驳回', 'promotion_policy'=>'推广规则修改',
    ];

    private function query(array $filters)
    {
        $keyword = trim((string)($filters['keyword'] ?? ''));
        if (mb_strlen($keyword) > 120) throw new ApiException('搜索内容不能超过 120 个字符');
        $kind = (string)($filters['kind'] ?? '');
        if ($kind !== '' && !isset(self::KINDS[$kind])) throw new ApiException('操作类型不支持');
        $dates = [];
        foreach (['start_date','end_date'] as $key) {
            $value = (string)($filters[$key] ?? '');
            if ($value !== '') {
                $date = \DateTimeImmutable::createFromFormat('!Y-m-d', $value);
                if (!$date || $date->format('Y-m-d') !== $value) throw new ApiException('日期格式不正确');
            }
            $dates[$key] = $value;
        }
        if ($dates['start_date'] !== '' && $dates['end_date'] !== '' && $dates['start_date'] > $dates['end_date']) {
            throw new ApiException('开始日期不能晚于结束日期');
        }
        $query = WaterLedgerModel::query()->alias('l')
            ->leftJoin('sa_water_enterprise as e','e.id','=','l.enterprise_id')
            ->leftJoin('sa_water_sku as s','s.id','=','l.sku_id')
            ->whereIn('l.kind',array_keys(self::KINDS))
            ->selectRaw('l.id,l.kind,l.quantity,l.reference,l.note,l.created_at,e.name AS enterprise_name,s.name AS sku_name');
        if ($keyword !== '') $query->matching('l.reference|l.note|e.name|s.name','%'.$keyword.'%');
        if ($kind !== '') $query->where('l.kind',$kind);
        if ($dates['start_date'] !== '') $query->where('l.created_at','>=',$dates['start_date'].' 00:00:00');
        if ($dates['end_date'] !== '') $query->where('l.created_at','<=',$dates['end_date'].' 23:59:59');
        return $query;
    }

    public function listing(array $filters): array
    {
        $result = $this->query($filters)->orderBy('l.id','desc')->pageResult(max(1,(int)($filters['page'] ?? 1)),max(1,min(100,(int)($filters['limit'] ?? 10))));
        foreach ($result['data'] as &$row) $row['kind_label'] = self::KINDS[$row['kind']];
        unset($row);
        $result['kinds'] = [];
        foreach (self::KINDS as $value=>$label) $result['kinds'][] = compact('value','label');
        return $result;
    }

    /** 返回 XLSX 内容，临时文件不在 Web 目录内，读取后立即删除。 */
    public function export(array $filters): string
    {
        // 多取一条用于检测上限；不受列表 page/limit 影响，也不会静默截断。
        $rows = $this->query($filters)->orderBy('l.id','desc')->limit(50001)->get()->toArray();
        if (count($rows) > 50000) throw new ApiException('导出记录超过 50000 条，请缩小筛选范围');
        $path = tempnam(sys_get_temp_dir(),'water-ledger-');
        if ($path === false) throw new ApiException('无法创建导出文件，请稍后重试');
        $writer = new Writer();
        $opened = false;
        try {
            $writer->openToFile($path);
            $opened = true;
            $writer->addRow(Row::fromValues(['流水编号','操作类型','商品规格','企业','数量 / 桶','关联凭据','说明','时间']));
            foreach ($rows as $row) {
                $writer->addRow(Row::fromValues([
                    (int)$row['id'], self::KINDS[$row['kind']], self::excelText($row['sku_name']),
                    self::excelText($row['enterprise_name']), (int)$row['quantity'],
                    self::excelText($row['reference']), self::excelText($row['note']), (string)$row['created_at'],
                ]));
            }
            $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); }
        }
    }

    private static function excelText($value): string
    {
        $text = (string)($value ?? '');
        return preg_match('/^[=+@\-\t\r\n]/u',$text) ? "'".$text : $text;
    }
}