Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| import gspread | |
| import gradio as gr | |
| from oauth2client.service_account import ServiceAccountCredentials | |
| from datetime import datetime | |
| # ------------------ AUTH ------------------ | |
| VALID_USERS = { | |
| "[email protected]": "Pass.123", | |
| "[email protected]": "Pass.123", | |
| "[email protected]": "Pass.123" | |
| } | |
| # ------------------ GOOGLE SHEET SETUP ------------------ | |
| scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"] | |
| creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope) | |
| client = gspread.authorize(creds) | |
| sheet_file = client.open("userAccess") | |
| # ------------------ HELPERS ------------------ | |
| def load_tab(sheet_name): | |
| try: | |
| df = pd.DataFrame(sheet_file.worksheet(sheet_name).get_all_records()) | |
| return df | |
| except: | |
| return pd.DataFrame([["⚠️ Could not load sheet."]], columns=["Error"]) | |
| def get_combined_orders(date_str): | |
| df_field = load_tab("Field Sales") | |
| df_ts = load_tab("TeleSales") | |
| combined = [] | |
| if not df_field.empty: | |
| df_field['Date'] = pd.to_datetime(df_field['Date'], errors='coerce') | |
| df_field['DateStr'] = df_field['Date'].dt.date.astype(str) | |
| df_field = df_field[df_field['DateStr'] == date_str.strip()] | |
| df_field['Order Value'] = pd.to_numeric(df_field['Order Value'], errors='coerce').fillna(0) | |
| df_field_orders = df_field.groupby("Rep").agg({ | |
| "Order Received": lambda x: (x == "Yes").sum(), | |
| "Order Value": "sum" | |
| }).reset_index().rename(columns={ | |
| "Order Received": "Orders Received", | |
| "Order Value": "Total Order Value" | |
| }) | |
| df_field_orders["Source"] = "Field Sales" | |
| combined.append(df_field_orders) | |
| if not df_ts.empty: | |
| df_ts['Date'] = pd.to_datetime(df_ts['Date'], errors='coerce') | |
| df_ts['DateStr'] = df_ts['Date'].dt.date.astype(str) | |
| df_ts = df_ts[df_ts['DateStr'] == date_str.strip()] | |
| df_ts['Order Value'] = pd.to_numeric(df_ts['Order Value'], errors='coerce').fillna(0) | |
| df_ts_orders = df_ts.groupby("Rep").agg({ | |
| "Order Received": lambda x: (x == "Yes").sum(), | |
| "Order Value": "sum" | |
| }).reset_index().rename(columns={ | |
| "Order Received": "Orders Received", | |
| "Order Value": "Total Order Value" | |
| }) | |
| df_ts_orders["Source"] = "TeleSales" | |
| combined.append(df_ts_orders) | |
| if combined: | |
| return pd.concat(combined, ignore_index=True) | |
| else: | |
| return pd.DataFrame([["No orders on this date"]], columns=["Message"]) | |
| def get_requests(): | |
| df = load_tab("Customer Requests") | |
| return df if not df.empty else pd.DataFrame([["No requests yet."]], columns=["Message"]) | |
| def get_listings(): | |
| df = load_tab("CustomerListings") | |
| return df if not df.empty else pd.DataFrame([["No listings found."]], columns=["Message"]) | |
| def get_users(): | |
| df = load_tab("Users") | |
| return df if not df.empty else pd.DataFrame([["No users configured."]], columns=["Message"]) | |
| def get_telesales_summary(): | |
| df = load_tab("TeleSales") | |
| if df.empty or "Rep" not in df.columns: | |
| return pd.DataFrame([["No Telesales data available"]], columns=["Message"]) | |
| return df.groupby("Rep").size().reset_index(name="Total Calls Made") | |
| def get_oem_summary(): | |
| df = load_tab("OEM Visit") | |
| if df.empty or "Rep" not in df.columns: | |
| return pd.DataFrame([["No OEM data available"]], columns=["Message"]) | |
| return df.groupby("Rep").size().reset_index(name="Total OEM Visits") | |
| def get_escalations(): | |
| df = load_tab("Field Sales") | |
| if df.empty: | |
| return pd.DataFrame([["No data in Field Sales"]], columns=["Message"]) | |
| col = "Customer Type & Status" | |
| if col in df.columns: | |
| flagged = df[df[col].str.contains("Second", na=False)] | |
| return flagged if not flagged.empty else pd.DataFrame([["No second-hand dealerships flagged."]], columns=["Message"]) | |
| else: | |
| return pd.DataFrame([["⚠️ Column 'Customer Type & Status' not found."]], columns=["Message"]) | |
| def load_field_sales(): | |
| df = load_tab("Field Sales") | |
| if df.empty: | |
| return pd.DataFrame() | |
| df["Date"] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce') | |
| df["DateStr"] = df["Date"].dt.date.astype(str) | |
| return df | |
| # ------------------ GRADIO APP ------------------ | |
| with gr.Blocks() as app: | |
| with gr.Row(): | |
| with gr.Column(visible=True) as login_ui: | |
| gr.Markdown("## 🔐 Login Required") | |
| email = gr.Textbox(label="Email") | |
| password = gr.Textbox(label="Password", type="password") | |
| login_btn = gr.Button("Login") | |
| login_msg = gr.Markdown("") | |
| with gr.Column(visible=False) as main_ui: | |
| gr.Markdown("## 🗂️ CarMat Dashboard") | |
| df_initial = load_field_sales() | |
| unique_dates = sorted(df_initial["DateStr"].unique(), reverse=True) if not df_initial.empty else [] | |
| # --- Tabs --- | |
| with gr.Tab("📊 Summary"): | |
| gr.Markdown("Summary content coming soon...") | |
| with gr.Tab("📂 Field Sales"): | |
| field_df = gr.Dataframe(value=load_field_sales, label="📂 Field Sales Records", interactive=False) | |
| field_btn = gr.Button("🔄 Refresh Field Sales") | |
| field_btn.click(fn=load_field_sales, outputs=field_df) | |
| with gr.Tab("📞 TeleSales"): | |
| ts_table = gr.Dataframe(value=get_telesales_summary, label="📞 TeleSales Summary") | |
| ts_refresh = gr.Button("🔄 Refresh") | |
| ts_refresh.click(fn=get_telesales_summary, outputs=ts_table) | |
| with gr.Tab("📦 Orders Summary"): | |
| order_date = gr.Dropdown(label="Select Date", choices=unique_dates, interactive=True) | |
| order_table = gr.Dataframe(label="🧾 Combined Order Summary") | |
| order_date.change(fn=get_combined_orders, inputs=order_date, outputs=order_table) | |
| with gr.Tab("🚨 Escalations"): | |
| esc_table = gr.Dataframe(value=get_escalations, label="🚨 Used Dealership Escalations") | |
| esc_btn = gr.Button("🔄 Refresh Escalations") | |
| esc_btn.click(fn=get_escalations, outputs=esc_table) | |
| with gr.Tab("🏭 OEM Visits"): | |
| oem_table = gr.Dataframe(value=get_oem_summary, label="🏭 OEM Visit Summary") | |
| oem_refresh = gr.Button("🔄 Refresh") | |
| oem_refresh.click(fn=get_oem_summary, outputs=oem_table) | |
| with gr.Tab("📬 Customer Requests"): | |
| req_table = gr.Dataframe(value=get_requests, label="📬 Customer Requests", interactive=False) | |
| req_refresh = gr.Button("🔄 Refresh Requests") | |
| req_refresh.click(fn=get_requests, outputs=req_table) | |
| with gr.Tab("📋 Dealership Directory"): | |
| listings_table = gr.Dataframe(value=get_listings, label="📋 Customer Listings") | |
| listings_refresh = gr.Button("🔄 Refresh Listings") | |
| listings_refresh.click(fn=get_listings, outputs=listings_table) | |
| with gr.Tab("👤 Users"): | |
| users_table = gr.Dataframe(value=get_users, label="👥 Users") | |
| users_refresh = gr.Button("🔄 Refresh Users") | |
| users_refresh.click(fn=get_users, outputs=users_table) | |
| def do_login(user, pw): | |
| if VALID_USERS.get(user) == pw: | |
| return gr.update(visible=False), gr.update(visible=True), "" | |
| else: | |
| return gr.update(visible=True), gr.update(visible=False), "❌ Invalid login." | |
| login_btn.click(fn=do_login, inputs=[email, password], outputs=[login_ui, main_ui, login_msg]) | |
| app.launch() | |