def funding(self): return list(latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.defc).values( "disaster_emergency_fund_code").annotate( def_code=F("disaster_emergency_fund_code"), amount=Sum("total_budgetary_resources_cpe"), ).values("def_code", "amount"))
def total_obligations(self, funding): remaining_balance = latest_gtas_of_each_year_queryset().order_by( "-fiscal_year").first() if remaining_balance: remaining_balance = remaining_balance.unobligated_balance_cpe else: remaining_balance = Decimal("0.0") return sum([elem["amount"] for elem in funding]) - remaining_balance
def total_outlays(self): return ( latest_gtas_of_each_year_queryset() .filter(disaster_emergency_fund_code__in=self.defc) .values("gross_outlay_amount_by_tas_cpe") .aggregate(total=Sum("gross_outlay_amount_by_tas_cpe"))["total"] or 0.0 )
def total_obligations(self): return (latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.defc).values( "obligations_incurred_total_cpe", "deobligations_or_recoveries_or_refunds_from_prior_year_cpe" ).aggregate(total=(Sum("obligations_incurred_total_cpe") - Sum( "deobligations_or_recoveries_or_refunds_from_prior_year_cpe") ))["total"]) or 0.0
def total_outlays(self): return (latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.defc).values( "gross_outlay_amount_by_tas_cpe", "anticipated_prior_year_obligation_recoveries").aggregate( total=(Sum("gross_outlay_amount_by_tas_cpe") - Sum("anticipated_prior_year_obligation_recoveries") ))["total"]) or 0.0
def remaining_balances(self): remaining_balances = list(latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.defc).values( "fiscal_year").annotate( total=Sum("unobligated_balance_cpe")).values( "fiscal_year", "total").order_by("-fiscal_year")) return remaining_balances[0][ "total"] if remaining_balances else Decimal("0.0")
def funding(self): funding = list(latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.defc ).values("disaster_emergency_fund_code").annotate( def_code=F("disaster_emergency_fund_code"), amount= (Sum("total_budgetary_resources_cpe") - (Sum("budget_authority_unobligated_balance_brought_forward_cpe") + Sum("deobligations_or_recoveries_or_refunds_from_prior_year_cpe") + Sum("prior_year_paid_obligation_recoveries"))), ).values("def_code", "amount")) total_budget_authority = self.sum_values(funding, "amount") return funding, total_budget_authority
def funding(self): raw_values = (latest_gtas_of_each_year_queryset().values( "disaster_emergency_fund_code", ).annotate( budget_authority_appropriation_amount_cpe=Sum( "budget_authority_appropriation_amount_cpe"), other_budgetary_resources_amount_cpe=Sum( "other_budgetary_resources_amount_cpe"), )) return [{ "def_code": elem["disaster_emergency_fund_code"], "amount": elem["budget_authority_appropriation_amount_cpe"] + elem["other_budgetary_resources_amount_cpe"], } for elem in raw_values]
def funding(self): funding = list( latest_gtas_of_each_year_queryset() .filter(disaster_emergency_fund_code__in=self.defc) .values("disaster_emergency_fund_code") .annotate( def_code=F("disaster_emergency_fund_code"), amount=Sum("total_budgetary_resources_cpe"), unobligated_balance=Sum("budget_authority_unobligated_balance_brought_forward_cpe"), ) .values("def_code", "amount", "unobligated_balance") ) total_budget_authority = self.sum_values(funding, "amount") - self.sum_values(funding, "unobligated_balance") for entry in funding: del entry["unobligated_balance"] return funding, total_budget_authority
def total_outlays(self): return (latest_gtas_of_each_year_queryset().values( "gross_outlay_amount_by_tas_cpe").aggregate( total=Sum("gross_outlay_amount_by_tas_cpe"))["total"] or 0.0)
def total_queryset(self): cte_filters = [ Q(treasury_account__isnull=False), self.all_closed_defc_submissions, self.is_in_provided_def_codes, self.is_non_zero_total_spending, ] cte_annotations = { "funding_toptier_agency_id": F("treasury_account__funding_toptier_agency_id"), "obligation": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then= F("obligations_incurred_by_program_object_class_cpe" ) + F("deobligations_recoveries_refund_pri_program_object_class_cpe" ), ), default=Value(0), )), 0, ), "outlay": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then= F("gross_outlay_amount_by_program_object_class_cpe" ) + F("ussgl487200_down_adj_pri_ppaid_undel_orders_oblig_refund_cpe" ) + F("ussgl497200_down_adj_pri_paid_deliv_orders_oblig_refund_cpe" ), ), default=Value(0), )), 0, ), } cte = With( FinancialAccountsByProgramActivityObjectClass.objects.filter( *cte_filters).values( "treasury_account__funding_toptier_agency_id").annotate( **cte_annotations).values(*cte_annotations)) annotations = { "id": Subquery( Agency.objects.filter( toptier_agency_id=OuterRef("toptier_agency_id")).order_by( "-toptier_flag", "id").values("id")[:1]), "code": F("toptier_code"), "description": F("name"), # Currently, this endpoint can never have children w/o type = `award` & `award_type_codes` "children": Value([], output_field=ArrayField(IntegerField())), "award_count": Value(None, output_field=IntegerField()), "obligation": cte.col.obligation, "outlay": cte.col.outlay, "total_budgetary_resources": Coalesce( Subquery( latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.def_codes, treasury_account_identifier__funding_toptier_agency_id= OuterRef("toptier_agency_id"), ).annotate( amount=Func("total_budgetary_resources_cpe", function="Sum"), unobligated_balance=Func( "budget_authority_unobligated_balance_brought_forward_cpe", function="Sum"), deobligation=Func( "deobligations_or_recoveries_or_refunds_from_prior_year_cpe", function="Sum"), prior_year=Func( "prior_year_paid_obligation_recoveries", function="Sum"), ).annotate( total_budget_authority=F("amount") - F("unobligated_balance") - F("deobligation") - F("prior_year")).values("total_budget_authority"), output_field=DecimalField(), ), 0, ), "link": Exists( SubmissionAttributes.objects.filter( toptier_code=OuterRef("toptier_code"))), } return (cte.join( ToptierAgency, toptier_agency_id=cte.col.funding_toptier_agency_id).with_cte( cte).annotate(**annotations).values(*annotations))
def total_queryset(self): filters = [ self.is_in_provided_def_codes, self.is_non_zero_total_spending, self.all_closed_defc_submissions, Q(treasury_account__isnull=False), Q(treasury_account__federal_account__isnull=False), ] annotations = { "fa_code": F("treasury_account__federal_account__federal_account_code"), "description": F("treasury_account__account_title"), "code": F("treasury_account__tas_rendering_label"), "id": F("treasury_account__treasury_account_identifier"), "award_count": Value(None, output_field=IntegerField()), "fa_description": F("treasury_account__federal_account__account_title"), "fa_id": F("treasury_account__federal_account_id"), "obligation": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then= F("obligations_incurred_by_program_object_class_cpe" ), ), default=Value(0), )), 0, ), "outlay": Coalesce( Sum( Case( When( self.final_period_submission_query_filters, then= F("gross_outlay_amount_by_program_object_class_cpe" ), ), default=Value(0), )), 0, ), "total_budgetary_resources": Coalesce( Subquery( latest_gtas_of_each_year_queryset().filter( disaster_emergency_fund_code__in=self.def_codes, treasury_account_identifier=OuterRef( "treasury_account"), ).annotate( amount=Func("total_budgetary_resources_cpe", function="Sum"), unobligated_balance=Func( "budget_authority_unobligated_balance_brought_forward_cpe", function="Sum"), ).annotate(total_budget_authority=F("amount") - F("unobligated_balance")).values( "total_budget_authority"), output_field=DecimalField(), ), 0, ), } # Assuming it is more performant to fetch all rows once rather than # run a count query and fetch only a page's worth of results return (FinancialAccountsByProgramActivityObjectClass.objects.filter( *filters).values( "treasury_account__federal_account__id", "treasury_account__federal_account__federal_account_code", "treasury_account__federal_account__account_title", ).annotate(**annotations).values(*annotations.keys()))