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)