Python django.db.models.Sum() Examples

def test_execute_query_with_wildcard_tag_filter(self):
        """Test that data is filtered to include entries with tag key."""
        # Pick tags for the same month we query on later
        url = "?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly"
        query_params = self.mocked_query_params(url, AzureTagView)
        handler = AzureTagQueryHandler(query_params)
        tag_keys = handler.get_tag_keys()
        filter_key = tag_keys[0]
        tag_keys = ["tag:" + tag for tag in tag_keys]

        ag_key = "cost_total"
        with tenant_context(self.tenant):
            totals = AzureCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=self.dh.this_month_start
            ).aggregate(**{ag_key: Sum(F("pretax_cost") + F("markup_cost"))})

        url = f"?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly&filter[tag:{filter_key}]=*"  # noqa: E501
        query_params = self.mocked_query_params(url, AzureCostView)
        handler = AzureReportQueryHandler(query_params)

        data = handler.execute_query()
        data_totals = data.get("total", {})
        result = data_totals.get("cost", {}).get("total")
        self.assertIsNotNone(result)
        self.assertAlmostEqual(result.get("value"), totals[ag_key], 6) 

def update_equity(self):
        today, trading = await is_trading_day(datetime.datetime.today().replace(tzinfo=pytz.FixedOffset(480)))
        if trading:
            logger.info('更新资金净值 %s %s', today, trading)
            dividend = Performance.objects.filter(
                broker=self.__broker, day__lt=today.date()).aggregate(Sum('dividend'))['dividend__sum']
            if dividend is None:
                dividend = Decimal(0)
            perform = Performance.objects.filter(
                broker=self.__broker, day__lt=today.date()).order_by('-day').first()
            if perform is None:
                unit = Decimal(1000000)
            else:
                unit = perform.unit_count
            nav = self.__current / unit
            accumulated = (self.__current - dividend) / (unit - dividend)
            Performance.objects.update_or_create(broker=self.__broker, day=today.date(), defaults={
                'used_margin': self.__margin,
                'capital': self.__current, 'unit_count': unit, 'NAV': nav, 'accumulated': accumulated}) 

def get_context_data(self, **kwargs):
        context = super(IncomeExpenseReport, self).get_context_data(**kwargs)
        queryset = Split.objects.past().order_by()
        incomes = queryset.income().annotate(m=TruncMonth('date')).values('m').annotate(
            total=models.Sum('amount'))
        expenses = queryset.expense().annotate(m=TruncMonth('date')).values('m').annotate(
            total=models.Sum('amount'))
        result = []
        for i, e in zip(incomes, expenses):
            result.append({
                'month': i['m'],
                'income': i['total'],
                'expense': e['total'],
                'total': i['total'] + e['total']
            })
        context['result'] = result
        return context 

def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        user = self.request.user
        orgas = organization_manager.get_user_organizations(user)
        cumulated_turnovers = (orgas
            .aggregate(sum=Sum('invoices__total_excl_tax'))["sum"]) or D('0')
        cumulated_debts = (orgas
            .aggregate(sum=Sum('bills__total_excl_tax'))["sum"]) or D('0')
        cumulated_profits = cumulated_turnovers - cumulated_debts

        context["organizations_count"] = orgas.count()
        context["organizations_cumulated_turnovers"] = cumulated_turnovers
        context["organizations_cumulated_profits"] = cumulated_profits
        context["organizations_cumulated_active_days"] = 0

        context["organizations"] = orgas
        context["last_invoices"] = Invoice.objects.all()[:10]

        return context 

def get_balances(request, dstart, dend):
    try:
        dstart = datetime.datetime.strptime(dstart, '%Y-%m-%d').date()
        dend = datetime.datetime.strptime(dend, '%Y-%m-%d').date()
    except ValueError:
        return HttpResponseBadRequest(_('Invalid date format, expected yyyy-mm-dd'))
    balance = Split.objects.personal().exclude_transfers().filter(date__lt=dstart).aggregate(
            models.Sum('amount'))['amount__sum'] or 0
    splits = Split.objects.personal().exclude_transfers().date_range(dstart, dend).order_by('date')
    data_points = []
    labels = []
    days = (dend - dstart).days
    if days > 50:
        step = days / 50 + 1
    else:
        step = 1
    for split in splits:
        while split.date > dstart:
            data_points.append(balance)
            labels.append(datetime.datetime.strftime(dstart, '%Y-%m-%d'))
            dstart += datetime.timedelta(days=step)
        balance += split.amount
    data_points.append(balance)
    labels.append(datetime.datetime.strftime(dend, '%Y-%m-%d'))
    return JsonResponse({'labels': labels, 'data': data_points}) 

