470 lines
15 KiB
Python
470 lines
15 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
from sqlalchemy import create_engine, text
|
|
from sqlalchemy.engine import URL
|
|
from datetime import datetime as dt
|
|
import pandas as pd
|
|
import geopandas as gpd
|
|
|
|
nom_numerateur = 'GEIER'
|
|
pnom_numerator = 'Colas'
|
|
today = dt.now().date().isoformat()
|
|
# Parametres bdd
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '91.134.194.221'
|
|
port = '5432'
|
|
base = 'sicen2'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=base,
|
|
)
|
|
con = create_engine(url)
|
|
|
|
nom_numerateur = nom_numerateur.upper()
|
|
pnom_numerator = pnom_numerator[0].upper() + pnom_numerator[1:]
|
|
query_get_numerator = """
|
|
SELECT * FROM md.personne
|
|
WHERE nom = '%s'
|
|
AND prenom = '%s'
|
|
AND id_structure = 1
|
|
""" % (nom_numerateur,pnom_numerator)
|
|
numerateur = pd.read_sql_query(
|
|
sql = query_get_numerator,
|
|
con = con
|
|
)
|
|
|
|
|
|
select_import = """
|
|
SELECT * FROM import.import_data_fauneisere_biolovision;
|
|
"""
|
|
data_import = pd.read_sql_query(
|
|
sql = select_import,
|
|
con = con)
|
|
|
|
|
|
verif_taxref = '''
|
|
SELECT CASE WHEN c.taxref_cdnom IS NULL THEN bb.cd_nom
|
|
ELSE c.taxref_cdnom END cdnom_taxref,
|
|
CASE WHEN c.cd_nom IS NULL THEN cc.cd_nom
|
|
ELSE c.cd_nom END cd_nom,
|
|
CASE WHEN c.cd_ref IS NULL THEN cc.cd_ref
|
|
ELSE c.cd_ref END cd_ref,
|
|
a.ref, a."id espèce biolovision", a."nom espèce", a."nom latin",
|
|
a.date, a.jour, a.mois, a.année, a."jour de l'année", a.pentade, a."décade",
|
|
a."numéro de la semaine", a."heure début", a."heure de début",
|
|
a."minute de début", a."heure fin", a."heure de fin", a."minute de fin", a.horaire,
|
|
--a.id_place, a.commune, a.municipality, a.county, a.country, "code insee", a.altitude,
|
|
a."lat (wgs84)", a."lon (wgs84)", a."type de localisation",
|
|
a.estimation, a.nombre, a."détails", a."code atlas", a.comportement,
|
|
a."prénom", a.nom, a."abréviation personne morale",
|
|
--a.search_export_species_sheet_protection_nat, a.search_export_entity_full_name, a.search_export_entity_tra_full_name,
|
|
a.search_export_species_sheet_dir_oiseaux, a.search_export_species_sheet_dir_habitat,
|
|
a.search_export_species_sheet_uicn, a.search_export_species_sheet_lr_nat, a.search_export_species_sheet_znieff,
|
|
a.search_export_species_sheet_id_fnat,
|
|
--a."contient des détails mortalité", a.protocol,
|
|
a.remarque, a."remarque privée",a."date d'insertion", a."date de dernière modification"
|
|
FROM import.import_data_fauneisere_biolovision a
|
|
LEFT JOIN (
|
|
SELECT DISTINCT b.taxref_cdnom, b.id_visio, t.*
|
|
FROM ref_lpo.taxons_fauneisere_biolovision_taxref b
|
|
JOIN inpn.taxref t ON t.cd_nom::integer = b.taxref_cdnom) c
|
|
ON a."id espèce biolovision" = c.id_visio
|
|
LEFT JOIN (inpn.taxons_isere_absents_taxref bb
|
|
JOIN inpn.taxref cc ON cc.cd_nom::integer = bb.cd_nom)
|
|
ON a."id espèce biolovision" = bb.id_taxon
|
|
;
|
|
'''
|
|
df_verif = pd.read_sql_query(
|
|
sql = verif_taxref,
|
|
con = con)
|
|
|
|
|
|
if df_verif.shape[0] != data_import.shape[0]:
|
|
print('shape data_import : %s') % data_import.shape[0]
|
|
print('shape taxref_verif : %s') % df_verif.shape[0]
|
|
raise Exception('ERROR : correspondance with TAXREF invalid !')
|
|
|
|
# df_verif[df_verif.cd_nom.isna()][["id espèce biolovision","nom espèce","nom latin"]].drop_duplicates()
|
|
# data_import[~data_import.id_species.isin(df_verif.id_species)] \
|
|
# [['name_species','latin_species','id_species']].drop_duplicates()
|
|
|
|
|
|
|
|
update_structure_name = """
|
|
UPDATE import.import_data_fauneisere_biolovision a
|
|
SET "abréviation personne morale" = REPLACE("abréviation personne morale", 'CEN38', 'CEN Isère');
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(update_structure_name)
|
|
|
|
|
|
|
|
create_table_pers = """
|
|
DROP TABLE IF EXISTS md.temp_import_personne;
|
|
CREATE TABLE md.temp_import_personne AS
|
|
|
|
WITH nom_complet_identifies AS
|
|
(SELECT DISTINCT
|
|
ltrim(unnest(string_to_array(a."prénom",'&'))) as prenom,
|
|
ltrim(unnest(CASE WHEN "abréviation personne morale" IN ('CEN Isère','-') THEN string_to_array(''|| upper(split_part(a.nom,' /',1)),'&')
|
|
ELSE string_to_array(upper(split_part(a.nom,' (',1)),'&') END)) as nom,
|
|
CASE WHEN "abréviation personne morale" ILIKE '-' THEN 'CEN Isère'
|
|
ELSE "abréviation personne morale" END as structure,
|
|
a.nom || ' ' || a."prénom" as full_name
|
|
FROM import.import_data_fauneisere_biolovision a
|
|
ORDER BY full_name),
|
|
|
|
personne_sicen AS
|
|
(SELECT a.id_personne, prenom, nom, nom || ' ' || prenom as nom_complet, role, specialite,
|
|
a.id_structure, b.nom_structure
|
|
FROM md.personne a
|
|
JOIN md.structure b ON a.id_structure = b.id_structure
|
|
--WHERE personne.id_personne BETWEEN 100000 AND 999998
|
|
)
|
|
|
|
SELECT DISTINCT
|
|
CASE WHEN b.id_personne IS NULL THEN nextval('md.personne_id_personne_hors_cen38_seq'::regclass)
|
|
ELSE b.id_personne END as id_personne,
|
|
b.id_personne id_personne_exist,
|
|
'Observateur Faune-Isère' as remarque,
|
|
a.prenom as prenom,
|
|
a.nom as nom,
|
|
a.full_name as nom_complet,
|
|
b.nom_structure,
|
|
a.structure,
|
|
CASE WHEN a.structure ILIKE 'CEN Isère' THEN 1
|
|
ELSE 1 END AS id_structure
|
|
FROM nom_complet_identifies a
|
|
LEFT JOIN personne_sicen b ON lower(a.nom || ' ' || a.prenom) = lower(b.nom_complet) AND b.nom_structure = a.structure
|
|
;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(create_table_pers)
|
|
|
|
select_pers = 'SELECT * FROM md.temp_import_personne'
|
|
df_pers = pd.read_sql_query(
|
|
sql = select_pers,
|
|
con = con)
|
|
|
|
|
|
select_persTOinsert = """
|
|
SELECT DISTINCT
|
|
a.id_personne,
|
|
a.remarque,
|
|
a.prenom as prenom,
|
|
a.nom as nom, --full_name
|
|
NULL as email,
|
|
'observ'::md.enum_role,
|
|
NULL::md.enum_specialite as specialite,
|
|
0 as mot_de_passe,
|
|
{id_creator} as createur,
|
|
'Mme / M.'::md.enum_titre as titre,
|
|
now()::date as date_maj,
|
|
a.id_structure
|
|
FROM md.temp_import_personne a
|
|
WHERE NOT EXISTS (SELECT DISTINCT id_personne FROM md.personne)
|
|
;
|
|
""".format(id_creator = numerateur.id_personne.item())
|
|
df_pers_insert = pd.read_sql_query(
|
|
sql = select_persTOinsert,
|
|
con = con)
|
|
|
|
if not df_pers_insert.empty:
|
|
raise Exception("DEV en cours ...")
|
|
insert_pers = """
|
|
INSERT INTO md.personne(
|
|
id_personne,
|
|
remarque,
|
|
prenom,
|
|
nom,
|
|
email,
|
|
role,
|
|
specialite,
|
|
mot_de_passe,
|
|
createur,
|
|
titre,
|
|
date_maj,
|
|
id_structure)
|
|
|
|
""" + select_persTOinsert
|
|
with con.begin() as cnx:
|
|
cnx.execute(insert_pers)
|
|
else:
|
|
pass
|
|
|
|
|
|
select_pers = """
|
|
WITH observateurs_multiples AS
|
|
(SELECT DISTINCT
|
|
string_agg(a.id_personne::text,'&') as id_personne,
|
|
--a.remarque,
|
|
--a.prenom as prenom,
|
|
--a.nom as nom,
|
|
a.nom_complet,
|
|
a.id_structure,
|
|
s.nom_structure
|
|
FROM md.temp_import_personne a
|
|
LEFT JOIN md.structure s ON a.id_structure = s.id_structure
|
|
GROUP BY a.nom_complet, a.id_structure,s.nom_structure)
|
|
"""
|
|
|
|
select_dataTOinsert = """
|
|
SELECT DISTINCT
|
|
--a."id espèce biolovision",
|
|
--a.name_species,
|
|
--a.latin_species,
|
|
--a.protegee,
|
|
--a.embr,
|
|
--a.classe1,
|
|
--a.classe2,
|
|
a.date::date,
|
|
CASE WHEN a.horaire <> '00:00:00' THEN a.horaire::time
|
|
ELSE NULL END as heure_obs,
|
|
--a.date_year,
|
|
--a.time_start,
|
|
--a.time_stop,
|
|
CASE WHEN t.regne IS NULL THEN tt.regne
|
|
ELSE t.regne END regne,
|
|
CASE WHEN t.phylum IS NULL THEN tt.phylum
|
|
ELSE t.phylum END phylum,
|
|
CASE WHEN t.classe IS NULL THEN tt.classe
|
|
ELSE t.classe END classe,
|
|
CASE WHEN t.ordre IS NULL THEN tt.ordre
|
|
ELSE t.ordre END ordre,
|
|
CASE WHEN t.famille IS NULL THEN tt.famille
|
|
ELSE t.famille END famille,
|
|
CASE WHEN t.nom_valide IS NULL THEN tt.nom_valide
|
|
ELSE t.nom_valide END nom_valide,
|
|
CASE WHEN t.nom_vern IS NULL THEN tt.nom_vern
|
|
ELSE t.nom_vern END nom_vern,
|
|
CASE WHEN t.nom_complet IS NULL THEN tt.nom_complet
|
|
ELSE t.nom_complet END nom_complet,
|
|
CASE WHEN t.cd_nom IS NULL THEN tt.cd_nom
|
|
ELSE t.cd_nom END cd_nom,
|
|
--t.cd_nom cd_nom_2,
|
|
--b.taxref_cdnom,
|
|
CASE WHEN a.estimation IS NULL AND (a.nombre IS NOT NULL OR a.nombre > 0) THEN a.nombre
|
|
WHEN a.estimation LIKE 'x' THEN '1'
|
|
ELSE NULL END AS effectif,
|
|
CASE WHEN a.estimation LIKE '>' AND (a.nombre IS NOT NULL OR a.nombre > 0) THEN a.nombre
|
|
WHEN a.estimation LIKE '~' AND (a.nombre IS NOT NULL OR a.nombre > 0) THEN a.nombre
|
|
ELSE NULL END AS effectif_min,
|
|
CASE WHEN a.estimation LIKE '~' AND (a.nombre IS NOT NULL OR a.nombre > 0) THEN (a.nombre + 10)
|
|
ELSE NULL END AS effectif_max,
|
|
a."lon (wgs84)" as longitude,
|
|
a."lat (wgs84)" as latitude,
|
|
a.commune as localisation,
|
|
o.id_personne as observateur,
|
|
/*a."prénom",
|
|
a.nom,
|
|
a.entity_short_name,
|
|
a.entity_full_name,
|
|
a.tra_surname,
|
|
a.tra_name,
|
|
a.tra_full_name,*/
|
|
{id_numerateur} as numerisateur,
|
|
o.id_structure,
|
|
concat_ws(' | ',
|
|
CASE WHEN a."détails" IS NOT NULL THEN 'Détails : '|| a."détails" ELSE NULL END,
|
|
CASE WHEN a."contient des détails mortalité" ILIKE 'oui' THEN 'Mortalité' ELSE NULL END,
|
|
CASE WHEN a.comportement IS NOT NULL THEN 'Comportement : '|| a.comportement ELSE NULL END,
|
|
CASE WHEN a."code atlas" IS NOT NULL THEN 'Code atlas : '|| c.categorie || CONCAT(' (',c.libelle,')') ELSE NULL END,
|
|
CASE WHEN a.remarque IS NOT NULL THEN 'Commentaires : '|| a.remarque ELSE NULL END
|
|
) as remarque_obs,
|
|
--a.id_place,
|
|
--a.municipality,
|
|
--a.county,
|
|
--a.country,
|
|
"code insee" as code_insee,
|
|
TRUE as diffusable,
|
|
--CASE WHEN a.hidden ILIKE 'oui' THEN TRUE ELSE FALSE END as diffusable,
|
|
CASE WHEN lower(a."type de localisation") ILIKE 'lieu-dit' THEN 'lieu-dit'
|
|
WHEN lower(a."type de localisation") ILIKE 'localisation précise' THEN '0 à 10m'
|
|
ELSE NULL END::saisie.enum_precision AS "precision",
|
|
--a.grid_name,
|
|
--a.estimation,
|
|
'validée'::saisie.enum_statut_validation as statut_validation,
|
|
99 as id_etude, -- Echange de données (partenaires)
|
|
3 as id_protocole, -- Observation aléatoire
|
|
a.altitude as elevation,
|
|
--a.hidden,
|
|
--a.admin_hidden,
|
|
--a.committee_chn,
|
|
--a.committee_chr,
|
|
--a.second_hand,
|
|
--a.private_comment,
|
|
--a.insert_date,
|
|
--a.update_date,
|
|
--a.project_code,
|
|
ST_Transform(st_setsrid(st_makepoint("lon (wgs84)", "lat (wgs84)"),4326),2154)::geometry(POINT,2154) AS geometrie,
|
|
CASE WHEN (a.nombre IS NULL OR a.nombre = 0) THEN TRUE ELSE FALSE END AS obs_null,
|
|
8 as id_lot, -- Code du lot de données : Import Faune-Isère (exporté par LPO)
|
|
a.ref as id_origine
|
|
|
|
FROM import.import_data_fauneisere_biolovision a
|
|
LEFT JOIN (
|
|
SELECT DISTINCT b.taxref_cdnom, b.id_visio, t.*
|
|
FROM ref_lpo.taxons_fauneisere_biolovision_taxref b
|
|
JOIN inpn.taxref t ON t.cd_nom::integer = b.taxref_cdnom) t
|
|
ON a."id espèce biolovision" = t.id_visio
|
|
LEFT JOIN (inpn.taxons_isere_absents_taxref bb
|
|
JOIN inpn.taxref tt ON tt.cd_nom::integer = bb.cd_nom)
|
|
ON a."id espèce biolovision" = bb.id_taxon
|
|
LEFT JOIN ref_lpo.tr_code_atlas_lpo c ON a."code atlas" = c.code_atlas
|
|
JOIN observateurs_multiples o ON lower(a.nom || ' ' || a."prénom") = lower(o.nom_complet)
|
|
--LEFT JOIN saisie.saisie_observation s
|
|
--ON ((a.ref::text = s.id_origine) AND (id_lot IN (7.8)))
|
|
--AND s.id_origine IS NULL
|
|
;
|
|
""".format(id_numerateur=numerateur.id_personne.item())
|
|
df_data_insert = gpd.read_postgis(
|
|
sql = text(select_pers+select_dataTOinsert),
|
|
con = con,
|
|
geom_col='geometrie')
|
|
|
|
# df_data_insert[df_data_insert.id_origine.duplicated(keep=False)]
|
|
# df_data_insert[df_data_insert.id_origine.duplicated(keep=False)].cd_nom.unique()
|
|
# df_data_insert[df_data_insert.cd_nom.isna()]
|
|
|
|
if df_data_insert.shape[0] == data_import.shape[0]:
|
|
insert_data = """
|
|
INSERT INTO saisie.saisie_observation(
|
|
--id_obs,
|
|
date_obs,
|
|
--date_debut_obs,
|
|
--date_fin_obs,
|
|
heure_obs,
|
|
--date_textuelle,
|
|
regne,
|
|
phylum,
|
|
classe,
|
|
ordre,
|
|
famille,
|
|
nom_valide,
|
|
nom_vern,
|
|
nom_complet,
|
|
cd_nom,
|
|
--effectif_textuel,
|
|
effectif,
|
|
effectif_min,
|
|
effectif_max,
|
|
--type_effectif,
|
|
--phenologie,
|
|
--id_waypoint,
|
|
longitude,
|
|
latitude,
|
|
localisation,
|
|
observateur,
|
|
numerisateur,
|
|
--validateur,
|
|
structure,
|
|
remarque_obs,
|
|
code_insee,
|
|
--id_lieu_dit,
|
|
diffusable,
|
|
"precision",
|
|
statut_validation,
|
|
id_etude,
|
|
id_protocole,
|
|
--url_photo,
|
|
--commentaire_photo,
|
|
--decision_validation,
|
|
--determination,
|
|
elevation,
|
|
geometrie,
|
|
--qualification,
|
|
--reprostatut,
|
|
obs_null,
|
|
--uuid,
|
|
id_lot,
|
|
id_origine)
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(
|
|
text(select_pers+
|
|
insert_data+
|
|
select_dataTOinsert)
|
|
)
|
|
|
|
|
|
update_structure_name = """
|
|
UPDATE saisie.suivi_saisie_observation a
|
|
SET utilisateur = REPLACE(utilisateur, 'inconnu', '{email_numerator}')
|
|
WHERE date_operation > '{today}'
|
|
;
|
|
""".format(
|
|
email_numerator=numerateur.email.item(),
|
|
today = today
|
|
)
|
|
drop_temp_table = """
|
|
DROP TABLE IF EXISTS md.temp_import_personne;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(update_structure_name)
|
|
cnx.execute(drop_temp_table)
|
|
|
|
|
|
|
|
{
|
|
'ref': 'ref',
|
|
"id espèce biolovision": 'id_species',
|
|
"lat (wgs84)": 'coord_lat',
|
|
"lon (wgs84)": 'coord_lon',
|
|
"nom espèce": 'name_species',
|
|
"nom latin": 'latin_species',
|
|
# 'abréviation de la personne morale du transmetteur',
|
|
"prénom":'surname',
|
|
'nom':'name',
|
|
'abréviation personne morale':'search_export_entity_short_name',
|
|
# 'personne morale',
|
|
'nombre': 'total_count',
|
|
'estimation': 'estimation_code',
|
|
'jour':'date_day',
|
|
'mois':'date_month',
|
|
'année':'date_year',
|
|
"jour de l'année":'date_jday',
|
|
'pentade':'date_pentade',
|
|
'décade':'date_decade',
|
|
"numéro de la semaine":'date_week',
|
|
"heure fin":'time_stop',
|
|
"heure de fin":'time_stop_hour',
|
|
"minute de fin":'time_stop_min',
|
|
"heure début":'time_start',
|
|
"heure de début":'time_start_hour',
|
|
"minute de début":'time_start_min',
|
|
# 'id universel transmetteur',
|
|
# 'id transmetteur',
|
|
# 'e-mail',
|
|
# 'personne morale du transmetteur',
|
|
# 'id universel observateur',
|
|
# 'nom transmetteur',
|
|
# 'prénom transmetteur',
|
|
# 'transmetteur anonyme',
|
|
# 'anonyme',
|
|
# 'liste complète ?',
|
|
# 'protégée',
|
|
# 'vérification',
|
|
# 'donnée de seconde main',
|
|
# 'commentaire de la liste',
|
|
# 'ordre systématique',
|
|
"détails": 'detail',
|
|
'horaire':'timing',
|
|
"type de localisation": 'precision',
|
|
'comportement': 'behaviour',
|
|
'remarque': 'comment',
|
|
"remarque privée": 'private_comment',
|
|
# 'protection nationale',
|
|
"date d'insertion": 'insert_date',
|
|
"date de dernière modification": 'update_date',
|
|
"code atlas":'atlas_code',
|
|
"code insee": 'insee',
|
|
"altitude": 'altitude',
|
|
"commune": 'place',
|
|
"contient des détails mortalité":'has_death_info',
|
|
# 'id liste'
|
|
} |