def load_file_b(submission_attributes, prg_act_obj_cls_data, db_cursor):
"""
Process and load file B broker data (aka TAS balances by program
activity and object class).
"""
reverse = re.compile(r'(_(cpe|fyb)$)|^transaction_obligated_amount$')
test_counter = 0
for row in prg_act_obj_cls_data:
test_counter += 1
account_balances = None
try:
# Check and see if there is an entry for this TAS
treasury_account = get_treasury_appropriation_account_tas_lookup(
row.get('tas_id'), db_cursor)
if treasury_account is None:
raise Exception(
'Could not find appropriation account for TAS: ' +
row['tas'])
except:
continue
# get the corresponding account balances row (aka "File A" record)
account_balances = AppropriationAccountBalances.objects.get(
treasury_account_identifier=treasury_account,
submission_id=submission_attributes.submission_id)
financial_by_prg_act_obj_cls = FinancialAccountsByProgramActivityObjectClass(
)
value_map = {
'submission':
submission_attributes,
'reporting_period_start':
submission_attributes.reporting_period_start,
'reporting_period_end':
submission_attributes.reporting_period_end,
'treasury_account':
treasury_account,
'appropriation_account_balances':
account_balances,
'object_class':
get_or_create_object_class(row['object_class'],
row['by_direct_reimbursable_fun'],
logger),
'program_activity':
get_or_create_program_activity(row, submission_attributes)
}
load_data_into_model(financial_by_prg_act_obj_cls,
row,
value_map=value_map,
save=True,
reverse=reverse)
# Insert File B quarterly numbers for this submission
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers(
submission_attributes.submission_id)
FinancialAccountsByProgramActivityObjectClass.populate_final_of_fy()
def load_file_b(submission_attributes, prg_act_obj_cls_data, db_cursor):
"""
Process and load file B broker data (aka TAS balances by program activity and object class).
"""
reverse = re.compile(r'(_(cpe|fyb)$)|^transaction_obligated_amount$')
# dictionary to capture TAS that were skipped and some metadata
# tas = top-level key
# count = number of rows skipped
# rows = row numbers skipped, corresponding to the original row numbers in the file that was submitted
skipped_tas = {}
test_counter = 0
for row in prg_act_obj_cls_data:
test_counter += 1
account_balances = None
try:
# Check and see if there is an entry for this TAS
treasury_account, tas_rendering_label = get_treasury_appropriation_account_tas_lookup(row.get('tas_id'),
db_cursor)
if treasury_account is None:
update_skipped_tas(row, tas_rendering_label, skipped_tas)
continue
except Exception: # TODO: What is this trying to catch, actually?
continue
# get the corresponding account balances row (aka "File A" record)
account_balances = AppropriationAccountBalances.objects.get(
treasury_account_identifier=treasury_account,
submission_id=submission_attributes.submission_id
)
financial_by_prg_act_obj_cls = FinancialAccountsByProgramActivityObjectClass()
value_map = {
'submission': submission_attributes,
'reporting_period_start': submission_attributes.reporting_period_start,
'reporting_period_end': submission_attributes.reporting_period_end,
'treasury_account': treasury_account,
'appropriation_account_balances': account_balances,
'object_class': get_or_create_object_class(row['object_class'], row['by_direct_reimbursable_fun'], logger),
'program_activity': get_or_create_program_activity(row, submission_attributes)
}
load_data_into_model(financial_by_prg_act_obj_cls, row, value_map=value_map, save=True, reverse=reverse)
# Insert File B quarterly numbers for this submission
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers(submission_attributes.submission_id)
FinancialAccountsByProgramActivityObjectClass.populate_final_of_fy()
for key in skipped_tas:
logger.info('Skipped %d rows due to missing TAS: %s', skipped_tas[key]['count'], key)
total_tas_skipped = 0
for key in skipped_tas:
total_tas_skipped += skipped_tas[key]['count']
logger.info('Skipped a total of {} TAS rows for File B'.format(total_tas_skipped))
def get_submission_attributes(broker_submission_id, submission_data):
"""
For a specified broker submission, return the existing corresponding usaspending submission record or create and
return a new one.
"""
# check if we already have an entry for this broker submission id; if not, create one
submission_attributes, created = SubmissionAttributes.\
objects.get_or_create(broker_submission_id=broker_submission_id)
if created:
# this is the first time we're loading this broker submission
logger.info('Creating broker submission id {}'.format(broker_submission_id))
else:
# we've already loaded this broker submission, so delete it before reloading if there's another submission that
# references this one as a "previous submission" do not proceed.
# TODO: now that we're chaining submisisons together, get clarification on what should happen when a submission
# in the middle of the chain is deleted
TasProgramActivityObjectClassQuarterly.refresh_downstream_quarterly_numbers(submission_attributes.submission_id)
logger.info('Broker submission id {} already exists. It will be deleted.'.format(broker_submission_id))
call_command('rm_submission', broker_submission_id)
logger.info("Merging CGAC and FREC columns")
submission_data["cgac_code"] = submission_data["cgac_code"]\
if submission_data["cgac_code"] else submission_data["frec_code"]
# Find the previous submission for this CGAC and fiscal year (if there is one)
previous_submission = get_previous_submission(
submission_data['cgac_code'],
submission_data['reporting_fiscal_year'],
submission_data['reporting_fiscal_period'])
# Update and save submission attributes
field_map = {
'reporting_period_start': 'reporting_start_date',
'reporting_period_end': 'reporting_end_date',
'quarter_format_flag': 'is_quarter_format',
}
# Create our value map - specific data to load
value_map = {
'broker_submission_id': broker_submission_id,
'reporting_fiscal_quarter': get_fiscal_quarter(submission_data['reporting_fiscal_period']),
'previous_submission': None if previous_submission is None else previous_submission,
# pull in broker's last update date to use as certified date
'certified_date': submission_data['updated_at'].date() if type(
submission_data['updated_at']) == datetime else None,
}
return load_data_into_model(
submission_attributes, submission_data,
field_map=field_map, value_map=value_map, save=True)
def forwards_or_backwards_func(apps, schema_editor):
"""
Because -1 * -1 == 1, this migration works the same both directions!
"""
db_alias = schema_editor.connection.alias
cls = apps.get_model('financial_activities',
'FinancialAccountsByProgramActivityObjectClass')
# invert values of all fields ending in _cpe or _fyb
updates = {
field_name: F(field_name) * -1
for field_name in dir(cls)
if (field_name.endswith('_cpe') or field_name.endswith('_fyb'))
}
cls.objects.using(db_alias).update(**updates)
# re-calculate all TasProgramActivityObjectClassQuarterly numbers
# to ensure that they match the signs of the underlying
# FinancialAccountsByProgramActivityObjectClass data
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers()
def test_insert_quarterly_numbers(tas_oc_pa_data):
"""
Test the function that inserts quarterly tas/obj class/pgm activity
numbers.
"""
sub1 = SubmissionAttributes.objects.get(
previous_submission_id__isnull=True)
sub2 = SubmissionAttributes.objects.get(
previous_submission_id__isnull=False)
# we start with an empty quarterly table
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 0
# load quarterly records and check results
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers()
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 2
quarter_sub2 = quarters.get(submission=sub2)
quarter_sub1 = quarters.get(submission=sub1)
assert quarter_sub2.gross_outlays_delivered_orders_paid_total_cpe == Decimal(
'10.50')
assert quarter_sub2.obligations_incurred_by_program_object_class_cpe == Decimal(
'-5.00')
assert quarter_sub2.obligations_undelivered_orders_unpaid_total_fyb == Decimal(
'0.00')
assert quarter_sub2.ussgl490800_authority_outlayed_not_yet_disbursed_fyb == Decimal(
'60.00')
# loading again drops and recreates quarterly data
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers()
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 2
assert quarters.get(submission=sub1).id != quarter_sub1.id
assert quarters.get(submission=sub2).id != quarter_sub2.id
# load quarterly data for submission 1 only
quarter_sub2 = quarters.get(submission=sub2)
quarter_sub1 = quarters.get(submission=sub1)
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers(
sub1.submission_id)
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 2
# submission 1 record should be updated
assert quarters.get(submission=sub1).id != quarter_sub1.id
# submission 2 record should not be updated
assert quarters.get(submission=sub2).id == quarter_sub2.id
def test_insert_quarterly_numbers(tas_oc_pa_data):
"""
Test the function that inserts quarterly tas/obj class/pgm activity
numbers.
"""
sub1 = SubmissionAttributes.objects.get(previous_submission_id__isnull=True)
sub2 = SubmissionAttributes.objects.get(previous_submission_id__isnull=False)
# we start with an empty quarterly table
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 0
# load quarterly records and check results
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers()
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 2
quarter_sub2 = quarters.get(submission=sub2)
quarter_sub1 = quarters.get(submission=sub1)
assert quarter_sub2.gross_outlays_delivered_orders_paid_total_cpe == Decimal('10.50')
assert quarter_sub2.obligations_incurred_by_program_object_class_cpe == Decimal('-5.00')
assert quarter_sub2.obligations_undelivered_orders_unpaid_total_fyb == Decimal('0.00')
assert quarter_sub2.ussgl490800_authority_outlayed_not_yet_disbursed_fyb == Decimal('60.00')
# loading again drops and recreates quarterly data
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers()
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 2
assert quarters.get(submission=sub1).id != quarter_sub1.id
assert quarters.get(submission=sub2).id != quarter_sub2.id
# load quarterly data for submission 1 only
quarter_sub2 = quarters.get(submission=sub2)
quarter_sub1 = quarters.get(submission=sub1)
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers(sub1.submission_id)
quarters = TasProgramActivityObjectClassQuarterly.objects.all()
assert quarters.count() == 2
# submission 1 record should be updated
assert quarters.get(submission=sub1).id != quarter_sub1.id
# submission 2 record should not be updated
assert quarters.get(submission=sub2).id == quarter_sub2.id
def get_submission_attributes(broker_submission_id, submission_data):
"""
For a specified broker submission, return the existing corresponding usaspending submission record or create and
return a new one.
"""
# check if we already have an entry for this broker submission id; if not, create one
submission_attributes, created = SubmissionAttributes.objects.get_or_create(
broker_submission_id=broker_submission_id)
if created:
# this is the first time we're loading this broker submission
logger.info(
"Creating broker submission id {}".format(broker_submission_id))
else:
# we've already loaded this broker submission, so delete it before reloading if there's another submission that
# references this one as a "previous submission" do not proceed.
# TODO: now that we're chaining submissions together, get clarification on what should happen when a submission
# in the middle of the chain is deleted
TasProgramActivityObjectClassQuarterly.refresh_downstream_quarterly_numbers(
submission_attributes.submission_id)
logger.info(
"Broker submission id {} already exists. It will be deleted.".
format(broker_submission_id))
call_command("rm_submission", broker_submission_id)
logger.info("Merging CGAC and FREC columns")
submission_data["toptier_code"] = (submission_data["cgac_code"]
if submission_data["cgac_code"] else
submission_data["frec_code"])
# Find the previous submission for this CGAC and fiscal year (if there is one)
previous_submission = get_previous_submission(
submission_data["toptier_code"],
submission_data["reporting_fiscal_year"],
submission_data["reporting_fiscal_period"],
)
# if another submission lists the previous submission as its previous submission, set to null and update later
potential_conflicts = []
if previous_submission:
potential_conflicts = SubmissionAttributes.objects.filter(
previous_submission=previous_submission)
if potential_conflicts:
logger.info(
"==== ATTENTION! Previous Submission ID Conflict Detected ===="
)
for conflict in potential_conflicts:
logger.info(
"Temporarily setting {}'s Previous Submission ID from {} to null"
.format(conflict, previous_submission.submission_id))
conflict.previous_submission = None
conflict.save()
# Update and save submission attributes
field_map = {
"reporting_period_start": "reporting_start_date",
"reporting_period_end": "reporting_end_date",
"quarter_format_flag": "is_quarter_format",
}
# Create our value map - specific data to load
value_map = {
"broker_submission_id":
broker_submission_id,
"reporting_fiscal_quarter":
get_fiscal_quarter(submission_data["reporting_fiscal_period"]),
"previous_submission":
previous_submission,
# pull in broker's last update date to use as certified date
"certified_date":
submission_data["updated_at"].date()
if type(submission_data["updated_at"]) == datetime else None,
}
new_submission = load_data_into_model(submission_attributes,
submission_data,
field_map=field_map,
value_map=value_map,
save=True)
# If there were any submissions which were temporarily modified, reassign the submission
for conflict in potential_conflicts:
remapped_previous = get_previous_submission(
conflict.toptier_code, conflict.reporting_fiscal_year,
conflict.reporting_fiscal_period)
logger.info(
"New Previous Submission ID for Submission ID {} permanently mapped to {} "
.format(conflict.submission_id, remapped_previous))
conflict.previous_submission = remapped_previous
conflict.save()
return new_submission
def insert_quarterly_records(apps, schema_editor):
"""Insert quarterly File B totals for existing submissions."""
TasProgramActivityObjectClassQuarterly.insert_quarterly_numbers()
