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