#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""研究方向 × 产品大类矩阵（含宝锐可及/不可及标识）+ 重出 HTML/Excel"""
import io
import json
import sys

sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本')
import brand_kit as BK  # noqa: E402
from openpyxl import load_workbook  # noqa: E402
from openpyxl.styles import Alignment, Font, PatternFill  # noqa: E402
from openpyxl.utils import get_column_letter  # noqa: E402

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
J = W + '/数据/科研经费_统一口径_产品线.json'
OUT_HTML = W + '/销售分析/科研经费与宝锐产品线关联_统一口径_2026-10-17.html'
OUT_XLSX = W + '/销售分析/科研经费_单位级清单_广东浙江_2026.xlsx'

L = ['分子·RT-qPCR', '分子·PCR/克隆', '分子·核酸提取', '分子·LAMP/恒温', '分子·其他', 'WB']
d = json.load(io.open(J, encoding='utf-8'))
P = d['provinces']; S = d['struct']

# 不可及部分的内部分布（占不可及经费的 %，经验值）
NON = {
    '蛋白/信号/修饰': {'mRNA': 2, '细胞': 35, '抗体/免疫': 40, '测序/组学': 15, '动物/表型': 8},
    '肿瘤': {'mRNA': 3, '细胞': 30, '抗体/免疫': 35, '测序/组学': 20, '动物/表型': 12},
    '免疫/感染/炎症': {'mRNA': 8, '细胞': 30, '抗体/免疫': 35, '测序/组学': 15, '动物/表型': 12},
    '神经科学': {'mRNA': 3, '细胞': 30, '抗体/免疫': 30, '测序/组学': 20, '动物/表型': 17},
    '代谢/内分泌': {'mRNA': 3, '细胞': 30, '抗体/免疫': 35, '测序/组学': 20, '动物/表型': 12},
    '遗传/表观/基因组': {'mRNA': 5, '细胞': 20, '抗体/免疫': 20, '测序/组学': 45, '动物/表型': 10},
    '生物材料/传感器': {'mRNA': 2, '细胞': 45, '抗体/免疫': 20, '测序/组学': 10, '动物/表型': 23},
    '细胞生物学': {'mRNA': 3, '细胞': 40, '抗体/免疫': 30, '测序/组学': 15, '动物/表型': 12},
    '药理/中药': {'mRNA': 2, '细胞': 30, '抗体/免疫': 30, '测序/组学': 15, '动物/表型': 23},
    '临床/流行病': {'mRNA': 3, '细胞': 25, '抗体/免疫': 35, '测序/组学': 20, '动物/表型': 17},
    '农学/动植': {'mRNA': 3, '细胞': 25, '抗体/免疫': 20, '测序/组学': 25, '动物/表型': 27},
    '其他生医': {'mRNA': 3, '细胞': 30, '抗体/免疫': 30, '测序/组学': 22, '动物/表型': 15},
}
COLS = [('分子·RT-qPCR', 'RT-qPCR', True), ('分子·PCR/克隆', 'PCR/克隆', True),
        ('分子·核酸提取', '提取', True), ('分子·LAMP/恒温', 'LAMP', True),
        ('分子·其他', '其他分子', True), ('WB', 'WB', True),
        ('mRNA', 'mRNA', True), ('细胞', '细胞培养', False),
        ('抗体/免疫', '抗体/免疫', False), ('测序/组学', '测序/组学', False), ('动物/表型', '动物/表型', False)]


def full_share(topic):
    rate, mix = S[topic]['rate'], S[topic]['mix']
    out = {k: rate * mix[k] / 100 for k in L}
    rest = 1 - rate
    for k, v in NON[topic].items():
        out[k] = rest * v / 100
    return out


def fmt(v):
    return f'{float(v):,.0f}'


def plain(x):
    import re
    return re.sub(r'<[^>]+>', '', str(x)).replace(',', '').replace('%', '').strip()


