def create_nested_object(values): """ Makes sure keys provided are valid """ validate_location_keys(values) nested_locations = {} for v in values: upper_case_dict_values(v) city = v.get("city") country = v.get("country") county = v.get("county") district = v.get("district") state = v.get("state") zip = v.get("zip") # First level in location filtering in country # All location requests must have a country otherwise there will be a key error if nested_locations.get(country) is None: nested_locations[country] = {} # Initialize the list if zip and not nested_locations[country].get("zip"): nested_locations[country]["zip"] = [] if city and not nested_locations[country].get("city"): nested_locations[country]["city"] = [] # Second level of filtering is zip and state # Requests must have a country+zip or country+state combination if zip: # Appending zips so we don't overwrite nested_locations[country]["zip"].append(zip) # If we have a state, add it to the list if state and nested_locations[country].get(state) is None: nested_locations[country][state] = { "county": [], "district": [], "city": [] } # Based on previous checks, there will always be a state if either of these exist if county: nested_locations[country][state]["county"].extend( get_fields_list("county", county)) if district: nested_locations[country][state]["district"].extend( get_fields_list("district", district)) if city and state: nested_locations[country][state]["city"].append(city) elif city: nested_locations[country]["city"].append(city) return nested_locations
def _save_file_c_rows(certified_award_financial, total_rows, start_time, skipped_tas, submission_attributes, reverse): save_manager = BulkCreateManager(FinancialAccountsByAwards) for index, row in enumerate(certified_award_financial, 1): if not (index % 1000): logger.info(f"C File Load: Loading row {index:,} of {total_rows:,} ({datetime.now() - start_time})") upper_case_dict_values(row) # Check and see if there is an entry for this TAS treasury_account, tas_rendering_label = get_treasury_appropriation_account_tas_lookup(row.get("tas_id")) if treasury_account is None: update_skipped_tas(row, tas_rendering_label, skipped_tas) continue # Find a matching transaction record, so we can use its subtier agency information to match to (or create) an # Award record. # Find the award that this award transaction belongs to. If it doesn't exist, create it. filters = {} if row.get("piid"): filters["piid"] = row.get("piid") filters["parent_piid"] = row.get("parent_award_id") else: if row.get("fain") and not row.get("uri"): filters["fain"] = row.get("fain") elif row.get("uri") and not row.get("fain"): filters["uri"] = row.get("uri") else: filters["fain"] = row.get("fain") filters["uri"] = row.get("uri") award_financial_data = FinancialAccountsByAwards() value_map_faba = { "submission": submission_attributes, "reporting_period_start": submission_attributes.reporting_period_start, "reporting_period_end": submission_attributes.reporting_period_end, "treasury_account": treasury_account, "object_class": row.get("object_class"), "program_activity": row.get("program_activity"), "disaster_emergency_fund": get_disaster_emergency_fund(row), } save_manager.append( load_data_into_model(award_financial_data, row, value_map=value_map_faba, save=False, reverse=reverse) ) save_manager.save_stragglers()
def _save_file_c_rows(certified_award_financial, total_rows, start_time, skipped_tas, submission_attributes, reverse): save_manager = BulkCreateManager(FinancialAccountsByAwards) for index, row in enumerate(certified_award_financial, 1): if not (index % 1000): logger.info( f"C File Load: Loading row {index:,} of {total_rows:,} ({datetime.now() - start_time})" ) upper_case_dict_values(row) # Check and see if there is an entry for this TAS treasury_account, tas_rendering_label = get_treasury_appropriation_account_tas_lookup( row.get("tas_id")) if treasury_account is None: update_skipped_tas(row, tas_rendering_label, skipped_tas) continue award_financial_data = FinancialAccountsByAwards() value_map_faba = { "submission": submission_attributes, "reporting_period_start": submission_attributes.reporting_period_start, "reporting_period_end": submission_attributes.reporting_period_end, "treasury_account": treasury_account, "object_class": row.get("object_class"), "program_activity": row.get("program_activity"), "disaster_emergency_fund": get_disaster_emergency_fund(row), "distinct_award_key": create_distinct_award_key(row), } save_manager.append( load_data_into_model(award_financial_data, row, value_map=value_map_faba, save=False, reverse=reverse)) save_manager.save_stragglers()
def load_file_c(submission_attributes, db_cursor, award_financial_frame): """ Process and load file C broker data. Note: this should run AFTER the D1 and D2 files are loaded because we try to join to those records to retrieve some additional information about the awarding sub-tier agency. """ # this matches the file b reverse directive, but am repeating it here to ensure that we don't overwrite it as we # change up the order of file loading if not award_financial_frame.size: logger.warning("No File C (award financial) data found, skipping...") return reverse = re.compile(r"(_(cpe|fyb)$)|^transaction_obligated_amount$") # dictionary to capture TAS that were skipped and some metadata # tas = top-level key # count = number of rows skipped # rows = row numbers skipped, corresponding to the original row numbers in the file that was submitted skipped_tas = {} award_financial_frame["object_class"] = award_financial_frame.apply( get_object_class_row, axis=1) award_financial_frame["program_activity"] = award_financial_frame.apply( get_or_create_program_activity, axis=1, submission_attributes=submission_attributes) total_rows = award_financial_frame.shape[0] start_time = datetime.now() awards_touched = [] # format award_financial_frame award_financial_frame = award_financial_frame.replace({np.nan: None}) for index, row in enumerate( award_financial_frame.to_dict(orient="records"), 1): if not (index % 100): logger.info("C File Load: Loading row {} of {} ({})".format( str(index), str(total_rows), datetime.now() - start_time)) upper_case_dict_values(row) # Check and see if there is an entry for this TAS treasury_account, tas_rendering_label = get_treasury_appropriation_account_tas_lookup( row.get("tas_id"), db_cursor) if treasury_account is None: update_skipped_tas(row, tas_rendering_label, skipped_tas) continue # Find a matching transaction record, so we can use its subtier agency information to match to (or create) an # Award record. # Find the award that this award transaction belongs to. If it doesn't exist, create it. filters = {} if row.get("piid"): filters["piid"] = row.get("piid") filters["parent_piid"] = row.get("parent_award_id") else: if row.get("fain") and not row.get("uri"): filters["fain"] = row.get("fain") elif row.get("uri") and not row.get("fain"): filters["uri"] = row.get("uri") else: filters["fain"] = row.get("fain") filters["uri"] = row.get("uri") award = find_matching_award(**filters) if award: awards_touched += [award] award_financial_data = FinancialAccountsByAwards() value_map_faba = { "award": award, "submission": submission_attributes, "reporting_period_start": submission_attributes.reporting_period_start, "reporting_period_end": submission_attributes.reporting_period_end, "treasury_account": treasury_account, "object_class": row.get("object_class"), "program_activity": row.get("program_activity"), } # Still using the cpe|fyb regex compiled above for reverse load_data_into_model(award_financial_data, row, value_map=value_map_faba, save=True, reverse=reverse) for key in skipped_tas: logger.info("Skipped %d rows due to missing TAS: %s", skipped_tas[key]["count"], key) total_tas_skipped = 0 for key in skipped_tas: total_tas_skipped += skipped_tas[key]["count"] logger.info( "Skipped a total of {} TAS rows for File C".format(total_tas_skipped)) return [award.id for award in awards_touched if award]
def insert_new_fpds(self, to_insert, total_rows): place_of_performance_field_map = { "location_country_code": "place_of_perform_country_c", "country_name": "place_of_perf_country_desc", "state_code": "place_of_performance_state", "state_name": "place_of_perfor_state_desc", "city_name": "place_of_perform_city_name", "county_name": "place_of_perform_county_na", "county_code": "place_of_perform_county_co", "zip_4a": "place_of_performance_zip4a", "congressional_code": "place_of_performance_congr", "zip_last4": "place_of_perform_zip_last4", "zip5": "place_of_performance_zip5", } legal_entity_location_field_map = { "location_country_code": "legal_entity_country_code", "country_name": "legal_entity_country_name", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_descrip", "city_name": "legal_entity_city_name", "county_name": "legal_entity_county_name", "county_code": "legal_entity_county_code", "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "zip4": "legal_entity_zip4", "congressional_code": "legal_entity_congressional", "zip_last4": "legal_entity_zip_last4", "zip5": "legal_entity_zip5", } for index, row in enumerate(to_insert, 1): upper_case_dict_values(row) # Create new LegalEntityLocation and LegalEntity from the row data legal_entity_location = create_location( legal_entity_location_field_map, row, {"recipient_flag": True, "is_fpds": True} ) recipient_name = row["awardee_or_recipient_legal"] legal_entity = LegalEntity.objects.create( recipient_unique_id=row["awardee_or_recipient_uniqu"], recipient_name=recipient_name if recipient_name is not None else "", ) legal_entity_value_map = { "location": legal_entity_location, "business_categories": get_business_categories(row=row, data_type="fpds"), "is_fpds": True, } set_legal_entity_boolean_fields(row) legal_entity = load_data_into_model(legal_entity, row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location = create_location(place_of_performance_field_map, row, {"place_of_performance_flag": True}) # Find the toptier awards from the subtier awards awarding_agency = Agency.get_by_subtier_only(row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_subtier_only(row["funding_sub_tier_agency_co"]) # Generate the unique Award ID # "CONT_AW_" + agency_id + referenced_idv_agency_iden + piid + parent_award_id generated_unique_id = ( "CONT_AW_" + (row["agency_id"] if row["agency_id"] else "-NONE-") + "_" + (row["referenced_idv_agency_iden"] if row["referenced_idv_agency_iden"] else "-NONE-") + "_" + (row["piid"] if row["piid"] else "-NONE-") + "_" + (row["parent_award_id"] if row["parent_award_id"] else "-NONE-") ) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=generated_unique_id, piid=row["piid"] ) award.parent_award_piid = row.get("parent_award_id") award.save() # Append row to list of Awards updated AWARD_UPDATE_ID_LIST.append(award.id) if row["last_modified"] and len(str(row["last_modified"])) == len("YYYY-MM-DD HH:MM:SS"): # 19 characters dt_fmt = "%Y-%m-%d %H:%M:%S" else: dt_fmt = "%Y-%m-%d %H:%M:%S.%f" # try using this even if last_modified isn't a valid string try: last_mod_date = datetime.strptime(str(row["last_modified"]), dt_fmt).date() except ValueError: # handle odd-string formats and NULLs from the upstream FPDS-NG system info_message = "Invalid value '{}' does not match: '{}'".format(row["last_modified"], dt_fmt) logger.info(info_message) last_mod_date = None award_type, award_type_desc = award_types(row) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": funding_agency, "recipient": legal_entity, "place_of_performance": pop_location, "period_of_performance_start_date": format_date(row["period_of_performance_star"]), "period_of_performance_current_end_date": format_date(row["period_of_performance_curr"]), "action_date": format_date(row["action_date"]), "last_modified_date": last_mod_date, "transaction_unique_id": row["detached_award_proc_unique"], "generated_unique_award_id": generated_unique_id, "is_fpds": True, "type": award_type, "type_description": award_type_desc, } contract_field_map = {"description": "award_description"} transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True, ) contract_instance = load_data_into_model(TransactionFPDS(), row, as_dict=True) # thrown away detached_award_proc_unique = contract_instance["detached_award_proc_unique"] unique_fpds = TransactionFPDS.objects.filter(detached_award_proc_unique=detached_award_proc_unique) if unique_fpds.first(): transaction_normalized_dict["update_date"] = datetime.now(timezone.utc) transaction_normalized_dict["fiscal_year"] = fy(transaction_normalized_dict["action_date"]) # update TransactionNormalized TransactionNormalized.objects.filter(id=unique_fpds.first().transaction.id).update( **transaction_normalized_dict ) # update TransactionFPDS unique_fpds.update(**contract_instance) else: # create TransactionNormalized transaction = TransactionNormalized(**transaction_normalized_dict) transaction.save() # create TransactionFPDS transaction_fpds = TransactionFPDS(transaction=transaction, **contract_instance) transaction_fpds.save() # Update legal entity to map back to transaction legal_entity.transaction_unique_id = detached_award_proc_unique legal_entity.save()
def insert_new_fabs(self, to_insert): place_of_performance_field_map = { "location_country_code": "place_of_perform_country_c", "country_name": "place_of_perform_country_n", "state_code": "place_of_perfor_state_code", "state_name": "place_of_perform_state_nam", "city_name": "place_of_performance_city", "county_name": "place_of_perform_county_na", "county_code": "place_of_perform_county_co", "foreign_location_description": "place_of_performance_forei", "zip_4a": "place_of_performance_zip4a", "congressional_code": "place_of_performance_congr", "performance_code": "place_of_performance_code", "zip_last4": "place_of_perform_zip_last4", "zip5": "place_of_performance_zip5", } legal_entity_location_field_map = { "location_country_code": "legal_entity_country_code", "country_name": "legal_entity_country_name", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "city_name": "legal_entity_city_name", "city_code": "legal_entity_city_code", "county_name": "legal_entity_county_name", "county_code": "legal_entity_county_code", "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "foreign_location_description": "legal_entity_foreign_descr", "congressional_code": "legal_entity_congressional", "zip_last4": "legal_entity_zip_last4", "zip5": "legal_entity_zip5", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "foreign_city_name": "legal_entity_foreign_city", } for row in to_insert: upper_case_dict_values(row) # Create new LegalEntityLocation and LegalEntity from the row data legal_entity_location = create_location( legal_entity_location_field_map, row, {"recipient_flag": True}) recipient_name = row['awardee_or_recipient_legal'] legal_entity = LegalEntity.objects.create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name if recipient_name is not None else "", parent_recipient_unique_id=row['ultimate_parent_unique_ide'], ) legal_entity_value_map = { "location": legal_entity_location, "business_categories": get_business_categories(row=row, data_type='fabs'), "business_types_description": row['business_types_desc'], } legal_entity = load_data_into_model( legal_entity, row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location = create_location(place_of_performance_field_map, row, {"place_of_performance_flag": True}) # Find the toptier awards from the subtier awards awarding_agency = Agency.get_by_subtier_only( row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_subtier_only( row["funding_sub_tier_agency_co"]) # Generate the unique Award ID # "ASST_AW_" + awarding_sub_tier_agency_c + fain + uri # this will raise an exception if the cast to an int fails, that's ok since we don't want to process # non-numeric record type values record_type_int = int(row['record_type']) if record_type_int == 1: uri = row['uri'] if row['uri'] else '-NONE-' fain = '-NONE-' elif record_type_int in (2, 3): uri = '-NONE-' fain = row['fain'] if row['fain'] else '-NONE-' else: msg = "Invalid record type encountered for the following afa_generated_unique record: {}" raise Exception(msg.format(row['afa_generated_unique'])) astac = row["awarding_sub_tier_agency_c"] if row[ "awarding_sub_tier_agency_c"] else "-NONE-" generated_unique_id = "ASST_AW_{}_{}_{}".format(astac, fain, uri) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=generated_unique_id, fain=row['fain'], uri=row['uri'], record_type=row['record_type'], ) award.save() # Append row to list of Awards updated AWARD_UPDATE_ID_LIST.append(award.id) try: last_mod_date = datetime.strptime(str( row['modified_at']), "%Y-%m-%d %H:%M:%S.%f").date() except ValueError: last_mod_date = datetime.strptime(str(row['modified_at']), "%Y-%m-%d %H:%M:%S").date() parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": funding_agency, "recipient": legal_entity, "place_of_performance": pop_location, "period_of_performance_start_date": format_date(row['period_of_performance_star']), "period_of_performance_current_end_date": format_date(row['period_of_performance_curr']), "action_date": format_date(row['action_date']), "last_modified_date": last_mod_date, "type_description": row['assistance_type_desc'], "transaction_unique_id": row['afa_generated_unique'], "generated_unique_award_id": generated_unique_id, } fad_field_map = { "type": "assistance_type", "description": "award_description", "funding_amount": "total_funding_amount", } transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True, ) financial_assistance_data = load_data_into_model( TransactionFABS(), row, as_dict=True) # thrown away afa_generated_unique = financial_assistance_data[ 'afa_generated_unique'] unique_fabs = TransactionFABS.objects.filter( afa_generated_unique=afa_generated_unique) if unique_fabs.first(): transaction_normalized_dict["update_date"] = datetime.now( timezone.utc) transaction_normalized_dict["fiscal_year"] = fy( transaction_normalized_dict["action_date"]) # Update TransactionNormalized TransactionNormalized.objects.filter( id=unique_fabs.first().transaction.id).update( **transaction_normalized_dict) # Update TransactionFABS unique_fabs.update(**financial_assistance_data) else: # Create TransactionNormalized transaction = TransactionNormalized( **transaction_normalized_dict) transaction.save() # Create TransactionFABS transaction_fabs = TransactionFABS(transaction=transaction, **financial_assistance_data) transaction_fabs.save() # Update legal entity to map back to transaction legal_entity.transaction_unique_id = afa_generated_unique legal_entity.save()
def create_subaward(self, row, shared_award_mappings, award_type): """ Creates a subaward if the internal ID of the current row is in the shared award mappings (this was made to satisfy codeclimate complexity issues) """ # only insert the subaward if the internal_id is in our mappings, otherwise there was a problem # finding one or more parts of the shared data for it and we don't want to insert it. if row['internal_id'] not in shared_award_mappings: logger.info( "[Internal ID {}] not in 'shared_award_mappings'".format( row["internal_id"])) else: shared_mappings = shared_award_mappings[row['internal_id']] prime_award_dict = {} if shared_mappings['award']: prime_award_dict['prime_recipient'] = shared_mappings[ 'award'].recipient if prime_award_dict['prime_recipient']: prime_award_dict['prime_recipient_name'] = shared_mappings[ 'award'].recipient.recipient_name prime_award_dict['business_categories'] = ( shared_mappings['award'].recipient.business_categories or []) upper_case_dict_values(row) subaward_dict = { 'recipient_unique_id': row['duns'], 'recipient_name': row['recipient_name'], 'dba_name': row['dba_name'], 'parent_recipient_unique_id': row['parent_duns'], 'parent_recipient_name': row.get('parent_recipient_name', None), 'business_type_description': row.get('bus_types', None), 'prime_recipient': prime_award_dict.get('prime_recipient', None), 'prime_recipient_name': prime_award_dict.get('prime_recipient_name', None), 'business_categories': prime_award_dict.get('business_categories', []), 'recipient_location_country_code': row['recipient_location_country_code'], 'recipient_location_state_code': row['recipient_location_state_code'], 'recipient_location_state_name': row['recipient_location_state_name'], 'recipient_location_city_name': row['recipient_location_city_name'], 'recipient_location_zip4': row['recipient_location_zip4'], 'recipient_location_zip5': row['recipient_location_zip5'], 'recipient_location_street_address': row['recipient_location_street_address'], 'recipient_location_congressional_code': row['recipient_location_congressional_code'], 'officer_1_name': row['top_paid_fullname_1'], 'officer_1_amount': row['top_paid_amount_1'], 'officer_2_name': row['top_paid_fullname_2'], 'officer_2_amount': row['top_paid_amount_2'], 'officer_3_name': row['top_paid_fullname_3'], 'officer_3_amount': row['top_paid_amount_3'], 'officer_4_name': row['top_paid_fullname_4'], 'officer_4_amount': row['top_paid_amount_4'], 'officer_5_name': row['top_paid_fullname_5'], 'officer_5_amount': row['top_paid_amount_5'], 'data_source': "DBR", 'subaward_number': row['subaward_num'], 'amount': row['subaward_amount'], 'description': row['description'], 'recovery_model_question1': row['q1_flag'], 'recovery_model_question2': row['q2_flag'], 'action_date': row['action_date'], 'award_report_fy_month': row['report_period_mon'], 'award_report_fy_year': row['report_period_year'], 'broker_award_id': row['id'], 'internal_id': row['internal_id'], 'award_type': award_type, 'pop_country_code': row['principle_place_country'], 'pop_state_code': row['principle_place_state'], 'pop_state_name': row['principle_place_state_name'], 'pop_city_name': row['principle_place_city'], 'pop_zip4': row['principle_place_zip'], 'pop_street_address': row['principle_place_street'], 'pop_congressional_code': row['principle_place_district'], 'piid': row.get('piid', None), 'fain': row.get('fain', None), 'updated_at': datetime.now(timezone.utc), # keyword_ts_vector # leave NULL, matview SQL will populate # award_ts_vector # leave NULL, matview SQL will populate # recipient_name_ts_vector # leave NULL, matview SQL will populate # product_or_service_description # leave NULL, matview SQL will populate # total_obl_bin # leave NULL, matview SQL will populate # business_type_code # always NULL # extent_competed # always NULL } if shared_mappings['award']: subaward_dict.update({ "award_id": shared_mappings["award"].id, "prime_award_type": shared_mappings["award"].type, "last_modified_date": shared_mappings["award"].last_modified_date, "latest_transaction_id": shared_mappings["award"].latest_transaction_id, "business_categories": get_le_business_categories( shared_mappings["award"].recipient_id), }) funding_agency = get_agency_values( shared_mappings["award"].funding_agency) awarding_agency = get_agency_values( shared_mappings["award"].awarding_agency) contract_data = get_contract_fields( shared_mappings["award"].latest_transaction_id) if contract_data: subaward_dict.update({ 'pulled_from': contract_data['pulled_from'], 'product_or_service_code': contract_data['product_or_service_code'], # 'product_or_service_description': None, 'type_of_contract_pricing': contract_data['type_of_contract_pricing'], 'type_set_aside': contract_data['type_set_aside'], }) else: funding_agency, awarding_agency = None, None if funding_agency: subaward_dict.update({ "funding_agency_id": funding_agency["agency_id"], "funding_toptier_agency_abbreviation": funding_agency["toptier_agency_abbreviation"], "funding_toptier_agency_name": funding_agency["toptier_agency_name"], "funding_subtier_agency_abbreviation": funding_agency["subtier_agency_abbreviation"], "funding_subtier_agency_name": funding_agency["subtier_agency_name"], }) if awarding_agency: subaward_dict.update({ "awarding_agency_id": awarding_agency["agency_id"], "awarding_toptier_agency_abbreviation": awarding_agency["toptier_agency_abbreviation"], "awarding_toptier_agency_name": awarding_agency["toptier_agency_name"], "awarding_subtier_agency_abbreviation": awarding_agency["subtier_agency_abbreviation"], "awarding_subtier_agency_name": awarding_agency["subtier_agency_name"], }) cfda = None if 'cfda_numbers' in row and row['cfda_numbers']: only_num = row['cfda_numbers'].split(' ') cfda = Cfda.objects.filter(program_number=only_num[0]).first() if cfda: subaward_dict.update({ "cfda_number": cfda.program_number, "cfda_title": cfda.program_title, "cfda_id": cfda.pk, }) subaward_dict['pop_country_name'] = get_country_name_from_code( row['principle_place_country']) subaward_dict[ 'recipient_location_country_name'] = get_country_name_from_code( row['recipient_location_country_code']) performance_city_county = get_city_and_county_from_state( row['principle_place_state'], row['principle_place_city']) subaward_dict['pop_county_code'] = performance_city_county.get( "county_code") subaward_dict['pop_county_name'] = performance_city_county.get( "county_name") subaward_dict['pop_city_code'] = performance_city_county.get( "city_code") ref_loc_city_county = get_city_and_county_from_state( row['recipient_location_state_code'], row['recipient_location_city_name']) subaward_dict[ 'recipient_location_county_code'] = ref_loc_city_county.get( "county_code") subaward_dict[ 'recipient_location_county_name'] = ref_loc_city_county.get( "county_name") subaward_dict[ 'recipient_location_city_code'] = ref_loc_city_county.get( "city_code") # Either we're starting with an empty table in regards to this award type or we've deleted all # subawards related to the internal_id, either way we just create the subaward Subaward.objects.create(**subaward_dict) if shared_mappings["award"]: award_update_id_list.add(shared_mappings["award"].id)
def insert_new_fabs(to_insert): fabs_normalized_field_map = { "type": "assistance_type", "description": "award_description", "funding_amount": "total_funding_amount", } fabs_field_map = { "officer_1_name": "high_comp_officer1_full_na", "officer_1_amount": "high_comp_officer1_amount", "officer_2_name": "high_comp_officer2_full_na", "officer_2_amount": "high_comp_officer2_amount", "officer_3_name": "high_comp_officer3_full_na", "officer_3_amount": "high_comp_officer3_amount", "officer_4_name": "high_comp_officer4_full_na", "officer_4_amount": "high_comp_officer4_amount", "officer_5_name": "high_comp_officer5_full_na", "officer_5_amount": "high_comp_officer5_amount", } update_award_ids = [] for row in to_insert: upper_case_dict_values(row) # Find the toptier awards from the subtier awards awarding_agency = Agency.get_by_subtier_only( row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_subtier_only( row["funding_sub_tier_agency_co"]) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=row["unique_award_key"], fain=row["fain"], uri=row["uri"], record_type=row["record_type"], ) award.save() # Append row to list of Awards updated update_award_ids.append(award.id) try: last_mod_date = datetime.strptime(str(row["modified_at"]), "%Y-%m-%d %H:%M:%S.%f").date() except ValueError: last_mod_date = datetime.strptime(str(row["modified_at"]), "%Y-%m-%d %H:%M:%S").date() parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": funding_agency, "period_of_performance_start_date": format_date(row["period_of_performance_star"]), "period_of_performance_current_end_date": format_date(row["period_of_performance_curr"]), "action_date": format_date(row["action_date"]), "last_modified_date": last_mod_date, "type_description": row["assistance_type_desc"], "transaction_unique_id": row["afa_generated_unique"], "business_categories": get_business_categories(row=row, data_type="fabs"), } transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fabs_normalized_field_map, value_map=parent_txn_value_map, as_dict=True, ) financial_assistance_data = load_data_into_model( TransactionFABS(), row, field_map=fabs_field_map, as_dict=True # thrown away ) # Hack to cut back on the number of warnings dumped to the log. financial_assistance_data["updated_at"] = cast_datetime_to_utc( financial_assistance_data["updated_at"]) financial_assistance_data["created_at"] = cast_datetime_to_utc( financial_assistance_data["created_at"]) financial_assistance_data["modified_at"] = cast_datetime_to_utc( financial_assistance_data["modified_at"]) afa_generated_unique = financial_assistance_data[ "afa_generated_unique"] unique_fabs = TransactionFABS.objects.filter( afa_generated_unique=afa_generated_unique) if unique_fabs.first(): transaction_normalized_dict["update_date"] = datetime.now( timezone.utc) transaction_normalized_dict["fiscal_year"] = fy( transaction_normalized_dict["action_date"]) # Update TransactionNormalized TransactionNormalized.objects.filter( id=unique_fabs.first().transaction.id).update( **transaction_normalized_dict) # Update TransactionFABS unique_fabs.update(**financial_assistance_data) else: # Create TransactionNormalized transaction_normalized = TransactionNormalized( **transaction_normalized_dict) transaction_normalized.save() # Create TransactionFABS transaction_fabs = TransactionFABS( transaction=transaction_normalized, **financial_assistance_data) transaction_fabs.save() return update_award_ids
def insert_new_fpds(self, to_insert, total_rows): place_of_performance_field_map = { "location_country_code": "place_of_perform_country_c", "country_name": "place_of_perf_country_desc", "state_code": "place_of_performance_state", "state_name": "place_of_perfor_state_desc", "city_name": "place_of_perform_city_name", "county_name": "place_of_perform_county_na", "county_code": "place_of_perform_county_co", "zip_4a": "place_of_performance_zip4a", "congressional_code": "place_of_performance_congr", "zip_last4": "place_of_perform_zip_last4", "zip5": "place_of_performance_zip5", } legal_entity_location_field_map = { "location_country_code": "legal_entity_country_code", "country_name": "legal_entity_country_name", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_descrip", "city_name": "legal_entity_city_name", "county_name": "legal_entity_county_name", "county_code": "legal_entity_county_code", "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "zip4": "legal_entity_zip4", "congressional_code": "legal_entity_congressional", "zip_last4": "legal_entity_zip_last4", "zip5": "legal_entity_zip5", } for index, row in enumerate(to_insert, 1): upper_case_dict_values(row) # Create new LegalEntityLocation and LegalEntity from the row data legal_entity_location = create_location( legal_entity_location_field_map, row, { "recipient_flag": True, "is_fpds": True }) recipient_name = row["awardee_or_recipient_legal"] legal_entity = LegalEntity.objects.create( recipient_unique_id=row["awardee_or_recipient_uniqu"], recipient_name=recipient_name if recipient_name is not None else "", ) legal_entity_value_map = { "location": legal_entity_location, "business_categories": get_business_categories(row=row, data_type="fpds"), "is_fpds": True, } set_legal_entity_boolean_fields(row) legal_entity = load_data_into_model( legal_entity, row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location = create_location(place_of_performance_field_map, row, {"place_of_performance_flag": True}) # Find the toptier awards from the subtier awards awarding_agency = Agency.get_by_subtier_only( row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_subtier_only( row["funding_sub_tier_agency_co"]) # Generate the unique Award ID # "CONT_AW_" + agency_id + referenced_idv_agency_iden + piid + parent_award_id generated_unique_id = ( "CONT_AW_" + (row["agency_id"] if row["agency_id"] else "-NONE-") + "_" + (row["referenced_idv_agency_iden"] if row["referenced_idv_agency_iden"] else "-NONE-") + "_" + (row["piid"] if row["piid"] else "-NONE-") + "_" + (row["parent_award_id"] if row["parent_award_id"] else "-NONE-")) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=generated_unique_id, piid=row["piid"]) award.parent_award_piid = row.get("parent_award_id") award.save() # Append row to list of Awards updated AWARD_UPDATE_ID_LIST.append(award.id) if row["last_modified"] and len(str(row["last_modified"])) == len( "YYYY-MM-DD HH:MM:SS"): # 19 characters dt_fmt = "%Y-%m-%d %H:%M:%S" else: dt_fmt = "%Y-%m-%d %H:%M:%S.%f" # try using this even if last_modified isn't a valid string try: last_mod_date = datetime.strptime(str(row["last_modified"]), dt_fmt).date() except ValueError: # handle odd-string formats and NULLs from the upstream FPDS-NG system info_message = "Invalid value '{}' does not match: '{}'".format( row["last_modified"], dt_fmt) logger.info(info_message) last_mod_date = None award_type, award_type_desc = award_types(row) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": funding_agency, "recipient": legal_entity, "place_of_performance": pop_location, "period_of_performance_start_date": format_date(row["period_of_performance_star"]), "period_of_performance_current_end_date": format_date(row["period_of_performance_curr"]), "action_date": format_date(row["action_date"]), "last_modified_date": last_mod_date, "transaction_unique_id": row["detached_award_proc_unique"], "generated_unique_award_id": generated_unique_id, "is_fpds": True, "type": award_type, "type_description": award_type_desc, } contract_field_map = {"description": "award_description"} transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True, ) contract_instance = load_data_into_model( TransactionFPDS(), row, as_dict=True) # thrown away detached_award_proc_unique = contract_instance[ "detached_award_proc_unique"] unique_fpds = TransactionFPDS.objects.filter( detached_award_proc_unique=detached_award_proc_unique) if unique_fpds.first(): transaction_normalized_dict["update_date"] = datetime.now( timezone.utc) transaction_normalized_dict["fiscal_year"] = fy( transaction_normalized_dict["action_date"]) # update TransactionNormalized TransactionNormalized.objects.filter( id=unique_fpds.first().transaction.id).update( **transaction_normalized_dict) # update TransactionFPDS unique_fpds.update(**contract_instance) else: # create TransactionNormalized transaction = TransactionNormalized( **transaction_normalized_dict) transaction.save() # create TransactionFPDS transaction_fpds = TransactionFPDS(transaction=transaction, **contract_instance) transaction_fpds.save() # Update legal entity to map back to transaction legal_entity.transaction_unique_id = detached_award_proc_unique legal_entity.save()
def insert_new_fabs(to_insert): place_of_performance_field_map = { "location_country_code": "place_of_perform_country_c", "country_name": "place_of_perform_country_n", "state_code": "place_of_perfor_state_code", "state_name": "place_of_perform_state_nam", "city_name": "place_of_performance_city", "county_name": "place_of_perform_county_na", "county_code": "place_of_perform_county_co", "foreign_location_description": "place_of_performance_forei", "zip_4a": "place_of_performance_zip4a", "congressional_code": "place_of_performance_congr", "performance_code": "place_of_performance_code", "zip_last4": "place_of_perform_zip_last4", "zip5": "place_of_performance_zip5", } legal_entity_location_field_map = { "location_country_code": "legal_entity_country_code", "country_name": "legal_entity_country_name", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "city_name": "legal_entity_city_name", "city_code": "legal_entity_city_code", "county_name": "legal_entity_county_name", "county_code": "legal_entity_county_code", "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "foreign_location_description": "legal_entity_foreign_descr", "congressional_code": "legal_entity_congressional", "zip_last4": "legal_entity_zip_last4", "zip5": "legal_entity_zip5", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "foreign_city_name": "legal_entity_foreign_city", } fabs_normalized_field_map = { "type": "assistance_type", "description": "award_description", "funding_amount": "total_funding_amount", } fabs_field_map = { "officer_1_name": "high_comp_officer1_full_na", "officer_1_amount": "high_comp_officer1_amount", "officer_2_name": "high_comp_officer2_full_na", "officer_2_amount": "high_comp_officer2_amount", "officer_3_name": "high_comp_officer3_full_na", "officer_3_amount": "high_comp_officer3_amount", "officer_4_name": "high_comp_officer4_full_na", "officer_4_amount": "high_comp_officer4_amount", "officer_5_name": "high_comp_officer5_full_na", "officer_5_amount": "high_comp_officer5_amount", } update_award_ids = [] for row in to_insert: upper_case_dict_values(row) # Create new LegalEntityLocation and LegalEntity from the row data legal_entity_location = create_location( legal_entity_location_field_map, row, {"recipient_flag": True}) recipient_name = row['awardee_or_recipient_legal'] legal_entity = LegalEntity.objects.create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name if recipient_name is not None else "", parent_recipient_unique_id=row['ultimate_parent_unique_ide'], ) legal_entity_value_map = { "location": legal_entity_location, "business_categories": get_business_categories(row=row, data_type='fabs'), "business_types_description": row['business_types_desc'], } legal_entity = load_data_into_model(legal_entity, row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location = create_location(place_of_performance_field_map, row, {"place_of_performance_flag": True}) # Find the toptier awards from the subtier awards awarding_agency = Agency.get_by_subtier_only( row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_subtier_only( row["funding_sub_tier_agency_co"]) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=row['unique_award_key'], fain=row['fain'], uri=row['uri'], record_type=row['record_type'], ) award.save() # Append row to list of Awards updated update_award_ids.append(award.id) try: last_mod_date = datetime.strptime(str(row['modified_at']), "%Y-%m-%d %H:%M:%S.%f").date() except ValueError: last_mod_date = datetime.strptime(str(row['modified_at']), "%Y-%m-%d %H:%M:%S").date() parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": funding_agency, "recipient": legal_entity, "place_of_performance": pop_location, "period_of_performance_start_date": format_date(row['period_of_performance_star']), "period_of_performance_current_end_date": format_date(row['period_of_performance_curr']), "action_date": format_date(row['action_date']), "last_modified_date": last_mod_date, "type_description": row['assistance_type_desc'], "transaction_unique_id": row['afa_generated_unique'], } transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fabs_normalized_field_map, value_map=parent_txn_value_map, as_dict=True, ) financial_assistance_data = load_data_into_model( TransactionFABS(), # thrown away row, field_map=fabs_field_map, as_dict=True) # Hack to cut back on the number of warnings dumped to the log. financial_assistance_data['updated_at'] = cast_datetime_to_utc( financial_assistance_data['updated_at']) financial_assistance_data['created_at'] = cast_datetime_to_utc( financial_assistance_data['created_at']) financial_assistance_data['modified_at'] = cast_datetime_to_utc( financial_assistance_data['modified_at']) afa_generated_unique = financial_assistance_data[ 'afa_generated_unique'] unique_fabs = TransactionFABS.objects.filter( afa_generated_unique=afa_generated_unique) if unique_fabs.first(): transaction_normalized_dict["update_date"] = datetime.now( timezone.utc) transaction_normalized_dict["fiscal_year"] = fy( transaction_normalized_dict["action_date"]) # Update TransactionNormalized TransactionNormalized.objects.filter( id=unique_fabs.first().transaction.id).update( **transaction_normalized_dict) # Update TransactionFABS unique_fabs.update(**financial_assistance_data) else: # Create TransactionNormalized transaction_normalized = TransactionNormalized( **transaction_normalized_dict) transaction_normalized.save() # Create TransactionFABS transaction_fabs = TransactionFABS( transaction=transaction_normalized, **financial_assistance_data) transaction_fabs.save() # Update legal entity to map back to transaction legal_entity.transaction_unique_id = afa_generated_unique legal_entity.save() return update_award_ids
def insert_new_fabs(to_insert): place_of_performance_field_map = { "location_country_code": "place_of_perform_country_c", "country_name": "place_of_perform_country_n", "state_code": "place_of_perfor_state_code", "state_name": "place_of_perform_state_nam", "city_name": "place_of_performance_city", "county_name": "place_of_perform_county_na", "county_code": "place_of_perform_county_co", "foreign_location_description": "place_of_performance_forei", "zip_4a": "place_of_performance_zip4a", "congressional_code": "place_of_performance_congr", "performance_code": "place_of_performance_code", "zip_last4": "place_of_perform_zip_last4", "zip5": "place_of_performance_zip5", } legal_entity_location_field_map = { "location_country_code": "legal_entity_country_code", "country_name": "legal_entity_country_name", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "city_name": "legal_entity_city_name", "city_code": "legal_entity_city_code", "county_name": "legal_entity_county_name", "county_code": "legal_entity_county_code", "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "foreign_location_description": "legal_entity_foreign_descr", "congressional_code": "legal_entity_congressional", "zip_last4": "legal_entity_zip_last4", "zip5": "legal_entity_zip5", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "foreign_city_name": "legal_entity_foreign_city", } update_award_ids = [] for row in to_insert: upper_case_dict_values(row) # Create new LegalEntityLocation and LegalEntity from the row data legal_entity_location = create_location(legal_entity_location_field_map, row, {"recipient_flag": True}) recipient_name = row['awardee_or_recipient_legal'] legal_entity = LegalEntity.objects.create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name if recipient_name is not None else "", parent_recipient_unique_id=row['ultimate_parent_unique_ide'], ) legal_entity_value_map = { "location": legal_entity_location, "business_categories": get_business_categories(row=row, data_type='fabs'), "business_types_description": row['business_types_desc'], } legal_entity = load_data_into_model(legal_entity, row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location = create_location(place_of_performance_field_map, row, {"place_of_performance_flag": True}) # Find the toptier awards from the subtier awards awarding_agency = Agency.get_by_subtier_only(row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_subtier_only(row["funding_sub_tier_agency_co"]) # Generate the unique Award ID # "ASST_AW_" + awarding_sub_tier_agency_c + fain + uri # this will raise an exception if the cast to an int fails, that's ok since we don't want to process # non-numeric record type values record_type_int = int(row['record_type']) if record_type_int == 1: uri = row['uri'] if row['uri'] else '-NONE-' fain = '-NONE-' elif record_type_int in (2, 3): uri = '-NONE-' fain = row['fain'] if row['fain'] else '-NONE-' else: msg = "Invalid record type encountered for the following afa_generated_unique record: {}" raise Exception(msg.format(row['afa_generated_unique'])) astac = row["awarding_sub_tier_agency_c"] if row["awarding_sub_tier_agency_c"] else "-NONE-" generated_unique_id = "ASST_AW_{}_{}_{}".format(astac, fain, uri) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=generated_unique_id, fain=row['fain'], uri=row['uri'], record_type=row['record_type'], ) award.save() # Append row to list of Awards updated update_award_ids.append(award.id) try: last_mod_date = datetime.strptime(str(row['modified_at']), "%Y-%m-%d %H:%M:%S.%f").date() except ValueError: last_mod_date = datetime.strptime(str(row['modified_at']), "%Y-%m-%d %H:%M:%S").date() parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": funding_agency, "recipient": legal_entity, "place_of_performance": pop_location, "period_of_performance_start_date": format_date(row['period_of_performance_star']), "period_of_performance_current_end_date": format_date(row['period_of_performance_curr']), "action_date": format_date(row['action_date']), "last_modified_date": last_mod_date, "type_description": row['assistance_type_desc'], "transaction_unique_id": row['afa_generated_unique'], "generated_unique_award_id": generated_unique_id, } fad_field_map = { "type": "assistance_type", "description": "award_description", "funding_amount": "total_funding_amount", } transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True, ) financial_assistance_data = load_data_into_model(TransactionFABS(), row, as_dict=True) # thrown away # Hack to cut back on the number of warnings dumped to the log. financial_assistance_data['updated_at'] = cast_datetime_to_utc(financial_assistance_data['updated_at']) financial_assistance_data['created_at'] = cast_datetime_to_utc(financial_assistance_data['created_at']) financial_assistance_data['modified_at'] = cast_datetime_to_utc(financial_assistance_data['modified_at']) afa_generated_unique = financial_assistance_data['afa_generated_unique'] unique_fabs = TransactionFABS.objects.filter(afa_generated_unique=afa_generated_unique) if unique_fabs.first(): transaction_normalized_dict["update_date"] = datetime.now(timezone.utc) transaction_normalized_dict["fiscal_year"] = fy(transaction_normalized_dict["action_date"]) # Update TransactionNormalized TransactionNormalized.objects.filter(id=unique_fabs.first().transaction.id).update( **transaction_normalized_dict ) # Update TransactionFABS unique_fabs.update(**financial_assistance_data) else: # Create TransactionNormalized transaction_normalized = TransactionNormalized(**transaction_normalized_dict) transaction_normalized.save() # Create TransactionFABS transaction_fabs = TransactionFABS(transaction=transaction_normalized, **financial_assistance_data) transaction_fabs.save() # Update legal entity to map back to transaction legal_entity.transaction_unique_id = afa_generated_unique legal_entity.save() return update_award_ids
def load_file_c(submission_attributes, db_cursor, award_financial_frame): """ Process and load file C broker data. Note: this should run AFTER the D1 and D2 files are loaded because we try to join to those records to retrieve some additional information about the awarding sub-tier agency. """ # this matches the file b reverse directive, but am repeating it here to ensure that we don't overwrite it as we # change up the order of file loading if not award_financial_frame.size: logger.warning('No File C (award financial) data found, skipping...') return reverse = re.compile(r'(_(cpe|fyb)$)|^transaction_obligated_amount$') # dictionary to capture TAS that were skipped and some metadata # tas = top-level key # count = number of rows skipped # rows = row numbers skipped, corresponding to the original row numbers in the file that was submitted skipped_tas = {} award_financial_frame['txn'] = award_financial_frame.apply(get_award_financial_transaction, axis=1) award_financial_frame['awarding_agency'] = award_financial_frame.apply(get_awarding_agency, axis=1) award_financial_frame['object_class'] = award_financial_frame.apply(get_or_create_object_class_rw, axis=1, logger=logger) award_financial_frame['program_activity'] = award_financial_frame.apply(get_or_create_program_activity, axis=1, submission_attributes=submission_attributes) total_rows = award_financial_frame.shape[0] start_time = datetime.now() awards_touched = [] # format award_financial_frame float_cols = ['transaction_obligated_amou'] award_financial_frame[float_cols] = award_financial_frame[float_cols].fillna(0) award_financial_frame = award_financial_frame.replace({np.nan: None}) for index, row in enumerate(award_financial_frame.to_dict(orient='records'), 1): if not (index % 100): logger.info('C File Load: Loading row {} of {} ({})'.format(str(index), str(total_rows), datetime.now() - start_time)) upper_case_dict_values(row) # Check and see if there is an entry for this TAS treasury_account, tas_rendering_label = get_treasury_appropriation_account_tas_lookup( row.get('tas_id'), db_cursor) if treasury_account is None: update_skipped_tas(row, tas_rendering_label, skipped_tas) continue # Find a matching transaction record, so we can use its subtier agency information to match to (or create) an # Award record. # Find the award that this award transaction belongs to. If it doesn't exist, create it. filters = {} if row.get('piid'): filters['piid'] = row.get('piid') filters['parent_piid'] = row.get('parent_award_id') else: if row.get('fain') and not row.get('uri'): filters['fain'] = row.get('fain') elif row.get('uri') and not row.get('fain'): filters['uri'] = row.get('uri') else: filters['fain'] = row.get('fain') filters['uri'] = row.get('uri') award = find_matching_award(**filters) if award: awards_touched += [award] award_financial_data = FinancialAccountsByAwards() value_map_faba = { 'award': award, 'submission': submission_attributes, 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, 'treasury_account': treasury_account, 'object_class': row.get('object_class'), 'program_activity': row.get('program_activity'), } # Still using the cpe|fyb regex compiled above for reverse load_data_into_model(award_financial_data, row, value_map=value_map_faba, save=True, reverse=reverse) awards_cache.clear() for key in skipped_tas: logger.info('Skipped %d rows due to missing TAS: %s', skipped_tas[key]['count'], key) total_tas_skipped = 0 for key in skipped_tas: total_tas_skipped += skipped_tas[key]['count'] logger.info('Skipped a total of {} TAS rows for File C'.format(total_tas_skipped)) return [award.id for award in awards_touched if award]