afe_21_vues_xapps.sql

/*Amt_Fon_Eco V1.0*/
/*Creation des vues applicatives stockées dans le schéma x_apps */
/* afe_21_vues_xapps.sql */
/*PostGIS*/

/* Propriétaire : GeoCompiegnois - http://geo.compiegnois.fr/ */
/* Auteur : Grégory Bodet */



-- ####################################################################################################################################################
-- ###                                                                                                                                              ###
-- ###                                                              VUES APPLICATIVES                                                                ###
-- ###                                                                                                                                              ###
-- ####################################################################################################################################################


-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_v_etabloc_stat #####################################################################

-- View: m_activite_eco.xapps_an_v_etabloc_stat

-- DROP VIEW m_activite_eco.xapps_an_v_etabloc_stat;

CREATE OR REPLACE VIEW m_activite_eco.xapps_an_v_etabloc_stat
 AS
 WITH req_t AS (
         SELECT s.codecommuneetablissement,
            count(*) AS nb_etab_tot
           FROM m_activite_eco.an_eco_etab e,
            s_sirene.an_etablissement_api s
          WHERE e.idsiret::text = s.siret::text AND s.etatadministratifetablissement::text = 'A'::text
          GROUP BY s.codecommuneetablissement
        ), req_non_loc AS (
         SELECT s.codecommuneetablissement,
            count(*) AS nb_etab_nonloc
           FROM s_sirene.an_etablissement_api s,
            m_activite_eco.lk_adresseetablissement l
          WHERE s.siret::text = l.siret::text AND s.etatadministratifetablissement::text = 'A'::text AND l.idadresse IS NULL
          GROUP BY s.codecommuneetablissement
        ), req_t_compte AS (
         SELECT s.codecommuneetablissement,
            count(*) AS nb_etab_tot_compte
           FROM m_activite_eco.an_eco_etab e,
            s_sirene.an_etablissement_api s
          WHERE e.idsiret::text = s.siret::text AND s.etatadministratifetablissement::text = 'A'::text AND e.l_compte = true
          GROUP BY s.codecommuneetablissement
        ), req_non_loc_compte AS (
         SELECT s.codecommuneetablissement,
            count(*) AS nb_etab_nonloc_compte
           FROM m_activite_eco.an_eco_etab e,
            s_sirene.an_etablissement_api s,
            m_activite_eco.lk_adresseetablissement l
          WHERE s.siret::text = l.siret::text AND e.idsiret::text = s.siret::text AND e.l_compte = true AND s.etatadministratifetablissement::text = 'A'::text AND l.idadresse IS NULL
          GROUP BY s.codecommuneetablissement
        )
 SELECT req_t.codecommuneetablissement,
    g.libgeo AS libellecommuneetablissement,
    req_t.nb_etab_tot,
        CASE
            WHEN req_non_loc.nb_etab_nonloc IS NULL THEN 0::bigint
            ELSE req_non_loc.nb_etab_nonloc
        END AS nb_etab_nonloc,
        CASE
            WHEN req_non_loc.nb_etab_nonloc IS NULL THEN '0%'::character varying::text
            ELSE round((req_non_loc.nb_etab_nonloc::double precision / req_t.nb_etab_tot::double precision * 100::double precision)::numeric, 1) || '%'::character varying::text
        END AS tx_nonloc,
    req_t_compte.nb_etab_tot_compte,
        CASE
            WHEN req_non_loc_compte.nb_etab_nonloc_compte IS NULL THEN 0::bigint
            ELSE req_non_loc_compte.nb_etab_nonloc_compte
        END AS nb_etab_nonloc_compte,
        CASE
            WHEN req_non_loc_compte.nb_etab_nonloc_compte IS NULL THEN '0%'::character varying::text
            ELSE round((req_non_loc_compte.nb_etab_nonloc_compte::double precision / req_t_compte.nb_etab_tot_compte::double precision * 100::double precision)::numeric, 1) || '%'::character varying::text
        END AS tx_nonloc_compte
   FROM req_t
     LEFT JOIN r_administratif.an_geo g ON req_t.codecommuneetablissement::text = g.insee::text
     LEFT JOIN req_non_loc ON req_t.codecommuneetablissement::text = req_non_loc.codecommuneetablissement::text
     LEFT JOIN req_t_compte ON req_t_compte.codecommuneetablissement::text = req_t.codecommuneetablissement::text
     LEFT JOIN req_non_loc_compte ON req_non_loc_compte.codecommuneetablissement::text = req_t.codecommuneetablissement::text
  ORDER BY req_t.codecommuneetablissement;

COMMENT ON VIEW m_activite_eco.xapps_an_v_etabloc_stat
    IS 'Vue alphanumérique permettant de calculer des statistiques de localisation des établissements (SIRENE) à l''adresse par commune';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_v_photo_site_alaune #####################################################################

-- View: m_activite_eco.xapps_an_v_photo_site_alaune

-- DROP VIEW m_activite_eco.xapps_an_v_photo_site_alaune;

CREATE OR REPLACE VIEW m_activite_eco.xapps_an_v_photo_site_alaune
 AS
 SELECT x.r,
    x.gid,
    x.id,
    x.media,
    x.miniature,
    x.n_fichier,
    x.t_fichier,
    x.date_sai,
    x.date_maj,
    x.op_sai,
    x.op_maj,
    x.l_doc,
    x.t_doc,
    x.d_photo,
    x.alaune,
    x.alaunesite
   FROM ( SELECT row_number() OVER (PARTITION BY t.id ORDER BY t.id) AS r,
            t.gid,
            t.id,
            t.media,
            t.miniature,
            t.n_fichier,
            t.t_fichier,
            t.date_sai,
            t.date_maj,
            t.op_sai,
            t.op_maj,
            t.l_doc,
            t.t_doc,
            t.d_photo,
            t.alaune,
            t.alaunesite
           FROM m_activite_eco.an_eco_media t
          WHERE t.alaunesite = true) x
  WHERE x.r = 1
  ORDER BY x.id;

COMMENT ON VIEW m_activite_eco.xapps_an_v_photo_site_alaune
    IS 'Vue alphanumérique remontant les médias un seul média à la une définti par l''utilisateur';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_v_prixloc_stat #####################################################################

-- View: m_activite_eco.xapps_an_v_prixloc_stat

-- DROP VIEW m_activite_eco.xapps_an_v_prixloc_stat;

