data/pgsql/v3.2.0-dev.sql

INSERT INTO nature VALUES ('DD', 'Dépôt de dossier DAT');
INSERT INTO nature VALUES ('DO', 'Demande d''ouverture ERP DAT');
INSERT INTO nature VALUES ('AD', 'Annulation de la demande');
INSERT INTO nature VALUES ('DA', 'Demande PC ratachée DAACT');
ALTER TABLE dossier ADD COLUMN erp boolean;
UPDATE action SET regle_avis = 'avis_decision' WHERE libelle = 'accepter un dossier';

--
-- Ajout de table: avis_consultation_tmp;
--


CREATE TABLE avis_consultation_tmp (
    avis character varying(2) NOT NULL,
    libelle character varying(30) NOT NULL,
    typeavis character(1) DEFAULT ''::bpchar NOT NULL,
    sitadel character(1) DEFAULT ''::bpchar NOT NULL,
    sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL
);


--
-- Data for Name: avis_consultation_tmp;
--

INSERT INTO avis_consultation_tmp VALUES ('D', 'Defavorable', 'D', '6', ' ');
INSERT INTO avis_consultation_tmp VALUES ('F', 'Favorable', 'F', '4', ' ');
INSERT INTO avis_consultation_tmp VALUES ('F1', 'Favorable avec Reserve', 'F', '4', ' ');
INSERT INTO avis_consultation_tmp VALUES ('T', 'Tacite', 'F', '2', ' ');
INSERT INTO avis_consultation_tmp VALUES ('A', 'Autre', ' ', '7', ' ');


--
-- Ajout de la table 'service_categorie' 
--
CREATE TABLE service_categorie (
  service_categorie integer,
  libelle varchar(70)  NOT NULL default ''
);

ALTER TABLE ONLY service_categorie
    ADD CONSTRAINT service_categorie_pkey PRIMARY KEY (service_categorie);

CREATE SEQUENCE service_categorie_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--
-- Modification de la table 'service' et des clés étangères
--
ALTER TABLE consultation DROP CONSTRAINT consultation_service_fkey;
ALTER TABLE service DROP CONSTRAINT service_pkey;

ALTER TABLE consultation RENAME COLUMN service TO service_old;
ALTER TABLE service RENAME COLUMN service TO service_old;

CREATE SEQUENCE service_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE service ADD COLUMN service integer NOT NULL DEFAULT nextval('service_seq'::regclass);
ALTER TABLE consultation ADD COLUMN service integer;

UPDATE consultation SET service=(select service.service from service where service_old=service.service_old);

ALTER TABLE service ADD COLUMN consultation_papier boolean;
ALTER TABLE service ADD COLUMN notification_email boolean;
ALTER TABLE service ADD COLUMN om_validite_debut date;
ALTER TABLE service ADD COLUMN om_validite_fin date;
ALTER TABLE service ADD COLUMN type_consultation varchar(70) NOT NULL DEFAULT 'avec_avis_attendu';

ALTER TABLE service RENAME COLUMN service_old TO abrege;
ALTER TABLE consultation DROP COLUMN service_old;

ALTER TABLE ONLY service
    ADD CONSTRAINT service_pkey PRIMARY KEY (service);

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_service_fkey FOREIGN KEY (service) REFERENCES service(service);

ALTER SEQUENCE service_seq OWNED BY service.service;

ALTER TABLE service ALTER COLUMN service DROP DEFAULT;

--
-- Ajout de la table 'lien_service_service_categorie'
--

CREATE TABLE lien_service_service_categorie (
  lien_service_service_categorie integer,
  service_categorie integer,
  service integer
);

ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_pkey PRIMARY KEY (lien_service_service_categorie);
ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_service_categorie_fkey FOREIGN KEY (service_categorie) REFERENCES service_categorie(service_categorie);
ALTER TABLE ONLY lien_service_service_categorie
    ADD CONSTRAINT lien_service_service_categorie_service_fkey FOREIGN KEY (service) REFERENCES service(service);

CREATE SEQUENCE lien_service_service_categorie_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--
-- Ajout de la table 'lien_service_utilisateur'
--

CREATE TABLE lien_service_om_utilisateur (
  lien_service_om_utilisateur integer,
  om_utilisateur bigint,
  service integer
);

ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_pkey PRIMARY KEY (lien_service_om_utilisateur);
ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_om_utilisateur_fkey FOREIGN KEY (om_utilisateur) REFERENCES om_utilisateur(om_utilisateur);
ALTER TABLE ONLY lien_service_om_utilisateur
    ADD CONSTRAINT lien_service_om_utilisateur_service_fkey FOREIGN KEY (service) REFERENCES service(service);

CREATE SEQUENCE lien_service_om_utilisateur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--
-- Ajout des tables 'avis_consultation' et 'avis_decision'
--

CREATE TABLE avis_decision (
  avis_old character varying(2) NOT NULL,
  libelle character varying(30) NOT NULL,
  typeavis character(1) DEFAULT ''::bpchar NOT NULL,
  sitadel character(1) DEFAULT ''::bpchar NOT NULL,
  sitadel_motif character(1) DEFAULT ''::bpchar NOT NULL
);

CREATE SEQUENCE avis_decision_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE avis_consultation (
  avis_old character varying(2) NOT NULL,
  libelle character varying(30) NOT NULL,
  abrege character varying(10),
  om_validite_debut date,
  om_validite_fin date
);

CREATE SEQUENCE avis_consultation_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE avis_decision ADD COLUMN avis_decision integer NOT NULL DEFAULT nextval('avis_decision_seq'::regclass);
INSERT INTO avis_decision(avis_old, libelle, typeavis, sitadel, sitadel_motif) SELECT avis, libelle, typeavis, sitadel, sitadel_motif 
  FROM avis;

ALTER TABLE avis_consultation ADD COLUMN avis_consultation integer NOT NULL DEFAULT nextval('avis_consultation_seq'::regclass);
INSERT INTO avis_consultation(avis_old, libelle) SELECT avis, libelle 
  FROM avis_consultation_tmp;


ALTER TABLE ONLY avis_decision
    ADD CONSTRAINT avis_decision_pkey PRIMARY KEY (avis_decision);
ALTER TABLE ONLY avis_consultation
    ADD CONSTRAINT avis_consultation_pkey PRIMARY KEY (avis_consultation);
ALTER SEQUENCE avis_consultation_seq OWNED BY avis_consultation.avis_consultation;
ALTER SEQUENCE avis_decision_seq OWNED BY avis_decision.avis_decision;
ALTER TABLE avis_decision ALTER COLUMN avis_decision DROP DEFAULT;
ALTER TABLE avis_consultation ALTER COLUMN avis_consultation DROP DEFAULT;

-- Changement des clés étrangères pour 'avis'
ALTER TABLE consultation DROP CONSTRAINT consultation_avis_fkey;
ALTER TABLE evenement DROP CONSTRAINT evenement_avis_fkey;
ALTER TABLE instruction DROP CONSTRAINT instruction_avis_fkey;
ALTER TABLE dossier DROP CONSTRAINT dossier_avis_fkey;

ALTER TABLE consultation ADD COLUMN avis_consultation integer;
ALTER TABLE evenement ADD COLUMN avis_decision integer;
ALTER TABLE instruction ADD COLUMN avis_decision integer;
ALTER TABLE dossier ADD COLUMN avis_decision integer;


UPDATE consultation SET avis_consultation=(select avis_consultation.avis_consultation from avis_consultation where avis=avis_consultation.avis_old);

--
-- Modification de la table 'consultation'
--
ALTER TABLE consultation ADD COLUMN date_reception date;
ALTER TABLE consultation ADD COLUMN motivation text DEFAULT '';
ALTER TABLE consultation ADD COLUMN fichier character varying(100);
ALTER TABLE consultation ADD COLUMN lu boolean;


UPDATE evenement SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old);
UPDATE instruction SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old);
UPDATE dossier SET avis_decision=(select avis_decision.avis_decision from avis_decision where avis=avis_decision.avis_old);

ALTER TABLE consultation DROP COLUMN avis;
ALTER TABLE evenement DROP COLUMN avis;
ALTER TABLE instruction DROP COLUMN avis;
ALTER TABLE dossier DROP COLUMN avis;

ALTER TABLE ONLY consultation
    ADD CONSTRAINT consultation_avis_consultation_fkey FOREIGN KEY (avis_consultation) REFERENCES avis_consultation(avis_consultation);
ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);
ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_avis_decision_fkey FOREIGN KEY (avis_decision) REFERENCES avis_decision(avis_decision);
ALTER TABLE avis_decision DROP COLUMN avis_old;
ALTER TABLE avis_consultation DROP COLUMN avis_old;
DROP TABLE avis;
DROP TABLE avis_consultation_tmp;

--
-- Ajout des droits sur les nouvelles tables
--
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'service_categorie', '4');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_decision', '4');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_consultation', '4');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_service_service_categorie', '4');

ALTER TABLE consultation ALTER service SET NOT NULL;
ALTER TABLE dossier ADD COLUMN enjeu_erp boolean;
ALTER TABLE dossier ADD COLUMN enjeu_urba boolean;

INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_mes_retours', 4);
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_tous_retours', 4);
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'PC', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'messages_mes_retours', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'messages_tous_retours', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier_message_consulter', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier_message_modifier', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier_message_tab', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dossier', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'dashboard', 2); -- droit d'instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'menu_instruction', 2); -- droit d'instructeur

--
-- Modification de la structure des instructeurs
--
CREATE SEQUENCE direction_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
CREATE TABLE direction (
  direction integer NOT NULL,
  code character varying(20) NOT NULL,
  libelle character varying(100) NOT NULL,
  description text,
  chef character varying(100) NOT NULL,
  om_validite_debut date,
  om_validite_fin date,
  PRIMARY KEY (direction)
);

INSERT INTO direction VALUES (nextval('direction_seq'::regclass),'ADS', 'Direction ADS', 'Direction des autorisations des droits du sol', 'Mme Dupont', NULL, NULL);

CREATE SEQUENCE division_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
CREATE TABLE division (
  division integer NOT NULL,
  code character varying(20) NOT NULL,
  libelle character varying(100) NOT NULL,
  description text,
  chef character varying(100) NOT NULL,
  direction integer NOT NULL,
  om_validite_debut date,
  om_validite_fin date,
  PRIMARY KEY (division),
  FOREIGN KEY ( direction ) REFERENCES direction ( direction )
);

INSERT INTO division VALUES (nextval('division_seq'::regclass),'Defaut', 'Division par defaut', '', 'Mme Dupont',1, NULL, NULL);

