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)
def generate_treasury_account_query(queryset, account_type, tas_id):
""" Derive necessary fields for a treasury account-grouped query """
derived_fields = {
"last_reported_submission_period": FiscalYearAndQuarter("reporting_period_end"),
"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": FiscalYearAndQuarter("reporting_period_end"),
"last_modified_date"
+ NAMING_CONFLICT_DISCRIMINATOR: Cast(Max("submission__published_date"), output_field=DateField()),
}
if account_type == "award_financial":
derived_fields = award_financial_derivations(derived_fields)
return queryset.annotate(**derived_fields)
