#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""刘子研名下 集团/公司 2025 vs 2026 前三季度（Q1-Q3）销售额同比 + 货号下钻。
数据源：用户表《刘子研三季度数据-截止至9.30.xls》 sheet「2026年销售数据」+「2025年销售数据」
口径：标准销售订单 · 已审核 · 非赠品 · 排除关联方；季度按【合同日期】切
"""
import os
import sys
import pandas as pd

USR = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents/doc_6f16ee249d8e_刘子研三季度数据-截止至9.30(1).xls')
OUTDIR = os.path.expanduser('~/Desktop/Hermes输出-工作类/销售分析')


def load(sheet):
    d = pd.read_excel(USR, sheet_name=sheet, header=0, engine='xlrd')
    d.columns = [str(c).strip() for c in d.columns]
    hk = '货号#' if '货号#' in d.columns else '货号'
    d['_sku'] = d[hk].fillna('（未填货号）').astype(str).str.strip().replace({'': '（未填货号）', 'nan': '（未填货号）'})
    d['_d'] = pd.to_datetime(d['合同日期'], errors='coerce')
    d['_yr'] = d['_d'].dt.year
    d['_q'] = d['_d'].dt.quarter
    d['_amt'] = pd.to_numeric(d['价税合计'], errors='coerce').fillna(0)
    d['_cust'] = d['客户'].astype(str).str.strip()
    gcol = None
    for c in ('集团客户#', '集团客户'):
        if c in d.columns:
            gcol = c
            break
    if gcol:
        g = d[gcol].fillna('').astype(str).str.strip().replace({'nan': '', 'None': ''})
        d['_grp'] = g.where(g != '', d['_cust'])
    else:
        d['_grp'] = d['_cust']
    return d, gcol


def clean(d):
    k = d['单据类型'].astype(str).str.contains('标准销售订单', na=False)
    k &= d['单据状态'].astype(str).str.contains('已审核', na=False)
    k &= ~d['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true'])
    k &= ~d['_cust'].str.contains('宝锐|宝泰|横琴', na=False, regex=True)
    k &= d['_q'].isin([1, 2, 3])
    return d[k].copy()


def main():
    d26a, g26 = load('2026年销售数据')
    d25a, g25 = load('2025年销售数据')
    print('列名: 2026 集团列=%s / 2025 集团列=%s' % (g26, g25))
    d26, d25 = clean(d26a), clean(d25a)
    print('前三季度(1-9月) 过滤后: 2025 %d 行 ¥%.2f ｜ 2026 %d 行 ¥%.2f'
          % (len(d25), d25['_amt'].sum(), len(d26), d26['_amt'].sum()))

    a = d25.groupby('_grp').agg(额25=('_amt', 'sum'), 单25=('单据编号', 'nunique')).reset_index()
    b = d26.groupby('_grp').agg(额26=('_amt', 'sum'), 单26=('单据编号', 'nunique')).reset_index()
    m = a.merge(b, on='_grp', how='outer').fillna(0)
    m['Δ'] = m['额26'] - m['额25']
    m['同比%'] = (m['Δ'] / m['额25'].replace(0, float('nan')) * 100).round(1)
    m = m.sort_values('额26', ascending=False)
    print('\n══ 集团/公司 前三季度同比（元）══')
    print('%-34s %12s %12s %12s %8s %5s %5s' % ('集团/公司', '2025Q1-3', '2026Q1-3', 'Δ', '同比%', '单25', '单26'))
    print('-' * 100)
    for _, r in m.iterrows():
        print('%-34s %12.2f %12.2f %12.2f %8s %5d %5d'
              % (str(r['_grp'])[:34], r['额25'], r['额26'], r['Δ'],
                 ('%.1f' % r['同比%']) if pd.notna(r['同比%']) else '—', r['单25'], r['单26']))
    print('-' * 100)
    print('%-34s %12.2f %12.2f %12.2f %8s' % ('【合计】', m['额25'].sum(), m['额26'].sum(), m['Δ'].sum(),
                                              '%.1f' % ((m['额26'].sum() / m['额25'].sum() - 1) * 100)))
    print('\n集团数: 2025 %d ｜ 2026 %d'
          % ((m['额25'] > 0).sum(), (m['额26'] > 0).sum()))

    # 货号下钻
    s25 = d25.groupby(['_grp', '_sku'])['_amt'].sum().rename('额25')
    s26 = d26.groupby(['_grp', '_sku'])['_amt'].sum().rename('额26')
    sk = pd.concat([s25, s26], axis=1).fillna(0).reset_index()
    sk['Δ'] = sk['额26'] - sk['额25']
    sk = sk.sort_values(['_grp', 'Δ'])
    print('\n══ 各集团 Top 涨/跌货号（|Δ|>0）══')
    for g in m['_grp']:
        sub = sk[sk['_grp'] == g]
        down = sub[sub['Δ'] < 0].head(5)
        up = sub[sub['Δ'] > 0].sort_values('Δ', ascending=False).head(4)
        print('\n▶ %s' % g)
        for _, r in down.iterrows():
            print('   ▼ %-30s %10.0f → %10.0f  Δ%+10.0f' % (str(r['_sku'])[:30], r['额25'], r['额26'], r['Δ']))
        for _, r in up.iterrows():
            print('   ▲ %-30s %10.0f → %10.0f  Δ%+10.0f' % (str(r['_sku'])[:30], r['额25'], r['额26'], r['Δ']))

    os.makedirs(OUTDIR, exist_ok=True)
    out = os.path.join(OUTDIR, '刘子研_集团前三季度同比_2025vs2026.xlsx')
    with pd.ExcelWriter(out) as w:
        m.rename(columns={'_grp': '集团/公司'}).to_excel(w, sheet_name='集团同比', index=False)
        sk.rename(columns={'_grp': '集团/公司', '_sku': '货号'}).to_excel(w, sheet_name='集团x货号', index=False)
        pd.concat([d25.assign(年份=2025), d26.assign(年份=2026)])[
            ['年份', '_d', '单据编号', '_cust', '_grp', '_sku', '物料名称', '销售数量', '价税合计', '销售员']
        ].rename(columns={'_d': '合同日期', '_cust': '客户', '_grp': '集团客户', '_sku': '货号'}) \
            .sort_values(['年份', '合同日期']).to_excel(w, sheet_name='明细', index=False)
    print('\n→ %s' % out)
    return m, sk


if __name__ == '__main__':
    main()