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:
self.query = self.query.where(
self.model.observers.ilike("%" + self.filters.pop("observers")[0] + "%")
)
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:
self.query = self.query.where(
self.model.date_min <= self.filters.pop("date_max")[0]
)
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"),
),
)
)
# 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))
else:
col = getattr(self.model.__table__.columns, colname)
self.query = self.query.where(col.in_(value))
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:
q = q.filter(model.date_min <= filters.pop("date_max")[0])
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_frameworks")
)
)
if "geoIntersection" in filters:
# Insersect 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_query_all_filters(model, q, filters, user, allowed_datasets):
"""
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 (TRoles): a user object from TRoles
- allowed datasets (List<int>): an array of ID dataset where the users have autorization
"""
# from geonature.core.users.models import UserRigth
# user = UserRigth(
# id_role=user.id_role,
# tag_object_code='3',
# tag_action_code="R",
# id_organisme=user.id_organisme,
# nom_role='Administrateur',
# prenom_role='test'
# )
q = filter_query_with_cruved(model, q, user, allowed_datasets)
if 'observers' in filters:
q = q.filter(
model.observers.ilike('%' + filters.pop('observers')[0] + '%'))
if 'date_min' in filters:
q = q.filter(model.date_min >= filters.pop('date_min')[0])
if 'date_max' in filters:
q = q.filter(model.date_min <= filters.pop('date_max')[0])
if 'id_acquisition_frameworks' 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_frameworks')))
if 'municipalities' in filters:
q = q.filter(
model.id_municipality.in_(
[com for com in filters['municipalities']]))
filters.pop('municipalities')
if 'geoIntersection' in filters:
# Insersect with the geom send from the map
geom_wkt = loads(request.args['geoIntersection'])
# if the geom is a circle
if 'radius' in filters:
radius = filters.pop('radius')[0]
geom_wkt = circle_from_point(geom_wkt, float(radius))
geom_wkb = from_shape(geom_wkt, srid=4326)
q = q.filter(model.the_geom_4326.ST_Intersects(geom_wkb))
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_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_frameworks")
)
)
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_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 "date_min" in filters:
q = q.filter(model.date_min >= filters.pop("date_min")[0])
if "date_max" in filters:
q = q.filter(model.date_min <= filters.pop("date_max")[0])
if "id_acquisition_frameworks" 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_frameworks")
)
)
if "geoIntersection" in filters:
# Insersect 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 = 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
elif colname.startswith("modif_since_validation"):
q = q.filter(model.meta_update_date > model.validation_date)
else:
col = getattr(model.__table__.columns, colname)
q = q.filter(col.in_(value))
return q
