534 lines
29 KiB
Python
534 lines
29 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
"""大梦 by 可能实验室 — 月度营收分析引擎（按月参数化）
|
||
|
||
用法: python3 build_analysis.py <月度账务目录> [YYYY-MM] # 默认 2026-05
|
||
生成 大梦可能实验室_{N}月营收分析_西湖店vs滨江店.xlsx（13 sheets）+ _analysis_summary.json，
|
||
并打印工资所需的部门/班次/部门×班次业绩三元组。
|
||
|
||
⚠️ 跑本脚本前先跑 make_menu_lib.py 生成当月菜品库，否则新上 SKU 会落到关键词兜底。
|
||
|
||
数据源（同目录）:
|
||
POS 店内订单明细 (菜品明细 / 订单明细 / 优惠明细 sheets)
|
||
全渠道订单明细 (含 餐段)
|
||
菜品库 (SKU→基础分类)
|
||
团购收益(美团大梦) + 新版收益(点评/可能实验室)
|
||
"""
|
||
import openpyxl, warnings, glob, sys, re
|
||
from collections import defaultdict
|
||
warnings.filterwarnings('ignore')
|
||
|
||
import sys as _sys, calendar as _cal
|
||
# 用法: python3 build_analysis.py <月度账务目录> [YYYY-MM]
|
||
BASE = (_sys.argv[1] if len(_sys.argv) > 1 else ".").rstrip("/")
|
||
MONTH = _sys.argv[2] if len(_sys.argv) > 2 else "2026-05"
|
||
_y,_m = int(MONTH[:4]), int(MONTH[5:7])
|
||
DAYS = _cal.monthrange(_y,_m)[1]
|
||
|
||
# ===================== 部门归口规则（菜品一级分类）=====================
|
||
def dept_of_category(primary, secondary, store):
|
||
p = (primary or "").strip()
|
||
pl = p.lower()
|
||
s = (secondary or "").strip()
|
||
if store == "西湖":
|
||
if p in ["小吃","主食","零食"] or pl.startswith("brunch"):
|
||
return "厨房"
|
||
if p in ["咖啡","甜品"] or 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 "调酒"
|
||
else: # 滨江
|
||
if p in ["肉肉肉","小吃","主食","零食"] or pl.startswith("brunch"):
|
||
return "厨房"
|
||
if p in ["咖啡","甜品点心"] or p in ["茶饮Tea","茶饮tea"]:
|
||
return "咖啡"
|
||
if p == "无咖无醇":
|
||
return "调酒" if "无醇鸡尾酒" in s else "咖啡"
|
||
if p.startswith("精酿") or p in ["瓶罐精酿","瓶装精酿"]:
|
||
return "精酿"
|
||
if p in ["鸡尾酒","纯饮酒","纯饮"]:
|
||
return "调酒"
|
||
return None # 其他/团购套餐/特惠套餐/加料 → 需 SKU 级处理或跳过
|
||
|
||
# 周边/服务（非部门，单列）
|
||
def is_peripheral(name):
|
||
n=str(name)
|
||
return any(k in n for k in ["扑克","点歌","雨伞","毛毯","游戏卡牌","桌游","充电","寄存"])
|
||
# POS 端团购套餐壳（收入计平台侧，POS 侧多为 0，跳过避免重复）
|
||
def is_teamgou_shell(name):
|
||
return "美团团购" in str(name) or "团购套餐" in str(name) or "打卡套餐" in str(name)
|
||
|
||
# 「其他」类 SKU 按菜品名关键词推断部门（复刻 4 月 SKU重归类思路）
|
||
# 注意：酒类关键词优先（避免"果酒优格奶昔"等被咖啡词误捕）
|
||
def dept_by_name(name, store):
|
||
n = str(name)
|
||
# —— 精酿（啤酒/西打/果酒/气泡酒/品牌）优先 ——
|
||
# 酒头/畅饮票=扎啤生啤(精酿)，与菜品库同类SKU一致
|
||
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 ["茶","龙井","乌龙","普洱","大梦冰茶","果茶"]):
|
||
return "咖啡"
|
||
# —— 厨房 ——
|
||
if any(k in n for k in ["拼盘","小食","沙拉","Tacos","吐司","蛋","焗饭","意面","薯","鸡","牛肉","猪","披萨","brunch","早餐","三明治","汉堡","面包","可颂","煮蛋"]):
|
||
return "厨房"
|
||
return None
|
||
|
||
# ===================== 团购项目 → 部门 =====================
|
||
def teamgou_dept_split(name):
|
||
"""返回 {dept: ratio} 或 None(=代金券跳过)"""
|
||
n = str(name)
|
||
if "代金券" in n:
|
||
return None # 跳过，POS 已计入
|
||
# 拆分套餐（先匹配，避免被"厨房/咖啡"单部门误判）
|
||
if "单人轻食" in n or "温馨时光" in n:
|
||
return {"厨房":0.6, "咖啡":0.4}
|
||
if "营养满溢" in n or ("南瓜沙拉" in n and "Tacos" in n):
|
||
return {"厨房":0.7, "咖啡":0.3}
|
||
if "豪华烤肉拼盘" in n:
|
||
return {"厨房":0.7, "调酒":0.3}
|
||
if "香菜" in n and ("葡萄酒" in n or "Tacos" in n):
|
||
return {"厨房":0.5, "调酒":0.5}
|
||
# 单部门
|
||
if any(k in n for k in ["咖啡任选","经典咖啡","美式","下午茶","白日梦"]):
|
||
return {"咖啡":1.0}
|
||
if "精酿" in n or "盲盒" in n:
|
||
return {"精酿":1.0}
|
||
if any(k in n for k in ["鸡尾酒","SHOT","小酌","HappyHour"]):
|
||
# HappyHour 精酿 已被上面拦截；这里是鸡尾酒/SHOT
|
||
if "精酿" in n: return {"精酿":1.0}
|
||
return {"调酒":1.0}
|
||
return {"未拆分":1.0}
|
||
|
||
# ===================== 读取工具 =====================
|
||
def find_header(ws, key):
|
||
for i, r in enumerate(ws.iter_rows(values_only=True), start=1):
|
||
if r and any(c == key for c in r if c is not None):
|
||
return i, [str(c).strip() if c is not None else '' for c in r]
|
||
return None, None
|
||
|
||
def g(path_glob):
|
||
fs = glob.glob(f"{BASE}/{path_glob}")
|
||
if not fs: sys.exit(f"缺少文件: {path_glob}")
|
||
return fs[0]
|
||
|
||
STORES = {"西湖":"西湖", "滨江":"滨江"}
|
||
|
||
# ===================== 1) 菜品库: SKU/名称 → 分类 =====================
|
||
def load_menu(store):
|
||
wb = openpyxl.load_workbook(g(f"大梦_可能实验室_{store}店_菜品库_*.xlsx"), data_only=True)
|
||
ws = wb["菜品"]; hr,hdr = find_header(ws,"菜品编码（SPUID）")
|
||
ci_name=hdr.index("菜品名称"); ci_cat=hdr.index("基础分类")
|
||
name2cat={}
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
|
||
if i<=hr: continue
|
||
if r[ci_name] and r[ci_cat]:
|
||
parts=str(r[ci_cat]).split("/")
|
||
name2cat[str(r[ci_name]).strip()]=(parts[0], parts[1] if len(parts)>1 else "")
|
||
return name2cat
|
||
|
||
# ===================== 2) 全渠道: 订单号 → 餐段 =====================
|
||
def load_shift(store):
|
||
wb=openpyxl.load_workbook(g(f"大梦可能实验室（{store}店）_全渠道订单明细_*.xlsx"),data_only=True)
|
||
ws=wb.active; hr,hdr=find_header(ws,"营业日期")
|
||
ci_o=hdr.index("订单号"); ci_s=hdr.index("餐段")
|
||
ci_pay=hdr.index("顾客实付"); ci_amt=hdr.index("订单金额")
|
||
o2shift={}; shift_rows=[]
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
|
||
if i<=hr: continue
|
||
if r[ci_o]:
|
||
sh=str(r[ci_s]) if r[ci_s] else None
|
||
o2shift[str(r[ci_o])]=sh
|
||
shift_rows.append((sh, float(r[ci_pay] or 0), float(r[ci_amt] or 0)))
|
||
return o2shift, shift_rows
|
||
|
||
# ===================== 3) POS 订单明细 (顾客应付/服务费/优惠/每日) =====================
|
||
def load_orders(store):
|
||
wb=openpyxl.load_workbook(g(f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"),data_only=True)
|
||
ws=wb["订单明细"]; hr,hdr=find_header(ws,"营业日期")
|
||
idx={k:hdr.index(k) for k in ["营业日期","订单号","订单金额（元）","顾客应付（元）","订单优惠（元）","菜品收入（元）","服务费收入（元）"]}
|
||
orders=[]
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
|
||
if i<=hr: continue
|
||
no=r[idx["订单号"]]
|
||
if not no or str(no).strip()=="--": continue # 跳过幽灵汇总行
|
||
if str(r[idx["营业日期"]]).strip()=="--": continue
|
||
orders.append({
|
||
"date":str(r[idx["营业日期"]]),
|
||
"order":str(r[idx["订单号"]]),
|
||
"amount":float(r[idx["订单金额（元）"]] or 0),
|
||
"payable":float(r[idx["顾客应付（元）"]] or 0),
|
||
"discount":float(r[idx["订单优惠（元）"]] or 0),
|
||
"dish_rev":float(r[idx["菜品收入（元）"]] or 0),
|
||
"service":float(r[idx["服务费收入（元）"]] or 0),
|
||
})
|
||
# 优惠明细(赠菜)
|
||
ws2=wb["优惠明细"]; hr2,hdr2=find_header(ws2,"营业日期")
|
||
gifts=[]
|
||
if hr2:
|
||
gi={k:(hdr2.index(k) if k in hdr2 else None) for k in ["营业日期","折扣优惠类型","折扣优惠名称","金额(¥)","折扣金额（元）","优惠金额（元）"]}
|
||
amt_col = gi["金额(¥)"] or gi["折扣金额（元）"] or gi["优惠金额（元）"]
|
||
for i,r in enumerate(ws2.iter_rows(values_only=True),start=1):
|
||
if i<=hr2: continue
|
||
typ=str(r[gi["折扣优惠类型"]]) if gi["折扣优惠类型"] is not None and r[gi["折扣优惠类型"]] else ""
|
||
if "赠菜" in typ:
|
||
gifts.append({"date":str(r[gi["营业日期"]]), "amt":float(r[amt_col] or 0) if amt_col is not None else 0})
|
||
# 支付方式分布
|
||
ws3=wb["支付明细"]; hr3,hdr3=find_header(ws3,"支付方式") if find_header(ws3,"支付方式")[0] else (None,None)
|
||
pays=defaultdict(lambda:[0,0.0])
|
||
if hr3:
|
||
pi_way=hdr3.index("支付方式")
|
||
pi_amt=None
|
||
for cand in ["支付金额（元）","实收金额（元）","金额（元）","支付金额(¥)"]:
|
||
if cand in hdr3: pi_amt=hdr3.index(cand); break
|
||
for i,r in enumerate(ws3.iter_rows(values_only=True),start=1):
|
||
if i<=hr3: continue
|
||
if r[pi_way]:
|
||
pays[str(r[pi_way])][0]+=1
|
||
pays[str(r[pi_way])][1]+=float(r[pi_amt] or 0) if pi_amt is not None else 0
|
||
return orders, gifts, dict(pays)
|
||
|
||
# ===================== 4) POS 菜品明细 → 部门/分类/班次 =====================
|
||
def load_dishes(store, name2cat, o2shift):
|
||
wb=openpyxl.load_workbook(g(f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"),data_only=True)
|
||
ws=wb["菜品明细"]; hr,hdr=find_header(ws,"订单编号")
|
||
ci_o=hdr.index("订单编号"); ci_name=hdr.index("菜品名称")
|
||
ci_qty=hdr.index("销售数量"); ci_amt=hdr.index("金额合计（元）")
|
||
ci_disc=hdr.index("菜品优惠（元）"); ci_rev=hdr.index("菜品收入（元）")
|
||
dept_pos=defaultdict(float) # dept -> 菜品收入
|
||
cross=defaultdict(float) # (dept,shift) -> 菜品收入
|
||
cat1=defaultdict(lambda:[0,0.0,0.0,0.0]) # primary -> [qty, 原价, 优惠, 收入]
|
||
cat2=defaultdict(lambda:[set(),0,0.0,0.0,0.0]) # basecat -> [skus,qty,原价,优惠,收入]
|
||
other_skus=defaultdict(lambda:[0,0.0]) # name -> [qty, rev] (其他/未归类)
|
||
unmatched=0
|
||
for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
|
||
if i<=hr: continue
|
||
name=str(r[ci_name]).strip() if r[ci_name] else None
|
||
if not name: continue
|
||
rev=float(r[ci_rev] or 0); amt=float(r[ci_amt] or 0)
|
||
disc=float(r[ci_disc] or 0); qty=float(r[ci_qty] or 0)
|
||
cat=name2cat.get(name)
|
||
primary = cat[0] if cat else "（无菜品库）"
|
||
secondary = cat[1] if cat else ""
|
||
basecat = f"{primary}/{secondary}" if secondary else primary
|
||
cat1[primary][0]+=qty; cat1[primary][1]+=amt; cat1[primary][2]+=disc; cat1[primary][3]+=rev
|
||
cat2[basecat][0].add(name); cat2[basecat][1]+=qty; cat2[basecat][2]+=amt; cat2[basecat][3]+=disc; cat2[basecat][4]+=rev
|
||
if is_teamgou_shell(name) or is_peripheral(name):
|
||
other_skus[name][0]+=qty; other_skus[name][1]+=rev
|
||
continue
|
||
dept = dept_of_category(primary, secondary, store)
|
||
if dept is None:
|
||
dept = dept_by_name(name, store)
|
||
if dept is None:
|
||
other_skus[name][0]+=qty; other_skus[name][1]+=rev
|
||
unmatched+=1
|
||
continue
|
||
dept_pos[dept]+=rev
|
||
sh=o2shift.get(str(r[ci_o]))
|
||
if sh in ("白班","晚班"):
|
||
cross[(dept,sh)]+=rev
|
||
return dept_pos, cross, cat1, cat2, other_skus, unmatched
|
||
|
||
# ===================== 5) 团购 → 部门 =====================
|
||
def load_teamgou():
|
||
# 美团大梦
|
||
rows=[] # (store, brand, name, cnt, price, settle)
|
||
wb=openpyxl.load_workbook(g("42323734_团购收益明细_*.xlsx"),data_only=True)
|
||
ws=wb["收益明细表"]; data=list(ws.iter_rows(values_only=True))
|
||
hdr=[str(c).strip() if c else '' for c in data[1]]
|
||
ci_store=hdr.index("消费门店"); ci_pkg=hdr.index("套餐名")
|
||
ci_total=hdr.index("总收入（元）")
|
||
ci_settle=hdr.index("结算价(总收入-美团点评技术服务费-商家营销费用-消费后退-其他调整)（元）")
|
||
ag=defaultdict(lambda:[0,0.0,0.0])
|
||
for r in data[2:]:
|
||
if not r[ci_pkg]: continue
|
||
store="西湖" if "西湖" in str(r[ci_store]) else "滨江"
|
||
k=(store,"大梦",str(r[ci_pkg]))
|
||
ag[k][0]+=1; ag[k][1]+=float(r[ci_total] or 0); ag[k][2]+=float(r[ci_settle] or 0)
|
||
# 点评可能实验室
|
||
wb2=openpyxl.load_workbook(g("新版收益明细_*团购_*.xlsx"),data_only=True)
|
||
ws2=wb2["收益明细"]; data2=list(ws2.iter_rows(values_only=True))
|
||
hdr2=[str(c).strip() if c else '' for c in data2[0]]
|
||
ci_store2=hdr2.index("美团门店名称"); ci_pkg2=hdr2.index("项目名称")
|
||
ci_price2=hdr2.index("售价（美团售价）"); ci_merch2=hdr2.index("商家应得")
|
||
for r in data2[1:]:
|
||
if not r[ci_pkg2]: continue
|
||
store="西湖" if "西湖" in str(r[ci_store2]) else "滨江"
|
||
k=(store,"可能实验室",str(r[ci_pkg2]))
|
||
ag[k][0]+=1; ag[k][1]+=float(r[ci_price2] or 0); ag[k][2]+=float(r[ci_merch2] or 0)
|
||
# 拆部门（用结算/商家应得）
|
||
dept_tg=defaultdict(lambda: defaultdict(float)) # store -> dept -> settle
|
||
voucher=defaultdict(lambda:[0,0.0,0.0]) # store -> [cnt,price,settle]
|
||
detail=[]
|
||
for (store,brand,name),(c,p,s) in sorted(ag.items()):
|
||
split=teamgou_dept_split(name)
|
||
if split is None:
|
||
voucher[store][0]+=c; voucher[store][1]+=p; voucher[store][2]+=s
|
||
detail.append((brand,store,name,c,p,s,"⚠️代金券跳过","—"))
|
||
continue
|
||
rule="; ".join(f"{d} {int(r*100)}%" for d,r in split.items())
|
||
for d,ratio in split.items():
|
||
dept_tg[store][d]+=s*ratio
|
||
detail.append((brand,store,name,c,p,s,rule,rule))
|
||
return dept_tg, voucher, detail
|
||
|
||
# ===================== 主流程 =====================
|
||
def main():
|
||
result={}
|
||
menus={st:load_menu(st) for st in STORES}
|
||
shifts={st:load_shift(st) for st in STORES}
|
||
dept_pos={}; cross={}; cat1={}; cat2={}; others={}; unmatched={}
|
||
orders={}; gifts={}; pays={}
|
||
for st in STORES:
|
||
o2shift=shifts[st][0]
|
||
dept_pos[st],cross[st],cat1[st],cat2[st],others[st],unmatched[st]=load_dishes(st,menus[st],o2shift)
|
||
orders[st],gifts[st],pays[st]=load_orders(st)
|
||
# 每日营收
|
||
daily={}
|
||
for st in STORES:
|
||
dd=defaultdict(lambda:[0,0.0,0.0,0.0]) # date->[orders,amount,payable,discount]
|
||
for o in orders[st]:
|
||
d=o["date"]
|
||
dd[d][0]+=1; dd[d][1]+=o["amount"]; dd[d][2]+=o["payable"]; dd[d][3]+=o["discount"]
|
||
gd=defaultdict(lambda:[0,0.0])
|
||
for gft in gifts[st]:
|
||
gd[gft["date"]][0]+=1; gd[gft["date"]][1]+=gft["amt"]
|
||
daily[st]={d:(dd[d],gd.get(d,[0,0.0])) for d in sorted(dd)}
|
||
dept_tg, voucher, tg_detail = load_teamgou()
|
||
|
||
# ---- 部门收入合计 ----
|
||
DEPTS=["厨房","咖啡","精酿","调酒"]
|
||
print("="*72)
|
||
print(f" 大梦 {_m} 月营收分析 — 部门收入（POS菜品 + 团购套餐结算）")
|
||
print("="*72)
|
||
print(f" {'部门':<6}{'滨江POS':>11}{'滨江团购':>10}{'滨江合计':>11}{'西湖POS':>11}{'西湖团购':>10}{'西湖合计':>11}")
|
||
dept_total={}
|
||
for d in DEPTS:
|
||
bp=dept_pos["滨江"].get(d,0); bt=dept_tg["滨江"].get(d,0); bc=bp+bt
|
||
xp=dept_pos["西湖"].get(d,0); xt=dept_tg["西湖"].get(d,0); xc=xp+xt
|
||
dept_total[d]={"滨江":bc,"西湖":xc}
|
||
print(f" {d:<6}{bp:>11.2f}{bt:>10.2f}{bc:>11.2f}{xp:>11.2f}{xt:>10.2f}{xc:>11.2f}")
|
||
|
||
# ---- 班次营收 ----
|
||
shift_sum=defaultdict(lambda:[0,0.0,0.0]) # (store,shift)->[orders, amount, payable]
|
||
for st in STORES:
|
||
for sh,pay,amt in shifts[st][1]:
|
||
if sh in ("白班","晚班"):
|
||
shift_sum[(st,sh)][0]+=1
|
||
shift_sum[(st,sh)][1]+=amt
|
||
shift_sum[(st,sh)][2]+=pay
|
||
print("\n 班次营收（顾客实付）:")
|
||
for st in STORES:
|
||
for sh in ["白班","晚班"]:
|
||
o,a,p=shift_sum[(st,sh)]
|
||
print(f" {st}店 {sh}: 订单{o:>5} 实付{p:>11.2f} 客单{p/o if o else 0:>7.2f}")
|
||
|
||
# ---- 部门×班次（按部门合计比例校正）----
|
||
print("\n 部门×班次（校正到部门合计）:")
|
||
cross_scaled={}
|
||
for st in STORES:
|
||
for d in DEPTS:
|
||
raw_sum=sum(cross[st].get((d,sh),0) for sh in ["白班","晚班"])
|
||
target=dept_total[d][st]
|
||
for sh in ["白班","晚班"]:
|
||
raw=cross[st].get((d,sh),0)
|
||
cross_scaled[(st,d,sh)]= round(raw/raw_sum*target) if raw_sum>0 else 0
|
||
for st in STORES:
|
||
line=f" {st}: "+" ".join(f"{d}(白{cross_scaled[(st,d,'白班')]}/晚{cross_scaled[(st,d,'晚班')]})" for d in DEPTS)
|
||
print(line)
|
||
|
||
# ---- 12 员工 三元组 ----
|
||
EMP=[
|
||
("蔡逸丰","西湖","精酿","晚班"),("何简","西湖","厨房","白班"),("宋群喜","西湖","咖啡","白班"),
|
||
("胡舒","西湖","调酒","晚班"),("郭思儒","西湖","咖啡","白班"),("秦天","西湖","厨房","晚班"),
|
||
("李想","滨江","调酒","晚班"),("王瑛胤","滨江","咖啡","白班"),("刘润祥","滨江","厨房","晚班"),
|
||
("朱秋风","滨江","精酿","晚班"),("叶磊","滨江","厨房","白班"),("尹志艳","滨江","厨房","中班"),
|
||
]
|
||
BJ_KITCHEN={"刘润祥","叶磊","尹志艳"}
|
||
print("\n"+"="*72)
|
||
print(" 12 员工 业绩三元组 (部门业绩 / 班次业绩 / 部门×班次业绩)")
|
||
print("="*72)
|
||
emp_out=[]
|
||
for name,st,dept,sh in EMP:
|
||
dr=dept_total.get(dept,{}).get(st,0)
|
||
sr=shift_sum.get((st,sh),[0,0,0])[2] if sh in ("白班","晚班") else 0
|
||
cr=cross_scaled.get((st,dept,sh),0)
|
||
if name in BJ_KITCHEN: sr=0; cr=0
|
||
if sh=="中班": sr=0; cr=0
|
||
emp_out.append((name,st,dept,sh,dr,sr,cr))
|
||
print(f" {name:<6}{st}店 {dept}/{sh:<3} 部门{dr:>11.2f} 班次{sr:>11.2f} 部门×班次{cr:>9}")
|
||
|
||
# 存盘供 workflow 校验 / 写表
|
||
import json
|
||
summary={
|
||
"month":MONTH,
|
||
"dept_pos":{st:dict(dept_pos[st]) for st in STORES},
|
||
"dept_tg":{st:dict(dept_tg[st]) for st in STORES},
|
||
"dept_total":dept_total,
|
||
"shift_sum":{f"{st}|{sh}":shift_sum[(st,sh)] for st in STORES for sh in ["白班","晚班"]},
|
||
"cross_scaled":{f"{st}|{d}|{sh}":cross_scaled[(st,d,sh)] for st in STORES for d in DEPTS for sh in ["白班","晚班"]},
|
||
"voucher":{st:voucher[st] for st in STORES},
|
||
"unmatched":unmatched,
|
||
"others":{st:dict(others[st]) for st in STORES},
|
||
"employees":emp_out,
|
||
"orders_count":{st:len(orders[st]) for st in STORES},
|
||
"pos_payable":{st:round(sum(o["payable"] for o in orders[st]),2) for st in STORES},
|
||
"pos_dishrev":{st:round(sum(o["dish_rev"] for o in orders[st]),2) for st in STORES},
|
||
}
|
||
with open(f"{BASE}/_analysis_summary.json","w") as f:
|
||
json.dump(summary,f,ensure_ascii=False,indent=2,default=str)
|
||
print(f"\n 未归类菜品笔数: 西湖={unmatched['西湖']} 滨江={unmatched['滨江']}")
|
||
print(f" POS订单数: 西湖={len(orders['西湖'])} 滨江={len(orders['滨江'])}")
|
||
|
||
write_xlsx(summary, dept_total, dept_pos, dept_tg, shift_sum, cross_scaled,
|
||
cat1, cat2, daily, pays, voucher, tg_detail, DEPTS, EMP, BJ_KITCHEN)
|
||
print(f"\n 汇总已存: {BASE}/_analysis_summary.json")
|
||
print(f" 分析表已存: {BASE}/大梦可能实验室_{_m}月营收分析_西湖店vs滨江店.xlsx")
|
||
return summary
|
||
|
||
|
||
def write_xlsx(s, dept_total, dept_pos, dept_tg, shift_sum, cross_scaled,
|
||
cat1, cat2, daily, pays, voucher, tg_detail, DEPTS, EMP, BJ_KITCHEN):
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import Font, PatternFill, Alignment
|
||
wb=Workbook(); wb.remove(wb.active)
|
||
H=Font(bold=True); TITLE=Font(bold=True,size=13)
|
||
GREEN=PatternFill("solid",fgColor="E2EFDA"); BLUE=PatternFill("solid",fgColor="DDEBF7")
|
||
HEADER=PatternFill("solid",fgColor="44546A"); HW=Font(bold=True,color="FFFFFF")
|
||
def sheet(name): return wb.create_sheet(name)
|
||
def hdr(ws,row,cols,fill=True):
|
||
for j,c in enumerate(cols,1):
|
||
cell=ws.cell(row=row,column=j,value=c)
|
||
if fill: cell.fill=HEADER; cell.font=HW
|
||
# —— 总览 ——
|
||
ws=sheet("总览"); ws["A1"]=f"大梦·可能实验室 — {_y}年{_m}月营收总览（西湖店 vs 滨江店）"; ws["A1"].font=TITLE
|
||
ws["A2"]=f"区间 {_y}/{_m:02d}/01–{_m:02d}/{DAYS} ｜ 数据源：POS店内订单明细 + 美团团购收益 + 点评/可能实验室收益"
|
||
r=4; ws.cell(r,1,"指标").font=H; ws.cell(r,2,"滨江店").font=H; ws.cell(r,3,"西湖店").font=H; ws.cell(r,4,"两店合计").font=H
|
||
bj_dep=sum(dept_total[d]["滨江"] for d in DEPTS); xh_dep=sum(dept_total[d]["西湖"] for d in DEPTS)
|
||
rows=[
|
||
("4部门收入合计(POS+团购)", bj_dep, xh_dep),
|
||
("POS菜品收入", sum(dept_pos["滨江"].values()), sum(dept_pos["西湖"].values())),
|
||
("团购套餐结算", sum(dept_tg["滨江"].values()), sum(dept_tg["西湖"].values())),
|
||
("白班实付", shift_sum[("滨江","白班")][2], shift_sum[("西湖","白班")][2]),
|
||
("晚班实付", shift_sum[("滨江","晚班")][2], shift_sum[("西湖","晚班")][2]),
|
||
("POS订单数", s["orders_count"]["滨江"], s["orders_count"]["西湖"]),
|
||
]
|
||
for i,(k,b,x) in enumerate(rows):
|
||
rr=r+1+i; ws.cell(rr,1,k); ws.cell(rr,2,round(b,2)); ws.cell(rr,3,round(x,2)); ws.cell(rr,4,round(b+x,2))
|
||
# —— 部门收入 ——
|
||
ws=sheet("部门收入"); ws["A1"]="负责部门收入 — POS菜品收入 + 团购套餐结算（代金券不重算）"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["部门","滨江_POS","滨江_团购","滨江_合计","西湖_POS","西湖_团购","西湖_合计","两店合计"])
|
||
for i,d in enumerate(DEPTS):
|
||
rr=4+i; bp=dept_pos["滨江"].get(d,0); bt=dept_tg["滨江"].get(d,0); xp=dept_pos["西湖"].get(d,0); xt=dept_tg["西湖"].get(d,0)
|
||
for j,v in enumerate([d,round(bp,2),round(bt,2),round(bp+bt,2),round(xp,2),round(xt,2),round(xp+xt,2),round(bp+bt+xp+xt,2)],1):
|
||
ws.cell(rr,j,v)
|
||
tot_r=4+len(DEPTS)
|
||
ws.cell(tot_r,1,"合计").font=H
|
||
for j,col in enumerate(["滨江_POS","滨江_团购","滨江_合计","西湖_POS","西湖_团购","西湖_合计","两店合计"],2):
|
||
ws.cell(tot_r,j,round(sum(ws.cell(4+i,j).value for i in range(len(DEPTS))),2)).font=H
|
||
# 占比块
|
||
ws.cell(tot_r+2,1,"② 4部门占比").font=H
|
||
hdr(ws,tot_r+3,["部门","滨江合计","西湖合计","两店合计","滨江占比","西湖占比"])
|
||
for i,d in enumerate(sorted(DEPTS,key=lambda x:-(dept_total[x]['滨江']+dept_total[x]['西湖']))):
|
||
rr=tot_r+4+i; bc=dept_total[d]["滨江"]; xc=dept_total[d]["西湖"]
|
||
ws.cell(rr,1,d); ws.cell(rr,2,round(bc,2)); ws.cell(rr,3,round(xc,2)); ws.cell(rr,4,round(bc+xc,2))
|
||
ws.cell(rr,5,round(bc/bj_dep,4)); ws.cell(rr,6,round(xc/xh_dep,4))
|
||
# —— 团购→部门归口 ——
|
||
ws=sheet("团购→部门归口"); ws["A1"]="团购平台项目 → 部门归口明细（结算/商家应得口径）"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["品牌","门店","项目名称","笔数","售价","结算/应得","归口规则"])
|
||
for i,(brand,store,name,c,p,sv,rule,_) in enumerate(tg_detail):
|
||
rr=4+i
|
||
for j,v in enumerate([brand,store+"店",name,c,round(p,2),round(sv,2),rule],1): ws.cell(rr,j,v)
|
||
base=4+len(tg_detail)+1
|
||
ws.cell(base,1,"② 团购套餐→部门 汇总（剔除代金券）").font=H
|
||
hdr(ws,base+1,["门店","部门","金额"])
|
||
rr=base+2
|
||
for st in ["滨江","西湖"]:
|
||
for d in DEPTS:
|
||
v=dept_tg[st].get(d,0)
|
||
if v: ws.cell(rr,1,st+"店"); ws.cell(rr,2,d); ws.cell(rr,3,round(v,2)); rr+=1
|
||
ws.cell(rr,1,"③ 代金券(跳过,未重算)").font=H; rr+=1
|
||
hdr(ws,rr,["门店","笔数","售价","结算"]); rr+=1
|
||
for st in ["滨江","西湖"]:
|
||
v=voucher[st]; ws.cell(rr,1,st+"店"); ws.cell(rr,2,v[0]); ws.cell(rr,3,round(v[1],2)); ws.cell(rr,4,round(v[2],2)); rr+=1
|
||
# —— 班次营收 ——
|
||
ws=sheet("班次营收"); ws["A1"]="班次营收对比 — 白班 vs 晚班（顾客实付）"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["门店","餐段","订单数","订单金额(原价)","顾客实付","日均订单","日均实付","客单价(实付)"])
|
||
rr=4
|
||
for st in ["滨江","西湖"]:
|
||
for sh in ["白班","晚班"]:
|
||
o,a,p=shift_sum[(st,sh)]
|
||
for j,v in enumerate([st+"店",sh,o,round(a,2),round(p,2),round(o/DAYS,1),round(p/DAYS,1),round(p/o if o else 0,2)],1): ws.cell(rr,j,v); rr+=0
|
||
rr+=1
|
||
# —— 部门×班次 ——
|
||
ws=sheet("部门x班次"); ws["A1"]="部门 × 班次 营收（校正到部门合计）"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["门店","部门","白班","晚班","合计"])
|
||
rr=4
|
||
for st in ["滨江","西湖"]:
|
||
for d in DEPTS:
|
||
wv=cross_scaled[(st,d,"白班")]; nv=cross_scaled[(st,d,"晚班")]
|
||
for j,v in enumerate([st+"店",d,wv,nv,wv+nv],1): ws.cell(rr,j,v)
|
||
rr+=1
|
||
# —— 品类营收(一级) ——
|
||
ws=sheet("品类营收(一级)"); ws["A1"]="品类营收 — 一级分类 西湖 vs 滨江"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["一级分类","滨江_件数","滨江_收入","西湖_件数","西湖_收入"])
|
||
allcat=sorted(set(cat1["滨江"])|set(cat1["西湖"]), key=lambda c:-(cat1['滨江'].get(c,[0,0,0,0])[3]+cat1['西湖'].get(c,[0,0,0,0])[3]))
|
||
for i,c in enumerate(allcat):
|
||
rr=4+i; b=cat1["滨江"].get(c,[0,0,0,0]); x=cat1["西湖"].get(c,[0,0,0,0])
|
||
for j,v in enumerate([c,int(b[0]),round(b[3],2),int(x[0]),round(x[3],2)],1): ws.cell(rr,j,v)
|
||
# —— 品类营收(二级) ——
|
||
ws=sheet("品类营收(二级)"); ws["A1"]="品类营收 — 二级分类(基础分类) 各店Top"; ws["A1"].font=TITLE
|
||
r0=3
|
||
for st in ["滨江","西湖"]:
|
||
ws.cell(r0,1,f"■ {st}店").font=H; r0+=1
|
||
hdr(ws,r0,["基础分类","SKU数","销售件数","菜品收入"]); r0+=1
|
||
top=sorted(cat2[st].items(), key=lambda x:-x[1][4])[:30]
|
||
for c,v in top:
|
||
ws.cell(r0,1,c); ws.cell(r0,2,len(v[0])); ws.cell(r0,3,int(v[1])); ws.cell(r0,4,round(v[4],2)); r0+=1
|
||
r0+=1
|
||
# —— 每日营收 ——
|
||
for st in ["滨江","西湖"]:
|
||
ws=sheet(f"{st}店_每日营收"); ws["A1"]=f"{st}店 — {_y}年{_m}月每日营收"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["日期","订单数","订单金额(原价)","顾客应付","订单优惠","赠菜笔数","赠菜金额"])
|
||
rr=4
|
||
for d,(dd,gd) in daily[st].items():
|
||
for j,v in enumerate([d,dd[0],round(dd[1],2),round(dd[2],2),round(dd[3],2),gd[0],round(gd[1],2)],1): ws.cell(rr,j,v)
|
||
rr+=1
|
||
# —— 支付方式分布 ——
|
||
ws=sheet("支付方式分布"); ws["A1"]="支付方式分布（POS端）"; ws["A1"].font=TITLE
|
||
r0=3
|
||
for st in ["滨江","西湖"]:
|
||
ws.cell(r0,1,f"■ {st}店").font=H; r0+=1
|
||
hdr(ws,r0,["支付方式","笔数","支付金额"]); r0+=1
|
||
for way,(c,amt) in sorted(pays[st].items(), key=lambda x:-x[1][1]):
|
||
ws.cell(r0,1,way); ws.cell(r0,2,c); ws.cell(r0,3,round(amt,2)); r0+=1
|
||
r0+=1
|
||
# —— 12员工业绩（工资交接）——
|
||
ws=sheet("12员工业绩"); ws["A1"]="12 员工业绩三元组（→ 工资表V2 cols 19/20/21）"; ws["A1"].font=TITLE
|
||
hdr(ws,3,["姓名","归属","部门","班次","部门业绩","班次业绩","部门×班次业绩"])
|
||
for i,(name,st,dept,sh,dr,sr,cr) in enumerate(s["employees"]):
|
||
rr=4+i
|
||
for j,v in enumerate([name,st+"店",dept,sh,round(dr,2),round(sr,2),cr],1): ws.cell(rr,j,v)
|
||
fill=GREEN if st=="西湖" else BLUE
|
||
for j in range(1,8): ws.cell(rr,j).fill=fill
|
||
# —— 深度分析 / 改进建议 占位（workflow 填充）——
|
||
ws=sheet("深度分析"); ws["A1"]="深度分析 — 异常项与关键洞察（见正文）"; ws["A1"].font=TITLE
|
||
ws=sheet("改进建议"); ws["A1"]=f"基于{_m}月数据的改进建议（见正文）"; ws["A1"].font=TITLE
|
||
|
||
for ws in wb.worksheets:
|
||
ws.column_dimensions["A"].width=26
|
||
for col in "BCDEFGH": ws.column_dimensions[col].width=14
|
||
wb.save(f"{BASE}/大梦可能实验室_{_m}月营收分析_西湖店vs滨江店.xlsx")
|
||
|
||
if __name__=="__main__":
|
||
main()
|