def test_float(self):
FloatModel.objects.create(f1=-27.55, f2=0.55)
obj = FloatModel.objects.annotate(f1_round=Round('f1'), f2_round=Round('f2')).first()
self.assertIsInstance(obj.f1_round, float)
self.assertIsInstance(obj.f2_round, float)
self.assertAlmostEqual(obj.f1_round, round(obj.f1))
self.assertAlmostEqual(obj.f2_round, round(obj.f2))
def test_decimal(self):
DecimalModel.objects.create(n1=Decimal('-12.9'), n2=Decimal('0.6'))
obj = DecimalModel.objects.annotate(n1_round=Round('n1'), n2_round=Round('n2')).first()
self.assertIsInstance(obj.n1_round, Decimal)
self.assertIsInstance(obj.n2_round, Decimal)
self.assertAlmostEqual(obj.n1_round, round(obj.n1))
self.assertAlmostEqual(obj.n2_round, round(obj.n2))
def get_queryset(self):
active = Count("optionwheel", filter=Q(optionwheel__is_active=True))
completed = Count("optionwheel",
filter=Q(optionwheel__is_active=False))
profit = Sum(F("optionwheel__total_profit") *
F("optionwheel__quantity"),
filter=Q(optionwheel__is_active=False),
output_field=fields.DecimalField())
collateral = Sum(F("optionwheel__collatoral") *
F("optionwheel__quantity"),
filter=Q(optionwheel__is_active=False),
output_field=fields.DecimalField())
total_wheels = Sum(F("optionwheel__quantity"),
filter=Q(optionwheel__is_active=False))
total_days_active_weighted_by_collateral = Sum(
F("optionwheel__total_days_active") * F("optionwheel__quantity") *
F("optionwheel__collatoral"),
filter=Q(optionwheel__is_active=False))
average_days = Cast(total_days_active_weighted_by_collateral,
fields.FloatField()) / Cast(
collateral, fields.FloatField())
annualized_rate_of_return = Power(
1 + profit / collateral,
BUSINESS_DAYS_IN_YEAR / Coalesce(average_days, 252))
users = User.objects.annotate(
active=active,
completed=completed,
profit=Round(100 * Coalesce(profit, 0)),
collateral=Round(100 * Coalesce(collateral, 0)),
return_percentage=Coalesce(profit / collateral, 0),
total_wheels=Coalesce(total_wheels, 0),
average_days=Coalesce(average_days, 0),
annualized_rate_of_return=Coalesce(annualized_rate_of_return, 0),
)
return users
def test_decimal(self):
DecimalModel.objects.create(n1=Decimal("-12.9"), n2=Decimal("0.6"))
obj = DecimalModel.objects.annotate(n1_round=Round("n1"),
n2_round=Round("n2")).first()
self.assertIsInstance(obj.n1_round, Decimal)
self.assertIsInstance(obj.n2_round, Decimal)
self.assertAlmostEqual(obj.n1_round, obj.n1, places=0)
self.assertAlmostEqual(obj.n2_round, obj.n2, places=0)
def test_float(self):
FloatModel.objects.create(f1=-27.55, f2=0.55)
obj = FloatModel.objects.annotate(f1_round=Round("f1"),
f2_round=Round("f2")).first()
self.assertIsInstance(obj.f1_round, float)
self.assertIsInstance(obj.f2_round, float)
self.assertAlmostEqual(obj.f1_round, obj.f1, places=0)
self.assertAlmostEqual(obj.f2_round, obj.f2, places=0)
def test_decimal_with_precision(self):
DecimalModel.objects.create(n1=Decimal("-5.75"), n2=Pi())
obj = DecimalModel.objects.annotate(
n1_round=Round("n1", 1),
n2_round=Round("n2", 5),
).first()
self.assertIsInstance(obj.n1_round, Decimal)
self.assertIsInstance(obj.n2_round, Decimal)
self.assertAlmostEqual(obj.n1_round, obj.n1, places=1)
self.assertAlmostEqual(obj.n2_round, obj.n2, places=5)
def test_float_with_precision(self):
FloatModel.objects.create(f1=-5.75, f2=Pi())
obj = FloatModel.objects.annotate(
f1_round=Round("f1", 1),
f2_round=Round("f2", 5),
).first()
self.assertIsInstance(obj.f1_round, float)
self.assertIsInstance(obj.f2_round, float)
self.assertAlmostEqual(obj.f1_round, obj.f1, places=1)
self.assertAlmostEqual(obj.f2_round, obj.f2, places=5)
def test_integer(self):
IntegerModel.objects.create(small=-20, normal=15, big=-1)
obj = IntegerModel.objects.annotate(
small_round=Round("small"),
normal_round=Round("normal"),
big_round=Round("big"),
).first()
self.assertIsInstance(obj.small_round, int)
self.assertIsInstance(obj.normal_round, int)
self.assertIsInstance(obj.big_round, int)
self.assertAlmostEqual(obj.small_round, obj.small, places=0)
self.assertAlmostEqual(obj.normal_round, obj.normal, places=0)
self.assertAlmostEqual(obj.big_round, obj.big, places=0)
def test_integer_with_precision(self):
IntegerModel.objects.create(small=-5, normal=3, big=-100)
obj = IntegerModel.objects.annotate(
small_round=Round("small", 1),
normal_round=Round("normal", 5),
big_round=Round("big", 2),
).first()
self.assertIsInstance(obj.small_round, int)
self.assertIsInstance(obj.normal_round, int)
self.assertIsInstance(obj.big_round, int)
self.assertAlmostEqual(obj.small_round, obj.small, places=1)
self.assertAlmostEqual(obj.normal_round, obj.normal, places=5)
self.assertAlmostEqual(obj.big_round, obj.big, places=2)
def test_integer(self):
IntegerModel.objects.create(small=-20, normal=15, big=-1)
obj = IntegerModel.objects.annotate(
small_round=Round('small'),
normal_round=Round('normal'),
big_round=Round('big'),
).first()
self.assertIsInstance(obj.small_round, int)
self.assertIsInstance(obj.normal_round, int)
self.assertIsInstance(obj.big_round, int)
self.assertEqual(obj.small_round, round(obj.small))
self.assertEqual(obj.normal_round, round(obj.normal))
self.assertEqual(obj.big_round, round(obj.big))
def _avg_rating(self, category):
"""Calculate the average rating for the given category."""
return int(
self.LecturerRating.filter(category=category)
.aggregate(avg=Coalesce(Round(models.Avg("rating")), models.Value(0.0)))
.get("avg", 0)
)
def get_nearest_places_from_lat_lng(lat, lng, topn=100, max_distance=5):
"""
lat: latitude of base location to calculate distances
lng: longitude of base location to calculate distances
topn: max number of regions to return
max_distance: maximum distance in km
returns list of regions in the descending order of distances
"""
base_point = Point(lng, lat)
nearest_regions = Region.objects.filter(
location__distance_lt=(base_point, D(km=max_distance))
).annotate(
distance=Round(Distance('location', base_point))
).order_by('distance')[:topn]
return [{
"id": region.id,
"name": region.name,
"location": {
"latitude": region.latitude,
"longitude": region.longitude,
},
"distance": region.distance.km,
} for region in nearest_regions]
def get(self, request, *args, **kwargs):
users = self.model.objects.all().annotate(
avg_price=Round(Avg('book__price')))
print(vars(users))
paginator = Paginator(users, 5)
page_number = request.GET.get('page', 1)
page = paginator.get_page(page_number)
is_paginated = page.has_other_pages()
if page.has_previous():
prev_url = '?page={}'.format(page.previous_page_number())
else:
prev_url = ''
if page.has_next():
next_url = '?page={}'.format(page.next_page_number())
else:
next_url = ''
args = {
'users': page,
'user_form': UserForm(),
'is_paginated': is_paginated,
'next_url': next_url,
'prev_url': prev_url
}
return render(request, self.template_name, args)
def DeudaVencidaClientesView(request):
model = AccountInvoice
deuda = AccountInvoice.objects.filter(
state='open',
journal_id=11).aggregate(sum=Round(Sum(F('residual'))))
template_name = "interfaces/dashboard.html"
# context_object_name = "obj" # renombramos el nombre del objeto para tener mas control. Por defecto Django lo llama "object"
# login_url = "bases:login" # definimos la url de login por si no lo estuvieramos
return render(request, template_name, {'obj': deuda})
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context["year_list"] = self.year_list
# aggregate returns dictionary
# get value from dictionary
summary_list = (BstmtDiv.objects.all().values('ticker').annotate(
Total=Round(Sum('bsdiv_amount')))).order_by('-Total')
context["summary_list"] = summary_list
return context
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context["year_list"] = self.year_list
context["month_list"] = self.month_list
# aggregate returns dictionary
# get value from dictionary
total_amount = round(BstmtDiv.objects.all().aggregate(
Sum('bsdiv_amount'))['bsdiv_amount__sum'])
summary_list = (BstmtDiv.objects.all().annotate(
year=ExtractYear('bsdiv_date')).values('year').annotate(
Total=Round(Sum('bsdiv_amount'))))
month_summary_list = (BstmtDiv.objects.all().annotate(
month=ExtractMonth('bsdiv_date')).values('month').annotate(
Total=Round(Sum('bsdiv_amount')))).order_by('month')
context["total_amount"] = total_amount
context["summary_list"] = summary_list
context["month_summary_list"] = month_summary_list
return context
def my_prices(request, day, customer):
'''
API endpoint that calculates average,
absolute_diff, percent_diff, along with
origin_code, price, destination_code
Parameters:
day (date) : Input day
customer (date) : Customer name
Returns:
list: Calculates average, absolute_diff, percent_diff, along with
origin_code, price, destination_code
'''
# converting data into dictionary format to serialiser
data = {"day": day, "customer": customer}
# serializer
serializer = MyPricesSerializer(data=data)
# if serialiser not valid
if not serializer.is_valid():
# return error message
content = {'message': str(serializer.errors)}
return Response(content, status=status.HTTP_400_BAD_REQUEST)
# filter by day
day_prices_obj = Prices.objects.filter(day=day)
# filter by customer
customer_prices_obj = day_prices_obj.filter(customer__customer=customer)
# fint the average
average_price = day_prices_obj.filter(
orig_code__in=customer_prices_obj.values_list('orig_code', flat=True),
dest_code__in=customer_prices_obj.values_list(
'dest_code',
flat=True)).aggregate(average_price=Round(Avg('price'))).get(
'average_price', 0.00)
# find the absoluteand percent difference
diff_obj_list = customer_prices_obj.annotate(absolute_diff=Cast(
Func(F('price') - average_price, function='ABS'),
FloatField()), ).annotate(
percent_diff=(F('absolute_diff') / average_price) * 100, ).values(
'absolute_diff',
'percent_diff',
'price',
origin_code=F('orig_code__code'),
destination_code=F('dest_code__code'),
)
# insert avarage price in result_set
result_set = [{**each, 'average': average_price} for each in diff_obj_list]
# response structure
success = [{"status": "success", "data": result_set}]
# returns response
return Response(success, status=status.HTTP_200_OK)
def save(self, *args, **kwargs):
"""
Override original save method to set the lineitem total and the loyalty
points earned
"""
if self.product.on_sale:
self.lineitem_total = self.product.sale_price * self.quantity
else:
self.lineitem_total = self.product.price * self.quantity
self.lineitem_points_earned = Round(self.lineitem_total * 10)
super().save(*args, **kwargs)
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
total_amount = round(
Dividend.objects.all().filter(div_date__year=self.get_year()).aggregate(Sum('amount'))['amount__sum'])
summary_list = (
Dividend.objects.all().filter(div_date__year=self.get_year()).annotate(
month=ExtractMonth('div_date')).values('month').annotate(
Total=Round(Sum('amount'))))
context["total_amount"] = total_amount
context["summary_list"] = summary_list
return context
class DeudaVencidaClientesDetalleView(LoginRequiredMixin, generic.ListView):
# model = AccountInvoice.objects.annotate(deuda=Sum(F('residual'))).filter(state='open', journal_id=11)
model = AccountInvoice
queryset = AccountInvoice.objects.filter(
state='open', journal_id=11).values(
'partner_id__ref', 'partner_id__name', 'partner_id__phone',
'partner_id__mobile').annotate(deuda=Round(Sum(F('residual'))))
# queryset = AccountInvoice.objects.filter(state='open', journal_id=11).values('partner_id').annotate(sum=Round(Sum(F('residual'))))
template_name = "interfaces/clientes_deuda.html"
context_object_name = "obj" # renombramos el nombre del objeto para tener mas control. Por defecto Django lo llama "object"
login_url = "bases:login" # definimos la url de login por si no lo estuvieramos
def getSubjectsScores():
'''
Returns a dictionary: {
1: {
'total': {'chinese': 240, 'english': 300, 'math': 220},
'avg': { 'chinese': 240, 'english': 290, 'math': 250 }
},
2: ...
}
'''
allTestNumbers = getAllTestNumbers()
subjectDict = {}
for time in allTestNumbers:
total = Grade.objects.filter(testNo=time) \
.aggregate(chinese=Sum('chinese'), english=Sum('english'), math=Sum('math'))
avg = Grade.objects.filter(testNo=time) \
.aggregate(chinese=Round(Avg('chinese')), english=Round(Avg('english')), math=Round(Avg('math')))
subjectDict[time] = {}
subjectDict[time]["total"] = total
subjectDict[time]["avg"] = avg
return subjectDict
def get_queryset(self):
queryset = super().get_queryset()
common_query = Q(owner=self.request.user,
deviceinfo__detected_mirai=True)
query = self.get_filter_q(set_filter_dict=True)
if self.request.GET.get('filter_by') == 'trust-score':
return queryset.annotate(
trust_score_prcnt=Round(Coalesce(F('trust_score'), 0.0) *
100)).filter(common_query
& query).distinct()
else:
return queryset.filter(common_query & query).distinct()
class DematTxnStatBuySellView(ListView):
model = DematTxn
# if pagination is desired
# paginate_by = 300
txn_amount = ExpressionWrapper(F('quantity') * F('txn_price'),
output_field=IntegerField())
queryset = DematTxn.objects.all(). \
annotate(txn_year=ExtractYear('txn_date')). \
values('txn_year', 'action').annotate(txn_amount=Round(Sum(txn_amount))). \
order_by('txn_year')
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
return context
def post(self, request, *args, **kwargs):
form = UserForm(request.POST, request.FILES)
users = self.model.objects.all().annotate(
avg_price=Round(Avg('book__price')))
paginator = Paginator(users, 5)
page_number = request.GET.get('page', 1)
page = paginator.get_page(page_number)
if form.is_valid():
form.save()
form = UserForm()
return redirect(reverse('user_list'))
args = {
'users': page,
'user_form': form,
}
return render(request, self.template_name, args)
def sort_ca_by_each(ct, ca):
result = []
answers_count = len(ca) / ct.cloze_count
ca = ca.order_by('cloze_num')
for ct_num in range(ct.cloze_count):
clozes = ca.filter(cloze_num=ct_num+1) \
.values(code=F('answer'), syntaxcheck=F('result')) \
.annotate(percentage=Count('answer')/answers_count*100) \
.annotate(percentage=Round('percentage')) \
.order_by('-percentage')
result.append({
'nr':'#{}'.format(ct_num+1),
'clozes':list(clozes)}) # [{'code': '""', 'percentage': 40}, {'code': '', 'percentage': 20}, {'code': '2', 'percentage': 20}, {'code': 'x', 'percentage': 20}]
return result
def get_nearest_places_names_from_lat_lng(lat, lng, topn=100, max_distance=5):
"""
lat: latitude of base location to calculate distances
lng: longitude of base location to calculate distances
topn: max number of regions to return
max_distance: maximum distance in km
returns list of name of regions in the descending order of distances
"""
base_point = Point(lng, lat)
nearest_regions = Region.objects.filter(
location__distance_lt=(base_point, D(km=max_distance))
).annotate(
distance=Round(Distance('location', base_point))
).order_by('distance')[:topn]
return nearest_regions.values_list('name', flat=True)
def post(self, request, projects=None):
dates = None
if request.data:
projects = projects.search(**request.data)
dates = request.data.get('days')
days = Day.objects.filter(project__in=projects)
if dates:
dates = [datetime.strptime(date, '%Y-%m-%d') for date in dates]
days = days.filter(date__in=dates)
# else:
# days = Day.objects.filter(project__user=profile, project__creator__isnull=False)
result = days.aggregate(sum=Round(Sum('project__money_per_day')),
days=Count('date', distinct=True),
projects=Count('project', distinct=True))
return Response(result)
class DematSumCapTypeView(ListView):
# model = DematSum
# if pagination is desired
# paginate_by = 300
# amfi_qset = Amfi.objects.filter(comp_isin=OuterRef('pk'))
# queryset = DematSum.objects.annotate(comp_rank=Subquery(amfi_qset.values('comp_rank'))).order_by('comp_rank')
# queryset = DematSum.objects.annotate(comp_rank=Subquery(amfi_qset.values('comp_rank')))
amfi_qs = Amfi.objects.filter(comp_isin=OuterRef("isin_code_id"))
queryset = DematSum.objects.all(). \
annotate(comp_rank=Subquery(amfi_qs.values('comp_rank')[:1])). \
annotate(cap_type=Lower(Trim(Subquery(amfi_qs.values('cap_type')[:1])))). \
values('cap_type'). \
annotate(cap_count=Count('cap_type')). \
annotate(cap_cost=Round(Sum('value_cost'))). \
order_by('cap_type')
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
return context
def majitele_view_post(request):
if request.method == "POST":
akce = request.POST.get('action_op')
pks = request.POST.getlist('vyber')
selected_objects = Majitele.objects.filter(pk__in=pks)
if akce == 'delete':
selected_objects.delete()
if akce == 'new':
return redirect('/admin/prvni/majitele/add/')
if akce == 'generovat':
Majitele.objects.all().update(cena_pole=F('cena_p') * F('v_pole'))
Majitele.objects.all().update(cena_les=F('cena_l') * F('v_les'))
Majitele.objects.all().update(cena_rok=F('cena_les') + F('cena_pole'))
Majitele.objects.all().update(hlasu=Round('v_celkem'))
return redirect('/pozemky/')
if akce == 'nastav_c_l':
cena_l_form = request.POST["cena"]
Majitele.objects.all().update(cena_l=cena_l_form)
if akce == 'nastav_c_p':
cena_p_form = request.POST["cena"]
Majitele.objects.all().update(cena_p=cena_p_form)
return render(request, 'tabrender.html', context={'table': MajiteleTable(Majitele.objects.all())})
def _datatables(self, *args, **kwargs):
"""
Process JQuery DataTables AJAX arguments (GET mode used, because of device list filter use GET params)
parameters description https://datatables.net/manual/server-side
:return: device list queryset, and additional DataTables params in self.ajax_info
"""
self.ajax_info['draw'] = self.request.GET.get(
'draw', '-') # this value should be repeated in response
start = self._get_int_arg('start', 0, 0) # start row of page [0..oo)
length = self._get_int_arg('length', -1,
-1) # page length or -1 for all [-1..oo)
queryset = self.get_queryset(*args, **kwargs)
self.ajax_info['recordsTotal'] = queryset.count(
) # total unfiltered records count
query = self.get_filter_q() # our filters
if self.request.GET.get('filter_by') == 'trust-score':
devices = queryset.annotate(
trust_score_prcnt=Round(Coalesce(F('trust_score'), 0.0) *
100)).filter(query).distinct()
else:
devices = queryset.filter(query).distinct()
self.ajax_info['recordsFiltered'] = devices.count(
) # total filtered records count
self.ajax_info['timestamp'] = timezone.now(
) # timestamp to be used by UI in "since" param to receive new nodes
if length == -1: # currently we have only 2 "modes":
if start == 0: # - with length = -1, then returns all records
return devices
else:
return devices[start:]
if start == 0: # - with length = N, then return first N records
return devices[:length]
else:
return devices[start:start + length]
