def create_new_bill(bill: bills.Bill, product_ids_and_quantities):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
cursor.execute("BEGIN")
for product in product_ids_and_quantities:
sql_cmd = "UPDATE products SET quantity = quantity - ? WHERE id=?;"
cursor.execute(sql_cmd,(int(product[1]),product[0]))
sql_cmd = 'INSERT INTO bills(total_amount,discount,cart_id,username) VALUES(?,?,?,?);'
cursor.execute(sql_cmd, (bill.total_amount, bill.discount, bill.cart_id, bill.username))
result = cursor.lastrowid
if result:
sql_cmd = 'UPDATE carts SET is_active=0 where id=?'
cursor.execute(sql_cmd, (bill.cart_id,))
if cursor.rowcount != 0:
cursor.execute("COMMIT")
bill.id = result
return True
except:
cursor.execute("ROLLBACK")
return None
finally:
if db_conn:
db_conn.close()
def update_users_active_cart(user: users.User,
product_details: products.Product, quantity):
active_cart_id = cart_db.get_active_cart_id_for_user(user.username)
try:
db_conn = init.create_connection()
cursor = db_conn.cursor()
if not active_cart_id:
sql_cmd = 'INSERT INTO carts(username,is_active) VALUES(?,?)'
cursor.execute(sql_cmd, (user.username, 1))
result = cursor.lastrowid
if not result:
return None
active_cart_id = result
sql_cmd = 'INSERT OR REPLACE INTO cartdetails(id, cart_id, product_id, quantity) VALUES( ' \
'(SELECT id from cartdetails where cart_id=? AND product_id=?),?,?,?);'
cursor.execute(sql_cmd,
(active_cart_id, product_details.get_id(),
active_cart_id, product_details.get_id(), quantity))
if cursor.lastrowid:
db_conn.commit()
return True
except:
return
finally:
if db_conn:
db_conn.close()
def insert_new_product(product):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
cursor.execute("BEGIN")
sql_cmd = 'INSERT INTO products(name,price,quantity) VALUES(?,?,?);'
cursor.execute(sql_cmd,
(product.name, product.price, product.quantity))
result = cursor.lastrowid
productcategories = []
total_inserts = 0
for category in product.categories.split(","):
total_inserts += 1
productcategories.extend([result, category])
if result:
sql_cmd = f"INSERT INTO productcategories(product_id,category_id) VALUES " \
f"{','.join(['(?,?)'] * total_inserts)};"
cursor.execute(sql_cmd, productcategories)
if cursor.lastrowid != 0:
cursor.execute("COMMIT")
return result
except:
cursor.execute("ROLLBACK")
return None
finally:
if db_conn:
db_conn.close()
def get_all_categories():
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT id,name from categories;'
cursor.execute(sql_cmd)
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_category_id_from_name(category_name):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT id FROM categories WHERE name=?;'
cursor.execute(sql_cmd, (category_name, ))
result = cursor.fetchone()
return result[0] if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_user_from_username_and_password(username: str, password: str):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT username,password,name,is_admin from users where username=? and password=?;'
cursor.execute(sql_cmd, (username, password))
result = cursor.fetchone()
return users.User(*result) if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_active_cart_id_for_user(username):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT id FROM carts WHERE username=? AND is_active=1;'
cursor.execute(sql_cmd, (username, ))
result = cursor.fetchone()
return result[0] if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_products_details(product_ids):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = f"SELECT id, name, price, quantity from products WHERE id IN ({','.join(['?']*len(product_ids))});"
cursor.execute(sql_cmd, product_ids)
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_all_products():
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT * from products;'
cursor.execute(sql_cmd)
result = cursor.fetchall()
return result if result else None
except:
# print("Failed to fetch records! Try again")
return
finally:
if db_conn:
db_conn.close()
def get_product_details(product_id):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT id, name, price, quantity from products WHERE id=?;'
cursor.execute(sql_cmd, (product_id, ))
result = cursor.fetchone()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_cartdetails_from_id(cart_id):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT * FROM cartdetails WHERE cart_id=?;'
cursor.execute(sql_cmd, (cart_id, ))
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_category_names_for_product(product_id):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT name FROM categories as c WHERE c.id IN' \
'(SELECT category_id FROM productcategories WHERE product_id=?);'
cursor.execute(sql_cmd, (product_id, ))
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_category_names_for_all_product():
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT pc.product_id,name FROM categories as c INNER JOIN productcategories as pc ON ' \
'c.id=pc.category_id;'
cursor.execute(sql_cmd)
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def get_bill_data_for_user(username):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT * FROM bills WHERE username=?;'
cursor.execute(sql_cmd, (username,))
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
return None
def insert_new_category(category_name):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'INSERT INTO categories(name) VALUES(?);'
cursor.execute(sql_cmd, (category_name, ))
result = cursor.lastrowid
if result:
db_conn.commit()
return result
except:
return None
finally:
if db_conn:
db_conn.close()
def create_user(user: users.User):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'INSERT INTO users(username,password,name) VALUES(?,?,?);'
cursor.execute(sql_cmd, (user.username, user.password, user.name))
if cursor.rowcount > 0:
db_conn.commit()
return True
else:
return False
except:
return None
finally:
if db_conn:
db_conn.close()
def get_products_in_category(category_name):
category_id = categories_db.get_category_id_from_name(category_name)
if not category_id:
return None
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT id, name, quantity ,price from products AS p WHERE p.id IN ( SELECT product_id from ' \
'productcategories WHERE category_id=?);'
cursor.execute(sql_cmd, (category_id, ))
result = cursor.fetchall()
return result if result else None
except:
return
finally:
if db_conn:
db_conn.close()
def check_if_username_exists(username: str):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
sql_cmd = 'SELECT 1 FROM users where username=?;'
cursor.execute(sql_cmd, (username, ))
if cursor.fetchone():
return True
else:
return False
except:
print("ERROR: Failed to fetch records!!")
sys.exit(0)
finally:
if db_conn:
db_conn.close()
def remove_product_from_cart(cart_id, product_id, is_only_item_in_cart):
try:
db_conn = None
db_conn = init.create_connection()
cursor = db_conn.cursor()
cursor.execute("BEGIN")
sql_cmd = 'DELETE FROM cartdetails WHERE cart_id=? and product_id=?;'
cursor.execute(sql_cmd, (cart_id, product_id))
if cursor.rowcount != 0:
if is_only_item_in_cart:
sql_cmd = "UPDATE carts SET is_active=0 WHERE id=?"
cursor.execute(sql_cmd, (cart_id, ))
cursor.execute("COMMIT")
return True
except:
cursor.execute("ROLLBACK")
return
finally:
if db_conn:
db_conn.close()