#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""验收核查：复星空货号明细 + Excel sheet 一览（只读，不写盘）。"""
import os

import pandas as pd

f = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents/doc_b68712feec68_刘子研三季度数据-截止至9.30(1).xls')
OUT = os.path.expanduser('~/Desktop/Hermes输出-工作类/销售分析/集团维度_货号增减归因.xlsx')

for s, yr in [('2025年销售数据', 2025), ('2026年销售数据', 2026)]:
    d = pd.read_excel(f, sheet_name=s, header=0, engine='xlrd')
    d.columns = [str(c).strip() for c in d.columns]
    d['_d'] = pd.to_datetime(d['合同日期'], errors='coerce')
    d = d[d['_d'].dt.month <= 9]
    g = d['集团客户#'].fillna('').astype(str).str.strip()
    h = d['货号#'].fillna('').astype(str).str.strip()
    sub = d[(g == '复星集团') & (h == '')]
    print('---', yr, '复星集团 空货号行:', len(sub),
          ' 金额=%.2f' % pd.to_numeric(sub['价税合计'], errors='coerce').sum())
    for _, r in sub.iterrows():
        print('    ', r['客户'], '|', r['物料名称'], '| qty', r['销售数量'],
              '| 金额', pd.to_numeric(r['价税合计'], errors='coerce'), '|', str(r['_d'])[:10])

print()
xl = pd.ExcelFile(OUT)
print('SHEETS:', xl.sheet_names)
for s in xl.sheet_names:
    dd = xl.parse(s)
    print('  %-20s rows=%-4d cols=%s' % (s, len(dd), list(dd.columns)))
print()
ov = xl.parse('集团同比总览')
print(ov[['集团', '2025同期', '2026同期', 'Δ', 'Δ%', '货号数']].to_string(index=False))
print()
fa = xl.parse('附口径_全年vs前9月')
print('附口径 合计: 2025全年=%.2f  2026前9月=%.2f  Δ=%.2f'
      % (fa['2025全年'].sum(), fa['2026前9月'].sum(), fa['Δ'].sum()))