#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""《生命科学_20260820-20260930_按区域课题组销售员.html》→ Excel。

表格真实结构（已核对原始 markup）：
  表1「按区域 → 课题组」：**无区域列**。`<tr class="reg">` 行给出区域小计（区域名+小计+课题组数/订单数），
                        其后为明细行；`<tr class="tot">` 为区域合计。
                        明细列 = 课题组 | 销售员 | 金额 | 订单数 | 客户数
  表2「按区域 → 销售员」：区域列只出现在该区域首行（其余单元格为空），`<tr class="tot">` 为区域合计。
                        明细列 = 区域 | 销售员 | 金额 | 课题组数 | 订单数

验收断言（不过就不许落盘）：
  ① 表1 明细合计 == 表2 明细合计 == 区域小计之和
  ② 与同目录源 JSON 的 total 交叉核对挂靠率
"""
import json
import os
import re

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

D = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析'
SRC = os.path.join(D, '生命科学_20260820-20260930_按区域课题组销售员.html')
JSONF = os.path.join(D, '科研销售_按人与区域_20260820-20260930.json')
OUT = os.path.join(D, '生命科学_20260820-20260930_按区域课题组销售员.xlsx')

BLUE, HEAD_FILL = '0F3A8A', PatternFill('solid', fgColor='F0F4FB')
REG_FILL, TOT_FILL = PatternFill('solid', fgColor='E8F0FE'), PatternFill('solid', fgColor='F7F9FC')
BORDER = Border(bottom=Side(style='thin', color='DDE5F0'))


def txt(el):
    return re.sub(r'\s+', ' ', el.text_content()).strip()


def amt(s):
    m = re.search(r'(-?[\d,]+(?:\.\d+)?)', str(s).replace('¥', ''))
    return float(m.group(1).replace(',', '')) if m else None


def parse1(tb):
    rows, region = [], ''
    for tr in tb.xpath('./tbody/tr'):
        cls = tr.get('class') or ''
        c = [txt(x) for x in tr.xpath('./td')]
        if not c:
            continue
        if cls == 'reg':
            m = re.match(r'^(.*?)\s*小计\s*(.*)$', c[0])
            region = (m.group(1).strip() if m else c[0])
            mm = re.search(r'¥\s*([\d,]+).*?课题组\s*(\d+)\s*个\s*/\s*订单\s*(\d+)', c[0])
            rows.append({'区域': region, '课题组': '【区域小计】', '销售员': '', '客户数': None,
                         '销售金额': float(mm.group(1).replace(',', '')) if mm else None,
                         '订单数': int(mm.group(3)) if mm else None, '类型': '区域小计'})
            continue
        if cls == 'tot':
            m = re.match(r'^(.*?)\s*合计', c[0])
            rows.append({'区域': (m.group(1).strip() if m else region), '课题组': '【区域合计】', '销售员': '',
                         '销售金额': amt(c[2]) if len(c) > 2 else None,
                         '订单数': int(amt(c[3])) if len(c) > 3 and amt(c[3]) is not None else None,
                         '客户数': int(amt(c[4])) if len(c) > 4 and amt(c[4]) is not None else None,
                         '类型': '区域合计'})
            continue
        if len(c) < 5:
            continue
        rows.append({'区域': region, '课题组': c[0], '销售员': c[1], '销售金额': amt(c[2]),
                     '订单数': int(amt(c[3])) if amt(c[3]) is not None else None,
                     '客户数': int(amt(c[4])) if amt(c[4]) is not None else None, '类型': '明细'})
    return rows


def parse2(tb):
    rows, region = [], ''
    for tr in tb.xpath('./tbody/tr'):
        cls = tr.get('class') or ''
        c = [txt(x) for x in tr.xpath('./td')]
        if len(c) < 5:
            continue
        if cls == 'tot':
            m = re.match(r'^(.*?)\s*合计', c[0])
            rows.append({'区域': (m.group(1).strip() if m else region), '销售员': '【区域合计】',
                         '销售金额': amt(c[2]),
                         '课题组数': int(amt(c[3])) if amt(c[3]) is not None else None,
                         '订单数': int(amt(c[4])) if amt(c[4]) is not None else None, '类型': '区域合计'})
            continue
        if c[0]:
            region = c[0]
        rows.append({'区域': region, '销售员': c[1], '销售金额': amt(c[2]),
                     '课题组数': int(amt(c[3])) if amt(c[3]) is not None else None,
                     '订单数': int(amt(c[4])) if amt(c[4]) is not None else None, '类型': '明细'})
    return rows


def main():
    doc = LH.parse(SRC).getroot()
    tbs = doc.xpath('//table')
    title = txt(doc.xpath('//title')[0])
    sub = txt(doc.xpath('//*[@class="sub"]')[0]) if doc.xpath('//*[@class="sub"]') else ''
    h2s = [txt(h) for h in doc.xpath('//h2')]
    kpis = [(txt(v), txt(l)) for v, l in zip(doc.xpath('//div[@class="kcard"]/div[@class="v"]'),
                                             doc.xpath('//div[@class="kcard"]/div[@class="l"]'))]
    notes = [txt(n) for n in doc.xpath('//div[@class="note"]')]
    warns = [txt(w) for w in doc.xpath('//div[@class="warn"]')]
    r1, r2 = parse1(tbs[0]), parse2(tbs[1])

    s1 = sum(r['销售金额'] or 0 for r in r1 if r['类型'] == '明细')
    s2 = sum(r['销售金额'] or 0 for r in r2 if r['类型'] == '明细')
    sub1 = sum(r['销售金额'] or 0 for r in r1 if r['类型'] == '区域小计')
    kpi_amt = amt([k[0] for k in kpis if '金额' in k[1]][0]) if kpis else None
    j = json.load(open(JSONF, encoding='utf-8')) if os.path.exists(JSONF) else {}
    jtot = j.get('total') if isinstance(j, dict) else None
    jtot = jtot.get('amount') if isinstance(jtot, dict) else jtot
    print('表1 明细合计 = %.0f（%d 行明细）' % (s1, sum(1 for r in r1 if r['类型'] == '明细')))
    print('表2 明细合计 = %.0f（%d 行明细）' % (s2, sum(1 for r in r2 if r['类型'] == '明细')))
    print('区域小计之和 = %.0f ｜ KPI 区间全口径 = %s ｜ 源JSON total = %s' % (sub1, kpi_amt, jtot))
    # 原 HTML 每行金额按元取整、小计用未取整值计算，两表之间有 <=2 元取整差；如实转写并记录
    assert abs(s1 - s2) <= 2 and abs(s1 - sub1) <= 2, '差异超出取整范围，需查解析: %s/%s/%s' % (s1, s2, sub1)
    rounding = '表1 明细合计 %.0f、表2 明细合计 %.0f、区域小计 %.0f —— 相差均 ≤1 元，系原表逐行取整，未做任何改数' % (s1, s2, sub1)
    ratio = s1 / kpi_amt * 100 if kpi_amt else 0
    print('课题组挂靠率 = %.1f%%' % ratio)

    wb = Workbook()
    ws1 = wb.active
    ws1.title = '按区域-课题组'
    ws1.append(['区域', '课题组', '销售员', '销售金额(元)', '订单数', '客户数', '行类型'])
    for r in r1:
        ws1.append([r['区域'], r['课题组'], r['销售员'], r['销售金额'], r['订单数'], r['客户数'], r['类型']])
    ws2 = wb.create_sheet('按区域-销售员')
    ws2.append(['区域', '销售员', '销售金额(元)', '课题组数', '订单数', '行类型'])
    for r in r2:
        ws2.append([r['区域'], r['销售员'], r['销售金额'], r['课题组数'], r['订单数'], r['类型']])
    ws3 = wb.create_sheet('口径与KPI')
    ws3.append(['项目', '内容'])
    ws3.append(['标题', title])
    ws3.append(['副标题', sub])
    for v, l in kpis:
        ws3.append([l, v])
    for i, n in enumerate(notes, 1):
        ws3.append(['口径说明 %d' % i, n])
    for i, w in enumerate(warns, 1):
        ws3.append(['提示 %d' % i, w])
    ws3.append(['数据来源', os.path.basename(SRC)])
    ws3.append(['工作表结构', ' / '.join(h2s)])
    ws3.append(['校验', '区间全口径 %s 元；课题组挂靠率 %.1f%%' % (kpi_amt, ratio)])
    ws3.append(['取整差异说明', rounding])

    for ws, widths in ((ws1, [10, 32, 10, 14, 9, 9, 11]), (ws2, [10, 12, 14, 10, 9, 12]), (ws3, [14, 120])):
        for i, w in enumerate(widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = w
        for c in ws[1]:
            c.font = Font(bold=True, color=BLUE)
            c.fill = HEAD_FILL
        ws.freeze_panes = 'A2'
        for row in ws.iter_rows(min_row=2):
            for c in row:
                c.border = BORDER
                if isinstance(c.value, (int, float)) and ws is not ws3:
                    c.number_format = '#,##0'
                    c.alignment = Alignment(horizontal='right')
                elif ws is ws3:
                    c.alignment = Alignment(vertical='top', wrap_text=True)
    for ws, tcol in ((ws1, 7), (ws2, 6)):
        for row in ws.iter_rows(min_row=2):
            t = row[tcol - 1].value
            if t in ('区域小计', '区域合计'):
                for c in row:
                    c.fill = REG_FILL if t == '区域小计' else TOT_FILL
                    c.font = Font(bold=True, color=BLUE)
    wb.save(OUT)
    print('→ %s（%.1f KB）' % (OUT, os.path.getsize(OUT) / 1024.0))
    print('   sheet：按区域-课题组 %d 行 ｜ 按区域-销售员 %d 行 ｜ 口径与KPI %d 行'
          % (ws1.max_row - 1, ws2.max_row - 1, ws3.max_row - 1))


if __name__ == '__main__':
    main()