CREATE OR REPLACE VIEW public.vue_edit_gen
AS SELECT d.id_dossier,
    d.id_dossier_modifie,
    get_nom_commune(c.n_commune, d.date_depot) AS nom_commune,
    c.observations AS observations_commune,
    c.logo_commune,
    (c.preposition::text || ' '::text) || c.nom::text AS de_nom_commune,
    c.code_postal AS code_postal_commune,
    c.adresse_mairie,
    c.telephone AS telephone_mairie,
    c.nom_maire,
    c.visa_plu,
    d.co_type_dossier,
    td.libelle AS type_dossier,
    d.departement,
    d.n_commune,
    d.annee,
    d.hn_dossier,
    d.bn_dossier,
    d.n_maj,
    d.nom_dossier,
    ('*'::text || replace(d.nom_dossier::text, ' '::text, '-'::text)) || '*'::text AS code_barre_dossier,
    d.numero_etude,
    d.n_archive,
    ( SELECT od.designation
           FROM objet_demande od
          WHERE od.id_objet_demande = d.id_objet_demande) AS objet_demande,
    ( SELECT p.designation
           FROM preemption p
          WHERE p.id_preemption = d.id_preemption) AS preemption,
    d.description_projet,
    d.description_projet_instructeur,
    recep.nom_prenom AS recepteur,
    recep.complement AS complement_recepteur,
    inst.nom_prenom AS instructeur,
    inst.complement AS complement_instructeur,
    inst.email AS email_instructeur,
    inst.telephone AS telephone_instructeur,
    inst.telephone_portable AS portable_instructeur,
    sign.nom_prenom AS signataire,
    sign.complement AS complement_signataire,
    ( SELECT ac.nom
           FROM autorite_competente ac
          WHERE ac.co_autorite_competente::text = d.co_autorite_competente::text) AS autorite_competente,
    dem.id_type_personne AS id_type_dem,
    dem.principale AS principal_dem,
    dem.id_personne AS id_personne_dem,
    ( SELECT c_1.designation
           FROM civilite c_1
          WHERE c_1.co_civilite::text = dem.co_civilite::text) AS civilite_dem,
    dem.nom AS nom_dem,
    dem.prenom AS prenom_dem,
    dem.raison_sociale AS raison_sociale_dem,
    dem.n_professionnel AS n_professionnel_dem,
    dem.categorie_juridique AS categorie_juridique_dem,
    dem.n_voirie AS n_voirie_dem,
    dem.r_voirie AS r_voirie_dem,
    dem.adresse AS adresse_dem,
    dem.complement_adresse AS complement_adresse_dem,
    dem.boite_postale AS boite_postale_dem,
    dem.cedex AS cedex_dem,
    dem.lieu_dit AS lieu_dit_dem,
    dem.code_postal AS code_postal_dem,
    dem.commune AS commune_dem,
    ( SELECT pays.nom
           FROM pays
          WHERE pays.id_pays = dem.id_pays) AS pays_dem,
    dem.telephone AS telephone_dem,
    dem.telephone_portable AS portable_dem,
    dem.fax AS fax_dem,
    dem.e_mail AS email_dem,
    ( SELECT qp.designation
           FROM qualite_personne qp
          WHERE qp.id_qualite_personne = dem.id_qualite_personne) AS qualite_dem,
    personne_dossier(d.id_dossier, 1) AS liste_dem,
    prop.civilite AS civilite_prop,
    prop.nom AS nom_prop,
    prop.prenom AS prenom_prop,
    prop.raison_sociale AS raison_sociale_prop,
    prop.n_professionnel AS n_professionnel_prop,
    prop.categorie_juridique AS categorie_juridique_prop,
    prop.n_voirie AS n_voirie_prop,
    prop.r_voirie AS r_voirie_prop,
    prop.adresse AS adresse_prop,
    prop.complement_adresse AS complement_adresse_prop,
    prop.boite_postale AS boite_postale_prop,
    prop.cedex AS cedex_prop,
    prop.lieu_dit AS lieu_dit_prop,
    prop.code_postal AS code_postal_prop,
    prop.commune AS commune_prop,
    ( SELECT pays.nom
           FROM pays
          WHERE pays.id_pays = prop.id_pays) AS pays_prop,
    prop.telephone AS telephone_prop,
    prop.telephone_portable AS portable_prop,
    prop.fax AS fax_prop,
    prop.e_mail AS email_prop,
    ( SELECT qp.designation
           FROM qualite_personne qp
          WHERE qp.id_qualite_personne = prop.id_qualite_personne) AS qualite_prop,
    personne_dossier(d.id_dossier, 2) AS liste_prop,
    ( SELECT c_1.designation
           FROM civilite c_1
          WHERE c_1.co_civilite::text = repr.co_civilite::text) AS civilite_repr,
    repr.nom AS nom_repr,
    repr.prenom AS prenom_repr,
    repr.n_voirie AS n_voirie_repr,
    repr.r_voirie AS r_voirie_repr,
    repr.adresse AS adresse_repr,
    repr.complement_adresse AS complement_adresse_repr,
    repr.boite_postale AS boite_postale_repr,
    repr.cedex AS cedex_repr,
    repr.lieu_dit AS lieu_dit_repr,
    repr.code_postal AS code_postal_repr,
    repr.commune AS commune_repr,
    ( SELECT c_1.designation
           FROM civilite c_1
          WHERE c_1.co_civilite::text = aut.co_civilite::text) AS civilite_aut,
    aut.nom AS nom_aut,
    aut.prenom AS prenom_aut,
    aut.n_voirie AS n_voirie_aut,
    aut.r_voirie AS r_voirie_aut,
    aut.adresse AS adresse_aut,
    aut.complement_adresse AS complement_adresse_aut,
    aut.boite_postale AS boite_postale_aut,
    aut.cedex AS cedex_aut,
    aut.lieu_dit AS lieu_dit_aut,
    aut.code_postal AS code_postal_aut,
    aut.commune AS commune_aut,
    ( SELECT pays.nom
           FROM pays
          WHERE pays.id_pays = aut.id_pays) AS pays_aut,
    aut.e_mail AS email_aut,
    aut.n_inscription AS n_inscription_aut,
    aut.conseil_regional AS conseil_regional_aut,
    aut.categorie_juridique AS categorie_juridique_aut,
    terrain(d.id_dossier, 0) AS terrain,
    terrain(d.id_dossier, 1) AS terrain_avec_superficie,
    d.n_voirie_terrain,
    d.adresse_terrain,
    d.complement_adresse_terrain,
    d.rivoli_terrain,
    COALESCE(d.code_postal_terrain, c.code_postal) AS code_postal_terrain,
    d.boite_postale_terrain,
    d.cedex_terrain,
    d.lieu_dit_terrain,
    ((((((((COALESCE(d.n_voirie_terrain, ''::character varying)::text || COALESCE(' '::text || d.adresse_terrain::text, ''::text)) || COALESCE(' '::text || d.complement_adresse_terrain::text, ''::text)) || COALESCE(' '::text || d.lieu_dit_terrain::text, ''::text)) || COALESCE(' BP '::text || d.boite_postale_terrain::text, ''::text)) || COALESCE(' '::text || COALESCE(d.code_postal_terrain, c.code_postal)::text, ''::text)) || COALESCE(' '::text || c.nom::text, ''::text)) || COALESCE(' Cedex '::text || d.cedex_terrain::text, ''::text)))::character varying(255) AS adresse_terrain_complet,
    d.superficie_terrain,
    pos_terrain(d.id_dossier) AS pos_terrain,
    cos_terrain(d.id_dossier) AS cos_terrain,
    ( SELECT s.nom
           FROM secteur s
          WHERE s.id_secteur = d.id_secteur) AS secteur_terrain,
    d.canton_terrain,
    d.quartier_terrain,
    ( SELECT pa.designation
           FROM programme_amenagement pa
          WHERE pa.co_programme_amenagement::text = d.co_programme_amenagement::text) AS programme_amenagement,
    lotissement.nom AS nom_lotissement,
    lotissement.date_autorisation AS date_autorisation_lotissement,
    lotissement.date_modification AS date_modification_lotissement,
    d.n_lot,
    d.delai_base,
    COALESCE(d.delai_base_j, 0) AS delai_base_j,
    d.delai_complementaire,
    COALESCE(d.delai_complementaire_j, 0) AS delai_complementaire_j,
    d.delai_mineur,
    d.delai_base + d.delai_complementaire + d.delai_mineur AS delai_total,
    d.delai_base_j + d.delai_complementaire_j AS delai_total_j,
    d.date_demande,
    d.date_depot,
    d.date_saisie,
    d.date_modification,
    d.date_dossier_complete,
    d.date_dossier_incomplet,
        CASE
            WHEN d.date_affichage_numerique IS NULL THEN d.date_affichage_papier
            ELSE d.date_affichage_numerique
        END AS date_affichage_mairie,
    d.date_affichage_papier AS affichage_depot_papier,
    d.date_affichage_numerique AS affichage_depot_numerique,
    d.date_notification_delais,
    d.date_prolongation,
    add_month(d.date_depot, 1) AS date_limite_demande_pieces,
    d.date_limite_fourniture_pieces,
    d.date_limite_instruction,
    dsign.date_decision,
    dsign.date_notification_decision,
        CASE
            WHEN dsign.date_affichage_numerique IS NULL THEN dsign.date_affichage_papier
            ELSE dsign.date_affichage_numerique
        END AS date_affichage_decision,
    add_month(
        CASE
            WHEN dsign.date_affichage_numerique IS NULL THEN dsign.date_affichage_papier
            ELSE dsign.date_affichage_numerique
        END, 2) AS date_fin_affichage_decision,
    dsign.date_affichage_papier AS affichage_decision_papier,
    add_month(dsign.date_affichage_papier, 2) AS affichage_decision_fin_papier,
    dsign.date_affichage_numerique AS affichage_decision_numeric,
    add_month(dsign.date_affichage_numerique, 2) AS affichage_decision_fin_numeric,
    d.date_transfert,
    dinst.date_decision AS date_avis_instructeur,
    dinst.co_decision AS co_avis_instructeur,
    ( SELECT de.designation
           FROM decision de
          WHERE de.co_decision::text = dinst.co_decision::text) AS avis_instructeur,
    dinst.observation_decision AS observation_avis_instructeur,
    dsign.co_decision,
    ( SELECT de.designation
           FROM decision de
          WHERE de.co_decision::text = dsign.co_decision::text) AS decision,
    dsign.observation_decision,
    dsign.n_arrete,
    dsign.date_caducite,
    d.observation_dossier,
    pole_ads.nom AS nom_pole_ads,
    pole_ads.logo AS logo_pole_ads,
    pole_ads.code_postal AS code_postal_pole_ads,
    pole_ads.commune AS commune_pole_ads,
    d.pwd_suivi_numerique,
    convert_boolean_to_oui_non(d.architecte_non_obligatoire) AS architecte_non_obligatoire,
    dmaire.date_decision AS date_avis_maire,
    dmaire.co_decision AS co_avis_maire,
    ( SELECT de.designation
           FROM decision de
          WHERE de.co_decision::text = dmaire.co_decision::text) AS avis_maire,
    maire.nom_prenom AS maire,
    maire.complement AS complement_maire,
    maire.email AS email_maire,
    maire.telephone AS telephone_maire,
    maire.telephone_portable AS portable_maire,
    d.id_platau,
    c.e_mail AS email_mairie,
    np.numero_aee,
    np.date_reception AS date_dossier_demat,
    COALESCE(compte_intervenant.login, compte_particulier.login) AS login_deposant,
    COALESCE(intervenant.e_mail, particulier.e_mail) AS email_deposant,
        CASE
            WHEN np.id_intervenant IS NOT NULL THEN 'le partenaire'::text
            ELSE 'l''usager'::text
        END AS type_compte_deposant,
        CASE
            WHEN np.id_intervenant IS NOT NULL THEN COALESCE(intervenant.denomination, ((COALESCE(civilite_intervenant.designation::text || ' '::text, ''::text) || intervenant.nom::text) || COALESCE(' '::text || intervenant.prenom::text, ''::text))::character varying)
            ELSE COALESCE(particulier.denomination, ((COALESCE(civilite_particulier.designation::text || ' '::text, ''::text) || particulier.nom::text) || COALESCE(' '::text || particulier.prenom::text, ''::text))::character varying)
        END AS denomination_deposant,
        CASE
            WHEN np.id_intervenant IS NOT NULL THEN concat(intervenant.n_voirie || ' '::text, intervenant.r_voirie, intervenant.adresse::text || ' '::text, (', '::text || intervenant.code_postal::text) || ' '::text, intervenant.commune)
            ELSE concat(particulier.n_voirie || ' '::text, particulier.r_voirie, particulier.adresse::text || ' '::text, (', '::text || particulier.code_postal::text) || ' '::text, particulier.commune)
        END AS adresse_deposant,
    COALESCE(particulier.telephone, intervenant.telephone) AS telephone_deposant,
    COALESCE(particulier.telephone_portable, intervenant.telephone_portable) AS telephone_portable_deposant,
    COALESCE(particulier.date_creation, intervenant.date_creation::timestamp without time zone) AS date_creation_compte,
    COALESCE(particulier.date_acceptation_cgu, intervenant.date_acceptation_cgu) AS date_acceptation_cgu,
    terrain_ss_prefixe(d.id_dossier, 0) AS terrain_ss_prefixe,
    terrain_ss_prefixe(d.id_dossier, 1) AS terrain_ss_prefixe_avec_superficie
   FROM dossier d
     JOIN commune c ON c.n_commune = d.n_commune
     JOIN type_dossier td ON td.co_type_dossier::text = d.co_type_dossier::text
     LEFT JOIN personne dem ON dem.id_dossier = d.id_dossier
     LEFT JOIN vue_proprietaire_principal prop ON prop.id_dossier = d.id_dossier
     LEFT JOIN notification_portail np ON np.id_dossier = d.id_dossier AND np.type_notification_portail = 1
     LEFT JOIN vue_decision_signataire dsign ON dsign.id_dossier = d.id_dossier
     LEFT JOIN intervenant_mairie sign ON sign.id_intervenant_mairie = dsign.id_intervenant_mairie
     LEFT JOIN decision dec_sign ON dec_sign.co_decision::text = dsign.co_decision::text
     LEFT JOIN personne repr ON repr.id_personne_representant = dem.id_personne AND repr.id_type_personne = 4
     LEFT JOIN personne aut ON aut.id_dossier = d.id_dossier AND aut.id_type_personne = 5 AND aut.principale::text = 'O'::text
     LEFT JOIN dos_intervenant_mairie drecep ON drecep.id_dossier = d.id_dossier AND drecep.id_type_intervenant_mairie = 1
     LEFT JOIN intervenant_mairie recep ON recep.id_intervenant_mairie = drecep.id_intervenant_mairie
     LEFT JOIN dos_intervenant_mairie dinst ON dinst.id_dossier = d.id_dossier AND dinst.id_type_intervenant_mairie = 2
     LEFT JOIN intervenant_mairie inst ON inst.id_intervenant_mairie = dinst.id_intervenant_mairie
     LEFT JOIN dos_intervenant_mairie dmaire ON dmaire.id_dossier = d.id_dossier AND dmaire.id_type_intervenant_mairie = 16
     LEFT JOIN intervenant_mairie maire ON maire.id_intervenant_mairie = dmaire.id_intervenant_mairie
     LEFT JOIN lotissement ON lotissement.id_lotissement = d.id_lotissement
     LEFT JOIN pole_ads ON pole_ads.id_pole_ads = d.id_pole_ads
     LEFT JOIN compte_utilisateur compte_intervenant ON compte_intervenant.id_intervenant = np.id_intervenant
     LEFT JOIN compte_utilisateur compte_particulier ON compte_particulier.id_particulier = np.id_particulier
     LEFT JOIN intervenant ON intervenant.id_intervenant = np.id_intervenant
     LEFT JOIN particulier ON particulier.id_particulier = np.id_particulier
     LEFT JOIN civilite civilite_intervenant ON intervenant.co_civilite::text = civilite_intervenant.co_civilite::text
     LEFT JOIN civilite civilite_particulier ON particulier.co_civilite::text = civilite_particulier.co_civilite::text;