#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
客户分年订单对比分析（可复用）
用法:
    /usr/bin/python3 脚本/客户分年对比分析.py "生科原"                # 客户名关键词（客户名包含即命中）
    可选: --out 输出名前缀

数据源（ERP 订单导出，按年份字典配置；2026 为不完整年度，自动按 1-8 月同期对比）
口径铁律（踩过的坑）:
  1) 客户名按关键词包含匹配，必须人工核对同前缀的关联实体（如「深圳生科原技术」+「深圳生科原生物」= 同一集团），
     逐行确认没混入名称相近的其他客户（如「胜泰生科」「涌源合生」）。
  2) 金额只认「价税合计」；预测订单（单据编号 FO 开头）不计销售，须先校验其金额为 0。
  3) 销量与均价只统计「有金额的付费行」—— 测试/赠品行金额为 0 但数量很大，
     混进来会把均价摊薄一半以上（本项目实测：混算 57 元 vs 付费口径 101 元，差 78%）。
  4) 2025 年导出文件可能缺「合同日期」列（只有「合同月份」），月度聚合需回退到合同月份。
  5) 同比必须用同期（1-8 月）口径，且两实体合并，否则主体切换会造成假暴涨/假暴跌。
输出:
    数据/<客户>分年订单对比_<年>-<年>.json   结构化中间数据
    销售分析/客户分年对比_<客户>_<年>-<年>.html  自包含 HTML（内联 SVG 图表 + 可搜索/排序/导出 CSV 表格）
