#!/usr/bin/env python3 # -*- coding: utf-8 -*- """2026 浙江省自然科学基金 × 已合作单位 → PI 级商机台账。 输入 数据/zjnsf_2026_projects.json （PDF 解析结果，2,669 项） ERP 2026 订单（至 10-21）/ 2025 全年订单 （单位级采购额） 数据/daily_activities_mcp.json （单位级活动数/最近活动/负责销售） 输出 销售分析/浙江自然科学基金_2026_PI级商机台账.xlsx 数据/zjnsf_2026_pi_ledger.json 口径（全部可复算） · 只保留「依托单位 = 我方活动客户」的 994 项； · 采购额、活动数是**单位级**，不是 PI 级；PI 级成交需销售补充，不做推断； · 产品线映射按项目名称关键词，命中词逐条写入「契合依据」列，标注为推断； · 优先级 = 采购力(0-3) + 项目量级(1-3) + 契合(0-2) + 近90天活动(0-1)。 """ import json import os import re from collections import Counter, defaultdict import openpyxl from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' ERP26 = os.path.join(os.path.expanduser('~'), 'Desktop', 'hermas输入-工作台数据库', '月度销售额数据', '销售订单_20260101-1021（含科研课题组名称）.xlsx') ERP25 = os.path.join(os.path.expanduser('~'), 'Desktop', 'hermas输入-工作台数据库', '2024和2025历史数据', '2025全年订单.xlsx') OUT_X = os.path.join(ROOT, '销售分析', '浙江自然科学基金_2026_PI级商机台账.xlsx') OUT_J = os.path.join(ROOT, '数据', 'zjnsf_2026_pi_ledger.json') TODAY = '2026-10-22' LINES = [ ('PCR/扩增原料', ['PCR', 'qPCR', '扩增', '引物', '探针', '熔解曲线', '数字PCR', '等温']), ('测序/建库', ['测序', '转录组', '基因组', '单细胞', '宏基因组', '表观遗传', '多组学']), ('核酸提取', ['核酸提取', 'DNA提取', 'RNA提取', '磁珠', '样本前处理', '核酸']) , ('分子克隆', ['克隆', '载体', '质粒', 'CRISPR', '基因编辑', '连接酶', '感受态', '同源重组']), ('蛋白表达纯化', ['蛋白', '酶', '抗体', '多肽', '表达纯化', '结构解析']), ('细胞/类器官', ['细胞', '类器官', '干细胞', '培养', '转染']), ('冻干/预混', ['冻干', '预混', '保护剂', '稳定剂', '冻存']), ('mRNA/核酸药', ['mRNA', 'siRNA', '核酸药', '递送', 'LNP', '脂质纳米', '环状RNA', 'circRNA']), ] BIG = {'重大项目', '重点项目', '杰青项目', '联合基金重点项目', '联合基金重大项目', '自主立项重大项目', '杰青项目延续项目', '重大项目延续项目'} def norm(s): s = re.sub(r'[（(].*?[)）]', '', str(s or '')) s = re.sub(r'-(浙江|广东|上海|江苏|北京)$', '', s) return re.sub(r'[\s\-—·]', '', s) def read_erp(path): wb = openpyxl.load_workbook(path, data_only=True) ws = wb['Sheet1'] if 'Sheet1' in wb.sheetnames else wb.worksheets[0] rows = ws.iter_rows(values_only=True) H = [str(x or '').strip() for x in next(rows)] ix = {h: j for j, h in enumerate(H)} out = defaultdict(lambda: {'amt': 0.0, 'ords': set(), 'last': '', 'skus': set()}) for r in rows: cust = str(r[ix['客户']] or '') if not cust: continue k = norm(cust) amt = float(r[ix['销售数量']] or 0) * float(r[ix['含税单价']] or 0) o = out[k] o['amt'] += amt o['ords'].add(str(r[ix['单据编号']] or '')) d = str(r[ix['发货日期']] or '')[:10] if d: o['last'] = max(o['last'], d) sku = str(r[ix['货号#']] or '') if sku: o['skus'].add(sku) return out print('读 ERP …') erp26 = read_erp(ERP26) erp25 = read_erp(ERP25) acts = json.load(open(os.path.join(ROOT, '数据', 'daily_activities_mcp.json'), encoding='utf-8'))['items'] act_by_unit = defaultdict(lambda: {'n': 0, 'last': '', 'owners': Counter(), 'n90': 0}) for a in acts: c = a.get('field_5f20fc') label = c.get('label') if isinstance(c, dict) else str(c or '') if not label: continue k = norm(label) d = str(a.get('start_time') or '')[:10] o = act_by_unit[k] o['n'] += 1 o['last'] = max(o['last'], d) if d >= '2026-07-24': o['n90'] += 1 ow = a.get('owner') if isinstance(ow, dict) and ow.get('name_cn'): o['owners'][ow['name_cn']] += 1 P = json.load(open(os.path.join(ROOT, '数据', 'zjnsf_2026_projects.json'), encoding='utf-8')) units = {p['unit'] for p in P if norm(p['unit']) in act_by_unit} DISC = {'A': '数理', 'B': '化学', 'C': '生命科学', 'D': '地球', 'E': '工程与材料', 'F': '信息', 'G': '管理', 'H': '医学'} for _p in P: _m = re.match(r'[A-Z]+(\d{2})([A-Z])\d+', _p['code']) _p['disc'] = DISC.get(_m.group(2), '?') if _m else '?' rows = [] for p in P: if p['unit'] not in units: continue hits = [] for name, kws in LINES: got = [w for w in kws if w.lower() in p['name'].lower()] if got: hits.append((name, got)) a = act_by_unit.get(norm(p['unit']), {'n': 0, 'last': '', 'owners': Counter(), 'n90': 0}) e26 = erp26.get(norm(p['unit']), {'amt': 0, 'ords': set(), 'last': '', 'skus': set()}) e25 = erp25.get(norm(p['unit']), {'amt': 0, 'ords': set(), 'last': '', 'skus': set()}) amt26 = e26['amt'] / 10000.0 p_power = 3 if amt26 >= 50 else 2 if amt26 >= 10 else 1 if amt26 >= 1 else 0 t_level = 3 if p['cat'] in BIG else (2 if p['cat'] in {'探索项目', '联合基金探索项目', '地区科学基金项目', '青稞项目', '联合基金青基项目'} else 1) k_level = 2 if len(hits) >= 2 else 1 if hits else 0 a_level = 1 if a['n90'] > 0 else 0 score = p_power + t_level + k_level + a_level tier = 'A' if score >= 7 else 'B' if score >= 5 else 'C' rows.append({ 'no': p['no'], 'unit': p['unit'], 'pi': p['pi'], 'name': p['name'], 'code': p['code'], 'cat': p['cat'], 'disc': p['disc'], 'lines': '、'.join(h[0] for h in hits) or '未命中', 'ev': '、'.join(sorted({w for _, ws in hits for w in ws}))[:80], 'amt26': round(amt26, 2), 'amt25': round(e25['amt'] / 10000.0, 2), 'ords26': len(e26['ords']), 'last_ship': e26['last'] or e25['last'], 'act_n': a['n'], 'act_last': a['last'], 'act90': a['n90'], 'owner': a['owners'].most_common(1)[0][0] if a['owners'] else '', 'score': score, 'tier': tier, 'proj_score': t_level + k_level, 'unit_score': p_power + a_level, 'act': ('核对是否已建课题组档案；带 1–2 个同方向货号做技术交流（项目：%s）' % p['name'][:24]) if tier == 'A' else ('确认课题组采购主体与预算年度；先寄试用装（项目：%s）' % p['name'][:24]) if tier == 'B' else ('纳入单位级年度拜访清单，暂不单独立项（项目：%s）' % p['name'][:24]), }) print('台账行数', len(rows), '｜单位', len(units)) print('分级', Counter(r['tier'] for r in rows)) print('产品线命中', Counter(r['lines'] for r in rows).most_common(8)) wb = openpyxl.Workbook() ws = wb.active ws.title = 'PI级商机台账' H = ['序号', '依托单位', '申请人(PI)', '项目名称', '申请编号', '项目类别', '学部', '可切入产品线', '契合依据（关键词）', '2026采购(万)', '2025采购(万)', '2026订单数', '最近发货', '活动数', '最近活动', '近90天活动', '负责销售', '优先级', '总分', '项目级分', '单位级分', '建议动作'] ws.append(H) for r in sorted(rows, key=lambda x: ({'A': 0, 'B': 1, 'C': 2}[x['tier']], -x['score'], -x['amt26'])): ws.append([r['no'], r['unit'], r['pi'], r['name'], r['code'], r['cat'], r['disc'], r['lines'], r['ev'], r['amt26'], r['amt25'], r['ords26'], r['last_ship'], r['act_n'], r['act_last'], r['act90'], r['owner'], r['tier'], r['score'], r['proj_score'], r['unit_score'], r['act']]) head_fill = PatternFill('solid', fgColor='1F4E79') for c in ws[1]: c.font = Font(bold=True, color='FFFFFF') c.fill = head_fill c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) widths = [6, 30, 10, 52, 15, 18, 10, 18, 24, 11, 11, 10, 11, 8, 11, 9, 10, 8, 6, 9, 9, 60] for j, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(j)].width = w ws.freeze_panes = 'D2' ws.auto_filter.ref = ws.dimensions tier_fill = {'A': PatternFill('solid', fgColor='C6EFCE'), 'B': PatternFill('solid', fgColor='FFEB9C'), 'C': PatternFill('solid', fgColor='F2F2F2')} for row in ws.iter_rows(min_row=2, max_row=ws.max_row): row[17].fill = tier_fill.get(row[17].value) row[17].alignment = Alignment(horizontal='center') # 单位汇总 s2 = wb.create_sheet('单位汇总') s2.append(['依托单位', '项目数', 'A级', 'B级', 'C级', '2026采购(万)', '2025采购(万)', '活动数', '近90天活动', '最近活动', '主责销售']) for u in sorted(units, key=lambda u: -sum(1 for r in rows if r['unit'] == u)): rs = [r for r in rows if r['unit'] == u] e26 = erp26.get(norm(u), {'amt': 0}) a = act_by_unit.get(norm(u), {'n': 0, 'last': '', 'owners': Counter(), 'n90': 0}) s2.append([u, len(rs), sum(1 for r in rs if r['tier'] == 'A'), sum(1 for r in rs if r['tier'] == 'B'), sum(1 for r in rs if r['tier'] == 'C'), round(e26['amt'] / 10000.0, 2), round(erp25.get(norm(u), {'amt': 0})['amt'] / 10000.0, 2), a['n'], a['n90'], a['last'], a['owners'].most_common(1)[0][0] if a['owners'] else '']) for c in s2[1]: c.font = Font(bold=True, color='FFFFFF') c.fill = PatternFill('solid', fgColor='2E75B6') c.alignment = Alignment(horizontal='center') for j, w in enumerate([34, 8, 7, 7, 7, 13, 13, 8, 11, 11, 10], 1): s2.column_dimensions[get_column_letter(j)].width = w s2.freeze_panes = 'A2' # 口径说明 s3 = wb.create_sheet('口径与规则') notes = [ ['项目', '说明'], ['数据来源', '2026年度浙江省自然科学基金拟资助项目清单（PDF，92 页，2,669 项）；ERP 订单至 2026-10-21；飞书活动库至 2026-10-22'], ['筛选范围', '依托单位与我方活动客户精确匹配的 13 家单位、994 个项目；匹配按去括号/去“-浙江”后缀归一后精确相等'], ['采购额口径', '单位级（依托单位名下全部订单），非 PI 级；金额=销售数量×含税单价；未剔除赠品 0 元行，故订单数含赠品单'], ['活动口径', '单位级活动条数；近90天= 2026-07-24 起；负责销售取该单位活动出现最多的负责人'], ['产品线映射', '按项目名称关键词命中，属推断，命中词见「契合依据」列，须销售确认后再对外'], ['评分规则', '总分 = 单位级分（采购力 0-3：≥50万=3，10-50=2，1-10=1，<1=0；近90天活动 0-1）+ 项目级分（项目量级 1-3：重大/重点/杰青=3；产品线契合 0-2：≥2类=2）。A≥7，B 5-6，C≤4'], ['分级', 'A ≥7 分；B 5-6 分；C ≤4 分'], ['已知缺口', '① 清单无资助金额字段，不能加总经费；② PI 级历史成交缺失（ERP 无课题组字段），需销售补；③ 13 家单位之外还有 218 家单位未覆盖'], ] for r in notes: s3.append(r) s3['A1'].font = Font(bold=True, color='FFFFFF') s3['B1'].font = Font(bold=True, color='FFFFFF') for c in ('A1', 'B1'): s3[c].fill = PatternFill('solid', fgColor='1F4E79') s3.column_dimensions['A'].width = 14 s3.column_dimensions['B'].width = 110 for row in s3.iter_rows(min_row=2, max_row=s3.max_row): row[1].alignment = Alignment(wrap_text=True, vertical='top') wb.save(OUT_X) json.dump(rows, open(OUT_J, 'w', encoding='utf-8'), ensure_ascii=False, indent=0) print('已写', OUT_X) print('已写', OUT_J)