# -*- coding: utf-8 -*-
"""HX-40 六表体检脚本（第一步：只体检，不改数据）"""
import pandas as pd, sys, io, re, json
pd.set_option('display.max_rows', 200); pd.set_option('display.width', 250)
BASE = "/sessions/0eadc283969de77d/mnt/00-总控/素材/HX-40/"

def sep(t): print("\n" + "="*30 + f" {t} " + "="*30)

# ---------- 1 ERP ----------
sep("1. ERP销售导出")
erp = pd.read_csv(BASE+"HX-40-ERP销售导出.csv", dtype=str)
print("shape:", erp.shape); print("cols:", list(erp.columns))
print("空值计数:\n", erp.isna().sum().to_string())
print("SALE_DATE 唯一值:", sorted(erp.SALE_DATE.dropna().unique()))
print("CHNL_CODE 值分布:\n", erp.CHNL_CODE.value_counts(dropna=False).to_string())
print("MAT_CODE 唯一值:", sorted(erp.MAT_CODE.dropna().unique()))
print("TAX_RATE 值分布:\n", erp.TAX_RATE.value_counts(dropna=False).to_string())
print("BILL_NO 重复条数:", erp.BILL_NO.duplicated().sum())
dups = erp[erp.BILL_NO.duplicated(keep=False)].sort_values("BILL_NO")
print("重复单号样例:\n", dups.head(12).to_string())
num = pd.to_numeric(erp.AMT_TAX, errors="coerce")
print("AMT_TAX 非数值行:\n", erp.loc[num.isna(), ["BILL_NO","AMT_TAX"]].to_string())
print("AMT_TAX 统计:\n", num.describe().to_string())
qty = pd.to_numeric(erp.QTY, errors="coerce")
print("QTY 非数值行:\n", erp.loc[qty.isna(), ["BILL_NO","QTY"]].to_string())
print("QTY<=0 行:\n", erp.loc[qty<=0].to_string())
print("AMT_TAX 极端值(|amt|>500000):\n", erp.loc[num.abs()>500000, ["BILL_NO","MAT_CODE","QTY","AMT_TAX"]].to_string())
print("CUST_CODE 缺失:\n", erp[erp.CUST_CODE.isna()].to_string())

# ---------- 2 渠道自报表 ----------
sep("2. 渠道自报表")
qz = pd.read_csv(BASE+"HX-40-渠道自报表.csv", dtype=str)
print("shape:", qz.shape); print("cols:", list(qz.columns))
print("空值:\n", qz.isna().sum().to_string())
print("区域（大区）值分布:\n", qz["区域（大区）"].value_counts(dropna=False).to_string())
print("填报日期 唯一值:", sorted(qz.填报日期.dropna().unique()))
amt = pd.to_numeric(qz["成交金额（万元）"], errors="coerce")
print("金额非数值行:\n", qz.loc[amt.isna(), ["渠道单号","成交金额（万元）","备注"]].to_string())
print("金额统计(万元):\n", amt.describe().to_string())
print("疑似重复单号:\n", qz[qz.渠道单号.duplicated(keep=False)].sort_values("渠道单号").to_string())
print("备注非空值:\n", qz.备注.value_counts().to_string())
print("填报人分布:\n", qz.填报人.value_counts(dropna=False).to_string())

# ---------- 3 电商 ----------
sep("3. 电商后台导出")
raw = open(BASE+"HX-40-电商后台导出.csv", encoding="utf-8").read().splitlines()
hdr = raw[0].split(",")
print("表头 %d 列: %s" % (len(hdr), hdr))
lens = pd.Series([len(r.split(",")) for r in raw[1:]]).value_counts()
print("各行字段数分布:\n", lens.to_string())
ec = pd.read_csv(BASE+"HX-40-电商后台导出.csv", dtype=str)
print("pandas shape:", ec.shape, "cols:", list(ec.columns))
print("订单状态分布:\n", ec["订单状态"].value_counts(dropna=False).to_string())
print("店铺分布:\n", ec["店铺"].value_counts(dropna=False).to_string())
print("收件大区分布:\n", ec["收件大区"].value_counts(dropna=False).to_string())
print("下单时间 月份分布:\n", pd.to_datetime(ec["下单时间"], errors="coerce").dt.to_period("M").value_counts().sort_index().to_string())
pay = pd.to_datetime(ec["付款时间"], errors="coerce")
print("付款时间 月份分布:\n", pay.dt.to_period("M").value_counts().sort_index().to_string())
print("付款早于下单的行数:", (pay < pd.to_datetime(ec["下单时间"], errors="coerce")).sum())
if "Unnamed: 11" in ec.columns or len(ec.columns)>11:
    extra = ec.columns[-1]
    print("多出列 %r 值分布(前10):\n" % extra, ec[extra].value_counts(dropna=False).head(10).to_string())
amt2 = pd.to_numeric(ec["实付金额"], errors="coerce")
print("实付金额非数值:\n", ec.loc[amt2.isna(), ["订单编号","订单状态","实付金额"]].to_string())
print("实付金额统计:\n", amt2.describe().to_string())
print("实付金额<0 行:\n", ec.loc[amt2<0, ["订单编号","订单状态","实付金额"]].to_string())
print("订单编号重复:\n", ec[ec["订单编号"].duplicated(keep=False)].sort_values("订单编号").head(10).to_string())
print("商品编码唯一值:", sorted(ec["商品编码"].dropna().unique()))
print("数量 值分布:\n", pd.to_numeric(ec["数量"],errors="coerce").value_counts(dropna=False).to_string())

