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))