--
--  START - Evolution Marseille - #589 — 2.4 - Historique des parcelles 
--

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'QUALIFICATEUR')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'QUALIFICATEUR')
    );
--
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'CELLULE SUIVI')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'CELLULE SUIVI')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'VISUALISATION DA et DI')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'VISUALISATION DA et DI')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'DIVISIONNAIRE')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab',(SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR FONCTIONNEL')
WHERE
    NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR FONCTIONNEL')
    );
INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab', (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET ET SUIVI')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET ET SUIVI')
    );

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
    );

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab', (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
    );

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab', (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT')
    );

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab', (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR SERVICE')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR SERVICE')
    );

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'dossier_autorisation_display_da_di_tab', (SELECT om_profil FROM om_profil WHERE libelle = 'SERVICE CONSULTÉ INTERNE')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'dossier_autorisation_display_da_di_tab' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'SERVICE CONSULTÉ INTERNE')
    );

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_avis_fichier_telecharger', 
    (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR SERVICE')
        WHERE NOT EXISTS (
            SELECT om_droit FROM om_droit WHERE libelle = 'demande_avis_fichier_telecharger' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR SERVICE')
        );

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_avis_fichier_telecharger', 
    (SELECT om_profil FROM om_profil WHERE libelle = 'SERVICE CONSULTÉ')
        WHERE NOT EXISTS (
            SELECT om_droit FROM om_droit WHERE libelle = 'demande_avis_fichier_telecharger' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'SERVICE CONSULTÉ')
        );

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'demande_avis_fichier_telecharger', 
    (SELECT om_profil FROM om_profil WHERE libelle = 'SERVICE CONSULTÉ INTERNE')
        WHERE NOT EXISTS (
            SELECT om_droit FROM om_droit WHERE libelle = 'demande_avis_fichier_telecharger' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'SERVICE CONSULTÉ INTERNE')
        );

--
--  END - Evolution Marseille - #589 — 2.4 - Historique des parcelles 
--

--
-- START - #422 — [EVOLUTION] #883 — #588 — 2.3 - Modification de l'édition des planches RAR
--

-- Ajout de la table phase, liée éventuellement à un événement

CREATE TABLE phase (
    phase integer NOT NULL,
    code character varying(15) NOT NULL,
    om_validite_debut date NULL,
    om_validite_fin date NULL
);
COMMENT ON COLUMN phase.phase IS 'Identifiant unique';
COMMENT ON COLUMN phase.code IS 'Code de phase composé de caractères alphanumériques';
COMMENT ON COLUMN phase.om_validite_debut IS 'Date de début de validité';
COMMENT ON COLUMN phase.om_validite_fin IS 'Date de fin de validité';
COMMENT ON TABLE phase IS 'Phase';
CREATE SEQUENCE phase_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE phase_seq OWNED BY phase.phase;
ALTER TABLE ONLY phase
    ADD CONSTRAINT phase_pkey PRIMARY KEY (phase);

-- Liaison avec evenement

ALTER TABLE evenement
    ADD phase integer NULL;
COMMENT ON COLUMN evenement.phase IS 'Phase';
ALTER TABLE ONLY evenement
    ADD CONSTRAINT evenement_phase_fkey FOREIGN KEY (phase) REFERENCES phase(phase);

--
-- END - #422 — [EVOLUTION] #883 — #588 — 2.3 - Modification de l'édition des planches RAR
--

--
-- START - #207 — Ajouter les champs de fusion concernant les AT pour les
-- consultations
--

