import gradio as gr import pandas as pd import gspread from oauth2client.service_account import ServiceAccountCredentials from datetime import datetime, timedelta # -------------------- AUTH -------------------- scope = [ "https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive" ] creds = ServiceAccountCredentials.from_json_keyfile_name( "deep-mile-461309-t8-0e90103411e0.json", scope ) client = gspread.authorize(creds) sheet_url = "https://docs.google.com/spreadsheets/d/1if4KoVQvw5ZbhknfdZbzMkcTiPfsD6bz9V3a1th-bwQ" # -------------------- UTILS -------------------- def normalize_header(raw_header): # strip and titleize return [h.strip().title() for h in raw_header] def load_sheet(sheet_name: str) -> pd.DataFrame: ws = client.open_by_url(sheet_url).worksheet(sheet_name) all_vals = ws.get_all_values() if not all_vals or len(all_vals) < 2: return pd.DataFrame() header = normalize_header(all_vals[0]) rows = all_vals[1:] df = pd.DataFrame(rows, columns=header) return df def get_current_week_range(): today = datetime.now() start = today - timedelta(days=today.weekday()) end = start + timedelta(days=6) return start.date(), end.date() # -------------------- CALLS -------------------- def get_calls(rep=None): df = load_sheet("Calls") if "Call Date" not in df: return pd.DataFrame([{"Error": "Missing 'Call Date' column"}]) df["Call Date"] = pd.to_datetime(df["Call Date"], errors="coerce").dt.date start, end = get_current_week_range() filtered = df[(df["Call Date"] >= start) & (df["Call Date"] <= end)] if rep: filtered = filtered[filtered["Rep"] == rep] return filtered def search_calls_by_date(y, m, d, rep): df = load_sheet("Calls") if "Call Date" not in df: return pd.DataFrame([{"Error": "Missing 'Call Date' column"}]) try: target = datetime(int(y), int(m), int(d)).date() except: return pd.DataFrame([{"Error": "Invalid date input"}]) df["Call Date"] = pd.to_datetime(df["Call Date"], errors="coerce").dt.date filtered = df[df["Call Date"] == target] if rep: filtered = filtered[filtered["Rep"] == rep] return filtered # -------------------- APPOINTMENTS -------------------- def appointments_detail(rep=None): df = load_sheet("Appointments") if "Appointment Date" not in df: return pd.DataFrame([{"Error": "Missing 'Appointment Date' column"}]) df["Appointment Date"] = pd.to_datetime(df["Appointment Date"], errors="coerce").dt.date start, end = get_current_week_range() filtered = df[(df["Appointment Date"] >= start) & (df["Appointment Date"] <= end)] if rep: filtered = filtered[filtered["Rep"] == rep] return filtered def appointments_summary(rep=None): det = appointments_detail(rep) if "Error" in det.columns: return det return det.groupby("Rep") \ .size() \ .reset_index(name="Appointment Count") def search_appointments_by_date(y, m, d, rep): df = load_sheet("Appointments") if "Appointment Date" not in df: return pd.DataFrame([{"Error": "Missing 'Appointment Date' column"}]) try: target = datetime(int(y), int(m), int(d)).date() except: return pd.DataFrame([{"Error": "Invalid date input"}]) df["Appointment Date"] = pd.to_datetime(df["Appointment Date"], errors="coerce").dt.date filtered = df[df["Appointment Date"] == target] if rep: filtered = filtered[filtered["Rep"] == rep] return filtered # -------------------- LEADS -------------------- def get_leads_detail(): df = load_sheet("AllocatedLeads") if "Assigned Rep" not in df or "Company Name" not in df: return pd.DataFrame([{"Error": "Missing 'Assigned Rep' or 'Company Name' column"}]) return df def get_leads_summary(): df = get_leads_detail() if "Error" in df.columns: return df return df.groupby("Assigned Rep") \ .size() \ .reset_index(name="Leads Count") # -------------------- INSIGHTS -------------------- def compute_insights(): calls = get_calls() appt = appointments_detail() leads = get_leads_detail() def top(df, col): return df[col].value_counts().idxmax() if not df.empty else "N/A" return pd.DataFrame([ {"Metric": "Most Calls This Week", "Rep": top(calls, "Rep")}, {"Metric": "Most Appointments This Week", "Rep": top(appt, "Rep")}, {"Metric": "Most Leads Allocated", "Rep": top(leads, "Assigned Rep")}, ]) # -------------------- DROPDOWN OPTIONS -------------------- def rep_options(sheet_name, rep_col): df = load_sheet(sheet_name) return sorted(df[rep_col].dropna().unique().tolist()) if rep_col in df.columns else [] # -------------------- UI LAYOUT -------------------- with gr.Blocks(title="Graffiti Admin Dashboard") as app: gr.Markdown("# 📆 Graffiti Admin Dashboard") # Calls Report with gr.Tab("Calls Report"): rep_calls = gr.Dropdown("Optional Rep Filter", choices=rep_options("Calls", "Rep"), allow_custom_value=True) calls_btn = gr.Button("Load Current Week Calls") calls_table = gr.Dataframe() calls_btn.click(fn=get_calls, inputs=rep_calls, outputs=calls_table) gr.Markdown("### 🔍 Search Calls by Specific Date") y1, m1, d1 = gr.Textbox("Year"), gr.Textbox("Month"), gr.Textbox("Day") rep1 = gr.Dropdown("Optional Rep Filter", choices=rep_options("Calls", "Rep"), allow_custom_value=True) calls_date_btn = gr.Button("Search Calls by Date") calls_date_table = gr.Dataframe() calls_date_btn.click(fn=search_calls_by_date, inputs=[y1, m1, d1, rep1], outputs=calls_date_table) # Appointments Report with gr.Tab("Appointments Report"): rep_appt = gr.Dropdown("Optional Rep Filter", choices=rep_options("Appointments", "Rep"), allow_custom_value=True) load_btn = gr.Button("Load Current Week Appointments") appt_sum = gr.Dataframe(label="📊 Weekly Appointments Summary by Rep") appt_det = gr.Dataframe(label="🔎 Detailed Appointments") load_btn.click( fn=lambda rep: (appointments_summary(rep), appointments_detail(rep)), inputs=rep_appt, outputs=[appt_sum, appt_det] ) gr.Markdown("### 🔍 Search Appointments by Specific Date") y2, m2, d2 = gr.Textbox("Year"), gr.Textbox("Month"), gr.Textbox("Day") rep2 = gr.Dropdown("Optional Rep Filter", choices=rep_options("Appointments", "Rep"), allow_custom_value=True) date_btn = gr.Button("Search Appointments by Date") date_sum = gr.Dataframe(label="📊 Appointments Summary for Date by Rep") date_det = gr.Dataframe(label="🔎 Detailed Appointments") def by_date(y, m, d, rep): df = search_appointments_by_date(y, m, d, rep) if "Error" in df.columns: return df, df return ( df.groupby("Rep").size().reset_index(name="Appointment Count"), df ) date_btn.click(fn=by_date, inputs=[y2, m2, d2, rep2], outputs=[date_sum, date_det]) # Appointed Leads with gr.Tab("Appointed Leads"): leads_btn = gr.Button("View Appointed Leads") leads_sum = gr.Dataframe(label="📊 Leads Count by Rep") leads_det = gr.Dataframe(label="🔎 Detailed Leads") leads_btn.click( fn=lambda: (get_leads_summary(), get_leads_detail()), outputs=[leads_sum, leads_det] ) # Insights with gr.Tab("Insights"): insights_btn = gr.Button("Generate Insights") insights_tbl = gr.Dataframe() insights_btn.click(fn=compute_insights, outputs=insights_tbl) app.launch()