139 lines
5.8 KiB
Python
139 lines
5.8 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
from sqlalchemy import text
|
|
|
|
def check_missing_data(con):
|
|
sql = '''
|
|
WITH t1 AS (SELECT
|
|
--tro.*,too.*
|
|
tro.unique_id_sinp_grp
|
|
FROM pr_occtax.t_occurrences_occtax AS too
|
|
JOIN pr_occtax.t_releves_occtax tro USING (id_releve_occtax)
|
|
WHERE too.id_occurrence_occtax NOT IN (
|
|
SELECT cco.id_occurrence_occtax FROM pr_occtax.cor_counting_occtax cco
|
|
)
|
|
)
|
|
SELECT
|
|
s.id_synthese,
|
|
too.id_releve_occtax,
|
|
too.id_occurrence_occtax,
|
|
tro.id_digitiser,
|
|
s.date_min,
|
|
dataset_name,
|
|
dataset_shortname,
|
|
geom_local geom,
|
|
meta_device_entry,
|
|
tro.id_digitiser,
|
|
tro.date_min,
|
|
tro.date_max,
|
|
too.cd_nom,
|
|
too.nom_cite,
|
|
s.nom_cite nom_cite_synthese,
|
|
s.cd_nom cd_nom_synthese,
|
|
s.last_action
|
|
FROM pr_occtax.t_occurrences_occtax AS too
|
|
JOIN pr_occtax.t_releves_occtax tro USING (id_releve_occtax)
|
|
JOIN gn_meta.t_datasets USING (id_dataset)
|
|
LEFT JOIN pr_occtax.cor_counting_occtax cco USING (id_occurrence_occtax)
|
|
LEFT JOIN gn_synthese.synthese s on s.unique_id_sinp = cco.unique_id_sinp_occtax
|
|
WHERE tro.unique_id_sinp_grp IN (SELECT t1.unique_id_sinp_grp FROM t1)
|
|
AND (too.nom_cite != s.nom_cite OR s.nom_cite is null)
|
|
ORDER BY too.id_releve_occtax
|
|
;
|
|
'''
|
|
with con.begin() as cnx:
|
|
return cnx.execute(text(sql)).fetchall()
|
|
|
|
def check_error_synthese(con):
|
|
sql = '''
|
|
WITH t1 AS (SELECT
|
|
--tro.*,too.*
|
|
tro.unique_id_sinp_grp
|
|
FROM pr_occtax.t_occurrences_occtax AS too
|
|
JOIN pr_occtax.t_releves_occtax tro USING (id_releve_occtax)
|
|
)
|
|
SELECT
|
|
s.id_synthese,
|
|
too.id_releve_occtax,
|
|
too.id_occurrence_occtax,
|
|
tro.id_digitiser,
|
|
s.date_min,
|
|
dataset_name,
|
|
dataset_shortname,
|
|
geom_local geom,
|
|
meta_device_entry,
|
|
tro.id_digitiser,
|
|
tro.date_min,
|
|
tro.date_max,
|
|
too.cd_nom,
|
|
too.nom_cite,
|
|
s.nom_cite nom_cite_synthese,
|
|
s.cd_nom cd_nom_synthese,
|
|
s.last_action
|
|
FROM pr_occtax.t_occurrences_occtax AS too
|
|
JOIN pr_occtax.t_releves_occtax tro USING (id_releve_occtax)
|
|
JOIN gn_meta.t_datasets USING (id_dataset)
|
|
LEFT JOIN pr_occtax.cor_counting_occtax cco USING (id_occurrence_occtax)
|
|
LEFT JOIN gn_synthese.synthese s on s.unique_id_sinp = cco.unique_id_sinp_occtax
|
|
WHERE tro.unique_id_sinp_grp IN (SELECT t1.unique_id_sinp_grp FROM t1)
|
|
AND (too.nom_cite != s.nom_cite OR s.nom_cite is null)
|
|
ORDER BY too.id_releve_occtax
|
|
;
|
|
'''
|
|
with con.begin() as cnx:
|
|
return cnx.execute(text(sql)).fetchall()
|
|
|
|
|
|
def resolve_missing_data(con):
|
|
sql = '''
|
|
INSERT INTO pr_occtax.cor_counting_occtax (
|
|
id_occurrence_occtax,id_nomenclature_life_stage,id_nomenclature_sex,
|
|
id_nomenclature_obj_count,id_nomenclature_type_count,count_min,count_max,
|
|
additional_fields)
|
|
SELECT
|
|
-- t.operation_type,
|
|
-- t.operation_date,
|
|
-- CAST(t.table_content->>'id_counting_occtax' AS BIGINT) id_occurrence_occtax,
|
|
CAST(t.table_content->>'id_occurrence_occtax' AS BIGINT) AS id_occurrence_occtax,
|
|
CAST(t.table_content->>'id_nomenclature_life_stage' AS INT) AS id_nomenclature_life_stage,
|
|
CAST(t.table_content->>'id_nomenclature_sex' AS INT) AS id_nomenclature_sex,
|
|
CAST(t.table_content->>'id_nomenclature_obj_count' AS INT) AS id_nomenclature_obj_count,
|
|
CAST(t.table_content->>'id_nomenclature_type_count' AS INT) AS id_nomenclature_type_count,
|
|
CAST(t.table_content->>'count_min' AS INT) AS count_min,
|
|
CAST(t.table_content->>'count_max' AS INT) AS count_max,
|
|
CAST(t.table_content->>'additional_fields' AS JSON) AS additional_fields
|
|
FROM (
|
|
SELECT DISTINCT ON (d1.uuid_attached_row)
|
|
d1.uuid_attached_row, d1.*
|
|
FROM pr_occtax.t_occurrences_occtax o
|
|
INNER JOIN gn_commons.t_history_actions o1 ON o.unique_id_occurence_occtax = o1.uuid_attached_row
|
|
INNER JOIN gn_commons.t_history_actions d1 ON d1.table_content->>'id_occurrence_occtax' = o1.table_content->>'id_occurrence_occtax'
|
|
INNER JOIN gn_commons.t_history_actions d2 ON d2.uuid_attached_row = d1.uuid_attached_row
|
|
INNER JOIN gn_commons.t_history_actions o2 ON d2.table_content->>'id_occurrence_occtax' = o2.table_content->>'id_occurrence_occtax'
|
|
WHERE
|
|
NOT EXISTS (SELECT NULL FROM pr_occtax.cor_counting_occtax c WHERE o.id_occurrence_occtax = c.id_occurrence_occtax)
|
|
AND o1.id_table_location = 6 -- ID table t_occurrence_occtax
|
|
AND d1.id_table_location = 5 -- ID table cor_counting_occtax
|
|
AND d2.id_table_location = 5 -- ID table cor_counting_occtax
|
|
AND o2.id_table_location = 6 AND o2.operation_type = 'I' -- table t_occurrence_occtax
|
|
AND d2.table_content->>'id_occurrence_occtax' <> d1.table_content->>'id_occurrence_occtax'
|
|
AND d2.operation_date <> d1.operation_date
|
|
-- AND o1.table_content->>'id_releve_occtax' = '3522'
|
|
ORDER BY d1.uuid_attached_row, d1.operation_date DESC
|
|
) AS t
|
|
;'''
|
|
with con.begin() as cnx:
|
|
cnx.execute(text(sql))
|
|
|
|
def resolve_synthese_errors(con):
|
|
sql = '''
|
|
|
|
'''
|
|
|
|
if __name__ == '__main__':
|
|
|
|
from pycen import con_gn
|
|
|
|
check_missing_data(con_gn)
|
|
check_error_synthese(con_gn)
|