import sys, os, json
sys.path.insert(0, os.path.expanduser('~/Desktop/Hermes输出-工作类/脚本'))
import pandas as pd
from collections import defaultdict
from erp_sales_core import load_mappings, load_sp_map, load_order_sp_map, ser2date, BAORUI_PATTERN, map_person

person_dept, lzy_custs = load_mappings()

def load_and_map(fp, use_sp_col, month_filter=None, min_date=None):
    df = pd.read_excel(fp, dtype=str)
    has_jshj = '价税合计' in df.columns
    has_sp = '销售员' in df.columns
    if has_jshj:
        df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
    else:
        rate = pd.to_numeric(df['税率%'], errors='coerce').fillna(0)
        df['_amt'] = pd.to_numeric(df['金额'], errors='coerce').fillna(0) * (1 + rate/100)
    if has_sp and use_sp_col:
        df['_sp'] = df['销售员'].fillna('').astype(str)
    else:
        order_map = load_order_sp_map()
        sp_map = load_sp_map()
        df['_oid'] = df['单据编号'].astype(str).str.strip()
        df['_sp'] = df['_oid'].map(order_map).fillna('')
        miss = df['_sp'] == ''
        df.loc[miss, '_sp'] = df.loc[miss, '客户'].astype(str).str.strip().map(sp_map).fillna('')
    df['_date'] = df['合同日期'].apply(ser2date)
    df['_month'] = df['_date'].str[:7]
    df['_day'] = df['_date'].str[:10]
    st = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
    st = st[~st['客户'].str.contains(BAORUI_PATTERN, na=False)].copy()
    st['_amt'] = st['_amt'].astype(float)
    res = defaultdict(lambda: defaultdict(float))
    for _, r in st.iterrows():
        m = r['_month']
        if not (m.startswith('2026') and len(m) == 7):
            continue
        if month_filter and m != month_filter:
            continue
        d = r['_day']
        if min_date and d < min_date:
            continue
        sp = str(r['_sp']).strip()
        cust = str(r['客户']).strip()
        amt = float(r['_amt'])
        pt = str(r.get('客户采购类型#') or r.get('产品类型#') or '')
        kpi, dept = map_person(sp, cust, person_dept, lzy_custs, pt)
        if kpi == '刘子研':
            res[cust][m] += amt
    return res

main_fp = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_2026年1月1日～8月20日订单数据.xlsx')
aug_fp = "数据/每日上传/销售订单_20260801-0824订单ERP导出.xlsx"

# 主文件：1月1日 ~ 8月20日（含销售员列）
res_main = load_and_map(main_fp, True)
# 8月文件：8月21-24 增量（无销售员列，用映射）
res_aug_tail = load_and_map(aug_fp, False, '2026-08', min_date='2026-08-21')

# 合并
merged = defaultdict(lambda: defaultdict(float))
for cust, months in res_main.items():
    for m, amt in months.items():
        merged[cust][m] += amt
for cust, months in res_aug_tail.items():
    for m, amt in months.items():
        merged[cust][m] += amt

all_months = sorted(set(m for cust in merged.values() for m in cust))
monthly_total = defaultdict(float)
for cust, months in merged.items():
    for m, amt in months.items():
        monthly_total[m] += amt

print("=== 刘子研（大客户部）月度销售额 2026年1-8月 ===")
print(f"{'月份':<10}{'销售额(元)':>16}{'万元':>10}")
for m in all_months:
    v = monthly_total[m]
    print(f"{m:<10}{v:>16,.0f}{v/1e4:>10.2f}")

print(f"\n>>> 7月: {monthly_total['2026-07']:,.0f} 元 = {monthly_total['2026-07']/1e4:.2f}万")
print(f">>> 8月(至今 8/24): {monthly_total['2026-08']:,.0f} 元 = {monthly_total['2026-08']/1e4:.2f}万")

print("\n=== 各客户 × 各月 明细（元）===")
customers = sorted(merged.keys(), key=lambda c: -sum(merged[c].values()))
hdr = "".join(f"{m[5:]}月".rjust(11) for m in all_months)
print(f"{'客户':<30}{hdr}{'合计'.rjust(12)}")
for c in customers:
    tot = sum(merged[c].values())
    row = "".join(f"{merged[c].get(m,0):,.0f}".rjust(11) for m in all_months)
    print(f"{c:<30}{row}{tot:>12,.0f}")

out = {"monthly": {m: round(monthly_total[m],2) for m in all_months},
       "customers": {c: {m: round(merged[c].get(m,0),2) for m in all_months} for c in customers}}
json.dump(out, open('/tmp/liuziyan_final.json','w',encoding='utf-8'), ensure_ascii=False, indent=1)
print("\n已保存 /tmp/liuziyan_final.json")