def search_regex_of(v):
if isinstance(v, str):
v = string_to_dictionary(v, "aid")
code_lookup = {
"ata": v["ata"] if v.get("ata") else None,
"aid": v["aid"] if v.get("aid") else ".*",
"main": v["main"] if v.get("main") else ".*",
"sub": v["sub"] if v.get("sub") else ".*",
"bpoa": v["bpoa"] if v.get("bpoa") else ".*",
"epoa": v["epoa"] if v.get("epoa") else ".*",
"a": v["a"] if v.get("a") else ".*",
}
# This is NOT the order of elements as displayed in the tas rendering label, but instead the order in the award_delta_view and transaction_delta_view
search_regex = TreasuryAppropriationAccount.generate_tas_rendering_label(
code_lookup["ata"],
code_lookup["aid"],
code_lookup["a"],
code_lookup["bpoa"],
code_lookup["epoa"],
code_lookup["main"],
code_lookup["sub"],
)
# TODO: move this to a Tinyshield filter
if not re.match(r"^(\d|\w|-|\*|\.)+$", search_regex):
raise UnprocessableEntityException(
f"Unable to parse TAS filter {search_regex}")
return search_regex
def get_treasury_appropriation_account_tas_lookup(tas_lookup_id, db_cursor):
"""Get the matching TAS object from the broker database and save it to our running list."""
if tas_lookup_id in TAS_ID_TO_ACCOUNT:
return TAS_ID_TO_ACCOUNT[tas_lookup_id]
# Checks the broker DB tas_lookup table for the tas_id and returns the matching TAS object in the datastore
db_cursor.execute(
"SELECT * FROM tas_lookup WHERE (financial_indicator2 <> 'F' OR financial_indicator2 IS NULL) "
"AND account_num = %s",
[tas_lookup_id],
)
tas_data = dictfetchall(db_cursor)
if tas_data is None or len(tas_data) == 0:
return None, "Account number {} not found in Broker".format(
tas_lookup_id)
tas_rendering_label = TreasuryAppropriationAccount.generate_tas_rendering_label(
ata=tas_data[0]["allocation_transfer_agency"],
aid=tas_data[0]["agency_identifier"],
typecode=tas_data[0]["availability_type_code"],
bpoa=tas_data[0]["beginning_period_of_availa"],
epoa=tas_data[0]["ending_period_of_availabil"],
mac=tas_data[0]["main_account_code"],
sub=tas_data[0]["sub_account_code"],
)
TAS_ID_TO_ACCOUNT[tas_lookup_id] = (
TreasuryAppropriationAccount.objects.filter(
tas_rendering_label=tas_rendering_label).first(),
tas_rendering_label,
)
return TAS_ID_TO_ACCOUNT[tas_lookup_id]
def get_treasury_appropriation_account_tas_lookup(tas_lookup_id, db_cursor):
"""Get the matching TAS object from the broker database and save it to our running list."""
if tas_lookup_id in TAS_ID_TO_ACCOUNT:
return TAS_ID_TO_ACCOUNT[tas_lookup_id]
# Checks the broker DB tas_lookup table for the tas_id and returns the matching TAS object in the datastore
db_cursor.execute("SELECT * FROM tas_lookup WHERE (financial_indicator2 <> 'F' OR financial_indicator2 IS NULL) "
"AND account_num = %s", [tas_lookup_id])
tas_data = dictfetchall(db_cursor)
if tas_data is None or len(tas_data) == 0:
return None, 'Account number {} not found in Broker'.format(tas_lookup_id)
tas_rendering_label = TreasuryAppropriationAccount.generate_tas_rendering_label(
ata=tas_data[0]["allocation_transfer_agency"],
aid=tas_data[0]["agency_identifier"],
typecode=tas_data[0]["availability_type_code"],
bpoa=tas_data[0]["beginning_period_of_availa"],
epoa=tas_data[0]["ending_period_of_availabil"],
mac=tas_data[0]["main_account_code"],
sub=tas_data[0]["sub_account_code"]
)
TAS_ID_TO_ACCOUNT[tas_lookup_id] = (TreasuryAppropriationAccount.objects.
filter(tas_rendering_label=tas_rendering_label).first(), tas_rendering_label)
return TAS_ID_TO_ACCOUNT[tas_lookup_id]
def test_availability_type_code_tas():
assert TreasuryAppropriationAccount.tas_rendering_label_to_component_dictionary(
"000-X-0400-080") == {
"aid": "000",
"a": "X",
"main": "0400",
"sub": "080",
}
def string_to_dictionary(string, true_agency_name):
if len(string.split("-")) == 1:
return {true_agency_name: string}
elif len(string.split("-")) == 2:
return FederalAccount.fa_rendering_label_to_component_dictionary(
string)
else:
return TreasuryAppropriationAccount.tas_rendering_label_to_component_dictionary(
string)
def bulk_treasury_appropriation_account_tas_lookup(rows, db_cursor):
# Eliminate nulls, TAS we already know about, and remove duplicates.
tas_lookup_ids = tuple(
set(r["tas_id"] for r in rows
if (r["tas_id"] and r["tas_id"] not in TAS_ID_TO_ACCOUNT)))
if not tas_lookup_ids:
return
db_cursor.execute(
"""
select distinct
account_num,
allocation_transfer_agency,
agency_identifier,
availability_type_code,
beginning_period_of_availa,
ending_period_of_availabil,
main_account_code,
sub_account_code
from tas_lookup
where account_num in %s
and (
financial_indicator2 != 'F'
or financial_indicator2 is null
)
""",
[tas_lookup_ids],
)
tas_data = dictfetchall(db_cursor)
tas_rendering_labels = {
tas["account_num"]:
TreasuryAppropriationAccount.generate_tas_rendering_label(
ata=tas["allocation_transfer_agency"],
aid=tas["agency_identifier"],
typecode=tas["availability_type_code"],
bpoa=tas["beginning_period_of_availa"],
epoa=tas["ending_period_of_availabil"],
mac=tas["main_account_code"],
sub=tas["sub_account_code"],
)
for tas in tas_data
}
taa_objects = {
taa.tas_rendering_label: taa
for taa in TreasuryAppropriationAccount.objects.filter(
tas_rendering_label__in=tas_rendering_labels.values())
}
TAS_ID_TO_ACCOUNT.update({
tid: (taa_objects.get(tas_rendering_labels.get(tid)),
tas_rendering_labels.get(tid))
for tid in tas_lookup_ids
})
def test_no_ata_tas():
assert TreasuryAppropriationAccount.tas_rendering_label_to_component_dictionary(
"000-2010/2011-0400-080") == {
"aid": "000",
"bpoa": "2010",
"epoa": "2011",
"main": "0400",
"sub": "080",
}
def csv_tas_loader(self, file_path):
field_map = {
"treasury_account_identifier": "ACCT_NUM",
"allocation_transfer_agency_id": "ATA",
"agency_id": "AID",
"beginning_period_of_availability": "BPOA",
"ending_period_of_availability": "EPOA",
"availability_type_code": "A",
"main_account_code": "MAIN",
"sub_account_code": "SUB",
"account_title": "GWA_TAS_NAME",
"reporting_agency_id": "Agency AID",
"reporting_agency_name": "Agency Name",
"budget_bureau_code": "ADMIN_ORG",
"budget_bureau_name": "Admin Org Name",
"fr_entity_code": "FR Entity Type",
"fr_entity_description": "FR Entity Description",
"budget_function_code": "Function Code",
"budget_function_title": "Function Description",
"budget_subfunction_code": "Sub Function Code",
"budget_subfunction_title": "Sub Function Description",
}
value_map = {
"data_source": "USA",
"tas_rendering_label": self.generate_tas_rendering_label,
"awarding_toptier_agency": None,
"funding_toptier_agency": None,
"internal_start_date": lambda row: datetime.strftime(
datetime.strptime(row["DT_TM_ESTAB"], "%m/%d/%Y %H:%M:%S"), "%Y-%m-%d"
),
"internal_end_date": lambda row: datetime.strftime(
datetime.strptime(row["DT_END"], "%m/%d/%Y %H:%M:%S"), "%Y-%m-%d"
)
if row["DT_END"]
else None,
}
with RetrieveFileFromUri(file_path).get_file_object(True) as tas_list_file_object:
# Get a total count for print out
tas_list_reader = csv.DictReader(tas_list_file_object)
total_count = len(list(tas_list_reader))
# Reset the reader back to the beginning of the file
tas_list_file_object.seek(0)
tas_list_reader = csv.DictReader(tas_list_file_object)
for count, row in enumerate(tas_list_reader, 1):
for key, value in row.items():
row[key] = value.strip() or None
# Check to see if we need to update or create a TreasuryAppropriationAccount record
current_record = TreasuryAppropriationAccount.objects.filter(
treasury_account_identifier=row["ACCT_NUM"]
).first()
taa_instance = current_record or TreasuryAppropriationAccount()
# Don't load Financing TAS
if row["financial_indicator_type2"] == "F":
if taa_instance.treasury_account_identifier:
taa_instance.delete()
logger.info(" Row contains Financing TAS, Skipping...")
continue
load_data_into_model(taa_instance, row, field_map=field_map, value_map=value_map, save=True)
if count % 1000 == 0:
logger.info(" Loaded {} rows of {}".format(count, total_count))
def generate_tas_rendering_label(self, row):
return TreasuryAppropriationAccount.generate_tas_rendering_label(
row["ATA"], row["Agency AID"], row["A"], row["BPOA"], row["EPOA"], row["MAIN"], row["SUB"]
)
# ensures that tests aren't failing for having the wrong TAS. We trust functionality of tas_rendering_label_to_component_dictionary because it is tested elsewhere
BASIC_TAS = 0
ATA_TAS = 1
BPOA_TAS = 2
ATA_BPOA_TAS = 3
SISTER_TAS = [1, 4, 5]
TAS_STRINGS = [
"000-X-0126-000",
"010-024-X-8445-002",
"012-2000/2000-1231-000",
"020-012-2000/2000-1231-000",
"010-024-X-8445-552",
"010-024-X-8445-578",
]
TAS_DICTIONARIES = [
TreasuryAppropriationAccount.tas_rendering_label_to_component_dictionary(
tas) for tas in TAS_STRINGS
]
@pytest.fixture
def award_with_tas(db):
award(db, 1)
tas_with_agency(db, 1, BASIC_TAS)
@pytest.fixture
def award_with_bpoa_tas(db):
award(db, 1)
tas_with_agency(db, 1, BPOA_TAS)
def test_unparsible_tas():
with pytest.raises(UnprocessableEntityException):
TreasuryAppropriationAccount.tas_rendering_label_to_component_dictionary(
"badinput")
