def administrativedivision(request):
if "q" not in request.params:
raise HTTPBadRequest(detail='parameter "q" is missing')
term = request.params["q"]
filter_lang = None
if request.locale_name != "en":
attribute = getattr(AdDiv, "name_" + request.locale_name)
filter_lang = func.unaccent(attribute).ilike(func.unaccent("%{}%".format(term)))
filter_lang = and_(filter_lang, AdminLevelType.mnemonic == "COU")
filter = func.unaccent(AdDiv.name).ilike(func.unaccent("%{}%".format(term)))
if filter_lang is not None:
filter = or_(filter_lang, filter)
query = (
request.dbsession.query(AdDiv)
.filter(filter)
.join(AdminLevelType)
.order_by(
AdDiv.name.ilike(term).desc(),
AdDiv.name.ilike("{}%".format(term)).desc(),
AdDiv.leveltype_id,
AdDiv.name,
)
.limit(10)
)
data = query.all()
return {"data": data}
def prospects(self, username):
""" Returns the attendees associated with the given users that are
not yet part of the group.
The result is a list of tuples with the first element being the
attendee and the second element being the booking for the linked
occasion, if such a booking already exists (otherwise None).
"""
if not username:
return
existing = {a.id for a, b in self.attendees}
attendees = self.session.query(Attendee)\
.filter(Attendee.username == username)\
.order_by(func.unaccent(Attendee.name))
bookings = self.session.query(Booking)\
.filter(Booking.occasion_id == self.occasion.id)\
.filter(Booking.attendee_id.in_(
attendees.with_entities(Attendee.id).subquery()))
bookings = {b.attendee_id: b for b in bookings}
for attendee in attendees:
if attendee.id not in existing:
yield attendee, bookings.get(attendee.id, None)
def attendees(self):
""" Returns the attendees linked to this invite. """
return tuple(
(booking.attendee, booking)
for booking in self.bookings().outerjoin(Attendee).order_by(
func.unaccent(Attendee.name)))
def used_agencies(self):
""" Returns a list of all the agencies peoples are members of.
"""
query = self.session.query(Agency.title).distinct()
query = query.join(Agency.memberships)
query = query.order_by(func.upper(func.unaccent(Agency.title)))
return [r.title for r in query]
def used_letters(self):
""" Returns a list of all the distinct first letters of the peoples
last names.
"""
letter = func.left(ExtendedPerson.last_name, 1)
letter = func.upper(func.unaccent(letter))
query = self.session.query(letter.distinct().label('letter'))
query = query.order_by(letter)
return [r.letter for r in query]
def on_request(self):
self.request.include('common')
self.request.include('chosen')
self.parent_id.choices = self.request.session.query(
ExtendedAgency.id.cast(String),
ExtendedAgency.title
).order_by(func.unaccent(ExtendedAgency.title)).all()
self.parent_id.choices.insert(
0, ('root', self.request.translate(_("- Root -")))
)
def query(self):
query = self.session.query(ExtendedPerson)
if self.exclude_hidden:
query = query.filter(
or_(
ExtendedPerson.meta['is_hidden_from_public'] == False,
ExtendedPerson.meta['is_hidden_from_public'] == None,
))
if self.letter:
query = query.filter(
func.upper(func.unaccent(ExtendedPerson.last_name)).startswith(
self.letter))
if self.agency:
query = query.join(ExtendedPerson.memberships)
query = query.join(AgencyMembership.agency)
query = query.filter(Agency.title == self.agency)
query = query.order_by(
func.upper(func.unaccent(ExtendedPerson.last_name)),
func.upper(func.unaccent(ExtendedPerson.first_name)))
return query
def _sanitized_string(value: str) -> Function:
sanitized = func.replace(value, "-", "")
sanitized = func.replace(sanitized, " ", "")
sanitized = func.unaccent(sanitized)
sanitized = func.lower(sanitized)
return sanitized
def search(self, qs):
return or_(
icontains(Word.name, qs),
func.unaccent(Word.name).contains(func.unaccent(qs)))
def subset(self):
subset = self.query().filter_by(street=self.street)
return subset.order_by(func.unaccent(WinterthurAddress.street),
WinterthurAddress.house_number,
WinterthurAddress.house_extra)
def search(self, qs):
return or_(
icontains(Word.name, qs),
func.unaccent(Word.name).contains(func.unaccent(qs)))
def available_usernames(self):
return self.usercollection.query()\
.with_entities(User.username, User.realname)\
.filter(func.trim(func.coalesce(User.realname, "")) != "")\
.filter(User.active == True)\
.order_by(func.unaccent(func.lower(User.realname)))
def get_AllTaxrefNameByListe(code_liste=None):
"""
Route utilisée pour les autocompletes
Si le paramètre search_name est passé, la requête SQL utilise l'algorithme
des trigrames pour améliorer la pertinence des résultats
Route utilisé par le mobile pour remonter la liste des taxons
params URL:
- code_liste : code de la liste (si id liste est null ou = à -1 on ne prend pas de liste)
params GET (facultatifs):
- search_name : nom recherché. Recherche basé sur la fonction
ilike de sql avec un remplacement des espaces par %
- regne : filtre sur le regne INPN
- group2_inpn : filtre sur le groupe 2 de l'INPN
- limit: nombre de résultat
- offset: numéro de la page
"""
# Traitement des cas ou code_liste = -1
id_liste = None
try:
if code_liste:
code_liste_to_int = int(code_liste)
if code_liste_to_int == -1:
id_liste = -1
else:
id_liste = -1
except ValueError:
# le code liste n'est pas un entier
# mais une chaine de caractère c-a-d bien un code
pass
# Get id_liste
try:
# S'il y a une id_liste elle à forcement la valeur -1
# c-a-d pas de liste
if not id_liste:
q = (
db.session.query(BibListes.id_liste)
.filter(BibListes.code_liste == code_liste)
).one()
print('LAAAAAA')
id_liste = q[0]
except NoResultFound:
return (
{
"success": False,
"message": "Code liste '{}' inexistant".format(code_liste)
},
500,
)
q = db.session.query(VMTaxrefListForautocomplete)
if id_liste and id_liste != -1:
q = q.join(
BibNoms, BibNoms.cd_nom == VMTaxrefListForautocomplete.cd_nom
).join(CorNomListe,
and_(
CorNomListe.id_nom == BibNoms.id_nom,
CorNomListe.id_liste == id_liste
),
)
search_name = request.args.get("search_name")
if search_name:
q = q.add_columns(
func.similarity(
VMTaxrefListForautocomplete.search_name, search_name
).label("idx_trgm")
)
search_name = search_name.replace(" ", "%")
q = q.filter(
func.unaccent(VMTaxrefListForautocomplete.search_name).ilike(func.unaccent("%" + search_name + "%"))
).order_by(desc("idx_trgm"))
regne = request.args.get("regne")
if regne:
q = q.filter(VMTaxrefListForautocomplete.regne == regne)
group2_inpn = request.args.get("group2_inpn")
if group2_inpn:
q = q.filter(VMTaxrefListForautocomplete.group2_inpn == group2_inpn)
q = q.order_by(
desc(VMTaxrefListForautocomplete.cd_nom == VMTaxrefListForautocomplete.cd_ref)
)
limit = request.args.get("limit", 20, int)
page = request.args.get("page", 1, int)
offset = request.args.get("offset", 0, int)
(limit, offset, page) = calculate_offset_page(limit, offset, page)
data = q.limit(limit).offset(offset).all()
if search_name:
return [d[0].as_dict() for d in data]
else:
return [d.as_dict() for d in data]