Spaces:
Running
Running
import pandas as pd | |
from data.solicitar_data import connection_db | |
def obtener_cliente_sucursal(id_reciclador, conn): | |
q = f""" | |
SELECT eu.business_name AS cliente | |
, s.nombre_sucursal AS sucursal | |
, s.id AS id_sucursal | |
FROM end_users eu | |
LEFT JOIN sucursales s | |
ON eu.id = s.id_end_user | |
WHERE eu.id_cliente_reciclador = '{id_reciclador}' | |
AND eu.enabled | |
AND s.enabled | |
""" | |
df = pd.read_sql_query(q, conn) | |
return df | |
def obtener_chofer(id_reciclador, conn): | |
q1 = f""" | |
SELECT name AS chofer | |
, id AS id_chofer | |
FROM drivers d | |
WHERE id_cliente_reciclador = '{id_reciclador}' | |
AND NOT disabled | |
""" | |
df = pd.read_sql_query(q1, conn) | |
return df | |
def obtener_camiones(id_reciclador, conn): | |
q2 = f""" | |
SELECT name AS camion | |
, id AS id_truck | |
FROM trucks t | |
WHERE id_cliente_reciclador = '{id_reciclador}' | |
AND enabled | |
""" | |
df = pd.read_sql_query(q2, conn) | |
return df | |
def obtener_rutas(id_reciclador, conn): | |
q3 = f""" | |
SELECT cr."name" AS ruta | |
,cr.id AS id_cat | |
FROM cat_route cr | |
WHERE id_cliente_reciclador = '{id_reciclador}' | |
AND status | |
""" | |
df = pd.read_sql_query(q3, conn) | |
return df | |
def obtener_recolecciones(id_reciclador, conn): | |
q4 = f""" | |
SELECT r.id AS id_recollection | |
, s.id AS id_sucursal | |
, eu.business_name AS cliente | |
, s.nombre_sucursal AS sucursal | |
, d.id AS id_driver | |
, d."name" AS chofer | |
, t.id AS id_camion | |
, t."name" AS camion | |
, cr.id AS id_cat | |
, cr."name" AS ruta | |
FROM recollections r | |
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 cr.id = r.id_cat_route | |
LEFT JOIN drivers d | |
ON d.id = cr.id_driver | |
LEFT JOIN trucks t | |
ON t.id = cr.id_truck | |
WHERE r.id_cliente_reciclador = '{id_reciclador}' | |
AND r.id_cat_route NOTNULL | |
AND date = current_date + 1 | |
AND r.enabled | |
""" | |
df = pd.read_sql(q4, conn) | |
return df | |
def recollections_existen(list_suc: list, cat_route:int, id_reciclaor:int, conn, fecha:str): | |
""" | |
Esta función regresa una lista con las ids de las sucursales que no tienen recolecciones | |
y los id_recollections de las sucurales que si tienen recolecciones | |
""" | |
if len(list_suc) == 1: | |
tupla_suc = tuple([list_suc[0], list_suc[0]]) | |
elif len(list_suc) > 1: | |
tupla_suc = tuple(list_suc) | |
else: | |
tupla_suc = tuple() | |
q = f""" | |
SELECT id AS id_recollections | |
, id_sucursal | |
,date | |
,id_cat_route | |
FROM recollections r | |
WHERE id_cliente_reciclador = '{id_reciclaor}' | |
AND date = '{fecha}' | |
AND enabled | |
AND id_sucursal IN {tupla_suc} | |
AND (id_cat_route ISNULL OR id_cat_route = '{cat_route}') | |
""" | |
df_database = pd.read_sql_query(q, conn) | |
suc_recoll = df_database["id_sucursal"].to_list() | |
suc_no_recoll = [suc for suc in list_suc if suc not in suc_recoll] #ids_suc que no tienen recolecciones | |
recoll_ids = df_database | |
return suc_no_recoll, recoll_ids | |
def contenedores(id_reciclador, conn): | |
q = f""" | |
SELECT id_sucursal | |
, CASE WHEN cantidad = 0 THEN 1 | |
ELSE cantidad END AS cantidad | |
, material | |
, CASE WHEN tipo = '3m3' THEN 'Contenedor 3m3' | |
WHEN tipo = 'metros cubicos' THEN 'Contenedor 1m3' ELSE tipo END AS contenedor | |
FROM ( | |
SELECT id_sucursal | |
,mode() within group (order by cantidad desc) AS cantidad | |
,mode() within group (order by material desc) as material | |
,mode() within group (order by tipo desc) AS tipo | |
FROM ( | |
SELECT DISTINCT ON (r.id) r.id | |
,r.id_sucursal | |
,(UNNEST(rd.collected_containers) ->> 'amount') :: NUMERIC AS cantidad | |
,UNNEST(rd.collected_containers) ->> 'material' AS material | |
,unnest(rd.collected_containers) ->> 'type' AS tipo | |
FROM recollections r | |
LEFT JOIN recollection_declarations rd | |
ON r.id = rd.id_recollection | |
WHERE r.id_cliente_reciclador = '{id_reciclador}' | |
AND r.date BETWEEN current_date - 14 AND current_date | |
AND r.status = 4 | |
) a | |
GROUP BY id_sucursal | |
) b | |
""" | |
df = pd.read_sql_query(q, conn) | |
return df |