def database_data_layer(self) -> tuple:
if self.subawards:
queryset = subaward_filter(self.filters)
obligation_column = "amount"
else:
queryset = spending_over_time(self.filters)
obligation_column = "generated_pragmatic_obligation"
values = ["fy"]
if self.group == "month":
queryset = queryset.annotate(month=FiscalMonth("action_date"),
fy=FiscalYear("action_date"))
values.append("month")
elif self.group == "quarter":
queryset = queryset.annotate(quarter=FiscalQuarter("action_date"),
fy=FiscalYear("action_date"))
values.append("quarter")
elif self.group == "fiscal_year":
queryset = queryset.annotate(fy=FiscalYear("action_date"))
queryset = (queryset.values(*values).annotate(
aggregated_amount=Sum(obligation_column)).order_by(
*["{}".format(value) for value in values]))
return queryset, values
def database_data_layer(self):
if self.subawards:
queryset = subaward_filter(self.filters)
obligation_column = "amount"
else:
queryset = spending_over_time(self.filters)
obligation_column = "generated_pragmatic_obligation"
values = ["fy"]
if self.groupings[self.group] == "month":
queryset = queryset.annotate(month=FiscalMonth("action_date"), fy=FiscalYear("action_date"))
values.append("month")
elif self.groupings[self.group] == "quarter":
queryset = queryset.annotate(quarter=FiscalQuarter("action_date"), fy=FiscalYear("action_date"))
values.append("quarter")
elif self.groupings[self.group] == "fiscal_year":
queryset = queryset.annotate(fy=FiscalYear("action_date"))
queryset = (
queryset.values(*values)
.annotate(aggregated_amount=Sum(obligation_column))
.order_by(*["{}".format(value) for value in values])
)
return queryset, values
def post(self, request):
"""Return all budget function/subfunction titles matching the provided search text"""
valid_groups = ['quarter', 'fiscal_year', 'month', 'fy', 'q', 'm']
models = [
{'name': 'subawards', 'key': 'subawards', 'type': 'boolean', 'default': False},
{'name': 'group', 'key': 'group', 'type': 'enum', 'enum_values': valid_groups, 'optional': False}
]
models.extend(copy.deepcopy(AWARD_FILTER))
models.extend(copy.deepcopy(PAGINATION))
json_request = TinyShield(models).block(request.data)
group = json_request['group']
subawards = json_request['subawards']
filters = json_request.get("filters", None)
if filters is None:
raise InvalidParameterException('Missing request parameters: filters')
# define what values are needed in the sql query
# we do not use matviews for Subaward filtering, just the Subaward download filters
if subawards:
queryset = subaward_filter(filters)
else:
queryset = spending_over_time(filters).values('action_date', 'generated_pragmatic_obligation')
# build response
response = {'group': group, 'results': []}
nested_order = ''
# list of time_period objects ie {"fy": "2017", "quarter": "3"} : 1000
group_results = OrderedDict()
# for Subawards we extract data from action_date
if subawards:
data_set = queryset \
.values('award_type') \
.annotate(month=ExtractMonth('action_date'), transaction_amount=Sum('amount')) \
.values('month', 'fiscal_year', 'transaction_amount')
else:
# for Awards we Sum generated_pragmatic_obligation for transaction_amount
queryset = queryset.values('fiscal_year')
if group in ('fy', 'fiscal_year'):
data_set = queryset \
.annotate(transaction_amount=Sum('generated_pragmatic_obligation')) \
.values('fiscal_year', 'transaction_amount')
else:
# quarterly also takes months and aggregates the data
data_set = queryset \
.annotate(
month=ExtractMonth('action_date'),
transaction_amount=Sum('generated_pragmatic_obligation')) \
.values('fiscal_year', 'month', 'transaction_amount')
for record in data_set:
# generate unique key by fiscal date, depending on group
key = {'fiscal_year': str(record['fiscal_year'])}
if group in ('m', 'month'):
# generate the fiscal month
key['month'] = generate_fiscal_month(date(year=2017, day=1, month=record['month']))
nested_order = 'month'
elif group in ('q', 'quarter'):
# generate the fiscal quarter
key['quarter'] = FiscalDate(2017, record['month'], 1).quarter
nested_order = 'quarter'
key = str(key)
# if key exists, aggregate
if group_results.get(key) is None:
group_results[key] = record['transaction_amount']
else:
group_results[key] = group_results.get(key) + record['transaction_amount']
# convert result into expected format, sort by key to meet front-end specs
results = []
# Expected results structure
# [{
# 'time_period': {'fy': '2017', 'quarter': '3'},
# 'aggregated_amount': '200000000'
# }]
sorted_group_results = sorted(
group_results.items(),
key=lambda k: (
ast.literal_eval(k[0])['fiscal_year'],
int(ast.literal_eval(k[0])[nested_order])) if nested_order else (ast.literal_eval(k[0])['fiscal_year']))
for key, value in sorted_group_results:
key_dict = ast.literal_eval(key)
result = {'time_period': key_dict, 'aggregated_amount': float(value) if value else float(0)}
results.append(result)
response['results'] = results
return Response(response)
def post(self, request):
"""Return all budget function/subfunction titles matching the provided search text"""
json_request = request.data
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"""
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)
