#!/bin/sh
echo "************************************"
echo "*    SIGUA(PG) A SIGUA(SQLITE)     *"
echo "*    autor: Jose M. Mira Martínez  *"
echo "************************************"
echo "NOTA: Esta base de datos no contiene geometrias"
echo "Definiendo variables"
vSQLite="siguaLite.db"
vHost="<tu hostname o ip>"
vDB="<tu nombre bbdd>"
vUser="<tu usuario bbdd>"
vPass="<tu password bbdd>"
echo "Borrando fichero Sqlite"
rm $vSQLite
echo "creando Fichero de SpatiaLite (SqLite) llamado "$vSQLite
sqlite3 siguaLite.db "CREATE TABLE actividades ( codactividad INTEGER PRIMARY KEY AUTOINCREMENT, txt_actividad VARCHAR(40) NOT NULL, activresum VARCHAR(15) NOT NULL, txt_actividad_val VARCHAR(40), util INTEGER_BOOLEAN, crue VARCHAR(50), u21 VARCHAR(10), personal INTEGER_BOOLEAN, superficie_computable INTEGER_BOOLEAN, inventariable INTEGER_BOOLEAN, carto VARCHAR(15), prevencion VARCHAR(50), net INTEGER);"
echo "rellenando tabla ACTIVIDADES"
ogr2ogr -progress -f SQLite -append siguaLite.db PG:"dbname='$vDB' host='$vHost' port='5432' user='$vUser' password='$vPass'" public.actividades
echo "creando tabla DEPARTAMENTOS"
ogr2ogr -progress -f SQLite -update $vSQLite -nln 'departamentos' PG:"dbname='$vDB' host='$vHost' port='5432' user='$vUser' password='$vPass'" public.departamentossigua
echo "creando tabla ZONAS"
ogr2ogr -progress -f SQLite -update $vSQLite PG:"dbname='$vDB' host='$vHost' port='5432' user='$vUser' password='$vPass'" public.zonas
echo "creando tabla EDIFICIOS"
ogr2ogr -progress -f SQLite -update $vSQLite -nln 'edificios' PG:"dbname='$vDB' host='$vHost' port='5432' user='$vUser' password='$vPass'" -sql "SELECT es.codigoedif AS codigo,e.txt_edificio AS txt_edif, st_x(st_centroid(st_transform(es.geometria,4326))) as lon,st_y(st_centroid(st_transform(es.geometria,4326))) as lat, pb AS tiene_pb, p1 AS tiene_p1, p2 AS tiene_p2, p3 AS tiene_p3, p4 AS tiene_p4, ps AS tiene_ps FROM (SELECT st_simplify(st_union(geometria),0.5) as geometria, substring(codigo from 1 for 4) AS codigoedif FROM sigpb WHERE codigo NOT LIKE '0000%' GROUP BY codigoedif) AS es, edificios e WHERE e.cod_zona || e.cod_edificio = es.codigoedif"
echo "creando tabla PERSONAL"
ogr2ogr -progress -f SQLite -update $vSQLite -nln 'personal' PG:"dbname='$vDB' host='$vHost' port='5432' user='$vUser' password='$vPass'" -sql "SELECT siguadmin.siguadmin_encriptar_nif(tp.nif) AS nif, p.nombre ||' '|| p.apellido1 || ' '|| p.apellido2 AS nombre, tp.codigo,tp.cod_puesto,tp.cod_depto, d.txt_dpto_sigua, pu.txt_puesto, st_x(st_centroid(st_transform(e.geometria,4326)) ) AS lon, st_y(st_centroid(st_transform(e.geometria,4326)) ) AS lat FROM todaspersonas tp, personal p, puestos pu, departamentossigua d, todasestancias e WHERE tp.nif = p.nif AND pu.cod_puesto = tp.cod_puesto AND tp.cod_depto = d.cod_dpto_sigua AND tp.codigo = e.codigo AND tp.codigo != '0000PB997' ORDER BY nombre"
echo "creando tabla ESTANCIAS"
ogr2ogr -progress -f SQLite -update $vSQLite -nln 'estancias' PG:"dbname='$vDB' host='$vHost' port='5432' user='$vUser' password='$vPass'" -sql "select e.codigo, e.coddpto as dpto, a.codactividad, a.txt_actividad AS actividad, d.txt_dpto_sigua AS organizacion, denominaci AS denominacion, round(st_area(e.geometria)::numeric,2) AS superficie, st_x(st_centroid(st_transform(geometria,4326)) ) AS lon, st_y(st_centroid(st_transform(geometria,4326)) ) AS lat FROM todasestancias e, actividades a, departamentossigua d WHERE e.actividad = a.codactividad AND e.coddpto = d.cod_dpto_sigua and e.actividad NOT IN (93,96)"
echo "Exportación a SpatiaLite satisfactoria"