#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""BR1C103 2026-08 至 2026-11-09 测试单与反馈汇总。"""
import json, os, re, collections
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
OUT = os.path.join(ROOT, '销售分析/BR1C103_测试单与反馈汇总_202608-20261109.xlsx')
LOGO = '/Users/liuxinyuan/work/公司资料/logo_transparent.png'
SKU = 'BR1C103'
START = '2026-08-01'
END = '2026-11-09'

NAVY='0B2A4A'; BLUE='1F6FEB'; PALE='F3F7FC'; LINE='D9E2EE'; GREY='5B6B7F'; RED='C00000'; AMBER='B26A00'; OCT='FFF4D6'
thin=Side(style='thin',color=LINE); BORDER=Border(left=thin,right=thin,top=thin,bottom=thin)


def f(size=10,bold=False,color='1F2937'):
    return Font(name='宋体',size=size,bold=bold,color=color)

def fill(c): return PatternFill('solid',fgColor=c)

def title(ws,text,sub=None,ncol=10):
    ws.merge_cells(start_row=1,start_column=1,end_row=1,end_column=ncol)
    c=ws.cell(1,1,text); c.font=f(17,True,NAVY); c.alignment=Alignment(vertical='center')
    ws.row_dimensions[1].height=30
    if sub:
        ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=ncol)
        c=ws.cell(2,1,sub); c.font=f(9,False,GREY); c.alignment=Alignment(vertical='center',wrap_text=True)
        ws.row_dimensions[2].height=34

def header(ws,row,vals,widths=None,height=34):
    for j,v in enumerate(vals,1):
        c=ws.cell(row,j,v); c.font=f(8.8,True,'FFFFFF'); c.fill=fill(NAVY); c.border=BORDER
        c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True)
    ws.row_dimensions[row].height=height
    if widths:
        for i,w in enumerate(widths,1): ws.column_dimensions[get_column_letter(i)].width=w

def body(ws,row,vals,height=22,fill_color=None,bold=False):
    for j,v in enumerate(vals,1):
        c=ws.cell(row,j,v); c.font=f(9,bold,'1F2937'); c.border=BORDER
        c.alignment=Alignment(horizontal='center' if j not in (3,7,8,10) else 'left',vertical='center',wrap_text=True)
        if fill_color: c.fill=fill(fill_color)
    ws.row_dimensions[row].height=height

def note(ws,row,text,ncol=10,color=GREY,height=30):
    ws.merge_cells(start_row=row,start_column=1,end_row=row,end_column=ncol)
    c=ws.cell(row,1,text); c.font=f(8.5,False,color); c.alignment=Alignment(vertical='top',wrap_text=True)
    ws.row_dimensions[row].height=height

# ---------- load test orders ----------
orders=list(json.load(open(os.path.join(ROOT,'数据/orders_detail_2026.json'),encoding='utf-8'))['orders'].values())
test_orders={}
for o in orders:
    if o.get('date','')<START or o.get('date','')>END: continue
    if not o.get('is_test'): continue
    ls=[l for l in o.get('lines',[]) if SKU in (l.get('sku') or '')]
    if not ls: continue
    test_orders[o['no']]=o
trows=[]
for o in test_orders.values():
    ls=[l for l in o.get('lines',[]) if SKU in (l.get('sku') or '')]
    trows.append({
        'date':o['date'],'no':o['no'],'customer':o['customer'],'sales':o.get('sales',''),
        'qty':sum(float(l.get('qty') or 0) for l in ls),
        'skus':', '.join(sorted(set(l.get('sku') or '' for l in ls))),
        'name':ls[0].get('name') or '','status':o.get('status',''),'type':o.get('type','')
    })
trows.sort(key=lambda x:(x['date'],x['no']))
assert len(trows)==87, len(trows)

# ---------- activities ----------
items=json.load(open(os.path.join(ROOT,'数据/daily_activities_mcp.json'),encoding='utf-8'))['items']
acts=[]
for x in items:
    dt=str(x.get('start_time',''))
    if dt<START or dt>END: continue
    text=str(x.get('field_b99055') or '')
    if not re.search(r'BR1C103|VellixClon',text,re.I): continue
    cust=x.get('field_5f20fc') or ''
    if isinstance(cust,dict): cust=cust.get('label') or ''
    owner=(x.get('owner') or {}).get('name_cn') or ''
    acts.append({'date':dt,'owner':owner,'customer':str(cust),'name':str(x.get('name') or ''),'text':text})
