def calculate_procent(self, obj):
run_current = "user__cards__run"
expression = "user__cards__cards_user__card"
date = expression + "__date"
run = Q(user__cards__cards_user__card__change__run=0)
time_remained = expression + "__change__time"
run_remained = expression + "__change__run"
run_initial = expression + "__change__initial_run"
return obj.annotate(now_days=ExtractDay(Now()),
now_month=ExtractMonth(Now()),
now_year=ExtractYear(Now()),
days=ExtractDay(date),
month=ExtractMonth(date),
year=ExtractYear(date),
).annotate(
passed_days=ExpressionWrapper(F('now_days') - F('days') + 31 * (F('now_month') - F('month')) + 365 * (
F('now_year') - F('year')), output_field=FloatField()),
run_total=F(run_remained) - F(run_initial),
run_passed=F(run_current) - F(run_initial),
).annotate(
procent=Case(
When(Q(passed_days__gt=0) & run, then=ExpressionWrapper(F('passed_days') / F(time_remained) * 100,
output_field=FloatField())),
When(~Q(run_passed__lte=0), then=ExpressionWrapper(F('run_passed') / F('run_total') * 100,
output_field=FloatField())),
default=0.0,
),
)
def test_extract_day_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)
self.assertQuerysetEqual(
DTModel.objects.annotate(extracted=ExtractDay(
'start_datetime')).order_by('start_datetime'),
[(start_datetime, start_datetime.day),
(end_datetime, end_datetime.day)], lambda m:
(m.start_datetime, m.extracted))
self.assertQuerysetEqual(
DTModel.objects.annotate(
extracted=ExtractDay('start_date')).order_by('start_datetime'),
[(start_datetime, start_datetime.day),
(end_datetime, end_datetime.day)], lambda m:
(m.start_datetime, m.extracted))
self.assertEqual(
DTModel.objects.filter(
start_datetime__day=ExtractDay('start_datetime')).count(), 2)
def calculate_average(self, obj):
return obj.annotate(now_days=ExtractDay(Now()),
now_month=ExtractMonth(Now()),
now_year=ExtractYear(Now()),
days=ExtractDay('user__date'),
month=ExtractMonth('user__date'),
year=ExtractYear('user__date'),
).annotate(
calculate_days=F('now_days') - F('days') + 31 * (F('now_month') - F('month')) + 365 * (
F('now_year') - F('year')),
run_passed=ExpressionWrapper(F('user__cards__run') - F('user__cards__initial_run'),
output_field=FloatField())
).annotate(average_speed=ExpressionWrapper(F('run_passed') / F('calculate_days'),
output_field=FloatField()))
def calculate_shareholder_profit(self,
net_profit,
invest_history,
invest_max_date=now()):
if not isinstance(invest_history, list):
raise ValueError("invest_history must be a list")
invest_max_date = invest_max_date.replace(day=1)
# print(todayDate.replace(day=1)
total_investment = self.model.objects.filter(
date__lt=invest_max_date).aggregate(
total_investor=Count('id'),
total_investment=Sum(F('amount') * (ExpressionWrapper(
ExtractDay(invest_max_date - TruncDate(F('date'))),
output_field=IntegerField())),
output_field=FloatField()))
sum = 0
for invest in invest_history:
if invest.date < invest_max_date:
profit = (invest.amount * (
(datetime.date(invest_max_date) - datetime.date(invest.date)).days) * net_profit) / \
total_investment[
'total_investment']
invest.profit = profit
invest.profit_percent = (profit * 100.00) / net_profit
sum += profit
else:
invest.profit = 0
invest.profit_percent = 0
return {
'invest_history': invest_history,
'total_profit': sum,
'total_profit_percent': (sum * 100.0) / net_profit
}
def matrizII(estacion, variable, periodo):
datos = []
obj_estacion = Estacion.objects.get(est_id=estacion.est_id)
consulta = (Medicion.objects.filter(est_id=estacion.est_id).filter(
var_id=variable).filter(med_fecha__year=periodo).annotate(
month=TruncMonth('med_fecha')).values('month'))
#valores de precipitación mensual
med_mensual = list(
consulta.annotate(suma=Sum('med_valor')).values(
'suma', 'month').order_by('month'))
datos_diarios = list(
Medicion.objects.filter(est_id=estacion.est_id).filter(
var_id=variable).filter(med_fecha__year=periodo).annotate(
month=ExtractMonth('med_fecha'),
day=ExtractDay('med_fecha')).values(
'month', 'day').annotate(valor=Sum('med_valor')).values(
'valor', 'month', 'day').order_by('month', 'day'))
max24H, maxdia, totdias = maximospre(datos_diarios)
for item in med_mensual:
obj_precipitacion = Precipitacion()
obj_precipitacion.est_id = obj_estacion
obj_precipitacion.pre_periodo = periodo
obj_precipitacion.pre_mes = item.get('month').month
obj_precipitacion.pre_suma = item.get('suma')
obj_precipitacion.pre_maximo = max24H[item.get('month').month - 1]
obj_precipitacion.pre_maximo_dia = maxdia[item.get('month').month - 1]
obj_precipitacion.pre_dias = totdias[item.get('month').month - 1]
datos.append(obj_precipitacion)
return datos
def get_queryset(self):
self.scope = get_request_or_fallback(self.request, "scope", "annualy", str,False)
self.year = get_request_or_fallback(self.request, "year", datetime.now().year, str, False)
self.month = get_request_or_fallback(self.request, "months", None, str, False)
self.day = get_request_or_fallback(self.request, "day", None, str, False)
if self.month != None : self.month = int(self.month)
if self.day != None: self.day = int(self.day)
qrySet = Timesheets.objects.filter(
time__year=self.year
).values(
"user__id",
"user__user__first_name",
"user__user__last_name"
).annotate(
year=ExtractYear("time", output_field=IntegerField()),
month=ExtractMonth("time", output_field=IntegerField()),
day=ExtractDay("time", output_field=IntegerField())
).annotate(
seconds=ExtractSecond("time", output_field=IntegerField()),
minutes=ExtractMinute("time", output_field=IntegerField()),
hours=ExtractHour("time", output_field=IntegerField())
).filter(getFilterIfContentAdmin(self.request)).order_by("time", "user__id")
return TimestampDisplay(TimeStampsManager(qrySet, self.year)).getScopedView(self.scope, self.month, self.day)
def batch_push_birthday(batch):
"""誕生日のプッシュ通知
本日誕生日の社員をスーパーユーザーと営業員にお知らせします。
:param batch:
:return:
"""
logger = batch.get_logger()
if batch.mail_template and batch.mail_template.mail_body and '%s' in batch.mail_template.mail_body:
gcm_url = models.Config.get_gcm_url()
today = datetime.date.today()
# 今日誕生日の社員
members = models.Member.objects.public_all().annotate(
month=ExtractMonth('birthday'), day=ExtractDay('birthday')).filter(
day='%02d' % today.day,
month='%02d' % today.month).exclude(member_type=4)
if members.count() > 0:
message = batch.mail_template.mail_body % u"、".join(
[unicode(m) for m in members])
# スーパーユーザーと営業員
users = User.objects.filter(Q(is_superuser=True) |
(Q(salesperson__isnull=False)
& Q(salesperson__is_retired=False)
& Q(salesperson__is_deleted=False)),
is_active=True)
push_notification(users, batch.mail_template.mail_title, message,
gcm_url)
logger.info(message)
else:
logger.info(u"今日(%s)誕生日の社員がいません。" % today.strftime('%Y-%m-%d'))
else:
logger.info(u"メール本文(Plain Text)が設定されていません。")
def computeHoursDaily(self, employee):
date = self.getDate()
querySet = Timesheets.objects.filter(
time__year=date.year
).values(
"user__id",
"user__user__first_name",
"user__user__last_name"
).annotate(
year=ExtractYear("time", output_field=IntegerField()),
month=ExtractMonth("time", output_field=IntegerField()),
day=ExtractDay("time", output_field=IntegerField())
).annotate(
seconds=ExtractSecond("time", output_field=IntegerField()),
minutes=ExtractMinute("time", output_field=IntegerField()),
hours=ExtractHour("time", output_field=IntegerField())
).filter(getFilterIfContentAdmin(self.request)).order_by("time", "user__id")
data = TimestampDisplay(TimeStampsManager(querySet, date.year )).getDailyView(
date.day,
date.month,
employee.id
)
if data == {}:
return (0,0)
else:
return data[employee.id]
def get(self, request):
user = request.user
if user.role == "doctor":
from datetime import date, timedelta
from django.db.models.functions import Concat, ExtractYear, ExtractMonth, ExtractDay
from core.users.models import LoginHistory
from django.db.models import DateTimeField, DateField, F, Count, Value, CharField
from django.db.models.functions import Cast
logins = LoginHistory.objects.all()
logins = logins.annotate(time=Concat(ExtractYear('date'),
Value('-'),
ExtractMonth('date'),
Value('-'),
ExtractDay('date'),
output_field=CharField()))
logins = logins.values('time').annotate(
number=Count('user')).order_by('time')
total_logins = 0
for login in logins:
total_logins += login['number']
average = int(total_logins / len(logins))
return Response(status=HTTP_200_OK,
data=[{
"time": str(date.today() + timedelta(days=1)),
"number": average
}, {
"time": date.today() + timedelta(days=2),
"number": average
}, {
"time": date.today() + timedelta(days=3),
"number": average
}])
else:
return Response(status=HTTP_400_BAD_REQUEST)
def get_data_gpu(delta, pk):
data = InformerData.objects.filter(informer_id=pk,
date__gte=timezone.now() -
timedelta(hours=delta))
msgs = data.values_list('msg', flat=True).distinct()
data_gpu = []
for msg in msgs:
data_msg = data.filter(msg=msg)
date_dict = {
1:
data_msg.values(hour=ExtractHour('date'),
minute=ExtractMinute('date')),
24:
data_msg.values(hour=ExtractHour('date')),
720:
data_msg.values(day=ExtractDay('date')),
8640:
data_msg.values(month=ExtractMonth('date'))
}
data_list_dict = date_dict[delta].annotate(
temperature_avg=Avg('temperature'))
os_x = []
os_y = []
for i in data_list_dict:
os_x.append(list(i.values())[0])
os_y.append(list(i.values())[1])
data_gpu.append({msg: [os_x, os_y]})
print(data_gpu)
return data_gpu
def getIncrementedData():
dataFromDM = dailyData.objects.values( day=ExtractDay('when'),
month=ExtractMonth('when'),
year = ExtractYear('when') ).annotate(Sum('confirmedCases'),
Sum('curedCases'),
Sum('deathCases'))
dataFromDM= dataFromDM.order_by('month')
print(dataFromDM)
print(len(dataFromDM))
incrementedConfirmedCases,incrementedCuredCases, incrementedDeathCases = dfd(int), dfd(int), dfd(int)
temp1, temp2, temp3 = 25435,5000,800
for i in dataFromDM:
d='{}/{}/{}'.format(i['day'],i['month'],i['year'])
incrementedConfirmedCases[d]=(i['confirmedCases__sum'] - temp1)
incrementedCuredCases[d]=(i['curedCases__sum'] - temp2)
incrementedDeathCases[d]=(i['deathCases__sum'] - temp3)
temp1 = i['confirmedCases__sum']
temp2 = i['curedCases__sum']
temp3 = i['deathCases__sum']
#print(i['confirmedCases__sum'],d)
print(incrementedConfirmedCases)
print(incrementedCuredCases)
print(incrementedDeathCases)
dateOfCnfInc ,dataOfCnfInc = list(incrementedConfirmedCases.keys()), list(incrementedConfirmedCases.values())
dateOfCurInc ,dataOfCurInc = list(incrementedCuredCases.keys()), list(incrementedCuredCases.values())
dateOfDthInc ,dataOfDthInc = list(incrementedDeathCases.keys()), list(incrementedDeathCases.values())
return dateOfCnfInc ,dataOfCnfInc,dateOfCurInc ,dataOfCurInc,dateOfDthInc ,dataOfDthInc
def get_queryset(self):
isContentAdmin = self.request.user.groups.filter(name=settings.ADMIN_GROUP).exists()
currUser = get_object_or_404(Employes, user__id=self.request.user.id)
if isContentAdmin:
addFilter = Q()
else:
addFilter = Q(user=currUser)
querySet = Timesheets.objects.values(
"user__user__last_name",
"user__user__first_name",
"user__id"
).annotate(
year=ExtractYear("recptTime"),
month=ExtractMonth("recptTime"),
day=ExtractDay("recptTime")
).filter(status='0').annotate(
nbTimb=Count("day")
).annotate(
odd=F("nbTimb") % 2
).filter(odd=True).filter(addFilter)
return querySet
def get_queryset(self):
year = self.request.query_params.get("year")
month = self.request.query_params.get("month")
date_end = self.request.query_params.get("date_end")
date_start = self.request.query_params.get("date_start")
# Set group by field
if year and month:
group_by = "day"
else:
group_by = "month"
# Initial querysets
cash_invoices = database.models.Invoice.objects.filter(credit=False)
payments = database.models.InvoiceCreditPayment.objects
# Handle date range filters
if month:
if not year:
raise ParseError("Provide year for month {0}".format(month))
cash_invoices = cash_invoices.filter(date_of_sale__month=month,
date_of_sale__year=year)
payments = payments.filter(date_of_payment__month=month,
date_of_payment__year=year)
elif year:
cash_invoices = cash_invoices.filter(date_of_sale__year=year)
payments = payments.filter(date_of_payment__year=year)
elif date_start and date_end:
cash_invoices = cash_invoices.filter(
date_of_sale__range=(date_start, date_end))
payments = payments.filter(date_of_payment__range=(date_start,
date_end))
else:
raise ParseError("Must provide a month, year or custom date range")
cash_invoices = cash_invoices.annotate(month=ExtractMonth('date_of_sale'), year=ExtractYear('date_of_sale'),
day=ExtractDay('date_of_sale'),
type=Value("invoice", output_field=models.CharField()))\
.values(group_by, "type")\
.annotate(cash=Sum('invoice_total'))
payments = payments.annotate(month=ExtractMonth('date_of_payment'), year=ExtractYear('date_of_payment'),
day=ExtractDay('date_of_payment'),
type=Value("credit_payment", output_field=models.CharField()))\
.values(group_by, "type")\
.annotate(cash=Sum('payment'))
return cash_invoices.union(payments).order_by(group_by)
def matrizIV(estacion,variable,periodo):
datos=[]
consulta=Medicion.objects.filter(est_id=estacion.est_id)\
.filter(var_id=variable.var_id).filter(med_fecha__year=periodo)\
.annotate(month=TruncMonth('med_fecha')).values('month')
consulta_max=(Medicion.objects.filter(est_id=estacion.est_id)
.filter(var_id=variable.var_id)
.filter(med_fecha__year=periodo).values('med_maximo').exists())
datos_diarios_max=list(Medicion.objects
.filter(est_id=estacion.est_id)
.filter(var_id=variable.var_id)
.filter(med_fecha__year=periodo)
.exclude(med_valor=0)
.annotate(month=ExtractMonth('med_fecha'),day=ExtractDay('med_fecha'))
.values('month','day')
.annotate(maximo=Max('med_maximo'),valor=Max('med_valor'))
.values('maximo','valor','month','day').order_by('month','day'))
datos_diarios_min=list(Medicion.objects
.filter(est_id=estacion.est_id)
.filter(var_id=variable.var_id)
.filter(med_fecha__year=periodo)
.exclude(med_valor=0)
.annotate(month=ExtractMonth('med_fecha'),day=ExtractDay('med_fecha'))
.values('month','day')
.annotate(minimo=Min('med_minimo'),valor=Min('med_valor'))
.values('minimo','valor','month','day').order_by('month','day'))
med_avg=list(consulta.exclude(med_valor=0).
annotate(media=Avg('med_valor')).values('media','month').order_by('month'))
maximo,maximo_dia = maximoshai(datos_diarios_max)
minimo,minimo_dia = minimoshai(datos_diarios_min)
for item in med_avg:
mes=item.get('month').month
obj_hai=HumedadAire()
obj_hai.est_id=estacion
obj_hai.hai_periodo=periodo
obj_hai.hai_mes=mes
obj_hai.hai_maximo=maximo[mes-1]
obj_hai.hai_maximo_dia=maximo_dia[mes-1]
obj_hai.hai_minimo=minimo[mes-1]
obj_hai.hai_minimo_dia=minimo_dia[mes-1]
obj_hai.hai_promedio=item.get('media')
datos.append(obj_hai)
return datos
def gerar_relatorio_pessoa(request, pessoa_cpf):
id_pessoa = cliente.objects.filter(ativo=True, cpf = pessoa_cpf).\
values('nome_cliente', 'id_cliente')
id = id_pessoa[0]['id_cliente']
vendas = ordem_de_venda.objects.filter(ativo=True,venda=True, id_cliente = id). \
select_related('id_lote_medicamento', 'id_cliente').select_related('id_medicamento').\
values('id_lote_medicamento__id_medicamento__nome_medicamento'). \
annotate(quant = Sum('quantidade', output_Field = FloatField()), \
nCompras = Count('quantidade', output_Field = FloatField()), \
aux = ExpressionWrapper(F('percentual_desconto')*F('quantidade'), output_field=FloatField()), \
diaS = Sum(ExtractDay('data_de_venda')*F('quantidade'), output_Field = FloatField())). \
annotate(descS = Sum('aux')). \
annotate( avg = ExpressionWrapper( F('quant')/F('nCompras'), output_field=FloatField())). \
annotate( desc = ExpressionWrapper( F('descS')/F('quant'), output_field=FloatField())). \
annotate( dia = ExpressionWrapper( F('diaS')/F('quant'), output_field=FloatField())). \
order_by('quant')
dt = datetime.now()
response = HttpResponse(content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename = Relatório de vendas - ' + id_pessoa[
0]['nome_cliente'] + str(date(dt.year, dt.month, dt.day)) + '.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Pessoas')
row_num = 0
columns = [
'id_lote_medicamento__id_medicamento__nome_medicamento', 'avg', 'dia',
'desc', 'quant'
]
style = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_string = "font: bold on; borders: bottom dashed"
style = xlwt.easyxf(style_string)
ws.write(row_num, 0, "Medicamento", style=style)
ws.write(row_num, 1, "Média por compra", style=style)
ws.write(row_num, 2, "Dia médio", style=style)
ws.write(row_num, 3, "Desconto médio", style=style)
ws.write(row_num, 4, "Quantidade comprada", style=style)
for row in vendas:
row_num += 1
for col_num in range(len(columns)):
ws.write(row_num, col_num, row[columns[col_num]])
wb.save(response)
return response
def post_details(req):
posts_per_day = Post.objects.annotate(
day=ExtractDay('created_at')).values('day').annotate(
count=Count('id')).order_by('day')
form = PostFilterForm()
return render(req, 'dashboard/posts.html', {
'postsPerDay': list(posts_per_day),
'form': form
})
def with_avg_daily_emails(self):
return self.annotate(
start=Cast(Min("email_headers__received_at"),
output_field=DateField()),
days=(ExtractDay(timezone.now() - F("start")) + 1),
avg_daily_emails=ExpressionWrapper(
F("emails_cnt") / F("days"),
output_field=FloatField()), # TODO: avoid 0 division
)
def view_data(request, patient_id):
"""
Shows answered questions data from a patient.
:param patient_id (int):
"""
answered_set = Patient.objects.get(
identifier=patient_id).answeredquestion_set
if 'search' in request.GET:
term = request.GET['search']
all_questions = answered_set.filter(
question__text__icontains=term, doctor=request.user.doctor).values(
'question', 'question__text').annotate(
n=Count('response')).order_by('question')
else:
all_questions = answered_set.filter(doctor=request.user.doctor).values(
'question', 'question__text').annotate(
n=Count('response')).order_by('question')
page = request.GET.get('page', 1)
paginator = Paginator(all_questions, settings.PAGE_SIZE)
try:
questions = paginator.page(page)
except PageNotAnInteger:
questions = paginator.page(1)
except EmptyPage:
questions = paginator.page(paginator.num_pages)
list_of_questions = dict()
for question in questions:
pie_data = list(
all_questions.filter(question_id=question['question']).values(
'response__text',
'response__order').annotate(n=Count('response')))
line_data = list(
all_questions.filter(question_id=question['question']).values(
'response', 'question').order_by('answer_date').annotate(
day=Cast(ExtractDay('answer_date'), CharField()),
month=Cast(ExtractMonth('answer_date'), CharField()),
year=Cast(ExtractYear('answer_date'), CharField()),
date=Concat('day',
Value('/'),
'month',
Value('/'),
'year',
output_field=CharField())).values(
'response__text', 'response__order',
'date'))
list_of_questions[question['question']] = {
'pie_data': pie_data,
'line_data': line_data
}
context = {
'questions': questions,
'list_of_questions': list_of_questions,
'patient': Patient.objects.get(identifier=patient_id)
}
return render(request, 'patients_manager/view_data.html', context)
def getpostperday(request):
data = dict()
data['posts'] = dict()
days = Post.objects.filter(
use_tf=True, ins_dt__month=today.month).annotate(
day=ExtractDay('ins_dt')).values('day').distinct()
for day in days:
data['posts'][day['day']] = Post.objects.filter(use_tf=True,
ins_dt__day=day['day'])
return render(request, "post/per_day.html", data)
def get_queryset(self):
ids = self.request.query_params.get("id")
year = self.request.query_params.get("year")
month = self.request.query_params.get("month")
group_by = self.request.query_params.get("group_by")
date_end = self.request.query_params.get("date_end")
date_start = self.request.query_params.get("date_start")
# Format id param into list
ids_list = ids.split(',') if ids else []
# Format group_by param into list
group_by_params = group_by.split(',') if group_by else ["customer"]
# Validate group_by params
if group_by_params and \
any(param not in ["customer", "day", "month", "year"] for param in group_by_params):
raise ParseError("Can only group by customer, day, month or year")
if "day" in group_by_params and "month" not in group_by_params and not month:
raise ParseError(
"Must group by both day and month when filtering by year or custom dates"
)
if "month" in group_by_params and year and month:
raise ParseError("Can not group by month when filtering by month")
# Initial queryset
queryset = database.models.Invoice.objects
# Handle ids filter
if ids_list:
queryset = queryset.filter(customer__in=ids_list)
# Handle date range filters
if month:
if not year:
raise ParseError("Provide year for month {0}".format(month))
queryset = queryset.filter(date_of_sale__month=month,
date_of_sale__year=year)
elif year:
queryset = queryset.filter(date_of_sale__year=year)
elif date_start and date_end:
queryset = queryset.filter(date_of_sale__range=(date_start,
date_end))
else:
raise ParseError("Must provide a month, year or custom date range")
queryset = queryset.annotate(month=ExtractMonth('date_of_sale'), year=ExtractYear('date_of_sale'),
day=ExtractDay('date_of_sale'))\
.values(*group_by_params)\
.annotate(sales=Sum('invoice_total'), profit=Sum('profit_total'))\
.order_by(*group_by_params)
return queryset
def get_statistics(self):
return self.aggregate(
emailbox_size=Coalesce(Sum('size'), 0),
emailbox_carbon=Coalesce(Sum('generated_carbon'), 0),
emailbox_carbon_forecast=Coalesce(Sum('carbon_yforecast'), 0),
emails_count=Count('pk'),
read=Count('pk', filter=Q(seen=True)),
received=Count('pk', filter=Q(owner__email=F('receiver_email'))),
created_since_months=Cast(
ExtractDay(timezone.now() -
Cast(Min('received_at'), output_field=DateField())),
output_field=FloatField()) / (365.25 / 12),
)
def __str__(self) -> str:
"""
Provide a sensible string for each animal or daily quest.
:return: string holding the animal name and date
"""
year = ExtractYear(self.date_started)
month = ExtractMonth(self.date_started)
day = ExtractDay(self.date_started)
if self.category == 'A':
text = '{}({}/{}/{})'.format(self.animal_name, month, day, year)
else:
text = 'Daily Quest for {}/{}/{}'.format(month, day, year)
return text
def get_queryset(self):
qs = super().get_queryset()
qs = qs.annotate(begin=Min('items__begin'), end=Max('items__end'))
qs = qs.annotate(nights=ExtractDay(F('end') - F('begin')))
qs = qs.annotate(headcount=Sum('items__headcount'))
overnights = ExtractDay(F('items__end') - F('items__begin')) * F('items__headcount')
qs = qs.annotate(overnights=ExpressionWrapper(Sum(overnights), output_field=models.DecimalField()))
qs = qs.annotate(price=Coalesce(Sum('items__price'), 0))
amount_pppn = ExtractDay(F('items__end') - F('items__begin')) * F('items__headcount') * F('items__price_pppn')
qs = qs.annotate(amount_pppn=ExpressionWrapper(Coalesce(Sum(amount_pppn), 0),
output_field=models.DecimalField()))
amount_pp = F('items__headcount') * F('items__price_pp')
qs = qs.annotate(amount_pp=ExpressionWrapper(Coalesce(Sum(amount_pp), 0), output_field=models.DecimalField()))
amount_pn = ExtractDay(F('items__end') - F('items__begin')) * F('items__price_pn')
qs = qs.annotate(amount_pn=ExpressionWrapper(Coalesce(Sum(amount_pn), 0), output_field=models.DecimalField()))
sub_amount_cot = ExpressionWrapper(ExtractDay(F('items__end') - F('items__begin')) * F('items__headcount'),
output_field=models.DecimalField())
amount_cot = Case(When(items__cotisation=True, then=sub_amount_cot))
qs = qs.annotate(amount_cot=ExpressionWrapper(Coalesce(Sum(amount_cot), 0), output_field=models.DecimalField()))
qs = qs.annotate(amount=Cast(F('price') + F('amount_pppn') + F('amount_pp') + F('amount_pn') + F('amount_cot'),
output_field=models.DecimalField(max_digits=8, decimal_places=2)))
qs = qs.annotate(deposit=Cast(F('amount') * .3,
output_field=models.DecimalField(max_digits=8, decimal_places=2)))
return qs
def temperature_chart(request):
form, chosen_month = create_form(request)
temperatures_current_month = Temperature.objects.filter(
date_time__month=chosen_month).annotate(
day=ExtractDay('date_time')).values('day', 'value')
chart_dict = create_chart_data(temperatures_current_month)
return render(
request, 'data/chart.html', {
'labels': list(chart_dict.keys()),
'data': list(chart_dict.values()),
'form': form,
})
def get_queryset(self):
qs = super().get_queryset()
qs = qs.annotate(nights=ExtractDay(F('end') - F('begin')))
qs = qs.annotate(overnights=ExpressionWrapper(F('nights') * F('headcount'), output_field=models.DecimalField()))
amount_pppn = Coalesce(F('overnights') * F('price_pppn'), 0)
qs = qs.annotate(amount_pppn=ExpressionWrapper(amount_pppn, output_field=models.DecimalField()))
amount_pp = Coalesce(F('headcount') * F('price_pp'), 0)
qs = qs.annotate(amount_pp=ExpressionWrapper(amount_pp, output_field=models.DecimalField()))
amount_pn = Coalesce(F('nights') * F('price_pn'), 0)
qs = qs.annotate(amount_pn=ExpressionWrapper(amount_pn, output_field=models.DecimalField()))
amount_cot = Coalesce(Case(When(cotisation=True, then=F('overnights'))), 0)
qs = qs.annotate(amount_cot=ExpressionWrapper(amount_cot, output_field=models.DecimalField()))
qs = qs.annotate(
amount=Coalesce(F('price'), 0) + F('amount_pppn') + F('amount_pp') + F('amount_pn') + F('amount_cot'))
return qs
def get_last_update():
data_banco = Embrapa_Last_Updated.objects.annotate(
day=ExtractDay('last_updated'),
month=ExtractMonth('last_updated'),
year=ExtractYear('last_updated'),
hour=ExtractHour('last_updated'),
minute=ExtractMinute('last_updated'),
second=ExtractSecond('last_updated'),
).values('day', 'month', 'year', 'hour', 'minute', 'second').get()
print(data_banco)
day = data_banco["day"]
month = data_banco["month"]
year = data_banco["year"]
hour = data_banco["hour"]
minute = data_banco["minute"]
second = data_banco["second"]
result_in_seconds_from_database = day * 86400 + month * 2592000 + year * 31104000 + hour * 3600 + minute * 60 + second
print("resultado do banco em segundos:")
print(result_in_seconds_from_database)
date_current = datetime.now()
format_date = str(date_current)
format_year = format_date[0:4]
format_year = int(format_year)
format_month = format_date[5:7]
format_month = int(format_month)
format_day = format_date[8:10]
format_day = int(format_day)
format_hour = format_date[11:13]
format_hour = int(format_hour)
format_minute = format_date[14:16]
format_minute = int(format_minute)
format_second = format_date[17:19]
format_second = int(format_second)
result_all_in_seconds_current = format_day * 86400 + format_month * 2592000 + format_year * 31104000 + format_hour * 3600 + format_minute * 60 + format_second
print(result_all_in_seconds_current)
return [result_all_in_seconds_current, result_in_seconds_from_database]
def GoogleChartData(request):
start_day = request.GET.get('start-day')
end_day = request.GET.get('end-day')
dt_start_day = datetime.datetime.strptime(start_day, '%Y-%m-%d')
dt_end_day = datetime.datetime.strptime(
end_day, '%Y-%m-%d') + datetime.timedelta(days=1)
datapoint = request.GET.get('datapoint')
building_pk, building, model, endpoint = datapoint.split('-')
# model에 따라 데이터 조회
if model == 'Ismart':
queryset = Ismart.objects.filter(
building__pk=building_pk,
datetime__range=(dt_start_day, dt_end_day)).order_by('datetime')
elif model == 'Weather':
station_id = Building.objects.get(
pk=building_pk).weather_station.station_id
queryset = Weather.objects.filter(
weather_station__station_id=station_id,
datetime__range=(dt_start_day, dt_end_day)).order_by('datetime')
queryset = queryset.annotate(timestamp=Concat(Value('Date('),
ExtractYear('datetime'),
Value(', '),
ExtractMonth('datetime') - 1,
Value(', '),
ExtractDay('datetime'),
Value(', '),
ExtractHour('datetime'),
Value(', '),
ExtractMinute('datetime'),
Value(', '),
ExtractSecond('datetime'),
Value(')'),
output_field=CharField()))
data = queryset.values_list('timestamp', endpoint)
result = {
'prop': {
'id': datapoint,
'label': building + '-' + model + '-' + endpoint,
'type': 'number'
},
'data': dict((x[0], x[1]) for x in data)
}
return HttpResponse(json.dumps(result, ensure_ascii=False),
content_type="application/json; charset=utf-8")
def GetStatOrderPeriod(start, end):
qs_count = Order.objects.filter(date_created__gte=start,
date_created__lte=end).count()
qs = Order.objects.filter(
date_created__gte=start, date_created__lte=end).annotate(
day=ExtractDay('date_created'), ).values('day').annotate(
n=Count('pk')).order_by('day')
q_mass = {}
for q in qs:
q_mass[q['day']] = q['n']
date_list_finish = []
date_list_full = []
base = datetime.datetime.now()
date_list = [base - datetime.timedelta(days=x) for x in range(0, 30)]
for d in date_list:
date_list_finish.append(int(d.strftime("%d")))
date_list_full.append(d.strftime("%d-%m-%Y"))
list_date = list(reversed(date_list_finish))
l = list(reversed(date_list_full))
q_date = []
q_finish = []
d = list_date
i = 30
y = 0
while y < i:
try:
if d[y] in q_mass:
q_finish.append([y, q_mass.get(d[y])])
q_date.append([y, d[y]])
y += 1
else:
q_finish.append([y, 0.1])
q_date.append([y, d[y]])
y += 1
except:
q_finish.append([y, 0.1])
y += 1
print(q_finish)
print(q_date)
print(qs_count)
return q_finish, qs_count
def fetch_all_project_details(project_details_list, user):
all_projects = ProjectDetails.objects.all().filter(user=user).\
annotate(month=ExtractMonth('date'), day=ExtractDay('date'))
for element in all_projects:
project_title = element.project_name
client_name = element.client_name
email = element.email
mobile_number = element.mobile
unique_id = element.unique_id
month = fetch_month_string(element.month)
day = element.day
current_element = {
'project_title': project_title,
'client_name': client_name,
'email': email,
'mobile_number': mobile_number,
'unique_id': unique_id,
'day': day,
'month': month
}
project_details_list.append(current_element)
def datos_horarios_json(est_id,var_id,fec_ini,fec_fin):
consulta=(Medicion.objects.filter(est_id=est_id)
.filter(var_id=var_id).filter(med_fecha__range=[fec_ini,fec_fin]))
consulta=consulta.annotate(year=ExtractYear('med_fecha'),
month=ExtractMonth('med_fecha'),
day=ExtractDay('med_fecha'),
hour=ExtractHour('med_fecha')
).values('year','month','day','hour')
if(var_id==1):
consulta=list(consulta.annotate(valor=Sum('med_valor')).
values('valor','year','month','day','hour').
order_by('year','month','day','hour'))
else:
consulta=list(consulta.annotate(valor=Avg('med_valor'),
maximo=Max('med_maximo'),minimo=Min('med_minimo')).
values('valor','maximo','minimo','year','month','day','hour').
order_by('year','month','day','hour'))
datos=[]
if len(consulta)>0:
for fila in consulta:
fecha_str = (str(fila.get('year'))+":"+
str(fila.get('month'))+":"+str(fila.get('day')))
fecha = datetime.strptime(fecha_str,'%Y:%m:%d').date()
hora=datetime.time(fila.get('hour'))
fecha_hora=datetime.combine(fecha,hora)
dato={
'fecha':fecha_hora,
'valor':fila.get('valor'),
'maximo':fila.get('maximo'),
'minimo':fila.get('minimo'),
}
datos.append(dato)
else:
datos={
'mensaje':'no hay datos'
}
return datos
