#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2027 商机预算导入模板 V02：诊断原料 / 生命科学 两张表 + 字典 + 说明
- 生命科学独立一张，粒度到「课题组 + 货号」
- 业务线字典：生命科学试剂 / 生命科学服务 / 生命科学仪器 / 生命科学耗材
"""
from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

OUT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/2027商机预算模板_V02_诊断原料+生命科学.xlsx'
HEAD_FILL = PatternFill('solid', fgColor='1F4E79')
SUB_FILL = PatternFill('solid', fgColor='DCE9F7')
WARN_FILL = PatternFill('solid', fgColor='FFF2CC')
THIN = Side(style='thin', color='D0D7E2')
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
HFONT = Font(name='微软雅黑', size=9, bold=True, color='FFFFFF')
BFONT = Font(name='微软雅黑', size=9)

DICT = {
    '客户类型': ['企业', '医院', '高校', '研究所', '经销商'],
    '商机类型': ['存量商机', '突破性商机', '探索性商机'],
    '采购方式': ['直接采购', '公开招标', '单一来源', '协议供货', '经销商采购'],
    '经费来源': ['国自然', '省部级基金', '横向课题', '单位自筹', '企业经费', '其他'],
    '经费状态': ['已批复', '申报中', '未确定'],
    '渠道类型': ['直销', '经销'],
    '含税口径': ['含税', '未税'],
    '业务线_诊断': ['诊断原料/PCR', '诊断原料/测序', '诊断原料/分子诊断', '诊断原料/其他'],
    '业务线_生命科学': ['生命科学试剂', '生命科学服务', '生命科学仪器', '生命科学耗材'],
}

wb = Workbook()

# ── 字典 ────────────────────────────────────────────────
wsd = wb.active
wsd.title = '字典'
wsd['A1'] = '下拉字典（新增取值请在此追加，导入前与系统字典核对）'
wsd['A1'].font = Font(name='微软雅黑', size=10, bold=True, color='1F4E79')
col = 1
dict_range = {}
for k, vals in DICT.items():
    c = get_column_letter(col)
    wsd.cell(row=3, column=col, value=k).font = HFONT
    wsd.cell(row=3, column=col).fill = HEAD_FILL
    for i, v in enumerate(vals, start=4):
        cell = wsd.cell(row=i, column=col, value=v)
        cell.font = BFONT
    dict_range[k] = "'字典'!$%s$4:$%s$%d" % (c, c, 3 + len(vals))
    wsd.column_dimensions[c].width = 20
    col += 1
wsd.freeze_panes = 'A4'


def build(ws, cols, widths, dicts, samples, key_cols, note):
    for j, (name, width) in enumerate(zip(cols, widths), start=1):
        c = ws.cell(row=1, column=j, value=name)
        c.font = HFONT; c.fill = HEAD_FILL; c.border = BORDER
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(j)].width = width
    ws.row_dimensions[1].height = 30
    ws.freeze_panes = 'B2'
    ws.auto_filter.ref = 'A1:%s200' % get_column_letter(len(cols))

    # 示例行
    for r, s in enumerate(samples, start=2):
        for j, v in enumerate(s, start=1):
            c = ws.cell(row=r, column=j, value=v)
            c.font = BFONT; c.border = BORDER
            c.alignment = Alignment(vertical='center', wrap_text=True)

    n = 300
    # 下拉
    for key, cols_idx in dicts.items():
        dv = DataValidation(type='list', formula1='=' + dict_range[key], allow_blank=True, showDropDown=False)
        dv.error = '请从下拉中选择：%s' % key
        dv.errorTitle = '取值不在字典内'
        ws.add_data_validation(dv)
        for j in cols_idx:
            cl = get_column_letter(j)
            dv.add('%s2:%s%d' % (cl, cl, n))

    # 年份校验
    yj = cols.index('年份') + 1
    dvy = DataValidation(type='whole', operator='between', formula1='2025', formula2='2035', allow_blank=False)
    dvy.error = '年份需为 2025–2035 的整数'
    dvy.errorTitle = '年份不合法'
    ws.add_data_validation(dvy)
    yl = get_column_letter(yj)
    dvy.add('%s2:%s%d' % (yl, yl, n))

    # 年度合计公式与校验
    tot_j = cols.index('年度目标合计(元)') + 1
    q1_j = cols.index('Q1预测(元)') + 1
    q4_j = q1_j + 3
    chk_j = cols.index('校验') + 1
    tot_l, q1_l, q4_l, chk_l = [get_column_letter(x) for x in (tot_j, q1_j, q4_j, chk_j)]
    key_l = [get_column_letter(cols.index(k) + 1) for k in key_cols]
    for r in range(2, n + 1):
        ws.cell(row=r, column=tot_j).value = '=IF(SUM(%s%d:%s%d)=0,"",SUM(%s%d:%s%d))' % (q1_l, r, q4_l, r, q1_l, r, q4_l, r)
        ws.cell(row=r, column=tot_j).number_format = '#,##0'
        for j in range(q1_j, q1_j + 4):
            ws.cell(row=r, column=j).number_format = '#,##0'
        cond = 'OR(%s="" ,%s="" ,SUM(%s%d:%s%d)<=0)' % (','.join('%s%d=""' % (k, r) for k in key_l), '', q1_l, r, q4_l, r)
        key_cells = ','.join('%s%d' % (k, r) for k in key_l)
        ws.cell(row=r, column=chk_j).value = '=IF(COUNTA(%s,%s%d:%s%d)=0,"",IF(OR(%s,SUM(%s%d:%s%d)<=0),"待补全","OK"))' % (
            key_cells, q1_l, r, q4_l, r, ','.join('%s%d=""' % (k, r) for k in key_l), q1_l, r, q4_l, r)
        ws.cell(row=r, column=chk_j).font = BFONT

    ws.conditional_formatting.add('%s2:%s%d' % (chk_l, chk_l, n),
                                  FormulaRule(formula=['%s2="待补全"' % chk_l], fill=WARN_FILL))

    ws.cell(row=n + 2, column=1, value=note).font = Font(name='微软雅黑', size=8, italic=True, color='7A8899')


# ── 诊断原料 ─────────────────────────────────────────────
ws1 = wb.create_sheet('诊断原料')
cols1 = ['年份', '销售', '部门', '客户名称', '商机名称', '商机ID(飞书)', '货号', '业务线', '商机类型', '客户类型',
         '采购方式', '含税口径', '年度目标合计(元)', 'Q1预测(元)', 'Q2预测(元)', 'Q3预测(元)', 'Q4预测(元)',
         '分布依据', '所需资源保障及时间节点', '填报人', '填报日期', '版本号', '校验']
w1 = [7, 9, 22, 30, 30, 14, 20, 16, 11, 9, 11, 9, 15, 12, 12, 12, 12, 16, 34, 9, 11, 8, 9]
s1 = ['2027', '示例销售', '诊断原料大客户销售部', '示例客户A（请替换）', '示例商机：报证优化', '000000', 'MD2104-003006',
      '诊断原料/PCR', '存量商机', '企业', '直接采购', '含税', None, 300000, 100000, 100000, 500000,
      '按历史月度节奏', '需 2026-12 前完成备货', '示例填报人', '2026-10-31', 'V1', None]
s2 = ['2027', '示例销售', '诊断原料销售拓展部', '示例客户B（请替换）', '示例商机：替换竞品', '000001', 'M2181-4-095203',
      '诊断原料/PCR', '突破性商机', '企业', '公开招标', '含税', None, 0, 200000, 200000, 100000,
      '按招标月份', '需技术支持配合比对', '示例填报人', '2026-10-31', 'V1', None]
build(ws1, cols1, w1,
      {'业务线_诊断': [8], '商机类型': [9], '客户类型': [10], '采购方式': [11], '含税口径': [12]},
      [s1, s2], ['货号', '商机ID(飞书)'],
      '唯一键：年份 + 商机ID + 货号（同一商机多个货号请拆行）。年度目标合计由 Q1–Q4 自动汇总；「校验」列显示“待补全”表示关键字段缺失或金额为 0。')

# ── 生命科学 ─────────────────────────────────────────────
ws2 = wb.create_sheet('生命科学')
cols2 = ['年份', '销售', '部门', '客户单位', '学院/院系', '课题组', 'PI/负责人', '商机名称', '商机ID(飞书)', '货号',
         '业务线', '商机类型', '采购方式', '经费来源', '经费状态', '预计招标/成交月份', '商机阶段/赢率', '渠道类型',
         '含税口径', '年度目标合计(元)', 'Q1预测(元)', 'Q2预测(元)', 'Q3预测(元)', 'Q4预测(元)',
         '分布依据', '所需资源保障及时间节点', '填报人', '填报日期', '版本号', '校验']
w2 = [7, 9, 20, 26, 16, 20, 10, 30, 14, 20, 15, 11, 11, 11, 9, 12, 12, 8, 9, 15,
      12, 12, 12, 12, 16, 34, 9, 11, 8, 9]
s3 = ['2027', '示例销售', '生命科学销售部', '示例大学（请替换）', '示例学院', '示例课题组', '示例PI', '示例商机：国自然结题采购',
      '000002', 'BR1D303-01', '生命科学试剂', '存量商机', '公开招标', '国自然', '已批复', '2027-03',
      '方案确认（60%）', '直销', '含税', None, 50000, 30000, 20000, 10000,
      '按经费执行期', '需 2027-02 前提供报价单', '示例填报人', '2026-10-31', 'V1', None]
s4 = ['2027', '示例销售', '生命科学销售部', '示例研究所（请替换）', '示例中心', '示例课题组2', '示例PI2', '示例商机：平台仪器配套',
      '000003', '示例货号', '生命科学仪器', '探索性商机', '单一来源', '省部级基金', '申报中', '2027-09',
      '需求确认（30%）', '经销', '含税', None, 0, 0, 100000, 100000,
      '按招标公告月份', '需厂家授权与参数支持', '示例填报人', '2026-10-31', 'V1', None]
build(ws2, cols2, w2,
      {'业务线_生命科学': [11], '商机类型': [12], '采购方式': [13], '经费来源': [14], '经费状态': [15],
       '渠道类型': [18], '含税口径': [19], '客户类型': []},
      [s3, s4], ['商机ID(飞书)', '课题组', '货号'],
      '唯一键：年份 + 商机ID + 课题组 + 货号（同一课题组多个货号请拆行）。业务线四选一：生命科学试剂 / 服务 / 仪器 / 耗材。')

# ── 说明 ────────────────────────────────────────────────
ws3 = wb.create_sheet('填写说明', 0)
ws3.column_dimensions['A'].width = 22
ws3.column_dimensions['B'].width = 16
ws3.column_dimensions['C'].width = 108
rows = [
    ('2027 年商机预算导入模板 V02 — 填写说明', '', ''),
    ('', '', ''),
    ('一、两张表的区别', '', ''),
    ('诊断原料', '按企业客户', '一行 = 一个商机 + 一个货号；客户为法人主体，采购方式以直接采购/框架为主。'),
    ('生命科学', '按课题组 + 货号', '一行 = 一个商机 + 一个课题组 + 一个货号；客户单位写学校/研究所，另填学院与 PI。'),
    ('', '', ''),
    ('二、必填字段', '', ''),
    ('两年通用', '必填', '年份、销售、部门、商机名称、商机ID(飞书)、货号、业务线、商机类型、Q1–Q4、填报人、填报日期、版本号'),
    ('诊断原料增补', '必填', '客户名称、客户类型、采购方式、含税口径'),
    ('生命科学增补', '必填', '客户单位、学院/院系、课题组、PI/负责人、采购方式、经费来源、经费状态、预计招标/成交月份、渠道类型、含税口径'),
    ('', '', ''),
    ('三、业务线字典', '', ''),
    ('诊断原料', '4 项', '诊断原料/PCR、诊断原料/测序、诊断原料/分子诊断、诊断原料/其他（以系统字典为准）'),
    ('生命科学', '4 项', '生命科学试剂、生命科学服务、生命科学仪器、生命科学耗材'),
    ('', '', ''),
    ('四、金额与季度', '', ''),
    ('单位', '', '整数元，含税口径由“含税口径”列声明，整表统一。'),
    ('年度目标合计', '', '由 Q1–Q4 自动汇总，不需要手填。'),
    ('分布依据', '', '写明季度拆分的依据：按历史节奏 / 按招标月份 / 按经费执行期 / 按合同节点。'),
    ('', '', ''),
    ('五、唯一键与导入', '', ''),
    ('诊断原料唯一键', '', '年份 + 商机ID + 货号；同键重复时按覆盖处理。'),
    ('生命科学唯一键', '', '年份 + 商机ID + 课题组 + 货号；同一课题组多个货号必须拆行。'),
    ('导入前置校验', '', '商机ID 在飞书不存在、业务线不在字典、必填缺失 → 该行阻断；其余行正常导入。'),
    ('', '', ''),
    ('六、填报纪律', '', ''),
    ('校验列', '', '显示“待补全”= 关键字段为空或 Q1–Q4 合计为 0，提交前必须清零。'),
    ('版本', '', '每轮修改递增版本号（V1→V2），填报日期写实际提交日；不要覆盖上一版文件。'),
    ('不得留空', '', '商机类型、采购方式、经费状态不得用“/”或“待定”占位，未确定填“未确定”。'),
]
for i, (a, b, c) in enumerate(rows, start=1):
    ws3.cell(row=i, column=1, value=a)
    ws3.cell(row=i, column=2, value=b)
    ws3.cell(row=i, column=3, value=c)
    if i == 1:
        ws3.cell(row=i, column=1).font = Font(name='微软雅黑', size=13, bold=True, color='1F4E79')
    elif a and not b and not c:
        ws3.cell(row=i, column=1).font = Font(name='微软雅黑', size=10, bold=True, color='1F4E79')
        ws3.cell(row=i, column=1).fill = SUB_FILL
    else:
        for j in (1, 2, 3):
            ws3.cell(row=i, column=j).font = BFONT
            ws3.cell(row=i, column=j).alignment = Alignment(vertical='top', wrap_text=True)
ws3.freeze_panes = 'A2'

wb.save(OUT)
print('SAVED', OUT)