CREATE OR REPLACE VIEW m_activite_eco.xapps_an_v_prixloc_stat
 AS
 WITH req_site AS (
         SELECT geo_eco_site.idsite,
            geo_eco_site.site_nom
           FROM m_activite_eco.geo_eco_site
        ), req_pvm2_bureau AS (
         SELECT s_1.idsite,
            round(avg(l.pventem2)::numeric, 0) AS pv_bureau_m2
           FROM m_activite_eco.an_eco_loc_act l,
            m_activite_eco.lk_eco_loc_site s_1
          WHERE l.idloc::text = s_1.idloc::text AND l.typ2::text = '30'::text
          GROUP BY s_1.idsite
        ), req_pvm2_commerce AS (
         SELECT s_1.idsite,
            round(avg(l.pventem2)::numeric, 0) AS pv_commerce_m2
           FROM m_activite_eco.an_eco_loc_act l,
            m_activite_eco.lk_eco_loc_site s_1
          WHERE l.idloc::text = s_1.idloc::text AND l.typ2::text = '40'::text
          GROUP BY s_1.idsite
        ), req_pvm2_activite AS (
         SELECT s_1.idsite,
            round(avg(l.pventem2)::numeric, 0) AS pv_activite_m2
           FROM m_activite_eco.an_eco_loc_act l,
            m_activite_eco.lk_eco_loc_site s_1
          WHERE l.idloc::text = s_1.idloc::text AND l.typ2::text = '50'::text
          GROUP BY s_1.idsite
        ), req_lm2_bureau AS (
         SELECT s_1.idsite,
            round(avg(l.loyerm2)::numeric, 0) AS l_bureau_m2
           FROM m_activite_eco.an_eco_loc_act l,
            m_activite_eco.lk_eco_loc_site s_1
          WHERE l.idloc::text = s_1.idloc::text AND l.typ2::text = '30'::text
          GROUP BY s_1.idsite
        ), req_lm2_commerce AS (
         SELECT s_1.idsite,
            round(avg(l.loyerm2)::numeric, 0) AS l_commerce_m2
           FROM m_activite_eco.an_eco_loc_act l,
            m_activite_eco.lk_eco_loc_site s_1
          WHERE l.idloc::text = s_1.idloc::text AND l.typ2::text = '40'::text
          GROUP BY s_1.idsite
        ), req_lm2_activite AS (
         SELECT s_1.idsite,
            round(avg(l.loyerm2)::numeric, 0) AS l_activite_m2
           FROM m_activite_eco.an_eco_loc_act l,
            m_activite_eco.lk_eco_loc_site s_1
          WHERE l.idloc::text = s_1.idloc::text AND l.typ2::text = '50'::text
          GROUP BY s_1.idsite
        )
 SELECT s.idsite,
        CASE
            WHEN b.pv_bureau_m2 IS NULL THEN 'n.r'::text
            ELSE b.pv_bureau_m2::text
        END AS pv_bureau_m2,
        CASE
            WHEN c.pv_commerce_m2 IS NULL THEN 'n.r'::text
            ELSE c.pv_commerce_m2::text
        END AS pv_commerce_m2,
        CASE
            WHEN a.pv_activite_m2 IS NULL THEN 'n.r'::text
            ELSE a.pv_activite_m2::text
        END AS pv_activite_m2,
        CASE
            WHEN lb.l_bureau_m2 IS NULL THEN 'n.r'::text
            ELSE lb.l_bureau_m2::text
        END AS l_bureau_m2,
        CASE
            WHEN lc.l_commerce_m2 IS NULL THEN 'n.r'::text
            ELSE lc.l_commerce_m2::text
        END AS l_commerce_m2,
        CASE
            WHEN la.l_activite_m2 IS NULL THEN 'n.r'::text
            ELSE la.l_activite_m2::text
        END AS l_activite_m2,
    ((((((((((('<table border = 1>
	<tr><td>&nbsp;</td><td>Loyer HT/m²/an</td><td>Prix de vente/m²</td></tr>
	<tr><td>Bureau</td><td align="center">'::text ||
        CASE
            WHEN lb.l_bureau_m2 IS NULL THEN 'n.r'::text
            ELSE lb.l_bureau_m2::text
        END) || '</td><td align="center">'::text) ||
        CASE
            WHEN c.pv_commerce_m2 IS NULL THEN 'n.r'::text
            ELSE c.pv_commerce_m2::text
        END) || '</td></tr>
	<tr><td>Commerce</td><td align="center">'::text) ||
        CASE
            WHEN lc.l_commerce_m2 IS NULL THEN 'n.r'::text
            ELSE lc.l_commerce_m2::text
        END) || '</td><td align="center">'::text) ||
        CASE
            WHEN c.pv_commerce_m2 IS NULL THEN 'n.r'::text
            ELSE c.pv_commerce_m2::text
        END) || '</td></tr>
	<tr><td>Activité</td><td align="center">'::text) ||
        CASE
            WHEN la.l_activite_m2 IS NULL THEN 'n.r'::text
            ELSE la.l_activite_m2::text
        END) || '</td><td align="center">'::text) ||
        CASE
            WHEN a.pv_activite_m2 IS NULL THEN 'n.r'::text
            ELSE a.pv_activite_m2::text
        END) || '</td></tr>
	</table>'::text AS tableau_prix
   FROM req_site s
     LEFT JOIN req_pvm2_bureau b ON s.idsite::text = b.idsite::text
     LEFT JOIN req_pvm2_commerce c ON s.idsite::text = c.idsite::text
     LEFT JOIN req_pvm2_activite a ON s.idsite::text = a.idsite::text
     LEFT JOIN req_lm2_bureau lb ON s.idsite::text = lb.idsite::text
     LEFT JOIN req_lm2_commerce lc ON s.idsite::text = lc.idsite::text
     LEFT JOIN req_lm2_activite la ON s.idsite::text = la.idsite::text;

COMMENT ON VIEW m_activite_eco.xapps_an_v_prixloc_stat
    IS 'Vue alphanumérique calculant les prix moyens des bureaux, commerces et activités par site';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_v_repgdsecteur_site_act_api #####################################################################

-- View: m_activite_eco.xapps_an_v_repgdsecteur_site_act_api

-- DROP VIEW m_activite_eco.xapps_an_v_repgdsecteur_site_act_api;

CREATE OR REPLACE VIEW m_activite_eco.xapps_an_v_repgdsecteur_site_act_api
 AS
 WITH req_naf AS (
         SELECT lk.idsite,
            u.code AS code_gdsecteur,
            u.valeur AS lib_gdsecteur,
            count(*) AS nb_etab
           FROM s_sirene.an_etablissement_api s,
            m_activite_eco.lk_eco_etab_site lk,
            s_sirene.naf_n5n1 n15,
            s_sirene.naf4_ursaff n4,
            s_sirene.lt_urssaf u
          WHERE s.siret::text = lk.siret::text AND s.activiteprincipaleetablissement::text = n15.niv5::text AND n15.niv4::text = n4.naf4::text AND n4.codengs::text = u.code::text
          GROUP BY lk.idsite, u.code, u.valeur
          ORDER BY lk.idsite
        ), req_etab_tot AS (
         SELECT lk.idsite,
            count(*) AS nb_etab_tot
           FROM s_sirene.an_etablissement_api s,
            m_activite_eco.lk_eco_etab_site lk
          WHERE s.siret::text = lk.siret::text
          GROUP BY lk.idsite
        )
 SELECT row_number() OVER () AS gid,
    req_etab_tot.idsite,
    req_naf.code_gdsecteur,
    req_naf.lib_gdsecteur,
    req_naf.nb_etab,
    round(req_naf.nb_etab::numeric / req_etab_tot.nb_etab_tot::numeric * 100::numeric, 1) AS tx_gdsecteur
   FROM req_etab_tot,
    req_naf
  WHERE req_etab_tot.idsite::text = req_naf.idsite::text;

COMMENT ON VIEW m_activite_eco.xapps_an_v_repgdsecteur_site_act_api
    IS 'Vue alphanumérique présentant la répartition des établissements (SIRENE et hors SIRENE) par grands secteurs d''activité et par site d''activité.';


-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_v_synt_site_act_api #####################################################################

-- View: m_activite_eco.xapps_an_v_synt_site_act_api

-- DROP VIEW m_activite_eco.xapps_an_v_synt_site_act_api;

CREATE OR REPLACE VIEW m_activite_eco.xapps_an_v_synt_site_act_api
 AS
 WITH req_a AS (
         SELECT geo_eco_site.idsite,
            geo_eco_site.site_nom,
                CASE
                    WHEN geo_eco_site.dest::text = '00'::text THEN 'Non renseigné'::text
                    WHEN geo_eco_site.dest::text = '10'::text THEN 'Artisanat'::text
                    WHEN geo_eco_site.dest::text = '20'::text THEN 'Industrie ou R&D'::text
                    WHEN geo_eco_site.dest::text = '30'::text THEN 'Tertiaire'::text
                    WHEN geo_eco_site.dest::text = '40'::text THEN 'Transport et logistique'::text
                    WHEN geo_eco_site.dest::text = '50'::text THEN 'Commerce'::text
                    WHEN geo_eco_site.dest::text = '60'::text THEN 'Agriculture'::text
                    WHEN geo_eco_site.dest::text = '70'::text THEN 'Service/Négoce'::text
                    ELSE NULL::text
                END AS dest,
                CASE
                    WHEN geo_eco_site.site_voca::text = '00'::text THEN 'Pas de vocation'::text
                    WHEN geo_eco_site.site_voca::text = '10'::text THEN 'ZI - zone industrielle'::text
                    WHEN geo_eco_site.site_voca::text = '20'::text THEN 'ZA - zone artisanale'::text
                    WHEN geo_eco_site.site_voca::text = '30'::text THEN 'ZC - zone commerciale'::text
                    WHEN geo_eco_site.site_voca::text = '40'::text THEN 'ZM - zone mixte'::text
                    ELSE NULL::text
                END AS voca,
            now() AS date_extract,
            round(geo_eco_site.surf_brt::numeric / 10000::numeric, 0) AS surf_site,
            geo_eco_site.p_implant,
                CASE
                    WHEN lt_eco_dest.valeur::text ~~ '%R&D%'::text THEN replace(lt_eco_dest.valeur::text, '&'::text, 'et'::text)::character varying
                    ELSE lt_eco_dest.valeur
                END AS dest_lib,
            geo_eco_site.epci,
                CASE
                    WHEN geo_eco_site.serv_tc IS TRUE AND geo_eco_site.serv_tc_g IS TRUE THEN 'Présence d''un service de transport en commun gratuit'::text
                    WHEN geo_eco_site.serv_tc IS TRUE AND geo_eco_site.serv_tc_g IS FALSE THEN 'Présence d''un service de transport en commun'::text
                    ELSE 'Pas d''offre de transport en commun'::text
                END AS offre_tc,
            ((((('<b>'::text || geo_eco_site.z_auto::text) || '</b> (échangeur '::text) || geo_eco_site.d_auto::text) || ' à '::text) || geo_eco_site.z_dst_auto) || ' km'::text AS acces_a,
            ((('<b>'::text || geo_eco_site.z_fr_v::text) || '</b> à '::text) || geo_eco_site.z_dst_fr_v) || ' km'::text AS acces_gare_v,
            ((('<b>'::text || geo_eco_site.z_fr_f::text) || '</b> à '::text) || geo_eco_site.z_dst_fr_f) || ' km'::text AS acces_gare_f
           FROM m_activite_eco.geo_eco_site
             LEFT JOIN m_activite_eco.lt_eco_dest ON geo_eco_site.site_voca::text = lt_eco_dest.code::text
          ORDER BY geo_eco_site.site_nom
        ), req_f AS (
         SELECT geo_eco_site.idsite,
            count(*) AS nb_etab_sirene
           FROM m_activite_eco.an_eco_etab,
            m_activite_eco.geo_eco_site,
            m_activite_eco.lk_eco_etab_site,
            s_sirene.an_etablissement_api
          WHERE geo_eco_site.idsite::text = lk_eco_etab_site.idsite::text AND lk_eco_etab_site.siret::text = an_eco_etab.idsiret::text AND an_eco_etab.idsiret::text = an_etablissement_api.siret::text AND an_eco_etab.l_compte = true AND an_etablissement_api.etatadministratifetablissement::text = 'A'::text
          GROUP BY geo_eco_site.idsite
        ), req_g AS (
         SELECT geo_eco_site.idsite,
            count(*) AS nb_etab_spe
           FROM m_activite_eco.geo_eco_etabp,
            m_activite_eco.geo_eco_site,
            m_activite_eco.lk_eco_etab_site
          WHERE geo_eco_site.idsite::text = lk_eco_etab_site.idsite::text AND lk_eco_etab_site.siret::text = geo_eco_etabp.idobjet::text AND geo_eco_etabp.l_compte = true
          GROUP BY geo_eco_site.idsite
        ), req_h AS (
         SELECT geo_eco_site.idsite,
            sum(an_eco_etab.eff_etab)::integer AS eff_globaux_sirene
           FROM m_activite_eco.an_eco_etab,
            m_activite_eco.lk_eco_etab_site,
            m_activite_eco.geo_eco_site,
            s_sirene.an_etablissement_api
          WHERE geo_eco_site.idsite::text = lk_eco_etab_site.idsite::text AND lk_eco_etab_site.siret::text = an_eco_etab.idsiret::text AND an_eco_etab.idsiret::text = an_etablissement_api.siret::text AND an_eco_etab.l_compte = true AND an_etablissement_api.etatadministratifetablissement::text = 'A'::text
          GROUP BY geo_eco_site.idsite
        ), req_i AS (
         SELECT geo_eco_site.idsite,
            sum(geo_eco_etabp.eff_etab)::integer AS eff_globaux_etabp
           FROM m_activite_eco.geo_eco_etabp,
            m_activite_eco.lk_eco_etab_site,
            m_activite_eco.geo_eco_site
          WHERE geo_eco_site.idsite::text = lk_eco_etab_site.idsite::text AND lk_eco_etab_site.siret::text = geo_eco_etabp.idobjet::text AND geo_eco_etabp.l_compte = true
          GROUP BY geo_eco_site.idsite
        ), req_j AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dispo_vente
           FROM m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.lk_amt_lot_site,
            m_amenagement.an_amt_lot_stade
          WHERE (an_amt_lot_stade.l_comm2::text = '11'::text OR an_amt_lot_stade.l_comm2::text = '12'::text) AND geo_objet_fon_lot.l_voca::text = '20'::text AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf AND geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dedie_act
           FROM m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf AND (geo_objet_fon_lot.l_voca::text = '20'::text OR geo_objet_fon_lot.l_voca::text = '60'::text) AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k1 AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_reserve_projet
           FROM m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE (an_amt_lot_stade.l_comm2::text = '31'::text OR an_amt_lot_stade.l_comm2::text = '32'::text) AND (geo_objet_fon_lot.l_voca::text = '20'::text OR geo_objet_fon_lot.l_voca::text = '60'::text) AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf AND geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k2 AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dedie_equ
           FROM m_amenagement.old_an_amt_lot_equ,
            m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf AND geo_objet_fon_lot.idgeolf = old_an_amt_lot_equ.idgeolf AND geo_objet_fon_lot.l_voca::text = '10'::text AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k3 AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dedie_mixte
           FROM m_amenagement.old_an_amt_lot_mixte,
            m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf AND geo_objet_fon_lot.idgeolf = old_an_amt_lot_mixte.idgeolf AND geo_objet_fon_lot.l_voca::text = '60'::text AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k4 AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dedie_esppu
           FROM m_amenagement.old_an_amt_lot_esppu,
            m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf AND geo_objet_fon_lot.idgeolf = old_an_amt_lot_esppu.idgeolf AND geo_objet_fon_lot.l_voca::text = '50'::text AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k5 AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dedie_divers
           FROM m_amenagement.an_amt_lot_divers,
            m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf AND geo_objet_fon_lot.idgeolf = an_amt_lot_divers.idgeolf AND geo_objet_fon_lot.l_voca::text = '40'::text AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_k6 AS (
         SELECT geo_eco_site.idsite,
                CASE
                    WHEN sum(geo_objet_fon_lot.surf) IS NOT NULL THEN
                    CASE
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) >= 1 AND length(sum(geo_objet_fon_lot.surf)::character varying::text) <= 3 THEN sum(geo_objet_fon_lot.surf)::character varying::text || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 4 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 5 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 6 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 7 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM9G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        WHEN length(sum(geo_objet_fon_lot.surf)::character varying::text) = 8 THEN replace(to_char(sum(geo_objet_fon_lot.surf), 'FM99G999G999'::text), ','::text, ' '::text) || ' m²'::text
                        ELSE NULL::text
                    END
                    ELSE '0 m²'::text
                END AS surf_dedie_hab
           FROM m_amenagement.old_an_amt_lot_hab,
            m_activite_eco.geo_eco_site,
            r_objet.geo_objet_fon_lot,
            m_amenagement.an_amt_lot_stade,
            m_amenagement.lk_amt_lot_site
          WHERE geo_eco_site.idsite::text = lk_amt_lot_site.idsite::text AND lk_amt_lot_site.idgeolf = geo_objet_fon_lot.idgeolf AND geo_objet_fon_lot.idgeolf = old_an_amt_lot_hab.idgeolf AND geo_objet_fon_lot.l_voca::text = '30'::text AND an_amt_lot_stade.idgeolf = geo_objet_fon_lot.idgeolf
          GROUP BY geo_eco_site.idsite
        ), req_m AS (
         SELECT e.idsite,
            string_agg('• '::text || li.nom, '<br>'::text ORDER BY li.eff_etab DESC) AS liste_ent
           FROM m_activite_eco.geo_eco_site e,
            m_activite_eco.xapps_an_vmr_site_act_10 li
          WHERE e.idsite::text = li.idsite::text
          GROUP BY e.idsite
        ), req_p AS (
         SELECT e.idsite,
            string_agg(('<div align=center><img src="https://geo.compiegnois.fr/documents/metiers/eco/doc/site/'::text || m_1.n_fichier) || '" alt="" width=50% heigth=35%></img></div>'::text, '<br>'::text) AS url_photo
           FROM m_activite_eco.geo_eco_site e,
            m_activite_eco.an_eco_media m_1
          WHERE e.idsite::text = m_1.id AND m_1.alaunesite IS TRUE
          GROUP BY e.idsite
         LIMIT 1
        )
 SELECT a.idsite AS "Identifiant du site",
    a.site_nom AS "Libellé du site",
    a.dest AS "Destination du site",
    a.voca AS "Vocation du site",
    a.surf_site AS "Surface du site",
        CASE
            WHEN a.p_implant IS NOT NULL OR a.p_implant::text <> ''::text THEN a.p_implant
            ELSE 'inconnue'::character varying
        END AS "Début de commercialisation",
    m.liste_ent,
    p.url_photo,
    a.dest_lib,
    a.offre_tc,
    a.acces_a,
    a.acces_gare_v,
    a.acces_gare_f,
    a.date_extract AS "Extraction des données le",
        CASE
            WHEN f.nb_etab_sirene::integer >= 0 AND g.nb_etab_spe::integer >= 0 THEN (f.nb_etab_sirene::integer + g.nb_etab_spe::integer)::text
            WHEN f.nb_etab_sirene::integer >= 0 AND g.nb_etab_spe::integer IS NULL THEN f.nb_etab_sirene::text
            WHEN f.nb_etab_sirene::integer IS NULL AND g.nb_etab_spe::integer >= 0 THEN g.nb_etab_spe::text
            ELSE '0'::text
        END AS nb_etab,
        CASE
            WHEN h.eff_globaux_sirene >= 0 AND i.eff_globaux_etabp >= 0 THEN (h.eff_globaux_sirene + i.eff_globaux_etabp)::text
            WHEN h.eff_globaux_sirene >= 0 AND i.eff_globaux_etabp IS NULL THEN h.eff_globaux_sirene::text
            WHEN h.eff_globaux_sirene IS NULL AND i.eff_globaux_etabp >= 0 THEN i.eff_globaux_etabp::text
            ELSE '0'::text
        END AS eff_etab,
        CASE
            WHEN j.surf_dispo_vente IS NOT NULL THEN j.surf_dispo_vente::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface disponible à la vente",
        CASE
            WHEN k.surf_dedie_act IS NOT NULL THEN k.surf_dedie_act::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface dédiée pour des activités",
        CASE
            WHEN k1.surf_reserve_projet IS NOT NULL THEN k1.surf_reserve_projet::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface réservée pour des projets en cours",
        CASE
            WHEN k2.surf_dedie_equ IS NOT NULL THEN k2.surf_dedie_equ::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface dédiée pour des équipements",
        CASE
            WHEN k3.surf_dedie_mixte IS NOT NULL THEN k3.surf_dedie_mixte::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface dédiée en mixité (économie/habitat)",
        CASE
            WHEN k4.surf_dedie_esppu IS NOT NULL THEN k4.surf_dedie_esppu::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface dédiée pour espace public",
        CASE
            WHEN k5.surf_dedie_divers IS NOT NULL THEN k5.surf_dedie_divers::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface dédiée en divers",
        CASE
            WHEN k6.surf_dedie_hab IS NOT NULL THEN k6.surf_dedie_hab::character varying
            ELSE 'Plus de disponibilités ou m² inconnu'::character varying
        END AS "Surface dédiée en habitat",
    a.epci
   FROM req_a a
     LEFT JOIN req_f f ON a.idsite::text = f.idsite::text
     LEFT JOIN req_g g ON a.idsite::text = g.idsite::text
     LEFT JOIN req_h h ON a.idsite::text = h.idsite::text
     LEFT JOIN req_i i ON a.idsite::text = i.idsite::text
     LEFT JOIN req_j j ON a.idsite::text = j.idsite::text
     LEFT JOIN req_k k ON a.idsite::text = k.idsite::text
     LEFT JOIN req_k1 k1 ON a.idsite::text = k1.idsite::text
     LEFT JOIN req_k2 k2 ON a.idsite::text = k2.idsite::text
     LEFT JOIN req_k3 k3 ON a.idsite::text = k3.idsite::text
     LEFT JOIN req_k4 k4 ON a.idsite::text = k4.idsite::text
     LEFT JOIN req_k5 k5 ON a.idsite::text = k5.idsite::text
     LEFT JOIN req_k6 k6 ON a.idsite::text = k6.idsite::text
     LEFT JOIN req_m m ON a.idsite::text = m.idsite::text
     LEFT JOIN req_p p ON a.idsite::text = p.idsite::text;

COMMENT ON VIEW m_activite_eco.xapps_an_v_synt_site_act_api
    IS 'Vue présentant les données de synthèses à l''échelle du site d''activité  (données sur l''environnement économique et statistiques foncières présentes sur le fiche d''information du site dans l''application métier GEO). Cette vue est rafraichie toutes les nuits par une tache CRON sur le serveur sig-sgbd.';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_v_eco_epci_tab #####################################################################

-- View: m_activite_eco.xapps_geo_v_eco_epci_tab

-- DROP VIEW m_activite_eco.xapps_geo_v_eco_epci_tab;

CREATE OR REPLACE VIEW m_activite_eco.xapps_geo_v_eco_epci_tab
 AS
 WITH req_epci AS (
         SELECT geo_vm_osm_epci_grdc.cepci,
            geo_vm_osm_epci_grdc.lib_epci,
                CASE
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
                CASE
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '200067965'::text THEN 'https://websigoval.oiselavallee.org/adws/app/bd77fdb2-4d07-11ec-b66d-b90bbf118374/index.html'::text
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '246000749'::text THEN 'https://websigoval.oiselavallee.org/adws/app/c3f2c215-4d0e-11ec-b66d-b90bbf118374/index.html'::text
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '246000772'::text THEN 'https://websigoval.oiselavallee.org/adws/app/594fbf03-4d0b-11ec-b66d-b90bbf118374/index.html'::text
                    WHEN geo_vm_osm_epci_grdc.cepci::text = '246000897'::text THEN 'https://websigoval.oiselavallee.org/adws/app/bab8d623-4d0a-11ec-b66d-b90bbf118374/index.html'::text
                    ELSE NULL::text
                END AS url_olv
           FROM r_osm.geo_vm_osm_epci_grdc
        ), req_site AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            count(*) AS nb_site,
            round(sum(s_1.surf_brt)::numeric / 10000::numeric, 0) AS surf_tot_site
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1
          WHERE st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE
          GROUP BY e_1.cepci
        ), req_zae AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            count(*) AS nb_zae
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1
          WHERE st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND s_1.typsite::text = '10'::text
          GROUP BY e_1.cepci
        ), req_horszae AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            count(*) AS nb_horszae
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1
          WHERE st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND s_1.typsite::text = '20'::text
          GROUP BY e_1.cepci
        ), req_autresite AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            count(*) AS nb_autresite
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1
          WHERE st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND s_1.typsite::text = '30'::text
          GROUP BY e_1.cepci
        ), req_enveco_site AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(ss.nb_etab::integer) AS nb_entreprise,
            sum(ss.eff_etab::integer) AS nb_emploi
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            m_activite_eco.xapps_an_v_synt_site_act_api ss
          WHERE ss."Identifiant du site"::text = s_1.idsite::text AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND (s_1.typsite::text = '10'::text OR s_1.typsite::text = '20'::text)
          GROUP BY e_1.cepci
        ), req_enveco_zae AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(ss.nb_etab::integer) AS nb_entreprise_zae,
            sum(ss.eff_etab::integer) AS nb_emploi_zae
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            m_activite_eco.xapps_an_v_synt_site_act_api ss
          WHERE ss."Identifiant du site"::text = s_1.idsite::text AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND s_1.typsite::text = '10'::text
          GROUP BY e_1.cepci
        ), req_enveco_horszae AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(ss.nb_etab::integer) AS nb_entreprise_horszae,
            sum(ss.eff_etab::integer) AS nb_emploi_horszae
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            m_activite_eco.xapps_an_v_synt_site_act_api ss
          WHERE ss."Identifiant du site"::text = s_1.idsite::text AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND s_1.typsite::text = '20'::text
          GROUP BY e_1.cepci
        ), req_enveco_envente AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(o.surf) AS lot_envente
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            r_objet.geo_objet_fon_lot o,
            m_activite_eco.an_eco_lot l,
            m_amenagement.an_amt_lot_stade sa_1,
            m_amenagement.lk_amt_lot_site lkl
          WHERE s_1.idsite::text = lkl.idsite::text AND lkl.idgeolf = l.idgeolf AND sa_1.idgeolf = l.idgeolf AND o.idgeolf = l.idgeolf AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND (sa_1.l_comm2::text = ANY (ARRAY['11'::character varying::text, '12'::character varying::text])) AND o.l_voca::text = '20'::text
          GROUP BY e_1.cepci
        ), req_enveco_vendu AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(o.surf) AS lot_vendu
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            r_objet.geo_objet_fon_lot o,
            m_activite_eco.an_eco_lot l,
            m_amenagement.an_amt_lot_stade sa_1,
            m_amenagement.lk_amt_lot_site lkl
          WHERE s_1.idsite::text = lkl.idsite::text AND lkl.idgeolf = l.idgeolf AND sa_1.idgeolf = l.idgeolf AND o.idgeolf = l.idgeolf AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND sa_1.l_comm2::text = '20'::text AND o.l_voca::text = '20'::text
          GROUP BY e_1.cepci
        ), req_enveco_reserve AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(o.surf) AS lot_reserve
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            r_objet.geo_objet_fon_lot o,
            m_activite_eco.an_eco_lot l,
            m_amenagement.an_amt_lot_stade sa_1,
            m_amenagement.lk_amt_lot_site lkl
          WHERE s_1.idsite::text = lkl.idsite::text AND lkl.idgeolf = l.idgeolf AND sa_1.idgeolf = l.idgeolf AND o.idgeolf = l.idgeolf AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND (sa_1.l_comm2::text = ANY (ARRAY['31'::character varying::text, '32'::character varying::text])) AND o.l_voca::text = '20'::text
          GROUP BY e_1.cepci
        ), req_enveco_noncom AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(o.surf) AS lot_noncom
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            r_objet.geo_objet_fon_lot o,
            m_activite_eco.an_eco_lot l,
            m_amenagement.an_amt_lot_stade sa_1,
            m_amenagement.lk_amt_lot_site lkl
          WHERE s_1.idsite::text = lkl.idsite::text AND lkl.idgeolf = l.idgeolf AND sa_1.idgeolf = l.idgeolf AND o.idgeolf = l.idgeolf AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND sa_1.l_comm2::text = '99'::text
          GROUP BY e_1.cepci
        ), req_enveco_nr AS (
         SELECT
                CASE
                    WHEN e_1.cepci::text = '200067965'::text THEN 'arc'::text
                    WHEN e_1.cepci::text = '246000749'::text THEN 'cclo'::text
                    WHEN e_1.cepci::text = '246000772'::text THEN 'cc2v'::text
                    WHEN e_1.cepci::text = '246000897'::text THEN 'ccpe'::text
                    ELSE NULL::text
                END AS nom_epci,
            sum(o.surf) AS lot_nr
           FROM m_activite_eco.geo_eco_site s_1,
            r_osm.geo_vm_osm_epci_grdc e_1,
            r_objet.geo_objet_fon_lot o,
            m_activite_eco.an_eco_lot l,
            m_amenagement.an_amt_lot_stade sa_1,
            m_amenagement.lk_amt_lot_site lkl
          WHERE s_1.idsite::text = lkl.idsite::text AND lkl.idgeolf = l.idgeolf AND sa_1.idgeolf = l.idgeolf AND o.idgeolf = l.idgeolf AND st_intersects(st_pointonsurface(s_1.geom), e_1.geom) IS TRUE AND sa_1.l_comm2::text = '00'::text
          GROUP BY e_1.cepci
        )
 SELECT e.cepci,
    e.lib_epci,
    e.nom_epci,
    e.url_olv,
    s.nb_site,
    s.surf_tot_site,
        CASE
            WHEN z.nb_zae IS NULL THEN 0::bigint
            ELSE z.nb_zae
        END AS nb_zae,
        CASE
            WHEN hz.nb_horszae IS NULL THEN 0::bigint
            ELSE hz.nb_horszae
        END AS nb_horszae,
        CASE
            WHEN sa.nb_autresite IS NULL THEN 0::bigint
            ELSE sa.nb_autresite
        END AS nb_autresite,
        CASE
            WHEN env.nb_emploi IS NULL THEN 0::bigint
            ELSE env.nb_emploi
        END AS nb_emploi,
        CASE
            WHEN env.nb_entreprise IS NULL THEN 0::bigint
            ELSE env.nb_entreprise
        END AS nb_entreprise,
        CASE
            WHEN env1.nb_emploi_zae IS NULL THEN 0::bigint
            ELSE env1.nb_emploi_zae
        END AS nb_emploi_zae,
        CASE
            WHEN env1.nb_entreprise_zae IS NULL THEN 0::bigint
            ELSE env1.nb_entreprise_zae
        END AS nb_entreprise_zae,
        CASE
            WHEN env2.nb_emploi_horszae IS NULL THEN 0::bigint
            ELSE env2.nb_emploi_horszae
        END AS nb_emploi_horszae,
        CASE
            WHEN env2.nb_entreprise_horszae IS NULL THEN 0::bigint
            ELSE env2.nb_entreprise_horszae
        END AS nb_entreprise_horszae,
        CASE
            WHEN cev.lot_envente IS NULL THEN 0::bigint
            ELSE cev.lot_envente
        END AS lot_envente,
        CASE
            WHEN cv.lot_vendu IS NULL THEN 0::bigint
            ELSE cv.lot_vendu
        END AS lot_vendu,
        CASE
            WHEN cr.lot_reserve IS NULL THEN 0::bigint
            ELSE cr.lot_reserve
        END AS lot_reserve,
        CASE
            WHEN cnm.lot_noncom IS NULL THEN 0::bigint
            ELSE cnm.lot_noncom
        END AS lot_noncom,
        CASE
            WHEN cnr.lot_nr IS NULL THEN 0::bigint
            ELSE cnr.lot_nr
        END AS lot_nr
   FROM req_epci e
     LEFT JOIN req_site s ON e.nom_epci = s.nom_epci
     LEFT JOIN req_zae z ON e.nom_epci = z.nom_epci
     LEFT JOIN req_horszae hz ON e.nom_epci = hz.nom_epci
     LEFT JOIN req_autresite sa ON e.nom_epci = sa.nom_epci
     LEFT JOIN req_enveco_site env ON e.nom_epci = env.nom_epci
     LEFT JOIN req_enveco_zae env1 ON e.nom_epci = env1.nom_epci
     LEFT JOIN req_enveco_horszae env2 ON e.nom_epci = env2.nom_epci
     LEFT JOIN req_enveco_envente cev ON e.nom_epci = cev.nom_epci
     LEFT JOIN req_enveco_vendu cv ON e.nom_epci = cv.nom_epci
     LEFT JOIN req_enveco_reserve cr ON e.nom_epci = cr.nom_epci
     LEFT JOIN req_enveco_noncom cnm ON e.nom_epci = cnm.nom_epci
     LEFT JOIN req_enveco_nr cnr ON e.nom_epci = cnr.nom_epci;


