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_columns(df: pd.DataFrame) -> pd.DataFrame: | |
| df.columns = df.columns.str.strip().str.title() | |
| return df | |
| # Replace get_all_records() to avoid duplicate-header errors | |
| def load_sheet(sheet_name: str) -> pd.DataFrame: | |
| try: | |
| ws = client.open_by_url(sheet_url).worksheet(sheet_name) | |
| all_values = ws.get_all_values() | |
| if not all_values: | |
| return pd.DataFrame() | |
| headers = [h.strip().title() for h in all_values[0]] | |
| data = all_values[1:] | |
| return pd.DataFrame(data, columns=headers) | |
| except Exception as e: | |
| return pd.DataFrame([{"Error": str(e)}]) | |
| # date utilities | |
| def get_current_week_range(): | |
| today = datetime.now() | |
| start = today - timedelta(days=today.weekday()) | |
| end = start + timedelta(days=6) | |
| return start.date(), end.date() | |
| def filter_week(df: pd.DataFrame, date_col: str, rep_col: str = None, rep=None): | |
| if date_col not in df.columns: | |
| return df | |
| 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 and rep_col in df.columns: | |
| out = out[out[rep_col] == rep] | |
| return out | |
| def filter_date(df: pd.DataFrame, date_col: str, rep_col: str, y, m, d, rep): | |
| try: | |
| target = datetime(int(y), int(m), int(d)).date() | |
| except: | |
| return pd.DataFrame([{"Error": "Invalid date input"}]) | |
| if date_col not in df.columns: | |
| return pd.DataFrame([{"Error": f"Missing '{date_col}' column"}]) | |
| df[date_col] = pd.to_datetime(df[date_col], errors='coerce').dt.date | |
| out = df[df[date_col] == target] | |
| if rep and rep_col in df.columns: | |
| out = out[out[rep_col] == rep] | |
| return out | |
| # -------------------- REPORT FUNCTIONS -------------------- | |
| def get_calls(rep=None): | |
| df = load_sheet("Calls") | |
| return filter_week(df, "Call Date", "Rep", rep) | |
| def get_appointments(rep=None): | |
| df = load_sheet("Appointments") | |
| return filter_week(df, "Appointment Date", "Rep", rep) | |
| def search_calls_by_date(y,m,d,rep): | |
| df = load_sheet("Calls") | |
| return filter_date(df, "Call Date", "Rep", y,m,d,rep) | |
| def search_appointments_by_date(y,m,d,rep): | |
| df = load_sheet("Appointments") | |
| return filter_date(df, "Appointment Date", "Rep", y,m,d,rep) | |
| # Leads | |
| def get_leads_detail(): | |
| df = load_sheet("AllocatedLeads") | |
| return df | |
| def get_leads_summary(): | |
| df = get_leads_detail() | |
| if "Assigned Rep" not in df.columns: | |
| return pd.DataFrame([{"Error": "Missing 'Assigned Rep'"}]) | |
| return df.groupby("Assigned Rep").size().reset_index(name="Leads Count") | |
| # -------------------- INSIGHTS -------------------- | |
| def compute_insights(): | |
| calls = get_calls() | |
| appts = get_appointments() | |
| leads = get_leads_detail() | |
| def top(df, col): | |
| if col in df.columns and not df.empty: | |
| try: | |
| return df[col].mode()[0] | |
| except: | |
| return "N/A" | |
| return "N/A" | |
| insights = pd.DataFrame([ | |
| {"Metric": "Most Calls This Week", "Rep": top(calls, "Rep")}, | |
| {"Metric": "Most Appointments This Week", "Rep": top(appts, "Rep")}, | |
| {"Metric": "Most Leads Allocated", "Rep": top(leads, "Assigned Rep")}, | |
| ]) | |
| return insights | |
| # -------------------- DROPDOWN OPTIONS -------------------- | |
| def rep_options(sheet_name, rep_col): | |
| df = load_sheet(sheet_name) | |
| if rep_col in df.columns: | |
| return sorted(df[rep_col].dropna().unique().tolist()) | |
| return [] | |
| # -------------------- USER MANAGEMENT -------------------- | |
| def save_users(df): | |
| ws = client.open_by_url(sheet_url).worksheet("User") | |
| headers = df.columns.tolist() | |
| rows = df.fillna("").values.tolist() | |
| ws.clear() | |
| ws.update([headers] + rows) | |
| return pd.DataFrame([{"Status": "Users saved."}]) | |
| # -------------------- UI -------------------- | |
| with gr.Blocks(title="Graffiti Admin Dashboard") as app: | |
| gr.Markdown("# 📆 Graffiti Admin Dashboard") | |
| with gr.Tab("Calls Report"): | |
| rep_calls = gr.Dropdown(label="Optional Rep Filter", | |
| choices=rep_options("Calls","Rep"), | |
| allow_custom_value=True) | |
| calls_btn = gr.Button("Load Current Week Calls") | |
| calls_tbl = gr.Dataframe() | |
| calls_btn.click(fn=get_calls, inputs=rep_calls, outputs=calls_tbl) | |
| gr.Markdown("### 🔍 Search Calls by Specific Date") | |
| y1,m1,d1 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day") | |
| rep1 = gr.Dropdown(label="Optional Rep Filter", | |
| choices=rep_options("Calls","Rep"), | |
| allow_custom_value=True) | |
| calls_date_btn = gr.Button("Search Calls by Date") | |
| calls_date_tbl = gr.Dataframe() | |
| calls_date_btn.click(fn=search_calls_by_date, | |
| inputs=[y1,m1,d1,rep1], | |
| outputs=calls_date_tbl) | |
| with gr.Tab("Appointments Report"): | |
| rep_appt = gr.Dropdown(label="Optional Rep Filter", | |
| choices=rep_options("Appointments","Rep"), | |
| allow_custom_value=True) | |
| appt_btn = gr.Button("Load Current Week Appointments") | |
| appt_summary = gr.Dataframe(label="📊 Weekly Appointments Summary by Rep") | |
| appt_tbl = gr.Dataframe() | |
| appt_btn.click( | |
| fn=lambda rep: ( | |
| get_appointments(rep).groupby("Rep").size().reset_index(name="Count"), | |
| get_appointments(rep) | |
| ), | |
| inputs=rep_appt, | |
| outputs=[appt_summary, appt_tbl] | |
| ) | |
| gr.Markdown("### 🔍 Search Appointments by Specific Date") | |
| y2,m2,d2 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day") | |
| rep2 = gr.Dropdown(label="Optional Rep Filter", | |
| choices=rep_options("Appointments","Rep"), | |
| allow_custom_value=True) | |
| appt_date_btn = gr.Button("Search Appointments by Date") | |
| appt_date_sum = gr.Dataframe(label="📊 Appointments Summary for Date by Rep") | |
| appt_date_tbl = gr.Dataframe() | |
| appt_date_btn.click( | |
| fn=lambda y,m,d,rep: ( | |
| search_appointments_by_date(y,m,d,rep) | |
| .groupby("Rep").size().reset_index(name="Count"), | |
| search_appointments_by_date(y,m,d,rep) | |
| ), | |
| inputs=[y2,m2,d2,rep2], | |
| outputs=[appt_date_sum, appt_date_tbl] | |
| ) | |
| 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]) | |
| with gr.Tab("Insights"): | |
| insights_btn = gr.Button("Generate Insights") | |
| insights_tbl = gr.Dataframe() | |
| insights_btn.click(fn=compute_insights, outputs=insights_tbl) | |
| with gr.Tab("User Management"): | |
| gr.Markdown("## 👤 Manage Users\nEdit the grid and click **Save Users** to push changes.") | |
| users_df = load_sheet("User") | |
| users_grid = gr.Dataframe(value=users_df, interactive=True) | |
| save_btn = gr.Button("Save Users") | |
| status = gr.Dataframe() | |
| save_btn.click(fn=save_users, inputs=users_grid, outputs=status) | |
| app.launch() |