CaiWuXiTong/app/Business/CourierPayableSummaryStore.php

<?php

namespace App\Business;

use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\Storage;
use Illuminate\Validation\ValidationException;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\StringValueBinder;
use PhpOffice\PhpSpreadsheet\IOFactory;
use RuntimeException;
use Throwable;

class CourierPayableSummaryStore
{
    public const PAGES = [
        'freight-difference' => '运费差',
        'other-surcharge-summary' => '其他附加费汇总',
        'return-fee' => '退件费',
    ];

    private const MAX_COLUMNS = 200;

    private const MAX_ROWS_BY_PAGE = [
        'freight-difference' => 500_000,
        'other-surcharge-summary' => 500_000,
        'return-fee' => 500_000,
    ];

    private const MAX_CELL_LENGTH = 10000;

    public function title(string $page): string
    {
        abort_unless(isset(self::PAGES[$page]), 404);

        return self::PAGES[$page];
    }

    public function maxRows(string $page): int
    {
        $this->title($page);

        return self::MAX_ROWS_BY_PAGE[$page];
    }

    /**
     * @return array{version:int,headers:list<string>,rows:list<list<string>>,source_name:string,imported_at:string,imported_by:int|null}|null
     */
    public function snapshot(int $companyId, string $page): ?array
    {
        $this->title($page);
        $path = $this->path($companyId, $page);
        $disk = Storage::disk('uploads');

        if (! $disk->exists($path)) {
            return null;
        }

        try {
            $snapshot = json_decode($disk->get($path), true, 512, JSON_THROW_ON_ERROR);
        } catch (Throwable $exception) {
            report($exception);

            throw new RuntimeException('courier_payable_summary_unreadable', previous: $exception);
        }

        if (! is_array($snapshot)
            || ! is_array($snapshot['headers'] ?? null)
            || ! is_array($snapshot['rows'] ?? null)) {
            throw new RuntimeException('courier_payable_summary_invalid');
        }

        return $snapshot;
    }

    /** @return array{rows:int,columns:int} */
    public function import(UploadedFile $file, int $companyId, string $page, ?int $userId): array
    {
        $this->title($page);
        $valueBinder = Cell::getValueBinder();
        Cell::setValueBinder(new StringValueBinder());

        try {
            $reader = IOFactory::createReaderForFile($file->getRealPath());
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($file->getRealPath());
            [$headers, $rows] = $this->readSpreadsheet($spreadsheet, $page);
        } catch (ValidationException $exception) {
            throw $exception;
        } catch (Throwable $exception) {
            throw ValidationException::withMessages([
                'import' => '无法读取导入文件，请确认文件未损坏且格式为 xlsx、xls 或 csv。',
            ]);
        } finally {
            Cell::setValueBinder($valueBinder);

            if (isset($spreadsheet)) {
                $spreadsheet->disconnectWorksheets();
            }
        }

        $payload = json_encode([
            'version' => 1,
            'headers' => $headers,
            'rows' => $rows,
            'source_name' => mb_substr($file->getClientOriginalName(), 0, 255),
            'imported_at' => now()->toIso8601String(),
            'imported_by' => $userId,
        ], JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE | JSON_THROW_ON_ERROR);

        $disk = Storage::disk('uploads');
        $path = $this->path($companyId, $page);
        $previousPath = $this->previousPath($companyId, $page);

        if ($disk->exists($path)) {
            $previous = $disk->get($path);

            if (! $disk->put($previousPath, $previous)) {
                throw new RuntimeException('无法备份当前汇总数据。');
            }
        }

        if (! $disk->put($path, $payload)) {
            throw new RuntimeException('无法保存汇总数据。');
        }

        return ['rows' => count($rows), 'columns' => count($headers)];
    }

    public function path(int $companyId, string $page): string
    {
        $this->title($page);

        return "courier-payable-summaries/{$companyId}/{$page}.json";
    }

    private function previousPath(int $companyId, string $page): string
    {
        return "courier-payable-summaries/{$companyId}/{$page}.previous.json";
    }

    /** @return array{0:list<string>,1:list<list<string>>} */
    private function readSpreadsheet($spreadsheet, string $page): array
    {
        $maxRows = $this->maxRows($page);

        foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
            $rawRows = $worksheet->toArray(null, false, true, false);
            $headerIndex = $this->headerIndex($rawRows);

            if ($headerIndex === null) {
                continue;
            }

            $lastColumn = $this->lastUsedColumn($rawRows, $headerIndex);

            if ($lastColumn >= self::MAX_COLUMNS) {
                throw ValidationException::withMessages([
                    'import' => '单个汇总表最多支持 ' . self::MAX_COLUMNS . ' 列。',
                ]);
            }

            $headers = [];
            for ($column = 0; $column <= $lastColumn; $column++) {
                $label = $this->cell($rawRows[$headerIndex][$column] ?? null);
                $headers[] = $label !== '' ? $label : '未命名列' . ($column + 1);
            }

            $rows = [];
            foreach (array_slice($rawRows, $headerIndex + 1) as $rawRow) {
                $row = [];
                $hasValue = false;

                for ($column = 0; $column <= $lastColumn; $column++) {
                    $value = $this->cell($rawRow[$column] ?? null);
                    $row[] = $value;
                    $hasValue = $hasValue || $value !== '';
                }

                if (! $hasValue) {
                    continue;
                }

                $rows[] = $row;

                if (count($rows) > $maxRows) {
                    throw ValidationException::withMessages([
                        'import' => '单次最多导入 ' . number_format($maxRows) . ' 条汇总数据，请拆分文件后重试。',
                    ]);
                }
            }

            if ($rows === []) {
                throw ValidationException::withMessages(['import' => '表格中没有可导入的数据行。']);
            }

            return [$headers, $rows];
        }

        throw ValidationException::withMessages(['import' => '没有找到有效的表头和数据。']);
    }

    private function headerIndex(array $rows): ?int
    {
        $firstNonEmpty = null;

        foreach (array_slice($rows, 0, 20, true) as $index => $row) {
            $count = count(array_filter(
                $row,
                fn (mixed $value): bool => $this->cell($value) !== '',
            ));

            if ($count === 0) {
                continue;
            }

            $firstNonEmpty ??= $index;

            if ($count >= 2) {
                return $index;
            }
        }

        return $firstNonEmpty;
    }

    private function lastUsedColumn(array $rows, int $headerIndex): int
    {
        $lastColumn = -1;

        foreach (array_slice($rows, $headerIndex) as $row) {
            foreach ($row as $column => $value) {
                if ($this->cell($value) !== '') {
                    $lastColumn = max($lastColumn, (int) $column);
                }
            }
        }

        return max($lastColumn, 0);
    }

    private function cell(mixed $value): string
    {
        if ($value === null) {
            return '';
        }

        if (is_bool($value)) {
            return $value ? '是' : '否';
        }

        if (! is_scalar($value) && ! $value instanceof \Stringable) {
            return '';
        }

        $value = trim((string) $value);

        if (mb_strlen($value) > self::MAX_CELL_LENGTH) {
            throw ValidationException::withMessages(['import' => '单元格内容过长，请精简后重试。']);
        }

        return $value;
    }
}