""" Polleo Planning β€” Streamlit App v4.0 (Demand + Supply) ======================================================= Browser-based interface with two modules sharing one data folder: πŸ“Š Demand β€” Forecast Engine v3.6 (dashboard, what-if, consensus, S&OP) πŸ“¦ Supply β€” Coverage, safety stock (FA-driven), reorder alerts, order entry, supply-plan export for CMs Handoff: when the consensus plan is saved or the demand plan is exported, the app writes `data/forecast_for_supply.csv` which the Supply module reads automatically β€” no manual copy-paste between modules. Run: streamlit run app.py Setup: Place data files in data/ folder, pip install plotly """ import streamlit as st import pandas as pd import numpy as np import os, json, re, subprocess from datetime import datetime, timedelta, date from pathlib import Path from openpyxl import load_workbook import plotly.graph_objects as go from constants import ( FORECAST_WEEKS, TRAILING_WEEKS, WATCHLIST_SIZE, OZNAKA_TIERS, YW_MULTIPLIER, ) # ---- CONFIG ---- st.set_page_config( page_title="Demand Planning β€” Demo", page_icon="πŸ“Š", layout="wide", initial_sidebar_state="expanded" ) DATA_DIR = Path("data") DATA_DIR.mkdir(exist_ok=True) CONSENSUS_DIR = DATA_DIR / "consensus" CONSENSUS_DIR.mkdir(exist_ok=True) CORRECTIONS_FILE = DATA_DIR / "planner_corrections.json" OUTPUT_FILE = DATA_DIR / "Polleo_Demand_Plan.xlsx" REQUIRED_CSV = [ "sales_clean.csv", "sku_prices.csv", "sku_uplift.csv", "cat_uplift.csv", "sku_category_map.csv", "sku_subcat_map.csv" ] # ================================================================== # DATA LOADING # ================================================================== def get_current_cw(): iso = datetime.now().isocalendar() return iso[0], iso[1] def load_corrections(): if CORRECTIONS_FILE.exists(): with open(CORRECTIONS_FILE) as f: return json.load(f) return {"factors": {}, "vp": {}, "mp": {}, "last_saved": None} def save_corrections(data): data["last_saved"] = datetime.now().strftime("%Y-%m-%d %H:%M") with open(CORRECTIONS_FILE, "w") as f: json.dump(data, f, indent=2) def file_status(): out = {} for f in REQUIRED_CSV: p = DATA_DIR / f out[f] = p.exists() out["planning_book"] = bool(list(DATA_DIR.glob("Polleo_Demand_Planning_Book*.xlsx"))) out["forecast"] = OUTPUT_FILE.exists() if OUTPUT_FILE.exists(): out["forecast_date"] = datetime.fromtimestamp(OUTPUT_FILE.stat().st_mtime).strftime("%Y-%m-%d %H:%M") return out @st.cache_data(ttl=60) def load_sales_data(): """Load full sales_clean.csv with category/oznaka info for dashboard & demand planning.""" sc_path = DATA_DIR / "sales_clean.csv" if not sc_path.exists(): return None sc = pd.read_csv(sc_path) sc["yw"] = sc["year"] * 100 + sc["week"] # Merge category cat_path = DATA_DIR / "sku_category_map.csv" if cat_path.exists(): cat_df = pd.read_csv(cat_path) cat_map = dict(zip(cat_df["sku"], cat_df["cat"])) if "name" in cat_df.columns: name_map = dict(zip(cat_df["sku"], cat_df["name"])) else: name_map = {} sc["cat"] = sc["sku"].map(cat_map).fillna("OTHER") sc["name"] = sc["sku"].map(name_map).fillna("") else: sc["cat"] = "OTHER" sc["name"] = "" # Merge oznaka plan_path = DATA_DIR / "sku_plan_list.csv" if plan_path.exists(): plan_df = pd.read_csv(plan_path) ozn_map = dict(zip(plan_df["sku"], plan_df["oznaka"])) sc["oznaka"] = sc["sku"].map(ozn_map).fillna("UNCLASSIFIED") else: sc["oznaka"] = "UNCLASSIFIED" return sc @st.cache_data(ttl=60) def get_sales_date_range(_sc_yw_min, _sc_yw_max): """Get min/max dates from sales data year-week bounds. Cached to avoid recomputing.""" min_dt = pd.to_datetime(f"{_sc_yw_min // 100}{_sc_yw_min % 100}1", format="%G%V%u", errors="coerce") max_dt = pd.to_datetime(f"{_sc_yw_max // 100}{_sc_yw_max % 100}1", format="%G%V%u", errors="coerce") min_d = min_dt.date() if not pd.isna(min_dt) else date.today() - timedelta(weeks=52) max_d = max_dt.date() if not pd.isna(max_dt) else date.today() return min_d, max_d @st.cache_data(ttl=60) def build_sku_name_map(sc): """Build display_label -> sku_code map from sales data. Cached.""" sku_name_map = {} for sku_code in sorted(sc["sku"].unique()): names_arr = sc[sc["sku"] == sku_code]["name"].dropna().unique() name = names_arr[0] if len(names_arr) > 0 and names_arr[0] else "" display = f"{name} ({sku_code})" if name else sku_code sku_name_map[display] = sku_code return sku_name_map @st.cache_data(ttl=60) def load_revenue_data(): sc_path = DATA_DIR / "sales_clean.csv" if not sc_path.exists(): return None sc = pd.read_csv(sc_path) sc["yw"] = sc["year"] * 100 + sc["week"] recent = sc.sort_values("yw").groupby("yw").agg( total_qty=("qty_total", "sum"), n_skus=("sku", "nunique") ).tail(13).reset_index() recent["year"] = recent["yw"] // 100 recent["week"] = recent["yw"] % 100 recent["label"] = "CW" + recent["week"].astype(str) return { "recent": recent, "total_skus": sc["sku"].nunique(), "total_rows": len(sc), "last_week": f"CW{int(recent['week'].iloc[-1])}", "last_year": int(recent["year"].iloc[-1]), } @st.cache_data(ttl=60) def load_demand_plan(): """Read DP sheet: returns {cws, rows} where each row has baseline + factors.""" if not OUTPUT_FILE.exists(): return None wb = load_workbook(OUTPUT_FILE, data_only=True) ws = wb["Demand Planning"] cws = [] for c in range(32, 52): v = ws.cell(5, c).value if v and str(v).startswith("CW"): cws.append(str(v)) elif v is None: break BLOCK, BASE = 11, 6 rows = [] for idx in range(496): br = BASE + idx * BLOCK sku = ws.cell(br, 1).value if not sku: break rows.append({ "SKU": sku, "Name": ws.cell(br, 2).value or "", "Category": ws.cell(br, 3).value or "", "OZNAKA": ws.cell(br, 4).value or "", "baseline": [round(float(ws.cell(br+1, 32+j).value or 0)) for j in range(len(cws))], "factors": [float(ws.cell(br+2, 32+j).value or 1.0) for j in range(len(cws))], }) wb.close() return {"cws": cws, "rows": rows} @st.cache_data(ttl=60) def load_input_sheet(sheet_name): """Read VP or MP input sheet.""" if not OUTPUT_FILE.exists(): return None wb = load_workbook(OUTPUT_FILE, data_only=True) if sheet_name not in wb.sheetnames: wb.close() return None ws = wb[sheet_name] cws = [] for c in range(6, 26): v = ws.cell(4, c).value if v and str(v).startswith("CW"): cws.append(str(v)) elif v is None: break rows = [] r = 5 while r <= ws.max_row: sku = ws.cell(r, 1).value if not sku: r += 1 continue rows.append({ "SKU": sku, "Name": ws.cell(r, 2).value or "", "Category": ws.cell(r, 3).value or "", "Type": ws.cell(r, 5).value or "", "values": [float(ws.cell(r, 6+j).value or 0) for j in range(len(cws))], }) r += 1 wb.close() return {"cws": cws, "rows": rows} @st.cache_data(ttl=60) def load_vp_mp_inputs(): """Load vp_input.csv and mp_input.csv as DataFrames for demand planning overlay.""" result = {} for tp in ["vp", "mp"]: p = DATA_DIR / f"{tp}_input.csv" if p.exists(): df = pd.read_csv(p) result[tp] = df else: result[tp] = None return result @st.cache_data(ttl=60) def load_sku_prices(): """{sku: avg_sell_price} β€” empty dict if file missing.""" p = DATA_DIR / "sku_prices.csv" if not p.exists(): return {} pdf = pd.read_csv(p) return dict(zip(pdf["sku"], pdf["avg_sell_price"])) def compute_plan_revenue(plan, sku_prices): """Sum(baseline * factor * price) across a plan's SKUs and CWs.""" if not plan: return 0.0 total = 0.0 cws = plan.get("cws", []) for r in plan.get("rows", []): price = sku_prices.get(r["SKU"], 0) baseline = r.get("baseline", []) factors = r.get("factors", []) for j in range(len(cws)): b = baseline[j] if j < len(baseline) else 0 f = factors[j] if j < len(factors) else 1.0 total += b * f * price return total def clear_all_caches(): """Invalidate every @st.cache_data loader so fresh data is picked up. Called after any write that changes underlying CSV/xlsx inputs (forecast run, sales update, consensus save, correction apply). """ load_sales_data.clear() load_revenue_data.clear() load_demand_plan.clear() load_input_sheet.clear() load_vp_mp_inputs.clear() load_sku_prices.clear() load_consensus_snapshots.clear() compute_accuracy_metrics.clear() compute_exceptions.clear() def apply_corrections_to_xlsx(): """Write planner_corrections.json into xlsx before engine re-reads it.""" if not OUTPUT_FILE.exists() or not CORRECTIONS_FILE.exists(): return corrections = load_corrections() has_data = (corrections.get("factors") or corrections.get("vp") or corrections.get("mp")) if not has_data: return try: wb = load_workbook(OUTPUT_FILE) # DP planner factors ws_dp = wb["Demand Planning"] cw_cols = {} for c in range(32, 52): v = ws_dp.cell(5, c).value if v and str(v).startswith("CW"): cw_cols[str(v).replace("CW", "")] = c elif v is None: break BLOCK, BASE = 11, 6 for idx in range(496): br = BASE + idx * BLOCK sku = ws_dp.cell(br, 1).value if not sku: break factors = corrections.get("factors", {}).get(sku, {}) for cw_num, col in cw_cols.items(): if cw_num in factors: ws_dp.cell(br + 2, col, factors[cw_num]) # VP / MP input sheets for sheet_name, key in [("Demand Input VP", "vp"), ("Demand Input MP", "mp")]: if sheet_name not in wb.sheetnames: continue ws = wb[sheet_name] inp_cws = {} for c in range(6, 26): v = ws.cell(4, c).value if v and str(v).startswith("CW"): inp_cws[str(v).replace("CW", "")] = c elif v is None: break r = 5 while r <= ws.max_row: sku = ws.cell(r, 1).value rtype = ws.cell(r, 5).value if not sku: r += 1 continue saved = corrections.get(key, {}).get(sku, {}) for cw_num, col in inp_cws.items(): cw_key = f"{rtype}_{cw_num}" if cw_key in saved: ws.cell(r, col, saved[cw_key]) r += 1 wb.save(OUTPUT_FILE) wb.close() except Exception as e: st.warning(f"Could not apply corrections: {e}") # ================================================================== # NEW DATA HELPERS: Accuracy, FVA, Exceptions, Consensus, Bridge # ================================================================== def save_consensus_snapshot(label=None): """Save the current demand plan as a versioned snapshot. Also captures VP/MP on-top inputs so that forecast accuracy can later be measured against actual wholesale (VP) and retail (MP) sales. """ if not OUTPUT_FILE.exists(): return None plan = load_demand_plan() if plan is None: return None cy, cw = get_current_cw() ts = datetime.now().strftime("%Y%m%d_%H%M%S") if not label: label = f"CW{cw} S&OP lock" inputs = load_vp_mp_inputs() sku_prices = load_sku_prices() total_rev = compute_plan_revenue(plan, sku_prices) # ---- Capture VP / MP on-top inputs ({sku: {CW_label: value}}) ---- vp_inputs, mp_inputs = {}, {} for tp, target in [("vp", vp_inputs), ("mp", mp_inputs)]: df = inputs.get(tp) if df is None or len(df) == 0: continue cw_cols = [c for c in df.columns if str(c).startswith("CW")] for _, row in df.iterrows(): sku = row["sku"] per_cw = {} for c in cw_cols: try: v = float(row.get(c, 0) or 0) except (TypeError, ValueError): v = 0.0 if v > 0: per_cw[c] = v if per_cw: target[sku] = per_cw snapshot = { "label": label, "timestamp": datetime.now().strftime("%Y-%m-%d %H:%M"), "snap_year": cy, # v3.6+: explicit year for CWβ†’(year,week) mapping "cw": cw, "n_skus": len(plan["rows"]), "cws": plan["cws"], "total_rev": round(total_rev), "rows": plan["rows"], "vp_inputs": vp_inputs, # v3.6+: historical VP on-top per SKU/CW "mp_inputs": mp_inputs, # v3.6+: historical MP on-top per SKU/CW } filepath = CONSENSUS_DIR / f"snapshot_{ts}.json" with open(filepath, "w") as f: json.dump(snapshot, f) # Auto-refresh the forecast bridge so the Supply module sees the new plan try: write_forecast_for_supply() except Exception: pass return filepath.name def _parse_cw_label(lbl): """'CW17' -> 17. Returns None on failure.""" try: return int(str(lbl).replace("CW", "").strip()) except (ValueError, AttributeError): return None def _assign_years_to_weeks(rows, start_year): """Given list of {sku, week, demand} preserving per-SKU input order, assign year to each row, incrementing on week-number wrap within a SKU.""" out = [] prev_sku = None year = start_year prev_wk = 0 for r in rows: if r["sku"] != prev_sku: year = start_year prev_wk = 0 prev_sku = r["sku"] wk = r["week"] if prev_wk and wk < prev_wk: year += 1 out.append({"sku": r["sku"], "year": year, "week": wk, "demand": int(max(0, r["demand"]))}) prev_wk = wk return out def _read_demand_output_total(wb): """Read 'Demand Output - Total' sheet if its cells are evaluated. Returns list of {sku, week, demand} or None if sheet is empty/formulas unevaluated.""" if "Demand Output - Total" not in wb.sheetnames: return None ws = wb["Demand Output - Total"] # Header row 4: col 1-4 = SKU/Artikl/Grupacija/OZNAKA, col 5+ = CW17, CW18, ... cw_cols = [] for c in range(5, ws.max_column + 1): wk = _parse_cw_label(ws.cell(4, c).value) if wk is not None: cw_cols.append((c, wk)) if not cw_cols: return None rows = [] any_value = False for r in range(5, ws.max_row + 1): sku = ws.cell(r, 1).value if not sku: continue for col, wk in cw_cols: v = ws.cell(r, col).value if v is None: continue any_value = True try: d = round(float(v)) except (TypeError, ValueError): d = 0 rows.append({"sku": sku, "week": wk, "demand": d}) return rows if any_value else None def _read_ontop_from_input_sheet(wb, sheet_name): """Read raw on-top values from 'Demand Input VP' or 'Demand Input MP'. These sheets have two rows per SKU (on-top demand + regular increase), with CW columns holding raw numbers (not formulas). Returns dict {(sku, week): total_ontop_value}.""" if sheet_name not in wb.sheetnames: return {} ws = wb[sheet_name] # Row 4: SKU | Artikl | Grupacija | OZNAKA | Type | CW17 | CW18 | ... cw_cols = [] for c in range(6, ws.max_column + 1): wk = _parse_cw_label(ws.cell(4, c).value) if wk is not None: cw_cols.append((c, wk)) if not cw_cols: return {} out = {} for r in range(5, ws.max_row + 1): sku = ws.cell(r, 1).value if not sku: continue for col, wk in cw_cols: v = ws.cell(r, col).value if v is None: continue try: fv = float(v) except (TypeError, ValueError): continue if fv == 0: continue key = (sku, wk) out[key] = out.get(key, 0.0) + fv # sum on-top + regular rows return out def _load_ontop_from_csv(fname): """Read vp_input.csv / mp_input.csv and return {(sku, week): value}. These CSVs are the authoritative KAM/CM input source β€” fresher than the xlsx sheets which only update when a demand planner opens and saves the workbook. Columns: sku + CW per planning week.""" p = DATA_DIR / fname if not p.exists(): return {} df = pd.read_csv(p) if "sku" not in df.columns: return {} cw_cols = [c for c in df.columns if isinstance(c, str) and c.startswith("CW")] out = {} for _, row in df.iterrows(): sku = row["sku"] if not sku or pd.isna(sku): continue for col in cw_cols: try: wk = int(col[2:]) v = float(row[col]) except (TypeError, ValueError): continue if v == 0 or pd.isna(v): continue key = (sku, wk) out[key] = out.get(key, 0.0) + v return out def _reconstruct_forecast_from_parts(wb): """Primary path: rebuild TOTAL DEMAND from authoritative sources. TOTAL DEMAND = round(baseline Γ— factor) + VP on-top + MP on-top. Baseline & factor come from the 'Demand Planning' xlsx block (rows br+1, br+2 β€” plain numbers, not formulas). Those two are xlsx-only because the factor override lives there. VP / MP on-top values come DIRECTLY from vp_input.csv / mp_input.csv, NOT from the xlsx 'Demand Input VP' / 'Demand Input MP' sheets β€” those sheets lag whenever KAM/CM inputs are refreshed but the xlsx hasn't been re-saved. CSVs are written the moment KAM files combine.""" if "Demand Planning" not in wb.sheetnames: return None ws = wb["Demand Planning"] cw_cols = [] for c in range(32, 52): wk = _parse_cw_label(ws.cell(5, c).value) if wk is None: break cw_cols.append((c, wk)) if not cw_cols: return None vp_ontop = _load_ontop_from_csv("vp_input.csv") mp_ontop = _load_ontop_from_csv("mp_input.csv") BLOCK, BASE = 11, 6 rows = [] for idx in range(520): br = BASE + idx * BLOCK sku = ws.cell(br, 1).value if not sku: break for col, wk in cw_cols: try: b = float(ws.cell(br + 1, col).value or 0) f = float(ws.cell(br + 2, col).value or 1.0) except (TypeError, ValueError): b, f = 0.0, 1.0 base_adj = round(b * f) on_top = vp_ontop.get((sku, wk), 0.0) + mp_ontop.get((sku, wk), 0.0) rows.append({"sku": sku, "week": wk, "demand": round(base_adj + on_top)}) return rows if rows else None def write_forecast_for_supply(): """Write data/forecast_for_supply.csv from the current Demand Plan. Primary source: reconstruct TOTAL = baseline Γ— factor + VP on-top + MP on-top, where baseline & factor come from the xlsx 'Demand Planning' sheet (factor override only lives there) and VP/MP on-top come directly from vp_input.csv / mp_input.csv (always fresh β€” updated the moment KAM/CM templates combine). No Excel re-save required. Fallback: 'Demand Output - Total' sheet if reconstruction fails (e.g. Demand Planning sheet missing or empty). Returns the source used ('reconstructed' | 'output_total' | None). """ if not OUTPUT_FILE.exists(): return None wb = load_workbook(OUTPUT_FILE, data_only=True) # Primary: reconstruct from parts β€” picks up latest vp_input.csv / # mp_input.csv without requiring the xlsx to be re-saved first. # Fallback: xlsx 'Demand Output - Total' sheet (only useful if CSV # reconstruction can't find the Demand Planning block). rows = _reconstruct_forecast_from_parts(wb) source = "reconstructed" if rows is None: rows = _read_demand_output_total(wb) source = "output_total" if not rows: return None cy, _ = get_current_cw() out = _assign_years_to_weeks(rows, cy) df = pd.DataFrame(out) # Drop zeros to keep the file small df = df[df["demand"] > 0].reset_index(drop=True) df.to_csv(DATA_DIR / "forecast_for_supply.csv", index=False) return source @st.cache_data(ttl=30) def load_consensus_snapshots(): """List all saved consensus snapshots.""" snapshots = [] for f in sorted(CONSENSUS_DIR.glob("snapshot_*.json"), reverse=True): try: with open(f) as fh: data = json.load(fh) data["filename"] = f.name snapshots.append(data) except Exception: pass return snapshots @st.cache_data(ttl=120) def compute_accuracy_metrics(sc, _dummy_hash=None): """Compare last S&OP snapshot forecast vs actual sales for overlapping weeks.""" if sc is None: return None snapshots = load_consensus_snapshots() if not snapshots: return None snap = snapshots[0] # latest snapshot snap_rows = {r["SKU"]: r for r in snap.get("rows", [])} snap_cws = snap.get("cws", []) if not snap_cws: return None # Get actual weekly sales per SKU for the last 4 weeks actuals = sc.groupby(["sku", "year", "week", "yw"])["qty_total"].sum().reset_index() actuals["cw_label"] = "CW" + actuals["week"].astype(str) recent_yws = sorted(actuals["yw"].unique())[-4:] actuals = actuals[actuals["yw"].isin(recent_yws)] # Build oznaka/cat maps plan_path = DATA_DIR / "sku_plan_list.csv" ozn_map, cat_map = {}, {} if plan_path.exists(): pl = pd.read_csv(plan_path) ozn_map = dict(zip(pl["sku"], pl["oznaka"])) cat_map = dict(zip(pl["sku"], pl["cat"])) results = [] for _, arow in actuals.iterrows(): sku = arow["sku"] cw_label = arow["cw_label"] actual = int(arow["qty_total"]) if actual == 0: continue if sku not in snap_rows or cw_label not in snap_cws: continue row = snap_rows[sku] j = snap_cws.index(cw_label) baseline = row["baseline"][j] if j < len(row["baseline"]) else 0 factor = row["factors"][j] if j < len(row["factors"]) else 1.0 forecast = round(baseline * factor) error = abs(actual - forecast) fa = max(0, 1 - error / actual) bias = forecast - actual hit = 1 if (error / actual) <= 0.3 else 0 results.append({ "sku": sku, "oznaka": ozn_map.get(sku, ""), "cat": cat_map.get(sku, ""), "cw": cw_label, "forecast": forecast, "actual": actual, "error": error, "fa": fa, "bias": bias, "hit": hit, }) if not results: return None return pd.DataFrame(results) @st.cache_data(ttl=120) def compute_exceptions(sc): """Detect anomalies: spikes, zero-sales, high-error SKUs.""" if sc is None: return {"spikes": [], "zero_sales": [], "high_error": []} plan_path = DATA_DIR / "sku_plan_list.csv" plan_df = pd.read_csv(plan_path) if plan_path.exists() else None ozn_map = dict(zip(plan_df["sku"], plan_df["oznaka"])) if plan_df is not None else {} name_map = {} cat_path = DATA_DIR / "sku_category_map.csv" if cat_path.exists(): cm = pd.read_csv(cat_path) if "name" in cm.columns: name_map = dict(zip(cm["sku"], cm["name"])) weekly = sc.groupby(["sku", "year", "week", "yw"])["qty_total"].sum().reset_index() weekly = weekly.sort_values(["sku", "yw"]) spikes = [] zero_sales = [] # Get last 2 weeks per SKU for spike detection for sku, grp in weekly.groupby("sku"): last_rows = grp.tail(5) if len(last_rows) >= 2: prev = last_rows.iloc[-2]["qty_total"] curr = last_rows.iloc[-1]["qty_total"] if prev > 5 and curr > 0: change = (curr - prev) / prev if abs(change) > 0.3: spikes.append({ "sku": sku, "name": name_map.get(sku, ""), "oznaka": ozn_map.get(sku, ""), "change": round(change * 100), "prev": int(prev), "curr": int(curr), "week": f"CW{int(last_rows.iloc[-1]['week'])}", }) # Zero-sales detection: last 3+ weeks = 0 last_3 = grp.tail(3) if len(last_3) >= 3 and last_3["qty_total"].sum() == 0: zero_sales.append({ "sku": sku, "name": name_map.get(sku, ""), "oznaka": ozn_map.get(sku, ""), "weeks_zero": len(last_3[last_3["qty_total"] == 0]), }) spikes.sort(key=lambda x: abs(x["change"]), reverse=True) return {"spikes": spikes[:15], "zero_sales": zero_sales[:15]} def compute_revenue_bridge(current_plan, snapshots): """Compute waterfall between last snapshot and current plan.""" if not snapshots or current_plan is None: return None sku_prices = load_sku_prices() # Current revenue per SKU curr_rev = {} for r in current_plan["rows"]: rev = 0 price = sku_prices.get(r["SKU"], 0) for j in range(len(current_plan["cws"])): b = r["baseline"][j] if j < len(r["baseline"]) else 0 f = r["factors"][j] if j < len(r["factors"]) else 1.0 rev += b * f * price curr_rev[r["SKU"]] = rev # Previous snapshot revenue per SKU prev_snap = snapshots[0] prev_rev = {} for r in prev_snap.get("rows", []): rev = 0 price = sku_prices.get(r["SKU"], 0) for j in range(len(prev_snap.get("cws", []))): b = r["baseline"][j] if j < len(r["baseline"]) else 0 f = r["factors"][j] if j < len(r["factors"]) else 1.0 rev += b * f * price prev_rev[r["SKU"]] = rev prev_total = sum(prev_rev.values()) curr_total = sum(curr_rev.values()) # Breakdown all_skus = set(list(curr_rev.keys()) + list(prev_rev.keys())) volume_change = 0 new_skus_rev = 0 removed_skus_rev = 0 for sku in all_skus: c = curr_rev.get(sku, 0) p = prev_rev.get(sku, 0) if p == 0 and c > 0: new_skus_rev += c elif c == 0 and p > 0: removed_skus_rev += p else: volume_change += (c - p) # VP/MP contribution inputs = load_vp_mp_inputs() vp_mp_rev = 0 for tp in ["vp", "mp"]: if inputs[tp] is not None: for _, row in inputs[tp].iterrows(): price = sku_prices.get(row["sku"], 0) cw_cols = [c for c in row.index if c.startswith("CW")] vp_mp_rev += sum(float(row[c]) for c in cw_cols if pd.notna(row[c])) * price return { "prev_total": round(prev_total), "curr_total": round(curr_total), "volume_change": round(volume_change - vp_mp_rev), "vp_mp": round(vp_mp_rev), "new_skus": round(new_skus_rev), "removed": round(removed_skus_rev), "prev_label": prev_snap.get("label", "Previous"), } # ================================================================== # PAGE RENDERERS # ================================================================== def page_dashboard(): st.title("Dashboard") cy, cw = get_current_cw() st.caption(f"Week {cw}, {cy}") sc = load_sales_data() if sc is None: st.info("Upload sales data to get started.") return # --- Filters --- c1, c2, c3 = st.columns([2, 2, 1]) all_cats = sorted(sc["cat"].dropna().unique().tolist()) cat_filter = c1.selectbox("Kategorija", ["All"] + all_cats, key="dash_cat") ozn_options = ["All"] + OZNAKA_TIERS ozn_filter = c2.selectbox("Oznaka", ozn_options, key="dash_ozn") view_mode = c3.radio("View", ["Weekly", "Monthly"], horizontal=True, key="dash_view") # Apply category & oznaka filters filtered = sc.copy() if cat_filter != "All": filtered = filtered[filtered["cat"] == cat_filter] if ozn_filter != "All": filtered = filtered[filtered["oznaka"] == ozn_filter] # SKU filter (after cat/oznaka narrowing) available_skus = sorted(filtered["sku"].unique().tolist()) sku_filter = st.selectbox( "SKU", ["All SKUs"] + available_skus, key="dash_sku" ) if sku_filter != "All SKUs": filtered = filtered[filtered["sku"] == sku_filter] if filtered.empty: st.warning("No data for selected filters.") return # --- Aggregate --- if view_mode == "Weekly": agg = filtered.groupby(["year", "week"])["qty_total"].sum().reset_index() agg["yw"] = agg["year"] * 100 + agg["week"] agg = agg.sort_values("yw") agg["label"] = "CW" + agg["week"].astype(str) + " '" + (agg["year"] % 100).astype(str) x_col = "label" y_col = "qty_total" else: filtered_copy = filtered.copy() # Convert year+week to approximate month filtered_copy["month"] = pd.to_datetime( filtered_copy["year"].astype(str) + filtered_copy["week"].astype(str) + "1", format="%G%V%u", errors="coerce" ).dt.to_period("M") filtered_copy = filtered_copy.dropna(subset=["month"]) agg = filtered_copy.groupby("month")["qty_total"].sum().reset_index() agg = agg.sort_values("month") agg["label"] = agg["month"].astype(str) x_col = "label" y_col = "qty_total" # --- Line chart using Plotly --- fig = go.Figure() fig.add_trace(go.Scatter( x=agg[x_col], y=agg[y_col], mode="lines+markers+text", line=dict(color="#2F5496", width=2.5), marker=dict(size=6), text=[f"{int(v):,}" for v in agg[y_col]], textposition="top center", textfont=dict(size=10, color="#2F5496"), name="Quantity" )) fig.update_layout( xaxis_title=None, yaxis_title="Quantity", yaxis=dict(tickformat=",", separatethousands=True), margin=dict(l=60, r=20, t=40, b=40), height=420, hovermode="x unified", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", ) fig.update_xaxes(showgrid=False, tickangle=-45) fig.update_yaxes(showgrid=True, gridcolor="rgba(200,200,200,0.3)") title_suffix = "" if sku_filter != "All SKUs": # Show name if we can names = sc[sc["sku"] == sku_filter]["name"].dropna().unique() if len(names) > 0 and names[0]: title_suffix = f" β€” {sku_filter} ({names[0]})" else: title_suffix = f" β€” {sku_filter}" st.subheader(f"Sales volume ({view_mode.lower()}){title_suffix}") st.plotly_chart(fig, use_container_width=True) # --- Exception flags --- st.divider() st.subheader("Attention needed") exceptions = compute_exceptions(sc) n_total = len(exceptions["spikes"]) + len(exceptions["zero_sales"]) if n_total == 0: st.success("No exceptions detected.") else: c1, c2 = st.columns(2) c1.metric("Demand spikes", len(exceptions["spikes"]), help=">30% week-over-week change") c2.metric("Zero-sales alerts", len(exceptions["zero_sales"]), help="3+ weeks with no sales") if exceptions["spikes"]: with st.expander(f"πŸ”΄ Demand spikes ({len(exceptions['spikes'])})"): spike_df = pd.DataFrame(exceptions["spikes"]) spike_df["change"] = spike_df["change"].apply(lambda x: f"{'+' if x > 0 else ''}{x}%") st.dataframe(spike_df[["sku", "name", "oznaka", "change", "prev", "curr", "week"]].rename( columns={"sku":"SKU","name":"Name","oznaka":"Tier","change":"Change","prev":"Prev","curr":"Curr","week":"Week"} ), use_container_width=True, hide_index=True) if exceptions["zero_sales"]: with st.expander(f"🟑 Zero-sales alerts ({len(exceptions['zero_sales'])})"): zero_df = pd.DataFrame(exceptions["zero_sales"]) st.dataframe(zero_df[["sku","name","oznaka","weeks_zero"]].rename( columns={"sku":"SKU","name":"Name","oznaka":"Tier","weeks_zero":"Weeks at 0"} ), use_container_width=True, hide_index=True) def page_demand_planning(): st.title("Demand planning") cy, cw = get_current_cw() sc = load_sales_data() plan = load_demand_plan() if sc is None: st.warning("Upload sales data first.") return # --- Filters row --- c1, c2 = st.columns([2, 2]) all_cats = sorted(sc["cat"].dropna().unique().tolist()) cat_filter = c1.selectbox("Kategorija", ["All"] + all_cats, key="dp_cat") ozn_options = ["All"] + OZNAKA_TIERS ozn_filter = c2.selectbox("Oznaka", ozn_options, key="dp_ozn") # Date range picker # Date range from sales data (cached computation) yw_min = int(sc["yw"].min()) yw_max = int(sc["yw"].max()) min_date, max_date = get_sales_date_range(yw_min, yw_max) # Extend max date to cover the 13-week forecast horizon forecast_end = max_date + timedelta(weeks=14) default_start = max(min_date, max_date - timedelta(weeks=26)) dc1, dc2 = st.columns(2) date_from = dc1.date_input("From", value=default_start, min_value=min_date, max_value=forecast_end, key="dp_date_from") date_to = dc2.date_input("To", value=forecast_end, min_value=min_date, max_value=forecast_end, key="dp_date_to") # Apply cat/oznaka to narrow SKU list filtered_sc = sc if cat_filter != "All": filtered_sc = filtered_sc[filtered_sc["cat"] == cat_filter] if ozn_filter != "All": filtered_sc = filtered_sc[filtered_sc["oznaka"] == ozn_filter] # Build SKU name list for dropdown (cached) sku_name_map = build_sku_name_map(filtered_sc) sku_options = ["All"] + sorted(sku_name_map.keys()) sku_selection = st.selectbox("SKU", sku_options, key="dp_sku") if sku_selection == "All": selected_skus = list(sku_name_map.values()) sku_filter = None # means all else: sku_filter = sku_name_map.get(sku_selection) selected_skus = [sku_filter] if sku_filter else [] if not selected_skus: st.info("No SKUs match your filters.") return # --- Historical data --- sku_data = sc[sc["sku"].isin(selected_skus)].copy() sku_data = sku_data.sort_values("yw") # Resolve display name if sku_filter: names = sku_data[sku_data["sku"] == sku_filter]["name"].dropna().unique() sku_name = names[0] if len(names) > 0 and names[0] else sku_filter display_title = f"{sku_name} ({sku_filter})" else: n_skus = len(selected_skus) label_parts = [] if cat_filter != "All": label_parts.append(cat_filter) if ozn_filter != "All": label_parts.append(ozn_filter) group_label = " Β· ".join(label_parts) if label_parts else "All SKUs" display_title = f"{group_label} ({n_skus} SKUs)" sku_name = group_label # Filter by date range using year-week integers (much faster than date conversion) from_iso = date_from.isocalendar() to_iso = date_to.isocalendar() yw_from = from_iso[0] * 100 + from_iso[1] yw_to = to_iso[0] * 100 + to_iso[1] sku_data = sku_data[(sku_data["yw"] >= yw_from) & (sku_data["yw"] <= yw_to)] # Aggregate weekly (sum across all selected SKUs) hist = sku_data.groupby(["year", "week", "yw"])["qty_total"].sum().reset_index() hist = hist.sort_values("yw") hist["label"] = "CW" + hist["week"].astype(str) # --- Check for promo flags --- has_promo = False promo_weeks = set() uplift_path = DATA_DIR / "sku_uplift.csv" if uplift_path.exists(): try: uplift_df = pd.read_csv(uplift_path) sku_uplift = uplift_df[uplift_df["sku"].isin(selected_skus)] if not sku_uplift.empty and "promo_flag" in sku_uplift.columns: promo_rows = sku_uplift[sku_uplift["promo_flag"] == 1] if not promo_rows.empty: has_promo = True for _, pr in promo_rows.iterrows(): promo_weeks.add(int(pr["year"]) * 100 + int(pr["week"])) except Exception: pass # --- Forecast data (aggregate across selected SKUs) --- forecast_cws = [] forecast_baseline = [] forecast_adjusted = [] vp_values = [] mp_values = [] if plan is not None: matching_plan_rows = [r for r in plan["rows"] if r["SKU"] in selected_skus] if matching_plan_rows: forecast_cws = plan["cws"] n_cws = len(forecast_cws) # Sum baselines and adjusted across all matching SKUs forecast_baseline = [0] * n_cws forecast_adjusted = [0] * n_cws for row in matching_plan_rows: for j in range(n_cws): b = row["baseline"][j] if j < len(row["baseline"]) else 0 f = row["factors"][j] if j < len(row["factors"]) else 1.0 forecast_baseline[j] += round(b) forecast_adjusted[j] += round(b * f) # Load VP/MP inputs (aggregate across selected SKUs) inputs = load_vp_mp_inputs() for tp, label_tp in [("vp", "VP"), ("mp", "MP")]: if inputs[tp] is not None and forecast_cws: sku_inp = inputs[tp][inputs[tp]["sku"].isin(selected_skus)] if not sku_inp.empty: vals = [0.0] * len(forecast_cws) for _, inp_row in sku_inp.iterrows(): for j, cw_label in enumerate(forecast_cws): v = inp_row.get(cw_label, 0) vals[j] += float(v) if pd.notna(v) else 0 if tp == "vp": vp_values = vals else: mp_values = vals # --- Compute total forecast (baseline adjusted + VP + MP) --- forecast_total = [] if forecast_cws: for j in range(len(forecast_cws)): base = forecast_adjusted[j] if j < len(forecast_adjusted) else 0 vp_add = vp_values[j] if j < len(vp_values) else 0 mp_add = mp_values[j] if j < len(mp_values) else 0 forecast_total.append(round(base + vp_add + mp_add)) # --- What-if scenario toggle --- st.divider() wif_cols = st.columns(3) show_stat = wif_cols[0].checkbox("Statistical forecast", value=True, key="wif_stat") show_factors = wif_cols[1].checkbox("+ Planner factors", value=True, key="wif_fac") show_vpmp = wif_cols[2].checkbox("+ VP/MP inputs", value=True, key="wif_vpmp") # Compute what-if total based on toggles whatif_total = [] if forecast_cws: for j in range(len(forecast_cws)): val = 0 if show_stat: val = forecast_baseline[j] if j < len(forecast_baseline) else 0 if show_factors: val = forecast_adjusted[j] if j < len(forecast_adjusted) else val if show_vpmp: val += (vp_values[j] if j < len(vp_values) else 0) val += (mp_values[j] if j < len(mp_values) else 0) whatif_total.append(round(val)) # --- Build the chart (continuous line: actuals β†’ forecast) --- fig = go.Figure() # Limit actuals to last 12 completed weeks before forecast starts fc_start_cw = int(forecast_cws[0].replace("CW","")) if forecast_cws else cw + 1 hist_before_fc = hist[hist["week"] < fc_start_cw] if not hist.empty else hist hist_display = hist_before_fc.tail(12) # Build unified ordered x-axis labels hist_labels = hist_display["label"].tolist() if not hist_display.empty else [] fc_labels = forecast_cws if forecast_cws else [] all_x_labels = hist_labels + fc_labels # Actuals line (blue) if not hist_display.empty: fig.add_trace(go.Scatter( x=hist_labels, y=hist_display["qty_total"].tolist(), mode="lines+markers+text", line=dict(color="#2F5496", width=2.5), marker=dict(size=6), text=[f"{int(v):,}" for v in hist_display["qty_total"]], textposition="top center", textfont=dict(size=9, color="#2F5496"), name="Actual sales", )) # Forecast line (green) β€” connect from last actual for continuity if forecast_cws and whatif_total and any(v > 0 for v in whatif_total): fc_x = list(fc_labels) fc_y = list(whatif_total) # Bridge: start forecast line from last actual point if not hist_display.empty: fc_x = [hist_labels[-1]] + fc_x fc_y = [int(hist_display["qty_total"].iloc[-1])] + fc_y fig.add_trace(go.Scatter( x=fc_x, y=fc_y, mode="lines+markers+text", line=dict(color="#548235", width=2.5), marker=dict(size=6), text=[""] + [f"{int(v):,}" for v in whatif_total], # skip bridge point text textposition="top center", textfont=dict(size=9, color="#548235"), name="Forecast (total)", )) # Stat-only baseline (dashed) if VP/MP present has_vp_mp = (vp_values and any(v > 0 for v in vp_values)) or \ (mp_values and any(v > 0 for v in mp_values)) if forecast_cws and forecast_adjusted and has_vp_mp and show_vpmp: fig.add_trace(go.Scatter( x=fc_labels, y=forecast_adjusted, mode="lines", line=dict(color="#548235", width=1.5, dash="dot"), name="Forecast (stat. only)", opacity=0.5, )) # VP bars if forecast_cws and vp_values and any(v > 0 for v in vp_values) and show_vpmp: fig.add_trace(go.Bar( x=fc_labels, y=vp_values, name="VP input", marker_color="rgba(47, 84, 150, 0.45)", text=[f"{int(v):,}" if v > 0 else "" for v in vp_values], textposition="outside", textfont=dict(size=9, color="#2F5496"), width=0.4, )) # MP bars if forecast_cws and mp_values and any(v > 0 for v in mp_values) and show_vpmp: fig.add_trace(go.Bar( x=fc_labels, y=mp_values, name="MP input", marker_color="rgba(255, 107, 53, 0.45)", text=[f"{int(v):,}" if v > 0 else "" for v in mp_values], textposition="outside", textfont=dict(size=9, color="#D85A30"), width=0.4, )) # Enforce x-axis order fig.update_layout( barmode="overlay", xaxis=dict(categoryorder="array", categoryarray=all_x_labels, showgrid=False, tickangle=-45), yaxis=dict(title="Quantity", tickformat=",", separatethousands=True, showgrid=True, gridcolor="rgba(200,200,200,0.3)"), margin=dict(l=60, r=20, t=40, b=40), height=450, hovermode="x unified", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="left", x=0), ) st.subheader(display_title) # Oznaka / category info if sku_filter and not sku_data.empty: ozn = sku_data["oznaka"].iloc[0] if not sku_data.empty else "" cat = sku_data["cat"].iloc[0] if not sku_data.empty else "" st.caption(f"{cat} Β· {ozn}") st.plotly_chart(fig, use_container_width=True) # --- Data table below chart --- st.subheader("Numbers") table_rows = {} # Historical weeks for _, r in hist.iterrows(): lbl = r["label"] table_rows[lbl] = { "Week": lbl, "Actual": int(r["qty_total"]), "Forecast (stat.)": "", "VP input": "", "MP input": "", "Forecast (total)": "", "Promo": "πŸ”Ά" if int(r["yw"]) in promo_weeks else "", } # Forecast weeks for j, cw_label in enumerate(forecast_cws): adj = forecast_adjusted[j] if j < len(forecast_adjusted) else 0 vp_v = vp_values[j] if j < len(vp_values) else 0 mp_v = mp_values[j] if j < len(mp_values) else 0 total = whatif_total[j] if j < len(whatif_total) else 0 if cw_label in table_rows: table_rows[cw_label]["Forecast (stat.)"] = adj table_rows[cw_label]["VP input"] = int(vp_v) if vp_v > 0 else "" table_rows[cw_label]["MP input"] = int(mp_v) if mp_v > 0 else "" table_rows[cw_label]["Forecast (total)"] = total else: table_rows[cw_label] = { "Week": cw_label, "Actual": "", "Forecast (stat.)": adj, "VP input": int(vp_v) if vp_v > 0 else "", "MP input": int(mp_v) if mp_v > 0 else "", "Forecast (total)": total, "Promo": "", } if table_rows: tdf = pd.DataFrame(list(table_rows.values())) st.dataframe(tdf, use_container_width=True, hide_index=True) # --- Planner factors section (only for single SKU) --- if plan is not None and sku_filter: plan_rows = [r for r in plan["rows"] if r["SKU"] == sku_filter] if plan_rows: with st.expander("✏️ Edit planner factors for this SKU"): row = plan_rows[0] corrections = load_corrections() saved = corrections.get("factors", {}).get(sku_filter, {}) factor_cols = {} cols = st.columns(min(len(forecast_cws), 13)) for j, cw_label in enumerate(forecast_cws): cw_num = cw_label.replace("CW", "") default = saved.get(cw_num, row["factors"][j] if j < len(row["factors"]) else 1.0) factor_cols[cw_num] = cols[j % len(cols)].number_input( cw_label, min_value=0.0, max_value=5.0, value=float(default), step=0.05, format="%.2f", key=f"dpf_{cw_label}" ) if st.button("πŸ’Ύ Save factors", type="primary", key="dp_save_factors"): new_factors = {} for cw_num, val in factor_cols.items(): if val != 1.0: new_factors[cw_num] = val if new_factors: corrections.setdefault("factors", {})[sku_filter] = new_factors elif sku_filter in corrections.get("factors", {}): del corrections["factors"][sku_filter] save_corrections(corrections) st.success("Saved!") st.rerun() def page_update_sales(): st.title("Update sales data") st.caption("Upload sales files β€” one per country (CRO, SLO, AUT) or a single combined file. Columns are auto-detected.") uploaded_files = st.file_uploader( "Upload sales file(s) (.xlsx)", type=["xlsx", "xls"], accept_multiple_files=True, help="Upload 1–3 files (e.g. one per country database). Needs columns: Datum, Artikal, Količina, Vrijednost (€), Tip dok. β€” column order doesn't matter, extra columns are ignored." ) merge_ws = st.checkbox( "Wholesale add-on mode (e.g. RIZ-only upload)", value=False, help="Use this when your upload only contains wholesale documents (e.g. RIZ). " "Existing retail/webshop quantities are preserved; wholesale is added. " "Without this, rows in the upload **replace** existing rows entirely." ) if merge_ws: st.info("πŸ“‹ Add-on mode β€” wholesale qty + ruc will be **added** to existing " "rows; retail & webshop are untouched.") if uploaded_files: saved_paths = [] for uf in uploaded_files: # Save each with a unique name to avoid overwrites safe_name = uf.name.replace(" ", "_") fpath = DATA_DIR / f"upload_{safe_name}" with open(fpath, "wb") as f: f.write(uf.getvalue()) saved_paths.append(fpath) st.success(f"βœ… {uf.name} ({uf.size:,} bytes)") # Preview each file for uf, fpath in zip(uploaded_files, saved_paths): try: import openpyxl as _oxl _wb = _oxl.load_workbook(fpath, read_only=True) _sheets = _wb.sheetnames; _wb.close() _skip = {f'Sheet{i}' for i in range(1, 10)} _data_sheets = [s for s in _sheets if s not in _skip] _target = _data_sheets[0] if _data_sheets else _sheets[0] preview = pd.read_excel(fpath, sheet_name=_target, nrows=5) with st.expander(f"Preview: {uf.name}" + (f" (sheet: {_target})" if len(_sheets) > 1 else "")): st.dataframe(preview, use_container_width=True) except Exception: pass if st.button("πŸ”„ Process sales update", type="primary", use_container_width=True): script = Path("update_sales.py").resolve() if not script.exists(): st.error("update_sales.py not found next to app.py") return # Pass all saved file paths as arguments cmd = ["python", str(script)] + [str(p.resolve()) for p in saved_paths] if merge_ws: cmd.append("--merge-wholesale") import copy as _copy env = _copy.copy(os.environ) env["PYTHONIOENCODING"] = "utf-8" with st.spinner(f"Processing {len(saved_paths)} file(s)..."): r = subprocess.run( cmd, cwd=str(DATA_DIR.resolve()), stdin=subprocess.DEVNULL, capture_output=True, text=True, timeout=300, env=env, encoding="utf-8", errors="replace" ) if r.returncode == 0: st.success(f"Sales updated from {len(saved_paths)} file(s)!") clear_all_caches() else: st.error("Failed") st.code(r.stdout[-2000:] + "\n" + r.stderr[-1000:]) with st.expander("Or upload CSV files directly"): for fname in REQUIRED_CSV: f = st.file_uploader(f"{fname}", type=["csv"], key=f"csv_{fname}") if f: (DATA_DIR / fname).write_bytes(f.getvalue()) st.success(f"Saved {fname}") clear_all_caches() def page_run_forecast(): cy, cw = get_current_cw() st.title("Run forecast engine") st.caption(f"Generates 13-week forecast: CW{cw+1} β†’ CW{cw+13}") status = file_status() missing = [f for f in REQUIRED_CSV if not status[f]] if missing: st.error(f"Missing: {', '.join(missing)}") return if not status["planning_book"]: st.error("Missing Polleo_Demand_Planning_Book.xlsx in data/") return st.success("All data files present β€” ready to forecast.") c1, c2, c3 = st.columns(3) c1.info(f"**Forecast window**\nCW{cw+1} β†’ CW{cw+13}") corr = load_corrections() n_fac = sum(1 for v in corr.get("factors", {}).values() if any(float(x) != 1.0 for x in v.values())) c2.info(f"**Corrections**\n{n_fac} SKUs with factors") if status["forecast"]: c3.info(f"**Last run**\n{status.get('forecast_date','')}") else: c3.info("**Last run**\nNever") if st.button("πŸš€ Run forecast engine", type="primary", use_container_width=True): script = Path("forecast_engine.py").resolve() if not script.exists(): st.error("forecast_engine.py not found next to app.py") return planning_book = list(DATA_DIR.glob("Polleo_Demand_Planning_Book*.xlsx"))[0] # ---- v3.6: PRE-FLIGHT Excel-lock check ---- # On Windows, if Excel has the file open, openpyxl's save() will fail # with PermissionError β€” but the engine catches the exception, prints # it, and exits 0. The app then falsely reports success. Check # writability up front and bail loudly if the file is locked. if OUTPUT_FILE.exists(): try: # Try to open for append (doesn't modify content but requires # write access). On locked files this raises PermissionError. with open(OUTPUT_FILE, "ab"): pass except PermissionError: st.error( f"❌ Cannot write to `{OUTPUT_FILE.name}` β€” the file is " f"locked by another program (almost always Excel on Windows).\n\n" f"**Fix:** Close `{OUTPUT_FILE.name}` in Excel (or any other " f"program that has it open), then click **Run forecast** again.\n\n" f"I stopped here instead of running the engine because the " f"engine would have run for ~2 minutes and then silently " f"failed at the save step β€” you'd have no new plan and no " f"clear error." ) return except Exception as lock_err: st.warning( f"Could not verify write access to {OUTPUT_FILE.name} " f"(`{type(lock_err).__name__}: {lock_err}`). Proceeding " f"anyway, but the engine may fail at the save step." ) # ---- v3.6: Back up previous plan before the engine overwrites it ---- # Keeps the last 5 plans in data/plan_history/ so nothing is ever lost # (audit trail + Excel-lock-recovery). if OUTPUT_FILE.exists(): try: hist_dir = DATA_DIR / "plan_history" hist_dir.mkdir(exist_ok=True) backup_ts = datetime.fromtimestamp( OUTPUT_FILE.stat().st_mtime ).strftime("%Y%m%d_%H%M%S") backup_name = f"Polleo_Demand_Plan_{backup_ts}.xlsx" backup_path = hist_dir / backup_name if not backup_path.exists(): import shutil shutil.copy2(OUTPUT_FILE, backup_path) # Prune to last 5 backups backups = sorted( hist_dir.glob("Polleo_Demand_Plan_*.xlsx"), key=lambda p: p.stat().st_mtime, reverse=True, ) for old in backups[5:]: old.unlink(missing_ok=True) st.caption(f"πŸ’Ύ Backed up previous plan β†’ `plan_history/{backup_name}`") except Exception as bk_err: st.warning( f"Could not back up previous plan: {bk_err}. " f"Proceeding anyway β€” engine will overwrite the current plan." ) progress = st.progress(0, "Applying corrections...") apply_corrections_to_xlsx() progress.progress(10, "Starting engine...") log_area = st.empty() log_lines = [] import time import copy as _copy2 env2 = _copy2.copy(os.environ) env2["PYTHONIOENCODING"] = "utf-8" proc = subprocess.Popen( ["python", "-u", str(script), str(planning_book.resolve())], cwd=str(DATA_DIR.resolve()), stdin=subprocess.DEVNULL, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, bufsize=1, env=env2, encoding="utf-8", errors="replace" ) stage_map = { "Loading data": 20, "Cleaning": 30, "Samples": 40, "GBR MAE": 60, "BACKTEST": 80, "Building workbook": 85, "Saved": 95, "DONE": 100, } start = time.time() for line in proc.stdout: line = line.rstrip() if line: log_lines.append(line) log_area.code("\n".join(log_lines[-20:])) for keyword, pct in stage_map.items(): if keyword in line: elapsed = int(time.time() - start) progress.progress(pct, f"{line.strip()} ({elapsed}s)") break proc.wait() elapsed = int(time.time() - start) if proc.returncode == 0: progress.progress(100, f"Done in {elapsed}s!") st.success(f"Forecast complete! ({elapsed} seconds)") st.balloons() clear_all_caches() # ---- Verify new plan file is actually on disk ---- if not OUTPUT_FILE.exists(): st.error( f"⚠️ Forecast reported success but {OUTPUT_FILE.name} " f"was NOT saved. Possible causes: Excel had the file open " f"(Windows lock), disk full, or the engine crashed before " f"the save step. Close Excel and try again." ) else: plan_size = OUTPUT_FILE.stat().st_size plan_mod = datetime.fromtimestamp(OUTPUT_FILE.stat().st_mtime) age_sec = (datetime.now() - plan_mod).total_seconds() if age_sec > 60: st.warning( f"⚠️ {OUTPUT_FILE.name} exists ({plan_size:,} bytes) " f"but was last modified {int(age_sec)}s ago β€” this is " f"older than the run that just completed. The save " f"may have failed silently. Check the engine log above " f"for errors near 'Saved:'." ) else: st.caption( f"βœ… {OUTPUT_FILE.name} saved " f"({plan_size:,} bytes, " f"{plan_mod.strftime('%H:%M:%S')})" ) # ---- Auto-save consensus snapshot (v3.6: errors surfaced) ---- try: snap_name = save_consensus_snapshot(f"CW{cw} auto-save") if snap_name: st.info(f"πŸ“Έ Auto-saved consensus snapshot: {snap_name}") else: st.warning( "⚠️ Consensus snapshot was not created " "(save_consensus_snapshot returned None). " "Most likely cause: the demand plan xlsx couldn't be " "read immediately after the engine saved it. Try " "opening the Consensus page and manually saving a " "snapshot, or re-run the forecast." ) except Exception as snap_err: st.error( f"⚠️ Consensus snapshot failed: " f"`{type(snap_err).__name__}: {snap_err}`. " f"The forecast itself succeeded and the plan is on disk β€” " f"only the snapshot wasn't captured. " f"You can retry via Consensus page β†’ Save current plan." ) else: progress.progress(100, "Error") st.error(f"Forecast failed after {elapsed}s") st.code("\n".join(log_lines[-30:])) def page_input(sheet_name, label, key_prefix): """VP or MP input page.""" st.title(f"Demand input β€” {label}") st.caption("Enter on-top demand and regular increases per SKU per week.") data = load_input_sheet(sheet_name) if data is None: st.warning("Run the forecast engine first.") return cws = data["cws"] corrections = load_corrections() search = st.text_input("πŸ” Search SKU", "", key=f"{key_prefix}_search") for rtype in ["on-top demand", "regular increase"]: type_rows = [r for r in data["rows"] if r["Type"] == rtype] if search: sl = search.lower() type_rows = [r for r in type_rows if sl in r["SKU"].lower() or sl in r["Name"].lower()] st.subheader(f"{rtype.title()} ({len(type_rows)} SKUs)") df_rows = [] for r in type_rows: saved = corrections.get(key_prefix, {}).get(r["SKU"], {}) row = {"SKU": r["SKU"], "Name": r["Name"][:35], "Cat": r["Category"]} for j, cw_label in enumerate(cws): cw_key = f"{rtype}_{cw_label.replace('CW', '')}" if cw_key in saved: row[cw_label] = int(saved[cw_key]) elif j < len(r["values"]): row[cw_label] = int(r["values"][j]) else: row[cw_label] = 0 df_rows.append(row) if not df_rows: st.info("No matching SKUs.") continue col_cfg = { "SKU": st.column_config.TextColumn("SKU", width=120, disabled=True), "Name": st.column_config.TextColumn("Name", width=180, disabled=True), "Cat": st.column_config.TextColumn("Cat", width=130, disabled=True), } for cw_label in cws: col_cfg[cw_label] = st.column_config.NumberColumn( cw_label, min_value=0, step=1, format="%d", width=70 ) edited = st.data_editor( pd.DataFrame(df_rows), column_config=col_cfg, use_container_width=True, hide_index=True, num_rows="fixed", key=f"{key_prefix}_{rtype}_ed" ) if st.button(f"πŸ’Ύ Save {rtype}", key=f"save_{key_prefix}_{rtype}"): for _, row in edited.iterrows(): sku = row["SKU"] vals = {} for cw_label in cws: v = int(row[cw_label]) if v > 0: vals[f"{rtype}_{cw_label.replace('CW', '')}"] = v if vals: corrections.setdefault(key_prefix, {})[sku] = { **corrections.get(key_prefix, {}).get(sku, {}), **vals } save_corrections(corrections) st.success(f"Saved {rtype}!") def page_revenue(): st.title("Revenue & RUC projections") plan = load_demand_plan() if plan is None: st.warning("Run the forecast engine first.") return sku_prices = load_sku_prices() cws = plan["cws"] rows = plan["rows"] # RUC per unit from sales β€” split by channel (wholesale vs retail margins differ) sc = load_sales_data() ruc_per_unit = {} # sku -> {'retail': X, 'wholesale': Y, 'blended': Z} has_ruc_data = False if sc is not None and "ruc_total" in sc.columns and sc["ruc_total"].sum() > 0: has_ruc_data = True recent_yw = sc[["year", "week"]].drop_duplicates().sort_values(["year", "week"]).tail(4) recent_keys_int = set(recent_yw["year"].astype(int) * 100 + recent_yw["week"].astype(int)) _yw_key = sc["year"].astype(int) * 100 + sc["week"].astype(int) sc_recent = sc[_yw_key.isin(recent_keys_int)] for sku, grp in sc_recent.groupby("sku"): qr = grp["qty_retail"].sum() + grp["qty_webshop"].sum() qw = grp["qty_wholesale"].sum() rr = grp.get("ruc_retail", pd.Series([0]*len(grp))).sum() + grp.get("ruc_webshop", pd.Series([0]*len(grp))).sum() rw = grp.get("ruc_wholesale", pd.Series([0]*len(grp))).sum() tq = grp["qty_total"].sum() tr = grp["ruc_total"].sum() ruc_per_unit[sku] = { "retail": rr / qr if qr > 0 else 0, "wholesale": rw / qw if qw > 0 else 0, "blended": tr / tq if tq > 0 and tr != 0 else 0, } def _get_rate(sku, channel="blended"): """Get price or RUC rate for a SKU. Channel: 'retail', 'wholesale', 'blended'.""" if is_ruc: r = ruc_per_unit.get(sku, {}) return r.get(channel, r.get("blended", 0)) if isinstance(r, dict) else r return sku_prices.get(sku, 0) # ---- Compute per-SKU wholesale share from recent history ---- # Used to split baseline forecast into wholesale / retail channel portions # so "VP only" and "MP only" filters can show full channel forecast # (baseline + on-top), not just the on-top bars. ws_share_by_sku = {} if sc is not None and len(sc) > 0: recent_yw = sc[["year", "week"]].drop_duplicates().sort_values(["year", "week"]).tail(13) recent_keys_int = set(recent_yw["year"].astype(int) * 100 + recent_yw["week"].astype(int)) _yw_key = sc["year"].astype(int) * 100 + sc["week"].astype(int) sc_recent = sc[_yw_key.isin(recent_keys_int)] for sku, grp in sc_recent.groupby("sku"): qw = float(grp["qty_wholesale"].sum()) qr = float(grp["qty_retail"].sum()) + float(grp["qty_webshop"].sum()) tot = qw + qr ws_share_by_sku[sku] = (qw / tot) if tot > 0 else 0.0 def _ws_share(sku): return ws_share_by_sku.get(sku, 0.0) # ---- Filters ---- from datetime import datetime as _dt _iso = _dt.now().isocalendar() _cur_y, _cur_w = int(_iso[0]), int(_iso[1]) def _cw_to_date(cw_label): w = int(str(cw_label).replace("CW", "")) y = _cur_y if w >= _cur_w else _cur_y + 1 return _dt.strptime(f"{y}-W{w:02d}-1", "%G-W%V-%u") cw_dates = [_cw_to_date(c) for c in cws] cw_months = [d.strftime("%b %Y") for d in cw_dates] month_order = [] for m in cw_months: if m not in month_order: month_order.append(m) c1, c2, c3 = st.columns(3) view_mode = c1.radio("View", ["Revenue", "RUC (margin)"] if has_ruc_data else ["Revenue"], horizontal=True, key="rev_mode") is_ruc = view_mode.startswith("RUC") all_cats = sorted(set(r["Category"] for r in rows if r["Category"])) cat_filter = c2.selectbox("Category", ["All"] + all_cats, key="rev_cat") source_filter = c3.selectbox("Source", ["All", "VP only", "MP only"], key="rev_source") c4, c5, c6 = st.columns([2, 2, 1.2]) month_sel = c4.multiselect("Months", month_order, default=month_order, key="rev_month") week_sel = c5.multiselect("Weeks", cws, default=cws, key="rev_week") # Gross-up toggle: include non-planned SKUs (those not in sku_plan_list). # Works with any source_filter β€” we compute a separate wholesale vs retail # gross-up ratio so VP-only and MP-only views include their channel share of # non-planned SKUs. Still disabled when a category is selected, because # non-planned items lack reliable category attribution. can_grossup = (cat_filter == "All" and sc is not None) include_nonplanned = c6.checkbox( "Include non-planned SKUs", value=can_grossup, disabled=not can_grossup, key="rev_inc_nonplanned", help="Past: real revenue across ALL SKUs. Forecast: planned Γ— channel-specific gross-up ratio.", ) if can_grossup else False # Intersect month and week filters (both must agree for a CW to be included). cws_by_month = set(c for c, m in zip(cws, cw_months) if m in month_sel) if month_sel else set(cws) cws_by_week = set(week_sel) if week_sel else set(cws) active_set = cws_by_month & cws_by_week if not active_set: active_set = set(cws) week_mask = [c in active_set for c in cws] # Load VP/MP inputs inputs = load_vp_mp_inputs() # ---- Compute per-category per-week values ---- filtered_rows = rows if cat_filter == "All" else [r for r in rows if r["Category"] == cat_filter] cat_vals = {} # baseline blended (legacy, used for "All" display) cat_vals_retail = {} # baseline retail portion cat_vals_ws = {} # baseline wholesale portion cat_vp = {} cat_mp = {} for r in filtered_rows: cat = r["Category"] or "OTHER" sku = r["SKU"] rate_bl = _get_rate(sku, "blended") rate_r = _get_rate(sku, "retail") rate_w = _get_rate(sku, "wholesale") wshare = _ws_share(sku) if cat not in cat_vals: cat_vals[cat] = [0.0] * len(cws) cat_vals_retail[cat] = [0.0] * len(cws) cat_vals_ws[cat] = [0.0] * len(cws) cat_vp[cat] = [0.0] * len(cws) cat_mp[cat] = [0.0] * len(cws) for j in range(min(len(cws), len(r["baseline"]))): f = r["factors"][j] if j < len(r["factors"]) else 1.0 units = r["baseline"][j] * f cat_vals[cat][j] += units * rate_bl cat_vals_ws[cat][j] += units * wshare * rate_w cat_vals_retail[cat][j] += units * (1 - wshare) * rate_r # VP contribution β€” uses WHOLESALE margin rate if inputs["vp"] is not None and cws: filtered_skus = set(r["SKU"] for r in filtered_rows) sku_inp = inputs["vp"][inputs["vp"]["sku"].isin(filtered_skus)] for _, inp_row in sku_inp.iterrows(): sku = inp_row["sku"] cat = next((r["Category"] for r in rows if r["SKU"] == sku), "OTHER") or "OTHER" rate = _get_rate(sku, "wholesale") if cat not in cat_vp: cat_vp[cat] = [0.0] * len(cws) for j, cw_label in enumerate(cws): v = float(inp_row.get(cw_label, 0) or 0) cat_vp[cat][j] += v * rate # MP contribution β€” uses RETAIL margin rate if inputs["mp"] is not None and cws: filtered_skus = set(r["SKU"] for r in filtered_rows) sku_inp = inputs["mp"][inputs["mp"]["sku"].isin(filtered_skus)] for _, inp_row in sku_inp.iterrows(): sku = inp_row["sku"] cat = next((r["Category"] for r in rows if r["SKU"] == sku), "OTHER") or "OTHER" rate = _get_rate(sku, "retail") if cat not in cat_mp: cat_mp[cat] = [0.0] * len(cws) for j, cw_label in enumerate(cws): v = float(inp_row.get(cw_label, 0) or 0) cat_mp[cat][j] += v * rate # Totals total_base = [sum(cat_vals[c][j] for c in cat_vals) for j in range(len(cws))] total_base_ws = [sum(cat_vals_ws[c][j] for c in cat_vals_ws) for j in range(len(cws))] total_base_retail = [sum(cat_vals_retail[c][j] for c in cat_vals_retail) for j in range(len(cws))] total_vp = [sum(cat_vp.get(c, [0]*len(cws))[j] for c in cat_vals) for j in range(len(cws))] total_mp = [sum(cat_mp.get(c, [0]*len(cws))[j] for c in cat_vals) for j in range(len(cws))] total_all = [total_base[j] + total_vp[j] + total_mp[j] for j in range(len(cws))] # Display line based on source filter. # "VP only" = wholesale channel forecast (baseline ws portion + VP on-tops) # "MP only" = retail channel forecast (baseline retail portion + MP on-tops) if source_filter == "VP only": display_line = [total_base_ws[j] + total_vp[j] for j in range(len(cws))] line_label = "Wholesale forecast (baseline + VP)" elif source_filter == "MP only": display_line = [total_base_retail[j] + total_mp[j] for j in range(len(cws))] line_label = "Retail forecast (baseline + MP)" else: display_line = total_all line_label = f"Total {'RUC' if is_ruc else 'Revenue'}" # Apply week mask for the filtered totals & chart cws_f = [c for c, keep in zip(cws, week_mask) if keep] display_line_f = [v for v, keep in zip(display_line, week_mask) if keep] total_vp_f = [v for v, keep in zip(total_vp, week_mask) if keep] total_mp_f = [v for v, keep in zip(total_mp, week_mask) if keep] total_base_ws_f = [v for v, keep in zip(total_base_ws, week_mask) if keep] total_base_retail_f = [v for v, keep in zip(total_base_retail, week_mask) if keep] # ---- Gross-up ratios (per channel) for non-planned SKUs ---- # Compute three ratios from the last ~13 weeks of history: # gross_up_ws = total_wholesale_all / total_wholesale_planned # gross_up_retail = total_retail_all / total_retail_planned # gross_up = blended (for source=All) # The applied multiplier is picked based on source_filter. gross_up_ws, gross_up_retail, gross_up = 1.0, 1.0, 1.0 nonplan_share_ws_pct = nonplan_share_retail_pct = nonplan_share_all_pct = 0.0 if include_nonplanned and sc is not None: planned_set = set(r["SKU"] for r in rows) recent_yw = sc[["year", "week"]].drop_duplicates().sort_values(["year", "week"]).tail(13) recent_keys_int = set(recent_yw["year"].astype(int) * 100 + recent_yw["week"].astype(int)) _yw_key = sc["year"].astype(int) * 100 + sc["week"].astype(int) sc_recent = sc[_yw_key.isin(recent_keys_int)].copy() plan_mask = sc_recent["sku"].isin(planned_set) if is_ruc: ws_all = float(sc_recent["ruc_wholesale"].sum()) ws_plan = float(sc_recent[plan_mask]["ruc_wholesale"].sum()) rt_all = float(sc_recent["ruc_retail"].sum() + sc_recent["ruc_webshop"].sum()) rt_plan = float(sc_recent[plan_mask]["ruc_retail"].sum() + sc_recent[plan_mask]["ruc_webshop"].sum()) else: ws_all = ws_plan = rt_all = rt_plan = 0.0 for _, rr in sc_recent.iterrows(): price = sku_prices.get(rr["sku"], 0) ws = float(rr["qty_wholesale"]) * price rt = (float(rr["qty_retail"]) + float(rr["qty_webshop"])) * price ws_all += ws rt_all += rt if rr["sku"] in planned_set: ws_plan += ws rt_plan += rt if ws_plan > 0 and ws_all > ws_plan: gross_up_ws = ws_all / ws_plan nonplan_share_ws_pct = (1 - ws_plan / ws_all) * 100 if rt_plan > 0 and rt_all > rt_plan: gross_up_retail = rt_all / rt_plan nonplan_share_retail_pct = (1 - rt_plan / rt_all) * 100 total_all = ws_all + rt_all total_plan = ws_plan + rt_plan if total_plan > 0 and total_all > total_plan: gross_up = total_all / total_plan nonplan_share_all_pct = (1 - total_plan / total_all) * 100 # Effective gross-up for the current source_filter view if source_filter == "VP only": effective_gu = gross_up_ws effective_share_pct = nonplan_share_ws_pct elif source_filter == "MP only": effective_gu = gross_up_retail effective_share_pct = nonplan_share_retail_pct else: effective_gu = gross_up effective_share_pct = nonplan_share_all_pct # ---- Actuals for past weeks in selected months ---- # If the user picked months that include weeks already behind us, append # realised revenue/RUC from sales_clean so the chart shows actuals + forecast. past_cws, past_actuals = [], [] if sc is not None and month_sel: # When gross-up is on, past actuals include ALL SKUs (not just planned). if include_nonplanned: sc_f = sc.copy() else: filtered_skus = set(r["SKU"] for r in filtered_rows) sc_f = sc[sc["sku"].isin(filtered_skus)].copy() sc_f["_y"] = sc_f["year"].astype(int) sc_f["_w"] = sc_f["week"].astype(int) past_rows = sc_f.groupby(["_y", "_w"], as_index=False).agg({ "qty_retail": "sum", "qty_webshop": "sum", "qty_wholesale": "sum", "qty_total": "sum", "ruc_retail": "sum", "ruc_webshop": "sum", "ruc_wholesale": "sum", "ruc_total": "sum", }) for _, r in past_rows.iterrows(): y, w = int(r["_y"]), int(r["_w"]) # Strictly past weeks only (exclude current ongoing week) if (y, w) >= (_cur_y, _cur_w): continue try: d = _dt.strptime(f"{y}-W{w:02d}-1", "%G-W%V-%u") except ValueError: continue m = d.strftime("%b %Y") if m not in month_sel: continue # Compute value per source_filter + view_mode if is_ruc: if source_filter == "VP only": val = float(r["ruc_wholesale"]) elif source_filter == "MP only": val = float(r["ruc_retail"]) + float(r["ruc_webshop"]) else: val = float(r["ruc_total"]) else: # Revenue: recompute per SKU so we use actual prices, not aggregates wk_skus = sc_f[(sc_f["_y"] == y) & (sc_f["_w"] == w)] val = 0.0 for _, sr in wk_skus.iterrows(): sku = sr["sku"] price = sku_prices.get(sku, 0) if source_filter == "VP only": val += float(sr["qty_wholesale"]) * price elif source_filter == "MP only": val += (float(sr["qty_retail"]) + float(sr["qty_webshop"])) * price else: val += float(sr["qty_total"]) * price past_cws.append((d, f"CW{w}", val)) past_cws.sort(key=lambda t: t[0]) past_labels = [t[1] for t in past_cws] past_values = [t[2] for t in past_cws] # Apply gross-up to forecast line only. On-top bars (VP/MP) are literal # planner commitments and must NOT be scaled. Use channel-specific ratio # so VP-only and MP-only views also reflect non-planned SKUs. if effective_gu != 1.0: display_line_f = [v * effective_gu for v in display_line_f] # ---- Current-week forecast ---- # The forecast engine starts at CW+1, so there is no explicit forecast for # the current week. Proxy it with the first forecast week's value (same # channel logic as display_line) when the current week's month is selected. cur_label, cur_value = None, None try: _cur_date = _dt.strptime(f"{_cur_y}-W{_cur_w:02d}-1", "%G-W%V-%u") _cur_month = _cur_date.strftime("%b %Y") if _cur_month in month_sel and len(display_line) > 0: cur_label = f"CW{_cur_w}" cur_value = float(display_line[0]) * effective_gu except ValueError: pass total_13wk = sum(display_line_f) + sum(past_values) + (cur_value or 0.0) unit = "RUC" if is_ruc else "Revenue" # ---- Metrics ---- extra = 1 if cur_value is not None else 0 total_weeks_shown = len(cws_f) + len(past_cws) + extra n_weeks_f = max(total_weeks_shown, 1) c1m, c2m, c3m = st.columns(3) _help = None if past_cws or extra: _help = f"{len(past_cws)} actual + {extra} current + {len(cws_f)} forecast weeks" c1m.metric(f"{total_weeks_shown}-week {unit}", f"\u20ac{total_13wk:,.0f}", help=_help) c2m.metric("Avg weekly", f"\u20ac{total_13wk / n_weeks_f:,.0f}") if include_nonplanned and effective_gu > 1.0: _ch_label = {"VP only": "Wholesale", "MP only": "Retail+Webshop"}.get(source_filter, "All channels") st.caption( f"{_ch_label}: non-planned SKUs contribute ~{effective_share_pct:.1f}% of recent {unit.lower()} " f"(gross-up Γ—{effective_gu:.2f} applied to forecast line only; VP/MP on-top bars unchanged)." ) elif include_nonplanned and effective_gu == 1.0: st.caption("No non-planned SKUs detected in recent history for this channel β€” gross-up is 1.00x.") if is_ruc and sum(total_base) > 0: # Revenue denominator that MIRRORS total_13wk scope: # past actuals + current-week proxy + forecast weeks, same source_filter, # same SKU set (planned-only for forecast, all SKUs for past if gross-up on), # and the same channel-specific gross-up applied to forecast weeks. # --- Forecast-week revenue (baseline + VP + MP on-tops) --- rev_fcst_base = 0.0 for r in filtered_rows: price = sku_prices.get(r["SKU"], 0) wshare = _ws_share(r["SKU"]) for j in range(len(cws)): if not week_mask[j]: continue f = r["factors"][j] if j < len(r["factors"]) else 1.0 units = r["baseline"][j] * f if source_filter == "VP only": rev_fcst_base += units * wshare * price elif source_filter == "MP only": rev_fcst_base += units * (1 - wshare) * price else: rev_fcst_base += units * price rev_fcst_base *= effective_gu # match RUC line treatment rev_fcst_ontop = 0.0 filtered_skus = set(r["SKU"] for r in filtered_rows) if source_filter != "MP only" and inputs["vp"] is not None: sku_inp = inputs["vp"][inputs["vp"]["sku"].isin(filtered_skus)] for _, inp_row in sku_inp.iterrows(): price = sku_prices.get(inp_row["sku"], 0) for j, cw_label in enumerate(cws): if not week_mask[j]: continue rev_fcst_ontop += float(inp_row.get(cw_label, 0) or 0) * price if source_filter != "VP only" and inputs["mp"] is not None: sku_inp = inputs["mp"][inputs["mp"]["sku"].isin(filtered_skus)] for _, inp_row in sku_inp.iterrows(): price = sku_prices.get(inp_row["sku"], 0) for j, cw_label in enumerate(cws): if not week_mask[j]: continue rev_fcst_ontop += float(inp_row.get(cw_label, 0) or 0) * price rev_fcst = rev_fcst_base + rev_fcst_ontop # --- Past revenue from sales_clean (same weeks as past_cws) --- rev_past = 0.0 if sc is not None and month_sel: if include_nonplanned: sc_f_rev = sc.copy() else: sc_f_rev = sc[sc["sku"].isin(set(r["SKU"] for r in filtered_rows))].copy() sc_f_rev["_y"] = sc_f_rev["year"].astype(int) sc_f_rev["_w"] = sc_f_rev["week"].astype(int) for _, sr in sc_f_rev.iterrows(): y, w = int(sr["_y"]), int(sr["_w"]) if (y, w) >= (_cur_y, _cur_w): continue try: d = _dt.strptime(f"{y}-W{w:02d}-1", "%G-W%V-%u") except ValueError: continue if d.strftime("%b %Y") not in month_sel: continue price = sku_prices.get(sr["sku"], 0) if source_filter == "VP only": rev_past += float(sr["qty_wholesale"]) * price elif source_filter == "MP only": rev_past += (float(sr["qty_retail"]) + float(sr["qty_webshop"])) * price else: rev_past += float(sr["qty_total"]) * price # --- Current-week proxy (first forecast week's revenue Γ— effective_gu) --- rev_cur = 0.0 if cur_value is not None and len(cws) > 0: first_rev = 0.0 for r in filtered_rows: price = sku_prices.get(r["SKU"], 0) wshare = _ws_share(r["SKU"]) f = r["factors"][0] if len(r["factors"]) > 0 else 1.0 units = r["baseline"][0] * f if source_filter == "VP only": first_rev += units * wshare * price elif source_filter == "MP only": first_rev += units * (1 - wshare) * price else: first_rev += units * price rev_cur = first_rev * effective_gu rev_total = rev_fcst + rev_past + rev_cur c3m.metric("Margin %", f"{total_13wk / max(rev_total, 1) * 100:.1f}%") else: c3m.metric("Categories", len(cat_vals)) # ---- Chart: line + VP/MP bars ---- fig = go.Figure() # Combined x-axis: actuals first, then current (if applicable), then forecast cur_x = [cur_label] if cur_value is not None else [] combined_x = past_labels + cur_x + cws_f combined_y_forecast = ( [None] * len(past_labels) + ([round(cur_value)] if cur_value is not None else []) + [round(v) for v in display_line_f] ) combined_y_actual = ( [round(v) for v in past_values] + [None] * len(cur_x) + [None] * len(cws_f) ) # Bridge the visual gap between actuals and forecast: duplicate the last # actual value into the forecast series at that x so the forecast line # starts from the same point. Suppress the extra label to avoid overlap. _bridge_idx = None if past_labels and (cur_x or cws_f): _bridge_idx = len(past_labels) - 1 combined_y_forecast[_bridge_idx] = combined_y_actual[_bridge_idx] # Actuals (solid, darker) if past_labels: fig.add_trace(go.Scatter( x=combined_x, y=combined_y_actual, mode="lines+markers+text", line=dict(color="#444444", width=2.5), marker=dict(size=7, symbol="square"), text=[f"{int(v):,}" if v is not None else "" for v in combined_y_actual], textposition="top center", textfont=dict(size=9), name=f"Actual {unit}", connectgaps=False, )) # Forecast line _fc_text = [] for i, v in enumerate(combined_y_forecast): if v is None or i == _bridge_idx: _fc_text.append("") else: _fc_text.append(f"{int(v):,}") fig.add_trace(go.Scatter( x=combined_x, y=combined_y_forecast, mode="lines+markers+text", line=dict(color="#2F5496" if not is_ruc else "#006600", width=2.5, dash="dot" if past_labels else "solid"), marker=dict(size=6), text=_fc_text, textposition="top center", textfont=dict(size=9), name=line_label, connectgaps=False, )) # VP bars (align with combined x axis β€” blank over past + current proxy weeks) _pad = [0] * (len(past_labels) + len(cur_x)) _pad_txt = [""] * (len(past_labels) + len(cur_x)) if source_filter in ("All", "VP only") and any(v > 0 for v in total_vp_f): fig.add_trace(go.Bar( x=combined_x, y=_pad + [round(v) for v in total_vp_f], name=f"VP on-top {unit}", marker_color="rgba(47, 84, 150, 0.45)", text=_pad_txt + [f"{int(v):,}" if v > 50 else "" for v in total_vp_f], textposition="outside", textfont=dict(size=8), width=0.4, )) # MP bars if source_filter in ("All", "MP only") and any(v > 0 for v in total_mp_f): fig.add_trace(go.Bar( x=combined_x, y=_pad + [round(v) for v in total_mp_f], name=f"MP on-top {unit}", marker_color="rgba(255, 107, 53, 0.45)", text=_pad_txt + [f"{int(v):,}" if v > 50 else "" for v in total_mp_f], textposition="outside", textfont=dict(size=8), width=0.4, )) fig.update_layout( barmode="overlay", height=420, yaxis=dict(title=f"{unit} (\u20ac)", tickformat=","), margin=dict(l=60, r=20, t=30, b=40), hovermode="x unified", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", legend=dict(orientation="h", y=1.05), ) fig.update_xaxes(showgrid=False) fig.update_yaxes(showgrid=True, gridcolor="rgba(200,200,200,0.3)") st.plotly_chart(fig, use_container_width=True) # ---- Category breakdown table ---- def _sum_filtered(arr): return sum(v for v, keep in zip(arr, week_mask) if keep) st.subheader(f"{unit} by category ({len(cws_f)} weeks)") cat_table = [] for cat in sorted(cat_vals.keys()): base_total = _sum_filtered(cat_vals[cat]) base_ws = _sum_filtered(cat_vals_ws.get(cat, [0]*len(cws))) base_rt = _sum_filtered(cat_vals_retail.get(cat, [0]*len(cws))) vp_total = _sum_filtered(cat_vp.get(cat, [0]*len(cws))) mp_total = _sum_filtered(cat_mp.get(cat, [0]*len(cws))) if source_filter == "VP only": row_total = base_ws + vp_total elif source_filter == "MP only": row_total = base_rt + mp_total else: row_total = base_total + vp_total + mp_total cat_table.append({ "Category": cat, f"Baseline {unit}": round(base_total), f"VP on-top {unit}": round(vp_total), f"MP on-top {unit}": round(mp_total), f"Shown {unit}": round(row_total), }) if cat_table: cdf = pd.DataFrame(cat_table).sort_values(f"Shown {unit}", ascending=False) st.dataframe(cdf, use_container_width=True, hide_index=True) # ---- Revenue bridge ---- snapshots = load_consensus_snapshots() if snapshots: st.divider() st.subheader(f"{unit} bridge vs. last snapshot") bridge = compute_revenue_bridge(plan, snapshots) if bridge: fig_w = go.Figure(go.Waterfall( name="Bridge", orientation="v", x=[bridge["prev_label"], "Volume", "VP/MP inputs", "New SKUs", "Discontinued", "This cycle"], y=[bridge["prev_total"], bridge["volume_change"], bridge["vp_mp"], bridge["new_skus"], -bridge["removed"], bridge["curr_total"]], measure=["absolute", "relative", "relative", "relative", "relative", "total"], connector=dict(line=dict(color="rgba(200,200,200,0.4)")), textposition="outside", text=[f"\u20ac{bridge['prev_total']:,}", f"\u20ac{bridge['volume_change']:+,}", f"\u20ac{bridge['vp_mp']:+,}", f"\u20ac{bridge['new_skus']:+,}", f"\u20ac{-bridge['removed']:+,}", f"\u20ac{bridge['curr_total']:,}"], increasing=dict(marker_color="#97C459"), decreasing=dict(marker_color="#F09595"), totals=dict(marker_color="#85B7EB"), )) fig_w.update_layout(height=350, margin=dict(l=50, r=20, t=30, b=40), plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", yaxis=dict(tickformat=",", title=f"{unit} \u20ac"), showlegend=False) fig_w.update_xaxes(showgrid=False) fig_w.update_yaxes(showgrid=True, gridcolor="rgba(200,200,200,0.3)") st.plotly_chart(fig_w, use_container_width=True) delta = bridge["curr_total"] - bridge["prev_total"] pct = delta / max(bridge["prev_total"], 1) * 100 st.caption(f"Total change: \u20ac{delta:+,} ({pct:+.1f}%)") else: st.info("Cannot compute bridge \u2014 snapshot data incomplete.") else: st.caption("Save a consensus snapshot to enable the revenue bridge chart.") def page_sku_management(): st.title("SKU list management") st.caption("Add, remove, or reclassify planned SKUs. Changes take effect on next forecast run.") PLAN_FILE = DATA_DIR / "sku_plan_list.csv" # ---- Initialize from Planning Book if no plan list yet ---- if not PLAN_FILE.exists(): planning_books = list(DATA_DIR.glob("Polleo_Demand_Planning_Book*.xlsx")) if planning_books: if st.button("Initialize SKU list from Planning Book", type="primary"): from openpyxl import load_workbook as lw wb = lw(planning_books[0], data_only=True) ws = wb["Run Rate"] rows = [] pr = {} if "cijena po artiklu" in wb.sheetnames: ws_p = wb["cijena po artiklu"] for r in range(2, ws_p.max_row+1): s = ws_p.cell(r,1).value if s: pr[s] = float(ws_p.cell(r,5).value or 0) for r in range(4, ws.max_row+1): sku = ws.cell(r,1).value if not sku: continue rows.append({"sku": sku, "name": ws.cell(r,2).value or "", "cat": ws.cell(r,3).value or "", "oznaka": ws.cell(r,4).value or "", "vpc": pr.get(sku, 0)}) wb.close() pd.DataFrame(rows).to_csv(PLAN_FILE, index=False) st.success(f"Created SKU list: {len(rows)} SKUs") st.rerun() else: st.error("No Planning Book or sku_plan_list.csv found.") return # ---- Load current plan list ---- plan = pd.read_csv(PLAN_FILE) # Summary metrics c1, c2, c3, c4 = st.columns(4) c1.metric("Total SKUs", len(plan)) for ozn in OZNAKA_TIERS: n = len(plan[plan["oznaka"] == ozn]) {"01 GOLD": c2, "02 SILVER": c3, "03 BRONZE": c4}[ozn].metric(ozn, n) st.divider() # ---- TABS for different actions ---- tab_view, tab_edit, tab_add, tab_remove = st.tabs([ "πŸ“‹ View list", "✏️ Edit OZNAKA", "βž• Add SKUs", "πŸ—‘οΈ Remove SKUs" ]) # ---- VIEW TAB ---- with tab_view: c1, c2, c3 = st.columns([2, 1, 1]) search = c1.text_input("Search", "", key="sku_mgmt_search") cat_filt = c2.selectbox("Category", ["All"] + sorted(plan["cat"].unique().tolist()), key="sku_mgmt_cat") ozn_filt = c3.selectbox("OZNAKA", ["All"] + OZNAKA_TIERS, key="sku_mgmt_ozn") view = plan.copy() if search: sl = search.lower() view = view[view["sku"].str.lower().str.contains(sl) | view["name"].str.lower().str.contains(sl)] if cat_filt != "All": view = view[view["cat"] == cat_filt] if ozn_filt != "All": view = view[view["oznaka"] == ozn_filt] st.dataframe( view[["sku", "name", "cat", "oznaka"]].rename( columns={"sku": "SKU", "name": "Name", "cat": "Category", "oznaka": "OZNAKA"}), use_container_width=True, hide_index=True ) st.caption(f"Showing {len(view)} of {len(plan)} SKUs") # ---- EDIT OZNAKA TAB ---- with tab_edit: st.caption("Change gold/silver/bronze classification. Filter first, then edit the OZNAKA column.") c1, c2 = st.columns([2, 1]) edit_search = c1.text_input("Search SKU or name", "", key="edit_search") edit_cat = c2.selectbox("Category", ["All"] + sorted(plan["cat"].unique().tolist()), key="edit_cat") edit_df = plan[["sku", "name", "cat", "oznaka"]].copy() if edit_search: sl = edit_search.lower() edit_df = edit_df[edit_df["sku"].str.lower().str.contains(sl) | edit_df["name"].str.lower().str.contains(sl)] if edit_cat != "All": edit_df = edit_df[edit_df["cat"] == edit_cat] if len(edit_df) == 0: st.info("No SKUs match.") else: col_config = { "sku": st.column_config.TextColumn("SKU", width=120, disabled=True), "name": st.column_config.TextColumn("Name", width=250, disabled=True), "cat": st.column_config.TextColumn("Category", width=160, disabled=True), "oznaka": st.column_config.SelectboxColumn("OZNAKA", width=120, options=OZNAKA_TIERS), } edited = st.data_editor(edit_df, column_config=col_config, use_container_width=True, hide_index=True, num_rows="fixed", key="oznaka_editor") if st.button("πŸ’Ύ Save OZNAKA changes", type="primary", key="save_oznaka"): changes = 0 for _, row in edited.iterrows(): mask = plan["sku"] == row["sku"] if plan.loc[mask, "oznaka"].values[0] != row["oznaka"]: plan.loc[mask, "oznaka"] = row["oznaka"] changes += 1 plan.to_csv(PLAN_FILE, index=False) st.success(f"Saved! {changes} SKU(s) reclassified.") st.rerun() # ---- ADD SKUs TAB ---- with tab_add: st.caption("Add SKUs from the full portfolio (9,600+ SKUs in sales data).") cat_map_path = DATA_DIR / "sku_category_map.csv" if not cat_map_path.exists(): st.warning("sku_category_map.csv not found β€” upload it first.") else: all_skus = pd.read_csv(cat_map_path) current_skus = set(plan["sku"]) available = all_skus[~all_skus["sku"].isin(current_skus)].copy() sc_path = DATA_DIR / "sales_clean.csv" if sc_path.exists(): sc = pd.read_csv(sc_path) last_12 = sc.sort_values(["year", "week"]).groupby("sku").tail(12) vol = last_12.groupby("sku")["qty_total"].sum().reset_index() vol.columns = ["sku", "last_12wk_qty"] available = available.merge(vol, on="sku", how="left") available["last_12wk_qty"] = available["last_12wk_qty"].fillna(0).astype(int) available = available.sort_values("last_12wk_qty", ascending=False) st.write(f"**{len(available):,}** SKUs available to add") c1, c2 = st.columns([2, 1]) add_search = c1.text_input("Search by SKU or name", "", key="add_search") add_cat = c2.selectbox("Category", ["All"] + sorted(available["cat"].dropna().unique().tolist()), key="add_cat") filtered = available.copy() if add_search: sl = add_search.lower() filtered = filtered[filtered["sku"].str.lower().str.contains(sl, na=False) | filtered["name"].str.lower().str.contains(sl, na=False)] if add_cat != "All": filtered = filtered[filtered["cat"] == add_cat] show = filtered.head(50).copy() if len(show) == 0: st.info("No matching SKUs.") else: show["add"] = False show["oznaka"] = "03 BRONZE" display_cols = ["add", "sku", "name", "cat", "oznaka"] if "last_12wk_qty" in show.columns: display_cols.insert(4, "last_12wk_qty") col_config = { "add": st.column_config.CheckboxColumn("Add?", width=60), "sku": st.column_config.TextColumn("SKU", width=120, disabled=True), "name": st.column_config.TextColumn("Name", width=250, disabled=True), "cat": st.column_config.TextColumn("Category", width=150, disabled=True), "oznaka": st.column_config.SelectboxColumn("OZNAKA", width=120, options=OZNAKA_TIERS), } if "last_12wk_qty" in show.columns: col_config["last_12wk_qty"] = st.column_config.NumberColumn( "12wk volume", width=100, disabled=True) edited = st.data_editor(show[display_cols], column_config=col_config, use_container_width=True, hide_index=True, num_rows="fixed", key="add_editor") to_add = edited[edited["add"] == True] if len(to_add) > 0: st.write(f"**{len(to_add)}** SKU(s) selected to add") if st.button(f"βž• Add {len(to_add)} SKU(s) to plan", type="primary", key="do_add", disabled=len(to_add)==0): new_rows = [] for _, row in to_add.iterrows(): info = all_skus[all_skus["sku"] == row["sku"]].iloc[0] new_rows.append({ "sku": row["sku"], "name": info.get("name", ""), "cat": info.get("cat", ""), "oznaka": row["oznaka"], "vpc": 0 }) updated = pd.concat([plan, pd.DataFrame(new_rows)], ignore_index=True) updated.to_csv(PLAN_FILE, index=False) st.success(f"Added {len(new_rows)} SKU(s)! Total: {len(updated)}") st.rerun() # ---- REMOVE SKUs TAB ---- with tab_remove: st.caption("Remove SKUs from the planned list. They stay in the portfolio forecast.") c1, c2 = st.columns([2, 1]) rm_search = c1.text_input("Search SKU or name", "", key="rm_search") rm_cat = c2.selectbox("Category", ["All"] + sorted(plan["cat"].unique().tolist()), key="rm_cat") rm_df = plan[["sku", "name", "cat", "oznaka"]].copy() if rm_search: sl = rm_search.lower() rm_df = rm_df[rm_df["sku"].str.lower().str.contains(sl) | rm_df["name"].str.lower().str.contains(sl)] if rm_cat != "All": rm_df = rm_df[rm_df["cat"] == rm_cat] if len(rm_df) == 0: st.info("No matching SKUs.") else: rm_df["remove"] = False col_config = { "remove": st.column_config.CheckboxColumn("Remove?", width=80), "sku": st.column_config.TextColumn("SKU", width=120, disabled=True), "name": st.column_config.TextColumn("Name", width=250, disabled=True), "cat": st.column_config.TextColumn("Category", width=160, disabled=True), "oznaka": st.column_config.TextColumn("OZNAKA", width=110, disabled=True), } edited = st.data_editor(rm_df, column_config=col_config, use_container_width=True, hide_index=True, num_rows="fixed", key="remove_editor") to_remove = edited[edited["remove"] == True] if len(to_remove) > 0: st.warning(f"⚠️ {len(to_remove)} SKU(s) selected for removal") if st.button(f"πŸ—‘οΈ Remove {len(to_remove)} SKU(s)", type="primary", key="do_remove", disabled=len(to_remove)==0): remove_skus = set(to_remove["sku"]) updated = plan[~plan["sku"].isin(remove_skus)] updated.to_csv(PLAN_FILE, index=False) st.success(f"Removed {len(remove_skus)} SKU(s). Remaining: {len(updated)}") st.rerun() def page_kam_inputs(): cy, cw = get_current_cw() cw_start = cw + 1 n_fc = FORECAST_WEEKS cw_labels = [f"CW{cw_start + j}" for j in range(n_fc)] st.title("KAM / CM inputs") st.caption("Slack is the main channel. Upload a fresh base once, then run the weekly Slack cycle from here.") tab_base, tab_slack, tab_view = st.tabs([ "πŸ“‚ Base inputs", "πŸ”” Slack cycle", "πŸ‘οΈ Combined view" ]) # ---- Load SKU list ---- plan_path = DATA_DIR / "sku_plan_list.csv" if not plan_path.exists(): st.warning("No sku_plan_list.csv found. Go to SKU list page first.") return plan = pd.read_csv(plan_path) # ---- BASE INPUTS ---- with tab_base: st.subheader("Seed VP / MP base from existing files") st.caption( "Pick a KAM/CM from the roster and upload their file β€” filename doesn't matter. " "Each slot rewrites that person's rows in `vp_input_detail.csv` / `mp_input_detail.csv`. " "Repeat for every person, then click **Save**." ) # Load roster from config roster = [] cfg_path = DATA_DIR / "kam_cm_config.json" if cfg_path.exists(): try: cfg = json.loads(cfg_path.read_text()) for key, info in (cfg.get("kam_cm_config") or {}).items(): role = str(info.get("role", "")).upper() if role not in ("VP", "MP"): continue roster.append({ "key": key, "name": info.get("display_name", key), "role": role, }) except Exception as e: st.warning(f"Could not read kam_cm_config.json: {e}") if not roster: st.error("No KAM/CM roster found in data/kam_cm_config.json. " "Configure names and roles there first.") return from openpyxl import load_workbook as lw staged_vp, staged_mp = [], [] # One uploader slot per roster entry for r in roster: c1, c2, c3 = st.columns([1.2, 0.5, 3]) c1.markdown(f"**{r['name']}**") c2.markdown(f"_{r['role']}_") uf = c3.file_uploader( f"File for {r['name']} ({r['role']})", type=["xlsx"], key=f"kam_base_up_{r['key']}", label_visibility="collapsed", ) if uf is None: continue tmp_path = DATA_DIR / f"_tmp_{r['key']}.xlsx" tmp_path.write_bytes(uf.getvalue()) target = staged_vp if r["role"] == "VP" else staged_mp parsed_any = False try: wb = lw(tmp_path, data_only=True) data_sheets = [s for s in wb.sheetnames if s != "_meta"] rows_added = 0 for sheet_name in data_sheets: ws = wb[sheet_name] buyer = sheet_name if len(data_sheets) > 1 else "" # Auto-detect Type column (VP has Subkategorija β†’ col 6; MP β†’ col 5) type_col = 5 for c in range(1, 10): hv = ws.cell(4, c).value if hv and str(hv).strip().lower() == "type": type_col = c break file_cws = {} for c in range(type_col + 1, ws.max_column + 1): v = ws.cell(4, c).value if v and str(v).startswith("CW"): file_cws[str(v)] = c if not file_cws: continue for row in range(5, ws.max_row + 1): sku = ws.cell(row, 1).value if not sku: continue rtype = ws.cell(row, type_col).value or "" row_data = {"sku": str(sku), "type": str(rtype), "kam": r["name"], "buyer": buyer} has_data = False for cw_label, col in file_cws.items(): v = ws.cell(row, col).value if v and isinstance(v, (int, float)) and v > 0: row_data[cw_label] = int(v) has_data = True else: row_data[cw_label] = 0 if has_data: target.append(row_data) rows_added += 1 wb.close() if rows_added > 0: parsed_any = True st.caption(f"βœ… {r['name']} ({r['role']}): {rows_added} rows (template format)") except Exception: pass if not parsed_any: try: vp_rows, mp_rows = _parse_legacy_format(tmp_path, uf.name) # Legacy parser infers channel per-block; keep its routing. for legacy_row in vp_rows: legacy_row["kam"] = r["name"] for legacy_row in mp_rows: legacy_row["kam"] = r["name"] staged_vp.extend(vp_rows) staged_mp.extend(mp_rows) if vp_rows or mp_rows: st.caption(f"βœ… {r['name']}: {len(vp_rows)} VP + {len(mp_rows)} MP rows (legacy)") else: st.warning(f"⚠️ {r['name']}: no rows detected.") except Exception as e: st.warning(f"⚠️ {r['name']}: {e}") tmp_path.unlink(missing_ok=True) st.divider() if staged_vp: st.write(f"**VP staged**: {len(staged_vp)} rows, " f"{len({r['sku'] for r in staged_vp})} SKUs from " f"{len({r['kam'] for r in staged_vp})} KAM(s)") if staged_mp: st.write(f"**MP staged**: {len(staged_mp)} rows, " f"{len({r['sku'] for r in staged_mp})} SKUs from " f"{len({r['kam'] for r in staged_mp})} CM(s)") if (staged_vp or staged_mp) and st.button("πŸ’Ύ Save as base inputs", type="primary", key="kam_base_save"): new_cws = [f"CW{cw+1+j}" for j in range(FORECAST_WEEKS)] for rows, tp in [(staged_vp, "vp"), (staged_mp, "mp")]: if not rows: continue df = pd.DataFrame(rows) cw_cols = [c for c in df.columns if c.startswith("CW")] df.to_csv(DATA_DIR / f"{tp}_input_detail.csv", index=False) summed = df.groupby("sku")[cw_cols].sum().reset_index() for _cw in new_cws: if _cw not in summed.columns: summed[_cw] = 0 keep = ["sku"] + [c for c in sorted(summed.columns) if c.startswith("CW")] summed[keep].to_csv(DATA_DIR / f"{tp}_input.csv", index=False) st.success("Base saved. Switch to the Slack cycle tab to start distribution.") # ═════════════════════════════════════════════════════════════════ # SLACK CYCLE β€” distribute templates & collect responses via Slack # ═════════════════════════════════════════════════════════════════ with tab_slack: st.subheader("Slack distribution & collection") st.caption("Automate template distribution and response pickup via Slack.") # ---- Import agent (handle missing deps gracefully) ---- agent = None try: from slack_agent import SlackAgent agent = SlackAgent(data_dir=str(DATA_DIR)) except ImportError as e: st.error(f"Cannot import slack_agent: {e}") st.caption("Ensure `slack_agent.py` sits next to `app.py` and run `pip install slack_sdk`.") except Exception as e: st.error(f"Agent init failed: {e}") # ---- Config sanity check ---- cfg_path = DATA_DIR / "kam_cm_config.json" slack_cfg, has_token, has_channel = {}, False, False if cfg_path.exists(): with open(cfg_path, encoding="utf-8") as f: _cfg = json.load(f) slack_cfg = _cfg.get("slack_config", {}) has_token = bool(slack_cfg.get("bot_token") or os.environ.get("POLLEO_SLACK_TOKEN")) has_channel = bool(slack_cfg.get("channel_id")) c1, c2, c3 = st.columns(3) c1.metric("Slack token", "βœ… set" if has_token else "❌ missing") c2.metric("Channel", slack_cfg.get("channel_id", "β€”") if has_channel else "β€”") c3.metric("Deadline", f"{slack_cfg.get('deadline_day','Mon')} {slack_cfg.get('deadline_hour',17)}:00") if agent and has_token and has_channel: # ---- Current cycle status ---- st.divider() cycle_path = DATA_DIR / "slack_cycle.json" cycle = None if cycle_path.exists(): with open(cycle_path, encoding="utf-8") as f: cycle = json.load(f) started = (cycle.get("started_at") or "")[:16].replace("T", " ") st.markdown(f"**Current cycle:** `{cycle.get('cycle_id','β€”')}` Β· started {started}") subs = cycle.get("submissions", {}) n_pending = sum(1 for s in subs.values() if s["status"] == "pending") n_ok = sum(1 for s in subs.values() if s["status"] in ("validated", "submitted")) n_err = sum(1 for s in subs.values() if s["status"] == "error") k1, k2, k3, k4 = st.columns(4) k1.metric("Total", len(subs)) k2.metric("Submitted", n_ok) k3.metric("Pending", n_pending) k4.metric("Errors", n_err) icon_map = {"pending": "⏳", "validated": "βœ…", "submitted": "βœ…", "error": "⚠️"} rows = [{ "": icon_map.get(s["status"], "❓"), "Person": s["display_name"], "Role": s["role"], "Status": s["status"], "Submitted": (s.get("submitted_at") or "β€”")[:16].replace("T", " "), "SKUs": s.get("sku_count", 0), "Units": f"{s.get('total_units', 0):,}", } for s in subs.values()] st.dataframe(rows, use_container_width=True, hide_index=True) else: st.info("No active cycle. Click **Distribute templates** below to start one.") # ---- Action buttons ---- st.divider() st.markdown("**Actions**") col_a, col_b = st.columns(2) # ── LEFT: Distribute + Nudge ── with col_a: any_pending = cycle and any( s["status"] == "pending" for s in cycle.get("submissions", {}).values() ) confirm = True if any_pending: confirm = st.checkbox( "Cycle active β€” start a new one anyway (overwrites current)", key="slack_confirm_redo" ) if st.button("πŸ“€ Distribute templates", type="primary", use_container_width=True, disabled=not confirm, key="slack_btn_distribute"): with st.spinner("Generating templates and posting to Slack…"): r = agent.distribute_all(dry_run=False) if r.get("errors"): st.error(f"{len(r['errors'])} error(s): {'; '.join(r['errors'][:3])}") if r.get("posted"): st.success(f"Posted: {', '.join(r['posted'])}") st.rerun() if st.button("πŸ”” Send nudges to pending", use_container_width=True, key="slack_btn_nudge"): if not cycle: st.warning("No active cycle.") else: with st.spinner("Sending reminders…"): nudged = agent.send_nudges(dry_run=False) if nudged: st.success(f"Nudged: {', '.join(nudged)}") else: st.info("Nothing to nudge β€” everyone's already in.") # ── RIGHT: Collect + Combine (one button) ── with col_b: if st.button("πŸ“₯ Collect + combine responses", type="primary", use_container_width=True, key="slack_btn_collect_combine"): if not cycle: st.warning("No active cycle β€” nothing to collect.") else: # Step 1 β€” pull files from Slack thread with st.spinner("Scanning Slack thread and downloading files…"): r_col = agent.collect_responses(dry_run=False) if r_col.get("error"): st.error(f"Collect failed: {r_col['error']}") else: n_collected = len(r_col.get("collected", [])) if n_collected: st.success(f"Collected {n_collected} file(s) from Slack") else: st.info("No new files found in the Slack thread.") if r_col.get("errors"): st.warning(f"Collect issues: {'; '.join(r_col['errors'][:3])}") # Step 2 β€” combine into vp/mp_input.csv with st.spinner("Combining into vp/mp_input.csv…"): r_com = agent.combine_collected() if r_com: for role, stats in r_com.items(): st.success( f"{role}: {stats['skus']} SKUs from " f"{stats['kams']} source(s), " f"{stats['total_units']:,} units β†’ " f"{role.lower()}_input.csv" ) else: st.info("Nothing to combine yet (no validated files).") st.rerun() st.divider() st.caption( "πŸ’‘ **Weekly rhythm** β€” Friday: *Distribute* Β· Monday 09:00: *Nudge* Β· " "Monday 17:00: *Collect + combine* β†’ Run forecast" ) # ---- VIEW COMBINED ---- with tab_view: st.subheader("Current combined inputs") for tp, label in [("vp", "VP (wholesale)"), ("mp", "MP (marketing/retail)")]: csv_path = DATA_DIR / f"{tp}_input.csv" detail_path = DATA_DIR / f"{tp}_input_detail.csv" if csv_path.exists(): df = pd.read_csv(csv_path) cw_cols = [c for c in df.columns if c.startswith("CW")] total = int(df[cw_cols].sum().sum()) st.write(f"**{label}**: {len(df)} SKUs, {total:,} total units") if detail_path.exists(): detail = pd.read_csv(detail_path) kams = detail["kam"].unique().tolist() st.caption(f"From: {', '.join(kams)}") with st.expander(f"View {label} data"): st.dataframe(df, use_container_width=True, hide_index=True) else: st.caption(f"βšͺ {label}: no combined file yet") def _parse_legacy_format(filepath, source_name="Unknown"): """Parse the old 5-row-block VP/MP format. Returns (vp_rows, mp_rows).""" from openpyxl import load_workbook as lw wb = lw(filepath, data_only=True) vp_rows = [] mp_rows = [] for sheet_name in wb.sheetnames: if "VP" in sheet_name: target = vp_rows elif "MP" in sheet_name: target = mp_rows else: continue ws = wb[sheet_name] cw_map = {} for c in range(1, ws.max_column + 1): v = ws.cell(4, c).value if not v: continue v = str(v) if v.startswith("CW") and "/" not in v: cw_map[c] = v elif "/" in v and "CW" in v: cw_label = v.split("/")[-1].strip() if cw_label.startswith("CW"): cw_map[c] = cw_label current_sku = None current_name = "" current_cat = "" current_ozn = "" for r in range(5, ws.max_row + 1): col1 = ws.cell(r, 1).value if col1 is None: continue col1 = str(col1).strip() if col1 in ("on-top demand", "regular increase") and current_sku: row_data = {"sku": current_sku, "name": current_name, "cat": current_cat, "oznaka": current_ozn, "type": col1, "kam": source_name} has_data = False for col_idx, cw_label in cw_map.items(): v = ws.cell(r, col_idx).value if v is not None and isinstance(v, (int, float)) and v > 0: row_data[cw_label] = int(v) has_data = True else: row_data[cw_label] = 0 if has_data: target.append(row_data) elif col1 not in ("", "Filter Match", "MATCH") and \ any(c.isdigit() for c in col1) and not col1.startswith("CW"): current_sku = col1 current_name = str(ws.cell(r, 2).value or "") current_cat = str(ws.cell(r, 3).value or "") current_ozn = str(ws.cell(r, 4).value or "") wb.close() return vp_rows, mp_rows def page_download(): st.title("Download forecast") if OUTPUT_FILE.exists(): mod = datetime.fromtimestamp(OUTPUT_FILE.stat().st_mtime) st.success(f"Latest forecast β€” {mod.strftime('%Y-%m-%d %H:%M')}") with open(OUTPUT_FILE, "rb") as f: st.download_button( "πŸ“₯ Download Polleo_Demand_Plan.xlsx", data=f.read(), file_name="Polleo_Demand_Plan.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", type="primary", use_container_width=True ) with st.expander("Download data files"): for fname in REQUIRED_CSV: p = DATA_DIR / fname if p.exists(): st.download_button(fname, p.read_bytes(), fname, "text/csv", key=f"dl_{fname}") st.divider() st.caption("**Supply module bridge**") fsp = DATA_DIR / "forecast_for_supply.csv" colA, colB = st.columns([1, 1]) if colA.button("πŸ”„ Refresh forecast_for_supply.csv", use_container_width=True): src = write_forecast_for_supply() if src == "output_total": st.success("Refreshed from **Demand Output - Total** sheet (authoritative).") elif src == "reconstructed": st.success("Refreshed β€” reconstructed from Demand Planning blocks. " "Tip: open `Polleo_Demand_Plan.xlsx` in Excel and save it " "to cache the Demand Output - Total values for direct reads.") else: st.warning("No demand plan loaded yet.") if fsp.exists(): mod_fsp = datetime.fromtimestamp(fsp.stat().st_mtime).strftime("%Y-%m-%d %H:%M") colB.caption(f"βœ… Last refreshed: {mod_fsp}") colB.download_button("forecast_for_supply.csv", fsp.read_bytes(), "forecast_for_supply.csv", "text/csv", key="dl_fsp", use_container_width=True) else: colB.caption("βšͺ Not yet generated") else: st.warning("No forecast generated yet.") # ================================================================== # NEW PAGES: Accuracy, Exceptions, What-If, FVA, Bridge, Consensus, S&OP # ================================================================== # ================================================================== # FA HELPERS β€” shared by the three FA tabs # ================================================================== def _collect_vp_history(): """Walk all consensus snapshots and return a dict: (sku, year, week) -> vp_on_top_value Uses 'latest snapshot wins' semantics: for each (SKU, target week), the value from the most recently saved snapshot whose forecast horizon contained that week is kept β€” this represents the final VP commitment before that week actually happened. Only on-top demand rows with value > 0 are included. Snapshots saved before v3.6 (no vp_inputs field) are skipped. """ snaps = load_consensus_snapshots() # sorted newest-first by filename # Iterate oldest-first so newer values overwrite older ones snaps_oldest_first = list(reversed(snaps)) vp_hist = {} for snap in snaps_oldest_first: vp_inputs = snap.get("vp_inputs") or {} if not vp_inputs: continue snap_cw = int(snap.get("cw", 0) or 0) ts = snap.get("timestamp", "") try: snap_year = int(snap.get("snap_year") or (ts[:4] if ts else datetime.now().year)) except (TypeError, ValueError): snap_year = datetime.now().year for sku, per_cw in vp_inputs.items(): if not isinstance(per_cw, dict): continue for cw_label, vp_val in per_cw.items(): try: target_cw = int(str(cw_label).replace("CW", "")) v = float(vp_val) except (TypeError, ValueError): continue if v <= 0: continue # Year inference: if target cw < snap cw, we crossed year boundary target_year = snap_year if target_cw >= snap_cw else snap_year + 1 vp_hist[(sku, target_year, target_cw)] = v return vp_hist def _collect_mp_history(): """Same shape as _collect_vp_history but for MP on-top (retail).""" snaps = load_consensus_snapshots() snaps_oldest_first = list(reversed(snaps)) mp_hist = {} for snap in snaps_oldest_first: mp_inputs = snap.get("mp_inputs") or {} if not mp_inputs: continue snap_cw = int(snap.get("cw", 0) or 0) ts = snap.get("timestamp", "") try: snap_year = int(snap.get("snap_year") or (ts[:4] if ts else datetime.now().year)) except (TypeError, ValueError): snap_year = datetime.now().year for sku, per_cw in mp_inputs.items(): if not isinstance(per_cw, dict): continue for cw_label, mp_val in per_cw.items(): try: target_cw = int(str(cw_label).replace("CW", "")) v = float(mp_val) except (TypeError, ValueError): continue if v <= 0: continue target_year = snap_year if target_cw >= snap_cw else snap_year + 1 mp_hist[(sku, target_year, target_cw)] = v return mp_hist @st.cache_data(ttl=60) def _load_total_fa_data(_sc): """Build the 'total demand' forecast-vs-actual DataFrame from backtest_fa.csv and forecast_log.csv (qty_total on the actual side). Returns DataFrame with sku, year, week, forecast, actual (+ cat, oznaka) or None if no data. The underscore in `_sc` tells Streamlit's cache_data to skip hashing the DataFrame argument β€” upstream loaders already cache sc with the same TTL, and rebuilding FA data on every widget click was the main page-render lag. """ sc = _sc bt_path = DATA_DIR / "backtest_fa.csv" log_path = DATA_DIR / "forecast_log.csv" fa_data = None if bt_path.exists(): fa_data = pd.read_csv(bt_path) if log_path.exists() and sc is not None: log_df = pd.read_csv(log_path) if len(log_df) > 0: actuals = sc.groupby(["sku", "year", "week"])["qty_total"].sum().reset_index() log_merged = log_df.merge(actuals, left_on=["sku", "target_year", "target_week"], right_on=["sku", "year", "week"], how="inner") if len(log_merged) > 0: log_merged = log_merged.rename(columns={"qty_total": "actual"}) log_merged["year"] = log_merged["target_year"] log_merged["week"] = log_merged["target_week"] log_merged = log_merged[["sku", "year", "week", "forecast", "actual"]] plan_csv = DATA_DIR / "sku_plan_list.csv" if plan_csv.exists(): plan = pd.read_csv(plan_csv) log_merged = log_merged.merge(plan[["sku", "cat", "oznaka"]].drop_duplicates("sku"), on="sku", how="left") if fa_data is not None: existing_keys = set(zip(fa_data["sku"], fa_data["year"], fa_data["week"])) lm_keys = list(zip(log_merged["sku"], log_merged["year"], log_merged["week"])) keep_mask = np.array([k not in existing_keys for k in lm_keys]) log_new = log_merged[keep_mask] fa_data = pd.concat([fa_data, log_new], ignore_index=True) else: fa_data = log_merged if fa_data is None or len(fa_data) == 0: return None # Ensure cat/oznaka metadata plan_csv = DATA_DIR / "sku_plan_list.csv" if plan_csv.exists() and ("oznaka" not in fa_data.columns or fa_data["oznaka"].isna().all()): plan = pd.read_csv(plan_csv) fa_data["oznaka"] = fa_data["sku"].map(dict(zip(plan["sku"], plan["oznaka"]))).fillna("") fa_data["cat"] = fa_data["sku"].map(dict(zip(plan["sku"], plan["cat"]))).fillna("") return fa_data @st.cache_data(ttl=60) def _build_fa_dataset(mode, _sc): """Build a forecast-vs-actual dataset for one of three modes. mode: 'total' β€” total model forecast vs qty_total (all SKUs). 'kam_cm_projections' β€” on-top commitment vs actual sales in that planner's own channel. VP β†’ qty_wholesale (TRC/VPT/VPB/RAC). MP β†’ qty_retail (RCM). 'both' sums VP+MP and actuals from wholesale+retail. Carries an 'input_kind' column. 'model_only' β€” baseline forecast and qty_total with the planner's channel subtracted for SKU-weeks they committed on. VP on-top β†’ wholesale stripped from both sides. MP on-top β†’ retail stripped from both sides. Returns DataFrame with columns: sku, year, week, forecast, actual, cat, oznaka, error, variance, fa, hit, cw_label, month (+ input_kind for kam_cm_projections) or None if no overlapping data. """ sc = _sc # alias: `_sc` param only skips Streamlit cache-key hashing if mode == "total": fa = _load_total_fa_data(sc) if fa is None or len(fa) == 0: return None elif mode in ("kam_cm_projections", "model_only"): if sc is None: return None vp_hist = _collect_vp_history() mp_hist = _collect_mp_history() # Channel actuals keyed by (sku, year, week) ch = sc.groupby(["sku", "year", "week"]).agg( qty_retail=("qty_retail", "sum"), qty_wholesale=("qty_wholesale", "sum"), ).reset_index() ws_key = {(r["sku"], int(r["year"]), int(r["week"])): float(r["qty_wholesale"]) for _, r in ch.iterrows()} rt_key = {(r["sku"], int(r["year"]), int(r["week"])): float(r["qty_retail"]) for _, r in ch.iterrows()} if mode == "kam_cm_projections": if not vp_hist and not mp_hist: return None keys = set(vp_hist) | set(mp_hist) rows = [] for (sku, y, w) in keys: vp = float(vp_hist.get((sku, y, w), 0.0)) mp = float(mp_hist.get((sku, y, w), 0.0)) ws_actual = ws_key.get((sku, y, w), 0.0) rt_actual = rt_key.get((sku, y, w), 0.0) if vp > 0 and mp > 0: kind, fcst, act = "both", vp + mp, ws_actual + rt_actual elif vp > 0: kind, fcst, act = "vp_only", vp, ws_actual else: kind, fcst, act = "mp_only", mp, rt_actual rows.append({"sku": sku, "year": y, "week": w, "forecast": round(fcst), "actual": round(act), "input_kind": kind}) if not rows: return None fa = pd.DataFrame(rows) plan_csv = DATA_DIR / "sku_plan_list.csv" if plan_csv.exists(): plan = pd.read_csv(plan_csv) fa["oznaka"] = fa["sku"].map(dict(zip(plan["sku"], plan["oznaka"]))).fillna("") fa["cat"] = fa["sku"].map(dict(zip(plan["sku"], plan["cat"]))).fillna("") else: fa["oznaka"] = "" fa["cat"] = "" else: # model_only fa_total = _load_total_fa_data(sc) if fa_total is None or len(fa_total) == 0: return None fa = fa_total.copy() fa["_vp"] = fa.apply( lambda r: vp_hist.get((r["sku"], int(r["year"]), int(r["week"])), 0.0), axis=1) fa["_mp"] = fa.apply( lambda r: mp_hist.get((r["sku"], int(r["year"]), int(r["week"])), 0.0), axis=1) fa["_ws_actual"] = fa.apply( lambda r: ws_key.get((r["sku"], int(r["year"]), int(r["week"])), 0.0), axis=1) fa["_rt_actual"] = fa.apply( lambda r: rt_key.get((r["sku"], int(r["year"]), int(r["week"])), 0.0), axis=1) # Strip the planner-owned channel from both forecast and actual when # an on-top exists. Retail is stripped as (vp + rt_actual) capped so # we don't go negative on thin-volume weeks. fa["forecast"] = ( fa["forecast"] - fa["_vp"].where(fa["_vp"] > 0, 0) - fa["_mp"].where(fa["_mp"] > 0, 0) ).clip(lower=0).round().astype(int) fa["actual"] = ( fa["actual"] - fa["_ws_actual"].where(fa["_vp"] > 0, 0) - fa["_rt_actual"].where(fa["_mp"] > 0, 0) ).clip(lower=0).round().astype(int) fa = fa.drop(columns=["_vp", "_mp", "_ws_actual", "_rt_actual"]) else: return None # --- Common post-processing --- fa = fa[fa["actual"] > 0].copy() if len(fa) == 0: return None fa["error"] = np.abs(fa["forecast"] - fa["actual"]) fa["variance"] = fa["forecast"] - fa["actual"] fa["fa"] = np.maximum(0, 1 - fa["error"] / fa["actual"]) # Signed FA β€” ratio forecast / actual. >100% = over-forecast, <100% = under-forecast. fa["fa_signed"] = fa["forecast"] / fa["actual"] fa["hit"] = (fa["error"] / fa["actual"].clip(lower=1) <= 0.3).astype(int) fa["cw_label"] = "CW" + fa["week"].astype(str) # Vectorised month lookup β€” build once over the unique (year, week) pairs # and map back, avoiding a row-wise .apply (was the main slowdown on every # FA widget interaction). _yw_unique = fa[["year", "week"]].drop_duplicates() _yw_unique["month"] = [ _week_to_month_name(int(y), int(w)) for y, w in zip(_yw_unique["year"], _yw_unique["week"]) ] fa = fa.merge(_yw_unique, on=["year", "week"], how="left") return fa def _fa_xlsx_download(df, label, filename, key): """Render a Streamlit download button that emits `df` as a single-sheet xlsx.""" if df is None or len(df) == 0: return from io import BytesIO buf = BytesIO() with pd.ExcelWriter(buf, engine="openpyxl") as xw: df.to_excel(xw, index=False, sheet_name="FA") st.download_button( label, buf.getvalue(), filename, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", key=key, ) def _render_fa_tab(fa_data, caption, key_prefix): """Render the shared FA body (filters, weekly table, monthly table, top-errors drill-down) inside a single tab, keyed uniquely by key_prefix.""" if fa_data is None or len(fa_data) == 0: st.info("No overlapping forecast vs actual data for this view yet.") return st.caption(caption) # --- Filters (rendered first so metrics reflect the active view) --- c1, c2, c3 = st.columns([1.2, 2, 1.2]) ozn_opts = ["All"] + sorted(fa_data["oznaka"].dropna().astype(str).unique().tolist()) ozn_filter = c1.selectbox("Oznaka tier", ozn_opts, key=f"{key_prefix}_ozn_tier") df = fa_data.copy() if ozn_filter != "All": df = df[df["oznaka"] == ozn_filter] if len(df) == 0: st.info("No data for selected filter.") return tier_label = ozn_filter if ozn_filter != "All" else "ALL TIERS" available_weeks = sorted(df["week"].unique()) available_cws = [f"CW{w}" for w in available_weeks] selected_cws = c2.multiselect("Select Week(s)", available_cws, default=available_cws[-min(4, len(available_cws)):], key=f"{key_prefix}_week_sel") selected_week_nums = [int(c.replace("CW", "")) for c in selected_cws] wk_df_full = df[df["week"].isin(selected_week_nums)] excl_n = c3.number_input( "Exclude top-N SKUs", min_value=0, max_value=50, value=0, step=1, key=f"{key_prefix}_excl_n", help="Removes the worst-offender SKUs (by aggregated abs error across " "the selected weeks) from ALL metrics and tables below.", ) # Aggregate per-SKU error across selected weeks to identify worst offenders if len(wk_df_full) > 0: sku_err = (wk_df_full.groupby("sku", as_index=False) .agg(abs_error=("error", "sum"), actual=("actual", "sum"), forecast=("forecast", "sum")) .sort_values("abs_error", ascending=False)) excluded_skus = set(sku_err.head(int(excl_n))["sku"].tolist()) if excl_n > 0 else set() else: sku_err = None excluded_skus = set() wk_df = wk_df_full[~wk_df_full["sku"].isin(excluded_skus)] if excluded_skus else wk_df_full # --- Summary metrics (reflect tier + week + exclusion) --- # Weekly view uses PER-WEEK AVERAGE across selected weeks (director rule): # compute FA/FA signed/BIAS per week first, then mean across weeks. # Monthly view keeps totals-based (implemented further down). tot_fc = int(wk_df["forecast"].sum()) tot_ac = int(wk_df["actual"].sum()) tot_var = tot_fc - tot_ac tot_err = int(wk_df["error"].sum()) n_skus = wk_df["sku"].nunique() def _per_week_avg_metrics(d): """Return (fa_avg, fa_signed_avg, bias_avg, n_weeks_counted).""" if len(d) == 0: return 0.0, 0.0, 0.0, 0 wk_totals = d.groupby("week", as_index=False).agg( ac=("actual", "sum"), fc=("forecast", "sum")) wk_totals = wk_totals[wk_totals["ac"] > 0] if len(wk_totals) == 0: return 0.0, 0.0, 0.0, 0 fa_list = (1 - (wk_totals["fc"] - wk_totals["ac"]).abs() / wk_totals["ac"]).clip(lower=0) * 100 fa_signed_list = (wk_totals["fc"] / wk_totals["ac"]) * 100 bias_list = (wk_totals["fc"] - wk_totals["ac"]) / wk_totals["ac"] * 100 return float(fa_list.mean()), float(fa_signed_list.mean()), float(bias_list.mean()), len(wk_totals) fa_overall, fa_signed_overall, bias_overall, n_weeks_used = _per_week_avg_metrics(wk_df) m1, m2, m3, m4, m5 = st.columns(5) m1.metric("Actuals", f"{tot_ac:,}") m2.metric("Forecast", f"{tot_fc:,}", f"{tot_var:+,}") m3.metric("FA signed", f"{fa_signed_overall:.1f}%", help=f"Average of per-week FA signed across {n_weeks_used} week(s). " "Each week: forecast / actual. <100% = under-forecast. >100% = over-forecast.") m4.metric("BIAS", f"{bias_overall:+.1f}%", help="Average of per-week BIAS. Each week: (forecast βˆ’ actual) / actual.") m5.metric("SKUs", f"{n_skus}") st.caption(f"Weekly metrics = average across {n_weeks_used} selected week(s). " "Monthly (below) uses sum-then-divide.") if excluded_skus: fa_orig_avg, _, _, _ = _per_week_avg_metrics(wk_df_full) st.caption( f"Excluded {len(excluded_skus)} SKU(s): {', '.join(sorted(excluded_skus))} " f"Β· FA without exclusion would be **{fa_orig_avg:.1f}%** " f"(Ξ” {fa_overall - fa_orig_avg:+.1f} pp)" ) # ==== WEEKLY ==== st.subheader("Weekly accuracy") if len(wk_df) > 0: wk_table = _build_fa_table(wk_df, tier_label, agg_mode="per_week_avg") _render_fa_table(wk_table, key=f"{key_prefix}_wk") _fa_xlsx_download(wk_table, "⬇️ Download weekly table (xlsx)", f"FA_weekly_{key_prefix}.xlsx", key=f"{key_prefix}_dl_weekly") with st.expander("Top SKUs ruining FA (aggregated across selected weeks)"): if sku_err is not None and len(sku_err) > 0: meta_cols = ["cat", "oznaka"] meta = wk_df_full.drop_duplicates("sku").set_index("sku")[meta_cols] ranked = sku_err.head(15).copy() ranked = ranked.join(meta, on="sku") ranked["FA%"] = (np.maximum(0, 1 - (ranked["forecast"] - ranked["actual"]).abs() / ranked["actual"].clip(lower=1)) * 100).round(1) ranked["FA signed%"] = (ranked["forecast"] / ranked["actual"].clip(lower=1) * 100).round(1) ranked["Bias%"] = ((ranked["forecast"] - ranked["actual"]) / ranked["actual"].clip(lower=1) * 100).round(1) ranked["Share of total err"] = (ranked["abs_error"] / max(wk_df_full["error"].sum(), 1) * 100).round(1) ranked = ranked.rename(columns={ "sku": "SKU", "cat": "Category", "oznaka": "Tier", "actual": "Actual", "forecast": "Forecast", "abs_error": "Abs Error", })[["SKU", "Category", "Tier", "Actual", "Forecast", "Abs Error", "FA%", "FA signed%", "Bias%", "Share of total err"]] st.dataframe(ranked, use_container_width=True, hide_index=True) _fa_xlsx_download(ranked, "⬇️ Download top errors (xlsx)", f"FA_top_errors_{key_prefix}.xlsx", key=f"{key_prefix}_dl_errors") else: st.info("No data for selected weeks") # ==== MONTHLY ==== st.divider() st.subheader("Monthly accuracy (S&OP)") available_months = sorted(df["month"].unique(), key=lambda m: _MONTH_ORDER.get(m, 99)) if available_months: c1m, c2m = st.columns([2, 2]) sel_month = c1m.selectbox("Select Month", available_months, index=len(available_months)-1, key=f"{key_prefix}_month_sel", help="Choose month for S&OP review") excl_n_mo = c2m.number_input( "Exclude top-N SKUs (monthly)", min_value=0, max_value=50, value=0, step=1, key=f"{key_prefix}_excl_n_mo", help="Removes the worst-offender SKUs (by aggregated abs error in " "the selected month) from monthly metrics and table.", ) mo_df_full = df[df["month"] == sel_month] if len(mo_df_full) > 0: # Show which ISO weeks map to this month (Monday-based mapping) so # the user can see the actual date range covered and spot partials. weeks_in_month = (mo_df_full[["year", "week"]] .drop_duplicates() .sort_values(["year", "week"])) today = datetime.now().date() parts, leaks, incomplete = [], False, False for _, r in weeks_in_month.iterrows(): y, w = int(r["year"]), int(r["week"]) try: mon = datetime.strptime(f"{y}-W{w:02d}-1", "%G-W%V-%u").date() sun = datetime.strptime(f"{y}-W{w:02d}-7", "%G-W%V-%u").date() except Exception: continue tag = f"W{w} ({mon.day}.{mon.month}.–{sun.day}.{sun.month}.)" if sun.month != mon.month: tag += "*" leaks = True if sun >= today: tag += "†" incomplete = True parts.append(tag) if parts: caption = f"**{sel_month}** = " + " + ".join(parts) notes = [] if leaks: notes.append("\\* tjedan čiji dani prelaze u sljedeΔ‡i mjesec β€” po ISO konvenciji cijeli pripada ovom mjesecu") if incomplete: notes.append("† tjedan joΕ‘ nije gotov β€” actuali mogu biti parcijalni") if notes: caption += " \n" + " \n".join(notes) st.markdown(caption) sku_err_mo = (mo_df_full.groupby("sku", as_index=False) .agg(abs_error=("error", "sum"), actual=("actual", "sum"), forecast=("forecast", "sum")) .sort_values("abs_error", ascending=False)) excluded_mo = (set(sku_err_mo.head(int(excl_n_mo))["sku"].tolist()) if excl_n_mo > 0 else set()) mo_df = mo_df_full[~mo_df_full["sku"].isin(excluded_mo)] if excluded_mo else mo_df_full # Monthly summary metrics (mirror weekly block) mo_fc = int(mo_df["forecast"].sum()) mo_ac = int(mo_df["actual"].sum()) mo_var = mo_fc - mo_ac mo_err = int(mo_df["error"].sum()) # FA derived from totals (consistent with FA signed): |bias|/actual. fa_mo = max(0, (1 - abs(mo_var) / mo_ac) * 100) if mo_ac > 0 else 0 fa_signed_mo = (mo_fc / mo_ac * 100) if mo_ac > 0 else 0 bias_mo = (mo_var / mo_ac * 100) if mo_ac > 0 else 0 n_skus_mo = mo_df["sku"].nunique() mm1, mm2, mm3, mm4, mm5 = st.columns(5) mm1.metric("Actuals", f"{mo_ac:,}") mm2.metric("Forecast", f"{mo_fc:,}", f"{mo_var:+,}") mm3.metric("FA signed", f"{fa_signed_mo:.1f}%", help="forecast / actual. <100% = under-forecast (sold more than planned). " ">100% = over-forecast. 100% = perfect.") mm4.metric("BIAS", f"{bias_mo:+.1f}%") mm5.metric("SKUs", f"{n_skus_mo}") if excluded_mo: ta_orig = mo_df_full["actual"].sum() tf_orig = mo_df_full["forecast"].sum() fa_orig = max(0, (1 - abs(tf_orig - ta_orig) / ta_orig) * 100) if ta_orig > 0 else 0 st.caption( f"Excluded {len(excluded_mo)} SKU(s): {', '.join(sorted(excluded_mo))} " f"Β· FA without exclusion would be **{fa_orig:.1f}%** " f"(Ξ” {fa_mo - fa_orig:+.1f} pp)" ) mo_table = _build_fa_table(mo_df, tier_label) _render_fa_table(mo_table, key=f"{key_prefix}_mo_{sel_month}") _fa_xlsx_download(mo_table, "⬇️ Download monthly table (xlsx)", f"FA_monthly_{key_prefix}_{sel_month}.xlsx", key=f"{key_prefix}_dl_monthly") with st.expander(f"Top SKUs ruining FA in {sel_month}"): if len(sku_err_mo) > 0: meta_cols = ["cat", "oznaka"] meta = mo_df_full.drop_duplicates("sku").set_index("sku")[meta_cols] ranked = sku_err_mo.head(15).copy() ranked = ranked.join(meta, on="sku") ranked["FA%"] = (np.maximum(0, 1 - (ranked["forecast"] - ranked["actual"]).abs() / ranked["actual"].clip(lower=1)) * 100).round(1) ranked["FA signed%"] = (ranked["forecast"] / ranked["actual"].clip(lower=1) * 100).round(1) ranked["Bias%"] = ((ranked["forecast"] - ranked["actual"]) / ranked["actual"].clip(lower=1) * 100).round(1) ranked["Share of total err"] = (ranked["abs_error"] / max(mo_df_full["error"].sum(), 1) * 100).round(1) ranked = ranked.rename(columns={ "sku": "SKU", "cat": "Category", "oznaka": "Tier", "actual": "Actual", "forecast": "Forecast", "abs_error": "Abs Error", })[["SKU", "Category", "Tier", "Actual", "Forecast", "Abs Error", "FA%", "FA signed%", "Bias%", "Share of total err"]] st.dataframe(ranked, use_container_width=True, hide_index=True) _fa_xlsx_download(ranked, "⬇️ Download top errors (xlsx)", f"FA_top_errors_monthly_{key_prefix}_{sel_month}.xlsx", key=f"{key_prefix}_dl_errors_mo") else: st.info(f"No data for {sel_month}") def _render_sku_drilldown(sc, fa_total): """Per-SKU root-cause analysis β€” shows stat baseline, VP on-top, total forecast vs total / wholesale / retail actuals, plus attribution per week. """ st.subheader("πŸ” Per-SKU drill-down β€” root-cause analysis") st.caption( "Pick a SKU to see exactly where the forecast missed β€” " "was it the stat baseline (driving retail) or the VP commitment " "(driving wholesale)?" ) if fa_total is None or sc is None or len(fa_total) == 0: st.info("No backtest data available β€” run a backtest first.") return # ---- Aggregate per-SKU to sort by total absolute error ---- agg = fa_total.copy() if "error" not in agg.columns: agg["error"] = np.abs(agg["forecast"] - agg["actual"]) sku_agg = (agg.groupby("sku") .agg(total_err=("error", "sum"), total_actual=("actual", "sum"), n_weeks=("week", "count")) .reset_index() .sort_values("total_err", ascending=False)) # ---- Build display labels with name + cumulative error ---- name_map, cat_map, ozn_map = {}, {}, {} plan_csv = DATA_DIR / "sku_plan_list.csv" if plan_csv.exists(): plan = pd.read_csv(plan_csv) name_map = dict(zip(plan["sku"], plan["name"])) cat_map = dict(zip(plan["sku"], plan["cat"])) ozn_map = dict(zip(plan["sku"], plan["oznaka"])) options = [] option_to_sku = {} for _, row in sku_agg.iterrows(): code = row["sku"] name = (name_map.get(code, "") or "")[:45] label = f"{code} Β· {name} Β· abs.err Ξ£={int(row['total_err']):,} ({int(row['n_weeks'])}w)" options.append(label) option_to_sku[label] = code sel = st.selectbox( "Select SKU (sorted by total absolute error β€” most problematic first)", [""] + options, key="drill_sku_sel", ) if not sel: return sku = option_to_sku[sel] # ---- Build per-week breakdown ---- sku_fa = agg[agg["sku"] == sku].copy() if len(sku_fa) == 0: st.info("No weekly data for this SKU.") return # Merge channel-split actuals from sales_clean.csv sc_sku = sc[sc["sku"] == sku] sc_wk = (sc_sku.groupby(["year", "week"]) .agg(qty_wholesale=("qty_wholesale", "sum"), qty_retail=("qty_retail", "sum"), qty_webshop=("qty_webshop", "sum")) .reset_index()) sku_fa = sku_fa.merge(sc_wk, on=["year", "week"], how="left").fillna(0) # VP on-top history for this SKU vp_hist = _collect_vp_history() sku_fa["vp_on_top"] = sku_fa.apply( lambda r: vp_hist.get((r["sku"], int(r["year"]), int(r["week"])), 0.0), axis=1, ) # v3.6: if the new channel-split backtest columns are available, use them # as the real stat_retail / stat_wholesale baselines. Otherwise fall back # to "forecast" as stat_baseline (old behavior). has_channel_split = ( "forecast_retail" in sku_fa.columns and "forecast_wholesale" in sku_fa.columns ) if has_channel_split: # Real per-channel stat forecasts from the new backtest sku_fa["stat_retail"] = sku_fa["forecast_retail"].astype(int) sku_fa["stat_wholesale"] = sku_fa["forecast_wholesale"].astype(int) # Stat baseline for the old-style columns: total stat = retail + wholesale # (OR the original "forecast" if channel_mode was 'total') sku_fa["stat_baseline_total"] = ( sku_fa["stat_retail"] + sku_fa["stat_wholesale"] ) # Build "Total forecast" = stat_retail + (stat_wholesale + VP on-top) # For split-mode SKUs this is the correct forecast; for total-mode # SKUs we keep using the raw 'forecast' as baseline. ch_mode_col = sku_fa.get("channel_mode", pd.Series(["total"]*len(sku_fa))) is_split = (ch_mode_col == "split") sku_fa["stat_baseline"] = np.where( is_split, sku_fa["stat_baseline_total"], sku_fa["forecast"] ).astype(int) else: # Legacy: approximate stat_retail as baseline, stat_wholesale as 0 sku_fa["stat_retail"] = 0 sku_fa["stat_wholesale"] = 0 sku_fa["stat_baseline"] = sku_fa["forecast"].astype(int) sku_fa["vp_on_top"] = sku_fa["vp_on_top"].astype(int) sku_fa["total_forecast"] = sku_fa["stat_baseline"] + sku_fa["vp_on_top"] sku_fa["actual_total"] = sku_fa["actual"].astype(int) sku_fa["qty_wholesale"] = sku_fa["qty_wholesale"].astype(int) sku_fa["retail_actual"] = (sku_fa["qty_retail"] + sku_fa["qty_webshop"]).astype(int) # Channel-specific errors if has_channel_split: # Real per-channel comparison sku_fa["stat_err"] = sku_fa["stat_retail"] - sku_fa["retail_actual"] sku_fa["vp_err"] = ( (sku_fa["stat_wholesale"] + sku_fa["vp_on_top"]) - sku_fa["qty_wholesale"] ) else: # Legacy: stat_err is stat_baseline vs retail (imperfect but best available) sku_fa["stat_err"] = sku_fa["stat_baseline"] - sku_fa["retail_actual"] sku_fa["vp_err"] = sku_fa["vp_on_top"] - sku_fa["qty_wholesale"] sku_fa["total_err_signed"] = sku_fa["total_forecast"] - sku_fa["actual_total"] # Attribution def _attribute(row): se = abs(row["stat_err"]) ve = abs(row["vp_err"]) total = max(row["actual_total"], 1) # "Spot on" = combined residual < 5% of actual if (se + ve) < 0.05 * total: return "βœ“ Spot on" # No VP commitment and no wholesale activity β†’ stat is solely responsible if row["vp_on_top"] == 0 and row["qty_wholesale"] == 0: return "Stat over" if row["stat_err"] > 0 else "Stat under" # Otherwise pick whichever channel missed more (1.5Γ— margin for clarity) if se > ve * 1.5: return "Stat over" if row["stat_err"] > 0 else "Stat under" if ve > se * 1.5: return "VP over" if row["vp_err"] > 0 else "VP under" return "Mixed" sku_fa["attribution"] = sku_fa.apply(_attribute, axis=1) sku_fa = sku_fa.sort_values(["year", "week"]) # ---- Header ---- sku_name = name_map.get(sku, "") cat = cat_map.get(sku, "") ozn = ozn_map.get(sku, "") st.markdown(f"### {sku}" + (f" β€” {sku_name}" if sku_name else "")) st.caption(f"{cat} Β· {ozn} Β· {len(sku_fa)} weeks with data") # ---- Summary metrics ---- tot_fc = int(sku_fa["total_forecast"].sum()) tot_ac = int(sku_fa["actual_total"].sum()) tot_err = int(sku_fa["total_err_signed"].abs().sum()) # FA from totals (consistent with FA signed): |bias|/actual. fa_pct = max(0, (1 - abs(tot_fc - tot_ac) / tot_ac) * 100) if tot_ac > 0 else 0 bias_pct = ((tot_fc - tot_ac) / tot_ac * 100) if tot_ac > 0 else 0 # Channel mix across all history for this SKU (not just the weeks # shown in the drill-down) β€” gives the user context for interpretation sc_sku_all = sc[sc["sku"] == sku] tot_r = float(sc_sku_all["qty_retail"].sum()) tot_w = float(sc_sku_all["qty_webshop"].sum()) tot_ws = float(sc_sku_all["qty_wholesale"].sum()) tot_ch = tot_r + tot_w + tot_ws ws_share = (tot_ws / tot_ch * 100) if tot_ch > 0 else 0 retail_share = (tot_r / tot_ch * 100) if tot_ch > 0 else 0 web_share = (tot_w / tot_ch * 100) if tot_ch > 0 else 0 # Classify SKU by dominance if ws_share >= 80: mix_tag = "πŸͺ Wholesale-dominant" elif ws_share >= 50: mix_tag = "πŸͺ Wholesale-leaning" elif ws_share >= 20: mix_tag = "βš–οΈ Mixed channels" else: mix_tag = "πŸ›οΈ Retail-dominant" # Pull XYZ classification from sku_plan_list.csv (written by compute_xyz.py) xyz_tag = "" plan_csv = DATA_DIR / "sku_plan_list.csv" if plan_csv.exists(): pl = pd.read_csv(plan_csv) pl_row = pl[pl["sku"] == sku] if len(pl_row) and "ws_xyz" in pl.columns: ws_class = str(pl_row["ws_xyz"].iloc[0]) ws_cv_val = pl_row.get("ws_cv", pd.Series([None])).iloc[0] xyz_label = {"X": "stable wholesale (X)", "Y": "variable wholesale (Y)", "Z": "lumpy wholesale (Z)", "-": "no wholesale history"}.get(ws_class, ws_class) cv_str = f" Β· CV={ws_cv_val:.2f}" if pd.notna(ws_cv_val) else "" xyz_tag = f" Β· {xyz_label}{cv_str}" st.markdown( f"**{mix_tag}**{xyz_tag} β€” historical channel mix: " f"**{ws_share:.0f}%** wholesale Β· " f"**{retail_share:.0f}%** retail Β· " f"**{web_share:.0f}%** webshop" ) m1, m2, m3, m4 = st.columns(4) m1.metric("Total forecast", f"{tot_fc:,}", f"{tot_fc - tot_ac:+,}") m2.metric("Total actual", f"{tot_ac:,}") m3.metric("FA", f"{fa_pct:.1f}%") m4.metric("Bias", f"{bias_pct:+.1f}%") # ---- Chart: stacked forecast bars + actual lines ---- labels = [f"CW{int(w)}" for w in sku_fa["week"]] fig = go.Figure() if has_channel_split and (sku_fa.get("channel_mode", pd.Series()).eq("split")).any(): # 3-component stacked bar: stat_retail, stat_wholesale, VP on-top fig.add_trace(go.Bar( x=labels, y=sku_fa["stat_retail"], name="Stat retail", marker_color="#C5D9F1", text=[f"{int(v):,}" if v > 0 else "" for v in sku_fa["stat_retail"]], textposition="inside", textfont=dict(size=9), )) fig.add_trace(go.Bar( x=labels, y=sku_fa["stat_wholesale"], name="Stat wholesale", marker_color="#9AC3DE", text=[f"{int(v):,}" if v > 0 else "" for v in sku_fa["stat_wholesale"]], textposition="inside", textfont=dict(size=9), )) fig.add_trace(go.Bar( x=labels, y=sku_fa["vp_on_top"], name="VP on-top", marker_color="#2F5496", text=[f"{int(v):,}" if v > 0 else "" for v in sku_fa["vp_on_top"]], textposition="inside", textfont=dict(size=9, color="white"), )) else: # Legacy 2-component stack fig.add_trace(go.Bar( x=labels, y=sku_fa["stat_baseline"], name="Stat baseline", marker_color="#9AC3DE", text=[f"{int(v):,}" if v > 0 else "" for v in sku_fa["stat_baseline"]], textposition="inside", textfont=dict(size=9), )) fig.add_trace(go.Bar( x=labels, y=sku_fa["vp_on_top"], name="VP on-top", marker_color="#2F5496", text=[f"{int(v):,}" if v > 0 else "" for v in sku_fa["vp_on_top"]], textposition="inside", textfont=dict(size=9, color="white"), )) fig.add_trace(go.Scatter( x=labels, y=sku_fa["actual_total"], name="Total actual", mode="lines+markers+text", line=dict(color="#548235", width=3), marker=dict(size=8), text=[f"{int(v):,}" for v in sku_fa["actual_total"]], textposition="top center", textfont=dict(size=10, color="#548235"), )) fig.add_trace(go.Scatter( x=labels, y=sku_fa["qty_wholesale"], name="Wholesale actual", mode="lines+markers", line=dict(color="#C00000", width=2, dash="dash"), marker=dict(size=6), )) fig.update_layout( barmode="stack", height=440, margin=dict(l=40, r=20, t=20, b=40), legend=dict(orientation="h", y=1.08, x=0), yaxis=dict(tickformat=",", title="Quantity", gridcolor="rgba(200,200,200,0.3)"), xaxis=dict(title="", tickangle=-30), hovermode="x unified", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", ) st.plotly_chart(fig, use_container_width=True) # ---- Detail table ---- if has_channel_split and (sku_fa.get("channel_mode", pd.Series()).eq("split")).any(): # Richer table: stat_retail and stat_wholesale separately (only # meaningful for split-mode SKUs) tbl = pd.DataFrame({ "Week": labels, "Stat retail": sku_fa["stat_retail"].values, "Stat wholesale": sku_fa["stat_wholesale"].values, "VP on-top": sku_fa["vp_on_top"].values, "Total forecast": sku_fa["total_forecast"].values, "Total actual": sku_fa["actual_total"].values, "Wholesale actual": sku_fa["qty_wholesale"].values, "Retail+web actual": sku_fa["retail_actual"].values, "Stat err (retail)": sku_fa["stat_err"].astype(int).values, "VP+stat_ws err (ws)": sku_fa["vp_err"].astype(int).values, "Attribution": sku_fa["attribution"].values, }) fmt_numeric_cols = [ "Stat retail", "Stat wholesale", "VP on-top", "Total forecast", "Total actual", "Wholesale actual", "Retail+web actual", ] err_cols = ["Stat err (retail)", "VP+stat_ws err (ws)"] else: # Legacy: collapsed stat baseline only tbl = pd.DataFrame({ "Week": labels, "Stat baseline": sku_fa["stat_baseline"].values, "VP on-top": sku_fa["vp_on_top"].values, "Total forecast": sku_fa["total_forecast"].values, "Total actual": sku_fa["actual_total"].values, "Wholesale actual": sku_fa["qty_wholesale"].values, "Retail+web actual": sku_fa["retail_actual"].values, "Stat err (vs retail)": sku_fa["stat_err"].astype(int).values, "VP err (vs wholesale)": sku_fa["vp_err"].astype(int).values, "Attribution": sku_fa["attribution"].values, }) fmt_numeric_cols = [ "Stat baseline", "VP on-top", "Total forecast", "Total actual", "Wholesale actual", "Retail+web actual", ] err_cols = ["Stat err (vs retail)", "VP err (vs wholesale)"] def _color_attr(v): if "Spot on" in v: return "color: #006100; font-weight: bold" if v.startswith("Stat"): return "color: #9C6500; font-weight: bold" if v.startswith("VP"): return "color: #C00000; font-weight: bold" if v == "Mixed": return "color: #666666" return "" def _color_err(v): if v > 0: return "color: #006100" if v < 0: return "color: #C00000" return "" fmt_map = {c: "{:,.0f}" for c in fmt_numeric_cols} fmt_map.update({c: "{:+,.0f}" for c in err_cols}) styled = (tbl.style .applymap(_color_attr, subset=["Attribution"]) .applymap(_color_err, subset=err_cols) .format(fmt_map) .hide(axis="index")) st.dataframe(styled, use_container_width=True, hide_index=True) # ---- Narrative ---- with st.expander("πŸ“– Per-week narrative β€” plain English"): st.caption( "Math note: total_err = stat_err + vp_err, where " "stat_err = stat baseline βˆ’ (retail+web actual) and " "vp_err = VP on-top βˆ’ wholesale actual. " "The stat baseline was trained on qty_total, so when VP is zero it " "represents the whole-SKU prediction; when VP is present it " "represents the 'no-surge' portion of demand." ) for _, r in sku_fa.iterrows(): w = int(r["week"]) sb = int(r["stat_baseline"]) vp = int(r["vp_on_top"]) tf = int(r["total_forecast"]) act = int(r["actual_total"]) ws = int(r["qty_wholesale"]) rt = int(r["retail_actual"]) fc_desc = (f"stat baseline **{sb:,}** + VP on-top **{vp:,}** " f"= total **{tf:,}**" if vp > 0 else f"stat baseline **{sb:,}** (no VP)") act_desc = f"total **{act:,}** ({ws:,} wholesale + {rt:,} retail/web)" attr = r["attribution"] if "Spot on" in attr: verdict = "β†’ **βœ“ Spot on.**" elif attr == "Stat under": verdict = (f"β†’ **Stat baseline was lower than retail+web** " f"(baseline {sb:,} vs retail+web {rt:,}, off by " f"{abs(int(r['stat_err'])):,}).") elif attr == "Stat over": verdict = (f"β†’ **Stat baseline was higher than retail+web** " f"(baseline {sb:,} vs retail+web {rt:,}, off by " f"{abs(int(r['stat_err'])):,}).") elif attr == "VP under": if vp == 0: verdict = (f"β†’ **No VP commitment despite {ws:,} in " f"actual wholesale activity** β€” KAMs missed this.") else: verdict = (f"β†’ **VP under-committed wholesale** β€” " f"committed {vp:,} vs actual wholesale {ws:,}.") elif attr == "VP over": verdict = (f"β†’ **VP over-committed wholesale** β€” " f"committed {vp:,} vs actual wholesale {ws:,}.") else: # Mixed verdict = (f"β†’ **Both sides contributed** β€” stat off by " f"{int(r['stat_err']):+,} on retail, " f"VP off by {int(r['vp_err']):+,} on wholesale.") st.markdown(f"- **CW{w}** β€” Forecast: {fc_desc}. Actual: " f"{act_desc}. {verdict}") def _bootstrap_historical_vp_snapshot(): """UI to import filled KAM VP templates from a past week as a synthetic consensus snapshot. Reuses the same file parser as the regular combine flow so formats stay consistent. Writes to CONSENSUS_DIR / snapshot_bootstrap_{year}w{cw}_{ts}.json so it sorts alongside normal snapshots and the FA builder picks it up automatically. Does NOT touch vp_input.csv (that's for current/future weeks only). """ with st.expander("πŸ“₯ Bootstrap VP history from past KAM templates " "(use this to import week-14 or earlier)"): st.caption( "Upload KAM-filled VP templates from a PAST cycle. Each upload " "creates one synthetic snapshot representing what VP committed " "to for that cycle's forecast horizon. Repeat for each past " "week you have data for (e.g. run it once for CW13's commitments, " "once for CW12's, etc.). VP history accumulates automatically." ) c1, c2, c3 = st.columns([1, 1, 3]) snap_cw = c1.number_input( "Snapshot CW", min_value=1, max_value=53, value=13, key="boot_snap_cw", help="The CW when this forecast would have been run. " "If the templates have CW14 as the first column, " "the snapshot CW is 13 (forecast runs against CW+1 onwards)." ) snap_year = c2.number_input( "Year", min_value=2020, max_value=2035, value=datetime.now().year, key="boot_snap_year" ) uploaded = c3.file_uploader( "Upload filled VP KAM templates (.xlsx) for this past cycle", type=["xlsx"], accept_multiple_files=True, key="boot_vp_upload", ) if not uploaded: return from openpyxl import load_workbook as lw all_rows = [] parse_errors = [] for uf in uploaded: try: tmp_path = DATA_DIR / f"_tmp_bootstrap_{uf.name}" tmp_path.write_bytes(uf.getvalue()) wb = lw(tmp_path, data_only=True) kam_name = "Unknown" if "_meta" in wb.sheetnames: ws_m = wb["_meta"] kam_name = ws_m.cell(1, 2).value or uf.name ws = wb.worksheets[0] file_cws = {} for c in range(6, 30): v = ws.cell(4, c).value if v and str(v).startswith("CW"): file_cws[str(v)] = c elif v is None: break row_count = 0 for r in range(5, ws.max_row + 1): sku = ws.cell(r, 1).value if not sku: continue rtype = ws.cell(r, 5).value or "" row_data = {"sku": sku, "type": rtype, "kam": kam_name} has_data = False for cw_label, col in file_cws.items(): v = ws.cell(r, col).value row_data[cw_label] = float(v) if v and v != "" else 0.0 if v and float(v) > 0: has_data = True if has_data: all_rows.append(row_data) row_count += 1 wb.close() tmp_path.unlink(missing_ok=True) st.caption(f"βœ… {uf.name} β€” {kam_name}: {row_count} rows with data") except Exception as e: parse_errors.append((uf.name, str(e))) st.warning(f"⚠️ {uf.name}: {e}") if not all_rows: if parse_errors: st.error("No data could be parsed from the uploaded files.") return combined = pd.DataFrame(all_rows) cw_cols = [c for c in combined.columns if str(c).startswith("CW")] # Sum on-top + regular per SKU per CW (same as the regular # combine flow produces in vp_input.csv) per_sku = combined.groupby("sku")[cw_cols].sum().reset_index() # Build the vp_inputs dict shape that snapshots store: # {sku: {CW_label: value}} β€” only positive values vp_inputs = {} for _, row in per_sku.iterrows(): sku = row["sku"] per_cw = {} for c in cw_cols: v = float(row.get(c, 0) or 0) if v > 0: per_cw[c] = v if per_cw: vp_inputs[sku] = per_cw total_units = int(sum(sum(v.values()) for v in vp_inputs.values())) st.divider() st.write( f"**Preview β€” {len(vp_inputs)} SKUs with commitments Β· " f"{total_units:,} total VP units across {len(cw_cols)} weeks**" ) st.caption(f"Snapshot will be labeled: " f"CW{int(snap_cw)}/{int(snap_year)} bootstrap from " f"{len(uploaded)} file(s) Β· " f"KAM(s): {', '.join(sorted(combined['kam'].unique()))}") preview_rows = [] for sku, per_cw in list(vp_inputs.items())[:15]: preview_rows.append({"SKU": sku, **{c: int(per_cw.get(c, 0)) for c in cw_cols}}) st.dataframe(pd.DataFrame(preview_rows), use_container_width=True, hide_index=True) if len(vp_inputs) > 15: st.caption(f"... and {len(vp_inputs) - 15} more SKUs") # Check for conflict with existing snapshots existing_boot = list(CONSENSUS_DIR.glob( f"snapshot_bootstrap_{int(snap_year)}w{int(snap_cw):02d}_*.json" )) if existing_boot: st.warning( f"⚠️ A bootstrap snapshot for CW{int(snap_cw)}/{int(snap_year)} " f"already exists ({len(existing_boot)} file(s)). Saving again " "will create a second one β€” the latest will win in FA. " "Delete old ones via the file system if needed." ) if st.button("πŸ’Ύ Save as synthetic snapshot", type="primary", key="boot_save_snap"): ts = datetime.now().strftime("%Y%m%d_%H%M%S") # Timestamp string uses the snap's declared year β€” so # _collect_vp_history's year inference works correctly timestamp_display = f"{int(snap_year)}-01-01 00:00 (bootstrap)" snap = { "label": f"CW{int(snap_cw)}/{int(snap_year)} bootstrap ({len(uploaded)} files)", "timestamp": timestamp_display, "snap_year": int(snap_year), "cw": int(snap_cw), "n_skus": len(vp_inputs), "cws": cw_cols, "total_rev": 0, "rows": [], # no forecast rows β€” this is VP-only "vp_inputs": vp_inputs, "mp_inputs": {}, # MP not bootstrapped here "_bootstrap": True, # marker flag } filename = f"snapshot_bootstrap_{int(snap_year)}w{int(snap_cw):02d}_{ts}.json" (CONSENSUS_DIR / filename).write_text(json.dumps(snap)) load_consensus_snapshots.clear() st.success( f"βœ… Saved {filename} β€” {len(vp_inputs)} SKUs, " f"{total_units:,} units. Refresh the page to see " "wholesale accuracy populate." ) @st.cache_data(ttl=60) def _load_factor_history_map(): """Return {(sku, target_year, target_week): factor} from factor_history.csv. When multiple runs target the same week, keep the LATEST recorded factor (sorted by run_date) β€” that is the value the planner committed to most recently before the week closed. """ path = DATA_DIR / "factor_history.csv" if not path.exists(): return {} try: df = pd.read_csv(path) except Exception: return {} if len(df) == 0: return {} df = df.sort_values("run_date") df = df.drop_duplicates(subset=["sku", "target_year", "target_week"], keep="last") return { (r["sku"], int(r["target_year"]), int(r["target_week"])): float(r["factor"]) for _, r in df.iterrows() } def _apply_factor_to_fa(fa_df, factor_map): """Return a copy of fa_df with forecast multiplied by planner factor and derived metrics (error, fa, fa_signed, hit, variance) recomputed. Rows without a recorded factor get 1.00 (i.e., unchanged).""" if fa_df is None or len(fa_df) == 0 or not factor_map: return fa_df fa = fa_df.copy() keys = list(zip(fa["sku"], fa["year"].astype(int), fa["week"].astype(int))) factors = np.array([factor_map.get(k, 1.0) for k in keys], dtype=float) fa["forecast"] = (fa["forecast"].astype(float) * factors).round().astype(int) fa["error"] = np.abs(fa["forecast"] - fa["actual"]) fa["variance"] = fa["forecast"] - fa["actual"] fa["fa"] = np.maximum(0, 1 - fa["error"] / fa["actual"].clip(lower=1)) fa["fa_signed"] = fa["forecast"] / fa["actual"].clip(lower=1) fa["hit"] = (fa["error"] / fa["actual"].clip(lower=1) <= 0.3).astype(int) return fa def page_forecast_accuracy(): st.title("Forecast Accuracy Dashboard") cy, cw = get_current_cw() sc = load_sales_data() # Build all three datasets up front (cheap β€” in-memory) fa_total = _build_fa_dataset("total", sc) fa_kam = _build_fa_dataset("kam_cm_projections", sc) fa_model_only = _build_fa_dataset("model_only", sc) # If nothing at all is available, show the bootstrap prompt (same as before) if fa_total is None and fa_kam is None and fa_model_only is None: st.warning("No forecast accuracy data available yet.") st.info("Run a backtest to generate accuracy data for past weeks.") if st.button("Run walk-forward backtest (last 8 weeks)", type="primary"): _run_backtest_from_app() return # ---- Planner Factor toggle ---- factor_map = _load_factor_history_map() factor_count = len(factor_map) pf_col, pf_info = st.columns([1, 4]) apply_factor = pf_col.toggle( "Apply Planner Factor", value=False, key="fa_apply_factor", help="Multiply forecast by the planner factor that was set in the " "Demand Plan at run time. Toggle to compare 'model only' vs " "'model + planner adjustment'.", ) if factor_count == 0: pf_info.caption("⚠️ No planner factor history yet. Run a forecast (factors " "from the saved Demand Plan are snapshotted into " "`data/factor_history.csv` per run). Until then this " "toggle has no effect.") else: pf_info.caption(f"Factor history: {factor_count:,} (sku, week) entries. " "When ON, the latest factor recorded before the week " "closed is applied. Weeks without a recorded factor " "default to 1.00.") if apply_factor and factor_count > 0: fa_total = _apply_factor_to_fa(fa_total, factor_map) fa_model_only = _apply_factor_to_fa(fa_model_only, factor_map) # KAM tab is on-top vs actual β€” factors don't apply, leave as-is. tab_total, tab_kam, tab_model = st.tabs([ "🌐 Global FA", "πŸ‘₯ KAM/CM projections FA", "πŸ€– Model-only FA (excl. KAM/CM)", ]) with tab_total: _render_fa_tab( fa_total, caption="Total model forecast vs qty_total, across **all SKUs**. " "Data comes from walk-forward backtest + forecast log.", key_prefix="fa_total", ) with tab_kam: st.caption("On-top commitment vs actual sales **in that planner's own channel**. " "VP β†’ qty_wholesale (TRC/VPT/VPB/RAC docs). " "MP β†’ qty_retail (RCM docs). " "Webshop isn't here because no planner owns it β€” it's tracked " "as regular model-forecasted sales in Global FA and Model-only FA.") if fa_kam is None: st.info( "No KAM/CM projection accuracy data yet. " "VP/MP on-top values are captured every time a forecast run " "auto-saves a consensus snapshot. " "Once at least one snapshot has been saved **and** a week in " "its horizon has passed with actual sales recorded, data " "will appear here." ) st.markdown("---") _bootstrap_historical_vp_snapshot() else: sub_all, sub_both, sub_vp, sub_mp = st.tabs([ f"All ({len(fa_kam):,})", f"Both VP + MP ({int((fa_kam['input_kind']=='both').sum()):,})", f"VP only ({int((fa_kam['input_kind']=='vp_only').sum()):,})", f"MP only ({int((fa_kam['input_kind']=='mp_only').sum()):,})", ]) with sub_all: _render_fa_tab(fa_kam, caption="All SKU-weeks with any VP or MP commitment.", key_prefix="fa_kam_all") with sub_both: sub = fa_kam[fa_kam["input_kind"] == "both"] _render_fa_tab(sub if len(sub) else None, caption="SKU-weeks with **both** VP and MP commitment.", key_prefix="fa_kam_both") with sub_vp: sub = fa_kam[fa_kam["input_kind"] == "vp_only"] _render_fa_tab(sub if len(sub) else None, caption="SKU-weeks with a **VP (wholesale)** commitment but no MP.", key_prefix="fa_kam_vp") with sub_mp: sub = fa_kam[fa_kam["input_kind"] == "mp_only"] _render_fa_tab(sub if len(sub) else None, caption="SKU-weeks with an **MP (retail)** commitment but no VP.", key_prefix="fa_kam_mp") st.markdown("---") _bootstrap_historical_vp_snapshot() with tab_model: _render_fa_tab( fa_model_only, caption="Model baseline vs actual, with the **planner-owned channel " "stripped** for every SKU-week that had an on-top. " "VP on-top β†’ wholesale removed from both sides. " "MP on-top β†’ retail removed from both sides. " "SKU-weeks with no on-top are kept as-is (total vs qty_total).", key_prefix="fa_model_only", ) # ---- TOP 10 Impactors per top-3 category ---- st.divider() _render_fa_top10_impactors(fa_total) # ---- Per-SKU drill-down ---- st.divider() _render_sku_drilldown(sc, fa_total) # ---- Backtest button ---- st.divider() c1b, c2b = st.columns([3, 1]) n_bt = c1b.number_input("Backtest weeks", min_value=1, max_value=26, value=8, key="bt_weeks") if c2b.button("Re-run backtest", use_container_width=True): _run_backtest_from_app(int(n_bt)) _MONTH_ORDER = {"January":1,"February":2,"March":3,"April":4,"May":5,"June":6, "July":7,"August":8,"September":9,"October":10,"November":11,"December":12} def _week_to_month_name(year, week): try: d = datetime.strptime(f'{year}-W{week:02d}-1', '%G-W%V-%u') return d.strftime("%B") except Exception: return "Unknown" def _render_fa_top10_impactors(fa_total): """ TOP 10 Impactors: worst-performing SKUs (by absolute forecast error) in each of the 3 biggest categories (by actual volume) for the selected week. Shows SKU, Name, Actual, Forecast, FA% per category. """ st.subheader("FA TOP 10 Impactors") st.caption("Worst-performing SKUs (biggest absolute error) in each of the " "**3 largest categories by actual volume** for the chosen week.") if fa_total is None or len(fa_total) == 0: st.info("No FA data available.") return # Name lookup from plan (drill-down does the same). plan_csv = DATA_DIR / "sku_plan_list.csv" name_map = {} if plan_csv.exists(): plan = pd.read_csv(plan_csv) name_map = dict(zip(plan["sku"], plan["name"])) weeks = sorted(fa_total["week"].unique().tolist()) if not weeks: st.info("No weeks in FA data.") return cw_labels = [f"CW{w}" for w in weeks] sel = st.selectbox("Week", cw_labels, index=len(cw_labels) - 1, key="fa_top10_week") sel_week = int(sel.replace("CW", "")) wk = fa_total[fa_total["week"] == sel_week].copy() if len(wk) == 0: st.info(f"No data for {sel}.") return # Top 3 categories by actual volume this week cat_vol = wk.groupby("cat", as_index=False)["actual"].sum().sort_values( "actual", ascending=False).head(3) top3_cats = cat_vol["cat"].tolist() if not top3_cats: st.info("No categories found for this week.") return cols = st.columns(len(top3_cats)) for col, cat in zip(cols, top3_cats): sub = wk[wk["cat"] == cat].copy() sub["abs_err"] = (sub["forecast"] - sub["actual"]).abs() sub = sub.sort_values("abs_err", ascending=False).head(10) sub["Name"] = sub["sku"].map(name_map).fillna("") sub["FA%"] = (sub["fa"] * 100).round(1) out = sub[["sku", "Name", "actual", "forecast", "FA%"]].rename(columns={ "sku": "SKU", "actual": "Actual", "forecast": "Forecast", }) with col: cat_total_ac = int(wk[wk["cat"] == cat]["actual"].sum()) st.markdown(f"**{cat}** \n*{cat_total_ac:,} kom ukupno*") st.dataframe( out.style.format({ "Actual": "{:,.0f}", "Forecast": "{:,.0f}", "FA%": "{:.1f}%", }), use_container_width=True, hide_index=True, ) def _build_fa_table(df, tier_label, agg_mode="totals"): """ agg_mode="totals" β†’ sum-then-divide (monthly convention). agg_mode="per_week_avg" β†’ compute per-week, then average across weeks (weekly convention; director rule). For per-week-avg mode, FA/FA signed/BIAS per category = mean of that category's per-week metrics across selected weeks. Actuals/Forecast/ Variance/Abs-error stay as sums (they're volumes, averaging is meaningless). Hit Rate stays as row-level mean (unchanged). """ rows = [] weeks_in_df = sorted(df["week"].unique()) if "week" in df.columns else [] def _per_week_avg_for(grp): """Return (fa_avg, fa_signed_avg, bias_avg) averaged across weeks in grp.""" if "week" not in grp.columns or len(grp) == 0: return 0.0, 0.0, 0.0 wk = grp.groupby("week", as_index=False).agg(ac=("actual","sum"), fc=("forecast","sum")) wk = wk[wk["ac"] > 0] if len(wk) == 0: return 0.0, 0.0, 0.0 fa_l = (1 - (wk["fc"] - wk["ac"]).abs() / wk["ac"]).clip(lower=0) * 100 fas_l = (wk["fc"] / wk["ac"]) * 100 b_l = (wk["fc"] - wk["ac"]) / wk["ac"] * 100 return float(fa_l.mean()), float(fas_l.mean()), float(b_l.mean()) for cat in sorted(df["cat"].dropna().unique()): grp = df[df["cat"] == cat] if len(grp) == 0: continue act = int(grp["actual"].sum()) fc = int(grp["forecast"].sum()) var = fc - act abs_err = int(grp["error"].sum()) if agg_mode == "per_week_avg": fa, fa_signed, bias = _per_week_avg_for(grp) else: bias = (var / act * 100) if act > 0 else 0 fa = max(0, (1 - abs(var) / act) * 100) if act > 0 else 0 fa_signed = (fc / act * 100) if act > 0 else 0 hr = grp["hit"].mean() * 100 rows.append({"Grupacija": cat, "Actuals": act, "Forecast": fc, "Variance": var, "Abs error": abs_err, "BIAS": round(bias, 1), "FA": round(fa, 1), "FA signed": round(fa_signed, 1), "Hit Rate": round(hr, 1)}) if rows: ta = sum(r["Actuals"] for r in rows); tf = sum(r["Forecast"] for r in rows) tv = tf - ta; te = sum(r["Abs error"] for r in rows) if agg_mode == "per_week_avg": fa_tot, fas_tot, b_tot = _per_week_avg_for(df) else: b_tot = (tv/ta*100) if ta>0 else 0 fa_tot = max(0,(1-abs(tv)/ta)*100) if ta>0 else 0 fas_tot = (tf/ta*100) if ta>0 else 0 rows.append({"Grupacija": f"{tier_label} SUMARNO", "Actuals": ta, "Forecast": tf, "Variance": tv, "Abs error": te, "BIAS": round(b_tot, 1), "FA": round(fa_tot, 1), "FA signed": round(fas_tot, 1), "Hit Rate": round(df["hit"].mean()*100, 1)}) return pd.DataFrame(rows) def _render_fa_table(table_df, key=None): if table_df.empty: return raw = table_df.copy() # Compact/Full toggle β€” compact hides Variance, Abs error, FA (capped). compact = st.toggle("SaΕΎeti prikaz", value=True, key=f"fa_compact_{key}" if key else None, help="SaΕΎeti: Actuals, Forecast, FA signed, BIAS, Hit Rate. " "ProΕ‘ireni: sve kolone.") if compact: compact_cols = ["Grupacija", "Actuals", "Forecast", "FA signed", "BIAS", "Hit Rate"] raw = raw[[c for c in compact_cols if c in raw.columns]] def color_bias(v): if v < -5: return "color: #C00000; font-weight: bold" if v > 5: return "color: #006600; font-weight: bold" return "" def color_fa(v): if v >= 70: return "background-color: #C6EFCE; color: #006100" if v >= 55: return "background-color: #FFEB9C; color: #9C6500" if v >= 40: return "background-color: #FCD5B4; color: #974706" return "background-color: #FFC7CE; color: #9C0006" def color_fa_signed(v): # Symmetric around 100% β€” bands match color_fa (30/45/60 pts from 100). d = abs(v - 100) if d <= 30: return "background-color: #C6EFCE; color: #006100" if d <= 45: return "background-color: #FFEB9C; color: #9C6500" if d <= 60: return "background-color: #FCD5B4; color: #974706" return "background-color: #FFC7CE; color: #9C0006" def color_hr(v): if v >= 60: return "background-color: #C6EFCE; color: #006100" if v >= 40: return "background-color: #FFEB9C; color: #9C6500" return "background-color: #FFC7CE; color: #9C0006" def style_fn(row): s = [""] * len(row) cols = list(row.index) for i, c in enumerate(cols): if c == "BIAS": s[i] = color_bias(row[c]) elif c == "FA": s[i] = color_fa(row[c]) elif c == "FA signed": s[i] = color_fa_signed(row[c]) elif c == "Hit Rate": s[i] = color_hr(row[c]) if str(row.get("Grupacija","")).endswith("SUMARNO"): s[i] += "; font-weight: bold; background-color: #D6E4F0" if s[i] else "font-weight: bold; background-color: #D6E4F0" return s fmt = { "Actuals": "{:,.0f}", "Forecast": "{:,.0f}", "Variance": "{:+,.0f}", "Abs error": "{:,.0f}", "BIAS": "{:+.1f}%", "FA": "{:.1f}%", "Hit Rate": "{:.1f}%", } if "FA signed" in raw.columns: fmt["FA signed"] = "{:.1f}%" styled = raw.style.apply(style_fn, axis=1).format(fmt).hide(axis="index") st.dataframe(styled, use_container_width=True, hide_index=True) def _run_backtest_from_app(n_weeks=8): import copy as _copy script = Path("run_backtest.py").resolve() if not script.exists(): st.error("run_backtest.py not found next to app.py") return env = _copy.copy(os.environ) env["PYTHONIOENCODING"] = "utf-8" with st.spinner(f"Running walk-forward backtest ({n_weeks} weeks)... This may take 2-5 minutes."): r = subprocess.run( ["python", str(script), str(n_weeks), str(DATA_DIR.resolve())], cwd=str(Path(".").resolve()), stdin=subprocess.DEVNULL, capture_output=True, text=True, timeout=900, env=env, encoding="utf-8", errors="replace" ) if r.returncode == 0: st.success("Backtest complete!") else: st.error("Backtest failed") st.code(r.stdout[-3000:] + "\n" + r.stderr[-1000:]) st.rerun() # ============================================================================ # TOP 30 WHOLESALE WATCHLIST (v3.6) # ============================================================================ # Ranked by trailing 26-week wholesale volume. These SKUs drive ~80% of # wholesale volume (per diagnostic). Demand planner reviews manually each # cycle β€” no KAM input collected for these, planner owns the forecast # directly. Shows CHECK flag from forecast_engine's Review column so the # planner sees at a glance which ones need attention. WATCHLIST_SIGNOFF_PATH = DATA_DIR / "watchlist_signoff.json" def _load_watchlist_signoff(week_key): """Load sign-off state for a given CW. Returns dict {sku: {signed, at, note}}.""" if not WATCHLIST_SIGNOFF_PATH.exists(): return {} try: all_so = json.loads(WATCHLIST_SIGNOFF_PATH.read_text()) return all_so.get(week_key, {}) except Exception: return {} def _save_watchlist_signoff(week_key, signoffs): """Persist sign-off state for a given CW (merges with other CWs in file).""" all_so = {} if WATCHLIST_SIGNOFF_PATH.exists(): try: all_so = json.loads(WATCHLIST_SIGNOFF_PATH.read_text()) except Exception: pass all_so[week_key] = signoffs WATCHLIST_SIGNOFF_PATH.write_text(json.dumps(all_so, indent=2)) def _read_demand_plan_review_and_fc(plan_xlsx_path, skus): """Extract Review flag and next 4 weeks of Baseline FC forecast for a set of SKUs from the generated Polleo_Demand_Plan.xlsx. Structure (written by forecast_engine v3.6): - Sheet 'Demand Planning', headers at row 5 - Block of 11 rows per SKU starting row 6 - Row offsets within block (from row 6 = block start): +0: SKU header row (no Label) +1: Baseline FC +2: Planner Factor +3: Adjusted FC +4: VP on-top +5: VP regular +6: MP on-top +7: MP regular +8: On-top Total +9: TOTAL DEMAND +10: blank separator - Columns: 1=SKU, 2=Artikl, 3=Grupacija, 4=Oznaka, 5=Label, 6..31 = RR-25..RR-0 (26 run rate weeks), 32..44 = FC+1..FC+13 (13 forecast weeks), 45 = Review ('OK' | 'CHECK') """ review_flags = {} next4_fc = {} if not plan_xlsx_path.exists(): return review_flags, next4_fc try: from openpyxl import load_workbook wb = load_workbook(plan_xlsx_path, data_only=True, read_only=True) if "Demand Planning" not in wb.sheetnames: wb.close() return review_flags, next4_fc dp = wb["Demand Planning"] REVIEW_COL = 45 FC_START_COL = 32 # FC+1 column BLOCK = 11 START_ROW = 6 # Read from Baseline FC row (block+1), which has hard-coded values # from the engine. TOTAL DEMAND (block+9) is formula-based and # openpyxl can't resolve formulas unless Excel has cached them. BASELINE_OFFSET = 1 sku_set = set(skus) for row in dp.iter_rows(min_row=START_ROW, max_col=REVIEW_COL, values_only=False): r_num = row[0].row # Only process "block start" rows (every 11th) if (r_num - START_ROW) % BLOCK != 0: continue sku = row[0].value if not sku or sku not in sku_set: continue # Review flag on the header row rev = row[REVIEW_COL - 1].value review_flags[sku] = rev # Baseline FC row (block + 1) has the raw engine forecast bl_r = r_num + BASELINE_OFFSET fc_vals = [] for c in range(FC_START_COL, FC_START_COL + 4): v = dp.cell(bl_r, c).value fc_vals.append(int(v) if isinstance(v, (int, float)) else 0) next4_fc[sku] = fc_vals wb.close() except Exception as e: st.warning(f"Could not read Demand Plan flags: {e}") return review_flags, next4_fc def _compute_watchlist_data(): """Build the top-30 watchlist dataframe. Returns (df, meta_dict) or (None, err_msg).""" sales_path = DATA_DIR / "sales_clean.csv" plan_path = DATA_DIR / "sku_plan_list.csv" if not sales_path.exists(): return None, "sales_clean.csv missing. Run **Update sales** first." if not plan_path.exists(): return None, "sku_plan_list.csv missing. Run forecast first (initializes the plan list)." sc = pd.read_csv(sales_path) plan = pd.read_csv(plan_path) plan_skus = set(plan["sku"]) # Trailing 26-week window, defensively excluding any weeks past # the current calendar week (same logic as run_backtest.py). # sales_clean.csv *shouldn't* contain future weeks but has in the past. iso = datetime.now().isocalendar() cur_key = iso[0] * 100 + iso[1] all_yw = sc[["year", "week"]].drop_duplicates().sort_values(["year", "week"]) all_yw["_key"] = all_yw["year"] * 100 + all_yw["week"] all_yw = all_yw[all_yw["_key"] < cur_key].drop(columns=["_key"]) window = all_yw.tail(TRAILING_WEEKS).copy() window["_in_w"] = 1 sc_w = sc.merge(window[["year", "week", "_in_w"]], on=["year", "week"], how="inner") sc_w = sc_w[sc_w["sku"].isin(plan_skus)] ws_vol = sc_w.groupby("sku")["qty_wholesale"].sum().reset_index() ws_vol.columns = ["sku", "ws_26w"] total_ws = ws_vol["ws_26w"].sum() # Top N by wholesale volume top = ws_vol.sort_values("ws_26w", ascending=False).head(WATCHLIST_SIZE).reset_index(drop=True) top["rank"] = top.index + 1 top = top.merge(plan, on="sku", how="left") # Last 4 weeks wholesale actuals per SKU (ordered calendar) last4_weeks = all_yw.tail(4).copy() last4_weeks["_in_l4"] = 1 sc_l4 = sc.merge(last4_weeks[["year", "week", "_in_l4"]], on=["year", "week"], how="inner") sc_l4 = sc_l4[sc_l4["sku"].isin(top["sku"])] # Build per-SKU ordered arrays last4_by_sku = {} l4_list = list(zip(last4_weeks["year"].astype(int), last4_weeks["week"].astype(int))) for sku in top["sku"]: sub = sc_l4[sc_l4["sku"] == sku] lookup = {(int(r["year"]), int(r["week"])): int(r["qty_wholesale"]) for _, r in sub.iterrows()} last4_by_sku[sku] = [lookup.get(yw, 0) for yw in l4_list] top["last4_ws"] = top["sku"].map(last4_by_sku) top["last4_ws_avg"] = top["last4_ws"].apply( lambda x: sum(x) / len(x) if x else 0 ) # Read Review flag + next 4 FC from Demand Plan xlsx review_flags, next4_fc = _read_demand_plan_review_and_fc( OUTPUT_FILE, top["sku"].tolist() ) top["review"] = top["sku"].map(lambda s: review_flags.get(s, None)) top["next4_fc"] = top["sku"].map(lambda s: next4_fc.get(s, [0, 0, 0, 0])) top["next4_fc_avg"] = top["next4_fc"].apply( lambda x: sum(x) / len(x) if x else 0 ) # Change indicator: next4 avg vs last4 avg def _chg(row): a = row["last4_ws_avg"] b = row["next4_fc_avg"] if a > 0: return (b - a) / a * 100 return float("nan") if b == 0 else 999.0 # no baseline top["chg_pct"] = top.apply(_chg, axis=1) meta = { "total_ws_26w": int(total_ws), "top_ws_26w": int(top["ws_26w"].sum()), "pct_of_total": (top["ws_26w"].sum() / total_ws * 100) if total_ws > 0 else 0, "n_check": int((top["review"] == "CHECK").sum()), "n_no_plan": int(top["review"].isna().sum()), "last4_week_labels": [f"CW{w}" for _, w in l4_list], } return top, meta def page_top30_watchlist(): st.title("🎯 Top 30 wholesale watchlist") st.caption( "Top 30 SKUs by trailing 26-week wholesale volume. Demand planner " "reviews these weekly as a focused attention list β€” **KAMs still " "receive the full SKU list in their templates**; this is a review " "layer on top. Look for CHECK flags (forecast Β±25% off recent run " "rate) and large next-vs-last swings." ) data = _compute_watchlist_data() if data[0] is None: st.warning(data[1]) return top, meta = data cy, cw = get_current_cw() week_key = f"{cy}w{cw:02d}" signoffs = _load_watchlist_signoff(week_key) top["signed"] = top["sku"].map(lambda s: bool(signoffs.get(s, {}).get("signed", False))) # ---- Summary metrics ---- c1, c2, c3, c4 = st.columns(4) c1.metric("Top 30 wholesale volume (26w)", f"{meta['top_ws_26w']:,}", f"{meta['pct_of_total']:.0f}% of all wholesale") c2.metric("⚠️ Flagged CHECK", f"{meta['n_check']} / {WATCHLIST_SIZE}", delta_color="inverse") signed_n = int(top["signed"].sum()) c3.metric(f"Reviewed ({week_key})", f"{signed_n} / {WATCHLIST_SIZE}", delta_color="normal") no_plan = meta["n_no_plan"] c4.metric("No forecast yet", f"{no_plan}", delta_color="off", help="SKUs missing from Polleo_Demand_Plan.xlsx β€” run forecast.") st.divider() # ---- Filter toggle ---- cols = st.columns([1, 1, 1, 2]) filter_mode = cols[0].radio( "Filter", ["All 30", "CHECK only", "Unreviewed only"], horizontal=False, key="wl_filter", label_visibility="collapsed" ) sort_mode = cols[1].radio( "Sort", ["Rank (volume)", "Change % (big swings first)", "CHECK first"], horizontal=False, key="wl_sort", label_visibility="collapsed" ) view = top.copy() if filter_mode == "CHECK only": view = view[view["review"] == "CHECK"] elif filter_mode == "Unreviewed only": view = view[~view["signed"]] if sort_mode == "Change % (big swings first)": view = view.reindex(view["chg_pct"].abs().sort_values(ascending=False).index) elif sort_mode == "CHECK first": view = view.assign(_ck=(view["review"] == "CHECK").astype(int)) \ .sort_values(["_ck", "rank"], ascending=[False, True]) \ .drop(columns=["_ck"]) if len(view) == 0: st.info("No rows match the current filter.") return # ---- Build display dataframe for st.data_editor ---- # Mini sparkline: last 4 actual + next 4 forecast (8 points) def _combine_trail(row): return list(row["last4_ws"]) + list(row["next4_fc"]) display = pd.DataFrame({ "#": view["rank"].astype(int).values, "SKU": view["sku"].values, "Tier": view.get("oznaka", pd.Series(["β€”"]*len(view))).fillna("β€”").values, "XYZ": view.get("ws_xyz", pd.Series(["β€”"]*len(view))).fillna("β€”").values, "Name": view["name"].fillna("").values, "26w ws": view["ws_26w"].astype(int).values, "Trail (last 4 β†’ next 4)": [_combine_trail(r) for _, r in view.iterrows()], "Last 4 avg": view["last4_ws_avg"].round(0).astype(int).values, "Next 4 avg": view["next4_fc_avg"].round(0).astype(int).values, "Ξ” %": view["chg_pct"].round(0).values, "Flag": view["review"].fillna("β€”").values, "Reviewed": view["signed"].values, }) edited = st.data_editor( display, column_config={ "#": st.column_config.NumberColumn("#", width="small", disabled=True), "SKU": st.column_config.TextColumn("SKU", width="small", disabled=True), "Tier": st.column_config.TextColumn("Tier", width="small", disabled=True), "XYZ": st.column_config.TextColumn("XYZ", width="small", disabled=True, help="Wholesale-channel variability: X=stable, Y=variable, Z=lumpy"), "Name": st.column_config.TextColumn("Name", width="medium", disabled=True), "26w ws": st.column_config.NumberColumn( "26w ws", width="small", disabled=True, format="%d" ), "Trail (last 4 β†’ next 4)": st.column_config.LineChartColumn( "Trajectory", width="medium", help="Left 4 points: last 4 weeks of wholesale actuals. Right 4 points: next 4 weeks of Baseline FC (raw engine forecast) from Demand Plan.", ), "Last 4 avg": st.column_config.NumberColumn( "Last 4 avg", width="small", disabled=True, format="%d" ), "Next 4 avg": st.column_config.NumberColumn( "Next 4 avg", width="small", disabled=True, format="%d" ), "Ξ” %": st.column_config.NumberColumn( "Ξ” %", width="small", disabled=True, format="%+.0f%%", help="Next 4 avg vs last 4 avg. >Β±25% = engine would flag CHECK." ), "Flag": st.column_config.TextColumn( "Flag", width="small", disabled=True, help="Review flag from forecast_engine: CHECK if forecast avg Β±25% off run rate avg." ), "Reviewed": st.column_config.CheckboxColumn( "Reviewed", default=False, help=f"Sign off that you've reviewed this SKU for {week_key}." ), }, hide_index=True, use_container_width=True, key=f"watchlist_editor_{week_key}", num_rows="fixed", ) # ---- Persist sign-off changes ---- changed_any = False for _, row in edited.iterrows(): sku = row["SKU"] new_sig = bool(row["Reviewed"]) prev_sig = bool(signoffs.get(sku, {}).get("signed", False)) if new_sig != prev_sig: if new_sig: signoffs[sku] = { "signed": True, "at": datetime.now().isoformat(timespec="minutes"), } else: signoffs.pop(sku, None) changed_any = True if changed_any: _save_watchlist_signoff(week_key, signoffs) st.caption( f"Sign-offs are saved per CW in `data/watchlist_signoff.json`. " f"Click **Reviewed** after reviewing the SKU in Forecast Accuracy drill-down " f"or the Demand Plan. Unchecking clears the sign-off for this week." ) # ---- Drill-down jump helper ---- st.divider() col_a, col_b = st.columns([1, 3]) sel_sku = col_a.selectbox( "Drill into SKU:", options=[""] + view["sku"].tolist(), key="wl_drill_sku" ) if sel_sku: col_b.info( f"Jump to **Forecast accuracy** β†’ type `{sel_sku}` in the " f"SKU search to see the channel-split drill-down for this SKU." ) # ---- Footer notes ---- with st.expander("ℹ️ How this watchlist works"): st.markdown(f""" **Logic** - **Top 30** is ranked by total wholesale units over the last 26 weeks (from `sales_clean.csv`). - **Trajectory** shows last 4 weeks of wholesale actuals followed by next 4 weeks of **Baseline FC** (raw engine forecast, pre-planner-factor, pre-VP/MP) from `Polleo_Demand_Plan.xlsx`. We read Baseline FC (not TOTAL DEMAND) because TOTAL DEMAND is formula-based in the xlsx β€” Baseline is the hard-coded engine output. - **Ξ” %** is next-4-avg vs last-4-avg. Engine flags **CHECK** when this exceeds Β±25%. - **Flag** is the same Review flag shown in the Demand Plan Excel (column 45). **Workflow** 1. Review each SKU's trajectory and flag 2. If something looks off (big Ξ”, unexpected CHECK, flat forecast against rising actuals), open Forecast Accuracy β†’ search the SKU β†’ drill down 3. Adjust via Demand Planning β†’ Planner Factor, or VP input if structural 4. Re-run forecast. Watchlist refreshes automatically. 5. Tick **Reviewed** to sign off for this week (CW{cw}). **Why this watchlist exists?** These 30 SKUs represent ~80% of wholesale volume. They benefit from direct planner review each cycle β€” look at the trajectory, check flags, drill down if anything's off. KAMs still receive the **full** SKU list in their templates as usual; this watchlist is a review layer *on top*, not a replacement. **Data sources** - `sales_clean.csv` β€” wholesale volume, last 4 weeks actuals - `sku_plan_list.csv` β€” name, category, oznaka, `ws_xyz` class - `Polleo_Demand_Plan.xlsx` β€” next 4 weeks forecast + Review flag - `watchlist_signoff.json` β€” sign-off state per CW (auto-created) """) def page_consensus_plan(): st.title("Consensus plan") st.caption("Lock and version your demand plan each S&OP cycle.") cy, cw = get_current_cw() plan = load_demand_plan() # Save new snapshot st.subheader("Save current plan") if plan is None: st.warning("No forecast generated yet.") else: c1, c2 = st.columns([3, 1]) label = c1.text_input("Snapshot label", value=f"CW{cw} S&OP lock", key="snap_label") if c2.button("πŸ’Ύ Save snapshot", type="primary", use_container_width=True): fname = save_consensus_snapshot(label) if fname: st.success(f"Saved: {fname}") st.rerun() # List snapshots st.divider() st.subheader("Saved snapshots") snapshots = load_consensus_snapshots() if not snapshots: st.info("No snapshots saved yet. Save your first one above.") return for i, snap in enumerate(snapshots): c1, c2, c3 = st.columns([4, 1, 1]) c1.markdown(f"**{snap['label']}** \n" f"{snap['timestamp']} β€” {snap['n_skus']} SKUs, " f"€{snap['total_rev']:,} rev", unsafe_allow_html=True) if i == 0: c2.caption("βœ… Latest") if c3.button("πŸ—‘οΈ", key=f"del_snap_{i}"): (CONSENSUS_DIR / snap["filename"]).unlink(missing_ok=True) st.rerun() # Compare two snapshots if len(snapshots) >= 2: st.divider() st.subheader("Compare snapshots") labels = [f"{s['label']} ({s['timestamp']})" for s in snapshots] c1, c2 = st.columns(2) idx_a = c1.selectbox("Snapshot A", range(len(labels)), format_func=lambda i: labels[i], key="cmp_a") idx_b = c2.selectbox("Snapshot B", range(len(labels)), index=min(1, len(labels)-1), format_func=lambda i: labels[i], key="cmp_b") if st.button("Compare", type="primary"): a, b = snapshots[idx_a], snapshots[idx_b] st.metric("Revenue change", f"€{a['total_rev']:,} β†’ €{b['total_rev']:,}", f"{b['total_rev'] - a['total_rev']:+,}") st.metric("SKU count", f"{a['n_skus']} β†’ {b['n_skus']}", f"{b['n_skus'] - a['n_skus']:+}") # SKU-level diff a_skus = {r["SKU"] for r in a.get("rows", [])} b_skus = {r["SKU"] for r in b.get("rows", [])} added = b_skus - a_skus removed = a_skus - b_skus if added: st.caption(f"Added: {len(added)} SKUs") if removed: st.caption(f"Removed: {len(removed)} SKUs") def page_sop_meeting(): st.title("S&OP meeting view") cy, cw = get_current_cw() st.caption(f"Executive summary β€” CW{cw}, {cy}") sc = load_sales_data() plan = load_demand_plan() # --- Top metrics --- sku_prices = load_sku_prices() total_rev = compute_plan_revenue(plan, sku_prices) if plan else 0 # Accuracy acc_df = compute_accuracy_metrics(sc) if sc is not None and plan is not None else None fa_pct = acc_df["fa"].mean() * 100 if acc_df is not None and not acc_df.empty else 0 bias_pct = (acc_df["bias"].sum() / max(acc_df["actual"].sum(), 1) * 100) if acc_df is not None and not acc_df.empty else 0 # Exceptions exceptions = compute_exceptions(sc) if sc is not None else {"spikes": [], "zero_sales": []} n_exceptions = len(exceptions["spikes"]) + len(exceptions["zero_sales"]) c1, c2, c3, c4 = st.columns(4) c1.metric("13-wk revenue", f"€{total_rev:,.0f}") c2.metric("Forecast accuracy", f"{fa_pct:.1f}%") c3.metric("Bias", f"{'+' if bias_pct >= 0 else ''}{bias_pct:.1f}%") c4.metric("Exceptions", f"{n_exceptions}") # --- Revenue by week chart --- if plan: st.subheader("Revenue projection") cws = plan["cws"] weekly_rev = [0.0] * len(cws) for r in plan["rows"]: price = sku_prices.get(r["SKU"], 0) for j in range(len(cws)): b = r["baseline"][j] if j < len(r["baseline"]) else 0 f = r["factors"][j] if j < len(r["factors"]) else 1.0 weekly_rev[j] += b * f * price fig = go.Figure() fig.add_trace(go.Bar( x=cws, y=[round(v) for v in weekly_rev], marker_color="#2F5496", text=[f"€{round(v):,}" for v in weekly_rev], textposition="outside", textfont=dict(size=9), )) fig.update_layout(height=280, margin=dict(l=50,r=20,t=20,b=30), yaxis_title="Revenue €", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)") fig.update_xaxes(showgrid=False) fig.update_yaxes(showgrid=True, gridcolor="rgba(200,200,200,0.3)", tickformat=",") st.plotly_chart(fig, use_container_width=True) # --- Top risks --- st.subheader("Top risks") risks = [] for s in exceptions["spikes"][:3]: risks.append({ "Risk": f"Demand spike: {s['name']} ({s['sku']})", "Impact": f"{s['change']:+}% WoW ({s['prev']}β†’{s['curr']})", "Action": "Verify with KAM, check stock" }) if len(exceptions["zero_sales"]) > 0: n_zero = len(exceptions["zero_sales"]) risks.append({ "Risk": f"{n_zero} SKUs with zero sales 3+ weeks", "Impact": "Forecasted units with no demand", "Action": "Review for delist or season end" }) if acc_df is not None and not acc_df.empty: low_fa = acc_df.groupby("oznaka")["fa"].mean() for ozn, fa in low_fa.items(): if fa < 0.4: risks.append({ "Risk": f"Low accuracy: {ozn} tier at {fa*100:.0f}%", "Impact": "Stock risk from forecast errors", "Action": "Review model fit, consider reclassification" }) if risks: st.dataframe(pd.DataFrame(risks), use_container_width=True, hide_index=True) else: st.success("No major risks detected.") # --- Key decisions --- st.subheader("Key decisions") st.caption("Track decisions from this S&OP cycle.") decisions_file = DATA_DIR / "sop_decisions.json" if decisions_file.exists(): with open(decisions_file) as f: decisions = json.load(f) else: decisions = [] # Add new decision with st.expander("βž• Add decision"): dc1, dc2, dc3 = st.columns([3, 1, 1]) new_dec = dc1.text_input("Decision", key="new_dec") new_owner = dc2.text_input("Owner", key="new_owner") new_status = dc3.selectbox("Status", ["Pending", "In progress", "Done", "Overdue"], key="new_status") if st.button("Add", key="add_dec") and new_dec: decisions.append({ "decision": new_dec, "owner": new_owner, "status": new_status, "cw": f"CW{cw}", }) with open(decisions_file, "w") as f: json.dump(decisions, f, indent=2) st.rerun() if decisions: dec_df = pd.DataFrame(decisions) st.dataframe(dec_df.rename(columns={ "decision": "Decision", "owner": "Owner", "status": "Status", "cw": "Created" }), use_container_width=True, hide_index=True) # ================================================================== # SIDEBAR + ROUTING # ================================================================== def page_erp_promo(): """ERP Promo Calendar import page.""" st.header("πŸ“… ERP Promo Calendar") st.caption("Import promotion data from Gath ERP to improve forecast accuracy.") erp_file = DATA_DIR / "erp_promo_calendar.csv" rabatne_file = DATA_DIR / "rabatne.xlsx" # Status if erp_file.exists(): df = pd.read_csv(erp_file) plan_df = df[df['sku'].isin( pd.read_csv(DATA_DIR / "sku_plan_list.csv")['sku'].tolist() )] if (DATA_DIR / "sku_plan_list.csv").exists() else df st.success(f"βœ… ERP promo calendar loaded: {len(df):,} promo-week rows, {df['sku'].nunique():,} SKUs") yr_wk = list(zip(df['year'], df['week'])) if yr_wk: st.caption(f"Coverage: {min(yr_wk)[0]}-CW{min(yr_wk)[1]:02d} β†’ {max(yr_wk)[0]}-CW{max(yr_wk)[1]:02d}") st.caption(f"Planning SKUs with promo data: {plan_df['sku'].nunique()}") else: st.warning("No ERP promo calendar found. Upload rabatne.xlsx from Gath to get started.") st.divider() # Upload st.subheader("Import from Gath ERP") st.markdown(""" **How to get the data:** 1. Open the Gath ERP query tool 2. Run the promotions query (Rabatne) 3. Select all results β†’ Ctrl+C 4. Paste into Excel β†’ Save as `rabatne.xlsx` 5. Upload here """) uploaded = st.file_uploader("Upload rabatne.xlsx", type=["xlsx"], key="erp_upload") if uploaded: # Save to data dir with open(rabatne_file, "wb") as f: f.write(uploaded.read()) st.info(f"Saved {uploaded.name} ({uploaded.size:,} bytes)") # Run build_erp_promo.py if st.button("πŸ”„ Process promo data", use_container_width=True, key="btn_build_erp"): with st.spinner("Processing ERP promo data..."): script = os.path.join(os.path.dirname(os.path.abspath(__file__)), "build_erp_promo.py") if not os.path.exists(script): st.error("build_erp_promo.py not found in project directory.") return result = subprocess.run( ["python", script, str(rabatne_file), str(erp_file)], capture_output=True, text=True ) if result.returncode == 0: st.success("βœ… ERP promo calendar built successfully!") st.text(result.stdout) st.rerun() else: st.error("Failed to process promo data.") st.text(result.stderr) st.divider() # Recalculate uplift st.subheader("Recalculate Uplift") st.caption("Rebuild sku_uplift.csv and cat_uplift.csv using ERP-confirmed promo weeks.") if not erp_file.exists(): st.info("Import ERP promo data first.") else: if st.button("πŸ”„ Recalculate uplift from ERP data", use_container_width=True, key="btn_recalc"): with st.spinner("Recalculating uplift..."): script = os.path.join(os.path.dirname(os.path.abspath(__file__)), "recalc_uplift_erp.py") if not os.path.exists(script): st.error("recalc_uplift_erp.py not found.") return # Backup old uplift files for fn in ["sku_uplift.csv", "cat_uplift.csv"]: src = DATA_DIR / fn bak = DATA_DIR / fn.replace(".csv", "_old.csv") if src.exists() and not bak.exists(): import shutil shutil.copy(str(src), str(bak)) result = subprocess.run( ["python", script], capture_output=True, text=True, cwd=str(DATA_DIR.parent) ) if result.returncode == 0: st.success("βœ… Uplift recalculated!") st.text(result.stdout) else: st.error("Failed.") st.text(result.stderr) # Show current promo data preview if erp_file.exists(): st.divider() st.subheader("Promo Calendar Preview") df = pd.read_csv(erp_file) if (DATA_DIR / "sku_plan_list.csv").exists(): plan_skus = pd.read_csv(DATA_DIR / "sku_plan_list.csv")['sku'].tolist() df = df[df['sku'].isin(plan_skus)] cy, cw = get_current_cw() # Show upcoming promos upcoming = df[(df['year'] > cy) | ((df['year'] == cy) & (df['week'] >= cw))] if len(upcoming) > 0: st.markdown(f"**Upcoming promos ({len(upcoming)} SKU-weeks):**") st.dataframe(upcoming.sort_values(['year', 'week', 'sku']).head(50), use_container_width=True) else: st.info("No upcoming promos in the calendar. Export fresh data from Gath to see future promos.") # ================================================================== # ================================================================== # πŸ“¦ SUPPLY MODULE v0.1 # ================================================================== # Shares DATA_DIR with Demand. Reads forecast_for_supply.csv (written # by write_forecast_for_supply() on consensus save or download). # Reads stock.csv, incoming_supply.csv, supply_master.csv (user uploads). # Writes orders_draft.json (CM order entries). # ================================================================== ORDERS_FILE = DATA_DIR / "orders_draft.json" # Display-only remap for supplier names. Keys are the raw strings as they # appear in supply_master.csv; values are what the UI shows. The CSV is # untouched so upstream imports stay consistent. SUPPLIER_DISPLAY_REMAP = { "03599 ZOE FASHION D.O.O.": "09244 ABC NUTRITIONAL LTD", } def remap_supplier(name): if name is None: return name return SUPPLIER_DISPLAY_REMAP.get(str(name).strip(), name) # Safety-stock defaults (override on Supply β†’ Settings page) SUP_DEFAULTS = { "fa": 0.70, # forecast accuracy "z": 1.65, # service level factor (95%) "target_coverage": 8, # weeks of stock to top up to "horizon": 15, # weeks to project forward } # ------- Safety stock maths ------- def sup_safety_stock(avg_demand, lt_weeks, fa, z): """SS = Z Γ— avg_demand Γ— (1 βˆ’ FA) Γ— √LT (units).""" if avg_demand <= 0 or lt_weeks <= 0: return 0.0 return z * avg_demand * (1.0 - fa) * np.sqrt(lt_weeks) def sup_reorder_point(avg_demand, lt_weeks, ss): return avg_demand * lt_weeks + ss def sup_suggested_qty(on_hand, incoming_within_lt, avg_demand, target_cov_weeks, moq): target = target_cov_weeks * avg_demand gap = target - on_hand - incoming_within_lt if gap <= 0: return 0 return max(int(round(gap)), int(moq or 0)) # ------- Data loaders ------- @st.cache_data(ttl=60) def sup_load_stock(): p = DATA_DIR / "stock.csv" if not p.exists(): return None return pd.read_csv(p) STORE_STOCK_FILES = [ ("HR", "stock_stores.csv"), # kept as-is for backward compatibility ("AT", "stock_stores_at.csv"), ("SLO", "stock_stores_slo.csv"), ] @st.cache_data(ttl=60) def sup_load_stock_stores(): """Stores stock across HR / AT / SLO β€” optional. Only used by Stock projection for full-company inventory € / units. Coverage and reorder logic ignore this on purpose. Returns a SKU-level sum across countries or None if nothing uploaded.""" frames = [] for _, fname in STORE_STOCK_FILES: p = DATA_DIR / fname if p.exists(): frames.append(pd.read_csv(p)) if not frames: return None combined = pd.concat(frames, ignore_index=True) return combined.groupby("sku", as_index=False)["on_hand"].sum() @st.cache_data(ttl=60) def sup_load_forecast(): p = DATA_DIR / "forecast_for_supply.csv" if not p.exists(): return None return pd.read_csv(p) @st.cache_data(ttl=60) def sup_load_incoming(): p = DATA_DIR / "incoming_supply.csv" if not p.exists(): return None return pd.read_csv(p) @st.cache_data(ttl=60) def sup_load_master(): """Merge sku_plan_list.csv (name/cat/tier from Demand side) with supply_master.csv (supplier/lead_time/moq from Supply side) into a single frame. Missing MOQ / LT get sensible defaults. """ plan_path = DATA_DIR / "sku_plan_list.csv" sup_path = DATA_DIR / "supply_master.csv" if not plan_path.exists(): return None plan = pd.read_csv(plan_path) # normalise demand-side columns cols = {"cat": "category", "oznaka": "tier"} plan = plan.rename(columns={k: v for k, v in cols.items() if k in plan.columns}) keep = [c for c in ["sku", "name", "category", "tier"] if c in plan.columns] master = plan[keep].copy() if sup_path.exists(): sup = pd.read_csv(sup_path) for c in ("supplier", "lead_time_weeks", "moq"): if c not in sup.columns: sup[c] = np.nan master = master.merge( sup[["sku", "supplier", "lead_time_weeks", "moq"]], on="sku", how="left", ) else: master["supplier"] = "" master["lead_time_weeks"] = np.nan master["moq"] = np.nan master["lead_time_weeks"] = master["lead_time_weeks"].fillna(8).astype(float) master["moq"] = master["moq"].fillna(0).astype(float) master["supplier"] = master["supplier"].fillna("").map(remap_supplier) master["category"] = master.get("category", "").fillna("") master["tier"] = master.get("tier", "").fillna("") return master def sup_load_orders(): if ORDERS_FILE.exists(): return json.loads(ORDERS_FILE.read_text()) return {} def sup_save_orders(orders): ORDERS_FILE.write_text(json.dumps(orders, indent=2)) def sup_params(): return st.session_state.get("sup_params", SUP_DEFAULTS) # ------- Coverage engine: forward-walk stock ------- def sup_build_coverage(stock, forecast, incoming, master, params, current_yw): """Walk stock forward HORIZON weeks. Returns (long_rows_df, summary_df, weeks). Closing stock floored at zero (unmet demand not carried forward). """ cy, cw = current_yw weeks = [] y, w = cy, cw for _ in range(params["horizon"]): weeks.append((y, w)) w += 1 if w > 52: w = 1 y += 1 on_hand = dict(zip(stock["sku"], stock["on_hand"])) if stock is not None else {} def _pivot(df, val_col): if df is None or len(df) == 0: return {} d = df.copy() d["yw"] = d["year"] * 100 + d["week"] return {(r.sku, r.yw): getattr(r, val_col) for r in d.itertuples()} fc_lookup = _pivot(forecast, "demand") in_lookup = _pivot(incoming, "qty") long_rows = [] summary = [] for _, m in master.iterrows(): sku = m["sku"] lt = float(m["lead_time_weeks"]) moq = float(m["moq"] or 0) opening = float(on_hand.get(sku, 0)) stock_now = opening demands = [] closing_at_lt = None closing_at_lt1 = None incoming_in_lt = 0.0 for i, (yy, ww) in enumerate(weeks): yw = yy * 100 + ww d = float(fc_lookup.get((sku, yw), 0)) inc = float(in_lookup.get((sku, yw), 0)) closing = max(0.0, opening + inc - d) cov = (closing / d) if d > 0 else np.nan long_rows.append({ "sku": sku, "year": yy, "week": ww, "yw": yw, "stock": opening, "demand": d, "incoming": inc, "closing": closing, "coverage_wk": cov, }) demands.append(d) if i < int(lt): incoming_in_lt += inc if i == int(lt): closing_at_lt = closing if i == int(lt) + 1: closing_at_lt1 = closing opening = closing avg_d = float(np.mean(demands)) if demands else 0.0 ss = sup_safety_stock(avg_d, lt, params["fa"], params["z"]) rop = sup_reorder_point(avg_d, lt, ss) if closing_at_lt is not None and closing_at_lt <= ss: flag = "order_now" elif closing_at_lt1 is not None and closing_at_lt1 <= ss: flag = "order_next_week" else: flag = "ok" # Suggested quantity aligned with the flag: only recommend ordering when # the lookahead says action is needed. Size the order to bring the # projected lead-time-boundary stock up to target_coverage Γ— avg_demand, # then round up to MOQ. if flag == "ok": sugg = 0 else: target_units = params["target_coverage"] * avg_d projected_at_lt = closing_at_lt if closing_at_lt is not None else 0 gap = target_units - projected_at_lt if gap <= 0: sugg = int(moq or 0) else: sugg = max(int(round(gap)), int(moq or 0)) summary.append({ "sku": sku, "name": m.get("name", ""), "supplier": m.get("supplier", ""), "category": m.get("category", ""), "tier": m.get("tier", ""), "lt_weeks": lt, "moq": int(moq), "on_hand": int(round(stock_now)), "avg_weekly_demand": round(avg_d, 1), "safety_stock": int(round(ss)), "rop": int(round(rop)), "coverage_wk_now": round((stock_now / avg_d), 1) if avg_d > 0 else np.nan, "closing_at_lt": int(round(closing_at_lt or 0)), "flag": flag, "suggested_qty": int(sugg), }) return pd.DataFrame(long_rows), pd.DataFrame(summary), weeks def _sup_data_guard(): """Load all four supply inputs. Return (stock, forecast, incoming, master) or None if anything critical is missing β€” also writes a user-facing warning.""" stock = sup_load_stock() forecast = sup_load_forecast() incoming = sup_load_incoming() master = sup_load_master() missing = [] if stock is None: missing.append("stock.csv") if forecast is None: missing.append("forecast_for_supply.csv") if master is None: missing.append("sku_plan_list.csv") if missing: st.warning("Missing files: " + ", ".join(missing)) st.caption("Go to **Supply β†’ Upload data** to upload them, or run the " "Demand β†’ Download page to generate `forecast_for_supply.csv`.") return None if incoming is None: # Treat as empty β€” not blocking incoming = pd.DataFrame(columns=["sku", "year", "week", "qty"]) return stock, forecast, incoming, master # ================================================================== # SUPPLY PAGES # ================================================================== def page_supply_dashboard(): st.title("πŸ“¦ Supply dashboard") st.caption(f"CW{get_current_cw()[1]} Β· {get_current_cw()[0]}") g = _sup_data_guard() if g is None: return stock, forecast, incoming, master = g params = sup_params() _, summary, _ = sup_build_coverage(stock, forecast, incoming, master, params, get_current_cw()) c = st.columns(4) c[0].metric("SKUs in scope", len(summary)) c[1].metric("πŸ”΄ Order now", int((summary["flag"] == "order_now").sum())) c[2].metric("🟑 Next week", int((summary["flag"] == "order_next_week").sum())) avg_cov = summary["coverage_wk_now"].replace([np.inf, -np.inf], np.nan).dropna().mean() c[3].metric("Avg coverage wk", f"{avg_cov:.1f}" if not np.isnan(avg_cov) else "β€”") st.subheader("By category") by_cat = summary.groupby("category").agg( skus=("sku", "count"), order_now=("flag", lambda s: int((s == "order_now").sum())), order_next=("flag", lambda s: int((s == "order_next_week").sum())), avg_cov=("coverage_wk_now", "mean"), ).round(1).sort_values("order_now", ascending=False) st.dataframe(by_cat, use_container_width=True) st.subheader("By tier") by_tier = summary.groupby("tier").agg( skus=("sku", "count"), order_now=("flag", lambda s: int((s == "order_now").sum())), order_next=("flag", lambda s: int((s == "order_next_week").sum())), avg_cov=("coverage_wk_now", "mean"), ).round(1) st.dataframe(by_tier, use_container_width=True) def page_supply_projection(): st.title("πŸ“ˆ Stock projection") st.caption("Global stock roll-forward: opening βˆ’ demand + incoming. " "Per-SKU detail lives on Coverage and Inventory health.") g = _sup_data_guard() if g is None: return stock, forecast, incoming, master = g params = sup_params() cy, cw = get_current_cw() # Fold stores into opening stock β€” for the full-company CFO picture. # Coverage / alerts still use WH-only (stock.csv). stores = sup_load_stock_stores() uploaded_countries = [c for c, f in STORE_STOCK_FILES if (DATA_DIR / f).exists()] missing_countries = [c for c, f in STORE_STOCK_FILES if not (DATA_DIR / f).exists()] if stores is not None and len(stores): combined = pd.concat([stock, stores], ignore_index=True) stock = combined.groupby("sku", as_index=False)["on_hand"].sum() caption = f"πŸ“¦ Includes store stock: **{', '.join(uploaded_countries)}** ({len(stores)} SKUs)" if missing_countries: caption += f" Β· βšͺ missing: {', '.join(missing_countries)}" st.caption(caption) else: st.caption("πŸ“¦ WH stock only β€” upload store stock on Supply β†’ Upload data for full picture.") costs_path = DATA_DIR / "sku_costs.csv" using_cost = costs_path.exists() cost_map = {} if using_cost: costs = pd.read_csv(costs_path) cost_map = dict(zip(costs["sku"], costs["cost_price"])) else: st.info("`sku_costs.csv` missing β€” value line will be 0. " "Upload via Supply β†’ Cost prices.") # ---- Extend master + forecast to cover long-tail (non-forecasted) SKUs ---- # Any SKU that has stock but no forecast gets a trailing-13w-avg demand proxy # so WH + CFO see the full picture (clothing/fitness/gadgets/low-sales tail). # This is run-rate, not a forecast β€” flat across the horizon. tiered_skus = set(master["sku"]) stock_skus = set(stock["sku"]) longtail_skus = stock_skus - tiered_skus sales = load_sales_data() if longtail_skus and sales is not None: cur_yw = cy * 100 + cw recent = sales[(sales["yw"] < cur_yw) & (sales["sku"].isin(longtail_skus))] if len(recent): yw_cut = sorted(recent["yw"].unique())[-TRAILING_WEEKS // 2:] # last 13w recent13 = recent[recent["yw"].isin(yw_cut)] avg_weekly = recent13.groupby("sku")["qty_total"].sum() / max(len(yw_cut), 1) else: avg_weekly = pd.Series(dtype=float) # Build synthetic forecast rows (flat avg across horizon) horizon_weeks = [] y, w = cy, cw for _ in range(params["horizon"]): w += 1 if w > 52: w = 1 y += 1 horizon_weeks.append((y, w)) synth_rows = [] for sku in longtail_skus: d = float(avg_weekly.get(sku, 0.0)) for (yy, ww) in horizon_weeks: synth_rows.append({"sku": sku, "year": yy, "week": ww, "demand": d}) if synth_rows: forecast = pd.concat([forecast, pd.DataFrame(synth_rows)], ignore_index=True) # Extend master β€” flag these rows so the user can filter extra_master = pd.DataFrame({ "sku": list(longtail_skus), "name": "", "category": "LONG_TAIL", "tier": "UNTIERED", "supplier": "", "lead_time_weeks": 8.0, "moq": 0.0, }) master = pd.concat([master, extra_master], ignore_index=True) cats = sorted([c for c in master["category"].dropna().unique() if c]) cat_sel = st.selectbox("Category", ["All"] + cats, key="proj_cat") if cat_sel != "All": allowed_skus = set(master[master["category"] == cat_sel]["sku"]) else: allowed_skus = set(master["sku"]) long_rows, _, _ = sup_build_coverage( stock, forecast, incoming, master, params, (cy, cw) ) long = long_rows[long_rows["sku"].isin(allowed_skus)].copy() long["is_longtail"] = long["sku"].isin(longtail_skus) long["value_close"] = long["sku"].map(cost_map).fillna(0.0) * long["closing"] agg = long.groupby(["year", "week", "yw"], as_index=False).agg( opening=("stock", "sum"), demand=("demand", "sum"), incoming=("incoming", "sum"), closing=("closing", "sum"), closing_value=("value_close", "sum"), ).sort_values("yw").reset_index(drop=True) stock_f = stock[stock["sku"].isin(allowed_skus)] current_units = float(stock_f["on_hand"].sum()) current_value = float( (stock_f["on_hand"] * stock_f["sku"].map(cost_map).fillna(0.0)).sum() ) total_demand = float(agg["demand"].sum()) total_incoming = float(agg["incoming"].sum()) c = st.columns(4) c[0].metric("Current stock (units)", f"{int(current_units):,}") c[1].metric("Current stock (€)", f"{current_value:,.0f}") c[2].metric(f"Demand next {len(agg)}w", f"{int(total_demand):,}") c[3].metric(f"Incoming next {len(agg)}w", f"{int(total_incoming):,}") # Forecasted vs long-tail split (current stock value) if longtail_skus and using_cost: lt_stock = stock_f[stock_f["sku"].isin(longtail_skus)] lt_value = float( (lt_stock["on_hand"] * lt_stock["sku"].map(cost_map).fillna(0.0)).sum() ) fc_value = current_value - lt_value total = max(current_value, 1) st.caption( f"**Stock € split** β€” forecasted: €{fc_value:,.0f} " f"({fc_value / total * 100:.0f}%) Β· " f"long-tail (run-rate proxy): €{lt_value:,.0f} " f"({lt_value / total * 100:.0f}%)" ) x_labels = [f"CW{int(w)}" for w in agg["week"]] fig = go.Figure() fig.add_trace(go.Bar( x=x_labels, y=[-int(v) for v in agg["demand"]], name="Demand", marker_color="rgba(200, 80, 80, 0.55)", text=[f"-{int(v):,}" if v > 0 else "" for v in agg["demand"]], textposition="outside", textfont=dict(size=9), )) fig.add_trace(go.Bar( x=x_labels, y=[int(v) for v in agg["incoming"]], name="Incoming", marker_color="rgba(80, 160, 100, 0.55)", text=[f"+{int(v):,}" if v > 0 else "" for v in agg["incoming"]], textposition="outside", textfont=dict(size=9), )) fig.add_trace(go.Scatter( x=x_labels, y=[int(v) for v in agg["closing"]], name="Closing stock", mode="lines+markers+text", line=dict(color="#2F5496", width=2.5), marker=dict(size=7), text=[f"{int(v):,}" for v in agg["closing"]], textposition="top center", textfont=dict(size=9), )) fig.update_layout( barmode="relative", height=460, yaxis=dict(title="Units", tickformat=","), margin=dict(l=60, r=20, t=30, b=40), hovermode="x unified", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", legend=dict(orientation="h", y=1.05), ) fig.update_xaxes(showgrid=False) fig.update_yaxes(showgrid=True, gridcolor="rgba(200,200,200,0.3)", zeroline=True, zerolinecolor="#999") st.plotly_chart(fig, use_container_width=True) if using_cost: st.subheader("Stock value roll-forward") fig_val = go.Figure() fig_val.add_trace(go.Scatter( x=x_labels, y=[round(v) for v in agg["closing_value"]], mode="lines+markers+text", line=dict(color="#B8860B", width=2.5), marker=dict(size=7), text=[f"€{int(v):,}" for v in agg["closing_value"]], textposition="top center", textfont=dict(size=9), name="Closing stock value (€)", )) fig_val.update_layout( height=300, yaxis=dict(title="€", tickformat=","), margin=dict(l=60, r=20, t=30, b=40), hovermode="x unified", plot_bgcolor="rgba(0,0,0,0)", paper_bgcolor="rgba(0,0,0,0)", legend=dict(orientation="h", y=1.05), ) fig_val.update_yaxes(showgrid=True, gridcolor="rgba(200,200,200,0.3)") st.plotly_chart(fig_val, use_container_width=True) with st.expander("Weekly detail"): disp = agg.copy() disp["week"] = disp["week"].map(lambda w: f"CW{int(w)}") disp = disp[["week", "opening", "demand", "incoming", "closing", "closing_value"]] disp.columns = ["Week", "Opening", "Demand", "Incoming", "Closing", "Closing value €"] st.dataframe(disp, use_container_width=True, hide_index=True) st.caption("πŸ’‘ Later: pallets view once logistics data lands (cases/pallet Γ— units).") def page_supply_coverage(): st.title("πŸ“‹ Coverage table") g = _sup_data_guard() if g is None: return stock, forecast, incoming, master = g params = sup_params() cov_rows, summary, _ = sup_build_coverage(stock, forecast, incoming, master, params, get_current_cw()) c = st.columns([1, 1, 1, 1]) sup = c[0].selectbox("Supplier", ["All"] + sorted([s for s in summary["supplier"].unique() if s])) cat = c[1].selectbox("Category", ["All"] + sorted([s for s in summary["category"].unique() if s])) tier = c[2].selectbox("Tier", ["All"] + sorted([s for s in summary["tier"].unique() if s])) flag_sel = c[3].selectbox("Flag", ["All", "Needs action", "order_now", "order_next_week", "ok"]) df = summary.copy() if sup != "All": df = df[df["supplier"] == sup] if cat != "All": df = df[df["category"] == cat] if tier != "All": df = df[df["tier"] == tier] if flag_sel == "Needs action": df = df[df["flag"].isin(["order_now", "order_next_week"])] elif flag_sel != "All": df = df[df["flag"] == flag_sel] st.caption(f"{len(df)} SKUs Β· horizon {params['horizon']} wk Β· " f"FA {params['fa']:.0%} Β· Z {params['z']}") st.dataframe(df.sort_values(["flag", "coverage_wk_now"]), use_container_width=True, height=380, hide_index=True) st.subheader("SKU detail β€” weekly coverage grid") if len(df): sel = st.selectbox("Pick SKU", df["sku"].tolist(), key="sup_cov_sel") if sel: block = cov_rows[cov_rows["sku"] == sel].copy() block["wk"] = block.apply(lambda r: f"W{int(r.week)}", axis=1) grid = pd.DataFrame({ "Stock": block.set_index("wk")["stock"].round(0).astype(int), "Demand": block.set_index("wk")["demand"].round(0).astype(int), "Incoming": block.set_index("wk")["incoming"].round(0).astype(int), "Closing": block.set_index("wk")["closing"].round(0).astype(int), "Coverage wk": block.set_index("wk")["coverage_wk"].round(1), }).T st.dataframe(grid, use_container_width=True) info = df[df["sku"] == sel].iloc[0] st.info(f"Safety stock = **{info['safety_stock']:,}** Β· " f"ROP = {info['rop']:,} Β· LT = {info['lt_weeks']:.0f} wk Β· " f"MOQ = {int(info['moq']):,} Β· " f"Suggested order qty = **{info['suggested_qty']:,}**") def page_supply_alerts(): st.title("🚨 Reorder alerts") g = _sup_data_guard() if g is None: return stock, forecast, incoming, master = g params = sup_params() _, summary, _ = sup_build_coverage(stock, forecast, incoming, master, params, get_current_cw()) alerts = summary[summary["flag"].isin(["order_now", "order_next_week"])].copy() alerts["flag_label"] = alerts["flag"].map({ "order_now": "πŸ”΄ Order now", "order_next_week": "🟑 Next week", }) alerts = alerts.sort_values(["flag", "coverage_wk_now"]) st.caption(f"{len(alerts)} SKUs need action Β· grouped by supplier. " "Go to **Order entry** to commit order quantities.") if len(alerts) == 0: st.success("No reorder alerts β€” all SKUs covered within lead time.") return for supplier, group in alerts.groupby("supplier"): label = supplier or "(no supplier set)" with st.expander(f"{label} β€” {len(group)} SKUs", expanded=True): st.dataframe( group[["flag_label", "sku", "name", "tier", "lt_weeks", "on_hand", "avg_weekly_demand", "safety_stock", "coverage_wk_now", "moq", "suggested_qty"]], use_container_width=True, hide_index=True, ) def page_supply_order_entry(): st.title("✏️ Order entry") st.caption("Enter final order quantity per SKU. Suggested value is pre-filled. " "Drafts are saved between sessions.") g = _sup_data_guard() if g is None: return stock, forecast, incoming, master = g params = sup_params() _, summary, _ = sup_build_coverage(stock, forecast, incoming, master, params, get_current_cw()) alerts = summary[summary["flag"].isin(["order_now", "order_next_week"])].copy() if len(alerts) == 0: st.success("No SKUs need ordering this cycle.") return orders = sup_load_orders() edit_df = pd.DataFrame([{ "sku": r["sku"], "name": r["name"], "supplier": r["supplier"], "flag": r["flag"], "suggested": r["suggested_qty"], "moq": r["moq"], "order_qty": int(orders.get(r["sku"], {}).get("qty", r["suggested_qty"])), "note": orders.get(r["sku"], {}).get("note", ""), } for _, r in alerts.iterrows()]) edited = st.data_editor( edit_df, use_container_width=True, hide_index=True, column_config={ "order_qty": st.column_config.NumberColumn("Order qty", min_value=0, step=100), "note": st.column_config.TextColumn("Note"), "suggested": st.column_config.NumberColumn("Suggested", disabled=True), "moq": st.column_config.NumberColumn("MOQ", disabled=True), }, disabled=["sku", "name", "supplier", "flag"], num_rows="fixed", key="sup_order_editor", ) col1, col2 = st.columns([1, 4]) if col1.button("πŸ’Ύ Save draft", use_container_width=True, type="primary"): for _, r in edited.iterrows(): orders[r["sku"]] = { "qty": int(r["order_qty"]), "supplier": r["supplier"], "moq": int(r["moq"]), "note": r["note"], "saved_at": datetime.now().isoformat(timespec="seconds"), "cw": f"CW{get_current_cw()[1]}", } sup_save_orders(orders) st.success(f"Saved {len(edited)} order lines to `{ORDERS_FILE.name}`") col2.caption("Order lines with qty = 0 are kept as draft but excluded from the download.") def page_supply_download(): st.title("πŸ“₯ Download supply plan") orders = sup_load_orders() if not orders: st.info("No orders saved yet. Go to **Order entry** first.") return df = pd.DataFrame([ {"sku": sku, **v} for sku, v in orders.items() if v.get("qty", 0) > 0 ]) if len(df) == 0: st.info("No order lines with qty > 0 yet.") return st.dataframe(df, use_container_width=True, hide_index=True) from io import BytesIO buf = BytesIO() with pd.ExcelWriter(buf, engine="openpyxl") as w: df.to_excel(w, sheet_name="Supply_plan", index=False) buf.seek(0) cy, cw = get_current_cw() filename = f"Supply_plan_CW{cw}_{datetime.now():%Y%m%d}.xlsx" st.download_button( "πŸ“₯ Download Supply plan (Excel)", data=buf, file_name=filename, mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", type="primary", use_container_width=True, ) def page_supply_upload(): st.title("πŸ“€ Upload supply data") st.caption("These files live in the shared `data/` folder. Existing files " "are overwritten on upload.") st.subheader("1 Β· Warehouse stock snapshot") st.caption("Columns: `sku, on_hand` (or Croatian `Ε ifra, Zaliha` β€” auto-renamed). " "**WH only** β€” drives Coverage / Reorder alerts. " "Store stock has its own uploader below.") up = st.file_uploader("stock.csv or .xlsx", type=["csv", "xlsx"], key="sup_up_stock") if up is not None: df = pd.read_excel(up) if up.name.endswith(".xlsx") else pd.read_csv(up) # Auto-normalise column names β€” ERP exports come in Croatian / encoded col_map = {} for c in df.columns: cl = str(c).strip().lower() if cl in ("sku", "code", "Ε‘ifra", "sifra") or "ifra" in cl: col_map[c] = "sku" elif cl in ("on_hand", "zaliha", "stock", "stanje", "qty"): col_map[c] = "on_hand" df = df.rename(columns=col_map) if "sku" not in df.columns or "on_hand" not in df.columns: st.error(f"Could not find sku / on_hand columns. Got: {list(df.columns)}") else: df = df[["sku", "on_hand"]].dropna(subset=["sku"]) df["on_hand"] = pd.to_numeric(df["on_hand"], errors="coerce").fillna(0).astype(int) # Sum across locations β€” works for WH-only OR combined WH+stores exports raw_rows = len(df) df = df.groupby("sku", as_index=False)["on_hand"].sum() df = df[df["on_hand"] != 0] df.to_csv(DATA_DIR / "stock.csv", index=False) rolled = raw_rows - len(df) msg = f"Saved stock.csv β€” {len(df)} SKUs with stock" if rolled > 0: msg += f" (rolled up {rolled} duplicate/location rows)" st.success(msg) clear_all_caches() st.divider() st.subheader("2 Β· Store stock snapshots (optional)") st.caption("Columns: `sku, on_hand` (or Croatian `Ε ifra, Zaliha`). " "**Stores only** β€” feeds the Stock projection page for full-company " "€ visibility. Coverage / reorder logic ignore these files. " "Upload a file per country; reuploading one doesn't affect the others.") def _ingest_store_stock(uploaded, target_filename, country_label): df = pd.read_excel(uploaded) if uploaded.name.endswith(".xlsx") else pd.read_csv(uploaded) col_map = {} for c in df.columns: cl = str(c).strip().lower() if cl in ("sku", "code", "Ε‘ifra", "sifra") or "ifra" in cl: col_map[c] = "sku" elif cl in ("on_hand", "zaliha", "stock", "stanje", "qty"): col_map[c] = "on_hand" df = df.rename(columns=col_map) if "sku" not in df.columns or "on_hand" not in df.columns: st.error(f"[{country_label}] Could not find sku / on_hand. Got: {list(df.columns)}") return df = df[["sku", "on_hand"]].dropna(subset=["sku"]) df["on_hand"] = pd.to_numeric(df["on_hand"], errors="coerce").fillna(0).astype(int) raw_rows = len(df) df = df.groupby("sku", as_index=False)["on_hand"].sum() df = df[df["on_hand"] != 0] df.to_csv(DATA_DIR / target_filename, index=False) rolled = raw_rows - len(df) msg = f"[{country_label}] Saved {target_filename} β€” {len(df)} SKUs with stock" if rolled > 0: msg += f" (rolled up {rolled} duplicate/location rows)" st.success(msg) clear_all_caches() st.rerun() for country, fname in STORE_STOCK_FILES: p = DATA_DIR / fname if p.exists(): n = len(pd.read_csv(p)) st.caption(f"βœ… **{country}** β€” `{fname}` ({n} SKUs)") else: st.caption(f"βšͺ **{country}** β€” not uploaded") up = st.file_uploader( f"{country} store stock ({fname})", type=["csv", "xlsx"], key=f"sup_up_stock_stores_{country.lower()}", label_visibility="collapsed", ) if up is not None: _ingest_store_stock(up, fname, country) st.divider() st.subheader("3 Β· Incoming supply (open POs)") st.caption("Columns: `sku, year, week, qty` β€” purchase orders expected to arrive.") up = st.file_uploader("incoming_supply.csv or .xlsx", type=["csv", "xlsx"], key="sup_up_in") if up is not None: df = pd.read_excel(up) if up.name.endswith(".xlsx") else pd.read_csv(up) df.to_csv(DATA_DIR / "incoming_supply.csv", index=False) st.success(f"Saved incoming_supply.csv β€” {len(df)} rows") st.cache_data.clear() st.divider() st.subheader("4 Β· Supply master (MOQ + lead time)") st.caption("Columns: `sku, supplier, lead_time_weeks, moq` β€” merged with " "`sku_plan_list.csv` at runtime. Upload once, edit rarely.") up = st.file_uploader("supply_master.csv or .xlsx", type=["csv", "xlsx"], key="sup_up_mas") if up is not None: df = pd.read_excel(up) if up.name.endswith(".xlsx") else pd.read_csv(up) df.to_csv(DATA_DIR / "supply_master.csv", index=False) st.success(f"Saved supply_master.csv β€” {len(df)} SKUs") st.cache_data.clear() st.divider() st.subheader("5 Β· Forecast bridge from Demand") fsp = DATA_DIR / "forecast_for_supply.csv" if fsp.exists(): st.success(f"βœ… `forecast_for_supply.csv` β€” " f"last refreshed {datetime.fromtimestamp(fsp.stat().st_mtime):%Y-%m-%d %H:%M}") else: st.warning("⚠️ `forecast_for_supply.csv` not yet generated") if st.button("πŸ”„ Generate from current Demand Plan", use_container_width=True): src = write_forecast_for_supply() if src == "output_total": st.success("Generated from **Demand Output - Total** sheet.") st.cache_data.clear() elif src == "reconstructed": st.success("Generated β€” reconstructed from Demand Planning blocks. " "(Open the xlsx in Excel and save it to enable " "direct reads from Demand Output - Total.)") st.cache_data.clear() else: st.error("No demand plan loaded β€” run the forecast first on the Demand side.") st.caption("πŸ’‘ To edit MOQs for all SKUs at once, use the **MOQ** page.") def supply_reference_editor( *, title: str, intro: str, storage_filename: str, value_cols: list[str], column_config: dict, upload_hint: str, key_prefix: str, extra_badges=None, # optional callable(master, edited) -> list[str] of badges per row ): """Shared Upload + bulk-edit UX for a per-SKU supply reference table. Upload accepts an Excel/CSV with `sku` + any of `value_cols`; rows are upserted into `data/` without disturbing other columns. The editor merges the storage file with `sku_plan_list.csv` for context (name / category / tier) and only allows edits to `value_cols`. """ st.title(title) if intro: st.caption(intro) storage_path = DATA_DIR / storage_filename # ---- Upload ---- st.subheader("Upload") st.caption(upload_hint) up = st.file_uploader(f"`sku` + {', '.join(value_cols)}", type=["xlsx", "csv"], key=f"{key_prefix}_upload") if up is not None: try: new = pd.read_excel(up) if up.name.endswith(".xlsx") else pd.read_csv(up) # Normalise: lower, strip, spacesβ†’underscores, drop non-alnum noise def _norm(c): s = str(c).strip().lower().replace(" ", "_") return "".join(ch for ch in s if ch.isalnum() or ch == "_") new.columns = [_norm(c) for c in new.columns] # Known Croatian / ERP aliases β†’ canonical alias = { "sku": {"sifra", "ifra", "code", "artikl", "artikal"}, "cost_price": {"nabavna", "nabavna_cijena", "costprice", "cost", "cijena"}, "ruc": {"marza", "margin", "ruc_pct"}, "moq": {"minimum_order_qty", "min_order", "min_qty"}, "on_hand": {"zaliha", "stock", "stanje", "qty"}, } for canonical, aliases in alias.items(): if canonical in new.columns: continue for a in aliases: if a in new.columns: new = new.rename(columns={a: canonical}) break missing = [c for c in ["sku", *value_cols] if c not in new.columns] if missing: st.error(f"Upload must contain these columns: {missing}. Found: {list(new.columns)}") else: new = new[["sku", *value_cols]].dropna(subset=["sku"]) for c in value_cols: new[c] = pd.to_numeric(new[c], errors="coerce").fillna(0) if storage_path.exists(): store = pd.read_csv(storage_path) else: store = pd.DataFrame(columns=["sku", *value_cols]) for c in value_cols: if c not in store.columns: store[c] = np.nan store = store.set_index("sku") for _, r in new.iterrows(): for c in value_cols: store.loc[r["sku"], c] = r[c] store = store.reset_index() store.to_csv(storage_path, index=False) st.success(f"Upserted {len(new)} SKU(s) into `{storage_filename}`.") st.cache_data.clear() except Exception as e: st.error(f"Could not parse upload: {e}") st.divider() # ---- Table editor ---- st.subheader("Edit") plan_path = DATA_DIR / "sku_plan_list.csv" if not plan_path.exists(): st.warning("No SKU master data. Upload `sku_plan_list.csv` on the Demand side first.") return plan = pd.read_csv(plan_path) plan = plan.rename(columns={"cat": "category", "oznaka": "tier"}) keep = [c for c in ["sku", "name", "category", "tier"] if c in plan.columns] master = plan[keep].copy() if storage_path.exists(): store = pd.read_csv(storage_path) for c in value_cols: if c not in store.columns: store[c] = np.nan master = master.merge(store[["sku", *value_cols]], on="sku", how="left") else: for c in value_cols: master[c] = np.nan for c in value_cols: master[c] = master[c].fillna(0) cats = ["All"] + sorted([c for c in master.get("category", pd.Series()).dropna().unique() if c]) c1, c2 = st.columns([1, 3]) cat_sel = c1.selectbox("Category", cats, key=f"{key_prefix}_cat_filter") search = c2.text_input("Search SKU / name", key=f"{key_prefix}_search").strip().lower() view = master.copy() if cat_sel != "All": view = view[view["category"] == cat_sel] if search: mask = view["sku"].astype(str).str.lower().str.contains(search, na=False) if "name" in view.columns: mask |= view["name"].astype(str).str.lower().str.contains(search, na=False) view = view[mask] edit_cols = [c for c in ["sku", "name", "category", "tier"] if c in view.columns] + value_cols view = view[edit_cols].reset_index(drop=True) edited = st.data_editor( view, use_container_width=True, hide_index=True, disabled=[c for c in edit_cols if c not in value_cols], column_config=column_config, num_rows="fixed", key=f"{key_prefix}_editor", ) if extra_badges is not None: msgs = extra_badges(master, edited) for m in msgs: st.caption(m) col_save, col_info = st.columns([1, 4]) if col_save.button(f"πŸ’Ύ Save changes", use_container_width=True, type="primary", key=f"{key_prefix}_save"): if storage_path.exists(): store = pd.read_csv(storage_path) else: store = pd.DataFrame(columns=["sku", *value_cols]) for c in value_cols: if c not in store.columns: store[c] = np.nan store = store.set_index("sku") changed = 0 for _, r in edited.iterrows(): row_changed = False for c in value_cols: new_val = float(r[c] or 0) existing = float(store.at[r["sku"], c]) if r["sku"] in store.index and pd.notna(store.at[r["sku"], c]) else None if existing is None or existing != new_val: store.loc[r["sku"], c] = new_val row_changed = True if row_changed: changed += 1 store = store.reset_index() store.to_csv(storage_path, index=False) st.success(f"Saved β€” {changed} row(s) updated.") st.cache_data.clear() col_info.caption(f"{len(view)} row(s) shown. Filter by category or search to narrow down.") st.divider() with st.expander("⬇️ Download current table"): dl = master[["sku", *value_cols]].copy() if "name" in master.columns: dl.insert(1, "name", master["name"]) if "category" in master.columns: dl.insert(2 if "name" in dl.columns else 1, "category", master["category"]) c1, c2 = st.columns(2) c1.download_button( "Download as CSV", dl.to_csv(index=False).encode("utf-8"), file_name=storage_filename, mime="text/csv", key=f"{key_prefix}_dl", ) # Excel version β€” useful for editing / cross-referencing in Excel from io import BytesIO xlsx_buf = BytesIO() with pd.ExcelWriter(xlsx_buf, engine="openpyxl") as writer: dl.to_excel(writer, index=False, sheet_name="data") xlsx_name = storage_filename.rsplit(".", 1)[0] + ".xlsx" c2.download_button( "Download as Excel", xlsx_buf.getvalue(), file_name=xlsx_name, mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", key=f"{key_prefix}_dl_xlsx", ) def page_supply_moq(): supply_reference_editor( title="πŸ“¦ MOQ β€” minimum order quantities", intro="Upload an Excel with `sku, moq` columns, or edit the table below. " "Other columns in `supply_master.csv` (supplier, lead time) are preserved.", storage_filename="supply_master.csv", value_cols=["moq"], column_config={ "moq": st.column_config.NumberColumn("MOQ", min_value=0, step=100, required=True), }, upload_hint="Excel/CSV with two columns: `sku` and `moq`. " "Existing MOQs are overwritten for SKUs in the upload; others are untouched.", key_prefix="moq", ) def _logistics_badges(master, edited): """Flag rows where pcs_per_pallet doesn't match tpkg * tpkgs_per_pallet.""" msgs = [] mismatches = 0 for _, r in edited.iterrows(): expected = float(r.get("pcs_per_transport_pkg", 0) or 0) * float(r.get("transport_pkgs_per_pallet", 0) or 0) actual = float(r.get("pcs_per_pallet", 0) or 0) if expected > 0 and actual > 0 and abs(expected - actual) > 0.5: mismatches += 1 if mismatches: msgs.append(f"⚠️ {mismatches} row(s) have `pcs_per_pallet` β‰  `pcs_per_transport_pkg Γ— transport_pkgs_per_pallet`. " "This is allowed (partial / mixed pallets) but worth double-checking.") return msgs def page_supply_logistics(): supply_reference_editor( title="🚚 Logistics β€” pack & pallet sizes", intro="Per-SKU transport configuration: pieces per transport package, " "transport packages per pallet, pieces per pallet. Used by order entry " "to round quantities to full pallets.", storage_filename="sku_logistics.csv", value_cols=["pcs_per_transport_pkg", "transport_pkgs_per_pallet", "pcs_per_pallet"], column_config={ "pcs_per_transport_pkg": st.column_config.NumberColumn( "Pcs / transport pkg", min_value=0, step=1), "transport_pkgs_per_pallet": st.column_config.NumberColumn( "Transport pkgs / pallet", min_value=0, step=1), "pcs_per_pallet": st.column_config.NumberColumn( "Pcs / pallet", min_value=0, step=1, help="Usually = pcs_per_transport_pkg Γ— transport_pkgs_per_pallet, " "but can differ for partial or mixed pallets."), }, upload_hint="Excel/CSV with columns: `sku`, `pcs_per_transport_pkg`, " "`transport_pkgs_per_pallet`, `pcs_per_pallet`. Rows in the upload " "overwrite existing values; SKUs not in the upload are untouched.", key_prefix="logistics", extra_badges=_logistics_badges, ) _GRAMMAGE_RE = re.compile( r"(?:^|[\s\(\-])(\d+(?:[.,]\d+)?)\s*(kg|g|ml|l|oz|lbs?)\b", re.IGNORECASE, ) _UNIT_TO_GRAMS = { "g": 1.0, "kg": 1000.0, "ml": 1.0, "l": 1000.0, "oz": 28.35, "lb": 453.59, "lbs": 453.59, } _STOPWORDS = { "the", "and", "for", "with", "without", "pulver", "pulverr", "powder", "tablete", "kapsule", "kaps", "tabs", "caps", "hr", "en", "de", "sl", } def _parse_grammage(name): """Return grams (or ml treated as grams) from a product name, or None. Picks the largest sensible value β€” some names have '500 g kapsula 60 pcs' where the weight is the meaningful one.""" if not name or pd.isna(name): return None matches = _GRAMMAGE_RE.findall(str(name)) if not matches: return None candidates = [] for num, unit in matches: try: val = float(num.replace(",", ".")) except ValueError: continue unit_l = unit.lower().rstrip("s") mult = _UNIT_TO_GRAMS.get(unit_l) if mult is None: continue grams = val * mult if 1 <= grams <= 20000: # sanity filter candidates.append(grams) return max(candidates) if candidates else None def _tokenize_name(name): """Lowercase tokens β‰₯3 chars, digits stripped, stopwords removed.""" if not name or pd.isna(name): return set() raw = re.sub(r"[^\w\s]", " ", str(name).lower()) raw = re.sub(r"\d+", " ", raw) return {t for t in raw.split() if len(t) >= 3 and t not in _STOPWORDS} def impute_missing_costs(): """Suggest cost_price for SKUs where it's missing or 0. Strategy (tries in order, keeps first match with β‰₯3 neighbours): 1. Same sub_cat + grammage Β±25% 2. Same sub_cat + keyword overlap β‰₯2 tokens 3. Same sub_cat β€” median €/g Γ— target grams (if grammage known) 4. Same cat β€” median €/g Γ— target grams 5. Same cat β€” plain median cost_price (grammage unknown) Returns DataFrame [sku, name, category, sub_cat, grams, suggested_cost, method, neighbours, sample_skus].""" costs_path = DATA_DIR / "sku_costs.csv" if not costs_path.exists(): return pd.DataFrame() costs = pd.read_csv(costs_path) # Build unified SKU master: name + cat + sub_cat from whichever map has it frames = [] for fname, cols in [ ("sku_subcat_map.csv", ["sku", "name", "sub_cat", "grup"]), ("sku_category_map.csv", ["sku", "name", "cat"]), ("sku_plan_list.csv", ["sku", "name", "cat"]), ]: p = DATA_DIR / fname if not p.exists(): continue df = pd.read_csv(p) keep = [c for c in cols if c in df.columns] frames.append(df[keep]) if not frames: return pd.DataFrame() master = frames[0] for f in frames[1:]: master = master.merge(f, on="sku", how="outer", suffixes=("", "_dup")) for c in list(master.columns): if c.endswith("_dup"): base = c[:-4] master[base] = master[base].where( master[base].notna() & (master[base].astype(str) != ""), master[c], ) master = master.drop(columns=[c]) for col in ["name", "sub_cat", "cat", "grup"]: if col not in master.columns: master[col] = "" master = master.drop_duplicates(subset="sku", keep="first") # Decorate with grammage + tokens master["grams"] = master["name"].apply(_parse_grammage) master["tokens"] = master["name"].apply(_tokenize_name) # Split known vs missing costs_pos = costs[costs["cost_price"].fillna(0) > 0][["sku", "cost_price"]] known = master.merge(costs_pos, on="sku", how="inner") known["per_gram"] = known.apply( lambda r: r["cost_price"] / r["grams"] if r.get("grams") and r["grams"] > 0 else None, axis=1, ) missing_skus = set(master["sku"]) - set(costs_pos["sku"]) missing = master[master["sku"].isin(missing_skus)].copy() suggestions = [] for _, row in missing.iterrows(): sku = row["sku"] name = row.get("name") or "" sub = row.get("sub_cat") or "" cat = row.get("cat") or "" g = row.get("grams") if g is not None and pd.isna(g): g = None tokens = row.get("tokens") or set() suggested, method, neighbours, samples = None, None, 0, [] # 1. Same sub_cat + grammage Β±25 % if sub and g: pool = known[(known["sub_cat"] == sub) & known["grams"].between(g * 0.75, g * 1.25)] if len(pool) >= 3: suggested = float(pool["cost_price"].median()) method = "sub_cat + grammage" neighbours = len(pool) samples = pool["sku"].head(5).tolist() # 2. Same sub_cat + keyword overlap β‰₯2 if suggested is None and sub and tokens: pool = known[known["sub_cat"] == sub].copy() if len(pool): pool["overlap"] = pool["tokens"].apply(lambda t: len(t & tokens)) pool = pool[pool["overlap"] >= 2] if len(pool) >= 3: if g: pg = pool["per_gram"].dropna() if len(pg): suggested = float(pg.median()) * g method = "sub_cat + keywords (per-gram)" else: suggested = float(pool["cost_price"].median()) method = "sub_cat + keywords (flat)" else: suggested = float(pool["cost_price"].median()) method = "sub_cat + keywords (flat)" neighbours = len(pool) samples = pool.sort_values("overlap", ascending=False)["sku"].head(5).tolist() # 3. Same sub_cat β€” per-gram median if suggested is None and sub and g: pool = known[known["sub_cat"] == sub] pg = pool["per_gram"].dropna() if len(pg) >= 3: suggested = float(pg.median()) * g method = "sub_cat per-gram median" neighbours = len(pg) samples = pool["sku"].head(5).tolist() # 4. Same cat β€” per-gram if suggested is None and cat and g: pool = known[known["cat"] == cat] pg = pool["per_gram"].dropna() if len(pg) >= 3: suggested = float(pg.median()) * g method = "cat per-gram median" neighbours = len(pg) samples = pool["sku"].head(5).tolist() # 5. Same cat β€” flat median (grammage unknown, e.g. clothing) if suggested is None and cat: pool = known[known["cat"] == cat] if len(pool) >= 3: suggested = float(pool["cost_price"].median()) method = "cat flat median" neighbours = len(pool) samples = pool["sku"].head(5).tolist() if suggested is None or suggested <= 0: continue suggestions.append({ "sku": sku, "name": name, "category": cat, "sub_cat": sub, "grams": round(g, 1) if g else None, "suggested_cost": round(suggested, 2), "method": method, "neighbours": neighbours, "sample_skus": ", ".join(samples), }) return pd.DataFrame(suggestions) def page_supply_costs(): supply_reference_editor( title="πŸ’Ά Cost prices β€” nabavna cijena", intro="Per-SKU purchase cost (EUR) used to value overstock and cash-frozen capital. " "RUC (margin in EUR) is stored alongside for reference. " "Upload columns must already be renamed to `sku`, `cost_price`, `ruc` " "(original `Nabavna` β†’ `cost_price`).", storage_filename="sku_costs.csv", value_cols=["cost_price", "ruc"], column_config={ "cost_price": st.column_config.NumberColumn( "Cost (EUR)", min_value=0.0, step=0.01, format="%.2f"), "ruc": st.column_config.NumberColumn( "RUC (EUR)", min_value=0.0, step=0.01, format="%.2f"), }, upload_hint="Excel/CSV with columns: `sku`, `cost_price`, `ruc`. " "Rows overwrite existing values; SKUs not in the upload are untouched.", key_prefix="costs", ) st.divider() st.subheader("πŸ”Ž Impute missing prices") st.caption( "Scans for SKUs with cost_price = 0 or missing and suggests a price based on " "similar articles (sub-category, grammage, keywords). Review below before applying." ) if st.button("Run analysis", key="costs_impute_run"): with st.spinner("Analysing similar articles…"): st.session_state["_costs_impute_df"] = impute_missing_costs() sug = st.session_state.get("_costs_impute_df") if sug is None: return if len(sug) == 0: st.success("No missing prices found β€” or no similar articles available to impute from.") return st.write(f"**{len(sug)} SKUs** with suggested prices:") by_method = sug.groupby("method").size().to_dict() st.caption("By method: " + " Β· ".join(f"{m}: {n}" for m, n in by_method.items())) st.dataframe(sug, use_container_width=True, height=360) col1, col2 = st.columns(2) col1.download_button( "πŸ“₯ Download suggestions (Excel)", data=_df_to_excel_bytes(sug, "imputed_prices"), file_name="imputed_prices.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", key="costs_impute_xlsx", ) if col2.button("βœ… Apply suggestions to sku_costs.csv", key="costs_impute_apply"): costs_path = DATA_DIR / "sku_costs.csv" existing = pd.read_csv(costs_path) if costs_path.exists() else pd.DataFrame( columns=["sku", "cost_price", "ruc"]) existing.set_index("sku", inplace=True) applied = 0 for _, r in sug.iterrows(): sku = r["sku"] existing.loc[sku, "cost_price"] = r["suggested_cost"] if "ruc" not in existing.columns or pd.isna(existing.loc[sku, "ruc"]): existing.loc[sku, "ruc"] = 0.0 applied += 1 existing.reset_index().to_csv(costs_path, index=False) st.success(f"Applied {applied} imputed prices. Reload the page to verify.") clear_all_caches() st.session_state.pop("_costs_impute_df", None) def _df_to_excel_bytes(df, sheet_name="Sheet1"): import io buf = io.BytesIO() with pd.ExcelWriter(buf, engine="openpyxl") as writer: df.to_excel(writer, sheet_name=sheet_name, index=False) return buf.getvalue() def _model_only_fa_per_sku(): """Per-SKU forecast accuracy for Inventory health. Reuses the 'model_only' FA view (planner-owned channels stripped). Returns {sku: {"fa": float, "n_weeks": int}} with fa ∈ [0,∞) before clipping. """ try: sc = load_sales_data() except Exception: sc = None if sc is None: return {} fa_df = _build_fa_dataset("model_only", sc) if fa_df is None or len(fa_df) == 0: return {} per_sku = {} for sku, grp in fa_df.groupby("sku"): act = float(grp["actual"].sum()) err = float(grp["error"].sum()) if act <= 0: continue per_sku[sku] = { "fa": max(0.0, 1.0 - err / act), "n_weeks": int(len(grp)), } return per_sku @st.cache_data(ttl=60) def _load_inventory_inputs(): """Gather every file Inventory health needs. Returns dict of DataFrames/dicts.""" out = {} for name in ("stock.csv", "supply_master.csv", "incoming_supply.csv", "forecast_for_supply.csv", "sku_costs.csv", "sku_prices.csv", "sku_plan_list.csv", "sku_logistics.csv"): p = DATA_DIR / name out[name] = pd.read_csv(p) if p.exists() else None if out["supply_master.csv"] is not None and "supplier" in out["supply_master.csv"].columns: out["supply_master.csv"]["supplier"] = out["supply_master.csv"]["supplier"].map(remap_supplier) return out def page_supply_inventory_health(): st.title("πŸ₯ Inventory health") st.caption("Cashflow snapshot. Uses **on-hand only** against the **demand plan** " "(baseline + VP on-top + VP regular + MP on-top + MP regular, per-week). " "Overstock = cover > `1.5 Γ— lead_time + safety_weeks`. " "Stockout risk = on-hand below safety buffer. " "Incoming is shown for reference but does not affect the flag.") data = _load_inventory_inputs() plan = data["sku_plan_list.csv"] stock = data["stock.csv"] master = data["supply_master.csv"] incoming = data["incoming_supply.csv"] fc = data["forecast_for_supply.csv"] costs = data["sku_costs.csv"] sell = data["sku_prices.csv"] if plan is None or stock is None or master is None or fc is None: missing = [n for n in ("sku_plan_list.csv", "stock.csv", "supply_master.csv", "forecast_for_supply.csv") if data[n] is None] st.warning(f"Missing required files: {', '.join(missing)}. Upload them on the Supply β†’ Upload page first.") return using_cost = costs is not None if not using_cost: st.warning("⚠️ `sku_costs.csv` not found β€” valuing overstock with **sell price** " "(overstates cash impact). Upload cost prices on πŸ’Ά Cost prices page for accurate numbers.") # ---- Controls ---- c1, c2, c3 = st.columns(3) service_gold = c1.slider("Service level β€” Gold", 80, 99, 98, key="sl_gold") / 100 service_silver = c2.slider("Service level β€” Silver", 80, 99, 95, key="sl_silver") / 100 service_bronze = c3.slider("Service level β€” Bronze", 80, 99, 92, key="sl_bronze") / 100 # Z for one-sided normal from math import sqrt try: from scipy.stats import norm z_from_sl = lambda sl: float(norm.ppf(sl)) except Exception: # Lookup table fallback β€” scipy should be present (it's in requirements). _tbl = {0.80:0.84, 0.85:1.04, 0.90:1.28, 0.93:1.48, 0.95:1.65, 0.97:1.88, 0.99:2.33} z_from_sl = lambda sl: _tbl.get(round(sl, 2), 1.65) tier_z = { "01 GOLD": z_from_sl(service_gold), "02 SILVER": z_from_sl(service_silver), "03 BRONZE": z_from_sl(service_bronze), } # ---- FA-aware safety stock (phase 2) ---- with st.expander("🎯 Forecast-accuracy assumption (safety-stock driver)", expanded=False): st.caption( "Safety stock formula: `Z Γ— √LT Γ— √(σ² + (avg Γ— (1 βˆ’ FA))Β²)`. " "Per-SKU measured FA (Model-only, planner channels stripped) is used " "when enough history exists; otherwise falls back to global assumption. " "FA is clipped to [30%, 95%] to keep a single outlier week from dominating." ) fc1, fc2 = st.columns(2) fa_min_weeks = fc1.slider( "Weeks of history needed to trust per-SKU FA", 2, 12, 6, key="ih_fa_min_weeks", help="Below this, the SKU uses the global fallback instead of its own noisy measurement.", ) fa_global = fc2.slider( "Global fallback FA (%) β€” used when SKU has less than threshold weeks", 30, 95, 70, key="ih_fa_global", help="Your operational target. 70% matches the 1–2 month target communicated to the board.", ) / 100.0 # Precompute per-SKU FA map per_sku_fa = _model_only_fa_per_sku() def resolve_fa(sku): """Return (fa_clipped, source). source ∈ {'measured','global'}.""" info = per_sku_fa.get(sku) if info and info["n_weeks"] >= fa_min_weeks: return max(0.30, min(0.95, info["fa"])), "measured" return fa_global, "global" # ---- Build per-SKU metrics ---- plan_idx = (plan.drop_duplicates(subset=["sku"], keep="last").set_index("sku") if "sku" in plan.columns else plan) stock_map = dict(zip(stock["sku"], stock["on_hand"])) master_idx = master.drop_duplicates(subset=["sku"], keep="last").set_index("sku") cost_map = dict(zip(costs["sku"], costs["cost_price"])) if using_cost else {} sell_map = dict(zip(sell["sku"], sell["avg_sell_price"])) if sell is not None else {} # Incoming sum per SKU (all future weeks in the file). inc_map = {} if incoming is not None and len(incoming) > 0: inc_map = incoming.groupby("sku")["qty"].sum().to_dict() # Forecast: handle both long-form (sku, year, week, demand) and wide (CW-columns). # Build per-SKU sorted weekly demand arrays so we can sum over the required window # rather than relying on a flat average (promo peaks would otherwise get smoothed out). fc_cols = [c for c in fc.columns if str(c).startswith("CW")] fc_series = {} # sku -> np.array of demand per week, chronological if fc_cols: for _, r in fc.iterrows(): fc_series[r["sku"]] = np.array([float(r[c] or 0) for c in fc_cols], dtype=float) elif {"sku", "year", "week", "demand"}.issubset(fc.columns): for sku, grp in fc.sort_values(["year", "week"]).groupby("sku"): fc_series[sku] = grp["demand"].to_numpy(dtype=float) elif "total_fc" in fc.columns: # Legacy flat format: fall back to flat average over 13 weeks. for _, r in fc.iterrows(): fc_series[r["sku"]] = np.full(13, float(r["total_fc"]) / 13.0) else: st.error("forecast_for_supply.csv format not recognised β€” need either CW columns, " "(sku, year, week, demand), or `total_fc`.") return from math import ceil def demand_over(weeks_needed: float, series: np.ndarray) -> float: """Sum demand over the first `weeks_needed` weeks. Partial trailing week is prorated. If horizon exceeds the forecast, extrapolates with the horizon's average.""" if len(series) == 0 or weeks_needed <= 0: return 0.0 whole = int(weeks_needed) frac = weeks_needed - whole available = len(series) if whole >= available: avg = series.mean() if available > 0 else 0.0 return float(series.sum() + (weeks_needed - available) * avg) total = float(series[:whole].sum()) if frac > 0: total += frac * float(series[whole]) return total rows = [] for sku, series in fc_series.items(): fc_total = float(series.sum()) n_weeks = int(len(series)) on_hand = float(stock_map.get(sku, 0) or 0) incoming_qty = float(inc_map.get(sku, 0) or 0) if sku in master_idx.index: lt = float(master_idx.at[sku, "lead_time_weeks"] or 0) moq = float(master_idx.at[sku, "moq"] or 0) supplier = str(master_idx.at[sku, "supplier"] or "") else: lt, moq, supplier = 0.0, 0.0, "" if sku in plan_idx.index: tier = str(plan_idx.at[sku, "oznaka"]) if "oznaka" in plan_idx.columns else "" cat = str(plan_idx.at[sku, "cat"]) if "cat" in plan_idx.columns else "" name = str(plan_idx.at[sku, "name"]) if "name" in plan_idx.columns else "" cv = float(plan_idx.at[sku, "total_cv"]) if "total_cv" in plan_idx.columns and pd.notna(plan_idx.at[sku, "total_cv"]) else 0.5 else: tier, cat, name, cv = "", "", "", 0.5 avg_weekly = fc_total / n_weeks if n_weeks else 0 sigma_weekly = avg_weekly * cv z = tier_z.get(tier, z_from_sl(0.93)) fa_used, fa_source = resolve_fa(sku) # SS = Z Γ— √LT Γ— √(σ² + (avg Γ— (1 βˆ’ FA))Β²) forecast_err_sigma = avg_weekly * (1.0 - fa_used) combined_sigma = sqrt(sigma_weekly ** 2 + forecast_err_sigma ** 2) safety_stock = z * combined_sigma * (sqrt(lt) if lt > 0 else 0) # Cashflow snapshot using ACTUAL per-week demand from the plan # (includes baseline + VP on-top + VP regular + MP on-top + MP regular). # Overstock β†’ on_hand > demand over 1.5Γ— lead_time + safety_weeks. # Stockout risk β†’ on_hand already below the safety buffer. # (Lead-time coverage ignored β€” some incoming POs may not # yet be captured in incoming_supply.csv, so we don't # want to false-flag SKUs that actually have orders in flight.) # Balanced β†’ in between. safety_weeks = (safety_stock / avg_weekly) if avg_weekly > 0 else 0 max_cover_needed = 1.5 * lt + safety_weeks max_on_hand = demand_over(max_cover_needed, series) cover_now = (on_hand / avg_weekly) if avg_weekly > 0 else (float('inf') if on_hand > 0 else 0) excess_units = max(0.0, on_hand - max_on_hand) shortage_units = max(0.0, safety_stock - on_hand) unit_value = float(cost_map.get(sku, 0) or 0) valued_with = "cost" if unit_value == 0 and sell_map: unit_value = float(sell_map.get(sku, 0) or 0) valued_with = "sell" if excess_units > 0: status = "overstock" elif shortage_units > 0: status = "stockout_risk" else: status = "balanced" excess_eur = excess_units * unit_value rows.append({ "sku": sku, "name": name, "category": cat, "tier": tier, "supplier": supplier, "on_hand": int(on_hand), "incoming": int(incoming_qty), "avg_weekly_fc": round(avg_weekly, 1), "cv": round(cv, 2), "lt_weeks": lt, "moq": int(moq), "safety_stock": int(round(safety_stock)), "safety_weeks": round(safety_weeks, 1), "fa_used": round(fa_used * 100, 1), "fa_source": fa_source, "max_cover_needed": round(max_cover_needed, 1), "excess_units": int(round(excess_units)), "excess_eur": round(excess_eur, 2), "shortage_units": int(round(shortage_units)), "cover_now": round(cover_now, 1) if cover_now != float('inf') else None, "status": status, "_valued_with": valued_with, }) df = pd.DataFrame(rows) if df.empty: st.info("No SKUs with forecast data β€” run the forecast first.") return # ---- KPIs ---- k1, k2, k3, k4 = st.columns(4) total_overstock_eur = df["excess_eur"].sum() n_overstock = int((df["status"] == "overstock").sum()) n_short = int((df["status"] == "stockout_risk").sum()) n_balanced = int((df["status"] == "balanced").sum()) unit_label = "nabavna" if using_cost else "⚠️ sell (proxy)" k1.metric(f"€ frozen in overstock ({unit_label})", f"€{total_overstock_eur:,.0f}") k2.metric("Overstock SKUs", n_overstock) k3.metric("Stockout-risk SKUs", n_short) k4.metric("Balanced SKUs", n_balanced) # ---- Filters ---- st.divider() f1, f2, f3, f4 = st.columns(4) tier_opts = ["All"] + sorted(df["tier"].dropna().unique().tolist()) cat_opts = ["All"] + sorted([c for c in df["category"].dropna().unique() if c]) sup_opts = ["All"] + sorted([s for s in df["supplier"].dropna().unique() if s]) status_opts = ["All", "overstock", "stockout_risk", "balanced"] tier_sel = f1.selectbox("Tier", tier_opts, key="ih_tier") cat_sel = f2.selectbox("Category", cat_opts, key="ih_cat") sup_sel = f3.selectbox("Supplier", sup_opts, key="ih_sup") status_sel = f4.selectbox("Status", status_opts, index=1, key="ih_status") view = df.copy() if tier_sel != "All": view = view[view["tier"] == tier_sel] if cat_sel != "All": view = view[view["category"] == cat_sel] if sup_sel != "All": view = view[view["supplier"] == sup_sel] if status_sel != "All": view = view[view["status"] == status_sel] view = view.sort_values("excess_eur", ascending=False) # ---- Main table ---- n_measured = int((df["fa_source"] == "measured").sum()) n_total = len(df) st.subheader(f"{len(view)} SKUs") st.caption(f"FA source: **{n_measured} / {n_total}** SKUs using per-SKU measured FA " f"(β‰₯ {fa_min_weeks} weeks of history). " f"Rest fall back to global assumption of {int(fa_global*100)}%.") display_cols = ["sku", "name", "tier", "category", "supplier", "on_hand", "incoming", "avg_weekly_fc", "cv", "lt_weeks", "fa_used", "fa_source", "safety_weeks", "max_cover_needed", "cover_now", "excess_units", "excess_eur", "status"] st.dataframe( view[display_cols], use_container_width=True, hide_index=True, column_config={ "excess_eur": st.column_config.NumberColumn("Excess €", format="€%.0f"), "cover_now": st.column_config.NumberColumn("Cover now (wks)", format="%.1f", help="on_hand / avg_weekly demand plan β€” drives the status flag"), "avg_weekly_fc": st.column_config.NumberColumn("Avg wk demand", format="%.1f"), "fa_used": st.column_config.NumberColumn("FA used (%)", format="%.1f", help="Forecast accuracy applied in safety-stock formula, clipped to 30–95%"), "fa_source": st.column_config.TextColumn("FA source", help="'measured' = per-SKU from Model-only FA; 'global' = fallback"), "safety_weeks": st.column_config.NumberColumn("Safety (wks)", format="%.1f"), "max_cover_needed": st.column_config.NumberColumn("Max cover (wks)", format="%.1f", help="1.5 Γ— lead_time + safety β€” cover above this = overstock"), }, ) # ---- Supplier rollup ---- st.divider() st.subheader("Overstock € by supplier") sup_roll = (df[df["status"] == "overstock"] .groupby("supplier", dropna=False) .agg(skus=("sku", "count"), excess_units=("excess_units", "sum"), excess_eur=("excess_eur", "sum")) .sort_values("excess_eur", ascending=False) .reset_index()) if not sup_roll.empty: st.dataframe( sup_roll, use_container_width=True, hide_index=True, column_config={"excess_eur": st.column_config.NumberColumn("Excess €", format="€%.0f")}, ) else: st.caption("No overstock.") # ---- Download (XLSX) ---- st.divider() from io import BytesIO buf = BytesIO() with pd.ExcelWriter(buf, engine="openpyxl") as xlw: view[display_cols].to_excel(xlw, sheet_name="Inventory health", index=False) if not sup_roll.empty: sup_roll.to_excel(xlw, sheet_name="By supplier", index=False) st.download_button( "⬇️ Download inventory health (XLSX)", buf.getvalue(), file_name="inventory_health.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ) def page_supply_settings(): st.title("βš™οΈ Supply settings") p = st.session_state.get("sup_params", SUP_DEFAULTS.copy()) st.subheader("Safety stock parameters") fa = st.slider("Forecast accuracy (FA)", 0.30, 0.95, p["fa"], 0.05, help="Used as error proxy in the SS formula. " "Measure per-tier via the Demand β†’ Forecast accuracy page.") z = st.slider("Service level factor (Z)", 1.00, 2.50, p["z"], 0.05, help="1.28 = 90% Β· 1.65 = 95% Β· 2.05 = 98% Β· 2.33 = 99%") tc = st.slider("Target coverage after reorder (weeks)", 4, 16, int(p["target_coverage"]), 1) horizon = st.slider("Projection horizon (weeks)", 8, 26, int(p["horizon"]), 1) st.session_state["sup_params"] = { "fa": fa, "z": z, "target_coverage": tc, "horizon": horizon, } st.subheader("Formula") st.latex(r"SS = Z \cdot \bar{D} \cdot (1 - FA) \cdot \sqrt{LT}") st.latex(r"ROP = \bar{D} \cdot LT + SS") st.latex(r"Q_{order} = \max\bigl(MOQ,\ T_{cov} \cdot \bar{D} - S_{on\_hand} - S_{incoming\_in\_LT}\bigr)") st.caption("Planned next: per-tier FA (Gold/Silver/Bronze), per-SKU FA " "override, before/after backtest of ROP crossings vs. actual stockouts.") with st.sidebar: st.title("πŸ“Š Demand Planning Demo") st.caption("Demo build Β· synthetic data Β· v4.0") # ── MODULE SWITCHER ── module = st.radio( "Module", ["demand", "supply"], format_func=lambda m: "πŸ“Š Demand" if m == "demand" else "πŸ“¦ Supply", horizontal=True, key="module", ) cy, cw = get_current_cw() st.metric("Current week", f"CW{cw}", f"Forecast β†’ CW{cw+1}") st.divider() if module == "demand": # ── DEMAND: OPERATIONS ── with st.expander("βš™οΈ **Operations**", expanded=True): if st.button("πŸ“₯ Update sales", use_container_width=True, key="nav_update"): st.session_state["page"] = "update_sales" if st.button("πŸš€ Run forecast", use_container_width=True, key="nav_forecast"): st.session_state["page"] = "run_forecast" if st.button("πŸ“¨ KAM/CM inputs", use_container_width=True, key="nav_kam"): st.session_state["page"] = "kam_inputs" if st.button("πŸ“Έ Consensus plan", use_container_width=True, key="nav_consensus"): st.session_state["page"] = "consensus" if st.button("⬇️ Download", use_container_width=True, key="nav_download"): st.session_state["page"] = "download" # ── DEMAND: PROJECTIONS ── with st.expander("πŸ“ˆ **Projections**", expanded=True): if st.button("πŸ“‹ Demand planning", use_container_width=True, key="nav_demand"): st.session_state["page"] = "demand_planning" if st.button("πŸ’° Revenue", use_container_width=True, key="nav_revenue"): st.session_state["page"] = "revenue" if st.button("🎯 Forecast accuracy", use_container_width=True, key="nav_accuracy"): st.session_state["page"] = "accuracy" if st.button("πŸ‘οΈ Top 30 watchlist", use_container_width=True, key="nav_watchlist"): st.session_state["page"] = "watchlist" if st.button("πŸ“‘ S&OP meeting", use_container_width=True, key="nav_sop"): st.session_state["page"] = "sop_meeting" # ── DEMAND: SETTINGS ── with st.expander("πŸ”§ **Settings**", expanded=False): if st.button("πŸ”§ SKU list", use_container_width=True, key="nav_sku"): st.session_state["page"] = "sku_list" if st.button("πŸͺ VP input", use_container_width=True, key="nav_vp"): st.session_state["page"] = "vp_input" if st.button("πŸ›οΈ MP input", use_container_width=True, key="nav_mp"): st.session_state["page"] = "mp_input" if st.button("πŸ“… ERP promos", use_container_width=True, key="nav_erp"): st.session_state["page"] = "erp_promo" else: # module == "supply" # ── SUPPLY: WORKFLOW ── with st.expander("πŸ“¦ **Workflow**", expanded=True): if st.button("πŸ“Š Dashboard", use_container_width=True, key="snav_dash"): st.session_state["page"] = "supply_dashboard" if st.button("πŸ“ˆ Stock projection", use_container_width=True, key="snav_proj"): st.session_state["page"] = "supply_projection" if st.button("πŸ“‹ Coverage table", use_container_width=True, key="snav_cov"): st.session_state["page"] = "supply_coverage" if st.button("🚨 Reorder alerts", use_container_width=True, key="snav_alerts"): st.session_state["page"] = "supply_alerts" if st.button("πŸ₯ Inventory health", use_container_width=True, key="snav_health"): st.session_state["page"] = "supply_health" if st.button("✏️ Order entry", use_container_width=True, key="snav_orders"): st.session_state["page"] = "supply_orders" if st.button("πŸ“₯ Download supply plan", use_container_width=True, key="snav_dl"): st.session_state["page"] = "supply_download" # ── SUPPLY: SETTINGS ── with st.expander("πŸ”§ **Settings**", expanded=False): if st.button("πŸ“€ Upload data", use_container_width=True, key="snav_up"): st.session_state["page"] = "supply_upload" if st.button("πŸ“¦ MOQ", use_container_width=True, key="snav_moq"): st.session_state["page"] = "supply_moq" if st.button("🚚 Logistics", use_container_width=True, key="snav_logistics"): st.session_state["page"] = "supply_logistics" if st.button("πŸ’Ά Cost prices", use_container_width=True, key="snav_costs"): st.session_state["page"] = "supply_costs" if st.button("βš™οΈ Safety stock params", use_container_width=True, key="snav_settings"): st.session_state["page"] = "supply_settings" # ── SHARED: DATA FILES STATUS ── with st.expander("πŸ“ **Data files**", expanded=False): status = file_status() st.caption("**Demand**") for f in REQUIRED_CSV: st.caption(f"{'βœ…' if status[f] else '❌'} {f}") st.caption(f"{'βœ…' if status['planning_book'] else '❌'} Planning Book") plan_list = (DATA_DIR / "sku_plan_list.csv").exists() st.caption(f"{'βœ…' if plan_list else 'βšͺ'} sku_plan_list.csv {'(active)' if plan_list else '(will init from Book)'}") erp_ok = (DATA_DIR / "erp_promo_calendar.csv").exists() st.caption(f"{'βœ…' if erp_ok else 'βšͺ'} erp_promo_calendar.csv {'(active)' if erp_ok else '(optional)'}") st.caption(f"{'βœ…' if status['forecast'] else '❌'} Forecast output") n_snaps = len(list(CONSENSUS_DIR.glob("snapshot_*.json"))) st.caption(f"{'βœ…' if n_snaps else 'βšͺ'} {n_snaps} consensus snapshot(s)") st.caption("**Supply**") for label, fname in [("Stock", "stock.csv"), ("Incoming POs", "incoming_supply.csv"), ("Supply master", "supply_master.csv"), ("Forecast bridge", "forecast_for_supply.csv")]: ok = (DATA_DIR / fname).exists() st.caption(f"{'βœ…' if ok else 'βšͺ'} {fname}") # Default page β€” routed by module DEMAND_PAGES = { "dashboard", "update_sales", "run_forecast", "demand_planning", "vp_input", "mp_input", "revenue", "sku_list", "kam_inputs", "download", "accuracy", "watchlist", "consensus", "sop_meeting", "erp_promo", } SUPPLY_PAGES = { "supply_dashboard", "supply_projection", "supply_coverage", "supply_alerts", "supply_orders", "supply_download", "supply_upload", "supply_settings", "supply_moq", "supply_logistics", "supply_costs", "supply_health", } if "page" not in st.session_state: st.session_state["page"] = "demand_planning" # Switching modules routes to that module's default page if module == "demand" and st.session_state["page"] in SUPPLY_PAGES: st.session_state["page"] = "demand_planning" elif module == "supply" and st.session_state["page"] in DEMAND_PAGES: st.session_state["page"] = "supply_dashboard" # Route current_page = st.session_state["page"] # ── DEMAND routes ── if current_page == "dashboard": page_demand_planning() elif current_page == "update_sales": page_update_sales() elif current_page == "run_forecast": page_run_forecast() elif current_page == "demand_planning": page_demand_planning() elif current_page == "vp_input": page_input("Demand Input VP", "VP (wholesale)", "vp") elif current_page == "mp_input": page_input("Demand Input MP", "MP (marketing/retail)", "mp") elif current_page == "revenue": page_revenue() elif current_page == "sku_list": page_sku_management() elif current_page == "kam_inputs": page_kam_inputs() elif current_page == "download": page_download() elif current_page == "accuracy": page_forecast_accuracy() elif current_page == "watchlist": page_top30_watchlist() elif current_page == "consensus": page_consensus_plan() elif current_page == "sop_meeting": page_sop_meeting() elif current_page == "erp_promo": page_erp_promo() # ── SUPPLY routes ── elif current_page == "supply_dashboard": page_supply_dashboard() elif current_page == "supply_projection": page_supply_projection() elif current_page == "supply_coverage": page_supply_coverage() elif current_page == "supply_alerts": page_supply_alerts() elif current_page == "supply_orders": page_supply_order_entry() elif current_page == "supply_download": page_supply_download() elif current_page == "supply_upload": page_supply_upload() elif current_page == "supply_moq": page_supply_moq() elif current_page == "supply_logistics": page_supply_logistics() elif current_page == "supply_costs": page_supply_costs() elif current_page == "supply_health": page_supply_inventory_health() elif current_page == "supply_settings": page_supply_settings()