def department_edit_main():
command = """SELECT {departments}.dept_id, {departments}.dept_name, {departments}.warranty, {departments}.dept_phone, {departments}.dept_manager
FROM {departments}
""".format(departments="departments")
cursor.execute(command)
departments_data = cursor.fetchall()
return render_template('departmentEdit.html',
URL=url_for('department_edit_main'),
departments_data=departments_data)
def productsPrinter():
command = """SELECT {products}.prod_id, {products}.prod_name, {products}.dept_id,
{products}.prod_price, {products}.prod_stock, {products}.prod_rating, {products}.prod_images, {products}.shipping, {products}.recycle_fee
FROM {products} JOIN {departments} ON {products}.dept_id = {departments}.dept_id
""".format(products = 'products', departments = 'departments')
cursor.execute(command)
products_data = cursor.fetchall()
return render_template("products.html", products_data = products_data, URL = url_for('productsPrinter'))
def get_message_dept(ID):
command = """SELECT {departments}.dept_id, {departments}.dept_name, {departments}.warranty, {departments}.dept_phone, {departments}.dept_manager
FROM {departments} WHERE {departments}.dept_id = {num_id}
""".format(departments="departments", num_id=ID)
cursor.execute(command)
departments_data = cursor.fetchall()
return render_template('departmentShower.html',
ID=ID,
URL=url_for('get_message', ID=ID),
departments_data=departments_data)
def get_message(ID):
command = """SELECT {p}.prod_id, {p}.prod_name, {p}.dept_id, {p}.prod_price,
{p}.prod_stock, {p}.prod_rating, {p}.prod_images, {p}.shipping, {p}.recycle_fee
FROM {p} JOIN {d} ON {p}.dept_id = {d}.dept_id
WHERE {p}.prod_id = {num_id}
""".format(p = 'products', d = 'departments', num_id = ID)
cursor.execute(command)
product_data = cursor.fetchall()
return render_template('product.html', ID = ID, URL = url_for('get_message', ID = ID), product_data = product_data)
def AdvSearch():
form = AdvancedSearch(request.form)
#command = """ SELECT {departments}.dept_id, {departments}.dept_name
# FROM departments
#""".format(departments = 'departments')#changed a bit for CHOICES
#cursor.execute(command)
#departments = cursor.fetchall()
command = """ SELECT {departments}.dept_id, {departments}.dept_name
FROM departments
""".format(departments = 'departments')#changed a bit for CHOICES
cursor.execute(command)
departments = cursor.fetchall()
form.dept_id.choices = form.dept_id.choices + departments
return render_template('advanced_search.html', URL = url_for('AdvSearch'), form = form)
def department_edit(key):
command = """SELECT *
FROM departments
WHERE dept_id = {p1}
""".format(p1=key)
cursor.execute(command)
single_department = cursor.fetchall()[
0] #gets all the information on the product with id = <key>
form = DepartmentForm(request.form,
csrf_enabled=False,
dept_name=single_department[1],
warranty=single_department[2],
dept_phone=single_department[3],
dept_manager=single_department[4])
if (request.method == 'POST' and form.validate()):
dept_name = form.dept_name.data
warranty = form.warranty.data
dept_phone = form.dept_phone.data
dept_manager = form.dept_manager.data
command = """
UPDATE departments
SET dept_name = '{name}', warranty = '{warranty}', dept_phone = '{phone}',
dept_manager = '{manager}'
WHERE departments.dept_id = {id}
""".format(departments='departments',
id=key,
name=dept_name,
warranty=warranty,
phone=dept_phone,
manager=dept_manager)
cursor.execute(command)
conn.commit()
flash('The department %s has been edited.' % (dept_name), 'success')
return redirect(url_for('department_edit_main'))
if form.errors:
flash(form.errors, 'danger')
return render_template('department_edit.html', form=form,
dept_id=key) #not sure about here
def department_delete(key):
command = """ SELECT *
FROM departments
WHERE departments.dept_id = {p1}
""".format(p1=key) #select whole row of <key>
cursor.execute(command)
single_row = cursor.fetchall()
dept_name = single_row #gets the name from row 0 column 1
command = """ DELETE FROM departments
WHERE departments.dept_id = {p1}
""".format(p1=key)
cursor.execute(command)
conn.commit()
flash('The product %s has been deleted' % (dept_name), 'success')
return redirect(url_for('department_delete_main'))
def product_edit(key):
command = """SELECT *
FROM products
WHERE prod_id = {p1}
""".format(p1=key)
cursor.execute(command)
single_product = cursor.fetchall()[
0] #gets all the information on the product with id = <key>
form = ProductForm(request.form,
csrf_enabled=False,
prod_name=single_product[1],
dept_id=single_product[2],
prod_price=single_product[3],
prod_stock=single_product[4],
prod_rating=single_product[5],
prod_images=single_product[6],
shipping=single_product[7],
recycle_fee=single_product[8])
command = """SELECT {departments}.dept_id, {departments}.dept_name
FROM departments
""".format(
departments='departments') #might run into same problem as prod_create
cursor.execute(command)
departments = cursor.fetchall()
form.dept_id.choices = departments
if (request.method == 'POST' and form.validate()):
prod_name = form.prod_name.data
dept_id = form.dept_id.data
prod_price = form.prod_price.data
prod_stock = form.prod_stock.data
prod_rating = form.prod_rating.data
prod_images = form.prod_images.data
#These have to be if statements because form.shipping.data returns a bool, which SQLite doesn't accept. It works with 1 and 0
if (form.shipping.data):
shipping = 1
else:
shipping = 0
if (form.recycle_fee.data):
recycle_fee = 1
else:
recycle_fee = 0
command = """
UPDATE products
SET prod_name = '{name}', dept_id = '{dept}', prod_price = '{price}',
prod_stock = '{stock}', prod_rating = '{rating}', prod_images = '{images}',
shipping = '{ship}', recycle_fee = '{fee}'
WHERE products.prod_id = {id}
""".format(products='products',
id=key,
name=prod_name,
dept=dept_id,
price=prod_price,
stock=prod_stock,
rating=prod_rating,
images=prod_images,
ship=shipping,
fee=recycle_fee)
#not sure if command WHERE should be dept_id or prod_id
cursor.execute(command)
conn.commit()
flash('The product %s has been edited.' % (prod_name), 'success')
return redirect(url_for('get_message', ID=key))
if form.errors:
flash(form.errors, 'danger')
return render_template('product_edit.html', form=form, prod_id=key)
def product_create():
command = """SELECT MAX(prod_id)
FROM products
"""
cursor.execute(command)
next_id = cursor.fetchone()
new_id = next_id[0] + 1
form = ProductForm(
request.form,
csrf_enabled=False) #not sure if to have CSRF enabled or not
command = """ SELECT {departments}.dept_id, {departments}.dept_name
FROM departments
""".format(departments='departments') #changed a bit for CHOICES
cursor.execute(command)
departments = cursor.fetchall()
form.dept_id.choices = departments # NOT 100% on what this line does
if (request.method == 'POST' and form.validate()):
prod_name = form.prod_name.data
dept_id = form.dept_id.data
prod_price = form.prod_price.data
prod_stock = form.prod_stock.data
prod_rating = form.prod_rating.data
prod_images = form.prod_images.data
#These have to be if statements because form.shipping.data returns a bool, which SQLite doesn't accept. It works with 1 and 0
if (form.shipping.data):
shipping = 1
else:
shipping = 0
if (form.recycle_fee.data):
recycle_fee = 1
else:
recycle_fee = 0
command = """ INSERT INTO products
(prod_id, prod_name, dept_id, prod_price, prod_stock, prod_rating, prod_images, shipping, recycle_fee)
VALUES
('{id}','{name}',{dept}, {price}, {stock},{rating}, '{images}', {ship}, {fee})
""".format(
id=new_id,
name=prod_name,
dept=dept_id,
price=prod_price,
stock=prod_stock,
rating=prod_rating,
images=prod_images,
ship=shipping,
fee=recycle_fee
) #you had shipping = shipping here. It should have been ship=shipping
cursor.execute(command)
conn.commit()
flash("The product %s with id %d has been created" %
(prod_name, new_id))
return redirect(url_for('get_message', ID=new_id))
if form.errors:
flash(form.errors, 'danger')
return render_template('product_create.html',
URL=url_for('product_create'),
form=form,
prod_id=new_id)
def searchingFunction():
if request.method == "POST":
name = request.form['name']
condition = ""
if name != None:
condition += "products.prod_name LIKE '%" + (name) + "%'"
if condition == "":
return ('', 204)
else:
command = """SELECT {products}.prod_id, {products}.prod_name, {products}.dept_id,
{products}.prod_price, {products}.prod_stock, {products}.prod_rating, {products}.prod_images, {products}.shipping, {products}.recycle_fee
FROM {products} JOIN {departments} ON {products}.dept_id = {departments}.dept_id
WHERE {cond}
""".format(products='products',
departments='departments',
cond=condition)
cursor.execute(command)
products_data = cursor.fetchall()
return render_template("products.html",
products_data=products_data,
URL=url_for('searchingFunction'),
condition=condition)
if request.method == "GET":
name = request.args.get('name')
price = request.args.get('price')
stock = request.args.get('stock')
rating = request.args.get('rating')
shipping = request.args.get('shipping')
no_recycle = request.args.get('no_recycle_fee')
department = request.args.get('dept_id')
price_greater_equal = request.args.get('price_ge')
price_smaller_equal = request.args.get('price_se')
rating_greater_equal = request.args.get('prod_rating_ge')
rating_smaller_equal = request.args.get('prod_rating_se')
stock_smaller_equal = request.args.get('stock_se')
stock_greater_equal = request.args.get('stock_ge')
condition = ""
if name != None:
condition += "products.prod_name LIKE '%" + (name) + "%'"
if price != None:
if condition != "":
condition += " AND "
condition += "products.prod_price LIKE '%" + str(price) + "%'"
if stock != None:
if condition != "":
condition += " AND "
condition += "products.prod_stock LIKE '%" + str(stock) + "%'"
if rating != None:
if condition != "":
condition += " AND "
condition += "products.prod_rating LIKE '%" + str(rating) + "%'"
if shipping != None:
if condition != "":
condition += " AND "
shipping.lower()
if shipping == "y" or shipping == "yes" or shipping == "true":
condition += "products.shipping = 1"
elif shipping == "n" or shipping == "no" or shipping == "false":
condition += "products.shipping = 0"
else:
condition += ""
if no_recycle != None:
if condition != "":
condition += " AND "
no_recycle.lower()
if no_recycle == "n" or no_recycle == "no" or no_recycle == "false":
condition += "products.recycle_fee = 1"
elif no_recycle == "y" or no_recycle == "yes" or no_recycle == "true":
condition += "products.recycle_fee = 0"
else:
condition += ""
if department != '0':
if condition != "":
condition += " AND "
condition += "products.dept_id = " + department
if price_greater_equal != "":
if condition != "":
condition += " AND "
condition += "products.prod_price >= " + price_greater_equal
if price_smaller_equal != "":
if condition != "":
condition += " AND "
condition += "products.prod_price <= " + price_smaller_equal
if rating_greater_equal != "":
if condition != "":
condition += " AND "
condition += "products.prod_rating >= " + rating_greater_equal
if rating_smaller_equal != "":
if condition != "":
condition += " AND "
#if rating_smaller_equal == "":
# condition += ""
condition += "products.prod_rating <= " + str(rating_smaller_equal)
if stock_greater_equal != None:
if condition != "":
condition += " AND "
condition += "products.prod_stock >= " + stock_greater_equal
if stock_smaller_equal != None:
if condition != "":
condition += " AND "
condition += "products.prod_stock <= " + stock_smaller_equal
if condition == "":
return ('', 204)
else:
command = """SELECT {products}.prod_id, {products}.prod_name, {products}.dept_id,
{products}.prod_price, {products}.prod_stock, {products}.prod_rating, {products}.prod_images, {products}.shipping, {products}.recycle_fee
FROM {products} JOIN {departments} ON {products}.dept_id = {departments}.dept_id
WHERE {cond}
""".format(products='products',
departments='departments',
cond=condition)
cursor.execute(command)
products_data = cursor.fetchall()
return render_template("products.html",
products_data=products_data,
URL=url_for('searchingFunction'),
condition=condition)
