def mid_end_value_annotation(count):
values_subquery = Result.objects.select_related(None).prefetch_related(
None).filter(
periodic_target__isnull=False,
indicator=models.OuterRef('pk'),
periodic_target__customsort__lte=count).order_by('-date_collected')
return models.Case(
models.When(unit_of_measure_type=Indicator.PERCENTAGE,
then=models.Subquery(
values_subquery.filter(
periodic_target__customsort=count).values(
'achieved')[:1])),
models.When(
is_cumulative=True,
then=models.Subquery(
values_subquery.order_by().values('indicator').annotate(
this_period_count=models.Sum(
models.Case(models.When(
periodic_target__customsort=count, then=1),
output_field=models.IntegerField()))).
annotate(achieved_sum=models.Case(
models.When(this_period_count__gt=0,
then=models.Sum('achieved')),
default=models.Value(None))).values('achieved_sum')[:1])),
default=models.Subquery(values_subquery.order_by().filter(
periodic_target__customsort=count).values('indicator').annotate(
achieved_sum=models.Sum('achieved')).values('achieved_sum')
[:1]),
output_field=models.DecimalField(decimal_places=2))
def get_interlocutors(self, *, interlocutor: User) -> QuerySet:
"""Get a list of interlocutors of the user."""
recipients = self.get_recipients(sender=interlocutor).values("pk")
senders = self.get_senders(recipient=interlocutor).values("pk")
return User.objects.filter(
Q(pk__in=models.Subquery(recipients))
| Q(pk__in=models.Subquery(senders))).distinct("pk")
def get_annotated_responses_qs(include_comments=False, include_time=False):
"""Retrieve a queryset for the set of responses belonging to a set of studies."""
# Create the subquery where we get the action from the most recent ruling.
newest_ruling_subquery = models.Subquery(
ConsentRuling.objects.filter(response=models.OuterRef("pk")).order_by(
"-created_at").values("action")[:1])
# Annotate that value as "current ruling" on our response queryset.
annotated_query = (
Response.objects.prefetch_related("consent_rulings").filter(
completed_consent_frame=True).annotate(current_ruling=Coalesce(
newest_ruling_subquery, models.Value(PENDING))))
if include_comments:
comment_subquery = models.Subquery(
ConsentRuling.objects.filter(response=models.OuterRef(
"pk")).order_by("-created_at").values("comments")[:1])
annotated_query = annotated_query.annotate(
ruling_comments=Coalesce(comment_subquery, models.Value("N/A")))
if include_time:
time_subquery = models.Subquery(
ConsentRuling.objects.filter(response=models.OuterRef(
"pk")).order_by("-created_at").values("created_at")[:1])
annotated_query = annotated_query.annotate(
time_of_ruling=time_subquery)
return annotated_query
def handle(self, *args, **options):
# Prefer upcoming events over ongoing events
updated_count = Hobby.objects.update(next_event_id=models.Subquery(
HobbyEvent.objects.filter(hobby=models.OuterRef('pk'),
start_date__gte=date.today()).
values_list('id').order_by('start_date')[:1]))
self.stdout.write(f'Updated {updated_count} hobbies')
# If there are Hobbies with only ongoing events and no upcoming events
# then fall back to the ongoing event
fallback_updated_count = Hobby.objects.filter(
next_event_id__isnull=True).update(next_event_id=models.Subquery(
HobbyEvent.objects.filter(hobby=models.OuterRef('pk'),
end_date__gte=date.today()).
values_list('id').order_by('start_date')[:1]))
self.stdout.write(
f'Updated {fallback_updated_count} hobbies with fallback query')
hobbies_without_next_event_count = Hobby.objects.filter(
next_event_id__isnull=True).count()
start_date_in_future_count = updated_count - fallback_updated_count
end_date_in_future_count = fallback_updated_count - hobbies_without_next_event_count
self.stdout.write(
f'{start_date_in_future_count} hobbies with upcoming next event')
self.stdout.write(
f'{end_date_in_future_count} hobbies with ongoing next event')
self.stdout.write(
f'{hobbies_without_next_event_count} hobbies without next event')
def annotate_with_app_statistics(self) -> QuerySet[User]:
pets_likes_count = User.objects.annotate(pets_likes_count=models.Count(
'users_pet_choices',
filter=models.Q(users_pet_choices__is_favorite=True))).filter(
pk=models.OuterRef('pk'))
pets_dislikes_count = User.objects.annotate(
pets_dislikes_count=models.Count(
'users_pet_choices',
filter=models.Q(users_pet_choices__is_favorite=False))).filter(
pk=models.OuterRef('pk'))
pets_getpet_requests_count = User.objects.annotate(
pets_getpet_requests_count=models.Count(
'get_pet_requests', )).filter(pk=models.OuterRef('pk'))
return self.annotate(
pets_likes_count=models.Subquery(
pets_likes_count.values('pets_likes_count'),
output_field=models.DateTimeField()),
pets_dislikes_count=models.Subquery(
pets_dislikes_count.values('pets_dislikes_count'),
output_field=models.IntegerField()),
pets_getpet_requests_count=models.Subquery(
pets_getpet_requests_count.values(
'pets_getpet_requests_count'),
output_field=models.IntegerField()),
)
def anotarPracticas(practicas, **kwargs):
practicas = Estado.anotarPracticas(practicas, **kwargs)
turnos = Programada.objects.filter(
practica=models.OuterRef("id")).order_by("-id")
if kwargs.get("programada_por", False):
practicas = practicas.annotate(
id_programada_por=models.Subquery(
turnos.values("usuario__id")[:1]),
nombre_programada_por=models.Subquery(
turnos.values("usuario__username")[:1]))
if kwargs.get("horario_turno", False) or kwargs.get(
"duracion_turno", False):
practicas = practicas.annotate(inicio_turno=models.Subquery(
turnos.values("inicio")[:1])).annotate(
finalizacion_turno=models.Subquery(
turnos.values("finalizacion")[:1]))
if kwargs.get("duracion_turno", False):
practicas = practicas.annotate(
duracion_turno=models.ExpressionWrapper(
models.F("finalizacion_turno") -
models.F("inicio_turno"), models.DurationField()))
if kwargs.get("reprogramaciones", False):
practicas = practicas.annotate(
reprogramaciones=models.ExpressionWrapper(
models.Count("estado__programada") -
1, models.IntegerField()))
return practicas
def periods(self, periods):
"""annotate a query with actual data sums for a set of time periods"""
period_annotations = {}
for c, period in enumerate(periods):
date_range = "{0}-{1}".format(
period['start_date'].strftime('%Y-%m-%d'),
period['end_date'].strftime('%Y-%m-%d')
)
period_annotations[date_range] = models.Case(
models.When(
unit_of_measure_type=Indicator.PERCENTAGE,
then=models.Subquery(
CollectedData.objects.filter(
indicator_id=models.OuterRef('pk'),
date_collected__lte=period['end_date']
).order_by('-date_collected').values('achieved')[:1],
output_field=models.FloatField()
)),
default=models.Subquery(
CollectedData.objects.filter(
indicator_id=models.OuterRef('pk'),
date_collected__lte=period['end_date']
).filter(
models.Q(date_collected__gte=period['start_date']) |
models.Q(indicator__is_cumulative=True)
).order_by().values('indicator_id').annotate(
total=models.Sum('achieved')).values('total'),
output_field=models.FloatField())
)
period_annotations["period_{0}".format(c)] = models.Value(
date_range, output_field=models.CharField())
return self.get_queryset().annotate(**period_annotations)
def get_for_gq(cls, user, only_member=False):
"""
Used by graphql schema
"""
current_user_role_subquery = models.Subquery(
ProjectMembership.objects.filter(
project=models.OuterRef('pk'),
member=user,
).order_by('role__level').values('role__type')[:1],
output_field=models.CharField(),
)
current_user_membership_data_subquery = JSONObject(
user_id=models.Value(user.id),
role=models.F('current_user_role'),
badges=models.Subquery(
ProjectMembership.objects.filter(
project=models.OuterRef('pk'),
member=user,
).order_by('badges').values('badges')[:1],
output_field=ArrayField(models.CharField()),
),
)
visible_projects = cls.objects\
.annotate(
# For using within query filters
current_user_role=current_user_role_subquery,
).annotate(
# NOTE: This is used by permission module
current_user_membership_data=current_user_membership_data_subquery,
# NOTE: Exclude if project is private + user is not a member
).exclude(is_private=True, current_user_role__isnull=True)
if only_member:
return visible_projects.filter(current_user_role__isnull=False)
return visible_projects
def indicator_lop_target_calculated_annotation():
"""annotates an indicator with the sum of targets for the entire program (not taking active/inactive targets)
into account - NOT for progress, for results display only"""
return models.Case(
models.When(models.Q(
models.Q(
models.Q(unit_of_measure_type=Indicator.PERCENTAGE)
| models.Q(is_cumulative=True))
& models.Q(
target_frequency__in=[Indicator.MID_END, Indicator.EVENT])),
then=models.Subquery(PeriodicTarget.objects.filter(
indicator=models.OuterRef('pk')).order_by(
'-customsort').values('target')[:1],
output_field=models.FloatField())),
models.When(models.Q(
models.Q(unit_of_measure_type=Indicator.PERCENTAGE)
| models.Q(is_cumulative=True)),
then=models.Subquery(PeriodicTarget.objects.filter(
indicator=models.OuterRef('pk')).order_by(
'-end_date').values('target')[:1],
output_field=models.FloatField())),
default=models.Subquery(
PeriodicTarget.objects.filter(indicator=models.OuterRef(
'pk')).order_by().values('indicator').annotate(
target_sum=models.Sum('target')).values('target_sum')[:1],
output_field=models.FloatField()))
def deleteContentCb(sender, instance, **kwargs):
from .models import ContentReference
references = ContentReference.objects.filter(target=instance)
other_references = ContentReference.objects.filter(~models.Q(
target=instance))
nogroup_references = references.filter(
deleteRecursive=DeleteRecursive.NO_GROUP.value, )
recursive_references = references.filter(
deleteRecursive=DeleteRecursive.TRUE.value)
# delete recursive connected contents
sender.objects.filter(references__in=recursive_references).delete()
nogroup_groups = set(nogroup_references.values_list("group", flat=True))
# delete contents if group vanishes and NO_GROUP is set
delete_ids = models.Subquery(
sender.objects.filter(
models.Q(references__in=nogroup_references)).annotate(
all_groups=models.Subquery(
ContentReference.objects.filter(
group__in=nogroup_groups,
source=models.OuterRef("pk"),
).annotate(amount=models.Count("group", distinct=True))),
remaining_groups=models.Subquery(
other_references.filter(
group__in=nogroup_groups,
source=models.OuterRef("pk"),
).annotate(amount=models.Count("group", distinct=True))),
).filter(remaining_groups__lt=models.F("all_groups")).values("pk"))
sender.objects.filter(id__in=delete_ids).delete()
def program_all_targets_defined_annotation():
"""annotates a queryset of programs with whether all targets are defined for all indicators for that program"""
targets_subquery = PeriodicTarget.objects.filter(
indicator__deleted__isnull=True,
indicator_id=models.OuterRef('pk')
).order_by().values('indicator_id').annotate(
target_count=models.Count('pk')
).values('target_count')[:1]
target_subquery = Indicator.objects.filter(
deleted__isnull=True,
program_id=models.OuterRef('pk')
).order_by().values('program_id').annotate(
defined_targets=models.Subquery(
targets_subquery,
output_field=models.IntegerField()
)
).annotate(defined_targets_months=indicator_defined_targets_months())
return models.functions.Coalesce(
models.Subquery(
target_subquery.filter(
program_get_defined_targets_filter()
).order_by().values('program_id').annotate(
all_defined_targets_count=models.Count('id')
).values('all_defined_targets_count')[:1],
output_field=models.IntegerField()
), 0)
def check_permission(request, permission, query, authset=None):
assert issubclass(query.model, Cluster), (
"Not a cluster query: %s" % query.model
)
global_groups = GlobalGroupProperty.objects.get_or_create(
name=permission, defaults={}
)[0].groups.all()
q = models.Q(clusters__in=query)
if getattr(settings, "SECRETGRAPH_BIND_TO_USER", False):
global_groups_names = global_groups.filter(
matchUserGroup=True
).values_list("name", flat=True)
if global_groups_names:
q |= models.Q(
clusters__in=models.Subquery(
query.filter(
user__group__name__in=global_groups_names
).values("id")
)
)
user = getattr(request, "user", None)
if user:
q |= models.Q(
name__in=models.Subquery(user.groups.values("name"))
)
return global_groups.filter(q).exists()
def annotate_for_analysis_pillar_summary(cls, qs):
analytical_statement_prefech = models.Prefetch(
'analyticalstatement_set',
queryset=(AnalyticalStatement.objects.annotate(
entries_count=models.Count('entries', distinct=True))))
return qs\
.prefetch_related(analytical_statement_prefech)\
.annotate(
dragged_entries=models.functions.Coalesce(
models.Subquery(
AnalyticalStatement.objects.filter(
analysis_pillar=models.OuterRef('pk')
).order_by().values('analysis_pillar').annotate(count=models.Count(
'entries',
distinct=True,
filter=models.Q(entries__lead__published_on__lte=models.OuterRef('analysis__end_date'))))
.values('count')[:1],
output_field=models.IntegerField(),
), 0),
discarded_entries=models.functions.Coalesce(
models.Subquery(
DiscardedEntry.objects.filter(
analysis_pillar=models.OuterRef('pk')
).order_by().values('analysis_pillar__analysis').annotate(count=models.Count(
'entry',
distinct=True,
filter=models.Q(entry__lead__published_on__lte=models.OuterRef('analysis__end_date'))))
.values('count')[:1],
output_field=models.IntegerField(),
), 0),
analyzed_entries=models.F('dragged_entries') + models.F('discarded_entries'),
)
def get_annotated():
from entry.models import Lead, Entry
pk = models.OuterRef('pk')
threshold = timezone.now() - timedelta(days=30)
return Project.objects.annotate(
number_of_leads=models.Count('lead', distinct=True),
number_of_entries=models.Count('lead__entry', distinct=True),
leads_activity=models.functions.Coalesce(
models.Subquery(
Lead.objects.filter(
project=pk,
created_at__gt=threshold,
).order_by().values('project').annotate(
c=models.Count('*')).values('c')[:1],
output_field=models.IntegerField(),
), 0),
entries_activity=models.functions.Coalesce(
models.Subquery(
Entry.objects.filter(
lead__project=pk,
created_at__gt=threshold,
).order_by().values('lead__project').annotate(
c=models.Count('*')).values('c')[:1],
output_field=models.IntegerField(),
), 0),
)
def get_queryset(self):
queryset = Activity.objects.all()
if self.action == 'timeline':
user = self.request.user
queryset = queryset.filter(
models.Q(user__in=models.Subquery(
user.following.through.objects.filter(
to_user_id=user.id).values('from_user_id')))
| (models.Q(brick_name__in=models.Subquery(
user.bricks_watching.through.objects.filter(
user=user.id).values('brick')))
& ~models.Q(user=user.id)))
user = self.request.query_params.get('user', None)
type = self.request.query_params.get('type', None)
if user is not None:
queryset = queryset.filter(user__username=user)
if type is not None:
queryset = queryset.filter(type__in=type.split(','))
queryset = queryset.annotate(score=models.Case(models.When(
type='Watch',
then=models.Subquery(
BiobrickMeta.objects.filter(part_name=models.OuterRef(
'brick_name')).values('rate_score'))),
default=None))
return queryset.order_by('-acttime')
def anotarPracticas(practicas, **kwargs):
practicas = Estado.anotarPracticas(practicas, **kwargs)
realizaciones = Realizada.objects.filter(
practica=models.OuterRef("id")).order_by("-id")
if kwargs.get("realizada_por", False):
practicas = practicas.annotate(
id_realizada_por=models.Subquery(
realizaciones.values("usuario__id")[:1]),
nombre_realizada_por=models.Subquery(
realizaciones.values("usuario__username")[:1]))
if kwargs.get("horario_realizacion", False) or kwargs.get(
"duracion_realizacion", False):
practicas = practicas.annotate(inicio_realizacion=models.Subquery(
realizaciones.values("inicio")[:1])).annotate(
finalizacion_realizacion=models.Subquery(
realizaciones.values("finalizacion")[:1]))
if kwargs.get("duracion_realizacion", False):
practicas = practicas.annotate(
duracion_realizacion=models.ExpressionWrapper(
models.F("finalizacion_realizacion") -
models.F("inicio_realizacion"), models.DurationField()))
return practicas
def persons_with_multiple_jobs(self, jobs=None):
"""Take only persons with multiple jobs that has the given jobs.
For every person:
> Filter all conditions and validate it empty
===> Not contains different positions (from the given).
> Annotate which each position in the given jobs.
> Take only the persons with '1' value on all the positions
annotations.
===> Exactly only given positions.
"""
HospitalWorker = apps.get_model(Covid19Config.app_name,
"HospitalWorker")
not_given_job_condition = models.Q()
if jobs is not None:
# Defining the given jobs conditions for matching worker.
matching_workers_conditions = {job: 1 for job in jobs}
# Value for job existence annotation per each person.
jobs_existence_annotation = {
job: models.Count(
models.Subquery(
HospitalWorker.objects.filter(
position=job, person=models.OuterRef(
"person")).values("position")))
for job in jobs
}
# Make the for detecting person with not given job.
for job in jobs:
not_given_job_condition &= ~models.Q(position=job)
# Search matching workers.
matching_workers = HospitalWorker.objects.annotate(
not_matching_jobs=models.Count(
models.Subquery(
HospitalWorker.objects.filter(
not_given_job_condition,
person=models.OuterRef("person")).values(
"person"))),
**jobs_existence_annotation).filter(
# Filter only the workers with 0 not matching jobs.
not_matching_jobs=0,
**matching_workers_conditions).values("person")
else:
# All of the workers are in potential matching workers.
matching_workers = HospitalWorker.objects.all().values("person")
multiple_jobs_workers = HospitalWorker.objects.filter(
person__in=matching_workers).values(
"person", "position",
"department").annotate(jobs=models.Count("person")).filter(
jobs__gte=2, ).values("person")
return self.filter(id__in=multiple_jobs_workers)
def annotate_file_data(self):
first_upload = Upload.objects.filter(
track=models.OuterRef("pk")).order_by("pk")
return self.annotate(
bitrate=models.Subquery(first_upload.values("bitrate")[:1]),
size=models.Subquery(first_upload.values("size")[:1]),
mimetype=models.Subquery(first_upload.values("mimetype")[:1]),
)
def target_actual_annotation():
""" value for "actual" on this target's row in the Results Table:
- NUMBER/cumulative: sum of results in this periodic target period,
- NUMBER/noncumulative: sum of results from all periods up to and including this one
- PERCENTAGE: latest result in this period
"""
return models.Case(
models.When(
models.Q(
models.Q(indicator__unit_of_measure_type=Indicator.PERCENTAGE)
& models.Q(indicator__target_frequency__in=[
f[0] for f in utils.TIME_AWARE_FREQUENCIES
])),
then=models.Subquery(
Result.objects.filter(periodic_target=models.OuterRef('pk')).
order_by('-date_collected').values('achieved')[:1], )),
models.When(models.Q(
models.Q(indicator__unit_of_measure_type=Indicator.PERCENTAGE)
& ~models.Q(indicator__target_frequency__in=[
f[0] for f in utils.TIME_AWARE_FREQUENCIES
])),
then=models.Subquery(
Result.objects.filter(
periodic_target=models.OuterRef('pk')).order_by(
'-date_collected').values('achieved')[:1], )),
models.When(
models.Q(
models.Q(indicator__unit_of_measure_type=Indicator.NUMBER)
& models.Q(indicator__is_cumulative=True)
& models.Q(indicator__target_frequency__in=[
f[0] for f in utils.TIME_AWARE_FREQUENCIES
]) & models.Q(results_count__gt=0)),
then=models.Subquery(
Result.objects.filter(
models.Q(indicator=models.OuterRef('indicator'))
& models.Q(periodic_target__end_date__lte=models.OuterRef(
'end_date'))).order_by().values('indicator').annotate(
achieved_sum=models.Sum('achieved')).values(
'achieved_sum')[:1])),
models.When(models.Q(
models.Q(indicator__unit_of_measure_type=Indicator.NUMBER)
& models.Q(indicator__is_cumulative=True)
& ~models.Q(indicator__target_frequency__in=[
f[0] for f in utils.TIME_AWARE_FREQUENCIES
]) & models.Q(results_count__gt=0)),
then=models.Subquery(
Result.objects.filter(
models.Q(indicator=models.OuterRef('indicator'))
& models.Q(periodic_target__customsort__lte=models.
OuterRef('customsort'))).order_by().
values('indicator').annotate(achieved_sum=models.Sum(
'achieved')).values('achieved_sum')[:1])),
default=models.Subquery(
Result.objects.filter(periodic_target=models.OuterRef(
'pk')).order_by().values('periodic_target').annotate(
achieved_sum=models.Sum('achieved')).values('achieved_sum')
[:1]),
output_field=models.DecimalField(decimal_places=2))
def debtors(self, level):
from contests.models import Credit
return self.filter(~models.Q(level=level)).annotate(
credit_id=models.Subquery(Credit.objects.filter(course__level=level,
user_id=models.OuterRef('user_id')).values('id'))
).annotate(
credit_score=models.Subquery(Credit.objects.filter(course__level=level,
user_id=models.OuterRef('user_id')).values('score'))
).filter(credit_score__lte=2)
def with_credits(self):
from contests.models import Credit
return self.annotate(
credit_id=models.Subquery(Credit.objects.filter(course__level=models.OuterRef('level'),
user_id=models.OuterRef('user_id')).values('id'))
).annotate(
credit_score=models.Subquery(Credit.objects.filter(course__level=models.OuterRef('level'),
user_id=models.OuterRef('user_id')).values('score'))
)
def indicator_lop_actual_progress_annotation():
"""actual progress:
- only data associated with active periodic targets
- summed if NUMBER, latest if PERCENTAGE
"""
return models.Case(
models.When(
models.Q(
models.Q(
models.Q(
models.Q(target_frequency=Indicator.LOP) &
# models.Q(program__reporting_period_end__lte=models.functions.Now())
models.Q(program__reporting_period_end__lt=UTCNow()))
| models.Q(target_frequency__in=[
Indicator.MID_END, Indicator.EVENT
])) & models.Q(unit_of_measure_type=Indicator.PERCENTAGE)),
then=models.Subquery(
Result.objects.filter(
periodic_target__isnull=False,
indicator=models.OuterRef('pk')).order_by(
'-date_collected').values('achieved')[:1])),
models.When(
models.Q(
models.Q(
models.Q(target_frequency=Indicator.LOP) &
# models.Q(program__reporting_period_end__lte=models.functions.Now())
models.Q(program__reporting_period_end__lt=UTCNow()))
| models.
Q(target_frequency__in=[Indicator.MID_END, Indicator.EVENT])),
then=models.Subquery(
Result.objects.filter(
periodic_target__isnull=False,
indicator=models.OuterRef('pk')).order_by().values(
'indicator').annotate(actual_sum=models.Sum(
'achieved')).values('actual_sum')[:1])),
models.When(models.Q(
models.Q(
target_frequency__in=[f[0] for f in TIME_AWARE_FREQUENCIES])
& models.Q(unit_of_measure_type=Indicator.PERCENTAGE)),
then=models.Subquery(
Result.objects.filter(
models.Q(periodic_target__isnull=False)
& models.Q(indicator=models.OuterRef('pk'))
& models.Q(periodic_target__end_date__lt=UTCNow())
).order_by('-date_collected').values('achieved')[:1])),
models.When(
target_frequency__in=[f[0] for f in TIME_AWARE_FREQUENCIES],
then=models.Subquery(
Result.objects.filter(
models.Q(periodic_target__isnull=False)
& models.Q(indicator=models.OuterRef('pk')) &
# models.Q(periodic_target__end_date__lt=models.functions.Now())
models.Q(periodic_target__end_date__lt=UTCNow())).order_by(
).values('indicator').annotate(actual_sum=models.Sum(
'achieved')).values('actual_sum')[:1])),
default=models.Value(None),
output_field=models.DecimalField(decimal_places=2))
def ns_ongoing_projects_stats(self, request, pk=None):
projects = self.get_projects()
ref_projects = projects.filter(reporting_ns=models.OuterRef('pk'))
project_per_sector = defaultdict(list)
for reporting_ns, primary_sector, count in (
projects.order_by('reporting_ns', 'primary_sector')
.values('reporting_ns', 'primary_sector')
.annotate(count=models.Count('id'))
.values_list('reporting_ns', 'primary_sector', 'count')
):
project_per_sector[reporting_ns].append({
'primary_sector': primary_sector,
'primary_sector_display': Sectors(primary_sector).label,
'count': count,
})
return Response({
'results': [
{
**ns_data,
'projects_per_sector': project_per_sector.get(ns_data['id']),
'operation_types_display': [
OperationTypes(operation_type).label
for operation_type in ns_data['operation_types']
]
}
for ns_data in Country.objects.annotate(
ongoing_projects=Coalesce(models.Subquery(
ref_projects.values('reporting_ns').annotate(
count=models.Count('id')).values('count')[:1],
output_field=models.IntegerField(),
), 0),
target_total=Coalesce(models.Subquery(
ref_projects.values('reporting_ns').annotate(
target_total=models.Sum('target_total')).values('target_total')[:1],
output_field=models.IntegerField(),
), 0),
budget_amount_total=Coalesce(models.Subquery(
ref_projects.values('reporting_ns').annotate(
budget_amount_total=models.Sum('budget_amount')).values('budget_amount_total')[:1],
output_field=models.IntegerField(),
), 0),
operation_types=Coalesce(models.Subquery(
ref_projects.values('reporting_ns').annotate(
operation_types=ArrayAgg('operation_type', distinct=True)).values('operation_types')[:1],
output_field=ArrayField(models.IntegerField()),
), []),
).filter(ongoing_projects__gt=0).order_by('id').values(
'id', 'name', 'iso3', 'iso3', 'society_name',
'ongoing_projects', 'target_total', 'budget_amount_total', 'operation_types',
)
]
})
def get_dead_details(self):
"""Get for each patient if he is dead and why."""
MedicalExaminationResult = apps.get_model(Covid19Config.app_name,
"MedicalExaminationResult")
patients = MedicalExaminationResult.objects. \
get_patient_examination_results(patient_id=models.OuterRef("id"))
return self.annotate(
is_dead=models.Subquery(patients[:1]),
reason_of_dead=models.Subquery(patients[1:2]),
).filter(is_dead=MedicalExaminationResult.RESULT_DEAD,
reason_of_dead=MedicalExaminationResult.RESULT_CORONA)
def get_queryset(self, request):
queryset = super().get_queryset(request)
# Add subquery for active contract
contracts = self.model.get_active_contracts(None,
models.OuterRef('pk'))
# Add annotated fields for contract id and company
queryset = queryset.annotate(
_country=models.F('birth_location__region__country'),
_contract_id=models.Subquery(contracts.values('pk')[:1]),
_contract_company=models.Subquery(
contracts.values('company__name')[:1]),
)
return queryset
def latest_annotations(self):
# Device is alerting in several cases
# 1. The latest carbon monoxide reading is > 9 PPM
# 2. The device's latest health status is one of
# "needs_service", "needs_new_filter" or "gas_leak."
latest_co = models.Subquery(
DeviceSensorLog.objects.filter(
device=models.OuterRef('pk'),
sensor_type=DeviceSensorLog.CARBON_MONOXIDE).order_by(
'-stamp').values('value')[:1])
latest_status = models.Subquery(
DeviceHealthStatus.objects.filter(device=models.OuterRef(
'pk'), ).order_by('-stamp').values('value')[:1])
return self.annotate(latest_co=latest_co, latest_status=latest_status)
def needs_additional_target_periods(self):
return self.annotate(needs_additional_target_periods=models.Case(
models.When(reporting_period_end__gt=models.Subquery(
Indicator.program_page_objects.filter(
program=models.OuterRef('pk')).annotate(
newest_end_date=models.Subquery(
PeriodicTarget.objects.filter(
indicator=models.OuterRef('pk')).order_by(
'-end_date').values('end_date')[:1])).
order_by('newest_end_date').values('newest_end_date')[:1],
output_field=models.DateField()),
then=models.Value(True)),
default=models.Value(False),
output_field=models.BooleanField()))
def with_partner_info(self):
from .partner.partner import Partner
return self.annotate(
partner_name=models.Subquery(
Organization.objects.filter(pk=models.OuterRef(
'organization_id'), ).values('name')[:1]),
partner_city=models.Subquery(
Partner.objects.filter(
pk=models.OuterRef('organization__partner__id'),
).annotate_address('city').values('city')[:1]),
partner_country=models.Subquery(
Partner.objects.filter(
pk=models.OuterRef('organization__partner__id'), ).
annotate_address('country__name').values('country_name')[:1]),
)
def get(self, request, *args, **kwargs):
program_id = kwargs.get('program')
programs = request.user.tola_user.available_programs.annotate(
indicators_count=models.Count('indicator'),
targets_exist=models.Exists(
PeriodicTarget.objects.filter(
indicator__program=models.OuterRef('pk')
)
),
tva_indicators_count=models.Subquery(
Indicator.rf_aware_objects.filter(
program=models.OuterRef('pk'),
target_frequency__in=Indicator.REGULAR_TARGET_FREQUENCIES + (Indicator.LOP, Indicator.MID_END)
).order_by().values('program').annotate(tva_count=models.Count('pk')).values('tva_count')[:1],
output_field=models.IntegerField()
)
).filter(
funding_status="Funded",
targets_exist=True,
reporting_period_start__isnull=False,
reporting_period_end__isnull=False,
indicators_count__gt=0
).order_by('name').values_list('pk', 'name', 'tva_indicators_count')
program_data = IPTTProgramSerializer.get_for_pk(program_id).data
react_data = {
'programs_list': list(programs),
'program_data': program_data,
}
return self.render_to_response({'react_context': react_data})
def with_annotations(self, *annotations):
if not annotations:
annotations = ['targets', 'results', 'evidence', 'scope']
qs = self
# this needs to be cleaned up if we want queries to be lightning fast again
#nondeleted_indicators = Indicator.objects.filter(deleted__isnull=True, program_id=models.OuterRef('pk'))
if any(
key in annotations for key in
['count', 'targets', 'results', 'evidence', 'reporting', 'scope']):
qs = qs.annotate(indicator_count=models.functions.Coalesce(
models.Subquery(Indicator.objects.filter(
deleted__isnull=True).filter(program_id=models.OuterRef(
'pk')).order_by().values('program').annotate(
i_cnt=models.Count('id')).values('i_cnt')[:1],
output_field=models.IntegerField()), 0))
if any(key in annotations
for key in ['results_count', 'results', 'evidence']):
qs = qs.annotate(
reported_results_sum=program_results_annotation(False))
if 'targets' in annotations:
qs = qs.annotate(
program_months=program_get_program_months_annotation())
qs = qs.annotate(
targets_defined_count=program_all_targets_defined_annotation())
if 'results' in annotations:
qs = qs.annotate(
total_results_count=program_results_annotation(False))
qs = qs.annotate(
reported_results_count=program_results_annotation(True))
if 'evidence' in annotations:
qs = qs.annotate(
results_evidence_count=program_evidence_annotation())
if 'reporting' in annotations or 'scope' in annotations:
qs = qs.prefetch_related(program_scope_annotations(*annotations))
return qs
