Spaces:
Sleeping
Sleeping
| import gradio as gr | |
| import pandas as pd | |
| import gspread | |
| from oauth2client.service_account import ServiceAccountCredentials | |
| from datetime import datetime | |
| # -- Login credentials -- | |
| USER_CREDENTIALS = {"[email protected]": "Pass.123"} | |
| # -- Load Google Sheets data -- | |
| def get_sheet_data(): | |
| scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"] | |
| creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope) | |
| client = gspread.authorize(creds) | |
| # ✅ Fixed sheet name here: | |
| sheet = client.open_by_title("userAccess").worksheet("Field Sales") | |
| data = pd.DataFrame(sheet.get_all_records()) | |
| return data | |
| # -- Generate report based on selected period -- | |
| def generate_report(data, period="Daily"): | |
| try: | |
| if "Date" not in data.columns: | |
| return "❌ Column 'Date' not found in the sheet." | |
| data["Date"] = pd.to_datetime(data["Date"], errors='coerce') | |
| data = data.dropna(subset=["Date"]) | |
| now = pd.Timestamp.today() | |
| if period == "Weekly": | |
| data = data[data["Date"] >= now - pd.Timedelta(days=7)] | |
| elif period == "Monthly": | |
| data = data[data["Date"].dt.month == now.month] | |
| elif period == "Yearly": | |
| data = data[data["Date"].dt.year == now.year] | |
| total_visits = len(data) | |
| current = len(data[data["Current/Prospect Custor"] == "Current"]) | |
| new = len(data[data["Current/Prospect Custor"] == "Prospect"]) | |
| second_hand = len(data[data["Customer Type"].str.contains("Second", na=False)]) | |
| telesales = len(data[data["Source"] == "TeleSales"]) | |
| oem_visits = len(data[data["Source"] == "OEM Visit"]) | |
| orders = len(data[data["Order Received"] == "Yes"]) | |
| order_value = data["Order Value"].sum() if "Order Value" in data.columns else 0 | |
| return ( | |
| f"### {period} Report\n" | |
| f"- **Total Visits:** {total_visits}\n" | |
| f"- **Current Dealerships:** {current}\n" | |
| f"- **New Dealerships:** {new}\n" | |
| f"- **% Current:** {current / total_visits * 100:.1f}% | **% New:** {new / total_visits * 100:.1f}%\n" | |
| f"- **Second-hand Dealerships:** {second_hand}\n" | |
| f"- **TeleSales Calls:** {telesales}\n" | |
| f"- **OEM Visits:** {oem_visits}\n" | |
| f"- **Orders Received:** {orders}\n" | |
| f"- **Total Order Value:** ${order_value:,.2f}" | |
| ) | |
| except Exception as e: | |
| return f"❌ Error during report generation: {str(e)}" | |
| # -- Login function -- | |
| def login(email, password): | |
| if USER_CREDENTIALS.get(email) == password: | |
| return gr.update(visible=True), gr.update(visible=False), "" | |
| else: | |
| return gr.update(visible=False), gr.update(visible=True), "❌ Invalid email or password" | |
| # -- Report trigger -- | |
| def report(period): | |
| try: | |
| data = get_sheet_data() | |
| if data.empty: | |
| return "⚠️ No data found in the sheet." | |
| return generate_report(data, period) | |
| except Exception as e: | |
| return f"❌ Failed to load sheet: {str(e)}" | |
| # -- Gradio UI -- | |
| with gr.Blocks() as app: | |
| with gr.Row(visible=True) as login_row: | |
| email = gr.Textbox(label="Email") | |
| password = gr.Textbox(label="Password", type="password") | |
| login_btn = gr.Button("Login") | |
| login_error = gr.Textbox(label="", visible=False) | |
| with gr.Row(visible=False) as dashboard: | |
| period_dropdown = gr.Dropdown(["Daily", "Weekly", "Monthly", "Yearly"], value="Weekly", label="Select Report Period") | |
| report_btn = gr.Button("Generate Report") | |
| report_output = gr.Markdown() | |
| login_btn.click(fn=login, inputs=[email, password], outputs=[dashboard, login_error, login_error]) | |
| report_btn.click(fn=report, inputs=[period_dropdown], outputs=report_output) | |
| app.launch() | |