--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.3.0-a7
--
-- @package openfoncier
-- @version SVN : $Id: v3.3.0-a7.sql 1804 2013-04-30 14:45:17Z fmichon $
--------------------------------------------------------------------------------

ALTER TABLE cerfa ADD COLUMN terr_juri_titul boolean;
ALTER TABLE cerfa ADD COLUMN terr_juri_lot boolean;
ALTER TABLE cerfa ADD COLUMN terr_juri_zac boolean;
ALTER TABLE cerfa ADD COLUMN terr_juri_afu boolean;
ALTER TABLE cerfa ADD COLUMN terr_juri_pup boolean;
ALTER TABLE cerfa ADD COLUMN terr_juri_oin boolean;
ALTER TABLE cerfa ADD COLUMN terr_juri_desc boolean;
ALTER TABLE cerfa ADD COLUMN terr_div_surf_etab boolean;
ALTER TABLE cerfa ADD COLUMN terr_div_surf_av_div boolean;

ALTER TABLE donnees_techniques ADD COLUMN terr_juri_titul character varying(20);
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_lot character varying(20);
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_zac character varying(20);
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_afu character varying(20);
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_pup character varying(20);
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_oin character varying(20);
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_desc text;
ALTER TABLE donnees_techniques ADD COLUMN terr_div_surf_etab numeric;
ALTER TABLE donnees_techniques ADD COLUMN terr_div_surf_av_div numeric;


-- Ajout des dates manquante dans le dossier d'autorisation
ALTER TABLE dossier_autorisation ADD COLUMN date_depot date;
ALTER TABLE dossier_autorisation ADD COLUMN date_decision date;
ALTER TABLE dossier_autorisation ADD COLUMN date_validite date;
ALTER TABLE dossier_autorisation ADD COLUMN date_chantier date;
ALTER TABLE dossier_autorisation ADD COLUMN date_achevement date;

-- Modification de l'enregistrement de l'état du DA
INSERT INTO etat_dossier_autorisation VALUES(1, 'En cours');
UPDATE dossier_autorisation SET etat_dossier_autorisation = 1 WHERE etat_dossier_autorisation = 7;
UPDATE lien_demande_type_etat_dossier_autorisation SET etat_dossier_autorisation = 1 WHERE etat_dossier_autorisation = 7;
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 7;
UPDATE dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 3;
UPDATE dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 5;
UPDATE lien_demande_type_etat_dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 3;
UPDATE lien_demande_type_etat_dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 5;
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 3;
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 5;
INSERT INTO etat_dossier_autorisation VALUES(3, 'Abandonné');
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 8;
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 6;

-- Ajout des champs d'état manquant
ALTER TABLE dossier_autorisation ADD COLUMN avis_decision integer;
ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);

ALTER TABLE dossier_autorisation ADD COLUMN etat_dernier_dossier_instruction_accepte integer;
ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_etat_dernier_dossier_instruction_accepte_fkey 
    FOREIGN KEY (etat_dernier_dossier_instruction_accepte) REFERENCES etat_dossier_autorisation(etat_dossier_autorisation);

-- Mise à jour du paramétrage suite aux modification précédante
UPDATE om_parametre SET valeur = '1' WHERE libelle = 'etat_initial_dossier_autorisation';

-- Ajout du champ version
ALTER TABLE dossier ADD COLUMN version integer;

UPDATE dossier SET version = substring(dossier from '.$')::integer;

UPDATE om_sousetat SET om_sql = 'SELECT

    CONCAT(dossier_autorisation_type.libelle, ''

'',dossier.dossier) as dossier,

    CONCAT(''Dépôt le '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''
Notifié le '', COALESCE(to_char(dossier.date_complet,''DD/MM/YYYY''),''inconu'')) as dates,

    CASE
        WHEN petitionnaire_principal.qualite=''particulier''

            THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '',
                                          petitionnaire_principal.particulier_prenom, ''
'',
                                          petitionnaire_principal.numero,'' '',
                                          petitionnaire_principal.voie,''
'',
                                          petitionnaire_principal.complement,''
'',
                                          petitionnaire_principal.lieu_dit,''
'',
                                          petitionnaire_principal.bp,''
'',
                                          petitionnaire_principal.code_postal,'' '',
                                          petitionnaire_principal.localite, '' '',
                                          petitionnaire_principal.cedex
                                          ))

    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '',
                                      petitionnaire_principal.personne_morale_denomination, ''
'',
                                          petitionnaire_principal.numero,'' '',
                                          petitionnaire_principal.voie,''
'',
                                          petitionnaire_principal.complement,''
'',
                                          petitionnaire_principal.lieu_dit,''
'',
                                          petitionnaire_principal.bp,''
'',
                                          petitionnaire_principal.code_postal,'' '',
                                          petitionnaire_principal.localite, '' '',
                                          petitionnaire_principal.cedex
                                      ))
    END as demandeur,

    CONCAT(dossier.terrain_adresse_voie_numero, '' '', dossier.complement, ''
'', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite,''
Arrondissement : '', arrondissement.libelle) as terrain,


    CONCAT(''superficie : '', '' '', ''
nombre de logements : '', '' '') as informations,

    CONCAT(''Délai '', delai, '' mois
Date limite le '', COALESCE(to_char(date_limite,''DD/MM/YYYY''),''inconu'')) as limite

