import datetime as dt
import json
import os
import re
from collections import defaultdict

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

P26 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx'
P25 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx'
OUTDIR = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/经营分析/产销/8254系列客户测试与采购_2026-10-21'
EXCL = ('宝锐', '宝泰', '横琴')


def parent_of(code):
    return re.sub(r'-\d$', '', code) if re.search(r'-\d$', code) else code


recs = []
H, it = None, None
wb = openpyxl.load_workbook(P26, read_only=True, data_only=True)
it = wb.active.iter_rows(values_only=True)
H = {h: i for i, h in enumerate([str(x).strip() for x in next(it)])}
for r in it:
    code = str(r[H['货号#']] or '').strip()
    if '8254' not in code:
        continue
    d = r[H['合同日期']]
    d = d.date() if isinstance(d, dt.datetime) else (d if isinstance(d, dt.date) else str(d)[:10])
    try:
        amt = float(r[H['价税合计']] or 0)
    except Exception:
        amt = 0.0
    recs.append({'year': 2026, 'date': str(d), 'no': str(r[H['单据编号']] or ''), 'cust': str(r[H['客户']] or ''),
                 'sku': code, 'parent': parent_of(code), 'type': str(r[H['单据类型']] or ''),
                 'gift': str(r[H['是否赠品']] or ''), 'amt': round(amt, 2), 'qty': r[H['销售数量']],
                 'unit': str(r[H['销售单位']] or ''), 'sales': str(r[H['销售员']] or ''),
                 'dept': str(r[H['备货部门']] or ''), 'name': str(r[H['物料名称']] or '')})

wb2 = openpyxl.load_workbook(P25, read_only=True, data_only=True)
it2 = wb2['Sheet1'].iter_rows(values_only=True)
H2 = {h: i for i, h in enumerate([str(x).strip() for x in next(it2)])}
for r in it2:
    code = str(r[H2['货号#']] or '').strip()
    if '8254' not in code:
        continue
    try:
        amt = float(r[H2['价税合计']] or 0)
    except Exception:
        amt = 0.0
    recs.append({'year': 2025, 'date': str(r[H2['合同月份']] or ''), 'no': str(r[H2['单据编号']] or ''),
                 'cust': str(r[H2['客户']] or ''), 'sku': code, 'parent': parent_of(code),
                 'type': str(r[H2['单据类型']] or ''), 'gift': str(r[H2['是否赠品']] or ''),
                 'amt': round(amt, 2), 'qty': r[H2['销售数量']], 'unit': str(r[H2['销售单位']] or ''),
                 'sales': str(r[H2['销售员']] or ''), 'dept': str(r[H2['备货部门']] or ''),
                 'name': str(r[H2['物料名称']] or '')})

# 客户 × 母号
agg = defaultdict(lambda: {'test': 0, 'paid': 0, 'free': 0, 'amt': 0.0, 'qty': 0.0,
                           'first': '', 'last': '', 'months': set(), 'paid_months': set(), 'sales': set(), 'depts': set(),
                           'subs': set(), 'names': set()})
for x in recs:
    if any(k in x['cust'] for k in EXCL):
        continue
    k = (x['parent'], x['cust'])
    a = agg[k]
    a['subs'].add(x['sku'])
    if x['name']:
        a['names'].add(x['name'])
    if x['type'] == '测试销售订单':
        a['test'] += 1
    elif x['gift'] == '是':
        a['free'] += 1
    elif x['amt'] > 0:
        a['paid'] += 1
        a['amt'] += x['amt']
        if x['date']:
            a['paid_months'].add(x['date'][:7])
    else:
        a['free'] += 1
    try:
        a['qty'] += float(x['qty'] or 0)
    except Exception:
        pass
    if x['sales']:
        a['sales'].add(x['sales'])
    if x['dept']:
        a['depts'].add(x['dept'])
    if x['date']:
        a['months'].add(x['date'][:7])
        a['first'] = min(a['first'], x['date']) if a['first'] else x['date']
        a['last'] = max(a['last'], x['date']) if a['last'] else x['date']

def status(a):
    if a['paid'] >= 2 or (a['paid'] >= 1 and len(a['paid_months']) >= 2):
        return '持续采购'
    if a['paid'] == 1:
        return '单次采购'
    if a['test'] > 0:
        return '仅测试'
    return '仅样板/赠'

detail = []
for (parent, cust), a in agg.items():
    detail.append({'parent': parent, 'cust': cust, 'test': a['test'], 'paid': a['paid'], 'free': a['free'],
                   'amt': round(a['amt'], 2), 'qty': a['qty'], 'months': len(a['months']),
                   'first': a['first'], 'last': a['last'], 'status': status(a),
                   'sales': '/'.join(sorted(a['sales'])), 'dept': '/'.join(sorted(a['depts'])),
                   'subs': '/'.join(sorted(a['subs'])), 'name': sorted(a['names'])[0][:44] if a['names'] else ''})
