#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""回填 2027 诊断原料销售部官方预算模板：单档 6,500 万，费用 154.77 万。"""
import os, shutil
import openpyxl
from openpyxl.comments import Comment

SRC = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/8779abc0a0c9c7cbdf9cfc76423e8fab8c80f780cccc16316b274e5525235464/doc_a4ac68b6f2e8_2027全面预算表 -诊断原料销售部.xlsx"
OUT = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/资料/2027预算回填_20261112/2027全面预算表 -诊断原料销售部_回填_20261112.xlsx"

# 差旅：2026年1-9月实际年化×1.2=482,963.66；246人次，住宿200/天、差补100/天
TRAVEL = [
    # 部门/人员, 月份, 内容, 人数, 次数/人, 天数, 交通/次, 市内/天, 住宿/天, 差补/天
    ("驻地业务员（天津、武汉、苏州、成都）", "1-12", "拜访客户", 3, 20, 3, 1179.52, 80, 200, 100),
    ("驻地业务员（广州）", "1-12", "拜访客户", 2, 20, 3, 479.52, 80, 200, 100),
    ("驻地业务员（广东）", "1,7", "返回公司开会", 3, 3, 4, 479.52, 80, 0, 0),
    ("驻地业务员（外省）", "1,7", "返回公司开会", 11, 3, 4, 1179.52, 80, 0, 0),
    ("部门经理", "1-12", "拜访客户", 1, 42, 3, 1179.52, 80, 200, 100),
    ("部门经理", "1,7", "返回公司开会", 1, 21, 3, 1179.52, 80, 0, 0),
    ("部门总监", "1-12", "拜访客户", 1, 41, 3, 1179.5624, 80, 200, 100),
]

CONF = [
    # 主导部门, 展会名称, 内容, 时间, 地点, 参加人员, 展会费, 搭建费, 差旅费, 礼品, 月份, 备注
    ("诊断原料销售部", "CACLP", "2027中国国际检验医学暨输血仪器试剂博览会", "2027-03", "厦门", "销售5+技术3+高层3+市场1", 80000, 55500, 0, 5000, "3", "2026预算180,680×77.76%=140,500"),
    ("诊断原料销售部", "区域定制会（5月）", "广东/浙江区域客户会", "2027-05", "广东/浙江", "销售1+技术1+高层1", 20000, 5000, 0, 650, "5", "按2026区域会预算折算"),
    ("诊断原料销售部", "区域定制会（8月）", "广东/浙江区域客户会", "2027-08", "广东/浙江", "销售1+技术1+高层1", 20000, 5000, 0, 650, "8", "按2026区域会预算折算"),
    ("诊断原料销售部", "NGS专题会议", "NGS产品迭代推广", "2027-07", "待定", "销售1+技术1+高层1", 20000, 5000, 0, 681.39, "7", "2026预算33,000×77.76%=25,681"),
]

HOSP = [
    # 人员, 事务, 对象, 形式, 次数, 每次金额, 月份, 备注
    ("驻地业务员（主管+大客户）6人", "客情维护", "客户", "日常伴手礼等", 12, 1117.67, "1-12", "2026预算21,600×62.1%=13,412"),
    ("驻地业务员（外省）8人", "客情维护", "客户", "日常伴手礼等", 12, 1490.22, "1-12", "2026预算28,800×62.1%=17,883"),
    ("部门经理", "客情维护", "客户", "日常伴手礼等", 12, 310.47, "1-12", "2026预算6,000×62.1%=3,726"),
    ("部门总监", "客情维护", "客户", "日常伴手礼等", 12, 310.47, "1-12", "2026预算6,000×62.1%=3,726"),
    ("诊断原料销售部", "客户来公司参观", "客户", "餐费/住宿/酒水", 24, 662.3783, "1-12", "2026预算25,600×62.1%=15,897"),
]