COMMENT ON VIEW m_activite_eco.xapps_geo_v_eco_epci_tab
    IS 'Vue alphanumérique ddes indicateurs EPCI pour tableau de bord synthétique n°1 dans GEO';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_v_eco_loc_dispo #####################################################################

-- View: m_activite_eco.xapps_geo_v_eco_loc_dispo

-- DROP VIEW m_activite_eco.xapps_geo_v_eco_loc_dispo;

CREATE OR REPLACE VIEW m_activite_eco.xapps_geo_v_eco_loc_dispo
 AS
 SELECT row_number() OVER () AS gid,
    lo.id,
    a.idloc,
        CASE
            WHEN lo.occup::text = ANY (ARRAY['10'::character varying::text, '11'::character varying::text, '30'::character varying::text, '31'::character varying::text]) THEN 'En vente'::character varying
            WHEN lo.occup::text = ANY (ARRAY['20'::character varying::text, '21'::character varying::text, '30'::character varying::text, '31'::character varying::text]) THEN 'En location'::character varying
            ELSE l.valeur
        END AS affiche_dispo_fiche_bien,
    l1.valeur AS occupation,
    a.lib_bati,
        CASE
            WHEN lo.libelle IS NOT NULL OR lo.libelle::text <> ''::text THEN lo.libelle
            ELSE ''::character varying
        END AS libelle,
        CASE
            WHEN lo.surf_p IS NOT NULL THEN lo.surf_p::text
            ELSE ' - '::text
        END || 'm²'::text AS surface,
        CASE
            WHEN lo.loyer IS NOT NULL THEN lo.loyer::text
            ELSE ' - '::text
        END || '€'::text AS loyer,
        CASE
            WHEN lo.loyerm2 IS NOT NULL THEN lo.loyerm2::text
            ELSE ' - '::text
        END || '€'::text AS loyerm2,
        CASE
            WHEN lo.pvente IS NOT NULL THEN lo.pvente::text
            ELSE ' - '::text
        END || '€'::text AS pvente,
    lo.descript,
    ('<b>'::text || (( SELECT string_agg(s.site_nom::text, chr(10)) AS string_agg
           FROM m_activite_eco.geo_eco_site s,
            m_activite_eco.lk_eco_loc_site rels
          WHERE s.idsite::text = rels.idsite::text AND rels.idloc::text = a.idloc::text))) || '</b>'::text AS site,
    ( SELECT string_agg(s.idsite::text, ','::text) AS string_agg
           FROM m_activite_eco.geo_eco_site s,
            m_activite_eco.lk_eco_loc_site rels
          WHERE s.idsite::text = rels.idsite::text AND rels.idloc::text = a.idloc::text) AS idsite,
        CASE
            WHEN lo.occup::text = ANY (ARRAY['10'::character varying::text, '11'::character varying::text, '30'::character varying::text, '31'::character varying::text]) THEN lo.libelle::text || ' - en vente'::text
            WHEN lo.occup::text = ANY (ARRAY['20'::character varying::text, '21'::character varying::text, '30'::character varying::text, '31'::character varying::text]) THEN lo.libelle::text || ' - en location'::text
            ELSE (lo.libelle::text || ' - '::text) || ((( SELECT lt_eco_occuploc.valeur
               FROM m_activite_eco.lt_eco_occuploc
              WHERE lt_eco_occuploc.code::text = lo.occup::text))::text)
        END AS affiche_dispo,
    a.commune,
    a.typ1,
    a.epci,
    a.adresse_a,
    st_buffer(a.geom, 6::double precision) AS geom
   FROM m_activite_eco.geo_eco_loc_act a
     LEFT JOIN m_activite_eco.an_eco_loc_act lo ON lo.idloc::text = a.idloc::text
     JOIN m_activite_eco.lt_eco_occuploc l ON lo.occup::text = l.code::text
     JOIN m_activite_eco.lt_eco_typoccup l1 ON lo.typ2::text = l1.code::text
  WHERE lo.occup::text = ANY (ARRAY['10'::character varying::text, '11'::character varying::text, '20'::character varying::text, '21'::character varying::text, '30'::character varying::text, '31'::character varying::text]);

