def getMembersChart(self):
if not self._membersChart:
months = list()
counts = dict()
monthly_active = dict()
total = 0
members = Member.objects.filter(community=self.community)
if self.member_tag:
members = members.filter(tags=self.member_tag)
if self.role:
members = members.filter(role=self.role)
seen = members.annotate(month=Trunc('first_seen', self.trunc_span)).values('month').annotate(member_count=Count('id', distinct=True)).order_by('month')
for m in seen:
total += 1
month = self.trunc_date(m['month'])
if month not in months:
months.append(month)
counts[month] = m['member_count']
active = members.annotate(month=Trunc('speaker_in__timestamp', self.trunc_span)).values('month').annotate(member_count=Count('id', distinct=True)).order_by('month')
for a in active:
if a['month'] is not None:
month = self.trunc_date(a['month'])
if month not in months:
months.append(month)
monthly_active[month] = a['member_count']
self._membersChart = (sorted(months), counts, monthly_active)
return self._membersChart
def get(self, request, **kwargs):
ret = {'dominios': {}, 'google_chart_data': {}}
starts = timezone.now() - timedelta(days=60)
# Año
dominios = Dominio.objects.filter(estado=STATUS_NO_DISPONIBLE, expire__gt=starts)\
.annotate(year_expire=Trunc('expire', 'year'))\
.order_by('year_expire')\
.values('year_expire')\
.annotate(total=Count('year_expire'))
ret['dominios']['year'] = list(dominios)
# return also google chart comaptible data
headers = ['Año', 'dominios a expirar']
google_chart_data = [headers]
for data in dominios:
line = [data['year_expire'], data['total']]
google_chart_data.append(line)
ret['google_chart_data']['year'] = google_chart_data
# SEMANA
dominios = Dominio.objects.filter(estado=STATUS_NO_DISPONIBLE, expire__gt=starts)\
.annotate(week_expire=Trunc('expire', 'week'))\
.order_by('week_expire')\
.values('week_expire')\
.annotate(total=Count('week_expire'))
ret['dominios']['week'] = list(dominios)
# return also google chart comaptible data
headers = ['Semana', 'dominios a expirar']
google_chart_data = [headers]
for data in dominios:
line = [data['week_expire'], data['total']]
google_chart_data.append(line)
ret['google_chart_data']['week'] = google_chart_data
# DIA
dominios = Dominio.objects.filter(estado=STATUS_NO_DISPONIBLE, expire__gt=starts)\
.annotate(day_expire=Trunc('expire', 'day'))\
.order_by('day_expire')\
.values('day_expire')\
.annotate(total=Count('day_expire'))
ret['dominios']['day'] = list(dominios)
# return also google chart comaptible data
headers = ['Día', 'dominios a expirar']
google_chart_data = [headers]
for data in dominios:
line = [data['day_expire'], data['total']]
google_chart_data.append(line)
ret['google_chart_data']['day'] = google_chart_data
return JsonResponse({'ok': True, 'data': ret}, status=200)
def report(request, report_type):
report_type = report_type
orders = Order.objects.filter(finish_flag=True)
if report_type == 'month':
orders = orders.annotate(
time=Trunc('date', 'month')).values('time').annotate(
count=Count('id'), total_income=Sum('total_price'))
elif report_type == 'day':
orders = orders.annotate(
time=Trunc('date', 'day')).values('time').annotate(
count=Count('id'), total_income=Sum('total_price'))
elif report_type == 'year':
orders = orders.annotate(
time=Trunc('date', 'year')).values('time').annotate(
count=Count('id'), total_income=Sum('total_price'))
elif report_type == 'week':
orders = orders.annotate(
time=Trunc('date', 'week')).values('time').annotate(
count=Count('id'),
total_income=Sum('total_price'),
week=ExtractWeek('time'))
return render(request,
'report/report.html',
context={
'orders': orders,
'type': report_type,
})
def estado_mensual(request, year=0, month=0):
nro_alumnos = Alumno.objects.filter(activo=True).count()
#calculamos porcentaje de cuotas pagadas en el mes actual
date = datetime.date.today()
date = date.replace(day=1)
nro_cuotas = (AsientoContable.objects.filter(rubro=1,
concepto=date,
borrado=False).count())
#nro_cuotas = (Alumno.objects
# .filter(fechaultimopago__year=datetime.date.today().year, fechaultimopago__month=datetime.date.today().month).count())
nro_cuotas_x_mes = (AsientoContable.objects.filter(borrado=False).exclude(
concepto__isnull=True).annotate(
periodo=Trunc('concepto', 'month')).values('periodo').annotate(
inscripciones=Count('rubro', filter=Q(rubro=2))).annotate(
cuotas=Count('rubro', filter=Q(rubro=1))).annotate(
promedio=Avg('ingreso', filter=Q(
rubro=1))).order_by('-periodo'))
porcentaje_cuotas = 0
if nro_alumnos != 0:
porcentaje_cuotas = int((nro_cuotas / nro_alumnos) * 100)
#movimientos mensuales del mes solicitado o en curso
if (year == 0 and month == 0):
fecha = datetime.date.today()
year = fecha.year
month = fecha.month
mov_cuenta = (AsientoContable.objects.filter(fecha__year=year,
fecha__month=month,
borrado=False).order_by(
'fecha', 'id'))
#resumen del total de movimientos de todos los meses
tabla = (AsientoContable.objects.filter(borrado=False).annotate(
periodo=Trunc('fecha', 'month')).values('periodo').annotate(
ingresos=Sum('ingreso')).annotate(egresos=Sum('egreso')).annotate(
resultado=Sum('ingreso') - Sum('egreso')).annotate(
retiro_caja=Sum('retiro_caja')).annotate(
en_caja=Sum('ingreso')).order_by('periodo'))
resultado = 0
caja = 0
for registro in tabla:
resultado = registro.get('resultado') #ultimo resultado
caja += registro.get('resultado') - registro.get('retiro_caja')
registro['en_caja'] = caja
context = {
"nro_alumnos": nro_alumnos,
"porcentaje_cuotas": porcentaje_cuotas,
"resultado": resultado,
"mov_cuenta": mov_cuenta,
"tabla": tabla,
"caja": caja,
"nro_cuotas_x_mes": nro_cuotas_x_mes,
}
return render(request, 'blackbird/contabilidad/estado_mensual.html',
context)
def changelist_view(self, request, extra_context=None):
response = super().changelist_view(
request,
extra_context=extra_context,
)
monthly_salaries = TeacherSalary.objects.annotate(
month=Trunc('paid_on', 'month')).values('month').annotate(
total=Sum('total_amount'))
monthly_fees = StudentFee.objects.annotate(
month=Trunc('date_submitted', 'month')).values('month').annotate(
total=Sum('total_amount'))
data = {}
for salary in monthly_salaries:
month_year = "{}-{}".format(salary['month'].month,
salary['month'].year)
data[month_year] = {
"salaries": salary['total'],
"fees": 0,
"profit": salary['total']
}
for fee in monthly_fees:
month_year = "{}-{}".format(fee['month'].month, fee['month'].year)
if month_year in data:
data[month_year]['fees'] = fee['total']
data[month_year][
'profit'] = data[month_year]['salaries'] - fee['total']
else:
data[month_year]['salaries'] = 0
data[month_year]['fees'] = fee['total']
data[month_year]['profit'] = 0 - fee['total']
# Prepare data to graph format
labels, salaries, fees = ([] for i in range(3))
for key, value in data.items():
labels.append(key)
salaries.append(value['salaries'])
fees.append(value['fees'])
# Scructure of graph_data is defined by Graph.js
graph_data = {
'labels':
labels,
'datasets': [{
'label': "Teacher",
'backgroundColor': "#F5DD5D",
'data': salaries
}, {
'label': "Student",
'backgroundColor': "#44B78B",
'data': fees
}]
}
response.context_data['data'] = data
response.context_data['graph_data'] = json.dumps(graph_data)
return response
def test_trunc_func(self):
start_datetime = microsecond_support(datetime(2015, 6, 15, 14, 30, 50, 321))
end_datetime = microsecond_support(datetime(2016, 6, 15, 14, 10, 50, 123))
if settings.USE_TZ:
start_datetime = timezone.make_aware(start_datetime, is_dst=False)
end_datetime = timezone.make_aware(end_datetime, is_dst=False)
self.create_model(start_datetime, end_datetime)
self.create_model(end_datetime, start_datetime)
with self.assertRaisesMessage(ValueError, 'output_field must be either DateField or DateTimeField'):
list(DTModel.objects.annotate(truncated=Trunc('start_datetime', 'year', output_field=IntegerField())))
with self.assertRaisesMessage(AssertionError, "'name' isn't a DateField or DateTimeField."):
list(DTModel.objects.annotate(truncated=Trunc('name', 'year', output_field=DateTimeField())))
with self.assertRaisesMessage(ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"):
list(DTModel.objects.annotate(truncated=Trunc('start_date', 'second')))
with self.assertRaisesMessage(ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"):
list(DTModel.objects.annotate(truncated=Trunc('start_date', 'month', output_field=DateTimeField())))
def test_datetime_kind(kind):
self.assertQuerysetEqual(
DTModel.objects.annotate(
truncated=Trunc('start_datetime', kind, output_field=DateTimeField())
).order_by('start_datetime'),
[
(start_datetime, truncate_to(start_datetime, kind)),
(end_datetime, truncate_to(end_datetime, kind))
],
lambda m: (m.start_datetime, m.truncated)
)
def test_date_kind(kind):
self.assertQuerysetEqual(
DTModel.objects.annotate(
truncated=Trunc('start_date', kind, output_field=DateField())
).order_by('start_datetime'),
[
(start_datetime, truncate_to(start_datetime.date(), kind)),
(end_datetime, truncate_to(end_datetime.date(), kind))
],
lambda m: (m.start_datetime, m.truncated)
)
test_date_kind('year')
test_date_kind('month')
test_date_kind('day')
test_datetime_kind('year')
test_datetime_kind('month')
test_datetime_kind('day')
test_datetime_kind('hour')
test_datetime_kind('minute')
test_datetime_kind('second')
qs = DTModel.objects.filter(start_datetime__date=Trunc('start_datetime', 'day', output_field=DateField()))
self.assertEqual(qs.count(), 2)
def gen_ctx_grpby_datefld(request, response, uniq_fld_name, filters, date_fld_name, clazz_model, date_hierarchy):
period = get_next_in_date_hierarchy(
request,
date_hierarchy,
)
startdate, lower_bound_op, enddate, upper_bound_op = extract_date_range(response)
dt_range = None
if startdate and lower_bound_op and enddate and upper_bound_op:
dt_range = {'{}__{}'.format(date_fld_name, lower_bound_op): startdate,
'{}__{}'.format(date_fld_name, upper_bound_op): enddate}
aggregate_by_period_ctx = {}
aggregate_by_period_ctx['period'] = period
total_by_date = None
q_filter = filters if filters is not None else None
if dt_range:
if q_filter:
q_filter = {**q_filter, **dt_range}
else:
q_filter = dt_range
if q_filter is not None:
total_by_date = clazz_model.objects.filter(**q_filter).annotate(
period=Trunc(
date_fld_name,
period,
output_field=DateTimeField(),
),
).values('period').annotate(sub_total=Count(uniq_fld_name)).order_by('period')
else:
total_by_date = clazz_model.objects.all().annotate(
period=Trunc(
date_fld_name,
period,
output_field=DateTimeField(),
),
).values('period').annotate(sub_total=Count(uniq_fld_name)).order_by('period')
sub_totals_range = total_by_date.aggregate(
low=Min('sub_total'),
high=Max('sub_total'),
)
high = sub_totals_range.get('high', 0)
low = sub_totals_range.get('low', 0)
chart_list = []
for e in total_by_date:
chart_list.append(
{'period': e['period'],
'sub_total': e['sub_total'] or 0,
'pct': (e['sub_total'] or 0) / high * 100 if high > low else 0, })
return chart_list
def get_stat(request, acc=0):
if acc != 0:
# Эта ветка никогда не заработает
if not Account.objects.filter(acc_id=acc).exists():
return redirect('start')
if Account.objects.get(acc_id=acc).user_id != request.user.id:
return redirect('start')
if Account.objects.filter(acc_id=acc).exists():
change_by_month = Charge.objects \
.filter(account_id=acc) \
.annotate(month=Trunc('date', 'month')) \
.values('month') \
.annotate(c=Count('id')) \
.annotate(s=Sum('value')) \
.values('month', 'c', 's') \
.order_by('month')
# todo вставить недостающие элементы в список, т.е. вставить месяцы, когда не происходило транзакций
return render(
request, 'get_stat.html', {
'acc': acc,
'amount': Account.objects.get(acc_id=acc).total,
'stat_data': change_by_month
})
else:
return redirect('create_account')
else:
account_info = Charge.objects \
.filter(account__in=Account.objects.filter(user_id=request.user.id)) \
.values('account', ) \
.annotate(acc_sum=Sum('value'))
for acc in account_info:
acc['change_by_month'] = Charge.objects \
.filter(account__in=Account.objects.filter(acc_id=acc['account'])) \
.annotate(month=Trunc('date', 'month')) \
.values('month') \
.annotate(c=Count('ch_id')) \
.annotate(s=Sum('value')) \
.values('month', 'c', 's') \
.order_by('month')
acc['name'] = Account.objects.filter(
acc_id=acc['account']).values('acc_name')[0]
# todo вставить недостающие элементы в список, т.е. вставить месяцы, когда не происходило транзакций
return render(request, 'get_stat.html', {
'acc': acc,
'stat_data': account_info
})
def changelist_view(self, request, extra_context=None):
response = super().changelist_view(request,
extra_context=extra_context)
try:
qs = response.context_data['cl'].queryset
summary = qs \
.annotate(period=Trunc('event_time', 'day', output_field=DateTimeField())) \
.values('period') \
.annotate(total=Count('id')) \
.order_by('period')
summary_range = summary.aggregate(low=Min('total'),
high=Max('total'))
high = summary_range.get('high', 0)
low = summary_range.get('low', 0)
total = qs.count()
response.context_data["summary_over_time"] = [{
'period': x['period'],
'total': x['total'] or 0,
'pct': \
((x['total'] or 0) / total * 100 if high >= low else 0),
} for x in summary]
except (AttributeError, KeyError):
pass
return response
def view_entries(request):
weight_data = WeightEntry.objects.order_by(
Trunc('data_date', 'date', output_field=DateField()).desc())
total_min_weight = 0
total_max_weight = 0
total_variance = 0
data_count = 0
if weight_data:
for wd in weight_data:
total_min_weight += wd.min_weight
total_max_weight += wd.max_weight
total_variance += wd.variance
data_count += 1
total_max_weight /= data_count
total_min_weight /= data_count
total_variance /= data_count
return render(
request, 'weight_tracker/view_data.html', {
'weight_data': weight_data,
'avg_min': total_min_weight,
'avg_max': total_max_weight,
'avg_var': total_variance
})
def test_datetime_kind(kind):
self.assertQuerysetEqual(
DTModel.objects.annotate(truncated=Trunc(
'start_datetime', kind,
output_field=DateTimeField())).order_by('start_datetime'),
[(truncate_to(start_datetime, kind)),
(truncate_to(end_datetime, kind))], lambda m: (m.truncated))
def get_time_data_yearly(year, section, lane=None, direction=None):
"""Vehicles by hour and day of the week"""
start = datetime(year, 1, 1)
end = datetime(year + 1, 1, 1)
# By lane/direction grouped per hour
qs = models.CountDetail.objects.filter(id_lane__id_section=section,
timestamp__range=(start, end))
if lane is not None:
qs = qs.filter(id_lane=lane)
if direction is not None:
qs = qs.filter(id_lane__direction=direction)
# Vehicles by day and hour
qs = qs.annotate(date=Trunc('timestamp', 'day'), hour=ExtractHour('timestamp')) \
.order_by('hour') \
.values('date', 'hour', 'times') \
.order_by('date', 'hour') \
.annotate(thm=Sum('times')) \
.values('import_status', 'date', 'hour', 'thm')
df = pd.DataFrame.from_records(qs)
df = df.groupby([df['date'].dt.dayofweek, 'hour']).thm.sum()
df = df.reset_index()
return df
def home(request):
"""
Homepage view loads all the LinkPosts
and a form to create LinkPost
"""
# linkposts are ordered by day then by total likes they have
links = LinkPost.objects.annotate(
day_created=Trunc('created', 'day', output_field=DateField()))\
.order_by('-day_created', '-total_likes')
# create a linkpost
if request.method == 'POST':
link_post_form = LinkPostForm(request.POST)
if link_post_form.is_valid():
link_post_obj = link_post_form.save(commit=False)
link_post_obj.author = request.user
link_post_obj.save()
# linkpost form
link_post_form = LinkPostForm()
# get posts already liked by logged-in user
user_likes = None
if request.user.is_authenticated:
user_likes = LinkPost.objects.filter(liked_by=request.user)
return render(
request, 'home/homepage.html', {
'links': links,
'link_post_form': link_post_form,
'user_likes': user_likes,
})
def vul(request):
create_log(request)
date = request.GET.get('date', 'minute')
objects = list(
WebLog.objects.annotate(time=Trunc('created_time', date)).values(
'time').order_by('-time').annotate(count=Count('id')))
return JsonResponse(data=objects, safe=False)
def get(self, request):
type_query = Module.objects.values('type__name').annotate(
count=Count('id')).order_by('-count')
type_breakdown = [(x['type__name'], x['count'])
for x in type_query[:20]]
type_breakdown.append(
('Other', sum(x['count'] for x in type_query[20:])))
module_count_query = (Module.objects.annotate(
hour=Trunc('first_seen', 'day')).annotate(cumsum=Window(
expression=Count('id'), order_by=F('hour').asc())).values(
'hour', 'cumsum').distinct('hour'))
module_count_data = [(x['hour'].strftime(r"%Y%m%d"), x['cumsum'])
for x in module_count_query]
prolific_creators = (EveCharacter.objects.annotate(
creation_count=Count('creations')).order_by('-creation_count')[:8])
traded_modules = Module.objects.annotate(
contract_count=Count('contracts')).order_by('-contract_count')[:8]
return render(
request, 'abyssal_modules/statistics.html', {
'type_breakdown': type_breakdown,
'module_count_data': module_count_data,
'prolific_creators': prolific_creators,
'traded_modules': traded_modules,
})
def _getTonersStats(self, range, field, kind):
filter = {
'is_deleted': False,
'status__link_printer': True,
}
if range:
filter[f'{field}__range'] = range
toners = TonerCartridgesLog.objects.filter(**filter)
# Get the number of records per kind for a period of time
tonersLog = toners.annotate(
group=Trunc(field, kind, output_field=DateTimeField())
).order_by(
'group'
).values(
'group'
).annotate(count=Count('id'))
# Get the number of printers of each type for a period of time
tonersStats = toners.order_by(
'equipment__brand', 'equipment__model',
).values(
'equipment__brand__short_name', 'equipment__model',
).annotate(
count=Count('equipment__type')
)
tonersStats = map(self._combineEquipmentName, tonersStats)
return {'log': list(tonersLog), 'stats': list(tonersStats)}
def index(request):
qtd_cadastro = Cadastro.objects.count()
total_vendas = Recibo.objects.all().aggregate(Sum('total'))
media_vendas = Recibo.objects.all().aggregate(Avg('total'))
hoje = timezone.now()
dt_inicio = hoje.replace(hour=0, minute=0, second=0, microsecond=0)
dt_final = hoje.replace(hour=23, minute=59, second=59, microsecond=0)
cad_por_hora = Cadastro.objects.filter(data_hora__range=(dt_inicio, dt_final)) \
.annotate(hora=ExtractHour('data_hora')) \
.values('hora') \
.order_by('hora') \
.annotate(qtd=Count('id')) \
.values('hora', 'qtd')
vendas_por_dia = Recibo.objects.annotate(dia=Trunc('data_hora', 'day', output_field=DateTimeField())) \
.values('dia') \
.order_by('dia') \
.annotate(qtd=Count('id')) \
.annotate(total=Sum('total')) \
.values('dia', 'qtd', 'total')
return render(request,
'index.html',
{
'qtd_cadastro': qtd_cadastro,
'total_vendas': total_vendas,
'media_vendas': media_vendas,
'vendas_por_dia': vendas_por_dia,
'cad_por_hora': cad_por_hora
})
def trunc_function():
# 打印每一天发布的应用数量
apps = App.objects.annotate(publish_day=Trunc('publish_date', 'day')).\
value('publish_day').\
annotate(publish_num=Count('appid'))
for i in apps:
print('date:', i['publish_date'], 'pubnum:', i['publish_num'])
def get_stats(cls) -> List:
query = CheckExecution.objects.filter(
created__gte=timezone.now() - timedelta(days=10)).annotate(
day=Trunc('created', 'day', output_field=models.DateField()))
results = [['day', 'executions', 'successes', 'fails']]
results = results + [[
date(day.year, day.month, day.day),
len([
se
for se in query if date(se.day.year, se.day.month, se.day.day)
== date(day.year, day.month, day.day)
]),
len([
se for se in query
if se.success and date(se.day.year, se.day.month, se.day.day)
== date(day.year, day.month, day.day)
]),
len([
se for se in query if not se.success
and date(se.day.year, se.day.month, se.day.day) == date(
day.year, day.month, day.day)
]),
] for day in (timezone.now() - timedelta(days=10) + timedelta(n)
for n in range(11))]
return results
def changelist_view(self, request, extra_context=None):
response = super().changelist_view(request,
extra_context=extra_context)
try:
qs = response.context_data['cl'].queryset
except (AttributeError, KeyError):
return response
period = get_next_in_date_hierarchy(request, self.date_hierarchy)
response.context_data['period'] = period
summery_over_time = qs.annotate(
period=Trunc(
'created_at', period, output_field=DateTimeField()
),
).values('period') \
.annotate(total=Count('id')) \
.order_by('period')
summary_range = summery_over_time.aggregate(high=Max('total'))
high = summary_range.get('high', 0)
response.context_data['summary_over_time'] = [{
'period':
x['period'],
'total':
x['total'] or 0,
'pct': (x['total'] or 0) / high * 100,
} for x in summery_over_time]
return response
def api_resumen_grafico_periodo(request):
labels = []
dataset0 = []
dataset1 = []
dataset2 = []
#periodos a mostrar
periodos = int(request.GET.get('periodos', 3))
queryset = (AsientoContable.objects.filter(
borrado=False, user=request.user, fecha__lte=datetime.date.today()
).annotate(periodo=Trunc('fecha', 'month')).values('periodo').annotate(
ingresos=Sum('ingreso')).annotate(
gastos_fijos=Sum('egreso', filter=Q(
rubro__gasto_fijo=True))).annotate(gastos_variables=Sum(
'egreso', filter=Q(rubro__gasto_variable=True))).order_by(
'-periodo')[:periodos])
for item in queryset:
labels.append(item['periodo'])
dataset0.append(item['ingresos'])
dataset1.append(item['gastos_fijos'])
dataset2.append(item['gastos_variables'])
return JsonResponse(
data={
'labels': labels,
'dataset0': dataset0,
'dataset1': dataset1,
'dataset2': dataset2,
})
def points_stat(roadmap_id, user):
months_stat = Scores.objects.filter(task__user=user, task__roadmap=roadmap_id) \
.annotate(year_month=Trunc('date', 'month')) \
.order_by('year_month') \
.values('year_month') \
.annotate(points=Sum('points'))
return months_stat
def test_measures_by_hour(self):
view = MeasurementViewSet()
sensor, silo_id = self.persist_test_sensor_and_silo()
values = {
datetime(2019, 3, 23, 18, 43, 30, tzinfo=timezone.utc): 64,
datetime(2019, 3, 23, 18, 42, 31, tzinfo=timezone.utc): 62,
datetime(2019, 3, 23, 18, 42, 30, tzinfo=timezone.utc): 60,
datetime(2019, 3, 23, 18, 41, 30, tzinfo=timezone.utc): 58,
datetime(2019, 3, 23, 18, 40, 32, tzinfo=timezone.utc): 56,
datetime(2019, 3, 23, 18, 40, 31, tzinfo=timezone.utc): 54,
datetime(2019, 3, 23, 18, 40, 30, tzinfo=timezone.utc): 52,
datetime(2019, 3, 23, 18, 39, 30, tzinfo=timezone.utc): 50,
datetime(2019, 3, 23, 15, 39, 30, tzinfo=timezone.utc): 50,
datetime(2019, 3, 21, 15, 39, 30, tzinfo=timezone.utc): 50,
# and one in the future
datetime(2020, 3, 21, 15, 39, 30, tzinfo=timezone.utc): 50,
}
self.persist_test_measurements(sensor, values)
response = view._get_measures_as_json_response("%H:%M", silo_id, Trunc('saved', 'minute', tzinfo=timezone.utc),
timezone.timedelta(hours=1))
self.assertJSONEqual(
str(response.content, encoding='utf8'),
{"19:39": 50.0, "19:40": 56.0, "19:41": 58.0, "19:42": 62.0, "19:43": 64.0}
)
def test_measures_by_day(self):
view = MeasurementViewSet()
sensor, silo_id = self.persist_test_sensor_and_silo()
values = {
datetime(2019, 3, 23, 14, 43, 30, tzinfo=timezone.utc): 64,
datetime(2019, 3, 23, 14, 42, 31, tzinfo=timezone.utc): 62,
datetime(2019, 3, 23, 13, 42, 30, tzinfo=timezone.utc): 60,
datetime(2019, 3, 23, 13, 41, 30, tzinfo=timezone.utc): 58,
datetime(2019, 3, 23, 13, 40, 32, tzinfo=timezone.utc): 56,
datetime(2019, 3, 23, 12, 45, 31, tzinfo=timezone.utc): 54,
datetime(2019, 3, 23, 12, 40, 30, tzinfo=timezone.utc): 52,
datetime(2019, 3, 23, 11, 39, 30, tzinfo=timezone.utc): 50,
datetime(2019, 3, 22, 11, 39, 30, tzinfo=timezone.utc): 50,
}
self.persist_test_measurements(sensor, values)
response = view._get_measures_as_json_response("%H:00", silo_id, Trunc('saved', 'hour', tzinfo=timezone.utc),
timezone.timedelta(days=1))
self.assertJSONEqual(
str(response.content, encoding='utf8'),
# times are bit different because of timezones
{"12:00": 50.0, "13:00": 54.0, "14:00": 60.0, "15:00": 64.0}
)
def values(self):
queryset = self.get_queryset()
return (queryset.annotate(
date=Trunc('created_date_format', 'day')).values('date').annotate(
count=Count('id')).values_list(
'date', 'count').order_by('-date').filter(
created_date__gte=one_week_ago))
def data_per_period(cls):
"""
Provide total students, courses and instances, from all services per period, day by default.
We summarize values per day, because in same day we can receive data from multiple different instances.
We suppose, that every instance send data only once per day.
"""
subquery = cls.objects.annotate(
date_in_days=Trunc('data_created_datetime', 'day', output_field=DateField())
).values('date_in_days').order_by()
# last order_by() is needed:
# http://chase-seibert.github.io/blog/2012/02/24/django-aggregation-group-by-day.html
# https://docs.djangoproject.com/en/dev/topics/db/aggregation/#interaction-with-default-ordering-or-order-by
students_per_day = subquery.annotate(
students=Sum('active_students_amount_day')
).values_list('students', flat=True)
courses_per_day = subquery.annotate(courses=Sum('courses_amount')).values_list('courses', flat=True)
instances_per_day = subquery.annotate(
instances=Count('edx_installation__access_token')
).values_list('instances', flat=True)
return list(students_per_day), list(courses_per_day), list(instances_per_day)
def query_stats(cls, query_id=None, query=None):
stats = []
one_month = datetime.date.today() - timedelta(days=30)
query = Document2.objects.get(id=query_id) if query is None else query
stats.append({
'name': 'query',
'value': '%s - %s' % (query_id, query.name),
'description': _('Query info')
})
executions = query.dependents.filter(is_history=True, type__startswith='query-')
stats.append({
'name': 'execution_count',
'value': executions.count(),
'description': _('How many times executed')
})
stats.append({
'name': 'execution_count_shared',
'value': executions.exclude(owner=query.owner).count(),
'description': _('Executions by others')
})
last_month_daily = executions.filter(last_modified__gte=one_month).annotate(day=Trunc('last_modified', 'day')).values('day').annotate(c=Count('day')).values('day', 'c').order_by('day')
stats.append({
'name': 'executions_30_days_histogram',
'value': last_month_daily,
'description': _('Daily executions 30 days')
})
# Could count number of "forks" (but would need to start tracking parent of Saved As query cf. saveAsNotebook)
return stats
def user_stats(cls, user_id=None, user=None):
stats = []
one_month = datetime.date.today() - timedelta(days=30)
user = User.objects.get(id=user_id) if user is None else user
queries = Document2.objects.filter(owner__id=user_id, type__startswith='query-', is_trashed=False, is_managed=False)
stats.append({
'name': 'user',
'value': '%s - %s' % (user_id, user.username),'description': _('User info')
})
query_executions = queries.filter(is_history=True, type__startswith='query-')
stats.append({
'name': 'query_executions',
'values': query_executions.count(),
'description': _('Query executions count')
})
stats.append({
'name': 'saved_queries_count',
'value': queries.filter(is_history=False, type__startswith='query-').count(),
'description': _('Saved queries count')
})
stats.append({
'name': 'query_executions_30_days_count',
'value': query_executions.filter(last_modified__gte=one_month).count(),
'description': _('Query executions 30 days total')
})
last_month_daily = queries.filter(last_modified__gte=one_month).annotate(day=Trunc('last_modified', 'day')).values('day').annotate(c=Count('day')).values('day', 'c').order_by('day')
stats.append({
'name': 'query_executions_30_days_histogram',
'value': last_month_daily,
'description': _('Daily executions 30 days')
})
return stats
def _getRepairsStats(self, range, field, kind):
filter = {'is_deleted': False}
if range:
filter[f'{field}__range'] = range
repairs = Repairs.objects.filter(**filter)
# Get the number of records per kind for a period of time
repairsLog = repairs.annotate(
group=Trunc(field, kind, output_field=DateTimeField())
).order_by(
'group'
).values(
'group'
).annotate(count=Count('id'))
# Get the number of equipment of each type for a period of time
repairsStats = repairs.order_by(
'equipment__type'
).values(
'equipment__type__name'
).annotate(
count=Count('equipment__type')
)
return {'log': list(repairsLog), 'stats': list(repairsStats)}
def test_time_kind(kind, tzinfo=melb):
self.assertQuerysetEqual(
DTModel.objects.annotate(truncated=Trunc(
'start_time', kind, output_field=TimeField(),
tzinfo=melb)).order_by('start_datetime'),
[(start_datetime, truncate_to(start_datetime.time(), kind)),
(end_datetime, truncate_to(end_datetime.time(), kind))],
lambda m: (m.start_datetime, m.truncated))
