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]