import streamlit as st import pandas as pd import numpy as np from prophet import Prophet import plotly.graph_objects as go from datetime import timedelta, datetime import os import math from sqlalchemy import create_engine, text import urllib.parse from streamlit_autorefresh import st_autorefresh import gc # ===================================================== # 1. CONFIGURATION & CONNEXION # ===================================================== st.set_page_config(page_title="Master Planner - Perf & Plan", layout="wide") DB_USER = "balakibawi" DB_PASS = "M@tch47om_2026" DB_HOST = "10.228.11.110" DB_NAME = "wfm_reporting" @st.cache_resource def get_engine(): try: safe_password = urllib.parse.quote_plus(DB_PASS) return create_engine(f"mysql+pymysql://{DB_USER}:{safe_password}@{DB_HOST}/{DB_NAME}", connect_args={'connect_timeout': 5}) except: return None # ===================================================== # 2. FONCTIONS DE CALCUL (ERLANG & PRECISION) # ===================================================== def apply_business_rules(row, acts): h = row['ds'].hour + row['ds'].minute/60 d = row['ds'].weekday() if h < 7 or h >= 21: return 0 if "PDV" in acts and d == 6: return 0 return max(0, row['yhat']) def erlang_c_besoin(calls, aht, interval_sec=1800, service_level=0.8, target_time=20): calls = max(0, calls) if calls <= 0 or aht <= 0: return 0 intensity = (calls * aht) / interval_sec agents = math.ceil(intensity) + 1 def get_service_level(n, intensity, aht, target_time): try: rho = intensity / n if rho >= 1: return 0 c_part = math.exp(n * math.log(intensity) - (math.lgamma(n + 1) + math.log(1 - rho))) sum_inv = sum([math.exp(i * math.log(intensity) - math.lgamma(i + 1)) for i in range(n)]) prob_attente = c_part / (sum_inv + c_part) return 1 - (prob_attente * math.exp(-(n - intensity) * (target_time / aht))) except: return 0 while agents < 500: if get_service_level(agents, intensity, aht, target_time) >= service_level: break agents += 1 return agents def calculer_precision_performance(reel, prev): mask = (reel.notnull()) & (reel > 0) if not mask.any(): return 0.0 erreur = np.sum(np.abs(reel[mask].values - prev[mask].clip(lower=0).values)) somme = np.sum(reel[mask].values) return max(0, min(100, (1 - (erreur / somme)) * 100)) if somme > 0 else 0.0 # ===================================================== # 3. CHARGEMENT DES DONNÉES # ===================================================== @st.cache_data(ttl=300) def load_data_source(uploaded_file=None): if uploaded_file is not None: df = pd.read_csv(uploaded_file) else: path = "full_history.csv" if os.path.exists(path): df = pd.read_csv(path) else: return pd.DataFrame() df['ds'] = pd.to_datetime(df['ds']) all_times = pd.date_range(start=df['ds'].min(), end=df['ds'].max(), freq='30min') df_list = [] for act in df['activite'].unique(): temp = df[df['activite'] == act].set_index('ds').reindex(all_times).fillna(0).reset_index() temp['activite'] = act temp.rename(columns={'index': 'ds'}, inplace=True) df_list.append(temp) return pd.concat(df_list, ignore_index=True) # ===================================================== # 4. MOTEUR DE PRÉVISION # ===================================================== @st.cache_resource(ttl=3600) def train_and_forecast(_df, activities): if _df.empty or not activities: return pd.DataFrame() results = [] for act in activities: gc.collect() df_act = _df[_df['activite'] == act].tail(8000).copy() m_vol = Prophet(seasonality_mode='multiplicative', daily_seasonality=True, weekly_seasonality=True, uncertainty_samples=50) m_vol.add_country_holidays(country_name='FR') m_vol.fit(df_act[['ds', 'y']]) m_aht = Prophet(daily_seasonality=True, weekly_seasonality=True, uncertainty_samples=50) m_aht.fit(df_act[['ds', 'aht']].rename(columns={'aht': 'y'})) future = m_vol.make_future_dataframe(periods=48*14, freq="30min") res_vol = m_vol.predict(future)[['ds', 'yhat']] res_aht = m_aht.predict(future)[['ds', 'yhat']].rename(columns={'yhat': 'aht_hat'}) res_act = res_vol.merge(res_aht, on='ds') res_act['activite'] = act results.append(res_act) return pd.concat(results, ignore_index=True) if results else pd.DataFrame() # ===================================================== # 5. UI PRINCIPALE # ===================================================== st.sidebar.title("📊 Pilotage Perf & Plan") up_file = st.sidebar.file_uploader("Mettre à jour le CSV", type="csv") df_full = load_data_source(up_file) if df_full.empty: st.info("Veuillez uploader un fichier 'full_history.csv' pour commencer.") st.stop() mode = st.sidebar.selectbox("Vue", ["Rétrospective", "Planification Futur"]) all_acts = sorted(df_full['activite'].unique().tolist()) sel_act = st.sidebar.multiselect("Activités", options=all_acts, default=all_acts[:2]) # --- NOUVEAU : SIMULATEUR DE SCÉNARIO --- st.sidebar.markdown("---") st.sidebar.header("🧪 Simulateur d'Impact") var_vol = st.sidebar.slider("Variation Volume (%)", -30, 50, 0) var_aht = st.sidebar.slider("Variation DMT (%)", -20, 30, 0) if not sel_act: st.warning("Sélectionnez une activité.") st.stop() with st.spinner("Calcul des prévisions..."): fc_all = train_and_forecast(df_full[df_full['activite'].isin(sel_act)], sel_act) if not fc_all.empty: fc_all['yhat'] = fc_all.apply(lambda r: apply_business_rules(r, [r['activite']]), axis=1) fc_all['work_load_pred'] = fc_all['yhat'] * fc_all['aht_hat'] df_filtered = df_full[df_full['activite'].isin(sel_act)][['ds', 'activite', 'y']] hist = fc_all.merge(df_filtered, on=['ds', 'activite'], how='left') hist_agg = hist.groupby('ds').agg({'y': 'sum', 'yhat': 'sum', 'work_load_pred': 'sum'}).reset_index() hist_agg['aht_hat'] = (hist_agg['work_load_pred'] / hist_agg['yhat']).fillna(180) d_min, d_max = hist_agg['ds'].min().date(), hist_agg['ds'].max().date() sel_range = st.sidebar.date_input("Période", value=(d_min, d_max)) if len(sel_range) == 2: mask = (hist_agg['ds'].dt.date >= sel_range[0]) & (hist_agg['ds'].dt.date <= sel_range[1]) view = hist_agg[mask].copy() # --- APPLICATION SIMULATION --- view['yhat_sim'] = view['yhat'] * (1 + var_vol/100) view['aht_sim'] = view['aht_hat'] * (1 + var_aht/100) # Calcul besoins (Base vs Simulé) view['besoin_agents'] = view.apply(lambda r: erlang_c_besoin(r['yhat'], r['aht_hat']), axis=1) view['besoin_sim'] = view.apply(lambda r: erlang_c_besoin(r['yhat_sim'], r['aht_sim']), axis=1) st.title(f"🚀 Master Planner - {mode}") # Métriques c1, c2, c3, c4 = st.columns(4) c1.metric("🎯 Précision", f"{calculer_precision_performance(view['y'], view['yhat']):.1f}%") c2.metric("📈 Volume Prévu", f"{int(view['yhat_sim'].sum()):,}", delta=f"{var_vol}%" if var_vol!=0 else None) staff_base = math.ceil(view[view['besoin_agents']>0]['besoin_agents'].mean()) staff_sim = math.ceil(view[view['besoin_sim']>0]['besoin_sim'].mean()) c3.metric("👥 Staff Requis", f"{staff_sim} agents", delta=f"{staff_sim - staff_base} agents" if staff_sim != staff_base else None) dmt_moy = int(view['aht_sim'].mean()) c4.metric("⏱️ DMT (moy)", f"{dmt_moy}s", delta=f"{var_aht}%" if var_aht!=0 else None) # Graphique fig = go.Figure() if mode == "Rétrospective": fig.add_trace(go.Scatter(x=view['ds'], y=view['y'], name="RÉEL", line=dict(color="#1f77b4", width=3))) fig.add_trace(go.Scatter(x=view['ds'], y=view['yhat_sim'], name="PRÉVISION (Simulée)", line=dict(color="#ff7f0e", dash='dot'))) fig.update_layout(title="Courbe de Charge (Workload)", hovermode="x unified") st.plotly_chart(fig, use_container_width=True) # Tableau de bord Staffing with st.expander("📅 Détails de la planification"): st.dataframe(view[['ds', 'y', 'yhat_sim', 'aht_sim', 'besoin_sim']].rename(columns={ 'ds': 'Intervalle', 'y': 'Réel', 'yhat_sim': 'Prévu', 'aht_sim': 'DMT', 'besoin_sim': 'Agents Requis' }), use_container_width=True) st.download_button("📥 Exporter le plan de charge", view.to_csv(index=False), "planification.csv", "text/csv") # ===================================================== # 9. FOOTER # ===================================================== st.sidebar.markdown("---") st.sidebar.caption(f"Propulsé par Prophet & Erlang-C | MatchAtom 2026")