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='******'" )