def test_fetch_and_replace_batch_overrides_data(no_award_db, monkeypatch):
def fetch_duns(award_id):
test_result = no_award_db.query(
FSRSGrant.duns).filter(FSRSGrant.id == award_id).one_or_none()
if test_result:
return test_result[0]
return None
award1 = FSRSGrantFactory(id=1, internal_id='12345', duns='To Be Replaced')
award1.subawards = [FSRSSubgrantFactory() for _ in range(4)]
award2 = FSRSGrantFactory(id=2, internal_id='54321', duns='Not Altered')
award2.subawards = [FSRSSubgrantFactory()]
monkeypatch.setattr(fsrs, 'retrieve_batch',
Mock(return_value=[award1, award2]))
fsrs.fetch_and_replace_batch(no_award_db, fsrs.GRANT)
assert fetch_duns(1) == 'To Be Replaced'
assert fetch_duns(2) == 'Not Altered'
# 5 subawards, 4 from award1 and 1 from award2
assert no_award_db.query(FSRSSubgrant).count() == 5
no_award_db.expunge_all() # Reset the model cache
award3 = FSRSGrantFactory(id=3, internal_id='12345', duns='Replaced')
award3.subawards = [FSRSSubgrantFactory() for _ in range(2)]
monkeypatch.setattr(fsrs, 'retrieve_batch', Mock(return_value=[award3]))
fsrs.fetch_and_replace_batch(no_award_db, fsrs.GRANT)
assert fetch_duns(1) is None
assert fetch_duns(2) == 'Not Altered'
assert fetch_duns(3) == 'Replaced'
# 3 subawards, 4 from award1/award3 and 1 from award2
assert no_award_db.query(FSRSSubgrant).count() == 3
def test_fetchAndReplaceBatch_overrides_data(no_award_db, monkeypatch):
def fetch_duns(award_id):
return no_award_db.query(
FSRSGrant.duns).filter(FSRSGrant.id == award_id).one()[0]
award1 = FSRSGrantFactory(id=1, duns='To Be Replaced')
award1.subawards = [FSRSSubgrantFactory() for _ in range(4)]
award2 = FSRSGrantFactory(id=2, duns='Not Altered')
award2.subawards = [FSRSSubgrantFactory()]
monkeypatch.setattr(fsrs, 'retrieveBatch',
Mock(return_value=[award1, award2]))
fsrs.fetchAndReplaceBatch(no_award_db, fsrs.GRANT)
assert fetch_duns(1) == 'To Be Replaced'
assert fetch_duns(2) == 'Not Altered'
# 5 subawards, 4 from award1 and 1 from award2
assert no_award_db.query(FSRSSubgrant).count() == 5
no_award_db.expunge_all() # Reset the model cache
award3 = FSRSGrantFactory(id=1, duns='Replaced')
award3.subawards = [FSRSSubgrantFactory() for _ in range(2)]
monkeypatch.setattr(fsrs, 'retrieveBatch', Mock(return_value=[award3]))
fsrs.fetchAndReplaceBatch(no_award_db, fsrs.GRANT)
assert fetch_duns(1) == 'Replaced'
assert fetch_duns(2) == 'Not Altered'
# 3 subawards, 4 from award1/award3 and 1 from award2
assert no_award_db.query(FSRSSubgrant).count() == 3
def test_generateFRows(database, monkeypatch):
"""generateFRows should find and convert subaward data relevant to a
specific submission id. We'll compare the resulting DUNs values for
uniqueness"""
sess = database.session
mock_fn = Mock(return_value=({'fain1', 'fain2'}, {'piid1'}))
monkeypatch.setattr(fileF, 'relevantFainsPiids', mock_fn)
# Create some dummy data: 4 procurements, 4 grants, each with 3 subawards
procs = [
FSRSProcurementFactory(contract_number='piid' + str(i))
for i in range(0, 4)
]
for proc in procs:
proc.subawards = [FSRSSubcontractFactory() for _ in range(3)]
grants = [FSRSGrantFactory(fain='fain' + str(i)) for i in range(0, 4)]
for grant in grants:
grant.subawards = [FSRSSubgrantFactory() for _ in range(3)]
sess.add_all(procs + grants)
actual = {
result['SubAwardeeOrRecipientUniqueIdentifier']
for result in fileF.generateFRows(sess, 1234)
}
expected = set()
for award in procs[1:3] + grants[1:2]:
expected.update(sub.duns for sub in award.subawards)
assert actual == expected
# Also make sure that we filtered by the right submission
assert mock_fn.call_args == ((sess, 1234), )
def test_copy_values_grant():
model_row = fileF.ModelRow(None, None, None, FSRSGrantFactory(duns='DUNS'),
FSRSSubgrantFactory(duns='DUNS SUB'))
mapper = fileF.CopyValues(grant='duns')
assert mapper(model_row) == 'DUNS'
mapper = fileF.CopyValues(subgrant='duns')
assert mapper(model_row) == 'DUNS SUB'
mapper = fileF.CopyValues(procurement='duns')
assert mapper(model_row) is None
def test_next_id(no_award_db):
no_award_db.add_all([
FSRSProcurementFactory(id=5),
FSRSProcurementFactory(id=3),
FSRSGrantFactory(id=2)
])
no_award_db.commit()
assert 6 == FSRSProcurement.next_id(no_award_db)
assert 3 == FSRSGrant.next_id(no_award_db)
def test_fetch_and_replace_batch_overrides_data(no_award_db, monkeypatch):
def fetch_duns(award_id):
test_result = no_award_db.query(FSRSGrant.duns).filter(
FSRSGrant.id == award_id).one_or_none()
if test_result:
return test_result[0]
return None
award1 = FSRSGrantFactory(id=1, internal_id='12345', duns='To Be Replaced')
award1.subawards = [FSRSSubgrantFactory() for _ in range(4)]
award2 = FSRSGrantFactory(id=2, internal_id='54321', duns='Not Altered')
award2.subawards = [FSRSSubgrantFactory()]
monkeypatch.setattr(fsrs, 'retrieve_batch', Mock(return_value=[award1, award2]))
fsrs.fetch_and_replace_batch(no_award_db, fsrs.GRANT)
assert fetch_duns(1) == 'To Be Replaced'
assert fetch_duns(2) == 'Not Altered'
# 5 subawards, 4 from award1 and 1 from award2
assert no_award_db.query(FSRSSubgrant).count() == 5
no_award_db.expunge_all() # Reset the model cache
award3 = FSRSGrantFactory(id=3, internal_id='12345', duns='Replaced')
award3.subawards = [FSRSSubgrantFactory() for _ in range(2)]
monkeypatch.setattr(fsrs, 'retrieve_batch', Mock(return_value=[award3]))
fsrs.fetch_and_replace_batch(no_award_db, fsrs.GRANT)
assert fetch_duns(1) is None
assert fetch_duns(2) == 'Not Altered'
assert fetch_duns(3) == 'Replaced'
# 3 subawards, 4 from award1/award3 and 1 from award2
assert no_award_db.query(FSRSSubgrant).count() == 3
def test_valueFromMapping_tuple():
proc = FSRSProcurementFactory()
subc = FSRSSubcontractFactory(duns='subcontract DUNS')
grant = FSRSGrantFactory()
subg = FSRSSubgrantFactory(parent_duns='subgrant DUNS')
mapping = ('duns', 'parent_duns')
assert 'subcontract DUNS' == fileF.valueFromMapping(
proc, subc, None, None, mapping)
assert 'subgrant DUNS' == fileF.valueFromMapping(None, None, grant, subg,
mapping)
# None indicates that no result should be provided
mapping = ('duns', None)
assert 'subcontract DUNS' == fileF.valueFromMapping(
proc, subc, None, None, mapping)
assert '' == fileF.valueFromMapping(None, None, grant, subg, mapping)
def test_fix_broken_links(database, monkeypatch):
""" Ensure that fix_broken_links works as intended """
# Setup - create awards, procurements/grants, subawards
sess = database.session
sess.query(Subaward).delete(synchronize_session=False)
sess.commit()
parent_duns, duns, dom_country, int_country = reference_data(sess)
min_date = '2019-06-06'
award_updated_at = '2019-06-07'
# Setup - Grants
sub = SubmissionFactory(submission_id=1)
d2_non = PublishedAwardFinancialAssistanceFactory(
submission_id=sub.submission_id,
record_type='2',
unique_award_key='NON',
is_active=True,
updated_at=award_updated_at)
grant_non = FSRSGrantFactory(
fain=d2_non.fain,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34))
sub_grant_non = FSRSSubgrantFactory(
parent=grant_non,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
duns=duns.awardee_or_recipient_uniqu,
subaward_date=datetime.now())
d2_agg = PublishedAwardFinancialAssistanceFactory(
submission_id=sub.submission_id,
record_type='1',
unique_award_key='AGG',
is_active=True,
updated_at=award_updated_at)
grant_agg = FSRSGrantFactory(
fain=d2_agg.fain,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
cfda_numbers='00.003 CFDA 3',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34))
sub_grant_agg = FSRSSubgrantFactory(
parent=grant_agg,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
duns=duns.awardee_or_recipient_uniqu,
subaward_date=datetime.now())
# Setup - Contracts
d1_awd = DetachedAwardProcurementFactory(submission_id=sub.submission_id,
idv_type=None,
unique_award_key='AWD',
updated_at=award_updated_at)
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,
date_signed=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34))
sub_contract_awd = FSRSSubcontractFactory(
parent=contract_awd,
company_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
subcontract_date=datetime.now())
d1_idv = DetachedAwardProcurementFactory(submission_id=sub.submission_id,
idv_type='C',
unique_award_key='IDV',
updated_at=award_updated_at)
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,
date_signed=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34))
sub_contract_idv = FSRSSubcontractFactory(
parent=contract_idv,
company_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
subcontract_date=datetime.now())
# Note: not including d1/d2 data
sess.add_all(
[sub, contract_awd, sub_contract_awd, contract_idv, sub_contract_idv])
sess.add_all([sub, grant_non, sub_grant_non, grant_agg, sub_grant_agg])
sess.commit()
populate_subaward_table(sess,
'procurement_service',
ids=[contract_awd.id, contract_idv.id])
populate_subaward_table(sess,
'grant_service',
ids=[grant_agg.id, grant_non.id])
contracts_results = sess.query(Subaward).order_by(Subaward.unique_award_key).\
filter(Subaward.subaward_type == 'sub-contract').all()
grants_results = sess.query(Subaward).order_by(Subaward.unique_award_key).\
filter(Subaward.subaward_type == 'sub-grant').all()
original_ids = [result.id for result in contracts_results + grants_results]
grant_created_at = grant_updated_at = grants_results[0].created_at
contract_created_at = contract_updated_at = contracts_results[0].created_at
# Expected Results - should be False as the award isn't provided
assert compare_contract_results(
contracts_results[0], d1_awd, contract_awd, sub_contract_awd,
parent_duns, duns, dom_country, int_country, contract_created_at,
contract_updated_at) is False
assert compare_contract_results(
contracts_results[1], d1_idv, contract_idv, sub_contract_idv,
parent_duns, duns, dom_country, int_country, contract_created_at,
contract_updated_at) is False
assert compare_grant_results(
grants_results[0], d2_agg, grant_agg, sub_grant_agg, parent_duns, duns,
dom_country, int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(
grants_results[1], d2_non, grant_non, sub_grant_non, parent_duns, duns,
dom_country, int_country, grant_created_at, grant_updated_at) is False
# now add the awards and fix the broken links
sess.add_all([d1_awd, d2_non, d1_idv, d2_agg])
sess.commit()
updated_proc_count = fix_broken_links(sess,
'procurement_service',
min_date=min_date)
updated_grant_count = fix_broken_links(sess,
'grant_service',
min_date=min_date)
assert updated_proc_count == updated_grant_count == 2
contracts_results = sess.query(Subaward).order_by(Subaward.unique_award_key).\
filter(Subaward.subaward_type == 'sub-contract').all()
grants_results = sess.query(Subaward).order_by(Subaward.unique_award_key).\
filter(Subaward.subaward_type == 'sub-grant').all()
updated_ids = [result.id for result in contracts_results + grants_results]
contract_created_at = contracts_results[0].created_at
contract_updated_at = contracts_results[0].updated_at
grant_created_at = grants_results[0].created_at
grant_updated_at = grants_results[0].updated_at
# Expected Results - should now be True as the award is now available
assert compare_contract_results(
contracts_results[0], d1_awd, contract_awd, sub_contract_awd,
parent_duns, duns, dom_country, int_country, contract_created_at,
contract_updated_at) is True
assert compare_contract_results(
contracts_results[1], d1_idv, contract_idv, sub_contract_idv,
parent_duns, duns, dom_country, int_country, contract_created_at,
contract_updated_at) is True
assert compare_grant_results(
grants_results[0], d2_agg, grant_agg, sub_grant_agg, parent_duns, duns,
dom_country, int_country, grant_created_at, grant_updated_at) is True
assert compare_grant_results(
grants_results[1], d2_non, grant_non, sub_grant_non, parent_duns, duns,
dom_country, int_country, grant_created_at, grant_updated_at) is True
# Ensuring only updates occurred, no deletes/inserts
assert set(original_ids) == set(updated_ids)
def test_generate_f_file_queries_grants(database, monkeypatch):
""" generate_f_file_queries should provide queries representing halves of F file data related to a submission
This will cover grants records.
"""
# Setup - create awards, procurements/grants, subawards
sess = database.session
sess.query(Subaward).delete(synchronize_session=False)
sess.commit()
parent_duns, duns, dom_country, int_country = reference_data(sess)
# Setup - create awards, procurements, subcontracts
sub = SubmissionFactory(submission_id=1)
d2_non = PublishedAwardFinancialAssistanceFactory(
submission_id=sub.submission_id,
record_type='2',
unique_award_key='NON',
is_active=True)
grant_non = FSRSGrantFactory(
fain=d2_non.fain,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34))
sub_grant_non = FSRSSubgrantFactory(
parent=grant_non,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
duns=duns.awardee_or_recipient_uniqu,
subaward_date=datetime.now())
d2_agg = PublishedAwardFinancialAssistanceFactory(
submission_id=sub.submission_id,
record_type='1',
unique_award_key='AGG',
is_active=True)
grant_agg = FSRSGrantFactory(
fain=d2_agg.fain,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
cfda_numbers='00.003 CFDA 3',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34))
sub_grant_agg = FSRSSubgrantFactory(
parent=grant_agg,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
duns=duns.awardee_or_recipient_uniqu,
subaward_date=datetime.now())
sess.add_all([
sub, d2_non, grant_non, sub_grant_non, d2_agg, grant_agg, sub_grant_agg
])
sess.commit()
# Gather the sql
populate_subaward_table(sess,
'grant_service',
ids=[grant_agg.id, grant_non.id])
# Get the records
grants_results = sess.query(Subaward).order_by(
Subaward.unique_award_key).all()
created_at = updated_at = grants_results[0].created_at
# Expected Results
assert compare_grant_results(grants_results[0], d2_agg, grant_agg,
sub_grant_agg, parent_duns, duns, dom_country,
int_country, created_at, updated_at) is True
assert compare_grant_results(grants_results[1], d2_non, grant_non,
sub_grant_non, parent_duns, duns, dom_country,
int_country, created_at, updated_at) is True
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_fix_broken_links(database, monkeypatch):
""" Ensure that fix_broken_links works as intended """
# Setup - create awards, procurements/grants, subawards
sess = database.session
sess.query(Subaward).delete(synchronize_session=False)
sess.commit()
parent_recipient, recipient, dom_country, int_country = reference_data(sess)
min_date = '2019-06-06'
award_updated_at = '2019-06-07'
# Setup - Grants
sub = SubmissionFactory(submission_id=1)
sub2 = SubmissionFactory(submission_id=2)
sub3 = SubmissionFactory(submission_id=3)
fabs_non_pop_subtier = PublishedFABSFactory(
submission_id=sub.submission_id,
awarding_sub_tier_agency_c='1234',
record_type=2,
unique_award_key='NON-POP-SUB',
fain='NON-FAIN-WITH-DASHES-POP-SUB',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-01'
)
fabs_non_pop_subtier_2 = PublishedFABSFactory(
submission_id=sub.submission_id,
awarding_sub_tier_agency_c='1234',
record_type=2,
unique_award_key='NON-POP-SUB',
fain='NON-FAIN-WITH-DASHES-POP-SUB',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-02'
)
grant_non_pop_subtier = FSRSGrantFactory(
fain=fabs_non_pop_subtier.fain.replace('-', ''),
federal_agency_id='1234',
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_non_pop_subtier = FSRSSubgrantFactory(
parent=grant_non_pop_subtier,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
parent_uei=parent_recipient.uei,
uei_number=recipient.uei.lower(),
subaward_date=datetime.now()
)
fabs_non_null_subtier = PublishedFABSFactory(
submission_id=sub.submission_id,
awarding_sub_tier_agency_c='5678',
record_type=2,
unique_award_key='NON-NULL-SUB',
fain='NON-FAIN-WITH-DASHES-NULL-SUB',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-01'
)
fabs_non_null_subtier_2 = PublishedFABSFactory(
submission_id=sub.submission_id,
awarding_sub_tier_agency_c='5678',
record_type=2,
unique_award_key='NON-NULL-SUB',
fain='NON-FAIN-WITH-DASHES-NULL-SUB',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-02'
)
grant_non_null_subtier = FSRSGrantFactory(
fain=fabs_non_null_subtier.fain.replace('-', ''),
federal_agency_id=None,
awardee_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_non_null_subtier = FSRSSubgrantFactory(
parent=grant_non_null_subtier,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
parent_uei=parent_recipient.uei,
uei_number=recipient.uei,
subaward_date=datetime.now()
)
fabs_non_other = PublishedFABSFactory(
submission_id=sub2.submission_id,
awarding_sub_tier_agency_c='1357',
record_type=2,
unique_award_key='NON-OTHER',
fain='NON-FAIN-WITH-DASHES-OTHER',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-01'
)
fabs_non_other_2 = PublishedFABSFactory(
submission_id=sub2.submission_id,
awarding_sub_tier_agency_c='1357',
record_type=2,
unique_award_key='NON-OTHER',
fain='NON-FAIN-WITH-DASHES-OTHER',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-02'
)
fabs_non_other_dup = PublishedFABSFactory(
submission_id=sub3.submission_id,
awarding_sub_tier_agency_c='2468',
record_type=2,
unique_award_key='NON-OTHER',
fain='NON-FAIN-WITH-DASHES-OTHER',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-01'
)
fabs_non_other_dup_2 = PublishedFABSFactory(
submission_id=sub3.submission_id,
awarding_sub_tier_agency_c='2468',
record_type=2,
unique_award_key='NON-OTHER',
fain='NON-FAIN-WITH-DASHES-OTHER',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-02'
)
grant_non_other = FSRSGrantFactory(
fain=fabs_non_other.fain.replace('-', ''),
federal_agency_id=None,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_non_other = FSRSSubgrantFactory(
parent=grant_non_other,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
parent_uei=parent_recipient.uei,
uei_number=recipient.uei,
subaward_date=datetime.now()
)
fabs_agg = PublishedFABSFactory(
submission_id=sub.submission_id,
awarding_sub_tier_agency_c='1234',
record_type=1,
unique_award_key='AGG',
fain='AGG-FAIN-WITH-DASHES',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-01'
)
fabs_agg_2 = PublishedFABSFactory(
submission_id=sub.submission_id,
awarding_sub_tier_agency_c='1234',
record_type=1,
unique_award_key='AGG',
fain='AGG-FAIN-WITH-DASHES',
is_active=True,
updated_at=award_updated_at,
action_date='2020-01-02'
)
grant_agg = FSRSGrantFactory(
fain=fabs_agg.fain.replace('-', ''),
federal_agency_id='1234',
awardee_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.003 CFDA 3',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_agg = FSRSSubgrantFactory(
parent=grant_agg,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
parent_uei=parent_recipient.uei,
uei_number=recipient.uei,
subaward_date=datetime.now()
)
# Setup - Contracts
d1_awd = DetachedAwardProcurementFactory(
submission_id=sub.submission_id,
idv_type=None,
unique_award_key='AWD',
piid='AWD-PIID-WITH-DASHES',
parent_award_id='AWD-PARENT-AWARD-ID-WITH-DASHES',
updated_at=award_updated_at,
action_date='2020-01-01'
)
d1_awd_2 = DetachedAwardProcurementFactory(
submission_id=sub.submission_id,
idv_type=None,
unique_award_key='AWD',
piid='AWD-PIID-WITH-DASHES',
parent_award_id='AWD-PARENT-AWARD-ID-WITH-DASHES',
updated_at=award_updated_at,
action_date='2020-01-02'
)
contract_awd = FSRSProcurementFactory(
contract_number=d1_awd.piid.replace('-', ''),
idv_reference_number=d1_awd.parent_award_id.replace('-', ''),
contracting_office_aid=d1_awd.awarding_sub_tier_agency_c,
company_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
uei_number=recipient.uei,
date_signed=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_contract_awd = FSRSSubcontractFactory(
parent=contract_awd,
company_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
subcontract_date=datetime.now()
)
d1_idv = DetachedAwardProcurementFactory(
submission_id=sub.submission_id,
idv_type='C',
unique_award_key='IDV',
piid='IDV-PIID-WITH-DASHES',
parent_award_id='IDV-PARENT-AWARD-IDV-WITH-DASHES',
updated_at=award_updated_at,
action_date='2020-01-01'
)
d1_idv_2 = DetachedAwardProcurementFactory(
submission_id=sub.submission_id,
idv_type='C',
unique_award_key='IDV',
piid='IDV-PIID-WITH-DASHES',
parent_award_id='IDV-PARENT-AWARD-IDV-WITH-DASHES',
updated_at=award_updated_at,
action_date='2020-01-02'
)
contract_idv = FSRSProcurementFactory(
contract_number=d1_idv.piid.replace('-', ''),
idv_reference_number=d1_idv.parent_award_id.replace('-', ''),
contracting_office_aid=d1_idv.awarding_sub_tier_agency_c,
company_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
uei_number=recipient.uei,
date_signed=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_contract_idv = FSRSSubcontractFactory(
parent=contract_idv,
company_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
subcontract_date=datetime.now()
)
# Note: not including d1/fabs data
sess.add_all([sub, contract_awd, sub_contract_awd, contract_idv, sub_contract_idv])
sess.add_all([sub, grant_non_pop_subtier, sub_grant_non_pop_subtier, grant_non_null_subtier,
sub_grant_non_null_subtier, grant_non_other, sub_grant_non_other, grant_agg, sub_grant_agg])
sess.commit()
populate_subaward_table(sess, 'procurement_service', ids=[contract_awd.id, contract_idv.id])
populate_subaward_table(sess, 'grant_service', ids=[grant_agg.id, grant_non_pop_subtier.id,
grant_non_null_subtier.id, grant_non_other.id])
contracts_results = sess.query(Subaward).order_by(Subaward.unique_award_key).\
filter(Subaward.subaward_type == 'sub-contract').all()
grants_results = sess.query(Subaward).order_by(Subaward.award_id).\
filter(Subaward.subaward_type == 'sub-grant').all()
original_ids = [result.id for result in contracts_results + grants_results]
grant_created_at = grant_updated_at = grants_results[0].created_at
contract_created_at = contract_updated_at = contracts_results[0].created_at
# Expected Results - should be False as the award isn't provided
assert compare_contract_results(contracts_results[0], d1_awd, contract_awd, sub_contract_awd, dom_country,
int_country, contract_created_at, contract_updated_at) is False
assert compare_contract_results(contracts_results[1], d1_idv, contract_idv, sub_contract_idv, dom_country,
int_country, contract_created_at, contract_updated_at) is False
assert compare_grant_results(grants_results[0], fabs_agg, grant_agg, sub_grant_agg, parent_recipient, recipient,
dom_country, int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(grants_results[1], fabs_non_other, grant_non_other, sub_grant_non_other,
parent_recipient, recipient, dom_country, int_country, grant_created_at,
grant_updated_at) is False
assert compare_grant_results(grants_results[2], fabs_non_null_subtier, grant_non_null_subtier,
sub_grant_non_null_subtier, parent_recipient, recipient, dom_country,
int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(grants_results[3], fabs_non_pop_subtier, grant_non_pop_subtier,
sub_grant_non_pop_subtier, parent_recipient, recipient, dom_country,
int_country, grant_created_at, grant_updated_at) is False
# now add the awards and fix the broken links
sess.add_all([d1_awd, d1_awd_2, d1_idv, d1_idv_2, fabs_non_null_subtier, fabs_non_null_subtier_2,
fabs_non_pop_subtier, fabs_non_pop_subtier_2, fabs_non_other, fabs_non_other_2, fabs_non_other_dup,
fabs_non_other_dup_2, fabs_agg, fabs_agg_2])
sess.commit()
updated_proc_count = fix_broken_links(sess, 'procurement_service', min_date=min_date)
updated_grant_count = fix_broken_links(sess, 'grant_service', min_date=min_date)
assert updated_proc_count == 2
# Note: Aggregates and blank federal_agency_ids should still not be linked, so 1 and not 3
assert updated_grant_count == 1
contracts_results = sess.query(Subaward).order_by(Subaward.unique_award_key).\
filter(Subaward.subaward_type == 'sub-contract').all()
grants_results = sess.query(Subaward).order_by(Subaward.award_id).\
filter(Subaward.subaward_type == 'sub-grant').all()
updated_ids = [result.id for result in contracts_results + grants_results]
contract_created_at = contracts_results[0].created_at
contract_updated_at = contracts_results[0].updated_at
grant_created_at = grants_results[0].created_at
grant_updated_at = grants_results[0].updated_at
# Expected Results - should now be True as the award is now available
assert compare_contract_results(contracts_results[0], d1_awd, contract_awd, sub_contract_awd, dom_country,
int_country, contract_created_at, contract_updated_at) is True
assert compare_contract_results(contracts_results[1], d1_idv, contract_idv, sub_contract_idv, dom_country,
int_country, contract_created_at, contract_updated_at) is True
assert compare_grant_results(grants_results[0], fabs_agg, grant_agg, sub_grant_agg, parent_recipient, recipient,
dom_country, int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(grants_results[1], fabs_non_null_subtier, grant_non_null_subtier,
sub_grant_non_null_subtier, parent_recipient, recipient, dom_country,
int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(grants_results[2], fabs_non_other, grant_non_other, sub_grant_non_other,
parent_recipient, recipient, dom_country, int_country, grant_created_at,
grant_updated_at) is False
assert compare_grant_results(grants_results[3], fabs_non_pop_subtier, grant_non_pop_subtier,
sub_grant_non_pop_subtier, parent_recipient, recipient, dom_country,
int_country, grant_created_at, grant_updated_at) is True
# Ensuring only updates occurred, no deletes/inserts
assert set(original_ids) == set(updated_ids)
def test_generate_f_file_queries_grants(database, monkeypatch):
""" generate_f_file_queries should provide queries representing halves of F file data related to a submission
This will cover grants records.
"""
# Setup - create awards, procurements/grants, subawards
sess = database.session
sess.query(Subaward).delete(synchronize_session=False)
sess.commit()
parent_recipient, recipient, dom_country, int_country = reference_data(sess)
# Setup - create awards, procurements, subcontracts
sub = SubmissionFactory(submission_id=1)
# FABS Non-aggregate award with federal_agency_id/awarding_sub_tier_agency_c populated
fabs_non_pop_subtier = PublishedFABSFactory(
submission_id=sub.submission_id,
record_type=2,
unique_award_key='NON-POP-SUB',
fain='NON-FAIN-WITH-DASHES-POP-SUB',
awarding_sub_tier_agency_c='1234',
is_active=True,
action_date='2020-01-01'
)
fabs_non_pop_subtier_2 = PublishedFABSFactory(
submission_id=sub.submission_id,
record_type=2,
unique_award_key='NON-POP-SUB',
fain='NON-FAIN-WITH-DASHES-POP-SUB',
awarding_sub_tier_agency_c='1234',
is_active=True,
action_date='2020-01-02'
)
grant_non_pop_subtier = FSRSGrantFactory(
fain=fabs_non_pop_subtier.fain.replace('-', ''),
federal_agency_id='1234',
awardee_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_non_pop_subtier = FSRSSubgrantFactory(
parent=grant_non_pop_subtier,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
parent_uei=parent_recipient.uei,
uei_number=recipient.uei,
subaward_date=datetime.now()
)
# FABS Non-aggregate award with federal_agency_id NULL
fabs_non_null_sub = PublishedFABSFactory(
submission_id=sub.submission_id,
record_type=2,
unique_award_key='NON-NULL-SUB',
fain='NON-FAIN-WITH-DASHES-NULL-SUB',
awarding_sub_tier_agency_c='5678',
is_active=True,
action_date='2020-01-01'
)
fabs_non_null_sub_2 = PublishedFABSFactory(
submission_id=sub.submission_id,
record_type=2,
unique_award_key='NON-NULL-SUB',
fain='NON-FAIN-WITH-DASHES-NULL-SUB',
awarding_sub_tier_agency_c='5678',
is_active=True,
action_date='2020-01-02'
)
grant_non_null_sub = FSRSGrantFactory(
fain=fabs_non_null_sub.fain.replace('-', ''),
federal_agency_id=None,
awardee_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_non_null_sub = FSRSSubgrantFactory(
parent=grant_non_null_sub,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code_2_char,
parent_uei=parent_recipient.uei,
uei_number=recipient.uei,
subaward_date=datetime.now()
)
# FABS Aggregate award
fabs_agg = PublishedFABSFactory(
submission_id=sub.submission_id,
record_type=1,
unique_award_key='AGG',
fain='AGG-FAIN-WITH-DASHES',
awarding_sub_tier_agency_c='1234',
is_active=True,
action_date='2020-01-01'
)
fabs_agg_2 = PublishedFABSFactory(
submission_id=sub.submission_id,
record_type=1,
unique_award_key='AGG',
fain='AGG-FAIN-WITH-DASHES',
awarding_sub_tier_agency_c='1234',
is_active=True,
action_date='2020-01-02'
)
grant_agg = FSRSGrantFactory(
fain=fabs_agg.fain.replace('-', ''),
federal_agency_id='1234',
awardee_address_country=int_country.country_code_2_char,
principle_place_country=dom_country.country_code,
parent_uei=parent_recipient.uei,
cfda_numbers='00.003 CFDA 3',
obligation_date=datetime.now(),
date_submitted=datetime(2019, 5, 30, 16, 25, 12, 34)
)
sub_grant_agg = FSRSSubgrantFactory(
parent=grant_agg,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_uei=parent_recipient.uei.lower(),
uei_number=recipient.uei,
subaward_date=datetime.now()
)
sess.add_all([sub, fabs_non_pop_subtier, fabs_non_pop_subtier_2, grant_non_pop_subtier, sub_grant_non_pop_subtier,
fabs_non_null_sub, fabs_non_null_sub_2, grant_non_null_sub, sub_grant_non_null_sub, fabs_agg,
fabs_agg_2, grant_agg, sub_grant_agg])
sess.commit()
# Gather the sql
populate_subaward_table(sess, 'grant_service', ids=[grant_agg.id, grant_non_pop_subtier.id, grant_non_null_sub.id])
# Get the records
grants_results = sess.query(Subaward).order_by(Subaward.award_id).all()
assert len(grants_results) == 3
created_at = updated_at = grants_results[0].created_at
# Expected Results
# Note: Aggregates should not be linked
assert compare_grant_results(grants_results[0], fabs_agg, grant_agg, sub_grant_agg, parent_recipient, recipient,
dom_country, int_country, created_at, updated_at) is False
# Note: If federal_agency_id is blank, no link should happen and FSRS data needs to be updated
assert compare_grant_results(grants_results[1], fabs_non_null_sub, grant_non_null_sub, sub_grant_non_null_sub,
parent_recipient, recipient, dom_country, int_country, created_at, updated_at) is False
assert compare_grant_results(grants_results[2], fabs_non_pop_subtier, grant_non_pop_subtier,
sub_grant_non_pop_subtier, parent_recipient, recipient, dom_country, int_country,
created_at, updated_at) is True
def test_generate_f_file_queries_grants(database, monkeypatch):
""" generate_f_file_queries should provide queries representing halves of F file data related to a submission
This will cover grants records.
"""
# Setup - create awards, procurements/grants, subawards
sess = database.session
parent_duns, duns, dom_country, int_country = reference_data(sess)
# Setup - create awards, procurements, subcontracts
sub = SubmissionFactory(submission_id=1)
d2_non = AwardFinancialAssistanceFactory(
submission_id=sub.submission_id,
record_type='2'
)
grant_non = FSRSGrantFactory(
fain=d2_non.fain,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
cfda_numbers='00.001 CFDA 1; 00.002 CFDA 2'
)
sub_grant_non = FSRSSubgrantFactory(
parent=grant_non,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
duns=duns.awardee_or_recipient_uniqu
)
d2_agg = AwardFinancialAssistanceFactory(
submission_id=sub.submission_id,
record_type='1'
)
grant_agg = FSRSGrantFactory(
fain=d2_agg.fain,
awardee_address_country=int_country.country_code,
principle_place_country=dom_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
cfda_numbers='00.003 CFDA 3'
)
sub_grant_agg = FSRSSubgrantFactory(
parent=grant_agg,
awardee_address_country=dom_country.country_code,
principle_place_country=int_country.country_code,
parent_duns=parent_duns.awardee_or_recipient_uniqu,
duns=duns.awardee_or_recipient_uniqu
)
sess.add_all([sub, d2_non, grant_non, sub_grant_non, d2_agg, grant_agg, sub_grant_agg])
sess.commit()
# Gather the sql
_, grant_query = fileF.generate_f_file_queries(sub.submission_id)
# Get the records
grants_records = sess.execute(grant_query)
grants_cols = grants_records.keys()
grants_results = grants_records.fetchall()
# Expected Results
expected_grants_results = [
replicate_grant_results(sub, d2_agg, grant_agg, sub_grant_agg, parent_duns, duns, dom_country, int_country),
replicate_grant_results(sub, d2_non, grant_non, sub_grant_non, parent_duns, duns, dom_country, int_country),
]
assert sorted(grants_results, key=lambda result: result[0]) == expected_grants_results
assert grants_cols == EXPECTED_COLS
