def general_stats(info_role): """Return stats about synthese. .. :quickref: Synthese; - nb of observations - nb of distinct species - nb of distinct observer - nb ob datasets """ allowed_datasets = get_datasets_cruved(info_role) q = DB.session.query( func.count(Synthese.id_synthese), func.count(func.distinct(Synthese.cd_nom)), func.count(func.distinct(Synthese.observers)), ) q = synthese_query.filter_query_with_cruved(Synthese, q, info_role) data = q.one() data = { "nb_data": data[0], "nb_species": data[1], "nb_observers": data[2], "nb_dataset": len(allowed_datasets), } return data
def general_stats(info_role): """ Return stats about synthese - nb of observations - nb of distinct species - nb of distinct observer - nb ob datasets """ allowed_datasets = TDatasets.get_user_datasets(info_role) q = DB.session.query( func.count(Synthese.id_dataset), func.count(func.distinct(Synthese.cd_nom)), func.count(func.distinct(Synthese.observers)), ) q = synthese_query.filter_query_with_cruved(Synthese, q, info_role) data = q.one() data = { "nb_data": data[0], "nb_species": data[1], "nb_observers": data[2], "nb_dataset": len(allowed_datasets), } return data
def export_observations_web(info_role): """ Optimized route for observations web export This view is customisable by the administrator Some columns arer mandatory: id_sythese, geojson and geojson_local to generate the exported files Parameters : Via POST: Use a list of id_synthese (in POST parameters) to filter the v_synthese_for_export_view Via GET: 'export_format' str<'csv', 'geojson', 'shapefiles'> """ params = request.args # set default to csv export_format = "csv" export_view = GenericTable( "v_synthese_for_export", "gn_synthese", "the_geom_local", current_app.config["LOCAL_SRID"], ) if "export_format" in params: export_format = params["export_format"] # get list of id synthese from POST id_list = request.get_json() db_cols_for_shape = [] columns_to_serialize = [] # loop over synthese config to get the columns for export for db_col in export_view.db_cols: if db_col.key in current_app.config["SYNTHESE"]["EXPORT_COLUMNS"]: db_cols_for_shape.append(db_col) columns_to_serialize.append(db_col.key) q = DB.session.query(export_view.tableDef).filter( export_view.tableDef.columns.idSynthese.in_(id_list) ) # check R and E CRUVED to know if we filter with cruved cruved = cruved_scope_for_user_in_module(info_role.id_role, module_code="SYNTHESE")[ 0 ] if cruved["R"] > cruved["E"]: # filter on cruved specifying the column # id_dataset, id_synthese, id_digitiser and observer in the v_synthese_for_export_view q = synthese_query.filter_query_with_cruved( export_view.tableDef, q, info_role, id_synthese_column=current_app.config["SYNTHESE"]["EXPORT_ID_SYNTHESE_COL"], id_dataset_column=current_app.config["SYNTHESE"]["EXPORT_ID_DATASET_COL"], observers_column=current_app.config["SYNTHESE"]["EXPORT_OBSERVERS_COL"], id_digitiser_column=current_app.config["SYNTHESE"][ "EXPORT_ID_DIGITISER_COL" ], with_generic_table=True, ) results = q.limit(current_app.config["SYNTHESE"]["NB_MAX_OBS_EXPORT"]) file_name = datetime.datetime.now().strftime("%Y_%m_%d_%Hh%Mm%S") file_name = filemanager.removeDisallowedFilenameChars(file_name) # columns = [db_col.key for db_col in export_view.db_cols] if export_format == "csv": formated_data = [ export_view.as_dict(d, columns=columns_to_serialize) for d in results ] return to_csv_resp( file_name, formated_data, separator=";", columns=columns_to_serialize ) elif export_format == "geojson": features = [] for r in results: geometry = ast.literal_eval( getattr(r, current_app.config["SYNTHESE"]["EXPORT_GEOJSON_4326_COL"]) ) feature = Feature( geometry=geometry, properties=export_view.as_dict(r, columns=columns_to_serialize), ) features.append(feature) results = FeatureCollection(features) return to_json_resp(results, as_file=True, filename=file_name, indent=4) else: try: filemanager.delete_recursively( str(ROOT_DIR / "backend/static/shapefiles"), excluded_files=[".gitkeep"] ) dir_path = str(ROOT_DIR / "backend/static/shapefiles") export_view.as_shape( db_cols=db_cols_for_shape, data=results, geojson_col=current_app.config["SYNTHESE"]["EXPORT_GEOJSON_LOCAL_COL"], dir_path=dir_path, file_name=file_name, ) return send_from_directory(dir_path, file_name + ".zip", as_attachment=True) except GeonatureApiError as e: message = str(e) return render_template( "error.html", error=message, redirect=current_app.config["URL_APPLICATION"] + "/#/synthese", )
def export_observations_web(info_role): """Optimized route for observations web export. .. :quickref: Synthese; This view is customisable by the administrator Some columns are mandatory: id_synthese, geojson and geojson_local to generate the exported files POST parameters: Use a list of id_synthese (in POST parameters) to filter the v_synthese_for_export_view :query str export_format: str<'csv', 'geojson', 'shapefiles'> """ params = request.args # set default to csv export_format = "csv" export_view = GenericTableGeo( tableName="v_synthese_for_export", schemaName="gn_synthese", engine=DB.engine, geometry_field=None, srid=current_app.config["LOCAL_SRID"], ) if "export_format" in params: export_format = params["export_format"] # get list of id synthese from POST id_list = request.get_json() db_cols_for_shape = [] columns_to_serialize = [] # loop over synthese config to get the columns for export for db_col in export_view.db_cols: if db_col.key in current_app.config["SYNTHESE"]["EXPORT_COLUMNS"]: db_cols_for_shape.append(db_col) columns_to_serialize.append(db_col.key) q = DB.session.query(export_view.tableDef).filter( export_view.tableDef.columns[ current_app.config["SYNTHESE"]["EXPORT_ID_SYNTHESE_COL"] ].in_(id_list) ) # check R and E CRUVED to know if we filter with cruved cruved = cruved_scope_for_user_in_module(info_role.id_role, module_code="SYNTHESE")[ 0 ] if cruved["R"] > cruved["E"]: # filter on cruved specifying the column # id_dataset, id_synthese, id_digitiser and observer in the v_synthese_for_export_view q = synthese_query.filter_query_with_cruved( export_view.tableDef, q, info_role, id_synthese_column=current_app.config["SYNTHESE"]["EXPORT_ID_SYNTHESE_COL"], id_dataset_column=current_app.config["SYNTHESE"]["EXPORT_ID_DATASET_COL"], observers_column=current_app.config["SYNTHESE"]["EXPORT_OBSERVERS_COL"], id_digitiser_column=current_app.config["SYNTHESE"][ "EXPORT_ID_DIGITISER_COL" ], with_generic_table=True, ) results = q.limit(current_app.config["SYNTHESE"]["NB_MAX_OBS_EXPORT"]) file_name = datetime.datetime.now().strftime("%Y_%m_%d_%Hh%Mm%S") file_name = filemanager.removeDisallowedFilenameChars(file_name) # columns = [db_col.key for db_col in export_view.db_cols] if export_format == "csv": formated_data = [ export_view.as_dict(d, columns=columns_to_serialize) for d in results ] return to_csv_resp( file_name, formated_data, separator=";", columns=columns_to_serialize ) elif export_format == "geojson": features = [] for r in results: geometry = ast.literal_eval( getattr( r, current_app.config["SYNTHESE"]["EXPORT_GEOJSON_4326_COL"]) ) feature = Feature( geometry=geometry, properties=export_view.as_dict( r, columns=columns_to_serialize), ) features.append(feature) results = FeatureCollection(features) return to_json_resp(results, as_file=True, filename=file_name, indent=4) else: try: filemanager.delete_recursively( str(ROOT_DIR / "backend/static/shapefiles"), excluded_files=[".gitkeep"] ) dir_path = str(ROOT_DIR / "backend/static/shapefiles") export_view.as_shape( db_cols=db_cols_for_shape, data=results, geojson_col=current_app.config["SYNTHESE"]["EXPORT_GEOJSON_LOCAL_COL"], dir_path=dir_path, file_name=file_name, ) return send_from_directory(dir_path, file_name + ".zip", as_attachment=True) except GeonatureApiError as e: message = str(e) return render_template( "error.html", error=message, redirect=current_app.config["URL_APPLICATION"] + "/#/synthese", )
def export_taxon_web(info_role): """Optimized route for taxon web export. .. :quickref: Synthese; This view is customisable by the administrator Some columns are mandatory: cd_ref POST parameters: Use a list of cd_ref (in POST parameters) to filter the v_synthese_taxon_for_export_view :query str export_format: str<'csv'> """ taxon_view = GenericTable( tableName="v_synthese_taxon_for_export_view", schemaName="gn_synthese", engine=DB.engine ) columns = taxon_view.tableDef.columns # Test de conformité de la vue v_synthese_for_export_view try: assert hasattr(taxon_view.tableDef.columns, "cd_ref") except AssertionError as e: return {"msg": """ View v_synthese_taxon_for_export_view must have a cd_ref column \n trace: {} """.format(str(e)) }, 500 id_list = request.get_json() # check R and E CRUVED to know if we filter with cruved cruved = cruved_scope_for_user_in_module( info_role.id_role, module_code="SYNTHESE" )[0] subq = DB.session.query( VSyntheseForWebApp.cd_ref, func.count(distinct( VSyntheseForWebApp.id_synthese )).label("nb_obs"), func.min(VSyntheseForWebApp.date_min).label("date_min"), func.max(VSyntheseForWebApp.date_max).label("date_max") ).filter( VSyntheseForWebApp.id_synthese.in_(id_list) ).group_by(VSyntheseForWebApp.cd_ref) if cruved["R"] > cruved["E"]: # filter on cruved specifying the column # id_dataset, id_synthese, id_digitiser # and observer in the v_synthese_for_export_view subq = synthese_query.filter_query_with_cruved( VSyntheseForWebApp, subq, info_role, id_synthese_column="id_synthese", id_dataset_column="id_dataset", observers_column="observers", id_digitiser_column="id_digitiser", with_generic_table=False, ) subq = subq.subquery() q = DB.session.query( *columns, subq.c.nb_obs, subq.c.date_min, subq.c.date_max ).join( subq, subq.c.cd_ref == columns.cd_ref ) return to_csv_resp( datetime.datetime.now().strftime("%Y_%m_%d_%Hh%Mm%S"), data=serializeQuery(q.all(), q.column_descriptions), separator=";", columns=[db_col.key for db_col in columns] + ["nb_obs", "date_min", "date_max"] )
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