#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生成 2027 年生命科学销售部预算测算与建议工作簿（内部经营分析用）。"""
import json, os, collections, statistics, datetime
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, LineChart, Reference

BASE = "/Users/liuxinyuan/Desktop/Hermes输出-工作类"
OUT = os.path.join(BASE, "资料", "2027年生命科学销售部预算_测算与建议_20261112.xlsx")

# ---------- styles ----------
NAVY = "1F4E79"
BLUE = "2E75B6"
LIGHT = "DDEBF7"
GREY = "F2F2F2"
GREEN = "E2EFDA"
ORANGE = "FCE4D6"
THIN = Side(style="thin", color="BFBFBF")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
F_TITLE = Font(name="宋体", size=16, bold=True, color=NAVY)
F_SEC = Font(name="宋体", size=12, bold=True, color="FFFFFF")
F_HDR = Font(name="宋体", size=10, bold=True, color="FFFFFF")
F_BODY = Font(name="宋体", size=10)
F_BOLD = Font(name="宋体", size=10, bold=True)
F_NOTE = Font(name="宋体", size=9, color="595959")
FILL_SEC = PatternFill("solid", fgColor=NAVY)
FILL_HDR = PatternFill("solid", fgColor=BLUE)
FILL_LIGHT = PatternFill("solid", fgColor=LIGHT)
FILL_GREY = PatternFill("solid", fgColor=GREY)
FILL_GREEN = PatternFill("solid", fgColor=GREEN)
FILL_ORANGE = PatternFill("solid", fgColor=ORANGE)
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True)
RIGHT = Alignment(horizontal="right", vertical="center")


def section(ws, row, text, ncols):
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=ncols)
    c = ws.cell(row, 1, text)
    c.font = F_SEC
    c.fill = FILL_SEC
    c.alignment = LEFT
    ws.row_dimensions[row].height = 24


def header(ws, row, cols):
    for j, v in enumerate(cols, 1):
        c = ws.cell(row, j, v)
        c.font = F_HDR
        c.fill = FILL_HDR
        c.alignment = CENTER
        c.border = BORDER
    ws.row_dimensions[row].height = 32


def body(ws, row, vals, bold=False, fill=None, numfmt=None, aligns=None):
    for j, v in enumerate(vals, 1):
        c = ws.cell(row, j, v)
        c.font = F_BOLD if bold else F_BODY
        c.border = BORDER
        al = aligns[j-1] if aligns and aligns[j-1] else (LEFT if j == 1 else CENTER)
        fmt = numfmt
        # 允许在 aligns 里直接写百分比/倍数格式，自动转成右对齐 + 数字格式
        if isinstance(al, str) and (al.endswith("%") or al.endswith("x")):
            fmt = al
            al = RIGHT
        c.alignment = al
        if fill:
            c.fill = fill
        if fmt and isinstance(v, (int, float)):
            c.number_format = fmt


def note(ws, row, text, ncols):
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=ncols)
    c = ws.cell(row, 1, text)
    c.font = F_NOTE
    c.alignment = LEFT
    c.fill = FILL_GREY


def widths(ws, ws_widths):
    for col, w in ws_widths.items():
        ws.column_dimensions[col].width = w


# ---------- load data ----------
orders = json.load(open(os.path.join(BASE, "数据/orders_detail_2026.json")))["orders"]
target = json.load(open(os.path.join(BASE, "数据/目标基础数据_2026.json")))
people = {p["name"]: p for p in target["people"] if p.get("dept") == "生命科学部"}

month_sales = collections.defaultdict(float)
month_region = collections.defaultdict(lambda: collections.defaultdict(float))
person_sales = collections.defaultdict(float)
person_orders = collections.Counter()
customer_sales = collections.defaultdict(float)
customer_orders = collections.defaultdict(set)
product_group = collections.defaultdict(float)
sku_sales = collections.defaultdict(float)
order_set = set()

for o in orders.values():
    s = o.get("sales")
    if s not in people:
        continue
    if o.get("type") != "标准销售订单" or o.get("status") != "已审核":
        continue
    cust = o.get("customer", "")
    if any(x in cust for x in ("宝锐", "宝泰", "横琴")):
        continue
    d = o.get("date", "")[:7]
    if not ("2026-01" <= d <= "2026-10"):
        continue
    amt = sum(float(l.get("amount") or 0) for l in o.get("lines", []) if not l.get("gift"))
    if amt <= 0:
        continue
    region = people[s]["region"]
    month_sales[d] += amt
    month_region[d][region] += amt
    person_sales[s] += amt
    person_orders[s] += 1
    customer_sales[cust] += amt
    customer_orders[cust].add(o["no"])
    order_set.add(o["no"])
    for l in o.get("lines", []):
        a = float(l.get("amount") or 0)
        if l.get("gift") or a <= 0:
            continue
        product_group[l.get("pgroup") or l.get("ptype") or "未分类"] += a
        sku_sales[(l.get("sku"), l.get("name"))] += a

sales_total = sum(month_sales.values())
region_total = {r: sum(v[r] for v in month_region.values()) for r in ("广东", "浙江")}
months = [f"2026-{m:02d}" for m in range(1, 11)]
q_sales = {
    "Q1": sum(month_sales[m] for m in months[:3]),
    "Q2": sum(month_sales[m] for m in months[3:6]),
    "Q3": sum(month_sales[m] for m in months[6:9]),
    "Q4(10月)": month_sales["2026-10"],
}
target_month = target["departments"]["生命科学部"]["target_monthly"]
target_ytd = sum(target_month[m] for m in months)
target_annual = target["departments"]["生命科学部"]["target_annual"]
annualized_sales = sales_total / 10 * 12
active_sales = sum(v for s, v in person_sales.items() if people[s]["status"] != "离职")
departed_sales = sum(v for s, v in person_sales.items() if people[s]["status"] == "离职")

