def generate_treasury_account_query(queryset, account_type, tas_id, filters): """ Derive necessary fields for a treasury account-grouped query """ derived_fields = { "allocation_transfer_agency_identifier_name": get_agency_name_annotation(tas_id, "allocation_transfer_agency_id"), "agency_identifier_name": get_agency_name_annotation(tas_id, "agency_id"), "submission_period": get_fyp_or_q_notation("submission"), "gross_outlay_amount": generate_gross_outlay_amount_derived_field(filters, account_type), "gross_outlay_amount_fyb_to_period_end": generate_gross_outlay_amount_derived_field(filters, account_type), } lmd = "last_modified_date" + NAMING_CONFLICT_DISCRIMINATOR if account_type == "award_financial": # Separating out last_modified_date like this prevents unnecessary grouping in the full File # C TAS download. Keeping it as MAX caused grouping on every single column in the SQL statement. derived_fields[lmd] = Cast("submission__published_date", output_field=DateField()) derived_fields = award_financial_derivations(derived_fields) else: derived_fields[lmd] = Cast(Max("submission__published_date"), output_field=DateField()) return queryset.annotate(**derived_fields)
def generate_federal_account_query(queryset, account_type, tas_id, filters): """ Group by federal account (and budget function/subfunction) and SUM all other fields """ derived_fields = { "reporting_agency_name": StringAgg("submission__reporting_agency_name", "; ", distinct=True), "budget_function": StringAgg(f"{tas_id}__budget_function_title", "; ", distinct=True), "budget_subfunction": StringAgg(f"{tas_id}__budget_subfunction_title", "; ", distinct=True), "submission_period": get_fyp_or_q_notation("submission"), "agency_identifier_name": get_agency_name_annotation(tas_id, "agency_id"), "last_modified_date" + NAMING_CONFLICT_DISCRIMINATOR: Cast(Max("submission__published_date"), output_field=DateField()), "gross_outlay_amount": Sum(generate_gross_outlay_amount_derived_field(filters, account_type)), "gross_outlay_amount_fyb_to_period_end": Sum(generate_gross_outlay_amount_derived_field(filters, account_type)), } if account_type == "award_financial": derived_fields = award_financial_derivations(derived_fields) queryset = queryset.annotate(**derived_fields) # List of all columns that may appear in A, B, or C files that can be summed all_summed_cols = [ "budget_authority_unobligated_balance_brought_forward", "adjustments_to_unobligated_balance_brought_forward", "budget_authority_appropriated_amount", "borrowing_authority_amount", "contract_authority_amount", "spending_authority_from_offsetting_collections_amount", "total_other_budgetary_resources_amount", "total_budgetary_resources", "obligations_incurred", "deobligations_or_recoveries_or_refunds_from_prior_year", "unobligated_balance", "status_of_budgetary_resources_total", "transaction_obligated_amount", ] # Group by all columns within the file that can't be summed fed_acct_values_dict = query_paths[account_type]["federal_account"] grouped_cols = [ fed_acct_values_dict[val] for val in fed_acct_values_dict if val not in all_summed_cols ] queryset = queryset.values(*grouped_cols) # Sum all fields from all_summed_cols that appear in this file values_dict = query_paths[account_type] summed_cols = { val: Sum(values_dict["treasury_account"].get(val, None)) for val in values_dict["federal_account"] if val in all_summed_cols } return queryset.annotate(**summed_cols)