def update_c_to_d_linkages(type):
logger.info("Starting File C to D linkage updates for %s records" % type)
if type.lower() == "contract":
file_names = ["update_file_c_linkages_piid.sql"]
unlinked_count_file_name = "check_contract_file_c_linkages.sql"
elif type.lower() == "assistance":
file_names = [
"update_file_c_linkages_fain.sql",
"update_file_c_linkages_uri.sql",
"update_file_c_linkages_fain_and_uri.sql",
]
unlinked_count_file_name = "check_assistance_file_c_linkages.sql"
else:
raise InvalidParameterException("Invalid type provided to process C to D linkages.")
file_paths = [ETL_SQL_FILE_PATH + file_name for file_name in file_names]
starting_unlinked_count = get_unlinked_count(file_name=unlinked_count_file_name)
logger.info("Current count of unlinked %s records: %s" % (type, str(starting_unlinked_count)))
total_start = datetime.now()
for file_name in file_paths:
start = datetime.now()
logger.info("Running %s" % file_name)
sql_commands = read_sql_file(file_path=file_name)
for command in sql_commands:
with connection.cursor() as cursor:
cursor.execute(command)
logger.info("Finished %s in %s seconds" % (file_name, str(datetime.now() - start)))
ending_unlinked_count = get_unlinked_count(file_name=unlinked_count_file_name)
logger.info("Count of unlinked %s records after updates: %s" % (type, str(ending_unlinked_count)))
logger.info("Finished all queries in %s seconds" % str(datetime.now() - total_start))
def update_c_to_d_linkages(type):
logger.info('Starting File C to D linkage updates for %s records' % type)
if type.lower() == 'contract':
file_names = ['update_file_c_linkages_piid.sql']
unlinked_count_file_name = 'check_contract_file_c_linkages.sql'
elif type.lower() == 'assistance':
file_names = ['update_file_c_linkages_fain.sql', 'update_file_c_linkages_uri.sql',
'update_file_c_linkages_fain_and_uri.sql']
unlinked_count_file_name = 'check_assistance_file_c_linkages.sql'
else:
raise InvalidParameterException('Invalid type provided to process C to D linkages.')
file_paths = [ETL_SQL_FILE_PATH + file_name for file_name in file_names]
starting_unlinked_count = get_unlinked_count(file_name=unlinked_count_file_name)
logger.info('Current count of unlinked %s records: %s' % (type, str(starting_unlinked_count)))
total_start = datetime.now()
for file_name in file_paths:
start = datetime.now()
logger.info('Running %s' % file_name)
sql_commands = read_sql_file(file_path=file_name)
for command in sql_commands:
with connection.cursor() as cursor:
cursor.execute(command)
logger.info('Finished %s in %s seconds' % (file_name, str(datetime.now() - start)))
ending_unlinked_count = get_unlinked_count(file_name=unlinked_count_file_name)
logger.info('Count of unlinked %s records after updates: %s' % (type, str(ending_unlinked_count)))
logger.info('Finished all queries in %s seconds' % str(datetime.now() - total_start))
def get_unlinked_count(file_name):
file_path = ETL_SQL_FILE_PATH + file_name
sql_commands = read_sql_file(file_path=file_path)
if len(sql_commands) != 1:
raise InvalidParameterException('Invalid number of commands in specified file. File should contain 1 SQL '
'command to get the count of unlinked records.')
with connection.cursor() as cursor:
cursor.execute(sql_commands[0])
result = cursor.fetchall()[0][0]
return int(result)
def get_unlinked_count(file_name):
file_path = str(_ETL_SQL_FILE_PATH / file_name)
sql_commands = read_sql_file(file_path=file_path)
if len(sql_commands) != 1:
raise InvalidParameterException(
"Invalid number of commands in specified file. File should contain 1 SQL "
"command to get the count of unlinked records.")
with connection.cursor() as cursor:
cursor.execute(sql_commands[0])
result = cursor.fetchall()[0][0]
return int(result)
def update_c_to_d_linkages(type, count=True, submission_id=None):
logger.info("Starting File C to D linkage updates for %s records" % type)
if type.lower() == "contract":
file_names = ["update_file_c_linkages_piid.sql"]
unlinked_count_file_name = "check_contract_file_c_linkages.sql"
elif type.lower() == "assistance":
file_names = [
"update_file_c_linkages_fain.sql",
"update_file_c_linkages_uri.sql",
"update_file_c_linkages_fain_and_uri.sql",
]
unlinked_count_file_name = "check_assistance_file_c_linkages.sql"
else:
raise InvalidParameterException(
"Invalid type provided to process C to D linkages.")
if count:
starting_unlinked_count = get_unlinked_count(
file_name=unlinked_count_file_name)
logger.info("Current count of unlinked %s records: %s" %
(type, str(starting_unlinked_count)))
total_start = datetime.now()
for file_name in file_names:
start = datetime.now()
logger.info(f"Running {file_name}")
sql_commands = read_sql_file(file_path=str(_ETL_SQL_FILE_PATH /
file_name))
for command in sql_commands:
submission_id_clause = f"and faba_sub.submission_id = {submission_id}" if submission_id else ""
command = command.format(submission_id_clause=submission_id_clause)
with connection.cursor() as cursor:
cursor.execute(command)
logger.info(
f"Finished {file_name} in {str(datetime.now() - start)} seconds")
if count:
ending_unlinked_count = get_unlinked_count(
file_name=unlinked_count_file_name)
logger.info("Count of unlinked %s records after updates: %s" %
(type, str(ending_unlinked_count)))
logger.info("Finished all queries in %s seconds" %
str(datetime.now() - total_start))