def database_data_layer(self) -> tuple: if self.subawards: queryset = subaward_filter(self.filters) obligation_column = "amount" else: queryset = spending_over_time(self.filters) obligation_column = "generated_pragmatic_obligation" values = ["fy"] if self.group == "month": queryset = queryset.annotate(month=FiscalMonth("action_date"), fy=FiscalYear("action_date")) values.append("month") elif self.group == "quarter": queryset = queryset.annotate(quarter=FiscalQuarter("action_date"), fy=FiscalYear("action_date")) values.append("quarter") elif self.group == "fiscal_year": queryset = queryset.annotate(fy=FiscalYear("action_date")) queryset = (queryset.values(*values).annotate( aggregated_amount=Sum(obligation_column)).order_by( *["{}".format(value) for value in values])) return queryset, values
def perform_search(self, validated_payload: dict, original_filters: dict) -> dict: self.filters = validated_payload.get("filters", {}) self.subawards = validated_payload["subawards"] self.pagination = self._get_pagination(validated_payload) if self.subawards: if self.category.name in self.sub_awards_not_implemented: self._raise_not_implemented() base_queryset = subaward_filter(self.filters) self.obligation_column = "amount" results = self.query_django(base_queryset) else: filter_query = QueryWithFilters.generate_transactions_elasticsearch_query( self.filters) results = self.query_elasticsearch(filter_query) # else: # base_queryset = spending_by_category_view_queryset(self.category.name, self.filters) # self.obligation_column = "generated_pragmatic_obligation" # results = self.query_django(base_queryset) page_metadata = get_simple_pagination_metadata(len(results), self.pagination.limit, self.pagination.page) response = { "category": self.category.name, "limit": self.pagination.limit, "page_metadata": page_metadata, "results": results[:self.pagination.limit], "messages": self._get_messages(original_filters), } return response
def post(self, request): """Returns boolean of whether a download request is greater than the max limit. """ models = [ { 'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False }, ] models.extend(copy.deepcopy(AWARD_FILTER)) json_request = TinyShield(models).block(request.data) # If no filters in request return empty object to return all transactions filters = json_request.get('filters', {}) is_over_limit = False if json_request['subawards']: total_count = subaward_filter(filters).count() else: queryset, model = download_transaction_count(filters) if model in ['UniversalTransactionView']: total_count = queryset.count() else: # "summary" materialized views are pre-aggregated and contain a counts col total_count = queryset.aggregate( total_count=Sum('counts'))['total_count'] if total_count and total_count > settings.MAX_DOWNLOAD_LIMIT: is_over_limit = True result = {"transaction_rows_gt_limit": is_over_limit} return Response(result)
def perform_search(self, validated_payload: dict, original_filters: dict) -> dict: self.filters = validated_payload.get("filters", {}) self.elasticsearch = validated_payload.get("elasticsearch") self.subawards = validated_payload["subawards"] self.pagination = self._get_pagination(validated_payload) if self.subawards: base_queryset = subaward_filter(self.filters) self.obligation_column = "amount" results = self.query_django(base_queryset) elif self.elasticsearch: logger.info( f"Using experimental Elasticsearch functionality for 'spending_by_category/{self.category.name}'" ) filter_query = QueryWithFilters.generate_transactions_elasticsearch_query(self.filters) results = self.query_elasticsearch(filter_query) else: base_queryset = spending_by_category_view_queryset(self.category.name, self.filters) self.obligation_column = "generated_pragmatic_obligation" results = self.query_django(base_queryset) page_metadata = get_simple_pagination_metadata(len(results), self.pagination.limit, self.pagination.page) response = { "category": self.category.name, "limit": self.pagination.limit, "page_metadata": page_metadata, "results": results[: self.pagination.limit], "messages": self._get_messages(original_filters), } return response
def perform_search(self, validated_payload: dict, original_filters: dict) -> dict: self.filters = validated_payload.get("filters", {}) self.subawards = validated_payload["subawards"] self.pagination = self._get_pagination(validated_payload) if self.subawards: base_queryset = subaward_filter(self.filters) self.obligation_column = "amount" results = self.query_django_for_subawards(base_queryset) else: filter_query = QueryWithFilters.generate_transactions_elasticsearch_query(self.filters) results = self.query_elasticsearch_for_prime_awards(filter_query) page_metadata = get_simple_pagination_metadata(len(results), self.pagination.limit, self.pagination.page) response = { "category": self.category.name, "limit": self.pagination.limit, "page_metadata": page_metadata, "results": results[: self.pagination.limit], "messages": self._get_messages(original_filters), } return response
def post(self, request): """Returns boolean of whether a download request is greater than the max limit. """ models = [ {'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}, ] models.extend(copy.deepcopy(AWARD_FILTER)) json_request = TinyShield(models).block(request.data) # If no filters in request return empty object to return all transactions filters = json_request.get('filters', {}) is_over_limit = False if json_request['subawards']: total_count = subaward_filter(filters).count() else: queryset, model = download_transaction_count(filters) if model in ['UniversalTransactionView']: total_count = queryset.count() else: # "summary" materialized views are pre-aggregated and contain a counts col total_count = queryset.aggregate(total_count=Sum('counts'))['total_count'] if total_count and total_count > settings.MAX_DOWNLOAD_LIMIT: is_over_limit = True result = { "transaction_rows_gt_limit": is_over_limit } return Response(result)
def database_data_layer(self): if self.subawards: queryset = subaward_filter(self.filters) obligation_column = "amount" else: queryset = spending_over_time(self.filters) obligation_column = "generated_pragmatic_obligation" values = ["fy"] if self.groupings[self.group] == "month": queryset = queryset.annotate(month=FiscalMonth("action_date"), fy=FiscalYear("action_date")) values.append("month") elif self.groupings[self.group] == "quarter": queryset = queryset.annotate(quarter=FiscalQuarter("action_date"), fy=FiscalYear("action_date")) values.append("quarter") elif self.groupings[self.group] == "fiscal_year": queryset = queryset.annotate(fy=FiscalYear("action_date")) queryset = ( queryset.values(*values) .annotate(aggregated_amount=Sum(obligation_column)) .order_by(*["{}".format(value) for value in values]) ) return queryset, values
def post(self, request): models = [{'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) filters = json_request.get("filters", None) subawards = json_request["subawards"] if filters is None: raise InvalidParameterException("Missing required request parameters: 'filters'") results = { "contracts": 0, "idvs": 0, "grants": 0, "direct_payments": 0, "loans": 0, "other": 0 } if not subawards else { "subcontracts": 0, "subgrants": 0 } if "award_type_codes" in filters and "no intersection" in filters["award_type_codes"]: # "Special case": there will never be results when the website provides this value return Response({"results": results}) if subawards: queryset = subaward_filter(filters) else: queryset, model = spending_by_award_count(filters) if subawards: queryset = queryset.values('award_type').annotate(category_count=Count('subaward_id')) elif model == 'SummaryAwardView': queryset = queryset.values('category').annotate(category_count=Sum('counts')) else: queryset = queryset.values('category').annotate(category_count=Count('category')) categories = { 'contract': 'contracts', 'idv': 'idvs', 'grant': 'grants', 'direct payment': 'direct_payments', 'loans': 'loans', 'other': 'other' } if not subawards else {'procurement': 'subcontracts', 'grant': 'subgrants'} category_name = 'category' if not subawards else 'award_type' # DB hit here for award in queryset: if award[category_name] is None: result_key = 'other' if not subawards else 'subcontracts' elif award[category_name] not in categories.keys(): result_key = 'other' else: result_key = categories[award[category_name]] results[result_key] += award['category_count'] return Response({"results": results})
def post(self, request): models = [{'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) filters = json_request.get("filters", None) subawards = json_request["subawards"] if filters is None: raise InvalidParameterException("Missing required request parameters: 'filters'") results = { "contracts": 0, "idvs": 0, "grants": 0, "direct_payments": 0, "loans": 0, "other": 0 } if not subawards else { "subcontracts": 0, "subgrants": 0 } if "award_type_codes" in filters and "no intersection" in filters["award_type_codes"]: # "Special case": there will never be results when the website provides this value return Response({"results": results}) if subawards: queryset = subaward_filter(filters) else: queryset, model = spending_by_award_count(filters) if subawards: queryset = queryset.values('award_type').annotate(category_count=Count('subaward_id')) elif model == 'SummaryAwardView': queryset = queryset.values('category').annotate(category_count=Sum('counts')) else: queryset = queryset.values('category').annotate(category_count=Count('category')) categories = { 'contract': 'contracts', 'idv': 'idvs', 'grant': 'grants', 'direct payment': 'direct_payments', 'loans': 'loans', 'other': 'other' } if not subawards else {'procurement': 'subcontracts', 'grant': 'subgrants'} category_name = 'category' if not subawards else 'award_type' # DB hit here for award in queryset: if award[category_name] is None: result_key = 'other' if not subawards else 'subcontracts' elif award[category_name] not in categories.keys(): result_key = 'other' else: result_key = categories[award[category_name]] results[result_key] += award['category_count'] return Response({"results": results})
def post(self, request): """Returns boolean of whether a download request is greater than the max limit. """ models = [{ "name": "subawards", "key": "subawards", "type": "boolean", "default": False }] models.extend(copy.deepcopy(AWARD_FILTER)) self.original_filters = request.data.get("filters") json_request = TinyShield(models).block(request.data) # If no filters in request return empty object to return all transactions filters = json_request.get("filters", {}) if json_request["subawards"]: total_count = subaward_filter(filters).count() else: queryset, model = download_transaction_count(filters) if model in ["UniversalTransactionView"]: total_count = queryset.count() else: # "summary" materialized views are pre-aggregated and contain a counts col total_count = queryset.aggregate( total_count=Sum("counts"))["total_count"] if total_count is None: total_count = 0 result = { "calculated_transaction_count": total_count, "maximum_transaction_limit": settings.MAX_DOWNLOAD_LIMIT, "transaction_rows_gt_limit": total_count > settings.MAX_DOWNLOAD_LIMIT, "messages": [ get_generic_filters_message( self.original_filters.keys(), [elem["name"] for elem in AWARD_FILTER]) ], } return Response(result)
def __init__(self, payload: dict): """ payload is tightly integrated with """ self.subawards = payload["subawards"] self.category = payload["category"] self.page = payload["page"] self.limit = payload["limit"] self.filters = payload.get("filters", {}) self.lower_limit = (self.page - 1) * self.limit self.upper_limit = self.page * self.limit + 1 # Add 1 for simple "Next Page" check if self.subawards: self.queryset = subaward_filter(self.filters) self.obligation_column = "amount" else: self.queryset = sbc_view_queryset(self.category, self.filters) self.obligation_column = "generated_pragmatic_obligation"
def __init__(self, payload: dict): """ payload is tightly integrated with """ self.subawards = payload["subawards"] self.category = payload["category"] self.page = payload["page"] self.limit = payload["limit"] self.filters = payload.get("filters", {}) self.lower_limit = (self.page - 1) * self.limit self.upper_limit = self.page * self.limit + 1 # Add 1 for simple "Next Page" check if self.subawards: self.queryset = subaward_filter(self.filters) self.obligation_column = "amount" else: self.queryset = sbc_view_queryset(self.category, self.filters) self.obligation_column = "generated_pragmatic_obligation"
def __init__(self, payload: dict): """ payload is tightly integrated with """ self.subawards = payload['subawards'] self.category = payload['category'] self.page = payload['page'] self.limit = payload['limit'] self.filters = payload.get('filters', {}) self.lower_limit = (self.page - 1) * self.limit self.upper_limit = self.page * self.limit + 1 # Add 1 for simple "Next Page" check if self.subawards: self.queryset = subaward_filter(self.filters) self.obligation_column = 'amount' else: self.queryset = sbc_view_queryset(self.category, self.filters) self.obligation_column = 'generated_pragmatic_obligation'
def post(self, request): """Returns boolean of whether a download request is greater than the max limit. """ models = [{ "name": "subawards", "key": "subawards", "type": "boolean", "default": False }] models.extend(copy.deepcopy(AWARD_FILTER)) self.original_filters = request.data.get("filters") json_request = TinyShield(models).block(request.data) # If no filters in request return empty object to return all transactions filters = json_request.get("filters", {}) if json_request["subawards"]: total_count = subaward_filter(filters).count() else: filter_query = QueryWithFilters.generate_transactions_elasticsearch_query( filters) search = TransactionSearch().filter(filter_query) total_count = search.handle_count() if total_count is None: total_count = 0 result = { "calculated_transaction_count": total_count, "maximum_transaction_limit": settings.MAX_DOWNLOAD_LIMIT, "transaction_rows_gt_limit": total_count > settings.MAX_DOWNLOAD_LIMIT, "messages": [ get_generic_filters_message( self.original_filters.keys(), [elem["name"] for elem in AWARD_FILTER]) ], } return Response(result)
def handle_subawards(filters: dict) -> dict: """Turn the filters into the result dictionary when dealing with Sub-Awards Note: Due to how the Django ORM joins to the awards table as an INNER JOIN, it is necessary to explicitly enforce the aggregations to only count Sub-Awards that are linked to a Prime Award. Remove the filter and update if we can move away from this behavior. """ queryset = ( subaward_filter(filters) .filter(award_id__isnull=False) .values("award_type") .annotate(count=Count("subaward_id")) ) results = {} results["subgrants"] = sum([sub["count"] for sub in queryset if sub["award_type"] == "grant"]) results["subcontracts"] = sum([sub["count"] for sub in queryset if sub["award_type"] == "procurement"]) return results
def post(self, request): models = [ {'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}, {'name': 'scope', 'key': 'scope', 'type': 'enum', 'optional': False, 'enum_values': ['place_of_performance', 'recipient_location']}, {'name': 'geo_layer', 'key': 'geo_layer', 'type': 'enum', 'optional': False, 'enum_values': ['state', 'county', 'district']}, {'name': 'geo_layer_filters', 'key': 'geo_layer_filters', 'type': 'array', 'array_type': 'text', 'text_type': 'search'} ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) self.subawards = json_request["subawards"] self.scope = json_request["scope"] self.filters = json_request.get("filters", None) self.geo_layer = json_request["geo_layer"] self.geo_layer_filters = json_request.get("geo_layer_filters", None) fields_list = [] # fields to include in the aggregate query loc_dict = { 'state': 'state_code', 'county': 'county_code', 'district': 'congressional_code' } model_dict = { 'place_of_performance': 'pop', 'recipient_location': 'recipient_location', # 'subawards_place_of_performance': 'pop', # 'subawards_recipient_location': 'recipient_location' } # Build the query based on the scope fields and geo_layers # Fields not in the reference objects above then request is invalid scope_field_name = model_dict.get(self.scope) loc_field_name = loc_dict.get(self.geo_layer) loc_lookup = '{}_{}'.format(scope_field_name, loc_field_name) if self.subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters self.queryset = subaward_filter(self.filters) self.model_name = SubawardView else: self.queryset, self.model_name = spending_by_geography(self.filters) if self.geo_layer == 'state': # State will have one field (state_code) containing letter A-Z column_isnull = 'generated_pragmatic_obligation__isnull' if self.subawards: column_isnull = 'amount__isnull' kwargs = { '{}_country_code'.format(scope_field_name): 'USA', column_isnull: False } # Only state scope will add its own state code # State codes are consistent in database i.e. AL, AK fields_list.append(loc_lookup) state_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.state_results(kwargs, fields_list, loc_lookup) } return Response(state_response) else: # County and district scope will need to select multiple fields # State code is needed for county/district aggregation state_lookup = '{}_{}'.format(scope_field_name, loc_dict['state']) fields_list.append(state_lookup) # Adding regex to county/district codes to remove entries with letters since can't be surfaced by map kwargs = { '{}__isnull'.format('amount' if self.subawards else 'generated_pragmatic_obligation'): False } if self.geo_layer == 'county': # County name added to aggregation since consistent in db county_name_lookup = '{}_county_name'.format(scope_field_name) fields_list.append(county_name_lookup) self.county_district_queryset( kwargs, fields_list, loc_lookup, state_lookup, scope_field_name ) county_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.county_results(state_lookup, county_name_lookup) } return Response(county_response) else: self.county_district_queryset( kwargs, fields_list, loc_lookup, state_lookup, scope_field_name ) district_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.district_results(state_lookup) } return Response(district_response)
def post(self, request): json_request = request.data self.subawards = json_request.get("subawards", False) self.scope = json_request.get("scope") self.filters = json_request.get("filters", {}) self.geo_layer = json_request.get("geo_layer") self.geo_layer_filters = json_request.get("geo_layer_filters") fields_list = [] # fields to include in the aggregate query loc_dict = { 'state': 'state_code', 'county': 'county_code', 'district': 'congressional_code' } model_dict = { 'place_of_performance': 'pop', 'recipient_location': 'recipient_location', 'subawards_place_of_performance': 'place_of_performance', 'subawards_recipient_location': 'recipient__location' } # Build the query based on the scope fields and geo_layers # Fields not in the reference objects above then request is invalid scope_field_name = model_dict.get('{}{}'.format('subawards_' if self.subawards else '', self.scope)) loc_field_name = loc_dict.get(self.geo_layer) loc_lookup = '{}_{}{}'.format(scope_field_name, '_' if self.subawards else '', loc_field_name) if scope_field_name is None: raise InvalidParameterException("Invalid request parameters: scope") if loc_field_name is None: raise InvalidParameterException("Invalid request parameters: geo_layer") if type(self.subawards) is not bool: raise InvalidParameterException('subawards does not have a valid value') if self.subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters self.queryset = subaward_filter(self.filters) self.model_name = Subaward else: self.queryset, self.model_name = spending_by_geography(self.filters) if self.geo_layer == 'state': # State will have one field (state_code) containing letter A-Z kwargs = { '{}_{}country_code'.format(scope_field_name, '_location_' if self.subawards else ''): 'USA', '{}'.format('amount__isnull' if self.subawards else 'federal_action_obligation__isnull'): False } # Only state scope will add its own state code # State codes are consistent in database i.e. AL, AK fields_list.append(loc_lookup) state_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.state_results(kwargs, fields_list, loc_lookup) } return Response(state_response) else: # County and district scope will need to select multiple fields # State code is needed for county/district aggregation state_lookup = '{}_{}{}'.format(scope_field_name, '_' if self.subawards else '', loc_dict['state']) fields_list.append(state_lookup) # Adding regex to county/district codes to remove entries with letters since can't be surfaced by map kwargs = { '{}__isnull'.format('amount' if self.subawards else 'federal_action_obligation'): False } if self.geo_layer == 'county': # County name added to aggregation since consistent in db county_name_lookup = '{}_{}county_name'.format(scope_field_name, '_' if self.subawards else '') fields_list.append(county_name_lookup) self.county_district_queryset( kwargs, fields_list, loc_lookup, state_lookup, scope_field_name ) county_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.county_results(state_lookup, county_name_lookup) } return Response(county_response) else: self.county_district_queryset( kwargs, fields_list, loc_lookup, state_lookup, scope_field_name ) district_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.district_results(state_lookup) } return Response(district_response)
def post(self, request: Request) -> Response: models = [ { "name": "subawards", "key": "subawards", "type": "boolean", "default": False }, { "name": "scope", "key": "scope", "type": "enum", "optional": False, "enum_values": ["place_of_performance", "recipient_location"], }, { "name": "geo_layer", "key": "geo_layer", "type": "enum", "optional": False, "enum_values": ["state", "county", "district"], }, { "name": "geo_layer_filters", "key": "geo_layer_filters", "type": "array", "array_type": "text", "text_type": "search", }, ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) original_filters = request.data.get("filters") json_request = TinyShield(models).block(request.data) agg_key_dict = { "county": "county_agg_key", "district": "congressional_agg_key", "state": "state_agg_key", } location_dict = { "county": "county_code", "district": "congressional_code", "state": "state_code" } model_dict = { "place_of_performance": "pop", "recipient_location": "recipient_location", # 'subawards_place_of_performance': 'pop', # 'subawards_recipient_location': 'recipient_location' } self.scope_field_name = model_dict[json_request["scope"]] self.agg_key = f"{self.scope_field_name}_{agg_key_dict[json_request['geo_layer']]}" self.filters = json_request.get("filters") self.geo_layer = GeoLayer(json_request["geo_layer"]) self.geo_layer_filters = json_request.get("geo_layer_filters") self.loc_field_name = location_dict[self.geo_layer.value] self.loc_lookup = f"{self.scope_field_name}_{self.loc_field_name}" self.subawards = json_request["subawards"] if self.subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters self.model_name = SubawardView self.queryset = subaward_filter(self.filters) self.obligation_column = "amount" result = self.query_django() elif is_experimental_elasticsearch_api(request): if self.scope_field_name == "pop": scope_filter_name = "place_of_performance_scope" else: scope_filter_name = "recipient_scope" # Only search for values within USA, but don't overwrite a user's search if scope_filter_name not in self.filters: self.filters[scope_filter_name] = "domestic" self.obligation_column = "generated_pragmatic_obligation" filter_query = QueryWithFilters.generate_transactions_elasticsearch_query( self.filters) result = self.query_elasticsearch(filter_query) else: self.queryset, self.model_name = spending_by_geography( self.filters) self.obligation_column = "generated_pragmatic_obligation" result = self.query_django() return Response({ "scope": json_request["scope"], "geo_layer": self.geo_layer.value, "results": result, "messages": get_generic_filters_message( original_filters.keys(), [elem["name"] for elem in AWARD_FILTER]), })
def post(self, request): models = [ {"name": "subawards", "key": "subawards", "type": "boolean", "default": False}, { "name": "scope", "key": "scope", "type": "enum", "optional": False, "enum_values": ["place_of_performance", "recipient_location"], }, { "name": "geo_layer", "key": "geo_layer", "type": "enum", "optional": False, "enum_values": ["state", "county", "district"], }, { "name": "geo_layer_filters", "key": "geo_layer_filters", "type": "array", "array_type": "text", "text_type": "search", }, ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) self.subawards = json_request["subawards"] self.scope = json_request["scope"] self.filters = json_request.get("filters", None) self.geo_layer = json_request["geo_layer"] self.geo_layer_filters = json_request.get("geo_layer_filters", None) fields_list = [] # fields to include in the aggregate query loc_dict = {"state": "state_code", "county": "county_code", "district": "congressional_code"} model_dict = { "place_of_performance": "pop", "recipient_location": "recipient_location", # 'subawards_place_of_performance': 'pop', # 'subawards_recipient_location': 'recipient_location' } # Build the query based on the scope fields and geo_layers # Fields not in the reference objects above then request is invalid scope_field_name = model_dict.get(self.scope) loc_field_name = loc_dict.get(self.geo_layer) loc_lookup = "{}_{}".format(scope_field_name, loc_field_name) if self.subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters self.queryset = subaward_filter(self.filters) self.model_name = SubawardView else: self.queryset, self.model_name = spending_by_geography(self.filters) if self.geo_layer == "state": # State will have one field (state_code) containing letter A-Z column_isnull = "generated_pragmatic_obligation__isnull" if self.subawards: column_isnull = "amount__isnull" kwargs = {"{}_country_code".format(scope_field_name): "USA", column_isnull: False} # Only state scope will add its own state code # State codes are consistent in database i.e. AL, AK fields_list.append(loc_lookup) state_response = { "scope": self.scope, "geo_layer": self.geo_layer, "results": self.state_results(kwargs, fields_list, loc_lookup), "messages": [get_time_period_message()], } return Response(state_response) else: # County and district scope will need to select multiple fields # State code is needed for county/district aggregation state_lookup = "{}_{}".format(scope_field_name, loc_dict["state"]) fields_list.append(state_lookup) # Adding regex to county/district codes to remove entries with letters since can't be surfaced by map kwargs = {"{}__isnull".format("amount" if self.subawards else "generated_pragmatic_obligation"): False} if self.geo_layer == "county": # County name added to aggregation since consistent in db county_name_lookup = "{}_county_name".format(scope_field_name) fields_list.append(county_name_lookup) self.county_district_queryset(kwargs, fields_list, loc_lookup, state_lookup, scope_field_name) county_response = { "scope": self.scope, "geo_layer": self.geo_layer, "results": self.county_results(state_lookup, county_name_lookup), "messages": [get_time_period_message()], } return Response(county_response) else: self.county_district_queryset(kwargs, fields_list, loc_lookup, state_lookup, scope_field_name) district_response = { "scope": self.scope, "geo_layer": self.geo_layer, "results": self.district_results(state_lookup), "messages": [get_time_period_message()], } return Response(district_response)
def post(self, request): models = [{ 'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False }, { 'name': 'scope', 'key': 'scope', 'type': 'enum', 'optional': False, 'enum_values': ['place_of_performance', 'recipient_location'] }, { 'name': 'geo_layer', 'key': 'geo_layer', 'type': 'enum', 'optional': False, 'enum_values': ['state', 'county', 'district'] }, { 'name': 'geo_layer_filters', 'key': 'geo_layer_filters', 'type': 'array', 'array_type': 'text', 'text_type': 'search' }] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) self.subawards = json_request["subawards"] self.scope = json_request["scope"] self.filters = json_request.get("filters", None) self.geo_layer = json_request["geo_layer"] self.geo_layer_filters = json_request.get("geo_layer_filters", None) fields_list = [] # fields to include in the aggregate query loc_dict = { 'state': 'state_code', 'county': 'county_code', 'district': 'congressional_code' } model_dict = { 'place_of_performance': 'pop', 'recipient_location': 'recipient_location', # 'subawards_place_of_performance': 'pop', # 'subawards_recipient_location': 'recipient_location' } # Build the query based on the scope fields and geo_layers # Fields not in the reference objects above then request is invalid scope_field_name = model_dict.get(self.scope) loc_field_name = loc_dict.get(self.geo_layer) loc_lookup = '{}_{}'.format(scope_field_name, loc_field_name) if self.subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters self.queryset = subaward_filter(self.filters) self.model_name = SubawardView else: self.queryset, self.model_name = spending_by_geography( self.filters) if self.geo_layer == 'state': # State will have one field (state_code) containing letter A-Z column_isnull = 'generated_pragmatic_obligation__isnull' if self.subawards: column_isnull = 'amount__isnull' kwargs = { '{}_country_code'.format(scope_field_name): 'USA', column_isnull: False } # Only state scope will add its own state code # State codes are consistent in database i.e. AL, AK fields_list.append(loc_lookup) state_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.state_results(kwargs, fields_list, loc_lookup) } return Response(state_response) else: # County and district scope will need to select multiple fields # State code is needed for county/district aggregation state_lookup = '{}_{}'.format(scope_field_name, loc_dict['state']) fields_list.append(state_lookup) # Adding regex to county/district codes to remove entries with letters since can't be surfaced by map kwargs = { '{}__isnull'.format('amount' if self.subawards else 'generated_pragmatic_obligation'): False } if self.geo_layer == 'county': # County name added to aggregation since consistent in db county_name_lookup = '{}_county_name'.format(scope_field_name) fields_list.append(county_name_lookup) self.county_district_queryset(kwargs, fields_list, loc_lookup, state_lookup, scope_field_name) county_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.county_results(state_lookup, county_name_lookup) } return Response(county_response) else: self.county_district_queryset(kwargs, fields_list, loc_lookup, state_lookup, scope_field_name) district_response = { 'scope': self.scope, 'geo_layer': self.geo_layer, 'results': self.district_results(state_lookup) } return Response(district_response)
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 post(self, request): """Return all budget function/subfunction titles matching the provided search text""" models = [{ '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'] = True''' json_request = TinyShield(models).block(request.data) filters = json_request.get("filters", None) subawards = json_request["subawards"] if filters is None: raise InvalidParameterException( "Missing one or more required request parameters: filters") results = { "contracts": 0, "grants": 0, "direct_payments": 0, "loans": 0, "other": 0 } if not subawards else { "subcontracts": 0, "subgrants": 0 } if "award_type_codes" in filters and "no intersection" in filters[ "award_type_codes"]: # "Special case": there will never be results when the website provides this value return Response({"results": results}) if subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters queryset = subaward_filter(filters) else: queryset, model = spending_by_award_count(filters) if subawards: queryset = queryset \ .values('award_type') \ .annotate(category_count=Count('subaward_id')) elif model == 'SummaryAwardView': queryset = queryset \ .values('category') \ .annotate(category_count=Sum('counts')) else: # for IDV CONTRACTS category is null. change to contract queryset = queryset \ .values('category') \ .annotate(category_count=Count(Coalesce('category', Value('contract')))) \ .values('category', 'category_count') categories = { 'contract': 'contracts', 'grant': 'grants', 'direct payment': 'direct_payments', 'loans': 'loans', 'other': 'other' } if not subawards else { 'procurement': 'subcontracts', 'grant': 'subgrants' } category_name = 'category' if not subawards else 'award_type' # DB hit here for award in queryset: if award[category_name] is None: result_key = 'contracts' if not subawards else 'subcontracts' elif award[category_name] not in categories.keys(): result_key = 'other' else: result_key = categories[award[category_name]] results[result_key] += award['category_count'] # build response return Response({"results": 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""" valid_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm'] models = [ {'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False}, {'name': 'group', 'key': 'group', 'type': 'enum', 'enum_values': valid_groups, 'optional': False} ] models.extend(copy.deepcopy(AWARD_FILTER)) models.extend(copy.deepcopy(PAGINATION)) json_request = TinyShield(models).block(request.data) group = json_request['group'] subawards = json_request['subawards'] filters = json_request.get("filters", None) if filters is None: raise InvalidParameterException('Missing request parameters: filters') # define what values are needed in the sql query # we do not use matviews for Subaward filtering, just the Subaward download filters if subawards: queryset = subaward_filter(filters) else: queryset = spending_over_time(filters).values('action_date', 'generated_pragmatic_obligation') # build response response = {'group': group, 'results': []} nested_order = '' # list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000 group_results = OrderedDict() # for Subawards we extract data from action_date if subawards: data_set = queryset \ .values('award_type') \ .annotate(month=ExtractMonth('action_date'), transaction_amount=Sum('amount')) \ .values('month', 'fiscal_year', 'transaction_amount') else: # for Awards we Sum generated_pragmatic_obligation for transaction_amount queryset = queryset.values('fiscal_year') if group in ('fy', 'fiscal_year'): data_set = queryset \ .annotate(transaction_amount=Sum('generated_pragmatic_obligation')) \ .values('fiscal_year', 'transaction_amount') else: # quarterly also takes months and aggregates the data data_set = queryset \ .annotate( month=ExtractMonth('action_date'), transaction_amount=Sum('generated_pragmatic_obligation')) \ .values('fiscal_year', 'month', 'transaction_amount') for record in data_set: # generate unique key by fiscal date, depending on group key = {'fiscal_year': str(record['fiscal_year'])} if group in ('m', 'month'): # generate the fiscal month key['month'] = generate_fiscal_month(date(year=2017, day=1, month=record['month'])) nested_order = 'month' elif group in ('q', 'quarter'): # generate the fiscal quarter key['quarter'] = FiscalDate(2017, record['month'], 1).quarter nested_order = 'quarter' key = str(key) # if key exists, aggregate if group_results.get(key) is None: group_results[key] = record['transaction_amount'] else: group_results[key] = group_results.get(key) + record['transaction_amount'] # convert result into expected format, sort by key to meet front-end specs results = [] # Expected results structure # [{ # 'time_period': {'fy': '2017', 'quarter': '3'}, # 'aggregated_amount': '200000000' # }] sorted_group_results = sorted( group_results.items(), key=lambda k: ( ast.literal_eval(k[0])['fiscal_year'], int(ast.literal_eval(k[0])[nested_order])) if nested_order else (ast.literal_eval(k[0])['fiscal_year'])) for key, value in sorted_group_results: key_dict = ast.literal_eval(key) result = {'time_period': key_dict, 'aggregated_amount': float(value) if value else float(0)} results.append(result) response['results'] = results return Response(response)
def post(self, request): """Return all budget function/subfunction titles matching the provided search text""" json_request = request.data filters = json_request.get("filters", None) subawards = json_request.get("subawards", False) if filters is None: raise InvalidParameterException("Missing one or more required request parameters: filters") if type(subawards) is not bool: raise InvalidParameterException("subawards does not have a valid value") if subawards: # We do not use matviews for Subaward filtering, just the Subaward download filters queryset = subaward_filter(filters) else: queryset, model = spending_by_award_count(filters) if subawards: queryset = queryset \ .values('award_type') \ .annotate(category_count=Count('id')) elif model == 'SummaryAwardView': queryset = queryset \ .values('category') \ .annotate(category_count=Sum('counts')) else: # for IDV CONTRACTS category is null. change to contract queryset = queryset \ .values('category') \ .annotate(category_count=Count(Coalesce('category', Value('contract')))) \ .values('category', 'category_count') results = { "contracts": 0, "grants": 0, "direct_payments": 0, "loans": 0, "other": 0 } if not subawards else { "subcontracts": 0, "subgrants": 0 } categories = { 'contract': 'contracts', 'grant': 'grants', 'direct payment': 'direct_payments', 'loans': 'loans', 'other': 'other' } if not subawards else {'procurement': 'subcontracts', 'grant': 'subgrants'} category_name = 'category' if not subawards else 'award_type' # DB hit here for award in queryset: if award[category_name] is None: result_key = 'contracts' if not subawards else 'subcontracts' elif award[category_name] not in categories.keys(): result_key = 'other' else: result_key = categories[award[category_name]] results[result_key] += award['category_count'] # build response return Response({"results": results})
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""" json_request = request.data group = json_request.get('group', None) filters = json_request.get('filters', None) subawards = json_request.get('subawards', False) if group is None: raise InvalidParameterException('Missing one or more required request parameters: group') if filters is None: raise InvalidParameterException('Missing one or more required request parameters: filters') potential_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm'] if group not in potential_groups: raise InvalidParameterException('group does not have a valid value') if type(subawards) is not bool: raise InvalidParameterException('subawards does not have a valid value') # define what values are needed in the sql query # we do not use matviews for Subaward filtering, just the Subaward download filters queryset = subaward_filter(filters) if subawards else spending_over_time(filters) \ .values('action_date', 'federal_action_obligation', 'original_loan_subsidy_cost') # build response response = {'group': group, 'results': []} nested_order = '' # list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000 group_results = OrderedDict() # for Subawards we extract data from action_date, for Awards we use sum_transaction_amount if subawards: data_set = queryset.values('award_type'). \ annotate(month=ExtractMonth('action_date'), year=ExtractYear('action_date'), transaction_amount=Sum('amount')). \ values('month', 'year', 'transaction_amount') else: data_set = queryset.values('fiscal_year') if not (group == 'fy' or group == 'fiscal_year'): # quarterly also takes months and aggregates the data data_set = queryset.annotate(month=ExtractMonth('action_date')).values('fiscal_year', 'month') filter_types = filters['award_type_codes'] if 'award_type_codes' in filters else award_type_mapping data_set = sum_transaction_amount(data_set, filter_types=filter_types) for record in data_set: # create fiscal year data based on the action_date for Subawards if subawards: record['fiscal_year'] = generate_fiscal_year(date(record['year'], record['month'], 1)) # generate unique key by fiscal date, depending on group key = {'fiscal_year': str(record['fiscal_year'])} if group == 'm' or group == 'month': # generate the fiscal month key['month'] = generate_fiscal_month(date(year=2017, day=1, month=record['month'])) nested_order = 'month' elif group == 'q' or group == 'quarter': # generate the fiscal quarter key['quarter'] = FiscalDate(2017, record['month'], 1).quarter nested_order = 'quarter' key = str(key) # if key exists, aggregate if group_results.get(key) is None: group_results[key] = record['transaction_amount'] else: group_results[key] = group_results.get(key) + record['transaction_amount'] # convert result into expected format, sort by key to meet front-end specs results = [] # Expected results structure # [{ # 'time_period': {'fy': '2017', 'quarter': '3'}, # 'aggregated_amount': '200000000' # }] sorted_group_results = sorted( group_results.items(), key=lambda k: ( ast.literal_eval(k[0])['fiscal_year'], int(ast.literal_eval(k[0])[nested_order])) if nested_order else (ast.literal_eval(k[0])['fiscal_year'])) for key, value in sorted_group_results: key_dict = ast.literal_eval(key) result = {'time_period': key_dict, 'aggregated_amount': float(value) if value else float(0)} results.append(result) response['results'] = results return Response(response)