acts.sort(key=lambda x:(x['date'],x['customer']))
assert len(acts)==45, len(acts)
# 测试相关跟进：只取明确“送测/同意测试/主动申请测试”的记录，排除明确拒绝或无测试意愿
SEL_PAT = re.compile(r'建议客户更换BR1C103|准备发BR1C103|下周送一份BR1C103|同意测试|今日客户将开展BR1C103|愿意试用|要了测试装|主动申请测试')
SEL_KEYS = {
    ('2026-09-09', '王诗香', '中山大学-广东'),
    ('2026-09-10', '袁殿果', '温州医科大学-浙江'),
    ('2026-09-11', '叶信福', '浙江农林大学-浙江'),
    ('2026-09-16', '王诗香', '华南农业大学-广东'),
    ('2026-09-17', '王诗香', '华南农业大学-广东'),
    ('2026-09-23', '王诗香', '华南农业大学-广东'),
    ('2026-10-26', '侯海瑜', '浙江大学-浙江'),
    ('2026-11-02', '王诗香', '华南农业大学-广东'),
}
sel = [a for a in acts if (a['date'], a['owner'], a['customer']) in SEL_KEYS and SEL_PAT.search(a['text'])]

wb=Workbook(); ws=wb.active; ws.title='00_汇总'
ws.sheet_view.showGridLines=False
for c in range(1,11): ws.column_dimensions[get_column_letter(c)].width=14
title(ws,'BR1C103 测试单与反馈汇总',f'2026年8月1日—2026年11月9日｜测试单=已审核测试销售订单；客户=ERP客户名称；反馈=测试反馈标注库正式记录',10)
if os.path.exists(LOGO):
    img=Image(LOGO); img.width=150; img.height=51; ws.add_image(img,'A4')
# KPI cards
cards=[('A6:B8','测试单','87 单'),('C6:D8','测试客户','18 家'),('E6:F8','测试装数量','90 T'),('G6:H8','正式反馈','0 条')]
for rng,label,value in cards:
    ws.merge_cells(rng); c=ws[rng.split(':')[0]]; c.value=f'{label}\n{value}'
    c.font=f(13,True,'FFFFFF'); c.fill=fill(NAVY if label!='正式反馈' else RED)
    c.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True)
    for row in ws[rng]:
        for cell in row: cell.fill=fill(NAVY if label!='正式反馈' else RED)
# monthly table
ws.merge_cells('A11:H11'); c=ws['A11']; c.value='月度测试单'; c.font=f(11,True,NAVY); c.fill=fill('E8F1FC'); c.alignment=Alignment(vertical='center')
header(ws,12,['月份','测试单数','测试客户数','测试装数量(T)','正式反馈数','活动提及数','结果反馈数','说明'],[10,12,12,14,12,12,12,30])
months=[f'2026-{m:02d}' for m in range(8,12)]
for i,m in enumerate(months,13):
    rr=[x for x in trows if x['date'].startswith(m)]
    aa=[a for a in acts if a['date'].startswith(m)]
    body(ws,i,[f'{int(m[5:7])}月',len(rr),len(set(x['customer'] for x in rr)),sum(x['qty'] for x in rr),0,len(aa),0,
               '8月无测试单' if not rr else ('10月为测试高峰' if m=='2026-10' else '')],height=22)
body(ws,17,['合计',len(trows),len(set(x['customer'] for x in trows)),sum(x['qty'] for x in trows),0,len(acts),0,'正式结果反馈为0'],height=24)
for col in range(1,9): ws.cell(17,col).font=f(9.5,True,NAVY); ws.cell(17,col).fill=fill('E8F1FC')

# chart
ch=BarChart(); ch.type='col'; ch.style=10; ch.title='月度测试单数'; ch.y_axis.title='测试单数'; ch.x_axis.title='月份'
data=Reference(ws,min_col=2,min_row=12,max_row=16)
cats=Reference(ws,min_col=1,min_row=13,max_row=16)
ch.add_data(data,titles_from_data=True); ch.set_categories(cats); ch.height=6.2; ch.width=11
ws.add_chart(ch,'J11')

# customer summary
ws.merge_cells('A19:H19'); c=ws['A19']; c.value='客户分布（按测试单数降序）'; c.font=f(11,True,NAVY); c.fill=fill('E8F1FC'); c.alignment=Alignment(vertical='center')
header(ws,20,['客户','测试单数','测试装数量(T)','销售员','首次测试','最近测试','正式反馈','活动提及'],[28,10,14,10,11,11,10,10])
cust=collections.defaultdict(lambda:{'n':0,'qty':0,'sales':set(),'first':'9999','last':'','acts':0})
for x in trows:
    d=cust[x['customer']]; d['n']+=1; d['qty']+=x['qty']; d['sales'].add(x['sales']); d['first']=min(d['first'],x['date']); d['last']=max(d['last'],x['date'])
for a in acts:
    # activity customer labels often include -广东/-浙江; normalize contains
    for name in cust:
        if name in a['customer'] or a['customer'] in name:
            cust[name]['acts']+=1
r=21
for name,d in sorted(cust.items(),key=lambda kv:(-kv[1]['n'],-kv[1]['qty'])):
    body(ws,r,[name,d['n'],d['qty'],'、'.join(sorted(d['sales'])),d['first'],d['last'],0,d['acts']],height=22)
    r+=1
