def add_item():
form = CategoryForm()
conn = get_db()
c = conn.cursor()
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
form.subcategory.data = int(form.subcategory.raw_data[0])
# TODO fix problem with subctegories valid choices
if form.validate_on_submit():
filename = ""
if form.image.data:
filename = save_image_upload(form.image.data)
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 added.",
"success")
return redirect(url_for('main.index'))
return render_template('add.html', form=form)
def delete_item(item_id):
conn = get_db()
c = conn.cursor()
item_from_db = c.execute("""SELECT * FROM items WHERE id = ?""",
(item_id, ))
row = c.fetchone()
try:
item = {
"id": row[0],
"title": row[1],
"description": row[2],
"price": row[3],
'image': row[4]
}
except TypeError:
item = {}
if item:
c.execute("""DELETE FROM items WHERE id = ?""", (item_id, ))
conn.commit()
flash(f"Item {item['title']} has been successfully removed.",
"success")
else:
flash(f"This item does not exist", "danger")
return redirect(url_for('main.index'))
def get_subcategories(category_id):
conn = get_db()
c = conn.cursor()
c.execute(
"""SELECT id, name FROM subcategories WHERE category_id = ?
""", (category_id, ))
subcategories = c.fetchall()
return jsonify(subcategories=subcategories)
def edit_item(item_id):
conn = get_db()
c = conn.cursor()
item_from_db = c.execute("""SELECT * FROM items WHERE id = ?""",
(item_id, ))
row = c.fetchone()
try:
item = {
"id": row[0],
"title": row[1],
"description": row[2],
"price": row[3],
'image': row[4]
}
except TypeError:
item = {}
if item:
form = EditItemForm()
if form.validate_on_submit():
filename = item['image']
if form.image.data:
filename = save_image_upload(form.image.data)
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 edited.",
"success")
return redirect(url_for('items.get_item', item_id=item_id))
form.title.data = item["title"]
form.description.data = unescape(item["description"])
form.price.data = item["price"]
if form.errors:
flash("{form.error}", "danger")
return render_template('edit.html', form=form, item=item)
return redirect(url_for("main.index"))
def add_comment():
conn = get_db()
c = conn.cursor()
form = NewCommentForm()
is_ajax = True
if form.validate_on_submit():
c.execute("""INSERT INTO comments (content, item_id) VALUES (?, ?)""",
(escape(form.content.data), form.item_id.data))
conn.commit()
if is_ajax:
return render_template("/_comment.html", content=form.content.data)
return redirect(url_for("items.get_item", item_id=form.item_id.data))
def get_item(item_id):
removeItemForm = RemoveItemForm()
c = get_db().cursor()
row = []
item_from_db = 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:
item = {
"id": row[0],
"title": row[1],
"description": row[2],
"price": row[3],
'image': row[4],
"category": row[5],
"subcategory": row[6]
}
except TypeError:
item = {}
comments = []
if item:
comments_from_db = c.execute(
"""SELECT content FROM comments
WHERE item_id = ?
ORDER BY id DESC
""", (item_id, ))
comments = [{"content": row[0]} for row in comments_from_db]
commentForm = NewCommentForm()
commentForm.item_id.data = item_id
return render_template('/item.html',
item=item,
removeItemForm=removeItemForm,
commentForm=commentForm,
comments=comments)
def index():
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""")
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 ?' if not form.adv_filter.data
else "(i.title LIKE ? OR i.description LIKE ?)")
parameters.append(f"%{escape(form.title.data)}%")
if form.adv_filter.data:
parameters.append(f"%{escape(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("index.html", items=items, form=form)