COMMENT ON VIEW m_activite_eco.xapps_geo_v_eco_loc_dispo
    IS 'Vue géographique recensant l''ensemble des locaux disponibles';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_v_etab_compte_naf #####################################################################

-- View: m_activite_eco.xapps_geo_v_etab_compte_naf

-- DROP VIEW m_activite_eco.xapps_geo_v_etab_compte_naf;

CREATE OR REPLACE VIEW m_activite_eco.xapps_geo_v_etab_compte_naf
 AS
 SELECT s.siret,
    replace(s.nom_etab_geo::text, '<br>'::text, chr(10)) AS nom_etab,
    n.code AS naf,
    n.valeur AS lib_naf,
    a.geom
   FROM s_sirene.an_etablissement_api s,
    m_activite_eco.lk_adresseetablissement lk,
    m_activite_eco.an_eco_etab e,
    x_apps.xapps_geo_vmr_adresse a,
    s_sirene.lt_nafrev2 n
  WHERE e.l_compte = true AND s.siret::text = lk.siret::text AND s.siret::text = e.idsiret::text AND a.id_adresse = lk.idadresse AND n.code::text = s.activiteprincipaleetablissement::text;


COMMENT ON VIEW m_activite_eco.xapps_geo_v_etab_compte_naf
    IS 'Vue géographique localisant les établissements SIRENE comptabilisés à l''adresse';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_v_zae #####################################################################

-- View: m_activite_eco.xapps_geo_v_zae

-- DROP VIEW m_activite_eco.xapps_geo_v_zae;