CREATE SEQUENCE instructeur_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
CREATE TABLE instructeur (
  instructeur integer NOT NULL,
  nom character varying(100) NOT NULL,
  telephone character varying(14),
  division integer NOT NULL,
  om_utilisateur integer,
  om_validite_debut date,
  om_validite_fin date,
  PRIMARY KEY ( instructeur ),
  FOREIGN KEY ( division ) REFERENCES division ( division ),
  FOREIGN KEY ( om_utilisateur ) REFERENCES om_utilisateur ( om_utilisateur )
);

-- Insertion des utilisateurs instructeurs dans la table instructeur
INSERT INTO instructeur (instructeur,nom,telephone,division,om_utilisateur)
(SELECT om_utilisateur,nom,telephone,1,om_utilisateur FROM om_utilisateur WHERE instructeur='Oui');
-- Mise a jour de la sequence
SELECT setval('instructeur_seq',(SELECT MAX(instructeur) FROM instructeur));


-- Augment le om_parametre.libelle d'avoir 40 characteres
ALTER TABLE om_parametre ALTER libelle TYPE character varying(50);

-- Creation du parametre pour afficher ou non la division dans les dossiers
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'afficher_division','false',1);

-- Ajout des droits sur les tables d'organisation
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'direction', '2');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'division', '2');
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'instructeur', '2');

-- Modification de la clé étrangère  dossier -> om_utilisateur par dossier -> instructeur
ALTER TABLE dossier DROP CONSTRAINT dossier_instructeur_fkey;
ALTER TABLE dossier ADD CONSTRAINT
dossier_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur);

ALTER TABLE dossier ADD COLUMN division integer;
ALTER TABLE dossier ADD CONSTRAINT
dossier_division_fkey FOREIGN KEY (division) REFERENCES division(division);

-- Ajout des parametres des liens dans la table om_parametre
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'services_consultes_lien_interne', '',1);
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'::regclass),'services_consultes_lien_externe', '',1);
ALTER TABLE om_parametre ALTER COLUMN valeur TYPE text;


INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'avis_code_barre', '2');

--
-- Ajout des tables arrondissement, quartier et affectation_automatique
--
CREATE TABLE arrondissement (
        arrondissement integer NOT NULL,
        libelle character varying(3) NOT NULL,
        code_postal character varying(5) NOT NULL
);

ALTER TABLE ONLY arrondissement
    ADD CONSTRAINT arrondissement_pkey PRIMARY KEY (arrondissement);

CREATE SEQUENCE arrondissement_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE quartier (
        quartier integer NOT NULL,
        arrondissement integer NOT NULL,
        code_impots character varying(3) NOT NULL,
        libelle character varying(40) NOT NULL
);

ALTER TABLE ONLY quartier
    ADD CONSTRAINT quartier_pkey PRIMARY KEY (quartier);
ALTER TABLE ONLY quartier
    ADD CONSTRAINT quartier_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);

CREATE SEQUENCE quartier_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE affectation_automatique (
        affectation_automatique integer NOT NULL,
        arrondissement integer,
        quartier integer ,
        section varchar(2),
        instructeur integer NOT NULL
);

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_pkey PRIMARY KEY (affectation_automatique);
ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);
ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_quartier_fkey FOREIGN KEY (quartier) REFERENCES quartier(quartier);
ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_instructeur_fkey FOREIGN KEY (instructeur) REFERENCES instructeur(instructeur);

CREATE SEQUENCE affectation_automatique_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Ajout des droits pour le retour des services
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'demande_avis_encours', '2');

-- Suppression des colonnes inutiles dans la table om_utilisateur
ALTER TABLE om_utilisateur DROP instructeur;

ALTER TABLE om_utilisateur DROP telephone;

-- Ajout des droits sur lien_service_om_utilisateur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'lien_service_om_utilisateur', '2');
-- Ajout des droits pour le retour des services
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'demande_avis_passee', '2');

-- Ajout des droits sur affectation_automatique
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'affectation_automatique', '2');

-- Ajout du droit pour changer l'état (lu/non lu) d'une  consultation
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'consultation_modifier_lu', '2');

-- Changement de taille du champs parcelle de la table parcelle et terrain 
ALTER TABLE parcelle ALTER COLUMN parcelle TYPE character varying(20);
ALTER TABLE terrain ALTER COLUMN parcelle TYPE character varying(20);


--
-- Messages
--

-- create sequence for the message ID generation
CREATE SEQUENCE dossier_message_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Create table dossier_message
CREATE TABLE dossier_message (
    dossier_message integer PRIMARY KEY,
    dossier character varying(20),
    type character varying(60),
    emetteur character varying(40),
    date_emission TIMESTAMP NOT NULL,
    lu boolean default FALSE,
    contenu text,
    FOREIGN KEY ( dossier ) REFERENCES dossier ( dossier )
);

INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'menu_suivi', '2');

-- Droit de l'ajout forcé d'un instructeur
INSERT INTO om_droit VALUES (nextval('om_droit_seq'),'dossier_modifier_instructeur', '2');


---
--- TABLE : GENRE
---
CREATE TABLE genre (
        genre integer,
        code character varying(20),
        libelle character varying(100),
        description text
);
ALTER TABLE ONLY genre
    ADD CONSTRAINT genre_pkey PRIMARY KEY (genre);
CREATE SEQUENCE genre_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


---
--- TABLE : GROUPE
---
CREATE TABLE groupe (
        groupe integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        genre integer NOT NULL
);
ALTER TABLE ONLY groupe
    ADD CONSTRAINT groupe_pkey PRIMARY KEY (groupe);
ALTER TABLE ONLY groupe
    ADD CONSTRAINT groupe_genre_fkey FOREIGN KEY (genre) REFERENCES genre(genre);
CREATE SEQUENCE groupe_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


---
---
---
CREATE TABLE dossier_autorisation_type (
        dossier_autorisation_type integer,
        code character varying(20) not null,
        libelle character varying(100),
        description text,
        confidentiel boolean default FALSE,
        CONSTRAINT code_unique UNIQUE (code)
);

ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT dossier_autorisation_type_pkey PRIMARY KEY (dossier_autorisation_type);

CREATE SEQUENCE dossier_autorisation_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE dossier_autorisation_type_detaille (
        dossier_autorisation_type_detaille integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        dossier_autorisation_type integer NOT NULL
);

ALTER TABLE ONLY dossier_autorisation_type_detaille
    ADD CONSTRAINT dossier_autorisation_type_detaille_pkey PRIMARY KEY (dossier_autorisation_type_detaille);
ALTER TABLE ONLY dossier_autorisation_type_detaille
    ADD CONSTRAINT dossier_autorisation_type_detaille_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);

CREATE SEQUENCE dossier_autorisation_type_detaille_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Ajout du champs dossier_autorisation_type_detaille dans la table affectation_automatique et de la contrainte FK

ALTER TABLE affectation_automatique ADD COLUMN dossier_autorisation_type_detaille integer;

ALTER TABLE ONLY affectation_automatique
    ADD CONSTRAINT affectation_automatique_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);

--

CREATE TABLE dossier_instruction_type (
        dossier_instruction_type integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        dossier_autorisation_type_detaille integer NOT NULL,
        suffixe boolean default FALSE,
    cerfa integer,
    cerfa_lot integer
);

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_pkey PRIMARY KEY (dossier_instruction_type);
ALTER TABLE ONLY dossier_instruction_type
    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);

CREATE SEQUENCE dossier_instruction_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


-- Ajout de clé étrangère à la table dossier_autorisation_type
ALTER TABLE dossier_autorisation_type ADD COLUMN groupe integer;
ALTER TABLE ONLY dossier_autorisation_type
    ADD CONSTRAINT dossier_autorisation_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe);

--Demande nature

CREATE TABLE demande_nature (
        demande_nature integer,
        code character varying(20),
        libelle character varying(100),
        description text
);

ALTER TABLE ONLY demande_nature
    ADD CONSTRAINT demande_nature_pkey PRIMARY KEY (demande_nature);

CREATE SEQUENCE demande_nature_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--Demande type

CREATE TABLE demande_type (
        demande_type integer,
        code character varying(20),
        libelle character varying(100),
        description text,
        demande_nature integer,
        groupe integer,
        dossier_instruction_type integer,
        dossier_autorisation_type_detaille integer,
        contraintes character varying(20),
        etats_dossier_autorisation_autorises character varying(100),
        qualification boolean,
        evenement integer NOT NULL
);

ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_pkey PRIMARY KEY (demande_type);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_demande_nature_fkey FOREIGN KEY (demande_nature) REFERENCES demande_nature(demande_nature);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_groupe_fkey FOREIGN KEY (groupe) REFERENCES groupe(groupe);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_dossier_instruction_type_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);
ALTER TABLE ONLY demande_type
    ADD CONSTRAINT demande_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);

CREATE SEQUENCE demande_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE lien_evenement_dossier_autorisation_type (
        lien_evenement_dossier_autorisation_type integer,
        evenement integer,
        dossier_autorisation_type integer
);

ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_pkey PRIMARY KEY (lien_evenement_dossier_autorisation_type);
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);
ALTER TABLE ONLY lien_evenement_dossier_autorisation_type
    ADD CONSTRAINT lien_evenement_dossier_autorisation_type_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type(dossier_autorisation_type);

CREATE SEQUENCE lien_evenement_dossier_autorisation_type_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

--

CREATE TABLE autorite_competente (
        autorite_competente integer,
        code character varying(20),
        libelle character varying(100),
        description text
);

ALTER TABLE ONLY autorite_competente
    ADD CONSTRAINT autorite_competente_pkey PRIMARY KEY (autorite_competente);

CREATE SEQUENCE autorite_competente_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Ajout de clé étrangère à la table dossier_autorisation_type
ALTER TABLE dossier ADD COLUMN autorite_competente integer;
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_autorite_competente_fkey FOREIGN KEY (autorite_competente) REFERENCES autorite_competente(autorite_competente);

-- Donnees des tables 
INSERT INTO dossier_autorisation_type(dossier_autorisation_type, code, libelle) SELECT nextval('dossier_autorisation_type_seq'), nature, libelle FROM nature;
---INSERT INTO dossier_autorisation_type_detaille(dossier_autorisation_type_detaille, code, libelle) SELECT nextval('dossier_autorisation_type_seq'), nature, libelle FROM nature;

-- Table dossier_autorisation

CREATE TABLE dossier_autorisation (
    dossier_autorisation character varying(20),
    dossier_autorisation_type_detaille integer,
    exercice integer,
    insee integer,
    terrain_references_cadastrales character varying(100),
    terrain_adresse_voie_numero integer,
    complement character varying(30),
    terrain_adresse_lieu_dit character varying(30),
    terrain_adresse_localite character varying(30),
    terrain_adresse_code_postal character varying(5),
    terrain_adresse_bp character varying(15),
    terrain_adresse_cedex character varying(15),
    terrain_superficie double precision,
    arrondissement integer,
    depot_initial date,
    etat character varying(20),
    erp_numero_batiment integer,
    erp_ouvert boolean,
    erp_date_ouverture date,
    erp_arrete_decision boolean,
    erp_date_arrete_decision date,
        numero_version integer DEFAULT 0
);

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_pkey PRIMARY KEY (dossier_autorisation);
ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);
ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);
ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat);

