def update_application_status(app_id):
try:
data = []
email_events = []
path = ""
params = request.json
if params["status"] == 'Approved':
user_app = params.get('application')
user_app["comment"] = params["user_comment"]
path = os.path.join(app.config['SCRIPT_FOLDER'],"approve_application.sql")
email_events.append("Application Approved")
elif params["status"] == 'Denied':
user_app = params.get('application')
user_app["comment"] = params["user_comment"]
path = os.path.join(app.config['SCRIPT_FOLDER'],"deny_application.sql")
email_events.append("Application Denied")
elif params["status"] == 'Pending':
del params['user_comment'] #remove because this field is not needed in override script
path = os.path.join(app.config['SCRIPT_FOLDER'],"override_application_status.sql")
else:
raise Exception("Invalid Status only ['Pending','Approved','Denied'] permitted")
del params['application'] #remove application because oracle cannot not parse dictionary data
sql = open(path,"r")
user = get_jwt_identity()
user = user['user_name']
params.update([("user_name",user),("app_id",app_id)])
with cx_Oracle.connect(f"{oraDB.user_name}/{oraDB.password}@{oraDB.db}") as conn:
with conn.cursor() as cursor:
cursor.execute(sql.read(),params)
conn.commit()
sql.close()
if params['status'] not in ['Pending']:
send_mail(email_events,user_app)
return jsonify(success="Y",data=data),200
except Exception as e:
return jsonify(success="N",message=f"System Error: {str(e)}"),500
def send_notice_to_all_paid_applicants():
try:
print("Job Running...")
email_events = []
email_events.append("Payment Generated")
scripts_folder_path = os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),"scripts")
with cx_Oracle.connect(f"{oraDB.user_name}/{oraDB.password}@{oraDB.db}") as conn:
with conn.cursor() as cursor:
path = os.path.join(scripts_folder_path ,"get_paid_applications_without_payment_notifications.sql")
sql = open(path,"r")
results = cursor.execute(sql.read())
while True:
rows = results.fetchall()
if not rows:
break
for r in rows:
application = {"first_name":r[0],"beg_pay_period":r[1],"end_pay_period":r[2],
"num_weeks_paid":r[3],"max_weeks":r[4],"pmt_type":r[5],
"branch_name":r[6],"bank_account_number":r[7],"pmt_id":r[8],
"email":r[9],"tourism_industry":r[10]}
send_mail(email_events,application)
#update payment info
with conn.cursor() as update_cursor:
params = {"pmt_id":application["pmt_id"]}
script_path = os.path.join(scripts_folder_path,"update_payment_notice.sql")
update_sql = open(script_path,"r")
update_cursor.execute(update_sql.read(),params)
conn.commit()
update_sql.close()
except Exception as e:
print(str(e))
email_events = []
error_message = str(e)
email_events.append("Payment Notice Error")
send_mail(email_events,error_message)
def upload_file(app_id):
try:
email_events = []
files = request.files.getlist('file')
errors = {}
success = False
for file in files:
print(file)
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
if not os.path.exists(os.path.join(app.config['UPLOAD_FOLDER'],app_id)):
os.mkdir(os.path.join(app.config['UPLOAD_FOLDER'],app_id))
if not os.path.exists(os.path.join(f"{app.config['UPLOAD_FOLDER']}\\{app_id}\\", filename)):
file.save(os.path.join(f"{app.config['UPLOAD_FOLDER']}\\{app_id}\\", filename))
success = True
else:
success = True
else:
errors[file.filename] = 'File type is not allowed'
if success and errors:
errors['message'] = 'File(s) successfully uploaded'
resp = jsonify(errors)
resp.status_code = 500
return resp
if success:
resp = jsonify({'message' : 'Files successfully uploaded'})
resp.status_code = 201
user = None
path = os.path.join(app.config['SCRIPT_FOLDER'],"get_individual_applications.sql")
sql = open(path,"r")
params = {"app_id":app_id}
with cx_Oracle.connect(f"{oraDB.user_name}/{oraDB.password}@{oraDB.db}") as conn:
with conn.cursor() as cursor:
results = cursor.execute(sql.read(),params)
while True:
rows = results.fetchall()
if not rows:
break
for r in rows:
user = {"application_id":r[0],"first_name":r[1],"last_name":r[2],
"dob":r[3],"eeni":r[4],"erni":r[5],
"email":r[6],"primary_contact":r[7],"secondary_contact":r[8],
"place_of_operation":r[9],"island_of_operation":r[10],
"estimated_weekly_earnings":r[11],"status":r[13],"approval_date":r[14],
"inserted_by":r[15],
"inserted_date":r[16],"updated_by":r[17],"updated_date":r[18],
"approved_by":r[19],"denied_by":r[20],"comment":r[21],
"denial_date":r[22],"nature_of_employment":r[23],
"url":f"/Self-Employed-UEB/applications/{r[0]}"}
sql.close()
if len(str(user["eeni"])) == 0:
email_events.append("Employee Registration")
if len(str(user["erni"])) == 0:
email_events.append("Employer Registration")
email_events.append("Application Submitted")
send_mail(email_events,user)
return resp
else:
resp = jsonify(errors)
resp.status_code = 500
return resp
except FileExistsError as e:
return jsonify(success="N",message=f"File already exists"),500
except Exception as e:
return jsonify(success="N",message=f"System Error: {str(e)}"),500
def execute_check_run(user_name):
try:
log.info("Initiating Check Run")
#Execute Check Run DB Process (Generate Payments in Pending Status)
checkrun = LumpSumCheckRun()
checkrun.generateLumpSumPayments()
# conn = cx_Oracle.connect(f"{oraDB.user_name}/{oraDB.password}@{oraDB.db}")
# cursor = conn.cursor()
# success = cursor.var(cx_Oracle.STRING,1) if not None else ''
# message = cursor.var(cx_Oracle.STRING,250) if not None else ''
# cursor.callproc("client.create_se_ueb_checks",[user_name,success,message])
# if success.getvalue() == "N":
# raise Exception(f"Error Generating Payments: {message.getvalue()}")
log.info("Pending Payments Generated")
data = []
email_events = []
scripts_path = os.path.join(
os.path.dirname(os.path.dirname(os.path.realpath(__file__))),
"scripts")
file_header = None
batch_header = None
count = 0
block_count = 0
entry_hash = 0
total_credit = 0
EFT_FILES_FOLDER = 'C:\\Check_Run\\EFT'
MANUAL_CHECK_FOLDER = 'C:\\Check_Run\\Manual Check'
SUN_CASH_FOLDER = 'C:\\Check_Run\\Sun Cash'
with cx_Oracle.connect(
f"{oraDB.user_name}/{oraDB.password}@{oraDB.db}") as conn:
with conn.cursor() as cursor:
log.info("Executing Proc to create batch payment history")
#write payments for check run to batch payment history
success = cursor.var(cx_Oracle.STRING, 1) if not None else ''
message = cursor.var(cx_Oracle.STRING, 250) if not None else ''
cursor.callproc("client.create_batch_pmt_history",
[user_name, success, message])
if success.getvalue() == "N":
raise Exception(
f"Error Writing Payment Batch History: {message.getvalue()}"
)
log.info("Batch Payment History Created")
log.info("Generating EFT payment file")
#process EFT Files
path = os.path.join(scripts_path,
"get_eft_pending_payments.sql")
sql = open(path, "r")
results = cursor.execute(sql.read())
while True:
rows = results.fetchall()
if not rows:
break
#Write EFT File(Nacha File Format)
file_name = f"SE_UEB_EFT_{datetime.today().strftime('%Y-%m-%d-%I%M')}.txt"
file_path = os.path.join(EFT_FILES_FOLDER, file_name)
file_header = f"101 0056250030000000000{datetime.today().strftime('%y%m%d')}{datetime.today().strftime('%I%M')}1094101Royal Bank of Canada"\
" National Insurance "
write_eft_file(file_path, file_header)
batch_header = f"5220NIB Long Term Benefits 0002197192PPDClaims {datetime.today().strftime('%y%m%d')}{datetime.today().strftime('%y%m%d')}"\
"0001056250030000001"
write_eft_file(file_path, batch_header)
count = len(rows)
if (count + 4) % 10 == 0:
block_count = int((count + 4) / 10)
else:
block_count = int((count + 4) / 10) + 1
for r in rows:
pmt_record = f"{r[0]}{r[1]}{r[2].rjust(8,'0')}{r[3]}{r[4]:<17}{r[5]:010}{r[6]:<15}{r[7]:<22}{r[8]}{r[9]:06}"
write_eft_file(file_path, pmt_record)
entry_hash += int(r[2])
total_credit += r[5]
entry_hash = str(
entry_hash
)[-10:] #entry has = last 10 digits of the sum of all routing numbers
batch_control = f"8220{count:06}{entry_hash:0>10}000000000000{total_credit:012}0000000000"\
" "\
"056250030000001"
write_eft_file(file_path, batch_control)
file_control = f"9000001{block_count:06}{count:08}{entry_hash:0>10}000000000000{total_credit:012}"
write_eft_file(file_path, file_control)
end_of_file = "9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999"
write_eft_file(file_path, end_of_file)
sql.close()
log.info(f"EFT Payment File Written Successfully")
log.info(f"Generating Manual Check File")
#Write Manual Check Payments
file_name = f"SE_UEB_MANUAL_{datetime.today().strftime('%Y-%m-%d-%I%M')}.csv"
file_path = os.path.join(MANUAL_CHECK_FOLDER, file_name)
path = os.path.join(
scripts_path, "get_pending_reissued_manual_payments.sql")
sql = open(path, "r")
results = cursor.execute(sql.read())
while True:
rows = results.fetchall()
if not rows:
break
for r in rows:
write_CSV_file(file_path, r)
sql.close()
log.info(f"Manual Check File Written Successfully")
log.info(f"Generating Sun Cash Payment File")
#Write Sun Cash Payments
file_name = f"SE_UEB_SUN_CASH_{datetime.today().strftime('%Y-%m-%d-%I%M')}.csv"
file_path = os.path.join(SUN_CASH_FOLDER, file_name)
path = os.path.join(
scripts_path, "get_pending_reissued_sun_cash_payments.sql")
sql = open(path, "r")
results = cursor.execute(sql.read())
while True:
rows = results.fetchall()
if not rows:
break
for r in rows:
write_CSV_file(file_path, r)
sql.close()
log.info(f"Sun Cash Payment File Written Successfully")
log.info(f"Updating all Pending/Reissued Payments to Paid")
#update all pending payments to paid
script_path = os.path.join(
scripts_path, "mark_pending_reissued_pmts_paid.sql")
update_sql = open(script_path, "r")
cursor.execute(update_sql.read())
conn.commit()
update_sql.close()
#notify users
log.info(f"All Pending/Reissued Payments marked as Paid")
log.info(f"Sending Successful Email...")
email_events.append("Check Run Completed")
send_mail(email_events, None)
log.info(f"Check Run Success Email Sent")
except Exception as e:
error_message = str(e)
log.error(f"Check Run Aborted: {error_message}")
email_events = []
email_events.append("Check Run Aborted")
send_mail(email_events, error_message)
def test_email():
email_events = []
user_app = "test"
email_events.append("Check Run Aborted")
send_mail(email_events,user_app)
return jsonify("Test Email Send Successfully"), 200