CREATE OR REPLACE VIEW m_activite_eco.xapps_geo_v_zae
 AS
 SELECT geo_eco_site.idsite,
    geo_eco_site.idsitereg,
    geo_eco_site.idpole,
    geo_eco_site.site_nom,
    geo_eco_site.site_voca,
    geo_eco_site.site_etat,
    geo_eco_site.typsite,
    geo_eco_site.typo,
    geo_eco_site.dest,
    geo_eco_site.dest_autre,
    geo_eco_site.date_crea,
    geo_eco_site.p_implant,
    geo_eco_site.commune,
    geo_eco_site.z_proced,
    geo_eco_site.surf_brt,
    geo_eco_site.z_mai_ouvr,
    geo_eco_site.z_compet,
    geo_eco_site.z_amng,
    geo_eco_site.z_gest,
    geo_eco_site.z_anim,
    geo_eco_site.z_comm,
    geo_eco_site.z_cession,
    geo_eco_site.z_env,
    geo_eco_site.z_paysage,
    geo_eco_site.z_rehab,
    geo_eco_site.z_epu,
    geo_eco_site.z_dechet,
    geo_eco_site.z_tr_slect,
    geo_eco_site.res_ass,
    geo_eco_site.res_pluvia,
    geo_eco_site.res_eau,
    geo_eco_site.res_gaz,
    geo_eco_site.res_elect,
    geo_eco_site.res_fibre,
    geo_eco_site.res_autre,
    geo_eco_site.z_auto,
    geo_eco_site.d_auto,
    geo_eco_site.z_dst_auto,
    geo_eco_site.z_tps_auto,
    geo_eco_site.z_ar_f,
    geo_eco_site.z_dst_ar_f,
    geo_eco_site.z_ar_v,
    geo_eco_site.z_dst_ar_v,
    geo_eco_site.z_fr_f,
    geo_eco_site.z_dst_fr_f,
    geo_eco_site.z_fr_v,
    geo_eco_site.z_dst_fr_v,
    geo_eco_site.z_pmm,
    geo_eco_site.z_dst_pmm,
    geo_eco_site.serv_tc,
    geo_eco_site.serv_tc_nb,
    geo_eco_site.serv_tc_fr,
    geo_eco_site.serv_tc_g,
    geo_eco_site.circ_douce,
    geo_eco_site.serv_rest,
    geo_eco_site.serv_rest_nb,
    geo_eco_site.serv_crech,
    geo_eco_site.serv_crech_nb,
    geo_eco_site.serv_autre,
    geo_eco_site.z_aide_pb,
    geo_eco_site.src_geom,
    geo_eco_site.src_date,
    geo_eco_site.date_sai,
    geo_eco_site.date_maj,
    geo_eco_site.op_sai,
    geo_eco_site.op_maj,
    geo_eco_site.epci,
    geo_eco_site.observ,
    geo_eco_site.geom,
    geo_eco_site.geom1
   FROM m_activite_eco.geo_eco_site
  WHERE geo_eco_site.typsite::text = '10'::text;

COMMENT ON VIEW m_activite_eco.xapps_geo_v_zae
    IS 'Vue géographique des ZAE uniquement (zone d''activités économiques de compétence intercommunale), permet d''exporter un shape pour l''open data et d''être utilisé dans les applications ou carte';


-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_vmr_evo_site_act_api #####################################################################

-- View: m_activite_eco.xapps_an_vmr_evo_site_act_api

-- DROP MATERIALIZED VIEW m_activite_eco.xapps_an_vmr_evo_site_act_api;

CREATE MATERIALIZED VIEW m_activite_eco.xapps_an_vmr_evo_site_act_api
TABLESPACE pg_default
AS
 SELECT h_an_eco_site_emploi.idsite,
    h_an_eco_site_emploi.annee_h AS annee,
    h_an_eco_site_emploi.eff_etab,
    h_an_eco_site_emploi.nb_etab
   FROM m_activite_eco.h_an_eco_site_emploi
  ORDER BY h_an_eco_site_emploi.idsite, h_an_eco_site_emploi.annee_h
WITH DATA;

COMMENT ON MATERIALIZED VIEW m_activite_eco.xapps_an_vmr_evo_site_act_api
    IS 'Vue matérialisée présentant les données d''évolution des emplois et des établissements à l''échelle du site d''activité pour le graphique d''évolution dans GEO';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_an_vmr_site_act_10 #####################################################################

-- View: m_activite_eco.xapps_an_vmr_site_act_10

-- DROP MATERIALIZED VIEW m_activite_eco.xapps_an_vmr_site_act_10;

CREATE MATERIALIZED VIEW m_activite_eco.xapps_an_vmr_site_act_10
TABLESPACE pg_default
AS
 WITH req_t AS (
         WITH req_ul AS (
                 SELECT DISTINCT u_1.siren,
                    u_1.denominationunitelegale,
                    u_1.denominationusuelle1unitelegale,
                    u_1.nomunitelegale,
                    u_1.prenom1unitelegale
                   FROM s_sirene.an_unitelegale_api u_1
                )
         SELECT si.idsite,
            row_number() OVER (PARTITION BY si.idsite ORDER BY e.eff_etab DESC) AS liste,
                CASE
                    WHEN length(e.l_nom::text) > 0 THEN e.l_nom
                    WHEN length(s.denominationusuelleetablissement::text) > 0 THEN s.denominationusuelleetablissement
                    WHEN length(u.denominationunitelegale::text) > 0 THEN u.denominationunitelegale
                    WHEN length(u.denominationusuelle1unitelegale::text) > 0 THEN u.denominationusuelle1unitelegale
                    WHEN length(u.nomunitelegale::text) > 0 AND length(u.prenom1unitelegale::text) > 0 THEN (u.nomunitelegale::text || u.prenom1unitelegale::text)::character varying
                    ELSE NULL::character varying
                END::text ||
                CASE
                    WHEN length(s.enseigne1etablissement::text) > 0 AND s.enseigne1etablissement::text <>
                    CASE
                        WHEN length(e.l_nom::text) > 0 THEN e.l_nom
                        WHEN length(s.denominationusuelleetablissement::text) > 0 THEN s.denominationusuelleetablissement
                        WHEN length(u.denominationunitelegale::text) > 0 THEN u.denominationunitelegale
                        WHEN length(u.denominationusuelle1unitelegale::text) > 0 THEN u.denominationusuelle1unitelegale
                        WHEN length(u.nomunitelegale::text) > 0 AND length(u.prenom1unitelegale::text) > 0 THEN (u.nomunitelegale::text || u.prenom1unitelegale::text)::character varying
                        ELSE NULL::character varying
                    END::text THEN ', '::text || s.enseigne1etablissement::text
                    ELSE ''::text
                END AS nom,
            e.eff_etab,
                CASE
                    WHEN e.source_eff::text = 'SIRENE'::text THEN 'Insee (SIRENE)'::character varying
                    ELSE 'ARC'::character varying
                END AS source_eff
           FROM m_activite_eco.an_eco_etab e,
            m_activite_eco.geo_eco_site si,
            m_activite_eco.lk_eco_etab_site lk,
            s_sirene.an_etablissement_api s,
            req_ul u
          WHERE e.idsiret::text = s.siret::text AND s.siren::text = u.siren::text AND si.idsite::text = lk.idsite::text AND lk.siret::text = e.idsiret::text AND e.l_compte IS TRUE AND e.etatadministratifetablissement::text = 'A'::text AND e.eff_etab > 0
          ORDER BY si.idsite, (row_number() OVER (PARTITION BY si.idsite ORDER BY e.eff_etab DESC)), e.eff_etab DESC
        )
 SELECT row_number() OVER () AS gid,
    t.idsite,
    t.nom,
    t.eff_etab,
    t.source_eff
   FROM req_t t
  WHERE t.liste <= 10
WITH DATA;

COMMENT ON MATERIALIZED VIEW m_activite_eco.xapps_an_vmr_site_act_10
    IS 'Extraction de la liste des 10 premières entreprises en terme d''emplois par site d''activité. Vue matérialisée rafraichie toutes les nuits.';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_vmr_etab_api #####################################################################

-- View: m_activite_eco.xapps_geo_vmr_etab_api

-- DROP MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_etab_api;

