def handle(self, *args, **options):
logger.info('Starting FSRS data load...')
db_cursor = connections['data_broker'].cursor()
logger.info('Get Broker FSRS procurement data...')
self.process_award_type(db_cursor, "procurement", "subcontract")
logger.info('Get Broker FSRS grant data...')
self.process_award_type(db_cursor, "grant", "subgrant")
logger.info('Completed FSRS data load...')
logger.info('Updating related award metadata...')
update_award_subawards(tuple(award_update_id_list))
logger.info('Finished updating award metadata...')
logger.info('Load FSRS Script Complete!')
def handle(self, *args, **options):
logger.info('Starting FSRS data load...')
db_cursor = connections['data_broker'].cursor()
# This is called at the start to prevent duplicating efforts
# It may be called later but requires update_award_subawards to be called after
logger.info('Cleaning up previous subawards without parent awards...')
self.cleanup_broken_links(db_cursor)
logger.info('Get Broker FSRS procurement data...')
self.process_award_type(db_cursor, "procurement", "subcontract")
logger.info('Get Broker FSRS grant data...')
self.process_award_type(db_cursor, "grant", "subgrant")
logger.info('Completed FSRS data load...')
logger.info('Updating related award metadata...')
update_award_subawards(tuple(award_update_id_list))
logger.info('Finished updating award metadata...')
logger.info('Load FSRS Script Complete!')
def load_subawards(submission_attributes, db_cursor):
"""
Loads File F from the broker. db_cursor should be the db_cursor for Broker
"""
# A list of award id's to update the subaward accounts and totals on
award_ids_to_update = set()
# Get a list of PIIDs from this submission
awards_for_sub = Award.objects.filter(transaction__submission=submission_attributes).distinct()
piids = list(awards_for_sub.values_list("piid", flat=True))
fains = list(awards_for_sub.values_list("fain", flat=True))
# This allows us to handle an empty list in the SQL without changing the query
piids.append(None)
fains.append(None)
# D1 File F
db_cursor.execute(D1_FILE_F_QUERY, [submission_attributes.broker_submission_id, tuple(piids)])
d1_f_data = dictfetchall(db_cursor)
logger.info("Creating D1 F File Entries (Subcontracts): {}".format(len(d1_f_data)))
d1_create_count = 0
d1_update_count = 0
d1_empty_count = 0
for row in d1_f_data:
if row['subcontract_num'] is None:
if row['id'] is not None and row['subcontract_amount'] is not None:
logger.warn("Subcontract of broker id {} has amount, but no number".format(row["id"]))
logger.warn("Failing row: {}".format(row))
else:
d1_empty_count += 1
continue
# Get the agency
agency = get_valid_awarding_agency(row)
if not agency:
logger.warn("Subaward number {} cannot find matching agency with toptier code {} and subtier code {}".format(row['subcontract_num'], row['awarding_agency_code'], row['awarding_sub_tier_agency_c']))
continue
# Find the award to attach this sub-contract to
# We perform this lookup by finding the Award containing a transaction with
# a matching parent award id, piid, and submission attributes
award = Award.objects.filter(awarding_agency=agency,
transaction__submission=submission_attributes,
transaction__contract_data__piid=row['piid'],
transaction__contract_data__isnull=False,
transaction__contract_data__parent_award_id=row['parent_award_id']).distinct().order_by("-date_signed").first()
# We don't have a matching award for this subcontract, log a warning and continue to the next row
if not award:
logger.warn("Subcontract number {} cannot find matching award with piid {}, parent_award_id {}; skipping...".format(row['subcontract_num'], row['piid'], row['parent_award_id']))
continue
award_ids_to_update.add(award.id)
# Find the recipient by looking up by duns
recipient, created = LegalEntity.get_or_create_by_duns(duns=row['duns'])
if created:
recipient.parent_recipient_unique_id = row['parent_duns']
recipient.recipient_name = row['company_name']
recipient.location = get_or_create_location(row, location_d1_recipient_mapper)
recipient.save()
# Get or create POP
place_of_performance = get_or_create_location(row, pop_mapper)
d1_f_dict = {
'award': award,
'recipient': recipient,
'submission': submission_attributes,
'data_source': "DBR",
'cfda': None,
'awarding_agency': award.awarding_agency,
'funding_agency': award.funding_agency,
'place_of_performance': place_of_performance,
'subaward_number': row['subcontract_num'],
'amount': row['subcontract_amount'],
'description': row['overall_description'],
'recovery_model_question1': row['recovery_model_q1'],
'recovery_model_question2': row['recovery_model_q2'],
'action_date': row['subcontract_date'],
'award_report_fy_month': row['report_period_mon'],
'award_report_fy_year': row['report_period_year'],
'naics': row['naics'],
'naics_description': row['naics_description'],
}
# Create the subaward
subaward, created = Subaward.objects.update_or_create(subaward_number=row['subcontract_num'],
award=award,
defaults=d1_f_dict)
if created:
d1_create_count += 1
else:
d1_update_count += 1
# D2 File F
db_cursor.execute(D2_FILE_F_QUERY, [submission_attributes.broker_submission_id, tuple(fains)])
d2_f_data = dictfetchall(db_cursor)
logger.info("Creating D2 F File Entries (Subawards): {}".format(len(d2_f_data)))
d2_create_count = 0
d2_update_count = 0
d2_empty_count = 0
for row in d2_f_data:
if row['subaward_num'] is None:
if row['id'] is not None and row['subaward_amount'] is not None:
logger.warn("Subcontract of broker id {} has amount, but no number".format(row["id"]))
logger.warn("Failing row: {}".format(row))
else:
d2_empty_count += 1
continue
agency = get_valid_awarding_agency(row)
if not agency:
logger.warn("Subaward number {} cannot find matching agency with toptier code {} and subtier code {}".format(row['subaward_num'], row['awarding_agency_code'], row['awarding_sub_tier_agency_c']))
continue
# Find the award to attach this sub-award to
# We perform this lookup by finding the Award containing a transaction with
# a matching fain and submission. If this fails, try submission and uri
if row['fain'] and len(row['fain']) > 0:
award = Award.objects.filter(awarding_agency=agency,
transaction__submission=submission_attributes,
transaction__assistance_data__isnull=False,
transaction__assistance_data__fain=row['fain']).distinct().order_by("-date_signed").first()
# Couldn't find a match on FAIN, try URI if it exists
if not award and row['uri'] and len(row['uri']) > 0:
award = Award.objects.filter(awarding_agency=agency,
transaction__submission=submission_attributes,
transaction__assistance_data__isnull=False,
transaction__assistance_data__uri=row['uri']).distinct().first()
# We don't have a matching award for this subcontract, log a warning and continue to the next row
if not award:
logger.warn("Subaward number {} cannot find matching award with fain {}, uri {}; skipping...".format(row['subaward_num'], row['fain'], row['uri']))
continue
award_ids_to_update.add(award.id)
# Find the recipient by looking up by duns
recipient, created = LegalEntity.get_or_create_by_duns(duns=row['duns'])
if created:
recipient_name = row['awardee_name']
if recipient_name is None:
recipient_name = row['awardee_or_recipient_legal']
if recipient_name is None:
recipient_name = ""
recipient.recipient_name = recipient_name
recipient.parent_recipient_unique_id = row['parent_duns']
recipient.location = get_or_create_location(row, location_d2_recipient_mapper)
recipient.save()
# Get or create POP
place_of_performance = get_or_create_location(row, pop_mapper)
# Get CFDA Program
cfda = Cfda.objects.filter(program_number=row['cfda_number']).first()
d2_f_dict = {
'award': award,
'recipient': recipient,
'submission': submission_attributes,
'data_source': "DBR",
'cfda': cfda,
'awarding_agency': award.awarding_agency,
'funding_agency': award.funding_agency,
'place_of_performance': place_of_performance,
'subaward_number': row['subaward_num'],
'amount': row['subaward_amount'],
'description': row['project_description'],
'recovery_model_question1': row['compensation_q1'],
'recovery_model_question2': row['compensation_q2'],
'action_date': row['subaward_date'],
'award_report_fy_month': row['report_period_mon'],
'award_report_fy_year': row['report_period_year'],
'naics': None,
'naics_description': None,
}
# Create the subaward
subaward, created = Subaward.objects.update_or_create(subaward_number=row['subaward_num'],
award=award,
defaults=d2_f_dict)
if created:
d2_create_count += 1
else:
d2_update_count += 1
# Update Award objects with subaward aggregates
update_award_subawards(tuple(award_ids_to_update))
logger.info(
"""Submission {}
Subcontracts created: {}
Subcontracts updated: {}
Empty subcontract rows: {}
Subawards created: {}
Subawards updated: {}
Empty subaward rows: {}""".format(submission_attributes.broker_submission_id,
d1_create_count,
d1_update_count,
d1_empty_count,
d2_create_count,
d2_update_count,
d2_empty_count))
def load_subawards(submission_attributes, awards_touched, db_cursor):
"""
Loads File F from the broker. db_cursor should be the db_cursor for Broker
"""
# A list of award id's to update the subaward accounts and totals on
award_ids_to_update = set()
# Get a list of PIIDs from this submission
# TODO: URIS
awards_touched = [Award.objects.filter(id=award_id).first() for award_id in awards_touched]
piids = list([award.piid for award in awards_touched if award.piid])
fains = list([award.fain for award in awards_touched if award.fain])
uris = list([award.uri for award in awards_touched if award.uri])
# This allows us to handle an empty list in the SQL without changing the query
piids.append(None)
fains.append(None)
# D1 File F
db_cursor.execute(D1_FILE_F_QUERY, [submission_attributes.broker_submission_id, tuple(piids)])
d1_f_data = dictfetchall(db_cursor)
logger.info("Creating D1 F File Entries (Subcontracts): {}".format(len(d1_f_data)))
d1_create_count = 0
d1_update_count = 0
d1_empty_count = 0
for row in d1_f_data:
if row['subcontract_num'] is None:
if row['id'] is not None and row['subcontract_amount'] is not None:
logger.warn("Subcontract of broker id {} has amount, but no number".format(row["id"]))
logger.warn("Failing row: {}".format(row))
else:
d1_empty_count += 1
continue
# Get the agency
agency = get_valid_awarding_agency(row)
if not agency:
logger.warn(
"Subaward number {} cannot find matching agency with toptier code {} and subtier code {}".format(
row['subcontract_num'], row['awarding_agency_code'], row['awarding_sub_tier_agency_c']))
continue
# Find the award to attach this sub-contract to
# We perform this lookup by finding the Award containing a transaction with a matching parent award id, piid,
# and submission attributes
award = Award.objects.filter(
awarding_agency=agency,
latest_transaction__contract_data__piid=row['piid'],
latest_transaction__contract_data__parent_award_id=row['parent_award_id']).distinct().order_by(
"-date_signed").first()
# We don't have a matching award for this subcontract, log a warning and continue to the next row
if not award:
logger.warn(
"Subcontract number {} cannot find matching award with piid {}, parent_award_id {}; skipping...".format(
row['subcontract_num'], row['piid'], row['parent_award_id']))
continue
award_ids_to_update.add(award.id)
# Get or create unique DUNS-recipient pair
recipient, created = LegalEntity.objects.get_or_create(
recipient_unique_id=row['duns'],
recipient_name=row['company_name']
)
if created:
recipient.parent_recipient_unique_id = row['parent_duns']
recipient.location = get_or_create_location(row, location_d1_recipient_mapper)
recipient.save()
# Get or create POP
place_of_performance = get_or_create_location(row, pop_mapper)
d1_f_dict = {
'award': award,
'recipient': recipient,
'data_source': "DBR",
'cfda': None,
'awarding_agency': award.awarding_agency,
'funding_agency': award.funding_agency,
'place_of_performance': place_of_performance,
'subaward_number': row['subcontract_num'],
'amount': row['subcontract_amount'],
'description': row['overall_description'],
'recovery_model_question1': row['recovery_model_q1'],
'recovery_model_question2': row['recovery_model_q2'],
'action_date': row['subcontract_date'],
'award_report_fy_month': row['report_period_mon'],
'award_report_fy_year': row['report_period_year']
}
# Create the subaward
subaward, created = Subaward.objects.update_or_create(subaward_number=row['subcontract_num'],
award=award,
defaults=d1_f_dict)
if created:
d1_create_count += 1
else:
d1_update_count += 1
# D2 File F
db_cursor.execute(D2_FILE_F_QUERY, [tuple(fains), tuple(uris)])
d2_f_data = dictfetchall(db_cursor)
logger.info("Creating D2 F File Entries (Subawards): {}".format(len(d2_f_data)))
d2_create_count = 0
d2_update_count = 0
d2_empty_count = 0
for row in d2_f_data:
if row['subaward_num'] is None:
if row['id'] is not None and row['subaward_amount'] is not None:
logger.warn("Subcontract of broker id {} has amount, but no number".format(row["id"]))
logger.warn("Failing row: {}".format(row))
else:
d2_empty_count += 1
continue
agency = get_valid_awarding_agency(row)
if not agency:
logger.warn("Subaward number {} cannot find matching agency with toptier code {} and subtier "
"code {}".format(row['subaward_num'], row['awarding_agency_code'],
row['awarding_sub_tier_agency_c']))
continue
# Find the award to attach this sub-award to
# We perform this lookup by finding the Award containing a transaction with a matching fain and submission.
# If this fails, try submission and uri
if row['fain'] and len(row['fain']) > 0:
award = Award.objects.filter(awarding_agency=agency,
latest_transaction__assistance_data__fain=row['fain']).\
distinct().order_by("-date_signed").first()
# Couldn't find a match on FAIN, try URI if it exists
if not award and row['uri'] and len(row['uri']) > 0:
award = Award.objects.filter(awarding_agency=agency,
latest_transaction__assistance_data__uri=row['uri']).distinct().first()
# Try both
if not award and row['fain'] and len(row['fain']) > 0 and row['uri'] and len(row['uri']) > 0:
award = Award.objects.filter(awarding_agency=agency,
latest_transaction__assistance_data__fain=row['fain'],
latest_transaction__assistance_data__uri=row['uri']).\
distinct().order_by("-date_signed").first()
# We don't have a matching award for this subcontract, log a warning and continue to the next row
if not award:
logger.warn("Subaward number {} cannot find matching award with fain {}, uri {}; "
"skipping...".format(row['subaward_num'], row['fain'], row['uri']))
continue
award_ids_to_update.add(award.id)
recipient_name = row['awardee_name']
if recipient_name is None:
recipient_name = row['awardee_or_recipient_legal']
if recipient_name is None:
recipient_name = ""
# Get or create unique DUNS-recipient pair
recipient, created = LegalEntity.objects.get_or_create(
recipient_unique_id=row['duns'],
recipient_name=recipient_name
)
if created:
recipient.parent_recipient_unique_id = row['parent_duns']
recipient.location = get_or_create_location(row, location_d2_recipient_mapper)
recipient.save()
# Get or create POP
place_of_performance = get_or_create_location(row, pop_mapper)
# Get CFDA Program
cfda = Cfda.objects.filter(program_number=row['cfda_number']).first()
d2_f_dict = {
'award': award,
'recipient': recipient,
'data_source': "DBR",
'cfda': cfda,
'awarding_agency': award.awarding_agency,
'funding_agency': award.funding_agency,
'place_of_performance': place_of_performance,
'subaward_number': row['subaward_num'],
'amount': row['subaward_amount'],
'description': row['project_description'],
'recovery_model_question1': row['compensation_q1'],
'recovery_model_question2': row['compensation_q2'],
'action_date': row['subaward_date'],
'award_report_fy_month': row['report_period_mon'],
'award_report_fy_year': row['report_period_year']
}
# Create the subaward
subaward, created = Subaward.objects.update_or_create(
subaward_number=row['subaward_num'],
award=award,
defaults=d2_f_dict
)
if created:
d2_create_count += 1
else:
d2_update_count += 1
# Update Award objects with subaward aggregates
update_award_subawards(tuple(award_ids_to_update))
logger.info(
"""Submission {}
Subcontracts created: {}
Subcontracts updated: {}
Empty subcontract rows: {}
Subawards created: {}
Subawards updated: {}
Empty subaward rows: {}""".format(submission_attributes.broker_submission_id,
d1_create_count,
d1_update_count,
d1_empty_count,
d2_create_count,
d2_update_count,
d2_empty_count))