def login():
form = LoginForm(request.form)
if request.method == 'POST' and form.validate:
email = form.email.data
password = form.password.data
command = """ SELECT *
FROM User
WHERE User.email = "{e}"
""".format(e=email)
cursor.execute(command)
login_verified = cursor.fetchone()
if login_verified != None:
if email == login_verified[1] and password == login_verified[3]:
session['logged_in'] = True
# session['email_address'] = email_address
flash('You are logged in as %s!' %(email), 'success')
# return '<h1> Login Successful </h1>'
return redirect(url_for('app.home'))
else:
# flash('Wrong Email Address','danger')
return render_template('login.html', form=form)
else:
flash('Wrong Email Address','danger')
return render_template('login.html', form=form)
return render_template('login.html', form=form)
def organizations():
command = """SELECT {a}.organization_id, {a}.organization_name, {a}.president, {a}.number_of_members,\
{b}.category_name, {a}.rating, {a}.Image_URL, {a}.description
FROM {a} join {b} ON {a}.category_id = {b}.category_id
""".format(a="organization", b='category')
cursor.execute(command)
org_data = cursor.fetchall()
return render_template("organization.html", org_list=org_data)
def category():
command = """SELECT {b}.category_id, {b}.category_name
FROM {b}
""".format(b='category')
cursor.execute(command)
club_data = cursor.fetchall()
return render_template("category.html", club_category = club_data)
def category_filtered(key):
command = """SELECT {a}.club_id, {a}.organization_name, {a}.president, {a}.number_of_members, {b}.category, {a}.rating
FROM {a} join {b} ON {a}.category_id = {b}.category_id
WHERE {b}.category_id = {k}
""".format(a="organizations", b='category', k=key)
cursor.execute(command)
club_data = cursor.fetchall()
return render_template("organization.html", club_list = club_data)
def organization_search():
org_name = request.args.get('search-name')
condition = ""
if org_name != None:
condition += "organization.organization_name LIKE '%" + (
org_name) + "%'"
if condition == "":
command = """SELECT {a}.organization_id, {a}.organization_name, {a}.president, {a}.number_of_members, {b}.category_name, {a}.rating
FROM {a} join {b} ON {a}.category_id = {b}.category_id
""".format(a="organization", b='category')
else:
command = """SELECT {a}.organization_id, {a}.organization_name, {a}.president, {a}.number_of_members, {b}.category_name, {a}.rating
FROM {a} join {b} ON {a}.category_id = {b}.category_id
WHERE {cond}
""".format(a="organization", b='category', cond=condition)
cursor.execute(command)
org_data = cursor.fetchall()
return render_template("organization.html", org_list=org_data)
def organization_detail(key):
command = """ SELECT {a}.organization_name, {a}.organization_id, {a}.description, {a}.location, {a}.president,
{a}.membership_cost, {a}.is_payment_required, {a}.rating, {a}.number_of_members, {a}.Image_URL
FROM {a}
WHERE {a}.organization_id = {p1}
""".format(a="organization", p1=key)
cursor.execute(command)
club_data3 = cursor.fetchall()
if len(club_data3) == 0:
return "Page Error. The key " + key + " cannot be found"
individual_club = club_data3[0]
command_review = """SELECT {a}.review_id, {a}.first_name, {a}.last_name,{a}.organization_name, {a}.user_review
FROM {a}
WHERE {a}.organization_id = {id}
""".format(a='review', id=key)
cursor.execute(command_review)
review_data = cursor.fetchall()
return render_template("organization_detail.html",
org_detail=individual_club,
review_list=review_data)
def review_edit(key):
command = """SELECT {a}.review_id, {a}.first_name, {a}.last_name, {a}.organization_name, {a}.user_review, {a}.organization_id
FROM {a}
WHERE review_id = {p1}
""".format(a='review', p1=key)
cursor.execute(command)
single_review = cursor.fetchone()
org_id = single_review[5]
form = ReviewForm(request.form,
csrf_enabled=False,
first_name=single_review[1],
last_name=single_review[2],
organization_name=single_review[3],
user_review=single_review[4])
command = """ SELECT {a}.organization_name, {a}.organization_name
FROM {a} INNER JOIN {b} ON {a}.organization_id = {b}.organization_id
WHERE {b}.review_id = {id}
""".format(a='organization', b='review', id=key)
cursor.execute(command)
org_name = cursor.fetchall()
form.org_name.choices = org_name
if request.method == 'POST' and form.validate():
first_name = form.first_name.data
last_name = form.last_name.data # This variable is linked to the models
org_name = form.org_name.data
user_review = form.user_review.data # This command only works when if request.method == POST
command = """
UPDATE review SET first_name='{f}', last_name='{l}',organization_name='{o}',user_review='{u}'
WHERE review_id ={i}
""".format(f=first_name,
l=last_name,
o=org_name,
u=user_review,
i=key)
cursor.execute(command)
conn.commit()
flash('Your Review has been edited', 'success')
return redirect(url_for('app.organization_detail', key=org_id))
if form.errors:
flash(form.errors, 'danger')
return render_template('review-edit.html', form=form, review_id=key)
def review_delete(key):
command = """ SELECT organization_id
FROM review
WHERE review_id = {id}
""".format(id=key)
cursor.execute(command)
selected_org_id = cursor.fetchone()
org_id = selected_org_id[0]
command = """ SELECT *
FROM review
WHERE review_id = {id}
""".format(id=key)
cursor.execute(command)
command = """ DELETE FROM review
WHERE review_id = {id}
""".format(id=key)
cursor.execute(command)
conn.commit()
flash('Your Review has been deleted')
return redirect(url_for('app.organization_detail', key=org_id))
def sign_up():
# 1) Select the table to fetch all data from the database
# command = """SELECT {a}.user_id, {a}.email, {a}.username, {a}.password
# FROM {a}
# """.format(a='User')
# cursor.execute(command)
# user_data = cursor.fetchall()
# 2) Select the MAX(user_id) to make the id increment. If it's None then return 1.
command = """ SELECT MAX(user_id)
FROM User
"""
cursor.execute(command)
next_user_id = cursor.fetchone()
if next_user_id[0] == None:
user_id = 1
else:
user_id = next_user_id[0]+1
# 3) create the form function
form = RegisterForm(request.form, crsf_enabled=False)
# 4) creata an IF function if method = POST and create variable for form database
if request.method == 'POST' and form.validate():
email = form.email.data
username = form.username.data
password = form.password.data
command = """ SELECT {a}.email, {a}.username
FROM {a}
WHERE {a}.email = '{e}' OR {a}.username = '******'
""".format(e=email, u=username, a="User")
cursor.execute(command)
sign_up_verification = cursor.fetchone()
if sign_up_verification == None:
command = """ INSERT INTO {a} (user_id, email, username, password)
VALUES ({id}, '{e}','{un}', '{p}')
""".format(a='User', id=user_id, e=email, un=username, p=password)
cursor.execute(command)
conn.commit()
session['logged_in'] = True
flash('The user %s has been created' % (email), 'success')
return redirect(url_for('app.home'))
email_data = sign_up_verification[0]
username_data = sign_up_verification[1]
if email_data != None and username_data != None:
if username == username_data and email == email_data:
flash('Your email and username has been taken. Please choose another')
return redirect(url_for('app.sign_up'))
if email == email_data:
flash('Your email has been taken. Please choose another')
return redirect(url_for('app.sign_up'))
if username == username_data:
flash('Your username has been taken. Please choose another')
return redirect(url_for('app.sign_up'))
# 5) Insert data into database (INSERT INTO ... VALUES ...)
#5) Create an error form
if form.errors:
flash(form.errors, 'danger')
return render_template('sign_up.html', form=form)
def review_detail(key):
command = """SELECT {a}.review_id, {a}.first_name, {a}.last_name, {a}.organization_name, {a}.user_review
FROM {a}
""".format(a='review')
cursor.execute(command)
review_data = cursor.fetchall()
# This queries the review_id and make it autoincrement
command = """ SELECT MAX(review_id)
FROM review
"""
cursor.execute(command)
next_id = cursor.fetchone()
if next_id[0] == None:
review_id = 1
else:
review_id = next_id[0] + 1
command = """ SELECT {a}.organization_id
FROM {a}
WHERE {a}.organization_id = {id}
""".format(a='organization', id=key)
cursor.execute(command)
selected_org_id = cursor.fetchone()
form = ReviewForm(
request.form,
crsf_enabled=False) # This variable is linked to models.py
command = """ SELECT organization_name, organization_name
FROM organization
WHERE {a}.organization_id = {id}
""".format(a='organization', id=key)
cursor.execute(command)
org_name = cursor.fetchall()
form.org_name.choices = org_name
if request.method == 'POST' and form.validate():
first_name = form.first_name.data
last_name = form.last_name.data # This variable is linked to the models
org_name = form.org_name.data
user_review = form.user_review.data
org_id = selected_org_id[0]
command = """ INSERT INTO review (review_id, first_name, last_name, organization_name, user_review, organization_id)
VALUES ({i},'{f}','{l}','{n}','{r}',{o})
""".format(
i=review_id,
f=first_name,
l=last_name,
n=org_name,
r=user_review,
o=org_id) #This format matches the models and if POST statement
cursor.execute(command)
conn.commit()
# flash is a pop up?
flash('Your Review has been added', 'success')
return redirect(url_for('app.organization_detail', key=org_id))
if form.errors:
flash(form.errors, 'danger')
# This request's syntax is the router.(html file)
# The user will be directed to this URL. The database should already be inserted and able to be viewed once redirected
return render_template('reviewpage.html',
form=form,
review_list=review_data)