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)