#!/usr/bin/env python3 # -*- coding: utf-8 -*- """2027 战略研讨：需求 → 对应客户（3–5 家，大客户部优先） 数据源 1) 飞书活动近半年：activities_q2q3_2026.json(4–7 月) + activities_feishu.json(7–9 月) + daily_activities_mcp.json(7–10 月)，去重后按 2026-04-17 ~ 2026-10-17 过滤 2) 大客户部 Q3 汇报：刘子研 / 赵云浩 pptx（逐客户说明） 3) 官方客户清单：数据/客户清单_官方_20260930.json（用于标注大客户部/部门） 输出：数据/2027战略_需求客户映射.json """ import io import json import re import zipfile from collections import defaultdict W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' D = W + '/数据/' B = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2026年3季度汇报/大客户部/' LO, HI = '2026-04-17', '2026-10-17' # ── 需求类别与关键词（中文；命中即计一次）────────────────────────────── NEEDS = [ ('极速 / 直扩', ['直扩', '免提取', '直接扩增', '极速', '快速扩增', '15分钟', '10分钟', '快速PCR']), ('全组分冻干', ['冻干球', '冻干粉', '冻干珠', '冻干保护剂', '冻干项目', '冻干试剂', '冻干体系', '复溶']), ('通用型单管', ['通用型', '单管', '一管', '通用体系', '多项目兼容']), ('低核酸残留', ['核酸残留', '残留', '起峰', 'NTC', '气溶胶', '宿主DNA', 'gDNA', '防污染', '背景核酸']), ('常温稳定', ['常温稳定', '常温保存', '常温', '室温', '37℃', '37度', '冷链']), ('冻干免湿度', ['免湿度', '控湿', '低湿', '分装环境', '湿度']), ('miRNA / 对标', ['miRNA', 'microRNA']), ('交期 / 供应', ['交期', '货期', '急单', '断货', '备货', '工作日']), ('新平台（三代/单细胞）', ['三代测序', '单细胞', '长读长', '纳米孔', 'Nanopore']), ] # ── 官方客户清单 ────────────────────────────────────────────────── official = json.load(io.open(D + '客户清单_官方_20260930.json', encoding='utf-8'))['customers'] OFF = {} for x in official: OFF.setdefault(x['cust'], x) DK = {x['cust'] for x in official if x['dept'] == '大客户部'} NAMES = sorted(OFF, key=len, reverse=True) def match_cust(raw): """把活动里的客户串对齐到官方客户名（最长包含匹配）""" if not raw: return '' s = str(raw).strip() s = re.sub(r'[-－]\s*(广东|浙江|上海|北京|江苏|华南|华北|华中|华西|福建|山东|四川|湖南|湖北|河南)$', '', s) if s in OFF: return s for n in NAMES: if n in s or (len(s) >= 6 and s in n): return n return s # ── 活动：三源合并去重 ──────────────────────────────────────────── acts = [] def push(date, cust, sales, text, src): if not date or not text: return d = str(date)[:10] if not (LO <= d <= HI): return sp = '' if isinstance(sales, dict): sp = sales.get('name') or sales.get('label') or sales.get('email') or '' elif sales: sp = str(sales) sp = re.sub(r"^\{.*?'(?:name|label)':\s*'([^']+)'.*$", r'\1', sp) acts.append({'d': d, 'c': match_cust(cust), 'sp': sp, 't': str(text), 'src': src}) d1 = json.load(io.open(D + 'activities_q2q3_2026.json', encoding='utf-8'))['activities'] for x in d1: push(x.get('created'), x.get('name'), x.get('created_by_name'), x.get('description') or x.get('name'), 'Q2Q3') d2 = json.load(io.open(D + 'activities_feishu.json', encoding='utf-8'))['items'] for x in d2: push(x.get('date'), x.get('customer'), x.get('sales'), x.get('text'), 'FS') d3 = json.load(io.open(D + 'daily_activities_mcp.json', encoding='utf-8'))['items'] for x in d3: cu = x.get('field_5f20fc') if isinstance(cu, str): m = re.search(r"'label':\s*'([^']+)'", cu) cu = m.group(1) if m else cu elif isinstance(cu, dict): cu = cu.get('label', '') sp = x.get('owner') if isinstance(sp, dict): sp = sp.get('name') or sp.get('label') or '' elif isinstance(sp, str): m = re.search(r"'name':\s*'([^']+)'", sp) sp = m.group(1) if m else sp push(x.get('start_time'), cu, sp, x.get('field_b99055'), 'MCP') # 去重 seen, uniq = set(), [] for a in acts: k = (a['d'], a['c'], a['t'][:80]) if k in seen: continue seen.add(k) uniq.append(a) acts = uniq print('█ 近半年活动去重后 %d 条 ｜ 涉及客户 %d 家' % (len(acts), len({a['c'] for a in acts}))) mon = defaultdict(int) for a in acts: mon[a['d'][:7]] += 1 print(' 月度:', dict(sorted(mon.items()))) # ── 大客户部 Q3 汇报：逐客户说明 ────────────────────────────────── deck_txt = '' for f in ('1-大客户部-2026年三季度销售工作汇报-刘子研.pptx', '2-大客户部三季度汇报-赵云浩-V01.pptx'): z = zipfile.ZipFile(B + f) for n in sorted([n for n in z.namelist() if re.match(r'ppt/slides/slide\d+\.xml$', n)], key=lambda s: int(re.search(r'\d+', s).group())): x = z.read(n).decode('utf-8', 'ignore') x = re.sub(r'', '\n', x) deck_txt += re.sub(r'<[^>]+>', '', x) + '\n' print('█ 大客户部 Q3 汇报文本 %d 字' % len(re.findall(r'[\u4e00-\u9fff]', deck_txt))) # ── 按需求聚合 ─────────────────────────────────────────────────── res = [] for need, kws in NEEDS: hits = defaultdict(lambda: {'n': 0, 'dk': False, 'samples': [], 'sp': set(), 'srcs': set()}) for a in acts: if not a['c']: continue t = a['t'] if any(k.lower() in t.lower() for k in kws): h = hits[a['c']] h['n'] += 1 h['dk'] = a['c'] in DK if a['sp']: h['sp'].add(a['sp']) h['srcs'].add(a['src']) if len(h['samples']) < 2 and len(t) > 8: h['samples'].append({'d': a['d'], 't': t[:110]}) # 汇报里提到该需求 + 客户 deck_cust = set() for c in DK: if c in deck_txt: # 客户名后 120 字内含关键词 for m in re.finditer(re.escape(c), deck_txt): seg = deck_txt[m.end():m.end() + 160] if any(k in seg for k in kws): deck_cust.add(c) for c in deck_cust: h = hits[c] h['dk'] = c in DK h['n'] += 1 h['srcs'].add('Q3汇报') if not h['samples']: h['samples'].append({'d': '2026-Q3', 't': '（Q3 汇报逐客户说明命中）'}) ranked = sorted(hits.items(), key=lambda kv: (-kv[1]['dk'], -kv[1]['n'], kv[0])) top = [] for c, h in ranked[:5]: top.append({'cust': c, 'dept': OFF.get(c, {}).get('dept', '未收录'), 'owner': OFF.get(c, {}).get('sp', '') or '、'.join(sorted(h['sp'])), 'dk': h['dk'], 'hits': h['n'], 'srcs': sorted(h['srcs']), 'samples': h['samples'][:2]}) res.append({'need': need, 'kws': kws, 'total_hits': sum(h['n'] for _, h in ranked), 'customers': top}) out = {'generated': '2026-10-17', 'window': [LO, HI], 'activities': len(acts), 'dk_customers': len(DK), 'needs': res} json.dump(out, io.open(D + '2027战略_需求客户映射.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1) print('\n' + '=' * 78) for r in res: print('\n▸ %s（命中 %d 条）' % (r['need'], r['total_hits'])) for c in r['customers']: print(' %s %-30s %-6s 归属 %-5s ｜命中 %2d ｜%s' % ('★' if c['dk'] else ' ', c['cust'][:28], c['dept'], c['owner'], c['hits'], '/'.join(c['srcs']))) print('\n✅ 数据/2027战略_需求客户映射.json')