def get_site_areas(id_site):
"""
Get areas of a site from cor_site_area as geojson
.. :quickref: Monitoring;
:param id_module: int
:type id_module: int
:param id_area_type:
:type id_area_type: int
"""
params = request.args
q = (DB.session.query(corSiteArea,
func.ST_Transform(LAreas.geom, 4326)).join(
LAreas,
LAreas.id_area == corSiteArea.c.id_area).filter(
corSiteArea.c.id_base_site == id_site))
if "id_area_type" in params:
q = q.filter(LAreas.id_type == params["id_area_type"])
if "id_module" in params:
q = q.join(corSiteModule,
corSiteModule.c.id_base_site == id_site).filter(
corSiteModule.c.id_module == params["id_module"])
data = q.all()
features = []
for d in data:
feature = get_geojson_feature(d[2])
feature["id"] = d[1]
features.append(feature)
return FeatureCollection(features)
def get_municipalities():
"""List all enabled municipalities
---
tags:
- Reférentiel géo
definitions:
area_name:
type: string
description: Municipality name
area_code:
type: string
description: Municipality insee code
geometry:
type: geometry
responses:
200:
description: A list of municipalities
"""
try:
q = db.session.query(
LAreas.area_name,
LAreas.area_code,
func.ST_Transform(LAreas.geom, 4326).label("geom"),
).filter(LAreas.enable, LAreas.id_type == 101)
datas = q.all()
features = []
for data in datas:
feature = get_geojson_feature(data.geom)
feature["properties"]["area_name"] = data.area_name
feature["properties"]["area_code"] = data.area_code
features.append(feature)
return FeatureCollection(features)
except Exception as e:
return {"message": str(e)}, 400
def format_site(site, dashboard=False):
feature = get_geojson_feature(site.geom)
site_dict = site.as_dict(True)
for k in site_dict:
if k not in ("geom", ):
feature["properties"][k] = site_dict[k]
if dashboard:
# Site creator can delete it only if no visit have been added by others
feature["properties"]["creator_can_delete"] = (
site.id_role
and VisitModel.query.filter_by(id_site=site.id_site).filter(
or_(
VisitModel.id_role != site.id_role,
VisitModel.id_role.is_(None),
)).count() == 0)
return feature
def export_sites_xls(user_id):
current_user = get_user_if_exists()
try:
if current_user.id_user != user_id:
return ("unauthorized"), 403
title_style = xlwt.easyxf("font: bold on")
date_style = xlwt.easyxf(num_format_str="D/M/YY")
wb = xlwt.Workbook()
# SITES SHEET
ws = wb.add_sheet("mes sites")
sites = _get_user_sites(user_id)
fields = (
{
"col_name": "id_site",
"getter": lambda s: s.id_site
},
{
"col_name": "Programme",
"getter": lambda s: s.program.title
},
{
"col_name": "Type",
"getter": lambda s: s.site_type.type
},
{
"col_name": "Nom",
"getter": lambda s: s.name
},
{
"col_name": "Coord. x",
"getter": lambda s: s.coordinates[0]
},
{
"col_name": "Coord. y",
"getter": lambda s: s.coordinates[1]
},
{
"col_name": "Date création",
"getter": lambda s: s.timestamp_create,
"style": date_style,
},
)
row = 0
for col, field in enumerate(fields):
ws.write(row, col, field["col_name"], title_style)
row += 1
for site in sites:
site.coordinates = get_geojson_feature(
site.geom)["geometry"]["coordinates"]
for col, field in enumerate(fields):
args = []
if field.get("style"):
args.append(field.get("style"))
ws.write(row, col, field["getter"](site), *args)
row += 1
# VISITS SHEET
ws = wb.add_sheet("mes visites")
visits = VisitModel.query.filter_by(id_role=user_id)
basic_fields = (
{
"col_name": "id_visit",
"getter": lambda s: s.id_visit
},
{
"col_name": "Site",
"getter": lambda s: s.site.name
},
{
"col_name": "Date",
"getter": lambda s: s.date,
"style": date_style,
},
)
json_keys = list(
set([key for v in visits for key in v.json_data.keys()]))
row, col = 0, 0
for field in basic_fields:
ws.write(row, col, field["col_name"], title_style)
col += 1
for key in json_keys:
ws.write(row, col, key, title_style)
col += 1
row, col = 1, 0
for visit in visits:
for field in basic_fields:
args = []
if field.get("style"):
args.append(field.get("style"))
ws.write(row, col, field["getter"](visit), *args)
col += 1
for key in json_keys:
ws.write(row, col, visit.json_data.get(key))
col += 1
row += 1
col = 0
# In memory save and return xls file
xls_file = io.BytesIO()
wb.save(xls_file)
output = make_response(xls_file.getvalue())
output.headers["Content-Disposition"] = ("attachment; filename=" +
"export_sites.xls")
output.headers["Content-type"] = "application/xls"
return output
except Exception as e:
current_app.logger.warning("Error: %s", str(e))
return {"error_message": str(e)}, 400
def get_observations_by_user_id(user_id):
try:
observations = (db.session.query(
ObservationModel,
ProgramsModel,
UserModel.username,
func.json_agg(
func.json_build_array(MediaModel.filename,
MediaModel.id_media)).label("images"),
LAreas.area_name,
LAreas.area_code,
).filter(ObservationModel.id_role == user_id).join(
LAreas,
LAreas.id_area == ObservationModel.municipality,
isouter=True,
).join(
ProgramsModel,
ProgramsModel.id_program == ObservationModel.id_program,
isouter=True,
full=True,
).join(
ObservationMediaModel,
ObservationMediaModel.id_data_source ==
ObservationModel.id_observation,
isouter=True,
).join(
MediaModel,
ObservationMediaModel.id_media == MediaModel.id_media,
isouter=True,
).join(
UserModel,
ObservationModel.id_role == UserModel.id_user,
full=True,
).group_by(
ObservationModel.id_observation,
ProgramsModel.id_program,
UserModel.username,
LAreas.area_name,
LAreas.area_code,
))
observations = observations.order_by(
desc(ObservationModel.timestamp_create))
# current_app.logger.debug(str(observations))
observations = observations.all()
try:
if current_app.config.get("API_TAXHUB") is not None:
taxon_repository = []
taxhub_list_id = []
for observation in observations:
if (observation.ProgramsModel.taxonomy_list
not in taxhub_list_id):
taxhub_list_id.append(
observation.ProgramsModel.taxonomy_list)
for tax_list in taxhub_list_id:
taxon_repository.append(mkTaxonRepository(tax_list))
features = []
except Exception as e:
return {"message": str(e)}, 500
for observation in observations:
feature = get_geojson_feature(observation.ObservationModel.geom)
feature["properties"]["municipality"] = {
"name": observation.area_name,
"code": observation.area_code,
}
# Observer
feature["properties"]["observer"] = {
"username": observation.username
}
# Observer submitted media
feature["properties"]["image"] = ("/".join([
"/api",
current_app.config["MEDIA_FOLDER"],
observation.images[0][0],
]) if observation.images and observation.images != [[None, None]]
else None)
# Photos
feature["properties"]["photos"] = [{
"url":
"/media/{}".format(filename),
"id_media":
id_media
} for filename, id_media in observation.images
if id_media is not None]
# Municipality
observation_dict = observation.ObservationModel.as_dict(True)
for k in observation_dict:
if k in obs_keys and k != "municipality":
feature["properties"][k] = observation_dict[k]
# Program
program_dict = observation.ProgramsModel.as_dict(True)
for program in program_dict:
if program == "title":
feature["properties"]["program_title"] = program_dict[
program]
# TaxRef
if current_app.config.get("API_TAXHUB") is None:
taxref = Taxref.query.filter(Taxref.cd_nom == observation.
ObservationModel.cd_nom).first()
if taxref:
feature["properties"]["taxref"] = taxref.as_dict(True)
medias = TMedias.query.filter(TMedias.cd_ref == observation.
ObservationModel.cd_nom).all()
if medias:
feature["properties"]["medias"] = [
media.as_dict(True) for media in medias
]
else:
try:
for taxon_rep in taxon_repository:
for taxon in taxon_rep:
if (taxon["taxref"]["cd_nom"] ==
observation.ObservationModel.cd_nom):
feature["properties"]["nom_francais"] = taxon[
"nom_francais"]
feature["properties"]["taxref"] = taxon[
"taxref"]
feature["properties"]["medias"] = taxon[
"medias"]
except StopIteration:
pass
features.append(feature)
return FeatureCollection(features), 200
except Exception as e:
raise e
current_app.logger.critical("[get_program_observations] Error: %s",
str(e))
return {"message": str(e)}, 400
def get_all_observations() -> Union[FeatureCollection, Tuple[Dict, int]]:
"""Get all observations from all programs
GET
---
tags:
- observations
responses:
200:
description: A list of all species lists
"""
try:
observations = (db.session.query(
ObservationModel,
UserModel.username,
MediaModel.filename.label("image"),
LAreas.area_name,
LAreas.area_code,
).filter(ProgramsModel.is_active).join(
LAreas,
LAreas.id_area == ObservationModel.municipality,
isouter=True,
).join(
ProgramsModel,
ProgramsModel.id_program == ObservationModel.id_program,
isouter=True,
).join(
ObservationMediaModel,
ObservationMediaModel.id_data_source ==
ObservationModel.id_observation,
isouter=True,
).join(
MediaModel,
ObservationMediaModel.id_media == MediaModel.id_media,
isouter=True,
).join(
UserModel,
ObservationModel.id_role == UserModel.id_user,
full=True,
))
observations = observations.order_by(
desc(ObservationModel.timestamp_create))
# current_app.logger.debug(str(observations))
observations = observations.all()
# loop to retrieve taxonomic data from all programs
if current_app.config.get("API_TAXHUB") is not None:
programs = ProgramsModel.query.all()
taxon_repository = []
for program in programs:
taxhub_list_id = (ProgramsModel.query.filter_by(
id_program=program.id_program).one().taxonomy_list)
taxon_data = mkTaxonRepository(taxhub_list_id)
try:
for taxon in taxon_data:
if taxon not in taxon_repository:
taxon_repository.append(taxon)
except Exception as e:
current_app.logger.critical(str(e))
features = []
for observation in observations:
feature = get_geojson_feature(observation.ObservationModel.geom)
feature["properties"]["municipality"] = {
"name": observation.area_name,
"code": observation.area_code,
}
# Observer
feature["properties"]["observer"] = {
"username": observation.username
}
# Observer submitted media
feature["properties"]["image"] = ("/".join([
"/api",
current_app.config["MEDIA_FOLDER"],
observation.image,
]) if observation.image else None)
# Municipality
observation_dict = observation.ObservationModel.as_dict(True)
for k in observation_dict:
if k in obs_keys and k != "municipality":
feature["properties"][k] = observation_dict[k]
# TaxRef
if current_app.config.get("API_TAXHUB") is None:
taxref = Taxref.query.filter(Taxref.cd_nom == observation.
ObservationModel.cd_nom).first()
if taxref:
feature["properties"]["taxref"] = taxref.as_dict(True)
medias = TMedias.query.filter(TMedias.cd_ref == observation.
ObservationModel.cd_nom).all()
if medias:
feature["properties"]["medias"] = [
media.as_dict(True) for media in medias
]
else:
try:
taxon = next(
taxon for taxon in taxon_repository if taxon
and taxon["cd_nom"] == feature["properties"]["cd_nom"])
feature["properties"]["taxref"] = taxon["taxref"]
feature["properties"]["medias"] = taxon["medias"]
except StopIteration:
pass
features.append(feature)
return FeatureCollection(features)
except Exception as e:
# if current_app.config["DEBUG"]:
# import traceback
# import sys
# import pdb
# pdb.set_trace()
# etype, value, tb = sys.exc_info()
# trace = str(traceback.print_exception(etype, value, tb))
# trace = traceback.format_exc()
# return("<pre>" + trace + "</pre>"), 500
raise e
current_app.logger.critical("[get_program_observations] Error: %s",
str(e))
return {"message": str(e)}, 400
def generate_observation_geojson(id_observation):
"""generate observation in geojson format from observation id
:param id_observation: Observation unique id
:type id_observation: int
:return features: Observations as a Feature dict
:rtype features: dict
"""
# Crée le dictionnaire de l'observation
observation = (db.session.query(
ObservationModel,
UserModel.username,
LAreas.area_name,
LAreas.area_code,
).join(UserModel, ObservationModel.id_role == UserModel.id_user,
full=True).join(
LAreas,
LAreas.id_area == ObservationModel.municipality,
isouter=True,
).filter(ObservationModel.id_observation == id_observation)).one()
photos = (db.session.query(MediaModel, ObservationModel).filter(
ObservationModel.id_observation == id_observation).join(
ObservationMediaModel,
ObservationMediaModel.id_data_source ==
ObservationModel.id_observation,
).join(MediaModel,
ObservationMediaModel.id_media == MediaModel.id_media).all())
result_dict = observation.ObservationModel.as_dict(True)
result_dict["observer"] = {"username": observation.username}
result_dict["municipality"] = {
"name": observation.area_name,
"code": observation.area_code,
}
# Populate "geometry"
features = []
feature = get_geojson_feature(observation.ObservationModel.geom)
# Populate "properties"
for k in result_dict:
if k in obs_keys:
feature["properties"][k] = result_dict[k]
feature["properties"]["photos"] = [{
"url":
"/media/{}".format(p.MediaModel.filename),
"date":
p.ObservationModel.as_dict()["date"],
"author":
p.ObservationModel.obs_txt,
} for p in photos]
if current_app.config.get("API_TAXHUB") is None:
current_app.logger.debug("Selecting TaxHub Medias schema.")
# Get official taxref scientific and common names (first one) from cd_nom where cd_nom = cd_ref
# taxref = get_specie_from_cd_nom(feature["properties"]["cd_nom"])
# for k in taxref:
# feature["properties"][k] = taxref[k]
taxref = Taxref.query.filter(
Taxref.cd_nom == observation.ObservationModel.cd_nom).first()
if taxref:
feature["properties"]["taxref"] = taxref.as_dict(True)
medias = TMedias.query.filter(
TMedias.cd_ref == observation.ObservationModel.cd_nom).all()
if medias:
feature["properties"]["medias"] = [
media.as_dict(True) for media in medias
]
else:
taxhub_list_id = (ProgramsModel.query.filter_by(
id_program=observation.ObservationModel.id_program).one().
taxonomy_list)
taxon_repository = mkTaxonRepository(taxhub_list_id)
try:
taxon = next(
taxon for taxon in taxon_repository if taxon
and taxon["cd_nom"] == feature["properties"]["cd_nom"])
feature["properties"]["taxref"] = taxon["taxref"]
feature["properties"]["medias"] = taxon["medias"]
except StopIteration:
pass
features.append(feature)
return features
def get_program_observations(
program_id: int, ) -> Union[FeatureCollection, Tuple[Dict, int]]:
"""Get all observations from a program
GET
---
tags:
- observations
parameters:
- name: id
in: path
type: integer
required: true
example: 1
definitions:
cd_nom:
type: integer
description: cd_nom taxref
geometry:
type: dict
description: Géométrie de la donnée
name:
type: string
geom:
type: geometry
responses:
200:
description: A list of all species lists
"""
try:
observations = (db.session.query(
ObservationModel,
UserModel.username,
UserModel.avatar,
func.array_agg(MediaModel.filename).label("images"),
LAreas.area_name,
LAreas.area_code,
).filter(
ObservationModel.id_program == program_id,
ProgramsModel.is_active,
).join(
LAreas,
LAreas.id_area == ObservationModel.municipality,
isouter=True,
).join(
ProgramsModel,
ProgramsModel.id_program == ObservationModel.id_program,
isouter=True,
).join(
ObservationMediaModel,
ObservationMediaModel.id_data_source ==
ObservationModel.id_observation,
isouter=True,
).join(
MediaModel,
ObservationMediaModel.id_media == MediaModel.id_media,
isouter=True,
).join(
UserModel,
ObservationModel.id_role == UserModel.id_user,
full=True,
).group_by(
ObservationModel.id_observation,
UserModel.username,
UserModel.avatar,
LAreas.area_name,
LAreas.area_code,
))
observations = observations.order_by(
desc(ObservationModel.timestamp_create))
# current_app.logger.debug(str(observations))
observations = observations.all()
if current_app.config.get("API_TAXHUB") is not None:
taxhub_list_id = (ProgramsModel.query.filter_by(
id_program=program_id).one().taxonomy_list)
taxon_repository = mkTaxonRepository(taxhub_list_id)
features = []
for observation in observations:
feature = get_geojson_feature(observation.ObservationModel.geom)
feature["properties"]["municipality"] = {
"name": observation.area_name,
"code": observation.area_code,
}
# Observer
feature["properties"]["observer"] = {
"username": observation.username,
"userAvatar": observation.avatar,
}
# Observer submitted media
feature["properties"]["image"] = ("/".join([
"/api",
current_app.config["MEDIA_FOLDER"],
observation.images[0],
]) if observation.images and observation.images != [None] else
None)
# Municipality
observation_dict = observation.ObservationModel.as_dict(True)
for k in observation_dict:
if k in obs_keys and k != "municipality":
feature["properties"][k] = observation_dict[k]
# TaxRef
# if current_app.config.get("API_TAXHUB") is None:
# taxref = Taxref.query.filter(
# Taxref.cd_nom == observation.ObservationModel.cd_nom
# ).first()
# if taxref:
# feature["properties"]["taxref"] = taxref.as_dict(True)
# medias = TMedias.query.filter(
# TMedias.cd_ref == observation.ObservationModel.cd_nom
# ).all()
# if medias:
# feature["properties"]["medias"] = [
# media.as_dict(True) for media in medias
# ]
# else:
try:
taxon = next(
taxon for taxon in taxon_repository if taxon
and taxon["cd_nom"] == feature["properties"]["cd_nom"])
feature["properties"]["nom_francais"] = taxon["nom_francais"]
feature["properties"]["taxref"] = taxon["taxref"]
feature["properties"]["medias"] = taxon["medias"]
except StopIteration:
pass
features.append(feature)
return FeatureCollection(features)
except Exception as e:
# if current_app.config["DEBUG"]:
# import traceback
# import sys
# import pdb
# pdb.set_trace()
# etype, value, tb = sys.exc_info()
# trace = str(traceback.print_exception(etype, value, tb))
# trace = traceback.format_exc()
# return("<pre>" + trace + "</pre>"), 500
raise e
current_app.logger.critical("[get_program_observations] Error: %s",
str(e))
return {"message": str(e)}, 400
