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