| 1 |
INSERT INTO nature VALUES ('DD', 'Dépôt de dossier DAT'); |
| 2 |
INSERT INTO nature VALUES ('DO', 'Demande d''ouverture ERP DAT'); |
| 3 |
INSERT INTO nature VALUES ('AD', 'Annulation de la demande'); |
| 4 |
INSERT INTO nature VALUES ('DA', 'Demande PC ratachée DAACT'); |
| 5 |
ALTER TABLE dossier ADD COLUMN erp boolean; |
| 6 |
UPDATE action SET regle_avis = 'avis_decision' WHERE libelle = 'accepter un dossier'; |
| 7 |
|
| 8 |
-- |
| 9 |
-- Ajout de table: avis_consultation_tmp; |
| 10 |
-- |
| 11 |
|
| 12 |
|
| 13 |
CREATE TABLE avis_consultation_tmp ( |
| 14 |
avis character varying(2) NOT NULL, |
| 15 |
libelle character varying(30) NOT NULL, |
| 16 |
typeavis character(1) DEFAULT ''::bpchar NOT NULL, |
| 17 |
sitadel character(1) DEFAULT ''::bpchar NOT NULL, |
| 18 |
sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL |
| 19 |
); |
| 20 |
|
| 21 |
|
| 22 |
-- |
| 23 |
-- Data for Name: avis_consultation_tmp; |
| 24 |
-- |
| 25 |
|
| 26 |
INSERT INTO avis_consultation_tmp VALUES ('D', 'Defavorable', 'D', '6', ' '); |
| 27 |
INSERT INTO avis_consultation_tmp VALUES ('F', 'Favorable', 'F', '4', ' '); |
| 28 |
INSERT INTO avis_consultation_tmp VALUES ('F1', 'Favorable avec Reserve', 'F', '4', ' '); |
| 29 |
INSERT INTO avis_consultation_tmp VALUES ('T', 'Tacite', 'F', '2', ' '); |
| 30 |
INSERT INTO avis_consultation_tmp VALUES ('A', 'Autre', ' ', '7', ' '); |
| 31 |
|
| 32 |
|
| 33 |
-- |
| 34 |
-- Ajout de la table 'service_categorie' |
| 35 |
-- |
| 36 |
CREATE TABLE service_categorie ( |
| 37 |
service_categorie integer, |
| 38 |
libelle varchar(70) NOT NULL default '' |
| 39 |
); |
| 40 |
|
| 41 |
ALTER TABLE ONLY service_categorie |
| 42 |
ADD CONSTRAINT service_categorie_pkey PRIMARY KEY (service_categorie); |
| 43 |
|
| 44 |
CREATE SEQUENCE service_categorie_seq |
| 45 |
INCREMENT 1 |
| 46 |
MINVALUE 1 |
| 47 |
MAXVALUE 9223372036854775807 |
| 48 |
START 1 |
| 49 |
CACHE 1; |
| 50 |
|
| 51 |
-- |
| 52 |
-- Modification de la table 'service' et des clés étangères |
| 53 |
-- |
| 54 |
ALTER TABLE consultation DROP CONSTRAINT consultation_service_fkey; |
| 55 |
ALTER TABLE service DROP CONSTRAINT service_pkey; |
| 56 |
|
| 57 |
ALTER TABLE consultation RENAME COLUMN service TO service_old; |
| 58 |
ALTER TABLE service RENAME COLUMN service TO service_old; |
| 59 |
|
| 60 |
CREATE SEQUENCE service_seq |
| 61 |
START WITH 1 |
| 62 |
INCREMENT BY 1 |
| 63 |
NO MAXVALUE |
| 64 |
NO MINVALUE |
| 65 |
CACHE 1; |
| 66 |
|
| 67 |
ALTER TABLE service ADD COLUMN service integer NOT NULL DEFAULT nextval('service_seq'::regclass); |
| 68 |
ALTER TABLE consultation ADD COLUMN service integer; |
| 69 |
|
| 70 |
UPDATE consultation SET service=(select service.service from service where service_old=service.service_old); |
| 71 |
|
| 72 |
ALTER TABLE service ADD COLUMN consultation_papier boolean; |
| 73 |
ALTER TABLE service ADD COLUMN notification_email boolean; |
| 74 |
ALTER TABLE service ADD COLUMN om_validite_debut date; |
| 75 |
ALTER TABLE service ADD COLUMN om_validite_fin date; |
| 76 |
ALTER TABLE service ADD COLUMN type_consultation varchar(70) NOT NULL DEFAULT 'avec_avis_attendu'; |
| 77 |
|
| 78 |
ALTER TABLE service RENAME COLUMN service_old TO abrege; |
| 79 |
ALTER TABLE consultation DROP COLUMN service_old; |
| 80 |
|
| 81 |
ALTER TABLE ONLY service |
| 82 |
ADD CONSTRAINT service_pkey PRIMARY KEY (service); |
| 83 |
|
| 84 |
ALTER TABLE ONLY consultation |
| 85 |
ADD CONSTRAINT consultation_service_fkey FOREIGN KEY (service) REFERENCES service(service); |
| 86 |
|
| 87 |
ALTER SEQUENCE service_seq OWNED BY service.service; |
| 88 |
|
| 89 |
ALTER TABLE service ALTER COLUMN service DROP DEFAULT; |
| 90 |
|
| 91 |
-- |
| 92 |
-- Ajout de la table 'lien_service_service_categorie' |
| 93 |
-- |
| 94 |
|
| 95 |
CREATE TABLE lien_service_service_categorie ( |
| 96 |
lien_service_service_categorie integer, |
| 97 |
service_categorie integer, |
| 98 |
service integer |
| 99 |
); |
| 100 |
|
| 101 |
ALTER TABLE ONLY lien_service_service_categorie |
| 102 |
ADD CONSTRAINT lien_service_service_categorie_pkey PRIMARY KEY (lien_service_service_categorie); |
| 103 |
ALTER TABLE ONLY lien_service_service_categorie |
| 104 |
ADD CONSTRAINT lien_service_service_categorie_service_categorie_fkey FOREIGN KEY (service_categorie) REFERENCES service_categorie(service_categorie); |
| 105 |
ALTER TABLE ONLY lien_service_service_categorie |
| 106 |
ADD CONSTRAINT lien_service_service_categorie_service_fkey FOREIGN KEY (service) REFERENCES service(service); |
| 107 |
|
| 108 |
CREATE SEQUENCE lien_service_service_categorie_seq |
| 109 |
INCREMENT 1 |
| 110 |
MINVALUE 1 |
| 111 |
MAXVALUE 9223372036854775807 |
| 112 |
START 1 |
| 113 |
CACHE 1; |
| 114 |
|
| 115 |
-- |
| 116 |
-- Ajout de la table 'lien_service_utilisateur' |
| 117 |
-- |
| 118 |
|
| 119 |
CREATE TABLE lien_service_om_utilisateur ( |
| 120 |
lien_service_om_utilisateur integer, |
| 121 |
om_utilisateur bigint, |
| 122 |
service integer |
| 123 |
); |
| 124 |
|
| 125 |
ALTER TABLE ONLY lien_service_om_utilisateur |
| 126 |
ADD CONSTRAINT lien_service_om_utilisateur_pkey PRIMARY KEY (lien_service_om_utilisateur); |
| 127 |
ALTER TABLE ONLY lien_service_om_utilisateur |
| 128 |
ADD CONSTRAINT lien_service_om_utilisateur_om_utilisateur_fkey FOREIGN KEY (om_utilisateur) REFERENCES om_utilisateur(om_utilisateur); |
| 129 |
ALTER TABLE ONLY lien_service_om_utilisateur |
| 130 |
ADD CONSTRAINT lien_service_om_utilisateur_service_fkey FOREIGN KEY (service) REFERENCES service(service); |
| 131 |
|
| 132 |
CREATE SEQUENCE lien_service_om_utilisateur_seq |
| 133 |
INCREMENT 1 |
| 134 |
MINVALUE 1 |
| 135 |
MAXVALUE 9223372036854775807 |
| 136 |
START 1 |
| 137 |
CACHE 1; |
| 138 |
|
| 139 |
-- |
| 140 |
-- Ajout des tables 'avis_consultation' et 'avis_decision' |
| 141 |
-- |
| 142 |
|
| 143 |
CREATE TABLE avis_decision ( |
| 144 |
avis_old character varying(2) NOT NULL, |
| 145 |
libelle character varying(30) NOT NULL, |
| 146 |
typeavis character(1) DEFAULT ''::bpchar NOT NULL, |
| 147 |
sitadel character(1) DEFAULT ''::bpchar NOT NULL, |
| 148 |
sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL |
| 149 |
); |
| 150 |
|
| 151 |
CREATE SEQUENCE avis_decision_seq |
| 152 |
INCREMENT 1 |
| 153 |
MINVALUE 1 |
| 154 |
MAXVALUE 9223372036854775807 |
| 155 |
START 1 |
| 156 |
CACHE 1; |
| 157 |
|
| 158 |
CREATE TABLE avis_consultation ( |
| 159 |
avis_old character varying(2) NOT NULL, |
| 160 |
libelle character varying(30) NOT NULL, |
| 161 |
abrege character varying(10), |
| 162 |
om_validite_debut date, |
| 163 |
om_validite_fin date |
| 164 |
); |
| 165 |
|
| 166 |
CREATE SEQUENCE avis_consultation_seq |
| 167 |
INCREMENT 1 |
| 168 |
MINVALUE 1 |
| 169 |
MAXVALUE 9223372036854775807 |
| 170 |
START 1 |
| 171 |
CACHE 1; |
| 172 |
|
| 173 |
ALTER TABLE avis_decision ADD COLUMN avis_decision integer NOT NULL DEFAULT nextval('avis_decision_seq'::regclass); |
| 174 |
INSERT INTO avis_decision(avis_old, libelle, typeavis, sitadel, sitadel_motif) SELECT avis, libelle, typeavis, sitadel, sitadel_motif |
| 175 |
FROM avis; |
| 176 |
|
| 177 |
ALTER TABLE avis_consultation ADD COLUMN avis_consultation integer NOT NULL DEFAULT nextval('avis_consultation_seq'::regclass); |
| 178 |
INSERT INTO avis_consultation(avis_old, libelle) SELECT avis, libelle |
| 179 |
FROM avis_consultation_tmp; |
| 180 |
|
| 181 |
|
| 182 |
ALTER TABLE ONLY avis_decision |
| 183 |
ADD CONSTRAINT avis_decision_pkey PRIMARY KEY (avis_decision); |
| 184 |
ALTER TABLE ONLY avis_consultation |
| 185 |
ADD CONSTRAINT avis_consultation_pkey PRIMARY KEY (avis_consultation); |
| 186 |
ALTER SEQUENCE avis_consultation_seq OWNED BY avis_consultation.avis_consultation; |
| 187 |
ALTER SEQUENCE avis_decision_seq OWNED BY avis_decision.avis_decision; |
| 188 |
ALTER TABLE avis_decision ALTER COLUMN avis_decision DROP DEFAULT; |
| 189 |
ALTER TABLE avis_consultation ALTER COLUMN avis_consultation DROP DEFAULT; |
| 190 |
|
| 191 |
-- Changement des clés étrangères pour 'avis' |
| 192 |
ALTER TABLE consultation DROP CONSTRAINT consultation_avis_fkey; |
| 193 |
ALTER TABLE evenement DROP CONSTRAINT evenement_avis_fkey; |
| 194 |
ALTER TABLE instruction DROP CONSTRAINT instruction_avis_fkey; |
| 195 |
ALTER TABLE dossier DROP CONSTRAINT dossier_avis_fkey; |
| 196 |
|
| 197 |
ALTER TABLE consultation ADD COLUMN avis_consultation integer; |
| 198 |
ALTER TABLE evenement ADD COLUMN avis_decision integer; |
| 199 |
ALTER TABLE instruction ADD COLUMN avis_decision integer; |
| 200 |
ALTER TABLE dossier ADD COLUMN avis_decision integer; |
| 201 |
|
| 202 |
|
| 203 |
|
| 204 |
|
| 205 |
|
| 206 |
UPDATE consultation SET avis_consultation=(select avis_consultation.avis_consultation from avis_consultation where avis=avis_consultation.avis_old); |
| 207 |
|
| 208 |
-- |
| 209 |
-- Modification de la table 'consultation' |
| 210 |
-- |
| 211 |
ALTER TABLE consultation ADD COLUMN date_reception date; |
| 212 |
ALTER TABLE consultation ADD COLUMN motivation text DEFAULT ''; |
| 213 |
ALTER TABLE consultation ADD COLUMN fichier character varying(100); |
| 214 |
ALTER TABLE consultation ADD COLUMN lu boolean; |
| 215 |
|
| 216 |
|
| 217 |
|
| 218 |
UPDATE evenement SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old); |
| 219 |
UPDATE instruction SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old); |
| 220 |
UPDATE dossier SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old); |
| 221 |
|
| 222 |
ALTER TABLE consultation DROP COLUMN avis; |
| 223 |
ALTER TABLE evenement DROP COLUMN avis; |
| 224 |
ALTER TABLE instruction DROP COLUMN avis; |
| 225 |
ALTER TABLE dossier DROP COLUMN avis; |
| 226 |
|
| 227 |
ALTER TABLE ONLY consultation |
| 228 |
ADD CONSTRAINT consultation_avis_consultation_fkey FOREIGN KEY (avis_consultation) REFERENCES avis_consultation(avis_consultation); |
| 229 |
ALTER TABLE ONLY evenement |
| 230 |
ADD CONSTRAINT evenement_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision); |
| 231 |
ALTER TABLE ONLY instruction |
| 232 |
ADD CONSTRAINT instruction_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision); |
| 233 |
ALTER TABLE ONLY dossier |
| 234 |
ADD CONSTRAINT dossier_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision); |
| 235 |
ALTER TABLE avis_decision DROP COLUMN avis_old; |
| 236 |
ALTER TABLE avis_consultation DROP COLUMN avis_old; |
| 237 |
DROP TABLE avis; |
| 238 |
DROP TABLE avis_consultation_tmp; |
| 239 |
|
| 240 |
-- |
| 241 |
-- Ajout des droits sur les nouvelles tables |
| 242 |
-- |
| 243 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'service_categorie', '4'); |
| 244 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_decision', '4'); |
| 245 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_consultation', '4'); |
| 246 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_service_service_categorie', '4'); |
| 247 |
|
| 248 |
ALTER TABLE consultation ALTER service SET NOT NULL; |
| 249 |
ALTER TABLE dossier ADD COLUMN enjeu_erp boolean; |
| 250 |
ALTER TABLE dossier ADD COLUMN enjeu_urba boolean; |
| 251 |
|
| 252 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_mes_retours', 4); |
| 253 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_tous_retours', 4); |
| 254 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'PC', 2); -- droit d'instructeur |
| 255 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'messages_mes_retours', 2); -- droit d'instructeur |
| 256 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'messages_tous_retours', 2); -- droit d'instructeur |
| 257 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier_message_consulter', 2); -- droit d'instructeur |
| 258 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier_message_modifier', 2); -- droit d'instructeur |
| 259 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier_message_tab', 2); -- droit d'instructeur |
| 260 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier', 2); -- droit d'instructeur |
| 261 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dashboard', 2); -- droit d'instructeur |
| 262 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'menu_instruction', 2); -- droit d'instructeur |
| 263 |
|
| 264 |
-- |
| 265 |
-- Modification de la structure des instructeurs |
| 266 |
-- |
| 267 |
CREATE SEQUENCE direction_seq |
| 268 |
START WITH 1 |
| 269 |
INCREMENT BY 1 |
| 270 |
NO MINVALUE |
| 271 |
NO MAXVALUE |
| 272 |
CACHE 1; |
| 273 |
|
| 274 |
CREATE TABLE direction ( |
| 275 |
direction integer NOT NULL, |
| 276 |
code character varying(20) NOT NULL, |
| 277 |
libelle character varying(100) NOT NULL, |
| 278 |
description text, |
| 279 |
chef character varying(100) NOT NULL, |
| 280 |
om_validite_debut date, |
| 281 |
om_validite_fin date, |
| 282 |
PRIMARY KEY (direction) |
| 283 |
); |
| 284 |
|
| 285 |
INSERT INTO direction VALUES (nextval('direction_seq'::regclass),'ADS', 'Direction ADS', 'Direction des autorisations des droits du sol', 'Mme Dupont', NULL, NULL); |
| 286 |
|
| 287 |
CREATE SEQUENCE division_seq |
| 288 |
START WITH 1 |
| 289 |
INCREMENT BY 1 |
| 290 |
NO MINVALUE |
| 291 |
NO MAXVALUE |
| 292 |
CACHE 1; |
| 293 |
|
| 294 |
CREATE TABLE division ( |
| 295 |
division integer NOT NULL, |
| 296 |
code character varying(20) NOT NULL, |
| 297 |
libelle character varying(100) NOT NULL, |
| 298 |
description text, |
| 299 |
chef character varying(100) NOT NULL, |
| 300 |
direction integer NOT NULL, |
| 301 |
om_validite_debut date, |
| 302 |
om_validite_fin date, |
| 303 |
PRIMARY KEY (division), |
| 304 |
FOREIGN KEY ( direction ) REFERENCES direction ( direction ) |
| 305 |
); |
| 306 |
|
| 307 |
INSERT INTO division VALUES (nextval('division_seq'::regclass),'Defaut', 'Division par defaut', '', 'Mme Dupont',1, NULL, NULL); |
| 308 |
|
| 309 |
CREATE SEQUENCE instructeur_seq |
| 310 |
START WITH 1 |
| 311 |
INCREMENT BY 1 |
| 312 |
NO MINVALUE |
| 313 |
NO MAXVALUE |
| 314 |
CACHE 1; |
| 315 |
|
| 316 |
CREATE TABLE instructeur ( |
| 317 |
instructeur integer NOT NULL, |
| 318 |
nom character varying(100) NOT NULL, |
| 319 |
telephone character varying(14), |
| 320 |
division integer NOT NULL, |
| 321 |
om_utilisateur integer, |
| 322 |
om_validite_debut date, |
| 323 |
om_validite_fin date, |
| 324 |
PRIMARY KEY ( instructeur ), |
| 325 |
FOREIGN KEY ( division ) REFERENCES division ( division ), |
| 326 |
FOREIGN KEY ( om_utilisateur ) REFERENCES om_utilisateur ( om_utilisateur ) |
| 327 |
); |
| 328 |
|
| 329 |
-- Insertion des utilisateurs instructeurs dans la table instructeur |
| 330 |
INSERT INTO instructeur (instructeur,nom,telephone,division,om_utilisateur) |
| 331 |
(SELECT om_utilisateur,nom,telephone,1,om_utilisateur FROM om_utilisateur WHERE instructeur='Oui'); |
| 332 |
-- Mise a jour de la sequence |
| 333 |
SELECT setval('instructeur_seq',(SELECT MAX(instructeur) FROM instructeur)); |
| 334 |
|
| 335 |
|
| 336 |
-- Augment le om_parametre.libelle d'avoir 40 characteres |
| 337 |
ALTER TABLE om_parametre ALTER libelle TYPE character varying(40); |
| 338 |
|
| 339 |
-- Creation du parametre pour afficher ou non la division dans les dossiers |
| 340 |
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'afficher_division','false',1); |
| 341 |
|
| 342 |
-- Ajout des droits sur les tables d'organisation |
| 343 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'direction', '2'); |
| 344 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'division', '2'); |
| 345 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'instructeur', '2'); |
| 346 |
|
| 347 |
-- Modification de la clé étrangère dossier -> om_utilisateur par dossier -> instructeur |
| 348 |
ALTER TABLE dossier DROP CONSTRAINT dossier_instructeur_fkey; |
| 349 |
ALTER TABLE dossier ADD CONSTRAINT |
| 350 |
dossier_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur); |
| 351 |
|
| 352 |
ALTER TABLE dossier ADD COLUMN division integer; |
| 353 |
ALTER TABLE dossier ADD CONSTRAINT |
| 354 |
dossier_division_fkey FOREIGN KEY (division) REFERENCES division(division); |
| 355 |
|
| 356 |
-- Ajout des parametres des liens dans la table om_parametre |
| 357 |
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'services_consultes_lien_interne', '',1); |
| 358 |
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'services_consultes_lien_externe', '',1); |
| 359 |
ALTER TABLE om_parametre ALTER COLUMN valeur TYPE character varying(150); |
| 360 |
|
| 361 |
|
| 362 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_code_barre', '2'); |
| 363 |
|
| 364 |
-- |
| 365 |
-- Ajout des tables arrondissement, quartier et affectation_automatique |
| 366 |
-- |
| 367 |
CREATE TABLE arrondissement ( |
| 368 |
arrondissement integer NOT NULL, |
| 369 |
libelle character varying(3) NOT NULL, |
| 370 |
code_postal character varying(5) NOT NULL |
| 371 |
); |
| 372 |
|
| 373 |
ALTER TABLE ONLY arrondissement |
| 374 |
ADD CONSTRAINT arrondissement_pkey PRIMARY KEY (arrondissement); |
| 375 |
|
| 376 |
CREATE SEQUENCE arrondissement_seq |
| 377 |
INCREMENT 1 |
| 378 |
MINVALUE 1 |
| 379 |
MAXVALUE 9223372036854775807 |
| 380 |
START 1 |
| 381 |
CACHE 1; |
| 382 |
|
| 383 |
CREATE TABLE quartier ( |
| 384 |
quartier integer NOT NULL, |
| 385 |
arrondissement integer NOT NULL, |
| 386 |
code_impots character varying(3) NOT NULL, |
| 387 |
libelle character varying(40) NOT NULL |
| 388 |
); |
| 389 |
|
| 390 |
ALTER TABLE ONLY quartier |
| 391 |
ADD CONSTRAINT quartier_pkey PRIMARY KEY (quartier); |
| 392 |
ALTER TABLE ONLY quartier |
| 393 |
ADD CONSTRAINT quartier_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement); |
| 394 |
|
| 395 |
CREATE SEQUENCE quartier_seq |
| 396 |
INCREMENT 1 |
| 397 |
MINVALUE 1 |
| 398 |
MAXVALUE 9223372036854775807 |
| 399 |
START 1 |
| 400 |
CACHE 1; |
| 401 |
|
| 402 |
CREATE TABLE affectation_automatique ( |
| 403 |
affectation_automatique integer NOT NULL, |
| 404 |
arrondissement integer, |
| 405 |
quartier integer , |
| 406 |
section varchar(2), |
| 407 |
instructeur integer NOT NULL |
| 408 |
); |
| 409 |
|
| 410 |
ALTER TABLE ONLY affectation_automatique |
| 411 |
ADD CONSTRAINT affectation_automatique_pkey PRIMARY KEY (affectation_automatique); |
| 412 |
ALTER TABLE ONLY affectation_automatique |
| 413 |
ADD CONSTRAINT affectation_automatique_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement); |
| 414 |
ALTER TABLE ONLY affectation_automatique |
| 415 |
ADD CONSTRAINT affectation_automatique_quartier_fkey FOREIGN KEY (quartier) REFERENCES quartier(quartier); |
| 416 |
ALTER TABLE ONLY affectation_automatique |
| 417 |
ADD CONSTRAINT affectation_automatique_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur); |
| 418 |
|
| 419 |
CREATE SEQUENCE affectation_automatique_seq |
| 420 |
INCREMENT 1 |
| 421 |
MINVALUE 1 |
| 422 |
MAXVALUE 9223372036854775807 |
| 423 |
START 1 |
| 424 |
CACHE 1; |
| 425 |
|
| 426 |
-- Ajout des droits pour le retour des services |
| 427 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'demande_avis_encours', '2'); |
| 428 |
|
| 429 |
-- Suppression des colonnes inutiles dans la table om_utilisateur |
| 430 |
ALTER TABLE om_utilisateur DROP instructeur; |
| 431 |
|
| 432 |
ALTER TABLE om_utilisateur DROP telephone; |
| 433 |
|
| 434 |
-- Ajout des droits sur lien_service_om_utilisateur |
| 435 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_service_om_utilisateur', '2'); |
| 436 |
-- Ajout des droits pour le retour des services |
| 437 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'demande_avis_passee', '2'); |
| 438 |
|
| 439 |
-- Ajout des droits sur affectation_automatique |
| 440 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'affectation_automatique', '2'); |
| 441 |
|
| 442 |
-- Ajout du droit pour changer l'état (lu/non lu) d'une consultation |
| 443 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_modifier_lu', '2'); |
| 444 |
|
| 445 |
-- Changement de taille du champs parcelle de la table parcelle et terrain |
| 446 |
ALTER TABLE parcelle ALTER COLUMN parcelle TYPE character varying(20); |
| 447 |
ALTER TABLE terrain ALTER COLUMN parcelle TYPE character varying(20); |
| 448 |
|
| 449 |
|
| 450 |
-- |
| 451 |
-- Messages |
| 452 |
-- |
| 453 |
|
| 454 |
-- create sequence for the message ID generation |
| 455 |
CREATE SEQUENCE messages_seq |
| 456 |
START WITH 1 |
| 457 |
INCREMENT BY 1 |
| 458 |
NO MINVALUE |
| 459 |
NO MAXVALUE |
| 460 |
CACHE 1; |
| 461 |
|
| 462 |
-- Create table dossier_message |
| 463 |
CREATE TABLE dossier_message ( |
| 464 |
message integer PRIMARY KEY DEFAULT nextval('messages_seq'), |
| 465 |
dossier character varying(20), |
| 466 |
type character varying(60), |
| 467 |
emetteur character varying(40), |
| 468 |
date_emission TIMESTAMP NOT NULL, |
| 469 |
lu boolean default FALSE, |
| 470 |
contenu text, |
| 471 |
FOREIGN KEY ( dossier ) REFERENCES dossier ( dossier ) |
| 472 |
); |
| 473 |
ALTER SEQUENCE messages_seq OWNED BY dossier_message.message; |
| 474 |
|
| 475 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'menu_suivi', '2'); |
| 476 |
|
| 477 |
-- Droit de l'ajout forcé d'un instructeur |
| 478 |
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'dossier_modifier_instructeur', '2'); |
| 479 |
|
| 480 |
|
| 481 |
--- |
| 482 |
--- TABLE : GENRE |
| 483 |
--- |
| 484 |
CREATE TABLE genre ( |
| 485 |
genre integer, |
| 486 |
code character varying(20), |
| 487 |
libelle character varying(100), |
| 488 |
description text |
| 489 |
); |
| 490 |
ALTER TABLE ONLY genre |
| 491 |
ADD CONSTRAINT genre_pkey PRIMARY KEY (genre); |
| 492 |
CREATE SEQUENCE genre_seq |
| 493 |
INCREMENT 1 |
| 494 |
MINVALUE 1 |
| 495 |
MAXVALUE 9223372036854775807 |
| 496 |
START 1 |
| 497 |
CACHE 1; |
| 498 |
|
| 499 |
|
| 500 |
--- |
| 501 |
--- TABLE : GROUPE |
| 502 |
--- |
| 503 |
CREATE TABLE groupe ( |
| 504 |
groupe integer, |
| 505 |
code character varying(20), |
| 506 |
libelle character varying(100), |
| 507 |
description text, |
| 508 |
genre integer NOT NULL |
| 509 |
); |
| 510 |
ALTER TABLE ONLY groupe |
| 511 |
ADD CONSTRAINT groupe_pkey PRIMARY KEY (groupe); |
| 512 |
ALTER TABLE ONLY groupe |
| 513 |
ADD CONSTRAINT groupe_genre_fkey FOREIGN KEY (genre) REFERENCES genre(genre); |
| 514 |
CREATE SEQUENCE groupe_seq |
| 515 |
INCREMENT 1 |
| 516 |
MINVALUE 1 |
| 517 |
MAXVALUE 9223372036854775807 |
| 518 |
START 1 |
| 519 |
CACHE 1; |
| 520 |
|
| 521 |
|
| 522 |
--- |
| 523 |
--- |
| 524 |
--- |
| 525 |
CREATE TABLE dossier_autorisation_type ( |
| 526 |
dossier_autorisation_type integer, |
| 527 |
code character varying(20), |
| 528 |
libelle character varying(100), |
| 529 |
description text, |
| 530 |
confidentiel boolean default FALSE |
| 531 |
); |
| 532 |
|
| 533 |
ALTER TABLE ONLY dossier_autorisation_type |
| 534 |
ADD CONSTRAINT dossier_autorisation_type_pkey PRIMARY KEY (dossier_autorisation_type); |
| 535 |
|
| 536 |
CREATE SEQUENCE dossier_autorisation_type_seq |
| 537 |
INCREMENT 1 |
| 538 |
MINVALUE 1 |
| 539 |
MAXVALUE 9223372036854775807 |
| 540 |
START 1 |
| 541 |
CACHE 1; |
| 542 |
|
| 543 |
-- |
| 544 |
|
| 545 |
CREATE TABLE dossier_autorisation_type_detaille ( |
| 546 |
dossier_autorisation_type_detaille integer, |
| 547 |
code character varying(20), |
| 548 |
libelle character varying(100), |
| 549 |
description text, |
| 550 |
dossier_autorisation_type integer NOT NULL |
| 551 |
); |
| 552 |
|
| 553 |
ALTER TABLE ONLY dossier_autorisation_type_detaille |
| 554 |
ADD CONSTRAINT dossier_autorisation_type_detaille_pkey PRIMARY KEY (dossier_autorisation_type_detaille); |
| 555 |
ALTER TABLE ONLY dossier_autorisation_type_detaille |
| 556 |
ADD CONSTRAINT dossier_autorisation_type_detaille_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type); |
| 557 |
|
| 558 |
CREATE SEQUENCE dossier_autorisation_type_detaille_seq |
| 559 |
INCREMENT 1 |
| 560 |
MINVALUE 1 |
| 561 |
MAXVALUE 9223372036854775807 |
| 562 |
START 1 |
| 563 |
CACHE 1; |
| 564 |
|
| 565 |
-- Ajout du champs dossier_autorisation_type_detaille dans la table affectation_automatique et de la contrainte FK |
| 566 |
|
| 567 |
ALTER TABLE affectation_automatique ADD COLUMN dossier_autorisation_type_detaille integer; |
| 568 |
|
| 569 |
ALTER TABLE ONLY affectation_automatique |
| 570 |
ADD CONSTRAINT affectation_automatique_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille); |
| 571 |
|
| 572 |
-- |
| 573 |
|
| 574 |
CREATE TABLE dossier_instruction_type ( |
| 575 |
dossier_instruction_type integer, |
| 576 |
code character varying(20), |
| 577 |
libelle character varying(100), |
| 578 |
description text, |
| 579 |
dossier_autorisation_type_detaille integer NOT NULL, |
| 580 |
suffixe boolean default FALSE |
| 581 |
); |
| 582 |
|
| 583 |
ALTER TABLE ONLY dossier_instruction_type |
| 584 |
ADD CONSTRAINT dossier_instruction_type_pkey PRIMARY KEY (dossier_instruction_type); |
| 585 |
ALTER TABLE ONLY dossier_instruction_type |
| 586 |
ADD CONSTRAINT dossier_instruction_type_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille); |
| 587 |
|
| 588 |
CREATE SEQUENCE dossier_instruction_type_seq |
| 589 |
INCREMENT 1 |
| 590 |
MINVALUE 1 |
| 591 |
MAXVALUE 9223372036854775807 |
| 592 |
START 1 |
| 593 |
CACHE 1; |
| 594 |
|
| 595 |
|
| 596 |
-- Ajout de clé étrangère à la table dossier_autorisation_type |
| 597 |
ALTER TABLE dossier_autorisation_type ADD COLUMN groupe integer; |
| 598 |
ALTER TABLE ONLY dossier_autorisation_type |
| 599 |
ADD CONSTRAINT dossier_autorisation_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe); |
| 600 |
|
| 601 |
--Demande nature |
| 602 |
|
| 603 |
CREATE TABLE demande_nature ( |
| 604 |
demande_nature integer, |
| 605 |
code character varying(20), |
| 606 |
libelle character varying(100), |
| 607 |
description text |
| 608 |
); |
| 609 |
|
| 610 |
ALTER TABLE ONLY demande_nature |
| 611 |
ADD CONSTRAINT demande_nature_pkey PRIMARY KEY (demande_nature); |
| 612 |
|
| 613 |
CREATE SEQUENCE demande_nature_seq |
| 614 |
INCREMENT 1 |
| 615 |
MINVALUE 1 |
| 616 |
MAXVALUE 9223372036854775807 |
| 617 |
START 1 |
| 618 |
CACHE 1; |
| 619 |
|
| 620 |
--Demande type |
| 621 |
|
| 622 |
CREATE TABLE demande_type ( |
| 623 |
demande_type integer, |
| 624 |
code character varying(20), |
| 625 |
libelle character varying(100), |
| 626 |
description text, |
| 627 |
demande_nature integer, |
| 628 |
groupe integer, |
| 629 |
dossier_instruction_type integer, |
| 630 |
dossier_autorisation_type_detaille integer, |
| 631 |
contraintes character varying(20), |
| 632 |
etats_dossier_autorisation_autorises character varying(100), |
| 633 |
qualification boolean, |
| 634 |
evenement integer NOT NULL |
| 635 |
); |
| 636 |
|
| 637 |
ALTER TABLE ONLY demande_type |
| 638 |
ADD CONSTRAINT demande_type_pkey PRIMARY KEY (demande_type); |
| 639 |
ALTER TABLE ONLY demande_type |
| 640 |
ADD CONSTRAINT demande_type_demande_nature_fkey FOREIGN KEY (demande_nature) REFERENCES demande_nature(demande_nature); |
| 641 |
ALTER TABLE ONLY demande_type |
| 642 |
ADD CONSTRAINT demande_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe); |
| 643 |
ALTER TABLE ONLY demande_type |
| 644 |
ADD CONSTRAINT demande_type_dossier_instruction_type_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type); |
| 645 |
ALTER TABLE ONLY demande_type |
| 646 |
ADD CONSTRAINT demande_type_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille); |
| 647 |
ALTER TABLE ONLY demande_type |
| 648 |
ADD CONSTRAINT demande_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement); |
| 649 |
|
| 650 |
CREATE SEQUENCE demande_type_seq |
| 651 |
INCREMENT 1 |
| 652 |
MINVALUE 1 |
| 653 |
MAXVALUE 9223372036854775807 |
| 654 |
START 1 |
| 655 |
CACHE 1; |
| 656 |
|
| 657 |
-- |
| 658 |
|
| 659 |
CREATE TABLE lien_evenement_dossier_autorisation_type ( |
| 660 |
lien_evenement_dossier_autorisation_type integer, |
| 661 |
evenement integer, |
| 662 |
dossier_autorisation_type integer |
| 663 |
); |
| 664 |
|
| 665 |
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type |
| 666 |
ADD CONSTRAINT lien_evenement_dossier_autorisation_type_pkey PRIMARY KEY (lien_evenement_dossier_autorisation_type); |
| 667 |
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type |
| 668 |
ADD CONSTRAINT lien_evenement_dossier_autorisation_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement); |
| 669 |
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type |
| 670 |
ADD CONSTRAINT lien_evenement_dossier_autorisation_type_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type); |
| 671 |
|
| 672 |
CREATE SEQUENCE lien_evenement_dossier_autorisation_type_seq |
| 673 |
INCREMENT 1 |
| 674 |
MINVALUE 1 |
| 675 |
MAXVALUE 9223372036854775807 |
| 676 |
START 1 |
| 677 |
CACHE 1; |
| 678 |
|
| 679 |
-- |
| 680 |
|
| 681 |
CREATE TABLE autorite_competente ( |
| 682 |
autorite_competente integer, |
| 683 |
code character varying(20), |
| 684 |
libelle character varying(100), |
| 685 |
description text |
| 686 |
); |
| 687 |
|
| 688 |
ALTER TABLE ONLY autorite_competente |
| 689 |
ADD CONSTRAINT autorite_competente_pkey PRIMARY KEY (autorite_competente); |
| 690 |
|
| 691 |
CREATE SEQUENCE autorite_competente_seq |
| 692 |
INCREMENT 1 |
| 693 |
MINVALUE 1 |
| 694 |
MAXVALUE 9223372036854775807 |
| 695 |
START 1 |
| 696 |
CACHE 1; |
| 697 |
|
| 698 |
-- Ajout de clé étrangère à la table dossier_autorisation_type |
| 699 |
ALTER TABLE dossier ADD COLUMN autorite_competente integer; |
| 700 |
ALTER TABLE ONLY dossier |
| 701 |
ADD CONSTRAINT dossier_autorite_competente_fkey FOREIGN KEY (autorite_competente) REFERENCES autorite_competente(autorite_competente); |
| 702 |
|
| 703 |
-- Donnees des tables |
| 704 |
INSERT INTO dossier_autorisation_type(dossier_autorisation_type, code, libelle) SELECT nextval('dossier_autorisation_type_seq'), nature, libelle FROM nature; |
| 705 |
INSERT INTO dossier_autorisation_type_detaille(dossier_autorisation_type_detaille, code, libelle) SELECT nextval('dossier_autorisation_type_seq'), nature, libelle FROM nature; |
| 706 |
|
| 707 |
-- Table dossier_autorisation |
| 708 |
|
| 709 |
CREATE TABLE dossier_autorisation ( |
| 710 |
dossier_autorisation character varying(20), |
| 711 |
nature character varying(2), |
| 712 |
dossier_autorisation_type_detaille integer, |
| 713 |
exercice integer, |
| 714 |
insee integer, |
| 715 |
terrain_references_cadastrales character varying(100), |
| 716 |
terrain_adresse_voie_numero integer, |
| 717 |
complement character varying(30), |
| 718 |
terrain_adresse_lieu_dit character varying(30), |
| 719 |
terrain_adresse_localite character varying(30), |
| 720 |
terrain_adresse_code_postal character varying(5), |
| 721 |
terrain_adresse_bp character varying(15), |
| 722 |
terrain_adresse_cedex character varying(15), |
| 723 |
terrain_superficie double precision, |
| 724 |
arrondissement integer, |
| 725 |
depot_initial date, |
| 726 |
etat character varying(20), |
| 727 |
erp_numero_batiment integer, |
| 728 |
erp_ouvert boolean, |
| 729 |
erp_date_ouverture date, |
| 730 |
erp_arrete_decision boolean, |
| 731 |
erp_date_arrete_decision date |
| 732 |
); |
| 733 |
|
| 734 |
ALTER TABLE ONLY dossier_autorisation |
| 735 |
ADD CONSTRAINT dossier_autorisation_pkey PRIMARY KEY (dossier_autorisation); |
| 736 |
ALTER TABLE ONLY dossier_autorisation |
| 737 |
ADD CONSTRAINT dossier_autorisation_nature_fkey FOREIGN KEY (nature) REFERENCES nature(nature); |
| 738 |
ALTER TABLE ONLY dossier_autorisation |
| 739 |
ADD CONSTRAINT dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille); |
| 740 |
ALTER TABLE ONLY dossier_autorisation |
| 741 |
ADD CONSTRAINT dossier_autorisation_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement); |
| 742 |
ALTER TABLE ONLY dossier_autorisation |
| 743 |
ADD CONSTRAINT dossier_autorisation_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat); |
| 744 |
|
| 745 |
CREATE SEQUENCE dossier_autorisation_seq |
| 746 |
INCREMENT 1 |
| 747 |
MINVALUE 1 |
| 748 |
MAXVALUE 9223372036854775807 |
| 749 |
START 1 |
| 750 |
CACHE 1; |
| 751 |
|
| 752 |
-- Table Demande |
| 753 |
|
| 754 |
CREATE TABLE demande ( |
| 755 |
demande integer, |
| 756 |
dossier_autorisation_type_detaille integer NOT NULL, |
| 757 |
demande_type integer NOT NULL, |
| 758 |
dossier_instruction character varying(20), |
| 759 |
dossier_autorisation character varying(20), |
| 760 |
date_demande date NOT NULL, |
| 761 |
terrain_references_cadastrales character varying(100), |
| 762 |
terrain_adresse_voie_numero integer, |
| 763 |
complement character varying(30), |
| 764 |
terrain_adresse_lieu_dit character varying(30), |
| 765 |
terrain_adresse_localite character varying(30), |
| 766 |
terrain_adresse_code_postal character varying(5), |
| 767 |
terrain_adresse_bp character varying(15), |
| 768 |
terrain_adresse_cedex character varying(15), |
| 769 |
terrain_superficie double precision, |
| 770 |
nombre_lots integer |
| 771 |
); |
| 772 |
|
| 773 |
ALTER TABLE ONLY demande |
| 774 |
ADD CONSTRAINT demande_pkey PRIMARY KEY (demande); |
| 775 |
ALTER TABLE ONLY demande |
| 776 |
ADD CONSTRAINT demande_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille); |
| 777 |
ALTER TABLE ONLY demande |
| 778 |
ADD CONSTRAINT demande_demande_type_fkey FOREIGN KEY (demande_type) REFERENCES demande_type(demande_type); |
| 779 |
ALTER TABLE ONLY demande |
| 780 |
ADD CONSTRAINT demande_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier); |
| 781 |
ALTER TABLE ONLY demande |
| 782 |
ADD CONSTRAINT demande_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation); |
| 783 |
|
| 784 |
CREATE SEQUENCE demande_seq |
| 785 |
INCREMENT 1 |
| 786 |
MINVALUE 1 |
| 787 |
MAXVALUE 9223372036854775807 |
| 788 |
START 1 |
| 789 |
CACHE 1; |
| 790 |
|
| 791 |
-- Table Demandeur |
| 792 |
|
| 793 |
CREATE TABLE demandeur ( |
| 794 |
demandeur integer, |
| 795 |
type_demandeur character varying(40), |
| 796 |
qualite character varying(40), |
| 797 |
particulier_civilite character varying(10), |
| 798 |
particulier_nom character varying(40), |
| 799 |
particulier_prenom character varying(40), |
| 800 |
particulier_date_naissance date, |
| 801 |
particulier_commune_naissance character varying(30), |
| 802 |
particulier_departement_naissance character varying(80), |
| 803 |
personne_morale_denomination character varying(15), |
| 804 |
personne_morale_raison_sociale character varying(15), |
| 805 |
personne_morale_siret character varying(15), |
| 806 |
personne_morale_categorie_juridique character varying(15), |
| 807 |
personne_morale_civilite character varying(10), |
| 808 |
personne_morale_nom character varying(40), |
| 809 |
personne_morale_prenom character varying(40), |
| 810 |
numero character varying(5), |
| 811 |
voie character varying(40), |
| 812 |
complement character varying(39), |
| 813 |
lieu_dit character varying(39), |
| 814 |
localite character varying(30), |
| 815 |
code_postal character varying(5), |
| 816 |
bp character varying(5), |
| 817 |
cedex character varying(5), |
| 818 |
pays character varying(40), |
| 819 |
division_territoriale character varying(40), |
| 820 |
telephone_fixe character varying(14), |
| 821 |
telephone_mobile character varying(14), |
| 822 |
indicatif character varying(5), |
| 823 |
courriel character varying(40), |
| 824 |
notification boolean, |
| 825 |
frequent boolean |
| 826 |
); |
| 827 |
|
| 828 |
ALTER TABLE ONLY demandeur |
| 829 |
ADD CONSTRAINT demandeur_pkey PRIMARY KEY (demandeur); |
| 830 |
ALTER TABLE ONLY demandeur |
| 831 |
ADD CONSTRAINT demandeur_particulier_civilite_fkey FOREIGN KEY (particulier_civilite) REFERENCES civilite(civilite); |
| 832 |
ALTER TABLE ONLY demandeur |
| 833 |
ADD CONSTRAINT demandeur_personne_morale_civilite_fkey FOREIGN KEY (personne_morale_civilite) REFERENCES civilite(civilite); |
| 834 |
|
| 835 |
CREATE SEQUENCE demandeur_seq |
| 836 |
INCREMENT 1 |
| 837 |
MINVALUE 1 |
| 838 |
MAXVALUE 9223372036854775807 |
| 839 |
START 1 |
| 840 |
CACHE 1; |
| 841 |
|
| 842 |
-- Table Lien demande demandeur |
| 843 |
|
| 844 |
CREATE TABLE lien_demande_demandeur ( |
| 845 |
lien_demande_demandeur integer, |
| 846 |
petitionnaire_principal boolean, |
| 847 |
demande integer, |
| 848 |
demandeur integer |
| 849 |
); |
| 850 |
|
| 851 |
ALTER TABLE ONLY lien_demande_demandeur |
| 852 |
ADD CONSTRAINT lien_demande_demandeur_pkey PRIMARY KEY (lien_demande_demandeur); |
| 853 |
ALTER TABLE ONLY lien_demande_demandeur |
| 854 |
ADD CONSTRAINT lien_demande_demandeur_demande_fkey FOREIGN KEY (demande) REFERENCES demande(demande); |
| 855 |
ALTER TABLE ONLY lien_demande_demandeur |
| 856 |
ADD CONSTRAINT lien_demande_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur); |
| 857 |
|
| 858 |
CREATE SEQUENCE lien_demande_demandeur_seq |
| 859 |
INCREMENT 1 |
| 860 |
MINVALUE 1 |
| 861 |
MAXVALUE 9223372036854775807 |
| 862 |
START 1 |
| 863 |
CACHE 1; |
| 864 |
|
| 865 |
-- Table lot |
| 866 |
|
| 867 |
CREATE TABLE lot ( |
| 868 |
lot integer, |
| 869 |
dossier_instruction character varying(12) |
| 870 |
); |
| 871 |
|
| 872 |
ALTER TABLE ONLY lot |
| 873 |
ADD CONSTRAINT lot_pkey PRIMARY KEY (lot); |
| 874 |
ALTER TABLE ONLY lot |
| 875 |
ADD CONSTRAINT lot_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier); |
| 876 |
|
| 877 |
CREATE SEQUENCE lot_seq |
| 878 |
INCREMENT 1 |
| 879 |
MINVALUE 1 |
| 880 |
MAXVALUE 9223372036854775807 |
| 881 |
START 1 |
| 882 |
CACHE 1; |
| 883 |
|
| 884 |
-- Table lien_lot_demandeur |
| 885 |
|
| 886 |
CREATE TABLE lien_lot_demandeur ( |
| 887 |
lien_lot_demandeur integer, |
| 888 |
lot integer, |
| 889 |
demandeur integer |
| 890 |
); |
| 891 |
|
| 892 |
ALTER TABLE ONLY lien_lot_demandeur |
| 893 |
ADD CONSTRAINT lien_lot_demandeur_pkey PRIMARY KEY (lien_lot_demandeur); |
| 894 |
ALTER TABLE ONLY lien_lot_demandeur |
| 895 |
ADD CONSTRAINT lien_lot_demandeur_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot); |
| 896 |
ALTER TABLE ONLY lien_lot_demandeur |
| 897 |
ADD CONSTRAINT lien_lot_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur); |
| 898 |
|
| 899 |
CREATE SEQUENCE lien_lot_demandeur_seq |
| 900 |
INCREMENT 1 |
| 901 |
MINVALUE 1 |
| 902 |
MAXVALUE 9223372036854775807 |
| 903 |
START 1 |
| 904 |
CACHE 1; |
| 905 |
|
| 906 |
|
| 907 |
--- |
| 908 |
--- Nouvelle gestion des tableaux de bord |
| 909 |
--- |
| 910 |
|
| 911 |
CREATE TABLE om_dashboard ( |
| 912 |
om_dashboard integer NOT NULL, |
| 913 |
om_profil integer NOT NULL, |
| 914 |
bloc character varying(10) NOT NULL, |
| 915 |
position integer, |
| 916 |
om_widget integer NOT NULL |
| 917 |
); |
| 918 |
|
| 919 |
ALTER TABLE ONLY om_dashboard |
| 920 |
ADD CONSTRAINT om_dashboard_pkey PRIMARY KEY (om_dashboard); |
| 921 |
ALTER TABLE ONLY om_dashboard |
| 922 |
ADD CONSTRAINT om_dashboard_om_profil_fkey FOREIGN KEY (om_profil) REFERENCES om_profil(om_profil); |
| 923 |
ALTER TABLE ONLY om_dashboard |
| 924 |
ADD CONSTRAINT om_dashboard_om_widget_fkey FOREIGN KEY (om_widget) REFERENCES om_widget(om_widget); |
| 925 |
|
| 926 |
CREATE SEQUENCE om_dashboard_seq |
| 927 |
START WITH 1 |
| 928 |
INCREMENT BY 1 |
| 929 |
NO MINVALUE |
| 930 |
NO MAXVALUE |
| 931 |
CACHE 1; |
| 932 |
|
| 933 |
SELECT pg_catalog.setval('om_dashboard_seq', 1, false); |
| 934 |
|
| 935 |
ALTER TABLE om_widget |
| 936 |
DROP CONSTRAINT om_widget_om_profil_fkey; |
| 937 |
|
| 938 |
ALTER TABlE om_widget DROP COLUMN om_profil; |
| 939 |
|
| 940 |
ALTER TABLE om_widget ADD COLUMN "type" character varying(40) NOT NULL DEFAULT 'web'::character varying; |
| 941 |
ALTER TABLE om_widget ALTER COLUMN "lien" SET DEFAULT ''::character varying; |
| 942 |
ALTER TABLE om_widget ALTER COLUMN "texte" SET DEFAULT ''::text; |
| 943 |
|
| 944 |
-- Modification de la table civilite |
| 945 |
ALTER TABLE dossier DROP CONSTRAINT dossier_delegataire_civilite_fkey; |
| 946 |
ALTER TABLE dossier DROP CONSTRAINT dossier_demandeur_civilite_fkey; |
| 947 |
ALTER TABLE proprietaire DROP CONSTRAINT proprietaire_civilite_fkey; |
| 948 |
ALTER TABLE demandeur DROP CONSTRAINT demandeur_particulier_civilite_fkey; |
| 949 |
ALTER TABLE demandeur DROP CONSTRAINT demandeur_personne_morale_civilite_fkey; |
| 950 |
ALTER TABLE civilite DROP CONSTRAINT civilite_pkey; |
| 951 |
|
| 952 |
ALTER TABLE dossier RENAME COLUMN delegataire_civilite TO delegataire_civilite_old; |
| 953 |
ALTER TABLE dossier RENAME COLUMN demandeur_civilite TO demandeur_civilite_old; |
| 954 |
ALTER TABLE proprietaire RENAME COLUMN civilite TO civilite_old; |
| 955 |
ALTER TABLE demandeur RENAME COLUMN particulier_civilite TO particulier_civilite_old; |
| 956 |
ALTER TABLE demandeur RENAME COLUMN personne_morale_civilite TO personne_morale_civilite_old; |
| 957 |
ALTER TABLE civilite RENAME COLUMN civilite TO civilite_old; |
| 958 |
|
| 959 |
CREATE SEQUENCE civilite_seq |
| 960 |
START WITH 1 |
| 961 |
INCREMENT BY 1 |
| 962 |
NO MAXVALUE |
| 963 |
NO MINVALUE |
| 964 |
CACHE 1; |
| 965 |
|
| 966 |
ALTER TABLE civilite ADD COLUMN civilite integer NOT NULL DEFAULT nextval('civilite_seq'::regclass); |
| 967 |
ALTER TABLE dossier ADD COLUMN delegataire_civilite integer; |
| 968 |
ALTER TABLE dossier ADD COLUMN demandeur_civilite integer; |
| 969 |
ALTER TABLE proprietaire ADD COLUMN civilite integer; |
| 970 |
ALTER TABLE demandeur ADD COLUMN particulier_civilite integer; |
| 971 |
ALTER TABLE demandeur ADD COLUMN personne_morale_civilite integer; |
| 972 |
|
| 973 |
UPDATE dossier SET delegataire_civilite=(select civilite.civilite from civilite where delegataire_civilite_old=civilite.civilite_old); |
| 974 |
UPDATE dossier SET demandeur_civilite=(select civilite.civilite from civilite where demandeur_civilite_old=civilite.civilite_old); |
| 975 |
UPDATE proprietaire SET civilite=(select civilite.civilite from civilite where civilite_old=civilite.civilite_old); |
| 976 |
UPDATE demandeur SET particulier_civilite=(select civilite.civilite from civilite where particulier_civilite_old=civilite.civilite_old); |
| 977 |
UPDATE demandeur SET personne_morale_civilite=(select civilite.civilite from civilite where personne_morale_civilite_old=civilite.civilite_old); |
| 978 |
|
| 979 |
ALTER TABLE civilite ADD COLUMN libelle character varying(100); |
| 980 |
ALTER TABLE civilite ADD COLUMN om_validite_debut date; |
| 981 |
ALTER TABLE civilite ADD COLUMN om_validite_fin date; |
| 982 |
|
| 983 |
|
| 984 |
ALTER TABLE civilite RENAME COLUMN civilite_old TO code; |
| 985 |
ALTER TABLE dossier DROP COLUMN delegataire_civilite_old; |
| 986 |
ALTER TABLE dossier DROP COLUMN demandeur_civilite_old; |
| 987 |
ALTER TABLE proprietaire DROP COLUMN civilite_old; |
| 988 |
ALTER TABLE demandeur DROP COLUMN particulier_civilite_old; |
| 989 |
ALTER TABLE demandeur DROP COLUMN personne_morale_civilite_old; |
| 990 |
|
| 991 |
ALTER TABLE ONLY civilite |
| 992 |
ADD CONSTRAINT civilite_pkey PRIMARY KEY (civilite); |
| 993 |
|
| 994 |
ALTER TABLE ONLY dossier |
| 995 |
ADD CONSTRAINT dossier_delegataire_civilite_fkey FOREIGN KEY (delegataire_civilite) REFERENCES civilite(civilite); |
| 996 |
ALTER TABLE ONLY dossier |
| 997 |
ADD CONSTRAINT dossier_demandeur_civilite_fkey FOREIGN KEY (demandeur_civilite) REFERENCES civilite(civilite); |
| 998 |
ALTER TABLE ONLY proprietaire |
| 999 |
ADD CONSTRAINT proprietaire_civilite_fkey FOREIGN KEY (civilite) REFERENCES civilite(civilite); |
| 1000 |
ALTER TABLE ONLY demandeur |
| 1001 |
ADD CONSTRAINT demandeur_particulier_civilite_fkey FOREIGN KEY (particulier_civilite) REFERENCES civilite(civilite); |
| 1002 |
ALTER TABLE ONLY demandeur |
| 1003 |
ADD CONSTRAINT demandeur_personne_morale_civilite_fkey FOREIGN KEY (personne_morale_civilite) REFERENCES civilite(civilite); |
| 1004 |
|
| 1005 |
ALTER SEQUENCE civilite_seq OWNED BY civilite.civilite; |
| 1006 |
|
| 1007 |
ALTER TABLE civilite ALTER COLUMN civilite DROP DEFAULT; |
| 1008 |
|
| 1009 |
UPDATE civilite SET libelle='Monsieur Madame' WHERE civilite = 1 ; |
| 1010 |
UPDATE civilite SET libelle='Mademoiselle' WHERE civilite = 2 ; |
| 1011 |
UPDATE civilite SET libelle='Madame' WHERE civilite = 3 ; |
| 1012 |
UPDATE civilite SET libelle='Monsieur' WHERE civilite = 4 ; |
| 1013 |
|
| 1014 |
-- Table Lien dossier_autorisation demandeur |
| 1015 |
|
| 1016 |
CREATE TABLE lien_dossier_autorisation_demandeur ( |
| 1017 |
lien_dossier_autorisation_demandeur integer, |
| 1018 |
petitionnaire_principal boolean, |
| 1019 |
dossier_autorisation character varying(20), |
| 1020 |
demandeur integer |
| 1021 |
); |
| 1022 |
|
| 1023 |
ALTER TABLE ONLY lien_dossier_autorisation_demandeur |
| 1024 |
ADD CONSTRAINT lien_dossier_autorisation_demandeur_pkey PRIMARY KEY (lien_dossier_autorisation_demandeur); |
| 1025 |
ALTER TABLE ONLY lien_dossier_autorisation_demandeur |
| 1026 |
ADD CONSTRAINT lien_dossier_autorisation_demandeur_demande_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation); |
| 1027 |
ALTER TABLE ONLY lien_dossier_autorisation_demandeur |
| 1028 |
ADD CONSTRAINT lien_dossier_autorisation_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur); |
| 1029 |
|
| 1030 |
CREATE SEQUENCE lien_dossier_autorisation_demandeur_seq |
| 1031 |
INCREMENT 1 |
| 1032 |
MINVALUE 1 |
| 1033 |
MAXVALUE 9223372036854775807 |
| 1034 |
START 1 |
| 1035 |
CACHE 1; |
| 1036 |
|
| 1037 |
-- Ajout du champ à qualifier -- |
| 1038 |
|
| 1039 |
ALTER TABLE dossier ADD COLUMN a_qualifier boolean; |
| 1040 |
|
| 1041 |
-- Ajout d'un champ à la table état -- |
| 1042 |
ALTER TABLE etat ADD COLUMN statut character varying(60); |
| 1043 |
UPDATE etat set statut='encours' |
| 1044 |
where etat = 'notifier' or etat = 'majorer' or etat = 'initialiser'; |
| 1045 |
UPDATE etat set statut='cloture' |
| 1046 |
where etat = 'accepter' or etat = 'cloturer' or etat = 'rejeter' |
| 1047 |
or etat = 'executer' or etat = 'terminer'; |
| 1048 |
|
| 1049 |
--Changement des civilités dans les états et sous-états |
| 1050 |
UPDATE om_sousetat SET om_sql='SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier, ''Depot ''||to_char(date_depot,''DD/MM/YYYY'')||'' Notifie le ''||COALESCE(to_char(date_complet,''DD/MM/YYYY''),''inconu'') as date_dp_n, TRIM(CONCAT(civilite.libelle, '' '', demandeur_nom, '' '',demandeur_adresse,'' '',demandeur_cp,'' '',demandeur_ville,'' Parcelle '',parcelle)) as nom_adresse_demandeur, terrain_numero||'' ''||terrain_numero_complement||'' ''||terrain_adresse||'' ''||terrain_adresse_complement||'' ''||terrain_cp||'' ''||terrain_ville||'' ''||travaux.libelle as adresse_terrain_travaux, ''shon ''||shon||'' shob ''||shob as SN_SB, terrain_surface as superficie, logement_nombre as nbr_logement, COALESCE(avis_decision.libelle,''inconu'') as avis_decision, ''Decision''||COALESCE(to_char(date_decision,''DD/MM/YYYY''),''inconu'')||'' Limite ''||COALESCE(to_char(date_limite,''DD/MM/YYYY''),''inconu'') as date_dc_l, 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 left join civilite on demandeur_civilite = civilite.civilite where dossier.nature=''&nature'' AND (date_decision>=''&datedebut'' AND date_decision<=''&datefin'') ORDER BY dossier' WHERE om_sousetat = 10; |
| 1051 |
|
| 1052 |
|
| 1053 |
UPDATE om_etat SET om_sql='select dossier,nature.libelle as nature, civilite.libelle AS demandeur_civilite,demandeur_nom,demandeur_societe, demandeur_adresse,demandeur_cp,demandeur_ville, terrain_numero, terrain_adresse, terrain_cp, terrain_ville, terrain_surface, hauteur, shon, shob, batiment_nombre, logement_nombre, delai, to_char(date_depot,''DD/MM/YYYY'') as date_depot, to_char(date_complet,''DD/MM/YYYY'') as date_complet, to_char(date_notification_delai,''DD/MM/YYYY'') as date_notification_delai, to_char(date_limite,''DD/MM/YYYY'') as date_limite, travaux.libelle as travaux from dossier inner join nature on nature.nature = dossier.nature left join travaux on dossier.travaux=travaux.travaux left join civilite on demandeur_civilite=civilite.civilite where dossier = ''&idx''' WHERE om_etat = 7; |
| 1054 |
|
| 1055 |
UPDATE om_etat SET om_sql='select service.libelle as service_libelle, service.adresse as service_adresse, service.cp as service_cp, service.ville as service_ville, consultation.dossier as dossier, travaux.libelle as libelle_travaux, civilite.libelle as demandeur_civilite,date_demande, demandeur_nom, demandeur_adresse, demandeur_cp, demandeur_ville, terrain_adresse from consultation inner join service on service.service = consultation.service inner join dossier on dossier.dossier =consultation.dossier left join travaux on travaux.travaux = dossier.travaux left join civilite on demandeur_civilite = civilite.civilite where consultation= &idx' WHERE om_etat = 6; |
| 1056 |
|
| 1057 |
UPDATE om_etat SET om_sql='select service.libelle as service, service.adresse, service.cp, service.ville, dossier.dossier,nature.libelle as nature, civilite.libelle as demandeur_civilite, demandeur_nom, emandeur_societe, demandeur_adresse, demandeur_cp, demandeur_ville, terrain_numero, terrain_adresse, terrain_cp, terrain_ville, terrain_surface, hauteur, shon, shob, batiment_nombre, logement_nombre, to_char(date_depot,''DD/MM/YYYY'') as date_depot, to_char(date_rejet,''DD/MM/YYYY'') as date_rejet, travaux.libelle as travaux, to_char(date_envoi,''DD/MM/YYYY'') as date_envoi from consultation inner join dossier on dossier.dossier=consultation.dossier inner join service on service.service=consultation.service inner join nature on nature.nature = dossier.nature left join travaux on dossier.travaux=travaux.travaux left join civilite on demandeur_civilite = civilite.civilite where consultation = &idx' WHERE om_etat = 4; |
| 1058 |
|
| 1059 |
UPDATE om_sousetat SET om_sql='SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier,to_char(date_depot,''DD/MM/YYYY'') as date_depot, TRIM(CONCAT( civilite.libelle,'' '', demandeur_nom)) as nom_demandeur, terrain_numero||'' ''||terrain_numero_complement||'' ''||terrain_adresse||'' ''||terrain_adresse_complement||'' ''||terrain_cp||'' ''||terrain_ville as adresse_terrain, shon, logement_nombre as nb_logt from dossier left join civilite on demandeur_civilite = civilite.civilite where dossier.nature=''&nature'' AND (date_depot>=''&datedebut'' AND date_depot<=''&datefin'') ORDER BY dossier' WHERE om_sousetat = 9; |
| 1060 |
|
| 1061 |
UPDATE om_sousetat SET om_sql='SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier, ''Depot ''||to_char(date_depot,''DD/MM/YYYY'')||'' Notifie le ''||COALESCE(to_char(date_complet,''DD/MM/YYYY''),''inconu'') as date_dp_n, TRIM(CONCAT(civilite.libelle,'' '',demandeur_nom,'' '',demandeur_adresse,'' '',demandeur_cp,'' '',demandeur_ville,'' Parcelle '',parcelle)) as nom_adresse_demandeur, terrain_numero||'' ''||terrain_numero_complement||'' ''||terrain_adresse||'' ''||terrain_adresse_complement||'' ''||terrain_cp||'' ''||terrain_ville||'' ''||travaux.libelle as adresse_terrain_travaux, ''shon ''||shon||'' shob ''||shob as SN_SB, terrain_surface as superficie, logement_nombre as nbr_logement, COALESCE(avis_decision.libelle,''inconu'') as avis_decision, ''Decision''||COALESCE(to_char(date_decision,''DD/MM/YYYY''),''inconu'')||'' Limite ''||COALESCE(to_char(date_limite,''DD/MM/YYYY''),''inconu'') as date_dc_l, delai||'' mois'' as delai, '' '' as date_affichage_decision, '' '' as DOC_DAT_Conformite from dossier left join civilite on demandeur_civilite = civilite.civilite 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; |
| 1062 |
|
| 1063 |
-- Ajout d'un nouvel état et d'un nouvel sous-état pour les affichages des dossiers / ! \ GROUPE a rajouter |
| 1064 |
INSERT INTO om_etat VALUES (nextval('om_etat_seq'), 1, 'dossier_m', 'import du 25/11/2012', true, 'L', 'A4', 'helvetica', 'I', 8, 'pixel.png', 58, 7, 'Registre des dossiers en cours', 5, 7, 100, 10, 'helvetica', 'B', 15, '1', 'L', ' |
| 1065 |
No commune : &departement &commune &ville', 120, 7, 195, 5, 'helvetica', '', 10, '0', 'J', 'select nom from om_utilisateur', 'dossier_m', 'helvetica', 8, 5, 5, '0-0-0'); |
| 1066 |
|
| 1067 |
INSERT INTO om_sousetat VALUES (nextval('om_sousetat_seq'), 1, 'dossier_m', 'import du 26/11/2012', true, 'Edition du &aujourdhui', 8, 'helvetica', '', 9, '0', 'L', '0', '243-246-246', '0-0-0', 5, 0, '1', '1', '0|0|0|0|0|0|90|90|0|0|0|90|90', 37, 'TLB|LTB|LTBR|TLB|LTB|LTBR|TLB|LTB|LTBR|LTBR|TLB|LTB|LTBR|LTBR|LTBR', 'C|C|C|L|L|R|R|R|L|R|R|R|R', '145-184-189', '0-0-0', 280, '1', 7, '0-0-0', '243-246-246', '255-255-255', '1', 9, '20|20|20|50|57|15|15|10|20|20|11|21|21', 'LTBR|LTBR|LTBRL|LTBR|LTBR|LTBRL|LTBR|LTBR|LTBR|LTBRL|LTBR|LTBR|LTBR', 'LTBR|LTBR|LTBR|LTBR|LTBR|LTBRL|LTBR|LTBR|LTBR|LTBRL|LTBR|LTBR|LTBR', 'C|C|C|L|L|C|R|R|L|C|R|C|C', '1', 10, 15, '196-213-215', 'TBL|TBL|TBL|TBLR|TBL|TBL|TBLR|TBLR|TBL|TBL|TBLR|TBLR|TBLR', 'L|L|L|C|L|L|C|C|L|L|C|C|C', '1', 10, 5, '212-219-220', 'BTL|BTL|BTL|BTLR|BTL|BTL|BTLR|TBLR|BTL|BTL|BTLR|TBLR|TBLR', 'L|L|L|C|L|L|C|C|L|L|C|C|C', '1', 10, 15, '255-255-255', 'TBL|TBL|TBL|TBLR|TBL|TBL|TBLR|TBLR|TBL|TBL|TBLR|TBLR|TBLR', 'L|L|L|R|L|L|R|R|L|L|R|R|R', '999|999|999|999|999|999|999|999|999|999|999|999|999', '0|0|0|0|0|0|0|0|0|0|0|0|0', '0|0|0|0|0|0|0|0|0|0|0|0|0', '0|0|0|0|0|0|0|0|0|0|0|0|0', 'SELECT ''Commune &commune''||'' ''||''Dossier ''||dossier as dossier, ''Depot ''||to_char(date_depot,''DD/MM/YYYY'')||'' Notifie le ''||COALESCE(to_char(date_complet,''DD/MM/YYYY''),''inconu'') as date_dp_n, arrondissement.libelle as arrondissement, TRIM(CONCAT(civilite.libelle,'' '',demandeur_nom,'' '',demandeur_adresse,'' '',demandeur_cp,'' '',demandeur_ville,'' Parcelle '',parcelle)) as nom_adresse_demandeur, CONCAT(terrain_numero, '' '', terrain_numero_complement, '' '', terrain_adresse, '' '', terrain_adresse_complement, '' '', terrain_cp, '' '', terrain_ville, '' '', travaux.libelle) as adresse_terrain_travaux, CONCAT(''shon '', shon, '' shob '', shob) as SN_SB, terrain_surface as superficie, logement_nombre as nbr_logement, COALESCE(avis_decision.libelle,''inconu'') as avis_decision, ''Decision''||COALESCE(to_char(date_decision,''DD/MM/YYYY''),''inconu'')||'' Limite ''||COALESCE(to_char(date_limite,''DD/MM/YYYY''),''inconu'') as date_dc_l, delai||'' mois'' as delai, '' '' as date_affichage_decision, '' '' as DOC_DAT_Conformite from dossier left join civilite on demandeur_civilite = civilite.civilite left join travaux on dossier.travaux=travaux.travaux left join avis_decision on dossier.avis_decision=avis_decision.avis_decision left join arrondissement on terrain_cp = arrondissement.code_postal where (select e.statut from etat e where e.etat = dossier.etat ) = ''encours'' ORDER BY arrondissement.libelle'); |
| 1068 |
|
| 1069 |
-- Ajout d'un nouvel évènement "affichage_obligatoire" |
| 1070 |
INSERT INTO evenement VALUES (89, 'affichage_obligatoire', 'T', 'divers', NULL, 0, 'Non', 0, 'attestation_affichage', ' ', NULL); |
| 1071 |
-- Ajout d'un nouveau paramètre pour l'évènement "affichage_obligatoire" |
| 1072 |
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'), 'affichage_obligatoire', '89', 1); |
| 1073 |
|
| 1074 |
-- Ajout des colonnes manquant dans dossier por rapport à la table demande |
| 1075 |
ALTER TABLE dossier ADD COLUMN terrain_references_cadastrales character varying(100); |
| 1076 |
ALTER TABLE dossier ADD COLUMN terrain_adresse_voie_numero integer; |
| 1077 |
ALTER TABLE dossier ADD COLUMN complement character varying(30); |
| 1078 |
ALTER TABLE dossier ADD COLUMN terrain_adresse_lieu_dit character varying(30); |
| 1079 |
ALTER TABLE dossier ADD COLUMN terrain_adresse_localite character varying(30); |
| 1080 |
ALTER TABLE dossier ADD COLUMN terrain_adresse_code_postal character varying(5); |
| 1081 |
ALTER TABLE dossier ADD COLUMN terrain_adresse_bp character varying(15); |
| 1082 |
ALTER TABLE dossier ADD COLUMN terrain_adresse_cedex character varying(15); |
| 1083 |
ALTER TABLE dossier ADD COLUMN terrain_superficie double precision; |
| 1084 |
|
| 1085 |
-- Ajout d'une nouvelle lettre type |
| 1086 |
INSERT INTO om_lettretype VALUES (nextval('om_lettretype_seq'), 1, 'attestation_affichage', 'attestion d''affichage obligatoire', true, 'P', 'A4', 'logolettretype.png', 8, 9, 'Attestation d''affichage', 70, 20, 130, 5, 'arial', '', 8, '1', 'L', 'Bonjour', 30, 102, 160, 4, 'arial', '', 8, '0', 'J', 'select nom from &DB_PREFIXEom_utilisateur'); |
| 1087 |
|
| 1088 |
-- Table Lien demande demandeur |
| 1089 |
|
| 1090 |
CREATE TABLE lien_dossier_demandeur ( |
| 1091 |
lien_dossier_demandeur integer, |
| 1092 |
petitionnaire_principal boolean, |
| 1093 |
dossier character varying(20), |
| 1094 |
demandeur integer |
| 1095 |
); |
| 1096 |
|
| 1097 |
ALTER TABLE ONLY lien_dossier_demandeur |
| 1098 |
ADD CONSTRAINT lien_dossier_demandeur_pkey PRIMARY KEY (lien_dossier_demandeur); |
| 1099 |
ALTER TABLE ONLY lien_dossier_demandeur |
| 1100 |
ADD CONSTRAINT lien_dossier_demandeur_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier); |
| 1101 |
ALTER TABLE ONLY lien_dossier_demandeur |
| 1102 |
ADD CONSTRAINT lien_dossier_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur); |
| 1103 |
|
| 1104 |
CREATE SEQUENCE lien_dossier_demandeur_seq |
| 1105 |
INCREMENT 1 |
| 1106 |
MINVALUE 1 |
| 1107 |
MAXVALUE 9223372036854775807 |
| 1108 |
START 1 |
| 1109 |
CACHE 1; |
| 1110 |
|
| 1111 |
ALTER TABLE dossier ALTER nature TYPE character varying(3); |
| 1112 |
ALTER TABLE nature ALTER nature TYPE character varying(3); |
| 1113 |
ALTER TABLE nature ALTER libelle TYPE character varying(150); |
| 1114 |
ALTER TABLE dossier ALTER nature DROP NOT NULL; |
| 1115 |
|
| 1116 |
INSERT INTO nature VALUES ('PCI', 'Permis de construire pour une maison individuelle et / ou ses annexes'); |
| 1117 |
INSERT INTO nature VALUES ('PCA', 'Permis de construire comprenant ou non des démolitions'); |
| 1118 |
INSERT INTO nature VALUES ('AZ', 'Demande d''autorisation spéciale de travaux dans le périmètre d''une AVAP'); |
| 1119 |
INSERT INTO nature VALUES ('DAT', 'Demande d''autorisation de construire, d''aménager ou de modifier un ERP'); |
| 1120 |
|
| 1121 |
ALTER TABLE dossier ALTER COLUMN dossier TYPE character varying(20); |
| 1122 |
|
| 1123 |
ALTER TABLE dossier ADD column dossier_autorisation character varying(20) NOT NULL; |
| 1124 |
ALTER TABLE ONLY dossier |
| 1125 |
ADD CONSTRAINT dossier_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation); |
| 1126 |
|
| 1127 |
ALTER TABLE instruction ALTER COLUMN dossier TYPE character varying(20); |
| 1128 |
|
| 1129 |
ALTER TABLE instruction ALTER COLUMN dossier TYPE character varying(20); |
| 1130 |
ALTER TABLE consultation ALTER COLUMN dossier TYPE character varying(20); |
| 1131 |
ALTER TABLE terrain ALTER COLUMN dossier TYPE character varying(20); |
| 1132 |
ALTER TABLE blocnote ALTER COLUMN dossier TYPE character varying(20); |
| 1133 |
ALTER TABLE destination_shon ALTER COLUMN dossier TYPE character varying(20); |
| 1134 |
ALTER TABLE statistique ALTER COLUMN dossier TYPE character varying(20); |
| 1135 |
|
| 1136 |
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'), 'option_ERP', 'true', 1); |
| 1137 |
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'), 'option_GED', 'false', 1); |
Parent Directory
| 
Revision Log