CREATE MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_etab_api
TABLESPACE pg_default
AS
 WITH req_tout_etab AS (
         WITH req_tot AS (
                 WITH req_t AS (
                         SELECT DISTINCT row_number() OVER () AS gid,
                            a_1.id_adresse,
                            a_1.insee,
                            count(*) AS nb_etab_t
                           FROM m_activite_eco.an_eco_etab e,
                            x_apps.xapps_geo_vmr_adresse a_1,
                            m_activite_eco.lk_adresseetablissement l
                          WHERE l.idadresse = a_1.id_adresse AND l.siret::text = e.idsiret::text AND e.etatadministratifetablissement::text = 'A'::text AND e.l_compte IS TRUE
                          GROUP BY a_1.id_adresse, a_1.insee
                        ), req_f AS (
                         SELECT DISTINCT row_number() OVER () AS gid,
                            a_1.id_adresse,
                            a_1.insee,
                            count(*) AS nb_etab_f
                           FROM m_activite_eco.an_eco_etab e,
                            x_apps.xapps_geo_vmr_adresse a_1,
                            m_activite_eco.lk_adresseetablissement l
                          WHERE l.idadresse = a_1.id_adresse AND l.siret::text = e.idsiret::text AND e.etatadministratifetablissement::text = 'A'::text AND e.l_compte IS FALSE
                          GROUP BY a_1.id_adresse, a_1.insee
                        ), req_a AS (
                         SELECT DISTINCT a_1.id_adresse,
                            a_1.insee,
                            count(*) AS nb_etab
                           FROM m_activite_eco.an_eco_etab e,
                            x_apps.xapps_geo_vmr_adresse a_1,
                            m_activite_eco.lk_adresseetablissement l
                          WHERE l.idadresse = a_1.id_adresse AND l.siret::text = e.idsiret::text AND e.etatadministratifetablissement::text = 'A'::text
                          GROUP BY a_1.id_adresse, a_1.insee
                        )
                 SELECT row_number() OVER () AS gid,
                    a.id_adresse,
                    (((((
                        CASE
                            WHEN a.etiquette::text IS NULL OR a.etiquette::text = ''::text THEN a.numero::text
                            ELSE a.etiquette::text
                        END || ' '::text) || btrim(a.libvoie_c::text)) || ' '::text) || a.codepostal::text) || ' '::text) || upper(unaccent_string(a.commune::text)) AS lib_adresse,
                    req_a.nb_etab,
                        CASE
                            WHEN req_t.nb_etab_t > 0 THEN req_t.nb_etab_t
                            ELSE 0::bigint
                        END AS nb_etab_t,
                        CASE
                            WHEN req_f.nb_etab_f > 0 THEN req_f.nb_etab_f
                            ELSE 0::bigint
                        END AS nb_etab_f,
                    a.etiquette,
                    a.angle,
                    a.insee,
                    a.geom
                   FROM x_apps.xapps_geo_vmr_adresse a
                     LEFT JOIN req_a ON req_a.id_adresse = a.id_adresse
                     LEFT JOIN req_t ON req_t.id_adresse = req_a.id_adresse
                     LEFT JOIN req_f ON req_f.id_adresse = req_a.id_adresse
                ), req_naf AS (
                 SELECT DISTINCT a_1.id_adresse,
                    n.niv1
                   FROM m_activite_eco.an_eco_etab e,
                    x_apps.xapps_geo_vmr_adresse a_1,
                    s_sirene.an_etablissement_api s,
                    m_activite_eco.lk_adresseetablissement l,
                    s_sirene.naf_n5n1 n
                  WHERE l.idadresse = a_1.id_adresse AND l.siret::text = e.idsiret::text AND s.siret::text = e.idsiret::text AND n.niv5::text = s.activiteprincipaleetablissement::text AND s.nomenclatureactiviteprincipaleetablissement::text = 'NAFRev2'::text AND e.etatadministratifetablissement::text = 'A'::text
                  GROUP BY a_1.id_adresse, n.niv1
                  ORDER BY a_1.id_adresse
                ), req_nace AS (
                 SELECT DISTINCT a_1.id_adresse,
                    u.code AS secteur_urssaf,
                    u.valeur AS lib_secteur_urssaf
                   FROM m_activite_eco.an_eco_etab e,
                    x_apps.xapps_geo_vmr_adresse a_1,
                    s_sirene.an_etablissement_api s,
                    m_activite_eco.lk_adresseetablissement l,
                    s_sirene.naf_n5n1 n,
                    s_sirene.naf4_ursaff n4,
                    s_sirene.lt_urssaf u
                  WHERE l.idadresse = a_1.id_adresse AND l.siret::text = e.idsiret::text AND s.siret::text = e.idsiret::text AND n.niv5::text = s.activiteprincipaleetablissement::text AND n.niv4::text = n4.naf4::text AND n4.codengs::text = u.code::text AND s.nomenclatureactiviteprincipaleetablissement::text = 'NAFRev2'::text AND e.etatadministratifetablissement::text = 'A'::text
                  GROUP BY a_1.id_adresse, u.valeur, u.code
                  ORDER BY a_1.id_adresse
                )
         SELECT req_tot.gid,
            req_tot.id_adresse,
            req_tot.lib_adresse,
            req_tot.nb_etab,
            req_tot.nb_etab_t,
            req_tot.nb_etab_f,
            req_tot.etiquette,
            req_tot.angle,
            req_tot.insee,
                CASE
                    WHEN length(string_agg(req_naf.niv1::text, ''::text)) = 1 THEN string_agg(req_naf.niv1::text, ''::text)
                    ELSE 'n.r'::text
                END AS naf5,
                CASE
                    WHEN length(string_agg(req_nace.secteur_urssaf::text, ''::text)) = 2 THEN string_agg(req_nace.secteur_urssaf::text, ''::text)
                    ELSE 'n.r'::text
                END AS secteur_urssaf,
            req_tot.geom
           FROM req_tot
             LEFT JOIN req_naf ON req_tot.id_adresse = req_naf.id_adresse
             LEFT JOIN req_nace ON req_tot.id_adresse = req_nace.id_adresse
          WHERE req_naf.niv1 IS NOT NULL
          GROUP BY req_tot.gid, req_tot.id_adresse, req_tot.lib_adresse, req_tot.nb_etab, req_tot.nb_etab_t, req_tot.nb_etab_f, req_tot.etiquette, req_tot.angle, req_tot.insee, req_tot.geom
        UNION ALL
         SELECT DISTINCT e.idgeoet,
            0 AS id_adresse,
            ''::text AS lib_adresse,
            1 AS nb_etab,
                CASE
                    WHEN e.l_compte IS TRUE THEN 1
                    ELSE NULL::integer
                END AS nb_etab_t,
                CASE
                    WHEN e.l_compte IS FALSE THEN 0
                    ELSE NULL::integer
                END AS nb_etab_f,
            e.nom AS etiquette,
            0 AS angle,
            e.insee,
            '99999'::text AS naf5,
                CASE
                    WHEN lt.code::text = '00'::text OR lt.code IS NULL THEN 'n.r'::character varying
                    ELSE lt.code
                END AS secteur_urssaf,
            e.geom
           FROM m_activite_eco.geo_eco_etabp e
             LEFT JOIN s_sirene.naf_n5n1 n51 ON n51.niv1::text = e.ape::text
             LEFT JOIN s_sirene.naf4_ursaff nace ON n51.niv4::text = nace.naf4::text
             LEFT JOIN s_sirene.lt_urssaf lt ON lt.code::text = nace.codengs::text
        )
 SELECT row_number() OVER () AS gid,
    req_tout_etab.id_adresse,
    req_tout_etab.lib_adresse,
    req_tout_etab.nb_etab,
    req_tout_etab.nb_etab_t,
    req_tout_etab.nb_etab_f,
    req_tout_etab.etiquette,
    req_tout_etab.angle,
    req_tout_etab.insee,
    req_tout_etab.naf5,
    req_tout_etab.secteur_urssaf,
    req_tout_etab.geom
   FROM req_tout_etab
WITH DATA;

COMMENT ON MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_etab_api
    IS 'Vue matérialisé rafraichit à chaque intégration ou mise à jour de la table lk_adresseetablissement et dénombrant le nombre d''établissement par adresse (lien dans GEO pour avoir la liste des établissements et les modifier) (en cours de refonte suite migration API Sirene)';

CREATE INDEX idx_9801_id_adresse
    ON m_activite_eco.xapps_geo_vmr_etab_api USING btree
    (id_adresse)
    TABLESPACE pg_default;
CREATE INDEX xapps_geo_vmr_etab_api_gid_idx
    ON m_activite_eco.xapps_geo_vmr_etab_api USING btree
    (gid)
    TABLESPACE pg_default;

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_vmr_etab_api_export_site #####################################################################
-- m_activite_eco.xapps_geo_vmr_etab_api_export_site source
drop  MATERIALIZED VIEW if exists m_activite_eco.xapps_geo_vmr_etab_api_export_site;
CREATE MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_etab_api_export_site
TABLESPACE pg_default

