def county_district_queryset(self, kwargs, fields_list, loc_lookup, state_lookup, scope_field_name):
# Filtering queryset to specific county/districts if requested
# Since geo_layer_filters comes as concat of state fips and county/district codes
# need to split for the geocode_filter
if self.geo_layer_filters:
self.queryset &= geocode_filter_locations(scope_field_name, [
{'state': fips_to_code.get(x[:2]), self.geo_layer: x[2:], 'country': 'USA'}
for x in self.geo_layer_filters
], self.model_name, not self.subawards)
else:
# Adding null, USA, not number filters for specific partial index when not using geocode_filter
kwargs['{}__{}'.format(loc_lookup, 'isnull')] = False
kwargs['{}__{}'.format(state_lookup, 'isnull')] = False
kwargs['{}_{}country_code'.format(scope_field_name, '_location_' if self.subawards else '')] = 'USA'
kwargs['{}__{}'.format(loc_lookup, 'iregex')] = r'^[0-9]*(\.\d+)?$'
# Turn county/district codes into float since inconsistent in database
# Codes in location table ex: '01', '1', '1.0'
# Cast will group codes as a float and will combine inconsistent codes
self.geo_queryset = self.queryset.filter(**kwargs) \
.values(*fields_list) \
.annotate(code_as_float=Cast(loc_lookup, FloatField()))
filter_types = self.filters['award_type_codes'] if 'award_type_codes' in self.filters else award_type_mapping
self.geo_queryset = sum_transaction_amount(self.geo_queryset, filter_types=filter_types) if not self.subawards \
else self.geo_queryset.annotate(transaction_amount=Sum('amount'))
return self.geo_queryset
def state_results(self, filter_args, lookup_fields, loc_lookup):
# Adding additional state filters if specified
if self.geo_layer_filters:
self.queryset = self.queryset.filter(
**{'{}__{}'.format(loc_lookup, 'in'): self.geo_layer_filters})
else:
# Adding null filter for state for specific partial index
# when not using geocode_filter
filter_args['{}__isnull'.format(loc_lookup)] = False
self.geo_queryset = self.queryset.filter(**filter_args) \
.values(*lookup_fields)
filter_types = self.filters[
'award_type_codes'] if 'award_type_codes' in self.filters else award_type_mapping
self.geo_queryset = sum_transaction_amount(self.geo_queryset,
filter_types=filter_types)
# State names are inconsistent in database (upper, lower, null)
# Used lookup instead to be consistent
results = [{
'shape_code':
x[loc_lookup],
'aggregated_amount':
x['transaction_amount'],
'display_name':
code_to_state.get(x[loc_lookup], {
'name': 'None'
}).get('name').title()
} for x in self.geo_queryset]
return results
def test_sum_query_filter_loans_awards(award_exp_data):
"""Tests on award results when there is a loan award type filter"""
result = sum_transaction_amount(award_exp_data,
filter_types=["07", "08"],
calculate_totals=False)
# Contract award's total should equal zero since total original subsity is zero
assert result.filter(type="B")[0]["transaction_amount"] == 0
assert result.filter(type="07")[0]["transaction_amount"] == 1000.00
def test_sum_query_all_types_awards(award_exp_data):
"""Tests on award results when there is a no award type filter"""
result = sum_transaction_amount(award_exp_data,
filter_types=award_type_mapping,
calculate_totals=False)
assert result.filter(type="07")[0]["transaction_amount"] == 1000.00
assert result.filter(type="B")[0]["transaction_amount"] == 1200.00
def test_sum_query_filter_other_awards(award_exp_data):
result = sum_transaction_amount(award_exp_data,
filter_types=["A", "B", "C"],
calculate_totals=False)
# Contract award's total should equal zero since total obligation is zero
assert result.filter(type="07")[0]["transaction_amount"] == 0
assert result.filter(type="B")[0]["transaction_amount"] == 1200.00
def test_sum_query_filter_other_awards(award_exp_data):
result = sum_transaction_amount(award_exp_data,
filter_types=['A', 'B', 'C'],
calculate_totals=False)
# Contract award's total should equal zero since total obligation is zero
assert result.filter(type='07')[0]['transaction_amount'] == 0
assert result.filter(type='B')[0]['transaction_amount'] == 1200.00
def get(self, request, fips):
get_request = request.query_params
year = get_request.get('year')
fips = fips.zfill(2)
state_data_qs = StateData.objects.filter(fips=fips)
if not state_data_qs.count():
raise InvalidParameterException('Invalid FIPS ({}) or data unavailable.'.format(fips))
state_data_results = state_data_qs.values()
general_state_data = state_data_results[0]
# recreate filters
filters = {'place_of_performance_locations': [{'country': 'USA', 'state': general_state_data['code']}]}
today = datetime.now()
if year and year.isdigit():
time_period = [{
'start_date': '{}-10-01'.format(int(year)-1),
'end_date': '{}-09-30'.format(year)
}]
elif year == 'all':
time_period = [{
'start_date': '2008-10-01',
'end_date': datetime.strftime(today, '%Y-%m-%d')
}]
elif year == 'latest' or not year:
last_year = today - relativedelta(years=1)
time_period = [{
'start_date': datetime.strftime(last_year, '%Y-%m-%d'),
'end_date': datetime.strftime(today, '%Y-%m-%d')
}]
else:
raise InvalidParameterException('Invalid year: {}.'.format(year))
filters['time_period'] = time_period
state_pop_data = self.get_state_data(state_data_results, 'population', year)
state_mhi_data = self.get_state_data(state_data_results, 'median_household_income', year)
# calculate award total filtered by state
total_award_qs = universal_transaction_matview_filter(filters)
total_award_qs = sum_transaction_amount(total_award_qs.values('award_id'))
total_award_count = total_award_qs.values('award_id').distinct().count()
total_award_amount = total_award_qs.aggregate(total=Sum('transaction_amount'))['total'] \
if total_award_count else 0
if year == 'all' or (year and year.isdigit() and int(year) == generate_fiscal_year(today)):
amt_per_capita = None
else:
amt_per_capita = round(total_award_amount/state_pop_data['population'], 2) if total_award_count else 0
# calculate subaward total filtered by state - COMMENTED OUT FOR NOW
# total_subaward_qs = subaward_filter(filters)
# total_subaward_count = total_subaward_qs.count()
# total_subaward_amount = total_subaward_qs.aggregate(total=Sum('amount'))['total'] \
# if total_subaward_count else 0
result = {
'name': general_state_data['name'],
'code': general_state_data['code'],
'fips': general_state_data['fips'],
'type': general_state_data['type'],
'population': state_pop_data['population'],
'pop_year': state_pop_data['year'],
'pop_source': state_pop_data['pop_source'],
'median_household_income': state_mhi_data['median_household_income'],
'mhi_year': state_mhi_data['year'],
'mhi_source': state_mhi_data['mhi_source'],
'total_prime_amount': total_award_amount,
'total_prime_awards': total_award_count,
'award_amount_per_capita': amt_per_capita,
# Commented out for now
# 'total_subaward_amount': total_subaward_amount,
# 'total_subawards': total_subaward_count,
}
return Response(result)
def post(self, request):
"""Return all budget function/subfunction titles matching the provided search text"""
json_request = request.data
group = json_request.get('group', None)
filters = json_request.get('filters', None)
subawards = json_request.get('subawards', False)
if group is None:
raise InvalidParameterException('Missing one or more required request parameters: group')
if filters is None:
raise InvalidParameterException('Missing one or more required request parameters: filters')
potential_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm']
if group not in potential_groups:
raise InvalidParameterException('group does not have a valid value')
if type(subawards) is not bool:
raise InvalidParameterException('subawards does not have a valid value')
# define what values are needed in the sql query
# we do not use matviews for Subaward filtering, just the Subaward download filters
queryset = subaward_filter(filters) if subawards else spending_over_time(filters) \
.values('action_date', 'federal_action_obligation', 'original_loan_subsidy_cost')
# build response
response = {'group': group, 'results': []}
nested_order = ''
# list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000
group_results = OrderedDict()
# for Subawards we extract data from action_date, for Awards we use sum_transaction_amount
if subawards:
data_set = queryset.values('award_type'). \
annotate(month=ExtractMonth('action_date'), year=ExtractYear('action_date'),
transaction_amount=Sum('amount')). \
values('month', 'year', 'transaction_amount')
else:
data_set = queryset.values('fiscal_year')
if not (group == 'fy' or group == 'fiscal_year'):
# quarterly also takes months and aggregates the data
data_set = queryset.annotate(month=ExtractMonth('action_date')).values('fiscal_year', 'month')
filter_types = filters['award_type_codes'] if 'award_type_codes' in filters else award_type_mapping
data_set = sum_transaction_amount(data_set, filter_types=filter_types)
for record in data_set:
# create fiscal year data based on the action_date for Subawards
if subawards:
record['fiscal_year'] = generate_fiscal_year(date(record['year'], record['month'], 1))
# generate unique key by fiscal date, depending on group
key = {'fiscal_year': str(record['fiscal_year'])}
if group == 'm' or group == 'month':
# generate the fiscal month
key['month'] = generate_fiscal_month(date(year=2017, day=1, month=record['month']))
nested_order = 'month'
elif group == 'q' or group == 'quarter':
# generate the fiscal quarter
key['quarter'] = FiscalDate(2017, record['month'], 1).quarter
nested_order = 'quarter'
key = str(key)
# if key exists, aggregate
if group_results.get(key) is None:
group_results[key] = record['transaction_amount']
else:
group_results[key] = group_results.get(key) + record['transaction_amount']
# convert result into expected format, sort by key to meet front-end specs
results = []
# Expected results structure
# [{
# 'time_period': {'fy': '2017', 'quarter': '3'},
# 'aggregated_amount': '200000000'
# }]
sorted_group_results = sorted(
group_results.items(),
key=lambda k: (
ast.literal_eval(k[0])['fiscal_year'],
int(ast.literal_eval(k[0])[nested_order])) if nested_order else (ast.literal_eval(k[0])['fiscal_year']))
for key, value in sorted_group_results:
key_dict = ast.literal_eval(key)
result = {'time_period': key_dict, 'aggregated_amount': float(value) if value else float(0)}
results.append(result)
response['results'] = results
return Response(response)
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
json_request = request.data
category = json_request.get("category", None)
scope = json_request.get("scope", None)
filters = json_request.get("filters", None)
limit = json_request.get("limit", 10)
page = json_request.get("page", 1)
lower_limit = (page - 1) * limit
upper_limit = page * limit
if category is None:
raise InvalidParameterException("Missing one or more required request parameters: category")
potential_categories = ["awarding_agency", "funding_agency", "recipient", "cfda_programs", "industry_codes"]
if category not in potential_categories:
raise InvalidParameterException("Category does not have a valid value")
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 test_sum_query_filter_other_trans(trans_exp_data):
"""Tests aggregation on transactions when there is a non-loan award type filter"""
result = sum_transaction_amount(trans_exp_data,
filter_types=["A", "B", "C"])
assert result[0]["transaction_amount"] == 200
def test_sum_query_all_types_trans(trans_exp_data):
"""Tests aggregation on transactions where there is no award type filter"""
result = sum_transaction_amount(trans_exp_data,
filter_types=award_type_mapping)
assert result[0]["transaction_amount"] == 300
def post(self, request):
"""Return all budget function/subfunction titles matching the provided search text"""
json_request = request.data
group = json_request.get('group', None)
filters = json_request.get('filters', None)
if group is None:
raise InvalidParameterException(
'Missing one or more required request parameters: group')
if filters is None:
raise InvalidParameterException(
'Missing one or more required request parameters: filters')
potential_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm']
if group not in potential_groups:
raise InvalidParameterException(
'group does not have a valid value')
queryset = spending_over_time(filters)
filter_types = filters[
'award_type_codes'] if 'award_type_codes' in filters else award_type_mapping
# define what values are needed in the sql query
queryset = queryset.values('action_date', 'federal_action_obligation',
'original_loan_subsidy_cost')
# build response
response = {'group': group, 'results': []}
nested_order = ''
group_results = OrderedDict(
) # list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000
if group == 'fy' or group == 'fiscal_year':
fy_set = sum_transaction_amount(queryset.values('fiscal_year'),
filter_types=filter_types)
for trans in fy_set:
key = {'fiscal_year': str(trans['fiscal_year'])}
key = str(key)
group_results[key] = trans['transaction_amount']
elif group == 'm' or group == 'month':
month_set = queryset.annotate(month=ExtractMonth('action_date')) \
.values('fiscal_year', 'month')
month_set = sum_transaction_amount(month_set,
filter_types=filter_types)
for trans in month_set:
# Convert month to fiscal month
fiscal_month = generate_fiscal_month(
date(year=2017, day=1, month=trans['month']))
key = {
'fiscal_year': str(trans['fiscal_year']),
'month': str(fiscal_month)
}
key = str(key)
group_results[key] = trans['transaction_amount']
nested_order = 'month'
else: # quarterly, take months and add them up
month_set = queryset.annotate(month=ExtractMonth('action_date')) \
.values('fiscal_year', 'month')
month_set = sum_transaction_amount(month_set,
filter_types=filter_types)
for trans in month_set:
# Convert month to quarter
quarter = FiscalDate(2017, trans['month'], 1).quarter
key = {
'fiscal_year': str(trans['fiscal_year']),
'quarter': str(quarter)
}
key = str(key)
# If key exists {fy : quarter}, aggregate
if group_results.get(key) is None:
group_results[key] = trans['transaction_amount']
else:
if trans['transaction_amount']:
group_results[key] = group_results.get(
key) + trans['transaction_amount']
else:
group_results[key] = group_results.get(key)
nested_order = 'quarter'
# convert result into expected format, sort by key to meet front-end specs
results = []
# Expected results structure
# [{
# 'time_period': {'fy': '2017', 'quarter': '3'},
# 'aggregated_amount': '200000000'
# }]
sorted_group_results = sorted(
group_results.items(),
key=lambda k: (ast.literal_eval(k[0])['fiscal_year'],
int(ast.literal_eval(k[0])[nested_order]))
if nested_order else (ast.literal_eval(k[0])['fiscal_year']))
for key, value in sorted_group_results:
key_dict = ast.literal_eval(key)
result = {
'time_period': key_dict,
'aggregated_amount': float(value) if value else float(0)
}
results.append(result)
response['results'] = results
return Response(response)
def test_sum_query_filter_loans_trans(trans_exp_data):
"""Tests aggregation on transactions when there is a loan award type filter"""
result = sum_transaction_amount(trans_exp_data, filter_types=['07', '08'])
assert result[0]['transaction_amount'] == 100