78 lines
4.5 KiB
Python
78 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
"""各部门「在架SKU」卖最差排名(按白班/晚班拆分)。
|
||
|
||
⚠️ 口径要点(5月踩坑):
|
||
- 菜品库导出**无「售卖状态」字段**(导出时是"全部状态",在售/下架混在一起无法区分)。
|
||
- 因此用「当月有售(≥1件)」作为"在架"的代理 —— 下架的季节菜(披萨/牛排/汉堡线)自动排除。
|
||
- 代价: 会漏掉极少数"在架但整月真没人点"的款。要100%精确, 需用户重新导出菜品库勾选「售卖状态=售卖中」。
|
||
|
||
用法: python3 menu_onsale_ranking.py <月度账务目录>
|
||
输出: 每店每部门, 在架SKU按销量升序(白班/晚班分列), 标注濒死(≤3件)。
|
||
"""
|
||
import openpyxl, warnings, glob, sys
|
||
from collections import defaultdict
|
||
warnings.filterwarnings('ignore')
|
||
BASE=(sys.argv[1] if len(sys.argv)>1 else ".").rstrip("/")
|
||
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]
|
||
def g(p): f=glob.glob(f"{BASE}/{p}"); return f[0]
|
||
|
||
# 各部门一级分类归属(与 build_analysis 一致)
|
||
DEPTMAP={
|
||
"西湖":{"厨房":["小吃","主食","零食"],"咖啡":["咖啡","甜品","茶饮Tea","茶饮tea"]},
|
||
"滨江":{"厨房":["肉肉肉","小吃","主食","零食"],"咖啡":["咖啡","甜品点心","茶饮Tea","茶饮tea"]},
|
||
}
|
||
def dept_of(p,n,store):
|
||
p=(p or "").strip();pl=p.lower()
|
||
for d,cats in DEPTMAP[store].items():
|
||
if p in cats or (d=="厨房" and pl.startswith("brunch")): return d
|
||
if store=="西湖":
|
||
if p=="软饮": return "咖啡" if False else "调酒"
|
||
if p.startswith("精酿"): return "精酿"
|
||
if p in ["鸡尾酒","纯饮","纯饮酒"]: return "精酿" if any(k in str(n) for k in ["酒头","畅饮"]) else "调酒"
|
||
else:
|
||
if p=="无咖无醇": return "咖啡"
|
||
if p.startswith("精酿") or p in ["瓶罐精酿","瓶装精酿"]: return "精酿"
|
||
if p in ["鸡尾酒","纯饮酒","纯饮"]: return "精酿" if any(k in str(n) for k in ["酒头","畅饮"]) else "调酒"
|
||
return None
|
||
|
||
DEPTS=["厨房","咖啡","精酿","调酒"]
|
||
for store in ["西湖","滨江"]:
|
||
mf=g(f"大梦_可能实验室_{store}店_菜品库_*.xlsx"); wb=openpyxl.load_workbook(mf,data_only=True); ws=wb["菜品"]
|
||
hr,hdr=fh(ws,"菜品编码（SPUID）"); cn=hdr.index("菜品名称"); cc=hdr.index("基础分类"); cpx=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]:
|
||
nm=str(r[cn]).strip()
|
||
if nm not in menu:
|
||
try: px=float(r[cpx]) if r[cpx] not in (None,"") else None
|
||
except: px=None
|
||
menu[nm]=(str(r[cc]).split("/")[0],px)
|
||
gf=g(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={str(r[co]):(str(r[cs]) if r[cs] else None) for i,r in enumerate(ws.iter_rows(values_only=True),1) if i>hr and r[co]}
|
||
of=g(f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"); wb=openpyxl.load_workbook(of,data_only=True); ws=wb["菜品明细"]
|
||
hr,hdr=fh(ws,"订单编号"); cio=hdr.index("订单编号"); cin=hdr.index("菜品名称"); ciq=hdr.index("销售数量"); cir=hdr.index("菜品收入（元）")
|
||
sales=defaultdict(lambda:defaultdict(lambda:[0.0,0.0]))
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),1):
|
||
if i<=hr: continue
|
||
nm=str(r[cin]).strip() if r[cin] else None
|
||
if nm and nm!="--" and nm in menu:
|
||
seg=o2s.get(str(r[cio]))
|
||
if seg in ("白班","晚班"): sales[nm][seg][0]+=float(r[ciq] or 0); sales[nm][seg][1]+=float(r[cir] or 0)
|
||
print("="*66); print(f"{store}店 在架(当月有售)出品 卖最差排名"); print("="*66)
|
||
for dept in DEPTS:
|
||
items=[nm for nm in sales if dept_of(menu[nm][0],nm,store)==dept]
|
||
rows=[]
|
||
for nm in items:
|
||
wq,wr=sales[nm]["白班"]; nq,nr=sales[nm]["晚班"]; tq=wq+nq; tr=wr+nr
|
||
rows.append((tr,tq,nm,wq,nq))
|
||
rows.sort(key=lambda x:(x[0],x[1]))
|
||
dying=sum(1 for r in rows if r[1]<=3)
|
||
print(f"\n--- {dept}: 在架{len(rows)}款, 濒死(≤3件){dying}款, 卖最差Top8 ---")
|
||
for tr,tq,nm,wq,nq in rows[:8]:
|
||
print(f" 合计¥{tr:>6.0f}/{tq:>3.0f}件 (白{wq:.0f}/晚{nq:.0f}) {nm[:30]}")
|