#!/usr/bin/env python3 """Build 18 evidence-backed smart-canteen configuration and quotation workbooks.""" from __future__ import annotations import argparse import html import json import re import tempfile from copy import copy from pathlib import Path from zipfile import ZIP_DEFLATED, ZipFile from openpyxl import Workbook, load_workbook from openpyxl.formatting.rule import CellIsRule from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation ORANGE = "F57C00" ORANGE_2 = "FF9F1C" LIGHT_ORANGE = "FFF1DF" PALE_YELLOW = "FFF4CC" NAVY = "12324A" BLUE = "0B79B7" GREEN = "218C74" RED = "C0392B" GRAY = "E9EEF2" LIGHT_GRAY = "F5F7F9" WHITE = "FFFFFF" BLACK = "222222" THIN_GRAY = Side(style="thin", color="C9D2D9") MEDIUM_ORANGE = Side(style="medium", color=ORANGE) def ntext(value) -> str: return re.sub(r"\s+", "", str(value or "")).replace("（", "(").replace("）", ")") def numeric(value): if isinstance(value, (int, float)): return float(value) return None def load_catalog(source: Path): wb = load_workbook(source, data_only=False, read_only=False) rows = [] for ws in wb.worksheets: if ws.title in {"1-营养结算", "2-智慧食安"}: cols = dict(name=3, model=4, brand=6, unit=8, function=10, list_price=11, floor=12, channel=13, scene=14) else: cols = dict(name=3, model=4, brand=6, unit=8, function=9, list_price=10, floor=11, channel=12, scene=13) for r in range(1, ws.max_row + 1): name = ws.cell(r, cols["name"]).value if not name: continue list_price = ws.cell(r, cols["list_price"]).value if numeric(list_price) is None: alt = ws.cell(r, 7).value if numeric(alt) is not None: list_price = alt rows.append( { "sheet": ws.title, "row": r, "name": str(name).strip(), "model": str(ws.cell(r, cols["model"]).value or "-").strip(), "brand": str(ws.cell(r, cols["brand"]).value or "-").strip(), "unit": str(ws.cell(r, cols["unit"]).value or "项").strip(), "function": str(ws.cell(r, cols["function"]).value or "").strip(), "scene": str(ws.cell(r, cols["scene"]).value or "").strip(), "list_price": numeric(list_price), "floor_price": numeric(ws.cell(r, cols["floor"]).value), "channel_price": numeric(ws.cell(r, cols["channel"]).value), "source": f"{ws.title}!B{r}:N{r}", } ) return rows ALIASES = { "NUTR-PC": ("1-营养结算", "智慧营养健康餐厅管理系统V2.0(PC端)"), "NUTR-MOBILE": ("1-营养结算", "智慧营养健康餐厅管理系统2.0(手机端)"), "SETTLEMENT": ("1-营养结算", "智能结算系统V1.0"), "SERVER-NUTR": ("1-营养结算", "联想塔式服务器"), "CLOUD-NUTR": ("1-营养结算", "专业版阿里云服务"), "N450D": ("1-营养结算", "膳识智能台(双秤)"), "N460": ("1-营养结算", "膳识智能台(无需绑盘终端)"), "BP001": ("1-营养结算", "智能绑盘终端"), "BUFFET-D": ("1-营养结算", "布菲炉(双秤)"), "TRAY": ("1-营养结算", "智能套餐平托盘"), "S20A": ("1-营养结算", "AI智能识别结算台-桌面款"), "P200": ("1-营养结算", "台式双面收银机"), "P180": ("1-营养结算", "壁挂式收银机"), "P160": ("1-营养结算", "卧式消费机"), "P128": ("1-营养结算", "手持机"), "P280": ("1-营养结算", "智能称重收银机"), "PRINTER": ("1-营养结算", "后厨小票打印机"), "PICKUP": ("1-营养结算", "智能取餐柜"), "DATA-SCREEN": ("1-营养结算", "智慧数据大屏"), "REMAINDER": ("1-营养结算", "菜品余量看板"), "MENU-SCREEN": ("1-营养结算", "电子菜牌"), "CARD-READER": ("1-营养结算", "发卡器"), "M1-CARD": ("1-营养结算", "M1智能卡"), "IMPL-NUTR": ("1-营养结算", "实施运杂费"), "FS-PC": ("2-智慧食安", "食品安全监督管理系统(PC端)"), "FS-MOBILE": ("2-智慧食安", "食品安全监督管理系统(手机端)"), "CAMERA": ("2-智慧食安", "高清网络摄像机"), "AI-BOX": ("2-智慧食安", "AI边缘计算智能分析盒"), "MORNING": ("2-智慧食安", "AI智能晨检仪"), "SAMPLE-CABINET": ("2-智慧食安", "智能食品留样柜"), "SAMPLE-SCALE": ("2-智慧食安", "智能食品留样称"), "TEMP": ("2-智慧食安", "智能温湿度监测仪"), "FOOD-TEST": ("2-智慧食安", "多功能食品安全检测仪"), "FS-SCREEN": ("2-智慧食安", "食安监管驾驶舱大屏"), "SERVER-FS": ("2-智慧食安", "本地部署]塔式服务器"), "CLOUD-FS": ("2-智慧食安", "云端部署]云服务器"), "IMPL-FS": ("2-智慧食安", "实施运杂费"), "PUR-SCH": ("3-智慧采购", "学校版]智慧采购管理系统"), "PUR-GOV": ("3-智慧采购", "政府版]智慧采购管理系统"), "PUR-GRP": ("3-智慧采购", "集团版]智慧采购管理系统"), "RECEIPT-SCALE": ("3-智慧采购", "AI智能收货秤(含软件)"), "RECEIPT-SCALE-2": ("3-智慧采购", "AI智能收货秤双摄版"), "PDA": ("3-智慧采购", "库存盘点机"), "PUR-SCREEN": ("3-智慧采购", "智慧采购分析中心"), "SERVER-PUR": ("3-智慧采购", "本地部署]塔式服务器"), "CLOUD-PUR": ("3-智慧采购", "云端部署]云服务器"), "IMPL-PUR": ("3-智慧采购", "实施运杂费"), } def catalog_item(catalog, alias): sheet, needle = ALIASES[alias] strip_brackets = str.maketrans("", "", "[]【】") needle_n = ntext(needle).translate(strip_brackets) matches = [] for item in catalog: hay = ntext(item["name"]).translate(strip_brackets) if item["sheet"] == sheet and needle_n in hay: matches.append(item) if not matches: raise KeyError(f"catalog alias not found: {alias} {sheet} {needle}") return dict(matches[0]) def line(alias, qty, category, note="", attribute="标配", include=True): return {"alias": alias, "qty": qty, "category": category, "note": note, "attribute": attribute, "include": include} def custom(name, model, qty, unit, category, note, attribute="待确认", include=True): return { "custom": True, "name": name, "model": model, "brand": "待确认", "unit": unit, "qty": qty, "category": category, "note": note, "attribute": attribute, "include": include, "list_price": None, "floor_price": None, "channel_price": None, "source": "无统一价格证据，需商务/产品确认", "function": "", "scene": "", } SPECS = { "SPU01-SKU01": { "baseline": "1个食堂、1台消费机、100张M1卡的最小刷卡闭环", "logic": "用智能结算系统承接人员、卡片、账户、消费和对账；设备与卡片按实际人数扩容。", "items": [line("SETTLEMENT", 1, "软件"), line("P200", 1, "结算设备"), line("CARD-READER", 1, "卡务设备"), line("M1-CARD", 100, "卡片"), line("CLOUD-NUTR", 1, "云服务"), line("IMPL-NUTR", 1, "实施服务")], "optional": [line("P180", 1, "结算设备", "壁挂点位可选", "选配", False), line("SERVER-NUTR", 1, "部署", "本地部署替代公有云", "选配", False)], }, "SPU01-SKU02": { "baseline": "2个食堂、4个档口终端的多点运营起步包", "logic": "平台和移动端按项目授权，消费终端按食堂/档口数量配置；高峰容量需另做压测。", "items": [line("SETTLEMENT", 1, "软件"), line("NUTR-MOBILE", 1, "移动端"), line("P200", 4, "结算设备"), line("CLOUD-NUTR", 1, "云服务"), line("IMPL-NUTR", 1, "实施服务")], "optional": [line("P180", 1, "结算设备", "按档口追加", "选配", False), line("P280", 1, "称重档口", "按重量计价档口选配", "选配", False)], }, "SPU01-SKU03": { "baseline": "1个集团、3个公司、4台消费终端的集团起步包", "logic": "采用综合管理平台作为集团管理底座；多法人结算、组织权限和集团授权范围须在报价前冻结。", "items": [line("NUTR-PC", 1, "集团平台"), line("NUTR-MOBILE", 1, "移动端"), line("P200", 4, "结算设备"), line("CLOUD-NUTR", 1, "云服务"), line("IMPL-NUTR", 1, "实施服务"), custom("集团多组织管控授权与配置", "CPT-GRP-AUTH", 1, "项", "授权配置", "集团/公司数量、权限和报表范围确认后单独定价")], "optional": [line("DATA-SCREEN", 1, "展示", "集团经营驾驶舱", "选配", False)], }, "SPU01-SKU04": { "baseline": "1个单位、2台终端、私有部署的补贴策略起步包", "logic": "综合管理平台承接复杂账户与消费规则；新增消费场景、财务接口和资金流程单独评估。", "items": [line("NUTR-PC", 1, "策略平台"), line("NUTR-MOBILE", 1, "H5/移动端"), line("P200", 2, "结算设备"), line("SERVER-NUTR", 1, "本地部署"), line("IMPL-NUTR", 1, "实施服务"), custom("补贴与多策略专项配置", "CPT-POL-CONF", 1, "项", "规则配置", "班次、餐标、本人/亲友/招待/访客等策略范围确认后定价")], "optional": [custom("财务系统接口", "API-FIN", 1, "个", "第三方接口", "取得接口文档与测试环境后评估", "选配", False)], }, "SPU01-SKU05": { "baseline": "10个菜位（5台双秤）、1台绑盘终端、200个托盘", "logic": "按菜位数配置称重台和布菲炉，绑盘终端按入口与高峰吞吐配置，托盘按周转量配置。", "items": [line("NUTR-PC", 1, "软件"), line("NUTR-MOBILE", 1, "移动端"), line("N450D", 5, "称重设备"), line("BUFFET-D", 5, "配套餐炉"), line("BP001", 1, "身份设备"), line("TRAY", 200, "托盘"), line("SERVER-NUTR", 1, "本地部署"), line("IMPL-NUTR", 1, "实施服务")], "optional": [custom("一卡通接口联调", "API-CARD", 1, "个", "第三方接口", "按卡系统协议和测试环境评估", "选配", False)], }, "SPU01-SKU06": { "baseline": "10个免绑盘称重菜位、1套余量看板、本地部署", "logic": "每个菜位配置免绑盘称重台；免绑身份链路、自动扣款和高峰吞吐必须真机验收。", "items": [line("NUTR-PC", 1, "软件"), line("NUTR-MOBILE", 1, "移动端"), line("N460", 10, "免绑称重设备"), line("REMAINDER", 1, "余量管理"), line("SERVER-NUTR", 1, "本地部署"), line("IMPL-NUTR", 1, "实施服务"), custom("免绑身份与自动扣款专项验收", "CPT-NBW-VERIFY", 1, "项", "专项服务", "按项目身份方式、网络和设备协议核价")], "optional": [], }, "SPU01-SKU07": { "baseline": "1个人工兜底通道、1台桌面AI识别结算台", "logic": "硬件按通道数量配置；首批菜品建模、餐具标准和现场光照决定项目调优工作量。", "items": [line("NUTR-PC", 1, "软件"), line("S20A", 1, "AI结算设备"), line("SERVER-NUTR", 1, "本地部署"), line("IMPL-NUTR", 1, "实施服务"), custom("首批菜品建模与识别调优", "CPT-AIV-MODEL", 1, "项", "模型服务", "按菜品数量、餐具和现场测试结果定价")], "optional": [custom("立式单/双通道升级", "CPT-S30A/S40A", 1, "台", "AI结算设备", "按高峰客流和通道选择", "选配", False)], }, "SPU01-SKU08": { "baseline": "1个线上订餐入口、1台后厨打印机、1组取餐柜", "logic": "软件按项目授权，打印机按厨房/档口配置，取餐柜按柜格和高峰订单量配置。", "items": [line("NUTR-PC", 1, "软件"), line("NUTR-MOBILE", 1, "移动订餐端"), line("PRINTER", 1, "后厨设备"), line("PICKUP", 1, "取餐设备"), line("CLOUD-NUTR", 1, "云服务"), line("IMPL-NUTR", 1, "实施服务")], "optional": [custom("自助点餐机", "CPT-DCJ300", 1, "台", "点餐设备", "原清单无统一价格", "选配", False), custom("排队叫号系统", "CPT-PD230", 1, "套", "叫号系统", "原清单无统一价格", "选配", False)], }, "SPU01-SKU09": { "baseline": "3所学校的区域起步包，每校1台核销终端", "logic": "平台、家长H5和学校开通按母项目计价；学校数量、银行接口和学生规模决定专项费用。", "items": [line("NUTR-PC", 1, "区域平台"), line("NUTR-MOBILE", 1, "家长H5"), line("P180", 3, "学校核销终端"), line("SERVER-NUTR", 1, "私有部署"), line("IMPL-NUTR", 1, "实施服务"), custom("银行App/H5接口联调", "API-BANK-H5", 1, "个", "第三方接口", "取得银行接口、测试环境和联调责任人后报价")], "optional": [custom("新增学校开通服务", "CPT-K12-SCHOOL", 1, "所", "扩容服务", "按学校、班级和数据初始化规模报价", "选配", False)], }, "SPU01-SKU10": { "baseline": "2块电子菜牌、1套云服务和统一内容发布", "logic": "显示终端按屏幕和档口数量配置；内容管理、审核、定时发布和设备状态作为软件范围确认。", "items": [line("MENU-SCREEN", 2, "显示设备"), line("CLOUD-NUTR", 1, "云服务"), line("IMPL-NUTR", 1, "实施服务"), custom("电子菜牌营养管理系统V1.0", "CPT-EM431", 1, "套", "发布软件", "原产品清单未给统一价格，需商务确认")], "optional": [custom("信息发布盒", "HLF-H313", 1, "台", "显示配件", "客户自有电视/一体机时选配，价格待确认", "选配", False)], }, "SPU01-SKU11": { "baseline": "1套内网平台、2台核销终端、1台手持应急机", "logic": "本地服务器和离线终端构成最小闭环；断网一致性、恢复回传和安全审计需专项验收。", "items": [line("SETTLEMENT", 1, "软件"), line("SERVER-NUTR", 1, "本地部署"), line("P180", 2, "核销终端"), line("P128", 1, "应急终端"), line("IMPL-NUTR", 1, "实施服务"), custom("纯离线、恢复回传与安全审计专项", "CPT-SUP-OFFLINE", 1, "项", "专项服务", "按网络隔离、安全等级和回传方式评估")], "optional": [], }, "SPU01-SKU12": { "baseline": "10个菜位（5台双秤）、健康测评、智能烹饪和余量运营组合包", "logic": "先按合同组合形成专业版BOM；健康数据、菜谱和余量是否贯通须分别联调验收。", "items": [line("NUTR-PC", 1, "营养健康平台"), line("NUTR-MOBILE", 1, "移动端"), line("N450D", 5, "称重设备"), line("BUFFET-D", 5, "配套餐炉"), line("BP001", 1, "身份设备"), line("TRAY", 200, "托盘"), line("PRINTER", 1, "后厨设备"), line("REMAINDER", 1, "余量管理"), line("CLOUD-NUTR", 1, "云服务"), line("IMPL-NUTR", 1, "实施服务"), custom("SH-V20智能互联健康体检一体机", "SH-V20", 1, "台", "健康设备", "附件有参数但无统一价格，需商务确认"), custom("TC-40EA中型智能烹饪机器人", "TC-40EA", 1, "台", "智厨设备", "附件有参数但无统一价格，需商务确认"), custom("健康/菜谱/余量数据接口联调", "API-HEALTH-KITCHEN", 1, "项", "接口服务", "按三类数据契约和验收范围报价")], "optional": [], }, "SPU02-SKU01": { "baseline": "1个食堂、8路视频、1套晨检/留样/快检的标准食安包", "logic": "软件一套，AI视频按摄像头路数和点位配置，现场设备按食堂配置。", "items": [line("FS-PC", 1, "食安平台"), line("FS-MOBILE", 1, "移动端"), line("CAMERA", 8, "视频采集"), line("AI-BOX", 1, "AI分析"), line("MORNING", 1, "晨检"), line("SAMPLE-CABINET", 1, "留样"), line("TEMP", 2, "环境监测"), line("FOOD-TEST", 1, "快检"), line("CLOUD-FS", 1, "云服务"), line("IMPL-FS", 1, "实施服务")], "optional": [line("FS-SCREEN", 1, "公示大屏", "客户需要前厅公示时选配", "选配", False)], }, "SPU02-SKU02": { "baseline": "3个食堂、每食堂8路视频和一套现场设备", "logic": "多食堂平台统一管理；硬件与实施按食堂数量计价，管理汇总范围需在合同中冻结。", "items": [line("FS-PC", 1, "食安平台"), line("FS-MOBILE", 1, "移动端"), line("CAMERA", 24, "视频采集"), line("AI-BOX", 3, "AI分析"), line("MORNING", 3, "晨检"), line("SAMPLE-SCALE", 3, "留样"), line("TEMP", 6, "环境监测"), line("FOOD-TEST", 3, "快检"), line("SERVER-FS", 1, "本地部署"), line("IMPL-FS", 3, "实施服务"), custom("多食堂汇总、分级权限与督办配置", "待商务确认", 1, "项", "管理配置", "按食堂数量、层级和汇总报表范围定价")], "optional": [line("FS-SCREEN", 1, "管理大屏", "管理中心选配", "选配", False)], }, "SPU02-SKU03": { "baseline": "1个集团、5个食堂、双服务器高可用的集团食安起步包", "logic": "总部平台与驾驶舱集中部署；现场采集设备按食堂复制，跨区域和高可用单独评估。", "items": [line("FS-PC", 1, "集团食安平台"), line("FS-MOBILE", 1, "移动端"), line("FS-SCREEN", 1, "集团驾驶舱"), line("CAMERA", 40, "视频采集"), line("AI-BOX", 5, "AI分析"), line("MORNING", 5, "晨检"), line("SAMPLE-SCALE", 5, "留样"), line("TEMP", 10, "环境监测"), line("FOOD-TEST", 5, "快检"), line("SERVER-FS", 2, "高可用服务器"), line("IMPL-FS", 5, "实施服务"), custom("集团多级组织、高可用与跨区域配置", "待商务确认", 1, "项", "集团配置", "组织层级、区域数、高可用和数据汇聚方式确认后报价")], "optional": [], }, "SPU03-SKU01": { "baseline": "1个食堂、1个收货点、1台盘点机的基础进销存包", "logic": "单食堂以学校版作为基础计价样板；非学校客户须在报价前确认对应产品版本。", "items": [line("PUR-SCH", 1, "进销存平台"), line("RECEIPT-SCALE", 1, "收货设备"), line("PDA", 1, "盘点设备"), line("CLOUD-PUR", 1, "云服务"), line("IMPL-PUR", 1, "实施服务")], "optional": [custom("财务系统接口", "API-FIN", 1, "个", "第三方接口", "取得接口文档后评估", "选配", False)], }, "SPU03-SKU02": { "baseline": "3个食堂、2个收货点、3台盘点机的多食堂包", "logic": "以政府版的审批和监管能力作为多食堂计价样板；食堂数量和仓库数量驱动设备与实施费用。", "items": [line("PUR-GOV", 1, "多食堂平台"), line("RECEIPT-SCALE-2", 2, "收货设备"), line("PDA", 3, "盘点设备"), line("SERVER-PUR", 1, "本地部署"), line("IMPL-PUR", 1, "实施服务"), custom("多食堂需求汇总、分仓与调拨配置", "待商务确认", 1, "项", "管理配置", "按食堂、仓库和调拨流程范围定价")], "optional": [line("PUR-SCREEN", 1, "管理大屏", "统一采购分析选配", "选配", False)], }, "SPU03-SKU03": { "baseline": "1个集团、5个食堂、3个收货点、5台盘点机", "logic": "集团版平台按项目授权；收货、仓储和实施按点位扩容，ERP/财务接口单独核价。", "items": [line("PUR-GRP", 1, "集团采购平台"), line("RECEIPT-SCALE-2", 3, "收货设备"), line("PDA", 5, "盘点设备"), line("PUR-SCREEN", 1, "集团分析中心"), line("SERVER-PUR", 2, "高可用服务器"), line("IMPL-PUR", 1, "实施服务"), custom("集团多公司、多仓与成本归集配置", "待商务确认", 1, "项", "集团配置", "按公司、食堂、仓库和核算口径定价"), custom("ERP/财务接口联调", "API-ERP-FIN", 1, "个", "第三方接口", "取得接口文档、测试环境和责任人后报价")], "optional": [], }, } FRONT_CODES = [f"SPU01-SKU{i:02d}" for i in range(1, 13)] def feature(l1, l2, hardware, std=(), opt=(), trigger="", recommend="", talk=""): return { "l1": l1, "l2": l2, "hardware": hardware, "std": set(std), "opt": set(opt), "trigger": trigger, "recommend": recommend, "talk": talk, } PC_FEATURES = [ feature("组织与权限", "单食堂/餐厅档案", "PC管理端", FRONT_CODES, trigger="客户要建立基础餐厅和权限", recommend="当前SKU", talk="这是所有前厅SKU的基础管理能力，可在当前SKU内配置。"), feature("组织与权限", "多食堂与档口分级", "PC管理端；消费终端按档口绑定", ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU10","SPU01-SKU12"), ("SPU01-SKU01","SPU01-SKU11"), "客户说一个食堂不够，要分食堂和档口看账", "SPU01-SKU02 多档口多食堂消费结算", "极简版只管单食堂；需要多食堂、档口分级和分别对账，建议升级多档口多食堂版。"), feature("组织与权限", "集团/多公司数据权限", "集团PC管理端", ("SPU01-SKU03",), ("SPU01-SKU02","SPU01-SKU04"), "客户要求总部看全局、公司只看本公司", "SPU01-SKU03 集团版消费结算", "这不是普通多食堂配置，而是集团和多公司权限问题，建议直接选集团版。"), feature("组织与权限", "多学校独立开通与隔离", "区域PC管理端；学校核销终端", ("SPU01-SKU09",), (), "客户要连续开学校，每校独立管理", "SPU01-SKU09 K12家校阳光消费", "多学校、家长和学生链路属于K12版本，普通企业版不建议硬改。"), feature("人员与身份", "人员建档、导入与状态管理", "PC管理端", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU11","SPU01-SKU12"), (), "客户需要管理员工/学生/保障人员", "按客户类型选择对应SKU", "人员建档是交易类SKU基础能力；电子菜牌版不管理消费人员。"), feature("人员与身份", "M1卡发卡、绑卡与状态管理", "发卡器FK-MU-10；M1卡FM1108", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU11","SPU01-SKU12"), ("SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09"), "客户明确要求实体卡和原有卡务", "SPU01-SKU01 单食堂消费结算；复杂组织选SKU02/03/04", "如果只是刷卡闭环，单食堂版最合适；组织、补贴复杂时再升级对应版本。"), feature("人员与身份", "本人/亲友/招待/访客身份归类", "PC管理端；消费机", ("SPU01-SKU04",), ("SPU01-SKU02","SPU01-SKU03"), "客户有招待、亲友和访客不同扣款规则", "SPU01-SKU04 补贴与多策略结算", "基础版只解决统一消费；多身份和多场景扣款建议选补贴与多策略版。"), feature("人员与身份", "学生、班级与家长绑定", "邮储App/H5；学校核销终端", ("SPU01-SKU09",), (), "客户提出家长给学生缴费订餐", "SPU01-SKU09 K12家校阳光消费", "家长、学生、班级和学校对账是一套完整链路，直接推荐K12版本。"), feature("人员与身份", "健康测评档案与历史记录", "SH-V20健康体检一体机", ("SPU01-SKU12",), ("SPU01-SKU05","SPU01-SKU06","SPU01-SKU07"), "客户希望把体测/健康数据用于食堂服务", "SPU01-SKU12 营养健康智厨专业版", "普通结算版只记录就餐；需要健康测评、营养和智厨联动，建议专业版。"), feature("账户与补贴", "现金账户与补贴账户", "消费机/称重台/AI结算台", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU11","SPU01-SKU12"), (), "客户需要余额、补贴和扣款", "按场景选择交易SKU", "账户是交易类SKU基础能力；如果还要复杂补贴规则，升级SKU04。"), feature("账户与补贴", "充值与批量补贴发放", "PC管理端；发卡器/支付入口", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU09","SPU01-SKU11","SPU01-SKU12"), ("SPU01-SKU08",), "客户需要按部门或人员批量发补贴", "简单发放保留当前SKU；复杂规则选SPU01-SKU04", "简单充值补贴多数交易版都支持；审批、场景和多人群规则建议选多策略版。"), feature("账户与补贴", "班次、餐标、限额与多场景策略", "PC管理端；消费终端", ("SPU01-SKU04",), ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU11"), "客户说不同班次、人群和餐次扣款不同", "SPU01-SKU04 补贴与多策略结算", "这属于策略产品，不是基础结算开关；建议直接升级多策略版。"), feature("菜单与营养", "菜品、餐次、价格与菜单维护", "PC管理端", ("SPU01-SKU02","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU10","SPU01-SKU12"), ("SPU01-SKU01","SPU01-SKU03","SPU01-SKU04","SPU01-SKU11"), "客户需要维护菜品或线上菜单", "展示选SKU10；订餐选SKU08/09；营养运营选SKU12", "菜单功能要看目的：只展示选电子菜牌，线上下单选移动订餐，健康运营选专业版。"), feature("菜单与营养", "营养标签、摄入累计与营养反馈", "称重台；AI结算台；营养分析终端", ("SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU10","SPU01-SKU12"), ("SPU01-SKU08","SPU01-SKU09"), "客户不仅要结算，还要营养数据", "轻量展示选SKU10；取餐营养选SKU05/06/07；完整健康选SKU12", "营养不是一个按钮：展示、取餐累计和健康干预对应不同SKU，按客户目标推荐。"), feature("菜单与营养", "营养目标与健康运营", "SH-V20；营养分析一体机；PC/手机端", ("SPU01-SKU12",), ("SPU01-SKU05","SPU01-SKU06","SPU01-SKU07"), "客户要个人健康目标和持续复盘", "SPU01-SKU12 营养健康智厨专业版", "基础称重版能记录摄入；要健康目标、测评和持续复盘，应升级专业版。"), feature("菜单与营养", "电子菜牌内容审核与定时发布", "电子菜牌CPT-NE540/CPT-EM43；信息发布盒", ("SPU01-SKU10",), ("SPU01-SKU02","SPU01-SKU08","SPU01-SKU12"), "客户已有屏幕或要统一发布菜品价格", "SPU01-SKU10 营养菜单与电子菜牌", "普通结算版不默认带多屏发布；需要统一菜牌和营养展示，叠加电子菜牌SKU。"), feature("消费与结算", "定额、按次与自定义金额消费", "CPT-P200/P180/P160/P128", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU11"), ("SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU12"), "客户只要刷卡/刷码按次或金额扣费", "单食堂选SKU01；多档口选SKU02；集团选SKU03；复杂补贴选SKU04", "先按组织和策略复杂度选版本，不必直接上称重或AI。"), feature("消费与结算", "刷卡消费与核销", "消费机；绑盘终端；M1卡", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU11","SPU01-SKU12"), ("SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09"), "客户保留实体卡消费", "按组织规模选择SKU01/02/03/04", "刷卡是入口，不是版本名称；版本差异看食堂、公司和补贴策略。"), feature("消费与结算", "扫码/人脸身份与支付", "消费机；N460；AI识别台；核销终端", ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09"), ("SPU01-SKU01","SPU01-SKU05","SPU01-SKU11","SPU01-SKU12"), "客户要求不带卡或多种身份入口", "称重免绑选SKU06；AI菜品选SKU07；普通档口选SKU02/03/04", "先判断客户是要免卡、免绑盘还是AI识菜，再推荐对应SKU。"), feature("消费与结算", "绑盘称重与多菜位归集", "N450/N450D；BP001；条码托盘；布菲炉", ("SPU01-SKU05","SPU01-SKU12"), ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU04"), "客户想按克取餐、多菜位连续结算", "SPU01-SKU05 绑盘称重结算", "普通消费机做不到多菜位按克归集；建议选绑盘称重版。"), feature("消费与结算", "免绑盘称重与自动归集", "CPT-N460", ("SPU01-SKU06",), ("SPU01-SKU02","SPU01-SKU03"), "客户嫌绑盘多一步，希望刷脸直接称重", "SPU01-SKU06 免绑盘称重结算", "需要减少入口步骤，就从绑盘版升级到免绑盘版，但要做真机和高峰验收。"), feature("消费与结算", "AI菜品识别、纠错与自动计价", "CPT-S20A/S30A/S40A", ("SPU01-SKU07",), ("SPU01-SKU02","SPU01-SKU03"), "客户是小碗菜并希望自动识别菜品", "SPU01-SKU07 AI视觉识别结算", "基础结算不识别菜品；标准小碗菜场景建议AI视觉版，并保留人工纠错。"), feature("消费与结算", "纯离线核销、恢复回传与差异处理", "本地服务器；P180/P128；内网设备", ("SPU01-SKU11",), ("SPU01-SKU01","SPU01-SKU05","SPU01-SKU06"), "客户要求断外网仍能供餐", "SPU01-SKU11 集中保障供餐", "普通云端版不承诺纯离线闭环；封闭内网和保障供餐应选集中保障版。"), feature("订餐与取餐", "移动菜单、下单与支付", "手机端/H5；自助点餐机", ("SPU01-SKU08","SPU01-SKU09"), ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU12"), "客户要提前订餐、错峰分流", "企业选SPU01-SKU08；家校选SPU01-SKU09", "员工订餐和家长订餐是两套对象，分别推荐移动订餐版和K12版。"), feature("订餐与取餐", "厨房分单、打印与出餐任务", "后厨小票打印机XP-C58H；厨房屏", ("SPU01-SKU08","SPU01-SKU12"), ("SPU01-SKU09",), "客户要订单自动到厨房", "SPU01-SKU08 移动订餐与取餐", "极简结算只负责扣款；要把订单送到厨房并跟踪出餐，建议移动订餐版。"), feature("订餐与取餐", "叫号、取餐码与取餐柜核销", "叫号大屏；智能取餐柜CPT-QCG300", ("SPU01-SKU08",), ("SPU01-SKU09",), "客户排队长或需要错峰自提", "SPU01-SKU08 移动订餐与取餐", "要解决取餐排队，就叠加叫号或取餐柜，不是基础消费机功能。"), feature("订餐与取餐", "家长缴费、学生订餐与学校对账", "邮储App/H5；学校核销终端", ("SPU01-SKU09",), (), "客户要家长端缴费和学生餐", "SPU01-SKU09 K12家校阳光消费", "企业订餐版不含家校关系和学校对账，K12场景直接选家校阳光消费。"), feature("订单与对账", "订单查询、交易统计与日结核对", "PC管理端", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU11","SPU01-SKU12"), (), "客户要求查交易和日结", "保留当前交易SKU", "交易统计是交易类SKU基础能力；电子菜牌版不产生消费订单。"), feature("订单与对账", "分食堂、分档口经营对账", "PC管理端", ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU11","SPU01-SKU12"), ("SPU01-SKU01",), "客户要分别看食堂和档口账", "SPU01-SKU02 多档口多食堂消费结算", "单食堂版只能做基础日结；要分食堂、分档口，升级多档口多食堂版。"), feature("订单与对账", "集团/公司分级汇总", "集团PC管理端", ("SPU01-SKU03",), ("SPU01-SKU02","SPU01-SKU04"), "客户总部要合并看，各公司独立对账", "SPU01-SKU03 集团版消费结算", "多档口版解决经营点位，集团版才解决公司权限和资金归属。"), feature("订单与对账", "异常补单审核与分类财务汇总", "PC管理端", ("SPU01-SKU04",), ("SPU01-SKU02","SPU01-SKU03","SPU01-SKU05","SPU01-SKU06"), "客户要对异常和多场景账务分开审核", "SPU01-SKU04 补贴与多策略结算", "基础统计不等于复杂财务分类，建议升级多策略版。"), feature("设备与运营", "终端绑定、点位配置与在线状态", "消费机/称重台/AI台/菜牌/柜机", ("SPU01-SKU02","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU10","SPU01-SKU11","SPU01-SKU12"), ("SPU01-SKU01","SPU01-SKU03","SPU01-SKU04","SPU01-SKU09"), "客户设备多、需要统一看状态", "按设备场景选择SKU02/05/06/07/08/10/12", "设备管理跟随业务SKU，不单独卖一个万能设备平台。"), feature("设备与运营", "称重餐线设备监控与经营分析", "N450/N460；BP001；余量看板", ("SPU01-SKU05","SPU01-SKU06","SPU01-SKU12"), (), "客户要看称重台状态、菜位和余量", "绑盘选SKU05；免绑选SKU06；健康智厨选SKU12", "称重运营要绑定具体餐线版本，不建议在普通消费版上临时拼接。"), feature("设备与运营", "AI识别日志与模型复盘", "AI识别结算台", ("SPU01-SKU07",), (), "客户担心识别错、要追溯和调优", "SPU01-SKU07 AI视觉识别结算", "AI版不仅识别，还保留纠错和日志，便于持续调优。"), feature("设备与运营", "多屏发布状态与异常重发", "电子菜牌；信息发布盒", ("SPU01-SKU10",), (), "客户要知道每块屏是否发布成功", "SPU01-SKU10 营养菜单与电子菜牌", "普通大屏展示不等于受控发布，电子菜牌SKU才包含屏幕编组、状态和异常重发。"), feature("健康与智厨", "智能烹饪任务与标准化出餐", "TC-40EA智能烹饪机器人；后厨打印机", ("SPU01-SKU12",), (), "客户要求菜谱执行、自动烹饪和标准出餐", "SPU01-SKU12 营养健康智厨专业版", "结算版只管前厅交易；涉及健康测评和智能厨房，应选专业版。"), feature("健康与智厨", "菜品余量与健康复盘", "菜品余量看板；SH-V20；PC/手机端", ("SPU01-SKU12",), ("SPU01-SKU05","SPU01-SKU06"), "客户要把余量、消费和健康一起复盘", "SPU01-SKU12 营养健康智厨专业版", "单独余量看板只解决补餐；要与健康和营养运营结合，升级专业版。"), feature("系统与接口", "公有云部署、备份与基础运维", "阿里云ECS/RDS", ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU08","SPU01-SKU10","SPU01-SKU12"), ("SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU09"), "客户允许公有云并希望快速上线", "选择支持公有云的对应业务SKU", "部署不是一个独立功能按钮，要与客户类型、安全和业务SKU一起确定。"), feature("系统与接口", "私有云、本地或纯内网部署", "本地服务器TS90X；客户私有云资源", ("SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU09","SPU01-SKU11"), ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU08","SPU01-SKU10","SPU01-SKU12"), "客户是国企/政务/封闭内网", "按业务SKU叠加私有/本地部署；纯离线优先SKU11", "部署工作量和安全边界不同，必须单独报价，不能沿用SaaS价格。"), feature("系统与接口", "一卡通、银行、财务、ERP等第三方接口", "接口网关；第三方系统", (), ("SPU01-SKU01","SPU01-SKU02","SPU01-SKU03","SPU01-SKU04","SPU01-SKU05","SPU01-SKU06","SPU01-SKU07","SPU01-SKU08","SPU01-SKU09","SPU01-SKU11","SPU01-SKU12"), "客户提出已有系统必须打通", "先选业务SKU，再单列接口报价", "接口不是默认赠送功能；拿到文档、测试环境和联调人后才能确认价格与周期。"), ] def resolve_lines(catalog, specs): resolved = [] for raw in specs: if raw.get("custom"): item = dict(raw) else: item = catalog_item(catalog, raw["alias"]) item.update({k: v for k, v in raw.items() if k != "alias"}) item.setdefault("attribute", "标配") item.setdefault("include", True) item.setdefault("note", "") resolved.append(item) return resolved def apply_title(ws, title, subtitle, width=11): ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=width) c = ws.cell(1, 1, title) c.fill = PatternFill("solid", fgColor=NAVY) c.font = Font(name="微软雅黑", size=18, bold=True, color=WHITE) c.alignment = Alignment(vertical="center") ws.row_dimensions[1].height = 34 ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=width) c = ws.cell(2, 1, subtitle) c.fill = PatternFill("solid", fgColor=LIGHT_ORANGE) c.font = Font(name="微软雅黑", size=10, color=BLACK) c.alignment = Alignment(wrap_text=True, vertical="center") ws.row_dimensions[2].height = 34 def style_header(ws, row, start, end, fill=BLUE): for col in range(start, end + 1): c = ws.cell(row, col) c.fill = PatternFill("solid", fgColor=fill) c.font = Font(name="微软雅黑", size=10, bold=True, color=WHITE) c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) c.border = Border(top=THIN_GRAY, bottom=THIN_GRAY, left=THIN_GRAY, right=THIN_GRAY) ws.row_dimensions[row].height = 30 def style_body(ws, start_row, end_row, start_col, end_col): for row in ws.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col): for c in row: c.font = Font(name="微软雅黑", size=10, color=BLACK) c.alignment = Alignment(vertical="center", wrap_text=True) c.border = Border(top=THIN_GRAY, bottom=THIN_GRAY, left=THIN_GRAY, right=THIN_GRAY) def set_widths(ws, widths): for col, width in enumerate(widths, 1): ws.column_dimensions[get_column_letter(col)].width = width def setup_print(ws, landscape=True, paper_size="A4"): ws.sheet_view.showGridLines = False ws.page_setup.orientation = "landscape" if landscape else "portrait" ws.page_setup.paperSize = ws.PAPERSIZE_A3 if paper_size == "A3" else ws.PAPERSIZE_A4 ws.page_setup.fitToWidth = 1 ws.page_setup.fitToHeight = 0 ws.sheet_properties.pageSetUpPr.fitToPage = True ws.page_margins.left = 0.25 ws.page_margins.right = 0.25 ws.page_margins.top = 0.45 ws.page_margins.bottom = 0.45 ws.oddFooter.center.text = "CPT｜最终范围、数量、税费与价格以商务审批及合同为准" ws.oddFooter.center.size = 8 def inject_formula_cache(path: Path, cache_maps): """Keep formulas and add cached numeric results for previewers without recalculation.""" with ZipFile(path, "r") as src: members = {name: src.read(name) for name in src.namelist()} for member, cell_values in cache_maps.items(): xml = members[member].decode("utf-8") for cell, value in cell_values.items(): if value is None: replacement = "" else: replacement = str(int(value) if float(value).is_integer() else float(value)) pattern = re.compile(rf'(]*>.*?.*?).*?()') xml, count = pattern.subn(rf'\g<1>{replacement}\g<2>', xml, count=1) if count != 1: raise RuntimeError(f"formula cache target not found: {member} {cell}") members[member] = xml.encode("utf-8") with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False, dir=path.parent) as tmp: tmp_path = Path(tmp.name) try: with ZipFile(tmp_path, "w", ZIP_DEFLATED) as dst: for name, data in members.items(): dst.writestr(name, data) tmp_path.replace(path) finally: if tmp_path.exists(): tmp_path.unlink() def customer_sheet(wb, manifest, spec, items): ws = wb.create_sheet("01-客户报价样板") apply_title(ws, f"{manifest['release_name']}｜产品配置与报价", "SKU标准报价样板；黄色单元格可修改。已定价项小计不包含待商务确认项，不得直接作为最终合同价。", 11) meta = [ ("SKU编码", manifest["release_code"], "报价编号", f"{manifest['release_code']}-Q-20260902"), ("产品名称", manifest["name"], "报价日期", "2026-09-02"), ("客户名称", "待填写", "建议有效期", "30天（可修改）"), ("计价样板", spec["baseline"], "默认部署", manifest["deployment"]), ] for idx, row in enumerate(meta, 4): ws.cell(idx, 1, row[0]); ws.cell(idx, 2, row[1]); ws.merge_cells(start_row=idx, start_column=2, end_row=idx, end_column=5) ws.cell(idx, 6, row[2]); ws.cell(idx, 7, row[3]); ws.merge_cells(start_row=idx, start_column=7, end_row=idx, end_column=11) for c in range(1, 12): ws.cell(idx, c).border = Border(bottom=THIN_GRAY) ws.cell(idx, c).font = Font(name="微软雅黑", size=10, bold=c in {1, 6}, color=BLACK) ws.cell(idx, c).alignment = Alignment(vertical="center", wrap_text=True) ws.cell(idx, 1).fill = PatternFill("solid", fgColor=GRAY) ws.cell(idx, 6).fill = PatternFill("solid", fgColor=GRAY) if row[1] == "待填写": ws.cell(idx, 2).fill = PatternFill("solid", fgColor=PALE_YELLOW) header_row = 9 headers = ["序号", "配置类别", "产品/服务名称", "型号", "配置属性", "数量", "单位", "参考报价单价", "合计", "报价状态", "说明"] for col, val in enumerate(headers, 1): ws.cell(header_row, col, val) style_header(ws, header_row, 1, 11) first = header_row + 1 quote_items = [x for x in items if x.get("include", True)] cache = {} for i, item in enumerate(quote_items, first): ws.cell(i, 1, i - first + 1) ws.cell(i, 2, item["category"]) ws.cell(i, 3, item["name"]) ws.cell(i, 4, item["model"]) ws.cell(i, 5, item["attribute"]) ws.cell(i, 6, item["qty"]) ws.cell(i, 7, item["unit"]) ws.cell(i, 8, item["list_price"]) ws.cell(i, 9, f'=IF(OR(F{i}="",H{i}=""),"",F{i}*H{i})') cache[f"I{i}"] = item["qty"] * item["list_price"] if item["list_price"] is not None else None ws.cell(i, 10, "已定价" if item["list_price"] is not None else "待商务确认") ws.cell(i, 11, item["note"] or item.get("scene", "")) ws.cell(i, 6).fill = PatternFill("solid", fgColor=PALE_YELLOW) if item["list_price"] is None: ws.cell(i, 8).fill = PatternFill("solid", fgColor="FFD9D9") ws.cell(i, 10).font = Font(name="微软雅黑", size=10, bold=True, color=RED) else: ws.cell(i, 8).fill = PatternFill("solid", fgColor=PALE_YELLOW) ws.row_dimensions[i].height = 38 last = first + len(quote_items) - 1 style_body(ws, first, last, 1, 11) total_row = last + 2 ws.merge_cells(start_row=total_row, start_column=1, end_row=total_row, end_column=7) ws.cell(total_row, 1, "已定价项小计（不含待确认项）") ws.cell(total_row, 8, "人民币") ws.cell(total_row, 9, f"=SUM(I{first}:I{last})") cache[f"I{total_row}"] = price_sum(quote_items, "list_price") pending_row = total_row + 1 ws.merge_cells(start_row=pending_row, start_column=1, end_row=pending_row, end_column=8) ws.cell(pending_row, 1, "待商务确认项数量") ws.cell(pending_row, 9, f'=COUNTIF(J{first}:J{last},"待商务确认")') cache[f"I{pending_row}"] = sum(1 for item in quote_items if item["list_price"] is None) final_row = total_row + 2 ws.merge_cells(start_row=final_row, start_column=1, end_row=final_row, end_column=8) ws.cell(final_row, 1, "最终报价（完成配置、税费和审批后填写）") ws.cell(final_row, 9, "待商务确认") ws.cell(final_row, 9).fill = PatternFill("solid", fgColor=PALE_YELLOW) for r in (total_row, pending_row, final_row): for c in range(1, 12): ws.cell(r, c).border = Border(top=MEDIUM_ORANGE if r == total_row else THIN_GRAY, bottom=THIN_GRAY) ws.cell(r, c).font = Font(name="微软雅黑", size=11, bold=True, color=BLACK) ws.cell(r, c).alignment = Alignment(vertical="center", wrap_text=True) notes_row = final_row + 2 ws.merge_cells(start_row=notes_row, start_column=1, end_row=notes_row, end_column=11) ws.cell(notes_row, 1, f"核心购买理由：{manifest['primary_buying_point']}｜主流程：{' → '.join(manifest['main_flow'])}") ws.cell(notes_row, 1).fill = PatternFill("solid", fgColor=LIGHT_ORANGE) ws.cell(notes_row, 1).font = Font(name="微软雅黑", size=10, bold=True, color=BLACK) ws.cell(notes_row, 1).alignment = Alignment(wrap_text=True, vertical="center") ws.row_dimensions[notes_row].height = 34 for row in range(first, last + 1): ws.cell(row, 8).number_format = '¥#,##0.00;[Red](¥#,##0.00);-' ws.cell(row, 9).number_format = '¥#,##0.00;[Red](¥#,##0.00);-' ws.cell(total_row, 9).number_format = '¥#,##0.00;[Red](¥#,##0.00);-' set_widths(ws, [7, 13, 28, 21, 11, 9, 8, 15, 16, 14, 38]) ws.freeze_panes = "A10" ws.auto_filter.ref = f"A{header_row}:K{last}" ws.conditional_formatting.add(f"J{first}:J{last}", CellIsRule(operator="equal", formula=['"待商务确认"'], fill=PatternFill("solid", fgColor="FFD9D9"))) setup_print(ws) return cache def configuration_sheet(wb, manifest, spec, items, optional): ws = wb.create_sheet("02-产品配置清单") apply_title(ws, f"{manifest['release_name']}｜产品配置清单", "先确认客户规模与流程，再调整黄色数量。标配、选配、第三方接口和非标开发必须分开。", 10) info = [ ("购买人", manifest["buyer"]), ("主要买点", manifest["primary_buying_point"]), ("计价样板", spec["baseline"]), ("报价逻辑", spec["logic"]), ("部署方式", manifest["deployment"]), ("主流程", " → ".join(manifest["main_flow"])), ] r = 4 for label, value in info: ws.cell(r, 1, label); ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=10); ws.cell(r, 2, value) ws.cell(r, 1).fill = PatternFill("solid", fgColor=GRAY) for c in range(1, 11): ws.cell(r, c).border = Border(bottom=THIN_GRAY) ws.cell(r, c).font = Font(name="微软雅黑", size=10, bold=c == 1) ws.cell(r, c).alignment = Alignment(vertical="center", wrap_text=True) r += 1 r += 1 ws.cell(r, 1, "核心模块"); ws.cell(r, 2, "功能点") style_header(ws, r, 1, 10, fill=GREEN) module_start = r + 1 for mod in manifest["core_modules"]: ws.cell(r + 1, 1, mod["name"]); ws.merge_cells(start_row=r + 1, start_column=2, end_row=r + 1, end_column=10); ws.cell(r + 1, 2, "；".join(mod["points"])) r += 1 style_body(ws, module_start, r, 1, 10) r += 2 headers = ["序号", "产品/服务名称", "型号", "品牌", "属性", "数量", "单位", "部署/点位", "来源状态", "备注"] for c, value in enumerate(headers, 1): ws.cell(r, c, value) style_header(ws, r, 1, 10) start = r + 1 all_items = items + optional for idx, item in enumerate(all_items, start): ws.cell(idx, 1, idx - start + 1) ws.cell(idx, 2, item["name"]) ws.cell(idx, 3, item["model"]) ws.cell(idx, 4, item["brand"]) ws.cell(idx, 5, item["attribute"]) ws.cell(idx, 6, item["qty"]) ws.cell(idx, 7, item["unit"]) ws.cell(idx, 8, item["category"]) ws.cell(idx, 9, "有价格证据" if item["list_price"] is not None else "价格待确认") ws.cell(idx, 10, item["note"] or item.get("scene", "")) ws.cell(idx, 6).fill = PatternFill("solid", fgColor=PALE_YELLOW) if item["list_price"] is None: ws.cell(idx, 9).font = Font(name="微软雅黑", size=10, bold=True, color=RED) ws.row_dimensions[idx].height = 36 end = start + len(all_items) - 1 style_body(ws, start, end, 1, 10) r = end + 2 ws.cell(r, 1, "产品边界"); ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=10); ws.cell(r, 2, "；".join(manifest["boundaries"])) ws.cell(r, 1).fill = PatternFill("solid", fgColor=GRAY) ws.cell(r + 1, 1, "证据状态"); ws.merge_cells(start_row=r + 1, start_column=2, end_row=r + 1, end_column=10); ws.cell(r + 1, 2, manifest["evidence_status"]) ws.cell(r + 1, 1).fill = PatternFill("solid", fgColor=GRAY) for rr in (r, r + 1): for c in range(1, 11): ws.cell(rr, c).border = Border(top=THIN_GRAY, bottom=THIN_GRAY) ws.cell(rr, c).font = Font(name="微软雅黑", size=10, bold=c == 1) ws.cell(rr, c).alignment = Alignment(wrap_text=True, vertical="center") set_widths(ws, [8, 30, 22, 12, 11, 9, 8, 16, 14, 42]) ws.freeze_panes = f"A{start}" setup_print(ws) def internal_sheet(wb, manifest, items): ws = wb.create_sheet("03-内部测算_严禁外发") apply_title(ws, f"{manifest['release_name']}｜内部价格测算", "内部受控：含直营最低价和渠道价，严禁整表对外发送。价格源为2026-08-20产品清单，使用前必须由商务确认当前有效性。", 12) headers = ["序号", "产品/服务名称", "型号", "数量", "单位", "参考报价单价", "直营最低价", "渠道价", "报价小计", "底价小计", "渠道小计", "价格来源"] row = 5 for c, value in enumerate(headers, 1): ws.cell(row, c, value) style_header(ws, row, 1, 12, fill=RED) start = row + 1 quote_items = [x for x in items if x.get("include", True)] cache = {} for idx, item in enumerate(quote_items, start): ws.cell(idx, 1, idx - start + 1) ws.cell(idx, 2, item["name"]) ws.cell(idx, 3, item["model"]) ws.cell(idx, 4, item["qty"]) ws.cell(idx, 5, item["unit"]) ws.cell(idx, 6, item["list_price"]) ws.cell(idx, 7, item["floor_price"]) ws.cell(idx, 8, item["channel_price"]) ws.cell(idx, 9, f'=IF(OR(D{idx}="",F{idx}=""),"",D{idx}*F{idx})') ws.cell(idx, 10, f'=IF(OR(D{idx}="",G{idx}=""),"",D{idx}*G{idx})') ws.cell(idx, 11, f'=IF(OR(D{idx}="",H{idx}=""),"",D{idx}*H{idx})') cache[f"I{idx}"] = item["qty"] * item["list_price"] if item["list_price"] is not None else None cache[f"J{idx}"] = item["qty"] * item["floor_price"] if item["floor_price"] is not None else None cache[f"K{idx}"] = item["qty"] * item["channel_price"] if item["channel_price"] is not None else None ws.cell(idx, 12, item["source"]) ws.cell(idx, 4).fill = PatternFill("solid", fgColor=PALE_YELLOW) for col in (6, 7, 8): if ws.cell(idx, col).value is None: ws.cell(idx, col).fill = PatternFill("solid", fgColor="FFD9D9") ws.row_dimensions[idx].height = 34 end = start + len(quote_items) - 1 style_body(ws, start, end, 1, 12) total = end + 2 ws.merge_cells(start_row=total, start_column=1, end_row=total, end_column=8) ws.cell(total, 1, "合计") for col in (9, 10, 11): ws.cell(total, col, f"=SUM({get_column_letter(col)}{start}:{get_column_letter(col)}{end})") cache[f"I{total}"] = price_sum(quote_items, "list_price") cache[f"J{total}"] = price_sum(quote_items, "floor_price") cache[f"K{total}"] = price_sum(quote_items, "channel_price") ws.cell(total, 12, "仅汇总已有数字价格；空价项不计入") for c in range(1, 13): ws.cell(total, c).border = Border(top=MEDIUM_ORANGE, bottom=THIN_GRAY) ws.cell(total, c).font = Font(name="微软雅黑", size=11, bold=True) for r in range(start, total + 1): for c in range(6, 12): ws.cell(r, c).number_format = '¥#,##0.00;[Red](¥#,##0.00);-' set_widths(ws, [7, 30, 22, 9, 8, 15, 15, 15, 16, 16, 16, 24]) ws.freeze_panes = f"A{start}" setup_print(ws) return cache def boundary_sheet(wb, manifest, spec): ws = wb.create_sheet("04-报价思路与边界") apply_title(ws, f"{manifest['release_name']}｜报价思路与边界", "销售先卖清楚闭环，再按规模、部署、设备、接口和服务增配。", 4) rows = [ ("购买角色", manifest["buyer"]), ("唯一购买理由", manifest["primary_buying_point"]), ("标准计价样板", spec["baseline"]), ("核心报价逻辑", spec["logic"]), ("主流程", " → ".join(manifest["main_flow"])), ("客户价值", "；".join(manifest["customer_values"])), ("默认部署", manifest["deployment"]), ("标准边界", "；".join(manifest["boundaries"])), ("价格证据", "产品与设备价格来自《产品清单及场景介绍-20260820.xlsx》；空价项不得自行补价。"), ("报价口径", "客户报价样板采用源清单参考单价；直营最低价与渠道价仅用于内部审批，不代表自动可成交价格。"), ("税费口径", "源清单未统一证明含税口径，正式报价前必须确认税率、发票类型和含税/未税。"), ("实施服务", "安装、配置、调试、培训和上线支持；强弱电、土建、网络改造、差旅驻场按合同单列。"), ("维保建议", "源清单记录质保期后维保可按合同总金额5%参考，最终比例、范围和起算时间需商务确认。"), ("客户必填", "客户类型、食堂/公司/学校数、人数、高峰时长、档口/菜位/仓库/监管点位、部署、安全、接口、实施地点。"), ("最终原则", "最终产品、型号、数量、税费、价格、工期、验收和责任边界以审批后的配置清单及合同为准。"), ] r = 4 for label, value in rows: ws.cell(r, 1, label); ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4); ws.cell(r, 2, value) ws.cell(r, 1).fill = PatternFill("solid", fgColor=GRAY) for c in range(1, 5): ws.cell(r, c).border = Border(top=THIN_GRAY, bottom=THIN_GRAY, left=THIN_GRAY, right=THIN_GRAY) ws.cell(r, c).font = Font(name="微软雅黑", size=10, bold=c == 1, color=BLACK) ws.cell(r, c).alignment = Alignment(wrap_text=True, vertical="center") ws.row_dimensions[r].height = 42 r += 1 set_widths(ws, [18, 38, 38, 38]) setup_print(ws, landscape=False) def build_one(out_path, manifest, spec, catalog): items = resolve_lines(catalog, spec["items"]) optional = resolve_lines(catalog, spec.get("optional", [])) wb = Workbook() wb.remove(wb.active) customer_cache = customer_sheet(wb, manifest, spec, items) configuration_sheet(wb, manifest, spec, items, optional) internal_cache = internal_sheet(wb, manifest, items) boundary_sheet(wb, manifest, spec) wb.calculation.calcMode = "auto" wb.calculation.fullCalcOnLoad = True wb.calculation.forceFullCalc = True wb.properties.title = f"{manifest['release_name']} 产品配置与报价" wb.properties.subject = "CPT智慧食堂SKU配置报价样板" wb.properties.creator = "康比特数字体育科技" out_path.parent.mkdir(parents=True, exist_ok=True) wb.save(out_path) inject_formula_cache( out_path, { "xl/worksheets/sheet1.xml": customer_cache, "xl/worksheets/sheet3.xml": internal_cache, }, ) return items, optional def price_sum(items, key): return sum((x.get(key) or 0) * (x.get("qty") or 0) for x in items if x.get("include", True)) def build_overview(out_path, manifests, results): wb = Workbook() ws = wb.active ws.title = "01-SKU报价总览" apply_title(ws, "智慧食堂18个SKU｜配置与报价总览", "总览用于横向比较；每个SKU的具体配置、客户报价、内部底价和边界请打开对应独立Excel。", 12) headers = ["序号", "SPU", "SKU编码", "SKU名称", "标准计价样板", "参考报价小计", "直营底价小计", "渠道价小计", "待确认项数", "默认部署", "核心报价思路", "独立报价文件"] row = 5 for c, value in enumerate(headers, 1): ws.cell(row, c, value) style_header(ws, row, 1, 12) summary_cache = {} for i, manifest in enumerate(manifests, row + 1): code = manifest["release_code"] spec = SPECS[code] filename = f"{manifest['release_name']}_产品配置与报价.xlsx" vals = [f"{i-row:02d}", manifest["spu"], code, manifest["name"], spec["baseline"], None, None, None, None, manifest["deployment"], spec["logic"], filename] for c, value in enumerate(vals, 1): ws.cell(i, c, value) detail = "02-配置价格明细" ws.cell(i, 6, f'=SUMIFS(\'{detail}\'!$J:$J,\'{detail}\'!$A:$A,$C{i})') ws.cell(i, 7, f'=SUMIFS(\'{detail}\'!$K:$K,\'{detail}\'!$A:$A,$C{i})') ws.cell(i, 8, f'=SUMIFS(\'{detail}\'!$M:$M,\'{detail}\'!$A:$A,$C{i})') ws.cell(i, 9, f'=COUNTIFS(\'{detail}\'!$A:$A,$C{i},\'{detail}\'!$N:$N,"待商务确认")') items = results[code]["items"] summary_cache[f"F{i}"] = price_sum(items, "list_price") summary_cache[f"G{i}"] = price_sum(items, "floor_price") summary_cache[f"H{i}"] = price_sum(items, "channel_price") summary_cache[f"I{i}"] = sum(1 for x in items if x.get("include", True) and x.get("list_price") is None) for c in range(1, 13): ws.cell(i, c).alignment = Alignment(vertical="center", wrap_text=True) for c in (6, 7, 8): ws.cell(i, c).number_format = '¥#,##0.00;[Red](¥#,##0.00);-' ws.row_dimensions[i].height = 54 style_body(ws, row + 1, row + len(manifests), 1, 12) set_widths(ws, [7, 13, 15, 24, 38, 16, 16, 16, 13, 22, 45, 46]) ws.freeze_panes = "A6" ws.auto_filter.ref = f"A5:L{5+len(manifests)}" setup_print(ws) dws = wb.create_sheet("02-配置价格明细") apply_title(dws, "18个SKU配置价格明细", "内部受控价格底表。报价总览通过SUMIFS自动汇总本页。", 14) headers2 = ["SKU编码", "行号", "类别", "产品/服务名称", "型号", "数量", "单位", "参考单价", "直营最低价", "参考小计", "底价小计", "渠道价", "渠道小计", "报价状态"] for c, value in enumerate(headers2, 1): dws.cell(5, c, value) style_header(dws, 5, 1, 14, fill=RED) r = 6 detail_cache = {} for manifest in manifests: code = manifest["release_code"] for seq, item in enumerate(results[code]["items"], 1): vals = [code, seq, item["category"], item["name"], item["model"], item["qty"], item["unit"], item["list_price"], item["floor_price"], None, None, item["channel_price"], None, "已定价" if item["list_price"] is not None else "待商务确认"] for c, value in enumerate(vals, 1): dws.cell(r, c, value) dws.cell(r, 10, f'=IF(OR(F{r}="",H{r}=""),"",F{r}*H{r})') dws.cell(r, 11, f'=IF(OR(F{r}="",I{r}=""),"",F{r}*I{r})') dws.cell(r, 13, f'=IF(OR(F{r}="",L{r}=""),"",F{r}*L{r})') detail_cache[f"J{r}"] = item["qty"] * item["list_price"] if item["list_price"] is not None else None detail_cache[f"K{r}"] = item["qty"] * item["floor_price"] if item["floor_price"] is not None else None detail_cache[f"M{r}"] = item["qty"] * item["channel_price"] if item["channel_price"] is not None else None r += 1 style_body(dws, 6, r - 1, 1, 14) for rr in range(6, r): for c in (8, 9, 10, 11, 12, 13): dws.cell(rr, c).number_format = '¥#,##0.00;[Red](¥#,##0.00);-' set_widths(dws, [15, 8, 14, 30, 22, 9, 8, 15, 15, 16, 16, 15, 16, 14]) dws.freeze_panes = "A6" dws.auto_filter.ref = f"A5:N{r-1}" setup_print(dws) pws = wb.create_sheet("03-报价原则") apply_title(pws, "智慧食堂18个SKU｜报价原则", "本页是销售与商务统一口径，不替代项目审批和合同。", 4) principles = [ ("1. 先冻结场景", "先确认客户类型、组织/食堂数、人数与高峰、档口/菜位/仓库/监管点位，再选SKU。"), ("2. 软件按责任边界", "单食堂、多食堂、集团/多公司是不同管理责任和实施工作量，必须拆分报价。"), ("3. 部署强绑定", "公有云、私有云、本地、纯离线、政务云、高可用分别核算服务器、软件环境、安全和实施。"), ("4. 硬件按点位", "消费机按档口，称重台按菜位，摄像头按监管点位，收货秤按验收点，PDA按仓库配置。"), ("5. 接口单列", "银行、一卡通、ERP、财务、教务、闸机、柜机等接口取得文档和测试环境后评估。"), ("6. 非标单列", "现有产品不具备的功能必须完成产品/研发评估，再报价、排期和定义验收。"), ("7. 价格证据", "参考报价、直营最低价、渠道价来自2026-08-20产品清单；空价项保持待确认。"), ("8. 商务控制", "对外先报参考价；折扣不得自动突破内部控制线，最终由商务审批。"), ("9. 税费与维保", "税费、发票、质保、维保起算和比例必须在正式报价中写清。"), ("10. 合同一致", "报价、配置、合同、交付和验收保持同一产品名称、型号、数量和责任边界。"), ] rr = 4 for title, body in principles: pws.cell(rr, 1, title); pws.merge_cells(start_row=rr, start_column=2, end_row=rr, end_column=4); pws.cell(rr, 2, body) pws.cell(rr, 1).fill = PatternFill("solid", fgColor=GRAY) for c in range(1, 5): pws.cell(rr, c).border = Border(top=THIN_GRAY, bottom=THIN_GRAY, left=THIN_GRAY, right=THIN_GRAY) pws.cell(rr, c).font = Font(name="微软雅黑", size=10, bold=c == 1) pws.cell(rr, c).alignment = Alignment(wrap_text=True, vertical="center") pws.row_dimensions[rr].height = 44 rr += 1 set_widths(pws, [20, 40, 40, 40]) setup_print(pws, landscape=False) wb.calculation.calcMode = "auto" wb.calculation.fullCalcOnLoad = True wb.calculation.forceFullCalc = True wb.save(out_path) inject_formula_cache( out_path, { "xl/worksheets/sheet1.xml": summary_cache, "xl/worksheets/sheet2.xml": detail_cache, }, ) def build_html(out_path, manifests, results): cards = [] for manifest in manifests: code = manifest["release_code"] items = results[code]["items"] public = price_sum(items, "list_price") floor = price_sum(items, "floor_price") pending = sum(1 for x in items if x.get("include", True) and x.get("list_price") is None) cards.append( f"""
{html.escape(manifest['spu'])}

