#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026 Q3 预算差异分析：市场部 + 诊断原料销售部。

数据源：
  1) 2026年第三季度预算情况分析表 - 市场部.xlsx
  2) 2026年第三季度预算情况分析表  - 诊断原料销售部.xlsx
  3) 全面预算套表-市场部-2026年度.xlsx
  4) 全面预算套表-诊断原料销售部-2026年度.xlsx

口径：
  · Q3 表 H/I 列是 1-9 月累计实际/预算（公式 SUMIFS 月份<=L3，L3=9）。
  · 金额单位：元；执行率=实际/预算。
  · 差异=实际-预算；正数为超预算，负数为低于预算。
  · 工资/社保在年初全面预算套表的“3-期间费用”页未列，只有 Q3 表有；
    工资行按对照表包含“辞退福利”（ERP 辞退福利→工资）。
"""
import os
import re
import json
from collections import defaultdict, Counter

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.properties import PageSetupProperties

ROOT = "/Users/liuxinyuan/Desktop/Hermes输出-工作类"
DOCS = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents"
OUT = os.path.join(ROOT, "经营分析", "2026Q3预算差异分析_市场部与诊断原料销售部_20261031_V01.xlsx")

FILES = {
    "市场部": {
        "q3": os.path.join(DOCS, "doc_1a7f4ec15c6c_2026年第三季度预算情况分析表 - 市场部.xlsx"),
        "annual": os.path.join(DOCS, "doc_fd420aa8c900_全面预算套表-市场部-2026年度.xlsx"),
    },
    "诊断原料销售部": {
        "q3": os.path.join(DOCS, "doc_da185711a0f0_2026年第三季度预算情况分析表  - 诊断原料销售部.xlsx"),
        "annual": os.path.join(DOCS, "doc_2d1055ac3c74_全面预算套表-诊断原料销售部-2026年度.xlsx"),
    },
}

# ---------- styles ----------
NAVY = "1F3864"
BLUE = "2F5597"
LIGHT_BLUE = "D9E2F3"
PALE_BLUE = "EEF3FA"
GREY = "F2F2F2"
WHITE = "FFFFFF"
RED = "C00000"
GREEN = "006100"
ORANGE = "FCE4D6"
YELLOW = "FFF2CC"

THIN = Side(style="thin", color="BFBFBF")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True)
RIGHT = Alignment(horizontal="right", vertical="center")

NUM_FMT = '#,##0.00;[Red]-#,##0.00'
PCT_FMT = '0.0%;[Red]-0.0%'


def read_q3(dept):
    wb = openpyxl.load_workbook(FILES[dept]["q3"], data_only=True)
    ws = wb["3-1期间费用"]
    rows = []
    for r in range(7, 50):
        b = ws.cell(r, 2).value
        c = ws.cell(r, 3).value
        d = ws.cell(r, 4).value
        h = ws.cell(r, 8).value
        i = ws.cell(r, 9).value
        j = ws.cell(r, 10).value
        k = ws.cell(r, 11).value
        l = ws.cell(r, 12).value
        if h is None and i is None:
            continue
        if r in (12, 37, 43, 48, 49):
            continue
        label = d if d not in (None, "——") else c
        rows.append({
            "row": r, "category": b, "l1": c, "l2": d, "label": str(label),
            "actual": float(h or 0), "budget": float(i or 0),
            "ly": float(j or 0) if isinstance(j, (int, float)) else None,
            "var": float(k or 0), "rate": float(l) if isinstance(l, (int, float)) else None,
        })
    totals = {
        "actual": float(ws["H49"].value),
        "budget": float(ws["I49"].value),
        "ly": float(ws["J49"].value),
        "var": float(ws["K49"].value),
        "rate": float(ws["L49"].value),
        "fixed_actual": float(ws["H12"].value),
        "fixed_budget": float(ws["I12"].value),
        "variable_actual": float(ws["H37"].value),
        "variable_budget": float(ws["I37"].value),
        "labor_actual": float(ws["H43"].value),
        "labor_budget": float(ws["I43"].value),
        "material_actual": float(ws["H48"].value),
        "material_budget": float(ws["I48"].value),
    }
    # monthly actual/budget for selected rows
    monthly = {}
    for r in [19, 20, 21, 28, 31, 32, 38, 39, 44]:
        d = ws.cell(r, 4).value
        c = ws.cell(r, 3).value
        label = d if d not in (None, '——') else (c or ws.cell(r, 2).value)
        rec = {}
        for q, start in [("Q1", 13), ("Q2", 25), ("Q3", 37)]:
            a = sum(ws.cell(r, start + 4 * k).value or 0 for k in range(3))
            b = sum(ws.cell(r, start + 1 + 4 * k).value or 0 for k in range(3))
            rec[q] = {"actual": float(a), "budget": float(b)}
        monthly[str(label)] = rec
    return {"rows": rows, "totals": totals, "monthly": monthly, "wb": wb}


def read_annual(dept):
    wb = openpyxl.load_workbook(FILES[dept]["annual"], data_only=True)
    ws = wb["3-期间费用"]
    out = {}
    for r in range(6, ws.max_row + 1):
        c = ws.cell(r, 3).value
        d = ws.cell(r, 4).value
        i = ws.cell(r, 9).value
        if not isinstance(i, (int, float)):
            continue
        label = d if d not in (None, "——") else c
        if label:
            # 同名行取首个非零，避免汇总行覆盖
            if str(label) not in out or (out[str(label)] == 0 and i):
                out[str(label)] = float(i)
    return out


def detail_rows(dept):
    wb = openpyxl.load_workbook(FILES[dept]["q3"], data_only=True)
    ws = wb["附表4-2026年实际费用明细"]
    rows = []
    for r in range(2, ws.max_row + 1):
        cat = ws.cell(r, 6).value
        amt = ws.cell(r, 13).value  # 还原
        if not isinstance(amt, (int, float)):
            continue
        rows.append({
            "row": r,
            "erp_dept": ws.cell(r, 1).value or "",
            "cat": cat,
            "month": ws.cell(r, 8).value,
            "summary": ws.cell(r, 11).value or "",
            "amount": float(amt),
        })
    return rows


def fmt_num(v):
    return float(v or 0)


def add_title(ws, row, text, ncols=11, sub=None):
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=ncols)
    c = ws.cell(row, 1, text)
    c.font = Font(name="微软雅黑", bold=True, size=14, color=WHITE)
    c.fill = PatternFill("solid", fgColor=NAVY)
    c.alignment = Alignment(horizontal="left", vertical="center")
    ws.row_dimensions[row].height = 28
    if sub:
        ws.merge_cells(start_row=row + 1, start_column=1, end_row=row + 1, end_column=ncols)
        c2 = ws.cell(row + 1, 1, sub)
        c2.font = Font(name="微软雅黑", size=9, color="404040")
        c2.fill = PatternFill("solid", fgColor=PALE_BLUE)
        c2.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)
        ws.row_dimensions[row + 1].height = 32


def header_row(ws, row, headers, fill=BLUE):
    for j, h in enumerate(headers, 1):
        c = ws.cell(row, j, h)
        c.font = Font(name="微软雅黑", bold=True, size=9, color=WHITE)
        c.fill = PatternFill("solid", fgColor=fill)
        c.alignment = CENTER
        c.border = BORDER
    ws.row_dimensions[row].height = 32


def body_cell(ws, row, col, value, fmt=None, align=None, bold=False, fill=None, font_color=None):
    c = ws.cell(row, col, value)
    c.font = Font(name="微软雅黑", size=9, bold=bold, color=font_color or "000000")
    c.border = BORDER
    c.alignment = align or LEFT
    if fmt:
        c.number_format = fmt
    if fill:
        c.fill = PatternFill("solid", fgColor=fill)


def set_widths(ws, widths):
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w


def var_font(v):
    return RED if v > 0.005 else (GREEN if v < -0.005 else "000000")


def build():
    q3 = {d: read_q3(d) for d in FILES}
    annual = {d: read_annual(d) for d in FILES}
    details = {d: detail_rows(d) for d in FILES}

    wb = openpyxl.Workbook()
    wb.remove(wb.active)

    # ================= 1. 结论与口径 =================
    ws = wb.create_sheet("00_结论与口径")
    set_widths(ws, [5, 24, 100])
    add_title(ws, 1, "2026年1-9月预算差异分析｜市场部 · 诊断原料销售部", 3,
              "期间：2026-01-01 至 2026-09-30｜编制：2026-10-31｜金额单位：元｜口径：实际 vs 1-9月累计预算")
    r = 3
    ws.cell(r, 2, "一、先看结论").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    conclusions = [
        ("总判断", "两部门表内合计：实际 4,105,561.96 元，预算 3,598,470.21 元，超预算 507,091.75 元（+14.1%）。但诊断原料的客户测试物料在 Q3 表预算为 0、年初预算有 496,938.82 元；把这笔预算还原后，两部门合计仅超 10,152.93 元（+0.25%）。"),
        ("但结构错位", "净额几乎打平，是靠“诊断人工/差旅超”与“市场人工/推广少花”互相对冲，不是费用效率整体达标。诊断原料人工 +303,791、差旅 +100,336；市场部人工 -175,615、业务推广 -69,477、会务 -71,376、招待 -31,848。"),
        ("诊断原料", "表内超预算 795,791.80 元（+27.7%）。最大两项：客户测试物料 +492,572.16（Q3 表未列预算，按年初预算执行率 99.1%，不是真超支）；人工 +303,791（工资 +158,196、社保 +133,821、福利 +11,774）。差旅 +100,336 集中在 Q3，Q3 差旅/营收比从 Q2 的 0.74% 升到 1.46%。"),
        ("市场部", "表内低于预算 288,700.05 元（-39.8%）。主因是人工 -175,615：预算从 3 月起按 2 人计（工资 44,632/月、社保 10,607/月），实际一直按 1 人发生（约 24,500/月、5,269/月）。业务推广 -69,477 是 Q3 活动空档，年初预算执行率仅 38.0%，Q4 实验加速季/秋季促销会集中释放。"),
        ("收入背景", "诊断原料 1-9 月营收 3,402.57 万元，期间目标 4,414.65 万元，完成 77.1%，缺口 1,012.08 万元；Q3 营收 1,159.76 万元，Q3 目标 1,394.31 万元，完成 83.2%。大客户部营收 Q1→Q3 为 461.69→335.29→309.74 万元，连续下滑；销售拓展部 640.18→805.66→850.02 万元，持续上升。"),
        ("费用效率", "诊断原料 1-9 月费用率 10.78%（费用 366.89 万/营收 3,402.57 万），按期间目标计算的预算费用率 6.51%（287.31 万/4,414.65 万），高 4.27 个百分点。费用超支与收入缺口同时存在。"),
    ]
    for k, v in conclusions:
        body_cell(ws, r, 2, k, bold=True, fill=PALE_BLUE)
        body_cell(ws, r, 3, v)
        ws.row_dimensions[r].height = 58
        r += 1

    r += 1
    ws.cell(r, 2, "二、口径与数据源").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    notes = [
        ("期间", "Q3 表 H/I 列是 1-9 月累计实际/预算；I 列公式为 SUMIFS(月份<=9)，不是全年预算。"),
        ("全年预算", "年初《全面预算套表》的“3-期间费用”页只列变动费用，未列工资/社保/固定费用；工资/社保只能用 Q3 表预算和 2025 年同期实际对照。"),
        ("客户测试物料", "Q3 表预算 0；年初诊断原料预算 496,938.82 元。本表同时给“表内口径”和“还原年初预算口径”。"),
        ("会务费", "年初市场部预算 180,680 元；Q3 表市场部为 0，诊断原料为 213,680 元（从金额与月度分布看，含原市场部会务180,680+追加33,000；需财务确认）。跨部门比较必须合并看，否则会误判。"),
        ("人工", "工资行按《对照表》包含辞退福利（ERP 辞退福利→工资）；Q3 表未提供工资/社保明细，2 月、7-8 月单月异常只能标“待人力核”。"),
        ("金额", "全部为含税/财务入账口径；差异=实际-预算，正数超预算、负数低于预算。"),
        ("数据源", "① 2026年第三季度预算情况分析表 - 市场部.xlsx；② 2026年第三季度预算情况分析表  - 诊断原料销售部.xlsx；③ 全面预算套表-市场部-2026年度.xlsx；④ 全面预算套表-诊断原料销售部-2026年度.xlsx。"),
    ]
    for k, v in notes:
        body_cell(ws, r, 2, k, bold=True, fill=GREY)
        body_cell(ws, r, 3, v)
        ws.row_dimensions[r].height = 42
        r += 1

    r += 1
    ws.cell(r, 2, "三、需要核实的3个点").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    unknowns = [
        ("诊断工资2月峰值", "2 月工资 424,590.18 元，比月预算高 205,078.98 元；其余 8 个月合计低于预算 46,883 元。是否含年终奖/离职补偿/补发，需人力提供工资明细。"),
        ("诊断社保基数", "社保 9 个月每月都超预算 1.1-2.2 万元，合计 133,821 元；是缴费基数上调还是人数多于预算，需人力提供缴费明细。"),
        ("市场推广Q4", "业务推广年初预算 389,000 元，1-9 月实际 147,749.57 元（38.0%）；Q4 实验加速季/秋季促销若按计划投放，当前结余会消化，不能当成全年节省。"),
    ]
    for k, v in unknowns:
        body_cell(ws, r, 2, k, bold=True, fill=YELLOW)
        body_cell(ws, r, 3, v)
        ws.row_dimensions[r].height = 52
        r += 1

    # ================= 2. 差异总览 =================
    ws = wb.create_sheet("01_差异总览")
    set_widths(ws, [26, 18, 18, 18, 12, 60])
    add_title(ws, 1, "差异总览｜表内口径、还原口径、两部门合计", 6,
              "正数=超预算，负数=低于预算；执行率=实际/预算")
    headers = ["口径", "2026年1-9月实际", "2026年1-9月预算", "差异额", "执行率", "说明"]
    header_row(ws, 3, headers)
    d_t = q3["诊断原料销售部"]["totals"]
    m_t = q3["市场部"]["totals"]
    test_budget = annual["诊断原料销售部"].get("客户测试物料", 0)
    conference_mkt = annual["市场部"].get("会务费", 0)
    rows = [
        ("诊断原料销售部（表内）", d_t["actual"], d_t["budget"], d_t["var"], d_t["rate"], "Q3表原口径；客户测试物料预算为0"),
        ("诊断原料销售部（还原测试物料年初预算）", d_t["actual"], d_t["budget"] + test_budget, d_t["actual"] - d_t["budget"] - test_budget, d_t["actual"] / (d_t["budget"] + test_budget), "加回年初客户测试物料预算 496,938.82 元"),
        ("市场部（表内）", m_t["actual"], m_t["budget"], m_t["var"], m_t["rate"], "Q3表原口径；会务预算为0"),
        ("市场部（还原会务年初预算）", m_t["actual"], m_t["budget"] + conference_mkt, m_t["actual"] - m_t["budget"] - conference_mkt, m_t["actual"] / (m_t["budget"] + conference_mkt), "加回年初会务预算 180,680 元（若按年初归属）"),
        ("两部门合计（表内）", d_t["actual"] + m_t["actual"], d_t["budget"] + m_t["budget"], d_t["var"] + m_t["var"], (d_t["actual"] + m_t["actual"]) / (d_t["budget"] + m_t["budget"]), "表内净额超 50.71 万"),
        ("两部门合计（还原测试物料）", d_t["actual"] + m_t["actual"], d_t["budget"] + m_t["budget"] + test_budget, d_t["actual"] + m_t["actual"] - d_t["budget"] - m_t["budget"] - test_budget, (d_t["actual"] + m_t["actual"]) / (d_t["budget"] + m_t["budget"] + test_budget), "还原后仅超 1.02 万（+0.25%），但结构仍错位"),
    ]
    rr = 4
    for name, a, b, v, rate, note in rows:
        body_cell(ws, rr, 1, name, bold=True, fill=PALE_BLUE if "合计" in name else None)
        body_cell(ws, rr, 2, a, NUM_FMT, RIGHT)
        body_cell(ws, rr, 3, b, NUM_FMT, RIGHT)
        body_cell(ws, rr, 4, v, NUM_FMT, RIGHT, bold=True, font_color=var_font(v))
        body_cell(ws, rr, 5, rate, PCT_FMT, CENTER)
        body_cell(ws, rr, 6, note)
        ws.row_dimensions[rr].height = 34
        rr += 1

    rr += 1
    ws.cell(rr, 1, "差异贡献（按绝对额排序）").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    rr += 1
    header_row(ws, rr, ["部门", "科目", "差异额", "占本部门总差异", "性质", "证据/原因"])
    rr += 1
    contribs = [
        ("诊断原料销售部", "客户测试物料", 492572.16, 0.6190, "口径缺失", "Q3表预算0；年初预算496,938.82；Q2单月峰值10.2万；80.2%在销售拓展部"),
        ("诊断原料销售部", "人工（工资+社保+福利）", 303791.00, 0.3818, "预算偏低+一次性", "工资+158,196（2月单月+205,079）；社保+133,821（每月均超）；福利+11,774"),
        ("诊断原料销售部", "差旅（交通+补贴+住宿）", 100336.29, 0.1261, "业务投入+预算遗漏", "Q3差旅16.96万，Q1/Q2均低于预算；大客户部预算未列7.4万；赵云浩/刘新元/刘欣Q3合计10.28万"),
        ("诊断原料销售部", "会务费", -71376.18, -0.0897, "预算重分配+会议取消", "CACLP/EBC/IMDx实际14.23万；7月武汉会议未参加，Q3预算3.3万未用"),
        ("诊断原料销售部", "业务招待费", -31848.08, -0.0400, "时间性+预算偏高", "9月中秋部分10月报销；Q1已少1.87万；2025同期2.53万，2026为3.42万"),
        ("市场部", "人工（工资+社保）", -175615.44, 0.6083, "编制未满", "3月起预算按2人计，实际1人；工资/社保与2025同期基本持平"),
        ("市场部", "业务推广费", -69477.40, 0.2406, "时间性/活动后置", "Q3仅3.22万 vs 预算7.24万；年初预算执行率38.0%；Q4实验加速季/秋季促销"),
        ("市场部", "差旅（交通+补贴+住宿）", -39984.00, 0.1385, "编制未满/零执行", "全年实际0；预算5.0万"),
        ("市场部", "快递费+业务招待费", -8120.00, 0.0281, "零执行", "两项全年实际均为0；预算合计1.01万"),
        ("市场部", "其他（办公/软件/IP/包材/固定）", 5053.37 - 556.58, -0.0156, "预算外小额", "办公用品774、软件1,420、知识产权1,980、包材880；固定费用净-557"),
    ]
    for dept, cat, v, pct, nature, ev in sorted(contribs, key=lambda x: -abs(x[2])):
        body_cell(ws, rr, 1, dept)
        body_cell(ws, rr, 2, cat)
        body_cell(ws, rr, 3, v, NUM_FMT, RIGHT, bold=True, font_color=var_font(v))
        body_cell(ws, rr, 4, pct, PCT_FMT, CENTER)
        body_cell(ws, rr, 5, nature, fill=GREY)
        body_cell(ws, rr, 6, ev)
        ws.row_dimensions[rr].height = 44
        rr += 1

    # ================= 3/4. 差异明细 =================
    def detail_sheet(dept, sheet_name):
        ws = wb.create_sheet(sheet_name)
        set_widths(ws, [4, 18, 12, 12, 12, 7, 12, 10, 14, 50, 7])
        add_title(ws, 1, f"{dept}｜2026年1-9月预算差异明细", 11,
                  "按差异绝对额排序；正数=超预算，负数=低于预算；年初预算仅对变动费用有值，工资/社保/固定费用以“—”表示")
        headers = ["序号", "科目", "1-9月实际", "1-9月预算", "差异额", "执行率", "年初预算", "年初执行率", "差异性质", "原因/证据", "置信度"]
        header_row(ws, 3, headers)
        rows = sorted(q3[dept]["rows"], key=lambda x: -abs(x["var"]))
        rr = 4
        for i, x in enumerate(rows, 1):
            if abs(x["var"]) < 0.005 and x["budget"] == 0 and x["actual"] == 0:
                continue
            annual_b = annual[dept].get(x["label"])
            nature, evidence, confidence = cause_for(dept, x)
            body_cell(ws, rr, 1, i, align=CENTER)
            body_cell(ws, rr, 2, x["label"], bold=abs(x["var"]) >= 10000)
            body_cell(ws, rr, 3, x["actual"], NUM_FMT, RIGHT)
            body_cell(ws, rr, 4, x["budget"], NUM_FMT, RIGHT)
            body_cell(ws, rr, 5, x["var"], NUM_FMT, RIGHT, bold=abs(x["var"]) >= 10000, font_color=var_font(x["var"]))
            body_cell(ws, rr, 6, x["rate"] if x["rate"] is not None else "—", PCT_FMT if x["rate"] is not None else None, CENTER)
            body_cell(ws, rr, 7, annual_b if annual_b is not None else "—", NUM_FMT if annual_b is not None else None, RIGHT)
            body_cell(ws, rr, 8, (x["actual"] / annual_b) if annual_b else "—", PCT_FMT if annual_b else None, CENTER)
            body_cell(ws, rr, 9, nature, fill=GREY)
            body_cell(ws, rr, 10, evidence)
            body_cell(ws, rr, 11, confidence, align=CENTER)
            ws.row_dimensions[rr].height = 48
            rr += 1
        # total
        t = q3[dept]["totals"]
        body_cell(ws, rr, 1, "", fill=LIGHT_BLUE)
        body_cell(ws, rr, 2, "合计", bold=True, fill=LIGHT_BLUE)
        body_cell(ws, rr, 3, t["actual"], NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
        body_cell(ws, rr, 4, t["budget"], NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
        body_cell(ws, rr, 5, t["var"], NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE, font_color=var_font(t["var"]))
        body_cell(ws, rr, 6, t["rate"], PCT_FMT, CENTER, bold=True, fill=LIGHT_BLUE)
        body_cell(ws, rr, 7, "", fill=LIGHT_BLUE)
        body_cell(ws, rr, 8, "", fill=LIGHT_BLUE)
        body_cell(ws, rr, 9, "", fill=LIGHT_BLUE)
        body_cell(ws, rr, 10, "与 Q3 表 H49/I49/K49/L49 勾稽一致", fill=LIGHT_BLUE)
        body_cell(ws, rr, 11, "", fill=LIGHT_BLUE)
        ws.freeze_panes = "C4"
        ws.sheet_view.zoomScale = 80
        ws.page_setup.orientation = 'landscape'
        ws.page_setup.fitToWidth = 1
        ws.page_setup.fitToHeight = 0
        ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
        return ws

    detail_sheet("诊断原料销售部", "02_诊断_差异明细")
    detail_sheet("市场部", "03_市场_差异明细")

    # ================= 5. 口径核对 =================
    ws = wb.create_sheet("04_口径核对")
    set_widths(ws, [24, 16, 18, 18, 14, 60])
    add_title(ws, 1, "年初预算 vs Q3表预算 vs 1-9月实际｜关键科目核对", 6,
              "年初预算取《全面预算套表》3-期间费用页；Q3表预算为1-9月累计；工资/社保年初套表未列")
    header_row(ws, 3, ["科目", "年初全年预算", "Q3表1-9月预算", "1-9月实际", "年初执行率", "核对说明"])
    rr = 4
    checks = []
    for dept, items in [
        ("诊断原料销售部", ["差旅费-交通费", "差旅费-补贴", "差旅费-差旅补贴", "差旅费-住宿费", "业务招待费", "会务费", "客户测试物料", "工资", "社保/公积金"]),
        ("市场部", ["差旅费-交通费", "差旅费-差旅补贴", "差旅费-住宿费", "业务招待费", "业务推广费", "会务费", "办公费用-快递费", "工资", "社保/公积金"]),
    ]:
        # section
        ws.merge_cells(start_row=rr, start_column=1, end_row=rr, end_column=6)
        c = ws.cell(rr, 1, dept)
        c.font = Font(name="微软雅黑", bold=True, size=10, color=WHITE)
        c.fill = PatternFill("solid", fgColor=BLUE)
        c.alignment = LEFT
        rr += 1
        for item in items:
            # normalize labels
            cand = [x for x in q3[dept]["rows"] if x["label"] == item or x["label"].replace("差旅费-补贴", "差旅费-差旅补贴") == item]
            if not cand:
                continue
            x = cand[0]
            ab = annual[dept].get(item) or annual[dept].get(x["label"])
            note = ""
            if item == "客户测试物料" and dept == "诊断原料销售部":
                note = "Q3表预算为0，年初有49.69万；这是表内差异最大的口径缺口"
            elif item == "会务费" and dept == "市场部":
                note = "年初18.07万，Q3表为0；Q3表把这笔预算放到诊断原料（21.37万，需财务确认）"
            elif item == "会务费" and dept == "诊断原料销售部":
                note = "年初为0，Q3表21.37万；从金额与月度分布看，含原市场部会务18.07万+追加3.3万（需财务确认）"
            elif item in ("工资", "社保/公积金"):
                note = "年初全面预算套表未列，只能用Q3表预算和2025同期实际对照"
            body_cell(ws, rr, 1, item, bold=item in ("客户测试物料", "会务费", "工资", "社保/公积金"))
            body_cell(ws, rr, 2, ab if ab is not None else "—", NUM_FMT if ab is not None else None, RIGHT)
            body_cell(ws, rr, 3, x["budget"], NUM_FMT, RIGHT)
            body_cell(ws, rr, 4, x["actual"], NUM_FMT, RIGHT)
            body_cell(ws, rr, 5, (x["actual"] / ab) if ab else "—", PCT_FMT if ab else None, CENTER)
            body_cell(ws, rr, 6, note)
            ws.row_dimensions[rr].height = 34
            rr += 1

    # ================= 6. 月度趋势 =================
    ws = wb.create_sheet("05_月度趋势")
    set_widths(ws, [26, 16, 16, 16, 16, 16, 16, 16])
    add_title(ws, 1, "Q1/Q2/Q3 实际 vs 预算｜关键科目", 8,
              "Q3表按月预算/实际汇总；用于判断差异是全年累积还是Q3单季发生")
    header_row(ws, 3, ["部门/科目", "Q1实际", "Q1预算", "Q2实际", "Q2预算", "Q3实际", "Q3预算", "Q3差异"])
    rr = 4
    for dept, items in [
        ("诊断原料销售部", ["差旅费-交通费", "差旅费-差旅补贴", "差旅费-住宿费", "客户测试物料", "工资", "社保/公积金", "业务招待费", "会务费"]),
        ("市场部", ["业务推广费", "工资", "社保/公积金", "差旅费-交通费", "差旅费-差旅补贴", "差旅费-住宿费"]),
    ]:
        ws.merge_cells(start_row=rr, start_column=1, end_row=rr, end_column=8)
        c = ws.cell(rr, 1, dept)
        c.font = Font(name="微软雅黑", bold=True, size=10, color=WHITE)
        c.fill = PatternFill("solid", fgColor=BLUE)
        c.alignment = LEFT
        rr += 1
        for item in items:
            if item.startswith("差旅费") and item != "差旅费-交通费":
                continue
            cand = [x for x in q3[dept]["rows"] if x["label"] == item]
            if not cand:
                continue
            # find monthly key: match label; special travel total by 3 lines
            if item.startswith("差旅费"):
                # sum 3 travel lines
                keys = [x["label"] for x in q3[dept]["rows"] if x["label"].startswith("差旅费")]
            else:
                keys = [item]
            qd = {}
            for q in ["Q1", "Q2", "Q3"]:
                a = sum(q3[dept]["monthly"].get(k, {}).get(q, {}).get("actual", 0) for k in keys)
                b = sum(q3[dept]["monthly"].get(k, {}).get(q, {}).get("budget", 0) for k in keys)
                qd[q] = (a, b)
            body_cell(ws, rr, 1, item if not item.startswith("差旅费") else "差旅合计", bold=item in ("客户测试物料", "工资", "社保/公积金", "业务推广费", "差旅费-交通费"))
            for j, q in enumerate(["Q1", "Q2", "Q3"], start=2):
                a, b = qd[q]
                body_cell(ws, rr, j * 2 - 2, a, NUM_FMT, RIGHT)
                body_cell(ws, rr, j * 2 - 1, b, NUM_FMT, RIGHT)
            a3, b3 = qd["Q3"]
            body_cell(ws, rr, 8, a3 - b3, NUM_FMT, RIGHT, bold=True, font_color=var_font(a3 - b3))
            ws.row_dimensions[rr].height = 26
            rr += 1

    # ================= 7. 佐证_诊断 =================
    ws = wb.create_sheet("06_佐证_诊断")
    set_widths(ws, [6, 22, 16, 16, 16, 16, 12, 50])
    add_title(ws, 1, "诊断原料销售部｜差异佐证", 8,
              "来源：附表4-2026年实际费用明细（还原列M）；金额单位：元")
    r = 3
    # travel by person
    ws.cell(r, 2, "1. 差旅按人（含交通/补贴/住宿）").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "姓名", "Q1", "Q2", "Q3", "合计", "占YTD", "备注"])
    r += 1
    travel_person = defaultdict(lambda: defaultdict(float))
    travel_dept = defaultdict(float)
    ent_month = defaultdict(float)
    test_month = defaultdict(float)
    test_dept = defaultdict(float)
    conf_items = []
    for x in details["诊断原料销售部"]:
        cat = x["cat"]
        amt = x["amount"]
        m = x["month"]
        try:
            q = "Q%d" % ((int(m) - 1) // 3 + 1)
        except Exception:
            q = "?"
        if cat and cat.startswith("差旅费"):
            mm = re.match(r"([\u4e00-\u9fa5]{2,4})报销", x["summary"])
            name = mm.group(1) if mm else ("刘新元" if "刘新元" in x["summary"] else ("赵云浩" if "赵云浩" in x["summary"] else "其他"))
            travel_person[name][q] += amt
            travel_person[name]["total"] += amt
            travel_dept[x["erp_dept"]] += amt
        if cat == "业务招待费":
            ent_month[str(m)] += amt
        if cat == "客户测试物料":
            test_month[str(m)] += amt
            test_dept[x["erp_dept"]] += amt
        if cat == "会务费":
            conf_items.append((x["summary"], amt))
    total_travel = sum(v["total"] for v in travel_person.values())
    for name, v in sorted(travel_person.items(), key=lambda kv: -kv[1]["total"]):
        body_cell(ws, r, 1, "", align=CENTER)
        body_cell(ws, r, 2, name, bold=v["total"] >= 30000)
        for j, q in enumerate(["Q1", "Q2", "Q3"], start=3):
            body_cell(ws, r, j, v.get(q, 0), NUM_FMT, RIGHT)
        body_cell(ws, r, 6, v["total"], NUM_FMT, RIGHT, bold=True)
        body_cell(ws, r, 7, v["total"] / total_travel if total_travel else 0, PCT_FMT, CENTER)
        note = ""
        if name in ("赵云浩", "刘新元", "刘欣"):
            note = "Q3增量主要来源；三人Q3合计102,760元，占Q3差旅60.6%"
        body_cell(ws, r, 8, note)
        r += 1
    body_cell(ws, r, 2, "合计", bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 3, sum(v.get("Q1", 0) for v in travel_person.values()), NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 4, sum(v.get("Q2", 0) for v in travel_person.values()), NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 5, sum(v.get("Q3", 0) for v in travel_person.values()), NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 6, total_travel, NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 7, 1 if total_travel else 0, PCT_FMT, CENTER, bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 8, "", fill=LIGHT_BLUE)
    r += 2

    # travel by ERP dept
    ws.cell(r, 2, "2. 差旅按ERP部门").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "ERP部门", "金额", "占YTD", "", "", "", ""])
    r += 1
    for k, v in sorted(travel_dept.items(), key=lambda kv: -kv[1]):
        body_cell(ws, r, 2, k)
        body_cell(ws, r, 3, v, NUM_FMT, RIGHT)
        body_cell(ws, r, 4, v / total_travel if total_travel else 0, PCT_FMT, CENTER)
        r += 1
    r += 1

    # test material
    ws.cell(r, 2, "3. 客户测试物料｜按月 + 按部门").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "月份", "金额", "占YTD", "", "", "", "备注"])
    r += 1
    total_test = sum(test_month.values())
    for m in sorted(test_month, key=lambda x: int(x) if str(x).isdigit() else 99):
        body_cell(ws, r, 2, f"{m}月" if str(m).isdigit() else str(m))
        body_cell(ws, r, 3, test_month[m], NUM_FMT, RIGHT)
        body_cell(ws, r, 4, test_month[m] / total_test if total_test else 0, PCT_FMT, CENTER)
        if str(m) == "5":
            body_cell(ws, r, 8, "单月峰值10.2万")
        r += 1
    body_cell(ws, r, 2, "合计", bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 3, total_test, NUM_FMT, RIGHT, bold=True, fill=LIGHT_BLUE)
    body_cell(ws, r, 4, 1, PCT_FMT, CENTER, bold=True, fill=LIGHT_BLUE)
    r += 1
    for k, v in sorted(test_dept.items(), key=lambda kv: -kv[1]):
        body_cell(ws, r, 2, f"  {k}")
        body_cell(ws, r, 3, v, NUM_FMT, RIGHT)
        body_cell(ws, r, 4, v / total_test if total_test else 0, PCT_FMT, CENTER)
        r += 1
    r += 1

    # conference items
    ws.cell(r, 2, "4. 会务费明细（按金额）").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "摘要", "金额", "", "", "", "", ""])
    r += 1
    for s, v in sorted(conf_items, key=lambda x: -x[1]):
        body_cell(ws, r, 2, s)
        body_cell(ws, r, 3, v, NUM_FMT, RIGHT)
        r += 1
    r += 1

    # entertainment by month
    ws.cell(r, 2, "5. 业务招待费｜按月").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "月份", "金额", "", "", "", "", ""])
    r += 1
    for m in sorted(ent_month, key=lambda x: int(x) if str(x).isdigit() else 99):
        body_cell(ws, r, 2, f"{m}月" if str(m).isdigit() else str(m))
        body_cell(ws, r, 3, ent_month[m], NUM_FMT, RIGHT)
        r += 1

    # ================= 8. 佐证_市场 =================
    ws = wb.create_sheet("07_佐证_市场")
    set_widths(ws, [6, 22, 16, 16, 16, 16, 12, 70])
    add_title(ws, 1, "市场部｜差异佐证", 8,
              "来源：附表4-2026年实际费用明细（还原列M）；金额单位：元")
    r = 3
    # labor monthly
    ws.cell(r, 2, "1. 工资/社保｜月度实际 vs 预算（证明1人 vs 2人）").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "月份", "工资实际", "工资预算", "社保实际", "社保预算", "工资差异", "说明"])
    r += 1
    ws_q3 = openpyxl.load_workbook(FILES["市场部"]["q3"], data_only=True)["3-1期间费用"]
    for m in range(1, 10):
        ac = 13 + 4 * (m - 1)
        bc = 14 + 4 * (m - 1)
        wa = ws_q3.cell(38, ac).value or 0
        wb_ = ws_q3.cell(38, bc).value or 0
        sa = ws_q3.cell(39, ac).value or 0
        sb = ws_q3.cell(39, bc).value or 0
        body_cell(ws, r, 2, f"{m}月")
        body_cell(ws, r, 3, wa, NUM_FMT, RIGHT)
        body_cell(ws, r, 4, wb_, NUM_FMT, RIGHT)
        body_cell(ws, r, 5, sa, NUM_FMT, RIGHT)
        body_cell(ws, r, 6, sb, NUM_FMT, RIGHT)
        body_cell(ws, r, 7, wa - wb_, NUM_FMT, RIGHT, font_color=var_font(wa - wb_))
        if m >= 3:
            body_cell(ws, r, 8, "3月起预算按2人计（工资44,632/月、社保10,607/月），实际仍为1人（约24,500/5,269）")
        r += 1
    r += 1

    # promotion by month
    ws.cell(r, 2, "2. 业务推广费｜按月").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "月份", "实际", "预算", "差异", "", "", ""])
    r += 1
    promo_month = defaultdict(float)
    promo_items = defaultdict(float)
    for x in details["市场部"]:
        if x["cat"] == "业务推广费" and isinstance(x["month"], (int, str)):
            try:
                m = int(x["month"])
            except Exception:
                continue
            promo_month[m] += x["amount"]
            promo_items[x["summary"]] += x["amount"]
    ws_q3_m = openpyxl.load_workbook(FILES["市场部"]["q3"], data_only=True)["3-1期间费用"]
    for m in range(1, 10):
        ac = 13 + 4 * (m - 1)
        bc = 14 + 4 * (m - 1)
        a = ws_q3_m.cell(31, ac).value or 0
        b = ws_q3_m.cell(31, bc).value or 0
        body_cell(ws, r, 2, f"{m}月")
        body_cell(ws, r, 3, a, NUM_FMT, RIGHT)
        body_cell(ws, r, 4, b, NUM_FMT, RIGHT)
        body_cell(ws, r, 5, a - b, NUM_FMT, RIGHT, font_color=var_font(a - b))
        r += 1
    r += 1

    # top promotion items
    ws.cell(r, 2, "3. 业务推广费｜主要明细（Top 20）").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "摘要", "金额", "占YTD", "", "", "", ""])
    r += 1
    total_promo = sum(promo_items.values())
    for s, v in sorted(promo_items.items(), key=lambda kv: -kv[1])[:20]:
        body_cell(ws, r, 2, s)
        body_cell(ws, r, 3, v, NUM_FMT, RIGHT)
        body_cell(ws, r, 4, v / total_promo if total_promo else 0, PCT_FMT, CENTER)
        r += 1
    r += 1

    # zero-spend accounts
    ws.cell(r, 2, "4. 零执行科目").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header_row(ws, r, ["", "科目", "预算", "实际", "差异", "", "", "说明"])
    r += 1
    for item in ["差旅费-交通费", "差旅费-差旅补贴", "差旅费-住宿费", "办公费用-快递费", "业务招待费"]:
        x = next((x for x in q3["市场部"]["rows"] if x["label"] == item), None)
        if not x:
            continue
        body_cell(ws, r, 2, item)
        body_cell(ws, r, 3, x["budget"], NUM_FMT, RIGHT)
        body_cell(ws, r, 4, x["actual"], NUM_FMT, RIGHT)
        body_cell(ws, r, 5, x["var"], NUM_FMT, RIGHT, font_color=var_font(x["var"]))
        body_cell(ws, r, 8, "全年零执行；与市场部编制未满、无出差/招待一致")
        r += 1

    # ---------- auto row height ----------
    import math
    for ws in wb.worksheets:
        for row in range(1, ws.max_row + 1):
            if row in (1, 2):
                continue
            lines = 1
            for cell in ws[row]:
                if isinstance(cell.value, str) and cell.value and (cell.alignment.wrap_text or len(cell.value) > 20):
                    width = ws.column_dimensions[cell.column_letter].width or 10
                    chars_per_line = max(6, width / 2)
                    lines = max(lines, math.ceil(len(cell.value) / chars_per_line))
            ws.row_dimensions[row].height = min(150, max(18, 14 * lines + 4))

    # ================= save =================
    # sanity asserts
    d_sum = sum(x["var"] for x in q3["诊断原料销售部"]["rows"])
    m_sum = sum(x["var"] for x in q3["市场部"]["rows"])
    assert abs(d_sum - d_t["var"]) < 0.02, (d_sum, d_t["var"])
    assert abs(m_sum - m_t["var"]) < 0.02, (m_sum, m_t["var"])
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    wb.save(OUT)
    print("SAVED", OUT)
    print("诊断 表内", d_t["actual"], d_t["budget"], d_t["var"])
    print("市场 表内", m_t["actual"], m_t["budget"], m_t["var"])
    print("合计 表内", d_t["actual"] + m_t["actual"], d_t["budget"] + m_t["budget"], d_t["var"] + m_t["var"])
    print("还原测试物料后合计差异", d_t["actual"] + m_t["actual"] - (d_t["budget"] + m_t["budget"] + test_budget))


def cause_for(dept, x):
    """Return (nature, evidence, confidence) for a variance row."""
    label = x["label"]
    v = x["var"]
    # Diagnostics
    if dept == "诊断原料销售部":
        if label == "客户测试物料":
            return ("口径缺失", "Q3表预算0；年初预算496,938.82元；1-9月实际492,572.16，年初执行率99.1%；80.2%发生在销售拓展部，5月单月10.2万为峰值。不是真实超支，是Q3表未列预算。", "高")
        if label == "工资":
            return ("预算偏低+一次性", "工资2,133,796.94 vs 预算1,975,600.80（+158,196.14）。2月单月424,590.18，超月预算205,078.98；其余8个月合计低于预算46,883。2025同期2,363,306，2026实际比2025低9.7%——不是人数扩张，是预算基数低于上年+2月一次性项目。", "中（2月明细待人力核）")
        if label == "社保/公积金":
            return ("结构性偏差", "536,893.49 vs 403,072.23（+133,821.26）。9个月每月均超预算1.1-2.2万；2025同期505,781.14，2026同比+6.2%。属缴费基数/人数结构性偏差，需人力提供缴费明细。", "高（金额）/中（原因）")
        if label.startswith("差旅费"):
            return ("业务投入+预算遗漏", "差旅合计301,852.29 vs 201,516（+100,336.29）。Q1/Q2均低于预算，Q3单季169,643.56 vs 57,576（+112,067.56）；Q3赵云浩/刘新元/刘欣三人合计102,760，占Q3差旅60.6%。财务说明：大客户部差旅年初未列7.4万、销售总监偏高2万、离职带教及客户随访频率高。", "高")
        if label == "业务招待费":
            return ("时间性+预算偏高", "34,151.90 vs 65,999.98（-31,848.08）。9月中秋部分10月报销只能解释9月一部分；Q1已少18,713、Q2少6,400。2025同期25,314.96，2026同比+34.9%，预算65,999.98偏高。", "中")
        if label == "会务费":
            return ("预算重分配+会议取消", "142,303.82 vs 213,680（-71,376.18）。Q3表预算含原市场部会务180,680+追加33,000（需财务确认）；实际CACLP/EBC/IMDx 142,303.82；7月武汉会议未参加，Q3 33,000预算未用。与年初市场部会务180,680比，实际执行78.8%。", "高")
        if label == "福利费-活动经费":
            return ("预算外支出", "11,773.60，预算0；2025同期2,409。主要为部门团建、十周年聚餐、沟通交流聚餐；属预算外一次性支出。", "高")
        if label == "咨询服务费-其他":
            return ("预算外小额", "2,787.62，预算0；库巴扎天津医科大学服务费1,886.79+喀斯玛平台Q2技术服务费900.83。", "高")
        if label == "固定资产折旧":
            return ("时间性", "9,024.08 vs 8,074.22（+949.86）；资产转固/折旧起算时间差异，金额小。", "高")
        return ("小额固定分摊差异", "金额小，属水电/租赁/摊销分摊口径差异。", "高")
    # Marketing
    else:
        if label == "工资":
            return ("编制未满", "227,249.80 vs 365,348.81（-138,099.01）。3月起预算按2人计（工资预算44,632/月），实际一直按1人发生（约24,500/月）；2025同期227,825.86，2026基本持平。不是降薪，是第二个编制未到位。", "高")
        if label == "社保/公积金":
            return ("编制未满", "47,457.52 vs 84,973.95（-37,516.43）。3月起预算社保10,607/月，实际5,269/月；与工资同一原因（1人 vs 2人）。", "高")
        if label == "业务推广费":
            return ("时间性/活动后置", "147,749.57 vs 217,226.97（-69,477.40）。Q3仅32,208 vs 预算72,409；年初预算389,000，1-9月执行38.0%。Q1/Q2以产品册/设计/物料为主，Q3无大型活动；10月实验加速季/秋季促销在Q4集中投放。", "高（金额）/中（Q4是否投放）")
        if label.startswith("差旅费"):
            return ("编制未满/零执行", "全年实际0 vs 预算合计49,984；与市场部仅1人、无出差一致。", "高")
        if label == "办公费用-快递费":
            return ("零执行", "全年实际0 vs 预算4,200；无市场部寄件发生。", "高")
        if label == "业务招待费":
            return ("零执行", "全年实际0 vs 预算3,920；无市场部招待发生。", "高")
        if label in ("办公费用-办公用品", "办公费用-软件使用费", "咨询服务费-知识产权费", "辅料/包材耗用"):
            return ("预算外小额", f"{label}实际{x['actual']:,.2f}，预算0；金额小，属预算外零星支出。", "高")
        return ("小额固定分摊差异", "金额小，属水电/租赁/折旧/摊销分摊口径差异。", "高")


if __name__ == "__main__":
    build()