def delete_task(task_id):
""" delete task"""
task = get_task(task_id)
stmt = "DELETE FROM task WHERE task_id=%s"
params = task_id
mydb.execute_update(stmt, params)
# delete files
if task.task_type == "Upload":
path = "janta/static/uploaded-files/"
stmt = "SELECT * FROM user_task_map WHERE task_id=%s"
params = task_id
rows = mydb.execute_query(stmt, params)
for row in rows:
path += row.submission
if os.path.exists(path):
os.remove(path)
stmt = "DELETE FROM user_task_map WHERE task_id=%s"
params = task_id
mydb.execute_update(stmt, params)
# send notification to all mapped user
noti_type = "Task Deleted"
text = "Task Id:" + task_id + " has been deleted by moderator:" + task.owner
notify_mapped_users(moderator=task.owner, noti_type=noti_type, text=text)
def admin_del_task(task_id):
""" delete task for task_id"""
stmt = "DELETE FROM task WHERE task_id=%s"
params = task_id
mydb.execute_update(stmt, params)
stmt = "DELETE FROM user_task_map WHERE task_id=%s"
params = task_id
mydb.execute_update(stmt, params)
def remove_user(user, moderator):
""" remove user"""
stmt = "DELETE FROM user_moderator_map WHERE user=%s and moderator=%s"
params = (user, moderator)
mydb.execute_update(stmt, params)
stmt = "DELETE FROM user_task_map WHERE handle=%s and task_id IN (SELECT task_id FROM task WHERE owner=%s)"
params = (user, moderator)
mydb.execute_update(stmt, params)
def accept_submission(task_id, handle):
""" accept submission"""
stmt = "UPDATE user_task_map SET accepted=1 WHERE task_id=%s AND handle=%s"
params = (task_id, handle)
mydb.execute_update(stmt, params)
noti_type = "Submission Accepted"
text = "Your submission for task id:" + task_id + " has been accepted."
sent_notification(handle=handle, noti_type=noti_type, text=text)
def send_message(sender, receiver, text):
""" send message"""
stmt = "INSERT INTO messages (sender, receiver, text, msg_time)" \
"VALUES (%s, %s, %s, NOW())"
params = (sender, receiver, text)
mydb.execute_update(stmt, params)
noti_type = "New Message"
text = sender + " sent a message."
sent_notification(handle=receiver, noti_type=noti_type, text=text)
def mark_as_read(noti_id, handle):
""" update the notifications mark as read"""
stmt = "UPDATE notifications SET marked=1 WHERE id=%s"
params = noti_id
mydb.execute_update(stmt, params)
# delete read notification more than 10
stmt = "DELETE FROM notifications WHERE marked=1 AND handle=%s AND id NOT IN (SELECT id FROM " \
"(SELECT id FROM notifications WHERE handle=%s AND marked=1 ORDER BY noti_time DESC LIMIT 10) foo)"
params = (handle, handle)
mydb.execute_update(stmt, params)
def validate_credentials(handle, password):
""" validate Credentials"""
stmt = "SELECT * FROM user WHERE handle=%s and password=%s"
params = (handle, password)
row = mydb.execute_query(stmt, params)
if len(row) == 1:
stmt = "UPDATE user SET last_login=NOW() WHERE handle=%s"
params = handle
mydb.execute_update(stmt, params)
return True
return False
def change_password(handle, old_password, new_password):
""" change password"""
stmt = 'SELECT * FROM user WHERE handle=%s AND password=%s'
params = (handle, old_password)
row = mydb.execute_query(stmt, params)
if len(row) == 0:
return False
stmt = 'UPDATE user SET password=%s WHERE handle=%s AND password=%s'
params = (new_password, handle, old_password)
mydb.execute_update(stmt, params)
return True
def add_users(moderator, user_handles):
""" add user"""
count = 0
invalid_handles = []
stmt = "INSERT INTO user_moderator_map (user, moderator, map_time)" \
"values (%s, %s, NOW())"
for handle in user_handles:
params = (handle, moderator)
mydb.execute_update(stmt, params)
count += 1
return "Add count: " + str(count)
def insert_task(task_id, owner, description, task_type, deadline):
""" insert task"""
deadline_datetime = datetime.combine(deadline, datetime.min.time())
# now = datetime.now()
# formatted_now = now.strftime('%Y-%m-%d %H:%M:%S')
formatted_deadline = deadline_datetime.strftime('%Y-%m-%d %H:%M:%S')
stmt = "INSERT INTO task (task_id, owner, task_type, description, deadline, creation_time)" \
"values(%s, %s, %s, %s, %s, NOW())"
params = (task_id, owner, task_type, description, formatted_deadline)
mydb.execute_update(stmt, params)
# send notification to all mapped user
noti_type = "Task Created"
text = "Task Id:" + task_id + " has been created by moderator:" + owner
notify_mapped_users(moderator=owner, noti_type=noti_type, text=text)
def reject_submission(task_id, handle):
""" reject submission """
task = get_task(task_id)
user_task_map = get_user_task_map(task_id=task_id, handle=handle)
stmt = "DELETE FROM user_task_map WHERE task_id=%s AND handle=%s"
params = (task_id, handle)
mydb.execute_update(stmt, params)
# delete file
if task is not None and task.task_type == "Upload":
path = "janta/static/uploaded-files/" + user_task_map.submission
if os.path.exists(path):
os.remove(path)
noti_type = "Submission Rejected"
text = "Your submission for task id:" + task_id + " has been rejected."
sent_notification(handle=handle, noti_type=noti_type, text=text)
def modify_task(task_id, owner, description, task_type, deadline):
""" modify task"""
task = get_task(task_id)
deadline_datetime = datetime.combine(deadline, datetime.min.time())
formatted_deadline = deadline_datetime.strftime('%Y-%m-%d %H:%M:%S')
stmt = "UPDATE task SET task_type=%s, description=%s, deadline=%s WHERE task_id=%s"
params = (task_type, description, formatted_deadline, task_id)
mydb.execute_update(stmt, params)
if task.task_type != task_type or task.description != description:
stmt = "DELETE FROM user_task_map WHERE task_id=%s"
params = task_id
mydb.execute_update(stmt, params)
# send notification to all mapped user
noti_type = "Task Modified"
text = "Task Id:" + task_id + " has been modified by moderator:" + owner
notify_mapped_users(moderator=owner, noti_type=noti_type, text=text)
def admin_del_moderator(handle):
""" delete Moderator """
stmt = "DELETE FROM user WHERE handle=%s"
params = handle
mydb.execute_update(stmt, params)
stmt = "DELETE FROM user_moderator_map WHERE moderator=%s"
params = handle
mydb.execute_update(stmt, params)
stmt = "DELETE FROM task WHERE owner=%s"
params = handle
mydb.execute_update(stmt, params)
stmt = "DELETE FROM messages WHERE sender=%s or receiver=%s"
params = (handle, handle)
mydb.execute_update(stmt, params)
stmt = "DELETE FROM notifications WHERE handle=%s"
params = handle
mydb.execute_update(stmt, params)
def delete_message(msg_id, handle):
""" delete message"""
stmt = "DELETE FROM messages WHERE (sender=%s OR receiver=%s) AND id=%s"
params = (handle, handle, msg_id)
mydb.execute_update(stmt, params)
def profile_edit(handle, name, emailid, gender, about, git_link,
linkedin_link):
""" Edit Profile"""
stmt = 'UPDATE user SET name=%s, email_id=%s, gender=%s, about=%s, git_link=%s, linkedin_link=%s WHERE handle=%s'
params = (name, emailid, gender, about, git_link, linkedin_link, handle)
mydb.execute_update(stmt, params)
def user_register(handle, password, user_type, name, emailid, gender):
""" User Register"""
stmt = 'INSERT INTO user (handle, password, user_type, name, email_id, gender, last_login, reg_time)' \
'values(%s, %s, %s, %s, %s, %s, NOW(), NOW())'
params = (handle, password, user_type, name, emailid, gender)
mydb.execute_update(stmt, params)
def submit_task(task_id, handle, submission):
""" submit task"""
stmt = "INSERT INTO user_task_map (task_id, handle, completion_time, submission, accepted)" \
"VALUES (%s, %s, NOW(), %s, 0)"
params = (task_id, handle, submission)
mydb.execute_update(stmt, params)
def sent_notification(handle, noti_type, text):
""" send notification """
stmt = "INSERT INTO notifications (handle, noti_type, text, noti_time, marked)" \
"VALUES (%s, %s, %s, NOW(), 0)"
params = (handle, noti_type, text)
mydb.execute_update(stmt, params)