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