def load_product(request, slug):
# send to login if no session exists:
if not ('customer_id' in request.session):
return redirect(load_login)
cart_item_no = ""
if 'cart' in request.session:
my_cart = request.session['cart']
if len(my_cart) > 0:
cart_item_no = "(" + str(len(my_cart)) + ")"
if request.method == 'POST' and 'search-btn' in request.POST:
keywords = request.POST['search']
keywords = str(keywords)
return homeviews.load_search_result(request, keywords)
cursor = connection.cursor()
sql = """SELECT p.PRODUCT_ID, p.NAME, p.CATEGORY, p.BRAND, p.PRICE, p.DESCRIPTION, o.DESCRIPTION AS DISCOUNT, p.IMAGE_LINK, s.quantity, o.DISCOUNT_TYPE
FROM THE_BAZAAR.PRODUCT p, THE_BAZAAR.OFFER o, THE_BAZAAR.STOCK s
WHERE p.OFFER_ID = o.OFFER_ID(+)
AND p.PRODUCT_ID = s.PRODUCT_ID
AND p.PRODUCT_ID = """ + slug + """;"""
cursor.execute(sql)
table = cursor.fetchall()
product_dict = []
for r in table:
id = r[0]
name = r[1]
category = r[2]
brand = r[3]
price = r[4]
description = r[5]
discount = r[6]
image_link = r[7]
quantity_left = r[8]
discount_desc = r[9]
row = {
'id': id,
'name': name,
'category': category,
'brand': brand,
'price': price,
'description': description,
'discount': discount,
'image_link': image_link,
'quantity_left': quantity_left,
'discount_desc': discount_desc
}
product_dict = row
sql = """SELECT DISTINCT CATEGORY
FROM PRODUCT
WHERE PRODUCT_ID = ANY(
SELECT PRODUCT_ID
FROM STOCK
WHERE EXPIRE_DATE > SYSDATE AND QUANTITY > 0
UNION
SELECT PRODUCT_ID
FROM STOCK WHERE
EXPIRE_DATE IS NULL AND QUANTITY > 0
);"""
cursor.execute(sql)
table = cursor.fetchall()
cat_dict = []
for r in table:
category = r[0]
row = {'category': category}
cat_dict.append(row)
sql = """SELECT OFFER_ID FROM PRODUCT WHERE OFFER_ID IS NOT NULL;"""
cursor.execute(sql)
temp = cursor.fetchall()
if cursor.rowcount > 0:
row = {'category': "Discounts"}
cat_dict.append(row)
customer_id = request.session.get('customer_id')
customer_id = str(customer_id)
sql = "SELECT EMAIL FROM CUSTOMER WHERE CUSTOMER_ID = " + customer_id
cursor.execute(sql)
name_table = cursor.fetchall()
username = [data[0] for data in name_table]
username = str(username[0])
stock_info = ""
pid = product_dict['id']
in_stock = cursor.callfunc('IS_IN_STOCK', str, [pid, 1])
in_stock = str(in_stock)
if in_stock != "NO":
stock_info = "available"
# adding to cart
if request.method == 'POST' and 'quantity-btn' in request.POST:
data = request.POST['quantity']
quantity = {'quantity': data}
q_dict = quantity
pid = product_dict['id']
# IS_IN_STOCK pl-sql function checks whether at least 1 with valid exp_date is in stock
in_stock = cursor.callfunc('IS_IN_STOCK', str, [pid, 1])
in_stock = str(in_stock)
if in_stock != "NO":
stock_info = "available"
if not ('cart' in request.session):
request.session['cart'] = init_cart(pid, data)
cart = request.session['cart']
cart_item_no = len(cart)
cart_item_no = "(" + str(cart_item_no) + ")"
else:
cart = request.session['cart']
cart = add_to_cart(pid, data, cart)
cart_item_no = len(cart)
cart_item_no = "(" + str(cart_item_no) + ")"
request.session['cart'] = cart
return render(
request, 'productpage.html', {
'stock_info': stock_info,
'username': username,
'product': product_dict,
'categories': cat_dict,
'quantity_selected': q_dict,
'items_len': cart_item_no
})
cursor.close()
return render(
request, 'productpage.html', {
'stock_info': stock_info,
'username': username,
'product': product_dict,
'categories': cat_dict,
'items_len': cart_item_no
})
def load_cart(request):
# send to login if no session exists:
if not ('customer_id' in request.session):
return redirect(load_login)
cart_item_no = ""
if 'cart' in request.session:
my_cart = request.session['cart']
if len(my_cart) > 0:
cart_item_no = "(" + str(len(my_cart)) + ")"
# buy btn pressed:
if request.method == 'POST' and 'buy-btn' in request.POST:
payment = str(request.POST['payment'])
gift_for = str(request.POST['gift'])
cart_item_no = ""
if check_stock_before_buy(request):
check_out(payment, gift_for, request)
else:
return redirect(load_cart)
# search btn pressed
if request.method == 'POST' and 'search-btn' in request.POST:
keywords = request.POST['search']
keywords = str(keywords)
return homeviews.load_search_result(request, keywords)
cursor = connection.cursor()
customer_id = request.session.get('customer_id')
customer_id = str(customer_id)
sql = "SELECT EMAIL FROM CUSTOMER WHERE CUSTOMER_ID = " + customer_id
cursor.execute(sql)
name_table = cursor.fetchall()
username = [data[0] for data in name_table]
username = str(username[0])
sql = """SELECT DISTINCT CATEGORY
FROM PRODUCT
WHERE PRODUCT_ID = ANY(
SELECT PRODUCT_ID
FROM STOCK
WHERE EXPIRE_DATE > SYSDATE AND QUANTITY > 0
UNION
SELECT PRODUCT_ID
FROM STOCK WHERE
EXPIRE_DATE IS NULL AND QUANTITY > 0
);"""
cursor.execute(sql)
table = cursor.fetchall()
cat_dict = []
for r in table:
category = r[0]
row = {'category': category}
cat_dict.append(row)
sql = """SELECT OFFER_ID FROM PRODUCT WHERE OFFER_ID IS NOT NULL;"""
cursor.execute(sql)
temp = cursor.fetchall()
if cursor.rowcount > 0:
row = {'category': "Discounts"}
cat_dict.append(row)
cursor.close()
cart_list = []
# total price of items in cart
total = 0
items = 0
if 'cart' in request.session:
cart_list = request.session['cart']
cart_list, total, items, can_check_out = get_cart_list(cart_list)
if can_check_out:
can_check_out = "yes"
else:
can_check_out = ""
return render(
request, "cart.html", {
'username': username,
'categories': cat_dict,
'can_buy': can_check_out,
'cart': cart_list,
'total_price': total,
'total_items': items,
'items_len': cart_item_no
})
else:
return render(
request, "cart.html", {
'username': username,
'categories': cat_dict,
'cart': cart_list,
'total_price': total,
'total_items': items,
'items_len': cart_item_no
})
def load_orders(request):
if not ('customer_id' in request.session):
return render(request, 'purchases.html')
cid = request.session['customer_id']
cid = str(cid)
cart_item_no = ""
if 'cart' in request.session:
my_cart = request.session['cart']
if len(my_cart) > 0:
cart_item_no = "(" + str(len(my_cart)) + ")"
# search btn pressed
if request.method == 'POST' and 'search-btn' in request.POST:
keywords = request.POST['search']
keywords = str(keywords)
return home_views.load_search_result(request, keywords)
cursor = connection.cursor()
# gets list of purchases with product names ordered by newest bought
sql = """SELECT p.NAME, t.PRICE, t.QUANTITY, t.PAYMENT_METHOD, t.PURCHASE_DATE, t.GIFT_FOR
FROM "THE_BAZAAR".PRODUCT p, "THE_BAZAAR".TRANSACTION t
WHERE p.PRODUCT_ID = t.PRODUCT_ID AND t.CUSTOMER_ID = """ + cid + """
ORDER BY t.PURCHASE_DATE DESC;"""
cursor.execute(sql)
data_table = cursor.fetchall()
product_dict = []
dictionary = []
length = len(data_table)
for index, data in enumerate(data_table):
name = data[0]
price = data[1]
quantity = data[2]
payment_method = data[3]
purchase_date = data[4]
gift = data[5]
if gift != "NULL":
gift = "Gift To: " + gift
else:
gift = ""
if payment_method == "COD":
payment_method = "Cash on Delivery"
else:
payment_method = "Bank A/C"
row = {
'price': price,
'quantity': quantity,
'name': name,
'gift': gift,
'payment_method': payment_method,
'purchase_date': purchase_date
}
dictionary.append(row)
# This condition makes sure that purchases of the
# same date are displayed together
# if next-row exists, check if purchase date matches
# if date does not match, append the "list-of-dict" and empty it!
if index < (length - 1):
next_row = data_table[index + 1]
if purchase_date != next_row[4]:
product_dict.append(dictionary)
dictionary = []
# append the last
if index == length - 1:
product_dict.append(dictionary)
# username display
sql = "SELECT EMAIL FROM CUSTOMER WHERE CUSTOMER_ID = " + cid
cursor.execute(sql)
name_table = cursor.fetchall()
username = [data[0] for data in name_table]
username = str(username[0])
sql = """SELECT DISTINCT CATEGORY
FROM PRODUCT
WHERE PRODUCT_ID = ANY(
SELECT PRODUCT_ID
FROM STOCK
WHERE EXPIRE_DATE > SYSDATE AND QUANTITY > 0
UNION
SELECT PRODUCT_ID
FROM STOCK WHERE
EXPIRE_DATE IS NULL AND QUANTITY > 0
);"""
cursor.execute(sql)
categories = cursor.fetchall()
category_dict = []
for r in categories:
category = r[0]
row = {'category': category}
category_dict.append(row)
sql = """SELECT OFFER_ID FROM PRODUCT WHERE OFFER_ID IS NOT NULL;"""
cursor.execute(sql)
temp = cursor.fetchall()
if cursor.rowcount > 0:
row = {'category': "Discounts"}
category_dict.append(row)
return render(
request, 'purchases.html', {
'products_list': product_dict,
'username': username,
'categories': category_dict,
'items_len': cart_item_no
})
def load_profile(request):
global id
global dictionary
global phone_dict
global category_dict
global username
global phone1
global phone2
global phone3
global msg
# send to login if no session exists:
if not ('customer_id' in request.session):
return redirect(login_views.load_login)
msg = ""
keep_running_profile(request)
cart_item_no = ""
if 'cart' in request.session:
my_cart = request.session['cart']
if len(my_cart) > 0:
cart_item_no = "(" + str(len(my_cart)) + ")"
# search btn pressed
if request.method == 'POST' and 'search-btn' in request.POST:
keywords = request.POST['search']
keywords = str(keywords)
return home_views.load_search_result(request, keywords)
# EDITING PROFILE ~
if request.method == 'POST':
id = str(id)
if 'bank' in request.POST:
bank = request.POST['bank']
if len(bank) != 0:
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET BANK_ACCOUNT = '" + bank + "' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
if 'house' in request.POST:
house = request.POST['house']
if len(house) != 0:
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET HOUSE_NO = '" + house + "' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
if 'street' in request.POST:
street = request.POST['street']
if len(street) != 0:
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET STREET = '" + street + "' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
if 'postal_code' in request.POST:
postal_code = request.POST['postal_code']
if len(postal_code) != 0:
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET POSTAL_CODE = '" + postal_code + "' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
if 'city' in request.POST:
city = request.POST['city']
if len(city) != 0:
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET CITY = '" + city + "' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
if 'email' in request.POST:
email = request.POST['email']
if len(email) != 0:
cursor = connection.cursor()
sql = "SELECT EMAIL FROM CUSTOMER WHERE EMAIL = '" + email + "';"
cursor.execute(sql)
emails_ = cursor.fetchall()
email_fetched = ""
if cursor.rowcount > 0:
email_fetched = [x[0] for x in emails_]
email_fetched = str(email_fetched[0])
# email already in use given!
if email_fetched == email:
msg = "Email Already in Use"
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'items_len': cart_item_no,
'phone_number': phone_dict,
'categories': category_dict
})
else:
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET EMAIL = '" + email + "' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
if 'old_password' in request.POST:
old_password = request.POST['old_password']
if len(old_password) != 0:
old_password = hashlib.md5(
old_password.encode('utf-8')).hexdigest()
cursor = connection.cursor()
sql = "SELECT PASSWORD FROM CUSTOMER WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
passwords_ = cursor.fetchall()
password_fetched = ""
if cursor.rowcount > 0:
password_fetched = [x[0] for x in passwords_]
password_fetched = str(password_fetched[0])
if password_fetched == old_password:
if 'password' in request.POST:
password = request.POST['password']
if len(password) != 0:
password = hashlib.md5(
password.encode('utf-8')).hexdigest()
cursor = connection.cursor()
sql = "UPDATE CUSTOMER SET PASSWORD = '******' WHERE CUSTOMER_ID = '" + id + "';"
cursor.execute(sql)
cursor.close()
else:
# new password empty
msg = "New Password Not Given!"
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
else:
# old password does not match
msg = "Current Password Incorrect"
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
if 'phone' in request.POST:
phone = request.POST['phone']
phone = convert_number(phone)
if len(phone) != 0:
cursor = connection.cursor()
sql = "SELECT PHONE_NUMBER FROM CUSTOMER_PHONE WHERE PHONE_NUMBER = '" + phone + "';"
cursor.execute(sql)
number1 = cursor.fetchall()
number_fetched = -1
if cursor.rowcount > 0:
number_fetched = [x[0] for x in number1]
number_fetched = str(number_fetched[0])
if number_fetched == phone:
msg = "Phone Number Already in Use"
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
else:
phone1 = str(phone1)
cursor = connection.cursor()
sql = "DELETE FROM CUSTOMER_PHONE WHERE PHONE_NUMBER = '" + phone1 + "';"
cursor.execute(sql)
phone = str(phone)
cursor = connection.cursor()
sql = "INSERT INTO CUSTOMER_PHONE(PHONE_NUMBER,CUSTOMER_ID) VALUES(" + phone + ", " + id + ");"
cursor.execute(sql)
cursor.close()
if 'phone_2' in request.POST:
phone_2 = request.POST['phone_2']
phone_2 = convert_number(phone_2)
if len(phone_2) != 0:
cursor = connection.cursor()
sql = "SELECT PHONE_NUMBER FROM CUSTOMER_PHONE WHERE PHONE_NUMBER = '" + phone_2 + "';"
cursor.execute(sql)
number1 = cursor.fetchall()
number_fetched = -1
if cursor.rowcount > 0:
number_fetched = [x[0] for x in number1]
number_fetched = str(number_fetched[0])
if number_fetched == phone_2:
msg = "Phone Number 2 Already in Use"
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
else:
phone2 = str(phone2)
cursor = connection.cursor()
sql = "DELETE FROM CUSTOMER_PHONE WHERE PHONE_NUMBER = '" + phone2 + "';"
cursor.execute(sql)
phone_2 = str(phone_2)
cursor = connection.cursor()
sql = "INSERT INTO CUSTOMER_PHONE(PHONE_NUMBER,CUSTOMER_ID) VALUES(" + phone_2 + ", " + id + ");"
cursor.execute(sql)
cursor.close()
if 'phone_3' in request.POST:
phone_3 = request.POST['phone_3']
phone_3 = convert_number(phone_3)
if len(phone_3) != 0:
cursor = connection.cursor()
sql = "SELECT PHONE_NUMBER FROM CUSTOMER_PHONE WHERE PHONE_NUMBER = '" + phone_3 + "';"
cursor.execute(sql)
number1 = cursor.fetchall()
number_fetched = -1
if cursor.rowcount > 0:
number_fetched = [x[0] for x in number1]
number_fetched = str(number_fetched[0])
if number_fetched == phone_3:
msg = "Phone Number 3 Already in Use"
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
else:
phone3 = str(phone3)
cursor = connection.cursor()
sql = "DELETE FROM CUSTOMER_PHONE WHERE PHONE_NUMBER = '" + phone3 + "';"
cursor.execute(sql)
phone_3 = str(phone_3)
cursor = connection.cursor()
sql = "INSERT INTO CUSTOMER_PHONE(PHONE_NUMBER,CUSTOMER_ID) VALUES(" + phone_3 + ", " + id + ");"
cursor.execute(sql)
cursor.close()
keep_running_profile(request) # reloads profile after changes made
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
else:
return render(
request, 'myProfile.html', {
'dictionary': dictionary,
'username': username,
'err': msg,
'phone_number': phone_dict,
'categories': category_dict,
'items_len': cart_item_no
})
def load_category(request, slug):
# send to login if no session exists:
if not ('customer_id' in request.session):
return redirect(load_login)
cart_item_no = ""
if 'cart' in request.session:
my_cart = request.session['cart']
if len(my_cart) > 0:
cart_item_no = "(" + str(len(my_cart)) + ")"
if request.method == 'POST':
keywords = request.POST['search']
keywords = str(keywords)
return homeviews.load_search_result(request, keywords)
categ = slug
current_category = {'category': categ + ""}
# Getting categories of products:
cursor = connection.cursor()
sql = """SELECT DISTINCT CATEGORY
FROM PRODUCT
WHERE PRODUCT_ID = ANY(
SELECT PRODUCT_ID
FROM STOCK
WHERE EXPIRE_DATE > SYSDATE AND QUANTITY > 0
UNION
SELECT PRODUCT_ID
FROM STOCK WHERE
EXPIRE_DATE IS NULL AND QUANTITY > 0
);"""
cursor.execute(sql)
categories = cursor.fetchall()
category_dict = []
for r in categories:
category = r[0]
row = {'category': category}
category_dict.append(row)
sql = """SELECT OFFER_ID FROM PRODUCT WHERE OFFER_ID IS NOT NULL;"""
cursor.execute(sql)
temp = cursor.fetchall()
if cursor.rowcount > 0:
row = {'category': "Discounts"}
category_dict.append(row)
# Getting products list according to category which are in stock:
sql = ""
if categ == "Discounts":
sql = """SELECT NAME, BRAND, PRICE, IMAGE_LINK, PRODUCT_ID, o.DESCRIPTION
FROM THE_BAZAAR.PRODUCT p, THE_BAZAAR.OFFER o
WHERE p.PRODUCT_ID =
ANY(
SELECT PRODUCT_ID
FROM THE_BAZAAR.STOCK
WHERE EXPIRE_DATE > SYSDATE AND QUANTITY > 0
UNION
SELECT PRODUCT_ID
FROM THE_BAZAAR.STOCK WHERE
EXPIRE_DATE IS NULL AND QUANTITY > 0
)
AND p.OFFER_ID = o.OFFER_ID(+)
AND p.OFFER_ID IS NOT NULL;"""
else:
sql = """SELECT NAME, BRAND, PRICE, IMAGE_LINK, PRODUCT_ID, o.DESCRIPTION
FROM THE_BAZAAR.PRODUCT p, THE_BAZAAR.OFFER o
WHERE p.PRODUCT_ID =
ANY(
SELECT PRODUCT_ID
FROM THE_BAZAAR.STOCK
WHERE EXPIRE_DATE > SYSDATE AND QUANTITY > 0
UNION
SELECT PRODUCT_ID
FROM THE_BAZAAR.STOCK WHERE
EXPIRE_DATE IS NULL AND QUANTITY > 0
)
AND p.OFFER_ID = o.OFFER_ID(+)
AND CATEGORY = '""" + categ + """';"""
cursor.execute(sql)
product_list = cursor.fetchall()
product_dict = []
for r in product_list:
name = r[0]
brand = r[1]
price = r[2]
image_link = r[3]
id = r[4]
discount = r[5]
row = {
'name': name,
'brand': brand,
'price': price,
'image_link': image_link,
'id': id,
'discount': discount
}
product_dict.append(row)
# Dividing product_dict returned into 9 items per page of website
# /?page=2 is the url of second page
paginate = Paginator(product_dict, 9)
page = request.GET.get('page')
product_dict = paginate.get_page(page)
customer_id = request.session.get('customer_id')
customer_id = str(customer_id)
sql = "SELECT EMAIL FROM CUSTOMER WHERE CUSTOMER_ID = " + customer_id
cursor.execute(sql)
name_table = cursor.fetchall()
username = [data[0] for data in name_table]
username = str(username[0])
cursor.close()
# return render(request,'list_jobs.html',{'jobs' : Job.objects.all()})
return render(
request, 'category.html', {
'username': username,
'categories': category_dict,
'products': product_dict,
'current_category': current_category,
'items_len': cart_item_no
})