def post(self, request):
models = [
{'name': 'fields', 'key': 'fields', 'type': 'array', 'array_type': 'text', 'text_type': 'search'},
]
models.extend(AWARD_FILTER)
models.extend(PAGINATION)
for m in models:
if m['name'] in ('keyword', 'award_type_codes', 'sort'):
m['optional'] = False
validated_payload = TinyShield(models).block(request.data)
if validated_payload['sort'] not in validated_payload['fields']:
raise InvalidParameterException("Sort value not found in fields: {}".format(validated_payload['sort']))
lower_limit = (validated_payload['page'] - 1) * validated_payload['limit']
success, response, total = search_transactions(validated_payload, lower_limit, validated_payload['limit'] + 1)
if not success:
raise InvalidParameterException(response)
metadata = get_simple_pagination_metadata(len(response), validated_payload['limit'], validated_payload['page'])
results = []
for transaction in response[:validated_payload['limit']]:
results.append(transaction)
response = {
'limit': validated_payload['limit'],
'results': results,
'page_metadata': metadata
}
return Response(response)
def post(self, request: dict):
"""Return all budget function/subfunction titles matching the provided search text"""
categories = [
'awarding_agency', 'awarding_subagency', 'funding_agency',
'funding_subagency', 'recipient_duns', 'recipient_parent_duns',
'cfda', 'psc', 'naics', 'county', 'district'
]
models = [{
'name': 'category',
'key': 'category',
'type': 'enum',
'enum_values': categories,
'optional': False
}, {
'name': 'subawards',
'key': 'subawards',
'type': 'boolean',
'default': False,
'optional': True
}]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
# Apply/enforce POST body schema and data validation in request
validated_payload = TinyShield(models).block(request.data)
# Execute the business logic for the endpoint and return a python dict to be converted to a Django response
return Response(BusinessLogic(validated_payload).results())
def validate_api_request(self, json_payload):
self.groupings = {
'quarter': 'quarter',
'q': 'quarter',
'fiscal_year': 'fiscal_year',
'fy': 'fiscal_year',
'month': 'month',
'm': 'month',
}
models = [{
'name': 'group',
'key': 'group',
'type': 'enum',
'enum_values': list(self.groupings.keys()),
'default': 'fy',
}]
advanced_search_filters = [
model for model in copy.deepcopy(AWARD_FILTER)
if model['name'] in ('time_period', 'recipient_id')
]
for model in advanced_search_filters:
if model['name'] in ('time_period', 'recipient_id'):
model['optional'] = False
models.extend(advanced_search_filters)
return TinyShield(models).block(json_payload)
def post(self, request):
models = [
{
'name': 'fields',
'key': 'fields',
'type': 'array',
'array_type': 'text',
'text_type': 'search',
'optional': False
},
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
for m in models:
if m['name'] in ('keywords', 'award_type_codes', 'sort'):
m['optional'] = False
validated_payload = TinyShield(models).block(request.data)
if validated_payload['sort'] not in validated_payload['fields']:
raise InvalidParameterException(
"Sort value not found in fields: {}".format(
validated_payload['sort']))
if "filters" in validated_payload and "no intersection" in validated_payload[
"filters"]["award_type_codes"]:
# "Special case": there will never be results when the website provides this value
return Response({
"limit": validated_payload["limit"],
"results": [],
"page_metadata": {
"page": validated_payload["page"],
"next": None,
"previous": None,
"hasNext": False,
"hasPrevious": False,
},
})
lower_limit = (validated_payload['page'] -
1) * validated_payload['limit']
success, response, total = search_transactions(
validated_payload, lower_limit, validated_payload['limit'] + 1)
if not success:
raise InvalidParameterException(response)
metadata = get_simple_pagination_metadata(len(response),
validated_payload['limit'],
validated_payload['page'])
results = []
for transaction in response[:validated_payload['limit']]:
results.append(transaction)
response = {
'limit': validated_payload['limit'],
'results': results,
'page_metadata': metadata
}
return Response(response)
def validate_api_request(self, json_payload):
self.groupings = {
"quarter": "quarter",
"q": "quarter",
"fiscal_year": "fiscal_year",
"fy": "fiscal_year",
"month": "month",
"m": "month",
}
models = [{
"name": "group",
"key": "group",
"type": "enum",
"enum_values": list(self.groupings.keys()),
"default": "fy",
}]
advanced_search_filters = [
model for model in copy.deepcopy(AWARD_FILTER)
if model["name"] in ("time_period", "recipient_id")
]
for model in advanced_search_filters:
if model["name"] in ("time_period", "recipient_id"):
model["optional"] = False
models.extend(advanced_search_filters)
return TinyShield(models).block(json_payload)
def validate_request_data(self, json_data):
self.groupings = {
"quarter": "quarter",
"q": "quarter",
"fiscal_year": "fiscal_year",
"fy": "fiscal_year",
"month": "month",
"m": "month",
}
models = [
{
"name": "subawards",
"key": "subawards",
"type": "boolean",
"default": False
},
{
"name": "group",
"key": "group",
"type": "enum",
"enum_values": list(self.groupings.keys()),
"default": "fy",
"optional": False, # allow to be optional in the future
},
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
validated_data = TinyShield(models).block(json_data)
if validated_data.get("filters", None) is None:
raise InvalidParameterException(
"Missing request parameters: filters")
return validated_data
def test_check_models():
'''We want this test to fail if either AWARD_FILTERS has an invalid model,
OR if the logic of the check_models function has been corrupted.
It will fail if an exception is raised. Otherwise it will define the global TS object
so we can use it in the remaining tests.'''
global TS
TS = TinyShield(copy.deepcopy(AWARD_FILTER))
def post(self, request):
"""
Returns a summary of transactions which match the award search filter
Desired values:
total number of transactions `award_count`
The federal_action_obligation sum of all those transactions `award_spending`
*Note* Only deals with prime awards, future plans to include sub-awards.
"""
models = [{
'name': 'keywords',
'key': 'filters|keywords',
'type': 'array',
'array_type': 'text',
'text_type': 'search',
'optional': False,
'text_min': 3
}]
validated_payload = TinyShield(models).block(request.data)
results = spending_by_transaction_sum_and_count(validated_payload)
if not results:
raise ElasticsearchConnectionException(
'Error generating the transaction sums and counts')
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))
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 post(self, request):
award_types = list(AWARD_TYPES.keys()) + ["all"]
models = [
{
"name": "keyword",
"key": "keyword",
"type": "text",
"text_type": "search"
},
{
"name": "award_type",
"key": "award_type",
"type": "enum",
"enum_values": award_types,
"default": "all"
},
]
models.extend(copy.deepcopy(PAGINATION)) # page, limit, sort, order
new_sort = {
"type": "enum",
"enum_values": ["name", "duns", "amount"],
"default": "amount"
}
models = update_model_in_list(models, "sort", new_sort)
models = update_model_in_list(models, "limit", {"default": 50})
validated_payload = TinyShield(models).block(request.data)
results, page_metadata = get_recipients(filters=validated_payload)
return Response({"page_metadata": page_metadata, "results": results})
def post(self, request):
models = [{'name': 'keyword', 'key': 'filters|keyword', 'type': 'text', 'text_type': 'search', 'min': 3}]
validated_payload = TinyShield(models).block(request.data)
results = spending_by_transaction_count(validated_payload)
if not results:
raise ElasticsearchConnectionException('Error during the aggregations')
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 _parse_and_validate_request(self, generated_unique_award_id) -> dict:
# TODO: May be better to accept either generated_unique_award_id or
# internal pk to ease transition from v1 to v2
models = [{
"key": "generated_unique_award_id",
"name": "generated_unique_award_id",
"type": "text",
"text_type": "search",
"optional": False
}]
request_dict = {"generated_unique_award_id": generated_unique_award_id}
validated_request_data = TinyShield(models).block(request_dict)
return validated_request_data
def _parse_and_validate_request(self, request_dict: dict) -> dict:
models = deepcopy(PAGINATION)
models.append({
"key": "award_id",
"name": "award_id",
"type": "integer",
"optional": False
})
for model in models:
# Change sort to an enum of the desired values
if model["name"] == "sort":
model["type"] = "enum"
model["enum_values"] = list(self.transaction_lookup.keys())
model["default"] = "action_date"
validated_request_data = TinyShield(models).block(request_dict)
return validated_request_data
def _parse_and_validate_request(self, request_dict):
models = deepcopy(PAGINATION)
models.append({
"key": "award_id",
"name": "award_id",
"type": "integer",
"optional": True,
"default": None,
"allow_nulls": True
})
for model in models:
# Change sort to an enum of the desired values
if model["name"] == "sort":
model["type"] = "enum"
model["enum_values"] = list(self.subaward_lookup.keys())
model["default"] = "subaward_number"
validated_request_data = TinyShield(models).block(request_dict)
return validated_request_data
def post(self, request: dict):
"""Return all budget function/subfunction titles matching the provided search text"""
categories = [
"awarding_agency",
"awarding_subagency",
"funding_agency",
"funding_subagency",
"recipient_duns",
"recipient_parent_duns",
"cfda",
"psc",
"naics",
"county",
"district",
"country",
"state_territory",
"federal_account",
]
models = [
{
"name": "category",
"key": "category",
"type": "enum",
"enum_values": categories,
"optional": False
},
{
"name": "subawards",
"key": "subawards",
"type": "boolean",
"default": False,
"optional": True
},
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
# Apply/enforce POST body schema and data validation in request
validated_payload = TinyShield(models).block(request.data)
# Execute the business logic for the endpoint and return a python dict to be converted to a Django response
return Response(BusinessLogic(validated_payload).results())
def _parse_and_validate_request(self, request_dict):
""" Validate the Request object includes the required fields """
fy_range = [str(i) for i in range(2001, FiscalDateTime.today().year + 1)]
last_fy = str(SubmissionAttributes.last_certified_fy()) or str(FiscalDateTime.today().year)
request_settings = [
{'key': 'sort', 'name': 'sort', 'type': 'object', 'optional': True, 'object_keys': {
'field': {'type': 'enum', 'enum_values': ['budgetary_resources', 'managing_agency', 'account_name',
'account_number'],
'optional': True, 'default': 'budgetary_resources'},
'direction': {'type': 'enum', 'enum_values': ['asc', 'desc'], 'optional': True, 'default': 'asc'},
}, 'default': {'field': 'budgetary_resources', 'direction': 'asc'}},
{'key': 'page', 'name': 'page', 'type': 'integer', 'default': 1, 'min': 1, 'optional': True},
{'key': 'limit', 'name': 'limit', 'type': 'integer', 'default': 10, 'min': 1, 'max': 100, 'optional': True},
{'key': 'filters', 'name': 'filters', 'type': 'object', 'optional': True, 'object_keys': {
'fy': {'type': 'enum', 'enum_values': fy_range, 'optional': True, 'default': last_fy},
}, 'default': {'fy': last_fy}},
{'key': 'keyword', 'name': 'keyword', 'type': 'text', 'text_type': 'search', 'optional': True}
]
validated_request_data = TinyShield(request_settings).block(request_dict)
return validated_request_data
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
models = [
{'name': 'category', 'key': 'category', 'type': 'enum',
'enum_values': ["awarding_agency", "funding_agency", "recipient", "cfda_programs", "industry_codes"],
'optional': False}
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
json_request = TinyShield(models).block(request.data)
category = json_request["category"]
scope = json_request.get("scope", None)
filters = json_request.get("filters", None)
limit = json_request["limit"]
page = json_request["page"]
lower_limit = (page - 1) * limit
upper_limit = page * limit
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):
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 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"]
lower_limit = (page - 1) * limit
upper_limit = page * limit
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 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:
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"""
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)