#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""分子试剂产品部 2026年9月 货号/金额 双口径统计。

口径A：严格按 ERP 备货部门字段 = 分子试剂产品部（9月为 0 行）
口径B：按该部门历史出货过的货号，在 9 月追踪其去向与金额
输出：销售分析/分子试剂产品部_2026年9月_货号金额_双口径.xlsx
"""
import os
import pandas as pd

ERP = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20260930.xlsx')
DEPT = '分子试剂产品部'
YM = '2026-09'
OUT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/分子试剂产品部_2026年9月_货号金额_双口径.xlsx'

df = pd.read_excel(ERP, sheet_name='Sheet1', header=0)
df['_m'] = pd.to_datetime(df['合同日期'], errors='coerce').dt.strftime('%Y-%m')

a = df[(df['_m'] == YM) & (df['备货部门'].astype(str).str.strip() == DEPT)]
print('口径A（备货部门=%s，9月）：%d 行 ｜ 金额 %.2f' % (DEPT, len(a), a['价税合计'].sum()))

hist = set(df[df['备货部门'].astype(str).str.strip() == DEPT]['货号#'].dropna().astype(str))
print('该部门历史货号数：%d' % len(hist))
d9 = df[(df['_m'] == YM) & (df['货号#'].astype(str).isin(hist))].copy()
filt = (d9['单据类型'].astype(str).str.contains('标准销售订单', na=False)
        & d9['单据状态'].astype(str).str.contains('已审核', na=False)
        & (~d9['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True']))
        & (~d9['客户'].astype(str).str.contains('宝锐|宝泰|横琴', na=False, regex=True))
        & (~d9['业务关闭'].astype(str).str.contains('作废|业务终止', na=False, regex=True)))
dd = d9[filt]
print('口径B：9月命中该部门历史货号 %d 行（过标准口径 %d 行）｜ 金额 %.2f'
      % (len(d9), len(dd), dd['价税合计'].sum()))

by = (dd.groupby(['货号#', '物料名称'], dropna=False)
        .agg(金额=('价税合计', 'sum'), 数量=('销售数量', 'sum'), 单数=('单据编号', 'nunique'))
        .sort_values('金额', ascending=False))
print('\n按货号汇总（%d 个货号，合计 %.2f）' % (len(by), by['金额'].sum()))
for (h, n), r in by.head(15).iterrows():
    print('  %-14s %-26s %10.2f  %8.2f  %2d单'
          % (str(h)[:14], str(n)[:24], r['金额'], r['数量'], r['单数']))
print('\n这些货 9 月由谁备货：', dict(dd['备货部门'].value_counts()))

os.makedirs(os.path.dirname(OUT), exist_ok=True)
with pd.ExcelWriter(OUT) as w:
    pd.DataFrame([{'口径': 'A 严格按备货部门=%s' % DEPT, '行数': len(a),
                   '金额': a['价税合计'].sum(), '说明': '9月该部门作为备货部门无任何记录'}]) \
        .to_excel(w, sheet_name='口径A_严格', index=False)
    by.reset_index().to_excel(w, sheet_name='口径B_按历史货号', index=False)
    dd[['合同日期', '发货日期', '单据编号', '客户', '销售员', '货号#', '物料名称',
        '销售数量', '价税合计', '备货部门']].sort_values('价税合计', ascending=False) \
        .to_excel(w, sheet_name='口径B_明细', index=False)
print('\n→ %s' % OUT)