--------------------------------------------------------------------------------
-- Script d'initialisation du jeu de données.
--
-- @package openresultat
-- @version SVN : $Id$
--------------------------------------------------------------------------------

-- 12 Aout 2019 - data 
-- contient les data de découpage et cartes geo specifique a Arles

-- code commune et code departement
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'commune', '004', 1);
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'departement', '13', 1);
-- lon et lat de la ville arles
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'option_localisation', 'sig_interne', 1);
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'lat', '43.677', 1);
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'lon', '4.631', 1);

-- identifiant des BV
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'id_type_unite_bureau_de_vote', '1', 1);

-- 
INSERT INTO om_widget (om_widget, libelle, lien, texte, type, script, arguments) VALUES
(nextval('om_widget_seq'), 'Élections', '', '', 'file', 'election', ''),
(nextval('om_widget_seq'), 'Animations', '', '', 'file', 'animation', ''),
(nextval('om_widget_seq'), 'Portail Web', '', '', 'file', 'web', '')
;
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES 
(nextval('om_dashboard_seq'), 1, 'C1', 1, (SELECT om_widget FROM om_widget WHERE libelle='Élections')),
(nextval('om_dashboard_seq'), 1, 'C2', 1, (SELECT om_widget FROM om_widget WHERE libelle='Animations')),
(nextval('om_dashboard_seq'), 1, 'C2', 2, (SELECT om_widget FROM om_widget WHERE libelle='Portail Web'))
;

-- decoupage administratif
INSERT INTO circonscription (circonscription, libelle, code, prefecture)
    VALUES (1, '13-53', '13-53', '53');
SELECT pg_catalog.setval('circonscription_seq', 2, false);

INSERT INTO canton (canton, libelle, code, prefecture)
    VALUES (1, 'Exemple', '13-24', '24');
SELECT pg_catalog.setval('canton_seq', 2, false);

INSERT INTO commune (commune, libelle, code, prefecture)
    VALUES (1, 'Exemple', '13110', '110');
SELECT pg_catalog.setval('commune_seq', 2, false);

INSERT INTO departement (departement, libelle, code, prefecture)
    VALUES (1, 'Bouches-du-Rhône', '13', '13');
SELECT pg_catalog.setval('departement_seq', 2, false);

-- decoupage unite
INSERT INTO unite (unite, libelle, type_unite, ordre,
    type_unite_contenu, perimetre, code_unite, canton, circonscription, commune, departement) VALUES
    (1, 'Salle des Mariages', 1, 1, null, false, 001, 1, 1, 1, 1),
    (2, 'Salle des fetes 1', 1, 2, null, false, 002, 1, 1, 1, 1),
    (3, 'Ecole A', 1, 3, null, false, 003, 1, 1, 1, 1),
    (4, 'Salle des fêtes 2', 1, 4, null, false, 004, 1, 1, 1, 1),
    (5, 'Gymnase', 1, 5, null, false, 005, 1, 1, 1, 1),
    (6, 'Ecole B', 1, 6, null, false, 006, 1, 1, 1, 1),
    (7, 'COMMUNE', 7, 7, 1, true, null, 1, 1, 1, 1);
SELECT pg_catalog.setval('unite_seq', 8, false);

-- lien unite
INSERT INTO lien_unite VALUES (1, 1, 7);
INSERT INTO lien_unite VALUES (2, 2, 7);
INSERT INTO lien_unite VALUES (3, 3, 7);
INSERT INTO lien_unite VALUES (4, 4, 7);
INSERT INTO lien_unite VALUES (5, 5, 7);
INSERT INTO lien_unite VALUES (6, 6, 7);
SELECT pg_catalog.setval('lien_unite_seq', 7, false);

-- Candidat
INSERT INTO candidat(candidat, libelle, libelle_liste) VALUES
(1, 'David G.', 'C''est pas si mal'),
(2, 'Jean Michel B.', 'Liste 2');
SELECT pg_catalog.setval('candidat_seq', 3, false);

-- -----
-- carto
-- -----

-- INSERT INTO om_sig_extent (om_sig_extent, nom, extent, valide) VALUES (1, 'paca', '4.23012696738327,42.9822468055588,7.71847763475323,45.1266001706513 ', true);
INSERT INTO om_sig_extent (om_sig_extent, nom, extent, valide) VALUES (1, 'Arles', '4.72345,43.55348,4.73134,43.55932', true);

