#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""大客户部客户清单（官方权威口径）

权威源：诊断原料&生命科学客户清单20260930.xls 的「大客户销售部」sheet
        （销售拓展部 / 生命科学销售部 同时导出，用于交叉校验与排除）

规则：
  客户部门 = 官方清单所在 sheet（客户级单值），不再按 ERP 开单人、也不按金额推导。
  ERP 销售员仅用于「该客户在本期有没有流水」。

输出：
  数据/客户清单_官方_20260930.json   三个部门全量（客户→部门→销售员→集团）
  数据/大客户部_客户清单.json        大客户部子集 + 2025/2026 ERP 流水 + 是否在 ERP 出现
"""
import io
import json
import os
import warnings
from collections import defaultdict

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

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
D = W + '/数据/'
BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
SRC_XLS = ('/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/'
           'doc_ec9c274f3021_诊断原料&生命科学客户清单20260930.xls')
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 = ('宝锐', '宝泰', '横琴')
SHEETS = {'大客户销售部': '大客户部', '销售拓展部': '销售拓展部', '生命科学销售部': '生命科学部'}


def read_official():
    wb = xlrd.open_workbook(SRC_XLS)
    out = []
    for sh_name, dept in SHEETS.items():
        sh = wb.sheet_by_name(sh_name)
        # 大客户/拓展：序号|集团|客户|销售员|上市；生命科学：序号|客户|销售员|区域
        if sh_name == '生命科学销售部':
            cols = {'cust': 1, 'sp': 2}
        else:
            cols = {'grp': 1, 'cust': 2, 'sp': 3}
        for r in range(2, sh.nrows):
            cust = str(sh.cell_value(r, cols['cust'])).strip()
            if not cust or cust in ('客户名称',):
                continue
            out.append({'cust': cust, 'dept': dept,
                        'grp': str(sh.cell_value(r, cols.get('grp', 0))).strip() if 'grp' in cols else '',
                        'sp': str(sh.cell_value(r, cols['sp'])).strip()})
    return out


def load_erp():
    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, 'amt': amt})
    return rows


official = read_official()
print('█ 官方清单：%d 行 ｜ 去重客户 %d' % (len(official), len({x['cust'] for x in official})))
from collections import Counter
print('   部门分布:', Counter([x['dept'] for x in official]).most_common())
# 同名跨部门冲突
by_name = defaultdict(set)
for x in official:
    by_name[x['cust']].add(x['dept'])
dup = {k: v for k, v in by_name.items() if len(v) > 1}
print('   跨部门同名客户:', len(dup), list(dup.items())[:5])

rows = load_erp()
print('█ ERP 有效明细 %d 行 ｜ 客户 %d 家' % (len(rows), len({r['cust'] for r in rows})))

erp_amt = defaultdict(lambda: {'2025': 0.0, '2026': 0.0})
for r in rows:
    erp_amt[r['cust']][r['y']] += r['amt']

# 全量官方清单落盘
json.dump({'generated': '2026-10-17', 'source': os.path.basename(SRC_XLS),
           'note': '官方客户清单（2026-09-30 版）：客户部门以本表为准，不按 ERP 开单人推导',
           'customers': official},
          io.open(D + '客户清单_官方_20260930.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)

# 大客户部子集
dk = []
seen = set()
for x in official:
    if x['dept'] != '大客户部' or x['cust'] in seen:
        continue
    seen.add(x['cust'])
    a25 = erp_amt[x['cust']]['2025']
    a26 = erp_amt[x['cust']]['2026']
    dk.append({'cust': x['cust'], 'grp': x['grp'], 'owner': x['sp'],
               'a25': round(a25, 2), 'a26': round(a26, 2),
               'in_erp': (a25 + a26) > 0})

print('\n█ 大客户部（官方）：%d 家' % len(dk))
print('   其中 ERP 有流水: %d 家 ｜ 纯名录（两年都无流水）: %d 家'
      % (sum(1 for x in dk if x['in_erp']), sum(1 for x in dk if not x['in_erp'])))
print('   2025 合计 %.1f 万 ｜ 2026 合计 %.1f 万'
      % (sum(x['a25'] for x in dk) / 1e4, sum(x['a26'] for x in dk) / 1e4))

# 与旧清单对比
oldp = D + '大客户部_客户清单.json'
if os.path.exists(oldp):
    old = {x['cust'] for x in json.load(io.open(oldp, encoding='utf-8'))['customers']}
    new = {x['cust'] for x in dk}
    print('\n█ 与旧版（推导口径 %d 家）对比' % len(old))
    print('   旧有新无（错纳，剔除）%d:' % len(old - new))
    for c in sorted(old - new):
        amt = erp_amt[c]
        print('      - %-38s 2025 %6.1f → 2026 %6.1f 万' % (c[:36], amt['2025'] / 1e4, amt['2026'] / 1e4))
    print('   新有旧无（漏掉，补入）%d（其中 ERP 有流水的 %d 家）:'
          % (len(new - old), sum(1 for c in new - old if erp_amt[c]['2025'] + erp_amt[c]['2026'] > 0)))
    for c in sorted(new - old):
        amt = erp_amt[c]
        if amt['2025'] + amt['2026'] > 0:
            print('      + %-38s 2025 %6.1f → 2026 %6.1f 万' % (c[:36], amt['2025'] / 1e4, amt['2026'] / 1e4))

json.dump({'generated': '2026-10-17',
           'caliber': '客户部门=官方《诊断原料&生命科学客户清单20260930.xls》sheet 归属（客户级单值）；ERP 仅用于流水',
           'source': os.path.basename(SRC_XLS),
           'dk_total': len(dk),
           'dk_with_revenue': sum(1 for x in dk if x['in_erp']),
           'customers': dk},
          io.open(oldp, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print('\n✅ 写入 数据/大客户部_客户清单.json（%d 家，含 %d 家有流水）'
      % (len(dk), sum(1 for x in dk if x['in_erp'])))