def rows(data):
    out = []
    for r in data:
        cells = []
        for i, c in enumerate(r):
            p = plain(c)
            num = bool(p) and p.replace('.', '').isdigit()
            cells.append('<td%s>%s</td>' % (' class="n"' if i and num else '', c))
        out.append('<tr>' + ''.join(cells) + '</tr>')
    return '\n'.join(out)


def align_numeric_headers(html):
    import re
    out, pos = [], 0
    for m in re.finditer(r'<table>(.*?)</table>', html, re.S):
        out.append(html[pos:m.start()])
        tbl = m.group(0)
        ths = re.findall(r'<th([^>]*)>(.*?)</th>', tbl, re.S)
        first = re.search(r'<tbody>\s*<tr>(.*?)</tr>', tbl, re.S)
        tds = re.findall(r'<td[^>]*>(.*?)</td>', first.group(1), re.S) if first else []
        assert len(tds) == len(ths), '表头 %d 列 vs 数据 %d 列：%s' % (len(ths), len(tds), tbl[:100])
        new = ['<th%s>%s</th>' % (a + (' class="n"' if i < len(tds) and plain(tds[i]).replace('.', '').isdigit() and 'class=' not in a else ''), inner)
               for i, (a, inner) in enumerate(ths)]
        old = re.search(r'<thead>.*?</thead>', tbl, re.S)
        if old:
            tbl = tbl.replace(old.group(0), '<thead><tr>' + ''.join(new) + '</tr></thead>', 1)
        out.append(tbl); pos = m.end()
    out.append(html[pos:])
    return ''.join(out)


# ── 矩阵：两省合计 ──
allt = sorted(set(P['浙江']['by_topic']) | set(P['广东']['by_topic']))
mat = []
for t in allt:
    z = P['浙江']['by_topic'].get(t, {'amt': 0, 'n': 0, 'addr': 0})
    g = P['广东']['by_topic'].get(t, {'amt': 0, 'n': 0, 'addr': 0})
    amt = z['amt'] + g['amt']
    if amt <= 0:
        continue
    fs = full_share(t)
    row = [t, fmt(amt)]
    for k, _, _ in COLS:
        row.append(fmt(amt * fs[k]))
    addr = amt * S[t]['rate']
    row += [fmt(addr), '%.0f%%' % (S[t]['rate'] * 100)]
    mat.append((amt, row))
mat.sort(key=lambda x: -x[0])
matrix_rows = [r for _, r in mat]

tot_row = ['<b>合计</b>', '<b>%s</b>' % fmt(sum(x[0] for x in mat))]
for ci in range(len(COLS)):
    tot_row.append('<b>%s</b>' % fmt(sum(float(x[1][2 + ci].replace(',', '')) for x in mat)))
_ta = sum(float(x[1][-2].replace(',', '')) for x in mat)
_tm = sum(float(x[1][1].replace(',', '')) for x in mat)
tot_row += ['<b>%s</b>' % fmt(_ta), '%.0f%%' % (_ta / _tm * 100)]
matrix_rows.append(tot_row)

# 技术路径表
TECH = [
    ('Western blot', '6.8%', '85–90%', '95%', 'WB 一线全线（RIPA/抑制剂/BCA/上样/电泳/转膜/TBST/膜/ECL/marker）', '高'),
    ('qPCR / RT-qPCR', '5.2%', '60–70%', '80%', 'RT-qPCR 预混 + 逆转录 + 核酸提取', '高'),
    ('免疫沉淀 IP/Co-IP', '4.5%', '20–30%', '65%', 'WB 线（裂解液、蛋白酶抑制剂）+ 蛋白纯化缺口', '中高'),
    ('免疫荧光 IF', '4.5%', '50–60%', '20%', '仅覆盖封闭/抗体稀释；抗体非我司', '低'),
    ('免疫组化 IHC', '3.1%', '20–30%', '15%', '同上，石蜡切片耗材非我司', '低'),
    ('流式细胞术', '3.5%', '20–25%', '5%', '几乎无对应货号', '极低'),
    ('ELISA', '2.7%', '15–20%', '15%', '仅缓冲液/封闭', '低'),
    ('RNA-seq', '7.9%', '30–40%', '25%', '仅核酸提取', '低'),
    ('质谱/蛋白组', '7.5%', '15–20%', '10%', '仅裂解/提取', '极低'),
    ('CRISPR/基因编辑', '1.7%', '10–15%', '75%', '无缝克隆/连接/感受态/质粒提取', '高'),
]

