#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026年新开单课题组统计：广东、浙江各一张表。"""
import json, os, collections
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.drawing.image import Image

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '数据/2026首单客户_浙广课题组_20261108.json')
OUT = os.path.join(ROOT, '销售分析/2026新开单课题组_广东浙江_20261109.xlsx')
LOGO = '/Users/liuxinyuan/work/公司资料/logo_transparent.png'

d = json.load(open(SRC, encoding='utf-8'))
rows = d['all_first_deal']
rows = [x for x in rows if str(x.get('first_deal', '')).startswith('2026')]
assert len(rows) == 160, len(rows)

NAVY = '0B2A4A'
BLUE = '1F6FEB'
PALE = 'F3F7FC'
LINE = 'D9E2EE'
GREY = '5B6B7F'
RED = 'C00000'
OCT = 'FFF4D6'
FONT = '宋体'
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=FONT, size=size, bold=bold, color=color)


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


def title(ws, text, sub=None, ncol=15):
    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 = 30


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):
    for j, v in enumerate(vals, 1):
        c = ws.cell(row, j, v)
        c.font = f(9, False, '1F2937')
        c.border = BORDER
        c.alignment = Alignment(horizontal='center' if j not in (2, 9, 11) 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=15, 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


def short(x):
    return x if x not in (None, '') else '—'


def region_stats(region):
    rr = [x for x in rows if x.get('region') == region]
    months = collections.defaultdict(lambda: [0, 0.0])
    for x in rr:
        m = str(x['first_deal'])[:7]
        months[m][0] += 1
        months[m][1] += float(x.get('amt_p26') or 0)
    return rr, months


# 新客户开发数量（首次测试单）——2026年1-10月官方口径：229 / 296 / 525
DEV_MONTHLY = {
    '2026-01': (35, 44), '2026-02': (6, 3), '2026-03': (61, 74),
    '2026-04': (32, 28), '2026-05': (7, 22), '2026-06': (19, 26),
    '2026-07': (31, 19), '2026-08': (12, 31), '2026-09': (20, 36),
    '2026-10': (6, 13),
}
DEV_TOTAL = {'浙江': 229, '广东': 296, '合计': 525}
# 新开单（首次有金额成交）逐月，从160家明细计算
ORDER_MONTHLY = collections.defaultdict(lambda: collections.Counter())
for x in rows:
    ORDER_MONTHLY[str(x['first_deal'])[:7]][x['region']] += 1


wb = Workbook()

# ---------------- 00 汇总 ----------------
ws = wb.active
ws.title = '00_汇总'
ws.sheet_view.showGridLines = False
for c in range(1, 8):
    ws.column_dimensions[get_column_letter(c)].width = 16
for r in range(1, 26):
    ws.row_dimensions[r].height = 24
title(ws, '2026年新开单课题组统计｜广东 / 浙江',
      '首单=2026年首次有金额成交的 PI 课题组；不含仅有测试单/赠品、无金额的课题组；金额=课题组2026累计含税销售额。数据截至2026-10-31。', 7)
ws.merge_cells('A3:G3')
c = ws['A3']
c.value = f'同文件另含：新客户开发数量（首次测试单）{DEV_TOTAL["合计"]} 家｜浙江 {DEV_TOTAL["浙江"]}｜广东 {DEV_TOTAL["广东"]}｜10月 19 家（见 01_新客户开发数量）'
c.font = f(9.5, True, BLUE)
c.fill = fill('E8F1FC')
c.alignment = Alignment(vertical='center')
ws.row_dimensions[3].height = 26
if os.path.exists(LOGO):
    img = Image(LOGO)
    img.width = 160
    img.height = 54
    ws.add_image(img, 'A4')

# KPI cards
cards = [
    ('A6:B8', '合计', f'{len(rows)} 家'),
    ('C6:D8', '浙江', f'{len([x for x in rows if x["region"]=="浙江"])} 家'),
    ('E6:F8', '广东', f'{len([x for x in rows if x["region"]=="广东"])} 家'),
    ('G6:G8', '10月新开', f'{len([x for x in rows if x["first_deal"].startswith("2026-10")])} 家'),
]
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 != '10月新开' else BLUE)
    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 != '10月新开' else BLUE)