# PI data
pi = json.load(open(os.path.join(BASE, "数据/ls_ketizu_purchase.json")))["ketizu"]
pi_pay = [x for x in pi if float(x.get("amt_p26") or 0) > 0]
pi_total = sum(float(x["amt_p26"]) for x in pi_pay)
pi_avg = pi_total / len(pi_pay)
pi_median = statistics.median([float(x["amt_p26"]) for x in pi_pay])
pi_new = [x for x in pi_pay if str(x.get("first_deal", ""))[:4] == "2026"]
pi_repeat = [x for x in pi_pay if int(x.get("orders_paid") or 0) >= 2]
pi_buckets = collections.Counter()
for x in pi_pay:
    a = float(x["amt_p26"])
    if a < 1000:
        pi_buckets["<1千"] += 1
    elif a < 5000:
        pi_buckets["1千–5千"] += 1
    elif a < 10000:
        pi_buckets["5千–1万"] += 1
    else:
        pi_buckets["≥1万"] += 1

# expense from 2027 workbook 2026 actual detail
SRC27 = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/30920b28c4bbba7e7dab1fe39339a91748b6a3fb562435f192c9a021aa225284/doc_eeab79257578_2027全面预算表 -生命科学销售部.xlsx"
wb27 = load_workbook(SRC27, data_only=True)
ws_exp = wb27["附表4-2026年实际费用明细"]
exp_cat = collections.defaultdict(float)
exp_month = collections.defaultdict(float)
exp_region = collections.defaultdict(float)
exp_region_cat = collections.defaultdict(float)
for r in range(2, ws_exp.max_row + 1):
    vals = [ws_exp.cell(r, c).value for c in range(1, 14)]
    amt = vals[12] if vals[12] not in (None, "") else (vals[11] or 0)
    if not amt:
        continue
    cat = str(vals[4] or vals[5] or "未分类").strip()
    m = str(vals[7])
    reg = str(vals[0] or "未标")
    exp_cat[cat] += float(amt)
    exp_month[m] += float(amt)
    exp_region[reg] += float(amt)
    exp_region_cat[(reg, cat)] += float(amt)

exp_total = sum(exp_cat.values())
exp_h1 = sum(exp_month[str(m)] for m in range(1, 7))
exp_q3 = sum(exp_month[str(m)] for m in range(7, 10))
exp_annualized = exp_total * 12 / 9

# 2026 budget from 2026 workbook 3-期间费用
SRC26 = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/22dd7242b5bb7cf681081afb6595a60ec46d720a1d4fa8339a5082d55557c414/doc_89da75053120_全面预算套表-科研销售部-2026年度V9(1).xlsx"
wb26 = load_workbook(SRC26, data_only=True)
ws26 = wb26["3-期间费用"]
budget26 = {}
for r in range(6, 30):
    name = ws26.cell(r, 4).value
    if not name or str(name).strip() == "——":
        name = ws26.cell(r, 3).value
    val = ws26.cell(r, 9).value
    if name and isinstance(val, (int, float)) and val:
        budget26[str(name).strip()] = float(val)
budget26_total = float(ws26.cell(30, 9).value or sum(budget26.values()))

# 2025 actual from 2027 workbook summary
ws27sum = wb27["3-1期间费用"]
actual25 = {}
for r in range(13, 49):
    name = ws27sum.cell(r, 4).value
    if not name or str(name).strip() == "——":
        name = ws27sum.cell(r, 3).value
    val = ws27sum.cell(r, 12).value
    if name and isinstance(val, (int, float)) and val:
        actual25[str(name).strip()] = float(val)
actual25_total = float(ws27sum.cell(49, 12).value or sum(actual25.values()))

# 2026 actual summary (official 3-1 mapping; 1.887 万技术服务费并入会务费)
summary26 = {}
for r in range(13, 49):
    name = ws27sum.cell(r, 4).value
    if not name or str(name).strip() == "——":
        name = ws27sum.cell(r, 3).value
    val = ws27sum.cell(r, 11).value
    if name and isinstance(val, (int, float)) and val:
        summary26[str(name).strip()] = float(val)

# ---------- model ----------
TARGET27 = 400.0
REGION27 = {"广东": 200.0, "浙江": 200.0}
SALES_Q = {"Q1": 60.0, "Q2": 100.0, "Q3": 110.0, "Q4": 130.0}
SALES_M = {
    "1月": 15.0, "2月": 20.0, "3月": 25.0,
    "4月": 30.0, "5月": 35.0, "6月": 35.0,
    "7月": 35.0, "8月": 37.0, "9月": 38.0,
    "10月": 40.0, "11月": 45.0, "12月": 45.0,
}
EXP_TOTAL27 = 100.0
EXP_Q = {"Q1": 20.0, "Q2": 35.0, "Q3": 25.0, "Q4": 20.0}
EXP_M = {
    "1月": 5.0, "2月": 8.0, "3月": 7.0,
    "4月": 10.0, "5月": 12.0, "6月": 13.0,
    "7月": 8.0, "8月": 9.0, "9月": 8.0,
    "10月": 7.0, "11月": 7.0, "12月": 6.0,
}
# official template mapping: 2027 budget by row/科目
BUDGET_CAT = [
    # 科目, 官方模板行, 2027预算(万), 2026实际(万), 说明
    ("办公费用-办公用品", 13, 2.0, 0.3825, "按 8–10 人配置；2026 实际 0.38 万"),
    ("办公费用-快递费", 16, 1.0, 0.0, "样品/合同寄送"),
    ("差旅费-交通费", 19, 10.0, 3.1758, "按 10 人、区域拜访频次配置"),
    ("差旅费-差旅补贴", 20, 1.0, 0.2760, "按实际出差天数"),
    ("差旅费-住宿费", 21, 4.0, 0.9577, "按区域差旅标准"),
    ("招聘费", 22, 3.0, 0.0, "新增 2 人，Q2 到位"),
    ("培训费", 23, 2.0, 0.0, "产品/销售方法培训"),
    ("咨询服务费-其他", 27, 2.0, 0.1931, "平台/资质/第三方服务；2026 技术服务费已并入会务"),
    ("业务招待费", 28, 20.0, 2.1422, "按销售目标 5% 控制"),
    ("业务推广费", 31, 10.0, 0.0, "校园行/线上推广/物料"),
    ("会务费", 32, 25.0, 0.8924, "区域学术会、校园行、展会（含 2026 技术服务费 0.189 万）"),
    ("福利费", 41, 2.0, 0.0, "团队活动/节日慰问"),
    ("客户测试物料", 44, 15.0, 2.1234, "测试转销售，按项目审批"),
    ("其他", 36, 3.0, 0.2989, "低值易耗/不可预见"),
]
# check sum
assert abs(sum(x[2] for x in BUDGET_CAT) - EXP_TOTAL27) < 1e-9

