def update_quantity_left(self, quantity_left, category_id, product_id): data_cursor = database.cursor(dictionary=True) sql = "UPDATE product_details SET quantityLeft=%s WHERE categoryID=%s AND productID=%s" values = (quantity_left, category_id, product_id) data_cursor.execute(sql, values) database.commit() data_cursor.close()
def update_cart_quantity(self, cart_quantity, category_id, product_id): data_cursor = database.cursor(dictionary=True) sql = "UPDATE orders SET quantity=%s WHERE salesID=%s AND categoryID=%s AND productID=%s" values = (cart_quantity, self.sessionID, category_id, product_id) data_cursor.execute(sql, values) database.commit() data_cursor.close()
def add_product(self): data_cursor = database.cursor(dictionary=True) sql = "INSERT INTO product_details (categoryID,productID,name,price,cost,quantityLeft) " \ "VALUES (%s, %s, %s, %s, %s, %s)" values = (self.categoryID, self.productID, self.name, self.price, self.cost, self.quantityLeft) data_cursor.execute(sql, values) database.commit()
def get_cart_total_price(self): data_cursor = database.cursor(dictionary=True) sql = "SELECT SUM(o.quantity*p.price) AS sum FROM orders AS o " \ "JOIN product_details AS p ON o.categoryID = p.categoryID AND o.productID = p.productID " \ "WHERE salesID=%s" data_cursor.execute(sql, (self.sessionID, )) total_price = data_cursor.fetchone() data_cursor.close() return total_price
def get_cart(self): data_cursor = database.cursor(dictionary=True) sql = "SELECT o.categoryID, o.productID, p.name, o.quantity, o.quantity*p.price AS amount " \ "FROM orders AS o JOIN product_details AS p ON o.categoryID = p.categoryID " \ "AND o.productID = p.productID WHERE salesID=%s" data_cursor.execute(sql, (self.sessionID, )) sql_result = data_cursor.fetchall() data_cursor.close() return sql_result
def update_category(self, column_name, new_value, target_category_id): self.sql = f"UPDATE product_category SET {column_name}=%s WHERE categoryID=%s" self.values = ( new_value, target_category_id, ) data_cursor = database.cursor(dictionary=True) data_cursor.execute(self.sql, self.values) database.commit() data_cursor.close()
def fetch_quantity_left(self, category_id, product_id): data_cursor = database.cursor(dictionary=True) sql = "SELECT quantityLeft FROM product_details WHERE categoryID=%s AND productID=%s" data_cursor.execute(sql, ( category_id, product_id, )) sql_result = data_cursor.fetchone() data_cursor.close() return sql_result['quantityLeft']
def store_record(self): data_cursor = database.cursor() sql = "INSERT INTO user_details " \ "(userID,password,firstName,lastName,address,cellPhoneNumber,emailAddress,status)" \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" values = (self.cellPhone, self.password, self.firstName, self.lastName, self.address, self.cellPhone, self.emailAddress, "active") data_cursor.execute(sql, values) database.commit() data_cursor.close()
def get_dashboard(self, user_id): data_cursor = database.cursor(dictionary=True) sql = "SELECT o.categoryID, o.productID, p.name, p.price, o.quantity, s.date FROM sales AS s " \ "JOIN orders as o ON s.salesID=o.salesID " \ "JOIN product_details as p ON o.categoryID=p.categoryID AND o.productID=p.productID " \ "WHERE userID=%s ORDER BY s.date DESC" data_cursor.execute(sql, (user_id, )) sql_result = data_cursor.fetchall() data_cursor.close() return sql_result
def insert_into_cart(self, category_id, product_id): data_cursor = database.cursor(dictionary=True) sql = "INSERT INTO orders(salesID,categoryID,productID,quantity) VALUES (%s, %s, %s, 1)" values = ( self.sessionID, category_id, product_id, ) data_cursor.execute(sql, values) database.commit() data_cursor.close()
def save_transaction(self, order_id): now = str(date.today()) total_amount = self.get_cart_total_price() data_cursor = database.cursor(dictionary=True) sql = "INSERT INTO sales(salesID,userID,orderID,totalAmount,date)VALUES(%s, %s, %s, %s, %s)" values = (session['session_id'], session['user_id'], order_id, total_amount['sum'], now) data_cursor.execute(sql, values) database.commit() timestamp = datetime.now().timestamp() session['session_id'] = session['user_id'] + str(timestamp) session['cart'] = 0
def fetch_cart_quantity(self, category_id, product_id): data_cursor = database.cursor(dictionary=True) sql = "SELECT quantity FROM orders WHERE salesID=%s AND categoryID=%s AND productID=%s" data_cursor.execute(sql, ( self.sessionID, category_id, product_id, )) sql_result = data_cursor.fetchone() data_cursor.close() if sql_result is not None: return sql_result['quantity'] else: return None
def remove_from_cart(self, category_id, product_id): cart_quantity = self.fetch_cart_quantity(category_id, product_id) quantity_left = self.fetch_quantity_left(category_id, product_id) data_cursor = database.cursor(dictionary=True) sql = "DELETE FROM orders WHERE salesID=%s AND categoryID=%s AND productID=%s" data_cursor.execute(sql, ( self.sessionID, category_id, product_id, )) database.commit() data_cursor.close() self.update_quantity_left(quantity_left + cart_quantity, category_id, product_id) session['cart'] -= cart_quantity
def products_info(self): data_cursor = database.cursor(dictionary=True) if self.view == "women": data_cursor.execute( "SELECT * FROM product_details WHERE categoryID BETWEEN 1000 AND 1999" ) elif self.view == "men": data_cursor.execute( "SELECT * FROM product_details WHERE categoryID BETWEEN 2000 AND 2999" ) elif self.view == 'admin': data_cursor.execute("SELECT * FROM product_details") sql_result = data_cursor.fetchall() data_cursor.close() return sql_result
def cancel_cart(self, ): data_cursor = database.cursor(dictionary=True) sql = "SELECT categoryID,productID,quantity FROM orders WHERE salesID=%s" data_cursor.execute(sql, (self.sessionID, )) cart = data_cursor.fetchall() if cart is not None: sql = "DELETE FROM orders WHERE salesID=%s" data_cursor.execute(sql, (self.sessionID, )) database.commit() data_cursor.close() for product in cart: cart_quantity = product['quantity'] quantity_left = self.fetch_quantity_left(product['categoryID'], product['productID']) self.update_quantity_left(quantity_left + cart_quantity, product['categoryID'], product['productID']) session['cart'] -= cart_quantity
def login_user(self): data_cursor = database.cursor(dictionary=True) sql = "SELECT password,firstName FROM user_details WHERE userID = %s" data_cursor.execute(sql, (self.userID,)) sql_result = data_cursor.fetchone() data_cursor.close() if sql_result is not None: if self.password == sql_result['password']: timestamp = datetime.now().timestamp() session['logged_in'] = True session['user_id'] = self.userID session['username'] = sql_result['firstName'] session['session_id'] = self.userID + str(timestamp) session['cart'] = 0 return True else: raise Error("Passwords did not match") else: raise Error("Username NOT found")
def add_category(self, category_id, category_name): data_cursor = database.cursor(dictionary=True) sql = "INSERT INTO product_category(categoryID,categoryName)VALUES(%s, %s)" values = (category_id, category_name) data_cursor.execute(sql, values) database.commit()
def delete_category(self, category_id): data_cursor = database.cursor(dictionary=True) sql = "DELETE FROM product_category WHERE categoryID=%s" data_cursor.execute(sql, (category_id, )) database.commit()