def notify_mapped_users(moderator, noti_type, text):
"""send notification to all mapped user"""
stmt = "SELECT user FROM user_moderator_map WHERE moderator=%s"
params = moderator
rows = mydb.execute_query(stmt, params)
for row in rows:
sent_notification(handle=row.user, noti_type=noti_type, text=text)
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 get_task(task_id):
""" get task"""
stmt = "SELECT * FROM task WHERE task_id=%s"
params = task_id
row = mydb.execute_query(stmt, params)
if len(row) == 0:
return None
task = Task()
task.set_task(row[0])
return task
def get_added_user(user, moderator):
""" get added user"""
stmt = "SELECT * FROM user_moderator_map WHERE user=%s and moderator=%s"
params = (user, moderator)
row = mydb.execute_query(stmt, params)
if len(row) == 0:
return None
added_user = UserModeratorMap()
added_user.set_user_moderator(row[0])
return added_user
def get_user(handle):
""" get user"""
stmt = "SELECT * FROM user WHERE handle=%s"
params = handle
row = mydb.execute_query(stmt, params)
if len(row) == 0:
return None
user = User()
user.set_user(row[0])
return user
def get_notifications(handle):
""" get unread notifications """
stmt = "SELECT COUNT(*) AS unread_count FROM notifications WHERE handle=%s AND marked=0"
params = handle
rows = mydb.execute_query(stmt, params)
unread_count = rows[0].unread_count
# get 10 notifications
stmt = "SELECT * FROM notifications WHERE handle=%s ORDER BY marked ASC, noti_time DESC LIMIT 10"
params = handle
rows = mydb.execute_query(stmt, params)
notifications = []
for row in rows:
obj = Notification()
obj.set_notification(row)
obj.noti_time = obj.noti_time.strftime("%d-%b-%Y at %H:%M")
notifications.append(obj)
return unread_count, notifications
def get_user_task_map(task_id, handle):
""" get user task """
stmt = "SELECT * FROM user_task_map WHERE task_id=%s AND handle=%s"
params = (task_id, handle)
row = mydb.execute_query(stmt, params)
if len(row) == 0:
return None
obj = UserTaskMap()
obj.set_user_task_map(row[0])
return obj
def get_user_by_type(handle, user_type):
""" get user by type"""
stmt = "SELECT * FROM user WHERE handle=%s and user_type=%s"
params = (handle, user_type)
row = mydb.execute_query(stmt, params)
if len(row) == 0:
return None
user = User()
user.set_user(row[0])
return user
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 admin_get_task_list():
""" return Task List """
stmt = "SELECT * FROM task ORDER BY creation_time DESC"
params = None
rows = mydb.execute_query(stmt, params)
tasks_list = []
for row in rows:
obj = Task()
obj.set_task(row)
tasks_list.append(obj)
return tasks_list
def admin_get_usr_modr_list():
""" return User Moderator list"""
stmt = "SELECT * FROM user WHERE user_type=%s OR user_type=%s ORDER BY reg_time DESC"
params = ("User", "Moderator")
rows = mydb.execute_query(stmt, params)
usr_mdrs = []
for row in rows:
obj = User()
obj.set_user(row)
usr_mdrs.append(obj)
return usr_mdrs
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 get_submission_status(task_id, moderator):
""" get submission status"""
submission_status = {'Accepted': [], 'Submitted': [], 'Legends': []}
stmt = "SELECT * FROM user_task_map WHERE task_id=%s"
params = task_id
rows = mydb.execute_query(stmt, params)
for row in rows:
if row.accepted == 1:
submission_status['Accepted'].append(row.handle)
else:
submission_status['Submitted'].append(row.handle)
stmt = "SELECT * FROM user_moderator_map WHERE moderator=%s AND user NOT IN " \
"(SELECT handle FROM user_task_map WHERE task_id=%s)"
params = (moderator, task_id)
rows = mydb.execute_query(stmt, params)
for row in rows:
submission_status['Legends'].append(row.user)
return submission_status
def get_messages(handle):
""" get message"""
stmt = "SELECT * FROM messages WHERE sender=%s OR receiver=%s ORDER BY msg_time DESC"
params = (handle, handle)
rows = mydb.execute_query(stmt, params)
messages = []
for row in rows:
obj = Messages()
obj.set_message(row)
messages.append(obj)
return messages
def get_not_accepted_submissions(task_id):
""" get not accepted submissions"""
stmt = "SELECT * FROM user_task_map WHERE task_id=%s AND accepted=0"
params = task_id
rows = mydb.execute_query(stmt, params)
submissions = []
for row in rows:
obj = UserTaskMap()
obj.set_user_task_map(row)
submissions.append(obj)
return submissions
def get_overdue_tasks(handle):
""" get overdue tasks"""
stmt = "SELECT * FROM task WHERE deadline < CURDATE() AND owner IN (SELECT moderator FROM user_moderator_map WHERE user=%s)" \
" AND task_id NOT IN (SELECT task_id FROM user_task_map WHERE handle=%s) ORDER BY deadline DESC"
params = (handle, handle)
rows = mydb.execute_query(stmt, params)
overdue_tasks = []
for row in rows:
task = Task()
task.set_task(row)
overdue_tasks.append(task)
return overdue_tasks
def get_my_tasks(owner, recent=False):
""" get my task"""
stmt = "SELECT * FROM task WHERE owner=%s"
if recent:
stmt = "SELECT * FROM task WHERE owner=%s ORDER BY creation_time DESC LIMIT 5"
params = owner
rows = mydb.execute_query(stmt, params)
tasks = []
for row in rows:
task = Task()
task.set_task(row)
tasks.append(task)
return tasks
def get_submitted_tasks(handle):
""" get submitted tasks """
stmt = "SELECT task.task_id AS task_id, task.owner AS owner, task.description AS description,task.creation_time AS creation_time," \
" task.task_type AS task_type, task.deadline AS deadline, user_task_map.completion_time AS completion_time" \
" FROM task INNER JOIN user_task_map ON task.task_id = user_task_map.task_id WHERE user_task_map.handle=%s" \
" ORDER BY completion_time DESC"
params = handle
rows = mydb.execute_query(stmt, params)
submitted_tasks = []
for row in rows:
task = Task()
task.set_task(row)
submitted_tasks.append(task)
return submitted_tasks
def get_recent_ongoing_tasks(handle):
""" get recent ongoing tasks"""
today = date.today()
today_datetime = datetime.combine(today, datetime.min.time())
formatted_datetime = today_datetime.strftime('%Y-%m-%d %H:%M:%S')
stmt = "SELECT * FROM task WHERE deadline >= %s AND owner IN (SELECT moderator FROM user_moderator_map WHERE user=%s)" \
" AND task_id NOT IN (SELECT task_id FROM user_task_map WHERE handle=%s) ORDER BY deadline DESC LIMIT 5"
params = (formatted_datetime, handle, handle)
rows = mydb.execute_query(stmt, params)
recent_ongoing_tasks = []
for row in rows:
task = Task()
task.set_task(row)
recent_ongoing_tasks.append(task)
return recent_ongoing_tasks
