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()
query = (
"INSERT INTO tasks (projectid, title, task_description, budget, task_status) VALUES (%s, %s, %s, %s, %s)"
)
try:
cursor.execute(query, (projectid, task_title, task_description, budget,
"waiting for delivery"))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
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()
# query = ("INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)")
query = ("INSERT INTO projects_users VALUES (%s, %s, %s, %s, %s)")
try:
# cursor.execute(query, (projectid, userid, read_permission, write_permission, modify_permission))
cursor.execute(
query,
(projectid, userid, read_permission == "TRUE", write_permission
== "TRUE", modify_permission == "TRUE"))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
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 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 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
"""
db.connect()
cursor = db.cursor()
query = ("INSERT INTO projects VALUES (NULL, %s, %s, %s, %s, %s)")
try:
cursor.execute(query, (categoryid, userid, project_title,
project_description, project_status))
db.commit()
users_projects = get_projects_by_owner(userid)
projectid = users_projects[-1][0]
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
projectid = None
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return projectid
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 update_task_status(taskid, status):
db.connect()
cursor = db.cursor()
query = ("UPDATE tasks SET task_status = %s WHERE taskid = %s")
try:
cursor.execute(query, (status, taskid))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
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 verify_user_by_email(verification_key):
is_real_key = match_verification_key(verification_key)
db.connect()
cursor = db.cursor()
query = "UPDATE users SET verified = 1 WHERE verification_key = %s"
try:
cursor.execute(query, (verification_key, ))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
exit(1)
finally:
cursor.close()
db.close()
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 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 record_user_login(username, ip, fullpath, access_time):
"""
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)
#print("enter logger!")
# log the request
sql_cmd_log = """INSERT INTO user_access_time VALUES (NULL, %s, %s, %s)"""
#print (sql_cmd_log)
msg = "A user attempt:IP:{}-{}".format(ip, fullpath)
#print(len(msg))
sql_value_log = (
username,
msg,
access_time,
)
try:
cursor.execute(sql_cmd_log, sql_value_log)
log_input_msg("A user attempt in IP record_user_login:{}-{}-{}".format(
ip, fullpath, sql_value_log))
db.commit()
except mysql.connector.Error as err:
log_error_msg(
"Failed executing query record_user_login: {}".format(err))
print("Failed executing query record_user_login: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
return
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()
query = ("UPDATE projects SET project_status = %s WHERE projectid = %s")
try:
cursor.execute(query, (status, projectid))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
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()
query = ("INSERT INTO task_files (taskid, filename) VALUES (%s, %s)")
try:
cursor.execute(query, (taskid, filename))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
def set_user(username, password, full_name, company, email,
street_address, city, state, postal_code, country, verification_key):
"""
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
: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
"""
db.connect()
cursor = db.cursor()
query = ("INSERT INTO users VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0)")
try:
cursor.execute(query, (username, password, full_name, company, email, street_address, city, state, postal_code, country, verification_key))
db.commit()
except mysql.connector.Error as err:
print("Failed executing query: {}".format(err))
cursor.fetchall()
exit(1)
finally:
cursor.close()
db.close()
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 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 collect_or_cancel_activity(user_id, activity_id):
"""
收藏或者，取消收藏
"""
user_activity = UserActivity.query.filter(UserActivity.user_id == user_id, UserActivity.activity_id == activity_id).first()
if user_activity:
try:
db.delete(user_activity)
db.commit()
return True
except:
return False
else:
user_activity = UserActivity(user_id=user_id, activity_id=activity_id)
try:
db.add(user_activity)
db.commit()
user_info = UserInfo.query.filter(UserInfo.user_id == user_id).first()
user_info.add_credit('activity')
sender_user = UserInfo.query.filter(UserInfo.user_id == user_id).first()
sender_user.add_reputation('activity')
return True
except:
return False
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 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()
def handle(self, message={}):
"""
Handle a received message in login state
:param message:
:return:
"""
try:
BaseController.handle(self, message)
except KeyError:
key = message.get("k")
value = message.get("v")
if key != "msg" or len(value) == 0:
return
# State - Login username
if self.state == State.login_username:
account = Account.get(lambda account: account.name.lower() == value.lower())
if account:
self.account_id = account.id
self.request_password()
self.state = State.login_password
else:
self.send_offtopic("Create new account with this name y/n?")
self.account_name = value
self.state = State.create_account
# State - Login password
elif self.state == State.login_password:
account = Account[self.account_id]
if self.hash_password_with_salt(account.password, self.dynamic_salt) == value:
self.send_offtopic("Login OK")
# Set controller to menu controller
self.connection.controller = MenuController(self.connection, self.runtime, self.account_id)
return
else:
logging.info("Expected password: {}".format(
self.hash_password_with_salt(account.password, self.dynamic_salt)))
logging.info("Received password: {}".format( value))
self.send_offtopic("Login fail")
self.state = State.login_username
self.greeting()
# State - Create account
elif self.state == State.create_account:
if value.lower()[0] == "y":
self.request_password(True, False)
self.state = State.create_password
else:
self.greeting()
self.state = State.login_username
# State - Create account - password
elif self.state == State.create_password:
self.password = value
logging.info("Received password 1: {}".format(value))
self.request_password(False)
self.state = State.create_password_repeat
# State - Create account - password repeat
elif self.state == State.create_password_repeat:
dynamic_password = self.hash_password_with_salt(self.password, self.dynamic_salt)
logging.info("Received password 2: {}".format(value))
if dynamic_password == value:
self.state = State.login_username
self.send_offtopic("New account has been created, you may now login.")
self.greeting()
account = Account(name=self.account_name,
password=self.password,
salt=self.static_salt)
db.commit()
else:
self.send_offtopic("Password mismatch. Account not created.")
self.greeting()
self.state = State.login_username