CREATE SEQUENCE dossier_autorisation_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table Demande

CREATE TABLE demande (
        demande integer,
        dossier_autorisation_type_detaille integer NOT NULL,
        demande_type integer NOT NULL,
        dossier_instruction character varying(20),
        dossier_autorisation character varying(20),
        date_demande date NOT NULL,
        terrain_references_cadastrales character varying(100),
        terrain_adresse_voie_numero integer,
        complement character varying(30),
        terrain_adresse_lieu_dit character varying(30),
        terrain_adresse_localite character varying(30),
        terrain_adresse_code_postal character varying(5),
        terrain_adresse_bp character varying(15),
        terrain_adresse_cedex character varying(15),
        terrain_superficie double precision,
        nombre_lots integer,
        instruction_recepisse integer,
        arrondissement integer
);

ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_pkey PRIMARY KEY (demande);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_autorisation_type_detaille_fkey FOREIGN KEY (dossier_autorisation_type_detaille) REFERENCES dossier_autorisation_type_detaille(dossier_autorisation_type_detaille);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_demande_type_fkey FOREIGN KEY (demande_type) REFERENCES demande_type(demande_type);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_instruction_recepisse_fkey FOREIGN KEY (instruction_recepisse) REFERENCES instruction(instruction);
ALTER TABLE ONLY demande
    ADD CONSTRAINT demande_arrondissement_fkey FOREIGN KEY (arrondissement) REFERENCES arrondissement(arrondissement);

CREATE SEQUENCE demande_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table Demandeur

CREATE TABLE demandeur (
        demandeur integer,
        type_demandeur character varying(40),
        qualite character varying(40),
        particulier_civilite character varying(10),
        particulier_nom character varying(40),
        particulier_prenom character varying(40),
        particulier_date_naissance date,
        particulier_commune_naissance character varying(30),
        particulier_departement_naissance character varying(80),
        personne_morale_denomination character varying(15),
        personne_morale_raison_sociale character varying(15),
        personne_morale_siret character varying(15),
        personne_morale_categorie_juridique character varying(15),
        personne_morale_civilite character varying(10),
        personne_morale_nom character varying(40),
        personne_morale_prenom character varying(40),
        numero character varying(5),
        voie character varying(40),
        complement character varying(39),
        lieu_dit character varying(39),
        localite character varying(30),
        code_postal character varying(5),
        bp character varying(5),
        cedex character varying(5),
        pays character varying(40),
        division_territoriale character varying(40),
        telephone_fixe character varying(14),
        telephone_mobile character varying(14),
        indicatif character varying(5),
        courriel character varying(40),
        notification boolean,
        frequent boolean
);

ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_pkey PRIMARY KEY (demandeur);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_particulier_civilite_fkey FOREIGN KEY (particulier_civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_personne_morale_civilite_fkey FOREIGN KEY (personne_morale_civilite) REFERENCES civilite(civilite);

CREATE SEQUENCE demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table Lien demande demandeur

CREATE TABLE lien_demande_demandeur (
        lien_demande_demandeur integer,
        petitionnaire_principal boolean,
        demande integer,
        demandeur integer
);

ALTER TABLE ONLY lien_demande_demandeur
    ADD CONSTRAINT lien_demande_demandeur_pkey PRIMARY KEY (lien_demande_demandeur);
ALTER TABLE ONLY lien_demande_demandeur
    ADD CONSTRAINT lien_demande_demandeur_demande_fkey FOREIGN KEY (demande) REFERENCES demande(demande);
ALTER TABLE ONLY lien_demande_demandeur
    ADD CONSTRAINT lien_demande_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);

CREATE SEQUENCE lien_demande_demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table lot

CREATE TABLE lot (
        lot integer,
        dossier_instruction character varying(12)
);

ALTER TABLE ONLY lot
    ADD CONSTRAINT lot_pkey PRIMARY KEY (lot);
ALTER TABLE ONLY lot
    ADD CONSTRAINT lot_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);

CREATE SEQUENCE lot_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table lien_lot_demandeur

CREATE TABLE lien_lot_demandeur (
        lien_lot_demandeur integer,
        lot integer,
        demandeur integer, 
        petitionnaire_principal boolean
);

ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_pkey PRIMARY KEY (lien_lot_demandeur);
ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);
ALTER TABLE ONLY lien_lot_demandeur
    ADD CONSTRAINT lien_lot_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);

CREATE SEQUENCE lien_lot_demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


---
--- Nouvelle gestion des tableaux de bord
---

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 TABLE om_widget
    DROP CONSTRAINT om_widget_om_profil_fkey;

ALTER TABlE om_widget DROP COLUMN om_profil;

ALTER TABLE om_widget ADD COLUMN "type" character varying(40) NOT NULL DEFAULT 'web'::character varying;
ALTER TABLE om_widget ALTER COLUMN "lien" SET DEFAULT ''::character varying;
ALTER TABLE om_widget ALTER COLUMN "texte" SET DEFAULT ''::text;

 -- Modification de la table civilite
ALTER TABLE dossier DROP CONSTRAINT dossier_delegataire_civilite_fkey;
ALTER TABLE dossier DROP CONSTRAINT dossier_demandeur_civilite_fkey;
ALTER TABLE proprietaire DROP CONSTRAINT proprietaire_civilite_fkey;
ALTER TABLE demandeur DROP CONSTRAINT demandeur_particulier_civilite_fkey;
ALTER TABLE demandeur DROP CONSTRAINT demandeur_personne_morale_civilite_fkey;
ALTER TABLE civilite DROP CONSTRAINT civilite_pkey;

ALTER TABLE dossier RENAME COLUMN delegataire_civilite TO delegataire_civilite_old;
ALTER TABLE dossier RENAME COLUMN demandeur_civilite TO demandeur_civilite_old;
ALTER TABLE proprietaire RENAME COLUMN civilite TO civilite_old;
ALTER TABLE demandeur RENAME COLUMN particulier_civilite TO particulier_civilite_old;
ALTER TABLE demandeur RENAME COLUMN personne_morale_civilite TO personne_morale_civilite_old;
ALTER TABLE civilite RENAME COLUMN civilite TO civilite_old;

CREATE SEQUENCE civilite_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE civilite ADD COLUMN civilite integer NOT NULL DEFAULT nextval('civilite_seq'::regclass);
ALTER TABLE dossier ADD COLUMN delegataire_civilite integer;
ALTER TABLE dossier ADD COLUMN demandeur_civilite integer;
ALTER TABLE proprietaire ADD COLUMN civilite integer;
ALTER TABLE demandeur ADD COLUMN particulier_civilite integer;
ALTER TABLE demandeur ADD COLUMN personne_morale_civilite integer;

UPDATE dossier SET delegataire_civilite=(select civilite.civilite from civilite where delegataire_civilite_old=civilite.civilite_old);
UPDATE dossier SET demandeur_civilite=(select civilite.civilite from civilite where demandeur_civilite_old=civilite.civilite_old);
UPDATE proprietaire SET civilite=(select civilite.civilite from civilite where civilite_old=civilite.civilite_old);
UPDATE demandeur SET particulier_civilite=(select civilite.civilite from civilite where particulier_civilite_old=civilite.civilite_old);
UPDATE demandeur SET personne_morale_civilite=(select civilite.civilite from civilite where personne_morale_civilite_old=civilite.civilite_old);

ALTER TABLE civilite ADD COLUMN libelle character varying(100);
ALTER TABLE civilite ADD COLUMN om_validite_debut date;
ALTER TABLE civilite ADD COLUMN om_validite_fin date;


ALTER TABLE civilite RENAME COLUMN civilite_old TO code;
ALTER TABLE dossier DROP COLUMN delegataire_civilite_old;
ALTER TABLE dossier DROP COLUMN demandeur_civilite_old;
ALTER TABLE proprietaire DROP COLUMN civilite_old;
ALTER TABLE demandeur DROP COLUMN particulier_civilite_old;
ALTER TABLE demandeur DROP COLUMN personne_morale_civilite_old;