# ---------- 4 费用台账 ----------
sep("4. 费用台账")
fe = pd.read_csv(BASE+"HX-40-费用台账.csv", dtype=str)
print("shape:", fe.shape); print("cols:", list(fe.columns))
print("空值:\n", fe.isna().sum().to_string())
print("月份分布:\n", fe["月份"].value_counts(dropna=False).sort_index().to_string())
print("部门分布:\n", fe["部门"].value_counts(dropna=False).to_string())
print("科目分布:\n", fe["费用科目"].value_counts(dropna=False).to_string())
fa = pd.to_numeric(fe["金额元"], errors="coerce")
print("金额非数值:\n", fe.loc[fa.isna()].to_string())
print("金额<0 或 >300000:\n", fe.loc[(fa<0)|(fa>300000)].to_string())
print("凭证号重复:\n", fe[fe["凭证号"].duplicated(keep=False)].sort_values("凭证号").to_string())

# ---------- 5 库存 ----------
sep("5. 库存月报")
st = pd.read_csv(BASE+"HX-40-库存月报.csv", dtype=str)
print("shape:", st.shape); print("cols:", list(st.columns))
print("空值:\n", st.isna().sum().to_string())
print("快照日期分布:\n", st["快照日期"].value_counts(dropna=False).sort_index().to_string())
print("仓库分布:\n", st["仓库"].value_counts(dropna=False).to_string())
print("品类分布:\n", st["品类"].value_counts(dropna=False).to_string())
print("SKU 编码样例:", sorted(st["SKU编码"].dropna().unique())[:20], "…共", st["SKU编码"].nunique(), "个")
sq = pd.to_numeric(st["库存数量"], errors="coerce")
sa = pd.to_numeric(st["库存金额万元"], errors="coerce")
print("库存数量非数值/负:\n", st.loc[sq.isna()|(sq<0)].to_string())
print("库存金额万元 统计:\n", sa.describe().to_string())
print("金额万元>500 或 <0.01:\n", st.loc[(sa>500)|(sa<0.01)].to_string())
key = st.groupby(["快照日期","仓库","SKU编码"]).size()
print("快照×仓库×SKU 重复组合:\n", key[key>1].to_string())

# ---------- 6 目标分解 ----------
sep("6. 目标分解表")
tg = pd.read_csv(BASE+"HX-40-目标分解表.csv", dtype=str)
print("shape:", tg.shape); print("cols:", list(tg.columns))
print("空值:\n", tg.isna().sum().to_string())
print("区域值:\n", tg["区域"].value_counts(dropna=False).to_string())
print("月份值:\n", tg["月份"].value_counts(dropna=False).sort_index().to_string())
print("制定部门:\n", tg["制定部门"].value_counts(dropna=False).to_string())
ta = pd.to_numeric(tg["目标额万元"], errors="coerce")
print("目标额非数值:\n", tg.loc[ta.isna()].to_string())
print("区域×月份重复:\n", tg[tg.duplicated(["区域","月份"], keep=False)].sort_values(["区域","月份"]).to_string())

# ---------- 7 交叉:编码/主数据可连接性 ----------
sep("7. 跨表连接性检查")
print("ERP MAT_CODE ∩ 电商 商品编码:", sorted(set(erp.MAT_CODE.dropna()) & set(ec["商品编码"].dropna())))
print("ERP MAT_CODE 独有:", sorted(set(erp.MAT_CODE.dropna()) - set(ec["商品编码"].dropna())))
print("电商 商品编码 独有:", sorted(set(ec["商品编码"].dropna()) - set(erp.MAT_CODE.dropna())))
print("库存 SKU 前缀:", sorted({s.split("-")[0]+"-"+s.split("-")[1] for s in st["SKU编码"].dropna()}))
print("渠道表 大区 ∩ 目标表 区域:", sorted(set(qz["区域（大区）"].dropna()) & set(tg["区域"].dropna())))
print("渠道表大区独有:", sorted(set(qz["区域（大区）"].dropna()) - set(tg["区域"].dropna())))
print("目标表区域独有:", sorted(set(tg["区域"].dropna()) - set(qz["区域（大区）"].dropna())))
# 渠道单号 vs ERP 单号 交集（验证"不得依赖单号"）
print("ERP单号∩渠道单号:", len(set(erp.BILL_NO.dropna()) & set(qz.渠道单号.dropna())))
# 渠道表与ERP是否有同客户同日同额（疑似同一业务两种记录）
e2 = erp.copy(); e2["amt_wan"] = (pd.to_numeric(e2.AMT_TAX, errors="coerce")/10000).round(2)
q2 = qz.copy(); q2["amt_wan"] = pd.to_numeric(q2["成交金额（万元）"], errors="coerce").round(2)
mer = pd.merge(q2[["填报日期","大客户名称","amt_wan","渠道单号"]],
               e2[["SALE_DATE","amt_wan","BILL_NO"]],
               left_on=["填报日期","amt_wan"], right_on=["SALE_DATE","amt_wan"], how="inner")
print("渠道表×ERP 同日同额(万元,2位) 候选对数:", len(mer))
print(mer.head(20).to_string())
