def index():
form = LoginForm()
if form.validate_on_submit():
username = dbManager.fetch(
'SELECT user_name FROM customer WHERE user_name=%s',
(form.username.data, ))
password = dbManager.fetch(
'SELECT user_password FROM customer WHERE user_password=%s',
(form.password.data, ))
if len(username) == 0 or len(password) == 0:
username = dbManager.fetch(
'SELECT user FROM employee_user WHERE user=%s',
(form.username.data, ))
password = dbManager.fetch(
'SELECT password FROM employee_user WHERE password=%s',
(form.password.data, ))
if len(username) == 0 or len(password) == 0:
flash('Invalid username or password')
return redirect(url_for('login.index'))
session['logged-in'] = True
session['name'] = username[0][0]
#cart_data = dbManager.fetch('SELECT * FROM cart JOIN item ON item_id = it_id WHERE u_name=%s', (session['name'],))
#session['cart_number'] = len(cart_data)
user_cart_items = dbManager.fetch('SELECT * FROM cart WHERE u_name=%s',
(session['name'], ))
cart_amount = 0
for item in user_cart_items:
cart_amount += item[2]
session['cart_amount'] = cart_amount
return redirect(url_for('homepage.index'))
return render_template('login.html', form=form)
def sign_in_form():
if request.method == 'POST':
email = request.form['Email']
psw = request.form['Password']
customer = dbManager.fetch(
'SELECT * FROM customers WHERE Email=%s AND Password=%s',
(email, psw))
shoppingCartID = dbManager.fetch(
'SELECT ShoppingCartID FROM shopping_carts WHERE Email=%s',
(email, ))
if customer and len(customer):
session['logged_in'] = True
session['customer'] = {
'FirstName': customer[0].FirstName,
'LastName': customer[0].LastName,
'Email': customer[0].Email,
'City': customer[0].City,
'Street': customer[0].Street,
'StreetNumber': customer[0].StreetNumber,
'ZipCode': customer[0].ZipCode,
'PhoneNumber': customer[0].PhoneNumber,
'DateOfBirth': customer[0].DateOfBirth,
'Password': customer[0].Password,
}
session['Email'] = customer[0].Email
session['ShoppingCartID'] = shoppingCartID[0].ShoppingCartID
return redirect(url_for('homepage.index'))
else:
return redirect('/sign_in?customerNotFound=true')
return render_template('sign_in.html')
def sign_up_form():
if request.method == 'POST':
email = request.form['Email']
fName = request.form['FirstName']
lName = request.form['LastName']
DOB = request.form['DateOfBirth']
psw = request.form['Password']
now = datetime.now()
checkCustomer = dbManager.fetch(
'SELECT * FROM customers WHERE Email=%s ', (email, ))
print(checkCustomer)
if checkCustomer == []:
maxID = dbManager.fetch(
'SELECT max(ShoppingCartID) AS max FROM shopping_carts ')
if maxID[0].max:
ShoppingCartID = maxID[0].max + 1
else:
ShoppingCartID = 1
dbManager.commit(
'INSERT INTO customers VALUES (%s, %s,%s, %s, %s, %s, %s, %s, %s, %s)',
(email, psw, fName, lName, DOB, '', '', 0, '', ''))
db = dbManager.commit(
'INSERT INTO shopping_carts VALUES (%s, %s, %s)',
(ShoppingCartID, now, email))
print(db)
return redirect(url_for('sign_in.index'))
else:
return redirect('/sign_up?emailexist=true')
def objects(type):
if type == 'cookie':
query_cookies = dbManager.fetch('SELECT * FROM cookies')
if query_cookies:
cookies = list(map(lambda row: row._asdict(), query_cookies))
return jsonify({
'success': True,
'cookies': cookies,
})
elif type == 'topping':
query_toppings = dbManager.fetch('SELECT * FROM toppings')
if query_toppings:
toppings = list(map(lambda row: row._asdict(), query_toppings))
return jsonify({
'success': True,
'toppings': toppings,
})
elif type == 'ice_cream' or type == 'yogurt':
query_flavours = dbManager.fetch(
'SELECT * FROM flavours AS f WHERE f.Type=%s', (type, ))
if query_flavours:
flavours = list(map(lambda row: row._asdict(), query_flavours))
return jsonify({
'success': True,
'flavours': flavours,
})
else:
return jsonify({'success': False})
def get_users(user_id=None, username=None):
if user_id:
users_api_data = dbManager.fetch('SELECT * FROM users WHERE id=%s',
(user_id, ))
elif username:
users_api_data = dbManager.fetch(
'SELECT * FROM users WHERE username = %s', (username, ))
else:
users_api_data = dbManager.fetch('SELECT * FROM users')
return json_response(users_api_data)
def get_orders(user):
query = "select m.path, p.user_name, sum(p.quantity) as quantity, p.size, p.price, m.makat from productincart as p " \
"inner join models as m on p.makat=m.makat " \
"where p.user_name= '%s'" \
"group by p.user_name, m.makat, p.size, m.path, p.price;" % (user)
orders = db.fetch(query)
query2 = "select sum(p.price*p.quantity) as total from productincart as p " \
"where p.user_name= '%s'" \
"group by p.user_name;" % (user)
total_cart = db.fetch(query2)
return orders, total_cart
def index():
email = session.get('Email')
ShoppingCartID = session.get('ShoppingCartID')
OrderDetails = dbManager.fetch(
'SELECT OrderID, OrderDate, totalPrice FROM orders WHERE shoppingCartID = %s',
(ShoppingCartID, ))
customer = dbManager.fetch('SELECT * FROM customers WHERE Email=%s',
(email, ))
if OrderDetails:
return render_template('profile.html',
Order_Details=OrderDetails,
customer=customer[0])
return render_template('profile.html', customer=customer[0])
def yogurtOrder():
if request.method == 'GET':
maxID = dbManager.fetch('SELECT max(ProductID) AS max FROM products ')
if maxID[0].max:
productID = maxID[0].max + 1
else:
productID = 1
ProductName = "יוגורט " + request.args['amount'] + " קילו"
Amount = request.args['quantity']
ShoppingCartID = session.get('ShoppingCartID')
yogurt_tastes = request.args.getlist('yogurt_tastes')
toppings_chosen = request.args.getlist('toppings_chosen')
if len(yogurt_tastes) >= 1:
rowProduct = dbManager.commit(
'INSERT INTO products VALUES (%s,%s,%s,%s)',
(productID, ProductName, Amount, ShoppingCartID))
print(rowProduct)
for i in range(len(yogurt_tastes)):
rowBox = dbManager.commit(
'INSERT INTO box_flavours VALUES (%s,%s)',
(productID, yogurt_tastes[i]))
print(rowBox)
if len(toppings_chosen) >= 1:
for i in range(len(toppings_chosen)):
rowBoxTopping = dbManager.commit(
'INSERT INTO yogurtbox_toppings VALUES (%s,%s)',
(productID, toppings_chosen[i]))
print(rowBoxTopping)
return render_template('order.html', tab_name='yogurt')
def get_product_order(self, email_address, id):
""" returns a specific product from orders associated to e-mail"""
sql = '''SELECT o.number, o.date_of_order, o.email_address, i.quantity, p.id, p.name, p.price, p.img
FROM orders AS o
JOIN include AS i ON o.number=i.number
JOIN product AS p ON i.sku=p.id
WHERE email_address=%s AND id=%s'''
return dbManager.fetch(sql, (email_address, id))
def is_product_by_category_code(self, category_code):
"""Returns the True if there is a product with a given category code,
False otherwise"""
data = dbManager.fetch('SELECT * FROM product WHERE category_code=%s',
(category_code, ))
if len(data) > 0:
return True
return False
def is_category_code(self, category_code):
''' Returns True if there is category with the given code,
otherwise reutrn False'''
sql = 'SELECT * FROM category WHERE category_code=%s'
data = dbManager.fetch(sql, (category_code, ))
if len(data) > 0:
return True
return False
def get_history(self, email):
"""This method returns the history of all the orders (their details) and the products
which the user submitted on."""
sql = '''
SELECT o.number, o.date_of_order, o.email_address, i.quantity, p.id ,p.name, p.price, p.img
FROM orders AS o
JOIN include AS i ON o.number = i.number
JOIN product AS p ON i.sku = p.id
WHERE email_address = %s
'''
return dbManager.fetch(sql, (email, ))
def get_review_by_email(self, email):
""" This method returns the reviews on product
which was submitted by user (his email address). """
sql = '''
SELECT r.date, r.rank, r.content, r.email_address, p.name
FROM review AS r
JOIN product AS p
ON r.id = p.id
WHERE email_address = %s
'''
return dbManager.fetch(sql, (email, ))
def get_address(self, email):
""" Returns the full address of a user, which identify by his email"""
sql = '''
SELECT c.email_address, c.country, c.city, c.street, c.number
#, z.zip
FROM customer AS c
# JOIN zips AS z ON c.country=z.country
# AND c.city=z.city AND c.street=z.street AND c.number=z.number
WHERE email_address=%s
'''
return dbManager.fetch(sql, (email, ))
def index():
if 'type' in request.args:
catalog_name = request.args['type']
query_results = dbManager.fetch(
'''
SELECT * FROM flavours AS f
WHERE f.Type=%s
''', (catalog_name, ))
if query_results:
return render_template('catalog.html', flavours=query_results)
return render_template('catalog.html')
def index():
if 'flavour' in request.args:
product_flavour = request.args['flavour']
query_result = dbManager.fetch(
'''
SELECT * FROM flavours AS f
WHERE f.FlavourID=%s
''', (product_flavour, ))
if query_result:
return render_template('product.html', flavour=query_result[0])
return render_template('product.html')
def recent_reviews(self, email):
"""Returns the top three recent reviews that the user has posted"""
sql = '''
SELECT r.date, r.rank, r.content, r.email_address, p.name
FROM review AS r
JOIN product AS p
ON r.id = p.id
WHERE email_address = %s
ORDER BY r.date DESC
LIMIT 3
'''
return dbManager.fetch(sql, (email, ))
def index():
catalog_pages = {
'catalog': url_for('catalog.index'),
'corona': url_for('corona.index'),
'oncology': url_for('onco.index'),
'psychiatry': url_for('psy.index'),
'sale': url_for('sale.index'),
}
for page in catalog_pages:
if catalog_pages[page] == request.path:
current_page=page
break
minimal_amount = '0'
if current_page == 'catalog':
items_data=dbManager.fetch('SELECT * FROM item WHERE inventory_amount>%s', (minimal_amount,))
elif current_page == 'sale':
items_data=dbManager.fetch('SELECT * FROM item WHERE discount_percent IS NOT NULL')
else:
items_data=dbManager.fetch('SELECT * FROM item JOIN item_class ON item_id=id WHERE item_class=%s AND inventory_amount>%s', (current_page, minimal_amount))
return render_template('catalog.html', items=items_data)
def index():
if request.method == 'POST':
maxID = dbManager.fetch('SELECT max(OfferID) AS max FROM occasion_offers ')
if maxID[0].max:
OfferID= maxID[0].max+1
else:
OfferID=1
Uname = request.form['FullName']
Uphone = request.form['PhoneNumber']
insertData = dbManager.commit('insert into occasion_offers values (%s, %s, %s)', (OfferID, Uname,Uphone))
if insertData:
return render_template('events.html')
elif request.method == 'GET':
return render_template('events.html')
def valid_login(
self
): # return 0 if the user dosnt exist, 1 if password match user_name, 2 not match
user_exist = self.exist()
if user_exist:
query = "select user_name from users where user_name = '%s' and password = '******';" % (
self.user_name, self.password)
ans = db.fetch(query)
if ans:
return 1
else:
return 2
else:
return 0
def update_models_values(orders, user_name):
counter_misses = 0
for order in orders:
query = "select %s as size_amount from models where makat= %s ;" % (
order.size, order.makat)
ans = db.fetch(query)
if ans[0].size_amount - order.quantity >= 0:
query = "update models set %s=(%s- %s) where makat = %s ;" % (
order.size, order.size, order.quantity, order.makat)
db.commit(query)
user.delete_cart(user_name, order.size, order.makat)
else:
counter_misses = counter_misses + 1
return counter_misses
def form():
if request.method == 'POST':
email = request.form['email']
phone = request.form['phone']
address_street = request.form['address-street']
address_number = request.form['address-number']
city = request.form['city']
zip = request.form['zip']
customer_update = dbManager.commit('UPDATE customers SET City=%s, Street=%s, StreetNumber=%s, ZipCode=%s, PhoneNumber=%s WHERE Email = %s', (city, address_street, address_number, zip, phone, email))
id = request.form['id']
card_number = request.form['cardnumber']
cvv = request.form['cvv']
exp_month = request.form['exp-month']
exp_year = request.form['exp-year']
comment = request.form['comment']
ShoppingCartID = session.get('ShoppingCartID')
Date = datetime.today().strftime('%y-%m-%d')
totalPriceResult = dbManager.fetch('''SELECT SUM(Amount*Price) totalPrice FROM products_prices as pp JOIN products as p on pp.ProductName=p.ProductName where ShoppingCartID=%s;''', (ShoppingCartID,))
totalPrice = totalPriceResult[0].totalPrice
maxID = dbManager.fetch('SELECT max(OrderID) AS max FROM orders')
if maxID[0].max :
OrderID= maxID[0].max+1
else:
OrderID=1
Order_table = dbManager.commit('INSERT into orders VALUES (%s,%s, %s, %s, %s, %s, %s, %s, %s, %s)', (OrderID, id, card_number, cvv, exp_month, exp_year, comment, ShoppingCartID, Date, totalPrice))
if Order_table:
product_delete = dbManager.fetch('SELECT productID FROM products WHERE ShoppingCartID = %s', (ShoppingCartID,))
if product_delete:
for i in range(len(product_delete)):
row1 = dbManager.commit('DELETE from box_flavours where ProductID = %s', (product_delete[i].productID,))
row2 = dbManager.commit('DELETE from icecream_sandwiches where ProductID = %s', (product_delete[i].productID,))
row3 = dbManager.commit('DELETE from yogurtbox_toppings where ProductID = %s', (product_delete[i].productID,))
row4 = dbManager.commit('DELETE from products where ProductID = %s', (product_delete[i].productID,))
return render_template('confirmOrder.html')
elif request.method == 'GET':
return render_template('payment.html')
def index():
if request.method == 'POST':
if request.form.get('email'):
dbManager.commit('UPDATE customer SET email=%s WHERE user_name=%s',
(request.form['email'], session['name']))
if request.form.get('phone_number'):
dbManager.commit(
'UPDATE customer SET phone_number=%s WHERE user_name=%s',
(request.form['phone_number'], session['name']))
if request.form.get('street'):
dbManager.commit(
'UPDATE customer SET street=%s WHERE user_name=%s',
(request.form['street'], session['name']))
if request.form.get('st_number'):
dbManager.commit(
'UPDATE customer SET st_number=%s WHERE user_name=%s',
(request.form['st_number'], session['name']))
if request.form.get('city'):
dbManager.commit('UPDATE customer SET city=%s WHERE user_name=%s',
(request.form['city'], session['name']))
if session['logged-in']:
if dbManager.fetch('SELECT * FROM private WHERE u_name=%s',
(session['name'], )):
Private = True
User_details = dbManager.fetch(
'SELECT * FROM customer LEFT JOIN private ON user_name=u_name WHERE user_name=%s',
(session['name'], ))
Recommended_items = dbManager.fetch(
'SELECT * FROM item JOIN item_department ON item_id=it_id WHERE department=%s ORDER BY price DESC, inventory_amount DESC LIMIT 3',
('private', ))
else:
Private = False
User_details = dbManager.fetch(
'SELECT * FROM customer LEFT JOIN business ON user_name=u_name WHERE user_name=%s',
(session['name'], ))
Recommended_items = dbManager.fetch(
'SELECT * FROM item JOIN item_department ON item_id=it_id WHERE department=%s ORDER BY price DESC, inventory_amount DESC LIMIT 3',
('business', ))
Purchased_items = dbManager.fetch(
'SELECT * FROM customer_purchased JOIN item ON it_id=item_id WHERE u_name=%s',
(session['name'], ))
Customer_manager = dbManager.fetch(
'SELECT * FROM customer JOIN employee ON customer_management_id=employee_id WHERE user_name=%s',
(session['name'], ))
return render_template('user.html',
user_details=User_details[0],
private=Private,
purchased_items=Purchased_items,
customer_manager=Customer_manager[0],
recommended_items=Recommended_items)
else:
return render_template('user.html')
def index():
if request.method == 'POST':
maxID = dbManager.fetch('SELECT max(ContactID) AS max FROM contacts ')
if maxID[0].max:
contactID = maxID[0].max + 1
else:
contactID = 1
Uname = request.form['full-name']
Uphone = request.form['phone-number']
message = request.form['message']
dbManager.commit('insert into contacts values (%s, %s, %s,%s)',
(contactID, Uname, Uphone, message))
return render_template('contact.html')
elif request.method == 'GET':
return render_template('contact.html')
def index():
if session.get('logged_in'):
ShoppingCartID = session.get('ShoppingCartID')
typeIceCream = 'ice_cream'
typeYogurt = 'yogurt'
iceCreamsResults = dbManager.fetch('''SELECT p.productID, pp.productName, Amount, Price, Flavour FROM products_prices as pp JOIN products as p on pp.ProductName=p.ProductName JOIN box_flavours bf on p.ProductID = bf.ProductID JOIN flavours f on bf.FlavourID = f.FlavourID where ShoppingCartID=%s AND Type=%s order by ProductID''', (ShoppingCartID, typeIceCream))
yogurtsResults = dbManager.fetch('''SELECT p.productID, pp.productName, Amount, Price, Flavour FROM products_prices as pp JOIN products as p on pp.ProductName=p.ProductName JOIN box_flavours bf on p.ProductID = bf.ProductID JOIN flavours f on bf.FlavourID = f.FlavourID where ShoppingCartID=%s AND Type=%s order by ProductID''', (ShoppingCartID, typeYogurt))
yogurtsToppingsResults = dbManager.fetch('''SELECT p.productID, ToppingName FROM products as p join yogurtbox_toppings yt on p.ProductID = yt.ProductID join toppings t on yt.ToppingID = t.ToppingID where ShoppingCartID=%s order by ProductID''', (ShoppingCartID,))
cookiesResults = dbManager.fetch('''SELECT p.productID, pp.productName, Amount, Price, CookieType, Flavour FROM products_prices as pp JOIN products as p on pp.ProductName=p.ProductName join icecream_sandwiches i on p.ProductID = i.ProductID join cookies c on i.CookieID = c.CookieID join flavours f on i.FlavourID = f.FlavourID where ShoppingCartID=%s''', (ShoppingCartID,))
totalPriceResult = dbManager.fetch('''SELECT SUM(Amount*Price) totalPrice FROM products_prices as pp JOIN products as p on pp.ProductName=p.ProductName where ShoppingCartID=%s;''', (ShoppingCartID,))
totalAmountResult = dbManager.fetch('''SELECT SUM(Amount) totalAmount FROM products_prices as pp JOIN products as p on pp.ProductName=p.ProductName where ShoppingCartID=%s;''', (ShoppingCartID,))
if iceCreamsResults or yogurtsResults or yogurtsToppingsResults or cookiesResults or totalPriceResult or totalAmountResult:
return render_template('shoppingcart.html', iceCreams=iceCreamsResults, yogurts=yogurtsResults, toppings = yogurtsToppingsResults, cookies = cookiesResults, totalPrice = totalPriceResult[0].totalPrice, totalAmount=totalAmountResult[0].totalAmount)
return render_template('shoppingcart.html')
else:
return render_template('sign_in.html')
def cookieOrder():
if request.method == 'GET':
maxID = dbManager.fetch('SELECT max(ProductID) AS max FROM products ')
if maxID[0].max:
productID = maxID[0].max + 1
else:
productID = 1
ProductName = "קוקילידה"
Amount = request.args['quantity']
ShoppingCartID = session.get('ShoppingCartID')
cookie_chosen = request.args['cookie_chosen']
cookie_filling = request.args['cookie_filling']
if cookie_chosen and cookie_filling:
rowProduct = dbManager.commit(
'INSERT INTO products VALUES (%s,%s,%s,%s)',
(productID, ProductName, Amount, ShoppingCartID))
print(rowProduct)
rowSanwich = dbManager.commit(
'INSERT INTO icecream_sandwiches VALUES (%s,%s,%s)',
(productID, cookie_chosen, cookie_filling))
print(rowSanwich)
return render_template('order.html', tab_name='cookie')
def iceCreamOrder():
if request.method == 'GET':
maxID = dbManager.fetch('SELECT max(ProductID) AS max FROM products ')
if maxID[0].max:
productID = maxID[0].max + 1
else:
productID = 1
selectAmount = request.args['amount']
ProductName = "גלידה " + selectAmount + " קילו"
Amount = request.args['quantity']
ShoppingCartID = session.get('ShoppingCartID')
ice_cream_tastes = request.args.getlist('ice_cream_tastes')
if len(ice_cream_tastes) >= 1:
rowProduct = dbManager.commit(
'INSERT INTO products VALUES (%s,%s,%s,%s)',
(productID, ProductName, Amount, ShoppingCartID))
print(rowProduct)
for i in range(len(ice_cream_tastes)):
rowBox = dbManager.commit(
'INSERT INTO box_flavours VALUES (%s,%s)',
(productID, ice_cream_tastes[i]))
print(rowBox)
return render_template('order.html', tab_name='icecream')
def index():
if request.method == 'POST':
item_data = dbManager.fetch(
'SELECT * FROM item JOIN item_class ON item_id=id WHERE item_id=%s',
(request.form['id'], ))
if dbManager.fetch('SELECT * FROM cart WHERE it_id=%s and u_name=%s',
(request.form['id'], session['name'])):
old_amount = dbManager.fetch(
'SELECT amount FROM cart WHERE it_id=%s and u_name=%s',
(request.form['id'], session['name']))
amount = int(request.form['quantity']) + old_amount[0][0]
dbManager.commit(
'UPDATE cart SET amount=%s WHERE it_id=%s AND u_name=%s',
(amount, item_data[0][0], session['name']))
else:
amount = request.form['quantity']
dbManager.commit('INSERT INTO cart VALUES (%s, %s, %s)',
(session['name'], item_data[0][0], amount))
user_cart_items = dbManager.fetch('SELECT * FROM cart WHERE u_name=%s',
(session['name'], ))
cart_amount = 0
for item in user_cart_items:
cart_amount += item[2]
session['cart_amount'] = cart_amount
purchase = True
return render_template('product.html', purchase=purchase)
else:
item_data = dbManager.fetch(
'SELECT * FROM item JOIN item_class ON item_id=id WHERE item_id=%s',
(request.args['id'], ))
item_class = item_data[0].item_class
recommended_data = dbManager.fetch(
'SELECT * FROM item JOIN item_class ON item_id=id WHERE item_class=%s AND item_id!=%s ORDER BY price DESC, inventory_amount DESC LIMIT 3',
(item_class, request.args['id']))
purchase = False
return render_template('product.html',
current_item=item_data[0],
recommended_items=recommended_data,
purchase=purchase)
def getUserNameByEmail(self, mail):
result = dbManager.fetch(
f"Select fullname From users WHERE email= '{mail}'")
username = [row[0] for row in result]
return username[0]
def checkUser(self, email, passwd):
return dbManager.fetch(
f"Select * From users WHERE email='{email}' AND password='******'"
)
