#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""回填 2027 市场部官方预算模板：单档 30 万，沿用 2026 预算结构（83.72%）。"""
import os, shutil
import openpyxl
from openpyxl.comments import Comment

SRC = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/dfbe76fee780c39a1a9b8f00684c4174558176e31776ccfd998c142b7af8b524/doc_da4ad27af2bb_2027全面预算表 -市场部.xlsx"
OUT = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/资料/2027预算回填_20261112/2027全面预算表 -市场部_回填_20261112.xlsx"

PROMO = [
    # 项目, 内容, 业务线, 次数/人数, 单价, 金额, 月份, 备注
    ("助销礼品", "笔记本、笔、背包、水杯、充电宝等", "诊断原料", 12, 1883.6017, 22603.22, "3,6,9,12", "2026预算27,000×83.72%=22,603；12人×约1,884元/人"),
    ("助销礼品", "笔记本、笔、背包、水杯、充电宝等", "制药", 6, 1506.8817, 9041.29, "3,6,9,12", "2026预算10,800×83.72%=9,041；6人×约1,507元/人"),
    ("助销礼品", "科研定制小礼品", "科研", 12, 3013.7625, 36165.15, "3,6,9,12", "2026预算43,200×83.72%=36,165；12人×约3,014元/人"),
    ("诊断原料推文", "小桔灯、IVD工具人", "诊断原料", 4, 8580.8525, 34323.41, "3,6,9,12", "2026预算41,000×83.72%=34,323；4期×约8,581元"),
    ("生物制药推文", "产品上市、解决方案推荐等内容", "制药", 4, 8371.5625, 33486.25, "3,6,9,12", "2026预算40,000×83.72%=33,486；4期×约8,372元"),
    ("资料印刷", "3个区域，每个区域半年8,000元", "科研", 2, 20091.75, 40183.50, "6,12", "2026预算48,000×83.72%=40,184；半年×2批"),
    ("百度", "关键词推广及年费", "三条业务线", 1, 6529.82, 6529.82, "1-12", "2026预算7,800×83.72%=6,530"),
    ("资料印刷", "分子诊断产品册+NGS产品册+单页", "诊断原料", 2, 9223.37, 18446.74, "6,12", "2026预算22,035×83.72%=18,447；半年×2批"),
    ("资料印刷", "mRNA产品资料+制药解决方案产品资料", "制药", 2, 12607.995, 25215.99, "6,12", "2026预算30,121×83.72%=25,216；半年×2批"),
    ("资料印刷", "国际业务资料：含英文版诊断、科研、制药", "国际业务", 1, 16475.25, 16475.25, "6,12", "2026预算19,680×83.72%=16,475；尾差调整"),
]


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


def fill_promotion(ws):
    r = 3
    for name, content, line, times, unit, amount, months, logic in PROMO:
        ws[f"B{r}"] = r - 2
        ws[f"C{r}"] = name
        ws[f"D{r}"] = content
        ws[f"E{r}"] = line
        ws[f"F{r}"] = times
        ws[f"G{r}"] = unit
        ws[f"I{r}"] = months
        ws[f"J{r}"] = logic
        cmt(ws, f"H{r}", f"预算逻辑：{times}×{unit:,.2f}={amount:,.2f}元；{logic}")
        r += 1


def fill_hospitality(ws):
    # 市场关系维护：客户10次×400；合作方2次×344.04；合计4,688.08
    rows = [
        (3, "市场关系维护", "客户", "宴请", 10, 400, "1-12", "10次×400元=4,000元；按2026预算折算"),
        (4, "市场关系维护", "合作方", "宴请", 2, 344.04, "6,12", "2次×344.04元=688.08元；30万总盘尾差"),
    ]
    for r, affair, obj, form, times, unit, months, note in rows:
        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}元。")


def fill_travel(ws):
    # 20次×3天；交通/次1,005.69；市内80/天；住宿301.38/天；差补80.37/天
    r = 4
    ws[f"B{r}"] = 1
    ws[f"C{r}"] = "市场部"
    ws[f"D{r}"] = "市场经理/市场部"
    ws[f"E{r}"] = "1-12"
    ws[f"F{r}"] = "展会及客户拜访"
    ws[f"G{r}"] = 20
    ws[f"H{r}"] = 3
    ws[f"I{r}"] = 1005.6885
    ws[f"J{r}"] = 80
    ws[f"L{r}"] = 301.3763
    ws[f"N{r}"] = 80.367
    cmt(ws, "Q4", "预算逻辑：20次×3天；交通1,005.69/次、市内80/天、住宿301.38/天、差补80.37/天；2026预算35.84万×83.72%=30万后折算。")


def fill_office(ws):
    ws["B5"] = "市场部"
    ws["M5"] = 5022.93
    ws["N5"] = "1-12"
    cmt(ws, "M5", "预算逻辑：快递费5,022.93元/年，按2026预算6,000×83.72%折算；按月均摊。")


def fill_file():
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    shutil.copy2(SRC, OUT)
    wb = openpyxl.load_workbook(OUT)
    fill_promotion(wb["附表（六）-业务推广费"])
    fill_hospitality(wb["附表（五）-业务招待费"])
    fill_travel(wb["附表（二）-差旅费"])
    fill_office(wb["附表（一）-办公费用"])
    # 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__":
    fill_file()