-- Script de mise à jour vers la version v3.9.0-a1
--
-- XXX Ce fichier doit être renommé en v3.9.0-a1.sql au moment de la release
--
-- @package openads
-- @version SVN : $Id$
--------------------------------------------------------------------------------

---
--- Ajout de la table sig_elyx
---
CREATE TABLE sig_elyx (
    sig_elyx integer NOT NULL,
    dossier character varying(30) NOT NULL,
    date_verif_parcelle timestamp,
    etat_verif_parcelle boolean,
    message_verif_parcelle text,
    date_calcul_emprise timestamp,
    etat_calcul_emprise boolean,
    message_calcul_emprise text,
    date_dessin_emprise timestamp,
    etat_dessin_emprise boolean,
    message_dessin_emprise text,
    date_calcul_centroide timestamp,
    etat_calcul_centroide boolean,
    message_calcul_centroide text,
    date_recup_contrainte timestamp,
    etat_recup_contrainte boolean,
    message_recup_contrainte text,
    terrain_references_cadastrales_archive character varying(100)
);

--- Création de la séquence
CREATE SEQUENCE sig_elyx_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--- Ajout de la clé primaire
ALTER TABLE ONLY sig_elyx
    ADD CONSTRAINT sig_elyx_pkey PRIMARY KEY (sig_elyx);

--- Ajout de la clé étrangère
ALTER TABLE ONLY sig_elyx
    ADD CONSTRAINT sig_elyx_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);

-- Ajout des codes impôts des arrondissements
ALTER TABLE ONLY arrondissement ADD COLUMN code_impots character varying(3);
UPDATE arrondissement SET code_impots = 201 WHERE code_postal = '13001';
UPDATE arrondissement SET code_impots = 202 WHERE code_postal = '13002';
UPDATE arrondissement SET code_impots = 203 WHERE code_postal = '13003';
UPDATE arrondissement SET code_impots = 204 WHERE code_postal = '13004';
UPDATE arrondissement SET code_impots = 205 WHERE code_postal = '13005';
UPDATE arrondissement SET code_impots = 206 WHERE code_postal = '13006';
UPDATE arrondissement SET code_impots = 207 WHERE code_postal = '13007';
UPDATE arrondissement SET code_impots = 208 WHERE code_postal = '13008';
UPDATE arrondissement SET code_impots = 209 WHERE code_postal = '13009';
UPDATE arrondissement SET code_impots = 210 WHERE code_postal = '13010';
UPDATE arrondissement SET code_impots = 211 WHERE code_postal = '13011';
UPDATE arrondissement SET code_impots = 212 WHERE code_postal = '13012';
UPDATE arrondissement SET code_impots = 213 WHERE code_postal = '13013';
UPDATE arrondissement SET code_impots = 214 WHERE code_postal = '13014';
UPDATE arrondissement SET code_impots = 215 WHERE code_postal = '13015';
UPDATE arrondissement SET code_impots = 216 WHERE code_postal = '13016';

ALTER TABLE arrondissement ALTER COLUMN code_impots SET NOT NULL;

---
--- Ajout des droits pour la géolocalisation
---
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'geolocalisation_consulter',(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'geolocalisation_consulter' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'geolocalisation_consulter',(SELECT om_profil FROM om_profil WHERE libelle = 'QUALIFICATEUR')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'geolocalisation_consulter' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'QUALIFICATEUR')
    );

---
--- Ajout du paramétrage pour le sig
---
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite)
SELECT nextval('om_parametre_seq'),'sig_web_server', 'sig.vdm.mars/elyx/layouts/vdm.jsp', 1
WHERE
    NOT EXISTS (
        SELECT om_parametre FROM om_parametre WHERE libelle = 'sig_web_server'
    );
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite)
SELECT nextval('om_parametre_seq'),'sig_couche_emprise_dossier', 'ADS_EMPRISE', 1
WHERE
    NOT EXISTS (
        SELECT om_parametre FROM om_parametre WHERE libelle = 'sig_couche_emprise_dossier'
    );
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite)
SELECT nextval('om_parametre_seq'),'sig_couche_affichage_dossier', 'ADS_DOSSIER', 1
WHERE
    NOT EXISTS (
        SELECT om_parametre FROM om_parametre WHERE libelle = 'sig_couche_affichage_dossier'
    );
INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite)
SELECT nextval('om_parametre_seq'),'sig_couche_affichage_parcelle', 'VM_PCI_PARC_MPM', 1
WHERE
    NOT EXISTS (
        SELECT om_parametre FROM om_parametre WHERE libelle = 'sig_couche_affichage_parcelle'
    );

INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite)
SELECT nextval('om_parametre_seq'),'sig_referentiel', '2154', 1
WHERE
    NOT EXISTS (
        SELECT om_parametre FROM om_parametre WHERE libelle = 'sig_referentiel'
    );




---
--- Nouvelle gestion des tableaux de bord <-- openmairie_exemple 4.4.0 START
---

-- Suppression de la table om_tdb désormais inutile
DROP TABLE om_tdb CASCADE;

-- Création de la table om_dashboard
--CREATE TABLE om_dashboard (
--om_dashboard integer NOT NULL,
--om_profil integer NOT NULL,
--bloc character varying(10) NOT NULL,
--position integer,
--om_widget integer NOT NULL
--);
--ALTER TABLE ONLY om_dashboard
--    ADD CONSTRAINT om_dashboard_pkey PRIMARY KEY (om_dashboard);
--ALTER TABLE ONLY om_dashboard
--    ADD CONSTRAINT om_dashboard_om_profil_fkey FOREIGN KEY (om_profil) REFERENCES om_profil(om_profil);
--ALTER TABLE ONLY om_dashboard
--    ADD CONSTRAINT om_dashboard_om_widget_fkey FOREIGN KEY (om_widget) REFERENCES om_widget(om_widget);
--CREATE SEQUENCE om_dashboard_seq
--    START WITH 1
--    INCREMENT BY 1
--    NO MINVALUE
--    NO MAXVALUE
--    CACHE 1;
--SELECT pg_catalog.setval('om_dashboard_seq', 1, false);
--ALTER SEQUENCE om_dashboard_seq OWNED BY om_dashboard.om_dashboard;
--INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_dashboard',
--(select om_profil from om_profil where libelle='ADMINISTRATEUR'));

-- Modification de la table om_widget
ALTER TABLE ONLY om_widget
    ADD CONSTRAINT om_widget_libelle_om_collectivite_key UNIQUE (libelle, om_collectivite);
--ALTER TABLE om_widget
--    DROP CONSTRAINT om_widget_om_profil_fkey;
ALTER TABLE om_widget
    DROP CONSTRAINT om_widget_om_collectivite_fkey;
--ALTER TABLE om_widget DROP COLUMN om_profil;
ALTER TABLE om_widget DROP COLUMN om_collectivite;
--ALTER TABLE om_widget ALTER COLUMN "libelle" TYPE character varying(100);
--ALTER TABLE om_widget ADD COLUMN "type" character varying(40) NOT NULL;
--ALTER TABLE om_widget ALTER COLUMN "lien" SET DEFAULT ''::character varying;
--ALTER TABLE om_widget ALTER COLUMN "texte" SET DEFAULT ''::text;

-- Mise à jour des om_widget pour mettre le web par défaut
UPDATE om_widget SET type='web' where type='' or type=null;

-- Le tableau de bord était déjà implémenté de cette manière dans openads donc
-- la mise à jour est différente de ce qui est fourni pas openmairie_exemple
ALTER TABLE om_widget ALTER COLUMN "type" DROP DEFAULT;

---
--- Nouvelle gestion des tableaux de bord <-- openmairie_exemple 4.4.0 END
---

---
--- Ajout des droits pour la géolocalisation
---
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_instruction_localiser-sig-externe',(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_instruction_localiser-sig-externe' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_instruction_localiser-sig-externe',(SELECT om_profil FROM om_profil WHERE libelle = 'QUALIFICATEUR')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_instruction_localiser-sig-externe' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'QUALIFICATEUR')
    );