{html.escape(code)} · {html.escape(manifest['name'])}

{html.escape(manifest['primary_buying_point'])}

计价样板
{html.escape(SPECS[code]['baseline'])}
参考报价小计
¥{public:,.0f}
内部底价小计
¥{floor:,.0f}
待确认项
{pending} 项

{html.escape(SPECS[code]['logic'])}

""" ) body = f"""智慧食堂18个SKU配置报价思路
CPT · SMART CANTEEN

18个SKU配置与报价思路

每个SKU采用“软件授权 + 必要硬件 + 部署资源 + 实施服务”的最小闭环；设备按点位扩容，接口与非标单列。页面金额仅汇总已有价格证据，空价项不计入。

报价统一思路

  1. 先选SKU，再谈设备数量
  2. 单食堂、多食堂、集团分别计价
  3. 部署方式与价格强绑定
  4. 硬件按档口、菜位、仓库和监管点位计价
  5. 接口取得文档后单列
  6. 未知价格保持待确认
  7. 内部底价和渠道价严禁外发
  8. 最终以审批配置和合同为准
{''.join(cards)}
""" out_path.write_text(body, encoding="utf-8") def build_front_function_matrix(out_path, manifests, results): manifest_map = {m["release_code"]: m for m in manifests} wb = Workbook() ws = wb.active ws.title = "01-PC功能模块对比" apply_title(ws, "前厅12个SKU｜PC端功能模块对比与选配指引", "✓ 标配：该SKU主流程内默认具备；○ 选配：需叠加模块、硬件、接口或专项评估；—：不建议在该SKU内销售。", 18) ws.merge_cells("A4:C4"); ws["A4"] = "功能与硬件" ws.merge_cells("D4:O4"); ws["D4"] = "前厅营养结算12个SKU" ws.merge_cells("P4:R4"); ws["P4"] = "销售选型与升级推荐" for c in range(1, 19): ws.cell(4, c).fill = PatternFill("solid", fgColor=NAVY) ws.cell(4, c).font = Font(name="微软雅黑", size=11, bold=True, color=WHITE) ws.cell(4, c).alignment = Alignment(horizontal="center", vertical="center") headers = ["一级模块", "PC端二级功能点", "对应硬件/设备"] for code in FRONT_CODES: headers.append(f"{code}\n{manifest_map[code]['name']}") headers += ["客户触发问题", "优先推荐", "销售推荐话术"] for c, value in enumerate(headers, 1): ws.cell(5, c, value) style_header(ws, 5, 1, 18) start = 6 for r, feat in enumerate(PC_FEATURES, start): ws.cell(r, 1, feat["l1"]) ws.cell(r, 2, feat["l2"]) ws.cell(r, 3, feat["hardware"]) for idx, code in enumerate(FRONT_CODES, 4): if code in feat["std"]: value, color, fill = "✓ 标配", GREEN, "EAF7F2" elif code in feat["opt"]: value, color, fill = "○ 选配", ORANGE, "FFF1DF" else: value, color, fill = "—", "8A969E", LIGHT_GRAY ws.cell(r, idx, value) ws.cell(r, idx).font = Font(name="微软雅黑", size=9, bold=value != "—", color=color) ws.cell(r, idx).fill = PatternFill("solid", fgColor=fill) ws.cell(r, idx).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.cell(r, 16, feat["trigger"]) ws.cell(r, 17, feat["recommend"]) ws.cell(r, 18, feat["talk"]) ws.row_dimensions[r].height = 54 end = start + len(PC_FEATURES) - 1 style_body(ws, start, end, 1, 3) style_body(ws, start, end, 16, 18) for r in range(start, end + 1): for c in range(4, 16): ws.cell(r, c).border = Border(top=THIN_GRAY, bottom=THIN_GRAY, left=THIN_GRAY, right=THIN_GRAY) set_widths(ws, [15, 31, 30] + [14] * 12 + [28, 28, 46]) ws.freeze_panes = "D6" ws.auto_filter.ref = f"A5:R{end}" ws.sheet_view.zoomScale = 70 setup_print(ws, paper_size="A3") hws = wb.create_sheet("02-SKU硬件对应") apply_title(hws, "前厅12个SKU｜硬件对应与配置依据", "硬件数量为标准计价样板，最终按档口、菜位、入口、屏幕和高峰吞吐调整。", 9) hheaders = ["序号", "SKU编码", "SKU名称", "标准硬件", "选配硬件", "默认部署", "数量配置依据", "价格缺口", "销售提醒"] for c, value in enumerate(hheaders, 1): hws.cell(5, c, value) style_header(hws, 5, 1, 9) software_words = ("软件", "平台", "移动端", "云服务", "部署", "实施", "授权", "接口", "配置", "服务", "专项") for r, code in enumerate(FRONT_CODES, 6): manifest = manifest_map[code] standard_hw = [x for x in results[code]["items"] if not any(word in x["category"] for word in software_words) and x.get("include", True)] optional_hw = [x for x in results[code]["optional"] if not any(word in x["category"] for word in software_words)] fmt = lambda x: f"{x['qty']}{x['unit']}×{x['name']}（{x['model']}）" missing = [x["name"] for x in results[code]["items"] if x.get("include", True) and x.get("list_price") is None] vals = [ f"{r-5:02d}", code, manifest["name"], "；".join(fmt(x) for x in standard_hw) or "无专用硬件，使用客户现有PC/终端", "；".join(fmt(x) for x in optional_hw) or "按项目需求选配", manifest["deployment"], SPECS[code]["baseline"], "；".join(missing) if missing else "标准配置均有价格证据", "先确认业务流程，再按点位与吞吐核数量；接口、施工和非标不含在硬件单价内。", ] for c, value in enumerate(vals, 1): hws.cell(r, c, value) hws.cell(r, c).alignment = Alignment(vertical="center", wrap_text=True) if missing: hws.cell(r, 8).font = Font(name="微软雅黑", size=10, bold=True, color=RED) hws.row_dimensions[r].height = 82 style_body(hws, 6, 17, 1, 9) set_widths(hws, [8, 15, 25, 52, 45, 23, 42, 32, 45]) hws.freeze_panes = "A6" hws.auto_filter.ref = "A5:I17" hws.sheet_view.zoomScale = 80 setup_print(hws, paper_size="A3") tws = wb.create_sheet("03-销售升级话术") apply_title(tws, "前厅12个SKU｜客户异议与升级推荐话术", "销售不要回答“这个版本也能做”；先承认版本边界，再把客户需求导向正确SKU或选配项。", 7) theaders = ["序号", "客户常见问题/触发需求", "基础版本为什么没有", "优先推荐", "一句话推荐", "需要补充确认", "禁止承诺"] for c, value in enumerate(theaders, 1): tws.cell(5, c, value) style_header(tws, 5, 1, 7, fill=ORANGE) seen = set() rows = [] for feat in PC_FEATURES: key = (feat["trigger"], feat["recommend"], feat["talk"]) if not feat["trigger"] or key in seen: continue seen.add(key) rows.append(feat) for r, feat in enumerate(rows, 6): recommended_codes = [code for code in FRONT_CODES if code in feat["std"]] boundary = "；".join(manifest_map[recommended_codes[0]]["boundaries"]) if recommended_codes else "按接口、部署和项目范围确认" vals = [ r - 5, feat["trigger"], f"极简/基础SKU只保留其主流程，不把“{feat['l2']}”默认塞进所有版本。", feat["recommend"], feat["talk"], "客户类型、组织/食堂数、人数与高峰、部署、设备点位、接口和验收口径。", boundary, ] for c, value in enumerate(vals, 1): tws.cell(r, c, value) tws.cell(r, c).alignment = Alignment(vertical="center", wrap_text=True) tws.row_dimensions[r].height = 74 style_body(tws, 6, 5 + len(rows), 1, 7) set_widths(tws, [8, 34, 38, 32, 48, 46, 46]) tws.freeze_panes = "A6" tws.auto_filter.ref = f"A5:G{5+len(rows)}" setup_print(tws, paper_size="A3") wb.properties.title = "前厅12个SKU PC功能模块对比与选配指引" wb.properties.subject = "CPT智慧食堂销售选型" wb.properties.creator = "康比特数字体育科技" wb.save(out_path) def main(): parser = argparse.ArgumentParser() parser.add_argument("--source", type=Path, required=True) parser.add_argument("--manifests", type=Path, required=True) parser.add_argument("--output", type=Path, required=True) args = parser.parse_args() catalog = load_catalog(args.source) manifests = [json.loads(p.read_text(encoding="utf-8")) for p in sorted(args.manifests.glob("*.json"))] if len(manifests) != 18: raise SystemExit(f"expected 18 manifests, got {len(manifests)}") quote_dir = args.output / "01-18个SKU独立报价单" overview_dir = args.output / "02-总览与报价思路" process_dir = args.output / "03-内部过程-可忽略" quote_dir.mkdir(parents=True, exist_ok=True) overview_dir.mkdir(parents=True, exist_ok=True) process_dir.mkdir(parents=True, exist_ok=True) results = {} for manifest in manifests: code = manifest["release_code"] if code not in SPECS: raise KeyError(f"missing quote spec: {code}") filename = f"{manifest['release_name']}_产品配置与报价.xlsx" items, optional = build_one(quote_dir / filename, manifest, SPECS[code], catalog) results[code] = {"items": items, "optional": optional, "filename": filename} build_overview(overview_dir / "智慧食堂18个SKU配置报价总览.xlsx", manifests, results) build_html(overview_dir / "smart-canteen-18-sku-quote-strategy.html", manifests, results) build_front_function_matrix(overview_dir / "智慧食堂前厅12个SKU_PC功能模块对比与选配指引.xlsx", manifests, results) with ZipFile(overview_dir / "智慧食堂18个SKU独立报价单.zip", "w", ZIP_DEFLATED) as zf: for p in sorted(quote_dir.glob("*.xlsx")): zf.write(p, p.name) audit = { "source": str(args.source), "manifest_count": len(manifests), "workbook_count": len(list(quote_dir.glob("*.xlsx"))), "front_pc_function_matrix": str(overview_dir / "智慧食堂前厅12个SKU_PC功能模块对比与选配指引.xlsx"), "rules": [ "公开报价采用源清单参考单价", "直营最低价和渠道价仅放内部受控页", "无价格证据的项目保持空价待确认", "标准样板数量可修改，不代表项目固定BOM", ], "items": [ { "sku": m["release_code"], "name": m["name"], "file": results[m["release_code"]]["filename"], "priced_subtotal": price_sum(results[m["release_code"]]["items"], "list_price"), "floor_subtotal": price_sum(results[m["release_code"]]["items"], "floor_price"), "channel_subtotal": price_sum(results[m["release_code"]]["items"], "channel_price"), "pending_count": sum(1 for x in results[m["release_code"]]["items"] if x.get("include", True) and x.get("list_price") is None), } for m in manifests ], } (process_dir / "build-audit.json").write_text(json.dumps(audit, ensure_ascii=False, indent=2), encoding="utf-8") print(json.dumps({"output": str(args.output), "quote_count": 18, "overview": str(overview_dir / '智慧食堂18个SKU配置报价总览.xlsx')}, ensure_ascii=False)) if __name__ == "__main__": main()