#!/usr/bin/env python3 # -*- coding: utf-8 -*- """大客户部 待转产 TOP10（研发承接货号 → 试剂生产部） 与 2026-10-21 全公司版的差别：只统计「大客户部」归属的订单（按 erp_sales_core.map_person 的人员→部门映射，含黄明月/张立娅离职后归刘子研的口径），其余口径、评分、输出格式保持一致。 评分（0–100，越大越急）：规模 50% + 复购客户 20% + 客户数 15% + 有销月份 15%（百分位）； 研发占比仅作门槛（≥50%），不计分。 门槛：大客户部 2026 销售额 ≥ 3 万元；研发两部门（应用研究部/产品部）金额占比 ≥ 50%。 """ import datetime as dt import json import os import sys from collections import defaultdict import openpyxl from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' sys.path.insert(0, os.path.join(W, '脚本')) import erp_sales_core as core OUTDIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) P = core.find_latest_erp() RD = ('分子试剂应用研究部', '分子试剂产品部') EXCL = ('宝锐', '宝泰', '横琴') MIN_AMT = 30000 RECENT_FROM = '2026-08' person_dept, lzy_custs = core.load_mappings() sku = defaultdict(lambda: {'amt': 0.0, 'orders': set(), 'cust_orders': defaultdict(set), 'cust_amt': defaultdict(float), 'dept': defaultdict(float), 'months': set(), 'recent': 0.0, 'names': set(), 'last': None, 'sales': defaultdict(float)}) total_da = 0.0 wb = openpyxl.load_workbook(P, read_only=True, data_only=True) ws = wb.active rows = ws.iter_rows(values_only=True) H = {h: i for i, h in enumerate([str(x).strip() for x in next(rows)])} for r in rows: if str(r[H['单据类型']] or '') != '标准销售订单' or str(r[H['单据状态']] or '') != '已审核': continue if str(r[H['是否赠品']] or '') == '是': continue cust = str(r[H['客户']] or '') if any(k in cust for k in EXCL): continue sp = str(r[H['销售员']] or '') pt = str(r[H.get('产品类型#', 0)] or '') person, dept = core.map_person(sp, cust, person_dept, lzy_custs, pt) if dept != '大客户部': continue code = str(r[H['货号#']] or '').strip() if not code: continue try: a = float(r[H['价税合计']] or 0) except Exception: a = 0.0 d = r[H['合同日期']] if isinstance(d, dt.datetime): d = d.date() elif not isinstance(d, dt.date): try: d = dt.datetime.strptime(str(d)[:10], '%Y-%m-%d').date() except Exception: d = None mon = d.strftime('%Y-%m') if d else '' x = sku[code] x['amt'] += a x['orders'].add(str(r[H['单据编号']] or '')) x['cust_orders'][cust].add(str(r[H['单据编号']] or '')) x['cust_amt'][cust] += a x['dept'][str(r[H['备货部门']] or '未填')] += a x['names'].add(str(r[H['物料名称']] or '').strip()) x['sales'][person or sp] += a if mon: x['months'].add(mon) x['last'] = d if x['last'] is None or d > x['last'] else x['last'] if mon >= RECENT_FROM: x['recent'] += a total_da += a cands = [] for code, x in sku.items(): if x['amt'] < MIN_AMT: continue rd_amt = sum(v for k, v in x['dept'].items() if k in RD) share = rd_amt / x['amt'] if x['amt'] else 0 if share < 0.5: continue rep = sum(1 for v in x['cust_orders'].values() if len(v) >= 2) cust_amt = sorted(x['cust_amt'].items(), key=lambda kv: -kv[1]) cands.append({'code': code, 'name': sorted(x['names'])[0][:52] if x['names'] else '', 'amt': x['amt'], 'share': share, 'orders': len(x['orders']), 'custs': len(x['cust_orders']), 'rep': rep, 'months': len(x['months']), 'recent': x['recent'], 'last': str(x['last'] or ''), 'dept': dict(x['dept']), 'top_cust': cust_amt[:3], 'top1_share': (cust_amt[0][1] / x['amt']) if cust_amt and x['amt'] else 0, 'sales': sorted(x['sales'].items(), key=lambda kv: -kv[1])[:2]}) def prank(vals): order = sorted(range(len(vals)), key=lambda i: vals[i]) rank = [0] * len(vals) for pos, i in enumerate(order): rank[i] = pos / (len(vals) - 1) * 100 if len(vals) > 1 else 100 return rank p_amt, p_rep = prank([c['amt'] for c in cands]), prank([c['rep'] for c in cands]) p_cus, p_mon = prank([c['custs'] for c in cands]), prank([c['months'] for c in cands]) for i, c in enumerate(cands): c['score'] = round(0.50 * p_amt[i] + 0.20 * p_rep[i] + 0.15 * p_cus[i] + 0.15 * p_mon[i], 1) cands.sort(key=lambda c: -c['score']) top = cands[:10] def action(c): if c['custs'] >= 3 and c['rep'] >= 2 and c['months'] >= 4: return '立即转产（多客户+复购+持续出货）' if c['rep'] >= 1 and c['months'] >= 3: return '试产验证后转产（已有复购，先做批次一致性）' if c['months'] >= 2: return '列入转产计划（连续出货，复购待观察）' return '观察（一次性/单客户，暂不转）' for c in cands: c['action'] = action(c) wb2 = openpyxl.Workbook() HF = PatternFill('solid', fgColor='1F4E79') HFONT = Font(color='FFFFFF', bold=True, size=10) def style(ws, heads, widths, data, money=(), pct=()): for i, h in enumerate(heads, 1): cell = ws.cell(row=1, column=i, value=h) cell.fill = HF cell.font = HFONT cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) ws.column_dimensions[get_column_letter(i)].width = widths[i - 1] for ri, row in enumerate(data, 2): for ci, v in enumerate(row, 1): cell = ws.cell(row=ri, column=ci, value=v) if ci in money: cell.number_format = '#,##0.00' if ci in pct: cell.number_format = '0.0"%"' ws.freeze_panes = 'A2' ws1 = wb2.active ws1.title = '大客户部TOP10' d1 = [] for i, c in enumerate(top, 1): d1.append([i, c['code'], c['name'], round(c['amt'], 2), c['score'], round(c['share'] * 100, 1), c['custs'], c['rep'], c['orders'], c['months'], round(c['recent'], 2), c['last'], '、'.join(k[:12] for k, _ in c['top_cust']), round(c['top1_share'] * 100, 1), '、'.join(k for k, _ in c['sales']), c['action']]) style(ws1, ['排名', '货号', '品名', '大客户部2026销售额(元)', '转产紧迫度', '研发部门占比%', '客户数', '复购客户数', '订单数', '有销月份', '近3月销售额(元)', '最近成交', '主要客户', '第一大客户占比%', '负责销售', '建议动作'], [6, 22, 42, 18, 11, 12, 8, 10, 8, 9, 15, 11, 32, 13, 14, 34], d1, money=(4, 11), pct=(6, 14)) ws2 = wb2.create_sheet('候选取数明细') d2 = [] for i, c in enumerate(cands, 1): d2.append([i, c['code'], c['name'], round(c['amt'], 2), c['score'], round(c['share'] * 100, 1), c['custs'], c['rep'], c['orders'], c['months'], round(c['recent'], 2), c['dept'].get('分子试剂应用研究部', 0), c['dept'].get('分子试剂产品部', 0), c['dept'].get('试剂生产部', 0), '、'.join(k for k, _ in c['sales']), c['action']]) style(ws2, ['排名', '货号', '品名', '大客户部销售额(元)', '紧迫度', '研发占比%', '客户数', '复购客户数', '订单数', '有销月份', '近3月销售额', '应用研究部金额', '产品部金额', '已转生产部金额', '负责销售', '建议'], [6, 22, 40, 16, 9, 10, 8, 10, 8, 9, 14, 15, 14, 15, 14, 32], d2, money=(4, 11, 12, 13, 14), pct=(6,)) ws3 = wb2.create_sheet('口径与评分') notes = [ ['项目', '说明'], ['数据源', os.path.basename(P) + '（截至 2026-10-22）'], ['部门口径', '按 erp_sales_core.map_person 的销售员→部门映射；黄明月/张立娅离职后业绩归刘子研（大客户部）；刘新元代管客户中属刘子研的归大客户部'], ['金额口径', '标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴；金额=价税合计'], ['候选范围', '仅大客户部订单：备货部门为「分子试剂应用研究部 / 分子试剂产品部」的金额占比 ≥50%，且大客户部 2026 销售额 ≥3 万元'], ['复购客户数', '同一客户对该货号有 ≥2 张有效订单'], ['有销月份', '2026 年内该货号在大客户部出现销售的月份数'], ['近3月', '2026-08-01 ~ 2026-10-22'], ['评分权重', '规模 50% + 复购客户 20% + 客户数 15% + 有销月份 15%（各因子百分位后加权）；研发占比仅作门槛'], ['候选数', '%d 个货号，合计 %.2f 万元' % (len(cands), sum(c['amt'] for c in cands) / 1e4)], ['与全公司版的差别', '本表只含大客户部订单，同一货号若其他部门也在卖，金额与客户数会小于全公司口径，不可直接对比排名'], ['注意', '品名泛化（如 A3）或仅 1–2 个有销月份的货号，转产前需先核实 BOM/项目属性'], ] for i, row in enumerate(notes, 1): for j, v in enumerate(row, 1): cell = ws3.cell(row=i, column=j, value=v) if i == 1: cell.fill = HF cell.font = HFONT ws3.column_dimensions['A'].width = 18 ws3.column_dimensions['B'].width = 110 xlsx_path = os.path.join(OUTDIR, '转产优先级_大客户部TOP10_2026-10-23.xlsx') wb2.save(xlsx_path) json.dump({'dept': '大客户部', 'source': os.path.basename(P), 'total_da': round(total_da, 2), 'cands': [{k: v for k, v in c.items()} for c in cands[:60]]}, open(os.path.join(OUTDIR, '数据', 'transfer_top_da.json'), 'w'), ensure_ascii=False, indent=1, default=str) print('源', os.path.basename(P)) print('大客户部 2026 合计 %.2f 万｜候选 %d 个｜合计 %.2f 万' % ( total_da / 1e4, len(cands), sum(c['amt'] for c in cands) / 1e4)) for i, c in enumerate(top, 1): print('%2d %-24s %10.0f 分%5.1f 客户%d 复购%d 月%d 首客占比%.0f%% %s' % ( i, c['code'], c['amt'], c['score'], c['custs'], c['rep'], c['months'], c['top1_share'] * 100, c['action'][:12])) print('✅', xlsx_path)