# ---------- workbook ----------
wb = Workbook()
ws = wb.active
ws.title = "01_结论与口径"
widths(ws, {"A": 22, "B": 18, "C": 16, "D": 16, "E": 16, "F": 18, "G": 22})
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:G1")
ws["A1"] = "2027 年生命科学销售部预算测算与建议"
ws["A1"].font = F_TITLE
ws["A1"].alignment = LEFT
ws.row_dimensions[1].height = 30
ws["A2"] = "编制日期：2026-11-12　｜　销售目标：400 万（广东 200 / 浙江 200）　｜　金额单位：万元（含税）"
ws.merge_cells("A2:G2")
ws["A2"].font = F_NOTE
ws["A2"].alignment = LEFT

section(ws, 4, "一、先看结论", 7)
conclusions = [
    ("2026 年 1–10 月实际销售", f"{sales_total/10000:.2f} 万", "ERP 订单明细，标准订单、已审核、非赠品、非内部客户"),
    ("2026 年 1–10 月目标", f"{target_ytd:.2f} 万", "目标基础数据 2026，按 1–10 月逐月加总"),
    ("1–10 月目标达成率", f"{sales_total/10000/target_ytd*100:.1f}%", "年度目标 600 万，年度达成率约 7.4%"),
    ("2026 年化销售", f"{annualized_sales/10000:.1f} 万", "按 1–10 月均值×12，未考虑季节性"),
    ("2027 年目标 400 万", f"+{(TARGET27/(annualized_sales/10000)-1)*100:.0f}%", "相对 2026 年化；相对 2026 年目标 600 万为 -33%"),
    ("2027 年预算档位", "冲刺 400/100；保底 300/80", "正式上报冲刺档；保底档作季度滚动基线；金额为销售/费用（万）"),
]
r = 5
for a, b, c in conclusions:
    ws.cell(r, 1, a).font = F_BOLD
    ws.cell(r, 1).border = BORDER
    ws.cell(r, 2, b).font = F_BOLD
    ws.cell(r, 2).fill = FILL_LIGHT
    ws.cell(r, 2).border = BORDER
    ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=3)
    ws.cell(r, 4, c).font = F_BODY
    ws.merge_cells(start_row=r, start_column=4, end_row=r, end_column=7)
    ws.cell(r, 4).alignment = LEFT
    r += 1

section(ws, 12, "二、三条判断", 7)
judgements = [
    ("判断 1：400 万不是“增长率”，是培育期跳变。",
     "2026 年 1–10 月实际 44.28 万，年化约 53.1 万；400 万相当于 7.5 倍。用“同比增长百分之几十”来做 2027 预算会失真，必须按客户数、PI 数、单客产出和区域动作倒推。"),
    ("判断 2：费用不能从 2026 年预算往下砍，要从 2026 年实际往上配。",
     "2026 年费用预算 38.63 万，1–9 月实际仅 10.44 万，预算执行率 27%（按 9 个月年化 36%）。2026 预算不是有效基数；2027 按 25% 费用率配 100 万，和 2026 实际费用率 23.6%–26.3% 基本一致。"),
    ("判断 3：广东是主要缺口，浙江是基本盘。",
     f"2026 年 1–10 月广东 {region_total['广东']/10000:.2f} 万、浙江 {region_total['浙江']/10000:.2f} 万；按年化，广东 19.5 万、浙江 33.7 万。做到各 200 万，广东要增长约 9.3 倍，浙江约 4.9 倍。"),
]
r = 13
for h, t in judgements:
    ws.cell(r, 1, h).font = F_BOLD
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
    ws.cell(r, 1).alignment = LEFT
    ws.cell(r, 1).fill = FILL_LIGHT
    ws.cell(r, 1).border = BORDER
    ws.cell(r + 1, 1, t).font = F_BODY
    ws.merge_cells(start_row=r + 1, start_column=1, end_row=r + 1, end_column=7)
    ws.cell(r + 1, 1).alignment = LEFT
    ws.cell(r + 1, 1).border = BORDER
    ws.row_dimensions[r + 1].height = 42
    r += 3