# notes
note(ws,r+1,'测试单：8月0、9月26、10月53、11月8（截至11/9）；87单中BR1C103-00 86单、BR1C103-01 1单。',10,height=28)
note(ws,r+2,'正式反馈：测试反馈标注库（数据/测试反馈_全量标注_修正版.xlsx）中BR1C103记录为0；test_feedback.json、test_ledger.json同样无BR1C103正式反馈。',10,color=RED,height=34)
note(ws,r+3,f'飞书活动提及45条，其中测试意向/送测/已安排共{len(sel)}条；均未记录BR1C103测试结果，故“结果反馈”按0计。',10,color=AMBER,height=30)

# detail sheet
ws=wb.create_sheet('01_测试单明细')
ws.sheet_view.showGridLines=False
title(ws,'BR1C103 测试单明细',f'{START} 至 {END}；按测试日期排序；共{len(trows)}单。',10)
header(ws,4,['#','测试日期','订单号','客户','销售员','货号','产品名称','数量(T)','订单状态','订单类型'],[5,11,18,30,10,14,46,9,10,14])
for i,x in enumerate(trows,1):
    row=5+i-1
    body(ws,row,[i,x['date'],x['no'],x['customer'],x['sales'],x['skus'],x['name'],x['qty'],x['status'],x['type']],height=22)
ws.freeze_panes='A5'; ws.auto_filter.ref=f'A4:J{4+len(trows)}'

# selected feedback sheet
ws=wb.create_sheet('02_测试相关活动')
ws.sheet_view.showGridLines=False
title(ws,'BR1C103 测试相关活动记录',f'飞书活动提及共{len(acts)}条，本页列测试意向/送测/已安排{len(sel)}条；均无测试结果数据。',10)
header(ws,4,['日期','销售员','客户','活动标题','活动内容','类型'],[11,10,26,18,90,16])
for i,a in enumerate(sel,1):
    typ='测试安排/送测' if a['date']!='2026-10-15' else '过程反馈（无结果数据）'
    body(ws,5+i-1,[a['date'],a['owner'],a['customer'],a['name'],a['text'],typ],height=48)
    ws.cell(5+i-1,5).alignment=Alignment(horizontal='left',vertical='top',wrap_text=True)
ws.freeze_panes='A5'; ws.auto_filter.ref=f'A4:F{4+len(sel)}'

# source sheet
ws=wb.create_sheet('03_口径与来源')
ws.sheet_view.showGridLines=False
title(ws,'口径与数据来源','本表只读源数据，不改写源文件。',3)
header(ws,4,['项目','说明','来源'],[22,72,32])
rows=[
 ('统计期间','2026-08-01 至 2026-11-09；订单数据截至11/9。','ERP订单导出'),
 ('测试单口径','测试销售订单、已审核、非标准销售订单；按订单号去重。','数据/orders_detail_2026.json'),
 ('测试客户','按ERP客户名称去重，共18家。','数据/orders_detail_2026.json'),
 ('测试装数量','BR1C103各订单行数量合计90T；不是销售金额。','数据/orders_detail_2026.json'),
 ('正式反馈','测试反馈标注库中产品编码含BR1C103的记录为0条。','数据/测试反馈_全量标注_修正版.xlsx'),
 ('辅助反馈源','test_feedback.json、test_ledger.json均无BR1C103记录。','数据/test_feedback.json、数据/test_ledger.json'),
 ('活动提及', f'飞书活动正文提及BR1C103/VellixClon共45条；多数为推广、竞品或无需求，测试意向/送测/已安排{len(sel)}条。', '数据/daily_activities_mcp.json'),
 ('结论口径','正式测试反馈0条，测试单87单；不能把活动提及当作测试结果反馈。','本表'),
]
for i,(a,b,c) in enumerate(rows,5):
    body(ws,i,[a,b,c],height=36)
    ws.cell(i,2).alignment=Alignment(horizontal='left',vertical='center',wrap_text=True)
    ws.cell(i,3).alignment=Alignment(horizontal='left',vertical='center',wrap_text=True)

for ws in wb.worksheets:
    ws.page_setup.orientation='landscape'; ws.page_setup.fitToWidth=1; ws.page_setup.fitToHeight=0
    ws.sheet_properties.pageSetUpPr.fitToPage=True
    ws.oddFooter.center.text='宝锐生物 BIORI BIOTECH｜守护生命健康，缔造美好生活'
    ws.oddFooter.center.size=8; ws.oddFooter.center.color=GREY

os.makedirs(os.path.dirname(OUT),exist_ok=True)
tmp=OUT+'.tmp.xlsx'; wb.save(tmp); os.replace(tmp,OUT)
print(OUT)
print('test',len(trows),'qty',sum(x['qty'] for x in trows),'customers',len(cust),'acts',len(acts),'selected',len(sel))