migracion_elecciones_nacionales_2007.sql

BEGIN;

/* script SQL para importar datos publicos elecciones nacionales 2011 */


/* Crear tabla "temporal" de trabajo */

DROP TABLE IF EXISTS tmp_datos_publicos;
CREATE TABLE tmp_datos_publicos (
    id SERIAL,
    anio INTEGER,
    eleccion TEXT,
    provinciaId INTEGER,
    provincia TEXT,
    departamentoId INTEGER,
    departamento TEXT,
    partidoId TEXT,
    partido TEXT,
    votos TEXT
);

/* Importar datos publicos */

COPY tmp_datos_publicos (anio, eleccion, provinciaId, provincia, departamentoId, departamento, partidoId, partido, votos)
    FROM '/home/reingart/web2py/applications/recuento_web2py/private/electoral-2003-2011-completo.csv' 
    WITH ( FORMAT CSV, HEADER );

/* limpiar datos (dejar solo 2007) */

DELETE FROM tmp_datos_publicos WHERE anio != '2007' OR eleccion ILIKE '%PASO%';

/* corregir caracteres extraños en campo votos y convertir a numero entero */

UPDATE tmp_datos_publicos SET votos = REPLACE(votos, '.', '') WHERE votos LIKE '%\.%';
UPDATE tmp_datos_publicos SET votos = REPLACE(votos, 'w', '') WHERE votos LIKE '%w%';
UPDATE tmp_datos_publicos SET votos = TRIM(both ' ' FROM votos);
ALTER TABLE tmp_datos_publicos ALTER COLUMN votos TYPE INTEGER USING votos::int;

/* corregir partidos sin nro de lista (0) -buscar alguno con el mismo nombre- */

UPDATE tmp_datos_publicos SET partidoid = PP.partidoid 
FROM tmp_datos_publicos PP 
WHERE PP.partidoid != '0' 
  AND PP.partido=tmp_datos_publicos.partido 
  AND tmp_datos_publicos.partidoid='0';

/* borrar votos totales y votos positivos (datos calculables) */

DELETE FROM tmp_datos_publicos WHERE partido IN ('VOTOS POSITIVOS', 'VOTOS TOTALES');

/* unificar cargo Presidente -> PV (presidente/vice) */
UPDATE tmp_datos_publicos SET eleccion = 'PV' WHERE eleccion='Presidente';

/* convertir campo partidoid a numero entero */
ALTER TABLE tmp_datos_publicos ALTER COLUMN partidoid TYPE INTEGER USING partidoid::int;

/* ajustar partidoid (nro de lista) para que sea unívoco (cuando la lista no 
   tiene candidato a presidente, se puede repetir en distintas provincias) */

ALTER TABLE tmp_datos_publicos ADD COLUMN nro_lista TEXT; 
UPDATE tmp_datos_publicos SET nro_lista = partidoid || '@' || provinciaid 
WHERE partidoid BETWEEN 151 AND 900;
UPDATE tmp_datos_publicos SET nro_lista = partidoid 
WHERE partidoid<= 151 or partidoid> 900;


/* NORMALIZACIÓN */

/* LISTAS */

DELETE FROM listas;
ALTER SEQUENCE listas_id_lista_seq RESTART 1; /* reinicio la secuencia autonumerica */
INSERT INTO listas (nro_lista) SELECT DISTINCT nro_lista 
                               FROM  tmp_datos_publicos ORDER BY nro_lista;
UPDATE listas SET descripcion = (SELECT MIN(T.partido) FROM  tmp_datos_publicos T 
                                 WHERE T.nro_lista = listas.nro_lista);
UPDATE listas SET idx_fila = id_lista, descripcion_corta=SUBSTRING(descripcion FROM 1 FOR 25);
/* votos nulos / blancos */
UPDATE listas SET positivo = 'T' WHERE nro_lista NOT IN ('992', '991'); 
UPDATE listas SET positivo = 'F' WHERE nro_lista IN ('992', '991'); 


