#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""出数：2026-08-20 ~ 2026-09-30 生命科学「按区域 × 课题组 × 销售员」销售金额统计表。
口径（用户 2026-10-08 拍板）：标准销售订单 & 已审核 & 非赠品 & 剔宝锐系；剔除徐中平（国际业务部）；
离职者按区域归并（王慧洁/刘静文/李仁钰/李岚风/赵以诺/曾琪 → 吴云）。
守恒门禁：Σ课题组 = Σ销售员 = Σ区域 = 区间挂靠金额；区间总额 − 挂靠金额 = 未挂课题组金额。
"""
import io
import json
import os
import sys

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, D1 = '2026-08-20', '2026-09-30'
CALIBER = '标准销售订单 & 已审核 & 非赠品 & 客户不含(宝锐|宝泰|横琴) & 未作废 & 非业务终止'
MERGE = {'王慧洁': '吴云', '刘静文': '吴云', '李仁钰': '吴云', '李岚风': '吴云', '赵以诺': '吴云', '曾琪': '吴云'}
REG = {'吴云': '浙江', '叶信福': '浙江', '侯海瑜': '浙江', '袁殿果': '浙江',
       '韩远怀': '广东', '王诗香': '广东', '刘斌': '广东', '陈彩红': '广东', '黄建华': '广东'}
for _k, _v in MERGE.items():
    REG[_k] = REG.get(_v, '')

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), '')
df['_reg'] = df['_sales'].map(REG).fillna('')

per = (df['_d'] >= pd.Timestamp(D0)) & (df['_d'] <= pd.Timestamp(D1))
d_all = df.loc[per & df['_std']]
d = d_all.loc[d_all['_kz'] != ''].copy()
tot_all = float(d_all['_a'].sum())
tot_kz = float(d['_a'].sum())
unattr = tot_all - tot_kz
odn_all = int(df.loc[per & df['_std'], '单据编号'].nunique())

pairs = (d.groupby(['_kz', '_sales', '_reg'], as_index=False)
         .agg(amt=('_a', 'sum'), orders=('单据编号', 'nunique'),
              cust=('客户', lambda s: s.nunique()), custs=('客户', lambda s: sorted(set(s)))))
multi = d.groupby('_kz')['_sales'].nunique()
pairs['multi'] = pairs['_kz'].map(multi) > 1
pairs['amt'] = pairs['amt'].round(2)
pairs = pairs.sort_values(['_reg', 'amt'], ascending=[True, False])
by_reg = d.groupby('_reg').agg(amt=('_a', 'sum'), kz=('_kz', 'nunique'), od=('单据编号', 'nunique')).round(2)
by_sales = (d.groupby(['_reg', '_sales'], as_index=False)
            .agg(amt=('_a', 'sum'), kz=('_kz', 'nunique'), od=('单据编号', 'nunique')).round(2))
by_sales = by_sales.sort_values(['_reg', 'amt'], ascending=[True, False])
by_kz_only = d.groupby('_kz').agg(amt=('_a', 'sum'), sales=('_sales', lambda s: '/'.join(sorted(set(s)))))

# ── 守恒门禁 ──
s_pair, s_reg, s_sales = pairs['amt'].sum(), by_reg['amt'].sum(), by_sales['amt'].sum()
assert abs(s_pair - tot_kz) < 0.01 and abs(s_reg - tot_kz) < 0.01 and abs(s_sales - tot_kz) < 0.01, \
    '守恒失败 %s / %s / %s vs %s' % (s_pair, s_reg, s_sales, tot_kz)
assert abs((tot_kz + unattr) - tot_all) < 0.01
print('守恒门禁 ✅  课题组(对) %.2f = 区域 %.2f = 销售员 %.2f ｜ +未挂 %.2f = 区间总额 %.2f'
      % (s_pair, s_reg, s_sales, unattr, tot_all))
print('区间: %s ~ %s ｜ 标准口径 %d 行 / %.2f 元 ｜ 挂课题组 %.2f 元（占 %.1f%%）｜ 课题组 %d 个 ｜ 订单 %d'
      % (D0, D1, len(d_all), tot_all, tot_kz, 100 * tot_kz / tot_all, by_kz_only.shape[0], d['单据编号'].nunique()))
print('\n按区域:'); print(by_reg.to_string())
print('\n按销售员:'); print(by_sales.to_string())
print('\n前 12 个课题组:')
top = pairs.sort_values('amt', ascending=False).head(12)
for _, r in top.iterrows():
    print('   %-6s %-16s %-5s %10.2f  %d单' % (r['_reg'], r['_kz'], r['_sales'], r['amt'], r['orders']))
print('\n跨销售员的课题组: %d 个 %s' % (int((multi > 1).sum()), list(multi[multi > 1].index)))

# ═══════════ HTML ═══════════
def wan(v):
    return '%.2f 万' % (v / 10000.0)


def trow(cells, cls='', align=None):
    out = []
    for i, c in enumerate(cells):
        a = (align[i] if align else 'left')
        out.append('<td style="text-align:%s%s">%s</td>' % (a, ';font-weight:700' if cls == 'tot' else '', c))
    return '<tr%s>%s</tr>' % (' class="tot"' if cls == 'tot' else '', ''.join(out))


head_ct = ['课题组', '销售员', '销售金额', '订单数', '客户数']
al = ['left', 'left', 'right', 'center', 'center']
kz_html = ''
for reg in ['浙江', '广东', '']:
    sub = pairs[pairs['_reg'] == reg]
    if not len(sub):
        continue
    label = reg or '未标注区域'
    kz_html += '<tr class="reg"><td colspan="5">%s　小计 <b>¥%s</b>（课题组 %d 个 / 订单 %d）</td></tr>' % (
        label, format(round(sub['amt'].sum()), ','), sub['_kz'].nunique(), sub['orders'].sum())
    for _, r in sub.iterrows():
        kz_html += trow(['<b>%s</b>%s' % (r['_kz'], ' <span class="mk">跨人</span>' if r['multi'] else ''),
                         r['_sales'] or '—', '¥' + format(round(r['amt']), ','), r['orders'], r['cust']], align=al)
    kz_html += trow(['<b>%s 合计</b>' % label, '', '<b>¥' + format(round(sub['amt'].sum()), ',') + '</b>',
                     sub['orders'].sum(), ''], 'tot', al)

sal_html = ''
for reg in ['浙江', '广东', '']:
    sub = by_sales[by_sales['_reg'] == reg]
    if not len(sub):
        continue
    label = reg or '未标注区域'
    for _, r in sub.iterrows():
        sal_html += trow([label if r.name == sub.index[0] else '', r['_sales'], '¥' + format(round(r['amt']), ','),
                          r['kz'], r['od']], align=['left', 'left', 'right', 'center', 'center'])
    sal_html += trow(['<b>%s 合计</b>' % label, '', '<b>¥' + format(round(sub['amt'].sum()), ',') + '</b>',
                      sub['kz'].sum(), sub['od'].sum()], 'tot',
                     ['left', 'left', 'right', 'center', 'center'])

html = """<!DOCTYPE html>
<html lang="zh-CN"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>生命科学销售金额统计表 __D0__~__D1__</title><style>
body{margin:0;background:#f5f7fa;color:#1f2328;font:14px/1.7 -apple-system,"PingFang SC","Microsoft YaHei",sans-serif}
.wrap{max-width:1080px;margin:0 auto;padding:20px}
h1{font-size:22px;color:#0F3A8A;margin:6px 0 4px}
.sub{color:#5f6368;font-size:12.5px;margin-bottom:14px}
.sec{background:#fff;border-radius:12px;padding:16px 18px;margin-bottom:14px;box-shadow:0 1px 3px rgba(16,24,40,.08)}
.sec>h2{font-size:16px;color:#0F3A8A;margin:0 0 12px;border-left:4px solid #1852B6;padding-left:10px}
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}
td{font-variant-numeric:tabular-nums}
tbody tr:hover{background:#F7FAFF}
tr.reg td{background:#E8F0FE;color:#0F3A8A;font-weight:800}
tr.tot td{background:#F7F9FC;border-top:2px solid #C9DBF5}
.mk{background:#FFF4D6;color:#B45309;border-radius:5px;padding:0 5px;font-size:11px;font-weight:700}
.kpi{display:grid;grid-template-columns:repeat(4,1fr);gap:10px;margin-bottom:14px}
.kcard{background:#fff;border-radius:11px;padding:12px 14px;box-shadow:0 1px 3px rgba(16,24,40,.08)}
.kcard .v{font-size:22px;font-weight:900;color:#1852B6;font-variant-numeric:tabular-nums}
.kcard .l{font-size:12px;color:#5f6368;margin-top:3px}
.note{background:#F2F6FD;border-radius:9px;padding:11px 14px;font-size:12.5px;color:#475467;line-height:1.9}
.warn{background:#FFF8E6;border-left:4px solid #E9A800;border-radius:9px;padding:11px 14px;font-size:12.5px;margin-top:12px}
</style></head><body><div class="wrap">
<h1>生命科学 · 销售金额统计表</h1>
<div class="sub">区间 <b>__D0__ ~ __D1__</b>　｜　口径：__CAL__　｜　出数日 __TODAY__</div>

<div class="kpi">
 <div class="kcard"><div class="v">¥__TOTALL__</div><div class="l">区间销售金额（标准口径）</div></div>
 <div class="kcard"><div class="v">__KZN__</div><div class="l">有成交课题组</div></div>
 <div class="kcard"><div class="v">__ODN__</div><div class="l">订单数（区间全口径）</div></div>
 <div class="kcard"><div class="v">__SALN__</div><div class="l">成交销售员</div></div>
</div>

<div class="sec"><h2>一、按区域 → 课题组</h2>
<table><thead><tr><th>课题组</th><th>销售员</th><th style="text-align:right">销售金额</th>
<th style="text-align:center">订单数</th><th style="text-align:center">客户数</th></tr></thead>
<tbody>__KZ__</tbody></table></div>

<div class="sec"><h2>二、按区域 → 销售员</h2>
<table><thead><tr><th>区域</th><th>销售员</th><th style="text-align:right">销售金额</th>
<th style="text-align:center">课题组数</th><th style="text-align:center">订单数</th></tr></thead>
<tbody>__SAL__</tbody></table></div>

<div class="sec"><h2>三、口径说明</h2>
<div class="note">
① 金额＝<b>价税合计</b>；口径＝<b>__CAL__</b>。<br>
② <b>离职归并</b>：王慧洁/刘静文/李仁钰/李岚风/赵以诺/曾琪 → <b>吴云</b>（浙江）；已剔除 <b>徐中平</b>（国际业务部，不统计）。<br>
③ 区域归属依据：MCP 商机视图 _rz-opbvg 的课题组 region；黄建华名下课题组均为<b>广东</b>。<br>
④ 课题组挂靠率 <b>__RATIO__</b>：区间总额 __TOTALL_F__，其中挂到课题组 <b>__TOTKZ_F__</b>，未填/非课题组值 <b>__UNATTR_F__</b>（未计入下表）。<br>
⑤ 标 <span class="mk">跨人</span> 者：该课题组本区间由多名销售员共同成交，分列显示。
</div>
<div class="warn"><b>提示：</b>本表为「课题组口径」分解表，与部门/个人 KPI 口径可能不同——个人业绩以 ERP 全口径为准；本表用于看<b>课题组产出与区域分布</b>。</div>
</div>
</div></body></html>
"""
html = (html.replace('__D0__', D0).replace('__D1__', D1).replace('__CAL__', CALIBER)
        .replace('__TODAY__', '2026-10-08')
        .replace('__TOTALL__', format(round(tot_all), ','))
        .replace('__TOTALL_F__', '¥' + format(round(tot_all), ','))
        .replace('__TOTKZ_F__', '¥' + format(round(tot_kz), ','))
        .replace('__UNATTR_F__', '¥' + format(round(unattr), ','))
        .replace('__RATIO__', '%.1f%%' % (100 * tot_kz / tot_all))
        .replace('__KZN__', str(by_kz_only.shape[0]))
        .replace('__ODN__', str(odn_all))
        .replace('__SALN__', str(by_sales['_sales'].nunique()))
        .replace('__KZ__', kz_html).replace('__SAL__', sal_html))
out = BK.inject(html, theme='light', tag='营销中心 · 销售管理',
                footer_extra='生命科学销售金额统计表 %s~%s（课题组口径）' % (D0, D1))
DST = os.path.join(ROOT, '销售分析/生命科学_20260820-20260930_按区域课题组销售员.html')
io.open(DST, 'w', encoding='utf-8').write(out)
vr = BK.verify(out)
bad = [k for k, v in vr.items() if not v]
print('\n写出 %s（%.0f KB）｜ 品牌套件 %s' % (os.path.basename(DST), os.path.getsize(DST) / 1024.0,
                                        '全 True' if not bad else bad))