AS WITH req_tot AS (
              WITH req_e AS (
                 SELECT DISTINCT e.idsiret,
                    e.l_nom,
                        CASE
                            WHEN e.eff_etab IS NOT NULL THEN e.eff_etab::character varying
                            ELSE 'n.r'::character varying
                        END AS eff_etab,
                    e.eff_etab_d AS eff_etab_n,
                    e.source_eff,
                    e.l_date_eff,
                    e.annee_eff,
                    e.l_compte,
                    se.site_nom AS site,
                    l.idadresse
                   FROM m_activite_eco.an_eco_etab e
                     LEFT JOIN m_activite_eco.lk_adresseetablissement l ON e.idsiret::text = l.siret::text
                     LEFT JOIN m_activite_eco.lk_eco_etab_site lk ON lk.siret::text = e.idsiret::text
                     LEFT JOIN m_activite_eco.geo_eco_site se ON se.idsite::text = lk.idsite::text
                ), req_c as (
                  with req_contact as (
   						 			select e.idsiret,tc.valeur, c.nom, c.tel, c.telp, c.email from 
			       					 m_activite_eco.an_eco_etab e, m_activite_eco.an_eco_contact c, m_activite_eco.lk_eco_contact lk, m_activite_eco.lt_eco_typcontact tc
									where 
   							 		e.idsiret = lk.idobjet and lk.idcontact = c.idcontact and tc.code = c.typcontact
			     					), req_contact_agg as (
								    select idsiret, array_agg(nom) as noms, array_agg(valeur) as typ_contact,  array_agg(tel) as tel, array_agg(telp) as telp, array_agg(email) as email
								    from req_contact
									group by idsiret
					) select idsiret, 
         				noms[1] as nom1, typ_contact[1] as typcontact1, tel[1] as tel1, telp[1] as portable1 , email[1] as email1, 
				        noms[2] as nom2,  typ_contact[2] as typcontact2, tel[2] as tel2, telp[2] as portable2 , email[2] as email2,
					    noms[3] as nom3,  typ_contact[3] as typcontact3, tel[3] as tel3, telp[3] as portable3 , email[3] as email3,
			            noms[4] as nom4,  typ_contact[4] as typcontact4, tel[4] as tel4, telp[4] as portable4 , email[4] as email4,
				        noms[5] as nom5,  typ_contact[5] as typcontact5, tel[5] as tel5, telp[5] as portable5 , email[5] as email5,
                	    noms[6] as nom6,  typ_contact[6] as typcontact6, tel[6] as tel6, telp[6] as portable6 , email[6] as email6
					from req_contact_agg
					order by idsiret
                ),
                req_si AS (
                 SELECT DISTINCT s.siret,
                    s.numerovoieetablissement::text ||
                        CASE
                            WHEN s.indicerepetitionetablissement IS NULL OR s.indicerepetitionetablissement::text <> ''::text THEN s.indicerepetitionetablissement
                            ELSE ''::character varying
                        END::text AS numvoie,
                    (
                        CASE
                            WHEN s.typevoieetablissement IS NULL OR s.typevoieetablissement::text <> ''::text THEN s.typevoieetablissement
                            ELSE ''::character varying
                        END::text || ' '::text) || s.libellevoieetablissement::text AS libvoie,
                    s.codecommuneetablissement,
                    s.complementadresseetablissement AS complementadresse,
                    s.distributionspecialeetablissement AS boite_postale,
                    s.codepostaletablissement AS code_postal,
                    s.denominationusuelleetablissement,
                    s.enseigne1etablissement,
                    s.datecreationetablissement,
                    s.etatadministratifetablissement,
                    s.datederniertraitementetablissement,
                    ul.denominationunitelegale,
                    ul.denominationusuelle1unitelegale,
                    ul.nomunitelegale,
                    (ul.nomusageunitelegale::text || ' '::text) || ul.prenom1unitelegale::text AS personnephysique,
                    s.nomenclatureactiviteprincipaleetablissement AS nomen_acti_princ,
                    s.activiteprincipaleetablissement AS code_acti_princ,
                        CASE
                            WHEN s.nomenclatureactiviteprincipaleetablissement::text = 'NAFRev2'::text THEN ( SELECT lt_nafrev2.valeur
                               FROM s_sirene.lt_nafrev2
                              WHERE lt_nafrev2.code::text = s.activiteprincipaleetablissement::text)
                            WHEN s.nomenclatureactiviteprincipaleetablissement::text = 'NAFRev1'::text THEN ( SELECT lt_nafrev1.valeur
                               FROM s_sirene.lt_nafrev1
                              WHERE lt_nafrev1.code::text = s.activiteprincipaleetablissement::text)
                            WHEN s.nomenclatureactiviteprincipaleetablissement::text = 'NAP'::text THEN ( SELECT lt_nap.valeur
                               FROM s_sirene.lt_nap
                              WHERE lt_nap.code::text = s.activiteprincipaleetablissement::text)
                            WHEN s.nomenclatureactiviteprincipaleetablissement::text = 'NAF1993'::text THEN ( SELECT lt_naf1993.valeur
                               FROM s_sirene.lt_naf1993
                              WHERE lt_naf1993.code::text = s.activiteprincipaleetablissement::text)
                            ELSE ''::character varying
                        END AS act_princ,
                    cj.valeur AS cate_juri,
                    n1.code AS naf1,
                    s.caractereemployeuretablissement,
                        CASE
                            WHEN r.siren IS NOT NULL THEN 'oui'::text
                            ELSE 'non'::text
                        END AS etab_radie,
                    a.idadresse
                   FROM s_sirene.an_etablissement_api s
                     LEFT JOIN s_sirene.an_unitelegale_api ul ON s.siren::text = ul.siren::text
                     LEFT JOIN s_sirene.lt_catejuri_n3 cj ON cj.code::text = ul.categoriejuridiqueunitelegale::text
                     LEFT JOIN s_sirene.naf_n5n1 n ON n.niv5::text = s.activiteprincipaleetablissement::text
                     LEFT JOIN s_sirene.naf_n1 n1 ON n1.code::text = n.niv1::text
                     LEFT JOIN m_activite_eco.an_eco_etab_rad r ON r.siren::text = "left"(s.siret::text, 9)
                     LEFT JOIN m_activite_eco.lk_adresseetablissement a ON a.siret::text = s.siret::text
                  WHERE s.etatadministratifetablissement::text = 'A'::text
                )
         SELECT DISTINCT e.idsiret,
            si.denominationusuelleetablissement,
            si.enseigne1etablissement,
            si.denominationunitelegale,
            si.denominationusuelle1unitelegale,
            si.nomunitelegale,
            si.personnephysique,
            e.l_nom,
            si.nomen_acti_princ,
            si.code_acti_princ,
            si.act_princ,
            si.cate_juri,
            si.naf1,
            si.caractereemployeuretablissement,
            e.eff_etab,
            e.eff_etab_n,
            e.source_eff,
            e.l_date_eff,
            e.annee_eff::character varying AS annee_eff,
            e.l_compte,
            si.etab_radie,
                CASE
                    WHEN to_char(si.datecreationetablissement, 'YYYY'::text)::integer IS NULL THEN 0
                    ELSE to_char(si.datecreationetablissement, 'YYYY'::text)::integer
                END AS datecreationetablissement,
            si.etatadministratifetablissement,
                CASE
                    WHEN to_char(si.datederniertraitementetablissement, 'YYYY'::text)::integer IS NULL THEN 0
                    ELSE to_char(si.datederniertraitementetablissement, 'YYYY'::text)::integer
                END AS datederniertraitementetablissement,
            (((
                CASE
                    WHEN si.numvoie IS NOT NULL THEN si.numvoie || ' '::text
                    ELSE ' '::text
                END ||
                CASE
                    WHEN si.libvoie IS NOT NULL THEN si.libvoie || ' '::text
                    ELSE ' '::text
                END) ||
                CASE
                    WHEN si.complementadresse IS NOT NULL THEN (si.complementadresse::text || ' '::text)::character varying
                    ELSE ' '::character varying
                END::text) ||
                CASE
                    WHEN si.boite_postale IS NOT NULL THEN (si.boite_postale::text || ' '::text)::character varying
                    ELSE ' '::character varying
                END::text) ||
                CASE
                    WHEN si.code_postal IS NOT NULL THEN si.code_postal
                    ELSE ''::character varying
                END::text AS adresse_sirene,
            g.libgeo AS commune,
            g.insee,
            g.lib_epci AS epci,
            e.site,
                CASE
                    WHEN e.idadresse IS NULL OR e.idadresse::text = ''::text THEN 'Etablissement non localisé à l''adresse'::character varying::text
                    ELSE (((((a.etiquette::text || ' '::text) || a.libvoie_c::text) || ' '::text) || a.codepostal::text) || ' '::text) || a.commune::text
                END AS adresse_loc,
                CASE
                    WHEN a.x_l93 IS NOT NULL THEN a.x_l93
                    ELSE ( SELECT round(st_x(c.geom1)::numeric, 2) AS round
                       FROM r_osm.geo_osm_commune c
                      WHERE c.insee::text = si.codecommuneetablissement::text)
                END AS x_l93,
                CASE
                    WHEN a.y_l93 IS NOT NULL THEN a.y_l93
                    ELSE ( SELECT round(st_y(c.geom1)::numeric, 2) AS round
                       FROM r_osm.geo_osm_commune c
                      WHERE c.insee::text = si.codecommuneetablissement::text)
                END AS y_l93,
            c.nom1, c.typcontact1, c.tel1, c.portable1 , c.email1, 
			c.nom2,  c.typcontact2, c.tel2, c.portable2 , c.email2,
			c.nom3,  c.typcontact3, c.tel3, c.portable3 , c.email3,
			c.nom4,  c.typcontact4, c.tel4, c.portable4 , c.email4,
			c.nom5,  c.typcontact5, c.tel5, c.portable5 , c.email5,
			c.nom6,  c.typcontact6, c.tel6, c.portable6 , c.email6,
			a.geom
           FROM req_si si
             JOIN req_e e ON e.idsiret::text = si.siret::text
             LEFT JOIN x_apps.xapps_geo_vmr_adresse a ON si.idadresse = a.id_adresse
             LEFT JOIN r_administratif.an_geo g ON g.insee::text = si.codecommuneetablissement::text
             LEFT JOIN req_c c ON c.idsiret::text = si.siret::text
          WHERE e.l_compte IS TRUE
        UNION ALL
         SELECT sp.idsiret,
            'non renseignée'::character varying AS denominationusuelleetablissement,
            'non renseignée'::character varying AS enseigne1etablissement,
            'non renseignée'::character varying AS denominationunitelegale,
            'non renseignée'::character varying AS denominationusuelle1unitelegale,
            'non renseignée'::character varying AS nomunitelegale,
            'non renseignée'::character varying AS personnephysique,
            sp.nom AS l_nom,
            'Non renseignée'::character varying AS nomen_acti_princ,
            'Non renseignée'::character varying AS code_acti_princ,
            'Non renseignée'::character varying AS act_princ,
            'Non renseignée'::character varying AS cate_juri,
                CASE
                    WHEN sp.ape IS NULL THEN '99'::character varying
                    ELSE sp.ape
                END AS naf1,
            'O'::character varying AS caractereemployeuretablissement,
            sp.eff_etab::character varying AS eff_etab,
            sp.eff_etab_d AS eff_etab_n,
            sp.source_eff,
            sp.date_eff AS l_date_eff,
            'non renseignée'::character varying AS annee_eff,
            sp.l_compte,
            'non'::text AS etab_radie,
            NULL::integer AS datecreationetablissement,
            'A'::character varying AS etatadministratifetablissement,
            NULL::integer AS datederniertraitementetablissement,
            'Non renseignée'::character varying AS adresse_sirene,
            g.libgeo AS commune,
            g.insee,
            g.lib_epci AS epci,
            se.site_nom AS site,
                CASE
                    WHEN sp.adresse IS NOT NULL THEN sp.adresse
                    ELSE 'Non renseignée'::character varying
                END AS adresse_loc,
            st_x(sp.geom) AS x_l93,
            st_y(sp.geom) AS y_l93,
            '' as nom1, '' as typcontact1, '' as tel1, '' as portable1, '' as email1, 
			'' as nom2, '' as typcontact2, '' as tel2, '' as portable2, '' as email2, 
			'' as nom3, '' as typcontact3, '' as tel3, '' as portable3, '' as email3, 
			'' as nom4, '' as typcontact4, '' as tel4, '' as portable4, '' as email4, 
			'' as nom5, '' as typcontact5, '' as tel5, '' as portable5, '' as email5, 
			'' as nom6, '' as typcontact6, '' as tel6, '' as portable6, '' as email6, 
            sp.geom
           FROM m_activite_eco.geo_eco_etabp sp
             LEFT JOIN m_activite_eco.lk_eco_etab_site lk ON lk.siret::text = ('EHS'::text || sp.idgeoet)
             LEFT JOIN m_activite_eco.geo_eco_site se ON se.idsite::text = lk.idsite::text
             LEFT JOIN r_administratif.an_geo g ON g.insee::text = sp.insee::text
        )

 SELECT
 row_number() OVER () AS gid,
    req_tot.idsiret,
    req_tot.denominationusuelleetablissement,
    req_tot.enseigne1etablissement,
    req_tot.denominationunitelegale,
    req_tot.denominationusuelle1unitelegale,
    req_tot.nomunitelegale,
    req_tot.personnephysique,
    req_tot.l_nom,
    req_tot.nomen_acti_princ,
    req_tot.code_acti_princ,
    req_tot.act_princ,
    req_tot.cate_juri,
    req_tot.naf1,
    req_tot.caractereemployeuretablissement,
    req_tot.eff_etab,
    req_tot.eff_etab_n,
    req_tot.source_eff,
    req_tot.l_date_eff,
    req_tot.annee_eff,
    req_tot.l_compte,
    req_tot.etab_radie,
    req_tot.datecreationetablissement,
    req_tot.etatadministratifetablissement,
    req_tot.datederniertraitementetablissement,
    req_tot.adresse_sirene,
    req_tot.commune,
    req_tot.insee,
    req_tot.epci,
    req_tot.site,
    req_tot.adresse_loc,
    req_tot.x_l93,
    req_tot.y_l93,
	req_tot.nom1, req_tot.typcontact1, req_tot.tel1, req_tot.portable1, req_tot.email1, 
    req_tot.nom2, req_tot.typcontact2, req_tot.tel2, req_tot.portable2, req_tot.email2,
    req_tot.nom3, req_tot.typcontact3, req_tot.tel3, req_tot.portable3, req_tot.email3,
    req_tot.nom4, req_tot.typcontact4, req_tot.tel4, req_tot.portable4, req_tot.email4,
    req_tot.nom5, req_tot.typcontact5, req_tot.tel5, req_tot.portable5, req_tot.email5,
    req_tot.nom6, req_tot.typcontact6, req_tot.tel6, req_tot.portable6, req_tot.email6,    
    req_tot.geom
   FROM req_tot
   
WITH DATA;

COMMENT ON MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_etab_api_export_site IS 'Vue géographique matérialisée (rafraichie ttes les nuits) composée des éléments sur les établissements actifs (API Sirene) permettant de gérer des exports de listes par site dans GEO';




-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_vmr_lot_plan_situation #####################################################################

-- View: m_activite_eco.xapps_geo_vmr_lot_plan_situation

-- DROP MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_lot_plan_situation;

