def updatePaidInOrder(order_id, paid):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"UPDATE orders SET paid = '{paid}' WHERE order_id = {order_id}")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def updateQuantityInProduct(product_id, updatedProdQty):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"UPDATE products SET model_quantity = {updatedProdQty} WHERE product_id = {product_id}")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def insertUserInfo(firstname, lastname, email_id, phone, passwd, disabled, veteran, user_type):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"INSERT INTO users (fname, lname, email, phone, password_provided, disabled, war_veteran, type_of_user) VALUES ('{firstname}', '{lastname}', '{email_id}', '{phone}', MD5('{passwd}'), '{disabled}', '{veteran}', '{user_type}')")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def delete_inventories(product_id, seller_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"DELETE FROM products WHERE product_id = '{product_id}' AND user_id = '{seller_id}'")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def updateProduct(id, make, model_name, qty, year, color, cost):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"UPDATE products SET make = '{make}', model = '{model_name}', model_quantity = {qty}, model_year = '{year}', color = '{color}', price = '{cost}' WHERE product_id = {id}")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def insertIntoOrder(buyer_id, product_id, order_qty, orig_price, discount, tax, downPayment_discount, paid):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"INSERT INTO orders (user_id, product_id, order_quantity, original_price, discount_percent, tax_percent, downPaymentDiscount, paid) VALUES ('{buyer_id}', '{product_id}', '{order_qty}', '{orig_price}', '{discount}', '{tax}', '{downPayment_discount}', '{paid}')")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def deleteOrder(order_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"DELETE FROM orders WHERE order_id = '{order_id}'")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def showAvailableProductsToUpdateAndDelete(seller_id):
conn = c.returnConnection()
cursor = conn.cursor(buffered=True)
# fetch data to update based on user that is logged in - show user products that belongs to that seller ESPECIALLY for Update!
cursor.execute(f"SELECT * FROM products WHERE user_id = '{seller_id}'")
products = list(cursor.fetchall())
if products is not None:
printListForSeller(products)
else:
print("You do not have any products yet!")
def insertProductInfo(user_id, manufacturer, model_name, model_quantity, model_year, color, cost):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"INSERT INTO products (user_id, make, model, model_quantity, model_year, color, price) VALUES ('{user_id}', '{manufacturer}', '{model_name}', '{model_quantity}', '{model_year}', '{color}', '{cost}')")
conn.commit()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def getUserId(email):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT user_id FROM users WHERE user_email = '{email}' ")
conn.commit()
cursor.close()
conn.close()
return row[0]
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def deleteUser(id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f'DELETE FROM users WHERE user_id = {id}')
conn.commit()
readUserInfo()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def checkIfUserExist(login_email, login_password):
conn = c.returnConnection()
try:
cursor = conn.cursor(buffered=True)
cursor.execute(f"SELECT user_id, fname, email, type_of_user FROM users WHERE email = '{login_email}' and password_provided = MD5('{login_password}')")
data=cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def fetchQuantity(order_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT order_quantity FROM orders WHERE order_id = '{order_id}'")
qty = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return qty
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def checkOrderQuantityEqualOrLesserThanAvailableProduct(product_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT model_quantity FROM products WHERE product_id = {product_id}")
data = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def getProductIDForViewCartForCurrentUser(buyer_id, paid):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT product_id, order_id, order_quantity FROM orders WHERE user_id = {buyer_id} AND paid = '{paid}'")
prod_ids = list(cursor.fetchall())
conn.commit()
cursor.close()
conn.close()
return prod_ids
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def fetchDisabledOrVeteranFromUser(buyer_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT disabled, war_veteran FROM users WHERE user_id = {buyer_id}")
data=cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def searchBestMatch(make, model, year, color):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT * FROM products WHERE make LIKE '%{make}%' OR model LIKE '%{model}%' OR model_year LIKE '%{year}%' OR color LIKE '%{color}%'")
products = list(cursor.fetchall())
conn.commit()
cursor.close()
conn.close()
return products
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def fetchProdQuantity(product_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT model_quantity FROM products WHERE product_id = '{product_id}'")
qty = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return qty
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def getUserDetailsForOrderSummary(buyer_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT fname, lname, email, phone FROM users WHERE user_id = '{buyer_id}'")
data = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def getProductDetailsForOrderSummary(product_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT make, model, model_year, color FROM products WHERE product_id = '{product_id}'")
data = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def getOrderDetailsForSummary(order_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT original_price, order_quantity, discount_percent, tax_percent, downPaymentDiscount FROM orders WHERE order_id = '{order_id}'")
data = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def insertuserinfo(type_of_user):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(
f"INSERT INTO users(type_of_user) VALUES ('{type_of_user}')")
conn.commit()
userinfo()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def fetchPriceFromProduct(product_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT color, price FROM products WHERE product_id = {product_id}")
data=cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
return data
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def updateUser(field, id, newValue):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(
f"UPDATE users SET {field} = '{newValue}' WHERE user_id = {id}")
conn.commit()
readUserInfo()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def insertproductInfo(VIN, make, model, year, color, qty, price):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(
f"INSERT INTO products(VIN,make,model,year,color,qty,price) VALUES ('{VIN}', '{make}', '{model}' , '{year}', '{color}, '{qty}', '{price}')"
)
conn.commit()
productinfo()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def insertcustomerInfo(fname, lname, age, address, phone):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(
f"INSERT INTO customers(firstname,lastname,age,address,phone) VALUES ('{fname}', '{lname}', '{age}' , '{address}','{phone}')"
)
conn.commit()
customerinfo()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def updateUsers(id,field, value):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"UPDATE users SET {field}='{value}' WHERE user_id={id}")
#print("return = {}".format(cursor.rowcount))
conn.commit()
cursor.close()
conn.close()
return(cursor.rowcount)
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def insertUserInfo(fname, lname, age, phone):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(
f"INSERT INTO users (user_firstname, user_lastname, user_age, user_phone) VALUES ('{fname}', '{lname}', {age}, '{phone}')"
)
conn.commit()
readUserInfo()
cursor.close()
conn.close()
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def deleteUser(user_id):
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f'DELETE FROM users WHERE user_id = {user_id}')
#print("return = {}".format(cursor.rowcount))
conn.commit()
cursor.close()
conn.close()
return cursor.rowcount
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
def totalCarsListed():
conn = c.returnConnection()
try:
cursor = conn.cursor()
cursor.execute(f"SELECT MAX(car_id) FROM cars")
conn.commit()
#readUsersInfo()
cursor.close()
conn.close()
return row[0]
except (Exception, mysql.connector.Error) as error:
print('Error while fetching data from mySQL', error)
