def reference_data(sess):
parent_duns = DunsFactory(awardee_or_recipient_uniqu='987654321', legal_business_name='TEST PARENT DUNS')
duns = DunsFactory(awardee_or_recipient_uniqu='123456789', legal_business_name='TEST DUNS',
business_types_codes=['A', 'B', 'C'])
dom_country = CountryCodeFactory(country_code='USA', country_name='UNITED STATES')
int_country = CountryCodeFactory(country_code='INT', country_name='INTERNATIONAL')
sess.add_all([parent_duns, duns, dom_country, int_country])
return parent_duns, duns, dom_country, int_country
def test_generate_file_e_sql(database, monkeypatch):
""" test_generate_file_e_sql should provide the query representing E file data related to a submission """
# Setup - create submission, awards, subawards
sess = database.session
sub1 = SubmissionFactory(submission_id=1)
sub2 = SubmissionFactory(submission_id=2)
d1_show = AwardProcurementFactory(submission_id=sub1.submission_id,
awardee_or_recipient_uniqu='000000000')
d2_show = AwardFinancialAssistanceFactory(
submission_id=sub1.submission_id,
awardee_or_recipient_uniqu='111111111')
d1_hide = AwardProcurementFactory(submission_id=sub2.submission_id,
awardee_or_recipient_uniqu='222222222')
d2_hide = AwardFinancialAssistanceFactory(
submission_id=sub2.submission_id,
awardee_or_recipient_uniqu='333333333')
duns_show = [
DunsFactory(awardee_or_recipient_uniqu=(str(i) * 9))
for i in range(0, 2)
]
duns_hide = [
DunsFactory(awardee_or_recipient_uniqu=(str(i) * 9))
for i in range(2, 4)
]
duns_s = duns_show + duns_hide
sess.add_all([sub1, sub2, d1_hide, d1_show, d2_hide, d2_show] + duns_s)
sess.commit()
# Gather the sql
file_e_query = fileE_F.generate_file_e_sql(sub1.submission_id)
# Get the records
file_e_records = sess.execute(file_e_query)
file_e_cols = file_e_records.keys()
file_e_value_sets = file_e_records.fetchall()
file_e_results = [
OrderedDict(list(zip(file_e_cols, file_e_value_set)))
for file_e_value_set in file_e_value_sets
]
# Expected Results
expected_file_e_results = [
replicate_file_e_results(duns) for duns in duns_show
]
assert file_e_results == expected_file_e_results
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)
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
])
db.session.commit()
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_generate_e_file(mock_broker_config_paths, database):
""" Verify that generate_e_file makes an appropriate query (matching both D1 and D2 entries) and creates
a file matching the expected DUNS
"""
# Generate several file D1 entries, largely with the same submission_id, and with two overlapping DUNS. Generate
# several D2 entries with the same submission_id as well
sess = database.session
sub = SubmissionFactory()
sub_2 = SubmissionFactory()
sess.add_all([sub, sub_2])
sess.commit()
file_path = str(mock_broker_config_paths['broker_files'].join('e_test1'))
job = JobFactory(job_status_id=JOB_STATUS_DICT['running'],
job_type_id=JOB_TYPE_DICT['file_upload'],
file_type_id=FILE_TYPE_DICT['executive_compensation'],
filename=file_path,
original_filename='e_test1',
submission_id=sub.submission_id)
database.session.add(job)
database.session.commit()
model = AwardProcurementFactory(submission_id=sub.submission_id)
aps = [
AwardProcurementFactory(submission_id=sub.submission_id)
for _ in range(4)
]
afas = [
AwardFinancialAssistanceFactory(submission_id=sub.submission_id)
for _ in range(5)
]
same_duns = AwardProcurementFactory(
submission_id=sub.submission_id,
awardee_or_recipient_uniqu=model.awardee_or_recipient_uniqu)
unrelated = AwardProcurementFactory(submission_id=sub_2.submission_id)
duns_list = [
DunsFactory(
awardee_or_recipient_uniqu=model.awardee_or_recipient_uniqu)
]
duns_list.extend([
DunsFactory(awardee_or_recipient_uniqu=ap.awardee_or_recipient_uniqu)
for ap in aps
])
duns_list.extend([
DunsFactory(awardee_or_recipient_uniqu=afa.awardee_or_recipient_uniqu)
for afa in afas
])
sess.add_all(aps + afas + duns_list + [model, same_duns, unrelated])
sess.commit()
file_gen_manager = FileGenerationManager(database.session,
CONFIG_BROKER['local'],
job=job)
file_gen_manager.generate_file()
# check headers
file_rows = read_file_rows(file_path)
assert file_rows[0] == [
'AwardeeOrRecipientUniqueIdentifier',
'AwardeeOrRecipientLegalEntityName', 'UltimateParentUniqueIdentifier',
'UltimateParentLegalEntityName', 'HighCompOfficer1FullName',
'HighCompOfficer1Amount', 'HighCompOfficer2FullName',
'HighCompOfficer2Amount', 'HighCompOfficer3FullName',
'HighCompOfficer3Amount', 'HighCompOfficer4FullName',
'HighCompOfficer4Amount', 'HighCompOfficer5FullName',
'HighCompOfficer5Amount'
]
# Check listed DUNS
expected = [[
duns.awardee_or_recipient_uniqu, duns.legal_business_name,
duns.ultimate_parent_unique_ide, duns.ultimate_parent_legal_enti,
duns.high_comp_officer1_full_na, duns.high_comp_officer1_amount,
duns.high_comp_officer2_full_na, duns.high_comp_officer2_amount,
duns.high_comp_officer3_full_na, duns.high_comp_officer3_amount,
duns.high_comp_officer4_full_na, duns.high_comp_officer4_amount,
duns.high_comp_officer5_full_na, duns.high_comp_officer5_amount
] for duns in duns_list]
received = [file_row for file_row in file_rows[1:]]
assert sorted(received) == list(sorted(expected))