| 1 |
nhaye |
1616 |
-- Mise à jour de la base pour la version 3.3.0-a7 |
| 2 |
|
|
|
| 3 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_titul boolean; |
| 4 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_lot boolean; |
| 5 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_zac boolean; |
| 6 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_afu boolean; |
| 7 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_pup boolean; |
| 8 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_oin boolean; |
| 9 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_juri_desc boolean; |
| 10 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_div_surf_etab boolean; |
| 11 |
|
|
ALTER TABLE cerfa ADD COLUMN terr_div_surf_av_div boolean; |
| 12 |
|
|
|
| 13 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_titul character varying(20); |
| 14 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_lot character varying(20); |
| 15 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_zac character varying(20); |
| 16 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_afu character varying(20); |
| 17 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_pup character varying(20); |
| 18 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_oin character varying(20); |
| 19 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_juri_desc text; |
| 20 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_div_surf_etab numeric; |
| 21 |
|
|
ALTER TABLE donnees_techniques ADD COLUMN terr_div_surf_av_div numeric; |
| 22 |
nhaye |
1639 |
|
| 23 |
|
|
|
| 24 |
|
|
-- Ajout des dates manquante dans le dossier d'autorisation |
| 25 |
|
|
ALTER TABLE dossier_autorisation ADD COLUMN date_depot date; |
| 26 |
|
|
ALTER TABLE dossier_autorisation ADD COLUMN date_decision date; |
| 27 |
|
|
ALTER TABLE dossier_autorisation ADD COLUMN date_validite date; |
| 28 |
|
|
ALTER TABLE dossier_autorisation ADD COLUMN date_chantier date; |
| 29 |
nhaye |
1640 |
ALTER TABLE dossier_autorisation ADD COLUMN date_achevement date; |
| 30 |
|
|
|
| 31 |
|
|
-- Modification de l'enregistrement de l'état du DA |
| 32 |
|
|
INSERT INTO etat_dossier_autorisation VALUES(1, 'En cours'); |
| 33 |
|
|
UPDATE dossier_autorisation SET etat_dossier_autorisation = 1 WHERE etat_dossier_autorisation = 7; |
| 34 |
|
|
UPDATE lien_demande_type_etat_dossier_autorisation SET etat_dossier_autorisation = 1 WHERE etat_dossier_autorisation = 7; |
| 35 |
|
|
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 7; |
| 36 |
|
|
UPDATE dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 3; |
| 37 |
|
|
UPDATE dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 5; |
| 38 |
|
|
UPDATE lien_demande_type_etat_dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 3; |
| 39 |
|
|
UPDATE lien_demande_type_etat_dossier_autorisation SET etat_dossier_autorisation = 2 WHERE etat_dossier_autorisation = 5; |
| 40 |
|
|
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 3; |
| 41 |
|
|
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 5; |
| 42 |
|
|
INSERT INTO etat_dossier_autorisation VALUES(3, 'Abandonné'); |
| 43 |
|
|
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 8; |
| 44 |
|
|
DELETE FROM etat_dossier_autorisation WHERE etat_dossier_autorisation = 6; |
| 45 |
|
|
|
| 46 |
nhaye |
1643 |
-- Ajout des champs d'état manquant |
| 47 |
nhaye |
1640 |
ALTER TABLE dossier_autorisation ADD COLUMN avis_decision integer; |
| 48 |
|
|
ALTER TABLE ONLY dossier_autorisation |
| 49 |
|
|
ADD CONSTRAINT dossier_autorisation_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision); |
| 50 |
|
|
|
| 51 |
nhaye |
1641 |
ALTER TABLE dossier_autorisation ADD COLUMN etat_dernier_dossier_instruction_accepte integer; |
| 52 |
nhaye |
1640 |
ALTER TABLE ONLY dossier_autorisation |
| 53 |
nhaye |
1641 |
ADD CONSTRAINT dossier_autorisation_etat_dernier_dossier_instruction_accepte_fkey |
| 54 |
nhaye |
1643 |
FOREIGN KEY (etat_dernier_dossier_instruction_accepte) REFERENCES etat_dossier_autorisation(etat_dossier_autorisation); |
| 55 |
|
|
|
| 56 |
|
|
-- Mise à jour du paramétrage suite aux modification précédante |
| 57 |
|
|
UPDATE om_parametre SET valeur = '1' WHERE libelle = 'etat_initial_dossier_autorisation'; |
| 58 |
|
|
|
| 59 |
|
|
-- Ajout du champ version |
| 60 |
|
|
ALTER TABLE dossier ADD COLUMN version integer; |
| 61 |
|
|
|
| 62 |
nhaye |
1647 |
UPDATE dossier SET version = substring(dossier from '.$')::integer; |
| 63 |
|
|
|
| 64 |
|
|
UPDATE om_sousetat SET om_sql = 'SELECT |
| 65 |
|
|
|
| 66 |
|
|
CONCAT(dossier_autorisation_type.libelle, '' |
| 67 |
|
|
|
| 68 |
|
|
'',dossier.dossier) as dossier, |
| 69 |
|
|
|
| 70 |
|
|
CONCAT(''Dépôt le '', to_char(dossier.date_depot,''DD/MM/YYYY''), '' |
| 71 |
|
|
Notifié le '', COALESCE(to_char(dossier.date_complet,''DD/MM/YYYY''),''inconu'')) as dates, |
| 72 |
|
|
|
| 73 |
|
|
CASE |
| 74 |
|
|
WHEN petitionnaire_principal.qualite=''particulier'' |
| 75 |
|
|
|
| 76 |
|
|
THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', |
| 77 |
|
|
petitionnaire_principal.particulier_prenom, '' |
| 78 |
|
|
'', |
| 79 |
|
|
petitionnaire_principal.numero,'' '', |
| 80 |
|
|
petitionnaire_principal.voie,'' |
| 81 |
|
|
'', |
| 82 |
|
|
petitionnaire_principal.complement,'' |
| 83 |
|
|
'', |
| 84 |
|
|
petitionnaire_principal.lieu_dit,'' |
| 85 |
|
|
'', |
| 86 |
|
|
petitionnaire_principal.bp,'' |
| 87 |
|
|
'', |
| 88 |
|
|
petitionnaire_principal.code_postal,'' '', |
| 89 |
|
|
petitionnaire_principal.localite, '' '', |
| 90 |
|
|
petitionnaire_principal.cedex |
| 91 |
|
|
)) |
| 92 |
|
|
|
| 93 |
|
|
ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', |
| 94 |
|
|
petitionnaire_principal.personne_morale_denomination, '' |
| 95 |
|
|
'', |
| 96 |
|
|
petitionnaire_principal.numero,'' '', |
| 97 |
|
|
petitionnaire_principal.voie,'' |
| 98 |
|
|
'', |
| 99 |
|
|
petitionnaire_principal.complement,'' |
| 100 |
|
|
'', |
| 101 |
|
|
petitionnaire_principal.lieu_dit,'' |
| 102 |
|
|
'', |
| 103 |
|
|
petitionnaire_principal.bp,'' |
| 104 |
|
|
'', |
| 105 |
|
|
petitionnaire_principal.code_postal,'' '', |
| 106 |
|
|
petitionnaire_principal.localite, '' '', |
| 107 |
|
|
petitionnaire_principal.cedex |
| 108 |
|
|
)) |
| 109 |
|
|
END as demandeur, |
| 110 |
|
|
|
| 111 |
|
|
CONCAT(dossier.terrain_adresse_voie_numero, '' '', dossier.complement, '' |
| 112 |
|
|
'', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite,'' |
| 113 |
|
|
Arrondissement : '', arrondissement.libelle) as terrain, |
| 114 |
|
|
|
| 115 |
|
|
|
| 116 |
|
|
CONCAT(''superficie : '', '' '', '' |
| 117 |
|
|
nombre de logements : '', '' '') as informations, |
| 118 |
|
|
|
| 119 |
|
|
CONCAT(''Délai '', delai, '' mois |
| 120 |
|
|
Date limite le '', COALESCE(to_char(date_limite,''DD/MM/YYYY''),''inconu'')) as limite |
| 121 |
|
|
|
| 122 |
|
|
FROM |
| 123 |
|
|
&DB_PREFIXEdossier |
| 124 |
|
|
LEFT JOIN &DB_PREFIXEdossier_autorisation |
| 125 |
|
|
ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation |
| 126 |
|
|
LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille |
| 127 |
|
|
ON dossier_autorisation.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille |
| 128 |
|
|
LEFT JOIN &DB_PREFIXEdossier_autorisation_type |
| 129 |
|
|
ON dossier_autorisation_type_detaille.dossier_autorisation_type=dossier_autorisation_type.dossier_autorisation_type |
| 130 |
|
|
LEFT JOIN &DB_PREFIXElien_dossier_demandeur |
| 131 |
|
|
ON dossier.dossier = lien_dossier_demandeur.dossier |
| 132 |
|
|
LEFT JOIN &DB_PREFIXEdemandeur as petitionnaire_principal |
| 133 |
|
|
ON lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE |
| 134 |
|
|
LEFT JOIN &DB_PREFIXEavis_decision |
| 135 |
|
|
ON dossier.avis_decision=avis_decision.avis_decision |
| 136 |
|
|
LEFT JOIN &DB_PREFIXEarrondissement |
| 137 |
|
|
ON dossier.terrain_adresse_code_postal = arrondissement.code_postal |
| 138 |
|
|
LEFT JOIN &DB_PREFIXEdonnees_techniques |
| 139 |
|
|
ON donnees_techniques.dossier_instruction=dossier.dossier |
| 140 |
|
|
|
| 141 |
|
|
WHERE |
| 142 |
|
|
(select e.statut from &DB_PREFIXEetat e where e.etat = dossier.etat ) = ''encours'' |
| 143 |
|
|
|
| 144 |
|
|
ORDER BY |
| 145 |
|
|
dossier_autorisation_type.libelle, arrondissement.libelle' WHERE om_sousetat=19; |
| 146 |
|
|
|
| 147 |
nhaye |
1648 |
-- V4.4.0 |
| 148 |
|
|
ALTER TABLE om_sig_map_wms ADD COLUMN baselayer character varying(3); |
| 149 |
|
|
ALTER TABLE om_sig_map_wms ADD COLUMN singletile character varying(3); |
| 150 |
|
|
ALTER TABLE om_sig_map_wms ADD COLUMN sql_filter text; |
| 151 |
|
|
ALTER TABLE om_sig_map_wms ADD COLUMN maxzoomlevel integer; |
| 152 |
|
|
ALTER TABLE om_sig_wms ADD COLUMN cache_type character varying(3); |
| 153 |
|
|
ALTER TABLE om_sig_wms ADD COLUMN cache_gfi_chemin character varying(255); |
| 154 |
nhaye |
1651 |
ALTER TABLE om_sig_wms ADD COLUMN cache_gfi_couches character varying(255); |
| 155 |
|
|
|
| 156 |
|
|
|
| 157 |
|
|
UPDATE om_sousetat SET om_sql = 'SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier, |
| 158 |
|
|
''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, |
| 159 |
|
|
COALESCE(demandeur_civilite,''sans'')||'' ''||demandeur_nom||'' ''||demandeur_adresse||'' ''||demandeur_cp||'' ''||demandeur_ville||'' Parcelle ''||dossier.parcelle as nom_adresse_demandeur, |
| 160 |
|
|
dossier.terrain_numero||'' ''||dossier.terrain_numero_complement||'' ''||dossier.terrain_adresse||'' ''||dossier.terrain_adresse_complement||'' |
| 161 |
|
|
''||dossier.terrain_cp||'' ''||dossier.terrain_ville||'' ''||dossier.travaux.libelle as adresse_terrain_travaux, |
| 162 |
|
|
''shon ''||dossier.shon||'' shob ''||dossier.shob as SN_SB, |
| 163 |
|
|
dossier.terrain_surface as superficie, |
| 164 |
|
|
dossier.logement_nombre as nbr_logement, |
| 165 |
|
|
COALESCE(avis_decision.libelle,''inconu'') as avis_decision, |
| 166 |
|
|
''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, |
| 167 |
|
|
dossier.delai||'' mois'' as delai, '' '' as date_affichage_decision, '' '' as DOC_DAT_Conformite |
| 168 |
|
|
from dossier left join travaux on dossier.travaux=travaux.travaux left join avis_decision on dossier.avis_decision=avis_decision.avis_decision |
| 169 |
|
|
where dossier.nature=''&nature'' AND (date_decision>=''&datedebut'' |
| 170 |
|
|
AND date_decision<=''&datefin'') |
| 171 |
|
|
ORDER BY dossier' WHERE om_sousetat=10; |
| 172 |
|
|
|
| 173 |
|
|
UPDATE om_sousetat SET om_sql = 'SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier, |
| 174 |
|
|
to_char(dossier.date_depot,''DD/MM/YYYY'') as date_depot, |
| 175 |
|
|
COALESCE(demandeur_civilite,''sans'')||'' ''||demandeur_nom as nom_demandeur, |
| 176 |
|
|
dossier.terrain_numero||'' ''||dossier.terrain_numero_complement||'' ''||dossier.terrain_adresse||'' ''||dossier.terrain_adresse_complement||'' |
| 177 |
|
|
''||dossier.terrain_cp||'' ''||dossier.terrain_ville as adresse_terrain, |
| 178 |
|
|
dossier.shon, |
| 179 |
|
|
dossier.logement_nombre as nb_logt |
| 180 |
|
|
from dossier |
| 181 |
|
|
where dossier.nature=''&nature'' AND (date_depot>=''&datedebut'' |
| 182 |
|
|
AND date_depot<=''&datefin'') |
| 183 |
|
|
ORDER BY dossier' WHERE om_sousetat=9; |
| 184 |
|
|
|
| 185 |
|
|
UPDATE om_sousetat SET om_sql = 'SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier, |
| 186 |
|
|
''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, |
| 187 |
|
|
COALESCE(demandeur_civilite,''sans'')||'' ''||demandeur_nom||'' ''||demandeur_adresse||'' ''||demandeur_cp||'' ''||demandeur_ville||'' Parcelle ''||parcelle as nom_adresse_demandeur, |
| 188 |
|
|
dossier.terrain_numero||'' ''||dossier.terrain_numero_complement||'' ''||dossier.terrain_adresse||'' ''||dossier.terrain_adresse_complement||'' |
| 189 |
|
|
''||dossier.terrain_cp||'' ''||dossier.terrain_ville||'' ''||travaux.libelle as adresse_terrain_travaux, |
| 190 |
|
|
''shon ''||dossier.shon||'' shob ''||dossier.shob as SN_SB, |
| 191 |
|
|
dossier.terrain_surface as superficie, |
| 192 |
|
|
dossier.logement_nombre as nbr_logement, |
| 193 |
|
|
COALESCE(avis_decision.libelle,''inconu'') as avis_decision, |
| 194 |
|
|
''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, |
| 195 |
|
|
dossier.delai||'' mois'' as delai, '' '' as date_affichage_decision, '' '' as DOC_DAT_Conformite |
| 196 |
|
|
from dossier left join travaux on dossier.travaux=travaux.travaux left join avis_decision on dossier.avis_decision=avis_decision.avis_decision |
| 197 |
|
|
where dossier.nature=''&nature'' AND (date_depot>=''&datedebut'' |
| 198 |
|
|
AND date_depot<=''&datefin'') |
| 199 |
nhaye |
1662 |
ORDER BY dossier' WHERE om_sousetat=8; |
| 200 |
|
|
|
| 201 |
|
|
-- Suppression du champ methode_trigger |
| 202 |
|
|
ALTER TABLE action DROP COLUMN methode_trigger; |
| 203 |
fmichon |
1666 |
|
| 204 |
|
|
-- |
| 205 |
|
|
UPDATE om_droit set libelle='suivi_retours_de_consultation' where libelle='avis_code_barre'; |
| 206 |
fmichon |
1667 |
UPDATE om_droit set libelle='suivi_mise_a_jour_des_dates' where libelle='maj_dates_suivi'; |
| 207 |
fmichon |
1666 |
|
| 208 |
fmichon |
1667 |
|
Parent Directory
| 
Revision Log