#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把《生命科学_20260820-20260930_按区域课题组销售员.html》按区域拆成两张表（浙江 / 广东）。

每张表自包含：标题+核心数字 → 按课题组明细（含小计）→ 按销售员明细（含合计）。
数据复用 html_lifesci_to_xlsx.py 的解析函数，避免两套口径。
"""
import importlib.util
import os

from lxml import html as LH
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
S = os.path.join(ROOT, '脚本/html_lifesci_to_xlsx.py')
OUT = os.path.join(ROOT, '销售分析/生命科学_20260820-20260930_分区域_浙江广东.xlsx')

spec = importlib.util.spec_from_file_location('m', S)
m = importlib.util.module_from_spec(spec)
spec.loader.exec_module(m)

BLUE = '0F3A8A'
HEAD = PatternFill('solid', fgColor='F0F4FB')
REG = PatternFill('solid', fgColor='E8F0FE')
TOT = PatternFill('solid', fgColor='F7F9FC')
TITLE = PatternFill('solid', fgColor='0F3A8A')
BORD = Border(bottom=Side(style='thin', color='DDE5F0'))


def main():
    doc = LH.parse(m.SRC).getroot()
    tbs = doc.xpath('//table')
    r1, r2 = m.parse1(tbs[0]), m.parse2(tbs[1])

    wb = Workbook()
    summary = {}
    for idx, region in enumerate(('浙江', '广东')):
        ws = wb.active if idx == 0 else wb.create_sheet()
        ws.title = region
        kq = [r for r in r1 if r['区域'] == region and r['类型'] == '明细']
        sp = [r for r in r2 if r['区域'] == region and r['类型'] == '明细']
        tot = next((r for r in r2 if r['区域'] == region and r['类型'] == '区域合计'), {})
        amt = sum(r['销售金额'] or 0 for r in kq)
        ws.append(['生命科学销售金额统计 · %s（2026-08-20 ~ 2026-09-30）' % region])
        ws.append(['课题组合计(元)', amt, '课题组数', len(kq),
                   '订单数', tot.get('订单数'), '销售员', len(sp)])
        ws.append([])
        ws.append(['按课题组明细'])
        ws.append(['课题组', '销售员', '销售金额(元)', '订单数', '客户数'])
        for r in kq:
            ws.append([r['课题组'], r['销售员'], r['销售金额'], r['订单数'], r['客户数']])
        src_sub = next((r['销售金额'] for r in r1 if r['区域'] == region and r['类型'] == '区域小计'), None)
        ws.append(['%s 小计（原表值）' % region, '', src_sub, tot.get('订单数'), sum(r['客户数'] or 0 for r in kq)])
        if src_sub is not None and abs(src_sub - amt) >= 0.5:
            ws.append(['注：明细逐行按元取整，明细合计 %s 与原表小计 %s 相差 %s 元（原表如此，未作调整）'
                       % (format(amt, ',.0f'), format(src_sub, ',.0f'), format(src_sub - amt, ',.0f'))])
        ws.append([])
        ws.append(['按销售员明细'])
        ws.append(['销售员', '销售金额(元)', '课题组数', '订单数'])
        for r in sp:
            ws.append([r['销售员'], r['销售金额'], r['课题组数'], r['订单数']])
        ws.append(['%s 合计（原表值）' % region, next((r['销售金额'] for r in r2 if r['区域'] == region and r['类型'] == '区域合计'), None) or sum(r['销售金额'] or 0 for r in sp),
                   sum(r['课题组数'] or 0 for r in sp), sum(r['订单数'] or 0 for r in sp)])
        summary[region] = (len(kq), amt, len(sp), tot.get('订单数'))

        for i, w in enumerate([30, 12, 14, 10, 10], 1):
            ws.column_dimensions[get_column_letter(i)].width = w
        for row in ws.iter_rows():
            for c in row:
                if isinstance(c.value, (int, float)):
                    c.number_format = '#,##0'
                    c.alignment = Alignment(horizontal='right')
                else:
                    c.alignment = Alignment(vertical='center', wrap_text=True)
        # 标题行
        ws['A1'].font = Font(bold=True, size=13, color='FFFFFF')
        ws['A1'].fill = TITLE
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=5)
        for c in ws[2]:
            c.font = Font(bold=True, color=BLUE)
        for r in (4, 8):
            ws.cell(row=r, column=1).font = Font(bold=True, color=BLUE)
            ws.cell(row=r, column=1).fill = REG
        for r in (5, 9):
            for c in ws[r]:
                if c.value:
                    c.font = Font(bold=True, color=BLUE)
                    c.fill = HEAD
        # 小计 / 合计行
        for row in ws.iter_rows():
            v = row[0].value
            if isinstance(v, str) and (v.endswith('小计') or v.endswith('合计')):
                for c in row:
                    c.fill = TOT
                    c.font = Font(bold=True, color=BLUE)
                    c.border = BORD
        ws.freeze_panes = 'A6'

    wb.save(OUT)
    print('→ %s（%.1f KB）' % (OUT, os.path.getsize(OUT) / 1024.0))
    for k, v in summary.items():
        print('   %s：课题组 %d 个 / %s 元 ｜ 销售员 %d 人 / 订单 %s' % (k, v[0], format(v[1], ',.0f'), v[2], v[3]))
    return summary


if __name__ == '__main__':
    main()