Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| import gspread | |
| import gradio as gr | |
| import plotly.express as px | |
| from oauth2client.service_account import ServiceAccountCredentials | |
| # ------------------ AUTH ------------------ | |
| VALID_USERS = { | |
| "[email protected]": "Pass.123", | |
| "[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("bid4carsappsheetdash.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', | |
| 'Interaction Type': '📞 Interaction', | |
| 'Product': '💼 Product Type' | |
| }) | |
| total_time = round(table['⏱️ Time Spent'].sum(), 2) | |
| summary_row = pd.DataFrame([{ | |
| 'Visit Order': '', | |
| '🧭 Dealer': f"🧮 Total Time: {total_time} min", | |
| '🕒 Time': '', | |
| '⏱️ Time Spent': '', | |
| '📞 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="Select Date (YYYY-MM-DD)", placeholder="2025-05-27", type="text") | |
| 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(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}_{report_date}.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() | |