Spaces:
Sleeping
Sleeping
File size: 3,869 Bytes
88ff990 4e6e288 88ff990 50acac4 88ff990 50acac4 4e6e288 1f48402 4e6e288 50acac4 4e6e288 1f48402 50acac4 4e6e288 50acac4 268f142 50acac4 |
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 |
import pandas as pd
import gspread
import gradio as gr
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime
from geopy.distance import geodesic
import folium
from io import BytesIO
# Google Sheets Auth
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")
# Load Data
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"])
# GPS calculations
def calculate_gps_data(df):
df = df.sort_values(['Date', 'Time'])
df[['Latitude', 'Longitude']] = df['Location'].str.split(', ', expand=True).astype(float)
df['Kms Travelled'] = df[['Latitude', 'Longitude']].shift().apply(
lambda row: geodesic((row['Latitude'], row['Longitude']), (row.name[0], row.name[1])).km if pd.notnull(row['Latitude']) else 0,
axis=1)
df['Duration Between Calls (min)'] = df[['Date', 'Time']].apply(
lambda row: pd.to_datetime(row['Date'] + ' ' + row['Time']), axis=1
).diff().dt.total_seconds().div(60)
df.fillna({'Kms Travelled': 0, 'Duration Between Calls (min)': 0}, inplace=True)
return df
# Load and process Field Sales data
field_sales_df = calculate_gps_data(load_tab("Field Sales"))
# Map generation
def generate_map(df):
if df.empty or df[['Latitude', 'Longitude']].isna().all().all():
return None
coords = df[['Latitude', 'Longitude']].dropna().values
map_center = coords[0]
m = folium.Map(location=map_center, zoom_start=12)
for idx, coord in enumerate(coords):
folium.Marker(location=coord, popup=f"Visit {idx+1}").add_to(m)
folium.PolyLine(coords, color='blue').add_to(m)
buf = BytesIO()
m.save(buf, close_file=False)
return buf.getvalue().decode()
# Gradio Interface
with gr.Blocks() as app:
gr.Markdown("## 🚗 CarMat Dashboard")
unique_dates = sorted(field_sales_df['Date'].unique(), reverse=True)
# Field Sales Tab
with gr.Tab("🗺️ Field Sales"):
date_selector = gr.Dropdown(label="Select Date", choices=unique_dates)
data_output = gr.DataFrame()
map_html = gr.HTML()
def update_field_sales(date):
day_df = field_sales_df[field_sales_df['Date'] == date]
map_render = generate_map(day_df)
return day_df, map_render
date_selector.change(fn=update_field_sales, inputs=date_selector, outputs=[data_output, map_html])
# Summary Tab
with gr.Tab("📊 Summary"):
date_summary = gr.Dropdown(label="Select Date", choices=unique_dates)
summary_visits = gr.DataFrame()
def update_summary(date):
day_df = field_sales_df[field_sales_df['Date'] == date]
visits = day_df.groupby("Rep").size().reset_index(name="Total Visits")
return visits
date_summary.change(fn=update_summary, inputs=date_summary, outputs=summary_visits)
# Orders Tab
with gr.Tab("📦 Orders"):
order_date = gr.Dropdown(label="Select Date", choices=unique_dates)
orders_output = gr.DataFrame()
def orders_summary(date):
day_df = field_sales_df[field_sales_df['Date'] == date]
orders_df = day_df[day_df["Order Received"] == "Yes"]
summary = orders_df.groupby("Rep").agg({
"Order Value": "sum",
"Order Received": "count"
}).rename(columns={"Order Received": "Orders Count"}).reset_index()
return summary
order_date.change(fn=orders_summary, inputs=order_date, outputs=orders_output)
app.launch() |