def test_extract_func_with_timezone(self): start_datetime = microsecond_support(datetime(2015, 6, 15, 23, 30, 1, 321)) end_datetime = microsecond_support(datetime(2015, 6, 16, 13, 11, 27, 123)) 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) melb = pytz.timezone('Australia/Melbourne') qs = DTModel.objects.annotate( day=Extract('start_datetime', 'day'), day_melb=Extract('start_datetime', 'day', tzinfo=melb), weekday=ExtractWeekDay('start_datetime'), weekday_melb=ExtractWeekDay('start_datetime', tzinfo=melb), hour=ExtractHour('start_datetime'), hour_melb=ExtractHour('start_datetime', tzinfo=melb), ).order_by('start_datetime') utc_model = qs.get() self.assertEqual(utc_model.day, 15) self.assertEqual(utc_model.day_melb, 16) self.assertEqual(utc_model.weekday, 2) self.assertEqual(utc_model.weekday_melb, 3) self.assertEqual(utc_model.hour, 23) self.assertEqual(utc_model.hour_melb, 9) with timezone.override(melb): melb_model = qs.get() self.assertEqual(melb_model.day, 16) self.assertEqual(melb_model.day_melb, 16) self.assertEqual(melb_model.weekday, 3) self.assertEqual(melb_model.weekday_melb, 3) self.assertEqual(melb_model.hour, 9) self.assertEqual(melb_model.hour_melb, 9)
def birthdaylist(request): if(request.user.is_authenticated): users=CustomUser.objects.annotate( birth_date_month=Extract('birth_date', 'month'), birth_date_day=Extract('birth_date', 'day') ).order_by('birth_date_month', 'birth_date_day').all() Months={ 1:'January', 2: 'February', 3:'March', 4: 'April', 5: 'May', 6: 'June', 7: 'July', 8: 'August', 9: 'September', 10: 'October', 11: 'November', 12: 'December', } context={ 'users':users, 'month':Months } return render(request,'dashboard/birthdaylist.html',context=context) else: return redirect('login')
def list(self, request, *args, **kwargs): queryset = self.get_queryset() from_date = self.request.query_params.get('from_date', None) to_date = self.request.query_params.get('to_date', None) filter = self.request.query_params.get('filter', None) # range_type = self.request.query_params.get('range_type', 'week_day') if from_date: from_date = dateutil.parser.parse(from_date) queryset = queryset.filter(created_at__gte=from_date) if to_date: to_date = dateutil.parser.parse(to_date) queryset = queryset.filter(created_at__lte=to_date) if filter: queryset = queryset.filter(primary_content_type=filter) queryset = queryset.filter(is_deleted_by_instagram_user=False) queryset = queryset.annotate( year=Extract('created_at', 'year'), month=Extract('created_at', 'month')).values('year', 'month').annotate(Count('id')) return Response(queryset)
def grafico_mensal_despesa(usuario): #dia e quantidade de despesas hoje= get_hoje() mes_atual= hoje.month resultado = [] query = Movimentacao.objects.filter(cod_usuario=usuario, data_lancamento__month=mes_atual)#filtrando movimentações relizadas no mes atual query = query.filter( valor_pago__isnull=False, valor_pago__lt=0)#filtrando despesas query = query.annotate(dia=Extract("data_lancamento","day")).annotate(mes=Extract("data_lancamento","month")) query = query.values("dia","mes","data_lancamento") quantidade_de_despesas=[] dias_do_mes=[] queryLista = list(query) for obj in queryLista:#removendo dias duplicados if obj not in dias_do_mes: dias_do_mes.append(obj) for obj in dias_do_mes:#contagem de despesas pagas por dia qtd_aux=len(query.filter(data_lancamento__day=obj["dia"])) qtd_aux2={"quantidade":qtd_aux} quantidade_de_despesas.append(qtd_aux2) #formatação resposta for (d,q) in zip(dias_do_mes, quantidade_de_despesas): qtd= {"data":d["data_lancamento"],"quantidade":q["quantidade"]} resultado.append(qtd) return resultado
def get(request, number=None): content = {} if len(request.query_params) == 0: content = (Charge.objects.filter(account__number=number).annotate( month=Extract('transactedAt', 'month')).values( 'month').annotate(total=Sum('value')).annotate( year=Extract('transactedAt', 'year')).values( 'year', 'month', 'total').order_by('year', 'month').values_list( 'year', 'month', 'total')) elif (request.query_params.get('date_from', None) and request.query_params.get('date_to', None)) is not None: date_from = (datetime.strptime( request.query_params.get('date_from'), '%Y-%m-%d').replace(tzinfo=UTC)) date_to = ((datetime.strptime(request.query_params.get('date_to'), '%Y-%m-%d') + timedelta(days=1)).replace(tzinfo=UTC)) content = (Charge.objects.filter( account__number=number, transactedAt__range=[ date_from, date_to ]).annotate(month=Extract('transactedAt', 'month')).values( 'month').annotate(total=Sum('value')).annotate( year=Extract('transactedAt', 'year')).values( 'year', 'month', 'total').order_by('year', 'month').values_list( 'year', 'month', 'total')) return Response(content, status=200)
def consulta(self, ano, mes): qFinal = MovimientoVentaBiable.objects.values( 'item_biable__descripcion', 'item_biable__descripcion_dos', 'item_biable__categoria_mercadeo', 'item_biable__categoria_mercadeo_dos', 'item_biable__categoria_mercadeo_tres', 'item_biable__serie', 'item_biable__id_item', 'factura__vendedor__linea_ventas__nombre', 'factura__cliente__nombre' ).annotate( year=Extract('factura__fecha_documento', 'year'), month=Extract('factura__fecha_documento', 'month'), vendedor=Upper( Case( When(factura__vendedor__colaborador__isnull=True, then=F('factura__vendedor__nombre')), default=Concat( 'factura__vendedor__colaborador__usuario__user__first_name', Value(' '), 'factura__vendedor__colaborador__usuario__user__last_name' ), output_field=CharField(), )), venta_neta=Sum('venta_neto'), cantidad_neta=Sum('cantidad'), ).filter(factura__fecha_documento__year__in=ano, month__in=mes, factura__activa=True) return qFinal
def resolve_upcoming_occasions(self, info, **kwargs): user = info.context.user days = kwargs.get('lead_time', 7) now = datetime.now() then = now + timedelta(days) # Get number of days in current month _, days_in_month = monthrange(now.year, now.month) ### Solution for querying credited to "twneale" # https://stackoverflow.com/questions/6128921/queryset-of-people-with-a-birthday-in-the-next-x-days # Build the list of month/day tuples. monthdays = [(now.month, now.day)] while now <= then: monthdays.append((now.month, now.day)) now += timedelta(days=1) monthdays = (dict(zip(("date__month", "date__day"), t)) for t in monthdays) # Compose the djano.db.models.Q objects together for a single query. query = reduce(operator.or_, (Q(**d) for d in monthdays)) # Query for matching monthday pairs for the authorized user. occasions = Occasion.objects.filter(query, contact__user_id=user.id) # Extract the day and month fields as integers occasions = occasions.annotate(month=Cast(Extract('date', 'month'), IntegerField()), day=Cast(Extract('date', 'day'), IntegerField())) # Order by "time from now", evaluate expression, and return return occasions.order_by(((F('month') - now.month) + 12) % 12, 'day')
def overview(request, s, *args, **kwargs): now = datetime.datetime.now() server = Server.objects.get(id=s) query = UserConnection.objects.annotate(day=Extract('disconnected', 'day'), month=Extract('disconnected', 'month'), year=Extract('disconnected', 'year')) month = [] subquery = query.filter(month=now.month, year=now.year, server=server)\ .values('user', 'day')\ .annotate(active=Count('user', distinct=True)) for day in range(1, now.day): month.append((day, subquery.filter(day=day).count())) ever = [] subquery = query.filter(server=server)\ .values('user', 'year')\ .annotate(active=Count('user', distinct=True)) for year in range(now.year - 2, now.year + 1): ever.append((year, subquery.filter(year=year).count())) loc = None with urllib.request.urlopen("https://geoip-db.com/json/{}".format(server.ip)) as url: data = json.loads(url.read().decode()) loc = '{}, {}'.format(data['city'], data['country_name']) if data['city'] else data['country_name'] return render(request, 'components/servers/detailed/overview.pug', {'data': server, 'months': month, 'years': ever, 'location': loc, 'status': status(server)})
class CasesPaluViewsets(viewsets.ModelViewSet): queryset = (CasesPalu.objects.annotate( year=Extract("reporting_date", "year"), week=Extract("reporting_date", "week"), ).values("year", "week").annotate(simple=Sum("simple")).annotate( acute=Sum("acute")).annotate( pregnant_women=Sum("pregnant_women")).annotate( decease=Sum("decease")).order_by("year", "week")) serializer_class = CasesPaluSerializer filter_backends = (django_filters.rest_framework.DjangoFilterBackend, ) filter_fields = ( "report__facility__district__province", "report__facility__district", "report__facility", ) def get_queryset(self): startdate = self.request.GET.get("startdate", "") enddate = self.request.GET.get("enddate", "") if startdate and startdate != "undefined": self.queryset = self.queryset.filter( reporting_date__week__gte=datetime.datetime.strptime( startdate, "%Y-%m-%d").isocalendar()[1]) if enddate and enddate != "undefined": self.queryset = self.queryset.filter( reporting_date__week__lte=datetime.datetime.strptime( enddate, "%Y-%m-%d").isocalendar()[1]) return self.queryset
def get(self, request, *args, **kwargs): month = int(kwargs.get('month', datetime.now().month)) year = int(kwargs.get('year', datetime.now().year)) self.object_list = self.get_queryset() self.object_list = self.object_list.annotate(year=Extract('created', 'year'), month=Extract('created', 'month')). \ filter(month=month, year=year).order_by('created') context = self.get_context_data() return self.render_to_response(context)
def stats(cls, events=None): ''' Returns stats on the events queryset provided. :param events: A queryset of events, that have the fields sessions, games, players ''' if events is None: events = cls.implicit() if events: # Tailwind's Median aggregator does not work on Durations (PostgreSQL Intervals) # So we have to convert it to Epoch time. Extract is a Django method that can extract # 'epoch' which is the documented method of casting a PostgreSQL interval to epoch time. # https://www.postgresql.org/message-id/19495.1059687790%40sss.pgh.pa.us # Django does not document 'epoch' alas but it works: # https://docs.djangoproject.com/en/4.0/ref/models/database-functions/#extract # We need a Django ExpressionWrapper to cast the uration field to DurationField as # for some reason even though it's a PostgreSQL interval, Django still thinks of it # as a DateTimeField (from the difference of two DateTimeFields I guess and a bug/feature) # that fails tor ecast a difference of DateTimeFiled's as DurationField. epoch_duration = Extract(ExpressionWrapper(F('duration'), output_field=DurationField()), lookup_name='epoch') epoch_gap = Extract(ExpressionWrapper(F('gap_time'), output_field=DurationField()), lookup_name='epoch') result = events.aggregate(Min('sessions'), Avg('sessions'), Median('sessions'), Max('sessions'), Min('games'), Avg('games'), Median('games'), Max('games'), Min('players'), Avg('players'), Median('players'), Max('players'), duration__min=Min('duration'), duration__avg=Avg('duration'), duration__median=Median(epoch_duration), duration__max=Max('duration'), gap__min=Min('gap_time'), gap__avg=Avg('gap_time'), gap__median=Median(epoch_gap), gap__max=Max('gap_time')) # Aggregate is a QuerySet enpoint (i.e results in evaluation of the Query and returns # a standard dict. To wit we can cast teh Epch times back to Durations for the consumer. result['duration__median'] = timedelta(seconds=result['duration__median']) result['gap__median'] = timedelta(seconds=result['gap__median']) else: result = None return result
def get_requests_count(queryset): """ This function returns a list of dictionaries containing each one the requests count per minute of a certain ``Tracker``s queryset. :param queryset: A Django QuerySet of ``Tracker``s. :return: List of dictionaries with the requests count per minute. """ return queryset.annotate(date=TruncDate('timestamp'), hour=Extract('timestamp', 'hour'), minute=Extract('timestamp', 'minute')).values( 'date', 'hour', 'minute').annotate(requests=Count('pk'))
def render_chart(request): labels = [] data = [] totals = {} if request.method == 'POST': reseller = Reseller.objects.get(pk=int(request.POST['reseller'])) month = request.POST.get('month') year = request.POST.get('year') view_type = request.POST.get('view_type') lst = [ 'Janaury', 'Febuary', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December' ] if int(view_type) == 1 and month: label = lst[int(month) - 1] queryset = Reseller.objects.filter(pk=int(request.POST['reseller'])).\ annotate(month=Extract('purchases__date_of_receipt', 'month')).\ annotate(year=Extract('purchases__date_of_receipt','year')).\ values('purchases__date_of_receipt', 'purchases__price_without_tax').\ filter(month=month, year=year).order_by('purchases__date_of_receipt').annotate(count=Sum('purchases__price_without_tax')) totals['month'] = 0 for entry in queryset: data.append(entry['count']) labels.append(entry['purchases__date_of_receipt']) totals['month'] += entry['count'] if int(view_type) == 2 and year: labels = [] data = [] totals = {} label = year label = lst[int(month) - 1] queryset = Reseller.objects.filter(pk=int(request.POST['reseller'])).\ annotate(year=Extract('purchases__date_of_receipt', 'year'), month=Extract('purchases__date_of_receipt', 'month')).\ values('month', 'purchases__price_without_tax').\ filter(year=year).order_by('year').annotate(count=Sum('purchases__price_without_tax')) totals['year'] = 0 for entry in queryset: data.append(entry['count']) labels.append(lst[int(entry['month']) - 1]) totals['year'] += entry['count'] return JsonResponse(data={ 'labels': labels, 'data': data, 'totals': totals })
def consulta(self, ano): qs = FacturasBiable.activas.all().values( 'vendedor__linea_ventas_id').annotate( v_bruta=Sum('venta_bruta'), v_neto=Sum('venta_neto'), Descuentos=Sum('dscto_netos'), Costo=Sum('costo_total'), renta=Sum('rentabilidad'), year=Extract('fecha_documento', 'year'), month=Extract('fecha_documento', 'month'), Margen=(Sum('rentabilidad') / Sum('venta_neto') * 100), ).filter(fecha_documento__year__in=list(map( lambda x: int(x), ano))).order_by('month') return qs
class MonthStatCollection(views.APIView): lookup_field = 'accId' queryset = ChargeModel.objects\ .filter(account=lookup_field)\ .annotate(year=Extract('date', 'year'), month=Extract('date', 'month'))\ .order_by('year', 'month')\ .values('year', 'month')\ .aggregate(summary=Sum('value')) serializer_class = MonthStatSerializer def get(self, request, accId, format=None): serializer = MonthStatSerializer(self.queryset, many=True) return Response(serializer.data)
def grafico_anual_saldo(usuario): hoje = datetime.strptime("2020-09-10", '%Y-%m-%d') ano= hoje.year query = Saldo.objects.filter(cod_usuario=usuario,mes_ano__year=ano) query= query.annotate(mes=Extract("mes_ano","month")).annotate(ano=Extract("mes_ano","year")).values("saldo","mes","ano") resultado = list() query = list(query) # Formata o resultado para a resposta esperada for obj in query: valor = {"mes": obj["mes"], "saldo": round(float(obj["saldo"]), 2)} resultado.append(valor) return resultado
def test_extract_func_explicit_timezone_priority(self): start_datetime = microsecond_support(datetime(2015, 6, 15, 23, 30, 1, 321)) end_datetime = microsecond_support(datetime(2015, 6, 16, 13, 11, 27, 123)) 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) melb = pytz.timezone('Australia/Melbourne') with timezone.override(melb): model = DTModel.objects.annotate( day_melb=Extract('start_datetime', 'day'), day_utc=Extract('start_datetime', 'day', tzinfo=timezone.utc), ).order_by('start_datetime').get() self.assertEqual(model.day_melb, 16) self.assertEqual(model.day_utc, 15)
def people_birthdays_list(request): p = models.Person.objects.filter( gone_to_eternity=False, gone_to_another_church=False, gone=False, member=True, birthday__isnull=False, ) return render(request, template_name='bh/people_birthdays_list.html', context={ 'people': p.order_by(Extract('birthday', 'month'), Extract('birthday', 'day')), })
def stats(self, request, *args, **kwargs): """ stats on rates """ period = request.GET.get('period', 'month') if period not in ['week', 'month', 'year']: return Response("Invalid period", status=status.HTTP_400_BAD_REQUEST) rate_filter = RateFilter(request.GET, queryset=self.queryset, request=request) qs = rate_filter.qs.values('currency', 'base_currency') if period == 'month': qs = qs.annotate(month=Extract('value_date', 'month')) if period == 'week': qs = qs.annotate(week=Extract('value_date', 'week')) qs = qs.annotate(year=Extract('value_date', 'year'), avg=models.Avg('value'), max=models.Max('value'), min=models.Min('value'), std_dev=models.StdDev('value')).order_by( '-year', '-' + period) results = [{ 'currency': result['currency'], 'base_currency': result['base_currency'], 'period': f"{result['year']}-{str(result[period]).zfill(2)}" if period != 'year' else result['year'], 'avg': result['avg'], 'max': result['max'], 'min': result['min'], 'std_dev': result['std_dev'], } for result in qs] data = { 'key': request.GET.get('key'), 'period': period, 'from_date': request.GET.get('from_date', ''), 'to_date': request.GET.get('to_date', ''), 'results': results } serializer = RateStatSerializer(data) return Response(serializer.data, content_type="application/json")
def installations_last_six_months(cls): month_counts = cls.objects.filter( created_at__gte=datetime.datetime.now() - datetime.timedelta(days=6*30) ).annotate(month=Extract('ac_term', 'month')).values('month').annotate( count=Count('month') ) month_mapping = { 1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr', 5: 'Mäi', 6: 'Jun', 7: 'Jul', 8: 'Aug', 9: 'Sep', 10: 'Okt', 11: 'Nov', 12: 'Dez', } curr_month = datetime.datetime.now().month result = {} for i in range(6): month = curr_month - i if month < 1: month += 12 if month_counts.filter(month=month).exists(): count = month_counts.get(month=month)['count'] else: count = 0 result[month_mapping[month]] = count return result
def notify_filter(self, qs): # start with those who want notifications and have a push token qs = qs.filter(verified=True, notify=True, fcm_token__isnull=False, last_seen__isnull=False) qs = qs.exclude(fcm_token='') # exclude those last notified within their notify_interval due_for_notification = Q(last_notified__isnull=True) | Q( notify_interval__lt=datetime.now() - F('last_notified')) # annotate with quiet time in UTC local_to_utc = lambda field: Extract(field, 'epoch') + (F('utc_offset') * 60) qs = qs.annotate(utc_quiet_time_start=local_to_utc('quiet_time_start'), utc_quiet_time_end=local_to_utc('quiet_time_end')) # filter out those currently in quiet time null_qt = Q(quiet_time_start__isnull=True) | Q( quiet_time_end__isnull=True) current_time = datetime.utcnow().time() current_time_epoch = (current_time.hour * 60 * 60) + ( current_time.minute * 60) + current_time.second not_quiet_time = null_qt | Q( utc_quiet_time_start__gt=current_time_epoch, utc_quiet_time_end__lt=current_time_epoch) return qs.filter(due_for_notification & not_quiet_time).order_by('-last_seen')
def home(request): orders = Order.objects.all() customers = Customer.objects.all() non_closed_orders = orders.filter(Q(status='Abierta') | Q(status='En revisión')) duration = ExpressionWrapper(Now() - F('date_created'), output_field=fields.DurationField()) non_closed_orders = non_closed_orders.annotate(duration=duration) non_closed_orders = non_closed_orders.annotate(duration_days=Extract('duration', 'day')) non_closed_orders = non_closed_orders.order_by("date_created") if request.user.group == "taller": taller_tag = TagOrder.objects.get(name="taller") non_closed_orders = non_closed_orders.filter(order_tags=taller_tag) taller_orders = orders.filter(order_tags=taller_tag) opened = taller_orders.filter(status='Abierta').count() on_revision = taller_orders.filter(status='En revisión').count() closed = taller_orders.filter(status='Cerrada').count() else: opened = orders.filter(status='Abierta').count() on_revision = orders.filter(status='En revisión').count() closed = orders.filter(status='Cerrada').count() context = {'orders': non_closed_orders, 'customers': customers, 'opened': opened, 'on_revision': on_revision, 'closed': closed, 'to_date': datetime.now()} return render(request, 'accounts/dashboard.html', context)
def monthlys(request): if request.method == 'POST': month = request.POST['month'] months = Transact.objects.annotate( month_stamp=Extract('date', 'month')).filter(month_stamp=month).all() print(months) return render(request, 'monthly.html', {'data': months})
def report(request): from_date = request.GET['_date'] end_date = request.GET['date2'] query_res=NCRI.objects.filter(~Q(weekday='Friday'), date__lte=end_date, date__gte=from_date).\ values('personnelNo','fistName','department').order_by('fistName').\ annotate(saacadaha= Coalesce(Extract(Sum('total_time'), 'hours' ), Value(0)), \ tar=Count('date'), absent=Coalesce(Sum('absent'),Value(0)),\ fasax=Count('exception',filter=Q(exception__contains='Holi')),\ # check=Sum('check_in_time'),\ sick=Count('exception',filter=Q(exception__contains='Sick')), annual=Count('exception', filter=Q(exception__contains='Annual')), \ maalmaha_shaqa_kujira_bishan=ExpressionWrapper(F('tar')-F('absent')-F('fasax')-F('sick')-F('annual'), output_field=FloatField()), saacadaha_shaqada=ExpressionWrapper(F('tar')*8, output_field=FloatField()),\ maamlmaha_dhiman=ExpressionWrapper(F('tar')-F('maalmaha_shaqa_kujira_bishan'),output_field=FloatField()), \ saacaadaha_dhiman=ExpressionWrapper(F('saacadaha_shaqada')-F('saacadaha'),output_field=IntegerField()) ) # with connection.cursor() as cursor: # testquery=NCRI.objects.raw ('SELECT "ID_App_ncri"."personnelNo", "ID_App_ncri"."fistName", "ID_App_ncri"."department", COALESCE(SUM("ID_App_ncri"."total_time"), 0) AS "saacadaha", COUNT("ID_App_ncri"."date") AS "tar", COALESCE(SUM("ID_App_ncri"."absent"), 0) AS "absent", COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Holi%") AS "fasax", COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Sick%") AS "sick", COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Annual%") AS "annual", ((((COUNT("ID_App_ncri"."date") - COALESCE(SUM("ID_App_ncri"."absent"), 0)) - COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Holi%")) - COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Sick%")) - COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Annual%")) AS "maalmaha_shaqa_kujira_bishan", (COUNT("ID_App_ncri"."date") * 8) AS "saacadaha_shaqada", (COUNT("ID_App_ncri"."date") - ((((COUNT("ID_App_ncri"."date") - COALESCE(SUM("ID_App_ncri"."absent"), 0)) - COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Holi%")) - COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Sick%")) - COUNT("ID_App_ncri"."exception") FILTER (WHERE "ID_App_ncri"."exception" LIKE "%Annual%"))) AS "maamlmaha_dhiman", ((COUNT("ID_App_ncri"."date") * 8) - COALESCE(SUM("ID_App_ncri"."total_time"), 0)) AS "saacaadaha_dhiman" FROM "ID_App_ncri" WHERE (NOT ("ID_App_ncri"."weekday" = "Friday") AND "ID_App_ncri"."date" between "2019-10-01" AND "2019-10-31" GROUP BY "ID_App_ncri"."personnelNo", "ID_App_ncri"."fistName", "ID_App_ncri"."department" ORDER BY "ID_App_ncri"."fistName" ASC') # print("====",testquery) # for test in testquery: # print(test) # query = serializers.serialize('json', query_res, fields= ('personnelNo','fistName','department')) query = json.dumps(list(query_res), cls=DjangoJSONEncoder) #print(query) #print("_date ==",request.GET['_date']) context = {'query': query} return JsonResponse(context, safe=False)
def list(self, request, *args, **kwargs): cache_key = request.get_full_path() force_cache = request.GET.get('cache', False) returnRespone = cache.get(cache_key) if force_cache: cache_key = cache_key.replace(' ', '')[:-8] if not force_cache and returnRespone is not None: return Response(returnRespone) else: queryset = self.get_queryset() # 2016-12-02T17:00:25.910711 from_date = self.request.query_params.get('from_date', None) to_date = self.request.query_params.get('to_date', None) filter = self.request.query_params.get('filter', None) range_type = self.request.query_params.get('range_type', 'week_day') if from_date: from_date = dateutil.parser.parse(from_date) queryset = queryset.filter(created_at__gte=from_date) if to_date: to_date = dateutil.parser.parse(to_date) queryset = queryset.filter(created_at__lte=to_date) if filter: queryset = queryset.filter(primary_content_type=filter) queryset = queryset.filter(is_deleted_by_instagram_user=False) queryset = queryset.annotate(data=Extract( 'created_at', range_type)).values('data').annotate( count=Count('id')).values('data', 'count').order_by('data') cache.set(cache_key, queryset, 24 * 60 * 60) return Response(queryset)
def get_queryset(self): self.set_time_zone() last_month = datetime.today() - timedelta(days=30) members = PersonsGroups.objects.filter( group_id=self.kwargs['group_id']).values_list('person_id') queryset = PersonOnline.objects.values('is_watching').annotate( count_person=Count('person_id')).annotate( hour_online=Extract('dt_online', 'hour')).annotate( week_day=Extract('dt_online', 'dow')).filter( dt_online__gte=last_month, is_watching=True, person__in=members, week_day=self.kwargs['week']).values( 'count_person', 'hour_online').order_by('hour_online') return queryset
def home(request): if request.method == 'GET': all_posts = BlogPost.objects.all().order_by('-date') paginator = Paginator(all_posts, 10) # need to see what happe ns when u take the 1 out below after 'page'. request.GET is a querydict which is a subclass of a dictionary, # it has methods such as .get().get(item) https://docs.djangoproject.com/en/2.0/ref/request-response/#django.http.QueryDict. So request.GET is a # dictionary like object containing HTTP GET Parameters . So we are setting the page variable to represent a key value pair, 'page' being the key and 1 being the value # t = BlogPost.objects.annotate(year=Extract('date', 'year')).values_list('date', flat=True) x = BlogPost.objects.annotate( year_stamp=Extract('date', 'year')).values_list( 'year_stamp', flat=True ) #With extract you can only get the year month day as number #so need to find a way to extract month as a 3 letter word. Is there a way i can just translate the blogpost month to a 2 digit month and then use django #templating language to comnvert month into word t = list(x.distinct()) #doing it this way means ill have to add this to every view. how would i even add it to a page in accounts as blogpost model is a home model. page = request.GET.get('page', 1) try: all_posts = paginator.page(page) # //may need to change this afterwards. solution implement from medium.com/... # is different to how django docs do it. feeling it amy slowdatabse with large number of blogposts down as i am except PageNotAnInteger: # If page is not an integer, deliver first page. all_posts = paginator.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. all_posts = paginator.page(paginator.num_pages) args = {'allposts': all_posts, 'paginator': paginator, 't': t} return render(request, 'home/home.html', args)
def weather_forecasts(self): """Returns recent and upcoming weather forecasts.""" now = datetime_to_unixtimestamp(timezone.now()) return self.location.weatherforecast_set \ .annotate(delta=Func(Extract('start_time', 'epoch') - now, function='ABS')) \ .filter(delta__lte=86400 * 3) \ .order_by('start_time')
def _front_page( paging_size=settings.PAGING_SIZE, page=0, add_filter={}, add_q=[], as_of=None, days_back=50, ): # TODO: weighting https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d # (P-1) / (T+2)^G if as_of is None: now = timezone.now() else: now = as_of if connection.vendor == "postgresql": now_value = Value(now, output_field=fields.DateTimeField()) submission_age_float = ExpressionWrapper( (now_value - F("created_at")), output_field=fields.DurationField()) submission_age_hours = ExpressionWrapper( Extract(F("tf"), "epoch") / 60 / 60 + 2.1, output_field=fields.FloatField()) real_p = ExpressionWrapper(F("points") - 1, output_field=fields.FloatField()) formula = ExpressionWrapper(F("p") / (Power(F("tfh"), F("g")) + 0.001), output_field=fields.FloatField()) return (Story.objects.select_related("user").filter( duplicate_of__isnull=True).filter(points__gte=1).filter( created_at__gte=now - datetime.timedelta(days=days_back)).filter( created_at__lte=now).filter(**add_filter).annotate( tf=submission_age_float). annotate(tfh=submission_age_hours).annotate(p=real_p).annotate( g=Value(1.8, output_field=fields.FloatField())).annotate( formula=formula).order_by("-formula")[( page * paging_size):(page + 1) * (paging_size)]) elif connection.vendor == "sqlite": now_value = Value(now, output_field=fields.DateTimeField()) submission_age_float = ExpressionWrapper( (now_value - F("created_at")), output_field=fields.FloatField()) submission_age_hours = ExpressionWrapper( F("tf") / 60 / 60 / 1000000 + 2.1, output_field=fields.FloatField()) real_p = ExpressionWrapper(F("points") - 1, output_field=fields.FloatField()) formula = ExpressionWrapper(F("p") / (Power(F("tfh"), F("g")) + 0.001), output_field=fields.FloatField()) return (Story.objects.select_related("user").filter( duplicate_of__isnull=True).filter(points__gte=1).filter( created_at__gte=now - datetime.timedelta(days=days_back)).filter( created_at__lte=now).filter(**add_filter).annotate( tf=submission_age_float). annotate(tfh=submission_age_hours).annotate(p=real_p).annotate( g=Value(1.8, output_field=fields.FloatField())).annotate( formula=formula).order_by("-formula")[( page * paging_size):(page + 1) * (paging_size)]) else: raise NotImplementedError( "No frontpage magic for database engine %s implemented" % (connection.vendor))
def stats(self, request): """Get stats of the last 7 days.""" qs = self.get_queryset() qs = self.filter_queryset(qs) qs = qs.annotate(week=Extract('recorded', 'week'), year=Extract('recorded', 'year')).order_by( '-year', '-week').values('week', 'year').annotate( Avg('distance'), Avg('duration')) response = [] for item in qs: item['speed__avg'] = round( item['distance__avg'] / item['duration__avg'], 2) item['recorded'] = Week(item.get('year'), item.get('week')).monday() response.append(item) return Response(response)