ws.merge_cells('A11:G11')
c = ws['A11']
c.value = '月度新开单课题组'
c.font = f(11, True, NAVY)
c.fill = fill('E8F1FC')
c.alignment = Alignment(vertical='center')
ws.row_dimensions[11].height = 24
header(ws, 12, ['月份', '浙江家数', '浙江销售额(元)', '广东家数', '广东销售额(元)', '合计家数', '合计销售额(元)'],
       [12, 14, 16, 14, 16, 12, 16])
zr, zm = region_stats('浙江')
gr, gm = region_stats('广东')
for i, m in enumerate([f'2026-{x:02d}' for x in range(1, 11)], 13):
    z = zm.get(m, [0, 0.0])
    g = gm.get(m, [0, 0.0])
    body(ws, i, [f'{int(m[5:7])}月', z[0], z[1], g[0], g[1], z[0]+g[0], z[1]+g[1]], height=23)
    for col in (3, 5, 7):
        ws.cell(i, col).number_format = '#,##0.00'
    if m == '2026-10':
        for col in range(1, 8):
            ws.cell(i, col).fill = fill(OCT)
# total
zsum = sum(x['amt_p26'] for x in zr)
gsum = sum(x['amt_p26'] for x in gr)
body(ws, 23, ['合计', len(zr), zsum, len(gr), gsum, len(rows), zsum+gsum], height=24)
for col in range(1, 8):
    ws.cell(23, col).font = f(9.5, True, NAVY)
    ws.cell(23, col).fill = fill('E8F1FC')
for col in (3, 5, 7):
    ws.cell(23, col).number_format = '#,##0.00'
note(ws, 25, '口径：160家=2026年1-10月首次有金额成交的课题组；525家=新客户开发数量（首次测试单），两者不是同一指标。10月新开6家（浙江4、广东2）。', 7, height=34)
note(ws, 26, '数据源：数据/2026首单客户_浙广课题组_20261108.json；如按2026-11-05后最新订单重算，首单家数会变化，需另出刷新版。', 7, color=GREY, height=30)

# ---------------- 01 新客户开发数量 ----------------
ws = wb.create_sheet('01_新客户开发数量')
ws.sheet_view.showGridLines = False
for c in range(1, 9):
    ws.column_dimensions[get_column_letter(c)].width = 16
title(ws, '2026年新客户开发数量（首次测试单）｜广东 / 浙江',
      '新客户开发数量=新课题组首次发出测试单；与“新开单课题组”（首次有金额成交）分开统计。数据截至2026-10-31。', 8)
# KPI
for rng, label, value, color in [
    ('A4:B6', '合计', '525 家', NAVY),
    ('C4:D6', '浙江', '229 家', BLUE),
    ('E4:F6', '广东', '296 家', BLUE),
    ('G4:H6', '10月开发', '19 家', 'B26A00'),
]:
    ws.merge_cells(rng)
    c = ws[rng.split(':')[0]]
    c.value = f'{label}\n{value}'
    c.font = f(14, True, 'FFFFFF')
    c.fill = fill(color)
    c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    for row in ws[rng]:
        for cell in row:
            cell.fill = fill(color)
header(ws, 9, ['月份', '浙江开发', '广东开发', '合计开发', '浙江新开单', '广东新开单', '合计新开单', '开发→开单转化率'],
       [10, 14, 14, 14, 14, 14, 14, 18])
tot_dev = tot_order = 0
for i, m in enumerate([f'2026-{x:02d}' for x in range(1, 11)], 10):
    zdev, gdev = DEV_MONTHLY[m]
    zo = ORDER_MONTHLY[m]['浙江']
    go = ORDER_MONTHLY[m]['广东']
    dev = zdev + gdev
    order = zo + go
    tot_dev += dev
    tot_order += order
    body(ws, i, [f'{int(m[5:7])}月', zdev, gdev, dev, zo, go, order, order/dev if dev else '—'], height=23)
    ws.cell(i, 8).number_format = '0.0%'
    if m == '2026-10':
        for col in range(1, 9):
            ws.cell(i, col).fill = fill(OCT)
body(ws, 20, ['合计', DEV_TOTAL['浙江'], DEV_TOTAL['广东'], DEV_TOTAL['合计'],
              len([x for x in rows if x['region']=='浙江']), len([x for x in rows if x['region']=='广东']),
              len(rows), len(rows)/DEV_TOTAL['合计']], height=24)
for col in range(1, 9):
    ws.cell(20, col).font = f(9.5, True, NAVY)
    ws.cell(20, col).fill = fill('E8F1FC')
