def results(self) -> dict:
results = []
# filter the transactions by category
if self.category in ("recipient_parent_duns", ):
results = self.parent_recipient()
elif self.category in ("federal_account"):
results = self.federal_account()
page_metadata = get_simple_pagination_metadata(len(results),
self.limit, self.page)
response = {
"category":
self.category,
"limit":
self.limit,
"page_metadata":
page_metadata,
# alias_response is a workaround for tests instead of applying any aliases in the querysets
"results":
results[:self.limit],
"messages":
get_generic_filters_message(
self.original_filters.keys(),
[elem["name"] for elem in AWARD_FILTER]),
}
return response
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']))
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):
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 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 results(self) -> dict:
results = []
# filter the transactions by category
if self.category in ("awarding_agency", "awarding_subagency"):
results = self.awarding_agency()
elif self.category in ("funding_agency", "funding_subagency"):
results = self.funding_agency()
elif self.category in ("recipient_duns", "recipient_parent_duns"):
results = self.recipient()
elif self.category in ("cfda", "psc", "naics"):
results = self.industry_and_other_codes()
elif self.category in ("county", "district", "state_territory", "country"):
results = self.location()
elif self.category in ("federal_account"):
results = self.federal_account()
page_metadata = get_simple_pagination_metadata(len(results), self.limit, self.page)
response = {
"category": self.category,
"limit": self.limit,
"page_metadata": page_metadata,
# alias_response is a workaround for tests instead of applying any aliases in the querysets
"results": results[: self.limit],
}
return response
def build_elasticsearch_result(self, request, response) -> dict:
results = []
for res in response:
hit = res.to_dict()
# Parsing API response values from ES query result JSON
# We parse the `hit` (result from elasticsearch) to get the award type, use the type to determine
# which lookup dict to use, and then use that lookup to retrieve the correct value requested from `fields`
row = {}
for field in request["fields"]:
row[field] = hit.get(TRANSACTIONS_SOURCE_LOOKUP[field])
row["generated_internal_id"] = hit["generated_unique_award_id"]
row["internal_id"] = hit["award_id"]
results.append(row)
metadata = get_simple_pagination_metadata(len(response),
request["limit"],
request["page"])
return {
"limit":
request["limit"],
"results":
results[:request["limit"]],
"page_metadata":
metadata,
"messages": [
get_generic_filters_message(
request["filters"].keys(),
[elem["name"] for elem in AWARD_FILTER_NO_RECIPIENT_ID])
],
}
def results(self) -> dict:
results = []
# filter the transactions by category
if self.category in ('awarding_agency', 'awarding_subagency'):
results = self.awarding_agency()
elif self.category in ('funding_agency', 'funding_subagency'):
results = self.funding_agency()
elif self.category in ('recipient_duns', 'recipient_parent_duns'):
results = self.recipient()
elif self.category in ('cfda', 'psc', 'naics'):
results = self.industry_and_other_codes()
elif self.category in ('county', 'district'):
results = self.location()
page_metadata = get_simple_pagination_metadata(len(results),
self.limit, self.page)
response = {
'category': self.category,
'limit': self.limit,
'page_metadata': page_metadata,
# alias_response is a workaround for tests instead of applying any aliases in the querysets
'results': results[:self.limit],
}
return response
def post(self, request, format=None):
""" Return all high-level Federal Account information """
request_data = self._parse_and_validate_request(request.data)
limit = request_data['limit']
page = request_data['page']
sort_field = request_data['sort']['field']
sort_direction = request_data['sort']['direction']
fy = request_data['filters']['fy']
keyword = request_data.get('keyword', None)
lower_limit = (page - 1) * limit
upper_limit = page * limit
agency_subquery = ToptierAgency.objects.filter(
cgac_code=OuterRef('corrected_agency_identifier'))
queryset = FederalAccount.objects.\
filter(treasuryappropriationaccount__account_balances__final_of_fy=True,
treasuryappropriationaccount__account_balances__submission__reporting_period_start__fy=fy).\
annotate(corrected_agency_identifier=Func(F('agency_identifier'), function='CORRECTED_CGAC')).\
annotate(account_id=F('id'),
account_number=Concat(F('agency_identifier'), Value('-'), F('main_account_code')),
account_name=F('account_title'),
budgetary_resources=Sum(
'treasuryappropriationaccount__account_balances__total_budgetary_resources_amount_cpe'),
managing_agency=Subquery(agency_subquery.values('name')[:1]),
managing_agency_acronym=Subquery(agency_subquery.values('abbreviation')[:1]))
# add keyword filter, if it exists
if keyword:
queryset = queryset.filter(
Q(account_name__icontains=keyword)
| Q(account_number__contains=keyword)
| Q(managing_agency__icontains=keyword)
| Q(managing_agency_acronym__contains=keyword.upper()))
if sort_direction == 'desc':
queryset = queryset.order_by(F(sort_field).desc(nulls_last=True))
else:
queryset = queryset.order_by(F(sort_field).asc())
result = {
'count': queryset.count(),
'limit': limit,
'page': page,
'fy': fy,
'keyword': keyword
}
resultset = queryset.values('account_id', 'account_number',
'account_name', 'budgetary_resources',
'agency_identifier', 'managing_agency',
'managing_agency_acronym')
resultset = resultset[lower_limit:upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit,
page)
result.update(page_metadata)
resultset = resultset[:limit]
result['results'] = resultset
return Response(result)
def results(self) -> dict:
results = []
# filter the transactions by category
if self.category in ("awarding_agency", "awarding_subagency"):
results = self.awarding_agency()
elif self.category in ("funding_agency", "funding_subagency"):
results = self.funding_agency()
elif self.category in ("recipient_duns", "recipient_parent_duns"):
results = self.recipient()
elif self.category in ("cfda", "psc", "naics"):
results = self.industry_and_other_codes()
elif self.category in ("county", "district", "state_territory", "country"):
results = self.location()
elif self.category in ("federal_account"):
results = self.federal_account()
page_metadata = get_simple_pagination_metadata(len(results), self.limit, self.page)
response = {
"category": self.category,
"limit": self.limit,
"page_metadata": page_metadata,
# alias_response is a workaround for tests instead of applying any aliases in the querysets
"results": results[: self.limit],
}
return response
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:
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: Request) -> Response:
request_data = self._parse_and_validate_request(request.data)
results = self._business_logic(request_data)
page_metadata = get_simple_pagination_metadata(len(results), request_data["limit"], request_data["page"])
response = {"page_metadata": page_metadata, "results": results[: request_data["limit"]]}
return Response(response)
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 post(self, request: Request) -> Response:
results = self._business_logic(request.data)
page_metadata = get_simple_pagination_metadata(len(results),
request.data["limit"],
request.data["page"])
response = OrderedDict((("results", results[:request.data["limit"]]),
("page_metadata", page_metadata)))
return Response(response)
def post(self, request: Request) -> Response:
request_data = self._parse_and_validate_request(request.data)
results = self._business_logic(request_data)
page_metadata = get_simple_pagination_metadata(len(results),
request_data['limit'],
request_data['page'])
response = OrderedDict((('results', results[:request_data['limit']]),
('page_metadata', page_metadata)))
return Response(response)
def post(self, request):
request_data = self._parse_and_validate_request(request.data)
results = self._business_logic(request_data)
page_metadata = get_simple_pagination_metadata(len(results), request_data["limit"], request_data["page"])
response = {
"page_metadata": page_metadata,
"results": results[:request_data["limit"]],
}
return Response(response)
def post(self, request: Request) -> Response:
request_data = self._parse_and_validate_request(request.data)
results = self._business_logic(request_data)
page_metadata = get_simple_pagination_metadata(len(results), request_data['limit'], request_data['page'])
response = OrderedDict((
('results', results[:request_data['limit']]),
('page_metadata', page_metadata)
))
return Response(response)
def post(self, request: Request) -> Response:
group_by = "group by ca.awarding_agency_id, taa.agency_id || '-' || taa.main_account_code"
columns = """sum(nullif(faba.transaction_obligated_amount, 'NaN')) total_transaction_obligated_amount,
taa.agency_id || '-' || taa.main_account_code federal_account,
ca.awarding_agency_id agency_id"""
request_data = self._parse_and_validate_request(request.data)
results = self._business_logic(request_data, columns, group_by)
page_metadata = get_simple_pagination_metadata(len(results), request_data['limit'], request_data['page'])
response = OrderedDict((
('results', results[:request_data['limit']]),
('page_metadata', page_metadata)
))
return Response(response)
def post(self, request, format=None):
"""Return all high-level Federal Account information"""
limit = request.data.get("limit", 10)
page = request.data.get("page", 1)
sorting = request.data.get("sort", {'field': 'budgetary_resources', 'direction': 'desc'})
sort_field = sorting.get('field', 'budgetary_resources')
sort_direction = sorting.get('direction', 'asc')
filters = request.data.get('filters', {})
fy = filters.get('fy') or SubmissionAttributes.last_certified_fy()
lower_limit = (page - 1) * limit
upper_limit = page * limit
agency_subquery = ToptierAgency.objects.filter(cgac_code=OuterRef('corrected_agency_identifier'))
queryset = FederalAccount.objects.\
filter(treasuryappropriationaccount__account_balances__final_of_fy=True,
treasuryappropriationaccount__account_balances__submission__reporting_period_start__fy=fy).\
annotate(corrected_agency_identifier=Func(F('agency_identifier'), function='CORRECTED_CGAC')).\
annotate(account_id=F('id'),
account_number=Concat(F('agency_identifier'), Value('-'), F('main_account_code')),
account_name=F('account_title'),
budgetary_resources=Sum(
'treasuryappropriationaccount__account_balances__total_budgetary_resources_amount_cpe'),
managing_agency=Subquery(agency_subquery.values('name')[:1]),
managing_agency_acronym=Subquery(agency_subquery.values('abbreviation')[:1])
)
if sort_direction == 'desc':
queryset = queryset.order_by(F(sort_field).desc(nulls_last=True))
else:
queryset = queryset.order_by(F(sort_field).asc())
result = {'count': queryset.count(), 'limit': limit, 'page': page, 'fy': fy}
resultset = queryset.values('account_id', 'account_number', 'account_name', 'budgetary_resources',
'agency_identifier', 'managing_agency', 'managing_agency_acronym')
resultset = resultset[lower_limit:upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit, page)
result.update(page_metadata)
resultset = resultset[:limit]
result['results'] = resultset
return Response(result)
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, format=None):
""" Return all high-level Federal Account information """
request_data = self._parse_and_validate_request(request.data)
limit = request_data["limit"]
page = request_data["page"]
sort_field = request_data["sort"]["field"]
sort_direction = request_data["sort"]["direction"]
keyword = request_data.get("keyword", None)
fy = request_data["filters"]["fy"]
agency_id = request_data["filters"].get("agency_identifier", None)
lower_limit = (page - 1) * limit
upper_limit = page * limit
agency_subquery = ToptierAgency.objects.filter(cgac_code=OuterRef("corrected_agency_identifier"))
queryset = (
FederalAccount.objects.filter(
treasuryappropriationaccount__account_balances__final_of_fy=True,
treasuryappropriationaccount__account_balances__submission__reporting_period_start__fy=fy,
)
.annotate(corrected_agency_identifier=Func(F("agency_identifier"), function="CORRECTED_CGAC"))
.annotate(
account_id=F("id"),
account_name=F("account_title"),
account_number=F("federal_account_code"),
budgetary_resources=Sum(
"treasuryappropriationaccount__account_balances__total_budgetary_resources_amount_cpe"
),
managing_agency=Subquery(agency_subquery.values("name")[:1]),
managing_agency_acronym=Subquery(agency_subquery.values("abbreviation")[:1]),
)
)
# add keyword filter, if it exists
if keyword:
queryset = queryset.filter(
Q(account_name__icontains=keyword)
| Q(account_number__contains=keyword)
| Q(managing_agency__icontains=keyword)
| Q(managing_agency_acronym__contains=keyword.upper())
)
if agency_id is not None:
tta_list = DOD_ARMED_FORCES_CGAC if agency_id == DOD_CGAC else [agency_id]
tta_filter = Q()
for tta in tta_list:
tta_filter |= Q(account_number__startswith=tta)
queryset &= queryset.filter(tta_filter)
if sort_direction == "desc":
queryset = queryset.order_by(F(sort_field).desc(nulls_last=True))
else:
queryset = queryset.order_by(F(sort_field).asc())
result = {"count": queryset.count(), "limit": limit, "page": page, "fy": fy, "keyword": keyword}
resultset = queryset.values(
"account_id",
"account_number",
"account_name",
"budgetary_resources",
"agency_identifier",
"managing_agency",
"managing_agency_acronym",
)
resultset = resultset[lower_limit : upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit, page)
result.update(page_metadata)
resultset = resultset[:limit]
result["results"] = resultset
return Response(result)
def post(self, request, format=None):
""" Return all high-level Federal Account information """
request_data = self._parse_and_validate_request(request.data)
limit = request_data["limit"]
page = request_data["page"]
sort_field = request_data["sort"]["field"]
sort_direction = request_data["sort"]["direction"]
keyword = request_data.get("keyword", None)
fy = request_data["filters"]["fy"]
agency_id = request_data["filters"].get("agency_identifier")
lower_limit = (page - 1) * limit
upper_limit = page * limit
account_filter = {}
if agency_id:
account_filter["parent_toptier_agency__toptier_code"] = agency_id
# Only return federal accounts that have ever had a submission. Only return budgetary_resources for
# the fiscal year requested. Note that we use Func instead of Sum below because Sum wants to perform a
# grouping when we don't want one.
queryset = (FederalAccount.objects.annotate(has_submission=Exists(
SubmissionAttributes.objects.filter(
appropriationaccountbalances__treasury_account_identifier__federal_account_id
=OuterRef("id")).values("pk")
)).filter(**account_filter, has_submission=True).annotate(
account_id=F("id"),
account_name=F("account_title"),
account_number=F("federal_account_code"),
budgetary_resources=Subquery(
AppropriationAccountBalances.objects.filter(
final_of_fy=True,
submission__reporting_period_start__fy=fy,
treasury_account_identifier__federal_account_id=OuterRef(
"id"),
).annotate(
the_sum=Func(F("total_budgetary_resources_amount_cpe"),
function="SUM")).values("the_sum"),
output_field=DecimalField(max_digits=23, decimal_places=2),
),
managing_agency=F("parent_toptier_agency__name"),
managing_agency_acronym=F("parent_toptier_agency__abbreviation"),
))
# add keyword filter, if it exists
if keyword:
queryset = queryset.filter(
Q(account_name__icontains=keyword)
| Q(account_number__contains=keyword)
| Q(managing_agency__icontains=keyword)
| Q(managing_agency_acronym__contains=keyword.upper()))
if sort_direction == "desc":
queryset = queryset.order_by(
F(sort_field).desc(nulls_last=True), "-federal_account_code")
else:
queryset = queryset.order_by(
F(sort_field).asc(), "federal_account_code")
result = {
"count": queryset.count(),
"limit": limit,
"page": page,
"fy": fy,
"keyword": keyword
}
resultset = queryset.values(
"account_id",
"account_number",
"account_name",
"budgetary_resources",
"agency_identifier",
"managing_agency",
"managing_agency_acronym",
)
resultset = resultset[lower_limit:upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit,
page)
result.update(page_metadata)
resultset = resultset[:limit]
result["results"] = resultset
return Response(result)