#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""科研促销期 · 档位达成与礼品发放表（按课题组）
区间 2026-08-20 ~ 2026-09-30；口径与《按客户》版完全一致，仅归集维度换成【课题组】。
档位规则（源自《2026 秋季促销活动方案》三、预存返利，与按客户版同一份实现）：
  取达标的最高档；返试剂 = 档位值；返礼品 = 返试剂 × 50%。
守恒：Σ课题组采购额 = 区间挂课题组金额；达档课题组返礼品合计 = Σ逐组。
"""
import io
import json
import os
import re
import subprocess
import sys
import tempfile
from collections import Counter

import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
os.chdir(ROOT)
sys.path.insert(0, os.path.join(ROOT, '脚本'))
import brand_kit as BK                                     # noqa: E402

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


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), '')
per = (df['_d'] >= pd.Timestamp(D0)) & (df['_d'] <= pd.Timestamp(D1))
d = df.loc[per].copy()
ds = d.loc[d['_std']].copy()
dk = ds.loc[ds['_kz'] != '']
tot_all, tot_kz = float(ds['_a'].sum()), float(dk['_a'].sum())

rows = []
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 ''
    rows.append({'课题组': name, '区域': REG.get(primary, ''), '销售员': primary, '金额': y,
                 '订单': int(g['单据编号'].nunique()), '客户': int(g['客户'].nunique()),
                 '档位': t, '返试剂': rg, '返礼品': gf})
rows.sort(key=lambda r: -r['金额'])
reach = [r for r in rows if r['档位']]
dist = Counter(r['档位'] for r in reach)
gift_tot = sum(r['返礼品'] for r in rows)
reag_tot = sum(r['返试剂'] for r in rows)
assert abs(sum(r['金额'] for r in rows) - tot_kz) < 0.01, '守恒失败'
assert gift_tot * 2 == reag_tot, '返礼品应为返试剂 50%%：%s / %s' % (gift_tot, reag_tot)

# ── 客户维度（同区间同口径【现算】，供对照；不再读别的产物，避免换区间张冠李戴）──
crows = []
for cname, g in ds.groupby('客户'):
    y = round(float(g['_a'].sum()), 2)
    t, rg, gf = tier_of(y)
    crows.append({'金额': y, '档位': t, '返试剂': rg, '返礼品': gf})
creach = [r for r in crows if r['档位']]
cgift, creag = sum(r['返礼品'] for r in crows), sum(r['返试剂'] for r in crows)
ctot = round(sum(r['金额'] for r in crows), 2)
assert abs(ctot - tot_all) < 0.01, '客户守恒失败 %s vs %s' % (ctot, tot_all)
assert cgift * 2 == creag, '客户级返礼品应为返试剂 50%%'

# 对照块所需数值：直接用上面现算的客户级结果
ref = {'amt': format(round(ctot), ','), 'n': str(len(crows)), 'reach': str(len(creach)),
       'gift': format(cgift, ','), 'reag': format(creag, ',')}

# 区间内只有测试/赠品、无采购额的课题组
only_test = set(d.loc[(d['_kz'] != '') & (~d['_std']), '_kz']) - set(dk['_kz'])

print('区间 %s ~ %s ｜ 采购额(挂课题组) %.2f 元 ｜ 课题组 %d ｜ 达档 %d ｜ 返礼品 %d ｜ 返试剂 %d'
      % (D0, D1, tot_kz, len(rows), len(reach), gift_tot, reag_tot))
print('档位分布:', dict(sorted(dist.items())))
print('按客户对照: %d 家 ｜ 采购额 %.2f 元 ｜ 达档 %d ｜ 返礼品 %d ｜ 返试剂 %d'
      % (len(crows), ctot, len(creach), cgift, creag))
print('区间仅有测试/赠品的课题组: %d 个' % len(only_test))
print('TOP6:' + ' | '.join('%s %.0f→%d档' % (r['课题组'], r['金额'], r['档位']) for r in rows[:6]))

CSS = """
*{box-sizing:border-box}
body{margin:0;background:#f5f7fa;color:#1f2328;font:14px/1.65 -apple-system,"PingFang SC","Microsoft YaHei",sans-serif}
.wrap{max-width:1240px;margin:0 auto;padding:18px}
h1{font-size:23px;font-weight:900;color:#0F3A8A;margin:8px 0 4px}
.sub{color:#5f6368;font-size:13px;margin-bottom:14px}
.kpi{display:flex;gap:10px;flex-wrap:wrap;margin-bottom:14px}
.k{background:#fff;border-radius:10px;padding:12px 16px;min-width:140px;border-left:4px solid #1852B6;box-shadow:0 1px 3px rgba(16,24,40,.08)}
.k .t{font-size:12px;color:#5f6368}.k .v{font-size:22px;font-weight:900;color:#1852B6;line-height:1.25}
.k .u{font-size:11px;color:#98A2B3}
.k.gold{border-left-color:#E9A800}.k.gold .v{color:#B45309}
.sec{background:#fff;border-radius:12px;padding:15px 17px;margin-bottom:14px;box-shadow:0 1px 3px rgba(16,24,40,.08)}
.sec h2{font-size:16px;font-weight:900;color:#0F3A8A;margin:0 0 11px;border-left:4px solid #1852B6;padding-left:10px}
.bar{display:flex;gap:9px;align-items:center;flex-wrap:wrap;margin-bottom:10px}
input[type=text]{border:1px solid #D0D5DD;border-radius:8px;padding:6px 10px;font-size:13px;min-width:210px}
.btn{border:1px solid #D0D5DD;background:#fff;border-radius:8px;padding:6px 12px;font-size:13px;cursor:pointer;color:#344054}
.btn:hover{background:#F2F6FD;border-color:#1852B6;color:#1852B6}
.tw{overflow-x:auto}
table{width:100%;border-collapse:collapse;font-size:13px}
th,td{padding:7px 9px;border-bottom:1px solid #EEF1F5}
th{background:#F0F4FB;color:#0F3A8A;font-weight:700;text-align:left;position:sticky;top:0}
th.r,td.r{text-align:right}th.c,td.c{text-align:center}
td{font-variant-numeric:tabular-nums}
tbody tr:hover{background:#F7FAFF}
tr.hit{background:#FFFBF0}
.tg{display:inline-block;border-radius:6px;padding:1px 8px;font-size:12px;font-weight:800;background:#E8F0FE;color:#1852B6}
.tg.no{background:#F1F3F6;color:#98A2B3;font-weight:600}
.tg.g{background:#FFF4D6;color:#B45309}
.warn{background:#FFF8E6;border-left:4px solid #E9A800;border-radius:9px;padding:11px 14px;font-size:13px;margin-bottom:13px}
.warn b{color:#8A6100}
.note{background:#EEF4FF;border-left:4px solid #1852B6;border-radius:9px;padding:11px 14px;font-size:13px;margin-bottom:13px}
.foot{background:#fff;border-radius:12px;padding:15px 17px;font-size:12.5px;color:#475467;line-height:1.9}
.foot h3{font-size:15px;color:#0F3A8A;margin:0 0 7px}
.bars{display:flex;gap:8px;align-items:flex-end;height:120px;margin:8px 0}
.bars .b{flex:1;text-align:center}
.bars .b i{display:block;background:linear-gradient(180deg,#4E9BFF,#1852B6);border-radius:5px 5px 0 0}
.bars .b span{font-size:11px;color:#5f6368;display:block;margin-top:4px}
.bars .b em{font-size:12px;font-weight:800;color:#1852B6;font-style:normal;display:block}
"""

den = max(dist.values()) if dist else 1
bars = ''.join('<div class="b"><em>%d</em><i style="height:%.0fpx"></i><span>%s元</span></div>'
               % (dist.get(t, 0), max(3, dist.get(t, 0) / den * 88), format(t, ','))
               for t in [1000, 2000, 5000, 10000, 20000, 50000, 100000])

body = ''
for i, r in enumerate(rows, 1):
    hit = ' class="hit"' if r['档位'] else ''
    tg = ('<span class="tg g">达档 %s 元</span>' % format(r['档位'], ',')) if r['档位'] else '<span class="tg no">未达 1,000</span>'
    body += ('<tr%s><td class="c">%d</td><td><b>%s</b></td><td class="c">%s</td><td>%s</td>'
             '<td class="c">%d</td><td class="r">%s</td><td class="c">%s</td><td class="r">%s</td><td class="r"><b>%s</b></td></tr>'
             % (hit, i, r['课题组'], r['区域'] or '—', r['销售员'] or '—', r['订单'],
                format(round(r['金额']), ','), tg,
                format(r['返试剂'], ',') if r['返试剂'] else '—',
                format(r['返礼品'], ',') if r['返礼品'] else '—'))

ref_block = ''
if ref:
    ref_block = f"""
<div class="sec"><h2>两种归集口径对照（同一区间、同一档位规则）</h2>
<div class="tw"><table><thead><tr><th>归集维度</th><th class="c">单位数</th><th class="r">采购额(元)</th>
<th class="c">达档</th><th class="r">返礼品(元)</th><th class="r">返试剂(元)</th><th>说明</th></tr></thead><tbody>
<tr><td>按 <b>客户（开票机构）</b></td><td class="c">{ref['n']}</td><td class="r">{ref['amt']}</td>
<td class="c">{ref['reach']}</td><td class="r">{ref['gift']}</td><td class="r">{ref['reag']}</td>
<td>同一机构的多笔采购合并算一档</td></tr>
<tr class="hit"><td>按 <b>课题组</b>（本表）</td><td class="c">{len(rows)}</td><td class="r">{tot_kz:,.0f}</td>
<td class="c">{len(reach)}</td><td class="r"><b>{gift_tot:,}</b></td><td class="r">{reag_tot:,}</td>
<td>同一机构拆到多个课题组，各自算档</td></tr>
</tbody></table></div>
<div class="sub" style="margin:9px 0 0">按课题组归集会<b>拆细</b>：原先靠一家机构合并达标的大额单，
拆到各组后可能落到更低档甚至未达 1,000 元；反之小组独立达标的家数会变多。礼品预算按哪种口径发放，请先定。</div></div>"""

html = f"""<!DOCTYPE html>
<html lang="zh-CN"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>科研促销期 · 档位达成与礼品发放表（按课题组）</title><style>{CSS}</style></head><body><div class="wrap">

<h1>科研促销期 · 档位达成与礼品发放表（按课题组）</h1>
<div class="sub">口径：促销期内<b>课题组累计采购额</b>套用「预存返利」7 档 ｜ 区间 <b>{D0} ~ {D1}</b>
 ｜ 生命科学销售部 ｜ 内部资料</div>

<div class="note">本表是《按客户》版的<b>课题组维度重算</b>：区间、口径、档位规则完全一致，只把归集单位从「开票客户」换成「课题组」。</div>

<div class="warn"><b>⚠️ 三点必须知道：</b>
① 方案原文的档位依据是<b>预存款</b>，本地无预存款数据 —— 本表按<b>实际采购额</b>套用同一组档位，<b>两者口径不同</b>；
若最终按预存发礼品，需先拿到预存款导出。<br>
② 金额＝<b>{CALIBER}</b>（测试单/赠品行金额为 0，天然不参与算档）。<br>
③ 区间内还有 <b>{len(only_test)}</b> 个课题组只有测试/赠品记录、无采购额，不计入本表。</div>

<div class="kpi">
  <div class="k"><div class="t">课题组采购额</div><div class="v">{tot_kz:,.0f}</div><div class="u">元（含税）</div></div>
  <div class="k"><div class="t">有成交课题组</div><div class="v">{len(rows)}</div><div class="u">个</div></div>
  <div class="k gold"><div class="t">达档课题组</div><div class="v">{len(reach)}</div><div class="u">≥1,000 元</div></div>
  <div class="k gold"><div class="t">返礼品合计</div><div class="v">{gift_tot:,}</div><div class="u">元</div></div>
  <div class="k"><div class="t">返试剂合计</div><div class="v">{reag_tot:,}</div><div class="u">元（=返礼品×2）</div></div>
</div>

<div class="sec"><h2>档位分布（达档 {len(reach)} 个课题组）</h2><div class="bars">{bars}</div>
<div class="sub" style="margin:0">各档家数标注在柱顶。</div></div>

<div class="sec"><h2>明细（按采购额降序）</h2>
<div class="bar"><input type="text" id="q" placeholder="筛选：课题组 / 销售 / 区域 / 只输「达档」…">
  <button class="btn" onclick="csv()">导出 CSV</button><span class="sub" id="n" style="margin:0"></span></div>
<div class="tw"><table id="t"><thead><tr>
<th class="c" style="width:34px">#</th><th>课题组</th><th class="c" style="width:64px">区域</th>
<th style="width:74px">销售员</th><th class="c" style="width:58px">订单</th><th class="r" style="width:104px">采购额(元)</th>
<th class="c" style="width:104px">达标档位</th><th class="r" style="width:92px">返试剂(元)</th><th class="r" style="width:92px">返礼品(元)</th>
</tr></thead><tbody>{body}</tbody></table></div></div>
{ref_block}
<div class="sec"><h2>档位对照表（方案原文 · 预存返利）</h2>
<table><thead><tr><th>预存款(元)</th>{''.join('<th class="r">%s</th>' % format(t, ',') for t, _, _ in TIERS[::-1])}</tr></thead>
<tbody><tr><td>返试剂(元)</td>{''.join('<td class="r">%s</td>' % format(r, ',') for _, r, _ in TIERS[::-1])}</tr>
<tr class="hit"><td><b>返礼品(元)</b></td>{''.join('<td class="r"><b>%s</b></td>' % format(g, ',') for _, _, g in TIERS[::-1])}</tr>
</tbody></table>
<div class="sub" style="margin:9px 0 0">取「达标的最高档」计一份礼品（方案要求：一笔预付款、一份赠品、一张签收单）。</div></div>

<div class="foot"><h3>口径与勾稽</h3>
① <b>金额</b>：{CALIBER}；单位=元（含税），区间按合同日期 {D0} ~ {D1}。<br>
② <b>档位</b>：取自《2026 秋季促销活动方案》三、预存返利规则；返礼品 = 对应档位返试剂价值的 50%。<br>
③ <b>勾稽</b>：Σ课题组采购额 <b>{tot_kz:,.2f}</b> 元 = 逐行相加 ✅；返礼品 {gift_tot:,} = 返试剂 {reag_tot:,} ÷ 2 ✅。<br>
④ <b>未达档</b>：{len(rows) - len(reach)} 个课题组采购额 &lt; 1,000 元，不计礼品。<br>
⑤ <b>未挂课题组</b>：区间另有 ¥3,679.00 的采购额未填/填了非课题组值（占 {100 * (tot_all - tot_kz) / tot_all:.1f}%），不计入本表。<br>
⑥ 本表含真实课题组名与金额，<b>仅内部使用，不得对外发送</b>。
</div></div>
<script>
function rows(){{return [...document.querySelectorAll('#t tbody tr')];}}
var comp=false;
document.getElementById('q').addEventListener('compositionstart',function(){{comp=true;}});
document.getElementById('q').addEventListener('compositionend',function(){{comp=false;flt();}});
document.getElementById('q').addEventListener('input',function(){{if(!comp)flt();}});
function flt(){{
  var q=(document.getElementById('q').value||'').trim().toLowerCase();
  var qq=q.replace(/,/g,'');
  var v=0;
  rows().forEach(function(tr){{var ok=!qq||tr.innerText.toLowerCase().replace(/,/g,'').indexOf(qq)>=0;tr.style.display=ok?'':'none';if(ok)v++;}});
  document.getElementById('n').textContent='显示 '+v+' / '+rows().length+' 行';
}}
function csv(){{
  var t=document.getElementById('t');
  var hs=[...t.querySelectorAll('thead th')].map(function(x){{return x.innerText.trim();}});
  var ls=[hs.join(',')];
  rows().forEach(function(tr){{if(tr.style.display==='none')return;
    ls.push([...tr.children].map(function(td){{return '"'+td.innerText.trim().replace(/"/g,'""')+'"';}}).join(','));}});
  var b=new Blob(['\\ufeff'+ls.join('\\n')],{{type:'text/csv;charset=utf-8'}});
  var a=document.createElement('a');a.href=URL.createObjectURL(b);a.download='科研促销期_档位礼品_按课题组.csv';a.click();
}}
flt();
</script>
</body></html>
"""

html = BK.inject(html, theme='light', tag='营销中心 · 销售管理',
                 footer_extra='科研促销期档位达成与礼品发放表（按课题组）｜ %s~%s' % (D0, D1))
io.open(OUT, 'w', encoding='utf-8').write(html)

print('\n=== 质检 ===')
vr = BK.verify(html)
bad = [k for k, v in vr.items() if not v]
print(('✅' if not bad else '❌'), '品牌套件', '全 True' if not bad else bad)
print(('✅' if not re.findall(r'(?:src|href)\s*=\s*["\'](?:https?:)?//', html) else '❌'), '外链 0')
blocks = re.findall(r'<script[^>]*>(.*?)</script>', html, re.S)
f = 0
for b in blocks:
    fd, p = tempfile.mkstemp(suffix='.js')
    os.write(fd, b.encode())
    os.close(fd)
    r = subprocess.run(['node', '--check', p], capture_output=True, text=True)
    if r.returncode:
        f += 1
        print('   ❌', r.stderr.strip().splitlines()[-1][:110])
    os.unlink(p)
print(('✅' if not f else '❌'), 'node --check：%d 块 / %d 报错' % (len(blocks), f))
print(('✅' if '出单' not in html else '❌'), '「出单」字眼计数 %d' % html.count('出单'))
print('  输出 %s（%.0f KB）' % (OUT, os.path.getsize(OUT) / 1024.0))