def search_recipes(name, type_, checked=None):
d = {
'nazwa': 'r.name',
'typ': 'tf.name',
'kategoria': 'fc.name',
}
sql = """
SELECT r.recipe_id,
r.user_id, u.username,
r.type_of_food_id, tf.name AS type_of_food,
r.food_category_id, fc.name AS food_category,
r.name, r.creation_date, r.description, r.rating, r.checked, r.time_required, r.views
FROM recipes r
LEFT JOIN users u USING(user_id)
INNER JOIN types_of_food tf USING(type_of_food_id)
INNER JOIN food_categories fc USING(food_category_id)
WHERE {} LIKE '%{}%'
""".format(d[type_], name)
if checked is True:
return query_db_object(
sql + " AND checked = 1 ORDER BY r.creation_date DESC")
elif checked is False:
return query_db_object(
sql + " AND checked = 0 ORDER BY r.creation_date DESC")
else:
return query_db_object(sql + " ORDER BY r.creation_date DESC")
def get_all_users():
return query_db_object("""
SELECT u.user_id, u.username, u.creation_date, u.last_login_date,
u.role_id, r.name AS role,
u.cooking_level_id, cl.name AS cooking_level,
u.sex
FROM users u
INNER JOIN roles r USING (role_id)
LEFT JOIN cooking_levels cl USING (cooking_level_id)
""")
def get_recipes_list(checked=None):
sql = """
SELECT r.recipe_id,
r.user_id, u.username,
r.type_of_food_id, tf.name AS type_of_food,
r.food_category_id, fc.name AS food_category,
r.name, r.creation_date, r.description, r.rating, r.checked, r.time_required, r.views
FROM recipes r
LEFT JOIN users u USING(user_id)
INNER JOIN types_of_food tf USING(type_of_food_id)
INNER JOIN food_categories fc USING(food_category_id)
"""
if checked is True:
return query_db_object(
sql + " WHERE checked = 1 ORDER BY r.creation_date DESC")
elif checked is False:
return query_db_object(
sql + " WHERE checked = 0 ORDER BY r.creation_date DESC")
else:
return query_db_object(sql + " ORDER BY r.creation_date DESC")
def add_user_to_session(username):
query_db("UPDATE users SET last_login_date = DATETIME('now', 'localtime') WHERE username = ?",
username, commit=True)
row = query_db_object("""
SELECT u.user_id, u.username, r.name AS role_name, u.creation_date, u.last_login_date, u.sex
FROM users u
INNER JOIN roles r USING(role_id)
WHERE username = ?
""", username, one=True)
session.permanent = True
session['user'] = row
def get_recipe(recipe_id):
return query_db_object("""
SELECT r.recipe_id,
r.user_id, u.username,
r.type_of_food_id, tf.name AS type_of_food,
r.food_category_id, fc.name AS food_category,
r.name, r.creation_date, r.description, r.rating, r.checked, r.time_required, r.views
FROM recipes r
LEFT JOIN users u USING(user_id)
INNER JOIN types_of_food tf USING(type_of_food_id)
INNER JOIN food_categories fc USING(food_category_id)
WHERE r.recipe_id = ?
""",
recipe_id,
one=True)
def get_types_of_food_list():
return query_db_object(
"SELECT type_of_food_id, name FROM types_of_food ORDER BY name")
def get_cooking_levels():
return query_db_object("SELECT cooking_level_id, name, description FROM cooking_levels")