def adminOrders():
if not adminValidLogin():
return redirect('/admin/login', 303)
if request.method == 'GET':
value = 'all'
else:
value = request.form['status']
if value == "all":
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT * from reserved;")
oders = cur.fetchall()
finally:
con.close()
else:
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT * from reserved WHERE ReservedStatus=%s;",
(value, ))
oders = cur.fetchall()
finally:
con.close()
return render_template("adminOrders.html", oders=oders)
def whisky():
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
whiskyNumber = []
whiskyprod = []
cur.execute('SELECT * FROM whisky WHERE Active = True;')
rows = cur.fetchall()
for row in rows:
whiskyNumber.append(row['WhiskyID'])
whiskyprod.append(row['WhiskyName'])
finally:
con.close()
return render_template("whisky.html",
title="Whisky Master",
message=whiskyprod,
whiskyID=whiskyNumber)
def addToBasket(whiskyID, count):
userID = request.cookies.get('userID')
print("add to basket")
if userID == None:
return False
createBasket(userID)
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SET @basketid = (SELECT ID from Basket WHERE CustomerID = %s);", (userID,))
cur.execute("SELECT ProductNumber FROM BasketProduct WHERE ProductNumber = %s AND BasketID = @basketid;", (whiskyID,))
if cur.fetchone() == None:
cur.execute("SET @id = IF(EXISTS(SELECT ID FROM BasketProduct), ((SELECT MAX(ID) FROM BasketProduct) + 1), 0);")
cur.execute("INSERT INTO BasketProduct( ID, Quantity, BasketID, ProductNumber) VALUES (@id, %s, @basketid, %s);", (count, whiskyID))
else:
cur.execute("UPDATE BasketProduct SET Quantity=(Quantity + %s) WHERE ProductNumber = %s AND BasketID = @basketid;", (count, whiskyID))
con.commit()
finally:
con.close()
return True
def addWhiskyPage():
if not adminValidLogin():
return redirect('/admin/login', 303)
if request.method == 'POST':
print(request.form)
form = request.form
con = getConnection()
try:
with con.cursor() as cur:
cur.execute(
"SET @id = IF(EXISTS(SELECT WhiskyID FROM whisky), ((SELECT MAX(WhiskyID) FROM whisky) + 1), 0);"
)
if form['Region'] != "":
cur.execute(
"""INSERT INTO whisky(WhiskyID, WhiskyName, Price, StorageLeft, Nation, Distillery, Region, Alohol, Picture, Active)
VALUES (@id, %s, %s, %s, %s, %s, %s, %s, %s, True)""",
(form['WhiskyName'], form['Price'],
form['StorageLeft'], form['Nation'],
form['Distillery'], form['Region'], form['Alohol'],
'0'))
else:
cur.execute(
"""INSERT INTO whisky(WhiskyID, WhiskyName, Price, StorageLeft, Nation, Distillery, Alohol, Picture, Active)
VALUES (@id, %s, %s, %s, %s, %s, %s, %s, True)""",
(form['WhiskyName'], form['Price'],
form['StorageLeft'], form['Nation'],
form['Distillery'], form['Alohol'], '0'))
con.commit()
finally:
con.close()
return render_template("addWhisky.html")
def login():
if (request.method == 'POST'):
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM customers WHERE UserName=%s AND PassW=%s;", (request.form['username'], request.form['password']))
row = cur.fetchone()
finally:
con.close()
if row == None:
return "You done Goffed"
else:
ret = make_response(redirect('/user/', 303))
ret.set_cookie('userID', str(row['CustomerID']))
return ret
return render_template(
"login.html")
def oders():
if not userValidLogin():
return redirect('/login')
userID = request.cookies.get('userID')
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute("SELECT * from reserved WHERE CustomerID = %s;", (userID,))
oders = cur.fetchall()
finally:
con.close()
return render_template("oders.html",
oders = oders)
def upload_image():
if not adminValidLogin():
return redirect('/admin/login', 303)
print(os.getcwd())
if request.method == "POST":
if request.files:
image = request.files["image"]
image.save(
os.path.join(app.config["IMAGE_UPLOADS"], image.filename))
#Check if the name was a number.
try:
int(image.filename.split(".")[0])
except:
return redirect(request.url)
con = getConnection()
try:
with con.cursor() as cur:
cur.execute((
"UPDATE whisky SET Picture=True WHERE WhiskyID = %s;"),
(image.filename.split(".")[0], ))
con.commit()
finally:
con.close()
return redirect(request.url)
return render_template("uploadImage.html")
def loginAdmin():
form = LoginForm(request.form)
if (request.method == 'POST'):
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
row = None
try:
with con.cursor() as cur:
cur.execute(
"SELECT * FROM admins WHERE UserName=%s AND PassW=%s;",
(request.form['username'], request.form['password']))
row = cur.fetchone()
finally:
con.close()
if row == None:
return "You done Goffed"
else:
#return userPageLoginAdmin(row['ID'])
ret = make_response(redirect('/admin', 303))
ret.set_cookie('adminID', row['ID'])
return ret
return render_template("adminLogin.html", form=form)
def oder(ID):
if not userValidLogin():
return redirect('/login')
userID = request.cookies.get('userID')
if userID == None:
return redirect('/login')
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute("SELECT whisky.WhiskyID, whisky.WhiskyName, reservedProduct.Price, reservedProduct.Quantity FROM (whisky INNER JOIN reservedProduct on whisky.WhiskyID=reservedProduct.ProductNumber) WHERE ReservedID = %s;", (ID,))
rows = cur.fetchall()
cur.execute("SELECT SUM(Price * Quantity) FROM reservedProduct WHERE ReservedID = %s;", (ID,))
price = cur.fetchone()
price = price['SUM(Price * Quantity)']
cur.execute("""SELECT
customers.CustomerID,
customers.CorpName,
customers.UserName,
customers.Mail,
customers.PNumber,
reserved.ReservedID,
reserved.City,
reserved.Adress,
reserved.ZipCode,
reserved.ReservedStatus
FROM
(reserved INNER JOIN customers ON
reserved.CustomerID = customers.CustomerID)
WHERE
reserved.ReservedID = %s""",
(ID,))
info = cur.fetchone()
finally:
con.close()
#print(rows)
return render_template("oder.html",
whisky = rows,
price = price,
info=info)
def getUsername():
userID = request.cookies.get('userID')
if userID == None:
return None
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT UserName FROM customers WHERE CustomerID=%s;", (userID,))
name = cur.fetchone()
name = name['UserName']
finally:
con.close()
return name
def createBasket(userID):
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT ID FROM Basket WHERE CustomerID = %s;", (userID,))
check = cur.fetchone()
if check != None:
return
cur.execute("SET @id = IF(EXISTS(SELECT ID FROM Basket), ((SELECT MAX(ID) FROM Basket) + 1), 0);")
cur.execute("INSERT INTO Basket( ID, CustomerID) VALUES (@id, %s);", (userID,))
con.commit()
finally:
con.close()
def adminValidLogin():
adminID = request.cookies.get('adminID')
if adminID == None:
return False
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM admins WHERE ID = %s;", adminID)
admins = cur.fetchall()
finally:
con.close()
if len(admins) == 0:
return False
else:
return True
def userValidLogin():
userID = request.cookies.get('userID')
if userID == None:
return False
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM customers WHERE CustomerID = %s;", userID)
users = cur.fetchall()
finally:
con.close()
if len(users) == 0:
return False
else:
return True
def addComment(whiskyID, comment):
userID = request.cookies.get('userID')
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SET @id = IF(EXISTS(SELECT ID FROM comments), ((SELECT MAX(ID) FROM comments) + 1), 0);")
cur.execute("SET @username = (SELECT UserName from customers WHERE CustomerID = %s);", (userID,))
cur.execute("INSERT INTO comments( ID, UserName, Comments, UserID, Productnumber) VALUES (@id, @username, %s, %s, %s);", (comment, userID, whiskyID))
con.commit()
finally:
con.close()
return True
def userPageCookie():
ID = request.cookies.get('userID')
if ID == None:
return redirect('/login')
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM customers WHERE CustomerID=%s;", (ID,))
row = cur.fetchone()
finally:
con.close()
if row == None:
return redirect('/login', 303)
return render_template('userPage.html', customer = row)
def registerUser():
if request.method == 'POST':
print(request.form)
form = request.form
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM customers WHERE UserName=%s OR Mail=%s;", (form['UserName'], form['Mail']))
rows = cur.fetchone()
if rows == None:
cur.execute("SET @id = IF(EXISTS(SELECT CustomerID FROM customers), ((SELECT MAX(CustomerID) FROM customers) + 1), 0);")
cur.execute("""INSERT INTO customers(CustomerID, CorpName, UserName, PassW, Mail, PNumber, City, Address, ZipCode)
VALUES (@id, %s, %s, %s, %s, %s, %s, %s, %s)""",
(form['CorpName'], form['UserName'], form['PassW'], form['Mail'], form['PNumber'], form['City'], form['Address'], 'ZipCode'))
print("insert")
con.commit()
else:
con.close()
return "Username or Mail is taken"
finally:
con.close()
return render_template("register.html")
def purchaseBasket(userID, basketID):
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SET @reservedId = IF(EXISTS(SELECT ReservedID FROM reserved), ((SELECT MAX(ReservedID) FROM reserved) + 1), 0);")
cur.execute("SET @userId = %s;", (userID,))
cur.execute("""INSERT INTO
reserved( ReservedID, CustomerID, ReserverDate, ReservedStatus, Mail, PNumber, City, Adress, ZipCode)
VALUES (@reservedId, %s, %s, %s,
(SELECT Mail FROM customers WHERE CustomerID = @userId),
(SELECT PNumber FROM customers WHERE CustomerID = @userId),
(SELECT City FROM customers WHERE CustomerID = @userId),
(SELECT Address FROM customers WHERE CustomerID = @userId),
(SELECT ZipCode FROM customers WHERE CustomerID = @userId));""",
(userID,"200101", "ordered"))
cur.execute("SELECT Price, StorageLeft, Quantity, ProductNumber FROM (whisky INNER JOIN BasketProduct ON WhiskyID = ProductNumber) WHERE BasketID = %s;", (basketID,))
products = cur.fetchall()
for row in products:
cur.execute("SET @rpId = IF(EXISTS(SELECT ID FROM reservedProduct), ((SELECT MAX(ID) FROM reservedProduct) + 1), 0);")
if int(row['StorageLeft']) < int(row['Quantity']):
con.rollback()
return False
cur.execute("INSERT INTO reservedProduct(ID, ReservedID, Quantity, ProductNumber, Price)VALUES (@rpID, @reservedId, %s, %s, %s);",
(row['Quantity'],
row['ProductNumber'],
row['Price']))
cur.execute("UPDATE whisky SET StorageLeft=(StorageLeft - %s) WHERE WhiskyID = %s;", (row['Quantity'], row['ProductNumber']))
cur.execute("DELETE FROM BasketProduct WHERE BasketID = %s;", (basketID,))
con.commit()
finally:
con.close()
return True
def rateWhisky(whiskyID, grade):
userID = request.cookies.get('userID')
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("SELECT GradingID FROM grading WHERE UserID = %s AND ProductNumber = %s;", (userID, whiskyID))
if cur.fetchone() == None:
cur.execute("SET @id = IF(EXISTS(SELECT GradingID FROM grading), ((SELECT MAX(GradingID) FROM grading) + 1), 0);")
cur.execute("INSERT INTO grading( GradingID, Grade, ProductNumber, UserID) VALUES (@id, %s, %s, %s);", (grade, whiskyID, userID))
else:
cur.execute("UPDATE grading SET Grade = %s WHERE ProductNumber = %s AND UserID = %s;", (grade, whiskyID, userID))
con.commit()
finally:
con.close()
return True
def adminOrder(ID):
if not adminValidLogin():
return redirect('/admin/login', 303)
if request.method == 'POST':
print(request.form)
value = request.form['status']
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute(
"UPDATE reserved SET ReservedStatus=%s WHERE ReservedID = %s;",
(value, ID))
con.commit()
finally:
con.close()
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute(
"SELECT whisky.WhiskyID, whisky.WhiskyName, reservedProduct.Price, reservedProduct.Quantity FROM (whisky INNER JOIN reservedProduct on whisky.WhiskyID=reservedProduct.ProductNumber) WHERE ReservedID = %s;",
(ID, ))
rows = cur.fetchall()
cur.execute(
"SELECT SUM(Price * Quantity) FROM reservedProduct WHERE ReservedID = %s;",
(ID, ))
price = cur.fetchone()
price = price['SUM(Price * Quantity)']
cur.execute(
"""SELECT
customers.CustomerID,
customers.CorpName,
customers.UserName,
customers.Mail,
customers.PNumber,
reserved.ReservedID,
reserved.City,
reserved.Adress,
reserved.ZipCode,
reserved.ReservedStatus
FROM
(reserved INNER JOIN customers ON
reserved.CustomerID = customers.CustomerID)
WHERE
reserved.ReservedID = %s""", (ID, ))
info = cur.fetchone()
finally:
con.close()
return render_template("adminOrder.html",
whisky=rows,
price=price,
info=info)
def basketPage():
error = 'no'
if not userValidLogin():
return redirect('/login', 303)
userID = request.cookies.get('userID')
if request.method == 'POST':
print("form: "+ str(request.form))
field = (next(iter(request.form)))
#buy basket
if field == "buy":
con = getConnection()
basketID = None
try:
with con.cursor() as cur:
cur.execute("SELECT ID from Basket WHERE CustomerID = %s;", (userID,))
basketID = cur.fetchone()['ID']
print("basketID: " + str(basketID))
con.commit()
finally:
con.close()
if not purchaseBasket(userID, basketID):
error = 'no purshese'
#update basket
else:
print(request.form)
qvant = request.form[field]
print("ID " + field + "\nQ: " + qvant)
if qvant == '' or int(qvant) < 0:
return redirect('/basket')
con = getConnection()
if qvant == '0':
try:
with con.cursor() as cur:
cur.execute("DELETE FROM BasketProduct WHERE ID = %s;", (field, ))
con.commit()
finally:
con.close()
else:
try:
with con.cursor() as cur:
cur.execute("UPDATE BasketProduct SET Quantity=%s WHERE ID = %s;", (qvant, field))
con.commit()
finally:
con.close()
return redirect('/basket')
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM (whisky INNER JOIN BasketProduct on whisky.WhiskyID=BasketProduct.ProductNumber) WHERE BasketID = (SELECT ID FROM Basket WHERE CustomerID=%s);", (userID,))
row = cur.fetchall()
cur.execute("SELECT SUM(Price * Quantity) FROM (whisky INNER JOIN BasketProduct on whisky.WhiskyID=BasketProduct.ProductNumber) WHERE BasketID = (SELECT ID FROM Basket WHERE CustomerID=%s);", (userID,))
price = cur.fetchone()
price = price['SUM(Price * Quantity)']
finally:
con.close()
return render_template(
"basket.html",
title = "Whisky Master",
basket = row,
price = price,
error = error)
def whiskypage(whiskyID):
#form = AddForm(request.form)
if (request.method == 'POST'):
if not userValidLogin():
return redirect('/login', 303)
print(request.form)
comments = request.form
field = (next(iter(request.form)))
print(field)
#End up here if comments.
if field == 'Comments':
if len(comments['Comments']) > 511:
return "comment To long"
if addComment(whiskyID, comments['Comments']):
return redirect('/whisky/' + whiskyID)
else:
return redirect('/login')
#End up here if Buy
elif field == 'addToCart':
count = int(request.form['addToCart'])
print(count)
if addToBasket(whiskyID, count):
print("here")
return redirect('/whisky/' + whiskyID)
else:
return redirect('/login')
elif field == 'rate':
if rateWhisky(whiskyID, comments['rate']):
return redirect('/whisky/' + whiskyID)
else:
return redirect('/login')
else:
return 'wrong!!!!!'
else:
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM whisky WHERE WhiskyID=%s;",
(str(whiskyID), ))
row = cur.fetchone()
cur.execute("SELECT * FROM comments WHERE ProductNumber=%s;",
(str(whiskyID), ))
comments = cur.fetchall()
cur.execute(
"SELECT AVG(Grade) FROM grading WHERE ProductNumber = %s;",
(whiskyID, ))
grade = cur.fetchone()
finally:
con.close()
return render_template("whiskypage.html",
title="Whisky Master",
message=row,
comments=comments,
grade=grade)
def editWhiskuPage(wid):
if not adminValidLogin():
return redirect('/admin/login', 303)
if request.method == 'POST':
field = next(iter(request.form))
value = request.form[field]
if value == "":
return redirect('/admin/editwhisky/' + str(wid))
#Removes comments
if field == "removeComment":
con = getConnection()
try:
with con.cursor() as cur:
cur.execute("DELETE FROM comments WHERE ID = %s;",
(value, ))
con.commit()
finally:
con.close()
return redirect('/admin/editwhisky/' + str(wid))
if field == "dont":
con = getConnection()
try:
with con.cursor() as cur:
cur.execute(
"UPDATE whisky SET Active = False WHERE WhiskyID = %s;",
(wid, ))
cur.execute(
"DELETE FROM BasketProduct WHERE ProductNumber = %s;",
(wid, ))
con.commit()
finally:
con.close()
return redirect('/admin/editwhisky/' + str(wid))
if field == "do":
con = getConnection()
try:
with con.cursor() as cur:
cur.execute(
"UPDATE whisky SET Active = True WHERE WhiskyID = %s;",
(wid, ))
con.commit()
finally:
con.close()
return redirect('/admin/editwhisky/' + str(wid))
con = getConnection()
try:
with con.cursor() as cur:
cur.execute(("UPDATE whisky SET " + field +
"=%s WHERE WhiskyID = %s;"), (value, wid))
con.commit()
finally:
con.close()
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
with con.cursor() as cur:
cur.execute("SELECT * FROM whisky WHERE WhiskyID=%s;",
(str(wid), ))
whisky = cur.fetchone()
cur.execute("SELECT * FROM comments WHERE ProductNumber=%s;",
(str(wid), ))
comments = cur.fetchall()
cur.execute(
"SELECT AVG(Grade) FROM grading WHERE ProductNumber = %s;",
(wid, ))
grade = cur.fetchone()
finally:
con.close()
return render_template("editwhisky.html",
whisky=whisky,
comments=comments,
grade=grade)
def admin():
if not adminValidLogin():
return redirect('/admin/login', 303)
if request.method == 'POST':
modID = (next(iter(request.form)))
qvant = request.form[modID]
#Filter Whisky
if modID == "filter":
con = getConnection()
try:
if qvant == "active":
with con.cursor() as cur:
cur.execute('SELECT * FROM whisky WHERE Active = True')
rows = cur.fetchall()
elif qvant == "deactive":
con = getConnection()
with con.cursor() as cur:
cur.execute(
'SELECT * FROM whisky WHERE Active = False')
rows = cur.fetchall()
else:
with con.cursor() as cur:
cur.execute('SELECT * FROM whisky')
rows = cur.fetchall()
finally:
con.close()
return render_template("adminPage.html",
title="Whisky Master",
inventory=rows)
#Try to change value of Whisky
elif qvant == '' or int(qvant) < 0:
return redirect('/admin')
else:
con = getConnection()
try:
with con.cursor() as cur:
cur.execute(
"UPDATE whisky SET StorageLeft=%s WHERE WhiskyID = %s;",
(qvant, modID))
con.commit()
finally:
con.close()
return redirect('/admin')
#The connection the the server.
con = getConnection()
# Try to connect to the server and find all values for
# whisky tabel.
try:
#Just want to fetch all the inventory.
with con.cursor() as cur:
cur.execute('SELECT * FROM whisky')
rows = cur.fetchall()
finally:
con.close()
return render_template("adminPage.html",
title="Whisky Master",
inventory=rows)