section(ws, 23, "三、口径与数据源", 7)
source_rows = [
    ("销售实际", "ERP《销售订单_20260101-20261111.xlsx》订单明细，取 2026-01-01 至 2026-10-31；标准销售订单、已审核、非赠品、客户不含宝锐/宝泰/横琴。"),
    ("2026 目标", "《目标基础数据_2026.json》生命科学部，年度 600 万；1–10 月目标 411.40 万。"),
    ("2026 费用", "《2027全面预算表-生命科学销售部.xlsx》附表4-2026年实际费用明细，实际只到 2026-09，未含 10 月未入账费用。"),
    ("2025 对比", "《2025全年订单.xlsx》按科研-广东/浙江销售员归集；仅覆盖原 8 名科研销售，新增人员未覆盖，作参考。"),
    ("PI / 课题组", "《ls_ketizu_purchase.json》，2026 年有金额课题组 192 个，金额合计 43.70 万；与 ERP 44.28 万差 0.58 万，为订单未映射到课题组的部分。"),
    ("金额口径", "含税（ERP 价税合计）；费用为不含税/财务入账口径，二者只用于费用率参考，不做严格勾稽。"),
]
r = 24
for k, v in source_rows:
    ws.cell(r, 1, k).font = F_BOLD
    ws.cell(r, 1).border = BORDER
    ws.cell(r, 2, v).font = F_BODY
    ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=7)
    ws.cell(r, 2).alignment = LEFT
    ws.cell(r, 2).border = BORDER
    ws.row_dimensions[r].height = 32
    r += 1

section(ws, 32, "四、已确认口径（2026-11-12）", 7)
for i, t in enumerate([
    "2026 年 10 月无费用：本表费用实际为 1–9 月，按 9 个月年化 13.92 万。",
    "400 万为含税销售；收入来源领域不限定，预算不锁死试剂/服务/仪器的产品结构。",
    "2027 年不论人员是否新增，400 万目标必须达成；新增人员只影响费用结构，不作为目标前提。",
    "高低两档建议：保底 300/80，冲刺 400/100；正式上报冲刺档，保底档作季度滚动基线。低档目标值待拍板。",
], 1):
    ws.cell(32 + i, 1, f"{i}. {t}").font = F_BODY
    ws.merge_cells(start_row=32 + i, start_column=1, end_row=32 + i, end_column=7)
    ws.cell(32 + i, 1).alignment = LEFT
    ws.cell(32 + i, 1).border = BORDER
    ws.row_dimensions[32 + i].height = 30

# ---------- 02 sales ----------
ws2 = wb.create_sheet("02_2026销售实际")
widths(ws2, {"A": 12, "B": 14, "C": 14, "D": 14, "E": 14, "F": 14, "G": 14})
ws2.sheet_view.showGridLines = False
ws2.merge_cells("A1:G1")
ws2["A1"] = "2026 年 1–10 月生命科学销售实际"
ws2["A1"].font = F_TITLE
section(ws2, 3, "按月：实际 vs 目标（万元）", 7)
header(ws2, 4, ["月份", "广东实际", "浙江实际", "合计实际", "当月目标", "达成率", "累计实际"])
r = 5
cum = 0
for m in months:
    cum += month_sales[m] / 10000
    vals = [m, round(month_region[m]["广东"] / 10000, 4), round(month_region[m]["浙江"] / 10000, 4),
            round(month_sales[m] / 10000, 4), target_month[m], month_sales[m] / 10000 / target_month[m] if target_month[m] else 0,
            round(cum, 4)]
    body(ws2, r, vals, numfmt="0.00", aligns=[CENTER, RIGHT, RIGHT, RIGHT, RIGHT, "0.0%", RIGHT])
    r += 1
body(ws2, r, ["合计", round(region_total["广东"]/10000,4), round(region_total["浙江"]/10000,4),
              round(sales_total/10000,4), round(target_ytd,4), sales_total/10000/target_ytd,
              round(sales_total/10000,4)], bold=True, fill=FILL_LIGHT, numfmt="0.00",
     aligns=[CENTER, RIGHT, RIGHT, RIGHT, RIGHT, "0.0%", RIGHT])
r += 2
section(ws2, r, "按季度", 7); r += 1
header(ws2, r, ["季度", "广东", "浙江", "合计", "占 1–10 月", "季度目标", "达成率"]); r += 1
for q in ["Q1", "Q2", "Q3", "Q4(10月)"]:
    if q == "Q4(10月)":
        gd = month_region["2026-10"]["广东"]/10000; zj = month_region["2026-10"]["浙江"]/10000; tot = month_sales["2026-10"]/10000
        tgt = target_month["2026-10"]
    else:
        idx = {"Q1": months[:3], "Q2": months[3:6], "Q3": months[6:9]}[q]
        gd = sum(month_region[m]["广东"] for m in idx)/10000; zj = sum(month_region[m]["浙江"] for m in idx)/10000
        tot = sum(month_sales[m] for m in idx)/10000; tgt = sum(target_month[m] for m in idx)
    body(ws2, r, [q, round(gd,4), round(zj,4), round(tot,4), tot/(sales_total/10000), tgt, tot/tgt if tgt else 0],
         numfmt="0.00", aligns=[CENTER, RIGHT, RIGHT, RIGHT, "0.0%", RIGHT, "0.0%"]); r += 1
note(ws2, r+1, "注：2026 年目标为 600 万，1–10 月目标合计 411.40 万；实际 44.28 万，达成率 10.8%。", 7)

# chart monthly actual vs target
ch = LineChart(); ch.title = "2026 年 1–10 月：实际 vs 目标（万元）"; ch.y_axis.title = "万元"; ch.x_axis.title = "月份"
data = Reference(ws2, min_col=4, max_col=5, min_row=4, max_row=14)
cats = Reference(ws2, min_col=1, min_row=5, max_row=14)
ch.add_data(data, titles_from_data=True); ch.set_categories(cats)
ch.height = 8; ch.width = 16
ws2.add_chart(ch, "I4")

