def database_data_layer(self) -> tuple:
if self.subawards:
queryset = subaward_filter(self.filters)
obligation_column = "amount"
else:
queryset = spending_over_time(self.filters)
obligation_column = "generated_pragmatic_obligation"
values = ["fy"]
if self.group == "month":
queryset = queryset.annotate(month=FiscalMonth("action_date"),
fy=FiscalYear("action_date"))
values.append("month")
elif self.group == "quarter":
queryset = queryset.annotate(quarter=FiscalQuarter("action_date"),
fy=FiscalYear("action_date"))
values.append("quarter")
elif self.group == "fiscal_year":
queryset = queryset.annotate(fy=FiscalYear("action_date"))
queryset = (queryset.values(*values).annotate(
aggregated_amount=Sum(obligation_column)).order_by(
*["{}".format(value) for value in values]))
return queryset, values
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"),
"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("/")),
}
return annotation_fields
def idv_order_annotations():
annotation_fields = {
"award_base_action_date_fiscal_year":
FiscalYear("date_signed"),
"treasury_accounts_funding_this_award":
Subquery(
Award.objects.filter(id=OuterRef("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("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("generated_unique_award_id"), function="urlencode"),
Value("/")),
}
return annotation_fields
def database_data_layer(self):
recipient_hash = self.filters['recipient_id'][:-2]
time_ranges = []
for t in self.filters['time_period']:
t['date_type'] = 'action_date'
time_ranges.append(t)
queryset = SummaryAwardRecipient.objects.filter()
queryset &= combine_date_range_queryset(time_ranges,
SummaryAwardRecipient,
API_SEARCH_MIN_DATE,
API_MAX_DATE)
if self.filters['recipient_id'][-1] == 'P':
# there *should* only one record with that hash and recipient_level = 'P'
parent_duns_rows = RecipientProfile.objects.filter(
recipient_hash=recipient_hash,
recipient_level='P').values('recipient_unique_id')
if len(parent_duns_rows) != 1:
raise InvalidParameterException(
'Provided recipient_id has no parent records')
parent_duns = parent_duns_rows[0]['recipient_unique_id']
queryset = queryset.filter(parent_recipient_unique_id=parent_duns)
elif self.filters['recipient_id'][-1] == 'R':
queryset = queryset.filter(recipient_hash=recipient_hash,
parent_recipient_unique_id__isnull=True)
else:
queryset = queryset.filter(
recipient_hash=recipient_hash,
parent_recipient_unique_id__isnull=False)
values = ['fy']
if self.groupings[self.json_request['group']] == 'month':
queryset = queryset.annotate(month=FiscalMonth('action_date'),
fy=FiscalYear('action_date'))
values.append('month')
elif self.groupings[self.json_request['group']] == 'quarter':
queryset = queryset.annotate(quarter=FiscalQuarter('action_date'),
fy=FiscalYear('action_date'))
values.append('quarter')
elif self.groupings[self.json_request['group']] == 'fiscal_year':
queryset = queryset.annotate(fy=FiscalYear('action_date'))
queryset = queryset.values(*values).annotate(count=Count('award_id'))
return queryset, values
def database_data_layer(self):
recipient_hash = self.filters["recipient_id"][:-2]
time_ranges = []
for t in self.filters["time_period"]:
t["date_type"] = "action_date"
time_ranges.append(t)
queryset = SummaryAwardRecipient.objects.filter()
queryset &= combine_date_range_queryset(time_ranges,
SummaryAwardRecipient,
API_SEARCH_MIN_DATE,
API_MAX_DATE)
if self.filters["recipient_id"][-1] == "P":
# there *should* only one record with that hash and recipient_level = 'P'
parent_duns_rows = RecipientProfile.objects.filter(
recipient_hash=recipient_hash,
recipient_level="P").values("recipient_unique_id")
if len(parent_duns_rows) != 1:
raise InvalidParameterException(
"Provided recipient_id has no parent records")
parent_duns = parent_duns_rows[0]["recipient_unique_id"]
queryset = queryset.filter(parent_recipient_unique_id=parent_duns)
elif self.filters["recipient_id"][-1] == "R":
queryset = queryset.filter(recipient_hash=recipient_hash,
parent_recipient_unique_id__isnull=True)
else:
queryset = queryset.filter(
recipient_hash=recipient_hash,
parent_recipient_unique_id__isnull=False)
values = ["fy"]
if self.groupings[self.json_request["group"]] == "month":
queryset = queryset.annotate(month=FiscalMonth("action_date"),
fy=FiscalYear("action_date"))
values.append("month")
elif self.groupings[self.json_request["group"]] == "quarter":
queryset = queryset.annotate(quarter=FiscalQuarter("action_date"),
fy=FiscalYear("action_date"))
values.append("quarter")
elif self.groupings[self.json_request["group"]] == "fiscal_year":
queryset = queryset.annotate(fy=FiscalYear("action_date"))
queryset = queryset.values(*values).annotate(count=Count("award_id"))
return queryset, values
def subaward_annotations():
annotation_fields = {
"subaward_action_date_fiscal_year":
FiscalYear("subaward__action_date"),
"prime_award_action_date_fiscal_year":
FiscalYear("award__date_signed"),
"prime_award_federal_accounts_funding_this_award":
StringAgg(
"award__financial_set__treasury_account__federal_account__federal_account_code",
";",
distinct=True),
"prime_award_treasury_accounts_funding_this_award":
StringAgg(
"award__financial_set__treasury_account__tas_rendering_label",
";",
distinct=True),
}
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_federal_accounts_funding_this_award":
StringAgg(
"award__financial_set__treasury_account__federal_account__federal_account_code",
";",
distinct=True),
"prime_award_treasury_accounts_funding_this_award":
StringAgg(
"award__financial_set__treasury_account__tas_rendering_label",
";",
distinct=True),
"usaspending_permalink":
Concat(
Value(AWARD_URL),
Func(F("award__generated_unique_award_id"), function="urlencode"),
Value("/")),
}
return annotation_fields
def idv_transaction_annotations():
annotation_fields = {
"action_date_fiscal_year":
FiscalYear("action_date"),
"treasury_accounts_funding_this_award":
StringAgg(
"award__financial_set__treasury_account__tas_rendering_label",
";",
distinct=True),
"federal_accounts_funding_this_award":
StringAgg(
"award__financial_set__treasury_account__federal_account__federal_account_code",
";",
distinct=True),
}
return annotation_fields
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["usaspending_permalink"] = Case(
When(
**{
"award__generated_unique_award_id__isnull": False,
"then": Concat(
Value(AWARD_URL), Func(F("award__generated_unique_award_id"), function="urlencode"), Value("/")
),
}
),
default=Value(""),
output_field=CharField(),
)
return derived_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": 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
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