def delete_grocery_list(list_id: int) -> None:
grocery_list: GroceryList = get_grocery_list_by_id(list_id)
if grocery_list:
query_db('delete from grocery_lists where id=%s', [list_id])
return
else:
raise GroceryListNotFoundException(list_id)
def delete_grocery_item_from_list(list_id: int, item_id: int) -> GroceryList:
grocery_items_rows = query_db(
'select * from grocery_items where list_id=%s and id=%s',
[list_id, item_id])
if len(grocery_items_rows) == 1:
query_db('delete from grocery_items where id=%s and list_id=%s',
[item_id, list_id])
return get_grocery_list_by_id(list_id)
def get_grocery_list_by_id(list_id: int) -> GroceryList:
grocery_list_rows = query_db('select * from grocery_lists where id=%s',
[list_id])
if len(grocery_list_rows) != 1:
raise GroceryListNotFoundException(list_id)
grocery_items_rows = query_db(
'select * from grocery_items where list_id=%s', [list_id])
return _grocery_list_from_row(grocery_list_rows[0], grocery_items_rows)
def get_user(id):
maybe_professionals = query_db('SELECT * FROM professionals WHERE id = ?', [id], one=True)
if maybe_professionals is None:
return {}, status.HTTP_404_NOT_FOUND
else:
maybe_badges = query_db('SELECT * FROM badges WHERE professional_id = ?', [id])
maybe_professionals["badges"] = maybe_badges
return maybe_professionals
def new_recipe(user_id, name, type_of_food, food_category, description, time):
query_db("""
INSERT INTO recipes (user_id, type_of_food_id, food_category_id, name, description, time_required)
VALUES (?, ?, ?, ?, ?, ?)
""",
user_id,
type_of_food,
food_category,
name,
description,
time,
commit=True)
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 download_checkpoint(checkpoint: int):
"""
Downloads a zip of model .pt files.
:param checkpoint: The name of the checkpoint to download.
"""
ckpt = db.query_db(f'SELECT * FROM ckpt WHERE id = {checkpoint}', one = True)
models = db.get_models(checkpoint)
model_data = io.BytesIO()
with zipfile.ZipFile(model_data, mode='w') as z:
for model in models:
model_path = os.path.join(app.config['CHECKPOINT_FOLDER'], f'{model["id"]}.pt')
z.write(model_path, os.path.basename(model_path))
model_data.seek(0)
return send_file(
model_data,
mimetype='application/zip',
as_attachment=True,
attachment_filename=f'{ckpt["ckpt_name"]}.zip',
cache_timeout=-1
)
def get_user(id):
maybe_consumer = query_db('SELECT * FROM consumers WHERE id = ?', [id], one=True)
if maybe_consumer is None:
return {}, status.HTTP_404_NOT_FOUND
else:
return maybe_consumer
def create_user(login, password):
if query_db('select count(*) from users where login=?', [login])[0][0] > 0:
return None
if login in app.config['FAKE_USERS']:
return None
directory = ''.join([random.choice(string.ascii_lowercase) for _ in range(10)])
query_db_and_commit('insert into users (login, password, directory) values (?, ?, ?)', [login, password, directory])
return User(login, password, directory)
def assign_pioneer_badge():
badge_name = "pioneer"
badge_description = "One of the first 100 professionals with at least one confirmed appointment"
pioneers = query_db('SELECT DISTINCT professional_id FROM appointments WHERE professional_scheduled is NOT NULL AND consumer_accepted is NOT NULL ORDER BY consumer_accepted asc', [])
update_badge_table(badge_name, badge_description, pioneers)
return pioneers
def assign_hero_badge():
badge_name = "hero"
badge_description = "Professional who spend more than 2 hours helping people in need."
heroes = query_db('SELECT professional_id, SUM(appointment_duration) as total FROM appointments GROUP BY professional_id HAVING total >= 120', [])
update_badge_table(badge_name, badge_description, heroes)
return heroes
def list_scheduled(pid):
maybe_requests = query_db(
'SELECT appointments.id AS appointments_id, consumer_id, professional_id, consumers.username, requested, appointment_date, appointment_duration, consumer_accepted, consumer_resigned FROM appointments JOIN consumers ON ('
'consumers.id = consumer_id) WHERE professional_declined is NULL AND professional_scheduled '
'is NOT NULL AND professional_id =?', [pid])
if not maybe_requests:
return {}, status.HTTP_404_NOT_FOUND
else:
return maybe_requests
def list_availability_requests(pid):
maybe_requests = query_db(
'SELECT appointments.id AS appointments_id, consumer_id, consumers.username, requested FROM appointments JOIN '
'consumers ON ( consumers.id = consumer_id) WHERE professional_declined is NULL AND professional_scheduled '
'is NULL AND professional_id =?', [pid])
if not maybe_requests:
return {}, status.HTTP_404_NOT_FOUND
else:
return maybe_requests
def list_requested_declined(cid):
maybe_requests = query_db(
'SELECT appointments.id AS appointments_id, professional_id, requested, professionals.fullname, professionals.qualifications, professionals.profession, professional_declined FROM appointments JOIN professionals ON ('
'professionals.id = professional_id) WHERE professional_declined is NOT NULL AND professional_scheduled '
'is NULL AND consumer_id =?', [cid])
if not maybe_requests:
return {}, status.HTTP_404_NOT_FOUND
else:
return maybe_requests
def get_bookmarks(id):
maybe_bookmarks = query_db('SELECT professionals.id, fullname, qualifications, profession, specialties, languages '
'FROM professionals JOIN professionals_bookmarks ON (professionals.id = '
'professional_id) '
'WHERE professionals_bookmarks.consumer_id = ?', [id])
if not maybe_bookmarks:
return {}, status.HTTP_404_NOT_FOUND
else:
return maybe_bookmarks
def search():
# payload expected as x-www-form-urlencoded
# accepts a list of words separated with whitespaces, commas etc
# takes into consideration only 5 first words
# two first words are most important
# not-secure for demo only
criteria = create_criteria_list(request.form['s'])
term = create_matching_term(criteria)
matching_profiles = query_db('SELECT DISTINCT professional_id, fullname, qualifications, profession '
'FROM profile_search WHERE profile MATCH ? ORDER BY rank', [term])
return matching_profiles
def update_bookmarks(id, professional_id):
maybe_bookmark = query_db('SELECT professional_id FROM professionals_bookmarks WHERE consumer_id = ? AND '
'professional_id = ? ', [id, professional_id])
# if bookmark exists remove it, else create it
if not maybe_bookmark:
r = update_db('INSERT INTO professionals_bookmarks (consumer_id, professional_id) values (?, ?)',
[id, professional_id])
return {}, status.HTTP_202_ACCEPTED
else:
update_db('DELETE FROM professionals_bookmarks WHERE consumer_id = ? AND professional_id = ?',
[id, professional_id])
return {}, status.HTTP_202_ACCEPTED
def assign_hero_of_the_week(week, year):
badge_name = f"hero of the week {week}"
badge_description = "f10 professionals who spend most time helping people in need in week {week}/{year}."
# libraries star counting weeks from 0 - this needs a bit more investigation
c_week = week - 1
week_start_date = datetime.datetime.strptime(f'{year}-{c_week}-1', "%Y-%W-%w").date()
week_end_date = week_start_date + datetime.timedelta(days=7)
heroes_of_the_week = query_db('SELECT professional_id, appointment_date, SUM(appointment_duration) as total FROM appointments WHERE appointment_date >=date(?) and appointment_date <= date(?) GROUP BY professional_id ORDER BY total DESC LIMIT 10',
[week_start_date.isoformat(), week_end_date.isoformat()])
update_badge_table(badge_name, badge_description, heroes_of_the_week)
return heroes_of_the_week
def get(user_id):
result = query_db('select password, directory from users where login=?', [user_id])
if len(result) != 1:
return None
return User(user_id, result[0][0], result[0][1])
def find_user(login):
result = query_db('select password, directory from users where login=?', [login])
if len(result) == 0:
return None
return User(login, result[0][0], result[0][1])
def create_grocery_list(name: str) -> GroceryList:
query_db('insert into grocery_lists (name) values (%s)', [name])
new_id: int = query_db('select last_insert_rowid()')
return get_grocery_list_by_id(new_id)
def delete_recipe(recipe_id):
query_db("DELETE FROM recipes WHERE recipe_id = ?", recipe_id, commit=True)
def add_view(recipe_id):
query_db("UPDATE recipes SET views = views + 1 WHERE recipe_id = ?",
recipe_id,
commit=True)
def add_grocery_item_to_list(list_id: int,
new_item: GroceryItem) -> GroceryList:
query_db(
'insert into grocery_items (list_id, name, quantity) values (%s, %s, %s)',
[list_id, new_item['name'], new_item['quantity']])
return get_grocery_list_by_id(list_id)
def recipe_checked(recipe_id):
res = query_db("SELECT checked FROM recipes WHERE recipe_id = ?",
recipe_id,
one=True)
return True if res and res[0] == 1 else False
def recipe_exists(name):
res = query_db("SELECT COUNT(*) FROM recipes WHERE name = ?",
name,
one=True)
return True if res[0] > 0 else False
def delete_user(user_id):
query_db("DELETE FROM users WHERE user_id = ?", user_id, commit=True)
def accept_recipe(recipe_id):
query_db("UPDATE recipes SET checked = 1 WHERE recipe_id = ?",
recipe_id,
commit=True)
def change_password(username, password):
query_db("UPDATE users SET password = ? WHERE username = ?",
generate_password_hash(password), username, commit=True)
def get_grocery_lists() -> List[GroceryList]:
grocery_lists_rows = query_db('select * from grocery_lists')
grocery_items_rows = query_db('select * from grocery_items')
return _grocery_lists_from_rows(grocery_lists_rows, grocery_items_rows)
