def industry_and_other_codes(self) -> list:
if self.category == "cfda":
filters = {"{}__isnull".format(self.obligation_column): False, "cfda_number__isnull": False}
values = ["cfda_number"]
elif self.category == "psc":
if self.subawards:
# N/A for subawards
self.raise_not_implemented()
filters = {"product_or_service_code__isnull": False}
values = ["product_or_service_code"]
elif self.category == "naics":
if self.subawards:
# TODO: get subaward NAICS from Broker
self.raise_not_implemented()
filters = {"naics_code__isnull": False}
values = ["naics_code", "naics_description"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit : self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
if self.category == "cfda":
row["id"], row["name"] = fetch_cfda_id_title_by_number(row["code"])
elif self.category == "psc":
row["id"] = None
row["name"] = fetch_psc_description_by_code(row["code"])
elif self.category == "naics":
row["id"] = None
row["name"] = fetch_naics_description_from_code(row["code"], row["name"])
return results
def funding_agency(self) -> list:
"""
NOTICE: These categories were originally included for both Prime and Sub awards.
However, there are concerns with the data sparsity so it is unlikely to be used immediately.
So this category will be "dark" for now (removed from API doc). If undesired long-term they should be
removed from code and API contract.
"""
if self.category == 'funding_agency':
filters = {'funding_toptier_agency_name__isnull': False}
values = [
'funding_toptier_agency_name',
'funding_toptier_agency_abbreviation'
]
elif self.category == 'funding_subagency':
filters = {'funding_subtier_agency_name__isnull': False}
values = [
'funding_subtier_agency_name',
'funding_subtier_agency_abbreviation'
]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row['id'] = fetch_agency_tier_id_by_agency(
row['name'], self.category == 'funding_subagency')
return results
def industry_and_other_codes(self) -> list:
if self.category == 'cfda':
filters = {
'{}__isnull'.format(self.obligation_column): False,
'cfda_number__isnull': False
}
values = ['cfda_number']
elif self.category == 'psc':
if self.subawards:
# N/A for subawards
self.raise_not_implemented()
filters = {'product_or_service_code__isnull': False}
values = ['product_or_service_code']
elif self.category == 'naics':
if self.subawards:
# TODO: get subaward NAICS from Broker
self.raise_not_implemented()
filters = {'naics_code__isnull': False}
values = ['naics_code', 'naics_description']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
if self.category == 'cfda':
row['id'], row['name'] = fetch_cfda_id_title_by_number(
row['code'])
elif self.category == 'psc':
row['id'] = None
row['name'] = fetch_psc_description_by_code(row['code'])
elif self.category == 'naics':
row['id'] = None
return results
def industry_and_other_codes(self) -> list:
if self.category == "cfda":
filters = {"{}__isnull".format(self.obligation_column): False, "cfda_number__isnull": False}
values = ["cfda_number"]
elif self.category == "psc":
if self.subawards:
# N/A for subawards
self.raise_not_implemented()
filters = {"product_or_service_code__isnull": False}
values = ["product_or_service_code"]
elif self.category == "naics":
if self.subawards:
# TODO: get subaward NAICS from Broker
self.raise_not_implemented()
filters = {"naics_code__isnull": False}
values = ["naics_code", "naics_description"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
if self.category == "cfda":
row["id"], row["name"] = fetch_cfda_id_title_by_number(row["code"])
elif self.category == "psc":
row["id"] = None
row["name"] = fetch_psc_description_by_code(row["code"])
elif self.category == "naics":
row["id"] = None
row["name"] = fetch_naics_description_from_code(row["code"], row["name"])
return results
def awarding_agency(self) -> list:
if self.category == "awarding_agency":
filters = {"awarding_toptier_agency_name__isnull": False}
values = ["awarding_toptier_agency_name", "awarding_toptier_agency_abbreviation"]
elif self.category == "awarding_subagency":
filters = {"awarding_subtier_agency_name__isnull": False}
values = ["awarding_subtier_agency_name", "awarding_subtier_agency_abbreviation"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row["id"] = fetch_agency_tier_id_by_agency(row["name"], self.category == "awarding_subagency")
return results
def awarding_agency(self) -> list:
if self.category == "awarding_agency":
filters = {"awarding_toptier_agency_name__isnull": False}
values = ["awarding_toptier_agency_name", "awarding_toptier_agency_abbreviation"]
elif self.category == "awarding_subagency":
filters = {"awarding_subtier_agency_name__isnull": False}
values = ["awarding_subtier_agency_name", "awarding_subtier_agency_abbreviation"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit : self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row["id"] = fetch_agency_tier_id_by_agency(row["name"], self.category == "awarding_subagency")
return results
def awarding_agency(self) -> list:
if self.category == 'awarding_agency':
filters = {'awarding_toptier_agency_name__isnull': False}
values = ['awarding_toptier_agency_name', 'awarding_toptier_agency_abbreviation']
elif self.category == 'awarding_subagency':
filters = {'awarding_subtier_agency_name__isnull': False}
values = ['awarding_subtier_agency_name', 'awarding_subtier_agency_abbreviation']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row['id'] = fetch_agency_tier_id_by_agency(row['name'], self.category == 'awarding_subagency')
return results
def federal_account(self) -> list:
# Awards -> FinancialAccountsByAwards -> TreasuryAppropriationAccount -> FederalAccount
filters = {"federal_account_id__isnull": False}
values = ["federal_account_id", "federal_account_display", "account_title"]
if self.subawards:
# N/A for subawards
self.raise_not_implemented()
# Note: For performance reasons, limiting to only recipient profile requests
if "recipient_id" not in self.filters:
raise InvalidParameterException("Federal Account category requires recipient_id in search filter")
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
return alias_response(ALIAS_DICT[self.category], query_results)
def federal_account(self) -> list:
# Awards -> FinancialAccountsByAwards -> TreasuryAppropriationAccount -> FederalAccount
filters = {"federal_account_id__isnull": False}
values = ["federal_account_id", "federal_account_display", "account_title"]
if self.subawards:
# N/A for subawards
self.raise_not_implemented()
# Note: For performance reasons, limiting to only recipient profile requests
if "recipient_id" not in self.filters:
raise InvalidParameterException("Federal Account category requires recipient_id in search filter")
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit : self.upper_limit])
return alias_response(ALIAS_DICT[self.category], query_results)
def federal_account(self) -> list:
filters = {'transaction__award__financial_set__treasury_account__federal_account_id__isnull': False}
values = {'transaction__award__financial_set__treasury_account__federal_account_id'}
# Note: Purely for performance reasons, can be removed if still performant
if 'recipient_id' not in self.filters:
raise InvalidParameterException('Federal Account Category requires recipient ID filter')
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
agency_identifier, main_account_code, federal_account_name = fetch_federal_account_from_id(row['id'])
row['code'] = '-'.join([agency_identifier, main_account_code])
row['name'] = federal_account_name
return results
def recipient(self) -> list:
if self.category == "recipient_duns":
filters = {}
if self.subawards:
values = ["recipient_name", "recipient_unique_id"]
else:
values = ["recipient_hash"]
elif self.category == "recipient_parent_duns":
# TODO: check if we can aggregate on recipient name and parent duns,
# since parent recipient name isn't available
# Not implemented until Parent Recipient Name's in LegalEntity and matviews
self.raise_not_implemented()
# filters = {'parent_recipient_unique_id__isnull': False}
# values = ['recipient_name', 'parent_recipient_unique_id']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit : self.upper_limit])
for row in query_results:
row["recipient_id"] = self._get_recipient_id(row)
if not self.subawards:
lookup = (
RecipientLookup.objects.filter(recipient_hash=row["recipient_hash"])
.values("legal_business_name", "duns")
.first()
)
# The Recipient Name + DUNS should always be retrievable in RecipientLookup
# For odd edge cases or data sync issues, handle gracefully:
if lookup is None:
lookup = {}
row["recipient_name"] = lookup.get("legal_business_name", None)
row["recipient_unique_id"] = lookup.get("duns", "DUNS Number not provided")
del row["recipient_hash"]
results = alias_response(ALIAS_DICT[self.category], query_results)
return results
def location(self) -> list:
if self.category == 'county':
filters = {'pop_county_code__isnull': False}
values = ['pop_county_code', 'pop_county_name']
elif self.category == 'district':
filters = {'pop_congressional_code__isnull': False}
values = ['pop_congressional_code', 'pop_state_code']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row['id'] = None
if self.category == 'district':
row['name'] = '{}-{}'.format(row['pop_state_code'],
row['code'])
del row['pop_state_code']
return results
def recipient(self) -> list:
if self.category == 'recipient_duns':
filters = {}
values = ['recipient_name', 'recipient_unique_id']
elif self.category == 'recipient_parent_duns':
# TODO: check if we can aggregate on recipient name and parent duns,
# since parent recipient name isn't available
# Not implemented until Parent Recipient Name's in LegalEntity and matviews
self.raise_not_implemented()
# filters = {'parent_recipient_unique_id__isnull': False}
# values = ['recipient_name', 'parent_recipient_unique_id']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row['id'] = fetch_recipient_id_by_duns(row['code'])
return results
def funding_agency(self) -> list:
"""
NOTICE: These categories were originally included for both Prime and Sub awards.
However, there are concerns with the data sparsity so it is unlikely to be used immediately.
So this category will be "dark" for now (removed from API doc). If undesired long-term they should be
removed from code and API contract.
"""
if self.category == "funding_agency":
filters = {"funding_toptier_agency_name__isnull": False}
values = ["funding_toptier_agency_name", "funding_toptier_agency_abbreviation"]
elif self.category == "funding_subagency":
filters = {"funding_subtier_agency_name__isnull": False}
values = ["funding_subtier_agency_name", "funding_subtier_agency_abbreviation"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row["id"] = fetch_agency_tier_id_by_agency(row["name"], self.category == "funding_subagency")
return results
def recipient(self) -> list:
if self.category == "recipient_duns":
filters = {}
if self.subawards:
values = ["recipient_name", "recipient_unique_id"]
else:
values = ["recipient_hash"]
elif self.category == "recipient_parent_duns":
# TODO: check if we can aggregate on recipient name and parent duns,
# since parent recipient name isn't available
# Not implemented until Parent Recipient Name's in LegalEntity and matviews
self.raise_not_implemented()
# filters = {'parent_recipient_unique_id__isnull': False}
# values = ['recipient_name', 'parent_recipient_unique_id']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
for row in query_results:
row["recipient_id"] = None
if not self.subawards:
lookup = (
RecipientLookup.objects.filter(recipient_hash=row["recipient_hash"])
.values("legal_business_name", "duns")
.first()
)
# The Recipient Name + DUNS should always be retrievable in RecipientLookup
# For odd edge cases or data sync issues, handle gracefully:
if lookup is None:
lookup = {}
row["recipient_name"] = lookup.get("legal_business_name", None)
row["recipient_unique_id"] = lookup.get("duns", "DUNS Number not provided")
del row["recipient_hash"]
results = alias_response(ALIAS_DICT[self.category], query_results)
return results
def location(self) -> list:
filters = {}
values = {}
if self.category == "county":
filters = {"pop_county_code__isnull": False}
values = ["pop_county_code", "pop_county_name"]
elif self.category == "district":
filters = {"pop_congressional_code__isnull": False}
values = ["pop_congressional_code", "pop_state_code"]
elif self.category == "country":
filters = {"pop_country_code__isnull": False}
values = ["pop_country_code"]
elif self.category == "state_territory":
filters = {"pop_state_code__isnull": False}
values = ["pop_state_code"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit : self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row["id"] = None
if self.category == "district":
cd_code = row["code"]
if cd_code == "90": # 90 = multiple districts
cd_code = "MULTIPLE DISTRICTS"
row["name"] = "{}-{}".format(row["pop_state_code"], cd_code)
del row["pop_state_code"]
if self.category == "country":
row["name"] = fetch_country_name_from_code(row["code"])
if self.category == "state_territory":
row["name"] = fetch_state_name_from_code(row["code"])
return results
def location(self) -> list:
filters = {}
values = {}
if self.category == 'county':
filters = {'pop_county_code__isnull': False}
values = ['pop_county_code', 'pop_county_name']
elif self.category == 'district':
filters = {'pop_congressional_code__isnull': False}
values = ['pop_congressional_code', 'pop_state_code']
elif self.category == 'country':
filters = {'pop_country_code__isnull': False}
values = ['pop_country_code']
elif self.category == 'state_territory':
filters = {'pop_state_code__isnull': False}
values = ['pop_state_code']
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row['id'] = None
if self.category == 'district':
cd_code = row['code']
if cd_code == '90': # 90 = multiple districts
cd_code = 'MULTIPLE DISTRICTS'
row['name'] = '{}-{}'.format(row['pop_state_code'], cd_code)
del row['pop_state_code']
if self.category == 'country':
row['name'] = fetch_country_name_from_code(row['code'])
if self.category == 'state_territory':
row['name'] = fetch_state_name_from_code(row['code'])
return results
def location(self) -> list:
filters = {}
values = {}
if self.category == "county":
filters = {"pop_county_code__isnull": False}
values = ["pop_county_code", "pop_county_name"]
elif self.category == "district":
filters = {"pop_congressional_code__isnull": False}
values = ["pop_congressional_code", "pop_state_code"]
elif self.category == "country":
filters = {"pop_country_code__isnull": False}
values = ["pop_country_code"]
elif self.category == "state_territory":
filters = {"pop_state_code__isnull": False}
values = ["pop_state_code"]
self.queryset = self.common_db_query(filters, values)
# DB hit here
query_results = list(self.queryset[self.lower_limit:self.upper_limit])
results = alias_response(ALIAS_DICT[self.category], query_results)
for row in results:
row["id"] = None
if self.category == "district":
cd_code = row["code"]
if cd_code == "90": # 90 = multiple districts
cd_code = "MULTIPLE DISTRICTS"
row["name"] = "{}-{}".format(row["pop_state_code"], cd_code)
del row["pop_state_code"]
if self.category == "country":
row["name"] = fetch_country_name_from_code(row["code"])
if self.category == "state_territory":
row["name"] = fetch_state_name_from_code(row["code"])
return results
