def general_stats(info_role): """Return stats about synthese. .. :quickref: Synthese; - nb of observations - nb of distinct species - nb of distinct observer - nb of datasets """ allowed_datasets = get_datasets_cruved(info_role) q = select( [ func.count(Synthese.id_synthese), func.count(func.distinct(Synthese.cd_nom)), func.count(func.distinct(Synthese.observers)) ] ) synthese_query_obj = SyntheseQuery(Synthese, q, {}) synthese_query_obj.filter_query_with_cruved(info_role) result = DB.session.execute(synthese_query_obj.query) synthese_counts = result.fetchone() data = { "nb_data": synthese_counts[0], "nb_species": synthese_counts[1], "nb_observers": synthese_counts[2], "nb_dataset": len(allowed_datasets), } return data
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', 'gpkg'> """ params = request.args export_format = params.get("export_format", "csv") # Test export_format if not export_format in current_app.config["SYNTHESE"]["EXPORT_FORMAT"]: raise BadRequest("Unsupported format") # set default to csv export_view = GenericTableGeo( tableName="v_synthese_for_export", schemaName="gn_synthese", engine=DB.engine, geometry_field=None, srid=current_app.config["LOCAL_SRID"], ) # 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) query = select([export_view.tableDef]).where( export_view.tableDef.columns[current_app.config["SYNTHESE"]["EXPORT_ID_SYNTHESE_COL"]].in_( id_list ) ) synthese_query_class = SyntheseQuery( export_view.tableDef, query, {}, 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, ) # 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"]: synthese_query_class.filter_query_with_cruved(info_role) results = DB.session.execute(synthese_query_class.query.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) 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: dir_name, file_name = export_as_geo_file( export_format=export_format, export_view=export_view, db_cols=db_cols_for_shape, geojson_col=current_app.config["SYNTHESE"]["EXPORT_GEOJSON_LOCAL_COL"], data=results, file_name=file_name, ) return send_from_directory(dir_name, file_name, 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] sub_query = ( select([ 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") ]) .where(VSyntheseForWebApp.id_synthese.in_(id_list)) .group_by(VSyntheseForWebApp.cd_ref) ) synthese_query_class = SyntheseQuery( VSyntheseForWebApp, sub_query, {}, ) if cruved["R"] > cruved["E"]: # filter on cruved synthese_query_class.filter_query_with_cruved(info_role) subq = synthese_query_class.query.alias("subq") 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"], )