def disaster_filter_function(filters: dict, download_type: str,
values: List[str]):
aggregation_mapping = {
"disaster_recipient": _disaster_recipient_aggregations
}
def_codes = filters["def_codes"]
query = filters.get("query")
award_type_codes = filters.get("award_type_codes")
award_filters = [
~Q(total_loan_value=0) | ~Q(total_obligation_by_award=0)
| ~Q(total_outlay_by_award=0)
]
if query:
query_text = query["text"]
q = Q()
for field in query["fields"]:
q |= Q(**{f"{field}__icontains": query_text})
award_filters.append(q)
if award_type_codes:
award_filters.append(Q(type__in=award_type_codes))
faba_filters = [
filter_by_defc_closed_periods(),
Q(disaster_emergency_fund__code__in=def_codes)
]
dollar_annotations = {
"inner_obligation":
Coalesce(Sum("transaction_obligated_amount"), 0),
"inner_outlay":
Coalesce(
Sum(
Case(
When(filter_by_latest_closed_periods(),
then=F("gross_outlay_amount_by_award_cpe")),
default=Value(0),
)),
0,
),
}
cte = With(
FinancialAccountsByAwards.objects.filter(
*faba_filters).values("award_id").annotate(
**dollar_annotations).exclude(inner_obligation=0,
inner_outlay=0))
return (cte.join(AwardSearchView,
award_id=cte.col.award_id).with_cte(cte).annotate(
total_obligation_by_award=cte.col.inner_obligation,
total_outlay_by_award=cte.col.inner_outlay).filter(
*award_filters).values(*values).annotate(
**aggregation_mapping[download_type]()))
def _covid_outlay_subquery(award_id_col: Optional[str] = "award_id") -> Q:
return Subquery(
FinancialAccountsByAwards.objects.filter(
filter_by_latest_closed_periods(),
award_id=OuterRef(award_id_col),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(
datetime.date(2020, 4, 1)),
).values("award_id").annotate(sum=Coalesce(
Sum("gross_outlay_amount_by_award_cpe"), 0
) + Coalesce(
Sum("ussgl487200_down_adj_pri_ppaid_undel_orders_oblig_refund_cpe"
), 0
) + Coalesce(
Sum("ussgl497200_down_adj_pri_paid_deliv_orders_oblig_refund_cpe"),
0)).values("sum"),
output_field=DecimalField(),
)
def subaward_annotations():
annotation_fields = {
"subaward_action_date_fiscal_year": FiscalYear("subaward__action_date"),
"prime_award_base_action_date_fiscal_year": FiscalYear("award__date_signed"),
"prime_award_period_of_performance_potential_end_date": Cast(
F("award__latest_transaction__contract_data__period_of_perf_potential_e"), DateField()
),
"prime_award_treasury_accounts_funding_this_award": Subquery(
Award.objects.filter(id=OuterRef("award_id"))
.annotate(value=StringAgg("financial_set__treasury_account__tas_rendering_label", ";", distinct=True))
.values("value"),
output_field=TextField(),
),
"prime_award_federal_accounts_funding_this_award": Subquery(
Award.objects.filter(id=OuterRef("award_id"))
.annotate(
value=StringAgg(
"financial_set__treasury_account__federal_account__federal_account_code", ";", distinct=True
)
)
.values("value"),
output_field=TextField(),
),
"usaspending_permalink": Concat(
Value(AWARD_URL), Func(F("award__generated_unique_award_id"), function="urlencode"), Value("/")
),
"prime_award_object_classes_funding_this_award": Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(), award_id=OuterRef("award_id"), object_class_id__isnull=False
)
.annotate(
value=ExpressionWrapper(
Concat(F("object_class__object_class"), Value(": "), F("object_class__object_class_name")),
output_field=TextField(),
)
)
.values("award_id")
.annotate(total=StringAgg("value", ";", distinct=True))
.values("total"),
output_field=TextField(),
),
"prime_award_program_activities_funding_this_award": Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(), award_id=OuterRef("award_id"), program_activity_id__isnull=False
)
.annotate(
value=ExpressionWrapper(
Concat(
F("program_activity__program_activity_code"),
Value(": "),
F("program_activity__program_activity_name"),
),
output_field=TextField(),
)
)
.values("award_id")
.annotate(total=StringAgg("value", ";", distinct=True))
.values("total"),
output_field=TextField(),
),
"prime_award_disaster_emergency_fund_codes": Case(
When(
broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(), award_id=OuterRef("award_id")
)
.annotate(
value=ExpressionWrapper(
Case(
When(
disaster_emergency_fund__code__isnull=False,
then=Concat(
F("disaster_emergency_fund__code"),
Value(": "),
F("disaster_emergency_fund__public_law"),
),
),
default=Value(None, output_field=TextField()),
output_field=TextField(),
),
output_field=TextField(),
)
)
.values("award_id")
.annotate(total=StringAgg("value", ";", distinct=True))
.values("total"),
output_field=TextField(),
),
)
),
"prime_award_outlayed_amount_funded_by_COVID-19_supplementals": Case(
When(
broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_by_latest_closed_periods(),
award_id=OuterRef("award_id"),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(datetime.date(2020, 4, 1)),
)
.values("award_id")
.annotate(sum=Sum("gross_outlay_amount_by_award_cpe"))
.values("sum"),
output_field=DecimalField(),
),
),
),
"prime_award_obligated_amount_funded_by_COVID-19_supplementals": Case(
When(
broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("award_id"),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(datetime.date(2020, 4, 1)),
)
.values("award_id")
.annotate(sum=Sum("transaction_obligated_amount"))
.values("sum"),
output_field=DecimalField(),
),
),
),
"prime_award_latest_action_date_fiscal_year": FiscalYear("award__latest_transaction__action_date"),
}
return annotation_fields
