def is_user_with_email_in_user_list(cls, email):
"""checks that is user with given email in database"""
db = get_db()
query = "SELECT * FROM users WHERE email=?"
values = (email, )
user_from_db = DB.execute_select_query(db, query, values)
return bool(user_from_db)
def archived_todos_count(self):
"""connects with db and counts archived todos for user"""
db = get_db()
query = """SELECT COUNT(`id`) FROM `todo_items`
WHERE `owner_id` = ? AND `is_archived` = 1;"""
values = (self.id, )
return DB.execute_select_query(db, query, values)[0][0]
def is_user_with_name_in_user_list(cls, name):
"""checks that is user with given name in database"""
db = get_db()
query = "SELECT * FROM users WHERE name=?"
values = (name, )
user_from_db = DB.execute_select_query(db, query, values)
return bool(user_from_db)
def active_todos_undone_count(self):
"""connects with db and counts active todos with undone status for user"""
db = get_db()
query = """SELECT COUNT(`id`) FROM `todo_items`
WHERE `owner_id` = ? AND `is_archived` = 0 AND `status` = 0;"""
values = (self.id, )
return DB.execute_select_query(db, query, values)[0][0]
def is_admin(self):
"""checks that user has admin status """
db = get_db()
query = "SELECT users.id FROM users_permissions " \
"JOIN users ON users.id=users_permissions.user_id " \
"JOIN permission_types ON users_permissions.permission_id = permission_types.id " \
"WHERE users.id = ? AND permission_types.name = ?"
values = (self.id, 'admin')
return bool(DB.execute_select_query(db, query, values))
def get_users_list(cls):
""" Retrieves all users from database and returns them as list.
Returns:
list(Userlist): list of all users
"""
db = get_db()
users = []
query = """SELECT `name`, `password`, `id`, `email`, `registration_date` FROM `users`;"""
users_from_db = DB.execute_select_query(db, query)
for user in users_from_db:
users.append(User(*user))
return users
def get_by_id(cls, id_):
""" Retrieves user with given id from database.
Args:
id_(int): user id
Returns:
Todo: User object with a given id
"""
db = get_db()
query = """SELECT `name`, `password`, `id`, `email`, `registration_date`
FROM `users`
WHERE `id` = ?;"""
values = (id_, )
user_from_db = DB.execute_select_query(db, query, values)
return User(*user_from_db[0]) if user_from_db else None
def get_by_id(cls, id_):
""" Retrieves todo item with given id from database.
Args:
id_(int): item id
Returns:
Todo: Todo object with a given id
"""
db = get_db()
query = "SELECT `name`, `id`, `status`, `create_date`, `priority`, `due_date`," \
" `owner_id`, `is_archived`, `description` " \
"FROM `todo_items` " \
"WHERE `todo_items`.`id` = ?;"
values = (id_, )
todo_item_from_db = DB.execute_select_query(db, query, values)
return Todo(*todo_item_from_db[0]) if todo_item_from_db else None
def get_by_email(cls, email):
""" Retrieves user with given name from database.
Args:
name(string): user name,
db: dababase object
Returns:
User object with a given name
"""
db = get_db()
query = """SELECT `name`, `password`, `id`, `email`, `registration_date`
FROM `users`
WHERE `email` = ?;"""
values = (email, )
user_from_db = DB.execute_select_query(db, query, values)
return User(*user_from_db[0])
def get_all(cls, user_id, is_archived=False):
""" Retrieves all Todos form database and returns them as list.
Returns:
list(Todo): list of all todos
"""
db = get_db()
todo_list = []
query = "SELECT `name`, `id`, `status`, `create_date`, " \
"`priority`, `due_date`, `owner_id`, `is_archived`, `description` " \
"FROM `todo_items` " \
"WHERE `is_archived` = ? AND `owner_id` = ? ORDER BY create_date DESC;"
values = (int(is_archived), user_id)
todo_items_from_db = DB.execute_select_query(db, query, values)
for item in todo_items_from_db:
todo_list.append(Todo(*item))
return todo_list