def get_agency_data(self):
agency_filters = []
if self.filter is not None:
agency_filters.append(Q(name__icontains=self.filter) | Q(abbreviation__icontains=self.filter))
results = (
ToptierAgencyPublishedDABSView.objects.annotate(
fiscal_period=Subquery(
SubmissionAttributes.objects.filter(
submission_window__submission_fiscal_year=self.fiscal_year,
submission_window__submission_reveal_date__lte=now(),
toptier_code=OuterRef("toptier_code"),
)
.values("toptier_code", "submission_window__submission_fiscal_year")
.annotate(fiscal_period=Max("submission_window__submission_fiscal_month"))
.values("fiscal_period")
)
)
.filter(*agency_filters)
.annotate(
current_total_budget_authority_amount=Subquery(
ReportingAgencyOverview.objects.filter(
toptier_code=OuterRef("toptier_code"),
fiscal_year=self.fiscal_year,
fiscal_period=OuterRef("fiscal_period"),
).values("total_budgetary_resources")
),
periods=Subquery(
SubmissionAttributes.objects.filter(
reporting_fiscal_year=self.fiscal_year,
toptier_code=OuterRef("toptier_code"),
submission_window__submission_reveal_date__lte=now(),
)
.values("toptier_code")
.annotate(
period=ArrayAgg(
ConcatAll(
Value('{"period": '),
Cast("reporting_fiscal_period", output_field=TextField()),
Value(', "quarter": '),
Cast("reporting_fiscal_quarter", output_field=TextField()),
Value(', "submission_dates":{ "publication_date": "'),
Cast("published_date", output_field=TextField()),
Value('", "certification_date": "'),
Cast("certified_date", output_field=TextField()),
Value('"}, "quarterly": '),
Cast("quarter_format_flag", output_field=TextField()),
Value("}"),
output_field=TextField(),
)
)
)
.values("period"),
output_field=TextField(),
),
)
.values("name", "toptier_code", "abbreviation", "current_total_budget_authority_amount", "periods")
)
return self.format_results(results)
def get_last_closed_quarter_relative_to_month(
fiscal_year: int, fiscal_month: int) -> Optional[dict]:
""" Returns the most recently closed fiscal quarter in the fiscal year less than or equal to the fiscal month. """
return (DABSSubmissionWindowSchedule.objects.filter(
is_quarter=True,
submission_fiscal_year=fiscal_year,
submission_fiscal_month__lte=fiscal_month,
submission_reveal_date__lte=now(),
).order_by("-submission_fiscal_quarter").values_list(
"submission_fiscal_quarter", flat=True).first())
def final_submissions_for_all_fy() -> List[tuple]:
"""
Returns a list the latest monthly and quarterly submission for each
fiscal year IF it is "closed" aka ready for display on USAspending.gov
"""
return (DABSSubmissionWindowSchedule.objects.filter(
submission_reveal_date__lte=now()).values(
"submission_fiscal_year", "is_quarter").annotate(
fiscal_year=F("submission_fiscal_year"),
fiscal_period=Max("submission_fiscal_month")).values_list(
"fiscal_year", "is_quarter", "fiscal_period", named=True))
def calculate_last_completed_fiscal_quarter(fiscal_year):
"""
Returns either the most recently completed fiscal quarter or None if it's too early in the
fiscal year for the first quarter to be considered "completed". Should always return None for
future fiscal years.
"""
row = DABSSubmissionWindowSchedule.objects.filter(
Q(submission_reveal_date__lte=now()) & Q(submission_fiscal_year=fiscal_year) & Q(is_quarter=True)
).aggregate(Max("submission_fiscal_quarter"))
return row["submission_fiscal_quarter__max"]
def get_total_federal_budgetary_resources(self):
submission_windows = (DABSSubmissionWindowSchedule.objects.filter(
submission_reveal_date__lte=now()).values(
"submission_fiscal_year").annotate(
fiscal_year=F("submission_fiscal_year"),
fiscal_period=Max("submission_fiscal_month")))
q = Q()
for sub in submission_windows:
q |= Q(fiscal_year=sub["fiscal_year"]) & Q(
fiscal_period=sub["fiscal_period"])
results = (
GTASSF133Balances.objects.filter(q).values("fiscal_year").annotate(
total_budgetary_resources=Sum(
"total_budgetary_resources_cpe")).values(
"fiscal_year", "total_budgetary_resources"))
return results
def fail_processing(submission_id: int, processor_id: str,
exception: BaseException) -> int:
"""
Release our claim on the submission, update the processing_failed timestamp, and record the
exception. Returns 1 if the submission was successfully released or 0 if not. A submission
that was not successfully released was likely claimed by another process either in error or
because the heartbeat was not properly refreshed.
"""
exception_message = "".join(
format_exception(type(exception), exception, exception.__traceback__))
return DABSLoaderQueue.objects.filter(
submission_id=submission_id,
processor_id=processor_id,
state=DABSLoaderQueue.IN_PROGRESS).update(state=DABSLoaderQueue.FAILED,
processor_id=None,
processing_failed=now(),
exception=exception_message)
def start_processing(submission_id: int, processor_id: str) -> int:
"""
Claim the submission and update the processing_started timestamp. Returns 1 if the submission
was successfully claimed or 0 if not. Submissions can only be claimed if they are not already
claimed by another processor.
"""
now_ = now()
return DABSLoaderQueue.objects.filter(
Q(state=DABSLoaderQueue.READY) | Q(state=DABSLoaderQueue.FAILED),
submission_id=submission_id,
processor_id__isnull=True,
).update(
state=DABSLoaderQueue.IN_PROGRESS,
processor_id=processor_id,
processing_started=now_,
heartbeat=now_,
processing_failed=None,
exception=None,
)
def update_heartbeat(submission_id: int, processor_id: str) -> int:
"""
We maintain a heartbeat on in-progress submissions so processing can be restarted in the event
of a silent failure. Returns the count of updated heartbeats. Should always return 1. If it
doesn't then your submission no longer exists in the queue or someone else has claimed it and
that's probably a problem. This uses psycopg2 instead of Django because we need a connection
outside of those managed by Django to ensure the heartbeat is outside of any outstanding
transactions.
"""
sql = f"""
update {DABSLoaderQueue._meta.db_table}
set heartbeat = %s::timestamptz
where submission_id = %s and processor_id = %s and state = %s
"""
with psycopg2.connect(dsn=get_database_dsn_string()) as connection:
with connection.cursor() as cursor:
cursor.execute(sql, [
now(), submission_id, processor_id, DABSLoaderQueue.IN_PROGRESS
])
return cursor.rowcount
def get_agency_budgetary_resources(self):
aab = (AppropriationAccountBalances.objects.filter(
treasury_account_identifier__funding_toptier_agency=self.
toptier_agency,
submission__submission_window__submission_reveal_date__lte=now(),
submission__is_final_balances_for_fy=True,
).values("submission__reporting_fiscal_year").annotate(
agency_budgetary_resources=Sum(
"total_budgetary_resources_amount_cpe"),
agency_total_obligated=Sum(
"obligations_incurred_total_by_tas_cpe"),
))
fbr = self.get_total_federal_budgetary_resources()
resources = {}
for z in fbr:
resources.update(
{z["fiscal_year"]: z["total_budgetary_resources"]})
results = [{
"fiscal_year":
x["submission__reporting_fiscal_year"],
"agency_budgetary_resources":
x["agency_budgetary_resources"],
"agency_total_obligated":
x["agency_total_obligated"],
"total_budgetary_resources":
resources.get(x["submission__reporting_fiscal_year"]),
} for x in aab]
years = [x["fiscal_year"] for x in results]
for year in range(2017, current_fiscal_year() + 1):
if year not in years:
results.append({
"fiscal_year":
year,
"agency_budgetary_resources":
None,
"agency_total_obligated":
None,
"total_budgetary_resources":
resources.get(year),
})
return sorted(results, key=lambda x: x["fiscal_year"], reverse=True)
def get(self, request, toptier_code, fiscal_year, fiscal_period):
self.fiscal_year = int(fiscal_year)
self.sortable_columns = ["publication_date", "certification_date"]
self.default_sort_column = "publication_date"
self.validate_fiscal_period({"fiscal_period": int(fiscal_period)})
record = list(
SubmissionAttributes.objects.filter(
toptier_code=toptier_code,
reporting_fiscal_year=fiscal_year,
reporting_fiscal_period=fiscal_period,
submission_window_id__submission_reveal_date__lte=now(),
).values_list("history", flat=True)
)
if len(record) == 0:
raise NoDataFoundException("No Agency Account Submission History records match the provided parameters")
# Convoluted list comprehension and sort to
# A) construct the dict list
# B) add secondary sort key and handle nulls in `certification_date` for sorting
results = sorted(
[
{"publication_date": row["published_date"], "certification_date": row["certified_date"]}
for row in record[0]
],
key=lambda x: x["publication_date"]
if self.pagination.sort_key == "publication_date"
else (x["certification_date"] or "", x["publication_date"]),
reverse=self.pagination.sort_order == "desc",
)
page_metadata = get_pagination_metadata(len(results), self.pagination.limit, self.pagination.page)
results = results[self.pagination.lower_limit : self.pagination.upper_limit]
return Response(
{"page_metadata": page_metadata, "results": results, "messages": self.standard_response_messages}
)
def get(self, request, format=None):
sortable_columns = [
"agency_id",
"agency_name",
"active_fy",
"active_fq",
"outlay_amount",
"obligated_amount",
"budget_authority_amount",
"current_total_budget_authority_amount",
"percentage_of_total_budget_authority",
]
sort = request.query_params.get("sort", "agency_name")
order = request.query_params.get("order", "asc")
response = {"results": []}
if sort not in sortable_columns:
raise InvalidParameterException(
"The sort value provided is not a valid option. "
"Please choose from the following: " + str(sortable_columns)
)
if order not in ["asc", "desc"]:
raise InvalidParameterException(
"The order value provided is not a valid option. Please choose from the following: ['asc', 'desc']"
)
# get agency queryset, distinct toptier id to avoid duplicates, take first ordered agency id for consistency
agency_queryset = Agency.objects.order_by("toptier_agency_id", "id").distinct("toptier_agency_id")
for agency in agency_queryset:
toptier_agency = agency.toptier_agency
# get corresponding submissions through cgac code
queryset = SubmissionAttributes.objects.all()
queryset = queryset.filter(
toptier_code=toptier_agency.toptier_code, submission_window__submission_reveal_date__lte=now()
)
# get the most up to date fy and quarter
queryset = queryset.order_by("-reporting_fiscal_year", "-reporting_fiscal_quarter")
queryset = queryset.annotate(
fiscal_year=F("reporting_fiscal_year"), fiscal_quarter=F("reporting_fiscal_quarter")
)
submission = queryset.first()
if submission is None:
continue
active_fiscal_year = submission.reporting_fiscal_year
active_fiscal_quarter = submission.fiscal_quarter
active_fiscal_period = submission.reporting_fiscal_period
queryset = AppropriationAccountBalances.objects.filter(submission__is_final_balances_for_fy=True)
# get the incoming agency's toptier agency, because that's what we'll
# need to filter on
# (used filter() instead of get() b/c we likely don't want to raise an
# error on a bad agency id)
aggregate_dict = queryset.filter(
submission__reporting_fiscal_year=active_fiscal_year,
submission__reporting_fiscal_quarter=active_fiscal_quarter,
treasury_account_identifier__funding_toptier_agency=toptier_agency,
).aggregate(
budget_authority_amount=Coalesce(Sum("total_budgetary_resources_amount_cpe"), 0),
obligated_amount=Coalesce(Sum("obligations_incurred_total_by_tas_cpe"), 0),
outlay_amount=Coalesce(Sum("gross_outlay_amount_by_tas_cpe"), 0),
)
abbreviation = ""
if toptier_agency.abbreviation is not None:
abbreviation = toptier_agency.abbreviation
cj = toptier_agency.justification if toptier_agency.justification else None
# craft response
total_obligated = get_total_obligations_incurred(active_fiscal_year, active_fiscal_period)
response["results"].append(
{
"agency_id": agency.id,
"toptier_code": toptier_agency.toptier_code,
"abbreviation": abbreviation,
"agency_name": toptier_agency.name,
"congressional_justification_url": cj,
"active_fy": str(active_fiscal_year),
"active_fq": str(active_fiscal_quarter),
"outlay_amount": float(aggregate_dict["outlay_amount"]),
"obligated_amount": float(aggregate_dict["obligated_amount"]),
"budget_authority_amount": float(aggregate_dict["budget_authority_amount"]),
"current_total_budget_authority_amount": float(
get_total_budgetary_resources(active_fiscal_year, active_fiscal_period)
),
"percentage_of_total_budget_authority": (
(float(aggregate_dict["budget_authority_amount"]) / float(total_obligated))
if total_obligated > 0
else None
),
}
)
response["results"] = sorted(response["results"], key=lambda k: k[sort], reverse=(order == "desc"))
return Response(response)
def get_abandoned_heartbeat_cutoff():
return now() - timedelta(minutes=ABANDONED_LOCK_MINUTES)
def get_last_closed_submission_date(is_quarter: bool) -> Optional[dict]:
return (DABSSubmissionWindowSchedule.objects.filter(
is_quarter=is_quarter, submission_reveal_date__lte=now()).order_by(
"-submission_fiscal_year", "-submission_fiscal_quarter",
"-submission_fiscal_month").values().first())
def get(self, request, pk, format=None):
"""
Return the view's queryset.
"""
response = {"results": {}}
# get id from url
agency_id = int(pk)
# get agency's cgac code and use that code to get the agency's submission
agency = Agency.objects.filter(id=agency_id).first()
if agency is None:
return Response(response)
toptier_agency = agency.toptier_agency
# get corresponding submissions through cgac code
queryset = SubmissionAttributes.objects.all()
queryset = queryset.filter(
toptier_code=toptier_agency.toptier_code, submission_window__submission_reveal_date__lte=now()
)
# get the most up to date fy and quarter
queryset = queryset.order_by("-reporting_fiscal_year", "-reporting_fiscal_quarter")
queryset = queryset.annotate(
fiscal_year=F("reporting_fiscal_year"), fiscal_quarter=F("reporting_fiscal_quarter")
)
submission = queryset.first()
if submission is None:
return Response(response)
active_fiscal_year = submission.reporting_fiscal_year
active_fiscal_quarter = submission.fiscal_quarter
active_fiscal_period = submission.reporting_fiscal_period
queryset = AppropriationAccountBalances.objects.filter(submission__is_final_balances_for_fy=True)
# get the incoming agency's toptier agency, because that's what we'll
# need to filter on
# (used filter() instead of get() b/c we likely don't want to raise an
# error on a bad agency id)
aggregate_dict = queryset.filter(
submission__reporting_fiscal_year=active_fiscal_year,
submission__reporting_fiscal_quarter=active_fiscal_quarter,
treasury_account_identifier__funding_toptier_agency=toptier_agency,
).aggregate(
budget_authority_amount=Sum("total_budgetary_resources_amount_cpe"),
obligated_amount=Sum("obligations_incurred_total_by_tas_cpe"),
outlay_amount=Sum("gross_outlay_amount_by_tas_cpe"),
)
cj = toptier_agency.justification if toptier_agency.justification else None
# craft response
response["results"] = {
"agency_name": toptier_agency.name,
"active_fy": str(active_fiscal_year),
"active_fq": str(active_fiscal_quarter),
"outlay_amount": str(aggregate_dict["outlay_amount"]),
"obligated_amount": str(aggregate_dict["obligated_amount"]),
"budget_authority_amount": str(aggregate_dict["budget_authority_amount"]),
"current_total_budget_authority_amount": str(
get_total_budgetary_resources(active_fiscal_year, active_fiscal_period)
),
"mission": toptier_agency.mission,
"website": toptier_agency.website,
"icon_filename": toptier_agency.icon_filename,
"congressional_justification_url": cj,
}
return Response(response)