123 lines
4.4 KiB
Python
123 lines
4.4 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
联调冒烟：用桌面海运样表抽单元格清单 → 真网千问 sheet_map。
|
|
不经主账 HTTP（本机 8180 未起）；验证「提示词+模型」能否产出合法 mappingJson。
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import json
|
|
import os
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
DESK = Path(r"C:/Users/Administrator/Desktop/海陆空报价单模板最新-0917/海陆空报价单模板最新-0917")
|
|
|
|
|
|
def load_dotenv(path: Path) -> None:
|
|
if not path.is_file():
|
|
return
|
|
for line in path.read_text(encoding="utf-8", errors="replace").splitlines():
|
|
s = line.strip()
|
|
if not s or s.startswith("#") or "=" not in s:
|
|
continue
|
|
k, v = s.split("=", 1)
|
|
os.environ.setdefault(k.strip(), v.strip().strip('"').strip("'"))
|
|
|
|
|
|
def build_inventory_openpyxl(xlsx: Path, max_lines: int = 1800) -> dict:
|
|
import openpyxl
|
|
|
|
wb = openpyxl.load_workbook(xlsx, data_only=False)
|
|
ws = wb.active
|
|
yellow_approx = 0
|
|
lines: list[str] = []
|
|
merges = {}
|
|
for rng in ws.merged_cells.ranges:
|
|
origin = f"{openpyxl.utils.get_column_letter(rng.min_col)}{rng.min_row}"
|
|
merges[origin] = str(rng)
|
|
for row in ws.iter_rows(max_row=120, max_col=20):
|
|
for cell in row:
|
|
val = cell.value
|
|
text = "" if val is None else str(val).strip()
|
|
fill = ""
|
|
try:
|
|
fg = cell.fill.fgColor
|
|
if fg and fg.rgb and isinstance(fg.rgb, str) and fg.rgb not in ("00000000", "0"):
|
|
fill = fg.rgb[-6:].upper()
|
|
if fill.startswith("FF") and len(fg.rgb) >= 8:
|
|
fill = fg.rgb[-6:].upper()
|
|
if fill in {"FFFF00", "FFEB9C", "FFC000", "FFFF99", "FFCC00", "FFD966", "FFF2CC", "FFE699"} or (
|
|
len(fill) == 6 and int(fill[0:2], 16) > 200 and int(fill[2:4], 16) > 180 and int(fill[4:6], 16) < 160
|
|
):
|
|
yellow_approx += 1
|
|
except Exception:
|
|
pass
|
|
addr = cell.coordinate
|
|
merge = merges.get(addr, "-")
|
|
if not text and merge == "-" and not fill:
|
|
continue
|
|
safe = text.replace("|", "/")[:120]
|
|
lines.append(f"{ws.title}!{addr} | {safe} | {merge} | {fill or '-'}")
|
|
if len(lines) >= max_lines:
|
|
break
|
|
if len(lines) >= max_lines:
|
|
break
|
|
return {
|
|
"sheet": ws.title,
|
|
"cellInventory": lines,
|
|
"cellCount": len(lines),
|
|
"yellowCount": yellow_approx,
|
|
"fileName": xlsx.name,
|
|
"transportMode": "sea",
|
|
"versionId": "smoke-local",
|
|
}
|
|
|
|
|
|
def main() -> int:
|
|
load_dotenv(ROOT.parent / ".env")
|
|
# 本脚本强制允许出站，否则 Worker 配置会挡掉真网
|
|
os.environ["LLM_ALLOW_NETWORK"] = "true"
|
|
os.environ["LLM_DATA_USAGE_CONFIRMED"] = "1"
|
|
sys.path.insert(0, str(ROOT))
|
|
|
|
sea = DESK / "海运报价单模板.xlsx"
|
|
if not sea.is_file():
|
|
print("FAIL missing", sea)
|
|
return 2
|
|
payload = build_inventory_openpyxl(sea, max_lines=800)
|
|
print(
|
|
f"inventory sheet={payload['sheet']} cells={payload['cellCount']} yellow≈{payload['yellowCount']}",
|
|
flush=True,
|
|
)
|
|
|
|
from agent.llm.mode_sheet_map import invoke_sheet_map
|
|
|
|
print("calling qwen sheet_map …", flush=True)
|
|
result = invoke_sheet_map(payload, allow_network=True, timeout_seconds=90.0)
|
|
if not result.get("ok"):
|
|
print("FAIL AI", result.get("error"))
|
|
if result.get("raw_preview"):
|
|
print("raw_preview", result["raw_preview"][:300])
|
|
return 1
|
|
mapping = result["mapping"]
|
|
fields = mapping.get("fields") or {}
|
|
fees = mapping.get("feeRegions") or []
|
|
routes = mapping.get("routeRegions") or []
|
|
print("OK AI mapping")
|
|
print(" transportMode=", mapping.get("transportMode"))
|
|
print(" layoutType=", mapping.get("layoutType"))
|
|
print(" fields=", len(fields), sorted(fields.keys())[:12])
|
|
print(" feeRegions=", len(fees))
|
|
print(" routeRegions=", len(routes))
|
|
print(" unmappedRequired=", mapping.get("unmappedRequired"))
|
|
print(" confidence=", mapping.get("confidence"))
|
|
out = ROOT / "scripts" / "_last_sea_mapping.json"
|
|
out.write_text(json.dumps(mapping, ensure_ascii=False, indent=2), encoding="utf-8")
|
|
print("saved", out)
|
|
return 0
|
|
|
|
|
|
if __name__ == "__main__":
|
|
raise SystemExit(main())
|