FROM
  &DB_PREFIXEdossier
  LEFT JOIN &DB_PREFIXEdossier_autorisation
    ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
  LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille
    ON dossier_autorisation.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
  LEFT JOIN &DB_PREFIXEdossier_autorisation_type
    ON dossier_autorisation_type_detaille.dossier_autorisation_type=dossier_autorisation_type.dossier_autorisation_type
  LEFT JOIN &DB_PREFIXElien_dossier_demandeur
    ON dossier.dossier = lien_dossier_demandeur.dossier
  LEFT JOIN &DB_PREFIXEdemandeur as petitionnaire_principal
    ON lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
  LEFT JOIN &DB_PREFIXEavis_decision
    ON dossier.avis_decision=avis_decision.avis_decision
  LEFT JOIN &DB_PREFIXEarrondissement
    ON dossier.terrain_adresse_code_postal = arrondissement.code_postal
  LEFT JOIN &DB_PREFIXEdonnees_techniques
    ON donnees_techniques.dossier_instruction=dossier.dossier

WHERE
    (select e.statut from &DB_PREFIXEetat e where e.etat = dossier.etat ) = ''encours''

ORDER BY
    dossier_autorisation_type.libelle, arrondissement.libelle' WHERE om_sousetat=19; 

-- V4.4.0
ALTER TABLE om_sig_map_wms ADD COLUMN baselayer character varying(3);
ALTER TABLE om_sig_map_wms ADD COLUMN singletile character varying(3);
ALTER TABLE om_sig_map_wms ADD COLUMN sql_filter text;
ALTER TABLE om_sig_map_wms ADD COLUMN maxzoomlevel integer;
ALTER TABLE om_sig_wms ADD COLUMN cache_type character varying(3);
ALTER TABLE om_sig_wms ADD COLUMN cache_gfi_chemin character varying(255);
ALTER TABLE om_sig_wms ADD COLUMN cache_gfi_couches character varying(255);


UPDATE om_sousetat SET om_sql = 'SELECT  ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier,
 ''Depot ''||to_char(dossier.date_depot,''DD/MM/YYYY'')||'' Notifie le  ''||COALESCE(to_char(dossier.date_complet,''DD/MM/YYYY''),''inconu'') as date_dp_n,
COALESCE(demandeur_civilite,''sans'')||'' ''||demandeur_nom||'' ''||demandeur_adresse||'' ''||demandeur_cp||''  ''||demandeur_ville||'' Parcelle ''||dossier.parcelle as nom_adresse_demandeur,
dossier.terrain_numero||'' ''||dossier.terrain_numero_complement||'' ''||dossier.terrain_adresse||'' ''||dossier.terrain_adresse_complement||''
 ''||dossier.terrain_cp||''  ''||dossier.terrain_ville||'' ''||dossier.travaux.libelle as adresse_terrain_travaux,
''shon  ''||dossier.shon||'' shob ''||dossier.shob as SN_SB,
dossier.terrain_surface as superficie,
dossier.logement_nombre as nbr_logement,
COALESCE(avis_decision.libelle,''inconu'') as avis_decision,
''Decision''||COALESCE(to_char(dossier.date_decision,''DD/MM/YYYY''),''inconu'')||'' Limite ''||COALESCE(to_char(dossier.date_limite,''DD/MM/YYYY''),''inconu'') as date_dc_l,
dossier.delai||'' mois'' as delai, '' '' as date_affichage_decision, '' '' as DOC_DAT_Conformite
from dossier left join travaux on dossier.travaux=travaux.travaux left join avis_decision on dossier.avis_decision=avis_decision.avis_decision
 where dossier.nature=''&nature'' AND (date_decision>=''&datedebut''
 AND date_decision<=''&datefin'')
ORDER BY dossier' WHERE  om_sousetat=10;

UPDATE om_sousetat SET om_sql = 'SELECT  ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier,
to_char(dossier.date_depot,''DD/MM/YYYY'') as date_depot,
COALESCE(demandeur_civilite,''sans'')||'' ''||demandeur_nom as nom_demandeur,
dossier.terrain_numero||'' ''||dossier.terrain_numero_complement||'' ''||dossier.terrain_adresse||'' ''||dossier.terrain_adresse_complement||''
 ''||dossier.terrain_cp||''  ''||dossier.terrain_ville as adresse_terrain,
dossier.shon,
dossier.logement_nombre as nb_logt
from dossier
 where dossier.nature=''&nature'' AND (date_depot>=''&datedebut''
 AND date_depot<=''&datefin'')
ORDER BY dossier' WHERE  om_sousetat=9;

UPDATE om_sousetat SET om_sql = 'SELECT  ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier,
 ''Depot ''||to_char(dossier.date_depot,''DD/MM/YYYY'')||'' Notifie le  ''||COALESCE(to_char(dossier.date_complet,''DD/MM/YYYY''),''inconu'') as date_dp_n,