"""
import json, collections, re, base64, os, sys, argparse

SRC_FILES = {
    '2024': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2024全年订单.xlsx',
    '2025': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx',
    '2026': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20260909.xlsx',
}
COLS = ['合同日期', '合同月份', '单据类型', '单据编号', '客户', '销售员', '物料编码', '货号#',
        '物料名称', '销售单位', '销售数量', '含税单价', '价税合计', '是否赠品', '区域']
YEARS = ['2024', '2025', '2026']
MONTHS = [f'{i:02d}' for i in range(1, 13)]
YTD_MONTH = '08'


def load_year(path):
    import openpyxl
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb['Sheet1'] if 'Sheet1' in wb.sheetnames else wb[wb.sheetnames[0]]
    rows = list(ws.iter_rows(values_only=True))
    wb.close()
    # 找表头行（含「客户」且含「价税合计」）
    hi = 0
    for i, r in enumerate(rows[:20]):
        s = [str(x) for x in r if x is not None]
        if any('客户' == x for x in s) and any('价税合计' in x for x in s):
            hi = i
            break
    hdr = [str(x).strip() if x is not None else '' for x in rows[hi]]
    out = []
    for r in rows[hi + 1:]:
        if r is None or all(x is None for x in r):
            continue
        d = {}
        for k, v in zip(hdr, r):
            if k:
                d[k] = v
        out.append(d)
    return out


def num(v):
    try:
        return float(v)
    except Exception:
        return 0.0


def ym(r):
    d = str(r.get('合同日期') or '')[:10]
    if re.match(r'\d{4}-\d{2}', d):
        return d[:7]
    m = str(r.get('合同月份') or '')[:7]
    return m if re.match(r'\d{4}-\d{2}', m) else ''


def code_of(r):
    return str(r.get('货号#') or r.get('物料编码') or '').strip()


def wan(v):
    return f'{v/1e4:,.1f}'


def analyze(keyword):
    data = {}
    for y, p in SRC_FILES.items():
        if not os.path.exists(p):
            print(f'  ! 缺文件 {y}: {p}')
            continue
        rows = load_year(p)
        hit = [r for r in rows if keyword in str(r.get('客户', ''))]
        data[y] = hit
        names = collections.Counter(str(r.get('客户', '')).strip() for r in hit)
        other = collections.Counter(str(r.get('客户', '')).strip() for r in rows
                                    if keyword in str(r.get('客户', '')))
        print(f'  {y}: 源 {len(rows):,} 行 → 命中 {len(hit):,} 行 {dict(names)}')
    return data


def build(data, keyword):
    amt = {y: 0.0 for y in YEARS}
    qty = {y: 0.0 for y in YEARS}        # 付费行
    free_qty = {y: 0.0 for y in YEARS}   # 免费样品/赠品行
    rows_amt = {y: 0 for y in YEARS}
    codes = {y: collections.defaultdict(lambda: [0.0, 0.0]) for y in YEARS}
    cname, orders, odate = {}, {y: collections.defaultdict(float) for y in YEARS}, {}
    matrix = {y: {m: 0.0 for m in MONTHS} for y in YEARS}
    ent = {y: collections.defaultdict(lambda: [0.0, 0]) for y in YEARS}
    types = {y: collections.Counter() for y in YEARS}
    gift = {y: [0, 0.0] for y in YEARS}
    detail, fo_rows, fo_amt = [], 0, 0.0

    for y in YEARS:
        for r in data.get(y, []):
            a, q = num(r.get('价税合计')), num(r.get('销售数量'))
            t = str(r.get('单据类型', '')).strip()
            oid = str(r.get('单据编号', '')).strip()
            if oid.startswith('FO'):
                fo_rows += 1
                fo_amt += a
            types[y][t] += 1
            if str(r.get('是否赠品', '')).strip() in ('是', 'Y', 'True', '1'):
                gift[y][0] += 1
                gift[y][1] += a
            amt[y] += a
            if a > 0:
                rows_amt[y] += 1
                qty[y] += q
            else:
                free_qty[y] += q
            c = code_of(r)
            if c:
                codes[y][c][0] += a
                if a > 0:
                    codes[y][c][1] += q
                if c not in cname and str(r.get('物料名称', '')).strip():
                    cname[c] = str(r.get('物料名称')).strip()
            k = ym(r)
            if k:
                matrix[y][k[5:7]] += a
            orders[y][oid] += a
            d = str(r.get('合同日期') or r.get('合同月份') or '')[:10].replace(' 00:00:00', '')
            if oid not in odate or (d and d < odate[oid]):
                odate[oid] = d
            e = str(r.get('客户', '')).strip()
            ent[y][e][0] += a
            ent[y][e][1] += 1
            if a > 0 and c:
                detail.append({'year': y, 'date': d or f'{y}-00', 'order': oid, 'type': t,
                               'customer': e, 'code': c, 'name': cname.get(c, ''),
                               'qty': round(q, 2), 'price': round(num(r.get('含税单价')), 2),
                               'amount': round(a, 2), 'sales': str(r.get('销售员', '')).strip()})
    return dict(amt=amt, qty=qty, free_qty=free_qty, rows_amt=rows_amt, codes=codes, cname=cname,
                orders=orders, odate=odate, matrix=matrix, ent=ent, types=types, gift=gift,
                detail=detail, fo_rows=fo_rows, fo_amt=fo_amt)


def build_html(res, keyword):
    amt, qty, free_qty = res['amt'], res['qty'], res['free_qty']
    matrix, codes, cname, ent, types, gift = res['matrix'], res['codes'], res['cname'], res['ent'], res['types'], res['gift']
    detail, orders, odate = res['detail'], res['orders'], res['odate']
    valid_orders = {y: len([v for v in orders[y].values() if v > 0]) for y in YEARS}
    avg_order = {y: (amt[y] / valid_orders[y] if valid_orders[y] else 0) for y in YEARS}
    avg_price = {y: (amt[y] / qty[y] if qty[y] else 0) for y in YEARS}
    active_codes = {y: len([c for c, v in codes[y].items() if v[0] > 0]) for y in YEARS}
    ytd = {y: sum(v for m, v in matrix[y].items() if m <= YTD_MONTH) for y in YEARS}
    yoy25 = (ytd['2025'] / ytd['2024'] - 1) * 100 if ytd['2024'] else 0
    yoy26 = (ytd['2026'] / ytd['2025'] - 1) * 100 if ytd['2025'] else 0
    q_tail = sum(v for m, v in matrix['2025'].items() if m > YTD_MONTH)
    last_order = max([d for d in odate.values() if d]) if odate else ''
    colors = {'2024': '#95a5a6', '2025': '#1a5276', '2026': '#e74c3c'}

    # KPI
    kpis = [
        ('2024 全年', f'{wan(amt["2024"])} 万', f'{valid_orders["2024"]} 单 · 单均 {wan(avg_order["2024"])} 万', '#6b7c93'),
        ('2025 全年', f'{wan(amt["2025"])} 万', f'{valid_orders["2025"]} 单 · 单均 {wan(avg_order["2025"])} 万', '#1a5276'),
        ('2026 至今', f'{wan(amt["2026"])} 万', f'{valid_orders["2026"]} 单 · 单均 {wan(avg_order["2026"])} 万', '#c0392b'),
        ('同期同比（1-8月）', f'{yoy26:+.1f}%', f'2025 同期 {yoy25:+.1f}% · 2024 基数 {wan(ytd["2024"])} 万', '#e67e22'),
    ]
    kpi_html = ''.join(f'<div class="kpi" style="border-left-color:{c}"><div class="k-lb">{t}</div>'
                       f'<div class="k-v" style="color:{c}">{v}</div><div class="k-s">{s}</div></div>' for t, v, s, c in kpis)

    # 月度折线
    CH, CW, PADL, PADR, PADT, PADB = 260, 760, 52, 16, 16, 28
    maxv = max([max(matrix[y].values()) for y in YEARS] + [1])
    plotw, ploth = CW - PADL - PADR, CH - PADT - PADB
    xf = lambda i: PADL + i * plotw / 11
    yf = lambda v: PADT + ploth - v / maxv * ploth
    sv = [f'<svg viewBox="0 0 {CW} {CH}" class="chart">']
    for g in range(5):
        v = maxv * g / 4
        sv.append(f'<line x1="{PADL}" y1="{yf(v):.1f}" x2="{CW-PADR}" y2="{yf(v):.1f}" stroke="#e8edf2"/>')
        sv.append(f'<text x="{PADL-8}" y="{yf(v)+4:.1f}" class="ax" text-anchor="end">{v/1e4:,.0f}</text>')
    for i in range(12):
        sv.append(f'<text x="{xf(i):.1f}" y="{CH-8}" class="ax" text-anchor="middle">{i+1}月</text>')
    for y in YEARS:
        pts = ' '.join(f'{xf(i):.1f},{yf(matrix[y][m]):.1f}' for i, m in enumerate(MONTHS))
        sv.append(f'<polyline points="{pts}" fill="none" stroke="{colors[y]}" stroke-width="2.2"/>')
        for i, m in enumerate(MONTHS):
            if matrix[y][m] > 0:
                sv.append(f'<circle cx="{xf(i):.1f}" cy="{yf(matrix[y][m]):.1f}" r="3" fill="{colors[y]}">'
                          f'<title>{y}年{i+1}月 {matrix[y][m]/1e4:,.1f}万</title></circle>')
    sv.append('</svg>')
    line_svg = ''.join(sv)
    legend = ''.join(f'<span class="lg"><i style="background:{colors[y]}"></i>{y}年</span>' for y in YEARS) + '<span class="lg" style="color:#8a94a6">（单位：万元）</span>'

    # 年度柱
    BH, bmax = 200, max(list(amt.values()) + [1])
    bars = []
    for i, y in enumerate(YEARS):
        h = amt[y] / bmax * (BH - 40)
        bx = 70 + i * 150
        tag = '（不完整）' if y == YEARS[-1] else ''
        bars.append(f'<rect x="{bx}" y="{BH-30-h:.1f}" width="108" height="{h:.1f}" rx="4" fill="{colors[y]}"/>')
        bars.append(f'<text x="{bx+54}" y="{BH-30-h-8:.1f}" class="bv" text-anchor="middle">{wan(amt[y])}万</text>')
        bars.append(f'<text x="{bx+54}" y="{BH-12}" class="ax" text-anchor="middle">{y}{tag}</text>')
    bar_svg = f'<svg viewBox="0 0 520 {BH}" class="chart">{"".join(bars)}</svg>'

    # 货号表
    allc = sorted({c for y in YEARS for c in codes[y]}, key=lambda c: -sum(codes[y][c][0] for y in YEARS))
    code_rows = []
    for c in allc:
        tot = sum(codes[y][c][0] for y in YEARS)
        if tot <= 0:
            continue
        row = {'code': c, 'name': cname.get(c, '') or '—'}
        for y in YEARS:
            row[f'a{y}'] = round(codes[y][c][0], 2)
            row[f'q{y}'] = round(codes[y][c][1], 2)
        row['total'] = round(tot, 2)
        code_rows.append(row)

    ent_rows = [{'year': y, 'customer': e, 'amount': round(a, 2), 'rows': n}
                for y in YEARS for e, (a, n) in sorted(ent[y].items(), key=lambda kv: -kv[1][0])]
    type_rows = [{'year': y, 'type': t or '—', 'lines': n,
                  'amount': round(sum(num(r.get('价税合计')) for r in [] ), 2)} for y in YEARS for t, n in types[y].most_common()]

    # 洞察
    n_new = len({c for c in codes['2025'] if codes['2025'][c][0] > 0} - {c for c in codes['2024'] if codes['2024'][c][0] > 0})
    top = {y: sorted([r for r in code_rows if r[f'a{y}'] > 0], key=lambda r: -r[f'a{y}'])[:3] for y in YEARS}
    t3t = lambda y: '、'.join(f"{r['code']}（{wan(r[f'a{y}'])}万）" for r in top[y])
    p3 = sum(r['a2026'] for r in top['2026']) / amt['2026'] * 100 if amt['2026'] else 0
    e24 = sorted(ent['2024'].items(), key=lambda kv: -kv[1][0])[0] if ent['2024'] else ('—', [0, 0])
    e25 = sorted(ent['2025'].items(), key=lambda kv: -kv[1][0])[0] if ent['2025'] else ('—', [0, 0])
    e26 = '、'.join(f'{k} {wan(v[0])}万' for k, v in sorted(ent['2026'].items(), key=lambda kv: -kv[1][0]))

    insights = [
        ('规模走势', f"2024 <b>{wan(amt['2024'])} 万</b> → 2025 <b>{wan(amt['2025'])} 万</b>（全年 {amt['2025']/amt['2024']*100-100:+.0f}%），2026 至今 <b>{wan(amt['2026'])} 万</b>（1-8 月同期同比 <b>{yoy26:+.1f}%</b>）。"),
        ('旺季是否启动', f"2025 年 {int(YTD_MONTH)+1}-12 月做了 <b>{wan(q_tail)} 万</b>（占全年 {q_tail/amt['2025']*100:.0f}%）；2026 末单 <b>{last_order}</b>。情景测算：Q4 若为去年同期的 0~50%（0 到 {wan(q_tail*0.5)} 万），全年 {wan(amt['2026'])}~{wan(amt['2026']+q_tail*0.5)} 万，同比 {amt['2026']/amt['2025']*100-100:.0f}%~{(amt['2026']+q_tail*0.5)/amt['2025']*100-100:.0f}%。"),
        ('集中度', f"2026 有金额货号 <b>{active_codes['2026']} 个</b>（2024 {active_codes['2024']} / 2025 {active_codes['2025']}）。2026 前三：{t3t('2026')}，合计占 {p3:.0f}%。2025 前三：{t3t('2025')}。"),
        ('量价关系', f"付费销量 {qty['2024']:,.0f} → <b>{qty['2025']:,.0f}</b> → {qty['2026']:,.0f} 单位；含税均价 {avg_price['2024']:.0f} → <b>{avg_price['2025']:.0f}</b> → {avg_price['2026']:.0f} 元/单位。2025 单价 {(avg_price['2025']/avg_price['2024']-1)*100:+.0f}%、销量 {qty['2025']/qty['2024']:.1f} 倍；2026 量回到 2024 的 {qty['2026']/qty['2024']*100:.0f}%、单价 {(avg_price['2026']/avg_price['2025']-1)*100:+.0f}%。"),
        ('核算主体', f"2024 主体 <b>{e24[0]}</b>（{wan(e24[1][0])} 万）→ 2025 起 <b>{e25[0]}</b>（{wan(e25[1][0])} 万）；2026 分布：{e26}。单一主体做同比会出现假暴涨/假暴跌，必须合并集团。"),
        ('样品与新品', f"测试单行数 {types['2024'].get('测试销售订单', 0)}/{types['2025'].get('测试销售订单', 0)}/{types['2026'].get('测试销售订单', 0)}；免费样品数量 {free_qty['2024']:,.0f}/{free_qty['2025']:,.0f}/{free_qty['2026']:,.0f} 单位。2025 新增货号 {n_new} 个。"),
    ]
    ins_html = ''.join(f'<div class="ins"><div class="ins-t">{i+1}. {t}</div><div class="ins-b">{b}</div></div>' for i, (t, b) in enumerate(insights))

    qty_row = (
        f'<tr><td class="ky">付费销量（单位）</td>' + ''.join(f'<td class="num">{qty[y]:,.0f}</td>' for y in YEARS) + '</tr>'
        + f'<tr><td class="ky">免费样品/赠品数量</td>' + ''.join(f'<td class="num">{free_qty[y]:,.0f}</td>' for y in YEARS) + '</tr>'
        + f'<tr><td class="ky">含税均价（元/单位）</td>' + ''.join(f'<td class="num">{avg_price[y]:,.1f}</td>' for y in YEARS) + '</tr>'
        + f'<tr><td class="ky">测试单行数</td>' + ''.join(f'<td class="num">{types[y].get("测试销售订单", 0)}</td>' for y in YEARS) + '</tr>'
        + f'<tr><td class="ky">赠品行数</td>' + ''.join(f'<td class="num">{gift[y][0]}</td>' for y in YEARS) + '</tr>'
        + f'<tr><td class="ky">有金额货号数</td>' + ''.join(f'<td class="num">{active_codes[y]}</td>' for y in YEARS) + '</tr>')
    month_rows = ''.join(f'<tr><td class="ky">{int(m)}月</td>' + ''.join(f'<td class="num{" hi" if y == "2025" else ""}">{matrix[y][m]/1e4:,.1f}</td>' for y in YEARS) + '</tr>' for m in MONTHS)
    month_rows += '<tr class="tot"><td class="ky">合计</td>' + ''.join(f'<td class="num">{wan(amt[y])}</td>' for y in YEARS) + '</tr>'

    logo_uri = ''
    lp = os.path.expanduser('~/work/公司资料/logo_transparent.png')
    if os.path.exists(lp):
        logo_uri = 'data:image/png;base64,' + base64.b64encode(open(lp, 'rb').read()).decode()
    brand = f'<img src="{logo_uri}" alt="BIORI" class="logo">' if logo_uri else '<b class="logo-txt">BIORI BIOTECH</b>'

    CSS = """*{box-sizing:border-box}
