def get_view_queryset(filters, view_name): try: view_model = MATVIEW_SELECTOR[view_name]["model"] except Exception: raise InvalidParameterException("Invalid view: " + view_name + " does not exist.") return matview_search_filter(filters, view_model)
def get_view_queryset(filters, view_name): try: view_model = MATVIEW_SELECTOR[view_name]['model'] except Exception: raise InvalidParameterException('Invalid view: ' + view_name + ' does not exist.') return matview_search_filter(filters, view_model)
def obtain_state_totals(fips, year=None, award_type_codes=None, subawards=False): filters = reshape_filters(state_code=VALID_FIPS[fips]["code"], year=year, award_type_codes=award_type_codes) if not subawards: queryset = (matview_search_filter( filters, SummaryStateView).values("pop_state_code").annotate( total=Sum("generated_pragmatic_obligation"), distinct_awards=StringAgg("distinct_awards", ",")).values("distinct_awards", "pop_state_code", "total")) try: row = list(queryset)[0] result = { "pop_state_code": row["pop_state_code"], "total": row["total"], "count": len(set(row["distinct_awards"].split(","))), } return result except IndexError: # would prefer to catch an index error gracefully if the SQL query produces 0 rows logger.warn("No results found for FIPS {} with filters: {}".format( fips, filters)) return {"count": 0, "pop_state_code": None, "total": 0}
def obtain_state_totals(fips, year=None, award_type_codes=None, subawards=False): filters = reshape_filters(state_code=VALID_FIPS[fips]['code'], year=year, award_type_codes=award_type_codes) if not subawards: queryset = matview_search_filter(filters, SummaryStateView) \ .values('pop_state_code') \ .annotate( total=Sum('generated_pragmatic_obligation'), distinct_awards=StringAgg('distinct_awards', ',')) \ .values('distinct_awards', 'pop_state_code', 'total') try: row = list(queryset)[0] result = { 'pop_state_code': row['pop_state_code'], 'total': row['total'], 'count': len(set(row['distinct_awards'].split(','))), } return result except IndexError: # would prefer to catch an index error gracefully if the SQL query produces 0 rows logger.warn('No results found for FIPS {} with filters: {}'.format( fips, filters)) return {'count': 0, 'pop_state_code': None, 'total': 0}
def fetch_all_category_counts(filters, category_to_model_dict): loop = asyncio.new_event_loop() results = {} for k, v in category_to_model_dict.items(): queryset = matview_search_filter( filters, v).annotate(count=Count("*")).values("count") sql = generate_raw_quoted_query(queryset) # Django refuses to provide a viable option to exclude "GROUP BY ..." so it is stripped before running the SQL remove_groupby_string_index = sql.find("GROUP BY") results[k] = asyncio.ensure_future(async_run_select( sql[:remove_groupby_string_index]), loop=loop) all_statements = asyncio.gather(*[value for value in results.values()]) loop.run_until_complete(all_statements) loop.close() return {k: v.result()[0]["count"] for k, v in results.items()}
def get_all_states(year=None, award_type_codes=None, subawards=False): filters = reshape_filters(year=year, award_type_codes=award_type_codes) if not subawards: # calculate award total filtered by state queryset = matview_search_filter(filters, SummaryStateView) \ .filter(pop_state_code__isnull=False, pop_country_code='USA') \ .values('pop_state_code') \ .annotate( total=Sum('generated_pragmatic_obligation'), distinct_awards=StringAgg('distinct_awards', ',')) \ .values('pop_state_code', 'total', 'distinct_awards') results = [{ 'pop_state_code': row['pop_state_code'], 'total': row['total'], 'count': len(set(row['distinct_awards'].split(','))), } for row in list(queryset)] return results
def get_all_states(year=None, award_type_codes=None, subawards=False): filters = reshape_filters(year=year, award_type_codes=award_type_codes) if not subawards: # calculate award total filtered by state queryset = (matview_search_filter(filters, SummaryStateView).filter( pop_state_code__isnull=False, pop_country_code="USA").values("pop_state_code").annotate( total=Sum("generated_pragmatic_obligation"), distinct_awards=StringAgg("distinct_awards", ",")).values("pop_state_code", "total", "distinct_awards")) results = [{ "pop_state_code": row["pop_state_code"], "total": row["total"], "count": len(set(row["distinct_awards"].split(","))), } for row in list(queryset)] return results
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""" json_request = request.data fields = json_request.get("fields", None) filters = json_request.get("filters", None) subawards = json_request.get("subawards", False) order = json_request.get("order", "asc") limit = json_request.get("limit", 10) page = json_request.get("page", 1) lower_limit = (page - 1) * limit upper_limit = page * limit # input validation if fields is None: raise InvalidParameterException("Missing one or more required request parameters: fields") elif len(fields) == 0: raise InvalidParameterException("Please provide a field in the fields request parameter.") if filters is None: raise InvalidParameterException("Missing one or more required request parameters: filters") if "award_type_codes" not in filters: raise InvalidParameterException( "Missing one or more required request parameters: filters['award_type_codes']") if order not in ["asc", "desc"]: raise InvalidParameterException("Invalid value for order: {}".format(order)) if type(subawards) is not bool: raise InvalidParameterException('subawards does not have a valid value') 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: 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]] if sort == "Award ID": sort_filters = ["award__piid", "award__fain"] if subawards else ["piid", "fain", "uri"] if order == "desc": sort_filters = ["-" + sort_filter for sort_filter in sort_filters] queryset = queryset.order_by(*sort_filters).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 post(self, request): """Return all budget function/subfunction titles matching the provided search text""" # TODO: check logic in name_dict[x]["aggregated_amount"] statements json_request = request.data category = json_request.get("category", None) scope = json_request.get("scope", None) filters = json_request.get("filters", None) limit = json_request.get("limit", 10) page = json_request.get("page", 1) lower_limit = (page - 1) * limit upper_limit = page * limit if category is None: raise InvalidParameterException("Missing one or more required request parameters: category") potential_categories = ["awarding_agency", "funding_agency", "recipient", "cfda_programs", "industry_codes"] if category not in potential_categories: raise InvalidParameterException("Category does not have a valid value") if (scope is None) and (category != "cfda_programs"): raise InvalidParameterException("Missing one or more required request parameters: scope") if filters is None: raise InvalidParameterException("Missing one or more required request parameters: filters") # filter queryset queryset = matview_search_filter(filters, UniversalTransactionView) filter_types = filters['award_type_codes'] if 'award_type_codes' in filters else award_type_mapping # filter the transactions by category if category == "awarding_agency": potential_scopes = ["agency", "subagency"] if scope not in potential_scopes: raise InvalidParameterException("scope does not have a valid value") if scope == "agency": queryset = queryset \ .filter(awarding_toptier_agency_name__isnull=False) \ .values( agency_name=F('awarding_toptier_agency_name'), agency_abbreviation=F('awarding_toptier_agency_abbreviation')) elif scope == "subagency": queryset = queryset \ .filter( awarding_subtier_agency_name__isnull=False) \ .values( agency_name=F('awarding_subtier_agency_name'), agency_abbreviation=F('awarding_subtier_agency_abbreviation')) elif scope == "office": # NOT IMPLEMENTED IN UI raise NotImplementedError queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types)\ .order_by('-aggregated_amount') results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] response = {"category": category, "scope": scope, "limit": limit, "results": results, "page_metadata": page_metadata} return Response(response) elif category == "funding_agency": potential_scopes = ["agency", "subagency"] if scope not in potential_scopes: raise InvalidParameterException("scope does not have a valid value") if scope == "agency": queryset = queryset \ .filter(funding_toptier_agency_name__isnull=False) \ .values( agency_name=F('funding_toptier_agency_name'), agency_abbreviation=F('funding_toptier_agency_abbreviation')) elif scope == "subagency": queryset = queryset \ .filter( funding_subtier_agency_name__isnull=False) \ .values( agency_name=F('funding_subtier_agency_name'), agency_abbreviation=F('funding_subtier_agency_abbreviation')) elif scope == "office": # NOT IMPLEMENTED IN UI raise NotImplementedError queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .order_by('-aggregated_amount') results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] response = {"category": category, "scope": scope, "limit": limit, "results": results, "page_metadata": page_metadata} return Response(response) elif category == "recipient": if scope == "duns": queryset = queryset \ .values(legal_entity_id=F("recipient_id")) queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .order_by('-aggregated_amount') \ .values("aggregated_amount", "legal_entity_id", "recipient_name") \ .order_by("-aggregated_amount") # Begin DB hits here results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] elif scope == "parent_duns": queryset = queryset \ .filter(parent_recipient_unique_id__isnull=False) queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types, calculate_totals=False) \ .values( 'aggregated_amount', 'recipient_name', 'parent_recipient_unique_id') \ .order_by('-aggregated_amount') # Begin DB hits here results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] else: # recipient_type raise InvalidParameterException("recipient type is not yet implemented") response = {"category": category, "scope": scope, "limit": limit, "results": results, "page_metadata": page_metadata} return Response(response) elif category == "cfda_programs": if can_use_view(filters, 'SummaryCfdaNumbersView'): queryset = get_view_queryset(filters, 'SummaryCfdaNumbersView') queryset = queryset \ .filter( federal_action_obligation__isnull=False, cfda_number__isnull=False) \ .values(cfda_program_number=F("cfda_number")) queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .values( "aggregated_amount", "cfda_program_number", program_title=F("cfda_title")) \ .order_by('-aggregated_amount') # Begin DB hits here results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] for trans in results: trans['popular_name'] = None # small DB hit every loop here cfda = Cfda.objects \ .filter( program_title=trans['program_title'], program_number=trans['cfda_program_number']) \ .values('popular_name').first() if cfda: trans['popular_name'] = cfda['popular_name'] else: queryset = queryset \ .filter( cfda_number__isnull=False) \ .values(cfda_program_number=F("cfda_number")) queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .values( "aggregated_amount", "cfda_program_number", popular_name=F("cfda_popular_name"), program_title=F("cfda_title")) \ .order_by('-aggregated_amount') # Begin DB hits here results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] response = {"category": category, "limit": limit, "results": results, "page_metadata": page_metadata} return Response(response) elif category == "industry_codes": # industry_codes if scope == "psc": if can_use_view(filters, 'SummaryPscCodesView'): queryset = get_view_queryset(filters, 'SummaryPscCodesView') queryset = queryset \ .filter(product_or_service_code__isnull=False) \ .values(psc_code=F("product_or_service_code")) else: queryset = queryset \ .filter(psc_code__isnull=False) \ .values("psc_code") queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .order_by('-aggregated_amount') # Begin DB hits here results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] response = {"category": category, "scope": scope, "limit": limit, "results": results, "page_metadata": page_metadata} return Response(response) elif scope == "naics": if can_use_view(filters, 'SummaryNaicsCodesView'): queryset = get_view_queryset(filters, 'SummaryNaicsCodesView') queryset = queryset \ .filter(naics_code__isnull=False) \ .values('naics_code') queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .order_by('-aggregated_amount') \ .values( 'naics_code', 'aggregated_amount', 'naics_description') else: queryset = queryset \ .filter(naics_code__isnull=False) \ .values("naics_code") queryset = sum_transaction_amount(queryset, 'aggregated_amount', filter_types=filter_types) \ .order_by('-aggregated_amount') \ .values( 'naics_code', 'aggregated_amount', 'naics_description') # Begin DB hits here results = list(queryset[lower_limit:upper_limit + 1]) page_metadata = get_simple_pagination_metadata(len(results), limit, page) results = results[:limit] response = {"category": category, "scope": scope, "limit": limit, "results": results, "page_metadata": page_metadata} return Response(response) else: # recipient_type raise InvalidParameterException("recipient type is not yet implemented")
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 get_view_queryset(filters, view_name): try: view_model = MATVIEW_SELECTOR[view_name]['model'] except Exception: raise InvalidParameterException('Invalid view: ' + view_name + ' does not exist.') return matview_search_filter(filters, view_model)