Spaces:
Sleeping
Sleeping
File size: 5,556 Bytes
88ff990 4e6e288 88ff990 f97e304 88ff990 d5ae19a 4e6e288 1f48402 4e6e288 d5ae19a 593765d d5ae19a f97e304 5c20576 f97e304 5c20576 f97e304 5c20576 f97e304 5c20576 f97e304 5c20576 f97e304 5c20576 f97e304 5c20576 d5ae19a 5c20576 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
import pandas as pd
import gspread
import gradio as gr
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime
from math import radians, cos, sin, asin, sqrt
# ------------------ AUTH ------------------
VALID_USERS = {
"[email protected]": "Pass.123",
"[email protected]": "Pass.123",
"[email protected]": "Pass.123"
}
# ------------------ GOOGLE SHEET SETUP ------------------
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)
sheet_file = client.open("userAccess")
# ------------------ HELPERS ------------------
def load_tab(sheet_name):
try:
df = pd.DataFrame(sheet_file.worksheet(sheet_name).get_all_records())
return df
except:
return pd.DataFrame([["⚠️ Could not load sheet."]], columns=["Error"])
def haversine(coord1, coord2):
lon1, lat1 = map(radians, map(float, coord1.split(',')[::-1]))
lon2, lat2 = map(radians, map(float, coord2.split(',')[::-1]))
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
c = 2 * asin(sqrt(a))
return 6371 * c # in km
# ------------------ LOAD SHEETS ------------------
def load_field_sales():
df = load_tab("Field Sales")
if df.empty:
return pd.DataFrame(columns=["Date", "Rep", "Order Value", "Order Received", "Location", "KM Travelled"])
df['Date'] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce')
df = df.dropna(subset=["Date"])
df["Order Value"] = pd.to_numeric(df.get("Order Value", 0), errors="coerce").fillna(0)
df["KM Travelled"] = 0.0
for rep in df["Rep"].unique():
rep_df = df[df["Rep"] == rep].sort_values(by="Date")
prev_coord = None
for idx, row in rep_df.iterrows():
curr_coord = row.get("Location", "")
if prev_coord and curr_coord:
try:
km = haversine(prev_coord, curr_coord)
df.at[idx, "KM Travelled"] = km
except:
df.at[idx, "KM Travelled"] = 0
prev_coord = curr_coord
df["KM Travelled"] = df["KM Travelled"].round(2)
return df
def load_telesales():
df = load_tab("Telesales")
df["Order Value"] = pd.to_numeric(df.get("Order Value", 0), errors="coerce").fillna(0)
return df
def load_summary(field_df, telesales_df):
summary = []
reps = pd.concat([field_df["Rep"], telesales_df["Rep"]]).dropna().unique()
for rep in reps:
field_orders = field_df[(field_df["Rep"] == rep) & (field_df["Order Received"].str.lower() == "yes")]
telesales_orders = telesales_df[(telesales_df["Rep"] == rep) & (telesales_df["Order Received"].str.lower() == "yes")]
total_value = field_orders["Order Value"].sum() + telesales_orders["Order Value"].sum()
total_orders = len(field_orders) + len(telesales_orders)
total_km = field_df[field_df["Rep"] == rep]["KM Travelled"].sum()
summary.append([rep, total_value, total_orders, round(total_km, 2)])
return pd.DataFrame(summary, columns=["Rep", "Total Order Value", "Orders Received", "KM Travelled"])
# ------------------ MAIN INTERFACE ------------------
def login(email, password):
if VALID_USERS.get(email) == password:
field_df = load_field_sales()
telesales_df = load_telesales()
summary_df = load_summary(field_df, telesales_df)
orders_df = load_tab("Orders")
escalation_df = load_tab("Escalations")
oem_df = load_tab("OEM Visits")
cust_req_df = load_tab("Customer Requests")
dealer_df = load_tab("Dealership Directory")
users_df = load_tab("Users")
with gr.Tab("Summary"):
gr.Dataframe(summary_df, label="📊 Rep Summary")
with gr.Tab("Field Sales"):
gr.Dataframe(field_df[["Rep", "Order Value", "Order Received", "KM Travelled"]], label="Field Sales Summary")
gr.Dataframe(field_df, label="Raw Field Sales")
with gr.Tab("TeleSales"):
gr.Dataframe(telesales_df, label="TeleSales Data")
with gr.Tab("Orders"):
gr.Dataframe(orders_df, label="Orders")
with gr.Tab("Escalations"):
gr.Dataframe(escalation_df, label="Escalations")
with gr.Tab("OEM Visits"):
gr.Dataframe(oem_df, label="OEM Visits")
with gr.Tab("Customer Requests"):
gr.Dataframe(cust_req_df, label="Customer Requests")
with gr.Tab("Dealership Directory"):
gr.Dataframe(dealer_df, label="Dealership Directory")
with gr.Tab("Users"):
gr.Dataframe(users_df, label="Users")
return gr.update(visible=False), gr.update(visible=True)
else:
return "Invalid credentials", gr.update(visible=False)
with gr.Blocks(theme=gr.themes.Monochrome(), css="footer {visibility: hidden}") as demo:
with gr.Column(visible=True) as login_col:
gr.Markdown("### 🔐 CarMat Dashboard Login")
email = gr.Textbox(label="Email")
password = gr.Textbox(label="Password", type="password")
login_btn = gr.Button("Login")
login_msg = gr.Text()
with gr.Column(visible=False) as dashboard_col:
gr.Markdown("## 📂 CarMat Dashboard")
login_btn.click(fn=login, inputs=[email, password], outputs=[login_msg, dashboard_col])
demo.launch()
|