def runindex(request, event=None):
    event = viewutil.get_event(event)

    if not event.id:
        return HttpResponseRedirect(
            reverse('tracker:runindex', args=(Event.objects.latest().short,))
        )

    searchParams = {}
    searchParams['event'] = event.id

    runs = filters.run_model_query('run', searchParams)
    runs = runs.annotate(hasbids=Sum('bids'))

    return views_common.tracker_response(
        request, 'tracker/runindex.html', {'runs': runs, 'event': event},
    ) 

def calculate_credit(self, user, start, end):
        """
        Calculate approved days of type for user in given time frame.

        For absence types which fill worktime this will be None.
        """
        if self.fill_worktime:
            return None

        credits = AbsenceCredit.objects.filter(
            user=user, absence_type=self, date__range=[start, end]
        )
        data = credits.aggregate(credit=Sum("days"))
        credit = data["credit"] or 0

        return credit 

def update_total(self):
        if self.istarget:
            self.total = self.bids.filter(
                donation__transactionstate='COMPLETED'
            ).aggregate(Sum('amount'))['amount__sum'] or Decimal('0.00')
            self.count = self.bids.filter(
                donation__transactionstate='COMPLETED'
            ).count()
            # auto close this if it's a challenge with no children and the goal's been met
            if (
                self.goal
                and self.state == 'OPENED'
                and self.total >= self.goal
                and self.istarget
            ):
                self.state = 'CLOSED'
        else:
            options = self.options.exclude(
                state__in=('HIDDEN', 'DENIED', 'PENDING')
            ).aggregate(Sum('total'), Sum('count'))
            self.total = options['total__sum'] or Decimal('0.00')
            self.count = options['count__sum'] or 0 

def base_units_assigned(self):
        crs = TACourse.objects.filter(contract__application=self).exclude(contract__status__in=['CAN', 'REJ'])\
            .aggregate(Sum('bu'))
        return crs['bu__sum'] 

def filter_entities(self, queryset, name, value):
        if not value:
            return queryset
        return (
            queryset.filter(contract__entity__in=value)
            .distinct()
            .annotate(
                contracts_total=Sum(
                    "contract__amount_to_pay",
                    filter=Q(contract__parent=None, contract__entity__in=value),
                ),
                contracts_count=Count(
                    "contract",
                    filter=Q(contract__parent=None, contract__entity__in=value),
                ),
            )
        ) 

def test_TransferForm(self):
        data = {
            'title': 'transfer',
            'src': self.account.pk,
            'dst': self.personal.pk,
            'amount': 123,
            'date': '2017-01-01'
            }
        form = TransferForm(data)
        self.assertTrue(form.is_valid())
        transfer = form.save()
        self.assertIsInstance(transfer, Transaction)
        self.assertEqual(len(Account.objects.all()), 3)  # System account is also present
        self.assertEqual(len(Transaction.objects.all()), 1)
        self.assertEqual(len(Split.objects.all()), 2)
        self.assertEqual(Split.objects.all().aggregate(
            models.Sum('amount'))['amount__sum'], 0)
        self.assertTrue(Split.objects.get(
            account=self.personal, opposing_account=self.account, amount=123).is_transfer)
        self.assertTrue(Split.objects.get(
            account=self.account, opposing_account=self.personal, amount=-123).is_transfer) 

def test_populate_markup_cost_no_billsids(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.aggregate(markup=Sum(F("pretax_cost") * decimal.Decimal(0.1)))
            expected_markup = expected_markup.get("markup")
            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        self.accessor.populate_markup_cost(0.1, start_date, end_date, None)
        with schema_context(self.schema):
            query = self.accessor._get_db_obj_query(summary_table_name).aggregate(Sum("markup_cost"))
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def test_execute_query_with_wildcard_tag_filter(self):
        """Test that data is filtered to include entries with tag key."""
        url = "?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly"  # noqa: E501
        query_params = self.mocked_query_params(url, AWSTagView)
        handler = AWSTagQueryHandler(query_params)
        tag_keys = handler.get_tag_keys()
        filter_key = tag_keys[0]
        tag_keys = ["tag:" + tag for tag in tag_keys]

        with tenant_context(self.tenant):
            totals = AWSCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=self.dh.this_month_start
            ).aggregate(**{"cost": Sum(F("unblended_cost") + F("markup_cost"))})

        url = f"?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly&filter[tag:{filter_key}]=*"  # noqa: E501
        query_params = self.mocked_query_params(url, AWSCostView)
        handler = AWSReportQueryHandler(query_params)
        data = handler.execute_query()
        data_totals = data.get("total", {})
        result = data_totals.get("cost", {}).get("total", {}).get("value")
        self.assertEqual(result, totals["cost"]) 

