示例#1
0
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)
示例#2
0
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'])
示例#3
0
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)
示例#4
0
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)
示例#5
0
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'])
示例#6
0
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)
示例#7
0
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)