#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026 浙江省自然科学基金 → 课题组级台账（2,607 个课题组）。

课题组命名 = 申请人 + “课题组”，与 ERP「商机(飞书)#」一致。
输出：销售分析/浙江自然科学基金_2026_课题组级台账.xlsx
     数据/zjnsf_2026_group_ledger.json
"""
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输出-工作类'
ERP = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-1021（含科研课题组名称）.xlsx'
OUT = ROOT + '/销售分析/浙江自然科学基金_2026_课题组级台账.xlsx'

DISC = {'A': '数理', 'B': '化学', 'C': '生命科学', 'D': '地球', 'E': '工程与材料',
        'F': '信息', 'G': '管理', 'H': '医学'}
BIG = {'重大项目', '重点项目', '杰青项目', '联合基金重点项目', '联合基金重大项目',
       '自主立项重大项目', '杰青项目延续项目', '重大项目延续项目'}
MID = {'探索项目', '联合基金探索项目', '地区科学基金项目', '青稞项目', '联合基金青基项目'}
LINES = [('PCR/扩增', ['PCR', 'qPCR', '扩增', '引物', '探针', '熔解曲线', '数字PCR']),
         ('测序/建库', ['测序', '转录组', '基因组', '单细胞', '宏基因组', '表观']),
         ('核酸提取', ['核酸', 'DNA提取', 'RNA提取', '磁珠', '提取']),
         ('分子克隆', ['克隆', '载体', '质粒', 'CRISPR', '基因编辑', '连接酶', '感受态']),
         ('蛋白', ['蛋白', '酶', '抗体', '多肽', '表达纯化']),
         ('细胞/类器官', ['细胞', '类器官', '干细胞', '培养']),
         ('冻干/预混', ['冻干', '预混', '保护剂', '稳定剂']),
         ('mRNA/核酸药', ['mRNA', 'siRNA', '核酸药', '递送', 'LNP', '环状RNA', 'circRNA'])]

P = json.load(open(ROOT + '/数据/zjnsf_2026_projects.json', encoding='utf-8'))
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 '?'

grp = defaultdict(lambda: {'projs': [], 'lines': Counter()})
for p in P:
    g = grp[p['pi'] + '课题组']
    g['projs'].append(p)
    g['lines'].update({n for n, kws in LINES if any(w.lower() in p['name'].lower() for w in kws)})

wb = openpyxl.load_workbook(ERP, data_only=True)
ws = wb['Sheet1']
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)}
erp = defaultdict(lambda: {'amt': 0.0, 'ords': set(), 'cust': Counter(), 'sp': Counter(), 'sku': Counter()})
for r in rows:
    k = str(r[ix['商机(飞书)#']] or '').strip()
    if not k:
        continue
    x = erp[k]
    x['amt'] += float(r[ix['销售数量']] or 0) * float(r[ix['含税单价']] or 0)
    x['ords'].add(str(r[ix['单据编号']] or ''))
    x['cust'][str(r[ix['客户']] or '')] += 1
    x['sp'][str(r[ix['销售员']] or '')] += 1
    x['sku'][str(r[ix['货号#']] or '')] += 1

out = []
for name, g in grp.items():
    ps = sorted(g['projs'], key=lambda x: x['no'])
    cats = Counter(p['cat'] for p in ps)
    discs = Counter(p['disc'] for p in ps)
    top = 3 if any(c in BIG for c in cats) else (2 if any(c in MID for c in cats) else 1)
    k = 2 if len(g['lines']) >= 2 else (1 if g['lines'] else 0)
    e = erp.get(name)
    own = 1 if e else 0
    s = top + k + own
    tier = 'A' if s >= 5 else 'B' if s >= 3 else 'C'
    out.append({
        'group': name, 'pi': ps[0]['pi'], 'unit': ps[0]['unit'], 'n': len(ps),
        'top_cat': cats.most_common(1)[0][0], 'disc': discs.most_common(1)[0][0],
        'lines': '、'.join(g['lines']), 'kw_n': len(g['lines']),
        'amt': round(e['amt'], 2) if e else 0, 'ords': len(e['ords']) if e else 0,
        'cust': e['cust'].most_common(1)[0][0] if e else '',
        'sp': e['sp'].most_common(1)[0][0] if e else '',
        'sku': '、'.join(x for x, _ in e['sku'].most_common(2)) if e else '',
        'tier': tier, 'score': s,
        'proj_names': ' ｜ '.join('%s（%s）' % (p['name'], p['cat']) for p in ps),
        'codes': '、'.join(p['code'] for p in ps),
    })

out.sort(key=lambda x: ({'A': 0, 'B': 1, 'C': 2}[x['tier']], -x['score'], -x['amt'], -x['n']))

W = openpyxl.Workbook()
s1 = W.active
s1.title = '课题组台账'
H1 = ['课题组', 'PI', '依托单位', '项目数', '最高类别', '学部', '可切入产品线', '命中线数',
      '我方采购(元)', '单数', '客户', '销售员', '主销货号', '级别', '得分', '项目名称（类别）', '申请编号']
s1.append(H1)
for r in out:
    s1.append([r['group'], r['pi'], r['unit'], r['n'], r['top_cat'], r['disc'], r['lines'] or '—',
               r['kw_n'], r['amt'], r['ords'], r['cust'], r['sp'], r['sku'], r['tier'], r['score'],
               r['proj_names'], r['codes']])
for c in s1[1]:
    c.font = Font(bold=True, color='FFFFFF')
    c.fill = PatternFill('solid', fgColor='1F4E79')
    c.alignment = Alignment(horizontal='center', wrap_text=True)
for j, w in enumerate([18, 8, 30, 7, 16, 11, 24, 8, 12, 6, 26, 8, 26, 6, 6, 90, 40], 1):
    s1.column_dimensions[get_column_letter(j)].width = w
s1.freeze_panes = 'D2'
s1.auto_filter.ref = s1.dimensions
tf = {'A': PatternFill('solid', fgColor='C6EFCE'), 'B': PatternFill('solid', fgColor='FFEB9C'),
      'C': PatternFill('solid', fgColor='F2F2F2')}
for row in s1.iter_rows(min_row=2, max_row=s1.max_row):
    row[13].fill = tf[row[13].value]
    row[13].alignment = Alignment(horizontal='center')

s2 = W.create_sheet('汇总')
s2.append(['维度', '取值', '课题组数', 'A', 'B', 'C', '已购(元)'])
for dim, key in (('学部', 'disc'), ('最高类别', 'top_cat')):
    seen = Counter(r[key] for r in out)
    for v, n in seen.most_common():
        rs = [r for r in out if r[key] == v]
        s2.append([dim, v, n, 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(sum(r['amt'] for r in rs))])
for v, n in Counter(r['unit'] for r in out).most_common(20):
    rs = [r for r in out if r['unit'] == v]
    s2.append(['依托单位Top20', v, n, 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(sum(r['amt'] for r in rs))])
for c in s2[1]:
    c.font = Font(bold=True, color='FFFFFF')
    c.fill = PatternFill('solid', fgColor='2E75B6')
for j, w in enumerate([16, 34, 10, 8, 8, 8, 12], 1):
    s2.column_dimensions[get_column_letter(j)].width = w
s2.freeze_panes = 'A2'

s3 = W.create_sheet('口径')
for r in [['项目', '说明'],
          ['来源', '2026 年度浙江省自然科学基金拟资助项目清单（2,669 项，92 页）'],
          ['课题组', '申请人 + “课题组”，与 ERP「商机(飞书)#」命名一致；2,669 项 → 2,607 个课题组'],
          ['级别', '重大/重点/杰青=3 分；探索/联合/地区/青稞=2 分；青基=1 分；产品线 ≥2 类 +2；已有采购 +1。A≥5，B 3–4，C≤2'],
          ['产品线', '项目名称关键词命中，属推断；命中词可在 HTML 报告三·六节回溯'],
          ['我方采购', 'ERP「商机(飞书)#」与课题组名精确匹配；未匹配=0，不代表无采购（该字段仅 11.8% 订单有值）；采购 0 元但有单数=赠品/测试单'],
          ['金额', '清单无经费字段，本表不推算金额；采购额为实际订单（数量×含税单价）']]:
    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 = 12
s3.column_dimensions['B'].width = 100
for row in s3.iter_rows(min_row=2, max_row=s3.max_row):
    row[1].alignment = Alignment(wrap_text=True, vertical='top')

W.save(OUT)
json.dump(out, open(ROOT + '/数据/zjnsf_2026_group_ledger.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=0)
print('课题组', len(out), '｜A', sum(1 for r in out if r['tier'] == 'A'),
      '｜B', sum(1 for r in out if r['tier'] == 'B'), '｜C', sum(1 for r in out if r['tier'] == 'C'))
print('已写', OUT)