def test_populate_markup_cost_no_billsids(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.aggregate(markup=Sum(F("unblended_cost") * decimal.Decimal(0.1)))
            expected_markup = expected_markup.get("markup")

            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        self.accessor.populate_markup_cost(0.1, start_date, end_date, None)
        with schema_context(self.schema):
            query = self.accessor._get_db_obj_query(summary_table_name).aggregate(Sum("markup_cost"))
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def filter_contractors_by_id(self, queryset, name, value):
        if not value:
            return queryset

        contracts = Contract.objects.filter(contractors__in=value).only("id")

        return (
            queryset.filter(contract__in=contracts)
            .distinct()
            .annotate(
                contracts_total=Sum(
                    "contract__amount_to_pay",
                    filter=Q(contract__parent=None, contract__in=contracts),
                ),
                contracts_count=Count(
                    "contract", filter=Q(contract__parent=None, contract__in=contracts)
                ),
            )
        ) 

def filter_entity_by_id(self, queryset, name, value):
        if not value:
            return queryset

        return (
            queryset.filter(contract__entity_id__in=value)
            .distinct()
            .annotate(
                contracts_total=Sum(
                    "contract__amount_to_pay",
                    filter=Q(contract__parent=None, contract__entity_id__in=value),
                ),
                contracts_count=Count(
                    "contract",
                    filter=Q(contract__parent=None, contract__entity_id__in=value),
                ),
            )
        ) 

def spending_over_time(self, request):
        fiscal_year = request.query_params.get("fiscal_year")

        queryset = self.filter_queryset(self.get_queryset())

        if fiscal_year:
            start_date, end_date = get_fiscal_year_range(int(fiscal_year))
            queryset = queryset.filter(
                date_of_grant__gte=start_date, date_of_grant__lte=end_date
            )

        queryset = (
            queryset.without_amendments()
            .annotate(month=TruncMonth("date_of_grant"))
            .values("month")
            .annotate(total=Sum("amount_to_pay"), count=Count("id"))
            .values("month", "total", "count")
            .order_by("month")
        )

        return Response(queryset) 

def test_execute_query_with_tag_filter(self):
        """Test that data is filtered by tag key."""
        url = "?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly"
        query_params = self.mocked_query_params(url, AzureTagView)
        handler = AzureTagQueryHandler(query_params)
        tag_keys = handler.get_tag_keys()
        filter_key = tag_keys[0]
        tag_keys = ["tag:" + tag for tag in tag_keys]

        ag_key = "cost_total"
        with tenant_context(self.tenant):
            labels = (
                AzureCostEntryLineItemDailySummary.objects.filter(usage_start__gte=self.dh.this_month_start)
                .filter(tags__has_key=filter_key)
                .values(*["tags"])
                .all()
            )
            label_of_interest = labels[0]
            filter_value = label_of_interest.get("tags", {}).get(filter_key)

            totals = (
                AzureCostEntryLineItemDailySummary.objects.filter(usage_start__gte=self.dh.this_month_start)
                .filter(**{f"tags__{filter_key}": filter_value})
                .aggregate(**{ag_key: Sum(F("pretax_cost") + F("markup_cost"))})
            )

        url = f"?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly&filter[tag:{filter_key}]={filter_value}"  # noqa: E501
        query_params = self.mocked_query_params(url, AzureCostView)
        handler = AzureReportQueryHandler(query_params)

        data = handler.execute_query()
        data_totals = data.get("total", {})
        result = data_totals.get("cost", {}).get("total")
        self.assertIsNotNone(result)
        self.assertAlmostEqual(result.get("value"), totals[ag_key], 6) 

def test_filter_org_unit(self):
        """Check that the total is correct when filtering by org_unit_id."""
        with tenant_context(self.tenant):
            org_unit = "R_001"
            org_group_by_url = f"?filter[org_unit_id]={org_unit}"
            query_params = self.mocked_query_params(org_group_by_url, AWSCostView)
            handler = AWSReportQueryHandler(query_params)
            org_data = handler.execute_query()
            # grab the expected totals
            ten_days_ago = self.dh.n_days_ago(self.dh.today, 10)
            expected = AWSCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=ten_days_ago,
                usage_end__lte=self.dh.today,
                organizational_unit__org_unit_id__icontains=org_unit,
            ).aggregate(
                **{
                    "cost_total": Sum(
                        Coalesce(F("unblended_cost"), Value(0, output_field=DecimalField()))
                        + Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
                    )
                }
            )
            # grab the actual totals for the org_unit_id filter
            org_cost_total = org_data.get("total").get("cost").get("total").get("value")
            org_infra_total = org_data.get("total").get("infrastructure").get("total").get("value")
            # make sure they add up
            expected_cost_total = expected.get("cost_total") or 0
            # infra and total cost match
            self.assertEqual(org_cost_total, expected_cost_total)
            self.assertEqual(org_infra_total, expected_cost_total) 

