#!/usr/bin/env python3 """Build a simple three-version sales training pack from twelve front-office SKUs.""" from __future__ import annotations import argparse import csv import html import json import re import tempfile from pathlib import Path from zipfile import ZIP_DEFLATED, ZipFile from openpyxl import Workbook, load_workbook from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter NAVY = "14354B" ORANGE = "F57C00" AMBER = "FFA51F" GREEN = "218C74" BLUE = "0B79B7" RED = "C0392B" WHITE = "FFFFFF" BLACK = "222222" GRAY = "E8EEF2" LIGHT_ORANGE = "FFF1DF" LIGHT_GRAY = "F5F7F9" THIN = Side(style="thin", color="CAD4DB") FEATURES = [ ("基础经营", "组织、人员与权限", "✓ 标配", "✓ 标配", "✓ 标配"), ("基础经营", "账户、充值与补贴", "✓ 标配", "✓ 标配", "✓ 标配"), ("基础经营", "刷卡/刷码/人脸消费入口", "✓ 标配", "✓ 标配", "✓ 标配"), ("基础经营", "定额、按次与金额消费", "✓ 标配", "✓ 标配", "✓ 标配"), ("基础经营", "订单查询与统计对账", "✓ 标配", "✓ 标配", "✓ 标配"), ("组织配置", "多食堂/多档口/集团权限", "○ 场景配置", "○ 场景配置", "○ 场景配置"), ("行业配置", "K12家长、学生与学校对账", "○ K12子包", "○ K12+营养", "○ 按项目"), ("部署配置", "公有云/私有云/本地/纯内网", "○ 单独报价", "○ 单独报价", "○ 单独报价"), ("营养运营", "菜品、菜单、价格与营养标签", "—", "✓ 标配", "✓ 标配"), ("营养运营", "个人餐食记录与营养分析", "—", "✓ 标配", "✓ 标配"), ("智能餐线", "绑盘称重结算", "—", "○ 餐线包", "○ 餐线包"), ("智能餐线", "免绑盘称重结算", "—", "○ 餐线包", "○ 餐线包"), ("智能餐线", "AI菜品识别结算", "—", "○ 餐线包", "○ 餐线包"), ("订餐取餐", "移动订餐、厨房分单与核销", "—", "○ 订餐包", "○ 订餐包"), ("展示运营", "电子菜牌与菜品余量", "—", "○ 展示包", "○ 展示包"), ("健康服务", "健康档案与历史测评", "—", "—", "✓ 标配"), ("健康服务", "身高体重、BMI、血压与体脂", "—", "—", "✓ 标配"), ("健康服务", "营养目标、个性化建议与人群分析", "—", "—", "✓ 标配"), ("智能厨房", "智能烹饪任务与标准化出餐", "—", "—", "○ 智厨包"), ("持续运营", "余量、餐食与健康复盘", "—", "○ 经营分析", "✓ 标配") ] TALKS = [ ("我们只要刷卡消费，不需要营养。", "智能结算基础版", "基础版就是为人员、账户、补贴、消费和对账设计的，不必为暂时不用的营养功能付费。"), ("为什么基础版没有称重？", "营养结算标准版", "称重不是一个开关，而是一套餐线设备和营养结算流程；需要按克取餐就升级标准版并选择称重餐线包。"), ("为什么基础版没有营养分析？", "营养结算标准版", "基础版先把钱算清楚；如果要把消费数据转成菜品和个人营养服务，就升级营养结算标准版。"), ("我们要AI识别菜品。", "营养结算标准版 + AI餐线包", "AI识别属于标准版的智能餐线选配，需要根据菜品、餐具、光照和通道做真实测试。"), ("我们要线上订餐和取餐柜。", "营养结算标准版 + 订餐取餐包", "移动订餐、厨房分单、叫号和取餐柜是一条完整闭环，建议选择标准版并叠加订餐取餐硬件。"), ("我们只想上电子菜牌。", "营养结算标准版轻量配置", "可以只选择标准版的菜单营养与电子菜牌配置，不需要同时购买称重和AI餐线。"), ("标准版为什么没有健康档案？", "营养健康专业版", "标准版解决吃了什么；专业版才把健康测评、营养目标和持续复盘连接起来。"), ("我们要体重体脂、血压和健康报告。", "营养健康专业版", "这属于健康测评和持续营养服务，应选择专业版并配置SH-V20等健康设备。"), ("我们要智能烹饪机器人。", "营养健康专业版 + 智厨包", "机器人不是普通结算硬件，需要与菜谱、厨房任务、供电和给排水一起评估。"), ("我们有多个食堂或集团公司。", "先选功能版本，再选多食堂/集团子包", "基础、标准、专业解决功能深度；多食堂、集团和多公司决定组织权限、实施和价格，两者要组合选择。"), ("我们是K12，要家长缴费。", "智能结算基础版 + K12子包", "家长、学生、学校和银行入口属于K12业务子包；如还要营养公示，再叠加标准版能力。"), ("我们要求政务云或本地部署。", "三版本均可叠加部署包", "部署方式不决定功能版本，但会改变服务器、安全、实施和运维工作量，需要单独报价。") ] def title(ws, text, subtitle, cols): ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=cols) ws.cell(1, 1, text) ws.cell(1, 1).fill = PatternFill("solid", fgColor=NAVY) ws.cell(1, 1).font = Font(name="微软雅黑", size=18, bold=True, color=WHITE) ws.cell(1, 1).alignment = Alignment(vertical="center") ws.row_dimensions[1].height = 34 ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=cols) ws.cell(2, 1, subtitle) ws.cell(2, 1).fill = PatternFill("solid", fgColor=LIGHT_ORANGE) ws.cell(2, 1).font = Font(name="微软雅黑", size=10, color=BLACK) ws.cell(2, 1).alignment = Alignment(wrap_text=True, vertical="center") ws.row_dimensions[2].height = 32 def header(ws, row, values, fill=BLUE): for c, value in enumerate(values, 1): cell = ws.cell(row, c, value) cell.fill = PatternFill("solid", fgColor=fill) cell.font = Font(name="微软雅黑", size=10, bold=True, color=WHITE) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.border = Border(top=THIN, bottom=THIN, left=THIN, right=THIN) ws.row_dimensions[row].height = 30 def body(ws, first, last, cols): for row in ws.iter_rows(min_row=first, max_row=last, min_col=1, max_col=cols): for cell in row: cell.font = Font(name="微软雅黑", size=10, color=BLACK) cell.alignment = Alignment(vertical="center", wrap_text=True) cell.border = Border(top=THIN, bottom=THIN, left=THIN, right=THIN) def widths(ws, values): for c, value in enumerate(values, 1): ws.column_dimensions[get_column_letter(c)].width = value def setup(ws, a3=False): ws.sheet_view.showGridLines = False ws.page_setup.orientation = "landscape" ws.page_setup.paperSize = ws.PAPERSIZE_A3 if 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_cache(path, cache): with ZipFile(path, "r") as source: members = {name: source.read(name) for name in source.namelist()} xml = members["xl/worksheets/sheet1.xml"].decode("utf-8") for ref, value in cache.items(): pattern = re.compile(rf'(]*>.*?.*?).*?()') xml, count = pattern.subn(rf'\g<1>{value}\g<2>', xml, count=1) if count != 1: raise RuntimeError(f"cache target missing: {ref}") members["xl/worksheets/sheet1.xml"] = xml.encode("utf-8") with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False, dir=Path(path).parent) as tmp: temp = Path(tmp.name) try: with ZipFile(temp, "w", ZIP_DEFLATED) as target: for name, data in members.items(): target.writestr(name, data) temp.replace(path) finally: if temp.exists(): temp.unlink() def build_master(model, path): wb = Workbook() ws = wb.active ws.title = "01-三版本总览" title(ws, "智慧食堂前厅｜三版本销售培训总览", model["principle"], 10) headers = ["序号", "销售版本", "一句记忆", "一句话产品", "购买人", "主流程", "归档子SKU数", "主要硬件", "不包含", "销售动作"] header(ws, 4, headers, fill=ORANGE) cache = {} for r, version in enumerate(model["versions"], 5): hardware = "；".join(f"{x['name']}（{x['model']}）" for x in version["hardware"][:5]) vals = [version["sequence"], version["name"], version["slogan"], version["one_line"], version["buyer"], " → ".join(version["main_flow"]), None, hardware, "；".join(version["boundaries"][:3]), "先讲客户结果，再选子SKU、部署和硬件"] for c, value in enumerate(vals, 1): ws.cell(r, c, value) ws.cell(r, 7, f'=COUNTIF(\'02-12SKU归档\'!$D:$D,B{r})') cache[f"G{r}"] = len(version["source_skus"]) ws.row_dimensions[r].height = 86 body(ws, 5, 7, 10); widths(ws, [7, 22, 18, 42, 34, 42, 14, 58, 48, 38]); ws.freeze_panes = "A5"; setup(ws, a3=True) mws = wb.create_sheet("02-12SKU归档") title(mws, "原12个业务SKU归档到三版本", "原SKU保留为内部场景子包，继续承接组织规模、餐线、行业和部署的配置与报价差异。", 7) mh = ["序号", "原SKU编码", "原SKU名称", "归档销售版本", "子包角色", "销售使用方式", "备注"] header(mws, 4, mh) for r, item in enumerate(model["source_sku_mapping"], 5): vals = [f"{r-4:02d}", item["code"], item["name"], item["archive"], item["role"], "客户不看复杂编码；销售内部据此选配置和报价", "三版本是功能深度，原SKU是业务场景"] for c, value in enumerate(vals, 1): mws.cell(r, c, value) mws.row_dimensions[r].height = 48 body(mws, 5, 16, 7); widths(mws, [7, 16, 27, 24, 28, 48, 36]); mws.freeze_panes = "A5"; mws.auto_filter.ref = "A4:G16"; setup(mws, a3=True) fws = wb.create_sheet("03-版本功能差异") title(fws, "三版本功能差异｜一眼完成升级推荐", "基础版把钱算清楚，标准版把饭吃明白，专业版把健康管起来。", 7) fh = ["一级模块", "功能点", "智能结算基础版", "营养结算标准版", "营养健康专业版", "对应硬件", "升级建议"] header(fws, 4, fh, fill=GREEN) hardware_map = { "刷卡/刷码/人脸消费入口":"消费机、发卡器、M1卡", "绑盘称重结算":"N450D、BP001、智能托盘", "免绑盘称重结算":"N460", "AI菜品识别结算":"S20A/S30A/S40A", "移动订餐、厨房分单与核销":"手机端、打印机、叫号屏、取餐柜", "电子菜牌与菜品余量":"NE540/EM43、NY43", "身高体重、BMI、血压与体脂":"SH-V20", "智能烹饪任务与标准化出餐":"TC-40EA、后厨打印机" } for r, feat in enumerate(FEATURES, 5): l1, name, basic, standard, pro = feat if basic == "—" and standard != "—": upgrade = "需要该能力：基础版升级标准版" elif standard == "—" and pro != "—": upgrade = "需要该能力：升级专业版" else: upgrade = "按业务子包、部署或硬件配置" vals = [l1, name, basic, standard, pro, hardware_map.get(name, "PC管理端/对应业务终端"), upgrade] for c, value in enumerate(vals, 1): fws.cell(r, c, value) fws.row_dimensions[r].height = 46 body(fws, 5, 24, 7) for r in range(5, 25): for c in range(3, 6): value = fws.cell(r, c).value color = GREEN if str(value).startswith("✓") else ORANGE if str(value).startswith("○") else "87949C" fill = "EAF7F2" if str(value).startswith("✓") else "FFF1DF" if str(value).startswith("○") else LIGHT_GRAY fws.cell(r, c).font = Font(name="微软雅黑", size=10, bold=value != "—", color=color) fws.cell(r, c).fill = PatternFill("solid", fgColor=fill) fws.cell(r, c).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) widths(fws, [16, 32, 19, 19, 19, 38, 42]); fws.freeze_panes = "C5"; setup(fws, a3=True) hws = wb.create_sheet("04-硬件对照") title(hws, "三版本对应硬件选型", "硬件不是三档全部打包；按客户餐线、入口、菜位、展示和部署条件选择。", 7) hh = ["销售版本", "属性", "硬件名称", "型号", "主要用途", "什么时候选", "报价提醒"] header(hws, 4, hh, fill=ORANGE) r = 5 for version in model["versions"]: for item in version["hardware"]: vals = [version["name"], item["attribute"], item["name"], item["model"], item["use"], "客户明确需要该入口/餐线/部署时", "按型号、数量、点位、安装和接口单独报价"] for c, value in enumerate(vals, 1): hws.cell(r, c, value) hws.row_dimensions[r].height = 44 r += 1 body(hws, 5, r-1, 7); widths(hws, [23, 18, 34, 26, 44, 40, 44]); hws.freeze_panes = "A5"; hws.auto_filter.ref = f"A4:G{r-1}"; setup(hws, a3=True) tws = wb.create_sheet("05-销售升级话术") title(tws, "销售常见问题与升级话术", "先承认当前版本边界，再告诉客户应升级哪个版本或叠加哪个场景包。", 5) th = ["客户问题", "推荐版本/组合", "销售回答", "需要确认", "禁止说法"] header(tws, 4, th, fill=RED) for r, item in enumerate(TALKS, 5): vals = [item[0], item[1], item[2], "客户规模、组织、餐线、部署、硬件点位和接口", "这个功能所有版本都能免费打开"] for c, value in enumerate(vals, 1): tws.cell(r, c, value) tws.row_dimensions[r].height = 70 body(tws, 5, 16, 5); widths(tws, [38, 34, 70, 48, 38]); tws.freeze_panes = "A5"; setup(tws, a3=True) cws = wb.create_sheet("06-三版本客户买点") title(cws, "三版本客户场景、人群、需求与买点", "销售先判断客户，再讲功能：谁在什么场景遇到什么问题，为什么愿意为这个版本买单。", 8) ch = ["销售版本", "典型客户场景", "人群特点", "客户核心需求", "管理/决策买点", "最终用户买点", "第一句怎么问", "推荐原则"] header(cws, 4, ch, fill=GREEN) questions = { "智能结算基础版": "您现在最头疼的是人员账户、补贴规则、消费效率，还是财务对账？", "营养结算标准版": "除了结算，您是否还需要称重/AI餐线、移动订餐或营养展示？", "营养健康专业版": "您希望做一次健康展示，还是要形成持续的测评、营养干预和复盘？", } for r, version in enumerate(model["versions"], 5): vals = [version["name"], "；".join(version["customer_scenarios"]), "；".join(version["population_traits"]), "；".join(version["customer_needs"]), "；".join(version["decision_buying_points"]), "；".join(version["user_buying_points"]), questions[version["name"]], "先选功能深度，再选组织/餐线/部署/硬件子包"] for c, value in enumerate(vals, 1): cws.cell(r, c, value) cws.row_dimensions[r].height = 135 body(cws, 5, 7, 8); widths(cws, [23, 48, 48, 48, 44, 42, 48, 38]); cws.freeze_panes = "A5"; setup(cws, a3=True) sws = wb.create_sheet("07-12SKU场景买点") title(sws, "原12个业务SKU｜客户场景与购买理由", "三版本用于首次培训；需要具体配置和报价时，再回到本表选择业务子SKU。", 9) sh = ["序号", "原SKU编码", "原SKU名称", "归档版本", "典型客户场景", "人群特点", "客户需求", "客户购买理由", "培训提示"] header(sws, 4, sh, fill=ORANGE) for r, item in enumerate(model["source_sku_mapping"], 5): vals = [f"{r-4:02d}", item["code"], item["name"], item["archive"], item["scenario"], item["people"], item["need"], item["buying_point"], "客户不看编码；销售按场景选子包"] for c, value in enumerate(vals, 1): sws.cell(r, c, value) sws.row_dimensions[r].height = 72 body(sws, 5, 16, 9); widths(sws, [7, 16, 27, 23, 42, 40, 48, 48, 34]); sws.freeze_panes = "A5"; sws.auto_filter.ref = "A4:I16"; setup(sws, a3=True) wb.calculation.calcMode = "auto" wb.calculation.fullCalcOnLoad = True wb.calculation.forceFullCalc = True wb.properties.title = "智慧食堂前厅三版本归档与硬件选型" wb.properties.creator = "康比特数字体育科技" path.parent.mkdir(parents=True, exist_ok=True) wb.save(path) inject_cache(path, cache) def version_html(version): functions = "".join(f"
  • {html.escape(x)}
    • " for x in version["standard_functions"]) hardware = "".join(f"{html.escape(x['attribute'])}{html.escape(x['name'])}{html.escape(x['model'])}{html.escape(x['use'])}" for x in version["hardware"]) mappings = "、".join(version["source_skus"]) boundaries = "".join(f"
  • {html.escape(x)}
    • " for x in version["boundaries"]) scenarios = "".join(f"
  • {html.escape(x)}
    • " for x in version["customer_scenarios"]) people = "".join(f"
  • {html.escape(x)}
    • " for x in version["population_traits"]) needs = "".join(f"
  • {html.escape(x)}
    • " for x in version["customer_needs"]) decision_points = "".join(f"
  • {html.escape(x)}
    • " for x in version["decision_buying_points"]) user_points = "".join(f"
  • {html.escape(x)}
    • " for x in version["user_buying_points"]) return f"""{html.escape(version['name'])}培训卡
    CPT · SALES TRAINING

    {version['sequence']} · {html.escape(version['name'])}

    {html.escape(version['slogan'])}

    {html.escape(version['one_line'])}

    销售先记住

    卖给谁：{html.escape(version['buyer'])}

    {' → '.join(version['main_flow'])}

    内部对应子SKU：{mappings}

    典型客户场景

      {scenarios}

    人群特点

      {people}

    客户真正需要什么

      {needs}

    为什么会买

    管理者/购买人

      {decision_points}

    最终使用者

      {user_points}

    主要能力

      {functions}

    版本边界

      {boundaries}

    对应硬件

    {hardware}
    属性设备型号用途
    """ def build_version_workbook(version, path): wb = Workbook() ws = wb.active ws.title = "版本速查" title(ws, f"{version['sequence']}｜{version['name']}", f"{version['slogan']}｜{version['one_line']}", 6) info = [("购买人", version["buyer"]), ("客户场景", "；".join(version["customer_scenarios"])), ("人群特点", "；".join(version["population_traits"])), ("客户需求", "；".join(version["customer_needs"])), ("管理者买点", "；".join(version["decision_buying_points"])), ("最终用户买点", "；".join(version["user_buying_points"])), ("主流程", " → ".join(version["main_flow"])), ("归档子SKU", "、".join(version["source_skus"])), ("主要能力", "；".join(version["standard_functions"])), ("产品边界", "；".join(version["boundaries"]))] 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=6); ws.cell(r, 2, value) ws.cell(r, 1).fill = PatternFill("solid", fgColor=GRAY) for c in range(1, 7): ws.cell(r, c).border = Border(top=THIN, bottom=THIN, left=THIN, right=THIN) ws.cell(r, c).font = Font(name="微软雅黑", size=10, bold=c == 1) ws.cell(r, c).alignment = Alignment(wrap_text=True, vertical="center") ws.row_dimensions[r].height = 54 r += 1 r += 1 header(ws, r, ["属性", "硬件名称", "型号", "主要用途", "选择条件", "报价提醒"], fill=ORANGE) start = r + 1 for idx, item in enumerate(version["hardware"], start): vals = [item["attribute"], item["name"], item["model"], item["use"], "客户明确需要该入口/餐线时", "型号、数量、安装、接口单列"] for c, value in enumerate(vals, 1): ws.cell(idx, c, value) ws.row_dimensions[idx].height = 46 body(ws, start, start + len(version["hardware"]) - 1, 6) widths(ws, [18, 34, 28, 44, 38, 38]); setup(ws, a3=True) path.parent.mkdir(parents=True, exist_ok=True) wb.save(path) def main(): parser = argparse.ArgumentParser() parser.add_argument("--model", type=Path, required=True) parser.add_argument("--output", type=Path, required=True) args = parser.parse_args() model = json.loads(args.model.read_text(encoding="utf-8")) overview = args.output / "00-培训总览" overview.mkdir(parents=True, exist_ok=True) build_master(model, overview / "智慧食堂前厅三版本归档与硬件选型.xlsx") for version in model["versions"]: folder = args.output / f"{version['sequence']}-{version['name']}" folder.mkdir(parents=True, exist_ok=True) build_version_workbook(version, folder / f"{version['name']}_产品与硬件培训.xlsx") (folder / f"{version['name']}_销售培训速查卡.html").write_text(version_html(version), encoding="utf-8") archive = args.output / "04-内部映射" archive.mkdir(parents=True, exist_ok=True) with (archive / "12个业务SKU归档到3个销售版本.csv").open("w", encoding="utf-8-sig", newline="") as f: writer = csv.DictWriter(f, fieldnames=["code", "name", "archive", "role", "scenario", "people", "need", "buying_point"]) writer.writeheader(); writer.writerows(model["source_sku_mapping"]) (archive / "使用说明.md").write_text("# 使用说明\n\n销售先看`00-培训总览`，先选基础/标准/专业；再进入对应版本文件夹查看功能、硬件和边界。原12个SKU仅用于内部场景、配置和报价，不直接作为客户首次培训目录。\n", encoding="utf-8") print(json.dumps({"status":"PASS","versions":3,"source_skus":12,"output":str(args.output)}, ensure_ascii=False)) if __name__ == "__main__": main()