#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""备货基础数据：近 12 个月（2025-11-01 ~ 2026-10-20）货号消耗与采购结构"""
import datetime as dt
import os
from collections import defaultdict

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

D25 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx'
D26 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx'
OUT = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '备货基础数据_近12个月货号消耗_2026-10-21.xlsx')
EXCL = ('宝锐', '宝泰', '横琴')

sku = defaultdict(lambda: {'amt': 0.0, 'orders': set(), 'cust': set(), 'qty': 0.0, 'units': set(),
                           'dept': defaultdict(float), 'months': defaultdict(float), 'names': set()})


def add(code, name, cust, no, amt, qty, unit, dept, month):
    if not code:
        return
    x = sku[code]
    x['amt'] += amt
    x['orders'].add(no)
    x['cust'].add(cust)
    x['qty'] += qty
    x['units'].add(unit or '')
    x['dept'][dept or '未填'] += amt
    x['months'][month] += amt
    if name:
        x['names'].add(name)


# 2025-11 ~ 2025-12
wb = openpyxl.load_workbook(D25, read_only=True, data_only=True)
ws = wb['Sheet1']
rows = ws.iter_rows(values_only=True)
H = {h: i for i, h in enumerate([str(x).strip() for x in next(rows)])}
for r in rows:
    m = str(r[H['合同月份']] or '')
    if m not in ('2025-11', '2025-12'):
        continue
    if str(r[H['单据类型']] or '') != '标准销售订单' or str(r[H['是否赠品']] or '') == '是':
        continue
    cust = str(r[H['客户']] or '')
    if any(k in cust for k in EXCL):
        continue
    try:
        amt = float(r[H['价税合计']] or 0)
    except Exception:
        amt = 0.0
    try:
        qty = float(r[H['销售数量']] or 0)
    except Exception:
        qty = 0.0
    add(str(r[H['货号#']] or '').strip(), str(r[H['物料名称']] or '').strip(), cust,
        str(r[H['单据编号']] or ''), amt, qty, str(r[H['销售单位']] or ''),
        str(r[H['备货部门']] or ''), m)

# 2026-01 ~ 2026-10-20
wb2 = openpyxl.load_workbook(D26, read_only=True, data_only=True)
ws2 = wb2.active
rows2 = ws2.iter_rows(values_only=True)
H2 = {h: i for i, h in enumerate([str(x).strip() for x in next(rows2)])}
for r in rows2:
    if str(r[H2['单据类型']] or '') != '标准销售订单' or str(r[H2['单据状态']] or '') != '已审核':
        continue
    if str(r[H2['是否赠品']] or '') == '是':
        continue
    cust = str(r[H2['客户']] or '')
    if any(k in cust for k in EXCL):
        continue
    d = r[H2['合同日期']]
    if isinstance(d, dt.datetime):
        d = d.date()
    elif not isinstance(d, dt.date):
        try:
            d = dt.datetime.strptime(str(d)[:10], '%Y-%m-%d').date()
        except Exception:
            continue
    try:
        amt = float(r[H2['价税合计']] or 0)
    except Exception:
        amt = 0.0
    try:
        qty = float(r[H2['销售数量']] or 0)
    except Exception:
        qty = 0.0
    add(str(r[H2['货号#']] or '').strip(), str(r[H2['物料名称']] or '').strip(), cust,
        str(r[H2['单据编号']] or ''), amt, qty, str(r[H2['销售单位']] or ''),
        str(r[H2['备货部门']] or ''), d.strftime('%Y-%m'))

MONTHS = 11.65  # 2025-11 ~ 2026-09 整 11 个月 + 2026-10 的 20/31
rows_out = []
for code, v in sku.items():
    if v['amt'] <= 0:
        continue
    top = max(v['dept'].items(), key=lambda kv: kv[1])
    last = max(v['months']) if v['months'] else ''
    units = [u for u in v['units'] if u]
    qty_txt = round(v['qty'], 2) if len(units) == 1 else '多单位'
    rows_out.append([code, sorted(v['names'])[0][:46] if v['names'] else '', round(v['amt'], 2),
                     len(v['orders']), len(v['cust']), round(v['amt'] / MONTHS, 2),
                     qty_txt, units[0] if len(units) == 1 else '/'.join(sorted(units))[:12],
                     last, top[0], round(top[1] / v['amt'] * 100, 1)])
rows_out.sort(key=lambda x: -x[2])

wb3 = openpyxl.Workbook()
ws3 = wb3.active; ws3.title = '近12月货号消耗'
HF = PatternFill('solid', fgColor='1F4E79'); HFONT = Font(color='FFFFFF', bold=True, size=10)
heads = ['货号', '品名', '近12月销售额(元)', '订单数', '采购客户数', '折合月均销售额(元)', '数量合计', '单位', '最近采购月', '主备货部门', '部门占比%']
widths = [20, 44, 16, 8, 10, 18, 12, 8, 11, 20, 10]
for i, h in enumerate(heads, 1):
    c = ws3.cell(row=1, column=i, value=h); c.fill = HF; c.font = HFONT
    c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws3.column_dimensions[get_column_letter(i)].width = widths[i - 1]
for ri, row in enumerate(rows_out, 2):
    for ci, v in enumerate(row, 1):
        c = ws3.cell(row=ri, column=ci, value=v)
        if ci in (3, 6):
            c.number_format = '#,##0.00'
        if ci == 11:
            c.number_format = '0.0'
ws3.freeze_panes = 'A2'

ws4 = wb3.create_sheet('口径')
notes = [['项目', '说明'],
         ['窗口', '2025-11-01 ~ 2026-10-20（2025 年 11–12 月 + 2026 年 1 月至 10-20）'],
         ['折合月均', '销售额 ÷ 11.65 个月（11 个整月 + 10 月 20/31）'],
         ['口径', '标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴；金额＝价税合计'],
         ['数量', '同一货号存在多种销售单位时不做加总，显示「多单位」'],
         ['用途', '作为备货评审的消耗侧底表；不等于备货建议量（备货需再叠加安全库存、交付周期与客户预测）']]
for i, row in enumerate(notes, 1):
    for j, v in enumerate(row, 1):
        c = ws4.cell(row=i, column=j, value=v)
        if i == 1:
            c.fill = HF; c.font = HFONT
ws4.column_dimensions['A'].width = 14
ws4.column_dimensions['B'].width = 96
wb3.save(OUT)
print('货号数', len(rows_out), '｜合计 %.2f 元' % sum(r[2] for r in rows_out))
print('✅', OUT)