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))