#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生命科学部 Q3 汇报 · 产品需求汇总（含两位经理）

来源：2026 年三季度生命科学销售部 8 份汇报的「客户需求（优化方向）」「需求总结」页
输出：
  数据/科研Q3产品需求汇总.json
  销售分析/生命科学_Q3汇报产品需求汇总_2026-10-17.xlsx（2 Sheet）
"""
import io
import json
import os

import openpyxl
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
D = W + '/数据/'
OUT_XLSX = W + '/销售分析/生命科学_Q3汇报产品需求汇总_2026-10-17.xlsx'

# (品类, 需求, 提出人, 货号/系列, 需求量 / 客户范围, 性质, 优先级)
ROWS = [
    ('逆转录 / RT-qPCR', '一步法预混反转试剂（反转后可直接做 PCR）', '王诗香', '—（新品）', '华农兽医院 50%', '新品', 'P0'),
    ('逆转录 / RT-qPCR', '反转试剂后续做 PCR 效果增强', '王诗香', 'BR1D303', '—', '性能改进', 'P0'),
    ('逆转录 / RT-qPCR', '带颜色指示的逆转录（有颜色的逆转录）', '王诗香、陈彩红、韩远怀', 'BR1D303', '追市场扫描', '性能改进', 'P1'),
    ('逆转录 / RT-qPCR', '处理微量、低丰度样品', '陈彩红', 'RT-qPCR 系列', '—', '性能改进', 'P1'),
    ('逆转录 / RT-qPCR', '稳定性强、批间差异小、减少重复失败', '陈彩红', 'RT-qPCR 系列', '—', '性能改进', 'P0'),
    ('逆转录 / RT-qPCR', 'ROX 浓度适配不同仪器（罗氏、爱博泰克等）', '黄建华、韩远怀', 'BR1E102', '—', '性能改进', 'P0'),
    ('高保真 / PCR', '高保真酶不稳定、有时一直 P 不出来', '侯海瑜', 'BR3M121', '—', '性能改进', 'P0'),
    ('高保真 / PCR', '增强粗模板抗抑制能力', '叶信福', 'BR3M121', '—', '性能改进', 'P0'),
    ('高保真 / PCR', '通用退火高保真酶', '袁殿果', '—（新品）', '10ml / 1283 元', '新品', 'P1'),
    ('高保真 / PCR', '点突变相关实验性能优化', '王诗香', 'BR3M121', '—', '性能改进', 'P2'),
    ('连接 / 克隆', 'T4 连接酶连接效率', '黄建华', 'BR3P301', '—', '性能改进', 'P1'),
    ('连接 / 克隆', 'Tn5 酶打断效率', '黄建华', 'BR3P201', '预估需求 20 万', '性能改进', 'P0'),
    ('连接 / 克隆', '提升反向 PCR 后阳性率', '王诗香', 'BR1C101', '—', '性能改进', 'P1'),
    ('提取（RNA / DNA / 质粒）', '适配动物用的 RNA 柱式提取试剂盒', '王诗香、侯海瑜、袁殿果', '—（新品）', '华农兽医+动科；2–3 个课题组；2 盒', '新品', 'P0'),
    ('提取（RNA / DNA / 质粒）', '通用基因组 DNA 提取试剂盒', '王诗香', '—（新品）', '70% 市场', '新品', 'P0'),
    ('提取（RNA / DNA / 质粒）', '小提 A260/A230 提升（对标 omega）', '王诗香', 'BR2D101', '—', '性能改进', 'P1'),
    ('提取（RNA / DNA / 质粒）', '质粒提取所有组分可常温储存（RNase A）', '袁殿果', '质粒提取系列', '—', '配方改进', 'P2'),
    ('提取（RNA / DNA / 质粒）', '裂解稳定性', '叶信福', 'BR1A503', '—', '性能改进', 'P1'),
    ('提取（RNA / DNA / 质粒）', '组织支扩试剂盒裂解液可单卖', '袁殿果', 'BR1A502', '—', '包装/销售单元', 'P2'),
    ('蛋白 / WB', '蛋白 marker 价格', '叶信福', 'BR4C303', '—', '价格', 'P1'),
    ('蛋白 / WB', '一步法制胶不稳定、有时需放隔夜', '侯海瑜', 'BR4C333', '—', '性能改进', 'P0'),
    ('蛋白 / WB', '上下层胶分界明显、提高美观度', '袁殿果', '一步法制胶', '—', '性能改进', 'P2'),
    ('蛋白 / WB', 'fg 级曝光液', '袁殿果', '—（新品）', '100ml', '新品', 'P2'),
    ('蛋白 / WB', '梯度胶整合', '韩远怀', '—（整合）', '—', '产品整合', 'P2'),
    ('DNA marker', 'DNA marker 5000 / 10000 / 15000', '王诗香', '—（新品）', '80% 市场', '新品', 'P0'),
    ('LAMP', 'LAMP 试剂持续挖掘 + 专题讲座（PI 主导）', '吴云', 'LAMP 系列', '需求体量不小、价格空间大', '市场/推广', 'P1'),
    ('文档 / 说明书', '试剂盒组分标签优化、命名更直观', '叶信福', 'BR2B202', '—', '文档/包装', 'P2'),
    ('文档 / 说明书', '直扩裂解液与高保真体系不匹配 → 说明书标注或加量', '侯海瑜', 'BR1A503', '—', '文档/配方', 'P1'),
]

HEAD = ['品类', '客户需求 / 优化方向', '提出人', '涉及货号', '需求量 / 客户范围', '性质', '优先级']

json.dump({'generated': '2026-10-17',
           'source': '2026 年三季度生命科学销售部 8 份汇报（黄建华/王诗香/陈彩红/叶信福/侯海瑜/袁殿果/吴云/韩远怀）',
           'note': '吴云汇报无「客户需求（优化方向）」专节，其产品相关诉求按汇报原文摘录（LAMP、新品推广）',
           'count': len(ROWS),
           'items': [dict(zip(HEAD, r)) for r in ROWS]},
          io.open(D + '科研Q3产品需求汇总.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)

wb = openpyxl.Workbook()
ws = wb.active
ws.title = '按品类'
thin = Side(style='thin', color='D9DEE8')
bd = Border(left=thin, right=thin, top=thin, bottom=thin)
hdr_fill = PatternFill('solid', fgColor='1F4E79')
pf = {'P0': PatternFill('solid', fgColor='FDE8E6'), 'P1': PatternFill('solid', fgColor='E8F0FB'), 'P2': PatternFill('solid', fgColor='EEF2F8')}
ws.append(HEAD)
for c in ws[1]:
    c.font = Font(bold=True, color='FFFFFF', size=11)
    c.fill = hdr_fill
    c.alignment = Alignment(horizontal='center', vertical='center')
    c.border = bd
for r in ROWS:
    ws.append(list(r))
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    for c in row:
        c.border = bd
        c.alignment = Alignment(vertical='top', wrap_text=True)
    row[6].fill = pf.get(row[6].value, PatternFill())
    row[6].alignment = Alignment(horizontal='center', vertical='center')
widths = [22, 46, 22, 16, 30, 14, 8]
for i, w in enumerate(widths, 1):
    ws.column_dimensions[get_column_letter(i)].width = w
ws.freeze_panes = 'A2'
ws.auto_filter.ref = 'A1:G%d' % ws.max_row

# Sheet2 按人
ws2 = wb.create_sheet('按提出人')
ws2.append(['提出人', '条数', '需求（简）'])
by = {}
for r in ROWS:
    for p in r[2].replace('、', ',').split(','):
        by.setdefault(p.strip(), []).append(r[1])
for p, items in sorted(by.items(), key=lambda kv: -len(kv[1])):
    ws2.append([p, len(items), '；'.join(items)])
for c in ws2[1]:
    c.font = Font(bold=True, color='FFFFFF', size=11)
    c.fill = hdr_fill
    c.alignment = Alignment(horizontal='center')
for row in ws2.iter_rows(min_row=2, max_row=ws2.max_row):
    for c in row:
        c.border = bd
        c.alignment = Alignment(vertical='top', wrap_text=True)
ws2.column_dimensions['A'].width = 14
ws2.column_dimensions['B'].width = 8
ws2.column_dimensions['C'].width = 110

# Sheet3 说明
ws3 = wb.create_sheet('说明')
ws3['A1'] = '生命科学部 2026 Q3 汇报 · 产品需求汇总'
ws3['A1'].font = Font(bold=True, size=13, color='1F4E79')
notes = [
    '',
    '数据来源：8 份生命科学销售部 Q3 汇报的「客户需求（优化方向）」「需求总结」页，逐字提取。',
    '提出人：黄建华、王诗香、陈彩红、叶信福、侯海瑜、袁殿果、吴云（浙江经理）、韩远怀（广东经理）。',
    '说明：吴云的汇报没有「客户需求（优化方向）」专节，其产品相关诉求（LAMP 品类、新品推广）按原文摘录，已标注。',
    '优先级口径：P0 = 多人提出 或 有明确市场占比/需求量；P1 = 单人提出但影响成交；P2 = 文档/包装/单点改进。',
    '汇总时间：2026-10-17',
]
for i, n in enumerate(notes, 2):
    ws3.cell(row=i, column=1, value=n).alignment = Alignment(wrap_text=True, vertical='top')
ws3.column_dimensions['A'].width = 110

wb.save(OUT_XLSX)
print('✅ %s（%d 条需求）' % (OUT_XLSX, len(ROWS)))
from collections import Counter
print('   按优先级:', Counter(r[6] for r in ROWS).most_common())
print('   按品类:', Counter(r[0] for r in ROWS).most_common())
print('   提出人条数:', Counter([p for r in ROWS for p in r[2].replace('、', ',').split(',')]).most_common())