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 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 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 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( list(contract_type_mapping) + list(idv_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 {}, IDVs {}, or grants {} are available""" msg = msg.format(list(contract_type_mapping.keys()), list(idv_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 } })
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"""
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"] }
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')" output_field = IntegerField()