# ---------- 03 customer/product ----------
ws3 = wb.create_sheet("03_客户与产品")
widths(ws3, {"A": 28, "B": 14, "C": 10, "D": 18, "E": 18, "F": 14, "G": 14})
ws3.sheet_view.showGridLines = False
ws3.merge_cells("A1:G1"); ws3["A1"] = "2026 年客户、课题组与产品结构"; ws3["A1"].font = F_TITLE
section(ws3, 3, "客户单位 Top10（ERP 客户字段）", 7)
header(ws3, 4, ["客户", "金额（万）", "占比", "订单数", "", "", ""])
r = 5
for k, v in sorted(customer_sales.items(), key=lambda x: -x[1])[:10]:
    body(ws3, r, [k, round(v/10000,4), v/sales_total, len(customer_orders[k]), "", "", ""], numfmt="0.00",
         aligns=[LEFT, RIGHT, "0.0%", CENTER, LEFT, LEFT, LEFT]); r += 1
note(ws3, r, f"客户单位 {len(customer_sales)} 个；Top1 浙江大学占 25.1%，Top5 占 45.5%。客户字段是单位，不是 PI 课题组。", 7)

r += 2
section(ws3, r, "PI / 课题组（2026 年有金额，截至 11-11）", 7); r += 1
header(ws3, r, ["指标", "数值", "说明", "", "", "", ""]); r += 1
pi_rows = [
    ("有金额课题组数", len(pi_pay), "2026 年有金额的 PI/课题组"),
    ("课题组金额合计（万）", round(pi_total/10000,4), "与 ERP 销售 44.28 万差 0.58 万"),
    ("单课题组均值（元）", round(pi_avg,0), "中位数 1,000 元"),
    ("单课题组中位数（元）", round(pi_median,0), "一半课题组年采购低于 1,000 元"),
    ("2026 新成交课题组", len(pi_new), f"金额 {sum(float(x['amt_p26']) for x in pi_new)/10000:.2f} 万"),
    ("复购课题组（≥2 单）", len(pi_repeat), f"复购率 {len(pi_repeat)/len(pi_pay)*100:.1f}%"),
    ("≥1 万元课题组", pi_buckets["≥1万"], "5 个，合计贡献约 9.16 万"),
    ("<1 千元课题组", pi_buckets["<1千"], "94 个，长尾明显"),
]
for k, v, t in pi_rows:
    body(ws3, r, [k, v, t, "", "", "", ""], numfmt="#,##0" if isinstance(v,(int,float)) else None,
         aligns=[LEFT, RIGHT, LEFT, LEFT, LEFT, LEFT, LEFT]); r += 1

r += 1
section(ws3, r, "Top10 课题组", 7); r += 1
header(ws3, r, ["课题组", "区域", "销售", "金额（万）", "订单数", "", ""]); r += 1
for x in sorted(pi_pay, key=lambda x: -float(x["amt_p26"]))[:10]:
    body(ws3, r, [x.get("name"), x.get("region"), x.get("sales"), round(float(x["amt_p26"])/10000,4), x.get("orders_paid"), "", ""],
         numfmt="0.00", aligns=[LEFT, CENTER, CENTER, RIGHT, CENTER, LEFT, LEFT]); r += 1

r += 1
section(ws3, r, "产品结构（按产品组）", 7); r += 1
header(ws3, r, ["产品组", "金额（万）", "占比", "", "", "", ""]); r += 1
for k, v in sorted(product_group.items(), key=lambda x: -x[1])[:8]:
    body(ws3, r, [k, round(v/10000,4), v/sales_total, "", "", "", ""], numfmt="0.00",
         aligns=[LEFT, RIGHT, "0.0%", LEFT, LEFT, LEFT, LEFT]); r += 1

r += 1
section(ws3, r, "销售员（2026 年 1–10 月）", 7); r += 1
header(ws3, r, ["销售员", "区域", "状态", "金额（万）", "订单数", "", ""]); r += 1
for s, v in sorted(person_sales.items(), key=lambda x: -x[1]):
    body(ws3, r, [s, people[s]["region"], people[s]["status"], round(v/10000,4), person_orders[s], "", ""],
         numfmt="0.00", aligns=[LEFT, CENTER, CENTER, RIGHT, CENTER, LEFT, LEFT]); r += 1
body(ws3, r, ["在职小计", "", "", round(active_sales/10000,4), "", "", ""], bold=True, fill=FILL_GREEN, numfmt="0.00", aligns=[LEFT,CENTER,CENTER,RIGHT,CENTER,LEFT,LEFT]); r += 1
body(ws3, r, ["离职小计", "", "", round(departed_sales/10000,4), "", "", ""], bold=True, fill=FILL_ORANGE, numfmt="0.00", aligns=[LEFT,CENTER,CENTER,RIGHT,CENTER,LEFT,LEFT])

# ---------- 04 expense ----------
ws4 = wb.create_sheet("04_2026费用实际")
widths(ws4, {"A": 26, "B": 14, "C": 16, "D": 14, "E": 14, "F": 14, "G": 16})
ws4.sheet_view.showGridLines = False
ws4.merge_cells("A1:G1"); ws4["A1"] = "2026 年费用实际（1–9 月）与预算对比"; ws4["A1"].font = F_TITLE
section(ws4, 3, "按费用科目（万元）", 7)
header(ws4, 4, ["科目", "2026预算", "2026实际1–9月", "预算执行率", "2025实际", "同比", "说明"])
r = 5
cat_order = [
    ("差旅费-交通费", 46800, "差旅费-交通费"),
    ("差旅费-差旅补贴", 5760, "差旅费-差旅补贴"),
    ("差旅费-住宿费", 21600, "差旅费-住宿费"),
    ("业务招待费", 216000, "业务招待费"),
    ("会务费", 96120, "会务费"),
    ("客户测试物料", 0, "客户测试物料"),
    ("办公费用-办公用品", 0, "办公费用-办公用品"),
    ("咨询服务费", 0, None),
    ("低值易耗/其他", 0, "物料消耗-低值易耗品领用"),
]
for name, bud, actual_key in cat_order:
    if name == "咨询服务费":
        act = summary26.get("咨询服务费-其他", 0)
        old = actual25.get("咨询服务费-其他", 0)
    elif name == "低值易耗/其他":
        act = exp_cat.get("物料消耗-低值易耗品领用", 0)
        old = 0
    else:
        act = summary26.get(actual_key, exp_cat.get(actual_key, 0))
        old = actual25.get(actual_key, 0)
    body(ws4, r, [name, round(bud/10000,4), round(act/10000,4), act/bud if bud else None, round(old/10000,4),
                  (act/old-1) if old >= 1000 else None, ""], numfmt="0.00",
         aligns=[LEFT, RIGHT, RIGHT, "0.0%", RIGHT, "0.0%", LEFT]); r += 1
