def get_agency_data(self):
agency_filters = []
if self.filter is not None:
agency_filters.append(Q(name__icontains=self.filter) | Q(abbreviation__icontains=self.filter))
results = (
ToptierAgencyPublishedDABSView.objects.annotate(
fiscal_period=Subquery(
SubmissionAttributes.objects.filter(
submission_window__submission_fiscal_year=self.fiscal_year,
submission_window__submission_reveal_date__lte=now(),
toptier_code=OuterRef("toptier_code"),
)
.values("toptier_code", "submission_window__submission_fiscal_year")
.annotate(fiscal_period=Max("submission_window__submission_fiscal_month"))
.values("fiscal_period")
)
)
.filter(*agency_filters)
.annotate(
current_total_budget_authority_amount=Subquery(
ReportingAgencyOverview.objects.filter(
toptier_code=OuterRef("toptier_code"),
fiscal_year=self.fiscal_year,
fiscal_period=OuterRef("fiscal_period"),
).values("total_budgetary_resources")
),
periods=Subquery(
SubmissionAttributes.objects.filter(
reporting_fiscal_year=self.fiscal_year,
toptier_code=OuterRef("toptier_code"),
submission_window__submission_reveal_date__lte=now(),
)
.values("toptier_code")
.annotate(
period=ArrayAgg(
ConcatAll(
Value('{"period": '),
Cast("reporting_fiscal_period", output_field=TextField()),
Value(', "quarter": '),
Cast("reporting_fiscal_quarter", output_field=TextField()),
Value(', "submission_dates":{ "publication_date": "'),
Cast("published_date", output_field=TextField()),
Value('", "certification_date": "'),
Cast("certified_date", output_field=TextField()),
Value('"}, "quarterly": '),
Cast("quarter_format_flag", output_field=TextField()),
Value("}"),
output_field=TextField(),
)
)
)
.values("period"),
output_field=TextField(),
),
)
.values("name", "toptier_code", "abbreviation", "current_total_budget_authority_amount", "periods")
)
return self.format_results(results)
def queryset(self):
query = self.construct_loan_queryset(
ConcatAll("object_class__major_object_class", Value(":"), "object_class__object_class"),
ObjectClass.objects.annotate(join_key=ConcatAll("major_object_class", Value(":"), "object_class")),
"join_key",
)
annotations = {
"major_code": F("major_object_class"),
"description": Min("object_class_name"),
"code": F("object_class"),
"id_": Cast(Min("id"), output_field=TextField()),
"major_description": Min("major_object_class_name"),
"obligation": query.obligation_column,
"outlay": query.outlay_column,
"total_budgetary_resources": query.face_value_of_loan_column,
"award_count": query.award_count_column,
}
return query.queryset.values("major_object_class", "object_class").annotate(**annotations).values(*annotations)
def unique_file_c_awards(self):
delimiter = Value("|")
return ConcatAll("piid", delimiter, "parent_award_id", delimiter,
"fain", delimiter, "uri")
def award_financial_derivations(derived_fields):
derived_fields["award_type_code"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type",
"award__latest_transaction__assistance_data__assistance_type",
)
derived_fields["award_type"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type_desc",
"award__latest_transaction__assistance_data__assistance_type_desc",
)
derived_fields["awarding_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_agency_code",
"award__latest_transaction__assistance_data__awarding_agency_code",
)
derived_fields["awarding_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_agency_name",
"award__latest_transaction__assistance_data__awarding_agency_name",
)
derived_fields["awarding_subagency_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_sub_tier_agency_c",
"award__latest_transaction__assistance_data__awarding_sub_tier_agency_c",
)
derived_fields["awarding_subagency_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_sub_tier_agency_n",
"award__latest_transaction__assistance_data__awarding_sub_tier_agency_n",
)
derived_fields["awarding_office_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_office_code",
"award__latest_transaction__assistance_data__awarding_office_code",
)
derived_fields["awarding_office_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_office_name",
"award__latest_transaction__assistance_data__awarding_office_name",
)
derived_fields["funding_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_agency_code",
"award__latest_transaction__assistance_data__funding_agency_code",
)
derived_fields["funding_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_agency_name",
"award__latest_transaction__assistance_data__funding_agency_name",
)
derived_fields["funding_sub_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_sub_tier_agency_co",
"award__latest_transaction__assistance_data__funding_sub_tier_agency_co",
)
derived_fields["funding_sub_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_sub_tier_agency_na",
"award__latest_transaction__assistance_data__funding_sub_tier_agency_na",
)
derived_fields["funding_office_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_office_code",
"award__latest_transaction__assistance_data__funding_office_code",
)
derived_fields["funding_office_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_office_name",
"award__latest_transaction__assistance_data__funding_office_name",
)
derived_fields["recipient_duns"] = Coalesce(
"award__latest_transaction__contract_data__awardee_or_recipient_uniqu",
"award__latest_transaction__assistance_data__awardee_or_recipient_uniqu",
)
derived_fields["recipient_name"] = Coalesce(
"award__latest_transaction__contract_data__awardee_or_recipient_legal",
"award__latest_transaction__assistance_data__awardee_or_recipient_legal",
)
derived_fields["recipient_parent_duns"] = Coalesce(
"award__latest_transaction__contract_data__ultimate_parent_unique_ide",
"award__latest_transaction__assistance_data__ultimate_parent_unique_ide",
)
derived_fields["recipient_parent_name"] = Coalesce(
"award__latest_transaction__contract_data__ultimate_parent_legal_enti",
"award__latest_transaction__assistance_data__ultimate_parent_legal_enti",
)
derived_fields["recipient_country"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_country_code",
"award__latest_transaction__assistance_data__legal_entity_country_code",
)
derived_fields["recipient_state"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_state_code",
"award__latest_transaction__assistance_data__legal_entity_state_code",
)
derived_fields["recipient_county"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_county_name",
"award__latest_transaction__assistance_data__legal_entity_county_name",
)
derived_fields["recipient_city"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_city_name",
"award__latest_transaction__assistance_data__legal_entity_city_name",
)
derived_fields["recipient_congressional_district"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_congressional",
"award__latest_transaction__assistance_data__legal_entity_congressional",
)
derived_fields["recipient_zip_code"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_zip4",
Concat(
"award__latest_transaction__assistance_data__legal_entity_zip5",
"award__latest_transaction__assistance_data__legal_entity_zip_last4",
),
)
derived_fields["primary_place_of_performance_country"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perf_country_desc",
"award__latest_transaction__assistance_data__place_of_perform_country_n",
)
derived_fields["primary_place_of_performance_state"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perfor_state_desc",
"award__latest_transaction__assistance_data__place_of_perform_state_nam",
)
derived_fields["primary_place_of_performance_county"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perform_county_na",
"award__latest_transaction__assistance_data__place_of_perform_county_na",
)
derived_fields["primary_place_of_performance_congressional_district"] = Coalesce(
"award__latest_transaction__contract_data__place_of_performance_congr",
"award__latest_transaction__assistance_data__place_of_performance_congr",
)
derived_fields["primary_place_of_performance_zip_code"] = Coalesce(
"award__latest_transaction__contract_data__place_of_performance_zip4a",
"award__latest_transaction__assistance_data__place_of_performance_zip4a",
)
derived_fields["award_base_action_date_fiscal_year"] = FiscalYear(
"award__date_signed")
derived_fields["award_latest_action_date_fiscal_year"] = FiscalYear(
"award__certified_date")
derived_fields["usaspending_permalink"] = Case(
When(
**{
"award__generated_unique_award_id__isnull":
False,
"then":
ConcatAll(
Value(AWARD_URL),
Func(F("award__generated_unique_award_id"),
function="urlencode"), Value("/")),
}),
default=Value(""),
output_field=CharField(),
)
return derived_fields
def transaction_search_annotations():
annotation_fields = {
"action_date_fiscal_year":
FiscalYear("action_date"),
"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(),
),
"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":
ConcatAll(
Value(AWARD_URL),
Func(F("transaction__award__generated_unique_award_id"),
function="urlencode"), Value("/")),
"disaster_emergency_fund_codes_for_overall_award":
Case(
When(
transaction__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=ConcatAll(
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(),
),
)),
"outlayed_amount_funded_by_COVID-19_supplementals_for_overall_award":
Case(
When(
transaction__action_date__gte=datetime.date(2020, 4, 1),
then=_covid_outlay_subquery(),
), ),
"obligated_amount_funded_by_COVID-19_supplementals_for_overall_award":
Case(
When(
transaction__action_date__gte=datetime.date(2020, 4, 1),
then=_covid_obligation_subquery(),
), ),
"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(
ConcatAll(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(),
),
"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(
ConcatAll(
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(),
),
}
return annotation_fields
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":
ConcatAll(
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(
ConcatAll(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(
ConcatAll(
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=ConcatAll(
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