251 lines
7.9 KiB
Python
251 lines
7.9 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
|
|
import geopandas as gpd
|
|
from pycen import con_sicen
|
|
|
|
drop_v_inv = "DROP VIEW IF EXISTS saisie.v_synthese_invertebre"
|
|
v_synthese_invertebre = """
|
|
CREATE OR REPLACE VIEW saisie.v_synthese_invertebre
|
|
AS WITH update_data AS (
|
|
SELECT
|
|
vm.id_obs,
|
|
MAX(s.date_operation) date_update
|
|
FROM saisie.vm_synthese_observations vm
|
|
LEFT JOIN saisie.suivi_saisie_observation s USING (id_obs)
|
|
GROUP BY vm.id_obs
|
|
)
|
|
SELECT
|
|
vm.id_obs,
|
|
vm.etude,
|
|
vm.protocole,
|
|
vm.lot_donnee,
|
|
s.date_operation,
|
|
ud.date_update,
|
|
vm.date_obs::date,
|
|
vm.date_debut_obs::date,
|
|
vm.date_fin_obs::date,
|
|
vm.heure_obs::time,
|
|
vm.regne,
|
|
vm.classe,
|
|
vm.ordre,
|
|
vm.famille,
|
|
vm.group1_inpn,
|
|
vm.group2_inpn,
|
|
vm.nom_latin,
|
|
vm.nom_vern,
|
|
vm.nom_complet,
|
|
vm.cd_nom,
|
|
vm.cd_ref,
|
|
vm.absence_observation,
|
|
vm.effectif,
|
|
vm.effectif_min,
|
|
vm.effectif_max,
|
|
vm.effectif_textuel,
|
|
vm.strate_flore,
|
|
vm.phenologie_flore,
|
|
vm.age_faune,
|
|
vm.sexe_faune,
|
|
vm.reprostatut_faune,
|
|
vm.determination,
|
|
vm.rmq_observation,
|
|
vm.observateurs,
|
|
vm.observateurs_v2,
|
|
vm.numerisateur,
|
|
vm.structures,
|
|
vm.diffusable,
|
|
vm.statut_validation,
|
|
vm.validateur,
|
|
vm.decision_validation,
|
|
vm.insee_commune,
|
|
vm.nom_commune,
|
|
vm.lieu_dit,
|
|
vm.altitude_z,
|
|
vm.longitude_x,
|
|
vm.latitude_y,
|
|
vm."precision",
|
|
vm.rmq_localisation,
|
|
vm.geom,
|
|
so.id_origine
|
|
FROM saisie.vm_synthese_observations vm
|
|
LEFT JOIN ref_inpn_taxref.taxref_v11 tax ON vm.cd_nom::integer = tax.cd_nom
|
|
JOIN saisie.saisie_observation so USING (id_obs)
|
|
LEFT JOIN update_data ud USING (id_obs)
|
|
LEFT JOIN (SELECT * FROM saisie.suivi_saisie_observation WHERE operation = 'INSERT'::text) s USING (id_obs)
|
|
WHERE tax.regne = 'Animalia'::text AND tax.phylum <> 'Chordata'::text OR (vm.cd_nom::text = ANY (ARRAY['9999024'::text, '9999025'::text, '9999032'::text, '9999055'::text, '9999061'::text, '9999062'::text, '9999069'::text, '9999036'::text, '9999040'::text, '9999072'::text, '9999045'::text, '9999047'::text, '9999051'::text, '9999052'::text, '9999053'::text, '9999081'::text, '9999026'::text, '9999050'::text, '9999048'::text, '9999037'::text, '9999066'::text, '9999065'::text, '9999080'::text]));
|
|
"""
|
|
grant_v_inv = """
|
|
ALTER TABLE saisie.v_synthese_invertebre OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE saisie.v_synthese_invertebre TO cen_admin;
|
|
GRANT SELECT ON TABLE saisie.v_synthese_invertebre TO grp_consult;
|
|
GRANT ALL ON TABLE saisie.v_synthese_invertebre TO grp_admin;
|
|
"""
|
|
with con_sicen.begin() as cnx:
|
|
cnx.execute(drop_v_inv)
|
|
cnx.execute(v_synthese_invertebre)
|
|
cnx.execute(grant_v_inv)
|
|
|
|
|
|
drop_v_vert = "DROP VIEW IF EXISTS saisie.v_synthese_vertebre"
|
|
v_synthese_vertebre = """
|
|
CREATE OR REPLACE VIEW saisie.v_synthese_vertebre
|
|
AS WITH update_data AS (
|
|
SELECT
|
|
vm.id_obs,
|
|
MAX(s.date_operation) date_update
|
|
FROM saisie.vm_synthese_observations vm
|
|
LEFT JOIN saisie.suivi_saisie_observation s USING (id_obs)
|
|
GROUP BY vm.id_obs
|
|
)
|
|
SELECT
|
|
vm.id_obs,
|
|
vm.etude,
|
|
vm.protocole,
|
|
vm.lot_donnee,
|
|
s.date_operation,
|
|
ud.date_update,
|
|
vm.date_obs::date,
|
|
vm.date_debut_obs::date,
|
|
vm.date_fin_obs::date,
|
|
vm.heure_obs::time,
|
|
vm.regne,
|
|
vm.classe,
|
|
vm.ordre,
|
|
vm.famille,
|
|
vm.group1_inpn,
|
|
vm.group2_inpn,
|
|
vm.nom_latin,
|
|
vm.nom_vern,
|
|
vm.nom_complet,
|
|
vm.cd_nom,
|
|
vm.cd_ref,
|
|
vm.absence_observation,
|
|
vm.effectif,
|
|
vm.effectif_min,
|
|
vm.effectif_max,
|
|
vm.effectif_textuel,
|
|
vm.strate_flore,
|
|
vm.phenologie_flore,
|
|
vm.age_faune,
|
|
vm.sexe_faune,
|
|
vm.reprostatut_faune,
|
|
vm.determination,
|
|
vm.rmq_observation,
|
|
vm.observateurs,
|
|
vm.observateurs_v2,
|
|
vm.numerisateur,
|
|
vm.structures,
|
|
vm.diffusable,
|
|
vm.statut_validation,
|
|
vm.validateur,
|
|
vm.decision_validation,
|
|
vm.insee_commune,
|
|
vm.nom_commune,
|
|
vm.lieu_dit,
|
|
vm.altitude_z,
|
|
vm.longitude_x,
|
|
vm.latitude_y,
|
|
vm."precision",
|
|
vm.rmq_localisation,
|
|
vm.geom,
|
|
so.id_origine
|
|
FROM saisie.vm_synthese_observations vm
|
|
LEFT JOIN ref_inpn_taxref.taxref_v11 tax ON vm.cd_nom::integer = tax.cd_nom
|
|
JOIN saisie.saisie_observation so USING (id_obs)
|
|
LEFT JOIN update_data ud USING (id_obs)
|
|
LEFT JOIN (SELECT * FROM saisie.suivi_saisie_observation WHERE operation = 'INSERT'::text) s USING (id_obs)
|
|
WHERE tax.regne = 'Animalia'::text AND tax.phylum = 'Chordata'::text OR (vm.cd_nom::text = ANY (ARRAY['9999042'::text, '9999028'::text, '9999029'::text, '9999056'::text, '9999058'::text, '9999067'::text, '9999068'::text, '9999030'::text, '9999031'::text, '9999034'::text, '9999034'::text, '9999035'::text, '9999035'::text, '9999038'::text, '9999039'::text, '9999070'::text, '9999073'::text, '9999057'::text, '9999054'::text, '9999049'::text, '9999022'::text, '9999027'::text, '9999043'::text, '9999044'::text, '9999046'::text, '9999041'::text, '9999033'::text, '9999071'::text, '9999064'::text, '9999063'::text, '9999060'::text, '9999059'::text, '9999074'::text, '9999023'::text, '9999082'::text, '9999083'::text]));
|
|
"""
|
|
grant_v_vert = """
|
|
ALTER TABLE saisie.v_synthese_vertebre OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE saisie.v_synthese_vertebre TO cen_admin;
|
|
GRANT SELECT ON TABLE saisie.v_synthese_vertebre TO grp_consult;
|
|
GRANT ALL ON TABLE saisie.v_synthese_vertebre TO grp_admin;
|
|
"""
|
|
with con_sicen.begin() as cnx:
|
|
cnx.execute(drop_v_vert)
|
|
cnx.execute(v_synthese_vertebre)
|
|
cnx.execute(grant_v_vert)
|
|
|
|
|
|
drop_v_flo = "DROP VIEW IF EXISTS saisie.v_synthese_flore"
|
|
v_synthese_flore = """
|
|
CREATE OR REPLACE VIEW saisie.v_synthese_flore
|
|
AS WITH update_data AS (
|
|
SELECT
|
|
vm.id_obs,
|
|
MAX(s.date_operation) date_update
|
|
FROM saisie.vm_synthese_observations vm
|
|
LEFT JOIN saisie.suivi_saisie_observation s USING (id_obs)
|
|
GROUP BY vm.id_obs
|
|
)
|
|
SELECT
|
|
vm.id_obs,
|
|
vm.etude,
|
|
vm.protocole,
|
|
vm.lot_donnee,
|
|
s.date_operation,
|
|
ud.date_update,
|
|
vm.date_obs::date,
|
|
vm.date_debut_obs::date,
|
|
vm.date_fin_obs::date,
|
|
vm.heure_obs::time,
|
|
vm.regne,
|
|
vm.classe,
|
|
vm.ordre,
|
|
vm.famille,
|
|
vm.group1_inpn,
|
|
vm.group2_inpn,
|
|
vm.nom_latin,
|
|
vm.nom_vern,
|
|
vm.nom_complet,
|
|
vm.cd_nom,
|
|
vm.cd_ref,
|
|
vm.absence_observation,
|
|
vm.effectif,
|
|
vm.effectif_min,
|
|
vm.effectif_max,
|
|
vm.effectif_textuel,
|
|
vm.strate_flore,
|
|
vm.phenologie_flore,
|
|
vm.age_faune,
|
|
vm.sexe_faune,
|
|
vm.reprostatut_faune,
|
|
vm.determination,
|
|
vm.rmq_observation,
|
|
vm.observateurs,
|
|
vm.observateurs_v2,
|
|
vm.numerisateur,
|
|
vm.structures,
|
|
vm.diffusable,
|
|
vm.statut_validation,
|
|
vm.validateur,
|
|
vm.decision_validation,
|
|
vm.insee_commune,
|
|
vm.nom_commune,
|
|
vm.lieu_dit,
|
|
vm.altitude_z,
|
|
vm.longitude_x,
|
|
vm.latitude_y,
|
|
vm."precision",
|
|
vm.rmq_localisation,
|
|
vm.geom,
|
|
so.id_origine
|
|
FROM saisie.vm_synthese_observations vm
|
|
LEFT JOIN ref_inpn_taxref.taxref_v11 tax ON vm.cd_nom::integer = tax.cd_nom
|
|
JOIN saisie.saisie_observation so USING (id_obs)
|
|
LEFT JOIN update_data ud USING (id_obs)
|
|
LEFT JOIN (SELECT * FROM saisie.suivi_saisie_observation WHERE operation = 'INSERT'::text) s USING (id_obs)
|
|
WHERE tax.regne <> 'Animalia'::text OR (vm.cd_nom::text = ANY (ARRAY['9999008'::text, '9999003'::text, '9999005'::text, '9999006'::text, '9999004'::text, '9999009'::text, '9999010'::text, '9999011'::text, '9999013'::text, '9999014'::text, '9999015'::text, '9999016'::text, '9999017'::text, '9999018'::text, '9999019'::text, '9999020'::text, '9999021'::text, '9999007'::text, '9999012'::text, '9999001'::text, '9999002'::text]));
|
|
"""
|
|
grant_v_flo = """
|
|
ALTER TABLE saisie.v_synthese_flore OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE saisie.v_synthese_flore TO cen_admin;
|
|
GRANT SELECT ON TABLE saisie.v_synthese_flore TO grp_consult;
|
|
GRANT ALL ON TABLE saisie.v_synthese_flore TO grp_admin;
|
|
"""
|
|
with con_sicen.begin() as cnx:
|
|
cnx.execute(drop_v_flo)
|
|
cnx.execute(v_synthese_flore)
|
|
cnx.execute(grant_v_flo)
|