import pandas as pd import gspread import gradio as gr from oauth2client.service_account import ServiceAccountCredentials from datetime import datetime from math import radians, cos, sin, asin, sqrt # ------------------ AUTH ------------------ VALID_USERS = { "andrew@lortechnologies.com": "Pass.123", "phonnie@carfind.co.za": "Pass.123", "monique@carfind.co.za": "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 haversine(coord1, coord2): try: lon1, lat1 = map(radians, map(float, coord1.split(',')[::-1])) lon2, lat2 = map(radians, map(float, coord2.split(',')[::-1])) dlon = lon2 - lon1 dlat = lat2 - lat1 a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2 c = 2 * asin(sqrt(a)) return round(6371 * c, 2) except: return 0.0 # ------------------ FIELD SALES ------------------ def load_field_sales(): df = load_tab("Field Sales") if df.empty: return pd.DataFrame(columns=["Date", "Rep", "Order Value", "Order Received", "Location", "DateStr", "KM Travelled"]) df['Date'] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce') df = df.dropna(subset=["Date"]) df['DateStr'] = df['Date'].dt.date.astype(str) df["Order Value"] = pd.to_numeric(df.get("Order Value", 0), errors="coerce").fillna(0) df["KM Travelled"] = 0.0 for rep in df["Rep"].unique(): rep_df = df[df["Rep"] == rep].sort_values(by="Date") prev_coord = None for idx, row in rep_df.iterrows(): curr_coord = row.get("Location", "") if prev_coord: df.at[idx, "KM Travelled"] = haversine(prev_coord, curr_coord) prev_coord = curr_coord return df def get_field_summary(): df = load_field_sales() if df.empty: return pd.DataFrame([["No data available"]], columns=["Message"]) summary = df.groupby("Rep").agg({ "Order Value": "sum", "Order Received": lambda x: (x == "Yes").sum(), "KM Travelled": "sum" }).reset_index().rename(columns={ "Order Value": "Total Order Value", "Order Received": "Orders Received" }) return summary # ------------------ TELESALES ------------------ def get_telesales_summary(): df = load_tab("TeleSales") if df.empty or "Rep" not in df.columns: return pd.DataFrame([["No data available"]], columns=["Message"]) df["Date"] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce') df["DateStr"] = df["Date"].dt.date.astype(str) df["Order Value"] = pd.to_numeric(df.get("Order Value", 0), errors="coerce").fillna(0) summary = df.groupby("Rep").agg({ "Order Value": "sum", "Order Received": lambda x: (x == "Yes").sum() }).reset_index().rename(columns={ "Order Value": "Total Order Value", "Order Received": "Orders Received" }) return summary # ------------------ COMBINED ORDERS ------------------ def get_combined_orders(): fs = get_field_summary() ts = get_telesales_summary() fs["Source"] = "Field Sales" ts["Source"] = "TeleSales" combined = pd.concat([fs, ts], ignore_index=True) return combined[["Rep", "Orders Received", "Total Order Value", "Source"]].sort_values(by="Total Order Value", ascending=False) # ------------------ OEM VISITS ------------------ def get_oem_summary(): df = load_tab("OEM Visit") if df.empty or "Rep" not in df.columns: return pd.DataFrame([["No data available"]], columns=["Message"]) df["Date"] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce') df["DateStr"] = df["Date"].dt.date.astype(str) return df.groupby(["Rep", "DateStr"]).size().reset_index(name="OEM Visits") # ------------------ OTHER TABS ------------------ 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_escalations(): df = load_field_sales() 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"]) # ------------------ 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") # --- Summary Tab --- with gr.Tab("📊 Summary"): summary_table = gr.Dataframe(label="Combined Orders", value=get_combined_orders) # --- Field Sales Tab --- with gr.Tab("🛣️ Field Sales"): fs_table = gr.Dataframe(label="Field Sales Summary", value=get_field_summary) fs_raw = gr.Dataframe(label="Raw Field Sales", value=load_field_sales) # --- Telesales Tab --- with gr.Tab("📞 TeleSales"): ts_table = gr.Dataframe(label="TeleSales Summary", value=get_telesales_summary) # --- Orders Tab --- with gr.Tab("📦 Orders"): order_table = gr.Dataframe(label="All Orders Combined", value=get_combined_orders) # --- Escalations --- with gr.Tab("🚨 Escalations"): esc_table = gr.Dataframe(value=get_escalations, label="Second-hand Dealerships") esc_btn = gr.Button("🔄 Refresh") esc_btn.click(fn=get_escalations, outputs=esc_table) # --- OEM Visits --- with gr.Tab("🏭 OEM Visits"): oem_table = gr.Dataframe(value=get_oem_summary, label="OEM Visit Summary") oem_refresh = gr.Button("🔄 Refresh OEM") oem_refresh.click(fn=get_oem_summary, outputs=oem_table) # --- Requests --- 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) # --- Dealership Listings --- 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) # --- Users --- 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()