Spaces:
Running
Running
File size: 6,382 Bytes
d685333 44e7320 d685333 d975ba4 bc2d37c 7074721 d685333 44e7320 87d485d 44e7320 87d485d d685333 35f7fbb d685333 35f7fbb d685333 35f7fbb 65e2013 0b258de 44e7320 65e2013 44e7320 a137762 44e7320 a137762 44e7320 a137762 87d485d d685333 35f7fbb 87d485d 35f7fbb 87d485d 35f7fbb 87d485d 35f7fbb 87d485d 44e7320 d685333 6cca50e d685333 6cca50e d685333 6cca50e d685333 |
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 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
import gradio as gr
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import plotly.express as px
# ------------------ Authentication ------------------
VALID_USERS = {
"[email protected]": "Pass.123",
"[email protected]": "Pass.123",
"[email protected]": "Pass.123"
}
# ------------------ Data Setup ------------------
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("tough-star.json", scope)
client = gspread.authorize(creds)
sheet_url = "https://docs.google.com/spreadsheets/d/1bpeFS6yihb6niCavpwjWmVEypaSkGxONGg2jZfKX_sA"
sheet = client.open_by_url(sheet_url).worksheet("Calls")
data = sheet.get_all_records()
df = pd.DataFrame(data)
df['Timestamp'] = pd.to_datetime(df['Timestamp'], dayfirst=True, errors='coerce')
df['Date'] = df['Timestamp'].dt.date.astype(str)
df['Time'] = df['Timestamp'].dt.time
location_split = df['Location'].str.split(',', expand=True)
df['Latitude'] = pd.to_numeric(location_split[0], errors='coerce')
df['Longitude'] = pd.to_numeric(location_split[1], errors='coerce')
df = df.dropna(subset=['Date', 'Rep Name', 'Latitude', 'Longitude'])
df = df[(df['Latitude'] != 0) & (df['Longitude'] != 0)]
df = df.sort_values(by=['Rep Name', 'Timestamp'])
df['Time Diff (min)'] = df.groupby(['Rep Name', 'Date'])['Timestamp'].diff().dt.total_seconds().div(60).fillna(0)
df['Visit Order'] = df.groupby(['Rep Name', 'Date']).cumcount() + 1
ALL_REPS = sorted(df['Rep Name'].dropna().unique())
# ------------------ App Logic ------------------
def generate_summary(date_str):
day_df = df[df['Date'] == date_str]
active = day_df.groupby('Rep Name').size().reset_index(name='Total Visits')
active_list = active['Rep Name'].tolist()
inactive_list = [rep for rep in ALL_REPS if rep not in active_list]
inactive_df = pd.DataFrame({'Inactive Reps': inactive_list})
return active, inactive_df
def get_reps(date_str):
reps = df[df['Date'] == date_str]['Rep Name'].dropna().unique()
return gr.update(choices=sorted(reps))
def show_map(date_str, rep):
subset = df[(df['Date'] == date_str) & (df['Rep Name'] == rep)]
if subset.empty:
return "No valid data", None
subset = subset.sort_values(by='Timestamp').copy()
subset['Visit Order'] = range(1, len(subset) + 1)
center_lat = subset['Latitude'].mean()
center_lon = subset['Longitude'].mean()
fig = px.line_mapbox(
subset,
lat="Latitude", lon="Longitude",
hover_name="Dealership Name",
hover_data={"Time": True, "Time Diff (min)": True, "Visit Order": True},
height=700,
zoom=13,
center={"lat": center_lat, "lon": center_lon}
)
scatter = px.scatter_mapbox(
subset,
lat="Latitude", lon="Longitude",
color="Visit Order",
hover_name="Dealership Name",
hover_data=["Time", "Time Diff (min)"],
color_continuous_scale="Bluered"
)
for trace in scatter.data:
fig.add_trace(trace)
fig.add_trace(px.scatter_mapbox(
pd.DataFrame([subset.iloc[0]]),
lat="Latitude", lon="Longitude",
text=["Start"], color_discrete_sequence=["green"]).data[0])
fig.add_trace(px.scatter_mapbox(
pd.DataFrame([subset.iloc[-1]]),
lat="Latitude", lon="Longitude",
text=["End"], color_discrete_sequence=["red"]).data[0])
fig.update_layout(mapbox_style="open-street-map", title=f"๐ {rep}'s Route on {date_str}")
table = subset[[
'Visit Order', 'Dealership Name', 'Time', 'Time Diff (min)',
'Type of call', 'Sales or service'
]].rename(columns={
'Dealership Name': '๐งญ Dealer',
'Time': '๐ Time',
'Time Diff (min)': 'โฑ๏ธ Time Spent',
'Type of call': '๐ Call Type',
'Sales or service': '๐ผ Category'
})
total_time = round(table['โฑ๏ธ Time Spent'].sum(), 2)
summary_row = pd.DataFrame([{
'Visit Order': '',
'๐งญ Dealer': f"๐งฎ Total Time: {total_time} min",
'๐ Time': '',
'โฑ๏ธ Time Spent': '',
'๐ Call Type': '',
'๐ผ Category': ''
}])
table = pd.concat([table, summary_row], ignore_index=True)
return table, fig
# ------------------ Login + Protected UI ------------------
with gr.Blocks() as app:
state = gr.State(False)
with gr.Row():
with gr.Column(scale=1):
login_title = gr.Markdown("## ๐ Login Required")
email = gr.Textbox(label="Email")
password = gr.Textbox(label="Password", type="password")
login_btn = gr.Button("Login")
login_msg = gr.Markdown("")
with gr.Column(visible=False) as main_ui:
gr.Markdown("## ๐๏ธ Carfind Rep Tracker")
with gr.Tab("๐ Summary"):
date_summary = gr.Dropdown(label="Select Date", choices=sorted(df['Date'].unique(), reverse=True))
active_table = gr.Dataframe(label="โ
Active Reps (with total visits)")
inactive_table = gr.Dataframe(label="โ ๏ธ Inactive Reps")
date_summary.change(fn=generate_summary, inputs=date_summary, outputs=[active_table, inactive_table])
with gr.Tab("๐ค KAM's"):
with gr.Row():
with gr.Column(scale=1):
date_picker = gr.Dropdown(label="Select Date", choices=sorted(df['Date'].unique(), reverse=True))
rep_picker = gr.Dropdown(label="Select Rep")
btn = gr.Button("Show Route")
with gr.Column(scale=2):
table = gr.Dataframe(label="Call Table")
map_plot = gr.Plot(label="Map")
date_picker.change(fn=get_reps, inputs=date_picker, outputs=rep_picker)
btn.click(fn=show_map, inputs=[date_picker, rep_picker], outputs=[table, map_plot])
def check_login(u, p):
if VALID_USERS.get(u) == p:
return gr.update(visible=False), gr.update(visible=True), ""
else:
return gr.update(), gr.update(), "โ Incorrect email or password."
login_btn.click(fn=check_login, inputs=[email, password], outputs=[login_title, main_ui, login_msg])
app.launch()
|