def get_dataverse_counts_by_month(self,
date_param=DVOBJECT_CREATEDATE_ATTR,
**extra_filters):
"""
Return Dataverse counts by month
"""
# Was an error found earlier?
#
if self.was_error_found():
return self.get_error_msg_return()
# -----------------------------------
# (1) Build query filters
# -----------------------------------
# Exclude records where dates are null
# - e.g. a record may not have a publication date
exclude_params = {'%s__isnull' % date_param: True}
if self.include_harvested:
exclude_params[
'dvobject__id__in'] = self.get_harvested_dataverse_ids()
# Retrieve the date parameters
#
filter_params = self.get_date_filter_params()
# Add extra filters from kwargs
#
if extra_filters:
for k, v in extra_filters.items():
filter_params[k] = v
# -----------------------------------
# (2) Construct query
# -----------------------------------
# add exclude filters date filters
#
dv_counts_by_month = Dataverse.objects.select_related('dvobject'\
).exclude(**exclude_params\
).filter(**filter_params)
# annotate query adding "month_year" and "count"
#
dv_counts_by_month = dv_counts_by_month.annotate(\
yyyy_mm=TruncYearMonth('%s' % date_param)\
).values('yyyy_mm'\
).annotate(count=models.Count('dvobject_id')\
).values('yyyy_mm', 'count'\
).order_by('%syyyy_mm' % self.time_sort)
# -----------------------------------
# (2a) Get SQL query string
# -----------------------------------
sql_query = str(dv_counts_by_month.query)
# -----------------------------------
# (3) Format results
# -----------------------------------
# hold the running total count
running_total = self.get_dataverse_count_start_point(**extra_filters)
formatted_records = [] # move from a queryset to a []
for d in dv_counts_by_month:
rec_fmt = OrderedDict()
# change the datetime object to a string
rec_fmt['yyyy_mm'] = d['yyyy_mm'].strftime('%Y-%m')
rec_fmt['count'] = d['count']
# running total
running_total += d['count']
rec_fmt['running_total'] = running_total
# d['month_year'] = d['yyyy_mm'].strftime('%Y-%m')
# Add year and month numbers
rec_fmt['year_num'] = d['yyyy_mm'].year
rec_fmt['month_num'] = d['yyyy_mm'].month
# Add month name
month_name_found, month_name_short = get_month_name_abbreviation(
d['yyyy_mm'].month)
if month_name_found:
assume_month_name_found, rec_fmt[
'month_name'] = get_month_name(d['yyyy_mm'].month)
rec_fmt['month_name_short'] = month_name_short
else:
# Log it!!!!!!
pass
# Add formatted record
formatted_records.append(rec_fmt)
data_dict = OrderedDict()
data_dict['record_count'] = len(formatted_records)
data_dict['total_count'] = running_total
data_dict['records'] = formatted_records
return StatsResult.build_success_result(data_dict, sql_query)
def get_dataverse_counts_by_type(self,
exclude_uncategorized=True,
**extra_filters):
"""
Return dataverse counts by 'dataversetype'
Optional if a dataverse is uncategorized:
- Specifying 'uncategorized_replacement_name' will
set "UNCATEGORIZED" to another string
Returns: { "dv_counts_by_type": [
{
"dataversetype": "RESEARCH_PROJECTS",
"type_count": 85,
"total_count": 356,
"percent_string": "23.9%"
},
{
"dataversetype": "TEACHING_COURSES",
"type_count": 10,
"total_count": 356,
"percent_string": "2.8%"
}
... etc
]
}
"""
if self.was_error_found():
return self.get_error_msg_return()
# Retrieve the date parameters
#
filter_params = self.get_date_filter_params(DVOBJECT_CREATEDATE_ATTR)
# Add extra filters
if extra_filters:
for k, v in extra_filters.items():
filter_params[k] = v
if exclude_uncategorized:
exclude_params = dict(dataversetype=DATAVERSE_TYPE_UNCATEGORIZED)
else:
exclude_params = {}
dataverse_counts_by_type = Dataverse.objects.select_related('dvobject'\
).filter(**filter_params\
).exclude(**exclude_params\
).values('dataversetype'\
).order_by('dataversetype'\
).annotate(type_count=models.Count('dataversetype')\
).order_by('-type_count')
# -----------------------------------
# Get SQL query string
# -----------------------------------
sql_query = str(dataverse_counts_by_type.query)
# Count all dataverses
#
total_count = sum(
[rec.get('type_count', 0) for rec in dataverse_counts_by_type])
total_count = total_count + 0.0
# Format the records, adding 'total_count' and 'percent_string' to each one
#
formatted_records = []
for rec in dataverse_counts_by_type:
fmt_dict = OrderedDict()
fmt_dict['dataversetype'] = rec['dataversetype']
fmt_dict['dataversetype_label'] = rec['dataversetype'].replace(
'_', ' ')
fmt_dict['type_count'] = rec.get('type_count', 0)
if total_count > 0:
float_percent = rec.get('type_count', 0) / total_count
fmt_dict['total_count'] = int(total_count)
fmt_dict['percent_string'] = '{0:.1%}'.format(float_percent)
else:
fmt_dict['total_count'] = 0
fmt_dict['percent_string'] = '0%'
formatted_records.append(fmt_dict)
data_dict = OrderedDict()
data_dict['record_count'] = len(formatted_records)
data_dict['records'] = formatted_records
return StatsResult.build_success_result(data_dict, sql_query)
def get_topic_list(self):
return (self.get_queryset().filter(is_topic=True)
.order_by('rank', 'name')
.annotate(num_publicbodies=models.Count('publicbodies'))
)
def get_list(self):
return self.get_visible().annotate(num_publicbodies=models.Count('publicbody'))
def get_counts(self, guid):
works = dict(
CompatReport.objects.filter(
guid=guid).values_list('works_properly').annotate(
models.Count('id')))
return {'success': works.get(True, 0), 'failure': works.get(False, 0)}
def get_models_for_count(*model_names):
return [models.Count(model_name) for model_name in model_names]
def with_tracks_count(self):
return self.annotate(_tracks_count=models.Count("tracks"))
def resolve_films(root, info, **kwargs):
qs = Film.objects.prefetch_related("reporters")
return qs.annotate(reporters_count=models.Count("reporters"))
def get_queryset(self):
return super(NoStatementManager, self).get_queryset()\
.annotate(scount=models.Count('statement'))\
.exclude(scount__gt=0)
def with_albums_count(self):
return self.annotate(_albums_count=models.Count("albums"))
def get_queryset(self, request):
return super().get_queryset(request) \
.annotate(projects_count=models.Count('projects'))
def _filter_or_exclude(self, negate, *args, **kwargs):
"""
Custom lookups for tag fields
"""
if django.VERSION >= (3, 2):
# Arguments changed to _filter_or_exclude(self, negate, args, kwargs)
if len(args) == 2:
args, kwargs = args
elif set(kwargs.keys()) == {"args", "kwargs"}:
args, kwargs = kwargs["args"], kwargs["kwargs"]
# TODO: Replace with custom lookups
safe_fields, singletag_fields, tag_fields, field_lookup = _split_kwargs(
self.model, kwargs, lookups=True, with_fields=True)
# Look up string values for SingleTagFields by name
for field_name, val in singletag_fields.items():
query_field_name = field_name
if isinstance(val, str):
query_field_name += "__name"
if not field_lookup[field_name].tag_options.case_sensitive:
query_field_name += "__iexact"
safe_fields[query_field_name] = val
# Query as normal
if django.VERSION >= (3, 2):
qs = super(TaggedQuerySet,
self)._filter_or_exclude(negate, args, safe_fields)
else:
qs = super(TaggedQuerySet,
self)._filter_or_exclude(negate, *args, **safe_fields)
# Look up TagFields by string name
#
# Each of these comparisons will be done with a subquery; for
# A filter can chain, ie .filter(tags__name=..).filter(tags__name=..),
# but exclude won't work that way; has to be done with a subquery
for field_name, val in tag_fields.items():
val, lookup = val
tag_options = field_lookup[field_name].tag_options
# Only perform custom lookup if value is a string
if not isinstance(val, str):
if django.VERSION >= (3, 2):
qs = super(TaggedQuerySet, self)._filter_or_exclude(
negate, [], {field_name: val})
else:
qs = super(TaggedQuerySet, self)._filter_or_exclude(
negate, **{field_name: val})
continue
# Parse the tag string
tags = utils.parse_tags(
val, space_delimiter=tag_options.space_delimiter)
# Prep the subquery
subqs = qs
if negate:
subqs = self.__class__(model=self.model, using=self._db)
# To get an exact match, filter this queryset to only include
# items with a tag count that matches the number of specified tags
if lookup == "exact":
count_name = "_tagulous_count_%s" % field_name
subqs = subqs.annotate(**{
count_name: models.Count(field_name)
}).filter(**{count_name: len(tags)})
# Explicit order as meta ordering will be ignored
subqs = subqs.order_by("name")
# Prep the field name
query_field_name = field_name + "__name"
if not tag_options.case_sensitive:
query_field_name += "__iexact"
# Now chain the filters for each tag
#
# Have to do it this way to create new inner joins for each tag;
# ANDing Q objects will do it all on a single inner join, which
# will match nothing
for tag in tags:
subqs = subqs.filter(**{query_field_name: tag})
# Fold subquery back into main query
if negate:
# Exclude on matched ID
qs = qs.exclude(pk__in=subqs.values("pk"))
else:
# A filter op can just replace the main query
qs = subqs
return qs
def _folder(self, related, filters, option=None, order_by=None):
"""Base code, in common to the folders."""
qs = self.all() if option == OPTION_MESSAGES else QuerySet(
self.model, PostmanQuery(self.model), using=self._db)
if related:
qs = qs.select_related(*related)
if order_by:
qs = qs.order_by(order_by)
if isinstance(filters, (list, tuple)):
lookups = models.Q()
for filter in filters:
lookups |= models.Q(**filter)
else:
lookups = models.Q(**filters)
if option == OPTION_MESSAGES:
return qs.filter(lookups)
# Adding a 'count' attribute, to be similar to the by-conversation case,
# should not be necessary. Otherwise add:
# .extra(select={'count': 'SELECT 1'})
else:
qs = qs.extra(
select={'count': '{0}.count'.format(qs.query.pm_alias_prefix)})
qs.query.pm_set_extra(table=(
# extra columns are always first in the SELECT query
# for tests with the version 2.4.1 of sqlite3 in py26, add to the select: , 'id': 'postman_message.id'
self.filter(lookups, thread_id__isnull=True).extra(select={'count': 0})\
.values_list('id', 'count').order_by(),
# use separate annotate() to keep control of the necessary order
self.filter(lookups, thread_id__isnull=False).values('thread').annotate(count=models.Count('pk')).annotate(id=models.Max('pk'))\
.values_list('id', 'count').order_by(),
))
return qs
def task_mode_report(self):
"""
Returns queryset with annotate how many
tasks with each task mode was
"""
return self.values("mode").annotate(task_count=models.Count("pk")).order_by("mode")
def count_each_kind(self):
result = self.values_list("kind").annotate(count=models.Count("kind"))
return dict(result)