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()