def _get_queryset(self, cl, form_data):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk
).order_by().values('position_id').annotate(
m=Max('datetime')
).values('m')
qs = OrderPosition.objects.filter(
order__event=self.event,
).annotate(
last_checked_in=Subquery(cqs),
auto_checked_in=Exists(
Checkin.objects.filter(position_id=OuterRef('pk'), list_id=cl.pk, auto_checked_in=True)
)
).prefetch_related(
'answers', 'answers__question', 'addon_to__answers', 'addon_to__answers__question'
).select_related('order', 'item', 'variation', 'addon_to', 'order__invoice_address', 'voucher', 'seat')
if not cl.all_products:
qs = qs.filter(item__in=cl.limit_products.values_list('id', flat=True))
if cl.subevent:
qs = qs.filter(subevent=cl.subevent)
if form_data['sort'] == 'name':
qs = qs.order_by(
Coalesce(
NullIf('attendee_name_cached', Value('')),
NullIf('addon_to__attendee_name_cached', Value('')),
NullIf('order__invoice_address__name_cached', Value('')),
'order__code'
)
)
elif form_data['sort'] == 'code':
qs = qs.order_by('order__code')
elif form_data['sort'].startswith('name:'):
part = form_data['sort'][5:]
qs = qs.annotate(
resolved_name=Case(
When(attendee_name_cached__ne='', then='attendee_name_parts'),
When(addon_to__attendee_name_cached__isnull=False, addon_to__attendee_name_cached__ne='', then='addon_to__attendee_name_parts'),
default='order__invoice_address__name_parts',
)
).annotate(
resolved_name_part=JSONExtract('resolved_name', part)
).order_by(
'resolved_name_part'
)
if form_data.get('attention_only'):
qs = qs.filter(Q(item__checkin_attention=True) | Q(order__checkin_attention=True))
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID, Order.STATUS_PENDING))
return qs
class SearchContactExportAPIView(SearchContactAPIViewMixin,
SearchExportAPIView):
"""Company search export view."""
db_sort_by_remappings = {
'address_country.name': 'computed_country_name',
}
queryset = DBContact.objects.annotate(
name=get_full_name_expression(),
link=get_front_end_url_expression('contact', 'pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
company_link=get_front_end_url_expression('company', 'company__pk'),
computed_country_name=Case(
When(address_same_as_company=True,
then='company__address_country__name'),
default='address_country__name',
),
computed_postcode=Case(
When(address_same_as_company=True,
then='company__address_postcode'),
default='address_postcode',
),
full_telephone_number=ConcatWS(
Value(' '),
NullIf('telephone_countrycode', Value('')),
NullIf('telephone_number', Value('')),
),
date_of_latest_interaction=get_aggregate_subquery(
DBContact,
Max('interactions__date'),
),
team_of_latest_interaction=get_top_related_expression_subquery(
DBInteraction.contacts.field,
F('dit_team__name'),
('-date', ),
),
)
field_titles = {
'name': 'Name',
'job_title': 'Job title',
'created_on': 'Date created',
'archived': 'Archived',
'link': 'Link',
'company__name': 'Company',
'company_sector_name': 'Company sector',
'company_link': 'Company link',
'company__uk_region__name': 'Company UK region',
'computed_country_name': 'Country',
'computed_postcode': 'Postcode',
'full_telephone_number': 'Phone number',
'email': 'Email address',
'accepts_dit_email_marketing': 'Accepts DIT email marketing',
'date_of_latest_interaction': 'Date of latest interaction',
'team_of_latest_interaction': 'Team of latest interaction',
'created_by__dit_team__name': 'Created by team',
}
def get_bracketed_concat_expression(*expressions, expression_to_bracket=None):
"""
Gets an SQL expression that concatenates a number of expressions and optionally another
field surrounded by brackets.
For simple annotation of full names of contacts or advisers, get_full_name_expression() should
be preferred. However, this function can handle other or more complex scenarios.
Usage examples:
# Effectively '{Contact.first_name} {Contact.last_name}'
Contact.objects.annotate(
name=get_bracketed_concat_expression('first_name', 'last_name'),
)
# Effectively '{Contact.first_name} {Contact.last_name} ({Contact.job_title})'
# (but the job title would be omitted if blank or NULL)
Contact.objects.annotate(
name=get_bracketed_concat_expression(
'first_name',
'last_name',
expression_to_bracket='job_title',
),
)
# Effectively '{Interaction.dit_adviser.first_name} {Interaction.dit_adviser.last_name}'
Interaction.objects.annotate(
dit_adviser_name=get_bracketed_concat_expression(
'dit_adviser__first_name',
'dit_adviser__last_name',
),
)
"""
parts = [
NullIf(field, Value('', output_field=CharField()))
for field in expressions
]
if expression_to_bracket:
bracketed_expression = PreferNullConcat(
Value('('),
NullIf(expression_to_bracket, Value('', output_field=CharField())),
Value(')'),
)
parts.append(bracketed_expression)
return ConcatWS(Value(' ', output_field=CharField()),
*parts,
output_field=CharField())
def _full_name_concat(first_name_field, last_name_field, bracketed_field=None):
parts = [
NullIf(first_name_field, Value('')),
NullIf(last_name_field, Value('')),
]
if bracketed_field:
bracketed_expression = PreferNullConcat(
Value('('),
NullIf(bracketed_field, Value('')),
Value(')'),
)
parts.append(bracketed_expression)
return ConcatWS(Value(' '), *parts)
def get_field(self):
return Coalesce(
Cast('debateteam__teamscore__votes_given', FloatField()) /
NullIf('debateteam__teamscore__votes_possible',
0,
output_field=FloatField()) * self.adjs_per_debate,
Value(0.0))
def annotate_bookmarks(self, obj_list):
"""Hoist up bookmark annoations."""
ub_filter = self.get_userbookmark_filter()
# Hoist up: are the children finished or unfinished?
finished_aggregate = Cast(
NullIf(
Coalesce(
Avg( # distinct average of user's finished values
"comic__userbookmark__finished",
filter=ub_filter,
distinct=True,
output_field=DecimalField(),
),
False, # Null db values counted as False
),
Value(0.5), # Null result if mixed true & false
),
BooleanField(), # Finally ends up as a ternary boolean
)
# Hoist up the bookmark
bookmark_sum = Sum("comic__userbookmark__bookmark", filter=ub_filter)
obj_list = obj_list.annotate(finished=finished_aggregate, bookmark=bookmark_sum)
return obj_list
def test_null_literal(self):
msg = "Oracle does not allow Value(None) for expression1."
with self.assertRaisesMessage(ValueError, msg):
list(
Author.objects.annotate(nullif=NullIf(Value(None), "name")).values_list(
"nullif"
)
)
def filter(self, qs, value):
if value and len(value) == 1 and value[0] in self.null_last_fields:
qs = qs.annotate(
**{
f'{value[0]}__nullif': NullIf(f'{value[0]}', Value(''))
}).order_by(f'{value[0]}__nullif')
return qs
return super().filter(qs, value)
def test_basic(self):
authors = Author.objects.annotate(
nullif=NullIf('alias', 'name')).values_list('nullif')
self.assertSequenceEqual(
authors,
[('smithj', ),
('' if connection.features.interprets_empty_strings_as_nulls else
None, )])
def form_filter_queryset(self):
"""
In our filter forms, we want to display the sectors by group.
"""
return (self.select_related("group").exclude(
group=None) # sector must have a group !
.annotate(
sector_is_autre=NullIf("name", Value("Autre"))).order_by(
"group__id", "sector_is_autre"))
def get_other_field_if_null_or_empty_expression(field_a, field_b, default=None):
"""
Get other field_b value if field_a is null or empty.
If field_b is null, return empty string.
"""
return Coalesce(
NullIf(field_a, Value('')),
field_b,
default,
)
def test_basic(self):
authors = Author.objects.annotate(nullif=NullIf("alias", "name")).values_list(
"nullif"
)
self.assertCountEqual(
authors,
[
("smithj",),
(
""
if connection.features.interprets_empty_strings_as_nulls
else None,
),
],
)
def _add_formal_type_ids(self, qs):
# define the integer pubchem_cid field as CharField
cast = Cast(
"measurement__measurement_type__metabolite__pubchem_cid",
output_field=CharField(),
)
prefix = Value("cid:", output_field=CharField())
# instruct database to give PubChem ID in the cid:N format, or None
qs = qs.annotate(anno_pubchem=NullIf(Concat(prefix, cast), prefix))
# grab formal type IDs if able, otherwise empty string
qs = qs.annotate(anno_formal_type=Coalesce(
"anno_pubchem",
"measurement__measurement_type__proteinidentifier__accession_id",
Value("", output_field=CharField()),
output_field=CharField(),
))
return qs
def as_sql(self, compiler, connection):
language = translation.get_language()
fallback_config = getattr(settings, "LOCALIZED_FIELDS_FALLBACKS", {})
target_languages = fallback_config.get(language, [])
if not target_languages and language != settings.LANGUAGE_CODE:
target_languages.append(settings.LANGUAGE_CODE)
if language:
target_languages.insert(0, language)
if len(target_languages) > 1:
return Coalesce(*[
NullIf(KeyTransform(language, self.lhs), Value(""))
for language in target_languages
]).as_sql(compiler, connection)
return KeyTransform(target_languages[0],
self.lhs).as_sql(compiler, connection)
def employer_median_salaries(self):
if not hasattr(self, '_employer_median_salaries'):
median_base_pay = Percentile('positions__jobs__salaries__amount',
0.5,
filter=self.salary_q,
output_field=FloatField())
median_extra_pay = Percentile(
'positions__jobs__salaries__extra_pay',
0.5,
filter=self.salary_q,
output_field=FloatField())
median_total_pay = Percentile(NullIf(
Coalesce('positions__jobs__salaries__amount', 0) +
Coalesce('positions__jobs__salaries__extra_pay', 0), 0),
0.5,
filter=self.salary_q,
output_field=FloatField())
self._employer_median_salaries = self.employer_queryset.aggregate(
median_base_pay=Coalesce(median_base_pay, 0),
median_extra_pay=Coalesce(median_extra_pay, 0),
median_total_pay=Coalesce(median_total_pay, 0))
return self._employer_median_salaries
def test_too_few_args(self):
msg = "'NullIf' takes exactly 2 arguments (1 given)"
with self.assertRaisesMessage(TypeError, msg):
NullIf("name")
def get(self, request, *args, **kwargs):
# -----> ACTUAL SIZE <-------
base = BasePart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(total=Sum('lines_current_base'))
added = BasePart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(total=Sum('lines_current_added'))
deleted = BasePart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(
total=Sum('lines_current_deleted'))
reused = ReusedPart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(total=Sum('current_lines'))
self.data["actual_size"] = Program.objects.values('pk', 'name').filter(
programmer=self.user,
finish_date__isnull=False).annotate(total=Coalesce(
Subquery(base.values('total')) -
Subquery(deleted.values('total')) +
Subquery(reused.values('total')) +
Subquery(added.values('total')), 0))
# -----> END ACTUAL SIZE <------
# -----> Defects Removed <--------
self.data["defects_removed"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False).annotate(
planning=Count('name',
Q(get_defects__phase_removed__name='Planning')),
design=Count('name',
Q(get_defects__phase_removed__name='Design')),
design_review=Count(
'name',
Q(get_defects__phase_removed__name='Design Review')),
codification=Count(
'name',
Q(get_defects__phase_removed__name='Codification')),
codification_review=Count(
'name',
Q(get_defects__phase_removed__name='Codification Review')),
compilation=Count(
'name', Q(get_defects__phase_removed__name='Compilation')),
unit_test=Count(
'name', Q(get_defects__phase_removed__name='Unit Test')),
postmortem=Count(
'name',
Q(get_defects__phase_removed__name='Postmortem'))).values(
'pk', 'name', 'planning', 'design', 'design_review',
'codification', 'codification_review', 'compilation',
'unit_test', 'postmortem').order_by('created_at')
# -----> END Defects removed <------
# -----> Defects Injected <-------
self.data["defects_injected"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False
).annotate(
planning=Count('name',
Q(get_defects__phase_injected__name='Planning')),
design=Count('name',
Q(get_defects__phase_injected__name='Design')),
design_review=Count(
'name', Q(get_defects__phase_injected__name='Design Review')),
codification=Count(
'name', Q(get_defects__phase_injected__name='Codification')),
codification_review=Count(
'name',
Q(get_defects__phase_injected__name='Codification Review')),
compilation=Count(
'name', Q(get_defects__phase_injected__name='Compilation')),
unit_test=Count('name',
Q(get_defects__phase_injected__name='Unit Test')),
postmortem=Count(
'name',
Q(get_defects__phase_injected__name='Postmortem'))).values(
'pk', 'name', 'planning', 'design', 'design_review',
'codification', 'codification_review', 'compilation',
'unit_test', 'postmortem').order_by('created_at')
# ------> END Defects injected <------
# -----> Total time <--------
self.data["total_time"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False).annotate(
total=TIME_TOTAL_BY_PROGRAM).values(
'pk', 'name', 'total').order_by('created_at')
# -----> END total time <------
# -----> Failure COQ <--------
# Hago dos subconsultas que retornan el tiempo dedicado en la fase de compilación y de pruebas
timelogComp = TimeLog.objects.filter(
program__pk=OuterRef("pk"), phase__name='Compilation').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
timelogUnit = TimeLog.objects.filter(
program__pk=OuterRef("pk"), phase__name='Unit Test').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
self.data["failure_COQ"] = Program.objects.filter(
programmer=self.user,
finish_date__isnull=False).annotate(total=Coalesce(
100 * ((Coalesce(Subquery(timelogComp), 0) +
Coalesce(Subquery(timelogUnit), 0)) /
NullIf(TIME_TOTAL_BY_PROGRAM, 0)), 0)).values(
'pk', 'name', 'total').order_by('created_at')
# ------> END Failure COQ <------
# ------> Appraisal Cost Of Quality <---------
# Hago dos subconsultas que retornan el tiempo dedicado en la fase de revisión de diseño y revisión de código
timelog_design_review = TimeLog.objects.filter(
program__pk=OuterRef("pk"), phase__name='Design Review').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
timelog_code_review = TimeLog.objects.filter(
program__pk=OuterRef("pk"),
phase__name='Codification Review').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
self.data["appraisal_COQ"] = Program.objects.filter(
programmer=self.user,
finish_date__isnull=False).annotate(total=Coalesce(
100 * ((Coalesce(Subquery(timelog_design_review), 0) +
Coalesce(Subquery(timelog_code_review), 0)) /
NullIf(TIME_TOTAL_BY_PROGRAM, 0)), 0)).values(
'pk', 'name', 'total').order_by('created_at')
# -------> END Total defects <-------
# ------> Total defects <---------
self.data["total_defects"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False).annotate(
total=Count('get_defects')).values('pk', 'name', 'total')
# ------> END total defects <-------
return Response(data=self.data, status=status.HTTP_200_OK)
def test_null_argument(self):
authors = Author.objects.annotate(
nullif=NullIf('name', Value(None))).values_list('nullif')
self.assertSequenceEqual(authors, [('John Smith', ), ('Rhonda', )])
class SearchContactExportAPIView(SearchContactAPIViewMixin,
SearchExportAPIView):
"""Company search export view."""
def _is_valid_email(self, value):
"""Validate if emails are valid and return a boolean flag."""
try:
validate_email(value)
return True
except ValidationError:
return False
consent_page_size = 100
db_sort_by_remappings = {
'address_country.name': 'computed_country_name',
}
queryset = DBContact.objects.annotate(
name=get_full_name_expression(),
link=get_front_end_url_expression('contact', 'pk'),
company_sector_name=get_sector_name_subquery('company__sector'),
company_link=get_front_end_url_expression('company', 'company__pk'),
computed_country_name=Case(
When(address_same_as_company=True,
then='company__address_country__name'),
default='address_country__name',
),
computed_postcode=Case(
When(address_same_as_company=True,
then='company__address_postcode'),
default='address_postcode',
),
full_telephone_number=ConcatWS(
Value(' '),
NullIf('telephone_countrycode', Value('')),
NullIf('telephone_number', Value('')),
),
date_of_latest_interaction=get_aggregate_subquery(
DBContact,
Max('interactions__date'),
),
teams_of_latest_interaction=get_top_related_expression_subquery(
DBInteraction.contacts.field,
get_string_agg_subquery(DBInteraction,
'dit_participants__team__name',
distinct=True),
('-date', ),
),
)
field_titles = {
'name': 'Name',
'job_title': 'Job title',
'created_on': 'Date created',
'archived': 'Archived',
'link': 'Link',
'company__name': 'Company',
'company_sector_name': 'Company sector',
'company_link': 'Company link',
'company__uk_region__name': 'Company UK region',
'computed_country_name': 'Country',
'computed_postcode': 'Postcode',
'full_telephone_number': 'Phone number',
'email': 'Email address',
'accepts_dit_email_marketing': 'Accepts DIT email marketing',
'date_of_latest_interaction': 'Date of latest interaction',
'teams_of_latest_interaction': 'Teams of latest interaction',
'created_by__dit_team__name': 'Created by team',
}
def _add_consent_response(self, rows):
"""
Transforms iterable to add user consent from the consent service.
The consent lookup makes an external API call to return consent.
For perfromance reasons the consent amount is limited by consent_page_size.
Due to this limitaion the iterable are sliced into chunks requesting consent for 100 rows
at a time.
"""
# Slice iterable into chunks
row_chunks = slice_iterable_into_chunks(rows, self.consent_page_size)
for chunk in row_chunks:
"""
Loop over the chunks and extract the email and item.
Save the item because the iterator cannot be used twice.
"""
rows = list(chunk)
# Peform constent lookup on emails POST request
consent_lookups = consent.get_many([
row['email']
for row in rows if self._is_valid_email(row['email'])
], )
for row in rows:
# Assign contact consent boolean to accepts_dit_email_marketing
# and yield modified result.
row['accepts_dit_email_marketing'] = consent_lookups.get(
row['email'], False)
yield row
def _get_rows(self, ids, search_ordering):
"""
Get row queryset for constent service.
This populates accepts_dit_email_marketing field from the consent service and
removes the accepts_dit_email_marketing from the field query because the field is not in
the db.
"""
db_ordering = self._translate_search_ordering_to_django_ordering(
search_ordering)
field_titles = self.field_titles.copy()
del field_titles['accepts_dit_email_marketing']
rows = self.queryset.filter(pk__in=ids, ).order_by(
*db_ordering, ).values(*field_titles, ).iterator()
return self._add_consent_response(rows)
def test_null_argument(self):
authors = Author.objects.annotate(
nullif=NullIf("name", Value(None))
).values_list("nullif")
self.assertCountEqual(authors, [("John Smith",), ("Rhonda",)])
def _get_queryset(self, cl, form_data):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')
cqsin = cqs.filter(type=Checkin.TYPE_ENTRY)
cqsout = cqs.filter(type=Checkin.TYPE_EXIT)
qs = OrderPosition.objects.filter(order__event=self.event, ).annotate(
last_checked_in=Subquery(cqsin),
last_checked_out=Subquery(cqsout),
auto_checked_in=Exists(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk,
auto_checked_in=True))).prefetch_related(
'answers', 'answers__question', 'addon_to__answers',
'addon_to__answers__question').select_related(
'order', 'item', 'variation', 'addon_to',
'order__invoice_address', 'voucher', 'seat')
if not cl.all_products:
qs = qs.filter(
item__in=cl.limit_products.values_list('id', flat=True))
if cl.subevent:
qs = qs.filter(subevent=cl.subevent)
if form_data.get('date_from'):
dt = make_aware(
datetime.combine(
dateutil.parser.parse(form_data['date_from']).date(),
time(hour=0, minute=0, second=0)), self.event.timezone)
qs = qs.filter(subevent__date_from__gte=dt)
if form_data.get('date_to'):
dt = make_aware(
datetime.combine(
dateutil.parser.parse(form_data['date_to']).date() +
timedelta(days=1), time(hour=0, minute=0, second=0)),
self.event.timezone)
qs = qs.filter(subevent__date_from__lt=dt)
o = ()
if self.event.has_subevents and not cl.subevent:
o = ('subevent__date_from', 'subevent__name')
sort = form_data.get('sort') or 'name'
if sort == 'name':
qs = qs.order_by(
*o,
Coalesce(
NullIf('attendee_name_cached', Value('')),
NullIf('addon_to__attendee_name_cached', Value('')),
NullIf('order__invoice_address__name_cached', Value('')),
'order__code'))
elif sort == 'code':
qs = qs.order_by(*o, 'order__code')
elif sort.startswith('name:'):
part = sort[5:]
qs = qs.annotate(resolved_name=Case(
When(attendee_name_cached__ne='', then='attendee_name_parts'),
When(addon_to__attendee_name_cached__isnull=False,
addon_to__attendee_name_cached__ne='',
then='addon_to__attendee_name_parts'),
default='order__invoice_address__name_parts',
)).annotate(resolved_name_part=JSONExtract(
'resolved_name', part)).order_by(*o, 'resolved_name_part')
if form_data.get('attention_only'):
qs = qs.filter(
Q(item__checkin_attention=True)
| Q(order__checkin_attention=True))
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID,
Order.STATUS_PENDING))
return qs