#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""提取宝锐核酸扩增原料完整货号清单，按品类分类，输出 JSON + Excel"""
import pandas as pd
import json, os, re

SRC = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_2026年1月1日～8月20日订单数据.xlsx'
OUT_DIR = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/刘子研上手包/数据'
os.makedirs(OUT_DIR, exist_ok=True)

df = pd.read_excel(SRC, dtype=str)
sub = df[df['产品类型#'] == '核酸扩增原料'].copy()
sub['价税合计'] = pd.to_numeric(sub['价税合计'], errors='coerce')
sub['含税单价'] = pd.to_numeric(sub['含税单价'], errors='coerce')
sub['销售数量'] = pd.to_numeric(sub['销售数量'], errors='coerce')

def classify(name):
    n = str(name).upper()
    if '动物诊断' in name:
        return '动物诊断原料'
    if 'LAMP' in n or 'BST' in n or '等温' in name:
        return 'LAMP等温扩增'
    if 'RTASE' in n and 'PREMIX' not in n:
        return '逆转录酶'
    if ('RT PREMIX' in n) or ('PREMIX' in n and ('QRT' in n or 'RT-UNG' in n or 'RT UNG' in n)):
        return 'qRT-PCR预混液'
    if 'PREMIX' in n or 'MASTER MIX' in n or 'MASTERMIX' in n:
        return 'qPCR预混液'
    if 'UNG' in n:
        return 'UNG防污染'
    if 'RNASE INHIBITOR' in n or 'RNASIN' in n:
        return 'RNA酶抑制剂'
    if 'TAQ' in n:
        return 'Taq聚合酶'
    if 'POLYMERASE' in n:
        return 'DNA聚合酶'
    if 'BUFFER' in n or 'DNTPS' in n or 'DNTPS' in n:
        return '缓冲液与dNTP'
    return '其他'

sub['品类'] = sub['物料名称'].apply(classify)

# 按 货号 聚合（货号可能是 NaN，用物料编码兜底）
sub['唯一键'] = sub['货号#'].fillna(sub['物料编码'])
sub['唯一键'] = sub['唯一键'].fillna(sub['物料名称'])

rows = []
for key, grp in sub.groupby('唯一键'):
    name = grp['物料名称'].dropna().iloc[0]
    cat = grp['品类'].iloc[0]
    unit = grp['销售单位'].dropna().iloc[0] if grp['销售单位'].dropna().any() else ''
    price = grp['含税单价'].dropna()
    r = {
        '货号': str(key),
        '产品名': str(name),
        '品类': cat,
        '单位': str(unit),
        '订单数': int(grp['单据编号'].nunique()),
        '客户数': int(grp['客户'].nunique()),
        '2026价税合计': round(float(grp['价税合计'].sum()), 0),
        '含税单价主价': round(float(price.median()), 2) if len(price) else None,
        '含税单价区间': [round(float(price.min()),2), round(float(price.max()),2)] if len(price) else [],
    }
    rows.append(r)

rows.sort(key=lambda x: (-x['2026价税合计']))

# 品类统计
cat_stat = {}
for r in rows:
    c = r['品类']
    cat_stat.setdefault(c, {'货号数':0, '金额':0, '订单数':0})
    cat_stat[c]['货号数'] += 1
    cat_stat[c]['金额'] += r['2026价税合计']
    cat_stat[c]['订单数'] += r['订单数']

print('=== 品类统计（按货号）===')
for c, s in sorted(cat_stat.items(), key=lambda x: -x[1]['金额']):
    print(f"{c:<12} 货号{s['货号数']:>5}  金额{s['金额']:>12.0f}  订单{s['订单数']:>6}")

# 输出 JSON
json_path = os.path.join(OUT_DIR, '核酸扩增原料货号清单.json')
with open(json_path, 'w', encoding='utf-8') as fp:
    json.dump({'generated_at': '2026-08-29', 'source': '销售订单_2026年1月1日～8月20日订单数据.xlsx',
               'total_货号': len(rows), '品类统计': cat_stat, '货号': rows}, fp, ensure_ascii=False, indent=1)

# 输出 Excel（多 Sheet：全部 + 按品类）
excel_path = os.path.join(OUT_DIR, '核酸扩增原料货号清单.xlsx')
dfr = pd.DataFrame(rows)
with pd.ExcelWriter(excel_path, engine='openpyxl') as w:
    dfr.to_excel(w, sheet_name='全部货号', index=False)
    for c in dfr['品类'].unique():
        dfr[dfr['品类'] == c].to_excel(w, sheet_name=c, index=False)

print(f'\n总货号数: {len(rows)}')
print(f'JSON: {json_path}')
print(f'Excel: {excel_path}')