ws.cell(20, 8).number_format = '0.0%'
note(ws, 22, '口径：新客户开发数量=新课题组首次测试单（KPI 20%指标），不是“新开单”；新开单=首次有金额成交。525家中160家已转为首单，转化率30.5%。', 8, height=34)
note(ws, 23, '分月数据：2026年1-10月；浙江229、广东296、合计525；10月开发19家（浙江6、广东13）。', 8, color=GREY, height=24)

# ---------------- region sheets ----------------
hdr = ['#', 'PI课题组', '销售员', '首单日期', '首单月份', '2026累计销售额(元)', '订单数', '已付款订单',
       '客户单位', '主销货号', '主销货号名称', '主销金额(元)', '竞对(PI级)', '竞对(客户级)', 'PI活动条数']
widths = [5, 20, 9, 11, 9, 16, 7, 9, 38, 14, 36, 12, 16, 20, 9]

for region in ['浙江', '广东']:
    rr, mm = region_stats(region)
    ws = wb.create_sheet(f'{region}_新开单课题组')
    ws.sheet_view.showGridLines = False
    title(ws, f'{region}｜2026年新开单课题组（{len(rr)}家）',
          '按首单日期倒序；黄色底为2026年10月新开；金额为课题组2026累计含税销售额。', 15)
    # summary line
    total = sum(x['amt_p26'] for x in rr)
    oct_n = sum(1 for x in rr if x['first_deal'].startswith('2026-10'))
    oct_amt = sum(x['amt_p26'] for x in rr if x['first_deal'].startswith('2026-10'))
    ws.merge_cells('A4:O4')
    c = ws['A4']
    c.value = f'合计：{len(rr)} 家｜2026累计销售 {total:,.2f} 元｜平均 {total/len(rr):,.2f} 元/家｜10月新开 {oct_n} 家、{oct_amt:,.2f} 元｜新客户开发数量 {DEV_TOTAL[region]} 家'
    c.font = f(9.5, True, NAVY)
    c.fill = fill('E8F1FC')
    c.alignment = Alignment(vertical='center')
    ws.row_dimensions[4].height = 26
    header(ws, 6, hdr, widths)
    # sort by first_deal desc, amount desc
    srr = sorted(rr, key=lambda x: (x['first_deal'], x['amt_p26']), reverse=True)
    r = 7
    for i, x in enumerate(srr, 1):
        customers = '；'.join(x.get('customers') or [])
        note_owner = '；'.join(x.get('owner_merged_from') or [])
        vals = [i, x['name'], short(x.get('sales')), x['first_deal'], str(x['first_deal'])[5:7] + '月',
                round(float(x.get('amt_p26') or 0), 2), x.get('orders'), x.get('orders_paid'),
                customers, short(x.get('main_sku')), short(x.get('main_sku_name')),
                round(float(x.get('main_sku_amt') or 0), 2), short(x.get('competitor_pi')),
                short(x.get('competitor_cust')), x.get('pi_activity_count')]
        body(ws, r, vals, height=22, fill_color=OCT if str(x['first_deal']).startswith('2026-10') else None)
        for col in (6, 12):
            ws.cell(r, col).number_format = '#,##0.00'
        # 备注：原归属人并入客户单位列尾，不新增列
        if note_owner:
            ws.cell(r, 9).value = (customers + '｜原归属：' + note_owner).strip('｜')
        r += 1
    # total row
    body(ws, r, ['合计', '', '', '', '', total, sum(x.get('orders') or 0 for x in rr),
                 sum(x.get('orders_paid') or 0 for x in rr), '', '', '', '', '', '', ''], height=24)
    for col in range(1, 16):
        ws.cell(r, col).font = f(9.5, True, NAVY)
        ws.cell(r, col).fill = fill('E8F1FC')
    ws.cell(r, 6).number_format = '#,##0.00'
    ws.freeze_panes = 'A7'
    ws.auto_filter.ref = f'A6:O{r-1}'
    note(ws, r+2, '注：首单日期为2026年首次有金额成交日期；客户单位为该课题组对应的学校/医院抬头；主销货号为2026累计销售额最高的货号。', 15, height=28)

# page setup
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 = '5B6B7F'

os.makedirs(os.path.dirname(OUT), exist_ok=True)
tmp = OUT + '.tmp.xlsx'
wb.save(tmp)
os.replace(tmp, OUT)
print(OUT)
print('ZJ', len(zr), sum(x['amt_p26'] for x in zr), 'GD', len(gr), sum(x['amt_p26'] for x in gr), 'TOTAL', len(rows))