def get_datasets_cruved(info_role, params=dict()):
"""
Return the datasets filtered with cruved
"""
q = DB.session.query(TDatasets).distinct()
# filter with modules
if "module_code" in params:
q = q.filter(TDatasets.modules.any(module_code=params["module_code"]))
# filters with cruved
if info_role.value_filter == "2":
q = q.join(CorDatasetActor,
CorDatasetActor.id_dataset == TDatasets.id_dataset)
# if organism is None => do not filter on id_organism even if level = 2
if info_role.id_organisme is None:
q = q.filter(CorDatasetActor.id_role == info_role.id_role)
else:
q = q.filter(
or_(
CorDatasetActor.id_organism == info_role.id_organisme,
CorDatasetActor.id_role == info_role.id_role,
))
elif info_role.value_filter == "1":
q = q.join(CorDatasetActor,
CorDatasetActor.id_dataset == TDatasets.id_dataset).filter(
CorDatasetActor.id_role == info_role.id_role)
# filters query string
if "active" in request.args:
q = q.filter(TDatasets.active == bool(request.args["active"]))
params.pop("active")
if "id_acquisition_framework" in params:
if type(request.args["id_acquisition_framework"]) is list:
q = q.filter(
TDatasets.id_acquisition_framework.in_([
int(id_af) for id_af in params["id_acquisition_framework"]
]))
else:
q = q.filter(TDatasets.id_acquisition_framework == int(
request.args["id_acquisition_framework"]))
params.pop("id_acquisition_framework")
table_columns = TDatasets.__table__.columns
# Generic Filters
for param in params:
if param in table_columns:
col = getattr(table_columns, param)
testT = testDataType(params[param], col.type, param)
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(col == params[param])
if "orderby" in params:
try:
orderCol = getattr(TDatasets.__table__.columns, params["orderby"])
q = q.order_by(orderCol)
except AttributeError:
log.error("the attribute to order on does not exist")
data = q.distinct().all()
return [d.as_dict(True) for d in data]
def get_datasets_cruved(info_role, params):
"""
Return the datasets filtered with cruved
"""
q = DB.session.query(TDatasets)
# filters with cruved
if info_role.tag_object_code == '2':
q = q.join(CorDatasetActor,
CorDatasetActor.id_dataset == TDatasets.id_dataset)
# if organism is None => do not filter on id_organism even if level = 2
if info_role.id_organisme is None:
q = q.filter(CorDatasetActor.id_role == info_role.id_role)
else:
q = q.filter(
or_(CorDatasetActor.id_organism == info_role.id_organisme,
CorDatasetActor.id_role == info_role.id_role))
elif info_role.tag_object_code == '1':
q = q.join(CorDatasetActor,
CorDatasetActor.id_dataset == TDatasets.id_dataset).filter(
CorDatasetActor.id_role == info_role.id_role)
# filters query string
if 'active' in request.args:
q = q.filter(TDatasets.active == bool(request.args['active']))
params.pop('active')
if 'id_acquisition_framework' in params:
if type(request.args['id_acquisition_framework']) is list:
q = q.filter(
TDatasets.id_acquisition_framework.in_([
int(id_af) for id_af in params['id_acquisition_framework']
]))
else:
q = q.filter(TDatasets.id_acquisition_framework == int(
request.args['id_acquisition_framework']))
params.pop('id_acquisition_framework')
table_columns = TDatasets.__table__.columns
# Generic Filters
for param in params:
if param in table_columns:
col = getattr(table_columns, param)
testT = testDataType(params[param], col.type, param)
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(col == params[param])
data = q.all()
return [d.as_dict(True) for d in data]
def get_query_occtax_filters(args,
mappedView,
q,
from_generic_table=False,
obs_txt_column="observers_txt"):
if from_generic_table:
mappedView = mappedView.tableDef.columns
params = args.to_dict()
testT = None
if "cd_nom" in params:
testT = testDataType(params.get("cd_nom"), DB.Integer, "cd_nom")
if testT:
raise GeonatureApiError(message=testT)
q = q.join(
TOccurrencesOccurrence,
TOccurrencesOccurrence.id_releve_occtax ==
mappedView.id_releve_occtax,
).filter(TOccurrencesOccurrence.cd_nom == int(params.pop("cd_nom")))
if "observers" in params:
if not is_already_joined(corRoleRelevesOccurrence, q):
q = q.join(
corRoleRelevesOccurrence,
corRoleRelevesOccurrence.id_releve_occtax ==
mappedView.id_releve_occtax,
)
q = q.filter(
corRoleRelevesOccurrence.id_role.in_(args.getlist("observers")))
params.pop("observers")
if "date_up" in params:
testT = testDataType(params.get("date_up"), DB.DateTime, "date_up")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_max <= params.pop("date_up"))
if "date_low" in params:
testT = testDataType(params.get("date_low"), DB.DateTime, "date_low")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_min >= params.pop("date_low"))
if "date_eq" in params:
testT = testDataType(params.get("date_eq"), DB.DateTime, "date_eq")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_min == params.pop("date_eq"))
if "altitude_max" in params:
testT = testDataType(params.get("altitude_max"), DB.Integer,
"altitude_max")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.altitude_max <= params.pop("altitude_max"))
if "altitude_min" in params:
testT = testDataType(params.get("altitude_min"), DB.Integer,
"altitude_min")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.altitude_min >= params.pop("altitude_min"))
if "organism" in params:
q = q.join(CorDatasetActor,
CorDatasetActor.id_dataset == mappedView.id_dataset).filter(
CorDatasetActor.id_actor == int(params.pop("organism")))
if "observers_txt" in params:
observers_query = "%{}%".format(params.pop("observers_txt"))
q = q.filter(
getattr(mappedView, obs_txt_column).ilike(observers_query))
if from_generic_table:
table_columns = mappedView
else:
table_columns = mappedView.__table__.columns
if "non_digital_proof" in params:
if not is_already_joined(TOccurrencesOccurrence, q):
q = q.join(
TOccurrencesOccurrence,
mappedView.id_releve_occtax ==
TOccurrencesOccurrence.id_releve_occtax,
)
q = q.filter(TOccurrencesOccurrence.non_digital_proof == params.pop(
"non_digital_proof"))
if "digital_proof" in params:
if not is_already_joined(TOccurrencesOccurrence, q):
q = q.join(
TOccurrencesOccurrence,
mappedView.id_releve_occtax ==
TOccurrencesOccurrence.id_releve_occtax,
)
q = q.filter(TOccurrencesOccurrence.digital_proof == params.pop(
"digital_proof"))
# Generic Filters
for param in params:
if param in table_columns:
col = getattr(table_columns, param)
testT = testDataType(params[param], col.type, param)
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(col == params[param])
releve_filters, occurrence_filters, counting_filters = get_nomenclature_filters(
params)
if len(releve_filters) > 0:
q = q.join(
TRelevesOccurrence,
mappedView.id_releve_occtax == TRelevesOccurrence.id_releve_occtax,
)
for nomenclature in releve_filters:
col = getattr(TRelevesOccurrence.__table__.columns, nomenclature)
q = q.filter(col == params.pop(nomenclature))
if len(occurrence_filters) > 0:
if not is_already_joined(TOccurrencesOccurrence, q):
q = q.join(
TOccurrencesOccurrence,
mappedView.id_releve_occtax ==
TOccurrencesOccurrence.id_releve_occtax,
)
for nomenclature in occurrence_filters:
col = getattr(TOccurrencesOccurrence.__table__.columns,
nomenclature)
q = q.filter(col == params.pop(nomenclature))
if len(counting_filters) > 0:
if len(occurrence_filters) > 0:
q = q.join(
CorCountingOccurrence,
TOccurrencesOccurrence.id_occurrence_occtax ==
CorCountingOccurrence.id_occurrence_occtax,
)
else:
q = q.join(
TOccurrencesOccurrence,
TOccurrencesOccurrence.id_releve_occtax ==
mappedView.id_releve_occtax,
).join(
CorCountingOccurrence,
TOccurrencesOccurrence.id_occurrence_occtax ==
CorCountingOccurrence.id_occurrence_occtax,
)
for nomenclature in counting_filters:
col = getattr(CorCountingOccurrence.__table__.columns,
nomenclature)
q = q.filter(col == params.pop(nomenclature))
return q
def get_query_occtax_filters(args, mappedView, q, from_generic_table=False):
if from_generic_table:
mappedView = mappedView.tableDef.columns
params = args.to_dict()
testT = None
if "cd_nom" in params:
testT = testDataType(params.get("cd_nom"), DB.Integer, "cd_nom")
if testT:
raise GeonatureApiError(message=testT)
q = q.join(
TOccurrencesOccurrence,
TOccurrencesOccurrence.id_releve_occtax == mappedView.id_releve_occtax,
).filter(TOccurrencesOccurrence.cd_nom == int(params.pop("cd_nom")))
if "observers" in params:
q = q.join(
corRoleRelevesOccurrence,
corRoleRelevesOccurrence.columns.id_releve_occtax
== mappedView.id_releve_occtax,
).filter(
corRoleRelevesOccurrence.columns.id_role.in_(args.getlist("observers"))
)
params.pop("observers")
if "date_up" in params:
testT = testDataType(params.get("date_up"), DB.DateTime, "date_up")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_max <= params.pop("date_up"))
if "date_low" in params:
testT = testDataType(params.get("date_low"), DB.DateTime, "date_low")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_min >= params.pop("date_low"))
if "date_eq" in params:
testT = testDataType(params.get("date_eq"), DB.DateTime, "date_eq")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_min == params.pop("date_eq"))
if "altitude_max" in params:
testT = testDataType(params.get("altitude_max"), DB.Integer, "altitude_max")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.altitude_max <= params.pop("altitude_max"))
if "altitude_min" in params:
testT = testDataType(params.get("altitude_min"), DB.Integer, "altitude_min")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.altitude_min >= params.pop("altitude_min"))
if "organism" in params:
q = q.join(
CorDatasetActor, CorDatasetActor.id_dataset == mappedView.id_dataset
).filter(CorDatasetActor.id_actor == int(params.pop("organism")))
if "observateurs" in params:
observers_query = "%{}%".format(params.pop("observateurs"))
q = q.filter(mappedView.observateurs.ilike(observers_query))
if from_generic_table:
table_columns = mappedView
else:
table_columns = mappedView.__table__.columns
# Generic Filters
for param in params:
if param in table_columns:
col = getattr(table_columns, param)
testT = testDataType(params[param], col.type, param)
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(col == params[param])
releve_filters, occurrence_filters, counting_filters = get_nomenclature_filters(
params
)
if len(releve_filters) > 0:
q = q.join(
TRelevesOccurrence,
mappedView.id_releve_occtax == TRelevesOccurrence.id_releve_occtax,
)
for nomenclature in releve_filters:
col = getattr(TRelevesOccurrence.__table__.columns, nomenclature)
q = q.filter(col == params.pop(nomenclature))
if len(occurrence_filters) > 0:
q = q.join(
TOccurrencesOccurrence,
mappedView.id_releve_occtax == TOccurrencesOccurrence.id_releve_occtax,
)
for nomenclature in occurrence_filters:
col = getattr(TOccurrencesOccurrence.__table__.columns, nomenclature)
q = q.filter(col == params.pop(nomenclature))
if len(counting_filters) > 0:
if len(occurrence_filters) > 0:
q = q.join(
CorCountingOccurrence,
TOccurrencesOccurrence.id_occurrence_occtax
== CorCountingOccurrence.id_occurrence_occtax,
)
else:
q = q.join(
TOccurrencesOccurrence,
TOccurrencesOccurrence.id_releve_occtax == mappedView.id_releve_occtax,
).join(
CorCountingOccurrence,
TOccurrencesOccurrence.id_occurrence_occtax
== CorCountingOccurrence.id_occurrence_occtax,
)
for nomenclature in counting_filters:
col = getattr(CorCountingOccurrence.__table__.columns, nomenclature)
q = q.filter(col == params.pop(nomenclature))
# Order by
if "orderby" in params:
if params.get("orderby") in mappedView.__table__.columns:
orderCol = getattr(mappedView.__table__.columns, params["orderby"])
# else:
# orderCol = getattr(
# mappedView.__table__.columns,
# 'occ_meta_create_date'
# )
if "order" in params:
if params["order"] == "desc":
orderCol = orderCol.desc()
q = q.order_by(orderCol)
return q
def get_query_occtax_filters(args, mappedView, q, from_generic_table=False):
if from_generic_table:
mappedView = mappedView.tableDef.columns
params = args.to_dict()
testT = None
if "cd_nom" in params:
testT = testDataType(params.get("cd_nom"), DB.Integer, "cd_nom")
if testT:
raise GeonatureApiError(message=testT)
q = q.join(
TOccurrencesOccurrence,
TOccurrencesOccurrence.id_releve_occtax ==
mappedView.id_releve_occtax,
).filter(TOccurrencesOccurrence.cd_nom == int(params.pop("cd_nom")))
if "observers" in params:
q = q.join(
corRoleRelevesOccurrence,
corRoleRelevesOccurrence.columns.id_releve_occtax ==
mappedView.id_releve_occtax,
).filter(
corRoleRelevesOccurrence.columns.id_role.in_(
args.getlist("observers")))
params.pop("observers")
if "date_up" in params:
testT = testDataType(params.get("date_up"), DB.DateTime, "date_up")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_max <= params.pop("date_up"))
if "date_low" in params:
testT = testDataType(params.get("date_low"), DB.DateTime, "date_low")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_min >= params.pop("date_low"))
if "date_eq" in params:
testT = testDataType(params.get("date_eq"), DB.DateTime, "date_eq")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.date_min == params.pop("date_eq"))
if "altitude_max" in params:
testT = testDataType(params.get("altitude_max"), DB.Integer,
"altitude_max")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.altitude_max <= params.pop("altitude_max"))
if "altitude_min" in params:
testT = testDataType(params.get("altitude_min"), DB.Integer,
"altitude_min")
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(mappedView.altitude_min >= params.pop("altitude_min"))
if "organism" in params:
q = q.join(CorDatasetActor,
CorDatasetActor.id_dataset == mappedView.id_dataset).filter(
CorDatasetActor.id_actor == int(params.pop("organism")))
if "observateurs" in params:
observers_query = "%{}%".format(params.pop("observateurs"))
q = q.filter(mappedView.observateurs.ilike(observers_query))
if from_generic_table:
table_columns = mappedView
else:
table_columns = mappedView.__table__.columns
# Generic Filters
for param in params:
if param in table_columns:
col = getattr(table_columns, param)
testT = testDataType(params[param], col.type, param)
if testT:
raise GeonatureApiError(message=testT)
q = q.filter(col == params[param])
releve_filters, occurrence_filters, counting_filters = get_nomenclature_filters(
params)
if len(releve_filters) > 0:
q = q.join(
TRelevesOccurrence,
mappedView.id_releve_occtax == TRelevesOccurrence.id_releve_occtax,
)
for nomenclature in releve_filters:
col = getattr(TRelevesOccurrence.__table__.columns, nomenclature)
q = q.filter(col == params.pop(nomenclature))
if len(occurrence_filters) > 0:
q = q.join(
TOccurrencesOccurrence,
mappedView.id_releve_occtax ==
TOccurrencesOccurrence.id_releve_occtax,
)
for nomenclature in occurrence_filters:
col = getattr(TOccurrencesOccurrence.__table__.columns,
nomenclature)
q = q.filter(col == params.pop(nomenclature))
if len(counting_filters) > 0:
if len(occurrence_filters) > 0:
q = q.join(
CorCountingOccurrence,
TOccurrencesOccurrence.id_occurrence_occtax ==
CorCountingOccurrence.id_occurrence_occtax,
)
else:
q = q.join(
TOccurrencesOccurrence,
TOccurrencesOccurrence.id_releve_occtax ==
mappedView.id_releve_occtax,
).join(
CorCountingOccurrence,
TOccurrencesOccurrence.id_occurrence_occtax ==
CorCountingOccurrence.id_occurrence_occtax,
)
for nomenclature in counting_filters:
col = getattr(CorCountingOccurrence.__table__.columns,
nomenclature)
q = q.filter(col == params.pop(nomenclature))
# Order by
if "orderby" in params:
if params.get("orderby") in mappedView.__table__.columns:
orderCol = getattr(mappedView.__table__.columns, params["orderby"])
# else:
# orderCol = getattr(
# mappedView.__table__.columns,
# 'occ_meta_create_date'
# )
if "order" in params:
if params["order"] == "desc":
orderCol = orderCol.desc()
q = q.order_by(orderCol)
return q