""" Polleo Weekly Sales Update =========================== Accepts raw ERP exports or the Weekly_Sales_Update.xlsx template. Supports multiple files (e.g. one per country: CRO, SLO, AUT). Columns are auto-detected by name (order doesn't matter, extra columns ignored). Required: Datum, Artikal, Količina, Vrijednost (€), Tip dok. Optional: Naziv grupacije (category), Naziv (product name), Kategorija artikla (sub-cat) Usage: python update_sales.py [file1.xlsx] [file2.xlsx] [file3.xlsx] (or upload via Streamlit app → Update sales page) """ import pandas as pd, numpy as np, os, sys, glob from datetime import datetime from pathlib import Path from constants import PROMO_DISCOUNT_PCT_THRESHOLD, WS_SPIKE_MULT # Fix Windows console encoding for Croatian/Slovenian/German characters import io if sys.stdout.encoding and sys.stdout.encoding.lower() not in ('utf-8', 'utf8'): try: sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace') sys.stderr = io.TextIOWrapper(sys.stderr.buffer, encoding='utf-8', errors='replace') except Exception: pass VALID_TIPS = {'RCM','WSA','WSB','WSC','WSD','TRC','VPT','VPB','RAC','RIZ'} # Window length (in weeks) used to estimate the "current regular" price # for each SKU. Anchored to recent weeks so reprices don't lag for months # and high-promo-frequency SKUs don't drag the anchor below the real # shelf price. See compute_recent_normal_ppp() for the mode+max logic. PRICE_RECENT_WINDOW = 12 def channel(t): if t == 'RCM': return 'retail' if t in ('WSA','WSB','WSC','WSD'): return 'webshop' # RAC = B2B-ish web orders (gyms/resellers via the website), priced and # serviced as wholesale. KAMs started booking wholesale through RAC, so # it counts as wholesale (matches lookup_channel_map; changed 2026-06). if t in ('RAC','TRC','VPT','VPB','RIZ','RPE'): return 'wholesale' return 'other' def compute_recent_normal_ppp(df: pd.DataFrame, qty_col: str, ppp_col: str, window: int = PRICE_RECENT_WINDOW) -> dict: """Per SKU: 'current regular' price = mode of `avg_ppp_*` over the most recent `window` weeks where qty>0 and ppp>0. Ties resolved by the highest price (regular is always >= promo; reprices typically only go up). Returns {sku: float}.""" if df.empty or qty_col not in df.columns or ppp_col not in df.columns: return {} d = df[(df[qty_col] > 0) & (df[ppp_col] > 0)][['sku', 'year', 'week', ppp_col]].copy() if d.empty: return {} d['yw'] = d['year'].astype(int) * 100 + d['week'].astype(int) d = d.sort_values(['sku', 'yw']) d['rk'] = d.groupby('sku').cumcount(ascending=False) recent = d[d['rk'] < window] out: dict = {} for sku, grp in recent.groupby('sku'): prices = grp[ppp_col].round(2) counts = prices.value_counts() winners = counts[counts == counts.max()].index.tolist() out[sku] = float(max(winners)) return out def compute_recent_avg_sell(df: pd.DataFrame, window: int = PRICE_RECENT_WINDOW) -> dict: """Per SKU: blended retail+webshop revenue/qty over the most recent `window` weeks with any retail/web activity. Wholesale is always excluded — VP has its own pricing logic.""" if df.empty: return {} d = df[['sku', 'year', 'week', 'qty_retail', 'qty_webshop', 'avg_ppp_retail', 'avg_ppp_webshop']].copy() d['yw'] = d['year'].astype(int) * 100 + d['week'].astype(int) d['_act'] = (d['qty_retail'].fillna(0) + d['qty_webshop'].fillna(0)) > 0 active = d[d['_act']].sort_values(['sku', 'yw']) active['rk'] = active.groupby('sku').cumcount(ascending=False) recent = active[active['rk'] < window] out: dict = {} for sku, grp in recent.groupby('sku'): qr = grp['qty_retail'].fillna(0).sum() qw = grp['qty_webshop'].fillna(0).sum() rev_r = (grp['qty_retail'].fillna(0) * grp['avg_ppp_retail'].fillna(0)).sum() rev_w = (grp['qty_webshop'].fillna(0) * grp['avg_ppp_webshop'].fillna(0)).sum() tq = qr + qw out[sku] = float((rev_r + rev_w) / tq) if tq > 0 else 0.0 return out def read_one_file(filepath): """Read a single sales xlsx and return (df_filtered, col_map, raw_df). df_filtered has columns: date, sku, qty, value, tip (filtered to valid docs). col_map is the detected column mapping (may include 'cat', 'name'). raw_df is the original DataFrame (for category/subcat extraction). """ print(f'\n Reading {filepath}...') import openpyxl wb_check = openpyxl.load_workbook(filepath, read_only=True) sheet_names = wb_check.sheetnames wb_check.close() skip_sheets = {f'Sheet{i}' for i in range(1, 10)} | {f'sheet{i}' for i in range(1, 10)} data_sheets = [s for s in sheet_names if s not in skip_sheets] target_sheet = data_sheets[0] if data_sheets else sheet_names[0] if len(sheet_names) > 1: print(f' Sheets: {sheet_names} → using "{target_sheet}"') new = None for skip in range(0, 6): try: df = pd.read_excel(filepath, sheet_name=target_sheet, header=skip) cols_lower = [str(c).lower() for c in df.columns] if any('datum' in c or 'date' in c for c in cols_lower) or \ any('artikal' in c or 'artikl' in c or 'sku' in c for c in cols_lower): new = df if skip > 0: print(f' (skipped {skip} title row{"s" if skip>1 else ""})') break except: continue if new is None: new = pd.read_excel(filepath, sheet_name=target_sheet) col_map = {} # --- Pass 1: exact/specific matches first --- for col in new.columns: cl = str(col).lower().strip() if cl in ('datum', 'date') and 'date' not in col_map: col_map['date'] = col elif cl in ('artikal', 'artikl', 'sku') and 'sku' not in col_map: col_map['sku'] = col elif cl in ('količina', 'kolicina', 'qty', 'quantity') and 'qty' not in col_map: col_map['qty'] = col elif cl in ('vrijednost €', 'vrijednost', 'value') and 'value' not in col_map: col_map['value'] = col elif cl in ('tip dok.', 'tip dok', 'tip_dok', 'doc type') and 'tip' not in col_map: col_map['tip'] = col elif cl in ('naziv grupacije',) and 'cat' not in col_map: col_map['cat'] = col elif cl == 'naziv' and 'name' not in col_map: col_map['name'] = col elif 'ruc' not in col_map and '%' not in cl and (cl.startswith('ruc') or cl.startswith('marž') or cl.startswith('marza') or cl == 'margin'): col_map['ruc'] = col # --- Pass 2: fuzzy fallbacks for non-standard templates --- for col in new.columns: cl = str(col).lower().strip() if 'date' not in col_map and ('datum' in cl or 'date' in cl): col_map['date'] = col if 'sku' not in col_map and ('artikal' in cl or 'artikl' in cl or 'sku' in cl): col_map['sku'] = col if 'qty' not in col_map and ('količ' in cl or 'kolic' in cl): col_map['qty'] = col if 'value' not in col_map and 'nabavn' not in cl and ('vrijednost' in cl or 'vrednost' in cl or 'value' in cl): col_map['value'] = col if 'tip' not in col_map and ('tip dok' in cl or 'tip_dok' in cl): col_map['tip'] = col if 'cat' not in col_map and 'naziv grupac' in cl: col_map['cat'] = col if 'cat' not in col_map and 'grupacija' in cl and 'naziv' not in cl and 'artikla' not in cl: col_map['cat'] = col if 'ruc' not in col_map and '%' not in cl and ('ruc' in cl or 'marž' in cl or 'marz' in cl or 'margin' in cl or 'marge' in cl): col_map['ruc'] = col required = ['date','sku','qty','value','tip'] missing = [k for k in required if k not in col_map] if missing: raise ValueError(f'Missing columns in {os.path.basename(filepath)}: {missing}\n' f' Found: {list(new.columns)}\n' f' Need: Datum, Artikal, Količina, Vrijednost, Tip dok.') print(f' Mapped columns: { {k: col_map[k] for k in col_map} }') # Build output df: required + optional RUC selected = [col_map[k] for k in required] df = new[selected].copy() df.columns = ['date','sku','qty','value','tip'] # Add RUC if detected (expects EUR value, skips % columns) if 'ruc' in col_map: df['ruc'] = pd.to_numeric(new[col_map['ruc']], errors='coerce') print(f' RUC column found: "{col_map["ruc"]}"') print(f' RUC EUR sample: {df["ruc"].dropna().head(5).tolist()}') else: print(f' WARNING: No RUC column detected.') print(f' Available columns: {[str(c) for c in new.columns]}') print(f' Rows: {len(df):,}') df['date'] = pd.to_datetime(df['date']) df = df[df['tip'].isin(VALID_TIPS)] df = df[df['qty'] > 0] print(f' After filter (valid docs, no returns): {len(df):,}') if len(df) > 0: print(f' Date range: {df["date"].min().date()} to {df["date"].max().date()}') return df, col_map, new def find_update_files(): """Find all sales update files — from args, or auto-detect in current dir.""" files = [] # Check command-line arguments (multiple files supported) # --merge-wholesale is a mode flag consumed in run(); ignore it here if len(sys.argv) > 1: for arg in sys.argv[1:]: if arg.startswith('--'): continue if os.path.exists(arg) and arg.lower().endswith(('.xlsx', '.xls')): files.append(arg) if files: return files # Fallback: look for Weekly_Sales_Update*.xlsx pattern for pattern in ['Weekly_Sales_Update*.xlsx', 'weekly_sales_update*.xlsx', 'sales_update_*.xlsx', 'Sales_*.xlsx']: found = glob.glob(pattern) files.extend(found) if files: return list(set(files)) # Last resort: any xlsx that's not a system file xlsxs = glob.glob('*.xlsx') xlsxs = [x for x in xlsxs if not x.startswith('Polleo_Demand') and not x.startswith('sku_') and not x.startswith('VP_Input') and not x.startswith('MP_Input')] return xlsxs def run(): merge_wholesale = '--merge-wholesale' in sys.argv print(f'\n{"="*60}') print(f' POLLEO WEEKLY SALES UPDATE') if merge_wholesale: print(f' [MODE: wholesale add-on merge — preserves existing retail/webshop]') print(f' {datetime.now().strftime("%Y-%m-%d %H:%M")}') print(f'{"="*60}') update_files = find_update_files() if not update_files: print('\n ERROR: No sales update file found.') print(' Upload .xlsx file(s) with columns: Datum, Artikal, Količina, Vrijednost, Tip dok.') try: input('\n Press Enter...') except (EOFError, OSError): pass sys.exit(1) print(f'\n Found {len(update_files)} file(s): {update_files}') # ---- READ ALL FILES AND COMBINE ---- all_dfs = [] all_raw = [] # raw DataFrames for category/subcat extraction all_col_maps = [] # column mappings per file for fpath in update_files: try: df_f, cmap, raw = read_one_file(fpath) if len(df_f) > 0: all_dfs.append(df_f) all_raw.append(raw) all_col_maps.append(cmap) except Exception as e: print(f'\n WARNING: Skipping {fpath}: {e}') if not all_dfs: print('\n ERROR: No valid data found in any uploaded file.') try: input('\n Press Enter...') except (EOFError, OSError): pass sys.exit(1) df = pd.concat(all_dfs, ignore_index=True) print(f'\n Combined total: {len(df):,} rows from {len(all_dfs)} file(s)') print(f' Date range: {df["date"].min().date()} to {df["date"].max().date()}') # Merge-wholesale mode: keep only RIZ rows. Protects against mixed uploads # double-counting existing TRC/VPT/VPB already in sales_clean.csv. if merge_wholesale: n_before = len(df) df = df[df['tip'] == 'RIZ'] print(f' [merge-wholesale] Filtered to RIZ only: {n_before:,} → {len(df):,} rows') if len(df) == 0: print('\n ERROR: No RIZ rows found in upload. Aborting.') sys.exit(1) # Aggregate df['channel'] = df['tip'].map(channel) df['week'] = df['date'].dt.isocalendar().week.astype(int) df['year'] = df['date'].dt.year df['ppp'] = df['value'].abs() / df['qty'] new_agg = df.groupby(['sku','year','week','channel']).agg( qty=('qty','sum'), avg_ppp=('ppp','median'), txns=('qty','count') ).reset_index() # Pivot to one row per SKU-week piv = new_agg.pivot_table(index=['sku','year','week'], columns='channel', values=['qty','avg_ppp'], fill_value=0, aggfunc='sum').reset_index() piv.columns = ['_'.join(c).strip('_') if c[1] else c[0] for c in piv.columns] # Ensure all expected columns exist for ch in ['retail','webshop','wholesale']: for prefix in ['qty','avg_ppp']: col = f'{prefix}_{ch}' if col not in piv.columns: piv[col] = 0 piv['qty_total'] = piv['qty_retail'] + piv['qty_webshop'] + piv['qty_wholesale'] # RUC (margin) aggregation by channel — wholesale margin differs from retail has_ruc = 'ruc' in df.columns and df['ruc'].notna().any() if has_ruc: ruc_by_ch = df[df['ruc'].notna()].groupby(['sku','year','week','channel']).agg( ruc=('ruc','sum') ).reset_index() ruc_piv = ruc_by_ch.pivot_table(index=['sku','year','week'], columns='channel', values='ruc', fill_value=0, aggfunc='sum').reset_index() # Columns are flat strings after single-value pivot: 'sku','year','week','retail','webshop','wholesale' # Remove the channel name from column axis ruc_piv.columns.name = None for ch in ['retail','webshop','wholesale']: if ch not in ruc_piv.columns: ruc_piv[f'ruc_{ch}'] = 0 else: ruc_piv = ruc_piv.rename(columns={ch: f'ruc_{ch}'}) ruc_piv['ruc_total'] = ruc_piv['ruc_retail'] + ruc_piv['ruc_webshop'] + ruc_piv['ruc_wholesale'] piv = piv.merge(ruc_piv[['sku','year','week','ruc_retail','ruc_webshop','ruc_wholesale','ruc_total']], on=['sku','year','week'], how='left') for rc in ['ruc_retail','ruc_webshop','ruc_wholesale','ruc_total']: piv[rc] = piv[rc].fillna(0) print(f' RUC data: {ruc_by_ch["ruc"].sum():,.0f} EUR total margin across {len(ruc_by_ch):,} channel-rows') else: piv['ruc_retail'] = 0; piv['ruc_webshop'] = 0; piv['ruc_wholesale'] = 0; piv['ruc_total'] = 0 # Fix ppp (pivot summed them, need median) for ch in ['retail','webshop']: sub = new_agg[new_agg['channel']==ch].groupby(['sku','year','week'])['avg_ppp'].median().reset_index() sub.columns = ['sku','year','week',f'avg_ppp_{ch}'] piv = piv.drop(columns=[f'avg_ppp_{ch}'], errors='ignore') piv = piv.merge(sub, on=['sku','year','week'], how='left') piv[f'avg_ppp_{ch}'] = piv[f'avg_ppp_{ch}'].fillna(0) print(f' New weeks: {piv.groupby(["year","week"]).ngroups}') print(f' New SKU-weeks: {len(piv):,}') # ---- MERGE-WHOLESALE MODE ---- # For an add-on upload (e.g. RIZ documents only), don't replace rows. # Add qty_wholesale + ruc_wholesale to existing rows and recompute totals. if merge_wholesale: if not os.path.exists('sales_clean.csv'): print('\n ERROR: --merge-wholesale needs an existing sales_clean.csv.') sys.exit(1) existing = pd.read_csv('sales_clean.csv') print(f'\n Existing sales_clean.csv: {len(existing):,} rows') # Keep only wholesale contribution from new upload — ignore retail/webshop # in case the user accidentally uploaded a mixed file. ws_only = piv[['sku','year','week','qty_wholesale','ruc_wholesale']].copy() ws_only = ws_only[ws_only['qty_wholesale'] > 0] ws_only['year'] = ws_only['year'].astype(int) ws_only['week'] = ws_only['week'].astype(int) existing['year'] = existing['year'].astype(int) existing['week'] = existing['week'].astype(int) merged = existing.merge( ws_only.rename(columns={'qty_wholesale':'_add_qty_ws', 'ruc_wholesale':'_add_ruc_ws'}), on=['sku','year','week'], how='outer' ) # Fill NaN from outer join for c in ['qty_retail','qty_webshop','qty_wholesale','qty_total', 'ruc_retail','ruc_webshop','ruc_wholesale','ruc_total', '_add_qty_ws','_add_ruc_ws']: if c in merged.columns: merged[c] = merged[c].fillna(0) # Add wholesale contribution added_qty = merged['_add_qty_ws'].sum() added_ruc = merged['_add_ruc_ws'].sum() if '_add_ruc_ws' in merged.columns else 0 merged['qty_wholesale'] = merged['qty_wholesale'] + merged['_add_qty_ws'] merged['ruc_wholesale'] = merged['ruc_wholesale'] + merged['_add_ruc_ws'] merged['qty_total'] = merged['qty_retail'] + merged['qty_webshop'] + merged['qty_wholesale'] merged['ruc_total'] = merged['ruc_retail'] + merged['ruc_webshop'] + merged['ruc_wholesale'] merged = merged.drop(columns=['_add_qty_ws','_add_ruc_ws']) # Fill any other columns that came in as NaN for brand-new (sku,y,w) rows for c in merged.columns: if merged[c].isna().any(): if merged[c].dtype.kind in 'biufc': merged[c] = merged[c].fillna(0) else: merged[c] = merged[c].fillna('') merged.to_csv('sales_clean.csv', index=False) print(f'\n Merged: +{int(added_qty):,} units wholesale, +{added_ruc:,.0f} EUR ruc') print(f' Updated sales_clean.csv: {len(merged):,} rows') sys.exit(0) # ---- LOAD EXISTING sales_clean.csv ---- if os.path.exists('sales_clean.csv'): existing = pd.read_csv('sales_clean.csv') print(f'\n Existing sales_clean.csv: {len(existing):,} rows') # Remove any overlapping weeks (replace with new data) new_keys = set(zip(piv['sku'], piv['year'], piv['week'])) mask = existing.apply(lambda r: (r['sku'], int(r['year']), int(r['week'])) not in new_keys, axis=1) existing = existing[mask] print(f' After removing overlaps: {len(existing):,} rows') else: existing = pd.DataFrame() # ---- ADD PROMO/SPIKE FLAGS ---- # Placeholders only — the SKU-wide "current regular" price (and the # promo flags derived from it) get computed AFTER the existing+new # merge below, so historical rows also pick up the fresh anchor. for c in ('normal_ppp_retail', 'retail_discount_pct', 'is_retail_promo', 'normal_ppp_webshop', 'webshop_discount_pct', 'is_webshop_promo', 'is_wholesale_spike', 'is_any_promo', 'promo_pct_volume'): if c not in piv.columns: piv[c] = 0 # ---- COMBINE AND SAVE ---- # Match columns to existing keep_cols = ['sku','year','week','qty_retail','qty_webshop','qty_wholesale','qty_total', 'avg_ppp_retail','normal_ppp_retail','retail_discount_pct','is_retail_promo', 'avg_ppp_webshop','normal_ppp_webshop','webshop_discount_pct','is_webshop_promo', 'is_wholesale_spike','is_any_promo','promo_pct_volume', 'ruc_retail','ruc_webshop','ruc_wholesale','ruc_total'] for c in keep_cols: if c not in piv.columns: piv[c] = 0 # Ensure existing data has all columns too (old sales_clean.csv may lack ruc columns) if len(existing) > 0: for c in keep_cols: if c not in existing.columns: existing[c] = 0 final = pd.concat([existing[keep_cols] if len(existing) > 0 else pd.DataFrame(columns=keep_cols), piv[keep_cols]], ignore_index=True) final = final.sort_values(['sku','year','week']).reset_index(drop=True) # ---- RECOMPUTE is_wholesale_spike across ALL rows ---- # A spike is an ISOLATED singleton outlier: much larger than the SKU's # typical high weeks (p90) AND surrounded by normal weeks. Continuous # high-wholesale periods (3+ big weeks in a row) are genuine demand and # are NOT flagged. Rule: # qty > 3 × p90_nonzero AND qty > 1000 AND max(prev, next) < 0.5 × qty final = final.sort_values(['sku', 'year', 'week']).reset_index(drop=True) wh_pos = final[final['qty_wholesale'] > 0].groupby('sku')['qty_wholesale'] p90 = wh_pos.quantile(0.90) final['_p90'] = final['sku'].map(p90).fillna(0) final['_prev_wh'] = final.groupby('sku')['qty_wholesale'].shift(1).fillna(0) final['_next_wh'] = final.groupby('sku')['qty_wholesale'].shift(-1).fillna(0) final['_neighbor_max_wh'] = final[['_prev_wh', '_next_wh']].max(axis=1) big = (final['qty_wholesale'] > 3 * final['_p90']) & (final['qty_wholesale'] > 1000) isolated = final['_neighbor_max_wh'] < 0.5 * final['qty_wholesale'] final['is_wholesale_spike'] = (big & isolated).astype(int) final = final.drop(columns=['_p90', '_prev_wh', '_next_wh', '_neighbor_max_wh']) n_spike = int(final['is_wholesale_spike'].sum()) print(f' Wholesale spikes flagged (singleton outliers): {n_spike:,} week-SKUs') # ---- RECOMPUTE NORMAL PRICES + PROMO FLAGS (on full final) ---- # Anchor = mode of last 12w retail/web prices per SKU (max as tiebreak). # Applied to every row so historical promo flags stay consistent with # the current shelf price. See compute_recent_normal_ppp() docstring. retail_norm = compute_recent_normal_ppp(final, 'qty_retail', 'avg_ppp_retail') webshop_norm = compute_recent_normal_ppp(final, 'qty_webshop', 'avg_ppp_webshop') final['normal_ppp_retail'] = final['sku'].map(retail_norm).fillna(0) final['retail_discount_pct'] = np.where( (final['normal_ppp_retail'] > 0) & (final['avg_ppp_retail'] > 0), (1 - final['avg_ppp_retail'] / final['normal_ppp_retail']) * 100, 0) final['is_retail_promo'] = (final['retail_discount_pct'] > PROMO_DISCOUNT_PCT_THRESHOLD).astype(int) final['normal_ppp_webshop'] = final['sku'].map(webshop_norm).fillna(0) final['webshop_discount_pct'] = np.where( (final['normal_ppp_webshop'] > 0) & (final['avg_ppp_webshop'] > 0), (1 - final['avg_ppp_webshop'] / final['normal_ppp_webshop']) * 100, 0) final['is_webshop_promo'] = (final['webshop_discount_pct'] > PROMO_DISCOUNT_PCT_THRESHOLD).astype(int) final['is_any_promo'] = ((final['is_retail_promo'] == 1) | (final['is_webshop_promo'] == 1)).astype(int) n_rp = int(final['is_retail_promo'].sum()) n_wp = int(final['is_webshop_promo'].sum()) print(f' Retail promo weeks flagged: {n_rp:,} · webshop promo weeks: {n_wp:,}') final.to_csv('sales_clean.csv', index=False) print(f'\n Updated sales_clean.csv: {len(final):,} rows') # ---- UPDATE UPLIFT FILES ---- # Uplift is driven by the ERP promo calendar (ground truth), not the # statistical flags we just wrote. Those flags remain in sales_clean.csv # for diagnostics + engine fallback, but sku_uplift.csv / cat_uplift.csv # are authored by recalc_uplift_erp.py. if os.path.exists('erp_promo_calendar.csv'): print(f'\n Recomputing uplift from ERP promo calendar...') import recalc_uplift_erp recalc_uplift_erp.main() else: print('\n [skip] erp_promo_calendar.csv not found — uplift files not regenerated.') print(' Run build_erp_promo.py first, then recalc_uplift_erp.py.') # ---- BUILD DETAILED SALES (analytics-only) ---- # Preserves full ERP granularity (customer, store, document, daily detail) # in data/sales_detailed.csv. Does NOT feed the forecasting pipeline. # Append-safe — idempotent on re-runs. try: print(f'\n Building sales_detailed.csv (analytics granularity)...') import build_detailed_sales build_detailed_sales.main() except Exception as e: print(f' WARN: build_detailed_sales failed: {e}') # ---- BUILD PROMO PERFORMANCE (analytics-only) ---- # Per-campaign uplift / cannibalization / net_effect into # data/promo_performance.csv. Reads erp_promo_calendar + sales_clean. # Refresh weekly so the PromoTool > Promo Performance page stays current. try: print(f'\n Building promo_performance.csv...') import build_promo_performance build_promo_performance.main() except Exception as e: print(f' WARN: build_promo_performance failed: {e}') # ---- UPDATE PRICES ---- # Uses the same recent-window logic as sales_clean: regular price per # channel is the mode of the last 12 active weeks (max tiebreak), # blended avg_sell_price is revenue/qty over the same window (VP # always excluded). Reuses retail_norm/webshop_norm computed above. print(f'\n Updating prices...') avg_sell = compute_recent_avg_sell(final) price_stats = [] for sku, grp in final.groupby('sku'): qr = int(grp['qty_retail'].sum()) qw = int(grp['qty_webshop'].sum()) qwh = int(grp['qty_wholesale'].sum()) price_stats.append({ 'sku': sku, 'avg_sell_price': round(avg_sell.get(sku, 0.0), 2), 'normal_retail_ppp': round(retail_norm.get(sku, 0.0), 2), 'normal_webshop_ppp': round(webshop_norm.get(sku, 0.0), 2), 'qty_retail': qr, 'qty_webshop': qw, 'qty_wholesale': qwh, 'weeks_active': int(grp['week'].nunique()), }) pd.DataFrame(price_stats).to_csv('sku_prices.csv', index=False) print(f' sku_prices.csv: {len(price_stats)} SKUs') # ---- RECOMPUTE REALIZED RUC (per-unit avg from erp_transactions) ---- # erp_costs.ruc was historically a static ERP-provided number that # didn't reflect channel mix or actual discounts. We now derive it # directly from sales documents: SUM(ruc_eur) / SUM(quantity). # Matches the live SKU detail card's "RUC (realized)" figure. try: print(f'\n Recomputing RUC from erp_transactions...') import subprocess, sys as _sys subprocess.run( [_sys.executable, str(Path(__file__).parent / 'scripts' / 'recompute_ruc.py')], check=False, ) except Exception as e: print(f' WARN: recompute_ruc failed: {e}') # ---- UPDATE CATEGORY MAP (from all files that have categories) ---- CAT_MAP = {'OBLAČILA IN OBUTEV':'ODJEĆA I OBUĆA','BEKLEIDUNG UND SCHUHE':'ODJEĆA I OBUĆA', 'KAMPFSPORT AUSRÜSTUNG':'BORILAČKA OPREMA','BORILNA OPREMA':'BORILAČKA OPREMA', 'SPORTNAHRUNG':'SPORTSKA PREHRANA','ŠPORTNA PREHRANA':'SPORTSKA PREHRANA', 'BIO IN SUPERFOODS':'BIO I SUPERFOODS','BIO UND SUPERFOODS':'BIO I SUPERFOODS', 'PROTEINE':'PROTEINI','DRINKWARE IN DOM':'DRINKWARE I HOME','DRINKWARE UND HOME':'DRINKWARE I HOME', 'FITNESS EQUIPMENT':'FITNESS OPREMA','USLUGE':'OTHER','STORITVE':'OTHER','DIENSTLEISTUNGEN':'OTHER','PARIS':'OTHER'} all_new_cats = [] for raw_df, cmap in zip(all_raw, all_col_maps): if 'cat' not in cmap: continue cat_col = cmap['cat'] cat_cols = [cmap['sku'], cat_col] has_name = 'name' in cmap and cmap['name'] in raw_df.columns if has_name: cat_cols.insert(1, cmap['name']) nc = raw_df[cat_cols].dropna(subset=[cmap['sku'], cat_col]).drop_duplicates(cmap['sku'], keep='last') if has_name: nc.columns = ['sku', 'name', 'cat'] else: nc.columns = ['sku', 'cat'] nc['cat'] = nc['cat'].replace(CAT_MAP) all_new_cats.append(nc) if all_new_cats: new_cats = pd.concat(all_new_cats, ignore_index=True).drop_duplicates('sku', keep='last') if os.path.exists('sku_category_map.csv'): existing_cats = pd.read_csv('sku_category_map.csv') combined = pd.concat([existing_cats, new_cats]).drop_duplicates('sku', keep='last') else: combined = new_cats if 'name' not in combined.columns: combined['name'] = '' combined = combined[['sku','name','cat']] combined.to_csv('sku_category_map.csv', index=False) print(f' sku_category_map.csv: {len(combined)} SKUs') # ---- UPDATE SUB-CATEGORY MAP (from all files) ---- all_sub_maps = [] for raw_df, cmap in zip(all_raw, all_col_maps): _sku_col = cmap.get('sku', 'Artikal') _name_col = cmap.get('name', None) _subcat_col = None _grup_col = None for c in raw_df.columns: cl = str(c).lower().strip() if 'kategorija artikla' in cl or cl == 'podkat': _subcat_col = c if 'naziv grupacije' in cl: _grup_col = c if _name_col is None and cl == 'naziv': _name_col = c if not _subcat_col or _sku_col not in raw_df.columns: continue sub_cols = [_sku_col] sub_cols.append(_name_col if _name_col and _name_col in raw_df.columns else None) sub_cols.append(_subcat_col) sub_cols.append(_grup_col if _grup_col and _grup_col in raw_df.columns else None) sub_cols = [c for c in sub_cols if c is not None] sub_map = raw_df[sub_cols].dropna(subset=[_sku_col, _subcat_col]) rename = {_sku_col: 'sku'} if _name_col and _name_col in sub_cols: rename[_name_col] = 'name' if _subcat_col in sub_cols: rename[_subcat_col] = 'sub_cat' if _grup_col and _grup_col in sub_cols: rename[_grup_col] = 'grup' sub_map = sub_map.rename(columns=rename) sub_map = sub_map.drop_duplicates('sku', keep='last') for expected in ['sku','name','sub_cat','grup']: if expected not in sub_map.columns: sub_map[expected] = '' sub_map = sub_map[['sku','name','sub_cat','grup']] all_sub_maps.append(sub_map) if all_sub_maps: new_sub = pd.concat(all_sub_maps, ignore_index=True).drop_duplicates('sku', keep='last') if os.path.exists('sku_subcat_map.csv'): existing_sub = pd.read_csv('sku_subcat_map.csv') combined = pd.concat([existing_sub, new_sub]).drop_duplicates('sku', keep='last') else: combined = new_sub combined.to_csv('sku_subcat_map.csv', index=False) print(f' sku_subcat_map.csv: {len(combined)} SKUs') print(f'\n{"="*60}') print(f' ALL FILES UPDATED') print(f' Now run: python forecast_engine.py') print(f'{"="*60}\n') if __name__ == '__main__': try: run() except Exception as e: print(f'\n ERROR: {e}'); import traceback; traceback.print_exc() try: input('\n Press Enter to close...') except (EOFError, OSError): pass