def Review():
data = sql_pull(
f'''SELECT * FROM DATA_ENTRY WHERE ENTRY_ID = "{session['ENTRY_ID']}"'''
)[0]
status = sql_single_field(
f'''WITH MAX_DT_TBL AS (SELECT ENTRY_ID, MAX(INSERT_DTTM) AS MAX_DT FROM APP_STATUS_TBL WHERE ENTRY_ID = "{session['ENTRY_ID']}")
SELECT STATUS FROM APP_STATUS_TBL AST LEFT JOIN MAX_DT_TBL MDT ON AST.ENTRY_ID=MDT.ENTRY_ID WHERE AST.ENTRY_ID = "{session['ENTRY_ID']}" AND INSERT_DTTM=MAX_DT'''
)
#too lazy to not do select *, so I'm reformatting dates in python instead
data['ENTRY_DATE'] = datetime.datetime.strptime(
data['ENTRY_DATE'], '%Y-%m-%d').strftime('%m/%d/%Y')
if pandas.notnull(data['DOB_PRI']) and data['DOB_PRI'] != "":
data['DOB_PRI'] = datetime.datetime.strptime(
data['DOB_PRI'], '%Y-%m-%d').strftime('%m/%d/%Y')
if pandas.notnull(data['DOB_SEC']) and data['DOB_SEC'] != '':
data['DOB_SEC'] = datetime.datetime.strptime(
data['DOB_SEC'], '%Y-%m-%d').strftime('%m/%d/%Y')
form = ReviewForm()
error = None
if form.validate_on_submit():
if data['NAME_LAST_PRI'] == '':
error = '''Applicant information isn't filled out'''
elif status not in ['Upload Error - Needs Review', 'Needs Review']:
error = '''Cannot Edit due to Status'''
else:
sql_edit(
f'''INSERT INTO APP_STATUS_TBL(ENTRY_ID, INSERT_DTTM, STATUS, INSERT_USER) VALUES("{session['ENTRY_ID']}", datetime("now", "localtime"), "Reviewed", "{session['user']}")'''
)
return redirect(url_for('application.home'))
return render_template('app_review.html',
status=status,
data=data,
form=form,
error=error)
def users():
data = sql_pull(
'''SELECT NAME_FIRST || " " || NAME_LAST AS FULL_NAME, EMAIL, RIGHTS, INSERT_DTTM FROM USERS'''
)
form = AddUser()
error = None
if form.validate_on_submit():
usercheck = sql_pull(
f'''SELECT USERID FROM USERS WHERE EMAIL="{form.email.data}"''')
if usercheck != []:
error = 'User already exists'
else:
rtoken = ''.join(
random.choices(string.ascii_letters + string.digits, k=8))
send_email(
form.email.data, 'Data Entry Registration',
f'''Hi {form.firstname.data} {form.lastname.data},\n\nYou've been registered to use the Credit Card Data Entry website. You'll need to enter the below information into the site {host + 'resetpw'} below to access the site for the first time.\n\nUser ID: {form.userid.data}\nConfirmation Code: {rtoken}'''
)
sql_edit(
f'''INSERT INTO USERS(NAME_FIRST, NAME_LAST, EMAIL, PASSWORD, RIGHTS, TOKEN, CONFIRMED, INSERT_DTTM) VALUES("{form.firstname.data}", "{form.lastname.data}", "{form.email.data}", "{generate_password_hash('temp')}", "Normal", "{generate_password_hash(rtoken)}", "FALSE", datetime("now", "localtime"))'''
)
return redirect(url_for('tables.users'))
return render_template("users.html",
data=data,
form=form,
error=error,
permissions=session['rights'])
def ApplicationBase(input_type):
if input_type in ['new', 'edit']:
next
else:
return redirect(url_for('application.home'))
#set form & cookies
error = None
form = ApplicationForm()
form.Product.choices = product_opts()
if input_type == 'new':
session['ENTRY_ID'] = ''
else:
next
if form.validate_on_submit():
if form.dtreceived.data > datetime.date.today():
error = "The date received can't be greater than today's date"
else:
prodcodes = sql_pull(
f'''SELECT * FROM PRODUCT_CODES WHERE PROD_ID = {form.Product.data}'''
)[0]
if input_type == 'new':
maxappno = sql_single_field(
'''SELECT MAX(CAST(REPLACE(ENTRY_ID, 'PAPER_', '') AS INTEGER)) FROM DATA_ENTRY'''
)
if maxappno == None:
ENTRY_ID = 'APPID_1'
else:
ENTRY_ID = 'APPID_' + str(maxappno + 1)
session['ENTRY_ID'] = ENTRY_ID
sql_edit(
f'''INSERT INTO DATA_ENTRY(ENTRY_ID, ENTRY_DATE, PRODUCT, INSERT_DTTM, INSERT_USER)
VALUES("{ENTRY_ID}", "{form.dtreceived.data}", "{prodcodes['PRODUCT']}",
datetime("now", "localtime"), "{session['user']}")'''
)
sql_edit(
f'''INSERT INTO APP_STATUS_TBL(ENTRY_ID, INSERT_DTTM, STATUS, INSERT_USER) VALUES("{ENTRY_ID}", datetime("now", "localtime"), "Needs Review", "{session['user']}")'''
)
tableinfo = sql_pull('''PRAGMA table_info(DATA_ENTRY)''')
nonnullable = [
t['name'] for t in tableinfo if t['notnull'] == 0
]
for col in nonnullable:
sql_edit(
f'''UPDATE DATA_ENTRY SET {col}="" WHERE ENTRY_ID="{ENTRY_ID}" AND {col} IS NULL'''
)
return redirect(
url_for('application.Applicant', input_type='new'))
elif input_type == 'edit':
sql_edit(
f'''UPDATE DATA_ENTRY SET ENTRY_DATE="{form.dtreceived.data}", PRODUCT="{prodcodes['PRODUCT']}",
WHERE ENTRY_ID="{session['ENTRY_ID']}"''')
return redirect(url_for('application.Review'))
else:
error = 'Unknown input type'
return render_template("base_input.html",
form=form,
error=error,
entry_id=session['ENTRY_ID'],
input_type=input_type)
def welcome():
error = None
form = LoginForm(request.form)
if request.method == 'POST':
if form.validate_on_submit():
user = sql_pull('''SELECT * FROM USERS WHERE EMAIL = "%s"''' %
request.form['username'])
if 'Forgot Password' in request.form:
if user == []:
error = 'Cannot find username. Please try again or sign up for account.'
else:
sql_edit(
'UPDATE USERS SET CONFIRMED = "RESET" WHERE EMAIL = "%s"'
% request.form['username'])
rtoken = ''.join(
random.choices(string.ascii_letters + string.digits,
k=8))
sql_edit(
'UPDATE USERS SET TOKEN = "%s" WHERE EMAIL = "%s"' %
(generate_password_hash(rtoken),
request.form['username']))
send_email(
form.username.data,
'Credit Card Data Entry - Reset Password',
"Hi %s %s,\n\nTo reset your password, please enter your confimation code provided below into the prompted screen.\n\nConfirmation Code: %s"
%
(user[0]['NAME_FIRST'], user[0]['NAME_LAST'], rtoken))
return redirect(url_for('login.resetpw'))
else:
if user == []:
error = 'Invalid Credentials. Please try again.'
elif check_password_hash(user[0]['PASSWORD'],
request.form['password']) == False:
error = 'Invalid Credentials. Please try again.'
elif user[0]['CONFIRMED'] != 'TRUE':
if user[0]['CONFIRMED'] == 'RESET':
error = 'Password must be reset.'
else:
error = 'Please finish registering account with confirmation code.'
else:
session['logged_in'] = True
session['user'] = user[0]['NAME_FIRST'] + ' ' + user[0][
'NAME_LAST']
session['email'] = request.form['username']
session['rights'] = user[0]['RIGHTS']
return redirect(url_for('application.home'))
else:
return render_template('welcome.html', form=form, error=error)
return render_template('welcome.html', form=form, error=error)
def CoappQuestion():
if (request.method == 'POST'):
if 'Yes' in request.form:
return redirect(url_for('application.Coapp', input_type='new'))
else:
sql_edit(
f'''UPDATE DATA_ENTRY SET NAME_FIRST_SEC="", NAME_MIDDLE_SEC="", NAME_LAST_SEC="", NAME_SUFFIX_SEC="", DOB_SEC="", EMAIL_SEC="", STREET_ADDRESS1_SEC="",
STREET_ADDRESS2_SEC="", STREET_CITY_SEC="", STREET_COUNTRY_SEC="", STREET_STATE_SEC="", STREET_ZIP_SEC="", MAIL_ADDRESS1_SEC="", MAIL_ADDRESS2_SEC="", MAIL_CITY_SEC="", MAIL_COUNTRY_SEC="",
MAIL_STATE_SEC="", MAIL_ZIP_SEC="", MOBILE_PHONE_SEC="", HOME_PHONE_SEC="", WORK_PHONE_SEC="", EMPLOYMENT_STATUS_SEC="", EMPLOYER_SEC="", OCCUPATION_SEC="", INCOME_SEC="",
NON_TAX_INCOME_SEC="", RESIDENTIAL_STATUS_SEC="", RENT_SEC="" WHERE ENTRY_ID="{session['ENTRY_ID']}"'''
)
return redirect(url_for('application.Review', input_type='new'))
else:
next
return render_template("coapp_question.html", entry_id=session['ENTRY_ID'])
def resetpw():
error = None
form = ForgotPassword()
if form.validate_on_submit():
user = sql_pull('SELECT * FROM USERS WHERE EMAIL = "%s"' %
request.form['username'])
if user == []:
error = 'Invalid Email. Please try again.'
elif check_password_hash(user[0]['TOKEN'],
form.confirmcode.data) == False:
error = 'Invalid Confirmation Code. Please try again.'
else:
sql_edit(
'''UPDATE USERS SET CONFIRMED = "TRUE" WHERE EMAIL = "%s"''' %
form.username.data)
sql_edit(
'''UPDATE USERS SET PASSWORD = "******" WHERE EMAIL = "%s"''' %
(generate_password_hash(
form.password.data), form.username.data))
return redirect(url_for('login.welcome'))
return render_template('resetpassword.html', form=form, error=error)
def Coapp(input_type):
if input_type in ['new', 'edit']:
next
else:
return redirect(url_for('application.home'))
#set form & cookies
error = None
form = ApplicantForm()
form.StCountry.choices = form.MailCountry.choices = country_opts()
form.StState.choices = form.MailState.choices = state_opts()
if form.validate_on_submit():
if (len(re.sub('[^0-9]', '', form.CellPhone.data)) < 10) & (len(
re.sub('[^0-9]', '', form.HomePhone.data)) < 10) & (len(
re.sub('[^0-9]', '', form.WorkPhone.data)) < 10):
error = "At least one valid phone number is required"
elif (form.DOB.data > datetime.date.today()):
error = "Date of birth is greater than current date"
elif len(form.FirstName.data + ' ' + form.MiddleName.data + '*' +
form.LastName.data) > 36:
error = "Together, the first, middle, and last name cannot be more than 36 characters long."
else:
if form.AddrBoo.data == True:
mailaddr1 = form.MailAddress1.data
mailaddr2 = form.MailAddress2.data
mailcity = form.MailCity.data
mailctry = form.MailCountry.data
mailst = form.MailState.data
mailzip = form.MailZip.data
else:
mailaddr1 = form.StAddress1.data
mailaddr2 = form.StAddress2.data
mailcity = form.StCity.data
mailctry = form.StCountry.data
mailst = form.StState.data
mailzip = form.StZip.data
if form.NonTax.data == False:
form.NonTaxIncome.data = 0
else:
next
sql_edit(
f'''UPDATE DATA_ENTRY SET NAME_FIRST_SEC="{form.FirstName.data}", NAME_MIDDLE_SEC="{form.MiddleName.data}", NAME_LAST_SEC="{form.LastName.data}",
NAME_SUFFIX_SEC="{form.Suffix.data}", DOB_SEC="{form.DOB.data}", EMAIL_SEC="{form.Email.data}", STREET_ADDRESS1_SEC="{form.StAddress1.data}",
STREET_ADDRESS2_SEC="{form.StAddress2.data}", STREET_CITY_SEC="{form.StCity.data}", STREET_COUNTRY_SEC="{form.StCountry.data}", STREET_STATE_SEC="{form.StState.data}",
STREET_ZIP_SEC="{form.StZip.data}", MAIL_ADDRESS1_SEC="{mailaddr1}", MAIL_ADDRESS2_SEC="{mailaddr2}", MAIL_CITY_SEC="{mailcity}", MAIL_COUNTRY_SEC="{mailctry}",
MAIL_STATE_SEC="{mailst}", MAIL_ZIP_SEC="{mailzip}", MOBILE_PHONE_SEC="{form.CellPhone.data}", HOME_PHONE_SEC="{form.HomePhone.data}", WORK_PHONE_SEC="{form.WorkPhone.data}",
EMPLOYMENT_STATUS_SEC="{form.EmploymentSt.data}", EMPLOYER_SEC="{form.Employer.data}", OCCUPATION_SEC="{form.Occupation.data}", INCOME_SEC={form.Income.data},
NON_TAX_INCOME_SEC={form.NonTaxIncome.data}, RESIDENTIAL_STATUS_SEC="{form.ResidentialSt.data}", RENT_SEC={form.Rent.data} WHERE ENTRY_ID="{session['ENTRY_ID']}"'''
)
if input_type == 'new':
return redirect(url_for('application.Branch',
input_type='new'))
elif input_type == 'edit':
return redirect(url_for('application.Review'))
else:
error = 'Unknown input type'
return render_template("applicant_input.html",
form=form,
error=error,
title='Co-Applicant Information',
entry_id=session['ENTRY_ID'],
input_type=input_type)