CREATE MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_lot_plan_situation
TABLESPACE pg_default
AS
 WITH req_tout_lot AS (
         WITH req_lot AS (
                 SELECT DISTINCT o.idgeolf,
                    o.l_voca,
                        CASE
                            WHEN f.cnom IS NULL OR f.cnom::text = ''::text THEN f.lnom
                            ELSE f.cnom
                        END AS l_nom,
                    f.occupant,
                    s.l_comm2::text AS carto,
                    o.insee,
                    o.epci,
                    l.idsite
                   FROM m_activite_eco.an_eco_lot f
                     JOIN r_objet.geo_objet_fon_lot o ON f.idgeolf = o.idgeolf
                     JOIN m_amenagement.an_amt_lot_stade s ON o.idgeolf = s.idgeolf
                     LEFT JOIN m_amenagement.lk_amt_lot_site l ON l.idgeolf = o.idgeolf
                  WHERE s.l_comm2::text = '20'::text
                ), req_bati AS (
                 SELECT DISTINCT l_1.idgeolf,
                    l_1.geom
                   FROM r_objet.geo_objet_fon_lot l_1
                  WHERE NOT (EXISTS ( SELECT b_1.idgeolf
                           FROM m_activite_eco.geo_eco_loc_act b_1
                          WHERE l_1.idgeolf = b_1.idgeolf))
                )
         SELECT row_number() OVER () AS gid,
            string_agg(DISTINCT
                CASE
                    WHEN b.l_nom::text = b.occupant::text THEN b.l_nom::text
                    ELSE b.occupant::text
                END, chr(10)) AS l_nom,
            b.occupant,
                CASE
                    WHEN sum(l.idgeolf) IS NULL THEN 'non'::text
                    ELSE 'oui'::text
                END AS bati,
            b.carto,
                CASE
                    WHEN length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) >= 1 AND length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) <= 3 THEN round(st_area(st_union(l.geom))::numeric, 0) || 'm²'::text
                    WHEN length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) = 4 THEN replace(to_char(round(st_area(st_union(l.geom))::numeric, 0), 'FM9G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) = 5 THEN replace(to_char(round(st_area(st_union(l.geom))::numeric, 0), 'FM99G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) = 6 THEN replace(to_char(round(st_area(st_union(l.geom))::numeric, 0), 'FM999G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) = 7 THEN replace(to_char(round(st_area(st_union(l.geom))::numeric, 0), 'FM9G999G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(l.geom))::numeric, 0)::character varying::text) = 8 THEN replace(to_char(round(st_area(st_union(l.geom))::numeric, 0), 'FM99G999G999'::text), ','::text, ' '::text) || 'm²'::text
                    ELSE NULL::text
                END AS l_surf_l,
            b.insee,
            b.epci,
            b.idsite,
            st_union(l.geom) AS geom
           FROM req_lot b
             LEFT JOIN req_bati l ON l.idgeolf = b.idgeolf
          WHERE l.geom IS NOT NULL
          GROUP BY b.occupant, b.l_voca, b.carto, b.insee, b.epci, b.idsite
        UNION ALL
        ( WITH req_lot_loc AS (
                 SELECT l.idgeolf,
                    lo_1.lib_bati AS l_nom,
                    f.occupant,
                    'non'::text AS bati,
                    'ZZ' AS carto,
                    l.surf_l,
                    l.insee,
                    l.epci,
                    lk.idsite,
                    st_union(l.geom) AS geom
                   FROM m_activite_eco.geo_eco_loc_act lo_1
                     JOIN m_activite_eco.an_eco_lot f ON lo_1.idgeolf = f.idgeolf
                     JOIN r_objet.geo_objet_fon_lot l ON lo_1.insee::text = l.insee::text
                     JOIN m_amenagement.an_amt_lot_stade s ON l.idgeolf = s.idgeolf
                     LEFT JOIN m_amenagement.lk_amt_lot_site lk ON lk.idgeolf = l.idgeolf
                  WHERE s.l_comm2::text = '20'::text AND st_contains(l.geom, lo_1.geom) IS TRUE
                  GROUP BY l.idgeolf, l.insee, f.cnom, lo_1.lib_bati, f.occupant, lk.idsite
                ), req_lot_bati AS (
                 SELECT DISTINCT l.idgeolf,
                    l.geom
                   FROM r_objet.geo_objet_fon_lot l,
                    r_cadastre.geo_batiment b
                  WHERE l.insee::text = b.insee::text AND st_intersects(l.geom, b.geom) IS TRUE
                )
         SELECT row_number() OVER () AS gid,
            string_agg(lo.l_nom::text, chr(10)) AS l_nom,
            lo.occupant,
            lo.bati,
            lo.carto::text AS carto,
            lo.surf_l,
            lo.insee,
            lo.epci,
            lo.idsite,
            lo.geom
           FROM req_lot_loc lo
          WHERE NOT (EXISTS ( SELECT b.idgeolf
                   FROM req_lot_bati b
                  WHERE b.idgeolf = lo.idgeolf))
          GROUP BY lo.occupant, lo.bati, (lo.carto::text), lo.surf_l, lo.insee, lo.epci, lo.idsite, lo.geom)
        UNION ALL
         SELECT row_number() OVER () AS gid,
                CASE
                    WHEN f.cnom IS NULL OR f.cnom::text = ''::text THEN
                    CASE
                        WHEN f.lnom::text = f.occupant::text THEN f.lnom::text
                        ELSE f.occupant::text
                    END
                    ELSE
                    CASE
                        WHEN f.cnom::text = f.occupant::text THEN f.cnom::text
                        ELSE f.occupant::text
                    END
                END AS l_nom,
            f.occupant,
            'non'::text AS bati,
            s.l_comm2 AS carto,
            o.surf_l,
            o.insee,
            o.epci,
            lk.idsite,
            o.geom
           FROM m_activite_eco.an_eco_lot f
             JOIN r_objet.geo_objet_fon_lot o ON f.idgeolf = o.idgeolf
             JOIN m_amenagement.an_amt_lot_stade s ON o.idgeolf = s.idgeolf
             LEFT JOIN m_amenagement.lk_amt_lot_site lk ON lk.idgeolf = o.idgeolf
          WHERE s.l_comm2::text = '11'::text OR s.l_comm2::text = '12'::text OR s.l_comm2::text = '99'::text OR s.l_comm2::text = '00'::text
        UNION ALL
         SELECT row_number() OVER () AS gid,
            string_agg(DISTINCT
                CASE
                    WHEN f.cnom IS NULL OR f.cnom::text = ''::text THEN
                    CASE
                        WHEN f.lnom::text = f.occupant::text THEN f.lnom::text
                        ELSE f.occupant::text
                    END
                    ELSE
                    CASE
                        WHEN f.cnom::text = f.occupant::text THEN f.cnom::text
                        ELSE f.occupant::text
                    END
                END, chr(10)) AS l_nom,
            f.occupant,
            'non'::text AS bati,
            '3132'::text AS carto,
                CASE
                    WHEN length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) >= 1 AND length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) <= 3 THEN round(st_area(st_union(o.geom))::numeric, 0) || 'm²'::text
                    WHEN length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) = 4 THEN replace(to_char(round(st_area(st_union(o.geom))::numeric, 0), 'FM9G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) = 5 THEN replace(to_char(round(st_area(st_union(o.geom))::numeric, 0), 'FM99G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) = 6 THEN replace(to_char(round(st_area(st_union(o.geom))::numeric, 0), 'FM999G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) = 7 THEN replace(to_char(round(st_area(st_union(o.geom))::numeric, 0), 'FM9G999G999'::text), ','::text, ' '::text) || 'm²'::text
                    WHEN length(round(st_area(st_union(o.geom))::numeric, 0)::character varying::text) = 8 THEN replace(to_char(round(st_area(st_union(o.geom))::numeric, 0), 'FM99G999G999'::text), ','::text, ' '::text) || 'm²'::text
                    ELSE NULL::text
                END AS l_surf_l,
            o.insee,
            o.epci,
            lk.idsite,
            st_union(o.geom) AS geom
           FROM m_activite_eco.an_eco_lot f
             JOIN r_objet.geo_objet_fon_lot o ON f.idgeolf = o.idgeolf
             JOIN r_objet.geo_objet_fon_lot o1 ON f.idgeolf = o1.idgeolf
             JOIN m_amenagement.an_amt_lot_stade s ON o1.idgeolf = s.idgeolf
             LEFT JOIN m_amenagement.lk_amt_lot_site lk ON lk.idgeolf = o.idgeolf
          WHERE (s.l_comm2::text = '32'::text OR s.l_comm2::text = '31'::text) AND st_touches(o.geom, o1.geom) IS TRUE
          GROUP BY o.l_voca, f.occupant, o.insee, o.epci, lk.idsite
        UNION ALL
         SELECT row_number() OVER () AS gid,
                CASE
                    WHEN f.cnom IS NULL OR f.cnom::text = ''::text THEN
                    CASE
                        WHEN f.lnom::text = f.occupant::text THEN f.lnom::text
                        ELSE f.occupant::text
                    END
                    ELSE
                    CASE
                        WHEN f.cnom::text = f.occupant::text THEN f.cnom::text
                        ELSE f.occupant::text
                    END
                END AS l_nom,
            f.occupant,
            'non'::text AS bati,
            '3132'::text AS carto,
            o.surf_l,
            o.insee,
            o.epci,
            lk.idsite,
            o.geom
           FROM m_activite_eco.an_eco_lot f
             JOIN r_objet.geo_objet_fon_lot o ON f.idgeolf = o.idgeolf
             JOIN r_objet.geo_objet_fon_lot o1 ON f.idgeolf = o1.idgeolf
             JOIN m_amenagement.an_amt_lot_stade s ON o.idgeolf = s.idgeolf
             LEFT JOIN m_amenagement.lk_amt_lot_site lk ON lk.idgeolf = o.idgeolf
          WHERE (s.l_comm2::text = '32'::text OR s.l_comm2::text = '31'::text) AND st_touches(o.geom, o1.geom) IS FALSE
        )
 SELECT row_number() OVER () AS gid,
    req_tout_lot.l_nom,
    req_tout_lot.occupant,
    req_tout_lot.bati,
    req_tout_lot.carto,
    req_tout_lot.l_surf_l,
    req_tout_lot.insee,
    req_tout_lot.epci,
    req_tout_lot.idsite,
    req_tout_lot.geom
   FROM req_tout_lot
WITH DATA;

COMMENT ON MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_lot_plan_situation
    IS 'Vue géographique des lots fonciers avec les informations de stade d''aménagement et si informations immobilère présentes (pour la cartographie des plans de situation dans GEO de l''application)';

-- ########################################################### SCHEMA m_activite_eco ################################################################

-- ##################################################### xapps_geo_vmr_plan_situ_bati_act #####################################################################

-- View: m_activite_eco.xapps_geo_vmr_plan_situ_bati_act

-- DROP MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_plan_situ_bati_act;

CREATE MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_plan_situ_bati_act
TABLESPACE pg_default
AS
 SELECT row_number() OVER () AS gid,
    string_agg(
        CASE
            WHEN (l.lib_bati IS NOT NULL OR l.lib_bati::text <> ''::text) AND l.p_etiq IS TRUE THEN l.lib_bati::text
            ELSE ''::text
        END, chr(10)) AS lib_bati,
    l.insee,
    l.epci,
    lk.idsite,
    b.geom
   FROM m_activite_eco.geo_eco_loc_act l
     LEFT JOIN m_activite_eco.lk_eco_loc_site lk ON lk.idloc::text = l.idloc::text,
    r_cadastre.geo_batiment b
  WHERE l.insee::text = b.insee::text AND st_intersects(l.geom, b.geom) IS TRUE
  GROUP BY l.insee, l.epci, lk.idsite, b.geom
WITH DATA;

COMMENT ON MATERIALIZED VIEW m_activite_eco.xapps_geo_vmr_plan_situ_bati_act
    IS 'Vue matérialisée présentant les bâtiments d''activités avec le libellé du bâitment à afficher';


-- ########################################################### SCHEMA m_urbanisme_reg ################################################################

-- ##################################################### xapps_geo_vmr_proc_zac #####################################################################

-- View: m_urbanisme_reg.xapps_geo_vmr_proc_zac

-- DROP MATERIALIZED VIEW m_urbanisme_reg.xapps_geo_vmr_proc_zac;

CREATE MATERIALIZED VIEW m_urbanisme_reg.xapps_geo_vmr_proc_zac
TABLESPACE pg_default
AS
 SELECT row_number() OVER () AS gid,
    so.nom,
    so.geom,
    so.geom1
   FROM m_urbanisme_reg.geo_proced so
  WHERE so.z_proced::text = '10'::text
WITH DATA;


COMMENT ON MATERIALIZED VIEW m_urbanisme_reg.xapps_geo_vmr_proc_zac
    IS 'Vue matérialisée des procédures d''aménagements spécifique ZAC (périmètre global sans détail de zones d''aménagement éventuelles)';