process_sheet/fastadmin/application/common/library/ps/BizExtractor.php

<?php

namespace app\common\library\ps;

use think\Exception;

/**
 * 从表格/文本/图片中提取产品或工单。优先规则解析表格，再用大模型补全。
 */
class BizExtractor
{
    /**
     * @param string $filePath
     * @param string $type product|workorder
     * @return array {items, source, model}
     * @throws Exception
     */
    public static function extract($filePath, $type = 'workorder')
    {
        $type = $type === 'product' ? 'product' : 'workorder';
        if (!is_file($filePath)) {
            throw new Exception('文件不存在');
        }
        $ext = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
        $text = '';
        $hintRows = [];
        $model = '';

        if (in_array($ext, ['csv', 'txt', 'json'], true)) {
            $text = (string)file_get_contents($filePath);
            $hintRows = self::parseTabular($text, $type);
        } elseif (in_array($ext, ['xlsx', 'xls'], true)) {
            $text = self::xlsxToText($filePath);
            $hintRows = self::parseTabular($text, $type);
        } elseif ($ext === 'pdf') {
            $text = self::pdfText($filePath);
            if ($text === '' && class_exists('\Imagick')) {
                $img = self::pdfFirstImage($filePath);
                if ($img) {
                    try {
                        $got = self::visionExtract($img, $type);
                        @unlink($img);
                        return [
                            'items'  => self::normalizeItems($got['items'], $type),
                            'source' => 'vision',
                            'model'  => $got['model'],
                        ];
                    } catch (\Exception $e) {
                        @unlink($img);
                    }
                }
            }
            $hintRows = self::parseTabular($text, $type);
        } elseif (in_array($ext, ['png', 'jpg', 'jpeg', 'webp', 'bmp', 'gif'], true)) {
            $got = self::visionExtract($filePath, $type);
            return [
                'items'  => self::normalizeItems($got['items'], $type),
                'source' => 'vision',
                'model'  => $got['model'],
            ];
        } else {
            throw new Exception('不支持的文件类型，请上传 csv / xlsx / txt / pdf / 图片');
        }

        $items = $hintRows;
        $ai = self::llmExtract($text, $type, $hintRows);
        if (!empty($ai['items'])) {
            $items = $ai['items'];
            $model = $ai['model'];
        }
        if (!$items) {
            throw new Exception('未能从文件中识别出有效的' . ($type === 'product' ? '产品' : '工单') . '信息');
        }
        return [
            'items'  => self::normalizeItems($items, $type),
            'source' => $model ? 'ai' : 'file',
            'model'  => $model,
        ];
    }