detail.sort(key=lambda d: (-d['amt'], d['parent'], -d['paid']))

# 母号汇总
bysku = defaultdict(lambda: {'custs': set(), 'test_custs': set(), 'paid_custs': set(), 'rep_custs': set(),
                             'amt': 0.0, 'paid_orders': 0, 'name': ''})
for d in detail:
    s = bysku[d['parent']]
    s['custs'].add(d['cust'])
    if d['test']:
        s['test_custs'].add(d['cust'])
    if d['paid']:
        s['paid_custs'].add(d['cust'])
        s['paid_orders'] += d['paid']
        s['amt'] += d['amt']
    if d['status'] == '持续采购':
        s['rep_custs'].add(d['cust'])
    if d['name'] and not s['name']:
        s['name'] = d['name']

os.makedirs(OUTDIR + '/数据', exist_ok=True)
sf = []
for p, s in sorted(bysku.items(), key=lambda kv: -kv[1]['amt']):
    sf.append([p, s['name'], len(s['custs']), len(s['test_custs']), len(s['paid_custs']), len(s['rep_custs']),
               s['paid_orders'], round(s['amt'], 2)])

wb3 = openpyxl.Workbook()
HF = PatternFill('solid', fgColor='1F4E79'); HFONT = Font(color='FFFFFF', bold=True, size=10)


def style(ws, heads, widths, data, money=()):
    for i, h in enumerate(heads, 1):
        c = ws.cell(row=1, column=i, value=h); c.fill = HF; c.font = HFONT
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(i)].width = widths[i - 1]
    for ri, row in enumerate(data, 2):
        for ci, v in enumerate(row, 1):
            cell = ws.cell(row=ri, column=ci, value=v)
            if ci in money:
                cell.number_format = '#,##0.00'
    ws.freeze_panes = 'A2'


ws1 = wb3.active; ws1.title = '母号汇总'
style(ws1, ['母号', '品名', '接触客户数', '测试客户数', '成交客户数', '持续采购客户数', '成交订单数', '成交金额(元)'],
      [22, 46, 11, 11, 11, 14, 10, 15], sf, money=(8,))
ws2 = wb3.create_sheet('客户明细')
rows = [[d['parent'], d['name'], d['cust'], d['status'], d['test'], d['paid'], d['free'],
         d['amt'], round(d['qty'], 2), d['months'], d['first'], d['last'], d['sales'], d['dept'], d['subs']]
        for d in detail]
style(ws2, ['母号', '品名', '客户', '状态', '测试次数', '成交次数', '0元/赠品次数', '成交金额(元)', '数量', '活跃月数',
            '首次', '最近', '销售员', '备货部门', '涉及子货号'],
      [22, 40, 30, 10, 9, 9, 11, 14, 10, 9, 9, 9, 12, 22, 30], rows, money=(8,))
ws3 = wb3.create_sheet('口径')
notes = [['项目', '说明'],
         ['范围', '货号含「8254」的全部 SKU（M8254 / FM8254 及其 -NA / -T1 / -014101 等变体）'],
         ['套装归集', '末尾 -1/-2/-3 视为同套装的组份，按母号归集，避免父子重复计数'],
         ['测试', '单据类型＝测试销售订单'],
         ['成交', '标准销售订单且价税合计 >0'],
         ['持续采购', '同一客户 ≥2 张成交单，或 ≥2 个不同月份有成交'],
         ['数据源', '2026：销售订单_20260101-20261020.xlsx；2025：2025全年订单.xlsx Sheet1'],
         ['剔除', '宝锐/宝泰/横琴自体系客户；赠品行不计金额'],
         ['客户数', '按客户全称去重']]
for i, row in enumerate(notes, 1):
    for j, v in enumerate(row, 1):
        c = ws3.cell(row=i, column=j, value=v)
        if i == 1:
            c.fill = HF; c.font = HFONT
ws3.column_dimensions['A'].width = 14
ws3.column_dimensions['B'].width = 96

wb3.save(OUTDIR + '/8254系列_客户测试与采购_2026-10-21.xlsx')
json.dump({'skus': sf, 'detail': detail}, open(OUTDIR + '/数据/sku8254.json', 'w'), ensure_ascii=False, indent=1)
print('母号 %d 个｜客户 %d 家｜合计成交 %.2f 元' % (len(sf), len({d['cust'] for d in detail}), sum(x[7] for x in sf)))
print('\n母号 TOP：')
for x in sf[:12]:
    print('  %-22s 客户%2d 测试%2d 成交%2d 持续%2d 订单%3d 金额%10.0f' % (x[0], x[2], x[3], x[4], x[5], x[6], x[7]))
print('\n客户 TOP：')
for d in detail[:14]:
    print('  %-26s %-10s %-14s 测试%d 成交%d 金额%9.0f %s~%s' % (
        d['cust'][:24], d['parent'], d['status'], d['test'], d['paid'], d['amt'], d['first'], d['last']))