def filter_by_params(self, params={}):
if "active" in params:
self = self.filter(TDatasets.active == bool(params["active"]))
params.pop("active")
if "id_acquisition_framework" in params:
if type(params["id_acquisition_framework"]) is list:
self = self.filter(
TDatasets.id_acquisition_framework.in_(
[int(id_af) for id_af in params["id_acquisition_framework"]]
)
)
else:
self = self.filter(
TDatasets.id_acquisition_framework == int(params["id_acquisition_framework"])
)
table_columns = TDatasets.__table__.columns
if "orderby" in params:
try:
orderCol = getattr(table_columns, params.pop("orderby"))
self = self.order_by(orderCol)
except AttributeError:
raise BadRequest("the attribute to order on does not exist")
if "module_code" in params:
self = self.filter(TDatasets.modules.any(module_code=params["module_code"]))
# 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 BadRequest(testT)
self = self.filter(col == params[param])
return self
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=dict(), as_model=False):
"""
Return the datasets filtered with cruved
Params:
params (dict): parameter to add where clause
as_model (boolean): default false, if truereturn an array of model
instead of an array of dict
"""
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 in ("1", "2"):
q = cruved_filter(q, TDatasets, info_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()
if as_model:
return data
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:
# if not from generic table, the FROM clause is already from TRelevesOccurrences
if from_generic_table:
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