def detailed_ticket_count(account, max_months_ago=None):
""" Returns a thing """
tickets = Session.query(func.date_part('year', Ticket.created_at),
func.date_part('month', Ticket.created_at),
count(Ticket.id))\
.join(TicketOrder, Order).filter(Order.account_id==account.id)\
.group_by(func.date_part('year', Ticket.created_at),
func.date_part('month', Ticket.created_at))
if max_months_ago is not None:
past_date = datetime.datetime.utcnow() - datetime.timedelta(
days=(max_months_ago * 365) / 12)
past_date = datetime.date(year=past_date.year,
month=past_date.month,
day=1) # set to beginning of month
tickets = tickets.filter(Ticket.created_at >= past_date)
statistic_tuples = tickets.all() # [[year, month, amount], ..]
result = dict()
for year, month, amount in statistic_tuples:
year = int(year)
month = int(month)
amount = int(amount)
if year in result:
if not month in result[year]:
result[year][month] = amount
else:
result[year][month] += amount
else:
result[year] = {month: amount}
return result
def get_zprospect():
"""
Retourne toutes les zones de prospection du module
"""
id_type_commune = blueprint.config["id_type_commune"]
parameters = request.args
q = (DB.session.query(TZprospect, Taxref,
func.string_agg(LAreas.area_name, ", ")).outerjoin(
Taxref,
TZprospect.cd_nom == Taxref.cd_nom).outerjoin(
CorZpArea,
CorZpArea.indexzp == TZprospect.indexzp).
outerjoin(CorZpObs, CorZpObs.indexzp == TZprospect.indexzp).outerjoin(
User, User.id_role == CorZpObs.id_role).outerjoin(
BibOrganismes,
BibOrganismes.id_organisme == User.id_organisme).outerjoin(
LAreas,
and_(LAreas.id_area == CorZpArea.id_area,
LAreas.id_type == id_type_commune),
).group_by(TZprospect, Taxref))
if "indexzp" in parameters:
q = q.filter(TZprospect.indexzp == parameters["indexzp"])
if "cd_nom" in parameters:
q = q.filter(Taxref.cd_nom == parameters["cd_nom"])
if "commune" in parameters:
q = q.filter(LAreas.area_name == parameters["commune"])
if "organisme" in parameters:
q = q.filter(BibOrganismes.nom_organisme == parameters["organisme"])
if "year" in parameters:
q = q.filter(
func.date_part("year", TZprospect.date_min) == parameters["year"])
data = q.all()
features = []
for d in data:
feature = d[0].get_geofeature(
recursif=False,
columns=["indexzp", "date_min", "date_max", "cd_nom"])
id_zp = feature["properties"]["indexzp"]
feature["properties"]["taxon"] = d[1].as_dict(["nom_valide"])
features.append(feature)
return FeatureCollection(features)
def check_year_visit(id_base_site, new_visit_date):
"""
Check if there is already a visit of the same year.
If yes, observer is not allowed to post the new visit
"""
q_year = DB.session.query(
func.date_part('year', TBaseVisits.visit_date_min)).filter(
TBaseVisits.id_base_site == id_base_site)
tab_old_year = q_year.all()
print(tab_old_year)
year_new_visit = new_visit_date[0:4]
for y in tab_old_year:
year_old_visit = str(int(y[0]))
if year_old_visit == year_new_visit:
DB.session.rollback()
raise PostYearError(
('Maille {} has already been visited in {} ').format(
id_base_site, year_old_visit), 403)
def export_visit(info_role):
'''
Télécharge les données d'une visite (ou des visites )
'''
parameters = request.args
export_format = parameters[
'export_format'] if 'export_format' in request.args else 'shapefile'
file_name = datetime.datetime.now().strftime('%Y_%m_%d_%Hh%Mm%S')
q = (DB.session.query(ExportVisits))
if 'id_base_visit' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.idbvisit == parameters['id_base_visit']))
elif 'id_releve_plot' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.idreleve == parameters['id_releve_plot']))
elif 'id_base_site' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.idbsite == parameters['id_base_site']))
elif 'organisme' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.organisme == parameters['organisme']))
elif 'year' in parameters:
q = (DB.session.query(ExportVisits).filter(
func.date_part('year', ExportVisits.visitdate) ==
parameters['year']))
elif 'cd_hab' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.cd_hab == parameters['cd_hab']))
data = q.all()
features = []
# formate data
cor_hab_taxon = []
flag_cdhab = 0
strates = []
tab_header = []
column_name = get_base_column_name()
column_name_pro = get_pro_column_name()
mapping_columns = get_mapping_columns()
strates_list = get_stratelist_plot()
tab_visit = []
for d in data:
visit = d.as_dict()
# Get list hab/taxon
cd_hab = visit['cd_hab']
if flag_cdhab != cd_hab:
cor_hab_taxon = get_taxonlist_by_cdhab(cd_hab)
flag_cdhab = cd_hab
# remove geom Type
geom_wkt = to_shape(d.geom)
geom_array = array(geom_wkt)
visit['geom_wkt'] = geom_wkt
if export_format == 'csv' or export_format == 'shapefile':
visit['geom'] = d.geom
if geom_wkt.type.lower() == 'linestring':
visit['geom'] = str(geom_array[0]) + " / " + str(geom_array[1])
# remove html tag
visit['lbhab'] = striphtml(visit['lbhab'])
# Translate label column
visit = dict((mapping_columns[key], value)
for (key, value) in visit.items()
if key in mapping_columns)
# pivot strate
if visit['covstrate']:
for strate, cover in visit['covstrate'].items():
visit[strate] = " % " + str(cover)
if 'covstrate' in visit:
visit.pop('covstrate')
# pivot taxons
if visit['covtaxons']:
for taxon, cover in visit['covtaxons'].items():
visit[taxon] = " % " + str(cover)
if 'covtaxons' in visit:
visit.pop('covtaxons')
tab_visit.append(visit)
if export_format == 'geojson':
for d in tab_visit:
feature = mapping(d['geom_wkt'])
d.pop('geom_wkt', None)
properties = d
features.append(feature)
features.append(properties)
result = FeatureCollection(features)
return to_json_resp(result, as_file=True, filename=file_name, indent=4)
elif export_format == 'csv':
tab_header = column_name + [clean_string(x) for x in strates_list] + [
clean_string(x) for x in cor_hab_taxon
] + column_name_pro
return to_csv_resp(file_name, tab_visit, tab_header, ';')
else:
dir_path = str(ROOT_DIR / 'backend/static/shapefiles')
FionaShapeService.create_shapes_struct(
db_cols=ExportVisits.__mapper__.c,
srid=4326,
dir_path=dir_path,
file_name=file_name,
)
for row in data:
FionaShapeService.create_feature(row.as_dict(), row.geom)
FionaShapeService.save_and_zip_shapefiles()
return send_from_directory(dir_path,
file_name + '.zip',
as_attachment=True)
def get_sites_zp():
'''
Retourne la liste des ZP
'''
parameters = request.args
id_type_commune = blueprint.config['id_type_commune']
# grâce au fichier config
q = (
DB.session.query(
TInfoSite, func.max(TBaseVisits.visit_date_min),
Taxonomie.nom_complet,
func.count(distinct(TBaseVisits.id_base_visit)),
func.string_agg(distinct(BibOrganismes.nom_organisme), ', '),
func.string_agg(LAreas.area_name, ', ')).outerjoin(
TBaseVisits, TBaseVisits.id_base_site == TInfoSite.id_base_site
# get taxonomy lb_nom
).outerjoin(
Taxonomie, TInfoSite.cd_nom == Taxonomie.cd_nom
# get organisms of a site
).outerjoin(
corVisitObserver, corVisitObserver.c.id_base_visit ==
TBaseVisits.id_base_visit).outerjoin(
User,
User.id_role == corVisitObserver.c.id_role).outerjoin(
BibOrganismes,
BibOrganismes.id_organisme == User.id_organisme)
# get municipalities of a site
.outerjoin(
corSiteArea,
corSiteArea.c.id_base_site == TInfoSite.id_base_site).outerjoin(
LAreas,
and_(LAreas.id_area == corSiteArea.c.id_area,
LAreas.id_type == id_type_commune)).group_by(
TInfoSite, Taxonomie.nom_complet))
if 'id_base_site' in parameters:
q = q.filter(TInfoSite.id_base_site == parameters['id_base_site'])
if 'cd_nom' in parameters:
q = q.filter(TInfoSite.cd_nom == parameters['cd_nom'])
if 'organisme' in parameters:
q = q.filter(BibOrganismes.nom_organisme == parameters['organisme'])
if 'commune' in parameters:
q = q.filter(LAreas.area_name == parameters['commune'])
if 'year' in parameters:
# relance la requête pour récupérer la date_max exacte si on filtre sur l'année
q_year = (DB.session.query(
TInfoSite.id_base_site,
func.max(TBaseVisits.visit_date_min),
).outerjoin(
TBaseVisits,
TBaseVisits.id_base_site == TInfoSite.id_base_site).group_by(
TInfoSite.id_base_site))
data_year = q_year.all()
q = q.filter(
func.date_part('year', TBaseVisits.visit_date_min) ==
parameters['year'])
data = q.all()
features = []
for d in data:
feature = d[0].get_geofeature()
id_site = feature['properties']['base_site']['id_base_site']
if 'year' in parameters:
for dy in data_year:
# récupérer la bonne date max du site si on filtre sur année
if id_site == dy[0]:
feature['properties']['date_max'] = str(dy[1])
else:
feature['properties']['date_max'] = str(d[1])
if d[1] == None:
feature['properties']['date_max'] = 'Aucune visite'
feature['properties']['nom_taxon'] = str(d[2])
feature['properties']['nb_visit'] = str(d[3])
feature['properties']['organisme'] = str(d[4])
feature['properties']['nom_commune'] = str(d[5])
if d[4] == None:
feature['properties']['organisme'] = 'Aucun'
features.append(feature)
return FeatureCollection(features)
def export_visit():
'''
Télécharge les données d'une visite (ou des visites )
'''
parameters = request.args
# q = q.filter(TInfoSite.id_base_site == parameters['id_base_site'])
export_format = parameters[
'export_format'] if 'export_format' in request.args else 'shapefile'
file_name = datetime.datetime.now().strftime('%Y_%m_%d_%Hh%Mm%S')
q = (DB.session.query(ExportVisits))
if 'id_base_visit' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.id_base_visit == parameters['id_base_visit']))
elif 'id_base_site' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.id_base_site == parameters['id_base_site']))
elif 'organisme' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.organisme == parameters['organisme']))
elif 'commune' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.area_name == parameters['commune']))
elif 'year' in parameters:
q = (DB.session.query(ExportVisits).filter(
func.date_part('year', ExportVisits.visit_date) ==
parameters['year']))
elif 'cd_nom' in parameters:
q = (DB.session.query(ExportVisits).filter(
ExportVisits.cd_nom == parameters['cd_nom']))
data = q.all()
features = []
if export_format == 'geojson':
for d in data:
feature = d.as_geofeature('geom', 'id_area', False)
features.append(feature)
result = FeatureCollection(features)
return to_json_resp(result, as_file=True, filename=file_name, indent=4)
elif export_format == 'csv':
tab_visit = []
for d in data:
visit = d.as_dict()
geom_wkt = to_shape(d.geom)
visit['geom'] = geom_wkt
tab_visit.append(visit)
return to_csv_resp(file_name, tab_visit, tab_visit[0].keys(), ';')
else:
dir_path = str(ROOT_DIR / 'backend/static/shapefiles')
FionaShapeService.create_shapes_struct(
db_cols=ExportVisits.__mapper__.c,
srid=2154,
dir_path=dir_path,
file_name=file_name,
)
for row in data:
FionaShapeService.create_feature(row.as_dict(), row.geom)
FionaShapeService.save_and_zip_shapefiles()
return send_from_directory(dir_path,
file_name + '.zip',
as_attachment=True)
def makeParam(key, value):
""" Takes a value list as input and concatenates with OR.
This means that {age: [1, 12, 13]} will yield a result if
age == 1 OR age == 12 OR age == 13.
"""
if not (isinstance(value, list) or isinstance(value, tuple)):
value = [value]
or_clause = []
# Ask for the type of the parameter according to the entity
parameter_class = parameter_for_type(entity.declared_params[key])
for val in value:
if callable(val):
# we’ve been given a function
or_clause.append(val(parameter_class.value))
elif parameter_class == StringParameter:
# test string using ‘like’
if not options["strict"]:
val = "%" + val + "%"
or_clause.append(parameter_class.value.like(val))
else:
if options["convert_string"]:
try:
val = parameter_class.from_string(val)
except StringConversionError:
if parameter_class == DateParameter:
# Here, we want to match a certain YEAR, a certain
# combination of YEAR-MONTH or a certain combination
# YEAR-MONTH-DAY from a date.
# Therefore, we need to extract YEAR, MONTH and DAY
# from a date and match those separately.
# Unfortunately, there is no common SQL function for
# this task, so we're left with ``date_part('year', date)``
# for Postgres and ``strftime('%Y', date)`` for Sqlite.
# We check the `engine_name` and generate the respective
# methods.
# get year month day
ymd = val.split('-')
clauses = []
from sqlalchemy.sql.expression import func
if self.connection.engine_name == "postgresql":
year_part = lambda value: func.date_part('year', value)
month_part = lambda value: func.date_part('month', value)
day_part = lambda value: func.date_part('day', value)
elif self.connection.engine_name == "sqlite":
year_part = lambda value: func.strftime('%Y', value)
month_part = lambda value: func.strftime('%m', value)
day_part = lambda value: func.strftime('%d', value)
else:
raise ValueError("Unsupported operation: Unknown engine name %r." %
self.connection.engine_name)
if len(ymd) > 0:
clauses.append(year_part(parameter_class.value) == ymd[0])
if len(ymd) > 1:
clauses.append(month_part(parameter_class.value) == ymd[1])
if len(ymd) > 2:
clauses.append(day_part(parameter_class.value) == ymd[2])
clause = (and_(*clauses))
or_clause.append(clause)
else:
raise
else:
or_clause.append(parameter_class.value == val)
# FIXME
return entity._params.of_type(parameter_class).any(or_(*or_clause))
def export_ap():
"""
Télécharge les données d'une aire de présence
"""
parameters = request.args
export_format = (parameters["export_format"]
if "export_format" in request.args else "shapefile")
file_name = datetime.datetime.now().strftime("%Y_%m_%d_%Hh%Mm%S")
q = DB.session.query(ExportAp)
if "indexap" in parameters:
q = DB.session.query(ExportAp).filter(
ExportAp.indexap == parameters["indexap"])
elif "indexzp" in parameters:
q = DB.session.query(ExportAp).filter(
ExportAp.id_base_site == parameters["indexzp"])
elif "organisme" in parameters:
q = DB.session.query(ExportAp).filter(
ExportAp.organisme == parameters["organisme"])
elif "commune" in parameters:
q = DB.session.query(ExportAp).filter(
ExportAp.area_name == parameters["commune"])
elif "year" in parameters:
q = DB.session.query(ExportAp).filter(
func.date_part("year", ExportAp.visit_date) == parameters["year"])
elif "cd_nom" in parameters:
q = DB.session.query(ExportAp).filter(
ExportAp.cd_nom == parameters["cd_nom"])
data = q.all()
features = []
if export_format == "geojson":
for d in data:
feature = d.as_geofeature("geom_local", "indexap", False)
features.append(feature)
result = FeatureCollection(features)
return to_json_resp(result, as_file=True, filename=file_name, indent=4)
elif export_format == "csv":
tab_ap = []
for d in data:
ap = d.as_dict()
geom_wkt = to_shape(d.geom_local)
ap["geom_local"] = geom_wkt
tab_ap.append(ap)
return to_csv_resp(file_name, tab_ap, tab_ap[0].keys(), ";")
else:
dir_path = str(ROOT_DIR / "backend/static/shapefiles")
FionaShapeService.create_shapes_struct(
db_cols=ExportAp.__mapper__.c,
srid=2154,
dir_path=dir_path,
file_name=file_name,
)
for row in data:
FionaShapeService.create_feature(row.as_dict(), row.geom_local)
FionaShapeService.save_and_zip_shapefiles()
return send_from_directory(dir_path,
file_name + ".zip",
as_attachment=True)
def get_all_sites(info_role):
'''
Retourne tous les sites
'''
parameters = request.args
id_type_commune = blueprint.config['id_type_commune']
q = (
DB.session.query(
TInfosSite,
func.max(TBaseVisits.visit_date_min),
Habref.lb_hab_fr_complet,
func.count(distinct(TBaseVisits.id_base_visit)),
func.string_agg(distinct(BibOrganismes.nom_organisme), ', '),
func.string_agg(distinct(LAreas.area_name), ', ')
).outerjoin(
TBaseVisits, TBaseVisits.id_base_site == TInfosSite.id_base_site
# get habitat cd_hab
).outerjoin(
Habref, TInfosSite.cd_hab == Habref.cd_hab
# get organisms of a site
).outerjoin(
corVisitObserver, corVisitObserver.c.id_base_visit == TBaseVisits.id_base_visit
).outerjoin(
User, User.id_role == corVisitObserver.c.id_role
).outerjoin(
BibOrganismes, BibOrganismes.id_organisme == User.id_organisme
)
# get municipalities of a site
.outerjoin(
corSiteArea, corSiteArea.c.id_base_site == TInfosSite.id_base_site
).outerjoin(
LAreas, and_(LAreas.id_area == corSiteArea.c.id_area, LAreas.id_type == id_type_commune)
)
.group_by(
TInfosSite, Habref.lb_hab_fr_complet
)
)
if 'cd_hab' in parameters:
q = q.filter(TInfosSite.cd_hab == parameters['cd_hab'])
if 'id_base_site' in parameters:
q = q.filter(TInfosSite.id_base_site == parameters['id_base_site'])
if 'organisme' in parameters:
q = q.filter(BibOrganismes.id_organisme == parameters['organisme'])
if 'commune' in parameters:
q = q.filter(LAreas.area_name == parameters['commune'])
if 'year' in parameters:
# relance la requête pour récupérer la date_max exacte si on filtre sur l'année
q_year = (
DB.session.query(
TInfosSite.id_base_site,
func.max(TBaseVisits.visit_date_min),
).outerjoin(
TBaseVisits, TBaseVisits.id_base_site == TInfosSite.id_base_site
).group_by(TInfosSite.id_base_site)
)
data_year = q_year.all()
q = q.filter(func.date_part('year', TBaseVisits.visit_date_min) == parameters['year'])
page = request.args.get('page', 1, type=int)
items_per_page = blueprint.config['items_per_page']
pagination_serverside = blueprint.config['pagination_serverside']
if (pagination_serverside):
pagination = q.paginate(page, items_per_page, False)
data = pagination.items
totalItmes = pagination.total
else:
totalItmes = 0
data = q.all()
pageInfo= {
'totalItmes' : totalItmes,
'items_per_page' : items_per_page,
}
features = []
if data:
for d in data:
feature = d[0].get_geofeature()
id_site = feature['properties']['id_base_site']
base_site_code = feature['properties']['t_base_site']['base_site_code']
base_site_description = feature['properties']['t_base_site']['base_site_description'] or 'Aucune description'
base_site_name = feature['properties']['t_base_site']['base_site_name']
if feature['properties']['t_base_site']:
del feature['properties']['t_base_site']
if 'year' in parameters:
for dy in data_year:
# récupérer la bonne date max du site si on filtre sur année
if id_site == dy[0]:
feature['properties']['date_max'] = str(d[1])
else:
feature['properties']['date_max'] = str(d[1])
if d[1] == None:
feature['properties']['date_max'] = 'Aucune visite'
feature['properties']['nom_habitat'] = str(d[2])
feature['properties']['nb_visit'] = str(d[3])
feature['properties']['organisme'] = str(d[4])
feature['properties']['nom_commune'] = str(d[5])
if d[4] == None:
feature['properties']['organisme'] = 'Aucun'
feature['properties']['base_site_code'] = base_site_code
feature['properties']['base_site_description'] = base_site_description
feature['properties']['base_site_name'] = base_site_name
features.append(feature)
return [pageInfo,FeatureCollection(features)]
return None