ALTER TABLE ONLY civilite
    ADD CONSTRAINT civilite_pkey PRIMARY KEY (civilite);

ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_delegataire_civilite_fkey FOREIGN KEY (delegataire_civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_demandeur_civilite_fkey FOREIGN KEY (demandeur_civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY proprietaire
    ADD CONSTRAINT proprietaire_civilite_fkey FOREIGN KEY (civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_particulier_civilite_fkey FOREIGN KEY (particulier_civilite) REFERENCES civilite(civilite);
ALTER TABLE ONLY demandeur
    ADD CONSTRAINT demandeur_personne_morale_civilite_fkey FOREIGN KEY (personne_morale_civilite) REFERENCES civilite(civilite);

ALTER SEQUENCE civilite_seq OWNED BY civilite.civilite;

ALTER TABLE civilite ALTER COLUMN civilite DROP DEFAULT;

UPDATE civilite SET libelle='Monsieur Madame' WHERE civilite = 1 ;
UPDATE civilite SET libelle='Mademoiselle' WHERE civilite = 2 ;
UPDATE civilite SET libelle='Madame' WHERE civilite = 3 ;
UPDATE civilite SET libelle='Monsieur' WHERE civilite = 4 ;

-- Table Lien dossier_autorisation demandeur

CREATE TABLE lien_dossier_autorisation_demandeur (
    lien_dossier_autorisation_demandeur integer,
    petitionnaire_principal boolean,
    dossier_autorisation character varying(20),
    demandeur integer
);

ALTER TABLE ONLY lien_dossier_autorisation_demandeur
    ADD CONSTRAINT lien_dossier_autorisation_demandeur_pkey PRIMARY KEY (lien_dossier_autorisation_demandeur);
ALTER TABLE ONLY lien_dossier_autorisation_demandeur
    ADD CONSTRAINT lien_dossier_autorisation_demandeur_demande_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);
ALTER TABLE ONLY lien_dossier_autorisation_demandeur
    ADD CONSTRAINT lien_dossier_autorisation_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);

CREATE SEQUENCE lien_dossier_autorisation_demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

  -- Ajout du champ à qualifier --
  
  ALTER TABLE dossier ADD COLUMN a_qualifier boolean;

-- Ajout d'un champ à la table état --
ALTER TABLE etat ADD COLUMN statut character varying(60);
UPDATE etat set statut='encours'
where etat = 'notifier' or etat = 'majorer' or etat = 'initialiser';
UPDATE etat set statut='cloture'
where etat = 'accepter' or etat = 'cloturer' or etat = 'rejeter'
or etat = 'executer' or etat = 'terminer';

--Changement des civilités dans les états et sous-états
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEavis_decision on dossier.avis_decision=avis_decision.avis_decision
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite
where dossier_autorisation_type_detaille.code=''&nature'' AND (date_decision>=''&datedebut''   AND date_decision<=''&datefin'')  ORDER BY dossier'
WHERE om_sousetat = 10;


UPDATE om_etat SET om_sql='select dossier,
dossier_autorisation_type_detaille.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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
inner join &DB_PREFIXEnature on nature.nature = dossier.nature
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEcivilite on demandeur_civilite=civilite.civilite
where dossier = ''&idx''' WHERE om_etat = 7;

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  &DB_PREFIXEconsultation
inner join &DB_PREFIXEservice on service.service = consultation.service
inner join &DB_PREFIXEdossier on dossier.dossier =consultation.dossier
left join &DB_PREFIXEtravaux on travaux.travaux = dossier.travaux
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite
where  consultation= &idx' WHERE om_etat = 6;

UPDATE om_etat SET om_sql='select service.libelle as service, service.adresse,
service.cp, service.ville, dossier.dossier,
dossier_autorisation_type_detaille.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 &DB_PREFIXEconsultation
inner join &DB_PREFIXEdossier on dossier.dossier=consultation.dossier
inner join &DB_PREFIXEservice on service.service=consultation.service
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite
where consultation = &idx' WHERE om_etat = 4;

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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
inner join &DB_PREFIXEdossier_autorisation_type on dossier_autorisation_type.dossier_autorisation_type=dossier_autorisation_type_detaille.dossier_autorisation_type
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite 
where &DB_PREFIXEdossier_autorisation_type.code=''&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(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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
inner join &DB_PREFIXEdossier_autorisation_type on dossier_autorisation_type.dossier_autorisation_type=dossier_autorisation_type_detaille.dossier_autorisation_type
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEavis_decision on dossier.avis_decision=avis_decision.avis_decision
where dossier_autorisation_type.code=''&nature'' AND (date_depot>=''&datedebut'' AND date_depot<=''&datefin'')
ORDER BY dossier' WHERE om_sousetat = 8;

-- Ajout d'un nouvel état et d'un nouvel sous-état pour les affichages des dossiers / ! \ GROUPE a rajouter
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', '
No commune : &departement &commune &ville', 120, 7, 195, 5, 'helvetica', '', 10, '0', 'J',
'select nom from &DB_PREFIXEom_utilisateur', 'dossier_m', 'helvetica', 8, 5, 5, '0-0-0');

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 
    CONCAT(''Commune &commune'','' '',''Dossier '', dossier) as dossier, 
    CONCAT(''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(dossier.terrain_adresse_voie_numero, '' '', dossier.complement, '' '', dossier.terrain_adresse_code_postal, '' '', dossier.terrain_adresse_localite, '' '', 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, 
    CONCAT(''Decision'', COALESCE(to_char(date_decision,''DD/MM/YYYY''),''inconu''), '' Limite '', COALESCE(to_char(date_limite,''DD/MM/YYYY''),''inconu'')) as date_dc_l, 
    CONCAT(delai, '' mois'') as delai, 
    '' '' as date_affichage_decision, 
    '' '' as DOC_DAT_Conformite 
FROM 
    &DB_PREFIXEdossier 
    INNER JOIN 
        &DB_PREFIXEdossier_autorisation 
    ON 
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation 
    INNER JOIN 
        &DB_PREFIXEdossier_autorisation_type_detaille 
    ON 
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille 
    INNER JOIN 
        &DB_PREFIXEdossier_autorisation_type 
    ON 
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type 
    LEFT JOIN 
        &DB_PREFIXEcivilite 
    ON 
        demandeur_civilite = civilite.civilite 
    LEFT JOIN 
        &DB_PREFIXEtravaux 
    ON 
        dossier.travaux=travaux.travaux 
    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 
WHERE 
    (select e.statut from &DB_PREFIXEetat e where e.etat = dossier.etat ) = ''encours'' 
ORDER BY 
    dossier_autorisation_type.libelle, arrondissement.libelle');

-- Ajout d'un nouvel évènement "affichage_obligatoire"
INSERT INTO evenement VALUES (89, 'affichage_obligatoire', 'T', 'divers', NULL, 0, 'Non', 0, 'attestation_affichage', '   ', NULL);
ALTER SEQUENCE evenement_seq RESTART WITH 90;

-- Ajout d'un nouveau paramètre pour l'évènement "affichage_obligatoire"
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'), 'affichage_obligatoire', '89', 1);

-- Ajout des colonnes manquant dans dossier por rapport à la table demande
ALTER TABLE dossier ADD COLUMN terrain_references_cadastrales character varying(100);
ALTER TABLE dossier ADD COLUMN terrain_adresse_voie_numero integer;
ALTER TABLE dossier ADD COLUMN complement character varying(30);
ALTER TABLE dossier ADD COLUMN terrain_adresse_lieu_dit character varying(30);
ALTER TABLE dossier ADD COLUMN terrain_adresse_localite character varying(30);
ALTER TABLE dossier ADD COLUMN terrain_adresse_code_postal character varying(5);
ALTER TABLE dossier ADD COLUMN terrain_adresse_bp character varying(15);
ALTER TABLE dossier ADD COLUMN terrain_adresse_cedex character varying(15);
ALTER TABLE dossier ADD COLUMN terrain_superficie double precision;

-- Ajout d'une nouvelle lettre type
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');

  -- Table Lien demande demandeur

CREATE TABLE lien_dossier_demandeur (
    lien_dossier_demandeur integer,
    petitionnaire_principal boolean,
    dossier character varying(20),
    demandeur integer
);

ALTER TABLE ONLY lien_dossier_demandeur
    ADD CONSTRAINT lien_dossier_demandeur_pkey PRIMARY KEY (lien_dossier_demandeur);
ALTER TABLE ONLY lien_dossier_demandeur
    ADD CONSTRAINT lien_dossier_demandeur_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);
ALTER TABLE ONLY lien_dossier_demandeur
    ADD CONSTRAINT lien_dossier_demandeur_demandeur_fkey FOREIGN KEY (demandeur) REFERENCES demandeur(demandeur);

CREATE SEQUENCE lien_dossier_demandeur_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE dossier ALTER nature TYPE character varying(3);
ALTER TABLE nature ALTER nature TYPE character varying(3);
ALTER TABLE nature ALTER libelle TYPE character varying(150);
ALTER TABLE dossier ALTER nature DROP NOT NULL;

INSERT INTO nature VALUES ('PCI', 'Permis de construire pour une maison individuelle et / ou ses annexes');
INSERT INTO nature VALUES ('PCA', 'Permis de construire comprenant ou non des démolitions');
INSERT INTO nature VALUES ('AZ', 'Demande d''autorisation spéciale de travaux dans le périmètre d''une AVAP');
INSERT INTO nature VALUES ('DAT', 'Demande d''autorisation de construire, d''aménager ou de modifier un ERP');

ALTER TABLE dossier ALTER COLUMN dossier TYPE character varying(20);

ALTER TABLE dossier ADD column dossier_autorisation character varying(20) NOT NULL;
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);

ALTER TABLE instruction ALTER COLUMN dossier TYPE character varying(20);

ALTER TABLE instruction ALTER COLUMN dossier TYPE character varying(20);
ALTER TABLE consultation ALTER COLUMN dossier TYPE character varying(20);
ALTER TABLE terrain ALTER COLUMN dossier TYPE character varying(20);
ALTER TABLE blocnote ALTER COLUMN dossier TYPE character varying(20);
ALTER TABLE destination_shon ALTER COLUMN dossier TYPE character varying(20);
ALTER TABLE statistique ALTER COLUMN dossier TYPE character varying(20);

INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'), 'option_ERP', 'true', 1);
INSERT INTO om_parametre VALUES (nextval('om_parametre_seq'), 'option_GED', 'false', 1);

ALTER TABLE om_utilisateur ALTER COLUMN email TYPE character varying(100);


---
--- GESTION DES COMMISSIONS
---

CREATE TABLE commission_type (
    commission_type integer NOT NULL,
    code character varying(10),
    libelle character varying(100),

    lieu_adresse_ligne1 character varying(100),
    lieu_adresse_ligne2 character varying(100),
    
    lieu_salle character varying(100),
    
    listes_de_diffusion text,
    
    participants text,
    
    corps_du_courriel text,
    
    om_validite_debut date,
    om_validite_fin date
);

CREATE TABLE commission (
    commission integer NOT NULL,
    
    code character varying(20),

    commission_type integer NOT NULL,

    libelle character varying(100),    

    date_commission date NOT NULL,
    heure_commission character varying(5),

    lieu_adresse_ligne1 character varying(100),
    lieu_adresse_ligne2 character varying(100),
    
    lieu_salle character varying(100),
    
    listes_de_diffusion text,
    
    participants text
);

CREATE TABLE dossier_commission (
    dossier_commission integer NOT NULL,
    
    dossier character varying(20) NOT NULL,
    
    commission_type integer NOT NULL,
    date_souhaitee date NOT NULL,
    motivation text,

    
    commission integer,
    
    avis text,
    lu boolean default FALSE
);

ALTER TABLE ONLY commission_type
    ADD CONSTRAINT commission_type_pkey PRIMARY KEY(commission_type);
ALTER TABLE ONLY commission
    ADD CONSTRAINT commission_pkey PRIMARY KEY(commission);
ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_pkey PRIMARY KEY(dossier_commission);


ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);

ALTER TABLE ONLY commission
    ADD CONSTRAINT commission_commission_type_fkey FOREIGN KEY (commission_type) REFERENCES commission_type(commission_type);

ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_commission_fkey FOREIGN KEY (commission) REFERENCES commission(commission);

ALTER TABLE ONLY dossier_commission
    ADD CONSTRAINT dossier_commission_commission_type_fkey FOREIGN KEY (commission_type) REFERENCES commission_type(commission_type);


CREATE SEQUENCE commission_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE SEQUENCE commission_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE SEQUENCE dossier_commission_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


---
--- FIN GESTION DES COMMISSIONS
---

-- Suppression du champ action et ajout du champs evenement dans la table transition
-- Création d'une table temporaire

CREATE TABLE transition_tmp (
    transition_tmp integer NOT NULL,
    etat character varying(20) NOT NULL,
    evenement integer NOT NULL
);

CREATE SEQUENCE transition_tmp_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Ajout des données
INSERT INTO transition_tmp ( transition_tmp, etat, evenement)
        SELECT 
                nextval('transition_tmp_seq'), transition.etat, evenement.evenement
        FROM 
                transition
        LEFT JOIN 
                evenement
                ON 
                        transition.action=evenement.action;

-- Suppression de l'ancienne table et des contraintes
ALTER TABLE transition DROP CONSTRAINT transition_pkey;
ALTER TABLE transition DROP CONSTRAINT transition_action_fkey;
ALTER TABLE transition DROP CONSTRAINT transition_etat_fkey;
DROP SEQUENCE transition_seq;
DROP TABLE transition;

-- Renommage de la table, champ, ajout contraintes PK + FK, renommage de la séquence
ALTER TABLE transition_tmp RENAME TO transition;
ALTER TABLE transition RENAME COLUMN transition_tmp TO transition;

ALTER TABLE ONLY transition
    ADD CONSTRAINT transition_pkey PRIMARY KEY(transition);

ALTER TABLE ONLY transition
    ADD CONSTRAINT transition_etat_fkey FOREIGN KEY (etat) REFERENCES etat(etat);

ALTER TABLE ONLY transition
    ADD CONSTRAINT transition_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);

ALTER TABLE transition_tmp_seq RENAME TO transition_seq;
ALTER TABLE transition ALTER COLUMN transition SET DEFAULT NEXTVAL('transition_seq');


-- Création de la table "lien_dossier_instruction_type_evenement" 
CREATE TABLE lien_dossier_instruction_type_evenement (
    lien_dossier_instruction_type_evenement integer,
    dossier_instruction_type integer,
    evenement integer
);

ALTER TABLE ONLY lien_dossier_instruction_type_evenement
    ADD CONSTRAINT lien_dossier_instruction_type_evenement_pkey PRIMARY KEY (lien_dossier_instruction_type_evenement);
ALTER TABLE ONLY lien_dossier_instruction_type_evenement
    ADD CONSTRAINT lien_dossier_instruction_type_evenement_dossier_instruction_type_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);
ALTER TABLE ONLY lien_dossier_instruction_type_evenement
    ADD CONSTRAINT lien_dossier_instruction_type_evenement_evenement_fkey FOREIGN KEY (evenement) REFERENCES evenement(evenement);

CREATE SEQUENCE lien_dossier_instruction_type_evenement_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


---
--- Suppression de la table nature
---

--- Table travaux : remplacement de la colonne nature par dossier_autorisation_type
ALTER TABLE ONLY travaux ADD COLUMN dossier_autorisation_type integer;

UPDATE travaux SET dossier_autorisation_type = 
    (SELECT dossier_autorisation_type FROM dossier_autorisation_type
        WHERE dossier_autorisation_type.code=travaux.nature);

ALTER TABLE ONLY travaux
    ADD CONSTRAINT travaux_dossier_autorisation_type_fkey 
    FOREIGN KEY (dossier_autorisation_type) 
    REFERENCES dossier_autorisation_type(dossier_autorisation_type);

ALTER TABLE ONLY travaux DROP COLUMN nature;

--- Table dossier : suppression de la colonne nature
ALTER TABLE ONLY dossier DROP COLUMN nature;

--- Table evenement : suppression de la colonne nature (liaison N à N avec dossier_autorisation_type_detaille)
INSERT INTO lien_dossier_instruction_type_evenement
    SELECT nextval('lien_dossier_instruction_type_evenement_seq'),dossier_instruction_type.dossier_instruction_type,evenement.evenement
    FROM evenement
    INNER JOIN dossier_autorisation_type 
        ON dossier_autorisation_type.code=evenement.nature
    INNER JOIN dossier_autorisation_type_detaille 
        ON dossier_autorisation_type_detaille.dossier_autorisation_type=dossier_autorisation_type.dossier_autorisation_type
    INNER JOIN dossier_instruction_type 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille;

INSERT INTO lien_dossier_instruction_type_evenement
    SELECT nextval('lien_dossier_instruction_type_evenement_seq'),dossier_instruction_type,evenement
    FROM evenement AS e INNER JOIN dossier_instruction_type AS dit on e.nature='T';

ALTER TABLE ONLY evenement DROP COLUMN nature;

--- Table bible : remplacement de la colonne nature par dossier_autorisation_type

ALTER TABLE bible ADD COLUMN dossier_autorisation_type integer;

UPDATE bible SET dossier_autorisation_type=(
    SELECT dossier_autorisation_type
    FROM dossier_autorisation_type
    WHERE dossier_autorisation_type.code=bible.nature
);

ALTER TABLE bible DROP COLUMN nature;

--- Suppression de la table nature
DROP TABLE nature;

--- Mise à jour des etats

UPDATE om_etat SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier =  ''&idx''' WHERE om_etat = 7;

UPDATE om_etat SET om_sql = 'select service.libelle as service, service.adresse,
service.cp, service.ville, dossier.dossier,
dossier_autorisation_type_detaille.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 &DB_PREFIXEconsultation
inner join &DB_PREFIXEdossier on dossier.dossier=consultation.dossier
inner join &DB_PREFIXEservice on service.service=consultation.service
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite
where consultation = &idx' WHERE om_etat = 4;

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 &DB_PREFIXE consultation
inner join &DB_PREFIXEservice on service.service = consultation.service
inner join &DB_PREFIXEdossier on dossier.dossier =consultation.dossier
left join &DB_PREFIXEtravaux on travaux.travaux = dossier.travaux
left join &DB_PREFIXEcivilite on demandeur_civilite = civilite.civilite
where  consultation= &idx' WHERE om_etat = 6;

UPDATE om_etat SET om_sql = 'select nom from &DB_PREFIXEom_utilisateur' WHERE om_etat = 5;

UPDATE om_etat SET om_sql = 'select nom from &DB_PREFIXEom_utilisateur' WHERE om_etat = 3;

UPDATE om_etat SET om_sql = 'select nom from &DB_PREFIXEom_utilisateur' WHERE om_etat = 2;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'')  as date_depot,
to_char(date_rejet,''DD/MM/YYYY'')  as date_rejet,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 2;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email,om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 3;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 4;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
concat(substring(date_depot,9,2),''/'',substring(date_depot,6,2),''/'',substring(date_depot,1,4)) as date_depot,                concat(substring(date_notification_delai,9,2),''/'',substring(date_notification_delai,6,2),''/'',substring(date_notification_delai,1,4)) as date_notification_delai,
concat(substring(date_limite,9,2),''/'',substring(date_limite,6,2),''/'',substring(date_limite,1,4)) as date_limite,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 5;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 6;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 7;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 8;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
to_char(date_complet,''DD/MM/YYYY'') as date_complet,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 9;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 25;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
to_char(date_decision,''DD/MM/YYYY'') as date_decision,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 10;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 11;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 12;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 13;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 14;

