#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""科研（生命科学销售部）销售金额排名：2026-08-20 ~ 2026-09-30，按人 + 按区域。

口径（取自管道 脚本/erp_sales_core.py，不是印象）：
  单据类型=='标准销售订单' & 单据状态=='已审核' & 是否赠品!='是' & 客户不匹配 宝锐|宝泰|横琴
  金额 = 价税合计（含税）；区间按 合同日期
区域归并（技能 §二）：区域名含「浙江」→ 浙江大区；含「广州」→ 广州大区（按子串，不枚举后缀）
"""
import io
import json
import os
import sys
from collections import defaultdict

import pandas as pd

BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
ORD = os.path.join(BASE, '月度销售额数据', '销售订单_20260101-20260930.xlsx')
ORG = os.path.join(BASE, '销售组织架构', '统一工作台运行及授权.xlsx')
DEPT = '生命科学销售部'
A, B = '2026-08-20', '2026-09-30'
BAORUI = '宝锐|宝泰|横琴'
OUT_JSON = os.path.join(ROOT, '销售分析', '科研销售_按人与区域_20260820-20260930.json')

ok = True
def chk(label, cond, detail=''):
    global ok
    print('  %s %-40s %s' % ('✅' if cond else '❌', label, detail))
    if not cond:
        ok = False

# ── 读数据 ──
df = pd.read_excel(ORD)
df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce').dt.strftime('%Y-%m-%d')
df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0.0)

org = pd.read_excel(ORG, sheet_name='人员清单')
dept = org[(org['部门'] == DEPT)][['销售员', '区域', '角色', '是否在职', '可下单产品线']].copy()
dept['销售员'] = dept['销售员'].astype(str).str.strip()

def big(r):
    r = str(r)
    if '浙江' in r:
        return '浙江大区'
    if '广州' in r:
        return '广州大区'
    return r
dept['大区'] = dept['区域'].map(big)

# ── 过滤（管道同口径）──
std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
std = std[~std['客户'].astype(str).str.contains(BAORUI, na=False)]
std = std[(std['_d'] >= A) & (std['_d'] <= B)]
print('=== 筛选 ===')
print('  订单件: %s ｜ 合同日期 %s ~ %s' % (os.path.basename(ORD), df['_d'].min(), df['_d'].max()))
print('  区间 %s ~ %s ｜ 有效销售行 %d ｜ 合计 %.4f 万' % (A, B, len(std), std['_amt'].sum() / 1e4))

# ── 部门内逐人 ──
names = set(dept['销售员'])
sub = std[std['销售员'].astype(str).str.strip().isin(names)].copy()
sub['销售员'] = sub['销售员'].astype(str).str.strip()

by_sp = defaultdict(lambda: {'amt': 0.0, 'custs': set(), 'docs': set(), 'rows': 0})
for _, r in sub.iterrows():
    d = by_sp[r['销售员']]
    d['amt'] += float(r['_amt'])
    d['custs'].add(str(r['客户']).strip())
    d['docs'].add(str(r['单据编号']))
    d['rows'] += 1

rows = []
for _, p in dept.iterrows():
    n = p['销售员']
    v = by_sp.get(n, {'amt': 0.0, 'custs': set(), 'docs': set(), 'rows': 0})
    rows.append({'销售员': n, '区域': p['区域'], '大区': p['大区'], '角色': p['角色'],
                 '是否在职': p['是否在职'],
                 '金额': round(v['amt'] / 1e4, 4), '客户数': len(v['custs']),
                 '订单数': len(v['docs']), '行数': v['rows'],
                 '单均': round(v['amt'] / 1e4 / len(v['docs']), 3) if v['docs'] else 0.0})
rows.sort(key=lambda r: -r['金额'])
total = sum(r['金额'] for r in rows)
for i, r in enumerate(rows, 1):
    r['名次'] = i
    r['占比'] = round(r['金额'] * 100.0 / total, 2) if total else 0.0

# ── 按大区 ──
reg = defaultdict(lambda: {'amt': 0.0, 'custs': set(), 'docs': set(), 'people': 0, 'active': 0})
for r in rows:
    g = reg[r['大区']]
    g['amt'] += r['金额']
    g['people'] += 1
    if r['金额'] > 0:
        g['active'] += 1
for _, r in sub.iterrows():
    g = reg[dept.set_index('销售员').loc[r['销售员'], '大区']]
    g['custs'].add(str(r['客户']).strip())
    g['docs'].add(str(r['单据编号']))
regions = [{'大区': k, '金额': round(v['amt'], 4), '客户数': len(v['custs']),
            '订单数': len(v['docs']), '人数': v['people'], '出单人数': v['active'],
            '占比': round(v['amt'] * 100.0 / total, 2) if total else 0.0}
           for k, v in reg.items()]
regions.sort(key=lambda r: -r['金额'])

# ── 校验 ──
print('\n=== 校验 ===')
chk('区间端点 = 2026-09-30（订单件已含）', df['_d'].max() == B, df['_d'].max())
appeared = set(std['销售员'].astype(str).str.strip())
unmapped = sorted(appeared - names)
unmapped_amt = std[std['销售员'].astype(str).str.strip().isin(unmapped)]['_amt'].sum() / 1e4
chk('区间内出现的销售员全部有名册归属', not unmapped,
    ('未映射 %s（%.4f 万）' % (unmapped, unmapped_amt)) if unmapped else '无人缺失')
chk('勾稽：Σ逐人 == 部门合计', abs(sum(r['金额'] for r in rows) - total) < 1e-6)
chk('勾稽：Σ各大区 == 部门合计', abs(sum(r['金额'] for r in regions) - total) < 1e-3,
    '%.4f vs %.4f' % (sum(r['金额'] for r in regions), total))
chk('逐人金额与原始行加总一致',
    abs(sub['_amt'].sum() / 1e4 - sum(r['金额'] for r in rows)) < 1e-3,
    '%.4f vs %.4f' % (sub['_amt'].sum() / 1e4, sum(r['金额'] for r in rows)))

# ── 未计入但持本线权限的人（技能 §三.2 必须主动交代）──
line = org[org['可下单产品线'].astype(str).str.contains('生命科学', na=False)]
outsiders = line[line['部门'] != DEPT][['销售员', '部门', '区域']]
od = std[std['销售员'].astype(str).str.strip().isin(set(outsiders['销售员'].astype(str).str.strip()))]
outs = []
for _, p in outsiders.iterrows():
    a = od[od['销售员'].astype(str).str.strip() == str(p['销售员']).strip()]['_amt'].sum() / 1e4
    if a > 0:
        outs.append({'销售员': p['销售员'], '部门': p['部门'], '区域': p['区域'], '金额': round(a, 4)})

# ── 打印 ──
print('\n=== 按人排名（%s ｜ %s ~ %s）===' % (DEPT, A, B))
print('%-4s %-6s %-7s %-9s %-5s %10s %7s %6s %6s %8s' % ('名次', '销售员', '大区', '区域', '在职', '金额(万)', '占比%', '客户', '订单', '单均(万)'))
for r in rows:
    print('%-4d %-6s %-7s %-9s %-5s %10.4f %7.2f %6d %6d %8.3f'
          % (r['名次'], r['销售员'], r['大区'], r['区域'], r['是否在职'],
             r['金额'], r['占比'], r['客户数'], r['订单数'], r['单均']))
print('%-4s %-6s %-7s %-9s %-5s %10.4f %7.2f' % ('', '合计', '', '', '', total, 100.0))

print('\n=== 按区域（大区）排名 ===')
for r in regions:
    print('  %-7s %10.4f 万 ｜ 占比 %5.2f%% ｜ 人数 %2d（出单 %d）｜ 客户 %d ｜ 订单 %d'
          % (r['大区'], r['金额'], r['占比'], r['人数'], r['出单人数'], r['客户数'], r['订单数']))

print('\n=== 未计入本部门、但持生命科学线权限者（需你确认是否并入）===')
for o in outs:
    print('  %-6s %-14s %-8s %8.4f 万' % (o['销售员'], o['部门'], o['区域'], o['金额']))
if not outs:
    print('  （区间内无金额）')

payload = {'dept': DEPT, 'start': A, 'end': B, 'generated': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M'),
           'src': os.path.basename(ORD), 'roster': '统一工作台运行及授权.xlsx / 人员清单',
           'caliber': "单据类型=标准销售订单 & 单据状态=已审核 & 是否赠品≠是 & 客户不含(宝锐|宝泰|横琴)；金额=价税合计；区间按合同日期",
           'total': round(total, 4), 'people': rows, 'regions': regions,
           'unmapped': unmapped, 'outsiders': outs}
os.makedirs(os.path.dirname(OUT_JSON), exist_ok=True)
io.open(OUT_JSON, 'w', encoding='utf-8').write(json.dumps(payload, ensure_ascii=False, indent=1))
print('\nJSON → %s' % OUT_JSON)
sys.exit(0 if ok else 1)