def get_csv_sources(json_request): csv_sources = [] for award_level in json_request['award_levels']: queryset = VALUE_MAPPINGS[award_level]['filter_function'](json_request['filters']) award_level_table = VALUE_MAPPINGS[award_level]['table'] award_type_codes = set(json_request['filters']['award_type_codes']) d1_award_type_codes = set(contract_type_mapping.keys()) d2_award_type_codes = set(assistance_type_mapping.keys()) if award_type_codes & d1_award_type_codes: # only generate d1 files if the user is asking for contract data d1_source = CsvSource(VALUE_MAPPINGS[award_level]['table_name'], 'd1', award_level) d1_filters = {'{}__isnull'.format(VALUE_MAPPINGS[award_level]['contract_data']): False} d1_source.queryset = queryset & award_level_table.objects.filter(**d1_filters) csv_sources.append(d1_source) if award_type_codes & d2_award_type_codes: # only generate d2 files if the user is asking for assistance data d2_source = CsvSource(VALUE_MAPPINGS[award_level]['table_name'], 'd2', award_level) d2_filters = {'{}__isnull'.format(VALUE_MAPPINGS[award_level]['assistance_data']): False} d2_source.queryset = queryset & award_level_table.objects.filter(**d2_filters) csv_sources.append(d2_source) verify_requested_columns_available(tuple(csv_sources), json_request.get('columns', [])) return csv_sources
def award_financial_recipient_parent_derivation(derived_fields): derived_fields['recipient_parent_name'] = Case( When( award__latest_transaction__type__in=list( contract_type_mapping.keys()), then= 'award__latest_transaction__contract_data__ultimate_parent_legal_enti' ), default= 'award__latest_transaction__assistance_data__ultimate_parent_legal_enti', output_field=CharField()) return derived_fields
def get_csv_sources(json_request): csv_sources = [] for download_type in json_request['download_types']: agency_id = json_request['filters'].get('agency', 'all') filter_function = VALUE_MAPPINGS[download_type]['filter_function'] download_type_table = VALUE_MAPPINGS[download_type]['table'] if VALUE_MAPPINGS[download_type]['source_type'] == 'award': # Award downloads queryset = filter_function(json_request['filters']) award_type_codes = set(json_request['filters']['award_type_codes']) if award_type_codes & (set(contract_type_mapping.keys()) | set(idv_type_mapping.keys())): # only generate d1 files if the user is asking for contract data d1_source = CsvSource( VALUE_MAPPINGS[download_type]['table_name'], 'd1', download_type, agency_id) d1_filters = { '{}__isnull'.format(VALUE_MAPPINGS[download_type]['contract_data']): False } d1_source.queryset = queryset & download_type_table.objects.filter( **d1_filters) csv_sources.append(d1_source) if award_type_codes & set(assistance_type_mapping.keys()): # only generate d2 files if the user is asking for assistance data d2_source = CsvSource( VALUE_MAPPINGS[download_type]['table_name'], 'd2', download_type, agency_id) d2_filters = { '{}__isnull'.format(VALUE_MAPPINGS[download_type]['assistance_data']): False } d2_source.queryset = queryset & download_type_table.objects.filter( **d2_filters) csv_sources.append(d2_source) verify_requested_columns_available(tuple(csv_sources), json_request.get('columns', [])) elif VALUE_MAPPINGS[download_type]['source_type'] == 'account': # Account downloads account_source = CsvSource( VALUE_MAPPINGS[download_type]['table_name'], json_request['account_level'], download_type, agency_id) account_source.queryset = filter_function( download_type, VALUE_MAPPINGS[download_type]['table'], json_request['filters'], json_request['account_level']) csv_sources.append(account_source) return csv_sources
def get_csv_sources(json_request): csv_sources = [] for download_type in json_request["download_types"]: agency_id = json_request["filters"].get("agency", "all") filter_function = VALUE_MAPPINGS[download_type]["filter_function"] download_type_table = VALUE_MAPPINGS[download_type]["table"] if VALUE_MAPPINGS[download_type]["source_type"] == "award": # Award downloads # Use correct date range columns for advanced search # (Will not change anything for keyword search since "time_period" is not provided)) filters = add_date_range_comparison_types( json_request["filters"], is_subaward=download_type != "awards", gte_date_type="action_date", lte_date_type="date_signed", ) queryset = filter_function(filters) award_type_codes = set(filters["award_type_codes"]) if award_type_codes & (set(contract_type_mapping.keys()) | set(idv_type_mapping.keys())): # only generate d1 files if the user is asking for contract data d1_source = CsvSource(VALUE_MAPPINGS[download_type]["table_name"], "d1", download_type, agency_id) d1_filters = {"{}__isnull".format(VALUE_MAPPINGS[download_type]["contract_data"]): False} d1_source.queryset = queryset & download_type_table.objects.filter(**d1_filters) csv_sources.append(d1_source) if award_type_codes & set(assistance_type_mapping.keys()): # only generate d2 files if the user is asking for assistance data d2_source = CsvSource(VALUE_MAPPINGS[download_type]["table_name"], "d2", download_type, agency_id) d2_filters = {"{}__isnull".format(VALUE_MAPPINGS[download_type]["assistance_data"]): False} d2_source.queryset = queryset & download_type_table.objects.filter(**d2_filters) csv_sources.append(d2_source) verify_requested_columns_available(tuple(csv_sources), json_request.get("columns", [])) elif VALUE_MAPPINGS[download_type]["source_type"] == "account": # Account downloads account_source = CsvSource( VALUE_MAPPINGS[download_type]["table_name"], json_request["account_level"], download_type, agency_id ) account_source.queryset = filter_function( download_type, VALUE_MAPPINGS[download_type]["table"], json_request["filters"], json_request["account_level"], ) csv_sources.append(account_source) return csv_sources
def _test_correct_response_of_contracts(client): resp = post( client, award_type_codes=list(contract_type_mapping.keys()), def_codes=["L", "M"], geo_layer="district", geo_layer_filters=["4510", "4550", "5350"], spending_type="obligation", ) expected_response = { "geo_layer": "district", "scope": "recipient_location", "spending_type": "obligation", "results": [ { "amount": 2000000.0, "display_name": "SC-10", "per_capita": None, "population": None, "shape_code": "4510", "award_count": 1, }, { "amount": 200000.0, "display_name": "SC-50", "per_capita": 2000.0, "population": 100, "shape_code": "4550", "award_count": 1, }, { "amount": 22000.0, "display_name": "WA-50", "per_capita": 22.0, "population": 1000, "shape_code": "5350", "award_count": 2, }, ], } assert resp.status_code == status.HTTP_200_OK, "Failed to return 200 Response" resp_json = resp.json() resp_json["results"].sort(key=_get_shape_code_for_sort) assert resp_json == expected_response
def award_financial_derivations(derived_fields): derived_fields['recipient_parent_name'] = Case( When(award__latest_transaction__type__in=list(contract_type_mapping.keys()), then='award__latest_transaction__contract_data__ultimate_parent_legal_enti'), default='award__latest_transaction__assistance_data__ultimate_parent_legal_enti', output_field=CharField() ) 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' ) return derived_fields
def award_financial_derivations(derived_fields): derived_fields["recipient_parent_name"] = Case( When( award__latest_transaction__type__in=list( contract_type_mapping.keys()), then= "award__latest_transaction__contract_data__ultimate_parent_legal_enti", ), default= "award__latest_transaction__assistance_data__ultimate_parent_legal_enti", output_field=CharField(), ) 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", ) return derived_fields
def post(self, request): """Return all awards matching the provided filters and limits""" models = [{ 'name': 'fields', 'key': 'fields', 'type': 'array', 'array_type': 'text', 'text_type': 'search', 'min': 1 }, { 'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False }] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) for m in models: if m['name'] in ('award_type_codes', 'fields'): m['optional'] = False json_request = TinyShield(models).block(request.data) fields = json_request["fields"] filters = json_request.get("filters", {}) subawards = json_request["subawards"] order = json_request["order"] limit = json_request["limit"] page = json_request["page"] if "no intersection" in filters["award_type_codes"]: # "Special case": there will never be results when the website provides this value return Response({ "limit": limit, "results": [], "page_metadata": { "page": page, "hasNext": False }, }) sort = json_request.get("sort", fields[0]) if sort not in fields: raise InvalidParameterException( "Sort value '{}' not found in requested fields: {}".format( sort, fields)) subawards_values = list(contract_subaward_mapping.keys()) + list( grant_subaward_mapping.keys()) awards_values = list(award_contracts_mapping.keys()) + list(loan_award_mapping.keys()) + \ list(non_loan_assistance_award_mapping.keys()) + list(award_idv_mapping.keys()) msg = "Sort value '{}' not found in {{}} mappings: {{}}".format(sort) if not subawards and sort not in awards_values: raise InvalidParameterException(msg.format("award", awards_values)) elif subawards and sort not in subawards_values: raise InvalidParameterException( msg.format("subaward", subawards_values)) # build sql query filters if subawards: queryset = subaward_filter(filters) values = {'subaward_number', 'piid', 'fain', 'award_type'} for field in fields: if contract_subaward_mapping.get(field): values.add(contract_subaward_mapping.get(field)) if grant_subaward_mapping.get(field): values.add(grant_subaward_mapping.get(field)) else: queryset = matview_search_filter(filters, UniversalAwardView).values() values = {'award_id', 'piid', 'fain', 'uri', 'type'} for field in fields: if award_contracts_mapping.get(field): values.add(award_contracts_mapping.get(field)) if loan_award_mapping.get(field): values.add(loan_award_mapping.get(field)) if non_loan_assistance_award_mapping.get(field): values.add(non_loan_assistance_award_mapping.get(field)) if award_idv_mapping.get(field): values.add(award_idv_mapping.get(field)) # Modify queryset to be ordered by requested "sort" in the request or default value(s) if sort: if subawards: if set(filters["award_type_codes"]) <= set( contract_type_mapping): # Subaward contracts sort_filters = [contract_subaward_mapping[sort]] elif set(filters["award_type_codes"]) <= set( grant_type_mapping): # Subaward grants sort_filters = [grant_subaward_mapping[sort]] else: msg = 'Award Type codes limited for Subawards. Only contracts {} or grants {} are available' msg = msg.format(list(contract_type_mapping.keys()), list(grant_type_mapping.keys())) raise UnprocessableEntityException(msg) else: if set(filters["award_type_codes"]) <= set( contract_type_mapping): # contracts sort_filters = [award_contracts_mapping[sort]] elif set(filters["award_type_codes"]) <= set( loan_type_mapping): # loans sort_filters = [loan_award_mapping[sort]] elif set(filters["award_type_codes"]) <= set( idv_type_mapping): # idvs sort_filters = [award_idv_mapping[sort]] else: # assistance data sort_filters = [non_loan_assistance_award_mapping[sort]] # Explictly set NULLS LAST in the ordering to encourage the usage of the indexes if sort == "Award ID" and subawards: if order == "desc": queryset = queryset.order_by( F('award__piid').desc(nulls_last=True), F('award__fain').desc(nulls_last=True)).values( *list(values)) else: queryset = queryset.order_by( F('award__piid').asc(nulls_last=True), F('award__fain').asc(nulls_last=True)).values( *list(values)) elif sort == "Award ID": if order == "desc": queryset = queryset.order_by( F('piid').desc(nulls_last=True), F('fain').desc(nulls_last=True), F('uri').desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by( F('piid').asc(nulls_last=True), F('fain').asc(nulls_last=True), F('uri').asc(nulls_last=True)).values(*list(values)) elif order == "desc": queryset = queryset.order_by( F(sort_filters[0]).desc(nulls_last=True)).values( *list(values)) else: queryset = queryset.order_by( F(sort_filters[0]).asc(nulls_last=True)).values( *list(values)) limited_queryset = queryset[(page - 1) * limit:page * limit + 1] # lower limit : upper limit has_next = len(limited_queryset) > limit results = [] for award in limited_queryset[:limit]: if subawards: row = {"internal_id": award["subaward_number"]} if award['award_type'] == 'procurement': for field in fields: row[field] = award.get( contract_subaward_mapping[field]) elif award['award_type'] == 'grant': for field in fields: row[field] = award.get(grant_subaward_mapping[field]) else: row = {"internal_id": award["award_id"]} if award['type'] in loan_type_mapping: # loans for field in fields: row[field] = award.get(loan_award_mapping.get(field)) elif award[ 'type'] in non_loan_assistance_type_mapping: # assistance data for field in fields: row[field] = award.get( non_loan_assistance_award_mapping.get(field)) elif award['type'] in idv_type_mapping: for field in fields: row[field] = award.get(award_idv_mapping.get(field)) elif (award['type'] is None and award['piid']) or award['type'] in contract_type_mapping: # IDV + contract for field in fields: row[field] = award.get( award_contracts_mapping.get(field)) if "Award ID" in fields: for id_type in ["piid", "fain", "uri"]: if award[id_type]: row["Award ID"] = award[id_type] break results.append(row) return Response({ "limit": limit, "results": results, "page_metadata": { "page": page, "hasNext": has_next } })
def subaward_filter(filters, for_downloads=False): queryset = SubawardView.objects.all() for key, value in filters.items(): if value is None: raise InvalidParameterException('Invalid filter: ' + key + ' has null as its value.') key_list = [ 'keywords', 'elasticsearch_keyword', 'time_period', 'award_type_codes', 'agencies', 'legal_entities', 'recipient_search_text', 'recipient_scope', 'recipient_locations', 'recipient_type_names', 'place_of_performance_scope', 'place_of_performance_locations', 'award_amounts', 'award_ids', 'program_numbers', 'naics_codes', 'psc_codes', 'contract_pricing_type_codes', 'set_aside_type_codes', 'extent_competed_type_codes' ] if key not in key_list: raise InvalidParameterException('Invalid filter: ' + key + ' does not exist.') if key == "keywords": def keyword_parse(keyword): # keyword_ts_vector & award_ts_vector are Postgres TS_vectors. # keyword_ts_vector = recipient_name + psc_description + subaward_description # award_ts_vector = piid + fain + uri + subaward_number filter_obj = Q(keyword_ts_vector=keyword) | \ Q(award_ts_vector=keyword) if keyword.isnumeric(): filter_obj |= Q(naics_code__contains=keyword) if len(keyword) == 4 and PSC.objects.all().filter( code__iexact=keyword).exists(): filter_obj |= Q(product_or_service_code__iexact=keyword) return filter_obj filter_obj = Q() for keyword in value: filter_obj |= keyword_parse(keyword) potential_duns = list( filter((lambda x: len(x) > 7 and len(x) < 10), value)) if len(potential_duns) > 0: filter_obj |= Q(recipient_unique_id__in=potential_duns) | \ Q(parent_recipient_unique_id__in=potential_duns) queryset = queryset.filter(filter_obj) elif key == "elasticsearch_keyword": keyword = value transaction_ids = elasticsearch_helper.get_download_ids( keyword=keyword, field='transaction_id') # flatten IDs transaction_ids = list( itertools.chain.from_iterable(transaction_ids)) logger.info('Found {} transactions based on keyword: {}'.format( len(transaction_ids), keyword)) transaction_ids = [ str(transaction_id) for transaction_id in transaction_ids ] queryset = queryset.filter(latest_transaction_id__isnull=False) queryset &= queryset.extra(where=[ '"latest_transaction_id" = ANY(\'{{{}}}\'::int[])'.format( ','.join(transaction_ids)) ]) elif key == "time_period": min_date = API_SEARCH_MIN_DATE if for_downloads: min_date = API_MIN_DATE queryset &= combine_date_range_queryset(value, SubawardView, min_date, API_MAX_DATE) elif key == "award_type_codes": idv_flag = all(i in value for i in contract_type_mapping.keys()) if len(value) != 0: filter_obj = Q(prime_award_type__in=value) if idv_flag: filter_obj |= Q(pulled_from='IDV') queryset &= SubawardView.objects.filter(filter_obj) elif key == "agencies": # TODO: Make function to match agencies in award filter throwing dupe error funding_toptier = Q() funding_subtier = Q() awarding_toptier = Q() awarding_subtier = Q() for v in value: type = v["type"] tier = v["tier"] name = v["name"] if type == "funding": if tier == "toptier": funding_toptier |= Q(funding_toptier_agency_name=name) elif tier == "subtier": if 'toptier_name' in v: funding_subtier |= ( Q(funding_subtier_agency_name=name) & Q(funding_toptier_agency_name=v['toptier_name'] )) else: funding_subtier |= Q( funding_subtier_agency_name=name) elif type == "awarding": if tier == "toptier": awarding_toptier |= Q( awarding_toptier_agency_name=name) elif tier == "subtier": if 'toptier_name' in v: awarding_subtier |= (Q( awarding_subtier_agency_name=name ) & Q( awarding_toptier_agency_name=v['toptier_name']) ) else: awarding_subtier |= Q( awarding_subtier_agency_name=name) awarding_queryfilter = Q() funding_queryfilter = Q() # Since these are Q filters, no DB hits for boolean checks if funding_toptier: funding_queryfilter |= funding_toptier if funding_subtier: funding_queryfilter |= funding_subtier if awarding_toptier: awarding_queryfilter |= awarding_toptier if awarding_subtier: awarding_queryfilter |= awarding_subtier queryset = queryset.filter(funding_queryfilter & awarding_queryfilter) elif key == "legal_entities": # This filter key has effectively become obsolete by recipient_search_text msg = 'API request included "{}" key. No filtering will occur with provided value "{}"' logger.info(msg.format(key, value)) elif key == "recipient_search_text": def recip_string_parse(recipient_string): upper_recipient_string = recipient_string.upper() # recipient_name_ts_vector is a postgres TS_Vector filter_obj = Q(recipient_name_ts_vector=upper_recipient_string) if len(upper_recipient_string ) == 9 and upper_recipient_string[:5].isnumeric(): filter_obj |= Q(recipient_unique_id=upper_recipient_string) return filter_obj filter_obj = Q() for recipient in value: filter_obj |= recip_string_parse(recipient) queryset &= SubawardView.objects.filter(filter_obj) elif key == "recipient_scope": if value == "domestic": queryset = queryset.filter( recipient_location_country_name="UNITED STATES") elif value == "foreign": queryset = queryset.exclude( recipient_location_country_name="UNITED STATES") else: raise InvalidParameterException( 'Invalid filter: recipient_scope type is invalid.') elif key == "recipient_locations": or_queryset = geocode_filter_locations('recipient_location', value, SubawardView, True) queryset &= or_queryset elif key == "recipient_type_names": if len(value) != 0: queryset &= SubawardView.objects.filter( business_categories__overlap=value) elif key == "place_of_performance_scope": if value == "domestic": queryset = queryset.filter( Q(pop_country_name="UNITED STATES") | Q(pop_country_code="USA")) elif value == "foreign": queryset = queryset.exclude( Q(pop_country_name="UNITED STATES") | Q(pop_country_code="USA")) else: raise InvalidParameterException( 'Invalid filter: place_of_performance_scope is invalid.') elif key == "place_of_performance_locations": queryset &= geocode_filter_locations('pop', value, SubawardView, True) elif key == "award_amounts": queryset &= total_obligation_queryset(value, SubawardView, filters) elif key == "award_ids": filter_obj = Q() for val in value: # award_id_string is a Postgres TS_vector # award_id_string = piid + fain + uri + subaward_number filter_obj |= Q(award_ts_vector=val) queryset &= SubawardView.objects.filter(filter_obj) # add "naics_codes" (column naics) after NAICS are mapped to subawards elif key in ("program_numbers", "psc_codes", "contract_pricing_type_codes"): filter_to_col = { "program_numbers": "cfda_number", "psc_codes": "product_or_service_code", "contract_pricing_type_codes": "type_of_contract_pricing", } in_query = [v for v in value] if len(in_query) != 0: queryset &= SubawardView.objects.filter( **{'{}__in'.format(filter_to_col[key]): in_query}) elif key in ("set_aside_type_codes", "extent_competed_type_codes"): or_queryset = Q() filter_to_col = { "set_aside_type_codes": "type_set_aside", "extent_competed_type_codes": "extent_competed", } in_query = [v for v in value] for v in value: or_queryset |= Q( **{'{}__exact'.format(filter_to_col[key]): in_query}) queryset = queryset.filter(or_queryset) return queryset
def post(self, request): """Return all awards matching the provided filters and limits""" models = [ {'name': 'fields', 'key': 'fields', 'type': 'array', 'array_type': 'text', 'text_type': 'search', 'min': 1}, {'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False} ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) for m in models: if m['name'] in ('award_type_codes', 'fields'): m['optional'] = False json_request = TinyShield(models).block(request.data) fields = json_request["fields"] filters = json_request.get("filters", {}) subawards = json_request["subawards"] order = json_request["order"] limit = json_request["limit"] page = json_request["page"] if "no intersection" in filters["award_type_codes"]: # "Special case": there will never be results when the website provides this value return Response({ "limit": limit, "results": [], "page_metadata": {"page": page, "hasNext": False}, }) sort = json_request.get("sort", fields[0]) if sort not in fields: raise InvalidParameterException("Sort value '{}' not found in requested fields: {}".format(sort, fields)) subawards_values = list(contract_subaward_mapping.keys()) + list(grant_subaward_mapping.keys()) awards_values = list(award_contracts_mapping.keys()) + list(loan_award_mapping.keys()) + \ list(non_loan_assistance_award_mapping.keys()) + list(award_idv_mapping.keys()) msg = "Sort value '{}' not found in {{}} mappings: {{}}".format(sort) if not subawards and sort not in awards_values: raise InvalidParameterException(msg.format("award", awards_values)) elif subawards and sort not in subawards_values: raise InvalidParameterException(msg.format("subaward", subawards_values)) # build sql query filters if subawards: queryset = subaward_filter(filters) values = {'subaward_number', 'piid', 'fain', 'award_type'} for field in fields: if contract_subaward_mapping.get(field): values.add(contract_subaward_mapping.get(field)) if grant_subaward_mapping.get(field): values.add(grant_subaward_mapping.get(field)) else: queryset = matview_search_filter(filters, UniversalAwardView).values() values = {'award_id', 'piid', 'fain', 'uri', 'type'} for field in fields: if award_contracts_mapping.get(field): values.add(award_contracts_mapping.get(field)) if loan_award_mapping.get(field): values.add(loan_award_mapping.get(field)) if non_loan_assistance_award_mapping.get(field): values.add(non_loan_assistance_award_mapping.get(field)) if award_idv_mapping.get(field): values.add(award_idv_mapping.get(field)) # Modify queryset to be ordered by requested "sort" in the request or default value(s) if sort: if subawards: if set(filters["award_type_codes"]) <= set(contract_type_mapping): # Subaward contracts sort_filters = [contract_subaward_mapping[sort]] elif set(filters["award_type_codes"]) <= set(grant_type_mapping): # Subaward grants sort_filters = [grant_subaward_mapping[sort]] else: msg = 'Award Type codes limited for Subawards. Only contracts {} or grants {} are available' msg = msg.format(list(contract_type_mapping.keys()), list(grant_type_mapping.keys())) raise UnprocessableEntityException(msg) else: if set(filters["award_type_codes"]) <= set(contract_type_mapping): # contracts sort_filters = [award_contracts_mapping[sort]] elif set(filters["award_type_codes"]) <= set(loan_type_mapping): # loans sort_filters = [loan_award_mapping[sort]] elif set(filters["award_type_codes"]) <= set(idv_type_mapping): # idvs sort_filters = [award_idv_mapping[sort]] else: # assistance data sort_filters = [non_loan_assistance_award_mapping[sort]] # Explictly set NULLS LAST in the ordering to encourage the usage of the indexes if sort == "Award ID" and subawards: if order == "desc": queryset = queryset.order_by( F('award__piid').desc(nulls_last=True), F('award__fain').desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by( F('award__piid').asc(nulls_last=True), F('award__fain').asc(nulls_last=True)).values(*list(values)) elif sort == "Award ID": if order == "desc": queryset = queryset.order_by( F('piid').desc(nulls_last=True), F('fain').desc(nulls_last=True), F('uri').desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by( F('piid').asc(nulls_last=True), F('fain').asc(nulls_last=True), F('uri').asc(nulls_last=True)).values(*list(values)) elif order == "desc": queryset = queryset.order_by(F(sort_filters[0]).desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by(F(sort_filters[0]).asc(nulls_last=True)).values(*list(values)) limited_queryset = queryset[(page - 1) * limit:page * limit + 1] # lower limit : upper limit has_next = len(limited_queryset) > limit results = [] for award in limited_queryset[:limit]: if subawards: row = {"internal_id": award["subaward_number"]} if award['award_type'] == 'procurement': for field in fields: row[field] = award.get(contract_subaward_mapping[field]) elif award['award_type'] == 'grant': for field in fields: row[field] = award.get(grant_subaward_mapping[field]) else: row = {"internal_id": award["award_id"]} if award['type'] in loan_type_mapping: # loans for field in fields: row[field] = award.get(loan_award_mapping.get(field)) elif award['type'] in non_loan_assistance_type_mapping: # assistance data for field in fields: row[field] = award.get(non_loan_assistance_award_mapping.get(field)) elif award['type'] in idv_type_mapping: for field in fields: row[field] = award.get(award_idv_mapping.get(field)) elif (award['type'] is None and award['piid']) or award['type'] in contract_type_mapping: # IDV + contract for field in fields: row[field] = award.get(award_contracts_mapping.get(field)) if "Award ID" in fields: for id_type in ["piid", "fain", "uri"]: if award[id_type]: row["Award ID"] = award[id_type] break results.append(row) return Response({"limit": limit, "results": results, "page_metadata": {"page": page, "hasNext": has_next}})
def post(self, request): """Return all budget function/subfunction titles matching the provided search text""" models = [ {'name': 'fields', 'key': 'fields', 'type': 'array', 'array_type': 'text', 'text_type': 'search'}, {'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False} ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) for m in models: if m['name'] in ('award_type_codes', 'fields'): m['optional'] = False json_request = TinyShield(models).block(request.data) fields = json_request.get("fields", None) filters = json_request.get("filters", None) subawards = json_request["subawards"] order = json_request["order"] limit = json_request["limit"] page = json_request["page"] lower_limit = (page - 1) * limit upper_limit = page * limit sort = json_request.get("sort", fields[0]) if sort not in fields: raise InvalidParameterException("Sort value not found in fields: {}".format(sort)) subawards_values = list(contract_subaward_mapping.keys()) + list(grant_subaward_mapping.keys()) awards_values = list(award_contracts_mapping.keys()) + list(loan_award_mapping) + \ list(non_loan_assistance_award_mapping.keys()) if (subawards and sort not in subawards_values) or (not subawards and sort not in awards_values): raise InvalidParameterException("Sort value not found in award mappings: {}".format(sort)) # build sql query filters if subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters queryset = subaward_filter(filters) values = {'subaward_number', 'award__piid', 'award__fain', 'award_type'} for field in fields: if contract_subaward_mapping.get(field): values.add(contract_subaward_mapping.get(field)) if grant_subaward_mapping.get(field): values.add(grant_subaward_mapping.get(field)) else: queryset = matview_search_filter(filters, UniversalAwardView).values() values = {'award_id', 'piid', 'fain', 'uri', 'type'} for field in fields: if award_contracts_mapping.get(field): values.add(award_contracts_mapping.get(field)) if loan_award_mapping.get(field): values.add(loan_award_mapping.get(field)) if non_loan_assistance_award_mapping.get(field): values.add(non_loan_assistance_award_mapping.get(field)) # Modify queryset to be ordered if we specify "sort" in the request if sort and "no intersection" not in filters["award_type_codes"]: if subawards: if set(filters["award_type_codes"]) <= set(contract_type_mapping): # Subaward contracts sort_filters = [contract_subaward_mapping[sort]] elif set(filters["award_type_codes"]) <= set(grant_type_mapping): # Subaward grants sort_filters = [grant_subaward_mapping[sort]] else: msg = 'Award Type codes limited for Subawards. Only contracts {} or grants {} are available' msg = msg.format(list(contract_type_mapping.keys()), list(grant_type_mapping.keys())) raise UnprocessableEntityException(msg) else: if set(filters["award_type_codes"]) <= set(contract_type_mapping): # contracts sort_filters = [award_contracts_mapping[sort]] elif set(filters["award_type_codes"]) <= set(loan_type_mapping): # loans sort_filters = [loan_award_mapping[sort]] else: # assistance data sort_filters = [non_loan_assistance_award_mapping[sort]] # Explictly set NULLS LAST in the ordering to encourage the usage of the indexes if sort == "Award ID" and subawards: if order == "desc": queryset = queryset.order_by( F('award__piid').desc(nulls_last=True), F('award__fain').desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by( F('award__piid').asc(nulls_last=True), F('award__fain').asc(nulls_last=True)).values(*list(values)) elif sort == "Award ID": if order == "desc": queryset = queryset.order_by( F('piid').desc(nulls_last=True), F('fain').desc(nulls_last=True), F('uri').desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by( F('piid').asc(nulls_last=True), F('fain').asc(nulls_last=True), F('uri').asc(nulls_last=True)).values(*list(values)) elif order == "desc": queryset = queryset.order_by(F(sort_filters[0]).desc(nulls_last=True)).values(*list(values)) else: queryset = queryset.order_by(F(sort_filters[0]).asc(nulls_last=True)).values(*list(values)) limited_queryset = queryset[lower_limit:upper_limit + 1] has_next = len(limited_queryset) > limit results = [] for award in limited_queryset[:limit]: if subawards: row = {"internal_id": award["subaward_number"]} if award['award_type'] == 'procurement': for field in fields: row[field] = award.get(contract_subaward_mapping[field]) elif award['award_type'] == 'grant': for field in fields: row[field] = award.get(grant_subaward_mapping[field]) else: row = {"internal_id": award["award_id"]} if award['type'] in loan_type_mapping: # loans for field in fields: row[field] = award.get(loan_award_mapping.get(field)) elif award['type'] in non_loan_assistance_type_mapping: # assistance data for field in fields: row[field] = award.get(non_loan_assistance_award_mapping.get(field)) elif (award['type'] is None and award['piid']) or award['type'] in contract_type_mapping: # IDV + contract for field in fields: row[field] = award.get(award_contracts_mapping.get(field)) if "Award ID" in fields: for id_type in ["piid", "fain", "uri"]: if award[id_type]: row["Award ID"] = award[id_type] break results.append(row) # build response response = { 'limit': limit, 'results': results, 'page_metadata': { 'page': page, 'hasNext': has_next } } return Response(response)
def validate_disaster_recipient_request(request_data): _validate_required_parameters(request_data, ["filters"]) model = [ { "key": "filters|def_codes", "name": "def_codes", "type": "array", "array_type": "enum", "enum_values": sorted( DisasterEmergencyFundCode.objects.values_list("code", flat=True)), "allow_nulls": False, "optional": False, }, { "key": "filters|query", "name": "query", "type": "text", "text_type": "search", "allow_nulls": False, "optional": True, }, { "key": "filters|award_type_codes", "name": "award_type_codes", "type": "array", "array_type": "enum", "enum_values": sorted(award_type_mapping.keys()), "allow_nulls": False, "optional": True, }, ] filters = TinyShield(model).block(request_data)["filters"] # Determine what to use in the filename based on "award_type_codes" filter; # Also add "face_value_of_loans" column if only loan types award_category = "All-Awards" award_type_codes = set( filters.get("award_type_codes", award_type_mapping.keys())) columns = [ "recipient", "award_obligations", "award_outlays", "number_of_awards" ] if award_type_codes <= set(contract_type_mapping.keys()): award_category = "Contracts" elif award_type_codes <= set(idv_type_mapping.keys()): award_category = "Contract-IDVs" elif award_type_codes <= set(grant_type_mapping.keys()): award_category = "Grants" elif award_type_codes <= set(loan_type_mapping.keys()): award_category = "Loans" columns.insert(3, "face_value_of_loans") elif award_type_codes <= set(direct_payment_type_mapping.keys()): award_category = "Direct-Payments" elif award_type_codes <= set(other_type_mapping.keys()): award_category = "Other-Financial-Assistance" # Need to specify the field to use "query" filter on if present query_text = filters.pop("query", None) if query_text: filters["query"] = {"text": query_text, "fields": ["recipient_name"]} json_request = { "award_category": award_category, "columns": tuple(columns), "download_types": ["disaster_recipient"], "file_format": str(request_data.get("file_format", "csv")).lower(), "filters": filters, } _validate_file_format(json_request) return json_request
def award_financial_derivations(derived_fields): derived_fields["recipient_parent_name"] = Case( When( award__latest_transaction__type__in=list( contract_type_mapping.keys()), then= "award__latest_transaction__contract_data__ultimate_parent_legal_enti", ), default= "award__latest_transaction__assistance_data__ultimate_parent_legal_enti", output_field=CharField(), ) 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["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 __init__(self, request_data: dict): super().__init__(request_data) self.tinyshield_models.extend([ { "key": "filters|def_codes", "name": "def_codes", "type": "array", "array_type": "enum", "enum_values": sorted( DisasterEmergencyFundCode.objects.values_list("code", flat=True)), "allow_nulls": False, "optional": False, }, { "key": "filters|query", "name": "query", "type": "text", "text_type": "search", "allow_nulls": False, "optional": True, }, { "key": "filters|award_type_codes", "name": "award_type_codes", "type": "array", "array_type": "enum", "enum_values": sorted(award_type_mapping.keys()), "allow_nulls": False, "optional": True, }, ]) self._json_request["filters"] = request_data.get("filters") self._json_request = self.get_validated_request() self._json_request["download_types"] = [self.name] # Determine what to use in the filename based on "award_type_codes" filter; # Also add "face_value_of_loans" column if only loan types award_category = "All-Awards" award_type_codes = set(self._json_request["filters"].get( "award_type_codes", award_type_mapping.keys())) columns = [ "recipient", "award_obligations", "award_outlays", "number_of_awards" ] if award_type_codes <= set(contract_type_mapping.keys()): award_category = "Contracts" elif award_type_codes <= set(idv_type_mapping.keys()): award_category = "Contract-IDVs" elif award_type_codes <= set(grant_type_mapping.keys()): award_category = "Grants" elif award_type_codes <= set(loan_type_mapping.keys()): award_category = "Loans" columns.insert(3, "face_value_of_loans") elif award_type_codes <= set(direct_payment_type_mapping.keys()): award_category = "Direct-Payments" elif award_type_codes <= set(other_type_mapping.keys()): award_category = "Other-Financial-Assistance" self._json_request["award_category"] = award_category self._json_request["columns"] = self._json_request.get( "columns") or tuple(columns) # Need to specify the field to use "query" filter on if present query_text = self._json_request["filters"].pop("query", None) if query_text: self._json_request["filters"]["query"] = { "text": query_text, "fields": ["recipient_name"] }
assistance_type_mapping, contract_type_mapping, direct_payment_type_mapping, grant_type_mapping, idv_type_mapping, loan_type_mapping, other_type_mapping, procurement_type_mapping, ) TYPES_TO_QUOTE_IN_SQL = (str, date) CATEGORY_TO_MODEL = { "contracts": {"model": ContractAwardSearchMatview, "types": set(contract_type_mapping.keys())}, "direct_payments": {"model": DirectPaymentAwardSearchMatview, "types": set(direct_payment_type_mapping.keys())}, "grants": {"model": GrantAwardSearchMatview, "types": set(grant_type_mapping.keys())}, "idvs": {"model": IDVAwardSearchMatview, "types": set(idv_type_mapping.keys())}, "loans": {"model": LoanAwardSearchMatview, "types": set(loan_type_mapping.keys())}, "other": {"model": OtherAwardSearchMatview, "types": set(other_type_mapping.keys())}, } class AwardGroupsException(Exception): """Custom Exception for a specific event""" class FiscalMonth(Func): function = "EXTRACT" template = "%(function)s(MONTH from (%(expressions)s) + INTERVAL '3 months')"
def get_download_sources(json_request: dict, origination: Optional[str] = None): download_sources = [] for download_type in json_request["download_types"]: agency_id = json_request.get("agency", "all") filter_function = VALUE_MAPPINGS[download_type]["filter_function"] download_type_table = VALUE_MAPPINGS[download_type]["table"] if VALUE_MAPPINGS[download_type]["source_type"] == "award": # Award downloads # Use correct date range columns for advanced search # (Will not change anything for keyword search since "time_period" is not provided)) filters = add_date_range_comparison_types( json_request["filters"], is_subaward=download_type != "awards", gte_date_type="action_date", lte_date_type="date_signed", ) queryset = filter_function(filters) if filters.get("prime_and_sub_award_types") is not None: award_type_codes = set( filters["prime_and_sub_award_types"][download_type]) else: award_type_codes = set(filters["award_type_codes"]) if (award_type_codes & (set(contract_type_mapping.keys()) | set(idv_type_mapping.keys())) or "procurement" in award_type_codes): # only generate d1 files if the user is asking for contract data d1_source = DownloadSource( VALUE_MAPPINGS[download_type]["table_name"], "d1", download_type, agency_id) d1_filters = { f"{VALUE_MAPPINGS[download_type]['contract_data']}__isnull": False } d1_source.queryset = queryset & download_type_table.objects.filter( **d1_filters) download_sources.append(d1_source) if award_type_codes & set(assistance_type_mapping.keys()) or ( "grant" in award_type_codes): # only generate d2 files if the user is asking for assistance data d2_source = DownloadSource( VALUE_MAPPINGS[download_type]["table_name"], "d2", download_type, agency_id) d2_filters = { f"{VALUE_MAPPINGS[download_type]['assistance_data']}__isnull": False } d2_source.queryset = queryset & download_type_table.objects.filter( **d2_filters) download_sources.append(d2_source) elif VALUE_MAPPINGS[download_type]["source_type"] == "account": # Account downloads account_source = DownloadSource( VALUE_MAPPINGS[download_type]["table_name"], json_request["account_level"], download_type, agency_id) account_source.queryset = filter_function( download_type, VALUE_MAPPINGS[download_type]["table"], json_request["filters"], json_request["account_level"], ) download_sources.append(account_source) verify_requested_columns_available(tuple(download_sources), json_request.get("columns", [])) return download_sources
def matview_search_filter(filters, model): queryset = model.objects.all() faba_flag = False faba_queryset = FinancialAccountsByAwards.objects.filter( award__isnull=False) for key, value in filters.items(): if value is None: raise InvalidParameterException('Invalid filter: ' + key + ' has null as its value.') key_list = [ 'keyword', 'time_period', 'award_type_codes', 'agencies', 'legal_entities', 'recipient_search_text', 'recipient_scope', 'recipient_locations', 'recipient_type_names', 'place_of_performance_scope', 'place_of_performance_locations', 'award_amounts', 'award_ids', 'program_numbers', 'naics_codes', 'psc_codes', 'contract_pricing_type_codes', 'set_aside_type_codes', 'extent_competed_type_codes', # next 3 keys used by federal account page 'federal_account_ids', 'object_class', 'program_activity' ] if key not in key_list: raise InvalidParameterException('Invalid filter: ' + key + ' does not exist.') if key == "keyword": keyword = value upper_kw = keyword.upper() # keyword_string & award_id_string are Postgres TS_vectors. # keyword_string = recipient_name + naics_code + naics_description + psc_description + awards_description # award_id_string = piid + fain + uri compound_or = Q(keyword_ts_vector=keyword) | \ Q(award_ts_vector=keyword) | \ Q(recipient_unique_id=upper_kw) | \ Q(parent_recipient_unique_id=keyword) if keyword.isnumeric(): compound_or |= Q(naics_code__contains=keyword) if len(keyword) == 4 and PSC.objects.all().filter( code__iexact=keyword).exists(): compound_or |= Q(product_or_service_code__iexact=keyword) queryset = queryset.filter(compound_or) elif key == "time_period": success, or_queryset = date_or_fy_queryset(value, model, "fiscal_year", "action_date") if success: queryset &= or_queryset elif key == "award_type_codes": idv_flag = all(i in value for i in contract_type_mapping.keys()) if len(value) != 0: filter_obj = Q(type__in=value) if idv_flag: filter_obj |= Q(pulled_from='IDV') queryset &= model.objects.filter(filter_obj) elif key == "agencies": # TODO: Make function to match agencies in award filter throwing dupe error funding_toptier = Q() funding_subtier = Q() awarding_toptier = Q() awarding_subtier = Q() for v in value: type = v["type"] tier = v["tier"] name = v["name"] if type == "funding": if tier == "toptier": funding_toptier |= Q(funding_toptier_agency_name=name) elif tier == "subtier": if 'toptier_name' in v: funding_subtier |= ( Q(funding_subtier_agency_name=name) & Q(funding_toptier_agency_name=v['toptier_name'] )) else: funding_subtier |= Q( funding_subtier_agency_name=name) else: raise InvalidParameterException( 'Invalid filter: agencies ' + tier + ' tier is invalid.') elif type == "awarding": if tier == "toptier": awarding_toptier |= Q( awarding_toptier_agency_name=name) elif tier == "subtier": if 'toptier_name' in v: awarding_subtier |= (Q( awarding_subtier_agency_name=name ) & Q( awarding_toptier_agency_name=v['toptier_name']) ) else: awarding_subtier |= Q( awarding_subtier_agency_name=name) else: raise InvalidParameterException( 'Invalid filter: agencies ' + tier + ' tier is invalid.') else: raise InvalidParameterException( 'Invalid filter: agencies ' + type + ' type is invalid.') awarding_queryfilter = Q() funding_queryfilter = Q() # Since these are Q filters, no DB hits for boolean checks if funding_toptier: funding_queryfilter |= funding_toptier if funding_subtier: funding_queryfilter |= funding_subtier if awarding_toptier: awarding_queryfilter |= awarding_toptier if awarding_subtier: awarding_queryfilter |= awarding_subtier queryset = queryset.filter(funding_queryfilter & awarding_queryfilter) elif key == "legal_entities": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter(recipient_id__in=in_query) elif key == "recipient_search_text": if len(value) != 1: raise InvalidParameterException( 'Invalid filter: recipient_search_text must have exactly one value.' ) upper_recipient_string = str(value[0]).upper() # recipient_name_ts_vector is a postgres TS_Vector filter_obj = Q(recipient_name_ts_vector=upper_recipient_string) if len(upper_recipient_string ) == 9 and upper_recipient_string[:5].isnumeric(): filter_obj |= Q(recipient_unique_id=upper_recipient_string) queryset &= model.objects.filter(filter_obj) elif key == "recipient_scope": if value == "domestic": queryset = queryset.filter( recipient_location_country_name="UNITED STATES") elif value == "foreign": queryset = queryset.exclude( recipient_location_country_name="UNITED STATES") else: raise InvalidParameterException( 'Invalid filter: recipient_scope type is invalid.') elif key == "recipient_locations": or_queryset = geocode_filter_locations('recipient_location', value, model, True) queryset &= or_queryset elif key == "recipient_type_names": if len(value) != 0: queryset &= model.objects.filter( business_categories__overlap=value) elif key == "place_of_performance_scope": if value == "domestic": queryset = queryset.filter(pop_country_name="UNITED STATES") elif value == "foreign": queryset = queryset.exclude(pop_country_name="UNITED STATES") else: raise InvalidParameterException( 'Invalid filter: place_of_performance_scope is invalid.') elif key == "place_of_performance_locations": queryset &= geocode_filter_locations('pop', value, model, True) elif key == "award_amounts": success, and_queryset = total_obligation_queryset( value, model, filters) if success: queryset &= and_queryset elif key == "award_ids": if len(value) != 0: filter_obj = Q() for val in value: # award_id_string is a Postgres TS_vector # award_id_string = piid + fain + uri filter_obj |= Q(award_ts_vector=val) queryset &= model.objects.filter(filter_obj) elif key == "program_numbers": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter(cfda_number__in=in_query) elif key == "naics_codes": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter(naics_code__in=in_query) elif key == "psc_codes": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter( product_or_service_code__in=in_query) elif key == "contract_pricing_type_codes": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter( type_of_contract_pricing__in=in_query) elif key == "set_aside_type_codes": or_queryset = Q() for v in value: or_queryset |= Q(type_set_aside__exact=v) queryset = queryset.filter(or_queryset) elif key == "extent_competed_type_codes": or_queryset = Q() for v in value: or_queryset |= Q(extent_competed__exact=v) queryset = queryset.filter(or_queryset) # Federal Account Filter elif key == "federal_account_ids": faba_flag = True or_queryset = Q() for v in value: or_queryset |= Q(treasury_account__federal_account_id=v) faba_queryset = faba_queryset.filter(or_queryset) # Federal Account Filter elif key == "object_class": faba_flag = True result = Q() for oc in value: subresult = Q() for (key, values) in oc.items(): subresult &= filter_on( "treasury_account__program_balances__object_class", key, values) result |= subresult faba_queryset = faba_queryset.filter(result) # Federal Account Filter elif key == "program_activity": faba_flag = True or_queryset = Q() for v in value: or_queryset |= Q( treasury_account__program_balances__program_activity__program_activity_code =v) faba_queryset = faba_queryset.filter(or_queryset) if faba_flag: award_ids = faba_queryset.values('award_id') queryset = queryset.filter(award_id__in=award_ids) return queryset
def matview_search_filter(filters, model, for_downloads=False): queryset = model.objects.all() recipient_scope_q = Q(recipient_location_country_code="USA") | Q( recipient_location_country_name="UNITED STATES") pop_scope_q = Q(pop_country_code="USA") | Q( pop_country_name="UNITED STATES") faba_flag = False faba_queryset = FinancialAccountsByAwards.objects.filter( award__isnull=False) for key, value in filters.items(): if value is None: raise InvalidParameterException('Invalid filter: ' + key + ' has null as its value.') key_list = [ 'keywords', 'elasticsearch_keyword', 'time_period', 'award_type_codes', 'agencies', 'legal_entities', 'recipient_id', 'recipient_search_text', 'recipient_scope', 'recipient_locations', 'recipient_type_names', 'place_of_performance_scope', 'place_of_performance_locations', 'award_amounts', 'award_ids', 'program_numbers', 'naics_codes', 'psc_codes', 'contract_pricing_type_codes', 'set_aside_type_codes', 'extent_competed_type_codes', # next 3 keys used by federal account page 'federal_account_ids', 'object_class', 'program_activity' ] if key not in key_list: raise InvalidParameterException('Invalid filter: ' + key + ' does not exist.') if key == "keywords": def keyword_parse(keyword): # keyword_ts_vector & award_ts_vector are Postgres TS_vectors. # keyword_ts_vector = recipient_name + naics_code + naics_description # + psc_description + awards_description # award_ts_vector = piid + fain + uri filter_obj = Q(keyword_ts_vector=keyword) | \ Q(award_ts_vector=keyword) if keyword.isnumeric(): filter_obj |= Q(naics_code__contains=keyword) if len(keyword) == 4 and PSC.objects.all().filter( code__iexact=keyword).exists(): filter_obj |= Q(product_or_service_code__iexact=keyword) return filter_obj filter_obj = Q() for keyword in value: filter_obj |= keyword_parse(keyword) potential_duns = list( filter((lambda x: len(x) > 7 and len(x) < 10), value)) if len(potential_duns) > 0: filter_obj |= Q(recipient_unique_id__in=potential_duns) | \ Q(parent_recipient_unique_id__in=potential_duns) queryset = queryset.filter(filter_obj) elif key == "elasticsearch_keyword": keyword = " ".join(value) if isinstance(value, list) else value transaction_ids = elasticsearch_helper.get_download_ids( keyword=keyword, field='transaction_id') # flatten IDs transaction_ids = list( itertools.chain.from_iterable(transaction_ids)) logger.info('Found {} transactions based on keyword: {}'.format( len(transaction_ids), keyword)) transaction_ids = [ str(transaction_id) for transaction_id in transaction_ids ] if model is UniversalAwardView: queryset = queryset.filter( latest_transaction__id__isnull=False) queryset &= queryset.extra(where=[ '"transaction_normalized"."id" = ANY(\'{{{}}}\'::int[])'. format(','.join(transaction_ids)) ]) elif key == "time_period": min_date = API_SEARCH_MIN_DATE if for_downloads: min_date = API_MIN_DATE queryset &= combine_date_range_queryset(value, model, min_date, API_MAX_DATE) elif key == "award_type_codes": idv_flag = all(i in value for i in contract_type_mapping.keys()) filter_obj = Q(type__in=value) if idv_flag: filter_obj |= Q(pulled_from='IDV') & Q(type__isnull=True) queryset &= model.objects.filter(filter_obj) elif key == "agencies": # TODO: Make function to match agencies in award filter throwing dupe error funding_toptier = Q() funding_subtier = Q() awarding_toptier = Q() awarding_subtier = Q() for v in value: type = v["type"] tier = v["tier"] name = v["name"] if type == "funding": if tier == "toptier": funding_toptier |= Q(funding_toptier_agency_name=name) elif tier == "subtier": if 'toptier_name' in v: funding_subtier |= ( Q(funding_subtier_agency_name=name) & Q(funding_toptier_agency_name=v['toptier_name'] )) else: funding_subtier |= Q( funding_subtier_agency_name=name) elif type == "awarding": if tier == "toptier": awarding_toptier |= Q( awarding_toptier_agency_name=name) elif tier == "subtier": if 'toptier_name' in v: awarding_subtier |= (Q( awarding_subtier_agency_name=name ) & Q( awarding_toptier_agency_name=v['toptier_name']) ) else: awarding_subtier |= Q( awarding_subtier_agency_name=name) awarding_queryfilter = Q() funding_queryfilter = Q() # Since these are Q filters, no DB hits for boolean checks if funding_toptier: funding_queryfilter |= funding_toptier if funding_subtier: funding_queryfilter |= funding_subtier if awarding_toptier: awarding_queryfilter |= awarding_toptier if awarding_subtier: awarding_queryfilter |= awarding_subtier queryset = queryset.filter(funding_queryfilter & awarding_queryfilter) elif key == "legal_entities": # This filter key has effectively become obsolete by recipient_search_text msg = 'API request included "{}" key. No filtering will occur with provided value "{}"' logger.info(msg.format(key, value)) # in_query = [v for v in value] # if len(in_query) != 0: # queryset &= model.objects.filter(recipient_id__in=in_query) elif key == "recipient_search_text": all_filters_obj = Q() for recip in value: upper_recipient_string = str(recip).upper() # recipient_name_ts_vector is a postgres TS_Vector filter_obj = Q(recipient_name_ts_vector=upper_recipient_string) if len(upper_recipient_string ) == 9 and upper_recipient_string[:5].isnumeric(): filter_obj |= Q(recipient_unique_id=upper_recipient_string) all_filters_obj |= filter_obj queryset &= model.objects.filter(all_filters_obj) elif key == "recipient_id": filter_obj = Q() recipient_hash = value[:-2] if value.endswith( 'P' ): # For parent types, gather all of the children's transactions parent_duns_rows = (RecipientProfile.objects.filter( recipient_hash=recipient_hash, recipient_level='P').values('recipient_unique_id')) if len(parent_duns_rows) == 1: parent_duns = parent_duns_rows[0]['recipient_unique_id'] filter_obj = Q(parent_recipient_unique_id=parent_duns) elif len(parent_duns_rows) > 2: # shouldn't occur raise InvalidParameterException( 'Non-unique parent record found in RecipientProfile') elif value.endswith('C'): filter_obj = Q(recipient_hash=recipient_hash, parent_recipient_unique_id__isnull=False) else: # "R" recipient level filter_obj = Q(recipient_hash=recipient_hash, parent_recipient_unique_id__isnull=True) queryset &= model.objects.filter(filter_obj) elif key == "recipient_scope": if value == "domestic": queryset = queryset.filter(recipient_scope_q) elif value == "foreign": queryset = queryset.exclude(recipient_scope_q) else: raise InvalidParameterException( 'Invalid filter: recipient_scope type is invalid.') elif key == "recipient_locations": queryset = queryset.filter( geocode_filter_locations('recipient_location', value, True)) elif key == "recipient_type_names": if len(value) != 0: queryset &= model.objects.filter( business_categories__overlap=value) elif key == "place_of_performance_scope": if value == "domestic": queryset = queryset.filter(pop_scope_q) elif value == "foreign": queryset = queryset.exclude(pop_scope_q) else: raise InvalidParameterException( 'Invalid filter: place_of_performance_scope is invalid.') elif key == "place_of_performance_locations": queryset = queryset.filter( geocode_filter_locations('pop', value, True)) elif key == "award_amounts": queryset &= total_obligation_queryset(value, model, filters) elif key == "award_ids": filter_obj = Q() for val in value: # award_id_string is a Postgres TS_vector # award_id_string = piid + fain + uri filter_obj |= Q(award_ts_vector=val) queryset &= model.objects.filter(filter_obj) elif key == "program_numbers": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter(cfda_number__in=in_query) elif key == "naics_codes": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter(naics_code__in=in_query) elif key == "psc_codes": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter( product_or_service_code__in=in_query) elif key == "contract_pricing_type_codes": in_query = [v for v in value] if len(in_query) != 0: queryset &= model.objects.filter( type_of_contract_pricing__in=in_query) elif key == "set_aside_type_codes": or_queryset = Q() for v in value: or_queryset |= Q(type_set_aside__exact=v) queryset = queryset.filter(or_queryset) elif key == "extent_competed_type_codes": or_queryset = Q() for v in value: or_queryset |= Q(extent_competed__exact=v) queryset = queryset.filter(or_queryset) # Federal Account Filter elif key == "federal_account_ids": faba_flag = True or_queryset = Q() for v in value: or_queryset |= Q(treasury_account__federal_account_id=v) faba_queryset = faba_queryset.filter(or_queryset) # Federal Account Filter elif key == "object_class": faba_flag = True result = Q() for oc in value: subresult = Q() for (key, values) in oc.items(): subresult &= filter_on( "treasury_account__program_balances__object_class", key, values) result |= subresult faba_queryset = faba_queryset.filter(result) # Federal Account Filter elif key == "program_activity": faba_flag = True or_queryset = Q() for v in value: or_queryset |= Q( treasury_account__program_balances__program_activity__program_activity_code =v) faba_queryset = faba_queryset.filter(or_queryset) if faba_flag: award_ids = faba_queryset.values('award_id') queryset = queryset.filter(award_id__in=award_ids) return queryset
assistance_type_mapping, contract_type_mapping, direct_payment_type_mapping, grant_type_mapping, idv_type_mapping, loan_type_mapping, other_type_mapping, procurement_type_mapping, ) TYPES_TO_QUOTE_IN_SQL = (str, date) CATEGORY_TO_MODEL = { "contracts": { "model": ContractAwardSearchMatview, "types": set(contract_type_mapping.keys()) }, "direct_payments": { "model": DirectPaymentAwardSearchMatview, "types": set(direct_payment_type_mapping.keys()) }, "grants": { "model": GrantAwardSearchMatview, "types": set(grant_type_mapping.keys()) }, "idvs": { "model": IDVAwardSearchMatview, "types": set(idv_type_mapping.keys()) }, "loans": { "model": LoanAwardSearchMatview,