body{margin:0;background:#f0f2f5;color:#22303f;font:14px/1.6 -apple-system,"PingFang SC","Microsoft YaHei",sans-serif}
.top{background:#fff;border-bottom:3px solid #1a5276;padding:12px 26px;display:flex;align-items:center;gap:14px;position:sticky;top:0;z-index:9}
.top .logo{height:30px}.top .logo-txt{color:#1a5276;font-size:18px;letter-spacing:1px}
.top h1{font-size:17px;margin:0;font-weight:600;color:#12263a}
.top .sub{margin-left:auto;color:#7a8798;font-size:12px;text-align:right;line-height:1.4}
.wrap{max-width:1140px;margin:0 auto;padding:20px 18px 60px}
h2{font-size:16px;color:#12263a;margin:30px 0 12px;padding-left:10px;border-left:4px solid #1a5276}
.card{background:#fff;border-radius:10px;padding:18px;box-shadow:0 1px 4px rgba(20,40,70,.08);margin-bottom:14px}
.kpis{display:grid;grid-template-columns:repeat(auto-fit,minmax(215px,1fr));gap:12px}
.kpi{background:#fff;border-radius:10px;border-left:4px solid #1a5276;padding:14px 16px;box-shadow:0 1px 4px rgba(20,40,70,.08)}
.k-lb{color:#7a8798;font-size:12px}.k-v{font-size:26px;font-weight:700;margin:2px 0}.k-s{color:#7a8798;font-size:12px}
.chart{width:100%;height:auto}.ax{font-size:11px;fill:#8a94a6}.bv{font-size:12px;fill:#22303f;font-weight:600}
.lg{display:inline-flex;align-items:center;gap:5px;margin-right:14px;font-size:12px;color:#546478}
.lg i{width:14px;height:3px;border-radius:2px;display:inline-block}
.ins{border-left:3px solid #1a5276;background:#f7fafc;padding:11px 14px;border-radius:0 8px 8px 0;margin-bottom:10px}
.ins-t{font-weight:600;color:#12263a;margin-bottom:3px}.ins-b{color:#48586b;font-size:13px}
table{width:100%;border-collapse:collapse;font-size:13px}
th,td{padding:8px 9px;border-bottom:1px solid #eef2f6;text-align:center}
th{background:#f5f8fb;color:#12263a;font-weight:600;position:sticky;top:0;white-space:nowrap}
td.ky{text-align:left;color:#546478}td.lft{text-align:left}td.num{text-align:right;font-variant-numeric:tabular-nums}
tr.tot td{font-weight:700;background:#f5f8fb}td.hi{background:#eef6fc}
.tb-bar{display:flex;gap:8px;align-items:center;flex-wrap:wrap;margin-bottom:10px}
.tb-bar input{flex:1;min-width:150px;padding:7px 10px;border:1px solid #d8e0e8;border-radius:6px;font-size:13px}
.tb-bar button{padding:7px 12px;border:1px solid #d8e0e8;background:#fff;border-radius:6px;cursor:pointer;font-size:13px;color:#1a5276}
.tb-bar button:hover{background:#f0f6fb}.tb-bar .cnt{color:#7a8798;font-size:12px}
.scroll{overflow:auto;max-height:430px}
.note{color:#7a8798;font-size:12px;line-height:1.7}
.up{color:#1e8449;font-weight:600}.down{color:#c0392b;font-weight:600}"""
    JS = """var D1=__D1__,D2=__D2__;
function money(v){return v?Number(v).toLocaleString('zh-CN',{maximumFractionDigits:2}):''}
var COLS1=[['code',0],['name',0],['a2024',1],['q2024',1],['a2025',1],['q2025',1],['a2026',1],['q2026',1],['total',1]];
var COLS2=[['date',0],['order',0],['type',0],['code',0],['name',0],['qty',1],['price',1],['amount',1],['sales',0]];
var S1={k:'total',d:-1},S2={k:'date',d:1};
function esc2(s){return String(s).replace(/&/g,'&amp;').replace(/</g,'&lt;')}
function render(id,data,cols){
  var tb=document.querySelector('#'+id+' tbody'),h='';
  data.forEach(function(r){h+='<tr>'+cols.map(function(c){var k=c[0],n=c[1],v=r[k];
    return '<td class="'+(n?'num':'lft')+'">'+(n?esc2(money(v)):esc2(v))+'</td>'}).join('')+'</tr>'});
  if(!data.length)h='<tr><td colspan="'+cols.length+'" style="color:#8a94a6;padding:22px">无匹配数据</td></tr>';
  tb.innerHTML=h;var c=document.getElementById('c'+id.slice(1));if(c)c.textContent='共 '+data.length+' 行';
}
function paint(id){
  var q=(document.getElementById('q'+id.slice(1))||{}).value||'';
  var raw=(id==='t1')?D1:D2,cols=(id==='t1')?COLS1:COLS2,S=(id==='t1')?S1:S2;
  var d=q.trim()?raw.filter(function(r){return Object.keys(r).some(function(k){return String(r[k]).toLowerCase().indexOf(q.trim().toLowerCase())>=0})}):raw.slice();
  d.sort(function(a,b){var x=a[S.k],y=b[S.k];
    if(typeof x==='number'&&typeof y==='number')return (x-y)*S.d;
    return String(x).localeCompare(String(y))*S.d});
  render(id,d,cols);
}
['t1','t2'].forEach(function(id){
  document.getElementById('q'+id.slice(1)).addEventListener('input',function(){paint(id)});
  document.querySelectorAll('#'+id+' thead th[data-k]').forEach(function(th){
    th.addEventListener('click',function(){
      var k=th.getAttribute('data-k'),S=(id==='t1')?S1:S2;
      if(S.k===k)S.d=-S.d;else{S.k=k;S.d=(k==='date'||k==='order'||k==='name'||k==='code')?1:-1}
      paint(id);
    });
  });
  paint(id);
});
function exp(id,fn){
  var t=document.getElementById(id),out=[];
  t.querySelectorAll('tr').forEach(function(tr){var r=[];
    tr.querySelectorAll('th,td').forEach(function(td){r.push('"'+td.textContent.replace(/"/g,'""')+'"')});
    if(r.length)out.push(r.join(','))});
  var b=new Blob(['\\ufeff'+out.join('\\n')],{type:'text/csv;charset=utf-8'});
  var a=document.createElement('a');a.href=URL.createObjectURL(b);a.download=fn+'.csv';a.click();
}"""
    JS = JS.replace('__D1__', json.dumps(code_rows, ensure_ascii=False)).replace('__D2__', json.dumps(detail, ensure_ascii=False))

    H = f'''<!DOCTYPE html>
<html lang="zh-CN"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>{keyword} 分年订单对比 {YEARS[0]}-{YEARS[-1]} · 宝锐生物</title><style>{CSS}</style></head><body>
<div class="top">{brand}<h1>{keyword} · 分年订单对比（{YEARS[0]}–{YEARS[-1]}）</h1>
<div class="sub">客户：{' + '.join(sorted({r['customer'] for r in detail}))}<br>数据截至 {last_order}（末单日期）</div></div>
<div class="wrap">
<h2>一、总览</h2><div class="kpis">{kpi_html}</div>
<h2>二、年度金额与月度走势</h2>
<div class="card" style="display:flex;gap:26px;flex-wrap:wrap;align-items:center">
<div style="flex:0 0 300px">{bar_svg}</div><div style="flex:1;min-width:420px">{legend}{line_svg}</div></div>
<div class="card"><table><thead><tr><th style="text-align:left">指标</th>{''.join(f'<th>{y}</th>' for y in YEARS)}</tr></thead><tbody>
<tr><td class="ky">销售额（万元）</td>{''.join(f'<td class="num"><b>{wan(amt[y])}</b></td>' for y in YEARS)}</tr>
<tr><td class="ky">有效订单数</td>{''.join(f'<td class="num">{valid_orders[y]}</td>' for y in YEARS)}</tr>
<tr><td class="ky">单均金额（万元）</td>{''.join(f'<td class="num">{wan(avg_order[y])}</td>' for y in YEARS)}</tr>
{qty_row}</tbody></table>
<p class="note" style="margin:10px 0 0">注：同期同比（1-8 月）2025 vs 2024 <span class="{'up' if yoy25>0 else 'down'}">{yoy25:+.1f}%</span>，2026 vs 2025 <span class="{'up' if yoy26>0 else 'down'}">{yoy26:+.1f}%</span>。</p></div>
<div class="card"><table><thead><tr><th style="text-align:left">月度（万元）</th>{''.join(f'<th>{y}</th>' for y in YEARS)}</tr></thead><tbody>{month_rows}</tbody></table></div>
<h2>三、关键洞察</h2><div class="card">{ins_html}</div>
<h2>四、货号结构</h2><div class="card">
<div class="tb-bar"><input id="q1" placeholder="搜索货号 / 品名…"><button onclick="exp('t1','{keyword}货号对比')">📥 导出CSV</button><span class="cnt" id="c1"></span></div>
<div class="scroll"><table id="t1"><thead><tr><th data-k="code">货号</th><th data-k="name">品名</th>
<th data-k="a2024">2024金额</th><th data-k="q2024">2024量</th><th data-k="a2025">2025金额</th><th data-k="q2025">2025量</th>
<th data-k="a2026">2026金额</th><th data-k="q2026">2026量</th><th data-k="total">三年合计</th></tr></thead><tbody></tbody></table></div>
<p class="note" style="margin:10px 0 0">金额单位：元；量仅统计付费行。空白表示当年无采购。</p></div>
<h2>五、订单明细（{len(detail):,} 行）</h2><div class="card">
<div class="tb-bar"><input id="q2" placeholder="搜索单号 / 货号 / 品名 / 销售员…"><button onclick="exp('t2','{keyword}订单明细')">📥 导出CSV</button><span class="cnt" id="c2"></span></div>
<div class="scroll"><table id="t2"><thead><tr><th data-k="date">日期</th><th data-k="order">单据编号</th><th data-k="type">类型</th>
<th data-k="code">货号</th><th data-k="name">品名</th><th data-k="qty">数量</th><th data-k="price">含税单价</th>
<th data-k="amount">价税合计</th><th data-k="sales">销售员</th></tr></thead><tbody></tbody></table></div></div>
<h2>六、核算主体与单据类型</h2><div class="card" style="display:flex;gap:18px;flex-wrap:wrap">
<div style="flex:1;min-width:320px"><table><thead><tr><th style="text-align:left">主体</th><th>年份</th><th>金额（万元）</th><th>行数</th></tr></thead><tbody>
{''.join(f'<tr><td class="lft">{r["customer"]}</td><td>{r["year"]}</td><td class="num">{r["amount"]/1e4:,.1f}</td><td class="num">{r["rows"]}</td></tr>' for r in ent_rows)}
</tbody></table></div>
<div style="flex:1;min-width:320px"><table><thead><tr><th style="text-align:left">单据类型</th><th>年份</th><th>行数</th></tr></thead><tbody>
{''.join(f'<tr><td class="lft">{r["type"]}</td><td>{r["year"]}</td><td class="num">{r["lines"]}</td></tr>' for r in type_rows)}
</tbody></table></div></div>
<h2>七、数据来源与口径</h2><div class="card note">
① 数据源（ERP 订单导出）：{ '<br>　 '.join(f'{y}: {os.path.basename(p)}' for y, p in SRC_FILES.items())}<br>
② 客户筛选：客户名称包含「{keyword}」的全部实体合并统计（{'、'.join(sorted({r['customer'] for r in detail}))}）。<br>
③ 金额口径：ERP「价税合计」（含税）；预测订单（FO 开头，{res['fo_rows']} 行，金额 {res['fo_amt']:,.0f}）不计销售。<br>
④ 销量/均价口径：只统计有金额的付费行；测试与赠品行（金额 0）数量单列，不参与均价。销售单位混用盒/支/ml，均价仅供趋势参考。<br>
⑤ 同比口径：统一取 1-{int(YTD_MONTH)} 月同期；{YEARS[-1]} 为不完整年度。<br>
⑥ 本页由 <code>脚本/客户分年对比分析.py</code> 生成，中间数据：<code>数据/{keyword}分年订单对比_{YEARS[0]}-{YEARS[-1]}.json</code>。
</div></div><script>{JS}</script></body></html>'''
    return H


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('keyword', help='客户名关键词（包含匹配）')
    ap.add_argument('--out', default=None)
    a = ap.parse_args()
    print(f'== 客户分年对比：{a.keyword} ==')
    data = analyze(a.keyword)
    res = build(data, a.keyword)
    html = build_html(res, a.keyword)
    tag = a.out or a.keyword
    os.makedirs('数据', exist_ok=True)
    os.makedirs('销售分析', exist_ok=True)
    jp = f'数据/{tag}分年订单对比_{YEARS[0]}-{YEARS[-1]}.json'
    hp = f'销售分析/客户分年对比_{tag}_{YEARS[0]}-{YEARS[-1]}.html'
    payload = {'keyword': a.keyword, 'years': YEARS,
               'amount': {y: round(res['amt'][y], 2) for y in YEARS},
               'orders': {y: len([v for v in res['orders'][y].values() if v > 0]) for y in YEARS},
               'qty_paid': {y: round(res['qty'][y], 2) for y in YEARS},
               'qty_free': {y: round(res['free_qty'][y], 2) for y in YEARS},
               'matrix': res['matrix'], 'customers': collections.Counter(r['customer'] for r in res['detail']),
               'detail_rows': len(res['detail'])}
    json.dump(payload, open(jp, 'w'), ensure_ascii=False, indent=1, default=str)
    open(hp, 'w').write(html)
    print(f'  金额: ' + ' / '.join(f"{y} {wan(res['amt'][y])}万" for y in YEARS))
    print(f'  HTML: {hp}  {len(html.encode()):,} 字节')
    print(f'  JSON: {jp}')


if __name__ == '__main__':
    main()