TEST_MONTHS = [51098.03, 30044.15, 81606.20, 57746.78, 122561.12, 78005.50, 62386.31, 46700.09, 60938.41, 65676.29, 65676.29, 65676.29]
MONTH_COLS_31 = ["O", "S", "W", "AA", "AE", "AI", "AM", "AQ", "AU", "AY", "BC", "BG"]


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


def fill_travel(ws):
    for r, (person, months, content, people, times, days, traffic, local, hotel, per_diem) in enumerate(TRAVEL, 4):
        ws[f"B{r}"] = r - 3
        ws[f"C{r}"] = "诊断原料销售部"
        ws[f"D{r}"] = person
        ws[f"E{r}"] = months
        ws[f"F{r}"] = content
        ws[f"G{r}"] = people * times
        ws[f"H{r}"] = days
        ws[f"I{r}"] = traffic
        ws[f"J{r}"] = local
        ws[f"L{r}"] = hotel
        ws[f"N{r}"] = per_diem
        note = f"{people}人×{times}次={people*times}人次×{days}天；交通{traffic:,.2f}/次、市内{local}/天、住宿{hotel}/天、差补{per_diem}/天。"
        cmt(ws, f"Q{r}", f"预算逻辑：{note}")


def fill_conf(ws):
    for r, row in enumerate(CONF, 4):
        dept, name, content, when, place, people, expo, build, travel, gift, month, note = row
        ws[f"B{r}"] = r - 3
        ws[f"C{r}"] = dept
        ws[f"D{r}"] = name
        ws[f"E{r}"] = content
        ws[f"F{r}"] = when
        ws[f"G{r}"] = place
        ws[f"H{r}"] = people
        ws[f"I{r}"] = expo
        ws[f"J{r}"] = build
        ws[f"K{r}"] = travel
        ws[f"L{r}"] = gift
        ws[f"N{r}"] = month
        ws[f"O{r}"] = note
        cmt(ws, f"M{r}", f"预算逻辑：{note}；小计={expo+build+travel+gift:,.2f}元。")


def fill_hosp(ws):
    for r, (person, affair, obj, form, times, unit, months, note) in enumerate(HOSP, 3):
        ws[f"B{r}"] = r - 2
        ws[f"C{r}"] = "诊断原料销售部"
        ws[f"D{r}"] = affair
        ws[f"E{r}"] = obj
        ws[f"F{r}"] = form
        ws[f"G{r}"] = times
        ws[f"H{r}"] = unit
        ws[f"J{r}"] = months
        ws[f"K{r}"] = note
        cmt(ws, f"I{r}", f"预算逻辑：{times}×{unit:,.2f}={times*unit:,.2f}元；{person}。")


def fill_consult(ws):
    r = 72
    ws[f"C{r}"] = 1
    ws[f"D{r}"] = "诊断原料销售部"
    ws[f"E{r}"] = "其他咨询服务"
    ws[f"F{r}"] = "2026年实际2,787.62元年化3,716.83×1.2"
    ws[f"H{r}"] = 1
    ws[f"I{r}"] = 4460.19
    ws[f"K{r}"] = "1-12"
    cmt(ws, f"J{r}", "预算逻辑：2026年1-9月咨询服务费2,787.62元，年化3,716.83元×1.2=4,460.19元。")


def fill_test(ws):
    for col, v in zip(MONTH_COLS_31, TEST_MONTHS):
        ws[f"{col}44"] = v
    cmt(ws, "J44", "预算逻辑：2026年1-9月客户测试物料492,572.16元，年化656,762.88元×1.2=788,115.46元；按2026年1-9月月度结构×1.2分摊，10-12月按1-9月均值。")


def main():
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    shutil.copy2(SRC, OUT)
    wb = openpyxl.load_workbook(OUT)
    fill_travel(wb["附表（二）-差旅费"])
    fill_conf(wb["附表（七）-会务费"])
    fill_hosp(wb["附表（五）-业务招待费"])
    fill_consult(wb["附表（三）-咨询服务费"])
    fill_test(wb["3-1期间费用"])
    # 4-1 要员计划、5-资本性支出：2027 不新增人员、不新增资产，留空
    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__":
    main()