import pandas as pd import gspread import gradio as gr import plotly.express as px from oauth2client.service_account import ServiceAccountCredentials # ------------------ AUTH ------------------ VALID_USERS = { "andrew@lortechnologies.com": "Pass.123", "donovanm@bid4cars.co.za": "Pass.123", "grant@bid4cars.co.za": "Pass.123", "phonnie@c2group.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("bid4carsappsheetdash-05325ef42f70.json", scope) client = gspread.authorize(creds) sheet_url = "https://docs.google.com/spreadsheets/d/1eUXhcfWd3jtNmZC6U_Dr2F7obQcK81I4YrK-fKEbkyU" # ------------------ DATA REFRESH FUNCTION ------------------ def refresh_data(): sheet = client.open_by_url(sheet_url).worksheet("Calls2") data = sheet.get_all_records() df = pd.DataFrame(data) df['Timestamp'] = pd.to_datetime(df['Date'].astype(str) + " " + df['Time'].astype(str), errors='coerce') df['Date'] = pd.to_datetime(df['Date'], errors='coerce').dt.date.astype(str) df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S', errors='coerce').dt.time gps_split = df['GPS'].astype(str).str.split(',', expand=True) df['Latitude'] = pd.to_numeric(gps_split[0], errors='coerce') df['Longitude'] = pd.to_numeric(gps_split[1], errors='coerce') df = df.dropna(subset=['Date', 'Rep Name', 'Latitude', 'Longitude']) df = df[(df['Latitude'] != 0) & (df['Longitude'] != 0)] df = df.sort_values(by=['Rep Name', 'Timestamp']) df['Time Diff (min)'] = df.groupby(['Rep Name', 'Date'])['Timestamp'].diff().dt.total_seconds().div(60).fillna(0) df['Visit Order'] = df.groupby(['Rep Name', 'Date']).cumcount() + 1 return df # ------------------ SUMMARY GENERATION ------------------ def generate_summary(date_str): df = refresh_data() all_reps = sorted(df['Rep Name'].dropna().unique()) day_df = df[df['Date'] == date_str] active = day_df.groupby('Rep Name').size().reset_index(name='Total Visits') active_list = active['Rep Name'].tolist() inactive_list = [rep for rep in all_reps if rep not in active_list] inactive_df = pd.DataFrame({'Inactive Reps': inactive_list}) return active, inactive_df def get_reps(date_str): df = refresh_data() reps = df[df['Date'] == date_str]['Rep Name'].dropna().unique() return gr.update(choices=sorted(reps)) # ------------------ MAP & TABLE VIEW ------------------ def show_map(date_str, rep): df = refresh_data() subset = df[(df['Date'] == date_str) & (df['Rep Name'] == rep)] if subset.empty: return "No valid data", None subset = subset.sort_values(by='Timestamp').copy() subset['Visit Order'] = range(1, len(subset) + 1) center_lat = subset['Latitude'].mean() center_lon = subset['Longitude'].mean() fig = px.line_mapbox( subset, lat="Latitude", lon="Longitude", hover_name="Dealership", hover_data={"Time": True, "Time Diff (min)": True, "Visit Order": True}, height=700, zoom=13, center={"lat": center_lat, "lon": center_lon} ) scatter = px.scatter_mapbox( subset, lat="Latitude", lon="Longitude", color="Visit Order", hover_name="Dealership", hover_data=["Time", "Time Diff (min)"], color_continuous_scale="Bluered" ) for trace in scatter.data: fig.add_trace(trace) fig.add_trace(px.scatter_mapbox( pd.DataFrame([subset.iloc[0]]), lat="Latitude", lon="Longitude", text=["Start"], color_discrete_sequence=["green"]).data[0]) fig.add_trace(px.scatter_mapbox( pd.DataFrame([subset.iloc[-1]]), lat="Latitude", lon="Longitude", text=["End"], color_discrete_sequence=["red"]).data[0]) fig.update_layout(mapbox_style="open-street-map", title=f"{rep}'s Route on {date_str}") table = subset[[ 'Visit Order', 'Dealership', 'Time', 'Time Diff (min)', 'Interaction Type', 'Product' ]].rename(columns={ 'Dealership': 'Dealer', 'Time': 'Time', 'Time Diff (min)': 'Time Spent (min)', 'Interaction Type': 'Interaction', 'Product': 'Product Type' }) total_time = round(table['Time Spent (min)'].sum(), 2) summary_row = pd.DataFrame([{ 'Visit Order': '', 'Dealer': f"Total Time: {total_time} min", 'Time': '', 'Time Spent (min)': '', 'Interaction': '', 'Product Type': '' }]) table = pd.concat([table, summary_row], ignore_index=True) return table, fig # ------------------ GRADIO UI ------------------ 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("## 🗂️ Bid4Cars FieldApp Tracker") df_initial = refresh_data() unique_dates = sorted(df_initial['Date'].unique(), reverse=True) with gr.Tab("📊 Summary"): date_summary = gr.Dropdown(label="Select Date", choices=unique_dates) active_table = gr.Dataframe(label="✅ Active Reps (with total visits)") inactive_table = gr.Dataframe(label="⚠️ Inactive Reps") date_summary.change(fn=generate_summary, inputs=date_summary, outputs=[active_table, inactive_table]) with gr.Tab("👤 KAM's"): with gr.Row(): with gr.Column(scale=1): date_picker = gr.Dropdown(label="Select Date", choices=unique_dates) rep_picker = gr.Dropdown(label="Select Rep") btn = gr.Button("Show Route") with gr.Column(scale=2): table = gr.Dataframe(label="Call Table") map_plot = gr.Plot(label="Map") date_picker.change(fn=get_reps, inputs=date_picker, outputs=rep_picker) btn.click(fn=show_map, inputs=[date_picker, rep_picker], outputs=[table, map_plot]) with gr.Tab("📄 Reports"): with gr.Row(): report_type = gr.Radio(choices=["Daily", "Weekly", "Monthly"], label="Report Type", value="Daily") report_date = gr.Textbox(label="Enter Date (YYYY-MM-DD)", placeholder="2025-05-27") download_btn = gr.Button("📥 Download CSV") report_table = gr.Dataframe(label="📋 Report Summary") report_info = gr.Markdown() download_file = gr.File(label="📁 Download Link") def generate_report(report_type, report_date): df = refresh_data() try: date_obj = pd.to_datetime(str(report_date), errors='coerce') except: return pd.DataFrame(), "⚠️ Invalid date format.", None if pd.isnull(date_obj): return pd.DataFrame(), "⚠️ Date could not be parsed.", None if report_type == "Daily": title = f"📆 Report for {date_obj.strftime('%B %d, %Y')}" mask = df['Date'] == str(date_obj.date()) elif report_type == "Weekly": start = date_obj - pd.Timedelta(days=date_obj.weekday()) end = start + pd.Timedelta(days=6) title = f"📆 Week of {start.strftime('%b %d')} – {end.strftime('%b %d')}, {start.year}" mask = (pd.to_datetime(df['Date']) >= start) & (pd.to_datetime(df['Date']) <= end) elif report_type == "Monthly": title = f"📆 Report for {date_obj.strftime('%B %Y')}" mask = pd.to_datetime(df['Date']).dt.to_period("M") == pd.to_datetime(date_obj).to_period("M") filtered = df[mask] if filtered.empty: return pd.DataFrame(), "⚠️ No data found for that range.", None summary = filtered[[ 'Date', 'Rep Name', 'Dealership', 'Time', 'Interaction Type', 'Product', 'Time Diff (min)' ]].sort_values(by=["Rep Name", "Date"]) insights = f""" ### {title} ### 📊 Insights: - **Total Visits:** {len(filtered)} - **Unique Reps:** {filtered['Rep Name'].nunique()} - **Most Active Rep:** {filtered['Rep Name'].value_counts().idxmax()} - **Most Visited Dealership:** {filtered['Dealership'].value_counts().idxmax()} - **Avg Time Between Visits:** {round(filtered['Time Diff (min)'].mean(), 2)} min """ filename = f"Bid4Cars_Report_{report_type}_{date_obj.strftime('%Y-%m-%d')}.csv".replace(" ", "_") summary.to_csv(filename, index=False) return summary, insights, filename report_date.change(fn=generate_report, inputs=[report_type, report_date], outputs=[report_table, report_info, download_file]) report_type.change(fn=generate_report, inputs=[report_type, report_date], outputs=[report_table, report_info, download_file]) download_btn.click(fn=generate_report, inputs=[report_type, report_date], outputs=[report_table, report_info, download_file]) 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 email or password." login_btn.click(fn=do_login, inputs=[email, password], outputs=[login_ui, main_ui, login_msg]) app.launch()