debug

Develop/ronald

See merge request m3f_usm/admin_transporte/backend!3

.env.develop

@@ -20,10 +20,15 @@ DB_MONGO_PASS=password
 SMTP_HOST=smtp-mail.outlook.com
 SMTP_PORT=587
 SMTP_PROTOCOL=tls
-SMTP_USER=francisco.sandoval@outlook.cl
-SMTP_PASS=aigdvnrbueitklry
 SMTP_FROM='"Sistema Transporte" <francisco.sandoval@outlook.cl>'
 
 # PATH UPLOAD
 GTFS_UPLOADS=/uploads/gtfs
-PHOTOS_UPLOADS=/uploads/photos
+PHOTOS_UPLOADS=/uploads/photos
+
+# URL INFO PARADERO
+URL_PARADERO='http://localhost:3001/rutaParadero/?id='
+
+#COORDENADAS DE INICIO PARA EL MAPA
+COORDINI_LAT=-36.8270
+COORDINI_LNG=-73.0503

.env.testing

@@ -14,10 +14,8 @@ DB_REDIS_PORT=6379
 SMTP_HOST=smtp-mail.outlook.com
 SMTP_PORT=587
 SMTP_PROTOCOL=tls
-SMTP_USER=francisco.sandoval@outlook.cl
-SMTP_PASS=aigdvnrbueitklry
 SMTP_FROM='"Sistema Transporte" <francisco.sandoval@outlook.cl>'
 
 # PATH UPLOAD
 GTFS_UPLOADS=/uploads/gtfs
-PHOTOS_UPLOADS=/uploads/photos
+PHOTOS_UPLOADS=/uploads/photos

docs/rest/dispositivo.rest

@@ -68,6 +68,20 @@ Content-Type: application/json
 }
 
 
+###
+# @name get_info_device2
+POST {{server}}/dispositivos/getInfoDevice/
+Authorization: Bearer {{token}}
+Content-Type: application/json
+
+{
+    "GetInfoDevice": {
+        "idDispositivo": "00000000160f3b42b8:27:eb:0f:3b:42",
+        "KeyAutorizacion":"token"
+    }
+}
+
+
 
 
 ###

docs/rest/login.rest

@@ -8,8 +8,8 @@ POST {{server}}/auth/
 Content-Type: application/json
 
 {
-    "rut": "22222222-2",
+    "rut": "11111111-1",
-    "password": "usuario2"
+    "password": "usuario1"
 }
 
 ###

project/api/management/commands/actualiza_GTFS.sql

@@ -1,223 +1,393 @@
+DO $$
+DECLARE
+    inicio_vigencia DATE;
+    v_error TEXT := '';
+BEGIN
 
-ALTER TABLE rol_linea drop CONSTRAINT IF EXISTS rol_linea_id_linea_fkey;
+    -- Verificar agency_id único
+    IF (SELECT COUNT(*) FROM (SELECT agency_id FROM z_agency GROUP BY agency_id HAVING COUNT(*) > 1) AS duplicated) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - Agency_id duplicados'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '1 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
------
+    -- Verificar route_id único
+    IF (SELECT COUNT(*) FROM (SELECT route_id FROM z_routes GROUP BY route_id HAVING COUNT(*) > 1) AS duplicated) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - Route_id duplicados'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '2 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
-ALTER TABLE linea_paradero drop CONSTRAINT IF EXISTS linea_paradero_id_linea_fkey;
+    -- Verificar service_id único en calendar
+    IF (SELECT COUNT(*) FROM (SELECT service_id FROM z_calendar GROUP BY service_id HAVING COUNT(*) > 1) AS duplicated) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - service_id duplicados en calendar'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '3 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
------
+    -- Verificar trip_id único
+    IF (SELECT COUNT(*) FROM (SELECT trip_id FROM z_trips GROUP BY trip_id HAVING COUNT(*) > 1) AS duplicated) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - Trip_id duplicados '
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '4 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
-ALTER TABLE gtfs_stop_times drop CONSTRAINT IF EXISTS fk_gtfs_sto_reference_gtfs_tri;
+    -- Verificar stop_id único
+    IF (SELECT COUNT(*) FROM (SELECT stop_id FROM z_stops GROUP BY stop_id HAVING COUNT(*) > 1) AS duplicated) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - Stop_id duplicados'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '5 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
------
+    -- Verificar referencias cruzadas entre trips y routes
+    IF (SELECT COUNT(*) FROM z_trips WHERE route_id NOT IN (SELECT route_id FROM z_routes)) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - Trips_id con route_id que no existen'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '6 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
-ALTER TABLE gtfs_frequencie drop CONSTRAINT IF EXISTS fk_gtfs_fre_reference_gtfs_tri;
+    -- Verificar referencias cruzadas entre stop_times y trips
+    IF (SELECT COUNT(*) FROM z_stop_times WHERE trip_id NOT IN (SELECT trip_id FROM z_trips)) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - Stop_times con trips_id que no existen'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '7 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
------
+    -- Verificar referencias cruzadas entre stop_times y stops
+    IF (SELECT COUNT(*) FROM z_stop_times WHERE stop_id NOT IN (SELECT stop_id FROM z_stops)) > 0 THEN
+        update gtfs_archivo
+        set status = 'Error - stop_time con stop_id que no existen'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '8 La validacion de archivos detecto errores en los archivos GTFS';
+    END IF;
 
-DELETE FROM gtfs_calendar;
+    /*WITH inicio_calendar AS (
+        SELECT MIN(start_date) AS inicio FROM z_calendar
+    ), inicio_calendar_dates AS (
+        SELECT MIN(date) AS inicio FROM z_calendar_dates WHERE exception_type = 1
+    )
+    SELECT LEAST(
+        COALESCE((SELECT inicio FROM inicio_calendar), CURRENT_DATE),
+        COALESCE((SELECT inicio FROM inicio_calendar_dates), CURRENT_DATE)
+    ) INTO inicio_vigencia;*/
 
------
+    SELECT MIN(start_date) INTO inicio_vigencia FROM z_calendar;
+     update gtfs_archivo
+     set valid_from= inicio_vigencia
+     where trim(upper(status))='PROCESANDO' ;
 
-insert into gtfs_calendar  
+    IF CURRENT_DATE < inicio_vigencia THEN
-select service_id, monday::bool , tuesday::bool , wednesday::bool , thursday::bool ,friday::bool ,saturday::bool ,sunday::bool  
+        update gtfs_archivo
-from z_calendar zc;  
+        set status = 'PENDIENTE'
+        where trim(upper(status))='PROCESANDO' ;
+        v_error:=  '9 EL ARCHIVO AUN NO ESTA VIGENTE POR FECHA';
+    END IF;
 
------
+if v_error = '' THEN
 
-update paradero   
+            /* ASEGURA QUE CADA TRIPS TENGA UN SOLO GUION EN SU DESCRIPCION */
-set vigente = true, 
+            UPDATE z_trips
-stop_name= (select stop_name from z_stops where stop_id=paradero.id_paradero::text limit 1 ), 
+                SET trip_headsign =
-stop_desc=(select stop_desc from z_stops where stop_id=paradero.id_paradero::text limit 1 ), 
+                CASE
-stop_lat =(select stop_lat from z_stops where stop_id=paradero.id_paradero::text  limit 1)::float(8),   
+                    WHEN (LENGTH(trip_headsign) - LENGTH(REPLACE(trip_headsign, '-', '')) = 2) THEN
-stop_lon =(select stop_lon from z_stops where stop_id=paradero.id_paradero::text  limit 1)::float(8), 
+                        regexp_replace(trip_headsign, '^(.*)-(.*)-(.*)$', '\1-\2~\3')
-stop_code=(select stop_code from z_stops where stop_id=paradero.id_paradero::text  limit 1)  
+                    ELSE
-where id_paradero::text in (select stop_id from z_stops ); 
+                        trip_headsign
+                end
+                WHERE trip_headsign LIKE '%-%-%';
 
------
 
