def get_taxa_simple_list(id_area): """ :param type: :return: """ try: query_area = ( DB.session.query( Taxref.cd_ref.label("id"), Taxref.cd_ref, func.split_part(Taxref.nom_vern, ",", 1).label("nom_vern"), Taxref.lb_nom, Taxref.group2_inpn, func.coalesce(TMaxThreatenedStatus.threatened, False).label( "threatened" ), func.count(distinct(Synthese.id_synthese)).label("count_occtax"), func.count(distinct(Synthese.observers)).label("count_observer"), func.count(distinct(Synthese.date_min)).label("count_date"), func.count(distinct(Synthese.id_dataset)).label("count_dataset"), func.max(distinct(func.extract("year", Synthese.date_min))).label( "last_year" ), ) .select_from(Taxref) .outerjoin( TMaxThreatenedStatus, TMaxThreatenedStatus.cd_nom == Taxref.cd_nom ) .filter(Synthese.id_synthese == CorAreaSynthese.id_synthese) .filter(Synthese.cd_nom == Taxref.cd_nom) .filter(CorAreaSynthese.id_area == id_area) .distinct() .group_by( func.coalesce(TMaxThreatenedStatus.threatened, False), Taxref.cd_ref, Taxref.nom_vern, Taxref.lb_nom, Taxref.group1_inpn, Taxref.group2_inpn, ) .order_by( func.coalesce(TMaxThreatenedStatus.threatened, False).desc(), func.count(distinct(Synthese.id_synthese)).desc(), Taxref.group1_inpn, Taxref.group2_inpn, ) ) print("query_territory", query_area) result = query_area.all() data = [] for r in result: data.append(r._asdict()) return jsonify({"count": len(result), "data": data}), 200 except Exception as e: error = "<get_taxa_list> ERROR: {}".format(e) current_app.logger.error(error) return {"Error": error}, 400
def handle_custom_risks_order(context, event): session = Session() custom_risks = session.query(SurveyTreeItem).filter( and_( SurveyTreeItem.session_id == context.session_id, SurveyTreeItem.path.like(context.path + "%"), SurveyTreeItem.type == "risk", )) # First, set all paths and zodb_paths to bogus values (extra zeros) # to avoid constraint errors for risk in custom_risks: risk.path = risk.path + "000" risk.zodb_path = risk.zodb_path + "000" ordered_custom_risks = (session.query(SurveyTreeItem).filter( and_( SurveyTreeItem.session_id == context.session_id, SurveyTreeItem.path.like(context.path + "%"), SurveyTreeItem.type == "risk", )).order_by( cast(func.split_part(SurveyTreeItem.zodb_path, "/", 2), Integer))) # Iterate over the risks in their natural order. Close any gaps in numbering for count, risk in enumerate(ordered_custom_risks): risk.zodb_path = "custom-risks/{}".format(count + 1) # Now, set the path according to the zodb_path (= natural order) for risk in custom_risks: risk.path = "%s%03d" % (context.path, int( risk.zodb_path.split("/")[-1]))
def get_taxa_list(id_area): """ :param type: :return: """ try: reproduction_id = ( ( DB.session.query(TNomenclatures.id_nomenclature) .join( BibNomenclaturesTypes, TNomenclatures.id_type == BibNomenclaturesTypes.id_type, ) .filter( and_( BibNomenclaturesTypes.mnemonique.like("STATUT_BIO"), TNomenclatures.cd_nomenclature.like("3"), ) ) ) .first() .id_nomenclature ) print("reproduction_id", reproduction_id) query_territory = ( DB.session.query( Taxref.cd_ref.label("id"), LAreas.id_area, LAreas.area_code, Taxref.cd_ref, func.split_part(Taxref.nom_vern, ",", 1).label("nom_vern"), Taxref.nom_valide, Taxref.group1_inpn, Taxref.group2_inpn, func.count(distinct(Synthese.id_synthese)).label("count_occtax"), func.count(distinct(Synthese.observers)).label("count_observer"), func.count(distinct(Synthese.date_min)).label("count_date"), func.count(distinct(Synthese.id_dataset)).label("count_dataset"), func.max(distinct(func.extract("year", Synthese.date_min))).label( "last_year" ), func.array_agg( aggregate_order_by( distinct(func.extract("year", Synthese.date_min)), func.extract("year", Synthese.date_min).desc(), ) ).label("list_years"), func.array_agg( aggregate_order_by( distinct(func.extract("month", Synthese.date_min)), func.extract("month", Synthese.date_min).asc(), ) ).label("list_months"), func.bool_or( Synthese.id_nomenclature_bio_status == reproduction_id ).label("reproduction"), func.max(distinct(func.extract("year", Synthese.date_min))) .filter(Synthese.id_nomenclature_bio_status == reproduction_id) .label("last_year_reproduction"), func.array_agg(distinct(Synthese.id_nomenclature_bio_status)).label( "bio_status_id" ), case( [(func.count(TaxrefProtectionEspeces.cd_nom) > 0, True)], else_=False, ).label("protection"), ) .select_from(CorAreaSynthese) .join(Synthese, Synthese.id_synthese == CorAreaSynthese.id_synthese) .join(Taxref, Synthese.cd_nom == Taxref.cd_nom) .join(LAreas, LAreas.id_area == CorAreaSynthese.id_area) .outerjoin(TaxrefLR, TaxrefLR.cd_nom == Taxref.cd_ref) .outerjoin( TaxrefProtectionEspeces, TaxrefProtectionEspeces.cd_nom == Taxref.cd_nom ) .filter(LAreas.id_area == id_area) .group_by( LAreas.id_area, LAreas.area_code, Taxref.cd_ref, Taxref.nom_vern, Taxref.nom_valide, Taxref.group1_inpn, Taxref.group2_inpn, ) .order_by( func.count(distinct(Synthese.id_synthese)).desc(), Taxref.group1_inpn, Taxref.group2_inpn, Taxref.nom_valide, ) ) print("query_territory", query_territory) result = query_territory.all() count = len(result) data = [] for r in result: dict = r._asdict() bio_status = [] for s in r.bio_status_id: bio_status.append(get_nomenclature(s)) dict["bio_status"] = bio_status redlist = get_redlist_status(r.cd_ref) dict["redlist"] = redlist data.append(dict) redlistless_data = list(filter(redlist_list_is_null, data)) print("redlistless_data", len(redlistless_data)) redlist_data = list(filter(redlist_is_not_null, data)) print("redlist_data", len(redlist_data)) redlist_sorted_data = sorted( redlist_data, key=lambda k: ( k["redlist"][0]["priority_order"], k["redlist"][0]["threatened"], ), ) sorted_data = redlist_sorted_data + list(redlistless_data) return jsonify({"count": count, "data": sorted_data}), 200 except Exception as e: error = "<get_taxa_list> ERROR: {}".format(e) current_app.logger.error(error) return {"Error": error}, 400
def subtype(cls): return func.split_part(cls.action, ':', 2)