"""单文件流式入库:CSV/XLSX → 本地 SQLite 工作库(无需任何数据库服务)。 入库阶段完成: * 字段别名归一(支持中文表头); * 范围校验:10 位 HS、进出口方向、截止日期、ISO 年周合法性; * 周四归月:整周记入“周四年份-月份”,不拆分跨月周; * 明细审计:原始行、解析结果、数值异常全部落表,便于复核。 聚合表 ``monthly`` 是全流程唯一的取数入口,所有报表都从它派生, 因此“同一批记录、不同视图”的口径可以被显式表达。 """ from __future__ import annotations import csv import hashlib import json import sqlite3 import re import unicodedata from collections import Counter from ...source import iter_source from ...query_indexes import create_monthly_indexes from collections.abc import Callable, Iterator from datetime import date from pathlib import Path from .numbers import StrictSum, number from .rules import MEMORY_HS, Rules #: fact 表维度列(顺序即主键顺序) DIMENSIONS = [ "direction", "year", "month", "c1", "c2", "c3", "c4", "brand", "model", "brand_country", "app", "origin", "trade_country", "dispatch", "port", "port_country", "mapped", ] #: 必需字段 REQUIRED = ["I_E_FLAG", "HS_CODE", "YEAR", "WEEK", "MONORSUM", "COUNT", "G_MODEL"] #: 中文/别名表头 → 规范字段名 ALIASES = {'MONORSUM': 'MONORSUM', '金额': 'MONORSUM', '金额(美元)': 'MONORSUM', '金额(美元)': 'MONORSUM', '金额:美元': 'MONORSUM', '美元金额': 'MONORSUM', '成交金额': 'MONORSUM', '总金额': 'MONORSUM', '进出口': 'I_E_FLAG', '进/出口': 'I_E_FLAG', '进出口标志': 'I_E_FLAG', '进口/出口': 'I_E_FLAG', '进出口类型': 'I_E_FLAG', 'HS编码': 'HS_CODE', 'HS 编码': 'HS_CODE', '商品编码': 'HS_CODE', '商品编号': 'HS_CODE', '海关编码': 'HS_CODE', '税则号列': 'HS_CODE', '商品编码(10位)': 'HS_CODE', '年份': 'YEAR', '年度': 'YEAR', '周': 'WEEK', '周数': 'WEEK', 'ISO周': 'WEEK', '统计周': 'WEEK', '数量': 'COUNT', '成交数量': 'COUNT', '数量(成交)': 'COUNT', '数量(成交)': 'COUNT', '品牌/型号': 'G_MODEL', '品牌型号': 'G_MODEL', '申报要素': 'G_MODEL', '规格型号': 'G_MODEL', '原产国': 'ORIGIN_COUNTRY', '原产国(地区)': 'ORIGIN_COUNTRY', '贸易国': 'TRADE_AREA_CODE', '贸易国(地区)': 'TRADE_AREA_CODE', '启运国': 'TRADE_COUNTRY', '启运国(地区)': 'TRADE_COUNTRY', '经停港/指运港': 'DISTINATE_PORT', '经停港': 'DISTINATE_PORT', '指运港': 'DISTINATE_PORT', '目的国': 'DESTINATION_COUNTRY', '境内目的地/境内货源地': 'DISTRICT_CODE', '境内目的地': 'DISTRICT_CODE', '贸易方式': 'TRADE_MODE', '申报地海关': 'DECLARING_CUSTOMS', '运输方式': 'TRAF_MODE', '包装种类': 'PACKING_TYPE', 'MONORSUM_USD': 'MONORSUM'} Progress = Callable[[str, float], None] def connect(path: str | Path) -> sqlite3.Connection: database = sqlite3.connect(str(path)) database.row_factory = sqlite3.Row database.create_aggregate("dsum", 1, StrictSum) database.execute("PRAGMA temp_store=FILE") database.execute("PRAGMA cache_size=-65536") return database def sha256_file(path: str | Path, chunk: int = 1 << 20) -> str: digest = hashlib.sha256() with Path(path).open("rb") as handle: for block in iter(lambda: handle.read(chunk), b""): digest.update(block) return digest.hexdigest() def _iter_source_rows(source: Path, encoding: str, delimiter: str): # Retained for callers using the engine's previous reader helper. for row_id, end_line, headers, values in iter_source(source, encoding, delimiter): yield end_line, headers, values def normalize_header(value): return unicodedata.normalize("NFKC", str(value or "")).replace("\ufeff", "").replace("\u200b", "").strip() def normalize_code(value): text = normalize_header(value) # Excel may export integer code cells with a .0 suffix. No rounding or padding. if re.fullmatch(r"[0-9]+\.0+", text): text = text.split('.')[0] return text def ingest(config: dict, rules: Rules, out: Path, progress: Progress | None = None) -> tuple[sqlite3.Connection, dict]: """把源文件流式写入 ``working.sqlite``,返回连接与运行统计。""" def report(message: str, fraction: float) -> None: if progress: progress(message, fraction) source = Path(config["input_csv"]) database = connect(out / "working.sqlite") database.execute("CREATE TABLE raw(row_id INTEGER PRIMARY KEY, end_line INTEGER, original_json TEXT, status TEXT)") columns = ",".join(f"{name} TEXT" for name in DIMENSIONS) database.execute(f"CREATE TABLE fact(row_id INTEGER,{columns},amount TEXT,quantity TEXT)") database.execute("CREATE TABLE audit(row_id INTEGER, hs TEXT, raw_model TEXT, parsed_json TEXT)") database.execute("CREATE TABLE issues(row_id INTEGER, field TEXT, raw_value TEXT, reason TEXT)") cutoff = date.fromisoformat(config["cutoff_date"]) if config.get("cutoff_date") else date.today() stats: dict = { "total": 0, "included": 0, "excluded": 0, "alerts": 0, "min_month": None, "max_month": None, "recognition": {}, "exclusion_reasons": {}, } def issue(row_id: int, field: str, raw: object, reason: str) -> None: database.execute("INSERT INTO issues VALUES(?,?,?,?)", (row_id, field, str(raw), reason)) stats["alerts"] += 1 headers_seen: list[str] = [] recognition = Counter() exclusions = Counter() report("读取源文件…", 0.02) for row_id, end_line, headers, values in iter_source(source, config.get("encoding", "auto"), config.get("delimiter", "auto")): if values is None: cleaned = [normalize_header(name) for name in headers] lookup = {normalize_header(key).upper(): value for key, value in ALIASES.items()} headers_seen = [lookup.get(name.upper(), name.upper()) for name in cleaned] if "MONORSUM" not in headers_seen and any("人民币" in name or "RMB" in name.upper() or "CNY" in name.upper() for name in cleaned): raise ValueError("发现人民币金额列;本工具统计口径为美元,请先换汇并明确提供 MONORSUM_USD") stats["input_fields"] = headers_seen if len(set(headers_seen)) != len(headers_seen): raise ValueError("源文件存在重复字段名") missing = set(REQUIRED) - set(headers_seen) if missing: raise ValueError(f"缺少必需字段: {sorted(missing)}") continue stats["total"] += 1 if len(values) != len(headers_seen): row = dict(zip(headers_seen, values)) status = "CSV列数不一致" else: row = dict(zip(headers_seen, values)) status = "" hs = normalize_code(row.get("HS_CODE")) direction_raw = normalize_code(row.get("I_E_FLAG")).upper() direction = {"进口": "I", "出口": "E"}.get(direction_raw, direction_raw) if not normalize_header(row.get("G_MODEL")): status = status or "G_MODEL为空" thursday: date | None try: thursday = date.fromisocalendar(int(normalize_code(row["YEAR"])), int(normalize_code(row["WEEK"])), 4) except (ValueError, TypeError, KeyError): thursday = None status = status or "无效ISO年份或周号" if len(hs) != 10 or not hs.isdigit(): status = status or "HS必须为10位文本" elif hs not in rules.cap and hs not in MEMORY_HS: status = status or "不在项目HS范围" if direction not in config["directions"]: status = status or "不在配置进出口范围" if thursday and not int(config.get("start_year", 1900)) <= thursday.year <= int(config.get("end_year", 9998)): status = status or "不在配置年份范围" if thursday and thursday > cutoff: status = status or "晚于截止日期的周四" database.execute( "INSERT INTO raw VALUES(?,?,?,?)", (row_id, end_line, json.dumps(row, ensure_ascii=False), status or "纳入"), ) for key in ("MONORSUM", "COUNT"): value = number(row.get(key)) if value is None: issue(row_id, key, row.get(key), "无法识别;受影响指标留空") elif value <= 0: issue(row_id, key, row.get(key), "零值或负数;保留原值") if status: stats["excluded"] += 1 exclusions[status] += 1 issue(row_id, "记录范围", hs, status) else: parsed = rules.parse(hs, row["G_MODEL"]) recognition["brand_resolved" if parsed["brand"] else "brand_unresolved"] += 1 recognition["model_present" if parsed["model"] else "model_absent"] += 1 for label, marker in [("explicit_no_brand", "无品牌标记"), ("explicit_no_model", "无型号标记"), ("unknown_brand", "疑似缺品牌:"), ("brand_conflict", "品牌映射冲突")]: if marker in parsed.get("flags", ""): recognition[label] += 1 port, port_country = rules.port(row.get("DISTINATE_PORT")) record = dict( parsed, direction=direction, year=str(thursday.year), month=f"{thursday:%Y-%m}", origin=rules.country(row.get("ORIGIN_COUNTRY")), trade_country=rules.country(row.get("TRADE_AREA_CODE")), dispatch=rules.country(row.get("TRADE_COUNTRY")), port=port, port_country=port_country, ) placeholders = ",".join("?" for _ in range(len(DIMENSIONS) + 3)) database.execute( f"INSERT INTO fact VALUES({placeholders})", [row_id] + [str(record[key]) for key in DIMENSIONS] + [row["MONORSUM"], row["COUNT"]], ) database.execute( "INSERT INTO audit VALUES(?,?,?,?)", (row_id, hs, row["G_MODEL"], json.dumps(parsed, ensure_ascii=False)), ) stats["included"] += 1 month = record["month"] stats["min_month"] = min(stats["min_month"] or month, month) stats["max_month"] = max(stats["max_month"] or month, month) if row_id % 10000 == 0: database.commit() report(f"已读取 {row_id:,} 条记录", min(0.55, 0.02 + row_id / max(stats["total"], 1) * 0.1)) database.commit() report("汇总月度中间数据…", 0.6) dimensions = ",".join(DIMENSIONS) database.execute( f"CREATE TABLE monthly AS SELECT {dimensions},dsum(amount) amount,dsum(quantity) quantity,count(*) n " f"FROM fact GROUP BY {dimensions}" ) create_monthly_indexes(database) database.commit() stats.update( recognition=dict(recognition), exclusion_reasons=dict(exclusions), sha256=sha256_file(source), cutoff=str(cutoff), partial_year_rule="仅 confirmed_complete_years 明确确认为全年,其余均为已提供期间暂算", ) return database, stats class Store: """聚合查询封装:只暴露“维度 + 过滤 → 金额/数量/记录数”。""" def __init__(self, database: sqlite3.Connection) -> None: self.db = database def aggregate(self, dimensions: tuple = (), filters: dict | None = None) -> Iterator[dict]: dimensions = tuple(dimensions) if any(key not in DIMENSIONS for key in dimensions): raise ValueError(f"非法维度: {dimensions}") filters = filters or {} if any(key not in DIMENSIONS for key in filters): raise ValueError(f"非法过滤字段: {filters}") clause = " AND ".join(f"{key}=?" for key in filters) or "1=1" select = (",".join(dimensions) + ",") if dimensions else "" group = " GROUP BY " + ",".join(dimensions) if dimensions else "" query = f"SELECT {select}dsum(amount) amount,dsum(quantity) quantity,sum(n) n FROM monthly WHERE {clause}{group}" + (" ORDER BY " + ",".join(dimensions) if dimensions else "") for row in self.db.execute(query, list(filters.values())): result = dict(row) result["amount"] = number(row["amount"]) result["quantity"] = number(row["quantity"]) result["n"] = row["n"] or 0 yield result def total(self, filters: dict) -> dict: return next(self.aggregate(filters=filters)) def week_rows(start: int, end: int) -> Iterator[list]: """生成“ISO 年周 → 归属年/月”的核对表。""" for year in range(start - 1, end + 2): for week in range(1, 54): try: thursday = date.fromisocalendar(year, week, 4) except ValueError: continue yield [ year, week, str(date.fromisocalendar(year, week, 1)), str(date.fromisocalendar(year, week, 7)), str(thursday), thursday.year, thursday.month, "ISO;整周按周四归月", ]