def get_or_create_award(self, row, awarding_agency): fain = row.get("federal_award_id", None) uri = row.get("unique_transaction_id", None) # ask: why unique_transaction_id instead of uri? created, award = Award.get_or_create_summary_award( fain=fain, uri=uri, awarding_agency=awarding_agency) return award
def get_or_create_award(self, row, awarding_agency_id): piid = row.get("piid", None) parent_award_id = row.get("idvpiid", None) awarding_agency = Agency.objects.get(id=awarding_agency_id) award = Award.get_or_create_summary_award( piid=piid, fain=None, uri=None, awarding_agency=awarding_agency, parent_award_id=parent_award_id) return award
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 load_file_d1(submission_attributes, procurement_data, db_cursor, quick=False): """ Process and load file D1 broker data (contract award txns). """ legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "location_country_code": "legal_entity_country_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "state_code": "legal_entity_state_code", "zip4": "legal_entity_zip4" } place_of_performance_field_map = { # not sure place_of_performance_locat maps exactly to city name "city_name": "place_of_performance_locat", "congressional_code": "place_of_performance_congr", "state_code": "place_of_performance_state", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } place_of_performance_value_map = { "place_of_performance_flag": True } legal_entity_location_value_map = { "recipient_flag": True } contract_field_map = { "type": "contract_award_type", "description": "award_description" } d_start_time = time.time() if quick: parameters = {'broker_submission_id': submission_attributes.broker_submission_id} run_sql_file('usaspending_api/etl/management/load_file_d1.sql', parameters) logger.info('\n\n\n\nFile D1 time elapsed: {}'.format(time.time() - d_start_time)) return total_rows = len(procurement_data) start_time = datetime.now() for index, row in enumerate(procurement_data, 1): if not (index % 100): logger.info('D1 File Load: Loading row {} of {} ({})'.format(str(index), str(total_rows), datetime.now() - start_time)) legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, copy(legal_entity_location_value_map) ) recipient_name = row['awardee_or_recipient_legal'] if recipient_name is None: recipient_name = "" # Create the legal entity if it doesn't exist legal_entity, created = LegalEntity.objects.get_or_create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name ) if created: legal_entity_value_map = { "location": legal_entity_location, } 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, created = get_or_create_location( place_of_performance_field_map, row, copy(place_of_performance_value_map)) # If awarding toptier agency code (aka CGAC) is not supplied on the D1 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len(row['awarding_agency_code'].strip()) < 1: row['awarding_agency_code'] = Agency.get_by_subtier( row["awarding_sub_tier_agency_c"]).toptier_agency.cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len(row['funding_agency_code'].strip()) < 1: funding_agency = Agency.get_by_subtier(row["funding_sub_tier_agency_co"]) row['funding_agency_code'] = ( funding_agency.toptier_agency.cgac_code if funding_agency is not None else None) # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"] ) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_piid=row.get('parent_award_id')) # It is a FAIN/PIID/URI, not our db's pk award.save() award_update_id_list.append(award.id) award_contract_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "recipient": legal_entity, "place_of_performance": pop_location, 'submission': submission_attributes, "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True) transaction = TransactionNormalized.get_or_create_transaction(**transaction_dict) transaction.save() contract_value_map = { 'submission': submission_attributes, 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, "period_of_performance_potential_end_date": format_date(row['period_of_perf_potential_e']) } contract_instance = load_data_into_model( TransactionFPDS(), # thrown away row, field_map=contract_field_map, value_map=contract_value_map, as_dict=True) transaction_contract = TransactionFPDS(transaction=transaction, **contract_instance) transaction_contract.save() logger.info('\n\n\n\nFile D1 time elapsed: {}'.format(time.time() - d_start_time))
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 load_file_d2(submission_attributes, award_financial_assistance_data, db_cursor): """ Process and load file D2 broker data (financial assistance award txns). """ legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "city_code": "legal_entity_city_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "county_code": "legal_entity_county_code", "county_name": "legal_entity_county_name", "foreign_city_name": "legal_entity_foreign_city", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "zip5": "legal_entity_zip5", "zip_last4": "legal_entity_zip_last4", "location_country_code": "legal_entity_country_code" } place_of_performance_field_map = { "city_name": "place_of_performance_city", "performance_code": "place_of_performance_code", "congressional_code": "place_of_performance_congr", "county_name": "place_of_perform_county_na", "foreign_location_description": "place_of_performance_forei", "state_name": "place_of_perform_state_nam", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } legal_entity_location_value_map = {"recipient_flag": True} place_of_performance_value_map = {"place_of_performance_flag": True} fad_field_map = { "type": "assistance_type", "description": "award_description", } for row in award_financial_assistance_data: legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, legal_entity_location_value_map) # Create the legal entity if it doesn't exist try: legal_entity = LegalEntity.objects.get( recipient_unique_id=row['awardee_or_recipient_uniqu']) except ObjectDoesNotExist: legal_entity_value_map = { "location": legal_entity_location, "legal_entity_id": row['awardee_or_recipient_uniqu'] } legal_entity = load_data_into_model( LegalEntity(), row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location, created = get_or_create_location( place_of_performance_field_map, row, place_of_performance_value_map) # If toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up if row['awarding_agency_code'] is None: row['awarding_agency_code'] = Agency.get_by_subtier( row["awarding_sub_tier_agency_c"]).toptier_agency.cgac_code if row['funding_agency_code'] is None: row['funding_agency_code'] = Agency.get_by_subtier( row["funding_sub_tier_agency_co"]).toptier_agency.cgac_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"]) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_id=row.get('parent_award_id')) award.save() AWARD_UPDATE_ID_LIST.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "recipient": legal_entity, "place_of_performance": pop_location, 'submission': submission_attributes, "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']), } transaction_instance = load_data_into_model( Transaction(), row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True) transaction_instance, created = Transaction.objects.get_or_create( **transaction_instance) fad_value_map = { "transaction": transaction_instance, "submission": submission_attributes, "cfda": CFDAProgram.objects.filter( program_number=row['cfda_number']).first(), 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, "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']), } financial_assistance_data = load_data_into_model( TransactionAssistance(), row, field_map=fad_field_map, value_map=fad_value_map, save=True)
def load_file_c(submission_attributes, award_financial_data, db_cursor): """ 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 reverse = re.compile(r'(_(cpe|fyb)$)|^transaction_obligated_amount$') for row in award_financial_data: # Check and see if there is an entry for this TAS treasury_account = get_treasury_appropriation_account_tas_lookup( row.get('tas_id'), db_cursor) if treasury_account is None: raise Exception('Could not find appropriation account for TAS: ' + row['tas']) # Find a matching transaction record, so we can use its # subtier agency information to match to (or create) an Award record awarding_cgac = row.get('agency_identifier') # cgac from record's TAS txn = get_award_financial_transaction( awarding_cgac, piid=row.get('piid'), parent_award_id=row.get('parent_award_id'), fain=row.get('fain'), uri=row.get('uri')) if txn is not None: # We found a matching transaction, so grab its awarding agency # info and pass it get_or_create_summary_award awarding_agency = txn.awarding_agency else: # No matching transaction found, so find/create Award by using # topiter agency only, since CGAC code is the only piece of # awarding agency info that we have. awarding_agency = Agency.get_by_toptier(awarding_cgac) # Find the award that this award transaction belongs to. If it doesn't exist, create it. created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_id=row.get('parent_award_id'), use_cache=False) award_financial_data = FinancialAccountsByAwards() value_map = { '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': get_or_create_object_class(row['object_class'], row['by_direct_reimbursable_fun'], logger), 'program_activity': get_or_create_program_activity(row, submission_attributes) } # Still using the cpe|fyb regex compiled above for reverse afd = load_data_into_model(award_financial_data, row, value_map=value_map, save=True, reverse=reverse) awards_cache.clear()
def update_transaction_contract(db_cursor, fiscal_year=None, page=1, limit=500000): # logger.info("Getting IDs for what's currently in the DB...") # current_ids = TransactionFPDS.objects # # if fiscal_year: # current_ids = current_ids.filter(action_date__fy=fiscal_year) # # current_ids = current_ids.values_list('detached_award_procurement_id', flat=True) query = "SELECT * FROM detached_award_procurement" arguments = [] fy_begin = '10/01/' + str(fiscal_year - 1) fy_end = '09/30/' + str(fiscal_year) if fiscal_year: if arguments: query += " AND" else: query += " WHERE" query += ' action_date::Date BETWEEN %s AND %s' arguments += [fy_begin] arguments += [fy_end] query += ' ORDER BY detached_award_procurement_id LIMIT %s OFFSET %s' arguments += [limit, (page-1)*limit] logger.info("Executing query on Broker DB => " + query % (arguments[0], arguments[1], arguments[2], arguments[3])) db_cursor.execute(query, arguments) logger.info("Running dictfetchall on db_cursor") procurement_data = dictfetchall(db_cursor) legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "location_country_code": "legal_entity_country_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "state_code": "legal_entity_state_code", "zip4": "legal_entity_zip4" } legal_entity_location_value_map = { "recipient_flag": True } place_of_performance_field_map = { # not sure place_of_performance_locat maps exactly to city name # "city_name": "place_of_performance_locat", # location id doesn't mean it's a city. Can't use this mapping "congressional_code": "place_of_performance_congr", "state_code": "place_of_performance_state", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } place_of_performance_value_map = { "place_of_performance_flag": True } contract_field_map = { "type": "contract_award_type", "description": "award_description" } logger.info("Getting total rows") # rows_loaded = len(current_ids) total_rows = len(procurement_data) # - rows_loaded logger.info("Processing " + str(total_rows) + " rows of procurement data") # skip_count = 0 start_time = datetime.now() for index, row in enumerate(procurement_data, 1): with db_transaction.atomic(): # if TransactionFPDS.objects.values('detached_award_procurement_id').\ # filter(detached_award_procurement_id=str(row['detached_award_procurement_id'])).first(): # skip_count += 1 # # if not (skip_count % 100): # logger.info('Skipped {} records so far'.format(str(skip_count))) if not (index % 100): logger.info('D1 File Load: Loading row {} of {} ({})'.format(str(index), str(total_rows), datetime.now() - start_time)) recipient_name = row['awardee_or_recipient_legal'] if recipient_name is None: recipient_name = "" legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, copy(legal_entity_location_value_map) ) # Create the legal entity if it doesn't exist legal_entity, created = LegalEntity.objects.get_or_create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name ) if created: legal_entity_value_map = { "location": legal_entity_location, } 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, created = get_or_create_location( place_of_performance_field_map, row, copy(place_of_performance_value_map)) # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len(row['awarding_agency_code'].strip()) < 1: awarding_subtier_agency_id = subtier_agency_map[row["awarding_sub_tier_agency_c"]] awarding_toptier_agency_id = subtier_to_agency_map[awarding_subtier_agency_id]['toptier_agency_id'] awarding_cgac_code = toptier_agency_map[awarding_toptier_agency_id] row['awarding_agency_code'] = awarding_cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len(row['funding_agency_code'].strip()) < 1: funding_subtier_agency_id = subtier_agency_map.get(row["funding_sub_tier_agency_co"]) if funding_subtier_agency_id is not None: funding_toptier_agency_id = \ subtier_to_agency_map[funding_subtier_agency_id]['toptier_agency_id'] funding_cgac_code = toptier_agency_map[funding_toptier_agency_id] else: funding_cgac_code = None row['funding_agency_code'] = funding_cgac_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"] ) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_piid=row.get('parent_award_id')) award.save() award_update_id_list.append(award.id) award_contract_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True) transaction = TransactionNormalized.get_or_create_transaction(**transaction_dict) transaction.save() contract_instance = load_data_into_model( TransactionFPDS(), # thrown away row, as_dict=True) transaction_contract = TransactionFPDS(transaction=transaction, **contract_instance) # catch exception and do nothing if we see # "django.db.utils.IntegrityError: duplicate key value violates unique constraint" try: transaction_contract.save() except IntegrityError: pass
def update_transaction_contract(db_cursor, fiscal_year=None, page=1, limit=500000): # logger.info("Getting IDs for what's currently in the DB...") # current_ids = TransactionFPDS.objects # # if fiscal_year: # current_ids = current_ids.filter(action_date__fy=fiscal_year) # # current_ids = current_ids.values_list('detached_award_procurement_id', flat=True) query = "SELECT * FROM detached_award_procurement" arguments = [] fy_begin = '10/01/' + str(fiscal_year - 1) fy_end = '09/30/' + str(fiscal_year) if fiscal_year: if arguments: query += " AND" else: query += " WHERE" query += ' action_date::Date BETWEEN %s AND %s' arguments += [fy_begin] arguments += [fy_end] query += ' ORDER BY detached_award_procurement_id LIMIT %s OFFSET %s' arguments += [limit, (page - 1) * limit] logger.info("Executing query on Broker DB => " + query % (arguments[0], arguments[1], arguments[2], arguments[3])) db_cursor.execute(query, arguments) logger.info("Running dictfetchall on db_cursor") procurement_data = dictfetchall(db_cursor) legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "location_country_code": "legal_entity_country_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "state_code": "legal_entity_state_code", "zip4": "legal_entity_zip4" } legal_entity_location_value_map = {"recipient_flag": True} place_of_performance_field_map = { # not sure place_of_performance_locat maps exactly to city name # "city_name": "place_of_performance_locat", # location id doesn't mean it's a city. Can't use this mapping "congressional_code": "place_of_performance_congr", "state_code": "place_of_performance_state", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } place_of_performance_value_map = {"place_of_performance_flag": True} contract_field_map = { "type": "contract_award_type", "description": "award_description" } logger.info("Getting total rows") # rows_loaded = len(current_ids) total_rows = len(procurement_data) # - rows_loaded logger.info("Processing " + str(total_rows) + " rows of procurement data") # skip_count = 0 start_time = datetime.now() for index, row in enumerate(procurement_data, 1): with db_transaction.atomic(): # if TransactionFPDS.objects.values('detached_award_procurement_id').\ # filter(detached_award_procurement_id=str(row['detached_award_procurement_id'])).first(): # skip_count += 1 # # if not (skip_count % 100): # logger.info('Skipped {} records so far'.format(str(skip_count))) if not (index % 100): logger.info( 'D1 File Load: Loading row {} of {} ({})'.format( str(index), str(total_rows), datetime.now() - start_time)) recipient_name = row['awardee_or_recipient_legal'] if recipient_name is None: recipient_name = "" legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, copy(legal_entity_location_value_map)) # Create the legal entity if it doesn't exist legal_entity, created = LegalEntity.objects.get_or_create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name) if created: legal_entity_value_map = { "location": legal_entity_location, } 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, created = get_or_create_location( place_of_performance_field_map, row, copy(place_of_performance_value_map)) # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len( row['awarding_agency_code'].strip()) < 1: awarding_subtier_agency_id = subtier_agency_map[ row["awarding_sub_tier_agency_c"]] awarding_toptier_agency_id = subtier_to_agency_map[ awarding_subtier_agency_id]['toptier_agency_id'] awarding_cgac_code = toptier_agency_map[ awarding_toptier_agency_id] row['awarding_agency_code'] = awarding_cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len( row['funding_agency_code'].strip()) < 1: funding_subtier_agency_id = subtier_agency_map.get( row["funding_sub_tier_agency_co"]) if funding_subtier_agency_id is not None: funding_toptier_agency_id = \ subtier_to_agency_map[funding_subtier_agency_id]['toptier_agency_id'] funding_cgac_code = toptier_agency_map[ funding_toptier_agency_id] else: funding_cgac_code = None row['funding_agency_code'] = funding_cgac_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"]) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_piid=row.get('parent_award_id')) award.save() award_update_id_list.append(award.id) award_contract_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier( row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True) transaction = TransactionNormalized.get_or_create_transaction( **transaction_dict) transaction.save() contract_instance = load_data_into_model( TransactionFPDS(), # thrown away row, as_dict=True) transaction_contract = TransactionFPDS(transaction=transaction, **contract_instance) # catch exception and do nothing if we see # "django.db.utils.IntegrityError: duplicate key value violates unique constraint" try: transaction_contract.save() except IntegrityError: pass
def insert_new_fabs(self, to_insert, total_rows): logger.info('Starting insertion of new FABS data') 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" } start_time = datetime.now() for index, row in enumerate(to_insert, 1): if not (index % 1000): logger.info('Inserting Stale FABS: Inserting row {} of {} ({})'.format(str(index), str(total_rows), datetime.now() - start_time)) for key in row: if isinstance(row[key], str): row[key] = row[key].upper() # 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 "" ) legal_entity_value_map = { "location": legal_entity_location, "business_categories": get_business_categories(row=row, data_type='fabs'), "business_types_description": get_business_type_description(row['business_types']) } 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 == 2: uri = '-NONE-' fain = row['fain'] if row['fain'] else '-NONE-' else: raise Exception('Invalid record type encountered for the following afa_generated_unique record: %s' % row['afa_generated_unique']) generated_unique_id = 'ASST_AW_' +\ (row['awarding_sub_tier_agency_c'] if row['awarding_sub_tier_agency_c'] else '-NONE-') + '_' + \ 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": get_assistance_type_description(row['assistance_type']), "transaction_unique_id": row['afa_generated_unique'], "generated_unique_award_id": generated_unique_id } fad_field_map = { "type": "assistance_type", "description": "award_description", } 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(), # thrown away row, as_dict=True) 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.utcnow() 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()
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", } fpds_normalized_field_map = { "type": "contract_award_type", "description": "award_description" } fpds_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" } 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"]) # Create the summary Award (created, award) = Award.get_or_create_summary_award( generated_unique_award_id=row["unique_award_key"], 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"], "is_fpds": True, "type": award_type, "type_description": award_type_desc, } transaction_normalized_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fpds_normalized_field_map, value_map=parent_txn_value_map, as_dict=True, ) contract_instance = load_data_into_model( TransactionFPDS(), # thrown away row, field_map=fpds_field_map, as_dict=True ) 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", } 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 update_transaction_assistance(db_cursor, fiscal_year=None, page=1, limit=500000): query = "SELECT * FROM published_award_financial_assistance" arguments = [] fy_begin = "10/01/" + str(fiscal_year - 1) fy_end = "09/30/" + str(fiscal_year) if fiscal_year: if arguments: query += " AND" else: query += " WHERE" query += " action_date::Date BETWEEN %s AND %s" arguments += [fy_begin] arguments += [fy_end] query += " ORDER BY published_award_financial_assistance_id LIMIT %s OFFSET %s" arguments += [limit, (page - 1) * limit] logger.info("Executing query on Broker DB => " + query % (arguments[0], arguments[1], arguments[2], arguments[3])) db_cursor.execute(query, arguments) logger.info("Running dictfetchall on db_cursor") award_financial_assistance_data = dictfetchall(db_cursor) 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", } logger.info("Getting total rows") total_rows = len(award_financial_assistance_data) # - rows_loaded logger.info("Processing " + str(total_rows) + " rows of assistance data") # ROW ITERATION STARTS HERE award_bulk = [] transaction_assistance_bulk = [] transaction_normalized_bulk = [] awarding_agency_list = [] funding_agency_list = [] logger.info("Getting award objects for {} rows...".format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row["awarding_agency_code"] is None or len( row["awarding_agency_code"].strip()) < 1: awarding_subtier_agency_id = subtier_agency_map[ row["awarding_sub_tier_agency_c"]] awarding_toptier_agency_id = subtier_to_agency_map[ awarding_subtier_agency_id]["toptier_agency_id"] awarding_toptier_code = toptier_agency_map[ awarding_toptier_agency_id] row["awarding_agency_code"] = awarding_toptier_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row["funding_agency_code"] is None or len( row["funding_agency_code"].strip()) < 1: funding_subtier_agency_id = subtier_agency_map.get( row["funding_sub_tier_agency_co"]) if funding_subtier_agency_id is not None: funding_toptier_agency_id = subtier_to_agency_map[ funding_subtier_agency_id]["toptier_agency_id"] funding_toptier_code = toptier_agency_map[ funding_toptier_agency_id] else: funding_toptier_code = None row["funding_agency_code"] = funding_toptier_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row["awarding_agency_code"], row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_toptier_subtier( row["funding_agency_code"], row["funding_sub_tier_agency_co"]) awarding_agency_list.append(awarding_agency) funding_agency_list.append(funding_agency) # award.save() is called in Award.get_or_create_summary_award by default created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, fain=row.get("fain"), uri=row.get("uri"), generated_unique_award_id=row.get("unique_award_key"), save=False, ) award_bulk.append(award) award_update_id_list.append(award.id) award_assistance_update_id_list.append(award.id) logger.info("Bulk creating {} award rows...".format(len(award_bulk))) try: Award.objects.bulk_create(award_bulk) except IntegrityError: logger.info("!!! DUPLICATES FOUND. Continuing... ") logger.info("Getting transaction_normalized for {} rows...".format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): parent_txn_value_map = { "award": award_bulk[index - 1], "awarding_agency": awarding_agency_list[index - 1], "funding_agency": funding_agency_list[index - 1], "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"]), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fabs_normalized_field_map, value_map=parent_txn_value_map, as_dict=True, ) transaction_normalized = TransactionNormalized.get_or_create_transaction( **transaction_dict) transaction_normalized.fiscal_year = fy( transaction_normalized.action_date) transaction_normalized_bulk.append(transaction_normalized) logger.info("Bulk creating {} TransactionNormalized rows...".format( len(transaction_normalized_bulk))) try: TransactionNormalized.objects.bulk_create( transaction_normalized_bulk) except IntegrityError: logger.info( "Tried and failed to insert duplicate transaction_normalized row. Continuing... " ) for index, row in enumerate(award_financial_assistance_data, 1): financial_assistance_data = load_data_into_model( TransactionFABS(), row, field_map=fabs_field_map, as_dict=True # thrown away ) transaction_assistance = TransactionFABS( transaction=transaction_normalized_bulk[index - 1], **financial_assistance_data) transaction_assistance_bulk.append(transaction_assistance) logger.info("Bulk creating TransactionFABS rows...") try: TransactionFABS.objects.bulk_create(transaction_assistance_bulk) except IntegrityError: logger.info("!!! DUPLICATES FOUND. Continuing... ")
def update_transaction_contract(db_cursor, fiscal_year=None, page=1, limit=500000): # logger.info("Getting IDs for what's currently in the DB...") # current_ids = TransactionFPDS.objects # # if fiscal_year: # current_ids = current_ids.filter(action_date__fy=fiscal_year) # # current_ids = current_ids.values_list('detached_award_procurement_id', flat=True) query = "SELECT * FROM detached_award_procurement" arguments = [] fy_begin = "10/01/" + str(fiscal_year - 1) fy_end = "09/30/" + str(fiscal_year) if fiscal_year: if arguments: query += " AND" else: query += " WHERE" query += " action_date::Date BETWEEN %s AND %s" arguments += [fy_begin] arguments += [fy_end] query += " ORDER BY detached_award_procurement_id LIMIT %s OFFSET %s" arguments += [limit, (page - 1) * limit] logger.info("Executing query on Broker DB => " + query % (arguments[0], arguments[1], arguments[2], arguments[3])) db_cursor.execute(query, arguments) logger.info("Running dictfetchall on db_cursor") procurement_data = dictfetchall(db_cursor) fpds_normalized_field_map = { "type": "contract_award_type", "description": "award_description" } fpds_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", } logger.info("Getting total rows") total_rows = len(procurement_data) # - rows_loaded logger.info("Processing " + str(total_rows) + " rows of procurement data") start_time = datetime.now() for index, row in enumerate(procurement_data, 1): with db_transaction.atomic(): if not (index % 100): logger.info( "D1 File Load: Loading row {} of {} ({})".format( str(index), str(total_rows), datetime.now() - start_time)) # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row["awarding_agency_code"] is None or len( row["awarding_agency_code"].strip()) < 1: awarding_subtier_agency_id = subtier_agency_map[ row["awarding_sub_tier_agency_c"]] awarding_toptier_agency_id = subtier_to_agency_map[ awarding_subtier_agency_id]["toptier_agency_id"] awarding_toptier_code = toptier_agency_map[ awarding_toptier_agency_id] row["awarding_agency_code"] = awarding_toptier_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row["funding_agency_code"] is None or len( row["funding_agency_code"].strip()) < 1: funding_subtier_agency_id = subtier_agency_map.get( row["funding_sub_tier_agency_co"]) if funding_subtier_agency_id is not None: funding_toptier_agency_id = subtier_to_agency_map[ funding_subtier_agency_id]["toptier_agency_id"] funding_toptier_code = toptier_agency_map[ funding_toptier_agency_id] else: funding_toptier_code = None row["funding_agency_code"] = funding_toptier_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row["awarding_agency_code"], row["awarding_sub_tier_agency_c"]) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get("piid"), fain=row.get("fain"), uri=row.get("uri"), parent_award_piid=row.get("parent_award_id"), generated_unique_award_id=row.get("unique_award_key"), ) award.save() award_update_id_list.append(award.id) award_contract_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier( row["funding_agency_code"], row["funding_sub_tier_agency_co"]), "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"]), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fpds_normalized_field_map, value_map=parent_txn_value_map, as_dict=True, ) transaction = TransactionNormalized.get_or_create_transaction( **transaction_dict) transaction.save() contract_instance = load_data_into_model( TransactionFPDS(), row, field_map=fpds_field_map, as_dict=True # thrown away ) transaction_contract = TransactionFPDS(transaction=transaction, **contract_instance) # catch exception and do nothing if we see # "django.db.utils.IntegrityError: duplicate key value violates unique constraint" try: transaction_contract.save() except IntegrityError: pass
def load_file_d2( submission_attributes, award_financial_assistance_data, db_cursor, quick, row_preprocessor=no_preprocessing ): """ Process and load file D2 broker data (financial assistance award txns). """ d_start_time = time.time() if quick: setup_broker_fdw() parameters = {'broker_submission_id': submission_attributes.broker_submission_id} run_sql_file('usaspending_api/etl/management/load_file_d2.sql', parameters) logger.info('\n\n\n\nFile D2 time elapsed: {}'.format(time.time() - d_start_time)) return legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "city_code": "legal_entity_city_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "county_code": "legal_entity_county_code", "county_name": "legal_entity_county_name", "foreign_city_name": "legal_entity_foreign_city", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "zip5": "legal_entity_zip5", "zip_last4": "legal_entity_zip_last4", "location_country_code": "legal_entity_country_code" } place_of_performance_field_map = { "city_name": "place_of_performance_city", "performance_code": "place_of_performance_code", "congressional_code": "place_of_performance_congr", "county_name": "place_of_perform_county_na", "foreign_location_description": "place_of_performance_forei", "state_name": "place_of_perform_state_nam", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } legal_entity_location_value_map = { "recipient_flag": True } place_of_performance_value_map = { "place_of_performance_flag": True } fad_field_map = { "type": "assistance_type", "description": "award_description", } total_rows = len(award_financial_assistance_data) start_time = datetime.now() for index, row in enumerate(award_financial_assistance_data, 1): if not (index % 100): logger.info('D2 File Load: Loading row {} of {} ({})'.format(str(index), str(total_rows), datetime.now() - start_time)) row = row_preprocessor(row) legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, legal_entity_location_value_map ) recipient_name = row['awardee_or_recipient_legal'] if recipient_name is None: recipient_name = "" # Create the legal entity if it doesn't exist legal_entity, created = LegalEntity.objects.get_or_create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name ) if created: legal_entity_value_map = { "location": legal_entity_location, } 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, created = get_or_create_location( place_of_performance_field_map, row, place_of_performance_value_map ) # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len(row['awarding_agency_code'].strip()) < 1: row['awarding_agency_code'] = Agency.get_by_subtier( row["awarding_sub_tier_agency_c"]).toptier_agency.cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len(row['funding_agency_code'].strip()) < 1: funding_agency = Agency.get_by_subtier(row["funding_sub_tier_agency_co"]) row['funding_agency_code'] = ( funding_agency.toptier_agency.cgac_code if funding_agency is not None else None) # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"] ) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri')) award.save() award_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "recipient": legal_entity, "place_of_performance": pop_location, 'submission': submission_attributes, "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True) transaction = TransactionNormalized.get_or_create_transaction(**transaction_dict) transaction.save() fad_value_map = { "submission": submission_attributes, "cfda": Cfda.objects.filter(program_number=row['cfda_number']).first(), 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, "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']), } financial_assistance_data = load_data_into_model( TransactionFABS(), # thrown away row, field_map=fad_field_map, value_map=fad_value_map, as_dict=True) transaction_assistance = TransactionFABS.get_or_create_2(transaction=transaction, **financial_assistance_data) transaction_assistance.save() logger.info('\n\n\n\nFile D2 time elapsed: {}'.format(time.time() - d_start_time))
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) try: last_mod_date = datetime.strptime(str( row["last_modified"]), "%Y-%m-%d %H:%M:%S.%f").date() except ValueError: last_mod_date = datetime.strptime(str(row["last_modified"]), "%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, "transaction_unique_id": row["detached_award_proc_unique"], "generated_unique_award_id": generated_unique_id, "is_fpds": True, } contract_field_map = { "type": "contract_award_type", "type_description": "contract_award_type_desc", "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 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 reverse = re.compile(r'(_(cpe|fyb)$)|^transaction_obligated_amount$') 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) # for row in award_financial_data: for row in award_financial_frame.replace({ np.nan: None }).to_dict(orient='records'): # Check and see if there is an entry for this TAS treasury_account = get_treasury_appropriation_account_tas_lookup( row.get('tas_id'), db_cursor) if treasury_account is None: raise Exception('Could not find appropriation account for TAS: ' + row['tas']) # 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. created, award = Award.get_or_create_summary_award( awarding_agency=row['awarding_agency'], piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_id=row.get('parent_award_id'), use_cache=False) award_financial_data = FinancialAccountsByAwards() value_map = { '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 afd = load_data_into_model(award_financial_data, row, value_map=value_map, save=True, reverse=reverse) awards_cache.clear()
def load_file_d1(submission_attributes, procurement_data, db_cursor, quick=False): """ Process and load file D1 broker data (contract award txns). """ legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "location_country_code": "legal_entity_country_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "state_code": "legal_entity_state_code", "zip4": "legal_entity_zip4" } place_of_performance_field_map = { # not sure place_of_performance_locat maps exactly to city name "city_name": "place_of_performance_locat", "congressional_code": "place_of_performance_congr", "state_code": "place_of_performance_state", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } place_of_performance_value_map = {"place_of_performance_flag": True} legal_entity_location_value_map = {"recipient_flag": True} contract_field_map = { "type": "contract_award_type", "description": "award_description" } d_start_time = time.time() if quick: parameters = { 'broker_submission_id': submission_attributes.broker_submission_id } run_sql_file('usaspending_api/etl/management/load_file_d1.sql', parameters) logger.info('\n\n\n\nFile D1 time elapsed: {}'.format(time.time() - d_start_time)) return total_rows = len(procurement_data) start_time = datetime.now() for index, row in enumerate(procurement_data, 1): if not (index % 100): logger.info('D1 File Load: Loading row {} of {} ({})'.format( str(index), str(total_rows), datetime.now() - start_time)) legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, copy(legal_entity_location_value_map)) recipient_name = row['awardee_or_recipient_legal'] if recipient_name is None: recipient_name = "" # Create the legal entity if it doesn't exist legal_entity, created = LegalEntity.objects.get_or_create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name) if created: legal_entity_value_map = { "location": legal_entity_location, } 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, created = get_or_create_location( place_of_performance_field_map, row, copy(place_of_performance_value_map)) # If awarding toptier agency code (aka CGAC) is not supplied on the D1 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len( row['awarding_agency_code'].strip()) < 1: row['awarding_agency_code'] = Agency.get_by_subtier( row["awarding_sub_tier_agency_c"]).toptier_agency.cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len( row['funding_agency_code'].strip()) < 1: funding_agency = Agency.get_by_subtier( row["funding_sub_tier_agency_co"]) row['funding_agency_code'] = ( funding_agency.toptier_agency.cgac_code if funding_agency is not None else None) # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"]) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_piid=row.get( 'parent_award_id')) # It is a FAIN/PIID/URI, not our db's pk award.save() award_update_id_list.append(award.id) award_contract_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "recipient": legal_entity, "place_of_performance": pop_location, 'submission': submission_attributes, "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True) transaction = TransactionNormalized.get_or_create_transaction( **transaction_dict) transaction.save() contract_value_map = { 'submission': submission_attributes, 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, "period_of_performance_potential_end_date": format_date(row['period_of_perf_potential_e']) } contract_instance = load_data_into_model( TransactionFPDS(), # thrown away row, field_map=contract_field_map, value_map=contract_value_map, as_dict=True) transaction_contract = TransactionFPDS(transaction=transaction, **contract_instance) transaction_contract.save() logger.info('\n\n\n\nFile D1 time elapsed: {}'.format(time.time() - d_start_time))
def update_transaction_assistance(db_cursor, fiscal_year=None, page=1, limit=500000): # logger.info("Getting IDs for what's currently in the DB...") # current_ids = TransactionFABS.objects # # if fiscal_year: # current_ids = current_ids.filter(action_date__fy=fiscal_year) # # current_ids = current_ids.values_list('published_award_financial_assistance_id', flat=True) query = "SELECT * FROM published_award_financial_assistance" arguments = [] fy_begin = '10/01/' + str(fiscal_year - 1) fy_end = '09/30/' + str(fiscal_year) if fiscal_year: if arguments: query += " AND" else: query += " WHERE" query += ' action_date::Date BETWEEN %s AND %s' arguments += [fy_begin] arguments += [fy_end] query += ' ORDER BY published_award_financial_assistance_id LIMIT %s OFFSET %s' arguments += [limit, (page - 1) * limit] logger.info("Executing query on Broker DB => " + query % (arguments[0], arguments[1], arguments[2], arguments[3])) db_cursor.execute(query, arguments) logger.info("Running dictfetchall on db_cursor") award_financial_assistance_data = dictfetchall(db_cursor) legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "county_code": "legal_entity_county_code", "county_name": "legal_entity_county_name", "foreign_city_name": "legal_entity_foreign_city", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "zip5": "legal_entity_zip5", "zip_last4": "legal_entity_zip_last4", "location_country_code": "legal_entity_country_code" } place_of_performance_field_map = { "city_name": "place_of_performance_city", "performance_code": "place_of_performance_code", "congressional_code": "place_of_performance_congr", "county_name": "place_of_perform_county_na", "foreign_location_description": "place_of_performance_forei", "state_name": "place_of_perform_state_nam", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } fad_field_map = { "type": "assistance_type", "description": "award_description", } logger.info("Getting total rows") # rows_loaded = len(current_ids) total_rows = len(award_financial_assistance_data) # - rows_loaded logger.info("Processing " + str(total_rows) + " rows of assistance data") # skip_count = 0 # ROW ITERATION STARTS HERE lel_bulk = [] pop_bulk = [] legal_entity_bulk = [] award_bulk = [] transaction_assistance_bulk = [] transaction_normalized_bulk = [] logger.info( 'Getting legal entity location objects for {} rows...'.format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # Recipient flag is true for LeL legal_entity_location = get_or_create_location( legal_entity_location_field_map, row, {"recipient_flag": True}, save=False) lel_bulk.append(legal_entity_location) logger.info('Bulk creating {} legal entity location rows...'.format( len(lel_bulk))) try: Location.objects.bulk_create(lel_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') logger.info( 'Getting place of performance objects for {} rows...'.format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # Place of Performance flag is true for PoP pop_location = get_or_create_location( place_of_performance_field_map, row, {"place_of_performance_flag": True}, save=False) pop_bulk.append(pop_location) logger.info('Bulk creating {} place of performance rows...'.format( len(pop_bulk))) try: Location.objects.bulk_create(pop_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') logger.info('Getting legal entity objects for {} rows...'.format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): recipient_name = row.get('awardee_or_recipient_legal', '') legal_entity = LegalEntity.objects.filter( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name).first() if legal_entity is None: legal_entity = LegalEntity( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name) legal_entity_value_map = { "location": lel_bulk[index - 1], } legal_entity = load_data_into_model( legal_entity, row, value_map=legal_entity_value_map, save=False) legal_entity_bulk.append(legal_entity) logger.info('Bulk creating {} legal entity rows...'.format( len(legal_entity_bulk))) try: LegalEntity.objects.bulk_create(legal_entity_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') awarding_agency_list = [] funding_agency_list = [] logger.info('Getting award objects for {} rows...'.format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len( row['awarding_agency_code'].strip()) < 1: awarding_subtier_agency_id = subtier_agency_map[ row["awarding_sub_tier_agency_c"]] awarding_toptier_agency_id = subtier_to_agency_map[ awarding_subtier_agency_id]['toptier_agency_id'] awarding_cgac_code = toptier_agency_map[ awarding_toptier_agency_id] row['awarding_agency_code'] = awarding_cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len( row['funding_agency_code'].strip()) < 1: funding_subtier_agency_id = subtier_agency_map.get( row["funding_sub_tier_agency_co"]) if funding_subtier_agency_id is not None: funding_toptier_agency_id = \ subtier_to_agency_map[funding_subtier_agency_id]['toptier_agency_id'] funding_cgac_code = toptier_agency_map[ funding_toptier_agency_id] else: funding_cgac_code = None row['funding_agency_code'] = funding_cgac_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"]) funding_agency = Agency.get_by_toptier_subtier( row['funding_agency_code'], row["funding_sub_tier_agency_co"]) awarding_agency_list.append(awarding_agency) funding_agency_list.append(funding_agency) # award.save() is called in Award.get_or_create_summary_award by default created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, fain=row.get('fain'), uri=row.get('uri'), save=False) award_bulk.append(award) award_update_id_list.append(award.id) logger.info('Bulk creating {} award rows...'.format(len(award_bulk))) try: Award.objects.bulk_create(award_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') logger.info('Getting transaction_normalized for {} rows...'.format( len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): parent_txn_value_map = { "award": award_bulk[index - 1], "awarding_agency": awarding_agency_list[index - 1], "funding_agency": funding_agency_list[index - 1], "recipient": legal_entity_bulk[index - 1], "place_of_performance": pop_bulk[index - 1], "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True) transaction_normalized = TransactionNormalized.get_or_create_transaction( **transaction_dict) transaction_normalized.fiscal_year = fy( transaction_normalized.action_date) transaction_normalized_bulk.append(transaction_normalized) logger.info('Bulk creating {} TransactionNormalized rows...'.format( len(transaction_normalized_bulk))) try: TransactionNormalized.objects.bulk_create( transaction_normalized_bulk) except IntegrityError: logger.info( 'Tried and failed to insert duplicate transaction_normalized row. Continuing... ' ) for index, row in enumerate(award_financial_assistance_data, 1): financial_assistance_data = load_data_into_model( TransactionFABS(), # thrown away row, as_dict=True) transaction_assistance = TransactionFABS( transaction=transaction_normalized_bulk[index - 1], **financial_assistance_data) transaction_assistance_bulk.append(transaction_assistance) logger.info('Bulk creating TransactionFABS rows...') try: TransactionFABS.objects.bulk_create(transaction_assistance_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ')
def load_file_d2(submission_attributes, award_financial_assistance_data, db_cursor, quick, row_preprocessor=no_preprocessing): """ Process and load file D2 broker data (financial assistance award txns). """ d_start_time = time.time() if quick: setup_broker_fdw() parameters = { 'broker_submission_id': submission_attributes.broker_submission_id } run_sql_file('usaspending_api/etl/management/load_file_d2.sql', parameters) logger.info('\n\n\n\nFile D2 time elapsed: {}'.format(time.time() - d_start_time)) return legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "city_code": "legal_entity_city_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "county_code": "legal_entity_county_code", "county_name": "legal_entity_county_name", "foreign_city_name": "legal_entity_foreign_city", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "zip5": "legal_entity_zip5", "zip_last4": "legal_entity_zip_last4", "location_country_code": "legal_entity_country_code" } place_of_performance_field_map = { "city_name": "place_of_performance_city", "performance_code": "place_of_performance_code", "congressional_code": "place_of_performance_congr", "county_name": "place_of_perform_county_na", "foreign_location_description": "place_of_performance_forei", "state_name": "place_of_perform_state_nam", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } legal_entity_location_value_map = {"recipient_flag": True} place_of_performance_value_map = {"place_of_performance_flag": True} fad_field_map = { "type": "assistance_type", "description": "award_description", } total_rows = len(award_financial_assistance_data) start_time = datetime.now() for index, row in enumerate(award_financial_assistance_data, 1): if not (index % 100): logger.info('D2 File Load: Loading row {} of {} ({})'.format( str(index), str(total_rows), datetime.now() - start_time)) row = row_preprocessor(row) legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, legal_entity_location_value_map) recipient_name = row['awardee_or_recipient_legal'] if recipient_name is None: recipient_name = "" # Create the legal entity if it doesn't exist legal_entity, created = LegalEntity.objects.get_or_create( recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name) if created: legal_entity_value_map = { "location": legal_entity_location, } 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, created = get_or_create_location( place_of_performance_field_map, row, place_of_performance_value_map) # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len( row['awarding_agency_code'].strip()) < 1: row['awarding_agency_code'] = Agency.get_by_subtier( row["awarding_sub_tier_agency_c"]).toptier_agency.cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len( row['funding_agency_code'].strip()) < 1: funding_agency = Agency.get_by_subtier( row["funding_sub_tier_agency_co"]) row['funding_agency_code'] = ( funding_agency.toptier_agency.cgac_code if funding_agency is not None else None) # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"]) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri')) award.save() award_update_id_list.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "recipient": legal_entity, "place_of_performance": pop_location, 'submission': submission_attributes, "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True) transaction = TransactionNormalized.get_or_create_transaction( **transaction_dict) transaction.save() fad_value_map = { "submission": submission_attributes, "cfda": Cfda.objects.filter(program_number=row['cfda_number']).first(), 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, "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']), } financial_assistance_data = load_data_into_model( TransactionFABS(), # thrown away row, field_map=fad_field_map, value_map=fad_value_map, as_dict=True) transaction_assistance = TransactionFABS.get_or_create_2( transaction=transaction, **financial_assistance_data) transaction_assistance.save() logger.info('\n\n\n\nFile D2 time elapsed: {}'.format(time.time() - d_start_time))
def update_transaction_assistance(db_cursor, fiscal_year=None, page=1, limit=500000): # logger.info("Getting IDs for what's currently in the DB...") # current_ids = TransactionFABS.objects # # if fiscal_year: # current_ids = current_ids.filter(action_date__fy=fiscal_year) # # current_ids = current_ids.values_list('published_award_financial_assistance_id', flat=True) query = "SELECT * FROM published_award_financial_assistance" arguments = [] fy_begin = '10/01/' + str(fiscal_year - 1) fy_end = '09/30/' + str(fiscal_year) if fiscal_year: if arguments: query += " AND" else: query += " WHERE" query += ' action_date::Date BETWEEN %s AND %s' arguments += [fy_begin] arguments += [fy_end] query += ' ORDER BY published_award_financial_assistance_id LIMIT %s OFFSET %s' arguments += [limit, (page-1)*limit] logger.info("Executing query on Broker DB => " + query % (arguments[0], arguments[1], arguments[2], arguments[3])) db_cursor.execute(query, arguments) logger.info("Running dictfetchall on db_cursor") award_financial_assistance_data = dictfetchall(db_cursor) legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "county_code": "legal_entity_county_code", "county_name": "legal_entity_county_name", "foreign_city_name": "legal_entity_foreign_city", "foreign_postal_code": "legal_entity_foreign_posta", "foreign_province": "legal_entity_foreign_provi", "state_code": "legal_entity_state_code", "state_name": "legal_entity_state_name", "zip5": "legal_entity_zip5", "zip_last4": "legal_entity_zip_last4", "location_country_code": "legal_entity_country_code" } place_of_performance_field_map = { "city_name": "place_of_performance_city", "performance_code": "place_of_performance_code", "congressional_code": "place_of_performance_congr", "county_name": "place_of_perform_county_na", "foreign_location_description": "place_of_performance_forei", "state_name": "place_of_perform_state_nam", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } fad_field_map = { "type": "assistance_type", "description": "award_description", } logger.info("Getting total rows") # rows_loaded = len(current_ids) total_rows = len(award_financial_assistance_data) # - rows_loaded logger.info("Processing " + str(total_rows) + " rows of assistance data") # skip_count = 0 # ROW ITERATION STARTS HERE lel_bulk = [] pop_bulk = [] legal_entity_bulk = [] award_bulk = [] transaction_assistance_bulk = [] transaction_normalized_bulk = [] logger.info('Getting legal entity location objects for {} rows...'.format(len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # Recipient flag is true for LeL legal_entity_location = get_or_create_location( legal_entity_location_field_map, row, {"recipient_flag": True}, save=False ) lel_bulk.append(legal_entity_location) logger.info('Bulk creating {} legal entity location rows...'.format(len(lel_bulk))) try: Location.objects.bulk_create(lel_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') logger.info('Getting place of performance objects for {} rows...'.format(len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # Place of Performance flag is true for PoP pop_location = get_or_create_location( place_of_performance_field_map, row, {"place_of_performance_flag": True}, save=False ) pop_bulk.append(pop_location) logger.info('Bulk creating {} place of performance rows...'.format(len(pop_bulk))) try: Location.objects.bulk_create(pop_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') logger.info('Getting legal entity objects for {} rows...'.format(len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): recipient_name = row.get('awardee_or_recipient_legal', '') legal_entity = LegalEntity.objects.filter(recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name).first() if legal_entity is None: legal_entity = LegalEntity(recipient_unique_id=row['awardee_or_recipient_uniqu'], recipient_name=recipient_name) legal_entity_value_map = { "location": lel_bulk[index - 1], } legal_entity = load_data_into_model(legal_entity, row, value_map=legal_entity_value_map, save=False) legal_entity_bulk.append(legal_entity) logger.info('Bulk creating {} legal entity rows...'.format(len(legal_entity_bulk))) try: LegalEntity.objects.bulk_create(legal_entity_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') awarding_agency_list = [] funding_agency_list = [] logger.info('Getting award objects for {} rows...'.format(len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): # If awarding toptier agency code (aka CGAC) is not supplied on the D2 record, # use the sub tier code to look it up. This code assumes that all incoming # records will supply an awarding subtier agency code if row['awarding_agency_code'] is None or len(row['awarding_agency_code'].strip()) < 1: awarding_subtier_agency_id = subtier_agency_map[row["awarding_sub_tier_agency_c"]] awarding_toptier_agency_id = subtier_to_agency_map[awarding_subtier_agency_id]['toptier_agency_id'] awarding_cgac_code = toptier_agency_map[awarding_toptier_agency_id] row['awarding_agency_code'] = awarding_cgac_code # If funding toptier agency code (aka CGAC) is empty, try using the sub # tier funding code to look it up. Unlike the awarding agency, we can't # assume that the funding agency subtier code will always be present. if row['funding_agency_code'] is None or len(row['funding_agency_code'].strip()) < 1: funding_subtier_agency_id = subtier_agency_map.get(row["funding_sub_tier_agency_co"]) if funding_subtier_agency_id is not None: funding_toptier_agency_id = \ subtier_to_agency_map[funding_subtier_agency_id]['toptier_agency_id'] funding_cgac_code = toptier_agency_map[funding_toptier_agency_id] else: funding_cgac_code = None row['funding_agency_code'] = funding_cgac_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"] ) funding_agency = Agency.get_by_toptier_subtier( row['funding_agency_code'], row["funding_sub_tier_agency_co"] ) awarding_agency_list.append(awarding_agency) funding_agency_list.append(funding_agency) # award.save() is called in Award.get_or_create_summary_award by default created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, fain=row.get('fain'), uri=row.get('uri'), save=False ) award_bulk.append(award) award_update_id_list.append(award.id) logger.info('Bulk creating {} award rows...'.format(len(award_bulk))) try: Award.objects.bulk_create(award_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ') logger.info('Getting transaction_normalized for {} rows...'.format(len(award_financial_assistance_data))) for index, row in enumerate(award_financial_assistance_data, 1): parent_txn_value_map = { "award": award_bulk[index - 1], "awarding_agency": awarding_agency_list[index - 1], "funding_agency": funding_agency_list[index - 1], "recipient": legal_entity_bulk[index - 1], "place_of_performance": pop_bulk[index - 1], "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']), } transaction_dict = load_data_into_model( TransactionNormalized(), # thrown away row, field_map=fad_field_map, value_map=parent_txn_value_map, as_dict=True) transaction_normalized = TransactionNormalized.get_or_create_transaction(**transaction_dict) transaction_normalized.fiscal_year = fy(transaction_normalized.action_date) transaction_normalized_bulk.append(transaction_normalized) logger.info('Bulk creating {} TransactionNormalized rows...'.format(len(transaction_normalized_bulk))) try: TransactionNormalized.objects.bulk_create(transaction_normalized_bulk) except IntegrityError: logger.info('Tried and failed to insert duplicate transaction_normalized row. Continuing... ') for index, row in enumerate(award_financial_assistance_data, 1): financial_assistance_data = load_data_into_model( TransactionFABS(), # thrown away row, as_dict=True) transaction_assistance = TransactionFABS(transaction=transaction_normalized_bulk[index - 1], **financial_assistance_data) transaction_assistance_bulk.append(transaction_assistance) logger.info('Bulk creating TransactionFABS rows...') try: TransactionFABS.objects.bulk_create(transaction_assistance_bulk) except IntegrityError: logger.info('!!! DUPLICATES FOUND. Continuing... ')
def test_get_or_create_summary_award(): """Test award record lookup.""" sta1 = mommy.make(SubtierAgency, subtier_code='1234', name='Bureau of Effective Unit Tests') sta2 = mommy.make(SubtierAgency, subtier_code='5678', name='Bureau of Breaky Unit Tests') sta3 = mommy.make(SubtierAgency, subtier_code='0509', name='Bureau of Testing Bureaucracy') tta1 = mommy.make(ToptierAgency, cgac_code='020', name='Department of Unit Tests') tta2 = mommy.make(ToptierAgency, cgac_code='089', name='Department of Integrated Tests') a1 = mommy.make(Agency, id=1, toptier_agency=tta1, subtier_agency=sta1) a2 = mommy.make(Agency, id=2, toptier_agency=tta1) a3 = mommy.make(Agency, id=3, toptier_agency=tta1, subtier_agency=sta2) a4 = mommy.make(Agency, id=4, toptier_agency=tta2, subtier_agency=sta3) # match on awarding agency and piid m1 = mommy.make('awards.award', piid='DUT123', awarding_agency=a1) t1 = Award.get_or_create_summary_award(piid='DUT123', awarding_agency=a1)[1] assert t1 == m1 # match on awarding agency and piid + parent award pa1 = mommy.make('awards.award', piid='IDVDUT456') m2 = mommy.make('awards.award', piid='DUT456', parent_award=pa1, awarding_agency=a1) t2 = Award.get_or_create_summary_award(piid='DUT456', parent_award_id='IDVDUT456', awarding_agency=a1)[1] assert t2 == m2 # match on awarding agency and fain m3 = mommy.make('awards.award', fain='DUT789', awarding_agency=a1) t3 = Award.get_or_create_summary_award(fain='DUT789', awarding_agency=a1)[1] assert t3 == m3 # match on awarding agency and fain + uri (fain takes precedence, same uri) m4 = mommy.make('awards.award', fain='DUT987', uri='123-abc-456', awarding_agency=a1) t4 = Award.get_or_create_summary_award(fain='DUT987', uri='123-abc-456', awarding_agency=a1)[1] assert t4 == m4 # match on awarding agency and fain + uri (fain takes precedence, different uri) m5 = mommy.make('awards.award', fain='DUT123456', uri='123-abc-456', awarding_agency=a1) t5 = Award.get_or_create_summary_award(fain='DUT123456', uri='123-abc-456-a-different-uri', awarding_agency=a1)[1] assert t5 == m5 # match on awarding agency + uri m6 = mommy.make('awards.award', uri='abc-123-def', awarding_agency=a1) t6 = Award.get_or_create_summary_award(uri='abc-123-def', awarding_agency=a1)[1] assert t6 == m6 # match on awarding toptier agency only m7 = mommy.make('awards.award', uri='kkk-bbb-jjj', awarding_agency=a2) t7 = Award.get_or_create_summary_award(uri='kkk-bbb-jjj', awarding_agency=a2)[1] assert m7 == t7 # match on no awarding agency, uri m8 = mommy.make('awards.award', uri='mmm-fff-ddd') t8 = Award.get_or_create_summary_award(uri='mmm-fff-ddd')[1] assert m8 == t8 # match on no awarding agency, fain m9 = mommy.make('awards.award', fain='DUTDUTDUT') t9 = Award.get_or_create_summary_award(fain='DUTDUTDUT')[1] assert m9 == t9 # non-match with piid creates new award record m10 = mommy.make('awards.award', piid='imapiid') t10 = Award.get_or_create_summary_award(piid='imadifferentpiid') assert len(t10[0]) == 1 assert m10 != t10[1] # non-match with piid + non-matching parent award creates two new awards pa11 = mommy.make('awards.award', piid='momofpiidsarefun') m11 = mommy.make('awards.award', piid='piidsarefun', parent_award=pa11) t11 = Award.get_or_create_summary_award(piid='piidsarefun', parent_award_id='dadofpiidsarefun') assert len(t11[0]) == 2 assert m11 != t11[1] # non-match with piid + matching parent award creates one new award pa12 = mommy.make('awards.award', piid='thingmom') m12 = mommy.make('awards.award', piid='thing1', parent_award=pa12) t12 = Award.get_or_create_summary_award(piid='thing2', parent_award_id='thingmom') assert len(t12[0]) == 1 assert m12 != t12[1] # matching piid + non-matching parent pa13 = mommy.make('awards.award', piid='piidthing') m13 = mommy.make('awards.award', piid='0005', parent_award=pa13) t13 = Award.get_or_create_summary_award( piid='0005', parent_award_id='anotherpiidthing')[1] assert t13 != m13 # matching piid and parent award id but mismatched subtier agency, same top tier agency pa14 = mommy.make('awards.award', piid='imalittlepiid', awarding_agency=a1) m14 = mommy.make('awards.award', piid='shortandstout', parent_award=pa14, awarding_agency=a1) t14 = Award.get_or_create_summary_award(piid='shortandstout', parent_award_id='imalittlepiid', awarding_agency=a3)[1] assert t14 != m14 # matching fain but mismatched subtier agency, same top tier agency m15 = mommy.make('awards.award', fain='imalittlefain', parent_award=pa14, awarding_agency=a1) t15 = Award.get_or_create_summary_award(fain='imalittlefain', awarding_agency=a3)[1] assert t15 != m15 # matching piid and parent award but mismatched subtier and toptier agency pa16 = mommy.make('awards.Award', piid='imjustapiidparent', awarding_agency=a3) m16 = mommy.make('awards.Award', piid='imjustapiidchild', awarding_agency=a3) t16 = Award.get_or_create_summary_award( piid='imjustapiidchild', parent_award_id='imjustapiidparent', awarding_agency=a4)[1] assert t16 != m16
def load_file_d1(submission_attributes, procurement_data, db_cursor): """ Process and load file D1 broker data (contract award txns). """ legal_entity_location_field_map = { "address_line1": "legal_entity_address_line1", "address_line2": "legal_entity_address_line2", "address_line3": "legal_entity_address_line3", "location_country_code": "legal_entity_country_code", "city_name": "legal_entity_city_name", "congressional_code": "legal_entity_congressional", "state_code": "legal_entity_state_code", "zip4": "legal_entity_zip4" } place_of_performance_field_map = { # not sure place_of_performance_locat maps exactly to city name "city_name": "place_of_performance_locat", "congressional_code": "place_of_performance_congr", "state_code": "place_of_performance_state", "zip4": "place_of_performance_zip4a", "location_country_code": "place_of_perform_country_c" } place_of_performance_value_map = {"place_of_performance_flag": True} legal_entity_location_value_map = {"recipient_flag": True} contract_field_map = { "type": "contract_award_type", "description": "award_description" } for row in procurement_data: legal_entity_location, created = get_or_create_location( legal_entity_location_field_map, row, legal_entity_location_value_map) # Create the legal entity if it doesn't exist try: legal_entity = LegalEntity.objects.get( recipient_unique_id=row['awardee_or_recipient_uniqu']) except ObjectDoesNotExist: legal_entity_value_map = { "location": legal_entity_location, "legal_entity_id": row['awardee_or_recipient_uniqu'], } legal_entity = load_data_into_model( LegalEntity(), row, value_map=legal_entity_value_map, save=True) # Create the place of performance location pop_location, created = get_or_create_location( place_of_performance_field_map, row, place_of_performance_value_map) # If awarding/funding toptier agency code (aka CGAC) is not supplied on the D1 record, # use the sub tier code to look it up if row['awarding_agency_code'] is None: row['awarding_agency_code'] = Agency.get_by_subtier( row["awarding_sub_tier_agency_c"]).toptier_agency.cgac_code if row['funding_agency_code'] is None: row['funding_agency_code'] = Agency.get_by_subtier( row["funding_sub_tier_agency_co"]).toptier_agency.cgac_code # Find the award that this award transaction belongs to. If it doesn't exist, create it. awarding_agency = Agency.get_by_toptier_subtier( row['awarding_agency_code'], row["awarding_sub_tier_agency_c"]) created, award = Award.get_or_create_summary_award( awarding_agency=awarding_agency, piid=row.get('piid'), fain=row.get('fain'), uri=row.get('uri'), parent_award_id=row.get('parent_award_id')) award.save() AWARD_UPDATE_ID_LIST.append(award.id) AWARD_CONTRACT_UPDATE_ID_LIST.append(award.id) parent_txn_value_map = { "award": award, "awarding_agency": awarding_agency, "funding_agency": Agency.get_by_toptier_subtier(row['funding_agency_code'], row["funding_sub_tier_agency_co"]), "recipient": legal_entity, "place_of_performance": pop_location, 'submission': submission_attributes, "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']), } transaction_instance = load_data_into_model( Transaction(), row, field_map=contract_field_map, value_map=parent_txn_value_map, as_dict=True) transaction_instance, created = Transaction.objects.get_or_create( **transaction_instance) contract_value_map = { 'transaction': transaction_instance, 'submission': submission_attributes, 'reporting_period_start': submission_attributes.reporting_period_start, 'reporting_period_end': submission_attributes.reporting_period_end, "period_of_performance_potential_end_date": format_date(row['period_of_perf_potential_e']) } contract_instance = load_data_into_model(TransactionContract(), row, field_map=contract_field_map, value_map=contract_value_map, save=True)
def test_get_or_create_summary_award(): """Test award record lookup.""" sta1 = mommy.make(SubtierAgency, subtier_code="1234", name="Bureau of Effective Unit Tests") sta2 = mommy.make(SubtierAgency, subtier_code="5678", name="Bureau of Breaky Unit Tests") sta3 = mommy.make(SubtierAgency, subtier_code="0509", name="Bureau of Testing Bureaucracy") tta1 = mommy.make(ToptierAgency, toptier_code="020", name="Department of Unit Tests") tta2 = mommy.make(ToptierAgency, toptier_code="089", name="Department of Integrated Tests") a1 = mommy.make(Agency, id=1, toptier_agency=tta1, subtier_agency=sta1) a2 = mommy.make(Agency, id=2, toptier_agency=tta1) a3 = mommy.make(Agency, id=3, toptier_agency=tta1, subtier_agency=sta2) a4 = mommy.make(Agency, id=4, toptier_agency=tta2, subtier_agency=sta3) # match on awarding agency and piid m1 = mommy.make("awards.award", piid="DUT123", awarding_agency=a1) t1 = Award.get_or_create_summary_award(piid="DUT123", awarding_agency=a1)[1] assert t1 == m1 # match on awarding agency and piid + parent award piid m2 = mommy.make("awards.award", piid="DUT456", parent_award_piid="IDVDUT456", awarding_agency=a1) t2 = Award.get_or_create_summary_award(piid="DUT456", parent_award_piid="IDVDUT456", awarding_agency=a1)[1] assert t2 == m2 # match on awarding agency and fain m3 = mommy.make("awards.award", fain="DUT789", awarding_agency=a1) t3 = Award.get_or_create_summary_award(fain="DUT789", awarding_agency=a1)[1] assert t3 == m3 # match on awarding agency and fain + uri (fain takes precedence, same uri) m4 = mommy.make("awards.award", fain="DUT987", awarding_agency=a1) t4 = Award.get_or_create_summary_award(fain="DUT987", uri="123-abc-456", record_type="2", awarding_agency=a1)[1] assert t4 == m4 # match on awarding agency + uri m5 = mommy.make("awards.award", uri="abc-123-def", awarding_agency=a1) t5 = Award.get_or_create_summary_award(uri="abc-123-def", record_type="1", awarding_agency=a1)[1] assert t5 == m5 # match on awarding toptier agency only m6 = mommy.make("awards.award", uri="kkk-bbb-jjj", awarding_agency=a2) t6 = Award.get_or_create_summary_award(uri="kkk-bbb-jjj", record_type="1", awarding_agency=a2)[1] assert m6 == t6 # match on no awarding agency, uri m7 = mommy.make("awards.award", uri="mmm-fff-ddd") t7 = Award.get_or_create_summary_award(uri="mmm-fff-ddd", record_type="1")[1] assert m7 == t7 # match on no awarding agency, fain m8 = mommy.make("awards.award", fain="DUTDUTDUT") t8 = Award.get_or_create_summary_award(fain="DUTDUTDUT", record_type="2")[1] assert m8 == t8 # non-match with piid creates new award record m9 = mommy.make("awards.award", piid="imapiid") t9 = Award.get_or_create_summary_award(piid="imadifferentpiid") assert len(t9[0]) == 1 assert m9 != t9[1] # non-match with piid + matching parent award piid creates one new award m10 = mommy.make("awards.award", piid="thing1", parent_award_piid="thingmom") t10 = Award.get_or_create_summary_award(piid="thing2", parent_award_piid="thingmom") assert len(t10[0]) == 1 assert m10 != t10[1] # matching piid + non-matching parent m11 = mommy.make("awards.award", piid="0005", parent_award_piid="piidthing") t11 = Award.get_or_create_summary_award( piid="0005", parent_award_piid="anotherpiidthing") assert m11 != t11[0] # matching piid and parent award id but mismatched subtier agency, same top tier agency m12 = mommy.make("awards.award", piid="shortandstout", parent_award_piid="imalittlepiid", awarding_agency=a1) t12 = Award.get_or_create_summary_award(piid="shortandstout", parent_award_piid="imalittlepiid", awarding_agency=a3)[1] assert t12 != m12 # matching fain but mismatched subtier agency, same top tier agency m13 = mommy.make("awards.award", fain="imalittlefain", awarding_agency=a1) t13 = Award.get_or_create_summary_award(fain="imalittlefain", record_type="2", awarding_agency=a3)[1] assert t13 != m13 # matching piid and parent award but mismatched subtier and toptier agency mommy.make("awards.Award", piid="imjustapiidparent", awarding_agency=a3) m14 = mommy.make("awards.Award", piid="imjustapiidchild", awarding_agency=a3) t14 = Award.get_or_create_summary_award( piid="imjustapiidchild", parent_award_piid="imjustapiidparent", awarding_agency=a4)[1] assert t14 != m14 # matching piid and parent award but mismatched subtier and toptier agency m15 = mommy.make("awards.Award", generated_unique_award_id="this_is_generated_and_unique") t15 = Award.get_or_create_summary_award( generated_unique_award_id="this_is_generated_and_unique") assert m15 == t15[1] # matching piid and parent award but mismatched subtier and toptier agency m16 = mommy.make("awards.Award", generated_unique_award_id="this_is_generated_and_unique") t16 = Award.get_or_create_summary_award( generated_unique_award_id="this_is_generated_unique_and_different") assert len(t16[0]) == 1 assert m16 != t16[1]
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 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_fpds(self, to_insert, total_rows): logger.info('Starting insertion of new FPDS data') 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" } start_time = datetime.now() for index, row in enumerate(to_insert, 1): if not (index % 1000): logger.info( 'Inserting Stale FPDS: Inserting row {} of {} ({})'.format( str(index), str(total_rows), datetime.now() - start_time)) for key in row: if isinstance(row[key], str): row[key] = row[key].upper() # 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) try: last_mod_date = datetime.strptime(str( row['last_modified']), "%Y-%m-%d %H:%M:%S.%f").date() except ValueError: last_mod_date = datetime.strptime(str(row['last_modified']), "%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, "transaction_unique_id": row['detached_award_proc_unique'], "generated_unique_award_id": generated_unique_id, "is_fpds": True } contract_field_map = { "type": "contract_award_type", "type_description": "contract_award_type_desc", "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(), # thrown away row, as_dict=True) 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.utcnow() 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()
def test_get_or_create_summary_award(): """Test award record lookup.""" sta1 = mommy.make(SubtierAgency, subtier_code='1234', name='Bureau of Effective Unit Tests') sta2 = mommy.make(SubtierAgency, subtier_code='5678', name='Bureau of Breaky Unit Tests') sta3 = mommy.make(SubtierAgency, subtier_code='0509', name='Bureau of Testing Bureaucracy') tta1 = mommy.make(ToptierAgency, cgac_code='020', name='Department of Unit Tests') tta2 = mommy.make(ToptierAgency, cgac_code='089', name='Department of Integrated Tests') a1 = mommy.make(Agency, id=1, toptier_agency=tta1, subtier_agency=sta1) a2 = mommy.make(Agency, id=2, toptier_agency=tta1) a3 = mommy.make(Agency, id=3, toptier_agency=tta1, subtier_agency=sta2) a4 = mommy.make(Agency, id=4, toptier_agency=tta2, subtier_agency=sta3) # match on awarding agency and piid m1 = mommy.make('awards.award', piid='DUT123', awarding_agency=a1) t1 = Award.get_or_create_summary_award(piid='DUT123', awarding_agency=a1)[1] assert t1 == m1 # match on awarding agency and piid + parent award piid m2 = mommy.make('awards.award', piid='DUT456', parent_award_piid='IDVDUT456', awarding_agency=a1) t2 = Award.get_or_create_summary_award(piid='DUT456', parent_award_piid='IDVDUT456', awarding_agency=a1)[1] assert t2 == m2 # match on awarding agency and fain m3 = mommy.make('awards.award', fain='DUT789', awarding_agency=a1) t3 = Award.get_or_create_summary_award(fain='DUT789', awarding_agency=a1)[1] assert t3 == m3 # match on awarding agency and fain + uri (fain takes precedence, same uri) m4 = mommy.make('awards.award', fain='DUT987', awarding_agency=a1) t4 = Award.get_or_create_summary_award(fain='DUT987', uri='123-abc-456', record_type='2', awarding_agency=a1)[1] assert t4 == m4 # match on awarding agency + uri m5 = mommy.make('awards.award', uri='abc-123-def', awarding_agency=a1) t5 = Award.get_or_create_summary_award(uri='abc-123-def', record_type='1', awarding_agency=a1)[1] assert t5 == m5 # match on awarding toptier agency only m6 = mommy.make('awards.award', uri='kkk-bbb-jjj', awarding_agency=a2) t6 = Award.get_or_create_summary_award(uri='kkk-bbb-jjj', record_type='1', awarding_agency=a2)[1] assert m6 == t6 # match on no awarding agency, uri m7 = mommy.make('awards.award', uri='mmm-fff-ddd') t7 = Award.get_or_create_summary_award(uri='mmm-fff-ddd', record_type='1')[1] assert m7 == t7 # match on no awarding agency, fain m8 = mommy.make('awards.award', fain='DUTDUTDUT') t8 = Award.get_or_create_summary_award(fain='DUTDUTDUT', record_type='2')[1] assert m8 == t8 # non-match with piid creates new award record m9 = mommy.make('awards.award', piid='imapiid') t9 = Award.get_or_create_summary_award(piid='imadifferentpiid') assert len(t9[0]) == 1 assert m9 != t9[1] # non-match with piid + matching parent award piid creates one new award m10 = mommy.make('awards.award', piid='thing1', parent_award_piid='thingmom') t10 = Award.get_or_create_summary_award(piid='thing2', parent_award_piid='thingmom') assert len(t10[0]) == 1 assert m10 != t10[1] # matching piid + non-matching parent m11 = mommy.make('awards.award', piid='0005', parent_award_piid='piidthing') t11 = Award.get_or_create_summary_award(piid='0005', parent_award_piid='anotherpiidthing') assert m11 != t11[0] # matching piid and parent award id but mismatched subtier agency, same top tier agency m12 = mommy.make('awards.award', piid='shortandstout', parent_award_piid='imalittlepiid', awarding_agency=a1) t12 = Award.get_or_create_summary_award(piid='shortandstout', parent_award_piid='imalittlepiid', awarding_agency=a3)[1] assert t12 != m12 # matching fain but mismatched subtier agency, same top tier agency m13 = mommy.make('awards.award', fain='imalittlefain', awarding_agency=a1) t13 = Award.get_or_create_summary_award(fain='imalittlefain', record_type='2', awarding_agency=a3)[1] assert t13 != m13 # matching piid and parent award but mismatched subtier and toptier agency mommy.make('awards.Award', piid='imjustapiidparent', awarding_agency=a3) m14 = mommy.make('awards.Award', piid='imjustapiidchild', awarding_agency=a3) t14 = Award.get_or_create_summary_award(piid='imjustapiidchild', parent_award_piid='imjustapiidparent', awarding_agency=a4)[1] assert t14 != m14 # matching piid and parent award but mismatched subtier and toptier agency m15 = mommy.make('awards.Award', generated_unique_award_id='this_is_generated_and_unique') t15 = Award.get_or_create_summary_award(generated_unique_award_id='this_is_generated_and_unique') assert m15 == t15[1] # matching piid and parent award but mismatched subtier and toptier agency m16 = mommy.make('awards.Award', generated_unique_award_id='this_is_generated_and_unique') t16 = Award.get_or_create_summary_award(generated_unique_award_id='this_is_generated_unique_and_different') assert len(t16[0]) == 1 assert m16 != t16[1]