Spaces:
Sleeping
Sleeping
| import gradio as gr | |
| import pandas as pd | |
| import gspread | |
| from gspread.exceptions import WorksheetNotFound | |
| from oauth2client.service_account import ServiceAccountCredentials | |
| from datetime import datetime, timedelta | |
| from gspread_dataframe import set_with_dataframe | |
| # -------------------- CONFIG -------------------- | |
| SHEET_URL = "https://docs.google.com/spreadsheets/d/1if4KoVQvw5ZbhknfdZbzMkcTiPfsD6bz9V3a1th-bwQ" | |
| USER_SHEET_NAME = "Users" # <-- must match (or contain) your “Users” tab | |
| # -------------------- 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) | |
| # -------------------- FUZZY WORKSHEET LOOKUP -------------------- | |
| def open_ws_by_substring(substr: str): | |
| """ | |
| Try exact match, then fall back to the first worksheet | |
| whose title contains `substr` (case-insensitive). | |
| """ | |
| sh = client.open_by_url(SHEET_URL) | |
| try: | |
| return sh.worksheet(substr) | |
| except WorksheetNotFound: | |
| for ws in sh.worksheets(): | |
| if substr.lower() in ws.title.lower(): | |
| return ws | |
| raise WorksheetNotFound(f"No tab matching '{substr}'") | |
| # -------------------- SHEET UTILS -------------------- | |
| def normalize_columns(df: pd.DataFrame) -> pd.DataFrame: | |
| df.columns = df.columns.str.strip().str.title() | |
| return df | |
| def load_sheet(sheet_name: str) -> pd.DataFrame: | |
| """Return a DataFrame of the entire sheet, or an Error row.""" | |
| try: | |
| ws = open_ws_by_substring(sheet_name) | |
| df = pd.DataFrame(ws.get_all_records()) | |
| return normalize_columns(df) | |
| except Exception as e: | |
| return pd.DataFrame([{"Error": str(e)}]) | |
| def load_sheet_df(sheet_name: str) -> pd.DataFrame: | |
| """Like load_sheet, but lets WorksheetNotFound bubble up.""" | |
| ws = open_ws_by_substring(sheet_name) | |
| df = pd.DataFrame(ws.get_all_records()) | |
| return normalize_columns(df) | |
| # -------------------- DATE FILTER HELPERS -------------------- | |
| def get_current_week_range(): | |
| today = datetime.now().date() | |
| start = today - timedelta(days=today.weekday()) | |
| end = start + timedelta(days=6) | |
| return start, end | |
| def filter_week(df, date_col, rep_col=None, rep=None): | |
| df[date_col] = pd.to_datetime(df[date_col], errors="coerce").dt.date | |
| start, end = get_current_week_range() | |
| out = df[(df[date_col] >= start) & (df[date_col] <= end)] | |
| if rep: | |
| out = out[out[rep_col] == rep] | |
| return out | |
| def filter_date(df, date_col, rep_col, y, m, d, rep): | |
| try: | |
| target = datetime(int(y), int(m), int(d)).date() | |
| except: | |
| return pd.DataFrame([{"Error": "Invalid date"}]) | |
| df[date_col] = pd.to_datetime(df[date_col], errors="coerce").dt.date | |
| out = df[df[date_col] == target] | |
| if rep: | |
| out = out[out[rep_col] == rep] | |
| return out | |
| # -------------------- REPORT FUNCTIONS -------------------- | |
| def get_calls(rep=None): | |
| df = load_sheet("Calls") | |
| if "Call Date" not in df.columns: | |
| return df | |
| return filter_week(df, "Call Date", "Rep", rep) | |
| def search_calls_by_date(y, m, d, rep): | |
| df = load_sheet("Calls") | |
| if "Call Date" not in df.columns: | |
| return df | |
| return filter_date(df, "Call Date", "Rep", y, m, d, rep) | |
| def get_appointments(rep=None): | |
| df = load_sheet("Appointments") | |
| if "Appointment Date" not in df.columns: | |
| return df | |
| return filter_week(df, "Appointment Date", "Rep", rep) | |
| def search_appointments_by_date(y, m, d, rep): | |
| df = load_sheet("Appointments") | |
| if "Appointment Date" not in df.columns: | |
| return df | |
| return filter_date(df, "Appointment Date", "Rep", y, m, d, rep) | |
| def get_leads_detail(): | |
| return load_sheet("AllocatedLeads") | |
| 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") | |
| def compute_insights(): | |
| calls = get_calls() | |
| appts = get_appointments() | |
| leads = get_leads_detail() | |
| def top_rep(df, col): | |
| if "Error" in df.columns or df.empty or col not in df.columns: | |
| return "N/A" | |
| return df.groupby(col).size().idxmax() | |
| data = [ | |
| {"Metric": "Most Calls This Week", "Rep": top_rep(calls, "Rep")}, | |
| {"Metric": "Most Appointments This Week", "Rep": top_rep(appts, "Rep")}, | |
| {"Metric": "Most Leads Allocated", "Rep": top_rep(leads, "Assigned Rep")}, | |
| ] | |
| return pd.DataFrame(data) | |
| def rep_options(sheet, col): | |
| df = load_sheet(sheet) | |
| if col in df.columns: | |
| return sorted(df[col].dropna().unique().tolist()) | |
| return [] | |
| # -------------------- USER MANAGEMENT -------------------- | |
| def load_users() -> pd.DataFrame: | |
| cols = [ | |
| "ID","Email","Name","Business","Role", | |
| "Daily Phone Call Target","Daily Phone Appointment Target", | |
| "Daily Quote Number Target","Daily Quote Revenue Target", | |
| "Weekly Phone Call Target","Weekly Phone Appointment Target", | |
| "Weekly Quote Number Target","Weekly Quote Revenue Target", | |
| "Monthly Phone Call Target","Monthly Phone Appointment Target", | |
| "Monthly Quote Number Target","Monthly Quote Revenue Target", | |
| "Monthly Sales Revenue Target" | |
| ] | |
| try: | |
| return load_sheet_df(USER_SHEET_NAME) | |
| except WorksheetNotFound: | |
| return pd.DataFrame(columns=cols) | |
| def save_users(df: pd.DataFrame): | |
| ws = client.open_by_url(SHEET_URL).worksheet(USER_SHEET_NAME) | |
| ws.clear() | |
| set_with_dataframe(ws, df) | |
| return "✅ Users saved!" | |
| # -------------------- BUILD UI -------------------- | |
| 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(choices=rep_options("Calls","Rep"), | |
| label="Optional Rep Filter", | |
| allow_custom_value=True) | |
| calls_btn = gr.Button("Load Current Week Calls") | |
| with gr.Row(): | |
| with gr.Column(): | |
| calls_sum = gr.Dataframe(label="📊 Calls by Rep") | |
| with gr.Column(): | |
| calls_det = gr.Dataframe(label="🔎 Detailed Calls") | |
| calls_btn.click( | |
| fn=lambda r: ( | |
| get_calls(r).groupby("Rep") | |
| .size() | |
| .reset_index(name="Count"), | |
| get_calls(r) | |
| ), | |
| inputs=rep_calls, | |
| outputs=[calls_sum, calls_det] | |
| ) | |
| gr.Markdown("### 🔍 Search Calls by Specific Date") | |
| y1 = gr.Textbox(label="Year") | |
| m1 = gr.Textbox(label="Month") | |
| d1 = gr.Textbox(label="Day") | |
| rep1 = gr.Dropdown(choices=rep_options("Calls","Rep"), | |
| label="Optional Rep Filter", | |
| allow_custom_value=True) | |
| calls_date_btn = gr.Button("Search Calls by Date") | |
| with gr.Row(): | |
| with gr.Column(): | |
| calls_date_sum = gr.Dataframe(label="📊 Calls by Rep on Date") | |
| with gr.Column(): | |
| calls_date_det = gr.Dataframe(label="🔎 Detailed Calls on Date") | |
| calls_date_btn.click( | |
| fn=lambda y,m,d,r: ( | |
| search_calls_by_date(y,m,d,r) | |
| .groupby("Rep") | |
| .size() | |
| .reset_index(name="Count"), | |
| search_calls_by_date(y,m,d,r) | |
| ), | |
| inputs=[y1,m1,d1,rep1], | |
| outputs=[calls_date_sum, calls_date_det] | |
| ) | |
| # -- Appointments Report -- | |
| with gr.Tab("Appointments Report"): | |
| rep_appt = gr.Dropdown(choices=rep_options("Appointments","Rep"), | |
| label="Optional Rep Filter", | |
| allow_custom_value=True) | |
| appt_btn = gr.Button("Load Current Week Appointments") | |
| with gr.Row(): | |
| with gr.Column(): | |
| appt_sum = gr.Dataframe(label="📊 Weekly Appointments Summary by Rep") | |
| with gr.Column(): | |
| appt_det = gr.Dataframe(label="🔎 Detailed Appointments") | |
| appt_btn.click( | |
| fn=lambda r: ( | |
| get_appointments(r) | |
| .groupby("Rep") | |
| .size() | |
| .reset_index(name="Count"), | |
| get_appointments(r) | |
| ), | |
| inputs=rep_appt, | |
| outputs=[appt_sum, appt_det] | |
| ) | |
| gr.Markdown("### 🔍 Search Appointments by Specific Date") | |
| y2 = gr.Textbox(label="Year") | |
| m2 = gr.Textbox(label="Month") | |
| d2 = gr.Textbox(label="Day") | |
| rep2 = gr.Dropdown(choices=rep_options("Appointments","Rep"), | |
| label="Optional Rep Filter", | |
| allow_custom_value=True) | |
| appt_date_btn = gr.Button("Search Appointments by Date") | |
| with gr.Row(): | |
| with gr.Column(): | |
| appt_date_sum = gr.Dataframe(label="📊 Appts by Rep on Date") | |
| with gr.Column(): | |
| appt_date_det = gr.Dataframe(label="🔎 Detailed Appts on Date") | |
| appt_date_btn.click( | |
| fn=lambda y,m,d,r: ( | |
| search_appointments_by_date(y,m,d,r) | |
| .groupby("Rep") | |
| .size() | |
| .reset_index(name="Count"), | |
| search_appointments_by_date(y,m,d,r) | |
| ), | |
| inputs=[y2,m2,d2,rep2], | |
| outputs=[appt_date_sum, appt_date_det] | |
| ) | |
| # -- Appointed Leads -- | |
| with gr.Tab("Appointed Leads"): | |
| leads_btn = gr.Button("View Appointed Leads") | |
| with gr.Row(): | |
| with gr.Column(): | |
| leads_sum = gr.Dataframe(label="📊 Leads Count by Rep") | |
| with gr.Column(): | |
| 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) | |
| # -- User Management -- | |
| with gr.Tab("User Management"): | |
| gr.Markdown("### 🙍 Manage Users \nEdit the grid and click **Save Users**.") | |
| users_tbl = gr.Dataframe(value=load_users(), interactive=True) | |
| save_btn = gr.Button("Save Users") | |
| save_msg = gr.Textbox(interactive=False) | |
| save_btn.click(fn=save_users, inputs=users_tbl, outputs=save_msg) | |
| app.launch() | |