def test_populate_markup_cost(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        bills = self.accessor.get_cost_entry_bills_query_by_provider(self.azure_provider_uuid)
        with schema_context(self.schema):
            bill_ids = [str(bill.id) for bill in bills.all()]
            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.filter(cost_entry_bill__in=bill_ids).aggregate(
                markup=Sum(F("pretax_cost") * decimal.Decimal(0.1))
            )
            expected_markup = expected_markup.get("markup")

        query = self.accessor._get_db_obj_query(summary_table_name)

        self.accessor.populate_markup_cost(0.1, start_date, end_date, bill_ids)
        with schema_context(self.schema):
            query = (
                self.accessor._get_db_obj_query(summary_table_name)
                .filter(cost_entry_bill__in=bill_ids)
                .aggregate(Sum("markup_cost"))
            )
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def category_spending(request, dstart, dend):
    try:
        dstart = datetime.datetime.strptime(dstart, '%Y-%m-%d')
        dend = datetime.datetime.strptime(dend, '%Y-%m-%d')
    except ValueError:
        return HttpResponseBadRequest(_('Invalid date format, expected yyyy-mm-dd'))
    res = Split.objects.expense().past().date_range(dstart, dend).order_by('category').values(
        'category__name').annotate(spent=models.Sum('amount'))
    if res:
        res = [(e['category__name'] or 'No category', abs(e['spent'])) for e in res if e['spent']]
        categories, spent = zip(*res)
    else:
        categories, spent = [], []
    return JsonResponse({'categories': categories, 'spent': spent}) 

def _populate_storage_daily_table(self):
        """Populate the daily table."""
        included_fields = [
            "namespace",
            "pod",
            "report_period_id",
            "persistentvolumeclaim",
            "persistentvolume",
            "storageclass",
            "persistentvolume_labels",
            "persistentvolumeclaim_labels",
        ]
        annotations = {
            "node": Value(random.choice(self.line_items).get("node"), output_field=CharField()),
            "usage_start": F("report__interval_start"),
            "usage_end": F("report__interval_start"),
            "persistentvolumeclaim_capacity_bytes": Max("persistentvolumeclaim_capacity_bytes"),
            "persistentvolumeclaim_capacity_byte_seconds": Sum("persistentvolumeclaim_capacity_byte_seconds"),
            "volume_request_storage_byte_seconds": Sum("volume_request_storage_byte_seconds"),
            "persistentvolumeclaim_usage_byte_seconds": Sum("persistentvolumeclaim_usage_byte_seconds"),
            "cluster_id": F("report_period__cluster_id"),
            "cluster_alias": Value(self.cluster_alias, output_field=CharField()),
        }
        entries = OCPStorageLineItem.objects.values(*included_fields).annotate(**annotations)

        for entry in entries:
            entry["total_seconds"] = 3600
            daily = OCPStorageLineItemDaily(**entry)
            daily.save() 

def test_populate_markup_cost(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        bills = self.accessor.get_cost_entry_bills_query_by_provider(self.aws_provider.uuid)
        with schema_context(self.schema):
            bill_ids = [str(bill.id) for bill in bills.all()]

            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.filter(cost_entry_bill__in=bill_ids).aggregate(
                markup=Sum(F("unblended_cost") * decimal.Decimal(0.1))
            )
            expected_markup = expected_markup.get("markup")

        self.accessor.populate_markup_cost(0.1, start_date, end_date, bill_ids)
        with schema_context(self.schema):
            query = (
                self.accessor._get_db_obj_query(summary_table_name)
                .filter(cost_entry_bill__in=bill_ids)
                .aggregate(Sum("markup_cost"))
            )
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def get_spent_time(self, obj):
        """
        Calculate spent time for given project.

        Reports which are not billable or are in review are excluded.
        """
        reports = Report.objects.filter(
            task__project=obj, not_billable=False, review=False
        )
        data = reports.aggregate(spent_time=Sum("duration"))
        return duration_string(data["spent_time"] or timedelta()) 

def money_spent(self):
        return abs(Split.objects.filter(
                category=self, account__account_type=Account.PERSONAL,
                transaction__transaction_type=Transaction.WITHDRAW).aggregate(
            models.Sum('amount'))['amount__sum'] or 0) 

def filter_queryset(self, queryset):
        if self.request.user.is_anonymous():
            self.permission_denied(self.request)
        is_project = self.kwargs.get('is_project', None)
        pk = self.kwargs.get('pk', None)
        if is_project == "1":
            queryset = queryset.filter(project__id=pk)
        else:
            project_id = get_object_or_404(Site, pk=pk).project.id
            queryset = queryset.filter(Q(site__id=pk, project_stage_id=0) |Q (project__id=project_id))
        return queryset.annotate(sub_stage_weight=Sum(F('parent__weight'))) 

def stages_reorder(request):
    try:
        stages = request.data.get("stages")
        qs_list = []
        for i, stage in enumerate(stages):
            obj = Stage.objects.get(pk=stage.get("id"))
            obj.order = i+1
            obj.save()
            qs_list.append(obj.id)
        serializer = StageSerializer(Stage.objects.filter(pk__in=qs_list).annotate(
            sub_stage_weight=Sum(F('parent__weight'))), many=True)
        return Response({'data': serializer.data}, status=status.HTTP_200_OK)
    except Exception as e:
        return Response({'error': str(e)}, status=status.HTTP_400_BAD_REQUEST) 

def __init__(self, project):
        self.data = OrderedDict()
        
        
        data = Site.objects.filter(project_id = project.id).aggregate(
             unstarted = Sum(
                 Case(When(current_progress = 0, then= 1),
                      output_field = IntegerField())
             ),
             first = Sum(
                 Case(When(current_progress__gte = 1, current_progress__lt = 20, then= 1),
                      output_field = IntegerField())
             ),
             second = Sum(
                 Case(When(current_progress__gte = 20, current_progress__lt= 40, then= 1),
                      output_field = IntegerField())
             ),
             third = Sum(
                 Case(When(current_progress__gte = 40, current_progress__lt= 60, then= 1),
                      output_field = IntegerField())
             ),
             fourth = Sum(
                 Case(When(current_progress__gte = 60, current_progress__lt= 80, then= 1),
                      output_field = IntegerField())
             ),
             fifth = Sum(
                 Case(When(current_progress__gte = 80, current_progress__lt= 100, then= 1),
                      output_field = IntegerField())
             ),
            sixth = Sum(
                 Case(When(current_progress = 100, then = 1),
                      output_field = IntegerField())
             )
        )
        self.data['Unstarted'] = 0 if data['unstarted'] is None else data['unstarted']
        self.data['< 20'] = 0 if data['first'] is None else data['first']
        self.data['20 - 40'] = 0 if data['second'] is None else data['second']
        self.data['40 - 60'] = 0 if data['third'] is None else data['third']
        self.data['60 - 80'] = 0 if data['fourth'] is None else data['fourth']
        self.data['80 <'] = 0 if data['fifth'] is None else data['fifth']
        self.data['Completed'] = 0 if data['sixth'] is None else data['sixth'] 

def count(self, key=None):
        qs = super(StatsManager, self).get_queryset()
        if key:
            qs = qs.filter(key=key)
        return qs.aggregate(Sum('value'))['value__sum'] 

def get_queryset(self):
        queryset = Report.objects.all()
        queryset = queryset.annotate(
            year=ExtractYear("date"), month=ExtractMonth("date")
        )
        queryset = queryset.values("year", "month")
        queryset = queryset.annotate(duration=Sum("duration"))
        queryset = queryset.annotate(pk=F("year") * 100 + F("month"))
        return queryset