def annotate_usage(self, *statuses):
"""
Returns the current queryset annotated with usage information from requirements
that reference the resource.
"""
from .requirement import Requirement
# Generate the annotations for each possible status
annotations = dict(
chain.from_iterable(
(('{}_count'.format(status.name.lower()),
models.Count('status', filter=models.Q(status=status))),
('{}_total'.format(status.name.lower()),
models.Sum('amount', filter=models.Q(status=status))))
# If no statuses are given, use them all
for status in (statuses or Requirement.Status)))
# This subquery fetches the count and total of all requirements for the quota
requirements = (Requirement.objects.filter(
service__project__consortium=models.OuterRef('consortium'),
resource=models.OuterRef('resource')).order_by().values(
'service__project__consortium',
'resource').annotate(**annotations))
# Apply the annotations to the current query
return self.annotate(
# Coalesce the corresponding annotation from the subquery
**{
annotation: functions.Coalesce(
models.Subquery(requirements.values(annotation)),
models.Value(0))
for annotation in annotations
})
def index(request):
# Ordering
valid_ordering = ["query_string", "-query_string", "views", "-views"]
ordering = valid_ordering[0]
if "ordering" in request.GET and request.GET["ordering"] in valid_ordering:
ordering = request.GET["ordering"]
# Query
queries = Query.objects.filter(editors_picks__isnull=False).distinct()
if "views" in ordering:
queries = queries.annotate(
views=functions.Coalesce(Sum("daily_hits__hits"), 0))
queries = queries.order_by(ordering)
# Search
is_searching = False
query_string = request.GET.get("q", "")
if query_string:
queries = queries.filter(query_string__icontains=query_string)
is_searching = True
# Paginate
paginator = Paginator(queries, per_page=20)
queries = paginator.get_page(request.GET.get("p"))
if request.headers.get("x-requested-with") == "XMLHttpRequest":
return TemplateResponse(
request,
"wagtailsearchpromotions/results.html",
{
"is_searching": is_searching,
"ordering": ordering,
"queries": queries,
"query_string": query_string,
},
)
else:
return TemplateResponse(
request,
"wagtailsearchpromotions/index.html",
{
"is_searching":
is_searching,
"ordering":
ordering,
"queries":
queries,
"query_string":
query_string,
"search_form":
SearchForm(
data={"q": query_string} if query_string else None,
placeholder=_("Search promoted results"),
),
},
)
def get_context_data(self, **kwargs):
context = super(IndexTemplateView, self).get_context_data(**kwargs)
context['total_project'] = Project.objects.all().count()
context['success_project'] = Project.objects.filter(status='C').count()
context['progress_project'] = Project.objects.filter(
status='A').count()
context['late_project'] = Project.objects.filter(
status='A', end_date__lt=date.today()).count()
context['plan_project'] = Project.objects.filter(status='P').count()
context['total_budget'] = Project.objects.all().aggregate(
total_expected_budget=Sum(functions.Coalesce('expected_budget', 0),
output_field=FloatField()),
total_executed_budget=Sum(functions.Coalesce('executed_budget', 0),
output_field=FloatField()))
context['project_without_budget'] = Project.objects.filter(
expected_budget__isnull=True).count()
context['project_burst_budget'] = Project.objects.filter(
expected_budget__lt=F('executed_budget')).count()
cat = Category.objects.annotate(
total_project=Count('project__id')
).extra(
select={
'progress':
"CAST((SELECT COUNT(*) FROM core_project WHERE category_id = core_category.id and core_project.status = 'C') AS float)/CAST((SELECT COUNT(*) FROM core_project WHERE status = 'C') AS FLOAT)*100"
}).order_by('-progress')
tipos = Type.objects.values('name').annotate(
total_project=Count('project__id')).order_by('-total_project')
total_restante = 0
for tipo in tipos[5:]:
total_restante += tipo['total_project']
context['categories'] = cat[:5]
context['tipos'] = tipos[:5]
context['total_restante'] = total_restante
# Locais
context['institutions'] = Institution.objects.extra(select={
'projects':
"SELECT COUNT(*) from core_project WHERE core_project.institution_id = core_institution.id"
},
order_by=[
'-projects'
])[:5]
return context
def coalesce(*args):
"""
Chooses the first non-null value from left to right.
Usage:
coalesce(F.savings_account, F.check_account, 0)
"""
return functions.Coalesce(*args)
def _filter_and_order_markets(self, order_by, orig_filter):
if order_by == 'users':
return self.markets.filter(**orig_filter).order_by('-number_of_registered_users')
elif order_by == 'commission':
markets = self.markets.filter(**orig_filter)
annotated = markets.annotate(commision_null=functions.Coalesce('commission_lower', -1))
return annotated.order_by('commision_null', 'commission_lower')
else:
return self._order_markets_by_relevance(orig_filter)
def save(self, *args, **kwargs):
if not self.pk:
# попытка решить проблему того, что при создании коллекции,
# элементы отсортированы в порядке загрузки, а не в порядке
# добавления. Код ниже не решает проблему, но уменьшает её влияние.
if self.collection.items.filter(order=self.order).exists():
max_order = self.collection.items.aggregate(
order=functions.Coalesce(models.Max('order'), 0))['order']
self.order = max_order + 1
super().save(*args, **kwargs)
def annotate_available(self):
"""
Returns the current queryset annotated with information about the total amount
available from all the chunks for that resource.
"""
# This subquery returns the total of all the chunks for the outer resource
from .resource_chunk import ResourceChunk
chunks = (ResourceChunk.objects.filter(resource=models.OuterRef(
'pk')).order_by().values('resource').annotate(
total=models.Sum('amount')))
return self.annotate(total_available=functions.Coalesce(
models.Subquery(chunks.values('total')), models.Value(0)))
def index(request):
# Ordering
valid_ordering = ['query_string', '-query_string', 'views', '-views']
ordering = valid_ordering[0]
if 'ordering' in request.GET and request.GET['ordering'] in valid_ordering:
ordering = request.GET['ordering']
# Query
queries = Query.objects.filter(editors_picks__isnull=False).distinct()
if 'views' in ordering:
queries = queries.annotate(
views=functions.Coalesce(Sum('daily_hits__hits'), 0))
queries = queries.order_by(ordering)
# Search
is_searching = False
query_string = request.GET.get('q', '')
if query_string:
queries = queries.filter(query_string__icontains=query_string)
is_searching = True
# Paginate
paginator = Paginator(queries, per_page=20)
queries = paginator.get_page(request.GET.get('p'))
if request.headers.get('x-requested-with') == 'XMLHttpRequest':
return TemplateResponse(
request, "wagtailsearchpromotions/results.html", {
'is_searching': is_searching,
'ordering': ordering,
'queries': queries,
'query_string': query_string,
})
else:
return TemplateResponse(
request, 'wagtailsearchpromotions/index.html', {
'is_searching':
is_searching,
'ordering':
ordering,
'queries':
queries,
'query_string':
query_string,
'search_form':
SearchForm(data=dict(q=query_string) if query_string else None,
placeholder=_("Search promoted results")),
})
def for_user(self, user, start, end):
"""Get employments in given time frame for current user.
This includes overlapping employments.
:param User user: The user of the searched employments
:param datetime.date start: start of time frame
:param datetime.date end: end of time frame
:returns: queryset of employments
"""
# end date NULL on database is like employment is ending today
queryset = self.annotate(
end=functions.Coalesce("end_date", models.Value(date.today())))
return queryset.filter(user=user).exclude(
models.Q(end__lt=start) | models.Q(start_date__gt=end))
def annotate_usage(self):
"""
Returns the current queryset annotated with usage information from
quotas and requirements that reference the resource.
"""
# This subquery returns the count and total of all quotas for the outer resource
from .quota import Quota
quotas = (Quota.objects.filter(resource=models.OuterRef(
'pk')).order_by().values('resource').annotate(
count=models.Count('*'), total=models.Sum('amount')))
# This subquery fetches the count and total of all requirements for the current resource for each status
from .requirement import Requirement
# Generate the annotations for each possible status
subquery_annotations = dict(
chain.from_iterable(
(('{}_count'.format(status.name.lower()),
models.Count('status', filter=models.Q(status=status))),
('{}_total'.format(status.name.lower()),
models.Sum('amount', filter=models.Q(status=status))))
for status in Requirement.Status))
requirements = (Requirement.objects.filter(resource=models.OuterRef(
'pk')).order_by().values('resource').annotate(
**subquery_annotations))
# Apply the annotations to the current query
return self.annotate(
quota_count=functions.Coalesce(
models.Subquery(quotas.values('count')), models.Value(0)),
quota_total=functions.Coalesce(
models.Subquery(quotas.values('total')), models.Value(0)),
# Coalesce the corresponding annotation from the subquery
**{
annotation: functions.Coalesce(
models.Subquery(requirements.values(annotation)),
models.Value(0))
for annotation in subquery_annotations
})
def annotate_value_or_default(self, option_name):
"""
then annotate_option_values will annotate the queryset with
- The default value of the option as '<option_name>_default'
- COALSCE of the field value and the default value annotated as '<option_name>''
- raw value of the field available as '_' + 'option_name'
"""
field_name = '_' + option_name
default_annotation_name = option_name + '_default'
return (self.annotate_defaults(option_name).annotate(
**{
option_name:
functions.Coalesce(field_name, default_annotation_name)
}))