#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Q3 报告自报数 × ERP 实际 逐人对账。

ERP 口径（工作台既有口径，可复现）：
  · 源：销售订单_20260101-20260930.xlsx · 期间 2026-01-01~09-30
  · 过滤：单据类型=标准销售订单 · 单据状态=已审核 · 非赠品 · 排除关联方（宝锐/宝泰/横琴）
  · 金额：价税合计（含税）；离职者业绩按工作台规则并入区域经理/大客户1岗
输出：数据/2026Q3_ERP对账.json
"""
import collections
import json
import os

import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
ERP = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20260930.xlsx')
WB = os.path.join(ROOT, '数据', '三部门逐月_2026.json')
SUMMARY = os.path.join(ROOT, '数据', '2026Q3汇报_汇总.json')
OUT = os.path.join(ROOT, '数据', '2026Q3_ERP对账.json')

# 离职归并（与工作台一致）：大客户1岗 → 刘子研；其余按离职前区域 → 区域经理
MERGE = {'黄明月': '刘子研', '刘新元': '刘子研', '张立娅': '刘子研',
         '黄建佳': '黄文才', '黄自鑫': '黄文才', '龙友波': '吴云', '刘静文': '吴云',
         '梁莹莹': '吴云', '赵以诺': '吴云', '曾琦': '吴云', '曾琪': '吴云', '李岚风': '吴云',
         '王慧洁': '吴云', '刘斌': '吴云', '陈彩红': '吴云', '肖立': '吴云', '段玉': '韩远怀'}


NAMES = ['陈溪子', '杨天雄', '路要峰', '黄文才', '林潇', '李江辉', '张文蝶', '宋学伟', '冯贤平',
         '刘虹晶', '刘子研', '赵云浩', '徐卓凡', '黄建华', '王诗香']


def person(s):
    for n in NAMES:
        if n in str(s):
            return n
    return str(s)[:12]


def main():
    # ① 报告侧：商机表自报季度实绩（按汇报人）
    sm = json.load(open(SUMMARY, encoding='utf-8'))
    opps = sm['tables']['商机明细']
    rep, rep_cnt = collections.Counter(), collections.Counter()
    def _f(x):
        try:
            return float(str(x).replace(',', '').replace('￥', '').strip())
        except Exception:
            return 0.0

    # 实绩列名在归一表里是 '1季度\n实绩' 这类（也有 q1a 别名），两种都认
    for r in opps:
        keys = [k for k in r if ('季度' in str(k) and '实绩' in str(k)) or str(k) in ('q1a', 'q2a', 'q3a', 'q4a')]
        v = sum(_f(r[k]) for k in keys)
        who = person(r.get('销售') or r.get('_汇报人') or '—')
        if v > 0:
            rep[who] += v
            rep_cnt[who] += 1
    rep_w = {k: round(v / 10000.0, 2) for k, v in rep.items()}

    # ② ERP 侧：工作台口径逐人（含离职归并）
    wb = json.load(open(WB, encoding='utf-8'))
    dcnt = collections.defaultdict(collections.Counter)
    wb_amt = collections.defaultdict(float)
    for r in wb['salespersons']:
        mo = str(r.get('month') or '')
        if mo.startswith('2026-') and mo <= '2026-09':
            dcnt[r['sp']][r['dept']] += 1
            wb_amt[r['sp']] += float(r.get('amt') or 0)
    dept_map = {sp: c.most_common(1)[0][0] for sp, c in dcnt.items()}

    # 部门名归一 + 本轮报送名单覆盖（ERP 无部门列，工作台归属存在历史遗留：
    # 例：吴云在工作台=生命科学销售部、在目标表=大客户部、在逐月表=销售拓展部）
    LS_Q3 = {'吴云', '韩远怀', '黄建华', '王诗香', '陈彩红', '叶信福', '侯海瑜', '袁殿果'}

    def _norm_dept(sp, d):
        if sp in LS_Q3:
            return '生命科学部'
        if '大客户' in str(d) or sp in ('刘子研', '赵云浩'):
            return '大客户部'
        if '生命科学' in str(d):
            return '生命科学部'
        return '销售拓展部'
    dept_map = {sp: _norm_dept(sp, d) for sp, d in dept_map.items()}
    wb_dept_tot = collections.defaultdict(float)
    for sp, v in wb_amt.items():
        wb_dept_tot[dept_map[sp]] += v

    # ③ ERP 直算：本人自身业绩（客户数/单据数），与归并量拆开呈现
    e = pd.read_excel(ERP, sheet_name='Sheet1')
    e.columns = [str(c).strip() for c in e.columns]
    e['_d'] = pd.to_datetime(e['合同日期'], errors='coerce')
    e['_amt'] = pd.to_numeric(e['价税合计'], errors='coerce').fillna(0)
    e['_cust'] = e['客户'].astype(str).str.strip()
    e['_sp'] = e['销售员'].astype(str).str.strip()
    m = (e['单据类型'].astype(str).str.contains('标准销售订单', na=False)
         & e['单据状态'].astype(str).str.contains('已审核', na=False)
         & ~e['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true'])
         & ~e['_cust'].str.contains('宝锐|宝泰|横琴', na=False, regex=True)
         & e['_d'].dt.year.eq(2026) & e['_d'].dt.month.le(9))
    s = e[m].copy()
    own = s.groupby('_sp').agg(amt=('_amt', 'sum'), rows=('_amt', 'size'), custs=('_cust', 'nunique'))
    own_w = (own['amt'] / 10000.0).round(2)

    # ④ 组装逐人对照（含双方所有名字）
    names = sorted(set(list(wb_amt) + list(rep_w) + list(own_w)),
                   key=lambda x: -max(wb_amt.get(x, 0), rep_w.get(x, 0), own_w.get(x, 0)))
    rows = []
    for sp in names:
        wbv = round(wb_amt.get(sp, 0.0), 2)
        ownv = float(own_w.get(sp, 0.0))
        rv = rep_w.get(sp)
        dept = dept_map.get(sp) or ('大客户部' if sp in ('刘子研', '赵云浩') else
                                    ('' if sp in MERGE else '销售拓展部'))
        if sp in MERGE and sp not in wb_amt:
            dept = '（离职·已归并）'
        rows.append({'人': sp, '部门': dept, '报告实绩_万': rv, '报告商机条数': rep_cnt.get(sp, 0),
                     'ERP实绩_万': wbv or None, 'ERP归属': '工作台归并口径',
                     '本人直算_万': ownv, '归并量_万': round(wbv - ownv, 2) if wbv else None,
                     'ERP客户数': int(own.loc[sp, 'custs']) if sp in own.index else 0,
                     'ERP单据数': int(own.loc[sp, 'rows']) if sp in own.index else 0,
                     '差额_万': round((rv or 0) - wbv, 2) if (rv is not None or wbv) else None})

    two = round(wb_dept_tot.get('大客户部', 0) + wb_dept_tot.get('销售拓展部', 0), 2)
    report_total = round(sum(rep_w.values()), 2)
    res = {
        'meta': {'报告源': '各 PPT 商机明细表「季度实绩」列汇总（%d 人已报送）' % len(rep_w),
                 'ERP源': os.path.basename(ERP) + '（经工作台管道）',
                 '期间': '2026-01-01 ~ 2026-09-30',
                 'ERP口径': '标准销售订单·已审核·非赠品·排除关联方（宝锐/宝泰/横琴）；金额=价税合计（含税）',
                 '归并规则': '离职者业绩并入区域经理/大客户1岗（大客户1岗→刘子研）',
                 '说明': '报告数=销售在商机表里自报的季度实绩，仅覆盖已挂商机的成交；ERP=系统实际过账金额'},
        'kpi': {'ERP两部门_万': two, 'ERP大客户部_万': round(wb_dept_tot.get('大客户部', 0), 2),
                'ERP拓展部_万': round(wb_dept_tot.get('销售拓展部', 0), 2),
                'ERP生命科学部_万': round(wb_dept_tot.get('生命科学部', 0), 2),
                '报告汇总_万': report_total,
                '差额_万': round(two - report_total, 2),
                '报告覆盖率_%': round(report_total / two * 100, 1) if two else None},
        'rows': rows,
    }
    json.dump(res, open(OUT, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    print('ERP 两部门 %.2f 万 ｜ 报告汇总 %.2f 万 ｜ 缺口 %.2f 万 ｜ 报告覆盖率 %.1f%%' % (
        two, report_total, two - report_total, report_total / two * 100))
    print('生命科学部（参考）%.2f 万' % wb_dept_tot.get('生命科学部', 0))
    print()
    print('%-8s %-10s %10s %10s %10s %8s' % ('人', '部门', '报告万', 'ERP万', '差额万', '单据'))
    for r in rows[:20]:
        print('%-8s %-10s %10s %10s %10s %8d' % (
            r['人'], r['部门'], r['报告实绩_万'] if r['报告实绩_万'] is not None else '—',
            r['ERP实绩_万'] if r['ERP实绩_万'] is not None else '—',
            r['差额_万'] if r['差额_万'] is not None else '—', r['ERP单据数']))
    print('\n→ %s' % OUT)


if __name__ == '__main__':
    main()