def movimientos_by_guia_by_departamento(self, provider, departamento, razon_social):
""" devuelve detalle de movimientos de guias """
consulta = self.filter(
anulate=False,
detail_asignation__asignation__detail_guide__anulate=False,
detail_asignation__asignation__detail_guide__guide__pagado=False,
detail_asignation__asignation__detail_guide__guide__addressee=razon_social,
detail_asignation__asignation__detail_guide__guide__departamento__pk=departamento,
detail_asignation__asignation__detail_guide__guide__provider__pk=provider,
).values(
'detail_asignation__asignation__detail_guide',
'detail_asignation__asignation__detail_guide__guide__date_emission',
'detail_asignation__asignation__detail_guide__precio_guia',
'detail_asignation__asignation__detail_guide__count'
).annotate(
guia=Upper('detail_asignation__asignation__detail_guide__guide__number'),
proveedor=Upper('detail_asignation__asignation__detail_guide__guide__provider__name'),
departamento=Upper('detail_asignation__asignation__detail_guide__guide__departamento__name'),
cantidad = Max('detail_asignation__asignation__detail_guide__count'),
precio = Max('detail_asignation__asignation__detail_guide__precio_guia'),
devuelto=Sum('count_return'),
pagado=Sum('count_payment'),
magazine=Upper('detail_asignation__asignation__detail_guide__magazine_day__magazine__name'),
dia=Upper('detail_asignation__asignation__detail_guide__magazine_day__day'),
).order_by('detail_asignation__asignation__detail_guide__guide__date_emission','guia')
return consulta
def _get_special_ordering(cls, sort_name):
model = cls._meta.model
col = sort_name
# Check for a negative
negative = False
if col.startswith("-"):
negative = True
col = col[1:]
# Get the field
try:
# Iterate over relations to get the field type
sub_col = col
while "__" in sub_col:
sub_name, sub_col = sub_col.split("__", 1)
sub = model._meta.get_field(sub_name)
model = sub.target_field.model
field = model._meta.get_field(sub_col)
except:
return sort_name
# Check if some sort of text field (Do not do this for number field)
if isinstance(
field,
(models.CharField, models.TextField, models.EmailField,
models.FileField, models.FilePathField, models.SlugField,
models.URLField, models.UUIDField, models.GenericIPAddressField)):
if negative:
return Upper(col).desc()
return Upper(col)
return sort_name
def clean_duplicates(apps, schema_editor):
InternshipMaster = apps.get_model('internship', 'InternshipMaster')
MasterAllocation = apps.get_model('internship', 'MasterAllocation')
# get master duplicates concatenating full name + email + email_private
master_duplicates = InternshipMaster.objects.values(
'first_name', 'last_name', 'email', 'email_private').annotate(
fullname=Concat(Upper('first_name'),
Value(' '),
Upper('last_name'),
Value(' - '),
'email',
'email_private',
output_field=CharField())).annotate(
count_fullname=Count('fullname')).order_by(
'count_fullname').filter(
count_fullname__gt=1)
# retrieve all master's allocations into one instance of master and delete the others
for duplicate in master_duplicates:
duplicate_ids = InternshipMaster.objects.filter(
first_name=duplicate['first_name'],
last_name=duplicate['last_name'],
email=duplicate['email'],
email_private=duplicate['email_private']).values_list('id',
flat=True)
last_id = duplicate_ids.last()
MasterAllocation.objects.filter(master_id__in=duplicate_ids).update(
master_id=last_id)
to_delete_ids = duplicate_ids.exclude(id=last_id)
InternshipMaster.objects.filter(id__in=to_delete_ids).delete()
def magazine_boleta2(self, date, date2):
return self.filter(
anulate=False,
created__date__range=(date, date2),
).values(
'detail_asignation__asignation__detail_guide',
'detail_asignation__asignation__detail_guide__guide__date',
'detail_asignation__asignation__detail_guide__precio_unitario',
'detail_asignation__asignation__detail_guide__magazine_day__magazine__afecto',
).annotate(
count=Sum('count_payment'),
amount=Sum('amount'),
guide=Upper(
'detail_asignation__asignation__detail_guide__guide__number'),
vendor=Max('detail_asignation__vendor__cod'),
vendor_type=Max('detail_asignation__vendor__type_vendor'),
magazine=Upper(
'detail_asignation__asignation__detail_guide__magazine_day__magazine__name'
),
dia=Upper(
'detail_asignation__asignation__detail_guide__magazine_day__day'
),
addressee=Upper(
'detail_asignation__asignation__detail_guide__guide__addressee'
)).order_by('magazine')
return consulta
def mov_caja_day(self, date, cod):
#funcion que devuelve movimienntos de cja
return self.filter(
anulate=False,
date=date,
detail_asignation__vendor__cod=cod,
).annotate(
codigo=Upper('detail_asignation__vendor__cod'),
nombre=Upper('detail_asignation__vendor__name'),
apodo=Upper('detail_asignation__vendor__seudonimo'),
magazine=Upper(
'detail_asignation__asignation__detail_guide__magazine_day__magazine__name'
),
dia=Upper(
'detail_asignation__asignation__detail_guide__magazine_day__day'
),
entregado=F('detail_asignation__count'),
precio=F('detail_asignation__precio_venta'),
debe=(F('detail_asignation__count') -
(F('count_return') + F('count_payment'))),
#monto_deuda=ExpressionWrapper((F('detail_asignation__count')-(F('count_return')+F('count_payment')))*F('detail_asignation__precio_venta')), FloatField())
).values(
'created',
'count_return',
'count_payment',
'amount',
'precio',
'entregado',
'codigo',
'nombre',
'apodo',
'debe',
'magazine',
'dia',
)
def cuadre_caja_guia(self, date):
consulta = self.filter(
anulate=False,
created__date=date,
).filter(Q(count_return__gt=0) | Q(count_payment__gt=0)).values(
'detail_asignation__asignation__detail_guide',
'detail_asignation__asignation__detail_guide__guide__date',
'detail_asignation__asignation__detail_guide__precio_unitario'
).annotate(
guia=Upper(
'detail_asignation__asignation__detail_guide__guide__number'),
proveedor=Upper(
'detail_asignation__asignation__detail_guide__guide__provider__name'
),
devuelto=Sum('count_return'),
pagado=Sum('count_payment'),
total=Sum('amount'),
magazine=Upper(
'detail_asignation__asignation__detail_guide__magazine_day__magazine__name'
),
dia=Upper(
'detail_asignation__asignation__detail_guide__magazine_day__day'
)).order_by('magazine').order_by('guia')
return consulta
def recommend_identifiers(identifier: str, input_type: str):
if input_type == "gene":
identifier_upper = identifier.upper()
annotated_genes = Gene.objects.annotate(similarity=TrigramSimilarity(
Upper("gene_symbol"), identifier_upper))
similar_genes = annotated_genes.filter(
similarity__gt=0.3).order_by("-similarity")[0:5]
similar_ids = set(similar_genes.values_list("gene_symbol", flat=True))
elif input_type == "protein":
identifier_upper = identifier.upper()
annotated_proteins = Protein.objects.annotate(
similarity=TrigramSimilarity(Upper("protein_id"),
identifier_upper))
similar_proteins = annotated_proteins.filter(
similarity__gt=0.3).order_by("-similarity")[0:5]
similar_ids = set(similar_proteins.values_list("protein_id",
flat=True))
elif input_type == "modality":
identifier_upper = identifier.upper()
annotated_modalities = Modality.objects.annotate(
similarity=TrigramSimilarity(Upper("modality_name"),
identifier_upper))
similar_modalities = annotated_modalities.filter(
similarity__gt=0.3).order_by("-similarity")[0:5]
similar_ids = set(
similar_modalities.values_list("modality_name", flat=True))
return similar_ids
def test_order_by_nulls_last(self):
self.skipTest(
"TODO fix django.db.utils.ProgrammingError: Incorrect syntax near 'NULLS'."
)
Article.objects.filter(headline="Article 3").update(
author=self.author_1)
Article.objects.filter(headline="Article 4").update(
author=self.author_2)
# asc and desc are chainable with nulls_last.
self.assertSequenceEqual(
Article.objects.order_by(F("author").desc(nulls_last=True)),
[self.a4, self.a3, self.a1, self.a2],
)
self.assertSequenceEqual(
Article.objects.order_by(F("author").asc(nulls_last=True)),
[self.a3, self.a4, self.a1, self.a2],
)
self.assertSequenceEqual(
Article.objects.order_by(
Upper("author__name").desc(nulls_last=True)),
[self.a4, self.a3, self.a1, self.a2],
)
self.assertSequenceEqual(
Article.objects.order_by(
Upper("author__name").asc(nulls_last=True)),
[self.a3, self.a4, self.a1, self.a2],
)
def test_order_by_nulls_last(self):
Article.objects.filter(headline="Article 3").update(
author=self.author_1)
Article.objects.filter(headline="Article 4").update(
author=self.author_2)
# asc and desc are chainable with nulls_last.
self.assertQuerysetEqualReversible(
Article.objects.order_by(
F("author").desc(nulls_last=True), 'headline'),
[self.a4, self.a3, self.a1, self.a2],
)
self.assertQuerysetEqualReversible(
Article.objects.order_by(
F("author").asc(nulls_last=True), 'headline'),
[self.a3, self.a4, self.a1, self.a2],
)
self.assertQuerysetEqualReversible(
Article.objects.order_by(
Upper("author__name").desc(nulls_last=True), 'headline'),
[self.a4, self.a3, self.a1, self.a2],
)
self.assertQuerysetEqualReversible(
Article.objects.order_by(
Upper("author__name").asc(nulls_last=True), 'headline'),
[self.a3, self.a4, self.a1, self.a2],
)
class Meta:
db_table = "{}_main_reference".format(apps.app_name)
ordering = [
Upper('authors_list'), 'year',
Upper('title'),
Upper('reference_type__name'), 'id'
]
class ReporteTotalOrdenes(FormView):
FILTROS = {
'rubro': (Rubro, F("nombre"), lambda r: r.nombre),
'tipo_servicio': (TipoServicio, F("nombre"), lambda ts: ts.nombre),
'cliente': (Cliente,
Concat(Upper(F("persona__apellido")), Value(', '),
F("persona__nombre")), lambda c: "{}, {}".format(
c.persona.apellido.upper(), c.persona.nombre)),
'tecnico': (Tecnico,
Concat(Upper(F("persona__apellido")), Value(', '),
F("persona__nombre")), lambda t: "{}, {}".format(
t.persona.apellido.upper(), t.persona.nombre)),
}
template_name = "reportes/reporte_totales.html"
form_class = ReporteTotalOrdenesForm
def get(self, request, *args, **kwargs):
if (request.is_ajax()):
return self.ajax_get(request, *args, **kwargs)
return super().get(request, *args, **kwargs)
def ajax_get(self, request, *args, **kwargs):
form = self.form_class(request.GET or None)
if form.is_valid():
fecha_ini = form.cleaned_data.get("fecha_ini")
fecha_fin = form.cleaned_data.get("fecha_fin")
Klass = self.FILTROS[form.cleaned_data.get("filtros")][0]
criterio = self.FILTROS[form.cleaned_data.get("filtros")][1]
referencia = self.FILTROS[form.cleaned_data.get("filtros")][2]
query = Klass.objects.values(criterio=criterio).filter(
ordenes__fecha_fin__range=[fecha_ini, fecha_fin]).annotate(
total=Case(When(ordenes__precio_final__isnull=False,
then=F("ordenes__precio_final")),
default=Value(0))).order_by("criterio")
cache = {}
for q in query:
if q["criterio"] not in cache.keys():
cache[q["criterio"]] = q["total"]
continue
cache[q["criterio"]] += q["total"]
for k in Klass.objects.all():
if referencia(k) not in cache.keys():
cache[referencia(k)] = Decimal(0)
return JsonResponse({
"ordenes_total":
sorted(list(cache.items()), key=lambda ot: ot[0]),
})
return JsonResponse({})
def omnibox_mixed_search(org, search, types):
"""
Performs a mixed group, contact and URN search, returning the first N matches of each type.
"""
search_terms = search.split(" ") if search else None
search_types = types or (SEARCH_ALL_GROUPS, SEARCH_CONTACTS, SEARCH_URNS)
per_type_limit = 25
results = []
if SEARCH_ALL_GROUPS in search_types or SEARCH_STATIC_GROUPS in search_types:
groups = ContactGroup.get_user_groups(org)
# exclude dynamic groups if not searching all groups
if SEARCH_ALL_GROUPS not in search_types:
groups = groups.filter(query=None)
if search:
groups = term_search(groups, ("name__icontains", ), search_terms)
results += list(groups.order_by(Upper("name"))[:per_type_limit])
if SEARCH_CONTACTS in search_types:
contacts = Contact.objects.filter(org=org,
is_active=True,
is_blocked=False,
is_stopped=False,
is_test=False)
try:
search_id = int(search)
except (ValueError, TypeError):
search_id = None
if org.is_anon and search_id is not None:
contacts = contacts.filter(id=search_id)
elif search:
contacts = term_search(contacts, ("name__icontains", ),
search_terms)
results += list(contacts.order_by(Upper("name"))[:per_type_limit])
if SEARCH_URNS in search_types:
# only include URNs that are send-able
from temba.channels.models import Channel
allowed_schemes = org.get_schemes(
Channel.ROLE_SEND) if not org.is_anon else []
urns = ContactURN.objects.filter(
org=org, scheme__in=allowed_schemes).exclude(contact=None)
if search:
urns = term_search(urns, ("path__icontains", ), search_terms)
results += list(
urns.prefetch_related("contact").order_by(
Upper("path"))[:per_type_limit])
return results # sorted(results, key=lambda o: o.name if hasattr(o, 'name') else o.path)
def filter_sort(self, filter_details):
order_by = Upper("email").asc()
if "sort" in filter_details:
if str(filter_details["order"]) == "asc":
order_by = Upper("email").asc()
else:
order_by = Upper("email").desc()
return order_by
def omnibox_mixed_search(org, query, types):
"""
Performs a mixed group, contact and URN search, returning the first N matches of each type.
"""
query_terms = query.split(" ") if query else None
search_types = types or (SEARCH_ALL_GROUPS, SEARCH_CONTACTS, SEARCH_URNS)
per_type_limit = 25
results = []
if SEARCH_ALL_GROUPS in search_types or SEARCH_STATIC_GROUPS in search_types:
groups = ContactGroup.get_user_groups(org, ready_only=True)
# exclude dynamic groups if not searching all groups
if SEARCH_ALL_GROUPS not in search_types:
groups = groups.filter(query=None)
if query:
groups = term_search(groups, ("name__icontains", ), query_terms)
results += list(groups.order_by(Upper("name"))[:per_type_limit])
if SEARCH_CONTACTS in search_types:
try:
search_results = search_contacts(
org,
query,
group=org.cached_active_contacts_group,
sort="name")
contacts = IDSliceQuerySet(Contact, search_results.contact_ids, 0,
len(search_results.contact_ids))
results += list(contacts[:per_type_limit])
Contact.bulk_cache_initialize(org, contacts=results)
except SearchException:
pass
if SEARCH_URNS in search_types:
if not org.is_anon and query and len(query) >= 3:
try:
# build an OR'ed query of all sendable schemes
sendable_schemes = org.get_schemes(Channel.ROLE_SEND)
scheme_query = " OR ".join(f"{s} ~ {json.dumps(query)}"
for s in sendable_schemes)
search_results = search_contacts(
org,
scheme_query,
group=org.cached_active_contacts_group,
sort="name")
urns = ContactURN.objects.filter(
contact_id__in=search_results.contact_ids,
scheme__in=sendable_schemes)
results += list(
urns.prefetch_related("contact").order_by(
Upper("path"))[:per_type_limit])
except SearchException:
pass
return results
def test_order_related_ids(self):
z = Zoo(name='hoi')
z.save()
a9 = Animal.objects.create(zoo_id=z.id, name='a9').id
a0 = Animal.objects.create(zoo_id=z.id, name='a0').id
a2 = Animal.objects.create(zoo_id=z.id, name='a2').id
a6 = Animal.objects.create(zoo_id=z.id, name='a6').id
a7 = Animal.objects.create(zoo_id=z.id, name='a7').id
a5 = Animal.objects.create(zoo_id=z.id, name='a5').id
a4 = Animal.objects.create(zoo_id=z.id, name='a4').id
a3 = Animal.objects.create(zoo_id=z.id, name='a3').id
a8 = Animal.objects.create(zoo_id=z.id, name='a8').id
a1 = Animal.objects.create(zoo_id=z.id, name='a1').id
with CustomOrdering(Animal, 'name'):
response = self.client.get('/zoo/{}/?with=animals'.format(z.id))
self.assertEqual(response.status_code, 200)
returned_data = jsonloads(response.content)
self.assertEqual(returned_data['data']['animals'],
[a0, a1, a2, a3, a4, a5, a6, a7, a8, a9])
# Check ordering on complex OrderBy expression.
with CustomOrdering(Animal, Upper('name').asc(), '-id'):
response = self.client.get('/zoo/{}/?with=animals'.format(z.id))
self.assertEqual(response.status_code, 200)
returned_data = jsonloads(response.content)
self.assertEqual(returned_data['data']['animals'],
[a0, a1, a2, a3, a4, a5, a6, a7, a8, a9])
# Complex order by with desc on an F field with an operation on it
with CustomOrdering(Animal, OrderBy(Length(F('name')),
descending=True), '-name'):
response = self.client.get('/zoo/{}/?with=animals'.format(z.id))
self.assertEqual(response.status_code, 200)
returned_data = jsonloads(response.content)
self.assertEqual(returned_data['data']['animals'],
[a9, a8, a7, a6, a5, a4, a3, a2, a1, a0])
# Nested complex order by
with CustomOrdering(Animal, Reverse(Upper('name')), 'id'):
response = self.client.get('/zoo/{}/?with=animals'.format(z.id))
self.assertEqual(response.status_code, 200)
returned_data = jsonloads(response.content)
self.assertEqual(returned_data['data']['animals'],
[a0, a1, a2, a3, a4, a5, a6, a7, a8, a9])
with CustomOrdering(Animal, '-name'):
response = self.client.get('/zoo/{}/?with=animals'.format(z.id))
self.assertEqual(response.status_code, 200)
returned_data = jsonloads(response.content)
self.assertEqual(returned_data['data']['animals'],
[a9, a8, a7, a6, a5, a4, a3, a2, a1, a0])
def test_function_as_filter(self):
Author.objects.create(name='John Smith', alias='SMITHJ')
Author.objects.create(name='Rhonda')
self.assertQuerysetEqual(
Author.objects.filter(alias=Upper(V('smithj'))), ['John Smith'],
lambda x: x.name)
self.assertQuerysetEqual(
Author.objects.exclude(alias=Upper(V('smithj'))), ['Rhonda'],
lambda x: x.name)
def accountsmap():
df = pd.read_excel("acc_map1.xlsx", header=0)
# replace the NaN as false
df = df.fillna(False)
# import pdb;pdb.set_trace()
df['vchr_module_name'] = df['Type']
df['vchr_category'] = df.get(
'value').str.upper() # if not df.get('value').empty else None
df['COA Code'] = df.get('COA Code').apply(
str) # if not df.get('COA Code').empty else None
df['fk_branch_id'] = None
dct_branch = dict(
Branch.objects.annotate(code=Upper('vchr_code')).values_list(
'code', 'pk_bint_id'))
if not set(tuple(df['SAP Branch Code'].str.strip().str.upper())).issubset(
tuple(dct_branch.keys())):
lst_branch = list(
set(tuple(df['SAP Branch Code'].str.upper())) -
set(tuple(dct_branch.keys())))
return ({
"error_message":
"Some Branches ({str_missing_branch}) not found.".format(
str_missing_branch=','.join(lst_branch))
})
df['fk_branch_id'] = df['SAP Branch Code'].str.strip().str.upper().map(
dct_branch.get)
dct_account = dict(
ChartOfAccounts.objects.annotate(
accnum=Upper('vchr_acc_code')).values_list('accnum', 'pk_bint_id'))
if not set(tuple(df['COA Code'].str.strip().str.upper())).issubset(
tuple(dct_account.keys())):
lst_account = list(
set(tuple(df['COA Code'].str.upper())) -
set(tuple(dct_account.keys())))
return ({
"error_message":
"Some Accounts ({str_missing_account}) not found.".format(
str_missing_account=','.join(lst_account))
})
df['fk_coa_id'] = df['COA Code'].str.strip().str.upper().map(
dct_account.get)
df['int_status'] = 0
AccountsMap.objects.filter(
fk_coa__vchr_acc_code__in=df['COA Code']).update(int_status=1)
uploadcolumns = [
'vchr_module_name', 'vchr_category', 'fk_coa_id', 'int_status',
'fk_branch_id'
]
xlsdf = Savedftosql(df[uploadcolumns], 'accounts_map')
xlsdf.insert_data()
return 'Success'
def test_function_as_filter(self):
Author.objects.create(name="John Smith", alias="SMITHJ")
Author.objects.create(name="Rhonda")
self.assertQuerysetEqual(
Author.objects.filter(alias=Upper(V("smithj"))),
["John Smith"],
lambda x: x.name,
)
self.assertQuerysetEqual(
Author.objects.exclude(alias=Upper(V("smithj"))),
["Rhonda"],
lambda x: x.name,
)
def test_basic(self):
Author.objects.create(name='John Smith', alias='smithj')
Author.objects.create(name='Rhonda')
authors = Author.objects.annotate(upper_name=Upper('name'))
self.assertQuerysetEqual(authors.order_by('name'), [
'JOHN SMITH',
'RHONDA',
], lambda a: a.upper_name)
Author.objects.update(name=Upper('name'))
self.assertQuerysetEqual(authors.order_by('name'), [
('JOHN SMITH', 'JOHN SMITH'),
('RHONDA', 'RHONDA'),
], lambda a: (a.upper_name, a.name))
def consulta(self, ano, mes):
qsCotizacion = Cotizacion.objects.values('usuario').annotate(
vendedor=Upper(
Concat('usuario__first_name', Value(' '),
'usuario__last_name')),
ano_consulta=Extract('fecha_envio', 'year'),
mes_consulta=Extract('fecha_envio', 'month'),
dia_consulta=Extract('fecha_envio', 'day'),
dia_semana_consulta=Extract('fecha_envio', 'week_day'),
hora_consulta=Extract('fecha_envio', 'hour'),
nro_cotizaciones=Count('id'),
total_cotizaciones=Sum('total'),
descuentos_cotizaciones=Sum('descuento'),
nro_ventas=Value(0, output_field=IntegerField()),
facturacion=Value(0, output_field=IntegerField()),
vendedor__colaborador__usuario=Value(0,
output_field=IntegerField()),
).filter(fecha_envio__month__in=mes,
fecha_envio__year__in=ano).order_by('fecha_envio',
'dia_semana_consulta')
qsFacturacion = FacturasBiable.activas.values(
'vendedor__colaborador__usuario').annotate(
vendedor=Upper(
Case(
When(vendedor__colaborador__isnull=True,
then=F('vendedor__nombre')),
default=Concat(
'vendedor__colaborador__usuario__user__first_name',
Value(' '),
'vendedor__colaborador__usuario__user__last_name'),
output_field=CharField(),
)),
ano_consulta=Extract('fecha_documento', 'year'),
mes_consulta=Extract('fecha_documento', 'month'),
dia_consulta=Extract('fecha_documento', 'day'),
dia_semana_consulta=Extract('fecha_documento', 'week_day'),
nro_cotizaciones=Value(0, output_field=IntegerField()),
nro_ventas=Count('id'),
total_cotizaciones=Value(0, output_field=IntegerField()),
descuentos_cotizaciones=Value(0, output_field=IntegerField()),
facturacion=Sum('venta_neto'),
).filter(fecha_documento__month__in=mes,
fecha_documento__year__in=ano)
uno = list(qsCotizacion)
dos = list(qsFacturacion)
tres = []
tres.extend(uno)
tres.extend(dos)
return tres
def cuadre_ventas(self, date):
consulta = self.filter(
anulate=False,
created__date=date,
).values(
'detail_guide', 'detail_guide__guide__date',
'detail_guide__precio_unitario').annotate(
proveedor=Upper('detail_guide__guide__provider__name'),
vendido=Sum('count'),
total=Sum('amount'),
magazine=Upper('detail_guide__magazine_day__magazine__name'
)).order_by('magazine')
return consulta
def get_queryset(self, **kwargs):
# группы букв
letgroups = letters(self)
try:
get_letters = self.request.GET['letters']
return Employee.objects.annotate(
fl=Upper(Substr('surname', 1, 1))).filter(
fl__in=letgroups['letgroups'][int(
self.request.GET['letters'])])
except KeyError:
return Employee.objects.annotate(
fl=Upper(Substr('surname', 1, 1))).filter(
fl__in=letgroups['letgroups'][0])
def qs(self):
# We're doing all the filtering at once here in order to improve filtering performance
from django.db.models.functions import Upper, Substr
from django.db.models import Q, Subquery
if not self.form.is_valid():
return super().qs
if not hasattr(self, '_qs'):
qs = super().qs
selected_date = self.form.cleaned_data['registration_date']
selected_state = self.form.cleaned_data['status']
selected_letter = self.form.cleaned_data['letter']
selected_types = self.form.cleaned_data['concept_type']
# If they haven't selected anything
if selected_state == '':
selected_state = None
if not selected_types:
selected_types = None
# Return all the statuses that are valid at a particular date and then
# filter on the concepts linked to a valid status.
# Return only the statuses that are linked to the RA for the page that you are on
status_is_valid = Q(statuses__in=Subquery(
MDR.Status.objects.filter(
state=selected_state,
registrationAuthority__id=self.registration_authority_id).
valid_at_date(when=selected_date).values('pk')))
inner = MDR._concept.objects.filter(status_is_valid)
qs = qs.filter(pk__in=Subquery(inner.values(
'pk'))).prefetch_related('statuses__registrationAuthority')
# Filter on the selected concept types
if selected_types is not None:
qs = qs.filter(_type__in=selected_types)
qs = qs.annotate(first_letter=Upper(Substr('name', 1, 1)))
if selected_letter == "&":
qs = qs.filter(~Q(first_letter__in=string.ascii_uppercase))
elif selected_letter in string.ascii_uppercase:
qs = qs.filter(name__istartswith=selected_letter)
self._qs = qs.order_by(Upper('name'))
return self._qs
class Migration(migrations.Migration):
atomic = False
dependencies = [
("zerver", "0278_remove_userprofile_alert_words"),
]
operations = [
AddIndexConcurrently(
model_name="message",
index=models.Index(
"recipient",
Upper("subject"),
F("id").desc(nulls_last=True),
name="zerver_message_recipient_upper_subject",
),
),
AddIndexConcurrently(
model_name="message",
index=models.Index(
"recipient",
"subject",
F("id").desc(nulls_last=True),
name="zerver_message_recipient_subject",
),
),
]
def test_functions(self):
self.assertEqual(repr(Coalesce('a', 'b')), "Coalesce(F(a), F(b))")
self.assertEqual(repr(Concat('a', 'b')), "Concat(ConcatPair(F(a), F(b)))")
self.assertEqual(repr(Length('a')), "Length(F(a))")
self.assertEqual(repr(Lower('a')), "Lower(F(a))")
self.assertEqual(repr(Substr('a', 1, 3)), "Substr(F(a), Value(1), Value(3))")
self.assertEqual(repr(Upper('a')), "Upper(F(a))")
def test_it_applies_annotations_if_they_do_not_exist(self, db):
from django.db.models import F
from django.db.models.functions import Upper
u = UserFactory(first_name="hallo")
apply_annotations_to_user(u, {"boop": Upper(F("first_name"))})
assert u.boop == "HALLO"
def node(self, n):
self.nodes_current += 1
# TODO: add 'shop' in n.tags also, more info in https://github.com/gravitystorm/openstreetmap-carto/blob/96c64fa5b0449e79c17e39626f3b8f38c96a12bb/project.mml#L1504
if 'amenity' in n.tags and 'name' in n.tags and len(n.tags['name']) > 2:
try:
point = Point([float(n.location.x) / 10000000, float(n.location.y) / 10000000], srid=4326)
# this is a little slow, but it uses indexes :)
# TODO: improve this by using in-memory pandas queries like we did with the 'cross'
q = Recorrido.objects \
.order_by() \
.annotate(cond=RawSQL("ST_Intersects(ST_Buffer(%s::geography, 400, 2)::geometry, ruta)", (point.ewkb,), output_field=BooleanField())) \
.filter(cond=True) \
.only('id') \
.exists()
if q:
defaults = {
'tags': {k:v for k,v in n.tags},
'nom': n.tags['name'][:200],
'nom_normal': Substr(Trim(Upper(Unaccent(Value(n.tags['name'])))), 1, 200),
'latlng': point,
'country_code': king['country_code'],
}
Poi.objects.update_or_create(
osm_id=n.id,
osm_type='n',
defaults=defaults
)
self.nodes_added += 1
print(f'[{self.nodes_current*100/nodes_count:7.3f}%] / Nodes added: {self.nodes_added} | processed: {self.nodes_current} / {nodes_count}')
except Exception as e:
print(f'Could not save, exception {e}')
def search(request):
user_list = Profile.objects.all().order_by(Upper('nome').asc())
user_filter = UserFilter(request.GET, queryset=user_list)
return render(request, 'participante/participante_list.html', {
'filter': user_filter,
'section': 'participantes'
})
class OrderedByFBandAdmin(admin.ModelAdmin):
list_display = ['name', 'genres', 'nr_of_members']
ordering = (
F('nr_of_members').desc(nulls_last=True),
Upper(F('name')).asc(),
F('genres').asc(),
)
class CandidatoViewSet(viewsets.ReadOnlyModelViewSet):
queryset = (models.PessoaFisica.objects.annotate(
nome_normalizado=Upper(F("nome"))).exclude(candidato_ps=None).all())
serializer_class = serializers.PessoaFisicaSerializer
filter_fields = ("cpf", )
permission_classes = (permissions.IsMemberOfGroup, )
group_required = "API - Web Service SUAP"
