def test_award_update_with_list():
"""Test optional parameter to update specific awards with txn data."""
awards = mommy.make("awards.Award", total_obligation=0, _quantity=10)
test_award = awards[3]
# test a single award update
mommy.make("awards.TransactionNormalized", award=test_award, federal_action_obligation=1000, _quantity=5)
count = update_awards((test_award.id,))
test_award.refresh_from_db()
# one award is updated
assert count == 1
# specified award is updated
assert test_award.total_obligation == 5000
# other awards not updated
assert awards[0].total_obligation == 0
# test updating several awards
mommy.make("awards.TransactionNormalized", award=awards[0], federal_action_obligation=2000, _quantity=2)
mommy.make("awards.TransactionNormalized", award=awards[1], federal_action_obligation=-1000, _quantity=3)
count = update_awards((awards[0].id, awards[1].id))
awards[0].refresh_from_db()
awards[1].refresh_from_db()
# two awards are updated
assert count == 2
# specified awards are updated
assert awards[0].total_obligation == 4000
assert awards[1].total_obligation == -3000
# other awards not updated
assert awards[4].total_obligation == 0
def handle(self, *args, **options):
logger.info('Starting FABS bulk data load...')
db_cursor = connections['data_broker'].cursor()
ds_cursor = connection.cursor()
fiscal_year = options.get('fiscal_year')
if fiscal_year:
fiscal_year = fiscal_year[0]
else:
fiscal_year = 2017
logger.info('Processing data for Fiscal Year ' + str(fiscal_year))
with timer('Diff-ing FABS data', logger.info):
to_insert, to_delete = self.diff_fabs_data(
db_cursor=db_cursor, ds_cursor=ds_cursor, fiscal_year=fiscal_year)
total_rows = len(to_insert)
total_rows_delete = len(to_delete)
if total_rows_delete > 0:
with timer('Deleting stale FABS data', logger.info):
self.delete_stale_fabs(to_delete=to_delete)
if total_rows > 0:
# Set lookups after deletions to only get latest
self.set_lookup_maps()
with timer('Get Broker FABS data', logger.info):
fabs_broker_data = self.get_fabs_data(
db_cursor=db_cursor, fiscal_year=fiscal_year, to_insert=to_insert)
with timer('Loading POP Location data...', logger.info):
self.load_locations(fabs_broker_data=fabs_broker_data, total_rows=total_rows, pop_flag=True)
with timer('Loading LE Location data', logger.info):
self.load_locations(fabs_broker_data=fabs_broker_data, total_rows=total_rows)
with timer('Loading Legal Entity data', logger.info):
self.load_legal_entity(fabs_broker_data=fabs_broker_data, total_rows=total_rows)
with timer('Loading Award data', logger.info):
self.load_awards(fabs_broker_data=fabs_broker_data, total_rows=total_rows)
with timer('Loading Transaction Normalized data', logger.info):
self.load_transaction_normalized(fabs_broker_data=fabs_broker_data, total_rows=total_rows)
with timer('Loading Transaction FABS data', logger.info):
self.load_transaction_fabs(fabs_broker_data, total_rows)
award_update_id_list = [award.id for award in award_lookup]
with timer('Updating awards to reflect their latest associated transaction info', logger.info):
update_awards(tuple(award_update_id_list))
with timer('Updating award category variables', logger.info):
update_award_categories(tuple(award_update_id_list))
else:
logger.info('Nothing to insert...FINISHED!')
def handle(self, *args, **options):
logger.info("==== Starting FPDS nightly data load ====")
if options.get("date"):
date = options.get("date")[0]
date = datetime.strptime(date, "%Y-%m-%d").date()
else:
data_load_date_obj = ExternalDataLoadDate.objects.filter(
external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT["fpds"]
).first()
if not data_load_date_obj:
date = (datetime.utcnow() - timedelta(days=1)).strftime("%Y-%m-%d")
else:
date = data_load_date_obj.last_load_date
start_date = datetime.utcnow().strftime("%Y-%m-%d")
logger.info("Processing data for FPDS starting from %s" % date)
with timer("retrieval of deleted FPDS IDs", logger.info):
ids_to_delete = self.get_deleted_fpds_data_from_s3(date=date)
if len(ids_to_delete) > 0:
with timer("deletion of all stale FPDS data", logger.info):
self.delete_stale_fpds(ids_to_delete=ids_to_delete)
else:
logger.info("No FPDS records to delete at this juncture")
with timer("retrieval of new/modified FPDS data ID list", logger.info):
total_insert = self.get_fpds_transaction_ids(date=date)
if len(total_insert) > 0:
# Add FPDS records
with timer("insertion of new FPDS data in batches", logger.info):
self.insert_all_new_fpds(total_insert)
# Update Awards based on changed FPDS records
with timer("updating awards to reflect their latest associated transaction info", logger.info):
update_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update FPDS-specific Awards based on the info in child transactions
with timer("updating contract-specific awards to reflect their latest transaction info", logger.info):
update_contract_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update AwardCategories based on changed FPDS records
with timer("updating award category variables", logger.info):
update_award_categories(tuple(AWARD_UPDATE_ID_LIST))
# Check the linkages from file C to FPDS records and update any that are missing
with timer("updating C->D linkages", logger.info):
update_c_to_d_linkages("contract")
else:
logger.info("No FPDS records to insert or modify at this juncture")
# Update the date for the last time the data load was run
ExternalDataLoadDate.objects.filter(external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT["fpds"]).delete()
ExternalDataLoadDate(
last_load_date=start_date, external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT["fpds"]
).save()
logger.info("FPDS NIGHTLY UPDATE COMPLETE")
def perform_load(self, ids_to_delete, ids_to_insert):
if len(ids_to_delete) > 0:
with timer("deletion of all stale FPDS data", logger.info):
self.delete_stale_fpds(ids_to_delete=ids_to_delete)
else:
logger.info("No FPDS records to delete at this juncture")
if len(ids_to_insert) > 0:
# Add FPDS records
with timer("insertion of new FPDS data in batches", logger.info):
self.insert_all_new_fpds(ids_to_insert)
# Update Awards based on changed FPDS records
with timer("updating awards to reflect their latest associated transaction info", logger.info):
update_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update FPDS-specific Awards based on the info in child transactions
with timer("updating contract-specific awards to reflect their latest transaction info", logger.info):
update_contract_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update AwardCategories based on changed FPDS records
with timer("updating award category variables", logger.info):
update_award_categories(tuple(AWARD_UPDATE_ID_LIST))
# Check the linkages from file C to FPDS records and update any that are missing
with timer("updating C->D linkages", logger.info):
update_c_to_d_linkages("contract")
else:
logger.info("No FPDS records to insert or modify at this juncture")
def test_award_update_from_earliest_transaction():
"""Test awards fields that should be updated with most earliest transaction info."""
award = mommy.make('awards.Award')
mommy.make(
'awards.Transaction',
award=award,
# since this is the award's first transaction,
# the txn action_date will become the award
# signed date
action_date=datetime.date(2016, 1, 1))
# adding later transaction should not change award values
mommy.make('awards.Transaction',
award=award,
action_date=datetime.date(2017, 1, 1))
update_awards()
award.refresh_from_db()
assert award.date_signed == datetime.date(2016, 1, 1)
# adding earlier transaction should update award values
mommy.make('awards.Transaction',
award=award,
action_date=datetime.date(2010, 1, 1))
update_awards()
award.refresh_from_db()
assert award.date_signed == datetime.date(2010, 1, 1)
def handle(self, *args, **options):
logger.info('Starting FABS nightly data load...')
# Use date provided or pull most recent ExternalDataLoadDate
if options.get('date'):
date = options.get('date')[0]
else:
data_load_date_obj = ExternalDataLoadDate.objects. \
filter(external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT['fabs']).first()
if not data_load_date_obj:
date = (datetime.utcnow() - timedelta(days=1)).strftime('%Y-%m-%d')
else:
date = data_load_date_obj.last_load_date
start_date = datetime.utcnow().strftime('%Y-%m-%d')
logger.info('Processing data for FABS starting from %s' % date)
# Retrieve FABS data
with timer('retrieving/diff-ing FABS Data', logger.info):
to_insert, ids_to_delete = self.get_fabs_data(date=date)
total_rows = len(to_insert)
total_rows_delete = len(ids_to_delete)
if total_rows_delete > 0:
# Create a file with the deletion IDs and place in a bucket for ElasticSearch
self.send_deletes_to_s3(ids_to_delete)
# Delete FABS records by ID
with timer('deleting stale FABS data', logger.info):
self.delete_stale_fabs(ids_to_delete=ids_to_delete)
else:
logger.info('Nothing to delete...')
if total_rows > 0:
# Add FABS records
with timer('inserting new FABS data', logger.info):
self.insert_new_fabs(to_insert=to_insert, total_rows=total_rows)
# Update Awards based on changed FABS records
with timer('updating awards to reflect their latest associated transaction info', logger.info):
update_awards(tuple(award_update_id_list))
# Update AwardCategories based on changed FABS records
with timer('updating award category variables', logger.info):
update_award_categories(tuple(award_update_id_list))
# Check the linkages from file C to FABS records and update any that are missing
with timer('updating C->D linkages', logger.info):
update_c_to_d_linkages('assistance')
else:
logger.info('Nothing to insert...')
# Update the date for the last time the data load was run
ExternalDataLoadDate.objects.filter(external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT['fabs']).delete()
ExternalDataLoadDate(last_load_date=start_date,
external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT['fabs']).save()
logger.info('FABS NIGHTLY UPDATE FINISHED!')
def handle(self, *args, **options):
logger.info('Starting FPDS nightly data load...')
if options.get('date'):
date = options.get('date')[0]
date = datetime.strptime(date, '%Y-%m-%d').date()
else:
data_load_date_obj = ExternalDataLoadDate.objects. \
filter(external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT['fpds']).first()
if not data_load_date_obj:
date = (datetime.utcnow() -
timedelta(days=1)).strftime('%Y-%m-%d')
else:
date = data_load_date_obj.last_load_date
start_date = datetime.utcnow().strftime('%Y-%m-%d')
logger.info('Processing data for FPDS starting from %s' % date)
with timer('retrieving/diff-ing FPDS Data', logger.info):
to_insert, ids_to_delete = self.get_fpds_data(date=date)
total_rows = len(to_insert)
total_rows_delete = len(ids_to_delete)
if total_rows_delete > 0:
with timer('deleting stale FPDS data', logger.info):
self.delete_stale_fpds(ids_to_delete=ids_to_delete)
else:
logger.info('Nothing to delete...')
if total_rows > 0:
with timer('inserting new FPDS data', logger.info):
self.insert_new_fpds(to_insert=to_insert,
total_rows=total_rows)
with timer(
'updating awards to reflect their latest associated transaction info',
logger.info):
update_awards(tuple(award_update_id_list))
with timer(
'updating contract-specific awards to reflect their latest transaction info',
logger.info):
update_contract_awards(tuple(award_update_id_list))
with timer('updating award category variables', logger.info):
update_award_categories(tuple(award_update_id_list))
else:
logger.info('Nothing to insert...')
# Update the date for the last time the data load was run
ExternalDataLoadDate.objects.filter(
external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT['fpds']
).delete()
ExternalDataLoadDate(last_load_date=start_date,
external_data_type_id=lookups.
EXTERNAL_DATA_TYPE_DICT['fpds']).save()
logger.info('FPDS NIGHTLY UPDATE FINISHED!')
def test_award_update_obligated_amt():
"""Test that the award obligated amt updates as child transactions change."""
award = mommy.make("awards.Award", total_obligation=1000)
mommy.make("awards.TransactionNormalized", award=award, federal_action_obligation=1000, _quantity=5)
update_awards()
award.refresh_from_db()
assert award.total_obligation == 5000
def delete_stale_fabs(ids_to_delete):
"""ids_to_delete are published_award_financial_assistance_ids"""
logger.info("Starting deletion of stale FABS data")
if not ids_to_delete:
return []
transactions = TransactionNormalized.objects.filter(
assistance_data__published_award_financial_assistance_id__in=
ids_to_delete)
update_award_ids, delete_award_ids = find_related_awards(transactions)
delete_transaction_ids = [
delete_result[0] for delete_result in transactions.values_list("id")
]
delete_transaction_str_ids = ",".join(
[str(deleted_result) for deleted_result in delete_transaction_ids])
delete_award_str_ids = ",".join(
[str(deleted_result) for deleted_result in delete_award_ids])
queries = []
# Transaction FABS
if delete_transaction_ids:
fabs = 'DELETE FROM "transaction_fabs" tf WHERE tf."transaction_id" IN ({});'
tn = 'DELETE FROM "transaction_normalized" tn WHERE tn."id" IN ({});'
ts = 'DELETE FROM "transaction_search" ts WHERE ts."transaction_id" IN ({});'
td = "DELETE FROM transaction_delta td WHERE td.transaction_id in ({});"
queries.extend([
fabs.format(delete_transaction_str_ids),
tn.format(delete_transaction_str_ids),
ts.format(delete_transaction_str_ids),
td.format(delete_transaction_str_ids),
])
if delete_award_ids:
# Financial Accounts by Awards
faba = 'UPDATE "financial_accounts_by_awards" SET "award_id" = null WHERE "award_id" IN ({});'
# Subawards
sub = 'UPDATE "subaward" SET "award_id" = null WHERE "award_id" IN ({});'.format(
delete_award_str_ids)
# Delete Awards
delete_awards_query = 'DELETE FROM "awards" a WHERE a."id" IN ({});'.format(
delete_award_str_ids)
queries.extend(
[faba.format(delete_award_str_ids), sub, delete_awards_query])
if queries:
db_query = "".join(queries)
db_cursor = connections[DEFAULT_DB_ALIAS].cursor()
db_cursor.execute(db_query, [])
# Update Awards
if update_award_ids:
update_awards(tuple(update_award_ids))
return update_award_ids
def handle(self, *args, **options):
logger.info("==== Starting FPDS nightly data load ====")
if options.get("date"):
date = options.get("date")[0]
date = datetime.strptime(date, "%Y-%m-%d").date()
else:
default_last_load_date = datetime.now(timezone.utc) - timedelta(days=1)
date = get_last_load_date("fpds", default=default_last_load_date).date()
processing_start_datetime = datetime.now(timezone.utc)
logger.info("Processing data for FPDS starting from %s" % date)
with timer("retrieval of deleted FPDS IDs", logger.info):
ids_to_delete = self.get_deleted_fpds_data_from_s3(date=date)
if len(ids_to_delete) > 0:
with timer("deletion of all stale FPDS data", logger.info):
self.delete_stale_fpds(ids_to_delete=ids_to_delete)
else:
logger.info("No FPDS records to delete at this juncture")
with timer("retrieval of new/modified FPDS data ID list", logger.info):
total_insert = self.get_fpds_transaction_ids(date=date)
if len(total_insert) > 0:
# Add FPDS records
with timer("insertion of new FPDS data in batches", logger.info):
self.insert_all_new_fpds(total_insert)
# Update Awards based on changed FPDS records
with timer("updating awards to reflect their latest associated transaction info", logger.info):
update_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update FPDS-specific Awards based on the info in child transactions
with timer("updating contract-specific awards to reflect their latest transaction info", logger.info):
update_contract_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update AwardCategories based on changed FPDS records
with timer("updating award category variables", logger.info):
update_award_categories(tuple(AWARD_UPDATE_ID_LIST))
# Check the linkages from file C to FPDS records and update any that are missing
with timer("updating C->D linkages", logger.info):
update_c_to_d_linkages("contract")
else:
logger.info("No FPDS records to insert or modify at this juncture")
# Update the date for the last time the data load was run
update_last_load_date("fpds", processing_start_datetime)
logger.info("FPDS NIGHTLY UPDATE COMPLETE")
def delete_stale_fabs(ids_to_delete):
logger.info('Starting deletion of stale FABS data')
if not ids_to_delete:
return []
transactions = TransactionNormalized.objects.filter(
assistance_data__afa_generated_unique__in=ids_to_delete)
update_award_ids, delete_award_ids = find_related_awards(transactions)
delete_transaction_ids = [
delete_result[0] for delete_result in transactions.values_list('id')
]
delete_transaction_str_ids = ','.join(
[str(deleted_result) for deleted_result in delete_transaction_ids])
delete_award_str_ids = ','.join(
[str(deleted_result) for deleted_result in delete_award_ids])
queries = []
# Transaction FABS
if delete_transaction_ids:
fabs = 'DELETE FROM "transaction_fabs" tf WHERE tf."transaction_id" IN ({});'
tn = 'DELETE FROM "transaction_normalized" tn WHERE tn."id" IN ({});'
queries.extend([
fabs.format(delete_transaction_str_ids),
tn.format(delete_transaction_str_ids)
])
if delete_award_ids:
# Financial Accounts by Awards
faba = 'UPDATE "financial_accounts_by_awards" SET "award_id" = null WHERE "award_id" IN ({});'
# Subawards
sub = 'UPDATE "subaward" SET "award_id" = null WHERE "award_id" IN ({});'.format(
delete_award_str_ids)
# Delete Awards
delete_awards_query = 'DELETE FROM "awards" a WHERE a."id" IN ({});'.format(
delete_award_str_ids)
queries.extend(
[faba.format(delete_award_str_ids), sub, delete_awards_query])
if queries:
db_query = ''.join(queries)
db_cursor = connections['default'].cursor()
db_cursor.execute(db_query, [])
# Update Awards
if update_award_ids:
update_awards(tuple(update_award_ids))
return update_award_ids
def handle(self, *args, **options):
logger.info("Starting historical data load...")
db_cursor = connections["data_broker"].cursor()
fiscal_year = options.get("fiscal_year")
page = options.get("page")
limit = options.get("limit")
if fiscal_year:
fiscal_year = fiscal_year[0]
logger.info("Processing data for Fiscal Year " + str(fiscal_year))
else:
fiscal_year = 2017
page = page[0] if page else 1
limit = limit[0] if limit else 500000
if not options["assistance"]:
with timer("D1 historical data load", logger.info):
self.update_transaction_contract(db_cursor=db_cursor,
fiscal_year=fiscal_year,
page=page,
limit=limit)
if not options["contracts"]:
with timer("D2 historical data load", logger.info):
self.update_transaction_assistance(db_cursor=db_cursor,
fiscal_year=fiscal_year,
page=page,
limit=limit)
with timer(
"updating awards to reflect their latest associated transaction info",
logger.info):
update_awards(tuple(award_update_id_list))
with timer(
"updating assistance-specific awards to reflect their latest transaction info",
logger.info):
update_assistance_awards(tuple(award_assistance_update_id_list))
with timer(
"updating contract-specific awards to reflect their latest transaction info",
logger.info):
update_contract_awards(tuple(award_contract_update_id_list))
# Done!
logger.info("FINISHED")
def test_award_update_obligated_amt():
"""Test that the award obligated amt updates as child transactions change."""
award = mommy.make("awards.Award", total_obligation=1000, generated_unique_award_id="BIG_AGENCY_AWD_1")
mommy.make(
"awards.TransactionNormalized",
award=award,
federal_action_obligation=1000,
_quantity=5,
unique_award_key="BIG_AGENCY_AWD_1",
)
update_awards()
award.refresh_from_db()
assert award.total_obligation == 5000
def upsert_fabs_transactions(ids_to_upsert, externally_updated_award_ids):
if ids_to_upsert or externally_updated_award_ids:
update_award_ids = copy(externally_updated_award_ids)
if ids_to_upsert:
with timer("inserting new FABS data", logger.info):
update_award_ids.extend(insert_all_new_fabs(ids_to_upsert))
if update_award_ids:
update_award_ids = tuple(set(
update_award_ids)) # Convert to tuple and remove duplicates.
with timer(
"updating awards to reflect their latest associated transaction info",
logger.info):
award_record_count = update_awards(update_award_ids)
logger.info(
"{} awards updated from their transactional data".format(
award_record_count))
with timer("updating awards with executive compensation data",
logger.info):
award_record_count = update_assistance_awards(update_award_ids)
logger.info(
"{} awards updated FABS-specific and exec comp data".
format(award_record_count))
with timer("updating C->D linkages", logger.info):
update_c_to_d_linkages("assistance")
else:
logger.info("Nothing to insert...")
def handle(self, *args, **options):
logger.info('Starting historical data load...')
db_cursor = connections['data_broker'].cursor()
fiscal_year = options.get('fiscal_year')
page = options.get('page')
limit = options.get('limit')
if fiscal_year:
fiscal_year = fiscal_year[0]
logger.info('Processing data for Fiscal Year ' + str(fiscal_year))
else:
fiscal_year = 2017
page = page[0] if page else 1
limit = limit[0] if limit else 500000
if not options['assistance']:
with timer('D1 historical data load', logger.info):
self.update_transaction_contract(db_cursor=db_cursor,
fiscal_year=fiscal_year,
page=page,
limit=limit)
if not options['contracts']:
with timer('D2 historical data load', logger.info):
self.update_transaction_assistance(db_cursor=db_cursor,
fiscal_year=fiscal_year,
page=page,
limit=limit)
with timer(
'updating awards to reflect their latest associated transaction info',
logger.info):
update_awards(tuple(award_update_id_list))
with timer(
'updating contract-specific awards to reflect their latest transaction info',
logger.info):
update_contract_awards(tuple(award_contract_update_id_list))
with timer('updating award category variables', logger.info):
update_award_categories(tuple(award_update_id_list))
# Done!
logger.info('FINISHED')
def handle(self, *args, **options):
logger.info('Starting updates to award data...')
all_records_flag = options.get('all')
fiscal_year = options.get('fiscal_year')
award_update_id_list = []
award_contract_update_id_list = []
if not all_records_flag:
if fiscal_year:
fiscal_year = fiscal_year[0]
logger.info('Processing data for Fiscal Year ' +
str(fiscal_year))
else:
fiscal_year = 2017
# Lists to store for update_awards and update_contract_awards
award_update_id_list = TransactionNormalized.objects.filter(action_date__fy=fiscal_year).\
values_list('award_id', flat=True)
award_contract_update_id_list = TransactionFPDS.objects.filter(action_date__fy=fiscal_year).\
values_list('transaction__award_id', flat=True)
with timer(
'updating awards to reflect their latest associated transaction info',
logger.info):
update_awards() if all_records_flag else update_awards(
tuple(award_update_id_list))
with timer(
'updating contract-specific awards to reflect their latest transaction info...',
logger.info):
if all_records_flag:
update_contract_awards()
else:
update_contract_awards(tuple(award_contract_update_id_list))
with timer('updating award category variables', logger.info):
update_award_categories(
) if all_records_flag else update_award_categories(
tuple(award_update_id_list))
# Done!
logger.info('FINISHED')
def upsert_fabs_transactions(ids_to_upsert, externally_updated_award_ids):
if ids_to_upsert or externally_updated_award_ids:
update_award_ids = copy(externally_updated_award_ids)
if ids_to_upsert:
with timer("inserting new FABS data", logger.info):
update_award_ids.extend(insert_all_new_fabs(ids_to_upsert))
if update_award_ids:
update_award_ids = tuple(set(update_award_ids)) # Convert to tuple and remove duplicates.
with timer("updating awards to reflect their latest associated transaction info", logger.info):
update_awards(update_award_ids)
with timer("updating award category variables", logger.info):
update_award_categories(update_award_ids)
with timer("updating C->D linkages", logger.info):
update_c_to_d_linkages("assistance")
else:
logger.info("Nothing to insert...")
def test_award_update_from_earliest_transaction():
"""Test awards fields that should be updated with most earliest transaction info."""
award = mommy.make("awards.Award", generated_unique_award_id="AWD_ALPHA")
mommy.make(
"awards.TransactionNormalized",
award=award,
# since this is the award's first transaction,
# the txn action_date will become the award
# signed date
action_date=datetime.date(2016, 1, 1),
unique_award_key="AWD_ALPHA",
)
# adding later transaction should not change award values
mommy.make(
"awards.TransactionNormalized",
award=award,
action_date=datetime.date(2017, 1, 1),
unique_award_key="AWD_ALPHA",
)
update_awards()
award.refresh_from_db()
assert award.date_signed == datetime.date(2016, 1, 1)
# adding earlier transaction should update award values
mommy.make(
"awards.TransactionNormalized",
award=award,
action_date=datetime.date(2010, 1, 1),
unique_award_key="AWD_ALPHA",
)
update_awards()
award.refresh_from_db()
assert award.date_signed == datetime.date(2010, 1, 1)
def upsert_fabs_transactions(ids_to_upsert, externally_updated_award_ids):
if ids_to_upsert or externally_updated_award_ids:
update_award_ids = copy(externally_updated_award_ids)
if ids_to_upsert:
with timer("inserting new FABS data", logger.info):
update_award_ids.extend(insert_all_new_fabs(ids_to_upsert))
if update_award_ids:
update_award_ids = tuple(set(
update_award_ids)) # Convert to tuple and remove duplicates.
with timer(
"updating awards to reflect their latest associated transaction info",
logger.info):
update_awards(update_award_ids)
with timer("updating award category variables", logger.info):
update_award_categories(update_award_ids)
with timer("updating C->D linkages", logger.info):
update_c_to_d_linkages("assistance")
else:
logger.info("Nothing to insert...")
def handle(self, *args, **options):
logger.info('Starting historical data load...')
db_cursor = connections['data_broker'].cursor()
fiscal_year = options.get('fiscal_year')
page = options.get('page')
limit = options.get('limit')
if fiscal_year:
fiscal_year = fiscal_year[0]
logger.info('Processing data for Fiscal Year ' + str(fiscal_year))
else:
fiscal_year = 2017
page = page[0] if page else 1
limit = limit[0] if limit else 500000
if not options['assistance']:
with timer('D1 historical data load', logger.info):
self.update_transaction_contract(db_cursor=db_cursor, fiscal_year=fiscal_year, page=page, limit=limit)
if not options['contracts']:
with timer('D2 historical data load', logger.info):
self.update_transaction_assistance(db_cursor=db_cursor, fiscal_year=fiscal_year, page=page, limit=limit)
with timer('updating awards to reflect their latest associated transaction info', logger.info):
update_awards(tuple(award_update_id_list))
with timer('updating contract-specific awards to reflect their latest transaction info', logger.info):
update_contract_awards(tuple(award_contract_update_id_list))
with timer('updating award category variables', logger.info):
update_award_categories(tuple(award_update_id_list))
# Done!
logger.info('FINISHED')
def handle(self, *args, **options):
logger.info('Starting updates to award data...')
all_records_flag = options.get('all')
fiscal_year = options.get('fiscal_year')
award_update_id_list = []
award_contract_update_id_list = []
if not all_records_flag:
if fiscal_year:
fiscal_year = fiscal_year[0]
logger.info('Processing data for Fiscal Year ' + str(fiscal_year))
else:
fiscal_year = 2017
# Lists to store for update_awards and update_contract_awards
award_update_id_list = TransactionNormalized.objects.filter(action_date__fy=fiscal_year).\
values_list('award_id', flat=True)
award_contract_update_id_list = TransactionFPDS.objects.filter(action_date__fy=fiscal_year).\
values_list('transaction__award_id', flat=True)
with timer('updating awards to reflect their latest associated transaction info', logger.info):
update_awards() if all_records_flag else update_awards(tuple(award_update_id_list))
with timer('updating contract-specific awards to reflect their latest transaction info...', logger.info):
if all_records_flag:
update_contract_awards()
else:
update_contract_awards(tuple(award_contract_update_id_list))
with timer('updating award category variables', logger.info):
update_award_categories() if all_records_flag else update_award_categories(tuple(award_update_id_list))
# Done!
logger.info('FINISHED')
def handle(self, *args, **options):
logger.info("Starting FABS data load script...")
start_date = datetime.now(timezone.utc).strftime('%Y-%m-%d')
fabs_load_db_id = lookups.EXTERNAL_DATA_TYPE_DICT['fabs']
data_load_date_obj = ExternalDataLoadDate.objects.filter(
external_data_type_id=fabs_load_db_id).first()
if options.get("date"): # if provided, use cli data
load_from_date = options.get("date")[0]
elif data_load_date_obj: # else if last run is in DB, use that
load_from_date = data_load_date_obj.last_load_date
else: # Default is yesterday at midnight
load_from_date = (datetime.now(timezone.utc) -
timedelta(days=1)).strftime('%Y-%m-%d')
logger.info('Processing data for FABS starting from %s' %
load_from_date)
with timer('retrieving/diff-ing FABS Data', logger.info):
upsert_transactions = self.get_fabs_transaction_ids(
date=load_from_date)
with timer("obtaining delete records", logger.info):
ids_to_delete = self.get_fabs_records_to_delete(
date=load_from_date)
if ids_to_delete:
self.store_deleted_fabs(ids_to_delete)
# Delete FABS records by ID
with timer("deleting stale FABS data", logger.info):
self.delete_stale_fabs(ids_to_delete=ids_to_delete)
del ids_to_delete
else:
logger.info("Nothing to delete...")
if upsert_transactions:
# Add FABS records
with timer('inserting new FABS data', logger.info):
self.insert_all_new_fabs(all_new_to_insert=upsert_transactions)
# Update Awards based on changed FABS records
with timer(
'updating awards to reflect their latest associated transaction info',
logger.info):
update_awards(tuple(AWARD_UPDATE_ID_LIST))
# Update AwardCategories based on changed FABS records
with timer('updating award category variables', logger.info):
update_award_categories(tuple(AWARD_UPDATE_ID_LIST))
# Check the linkages from file C to FABS records and update any that are missing
with timer('updating C->D linkages', logger.info):
update_c_to_d_linkages('assistance')
else:
logger.info('Nothing to insert...')
# Update the date for the last time the data load was run
ExternalDataLoadDate.objects.filter(
external_data_type_id=lookups.EXTERNAL_DATA_TYPE_DICT['fabs']
).delete()
ExternalDataLoadDate(last_load_date=start_date,
external_data_type_id=lookups.
EXTERNAL_DATA_TYPE_DICT['fabs']).save()
logger.info('FABS UPDATE FINISHED!')
def award_data(db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make("download.JobStatus", job_status_id=js.id, name=js.name, description=js.desc)
# Create Locations
mommy.make("references.Location")
# Create LE
mommy.make("references.LegalEntity")
# Create Awarding Top Agency
ata1 = mommy.make(
"references.ToptierAgency",
name="Bureau of Things",
cgac_code="100",
website="http://test.com",
mission="test",
icon_filename="test",
)
ata2 = mommy.make(
"references.ToptierAgency",
name="Bureau of Stuff",
cgac_code="101",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Awarding subs
asa1 = mommy.make("references.SubtierAgency", name="Bureau of Things")
asa2 = mommy.make("references.SubtierAgency", name="Bureau of Stuff")
# Create Awarding Agencies
aa1 = mommy.make("references.Agency", toptier_agency=ata1, subtier_agency=asa1, toptier_flag=False)
aa2 = mommy.make("references.Agency", toptier_agency=ata2, subtier_agency=asa2, toptier_flag=False)
# Create Funding Top Agency
fta = mommy.make(
"references.ToptierAgency",
name="Bureau of Money",
cgac_code="102",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Funding SUB
fsa1 = mommy.make("references.SubtierAgency", name="Bureau of Things")
# Create Funding Agency
mommy.make("references.Agency", toptier_agency=fta, subtier_agency=fsa1, toptier_flag=False)
# Create Federal Account
mommy.make("accounts.FederalAccount", account_title="Compensation to Accounts", agency_identifier="102", id=1)
# Create Awards
award1 = mommy.make("awards.Award", category="contracts")
award2 = mommy.make("awards.Award", category="contracts")
award3 = mommy.make("awards.Award", category="assistance")
# Create Transactions
trann1 = mommy.make(TransactionNormalized, award=award1, modification_number=1, awarding_agency=aa1)
trann2 = mommy.make(TransactionNormalized, award=award2, modification_number=1, awarding_agency=aa2)
trann3 = mommy.make(TransactionNormalized, award=award3, modification_number=1, awarding_agency=aa2)
# Create TransactionContract
mommy.make(TransactionFPDS, transaction=trann1, piid="tc1piid")
mommy.make(TransactionFPDS, transaction=trann2, piid="tc2piid")
# Create TransactionAssistance
mommy.make(TransactionFABS, transaction=trann3, fain="ta1fain")
# Set latest_award for each award
update_awards()
def handle(self, *args, **options):
awards_cache.clear()
# Grab the data broker database connections
if not options['test']:
try:
db_conn = connections['data_broker']
db_cursor = db_conn.cursor()
except Exception as err:
logger.critical(
'Could not connect to database. Is DATA_BROKER_DATABASE_URL set?'
)
logger.critical(print(err))
return
else:
db_cursor = PhonyCursor()
# Grab the submission id
submission_id = options['submission_id'][0]
# Verify the ID exists in the database, and grab the data
db_cursor.execute('SELECT * FROM submission WHERE submission_id = %s',
[submission_id])
submission_data = dictfetchall(db_cursor)
if len(submission_data) == 0:
logger.error('Could not find submission with id ' +
str(submission_id))
return
elif len(submission_data) > 1:
logger.error('Found multiple submissions with id ' +
str(submission_id))
return
# We have a single submission, which is what we want
submission_data = submission_data[0]
broker_submission_id = submission_data['submission_id']
del submission_data[
'submission_id'] # To avoid collisions with the newer PK system
submission_attributes = get_submission_attributes(
broker_submission_id, submission_data)
# Move on, and grab file A data
db_cursor.execute(
'SELECT * FROM appropriation WHERE submission_id = %s',
[submission_id])
appropriation_data = dictfetchall(db_cursor)
logger.info('Acquired appropriation data for ' + str(submission_id) +
', there are ' + str(len(appropriation_data)) + ' rows.')
load_file_a(submission_attributes, appropriation_data, db_cursor)
# Let's get File B information
db_cursor.execute(
'SELECT * FROM object_class_program_activity WHERE submission_id = %s',
[submission_id])
prg_act_obj_cls_data = dictfetchall(db_cursor)
logger.info('Acquired program activity object class data for ' +
str(submission_id) + ', there are ' +
str(len(prg_act_obj_cls_data)) + ' rows.')
load_file_b(submission_attributes, prg_act_obj_cls_data, db_cursor)
# File D2
db_cursor.execute(
'SELECT * FROM award_financial_assistance WHERE submission_id = %s',
[submission_id])
award_financial_assistance_data = dictfetchall(db_cursor)
logger.info('Acquired award financial assistance data for ' +
str(submission_id) + ', there are ' +
str(len(award_financial_assistance_data)) + ' rows.')
load_file_d2(submission_attributes, award_financial_assistance_data,
db_cursor)
# File D1
db_cursor.execute(
'SELECT * FROM award_procurement WHERE submission_id = %s',
[submission_id])
procurement_data = dictfetchall(db_cursor)
logger.info('Acquired award procurement data for ' +
str(submission_id) + ', there are ' +
str(len(procurement_data)) + ' rows.')
load_file_d1(submission_attributes, procurement_data, db_cursor)
# Let's get File C information
# Note: we load File C last, because the D1 and D2 files have the awarding
# agency top tier (CGAC) and sub tier data needed to look up/create
# the most specific possible corresponding award. When looking up/
# creating awards for File C, we dont have sub-tier agency info, so
# we'll do our best to match them to the more specific award records
# already created by the D file load
db_cursor.execute(
'SELECT * FROM award_financial WHERE submission_id = %s',
[submission_id])
award_financial_data = dictfetchall(db_cursor)
logger.info('Acquired award financial data for ' + str(submission_id) +
', there are ' + str(len(award_financial_data)) + ' rows.')
load_file_c(submission_attributes, award_financial_data, db_cursor)
# Once all the files have been processed, run any global
# cleanup/post-load tasks.
# 1. Update the descriptions TODO: If this is slow, add ID limiting as above
update_model_description_fields()
# 2. Update awards to reflect their latest associated txn info
update_awards(tuple(AWARD_UPDATE_ID_LIST))
# 3. Update contract-specific award fields to reflect latest txn info
update_contract_awards(tuple(AWARD_CONTRACT_UPDATE_ID_LIST))
def award_data(db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make('download.JobStatus',
job_status_id=js.id,
name=js.name,
description=js.desc)
# Create Locations
mommy.make('references.Location')
# Create LE
mommy.make('references.LegalEntity')
# Create Awarding Top Agency
ata1 = mommy.make('references.ToptierAgency',
name="Bureau of Things",
cgac_code='100',
website='http://test.com',
mission='test',
icon_filename='test')
ata2 = mommy.make('references.ToptierAgency',
name="Bureau of Stuff",
cgac_code='101',
website='http://test.com',
mission='test',
icon_filename='test')
# Create Awarding subs
mommy.make('references.SubtierAgency', name="Bureau of Things")
# Create Awarding Agencies
aa1 = mommy.make('references.Agency',
id=1,
toptier_agency=ata1,
toptier_flag=False)
aa2 = mommy.make('references.Agency',
id=2,
toptier_agency=ata2,
toptier_flag=False)
# Create Funding Top Agency
mommy.make('references.ToptierAgency',
name="Bureau of Money",
cgac_code='102',
website='http://test.com',
mission='test',
icon_filename='test')
# Create Funding SUB
mommy.make('references.SubtierAgency', name="Bureau of Things")
# Create Funding Agency
mommy.make('references.Agency', id=3, toptier_flag=False)
# Create Awards
award1 = mommy.make('awards.Award', category='contracts')
award2 = mommy.make('awards.Award', category='contracts')
award3 = mommy.make('awards.Award', category='assistance')
# Create Transactions
trann1 = mommy.make(TransactionNormalized,
award=award1,
action_date='2018-01-01',
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa1)
trann2 = mommy.make(TransactionNormalized,
award=award2,
action_date='2018-01-01',
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2)
trann3 = mommy.make(TransactionNormalized,
award=award3,
action_date='2018-01-01',
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2)
# Create TransactionContract
mommy.make(TransactionFPDS, transaction=trann1, piid='tc1piid')
mommy.make(TransactionFPDS, transaction=trann2, piid='tc2piid')
# Create TransactionAssistance
mommy.make(TransactionFABS, transaction=trann3, fain='ta1fain')
# Set latest_award for each award
update_awards()
def download_test_data(db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make("download.JobStatus",
job_status_id=js.id,
name=js.name,
description=js.desc)
# Create Awarding Top Agency
ata1 = mommy.make(
"references.ToptierAgency",
name="Bureau of Things",
toptier_code="100",
website="http://test.com",
mission="test",
icon_filename="test",
)
ata2 = mommy.make(
"references.ToptierAgency",
name="Bureau of Stuff",
toptier_code="101",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Awarding subs
mommy.make("references.SubtierAgency", name="Bureau of Things")
# Create Awarding Agencies
aa1 = mommy.make("references.Agency",
id=1,
toptier_agency=ata1,
toptier_flag=False)
aa2 = mommy.make("references.Agency",
id=2,
toptier_agency=ata2,
toptier_flag=False)
# Create Funding Top Agency
ata3 = mommy.make(
"references.ToptierAgency",
name="Bureau of Money",
toptier_code="102",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Funding SUB
mommy.make("references.SubtierAgency", name="Bureau of Things")
# Create Funding Agency
mommy.make("references.Agency",
id=3,
toptier_agency=ata3,
toptier_flag=False)
# Create Awards
award1 = mommy.make("awards.Award", id=123, category="idv")
award2 = mommy.make("awards.Award", id=456, category="contracts")
award3 = mommy.make("awards.Award", id=789, category="assistance")
# Create Transactions
trann1 = mommy.make(
TransactionNormalized,
award=award1,
action_date="2018-01-01",
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa1,
)
trann2 = mommy.make(
TransactionNormalized,
award=award2,
action_date="2018-01-01",
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2,
)
trann3 = mommy.make(
TransactionNormalized,
award=award3,
action_date="2018-01-01",
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2,
)
# Create TransactionContract
mommy.make(TransactionFPDS, transaction=trann1, piid="tc1piid")
mommy.make(TransactionFPDS, transaction=trann2, piid="tc2piid")
# Create TransactionAssistance
mommy.make(TransactionFABS, transaction=trann3, fain="ta1fain")
# Set latest_award for each award
update_awards()
def award_data(db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make('download.JobStatus', job_status_id=js.id, name=js.name, description=js.desc)
# Create Locations
mommy.make('references.Location')
# Create LE
mommy.make('references.LegalEntity')
# Create Awarding Top Agency
ata1 = mommy.make(
'references.ToptierAgency',
name="Bureau of Things",
cgac_code='100',
website='http://test.com',
mission='test',
icon_filename='test')
ata2 = mommy.make(
'references.ToptierAgency',
name="Bureau of Stuff",
cgac_code='101',
website='http://test.com',
mission='test',
icon_filename='test')
# Create Awarding subs
mommy.make('references.SubtierAgency', name="Bureau of Things")
# Create Awarding Agencies
aa1 = mommy.make('references.Agency', id=1, toptier_agency=ata1, toptier_flag=False)
aa2 = mommy.make('references.Agency', id=2, toptier_agency=ata2, toptier_flag=False)
# Create Funding Top Agency
mommy.make(
'references.ToptierAgency',
name="Bureau of Money",
cgac_code='102',
website='http://test.com',
mission='test',
icon_filename='test')
# Create Funding SUB
mommy.make('references.SubtierAgency', name="Bureau of Things")
# Create Funding Agency
mommy.make('references.Agency', id=3, toptier_flag=False)
# Create Awards
award1 = mommy.make('awards.Award', category='contracts')
award2 = mommy.make('awards.Award', category='contracts')
award3 = mommy.make('awards.Award', category='assistance')
# Create Transactions
trann1 = mommy.make(
TransactionNormalized,
award=award1,
action_date='2018-01-01',
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa1)
trann2 = mommy.make(
TransactionNormalized,
award=award2,
action_date='2018-01-01',
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2)
trann3 = mommy.make(
TransactionNormalized,
award=award3,
action_date='2018-01-01',
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2)
# Create TransactionContract
mommy.make(TransactionFPDS, transaction=trann1, piid='tc1piid')
mommy.make(TransactionFPDS, transaction=trann2, piid='tc2piid')
# Create TransactionAssistance
mommy.make(TransactionFABS, transaction=trann3, fain='ta1fain')
# Set latest_award for each award
update_awards()
def download_test_data(db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make("download.JobStatus", job_status_id=js.id, name=js.name, description=js.desc)
# Create Awarding Top Agency
ata1 = mommy.make(
"references.ToptierAgency",
toptier_agency_id=100,
name="Bureau of Things",
toptier_code="100",
website="http://test0.com",
mission="test0",
icon_filename="test0",
)
ata2 = mommy.make(
"references.ToptierAgency",
toptier_agency_id=101,
name="Bureau of Stuff",
toptier_code="101",
website="http://test1.com",
mission="test1",
icon_filename="test1",
)
# Create Awarding Agencies
aa1 = mommy.make("references.Agency", id=1, toptier_agency=ata1, toptier_flag=False)
aa2 = mommy.make("references.Agency", id=2, toptier_agency=ata2, toptier_flag=False)
# Create Funding Top Agency
ata3 = mommy.make(
"references.ToptierAgency",
toptier_agency_id=102,
name="Bureau of Money",
toptier_code="102",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Funding Agency
mommy.make("references.Agency", id=3, toptier_agency=ata3, toptier_flag=False)
# Create Awards
award1 = mommy.make("awards.Award", id=123, category="idv", generated_unique_award_id="CONT_IDV_1")
award2 = mommy.make("awards.Award", id=456, category="contracts", generated_unique_award_id="CONT_AWD_1")
award3 = mommy.make("awards.Award", id=789, category="assistance", generated_unique_award_id="ASST_NON_1")
# Create Transactions
trann1 = mommy.make(
TransactionNormalized,
award=award1,
action_date="2018-01-01",
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa1,
unique_award_key="CONT_IDV_1",
)
trann2 = mommy.make(
TransactionNormalized,
award=award2,
action_date="2018-01-01",
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2,
unique_award_key="CONT_AWD_1",
)
trann3 = mommy.make(
TransactionNormalized,
award=award3,
action_date="2018-01-01",
type=random.choice(list(award_type_mapping)),
modification_number=1,
awarding_agency=aa2,
unique_award_key="ASST_NON_1",
)
# Create TransactionContract
mommy.make(TransactionFPDS, transaction=trann1, piid="tc1piid", unique_award_key="CONT_IDV_1")
mommy.make(TransactionFPDS, transaction=trann2, piid="tc2piid", unique_award_key="CONT_AWD_1")
# Create TransactionAssistance
mommy.make(TransactionFABS, transaction=trann3, fain="ta1fain", unique_award_key="ASST_NON_1")
# Create FederalAccount
fa1 = mommy.make(FederalAccount, id=10)
# Create TreasuryAppropriationAccount
taa1 = mommy.make(TreasuryAppropriationAccount, treasury_account_identifier=100, federal_account=fa1)
# Create FinancialAccountsByAwards
mommy.make(FinancialAccountsByAwards, financial_accounts_by_awards_id=1000, award=award1, treasury_account=taa1)
# Set latest_award for each award
update_awards()
def handle(self, *args, **options):
logger.info('Starting FABS bulk data load...')
db_cursor = connections['data_broker'].cursor()
ds_cursor = connection.cursor()
fiscal_year = options.get('fiscal_year')
if fiscal_year:
fiscal_year = fiscal_year[0]
else:
fiscal_year = 2017
logger.info('Processing data for Fiscal Year ' + str(fiscal_year))
with timer('Diff-ing FABS data', logger.info):
to_insert, to_delete = self.diff_fabs_data(db_cursor=db_cursor,
ds_cursor=ds_cursor,
fiscal_year=fiscal_year)
total_rows = len(to_insert)
total_rows_delete = len(to_delete)
if total_rows_delete > 0:
with timer('Deleting stale FABS data', logger.info):
self.delete_stale_fabs(to_delete=to_delete)
if total_rows > 0:
# Set lookups after deletions to only get latest
self.set_lookup_maps()
with timer('Get Broker FABS data', logger.info):
fabs_broker_data = self.get_fabs_data(db_cursor=db_cursor,
fiscal_year=fiscal_year,
to_insert=to_insert)
with timer('Loading POP Location data...', logger.info):
self.load_locations(fabs_broker_data=fabs_broker_data,
total_rows=total_rows,
pop_flag=True)
with timer('Loading LE Location data', logger.info):
self.load_locations(fabs_broker_data=fabs_broker_data,
total_rows=total_rows)
with timer('Loading Legal Entity data', logger.info):
self.load_legal_entity(fabs_broker_data=fabs_broker_data,
total_rows=total_rows)
with timer('Loading Award data', logger.info):
self.load_awards(fabs_broker_data=fabs_broker_data,
total_rows=total_rows)
with timer('Loading Transaction Normalized data', logger.info):
self.load_transaction_normalized(
fabs_broker_data=fabs_broker_data, total_rows=total_rows)
with timer('Loading Transaction FABS data', logger.info):
self.load_transaction_fabs(fabs_broker_data, total_rows)
award_update_id_list = [award.id for award in award_lookup]
with timer(
'Updating awards to reflect their latest associated transaction info',
logger.info):
update_awards(tuple(award_update_id_list))
with timer('Updating award category variables', logger.info):
update_award_categories(tuple(award_update_id_list))
else:
logger.info('Nothing to insert...FINISHED!')
def handle(self, *args, **options):
csv_file = options['file'][0]
self.logger.info("Starting load for file {}".format(csv_file))
# Create the csv reader
reader = CsvDataReader(csv_file)
# Create a new submission attributes object for this timestamp
subattr = SubmissionAttributes()
subattr.usaspending_update = datetime.now()
subattr.save()
# Create lists to hold model instances for bulk insert
txn_list = []
txn_contract_list = []
subtier_agency_dict = h.get_subtier_agency_dict()
# Store some additional support data needed for the laod
award_type_dict = {a[0]: a[1] for a in AWARD_TYPES}
contract_pricing_dict = {c[0]: c[1] for c in CONTRACT_PRICING_TYPES}
for idx, row in enumerate(reader):
if len(reader) % 1000 == 0:
self.logger.info("Read row {}".format(len(reader)))
row = h.cleanse_values(row)
awarding_agency_id = self.get_agency_id(row["contractingofficeagencyid"], subtier_agency_dict)
# Create the transaction object for this row
txn_dict = {
"action_date": h.convert_date(row['signeddate']),
"award": self.get_or_create_award(row, awarding_agency_id),
"awarding_agency_id": awarding_agency_id,
"data_source": "USA",
"description": row["descriptionofcontractrequirement"],
"federal_action_obligation": row["dollarsobligated"],
"funding_agency_id": self.get_agency_id(row["fundingrequestingagencyid"], subtier_agency_dict),
"last_modified_date": h.convert_date(row['last_modified_date']),
"modification_number": row["modnumber"],
"place_of_performance": h.get_or_create_location(
row, mapper=location_mapper_place_of_performance),
"period_of_performance_current_end_date": h.convert_date(row['currentcompletiondate']),
"period_of_performance_start_date": h.convert_date(row['effectivedate']),
"recipient": self.get_or_create_recipient(row),
"submission": subattr,
"type": evaluate_contract_award_type(row),
"type_description": award_type_dict.get(evaluate_contract_award_type(row)),
"usaspending_unique_transaction_id": row["unique_transaction_id"]
}
txn = Transaction(**txn_dict)
txn_list.append(txn)
# Create the transaction contract object for this row
txn_contract_dict = {
"submission": subattr,
"piid": row['piid'],
"parent_award_id": row['idvpiid'],
"current_total_value_award": h.parse_numeric_value(row["baseandexercisedoptionsvalue"]),
"period_of_performance_potential_end_date": h.convert_date(row['ultimatecompletiondate']),
"potential_total_value_of_award": h.parse_numeric_value(row["baseandalloptionsvalue"]),
"epa_designated_product": self.parse_first_character(row['useofepadesignatedproducts']),
"gfe_gfp": h.up2colon(row['gfe_gfp']),
"cost_or_pricing_data": h.up2colon(row['costorpricingdata']),
"type_of_contract_pricing": h.up2colon(row['typeofcontractpricing']),
"type_of_contract_pricing_description": contract_pricing_dict.get(h.up2colon(row['typeofcontractpricing'])),
"multiple_or_single_award_idv": h.up2colon(row['multipleorsingleawardidc']),
"naics": h.up2colon(row['nationalinterestactioncode']),
"dod_claimant_program_code": h.up2colon(row['claimantprogramcode']),
"commercial_item_acquisition_procedures": h.up2colon(
row['commercialitemacquisitionprocedures']),
"commercial_item_test_program": h.up2colon(row['commercialitemtestprogram']),
"consolidated_contract": h.up2colon(row['consolidatedcontract']),
"contingency_humanitarian_or_peacekeeping_operation": h.up2colon(
row['contingencyhumanitarianpeacekeepingoperation']),
"contract_bundling": h.up2colon(row['contractbundling']),
"contract_financing": h.up2colon(row['contractfinancing']),
"contracting_officers_determination_of_business_size": h.up2colon(
row['contractingofficerbusinesssizedetermination']),
"country_of_product_or_service_origin": h.up2colon(row['countryoforigin']),
"davis_bacon_act": h.up2colon(row['davisbaconact']),
"evaluated_preference": h.up2colon(row['evaluatedpreference']),
"extent_competed": h.up2colon(row['extentcompeted']),
"information_technology_commercial_item_category": h.up2colon(
row['informationtechnologycommercialitemcategory']),
"interagency_contracting_authority": h.up2colon(row['interagencycontractingauthority']),
"local_area_set_aside": h.up2colon(row['localareasetaside']),
"purchase_card_as_payment_method": h.up2colon(row['purchasecardaspaymentmethod']),
"multi_year_contract": h.up2colon(row['multiyearcontract']),
"national_interest_action": h.up2colon(row['nationalinterestactioncode']),
"number_of_actions": h.up2colon(row['numberofactions']),
"number_of_offers_received": h.up2colon(row['numberofoffersreceived']),
"performance_based_service_acquisition": h.up2colon(row['performancebasedservicecontract']),
"place_of_manufacture": h.up2colon(row['placeofmanufacture']),
"product_or_service_code": h.up2colon(row['productorservicecode']),
"recovered_materials_sustainability": h.up2colon(row['recoveredmaterialclauses']),
"research": h.up2colon(row['research']),
"sea_transportation": h.up2colon(row['seatransportation']),
"service_contract_act": h.up2colon(row['servicecontractact']),
"small_business_competitiveness_demonstration_program": self.parse_first_character(
row['smallbusinesscompetitivenessdemonstrationprogram']),
"solicitation_procedures": h.up2colon(row['solicitationprocedures']),
"subcontracting_plan": h.up2colon(row['subcontractplan']),
"type_set_aside": h.up2colon(row['typeofsetaside']),
"walsh_healey_act": h.up2colon(row['walshhealyact']),
"rec_flag": self.parse_first_character(h.up2colon(row['rec_flag'])),
"type_of_idc": self.parse_first_character(row['typeofidc']),
"a76_fair_act_action": self.parse_first_character(row['a76action']),
"clinger_cohen_act_planning": self.parse_first_character(row['clingercohenact']),
"cost_accounting_standards": self.parse_first_character(
row['costaccountingstandardsclause']),
"fed_biz_opps": self.parse_first_character(row['fedbizopps']),
"foreign_funding": self.parse_first_character(row['fundedbyforeignentity']),
"major_program": self.parse_first_character(row['majorprogramcode']),
"program_acronym": self.parse_first_character(row['programacronym']),
"referenced_idv_modification_number": self.parse_first_character(
row['idvmodificationnumber']),
"transaction_number": self.parse_first_character(row['transactionnumber']),
"solicitation_identifier": self.parse_first_character(row['solicitationid'])
}
txn_contract = TransactionContract(**txn_contract_dict)
txn_contract_list.append(txn_contract)
# Bulk insert transaction rows
self.logger.info("Starting Transaction bulk insert ({} records)".format(len(txn_list)))
Transaction.objects.bulk_create(txn_list)
self.logger.info("Completed Transaction bulk insert")
# Update txn contract list with newly-inserted transactions
award_id_list = [] # we'll need this when updating the awards later on
for idx, t in enumerate(txn_contract_list):
# add transaction info to this TransactionContract object
t.transaction = txn_list[idx]
# add the corresponding award id to a list we'll use when batch-updating award data
award_id_list.append(txn_list[idx].award_id)
# Bulk insert transaction contract rows
self.logger.info("Starting TransactionContract bulk insert ({} records)".format(len(txn_contract_list)))
TransactionContract.objects.bulk_create(txn_contract_list)
self.logger.info("Completed TransactionContract bulk insert")
# Update awards to reflect latest transaction information
# (note that this can't be done via signals or a save()
# override in the model itself, because those aren't
# triggered by a bulk update
self.logger.info("Starting Awards update")
count = update_awards(tuple(award_id_list))
update_contract_awards(tuple(award_id_list))
self.logger.info("Completed Awards update ({} records)".format(count))
def awards_and_transactions(transactional_db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make("download.JobStatus",
job_status_id=js.id,
name=js.name,
description=js.desc)
# Awards
award1 = mommy.make("awards.Award",
type="07",
total_loan_value=3,
generated_unique_award_id="ASST_NEW_1")
award2 = mommy.make("awards.Award",
type="07",
total_loan_value=30,
generated_unique_award_id="ASST_NEW_2")
award3 = mommy.make("awards.Award",
type="08",
total_loan_value=300,
generated_unique_award_id="ASST_NEW_3")
award4 = mommy.make("awards.Award",
type="B",
total_loan_value=0,
generated_unique_award_id="CONT_NEW_1")
award5 = mommy.make("awards.Award",
type="A",
total_loan_value=0,
generated_unique_award_id="CONT_NEW_2")
award6 = mommy.make("awards.Award",
type="C",
total_loan_value=0,
generated_unique_award_id="CONT_NEW_3")
award7 = mommy.make("awards.Award",
type="D",
total_loan_value=0,
generated_unique_award_id="CONT_NEW_4")
# Disaster Emergency Fund Code
defc1 = mommy.make(
"references.DisasterEmergencyFundCode",
code="L",
public_law="PUBLIC LAW FOR CODE L",
title="TITLE FOR CODE L",
group_name="covid_19",
)
defc2 = mommy.make(
"references.DisasterEmergencyFundCode",
code="M",
public_law="PUBLIC LAW FOR CODE M",
title="TITLE FOR CODE M",
group_name="covid_19",
)
mommy.make(
"references.DisasterEmergencyFundCode",
code="N",
public_law="PUBLIC LAW FOR CODE N",
title="TITLE FOR CODE N",
group_name="covid_19",
)
# Submission Attributes
sub1 = mommy.make(
"submissions.SubmissionAttributes",
reporting_fiscal_year=2022,
reporting_fiscal_period=8,
quarter_format_flag=False,
reporting_period_start="2022-05-01",
)
sub2 = mommy.make(
"submissions.SubmissionAttributes",
reporting_fiscal_year=2022,
reporting_fiscal_period=8,
quarter_format_flag=False,
reporting_period_start="2022-05-01",
)
sub3 = mommy.make(
"submissions.SubmissionAttributes",
reporting_fiscal_year=2022,
reporting_fiscal_period=8,
quarter_format_flag=False,
reporting_period_start="2022-05-01",
)
# Financial Accounts by Awards
mommy.make(
"awards.FinancialAccountsByAwards",
pk=1,
award=award1,
submission=sub1,
disaster_emergency_fund=defc1,
gross_outlay_amount_by_award_cpe=1,
transaction_obligated_amount=2,
)
mommy.make(
"awards.FinancialAccountsByAwards",
pk=2,
award=award2,
submission=sub1,
disaster_emergency_fund=defc1,
gross_outlay_amount_by_award_cpe=10,
transaction_obligated_amount=20,
)
mommy.make(
"awards.FinancialAccountsByAwards",
pk=3,
award=award3,
submission=sub2,
disaster_emergency_fund=defc1,
gross_outlay_amount_by_award_cpe=100,
transaction_obligated_amount=200,
)
mommy.make(
"awards.FinancialAccountsByAwards",
pk=4,
award=award4,
submission=sub2,
disaster_emergency_fund=defc1,
gross_outlay_amount_by_award_cpe=1000,
transaction_obligated_amount=2000,
)
mommy.make(
"awards.FinancialAccountsByAwards",
pk=5,
award=award5,
submission=sub3,
disaster_emergency_fund=defc2,
gross_outlay_amount_by_award_cpe=10000,
transaction_obligated_amount=20000,
)
mommy.make(
"awards.FinancialAccountsByAwards",
pk=6,
award=award6,
submission=sub3,
disaster_emergency_fund=defc2,
gross_outlay_amount_by_award_cpe=100000,
transaction_obligated_amount=200000,
)
mommy.make(
"awards.FinancialAccountsByAwards",
pk=7,
award=award7,
submission=sub3,
disaster_emergency_fund=defc2,
gross_outlay_amount_by_award_cpe=1000000,
transaction_obligated_amount=2000000,
)
# DABS Submission Window Schedule
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2022081",
is_quarter=False,
period_start_date="2022-05-01",
period_end_date="2022-05-30",
submission_fiscal_year=2022,
submission_fiscal_quarter=3,
submission_fiscal_month=8,
submission_reveal_date="2020-5-15",
)
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2022080",
is_quarter=True,
period_start_date="2022-05-01",
period_end_date="2022-05-30",
submission_fiscal_year=2022,
submission_fiscal_quarter=3,
submission_fiscal_month=8,
submission_reveal_date="2020-5-15",
)
# Transaction Normalized
mommy.make(
"awards.TransactionNormalized",
id=10,
award=award1,
federal_action_obligation=5,
action_date="2022-01-01",
is_fpds=False,
unique_award_key="ASST_NEW_1",
)
mommy.make(
"awards.TransactionNormalized",
id=20,
award=award2,
federal_action_obligation=50,
action_date="2022-01-02",
is_fpds=False,
unique_award_key="ASST_NEW_2",
)
mommy.make(
"awards.TransactionNormalized",
id=30,
award=award3,
federal_action_obligation=500,
action_date="2022-01-03",
is_fpds=False,
unique_award_key="ASST_NEW_3",
)
mommy.make(
"awards.TransactionNormalized",
id=40,
award=award4,
federal_action_obligation=5000,
action_date="2022-01-04",
is_fpds=True,
unique_award_key="CONT_NEW_1",
)
mommy.make(
"awards.TransactionNormalized",
id=50,
award=award5,
federal_action_obligation=50000,
action_date="2022-01-05",
is_fpds=True,
unique_award_key="CONT_NEW_2",
)
mommy.make(
"awards.TransactionNormalized",
id=60,
award=award6,
federal_action_obligation=500000,
action_date="2022-01-06",
is_fpds=True,
unique_award_key="CONT_NEW_3",
)
mommy.make(
"awards.TransactionNormalized",
id=70,
award=award7,
federal_action_obligation=5000000,
action_date="2022-01-07",
is_fpds=True,
unique_award_key="CONT_NEW_4",
)
# Transaction FABS
mommy.make(
"awards.TransactionFABS",
transaction_id=10,
cfda_number="10.100",
legal_entity_country_code="USA",
legal_entity_state_code=None,
legal_entity_county_code=None,
legal_entity_county_name=None,
legal_entity_congressional=None,
awardee_or_recipient_legal="RECIPIENT 1",
awardee_or_recipient_uniqu=None,
)
mommy.make(
"awards.TransactionFABS",
transaction_id=20,
cfda_number="20.200",
legal_entity_country_code="USA",
legal_entity_state_code="SC",
legal_entity_county_code="001",
legal_entity_county_name="CHARLESTON",
legal_entity_congressional="90",
awardee_or_recipient_legal="RECIPIENT 2",
awardee_or_recipient_uniqu="456789123",
)
mommy.make(
"awards.TransactionFABS",
transaction_id=30,
cfda_number="20.200",
legal_entity_country_code="USA",
legal_entity_state_code="SC",
legal_entity_county_code="001",
legal_entity_county_name="CHARLESTON",
legal_entity_congressional="50",
awardee_or_recipient_legal="RECIPIENT 3",
awardee_or_recipient_uniqu="987654321",
)
# Transaction FPDS
mommy.make(
"awards.TransactionFPDS",
transaction_id=40,
legal_entity_country_code="USA",
legal_entity_state_code="WA",
legal_entity_county_code="005",
legal_entity_county_name="TEST NAME",
legal_entity_congressional="50",
awardee_or_recipient_legal="MULTIPLE RECIPIENTS",
awardee_or_recipient_uniqu="096354360",
)
mommy.make(
"awards.TransactionFPDS",
transaction_id=50,
legal_entity_country_code="USA",
legal_entity_state_code="WA",
legal_entity_county_code="005",
legal_entity_county_name="TEST NAME",
legal_entity_congressional="50",
awardee_or_recipient_legal=None,
awardee_or_recipient_uniqu="987654321",
)
mommy.make(
"awards.TransactionFPDS",
transaction_id=60,
legal_entity_country_code="USA",
legal_entity_state_code="SC",
legal_entity_county_code="005",
legal_entity_county_name="TEST NAME",
legal_entity_congressional="50",
awardee_or_recipient_legal=None,
awardee_or_recipient_uniqu="987654321",
)
mommy.make(
"awards.TransactionFPDS",
transaction_id=70,
legal_entity_country_code="USA",
legal_entity_state_code="SC",
legal_entity_county_code="01",
legal_entity_county_name="CHARLESTON",
legal_entity_congressional="10",
awardee_or_recipient_legal="MULTIPLE RECIPIENTS",
awardee_or_recipient_uniqu=None,
)
# Recipient Profile
mommy.make(
"recipient.RecipientProfile",
recipient_name="RECIPIENT 1",
recipient_level="R",
recipient_hash="5f572ec9-8b49-e5eb-22c7-f6ef316f7689",
recipient_unique_id=None,
)
mommy.make(
"recipient.RecipientProfile",
recipient_name="RECIPIENT 2",
recipient_level="R",
recipient_hash="3c92491a-f2cd-ec7d-294b-7daf91511866",
recipient_unique_id="456789123",
)
mommy.make(
"recipient.RecipientProfile",
recipient_name="RECIPIENT 3",
recipient_level="P",
recipient_hash="d2894d22-67fc-f9cb-4005-33fa6a29ef86",
recipient_unique_id="987654321",
)
mommy.make(
"recipient.RecipientProfile",
recipient_name="RECIPIENT 3",
recipient_level="C",
recipient_hash="d2894d22-67fc-f9cb-4005-33fa6a29ef86",
recipient_unique_id="987654321",
)
mommy.make(
"recipient.RecipientProfile",
recipient_name="MULTIPLE RECIPIENTS",
recipient_level="R",
recipient_hash="5bf6217b-4a70-da67-1351-af6ab2e0a4b3",
recipient_unique_id="096354360",
)
mommy.make(
"recipient.RecipientProfile",
recipient_name="RECIPIENT 3",
recipient_level="R",
recipient_hash="d2894d22-67fc-f9cb-4005-33fa6a29ef86",
recipient_unique_id="987654321",
)
# Recipient Lookup
mommy.make(
"recipient.RecipientLookup",
legal_business_name="RECIPIENT 3",
recipient_hash="d2894d22-67fc-f9cb-4005-33fa6a29ef86",
duns="987654321",
)
# Set latest_award for each award
update_awards()
def award_data(transactional_db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make("download.JobStatus",
job_status_id=js.id,
name=js.name,
description=js.desc)
# Create Awarding Top Agency
ata1 = mommy.make(
"references.ToptierAgency",
toptier_agency_id=1,
name="Bureau of Things",
toptier_code="100",
website="http://test.com",
mission="test",
icon_filename="test",
)
ata2 = mommy.make(
"references.ToptierAgency",
toptier_agency_id=2,
name="Bureau of Stuff",
toptier_code="101",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Awarding subs
asa1 = mommy.make("references.SubtierAgency", name="SubBureau of Things")
asa2 = mommy.make("references.SubtierAgency", name="SubBureau of Stuff")
# Create Awarding Agencies
aa1 = mommy.make("references.Agency",
toptier_agency=ata1,
subtier_agency=asa1,
toptier_flag=False,
user_selectable=True)
aa2 = mommy.make("references.Agency",
toptier_agency=ata2,
subtier_agency=asa2,
toptier_flag=False,
user_selectable=True)
# Create Funding Top Agency
fta = mommy.make(
"references.ToptierAgency",
toptier_agency_id=3,
name="Bureau of Money",
toptier_code="102",
website="http://test.com",
mission="test",
icon_filename="test",
)
# Create Funding SUB
fsa1 = mommy.make("references.SubtierAgency", name="Bureau of Things")
# Create Funding Agency
mommy.make("references.Agency",
toptier_agency=fta,
subtier_agency=fsa1,
toptier_flag=False)
# Create Federal Account
mommy.make("accounts.FederalAccount",
account_title="Compensation to Accounts",
agency_identifier="102",
id=1)
# Create Awards
mommy.make("awards.Award",
id=1,
category="contracts",
generated_unique_award_id="TEST_AWARD_1")
mommy.make("awards.Award",
id=2,
category="contracts",
generated_unique_award_id="TEST_AWARD_2")
mommy.make("awards.Award",
id=3,
category="assistance",
generated_unique_award_id="TEST_AWARD_3")
mommy.make("awards.Award",
id=4,
category="contracts",
generated_unique_award_id="TEST_AWARD_4")
mommy.make("awards.Award",
id=5,
category="assistance",
generated_unique_award_id="TEST_AWARD_5")
mommy.make("awards.Award",
id=6,
category="assistance",
generated_unique_award_id="TEST_AWARD_6")
mommy.make("awards.Award",
id=7,
category="contracts",
generated_unique_award_id="TEST_AWARD_7")
mommy.make("awards.Award",
id=8,
category="assistance",
generated_unique_award_id="TEST_AWARD_8")
mommy.make("awards.Award",
id=9,
category="assistance",
generated_unique_award_id="TEST_AWARD_9")
# Create Transactions
mommy.make(
TransactionNormalized,
id=1,
award_id=1,
modification_number=1,
awarding_agency=aa1,
unique_award_key="TEST_AWARD_1",
action_date="2017-01-01",
type="A",
is_fpds=True,
)
mommy.make(
TransactionNormalized,
id=2,
award_id=2,
modification_number=1,
awarding_agency=aa2,
unique_award_key="TEST_AWARD_2",
action_date="2017-04-01",
type="IDV_B",
is_fpds=True,
)
mommy.make(
TransactionNormalized,
id=3,
award_id=3,
modification_number=1,
awarding_agency=aa2,
unique_award_key="TEST_AWARD_3",
action_date="2017-06-01",
type="02",
is_fpds=False,
)
mommy.make(
TransactionNormalized,
id=4,
award_id=4,
modification_number=1,
awarding_agency=aa1,
unique_award_key="TEST_AWARD_4",
action_date="2018-01-15",
type="A",
is_fpds=True,
)
mommy.make(
TransactionNormalized,
id=5,
award_id=5,
modification_number=1,
awarding_agency=aa2,
unique_award_key="TEST_AWARD_5",
action_date="2018-03-15",
type="07",
is_fpds=False,
)
mommy.make(
TransactionNormalized,
id=6,
award_id=6,
modification_number=1,
awarding_agency=aa2,
unique_award_key="TEST_AWARD_6",
action_date="2018-06-15",
type="02",
is_fpds=False,
)
mommy.make(
TransactionNormalized,
id=7,
award_id=7,
modification_number=1,
awarding_agency=aa1,
unique_award_key="TEST_AWARD_7",
action_date="2017-01-15",
type="A",
is_fpds=True,
)
mommy.make(
TransactionNormalized,
id=8,
award_id=8,
modification_number=1,
awarding_agency=aa2,
unique_award_key="TEST_AWARD_8",
action_date="2017-03-15",
type="07",
is_fpds=False,
)
mommy.make(
TransactionNormalized,
id=9,
award_id=9,
modification_number=1,
awarding_agency=aa2,
unique_award_key="TEST_AWARD_9",
action_date="2017-06-15",
type="02",
is_fpds=False,
)
# Create TransactionContract
mommy.make(
TransactionFPDS,
transaction_id=1,
piid="tc1piid",
unique_award_key="TEST_AWARD_1",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_c="USA",
place_of_perf_country_desc="UNITED STATES",
)
mommy.make(
TransactionFPDS,
transaction_id=2,
piid="tc2piid",
unique_award_key="TEST_AWARD_2",
legal_entity_country_code="CAN",
legal_entity_country_name="CANADA",
place_of_perform_country_c="CAN",
place_of_perf_country_desc="CANADA",
)
mommy.make(
TransactionFPDS,
transaction_id=4,
piid="tc4piid",
unique_award_key="TEST_AWARD_4",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_c="USA",
place_of_perf_country_desc="UNITED STATES",
)
mommy.make(
TransactionFPDS,
transaction_id=7,
piid="tc7piid",
unique_award_key="TEST_AWARD_7",
legal_entity_country_code="CAN",
legal_entity_country_name="CANADA",
place_of_perform_country_c="CAN",
place_of_perf_country_desc="CANADA",
)
# Create TransactionAssistance
mommy.make(
TransactionFABS,
transaction_id=3,
fain="ta1fain",
unique_award_key="TEST_AWARD_3",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_c="USA",
place_of_perform_country_n="UNITED STATES",
)
mommy.make(
TransactionFABS,
transaction_id=5,
fain="ta5fain",
unique_award_key="TEST_AWARD_5",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_c="USA",
place_of_perform_country_n="UNITED STATES",
)
mommy.make(
TransactionFABS,
transaction_id=6,
fain="ta6fain",
unique_award_key="TEST_AWARD_6",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_c="USA",
place_of_perform_country_n="UNITED STATES",
)
mommy.make(
TransactionFABS,
transaction_id=8,
fain="ta8fain",
unique_award_key="TEST_AWARD_8",
legal_entity_country_code="USA",
place_of_perform_country_c="USA",
)
mommy.make(
TransactionFABS,
transaction_id=9,
fain="ta9fain",
unique_award_key="TEST_AWARD_9",
legal_entity_country_code="CAN",
legal_entity_country_name="CANADA",
place_of_perform_country_c="CAN",
place_of_perform_country_n="CANADA",
)
# Create Subaward
mommy.make(
Subaward,
id=1,
award_id=4,
latest_transaction_id=4,
action_date="2018-01-15",
award_type="procurement",
recipient_location_country_code="USA",
recipient_location_country_name="UNITED STATES",
pop_country_code="USA",
pop_country_name="UNITED STATES",
)
mommy.make(
Subaward,
id=2,
award_id=5,
latest_transaction_id=5,
action_date="2018-03-15",
award_type="grant",
recipient_location_country_code="USA",
recipient_location_country_name="UNITED STATES",
pop_country_code="USA",
pop_country_name="UNITED STATES",
)
mommy.make(
Subaward,
id=3,
award_id=6,
latest_transaction_id=6,
action_date="2018-06-15",
award_type="grant",
recipient_location_country_code="USA",
recipient_location_country_name="UNITED STATES",
pop_country_code="USA",
pop_country_name="UNITED STATES",
)
mommy.make(
Subaward,
id=4,
award_id=7,
latest_transaction_id=7,
action_date="2017-01-15",
award_type="procurement",
recipient_location_country_code="USA",
recipient_location_country_name="UNITED STATES",
pop_country_code="USA",
pop_country_name="UNITED STATES",
)
mommy.make(
Subaward,
id=5,
award_id=8,
latest_transaction_id=8,
action_date="2017-03-15",
award_type="grant",
recipient_location_country_code="CAN",
recipient_location_country_name="CANADA",
pop_country_code="CAN",
pop_country_name="CANADA",
)
mommy.make(
Subaward,
id=6,
award_id=9,
latest_transaction_id=9,
action_date="2017-06-15",
award_type="grant",
recipient_location_country_code="CAN",
recipient_location_country_name="CANADA",
pop_country_code="CAN",
pop_country_name="CANADA",
)
# Create BrokerSubaward
mommy.make(
BrokerSubaward,
id=1,
prime_id=4,
action_date="2018-01-15",
subaward_type="sub-contract",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_co="USA",
place_of_perform_country_na="UNITED STATES",
)
mommy.make(
BrokerSubaward,
id=2,
prime_id=5,
action_date="2018-03-15",
subaward_type="sub-grant",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_co="USA",
place_of_perform_country_na="UNITED STATES",
)
mommy.make(
BrokerSubaward,
id=3,
prime_id=6,
action_date="2018-06-15",
subaward_type="sub-grant",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_co="USA",
place_of_perform_country_na="UNITED STATES",
)
mommy.make(
BrokerSubaward,
id=4,
prime_id=7,
action_date="2017-01-15",
subaward_type="sub-contract",
legal_entity_country_code="USA",
legal_entity_country_name="UNITED STATES",
place_of_perform_country_co="USA",
place_of_perform_country_na="UNITED STATES",
)
mommy.make(
BrokerSubaward,
id=5,
prime_id=8,
action_date="2017-03-15",
subaward_type="sub-grant",
legal_entity_country_code="CAN",
legal_entity_country_name="CANADA",
place_of_perform_country_co="CAN",
place_of_perform_country_na="CANADA",
)
mommy.make(
BrokerSubaward,
id=6,
prime_id=9,
action_date="2017-06-15",
subaward_type="sub-grant",
legal_entity_country_code="CAN",
legal_entity_country_name="CANADA",
place_of_perform_country_co="CAN",
place_of_perform_country_na="CANADA",
)
# Ref Country Code
mommy.make("references.RefCountryCode",
country_code="USA",
country_name="UNITED STATES")
mommy.make("references.RefCountryCode",
country_code="CAN",
country_name="CANADA")
# Set latest_award for each award
update_awards()
def test_award_update_from_latest_transaction():
"""Test awards fields that should be updated with most recent transaction info."""
agency1 = mommy.make("references.Agency")
agency2 = mommy.make("references.Agency")
award = mommy.make(
"awards.Award",
awarding_agency=agency1,
period_of_performance_current_end_date=datetime.date(2016, 1, 1),
description="original award",
generated_unique_award_id="AWD_1",
)
# adding transaction with same info should not change award values
transaction = mommy.make(
"awards.TransactionNormalized",
award=award,
awarding_agency=award.awarding_agency,
period_of_performance_current_end_date=award.
period_of_performance_current_end_date,
description=award.description,
action_date=datetime.date(2016, 2, 1),
unique_award_key="AWD_1",
)
update_awards()
award.refresh_from_db()
assert award.awarding_agency == agency1
assert award.period_of_performance_current_end_date == datetime.date(
2016, 1, 1)
assert award.description == "original award"
assert award.latest_transaction == transaction
# adding an older transaction with different info updates award's total obligation amt and the description
# (which is sourced from the earliest txn), but other info remains unchanged
mommy.make(
"awards.TransactionNormalized",
award=award,
awarding_agency=agency2,
period_of_performance_current_end_date=datetime.date(2017, 1, 1),
description="older description",
action_date=datetime.date(2016, 1, 1),
unique_award_key="AWD_1",
)
update_awards()
award.refresh_from_db()
assert award.awarding_agency == agency1
assert award.period_of_performance_current_end_date == datetime.date(
2016, 1, 1)
assert award.description == "older description"
# adding an newer transaction with different info updates award's total obligation amt and also overrides
# other values
mommy.make(
"awards.TransactionNormalized",
id=999,
award=award,
awarding_agency=agency2,
period_of_performance_current_end_date=datetime.date(2010, 1, 1),
description="new description",
action_date=datetime.date(2017, 1, 1),
unique_award_key="AWD_1",
)
update_awards()
award.refresh_from_db()
assert award.awarding_agency == agency2
assert award.period_of_performance_current_end_date == datetime.date(
2010, 1, 1)
# award desc should still reflect the earliest txn
assert award.description == "older description"
def test_award_update_transaction_fk():
"""Test executive comp is loaded correctly awards from txn contract."""
award = mommy.make("awards.Award",
generated_unique_award_id="FAKE_award_YELLOW_12")
txn1 = mommy.make(
"awards.TransactionNormalized",
award=award,
action_date="2011-10-01",
description="Original Desc",
modification_number="P0001",
unique_award_key="FAKE_award_YELLOW_12",
)
mommy.make(
"awards.TransactionNormalized",
award=award,
action_date="2012-10-01",
unique_award_key="FAKE_award_YELLOW_12",
)
mommy.make(
"awards.TransactionNormalized",
award=award,
action_date="2013-10-01",
unique_award_key="FAKE_award_YELLOW_12",
)
mommy.make(
"awards.TransactionNormalized",
award=award,
action_date="2014-10-01",
unique_award_key="FAKE_award_YELLOW_12",
)
mommy.make(
"awards.TransactionNormalized",
award=award,
action_date="2015-10-01",
unique_award_key="FAKE_award_YELLOW_12",
)
txn6 = mommy.make(
"awards.TransactionNormalized",
award=award,
action_date="2016-10-01",
description="Last Desc",
modification_number="P0011",
period_of_performance_current_end_date="2020-10-01",
unique_award_key="FAKE_award_YELLOW_12",
)
update_awards()
award.refresh_from_db()
assert award.description == txn1.description
assert award.earliest_transaction == txn1
assert award.latest_transaction == txn6
assert award.date_signed.strftime("%Y-%m-%d") == txn1.action_date
assert award.certified_date.strftime("%Y-%m-%d") == txn6.action_date
assert (award.period_of_performance_current_end_date.strftime("%Y-%m-%d")
== txn6.period_of_performance_current_end_date)
txn0 = mommy.make(
"awards.TransactionNormalized",
award=award,
action_date=txn1.action_date,
description="Updated Original Desc",
modification_number="P0000",
unique_award_key="FAKE_award_YELLOW_12",
)
txn10 = mommy.make(
"awards.TransactionNormalized",
award=award,
action_date=txn6.action_date,
modification_number="P1000",
period_of_performance_current_end_date="2019-10-01",
unique_award_key="FAKE_award_YELLOW_12",
)
update_awards()
award.refresh_from_db()
assert award.description == txn0.description
assert award.earliest_transaction == txn0
assert award.latest_transaction == txn10
assert award.date_signed.strftime("%Y-%m-%d") == txn1.action_date
assert award.certified_date.strftime("%Y-%m-%d") == txn6.action_date
assert (award.period_of_performance_current_end_date.strftime("%Y-%m-%d")
== txn10.period_of_performance_current_end_date)
def test_award_update_from_latest_transaction(agencies):
"""Test awards fields that should be updated with most recent transaction info."""
agency1 = Agency.objects.get(id=1)
agency2 = Agency.objects.get(id=2)
award = mommy.make('awards.Award',
awarding_agency=agency1,
period_of_performance_current_end_date=datetime.date(
2016, 1, 1),
description='original award')
# adding transaction with same info should not change award values
mommy.make('awards.Transaction',
award=award,
awarding_agency=award.awarding_agency,
period_of_performance_current_end_date=award.
period_of_performance_current_end_date,
description=award.description,
action_date=datetime.date(2016, 2, 1))
update_awards()
award.refresh_from_db()
assert award.awarding_agency == agency1
assert award.period_of_performance_current_end_date == datetime.date(
2016, 1, 1)
assert award.description == 'original award'
# adding an older transaction with different info updates award's total
# obligation amt and the description (which is sourced from the
# earliest txn), but other info remains unchanged
mommy.make('awards.Transaction',
award=award,
awarding_agency=agency2,
period_of_performance_current_end_date=datetime.date(
2017, 1, 1),
description='older description',
action_date=datetime.date(2016, 1, 1))
update_awards()
award.refresh_from_db()
assert award.awarding_agency == agency1
assert award.period_of_performance_current_end_date == datetime.date(
2016, 1, 1)
assert award.description == 'older description'
# adding an newer transaction with different info updates award's total
# obligation amt and also overrides other values
mommy.make('awards.Transaction',
id=999,
award=award,
awarding_agency=agency2,
period_of_performance_current_end_date=datetime.date(
2010, 1, 1),
description='new description',
action_date=datetime.date(2017, 1, 1))
update_awards()
award.refresh_from_db()
assert award.awarding_agency == agency2
assert award.period_of_performance_current_end_date == datetime.date(
2010, 1, 1)
# award desc should still reflect the earliest txn
assert award.description == 'older description'
def handle(self, *args, **options):
h.clear_caches()
csv_file = options['file'][0]
self.logger.info("Starting load for file {}".format(csv_file))
# Create the csv reader
reader = CsvDataReader(csv_file)
# Create a new submission attributes object for this timestamp
subattr = SubmissionAttributes()
subattr.usaspending_update = datetime.now()
subattr.save()
# Create lists to hold model instances for bulk insert
txn_list = []
txn_assistance_list = []
for idx, row in enumerate(reader):
if len(reader) % 1000 == 0:
self.logger.info("Read row {}".format(len(reader)))
row = h.cleanse_values(row)
awarding_agency = self.get_awarding_agency(
row) # todo: use agency dict?
# Create the transaction object for this row
txn_dict = {
"submission":
subattr,
"action_date":
h.convert_date(row['obligation_action_date']),
"action_type":
h.up2colon(row['action_type']),
"award":
self.get_or_create_award(row, awarding_agency=awarding_agency),
"awarding_agency":
awarding_agency,
"description":
row["project_description"], # ?? account_title is anther contender?
"data_source":
"USA",
"federal_action_obligation":
row["fed_funding_amount"],
"last_modified_date":
h.convert_date(row['last_modified_date']),
"modification_number":
row["federal_award_mod"], # ??
"period_of_performance_start_date":
h.convert_date(row['starting_date']),
"period_of_performance_current_end_date":
h.convert_date(row['ending_date']),
"place_of_performance":
h.get_or_create_location(
row, location_mapper_fin_assistance_principal_place),
"recipient":
self.get_or_create_recipient(row),
"type":
h.up2colon(row['assistance_type']),
"usaspending_unique_transaction_id":
row["unique_transaction_id"],
# ??"funding_agency_id":
# ?? "certified date":
}
txn = Transaction(**txn_dict)
txn.fiscal_year = fy(txn.action_date)
txn_list.append(txn)
# Create the transaction contract object for this row
txn_assistance_dict = {
"submission":
subattr,
"fain":
row["federal_award_id"],
"uri":
row["uri"],
"cfda":
Cfda.objects.filter(
program_number=row["cfda_program_num"]).first(),
"correction_late_delete_indicator":
h.up2colon(row['correction_late_ind']),
"face_value_loan_guarantee":
row["face_loan_guran"],
"fiscal_year_and_quarter_correction":
row["fyq_correction"],
"non_federal_funding_amount":
row["non_fed_funding_amount"],
"original_loan_subsidy_cost":
row["orig_sub_guran"], # ??
"record_type":
int(h.up2colon(row['record_type'])),
"sai_number":
row["sai_number"],
"submitted_type":
"C", # ?? For CSV?
}
# ?? business_funds_indicator
# ?? reporting period start/end??
txn_assistance = TransactionAssistance(**txn_assistance_dict)
txn_assistance_list.append(txn_assistance)
# Bulk insert transaction rows
self.logger.info(
"Starting Transaction bulk insert ({} records)".format(
len(txn_list)))
Transaction.objects.bulk_create(txn_list)
self.logger.info("Completed Transaction bulk insert")
# Update txn assistance list with newly-inserted transactions
award_id_list = [] # we'll need this when updating the awards later on
for idx, t in enumerate(txn_assistance_list):
t.transaction = txn_list[idx]
award_id_list.append(txn_list[idx].award_id)
# Bulk insert transaction assistance rows
self.logger.info(
"Starting TransactionAssistance bulk insert ({} records)".format(
len(txn_assistance_list)))
TransactionAssistance.objects.bulk_create(txn_assistance_list)
self.logger.info("Completed TransactionAssistance bulk insert")
# Update awards to reflect latest transaction information
# (note that this can't be done via signals or a save()
# override in the model itself, because those aren't
# triggered by a bulk update
self.logger.info("Starting Awards update")
count = update_awards(tuple(award_id_list))
update_contract_awards(tuple(award_id_list))
update_model_description_fields()
self.logger.info("Completed Awards update ({} records)".format(count))
def award_data(db):
# Populate job status lookup table
for js in JOB_STATUS:
mommy.make(
'download.JobStatus',
job_status_id=js.id,
name=js.name,
description=js.desc)
# Create Locations
mommy.make('references.Location')
# Create LE
mommy.make('references.LegalEntity')
# Create Awarding Top Agency
ata1 = mommy.make(
'references.ToptierAgency',
name='Bureau of Things',
cgac_code='100',
website='http://test.com',
mission='test',
icon_filename='test')
ata2 = mommy.make(
'references.ToptierAgency',
name='Bureau of Stuff',
cgac_code='101',
website='http://test.com',
mission='test',
icon_filename='test')
# Create Awarding subs
asa1 = mommy.make('references.SubtierAgency', name='Bureau of Things')
asa2 = mommy.make('references.SubtierAgency', name='Bureau of Stuff')
# Create Awarding Agencies
aa1 = mommy.make(
'references.Agency', toptier_agency=ata1, subtier_agency=asa1, toptier_flag=False)
aa2 = mommy.make(
'references.Agency', toptier_agency=ata2, subtier_agency=asa2, toptier_flag=False)
# Create Funding Top Agency
fta = mommy.make(
'references.ToptierAgency',
name='Bureau of Money',
cgac_code='102',
website='http://test.com',
mission='test',
icon_filename='test')
# Create Funding SUB
fsa1 = mommy.make('references.SubtierAgency', name='Bureau of Things')
# Create Funding Agency
mommy.make('references.Agency', toptier_agency=fta, subtier_agency=fsa1, toptier_flag=False)
# Create Federal Account
mommy.make(
'accounts.FederalAccount',
account_title='Compensation to Accounts',
agency_identifier='102',
id=1)
# Create Awards
award1 = mommy.make('awards.Award', category='contracts')
award2 = mommy.make('awards.Award', category='contracts')
award3 = mommy.make('awards.Award', category='assistance')
# Create Transactions
trann1 = mommy.make(
TransactionNormalized,
award=award1,
modification_number=1,
awarding_agency=aa1)
trann2 = mommy.make(
TransactionNormalized,
award=award2,
modification_number=1,
awarding_agency=aa2)
trann3 = mommy.make(
TransactionNormalized,
award=award3,
modification_number=1,
awarding_agency=aa2)
# Create TransactionContract
mommy.make(TransactionFPDS, transaction=trann1, piid='tc1piid')
mommy.make(TransactionFPDS, transaction=trann2, piid='tc2piid')
# Create TransactionAssistance
mommy.make(TransactionFABS, transaction=trann3, fain='ta1fain')
# Set latest_award for each award
update_awards()