UPDATE om_lettretype SET om_sql = 'select dossier,
dossier_autorisation_type_detaille.libelle as nature, 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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux,
om_utilisateur.nom,
om_utilisateur.email,
om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 15;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 16;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 17;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature, delai,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
to_char(date_rejet,''DD/MM/YYYY'') as date_rejet,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 18;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
concat(substring(date_depot,9,2),''/'',substring(date_depot,6,2),''/'',substring(date_depot,1,4)) as date_depot,                concat(substring(date_notification_delai,9,2),''/'',substring(date_notification_delai,6,2),''/'',substring(date_notification_delai,1,4)) as date_notification_delai,
concat(substring(date_limite,9,2),''/'',substring(date_limite,6,2),''/'',substring(date_limite,1,4)) as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email,om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 19;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
to_char(date_rejet,''DD/MM/YYYY'') as date_rejet,
travaux.libelle as travaux, temp1
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 20;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
concat(substring(date_depot,9,2),''/'',substring(date_depot,6,2),''/'',substring(date_depot,1,4)) as date_depot,                concat(substring(date_notification_delai,9,2),''/'',substring(date_notification_delai,6,2),''/'',substring(date_notification_delai,1,4)) as date_notification_delai,
concat(substring(date_limite,9,2),''/'',substring(date_limite,6,2),''/'',substring(date_limite,1,4)) as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email,om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 21;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
to_char(date_rejet,''DD/MM/YYYY'') as date_rejet,
travaux.libelle as travaux, temp1
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 22;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 23;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 24;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email,om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 26;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 27;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
concat(substring(date_depot,9,2),''/'',substring(date_depot,6,2),''/'',substring(date_depot,1,4)) as date_depot,                concat(substring(date_notification_delai,9,2),''/'',substring(date_notification_delai,6,2),''/'',substring(date_notification_delai,1,4)) as date_notification_delai,
concat(substring(date_limite,9,2),''/'',substring(date_limite,6,2),''/'',substring(date_limite,1,4)) as date_limite,
concat(substring(date_decision,9,2),''/'',substring(date_decision,6,2),''/'',substring(date_decision,1,4)) as date_decision,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 28;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 29;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 30;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 31;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
concat(substring(date_depot,9,2),''/'',substring(date_depot,6,2),''/'',substring(date_depot,1,4)) as date_depot,                concat(substring(date_notification_delai,9,2),''/'',substring(date_notification_delai,6,2),''/'',substring(date_notification_delai,1,4)) as date_notification_delai,
concat(substring(date_limite,9,2),''/'',substring(date_limite,6,2),''/'',substring(date_limite,1,4)) as date_limite,
concat(substring(date_complet,9,2),''/'',substring(date_complet,6,2),''/'',substring(date_complet,1,4)) as date_complet,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 32;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 33;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 34;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 35;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
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 &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 36;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 37;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 38;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'') as date_depot,
to_char(date_rejet,''DD/MM/YYYY'') as date_rejet,
travaux.libelle as travaux, temp1
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 39;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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_notification_delai,''DD/MM/YYYY'') as date_notification_delai,
to_char(date_limite,''DD/MM/YYYY'') as date_limite,
travaux.libelle as travaux, om_utilisateur.nom, om_utilisateur.email, om_utilisateur.telephone
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
left join &DB_PREFIXEom_utilisateur on dossier.instructeur=om_utilisateur.om_utilisateur
where dossier = ''&destinataire''' WHERE om_lettretype = 40;

UPDATE om_lettretype SET om_sql = 'select
dossier,dossier_autorisation_type_detaille.libelle as nature,
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,
to_char(date_depot,''DD/MM/YYYY'')  as date_depot,
to_char(date_rejet,''DD/MM/YYYY'')  as date_rejet,
travaux.libelle as travaux
from &DB_PREFIXEdossier
inner join &DB_PREFIXEdossier_autorisation on dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
inner join &DB_PREFIXEdossier_autorisation_type_detaille on dossier_autorisation_type_detaille.dossier_autorisation_type_detaille=dossier_autorisation.dossier_autorisation_type_detaille
left join &DB_PREFIXEtravaux on dossier.travaux=travaux.travaux
where dossier = ''&destinataire''' WHERE om_lettretype = 41;