zl, gl = P['浙江']['by_line'], P['广东']['by_line']
zs, gs = sum(zl.values()), sum(gl.values())
line_rows = [[l, fmt(zl[l]), '%.1f%%' % (zl[l] / zs * 100), fmt(gl[l]), '%.1f%%' % (gl[l] / gs * 100),
              fmt(zl[l] + gl[l]), '%.1f%%' % ((zl[l] + gl[l]) / (zs + gs) * 100), '<span class="ok">可及</span>'] for l in L]
line_rows.append(['<b>合计</b>', '<b>%s</b>' % fmt(zs), '100%', '<b>%s</b>' % fmt(gs), '100%',
                  '<b>%s</b>' % fmt(zs + gs), '100%', ''])


def unit_rows(prov, n=10):
    out = []
    for u in P[prov]['units'][:n]:
        main = max(L, key=lambda l: u['lines'][l])
        out.append([u['unit'], fmt(u['n']), fmt(u['amt']), fmt(u['addr']),
                    '%.0f%%' % (u['addr'] / u['amt'] * 100 if u['amt'] else 0), main])
    return out


CSS = """*{box-sizing:border-box}body{margin:0;background:#f4f6fa;font-family:-apple-system,"PingFang SC",sans-serif;color:#1b2430;font-size:13px;line-height:1.6}
.wrap{max-width:1320px;margin:0 auto;padding:0 18px 40px}h1{font-size:21px;margin:10px 0 4px}
.meta{color:#5b6675;font-size:12.3px;margin-bottom:12px}
.card{background:#fff;border:1px solid #e3e8f1;border-radius:10px;padding:14px 16px;margin-bottom:14px}
.card h2{font-size:15px;margin:0 0 10px;padding-left:9px;border-left:3px solid #1F4E79}
.card h2 span{font-size:10.6px;color:#8a94a3;font-weight:600;margin-left:6px}
h3{font-size:13px;color:#1F4E79;margin:14px 0 6px}
table{width:100%;border-collapse:collapse;font-size:10.9px}
th{background:#1F4E79;color:#fff;padding:6px 5px;text-align:left;font-weight:600;line-height:1.35}
th.n{text-align:right}
th.na{background:#6b7684}
td{padding:5px 5px;border-bottom:1px solid #eef1f6;vertical-align:top}
td.n{text-align:right;font-variant-numeric:tabular-nums;white-space:nowrap}
td.acc{background:#f2f7fd}td.no{color:#6b7684}
.kpis{display:grid;grid-template-columns:repeat(4,1fr);gap:9px;margin:10px 0 14px}
.kpi{background:#fff;border:1px solid #e3e8f1;border-top:3px solid #1F4E79;border-radius:9px;padding:9px 11px}
.kl{font-size:11px;color:#6b7684}.kv{font-size:19px;font-weight:800;color:#1F4E79;margin:2px 0}
.ks{font-size:10.5px;color:#8a94a3}
.tag{display:inline-block;font-size:10px;font-weight:800;padding:1px 6px;border-radius:8px}
.ok{background:#e8f6ee;color:#1c7c54}.est{background:#fff4e0;color:#8a5a12}
.box{background:#f7f9fd;border:1px solid #dfe6f2;border-radius:8px;padding:10px 12px}
.box.warn{background:#fffaf0;border-color:#f3e2bf}
.grid2{display:grid;grid-template-columns:1fr 1fr;gap:10px}
.fn{color:#6b7684;font-size:11.3px;margin-top:7px}
ul{margin:4px 0;padding-left:18px}
.legend{display:flex;gap:14px;font-size:11px;color:#5b6675;margin:6px 0 8px}
.legend i{display:inline-block;width:11px;height:11px;border-radius:2px;margin-right:4px;vertical-align:-1px}"""

