def get_projects_by_owner(userid): """ Retrieve all projects created by a specific user :param userid: The id of the user :type userid: str :return: An array of projects """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT * FROM projects WHERE userid = %s""" sql_value = (userid, ) #query = ("SELECT * FROM projects WHERE userid = \"" + userid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("get_project_by_owner: {}".format(sql_value)) projects = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_projects_by_owner: {}".format(err)) print("Failed executing query: {}".format(err)) projects = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return projects
def get_task_files(taskid): """ Retrieve all filenames registered in a task :param taskid: The task id :type taskid: str :return: An array of filenames """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT filename FROM task_files WHERE taskid = %s""" sql_value = (str(taskid), ) #query = ("SELECT filename FROM task_files WHERE taskid = \"" + str(taskid) + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("get_task_file: {}".format(sql_value)) filenames = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_task_files: {}".format(err)) print("Failed executing query: {}".format(err)) filenames = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return filenames
def set_task_file(taskid, filename): """ Register a new task - file relationship :param taskid: The task id :param filename: The name of the file :type taskid: str :type filename: str """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """INSERT INTO task_files (taskid, filename) VALUES (%s, %s)""" sql_value = ( taskid, filename, ) #query = ("INSERT INTO task_files (taskid, filename) VALUES (\"" + # taskid + "\", \"" + filename + "\")") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("set_task_file: {}".format(sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query set_task_file: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close()
def get_categories(): """ Get all categories :return: List of categories """ db.connect() cursor = db.cursor(prepared=True) query = ("SELECT * FROM project_category") try: cursor.execute(query) logger.log_input_msg("project.py>>get_categories:{}".format(query)) categories = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query project.py>>get_categories: {}".format( err)) print("Failed executing query: {}".format(err)) categories = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return categories
def update_project_status(projectid, status): """ Change the status of a selected project :param projectid: The project id :param status: The status to change to, should be either open, in progress or finished :type projectid: str :type status: str """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """UPDATE projects SET project_status = %s WHERE projectid = %s""" sql_value = ( status, projectid, ) #query = ("UPDATE projects SET project_status = \"" + status + # "\" WHERE projectid = \"" + projectid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "update_project_status_input: {}".format(sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query update_project_status: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close()
def get_user_name_by_id(userid): """ Get username from user id :param userid: The id of the user :return: The name of the user """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT username from users WHERE userid = %s""" sql_value = (userid, ) #query = ("SELECT username from users WHERE userid =\"" + userid + "\"") username = None try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("get_user_name_by_id: {}".format(sql_value)) users = cursor.fetchall() if len(users): username = users[0][0] except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_user_name_by_id: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return username
def get_project_by_id(projectid): """ Retrieve a project by its id :param projectid: The project id :type projectid: str :return: The selected project """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT * FROM projects WHERE projectid = %s""" sql_value = (projectid, ) #query = ("SELECT * FROM projects WHERE projectid = \"" + projectid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("get_project_id input: {}".format(sql_value)) project = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_project_by_id: {}".format(err)) print("Failed executing query: {}".format(err)) project = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return project[0]
def update_user_password(username, password, ip, fullpath): """ Update user's password :param username: The user attempting to authenticate :param password: The new corresponding password :type username: str :type password: str :return: user """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """UPDATE users SET password=%s WHERE username = %s """ sql_value = ( password, username, ) try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user success update_user_password:{}-{}-{}".format( ip, fullpath, sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query update_user_password: {}".format(err)) print("Failed executing query update_user_password: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return
def get_tasks_by_project_id(projectid): """ Get all tasks belonging to a project :param project_id: The id of the project holding the tasks :type project_id: str :return: List of tasks """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT * FROM tasks WHERE projectid = %s""" sql_value = (projectid, ) #query = ("SELECT * FROM tasks WHERE projectid = \"" + projectid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("get_task_by_project_id: {}".format(sql_value)) tasks = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_tasks_by_project_id: {}".format(err)) print("Failed executing query: {}".format(err)) tasks = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return tasks
def get_user_hashed_password(username, ip, fullpath): """ Check if user hashed password is match with database Need to retreive salt value :param username, ip, fullpath: The user attempting to authenticate :type username: str :return: salt (byte) """ db.connect() cursor = db.cursor(prepared=True) # query the username sql_cmd = """SELECT password FROM users WHERE username = %s""" sql_value = (username, ) try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user attempt in IP get_user_hashed_password:{}-{}-{}".format( ip, fullpath, sql_value)) password = cursor.fetchall() if len(password): password_return = password[0][0] except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_user_hashed_password: {}".format(err)) print( "Failed executing query get_user_hashed_password: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return password_return
def set_project(categoryid, userid, project_title, project_description, project_status): """ Store a project in the database :param categoryid: The id of the corresponding category :param userid: The id of the project owner :param project_title: The title of the project :param project_description: The project description :param project_status: The status of the project :type categoryid: str :type userid: str :type project_title: str :type project_description: str :type project_status: str :return: The id of the new project """ #using mysql placeholder # reference: https://pymysql.readthedocs.io/en/latest/user/examples.html db.connect() cursor = db.cursor(prepared=True) sql_cmd = "INSERT INTO projects VALUES (NULL, %s, %s, %s, %s, %s)" sql_value = ( categoryid, userid, project_title, project_description, project_status, ) #query = ("INSERT INTO projects VALUES (NULL, \"" + # categoryid + "\", \"" + userid + "\", \"" + project_title + "\", \"" + # project_description + "\", \"" + project_status + "\")") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("set_porject_input: {}".format(sql_value)) db.commit() users_projects = get_projects_by_owner(userid) projectid = users_projects[-1][0] except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query set_porject_input: {}".format(err)) print("Failed executing query: {}".format(err)) projectid = None cursor.fetchall() exit(1) finally: cursor.close() db.close() return projectid
def update_reset_token(username, token, ip, fullpath): """ Update user's forget token :param username: The user attempting to authenticate :param email: The user's corresponding email :param ip: user's ip address :param fullpath: user's accessed file path :timestamp: user's accessed time stamp :type username: str :type email: str :param ip: str :param fullpath: str :timestamp: str :return: email """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """UPDATE users SET verify_token = %s WHERE username = %s""" sql_value = ( token, username, ) try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user success update_reset_token:{}-{}-{}".format( ip, fullpath, sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query update_reset_token: {}".format(err)) print("Failed executing query update_reset_token: {}".format(err)) cursor.fetchall() exit(1) except Exception as e: print("An exception occur during execute update_reset_token {}".format( e)) finally: cursor.close() db.close() return
def set_projects_user(projectid, userid, read_permission="TRUE", write_permission="NULL", modify_permission="NULL"): """ Add a user to a project with specific permissions :param projectid: The project id :param userid: The user id :param read_permission: Describes whether a user can view information about a project :param write_permission: Describes whether a user can add files to tasks :param modify_permission: Describes wheter a user can deliver tasks :type projectid: str :type userid: str :type read_permission: str :type write_permission: str """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)""" sql_value = ( projectid, userid, read_permission, write_permission, modify_permission, ) #query = ("INSERT INTO projects_users VALUES (\"" + projectid + "\", \"" + # userid + "\", " + read_permission + ", " + # write_permission + ", " + modify_permission + ")") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("set_project_users: {}".format(sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query set_project_users: {}, {}".format( err, sql_value)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close()
def forget_password_match(username, email, ip, fullpath): """ Update user's password :param username: The user attempting to authenticate :param email: The user's corresponding email :param ip: user's ip address :param fullpath: user's accessed file path :timestamp: user's accessed time stamp :type username: str :type email: str :param ip: str :param fullpath: str :timestamp: str :return: email """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT email FROM users WHERE username = %s AND email=%s""" sql_value = ( username, email, ) try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user success forget_password_match:{}-{}-{}".format( ip, fullpath, sql_value)) query_result = cursor.fetchall() if len(query_result): email = query_result[0][0] except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query forget_password_match: {}".format(err)) print("Failed executing query forget_password_match: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return email
def update_token_to_null(username, token, ip, fullpath): """ search for user's token :param username: The user's name :param ip: user's ip address :param fullpath: user's accessed file path :timestamp: user's accessed time stamp :type username: str :type email: str :param ip: str :param fullpath: str :return: email """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """UPDATE users SET verify_token = Null, temp = 0 WHERE username = %s AND verify_token = %s""" sql_value = ( username, token, ) try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user success update_token_to_null:{}-{}-{}".format( ip, fullpath, sql_value)) actions = db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query update_token_to_null: {}".format(err)) print("Failed executing query update_token_to_null: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return actions
def search_for_token(token, ip, fullpath): """ search for user's token :param token: The user's token :param ip: user's ip address :param fullpath: user's accessed file path :timestamp: user's accessed time stamp :type username: str :type email: str :param ip: str :param fullpath: str :return: email """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT username FROM users WHERE google_token = %s """ sql_value = (token, ) try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user success search_for_google_token:{}-{}-{}".format( ip, fullpath, sql_value)) token_queries = cursor.fetchall() if len(token_queries): token_query = token_queries[0][0] except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query search_for_google_token: {}".format(err)) print("Failed executing query search_for_google_token: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return token_query
def match_user(username, password, ip, fullpath): """ Check if user credentials are correct, return if exists :param username: The user attempting to authenticate :param password: The corresponding password :type username: str :type password: str :return: user """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT userid, username FROM users WHERE username = %s AND password = %s AND temp = 0""" sql_value = ( username, password, ) #query = ("SELECT userid, username from users where username = \"" + username + # "\" and password = \"" + password + "\"") user = None try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "A user success log in-match_user:{}-{}-{}".format( ip, fullpath, sql_value)) users = cursor.fetchall() if len(users): user = users[0] except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query match_user: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return user
def get_projects_by_participant_and_status(userid, project_status): """ Retrieve all projects where the user is a participant with specific status :param userid: The id of the participant :param project_status: The status to filter on :type userid: str :type project_status: str :return: A list of projects """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT * FROM projects, projects_users WHERE projects.project_status = %s AND projects_users.userid = %s AND projects_users.projectid = projects.projectid""" sql_value = ( project_status, userid, ) #query = ("SELECT * FROM projects, projects_users WHERE projects.project_status = \"" + # project_status + "\" AND projects_users.userid = \"" + userid + # "\" AND projects_users.projectid = projects.projectid") db.connect() try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "get_projects_by_participant_and_status: {}".format(sql_value)) projects = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_projects_by_participant_and_status: {}" .format(err)) print("Failed executing query: {}".format(err)) projects = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return projects
def get_qurery_frequency(username, ip, fullpath): """ Create a lock out function. :param username: The user attempting to authenticate :type username: str :param ip: The user's ip :type username: str :param fullpath: The user access path :type username: str :return: times of query reference: https://stackoverflow.com/questions/19966123/get-time-interval-in-mysql """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT * from user_access_time AS t where username = %s \ AND t.access_time BETWEEN DATE_ADD(NOW(), INTERVAL -30 MINUTE) AND NOW()""" sql_value = (username, ) try: logger.log_input_msg( "A attemp to access a user account-get_qurery_frequency:{}-{}-{}". format(ip, fullpath, sql_value)) cursor.execute(sql_cmd, sql_value) result = cursor.fetchall() #print("query_result: {}".format(result)) count_access_time = int(len(result)) except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_qurery_frequency: {}".format(err)) print("Failed executing query get_qurery_frequency: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() return count_access_time
def get_users(): """ Retreive all registrered users from the database :return: users """ db.connect() cursor = db.cursor(prepared=True) query = ("SELECT userid, username from users") try: cursor.execute(query) #logger.log_input_msg("get_users: {}".format(query)) users = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_users: {}".format(err)) print("Failed executing query: {}".format(err)) users = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return users
def update_task_status(taskid, status): db.connect() cursor = db.cursor(prepared=True) sql_cmd = """UPDATE tasks SET task_status = %s WHERE taskid = %s""" sql_value = ( status, taskid, ) #query = ("UPDATE tasks SET task_status = \"" + status + # "\" WHERE taskid = \"" + taskid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("update_task_status: {}".format(sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query update_task_status: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close()
def get_user_permissions(userid, projectid): """ Get permissions for a selected users in a specific project :param userid: The id of the user :param projectid: The id of the project :type userid: str :type projectid: str :return: Permissions as an array of numbers as boolean values """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT read_permission, write_permission, modify_permission FROM projects_users\ WHERE projectid = %s AND userid = %s""" sql_value = ( projectid, userid, ) #query = ("SELECT read_permission, write_permission, modify_permission \ # FROM projects_users WHERE projectid = \"" + projectid + # "\" AND userid = \"" + userid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("get_user_permissions: {}".format(sql_value)) permissions = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_user_permissions: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close() if len(permissions): return permissions[0] return [0, 0, 0]
def set_task(projectid, task_title, task_description, budget): """ Create a task :param projectid: The corresponding project id :param task_title: The title of the task :param task_description: The description of the task :param budget: The task budget :type projectid: str :type task_title: str :type task_description: str :type budget: str """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (%s, %s, %s, %s, "waiting for delivery")""" sql_value = ( projectid, task_title, task_description, budget, ) #query = ("INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (\"" + # projectid + "\", \"" + task_title + "\", \"" + # task_description + "\", \"" + budget + "\", \"waiting for delivery\")") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("set_task: {}".format(sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg("Failed executing query set_task: {}".format(err)) print("Failed executing query: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close()
def get_projects_by_status_and_category(categoryid, project_status): """ Retrieve all projects from a category with a specific status :param catergoryid: The id of the category :param project_status: The status to filter on :type catergoryid: str :type project_status: str :return: A list of projects """ db.connect() cursor = db.cursor(prepared=True) sql_cmd = """SELECT * FROM projects WHERE project_status = %s AND categoryid = %s """ sql_value = ( project_status, categoryid, ) #query = ("SELECT * FROM projects WHERE project_status = \"" + # project_status + "\" AND categoryid = \"" + categoryid + "\"") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg( "get_project_id_by_status_and_category: {}".format(sql_value)) projects = cursor.fetchall() except mysql.connector.Error as err: logger.log_error_msg( "Failed executing query get_projects_by_status_and_category: {}". format(err)) print("Failed executing query: {}".format(err)) projects = [] cursor.fetchall() exit(1) finally: cursor.close() db.close() return projects
def set_user(username, password, full_name, company, email, street_address, city, state, postal_code, country, ip, path, temp, google_token, temp_token): """ Register a new user in the database :param username: The users unique user name :param password: The password :param full_name: The users full name :param company: The company the user represents :param email: The users email address :param street_address: The street address of the user :param city: The city where the user lives :param state: The state where the user lives :param postal_code: The corresponding postal code :param country: The users country :param ip: The user's ip address :param path: The user's access path :param temp: The user's account is temporary or not :param google_token: The user's google token :param temp_token: The user's access token :type username: str :type password: str :type full_name: str :type company: str :type email: str :type street_address: str :type city: str :type state: str :type postal_code: str :type country: str :type ip: num :type path: str :type temp: boolean :type google_token: str :type temp_token: str """ db.connect() cursor = db.cursor(prepared=True) # get register date current_time = datetime.datetime.today().strftime('%Y-%m-%d') #genereate sql command sql_cmd = """INSERT INTO users VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" sql_value = (username, password, full_name, company, email, street_address, city, state, postal_code, country, current_time, temp, google_token, temp_token) #query = ("INSERT INTO users VALUES (NULL, \"" + username + "\", \"" + # password + "\", \"" + full_name + "\" , \"" + company + "\", \"" + # email + "\", \"" + street_address + "\", \"" + city + "\", \"" + # state + "\", \"" + postal_code + "\", \"" + country + "\")") try: cursor.execute(sql_cmd, sql_value) logger.log_input_msg("register:IP:{}-{}-{}".format( ip, path, sql_value)) db.commit() except mysql.connector.Error as err: logger.log_error_msg(err) print("Failed executing query register-set_user: {}".format(err)) cursor.fetchall() exit(1) finally: cursor.close() db.close()