def filter_other_filters(self):
"""
Other filters
"""
if "id_dataset" in self.filters:
self.query = self.query.where(
self.model.id_dataset.in_(self.filters.pop("id_dataset")))
if "observers" in self.filters:
#découpe des éléments saisies par les espaces
observers = (self.filters.pop("observers")[0]).split()
self.query = self.query.where(
and_(*[
self.model.observers.ilike("%" + observer + "%")
for observer in observers
]))
if "observers_list" in self.filters:
self.query = self.query.where(
and_(*[
self.model.observers.ilike("%" +
observer.get('nom_complet') +
"%")
for observer in self.filters.pop("observers_list")
]))
if "id_organism" in self.filters:
datasets = (DB.session.query(CorDatasetActor.id_dataset).filter(
CorDatasetActor.id_organism.in_(
self.filters.pop("id_organism"))).all())
formated_datasets = [d[0] for d in datasets]
self.query = self.query.where(
self.model.id_dataset.in_(formated_datasets))
if "date_min" in self.filters:
self.query = self.query.where(
self.model.date_min >= self.filters.pop("date_min")[0])
if "date_max" in self.filters:
# set the date_max at 23h59 because a hour can be set in timestamp
date_max = datetime.datetime.strptime(
self.filters.pop("date_max")[0], '%Y-%m-%d')
date_max = date_max.replace(hour=23, minute=59, second=59)
self.query = self.query.where(self.model.date_max <= date_max)
if "id_acquisition_framework" in self.filters:
self.query = self.query.where(
self.model.id_acquisition_framework.in_(
self.filters.pop("id_acquisition_framework")))
if "geoIntersection" in self.filters:
# Insersect with the geom send from the map
ors = []
for str_wkt in self.filters["geoIntersection"]:
# if the geom is a circle
if "radius" in self.filters:
radius = self.filters.pop("radius")[0]
wkt = loads(str_wkt)
wkt = circle_from_point(wkt, float(radius))
else:
wkt = loads(str_wkt)
geom_wkb = from_shape(wkt, srid=4326)
ors.append(self.model.the_geom_4326.ST_Intersects(geom_wkb))
self.query = self.query.where(or_(*ors))
self.filters.pop("geoIntersection")
if "period_start" in self.filters and "period_end" in self.filters:
period_start = self.filters.pop("period_start")[0]
period_end = self.filters.pop("period_end")[0]
self.query = self.query.where(
or_(
func.gn_commons.is_in_period(
func.date(self.model.date_min),
func.to_date(period_start, "DD-MM"),
func.to_date(period_end, "DD-MM"),
),
func.gn_commons.is_in_period(
func.date(self.model.date_max),
func.to_date(period_start, "DD-MM"),
func.to_date(period_end, "DD-MM"),
),
))
# use for validation module since the class is factorized
if "modif_since_validation" in self.filters:
self.query = self.query.where(
self.model.meta_update_date > self.model.validation_date)
self.filters.pop("modif_since_validation")
# generic filters
for colname, value in self.filters.items():
if colname.startswith("area"):
self.add_join(CorAreaSynthese, CorAreaSynthese.id_synthese,
self.model.id_synthese)
self.query = self.query.where(
CorAreaSynthese.id_area.in_(value))
elif colname.startswith("id_"):
col = getattr(self.model.__table__.columns, colname)
self.query = self.query.where(col.in_(value))
else:
col = getattr(self.model.__table__.columns, colname)
self.query = self.query.where(
col.ilike("%{}%".format(value[0])))
def filter_query_all_filters(model, q, filters, user):
"""
Return a query filtered with the cruved and all
the filters available in the synthese form
parameters:
- q (SQLAchemyQuery): an SQLAchemy query
- filters (dict): a dict of filter
- user (User): a user object from User
- allowed datasets (List<int>): an array of ID dataset where the users have autorization
"""
q = filter_query_with_cruved(model, q, user)
if "observers" in filters:
q = q.filter(
model.observers.ilike("%" + filters.pop("observers")[0] + "%"))
if "id_organism" in filters:
id_datasets = (DB.session.query(CorDatasetActor.id_dataset).filter(
CorDatasetActor.id_organism.in_(filters.pop("id_organism"))).all())
formated_datasets = [d[0] for d in id_datasets]
q = q.filter(model.id_dataset.in_(formated_datasets))
if "date_min" in filters:
q = q.filter(model.date_min >= filters.pop("date_min")[0])
if "date_max" in filters:
# set the date_max at 23h59 because a hour can be set in timestamp
date_max = datetime.datetime.strptime(
filters.pop("date_max")[0], '%Y-%m-%d')
date_max = date_max.replace(hour=23, minute=59, second=59)
q = q.filter(model.date_max <= date_max)
if "id_acquisition_framework" in filters:
q = q.join(
TAcquisitionFramework,
model.id_acquisition_framework ==
TAcquisitionFramework.id_acquisition_framework,
)
q = q.filter(
TAcquisitionFramework.id_acquisition_framework.in_(
filters.pop("id_acquisition_framework")))
if "geoIntersection" in filters:
# Intersect with the geom send from the map
ors = []
for str_wkt in filters["geoIntersection"]:
# if the geom is a circle
if "radius" in filters:
radius = filters.pop("radius")[0]
wkt = loads(str_wkt)
wkt = circle_from_point(wkt, float(radius))
else:
wkt = loads(str_wkt)
geom_wkb = from_shape(wkt, srid=4326)
ors.append(model.the_geom_4326.ST_Intersects(geom_wkb))
q = q.filter(or_(*ors))
filters.pop("geoIntersection")
if "period_start" in filters and "period_end" in filters:
period_start = filters.pop("period_start")[0]
period_end = filters.pop("period_end")[0]
q = q.filter(
or_(
func.gn_commons.is_in_period(
func.date(model.date_min),
func.to_date(period_start, "DD-MM"),
func.to_date(period_end, "DD-MM"),
),
func.gn_commons.is_in_period(
func.date(model.date_max),
func.to_date(period_start, "DD-MM"),
func.to_date(period_end, "DD-MM"),
),
))
q, filters = filter_taxonomy(model, q, filters)
# generic filters
join_on_cor_area = False
for colname, value in filters.items():
if colname.startswith("area"):
if not join_on_cor_area:
q = q.join(CorAreaSynthese,
CorAreaSynthese.id_synthese == model.id_synthese)
q = q.filter(CorAreaSynthese.id_area.in_(value))
join_on_cor_area = True
else:
col = getattr(model.__table__.columns, colname)
q = q.filter(col.in_(value))
return q
def filter_other_filters(self):
"""
Other filters
"""
if "has_medias" in self.filters:
self.query = self.query.where(
self.model.has_medias
)
if "id_dataset" in self.filters:
self.query = self.query.where(
self.model.id_dataset.in_(self.filters.pop("id_dataset"))
)
if "observers" in self.filters:
# découpe des éléments saisies par les espaces
observers = (self.filters.pop("observers")[0]).split()
self.query = self.query.where(
and_(*[self.model.observers.ilike("%" + observer + "%") for observer in observers])
)
if "observers_list" in self.filters:
self.query = self.query.where(
and_(
*[
self.model.observers.ilike("%" + observer.get("nom_complet") + "%")
for observer in self.filters.pop("observers_list")
]
)
)
if "id_organism" in self.filters:
datasets = (
DB.session.query(CorDatasetActor.id_dataset)
.filter(CorDatasetActor.id_organism.in_(self.filters.pop("id_organism")))
.all()
)
formated_datasets = [d[0] for d in datasets]
self.query = self.query.where(self.model.id_dataset.in_(formated_datasets))
if "date_min" in self.filters:
self.query = self.query.where(self.model.date_min >= self.filters.pop("date_min")[0])
if "date_max" in self.filters:
# set the date_max at 23h59 because a hour can be set in timestamp
date_max = datetime.datetime.strptime(self.filters.pop("date_max")[0], "%Y-%m-%d")
date_max = date_max.replace(hour=23, minute=59, second=59)
self.query = self.query.where(self.model.date_max <= date_max)
if "id_acquisition_framework" in self.filters:
if hasattr(self.model, 'id_acquisition_framework'):
self.query = self.query.where(
self.model.id_acquisition_framework.in_(
self.filters.pop("id_acquisition_framework")
)
)
else:
self.add_join(TDatasets, self.model.id_dataset, TDatasets.id_dataset)
self.query = self.query.where(
TDatasets.id_acquisition_framework.in_(
self.filters.pop("id_acquisition_framework")
)
)
if "geoIntersection" in self.filters:
# Insersect with the geom send from the map
ors = []
for str_wkt in self.filters["geoIntersection"]:
# if the geom is a circle
if "radius" in self.filters:
radius = self.filters.pop("radius")[0]
wkt = loads(str_wkt)
wkt = circle_from_point(wkt, float(radius))
else:
wkt = loads(str_wkt)
geom_wkb = from_shape(wkt, srid=4326)
ors.append(self.model.the_geom_4326.ST_Intersects(geom_wkb))
self.query = self.query.where(or_(*ors))
self.filters.pop("geoIntersection")
if "period_start" in self.filters and "period_end" in self.filters:
period_start = self.filters.pop("period_start")[0]
period_end = self.filters.pop("period_end")[0]
self.query = self.query.where(
or_(
func.gn_commons.is_in_period(
func.date(self.model.date_min),
func.to_date(period_start, "DD-MM"),
func.to_date(period_end, "DD-MM"),
),
func.gn_commons.is_in_period(
func.date(self.model.date_max),
func.to_date(period_start, "DD-MM"),
func.to_date(period_end, "DD-MM"),
),
)
)
if "unique_id_sinp" in self.filters:
try:
uuid_filter = uuid.UUID(self.filters.pop("unique_id_sinp")[0])
except ValueError as e:
raise BadRequest(str(e))
self.query = self.query.where(self.model.unique_id_sinp == uuid_filter)
# generic filters
for colname, value in self.filters.items():
if colname.startswith("area"):
self.add_join(CorAreaSynthese, CorAreaSynthese.id_synthese, self.model.id_synthese)
self.query = self.query.where(CorAreaSynthese.id_area.in_(value))
elif colname.startswith("id_"):
col = getattr(self.model.__table__.columns, colname)
self.query = self.query.where(col.in_(value))
elif hasattr(self.model.__table__.columns, colname):
col = getattr(self.model.__table__.columns, colname)
if str(col.type) == "INTEGER":
if colname in ["precision"]:
self.query = self.query.where(col <= value[0])
else:
self.query = self.query.where(col == value[0])
else:
self.query = self.query.where(col.ilike("%{}%".format(value[0])))