COALESCE(demandeur_civilite,''sans'')||'' ''||demandeur_nom||'' ''||demandeur_adresse||'' ''||demandeur_cp||''  ''||demandeur_ville||'' Parcelle ''||parcelle as nom_adresse_demandeur,
dossier.terrain_numero||'' ''||dossier.terrain_numero_complement||'' ''||dossier.terrain_adresse||'' ''||dossier.terrain_adresse_complement||''
 ''||dossier.terrain_cp||''  ''||dossier.terrain_ville||'' ''||travaux.libelle as adresse_terrain_travaux,
''shon  ''||dossier.shon||'' shob ''||dossier.shob as SN_SB,
dossier.terrain_surface as superficie,
dossier.logement_nombre as nbr_logement,
COALESCE(avis_decision.libelle,''inconu'') as avis_decision,
''Decision''||COALESCE(to_char(dossier.date_decision,''DD/MM/YYYY''),''inconu'')||'' Limite ''||COALESCE(to_char(dossier.date_limite,''DD/MM/YYYY''),''inconu'') as date_dc_l,
dossier.delai||'' mois'' as delai, '' '' as date_affichage_decision, '' '' as DOC_DAT_Conformite
from dossier left join travaux on dossier.travaux=travaux.travaux left join avis_decision on dossier.avis_decision=avis_decision.avis_decision
 where dossier.nature=''&nature'' AND (date_depot>=''&datedebut''
 AND date_depot<=''&datefin'')
ORDER BY dossier' WHERE  om_sousetat=8;

-- Suppression du champ methode_trigger
ALTER TABLE action DROP COLUMN methode_trigger;

--
UPDATE om_droit set libelle='suivi_retours_de_consultation' where libelle='avis_code_barre';
UPDATE om_droit set libelle='suivi_mise_a_jour_des_dates' where libelle='maj_dates_suivi';
UPDATE om_droit set libelle='suivi_envoi_lettre_rar' where libelle='envoi_lettre_rar';
UPDATE om_droit set libelle='suivi_bordereaux' where libelle='bordereaux_envoi';

-- Ajout des champs pour gérer l'incomplétude
-- table dossier
ALTER TABLE dossier ADD COLUMN incompletude boolean NOT NULL default false;
ALTER TABLE dossier ADD COLUMN evenement_suivant_tacite integer;
ALTER TABLE dossier ADD COLUMN evenement_suivant_tacite_incompletude integer;
ALTER TABLE dossier ADD COLUMN etat_pendant_incompletude character varying(20);
ALTER TABLE dossier ADD COLUMN date_limite_incompletude date;
ALTER TABLE dossier ADD COLUMN delai_incompletude integer;

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_evenement_evenement_suivant_tacite_fkey 
    FOREIGN KEY (evenement_suivant_tacite) REFERENCES evenement(evenement);

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_evenement_evenement_suivant_tacite_incompletude_fkey 
    FOREIGN KEY (evenement_suivant_tacite_incompletude) REFERENCES evenement(evenement);

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_etat_etat_pendant_incompletude_fkey 
    FOREIGN KEY (etat_pendant_incompletude) REFERENCES etat(etat);

-- table instruction
ALTER TABLE instruction ADD COLUMN archive_incompletude boolean NOT NULL default false;
ALTER TABLE instruction ADD COLUMN archive_evenement_suivant_tacite integer;
ALTER TABLE instruction ADD COLUMN archive_evenement_suivant_tacite_incompletude integer;
ALTER TABLE instruction ADD COLUMN archive_etat_pendant_incompletude character varying(20);
ALTER TABLE instruction ADD COLUMN archive_date_limite_incompletude date;
ALTER TABLE instruction ADD COLUMN archive_delai_incompletude integer;

ALTER TABLE ONLY instruction
    ADD CONSTRAINT dossier_evenement_archive_evenement_suivant_tacite_fkey 
    FOREIGN KEY (archive_evenement_suivant_tacite) REFERENCES evenement(evenement);

ALTER TABLE ONLY instruction
    ADD CONSTRAINT dossier_evenement_archive_evenement_suivant_tacite_incompletude_fkey 
    FOREIGN KEY (archive_evenement_suivant_tacite_incompletude) REFERENCES evenement(evenement);

ALTER TABLE ONLY instruction
    ADD CONSTRAINT dossier_etat_archive_etat_pendant_incompletude_fkey 
    FOREIGN KEY (archive_etat_pendant_incompletude) REFERENCES etat(etat);

ALTER TABLE action ADD COLUMN regle_date_limite_incompletude character varying(60);
ALTER TABLE action ADD COLUMN regle_delai_incompletude character varying(60);

UPDATE action SET regle_avis='avis_decision' WHERE regle_avis='avis';

-- Suppression des champs types et objet_dossier de la table dossier
ALTER TABLE dossier DROP COLUMN types;
ALTER TABLE dossier DROP COLUMN objet_dossier;

--

insert into om_droit(om_droit, libelle, om_profil) values
(nextval('om_droit_seq'), 'dossier_commission_modifier_lu', 3),
(nextval('om_droit_seq'), 'blocnote', 3)
;