--
-- Mise à jour de la requête de 'om_requete' concernant le rapport d'instruction
-- pour ajouter le schéma devant le nom d'une table
--

UPDATE om_requete SET requete = 'SELECT

    rapport_instruction.dossier_instruction as dossier_instruction_rapport_instruction, 
    analyse_reglementaire as analyse_reglementaire_rapport_instruction,
    description_projet as description_projet_rapport_instruction,
    proposition_decision as proposition_decision_rapport_instruction, 
    dossier.dossier_libelle as libelle_dossier, 
    etat as etat_dossier,
    pos as pos_dossier, 
    servitude as servitude_dossier, 
    terrain_adresse_voie_numero as terrain_adresse_voie_numero_dossier,
    dossier.terrain_adresse_voie as terrain_adresse_voie_dossier,
    terrain_adresse_lieu_dit as terrain_adresse_lieu_dit_dossier,
    terrain_adresse_localite as terrain_adresse_localite_dossier,
    terrain_adresse_code_postal as terrain_adresse_code_postal_dossier,
    terrain_adresse_bp as terrain_adresse_bp_dossier,
    terrain_adresse_cedex as terrain_adresse_cedex_dossier,
    terrain_superficie as terrain_superficie_dossier,
    dossier.delai as delai_dossier,
    replace(dossier.terrain_references_cadastrales, '';'', '' '') as terrain_references_cadastrales_dossier,
    particulier_nom as particulier_nom_demandeur,
    particulier_prenom as particulier_prenom_demandeur,
    personne_morale_denomination as personne_morale_denomination_demandeur,
    personne_morale_raison_sociale as personne_morale_raison_sociale_demandeur,
    personne_morale_siret as personne_morale_siret_demandeur,
    personne_morale_nom as personne_morale_nom_demandeur,
    personne_morale_prenom as personne_morale_prenom_demandeur,
    numero as numero_demandeur,
    voie as voie_demandeur,
    demandeur.complement as complement_demandeur,
    lieu_dit as lieu_dit_demandeur,
    localite as localite_demandeur,
    code_postal as code_postal_demandeur,
    bp as bp_demandeur,
    cedex as cedex_demandeur,
    instructeur.nom as nom_instructeur, 
    dossier_autorisation_type_detaille.libelle as libelle_datd, 
    civilite.code as code_civilite,
    avis_decision.libelle as libelle_avis_decision,
    division.chef as chef_division,
    direction.chef as chef_direction,
    donnees_techniques.co_projet_desc as co_projet_desc_donnees_techniques,
    donnees_techniques.am_projet_desc as am_projet_desc_donnees_techniques,
    donnees_techniques.dm_projet_desc as dm_projet_desc_donnees_techniques,
    donnees_techniques.am_lot_max_nb as am_lot_max_nb_donnees_techniques,
    donnees_techniques.am_lot_max_shon as am_lot_max_shon_donnees_techniques,
    donnees_techniques.su_cstr_shon_tot as su_cstr_shon_tot_donnees_techniques,
    donnees_techniques.su_demo_shon_tot as su_demo_shon_tot_donnees_techniques,
    CONCAT(''Habitation : '', donnees_techniques.su_trsf_shon1, 
    '' - Hébergement hôtelier : '', donnees_techniques.su_trsf_shon2, 
    '' - Bureaux : '', donnees_techniques.su_trsf_shon3, 
    '' - Commerce : '', donnees_techniques.su_trsf_shon4,
    '' - Artisanat : '', donnees_techniques.su_trsf_shon5,
    '' - Industrie : '', donnees_techniques.su_trsf_shon6,
    '' - Exploitation agricole ou forestière : '', donnees_techniques.su_trsf_shon7,
    '' - Entrepôt : '', donnees_techniques.su_trsf_shon8,
    '' - Service public ou d''''intérêt collectif : '', donnees_techniques.su_trsf_shon9) as tab_surface_donnees_techniques,
    donnees_techniques.co_tot_log_nb as co_tot_log_nb_donnees_techniques,
    donnees_techniques.co_statio_place_nb as co_statio_place_nb_donnees_techniques


FROM 

    &DB_PREFIXErapport_instruction 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=rapport_instruction.dossier_instruction 
    LEFT JOIN &DB_PREFIXEavis_decision 
        ON dossier.avis_decision = avis_decision.avis_decision
    LEFT JOIN &DB_PREFIXEdonnees_techniques
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdivision
        ON dossier.division = division.division
    LEFT JOIN &DB_PREFIXEdirection
        ON division.direction = direction.direction
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur 
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=civilite.civilite 
    LEFT JOIN &DB_PREFIXEdossier_instruction_type 
        ON dossier.dossier_instruction_type=dossier_instruction_type.dossier_instruction_type 
    LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille 
    
WHERE 

    rapport_instruction = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE'
WHERE code = 'rapport_instruction';

--
-- Correction d'un droit manquant pour le profil DIVISIONNAIRE
--

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossiers_tous_limites',(SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossiers_tous_limites' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
    );

--
-- Ajout des colonnes de cerfa et données techniques non utilisées
--
ALTER TABLE cerfa ADD COLUMN co_statio_avt_shob boolean default false;
ALTER TABLE cerfa ADD COLUMN co_statio_apr_shob boolean default false;
ALTER TABLE cerfa ADD COLUMN co_statio_avt_surf boolean default false;
ALTER TABLE cerfa ADD COLUMN co_statio_apr_surf boolean default false;
ALTER TABLE cerfa ADD COLUMN co_trx_amgt boolean default false;
ALTER TABLE cerfa ADD COLUMN co_modif_aspect boolean default false;
ALTER TABLE cerfa ADD COLUMN co_modif_struct boolean default false;
ALTER TABLE cerfa ADD COLUMN co_ouvr_elec boolean default false;
ALTER TABLE cerfa ADD COLUMN co_ouvr_infra boolean default false;
ALTER TABLE cerfa ADD COLUMN co_trx_imm boolean default false;
ALTER TABLE cerfa ADD COLUMN co_cstr_shob boolean default false;
ALTER TABLE cerfa ADD COLUMN am_voyage_deb boolean default false;
ALTER TABLE cerfa ADD COLUMN am_voyage_fin boolean default false;
ALTER TABLE cerfa ADD COLUMN am_modif_amgt boolean default false;
ALTER TABLE cerfa ADD COLUMN am_lot_max_shob boolean default false;
ALTER TABLE cerfa ADD COLUMN mod_desc boolean default false;
ALTER TABLE cerfa ADD COLUMN tr_total boolean default false;
ALTER TABLE cerfa ADD COLUMN tr_partiel boolean default false;
ALTER TABLE cerfa ADD COLUMN tr_desc boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_co_elt_pro boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_nouv_haut_surf boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_co_clot boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_aut_coup_aba_arb boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_ouv_infra boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_aut_inst_mob boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_aut_plant boolean default false;
ALTER TABLE cerfa ADD COLUMN avap_aut_auv_elec boolean default false;
ALTER TABLE cerfa ADD COLUMN tax_dest_loc_tr boolean default false;

ALTER TABLE donnees_techniques ADD COLUMN co_statio_avt_shob character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_statio_apr_shob character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_statio_avt_surf character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_statio_apr_surf character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_trx_amgt character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_modif_aspect character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_modif_struct character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_ouvr_elec character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_ouvr_infra character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_trx_imm character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN co_cstr_shob character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN am_voyage_deb character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN am_voyage_fin character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN am_modif_amgt character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN am_lot_max_shob character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN mod_desc character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN tr_total character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN tr_partiel character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN tr_desc character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_co_elt_pro character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_nouv_haut_surf character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_co_clot character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_aut_coup_aba_arb character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_ouv_infra character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_aut_inst_mob character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_aut_plant character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN avap_aut_auv_elec character varying(250);
ALTER TABLE donnees_techniques ADD COLUMN tax_dest_loc_tr character varying(250);