    /**
     * @param array  $items
     * @param string $type
     * @return array
     */
    public static function normalizeItems(array $items, $type)
    {
        $out = [];
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }
            $processes = BizIngest::normalizeProcesses($item);
            if ($type === 'product') {
                $code = trim((string)($item['code'] ?? $item['product_code'] ?? $item['no'] ?? ''));
                $name = trim((string)($item['name'] ?? $item['product_name'] ?? ''));
                if ($code === '' && $name === '') {
                    continue;
                }
                $out[] = [
                    'code'          => $code,
                    'name'          => $name !== '' ? $name : $code,
                    'spec'          => trim((string)($item['spec'] ?? '')),
                    'unit'          => trim((string)($item['unit'] ?? '件')),
                    'qty'           => (float)($item['qty'] ?? $item['quantity'] ?? $item['total_qty'] ?? 0),
                    'sheet_qty'     => (float)($item['sheet_qty'] ?? $item['per_sheet'] ?? 0),
                    'remark'        => trim((string)($item['remark'] ?? '')),
                    'process_count' => count($processes) ?: (int)($item['process_count'] ?? 0),
                    'processes'     => $processes,
                ];
            } else {
                $no = trim((string)($item['no'] ?? $item['workorder_no'] ?? $item['code'] ?? ''));
                if ($no === '' && empty($item['product_code']) && empty($item['name'])) {
                    continue;
                }
                $out[] = [
                    'no'            => $no,
                    'product_code'  => trim((string)($item['product_code'] ?? '')),
                    'product_name'  => trim((string)($item['product_name'] ?? $item['name'] ?? '')),
                    'qty'           => (float)($item['qty'] ?? $item['quantity'] ?? $item['total_qty'] ?? 0),
                    'sheet_qty'     => (float)($item['sheet_qty'] ?? $item['per_sheet'] ?? 0),
                    'batch_no'      => trim((string)($item['batch_no'] ?? $item['batch'] ?? '')),
                    'customer'      => trim((string)($item['customer'] ?? '')),
                    'plan_date'     => trim((string)($item['plan_date'] ?? $item['date'] ?? '')),
                    'remark'        => trim((string)($item['remark'] ?? '')),
                    'process_count' => count($processes) ?: (int)($item['process_count'] ?? 0),
                    'processes'     => $processes,
                ];
            }
        }
        return $out;
    }

    protected static function llmExtract($text, $type, array $hintRows)
    {
        $text = trim((string)$text);
        if ($text === '') {
            return ['items' => $hintRows, 'model' => ''];
        }
        if (mb_strlen($text) > 12000) {
            $text = mb_substr($text, 0, 12000);
        }
        $online = LlmClient::online();
        $client = $online->enabled() ? $online : LlmClient::local();
        if (!$client->enabled()) {
            return ['items' => $hintRows, 'model' => ''];
        }
        $kind = $type === 'product' ? '产品' : '工单';
        $schema = $type === 'product'
            ? '{"items":[{"code":"产品编号","name":"名称","spec":"规格","unit":"单位","process_count":2,"processes":[{"code":"工艺编号","seq":1,"name":"工序名","workstation":"工位","standard_time":"工时","qc_points":""}]}]}'
            : '{"items":[{"no":"工单号","product_code":"产品编号","product_name":"产品名称","qty":100,"batch_no":"批次","customer":"客户","plan_date":"2026-01-01","process_count":2,"processes":[{"code":"工艺编号","seq":1,"name":"工序名","workstation":"工位","standard_time":"工时","qc_points":""}]}]}';
        $prompt = "从下面内容提取{$kind}信息，只输出 JSON，不要解释。格式：{$schema}\n"
            . "qty 是总数量，process_count 是工序条数，processes 里 code 是工艺编号。可有多条。\n\n"
            . $text;
        try {
            $raw = $client->chat($prompt, null, true);
            $parsed = self::decodeJson($raw);
            $items = isset($parsed['items']) && is_array($parsed['items']) ? $parsed['items'] : [];
            return ['items' => $items ?: $hintRows, 'model' => $client->textModel()];
        } catch (\Exception $e) {
            return ['items' => $hintRows, 'model' => ''];
        }
    }

    protected static function visionExtract($imagePath, $type)
    {
        $client = LlmClient::forRecognize();
        if (!$client->enabled()) {
            throw new Exception('未配置可用的视觉模型，无法识别图片');
        }
        $kind = $type === 'product' ? '产品' : '工单';
        $prompt = "提取图中的{$kind}：编号、名称、总数量、工序/工艺编号和工序名。只输出 JSON："
            . '{"items":[{"no":"","code":"","name":"","qty":0,"processes":[{"code":"","seq":1,"name":""}]}]}';
        $got = $client->vision($prompt, $imagePath);
        $parsed = self::decodeJson($got['content'] ?? '');
        $items = isset($parsed['items']) && is_array($parsed['items']) ? $parsed['items'] : [];
        return ['items' => $items, 'model' => $got['model'] ?? ''];
    }

    protected static function decodeJson($raw)
    {
        $raw = trim((string)$raw);
        if ($raw === '') {
            return [];
        }
        $try = json_decode($raw, true);
        if (is_array($try)) {
            return $try;
        }
        if (preg_match('/\{[\s\S]*\}/', $raw, $m)) {
            $try = json_decode($m[0], true);
            if (is_array($try)) {
                return $try;
            }
        }
        return [];
    }

    protected static function parseTabular($text, $type)
    {
        $lines = preg_split("/\r\n|\n|\r/", trim((string)$text));
        if (!$lines) {
            return [];
        }
        $rows = [];
        foreach ($lines as $line) {
            $line = trim($line);
            if ($line === '') {
                continue;
            }
            $rows[] = preg_split("/\t|,|，|;|；/", $line);
        }
        if (count($rows) < 2) {
            return [];
        }
        $header = array_map(function ($v) {
            return strtolower(trim((string)$v));
        }, $rows[0]);
        $map = self::headerMap($header, $type);
        $items = [];
        $grouped = [];
        for ($i = 1; $i < count($rows); $i++) {
            $assoc = [];
            foreach ($map as $idx => $key) {
                $assoc[$key] = isset($rows[$i][$idx]) ? trim((string)$rows[$i][$idx]) : '';
            }
            $pk = $type === 'product' ? ($assoc['code'] ?? '') : ($assoc['no'] ?? '');
            if ($pk === '') {
                $pk = 'row' . $i;
            }
            if (!isset($grouped[$pk])) {
                $grouped[$pk] = $assoc;
                $grouped[$pk]['processes'] = [];
            }
            $pName = $assoc['process_name'] ?? '';
            $pCode = $assoc['process_code'] ?? '';
            if ($pName !== '' || $pCode !== '') {
                $grouped[$pk]['processes'][] = [
                    'code' => $pCode,
                    'name' => $pName,
                    'seq'  => count($grouped[$pk]['processes']) + 1,
                ];
            }
        }
        foreach ($grouped as $item) {
            $items[] = $item;
        }
        return $items;
    }

    protected static function headerMap(array $header, $type)
    {
        $map = [];
        foreach ($header as $i => $h) {
            $h = str_replace(' ', '', $h);
            if (preg_match('/工单|workorder|^no$/', $h)) {
                $map[$i] = 'no';
            } elseif (preg_match('/产品编号|产品编码|物料|sku|^code$/', $h)) {
                $map[$i] = $type === 'product' ? 'code' : 'product_code';
            } elseif (preg_match('/产品名|名称|^name$/', $h)) {
                $map[$i] = $type === 'product' ? 'name' : 'product_name';
            } elseif (preg_match('/每版|每张|sheet_qty|tldx/', $h)) {
                $map[$i] = 'sheet_qty';
            } elseif (preg_match('/数量|qty|quantity/', $h)) {
                $map[$i] = 'qty';
            } elseif (preg_match('/批次|batch/', $h)) {
                $map[$i] = 'batch_no';
            } elseif (preg_match('/客户|customer/', $h)) {
                $map[$i] = 'customer';
            } elseif (preg_match('/日期|date/', $h)) {
                $map[$i] = 'plan_date';
            } elseif (preg_match('/规格|spec/', $h)) {
                $map[$i] = 'spec';
            } elseif (preg_match('/单位|unit/', $h)) {
                $map[$i] = 'unit';
            } elseif (preg_match('/工艺编号|工序编号|processcode|op.?code/', $h)) {
                $map[$i] = 'process_code';
            } elseif (preg_match('/工艺名|工序名|processname|工序/', $h)) {
                $map[$i] = 'process_name';
            }
        }
        return $map;
    }

    protected static function xlsxToText($path)
    {
        if (!class_exists('ZipArchive')) {
            return (string)@file_get_contents($path);
        }
        $zip = new \ZipArchive();
        if ($zip->open($path) !== true) {
            return '';
        }
        $shared = [];
        $ss = $zip->getFromName('xl/sharedStrings.xml');
        if ($ss) {
            if (preg_match_all('/<t[^>]*>([^<]*)<\/t>/', $ss, $m)) {
                $shared = $m[1];
            }
        }
        $sheet = $zip->getFromName('xl/worksheets/sheet1.xml');
        $zip->close();
        if (!$sheet) {
            return '';
        }
        $rows = [];
        if (preg_match_all('/<row[^>]*>([\s\S]*?)<\/row>/', $sheet, $rowMatch)) {
            foreach ($rowMatch[1] as $rowXml) {
                $cells = [];
                if (preg_match_all('/<c[^>]*?(t="s")?[^>]*>(?:<v>([^<]*)<\/v>)?/', $rowXml, $cMatch, PREG_SET_ORDER)) {
                    foreach ($cMatch as $c) {
                        $val = isset($c[2]) ? $c[2] : '';
                        if (!empty($c[1]) && isset($shared[(int)$val])) {
                            $val = html_entity_decode($shared[(int)$val], ENT_QUOTES, 'UTF-8');
                        }
                        $cells[] = $val;
                    }
                }
                if ($cells) {
                    $rows[] = implode("\t", $cells);
                }
            }
        }
        return implode("\n", $rows);
    }

    protected static function pdfText($path)
    {
        $bin = IS_WIN ? 'pdftotext.exe' : 'pdftotext';
        $tmp = tempnam(sys_get_temp_dir(), 'pstxt');
        @exec(escapeshellarg($bin) . ' -enc UTF-8 ' . escapeshellarg($path) . ' ' . escapeshellarg($tmp), $out, $code);
        $text = '';
        if ($code === 0 && is_file($tmp)) {
            $text = trim((string)file_get_contents($tmp));
        }
        @unlink($tmp);
        return $text;
    }

    protected static function pdfFirstImage($pdfPath)
    {
        try {
            $im = new \Imagick();
            $im->setResolution(120, 120);
            $im->readImage($pdfPath . '[0]');
            $im->setImageFormat('jpeg');
            $tmp = tempnam(sys_get_temp_dir(), 'psimp') . '.jpg';
            $im->writeImage($tmp);
            $im->clear();
            $im->destroy();
            return is_file($tmp) ? $tmp : '';
        } catch (\Exception $e) {
            return '';
        }
    }
}