def test_success(database):
""" The unique combination of FAIN, AwardModificationAmendmentNumber, URI, and AwardingSubTierAgencyCode must exist
as a currently published record when the record is a correction (i.e., if CorrectionLateDeleteIndicator = C).
Ignore all other CorrectionLateDeleteIndicators in this rule. """
det_award_1 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique="ama1asta1fain1uri1",
correction_late_delete_ind=None)
det_award_2 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique="ama1asta1fain2uri1",
correction_late_delete_ind="C")
det_award_3 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique="ama2asta1fain1uri1",
correction_late_delete_ind="D")
pub_award_1 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique="ama1asta1fain2uri1",
correction_late_delete_ind=None,
is_active=True)
pub_award_2 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique="ama1asta1fain1uri1",
correction_late_delete_ind=None,
is_active=True)
pub_award_3 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique="ama2asta1fain1uri1",
correction_late_delete_ind=None,
is_active=False)
errors = number_of_errors(_FILE,
database,
models=[
det_award_1, det_award_2, det_award_3,
pub_award_1, pub_award_2, pub_award_3
])
assert errors == 0
def test_failure(database):
""" The unique combination of FAIN, AwardModificationAmendmentNumber, URI, CFDA_Number, and
AwardingSubTierAgencyCode must exist as a currently published record when the record is a correction (i.e., if
CorrectionDeleteIndicator = C). Ignore all other CorrectionDeleteIndicators in this rule.
"""
det_award_1 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain2uri1',
correction_delete_indicatr='C')
# Test that capitalization differences don't affect the error
det_award_2 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique='amA1astA1faiN2uri1',
correction_delete_indicatr='C')
pub_award_1 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain1uri1',
correction_delete_indicatr=None,
is_active=True)
pub_award_2 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain2Uri1',
correction_delete_indicatr=None,
is_active=False)
errors = number_of_errors(
_FILE,
database,
models=[det_award_1, det_award_2, pub_award_1, pub_award_2])
assert errors == 2
def test_failure(database):
""" Test fail that empty funding office codes aren't matching invalid office codes from the base record. """
office_1 = OfficeFactory(office_code='12345a', contract_funding_office=True,
financial_assistance_funding_office=True)
office_2 = OfficeFactory(office_code='abcd', contract_funding_office=False,
financial_assistance_funding_office=False)
# Invalid code in record
pub_award_1 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abc', unique_award_key='zyxwv_123',
action_date='20181018', award_modification_amendme='0',
is_active=True)
# Earliest record inactive, newer record has invalid entry
pub_award_2 = PublishedAwardFinancialAssistanceFactory(funding_office_code='12345a', unique_award_key='4321_cba',
action_date='20181018', award_modification_amendme='0',
is_active=False)
pub_award_3 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abc', unique_award_key='4321_cba',
action_date='20181019', award_modification_amendme='1',
is_active=True)
# Has a valid code but it's not a funding code
pub_award_4 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abcd', unique_award_key='123_abc',
action_date='20181018', award_modification_amendme='0',
is_active=True)
# award_modification_amendme number is null
pub_award_5 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abc', unique_award_key='zyxwv_1234',
action_date='20181018', award_modification_amendme=None,
is_active=True)
# Entry for invalid code in base record
det_award_1 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='zyxwv_123',
action_date='20181020', award_modification_amendme='2',
correction_delete_indicatr=None)
# Entry with award_modification_amendme null
det_award_2 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='zyxwv_123',
action_date='20181020', award_modification_amendme=None,
correction_delete_indicatr=None)
# New entry for earliest inactive
det_award_3 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='4321_cba',
action_date='20181020', award_modification_amendme='2',
correction_delete_indicatr=None)
# New entry for has valid non-funding code
det_award_4 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='123_abc',
action_date='20181020', award_modification_amendme='2',
correction_delete_indicatr=None)
# Entry for award_modification_amendme null in base record
det_award_5 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='zyxwv_1234',
action_date='20181020', award_modification_amendme='2',
correction_delete_indicatr=None)
errors = number_of_errors(_FILE, database, models=[office_1, office_2, pub_award_1, pub_award_2, pub_award_3,
pub_award_4, pub_award_5, det_award_1, det_award_2, det_award_3,
det_award_4, det_award_5])
assert errors == 5
def test_success(database):
""" The combination of FAIN, AwardModificationAmendmentNumber, URI, CFDA_Number, and
AwardingSubTierAgencyCode must be unique from currently published ones unless the record is a correction or
deletion (i.e., if CorrectionDeleteIndicator = C or D). Ignores inactive records
"""
det_award_1 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain1uri1',
correction_delete_indicatr=None)
det_award_2 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain2uri1',
correction_delete_indicatr='C')
det_award_3 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique='ama2asta1fain1uri1',
correction_delete_indicatr='D')
pub_award_1 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain1uri2',
correction_delete_indicatr=None,
is_active=True)
pub_award_2 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta2fain1uri1',
correction_delete_indicatr=None,
is_active=True)
pub_award_3 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain2uri1',
correction_delete_indicatr=None,
is_active=True)
pub_award_4 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama2asta1fain1uri1',
correction_delete_indicatr=None,
is_active=True)
pub_award_5 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique='ama1asta1fain1uri1',
correction_delete_indicatr=None,
is_active=False)
errors = number_of_errors(_FILE,
database,
models=[
det_award_1, det_award_2, det_award_3,
pub_award_1, pub_award_2, pub_award_3,
pub_award_4, pub_award_5
])
assert errors == 0
def test_success_ignore_null_pafa(database):
""" Test that empty funding office codes aren't matching invalid office codes from the base record. """
office_1 = OfficeFactory(office_code='12345a', contract_funding_office=True,
financial_assistance_funding_office=False)
office_2 = OfficeFactory(office_code='12345b', contract_funding_office=False,
financial_assistance_funding_office=True)
# Base record has no funding office code, future records don't affect it
pub_award_1 = PublishedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='zyxwv_123',
action_date='20181018', award_modification_amendme='0',
is_active=True)
pub_award_2 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abc', unique_award_key='zyxwv_123',
action_date='20181019', award_modification_amendme='1',
is_active=True)
# Base record has an invalid code but new record has a funding office entered (ignore this rule)
pub_award_3 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abc', unique_award_key='abcd_123',
action_date='20181019', award_modification_amendme='0',
is_active=True)
# Base record with a valid office code (case insensitive)
pub_award_4 = PublishedAwardFinancialAssistanceFactory(funding_office_code='12345A', unique_award_key='1234_abc',
action_date='20181019', award_modification_amendme='0',
is_active=True)
# Earliest record inactive, newer record has valid entry
pub_award_5 = PublishedAwardFinancialAssistanceFactory(funding_office_code='abc', unique_award_key='4321_cba',
action_date='20181018', award_modification_amendme='0',
is_active=False)
pub_award_6 = PublishedAwardFinancialAssistanceFactory(funding_office_code='12345b', unique_award_key='4321_cba',
action_date='20181019', award_modification_amendme='1',
is_active=True)
# New entry for base award with no office code
det_award_1 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='zyxwv_123',
action_date='20181020', award_modification_amendme='2',
correction_delete_indicatr=None)
# New entry for base award with invalid code but entry has a funding office code
det_award_2 = DetachedAwardFinancialAssistanceFactory(funding_office_code='abd', unique_award_key='abcd_123',
action_date='20181020', award_modification_amendme='1',
correction_delete_indicatr=None)
# New entry for valid funding office
det_award_3 = DetachedAwardFinancialAssistanceFactory(funding_office_code=None, unique_award_key='1234_abc',
action_date='20181020', award_modification_amendme='1',
correction_delete_indicatr=None)
# Correction to base record (ignore)
det_award_4 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='abcd_123',
action_date='20181019', award_modification_amendme='0',
correction_delete_indicatr='C')
# New entry for earliest inactive
det_award_5 = DetachedAwardFinancialAssistanceFactory(funding_office_code='', unique_award_key='4321_cba',
action_date='20181020', award_modification_amendme='2',
correction_delete_indicatr=None)
errors = number_of_errors(_FILE, database, models=[office_1, office_2, pub_award_1, pub_award_2, pub_award_3,
pub_award_4, pub_award_5, pub_award_6, det_award_1, det_award_2,
det_award_3, det_award_4, det_award_5])
assert errors == 0
def test_failure(database):
""" The combination of FAIN, AwardModificationAmendmentNumber, URI, and AwardingSubTierAgencyCode must be unique
from currently published ones unless the record is a correction or deletion
(i.e., if CorrectionDeleteIndicator = C or D). """
det_award_1 = DetachedAwardFinancialAssistanceFactory(
afa_generated_unique="ama1asta1fain1uri1",
correction_delete_indicatr=None)
pub_award_1 = PublishedAwardFinancialAssistanceFactory(
afa_generated_unique="ama1asta1fain1uri1",
correction_delete_indicatr=None,
is_active=True)
errors = number_of_errors(_FILE,
database,
models=[det_award_1, pub_award_1])
assert errors == 1
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 initialize_db_values(db):
""" Initialize the values in the DB that can be used throughout the tests """
zip_code_1 = ZipsFactory(zip5='12345',
zip_last4='6789',
state_abbreviation='NY',
county_number='001',
congressional_district_no='01')
zip_code_2 = ZipsFactory(zip5='12345',
zip_last4='4321',
state_abbreviation='NY',
county_number='001',
congressional_district_no='02')
zip_code_3 = ZipsFactory(zip5='54321',
zip_last4='4321',
state_abbreviation='NY',
county_number='001',
congressional_district_no='05')
zip_code_4 = ZipsFactory(zip5='98765',
zip_last4='4321',
state_abbreviation='NY',
county_number='001',
congressional_district_no=None)
zip_city = ZipCityFactory(zip_code=zip_code_1.zip5, city_name='Test City')
zip_city_2 = ZipCityFactory(zip_code=zip_code_3.zip5,
city_name='Test City 2')
zip_city_3 = ZipCityFactory(zip_code=zip_code_4.zip5,
city_name='Test City 3')
city_code = CityCodeFactory(feature_name='Test City',
city_code='00001',
state_code='NY',
county_number=zip_code_1.county_number,
county_name='Test City County')
duns_1 = DunsFactory(awardee_or_recipient_uniqu='123456789',
ultimate_parent_unique_ide='234567890',
ultimate_parent_legal_enti='Parent 1')
duns_2a = DunsFactory(awardee_or_recipient_uniqu='234567890',
ultimate_parent_unique_ide='234567890',
ultimate_parent_legal_enti='Parent 2')
duns_2b = DunsFactory(awardee_or_recipient_uniqu='234567890',
ultimate_parent_unique_ide=None,
ultimate_parent_legal_enti=None)
duns_3 = DunsFactory(awardee_or_recipient_uniqu='345678901',
ultimate_parent_unique_ide=None,
ultimate_parent_legal_enti=None)
# record type 2 pafas
pafa_1 = PublishedAwardFinancialAssistanceFactory(
awarding_sub_tier_agency_c='1234',
fain='12345',
uri='123456',
action_date='04/28/2000',
funding_office_code=None,
awarding_office_code='033103',
is_active=True,
record_type=2,
award_modification_amendme='0')
pafa_2 = PublishedAwardFinancialAssistanceFactory(
awarding_sub_tier_agency_c='1234',
fain='123456',
uri='1234567',
action_date='04/28/2000',
funding_office_code='033103',
awarding_office_code=None,
is_active=True,
record_type=2,
award_modification_amendme=None)
# record type 1 pafas
pafa_3 = PublishedAwardFinancialAssistanceFactory(
awarding_sub_tier_agency_c='1234',
fain='54321',
uri='654321',
action_date='04/28/2000',
funding_office_code=None,
awarding_office_code='033103',
is_active=True,
record_type=1,
award_modification_amendme=None)
pafa_4 = PublishedAwardFinancialAssistanceFactory(
awarding_sub_tier_agency_c='1234',
fain='654321',
uri='7654321',
action_date='04/28/2000',
funding_office_code='033103',
awarding_office_code=None,
is_active=True,
record_type=1,
award_modification_amendme='0')
# record type 1 base pafa with invalid office codes
pafa_5 = PublishedAwardFinancialAssistanceFactory(
awarding_sub_tier_agency_c='1234',
fain='abcd',
uri='efg',
action_date='04/28/2000',
funding_office_code='123456',
awarding_office_code='123456',
is_active=True,
record_type=1,
award_modification_amendme='0')
# record type 1 base pafa with valid office codes but they aren't grant or funding type
pafa_6 = PublishedAwardFinancialAssistanceFactory(
awarding_sub_tier_agency_c='1234',
fain='efg',
uri='abcd',
action_date='04/28/2000',
funding_office_code='654321',
awarding_office_code='654321',
is_active=True,
record_type=1,
award_modification_amendme='0')
db.session.add_all([
zip_code_1, zip_code_2, zip_code_3, zip_code_4, zip_city, zip_city_2,
zip_city_3, city_code, duns_1, duns_2a, duns_2b, duns_3, pafa_1,
pafa_2, pafa_3, pafa_4, pafa_5, pafa_6
])
db.session.commit()
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)
sub2 = SubmissionFactory(submission_id=2)
sub3 = SubmissionFactory(submission_id=3)
d2_non_pop_subtier = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_pop_subtier_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_non_pop_subtier.fain.replace('-', ''),
federal_agency_id='1234',
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_pop_subtier = FSRSSubgrantFactory(
parent=grant_non_pop_subtier,
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_non_null_subtier = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_null_subtier_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_non_null_subtier.fain.replace('-', ''),
federal_agency_id=None,
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_null_subtier = FSRSSubgrantFactory(
parent=grant_non_null_subtier,
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_non_other = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_other_2 = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_other_dup = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_other_dup_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_non_other.fain.replace('-', ''),
federal_agency_id=None,
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_other = FSRSSubgrantFactory(
parent=grant_non_other,
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,
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')
d2_agg_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_agg.fain.replace('-', ''),
federal_agency_id='1234',
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',
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,
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',
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,
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_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,
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_other, grant_non_other, sub_grant_non_other,
parent_duns, duns, dom_country, int_country, grant_created_at,
grant_updated_at) is False
assert compare_grant_results(
grants_results[2], d2_non_null_subtier, grant_non_null_subtier,
sub_grant_non_null_subtier, parent_duns, duns, dom_country,
int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(
grants_results[3], d2_non_pop_subtier, grant_non_pop_subtier,
sub_grant_non_pop_subtier, 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, d1_awd_2, d1_idv, d1_idv_2, d2_non_null_subtier,
d2_non_null_subtier_2, d2_non_pop_subtier, d2_non_pop_subtier_2,
d2_non_other, d2_non_other_2, d2_non_other_dup, d2_non_other_dup_2,
d2_agg, d2_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,
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 False
assert compare_grant_results(
grants_results[1], d2_non_null_subtier, grant_non_null_subtier,
sub_grant_non_null_subtier, parent_duns, duns, dom_country,
int_country, grant_created_at, grant_updated_at) is False
assert compare_grant_results(
grants_results[2], d2_non_other, grant_non_other, sub_grant_non_other,
parent_duns, duns, dom_country, int_country, grant_created_at,
grant_updated_at) is False
assert compare_grant_results(
grants_results[3], d2_non_pop_subtier, grant_non_pop_subtier,
sub_grant_non_pop_subtier, 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-aggregate award with federal_agency_id/awarding_sub_tier_agency_c populated
d2_non_pop_subtier = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_pop_subtier_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_non_pop_subtier.fain.replace('-', ''),
federal_agency_id='1234',
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_pop_subtier = FSRSSubgrantFactory(
parent=grant_non_pop_subtier,
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 Non-aggregate award with federal_agency_id NULL
d2_non_null_sub = PublishedAwardFinancialAssistanceFactory(
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')
d2_non_null_sub_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_non_null_sub.fain.replace('-', ''),
federal_agency_id=None,
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_null_sub = FSRSSubgrantFactory(
parent=grant_non_null_sub,
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 Aggregate award
d2_agg = PublishedAwardFinancialAssistanceFactory(
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')
d2_agg_2 = PublishedAwardFinancialAssistanceFactory(
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=d2_agg.fain.replace('-', ''),
federal_agency_id='1234',
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_pop_subtier, d2_non_pop_subtier_2, grant_non_pop_subtier,
sub_grant_non_pop_subtier, d2_non_null_sub, d2_non_null_sub_2,
grant_non_null_sub, sub_grant_non_null_sub, d2_agg, d2_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], d2_agg, grant_agg,
sub_grant_agg, parent_duns, duns, 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], d2_non_null_sub,
grant_non_null_sub, sub_grant_non_null_sub,
parent_duns, duns, dom_country, int_country,
created_at, updated_at) is False
assert compare_grant_results(
grants_results[2], d2_non_pop_subtier, grant_non_pop_subtier,
sub_grant_non_pop_subtier, parent_duns, duns, dom_country, int_country,
created_at, updated_at) is True
