124 lines
7.9 KiB
Python
124 lines
7.9 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
"""各部门收入起伏深挖：4部门 × 2店 × (5月vs4月)。
|
||
输出 _deepdive_<dept>.json：总览/二级分类/SKU movers(含菜品库分类供验真)/量价/渗透/班次。
|
||
"""
|
||
import openpyxl, warnings, glob, json
|
||
from collections import defaultdict
|
||
from datetime import datetime
|
||
warnings.filterwarnings('ignore')
|
||
|
||
import sys as _sys
|
||
# 用法: python3 dept_deepdive.py <本月目录> <上月目录>
|
||
# 例: python3 dept_deepdive.py ~/Downloads/大梦5月账务处理 ~/Downloads/大梦4月账务处理
|
||
_cur=_sys.argv[1].rstrip("/") if len(_sys.argv)>1 else "."
|
||
_prev=_sys.argv[2].rstrip("/") if len(_sys.argv)>2 else "."
|
||
DIRS={"上月":_prev,"本月":_cur}
|
||
OUT=_cur
|
||
|
||
def fh(ws,k):
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),1):
|
||
if r and any(c==k for c in r if c is not None): return i,[str(c).strip() if c else '' for c in r]
|
||
return None,None
|
||
def g(b,p): f=glob.glob(f"{b}/{p}"); return f[0] if f else None
|
||
|
||
def dept_of(primary, secondary, name, store):
|
||
p=(primary or "").strip(); pl=p.lower(); s=(secondary or "").strip(); n=str(name)
|
||
if any(k in n for k in ["美团团购","团购套餐","打卡套餐"]): return None
|
||
if any(k in n for k in ["扑克","点歌","雨伞","毛毯","游戏卡牌","桌游","充电","寄存"]): return None
|
||
# 类目优先
|
||
if store=="西湖":
|
||
if p in ["小吃","主食","零食"] or pl.startswith("brunch"): return "厨房"
|
||
if p in ["咖啡","甜品","茶饮Tea","茶饮tea"]: return "咖啡"
|
||
if p=="软饮": return "咖啡" if any(k in s for k in ["可尔必思","海盐荔枝"]) else "调酒"
|
||
if p.startswith("精酿"): return "精酿"
|
||
if p in ["鸡尾酒","纯饮","纯饮酒"]: return "调酒" if not any(k in n for k in ["酒头","畅饮"]) else "精酿"
|
||
else:
|
||
if p in ["肉肉肉","小吃","主食","零食"] or pl.startswith("brunch"): return "厨房"
|
||
if p in ["咖啡","甜品点心","茶饮Tea","茶饮tea"]: return "咖啡"
|
||
if p=="无咖无醇": return "调酒" if "无醇鸡尾酒" in s else "咖啡"
|
||
if p.startswith("精酿") or p in ["瓶罐精酿","瓶装精酿"]: return "精酿"
|
||
if p in ["鸡尾酒","纯饮酒","纯饮"]: return "调酒" if not any(k in n for k in ["酒头","畅饮"]) else "精酿"
|
||
# 兜底按名
|
||
if any(k in n for k in ["酒头","畅饮","IPA","Lager","Stout","Ale","拉格","精酿","世涛","酸啤","古斯","Gose","西打","啤酒","札幌","健力士","三宝乐","制乐场","制乐厂","沙坡尾","气泡实验室","做梦去吧","滇麻","果酒","气泡酒"]): return "精酿"
|
||
if any(k in n for k in ["特调","鸡尾酒","威士忌","金酒","朗姆","龙舌兰","伏特加","僵尸","Zombie","Negroni","内格罗尼","Margarita","玛格丽特","Old Fashion","古典","SHOT","清酒","葡萄酒","红酒","白葡萄","金刚芭比","混四喜"]): return "调酒"
|
||
if any(k in n for k in ["美式","拿铁","咖啡","冷萃","澳白","Dirty","卡布","摩卡","瑰夏","耶加","曼特宁","葡萄成熟","优格","冰淇淋","奶昔","波旁","庄园","茶","龙井","乌龙","普洱","大梦冰茶","果茶"]): return "咖啡"
|
||
if any(k in n for k in ["拼盘","小食","沙拉","Tacos","吐司","蛋","焗饭","意面","薯","鸡","牛肉","猪","披萨","早餐","三明治","汉堡","面包","可颂","煮蛋"]): return "厨房"
|
||
return None
|
||
|
||
def load(base, store):
|
||
mf=g(base,f"大梦_可能实验室_{store}店_菜品库_*.xlsx"); wb=openpyxl.load_workbook(mf,data_only=True); ws=wb["菜品"]
|
||
hr,hdr=fh(ws,"菜品编码（SPUID）"); cn=hdr.index("菜品名称"); cc=hdr.index("基础分类")
|
||
menu={}
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),1):
|
||
if i<=hr: continue
|
||
if r[cn] and r[cc]:
|
||
parts=str(r[cc]).split("/"); menu[str(r[cn]).strip()]=(parts[0],parts[1] if len(parts)>1 else "")
|
||
gf=g(base,f"大梦可能实验室（{store}店）_全渠道订单明细_*.xlsx"); wb=openpyxl.load_workbook(gf,data_only=True); ws=wb.active
|
||
hr,hdr=fh(ws,"营业日期"); co=hdr.index("订单号"); cs=hdr.index("餐段")
|
||
o2s={}
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),1):
|
||
if i<=hr: continue
|
||
if r[co]: o2s[str(r[co])]=str(r[cs]) if r[cs] else None
|
||
df=g(base,f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"); wb=openpyxl.load_workbook(df,data_only=True); ws=wb["菜品明细"]
|
||
hr,hdr=fh(ws,"订单编号"); ci_o=hdr.index("订单编号"); ci_n=hdr.index("菜品名称"); ci_q=hdr.index("销售数量"); ci_r=hdr.index("菜品收入（元）")
|
||
rows=[]
|
||
tot_orders=set()
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),1):
|
||
if i<=hr: continue
|
||
name=str(r[ci_n]).strip() if r[ci_n] else None
|
||
if not name or name=="--": continue
|
||
o=str(r[ci_o]); tot_orders.add(o)
|
||
cat=menu.get(name); p=cat[0] if cat else ""; s=cat[1] if cat else ""
|
||
rows.append((o,name,p,s,float(r[ci_q] or 0),float(r[ci_r] or 0),o2s.get(o)))
|
||
return rows, len(tot_orders), menu
|
||
|
||
def decompose(dept):
|
||
res={}
|
||
for store in ["西湖","滨江"]:
|
||
for m,base in DIRS.items():
|
||
rows,tot_orders,menu=load(base,store)
|
||
sku=defaultdict(lambda:[0.0,0.0]); sec=defaultdict(lambda:[0.0,0.0]); shift=defaultdict(float)
|
||
ow=set(); trev=0.0; tqty=0.0
|
||
skucat={}
|
||
for o,name,p,s,q,rev,seg in rows:
|
||
if dept_of(p,s,name,store)!=dept: continue
|
||
sku[name][0]+=q; sku[name][1]+=rev
|
||
skucat[name]=f"{p}/{s}" if s else (p or "无库")
|
||
sk=(s or p or "其他"); sec[sk][0]+=q; sec[sk][1]+=rev
|
||
trev+=rev; tqty+=q; ow.add(o)
|
||
if seg in ("白班","晚班"): shift[seg]+=rev
|
||
res[(store,m)]={"rev":trev,"qty":tqty,"orders_with":len(ow),"tot_orders":tot_orders,
|
||
"attach":len(ow)/tot_orders if tot_orders else 0,"avg_price":trev/tqty if tqty else 0,
|
||
"sec":{k:v[1] for k,v in sec.items()},"shift":dict(shift),
|
||
"sku":{k:v[1] for k,v in sku.items()},"skucat":skucat}
|
||
# movers per store
|
||
movers={}
|
||
for store in ["西湖","滨江"]:
|
||
a=res[(store,"上月")]["sku"]; b=res[(store,"本月")]["sku"]
|
||
ca=res[(store,"上月")]["skucat"]; cb=res[(store,"本月")]["skucat"]
|
||
names=set(a)|set(b); mv=[]
|
||
for n in names:
|
||
ra=a.get(n,0); rb=b.get(n,0)
|
||
mv.append({"sku":n,"apr":round(ra),"may":round(rb),"delta":round(rb-ra),
|
||
"cat_apr":ca.get(n,"-"),"cat_may":cb.get(n,"-")})
|
||
mv.sort(key=lambda x:-x["delta"])
|
||
movers[store]={"up":[m for m in mv if m["delta"]>0][:10],"down":[m for m in mv if m["delta"]<0][-10:]}
|
||
out={"dept":dept,
|
||
"totals":{f"{s}|{m}":{k:round(res[(s,m)][k],1) for k in ["rev","qty","orders_with","tot_orders","attach","avg_price"]} for s in ["西湖","滨江"] for m in ["上月","本月"]},
|
||
"sec":{f"{s}|{m}":{k:round(v) for k,v in res[(s,m)]["sec"].items() if v>30} for s in ["西湖","滨江"] for m in ["上月","本月"]},
|
||
"shift":{f"{s}|{m}":{k:round(v) for k,v in res[(s,m)]["shift"].items()} for s in ["西湖","滨江"] for m in ["上月","本月"]},
|
||
"movers":movers}
|
||
with open(f"{OUT}/_deepdive_{dept}.json","w") as f: json.dump(out,f,ensure_ascii=False,indent=1)
|
||
# 简报
|
||
print(f"\n{'='*60}\n{dept}\n{'='*60}")
|
||
for s in ["西湖","滨江"]:
|
||
a=res[(s,'上月')]['rev']; b=res[(s,'本月')]['rev']
|
||
print(f" {s}: 4月{a:>9.0f} → 5月{b:>9.0f} ({(b-a)/a*100 if a else 0:+.0f}%) | 渗透{res[(s,'上月')]['attach']*100:.0f}%→{res[(s,'本月')]['attach']*100:.0f}% 均价{res[(s,'上月')]['avg_price']:.0f}→{res[(s,'本月')]['avg_price']:.0f}")
|
||
return out
|
||
|
||
if __name__=="__main__":
|
||
for d in ["厨房","咖啡","精酿","调酒"]:
|
||
decompose(d)
|
||
print(f"\n✓ 4部门 decomposition JSON 已存 {OUT}/_deepdive_*.json")
|