--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.9.0-a3
--
-- XXX Ce fichier doit être renommé en v3.9.0-a3.sql au moment de la release
--
-- @package openfoncier
-- @version SVN : $Id: v3.9.0-a3.sql 2397 2013-10-11 15:18:10Z softime $
--------------------------------------------------------------------------------

---
--- Rends certains champs de demande_type obligatoire
--- XXX Risque d'erreur si les colonnes comportent déjà des champs à NULL
---
ALTER TABLE ONLY demande_type ALTER COLUMN code SET NOT NULL;
ALTER TABLE ONLY demande_type ALTER COLUMN libelle SET NOT NULL;
ALTER TABLE ONLY demande_type ALTER COLUMN groupe SET NOT NULL;
ALTER TABLE ONLY demande_type ALTER COLUMN demande_nature SET NOT NULL;

---
---Modification du champ dans les requêtes concernant les surfaces par 
---destination pour les editions du DI 
---
UPDATE om_requete SET requete = 'SELECT

    -- Données générales du dossier d''instruction
    dossier.dossier_libelle as libelle_dossier,
    dossier_autorisation.dossier_autorisation_libelle as libelle_da,

    dossier_autorisation_type_detaille.code as code_datd,
    dossier_autorisation_type_detaille.libelle as libelle_datd,
    dossier_autorisation_type.code as code_dat,
    dossier_autorisation_type.libelle as libelle_dat,
    dossier_instruction_type.code as code_dit,
    dossier_instruction_type.libelle as libelle_dit,
    dossier.delai as delai_dossier,
    replace(dossier.terrain_references_cadastrales, '';'', '' '') as terrain_references_cadastrales_dossier,

    avis_decision.libelle as libelle_avis_decision,

    -- Coordonnées de l''instructeur
    instructeur.nom as nom_instructeur,
    instructeur.telephone as telephone_instructeur,
    division.code as division_instructeur,
    om_utilisateur.email as email_instructeur,

    -- Noms des signataires
    division.chef as division_chef,
    direction.chef as direction_chef,

    -- 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_bp as terrain_adresse_bp_dossier,
    dossier.terrain_adresse_code_postal as terrain_adresse_code_postal_dossier,
    dossier.terrain_adresse_localite as terrain_adresse_localite_dossier,
    dossier.terrain_adresse_cedex as terrain_adresse_cedex_dossier,

    arrondissement.libelle as libelle_arrondissement,

    -- Coordonnées du pétitionnaire principal
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
    END as nom_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,
    petitionnaire_principal.bp as bp_petitionnaire_principal,
    petitionnaire_principal.code_postal as code_postal_petitionnaire_principal,
    petitionnaire_principal.localite as localite_petitionnaire_principal,
    petitionnaire_principal.cedex as cedex_petitionnaire_principal,
    petitionnaire_principal.pays as pays_petitionnaire_principal,

    -- Coordonnées du délégataire
    CASE
        WHEN delegataire.qualite=''particulier''
        THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
        ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
    END as nom_delegataire,
    delegataire.numero as numero_delegataire,
    delegataire.voie as voie_delegataire,
    delegataire.complement as complement_delegataire,
    delegataire.lieu_dit as lieu_dit_delegataire,
    delegataire.bp as bp_delegataire,
    delegataire.code_postal as code_postal_delegataire,
    delegataire.localite as ville_delegataire,
    delegataire.cedex as cedex_delegataire,
    delegataire.pays as pays_delegataire,

    -- CORRESPONDANT : destinataire du courrier. Il est le délégataire ou le pétitionnaire principal
    CASE
        WHEN delegataire.qualite IS NULL
        THEN
            CASE WHEN petitionnaire_principal.qualite=''particulier''
                    THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
                    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
        ELSE
            CASE WHEN delegataire.qualite=''particulier''
                THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
                ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
            END
    END as nom_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.numero
        ELSE delegataire.numero
    END as numero_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.voie
        ELSE delegataire.voie
    END as voie_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.complement
        ELSE delegataire.complement
    END as complement_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.lieu_dit
        ELSE delegataire.lieu_dit
    END as lieu_dit_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.bp
        ELSE delegataire.bp
    END as bp_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.code_postal
        ELSE delegataire.code_postal
    END as code_postal_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.localite
        ELSE delegataire.localite
    END as ville_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.cedex
        ELSE delegataire.cedex
    END as cedex_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.pays
        ELSE delegataire.pays
    END as pays_correspondant,

    -- Dates importantes du dossier d''instruction
    to_char(dossier.date_depot, ''DD/MM/YYYY'') as date_depot_dossier,
    to_char(dossier.date_decision, ''DD/MM/YYYY'') as date_decision_dossier,
    to_char(dossier.date_limite,''DD/MM/YYYY'') as date_limite_dossier,
    to_char(dossier.date_achevement,''DD/MM/YYYY'') as date_achevement_dossier,
    to_char(dossier.date_conformite,''DD/MM/YYYY'') as date_conformite_dossier,
    
    -- Données générales des données techniques
    donnees_techniques.co_projet_desc as co_projet_desc_donnees_techniques,
    donnees_techniques.am_projet_desc as am_projet_desc_donnees_techniques,
    donnees_techniques.dm_projet_desc as dm_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_trsf_shon1 IS NULL
            THEN ''''
            ELSE CONCAT(''Habitation - '', donnees_techniques.su_trsf_shon1, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon2 IS NULL
            THEN ''''
            ELSE CONCAT(''Hébergement hôtelier - '', donnees_techniques.su_trsf_shon2, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon3 IS NULL
            THEN ''''
            ELSE CONCAT(''Bureaux - '', donnees_techniques.su_trsf_shon3, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon4 IS NULL
            THEN ''''
            ELSE CONCAT(''Commerce - '', donnees_techniques.su_trsf_shon4, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon5 IS NULL
            THEN ''''
            ELSE CONCAT(''Artisanat - '', donnees_techniques.su_trsf_shon5, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon6 IS NULL
            THEN ''''
            ELSE CONCAT(''Industrie - '', donnees_techniques.su_trsf_shon6, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon7 IS NULL
            THEN ''''
            ELSE CONCAT(''Exploitation agricole ou forestière - '', donnees_techniques.su_trsf_shon7, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon8 IS NULL
            THEN ''''
            ELSE CONCAT(''Entrepôt - '', donnees_techniques.su_trsf_shon8, '' m² / '')
        END, 
        CASE
            WHEN donnees_techniques.su_trsf_shon9 IS NULL
            THEN ''''
            ELSE CONCAT(''Service public ou d''''intérêt collectif - '', donnees_techniques.su_trsf_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

FROM
    &DB_PREFIXEdossier
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        dossier.dossier = lien_dossier_demandeur.dossier
LEFT JOIN
    &DB_PREFIXEdemandeur as petitionnaire_principal
    ON
        lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur as delegataire
    ON
        lien_dossier_demandeur.demandeur = delegataire.demandeur AND delegataire.type_demandeur = ''delegataire''
LEFT JOIN
    &DB_PREFIXEdossier_instruction_type
    ON
        dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type
    ON
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXEom_utilisateur
    ON
        om_utilisateur.om_utilisateur = instructeur.om_utilisateur
LEFT JOIN
    &DB_PREFIXEdivision
    ON
        instructeur.division = division.division
LEFT JOIN
    &DB_PREFIXEdirection
    ON
        division.direction = direction.direction
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEavis_decision
    ON
        dossier.avis_decision = avis_decision.avis_decision
LEFT JOIN
    &DB_PREFIXEetat
    ON
        dossier.etat = etat.etat
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
    dossier.dossier = donnees_techniques.dossier_instruction
WHERE dossier.dossier = ''&idx''' WHERE code = 'dossier';
UPDATE om_requete SET requete = 'SELECT
    --Données générales du rapport d''instruction
    rapport_instruction.dossier_instruction as dossier_instruction_rapport_instruction, 
    analyse_reglementaire as analyse_reglementaire_rapport_instruction,
    description_projet as description_projet_rapport_instruction,
    proposition_decision as proposition_decision_rapport_instruction, 

    --Données générales du dossier d''instruction
    dossier.dossier_libelle as libelle_dossier, 
    etat as etat_dossier,
    pos as pos_dossier, 
    servitude as servitude_dossier, 
    dossier.delai as delai_dossier,
    dossier_autorisation_type_detaille.libelle as libelle_datd, 
    avis_decision.libelle as libelle_avis_decision,

    --Adresse du terrain dossier d''instruction
    terrain_adresse_voie_numero as terrain_adresse_voie_numero_dossier,
    dossier.terrain_adresse_voie as terrain_adresse_voie_dossier,
    terrain_adresse_lieu_dit as terrain_adresse_lieu_dit_dossier,
    terrain_adresse_localite as terrain_adresse_localite_dossier,
    terrain_adresse_code_postal as terrain_adresse_code_postal_dossier,
    terrain_adresse_bp as terrain_adresse_bp_dossier,
    terrain_adresse_cedex as terrain_adresse_cedex_dossier,
    terrain_superficie as terrain_superficie_dossier,
    replace(dossier.terrain_references_cadastrales, '';'', '' '') as terrain_references_cadastrales_dossier,
    
    --Coordonnées du demandeur
    civilite.code as code_civilite,
    particulier_nom as particulier_nom_demandeur,
    particulier_prenom as particulier_prenom_demandeur,
    personne_morale_denomination as personne_morale_denomination_demandeur,
    personne_morale_raison_sociale as personne_morale_raison_sociale_demandeur,
    personne_morale_siret as personne_morale_siret_demandeur,
    personne_morale_nom as personne_morale_nom_demandeur,
    personne_morale_prenom as personne_morale_prenom_demandeur,
    numero as numero_demandeur,
    voie as voie_demandeur,
    demandeur.complement as complement_demandeur,
    lieu_dit as lieu_dit_demandeur,
    localite as localite_demandeur,
    code_postal as code_postal_demandeur,
    bp as bp_demandeur,
    cedex as cedex_demandeur,
    
    --Nom de l''instructeur
    instructeur.nom as nom_instructeur, 

    --Noms des signataires
    division.chef as chef_division,
    direction.chef as chef_direction,

    --Données techniques
    donnees_techniques.co_projet_desc as co_projet_desc_donnees_techniques,
    donnees_techniques.am_projet_desc as am_projet_desc_donnees_techniques,
    donnees_techniques.dm_projet_desc as dm_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_trsf_shon1 IS NULL
            THEN ''''
            ELSE CONCAT(''Habitation - '', donnees_techniques.su_trsf_shon1, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon2 IS NULL
            THEN ''''
            ELSE CONCAT(''Hébergement hôtelier - '', donnees_techniques.su_trsf_shon2, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon3 IS NULL
            THEN ''''
            ELSE CONCAT(''Bureaux - '', donnees_techniques.su_trsf_shon3, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon4 IS NULL
            THEN ''''
            ELSE CONCAT(''Commerce - '', donnees_techniques.su_trsf_shon4, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon5 IS NULL
            THEN ''''
            ELSE CONCAT(''Artisanat - '', donnees_techniques.su_trsf_shon5, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon6 IS NULL
            THEN ''''
            ELSE CONCAT(''Industrie - '', donnees_techniques.su_trsf_shon6, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon7 IS NULL
            THEN ''''
            ELSE CONCAT(''Exploitation agricole ou forestière - '', donnees_techniques.su_trsf_shon7, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon8 IS NULL
            THEN ''''
            ELSE CONCAT(''Entrepôt - '', donnees_techniques.su_trsf_shon8, '' m² / '')
        END, 
        CASE
            WHEN donnees_techniques.su_trsf_shon9 IS NULL
            THEN ''''
            ELSE CONCAT(''Service public ou d''''intérêt collectif - '', donnees_techniques.su_trsf_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


FROM 

    &DB_PREFIXErapport_instruction 
    LEFT JOIN &DB_PREFIXEdossier 
        ON dossier.dossier=rapport_instruction.dossier_instruction 
    LEFT JOIN &DB_PREFIXEavis_decision 
        ON dossier.avis_decision = avis_decision.avis_decision
    LEFT JOIN &DB_PREFIXEdonnees_techniques
        ON dossier.dossier = donnees_techniques.dossier_instruction
    LEFT JOIN &DB_PREFIXEdivision
        ON dossier.division = division.division
    LEFT JOIN &DB_PREFIXEdirection
        ON division.direction = direction.direction
    LEFT JOIN &DB_PREFIXEinstructeur 
        ON instructeur.instructeur=dossier.instructeur 
    LEFT JOIN &DB_PREFIXElien_dossier_demandeur 
        ON lien_dossier_demandeur.dossier=dossier.dossier 
    LEFT JOIN &DB_PREFIXEdemandeur 
        ON demandeur.demandeur=lien_dossier_demandeur.demandeur 
    LEFT JOIN &DB_PREFIXEcivilite 
        ON demandeur.personne_morale_civilite=civilite.civilite OR demandeur.particulier_civilite=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 
    
WHERE 

    rapport_instruction = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE' WHERE code = 'rapport_instruction';
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,

    --Données générales du dossier d''instruction
    dossier.dossier_libelle as libelle_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_code_postal as terrain_adresse_code_postal_dossier,
    dossier.terrain_adresse_localite as terrain_adresse_localite_dossier,

    --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.code_postal as code_postal_demandeur,
    demandeur.localite as ville_demandeur,
    demandeur.personne_morale_denomination as societe_demandeur,

    --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, 
    to_char(consultation.date_envoi,''DD/MM/YYYY'') as date_envoi_dossier, 

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

    --Données générales des données techniques
    donnees_techniques.co_projet_desc as co_prejet_desc_donnees_techniques,
    donnees_techniques.am_projet_desc as am_projet_desc_donnees_techniques,
    donnees_techniques.dm_projet_desc as dm_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_trsf_shon1 IS NULL
            THEN ''''
            ELSE CONCAT(''Habitation - '', donnees_techniques.su_trsf_shon1, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon2 IS NULL
            THEN ''''
            ELSE CONCAT(''Hébergement hôtelier - '', donnees_techniques.su_trsf_shon2, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon3 IS NULL
            THEN ''''
            ELSE CONCAT(''Bureaux - '', donnees_techniques.su_trsf_shon3, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon4 IS NULL
            THEN ''''
            ELSE CONCAT(''Commerce - '', donnees_techniques.su_trsf_shon4, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon5 IS NULL
            THEN ''''
            ELSE CONCAT(''Artisanat - '', donnees_techniques.su_trsf_shon5, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon6 IS NULL
            THEN ''''
            ELSE CONCAT(''Industrie - '', donnees_techniques.su_trsf_shon6, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon7 IS NULL
            THEN ''''
            ELSE CONCAT(''Exploitation agricole ou forestière - '', donnees_techniques.su_trsf_shon7, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon8 IS NULL
            THEN ''''
            ELSE CONCAT(''Entrepôt - '', donnees_techniques.su_trsf_shon8, '' m² / '')
        END, 
        CASE
            WHEN donnees_techniques.su_trsf_shon9 IS NULL
            THEN ''''
            ELSE CONCAT(''Service public ou d''''intérêt collectif - '', donnees_techniques.su_trsf_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_station_place_nb_donnees_techniques
    
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_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_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
        
         
WHERE 


    consultation.consultation = &idx AND lien_dossier_demandeur.petitionnaire_principal IS TRUE' WHERE code = 'consultation';
UPDATE om_requete SET requete = 'SELECT

    --Données générales de l''événement d''instruction
    instruction.complement as complement_instruction,
    instruction.complement2 as complement2_instruction,
    instruction.code_barres as code_barres_instruction,
    om_lettretype.libelle as libelle_om_lettretype,

    --Données générales du dossier d''instruction
    dossier.dossier_libelle as libelle_dossier,
    dossier_autorisation.dossier_autorisation_libelle as libelle_da,

    dossier_autorisation_type_detaille.code as code_datd,
    dossier_autorisation_type_detaille.libelle as libelle_datd,
    dossier_autorisation_type.code as code_dat,
    dossier_autorisation_type.libelle as libelle_dat,
    dossier_instruction_type.code as code_dit,
    dossier_instruction_type.libelle as libelle_dit,
    dossier.delai as delai_dossier,
    replace(dossier.terrain_references_cadastrales, '';'', '' '') as terrain_references_cadastrales_dossier,

    avis_decision.libelle as libelle_avis_decision,

    --Coordonnées de l''instructeur
    instructeur.nom as nom_instructeur,
    instructeur.telephone as telephone_instructeur,
    division.code as division_instructeur,
    om_utilisateur.email as email_instructeur,

    --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_bp as terrain_adresse_bp_dossier,
    dossier.terrain_adresse_code_postal as terrain_adresse_code_postal_dossier,
    dossier.terrain_adresse_localite as terrain_adresse_localite_dossier,
    dossier.terrain_adresse_cedex as terrain_adresse_cedex_dossier,

    arrondissement.libelle as libelle_arrondissement,

    --Coordonnées du pétitionnaire principale
    CASE WHEN petitionnaire_principal.qualite=''particulier''
        THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
    END as nom_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,
    petitionnaire_principal.bp as bp_petitionnaire_principal,
    petitionnaire_principal.code_postal as code_postal_petitionnaire_principal,
    petitionnaire_principal.localite as localite_petitionnaire_principal,
    petitionnaire_principal.cedex as cedex_petitionnaire_principal,
    petitionnaire_principal.pays as pays_petitionnaire_principal,

    --Coordonnées du délégataire
    CASE
        WHEN delegataire.qualite=''particulier''
        THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
        ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
    END as nom_delegataire,
    delegataire.numero as numero_delegataire,
    delegataire.voie as voie_delegataire,
    delegataire.complement as complemennt_delegataire,
    delegataire.lieu_dit as lieu_dit_delegataire,
    delegataire.bp as bp_delegataire,
    delegataire.code_postal as code_postal_delegataire,
    delegataire.localite as ville_delegataire,
    delegataire.cedex as cedex_delegataire,
    delegataire.pays as pays_delegataire,

    -- CORRESPONDANT : destinataire du courrier. Il est le délégataire ou le pétitionnaire principal
    CASE
        WHEN delegataire.qualite IS NULL
        THEN
            CASE WHEN petitionnaire_principal.qualite=''particulier''
                    THEN TRIM(CONCAT(petitionnaire_principal.particulier_nom, '' '', petitionnaire_principal.particulier_prenom))
                    ELSE TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, '' '', petitionnaire_principal.personne_morale_denomination))
            END
        ELSE
            CASE WHEN delegataire.qualite=''particulier''
                THEN TRIM(CONCAT(delegataire.particulier_nom, '' '', delegataire.particulier_prenom))
                ELSE TRIM(CONCAT(delegataire.personne_morale_raison_sociale, '' '', delegataire.personne_morale_denomination))
            END
    END as nom_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.numero
        ELSE delegataire.numero
    END as numero_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.voie
        ELSE delegataire.voie
    END as voie_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.complement
        ELSE delegataire.complement
    END as complement_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.lieu_dit
        ELSE delegataire.lieu_dit
    END as lieu_dit_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.bp
        ELSE delegataire.bp
    END as bp_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.code_postal
        ELSE delegataire.code_postal
    END as code_postal_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.localite
        ELSE delegataire.localite
    END as ville_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.cedex
        ELSE delegataire.cedex
    END as cedex_correspondant,

    CASE
        WHEN delegataire.qualite IS NULL
        THEN petitionnaire_principal.pays
        ELSE delegataire.pays
    END as pays_correspondant,

    --Dates importantes du dossier d''instruction
    to_char(dossier.date_depot, ''DD/MM/YYYY'') as date_depot_dossier,
    to_char(dossier.date_decision, ''DD/MM/YYYY'') as date_decision_dossier,
    to_char(dossier.date_limite,''DD/MM/YYYY'') as date_limite_dossier,
    to_char(dossier.date_achevement,''DD/MM/YYYY'') as date_achevement_dossier,
    to_char(dossier.date_conformite,''DD/MM/YYYY'') as date_conformite_dossier,
    
    --Noms des signataires
    CONCAT(signataire_arrete.prenom, '' '', signataire_arrete.nom) as arrete_signataire,
    division.chef as chef_division,
    direction.chef as chef_direction,
    
    --Données générales des données techniquesDONNÉES TECHNIQUES
    donnees_techniques.co_projet_desc as co_projet_desc_donnees_techniques,
    donnees_techniques.am_projet_desc as am_projet_desc_donnees_techniques,
    donnees_techniques.dm_projet_desc as dm_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_trsf_shon1 IS NULL
            THEN ''''
            ELSE CONCAT(''Habitation - '', donnees_techniques.su_trsf_shon1, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon2 IS NULL
            THEN ''''
            ELSE CONCAT(''Hébergement hôtelier - '', donnees_techniques.su_trsf_shon2, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon3 IS NULL
            THEN ''''
            ELSE CONCAT(''Bureaux - '', donnees_techniques.su_trsf_shon3, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon4 IS NULL
            THEN ''''
            ELSE CONCAT(''Commerce - '', donnees_techniques.su_trsf_shon4, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon5 IS NULL
            THEN ''''
            ELSE CONCAT(''Artisanat - '', donnees_techniques.su_trsf_shon5, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon6 IS NULL
            THEN ''''
            ELSE CONCAT(''Industrie - '', donnees_techniques.su_trsf_shon6, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon7 IS NULL
            THEN ''''
            ELSE CONCAT(''Exploitation agricole ou forestière - '', donnees_techniques.su_trsf_shon7, '' m² / '')
        END,
        CASE
            WHEN donnees_techniques.su_trsf_shon8 IS NULL
            THEN ''''
            ELSE CONCAT(''Entrepôt - '', donnees_techniques.su_trsf_shon8, '' m² / '')
        END, 
        CASE
            WHEN donnees_techniques.su_trsf_shon9 IS NULL
            THEN ''''
            ELSE CONCAT(''Service public ou d''''intérêt collectif - '', donnees_techniques.su_trsf_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
    
FROM
    &DB_PREFIXEinstruction
LEFT JOIN
    &DB_PREFIXEsignataire_arrete
    ON instruction.signataire_arrete = signataire_arrete.signataire_arrete
LEFT JOIN
    &DB_PREFIXEom_lettretype
    ON instruction.lettretype = om_lettretype.id and om_lettretype.actif IS TRUE
LEFT JOIN
    &DB_PREFIXEdossier
    ON
        instruction.dossier=dossier.dossier
LEFT JOIN
    &DB_PREFIXElien_dossier_demandeur
    ON
        instruction.dossier = lien_dossier_demandeur.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN
    &DB_PREFIXEdemandeur as petitionnaire_principal
    ON
        lien_dossier_demandeur.demandeur = petitionnaire_principal.demandeur
LEFT JOIN
    (
    &DB_PREFIXElien_dossier_demandeur AS lien_dossier_delegataire
        JOIN &DB_PREFIXEdemandeur as delegataire
        ON
            lien_dossier_delegataire.demandeur = delegataire.demandeur AND delegataire.type_demandeur = ''delegataire''
    )
    ON
        instruction.dossier = lien_dossier_delegataire.dossier AND lien_dossier_delegataire.petitionnaire_principal IS FALSE
LEFT JOIN
    &DB_PREFIXEdossier_instruction_type
    ON
        dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
LEFT JOIN
    &DB_PREFIXEdossier_autorisation
    ON
        dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type_detaille
    ON
        dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille
LEFT JOIN
    &DB_PREFIXEdossier_autorisation_type
    ON
        dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type
LEFT JOIN
    &DB_PREFIXEinstructeur
    ON
        dossier.instructeur = instructeur.instructeur
LEFT JOIN
    &DB_PREFIXEom_utilisateur
    ON
        om_utilisateur.om_utilisateur = instructeur.om_utilisateur
LEFT JOIN
    &DB_PREFIXEdivision
    ON
        instructeur.division = division.division
LEFT JOIN 
    &DB_PREFIXEdirection
    ON division.direction = direction.direction
LEFT JOIN
    &DB_PREFIXEarrondissement
    ON
        dossier.terrain_adresse_code_postal = arrondissement.code_postal
LEFT JOIN
    &DB_PREFIXEavis_decision
    ON
        dossier.avis_decision = avis_decision.avis_decision
LEFT JOIN
    &DB_PREFIXEetat
    ON
        dossier.etat = etat.etat
LEFT JOIN
    &DB_PREFIXEdonnees_techniques
    ON
    dossier.dossier = donnees_techniques.dossier_instruction
WHERE instruction.instruction = &idx' WHERE code = 'instruction';

---
--- Met le type "text" pour le champ terrain_references_cadastrales
--- des tables dossier et dossier_autorisation
---
ALTER TABLE dossier ALTER COLUMN terrain_references_cadastrales TYPE text;
ALTER TABLE dossier_autorisation ALTER COLUMN terrain_references_cadastrales TYPE text;

---
--- Ajout de la table dossier_autorisation_parcelle
---
CREATE TABLE dossier_autorisation_parcelle (
    dossier_autorisation_parcelle integer NOT NULL,
    dossier_autorisation character varying(30) NOT NULL,
    parcelle character varying(20) DEFAULT NULL,
    libelle character varying(20)
);

---
--- Ajout de la clé primaire de la table dossier_autorisation_parcelle
---
ALTER TABLE ONLY dossier_autorisation_parcelle
    ADD CONSTRAINT dossier_autorisation_parcelle_pkey PRIMARY KEY (dossier_autorisation_parcelle);

---
--- Ajout des clés étrangères de la table dossier_autorisation_parcelle
---
ALTER TABLE ONLY dossier_autorisation_parcelle
    ADD CONSTRAINT dossier_autorisation_parcelle_dossier_autorisation_fkey FOREIGN KEY (dossier_autorisation) REFERENCES dossier_autorisation(dossier_autorisation);
ALTER TABLE ONLY dossier_autorisation_parcelle
    ADD CONSTRAINT dossier_autorisation_parcelle_parcelle_fkey FOREIGN KEY (parcelle) REFERENCES parcelle(parcelle);

---
--- Ajout de la séquence de la table dossier_autorisation_parcelle
---
CREATE SEQUENCE dossier_autorisation_parcelle_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--- Suppression de la table terrain désormais inutile
DROP TABLE terrain CASCADE;

--- Suppression de la table parametre désormais inutile
DROP TABLE parametre CASCADE;

--- Suppression de la table destination désormais inutile
DROP TABLE destination CASCADE;

--- Suppression de la table lien_evenement_dossier_autorisation_type désormais 
--- inutile
DROP TABLE lien_evenement_dossier_autorisation_type CASCADE;

--- Suppression de la table travaux désormais inutile
DROP TABLE travaux CASCADE;

--- Suppression de la table travaux désormais inutile
DROP TABLE categorie_demandeur CASCADE;

--- Augmente la taille des champs libelle des tables avis_decison 
--- et avis_consultation
ALTER TABLE avis_decision ALTER libelle TYPE character varying(50);
ALTER TABLE avis_consultation ALTER libelle TYPE character varying(50);

-- Suppression des sequences inutiles
DROP SEQUENCE dossier_at_seq;
DROP SEQUENCE dossier_az_seq;
DROP SEQUENCE dossier_cu_seq;
DROP SEQUENCE dossier_dat_seq;
DROP SEQUENCE dossier_dp_seq;
DROP SEQUENCE dossier_pa_seq;
DROP SEQUENCE dossier_pc_seq;
DROP SEQUENCE dossier_pca_seq;
DROP SEQUENCE dossier_pci_seq;
DROP SEQUENCE dossier_pd_seq;
