def add_item():
conn = get_db()
c = conn.cursor()
form = NewItemForm()
c.execute("SELECT id, name FROM categories")
categories = c.fetchall()
form.category.choices = categories
c.execute(
"""SELECT id, name FROM subcategories
WHERE category_id = ?""", (1, ))
subcategories = c.fetchall()
form.subcategory.choices = subcategories
if form.validate_on_submit() and form.image.validate(
form, extra_validators=(FileRequired(), )):
filename = save_image_upload(form.image)
c.execute(
"""INSERT INTO items
(title, description, price, image, category_id, subcategory_id)
VALUES (?, ?, ?, ?, ?, ?)""",
(form.title.data, form.description.data, float(form.price.data),
filename, form.category.data, form.subcategory.data))
conn.commit()
flash(f"Item {form.title.data} has been successfully submitted.",
"success")
return redirect(url_for("main.home"))
return render_template("new_item.html", form=form)
def item(item_id):
c = get_db().cursor()
c.execute(
"""SELECT
i.id, i.title, i.description, i.price, i.image, c.name, s.name
FROM
items AS i
INNER JOIN categories AS c ON i.category_id = c.id
INNER JOIN subcategories AS s ON i.subcategory_id = s.id
WHERE i.id = ?""", (item_id, ))
row = c.fetchone()
try:
good = {
"id": row[0],
"title": row[1],
"description": row[2],
"price": row[3],
"image": row[4],
"category": row[5],
"subcategory": row[6]
}
except IndexError:
good = {}
if good:
delete_item_form = DeleteItemForm()
return render_template("item.html",
item=good,
deleteItemForm=delete_item_form)
return redirect(url_for('main.home'))
def edit_item(item_id):
conn = get_db()
c = conn.cursor()
c.execute("SELECT * FROM items WHERE id=?", (item_id, ))
row = c.fetchone()
try:
good = {
"id": row[0],
"title": row[1],
"description": row[2],
"price": row[3],
"image": row[4],
}
except IndexError:
good = {}
if good:
form = EditItemForm()
if form.validate_on_submit():
filename = good["image"]
if form.image.data:
filename = save_image_upload(form.image)
c.execute(
"""UPDATE items SET
title = ?, description = ?, price = ?, image = ?
WHERE id = ?""",
(form.title.data, form.description.data, float(
form.price.data), filename, item_id))
conn.commit()
flash(f"Item {form.title.data} has been successfully updated.",
"success")
return redirect(url_for("item.item", item_id=item_id))
form.title.data = good["title"]
form.description.data = good["description"]
form.price.data = good["price"]
return render_template("edit_item.html", form=form, item=good)
def delete_item(item_id):
conn = get_db()
c = conn.cursor()
c.execute("SELECT * FROM items WHERE id=?", (item_id, ))
row = c.fetchone()
try:
good = {
"id": row[0],
"title": row[1],
}
except IndexError:
good = {}
if good:
c.execute("DELETE FROM items WHERE id = ?", (item_id, ))
conn.commit()
flash(f"Item {good['title']} has been successfully deleted.",
"success")
else:
flash(f"This item does not exist", "danger")
return redirect(url_for("main.home"))
def index():
conn = get_db()
c = conn.cursor()
form = FilterForm(request.args, meta={'csrf': False})
c.execute('SELECT id, name FROM category')
categories = c.fetchall()
form.category.choices += categories
c.execute('SELECT id, name FROM subcategory WHERE category_id = ?', (1, ))
subcategories = c.fetchall()
form.subcategory.choices += subcategories
query = '''SELECT
i.id, i.title, i.description, i.price, i.image, c.name, s.name
FROM
item AS i
INNER JOIN category AS c ON i.category_id = c.id
INNER JOIN subcategory AS s ON i.subcategory_id = s.id
'''
filter_queries = []
parameters = []
if form.title.data.strip():
filter_queries.append('i.title LIKE ?')
parameters.append(f'%{form.title.data.strip()}%')
if form.description.data.strip():
filter_queries.append('i.description LIKE ?')
parameters.append(f'%{form.description.data.strip()}%')
if form.category.data:
filter_queries.append('i.category_id = ?')
parameters.append(form.category.data)
if form.subcategory.data:
filter_queries.append('i.subcategory_id = ?')
parameters.append(form.subcategory.data)
if filter_queries:
query += ' WHERE '
query += ' AND '.join(filter_queries)
if form.price.data:
if form.price.data == 1:
query += ' ORDER BY i.price DESC'
else:
query += ' ORDER BY i.price'
else:
query += ' ORDER BY i.id DESC'
items_from_db = c.execute(query, tuple(parameters))
items = []
for row in items_from_db:
print(row)
item = {
'id': row[0],
'title': row[1],
'description': row[2],
'price': row[3],
'image': row[4],
'category': row[5],
'subcategory': row[6],
}
items.append(item)
print(query)
print(items)
return render_template('index.html', items=items, form=form)
def home():
conn = get_db()
c = conn.cursor()
form = FilterForm(request.args, meta={"csrf": False})
c.execute("SELECT id, name FROM categories")
categories = c.fetchall()
categories.insert(0, (0, "---"))
form.category.choices = categories
c.execute("SELECT id, name FROM subcategories WHERE category_id = ?",
(1, ))
subcategories = c.fetchall()
subcategories.insert(0, (0, "---"))
form.subcategory.choices = subcategories
query = """SELECT
i.id, i.title, i.description, i.price, i.image, c.name, s.name
FROM
items AS i
INNER JOIN categories AS c ON i.category_id = c.id
INNER JOIN subcategories AS s ON i.subcategory_id = s.id
"""
if form.validate():
filter_queries = []
parameters = []
if form.title.data.strip():
filter_queries.append("i.title LIKE ?")
parameters.append(f"%{form.title.data}%")
if form.category.data:
filter_queries.append("i.category_id = ?")
parameters.append(form.category.data)
if form.subcategory.data:
filter_queries.append("i.subcategory_id = ?")
parameters.append(form.subcategory.data)
if filter_queries:
query += " WHERE "
query += " AND ".join(filter_queries)
if form.price.data:
if form.price.data == 1:
query += " ORDER BY i.price DESC"
else:
query += " ORDER BY i.price"
else:
query += " ORDER BY i.id DESC"
items_from_db = c.execute(query, tuple(parameters))
else:
items_from_db = c.execute(query + " ORDER BY i.id DESC")
items = []
for row in items_from_db:
item = {
"id": row[0],
"title": row[1],
"description": row[2],
"price": row[3],
"image": row[4],
"category": row[5],
"subcategory": row[6]
}
items.append(item)
return render_template('home.html', items=items, form=form)