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()))
