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))