#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""探针：ERP 销售员 → 部门 的真实分布，定位大客户部客户圈定偏差

关键问题：
  1) ERP 里「大客户1/大客户2」是否作为销售员出现（2025 对照表是岗位名）
  2) 张立娅 / 黄明月 / 刘新元 各卖了多少、卖给谁
  3) 按「客户的主销售员」而非「任一订单销售员」重新圈大客户部，差异多大
"""
import warnings
from collections import defaultdict

warnings.filterwarnings('ignore')
import openpyxl  # noqa: E402

BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
SRC = [('2025', BASE + '/2024和2025历史数据/2025全年订单.xlsx', 'Sheet1'),
       ('2026', BASE + '/月度销售额数据/销售订单_20260101-20261015.xlsx', None)]
M25 = {'2025-%02d' % i for i in range(1, 10)}
M26 = {'2026-%02d' % i for i in range(1, 10)}
BAORUI = ('宝锐', '宝泰', '横琴')

rows = []
for tag, path, sheet in SRC:
    wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
    ws = wb[sheet] if sheet else wb[wb.sheetnames[0]]
    hdr, ix = None, {}
    for r in ws.iter_rows(min_row=1, values_only=True):
        if hdr is None:
            hdr = [str(x or '').strip() for x in r]
            ix = {k: hdr.index(k) for k in hdr if k in
                  ('货号#', '物料名称', '价税合计', '客户', '销售员', '是否赠品', '单据类型', '单据状态', '合同月份', '合同日期')}
            continue
        g = {k: (r[v] if v < len(r) else None) for k, v in ix.items()}
        if '标准' not in str(g.get('单据类型') or ''):
            continue
        if str(g.get('是否赠品') or '').strip() == '是':
            continue
        if '单据状态' in g and str(g.get('单据状态') or '').strip() not in ('已审核', ''):
            continue
        c = str(g.get('客户') or '').strip()
        if not c or any(k in c for k in BAORUI):
            continue
        mon = str(g.get('合同月份') or g.get('合同日期') or '')[:7]
        if (tag == '2025' and mon not in M25) or (tag == '2026' and mon not in M26):
            continue
        try:
            amt = float(g.get('价税合计') or 0)
        except Exception:
            amt = 0.0
        rows.append({'y': tag, 'cust': c, 'sp': str(g.get('销售员') or '').strip(),
                     'amt': amt, 'mon': mon, 'sku': str(g.get('货号#') or '').strip()})
print('█ 有效明细 %d 行（%d 客户）' % (len(rows), len({r['cust'] for r in rows})))

# 1) 销售员总额
tot = defaultdict(float)
for r in rows:
    tot[(r['y'], r['sp'])] += r['amt']
print('\n█ 按销售员（2025 / 2026，万元）')
allsp = sorted({r['sp'] for r in rows}, key=lambda s: -(tot.get(('2026', s), 0) + tot.get(('2025', s), 0)))
for s in allsp:
    print('   %-10s 2025 %8.1f ｜ 2026 %8.1f' % (s or '(空)', tot.get(('2025', s), 0) / 1e4, tot.get(('2026', s), 0) / 1e4))

# 2) 客户主销售员
cust_sp = defaultdict(lambda: defaultdict(float))
for r in rows:
    cust_sp[r['cust']][(r['y'], r['sp'])] += r['amt']
def dominant(c):
    d = cust_sp[c]
    for y in ('2026', '2025'):
        cand = [(a, sp) for (yy, sp), a in d.items() if yy == y]
        if cand:
            cand.sort(reverse=True)
            return cand[0][1], y, sum(a for a, _ in cand)
    return '', '', 0.0

DK_NAMES = {'刘子研', '赵云浩', '大客户1', '大客户2', '黄明月', '张立娅'}
print('\n█ 客户主销售员为「疑似大客户部」的客户（按 2026→2025 金额降序 Top 25）')
sel = []
for c in cust_sp:
    sp, y, amt = dominant(c)
    if sp in DK_NAMES:
        a26 = sum(v for (yy, s), v in cust_sp[c].items() if yy == '2026')
        a25 = sum(v for (yy, s), v in cust_sp[c].items() if yy == '2025')
        sel.append((a26 + a25, c, sp, a25, a26, cust_sp[c]))
sel.sort(reverse=True)
for _, c, sp, a25, a26, d in sel[:25]:
    print('   %-34s 主销 %-5s ｜ 2025 %8.1f → 2026 %8.1f 万 ｜ 全部销售员: %s'
          % (c[:32], sp, a25 / 1e4, a26 / 1e4,
             '、'.join('%s(%.0f)' % (s, v / 1e4) for (yy, s), v in sorted(d.items(), key=lambda kv: -kv[1])[:4])))
print('   合计 %d 家' % len(sel))

# 3) 用「任一订单」口径 vs 「主销售员」口径对比
ANY = {c for c in cust_sp if any(s in DK_NAMES for (yy, s) in cust_sp[c])}
MAIN = {c for c in cust_sp if dominant(c)[0] in DK_NAMES}
print('\n█ 圈定口径对比')
print('   任一订单口径（旧）: %d 家' % len(ANY))
print('   主销售员口径（新）: %d 家' % len(MAIN))
print('   旧有新无（应剔除）: %d 家' % len(ANY - MAIN))
leak = ANY - MAIN
leak_amt = sum(sum(cust_sp[c].values()) for c in leak)
print('   被误纳客户金额合计: %.1f 万' % (leak_amt / 1e4))
for c in sorted(leak, key=lambda c: -sum(cust_sp[c].values()))[:15]:
    sp, y, _ = dominant(c)
    print('      %-34s 主销 %-6s ｜ 金额 %7.1f 万' % (c[:32], sp or '(无)', sum(cust_sp[c].values()) / 1e4))

# 4) 刘新元名下客户（管理挂单？）
lx = [c for c in cust_sp if dominant(c)[0] == '刘新元']
print('\n█ 主销为「刘新元」的客户 %d 家（诊断原料销售部/管理，需人工确认是否属大客户部）' % len(lx))
for c in sorted(lx, key=lambda c: -sum(cust_sp[c].values()))[:15]:
    d = cust_sp[c]
    a25 = sum(v for (yy, s), v in d.items() if yy == '2025')
    a26 = sum(v for (yy, s), v in d.items() if yy == '2026')
    print('      %-34s 2025 %7.1f → 2026 %7.1f 万 ｜ 参与销售员 %s' % (c[:32], a25 / 1e4, a26 / 1e4, '、'.join(sorted({s for (yy, s) in d}))))