#!/usr/bin/env python3 # -*- coding: utf-8 -*- """科研秋季促销 · 课题组（商机）维度统计 数据源：科研促销统计-20260820-1030.xlsx（含商机字段） 广东 9/1-10/16；浙江 8/20-9/30 """ import datetime as dt import json import os 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输出-工作类' SRC = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_6c4f26972d48_科研促销统计-20260820-1030.xlsx' OUT = os.path.join(W, '营销活动/科研秋季促销_课题组维度统计_2026.xlsx') JSON_OUT = os.path.join(W, '数据/promo_opp_stats.json') people = json.load(open(os.path.join(W, '数据/目标基础数据_2026.json'), encoding='utf-8'))['people'] PMAP = {p['name']: p for p in people if '生命科学' in p.get('dept', '')} WINDOWS = {'广东': (dt.date(2026, 9, 1), dt.date(2026, 10, 16)), '浙江': (dt.date(2026, 8, 20), dt.date(2026, 9, 30))} TIERS = [(100000, '10W', 5000, 10000), (50000, '5W', 2500, 5000), (20000, '2W', 1000, 2000), (10000, '1W', 500, 1000), (5000, '5K', 250, 500), (2000, '2K', 100, 200), (1000, '1K', 50, 100)] def tier(a): for th, n, lo, hi in TIERS: if a >= th: return n, lo, hi return '未达标', 0, 0 def nd(v): if isinstance(v, dt.datetime): return v.date() if isinstance(v, dt.date): return v try: return dt.datetime.strptime(str(v)[:10], '%Y-%m-%d').date() except Exception: return None wb = openpyxl.load_workbook(SRC, data_only=True) ws = wb.active rows = ws.iter_rows(values_only=True) head = [str(c).strip() if c is not None else '' for c in next(rows)] idx = {h: i for i, h in enumerate(head)} print('源表列数', len(head), '｜含商机', '商机(飞书)#' in idx) orders = {} skipped = defaultdict(int) for r in rows: d = nd(r[idx['合同日期']]) sales = str(r[idx['销售员']] or '').strip() if not d or sales not in PMAP: skipped['非生命科学/无日期'] += 1 continue reg = PMAP[sales].get('region') if reg not in WINDOWS: skipped['区域不在范围'] += 1 continue a, b = WINDOWS[reg] if not (a <= d <= b): skipped['窗口外'] += 1 continue if str(r[idx['单据类型']] or '') == '测试销售订单': skipped['测试单'] += 1 continue if str(r[idx['是否赠品']] or '') == '是': skipped['赠品'] += 1 continue if str(r[idx.get('作废状态', 25)] or '') == '已作废': skipped['已作废'] += 1 continue oid = str(r[idx['单据编号']] or '') try: amt = float(r[idx['价税合计']] or 0) except Exception: amt = 0.0 o = orders.setdefault(oid, {'region': reg, 'date': d, 'sales': sales, 'customer': str(r[idx['客户']] or '').strip(), 'project': str(r[idx['商机(飞书)#']] or '').strip() or '未挂课题组', 'amount': 0.0}) o['amount'] += amt print('有效订单', len(orders), '｜金额 %.2f' % sum(o['amount'] for o in orders.values())) print('跳过:', dict(skipped)) proj = defaultdict(lambda: {'orders': set(), 'amount': 0.0, 'sales': set()}) for oid, o in orders.items(): k = (o['region'], o['customer'], o['project']) proj[k]['orders'].add(oid) proj[k]['amount'] += o['amount'] proj[k]['sales'].add(o['sales']) rows_proj = [] summ = defaultdict(lambda: {'cust': set(), 'proj': 0, 'orders': set(), 'amount': 0.0, 'hit': 0, 'lo': 0, 'hi': 0, 'dates': []}) for (reg, cust, pj), v in sorted(proj.items(), key=lambda kv: -kv[1]['amount']): t, lo, hi = tier(v['amount']) rows_proj.append([reg, '、'.join(sorted(v['sales'])), cust, pj, len(v['orders']), round(v['amount'], 2), t, 0 if t == '未达标' else lo, 0 if t == '未达标' else hi]) s = summ[reg] s['cust'].add(cust); s['proj'] += 1; s['orders'] |= v['orders']; s['amount'] += v['amount'] if t != '未达标': s['hit'] += 1; s['lo'] += lo; s['hi'] += hi cust_roll = defaultdict(lambda: {'proj': set(), 'orders': set(), 'amount': 0.0}) for (reg, cust, pj), v in proj.items(): c = cust_roll[(reg, cust)] c['proj'].add(pj); c['orders'] |= v['orders']; c['amount'] += v['amount'] rows_cust = [] for (reg, cust), v in sorted(cust_roll.items(), key=lambda kv: -kv[1]['amount']): t, lo, hi = tier(v['amount']) rows_cust.append([reg, cust, len(v['proj']), len(v['orders']), round(v['amount'], 2), t]) by_sales = defaultdict(lambda: {'cust': set(), 'proj': set(), 'orders': set(), 'amount': 0.0}) for (reg, cust, pj), v in proj.items(): for sp in v['sales']: s = by_sales[(reg, sp)] s['cust'].add(cust); s['proj'].add(pj); s['orders'] |= v['orders']; s['amount'] += v['amount'] rows_sales = [] for (reg, sp), v in by_sales.items(): t, lo, hi = tier(v['amount']) rows_sales.append([reg, sp, len(v['cust']), len(v['proj']), len(v['orders']), round(v['amount'], 2), t]) rows_sales.sort(key=lambda x: (x[0], -x[5])) wb2 = openpyxl.Workbook() HF = PatternFill('solid', fgColor='1F4E79'); HFONT = Font(color='FFFFFF', bold=True, size=10) def style(ws_, headers, widths): for i, h in enumerate(headers, 1): c = ws_.cell(row=1, column=i, value=h) c.fill = HF; c.font = HFONT c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) ws_.column_dimensions[get_column_letter(i)].width = widths[i - 1] ws_.freeze_panes = 'A2' ws1 = wb2.active; ws1.title = '汇总_分区域' style(ws1, ['区域', '统计窗口', '客户数', '课题组数', '订单数', '销售额(元)', '达档课题组', '赠品下限(元)', '赠品上限(元)'], [8, 24, 8, 9, 8, 14, 10, 12, 12]) r = 2 for reg in ('广东', '浙江'): a, b = WINDOWS[reg]; s = summ[reg] for i, v in enumerate([reg, '%s ~ %s' % (a, b), len(s['cust']), s['proj'], len(s['orders']), round(s['amount'], 2), s['hit'], s['lo'], s['hi']], 1): ws1.cell(row=r, column=i, value=v) r += 1 for i, v in enumerate(['合计', '—', len(summ['广东']['cust'] | summ['浙江']['cust']), summ['广东']['proj'] + summ['浙江']['proj'], len(summ['广东']['orders'] | summ['浙江']['orders']), round(summ['广东']['amount'] + summ['浙江']['amount'], 2), summ['广东']['hit'] + summ['浙江']['hit'], summ['广东']['lo'] + summ['浙江']['lo'], summ['广东']['hi'] + summ['浙江']['hi']], 1): c = ws1.cell(row=r, column=i, value=v); c.font = Font(bold=True) ws2 = wb2.create_sheet('按课题组明细') style(ws2, ['区域', '销售员', '客户', '课题组（商机）', '订单数', '采购额(元)', '达标档位', '赠品下限', '赠品上限'], [8, 12, 30, 24, 7, 13, 9, 10, 10]) for i, row in enumerate(rows_proj, 2): for j, v in enumerate(row, 1): ws2.cell(row=i, column=j, value=v) ws3 = wb2.create_sheet('按客户汇总') style(ws3, ['区域', '客户', '课题组数', '订单数', '采购额(元)', '合并档位'], [8, 34, 9, 8, 13, 10]) for i, row in enumerate(rows_cust, 2): for j, v in enumerate(row, 1): ws3.cell(row=i, column=j, value=v) ws4 = wb2.create_sheet('按销售员') style(ws4, ['区域', '销售员', '客户数', '课题组数', '订单数', '采购额(元)', '合并档位'], [8, 10, 8, 9, 8, 13, 10]) for i, row in enumerate(rows_sales, 2): for j, v in enumerate(row, 1): ws4.cell(row=i, column=j, value=v) ws5 = wb2.create_sheet('口径与说明') notes = [['项目', '说明'], ['数据源', '科研促销统计-20260820-1030.xlsx（含「商机(飞书)#」字段）'], ['广东窗口', '2026-09-01 ~ 2026-10-16（含首尾）'], ['浙江窗口', '2026-08-20 ~ 2026-09-30（含首尾）'], ['统计单元', '课题组（飞书商机）；同一客户多个课题组分别计档'], ['计入规则', '仅标准销售订单；剔除测试销售订单、赠品行、已作废'], ['金额口径', 'ERP 价税合计（含税），按订单去重后汇总'], ['档位', '1K≥1,000；2K≥2,000；5K≥5,000；1W≥10,000；2W≥20,000；5W≥50,000；10W≥100,000'], ['赠品', '按活动方案 5%–10% 折算，取档位上下限'], ['区域归属', '按订单销售员所属区域（目标基础数据_2026），不按客户注册地']] for i, row in enumerate(notes, 1): for j, v in enumerate(row, 1): c = ws5.cell(row=i, column=j, value=v) if i == 1: c.fill = HF; c.font = HFONT ws5.column_dimensions['A'].width = 14 ws5.column_dimensions['B'].width = 84 wb2.save(OUT) json.dump({'windows': {k: [str(v[0]), str(v[1])] for k, v in WINDOWS.items()}, 'summary': {k: {'cust': len(v['cust']), 'proj': v['proj'], 'orders': len(v['orders']), 'amount': round(v['amount'], 2), 'hit': v['hit'], 'lo': v['lo'], 'hi': v['hi']} for k, v in summ.items()}, 'proj': rows_proj, 'cust': rows_cust, 'sales': rows_sales}, open(JSON_OUT, 'w'), ensure_ascii=False, indent=1) print('✅', OUT) for reg in ('广东', '浙江'): s = summ[reg] print(' %s：课题组 %d ｜客户 %d ｜订单 %d ｜金额 %.2f ｜达标 %d ｜赠品 %d–%d' % ( reg, s['proj'], len(s['cust']), len(s['orders']), s['amount'], s['hit'], s['lo'], s['hi']))