"""按《统计类数据输出格式.xlsx》生成全部统计表。 本模块只负责“把聚合结果写进正确的表、正确的行”,所有口径都写在字段里: * ``实际金额(美元)``/``成交数量`` 是原始真值; * ``金额指数``/``数量指数``/``价格指数`` 是以 2025-01 为 1000 的固定基期指数; * ``金额占比``/``数量占比`` 的分母写在 ``占比分母范围`` 列,绝不跨层级求和; * ``金额环比``/``数量环比`` 是与上月比,不是与基期比; * 无记录/缺数/分母为零一律留空,并把原因写进 ``计算说明``。 """ from __future__ import annotations import csv import json import heapq import sqlite3 from decimal import Decimal from pathlib import Path from openpyxl import load_workbook from .excel import Output from .numbers import D, growth, index_of, ratio, weights from .storage import Store, week_rows #: 固定基期 BASE = "2025-01" #: 指标列(顺序即输出顺序) METRICS = [ "实际金额(美元)", "成交数量", "平均单价(美元/成交数量单位)", "金额指数", "数量指数", "价格指数", "金额占比", "数量占比", "金额环比", "数量环比", "占比分母范围", "记录数", "计算说明", "年度数据状态", ] #: 辅助维度列(模板没有的会追加) EXTRA = [ "统计层级", "统计范围", "选样年度", "选样年度状态", "应用领域标签", "品牌国别", "品牌", "前十大型号", "港口所属国家地区", "港口统计粒度", ] HEADER_ALIASES = { "前五大型号": "前十大型号", "进口额": "实际金额(美元)", "进口量": "成交数量", "价格": "平均单价(美元/成交数量单位)", } SUPPLEMENTARY = [ ("样本权重", ["进/出口", "基础类目", "选样年度", "排名", "品牌", "型号", "年度金额(美元)", "权重", "样本数量", "权重规则", "年度数据状态"]), ("选样排除", ["进/出口", "基础类目", "选样年度", "品牌", "型号", "年度金额(美元)", "原因"]), ("重点全量层级", ["进/出口", "年度", "专项", "层级", "父级路径", "名称", "入选", "实际金额(美元)", "成交数量", "金额占比", "数量占比", "占比分母范围", "计算说明"]), ("供应链条件占比", ["进/出口", "年度", "专项", "港口统计粒度", "分析方式", "层级", "父级路径", "节点", "实际金额(美元)", "成交数量", "金额占比", "数量占比", "占比分母范围"]), ("周四归月映射", ["ISO年", "ISO周", "周一日期", "周日日期", "周四日期", "归属年", "归属月", "规则"]), ("数据质量摘要", ["项目", "值", "说明"]), ("映射冲突", ["类型", "匹配键", "冲突内容"]), ] def previous(month: str) -> str: """上一个自然月。""" year, value = map(int, month.split("-")) return f"{year - 1}-12" if value == 1 else f"{year}-{value - 1:02d}" def metric(value: dict, base: dict | None = None, prev: dict | None = None, parent: dict | None = None, denominator: str = "") -> dict: """构造一行指标:真值、指数、占比、环比、原因说明。""" base, prev, parent = base or {}, prev or {}, parent or {} amount, quantity = value.get("amount"), value.get("quantity") price = ratio(amount, quantity) result = dict( zip( METRICS[:11], [ amount, quantity, price, index_of(amount, base.get("amount")), index_of(quantity, base.get("quantity")), index_of(price, ratio(base.get("amount"), base.get("quantity"))), ratio(amount, parent.get("amount")), ratio(quantity, parent.get("quantity")), growth(amount, prev.get("amount")), growth(quantity, prev.get("quantity")), denominator, ], ) ) result["记录数"] = value.get("n", 0) reasons = [] if not value.get("n"): reasons.append("无记录,未按零填补") elif amount is None or quantity is None: reasons.append("金额或数量含无法识别值") if price is None: reasons.append("平均单价缺数或数量为零") if base: if result["金额指数"] is None: reasons.append("金额指数缺数或基期金额为零") if result["数量指数"] is None: reasons.append("数量指数缺数或基期数量为零") if result["价格指数"] is None: reasons.append("价格指数缺数或基期价格为零") if prev and (result["金额环比"] is None or result["数量环比"] is None): reasons.append("环比缺上月数或上月分母为零") result["计算说明"] = ";".join(reasons) return result class Reports: """一次完整运行的报表生成器。""" def __init__(self, database: sqlite3.Connection, config: dict, stats: dict, rules, out: Path, progress=None) -> None: self.store = Store(database) self.db = database self.config = config self.stats = stats self.rules = rules self.progress = progress or (lambda message, fraction: None) self.output = Output(out) self.years = range(int(config["start_year"]), int(config["end_year"]) + 1) last = min(stats["max_month"] or "0000-00", stats["cutoff"][:7]) self.months = [f"{year}-{month:02d}" for year in self.years for month in range(1, 13) if f"{year}-{month:02d}" <= last] self.port_names: dict[str, str] = {} self.catalog: list[dict] = [] self._open_template(Path(config["template_file"])) # ------------------------------------------------------------------ template def _open_template(self, path: Path) -> None: template = load_workbook(path, read_only=True, data_only=True) for sheet in template: headers = [str(cell or "") for cell in next(sheet.values)] headers = [HEADER_ALIASES.get(header, header) for header in headers] headers += [header for header in METRICS + EXTRA if header not in headers] self.output.sheet(sheet.title, headers) self.catalog.append({"sheet": sheet.title, "kind": "template", "columns": headers}) template.close() for name, headers in SUPPLEMENTARY: self.output.sheet(name, headers) self.catalog.append({"sheet": name, "kind": "supplementary", "columns": headers}) def state(self, year) -> str: return "已确认完整年度" if int(year) in self.config.get("confirmed_complete_years", []) else "已提供期间暂算;未确认全年完整" def emit(self, sheet: str, record: dict) -> None: headers = self.output.sheets[sheet]["headers"] record.setdefault("年度数据状态", self.state(record.get("年度") or self.config["end_year"])) if record.get("选样年度"): record["选样年度状态"] = self.state(record["选样年度"]) self.output.row(sheet, [record.get(header) for header in headers]) # ------------------------------------------------------------------ groups def class_groups(self): """返回 (电容器分组, 存储器分组),每组为 (类目名, 过滤条件, 父级条件, 层级)。""" caps = [(name, dict(c1="电容器", c2=name), dict(c1="电容器"), 2) for name in dict.fromkeys(self.rules.cap.values())] memory = [(name, dict(c1="存储器", c2=name), dict(c1="存储器"), 2) for name in ["DRAM", "Flash", "其他存储器"]] memory += [(name, dict(c1="存储器", c2="DRAM", c3=name), dict(c1="存储器", c2="DRAM"), 3) for name in ["SDRAM", "DDR", "其他DRAM"]] memory += [(name, dict(c1="存储器", c2="DRAM", c3="DDR", c4=name), dict(c1="存储器", c2="DRAM", c3="DDR"), 4) for name in ["DDR3", "DDR4", "DDR5", "其他DDR"]] memory += [(name, dict(c1="存储器", c2="Flash", c3=name), dict(c1="存储器", c2="Flash"), 3) for name in ["NAND", "NOR", "其他Flash"]] return caps, memory # ------------------------------------------------------------------ baskets def price_basket(self, name: str, filters: dict, year: int): """按年度金额选前 100 个型号作为价格指数样本,并计算封顶权重。""" if self.state(year).startswith("已提供") and not self.config.get("allow_partial_year_basket", True): return [], "年度未确认完整;配置禁止暂算" def eligible(): for row in self.store.aggregate(("brand", "model"), dict(filters, year=str(year))): if not row["brand"] or not row["model"] or row["amount"] is None or row["amount"] <= 0: self.output.row( "选样排除", [filters["direction"], name, year, row["brand"], row["model"], row["amount"], "品牌型号缺失或年度金额缺失/非正;实际值仍纳入"], ) else: yield row # Same Decimal ordering and tie-break as sorting every candidate, O(100) heap. selected = heapq.nsmallest(100, eligible(), key=lambda row: (-row["amount"], row["brand"], row["model"])) computed = weights([row["amount"] for row in selected]) basket = [] for rank, (row, weight) in enumerate(zip(selected, computed), 1): self.output.row( "样本权重", [ filters["direction"], name, year, rank, row["brand"], row["model"], row["amount"], weight, len(selected), "少于10个,取消上限" if len(selected) < 10 else "10%封顶再分配", self.state(year), ], ) series = {item["month"]: item for item in self.store.aggregate(("month",), dict(filters, brand=row["brand"], model=row["model"]))} basket.append((weight, series)) warning = "少于10个样本;取消10%上限" if 0 < len(basket) < 10 else ("无合格样本" if not basket else "") return basket, warning # ------------------------------------------------------------------ writers def monthly(self, sheet: str, name: str, filters: dict, parent: dict | None = None, level: int = 1, weighted: bool = False) -> None: series = {row["month"]: row for row in self.store.aggregate(("month",), filters)} parents = {row["month"]: row for row in self.store.aggregate(("month",), parent or filters)} baskets = {year: self.price_basket(name, filters, year) for year in self.years} if weighted else {} for month in self.months: year, value = map(int, month.split("-")) record = { "进/出口": filters["direction"], "年度": year, "月度": value, "基础类目": name, "一级类目": filters.get("c1"), "二级类目": filters.get("c2"), "三级类目": filters.get("c3"), "描述": filters.get("c4"), "统计层级": level, } record.update( metric( series.get(month, {}), series.get(BASE, {"n": 0}), series.get(previous(month), {"n": 0}), parents.get(month, {}), json.dumps(parent or filters, ensure_ascii=False), ) ) if weighted: basket, warning = baskets[year] indices = [ index_of( ratio(item.get(month, {}).get("amount"), item.get(month, {}).get("quantity")), ratio(item.get(BASE, {}).get("amount"), item.get(BASE, {}).get("quantity")), ) for _, item in basket ] 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 record["计算说明"] = ";".join( text for text in [ record["计算说明"], warning, "价格样本缺当期或基期,整体留空" if basket and record["价格指数"] is None else "", ] if text ) record["统计范围"] = "全部型号实际值;年度样本加权价格指数" else: record["价格指数"] = None record["统计范围"] = "类目全部记录;加权价格指数见1.0-3.0" self.emit(sheet, record) def annual(self, sheet: str, name: str, filters: dict, parent: dict, level: int) -> None: for year in self.years: value = self.store.total(dict(filters, year=str(year))) parent_value = self.store.total(dict(parent, year=str(year))) record = { "进/出口": filters["direction"], "年度": year, "基础类目": name, "一级类目": filters.get("c1"), "二级类目": filters.get("c2"), "三级类目": filters.get("c3"), "描述": filters.get("c4"), "统计层级": level, "统计范围": "当年已提供期间", } record.update(metric(value, parent=parent_value, denominator=json.dumps(parent, ensure_ascii=False))) self.emit(sheet, record) def nested(self, scope: str, filters: dict, year: int, sheet: str, monthly_sheet: str) -> None: """重点产品逐层分析:应用领域(前3) → 品牌国别(前3) → 品牌(前3) → 型号(前10)。""" dimensions = ["app", "brand_country", "brand", "model"] labels = ["应用领域标签", "品牌国别", "品牌", "前十大型号"] root = dict(filters, year=str(year)) selected_paths: list[tuple] = [] def walk(parent: dict, depth: int, path: list) -> None: dimension = dimensions[depth] rows = list(self.store.aggregate((dimension,), parent)) if not rows: return rankable = all(row["amount"] is not None for row in rows) ordered = sorted(rows, key=lambda row: (-(row["amount"] or D(0)), row[dimension])) selected = ordered[: (10 if depth == 3 else 3)] if rankable else [] selected_keys = {row[dimension] for row in selected} total = self.store.total(parent) denominator = json.dumps(path, ensure_ascii=False) if path else scope for row in ordered: self.output.row( "重点全量层级", [ filters["direction"], year, scope, labels[depth], denominator, row[dimension] or "未识别", row[dimension] in selected_keys, row["amount"], row["quantity"], ratio(row["amount"], total["amount"]), ratio(row["quantity"], total["quantity"]), denominator, "" if rankable else "金额含缺失;本分支不排名", ], ) groups = [(row[dimension] or "未识别", row, True) for row in selected] other = [row for row in ordered if row[dimension] not in selected_keys] if other: groups.append( ( "其他(未入选合计)", dict( amount=None if any(row["amount"] is None for row in other) else sum(row["amount"] for row in other), quantity=None if any(row["quantity"] is None for row in other) else sum(row["quantity"] for row in other), n=sum(row["n"] for row in other), ), False, ) ) for key, row, _chosen in groups: record = { "进/出口": filters["direction"], "年度": year, "一级类目": filters["c1"], "二级类目": filters.get("c2"), "三级类目": filters.get("c3"), **{labels[index]: path[index] for index in range(len(path))}, labels[depth]: key, "统计层级": labels[depth], } record.update(metric(row, parent=total, denominator=denominator)) self.emit(sheet, record) for row in selected: if depth < 3: walk(dict(parent, **{dimension: row[dimension]}), depth + 1, path + [row[dimension]]) elif row["model"] and parent.get("brand"): selected_paths.append((parent["app"], parent["brand_country"], parent["brand"], row["model"])) walk(root, 0, []) for app, country, brand, model in selected_paths: for label, monthly_filter in [ ("项目范围全部进口", dict(direction=filters["direction"], brand=brand, model=model)), ("限定入选路径", dict(filters, app=app, brand_country=country, brand=brand, model=model)), ]: series = {row["month"]: row for row in self.store.aggregate(("month",), monthly_filter)} for month in self.months: year_value, month_value = map(int, month.split("-")) record = { "进/出口": filters["direction"], "年度": year_value, "月度": month_value, "品牌": brand, "型号": model, "选样年度": year, "统计范围": label, "应用领域标签": app, "品牌国别": country, "一级类目": filters["c1"], "二级类目": filters.get("c2"), "三级类目": filters.get("c3"), } record.update( metric( series.get(month, {}), series.get(BASE, {"n": 0}), series.get(previous(month), {"n": 0}), ) ) self.emit(monthly_sheet, record) def supply(self, scope: str, filters: dict, year: int, sheet: str) -> None: """供应链关联统计:完整联合路径 + 品牌国别条件占比。""" root = dict(filters, year=str(year)) total = self.store.total(root) for port_dimension, granularity in [("port", "具体港口"), ("port_country", "港口国家地区")]: dimensions = ["brand_country", "origin", "trade_country", "dispatch", port_dimension, "app"] for row in self.store.aggregate(dimensions, root): record = { "进/出口": filters["direction"], "年度": year, "一级类目": filters["c1"], "二级类目": scope, "品牌国别": row["brand_country"], "原产国": row["origin"], "贸易国": row["trade_country"], "启运国": row["dispatch"], "经停港/指运港": row[port_dimension], "应用领域标签": row["app"], "港口统计粒度": granularity, "港口所属国家地区": row.get("port_country"), "统计范围": "完整联合路径", } if port_dimension == "port": record["港口所属国家地区"] = self.port_names.get(row["port"], "未识别") record.update(metric(row, parent=total, denominator=f'{scope}当年全部{filters["direction"]}')) self.emit(sheet, record) for depth in range(len(dimensions)): group = dimensions[: depth + 1] parents = {tuple(row[key] for key in group[:-1]): row for row in self.store.aggregate(tuple(group[:-1]), root)} for row in self.store.aggregate(tuple(group), root): key = tuple(row[item] for item in group[:-1]) parent = parents[key] path = json.dumps(dict(zip(group[:-1], key)), ensure_ascii=False) self.output.row( "供应链条件占比", [ filters["direction"], year, scope, granularity, "完整前缀条件", group[-1], path, row[group[-1]], row["amount"], row["quantity"], ratio(row["amount"], parent["amount"]), ratio(row["quantity"], parent["quantity"]), path or scope, ], ) # 原始 Word 的示例用“品牌国别”作为每个属性的分母。 for dimension in dimensions[1:]: parents = {row["brand_country"]: row for row in self.store.aggregate(("brand_country",), root)} for row in self.store.aggregate(("brand_country", dimension), root): parent = parents[row["brand_country"]] self.output.row( "供应链条件占比", [ filters["direction"], year, scope, granularity, "品牌国别条件", dimension, row["brand_country"], row[dimension], row["amount"], row["quantity"], ratio(row["amount"], parent["amount"]), ratio(row["quantity"], parent["quantity"]), "同年同专项同品牌国别全部记录", ], ) # ------------------------------------------------------------------ driver def run(self) -> Path: self.port_names = {name: country for name, country in self.rules.ports.values()} caps, memory = self.class_groups() directions = self.config["directions"] step = 0 total_steps = max(len(directions), 1) * 14 for direction in directions: for product, sheet in [("电容器", "4.1_MLCC"), ("存储器", "5.1_存储器")]: step += 1 self.progress(f"生成 {product} 月度总览({direction})", 0.62 + 0.18 * step / total_steps) self.monthly(sheet, product, dict(direction=direction, c1=product)) 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_存储器")]: for name, filters, parent, level in groups: step += 1 self.progress(f"生成 {name} 指数与结构表({direction})", 0.62 + 0.18 * step / total_steps) filters, parent = dict(filters, direction=direction), dict(parent, direction=direction) self.monthly(index_sheet, name, filters, parent, level, True) self.annual(annual_sheet, name, filters, parent, level) sheet = "4.2.2_MLCC" if filters["c1"] == "电容器" else {2: "5.2.2_存储器", 3: "5.2.4_存储器", 4: "5.2.6_存储器"}[level] self.monthly(sheet, name, filters, parent, level) mlcc = next((value for value in self.rules.cap.values() if "MLCC" in value), None) for scope, filters, top_sheet, monthly_sheet, supply_sheet in [ ("MLCC", dict(direction=direction, c1="电容器", c2=mlcc), "4.2.3 (1-4)_MLCC", "4.2.3 (5)_MLCC", "4.3_MLCC"), ("DDR", dict(direction=direction, c1="存储器", c2="DRAM", c3="DDR", mapped="1"), "5.2.7 (1-4)_存储器", "5.2.7 (5)_存储器", "5.3_存储器"), ]: for year in self.years: self.progress(f"生成 {year} 年 {scope} 重点分析与供应链", 0.8) self.nested(scope, filters, year, top_sheet, monthly_sheet) self.supply(scope, filters, year, supply_sheet) self._quality_sheets() self.progress("写出 Excel 与逐表 CSV…", 0.97) path = self.output.save() self.output.close() return path def _quality_sheets(self) -> None: for row in week_rows(int(self.config["start_year"]), int(self.config["end_year"])): self.output.row("周四归月映射", row) for row in self.rules.conflicts: self.output.row("映射冲突", row) for key, value in self.stats.items(): if isinstance(value, dict): for detail, count in value.items(): self.output.row("数据质量摘要", [f"{key}.{detail}", count, "已纳入记录的识别情况" if key == "recognition" else "排除原因"]) else: if isinstance(value, list): value = json.dumps(value, ensure_ascii=False) self.output.row("数据质量摘要", [key, value, "模拟数据" if self.config.get("demo") else "单文件统计"]) for key, value, description in [ ("币种", "USD", "按输入美元;不换汇不缩放"), ("数量单位", "成交数量", "待现场核对成交单位"), ("日期口径", "ISO周四归月", "不拆周,不按月内周数平均"), ("基期", BASE, "1000点;永不替换或链式衔接"), ("年度完整性", str(self.config.get("confirmed_complete_years", [])), "仅列表中的年份确认为全年完整"), ("精度", "Decimal 50有效位", "不额外舍入;精确结果CSV保留计算文本"), ("输入完整性", "来源无记录无法证明零贸易", "无记录月份留空"), ("国家代码制", self.config["country_code_system"], "ISO默认;GEC需显式配置"), ]: self.output.row("数据质量摘要", [key, value, description]) for field, reason, count in self.db.execute("SELECT field,reason,count(*) FROM issues GROUP BY field,reason"): self.output.row("数据质量摘要", [field, count, reason]) self._export_detail_csv() def _export_detail_csv(self) -> None: """把明细表导出为 CSV(不塞进 Excel,避免几百万行撑爆结果表)。""" out = self.output.out for table in ["issues", "audit", "raw", "monthly"]: with (out / f"{table}.csv").open("w", encoding="utf-8-sig", newline="") as handle: writer = csv.writer(handle) cursor = self.db.execute(f"SELECT * FROM {table}") writer.writerow([description[0] for description in cursor.description]) writer.writerows(cursor) fields = ["row_id", "hs", "raw_model", "brand_raw", "model_raw", "brand", "model", "app_raw", "app1", "app", "c1", "c2", "c3", "c4", "description", "mapped", "method", "flags"] handles = {} try: for name in ["解析明细", "应用二轮变化", "未识别及提取异常"]: handle = (out / f"{name}.csv").open("w", encoding="utf-8-sig", newline="") handles[name] = (handle, csv.writer(handle)) handles[name][1].writerow(fields) for row_id, hs, raw_model, parsed_json in self.db.execute("SELECT * FROM audit"): parsed = json.loads(parsed_json) parsed.update(row_id=row_id, hs=hs, raw_model=raw_model) values = [parsed.get(field) for field in fields] handles["解析明细"][1].writerow(values) if parsed["app1"] != parsed["app"]: handles["应用二轮变化"][1].writerow(values) if parsed["flags"]: handles["未识别及提取异常"][1].writerow(values) finally: for handle, _writer in handles.values(): handle.close()