def handle_loading(self, db_cursor, *args, **options):
submission_attributes = SubmissionAttributes()
submission_attributes.usaspending_update = timezone.now()
submission_attributes.save()
if not options['contracts'] and not options['financial_assistance']:
raise CommandError(
'Must specify either --contracts, --financial_assistance, or both'
)
if options['contracts']:
procurement_data = self.broker_data(db_cursor,
'detached_award_procurement',
options)
load_base.load_file_d1(submission_attributes, procurement_data,
db_cursor)
if options['financial_assistance']:
assistance_data = self.broker_data(
db_cursor, 'published_award_financial_assistance', options)
load_base.load_file_d2(
submission_attributes,
assistance_data,
db_cursor,
row_preprocessor=preprocess_historical_d2_row)
def get(self, request, pk, fy=0, format=None):
fy = int(fy) or SubmissionAttributes.latest_available_fy()
queryset = AppropriationAccountBalances.objects.filter(
submission__is_final_balances_for_fy=True,
treasury_account_identifier__federal_account_id=int(pk)).filter(
submission__reporting_fiscal_year=fy)
queryset = queryset.aggregate(
outlay=Sum("gross_outlay_amount_by_tas_cpe"),
budget_authority=Sum("total_budgetary_resources_amount_cpe"),
obligated=Sum("obligations_incurred_total_by_tas_cpe"),
unobligated=Sum("unobligated_balance_cpe"),
balance_brought_forward=Sum(
F("budget_authority_unobligated_balance_brought_forward_fyb") +
F("adjustments_to_unobligated_balance_brought_forward_cpe")),
other_budgetary_resources=Sum(
"other_budgetary_resources_amount_cpe"),
appropriations=Sum("budget_authority_appropriated_amount_cpe"),
)
if queryset["outlay"] is not None:
name = FederalAccount.objects.filter(
id=int(pk)).values("account_title").first()["account_title"]
queryset["name"] = name
return Response({"results": queryset})
else:
return Response({})
def _parse_and_validate_request(self, request_dict):
""" Validate the Request object includes the required fields """
fy_range = [str(i) for i in range(2001, FiscalDateTime.today().year + 1)]
last_fy = str(SubmissionAttributes.last_certified_fy()) or str(FiscalDateTime.today().year)
request_settings = [
{
"key": "sort",
"name": "sort",
"type": "object",
"optional": True,
"object_keys": {
"field": {
"type": "enum",
"enum_values": ["budgetary_resources", "managing_agency", "account_name", "account_number"],
"optional": True,
"default": "budgetary_resources",
},
"direction": {"type": "enum", "enum_values": ["asc", "desc"], "optional": True, "default": "asc"},
},
"default": {"field": "budgetary_resources", "direction": "asc"},
},
{"key": "page", "name": "page", "type": "integer", "default": 1, "min": 1, "optional": True},
{"key": "limit", "name": "limit", "type": "integer", "default": 10, "min": 1, "max": 100, "optional": True},
{
"key": "filters",
"name": "filters",
"type": "object",
"optional": True,
"object_keys": {
"agency_identifier": {
"name": "agency_identifier",
"type": "text",
"text_type": "search",
"optional": True,
},
"fy": {"type": "enum", "enum_values": fy_range, "optional": True, "default": last_fy},
},
"default": {"fy": last_fy},
},
{"key": "keyword", "name": "keyword", "type": "text", "text_type": "search", "optional": True},
]
validated_request_data = TinyShield(request_settings).block(request_dict)
validated_request_data["filters"]["fy"] = validated_request_data["filters"].get("fy", last_fy)
return validated_request_data
def post(self, request, format=None):
"""Return all high-level Federal Account information"""
limit = request.data.get("limit", 10)
page = request.data.get("page", 1)
sorting = request.data.get("sort", {'field': 'budgetary_resources', 'direction': 'desc'})
sort_field = sorting.get('field', 'budgetary_resources')
sort_direction = sorting.get('direction', 'asc')
filters = request.data.get('filters', {})
fy = filters.get('fy') or SubmissionAttributes.last_certified_fy()
lower_limit = (page - 1) * limit
upper_limit = page * limit
agency_subquery = ToptierAgency.objects.filter(cgac_code=OuterRef('corrected_agency_identifier'))
queryset = FederalAccount.objects.\
filter(treasuryappropriationaccount__account_balances__final_of_fy=True,
treasuryappropriationaccount__account_balances__submission__reporting_period_start__fy=fy).\
annotate(corrected_agency_identifier=Func(F('agency_identifier'), function='CORRECTED_CGAC')).\
annotate(account_id=F('id'),
account_number=Concat(F('agency_identifier'), Value('-'), F('main_account_code')),
account_name=F('account_title'),
budgetary_resources=Sum(
'treasuryappropriationaccount__account_balances__budget_authority_available_amount_total_cpe'),
managing_agency=Subquery(agency_subquery.values('name')[:1]),
managing_agency_acronym=Subquery(agency_subquery.values('abbreviation')[:1])
)
if sort_direction == 'desc':
queryset = queryset.order_by(F(sort_field).desc(nulls_last=True))
else:
queryset = queryset.order_by(F(sort_field).asc())
result = {'count': queryset.count(), 'limit': limit, 'page': page, 'fy': fy}
resultset = queryset.values('account_id', 'account_number', 'account_name', 'budgetary_resources',
'agency_identifier', 'managing_agency', 'managing_agency_acronym')
resultset = resultset[lower_limit:upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit, page)
result.update(page_metadata)
resultset = resultset[:limit]
result['results'] = resultset
return Response(result)
def get(self, request, pk, fy=0, format=None):
fy = int(fy) or SubmissionAttributes.last_certified_fy()
queryset = AppropriationAccountBalances.final_objects.filter(
treasury_account_identifier__federal_account_id=int(pk)).filter(submission__reporting_fiscal_year=fy)
queryset = queryset.aggregate(
outlay=Sum('gross_outlay_amount_by_tas_cpe'),
budget_authority=Sum('total_budgetary_resources_amount_cpe'),
obligated=Sum('obligations_incurred_total_by_tas_cpe'),
unobligated=Sum('unobligated_balance_cpe'),
balance_brought_forward=Sum(
F('budget_authority_unobligated_balance_brought_forward_fyb') +
F('adjustments_to_unobligated_balance_brought_forward_cpe')),
other_budgetary_resources=Sum('other_budgetary_resources_amount_cpe'),
appropriations=Sum('budget_authority_appropriated_amount_cpe'))
if queryset['outlay'] is not None:
name = FederalAccount.objects.filter(id=int(pk)).values('account_title').first()['account_title']
queryset['name'] = name
return Response({'results': queryset})
else:
return Response({})
def _parse_and_validate_request(self, request_dict):
""" Validate the Request object includes the required fields """
fy_range = [str(i) for i in range(2001, FiscalDateTime.today().year + 1)]
last_fy = str(SubmissionAttributes.last_certified_fy()) or str(FiscalDateTime.today().year)
request_settings = [
{'key': 'sort', 'name': 'sort', 'type': 'object', 'optional': True, 'object_keys': {
'field': {'type': 'enum', 'enum_values': ['budgetary_resources', 'managing_agency', 'account_name',
'account_number'],
'optional': True, 'default': 'budgetary_resources'},
'direction': {'type': 'enum', 'enum_values': ['asc', 'desc'], 'optional': True, 'default': 'asc'},
}, 'default': {'field': 'budgetary_resources', 'direction': 'asc'}},
{'key': 'page', 'name': 'page', 'type': 'integer', 'default': 1, 'min': 1, 'optional': True},
{'key': 'limit', 'name': 'limit', 'type': 'integer', 'default': 10, 'min': 1, 'max': 100, 'optional': True},
{'key': 'filters', 'name': 'filters', 'type': 'object', 'optional': True, 'object_keys': {
'fy': {'type': 'enum', 'enum_values': fy_range, 'optional': True, 'default': last_fy},
}, 'default': {'fy': last_fy}},
{'key': 'keyword', 'name': 'keyword', 'type': 'text', 'text_type': 'search', 'optional': True}
]
validated_request_data = TinyShield(request_settings).block(request_dict)
return validated_request_data
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 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))