head_cols = ''.join('<th class="n%s">%s</th>' % ('' if acc else ' na', name) for _, name, acc in COLS)

page = """<!DOCTYPE html><html lang="zh-CN"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>科研经费与宝锐产品线关联（统一口径）</title><style>__CSS__</style></head><body><div class="wrap">
<h1>科研经费与宝锐产品线关联：广东 / 浙江 2026 省基金</h1>
<div class="meta">口径：两省统一——项目名称 → 研究方向 → 技术路径 → 产品大类。<b>蓝色列＝宝锐可及</b>，灰色列＝看得见但做不了。金额为 2026 年立项经费（万元），结构与占比为经验值，技术路径占比用 PubMed 2024–2025 条件提及率做排序锚点。截至 2026-10-17。</div>

<div class="kpis">
 <div class="kpi"><div class="kl">两省省基金总盘</div><div class="kv">9.20 亿</div><div class="ks">5,838 项</div></div>
 <div class="kpi"><div class="kl">生医相关</div><div class="kv">5.19 亿</div><div class="ks">3,383 项</div></div>
 <div class="kpi"><div class="kl">宝锐可及</div><div class="kv">2.83 亿</div><div class="ks">占生医 54.5%</div></div>
 <div class="kpi"><div class="kl">不可及（抗体/测序/细胞）</div><div class="kv">2.36 亿</div><div class="ks">看得见、拿不到</div></div>
</div>

<div class="card"><h2>一、研究方向 × 产品大类矩阵 <span>万元；蓝色＝宝锐可及，灰色＝不可及</span></h2>
<div class="legend"><span><i style="background:#1F4E79"></i>宝锐可及</span><span><i style="background:#6b7684"></i>不可及（抗体/测序/细胞/动物）</span><span><i style="background:#f2f7fd;border:1px solid #d6e2f2"></i>可及列底色</span></div>
<table><thead><tr><th>研究方向</th><th class="n">经费合计</th>__HEAD__<th class="n">可及小计</th><th class="n">可及率</th></tr></thead>
<tbody>__MATRIX__</tbody></table>
<div class="fn">读法：每一行是一个研究方向，蓝色列相加＝该方向宝锐可做的那部分钱。mRNA 单列，因为它是宝锐科研产品线里唯一还没形成销售的品类。</div></div>

<div class="card"><h2>二、六个产品线的两省拆解 <span>宝锐可及部分的内部结构</span></h2>
<table><thead><tr><th>产品线</th><th class="n">浙江</th><th class="n">占比</th><th class="n">广东</th><th class="n">占比</th><th class="n">两省合计</th><th class="n">合计占比</th><th>状态</th></tr></thead>
<tbody>__LINES__</tbody></table>
<div class="fn">WB 35% 是第一大品类，RT-qPCR 21.6% 第二；两者合计 56.6%，构成科研市场的绝对主体。</div></div>

<div class="card"><h2>三、蛋白 / 信号 / 通路：主流技术路径与覆盖度 <span>回答“占比多少、关联度怎样”</span></h2>
<table><thead><tr><th>技术路径</th><th class="n">PubMed 提及率</th><th class="n">实验使用率<br>（经验值）</th><th class="n">宝锐覆盖度</th><th>对应产品线 / 缺口</th><th>关联强度</th></tr></thead>
<tbody>__TECH__</tbody></table>
<div class="grid2" style="margin-top:10px">
<div class="box warn"><h4 style="margin:0 0 4px">两个数为什么差这么多</h4><p style="margin:2px 0">PubMed 6.8% 是“摘要里提到 western blot”，不是实验使用率——绝大多数摘要不写方法。<b>排序可信，绝对值不可当份额。</b>实验使用率 85–90% 来自实验室惯例与试剂消耗结构，属经验值。</p></div>
<div class="box"><h4 style="margin:0 0 4px">三个可直接用的判断</h4><ul style="margin:2px 0">
<li>蛋白方向的钱一半在 WB，且 WB 覆盖度 95%——<b>这是我们产品力最强、最该守的基本盘</b>。</li>
<li>IF/IHC 使用率 50–60% 但覆盖度只有 20%——<b>不是产品缺口，是抗体缺口</b>，只能靠联名/OEM 补。</li>
<li>CRISPR 使用率虽低，覆盖度 75%，且客单价高——<b>适合做“小批量高毛利”补充</b>。</li></ul></div>
</div></div>

<div class="card"><h2>四、单位层级：宝锐可及经费 Top 10</h2>
<h3>广东</h3>
<table><thead><tr><th>单位</th><th class="n">省基金项数</th><th class="n">总经费</th><th class="n">宝锐可及</th><th class="n">可及占比</th><th>最大产品线</th></tr></thead><tbody>__UGD__</tbody></table>
<h3>浙江</h3>
<table><thead><tr><th>单位</th><th class="n">省基金项数</th><th class="n">总经费</th><th class="n">宝锐可及</th><th class="n">可及占比</th><th>最大产品线</th></tr></thead><tbody>__UZJ__</tbody></table>
<div class="fn">全量单位清单（广东 255 家 / 浙江 231 家）见 Excel，含每家六个产品线拆分。</div></div>

<div class="card"><h2>五、口径与不确定点 <span>请你判断</span></h2>
<div class="grid2">
<div class="box"><h4 style="margin:0 0 4px">口径</h4><ul>
<li>两省均按项目名称归类，浙江学科代码仅校验（与名称一致率 35.1%）。</li>
<li>生物材料/传感器已纳入，要求“材料词 + 生物锚点”同时命中。</li>
<li>矩阵各行相加＝该方向 100% 经费；蓝色列＝宝锐六大品类；mRNA 单列（部分可及）。</li>
</ul></div>
<div class="box warn"><h4 style="margin:0 0 4px">我不确定的 3 个点</h4><ul>
<li><b>不可及部分的内部分布（细胞/抗体/测序）是经验值</b>，没有客户支出数据支撑。</li>
<li><b>mRNA 占比（2–8%）最不确定</b>：省基金里 mRNA 项目很少，这个数更多反映“未来潜力”而非当期盘子。</li>
<li><b>浙江金额按类别标准额测算</b>，不是财政逐项下达数。</li>
</ul></div>
</div></div>
__FOOTER__
</div></body></html>"""

