def add_products(data_xls):
invalid_row = {}
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "INSERT INTO PRODUCT(`Product_id`,`title`,`description`,`price`,`category`,`occasion`,`image`,`model_id`) VALUES(DEFAULT,%s,%s,%s,%s,%s,%s,%s)"
for i, row in data_xls.iterrows():
img1 = "No File"
img2 = "No file"
img3 = "No File"
with open(tuple(row)[5], "rb") as image_file:
img1 = utils.image_encoding(image_file)
with open(tuple(row)[6], "rb") as image_file:
img2 = utils.image_encoding(image_file)
with open(tuple(row)[7], "rb") as image_file:
img3 = utils.image_encoding(image_file)
if img1 == "No File":
Exception("Please specify correct path")
valid, message = is_valid_product(row)
#print(i, message)
if valid:
# print(row[i])
row['product_image'] = img1
modal_id = add_modal(img3, img2)
product = tuple(row[:6], ) + (modal_id, )
cursor.execute(sql, product)
connection.commit()
else:
invalid_row[i] = message
print(invalid_row[i])
finally:
connection.close()
cursor.close()
return invalid_row
def add_to_cart(product_id, image, quantity, wood_id, pattern_id, user_name,
total_cost):
"""Creates a new order in the cart
Arguments:
product_id: product id
image: customized image
quantity: number of items
wood_id: selected wood type
pattern_id: selected wood pattern
user_name: name of the user
total_cost: cost of the final order
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
order_date = datetime.today().strftime('%Y-%m-%d')
sql = "INSERT INTO CUSTOMER_ORDER(`product_id`,`user_id`,`email_id`,`woodtype_id`,`woodpattern_id`," \
"`total_cost`,`state`,`order_date`,`quantity`,`Order_Id`,`image`) VALUES(%s, null, %s, %s, %s, %s," \
"'In Cart', %s, %s, null, %s) "
cart_details = (product_id, user_name, wood_id, pattern_id,
total_cost, order_date, quantity, image)
result = cursor.execute(sql, cart_details)
order_id = cursor.lastrowid
connection.commit()
return result, order_id
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
def get_product_details_cart(product_id):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT Product_id, title, price from PRODUCT where `Product_id`=%s"
cursor.execute(sql, product_id)
result = cursor.fetchone()
finally:
connection.close()
cursor.close()
return result
def place_order(user_name, price, address, zipcode, card_number, expiry, cvv,
contact):
"""Place an order
Arguments:
user_name: name of the user
price: total payment amount
address: shipping address
zipcode: zipcode
card_number: encrypted card number
expiry: expiry date on the card
cvv: encrypted cvv
contact: contact numnber
Returns:
result: details of the order
order_id: new id for the order
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "INSERT INTO ORDERS(`Address`,`Pincode`,`Contact_No`,`Card_No`,`cvv`,`expiry_date`,`total_price`) " \
"VALUES(%s, %s, %s, %s, %s, %s, %s) "
cart_details = (address, zipcode, contact, card_number, cvv,
expiry, price)
result = cursor.execute(sql, cart_details)
connection.commit()
order_id = cursor.lastrowid
sql1 = "UPDATE CUSTOMER_ORDER SET `Order_Id`=%s where `email_id`=%s AND `state`=%s"
id_update = (order_id, user_name, 'In Cart')
cursor.execute(sql1, id_update)
connection.commit()
sql2 = "UPDATE CUSTOMER_ORDER SET `state`=%s where `Order_Id`=%s"
order_update = ('Order Received', order_id)
cursor.execute(sql2, order_update)
connection.commit()
order_date = datetime.today().strftime('%Y-%m-%d')
sql2 = "UPDATE CUSTOMER_ORDER SET `order_date`=%s where `Order_Id`=%s"
date_update = (order_date, order_id)
cursor.execute(sql2, date_update)
connection.commit()
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
return result, order_id
def get_design():
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from WOOD_PATTERN ORDER BY ID DESC;"
cursor.execute(sql)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def remove(product_id):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "DELETE FROM PRODUCT where `Product_id`=%s"
cursor.execute(sql, product_id)
connection.commit()
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
def get_design_by_id(design_id):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from WOOD_PATTERN where `ID`=%s"
cursor.execute(sql, design_id)
result = cursor.fetchone()
finally:
connection.close()
cursor.close()
return result
def get_wood():
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from WOOD_TYPE"
cursor.execute(sql)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_products():
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from PRODUCT"
cursor.execute(sql)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_products_by_category(category):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from PRODUCT where `category`=%s"
cursor.execute(sql, category)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_products_mask(model_id):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from MODAL where `ID`=%s"
cursor.execute(sql, model_id)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_products_by_occasion(occasion):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from PRODUCT where occasion like %s"
cursor.execute(sql, '%' + occasion + '%')
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def search_product_by_name(name):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from PRODUCT where `title`=%s"
cursor.execute(sql, name)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_wood_by_id(wood_id):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from WOOD_TYPE where `ID`=%s"
cursor.execute(sql, wood_id)
result = cursor.fetchone()
finally:
connection.close()
cursor.close()
return result
def edit(product_id, title, description, price):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "UPDATE PRODUCT SET `title` = %s,`description` = %s,`price` = %s where `Product_id`=%s"
prod = (title, description, price, product_id)
cursor.execute(sql, prod)
connection.commit()
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
def login(email, password):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from USER where `Email`=%s AND `Password`=%s"
login_details = (email, password)
cursor.execute(sql, login_details)
result = cursor.fetchone()
finally:
connection.close()
cursor.close()
return result
def add_modal(modal_mask, image_mask):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "INSERT INTO MODAL VALUES(DEFAULT,%s,%s)"
mask = (modal_mask, image_mask)
cursor.execute(sql, mask)
connection.commit()
return cursor.lastrowid
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
def signup(lastname, firstname, email, password):
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "INSERT INTO USER VALUES(DEFAULT,%s,%s,%s,%s,'Customer')"
signup_details = (lastname, firstname, email, password)
result = cursor.execute(sql, signup_details)
user_id = cursor.lastrowid
connection.commit()
return result, user_id
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
def get_all_orders():
"""Gets all the active orders
Returns:
result: collection of all the active orders
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from `CUSTOMER_ORDER` where `state`<>%s"
cursor.execute(sql, 'In Cart')
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_order_details_by_id(order_id):
"""Get all the order deatils by order id
Arguments:
order_id: order id
Returns:
result: details of the order
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from `CUSTOMER_ORDER` where `ID`=%s"
cursor.execute(sql, order_id)
result = cursor.fetchone()
finally:
connection.close()
cursor.close()
return result
def load_cart(user_name):
"""Load products in the cart
Arguments:
user_name: name of the user
Returns:
result: list of products in the cart
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from CUSTOMER_ORDER where `email_id`=%s AND `state`=%s"
cart_details = (user_name, 'In Cart')
cursor.execute(sql, cart_details)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def get_order_details_for_user(user_name):
"""Gets all the orders for a particular user
Arguments:
user_name: name of the user
Returns:
list: collection of all the orders for a user
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * from CUSTOMER_ORDER where `email_id`=%s AND `state`<>%s"
cart_details = (user_name, 'In Cart')
cursor.execute(sql, cart_details)
result = cursor.fetchall()
finally:
connection.close()
cursor.close()
return result
def update_order_status_for_order(order_status, order_id):
"""Update the order status
Arguments:
order_id: order id
order_status: new order status
Returns:
result: updated order with updated status
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "UPDATE `CUSTOMER_ORDER` SET `state` = %s where `ID` = %s"
result = cursor.execute(sql, (order_status, order_id))
connection.commit()
finally:
connection.close()
cursor.close()
return result
def remove_cart(product_id):
"""Remove the product from cart
Arguments:
product_id: product id
Returns:
result: 1 if the product was removed successfully else 0
"""
connection = db_connection.get_connection()
try:
with connection.cursor() as cursor:
sql = "DELETE from CUSTOMER_ORDER where `ID`=%s"
result = cursor.execute(sql, product_id)
connection.commit()
except Exception as e:
print(e)
finally:
connection.close()
cursor.close()
return result
def tearDown_for_signup(self, user_id):
connection = db_connection.get_connection()
with connection.cursor() as cursor:
sql = "DELETE from USER where `ID`=%s"
cursor.execute(sql, user_id)
connection.commit()