body(ws4, r, ["合计", round(budget26_total/10000,4), round(exp_total/10000,4), exp_total/budget26_total,
              round(actual25_total/10000,4), exp_total/actual25_total-1, "实际只到 9 月"],
     bold=True, fill=FILL_LIGHT, numfmt="0.00", aligns=[LEFT, RIGHT, RIGHT, "0.0%", RIGHT, "0.0%", LEFT])
r += 2
section(ws4, r, "按区域（万元）", 7); r += 1
header(ws4, r, ["区域", "2026实际1–9月", "占费用", "2026销售1–10月", "费用/销售", "", ""]); r += 1
for reg in ["浙江", "广东", "生命科学销售部", "市场部"]:
    amt = exp_region.get(reg, 0)
    sale = region_total.get(reg, 0)
    body(ws4, r, [reg, round(amt/10000,4), amt/exp_total, round(sale/10000,4), amt/sale if sale else None, "", ""],
         numfmt="0.00", aligns=[LEFT, RIGHT, "0.0%", RIGHT, "0.0%", LEFT, LEFT]); r += 1
body(ws4, r, ["合计", round(exp_total/10000,4), 1.0, round(sales_total/10000,4), exp_total/sales_total, "", ""],
     bold=True, fill=FILL_LIGHT, numfmt="0.00", aligns=[LEFT, RIGHT, "0.0%", RIGHT, "0.0%", LEFT, LEFT])
r += 2
section(ws4, r, "按月（万元）", 7); r += 1
header(ws4, r, ["月份", "金额", "累计", "占全年", "", "", ""]); r += 1
cum = 0
for m in range(1, 10):
    a = exp_month.get(str(m), 0)/10000; cum += a
    body(ws4, r, [f"{m}月", round(a,4), round(cum,4), cum/(exp_total/10000), "", "", ""], numfmt="0.00",
         aligns=[CENTER, RIGHT, RIGHT, "0.0%", LEFT, LEFT, LEFT]); r += 1
note(ws4, r+1, "注：2026 年费用实际只到 9 月；10 月未入账。2026 年预算 38.63 万，1–9 月执行 10.44 万（27.0%）；按 9 个月年化 13.92 万（36.0%）。", 7)

# chart expense by month
ch2 = BarChart(); ch2.title = "2026 年 1–9 月费用（万元）"; ch2.y_axis.title = "万元"
data2 = Reference(ws4, min_col=2, min_row=26, max_row=34)
cats2 = Reference(ws4, min_col=1, min_row=26, max_row=34)
ch2.add_data(data2, titles_from_data=False); ch2.set_categories(cats2)
ch2.height = 8; ch2.width = 16
ws4.add_chart(ch2, "I4")

# ---------- 05 target ----------
ws5 = wb.create_sheet("05_2027目标测算")
widths(ws5, {"A": 18, "B": 14, "C": 14, "D": 14, "E": 14, "F": 14, "G": 20})
ws5.sheet_view.showGridLines = False
ws5.merge_cells("A1:G1"); ws5["A1"] = "2027 年销售目标测算：400 万如何拆"; ws5["A1"].font = F_TITLE
section(ws5, 3, "总量与增长", 7)
header(ws5, 4, ["指标", "2025实际", "2026年1–10月", "2026年化", "2027目标", "增长（对2026年化）", "说明"])
r = 5
body(ws5, r, ["销售额（万）", 28.47, round(sales_total/10000,4), round(annualized_sales/10000,4), TARGET27,
              TARGET27/(annualized_sales/10000)-1, "2025 仅覆盖原科研销售，参考"], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, "0.0%", LEFT]); r += 1
body(ws5, r, ["广东（万）", None, round(region_total["广东"]/10000,4), round(region_total["广东"]/10*12/10000,4), 200,
              200/(region_total["广东"]/10*12/10000)-1, "2026 年化 19.5 万"], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, "0.0%", LEFT]); r += 1
body(ws5, r, ["浙江（万）", None, round(region_total["浙江"]/10000,4), round(region_total["浙江"]/10*12/10000,4), 200,
              200/(region_total["浙江"]/10*12/10000)-1, "2026 年化 33.7 万"], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, "0.0%", LEFT]); r += 2

section(ws5, r, "季度拆分（万元）", 7); r += 1
header(ws5, r, ["季度", "广东", "浙江", "合计", "占全年", "累计", "说明"]); r += 1
region_q = {"Q1": (30, 30), "Q2": (50, 50), "Q3": (55, 55), "Q4": (65, 65)}
cum = 0
for q in ["Q1", "Q2", "Q3", "Q4"]:
    gd, zj = region_q[q]; tot = gd + zj; cum += tot
    body(ws5, r, [q, gd, zj, tot, tot/TARGET27, cum, ""], numfmt="0.00",
         aligns=[CENTER, RIGHT, RIGHT, RIGHT, "0.0%", RIGHT, LEFT]); r += 1