UPDATE om_sousetat SET om_sql = 'SELECT evenement.libelle as evenement,
instruction.lettretype,
to_char(date_evenement,''DD/MM/YYYY'') as datecourrier
from &DB_PREFIXEinstruction
inner join &DB_PREFIXEevenement on evenement.evenement = instruction.evenement
where dossier = ''&idx''' WHERE om_sousetat = 2;

UPDATE om_sousetat SET om_sql = 'select
blocnote,note,dossier 
from &DB_PREFIXEblocnote where
dossier = ''&idx''' WHERE om_sousetat = 3;

UPDATE om_sousetat SET om_sql = 'SELECT
destination_shon as no,destination.libelle,shon
from &DB_PREFIXEdestination_shon 
inner join &DB_PREFIXEdestination on destination_shon.destination=destination.destination
where dossier = ''&idx''' WHERE om_sousetat = 4;

UPDATE om_sousetat SET om_sql = 'select
dossier||'' ''||types||'' du ''||to_char(date_demande,''DD/MM/YYYY'') as demande,
demandeur_nom,etat 
from &DB_PREFIXEdossier
where substring(dossier,1,9) like substring(''&idx'',1,9)' 
WHERE om_sousetat = 5;

UPDATE om_sousetat SET om_sql = 'select
service.libelle||'' ''||to_char(date_envoi,''DD/MM/YYYY'')  as envoi,
avis_consultation.libelle||'' du ''||to_char(date_retour,''DD/MM/YYYY'')  as retour,
to_char(date_limite,''DD/MM/YYYY'')  as limite
from &DB_PREFIXEconsultation inner join &DB_PREFIXEservice on service.service = consultation.service
left join &DB_PREFIXEavis_consultation on avis_consultation.avis_consultation=consultation.avis_consultation
where consultation.dossier = ''&idx'''
WHERE om_sousetat = 6;

