def get_coupons(cls, coupon_codes):
coupon_obj = None
expression = F('sent_at') + datetime.timedelta(days=1) * F('validity')
annotate_expression = ExpressionWrapper(expression, DateTimeField())
random_coupons = cls.objects.annotate(last_date=annotate_expression)\
.filter(random_coupon__in=coupon_codes,
sent_at__isnull=False,
consumed_at__isnull=True,
last_date__gte=datetime.datetime.now()
).all()
if random_coupons:
c_list = ','.join(["'" + str(c) + "'" for c in coupon_codes])
coupon_obj = Coupon.objects\
.annotate(is_random=models.Value(True, models.BooleanField()),
random_count=models.Value(1, models.IntegerField()))\
.filter(id__in=random_coupons.values_list('coupon', flat=True)) \
.extra(select={'random_coupon_code': 'SELECT random_coupon FROM random_generated_coupon where coupon_id = coupon.id AND random_coupon IN ('+c_list+')'})
if not coupon_obj:
coupon_obj = Coupon.objects \
.annotate(is_random=models.Value(False, models.BooleanField()),
random_count=models.Value(0, models.IntegerField()),
random_coupon_code=models.Value("", models.CharField()))\
.filter(code__in=coupon_codes)
return coupon_obj
def articles(self):
if self._articles is not None:
return self._articles
article_content_type = ContentType.objects.get_for_model(
article_models.ArticlePage)
series_content_type = ContentType.objects.get_for_model(
article_models.SeriesPage)
articles = Page.objects.live().filter(
models.Q(content_type=article_content_type)
| models.Q(content_type=series_content_type)).annotate(
sticky=models.Case(
models.When(models.Q(seriespage__sticky=True)
| (models.Q(articlepage__sticky=True)),
then=models.Value(1)),
default=models.Value(0),
output_field=models.IntegerField())).exclude(
models.Q(seriespage__slippery=True)
| models.Q(articlepage__slippery=True)).order_by(
"-sticky", "-first_published_at")
articles = list(articles[:42])
used = []
if self.featured_item:
used.append(self.featured_item.id)
self._articles = self.get_article_set(
self.number_of_columns_of_articles,
self.number_of_rows_of_articles, articles, used)
return self._articles
def privileged_to_visibility(apps, schema):
EventSubtype = apps.get_model("events", "EventSubtype")
EventSubtype.objects.update(visibility=models.Case(
models.When(privileged_only=True, then=models.Value("D")),
default=models.Value("A"),
))
def get_context_data(self, *args, **kwargs):
context = super().get_context_data(*args, **kwargs)
user_progress = self.request.user.progress
started_subchapters = user_progress.values_list("sub_chapter_id", flat=True)
current_progress = user_progress.get(sub_chapter=self.sub_chapter)
context.update(
{
"course": self.course,
"chapter": self.chapter,
"sub_chapter": self.sub_chapter,
"sub_chapters": self.chapter.sub_chapters.annotate(
is_started=models.Case(
models.When(
id__in=started_subchapters, then=models.Value(True)
),
default=models.Value(False),
output_field=models.BooleanField(),
)
),
"current_progress": current_progress,
"stars": range(current_progress.grade or 0),
}
)
if not self.has_valid_subscription:
context.update(get_pricing_context())
return context
def target_is_complete_annotation():
"""is_complete == active == reporting
time-aware: the target period is over
lop: the program reporting period is over
event/mid-end: there is at least one data point entered
"""
return models.Case(
models.When(
models.Q(
models.Q(indicator__target_frequency=Indicator.LOP) &
# models.Q(indicator__program__reporting_period_end__lte=models.functions.Now())
models.Q(indicator__program__reporting_period_end__lt=utils.
UTCNow())),
then=models.Value(True)),
models.When(models.Q(
models.Q(indicator__target_frequency__in=[
Indicator.MID_END, Indicator.EVENT
]) & models.Q(results_count__gt=0)),
then=models.Value(True)),
models.When(
models.Q(
models.Q(indicator__target_frequency__in=[
f[0] for f in utils.TIME_AWARE_FREQUENCIES
]) &
# models.Q(end_date__lte=models.functions.Now())
models.Q(end_date__lt=utils.UTCNow())),
then=models.Value(True)),
default=models.Value(False),
output_field=models.BooleanField())
def update_instances_traffic():
# TODO: this function should be optimized, the for can be replaced with a single query
current_date = datetime.utcnow().replace(tzinfo=timezone.utc)
port_traffic_query = NetworkPortTraffic.objects.all()
if PUBLIC_TRAFFIC_ONLY:
port_traffic_query = port_traffic_query.filter(resource__is_private=False)
for instance in Instance.objects.exclude(status__in=[InstanceStatus.DELETED, InstanceStatus.ERROR]).all():
cycle_traffic = port_traffic_query.filter(
resource__instance_id=instance.id,
start_datetime__lt=current_date,
end_datetime__gt=current_date,
type=NetworkPortTrafficTypes.per_billing_cycle,
).aggregate(
incoming_bytes_sum=Coalesce(models.Sum('incoming_bytes'), models.Value(0)),
outgoing_bytes_sum=Coalesce(models.Sum('outgoing_bytes'), models.Value(0)),
)
month_traffic = port_traffic_query.filter(
resource__instance_id=instance.id,
start_datetime__lt=current_date,
end_datetime__gt=current_date,
type=NetworkPortTrafficTypes.per_month
).aggregate(
incoming_bytes_sum=Coalesce(models.Sum('incoming_bytes'), models.Value(0)),
outgoing_bytes_sum=Coalesce(models.Sum('outgoing_bytes'), models.Value(0)),
)
instance.current_cycle_traffic = cycle_traffic['incoming_bytes_sum'] + cycle_traffic['outgoing_bytes_sum']
instance.current_cycle_traffic = ceil(instance.current_cycle_traffic / BYTES_PER_GB)
instance.current_month_traffic = month_traffic['incoming_bytes_sum'] + month_traffic['outgoing_bytes_sum']
instance.current_month_traffic = ceil(instance.current_month_traffic / BYTES_PER_GB)
instance.save(update_fields=['current_cycle_traffic', 'current_month_traffic'])
def get_evidence_count(self, qs):
"""annotates qs with evidence_count= # of results that have evidence, and all_results_backed_up=Boolean"""
data_with_evidence = CollectedData.objects.filter(
models.Q(indicator_id=models.OuterRef('pk')) |
models.Q(periodic_target__indicator_id=models.OuterRef('pk')),
models.Q(evidence__isnull=False) | models.Q(tola_table__isnull=False)
).order_by().values('indicator_id')
qs = qs.annotate(
evidence_count=models.functions.Coalesce(
models.Subquery(
data_with_evidence.annotate(
total_count=models.Count('date_collected')
).order_by().values('total_count')[:1],
output_field=models.IntegerField()
), 0)
)
qs = qs.annotate(
all_results_backed_up=models.Case(
models.When(
# if no results, then it isn't "missing" data, so we count this as all_backed_up
models.Q(reported_results=0) |
models.Q(
#models.Q(reported_results__isnull=False) &
models.Q(evidence_count__isnull=False) &
models.Q(evidence_count=models.F('reported_results'))
),
then=models.Value(True)
),
default=models.Value(False),
output_field=models.BooleanField()
)
)
return qs
def with_relevance(self):
"""
Annotates "relevance" value based on next date and/or status.
Use with with_next_date() !
Relevance:
1) if private/canceled: -1
2) if event coming up OR same day as today: +1
3) if event passed: 0
Returns:
QuerySet
"""
now = timezone.now()
return self.annotate(relevance=models.Case(
models.When(
models.Q(published__isnull=True)
| models.Q(canceled__isnull=False),
then=models.Value(-1),
),
models.When(
models.Q(
models.Q(next_date__gte=now)
| models.Q(
next_date__day=now.day,
next_date__month=now.month,
next_date__year=now.year,
)),
then=models.Value(1),
),
default=0,
output_field=models.IntegerField(),
))
def indicator_all_targets_defined_annotation():
"""uses a set of filters that filter out indicators that do not have all defined targets
this version is used for an indicator_set that has been annotated with program_months
then returns a boolean indicating whether all targets have been defined"""
filters = []
# LOP indicators require a defined lop_target:
filters.append(
models.Q(target_frequency=Indicator.LOP)
& models.Q(lop_target__isnull=False))
# MID_END indicators require 2 defined targets (mid and end):
filters.append(
models.Q(target_frequency=Indicator.MID_END)
& models.Q(defined_targets__isnull=False)
& models.Q(defined_targets__gte=2))
# EVENT indicators require at least 1 defined target:
filters.append(
models.Q(target_frequency=Indicator.EVENT)
& models.Q(defined_targets__isnull=False)
& models.Q(defined_targets__gte=1))
# TIME_AWARE indicators need a number of indicators defined by the annotation on the program:
# note the program_months field annotation is required for this annotation to succeed
for frequency, month_count in TIME_AWARE_FREQUENCIES:
period_count_filter = models.Q(
defined_targets__gte=models.F('program_months') / month_count)
filters.append(
models.Q(target_frequency=frequency)
& models.Q(defined_targets__isnull=False) & period_count_filter)
combined_filter = filters.pop()
for filt in filters:
combined_filter |= filt
return models.Case(models.When(combined_filter, then=models.Value(True)),
default=models.Value(False),
output_field=models.BooleanField())
def privileged_to_visibility(apps, schema):
SupportGroupSubtype = apps.get_model("groups", "SupportGroupSubtype")
SupportGroupSubtype.objects.update(visibility=models.Case(
models.When(privileged_only=True, then=models.Value("D")),
default=models.Value("A"),
))
def filter_queryset(self, queryset):
queryset = super().filter_queryset(queryset)
# Check if project/framework exclusion query is present
exclude_project = self.request.query_params.get(
'members_exclude_project')
exclude_framework = self.request.query_params.get(
'members_exclude_framework')
if exclude_project:
queryset = queryset.filter(~models.Q(
projectmembership__project=exclude_project)).distinct()
if exclude_framework:
queryset = queryset.filter(~models.Q(
framework_membership__framework_id=exclude_framework))
search_str = self.request.query_params.get('search')
if search_str is None or not search_str.strip():
return queryset
return queryset.annotate(strpos=StrPos(
models.functions.Lower(
models.functions.Concat('first_name',
models.Value(' '),
'last_name',
models.Value(' '),
'email',
output_field=models.CharField())),
models.Value(search_str.lower(), models.CharField()))).filter(
strpos__gte=1).order_by('strpos')
def __anonymize_user_data_fast(self):
"""
Simply sets usernames to the ID of the user.
"""
if settings.DJANGO_CRADMIN_USE_EMAIL_AUTH_BACKEND:
get_user_model().objects.update(fullname='Full Name',
lastname='Lastname',
shortname=Concat(
models.F('id'),
models.Value('@example.com'),
output_field=CharField()))
else:
get_user_model().objects.update(fullname='Full Name',
lastname='Lastname',
shortname=Concat(
models.F('id'),
models.Value(''),
output_field=CharField()))
UserEmail.objects.update(email=Concat(models.F('user_id'),
models.Value('_'),
models.F('id'),
models.Value('@example.com'),
output_field=CharField()))
UserName.objects.update(username=Concat(models.F('user_id'),
models.Value('_'),
models.F('id'),
output_field=CharField()))
def test_serialize_complex_func_index(self):
index = models.Index(
models.Func("rating", function="ABS"),
models.Case(
models.When(name="special", then=models.Value("X")),
default=models.Value("other"),
),
models.ExpressionWrapper(
models.F("pages"),
output_field=models.IntegerField(),
),
models.OrderBy(models.F("name").desc()),
name="complex_func_index",
)
string, imports = MigrationWriter.serialize(index)
self.assertEqual(
string,
"models.Index(models.Func('rating', function='ABS'), "
"models.Case(models.When(name='special', then=models.Value('X')), "
"default=models.Value('other')), "
"models.ExpressionWrapper("
"models.F('pages'), output_field=models.IntegerField()), "
"models.OrderBy(models.OrderBy(models.F('name'), descending=True)), "
"name='complex_func_index')",
)
self.assertEqual(imports, {"from django.db import models"})
def viral_video_detail(request, id):
yesterday = datetime.date.today() - datetime.timedelta(days=1)
qs = ViralVideo.objects.annotate(
total_impressions=models.F("desktop_impressions") +
models.F("mobile_impressions"),
label=models.Case(
models.When(total_impressions__gt=POPULAR_FROM,
then=models.Value("popular")),
models.When(created__gt=yesterday, then=models.Value("new")),
default=models.Value("cool"),
output_field=models.CharField(),
),
)
# DEBUG: check the SQL query that Django ORM generates
print(qs.query)
qs = qs.filter(pk=id)
if request.flavour == "mobile":
qs.update(mobile_impressions=models.F("mobile_impressions") + 1)
else:
qs.update(desktop_impressions=models.F("desktop_impressions") + 1)
video = get_object_or_404(qs)
return render(request, "viral_videos/viral_video_detail.html",
{'video': video})
def viral_video_detail(request, pk):
yesterday = datetime.date.today() - datetime.timedelta(days=1)
qs = ViralVideo.objects.annotate(
total_views=models.F("authenticated_views") +
models.F("anonymous_views"),
label=models.Case(models.When(total_views__gt=POPULAR_FROM,
then=models.Value("popular")),
models.When(created__gt=yesterday,
then=models.Value("new")),
default=models.Value("cool"),
output_field=models.CharField()))
# DEBUG: check the SQL query that Django ORM generates
logger.debug(f"Query: {qs.query}")
qs = qs.filter(pk=pk)
if request.user.is_authenticated:
qs.update(authenticated_views=models.F("authenticated_views") + 1)
else:
qs.update(anonymous_views=models.F("anonymous_views") + 1)
video = get_object_or_404(qs)
return render(request, "viral_videos/viral_video_detail.html",
{'video': video})
def populate_status_field(apps, schema):
RSVP = apps.get_model("events", "RSVP")
RSVP.objects.update(status=models.Case(
models.When(canceled=True, then=models.Value("CA")),
default=models.Value("CO"),
))
def review_order(self):
review_order = [
STAFF_GROUP_NAME,
COMMUNITY_REVIEWER_GROUP_NAME,
REVIEWER_GROUP_NAME,
]
ordering = [
models.When(type__name=review_type, then=models.Value(i))
for i, review_type in enumerate(review_order)
]
return self.exclude(
# Remove people from the list who are opinionated but
# didn't review, they appear elsewhere
opinions__isnull=False,
review__isnull=True,
).annotate(type_order=models.Case(
*ordering,
output_field=models.IntegerField(),
),
has_review=models.Case(
models.When(review__isnull=True, then=models.Value(1)),
models.When(review__is_draft=True,
then=models.Value(1)),
default=models.Value(0),
output_field=models.IntegerField(),
)).order_by(
'type_order',
'has_review',
F('role__order').asc(nulls_last=True),
).select_related(
'reviewer',
'role',
)
def __next_tree_path_qx(self, prefix=()):
return DjPgTreeNext(
models.Value(self._meta.db_table),
models.Value(".".join(prefix)),
GAP,
PAD_LENGTH,
)
def populate_canceled_field(apps, schema):
RSVP = apps.get_model("events", "RSVP")
RSVP.objects.update(canceled=models.Case(
models.When(status="CO", then=models.Value(False)),
default=models.Value(True),
))
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 get_context(self, request, *args, **kwargs):
context = super().get_context(request, *args, **kwargs)
# Get all events to display on an event index page.
# Apply ascending order by start_date to upcoming events,
# but for past events apply descending ordering by start_date.
date_now = timezone.now().date()
events = self._annotated_descendant_events()
events = events.annotate(upcoming_order=models.Case(
models.When(latest_date__gte=date_now, then='start_date'),
default=models.Value(None),
output_field=models.DateField()))
events = events.annotate(past_order=models.Case(
models.When(latest_date__lt=date_now, then='start_date'),
default=models.Value(None),
output_field=models.DateField()))
events = events.order_by('upcoming_order', '-past_order')
paginator = Paginator(events, settings.DEFAULT_PER_PAGE)
try:
events = paginator.page(request.GET.get('page'))
except PageNotAnInteger:
events = paginator.page(1)
except EmptyPage:
events = paginator.page(paginator.num_pages)
context.update({
'events': events,
'sidebar_pages': self.get_siblings().live().public(),
})
context.update(get_adjacent_pages(paginator, events.number))
return context
def __init__(self, request, preselected_task_id=None, *args, **kwargs):
# https://stackoverflow.com/questions/291945/how-do-i-filter-foreignkey-choices-in-a-django-modelform
super(CompleteTaskForm, self).__init__(*args,
**kwargs) # populates the post
available_tasks = Task.objects.filter(id__in=[
task.id for task in Task.objects.all()
if task.can_be_completed_today(request.user)
])
if available_tasks.count() == 0:
messages.info(request,
f"Nie ma żadnych zadań, które możesz dziś wykonać")
fav_tasks = [
fav_task.task.id
for fav_task in FavouriteTask.objects.filter(user=request.user)
]
available_tasks = available_tasks.annotate(custom_order=models.Case(
models.When(id=preselected_task_id, then=models.Value(0)),
models.When(id__in=fav_tasks, then=models.Value(1)),
default=models.Value(2),
output_field=models.IntegerField())).order_by('custom_order')
self.fields['task'].queryset = available_tasks
if preselected_task_id:
self.initial['task'] = get_object_or_404(Task,
id=preselected_task_id)
def subscrib_sms_forward(apps, schema):
Person = apps.get_model("people", "Person")
Person.objects.update(subscribed_sms=models.Case(
models.When(subscribed=True, then=models.Value(True)),
default=models.Value(False),
))
def with_role(self, community):
"""Adds annotations "role" and "role_display" for users for this community.
Use in conjunction with for_community.
Args:
community (Community)
Returns:
QuerySet
"""
return self.annotate(
role=models.Subquery(
Membership.objects.filter(
community=community,
member=models.OuterRef("pk")).values("role"),
output_field=models.CharField(),
),
role_display=models.Case(
*[
models.When(role=k, then=models.Value(str(v)))
for k, v in Membership.Role.choices
],
default=models.Value(""),
output_field=models.CharField(),
),
)
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
chapter = self.request.user.current_chapter
qs = self.get_queryset()
qs = qs.annotate(complete_link=models.Case(
models.When(models.Q(
chapters__chapter=chapter), models.F("chapters__pk")),
default=models.Value(0),
)).annotate(complete_result=models.Case(
models.When(models.Q(
chapters__chapter=chapter), models.Value("True")),
default=models.Value(""),
output_field=models.CharField(),
))
# Annotate is duplicating things
qs = qs.distinct()
# Distinct sees incomplete/complete as different, so need to combine
complete = qs.filter(complete_result=True)
incomplete = qs.filter(~models.Q(pk__in=complete), complete_result="")
all_tasks = complete | incomplete
table = TaskTable(data=all_tasks)
table.request = self.request
RequestConfig(self.request, paginate={"per_page": 40}).configure(table)
context["table"] = table
return context
def filter_postcode(self, queryset, field_name, value) -> models.QuerySet:
"""Finds postcodes starting the same way, while removing spaces from the search and target"""
value = re.sub(r'\s+', '', value)
return queryset.annotate(
trimmed_postcode=Replace('default_address__postcode', models.Value(' '), models.Value(''))
).filter(
trimmed_postcode__startswith=value,
)
def reviews(self):
"""Return user reviews sorted by creation date."""
return self.review_set.annotate(
sum_votes=models.functions.Coalesce(models.Sum('vote__value'),
models.Value(0)),
user_vote=models.functions.Coalesce(
models.Sum('vote__value', filter=models.Q(vote__user=self)),
models.Value(0))).order_by('-created')
def update_remedy_script(self, script, heading=False):
if heading:
v = models.Value(script + '\n')
c = models.functions.Concat(v, 'remedy_script_todo')
else:
v = models.Value('\n' + script)
c = models.functions.Concat('remedy_script_todo', v)
self.__class__.objects.filter(pk=self.pk).update(remedy_script_todo=c)
def order_by_alphanumeric(self):
"""Sort the Tag by name's alphabetic chars and then by numeric chars."""
return self.annotate(
tag_name=Substring(models.F('name'), models.Value('[a-zA-Zа-яА-Я\s\-_,:;]+')),
tag_value=models.functions.Cast(
Substring(models.F('name'), models.Value('[0-9]+\.?[0-9]*')),
models.FloatField(),
)).order_by('group__position', 'group__name', 'tag_name', 'tag_value')
def get_annotation(self, cls):
from django.db.models.functions import Concat
return Concat('major',
models.Value('.'),
'minor',
models.Value('.'),
'patch',
output_field=models.CharField())
