reports.py 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569
  1. """按《统计类数据输出格式.xlsx》生成全部统计表。
  2. 本模块只负责“把聚合结果写进正确的表、正确的行”,所有口径都写在字段里:
  3. * ``实际金额(美元)``/``成交数量`` 是原始真值;
  4. * ``金额指数``/``数量指数``/``价格指数`` 是以 2025-01 为 1000 的固定基期指数;
  5. * ``金额占比``/``数量占比`` 的分母写在 ``占比分母范围`` 列,绝不跨层级求和;
  6. * ``金额环比``/``数量环比`` 是与上月比,不是与基期比;
  7. * 无记录/缺数/分母为零一律留空,并把原因写进 ``计算说明``。
  8. """
  9. from __future__ import annotations
  10. import csv
  11. import json
  12. import heapq
  13. import sqlite3
  14. from decimal import Decimal
  15. from pathlib import Path
  16. from openpyxl import load_workbook
  17. from .excel import Output
  18. from .numbers import D, growth, index_of, ratio, weights
  19. from .storage import Store, week_rows
  20. #: 固定基期
  21. BASE = "2025-01"
  22. #: 指标列(顺序即输出顺序)
  23. METRICS = [
  24. "实际金额(美元)",
  25. "成交数量",
  26. "平均单价(美元/成交数量单位)",
  27. "金额指数",
  28. "数量指数",
  29. "价格指数",
  30. "金额占比",
  31. "数量占比",
  32. "金额环比",
  33. "数量环比",
  34. "占比分母范围",
  35. "记录数",
  36. "计算说明",
  37. "年度数据状态",
  38. ]
  39. #: 辅助维度列(模板没有的会追加)
  40. EXTRA = [
  41. "统计层级",
  42. "统计范围",
  43. "选样年度",
  44. "选样年度状态",
  45. "应用领域标签",
  46. "品牌国别",
  47. "品牌",
  48. "前十大型号",
  49. "港口所属国家地区",
  50. "港口统计粒度",
  51. ]
  52. HEADER_ALIASES = {
  53. "前五大型号": "前十大型号",
  54. "进口额": "实际金额(美元)",
  55. "进口量": "成交数量",
  56. "价格": "平均单价(美元/成交数量单位)",
  57. }
  58. SUPPLEMENTARY = [
  59. ("样本权重", ["进/出口", "基础类目", "选样年度", "排名", "品牌", "型号", "年度金额(美元)", "权重", "样本数量", "权重规则", "年度数据状态"]),
  60. ("选样排除", ["进/出口", "基础类目", "选样年度", "品牌", "型号", "年度金额(美元)", "原因"]),
  61. ("重点全量层级", ["进/出口", "年度", "专项", "层级", "父级路径", "名称", "入选", "实际金额(美元)", "成交数量", "金额占比", "数量占比", "占比分母范围", "计算说明"]),
  62. ("供应链条件占比", ["进/出口", "年度", "专项", "港口统计粒度", "分析方式", "层级", "父级路径", "节点", "实际金额(美元)", "成交数量", "金额占比", "数量占比", "占比分母范围"]),
  63. ("周四归月映射", ["ISO年", "ISO周", "周一日期", "周日日期", "周四日期", "归属年", "归属月", "规则"]),
  64. ("数据质量摘要", ["项目", "值", "说明"]),
  65. ("映射冲突", ["类型", "匹配键", "冲突内容"]),
  66. ]
  67. def previous(month: str) -> str:
  68. """上一个自然月。"""
  69. year, value = map(int, month.split("-"))
  70. return f"{year - 1}-12" if value == 1 else f"{year}-{value - 1:02d}"
  71. def metric(value: dict, base: dict | None = None, prev: dict | None = None, parent: dict | None = None, denominator: str = "") -> dict:
  72. """构造一行指标:真值、指数、占比、环比、原因说明。"""
  73. base, prev, parent = base or {}, prev or {}, parent or {}
  74. amount, quantity = value.get("amount"), value.get("quantity")
  75. price = ratio(amount, quantity)
  76. result = dict(
  77. zip(
  78. METRICS[:11],
  79. [
  80. amount,
  81. quantity,
  82. price,
  83. index_of(amount, base.get("amount")),
  84. index_of(quantity, base.get("quantity")),
  85. index_of(price, ratio(base.get("amount"), base.get("quantity"))),
  86. ratio(amount, parent.get("amount")),
  87. ratio(quantity, parent.get("quantity")),
  88. growth(amount, prev.get("amount")),
  89. growth(quantity, prev.get("quantity")),
  90. denominator,
  91. ],
  92. )
  93. )
  94. result["记录数"] = value.get("n", 0)
  95. reasons = []
  96. if not value.get("n"):
  97. reasons.append("无记录,未按零填补")
  98. elif amount is None or quantity is None:
  99. reasons.append("金额或数量含无法识别值")
  100. if price is None:
  101. reasons.append("平均单价缺数或数量为零")
  102. if base:
  103. if result["金额指数"] is None:
  104. reasons.append("金额指数缺数或基期金额为零")
  105. if result["数量指数"] is None:
  106. reasons.append("数量指数缺数或基期数量为零")
  107. if result["价格指数"] is None:
  108. reasons.append("价格指数缺数或基期价格为零")
  109. if prev and (result["金额环比"] is None or result["数量环比"] is None):
  110. reasons.append("环比缺上月数或上月分母为零")
  111. result["计算说明"] = ";".join(reasons)
  112. return result
  113. class Reports:
  114. """一次完整运行的报表生成器。"""
  115. def __init__(self, database: sqlite3.Connection, config: dict, stats: dict, rules, out: Path, progress=None) -> None:
  116. self.store = Store(database)
  117. self.db = database
  118. self.config = config
  119. self.stats = stats
  120. self.rules = rules
  121. self.progress = progress or (lambda message, fraction: None)
  122. self.output = Output(out)
  123. self.years = range(int(config["start_year"]), int(config["end_year"]) + 1)
  124. last = min(stats["max_month"] or "0000-00", stats["cutoff"][:7])
  125. self.months = [f"{year}-{month:02d}" for year in self.years for month in range(1, 13) if f"{year}-{month:02d}" <= last]
  126. self.port_names: dict[str, str] = {}
  127. self.catalog: list[dict] = []
  128. self._open_template(Path(config["template_file"]))
  129. # ------------------------------------------------------------------ template
  130. def _open_template(self, path: Path) -> None:
  131. template = load_workbook(path, read_only=True, data_only=True)
  132. for sheet in template:
  133. headers = [str(cell or "") for cell in next(sheet.values)]
  134. headers = [HEADER_ALIASES.get(header, header) for header in headers]
  135. headers += [header for header in METRICS + EXTRA if header not in headers]
  136. self.output.sheet(sheet.title, headers)
  137. self.catalog.append({"sheet": sheet.title, "kind": "template", "columns": headers})
  138. template.close()
  139. for name, headers in SUPPLEMENTARY:
  140. self.output.sheet(name, headers)
  141. self.catalog.append({"sheet": name, "kind": "supplementary", "columns": headers})
  142. def state(self, year) -> str:
  143. return "已确认完整年度" if int(year) in self.config.get("confirmed_complete_years", []) else "已提供期间暂算;未确认全年完整"
  144. def emit(self, sheet: str, record: dict) -> None:
  145. headers = self.output.sheets[sheet]["headers"]
  146. record.setdefault("年度数据状态", self.state(record.get("年度") or self.config["end_year"]))
  147. if record.get("选样年度"):
  148. record["选样年度状态"] = self.state(record["选样年度"])
  149. self.output.row(sheet, [record.get(header) for header in headers])
  150. # ------------------------------------------------------------------ groups
  151. def class_groups(self):
  152. """返回 (电容器分组, 存储器分组),每组为 (类目名, 过滤条件, 父级条件, 层级)。"""
  153. caps = [(name, dict(c1="电容器", c2=name), dict(c1="电容器"), 2) for name in dict.fromkeys(self.rules.cap.values())]
  154. memory = [(name, dict(c1="存储器", c2=name), dict(c1="存储器"), 2) for name in ["DRAM", "Flash", "其他存储器"]]
  155. memory += [(name, dict(c1="存储器", c2="DRAM", c3=name), dict(c1="存储器", c2="DRAM"), 3) for name in ["SDRAM", "DDR", "其他DRAM"]]
  156. memory += [(name, dict(c1="存储器", c2="DRAM", c3="DDR", c4=name), dict(c1="存储器", c2="DRAM", c3="DDR"), 4) for name in ["DDR3", "DDR4", "DDR5", "其他DDR"]]
  157. memory += [(name, dict(c1="存储器", c2="Flash", c3=name), dict(c1="存储器", c2="Flash"), 3) for name in ["NAND", "NOR", "其他Flash"]]
  158. return caps, memory
  159. # ------------------------------------------------------------------ baskets
  160. def price_basket(self, name: str, filters: dict, year: int):
  161. """按年度金额选前 100 个型号作为价格指数样本,并计算封顶权重。"""
  162. if self.state(year).startswith("已提供") and not self.config.get("allow_partial_year_basket", True):
  163. return [], "年度未确认完整;配置禁止暂算"
  164. def eligible():
  165. for row in self.store.aggregate(("brand", "model"), dict(filters, year=str(year))):
  166. if not row["brand"] or not row["model"] or row["amount"] is None or row["amount"] <= 0:
  167. self.output.row(
  168. "选样排除",
  169. [filters["direction"], name, year, row["brand"], row["model"], row["amount"], "品牌型号缺失或年度金额缺失/非正;实际值仍纳入"],
  170. )
  171. else:
  172. yield row
  173. # Same Decimal ordering and tie-break as sorting every candidate, O(100) heap.
  174. selected = heapq.nsmallest(100, eligible(), key=lambda row: (-row["amount"], row["brand"], row["model"]))
  175. computed = weights([row["amount"] for row in selected])
  176. basket = []
  177. for rank, (row, weight) in enumerate(zip(selected, computed), 1):
  178. self.output.row(
  179. "样本权重",
  180. [
  181. filters["direction"],
  182. name,
  183. year,
  184. rank,
  185. row["brand"],
  186. row["model"],
  187. row["amount"],
  188. weight,
  189. len(selected),
  190. "少于10个,取消上限" if len(selected) < 10 else "10%封顶再分配",
  191. self.state(year),
  192. ],
  193. )
  194. series = {item["month"]: item for item in self.store.aggregate(("month",), dict(filters, brand=row["brand"], model=row["model"]))}
  195. basket.append((weight, series))
  196. warning = "少于10个样本;取消10%上限" if 0 < len(basket) < 10 else ("无合格样本" if not basket else "")
  197. return basket, warning
  198. # ------------------------------------------------------------------ writers
  199. def monthly(self, sheet: str, name: str, filters: dict, parent: dict | None = None, level: int = 1, weighted: bool = False) -> None:
  200. series = {row["month"]: row for row in self.store.aggregate(("month",), filters)}
  201. parents = {row["month"]: row for row in self.store.aggregate(("month",), parent or filters)}
  202. baskets = {year: self.price_basket(name, filters, year) for year in self.years} if weighted else {}
  203. for month in self.months:
  204. year, value = map(int, month.split("-"))
  205. record = {
  206. "进/出口": filters["direction"],
  207. "年度": year,
  208. "月度": value,
  209. "基础类目": name,
  210. "一级类目": filters.get("c1"),
  211. "二级类目": filters.get("c2"),
  212. "三级类目": filters.get("c3"),
  213. "描述": filters.get("c4"),
  214. "统计层级": level,
  215. }
  216. record.update(
  217. metric(
  218. series.get(month, {}),
  219. series.get(BASE, {"n": 0}),
  220. series.get(previous(month), {"n": 0}),
  221. parents.get(month, {}),
  222. json.dumps(parent or filters, ensure_ascii=False),
  223. )
  224. )
  225. if weighted:
  226. basket, warning = baskets[year]
  227. indices = [
  228. index_of(
  229. ratio(item.get(month, {}).get("amount"), item.get(month, {}).get("quantity")),
  230. ratio(item.get(BASE, {}).get("amount"), item.get(BASE, {}).get("quantity")),
  231. )
  232. for _, item in basket
  233. ]
  234. record["价格指数"] = sum((weight * index for (weight, _), index in zip(basket, indices)), D(0)) if basket and all(index is not None for index in indices) else None
  235. record["计算说明"] = ";".join(
  236. text
  237. for text in [
  238. record["计算说明"],
  239. warning,
  240. "价格样本缺当期或基期,整体留空" if basket and record["价格指数"] is None else "",
  241. ]
  242. if text
  243. )
  244. record["统计范围"] = "全部型号实际值;年度样本加权价格指数"
  245. else:
  246. record["价格指数"] = None
  247. record["统计范围"] = "类目全部记录;加权价格指数见1.0-3.0"
  248. self.emit(sheet, record)
  249. def annual(self, sheet: str, name: str, filters: dict, parent: dict, level: int) -> None:
  250. for year in self.years:
  251. value = self.store.total(dict(filters, year=str(year)))
  252. parent_value = self.store.total(dict(parent, year=str(year)))
  253. record = {
  254. "进/出口": filters["direction"],
  255. "年度": year,
  256. "基础类目": name,
  257. "一级类目": filters.get("c1"),
  258. "二级类目": filters.get("c2"),
  259. "三级类目": filters.get("c3"),
  260. "描述": filters.get("c4"),
  261. "统计层级": level,
  262. "统计范围": "当年已提供期间",
  263. }
  264. record.update(metric(value, parent=parent_value, denominator=json.dumps(parent, ensure_ascii=False)))
  265. self.emit(sheet, record)
  266. def nested(self, scope: str, filters: dict, year: int, sheet: str, monthly_sheet: str) -> None:
  267. """重点产品逐层分析:应用领域(前3) → 品牌国别(前3) → 品牌(前3) → 型号(前10)。"""
  268. dimensions = ["app", "brand_country", "brand", "model"]
  269. labels = ["应用领域标签", "品牌国别", "品牌", "前十大型号"]
  270. root = dict(filters, year=str(year))
  271. selected_paths: list[tuple] = []
  272. def walk(parent: dict, depth: int, path: list) -> None:
  273. dimension = dimensions[depth]
  274. rows = list(self.store.aggregate((dimension,), parent))
  275. if not rows:
  276. return
  277. rankable = all(row["amount"] is not None for row in rows)
  278. ordered = sorted(rows, key=lambda row: (-(row["amount"] or D(0)), row[dimension]))
  279. selected = ordered[: (10 if depth == 3 else 3)] if rankable else []
  280. selected_keys = {row[dimension] for row in selected}
  281. total = self.store.total(parent)
  282. denominator = json.dumps(path, ensure_ascii=False) if path else scope
  283. for row in ordered:
  284. self.output.row(
  285. "重点全量层级",
  286. [
  287. filters["direction"],
  288. year,
  289. scope,
  290. labels[depth],
  291. denominator,
  292. row[dimension] or "未识别",
  293. row[dimension] in selected_keys,
  294. row["amount"],
  295. row["quantity"],
  296. ratio(row["amount"], total["amount"]),
  297. ratio(row["quantity"], total["quantity"]),
  298. denominator,
  299. "" if rankable else "金额含缺失;本分支不排名",
  300. ],
  301. )
  302. groups = [(row[dimension] or "未识别", row, True) for row in selected]
  303. other = [row for row in ordered if row[dimension] not in selected_keys]
  304. if other:
  305. groups.append(
  306. (
  307. "其他(未入选合计)",
  308. dict(
  309. amount=None if any(row["amount"] is None for row in other) else sum(row["amount"] for row in other),
  310. quantity=None if any(row["quantity"] is None for row in other) else sum(row["quantity"] for row in other),
  311. n=sum(row["n"] for row in other),
  312. ),
  313. False,
  314. )
  315. )
  316. for key, row, _chosen in groups:
  317. record = {
  318. "进/出口": filters["direction"],
  319. "年度": year,
  320. "一级类目": filters["c1"],
  321. "二级类目": filters.get("c2"),
  322. "三级类目": filters.get("c3"),
  323. **{labels[index]: path[index] for index in range(len(path))},
  324. labels[depth]: key,
  325. "统计层级": labels[depth],
  326. }
  327. record.update(metric(row, parent=total, denominator=denominator))
  328. self.emit(sheet, record)
  329. for row in selected:
  330. if depth < 3:
  331. walk(dict(parent, **{dimension: row[dimension]}), depth + 1, path + [row[dimension]])
  332. elif row["model"] and parent.get("brand"):
  333. selected_paths.append((parent["app"], parent["brand_country"], parent["brand"], row["model"]))
  334. walk(root, 0, [])
  335. for app, country, brand, model in selected_paths:
  336. for label, monthly_filter in [
  337. ("项目范围全部进口", dict(direction=filters["direction"], brand=brand, model=model)),
  338. ("限定入选路径", dict(filters, app=app, brand_country=country, brand=brand, model=model)),
  339. ]:
  340. series = {row["month"]: row for row in self.store.aggregate(("month",), monthly_filter)}
  341. for month in self.months:
  342. year_value, month_value = map(int, month.split("-"))
  343. record = {
  344. "进/出口": filters["direction"],
  345. "年度": year_value,
  346. "月度": month_value,
  347. "品牌": brand,
  348. "型号": model,
  349. "选样年度": year,
  350. "统计范围": label,
  351. "应用领域标签": app,
  352. "品牌国别": country,
  353. "一级类目": filters["c1"],
  354. "二级类目": filters.get("c2"),
  355. "三级类目": filters.get("c3"),
  356. }
  357. record.update(
  358. metric(
  359. series.get(month, {}),
  360. series.get(BASE, {"n": 0}),
  361. series.get(previous(month), {"n": 0}),
  362. )
  363. )
  364. self.emit(monthly_sheet, record)
  365. def supply(self, scope: str, filters: dict, year: int, sheet: str) -> None:
  366. """供应链关联统计:完整联合路径 + 品牌国别条件占比。"""
  367. root = dict(filters, year=str(year))
  368. total = self.store.total(root)
  369. for port_dimension, granularity in [("port", "具体港口"), ("port_country", "港口国家地区")]:
  370. dimensions = ["brand_country", "origin", "trade_country", "dispatch", port_dimension, "app"]
  371. for row in self.store.aggregate(dimensions, root):
  372. record = {
  373. "进/出口": filters["direction"],
  374. "年度": year,
  375. "一级类目": filters["c1"],
  376. "二级类目": scope,
  377. "品牌国别": row["brand_country"],
  378. "原产国": row["origin"],
  379. "贸易国": row["trade_country"],
  380. "启运国": row["dispatch"],
  381. "经停港/指运港": row[port_dimension],
  382. "应用领域标签": row["app"],
  383. "港口统计粒度": granularity,
  384. "港口所属国家地区": row.get("port_country"),
  385. "统计范围": "完整联合路径",
  386. }
  387. if port_dimension == "port":
  388. record["港口所属国家地区"] = self.port_names.get(row["port"], "未识别")
  389. record.update(metric(row, parent=total, denominator=f'{scope}当年全部{filters["direction"]}'))
  390. self.emit(sheet, record)
  391. for depth in range(len(dimensions)):
  392. group = dimensions[: depth + 1]
  393. parents = {tuple(row[key] for key in group[:-1]): row for row in self.store.aggregate(tuple(group[:-1]), root)}
  394. for row in self.store.aggregate(tuple(group), root):
  395. key = tuple(row[item] for item in group[:-1])
  396. parent = parents[key]
  397. path = json.dumps(dict(zip(group[:-1], key)), ensure_ascii=False)
  398. self.output.row(
  399. "供应链条件占比",
  400. [
  401. filters["direction"],
  402. year,
  403. scope,
  404. granularity,
  405. "完整前缀条件",
  406. group[-1],
  407. path,
  408. row[group[-1]],
  409. row["amount"],
  410. row["quantity"],
  411. ratio(row["amount"], parent["amount"]),
  412. ratio(row["quantity"], parent["quantity"]),
  413. path or scope,
  414. ],
  415. )
  416. # 原始 Word 的示例用“品牌国别”作为每个属性的分母。
  417. for dimension in dimensions[1:]:
  418. parents = {row["brand_country"]: row for row in self.store.aggregate(("brand_country",), root)}
  419. for row in self.store.aggregate(("brand_country", dimension), root):
  420. parent = parents[row["brand_country"]]
  421. self.output.row(
  422. "供应链条件占比",
  423. [
  424. filters["direction"],
  425. year,
  426. scope,
  427. granularity,
  428. "品牌国别条件",
  429. dimension,
  430. row["brand_country"],
  431. row[dimension],
  432. row["amount"],
  433. row["quantity"],
  434. ratio(row["amount"], parent["amount"]),
  435. ratio(row["quantity"], parent["quantity"]),
  436. "同年同专项同品牌国别全部记录",
  437. ],
  438. )
  439. # ------------------------------------------------------------------ driver
  440. def run(self) -> Path:
  441. self.port_names = {name: country for name, country in self.rules.ports.values()}
  442. caps, memory = self.class_groups()
  443. directions = self.config["directions"]
  444. step = 0
  445. total_steps = max(len(directions), 1) * 14
  446. for direction in directions:
  447. for product, sheet in [("电容器", "4.1_MLCC"), ("存储器", "5.1_存储器")]:
  448. step += 1
  449. self.progress(f"生成 {product} 月度总览({direction})", 0.62 + 0.18 * step / total_steps)
  450. self.monthly(sheet, product, dict(direction=direction, c1=product))
  451. for groups, index_sheet, annual_sheet in [(caps, "1.0-3.0_MLCC", "4.2.1_MLCC"), (memory, "1.0-3.0_存储器", "5.2.1&3&5_存储器")]:
  452. for name, filters, parent, level in groups:
  453. step += 1
  454. self.progress(f"生成 {name} 指数与结构表({direction})", 0.62 + 0.18 * step / total_steps)
  455. filters, parent = dict(filters, direction=direction), dict(parent, direction=direction)
  456. self.monthly(index_sheet, name, filters, parent, level, True)
  457. self.annual(annual_sheet, name, filters, parent, level)
  458. sheet = "4.2.2_MLCC" if filters["c1"] == "电容器" else {2: "5.2.2_存储器", 3: "5.2.4_存储器", 4: "5.2.6_存储器"}[level]
  459. self.monthly(sheet, name, filters, parent, level)
  460. mlcc = next((value for value in self.rules.cap.values() if "MLCC" in value), None)
  461. for scope, filters, top_sheet, monthly_sheet, supply_sheet in [
  462. ("MLCC", dict(direction=direction, c1="电容器", c2=mlcc), "4.2.3 (1-4)_MLCC", "4.2.3 (5)_MLCC", "4.3_MLCC"),
  463. ("DDR", dict(direction=direction, c1="存储器", c2="DRAM", c3="DDR", mapped="1"), "5.2.7 (1-4)_存储器", "5.2.7 (5)_存储器", "5.3_存储器"),
  464. ]:
  465. for year in self.years:
  466. self.progress(f"生成 {year} 年 {scope} 重点分析与供应链", 0.8)
  467. self.nested(scope, filters, year, top_sheet, monthly_sheet)
  468. self.supply(scope, filters, year, supply_sheet)
  469. self._quality_sheets()
  470. self.progress("写出 Excel 与逐表 CSV…", 0.97)
  471. path = self.output.save()
  472. self.output.close()
  473. return path
  474. def _quality_sheets(self) -> None:
  475. for row in week_rows(int(self.config["start_year"]), int(self.config["end_year"])):
  476. self.output.row("周四归月映射", row)
  477. for row in self.rules.conflicts:
  478. self.output.row("映射冲突", row)
  479. for key, value in self.stats.items():
  480. if isinstance(value, dict):
  481. for detail, count in value.items():
  482. self.output.row("数据质量摘要", [f"{key}.{detail}", count, "已纳入记录的识别情况" if key == "recognition" else "排除原因"])
  483. else:
  484. if isinstance(value, list):
  485. value = json.dumps(value, ensure_ascii=False)
  486. self.output.row("数据质量摘要", [key, value, "模拟数据" if self.config.get("demo") else "单文件统计"])
  487. for key, value, description in [
  488. ("币种", "USD", "按输入美元;不换汇不缩放"),
  489. ("数量单位", "成交数量", "待现场核对成交单位"),
  490. ("日期口径", "ISO周四归月", "不拆周,不按月内周数平均"),
  491. ("基期", BASE, "1000点;永不替换或链式衔接"),
  492. ("年度完整性", str(self.config.get("confirmed_complete_years", [])), "仅列表中的年份确认为全年完整"),
  493. ("精度", "Decimal 50有效位", "不额外舍入;精确结果CSV保留计算文本"),
  494. ("输入完整性", "来源无记录无法证明零贸易", "无记录月份留空"),
  495. ("国家代码制", self.config["country_code_system"], "ISO默认;GEC需显式配置"),
  496. ]:
  497. self.output.row("数据质量摘要", [key, value, description])
  498. for field, reason, count in self.db.execute("SELECT field,reason,count(*) FROM issues GROUP BY field,reason"):
  499. self.output.row("数据质量摘要", [field, count, reason])
  500. self._export_detail_csv()
  501. def _export_detail_csv(self) -> None:
  502. """把明细表导出为 CSV(不塞进 Excel,避免几百万行撑爆结果表)。"""
  503. out = self.output.out
  504. for table in ["issues", "audit", "raw", "monthly"]:
  505. with (out / f"{table}.csv").open("w", encoding="utf-8-sig", newline="") as handle:
  506. writer = csv.writer(handle)
  507. cursor = self.db.execute(f"SELECT * FROM {table}")
  508. writer.writerow([description[0] for description in cursor.description])
  509. writer.writerows(cursor)
  510. fields = ["row_id", "hs", "raw_model", "brand_raw", "model_raw", "brand", "model", "app_raw", "app1", "app", "c1", "c2", "c3", "c4", "description", "mapped", "method", "flags"]
  511. handles = {}
  512. try:
  513. for name in ["解析明细", "应用二轮变化", "未识别及提取异常"]:
  514. handle = (out / f"{name}.csv").open("w", encoding="utf-8-sig", newline="")
  515. handles[name] = (handle, csv.writer(handle))
  516. handles[name][1].writerow(fields)
  517. for row_id, hs, raw_model, parsed_json in self.db.execute("SELECT * FROM audit"):
  518. parsed = json.loads(parsed_json)
  519. parsed.update(row_id=row_id, hs=hs, raw_model=raw_model)
  520. values = [parsed.get(field) for field in fields]
  521. handles["解析明细"][1].writerow(values)
  522. if parsed["app1"] != parsed["app"]:
  523. handles["应用二轮变化"][1].writerow(values)
  524. if parsed["flags"]:
  525. handles["未识别及提取异常"][1].writerow(values)
  526. finally:
  527. for handle, _writer in handles.values():
  528. handle.close()