Spaces:
Sleeping
Sleeping
| 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() | |