def test_generate_file_e_sql(database, monkeypatch):
""" test_generate_file_e_sql should provide the query representing E file data related to a submission """
# Setup - create submission, awards, subawards
sess = database.session
sub1 = SubmissionFactory(submission_id=1)
sub2 = SubmissionFactory(submission_id=2)
d1_show = AwardProcurementFactory(submission_id=sub1.submission_id, awardee_or_recipient_uei='00000000000e')
d2_show = AwardFinancialAssistanceFactory(submission_id=sub1.submission_id, awardee_or_recipient_uei='11111111111e')
d1_hide = AwardProcurementFactory(submission_id=sub2.submission_id, awardee_or_recipient_uei='22222222222e')
d2_hide = AwardFinancialAssistanceFactory(submission_id=sub2.submission_id, awardee_or_recipient_uei='33333333333e')
recipient_show = [SAMRecipientFactory(uei=(str(i) * 11) + 'e') for i in range(0, 2)]
recipient_hide = [SAMRecipientFactory(uei=(str(i) * 11) + 'e') for i in range(2, 4)]
recipient_s = recipient_show + recipient_hide
sess.add_all([sub1, sub2, d1_hide, d1_show, d2_hide, d2_show] + recipient_s)
sess.commit()
# Gather the sql
file_e_query = fileE_F.generate_file_e_sql(sub1.submission_id)
# Get the records
file_e_records = sess.execute(file_e_query)
file_e_cols = file_e_records.keys()
file_e_value_sets = file_e_records.fetchall()
file_e_results = [OrderedDict(list(zip(file_e_cols, file_e_value_set))) for file_e_value_set in file_e_value_sets]
# Expected Results
expected_file_e_results = [replicate_file_e_results(recipient) for recipient in recipient_show]
assert file_e_results == expected_file_e_results
def test_failure(database):
""" Test failure for each unique combination of PIID/ParentAwardId in File C, the sum of each
TransactionObligatedAmount should match (but with opposite signs) the sum of the FederalActionObligation
reported in D1. This rule does not apply if the ATA field is populated and is different from the Agency ID. """
# Create a 12 character random parent_award_id
paid_1 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
paid_2 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
# Basic sum, row 3 is ignored in this sum because it doesn't have a paid
af_1_row_1 = AwardFinancialFactory(transaction_obligated_amou=1100, piid=piid, parent_award_id=paid_1,
allocation_transfer_agency=None)
af_1_row_2 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid, parent_award_id=paid_1,
allocation_transfer_agency=None)
af_1_row_3 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid, parent_award_id=None,
allocation_transfer_agency=None)
# Same ATA/AID or no ATA sum
af_2_row_1 = AwardFinancialFactory(transaction_obligated_amou=1111, piid=piid, parent_award_id=paid_2,
allocation_transfer_agency=None)
af_2_row_2 = AwardFinancialFactory(transaction_obligated_amou=1111, piid=piid, parent_award_id=paid_2,
allocation_transfer_agency="good", agency_identifier="good")
# Sum of these values doesn't add up (ignoring third one because it has a different paid)
ap_1_row_1 = AwardProcurementFactory(parent_award_id=paid_1, piid=piid, federal_action_obligation=-1100)
ap_1_row_2 = AwardProcurementFactory(parent_award_id=paid_1, piid=piid, federal_action_obligation=-10)
ap_1_row_3 = AwardProcurementFactory(parent_award_id="1234", piid=piid, federal_action_obligation=-1)
# Sum of the two above should be both of them, not just one
ap_2 = AwardProcurementFactory(parent_award_id=paid_2, piid=piid, federal_action_obligation=-1111)
errors = number_of_errors(_FILE, database, models=[af_1_row_1, af_1_row_2, af_1_row_3, af_2_row_1, af_2_row_2,
ap_1_row_1, ap_1_row_2, ap_1_row_3, ap_2])
assert errors == 2
def test_generate_e_file_query(monkeypatch, mock_broker_config_paths,
database):
"""Verify that generate_e_file makes an appropriate query (matching both
D1 and D2 entries)"""
# Generate several file D1 entries, largely with the same submission_id,
# and with two overlapping DUNS. Generate several D2 entries with the same
# submission_id as well
model = AwardProcurementFactory()
aps = [AwardProcurementFactory(submission_id=model.submission_id)
for i in range(4)]
afas = [AwardFinancialAssistanceFactory(submission_id=model.submission_id)
for i in range(5)]
same_duns = AwardProcurementFactory(
submission_id=model.submission_id,
awardee_or_recipient_uniqu=model.awardee_or_recipient_uniqu)
unrelated = AwardProcurementFactory(submission_id=model.submission_id + 1)
database.session.add_all(aps + afas + [model, same_duns, unrelated])
monkeypatch.setattr(jobQueue.fileE, 'retrieveRows', Mock(return_value=[]))
# Mock out the interface holder class; rather nasty, as we want to _keep_
# the database session handler
interface_class = Mock()
interface_class.return_value.jobDb.session = database.session
jobQueue.generate_e_file(
model.submission_id, 1, interface_class, 'uniq', 'uniq',
is_local=True)
# [0][0] gives us the first, non-keyword args
call_args = jobQueue.fileE.retrieveRows.call_args[0][0]
expected = [ap.awardee_or_recipient_uniqu for ap in aps]
expected.append(model.awardee_or_recipient_uniqu)
expected.extend(afa.awardee_or_recipient_uniqu for afa in afas)
assert list(sorted(call_args)) == list(sorted(expected))
def test_failure(database):
""" Test failure for each unique PIID in File C, the sum of each TransactionObligatedAmount should match (but with
opposite signs) the sum of the FederalActionObligation reported in D1. This rule does not apply if the ATA field
is populated and is different from the Agency ID. Ignore rows that contain a PAID. """
# Create a 12 character random piid
piid_1 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid_2 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid_3 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
# No ATA, not matching (off by 1)
af_1_row_1 = AwardFinancialFactory(transaction_obligated_amou=1100, piid=piid_1, parent_award_id='',
allocation_transfer_agency=None)
af_1_row_2 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid_1, parent_award_id=None,
allocation_transfer_agency=None)
# No ATA, not matching, one record, no paid
af_2 = AwardFinancialFactory(transaction_obligated_amou=9999, piid=piid_2, parent_award_id=None,
allocation_transfer_agency=None)
# Matching ATA, should not be ignored
af_3 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid_3, parent_award_id=None,
allocation_transfer_agency="123", agency_identifier="123")
# Award Procurement portion of checks
# Sum of all these would be sum of piid_1 af if one wasn't ignored
ap_1_row_1 = AwardProcurementFactory(piid=piid_1, parent_award_id=None, federal_action_obligation=-1100)
ap_1_row_2 = AwardProcurementFactory(piid=piid_1, parent_award_id=None, federal_action_obligation=-10)
# second piid that simply doesn't match
ap_2 = AwardProcurementFactory(piid=piid_2, parent_award_id=None, federal_action_obligation=-1111)
# third piid that should not be ignored because ATA is present but matches
ap_3 = AwardProcurementFactory(piid=piid_3, parent_award_id=None, federal_action_obligation=0)
errors = number_of_errors(_FILE, database, models=[af_1_row_1, af_1_row_2, af_2, af_3, ap_1_row_1, ap_1_row_2, ap_2,
ap_3])
assert errors == 3
def test_generate_f_rows_naics_desc(database, monkeypatch):
"""The NAICS description should be retireved from an AwardProcurement"""
sub = SubmissionFactory()
database.session.add(sub)
database.session.commit()
award = AwardProcurementFactory(submission_id=sub.submission_id,
awarding_sub_tier_agency_c='1234')
other_aps = [
AwardProcurementFactory(submission_id=award.submission_id,
awarding_sub_tier_agency_c='1234')
for _ in range(3)
]
proc = FSRSProcurementFactory(
contract_number=award.piid,
idv_reference_number=award.parent_award_id,
subawards=[FSRSSubcontractFactory(naics=award.naics)],
contracting_office_aid='1234')
database.session.add_all([award, proc] + other_aps)
database.session.commit()
actual = {
result['NAICS_Description']
for result in fileF.generate_f_rows(award.submission_id)
}
assert actual == {award.naics_description}
def test_success(database):
""" Unique PIID, ParentAwardId from file D1 exists in file C during the same reporting period, except D1 records
with zero FederalActionObligation.
"""
ap = AwardProcurementFactory(piid='some_pIId',
parent_award_id='some_PArent_award_id',
federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_AWard_id')
assert number_of_errors(_FILE, database, models=[ap, af]) == 0
# Rule shouldn't be checked if federal_action_obligation is null
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_PArent_award_id',
federal_action_obligation=None)
assert number_of_errors(_FILE, database, models=[ap]) == 0
# Checks null = null
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id=None,
federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_pIId', parent_award_id=None)
assert number_of_errors(_FILE, database, models=[ap, af]) == 0
def test_success(database):
""" Test that a four digit object class with no flag is a success, and a three digit object class with
a flag is a success. Ignores rows with parent_award_id from AwardFinancialFactory and doesn't care about
parent_award_id in AwardProcurementFactory """
# Create a 12 character random piid
piid = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid_two = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid_three = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
first_piid_row_one = AwardFinancialFactory(transaction_obligated_amou=1100, piid=piid, parent_award_id='',
allocation_transfer_agency=None)
first_piid_row_two = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid, parent_award_id=None,
allocation_transfer_agency=None)
first_piid_row_three = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid, parent_award_id="Test",
allocation_transfer_agency=None)
# And add a row for a different piid
second_piid_row_one = AwardFinancialFactory(transaction_obligated_amou=9999, piid=piid_two, parent_award_id=None,
allocation_transfer_agency=None)
third_piid_row_one = AwardFinancialFactory(transaction_obligated_amou=8888, piid=piid_three, parent_award_id=None,
allocation_transfer_agency=123)
third_piid_row_two = AwardFinancialFactory(transaction_obligated_amou=8888, piid=piid_three, parent_award_id=None,
allocation_transfer_agency=None)
first_ap_row = AwardProcurementFactory(piid=piid, parent_award_id=None, federal_action_obligation=-1100)
second_ap_row = AwardProcurementFactory(piid=piid, parent_award_id=None, federal_action_obligation=-10)
third_ap_row = AwardProcurementFactory(piid=piid, parent_award_id=None, federal_action_obligation=-1)
second_piid_ap_row = AwardProcurementFactory(piid=piid_two, parent_award_id="1234", federal_action_obligation=-9999)
third_piid_ap_row = AwardProcurementFactory(piid=piid_three, parent_award_id=None, federal_action_obligation=-9999)
errors = number_of_errors(_FILE, database, models=[first_piid_row_one, first_piid_row_two, first_piid_row_three,
second_piid_row_one, third_piid_row_one, first_ap_row, second_ap_row, third_ap_row,
second_piid_ap_row, third_piid_ap_row, third_piid_row_two])
assert errors == 0
def test_success(database):
""" Unique PIID, ParentAwardId from file C exists in file D1 during the same reporting period. """
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
ap_1 = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
ap_2 = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap_1, ap_2]) == 0
af = AwardFinancialFactory(piid=None,
parent_award_id='some_parent_award_id')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid='some_piid', parent_award_id=None)
ap = AwardProcurementFactory(piid='some_piid', parent_award_id=None)
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
def test_failure(database):
""" Test failure for unique PIID, or combination of PIID/ParentAwardId, from file C exists in file D1 during the
same reporting period. Do not process if allocation transfer agency is not null and does not match agency ID.
"""
# Perform when there's a transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_pIId',
parent_award_id='some_pARent_award_id',
allocation_transfer_agency=None,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_parent_AWard_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
# Perform when there's an ata in the field and it matches the aid
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_AWard_id',
allocation_transfer_agency='bad',
agency_identifier='bad',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_pIId',
parent_award_id='soME_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
af = AwardFinancialFactory(piid='some_piid',
parent_award_id=None,
allocation_transfer_agency='bad',
agency_identifier='bad',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_oTHer_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
def test_failure(database):
""" Unique PIID, ParentAwardId from file C doesn't exist in file D1 during the same reporting period. """
# Perform when there's a transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
allocation_transfer_agency=None,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
# Perform when there's a transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
allocation_transfer_agency='bad',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
af = AwardFinancialFactory(piid='some_piid',
parent_award_id=None,
allocation_transfer_agency='bad',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
def test_failure(database):
""" Test that a three digit object class with no flag is an error. Only finds rows with matching piid AND
parent_award_id from AwardFinancialFactory and doesn't care about rows with null parent_award_id in
AwardFinancialFactory """
# Create a 12 character random parent_award_id
parent_award_id = ''.join(
choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
parent_award_id_two = ''.join(
choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
first_parent_award_id_row_one = AwardFinancialFactory(
transaction_obligated_amou=1100,
piid="1234",
parent_award_id=parent_award_id,
allocation_transfer_agency=None)
first_parent_award_id_row_two = AwardFinancialFactory(
transaction_obligated_amou=11,
piid="1234",
parent_award_id=parent_award_id,
allocation_transfer_agency=None)
first_parent_award_id_row_three = AwardFinancialFactory(
transaction_obligated_amou=11,
piid="1234",
parent_award_id=None,
allocation_transfer_agency=None)
# And add a row that is wrong
second_parent_award_id_row_one = AwardFinancialFactory(
transaction_obligated_amou=9999,
piid="1234",
parent_award_id=parent_award_id_two,
allocation_transfer_agency=None)
first_ap_row = AwardProcurementFactory(parent_award_id=parent_award_id,
piid="1234",
federal_action_obligation=-1100)
second_ap_row = AwardProcurementFactory(parent_award_id=parent_award_id,
piid="1234",
federal_action_obligation=-10)
third_ap_row = AwardProcurementFactory(parent_award_id="1234",
piid="1234",
federal_action_obligation=-10)
other_parent_award_id_ap_row = AwardProcurementFactory(
parent_award_id=parent_award_id_two,
piid="1234",
federal_action_obligation=-1111)
errors = number_of_errors(_FILE,
database,
models=[
first_parent_award_id_row_one,
first_parent_award_id_row_two,
first_parent_award_id_row_three,
second_parent_award_id_row_one, first_ap_row,
second_ap_row, third_ap_row,
other_parent_award_id_ap_row
])
assert errors == 2
def test_generate_e_file_query(monkeypatch, mock_broker_config_paths,
database):
""" Verify that generate_e_file makes an appropriate query (matching both D1 and D2 entries) """
# Generate several file D1 entries, largely with the same submission_id, and with two overlapping DUNS. Generate
# several D2 entries with the same submission_id as well
sess = database.session
sub = SubmissionFactory()
sub_2 = SubmissionFactory()
sess.add_all([sub, sub_2])
sess.commit()
file_path = str(mock_broker_config_paths['broker_files'].join('e_test1'))
job = JobFactory(job_status_id=JOB_STATUS_DICT['running'],
job_type_id=JOB_TYPE_DICT['file_upload'],
file_type_id=FILE_TYPE_DICT['executive_compensation'],
filename=file_path,
original_filename='e_test1',
submission_id=sub.submission_id)
database.session.add(job)
database.session.commit()
model = AwardProcurementFactory(submission_id=sub.submission_id)
aps = [
AwardProcurementFactory(submission_id=sub.submission_id)
for _ in range(4)
]
afas = [
AwardFinancialAssistanceFactory(submission_id=sub.submission_id)
for _ in range(5)
]
same_duns = AwardProcurementFactory(
submission_id=sub.submission_id,
awardee_or_recipient_uniqu=model.awardee_or_recipient_uniqu)
unrelated = AwardProcurementFactory(submission_id=sub_2.submission_id)
sess.add_all(aps + afas + [model, same_duns, unrelated])
sess.commit()
monkeypatch.setattr(file_generation_manager, 'mark_job_status', Mock())
monkeypatch.setattr(file_generation_manager.fileE, 'retrieve_rows',
Mock(return_value=[]))
file_gen_manager = FileGenerationManager(job, None, None,
CONFIG_BROKER['local'])
file_gen_manager.generate_e_file()
# [0][0] gives us the first, non-keyword args
call_args = file_generation_manager.fileE.retrieve_rows.call_args[0][0]
expected = [ap.awardee_or_recipient_uniqu for ap in aps]
expected.append(model.awardee_or_recipient_uniqu)
expected.extend(afa.awardee_or_recipient_uniqu for afa in afas)
assert list(sorted(call_args)) == list(sorted(expected))
def test_success(database):
""" Unique PIID, or combination of PIID/ParentAwardId, from file C exists in file D1 during the same reporting
period. Do not process if allocation transfer agency is not null and does not match agency ID """
af = AwardFinancialFactory(piid='some_piid', parent_award_id='some_parent_award_id',
allocation_transfer_agency=None, transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid', parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid='some_piid', parent_award_id='some_parent_award_id',
allocation_transfer_agency=None, transaction_obligated_amou='12345')
ap_1 = AwardProcurementFactory(piid='some_piid', parent_award_id='some_parent_award_id')
ap_2 = AwardProcurementFactory(piid='some_piid', parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap_1, ap_2]) == 0
af = AwardFinancialFactory(piid=None, parent_award_id='some_parent_award_id',
allocation_transfer_agency=None, transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid', parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid='some_piid', parent_award_id=None, allocation_transfer_agency=None,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid', parent_award_id=None)
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
# Not perform when no transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid', parent_award_id='some_parent_award_id',
allocation_transfer_agency=None, transaction_obligated_amou=None)
ap = AwardProcurementFactory(piid='some_other_piid', parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
# Not perform when no transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid', parent_award_id='some_parent_award_id',
allocation_transfer_agency=None, transaction_obligated_amou=None)
ap = AwardProcurementFactory(piid='some_piid', parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid=None, parent_award_id=None, allocation_transfer_agency=None,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid', parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
# Not perform when allocation transfer agency is filled in but is different from agency id
af = AwardFinancialFactory(piid='some_piid', parent_award_id='some_parent_award_id',
allocation_transfer_agency='good', agency_identifier='red',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid', parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
def test_failure(database):
""" Test that a three digit object class with no flag is an error"""
# Create a 12 character random piid
piid = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for i in range(12))
piid_two = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for i in range(12))
first_piid_row_one = AwardFinancialFactory(transaction_obligated_amou = 1100, piid = piid, allocation_transfer_agency = None)
first_piid_row_two = AwardFinancialFactory(transaction_obligated_amou = 11, piid = piid, allocation_transfer_agency = None)
# And add a row that shouldn't be included
second_piid_row_one = AwardFinancialFactory(transaction_obligated_amou = 9999, piid = piid_two, allocation_transfer_agency = None)
first_ap_row = AwardProcurementFactory(piid = piid, federal_action_obligation = -1100)
second_ap_row = AwardProcurementFactory(piid = piid, federal_action_obligation = -10)
other_piid_ap_row = AwardProcurementFactory(piid = piid_two, federal_action_obligation = -1111)
errors = number_of_errors(_FILE, database, models=[first_piid_row_one, first_piid_row_two, second_piid_row_one, first_ap_row, second_ap_row, other_piid_ap_row])
assert errors == 2
def test_success(database):
""" Test For each unique PIID in File C, the sum of each TransactionObligatedAmount should match (but with opposite
signs) the sum of the FederalActionObligation reported in D1. This rule does not apply if the ATA field is
populated and is different from the Agency ID. Ignore rows that contain a PAID.
"""
# Create a 12 character random piid
piid_1 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid_2 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid_3 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
# First piid rows
af_1_row_1 = AwardFinancialFactory(transaction_obligated_amou=1100, piid=piid_1.lower(), parent_award_id='',
allocation_transfer_agency=None)
af_1_row_2 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid_1, parent_award_id=None,
allocation_transfer_agency=None)
# Ignored because it has a paid
af_1_row_3 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid_1.upper(), parent_award_id='Test',
allocation_transfer_agency=None)
# Add a row for a different piid
af_2_row_1 = AwardFinancialFactory(transaction_obligated_amou=9900, piid=piid_2.lower(), parent_award_id=None,
allocation_transfer_agency=None)
# Matching ata/aid, not ignored
af_2_row_2 = AwardFinancialFactory(transaction_obligated_amou=99, piid=piid_2, parent_award_id=None,
allocation_transfer_agency='123', agency_identifier='123')
# Not matching ata/aid, ignored
af_2_row_3 = AwardFinancialFactory(transaction_obligated_amou=10, piid=piid_2.upper(), parent_award_id=None,
allocation_transfer_agency='345', agency_identifier='123')
# Third piid with all rows ignored because one has an ATA different from AID
af_3_row_1 = AwardFinancialFactory(transaction_obligated_amou=8888, piid=piid_3.lower(), parent_award_id=None,
allocation_transfer_agency='123', agency_identifier='345')
af_3_row_2 = AwardFinancialFactory(transaction_obligated_amou=8888, piid=piid_3, parent_award_id=None,
allocation_transfer_agency=None)
# Sum all of these should be equal to that of first piid
ap_1_row_1 = AwardProcurementFactory(piid=piid_1, parent_award_id=None, federal_action_obligation=-1100)
ap_1_row_2 = AwardProcurementFactory(piid=piid_1.lower(), parent_award_id=None, federal_action_obligation=-10)
ap_1_row_3 = AwardProcurementFactory(piid=piid_1, parent_award_id=None, federal_action_obligation=-1)
# Checking second piid
ap_2 = AwardProcurementFactory(piid=piid_2, parent_award_id='1234', federal_action_obligation=-9999)
# This one doesn't match but will be ignored
ap_3 = AwardProcurementFactory(piid=piid_3, parent_award_id=None, federal_action_obligation=-9999)
errors = number_of_errors(_FILE, database, models=[af_1_row_1, af_1_row_2, af_1_row_3, af_2_row_1, af_2_row_2,
af_2_row_3, af_3_row_1, af_3_row_2, ap_1_row_1, ap_1_row_2,
ap_1_row_3, ap_2, ap_3])
assert errors == 0
def test_generate_e_file_csv(monkeypatch, mock_broker_config_paths, database):
""" Verify that an appropriate CSV is written, based on fileE.Row's structure """
# Create an award so that we have _a_ duns
sub = SubmissionFactory()
database.session.add(sub)
database.session.commit()
ap = AwardProcurementFactory(submission_id=sub.submission_id)
database.session.add(ap)
database.session.commit()
file_path = str(mock_broker_config_paths['broker_files'].join('e_test1'))
job = JobFactory(job_status_id=JOB_STATUS_DICT['running'],
job_type_id=JOB_TYPE_DICT['file_upload'],
file_type_id=FILE_TYPE_DICT['executive_compensation'],
filename=file_path,
original_filename='e_test1',
submission_id=sub.submission_id)
database.session.add(job)
database.session.commit()
monkeypatch.setattr(file_generation_manager.fileE, 'row_to_dict', Mock())
file_generation_manager.fileE.row_to_dict.return_value = {}
monkeypatch.setattr(file_generation_manager.fileE, 'retrieve_rows', Mock())
file_generation_manager.fileE.retrieve_rows.return_value = [
fileE.Row('a', 'b', 'c', 'd', '1a', '1b', '2a', '2b', '3a', '3b', '4a',
'4b', '5a', '5b'),
fileE.Row('A', 'B', 'C', 'D', '1A', '1B', '2A', '2B', '3A', '3B', '4A',
'4B', '5A', '5B')
]
monkeypatch.setattr(file_generation_manager, 'mark_job_status', Mock())
file_gen_manager = FileGenerationManager(job, None, None,
CONFIG_BROKER['local'])
file_gen_manager.generate_e_file()
expected = [[
'AwardeeOrRecipientUniqueIdentifier',
'AwardeeOrRecipientLegalEntityName', 'UltimateParentUniqueIdentifier',
'UltimateParentLegalEntityName', 'HighCompOfficer1FullName',
'HighCompOfficer1Amount', 'HighCompOfficer2FullName',
'HighCompOfficer2Amount', 'HighCompOfficer3FullName',
'HighCompOfficer3Amount', 'HighCompOfficer4FullName',
'HighCompOfficer4Amount', 'HighCompOfficer5FullName',
'HighCompOfficer5Amount'
],
[
'a', 'b', 'c', 'd', '1a', '1b', '2a', '2b', '3a', '3b',
'4a', '4b', '5a', '5b'
],
[
'A', 'B', 'C', 'D', '1A', '1B', '2A', '2B', '3A', '3B',
'4A', '4B', '5A', '5B'
]]
assert read_file_rows(file_path) == expected
def test_failure(database):
""" Unique PIID, ParentAwardId from file C doesn't exist in file D1 during the same reporting period. """
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
allocation_transfer_agency=None)
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
allocation_transfer_agency='bad')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
def test_success(database):
""" Test for each unique combination of PIID/ParentAwardId in File C, the sum of each TransactionObligatedAmount
should match (but with opposite signs) the sum of the FederalActionObligation reported in D1. This rule does not
apply if the ATA field is populated and is different from the Agency ID. """
# Create a 12 character random parent_award_id
paid_1 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
paid_2 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
paid_3 = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
piid = ''.join(choice(ascii_uppercase + ascii_lowercase + digits) for _ in range(12))
af_1_row_1 = AwardFinancialFactory(transaction_obligated_amou=1100, piid=piid, parent_award_id=paid_1,
allocation_transfer_agency=None)
af_1_row_2 = AwardFinancialFactory(transaction_obligated_amou=11, piid=piid, parent_award_id=paid_1,
allocation_transfer_agency=None)
# next 2 rows ignored because they don't have a PIID
af_1_row_3 = AwardFinancialFactory(transaction_obligated_amou=11, piid=None, parent_award_id=paid_1,
allocation_transfer_agency=None)
af_1_row_4 = AwardFinancialFactory(transaction_obligated_amou=11, piid='', parent_award_id=paid_1,
allocation_transfer_agency=None)
# Two entries that aren't ignored because they have matching ATA/AID or no ATA
af_2_row_1 = AwardFinancialFactory(transaction_obligated_amou=9900, piid=piid, parent_award_id=paid_2,
allocation_transfer_agency=None)
af_2_row_2 = AwardFinancialFactory(transaction_obligated_amou=99, piid=piid, parent_award_id=paid_2,
allocation_transfer_agency="good", agency_identifier="good")
# Entry that is ignored because the ATA/AID don't match
af_3 = AwardFinancialFactory(transaction_obligated_amou=8888, piid=piid, parent_award_id=paid_3,
allocation_transfer_agency="good", agency_identifier="bad")
# Combine these to match paid_1
ap_1_row_1 = AwardProcurementFactory(parent_award_id=paid_1, piid=piid, federal_action_obligation=-1100)
ap_1_row_2 = AwardProcurementFactory(parent_award_id=paid_1, piid=piid, federal_action_obligation=-10)
ap_1_row_3 = AwardProcurementFactory(parent_award_id=paid_1, piid=piid, federal_action_obligation=-1)
# This one should match because nothing is ignored
ap_2 = AwardProcurementFactory(parent_award_id=paid_2, piid=piid, federal_action_obligation=-9999)
# This is ignored because the ATA/AID for this one don't match
ap_3 = AwardProcurementFactory(parent_award_id=paid_3, piid=piid, federal_action_obligation=-9999)
errors = number_of_errors(_FILE, database, models=[af_1_row_1, af_1_row_2, af_1_row_3, af_1_row_4, af_2_row_1,
af_2_row_2, af_3, ap_1_row_1, ap_1_row_2, ap_1_row_3, ap_2,
ap_3])
assert errors == 0
def test_failure(database):
""" Unique PIID, ParentAwardId from file D1 doesn't exist in file C during the same reporting period,
except D1 records with zero FederalActionObligation """
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_other_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
def test_valid_allocation_transfer_agency(database):
"""If File C (award financial) record has a valid allocation transfer agency, rule always passes."""
cgac = CGACFactory(cgac_code='good')
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
allocation_transfer_agency=cgac.cgac_code)
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap, cgac]) == 0
def test_valid_allocation_transfer_agency(database):
"""If File C (award financial) record has a valid allocation transfer agency, rule always passes."""
cgac = CGACFactory(cgac_code='good')
# Perform when there's a transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
allocation_transfer_agency=cgac.cgac_code,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap, cgac]) == 0
def test_failure(database):
""" Unique PIID, ParentAwardId from file D1 doesn't exist in file C during the same reporting period,
except D1 records with zero FederalActionObligation.
"""
ap = AwardProcurementFactory(piid='some_pIId', parent_award_id='some_parent_AWArd_id', federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_other_piid', parent_award_id='some_pARent_award_id',
transaction_obligated_amou=1)
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
ap = AwardProcurementFactory(piid='some_piid', parent_award_id='some_Parent_award_id', federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_pIId', parent_award_id='some_other_parenT_award_id',
transaction_obligated_amou=1)
assert number_of_errors(_FILE, database, models=[af, ap]) == 1
# Check to see that NULL TOA causes an error
ap = AwardProcurementFactory(piid='some_pIId', parent_award_id='some_PArent_award_id', federal_action_obligation=1)
af = AwardFinancialFactory(piid='some_piid', parent_award_id='some_parent_AWard_id',
transaction_obligated_amou=None)
assert number_of_errors(_FILE, database, models=[ap, af]) == 1
def test_generate_e_file_csv(monkeypatch, mock_broker_config_paths, database):
""" Verify that an appropriate CSV is written, based on fileE.Row's structure """
# Create an award so that we have _a_ duns
sub = SubmissionFactory()
database.session.add(sub)
database.session.commit()
ap = AwardProcurementFactory(submission_id=sub.submission_id)
database.session.add(ap)
database.session.commit()
file_path = str(mock_broker_config_paths['broker_files'].join('e_test1'))
job = JobFactory(
job_status=database.session.query(JobStatus).filter_by(name='running').one(),
job_type=database.session.query(JobType).filter_by(name='file_upload').one(),
file_type=database.session.query(FileType).filter_by(name='executive_compensation').one(),
filename=file_path, original_filename='e_test1', submission_id=sub.submission_id,
)
database.session.add(job)
database.session.commit()
monkeypatch.setattr(file_generation_handler.fileE, 'row_to_dict', Mock())
file_generation_handler.fileE.row_to_dict.return_value = {}
monkeypatch.setattr(file_generation_handler.fileE, 'retrieve_rows', Mock())
file_generation_handler.fileE.retrieve_rows.return_value = [
fileE.Row('a', 'b', 'c', 'd', '1a', '1b', '2a', '2b', '3a', '3b', '4a', '4b', '5a', '5b'),
fileE.Row('A', 'B', 'C', 'D', '1A', '1B', '2A', '2B', '3A', '3B', '4A', '4B', '5A', '5B')
]
monkeypatch.setattr(file_generation_handler, 'mark_job_status', Mock())
with Flask(__name__).app_context():
file_generation_handler.generate_e_file(database.session, job, is_local=True)
expected = [
['AwardeeOrRecipientUniqueIdentifier',
'AwardeeOrRecipientLegalEntityName',
'UltimateParentUniqueIdentifier',
'UltimateParentLegalEntityName',
'HighCompOfficer1FullName', 'HighCompOfficer1Amount',
'HighCompOfficer2FullName', 'HighCompOfficer2Amount',
'HighCompOfficer3FullName', 'HighCompOfficer3Amount',
'HighCompOfficer4FullName', 'HighCompOfficer4Amount',
'HighCompOfficer5FullName', 'HighCompOfficer5Amount'],
['a', 'b', 'c', 'd', '1a', '1b', '2a', '2b', '3a', '3b', '4a', '4b', '5a', '5b'],
['A', 'B', 'C', 'D', '1A', '1B', '2A', '2B', '3A', '3B', '4A', '4B', '5A', '5B']
]
assert read_file_rows(file_path) == expected
def test_generate_e_file_csv(monkeypatch, mock_broker_config_paths, database):
"""Verify that an appropriate CSV is written, based on fileE.Row's
structure"""
# Create an award so that we have _a_ duns
sess = database.session
ap = AwardProcurementFactory()
sess.add(ap)
sess.commit()
monkeypatch.setattr(jobQueue.fileE, 'retrieveRows', Mock())
jobQueue.fileE.retrieveRows.return_value = [
fileE.Row('a', 'b', 'c', '1a', '1b', '2a', '2b', '3a', '3b', '4a',
'4b', '5a', '5b'),
fileE.Row('A', 'B', 'C', '1A', '1B', '2A', '2B', '3A', '3B', '4A',
'4B', '5A', '5B')
]
monkeypatch.setattr(jobQueue, 'mark_job_status', Mock())
jobQueue.generate_e_file(ap.submission_id,
1,
'uniq',
'uniq',
is_local=True)
file_path = str(mock_broker_config_paths['broker_files'].join('uniq'))
expected = [[
'AwardeeOrRecipientUniqueIdentifier', 'UltimateParentUniqueIdentifier',
'UltimateParentLegalEntityName', 'HighCompOfficer1FullName',
'HighCompOfficer1Amount', 'HighCompOfficer2FullName',
'HighCompOfficer2Amount', 'HighCompOfficer3FullName',
'HighCompOfficer3Amount', 'HighCompOfficer4FullName',
'HighCompOfficer4Amount', 'HighCompOfficer5FullName',
'HighCompOfficer5Amount'
],
[
'a', 'b', 'c', '1a', '1b', '2a', '2b', '3a', '3b', '4a',
'4b', '5a', '5b'
],
[
'A', 'B', 'C', '1A', '1B', '2A', '2B', '3A', '3B', '4A',
'4B', '5A', '5B'
]]
assert read_file_rows(file_path) == expected
def test_generate_f_rows_false(database, monkeypatch):
"""Make sure we're converting False to a string"""
sub = SubmissionFactory()
database.session.add(sub)
database.session.commit()
award = AwardProcurementFactory(submission_id=sub.submission_id,
awarding_sub_tier_agency_c='1234')
proc = FSRSProcurementFactory(contract_number=award.piid,
idv_reference_number=award.parent_award_id,
subawards=[
FSRSSubcontractFactory(
recovery_model_q1=False,
recovery_model_q2=None)
],
contracting_office_aid='1234')
database.session.add_all([award, proc])
database.session.commit()
results = list(fileF.generate_f_rows(award.submission_id))
assert results[0]['RecModelQuestion1'] == 'False'
assert results[0]['RecModelQuestion2'] == ''
def test_success(database):
""" Unique PIID, ParentAwardId from file C exists in file D1 during the same reporting period. """
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
transaction_obligated_amou='12345')
ap_1 = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
ap_2 = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap_1, ap_2]) == 0
af = AwardFinancialFactory(piid=None,
parent_award_id='some_parent_award_id',
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid='some_piid',
parent_award_id=None,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid', parent_award_id=None)
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
# Not perform when no transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
transaction_obligated_amou=None)
ap = AwardProcurementFactory(piid='some_other_piid',
parent_award_id='some_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
# Not perform when no transaction obligated amount value in the field
af = AwardFinancialFactory(piid='some_piid',
parent_award_id='some_parent_award_id',
transaction_obligated_amou=None)
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
af = AwardFinancialFactory(piid=None,
parent_award_id=None,
transaction_obligated_amou='12345')
ap = AwardProcurementFactory(piid='some_piid',
parent_award_id='some_other_parent_award_id')
assert number_of_errors(_FILE, database, models=[af, ap]) == 0
def test_generate_e_file(mock_broker_config_paths, database):
""" Verify that generate_e_file makes an appropriate query (matching both D1 and D2 entries) and creates
a file matching the expected DUNS
"""
# Generate several file D1 entries, largely with the same submission_id, and with two overlapping DUNS. Generate
# several D2 entries with the same submission_id as well
sess = database.session
sub = SubmissionFactory()
sub_2 = SubmissionFactory()
sess.add_all([sub, sub_2])
sess.commit()
file_path = str(mock_broker_config_paths['broker_files'].join('e_test1'))
job = JobFactory(job_status_id=JOB_STATUS_DICT['running'],
job_type_id=JOB_TYPE_DICT['file_upload'],
file_type_id=FILE_TYPE_DICT['executive_compensation'],
filename=file_path,
original_filename='e_test1',
submission_id=sub.submission_id)
database.session.add(job)
database.session.commit()
model = AwardProcurementFactory(submission_id=sub.submission_id)
aps = [
AwardProcurementFactory(submission_id=sub.submission_id)
for _ in range(4)
]
afas = [
AwardFinancialAssistanceFactory(submission_id=sub.submission_id)
for _ in range(5)
]
same_duns = AwardProcurementFactory(
submission_id=sub.submission_id,
awardee_or_recipient_uniqu=model.awardee_or_recipient_uniqu)
unrelated = AwardProcurementFactory(submission_id=sub_2.submission_id)
duns_list = [
DunsFactory(
awardee_or_recipient_uniqu=model.awardee_or_recipient_uniqu)
]
duns_list.extend([
DunsFactory(awardee_or_recipient_uniqu=ap.awardee_or_recipient_uniqu)
for ap in aps
])
duns_list.extend([
DunsFactory(awardee_or_recipient_uniqu=afa.awardee_or_recipient_uniqu)
for afa in afas
])
sess.add_all(aps + afas + duns_list + [model, same_duns, unrelated])
sess.commit()
file_gen_manager = FileGenerationManager(database.session,
CONFIG_BROKER['local'],
job=job)
file_gen_manager.generate_file()
# check headers
file_rows = read_file_rows(file_path)
assert file_rows[0] == [
'AwardeeOrRecipientUniqueIdentifier',
'AwardeeOrRecipientLegalEntityName', 'UltimateParentUniqueIdentifier',
'UltimateParentLegalEntityName', 'HighCompOfficer1FullName',
'HighCompOfficer1Amount', 'HighCompOfficer2FullName',
'HighCompOfficer2Amount', 'HighCompOfficer3FullName',
'HighCompOfficer3Amount', 'HighCompOfficer4FullName',
'HighCompOfficer4Amount', 'HighCompOfficer5FullName',
'HighCompOfficer5Amount'
]
# Check listed DUNS
expected = [[
duns.awardee_or_recipient_uniqu, duns.legal_business_name,
duns.ultimate_parent_unique_ide, duns.ultimate_parent_legal_enti,
duns.high_comp_officer1_full_na, duns.high_comp_officer1_amount,
duns.high_comp_officer2_full_na, duns.high_comp_officer2_amount,
duns.high_comp_officer3_full_na, duns.high_comp_officer3_amount,
duns.high_comp_officer4_full_na, duns.high_comp_officer4_amount,
duns.high_comp_officer5_full_na, duns.high_comp_officer5_amount
] for duns in duns_list]
received = [file_row for file_row in file_rows[1:]]
assert sorted(received) == list(sorted(expected))
def test_generate_file_f_sql(database, monkeypatch):
""" generate_file_f_sql should provide the query representing F file data related to a submission """
# Setup - create submission, awards, subawards
sess = database.session
sub1 = SubmissionFactory(submission_id=1)
sub2 = SubmissionFactory(submission_id=2)
d1_show = AwardProcurementFactory(submission_id=sub1.submission_id,
piid='PIID1',
parent_award_id='PID1',
awarding_sub_tier_agency_c='ASAC1')
d2_show = AwardFinancialAssistanceFactory(submission_id=sub1.submission_id,
fain='FAIN1')
d1_hide = AwardProcurementFactory(submission_id=sub2.submission_id,
piid='PIID2',
parent_award_id='PID2',
awarding_sub_tier_agency_c='ASAC2')
d2_hide = AwardFinancialAssistanceFactory(submission_id=sub2.submission_id,
fain='FAIN2')
sub_contracts_show = [
SubawardFactory(
id=i,
subaward_type='sub-contract',
award_id=d1_show.piid,
parent_award_id=d1_show.parent_award_id,
awarding_sub_tier_agency_c=d1_show.awarding_sub_tier_agency_c)
for i in range(0, 5)
]
sub_grants_show = [
SubawardFactory(id=i, subaward_type='sub-grant', award_id=d2_show.fain)
for i in range(5, 10)
]
sub_contracts_hide = [
SubawardFactory(
id=i,
subaward_type='sub-contract',
award_id=d1_hide.piid,
parent_award_id=d1_hide.parent_award_id,
awarding_sub_tier_agency_c=d1_hide.awarding_sub_tier_agency_c)
for i in range(10, 15)
]
sub_grants_hide = [
SubawardFactory(id=i, subaward_type='sub-grant', award_id=d2_hide.fain)
for i in range(15, 20)
]
subawards = sub_contracts_show + sub_grants_show + sub_contracts_hide + sub_grants_hide
sess.add_all([sub1, sub2, d1_hide, d1_show, d2_hide, d2_show] + subawards)
sess.commit()
# Gather the sql
file_f_query = fileE_F.generate_file_f_sql(sub1.submission_id)
# Get the records
file_f_records = sess.execute(file_f_query)
file_f_cols = file_f_records.keys()
file_f_value_sets = file_f_records.fetchall()
file_f_results = [
OrderedDict(list(zip(file_f_cols, file_f_value_set)))
for file_f_value_set in file_f_value_sets
]
# Expected Results
expected_file_f_results = [
replicate_file_f_results(subaward)
for subaward in sub_contracts_show + sub_grants_show
]
assert file_f_results == expected_file_f_results
def test_generate_f_rows(database, monkeypatch):
"""generate_f_rows should find and convert subaward data relevant to a
specific submission id. We'll compare the resulting DUNs values for
uniqueness"""
# Setup - create awards, procurements/grants, subawards
sess = database.session
sub_1 = SubmissionFactory()
sub_2 = SubmissionFactory()
sess.add_all([sub_1, sub_2])
sess.commit()
awards = [
AwardProcurementFactory(submission_id=sub_1.submission_id,
piid='PIID1',
parent_award_id='PIID1',
awarding_sub_tier_agency_c='1234'),
AwardProcurementFactory(submission_id=sub_1.submission_id,
piid='PIID2',
parent_award_id='PIID2',
awarding_sub_tier_agency_c='1234'),
AwardFinancialAssistanceFactory(submission_id=sub_1.submission_id,
fain='FAIN1'),
AwardFinancialAssistanceFactory(submission_id=sub_1.submission_id,
fain='FAIN2'),
AwardProcurementFactory(submission_id=sub_2.submission_id,
piid='PIID1',
parent_award_id='PIID1',
awarding_sub_tier_agency_c='1234'),
AwardFinancialAssistanceFactory(submission_id=sub_2.submission_id,
fain='FAIN1')
]
sess.add_all(awards)
procurements = {}
for piid in ('PIID1', 'PIID2', 'PIID3'):
procurements[piid] = [
FSRSProcurementFactory(
contract_number=piid,
idv_reference_number=piid,
subawards=[FSRSSubcontractFactory() for _ in range(3)],
contracting_office_aid='1234'),
FSRSProcurementFactory(contract_number=piid,
idv_reference_number=piid,
subawards=[],
contracting_office_aid='1234'),
FSRSProcurementFactory(
contract_number=piid,
idv_reference_number=piid,
subawards=[FSRSSubcontractFactory() for _ in range(2)],
contracting_office_aid='1234')
]
sess.add_all(procurements[piid])
grants = {}
for fain in ('FAIN0', 'FAIN1'):
grants[fain] = [
FSRSGrantFactory(fain=fain,
subawards=[
FSRSSubgrantFactory() for _ in range(3)
]),
FSRSGrantFactory(fain=fain, subawards=[]),
FSRSGrantFactory(
fain=fain, subawards=[FSRSSubgrantFactory() for _ in range(2)])
]
sess.add_all(grants[fain])
sess.commit()
actual = {
result['SubAwardeeOrRecipientUniqueIdentifier']
for result in fileF.generate_f_rows(sub_1.submission_id)
}
expected = set()
expected.update(sub.duns for proc in procurements['PIID1']
for sub in proc.subawards)
expected.update(sub.duns for proc in procurements['PIID2']
for sub in proc.subawards)
expected.update(sub.duns for grant in grants['FAIN1']
for sub in grant.subawards)
assert actual == expected
def test_generate_f_file_queries_contracts(database, monkeypatch):
""" generate_f_file_queries should provide queries representing halves of F file data related to a submission
This will cover contracts records.
"""
sess = database.session
parent_duns, duns, dom_country, int_country = reference_data(sess)
# Setup - create awards, procurements, subcontracts
sub = SubmissionFactory(submission_id=1)
d1_awd = AwardProcurementFactory(
submission_id=sub.submission_id,
idv_type=None
)
contract_awd = FSRSProcurementFactory(
contract_number=d1_awd.piid,
idv_reference_number=d1_awd.parent_award_id,
contracting_office_aid=d1_awd.awarding_sub_tier_agency_c,
company_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
duns=duns.awardee_or_recipient_uniqu
)
sub_contract_awd = FSRSSubcontractFactory(
parent=contract_awd,
company_address_country=int_country.country_code,
principle_place_country=dom_country.country_code
)
d1_idv = AwardProcurementFactory(
submission_id=sub.submission_id,
idv_type='C'
)
contract_idv = FSRSProcurementFactory(
contract_number=d1_idv.piid,
idv_reference_number=d1_idv.parent_award_id,
contracting_office_aid=d1_idv.awarding_sub_tier_agency_c,
company_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
duns=duns.awardee_or_recipient_uniqu
)
sub_contract_idv = FSRSSubcontractFactory(
parent=contract_idv,
company_address_country=int_country.country_code,
principle_place_country=dom_country.country_code
)
sess.add_all([sub, d1_awd, contract_awd, sub_contract_awd, d1_idv, contract_idv, sub_contract_idv])
sess.commit()
# Gather the sql
contract_query, _ = fileF.generate_f_file_queries(sub.submission_id)
# Get the records
contracts_records = sess.execute(contract_query)
contracts_cols = contracts_records.keys()
contracts_results = contracts_records.fetchall()
# Expected Results
expected_contracts = [
replicate_contract_results(sub, d1_awd, contract_awd, sub_contract_awd, parent_duns, duns, dom_country,
int_country),
replicate_contract_results(sub, d1_idv, contract_idv, sub_contract_idv, parent_duns, duns, dom_country,
int_country)
]
assert sorted(contracts_results, key=lambda result: result[0]) == expected_contracts
assert contracts_cols == EXPECTED_COLS
