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()
