#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""BR1D303 / BR1E102 系列 2026年8–9月成交价明细（按真实包装规格折算到每个 test）。

规格（用户 2026-10-10 提供，权威）：
  BR1D303-01 = 100T/包    BR1D303-02 = 500T/包
  BR1E102-41 = 500T/包    BR1E102-42 = 2500T/包
注意：ERP「客户物料规格型号#」列写的「10T/包」与真实规格不符，不得用作换算依据。
"""
import os
import pandas as pd

P = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20260930.xlsx')
OUT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/BR1D303_BR1E102_2026年8-9月_每test成交价明细.xlsx'
T = {'BR1D303-00': None, 'BR1D303-01': 100, 'BR1D303-02': 500,
     'BR1E102-40': None, 'BR1E102-41': 500, 'BR1E102-42': 2500}

df = pd.read_excel(P, sheet_name='Sheet1', header=0)
for c in ('价税合计', '销售数量'):
    df[c] = pd.to_numeric(df[c], errors='coerce').fillna(0)
df['sku'] = df['货号#'].astype(str)
df['d'] = pd.to_datetime(df['合同日期'], errors='coerce')
df['ym'] = df['d'].dt.strftime('%Y-%m')

fam = df[df['sku'].str.startswith(('BR1D303', 'BR1E102'))].copy()
print('══ 族内货号（全期行数）══')
for k, v in fam['sku'].value_counts().items():
    print('   %-16s %4d 行   T/包=%s' % (k, v, T.get(k, '未提供')))

sel = fam[fam['ym'].isin(['2026-08', '2026-09'])].copy()
s1 = sel[sel['单据类型'].astype(str).str.contains('标准销售订单', na=False)]
s2 = s1[s1['单据状态'].astype(str).str.contains('已审核', na=False)]
s3 = s2[~s2['客户'].astype(str).str.contains('宝锐|宝泰|横琴', regex=True, na=False)]
s4 = s3[~s3['业务关闭'].astype(str).str.contains('作废|业务终止', regex=True, na=False)]
paid = s4[s4['价税合计'] > 0].copy()
zero = s4[s4['价税合计'] == 0]

paid['T'] = paid['sku'].map(T)
paid['每包'] = (paid['价税合计'] / paid['销售数量']).round(2)
paid['每test'] = (paid['每包'] / paid['T']).round(4)
paid = paid.sort_values(['sku', 'd'])

print('\n══ 2026-08~09 有效成交（0 元试用单已剔除）══')
for sku, g in paid.groupby('sku'):
    t = T.get(sku)
    print('\n  ── %s ｜ %s' % (sku, str(g['物料名称'].iloc[0])[:46]))
    print('     规格 %sT/包 ｜ %d 行 ｜ %d 单 ｜ %d 家客户 ｜ %d 包 ｜ 金额 %.2f 元'
          % (t, len(g), g['单据编号'].nunique(), g['客户'].nunique(), g['销售数量'].sum(), g['价税合计'].sum()))
    if t:
        print('     每包: %.2f ~ %.2f（中位 %.2f） →  每test: %.4f ~ %.4f（中位 %.4f）'
              % (g['每包'].min(), g['每包'].max(), g['每包'].median(),
                 g['每test'].min(), g['每test'].max(), g['每test'].median()))
    print('     %-11s %-24s %7s %10s %11s' % ('日期', '客户', '数量(包)', '每包', '每test'))
    for _, r in g.iterrows():
        print('     %-11s %-24s %7.0f %10.2f %11s'
              % (str(r['d'])[:10], str(r['客户'])[:22], r['销售数量'], r['每包'],
                 ('%.4f' % r['每test']) if t else '—'))

print('\n══ 0 元试用/测试单（不计入价格）══')
for sku, g in zero.groupby('sku'):
    print('   %-16s %3d 行 ｜ %d 包 ｜ %d 家客户' % (sku, len(g), g['销售数量'].sum(), g['客户'].nunique()))

cols = [c for c in ['d', '合同日期', '单据编号', '客户', '销售员', 'sku', '物料名称',
                    '销售数量', '含税单价', '每包', 'T', '每test', '价税合计', '出货批号#'] if c in paid.columns]
os.makedirs(os.path.dirname(OUT), exist_ok=True)
with pd.ExcelWriter(OUT) as w:
    paid[cols].rename(columns={'d': '日期'}).to_excel(w, sheet_name='有效成交明细', index=False)
    agg = (paid.groupby(['sku', '物料名称'], dropna=False)
           .agg(T_per_pack=('T', 'first'), 行数=('价税合计', 'size'), 单数=('单据编号', 'nunique'),
                客户数=('客户', 'nunique'), 包数=('销售数量', 'sum'), 金额=('价税合计', 'sum'),
                每包最低=('每包', 'min'), 每包中位=('每包', 'median'), 每包最高=('每包', 'max'),
                每test最低=('每test', 'min'), 每test中位=('每test', 'median'), 每test最高=('每test', 'max')))
    agg.to_excel(w, sheet_name='按货号统计')
    zero[['d', '单据编号', '客户', 'sku', '销售数量', '物料名称']].rename(columns={'d': '日期'}) \
        .to_excel(w, sheet_name='零价试用单', index=False)
print('\n→ %s' % OUT)