Spaces:
Running
Running
File size: 2,523 Bytes
eb0b2fd efb93c6 eb0b2fd |
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 |
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) |