def test_pubished_date_failure(database):
""" Test failure for when provided, AwardeeOrRecipientUEI must be registered (not necessarily active) in SAM,
unless the ActionDate is before October 1, 2010.
"""
pub_fabs_1 = PublishedFABSFactory(unique_award_key='active_key',
action_date='20220404',
is_active=True)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='inactive_key',
action_date='20091001',
is_active=False)
models = [pub_fabs_1, pub_fabs_2]
# new records that may or may not be related to older awards
recipient = SAMRecipient(uei='1111111111111E')
fabs_1 = FABSFactory(uei='12345',
assistance_type='02',
action_date='10/02/2010',
correction_delete_indicatr='',
unique_award_key='active_key')
fabs_2 = FABSFactory(uei='12345',
assistance_type='06',
action_date='04/05/2022',
correction_delete_indicatr=None,
unique_award_key='inactive_key')
models += [recipient, fabs_1, fabs_2]
errors = number_of_errors(_FILE, database, models=models)
assert errors == 2
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 deletion (i.e., if
CorrectionDeleteIndicator = D). Ignore all other CorrectionDeleteIndicators in this rule.
"""
fabs_1 = FABSFactory(afa_generated_unique='ama1asta1fain2uri1',
correction_delete_indicatr='D')
# Test that capitalization differences don't affect the error
fabs_2 = FABSFactory(afa_generated_unique='amA1astA1fain2uRi1',
correction_delete_indicatr='D')
pub_fabs_1 = PublishedFABSFactory(
afa_generated_unique='ama1asta1fain1uri1',
correction_delete_indicatr=None,
is_active=True)
pub_fabs_2 = PublishedFABSFactory(
afa_generated_unique='ama1asta1fAin2uri1',
correction_delete_indicatr=None,
is_active=False)
errors = number_of_errors(_FILE,
database,
models=[fabs_1, fabs_2, pub_fabs_1, pub_fabs_2])
assert errors == 2
def test_success(database):
""" ActionType should be B, C, or D for transactions that modify existing awards.
For aggregate (RecordType = 1) record transactions, we consider a record a modification if its combination of
URI + AwardingSubTierAgencyCode matches an existing published FABS record of the same RecordType.
For non-aggregate (RecordType = 2 or 3) transactions, we consider a record a modification if its combination
of FAIN + AwardingSubTierCode matches those of an existing published non-aggregate FABS record
(RecordType = 2 or 3) of the same RecordType.
"""
fabs_1 = FABSFactory(unique_award_key='unique1', action_type='B', correction_delete_indicatr=None)
fabs_2 = FABSFactory(unique_award_key='unique2', action_type='d', correction_delete_indicatr='')
# Ignore delete/correction record
fabs_3 = FABSFactory(unique_award_key='unique2', action_type='A', correction_delete_indicatr='D')
fabs_4 = FABSFactory(unique_award_key='unique2', action_type='A', correction_delete_indicatr='c')
# This is an inactive award so it will be ignored
fabs_5 = FABSFactory(unique_award_key='unique3', action_type='E', correction_delete_indicatr=None)
# This record doesn't have a matching published award at all
fabs_6 = FABSFactory(unique_award_key='unique4', action_type='A', correction_delete_indicatr='')
pub_fabs_1 = PublishedFABSFactory(unique_award_key='unique3', is_active=False)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='unique1', is_active=True)
pub_fabs_3 = PublishedFABSFactory(unique_award_key='unique2', is_active=True)
errors = number_of_errors(_FILE, database, models=[fabs_1, fabs_2, fabs_3, fabs_4, fabs_5, fabs_6, pub_fabs_1,
pub_fabs_2, pub_fabs_3])
assert errors == 0
def test_success(database):
""" ActionType should be "A" for the initial transaction of a new, non-aggregate award (RecordType = 2 or 3) and
“A” or “E” for a new aggregate award (RecordType = 1). An aggregate record transaction is considered the
initial transaction of a new award if it contains a unique combination of URI + AwardingSubTierAgencyCode when
compared to currently published FABS records of the same RecordType. A non-aggregate (RecordType = 2 or 3)
transaction is considered the initial transaction of a new award if it contains a unique combination of FAIN +
AwardingSubTierAgencyCode when compared to currently published non-aggregate FABS records (RecordType = 2 or 3)
of the same RecordType.
"""
fabs_1 = FABSFactory(unique_award_key='unique1',
action_type='A',
record_type=1,
correction_delete_indicatr=None)
fabs_2 = FABSFactory(unique_award_key='unique1',
action_type='e',
record_type=1,
correction_delete_indicatr='')
fabs_3 = FABSFactory(unique_award_key='unique2',
action_type='a',
record_type=3,
correction_delete_indicatr='')
# Ignore delete/correction record
fabs_4 = FABSFactory(unique_award_key='unique1',
action_type='C',
record_type=3,
correction_delete_indicatr='D')
fabs_5 = FABSFactory(unique_award_key='unique1',
action_type='C',
record_type=3,
correction_delete_indicatr='c')
# This is an active award so it will be ignored
fabs_6 = FABSFactory(unique_award_key='unique3',
action_type='d',
record_type=2,
correction_delete_indicatr=None)
fabs_7 = FABSFactory(unique_award_key='unique3',
action_type='e',
record_type=2,
correction_delete_indicatr=None)
pub_fabs_1 = PublishedFABSFactory(unique_award_key='unique2',
is_active=False)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='unique3',
is_active=True)
errors = number_of_errors(_FILE,
database,
models=[
fabs_1, fabs_2, fabs_3, fabs_4, fabs_5,
fabs_6, fabs_7, pub_fabs_1, pub_fabs_2
])
assert errors == 0
def test_failure(database):
""" Test failure for AwardeeOrRecipientUEI is required where ActionDate is after October 1, 2010, unless the record
is an aggregate or PII-redacted non-aggregate record (RecordType = 1 or 3) or the recipient is an individual
(BusinessTypes includes 'P').
"""
# Note: for FABS 31.2.1, we're setting assistance types to NOT 06, 07, 08, 09, 10, or 11 or having the base
# actiondate NOT be less than April 4, 2022. This rule will not trigger if those *don't* apply.
# FABS 31.2.2 *will* trigger when these apply.
pub_fabs_1 = PublishedFABSFactory(unique_award_key='before_key',
action_date='20091001',
is_active=True)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='after_key',
action_date='20230404',
is_active=True)
pub_fabs_3 = PublishedFABSFactory(unique_award_key='inactive_key',
action_date='20091001',
is_active=False)
models = [pub_fabs_1, pub_fabs_2, pub_fabs_3]
fabs_1 = FABSFactory(record_type=2,
business_types='AbC',
uei=None,
action_date='10/02/2010',
assistance_type='02',
correction_delete_indicatr='',
unique_award_key='before_key')
fabs_2 = FABSFactory(record_type=5,
business_types='aBc',
uei=None,
action_date='10/02/2010',
assistance_type='06',
correction_delete_indicatr='C',
unique_award_key='after_key')
fabs_3 = FABSFactory(record_type=4,
business_types='AbC',
uei='',
action_date='04/05/2023',
assistance_type='07',
correction_delete_indicatr='c',
unique_award_key='new_key')
fabs_4 = FABSFactory(record_type=5,
business_types='aBc',
uei='',
action_date='04/05/2023',
assistance_type='08',
correction_delete_indicatr=None,
unique_award_key='inactive_key')
models += [fabs_1, fabs_2, fabs_3, fabs_4]
errors = number_of_errors(_FILE, database, models=models)
assert errors == 4
def test_failure(database):
""" Fail ActionType should be "A" for the initial transaction of a new, non-aggregate award (RecordType = 2 or 3)
and “A” or “E” for a new aggregate award (RecordType = 1). An aggregate record transaction is considered the initial
transaction of a new award if it contains a unique combination of URI + AwardingSubTierAgencyCode when compared to
currently published FABS records of the same RecordType. A non-aggregate (RecordType = 2 or 3) transaction is
considered the initial transaction of a new award if it contains a unique combination of FAIN +
AwardingSubTierAgencyCode when compared to currently published non-aggregate FABS records (RecordType = 2 or 3) of
the same RecordType.
"""
fabs_1 = FABSFactory(unique_award_key='unique1',
action_type='b',
record_type=1,
correction_delete_indicatr=None)
# E is only valid for record type 1
fabs_2 = FABSFactory(unique_award_key='unique2',
action_type='E',
record_type=2,
correction_delete_indicatr='')
pub_fabs_1 = PublishedFABSFactory(unique_award_key='unique2',
is_active=False)
errors = number_of_errors(_FILE,
database,
models=[fabs_1, fabs_2, pub_fabs_1])
assert errors == 2
def test_pubished_date_success(database):
""" Test success for when provided, AwardeeOrRecipientUEI must be registered (not necessarily active) in SAM,
unless the ActionDate is before October 1, 2010.
"""
pub_fabs_1 = PublishedFABSFactory(unique_award_key='active_key',
action_date='20091001',
is_active=True)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='inactive_key',
action_date='20091001',
is_active=False)
models = [pub_fabs_1, pub_fabs_2]
# new records that may or may not be related to older awards
recipient = SAMRecipient(uei='22222222222E')
fabs_1 = FABSFactory(uei=recipient.uei,
assistance_type='02',
action_date='10/02/2010',
correction_delete_indicatr='',
unique_award_key='active_key')
fabs_2 = FABSFactory(uei=recipient.uei.lower(),
assistance_type='02',
action_date='10/02/2010',
correction_delete_indicatr='c',
unique_award_key='active_key')
fabs_3 = FABSFactory(uei=None,
assistance_type='01',
action_date='10/02/2010',
correction_delete_indicatr='c',
unique_award_key='active_key')
# Before October 1, 2010
fabs_4 = FABSFactory(uei='12345',
assistance_type='02',
action_date='09/30/2010',
correction_delete_indicatr='C',
unique_award_key='new_key')
# Ignore correction delete indicator of D
fabs_5 = FABSFactory(uei='12345',
assistance_type='01',
action_date='10/02/2010',
correction_delete_indicatr='d',
unique_award_key='inactive_key')
models += [recipient, fabs_1, fabs_2, fabs_3, fabs_4, fabs_5]
errors = number_of_errors(_FILE, database, models=models)
assert errors == 0
def test_success(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.
"""
fabs_1 = FABSFactory(afa_generated_unique='ama1asta1fain1uri1', correction_delete_indicatr=None)
fabs_2 = FABSFactory(afa_generated_unique='ama1asta1fain2uri1', correction_delete_indicatr='C')
fabs_3 = FABSFactory(afa_generated_unique='ama2asta1fain1uri1', correction_delete_indicatr='D')
pub_fabs_1 = PublishedFABSFactory(afa_generated_unique='ama1asta1fain2uri1', correction_delete_indicatr=None,
is_active=True)
pub_fabs_2 = PublishedFABSFactory(afa_generated_unique='ama1asta1fain1uri1', correction_delete_indicatr=None,
is_active=True)
pub_fabs_3 = PublishedFABSFactory(afa_generated_unique='ama2asta1fain1uri1', correction_delete_indicatr=None,
is_active=False)
errors = number_of_errors(_FILE, database, models=[fabs_1, fabs_2, fabs_3, pub_fabs_1, pub_fabs_2, pub_fabs_3])
assert errors == 0
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=False,
financial_assistance_funding_office=True)
office_2 = OfficeFactory(office_code='abcd', contract_funding_office=True,
financial_assistance_funding_office=False)
# Invalid code in record
pub_fabs_1 = PublishedFABSFactory(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_fabs_2 = PublishedFABSFactory(funding_office_code='12345a', unique_award_key='4321_cba', action_date='20181018',
award_modification_amendme='0', is_active=False)
pub_fabs_3 = PublishedFABSFactory(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 assistance office
pub_fabs_4 = PublishedFABSFactory(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_fabs_5 = PublishedFABSFactory(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
fabs_1 = FABSFactory(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
fabs_2 = FABSFactory(funding_office_code='', unique_award_key='zyxwv_123', action_date='20181020',
award_modification_amendme=None, correction_delete_indicatr=None)
# New entry for earliest inactive
fabs_3 = FABSFactory(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 assistance code
fabs_4 = FABSFactory(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
fabs_5 = FABSFactory(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_fabs_1, pub_fabs_2, pub_fabs_3,
pub_fabs_4, pub_fabs_5, fabs_1, fabs_2, fabs_3, fabs_4, fabs_5])
assert errors == 5
def test_failure(database):
""" Fail ActionType should be B, C, or D for transactions that modify existing awards. For aggregate
(RecordType = 1) record transactions, we consider a record a modification if its combination of URI +
AwardingSubTierAgencyCode matches an existing published FABS record of the same RecordType.
For non-aggregate (RecordType = 2 or 3) transactions, we consider a record a modification if its combination
of FAIN + AwardingSubTierCode matches those of an existing published non-aggregate FABS record
(RecordType = 2 or 3) of the same RecordType.
"""
fabs_1 = FABSFactory(unique_award_key='unique1', action_type='a', correction_delete_indicatr=None)
fabs_2 = FABSFactory(unique_award_key='unique1', action_type='E', correction_delete_indicatr='')
pub_fabs_1 = PublishedFABSFactory(unique_award_key='unique1', is_active=True)
errors = number_of_errors(_FILE, database, models=[fabs_1, fabs_2, pub_fabs_1])
assert errors == 2
def test_failure(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).
"""
fabs_1 = FABSFactory(afa_generated_unique='ama1asta1fain1uri1',
correction_delete_indicatr=None)
# Test that capitalization differences don't affect the error
fabs_2 = FABSFactory(afa_generated_unique='amA1asta1faiN1uri1',
correction_delete_indicatr=None)
pub_fabs_1 = PublishedFABSFactory(
afa_generated_unique='ama1asTa1fain1uri1',
correction_delete_indicatr=None,
is_active=True)
errors = number_of_errors(_FILE,
database,
models=[fabs_1, fabs_2, pub_fabs_1])
assert errors == 2
def test_success_ignore_null_pub_fabs(database):
""" Test that empty funding office codes aren't matching invalid office codes from the base record. """
office_1 = 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_fabs_1 = PublishedFABSFactory(funding_office_code='', unique_award_key='zyxwv_123', action_date='20181018',
award_modification_amendme='0', is_active=True)
pub_fabs_2 = PublishedFABSFactory(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_fabs_3 = PublishedFABSFactory(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_fabs_4 = PublishedFABSFactory(funding_office_code='12345B', unique_award_key='1234_abc', action_date='20181019',
award_modification_amendme='0', is_active=True)
# Earliest record inactive, newer record has valid entry, inactive date matching active doesn't mess it up
pub_fabs_5 = PublishedFABSFactory(funding_office_code='abc', unique_award_key='4321_cba', action_date='20181018',
award_modification_amendme='0', is_active=False)
pub_fabs_6 = PublishedFABSFactory(funding_office_code='abc', unique_award_key='4321_cba', action_date='20181019',
award_modification_amendme='1', is_active=False)
pub_fabs_7 = PublishedFABSFactory(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
fabs_1 = FABSFactory(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
fabs_2 = FABSFactory(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
fabs_3 = FABSFactory(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)
fabs_4 = FABSFactory(funding_office_code='', unique_award_key='abcd_123', action_date='20181019',
award_modification_amendme='0', correction_delete_indicatr='C')
# New entry for earliest inactive
fabs_5 = FABSFactory(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, pub_fabs_1, pub_fabs_2, pub_fabs_3, pub_fabs_4,
pub_fabs_5, pub_fabs_6, pub_fabs_7, fabs_1, fabs_2, fabs_3,
fabs_4, fabs_5])
assert errors == 0
def test_success(database):
""" Test success for AwardeeOrRecipientUEI is required where ActionDate is after October 1, 2010, unless the record
is an aggregate or PII-redacted non-aggregate record (RecordType = 1 or 3) or the recipient is an individual
(BusinessTypes includes 'P').
"""
# Note: for FABS 31.2.1, we're setting assistance types to NOT 06, 07, 08, 09, 10, or 11 or having the base
# actiondate NOT be less than April 4, 2023. This rule will not trigger if those *don't* apply.
# FABS 31.2.2 *will* trigger when these apply.
pub_fabs_1 = PublishedFABSFactory(unique_award_key='before_key',
action_date='20091001',
is_active=True)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='after_key',
action_date='20230404',
is_active=True)
pub_fabs_3 = PublishedFABSFactory(unique_award_key='inactive_key',
action_date='20091001',
is_active=False)
models = [pub_fabs_1, pub_fabs_2, pub_fabs_3]
# new records that may or may not be related to older awards
fabs_1 = FABSFactory(record_type=2,
business_types='AbC',
uei='test',
action_date='10/02/2010',
assistance_type='03',
correction_delete_indicatr='',
unique_award_key='after_key')
fabs_2 = FABSFactory(record_type=5,
business_types='aBc',
uei='test',
action_date='10/02/2010',
assistance_type='02',
correction_delete_indicatr='c',
unique_award_key='after_key')
# Ignored for dates before Oct 1 2010
fabs_3 = FABSFactory(record_type=2,
business_types='AbC',
uei=None,
action_date='09/01/2010',
assistance_type='02',
correction_delete_indicatr='',
unique_award_key='after_key')
# Ignored for record type 1/3
fabs_4 = FABSFactory(record_type=1,
business_types='AbC',
uei=None,
action_date='10/01/2010',
assistance_type='03',
correction_delete_indicatr='',
unique_award_key='after_key')
# Ignored for listed assistance types
fabs_5 = FABSFactory(record_type=1,
business_types='AbC',
uei='',
action_date='09/01/2010',
assistance_type='06',
correction_delete_indicatr='',
unique_award_key='after_key')
# Ignored when business types include P
fabs_6 = FABSFactory(record_type=5,
business_types='aBp',
uei=None,
action_date='10/02/2010',
assistance_type='05',
correction_delete_indicatr='',
unique_award_key='after_key')
# Ignore correction delete indicator of D
fabs_7 = FABSFactory(record_type=2,
business_types='AbC',
uei=None,
action_date='10/02/2010',
assistance_type='08',
correction_delete_indicatr='d',
unique_award_key='new_key')
# Ensuring that this rule gets ignored when the base actiondate case does apply
fabs_8 = FABSFactory(record_type=2,
business_types='AbC',
uei=None,
action_date='10/02/2010',
assistance_type='06',
correction_delete_indicatr='',
unique_award_key='before_key')
fabs_9 = FABSFactory(record_type=2,
business_types='AbC',
uei='',
action_date='10/02/2010',
assistance_type='06',
correction_delete_indicatr='',
unique_award_key='new_key')
fabs_10 = FABSFactory(record_type=2,
business_types='AbC',
uei='',
action_date='10/02/2010',
assistance_type='07',
correction_delete_indicatr='',
unique_award_key='inactive_key')
models += [
fabs_1, fabs_2, fabs_3, fabs_4, fabs_5, fabs_6, fabs_7, fabs_8, fabs_9,
fabs_10
]
errors = number_of_errors(_FILE, database, models=models)
assert errors == 0
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_failure(database):
""" Test failure for AwardeeOrRecipientUEI is required where ActionDate is after October 1, 2010, unless the record
is an aggregate or PII-redacted non-aggregate record (RecordType = 1 or 3) or the recipient is an individual
(BusinessTypes includes 'P'). For AssistanceType 06, 07, 08, 09, 10, or 11, if the base award (the earliest
record with the same unique award key) has an ActionDate prior to October 1, 2022, this will produce a warning
rather than a fatal error.
"""
# Note: for FABS 31.2.2, we're setting assistance types to 06, 07, 08, 09, 10, or 11 and having the base
# actiondate be less than October 1, 2022. This rule will not trigger if those *do* apply.
# FABS 31.2.1 *will not* trigger when these apply.
pub_fabs_1 = PublishedFABSFactory(unique_award_key='before_key',
action_date='20091001',
is_active=True)
pub_fabs_2 = PublishedFABSFactory(unique_award_key='after_key',
action_date='20230404',
is_active=True)
pub_fabs_3 = PublishedFABSFactory(unique_award_key='inactive_key',
action_date='20091001',
is_active=False)
models = [pub_fabs_1, pub_fabs_2, pub_fabs_3]
fabs_1 = FABSFactory(record_type=2,
business_types='AbC',
uei=None,
action_date='10/02/2010',
assistance_type='06',
correction_delete_indicatr='',
unique_award_key='before_key')
fabs_2 = FABSFactory(record_type=5,
business_types='aBc',
uei=None,
action_date='10/02/2010',
assistance_type='07',
correction_delete_indicatr='C',
unique_award_key='before_key')
fabs_3 = FABSFactory(record_type=4,
business_types='AbC',
uei='',
action_date='10/02/2010',
assistance_type='08',
correction_delete_indicatr='c',
unique_award_key='before_key')
fabs_4 = FABSFactory(record_type=5,
business_types='aBc',
uei='',
action_date='10/02/2010',
assistance_type='09',
correction_delete_indicatr=None,
unique_award_key='before_key')
fabs_5 = FABSFactory(record_type=2,
business_types='AbC',
uei='',
action_date='10/02/2010',
assistance_type='06',
correction_delete_indicatr='',
unique_award_key='inactive_key')
fabs_6 = FABSFactory(record_type=2,
business_types='AbC',
uei='',
action_date='10/02/2010',
assistance_type='06',
correction_delete_indicatr='',
unique_award_key='new_key')
models += [fabs_1, fabs_2, fabs_3, fabs_4, fabs_5, fabs_6]
errors = number_of_errors(_FILE, database, models=models)
assert errors == 6