/* CARGOS */

DELETE FROM cargos;
ALTER SEQUENCE cargos_id_cargo_seq RESTART 1; /* reinicio la secuencia autonumerica */
INSERT INTO cargos (descripcion, descripcion_corta, idx_col) VALUES 
    ('Presidente/Vice', 'PV', 1),
    ('Senador Nacional', 'SN', 2),
    ('Diputado Nacional', 'DN', 3);

/* UBICACIONES -arbol jerarquico PAIS/PROVINCIA/DEPARTAMENTO-  */

DELETE FROM ubicaciones;
ALTER SEQUENCE ubicaciones_id_ubicacion_seq RESTART 1; /* reinicio la secuencia autonumerica */
INSERT INTO ubicaciones (descripcion, clase) VALUES ('ARGENTINA', 'Pais');
 
INSERT INTO ubicaciones (descripcion, clase, id_ubicacion_padre)  
SELECT P.provincia, 'Provincia', 1 FROM
    (SELECT DISTINCT provinciaid, provincia
     FROM tmp_datos_publicos ORDER BY provinciaid) P;

INSERT INTO ubicaciones (descripcion, clase, id_ubicacion_padre)  
SELECT D.departamento, 'Departamento', D.id_ubicacion FROM
(SELECT DISTINCT provinciaid, provincia, departamentoid, departamento, U.id_ubicacion
           FROM tmp_datos_publicos 
                INNER JOIN ubicaciones U ON U.descripcion = provincia 
                                        AND U.clase='Provincia'
           ORDER BY provinciaid, departamentoid) D;

/* PLANILLAS (datos generales de cada telegrama, debería ser por mesa...) */

DELETE FROM planillas;
ALTER SEQUENCE planillas_id_planilla_seq RESTART 1; /* reinicio la secuencia autonumerica */
INSERT INTO planillas (id_ubicacion, id_estado, definitivo, ciudadanos_sufragaron)
SELECT U.id_ubicacion, 'Publicada', 'T', SUM(votos)
  FROM tmp_datos_publicos T
  INNER JOIN ubicaciones U ON U.descripcion = T.departamento 
         AND U.id_ubicacion_padre = (SELECT UP.id_ubicacion FROM ubicaciones UP 
                                      WHERE UP.descripcion=T.provincia 
                                        AND UP.clase='Provincia')
GROUP BY U.id_ubicacion
ORDER BY U.id_ubicacion;

/* DETALLE PLANILLA (votos) */

DELETE FROM planillas_det;
ALTER SEQUENCE planillas_det_id_seq RESTART 1; /* reinicio la secuencia autonumerica */
INSERT INTO planillas_det (id_planilla, id_cargo, id_lista, votos_definitivos)
SELECT P.id_planilla, C.id_cargo, L.id_lista, T.votos
  FROM tmp_datos_publicos T
  INNER JOIN listas L ON T.nro_lista = L.nro_lista
  INNER JOIN cargos C ON T.eleccion = C.descripcion_corta
  INNER JOIN ubicaciones U ON U.descripcion = T.departamento 
         AND U.id_ubicacion_padre = (SELECT UP.id_ubicacion FROM ubicaciones UP 
                                      WHERE UP.descripcion=T.provincia 
                                        AND UP.clase='Provincia') 
  INNER JOIN planillas P on P.id_ubicacion = U.id_ubicacion
ORDER BY U.id_ubicacion;


/* Listas y cargos habilitados por ubicación (por ahora, copia de planillas) */ 

DELETE FROM carg_list_ubic;
ALTER SEQUENCE carg_list_ubic_id_seq RESTART 1; /* reinicio la secuencia autonumerica */
INSERT INTO carg_list_ubic (id_ubicacion, id_cargo, id_lista)
SELECT P.id_ubicacion, PD.id_cargo, PD.id_lista  
FROM planillas P 
INNER JOIN planillas_det PD ON P.id_planilla = PD.id_planilla;


COMMIT;