page = (page.replace('__CSS__', CSS).replace('__HEAD__', head_cols)
        .replace('__MATRIX__', rows(matrix_rows)).replace('__LINES__', rows(line_rows))
        .replace('__TECH__', rows([list(t) for t in TECH]))
        .replace('__UGD__', rows(unit_rows('广东'))).replace('__UZJ__', rows(unit_rows('浙江'))))
page = align_numeric_headers(page)
page = BK.inject(page, theme='dark', tag='科研经费 × 产品线 · 统一口径',
                 footer_extra='内部资料 · 请勿外发 ｜ 源：广东/浙江 2026 省基金立项清单、PubMed E-utilities')
page = page.replace('__FOOTER__', '')
io.open(OUT_HTML, 'w', encoding='utf-8').write(page)
print('✅ HTML %.0f KB' % (len(page.encode()) / 1024))

# ── Excel：替换「研究方向」sheet ──
wb = load_workbook(OUT_XLSX)
if '研究方向' in wb.sheetnames:
    del wb['研究方向']
ws = wb.create_sheet('研究方向_产品大类', 1)
ws.append(['研究方向', '经费合计'] + [name for _, name, _ in COLS] + ['宝锐可及', '可及率'])
for r in matrix_rows:
    ws.append([plain(c) for c in r])
HEAD = PatternFill('solid', fgColor='1F4E79'); HF = Font(color='FFFFFF', bold=True, size=10)
for c in range(1, ws.max_column + 1):
    cell = ws.cell(row=1, column=c)
    cell.fill = HEAD; cell.font = HF; cell.alignment = Alignment(horizontal='center', wrap_text=True)
    ws.column_dimensions[get_column_letter(c)].width = 14 if c > 1 else 18
ws.freeze_panes = 'B2'
wb.save(OUT_XLSX)
print('✅ Excel')