|
#!/usr/bin/env python3
|
|
"""
|
|
Script: load_instalaciones_terceros.py
|
|
|
|
Carga incremental de Instalaciones de Terceros
|
|
|
|
Origen:
|
|
- Giscloud (giscloud.huella_telecom)
|
|
- ServiceNow + CM (mantenimiento correctivo)
|
|
|
|
Modalidad:
|
|
- CREATE TABLE IF NOT EXISTS
|
|
- Inserción incremental (NOT EXISTS)
|
|
- Clave lógica: (tarea_id, origen)
|
|
"""
|
|
|
|
import logging
|
|
from datetime import date
|
|
import psycopg2
|
|
|
|
# ================= CONFIG =================
|
|
|
|
PG_CONN = {
|
|
"host": "localhost",
|
|
"dbname": "postgres",
|
|
"user": "postgres",
|
|
"password": "post3008"
|
|
}
|
|
|
|
SCHEMA = "aux"
|
|
TABLE = "instalaciones_terceros"
|
|
|
|
# ================= LOGGING =================
|
|
|
|
logging.basicConfig(
|
|
level=logging.INFO,
|
|
format="%(asctime)s %(levelname)s %(message)s"
|
|
)
|
|
|
|
# ================= DDL =================
|
|
|
|
DDL_CREATE_TABLE = f"""
|
|
CREATE TABLE IF NOT EXISTS {SCHEMA}.{TABLE} (
|
|
tarea_id TEXT NOT NULL,
|
|
origen TEXT NOT NULL,
|
|
fecha TIMESTAMP,
|
|
latitud DOUBLE PRECISION,
|
|
longitud DOUBLE PRECISION,
|
|
ageom geometry(Point,4326),
|
|
partido TEXT,
|
|
upload_date DATE NOT NULL,
|
|
CONSTRAINT uq_instalaciones_terceros
|
|
UNIQUE (tarea_id, origen)
|
|
);
|
|
"""
|
|
|
|
DDL_GRANTS = f"""
|
|
GRANT SELECT ON {SCHEMA}.{TABLE} TO om_read;
|
|
GRANT SELECT ON {SCHEMA}.{TABLE} TO jibanez;
|
|
"""
|
|
|
|
# ================= SQL GISCLOUD =================
|
|
|
|
SQL_INSERT_GISCLOUD = f"""
|
|
INSERT INTO {SCHEMA}.{TABLE}
|
|
(tarea_id, origen, fecha, latitud, longitud, ageom, partido, upload_date)
|
|
SELECT
|
|
ht.proy_name || ':' || ht.fid AS tarea_id,
|
|
'GISCLOUD' AS origen,
|
|
ht._created AS fecha,
|
|
ST_Y(ht.geom) AS latitud,
|
|
ST_X(ht.geom) AS longitud,
|
|
ht.geom AS ageom,
|
|
ht.partido AS partido,
|
|
%s AS upload_date
|
|
FROM giscloud.huella_telecom ht
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM {SCHEMA}.{TABLE} it
|
|
WHERE it.tarea_id = ht.proy_name || ':' || ht.fid
|
|
AND it.origen = 'GISCLOUD'
|
|
);
|
|
"""
|
|
|
|
# ================= SQL SERVICENOW =================
|
|
|
|
SQL_INSERT_SN = f"""
|
|
INSERT INTO {SCHEMA}.{TABLE}
|
|
(tarea_id, origen, fecha, latitud, longitud, ageom, partido, upload_date)
|
|
SELECT
|
|
i.task_id AS tarea_id,
|
|
'SN_MTTOCORRECTIVO' AS origen,
|
|
m.sys_created_on AS fecha,
|
|
c.latitud AS latitud,
|
|
c.longitud AS longitud,
|
|
ST_SetSRID(
|
|
ST_MakePoint(c.longitud, c.latitud),
|
|
4326
|
|
) AS ageom,
|
|
muni.nam AS partido, -- campo nombre del municipio
|
|
%s AS upload_date
|
|
FROM sn.metric m
|
|
JOIN sn.instance i
|
|
ON i.number = m.instance
|
|
JOIN sn.task t
|
|
ON t.number = i.task_id
|
|
LEFT JOIN cm.ci_sfat_mfat_bfat c
|
|
ON c.nombre_cliente = t.cmdb_ci
|
|
LEFT JOIN cm.municipio muni
|
|
ON ST_Contains(
|
|
muni.geom,
|
|
ST_SetSRID(ST_MakePoint(c.longitud, c.latitud), 4326)
|
|
)
|
|
WHERE m.metric ILIKE '%%CTO DE TERCEROS EN CUADRA%%'
|
|
AND m.metric_definition = 'Si'
|
|
AND c.latitud IS NOT NULL
|
|
AND c.longitud IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM {SCHEMA}.{TABLE} it
|
|
WHERE it.tarea_id = i.task_id
|
|
AND it.origen = 'SN_MTTOCORRECTIVO'
|
|
);
|
|
"""
|
|
|
|
# ================= MAIN =================
|
|
|
|
def log_execution(conn):
|
|
sql = """
|
|
INSERT INTO z_procesos_python.scripts_ejecutados
|
|
(nombre_script, ultima_ejecucion)
|
|
VALUES
|
|
('load_instalaciones_terceros',
|
|
CURRENT_TIMESTAMP AT TIME ZONE 'America/Argentina/Buenos_Aires');
|
|
"""
|
|
with conn.cursor() as cur:
|
|
cur.execute(sql)
|
|
|
|
def main():
|
|
today = date.today()
|
|
conn = psycopg2.connect(**PG_CONN)
|
|
conn.autocommit = False
|
|
|
|
try:
|
|
with conn.cursor() as cur:
|
|
logging.info("Verificando / creando tabla")
|
|
cur.execute(DDL_CREATE_TABLE)
|
|
cur.execute(DDL_GRANTS)
|
|
conn.commit()
|
|
|
|
logging.info("Insertando nuevos registros desde Giscloud")
|
|
cur.execute(SQL_INSERT_GISCLOUD, (today,))
|
|
logging.info(f"Giscloud insertados: {cur.rowcount}")
|
|
conn.commit()
|
|
|
|
logging.info("Insertando nuevos registros desde ServiceNow")
|
|
cur.execute(SQL_INSERT_SN, (today,))
|
|
logging.info(f"ServiceNow insertados: {cur.rowcount}")
|
|
conn.commit()
|
|
|
|
logging.info("Proceso finalizado correctamente")
|
|
logging.info("Registrando ejecución en scripts_ejecutados")
|
|
log_execution(conn)
|
|
conn.commit()
|
|
|
|
except Exception as e:
|
|
conn.rollback()
|
|
logging.error(f"Error durante la carga: {e}")
|
|
raise
|
|
|
|
finally:
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
main()
|
