# -*- coding: utf-8 -*- """Export S0-S2 quote vs requirement/blueprint comparison to Excel.""" from __future__ import annotations from pathlib import Path import openpyxl from docx import Document from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter QUOTE = Path( r"C:\Users\skygu\OneDrive\Projects\AIDOP\项目\项目报价" r"\AI-DOP数字化运营平台开发报价清单_工时内容补充_增加算法_20260630.xlsx" ) DELIVERY = Path( r"C:\Users\skygu\OneDrive\Projects\AIDOP\项目\项目管理\产互联项目管理\交付文档" ) OUT_XLSX = Path(__file__).resolve().parent / "plan" / "S0-S2-报价与交付文档对比表.xlsx" DOCS = { "S0": ( DELIVERY / "S0" / "S0-基础主数据-业务需求描述-V0.2.docx", DELIVERY / "S0" / "S0-基础主数据建模-业务蓝图设计方案-V0.2.docx", ), "S1": ( DELIVERY / "S1" / "S1产销协同业务需求描述.docx", DELIVERY / "S1" / "S1产销协同模块蓝图设计方案.docx", ), "S2": ( DELIVERY / "S2" / "S2制造协同业务需求描述.docx", DELIVERY / "S2" / "S2制造协同模块蓝图设计方案.docx", ), } MOD_QUOTE_NAME = {"S0": "运营建模", "S1": "产销协同", "S2": "制造协同"} HEADERS = [ "模块", "报价需求模块", "报价需求项", "需求项描述", "需求文档", "蓝图文档", "说明", ] def load_quote_corpus() -> dict: """Load verbatim quote text from 汇总 / 明细 / 算法清单.""" wb = openpyxl.load_workbook(QUOTE, data_only=True) corpus: dict = {"summary": {}, "detail": {}, "algo": {}, "kanban": {}} ws = wb["报价汇总-.NET方案"] for mod, name in MOD_QUOTE_NAME.items(): for r in range(1, ws.max_row + 1): if ws.cell(r, 3).value == name: corpus["summary"][mod] = { "front": str(ws.cell(r, 4).value or ""), "front_days": ws.cell(r, 5).value, "back": str(ws.cell(r + 1, 4).value or ""), "db": str(ws.cell(r + 2, 4).value or ""), } break ws = wb["报价明细-.NET方案"] blueprint_map = { "S1": "产销协同蓝图设计", "S2": "制造协同蓝图设计", } for mod, label in blueprint_map.items(): for r in range(1, ws.max_row + 1): if ws.cell(r, 2).value == label: corpus["detail"][f"{mod}_blueprint"] = ( f"项目准备及业务蓝图设计 / {label}：" f"开发人数 {ws.cell(r, 4).value}，" f"开发天数 {ws.cell(r, 5).value}，" f"预估人天 {ws.cell(r, 6).value}" ) break detail_blocks = { "S0": "运营建模", "S1": "产销协同", "S2": "制造协同", } for mod, block in detail_blocks.items(): rows = [] in_block = False for r in range(1, ws.max_row + 1): c2, c3 = ws.cell(r, 2).value, ws.cell(r, 3).value if c2 == block: in_block = True if in_block and c2 and c2 != block and c2 in detail_blocks.values() and c2 != block: break if in_block and c3 and c3 != "单项小计": rows.append( f"功能开发和业务实现 / {block} / {c3}：" f"开发人数 {ws.cell(r, 4).value}，" f"开发天数 {ws.cell(r, 5).value}，" f"预估人天 {ws.cell(r, 6).value}" ) corpus["detail"][mod] = rows for r in range(1, ws.max_row + 1): if ws.cell(r, 2).value == "九宫格智慧运营看板": mod_name = ws.cell(r, 3).value for mod, qn in MOD_QUOTE_NAME.items(): if qn == mod_name: corpus["kanban"][mod] = ( f"九宫格智慧运营看板 / {mod_name}：" f"开发人数 {ws.cell(r, 4).value}，" f"开发天数 {ws.cell(r, 5).value}，" f"预估人天 {ws.cell(r, 6).value}" ) ws = wb["算法清单"] for r in range(3, ws.max_row + 1): mod_name = ws.cell(r, 2).value algo_text = str(ws.cell(r, 3).value or "") for mod, qn in MOD_QUOTE_NAME.items(): if qn == mod_name: corpus["algo"][mod] = algo_text wb.close() return corpus def load_doc_text(path: Path) -> str: doc = Document(path) parts = [p.text for p in doc.paragraphs] for table in doc.tables: for row in table.rows: for cell in row.cells: parts.append(cell.text) return "\n".join(parts) def match_status(text: str, keywords: list[str], *, required: list[str] | None = None) -> str: if not keywords: return "—" hits = [k for k in keywords if k in text] if required and all(r in text for r in required): return "✅" if required and any(r in text for r in required): return "⚠️" if len(hits) == len(keywords): return "✅" if hits: return "⚠️" return "❌" def split_quote_segments(line: str, sep: str) -> list[str]: if "：" in line: line = line.split("：", 1)[1] return [s.strip() for s in line.split(sep) if s.strip()] def find_segment(full: str, keyword: str, sep: str = "、") -> str: for seg in split_quote_segments(full, sep): if keyword in seg or seg.startswith(keyword): return seg return full def make_row( module: str, quote_module: str, quote_item: str, item_desc: str, req_doc: str, blue_doc: str, note: str, ) -> dict: if ( "🔴" not in note and req_doc not in ("—", "✅") and (req_doc == "❌" or blue_doc == "❌") ): note = f"🔴 报价已列此项，交付文档未覆盖或未写全。{note}" if note else "🔴 报价已列此项，交付文档未覆盖或未写全。" return { "模块": module, "报价需求模块": quote_module, "报价需求项": quote_item, "需求项描述": item_desc, "需求文档": req_doc, "蓝图文档": blue_doc, "说明": note, } def build_s0(req_t: str, blue_t: str, q: dict) -> list[dict]: m = "S0" s = q["summary"][m] rows: list[dict] = [] front_items = [ ( "主数据维护（物料/客户/供应商/人员组织）", "主数据维护页面", ["物料", "客户", "供应商"], ["员工", "组织", "人员"], "需求文档按列表逐项描述物料/客户/供应商等；蓝图第2–6章按域覆盖。", ), ( "BOM与工艺路线维护", "BOM与工艺路线维护", ["BOM", "工艺路线"], ["标准 BOM", "工艺"], "需求含标准BOM、工艺路线、工序等；蓝图「制造主数据」章有对应方案。", ), ( "仓储库位与条码规则", "仓储库位与条码规则", ["库位", "条码", "批次"], ["仓储", "货架"], "需求含库位、批次、条码规则；蓝图「仓储主数据」章有对应内容。", ), ( "质量检验标准配置", "质量检验标准配置", ["检验标准", "检验项目", "抽样"], ["IQC", "质量"], "需求含检验项目/标准/抽样；蓝图「质量主数据」章有检验基准类描述。", ), ( "指标目录与业务口径配置", "指标目录与业务口径配置", ["指标"], ["业务口径", "指标目录", "口径定义"], "需求文档有指标/KPI相关条目的覆盖不完整；蓝图未单独设指标/口径章节。", ), ( "指标图谱浏览", "指标图谱浏览", ["指标"], ["图谱", "指标图谱"], "报价前端单独列出指标图谱；需求仅有指标类描述，蓝图无「图谱浏览」专章。", ), ] for item, key, rkw, bkw, note in front_items: desc = find_segment(s["front"], key) if s["front_days"]: desc = f"{desc}（模块前端合计 {s['front_days']} 人天；原文：{s['front']})" rows.append( make_row( m, "【汇总·前端】运营建模", item, desc, match_status(req_t, rkw, required=rkw[:2] if len(rkw) >= 2 else rkw), match_status(blue_t, bkw if bkw else rkw), note, ) ) for seg in split_quote_segments(s["back"], "、"): rows.append( make_row( m, "【汇总·后端】运营建模", seg, f"后端：{seg}（原文：{s['back']}）", match_status(req_t, [seg[:4]] if len(seg) >= 4 else [seg[:2]]), match_status(blue_t, [seg[:4]] if len(seg) >= 4 else [seg[:2]]), "后端能力在需求列表条目中有分散体现；蓝图以域方案描述为主，未逐条复现报价原文。", ) ) for seg in split_quote_segments(s["db"], "，"): rows.append( make_row( m, "【汇总·数据库】运营建模", seg, f"数据库：{seg}（原文：{s['db']}）", match_status(req_t, [w for w in ["物料", "BOM", "库位", "检验", "指标", "参数"] if w in seg]), match_status(blue_t, [w for w in ["物料", "BOM", "库位", "检验", "指标", "参数"] if w in seg]), "数据对象在需求条目中分散出现；蓝图未单独设「数据库设计」节，按域方案间接覆盖。", ) ) detail_rows = q["detail"].get(m, []) for i, (item, note) in enumerate( [ ("数据建模", "报价明细工时包；对应需求文档大量列表型主数据条目。"), ("业务建模", "报价明细工时包；对应蓝图 Word 按销售/供应/制造/质量/仓储五域组织。"), ] ): desc = detail_rows[i] if i < len(detail_rows) else item rows.append( make_row( m, "【明细】功能开发", item, desc, "—", "✅", note, ) ) rows.append( make_row( m, "【算法清单】", "（本模块无单独算法行）", "算法清单 sheet 自「产销协同」起列出模块算法；运营建模未单列算法条目。", "—", "—", "不属于报有他无；本版报价未对 S0 承诺独立算法清单项。", ) ) return rows def build_s1(req_t: str, blue_t: str, q: dict) -> list[dict]: m = "S1" s = q["summary"][m] algo_full = q["algo"].get(m, "") rows: list[dict] = [] front_specs = [ ("订单评审", True, "（一）订单评审管理 REQ-S1-003", "蓝图第2章 订单评审管理", "报价前端与需求（一）、蓝图第2章一致，可逐项验收。"), ("合同评审", False, None, None, "报价汇总前端明确列出；需求文档无（一）之前章节，无 REQ-S1-002 专章；蓝图无 FUNC-S1-002 章。"), ("产品设计", False, None, None, "报价汇总前端明确列出；需求/蓝图均无 REQ-S1-001 / FUNC-S1-001 对应章节。"), ("订单交付", True, "（二）订单交付管理 REQ-S1-004", "蓝图第3章", "报价「订单交付」与需求（二）、蓝图第3章一致。"), ("发货", True, "（三）订单发货管理 REQ-S1-005", "蓝图第4章", "报价「发货」与需求（三）、蓝图第4章一致。"), ("工单下达", True, "（四）工单下达 REQ-S1-006", "蓝图第5章", "报价与需求（四）、蓝图第5章一致。"), ("计划联动看板", True, "（五）计划联动看板 REQ-S1-007", "蓝图第6章", "报价与需求（五）、蓝图第6章一致。"), ("产销协同看板", True, "（七）产销协同看板 REQ-S1-009", "蓝图第8章", "报价与需求（七）、蓝图第8章一致。"), ] for key, ok, req_ref, blue_ref, note in front_specs: seg = find_segment(s["front"], key, "/") desc = f"{seg}（模块前端合计 {s['front_days']} 人天；原文：{s['front']})" rows.append( make_row( m, "【汇总·前端】产销协同", key, desc, "✅" if ok else "❌", "✅" if ok else "❌", note if not ok else f"{note} 对照位置：需求{req_ref}；{blue_ref}。", ) ) for seg in split_quote_segments(s["back"], "、"): kw_map = { "销售订单": ["订单", "变更"], "合同": ["评审", "合同"], "交期": ["交期", "齐套"], "发货计划": ["发货", "ASN"], "工单下达": ["工单下达"], "订单交付进度": ["交付", "跟踪"], "发货达成": ["发货", "异常"], "看板指标": ["看板", "指标"], } kw = next((v for k, v in kw_map.items() if k in seg), [seg[:2]]) extra = "" if "设计评审" in seg or "合同" in seg: extra = "其中合同评审、设计评审在 Word 中缺独立章节。" elif "产品设计与评审资料" in seg: extra = "" rows.append( make_row( m, "【汇总·后端】产销协同", seg, f"后端：{seg}（原文：{s['back']}）", match_status(req_t, kw), match_status(blue_t, kw), f"报价后端能力项；需求/蓝图在相关功能章有分散描述。{extra}".strip(), ) ) for seg in split_quote_segments(s["db"], "，"): kw = [w for w in ["订单", "合同", "产品设计", "发货", "工单", "交付", "缺料", "看板"] if w in seg] note = "" if "产品设计" in seg: note = "报价数据库对象含产品设计资料；需求/蓝图均无产品设计专章，无法直接对照。" rows.append( make_row( m, "【汇总·数据库】产销协同", seg, f"数据库：{seg}（原文：{s['db']}）", match_status(req_t, kw), match_status(blue_t, kw), note or "报价数据域在需求/蓝图相关章节中间接体现。", ) ) detail_specs = [ ("产销协同蓝图设计", q["detail"].get("S1_blueprint", ""), "—", "✅", "报价明细 11 人天蓝图设计；交付件为《S1产销协同模块蓝图设计方案》Word 全文。"), ("订单管理", None, "⚠️", "⚠️", "报价 2 人天打包项，覆盖订单评审/合同/产品设计/交付/发货等；Word 缺 001/002 章，003–005 有。"), ("工单管理", None, "✅", "✅", "报价 2 人天；与需求（四）工单下达、蓝图第5章一致。"), ("计划管理", None, "⚠️", "⚠️", "报价 2 人天；对应计划联动看板+需求明细核验；后者在报价汇总前端未单独点名。"), ("产销协同看板", None, "✅", "✅", "报价 1 人天；与需求（七）、蓝图第8章一致。"), ] detail_lines = q["detail"].get(m, []) for i, (key, _, rs, bs, note) in enumerate(detail_specs): desc = detail_lines[i] if i < len(detail_lines) else key if key == "产销协同蓝图设计": desc = q["detail"].get("S1_blueprint", desc) rows.append( make_row(m, "【明细】", key, desc, rs, bs, note) ) if q["kanban"].get(m): rows.append( make_row( m, "【明细】九宫格智慧运营看板", "产销协同", q["kanban"][m], "❌", "❌", "报价另列 2 人天九宫格产销看板；需求/蓝图仅模块内产销协同看板，未说明与九宫格条目关系。", ) ) for algo in split_quote_segments(algo_full, "、"): kw_map = { "订单资源检查": (["交期", "齐套", "库存"], "003 订单评审"), "供需缺口": (["缺料", "差额", "满足"], "008 需求明细核验"), "安全库存": ([], None), "订单交期模拟": (["交期", "预计交付"], "003 订单评审"), } kw, ref = [], None for k, (w, r) in kw_map.items(): if k in algo: kw, ref = w, r break rs = match_status(req_t, kw) if kw else "❌" bs = match_status(blue_t, kw) if kw else "❌" ref_note = f"业务上最接近 {ref}。" if ref else "需求/蓝图全文无相关表述。" rows.append( make_row( m, "【算法清单】产销协同", algo, f"{algo}（模块算法原文：{algo_full}）", rs, bs, f"报价算法清单列名；Word 未出现该算法名称，{ref_note} 验收时需补算法说明或改报价。", ) ) rows.append( make_row( m, "（他有报无·供参考）", "需求明细核验", "未出现在报价汇总前端 8 项中；明细「计划管理」可能覆盖。", "✅", "✅", "需求（六）/蓝图第7章有专章；报价汇总前端未单独列出，属文档有而报价汇总未点名。", ) ) return rows def build_s2(req_t: str, blue_t: str, q: dict) -> list[dict]: m = "S2" s = q["summary"][m] algo_full = q["algo"].get(m, "") rows: list[dict] = [] front_specs = [ ("生产排程", ["生产排程", "REQ-S2-001"], ["生产排程", "FUNC-S2-001"], "报价与需求（一）、蓝图第2章一致。"), ("可执行日计划", ["日计划", "REQ-S2-003"], ["作业计划", "FUNC-S2-003"], "报价「可执行日计划」对应需求（二）作业计划、蓝图第3章。"), ("产线日历与休息加班", ["产线日历", "休息", "加班", "节假日"], ["REQ-S2-004", "REQ-S2-005"], "报价合并表述；需求有日历/休息/节假日/加班多条（004–007）；蓝图合并在作业计划章，未逐条分章。"), ("工单进度看板", ["工单", "进度", "REQ-S2-008"], ["进度"], "报价「工单进度看板」；需求 REQ-S2-008 有；蓝图无独立章标题，合并在看板章。"), ("制造协同看板", ["制造协同看板", "REQ-S2-009"], ["FUNC-S2-009"], "报价与需求（三）看板、蓝图第4章一致。"), ] for key, rkw, bkw, note in front_specs: seg = find_segment(s["front"], key.split("与")[0], "/") if key not in seg and key == "产线日历与休息加班": seg = "产线日历与休息加班" desc = f"{seg}（模块前端合计 {s['front_days']} 人天；原文：{s['front']})" rows.append( make_row( m, "【汇总·前端】制造协同", key, desc, match_status(req_t, rkw, required=rkw[:1]), match_status(blue_t, bkw, required=bkw[:1]), note, ) ) for seg in split_quote_segments(s["back"], "、"): note = "" if "重排" in seg or "冲突" in seg: note = "需求 REQ-S2-002 排产异常有覆盖；「冲突识别/动态重排」表述与报价原文不完全一致。" rows.append( make_row( m, "【汇总·后端】制造协同", seg, f"后端：{seg}（原文：{s['back']}）", match_status(req_t, [w for w in ["排程", "日计划", "日历", "异常", "进度", "看板"] if w in seg][:2] or [seg[:2]]), match_status(blue_t, [w for w in ["排程", "日计划", "日历", "异常", "进度", "看板"] if w in seg][:2] or [seg[:2]]), note or "报价后端项；需求/蓝图在排程、日计划、看板章有分散描述。", ) ) for seg in split_quote_segments(s["db"], "，"): rows.append( make_row( m, "【汇总·数据库】制造协同", seg, f"数据库：{seg}（原文：{s['db']}）", match_status(req_t, [w for w in ["排程", "日计划", "日历", "进度", "异常", "工艺", "看板"] if w in seg]), match_status(blue_t, [w for w in ["排程", "日计划", "日历", "进度", "异常", "工艺", "看板"] if w in seg]), "报价数据域；需求/蓝图未单独设库表清单，在方案描述中间接体现。", ) ) detail_specs = [ ("制造协同蓝图设计", "—", "✅", "报价明细 11 人天；交付件为《S2制造协同模块蓝图设计方案》Word。"), ("制造需求管理", "❌", "❌", "报价明细 2 人天独立行；需求/蓝图均无「制造需求管理」专章或同名能力描述。"), ("生产排程", "✅", "✅", "报价 2 人天；与需求（一）、蓝图第2章一致。"), ("作业计划", "✅", "✅", "报价 2 人天；与需求（二）及日历/休息/节假日/加班条目、蓝图第3章一致。"), ("制造协同看板", "✅", "✅", "报价 1 人天；与需求（三）、蓝图第4章一致。"), ] detail_lines = q["detail"].get(m, []) for i, (key, rs, bs, note) in enumerate(detail_specs): if key == "制造协同蓝图设计": desc = q["detail"].get("S2_blueprint", detail_lines[0] if detail_lines else key) else: idx = i - 1 if i > 0 else 0 desc = detail_lines[idx] if idx < len(detail_lines) else key rows.append(make_row(m, "【明细】", key, desc, rs, bs, note)) if q["kanban"].get(m): rows.append( make_row( m, "【明细】九宫格智慧运营看板", "制造协同", q["kanban"][m], "❌", "❌", "报价另列 2 人天；需求/蓝图仅有模块制造协同看板，未说明与九宫格条目对应关系。", ) ) for algo in split_quote_segments(algo_full, "、"): kw_map = { "齐套校验": (["齐套"], "排程/工单相关描述"), "多维约束排程": (["排程", "约束", "产能"], "生产排程章"), "扰动重排": (["排产异常", "异常"], "REQ-S2-002"), } kw, ref = [], "" for k, (w, r) in kw_map.items(): if k in algo: kw, ref = w, r break rows.append( make_row( m, "【算法清单】制造协同", algo, f"{algo}（模块算法原文：{algo_full}）", match_status(req_t, kw), match_status(blue_t, kw), f"报价算法清单列名；Word 未写算法名称，相关业务最接近 {ref or '未找到'}。", ) ) rows.append( make_row( m, "（文档扩展项）", "排产异常记录 REQ-S2-002", "未在报价汇总/算法清单中单列条目；需求文档有 REQ-S2-002 专条。", "✅", "⚠️", "需求有排产异常记录；蓝图无独立 FUNC-S2-002 章标题，内容可能在排程章中。", ) ) return rows def style_sheet(ws) -> None: header_fill = PatternFill("solid", fgColor="1F3864") header_font = Font(color="FFFFFF", bold=True) for col, h in enumerate(HEADERS, 1): cell = ws.cell(1, col, h) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.freeze_panes = "A2" widths = [6, 20, 28, 52, 10, 10, 56] for i, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = w for row_cells in ws.iter_rows(min_row=2, max_row=ws.max_row): for cell in row_cells: cell.alignment = Alignment(vertical="top", wrap_text=True) req_v = str(row_cells[4].value or "") blue_v = str(row_cells[5].value or "") note_v = str(row_cells[6].value or "") if "🔴" in note_v or ((req_v == "❌" or blue_v == "❌") and req_v != "—"): for cell in row_cells: cell.fill = PatternFill("solid", fgColor="FCE4D6") def write_excel(all_rows: list[dict]) -> None: OUT_XLSX.parent.mkdir(parents=True, exist_ok=True) wb = openpyxl.Workbook() wb.remove(wb.active) by_mod: dict[str, list[dict]] = {"S0": [], "S1": [], "S2": []} for r in all_rows: by_mod[r["模块"]].append(r) for mod in ["S0", "S1", "S2"]: ws = wb.create_sheet(mod) for c, h in enumerate(HEADERS, 1): ws.cell(1, c, h) for ri, data in enumerate(by_mod[mod], 2): for c, h in enumerate(HEADERS, 1): ws.cell(ri, c, data[h]) style_sheet(ws) ws_all = wb.create_sheet("全部", 0) for c, h in enumerate(HEADERS, 1): ws_all.cell(1, c, h) for ri, data in enumerate(all_rows, 2): for c, h in enumerate(HEADERS, 1): ws_all.cell(ri, c, data[h]) style_sheet(ws_all) info = wb.create_sheet("说明") info["A1"] = "对比说明" lines = [ f"报价单：{QUOTE.name}", "生成日期：2026-06-22", "列「需求项描述」：摘自报价单原文（汇总/明细/算法清单），含工时或完整句式。", "列「需求文档」「蓝图文档」：✅ 有对应章节或内容 ⚠️ 部分有 ❌ 无 — 不适用", "浅橙行：报价已列但交付文档未覆盖（说明列含 🔴）或双 ❌", "S0：…业务需求描述-V0.2.docx / …业务蓝图设计方案-V0.2.docx", "S1/S2：各模块业务需求描述.docx + 模块蓝图设计方案.docx", ] for i, line in enumerate(lines, 2): info.cell(i, 1, line) wb.save(OUT_XLSX) def main() -> None: corpus = load_quote_corpus() all_rows: list[dict] = [] for mod, (req_p, blue_p) in DOCS.items(): req_t = load_doc_text(req_p) blue_t = load_doc_text(blue_p) if mod == "S0": all_rows.extend(build_s0(req_t, blue_t, corpus)) elif mod == "S1": all_rows.extend(build_s1(req_t, blue_t, corpus)) else: all_rows.extend(build_s2(req_t, blue_t, corpus)) write_excel(all_rows) print(f"Wrote {len(all_rows)} rows -> {OUT_XLSX}") if __name__ == "__main__": main()