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)
