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_country_name(): model_row = fileF.ModelRow( None, None, None, None, FSRSSubgrantFactory(awardee_address_country='USA', principle_place_country='DE')) entity = fileF.mappings['LegalEntityCountryName'](model_row) assert entity == 'United States' place = fileF.mappings['PrimaryPlaceOfPerformanceCountryName'](model_row) assert place == 'Germany'
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