Spaces:
Running
Running
import pandas as pd | |
import numpy as np | |
import streamlit as st | |
import psycopg2 | |
from sqlalchemy import create_engine #Paquetería para subir datos a una base de datos | |
from pandasql import sqldf | |
import json | |
#Conexión a la DB usando st.secrets | |
def connection_db(): | |
conn = psycopg2.connect(**{ | |
"host": "blau-production.cvcvpaamxkwo.us-east-2.rds.amazonaws.com", | |
"port": 5432, | |
"dbname": "rme_prod", | |
"user": "3P_dashboards", | |
"password": "WoUYGWzI6J8IgqHCNDjLiifHwQvSdRj" | |
}) | |
return conn | |
def get_event(cliente_reciclador): | |
conn = connection_db() | |
q1 = f""" | |
SELECT r.id as id_servicio | |
,eu.business_name || ' Suc: ' || s.nombre_sucursal || ' | ' || ' Ruta: ' || cr.name || ' - ' || d.name || ' | ' || ' Estatus: ' || rs.description as title | |
,r.id || ' ' || eu.business_name || ' Suc: ' || s.nombre_sucursal || ' | ' || ' Ruta: ' || cr.name || ' - ' || d.name || ' | ' || ' Estatus: ' || rs.description as extendedProps | |
,cr.name AS ruta | |
,eu.business_name as cliente | |
,s.nombre_sucursal as sucursal | |
,d.name AS chofer | |
,rs.description as estatus | |
,cr.name || ' - ' || d.name as ruta_chofer | |
,t.name as truck_name | |
,CASE WHEN r.status < 4 THEN '#F1C40F' --'Pendiente' | |
WHEN r.status = 4 THEN '#2ECC71' --'Completada' | |
WHEN r.status = 5 THEN '#E74C3C' --'Cancelada' | |
WHEN r.status > 5 THEN '#E67E22' --'Servicio con incidencia' | |
ELSE NULL END AS color | |
,rs.description | |
,r.date as start --fecha | |
,r.date as end | |
,cr.id as resourceId --ruta_id | |
,r.id_cliente_reciclador | |
FROM recollections r | |
left join route_status rs on r.status = rs.id | |
LEFT JOIN sucursales s | |
ON s.id = r.id_sucursal | |
LEFT JOIN end_users eu | |
ON eu.id = s.id_end_user | |
LEFT JOIN cat_route cr | |
ON r.id_cat_route = cr.id | |
left join trucks t | |
on t.id = cr.id_truck | |
LEFT JOIN drivers d | |
ON d.id = r.id_driver | |
WHERE r.id_cliente_reciclador = '{cliente_reciclador}' | |
AND r.enabled | |
AND r.id_cat_route NOTNULL | |
AND r.date BETWEEN current_date -7 AND current_date +3 | |
""" | |
df0 = pd.read_sql_query(q1, conn) | |
df0['start'] = pd.to_datetime(df0['start']).dt.strftime('%Y-%m-%d') | |
df0['end'] = pd.to_datetime(df0['end']).dt.strftime('%Y-%m-%d') | |
#df1 = df0.to_json(orient='records', date_format='iso') | |
conn.close() | |
return df0 | |
#print(get_event()) | |
#print(get_resources()) | |
#a=get_event() | |
#b=a.to_json(orient='records') | |
#c= decoder.raw_decode(b) | |
#pprint.pprint(b) | |
#print(b) |