#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""诊断二：名册与 ERP 的姓名精确匹配（含不可见字符），以及在区间内未映射者的金额。"""
import pandas as pd

BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
ORD = BASE + '/月度销售额数据/销售订单_20260101-20260930.xlsx'
ORG = BASE + '/销售组织架构/统一工作台运行及授权.xlsx'
A, B = '2026-08-20', '2026-09-30'

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)
std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
std = std[~std['客户'].astype(str).str.contains('宝锐|宝泰|横琴', na=False)]
win = std[(std['_d'] >= A) & (std['_d'] <= B)].copy()
win['_sp'] = win['销售员'].astype(str).str.strip()

org = pd.read_excel(ORG, sheet_name='人员清单')
org['_n'] = org['销售员'].astype(str).str.strip()
print('名册人数 %d ｜ 去重后 %d' % (len(org), org['_n'].nunique()))
print('名册里有「刘」的:', [repr(x) for x in org['_n'] if '刘' in x])
print('名册里有「陈」的:', [repr(x) for x in org['_n'] if '陈' in x])
print()
print('=== 区间 %s ~ %s 内出现的销售员 vs 名册 ===' % (A, B))
appear = sorted(set(win['_sp']))
inroster = set(org['_n'])
miss = [n for n in appear if n not in inroster]
print('  ERP 出现 %d 人 ｜ 不在名册 %d 人: %s' % (len(appear), len(miss), [repr(m) for m in miss]))
print()
for n in miss:
    s = win[win['_sp'] == n]
    print('  %-8s 行 %3d ｜ 金额 %9.4f 万 ｜ 客户: %s' % (n, len(s), s['_amt'].sum() / 1e4,
          '、'.join(sorted({str(c) for c in s['客户']})[:3])[:70]))
print()
print('=== 陈彩红 / 刘子研 全部出现情况（不限区间）===')
for n in ('陈彩红', '刘子研'):
    s = std[std['销售员'].astype(str).str.strip() == n]
    print('  %-6s 行 %3d ｜ 金额 %9.4f 万 ｜ 月份 %s' % (n, len(s), s['_amt'].sum() / 1e4,
          dict(s['_d'].str[:7].value_counts().sort_index())))
    if len(s):
        print('        客户样例:', '、'.join(sorted({str(c) for c in s['客户']})[:4])[:90])
print()
print('=== 名册里但区间内无单的生命科学销售部人（应含 11 位离职）===')
ls = org[org['部门'] == '生命科学销售部']
for _, p in ls.iterrows():
    n = p['_n']
    s = win[win['_sp'] == n]
    if not len(s):
        print('  %-6s %-9s %s' % (n, p['区域'], p['是否在职']))