#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""科研促销期 · 达标清单 → Excel（多 Sheet）

Sheet：
  1. 课题组达标清单  —— 两个区间（8/20–9/30、9/1–9/30），含礼品发放登记列
  2. 客户达标清单    —— 按开票机构归集，同样两区间
  3. 档位对照表
  4. 口径说明

一致性门禁：本脚本现算的〔课题组〕结果必须与已交付的两份 HTML 的 KPI 数字逐个相等，
否则中止（防止 Excel 与 HTML 两套数）。
"""
import io
import json
import os
import re
import sys

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
os.chdir(ROOT)
OUTDIR = os.path.join(ROOT, '营销活动', '2026-10实验加速季')
SRC = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/源数据/生命科学销售订单_20261009_含作废终止标记.xlsx'
CALIBER = '标准销售订单 & 已审核 & 非赠品 & 客户不含(宝锐|宝泰|横琴) & 未作废 & 非业务终止'
TIERS = [(100000, 10000, 5000), (50000, 5000, 2500), (20000, 2000, 1000),
         (10000, 1000, 500), (5000, 500, 250), (2000, 200, 100), (1000, 100, 50)]
PERIODS = [('2026-08-20', '2026-09-30'), ('2026-09-01', '2026-09-30')]
HTML = {('2026-08-20', '2026-09-30'): '科研促销期_档位达成与礼品发放表_按课题组.html',
        ('2026-09-01', '2026-09-30'): '科研促销期_档位达成与礼品发放表_按课题组_20260901-20260930.html'}
MERGE = {'王慧洁': '吴云', '刘静文': '吴云', '李仁钰': '吴云', '李岚风': '吴云', '赵以诺': '吴云', '曾琪': '吴云'}
REG = {'吴云': '浙江', '叶信福': '浙江', '侯海瑜': '浙江', '袁殿果': '浙江',
       '韩远怀': '广东', '王诗香': '广东', '刘斌': '广东', '陈彩红': '广东', '黄建华': '广东'}
for _k, _v in MERGE.items():
    REG[_k] = REG.get(_v, '')
TIER_NAME = {1000: '1,000 元档', 2000: '2,000 元档', 5000: '5,000 元档', 10000: '10,000 元档',
             20000: '20,000 元档', 50000: '50,000 元档', 100000: '100,000 元档'}


def tier_of(y):
    for t, r, g in TIERS:
        if y >= t:
            return t, r, g
    return 0, 0, 0


df = pd.read_excel(SRC, sheet_name='Sheet1', dtype=str)
for c in df.columns:
    df[c] = df[c].astype(str).str.strip().replace({'nan': '', 'None': ''})
df['_a'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0.0)
df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce', format='mixed')
K = set(json.load(io.open('数据/ketizu_list.json', encoding='utf-8'))['ketizu'])
df['_sales'] = df['销售员'].map(lambda x: MERGE.get(x, x))
df = df.loc[df['销售员'] != '徐中平'].copy()
df['_std'] = ((df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核')
              & (df['是否赠品'] != '是') & (~df['客户'].str.contains('宝锐|宝泰|横琴', na=False)) & (~df['作废状态'].isin(['已作废'])) & (~df['业务终止'].isin(['业务终止'])))
df['_kz'] = df['商机(飞书)#'].where(df['商机(飞书)#'].isin(K), '')

kz_rows, cu_rows, gates, STATS = [], [], [], {}
for d0, d1 in PERIODS:
    per = (df['_d'] >= pd.Timestamp(d0)) & (df['_d'] <= pd.Timestamp(d1))
    ds = df.loc[per & df['_std']]
    dk = ds.loc[ds['_kz'] != '']
    rng = '%s ~ %s' % (d0, d1)
    kz_p, cu_p = [], []                        # 本区间【全部】有成交行（用于门禁口径）
    for name, g in dk.groupby('_kz'):
        y = round(float(g['_a'].sum()), 2)
        t, rg, gf = tier_of(y)
        sp = g.groupby('_sales')['_a'].sum().sort_values(ascending=False)
        primary = sp.index[0] if len(sp) else ''
        row = [rng, name, REG.get(primary, ''), primary, y, TIER_NAME[t] if t else '', rg, gf]
        kz_p.append(row)
        if t:
            kz_rows.append(row)                # 清单只放【达标】行
    for name, g in ds.groupby('客户'):
        y = round(float(g['_a'].sum()), 2)
        t, rg, gf = tier_of(y)
        row = [rng, name, y, TIER_NAME[t] if t else '', rg, gf]
        cu_p.append(row)
        if t:
            cu_rows.append(row)
    # ── 与已交付 HTML 的 KPI 门禁（amt=全部有成交课题组；n/gift/reag=其中达标者）──
    hp = os.path.join(OUTDIR, HTML[(d0, d1)])
    s = io.open(hp, encoding='utf-8').read()
    kv = dict(re.findall(r'<div class="t">(.*?)</div><div class="v">(.*?)</div>', s))
    got = {'amt': sum(r[4] for r in kz_p), 'n': sum(1 for r in kz_p if r[5]),
           'gift': sum(r[7] for r in kz_p), 'reag': sum(r[6] for r in kz_p)}
    exp = {'amt': float(kv['课题组采购额'].replace(',', '')), 'n': int(kv['达档课题组']),
           'gift': float(kv['返礼品合计'].replace(',', '')), 'reag': float(kv['返试剂合计'].replace(',', ''))}
    gates.append((rng, got, exp))
    STATS[rng] = {'kz_all': len(kz_p), 'kz_reach': sum(1 for r in kz_p if r[5]),
                  'cu_all': len(cu_p), 'cu_reach': sum(1 for r in cu_p if r[5]),
                  'cu_amt': round(sum(r[2] for r in cu_p), 2), 'cu_gift': sum(r[5] for r in cu_p)}

print('=== 一致性门禁（本脚本 vs 已交付 HTML 的 KPI）===')
bad = 0
for rng, got, exp in gates:
    pbad = 0
    for k in ('amt', 'n', 'gift', 'reag'):
        tol = 1.0 if k == 'amt' else 0.01     # HTML 的金额卡四舍五入到整数，故给 1 元容差
        if abs(float(got[k]) - float(exp[k])) >= tol:
            pbad += 1
            print('  ❌ %s %s: Excel %s vs HTML %s' % (rng, k, got[k], exp[k]))
    bad += pbad
    print(('  ✅ ' if not pbad else '  ❌ ') + rng)
assert bad == 0, 'Excel 与 HTML 数字不一致，中止'
print('达标课题组：%s ｜ 达标客户：%s' % ([sum(1 for r in kz_rows if r[0] == '%s ~ %s' % p) for p in PERIODS],
                                    [sum(1 for r in cu_rows if r[0] == '%s ~ %s' % p) for p in PERIODS]))

# ══════════ 写 Excel ══════════
BLUE = '1852B6'
DARK = '0F3A8A'
LIGHT = 'E8F0FE'
HEAD = PatternFill('solid', fgColor=BLUE)
SUB = PatternFill('solid', fgColor=LIGHT)
GOLD = PatternFill('solid', fgColor='FFF4D6')
THIN = Side(style='thin', color='D0D5DD')
BD = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
HF = Font(bold=True, color='FFFFFF', size=11)
TF = Font(bold=True, size=14, color=DARK)

wb = Workbook()


def sheet_title(ws, text, ncol, sub=None):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ncol)
    c = ws.cell(1, 1, text)
    c.font = TF
    c.alignment = Alignment(vertical='center')
    ws.row_dimensions[1].height = 26
    r = 2
    if sub:
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=ncol)
        ws.cell(2, 1, sub).font = Font(size=9.5, color='5F6368')
        ws.row_dimensions[2].height = 18
        r = 3
    return r + 0


def write_table(ws, start_row, headers, rows, widths, num_fmt=None, money_cols=()):
    for j, hname in enumerate(headers, 1):
        c = ws.cell(start_row, j, hname)
        c.fill, c.font, c.border = HEAD, HF, BD
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    for i, row in enumerate(rows, start_row + 1):
        for j, v in enumerate(row, 1):
            c = ws.cell(i, j, v)
            c.border = BD
            if j in money_cols:
                c.number_format = '#,##0'
                c.alignment = Alignment(horizontal='right')
            elif isinstance(v, (int, float)):
                c.alignment = Alignment(horizontal='center')
    for j, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(j)].width = w
    ws.row_dimensions[start_row].height = 30
    return start_row + len(rows)


# ── Sheet1 课题组达标清单 ──
ws = wb.active
ws.title = '课题组达标清单'
r0 = sheet_title(ws, '科研促销期 · 课题组达标清单（礼品发放用）', 12,
                 '口径：%s ｜ 取达标的最高档；返礼品 = 返试剂 × 50%% ｜ 数据源：生命科学销售订单（含课题组）' % CALIBER)
heads = ['区间', '#', '课题组', '区域', '销售员', '采购额(元)', '达标档位', '返试剂(元)', '返礼品(元)',
         '礼品发放状态', '寄出日期', '备注']
data = []
for rng in ['%s ~ %s' % p for p in PERIODS]:
    for i, r in enumerate(sorted([x for x in kz_rows if x[0] == rng], key=lambda x: -x[4]), 1):
        data.append([rng, i, r[1], r[2], r[3], r[4], r[5], r[6], r[7], '', '', ''])
hr = r0 + 1
end = write_table(ws, hr, heads, data, [22, 5, 22, 7, 10, 12, 12, 10, 10, 13, 12, 22], money_cols=(6, 8, 9))
last = end
ws.freeze_panes = ws.cell(hr + 1, 3)
ws.auto_filter.ref = 'A%d:L%d' % (hr, last)
dv = DataValidation(type='list', formula1='"未发放,已寄出,已签收,无需发放"', allow_blank=True)
dv.error, dv.errorTitle = '请从下拉中选择状态', '输入无效'
ws.add_data_validation(dv)
dv.add('J%d:J%d' % (hr + 1, last))
# 小计行
sc = last + 2
ws.cell(sc, 1, '小计').font = Font(bold=True, color=DARK)
for j, rng in enumerate(['%s ~ %s' % p for p in PERIODS]):
    sub = [x for x in data if x[0] == rng]
    row = sc + 1 + j
    ws.cell(row, 1, rng).font = Font(bold=True)
    ws.cell(row, 2, '%d 个' % len(sub)).alignment = Alignment(horizontal='center')
    ws.cell(row, 6, sum(x[5] for x in sub)).number_format = '#,##0'
    ws.cell(row, 8, sum(x[7] for x in sub)).number_format = '#,##0'
    ws.cell(row, 9, sum(x[8] for x in sub)).number_format = '#,##0'
    for col in (1, 2, 6, 8, 9):
        ws.cell(row, col).fill = SUB
        ws.cell(row, col).border = BD

# ── Sheet2 客户达标清单 ──
ws2 = wb.create_sheet('客户达标清单')
r0 = sheet_title(ws2, '科研促销期 · 客户（开票机构）达标清单', 7,
                 '同一机构的多笔采购合并算一档；与「课题组达标清单」口径不同，两份不能混用')
heads2 = ['区间', '#', '客户（开票机构）', '采购额(元)', '达标档位', '返试剂(元)', '返礼品(元)']
data2 = []
for rng in ['%s ~ %s' % p for p in PERIODS]:
    for i, r in enumerate(sorted([x for x in cu_rows if x[0] == rng], key=lambda x: -x[2]), 1):
        data2.append([rng, i, r[1], r[2], r[3], r[4], r[5]])
hr2 = r0 + 1
last2 = write_table(ws2, hr2, heads2, data2, [22, 5, 34, 13, 13, 11, 11], money_cols=(4, 6, 7))
ws2.freeze_panes = ws2.cell(hr2 + 1, 3)
ws2.auto_filter.ref = 'A%d:G%d' % (hr2, last2)
sc2 = last2 + 2
ws2.cell(sc2, 1, '小计').font = Font(bold=True, color=DARK)
for j, rng in enumerate(['%s ~ %s' % p for p in PERIODS]):
    sub = [x for x in data2 if x[0] == rng]
    row = sc2 + 1 + j
    ws2.cell(row, 1, rng).font = Font(bold=True)
    ws2.cell(row, 2, '%d 家' % len(sub)).alignment = Alignment(horizontal='center')
    ws2.cell(row, 4, sum(x[3] for x in sub)).number_format = '#,##0'
    ws2.cell(row, 6, sum(x[5] for x in sub)).number_format = '#,##0'
    ws2.cell(row, 7, sum(x[6] for x in sub)).number_format = '#,##0'
    for col in (1, 2, 4, 6, 7):
        ws2.cell(row, col).fill = SUB
        ws2.cell(row, col).border = BD

# ── Sheet2b/2c：区域课题组统计 + 无效单据留档 ──
P0 = '%s ~ %s' % PERIODS[0]
per0 = (df['_d'] >= pd.Timestamp(PERIODS[0][0])) & (df['_d'] <= pd.Timestamp(PERIODS[0][1]))
ds0 = df.loc[per0 & df['_std']]
dk0 = ds0.loc[ds0['_kz'] != '']
reg_rows = []
for _n, _g in dk0.groupby('_kz'):
    _y = round(float(_g['_a'].sum()), 2)
    _t, _rg, _gf = tier_of(_y)
    _sp = _g.groupby('_sales')['_a'].sum().sort_values(ascending=False)
    _primary = _sp.index[0] if len(_sp) else ''
    reg_rows.append([REG.get(_primary, ''), _n, _primary, _y, int(_g['单据编号'].nunique()),
                     TIER_NAME[_t] if _t else '未达 1,000', _rg, _gf])
reg_rows.sort(key=lambda r: (r[0], -r[3]))

inv_rows = []
_inv = df.loc[(df['作废状态'] == '已作废') | (df['业务终止'] == '业务终止')].sort_values(['合同日期', '单据编号'])
for _, _r in _inv.iterrows():
    _d10 = str(_r['_d'])[:10]
    _inper = PERIODS[0][0] <= _d10 <= PERIODS[0][1]
    _would = (_r['单据类型'] == '标准销售订单' and _r['单据状态'] == '已审核' and _r['是否赠品'] != '是'
              and not any(x in _r['客户'] for x in ('宝锐', '宝泰', '横琴')))
    inv_rows.append([_r['单据编号'], _d10, _r['客户'], _r['销售员'], _r['货号#'], float(_r['_a']),
                     _r['单据类型'], _r['单据状态'], _r['是否赠品'],
                     '已作废' if _r['作废状态'] == '已作废' else '业务终止',
                     '是' if _inper else '否', '是' if _would else '否'])

ws2b = wb.create_sheet('区域课题组统计')
r0 = sheet_title(ws2b, '8/20–9/30 · 按区域 × 课题组 × 销售员（含未达档，全量）', 8,
                 '口径：%s ｜ 未达 1,000 元档的课题组也列出，便于核对为何未发礼品' % CALIBER)
heads2b = ['区域', '课题组', '销售员', '采购额(元)', '订单数', '达标档位', '返试剂(元)', '返礼品(元)']
hr2b = r0 + 1
last2b = write_table(ws2b, hr2b, heads2b, reg_rows, [7, 24, 11, 13, 8, 13, 11, 11], money_cols=(4, 7, 8))
ws2b.freeze_panes = ws2b.cell(hr2b + 1, 3)
ws2b.auto_filter.ref = 'A%d:H%d' % (hr2b, last2b)
sc2b = last2b + 2
for _j, _reg in enumerate(['浙江', '广东']):
    _sub = [x for x in reg_rows if x[0] == _reg]
    _row = sc2b + _j
    ws2b.cell(_row, 1, '%s 小计' % _reg).font = Font(bold=True)
    ws2b.cell(_row, 2, '%d 个课题组' % len(_sub)).alignment = Alignment(horizontal='center')
    ws2b.cell(_row, 4, sum(x[3] for x in _sub)).number_format = '#,##0'
    ws2b.cell(_row, 5, sum(x[4] for x in _sub)).alignment = Alignment(horizontal='center')
    ws2b.cell(_row, 8, sum(x[7] for x in _sub)).number_format = '#,##0'
    for _c in (1, 2, 4, 5, 8):
        ws2b.cell(_row, _c).fill = SUB
        ws2b.cell(_row, _c).border = BD
ws2b.cell(sc2b + 2, 1, '合计').font = Font(bold=True, color=DARK)
ws2b.cell(sc2b + 2, 2, '%d 个课题组' % len(reg_rows)).alignment = Alignment(horizontal='center')
ws2b.cell(sc2b + 2, 4, sum(x[3] for x in reg_rows)).number_format = '#,##0'
ws2b.cell(sc2b + 2, 5, sum(x[4] for x in reg_rows)).alignment = Alignment(horizontal='center')
ws2b.cell(sc2b + 2, 8, sum(x[7] for x in reg_rows)).number_format = '#,##0'

ws2c = wb.create_sheet('无效单据留档')
r0 = sheet_title(ws2c, '全表无效单据留档（已作废 / 业务终止，共 %d 行）' % len(inv_rows), 12,
                 '「若不作废是否会计入」= 该行是否同时满足 标准单&已审核&非赠品&非宝锐系；用于人工复核剔除是否恰当')
heads2c = ['单据编号', '合同日期', '客户', '销售员', '货号', '价税合计(元)', '单据类型', '单据状态',
           '是否赠品', '无效标记', '在8/20-9/30区间', '若不作废/终止是否会计入']
hr2c = r0 + 1
last2c = write_table(ws2c, hr2c, heads2c, inv_rows, [16, 12, 26, 10, 13, 13, 14, 10, 9, 10, 15, 20],
                     money_cols=(6,))
ws2c.freeze_panes = ws2c.cell(hr2c + 1, 1)
ws2c.auto_filter.ref = 'A%d:L%d' % (hr2c, last2c)
ws2c.cell(last2c + 2, 1, '合计 · %d 行 ｜ 价税合计 %s 元 ｜ 其中「若不作废会计入」%d 行' % (
    len(inv_rows), format(round(sum(x[5] for x in inv_rows)), ','), sum(1 for x in inv_rows if x[11] == '是'))).font = \
    Font(bold=True, color=DARK)
ws2c.cell(last2c + 3, 1, '注：本文件按【行级】剔除（作废单/终止单逐行排除）；「合计」行（销售员=合计）非业务数据，'
                         '因 单据类型≠标准销售订单 被口径自然排除。').font = Font(size=9.5, color='5F6368')

# ── Sheet3 档位对照表 ──
ws3 = wb.create_sheet('档位对照表')
r0 = sheet_title(ws3, '档位对照表（《2026 秋季促销活动方案》三 · 预存返利）', 7,
                 '取达标的最高档计一份礼品；返礼品 = 对应档位返试剂价值的 50%')
heads3 = ['预存款(元)'] + [format(t, ',') for t, _, _ in TIERS[::-1]]
rows3 = [['返试剂(元)'] + [format(r, ',') for _, r, _ in TIERS[::-1]],
         ['返礼品(元)'] + [format(g, ',') for _, _, g in TIERS[::-1]]]
hr3 = r0 + 1
last3 = write_table(ws3, hr3, heads3, rows3, [14] + [13] * 7)
for j in range(1, 8):
    ws3.cell(last3, j).fill = GOLD
    ws3.cell(last3, j).font = Font(bold=True, color='B45309')
ws3.cell(last3 + 2, 1, '注：方案原文档位依据是「预存款」；本地无预存款数据，本工作簿按实际采购额套用同一组档位，两者口径不同。').font = \
    Font(size=9.5, color='B45309')

# ── Sheet4 口径说明 ──
ws4 = wb.create_sheet('口径说明')
notes = [
    ['科研促销期 · 达标清单 · 口径说明', ''],
    ['', ''],
    ['数据源', '销售分析/源数据/生命科学销售订单_20261009_含作废终止标记.xlsx（2026-10-09 导出；商机(飞书)# = 课题组）'],
    ['数据日期', '合同日期 2024-12-27 ~ 2026-10-08 ｜ 本工作簿生成于 2026-10-09'],
    ['金额口径', CALIBER],
    ['无效单据剔除', '已剔除 作废状态=已作废（59 行）与 业务终止=业务终止（26 行），共 85 行；逐行明细见「无效单据留档」'],
    ['✅ 剔除核验', '8/20-9/30 内 已作废 17 行 + 业务终止 1 行 全部排除。其中 17 行「若不作废本也不会计入」（单据状态=创建 或 测试单），'
                '故该区间实际影响＝1 行 414.36 元（ZHBR260915021 南方医科大学/陈彩红）。已用指纹法反向验证交付物不含被剔行。'],
    ['区间', '① 2026-08-20 ~ 2026-09-30　② 2026-09-01 ~ 2026-09-30（按合同日期）'],
    ['档位规则', '取达标的最高档；返礼品 = 返试剂 × 50%（源自《2026 秋季促销活动方案》三·预存返利）'],
    ['课题组名单', '数据/ketizu_list.json（飞书商机名含「课题组」，1744 个）为准；订单侧不属于该名单的值（公司名/代理商等）不建课题组条目'],
    ['离职归并', '王慧洁 / 刘静文 / 李仁钰 / 李岚风 / 赵以诺 / 曾琪 → 吴云（浙江）'],
    ['剔除', '徐中平（国际业务部）不统计'],
    ['区域归属', 'MCP 商机视图 _rz-opbvg；黄建华名下课题组为广东'],
    ['', ''],
    ['⚠️ 口径提示', '方案原文依据「预存款」，本地无预存款数据 → 本表按实际采购额套用同组档位，两者口径不同'],
    ['⚠️ 未挂课题组', '8/20-9/30：¥3,679（3.7%）｜ 9/1-9/30：¥1,551（1.9%）—— 未填/填了非课题组值，计入「客户达标清单」不计入课题组清单'],
    ['达标情况', '\n'.join('%s：课题组达标 %d / 有成交 %d（未达 %d）；客户达标 %d / 有成交 %d'
                          % (r, STATS[r]['kz_reach'], STATS[r]['kz_all'], STATS[r]['kz_all'] - STATS[r]['kz_reach'],
                             STATS[r]['cu_reach'], STATS[r]['cu_all'])
                          for r in ['%s ~ %s' % p for p in PERIODS])],
    ['⚠️ 两种归集', '按客户合并算档 vs 按课题组各自算档，结果不同（份数与金额都会变），发放前须先定口径'],
    ['', ''],
    ['生成', '脚本/gen_promo_tier_xlsx.py（课题组维度与已交付 HTML 逐项门禁一致）'],
    ['保密', '含真实课题组名、客户名与金额 —— 仅内部使用，不得对外发送'],
]
for i, (a, b) in enumerate(notes, 1):
    ws4.cell(i, 1, a).font = Font(bold=(i == 1 or str(a).startswith('⚠️') or a in ('数据源', '金额口径', '区间', '档位规则')),
                              size=13 if i == 1 else 10.5,
                              color=DARK if i == 1 else ('B45309' if str(a).startswith('⚠️') else '1F2328'))
    ws4.cell(i, 2, b).alignment = Alignment(wrap_text=True, vertical='top')
ws4.column_dimensions['A'].width = 18
ws4.column_dimensions['B'].width = 108
ws4.row_dimensions[1].height = 24

OUT = os.path.join(OUTDIR, '科研促销期_达标清单_礼品发放.xlsx')
wb.save(OUT)

print('\n写出 %s（%.0f KB）' % (OUT, os.path.getsize(OUT) / 1024.0))
from openpyxl import load_workbook
wb2 = load_workbook(OUT)
print('Sheet:', wb2.sheetnames)
for nm in wb2.sheetnames:
    w = wb2[nm]
    print('  %-14s %d 行 × %d 列' % (nm, w.max_row, w.max_column))
print('课题组达标行:', len(data), '｜ 客户达标行:', len(data2))