#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""按用户确认口径回填 2027 生命科学销售部官方预算模板。
只写文字/数字，不改公式、样式、合并单元格、科目。
B 档正式，A 档单独副本。
"""
import os, shutil
import openpyxl
from openpyxl.comments import Comment
from openpyxl.utils import get_column_letter

SRC = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/30920b28c4bbba7e7dab1fe39339a91748b6a3fb562435f192c9a021aa225284/doc_aaeae3f7ee00_2027全面预算表 -生命科学销售部.xlsx"
OUTDIR = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/资料/2027预算回填_20261112"
os.makedirs(OUTDIR, exist_ok=True)
OUT_B = os.path.join(OUTDIR, "2027全面预算表 -生命科学销售部_B档_正式_20261112.xlsx")
OUT_A = os.path.join(OUTDIR, "2027全面预算表 -生命科学销售部_A档_备选_20261112.xlsx")

# names / regions
PEOPLE = [
    # name, region, role, months, trips, transport, note
    ("韩远怀", "广东", "广东区域经理", "1-12", 12, 300, "每月回珠海总部1次；单程300元含往返；住公司宿舍；差补80元/天×3天"),
    ("吴云", "浙江", "浙江区域经理", "1-12", 12, 2000, "每月回珠海总部1次；单程2,000元含往返；住公司宿舍；差补80元/天×3天"),
    ("黄建华", "广东", "广州科研", "1,4,7,10", 4, 300, "每季度回珠海总部1次；单程300元含往返；住公司宿舍；差补80元/天×3天"),
    ("王诗香", "广东", "广州科研", "1,4,7,10", 4, 300, "每季度回珠海总部1次；单程300元含往返；住公司宿舍；差补80元/天×3天"),
    ("新增-广东", "广东", "科研销售（新增）", "6,9,12", 3, 300, "2027年4月到位；Q2-Q4各回总部1次；单程300元含往返；住公司宿舍；差补80元/天×3天"),
    ("袁殿果", "浙江", "浙江科研", "1,4,7,10", 4, 2000, "每季度回珠海总部1次；单程2,000元含往返；住公司宿舍；差补80元/天×3天"),
    ("叶信福", "浙江", "浙江科研", "1,4,7,10", 4, 2000, "每季度回珠海总部1次；单程2,000元含往返；住公司宿舍；差补80元/天×3天"),
    ("侯海瑜", "浙江", "浙江科研", "1,4,7,10", 4, 2000, "每季度回珠海总部1次；单程2,000元含往返；住公司宿舍；差补80元/天×3天"),
    ("张晋菲", "浙江", "浙江科研", "1,4,7,10", 4, 2000, "每季度回珠海总部1次；单程2,000元含往返；住公司宿舍；差补80元/天×3天"),
    ("新增-浙江", "浙江", "科研销售（新增）", "6,9,12", 3, 2000, "2027年4月到位；Q2-Q4各回总部1次；单程2,000元含往返；住公司宿舍；差补80元/天×3天"),
]

B = dict(
    name="B档正式",
    travel_flex=0,
    hospitality={"广东": 33600, "浙江": 50400},
    promotion=[("线上学术推广", "公众号/视频号内容投放", 4, 10000, "3,6,9,12", "4期×1万/期；按季度投放")],
    conference=6000,
    conference_rows=12,
    recruitment=30000,
    training=20000,
    consulting=20000,
    other=0,
    welfare=20000,
    test=[15350.50,3540.70,18077.35,9897.40,7200.55,9057.95,7925.35,11336.30,23782.15,14793.97,15393.47,15992.96],
    express=0,
)
A = dict(
    name="A档备选",
    travel_flex=0,
    hospitality={"广东": 33600, "浙江": 50400},
    promotion=[("线上学术推广", "公众号/视频号内容投放", 4, 10000, "3,6,9,12", "4期×1万/期；按季度投放")],
    conference=6000,
    conference_rows=6,
    recruitment=30000,
    training=20000,
    consulting=20000,
    other=0,
    welfare=20000,
    test=[11512.88,2655.52,13558.01,7423.05,5400.41,6793.46,5944.01,8502.22,17836.61,11095.48,11545.10,11994.75],
    express=0,
)

MONTH_COLS_3_1 = ["O", "S", "W", "AA", "AE", "AI", "AM", "AQ", "AU", "AY", "BC", "BG"]


def cmt(ws, coord, text):
    c = ws[coord]
    if c.comment:
        c.comment = Comment(text, "销售管理部")
    else:
        c.comment = Comment(text, "销售管理部")


def fill_travel(ws, sc):
    # 附表（二） 行4起；只写输入列 C D E F G H I J L N；K/M/O/P/Q/T/U 公式不动
    r = 4
    for name, region, role, months, trips, transport, logic in PEOPLE:
        ws[f"B{r}"] = r - 3
        ws[f"C{r}"] = "生命科学销售部"
        ws[f"D{r}"] = f"{name}/{role}"
        ws[f"E{r}"] = months
        ws[f"F{r}"] = f"回珠海总部：{logic}"
        ws[f"G{r}"] = trips
        ws[f"H{r}"] = 3
        ws[f"I{r}"] = transport
        ws[f"J{r}"] = 0
        ws[f"L{r}"] = 0
        ws[f"N{r}"] = 80
        annual = trips * transport + trips * 3 * 80
        cmt(ws, f"Q{r}", f"预算逻辑：{trips}次×{transport}元/次 + {trips}次×3天×80元/天 = {annual:,}元。{logic}")
        r += 1
    # 区域客户拜访包干机动：本版删除，仅保留总部往返
    if sc["travel_flex"] > 0:
        ws[f"B{r}"] = r - 3
        ws[f"C{r}"] = "生命科学销售部"
        ws[f"D{r}"] = "区域客户拜访（广东/浙江）"
        ws[f"E{r}"] = "1-12"
        ws[f"F{r}"] = "区域客户拜访包干机动：覆盖重点课题组复访、2027年新PI开发；含交通/差补，按实际发生控制"
        ws[f"G{r}"] = 1
        ws[f"H{r}"] = 1
        ws[f"I{r}"] = sc["travel_flex"]
        ws[f"J{r}"] = 0
        ws[f"L{r}"] = 0
        ws[f"N{r}"] = 0
        cmt(ws, f"Q{r}", f"预算逻辑：区域客户拜访包干机动 {sc['travel_flex']:,}元；不拆个人次数，按季度审批、凭实际发生列支。")
        r += 1
    return r


def fill_hospitality(ws, sc):
    # 附表（五） 行3起；只写 C D E F G H J K；I 公式
    r = 3
    total = sc["hospitality"]["广东"] + sc["hospitality"]["浙江"]
    for region, amount in (("广东", sc["hospitality"]["广东"]), ("浙江", sc["hospitality"]["浙江"])):
        people = 4 if region == "广东" else 6
        trips = people * 12
        per = 700
        ws[f"B{r}"] = r - 2
        ws[f"C{r}"] = "生命科学销售部"
        ws[f"D{r}"] = f"客户日常客情（{region}）"
        ws[f"E{r}"] = "客户"
        ws[f"F{r}"] = "餐饮/咖啡/茶歇/小礼品"
        ws[f"G{r}"] = trips
        ws[f"H{r}"] = per
        ws[f"J{r}"] = "1-12"
        ws[f"K{r}"] = f"预算逻辑：{people}人×每月1次×700元/次，年{amount/10000:.2f}万；全年两区域合计{total/10000:.2f}万"
        cmt(ws, f"I{r}", f"预算逻辑：{people}人×12次×700元/次 = {trips*per:,}元。")
        r += 1


def fill_promotion(ws, sc):
    # 附表（六） 行3起；只写 C D E F G I J；H 公式
    r = 3
    for name, content, times, unit, months, logic in sc["promotion"]:
        ws[f"B{r}"] = r - 2
        ws[f"C{r}"] = name
        ws[f"D{r}"] = content
        ws[f"E{r}"] = "生命科学"
        ws[f"F{r}"] = times
        ws[f"G{r}"] = unit
        ws[f"I{r}"] = months
        ws[f"J{r}"] = f"预算逻辑：{times}×{unit:,}元={times*unit:,}元；{logic}"
        cmt(ws, f"H{r}", f"预算逻辑：{times}×{unit:,}元 = {times*unit:,}元。")
        r += 1


def fill_conference(ws, sc):
    # 附表（七） 行4起；每场会务3,000+物料/礼品3,000，差旅0
    if sc["conference_rows"] == 12:
        events = [
            ("广东", "2027-01", "广州/深圳", "1"), ("浙江", "2027-02", "杭州", "2"),
            ("广东", "2027-03", "广州/深圳", "3"), ("浙江", "2027-04", "杭州", "4"),
            ("广东", "2027-05", "广州/深圳", "5"), ("浙江", "2027-06", "杭州", "6"),
            ("广东", "2027-07", "广州/深圳", "7"), ("浙江", "2027-08", "杭州", "8"),
            ("广东", "2027-09", "广州/深圳", "9"), ("浙江", "2027-10", "杭州", "10"),
            ("广东", "2027-11", "广州/深圳", "11"), ("浙江", "2027-12", "杭州", "12"),
        ]
    else:
        events = [
            ("广东", "2027-03", "广州/深圳", "3"), ("浙江", "2027-04", "杭州", "4"),
            ("广东", "2027-07", "广州/深圳", "7"), ("浙江", "2027-08", "杭州", "8"),
            ("广东", "2027-11", "广州/深圳", "11"), ("浙江", "2027-12", "杭州", "12"),
        ]
    r = 4
    for i, (region, time, place, month) in enumerate(events, 1):
        ws[f"B{r}"] = i
        ws[f"C{r}"] = "生命科学销售部"
        ws[f"D{r}"] = f"2027校园行/科研展会（{region}）第{i}场"
        ws[f"E{r}"] = "重点高校/科研院所实验楼路演+技术讲座"
        ws[f"F{r}"] = time
        ws[f"G{r}"] = place
        ws[f"H{r}"] = f"{region}团队"
        ws[f"I{r}"] = 3000
        ws[f"J{r}"] = 0
        ws[f"K{r}"] = 0
        ws[f"L{r}"] = 3000
        ws[f"N{r}"] = month
        ws[f"O{r}"] = "预算逻辑：2026年楼展0.5次/月×2区域；每场会务3,000+物料/礼品3,000；差旅0"
        cmt(ws, f"M{r}", "预算逻辑：会务3,000+物料/礼品3,000=6,000元/场；差旅不列入。")
        r += 1


def fill_other(ws, sc):
    # 附表（十一） 行4起；只写 C D E F G I J；H 公式
    rows = [
        ("招聘费", "2027年新增2名科研销售（广东1、浙江1）招聘平台/猎头", 2, 15000, "4", sc["recruitment"]),
        ("培训费", "新产品培训+销售方法培训", 4, 5000, "5,9", sc["training"]),
    ]
    if sc["other"] > 0:
        rows.append(("其他", "低值易耗/不可预见", 1, sc["other"], "1-12", sc["other"]))
    r = 4
    for i, (name, driver, times, unit, months, amount) in enumerate(rows, 1):
        ws[f"B{r}"] = i
        ws[f"C{r}"] = "生命科学销售部"
        ws[f"D{r}"] = name
        ws[f"E{r}"] = driver
        ws[f"F{r}"] = times
        ws[f"G{r}"] = unit
        ws[f"I{r}"] = months
        ws[f"J{r}"] = f"预算逻辑：{times}×{unit:,}元={amount:,}元；{driver}"
        cmt(ws, f"H{r}", f"预算逻辑：{times}×{unit:,}元={amount:,}元。")
        r += 1


def fill_welfare(ws, sc):
    r = 3
    ws[f"B{r}"] = "1"
    ws[f"C{r}"] = "活动经费"
    ws[f"D{r}"] = "团队建设/节日活动"
    ws[f"E{r}"] = "10人×2次×1,000元/次"
    ws[f"F{r}"] = 2
    ws[f"G{r}"] = 10000
    ws[f"I{r}"] = "6,12"
    ws[f"J{r}"] = "预算逻辑：10人×2次×1,000元=20,000元；6月/12月各一次"
    cmt(ws, f"H{r}", "预算逻辑：2次×10,000元=20,000元。")


def fill_consulting(ws, sc):
    # 附表（三） 其他 section 行72
    r = 72
    ws[f"D{r}"] = "生命科学销售部"
    ws[f"E{r}"] = "科研平台/第三方服务费"
    ws[f"F{r}"] = "云采通平台、资质验证、第三方服务"
    ws[f"H{r}"] = 1
    ws[f"I{r}"] = sc["consulting"]
    ws[f"K{r}"] = "3,9"
    cmt(ws, f"J{r}", f"预算逻辑：1次×{sc['consulting']:,}元；3月/9月各半。")


def fill_office(ws, sc):
    # 附表（一） 行5：快递费本版删除，不填
    if sc["express"] > 0:
        ws[f"B5"] = "生命科学销售部"
        ws[f"M5"] = sc["express"]
        ws[f"N5"] = "1-12"
        cmt(ws, "M5", f"预算逻辑：快递费{sc['express']:,}元/年，按10人×约83元/月×12个月；样品/合同寄送。")


def fill_capex(ws):
    r = 5
    ws[f"C{r}"] = "生命科学销售部"
    ws[f"D{r}"] = "办公笔记本电脑"
    ws[f"E{r}"] = "常规配置"
    ws[f"F{r}"] = "办公设备"
    ws[f"G{r}"] = "2027年新增2名科研销售（广东1、浙江1）配装电脑"
    ws[f"H{r}"] = "不需要"
    ws[f"J{r}"] = 2
    ws[f"K{r}"] = 3000
    ws[f"P{r}"] = 6000
    cmt(ws, f"L{r}", "预算逻辑：2台×3,000元=6,000元；2027年4月随新增人员到位采购。")


def fill_headcount(ws):
    rows = [
        (5, "科研销售（广东）", "广东区域现有3人，2027年补强1人，支撑广东200万目标"),
        (6, "科研销售（浙江）", "浙江区域现有5人，2027年补强1人，支撑浙江200万目标"),
    ]
    for r, post, reason in rows:
        ws[f"B{r}"] = "生命科学销售部"
        ws[f"C{r}"] = post
        ws[f"D{r}"] = reason
        ws[f"E{r}"] = 1
        ws[f"I{r}"] = 1


def fill_test_monthly(ws31, values, amount_label):
    for col, v in zip(MONTH_COLS_3_1, values):
        ws31[f"{col}44"] = v
    cmt(ws31, "J44", f"预算逻辑：2026年1-9月客户测试物料2.12万；按1-9月线性趋势补全10-12月，2026全年约3.05万；2027销售400万/2026销售80万=5倍，测试物料B档15.23万（A档按300/400折算11.43万），按月按2026结构×5分摊。")


def fill_workbook(sc, out):
    shutil.copy2(SRC, out)
    wb = openpyxl.load_workbook(out)
    ws31 = wb["3-1期间费用"]
    fill_travel(wb["附表（二）-差旅费"], sc)
    fill_hospitality(wb["附表（五）-业务招待费"], sc)
    fill_promotion(wb["附表（六）-业务推广费"], sc)
    fill_conference(wb["附表（七）-会务费"], sc)
    fill_other(wb["附表（十一）-其他"], sc)
    fill_welfare(wb["附表（十二）-福利费"], sc)
    fill_consulting(wb["附表（三）-咨询服务费"], sc)
    fill_office(wb["附表（一）-办公费用"], sc)
    fill_capex(wb["5-资本性支出"])
    fill_headcount(wb["4-1要员计划"])
    fill_test_monthly(ws31, sc["test"], "17万" if sc["name"].startswith("B") else "14万")
    wb.calculation.fullCalcOnLoad = True
    wb.calculation.forceFullCalc = True
    wb.calculation.calcMode = "auto"
    wb.save(out)
    print("saved", out, os.path.getsize(out))


if __name__ == "__main__":
    fill_workbook(B, OUT_B)
    fill_workbook(A, OUT_A)