--
UPDATE om_requete
SET requete = '
SELECT 
    --Coordonnées du service
    service.libelle as libelle_service, 
    service.adresse as adresse_service, 
    service.adresse2 as adresse2_service, 
    service.cp as cp_service, 
    service.ville as ville_service,
    service.delai as delai_service,
    CASE WHEN LOWER(service.delai_type) = LOWER(''jour'')
        THEN ''jour(s)''
        ELSE ''mois''
    END as delai_type_service,

    --Données générales du dossier d''instruction
    dossier.dossier_libelle as libelle_dossier,
    dossier.dossier as code_barres_dossier,
    replace(dossier.terrain_references_cadastrales, '';'', '' '') as terrain_references_cadastrales_dossier,
    dossier_autorisation_type_detaille.libelle as libelle_datd, 

    --Adresse du terrain du dossier d''instruction
    dossier.terrain_adresse_voie_numero as terrain_adresse_voie_numero_dossier,
    dossier.terrain_adresse_voie as terrain_adresse_voie_dossier,
    dossier.terrain_adresse_lieu_dit as terrain_adresse_lieu_dit_dossier,
    dossier.terrain_adresse_code_postal as terrain_adresse_code_postal_dossier,
    dossier.terrain_adresse_localite as terrain_adresse_localite_dossier,

    --Coordonnées de l''instructeur
    instructeur.nom as nom_instructeur,
    instructeur.telephone as tel_instructeur,

    --Coordonnées du demandeur
    civilite.libelle as civilite_demandeur,
    CASE WHEN demandeur.qualite=''particulier''
        THEN TRIM(CONCAT(demandeur.particulier_nom, '' '', demandeur.particulier_prenom))
        ELSE TRIM(CONCAT(demandeur.personne_morale_raison_sociale, '' '', demandeur.personne_morale_denomination))
    END as nom_demandeur,
    CONCAT(demandeur.numero, '' '', demandeur.voie) as adresse_demandeur,
    demandeur.complement as complement_adresse_demandeur,
    demandeur.lieu_dit as lieu_dit_demandeur,
    demandeur.code_postal as code_postal_demandeur,
    demandeur.localite as ville_demandeur,
    demandeur.personne_morale_denomination as societe_demandeur,

    -- Coordonnées du pétitionnaire principal
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT_WS('' '', petitionnaire_principal_civilite.libelle, petitionnaire_principal.particulier_nom, petitionnaire_principal.particulier_prenom))
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
                THEN TRIM(CONCAT_WS('' '', petitionnaire_principal.personne_morale_raison_sociale, petitionnaire_principal.personne_morale_denomination, ''représenté(e) par'', petitionnaire_principal_civilite.libelle, petitionnaire_principal.personne_morale_nom, petitionnaire_principal.personne_morale_prenom))
                ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
    END as nom_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite=''particulier'' OR petitionnaire_principal.personne_morale_nom IS NOT NULL OR petitionnaire_principal.personne_morale_prenom IS NOT NULL
        THEN petitionnaire_principal_civilite.libelle
        ELSE ''''
    END as civilite_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN petitionnaire_principal.particulier_nom
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_nom IS NOT NULL
                THEN petitionnaire_principal.personne_morale_nom
                ELSE ''''
            END
    END as nom_particulier_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN petitionnaire_principal.particulier_prenom
        ELSE
            CASE WHEN petitionnaire_principal.personne_morale_prenom IS NOT NULL
                THEN petitionnaire_principal.personne_morale_prenom
                ELSE ''''
            END
    END as prenom_particulier_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN ''''
        ELSE petitionnaire_principal.personne_morale_raison_sociale
    END as raison_sociale_petitionnaire_principal,
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN ''''
        ELSE petitionnaire_principal.personne_morale_denomination
    END as denomination_petitionnaire_principal,
    petitionnaire_principal.numero as numero_petitionnaire_principal,
    petitionnaire_principal.voie as voie_petitionnaire_principal,
    petitionnaire_principal.complement as complement_petitionnaire_principal,
    petitionnaire_principal.lieu_dit as lieu_dit_petitionnaire_principal,
    CASE 
        WHEN petitionnaire_principal.bp IS NULL
        THEN ''''
        ELSE CONCAT(''BP '', petitionnaire_principal.bp)
    END as bp_petitionnaire_principal,
    petitionnaire_principal.code_postal as code_postal_petitionnaire_principal,
    petitionnaire_principal.localite as localite_petitionnaire_principal,
    CASE 
        WHEN petitionnaire_principal.cedex IS NULL
        THEN ''''
        ELSE CONCAT(''CEDEX '', petitionnaire_principal.cedex)
    END as cedex_petitionnaire_principal,
    petitionnaire_principal.pays as pays_petitionnaire_principal,

    --Dates importantes du dossier d''instruction
    to_char(dossier.date_depot,''DD/MM/YYYY'') as date_depot_dossier, 
    to_char(dossier.date_rejet,''DD/MM/YYYY'') as date_rejet_dossier, 
    CASE WHEN dossier.incomplet_notifie IS TRUE AND dossier.incompletude IS TRUE 
        THEN to_char(dossier.date_limite_incompletude ,''DD/MM/YYYY'') 
        ELSE to_char(dossier.date_limite ,''DD/MM/YYYY'')
    END as date_limite_dossier, 
    to_char(consultation.date_envoi,''DD/MM/YYYY'') as date_envoi_dossier, 
    to_char(instruction.date_evenement,''DD/MM/YYYY'') as date_evenement,
    dossier.delai as delai_limite_decision,

    --Code barres de la consultation
    consultation.code_barres as code_barres_consultation,

    --Données générales des données techniques
    TRIM(CONCAT(
        donnees_techniques.co_projet_desc, '' '',
        donnees_techniques.am_projet_desc, '' '',
        donnees_techniques.dm_projet_desc, '' '',
        donnees_techniques.ope_proj_desc
        )) as projet_desc_donnees_techniques,
    donnees_techniques.am_lot_max_nb as am_lot_max_nb_donnees_techniques,
    donnees_techniques.am_lot_max_shon as am_lot_max_shon_donnees_techniques,
    donnees_techniques.su_cstr_shon_tot as su_cstr_shon_tot_donnees_techniques,
    donnees_techniques.su_demo_shon_tot as su_demo_shon_tot_donnees_techniques,
    REGEXP_REPLACE(CONCAT(
        CASE
            WHEN donnees_techniques.su_cstr_shon1 IS NULL
            THEN ''''
            ELSE CONCAT(''Habitation - '', donnees_techniques.su_cstr_shon1, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon2 IS NULL
            THEN ''''
            ELSE CONCAT(''Hébergement hôtelier - '', donnees_techniques.su_cstr_shon2, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon3 IS NULL
            THEN ''''
            ELSE CONCAT(''Bureaux - '', donnees_techniques.su_cstr_shon3, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon4 IS NULL
            THEN ''''
            ELSE CONCAT(''Commerce - '', donnees_techniques.su_cstr_shon4, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon5 IS NULL
            THEN ''''
            ELSE CONCAT(''Artisanat - '', donnees_techniques.su_cstr_shon5, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon6 IS NULL
            THEN ''''
            ELSE CONCAT(''Industrie - '', donnees_techniques.su_cstr_shon6, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon7 IS NULL
            THEN ''''
            ELSE CONCAT(''Exploitation agricole ou forestière - '', donnees_techniques.su_cstr_shon7, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_cstr_shon8 IS NULL
            THEN ''''
            ELSE CONCAT(''Entrepôt - '', donnees_techniques.su_cstr_shon8, '' m² / '')
        END, 
        CASE
            WHEN donnees_techniques.su_cstr_shon9 IS NULL
            THEN ''''
            ELSE CONCAT(''Service public ou d''''intérêt collectif - '', donnees_techniques.su_cstr_shon9, '' m²'')
        END
    ), '' / $'', '''') as tab_surface_donnees_techniques,
    donnees_techniques.co_tot_log_nb as co_tot_log_nb_donnees_techniques,
    donnees_techniques.co_statio_place_nb as co_statio_place_nb_donnees_techniques,
    donnees_techniques.su_tot_shon_tot as su_tot_shon_tot_donnees_techniques,

    -- Données techniques pour les AT
    CONCAT_WS('', '',
        CASE WHEN donnees_techniques.erp_cstr_neuve IS TRUE THEN ''construction neuve'' END,
        CASE WHEN donnees_techniques.erp_trvx_acc IS TRUE THEN ''travaux de mise en conformité totale aux règles d’accessibilité'' END,
        CASE WHEN donnees_techniques.erp_extension IS TRUE THEN ''extension'' END,
        CASE WHEN donnees_techniques.erp_rehab IS TRUE THEN ''réhabilitation'' END,
        CASE WHEN donnees_techniques.erp_trvx_am IS TRUE THEN ''travaux d’aménagement (remplacement de revêtements, rénovation électrique, création d’une rampe, par exemple)'' END,
        CASE WHEN donnees_techniques.erp_vol_nouv_exist IS TRUE THEN ''création de volumes nouveaux dans des volumes existants (modification du cloisonnement, par exemple)'' END
    ) as at_type_travaux,
    donnees_techniques.erp_public_eff_tot as at_effectif_public_total,
    CONCAT_WS('' - '', erp_categorie.libelle, erp_categorie.description) as at_categorie_etablissement,
    CONCAT_WS('' - '', erp_type.libelle, erp_type.description) as at_type_etablissement
    
FROM 

    &DB_PREFIXEconsultation 
    LEFT JOIN &DB_PREFIXEservice 
        ON service.service=consultation.service 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=consultation.dossier 
    LEFT JOIN &DB_PREFIXEdonnees_techniques 
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdossier_autorisation 
        ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation 
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur
    LEFT JOIN &DB_PREFIXEinstruction
        ON dossier.dossier=instruction.dossier 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=civilite.civilite
    LEFT JOIN &DB_PREFIXEdemandeur as petitionnaire_principal
        ON lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
    LEFT JOIN &DB_PREFIXEcivilite as petitionnaire_principal_civilite
        ON petitionnaire_principal.personne_morale_civilite=petitionnaire_principal_civilite.civilite OR petitionnaire_principal.particulier_civilite=petitionnaire_principal_civilite.civilite
    LEFT JOIN &DB_PREFIXEdossier_instruction_type 
        ON dossier.dossier_instruction_type=dossier_instruction_type.dossier_instruction_type 
    LEFT JOIN &DB_PREFIXEdossier_autorisation_type_detaille 
        ON dossier_instruction_type.dossier_autorisation_type_detaille=dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
    LEFT JOIN
        &DB_PREFIXEerp_categorie
        ON
            donnees_techniques.erp_class_cat = erp_categorie.erp_categorie
    LEFT JOIN
        &DB_PREFIXEerp_type
        ON
            donnees_techniques.erp_class_type = erp_type.erp_type
WHERE consultation.consultation = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
',
merge_fields = '
--Coordonnées du service
[libelle_service] 
[adresse_service] 
[adresse2_service] 
[cp_service]    [ville_service]
[delai_service]
[delai_type_service]

--Données générales du dossier d''instruction
[libelle_dossier]    [code_barres_dossier]    [terrain_references_cadastrales_dossier]
[libelle_datd] 

--Adresse du terrain du dossier d''instruction
[terrain_adresse_voie_numero_dossier]    [terrain_adresse_voie_dossier]
[terrain_adresse_lieu_dit_dossier]     [terrain_adresse_code_postal_dossier]
[terrain_adresse_localite_dossier]

--Coordonnées de l''instructeur
[nom_instructeur]
[tel_instructeur]

--Coordonnées du demandeur
[civilite_demandeur]    [nom_demandeur]
[adresse_demandeur]
[complement_adresse_demandeur]    [lieu_dit_demandeur]
[code_postal_demandeur]    [ville_demandeur]
[societe_demandeur]

--Coordonnées du pétitionnaire principal
[nom_petitionnaire_principal]
[civilite_petitionnaire_principal]
[nom_particulier_petitionnaire_principal]
[prenom_particulier_petitionnaire_principal]
[raison_sociale_petitionnaire_principal]
[denomination_petitionnaire_principal]
[numero_petitionnaire_principal]    [voie_petitionnaire_principal]    [complement_petitionnaire_principal]
[lieu_dit_petitionnaire_principal]    [bp_petitionnaire_principal]
[code_postal_petitionnaire_principal]    [localite_petitionnaire_principal]    [cedex_petitionnaire_principal]
[pays_petitionnaire_principal]

--Dates importantes du dossier d''instruction
[date_depot_dossier]
[date_rejet_dossier]
[date_limite_dossier]
[date_envoi_dossier]
[date_evenement]
[delai_limite_decision]

--Code barres de la consultation
[code_barres_consultation]

--Données générales des données techniques
[projet_desc_donnees_techniques]
[co_tot_log_nb_donnees_techniques]    [co_station_place_nb_donnees_techniques]
[am_lot_max_nb_donnees_techniques]    [am_lot_max_shon_donnees_techniques]
[su_cstr_shon_tot_donnees_techniques]    [su_demo_shon_tot_donnees_techniques]
[tab_surface_donnees_techniques]
[su_tot_shon_tot_donnees_techniques]

--Données techniques des AT
[at_type_travaux]
[at_effectif_public_total]
[at_categorie_etablissement]
[at_type_etablissement]
'
WHERE code = 'consultation';

--
-- END - #207 — Ajouter les champs de fusion concernant les AT pour les
-- consultations
--

--
-- BEGIN - #175 — La date de notification de délai doit être mise à jour à la complétude 
--

-- Augmentation de la taille du champ restriction d'un événement
ALTER TABLE evenement
    ALTER restriction TYPE character varying(255);

-- Nouveau champ instruction récupéré du DI : date de dépôt
ALTER TABLE instruction
    ADD date_depot date NULL;
COMMENT ON COLUMN instruction.date_depot IS 'Date du dépôt du dossier d''instruction';

--
-- END - #175 — La date de notification de delai doit être mise à jour à la complétude 
--

--
-- BEGIN - I#434 — Loguer toutes les instructions d'un dossier 
--

-- Ajout de la colonne
ALTER TABLE dossier
    ADD log_instructions text NULL;
COMMENT ON COLUMN dossier.log_instructions IS 'Log des événements d''instruction du dossier d''instruction en cours';

--
-- END - I#434 — Loguer toutes les instructions d'un dossier 
--

--
--  START - Evolution Marseille - #589 — 2.5 - Mise à jour des dates de suivi
--

INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'),'instruction_modification_dates', (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET ET SUIVI')
WHERE
    NOT EXISTS ( 
       SELECT om_droit FROM om_droit WHERE libelle = 'instruction_modification_dates' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET ET SUIVI')
    );

--
--  END - Evolution Marseille - #589 — 2.5 - Mise à jour des dates de suivi
--


--
-- BEGIN / TI#447 — Modification des "sous etat" 16, 17 et 18
--

-- om_sousetat 16 commission_proposition_ordre_jour
UPDATE om_sousetat SET
om_sql = '
SELECT
    row_number() OVER(ORDER BY division.chef ASC,dossier_commission.dossier) cel1,
    CONCAT(
        ''Dossier :        '', dossier.dossier_libelle, ''      '', ''Rap. '', division.chef, ''
'',
        ''Demandeur : '', CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(demandeur.particulier_nom, '' '', demandeur.particulier_prenom) WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, '' '', demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale) END,'' 
'', ''                      '',CONCAT(demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite) , ''
'',

        ''Adresse'',''
'',''Travaux :       '', TRIM(CONCAT(dossier.terrain_adresse_voie_numero,'' '',dossier.terrain_adresse_voie,'' '', dossier.terrain_adresse_lieu_dit,'' '', dossier.terrain_adresse_code_postal)),''
'',      ''Quartier :       '', quartier.libelle,''
'',
        ''Opération :    '', CONCAT(donnees_techniques.am_projet_desc, donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc), ''
'',
        ''Architecte :    '', CONCAT(architecte.nom,'' '',architecte.prenom),''
'',
''
'',
        ''Dépot : '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''        Délai : '', to_char(dossier.date_limite,''DD/MM/YYYY''), ''       Etat : '', etat.libelle, ''
'',
        ''SP : '',donnees_techniques.su_tot_shon_tot,'' m²'',''    Nbr lots :  '',donnees_techniques.am_lot_max_nb,''          Nbr parkings : '', donnees_techniques.co_statio_apr_nb,'' + '',donnees_techniques.co_statio_place_nb, '' hors terrain'',''
'',
        ''Nbr logts : '', donnees_techniques.co_tot_log_nb , '' -->'','' Typologie : '', donnees_techniques.co_log_1p_nb,'' T1,   '', donnees_techniques.co_log_2p_nb, '' T2,   '', donnees_techniques.co_log_3p_nb, '' T3,   '', donnees_techniques.co_log_4p_nb,'' T4,   '', donnees_techniques.co_log_5p_nb, '' T5,   '', donnees_techniques.co_log_6p_nb,''T6 '')as cel2,
    dossier_commission.motivation as motivation
FROM
    &DB_PREFIXEcommission
LEFT JOIN
    &DB_PREFIXEdossier_commission
    ON
        commission.commission=dossier_commission.commission
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        dossier_commission.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
        AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur
    ON
        lien_dossier_demandeur.demandeur = demandeur.demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        demandeur.particulier_civilite = civilite.civilite OR demandeur.personne_morale_civilite = civilite.civilite
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
        dossier.dossier = donnees_techniques.dossier_instruction
LEFT JOIN
    &DB_PREFIXEquartier
    ON
        dossier.quartier = quartier.quartier
LEFT JOIN 
    &DB_PREFIXEdivision 
    ON 
    instructeur.division = division.division
LEFT JOIN
    &DB_PREFIXEarchitecte
    ON
    donnees_techniques.architecte = architecte.architecte
LEFT JOIN
    &DB_PREFIXEetat
    ON
    dossier.etat = etat.etat
WHERE
    commission.commission = &idx
'
WHERE om_sousetat = 16;

-- om_sousetat 17 commission_ordre_jour
UPDATE om_sousetat SET
om_sql = '
SELECT
    row_number() OVER(ORDER BY division.chef ASC,dossier_commission.dossier) cel1,
    CONCAT(
        ''Dossier :        '', dossier.dossier_libelle, ''      '', ''Rap. '', division.chef, ''
'',
        ''Demandeur : '', CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(demandeur.particulier_nom, '' '', demandeur.particulier_prenom) WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, '' '', demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale) END,'' 
'', ''                      '',CONCAT(demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite) , ''
'',

        ''Adresse'',''
'',''Travaux :       '', TRIM(CONCAT(dossier.terrain_adresse_voie_numero,'' '',dossier.terrain_adresse_voie,'' '', dossier.terrain_adresse_lieu_dit,'' '', dossier.terrain_adresse_code_postal)),''
'',      ''Quartier :       '', quartier.libelle,''
'',
        ''Opération :    '', CONCAT(donnees_techniques.am_projet_desc, donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc), ''
'',
        ''Architecte :    '', CONCAT(architecte.nom,'' '',architecte.prenom),''
'',
''
'',
        ''Dépot : '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''        Délai : '', to_char(dossier.date_limite,''DD/MM/YYYY''), ''       Etat : '', etat.libelle, ''
'',
        ''SP : '',donnees_techniques.su_tot_shon_tot,'' m²'',''    Nbr lots :  '',donnees_techniques.am_lot_max_nb,''          Nbr parkings : '', donnees_techniques.co_statio_apr_nb,'' + '',donnees_techniques.co_statio_place_nb, '' hors terrain'',''
'',
        ''Nbr logts : '', donnees_techniques.co_tot_log_nb , '' -->'','' Typologie : '', donnees_techniques.co_log_1p_nb,'' T1,   '', donnees_techniques.co_log_2p_nb, '' T2,   '', donnees_techniques.co_log_3p_nb, '' T3,   '', donnees_techniques.co_log_4p_nb,'' T4,   '', donnees_techniques.co_log_5p_nb, '' T5,   '', donnees_techniques.co_log_6p_nb,''T6 '')as cel2,
       '''' as motivation
FROM
    &DB_PREFIXEcommission
LEFT JOIN
    &DB_PREFIXEdossier_commission
    ON
        commission.commission=dossier_commission.commission
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        dossier_commission.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
        AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur
    ON
        lien_dossier_demandeur.demandeur = demandeur.demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        demandeur.particulier_civilite = civilite.civilite OR demandeur.personne_morale_civilite = civilite.civilite
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
        dossier.dossier = donnees_techniques.dossier_instruction
LEFT JOIN
    &DB_PREFIXEquartier
    ON
        dossier.quartier = quartier.quartier
LEFT JOIN 
    &DB_PREFIXEdivision 
    ON 
    instructeur.division = division.division
LEFT JOIN
    &DB_PREFIXEarchitecte
    ON
    donnees_techniques.architecte = architecte.architecte
LEFT JOIN
    &DB_PREFIXEetat
    ON
    dossier.etat = etat.etat
WHERE
    commission.commission = &idx
'
WHERE om_sousetat = 17;

-- om_sousetat 18 commission_compte_rendu
UPDATE om_sousetat SET
om_sql = '
SELECT
    row_number() OVER(ORDER BY division.chef ASC,dossier_commission.dossier) cel1,
    CONCAT(
        ''Dossier :        '', dossier.dossier_libelle, ''      '', ''Rap. '', division.chef, ''
'',
        ''Demandeur : '', CASE WHEN demandeur.qualite=''particulier'' THEN CONCAT(demandeur.particulier_nom, '' '', demandeur.particulier_prenom) WHEN demandeur.qualite=''personne_morale'' THEN CONCAT(demandeur.personne_morale_nom, '' '', demandeur.personne_morale_prenom, '' '', demandeur.personne_morale_denomination, '' '', demandeur.personne_morale_raison_sociale) END,'' 
'', ''                      '',CONCAT(demandeur.numero, '' '', demandeur.voie, '' '', demandeur.complement, '' '', demandeur.lieu_dit, '' '', demandeur.code_postal, '' '', demandeur.localite) , ''
'',

        ''Adresse'',''
'',''Travaux :       '', TRIM(CONCAT(dossier.terrain_adresse_voie_numero,'' '',dossier.terrain_adresse_voie,'' '', dossier.terrain_adresse_lieu_dit,'' '', dossier.terrain_adresse_code_postal)),''
'',      ''Quartier :       '', quartier.libelle,''
'',
        ''Opération :    '', CONCAT(donnees_techniques.am_projet_desc, donnees_techniques.co_projet_desc, donnees_techniques.dm_projet_desc), ''
'',
        ''Architecte :    '', CONCAT(architecte.nom,'' '',architecte.prenom),''
'',
''
'',
        ''Dépot : '', to_char(dossier.date_depot,''DD/MM/YYYY''), ''        Délai : '', to_char(dossier.date_limite,''DD/MM/YYYY''), ''       Etat : '', etat.libelle, ''
'',
        ''SP : '',donnees_techniques.su_tot_shon_tot,'' m²'',''    Nbr lots :  '',donnees_techniques.am_lot_max_nb,''          Nbr parkings : '', donnees_techniques.co_statio_apr_nb,'' + '',donnees_techniques.co_statio_place_nb, '' hors terrain'',''
'',
        ''Nbr logts : '', donnees_techniques.co_tot_log_nb , '' -->'','' Typologie : '', donnees_techniques.co_log_1p_nb,'' T1,   '', donnees_techniques.co_log_2p_nb, '' T2,   '', donnees_techniques.co_log_3p_nb, '' T3,   '', donnees_techniques.co_log_4p_nb,'' T4,   '', donnees_techniques.co_log_5p_nb, '' T5,   '', donnees_techniques.co_log_6p_nb,''T6 '')as cel2,
    dossier_commission.avis as motivation
FROM
    &DB_PREFIXEcommission
LEFT JOIN
    &DB_PREFIXEdossier_commission
    ON
        commission.commission=dossier_commission.commission
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        dossier_commission.dossier = dossier.dossier
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
        AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur
    ON
        lien_dossier_demandeur.demandeur = demandeur.demandeur
LEFT JOIN
    &DB_PREFIXEcivilite
    ON
        demandeur.particulier_civilite = civilite.civilite OR demandeur.personne_morale_civilite = civilite.civilite
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
        dossier.dossier = donnees_techniques.dossier_instruction
LEFT JOIN
    &DB_PREFIXEquartier
    ON
        dossier.quartier = quartier.quartier
LEFT JOIN 
    &DB_PREFIXEdivision 
    ON 
    instructeur.division = division.division
LEFT JOIN
    &DB_PREFIXEarchitecte
    ON
    donnees_techniques.architecte = architecte.architecte
LEFT JOIN
    &DB_PREFIXEetat
    ON
    dossier.etat = etat.etat
WHERE
    commission.commission = &idx
'
WHERE om_sousetat = 18;

--
-- END / TI#447 — Modification des "sous etat" 16, 17 et 18
--