body(ws5, r, ["合计", 200, 200, 400, 1.0, 400, ""], bold=True, fill=FILL_LIGHT, numfmt="0.00",
     aligns=[CENTER, RIGHT, RIGHT, RIGHT, "0.0%", RIGHT, LEFT]); r += 2

section(ws5, r, "400 万需要多少课题组／PI", 7); r += 1
header(ws5, r, ["单课题组年均产出", "需要的课题组数", "对比 2026 有金额课题组 192 个", "判断", "", "", ""]); r += 1
for arpu in [2276, 5000, 8000, 10000, 20000]:
    need = TARGET27*10000/arpu
    body(ws5, r, [f"{arpu:,} 元", round(need), f"{need/len(pi_pay):.1f} 倍", "靠数量" if need>800 else ("数量+客单价" if need>400 else "靠客单价")],
         numfmt="#,##0", aligns=[LEFT, RIGHT, RIGHT, LEFT, LEFT, LEFT, LEFT]); r += 1
note(ws5, r, "2026 年有金额课题组 192 个，合计 43.70 万，均值 2,276 元、中位数 1,000 元；5 个课题组≥1 万，94 个<1 千。", 7)

r += 2
section(ws5, r, "人效与人员（目标不依赖新增人员）", 7); r += 1
header(ws5, r, ["指标", "2026年1–10月", "2027目标", "变化", "说明", "", ""]); r += 1
body(ws5, r, ["在职销售人数", 8, 8, "0", "基准按 8 人；新增 2 人仅作加速选项"], numfmt="0",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, LEFT, LEFT, LEFT]); r += 1
body(ws5, r, ["人均销售（万，8 人）", round(active_sales/10000/8,4), round(400/8,4), round((400/8)/(active_sales/10000/8),1), "不新增也要达成 50 万/人"],
     numfmt="0.00", aligns=[LEFT, RIGHT, RIGHT, "0.0x", LEFT, LEFT, LEFT]); r += 1
body(ws5, r, ["人均销售（万，10 人）", round(active_sales/10000/8,4), round(400/10,4), round((400/10)/(active_sales/10000/8),1), "仅作新增 2 人的加速情景"],
     numfmt="0.00", aligns=[LEFT, RIGHT, RIGHT, "0.0x", LEFT, LEFT, LEFT]); r += 1
body(ws5, r, ["人均有金额课题组", round(len(pi_pay)/8,1), 50, round(50/(len(pi_pay)/8),1), "400 个 PI / 8 人；新增人不降目标"],
     numfmt="0.0", aligns=[LEFT, RIGHT, RIGHT, "0.0x", LEFT, LEFT, LEFT]); r += 1

# ---------- 06 budget ----------
ws6 = wb.create_sheet("06_2027费用预算")
widths(ws6, {"A": 26, "B": 14, "C": 14, "D": 14, "E": 14, "F": 14, "G": 26})
ws6.sheet_view.showGridLines = False
ws6.merge_cells("A1:G1"); ws6["A1"] = "2027 年费用预算建议：B 档（冲刺）100 万 / 销售 400 万"; ws6["A1"].font = F_TITLE
section(ws6, 3, "按科目（万元）", 7)
header(ws6, 4, ["科目", "官方模板行", "2027预算", "2026实际1–9月", "预算/2026实际", "占销售", "配置说明"])
r = 5
for name, row, bud, act, desc in BUDGET_CAT:
    body(ws6, r, [name, row, bud, round(act,4), bud/act if act else None, bud/TARGET27, desc], numfmt="0.00",
         aligns=[LEFT, CENTER, RIGHT, RIGHT, "0.0x", "0.0%", LEFT]); r += 1
body(ws6, r, ["合计", "", round(EXP_TOTAL27,4), round(exp_total/10000,4), EXP_TOTAL27/(exp_total/10000), EXP_TOTAL27/TARGET27, ""],
     bold=True, fill=FILL_LIGHT, numfmt="0.00", aligns=[LEFT, CENTER, RIGHT, RIGHT, "0.0x", "0.0%", LEFT]); r += 2

section(ws6, r, "按季度（万元）", 7); r += 1
header(ws6, r, ["季度", "费用预算", "占全年", "对应销售", "费用率", "累计费用", ""]); r += 1
cum = 0
for q in ["Q1", "Q2", "Q3", "Q4"]:
    cum += EXP_Q[q]
    body(ws6, r, [q, EXP_Q[q], EXP_Q[q]/EXP_TOTAL27, SALES_Q[q], EXP_Q[q]/SALES_Q[q], cum, ""], numfmt="0.00",
         aligns=[CENTER, RIGHT, "0.0%", RIGHT, "0.0%", RIGHT, LEFT]); r += 1
body(ws6, r, ["合计", EXP_TOTAL27, 1.0, TARGET27, EXP_TOTAL27/TARGET27, EXP_TOTAL27, ""], bold=True, fill=FILL_LIGHT, numfmt="0.00",
     aligns=[CENTER, RIGHT, "0.0%", RIGHT, "0.0%", RIGHT, LEFT]); r += 2

section(ws6, r, "按月（万元）", 7); r += 1
header(ws6, r, ["月份", "费用预算", "累计", "对应销售", "费用率", "", ""]); r += 1
cum = 0
for m in ["1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"]:
    cum += EXP_M[m]
    body(ws6, r, [m, EXP_M[m], cum, SALES_M[m], EXP_M[m]/SALES_M[m], "", ""], numfmt="0.00",
         aligns=[CENTER, RIGHT, RIGHT, RIGHT, "0.0%", LEFT, LEFT]); r += 1
