--
-- PostgreSQL database dump
--

-- SET statement_timeout = 0;
-- SET client_encoding = 'UTF8';
-- SET standard_conforming_strings = on;
-- SET check_function_bodies = false;
-- SET client_min_messages = warning;

--
-- Name: openexemple; Type: SCHEMA; Schema: -; Owner: -
--

-- CREATE SCHEMA openexemple;


--
-- PostgreSQL database dump complete
--

-- ajout des champs utilisateur
alter table om_utilisateur add adresse1 varchar(100);
alter table om_utilisateur add adresse2 varchar(100);
alter table om_utilisateur add cp varchar(10);
alter table om_utilisateur add ville varchar(100);
alter table om_utilisateur add coordinates varchar(50); 
alter table om_utilisateur add geom geometry(Point,2154);

-- ajout trigger -> transforme en geom postgis les coordonnees récupérés dans le champ coordinates
CREATE OR REPLACE FUNCTION pr_maj_om_utilisateur()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
   IF (TG_OP='INSERT' AND NEW.coordinates IS NOT NULL) THEN
		NEW.geom=ST_Transform(ST_SetSRID(cast(ST_MakePoint(cast(substr(NEW.coordinates,1,strpos( NEW.coordinates, ',')-1) as float), 
			cast(substr(NEW.coordinates,strpos( NEW.coordinates, ',')+1) as float)) as geometry),4326),2154);
   ELSEIF(TG_OP='UPDATE') THEN
        IF (NEW.coordinates is null) THEN
			NEW.geom=NULL;
		ELSEIF (OLD.coordinates <> NEW.coordinates OR OLD.coordinates IS NULL) THEN
			NEW.geom=ST_Transform(ST_SetSRID(cast(ST_MakePoint(cast(substr(NEW.coordinates,1,strpos( NEW.coordinates, ',')-1) as float), 
			cast(substr(NEW.coordinates,strpos( NEW.coordinates, ',')+1) as float)) as geometry),4326),2154);
		END IF;
   END IF;
   RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path FROM CURRENT;
  
CREATE TRIGGER tr_maj_om_utilisateur
  BEFORE INSERT OR UPDATE
  ON om_utilisateur
  FOR EACH ROW
  EXECUTE PROCEDURE pr_maj_om_utilisateur();

-- om_sig
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES (nextval('om_parametre_seq'), 'option_localisation', 'sig_interne', 1);
SELECT pg_catalog.setval('om_sig_extent_seq', 1, true);
SELECT pg_catalog.setval('om_sig_map_comp_seq', 1, true);
SELECT pg_catalog.setval('om_sig_map_seq', 1, true);

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_map (om_sig_map, om_collectivite, id, libelle, actif, zoom, fond_osm, fond_bing, fond_sat, layer_info, projection_externe, url, om_sql, retour, util_idx, util_reqmo, util_recherche, source_flux, fond_default, om_sig_extent, restrict_extent, sld_marqueur, sld_data, point_centrage) 
VALUES (1, 1, 'om_utilisateur', 'om_utilisateur', true, '6', true, false, false, true, 'EPSG:2154', '*', '*', '*', true, false, false, NULL, 'osm', 1, false, NULL, NULL, NULL);

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, false, 'point', 'om_utilisateur', 'geom', 'om_utilisateur', 'om_utilisateur');