UPDATE om_sousetat SET om_sql = 'SELECT terrain.parcelle,
proprietaire.nom||'' ''||  proprietaire.prenom as proprietaire,
parcelle.surface
from &DB_PREFIXEterrain inner join &DB_PREFIXEparcelle on terrain.parcelle = parcelle.parcelle
left join &DB_PREFIXEproprietaire on proprietaire.proprietaire = parcelle.proprietaire
where dossier = ''&idx'''
WHERE om_sousetat = 7;

---
--- Fin de suppression de la table nature
---


-- Modification de la table evenement
ALTER TABLE ONLY evenement ADD COLUMN restriction character varying(60);
ALTER TABLE ONLY evenement ADD COLUMN type character varying(100);
ALTER TABLE ONLY evenement ADD COLUMN evenement_retour_ar integer;
ALTER TABLE ONLY evenement ADD COLUMN evenement_suivant_tacite integer;

ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_evenement_retour_ar_fkey FOREIGN KEY (evenement_retour_ar) REFERENCES evenement(evenement);
ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_evenement_suivant_tacite_fkey FOREIGN KEY (evenement_suivant_tacite) REFERENCES evenement(evenement);

-- Modification de la table instruction
ALTER TABLE instruction ADD COLUMN date_finalisation_courrier date;
ALTER TABLE instruction ADD COLUMN date_envoi_signature date;
ALTER TABLE instruction ADD COLUMN date_retour_signature date;
ALTER TABLE instruction ADD COLUMN date_envoi_rar date;
ALTER TABLE instruction ADD COLUMN date_retour_rar date;
ALTER TABLE instruction ADD COLUMN date_envoi_controle_legalite date;
ALTER TABLE instruction ADD COLUMN date_retour_controle_legalite date;

ALTER TABLE instruction RENAME COLUMN datecourrier TO date_evenement;

-- Mis à jour des requêtes concernant l'ancien champ datecourrier
UPDATE action SET regle_date_limite='date_evenement+delai', regle_date_notification_delai='date_evenement+1', regle_date_complet='date_evenement' WHERE action = 'retour';
UPDATE action SET regle_date_rejet='date_evenement' WHERE action = 'rejet';
UPDATE action SET regle_date_validite='date_evenement+delai', regle_date_decision='date_evenement' WHERE action = 'acceptation';
UPDATE action SET regle_date_decision='date_evenement' WHERE action = 'refus';
UPDATE action SET regle_date_limite='date_evenement+delai', regle_date_validite='date_evenement+delai+2', regle_date_decision='date_evenement' WHERE action = 'sursis';
UPDATE action SET regle_date_chantier='date_evenement' WHERE action = 'execution';
UPDATE action SET regle_date_achevement='date_evenement' WHERE action = 'achevement';
UPDATE action SET regle_date_conformite='date_evenement' WHERE action = 'archivage';

-- Agrandissement du champ libelle de la table om_widget
ALTER TABLE om_widget ALTER COLUMN libelle TYPE character varying(100);

-- Gestion du rapport d'instruction
CREATE TABLE rapport_instruction (
        rapport_instruction integer,
        dossier_instruction character varying(20),
        analyse_reglementaire text,
        description_projet text,
        proposition_decision text
);

ALTER TABLE ONLY rapport_instruction
    ADD CONSTRAINT rapport_instruction_pkey PRIMARY KEY (rapport_instruction);
ALTER TABLE ONLY rapport_instruction
    ADD CONSTRAINT rapport_instruction_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);

CREATE SEQUENCE rapport_instruction_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE om_profil ADD COLUMN om_validite_debut date;
ALTER TABLE om_profil ADD COLUMN om_validite_fin date;

-- Ajout de la colonne dossier_instruction_type dans la table dossier
ALTER TABLE dossier ADD COLUMN dossier_instruction_type integer NOT NULL;
ALTER TABLE ONLY dossier
    ADD CONSTRAINT dossier_instruction_type_dossier_fkey FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type);

-- Gestion des signataires
CREATE TABLE signataire_arrete (
        signataire_arrete integer,
        civilite integer,
        nom character varying(80),
        prenom character varying(80),
        qualite character varying(80),
        signature text,
        defaut boolean,
        om_validite_debut date,
    om_validite_fin date
);

ALTER TABLE ONLY signataire_arrete
    ADD CONSTRAINT signataire_arrete_pkey PRIMARY KEY (signataire_arrete);
ALTER TABLE ONLY signataire_arrete
    ADD CONSTRAINT signataire_arrete_civilite_fkey FOREIGN KEY (civilite) REFERENCES civilite(civilite);

CREATE SEQUENCE signataire_arrete_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- FK dans la table instruction
ALTER TABLE instruction ADD COLUMN signataire_arrete integer;

ALTER TABLE ONLY instruction
    ADD CONSTRAINT instruction_signataire_arrete_fkey FOREIGN KEY (signataire_arrete) REFERENCES signataire_arrete(signataire_arrete);

-- Modification de la structure pour l'ajout des lots

ALTER TABLE lot DROP COLUMN dossier_instruction;
ALTER TABLE lot ADD COLUMN libelle character varying(80) NOT NULL;
ALTER TABLE lot ADD COLUMN dossier_autorisation character varying(20);

ALTER TABLE lot ADD CONSTRAINT lot_dossier_autorisation_fkey 
    FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);

CREATE TABLE lien_dossier_lot (
    lien_dossier_lot integer,
    dossier character varying(20),
    lot integer
);

ALTER TABLE ONLY lien_dossier_lot
    ADD CONSTRAINT lien_dossier_lot_pkey PRIMARY KEY (lien_dossier_lot);
ALTER TABLE ONLY lien_dossier_lot
    ADD CONSTRAINT lien_dossier_lot_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);
ALTER TABLE ONLY lien_dossier_lot
    ADD CONSTRAINT lien_dossier_lot_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);

CREATE SEQUENCE lien_dossier_lot_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE demande DROP COLUMN nombre_lots;

-- Table CERFA
CREATE TABLE cerfa (
    cerfa integer,
    libelle character varying(200),
        code character varying(20),
    om_validite_debut date,
    om_validite_fin date,
    avap_co_elt_pro boolean,
    avap_nouv_haut_surf boolean,
    avap_co_clot boolean,
    avap_aut_coup_aba_arb boolean,
    avap_ouv_infra boolean,
    avap_aut_inst_mob boolean,
    avap_aut_plant boolean,
    avap_aut_auv_elec boolean,
    tr_total boolean,
    tr_partiel boolean,
    tr_desc boolean,
    am_lotiss boolean,
    am_autre_div boolean,
    am_camping boolean,
    am_caravane boolean,
    am_carav_duree boolean,
    am_statio boolean,
    am_statio_cont boolean,
    am_affou_exhau boolean,
        am_affou_exhau_sup boolean, 
    am_affou_prof boolean,
    am_exhau_haut boolean,
    am_coupe_abat boolean,
    am_prot_plu boolean,
    am_prot_muni boolean,
    am_mobil_voyage boolean,
    am_voyage_deb boolean,
    am_voyage_fin boolean,
    am_aire_voyage boolean,
    am_rememb_afu boolean,
    am_parc_resid_loi boolean,
    am_sport_moto boolean,
    am_sport_attrac boolean,
    am_sport_golf boolean,
    am_mob_art boolean,
    am_modif_voie_esp boolean,
    am_plant_voie_esp boolean,
    am_chem_ouv_esp boolean,
    am_agri_peche boolean,
    am_crea_voie boolean,
    am_modif_voie_exist boolean,
    am_crea_esp_sauv boolean,
    am_modif_amgt boolean,
    am_crea_esp_class boolean,
    am_projet_desc boolean,
    am_terr_surf boolean,
    am_tranche_desc boolean,
    am_lot_max_nb boolean,
    am_lot_max_shon boolean,
    am_lot_max_shob boolean,
    am_lot_cstr_cos boolean,
    am_lot_cstr_plan boolean,
    am_lot_cstr_vente boolean,
    am_lot_fin_diff boolean,
    am_lot_consign boolean,
    am_lot_gar_achev boolean,
    am_lot_vente_ant boolean,
    am_empl_nb boolean,
    am_tente_nb boolean,
    am_carav_nb boolean,
    am_mobil_nb boolean,
    am_pers_nb boolean,
    am_empl_hll_nb boolean,
    am_hll_shon boolean,
    am_periode_exploit boolean,
    am_exist_agrand boolean,
    am_exist_date boolean,
    am_exist_num boolean,
    am_exist_nb_avant boolean,
    am_exist_nb_apres boolean,
    am_coupe_bois boolean,
    am_coupe_parc boolean,
    am_coupe_align boolean,
    am_coupe_ess boolean,
    am_coupe_age boolean,
    am_coupe_dens boolean,
    am_coupe_qual boolean,
    am_coupe_trait boolean,
    am_coupe_autr boolean,
    co_archi_recours boolean,
    co_cstr_nouv boolean,
    co_cstr_exist boolean,
    co_modif_aspect boolean,
    co_modif_struct boolean,
    co_cloture boolean,
    co_ouvr_elec boolean,
    co_elec_tension boolean,
    co_ouvr_infra boolean,
    co_trx_imm boolean,
    co_div_terr boolean,
    co_projet_desc boolean,
    co_cstr_shob boolean,
    co_anx_pisc boolean,
    co_anx_gara boolean,
    co_anx_veran boolean,
    co_anx_abri boolean,
    co_anx_autr boolean,
    co_anx_autr_desc boolean,
    co_tot_log_nb boolean,
    co_tot_ind_nb boolean,
    co_tot_coll_nb boolean,
    co_mais_piece_nb boolean,
    co_mais_niv_nb boolean,
    co_fin_lls_nb boolean,
    co_fin_aa_nb boolean,
    co_fin_ptz_nb boolean,
    co_fin_autr_nb boolean,
    co_fin_autr_desc boolean,
    co_finan1_id boolean,
    co_finan1_nb boolean,
    co_finan2_id boolean,
    co_finan2_nb boolean,
    co_finan3_id boolean,
    co_finan3_nb boolean,
    co_finan4_id boolean,
    co_finan4_nb boolean,
    co_finan5_id boolean,
    co_finan5_nb boolean,
    co_mais_contrat_ind boolean,
    co_uti_pers boolean,
    co_uti_vente boolean,
    co_uti_loc boolean,
    co_uti_princ boolean,
    co_uti_secon boolean,
    co_resid_agees boolean,
    co_resid_etud boolean,
    co_resid_tourism boolean,
    co_resid_hot_soc boolean,
    co_resid_soc boolean,
    co_resid_hand boolean,
    co_resid_autr boolean,
    co_resid_autr_desc boolean,
    co_foyer_chamb_nb boolean,
    co_log_1p_nb boolean,
    co_log_2p_nb boolean,
    co_log_3p_nb boolean,
    co_log_4p_nb boolean,
    co_log_5p_nb boolean,
    co_log_6p_nb boolean,
    co_bat_niv_nb boolean,
    co_trx_exten boolean,
    co_trx_surelev boolean,
    co_trx_nivsup boolean,
    co_trx_amgt boolean,
    co_demont_periode boolean,
    co_sp_transport boolean,
    co_sp_enseign boolean,
    co_sp_act_soc boolean,
    co_sp_ouvr_spe boolean,
    co_sp_sante boolean,
    co_sp_culture boolean,
    co_statio_avt_nb boolean,
    co_statio_apr_nb boolean,
    co_statio_avt_shob boolean,
    co_statio_apr_shob boolean,
    co_statio_avt_surf boolean,
    co_statio_apr_surf boolean,
    co_statio_adr boolean,
    co_statio_place_nb boolean,
    co_statio_tot_surf boolean,
    co_statio_tot_shob boolean,
        co_statio_comm_cin_surf boolean,
    tab_surface integer,
    dm_constr_dates boolean,
    dm_total boolean,
    dm_partiel boolean,
    dm_projet_desc boolean,
    dm_tot_log_nb boolean,
    tax_surf_tot boolean,
    tax_surf boolean,
    tax_surf_suppr_mod boolean, 
    tax_dest_loc_tr boolean,
    tab_tax_su_princ integer,
    tab_tax_su_heber integer,
    tab_tax_su_secon integer,
    tab_tax_su_tot integer,

    tax_ext_pret boolean,
    tax_ext_desc boolean,
    tax_surf_tax_exist_cons boolean,
    tax_log_exist_nb boolean,


    tax_trx_presc_ppr boolean,
    tax_monu_hist boolean,
    tax_comm_nb boolean,
    tab_tax_su_non_habit_surf integer,
    tab_tax_am integer,

    vsd_surf_planch_smd boolean,
    vsd_unit_fonc_sup boolean,
    vsd_unit_fonc_constr_sup boolean,
    vsd_val_terr boolean,
    vsd_const_sxist_non_dem_surf boolean,
    vsd_rescr_fisc boolean,

    pld_val_terr boolean,
    pld_const_exist_dem boolean,
    pld_const_exist_dem_surf boolean,

    code_cnil boolean
);

ALTER TABLE ONLY cerfa
    ADD CONSTRAINT cerfa_pkey PRIMARY KEY (cerfa);

CREATE SEQUENCE cerfa_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_cerfa_fkey 
    FOREIGN KEY (cerfa) REFERENCES cerfa(cerfa);

ALTER TABLE ONLY dossier_instruction_type
    ADD CONSTRAINT dossier_instruction_type_cerfa_lot_fkey 
    FOREIGN KEY (cerfa_lot) REFERENCES cerfa(cerfa);

-- Table donnees_techniques
CREATE TABLE donnees_techniques (
        donnees_techniques integer,
        dossier_instruction character varying(20),
        lot integer,
    avap_co_elt_pro boolean,
    avap_nouv_haut_surf boolean,
    avap_co_clot boolean,
    avap_aut_coup_aba_arb boolean,
    avap_ouv_infra boolean,
    avap_aut_inst_mob boolean,
    avap_aut_plant boolean,
    avap_aut_auv_elec boolean,
    tr_total boolean,
    tr_partiel boolean,
    tr_desc text,
    am_lotiss boolean,
    am_autre_div boolean,
    am_camping boolean,
    am_caravane boolean,
    am_carav_duree integer,
    am_statio boolean,
    am_statio_cont integer,
    am_affou_exhau boolean,
        am_affou_exhau_sup numeric,
    am_affou_prof numeric,
    am_exhau_haut numeric,
    am_coupe_abat boolean,
    am_prot_plu boolean,
    am_prot_muni boolean,
    am_mobil_voyage boolean,
    am_voyage_deb date,
    am_voyage_fin date,
    am_aire_voyage boolean,
    am_rememb_afu boolean,
    am_parc_resid_loi boolean,
    am_sport_moto boolean,
    am_sport_attrac boolean,
    am_sport_golf boolean,
    am_mob_art boolean,
    am_modif_voie_esp boolean,
    am_plant_voie_esp boolean,
    am_chem_ouv_esp boolean,
    am_agri_peche boolean,
    am_crea_voie boolean,
    am_modif_voie_exist boolean,
    am_crea_esp_sauv boolean,
    am_modif_amgt boolean,
    am_crea_esp_class boolean,
    am_projet_desc text,
    am_terr_surf numeric,
    am_tranche_desc text,
    am_lot_max_nb integer,
    am_lot_max_shon numeric,
    am_lot_max_shob numeric,
    am_lot_cstr_cos boolean,
    am_lot_cstr_plan boolean,
    am_lot_cstr_vente boolean,
    am_lot_fin_diff boolean,
    am_lot_consign boolean,
    am_lot_gar_achev boolean,
    am_lot_vente_ant boolean,
    am_empl_nb integer,
    am_tente_nb integer,
    am_carav_nb integer,
    am_mobil_nb integer,
    am_pers_nb integer,
    am_empl_hll_nb integer,
    am_hll_shon numeric,
    am_periode_exploit text,
    am_exist_agrand boolean,
    am_exist_date date,
    am_exist_num character varying(100),
    am_exist_nb_avant integer,
    am_exist_nb_apres integer,
    am_coupe_bois boolean,
    am_coupe_parc boolean,
    am_coupe_align boolean,
    am_coupe_ess character varying(100),
    am_coupe_age character varying(15),
    am_coupe_dens character varying(100),
    am_coupe_qual character varying(100),
    am_coupe_trait character varying(100),
    am_coupe_autr character varying(100),
    co_archi_recours boolean,
    co_cstr_nouv boolean,
    co_cstr_exist boolean,
    co_modif_aspect boolean,
    co_modif_struct boolean,
    co_cloture boolean,
    co_ouvr_elec boolean,
    co_elec_tension numeric,
    co_ouvr_infra boolean,
    co_trx_imm boolean,
    co_div_terr boolean,
    co_projet_desc text,
    co_cstr_shob numeric,
    co_anx_pisc boolean,
    co_anx_gara boolean,
    co_anx_veran boolean,
    co_anx_abri boolean,
    co_anx_autr boolean,
    co_anx_autr_desc text,
    co_tot_log_nb integer,
    co_tot_ind_nb integer,
    co_tot_coll_nb integer,
    co_mais_piece_nb integer,
    co_mais_niv_nb integer,
    co_fin_lls_nb integer,
    co_fin_aa_nb integer,
    co_fin_ptz_nb integer,
    co_fin_autr_nb integer,
    co_fin_autr_desc text,
    co_finan1_id integer,
    co_finan1_nb integer,
    co_finan2_id integer,
    co_finan2_nb integer,
    co_finan3_id integer,
    co_finan3_nb integer,
    co_finan4_id integer,
    co_finan4_nb integer,
    co_finan5_id integer,
    co_finan5_nb integer,
    co_mais_contrat_ind boolean,
    co_uti_pers boolean,
    co_uti_vente boolean,
    co_uti_loc boolean,
    co_uti_princ boolean,
    co_uti_secon boolean,
    co_resid_agees boolean,
    co_resid_etud boolean,
    co_resid_tourism boolean,
    co_resid_hot_soc boolean,
    co_resid_soc boolean,
    co_resid_hand boolean,
    co_resid_autr boolean,
    co_resid_autr_desc text,
    co_foyer_chamb_nb integer,
    co_log_1p_nb integer,
    co_log_2p_nb integer,
    co_log_3p_nb integer,
    co_log_4p_nb integer,
    co_log_5p_nb integer,
    co_log_6p_nb integer,
    co_bat_niv_nb integer,
    co_trx_exten boolean,
    co_trx_surelev boolean,
    co_trx_nivsup boolean,
    co_trx_amgt boolean,
    co_demont_periode text,
    co_sp_transport boolean,
    co_sp_enseign boolean,
    co_sp_act_soc boolean,
    co_sp_ouvr_spe boolean,
    co_sp_sante boolean,
    co_sp_culture boolean,
    co_statio_avt_nb integer,
    co_statio_apr_nb integer,
    co_statio_avt_shob numeric,
    co_statio_apr_shob numeric,
    co_statio_avt_surf numeric,
    co_statio_apr_surf numeric,
    co_statio_adr text,
    co_statio_place_nb integer,
    co_statio_tot_surf numeric,
    co_statio_tot_shob numeric,
        co_statio_comm_cin_surf numeric,
    su_avt_shon1 numeric,
    su_avt_shon2 numeric,
    su_avt_shon3 numeric,
    su_avt_shon4 numeric,
    su_avt_shon5 numeric,
    su_avt_shon6 numeric,
    su_avt_shon7 numeric,
    su_avt_shon8 numeric,
    su_avt_shon9 numeric,
    su_cstr_shon1 numeric,
    su_cstr_shon2 numeric,
    su_cstr_shon3 numeric,
    su_cstr_shon4 numeric,
    su_cstr_shon5 numeric,
    su_cstr_shon6 numeric,
    su_cstr_shon7 numeric,
    su_cstr_shon8 numeric,
    su_cstr_shon9 numeric,
    su_trsf_shon1 numeric,
    su_trsf_shon2 numeric,
    su_trsf_shon3 numeric,
    su_trsf_shon4 numeric,
    su_trsf_shon5 numeric,
    su_trsf_shon6 numeric,
    su_trsf_shon7 numeric,
    su_trsf_shon8 numeric,
    su_trsf_shon9 numeric,
    su_chge_shon1 numeric,
    su_chge_shon2 numeric,
    su_chge_shon3 numeric,
    su_chge_shon4 numeric,
    su_chge_shon5 numeric,
    su_chge_shon6 numeric,
    su_chge_shon7 numeric,
    su_chge_shon8 numeric,
    su_chge_shon9 numeric,
    su_demo_shon1 numeric,
    su_demo_shon2 numeric,
    su_demo_shon3 numeric,
    su_demo_shon4 numeric,
    su_demo_shon5 numeric,
    su_demo_shon6 numeric,
    su_demo_shon7 numeric,
    su_demo_shon8 numeric,
    su_demo_shon9 numeric,
    su_sup_shon1 numeric,
    su_sup_shon2 numeric,
    su_sup_shon3 numeric,
    su_sup_shon4 numeric,
    su_sup_shon5 numeric,
    su_sup_shon6 numeric,
    su_sup_shon7 numeric,
    su_sup_shon8 numeric,
    su_sup_shon9 numeric,
    su_tot_shon1 numeric,
    su_tot_shon2 numeric,
    su_tot_shon3 numeric,
    su_tot_shon4 numeric,
    su_tot_shon5 numeric,
    su_tot_shon6 numeric,
    su_tot_shon7 numeric,
    su_tot_shon8 numeric,
    su_tot_shon9 numeric,
    su_avt_shon_tot numeric,
    su_cstr_shon_tot numeric,
    su_trsf_shon_tot numeric,
    su_chge_shon_tot numeric,
    su_demo_shon_tot numeric,
    su_sup_shon_tot numeric,
    su_tot_shon_tot numeric,
    dm_constr_dates text,
    dm_total boolean,
    dm_partiel boolean,
    dm_projet_desc text,
    dm_tot_log_nb integer,
    tax_surf_tot numeric,
    tax_surf numeric,
    tax_surf_suppr_mod numeric, 
    tax_dest_loc_tr text,

    tax_su_princ_log_nb1 numeric,
    tax_su_princ_log_nb2 numeric,
    tax_su_princ_log_nb3 numeric,
    tax_su_princ_log_nb4 numeric,
    tax_su_princ_log_nb_tot1 numeric,
    tax_su_princ_log_nb_tot2 numeric,
    tax_su_princ_log_nb_tot3 numeric,
    tax_su_princ_log_nb_tot4 numeric,
    tax_su_princ_surf1 numeric,
    tax_su_princ_surf2 numeric,
    tax_su_princ_surf3 numeric,
    tax_su_princ_surf4 numeric,
    tax_su_princ_surf_sup1 numeric,
    tax_su_princ_surf_sup2 numeric,
    tax_su_princ_surf_sup3 numeric,
    tax_su_princ_surf_sup4 numeric,

    tax_su_heber_log_nb1 integer,
    tax_su_heber_log_nb2 integer,
    tax_su_heber_log_nb3 integer,
    tax_su_heber_log_nb_tot1 integer,
    tax_su_heber_log_nb_tot2 integer,
    tax_su_heber_log_nb_tot3 integer,
    tax_su_heber_surf1 numeric,
    tax_su_heber_surf2 numeric,
    tax_su_heber_surf3 numeric,
    tax_su_heber_surf_sup1 numeric,
    tax_su_heber_surf_sup2 numeric,
    tax_su_heber_surf_sup3 numeric,

    tax_su_secon_log_nb integer,
    tax_su_tot_log_nb integer,
    tax_su_secon_log_nb_tot integer,
    tax_su_tot_log_nb_tot integer,
    tax_su_secon_surf numeric,
    tax_su_tot_surf numeric,
    tax_su_secon_surf_sup numeric,
    tax_su_tot_surf_sup numeric,

    tax_ext_pret boolean,
    tax_ext_desc text,
    tax_surf_tax_exist_cons numeric,
    tax_log_exist_nb integer,

    tax_am_statio_ext integer,
    tax_sup_bass_pisc numeric,
    tax_empl_ten_carav_mobil_nb integer,
    tax_empl_hll_nb integer,
    tax_eol_haut_nb integer,
    tax_pann_volt_sup numeric,

    tax_am_statio_ext_sup integer,
    tax_sup_bass_pisc_sup numeric,
    tax_empl_ten_carav_mobil_nb_sup integer,
    tax_empl_hll_nb_sup integer,
    tax_eol_haut_nb_sup integer,
    tax_pann_volt_sup_sup numeric,

    tax_trx_presc_ppr boolean,
    tax_monu_hist boolean,

    tax_comm_nb integer,

    tax_su_non_habit_surf1 numeric,
    tax_su_non_habit_surf2 numeric,
    tax_su_non_habit_surf3 numeric,
    tax_su_non_habit_surf4 numeric,
    tax_su_non_habit_surf5 numeric,
    tax_su_non_habit_surf6 numeric,
    tax_su_non_habit_surf7 numeric,
    tax_su_non_habit_surf_sup1 numeric,
    tax_su_non_habit_surf_sup2 numeric,
    tax_su_non_habit_surf_sup3 numeric,
    tax_su_non_habit_surf_sup4 numeric,
    tax_su_non_habit_surf_sup5 numeric,
    tax_su_non_habit_surf_sup6 numeric,
    tax_su_non_habit_surf_sup7 numeric,

    vsd_surf_planch_smd boolean,
    vsd_unit_fonc_sup numeric,
    vsd_unit_fonc_constr_sup numeric,
    vsd_val_terr numeric,
    vsd_const_sxist_non_dem_surf numeric,
    vsd_rescr_fisc date,

    pld_val_terr numeric,
    pld_const_exist_dem boolean,
    pld_const_exist_dem_surf numeric,

    code_cnil boolean
);

ALTER TABLE ONLY donnees_techniques
    ADD CONSTRAINT donnees_techniques_pkey PRIMARY KEY (donnees_techniques);
ALTER TABLE ONLY donnees_techniques
    ADD CONSTRAINT donnees_techniques_dossier_instruction_fkey FOREIGN KEY (dossier_instruction) REFERENCES dossier(dossier);
ALTER TABLE ONLY donnees_techniques
    ADD CONSTRAINT donnees_techniques_lot_fkey FOREIGN KEY (lot) REFERENCES lot(lot);

CREATE SEQUENCE donnees_techniques_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Table etat_dossier_autorisation
CREATE TABLE etat_dossier_autorisation (
    etat_dossier_autorisation integer,
    libelle character varying(100)
);

ALTER TABLE ONLY etat_dossier_autorisation
    ADD CONSTRAINT etat_dossier_autorisation_pkey PRIMARY KEY (etat_dossier_autorisation);

CREATE SEQUENCE etat_dossier_autorisation_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE dossier_autorisation DROP COLUMN etat;
ALTER TABLE dossier_autorisation ADD COLUMN etat_dossier_autorisation integer;

ALTER TABLE ONLY dossier_autorisation
    ADD CONSTRAINT dossier_autorisation_etat_dossier_autorisation_fkey FOREIGN KEY (etat_dossier_autorisation) REFERENCES etat_dossier_autorisation(etat_dossier_autorisation);


ALTER TABLE action ADD COLUMN methode_trigger character varying(250);

ALTER TABLE instruction ADD COLUMN numero_arrete character varying(100);

-- Suppression des tables Statistique et Destination_shon
--Suppression des contraintes
ALTER TABLE destination_shon DROP CONSTRAINT destination_shon_pkey;
ALTER TABLE destination_shon DROP CONSTRAINT destination_shon_destination_fkey;
ALTER TABLE destination_shon DROP CONSTRAINT destination_shon_dossier_fkey;

-- Suppression des données inutiles
DELETE FROM om_droit WHERE om_droit = 72 ;
DELETE FROM om_sousetat WHERE om_sousetat = 4 ;
UPDATE om_etat SET sousetat='instruction
consultation
blocnote
terrain'
WHERE om_etat = 7;

-- Suppression de la séquence
DROP SEQUENCE destination_shon_seq ;

-- Suppression de la table
DROP TABLE destination_shon;

-- Suppression des champs de la table dossier

ALTER TABLE dossier DROP COLUMN demandeur_categorie;
ALTER TABLE dossier DROP COLUMN demandeur_civilite;
ALTER TABLE dossier DROP COLUMN demandeur_nom;
ALTER TABLE dossier DROP COLUMN demandeur_societe;
ALTER TABLE dossier DROP COLUMN demandeur_adresse;
ALTER TABLE dossier DROP COLUMN demandeur_adresse_complement;
ALTER TABLE dossier DROP COLUMN demandeur_cp;
ALTER TABLE dossier DROP COLUMN demandeur_ville;
ALTER TABLE dossier DROP COLUMN demandeur_pays;
ALTER TABLE dossier DROP COLUMN demandeur_email;
ALTER TABLE dossier DROP COLUMN demandeur_telephone;
ALTER TABLE dossier DROP COLUMN delegataire;
ALTER TABLE dossier DROP COLUMN delegataire_civilite;
ALTER TABLE dossier DROP COLUMN delegataire_nom;
ALTER TABLE dossier DROP COLUMN delegataire_societe;
ALTER TABLE dossier DROP COLUMN delegataire_adresse;
ALTER TABLE dossier DROP COLUMN delegataire_adresse_complement;
ALTER TABLE dossier DROP COLUMN delegataire_cp;
ALTER TABLE dossier DROP COLUMN delegataire_ville;
ALTER TABLE dossier DROP COLUMN delegataire_pays;
ALTER TABLE dossier DROP COLUMN delegataire_email;
ALTER TABLE dossier DROP COLUMN delegataire_telephone;
ALTER TABLE dossier DROP COLUMN architecte;
ALTER TABLE dossier DROP COLUMN travaux;
ALTER TABLE dossier DROP COLUMN travaux_complement;
ALTER TABLE dossier DROP COLUMN terrain_numero;
ALTER TABLE dossier DROP COLUMN terrain_numero_complement;
ALTER TABLE dossier DROP COLUMN rivoli;
ALTER TABLE dossier DROP COLUMN terrain_adresse;
ALTER TABLE dossier DROP COLUMN terrain_adresse_complement;
ALTER TABLE dossier DROP COLUMN terrain_cp;
ALTER TABLE dossier DROP COLUMN terrain_ville;
ALTER TABLE dossier DROP COLUMN terrain_surface;
ALTER TABLE dossier DROP COLUMN terrain_surface_calcul;

-- Statistique
-- Suppression de la séquence
DROP SEQUENCE statistique_seq ;

-- Suppression de la table
DROP TABLE statistique;