body(ws6, r, ["合计", EXP_TOTAL27, EXP_TOTAL27, TARGET27, EXP_TOTAL27/TARGET27, "", ""], bold=True, fill=FILL_LIGHT, numfmt="0.00",
     aligns=[CENTER, RIGHT, RIGHT, RIGHT, "0.0%", LEFT, LEFT]); r += 2

section(ws6, r, "三档情景（万元）", 7); r += 1
header(ws6, r, ["情景", "费用预算", "费用率", "H1", "H2", "对应销售", "适用条件"]); r += 1
for name, amt, desc in [("保守", 90, "按 22.5% 费用率；只做现有区域深耕"), ("建议", 100, "按 25%；覆盖广东补强+浙江复购"), ("进取", 110, "按 27.5%；新增 2 人+校园行加密")]:
    body(ws6, r, [name, amt, amt/TARGET27, amt*0.55, amt*0.45, TARGET27, desc], numfmt="0.00",
         aligns=[CENTER, RIGHT, "0.0%", RIGHT, RIGHT, RIGHT, LEFT]); r += 1
note(ws6, r+1, "注：90/100/110 是 400 万销售目标下的费用弹性，不是两档销售目标；A 档保底 300/80 见 07 表。", 7)

# ---------- 07 two tiers ----------
ws7 = wb.create_sheet("07_高低两档预算")
widths(ws7, {"A": 16, "B": 14, "C": 14, "D": 14, "E": 14, "F": 14, "G": 14, "H": 14, "I": 24})
ws7.sheet_view.showGridLines = False
ws7.merge_cells("A1:I1")
ws7["A1"] = "2027 年高低两档预算：A 档保底 300 万 / B 档冲刺 400 万"
ws7["A1"].font = F_TITLE

section(ws7, 3, "一、两档总表（万元）", 9)
header(ws7, 4, ["档位", "销售目标", "费用预算", "费用率", "H1 销售", "H2 销售", "H1 费用", "H2 费用", "定位"])
body(ws7, 5, ["A 档 保底", 300, 80, 80/300, 120, 180, 46, 34, "季度滚动基线，守住基本盘"],
     numfmt="0.00", aligns=[LEFT, RIGHT, RIGHT, "0.0%", RIGHT, RIGHT, RIGHT, RIGHT, LEFT])
body(ws7, 6, ["B 档 冲刺", 400, 100, 100/400, 160, 240, 55, 45, "正式上报档，按 400 万配置"],
     numfmt="0.00", aligns=[LEFT, RIGHT, RIGHT, "0.0%", RIGHT, RIGHT, RIGHT, RIGHT, LEFT])
body(ws7, 7, ["B − A 增量", 100, 20, 20/100, 40, 60, 9, 11, "增量费用率 20%，只投广东和课题组获取"],
     bold=True, fill=FILL_LIGHT, numfmt="0.00", aligns=[LEFT, RIGHT, RIGHT, "0.0%", RIGHT, RIGHT, RIGHT, RIGHT, LEFT])

section(ws7, 9, "二、季度销售（万元）", 9)
header(ws7, 10, ["档位", "Q1", "Q2", "Q3", "Q4", "合计", "", "", ""])
body(ws7, 11, ["A 档 保底", 45, 75, 85, 95, 300, "", "", ""], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, RIGHT, LEFT, LEFT, LEFT])
body(ws7, 12, ["B 档 冲刺", 60, 100, 110, 130, 400, "", "", ""], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, RIGHT, LEFT, LEFT, LEFT])

section(ws7, 14, "三、季度费用（万元）", 9)
header(ws7, 15, ["档位", "Q1", "Q2", "Q3", "Q4", "合计", "", "", ""])
body(ws7, 16, ["A 档 保底", 18, 28, 20, 14, 80, "", "", ""], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, RIGHT, LEFT, LEFT, LEFT])
body(ws7, 17, ["B 档 冲刺", 20, 35, 25, 20, 100, "", "", ""], numfmt="0.00",
     aligns=[LEFT, RIGHT, RIGHT, RIGHT, RIGHT, RIGHT, LEFT, LEFT, LEFT])

section(ws7, 19, "四、使用规则", 9)
rules = [
    "正式预算只报 B 档 400/100；A 档作为季度滚动基线，不单独形成第二套上报口径。",
    "费用按 B 档配置，但 Q1/Q2 先释放 A 档额度；达到季度里程碑后再释放 B 档增量费用。",
    "H1 销售低于 A 档 120 万，冻结 B 档增量费用；H1 达到 160 万，Q3 起按 B 档全额释放。",
    "人员不新增也按 B 档目标考核；新增 2 人只改变费用结构，不降低 400 万目标。",
    "收入来源不限定，试剂、服务、仪器等均计入 400 万；预算不锁死产品结构。",
]
r = 20
for t in rules:
    ws7.cell(r, 1, "• " + t).font = F_BODY
    ws7.merge_cells(start_row=r, start_column=1, end_row=r, end_column=9)
    ws7.cell(r, 1).alignment = LEFT
    ws7.cell(r, 1).border = BORDER
    ws7.row_dimensions[r].height = 30
    r += 1

# ---------- save ----------
for s in wb.worksheets:
    s.sheet_properties.pageSetUpPr.fitToPage = True
    s.page_setup.fitToWidth = 1
    s.page_setup.fitToHeight = 0
    s.freeze_panes = "A5"
wb.save(OUT)
print("OUT", OUT)
print("sales", round(sales_total/10000,4), "region", {k:round(v/10000,4) for k,v in region_total.items()})
print("target_ytd", target_ytd, "annualized", round(annualized_sales/10000,4))
print("expense", round(exp_total/10000,4), "annualized", round(exp_annualized/10000,4), "budget26", round(budget26_total/10000,4))
print("PI", len(pi_pay), round(pi_total/10000,4), round(pi_avg,2), pi_buckets)