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)