76 lines
2.1 KiB
Python
76 lines
2.1 KiB
Python
from pycen import con
|
|
from sqlalchemy import text
|
|
|
|
v_sites = '''
|
|
DROP VIEW IF EXISTS sites.v_sites;
|
|
CREATE OR REPLACE VIEW sites.v_sites
|
|
AS
|
|
WITH tmp_auteur_site as (
|
|
SELECT
|
|
s.id,
|
|
string_agg(s11.auteur,';' ORDER BY s11.auteur) AS auteur_site
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_auteur s1
|
|
JOIN personnes.v_personne s11 ON s1.id_auteur = s11.id
|
|
) ON s.id::text = s1.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
GROUP BY s.id
|
|
ORDER BY s.id
|
|
), tmp_auteur_geom as (
|
|
SELECT
|
|
s6.id,
|
|
string_agg(s611.auteur,';' ORDER BY s611.auteur) AS auteur_geom
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN (sites.r_geomsites_auteur s61
|
|
JOIN personnes.v_personne s611 ON s61.id_auteur = s611.id
|
|
) ON s6.id = s61.id_geom_site
|
|
) ON s.id = s6.id_site
|
|
WHERE s.date_fin IS NULL
|
|
GROUP BY s6.id
|
|
ORDER BY s6.id
|
|
)
|
|
SELECT
|
|
g.id id_geom_site,
|
|
s.id code_site,
|
|
s.nom,
|
|
s1.auteur_site,
|
|
g1.auteur_geom,
|
|
s.date_deb date,
|
|
s.date_fin,
|
|
s.id_old_site,
|
|
s3.nom_court AS type_milieu,
|
|
s4.nom AS type_site,
|
|
s5.id||' - '||s5.nom AS typo_sdage,
|
|
s.dept,
|
|
s.org,
|
|
s.num,
|
|
s.remarques,
|
|
s.nom_com,
|
|
s.nom_com_json,
|
|
g.geom,
|
|
g.date date_geom,
|
|
g.link_pdf,
|
|
g.rmq_fct_majeur,
|
|
g.rmq_interet_patri,
|
|
g.rmq_bilan_menace,
|
|
g.rmq_orient_act,
|
|
g.rmq_usage_process,
|
|
g2.libelle lot,
|
|
g.id_origine,
|
|
g.date_insert
|
|
FROM sites.sites s
|
|
LEFT JOIN tmp_auteur_site s1 ON s.id = s1.id
|
|
LEFT JOIN sites.autre_nom s2 ON s.id = s2.id_site
|
|
LEFT JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN sites.type_site s4 ON s.id_type_site::text = s4.id::text
|
|
LEFT JOIN sites.typo_sdage s5 ON s.id_typo_sdage::text = s5.id::text
|
|
LEFT JOIN sites.r_sites_geom g ON s.id = g.id_site
|
|
LEFT JOIN tmp_auteur_geom g1 ON g.id = g1.id
|
|
LEFT JOIN sites.lots g2 USING (id_lot)
|
|
;
|
|
'''
|
|
with con.begin() as cnx:
|
|
cnx.execute(text(v_sites)) |