# -*- coding: utf-8 -*- """三部门月度关键任务 KPI 积分卡看板生成器 输入: 数据/kpi_scheme_raw.json (由 _parse_kpi_scheme.py 生成) 输出: 数据/kpi_scheme.json + 三部门KPI关键任务积分卡看板.html 口径: 自然月(1日-月末) """ import json, re, os, datetime BASE = "/Users/liuxinyuan/Desktop/Hermes输出-工作类" RAW = json.load(open(os.path.join(BASE, "数据/kpi_scheme_raw.json"))) ACT = json.load(open(os.path.join(BASE, "数据/mcp_activities_by_person_month.json"))) H2T = json.load(open(os.path.join(BASE, "数据/h2_monthly_targets.json"))) MONTHS = ["2026-08", "2026-09"] # ---------- 角色定义 ---------- ROLES = { "dk": { "name": "大客户部", "level": "销售工程师", "srcTag": "大客户部", "months": {"2026-08": "大客户部-8月", "2026-09": "大客户部-9月"}, "assign": {"2026-08": "指派任务-8月", "2026-09": "指派任务-9月"}, "h2": "大客户部", }, "tz": { "name": "销售拓展部", "level": "销售工程师", "srcTag": "拓展部", "months": {"2026-08": "销售工程师-8月", "2026-09": "销售工程师-9月"}, "assign": {"2026-08": "指派任务-8月", "2026-09": "指派任务-9月"}, "h2": "销售拓展部", }, "ls_eng": { "name": "生命科学销售部", "level": "销售工程师", "srcTag": "科研_销售工程师", "months": {"2026-08": "销售工程师-2026年8月", "2026-09": "销售工程师-2026年9月"}, "assign": {}, "h2": "生命科学部", }, "ls_mgr": { "name": "生命科学销售部", "level": "区域销售经理", "srcTag": "科研_区域经理", "months": {"2026-08": "科研区域销售经理8月", "2026-09": "科研区域销售经理9月"}, "assign": {}, "h2": "生命科学部", }, } # ---------- 人员名单（来源：workbench user_roles.json / diag_salespeople.json / 指派任务名单）---------- PEOPLE = [ # 大客户部 {"name": "赵云浩", "role": "dk", "region": "大客户2", "conf": "确认"}, {"name": "刘子研", "role": "dk", "region": "大客户1", "conf": "确认（9月新入职）"}, # 销售拓展部 {"name": "陈溪子", "role": "tz", "region": "华南1", "conf": "确认"}, {"name": "刘虹晶", "role": "tz", "region": "华北", "conf": "确认"}, {"name": "黄文才", "role": "tz", "region": "华南2", "conf": "确认"}, {"name": "宋学伟", "role": "tz", "region": "江苏", "conf": "确认"}, {"name": "李江辉", "role": "tz", "region": "华中", "conf": "确认"}, {"name": "路要峰", "role": "tz", "region": "上海2", "conf": "确认"}, {"name": "冯贤平", "role": "tz", "region": "北京", "conf": "确认"}, {"name": "杨天雄", "role": "tz", "region": "浙江", "conf": "确认"}, {"name": "林潇", "role": "tz", "region": "华南2", "conf": "确认"}, {"name": "徐卓凡", "role": "tz", "region": "上海1", "conf": "确认"}, {"name": "张文蝶", "role": "tz", "region": "上海1", "conf": "确认"}, # 生命科学销售部 {"name": "叶信福", "role": "ls_eng", "region": "浙江科研4", "conf": "确认"}, {"name": "袁殿果", "role": "ls_eng", "region": "浙江科研", "conf": "确认"}, {"name": "侯海瑜", "role": "ls_eng", "region": "浙江科研3", "conf": "确认"}, {"name": "刘斌", "role": "ls_eng", "region": "广州科研6", "conf": "确认"}, {"name": "王诗香", "role": "ls_eng", "region": "广州科研5", "conf": "确认"}, {"name": "黄建华", "role": "ls_eng", "region": "广州科研", "conf": "确认"}, {"name": "陈彩红", "role": "ls_eng", "region": "广州科研", "conf": "确认"}, {"name": "吴云", "role": "ls_mgr", "region": "浙江科研", "conf": "确认"}, {"name": "韩远怀", "role": "ls_mgr", "region": "广州科研", "conf": "确认"}, ] # ---------- 数据接入位（真实来源与状态）---------- BINDINGS = { "有效客户拜访次数": {"src": "飞书活动导出 / MCP活动", "status": "参考", "note": "当前仅有「活动条数」，协议口径=飞书打卡记录×对应活动记录互相匹配算1条有效拜访，需导出打卡数据做匹配；科研版按「当日1个课题组活动算1」需去重课题组"}, "协助拜访课题组数量": {"src": "飞书活动导出 / MCP活动", "status": "参考", "note": "需按「课题组+当日」去重"}, "课题组关键决策人拜访数量": {"src": "飞书活动（关键词「关键决策人拜访」）", "status": "待接入", "note": "协议要求在活动记录中标注「关键决策人拜访」，需统计标注覆盖率"}, "测试单发放数量": {"src": "ERP测试单 / 飞书测试反馈", "status": "待接入", "note": "需ERP测试单导出（含销售员+发出日期）"}, "当月测试单反馈率": {"src": "数据/test_feedback.json + 科研-测试反馈跟进", "status": "部分接入", "note": "科研有结构化测试反馈记录；诊断原料口径仍需按包装数计算"}, "成交客户数量": {"src": "ERP订单 + 飞书客户版块", "status": "可接入", "note": "按课题组/科研机构为单位统计，需客户归属表"}, "回款及时率": {"src": "回款数据/*回款进度*.xlsx（财务）", "status": "待接入", "note": "分母「本期应回款金额」需财务确认口径"}, "新客户开发数量": {"src": "ERP测试单首次成交", "status": "待接入", "note": "新课题组发出一个测试单即算开发成功"}, "召开销售周会和复盘会议": {"src": "会议纪要邮件 + 人工", "status": "人工", "note": "需统计邮件纪要数（销售支持部收件）"}, "月度关键任务": {"src": "业务部门统计 / 人工", "status": "人工", "note": "科研9月=workshop开展场次"}, "促销成果": {"src": "ERP促销期业绩（自然月）", "status": "可接入", "note": "协议为 x² 计分，x=实际/T2目标"}, "指派任务完成情况（浮动）": {"src": "混合（飞书活动关键词+逾期回款计划+人工）", "status": "部分接入", "note": "NGS拜访/PDRN专项可按活动关键词统计；决策链完善、问卷收回需人工"}, "上级评分": {"src": "人工评分（上级填写）", "status": "人工", "note": "直接上级/区域负责人 + 间接上级/销售总监（科研工程师版有第三档）"}, "官方通报实数": {"src": "各部门《考核分数通报》xlsx（1–8月已入库）", "status": "已接入", "note": "官方已算分的逐人逐项「实际达成 / 实际得分 / 折算得分」，看板「官方通报·真实数据」视图直接展示；积分卡视图自动回填 8 月达成值"}, } def num_tier(v): """T1/T2/T3 数值化""" if v is None: return None s = str(v).strip() if s in ("", "\\", "\\。", "nan"): return None s = s.replace("≧", ">=").replace("≥", ">=").replace("%", "") m = re.search(r"-?\d+(?:\.\d+)?", s) if not m: return None return float(m.group()) def abc_tier(s): """A:50% B:30% C:20% -> {A:0.5,B:0.3,C:0.2}""" out = {} for k, v in re.findall(r"([ABC])\s*[:：]\s*(\d+(?:\.\d+)?)\s*%?", str(s)): out[k] = float(v) / 100 return out or None def items_of(role_key, month): cfg = ROLES[role_key] sheet = cfg["months"][month] sh = RAW[cfg["srcTag"]]["sheets"][sheet] out = [] for it in sh["items"]: t1, t2, t3 = it["T1"], it["T2"], it["T3"] abc = abc_tier(t1) rec = { "no": it["no"], "item": it["item"], "desc": it["desc"], "weight": it["weight"], "source": it["source"], "owner": it["owner"], } if abc: rec.update({"type": "abc", "tiers": {"T1": abc, "T2": abc_tier(t2) or abc, "T3": abc_tier(t3) or abc}, "tierLabel": {"T1": t1, "T2": t2, "T3": t3}}) elif "T2均为x平方" in str(t3) or "x平方" in str(t3): rec.update({"type": "x2", "t1": num_tier(t1), "t2": num_tier(t2), "tierLabel": {"T1": t1, "T2": t2, "T3": t3}}) elif it["item"].startswith("指派任务"): rec.update({"type": "manual", "t1": num_tier(t1), "t2": num_tier(t2), "t3": num_tier(t3), "tierLabel": {"T1": t1, "T2": t2, "T3": t3}}) else: rec.update({"type": "num", "t1": num_tier(t1), "t2": num_tier(t2), "t3": num_tier(t3), "tierLabel": {"T1": t1, "T2": t2, "T3": t3}}) out.append(rec) sup = [{"role": s["role"], "full": s["full"], "grader": s["grader"]} for s in sh["superiors"]] return out, sup, sh["weight_sum"] def assign_of(role_key, month): cfg = ROLES[role_key] if month not in cfg.get("assign", {}): return None sh = RAW[cfg["srcTag"]]["sheets"].get(cfg["assign"][month]) if not sh: return None rows = sh["rows"] out = [] for r in rows: if not r or not r[0]: continue no = str(r[0]).strip() if not re.match(r"^\d+$", no): continue text = str(r[1]).strip() if not text: continue part = str(r[2]).strip() if len(r) > 2 else "" who = str(r[3]).strip() if len(r) > 3 else "" t1 = str(r[4]).strip() if len(r) > 4 else "" t2 = str(r[5]).strip() if len(r) > 5 else "" t3 = str(r[6]).strip() if len(r) > 6 else "" try: share = float(part.replace("%", "").split("（")[0]) except Exception: share = None grp = "either" if "二选一" in part else None out.append({"no": no, "text": text, "share": share, "shareLabel": part, "who": who, "grp": grp, "exec": share is not None, "T1": t1, "T2": t2, "T3": t3}) return out # ---------- 组装方案 ---------- scheme = { "meta": { "generated": datetime.datetime.now().strftime("%Y-%m-%d %H:%M"), "period_rule": "自然月（1日–月末），与ERP/月报口径一致", "months": MONTHS, "scoring": { "task_full": 70, "anchors": {"T1": 0.6, "T2": 1.0, "T3": 1.2}, "below_t1": 0, "anchor_note": "官方通报实证口径（2026-01～08 通报原件回测确认）：未达T1记0分、达T1记60分、达T2记100分、达T3记120分 —— 档位离散，档位之间不插值（同档内 35 与 37 同分）。折算得分 = 得分 × 权重。", "verified": "用 4 份《考核分数通报》1–8月共 804 个「实际达成→实际得分」格做回测：档位离散模型一致率 93.4%（8月 89 格中 88 格一致，唯一不一致为官方原表笔误），插值模型一致率仅 62%。", "rating": {"A": 90, "B": 80, "C": 70, "D": 0}, "rating_note": "绩效评级按「得分率」= 考核得分 ÷ 常规满分（关键任务70 + 上级评分满分）：≥90%=A、80–89%=B、70–79%=C、<70%=D。协议未定义，默认值可改。", "cap_note": "常规满分 = 70 + 上级评分满分（15+15 或 18+18）；全项达 T3 时关键任务得 84 分（70×1.2），为挑战上限。", }, "h2_targets": H2T["depts"], "decisions": [ "计分档位（官方通报实证）：未达T1=0分、达T1=60分、达T2=100分、达T3=120分，档位离散不插值；折算得分=得分×权重；关键任务得分=Σ折算（100分制）。（2026-09-10 通报原件回测确认）", "总得分 = 关键任务得分×0.7 + 上级评分：大客户部/拓展部权重合计100%（满分100）故×0.7，上级15+15=30，常规满分100；生命科学部考核项权重合计本身=70%（达T3上限84），不再打折，上级 工程师18+18=36 / 区域经理15+15=30，常规满分120。与官方通报逐人总分一致。", "总分就按算出来的分记：生命科学销售部-销售工程师版常规满分 120 分（84+36）；其余三版 100 分。（2026-09-10 确认）", "生命科学版关键任务权重合计 0.7、上级评价占比 0.3 —— 官方通报即按此执行，无需再归一化。（2026-09-10 确认）", "生命科学销售部 9月「有效客户拜访次数」门槛 120/140/160（较 7–8月 96/112/144 上调 25%）—— 已确认执行。（2026-09-10 确认）", "大客户部 9月 指派任务第1条（满意度问卷收回）、第2条（决策链更新）未标占比 —— 本月不执行、不计分；计分条为第3条 15% + 第4条 15% = 30%。（2026-09-10 确认）", "拓展部 9月 指派任务「NGS拜访」与「PDRN专项」按 15%（1和3二选一）取较高分；第2条（日报关联商机%）未标占比，本月不计分。", "人员名单：大客户部 赵云浩、刘子研（张立娅、王昕伟已离职，已剔除）；生命科学销售部工程师 7 人（陈彩红为准）；区域经理 吴云（浙江）、韩远怀（广州）。（2026-09-10 确认）", "官方《考核分数通报》2026年1–8月原件已解析入库（数据/kpi_actual.json + kpi_actual_pack.json），看板「官方通报·真实数据」视图可直接查看官方已算分的逐人逐项明细与月度趋势。", ], "open_items": [ "大客户部/拓展部 上级评分「占比30%」与满分 15+15=30 一致；生命科学工程师版 18+18=36 造成 106 分制，已按确认执行，若后续统一为 30 分需改 kpi_scheme.json。", "待接入数据（影响自动算分）：ERP测试单发放量、当月测试单反馈率、回款及时率、新客户开发数量、促销成果。", "需人工录入项：销售周会/复盘会议次数、workshop 场次、满意度问卷收回数、上级评分。", "占位待填：8月大客户部/拓展部 指派任务中的「飞书重点客户决策链完善」进度目标（协议仅列 0.7/0.8/0.9 三档）。", "促销成果计分：协议写「T2～T3 间的达成率假设为 x，计算分数为 x²」，但 T3 栏仅写「=T2」、无独立目标。页面按 x = 实际÷T2目标、档位分 = x²（封顶 100 分）计算；若 T3 另有目标或超出 T2 应继续加分，请给出规则。", ], }, "roles": {}, "people": PEOPLE, "bindings": BINDINGS, } # 各角色各月 KPI + 上级评分 for rk, cfg in ROLES.items(): entry = {"name": cfg["name"], "level": cfg["level"], "srcTag": cfg["srcTag"], "h2": cfg["h2"], "months": {}} for m in MONTHS: items, sup, wsum = items_of(rk, m) entry["months"][m] = {"items": items, "superiors": sup, "weight_sum": wsum, "assign": assign_of(rk, m)} scheme["roles"][rk] = entry # ---------- 真实值接入（仅可接入部分；其余留空由页面手填）---------- real = {} for m in MONTHS: per = ACT.get(m, {}) real[m] = {"拜访类活动条数": per} scheme["real_values"] = real # 同月规则变化对比 scheme["rule_diff"] = {} for rk, cfg in ROLES.items(): a = {i["item"]: (i.get("t1"), i.get("t2"), i.get("t3"), i["weight"]) for i in scheme["roles"][rk]["months"]["2026-08"]["items"]} b = {i["item"]: (i.get("t1"), i.get("t2"), i.get("t3"), i["weight"]) for i in scheme["roles"][rk]["months"]["2026-09"]["items"]} diffs = [] for k in set(list(a) + list(b)): if a.get(k) != b.get(k): diffs.append({"item": k, "8月": a.get(k), "9月": b.get(k)}) if diffs: scheme["rule_diff"][cfg["name"] + "-" + cfg["level"]] = diffs dst = os.path.join(BASE, "数据/kpi_scheme.json") json.dump(scheme, open(dst, "w"), ensure_ascii=False, indent=1) print("WROTE", dst, os.path.getsize(dst)) # ---------- 生成自包含看板 HTML ---------- TPL = open(os.path.join(BASE, "数据/_kpi_dashboard_template.html")).read() PACK = json.load(open(os.path.join(BASE, "数据/kpi_actual_pack.json"))) html = TPL.replace("__SCHEME_JSON__", json.dumps(scheme, ensure_ascii=False, separators=(",", ":"))) html = html.replace("__ACT_JSON__", json.dumps({"roles": PACK["roles"]}, ensure_ascii=False, separators=(",", ":"))) html = html.replace("__ACTMAP_JSON__", json.dumps(PACK["map"], ensure_ascii=False, separators=(",", ":"))) PROG = json.load(open(os.path.join(BASE, "数据/kpi_progress_pack.json"))) html = html.replace("__PROG_JSON__", json.dumps(PROG, ensure_ascii=False, separators=(",", ":"))) assert "__SCHEME_JSON__" not in html and "__ACT_JSON__" not in html and "__ACTMAP_JSON__" not in html and "__PROG_JSON__" not in html, "占位符未替换" dhtml = os.path.join(BASE, "三部门KPI关键任务积分卡看板.html") open(dhtml, "w").write(html) print("WROTE", dhtml, os.path.getsize(dhtml)) print("官方通报月份:", {k: sorted(v.keys()) for k, v in PACK["roles"].items()}) print("角色:", list(scheme["roles"])) for rk in scheme["roles"]: for m in MONTHS: s = scheme["roles"][rk]["months"][m] print(f" {rk:7s} {m} 项={len(s['items'])} Σw={s['weight_sum']} 上级={[(x['role'],x['full']) for x in s['superiors']]} 指派={len(s['assign'][''] if False else (s['assign'] or [])) if s['assign'] is not None else None}") print("规则差异:", json.dumps(scheme["rule_diff"], ensure_ascii=False)[:800])