INSERT INTO om_sig_flux (om_sig_flux, libelle, om_collectivite, id, attribution, chemin, couches, cache_type, cache_gfi_chemin, cache_gfi_couches) VALUES (3, 'cadastre-bati', 1, 'cadastre_bati', NULL, 'http://inspire.cadastre.gouv.fr/scpc/13004.wms?service=WMS&request=GetMap&VERSION=1.3&CRS=EPSG:3857&', 'BU.Building', NULL, NULL, NULL);
INSERT INTO om_sig_flux (om_sig_flux, libelle, om_collectivite, id, attribution, chemin, couches, cache_type, cache_gfi_chemin, cache_gfi_couches) VALUES (2, 'cadastre-autres', 1, 'cadastre_autre', NULL, 'http://inspire.cadastre.gouv.fr/scpc/13004.wms?service=WMS&request=GetMap&VERSION=1.3&CRS=EPSG:3857&', 'LIEUDIT,SUBFISCAL,CLOTURE,DETAIL_TOPO,HYDRO,VOIE_COMMUNICATION,BORNE_REPERE', NULL, NULL, NULL);
INSERT INTO om_sig_flux (om_sig_flux, libelle, om_collectivite, id, attribution, chemin, couches, cache_type, cache_gfi_chemin, cache_gfi_couches) VALUES (1, 'cadastre-parcelle', 1, 'cadastre_parcelle', NULL, '
http://inspire.cadastre.gouv.fr/scpc/13004.wms?service=WMS&request=GetMap&VERSION=1.3&CRS=EPSG:3857&', 'CP.CadastralParcel', NULL, NULL, NULL);
INSERT INTO om_sig_flux (om_sig_flux, libelle, om_collectivite, id, attribution, chemin, couches, cache_type, cache_gfi_chemin, cache_gfi_couches) VALUES (4, 'cadastre-section', 1, 'cadastre_section', NULL, 'http://inspire.cadastre.gouv.fr/scpc/13004.wms?service=WMS&request=GetMap&VERSION=1.3&CRS=EPSG:3857&', 'AMORCES_CAD', NULL, NULL, NULL);
INSERT INTO om_sig_flux (om_sig_flux, libelle, om_collectivite, id, attribution, chemin, couches, cache_type, cache_gfi_chemin, cache_gfi_couches) VALUES (5, 'cadastre toutes les couches', 1, 'cadastre_toutes', NULL, 'http://inspire.cadastre.gouv.fr/scpc/13004.wms?service=WMS&request=GetMap&VERSION=1.3&CRS=EPSG:3857&', 'AMORCES_CAD,LIEUDIT,CP.CadastralParcel,SUBFISCAL,CLOTURE,DETAIL_TOPO,HYDRO,VOIE_COMMUNICATION,BU.Building,BORNE_REPERE

', NULL, NULL, NULL);
-- map
INSERT INTO om_sig_map (om_sig_map, om_collectivite, id, libelle, actif, zoom, fond_osm, fond_bing, fond_sat, projection_externe, url, om_sql, util_idx, util_reqmo, util_recherche, source_flux, fond_default, om_sig_extent, restrict_extent, sld_marqueur, sld_data, point_centrage, librairie_cartographie) VALUES (3, 1, 'carte_flux_generique', 'carte pour flux generique', true, '0', true, false, false, 'EPSG:2154', '*', '*', false, false, false, NULL, 'osm', 1, true, NULL, NULL, NULL, 'openlayers2');
INSERT INTO om_sig_map (om_sig_map, om_collectivite, id, libelle, actif, zoom, fond_osm, fond_bing, fond_sat, projection_externe, url, om_sql, util_idx, util_reqmo, util_recherche, source_flux, fond_default, om_sig_extent, restrict_extent, sld_marqueur, sld_data, point_centrage, librairie_cartographie) VALUES (1, 1, 'unite', 'unite', true, '12', true, false, false, 'EPSG:2154', '../app/unite.php?idx=', 'select st_astext(geom) as geom, libelle as titre, concat(adresse1,'' '',cp,'' '',ville) as description, unite as idx from &DB_PREFIXEunite', true, false, true, 3, 'osm', 1, false, NULL, NULL, NULL, 'openlayers2');
INSERT INTO om_sig_map (om_sig_map, om_collectivite, id, libelle, actif, zoom, fond_osm, fond_bing, fond_sat, projection_externe, url, om_sql, util_idx, util_reqmo, util_recherche, source_flux, fond_default, om_sig_extent, restrict_extent, sld_marqueur, sld_data, point_centrage, librairie_cartographie) VALUES (2, 1, 'lien', 'lien', true, '12', true, false, false, 'EPSG:2154', '#', 'select st_astext(geom) as geom, libelle as titre, libelle as description, unite as idx from &DB_PREFIXElien', true, false, false, 3, 'osm', 1, false, NULL, NULL, NULL, 'openlayers2');
-- flux
INSERT INTO om_sig_map_flux (om_sig_map_flux, om_sig_flux, om_sig_map, ol_map, ordre, visibility, panier, pa_nom, pa_layer, pa_attribut, pa_encaps, pa_sql, pa_type_geometrie, sql_filter, baselayer, singletile, maxzoomlevel) VALUES (1, 4, 3, 'cadastre-section', 15, false, false, NULL, NULL, NULL, NULL, '', NULL, '', false, false, NULL);
INSERT INTO om_sig_map_flux (om_sig_map_flux, om_sig_flux, om_sig_map, ol_map, ordre, visibility, panier, pa_nom, pa_layer, pa_attribut, pa_encaps, pa_sql, pa_type_geometrie, sql_filter, baselayer, singletile, maxzoomlevel) VALUES (2, 1, 3, 'cadastre parcelle', 20, false, false, NULL, NULL, NULL, NULL, '', NULL, '', false, false, NULL);
INSERT INTO om_sig_map_flux (om_sig_map_flux, om_sig_flux, om_sig_map, ol_map, ordre, visibility, panier, pa_nom, pa_layer, pa_attribut, pa_encaps, pa_sql, pa_type_geometrie, sql_filter, baselayer, singletile, maxzoomlevel) VALUES (3, 3, 3, 'cadastre bati', 25, false, false, NULL, NULL, NULL, NULL, '', NULL, '', false, false, NULL);
INSERT INTO om_sig_map_flux (om_sig_map_flux, om_sig_flux, om_sig_map, ol_map, ordre, visibility, panier, pa_nom, pa_layer, pa_attribut, pa_encaps, pa_sql, pa_type_geometrie, sql_filter, baselayer, singletile, maxzoomlevel) VALUES (4, 2, 3, 'cadastre autres', 30, false, false, NULL, NULL, NULL, NULL, '', NULL, '', false, false, NULL);
INSERT INTO om_sig_map_flux (om_sig_map_flux, om_sig_flux, om_sig_map, ol_map, ordre, visibility, panier, pa_nom, pa_layer, pa_attribut, pa_encaps, pa_sql, pa_type_geometrie, sql_filter, baselayer, singletile, maxzoomlevel) VALUES (5, 5, 3, 'cadastre - toutes', 50, false, false, NULL, NULL, NULL, NULL, '', NULL, '', false, false, NULL);
-- comp
INSERT INTO om_sig_map_comp (om_sig_map_comp, om_sig_map, libelle, ordre, actif, comp_maj, type_geometrie, comp_table_update, comp_champ, comp_champ_idx, obj_class) VALUES (1, 1, 'geom', 10, true, true, 'point', 'unite', 'geom', 'unite', 'unite');
INSERT INTO om_sig_map_comp (om_sig_map_comp, om_sig_map, libelle, ordre, actif, comp_maj, type_geometrie, comp_table_update, comp_champ, comp_champ_idx, obj_class) VALUES (2, 2, 'lien', 10, true, true, 'polygon', 'lien', 'geom', 'lien', 'lien');

-- dashboard + widget
INSERT INTO om_widget (om_widget, libelle, lien, texte, type, script, arguments) VALUES (nextval('om_widget_seq'), 'Carte des unités', ' ../app/index.php?module=map&mode=tab_sig&obj=unite', '<iframe style=''width:100%;border: 0 none; height: 350px;'' src=''../app/index.php?module=map&mode=tab_sig&obj=unite&popup=1''></iframe>', 'web', '', '');
INSERT INTO om_dashboard (om_dashboard, om_profil, bloc, "position", om_widget) VALUES (nextval('om_dashboard_seq'), 1, 'C3', 1, (SELECT om_widget FROM om_widget WHERE libelle='Carte des unités'));
-- sequence carto
SELECT pg_catalog.setval('om_sig_extent_seq', 1, true);
SELECT pg_catalog.setval('om_sig_flux_seq', 5, true);
SELECT pg_catalog.setval('om_sig_map_flux_seq', 5, true);
SELECT pg_catalog.setval('om_sig_map_seq', 3, true);
SELECT pg_catalog.setval('om_sig_map_comp_seq', 1, true);
SELECT pg_catalog.setval('om_dashboard_seq', 1, true);
SELECT pg_catalog.setval('om_widget_seq', 1, true);

-- données pour les tests des périmètres

INSERT INTO unite (unite, libelle, type_unite, ordre, perimetre, type_unite_contenu, code_unite) VALUES
(8, 'BV1', 1, 1, false, null, 1),
(9, 'BV2', 1, 2, false, null, 2),
(10, 'BV3', 1, 3, false, null, 3),
(11, 'GP BV 1', 2, 4, true, 1, null),
(12, 'GP BV 2', 2, 5, true, 1, null),
(13, 'mairie', 3, 6, true, 2, null),
(14, 'region', 13, 7, true, 3, null);
SELECT pg_catalog.setval('unite_seq', 15, false);


INSERT INTO lien_unite VALUES
(7, 8, 11),
(8, 9, 11),
(9, 10, 12),
(10, 11, 13),
(11, 12, 13),
(12, 13, 14);
SELECT pg_catalog.setval('lien_unite_seq', 13, false);

