# -*- coding: utf-8 -*-
"""Build a self-contained, read-only 2026-10 customer rollup page.
Data sources (local, authorized):
- 数据/customer_rollup_v2.json (2026-11-01 snapshot; projects + activities)
- 数据/orders_detail_2026.json (ERP order detail, latest 2026-11-03/04)
- 数据/opps_by_customer.json (opportunity snapshot, 2026-07-11)
Output:
对外物料/客户拉通大表_2026年10月_内部评审_20261103_V01.html
"""
import json
import os
import re
import html
from collections import Counter, defaultdict
from datetime import datetime
BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类')
OUT_DIR = os.path.join(BASE, '对外物料')
OUT = os.path.join(OUT_DIR, '客户拉通大表_2026年10月_内部评审_20261104_V01.html')
START = '2026-10-01'
END = '2026-10-31'
REGION_RE = re.compile(r'-(广东|浙江|江苏|上海|北京|山东|四川|湖北|湖南|河南|福建|安徽|江西|河北|天津|重庆|陕西|辽宁|吉林|黑龙江|云南|贵州|广西|海南|山西|内蒙古|新疆|西藏|青海|宁夏|甘肃)$')
DEPT_MAP = {
'诊断原料销售拓展部': '销售拓展部',
'诊断原料大客户销售部': '大客户部',
'浙江': '生命科学部·浙江',
'广东': '生命科学部·广东',
}
def norm(s):
s = (s or '').strip()
s = REGION_RE.sub('', s)
s = re.sub(r'\s+', '', s)
return s
def load_json(path):
with open(path, encoding='utf-8') as f:
return json.load(f)
def dept_name(d):
return DEPT_MAP.get(d, d or '—')
def money(v):
try:
return round(float(v or 0), 2)
except Exception:
return 0.0
def top(counter, n=2, sep=' / '):
items = [k for k, v in counter.most_common(n) if k]
return sep.join(items) if items else '—'
def main():
rollup = load_json(os.path.join(BASE, '数据', 'customer_rollup_v2.json'))
orders = load_json(os.path.join(BASE, '数据', 'orders_detail_2026.json'))['orders']
projects = rollup.get('projects') or []
activities = rollup.get('activities') or []
oct_acts = [a for a in activities if START <= (a.get('date') or '') <= END]
oct_orders = [o for o in orders.values() if START <= (o.get('date') or '')[:10] <= END]
# Sales -> department mapping from all historical activities (helps order-only customers).
sales_dept = defaultdict(Counter)
for a in activities:
if a.get('sales') and a.get('dept'):
sales_dept[a['sales']][a['dept']] += 1
customers = defaultdict(lambda: {
'acts': [], 'orders': [], 'projs': [],
'depts': Counter(), 'sales': Counter(), 'scenes': Counter(),
'plines': Counter(), 'opps': set(),
})
for a in oct_acts:
k = norm(a.get('customer'))
c = customers[k]
c['acts'].append(a)
if a.get('dept'):
c['depts'][a['dept']] += 1
if a.get('sales'):
c['sales'][a['sales']] += 1
if a.get('project_scene'):
c['scenes'][a['project_scene']] += 1
if a.get('product_line'):
c['plines'][a['product_line']] += 1
for o in (a.get('opp_names') or []):
if o:
c['opps'].add(o)
for o in oct_orders:
k = norm(o.get('customer'))
c = customers[k]
c['orders'].append(o)
if o.get('sales'):
c['sales'][o['sales']] += 1
for p in projects:
k = norm(p.get('cust'))
c = customers[k]
c['projs'].append(p)
if p.get('dept'):
c['depts'][p['dept']] += 1
if p.get('sales'):
c['sales'][p['sales']] += 1
if p.get('scene'):
c['scenes'][p['scene']] += 1
if p.get('product_line'):
c['plines'][p['product_line']] += 1
if p.get('opp'):
c['opps'].add(p['opp'])
rows = []
for cust, c in customers.items():
if not cust:
continue
# Department: activity/project hint first, then sales->dept fallback.
dept = c['depts'].most_common(1)[0][0] if c['depts'] else ''
if not dept:
for sp, _ in c['sales'].most_common(3):
if sales_dept.get(sp):
dept = sales_dept[sp].most_common(1)[0][0]
break
sales_top = c['sales'].most_common(2)
sales = ' / '.join([x[0] for x in sales_top]) if sales_top else '—'
amount = sum(money(o.get('total')) for o in c['orders'])
std_amount = sum(money(o.get('total')) for o in c['orders'] if o.get('type') == '标准销售订单')
dates = [a.get('date') for a in c['acts'] if a.get('date')]
last_act = max(dates) if dates else ''
order_dates = [(o.get('date') or '')[:10] for o in c['orders'] if o.get('date')]
last_order = max(order_dates) if order_dates else ''
opps = sorted(c['opps'])
rows.append({
'cust': cust,
'dept': dept_name(dept),
'sales': sales,
'opp_count': len(opps),
'opps': opps[:4],
'act_count': len(c['acts']),
'last_act': last_act,
'order_count': len(c['orders']),
'amount': round(amount, 2),
'std_amount': round(std_amount, 2),
'last_order': last_order,
'scene': top(c['scenes'], 2),
'plines': top(c['plines'], 2),
'sales_n': len([x for x in c['sales'].values() if x > 0]),
})
# Stable order: amount desc, activity desc, customer.
rows.sort(key=lambda r: (-r['amount'], -r['act_count'], r['cust']))
# Activity detail records (only fields needed; no contact/phone/address).
act_rows = []
for a in oct_acts:
cust = norm(a.get('customer'))
if not cust:
continue
desc = (a.get('desc') or '').replace('\n', ' ').strip()
nxt = (a.get('next') or '').replace('\n', ' ').strip()
desc = re.sub(r'https?://\S+', '', desc)
nxt = re.sub(r'https?://\S+', '', nxt)
desc = re.sub(r'\s+', ' ', desc).strip()
nxt = re.sub(r'\s+', ' ', nxt).strip()
summary = desc[:220]
if nxt:
summary = (summary + ' ｜ 下一步：' + nxt)[:300]
act_rows.append({
'date': a.get('date') or '',
'cust': cust,
'dept': dept_name(a.get('dept')),
'sales': a.get('sales') or a.get('submitted_by') or '—',
'atype': a.get('activity_type') or '—',
'ctype': a.get('content_type') or '—',
'scene': a.get('project_scene') or '—',
'opps': ' / '.join(a.get('opp_names') or []) or '—',
'summary': summary,
})
act_rows.sort(key=lambda x: (x['date'], x['cust']), reverse=True)
# KPI + distributions.
active_customers = len(rows)
with_act = sum(1 for r in rows if r['act_count'] > 0)
with_order = sum(1 for r in rows if r['order_count'] > 0)
with_opp = sum(1 for r in rows if r['opp_count'] > 0)
total_amount = round(sum(r['amount'] for r in rows), 2)
total_std = round(sum(r['std_amount'] for r in rows), 2)
depts = Counter(r['dept'] for r in rows if r['dept'] and r['dept'] != '—')
sales_set = set()
for r in rows:
for s in [x.strip() for x in r['sales'].split(' / ')]:
if s and s != '—':
sales_set.add(s)
order_types = Counter(o.get('type') or '—' for o in oct_orders)
project_active = sum(1 for p in projects if norm(p.get('cust')) in customers and (customers[norm(p.get('cust'))]['acts'] or customers[norm(p.get('cust'))]['orders']))
payload = {
'meta': {
'month': '2026年10月（自然月）',
'start': START,
'end': END,
'generated': datetime.now().strftime('%Y-%m-%d %H:%M'),
'source_rollup': '数据/customer_rollup_v2.json（生成 2026-11-01 16:55）',
'source_orders': '数据/orders_detail_2026.json（数据截至 2026-11-03）',
'source_opps': '数据/opps_by_customer.json（商机快照 2026-07-11）',
'customer_count': active_customers,
'with_activity': with_act,
'with_order': with_order,
'with_opp': with_opp,
'activity_count': len(act_rows),
'order_count': len(oct_orders),
'amount': total_amount,
'std_amount': total_std,
'project_count': len(projects),
'project_active': project_active,
'sales_count': len(sales_set),
'dept_count': len(depts),
'order_types': dict(order_types),
},
'customers': rows,
'activities': act_rows,
}
# HTML uses a real embedded JSON snapshot; zero external requests.
data_js = json.dumps(payload, ensure_ascii=False, separators=(',', ':'))
html_doc = r'''
客户拉通大表 · 2026年10月 · 内部评审