#!/usr/bin/env python3 # -*- coding: utf-8 -*- """科研（生命科学销售部）销售金额排名 v3 —— 出全量结果 JSON（供 HTML 交付物用）。 在 v2 基础上补：客户明细 / 主销货号 / 日走势 / 原始记录。 名册 = 人员清单(19) ∪ dept_overrides(陈彩红) 区间 = 2026-08-20 ~ 2026-09-30 ｜ 金额 = 价税合计 ｜ 口径 = 管道同源 """ 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 = BASE + '/月度销售额数据/销售订单_20260101-20260930.xlsx' ORG = BASE + '/销售组织架构/统一工作台运行及授权.xlsx' OVR = ROOT + '/数据/dept_overrides.json' OUT = ROOT + '/销售分析/科研销售_按人与区域_20260820-20260930.json' DEPT, A, B = '生命科学销售部', '2026-08-20', '2026-09-30' ok = True def chk(label, cond, detail=''): global ok print(' %s %-40s %s' % ('✅' if cond else '❌', label, detail)) if not cond: ok = False def big(r): r = str(r) if '浙江' in r: return '浙江大区' if '广州' in r or '广东' in r: return '广州大区' return r or '未标区域' # 名册 org = pd.read_excel(ORG, sheet_name='人员清单') org['_n'] = org['销售员'].astype(str).str.strip() dept = org[org['部门'] == DEPT][['_n', '区域', '角色', '是否在职']].rename(columns={'_n': '销售员'}).copy() ov = json.load(io.open(OVR, encoding='utf-8')) extra = [{'销售员': k, '区域': v.get('region') or '', '角色': '销售工程师', '是否在职': '在职'} for k, v in ov.items() if not k.startswith('_') and isinstance(v, dict) and (v.get('dept') or '').strip() == DEPT] if extra: dept = pd.concat([dept, pd.DataFrame(extra)], ignore_index=True) dept['大区'] = dept['区域'].map(big) dept_map = dept.set_index('销售员').to_dict('index') # 订单 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)].copy() std['_sp'] = std['销售员'].astype(str).str.strip() win = std[(std['_d'] >= A) & (std['_d'] <= B)].copy() sub = win[win['_sp'].isin(set(dept['销售员']))].copy() # 逐人 by = defaultdict(lambda: {'amt': 0.0, 'custs': set(), 'docs': set(), 'rows': 0}) for _, r in sub.iterrows(): d = by[r['_sp']] d['amt'] += float(r['_amt']) d['custs'].add(str(r['客户']).strip()) d['docs'].add(str(r['单据编号'])) d['rows'] += 1 rows = [] for n, p in dept_map.items(): v = by.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['金额'], r['销售员'])) total = round(sum(r['金额'] for r in rows), 4) rk = 0 for r in rows: if r['金额'] > 0: rk += 1 r['名次'] = rk else: r['名次'] = 0 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 g['active'] += 1 if r['金额'] > 0 else 0 for _, r in sub.iterrows(): g = reg[dept_map[r['_sp']]['大区']] g['custs'].add(str(r['客户']).strip()) g['docs'].add(str(r['单据编号'])) regions = sorted([{'大区': 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()], key=lambda r: -r['金额']) # 客户明细 cm = defaultdict(lambda: {'amt': 0.0, 'docs': set(), 'sp': defaultdict(float), '大区': ''}) for _, r in sub.iterrows(): c = str(r['客户']).strip() m = cm[c] m['amt'] += float(r['_amt']) m['docs'].add(str(r['单据编号'])) m['sp'][r['_sp']] += float(r['_amt']) m['大区'] = dept_map[r['_sp']]['大区'] custs = sorted([{'客户': k, '金额': round(v['amt'] / 1e4, 4), '订单数': len(v['docs']), '主跟销售': max(v['sp'].items(), key=lambda x: x[1])[0], '大区': v['大区']} for k, v in cm.items()], key=lambda r: -r['金额']) # 主销货号 sk = defaultdict(lambda: {'amt': 0.0, 'qty': 0.0, 'docs': set(), 'name': ''}) for _, r in sub.iterrows(): code = str(r.get('物料编码') or r.get('货号#') or '').strip() s = sk[code] s['amt'] += float(r['_amt']) s['qty'] += float(pd.to_numeric(r.get('销售数量'), errors='coerce') or 0) s['docs'].add(str(r['单据编号'])) s['name'] = str(r.get('物料名称') or '').strip() skus = sorted([{'货号': k, '物料名称': v['name'], '金额': round(v['amt'] / 1e4, 4), '数量': round(v['qty'], 2), '订单数': len(v['docs'])} for k, v in sk.items()], key=lambda r: -r['金额']) # 日走势 dd = defaultdict(float) for _, r in sub.iterrows(): dd[r['_d']] += float(r['_amt']) daily = sorted([{'日期': k, '金额': round(v / 1e4, 4)} for k, v in dd.items()], key=lambda r: r['日期']) # 原始记录 raw = [{'合同日期': r['_d'], '单据编号': str(r['单据编号']), '客户': str(r['客户']).strip(), '销售员': r['_sp'], '大区': dept_map[r['_sp']]['大区'], '物料编码': str(r.get('物料编码') or '').strip(), '物料名称': str(r.get('物料名称') or '').strip(), '销售数量': float(pd.to_numeric(r.get('销售数量'), errors='coerce') or 0), '价税合计': round(float(r['_amt']), 2)} for _, r in sub.iterrows()] raw.sort(key=lambda r: (r['合同日期'], r['单据编号'])) # 交代项 line = org[org['可下单产品线'].astype(str).str.contains('生命科学', na=False)] outs = [] for _, p in line[line['部门'] != DEPT].iterrows(): s = win[win['_sp'] == p['_n']] if s['_amt'].sum() > 0: outs.append({'销售员': p['_n'], '部门': p['部门'], '区域': p['区域'], '金额': round(s['_amt'].sum() / 1e4, 4), '行数': len(s), '客户样例': '、'.join(sorted({str(c) for c in s['客户']})[:4])[:90]}) allroster = set(org['_n']) | set(ov.keys()) rosterless = [] for n in sorted(set(win['_sp']) - allroster): s = win[win['_sp'] == n] rosterless.append({'销售员': n, '金额': round(s['_amt'].sum() / 1e4, 4), '月份': {k: int(v) for k, v in s['_d'].str[:7].value_counts().sort_index().items()}, '客户样例': '、'.join(sorted({str(c) for c in s['客户']})[:3])[:80]}) print('=== 校验 ===') chk('区间端点 = 2026-09-30', df['_d'].max() == B, df['_d'].max()) ls_unmapped = sorted(set(sub['_sp']) - set(dept['销售员'])) chk('科研部人全部有名册归属（硬门禁）', not ls_unmapped, str(ls_unmapped)) chk('勾稽：Σ逐人 == 明细加总', abs(sum(r['金额'] for r in rows) - sub['_amt'].sum() / 1e4) < 1e-3, '%.4f vs %.4f' % (sum(r['金额'] for r in rows), sub['_amt'].sum() / 1e4)) chk('勾稽：Σ各大区 == 部门合计', abs(sum(r['金额'] for r in regions) - total) < 1e-3, '%.4f vs %.4f' % (sum(r['金额'] for r in regions), total)) chk('勾稽：Σ客户 == 部门合计', abs(sum(r['金额'] for r in custs) - total) < 1e-2) chk('金额非负', all(r['金额'] >= 0 for r in rows)) print(' ⚠️ 名册缺口（区间内有单但不在任何名册，已如实披露）：') for o in rosterless: print(' %s ｜ %.4f 万 ｜ %s' % (o['销售员'], o['金额'], o['月份'])) print(' ⚠️ 未计入本部门但持科研线权限：') for o in outs: print(' %s（%s/%s）｜ %.4f 万 ｜ %s' % (o['销售员'], o['部门'][:12], o['区域'], o['金额'], o['客户样例'])) payload = { 'dept': DEPT, 'start': A, 'end': B, 'total': total, 'generated': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M'), 'src': os.path.basename(ORD), 'src_max_date': df['_d'].max(), 'roster': '统一工作台运行及授权.xlsx/人员清单（19人）+ 数据/dept_overrides.json（陈彩红）', 'caliber': '单据类型=标准销售订单 & 单据状态=已审核 & 是否赠品≠是 & 客户不含(宝锐|宝泰|横琴)；金额=价税合计（含税）；区间按合同日期', 'people': rows, 'regions': regions, 'customers': custs, 'skus': skus, 'daily': daily, 'raw': raw, 'outsiders': outs, 'rosterless': rosterless, 'stats': {'有效行': len(sub), '客户数': len(custs), '订单数': len({r['单据编号'] for r in raw}), '货号数': len(skus), '有金额人数': sum(1 for r in rows if r['金额'] > 0), '编制人数': len(rows)}, } io.open(OUT, 'w', encoding='utf-8').write(json.dumps(payload, ensure_ascii=False, indent=1)) print('\nJSON（%.0f KB）→ %s' % (os.path.getsize(OUT) / 1024.0, OUT)) print('科研合计 %.4f 万 ｜ 有金额 %d 人 ｜ 客户 %d ｜ 订单 %d ｜ 货号 %d' % (total, payload['stats']['有金额人数'], len(custs), payload['stats']['订单数'], len(skus))) sys.exit(0 if ok else 1)