def _last_responses_to_draw_offer_datetime(self):
return (
super()
.get_queryset()
.filter(game=models.OuterRef("game"), name__in=DrawEventTypes.values())
.annotate(
_created_at=models.Case(
models.When(
name=DrawEventTypes.OFFERED_DRAW.value, then=datetime.min
),
default=models.F("created_at"),
)
)
.values("game")
.annotate(result=models.Max("_created_at"))
.values("result")[:1]
)
def get_queryset(self, request):
qs = super(SentenceAdmin, self).get_queryset(request)
qs = qs\
.annotate(sum_approved=models.Sum(
models.Case(
models.When(
quality_control__isnull=True,
then=models.Value(0)),
models.When(
quality_control__approved=True,
then=models.Value(1)),
models.When(
quality_control__approved=False,
then=models.Value(0)),
default=models.Value(0),
output_field=models.IntegerField())))
return qs
def with_filter_labels(self, program):
"""Booleans for program page filters:
indicator.reporting and on_scope are part of IPTTIndicatorManager"""
qs = self.get_queryset()
defined_targets_filter = get_defined_targets_filter(program)
qs = qs.annotate(
all_targets_defined=models.Case(
models.When(
defined_targets_filter,
then=models.Value(True)
),
default=models.Value(False),
output_field=models.BooleanField()
)
)
return qs
def get_proximas_avaliador(cls, fase, avaliador):
return (cls.ordered_objects.annotate(
minhas_inscricoes_incompletas=models.Case(
models.When(
curso__inscricoes_preanalise__avaliacoes_avaliador__avaliador
=avaliador,
curso__inscricoes_preanalise__avaliacoes_homologador__isnull
=True,
curso__inscricoes_preanalise__modalidade=models.F(
"modalidade"),
then=1,
),
default=0,
output_field=models.IntegerField(),
)).filter(fase=fase, inscricoes_pendentes=True).order_by(
"meta_atingida", "porcentagem_analise",
"minhas_inscricoes_incompletas").distinct())
def get_search_results(self, request, queryset, search_term):
qs, use_distinct = super(CourseEnrollmentAdmin,
self).get_search_results(
request, queryset, search_term)
# annotate each enrollment with whether the username was an
# exact match for the search term
qs = qs.annotate(exact_username_match=models.Case(
models.When(user__username=search_term, then=models.Value(True)),
default=models.Value(False),
output_field=models.BooleanField()))
# present exact matches first
qs = qs.order_by('-exact_username_match', 'user__username',
'course_id')
return qs, use_distinct
def view_ferm_ftr(request: HttpResponse, year: int,
order_no: int) -> Union[HttpResponse, FileResponse]:
from django.db.models import functions
batches = FermBatch.objects.select_related(
'tank', 'recipe__brew_item').filter(
date__year=year, order=order_no,
recipe__product__name='Beer').order_by('batch')
batches = batches.annotate(
cellar_size=models.Case(models.When(tank__cellar='CCT',
then=functions.ConcatPair(
models.F('tank__cellar'),
models.F('tank__capacity'))),
default=models.F('tank__cellar'),
output_field=models.CharField(max_length=7)),
filling_time_hours=Epoch(models.F('filling_time')) / 3600).only(
'date', 'recipe__brew_item', 'batch', 'tank', 'locked', 'analyzed')
if request.GET.get('download', False):
from django.http import FileResponse
from csv import DictWriter
filename = f'FTR-Ferm-{year}-{order_no}.csv'
fieldnames = ('date', 'recipe__brew_item__number', 'batch',
'cellar_size', 'metered_volume', 'filling_time')
baseqs = list(batches.values(*fieldnames))
for i in range(len(baseqs)):
try:
baseqs[i]['filling_time'] = baseqs[i][
'filling_time'].total_seconds() / 3600
except AttributeError:
pass
with open(f'/tmp/{filename}', 'w') as outfile:
writer = DictWriter(outfile, fieldnames)
writer.writeheader()
for row in baseqs:
try:
writer.writerow(row)
except ValueError:
print(row)
return FileResponse(open(f'/tmp/{filename}', 'rb'), as_attachment=True)
return render(request, 'cb/ferm/view_ferm_ftr.html', {
'batches': batches,
'title': f'FTR Fermentation: Order {order_no}, {year}'
})
def annotate_with_number_of_groups_on_assignment(self, assignment):
"""
Annotates the queryset with number of :class:`devilry.apps.core.models.AssignmentGroup`
objects where the RelatedExaminer is :class:`devilry.apps.core.models.Examiner` within the given
assignment.
Args:
assignment: A :class:`devilry.apps.core.models.Assignment` object.
"""
return self.annotate(
number_of_groups_on_assignment=models.Count(
models.Case(
models.When(examiner__assignmentgroup__parentnode=assignment,
then=1)
)
)
)
def with_status(self):
duplicates_subquery = (self.values(
"period", "form",
"org_unit").annotate(ids=ArrayAgg("id")).annotate(
c=models.Func("ids", models.Value(1), function="array_length")
).filter(form__in=Form.objects.filter(
single_per_period=True)).filter(c__gt=1).annotate(
id=models.Func("ids", function="unnest")).values("id"))
return self.annotate(status=models.Case(
models.When(id__in=duplicates_subquery,
then=models.Value(Instance.STATUS_DUPLICATED)),
models.When(last_export_success_at__isnull=False,
then=models.Value(Instance.STATUS_EXPORTED)),
default=models.Value(Instance.STATUS_READY),
output_field=models.CharField(),
))
def with_annotations(self):
qs = self.with_prefetch()
qs = qs.annotate(using_results_framework=models.Case(
models.When(program___using_results_framework=Program.NOT_MIGRATED,
then=models.Value(False)),
default=models.Value(True),
output_field=models.BooleanField()))
# add lop_target_calculated annotation (not used yet, but will replace deprecated lop_target value):
qs = qs.annotate(lop_target_calculated=utils.
indicator_lop_target_calculated_annotation())
# add lop_actual annotation
qs = qs.annotate(lop_actual=utils.indicator_lop_actual_annotation())
# add lop_met_real annotation (this is a float, formatting delivered on front end):
qs = qs.annotate(
lop_percent_met=utils.indicator_lop_percent_met_annotation())
qs = self.annotate_old_level(qs).order_by(
models.F('old_level_pk').asc(nulls_last=True))
return qs
def with_self_and_parents(self):
"""
This function populates the :py:attr:`Course.period_self_and_parents`
field.
:return: a QuerySet
"""
cases = [
models.When(
models.Q(period=period.name),
then=models.Value(list(
map(lambda e: e.name, [period, *period.all_parents()])),
output_field=ArrayField(models.CharField())))
for period in Period
]
return self.annotate(period_parents_and_self=models.Case(
*cases, default=models.Value([], ArrayField(models.CharField()))))
def annotate_sleep_time(baseQ):
start_cutoff = time(hour=0, minute=0, second=0)
when1 = models.When(start_time__gt=start_cutoff,
start_time__lt="05:00:00",
then=RawSQL(
"\
DATE(\
DATE_SUB(\
start , interval 1 day\
)\
)\
", []))
when2 = models.When(start_time__gt="05:00:00",
then=RawSQL("DATE(start)", []))
return baseQ.annotate(
sleep_date=models.Case(when1, when2, output_field=models.DateField()))
def room_radio_filters(self, room_pk):
room_related_filter_with_choice = RoomCharacteristics.objects.get(
pk=room_pk).radio_choices.all().values_list(
'related_filter_id', 'selected_option_id')
chosen_when = [
django_models.When(id=related_filter_id, then=choice_id)
for related_filter_id, choice_id in room_related_filter_with_choice
]
chosen_case = django_models.Case(
*chosen_when,
default=-1,
output_field=django_models.IntegerField())
return self.instance_of(RadioFilter).exclude(
django_models.Q(name__contains='Duration') |
django_models.Q(name__contains='Room Type'))\
.annotate(chosen_option_id=chosen_case)
def lease_doc_number_by_country(self, request):
"""
lessor -- Filter by lessor name
"""
lessor = request.query_params.get('lessor', None)
q = LeaseDocument.objects \
.values('address_country') \
.annotate(
leases_number=models.Count('address_country') if not lessor else models.Count(
models.Case(models.When(lessor=lessor, then=1)))) \
.order_by('-leases_number', 'address_country')
data = {'rows': [{'address_country': row['address_country'],
'address_country_code': LeaseMapViewSet._country_short(
row['address_country']),
'leases_number': row['leases_number']}
for row in q if row['address_country']]}
return JsonResponse(data)
def set_country_codes(apps, schema_editor):
# Set country_code on ZipCode objects in batches
# (so as not to read/update 1M rows)
batch_size = 1000
if schema_editor.connection.alias == 'default':
ZipCode = apps.get_model('django_geo', 'ZipCode')
offset = 0
while True:
ids = ZipCode.objects.order_by('id').values_list(
'id', flat=True)[offset:offset + batch_size]
if not ids.exists():
break
ZipCode.objects.filter(id__in=list(ids)).update(
country_code=models.Case(models.When(
zip_code__regex='[0-9]{5}', then=models.Value('US')),
default=models.Value('CA')))
offset += batch_size
def indicator_defined_targets_months():
"""annotates a queryset of indicators with the number of months their targets cover
(number of targets * months in period) for time-aware target frequencies
used by the program level get_defined_targets filter"""
cases = []
for frequency, month_count in utils.TIME_AWARE_FREQUENCIES:
cases.append(
models.When(
target_frequency=frequency,
then=models.ExpressionWrapper(
(models.F('defined_targets') * month_count),
output_field=models.IntegerField()
)
)
)
return models.Case(
*cases, default=models.Value(None, output_field=models.IntegerField(null=True))
)
def get_pior_desempenho_disciplina_turma(disciplina, turma):
"""
Retorna o pior desempenho da turma.
TODO: Unificar esse método com o get_melhor_desempenho_disciplina_turma
"""
return ExercisesDataWharehouse.objects.filter(
disciplina=disciplina, turma=turma).values(
'escola__nome', 'aluno__nome', 'turma__identificador',
'disciplina__nome').annotate(
correta=models.Sum(
models.Case(models.When(resposta=F('gabarito'), then=1),
default=0,
output_field=models.IntegerField())),
exercicios_realizados=models.Count('exercicio'),
indice=F('correta') * 100 /
F('exercicios_realizados')).aggregate(
min=models.Min(F('indice')))['min']
def product_class_as_queryset(product):
"Returns a queryset with the product_classes of a product (only one)"
ProductClass = product._meta.get_field("product_class").related_model
return ProductClass.objects.filter(
pk__in=product.__class__.objects.filter(pk=product.pk)
.annotate(
product_class_id=models.Case(
models.When(
structure=product.CHILD, then=models.F("parent__product_class")
),
models.When(
structure__in=[product.PARENT, product.STANDALONE],
then=models.F("product_class"),
),
)
)
.values("product_class_id")
)
def accueil(request):
"""
Affiche les 5 derniers articles du blog. Nous n'avons pas encore
vu comment faire de la pagination, donc on ne donne pas la
possibilité de lire les articles plus vieux via l'accueil pour
le moment.
"""
#articles = Article.objects.filter(is_visible=True).order_by('-date')[:4]
#articles = Article.objects.filter(is_visible=True).values('titre','slug','date','contenu').annotate(nbrcommentaire=Count('comment'))
#articles = Article.objects.filter(is_visible=True).values('titre','slug','date','contenu').annotate(nbrcommentaire=Count('comment',filter=Q(comment__is_visible=1)))
#articles = Article.objects.filter(is_visible=True,comment__is_visible=True).values('titre','date','contenu','comment__is_visible').annotate(nbrcommentaire=Count('comment'))
articles = Article.objects.all().annotate(nbrcommentaire=models.Sum(
models.Case(models.When(comment__is_visible=1, then=1),
default=0,
output_field=models.IntegerField())))
print(articles.query)
return render(request, 'blog/accueil.html', {'articles': articles})
def indicator_lop_actual_annotation():
"""annotates an indicator with the value for the results table Life of Program Actual field
NOT FOR progress measurement, does not take into account completed/active periods"""
return models.Case(
models.When(unit_of_measure_type=Indicator.PERCENTAGE,
then=models.Subquery(
Result.objects.select_related(None).prefetch_related(
None).filter(periodic_target__isnull=False,
indicator=models.OuterRef('pk')).
order_by('-date_collected').values('achieved')[:1])),
default=models.Subquery(
Result.objects.select_related(None).prefetch_related(None).filter(
periodic_target__isnull=False,
indicator=models.OuterRef('pk')).order_by().values(
'indicator').annotate(achieved_sum=models.Sum(
'achieved')).values('achieved_sum')[:1]),
output_field=models.DecimalField(decimal_places=2))
def view_clients(request):
"""
Show all clients
"""
q = Client.objects.filter(user=request.user).annotate(
num_invoices=models.Count('invoices'),
num_unpaid_invoices=models.Sum(
models.Case(
models.When(invoices__status=Invoice.ISSUED, then=1),
default=0,
output_field=models.IntegerField(),
),
),
)
return render(request, 'business/pages/view_clients.html', {
'title': "View Clients",
'clients': q,
})
def calculate_indice_turma_acumulado(turma, disciplina, bim_ref=1):
"""
Calcula a média acumulada do índice de desempenho da turma no ano corrente.
"""
rs = ExercisesDataWharehouse.objects.filter(
disciplina=disciplina,
turma=turma,
exercicio__aula__bimestre__lte=bim_ref).values(
'turma__identificador', 'exercicio__aula__bimestre',
'disciplina__nome').annotate(
correta=models.Sum(
models.Case(models.When(resposta=F('gabarito'), then=1),
default=0,
output_field=models.IntegerField())),
exercicios_realizados=models.Count('exercicio'),
indice=F('correta') * 100 /
F('exercicios_realizados')).aggregate(models.Sum('indice'))
acumulado = rs['indice__sum'] / bim_ref
return acumulado
def get_queryset(self, *args, **kwargs):
overridden_reviews = Review.objects.filter(
override_vote__isnull=False, submission_id=models.OuterRef('pk'))
queryset = self.request.event.submissions.filter(state__in=[
SubmissionStates.SUBMITTED,
SubmissionStates.ACCEPTED,
SubmissionStates.REJECTED,
SubmissionStates.CONFIRMED,
])
queryset = self.filter_queryset(queryset)
return (queryset.order_by('review_id').annotate(
has_override=models.Exists(overridden_reviews)).annotate(
avg_score=models.Case(
models.When(
has_override=True,
then=self.request.event.settings.review_max_score + 1,
),
default=models.Avg('reviews__score'),
)).order_by('-state', '-avg_score', 'code'))
def query_events_by_last_date(event_set, date):
""" Filters the set passed on latest event date
Parameters:
event_set (query_set): A query set of event objects to filter
date (datetime)
Returns:
(query_set): The filtered event set
"""
# Get events with dates earlier than ldate
event_set = event_set.annotate(has_date=Max(
models.Case(
models.When(eventdate__date__lt=date, then=1),
output_field=models.IntegerField(),
))).filter(has_date=True)
return event_set
def room_integral_filters(self, room_pk):
room_related_filter_with_choice = RoomCharacteristics.objects.get(
pk=room_pk).integral_choices.all().values_list(
'related_filter_id', 'selected_number')
chosen_when = [
django_models.When(id=related_filter_id, then=selected_number)
for related_filter_id, selected_number in
room_related_filter_with_choice
]
chosen_case = django_models.Case(
*chosen_when,
default=-1,
output_field=django_models.IntegerField())
return self.instance_of(IntegralFilter).exclude(
django_models.Q(name__contains='Price') |
django_models.Q(name__contains='People Allowed Number'))\
.annotate(selected_number=chosen_case)
def check_annotate_filters(list_display, request, filter_kwargs):
"""
Create additional annotate to queryset if specific list order is active
"""
if request.GET.get("o"):
# check ordering fields (-1 cuz of list indexing)
order_filter_fields = [
list_display[int(index) - 1]
for index in request.GET.get("o").replace("-", "").split(".")
]
if "donor_delay" in order_filter_fields:
filter_kwargs = {
"order_payment_delay": models.Value(None, models.DurationField())
}
if not request.GET.get("userchannels__event__id__in"):
messages.warning(
request, _("Please select event before sort by donor delay")
)
else:
event_id = request.GET["userchannels__event__id__in"].split(",")[0]
donor_channels = aklub_models.DonorPaymentChannel.objects.filter(
event_id=event_id,
user=models.OuterRef("id"),
).annotate(
duration_sort=models.Case( # noqa
models.When(
~models.Q(expected_regular_payment_date=None),
then=models.F("expected_regular_payment_date")
- datetime.date.today(),
),
default=None,
output_field=models.DurationField(),
)
)
filter_kwargs.update(
{
"order_payment_delay": models.Subquery(
donor_channels.values("duration_sort")
)
}
)
return filter_kwargs
return filter_kwargs
def desempenho_turma_assuntos(self, disciplina, turma, bimestre):
"""Desempenho de alunos por disciplina.
O resultado é filtrado por disciplina, turma e bimestre.
"""
return self.filter(
disciplina=disciplina,
turma=turma,
exercicio__aula__bimestre=bimestre).values(
'turma__identificador', 'disciplina__nome', 'disciplina',
'exercicio__aula__assunto', 'exercicio__aula').annotate(
acertos=models.Sum(
models.Case(models.When(resposta=F('gabarito'),
then=1),
default=0,
output_field=models.IntegerField())),
ttl_exercicios=models.Count('exercicio'),
erros=F('ttl_exercicios') - F('acertos'),
indice=F('acertos') * 100 / F('ttl_exercicios'))
def with_annotations(self, *annotations):
qs = self.filter(deleted__isnull=True)
if 'months' in annotations or 'targets' in annotations:
# 'months' is for unit testing,
# 'targets' because program_months is a prerequisite for measuring all_targets_defined
qs = qs.annotate(
program_months=utils.indicator_get_program_months_annotation())
if 'targets' in annotations or 'reporting' in annotations or 'scope' in annotations:
qs = qs.annotate(lop_target_calculated=utils.
indicator_lop_target_calculated_annotation())
if 'targets' in annotations:
# sets all_targets_defined to True/False based on business rules
qs = qs.annotate(defined_targets=models.Count('periodictargets'))
defined_targets_filter = indicator_get_defined_targets_filter()
qs = qs.annotate(all_targets_defined=models.Case(
models.When(defined_targets_filter, then=models.Value(True)),
default=models.Value(False),
output_field=models.BooleanField()))
if 'results' in annotations:
# result count = count of results associated with indicator:
qs = qs.annotate(
results_count=utils.indicator_results_count_annotation())
if 'evidence' in annotations:
# results with evidence count = count of results that have evidence associated
qs = qs.annotate(results_with_evidence_count=utils.
indicator_results_evidence_annotation())
if 'reporting' in annotations or 'scope' in annotations:
# reporting indicates whether indicator should be counted towards on-scope reporting
# note: "reporting" alone is for testing, scope relies on these annotations as a prerequisite
qs = qs.annotate(lop_actual_progress=utils.
indicator_lop_actual_progress_annotation())
qs = qs.annotate(lop_target_progress=utils.
indicator_lop_target_progress_annotation())
qs = qs.annotate(reporting=utils.indicator_reporting_annotation())
if 'scope' in annotations:
qs = qs.annotate(lop_percent_met_progress=utils.
indicator_lop_percent_met_progress_annotation())
qs = qs.annotate(
over_under=utils.indicator_over_under_annotation())
if 'table' in annotations:
qs = qs.select_related('level')
return qs
def calculate_turma_ranking(escola, disciplina, serie, bim=1):
"""
Retorna um dictionary em ordem decrescente de índice de desempenho. A posição
da turma no dict determina o seu ranqueamentoself.
"""
rs = ExercisesDataWharehouse.objects.filter(
turma__escola=escola,
disciplina=disciplina,
exercicio__aula__bimestre=bim,
exercicio__aula__serie=serie).values(
'turma', 'turma__identificador', 'disciplina__nome').annotate(
correta=models.Sum(
models.Case(models.When(resposta=F('gabarito'), then=1),
default=0,
output_field=models.IntegerField())),
exercicios_realizados=models.Count('exercicio'),
indice=F('correta') * 100 /
F('exercicios_realizados')).order_by('-indice')
return rs
def get_rate(source, target, backend=None):
"""
Returns an exchange rate between source and target currencies.
Converts exchange rate on the DB side if there is no backends with given base currency.
Uses data from the default backend if the backend is not specified.
"""
if backend is None:
backend = get_default_backend_name()
if source == target:
return 1
try:
forward = models.Q(currency=target, backend__base_currency=source)
reverse = models.Q(currency=source, backend__base_currency=target)
return Rate.objects.annotate(rate=models.Case(
models.When(forward, then=models.F('value')),
models.When(reverse,
then=models.Value(get_one()) / models.F('value')),
)).get(forward | reverse, backend=backend).rate
except Rate.DoesNotExist:
raise MissingRate('Rate %s -> %s does not exist' % (source, target))
def get_files_with_missing_dependencies(
cls,
expected_dependencies: int = 8
) -> List["DiskTSlicedSAveragedFormFactor4DFile"]:
"""Get all DiskTSlicedSAveragedFormFactor4DFile where the dependency does not
exist.
Note:
Currently expected_dependencies to exist is hardcoded.
Dependency does not exists is defined by:
1. Not expected_dependencies dependency files are in the db,
2. No file status is associated with the dependency file,
3. The file status of the dependency file is does not exist.
"""
return cls.objects.annotate(dependency_count=models.Count(
models.Case(
models.When(file__dependencies__disk__exists=True,
then=models.Value(1))))).filter(~models.Q(
dependency_count=expected_dependencies))
