#!/usr/bin/env python3 # -*- coding: utf-8 -*- """科研秋季促销 · 课题组（商机）维度统计 - 浙江：8/20-9/30（有课题组字段） - 广东：9/1-9/30（有课题组字段）+ 10/1-10/16（ERP 无课题组字段，按客户单列） """ 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输出-工作类' PROMO = os.path.join(W, '营销活动/20260820-0930促销季销售统计.xlsx') ERP = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261015.xlsx' OUT = os.path.join(W, '营销活动/科研秋季促销_课题组维度统计_2026.xlsx') 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 def load(path): wb = openpyxl.load_workbook(path, read_only=True, 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)] return head, {h: i for i, h in enumerate(head)}, rows # ---- 1) 促销导出（含课题组）---- head, idx, rows = load(PROMO) orders = {} for r in rows: d = nd(r[idx['合同日期']]) sales = str(r[idx['销售员']] or '').strip() if not d or sales not in PMAP or PMAP[sales].get('region') not in WINDOWS: continue if str(r[idx['单据类型']] or '') == '测试销售订单': continue if str(r[idx['是否赠品']] or '') == '是': continue reg = PMAP[sales]['region'] a, b = WINDOWS[reg] if not (a <= d <= b): 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())) # ---- 2) 广东 10/1-10/16（ERP，无课题组）---- head2, idx2, rows2 = load(ERP) oct_orders = {} for r in rows2: d = nd(r[idx2['合同日期']]) sales = str(r[idx2['销售员']] or '').strip() if not d or sales not in PMAP or PMAP[sales].get('region') != '广东': continue if not (dt.date(2026, 10, 1) <= d <= dt.date(2026, 10, 16)): continue if str(r[idx2['单据类型']] or '') == '测试销售订单': continue if str(r[idx2['是否赠品']] or '') == '是': continue oid = str(r[idx2['单据编号']] or '') try: amt = float(r[idx2['价税合计']] or 0) except Exception: amt = 0.0 o = oct_orders.setdefault(oid, {'date': d, 'sales': sales, 'customer': str(r[idx2['客户']] or '').strip(), 'amount': 0.0}) o['amount'] += amt print('广东10月订单', len(oct_orders), '｜金额 %.2f' % sum(o['amount'] for o in oct_orders.values())) # ---- 3) 按课题组聚合 ---- proj = defaultdict(lambda: {'orders': set(), 'amount': 0.0, 'sales': '', 'region': ''}) 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'] = o['sales']; proj[k]['region'] = o['region'] rows_proj = [] summ = defaultdict(lambda: {'cust': set(), 'proj': 0, 'orders': set(), 'amount': 0.0, 'hit': 0, 'lo': 0, 'hi': 0}) 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, 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, 0 if t == '未达标' else lo, 0 if t == '未达标' else hi]) # 10 月客户级 oct_cust = defaultdict(lambda: {'orders': set(), 'amount': 0.0, 'sales': ''}) for oid, o in oct_orders.items(): c = oct_cust[o['customer']] c['orders'].add(oid); c['amount'] += o['amount']; c['sales'] = o['sales'] rows_oct = [] for cust, v in sorted(oct_cust.items(), key=lambda kv: -kv[1]['amount']): t, lo, hi = tier(v['amount']) rows_oct.append(['广东', v['sales'], cust, len(v['orders']), round(v['amount'], 2), t, 0 if t == '未达标' else lo, 0 if t == '未达标' else hi]) # ---- 4) Excel ---- wb = 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' ws = wb.active; ws.title = '汇总_课题组' style(ws, ['区域', '统计窗口', '客户数', '课题小组数', '订单数', '销售额(元)', '达标课题组', '赠品下限(元)', '赠品上限(元)'], [8, 24, 8, 10, 8, 14, 10, 12, 12]) r = 2 for reg in ('广东', '浙江'): a, b = WINDOWS[reg]; s = summ[reg] win = '%s ~ %s' % (a, b if reg == '浙江' else dt.date(2026, 9, 30)) vals = [reg, win, len(s['cust']), s['proj'], len(s['orders']), round(s['amount'], 2), s['hit'], s['lo'], s['hi']] for i, v in enumerate(vals, 1): ws.cell(row=r, column=i, value=v) r += 1 # 广东 10 月单列 tot = ['广东10月', '2026-10-01 ~ 2026-10-16', len(oct_cust), '—', sum(len(v['orders']) for v in oct_cust.values()), round(sum(v['amount'] for v in oct_cust.values()), 2), '—', 0, 0] for i, v in enumerate(tot, 1): ws.cell(row=r, column=i, value=v).font = Font(italic=True) ws2 = wb.create_sheet('按课题组明细') style(ws2, ['区域', '销售员', '客户', '课题组（商机）', '订单数', '销售额(元)', '达标档位', '赠品下限', '赠品上限'], [8, 9, 30, 26, 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 = wb.create_sheet('按客户汇总') style(ws3, ['区域', '客户', '课题小组数', '订单数', '销售额(元)', '达标档位', '赠品下限', '赠品上限'], [8, 34, 9, 8, 13, 9, 10, 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 = wb.create_sheet('广东10月_客户级') style(ws4, ['区域', '销售员', '客户', '订单数', '销售额(元)', '达标档位', '赠品下限', '赠品上限'], [8, 9, 34, 8, 13, 9, 10, 10]) for i, row in enumerate(rows_oct, 2): for j, v in enumerate(row, 1): ws4.cell(row=i, column=j, value=v) ws5 = wb.create_sheet('口径与说明') notes = [['项目', '说明'], ['维度', '以「商机（课题组）」为统计单元；同一客户多个课题组分别计档'], ['浙江窗口', '2026-08-20 ~ 2026-09-30（促销导出，含商机字段）'], ['广东窗口', '2026-09-01 ~ 2026-10-16；其中 9 月在「按课题组明细」，10 月单列在「广东10月_客户级」'], ['为什么拆开', 'ERP 月度订单导出无「商机/课题组」字段；10 月无法归到课题组，只能到客户级'], ['计入规则', '仅标准销售订单；剔除测试单与赠品行'], ['档位', '1K≥1,000；2K≥2,000；5K≥5,000；1W≥10,000；2W≥20,000；5W≥50,000；10W≥100,000'], ['赠品', '按活动方案 5%–10% 折算，取档位上下限'], ['数据截止', '促销导出 2026-09-30；ERP 2026-10-15（10/16 无导出）']] 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 = 80 wb.save(OUT) json.dump({'proj': rows_proj, 'cust': rows_cust, 'oct': rows_oct, 'summ': {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()}}, open(os.path.join(W, '数据/promo_opp_stats.json'), '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'])) print(' 广东10月 客户 %d ｜订单 %d ｜金额 %.2f' % ( len(oct_cust), sum(len(v['orders']) for v in oct_cust.values()), sum(v['amount'] for v in oct_cust.values())))