Spaces:
Running
Running
File size: 4,505 Bytes
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 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 |
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 |