def test_fetch_and_replace_batch_saves_data(no_award_db, monkeypatch):
award1 = FSRSProcurementFactory()
award1.subawards = [FSRSSubcontractFactory() for _ in range(4)]
award2 = FSRSProcurementFactory()
award2.subawards = [FSRSSubcontractFactory()]
monkeypatch.setattr(fsrs, 'retrieve_batch',
Mock(return_value=[award1, award2]))
assert no_award_db.query(FSRSProcurement).count() == 0
fsrs.fetch_and_replace_batch(no_award_db, fsrs.PROCUREMENT)
assert no_award_db.query(FSRSProcurement).count() == 2
assert no_award_db.query(FSRSSubcontract).count() == 5
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_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_valueFromMapping_None():
result = fileF.valueFromMapping(FSRSProcurementFactory(),
FSRSSubcontractFactory(),
None,
None,
mapping=None)
assert '' == result
def test_copy_values_procurement():
model_row = fileF.ModelRow(None, FSRSProcurementFactory(duns='DUNS'),
FSRSSubcontractFactory(duns='DUNS SUB'), None,
None)
mapper = fileF.CopyValues(procurement='duns')
assert mapper(model_row) == 'DUNS'
mapper = fileF.CopyValues(subcontract='duns')
assert mapper(model_row) == 'DUNS SUB'
mapper = fileF.CopyValues(grant='duns')
assert mapper(model_row) is None
def test_zipcode_guard():
model_row = fileF.ModelRow(
None, None,
FSRSSubcontractFactory(company_address_country='USA',
company_address_zip='12345'), None, None)
us_zip = fileF.mappings['LegalEntityZIP+4'](model_row)
foreign_zip = fileF.mappings['LegalEntityForeignPostalCode'](model_row)
assert us_zip == '12345'
assert foreign_zip is None
model_row.subcontract.company_address_country = 'RU'
us_zip = fileF.mappings['LegalEntityZIP+4'](model_row)
foreign_zip = fileF.mappings['LegalEntityForeignPostalCode'](model_row)
assert us_zip is None
assert foreign_zip == '12345'
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_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_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_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
sess.query(Subaward).delete(synchronize_session=False)
sess.commit()
parent_duns, duns, dom_country, int_country = reference_data(sess)
# Setup - create awards, procurements, subcontract
sub = SubmissionFactory(submission_id=1)
d1_awd = DetachedAwardProcurementFactory(submission_id=sub.submission_id,
idv_type=None,
unique_award_key='AWD')
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')
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())
sess.add_all([
sub, d1_awd, contract_awd, sub_contract_awd, d1_idv, contract_idv,
sub_contract_idv
])
sess.commit()
# Gather the sql
populate_subaward_table(sess,
'procurement_service',
ids=[contract_awd.id, contract_idv.id])
# Get the records
contracts_results = sess.query(Subaward).order_by(
Subaward.unique_award_key).all()
created_at = updated_at = contracts_results[0].created_at
# Expected Results
assert compare_contract_results(contracts_results[0], d1_awd, contract_awd,
sub_contract_awd, parent_duns, duns,
dom_country, int_country, created_at,
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, 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_valueFromMapping_string():
proc = FSRSProcurementFactory()
sub = FSRSSubcontractFactory(duns='Some DUNS')
result = fileF.valueFromMapping(proc, sub, None, None, mapping='duns')
assert 'Some DUNS' == result
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_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