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 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 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 idlestatus():
data = sql_pull(
'''WITH MAX_DTTM_STATUS AS (SELECT ENTRY_ID, MAX(INSERT_DTTM) AS MAX_DT FROM APP_STATUS_TBL GROUP BY ENTRY_ID)
SELECT DE.ENTRY_ID, ENTRY_DATE, NAME_FIRST_PRI || " " ||NAME_MIDDLE_PRI || " " || NAME_LAST_PRI || " " || NAME_SUFFIX_PRI AS FULL_NAME, STATUS AS APPSTATUS, AST.INSERT_USER AS OWNER
FROM DATA_ENTRY DE
LEFT JOIN MAX_DTTM_STATUS MDS ON DE.ENTRY_ID=MDS.ENTRY_ID
LEFT JOIN APP_STATUS_TBL AST ON DE.ENTRY_ID=AST.ENTRY_ID AND AST.INSERT_DTTM=MAX_DT
WHERE STATUS NOT IN ("Needs Review")
''')
return render_template("home.html", data=data)
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 job_prefill(ENTRY_ID):
app = sql_pull(
f'''SELECT * FROM DATA_ENTRY WHERE ENTRY_ID = "{session['ENTRY_ID']}"'''
)[0]
prodid = {
'PROD_ID':
sql_single_field(
f'''SELECT PROD_ID FROM PRODUCT_CODES WHERE PRODUCT = "{app['PRODUCT']}"'''
)
}
app = {**app, **prodid}
if app['CHECKING_ACCT1'] + app['CHECKING_ACCT2'] + app[
'SAVINGS_ACCT1'] + app['SAVINGS_ACCT2'] != '':
atm = {'ATM': True}
else:
atm = {'ATM': False}
app = {**app, **atm}
if app['STREET_ADDRESS1_PRI'] + app['STREET_ADDRESS2_PRI'] != app[
'MAIL_ADDRESS1_PRI'] + app['MAIL_ADDRESS2_PRI']:
mlflgp = {'MLFLG_PRI': True}
else:
mlflgp = {'MLFLG_PRI': False}
app = {**app, **mlflgp}
if app['NON_TAX_INCOME_PRI'] > 0:
taxflgp = {'TAX_FLG_PRI': True}
else:
taxflgp = {'TAX_FLG_PRI': False}
app = {**app, **taxflgp}
if pandas.notnull(app['NAME_LAST_SEC']):
if app['STREET_ADDRESS1_SEC'] + app['STREET_ADDRESS2_SEC'] != app[
'MAIL_ADDRESS1_SEC'] + app['MAIL_ADDRESS2_SEC']:
mlflgs = {'MLFLG_SEC': True}
else:
mlflgs = {'MLFLG_SEC': False}
else:
mlflgs = {'MLFLG_SEC': False}
app = {**app, **mlflgs}
if pandas.notnull(
app['NON_TAX_INCOME_SEC']) and app['NON_TAX_INCOME_SEC'] != '':
if app['NON_TAX_INCOME_SEC'] > 0:
taxflgs = {'TAX_FLG_SEC': True}
else:
taxflgs = {'TAX_FLG_SEC': False}
else:
taxflgs = {'TAX_FLG_SEC': False}
app = {**app, **taxflgs}
return jsonify(app)
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 country_opts():
data = sql_pull('''SELECT DISTINCT COUNTRY, COUNTRY_CODE_3 FROM STATE_COUNTRY WHERE COUNTRY_CODE_3 != "USA" AND RISK = "" ORDER BY COUNTRY''')
choices = [('', 'Select Choice'), ('USA', 'United States of America')] + [(t['COUNTRY_CODE_3'], t['COUNTRY']) for t in data]
return choices
def product_opts():
data = sql_pull('''SELECT PROD_ID, PRODUCT FROM PRODUCT_CODES''')
choices = [(0, 'Select Choice')] + [(t['PROD_ID'], t['PRODUCT']) for t in data]
return choices
def state_opts():
data = sql_pull('''SELECT DISTINCT STATE, STATE_CODE FROM STATE_COUNTRY WHERE STATE_CODE NOT IN ("HI", "MP", "GU") AND STATE IS NOT NULL ORDER BY STATE''')
choices = [('', 'Select Choice'), ('HI', 'Hawaii'), ('GU', 'Guam'), ('MP', 'Saipan')] + [(t['STATE_CODE'], t['STATE']) for t in data]
return choices
def statecountry():
data = sql_pull('''SELECT * FROM STATE_COUNTRY''')
return render_template("statecountry.html", data=data)
def products():
data = sql_pull('''SELECT * FROM PRODUCT_CODES''')
return render_template("products.html", data=data)