def login():
"""Login screen"""
if request.method == 'POST':
db_conn = conn_sql()
db_cur = db_conn.cursor()
username = request.form['username']
password = request.form['password']
error = None
db_cur.execute(
'SELECT username,password FROM tb_user WHERE username = %s',
(username, ))
user = db_cur.fetchone()
db_cur.close()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user[1], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user[0]
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def register():
"""Check and register new users"""
if request.method == 'POST':
db_conn = conn_sql()
db_cur = db_conn.cursor()
username = request.form['username']
password = request.form['password']
error = None
if not username:
error = 'Username is required'
elif not password:
error = 'Password is required'
else:
db_cur.execute('SELECT id FROM tb_user WHERE username = %s',
(username, ))
user_id = db_cur.fetchone()
if user_id is not None:
error = 'User {} is already registered'.format(username)
if error is None:
db_cur.execute(
'INSERT INTO tb_user (username, password) VALUES (%s, %s)',
(username, generate_password_hash(password)))
db_conn.commit()
db_cur.close()
return redirect(url_for('auth.login'))
db_cur.close()
flash(error)
return render_template('auth/register.html')
def delete(bottle_id):
"""Remove bottle from inventory"""
get_bottle(bottle_id)
db_conn = conn_sql()
db_cur = db_conn.cursor()
db_cur.execute('DELETE FROM tb_whisky WHERE id = %s', (bottle_id, ))
db_conn.commit()
db_cur.close()
return redirect(url_for('listing.index'))
def get_dist_id(distillery):
"""Get distillery ID"""
dist_code = None
db_conn = conn_sql()
db_cur = db_conn.cursor()
db_cur.execute('SELECT id FROM tb_distillery WHERE name = %s',
(distillery, ))
dist_rec = db_cur.fetchone()
if dist_rec:
dist_code = dist_rec[0]
db_cur.close()
return dist_code
def load_logged_in_user():
"""Add user to session"""
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
db_conn = conn_sql()
db_cur = db_conn.cursor()
db_cur.execute('SELECT * FROM tb_user WHERE username = %s',
(user_id, ))
g.user = db_cur.fetchone()
db_cur.close()
def index():
"""Main Index Page, lists all available bottles"""
db_conn = conn_sql()
db_cur = db_conn.cursor()
db_cur.execute("""SELECT w.id, u.username,
w.name, d.name AS distillery,
d.region, d.country, w.age, w.abv,
w.notes
FROM tb_whisky w
JOIN tb_distillery d ON w.distillery = d.id
JOIN tb_user u ON u.id = w.owner""")
bottles = db_cur.fetchall()
db_cur.close()
return render_template('listing/index.html', bottles=bottles)
def update(bottle_id):
"""Update a bottle in inventory"""
error = None
bottle = get_bottle(bottle_id)
if request.method == 'POST':
name = request.form['name']
distillery = request.form['distillery']
age = request.form['age']
abv = request.form['abv']
notes = request.form['notes']
dist_code = get_dist_id(distillery)
if not name:
error = 'Bottle Name required'
elif not dist_code:
error = 'Distillery is blank or not found'
elif not age:
error = 'Age Statement required'
elif not abv:
error = 'Alcohol By Volume required'
else:
db_conn = conn_sql()
db_cur = db_conn.cursor()
# Do insert
db_cur.execute(
"""UPDATE tb_whisky
SET name = %s,
distillery = %s,
age = %s,
abv = %s,
notes = %s
WHERE id = %s
""", (
name,
dist_code,
age,
abv,
notes,
bottle_id,
))
db_conn.commit()
db_cur.close()
return redirect(url_for('listing.index'))
flash(error)
return render_template('listing/update.html', bottle=bottle)
def create():
"""Add bottle to inventory"""
if request.method == 'POST':
error = None
name = request.form['name']
distillery = request.form['distillery']
age = request.form['age']
abv = request.form['abv']
notes = request.form['notes']
dist_code = get_dist_id(distillery)
if not name:
error = 'Bottle Name required'
elif not dist_code:
error = 'Distillery is blank or not found'
elif not age:
error = 'Age Statement required'
elif not abv:
error = 'Alcohol By Volume required'
else:
db_conn = conn_sql()
db_cur = db_conn.cursor()
# Do insert
db_cur.execute(
"""INSERT INTO public.tb_whisky (name, distillery, age, abv, owner, notes)
VALUES (%s, %s, %s, %s, %s, %s)""", (
name,
dist_code,
age,
abv,
g.user[0],
notes,
))
db_conn.commit()
db_cur.close()
return redirect(url_for('listing.index'))
flash(error)
return render_template('listing/create.html')
def get_bottle(bottle_id, check_owner=True):
"""Get bottle by ID"""
db_conn = conn_sql()
db_cur = db_conn.cursor()
db_cur.execute(
"""SELECT w.id, u.username,
w.name, d.name AS distillery,
d.region, d.country, w.age, w.abv,
w.notes
FROM tb_whisky w
JOIN tb_distillery d ON w.distillery = d.id
JOIN tb_user u ON u.id = w.owner
WHERE w.id = %s""", (bottle_id, ))
bottle = db_cur.fetchone()
db_cur.close()
if bottle is None:
abort(404, "Bottle ID {0} does not exist,".format(bottle_id))
if check_owner and bottle[1] != g.user[1]:
abort(403)
return bottle
