def _remove_rcce_tag(Project):
"""
Remove RCCE tag and change it to combination of CEA and HEALTH_PUBLIC
"""
# Hardcode enum value present this time
RCCE = 14
HEALTH_PUBLIC = 4
CEA = 2
# Add HEALTH_PUBLIC/CEA if there is RCCE
for tag in [HEALTH_PUBLIC, CEA]:
Project.objects\
.filter(secondary_sectors__contains=[RCCE])\
.exclude(secondary_sectors__contains=[tag])\
.update(
secondary_sectors=Func(
F('secondary_sectors'),
Value(tag),
function='ARRAY_APPEND',
)
)
# Finally remove
Project.objects.filter(secondary_sectors__contains=[RCCE]).update(
secondary_sectors=Func(
F('secondary_sectors'), Value(RCCE), function='ARRAY_REMOVE'))
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
params = self.request.GET.copy()
now = datetime.now()
params.setdefault('hour', now.hour)
params.setdefault('weekday', now.weekday())
params.setdefault('lng', -73.989774)
params.setdefault('lat', 40.752336)
hour = params.get('hour')
weekday = params.get('weekday')
lat = float(params.get('lat'))
lng = float(params.get('lng'))
point = Point(lng, lat, srid=4326)
recommendations = (Recommendation.objects.filter(
hour=hour, weekday=weekday).annotate(
distance=Distance('poly', point)).annotate(point=Func(
F('poly'),
Func(Value(str(point)), function='ST_PointFromText'),
function='ST_ClosestPoint')).filter(
distance__lte=measure.Distance(
mi=5).m)).order_by('-score')[:10]
context['recommendations'] = [list(r.point) for r in recommendations]
context['form'] = RecommendationForm(data=params)
return context
def longrunning_filter(self, queryset, name, value):
if value:
return (
queryset.exclude(firefox_max_version__exact="")
.annotate(
firefox_min_int=Cast(
Func(
F("firefox_min_version"),
Value(ExperimentConstants.VERSION_REGEX.pattern),
function="substring",
),
IntegerField(),
),
firefox_max_int=Cast(
Func(
F("firefox_max_version"),
Value(ExperimentConstants.VERSION_REGEX.pattern),
function="substring",
),
IntegerField(),
),
version_count=F("firefox_max_int") - F("firefox_min_int"),
)
.filter(version_count__gte=3)
)
return queryset
def get_context_data(self, **kwargs):
context = super(InvoiceEntriesDetail, self).get_context_data(**kwargs)
billable_entries = context['billable_entries']
nonbillable_entries = context['nonbillable_entries']
context.update({
'billable_total':
billable_entries.aggregate(hours=Sum(
Func(F('hours'), Value(2), function='ROUND')))['hours'],
'nonbillable_total':
nonbillable_entries.aggregate(hours=Sum(
Func(F('hours'), Value(2), function='ROUND')))['hours'],
})
return context
def hitung_auditi_inspektorat(self, inspektorat):
tgl_sekarang = datetime.now()
data_auditi = list()
bulan_sekarang = tgl_sekarang.month
tahun_sekarang = tgl_sekarang.year
jml_auditi = super()\
.filter(assign_jenis_kegiatan=1,
assignment_st__assign_surat_id_assign__assignment_auditi__assign_auditee_id_auditee_id__gt='',
assignment_st__assign_surat_id_assign__assignment_auditi__assign_auditee_id_auditee__auditee_inspektorat=inspektorat.value)\
.annotate(bulan=Func(Func(F('assignment_st__assign_surat_tgl'), function='FROM_UNIXTIME'), function='MONTH'),
tahun=Func(Func(F('assignment_st__assign_surat_tgl'), function='FROM_UNIXTIME'), function='YEAR'))\
.values('bulan')
if bulan_sekarang < 12:
jml_auditi_st_sebelumnya = jml_auditi.filter(tahun=tahun_sekarang - 1)\
.annotate(total=Count('bulan'))\
.order_by('bulan')
auditi_from_model = dict()
for data in jml_auditi_st_sebelumnya:
bulan = data.get('bulan')
auditi_from_model.update({
bulan: data.get('total'),
})
for i in range(bulan_sekarang + 1, 13):
if auditi_from_model.get(i) is not None:
data_auditi.append(auditi_from_model.get(i))
else:
data_auditi.append(0)
jml_auditi_st = jml_auditi.filter(tahun=tahun_sekarang) \
.annotate(total=Count('bulan')).order_by('bulan')
auditi_from_model = dict()
for data in jml_auditi_st:
bulan = data.get('bulan')
auditi_from_model.update({
bulan: data.get('total'),
})
for i in range(1, bulan_sekarang + 1):
if auditi_from_model.get(i) is not None:
data_auditi.append(auditi_from_model.get(i))
else:
data_auditi.append(0)
return data_auditi
def resolve_expression(
self,
query=None,
allow_joins=True,
reuse=None,
summarize=False,
for_save=False,
):
expressions = list(self.flatten())
# Split expressions and wrappers.
index_expressions, wrappers = partition(
lambda e: isinstance(e, self.wrapper_classes),
expressions,
)
wrapper_types = [type(wrapper) for wrapper in wrappers]
if len(wrapper_types) != len(set(wrapper_types)):
raise ValueError(
"Multiple references to %s can't be used in an indexed "
"expression."
% ", ".join(
[wrapper_cls.__qualname__ for wrapper_cls in self.wrapper_classes]
)
)
if expressions[1 : len(wrappers) + 1] != wrappers:
raise ValueError(
"%s must be topmost expressions in an indexed expression."
% ", ".join(
[wrapper_cls.__qualname__ for wrapper_cls in self.wrapper_classes]
)
)
# Wrap expressions in parentheses if they are not column references.
root_expression = index_expressions[1]
resolve_root_expression = root_expression.resolve_expression(
query,
allow_joins,
reuse,
summarize,
for_save,
)
if not isinstance(resolve_root_expression, Col):
root_expression = Func(root_expression, template="(%(expressions)s)")
if wrappers:
# Order wrappers and set their expressions.
wrappers = sorted(
wrappers,
key=lambda w: self.wrapper_classes.index(type(w)),
)
wrappers = [wrapper.copy() for wrapper in wrappers]
for i, wrapper in enumerate(wrappers[:-1]):
wrapper.set_source_expressions([wrappers[i + 1]])
# Set the root expression on the deepest wrapper.
wrappers[-1].set_source_expressions([root_expression])
self.set_source_expressions([wrappers[0]])
else:
# Use the root expression, if there are no wrappers.
self.set_source_expressions([root_expression])
return super().resolve_expression(
query, allow_joins, reuse, summarize, for_save
)
def _add_quality_relevance(self, qs):
"""Annotates query with relevance based on quality score.
It is calculated by a formula:
R = log(Q + 1) + 0.1 * v
Where:
R - Relevance;
Q - Quality score (0 to 5);
v - 1 if collection belongs to a partner namespace, otherwise 0.
"""
quality_rank_expr = (
Func(Coalesce(F('latest_version__quality_score'), 0) + 1,
function='log') * CONTENT_SCORE_MULTIPLIER)
vendor_rank_expr = Case(
When(namespace__is_vendor=True, then=Value(VENDOR_RANK)),
When(namespace__is_vendor=False, then=Value(0)),
)
relevance_expr = F('quality_rank') + F('vendor_rank')
return qs.annotate(
quality_rank=Expr(quality_rank_expr,
output_field=db_fields.FloatField()),
vendor_rank=Expr(vendor_rank_expr,
output_field=db_fields.FloatField()),
relevance=Expr(relevance_expr,
output_field=db_fields.FloatField()),
)
def convert_context_to_csv(self, context):
rows = []
rows.append([
'Date',
'Weekday',
'Name',
'Location',
'Time In',
'Time Out',
'Breaks',
'Hours',
])
for entry in context['billable_entries']:
data = [
entry.start_time.strftime('%x'),
entry.start_time.strftime('%A'),
entry.user.get_name_or_username(),
entry.location,
entry.start_time.strftime('%X'),
entry.end_time.strftime('%X'),
seconds_to_hours(entry.seconds_paused),
"{0:.2f}".format(entry.hours),
]
rows.append(data)
total = context['billable_entries'].aggregate(
hours=Sum(Func(F('hours'), Value(2), function='ROUND')))['hours']
rows.append(
('', '', '', '', '', '', 'Total:', "{0:.2f}".format(total)))
return rows
def test_expressions(self):
self.assertEqual(
repr(Case(When(a=1))),
"<Case: CASE WHEN <Q: (AND: ('a', 1))> THEN Value(None), ELSE Value(None)>"
)
self.assertEqual(repr(Col('alias', 'field')), "Col(alias, field)")
self.assertEqual(repr(Date('published', 'exact')),
"Date(published, exact)")
self.assertEqual(repr(DateTime('published', 'exact', utc)),
"DateTime(published, exact, %s)" % utc)
self.assertEqual(repr(F('published')), "F(published)")
self.assertEqual(repr(F('cost') + F('tax')),
"<CombinedExpression: F(cost) + F(tax)>")
self.assertEqual(
repr(ExpressionWrapper(
F('cost') + F('tax'), models.IntegerField())),
"ExpressionWrapper(F(cost) + F(tax))")
self.assertEqual(repr(Func('published', function='TO_CHAR')),
"Func(F(published), function=TO_CHAR)")
self.assertEqual(repr(OrderBy(Value(1))),
'OrderBy(Value(1), descending=False)')
self.assertEqual(repr(Random()), "Random()")
self.assertEqual(repr(RawSQL('table.col', [])),
"RawSQL(table.col, [])")
self.assertEqual(repr(Ref('sum_cost', Sum('cost'))),
"Ref(sum_cost, Sum(F(cost)))")
self.assertEqual(repr(Value(1)), "Value(1)")
def _create_index_sql(self, model, *, fields=None, **kwargs):
if fields is None or len(fields) != 1 or not hasattr(
fields[0], 'geodetic'):
return super()._create_index_sql(model, fields=fields, **kwargs)
field = fields[0]
expressions = None
opclasses = None
if field.geom_type == 'RASTER':
# For raster fields, wrap index creation SQL statement with ST_ConvexHull.
# Indexes on raster columns are based on the convex hull of the raster.
expressions = Func(Col(None, field),
template=self.rast_index_template)
fields = None
elif field.dim > 2 and not field.geography:
# Use "nd" ops which are fast on multidimensional cases
opclasses = [self.geom_index_ops_nd]
name = kwargs.get('name')
if not name:
name = self._create_index_name(model._meta.db_table,
[field.column], '_id')
return super()._create_index_sql(
model,
fields=fields,
name=name,
using=' USING %s' % self.geom_index_type,
opclasses=opclasses,
expressions=expressions,
)
def test_filter_conditional(self):
query = Query(Item)
where = query.build_where(Func(output_field=BooleanField()))
exact = where.children[0]
self.assertIsInstance(exact, Exact)
self.assertIsInstance(exact.lhs, Func)
self.assertIs(exact.rhs, True)
def _get_search_vector(self):
"""
Gets a `SearchVector` instance containing the rendered search document.
:return: SearchVector
"""
fts_template = getattr(self._meta, 'fts_template', None)
# make sure we have a template to generate the content of the FTS index
if not fts_template:
logger.warning(
"'%(model)s' is FTSMixin instance but has no fts_template assigned."
% {'model': self.__class__.__name__})
return
# render the FTS document from the FTS template
fts_template = get_template(fts_template)
context = {'instance': self}
fts_document = fts_template.render(context)
return SearchVector(Func(Value(fts_document),
function='unaccent',
output_field=TextField()),
config=self.fts_language)
def calculate_total(self, **units):
"""Calculate aggregated totals for the query.
Args:
units (dict): The units dictionary
Returns:
(dict) The aggregated totals for the query
"""
q_table = self._mapper.query_table
query_group_by = ['date'] + self._get_group_by()
query = q_table.objects.filter(self.query_filter)
query_data = query.annotate(**self.annotations)
query_data = query_data.values(*query_group_by)
aggregates = self._mapper.report_type_map.get('aggregates')
counts = None
if 'count' in aggregates:
resource_ids = query_data.annotate(
resource_id=Func(F('resource_ids'), function='unnest')
).values_list('resource_id', flat=True).distinct()
counts = len(resource_ids)
total_query = query.aggregate(**aggregates)
for unit_key, unit_value in units.items():
total_query[unit_key] = unit_value
if counts:
total_query['count'] = counts
self._pack_data_object(total_query, **self._mapper.PACK_DEFINITIONS)
return total_query
def next_id(code_class, contains=None):
'''
Used in the Ajax request to help with creating the next id.
:param code_class: the Collection_id code_class
:param contains: the non numeric part of the id
:return: returns a unique id conforming to the given rules
'''
q1 = Collection_id.objects.filter(code_class=code_class)
if code_class == 'Location ID' and contains:
#should have a 'contains' with the pathogen abbreviation used on the -80 boxes.
if Collection_id.objects.filter(code_txt__icontains=contains).exists():
q1 = q1.filter(code_txt__icontains=contains)
pre = contains + '-'
else:
return ''
elif code_class in ['NBC', 'NFC', 'NVC']:
# this assumes the id to be an Naktuinbouw pathogen id
pre = code_class + '-'
#if code_class == 'NVC':
# The virus from different locations have the same handel.
# New entries should not to be in conflict with previous id's
else:
#should throw a error, other id
return False
q1 = q1.exclude(code_txt__isnull=True).exclude(code_txt__exact='')
q1 = q1.annotate(code_int=Cast(
Func(F('code_txt'),
Value(r'[^\d]'),
Value(''),
Value('g'),
function='regexp_replace'), IntegerField()))
q1 = q1.order_by('-code_int').first()
return pre + str(q1.code_int + 1)
def __init__(self, expression, **extra):
"""
Overridden to check for filtering star expressions.
:param expression: The SQL function expression
:type expression: django.db.models.expressions.Expression | str
:param extra: Additional keyword arguments to pass on
:type extra: any
"""
expression = Star() if expression == "*" else expression
has_filter = "filter" in extra and extra["filter"] is not None
if isinstance(expression, Star) and has_filter:
raise ValueError("Star cannot be used with filter.")
Func.__init__(self, expression, filter=filter, **extra)
def summaries(self, entries):
# Get the list of bundle names and hour sums
bundled_entries = entries.values('activity__activity_bundle',
'activity__activity_bundle__name')
bundled_entries = bundled_entries.annotate(
hours__sum=Sum(Func(F('hours'), Value(2), function='ROUND')))
bundled_entries = bundled_entries.order_by(
'activity__activity_bundle__order',
'activity__activity_bundle__name')
bundled_totals = list(
bundled_entries.values_list(
'activity__activity_bundle__name',
'activity__activity_bundle',
'hours__sum',
))
# Get the list of activity names and hour sums
activity_entries = entries.values('activity', 'activity__name',
'activity__activity_bundle')
activity_entries = activity_entries.annotate(
hours__sum=Sum(Func(F('hours'), Value(2), function='ROUND')))
activity_entries = activity_entries.order_by('activity')
activity_totals = list(
activity_entries.values_list(
'activity__name',
'activity__activity_bundle',
'hours__sum',
))
totals = {}
other_values = ()
for bundle in bundled_totals:
bundle_key, bundle_value = bundle[0], bundle[2]
act_values = [(act[0], act[2]) for act in activity_totals
if act[1] == bundle[1]]
if bundle_key is not None:
totals[bundle_key] = (bundle_value, act_values)
else:
other_values = (bundle_value, act_values)
totals = sorted(totals.items())
if other_values:
totals.append(('Other', other_values))
all_totals = sum([bt[2] for bt in bundled_totals])
totals.append(('Total', (all_totals, [])))
return totals
def as_oracle(self, compiler, connection, **extra_context):
# Oracle prohibits mixing TextField (NCLOB) and CharField (NVARCHAR2),
# so convert all fields to NCLOB when that type is expected.
if self.output_field.get_internal_type() == 'TextField':
clone = self.copy()
clone.set_source_expressions([
Func(expression, function='TO_NCLOB') for expression in self.get_source_expressions()
])
return super(Coalesce, clone).as_sql(compiler, connection, **extra_context)
return self.as_sql(compiler, connection, **extra_context)
def search(self, query):
if not query:
return self
tsquery = Func(Value('russian'),
Value(query),
function='plainto_tsquery')
return self.annotate(
query_size_annotation=Func(tsquery,
function='numnode',
output_field=models.IntegerField()),
found_annotation=Separator(
'@@',
Func(Value('russian'),
F('content'),
function='post_content_to_tsvector'),
tsquery,
output_field=models.BooleanField())).filter(
Q(query_size_annotation=0) | Q(found_annotation=True))
def apply_sorting(self, order_lib):
'''
Applies ordering on the columns and direction as given
the order of importeance is determined by the number after the 'order' flag.
:param order_lib: the columns to sort
:return: changes self.data
'''
order_lib = sorted([(int(k),i) for k,i in order_lib.items()])
order_list = []
for key,value in order_lib:
sort_string = self.columns[value['column']]['data']
sort_list = []
if sort_string in ["id_collectie"]:
str_name = sort_string+'_str'
int_name = sort_string+'_int'
self.data = self.data.annotate(**{
str_name:Func(
F(sort_string),
Value('[^A-Z]'),
Value(''),
Value('g'),
function='regexp_replace')
})
self.data = self.data.annotate(**{
int_name:Cast(Func(
F(sort_string),
Value('[^\d]'),
Value(''),
Value('g'),
function='regexp_replace'
), IntegerField())
})
sort_list.append(str_name)
sort_list.append(int_name)
else:
sort_list.append(sort_string)
if value['dir'] == 'desc':
for item in sort_list:
order_list.append("-" + item)
else:
order_list += sort_list
self.data = self.data.order_by(*order_list)
def _add_keyword_relevance(self, qs):
"""Annotates query with search rank value.
Search rank is calculated as result of `ts_rank` PostgreSQL
function, which ranks vectors based on the frequency of their matching
lexemes. Search rank is normalized by dividing it by itself + 1:
"""
ts_rank_fn = Func(F('search_vector'),
psql_search.SearchQuery(self.filters['keywords']),
RANK_NORMALIZATION,
function=RANK_FUNCTION,
output_field=db_fields.FloatField())
return qs.annotate(relevance=ts_rank_fn)
def calculate_total(self, **units):
"""Calculate aggregated totals for the query.
Args:
units (dict): The units dictionary
Returns:
(dict) The aggregated totals for the query
"""
query_group_by = ["date"] + self._get_group_by()
query = self.query_table.objects.filter(self.query_filter)
query_data = query.annotate(**self.annotations)
query_data = query_data.values(*query_group_by)
aggregates = copy.deepcopy(self._mapper.report_type_map.get("aggregates", {}))
if not self.parameters.parameters.get("compute_count"):
# Query parameter indicates count should be removed from DB queries
aggregates.pop("count", None)
counts = None
if "count" in aggregates:
resource_ids = (
query_data.annotate(resource_id=Func(F("resource_ids"), function="unnest"))
.values_list("resource_id", flat=True)
.distinct()
)
counts = len(resource_ids)
total_query = query.aggregate(**aggregates)
for unit_key, unit_value in units.items():
total_query[unit_key] = unit_value
if counts:
total_query["count"] = counts
self._pack_data_object(total_query, **self._mapper.PACK_DEFINITIONS)
return total_query
def _calculate_value_weights(user, question):
"""
Calculate the weights of the users metadata.
:param user: The current user instance
:type user: accounts.models.User
:type question: The question that is answered
:type question: activities.models.Question
:return: A queryset with the average of the weights
:rtype: django.db.models.query.QuerySet
"""
model = question.__class__
sub_q = model.objects.filter(id=question.id)
sub_q = sub_q.value("weight")
query = MetaData.objects.filter(Q(usermeta__user=user.id))
query = query.values("weight").union(sub_q)
query = query.annotate(__result=Func("weight", function="AVG"))
return query.values("__result")
def filter_by_q(self, queryset, name, value):
"""
Full text search provided by the 'q' option.
Args:
queryset: The query to add the additional full-text search filtering onto
name: The name of the option specified, i.e. 'q'
value: The string to search on
Returns:
The Django queryset that was passed in, additionally filtered by full-text search.
"""
search_query = SearchQuery(value)
qs = queryset.filter(search_vector=search_query)
ts_rank_fn = Func(
F("search_vector"),
search_query,
32, # RANK_NORMALIZATION = 32
function="ts_rank",
output_field=db_fields.FloatField(),
)
return qs.annotate(rank=ts_rank_fn).order_by("-rank")
def get_ids(self, obj):
all_ids = (PdfStorage.objects.annotate(
ids=Func(F("related_ids"), function="unnest")).values_list(
"ids", flat=True).distinct())
return all_ids
def as_sql(self, *args, **kwargs):
return Func.as_sql(self, *args, **kwargs)
def _add_relevance(self, qs):
"""Annotates query with relevance rank and its constituent values.
Relevance is calculated by a formula:
R = Sr + Dr + Qr,
where
R - relevance;
Sr - search rank (from 0 to 1);
Dr - download rank;
Qr - quality rank;
ts_rank()
Sr = -------------
ts_rank() + 1
For more details on search rank see `_add_search_rank` function.
Download rank is calculated by a formula:
ln(cd + 1)
Dr = 0.4 * --------------
ln(cd + 1) + 1
Quality rank is calculated by a formula:
Qr = 0.2 * log(Q + 1)
"""
c = 'repository__community_score'
d = 'repository__download_count'
# ln((MOD*c + MIN) * d + 1)
# where c = community_score and d = download_count
# We're using the community_score as a modifier to the download count
# instead of just allocating a certain number of points based on the
# score. The reason for this is that the download score is
# a logaritmic scale so adding a fixed number of points ended up
# boosting scores way too much for content with low numbers of
# downloads. This system allows for the weight of the community score
# to scale with the number of downloads
download_count_ln_expr = Func(
(((Coalesce(F(c), 0) * COMMUNITY_SCORE_MODIFIER) +
COMMUNITY_SCORE_MODIFIER_MIN) * F(d)) + 1,
function='ln')
download_rank_expr = (F('download_count_ln') /
(1 + F('download_count_ln')) *
DOWNLOAD_RANK_MULTIPLIER)
q = 'repository__quality_score'
# This function is better than using a linear function because it
# makes it so that the effect of losing the first few points is
# relatively minor, which reduces the impact of errors in scoring.
quality_rank_expr = (Func(Coalesce(F(q), 0) + 1, function='log') *
CONTENT_SCORE_MULTIPLIER)
relevance_expr = (F('search_rank') + F('download_rank') +
F('quality_rank'))
return qs.annotate(
download_count_ln=Expr(download_count_ln_expr,
output_field=db_fields.FloatField()),
download_rank=Expr(download_rank_expr,
output_field=db_fields.FloatField()),
quality_rank=Expr(quality_rank_expr,
output_field=db_fields.FloatField()),
relevance=Expr(relevance_expr,
output_field=db_fields.FloatField()),
)
def get_db_prep_save(self, value, connection):
prepped_value = super(CompressedBinaryField,
self).get_db_prep_save(value, connection)
if connection.vendor == 'mysql':
return Func(Value(prepped_value), function='COMPRESS')
return prepped_value
def test_filter_conditional_join(self):
query = Query(Item)
filter_expr = Func('note__note', output_field=BooleanField())
msg = 'Joined field references are not permitted in this query'
with self.assertRaisesMessage(FieldError, msg):
query.build_where(filter_expr)
def test_filter_non_conditional(self):
query = Query(Item)
msg = 'Cannot filter against a non-conditional expression.'
with self.assertRaisesMessage(TypeError, msg):
query.build_where(Func(output_field=CharField()))
