def show_all(cls):
"""
Displays a list of all projects in which the specified user is involved
:return: project list
"""
project_list = []
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("SELECT name, description, user_id, id FROM projects")
data = c.fetchall()
for i in range(len(data)):
c.execute(
"SELECT user_id FROM user_project WHERE project_id==('%d')" %
data[i][3])
project_users = c.fetchall()
users_to_add = []
for j in project_users:
c.execute("SELECT * FROM users WHERE id==('%d')" % j[0])
user_data = c.fetchone()
user = User(user_data[1], user_data[2], user_data[3],
user_data[0])
users_to_add.append(user)
project = Project(data[i][0], data[i][1], data[i][2], users_to_add)
project_list.append(project)
return project_list
def send_to_archive(cls, task):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"UPDATE tasks SET archive=('%s') WHERE name==('%s') AND column_id==('%s')" % (1, task.name, task.column_id))
conn.commit()
conn.close()
def add_project_to_db(cls, project, user):
"""
Add an instance of the Project class to the database
:param project: the instance of the Project class that you want to add
:param user: an instance of the User class that is the Creator of the project
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("SELECT name, user_id FROM projects")
all_projectnames = c.fetchall()
yep = False
for i in all_projectnames:
if project.name == i[0] and user.user_id == i[1]:
yep = True
if not yep:
c.execute(
"INSERT INTO projects (name, description, user_id) VALUES ('%s', '%s', '%s')"
% (project.name, project.description, user.user_id))
conn.commit()
c.execute("SELECT id FROM projects WHERE name==('%s')" %
project.name)
id = c.fetchone()
c.execute(
"INSERT INTO user_project (user_id, project_id) VALUES ('%d', '%d')"
% (user.user_id, id[0]))
conn.commit()
conn.close()
else:
raise ProjectWithThisNameAlreadyExist()
def set_subtask(cls, task1, task2):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"INSERT INTO task_subtask (subtask_id, task_id) VALUES ('%s','%s')"
% (task1.id, task2.id))
conn.commit()
conn.close()
def add_task_to_db(cls, task):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("INSERT INTO tasks (name, desc, project_id, column_id, user_id, first_date, second_date, "
"edit_date, tags, priority, archive, is_subtask) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',"
"'%s','%s')" % (task.name, task.desc, task.project_id, task.column_id, task.user_id,
task.first_date, task.second_date, task.edit_date, task.tags, task.priority, 0, task.is_subtask))
conn.commit()
conn.close()
def get_task_by_id(cls, project_name, column_name, id):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
column = ColumnStorage.get_column(project_name, column_name)
c.execute("SELECT * FROM tasks WHERE column_id==('%s') AND id==('%s')" % (column.id, id))
data = c.fetchone()
task = Task(data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9], data[10], data[11],
data[12], data[0])
return task
def save(self, task):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("UPDATE tasks SET name=('%s'),desc=('%s'),project_id=('%s'),column_id=('%s'),user_id=('%s'),"
"first_date=('%s'),second_date=('%s'), edit_date=('%s'), tags=('%s'),priority=('%s'),archive=('%s'),"
"is_subtask=('%s') WHERE id ==('%s')"
% (task.name, task.desc, task.project_id, task.column_id, task.user_id, task.first_date,
task.second_date, task.edit_date, task.tags, task.priority, task.archive, task.is_subtask, task.id))
conn.commit()
conn.close()
def get_all_subtasks(cls, project_name, column_name, task):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("SELECT * FROM task_subtask WHERE task_id == ('%s')"% task.id)
data = c.fetchall()
subtask_list = []
for i in data:
task = TaskStorage.get_task_by_id(project_name, column_name, i[0])
subtask_list.append(task)
return subtask_list
def delete_with_object(cls, project):
"""
Delete the specified project that was passed in the arguments
:param project: project whitch you want to delete
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("DELETE FROM projects WHERE name=('%s')" % project.name)
conn.commit()
conn.close()
def create_table(cls):
path = conf.get_path_to_db()
print("__________________________")
print(path)
conn = sqlite3.connect(path)
c = conn.cursor()
c.execute(
"CREATE TABLE `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` TEXT, `password` TEXT, "
"`email` TEXT);")
conn.commit()
conn.close()
def get_all_users(cls):
"""
Get a list of names of all existing users
:return: list of user names
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("SELECT username FROM users")
all_usernames = c.fetchall()
conn.close()
return all_usernames
def create_table(cls):
path = conf.get_path_to_db()
conn = sqlite3.connect(path)
c = conn.cursor()
c.execute(
"CREATE TABLE 'regular_task' (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `desc` TEXT, "
"`project_id` TEXT, `column_id` TEXT, `user_id` TEXT, `first_date` TEXT, `second_date` TEXT, "
"`step` TEXT, `type_of_step` INTEGER, `edit_date` TEXT, `tags` TEXT, `priority` TEXT, `archive` TEXT "
")")
conn.commit()
conn.close()
def delete_user(cls, name):
"""
Remove the user with the specified name from the database
:param name:
:return:
"""
user = UserStorage.get_user_by_name(name)
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("DELETE FROM users WHERE username==('%s')" % user.username)
conn.commit()
conn.close()
def get_task(cls, project_name, column_name, name):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
column = ColumnStorage.get_column(project_name, column_name)
c.execute("SELECT * FROM tasks WHERE column_id==('%s') AND name==('%s')" % (column.id, name))
data = c.fetchone()
try:
task = Task(data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9], data[10], data[11],
data[12], data[0])
except Exception:
raise CannotGetProject
return task
def create_table(cls):
path = conf.get_path_to_db()
conn = sqlite3.connect(path)
c = conn.cursor()
c.execute(
"CREATE TABLE `projects` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `description` TEXT, "
"`user_id` INTEGER)")
c.execute(
"CREATE TABLE `user_project` (`user_id` INTEGER, `project_id` INTEGER )"
)
conn.commit()
conn.close()
def set_password_for_user(cls, user):
"""
Setting a password for the user
:param user: user object
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("UPDATE users SET password=('%s') WHERE username==('%s')" %
(user.password, user.username))
conn.commit()
conn.close()
def get_project(cls, name):
"""
Getting the project by the specified name
:param name: name of project
:return: project
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("SELECT * FROM projects WHERE name==('%s')" % name)
data = c.fetchone()
conn.close()
project = Project(data[1], data[2], data[3], None, data[0])
return project
def get_all_tasks_for_user(cls, user):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("SELECT * FROM regular_task WHERE user_id==('%s')" %
user.user_id)
data = c.fetchall()
task_list = []
conn.close()
for i in data:
task = RegularTask(i[1], i[2], i[3], i[4], i[5], i[6], i[7], i[8],
i[9], i[10], i[11], i[12], i[0])
task_list.append(task)
return task_list
def create_table(cls):
"""
:return:
"""
path = conf.get_path_to_db()
conn = sqlite3.connect(path)
c = conn.cursor()
c.execute(
"CREATE TABLE `columns` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `desc` TEXT, "
"`project_id` TEXT)")
conn.commit()
conn.close()
def set_username_for_user(cls, user, oldname):
"""
Setting a new username for the user
:param user: user object
:param oldname: old name of user
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute("UPDATE users SET username=('%s') WHERE username==('%s')" %
(user.username, oldname))
conn.commit()
conn.close()
def get_all_persons_in_project(cls, project):
"""
Get the id of all users involved in the project
:param project: the name of the project
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"SELECT user_id FROM user_project WHERE project_id ==('%s')" %
project.id)
data = c.fetchall()
return data
def save(self, project):
"""
Saves the transferred instance of the Project class to the database
:param project: project to save
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"UPDATE projects SET name=('%s'),description=('%s') WHERE id==('%d')"
% (project.name, project.description, project.id))
conn.commit()
conn.close()
def get_all_tasks(cls, project_name, column_name):
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
project = ProjectStorage.get_project(project_name)
column = ColumnStorage.get_column(project_name, column_name)
c.execute("SELECT * FROM tasks WHERE project_id==('%s') AND column_id==('%s')"%(project.id,column.id))
data = c.fetchall()
task_list = []
conn.close()
for i in data:
task = Task(i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10],i[11],i[12],i[0])
task_list.append(task)
return task_list
def save(self, column):
"""
Saves all instance fields to the database
:param column: the instance that you want to keep
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"UPDATE columns SET name=('%s'),desc=('%s'),project_id=('%s') WHERE id==('%d')"
% (column.name, column.desc, column.project_id, column.id))
conn.commit()
conn.close()
def delete_column_from_db(cls, column):
"""
Remove the transferred instance from the database
:param column: the instance you want to delete
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"DELETE FROM columns WHERE name == ('%s') AND project_id==('%s')" %
(column.name, column.project_id))
conn.commit()
conn.close()
def check_permission(cls, person, project):
"""
Checks whether the specified user is participating in the project
:param person: the supposed worker
:param project: project to check
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
guys = ProjectStorage.get_all_persons_in_project(project)
for i in guys:
if i[0] == person.user_id:
return
raise NoPermission
def add_user_to_db(cls, user):
"""
Add the transferred user to the database
:param user: user to add
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"INSERT INTO users (username, password, email) VALUES ('%s', '%s', '%s')"
% (user.username, user.password, user.email))
conn.commit()
conn.close()
return
def delete_person_from_project(cls, person, project):
"""
Remove the specified user from the project
:param person: person to remove
:param project: project where you want to remove the user
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"DELETE FROM user_project WHERE user_id ==('%s') AND project_id==('%s')"
% (person.user_id, project.id))
conn.commit()
conn.close()
def add_person_to_project(cls, person, project):
"""
Adds the specified user to the project if the command was executed on behalf of the project creator
:param person: person to add to the project
:param project: project where you want to add the user
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
c.execute(
"INSERT INTO user_project (user_id, project_id) VALUES ('%d','%d')"
% (person.user_id, project.id))
conn.commit()
conn.close()
def is_admin(cls, person, project):
"""
Checks whether the specified user is the creator of the project
:param person: the supposed creator
:param project: project to check
:return:
"""
conn = sqlite3.connect(conf.get_path_to_db())
c = conn.cursor()
guys = ProjectStorage.get_all_persons_in_project(project)
if guys[0][0] == person.user_id:
pass
else:
raise UAreNotAdmin