-insert into paradero
+            update gtfs_archivo
-select 
+            set vigente = False
-stop_id, null as id_comuna, null as id_tipo_paradero, true,
+            where vigente = true and id_red in
-stop_code , stop_name , stop_desc , stop_lat::float(8) ,  stop_lon::float(8) , zone_id , 
+            (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' );
-stop_url , location_type ,
-parent_station ,null as stop_time_zone ,  wheelchair_boarding::numeric 
-from z_stops zs 
-where stop_id not in (select id_paradero::text from paradero);
 
------
 
-delete from linea_paradero; 
+            ALTER TABLE linea_paradero drop CONSTRAINT IF EXISTS linea_paradero_id_linea_fkey;
 
------ 
+            -----
 
-update linea 
+            ALTER TABLE gtfs_stop_times drop CONSTRAINT IF EXISTS fk_gtfs_sto_reference_gtfs_tri;
-set vigente = false
-where id_red in  (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' )
-and id_linea  not in (
-    select trim(zr.route_id)||'-'||trim(zt.direction_id::varchar)  
-    from z_routes zr  
-    inner join z_trips zt on zr.route_id =zt.route_id 
-);
 
------ 
+            -----
 
-update linea 
+            ALTER TABLE gtfs_frequencie drop CONSTRAINT IF EXISTS fk_gtfs_fre_reference_gtfs_tri;
-set vigente = true
-where id_red in  (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' )
-and id_linea in (
-    select trim(zr.route_id)||'-'||trim(zt.direction_id::varchar)  
-    from z_routes zr  
-    inner join z_trips zt on zr.route_id =zt.route_id 
-);
 
------ 
+            -----
 
-insert into linea  
+            DELETE FROM gtfs_calendar;
-select  distinct  
-    trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id, 
-    null,--zr.agency_id ,
-    substring(zt.trip_headsign FROM '(\S+) -') as route_short_name , 
-    zr.route_desc ,
-    zr.route_type::numeric ,   
-    zr.route_url,
-    zr.route_color , 
-    zr.route_text_color , 
-    zt.trip_headsign as    route_long_name , true ,
-     (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
-    from z_routes zr  
-    inner join z_trips zt 
-    	on zr.route_id =zt.route_id 
-    	and replace (zt.trip_headsign,' ','') =replace ((SPLIT_PART(route_long_name, '-', 1)||'-'||SPLIT_PART(route_long_name, '-', 2)),' ','')
-where route_long_name  not like '%'||route_short_name ||'%'
-and (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
-union  
-select distinct 
-trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id, 
-    null,--zr.agency_id ,
-    substring(zt.trip_headsign FROM '(\S+) -') as route_short_name , 
-    zr.route_desc ,
-    zr.route_type::numeric ,   
-    zr.route_url,
-    zr.route_color , 
-    zr.route_text_color , 
-    zt.trip_headsign as    route_long_name , true ,
-     (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
-    from z_routes zr  
-    inner join z_trips zt 
-    	on zr.route_id =zt.route_id 
-    	and replace (zt.trip_headsign,' ','') =replace ((SPLIT_PART(route_long_name, '-', 3)||'-'||SPLIT_PART(route_long_name, '-', 4)),' ','')
-where route_long_name  not like '%'||route_short_name ||'%'
-and (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
-union  
-select  distinct 
-	 trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id, 
-    null,--zr.agency_id ,
-     route_short_name , 
-    zr.route_desc ,
-    zr.route_type::numeric ,   
-    zr.route_url,
-    zr.route_color , 
-    zr.route_text_color , 
-    zt.trip_headsign as    route_long_name , true ,
-     (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
-    from z_routes zr  
-    inner join z_trips zt 
-    	on zr.route_id =zt.route_id 
-    	and 
-    	( position('-' in zt.trip_headsign) = 0  
-    	 or 
-    	 route_long_name    like '%'||route_short_name ||'%'
-    	)
-where (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
-order by 1;
 
------
+            -----
 
-truncate table gtfs_shape;
+            insert into gtfs_calendar
+            select service_id, monday::bool , tuesday::bool , wednesday::bool , thursday::bool ,friday::bool ,saturday::bool ,sunday::bool
+            from z_calendar zc;
 
------
+            -----
 
-insert into gtfs_shape
+            update paradero
-select shape_id::numeric,shape_pt_lat::float8,shape_pt_lon::float8,
+            set vigente = true,
-shape_pt_sequence::numeric,shape_dist_traveled::float8
+            stop_name= (select stop_name from z_stops where stop_id=paradero.id_paradero::text limit 1 ),
-from z_shapes zs;
+            stop_desc=(select stop_desc from z_stops where stop_id=paradero.id_paradero::text limit 1 ),
+            stop_lat =(select stop_lat from z_stops where stop_id=paradero.id_paradero::text  limit 1)::float(8),
+            stop_lon =(select stop_lon from z_stops where stop_id=paradero.id_paradero::text  limit 1)::float(8),
+            stop_code=(select stop_code from z_stops where stop_id=paradero.id_paradero::text  limit 1)
+            where id_paradero::text in (select stop_id from z_stops );
 
------
+            -----
- 
-truncate table gtfs_stop_times;
 
------
+            insert into paradero
+            select
+            stop_id, null as id_comuna, null as id_tipo_paradero, true,
+            stop_code , stop_name , stop_desc , stop_lat::float(8) ,  stop_lon::float(8) , zone_id ,
+            stop_url , location_type ,
+            parent_station ,null as stop_time_zone ,  wheelchair_boarding::numeric
+            from z_stops zs
+            where stop_id not in (select id_paradero::text from paradero);
 
-truncate table gtfs_frequencie;
+            -----
 
------
+            delete from linea_paradero;
 
-truncate table gtfs_trips;
+            -----
 
------
 
-insert into gtfs_trips 
+            CREATE TABLE IF NOT EXISTS z_verifica_route (
-select 
+                route_id text NULL,
-trip_id,
+                route_short_name text NULL
-trim(route_id)||'-'||trim(direction_id::varchar) as route_id,
+            );
-shape_id::numeric ,null as regreso, trip_headsign,trip_short_name,direction_id::numeric,service_id::varchar,block_id
-from z_trips zt;
 
------
 
-insert into  gtfs_stop_times  
+            delete from    linea l
-select stop_id,trip_id,arrival_time,stop_sequence::numeric,stop_headsign,departure_time,drop_off_type::numeric,null as shape_dist_traveled ,
+            where id_linea in
-timepoint::numeric , pickup_type::numeric
+            (
-from z_stop_times zst ;
+            select route_id||'-0'  from z_verifica_route as v
+            where coalesce((select count(*) from z_routes as r where r.route_id=v.route_id and r.route_short_name = v.route_short_name),0)=0
+            union
+            select route_id||'-1'  from z_verifica_route as v
+            where coalesce((select count(*) from z_routes as r where r.route_id=v.route_id and r.route_short_name = v.route_short_name),0)=0
+            union
+            select route_id  from z_verifica_route as v
+            where coalesce((select count(*) from z_routes as r where r.route_id=v.route_id and r.route_short_name = v.route_short_name),0)=0
+            ) ;
 
------
+            delete from z_verifica_route ;
-  
-insert into linea_paradero (id_linea ,  id_paradero )
-SELECT DISTINCT 
-l.id_linea ,  p.id_paradero  
-FROM linea l
-JOIN gtfs_trips t ON l.id_linea =t.id_linea 
-JOIN gtfs_stop_times   st ON t.id_trip = st.id_trip 
-JOIN paradero p   ON st.id_paradero  = p.id_paradero;
- 
------
 
-UPDATE paradero
+            insert into  z_verifica_route
-SET stop_name = REGEXP_REPLACE(
+            select route_id, route_short_name from z_routes ;
-    REGEXP_REPLACE(
-        stop_name,
-        '\yentre\y', 
-        '-entre', 
-        'gi'
-    ),
-    '\yesq\y', 
-    '-esq', 
-    'gi'
-);
 
------
+            -----
-update paradero 
-set id_comuna = (select id_comuna from comuna_georeferencia as c where ST_Contains(c.geom, ST_SetSRID(ST_MakePoint(paradero.stop_lon, paradero.stop_lat), 4326)) limit 1)
-where id_comuna is not null;  
------
 
-delete from rol_linea where id_linea not in (select id_linea from linea);
+            update linea
+            set vigente = false
+            where id_red in  (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' )
+            and id_linea  not in (
+                select trim(zr.route_id)||'-'||trim(zt.direction_id::varchar)
+                from z_routes zr
+                inner join z_trips zt on zr.route_id =zt.route_id
+            );
 
------
+            -----
 
-ALTER TABLE rol_linea ADD CONSTRAINT rol_linea_id_linea_fkey FOREIGN KEY (id_linea) REFERENCES linea(id_linea);
+            update linea
+            set vigente = true
+            where id_red in  (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' )
+            and id_linea in (
+                select trim(zr.route_id)||'-'||trim(zt.direction_id::varchar)
+                from z_routes zr
+                inner join z_trips zt on zr.route_id =zt.route_id
+            );
 
------
+            -----
 
-ALTER TABLE linea_paradero ADD CONSTRAINT linea_paradero_id_linea_fkey FOREIGN KEY (id_linea) REFERENCES linea(id_linea);
+            insert into linea
+            select  distinct
+                trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id,
+                null,--zr.agency_id ,
+                substring(zt.trip_headsign FROM '(\S+) -') as route_short_name ,
+                zr.route_desc ,
+                zr.route_type::numeric ,
+                zr.route_url,
+                zr.route_color ,
+                zr.route_text_color ,
+                zt.trip_headsign as    route_long_name , true ,
+                (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
+                from z_routes zr
+                inner join z_trips zt
+                    on zr.route_id =zt.route_id
+                    and replace (zt.trip_headsign,' ','') =replace ((SPLIT_PART(route_long_name, '-', 1)||'-'||SPLIT_PART(route_long_name, '-', 2)),' ','')
+            where route_long_name  not like '%'||route_short_name ||'%'
+            and (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
+            union
+            select distinct
+            trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id,
+                null,--zr.agency_id ,
+                substring(zt.trip_headsign FROM '(\S+) -') as route_short_name ,
+                zr.route_desc ,
+                zr.route_type::numeric ,
+                zr.route_url,
+                zr.route_color ,
+                zr.route_text_color ,
+                zt.trip_headsign as    route_long_name , true ,
+                (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
+                from z_routes zr
+                inner join z_trips zt
+                    on zr.route_id =zt.route_id
+                    and replace (zt.trip_headsign,' ','') =replace ((SPLIT_PART(route_long_name, '-', 3)||'-'||SPLIT_PART(route_long_name, '-', 4)),' ','')
+            where route_long_name  not like '%'||route_short_name ||'%'
+            and (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
+            union
+            select  distinct
+                trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id,
+                null,--zr.agency_id ,
+                route_short_name ,
+                zr.route_desc ,
+                zr.route_type::numeric ,
+                zr.route_url,
+                zr.route_color ,
+                zr.route_text_color ,
+                zt.trip_headsign as    route_long_name , true ,
+                (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
+                from z_routes zr
+                inner join z_trips zt
+                    on zr.route_id =zt.route_id
+                    and
+                    ( position('-' in zt.trip_headsign) = 0
+                    or
+                    route_long_name    like '%'||route_short_name ||'%'
+                    )
+            where (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
+            order by 1;
 
------
+            -----
 
-ALTER TABLE gtfs_stop_times ADD CONSTRAINT fk_gtfs_sto_reference_gtfs_tri FOREIGN KEY (id_trip) REFERENCES gtfs_trips(id_trip) ON DELETE RESTRICT ON UPDATE RESTRICT;
+            truncate table gtfs_shape;
 
------
+            -----
 
-ALTER TABLE gtfs_frequencie ADD CONSTRAINT fk_gtfs_fre_reference_gtfs_tri FOREIGN KEY (id_trip) REFERENCES gtfs_trips(id_trip) ON DELETE RESTRICT ON UPDATE RESTRICT;
+            insert into gtfs_shape
+            select shape_id,shape_pt_lat::float8,shape_pt_lon::float8,
+            shape_pt_sequence::numeric,shape_dist_traveled::float8
+            from z_shapes zs;
 
------
+            -----
+
+            truncate table gtfs_stop_times;
+
+            -----
+
+            truncate table gtfs_frequencie;
+
+            -----
+
+            truncate table gtfs_trips;
+
+            -----
+
+            insert into gtfs_trips
+            select
+            trip_id,
+            trim(route_id)||'-'||trim(direction_id::varchar) as route_id,
+            shape_id ,null as regreso, trip_headsign,trip_short_name,direction_id::numeric,service_id::varchar,block_id
+            from z_trips zt;
+
+            -----
+
+            insert into  gtfs_stop_times
+            select stop_id,trip_id,arrival_time,stop_sequence::numeric,stop_headsign,departure_time,drop_off_type::numeric,null as shape_dist_traveled ,
+            timepoint::numeric , pickup_type::numeric
+            from z_stop_times zst ;
+
+            -----
+
+            insert into linea_paradero (id_linea ,  id_paradero )
+            SELECT DISTINCT
+            l.id_linea ,  p.id_paradero
+            FROM linea l
+            JOIN gtfs_trips t ON l.id_linea =t.id_linea
+            JOIN gtfs_stop_times   st ON t.id_trip = st.id_trip
+            JOIN paradero p   ON st.id_paradero  = p.id_paradero;
+
+            -----
+
+            UPDATE paradero
+            SET stop_name = REGEXP_REPLACE(
+                REGEXP_REPLACE(
+                    stop_name,
+                    '\yentre\y',
+                    '-entre',
+                    'gi'
+                ),
+                '\yesq\y',
+                '-esq',
+                'gi'
+            );
+
+            -----
+            update paradero
+            set id_comuna = (select id_comuna from comuna_georeferencia as c where ST_Contains(c.geom, ST_SetSRID(ST_MakePoint(paradero.stop_lon, paradero.stop_lat), 4326)) limit 1)
+            where id_comuna is not null;
+            -----
+
+            insert into dispositivo
+            select 'QRCode-'||id_paradero , id_paradero , true, null, 3
+            from paradero  as p where id_paradero
+            not in (select id_paradero from dispositivo as d where id_tipo_dispositivo=3 and d.id_paradero = p.id_paradero);
+
+            -----
+
+            ALTER TABLE linea_paradero ADD CONSTRAINT linea_paradero_id_linea_fkey FOREIGN KEY (id_linea) REFERENCES linea(id_linea);
+
+            -----
+
+            ALTER TABLE gtfs_stop_times ADD CONSTRAINT fk_gtfs_sto_reference_gtfs_tri FOREIGN KEY (id_trip) REFERENCES gtfs_trips(id_trip) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+            -----
+
+            ALTER TABLE gtfs_frequencie ADD CONSTRAINT fk_gtfs_fre_reference_gtfs_tri FOREIGN KEY (id_trip) REFERENCES gtfs_trips(id_trip) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+            -----
+
+
+            update paradero p set id_comuna = (
+            select id_comuna from comuna_georeferencia cg
+            where st_contains(cg.geom, st_setsrid(st_makepoint(p.stop_lon, p.stop_lat) ,4326))
+            limit 1)
+            where id_comuna is null;
+
+
+
+            CREATE TABLE IF NOT EXISTS gtfs_validaciones (
+            	id_gtfs int4 not null,
+                route_id text NULL,
+                route_long_name text NULL,
+                observacion text NULL
+            );
+
+
+           insert into gtfs_validaciones
+           select (select id_gtfs from gtfs_archivo where trim(upper(status))='PROCESANDO' ),
+           route_id, route_long_name,
+           'Ruta sin Trips Asociados'
+           from z_routes
+           where route_id not in
+           (select route_id from z_trips  ) ;
+
+
+            update gtfs_archivo
+            set vigente = true , status = case when
+            (
+                select count(*) from gtfs_validaciones where  id_gtfs =(select id_gtfs from gtfs_archivo where trim(upper(status))='PROCESANDO' )
+            ) =0 then 'GTFS CARGADO' else 'GTFS CARGADO CON REPAROS'  END
+            where  trim(upper(status))='PROCESANDO' ;
+
+END IF;
+
+END$$;

project/api/management/commands/actualiza_GTFS.sql.old

@@ -0,0 +1,252 @@
+
+ALTER TABLE linea_paradero drop CONSTRAINT IF EXISTS linea_paradero_id_linea_fkey;
+
+-----
+
+ALTER TABLE gtfs_stop_times drop CONSTRAINT IF EXISTS fk_gtfs_sto_reference_gtfs_tri;
+
+-----
+
+ALTER TABLE gtfs_frequencie drop CONSTRAINT IF EXISTS fk_gtfs_fre_reference_gtfs_tri;
+
+-----
+
+DELETE FROM gtfs_calendar;
+
+-----
+
+insert into gtfs_calendar  
+select service_id, monday::bool , tuesday::bool , wednesday::bool , thursday::bool ,friday::bool ,saturday::bool ,sunday::bool  
+from z_calendar zc;  
+
+-----
+
+update paradero   
+set vigente = true, 
+stop_name= (select stop_name from z_stops where stop_id=paradero.id_paradero::text limit 1 ), 
+stop_desc=(select stop_desc from z_stops where stop_id=paradero.id_paradero::text limit 1 ), 
+stop_lat =(select stop_lat from z_stops where stop_id=paradero.id_paradero::text  limit 1)::float(8),   
+stop_lon =(select stop_lon from z_stops where stop_id=paradero.id_paradero::text  limit 1)::float(8), 
+stop_code=(select stop_code from z_stops where stop_id=paradero.id_paradero::text  limit 1)  
+where id_paradero::text in (select stop_id from z_stops ); 
+
+-----
+
+insert into paradero
+select 
+stop_id, null as id_comuna, null as id_tipo_paradero, true,
+stop_code , stop_name , stop_desc , stop_lat::float(8) ,  stop_lon::float(8) , zone_id , 
+stop_url , location_type ,
+parent_station ,null as stop_time_zone ,  wheelchair_boarding::numeric 
+from z_stops zs 
+where stop_id not in (select id_paradero::text from paradero);
+
+-----
+
+delete from linea_paradero;
+
+----- 
+
+
+CREATE TABLE IF NOT EXISTS z_verifica_route (
+    route_id text NULL,
+    route_short_name text NULL
+);
+
+
+delete from    linea l 
+where id_linea in 
+(
+select route_id||'-0'  from z_verifica_route as v
+where coalesce((select count(*) from z_routes as r where r.route_id=v.route_id and r.route_short_name = v.route_short_name),0)=0  
+union 
+select route_id||'-1'  from z_verifica_route as v
+where coalesce((select count(*) from z_routes as r where r.route_id=v.route_id and r.route_short_name = v.route_short_name),0)=0  
+union 
+select route_id  from z_verifica_route as v
+where coalesce((select count(*) from z_routes as r where r.route_id=v.route_id and r.route_short_name = v.route_short_name),0)=0  
+) ;
+
+delete from z_verifica_route ;
+
+insert into  z_verifica_route  
+select route_id, route_short_name from z_routes ;
+
+----- 
+
+update linea 
+set vigente = false
+where id_red in  (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' )
+and id_linea  not in (
+    select trim(zr.route_id)||'-'||trim(zt.direction_id::varchar)  
+    from z_routes zr  
+    inner join z_trips zt on zr.route_id =zt.route_id 
+);
+
+----- 
+
+update linea 
+set vigente = true
+where id_red in  (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' )
+and id_linea in (
+    select trim(zr.route_id)||'-'||trim(zt.direction_id::varchar)  
+    from z_routes zr  
+    inner join z_trips zt on zr.route_id =zt.route_id 
+);
+
+----- 
+
+insert into linea  
+select  distinct  
+    trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id, 
+    null,--zr.agency_id ,
+    substring(zt.trip_headsign FROM '(\S+) -') as route_short_name , 
+    zr.route_desc ,
+    zr.route_type::numeric ,   
+    zr.route_url,
+    zr.route_color , 
+    zr.route_text_color , 
+    zt.trip_headsign as    route_long_name , true ,
+     (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
+    from z_routes zr  
+    inner join z_trips zt 
+    	on zr.route_id =zt.route_id 
+    	and replace (zt.trip_headsign,' ','') =replace ((SPLIT_PART(route_long_name, '-', 1)||'-'||SPLIT_PART(route_long_name, '-', 2)),' ','')
+where route_long_name  not like '%'||route_short_name ||'%'
+and (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
+union  
+select distinct 
+trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id, 
+    null,--zr.agency_id ,
+    substring(zt.trip_headsign FROM '(\S+) -') as route_short_name , 
+    zr.route_desc ,
+    zr.route_type::numeric ,   
+    zr.route_url,
+    zr.route_color , 
+    zr.route_text_color , 
+    zt.trip_headsign as    route_long_name , true ,
+     (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
+    from z_routes zr  
+    inner join z_trips zt 
+    	on zr.route_id =zt.route_id 
+    	and replace (zt.trip_headsign,' ','') =replace ((SPLIT_PART(route_long_name, '-', 3)||'-'||SPLIT_PART(route_long_name, '-', 4)),' ','')
+where route_long_name  not like '%'||route_short_name ||'%'
+and (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
+union  
+select  distinct 
+	 trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) as route_id, 
+    null,--zr.agency_id ,
+     route_short_name , 
+    zr.route_desc ,
+    zr.route_type::numeric ,   
+    zr.route_url,
+    zr.route_color , 
+    zr.route_text_color , 
+    zt.trip_headsign as    route_long_name , true ,
+     (select id_red from gtfs_archivo where trim(upper(status))='PROCESANDO' limit 1) as id_red
+    from z_routes zr  
+    inner join z_trips zt 
+    	on zr.route_id =zt.route_id 
+    	and 
+    	( position('-' in zt.trip_headsign) = 0  
+    	 or 
+    	 route_long_name    like '%'||route_short_name ||'%'
+    	)
+where (trim(zr.route_id)||'-'||trim(zt.direction_id::varchar) ) not in (select id_linea from linea  )
+order by 1;
+
+-----
+
+truncate table gtfs_shape;
+
+-----
+
+insert into gtfs_shape
+select shape_id::numeric,shape_pt_lat::float8,shape_pt_lon::float8,
+shape_pt_sequence::numeric,shape_dist_traveled::float8
+from z_shapes zs;
+
+-----
+ 
+truncate table gtfs_stop_times;
+
+-----
+
+truncate table gtfs_frequencie;
+
+-----
+
+truncate table gtfs_trips;
+
+-----
+
+insert into gtfs_trips 
+select 
+trip_id,
+trim(route_id)||'-'||trim(direction_id::varchar) as route_id,
+shape_id::numeric ,null as regreso, trip_headsign,trip_short_name,direction_id::numeric,service_id::varchar,block_id
+from z_trips zt;
+
+-----
+
+insert into  gtfs_stop_times  
+select stop_id,trip_id,arrival_time,stop_sequence::numeric,stop_headsign,departure_time,drop_off_type::numeric,null as shape_dist_traveled ,
+timepoint::numeric , pickup_type::numeric
+from z_stop_times zst ;
+
+-----
+  
+insert into linea_paradero (id_linea ,  id_paradero )
+SELECT DISTINCT 
+l.id_linea ,  p.id_paradero  
+FROM linea l
+JOIN gtfs_trips t ON l.id_linea =t.id_linea 
+JOIN gtfs_stop_times   st ON t.id_trip = st.id_trip 
+JOIN paradero p   ON st.id_paradero  = p.id_paradero;
+ 
+-----
+
+UPDATE paradero
+SET stop_name = REGEXP_REPLACE(
+    REGEXP_REPLACE(
+        stop_name,
+        '\yentre\y', 
+        '-entre', 
+        'gi'
+    ),
+    '\yesq\y', 
+    '-esq', 
+    'gi'
+);
+
+-----
+update paradero 
+set id_comuna = (select id_comuna from comuna_georeferencia as c where ST_Contains(c.geom, ST_SetSRID(ST_MakePoint(paradero.stop_lon, paradero.stop_lat), 4326)) limit 1)
+where id_comuna is not null;  
+-----
+
+insert into dispositivo 
+select 'QRCode-'||id_paradero , id_paradero , true, null, 3 
+from paradero  as p where id_paradero 
+not in (select id_paradero from dispositivo as d where id_tipo_dispositivo=3 and d.id_paradero = p.id_paradero);
+
+-----
+
+ALTER TABLE linea_paradero ADD CONSTRAINT linea_paradero_id_linea_fkey FOREIGN KEY (id_linea) REFERENCES linea(id_linea);
+
+-----
+
+ALTER TABLE gtfs_stop_times ADD CONSTRAINT fk_gtfs_sto_reference_gtfs_tri FOREIGN KEY (id_trip) REFERENCES gtfs_trips(id_trip) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+-----
+
+ALTER TABLE gtfs_frequencie ADD CONSTRAINT fk_gtfs_fre_reference_gtfs_tri FOREIGN KEY (id_trip) REFERENCES gtfs_trips(id_trip) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+-----
+
+
+update paradero p set id_comuna = (
+select id_comuna from comuna_georeferencia cg
+where st_contains(cg.geom, st_setsrid(st_makepoint(p.stop_lon, p.stop_lat) ,4326))
+limit 1)
+where id_comuna is null;

project/api/management/commands/procesa_zip.py

@@ -13,7 +13,7 @@ class Command(BaseCommand):
     help = 'Procesa los archivos gtfs en formato comprimido (zip)'
 
     def handle(self, *args, **options):
-        # Lógica de tu comando aquí
+      
         folder = config('GTFS_UPLOADS','/tmp')
 
         redes = RedTransporte.objects.filter(vigente=True)
@@ -33,14 +33,14 @@ class Command(BaseCommand):
                 print(f'procesa: {filepath}')
                 procesa_zip(filepath)
 
-                registro_anterior = GtfsArchivo.objects.filter(vigente=True, id_red = red.id_red).first()
+                #registro_anterior = GtfsArchivo.objects.filter(vigente=True, id_red = red.id_red).first()
-                if registro_anterior:
+                #if registro_anterior:
-                    registro_anterior.vigente = False
+                #    registro_anterior.vigente = False
-                    registro_anterior.save()
+                #    registro_anterior.save()
 
-                gtfs_archivo.status = 'GTFS CARGADO'
+                #gtfs_archivo.status = 'GTFS CARGADO'
-                gtfs_archivo.vigente = True
+                #gtfs_archivo.vigente = True
-                gtfs_archivo.save()
+                #gtfs_archivo.save()
 
         self.stdout.write(self.style.SUCCESS('¡Comando ejecutado con éxito!'))
 
@@ -129,7 +129,10 @@ def procesa_tablas_z(cursor):
     current_folder = os.path.dirname(os.path.abspath(__file__))
 
     with open(os.path.join(current_folder, 'actualiza_GTFS.sql'),'r') as file:
-        content = ''.join(file.readlines())
+        sqlFile = file.read()
+        cursor.execute(sqlFile)
+        #cursor.commit() 
+        """ content = ''.join(file.readlines())
 
     arr_sql = content.split('-----')
     for sql in arr_sql:
@@ -137,4 +140,4 @@ def procesa_tablas_z(cursor):
         if sql > ' ':
             print(f'SQL> {sql}', flush=True)
             cursor.execute(sql)
-            print('', flush=True)
+            print('', flush=True) """

project/api/middlewares.py

@@ -14,9 +14,9 @@ class ApiMiddleware:
         if request.path[0:5] != '/api/':
             response = self.get_response(request)
             return response
-        
+
         match = resolve(request.path)
-        logging.error(match)
+        logging.info(match)
 
         # se omite esta regla al mostrar imagen de paradero
         if match.url_name == 'paradero_imagen-detail' and request.method == 'GET':
@@ -56,16 +56,16 @@ class ApiMiddleware:
             return HttpResponse('token ya no es valido', status = 400)
         except jwt.InvalidTokenError:
             return HttpResponse('token es invalido', status = 400)
-        
+
-        if decoded['login'] != '0':        
+        if decoded['login'] != '0':
             usuario = Usuario.objects.filter(login = decoded['login'], vigente = True).values().first()
             if not usuario:
                 return HttpResponse('Usuario ya no vigente', status = 400)
-            
+
             persona = Persona.objects.filter(rut = usuario['rut_id']).values().first()
             if not persona:
                 return HttpResponse('No existe información de la persona', status = 500)
-        
+
             request.jwt_info = { 'login': usuario['login'], 'persona': persona }
         else:
             request.jwt_info = { 'login': '0', 'persona': None }

project/api/models.py

@@ -8,7 +8,6 @@
 from django.db import models
 from .validaciones import rut_valido
 
-
 class Aplicacion(models.Model):
     id_aplicacion = models.IntegerField(primary_key=True)
     nombre_app = models.CharField(max_length=100, blank=False, null=False)
@@ -19,7 +18,6 @@ class Aplicacion(models.Model):
         managed = True
         db_table = 'aplicacion'
 
-
 class Comuna(models.Model):
     id_comuna = models.IntegerField(primary_key=True)
     id_region = models.ForeignKey('Region', models.DO_NOTHING, db_column='id_region', blank=True, null=True)
@@ -29,7 +27,6 @@ class Comuna(models.Model):
         managed = False
         db_table = 'comuna'
 
-
 class Conductor(models.Model):
     patente = models.OneToOneField('Vehiculo', models.DO_NOTHING, db_column='patente', primary_key=True)
     rut = models.ForeignKey('Persona', models.DO_NOTHING, db_column='rut', blank=True, null=True)
@@ -39,7 +36,6 @@ class Conductor(models.Model):
         managed = False
         db_table = 'conductor'
 
-
 class Dispositivo(models.Model):
     id_dispositivo = models.CharField(primary_key=True, max_length=100)
     id_paradero = models.ForeignKey('Paradero', models.DO_NOTHING, db_column='id_paradero', blank=False, null=False)
@@ -51,7 +47,6 @@ class Dispositivo(models.Model):
         managed = False
         db_table = 'dispositivo'
 
-
 class GtfsFrequencie(models.Model):
     id_trip = models.ForeignKey('GtfsTrips', models.DO_NOTHING, db_column='id_trip', blank=True, null=True)
     start_time = models.TimeField(blank=True, null=True)
@@ -63,7 +58,6 @@ class GtfsFrequencie(models.Model):
         managed = False
         db_table = 'gtfs_frequencie'
 
-
 class GtfsPosiciones(models.Model):
     id = models.UUIDField(primary_key=True)
     trip_id = models.UUIDField()
@@ -98,7 +92,7 @@ class GtfsRouteType(models.Model):
 
 
 class GtfsShape(models.Model):
-    id_shape = models.DecimalField(max_digits=18, decimal_places=0)
+    id_shape = models.CharField(max_length=150)
     shape_pt_lat = models.FloatField(blank=True, null=True)
     shape_pt_lon = models.FloatField(blank=True, null=True)
     shape_pt_sequence = models.IntegerField(blank=True, null=True)
@@ -132,7 +126,7 @@ class GtfsStopTimes(models.Model):
 class GtfsTrips(models.Model):
     id_trip = models.CharField(primary_key=True, max_length=150)
     id_linea = models.CharField(max_length=150, blank=True, null=True)
-    id_shape = models.IntegerField(blank=True, null=True)
+    id_shape = models.CharField(max_length=150,blank=True, null=True)
     id_trip_regreso = models.ForeignKey('self', models.DO_NOTHING, db_column='id_trip_regreso', blank=True, null=True)
     trip_headsign = models.CharField(max_length=100, blank=True, null=True)
     trip_short_name = models.CharField(max_length=100, blank=True, null=True)
@@ -177,7 +171,6 @@ class Linea(models.Model):
         managed = False
         db_table = 'linea'
 
-
 class LineaParadero(models.Model):
     id_linea_paradero = models.AutoField(primary_key=True)
     id_linea = models.ForeignKey(Linea, models.DO_NOTHING, db_column='id_linea')
@@ -187,7 +180,6 @@ class LineaParadero(models.Model):
         managed = False
         db_table = 'linea_paradero'
 
-
 class Rol(models.Model):
     id_rol = models.IntegerField(primary_key=True)
     nombre_rol = models.CharField(max_length=100)
@@ -196,7 +188,6 @@ class Rol(models.Model):
         managed = False
         db_table = 'rol'
 
-
 class Operador(models.Model):
     id_operador = models.CharField(primary_key=True, max_length=150)
     id_region = models.ForeignKey('Region', models.DO_NOTHING, db_column='id_region', blank=True, null=True)
@@ -213,7 +204,6 @@ class Operador(models.Model):
         managed = False
         db_table = 'operador'
 
-
 class Paradero(models.Model):
     id_paradero = models.CharField(primary_key=True, max_length=50)
     id_comuna = models.ForeignKey(Comuna, models.DO_NOTHING, db_column='id_comuna', blank=True, null=True)
@@ -235,7 +225,6 @@ class Paradero(models.Model):
         managed = False
         db_table = 'paradero'
 
-
 class ParaderoImagen(models.Model):
     id_paradero_imagen = models.AutoField(primary_key=True)
     id_paradero = models.ForeignKey(Paradero, models.DO_NOTHING, db_column='id_paradero')
@@ -246,7 +235,6 @@ class ParaderoImagen(models.Model):
         managed = False
         db_table = 'paradero_imagen'
 
-
 class Persona(models.Model):
     rut = models.DecimalField(primary_key=True, max_digits=12, decimal_places=0)
     id_tipo_tratamiento = models.ForeignKey('TipoTratamientoPersona', models.DO_NOTHING, db_column='id_tipo_tratamiento', blank=True, null=True)
@@ -263,7 +251,7 @@ class Persona(models.Model):
     class Meta:
         managed = False
         db_table = 'persona'
-    
+
     def save(self, *args, **kwargs):
         # validacion se realiza solo si se esta creando el registro
         if self._state.adding:
@@ -273,7 +261,6 @@ class Persona(models.Model):
 
         super().save(*args, **kwargs)
 
-
 class Region(models.Model):
     id_region = models.IntegerField(primary_key=True)
     nombre_region = models.CharField(max_length=100)
@@ -282,13 +269,10 @@ class Region(models.Model):
         managed = False
         db_table = 'region'
 
-
-
-
 class RolAplicacion(models.Model):
     id_rol_app = models.AutoField(primary_key=True)
     id_aplicacion = models.ForeignKey(Aplicacion, models.DO_NOTHING, db_column='id_aplicacion', blank=False, null=False)
-    id_rol = models.ForeignKey(Rol, models.DO_NOTHING, db_column='id_rol', blank=False, null=False)
+    id_rol = models.ForeignKey(Rol, on_delete=models.CASCADE, db_column='id_rol', blank=False, null=False)
     solo_visualizar = models.BooleanField(blank=True, null=True)
 
     class Meta:
@@ -299,7 +283,7 @@ class RolAplicacion(models.Model):
 class RolOperador(models.Model):
     id_rol_operador = models.AutoField(primary_key=True)
     id_rol = models.ForeignKey(Rol, models.DO_NOTHING, db_column='id_rol')
-    id_operador = models.ForeignKey(Operador, models.DO_NOTHING, db_column='id_operador')
+    id_operador = models.ForeignKey(Operador, on_delete=models.CASCADE, db_column='id_operador')
 
     class Meta:
         managed = True
@@ -326,7 +310,6 @@ class TipoDispositivo(models.Model):
         managed = False
         db_table = 'tipo_dispositivo'
 
-
 class TipoParadero(models.Model):
     id_tipo_paradero = models.IntegerField(primary_key=True)
     descripcion = models.CharField(max_length=100, blank=True, null=True)
@@ -335,7 +318,6 @@ class TipoParadero(models.Model):
         managed = False
         db_table = 'tipo_paradero'
 
-
 class TipoTransporte(models.Model):
     id_tipo_transporte = models.IntegerField(primary_key=True)
     descripcion = models.CharField(max_length=50, blank=True, null=True)
@@ -362,7 +344,6 @@ class TipoTratamientoPersona(models.Model):
         db_table = 'tipo_tratamiento_persona'
         db_table_comment = 'Establece el tratamiento de como dirigirse hacia una persona:\r\nEjemplo\r\nSeñor\r\nSeñora\r\nSrta'
 
-
 class TipoVehiculo(models.Model):
     id_tipo_vehiculo = models.IntegerField(primary_key=True)
     descripcion = models.CharField(max_length=100, blank=True, null=True)
@@ -371,7 +352,6 @@ class TipoVehiculo(models.Model):
         managed = False
         db_table = 'tipo_vehiculo'
 
-
 class Usuario(models.Model):
     login = models.CharField(primary_key=True, max_length=60)
     rut = models.ForeignKey(Persona, models.DO_NOTHING, db_column='rut', blank=True, null=True)
@@ -384,7 +364,6 @@ class Usuario(models.Model):
         managed = False
         db_table = 'usuario'
 
-
 class Vehiculo(models.Model):
     ppu = models.CharField(primary_key=True, max_length=10)
     id_tipo_vehiculo = models.ForeignKey(TipoVehiculo, models.DO_NOTHING, db_column='id_tipo_vehiculo', blank=True, null=True)
@@ -394,7 +373,6 @@ class Vehiculo(models.Model):
         managed = False
         db_table = 'vehiculo'
 
-
 class VehiculoLinea(models.Model):
     patente = models.OneToOneField(Vehiculo, models.DO_NOTHING, db_column='patente', primary_key=True)  # The composite primary key (patente, id_linea) found, that is not supported. The first column is selected.
     id_linea = models.ForeignKey(Linea, models.DO_NOTHING, db_column='id_linea')
@@ -404,7 +382,7 @@ class VehiculoLinea(models.Model):
         managed = False
         db_table = 'vehiculo_linea'
         unique_together = (('patente', 'id_linea'),)
-        
+
 class RedTransporte(models.Model):
     id_red = models.CharField(primary_key=True, max_length=10)
     nombre_red = models.CharField(max_length=100, blank=True, null=True)
@@ -424,7 +402,8 @@ class GtfsArchivo(models.Model):
     archivo = models.CharField(max_length=200, blank=True, null=True)
     ruta_archivo = models.CharField(max_length=200, blank=True, null=True)
     valid_from = models.DateField(blank=True, null=True)
-    created = models.DateTimeField(blank=True, null=True)
+    #created = models.DateTimeField(blank=True, null=True)
+    created = models.DateTimeField(auto_now_add=True)
     usuario = models.CharField(max_length=100, blank=True, null=True)
     vigente = models.BooleanField(blank=True, null=True)
     status = models.CharField(max_length=100, blank=True, null=True)
@@ -434,7 +413,6 @@ class GtfsArchivo(models.Model):
         db_table = 'gtfs_archivo'
         db_table_comment = 'Registro de los archivos GTFS que se cargan en el sistema'
 
-
 class VistaFuncionario(models.Model):
     rut = models.ForeignKey('Persona', models.DO_NOTHING, db_column='rut', blank=True, null=True)
     id_operador = models.ForeignKey('Operador', models.DO_NOTHING, db_column='id_operador', blank=True, null=True)
@@ -454,10 +432,10 @@ class Contrato(models.Model):
         db_table = 'contrato'
 
 class PersonaDatosLaborales(models.Model):
-    rut = models.OneToOneField('Persona', models.DO_NOTHING, db_column='rut', primary_key=True)    
+    rut = models.OneToOneField('Persona', models.DO_NOTHING, db_column='rut', primary_key=True)
     vigencia_clase_licencia = models.DateField(blank=True, null=True)
-    clase_licencia_conducir = models.CharField(max_length=50, blank=True, null=True)     
+    clase_licencia_conducir = models.CharField(max_length=50, blank=True, null=True)
-    profesion = models.CharField(max_length=100, blank=True, null=True)    
+    profesion = models.CharField(max_length=100, blank=True, null=True)
     class Meta:
         managed = False
-        db_table = 'persona_datos_laborales'
+        db_table = 'persona_datos_laborales'

project/api/urls.py

@@ -25,7 +25,7 @@ router.register('paraderos-image', paradero_imagen.ParaderoImagenListView, basen
 router.register('lineas', linea.LineaViewSet)
 router.register('lineas-paradero', linea_paradero.LineaParaderoViewSet)
 router.register('letreros-lur', letrero_lur.LetreroLUR_ViewSet)
-router.register('operadores', operador.OperadorViewSet, basename='operadores')
+router.register('operadores', operador.OperadorViewSet,  basename='operadores')
 router.register('red-transporte', red_transporte.RedTransporteViewSet)
 router.register('gtfs-archivo', gtfs_archivo.GtfsArchivoViewSet)
 router.register('roles', rol.RolViewSet)
@@ -41,5 +41,6 @@ urlpatterns = [
     path('auth/nueva-contrasena/', auth.nueva_contrasena, name='auth_contrasena'),
     path('mapas/paraderos/', mapa.paraderos, name='mapa-paraderos'),
     path('mapas/rutas/', mapa.rutas, name='mapa-rutas'),
-    path('upload/zip/', upload.upload_zip, name='upload_zip'),
+    path('mapas/coordenadas/', mapa.coordenadas, name='mapa-coordenadas'),
+    path('upload/zip/', upload.upload_zip, name='upload_zip'),  
 ]

project/api/views/auth.py

@@ -32,21 +32,21 @@ def jwt_login(request):
         if rut != '0':
             dv = rut[-1].upper()
             rut = rut[:-1]
-        
+
         usuario = None
 
         if rut == '0' and password == '0':
             usuario = { 'login': '0', 'clave': '0' }
-            
+
             # solo se permite usuario 0 si no existen usuarios vigentes
             count = models.Usuario.objects.filter(vigente = True).count()
             if count > 0:
-                return HttpResponse('Acceso no valido', status=400)
+                return HttpResponse('Acceso no valido 1', status=400)
         else:
             usuario = models.Usuario.objects.filter(vigente=1, rut__rut=rut, rut__dv=dv).values().first()
 
             if not check_password(input['password'], usuario['clave']):
-                return HttpResponse('Acceso no valido', status=400)
+                return HttpResponse('Acceso no valido 2', status=400)
 
         ahora = datetime.utcnow()
         manana = ahora + timedelta(days=1)
@@ -79,10 +79,10 @@ def recuperar(request):
     usuario = models.Usuario.objects.filter(rut=rut, vigente=True).first()
 
     if usuario == None or persona == None:
-        return HttpResponse('Acceso no valido', status=400)
+        return HttpResponse('El usuario no existe', status=400)
 
     if persona.email != input['email'].lower():
-        return HttpResponse('Acceso no valido', status=400)
+        return HttpResponse('El correo electrónico no es el registrado para el usuario', status=400)
 
     codigo_aleatorio = random.randint(100000, 999999)
     ahora = datetime.utcnow()
@@ -112,6 +112,7 @@ def recuperar(request):
 def info_token(request):
     input = json.loads(request.body)
     token = input['token']
+    logging.warning(input)
     try:
         decoded = jwt.decode(token, SECRET_KEY, algorithms=["HS256"])
         persona = models.Persona.objects.filter(rut=decoded['rut']).first()
@@ -158,31 +159,27 @@ def nueva_contrasena(request):
         return HttpResponse('error al cambiar contraseña', status = 500)
 
 
-
-    
 def enviar_correo(destinatario, asunto, contenido):
     try:
         template = get_template('correo_recuperar.html')  # Ruta al template del correo
         contenido_renderizado = template.render(contenido)
 
-        mensaje = EmailMultiAlternatives(asunto, '', settings.EMAIL_HOST_USER, [destinatario])
+        mensaje = EmailMultiAlternatives(asunto, '', settings.EMAIL_SENDER, [destinatario])
         mensaje.attach_alternative(contenido_renderizado, 'text/html')
         mensaje.send()
         return True
     except Exception as e:
-        print(f'EMAIL_HOST: {EMAIL_HOST}', flush=True)
+        print(f'EMAIL_HOST: {settings.EMAIL_HOST}', flush=True)
         print(f'ERROR: {e}', flush=True)
         return False
 
 
-
+def http_referer(request, clean=False):
-
+    if 'HTTP_REFERER' in request.META and clean==False:
-def http_referer(request):
-    if 'HTTP_REFERER' in request.META:
         referer = request.META['HTTP_REFERER']
     else:
         protocol = request.scheme
         host = request.META['HTTP_HOST']
         port = request.META['SERVER_PORT']
         referer = f'{protocol}://{host}'
-    return referer
+    return referer

project/api/views/contrato.py

@@ -1,7 +1,6 @@
 
 from django.db import transaction
 from django.http import HttpResponse, JsonResponse
-from django.contrib.auth.hashers import make_password
 from rest_framework import viewsets
 from rest_framework.response import Response
 from rest_framework.decorators import action
@@ -23,7 +22,6 @@ class ContratoViewSet(viewsets.ModelViewSet):
         try:
             with transaction.atomic():
                 input = json.loads(request.body)
-
                 persona = models.Persona.objects.filter(rut = input['rut']).first()
                 id_tipo_tratamiento = input.get('id_tipo_tratamiento',None)
                 if not id_tipo_tratamiento:

project/api/views/dispositivo.py

@@ -1,9 +1,10 @@
 
 from rest_framework import viewsets
+from rest_framework.decorators import action
 from django.http import JsonResponse
 from django.db.models import F
+from django.db import connection
 from django_filters.rest_framework import DjangoFilterBackend
-from rest_framework.decorators import action
 from api import models, serializers
 from os import getenv
 import redis
@@ -17,7 +18,7 @@ class DispositivoViewSet(viewsets.ModelViewSet):
     queryset = models.Dispositivo.objects.all()
     serializer_class = serializers.DispositivoSerializer
     filter_backends = [DjangoFilterBackend]
-    filterset_fields = ['id_paradero', 'id_tipo_dispositivo']
+    filterset_fields = ['id_dispositivo', 'id_paradero', 'id_tipo_dispositivo']
 
 
     @action(detail=False, methods=['post'])
@@ -50,12 +51,13 @@ class DispositivoViewSet(viewsets.ModelViewSet):
         })
 
 
-    @action(detail=False, methods=['post'])
+    @action(detail=False, methods=['post','get'])
     def getInfoDevice(self, request, pk=None):
         input = json.loads(request.body)
         getInfoDevice = input['GetInfoDevice']
 
-        record = models.Paradero.objects \
+        cursor = connection.cursor()
+        paradero = models.Paradero.objects \
             .filter(dispositivo__id_dispositivo=getInfoDevice['idDispositivo']) \
             .annotate(nro_paradero=F('id_paradero'), nombre_paradero=F('stop_name')) \
             .first()
@@ -64,58 +66,171 @@ class DispositivoViewSet(viewsets.ModelViewSet):
         db_port = getenv('DB_REDIS_PORT')
         r = redis.Redis(host=db_host, port=db_port, decode_responses=True)
 
-        key = f'stop_id:{record.nro_paradero}'
+        key = f'stop_id:{paradero.nro_paradero}'
         json_trayectos = r.get(key)
+        trayectos = []
         if json_trayectos != None:
             trayectos = json.loads(json_trayectos)
-        else:
-            trayectos = []
 
+        key = f'stop_id:none'
+        json_trayectos = r.get(key)
+        trayectos_none = []
+        if json_trayectos != None:
+            trayectos_none = json.loads(json_trayectos)
 
-        lineas = {}
+        # agregar al listado los trayecto en donde no se registro el paradero
-        lineas_agrupadas = {}
+        """
-        for t in trayectos:
+        json_trayectos = r.get('stop_id:none')
-
+        trayectos_none = []
-            pk_linea = f'{t["route_id"]}-{t["direction_id"]}'
+        if json_trayectos != None:
-            if pk_linea not in lineas:
+            trayectos_none = json.loads(json_trayectos)
-                lineas[pk_linea] = models.Linea.objects.filter(id_linea=pk_linea).first()
-
-            linea = lineas[pk_linea]
-
-            if linea == None:
-                logging.error(pk_linea)
-
-            if linea != None:
-                if pk_linea not in lineas_agrupadas:
-                    lineas_agrupadas[pk_linea] = {
-                        'Linea': linea.route_long_name,
-                        'Descripcion': linea.route_short_name,
-                        'TipoLocomocion': linea.route_type.descripcion,
-                        'colorFondo': linea.route_color,
-                        'colorTexto': linea.route_text_color,
-                        'Llegadas': []
-                    }
-
-                lineas_agrupadas[pk_linea]['Llegadas'].append({
-                    'patente': t['vehicle_license_plate'],
-                    'Planificada': None,
-                    'EstimadaGPS': t['hora_llegada'],
-                    'DistanciaGPS': None,
-                    'Mensajelinea': None,
-                })
 
+        for trayecto in trayectos_none:
+            trayecto['stop_id'] = 'none'
+            trayecto['hora_llegada'] = None
+            trayectos.append(trayecto)
 
+ """
+        lineas = models.Linea.objects.filter(lineaparadero__id_paradero = paradero.id_paradero).order_by('route_short_name')
         detalle_lineas = []
-        for pk_linea in lineas_agrupadas:
+        for linea in lineas:
-            detalle_lineas.append(lineas_agrupadas[pk_linea])
+            llegadas = []
+
+            for trayecto in trayectos:
+                id_linea = f"{trayecto['route_id']}-{trayecto['direction_id']}"
+                stop_id = trayecto['stop_id']
+                patente = trayecto['vehicle_license_plate']
+                trayecto['stop_id_alterno'] = None
+
+                if id_linea == linea.id_linea:
+                    hora_llegada = trayecto['hora_llegada']
+                    distancia_km = None
+                    texto_llegada= None
+
+                    # si no trae latitud ni longitud: buscar en trayectos_none por patente
+                    # y sobreescribir latitud y longitud
+                    if trayecto['latitude'] == 0 and trayecto['longitude'] == 0:
+                        for trayecto_none in trayectos_none:
+                            if patente == trayecto_none['vehicle_license_plate']:
+                                trayecto['longitude'] = trayecto_none['longitude']
+                                trayecto['latitude'] = trayecto_none['latitude']
+                                trayecto['stop_id_alterno'] = 'none'
+                                break
+
+                    if trayecto['latitude'] != 0 and trayecto['longitude'] != 0:
+                        trip_id = trayecto['trip_id']
+                        velocidad_promedio = 80
+                        p_lon = trayecto['longitude']
+                        p_lat = trayecto['latitude']
+
+                        sql = "select hora_llegada, distancia_km, texto_llegada from fn_gtfs_calcula_distancia_tiempo_llegada(%s,%s,%s,%s,%s)"
+                        cursor.execute(sql, [trip_id, stop_id, velocidad_promedio, p_lon, p_lat])
+                        row = cursor.fetchone()
+
+                        """ if patente == 'FDCB32':
+                            print(f'SQL: {sql}', flush=True)
+                            print(f'params: {[trip_id, stop_id, velocidad_promedio, p_lon, p_lat]}', flush=True)
+                            print(f'row: {row}', flush=True) """
+
+                        if row != None:
+                            hora_llegada = row[0].strftime('%H:%M:%S')
+                            distancia_km = row[1]
+                            texto_llegada= row[2]
+                        if distancia_km == None:
+                            distancia_km == 999
+
+
+                    llegadas.append({
+                        'patente': trayecto['vehicle_license_plate'],
+                        'Planificada': trayecto['hora_llegada'],
+                        'Latitud': trayecto['latitude'],
+                        'Longitud': trayecto['longitude'],
+                        'stop_id_alterno': trayecto['stop_id_alterno'],
+                        'EstimadaGPS': hora_llegada,
+                        'DistanciaGPS': distancia_km,
+                        'textoLlegada' : texto_llegada,
+                        'Mensajelinea': None,
+                    })
+
+            try:
+                llegadas_ordendas = sorted(llegadas, key=lambda x: x['DistanciaGPS'])
+            except:
+                logging.debug("No se pueden ordenar: {}".format(llegadas))
+                llegadas_ordendas = llegadas
+
+
+            item = {
+                'Linea': linea.route_long_name,
+                'Descripcion': linea.route_short_name,
+                'TipoLocomocion': linea.route_type.descripcion,
+                'colorFondo': linea.route_color,
+                'colorTexto': linea.route_text_color,
+                'Llegadas': llegadas_ordendas
+            }
+            detalle_lineas.append(item)
+
+        # lineas = {}
+        # lineas_agrupadas = {}
+        # for t in trayectos:
+
+        #     pk_linea = f'{t["route_id"]}-{t["direction_id"]}'
+        #     if pk_linea not in lineas:
+        #         lineas[pk_linea] = models.Linea.objects.filter(id_linea=pk_linea).first()
+
+        #     linea = lineas[pk_linea]
+
+        #     if linea == None:
+        #         print(pk_linea, flush=True)
+
+        #     if linea != None:
+        #         if pk_linea not in lineas_agrupadas:
+        #             lineas_agrupadas[pk_linea] = {
+        #                 'Linea': linea.route_long_name,
+        #                 'Descripcion': linea.route_short_name,
+        #                 'TipoLocomocion': linea.route_type.descripcion,
+        #                 'colorFondo': linea.route_color,
+        #                 'colorTexto': linea.route_text_color,
+        #                 'Llegadas': []
+        #             }
+
+        #         sql = "select hora_llegada, distancia_km from fn_gtfs_calcula_distancia_tiempo_llegada(%s,%s,%s,%s,%s)"
+
+        #         trip_id = t['trip_id']
+        #         paradero_id = t['stop_id']
+        #         velocidad_promedio = 30
+        #         p_lon = t['longitude']
+        #         p_lat = t['latitude']
+
+        #         cursor.execute(sql, [trip_id, paradero_id, velocidad_promedio, p_lon, p_lat])
+        #         row = cursor.fetchone()
+        #         hora_llegada = None
+        #         distancia_km = None
+        #         if row != None:
+        #             hora_llegada = row[0].strftime('%H:%M:%S')
+        #             distancia_km = row[1]
+
+        #         lineas_agrupadas[pk_linea]['Llegadas'].append({
+        #             'patente': t['vehicle_license_plate'],
+        #             'Planificada': t['hora_llegada'],
+        #             'EstimadaGPS': hora_llegada,
+        #             'DistanciaGPS': distancia_km,
+        #             'Mensajelinea': None,
+        #         })
+
+
+        # detalle_lineas = []
+        # for pk_linea in lineas_agrupadas:
+        #     detalle_lineas.append(lineas_agrupadas[pk_linea])
+
 
         save_log_dispositivo(id_dispositivo=getInfoDevice['idDispositivo'], accion_url='getInfoDevice')
-
+        cursor.close()
         return JsonResponse({
             "GetInfoDeviceResponse": {
                 "DetalleLineas": detalle_lineas,
                 "MensajeParadero": "No considerar, uso futuro",
-                "NroParadero": record.nro_paradero,
+                "NroParadero": paradero.nro_paradero,
-                "NombreParadero": record.nombre_paradero
+                "NombreParadero": paradero.nombre_paradero,
+                "proto": trayectos,
             }
         })

project/api/views/gtfs_archivo.py

@@ -2,19 +2,24 @@
 from rest_framework import viewsets
 from rest_framework.decorators import action
 from django_filters.rest_framework import DjangoFilterBackend
+from rest_framework.filters import OrderingFilter 
 from django.core.files.storage import FileSystemStorage
 from django.http import FileResponse, HttpResponse
 from api import models , serializers
 from decouple import config
+
+ 
+
 import os
 import logging
 
 class GtfsArchivoViewSet(viewsets.ModelViewSet):
     queryset = models.GtfsArchivo.objects.all()
     serializer_class = serializers.GtfsArchivoSerializer
-    filter_backends = [DjangoFilterBackend]
+    filter_backends = [DjangoFilterBackend, OrderingFilter]
     filterset_fields = ['id_gtfs', 'id_red', 'archivo','valid_from','created','usuario','vigente','status']
-    
+    ordering_fields = ['id_gtfs', 'id_red', 'archivo', 'valid_from', 'created', 'usuario', 'vigente', 'status']
+    ordering = ['id_gtfs'] 
 
     def create(self, request, *args, **kwargs):
         fs = FileSystemStorage(location = config('GTFS_UPLOADS','/tmp'))

project/api/views/linea.py

@@ -1,6 +1,6 @@
 
 from rest_framework import viewsets
-# from rest_framework.response import Response
+from rest_framework.response import Response
 from rest_framework.decorators import action
 from rest_framework.filters import OrderingFilter
 from django_filters.rest_framework import DjangoFilterBackend
@@ -46,6 +46,57 @@ class LineaViewSet(viewsets.ModelViewSet):
         return JsonResponse(list(paraderos), safe=False)
 
 
+    # @action(detail=False, methods=['get'])
+    def ___buses(self, request, pk=None):
+        pk = request.GET['id_linea']
+
+        # 1. obtener todas los paraderos de redis
+        # 2. consultar en cada paradero si hay una linea que corresponda a la consultada
+        # 3. si existe se agrega el vehiculo
+
+        db_host = getenv('DB_REDIS_HOST')
+        db_port = getenv('DB_REDIS_PORT')
+        r = redis.Redis(host=db_host, port=db_port, decode_responses=True)
+
+        fileproto = r.get('fileproto')
+        paraderos_redis = r.keys('stop_id:*')
+        object_buses = {}
+        for key_paradero in paraderos_redis:
+            string_trayectos = r.get(key_paradero)
+            array_trayectos = json.loads(string_trayectos)
+
+            for trayecto in array_trayectos:
+                patente = trayecto['vehicle_license_plate']
+                id_linea = f"{trayecto['route_id']}-{trayecto['direction_id']}"
+
+                if id_linea == pk and key_paradero == 'stop_id:none':
+                    print(trayecto, flush=True)
+                    object_buses[patente] = {
+                        'Patente_vehiculo': trayecto["vehicle_license_plate"],
+                        'latitude': trayecto["latitude"],
+                        'longitude': trayecto["longitude"],
+                        'speed': trayecto["speed"]
+                    }
+
+                if id_linea == pk and patente not in object_buses:
+                    object_buses[patente] = {
+                        'Patente_vehiculo': trayecto["vehicle_license_plate"],
+                        'latitude': trayecto["latitude"],
+                        'longitude': trayecto["longitude"],
+                        'speed': trayecto["speed"]
+                    }
+
+        array_buses = []
+        for patente in object_buses:
+            array_buses.append(object_buses[patente])
+        
+        return Response({
+            'fileproto': fileproto,
+            'buses': array_buses
+        })
+
+
+
     @action(detail=False, methods=['get'])
     def buses(self, request, pk=None):
         pk = request.GET['id_linea']
@@ -53,7 +104,7 @@ class LineaViewSet(viewsets.ModelViewSet):
         db_host = getenv('DB_REDIS_HOST')
         db_port = getenv('DB_REDIS_PORT')
         r = redis.Redis(host=db_host, port=db_port, decode_responses=True)
-
+    
         paraderos = models.Paradero.objects \
             .filter(vigente=True, lineaparadero__id_linea=pk) \
             .values('id_paradero') \
@@ -79,6 +130,23 @@ class LineaViewSet(viewsets.ModelViewSet):
                                 'longitude': trayecto["longitude"],
                                 'speed': trayecto["speed"]
                             }
+
+            key = f'stop_id:none'
+            data = r.get(key)
+
+            if data != None:
+                array_trayectos = json.loads(data)
+                for trayecto in array_trayectos:
+                    key_route = f'{trayecto["route_id"]}-{trayecto["direction_id"]}'
+                    if key_route == pk:
+                        patente = trayecto["vehicle_license_plate"]
+                        if (patente not in obj_buses) or (patente in obj_buses and obj_buses[patente]['longitude'] == 0 and obj_buses[patente]['latitude'] == 0):
+                            obj_buses[patente] = {
+                                'Patente_vehiculo': trayecto["vehicle_license_plate"],
+                                'latitude': trayecto["latitude"],
+                                'longitude': trayecto["longitude"],
+                                'speed': trayecto["speed"]
+                            }
         
         for patente in obj_buses:
             array_buses.append(obj_buses[patente])
@@ -97,8 +165,6 @@ class LineaViewSet(viewsets.ModelViewSet):
             .filter(id_linea=pk) \
             .first()
 
-        logging.error(linea)
-
         detalle_buses = []
         paraderos = models.Paradero.objects \
             .filter(vigente=True, lineaparadero__id_linea=pk) \
@@ -172,8 +238,11 @@ class LineaViewSet(viewsets.ModelViewSet):
         return JsonResponse(data, safe=False)
 
 
+    
+
+
     @action(detail=False, methods=['get'])
-    def count_buses_recorridos(self, request, pk=None):
+    def count_buses_recorridos_old(self, request, pk=None):
         query = "select count(distinct vehicle_license_plate) from gtfs_posiciones"
 
         with connection.cursor() as cursor:
@@ -181,4 +250,81 @@ class LineaViewSet(viewsets.ModelViewSet):
             result = cursor.fetchone()
 
         return JsonResponse({ 'count': result[0] })
+    
 
+    @action(detail=False, methods=['get'])
+    def count_lineas_correctas(self, request, pk=None):
+        query = """
+                SELECT 'Total Rutas correctas' AS titulo, COUNT(*) - (
+                    SELECT COUNT(*) AS cantidad 
+                    FROM gtfs_validaciones gv 
+                    WHERE id_gtfs IN (
+                        SELECT id_gtfs 
+                        FROM gtfs_archivo 
+                        WHERE NOT vigente
+                    )
+                ) AS cantidad 
+                FROM linea 
+                WHERE vigente
+
+                UNION
+
+                SELECT 'Total Rutas con Inconsistencias' AS titulo, COUNT(*) AS cantidad 
+                FROM gtfs_validaciones gv 
+                WHERE id_gtfs IN (
+                    SELECT id_gtfs 
+                    FROM gtfs_archivo 
+                    WHERE vigente
+                )
+                """
+
+        try:
+            with connection.cursor() as cursor:
+                cursor.execute(query)
+                result = cursor.fetchall()
+
+            data = []
+            for row in result:
+            # Cada 'row' es una tupla con dos elementos: titulo y cantidad
+                data.append({
+                    'titulo': row[0],
+                    'cantidad': row[1]
+                })
+
+            return JsonResponse(data, safe=False)  # Usar safe=False para permitir objetos no diccionario en la raíz
+        except Exception as e:
+            return JsonResponse({'error': str(e)}, status=400)
+    
+
+
+
+    @action(detail=False, methods=['get'])
+    def count_buses_recorridos(self, request, pk=None):
+  
+        db_host = getenv('DB_REDIS_HOST')
+        db_port = getenv('DB_REDIS_PORT')
+        r = redis.Redis(host=db_host, port=db_port, decode_responses=True)
+        key = f'stop_id:none'
+        data = r.get(key)      
+        data = json.loads(data)
+        unique_vehicle_plates = set(item["vehicle_license_plate"] for item in data)        
+        count_unique_vehicle_plates = len(unique_vehicle_plates)         
+        return JsonResponse({ 'count': count_unique_vehicle_plates })
+    
+    
+    @action(detail=False, methods=['get'])
+    def count_rutas_buses(self, request, pk=None):
+  
+        db_host = getenv('DB_REDIS_HOST')
+        db_port = getenv('DB_REDIS_PORT')
+        r = redis.Redis(host=db_host, port=db_port, decode_responses=True)
+        key = f'stop_id:none'
+        data = r.get(key)      
+        data = json.loads(data)
+        unique_route_direction = set(
+            (item["route_id"], item["direction_id"]) for item in data
+        ) 
+        count_unique_route_direction = len(unique_route_direction)              
+        return JsonResponse({ 'count': count_unique_route_direction })
+    
+   

project/api/views/mapa.py

@@ -6,10 +6,12 @@ from django.views.decorators.csrf import csrf_exempt
 from api.models import Paradero
 from api.models import GtfsShape, GtfsTrips
 from logging import error
+from decouple import config
 
 google_api_key = 'AIzaSyDnFO9w_SsodjBuY5tOK8-kQJns_l5klQ4'
 center = {'lat': -36.8077884, 'lng': -73.0775401}
 
+
 @csrf_exempt
 @action(detail=False, methods=['get'])
 @api_view(['GET'])
@@ -86,4 +88,19 @@ def rutas(request):
     return JsonResponse({
         'google_api_key': google_api_key,
         'positions': list(resultados)
+    })
+
+ 
+@action(detail=False, methods=['get'])
+@api_view(['GET'])
+
+def coordenadas(request):
+    # Obtener las coordenadas iniciales desde las variables de entorno
+    initial_lat = config('COORDINI_LAT', default=-36.8270)
+    initial_lng = config('COORDINI_LNG', default=-73.0503)
+
+    # Enviar las coordenadas iniciales como parte de la respuesta
+    return JsonResponse({
+        'initialLat': initial_lat,
+        'initialLng': initial_lng, 
     })

project/api/views/operador.py

@@ -8,6 +8,7 @@ from django.db.models import Max
 from django.db import transaction
 from api import models, serializers
 import logging
+from decouple import config
 
 class OperadorViewSet(viewsets.ModelViewSet):
     # queryset = models.Operador.objects.all()
@@ -35,30 +36,52 @@ class OperadorViewSet(viewsets.ModelViewSet):
                 "No se puede crear el operador: 'id_operador' no proporcionado.",
                 status=HTTP_400_BAD_REQUEST
             )
-        
+        newRecord= False;
+        if not models.Operador.objects.filter(id_operador=id_operador).exists():
+            newRecord = True
+       
         try:
             with transaction.atomic():
+
+                #Se Define Operador, pero no se graba
                 operador = models.Operador(
-                    id_operador = id_operador,
+                        id_operador = id_operador,
-                    nombre_operador = request.data.get('nombre_operador'),
+                        nombre_operador = request.data.get('nombre_operador'),
-                    vigente = request.data.get('vigente'),
+                        vigente = request.data.get('vigente'),                
-                )
+                        )    
+
+                if newRecord:
+                     # se crea el rol para el operador
+                    max_id = models.Rol.objects.aggregate(Max('id_rol'))['id_rol__max']
+                    rol = models.Rol(
+                        id_rol = max_id + 1,
+                        nombre_rol = f'Rol {operador.nombre_operador}'
+                    )
+                    rol.save()
+                    operador.id_rol = rol
+                
+                #Grabamos Operador, ya que si corresponde se le asigno el id del nuevo Rol
                 operador.save()
+                
+                 
+                if newRecord :
+                    app_default_operador_str = config('APP_DEFAULT_OPERADOR')
+                    app_default_operador = [int(x) for x in app_default_operador_str.split(',')]
+ 
+                    for id_aplicacion in app_default_operador:                   
+                        aplicacion = models.Aplicacion.objects.get(id_aplicacion=id_aplicacion)
+                        rolApp = models.RolAplicacion(
+                            id_rol=rol,
+                            id_aplicacion=aplicacion
+                        )
+                        rolApp.save()
 
-                # se crea el rol para el operador
+                    rol_operador = models.RolOperador(
-                max_id = models.Rol.objects.aggregate(Max('id_rol'))['id_rol__max']
+                            id_rol = rol,
-
+                            id_operador = operador
-                rol = models.Rol(
+                            )
-                    id_rol = max_id + 1,
+                    rol_operador.save() 
-                    nombre_rol = f'Rol {operador.nombre_operador}'
+                    newRecord =False                
-                )
-                rol.save()
-
-                rol_operador = models.RolOperador(
-                    id_rol = rol,
-                    id_operador = operador
-                )
-                rol_operador.save()
 
                 return Response({
                     'id_operador': operador.id_operador,

project/api/views/paradero.py

@@ -3,7 +3,9 @@ from rest_framework import viewsets
 from rest_framework.decorators import action
 from api.serializers import ParaderoSerializer
 from api.models import Paradero, Dispositivo
+from decouple import config
 import logging
+from django.db.models import Count
 
 class ParaderoViewSet(viewsets.ModelViewSet):
     queryset = Paradero.objects.all()
@@ -24,7 +26,17 @@ class ParaderoViewSet(viewsets.ModelViewSet):
             pass
         
         return JsonResponse({ 'count': queryset.count() })
+        
+    @action(detail=False, methods=['get'], url_path='count_by_comuna')
+    def count_by_comuna(self, request, pk=None):
+            # Solo paraderos vigentes
+            queryset = Paradero.objects.filter(vigente=True)
 
+            # Conteo agrupado por comuna
+            count_by_comuna = queryset.values('id_comuna__nombre_comuna').annotate(total=Count('id_paradero')).order_by('id_comuna')
+
+            return JsonResponse({'count_by_comuna': list(count_by_comuna)})
+        
     @action(detail=False, methods=['get'], url_path='info-public/(?P<pk>\S+)')
     def info_public(self, request, pk=None):
         if 'HTTP_REFERER' in request.META:
@@ -41,7 +53,8 @@ class ParaderoViewSet(viewsets.ModelViewSet):
 
         url = None
         if record != None:
-            url = f'https://transporte-paradero.hz.kursor.cl/rutaParadero/?id={record.id_dispositivo}'
+            base_url = config('URL_PARADERO', 'http://localhost/')
+            url = f'{base_url}{record.id_dispositivo}'
 
 
         # url = f'{referer}/public/infoStop?codigoParadero={pk}'

project/project/settings.py

@@ -24,7 +24,7 @@ BASE_DIR = Path(__file__).resolve().parent.parent
 # See https://docs.djangoproject.com/en/4.2/howto/deployment/checklist/
 
 # SECURITY WARNING: keep the secret key used in production secret!
-SECRET_KEY = 'django-insecure-ozq@8*t6cy&$lmu@qsvz+l6omsfncj6r1w)s**rtl3vd&j8_#b'
+SECRET_KEY = config('SECRET_KEY')
 
 # SECURITY WARNING: don't run with debug turned on in production!
 DEBUG = True
@@ -46,6 +46,7 @@ INSTALLED_APPS = [
     'corsheaders',
     'django_filters',
     'api',
+    'public',
     'logger',
 ]
 
@@ -180,7 +181,10 @@ LOGGING = {
 
 
 EMAIL_HOST = config('SMTP_HOST')
-EMAIL_PORT = config('SMTP_PORT', 587)
+EMAIL_PORT = config('SMTP_PORT')
-EMAIL_HOST_USER = config('SMTP_USER', 'tu_correo@gmail.com')  # Tu dirección de correo
+EMAIL_SENDER = config('SMTP_FROM')
-EMAIL_HOST_PASSWORD = config('SMTP_PASS', 'tu_contraseña')  # Tu contraseña de correo
+
-EMAIL_USE_TLS = config('SMTP_PROTOCOL') == 'tls'
+
+#EMAIL_HOST_USER = config('SMTP_USER', 'tu_correo@gmail.com')  # Tu dirección de correo
+#EMAIL_HOST_PASSWORD = config('SMTP_PASS', 'tu_contraseña')  # Tu contraseña de correo
+EMAIL_USE_TLS = config('SMTP_PROTOCOL') == 'tls'

project/project/urls.py

@@ -29,6 +29,7 @@ urlpatterns = [
     # BACKEND
     path('admin/', admin.site.urls),
     path('api/', include('api.urls')),    
+    path('api-public/', include('public.urls')),
     path('docs/', include_docs_urls(title = 'API Documentation')),
 ]#+ static(settings.MEDIA_URL, document_root=settings.MEDIA_ROOT)
 

project/public/admin.py

@@ -0,0 +1,3 @@
+from django.contrib import admin
+
+# Register your models here.

project/public/apps.py

@@ -0,0 +1,6 @@
+from django.apps import AppConfig
+
+
+class PublicConfig(AppConfig):
+    default_auto_field = 'django.db.models.BigAutoField'
+    name = 'public'

project/public/models.py

@@ -0,0 +1,3 @@
+from django.db import models
+
+# Create your models here.

project/public/tests.py

@@ -0,0 +1,3 @@
+from django.test import TestCase
+
+# Create your tests here.

project/public/urls.py

@@ -0,0 +1,10 @@
+from django.urls import path, include
+from rest_framework import routers
+from api.views.dispositivo import DispositivoViewSet
+
+router = routers.DefaultRouter()
+router.register('paradero_info', DispositivoViewSet)
+
+urlpatterns = [
+    path('', include(router.urls)),
+]

project/public/views.py

@@ -0,0 +1,6 @@
+from django.shortcuts import render
+from api.views.dispositivo import DispositivoViewSet
+
+# Create your views here.
+class DispositivoPublicViewSet(DispositivoViewSet):
+    pass

readme.md

@@ -68,6 +68,50 @@ gunzip -c "$LASTFILEDUMP" | psql -U postgres "$POSTGRES_DB"
 ~~~
 
 
+# archivo para compilar imagen: Dockerfile
+
+~~~docker
+# seccion frontend
+FROM node:20-alpine as frontend
+ARG gitusername
+ARG gitpassword
+
+# soluciona error en descarga de repositorio
+RUN sed -i 's/https\:\/\//http\:\/\//g' /etc/apk/repositories
+
+# descargar proyecto
+RUN apk update && apk add git
+RUN git clone "https://${gitusername}:${gitpassword}@gitlab.com/m3f_usm/admin_transporte/frontend" /frontend
+RUN git clone "https://${gitusername}:${gitpassword}@gitlab.com/m3f_usm/admin_transporte/backend" /backend
+RUN rm -rf /backend/project/dist
+
+# compilar
+WORKDIR /frontend
+RUN npm install && npm run build
+
+# seccion final -- se omite git y archivos innecerarios para reducir imagen
+FROM python:3.11-alpine
+COPY --from=frontend /backend/project/ /app/project
+COPY --from=frontend /backend/readme.md /app
+COPY --from=frontend /backend/requirements.txt /app
+COPY --from=frontend /frontend/dist/ /app/project/dist
+
+WORKDIR /app
+RUN pip install -r requirements.txt
+ENTRYPOINT ["python3"]
+CMD [ "/app/project/manage.py", "runserver", "0.0.0.0:4000" ]
+~~~
+
+# instrucciones para compilar archivo Dockerfile
+
+~~~bash
+docker build -t <tagname:version> -f Dockerfile --no-cache --build-arg gitusername=<usuario gitlab> --build-arg gitpassword=<password gitlab> .
+
+# ejemplo
+# docker build -t transporte:v1.2 -f Dockerfile --no-cache --build-arg gitusername=johndoe --build-arg gitpassword=mypassword .
+~~~
+
+
 # archivo produccion: docker-compose.yml
 
 ~~~yml
@@ -76,25 +120,126 @@ name: transporte
 
 services:
 
+  transporte:
+      image: transporte:v1.2
+      depends_on:
+        - db
+      environment:
+        - PORT=4000
+      ports:
+        - 4000:4000
+      volumes:
+        - ./uploads:/uploads
+        #- .env.prod:/app/.env       # (opcional: indicar conexiones a base datos en otro servidor)
+
+  db:
+    image: postgis/postgis:14-3.4-alpine
+    volumes:
+      - db:/var/lib/postgresql/data
+      - ./dumps:/dumps
+      - ./restore_db.sh:/docker-entrypoint-initdb.d/02_restore_db.sh
+    ports:
+      - 5432:5432
+    environment:
+      POSTGRES_PASSWORD: password
+      POSTGRES_DB: transporte
+      PGDATA: /var/lib/postgresql/data/pgdata
+
+  proto:
+    image: python:3.11-alpine
+    environment:
+      - DB_REDIS_HOST=dbproto
+      - DB_REDIS_PORT=6379
+      - TZ=America/Santiago
+    volumes:
+      - ./proto:/app
+      - venv_proto:/root/venv
+    working_dir: /app
+    command: sh -c "
+      [ -d /root/venv/bin ] || ( \
+          python -m venv /root/venv/ \
+          && . /root/venv/bin/activate \
+          && pip install -r requirements.txt \
+      ) ;
+
+      . /root/venv/bin/activate ;
+
+      cd /app ;
+      [ -f .env ] || ( [ -f .env.develop ] && cp .env.develop .env ) ;
+      watch -n 30 -t python main.py
+      "
+
+  dbproto:
+    image: redis:7.2-alpine
+    volumes:
+      - dbredis:/data
+
+  dbmongo:
+    image: mongo:jammy
+    volumes:
+      - dbmongo:/data/db
+      - dbmongoconfig:/data/configdb
+    ports:
+      - 27017:27017
+    environment:
+      MONGO_INITDB_ROOT_USERNAME: root
+      MONGO_INITDB_ROOT_PASSWORD: password
+
+
+volumes:
+  db:
+  dbredis:
+  dbmongo:
+  dbmongoconfig:
+  venv_proto:
+~~~
+
+<br>
+<br>
+<br>
+
+# tunnel para desarrollo
+
+Ver información en:
+
+https://gitlab.com/m3f_usm/admin_transporte/backend/-/wikis/home
+
+
+# archivo desarrollo: docker-compose.yml 
+
+~~~yml
+version: '3'
+name: transporte
+
+services:
+
+  frontend:
+    image: node:18-alpine
+    volumes:
+      - ./frontend:/app
+    ports:
+      - 3000:3000
+    environment:
+      - VITE_PORT=3000
+      - VITE_BACKEND=http://localhost:4000/api
+    working_dir: /app
+    command: sh -c "
+      [ ! -d node_modules ] && npm install ;
+      chmod -R o+w . ;
+      npm run dev
+      "
+
   backend:
       image: python:3.11-alpine
       depends_on:
         - db
       environment:
         - PORT=4000
-        - DBHOST=db
-        - DBPORT=5432
-        - DBNAME=transporte
-        - DBSCHEMA=public
-        - DBUSER=postgres
-        - DBPASS=password
-        - DB_REDIS_HOST=dbproto
-        - DB_REDIS_PORT=6379
-        - SECRET_JWT="kf6Jc!f30Z!1k1N0#!%#"
       ports:
         - 4000:4000
       volumes:
-        - ./transporte:/app
+        - ./backend:/app
+        - ./uploads:/uploads
         - venv:/root/venv
       working_dir: /app
       command: sh -c "
@@ -152,93 +297,30 @@ services:
 
   dbproto:
     image: redis:7.2-alpine
+    volumes:
+      - dbredis:/data
+
+  dbmongo:
+    image: mongo:jammy
+    volumes:
+      - dbmongo:/data/db
+      - dbmongoconfig:/data/configdb
+    ports:
+      - 27017:27017
+    environment:
+      MONGO_INITDB_ROOT_USERNAME: root
+      MONGO_INITDB_ROOT_PASSWORD: password
 
 
 volumes:
   db:
+  dbredis:
+  dbmongo:
+  dbmongoconfig:
   venv:
   venv_proto:
 ~~~
 
-<br>
-<br>
-<br>
-
-# tunnel para desarrollo
-
-Ver información en:
-
-https://gitlab.com/m3f_usm/admin_transporte/backend/-/wikis/home
-
-
-# archivo desarrollo: docker-compose.yml 
-
-~~~yml
-version: '3'
-name: transporte
-
-services:
-
-  frontend:
-    image: node:18-alpine
-    volumes:
-      - ./frontend:/app
-    ports:
-      - 3000:3000
-    environment:
-      - VITE_PORT=3000
-      - VITE_BACKEND=http://localhost:4000/api
-    working_dir: /app
-    command: sh -c "
-      [ ! -d node_modules ] && npm install ;
-      chmod -R o+w . ;
-      npm run dev
-      "
-
-  backend:
-      image: python:3.11-alpine
-      depends_on:
-        - db
-      environment:
-        - PORT=4000
-        - DBHOST=db
-        - DBPORT=5432
-        - DBNAME=transporte
-        - DBSCHEMA=public
-        - DBUSER=postgres
-        - DBPASS=password
-        - DB_REDIS_HOST=dbproto
-        - DB_REDIS_PORT=6379
-        - SECRET_JWT="kf6Jc!f30Z!1k1N0#!%#"
-      ports:
-        - 4000:4000
-      volumes:
-        - ./transporte:/app
-        - venv:/root/venv
-      working_dir: /app
-      command: sh -c "
-        [ -d /root/venv/bin ] || ( \
-            python -m venv /root/venv/ \
-            && . /root/venv/bin/activate \
-            && pip install -r requirements.txt \
-        ) ;
-
-        . /root/venv/bin/activate ;
-
-        cd /app ;
-        [ -d project ] || django-admin startproject project ;
-        [ -f .env ] || ( [ -f .env.develop ] && cp .env.develop .env ) ;
-        chmod -R o+w project/ ;
-        python project/manage.py runserver 0.0.0.0:$$PORT
-        "
-      extra_hosts:
-        - db:<direccion ip de tu computador>
-
-
-volumes:
-  venv:
-~~~
-
 
 # Script para levantar aplicacion python sin docker