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")