SELECT
    --Données générales du rapport d'instruction
    rapport_instruction.dossier_instruction as dossier_instruction_rapport_instruction, 
    analyse_reglementaire_om_html as analyse_reglementaire_rapport_instruction,
    description_projet_om_html 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, 
    dossier.dossier as code_barres_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,
    demandeur.particulier_nom as particulier_nom_demandeur,
    demandeur.particulier_prenom as particulier_prenom_demandeur,
    demandeur.personne_morale_denomination as personne_morale_denomination_demandeur,
    demandeur.personne_morale_raison_sociale as personne_morale_raison_sociale_demandeur,
    demandeur.personne_morale_siret as personne_morale_siret_demandeur,
    demandeur.personne_morale_nom as personne_morale_nom_demandeur,
    demandeur.personne_morale_prenom as personne_morale_prenom_demandeur,
    demandeur.numero as numero_demandeur,
    demandeur.voie as voie_demandeur,
    demandeur.complement as complement_demandeur,
    demandeur.lieu_dit as lieu_dit_demandeur,
    demandeur.localite as localite_demandeur,
    demandeur.code_postal as code_postal_demandeur,
    demandeur.bp as bp_demandeur,
    demandeur.cedex as cedex_demandeur,

    --Coordonnées du pétitionnaire 1
    CASE WHEN petitionnaire_1.qualite='particulier'
        THEN TRIM(CONCAT(civilite_1.civilite, ' ', petitionnaire_1.particulier_nom, ' ', petitionnaire_1.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_1.personne_morale_raison_sociale, ' ', petitionnaire_1.personne_morale_denomination))
    END as nom_petitionnaire_1,
    petitionnaire_1.numero as numero_petitionnaire_1,
    petitionnaire_1.voie as voie_petitionnaire_1,
    petitionnaire_1.complement as complement_petitionnaire_1,
    petitionnaire_1.lieu_dit as lieu_dit_petitionnaire_1,
    CASE 
        WHEN petitionnaire_1.bp IS NULL
        THEN ''
        ELSE CONCAT('BP ', petitionnaire_1.bp)
    END as bp_petitionnaire_1,
    petitionnaire_1.code_postal as code_postal_petitionnaire_1,
    petitionnaire_1.localite as localite_petitionnaire_1,
    CASE 
        WHEN petitionnaire_1.cedex IS NULL
        THEN ''
        ELSE CONCAT('CEDEX ', petitionnaire_1.cedex)
    END as cedex_petitionnaire_1,
    petitionnaire_1.pays as pays_petitionnaire_1,

    --Coordonnées du pétitionnaire 2
    CASE WHEN petitionnaire_2.qualite='particulier'
        THEN TRIM(CONCAT(civilite_2.civilite, ' ', petitionnaire_2.particulier_nom, ' ', petitionnaire_2.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_2.personne_morale_raison_sociale, ' ', petitionnaire_2.personne_morale_denomination))
    END as nom_petitionnaire_2,
    petitionnaire_2.numero as numero_petitionnaire_2,
    petitionnaire_2.voie as voie_petitionnaire_2,
    petitionnaire_2.complement as complement_petitionnaire_2,
    petitionnaire_2.lieu_dit as lieu_dit_petitionnaire_2,
    CASE 
        WHEN petitionnaire_2.bp IS NULL
        THEN ''
        ELSE CONCAT('BP ', petitionnaire_2.bp)
    END as bp_petitionnaire_2,
    petitionnaire_2.code_postal as code_postal_petitionnaire_2,
    petitionnaire_2.localite as localite_petitionnaire_2,
    CASE 
        WHEN petitionnaire_2.cedex IS NULL
        THEN ''
        ELSE CONCAT('CEDEX ', petitionnaire_2.cedex)
    END as cedex_petitionnaire_2,
    petitionnaire_2.pays as pays_petitionnaire_2,

    --Coordonnées du pétitionnaire 3
    CASE WHEN petitionnaire_3.qualite='particulier'
        THEN TRIM(CONCAT(civilite_3.civilite, ' ', petitionnaire_3.particulier_nom, ' ', petitionnaire_3.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_3.personne_morale_raison_sociale, ' ', petitionnaire_3.personne_morale_denomination))
    END as nom_petitionnaire_3,
    petitionnaire_3.numero as numero_petitionnaire_3,
    petitionnaire_3.voie as voie_petitionnaire_3,
    petitionnaire_3.complement as complement_petitionnaire_3,
    petitionnaire_3.lieu_dit as lieu_dit_petitionnaire_3,
    CASE 
        WHEN petitionnaire_3.bp IS NULL
        THEN ''
        ELSE CONCAT('BP ', petitionnaire_3.bp)
    END as bp_petitionnaire_3,
    petitionnaire_3.code_postal as code_postal_petitionnaire_3,
    petitionnaire_3.localite as localite_petitionnaire_3,
    CASE 
        WHEN petitionnaire_3.cedex IS NULL
        THEN ''
        ELSE CONCAT('CEDEX ', petitionnaire_3.cedex)
    END as cedex_petitionnaire_3,
    petitionnaire_3.pays as pays_petitionnaire_3,

    --Coordonnées du pétitionnaire 4
    CASE WHEN petitionnaire_4.qualite='particulier'
        THEN TRIM(CONCAT(civilite_4.civilite, ' ', petitionnaire_4.particulier_nom, ' ', petitionnaire_4.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_4.personne_morale_raison_sociale, ' ', petitionnaire_4.personne_morale_denomination))
    END as nom_petitionnaire_4,
    petitionnaire_4.numero as numero_petitionnaire_4,
    petitionnaire_4.voie as voie_petitionnaire_4,
    petitionnaire_4.complement as complement_petitionnaire_4,
    petitionnaire_4.lieu_dit as lieu_dit_petitionnaire_4,
    CASE 
        WHEN petitionnaire_4.bp IS NULL
        THEN ''
        ELSE CONCAT('BP ', petitionnaire_4.bp)
    END as bp_petitionnaire_4,
    petitionnaire_4.code_postal as code_postal_petitionnaire_4,
    petitionnaire_4.localite as localite_petitionnaire_4,
    CASE 
        WHEN petitionnaire_4.cedex IS NULL
        THEN ''
        ELSE CONCAT('CEDEX ', petitionnaire_4.cedex)
    END as cedex_petitionnaire_4,
    petitionnaire_4.pays as pays_petitionnaire_4,

    --Coordonnées du pétitionnaire 5
    CASE WHEN petitionnaire_5.qualite='particulier'
        THEN TRIM(CONCAT(civilite_5.civilite, ' ', petitionnaire_5.particulier_nom, ' ', petitionnaire_5.particulier_prenom))
        ELSE TRIM(CONCAT(petitionnaire_5.personne_morale_raison_sociale, ' ', petitionnaire_5.personne_morale_denomination))
    END as nom_petitionnaire_5,
    petitionnaire_5.numero as numero_petitionnaire_5,
    petitionnaire_5.voie as voie_petitionnaire_5,
    petitionnaire_5.complement as complement_petitionnaire_5,
    petitionnaire_5.lieu_dit as lieu_dit_petitionnaire_5,
    CASE 
        WHEN petitionnaire_5.bp IS NULL
        THEN ''
        ELSE CONCAT('BP ', petitionnaire_5.bp)
    END as bp_petitionnaire_5,
    petitionnaire_5.code_postal as code_postal_petitionnaire_5,
    petitionnaire_5.localite as localite_petitionnaire_5,
    CASE 
        WHEN petitionnaire_5.cedex IS NULL
        THEN ''
        ELSE CONCAT('CEDEX ', petitionnaire_5.cedex)
    END as cedex_petitionnaire_5,
    petitionnaire_5.pays as pays_petitionnaire_5,
    
    --Nom de l'instructeur
    instructeur.nom as nom_instructeur, 

    --Noms des signataires
    division.chef as chef_division,
    direction.chef as chef_direction,
    direction.libelle as libelle_direction,
    direction.description as description_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_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


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 (
        SELECT lien_dossier_demandeur.dossier, array_agg(lien_dossier_demandeur.demandeur ORDER BY lien_dossier_demandeur.demandeur) AS petitionnaire_autre
        FROM &DB_PREFIXElien_dossier_demandeur
        LEFT JOIN &DB_PREFIXEdossier
            ON lien_dossier_demandeur.dossier=dossier.dossier 
            AND lien_dossier_demandeur.petitionnaire_principal IS FALSE
        LEFT JOIN &DB_PREFIXErapport_instruction
            ON rapport_instruction.dossier_instruction = dossier.dossier
        WHERE rapport_instruction.rapport_instruction = &idx
        GROUP BY lien_dossier_demandeur.dossier
    ) as sub_petitionnaire_autre
    ON rapport_instruction.dossier_instruction = sub_petitionnaire_autre.dossier
    LEFT JOIN
        &DB_PREFIXEdemandeur as petitionnaire_1
    ON
        petitionnaire_1.demandeur = petitionnaire_autre[1] AND petitionnaire_1.type_demandeur != 'delegataire'
    LEFT JOIN
        &DB_PREFIXEcivilite as civilite_1
    ON
        petitionnaire_1.particulier_civilite = civilite_1.civilite OR petitionnaire_1.personne_morale_civilite = civilite_1.civilite
    LEFT JOIN
        &DB_PREFIXEdemandeur as petitionnaire_2
    ON
        petitionnaire_2.demandeur = petitionnaire_autre[2] AND petitionnaire_2.type_demandeur != 'delegataire'
    LEFT JOIN
        &DB_PREFIXEcivilite as civilite_2
    ON
        petitionnaire_2.particulier_civilite = civilite_2.civilite OR petitionnaire_2.personne_morale_civilite = civilite_2.civilite
    LEFT JOIN
        &DB_PREFIXEdemandeur as petitionnaire_3
    ON
        petitionnaire_3.demandeur = petitionnaire_autre[3] AND petitionnaire_3.type_demandeur != 'delegataire'
    LEFT JOIN
        &DB_PREFIXEcivilite as civilite_3
    ON
        petitionnaire_3.particulier_civilite = civilite_3.civilite OR petitionnaire_3.personne_morale_civilite = civilite_3.civilite
    LEFT JOIN
        &DB_PREFIXEdemandeur as petitionnaire_4
    ON
        petitionnaire_4.demandeur = petitionnaire_autre[4] AND petitionnaire_4.type_demandeur != 'delegataire'
    LEFT JOIN
        &DB_PREFIXEcivilite as civilite_4
    ON
        petitionnaire_4.particulier_civilite = civilite_4.civilite OR petitionnaire_4.personne_morale_civilite = civilite_4.civilite
    LEFT JOIN
        &DB_PREFIXEdemandeur as petitionnaire_5
    ON
        petitionnaire_5.demandeur = petitionnaire_autre[5] AND petitionnaire_5.type_demandeur != 'delegataire'
    LEFT JOIN
        &DB_PREFIXEcivilite as civilite_5
    ON
        petitionnaire_5.particulier_civilite = civilite_5.civilite OR petitionnaire_5.personne_morale_civilite = civilite_5.civilite
    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