def getAllEventsOfUser(userId, start, end): # return list of event objects. "start" and "end" is optional cursor = getCursor() try: sql = "select eventn.EventId, eventn.`Name`, eventn.`Start`, eventn.`End` " \ "from `user` " \ "join usercalendars on usercalendars.UserId = `user`.UserId " \ "join eventcalendar on eventcalendar.CalendarId = usercalendars.CalendarId " \ "join eventn on eventn.EventId = eventcalendar.EventId " \ "where `user`.UserId = %s;" if start is not None and end is not None: sql = sql[:-1] + " and eventn.Start < %s AND eventn.End > %s;" cursor.execute(sql, (userId, end, start)) else: cursor.execute(sql, (userId, )) result = cursor.fetchall() events = [{ "id": eventTuple[0], "name": eventTuple[1], "start": eventTuple[2], "end": eventTuple[3] } for eventTuple in result] return events except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def getEvent(eventId): if not isLoggedIn(): return -1 userId = session["id"] # return list of event objects. "start" and "end" is optional cursor = getCursor() try: sql = "select eventn.EventId, eventn.`Name`, eventn.`Start`, eventn.`End`, eventcalendar.CalendarId " \ "from `user` " \ "join usercalendars on usercalendars.UserId = `user`.UserId " \ "join eventcalendar on eventcalendar.CalendarId = usercalendars.CalendarId " \ "join eventn on eventn.EventId = eventcalendar.EventId " \ "where `user`.UserId = %s and eventn.EventId = %s" cursor.execute(sql, ( userId, eventId, )) result = cursor.fetchall() if len(result) != 1: return -1 event = { "id": result[0][0], "name": result[0][1], "start": result[0][2], "end": result[0][3], "calendarId": result[0][4], } return event except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def getTask(taskId): # load root task attributes = "TaskId, Name, task.Interval, Deleted, IsDone, ParentId, CalendarId, Timestamp" cursor = getCursor() try: sql = "select {} from task where TaskId = %s or ParentId = %s;" sql = sql.format(attributes) cursor.execute(sql, (taskId, taskId)) result = cursor.fetchall() taskObject = {} taskObject["todos"] = [] for r in result: if r[0] == int(taskId): taskObject["task"] = {"id": r[0], "name": r[1], "interval": json.loads(r[2]), "deleted": r[3],\ "isDone": r[4], "parentId": r[5], "calendarId": r[6], "timestamp": r[7]} if r[5] == int(taskId): taskObject["todos"].append({"id": r[0], "name": r[1], "interval": json.loads(r[2]), "deleted": r[3],\ "isDone": r[4], "parentId": r[5], "calendarId": r[6], "timestamp": r[7]}) cursor.close() except mysql.connector.Error as err: printError(err) cursor.close() return -1 except BaseException as err: printError(err) cursor.close() return -1 return taskObject
def createNewCalendar(userId, calendarName, isPublic): database = getDatabase() cursor = getCursor() try: sql = "INSERT INTO calendar " \ "(Name, Public) " \ "VALUES (%s, %s) " cursor.execute(sql, (calendarName, isPublic)) calendarId = cursor.lastrowid except mysql.connector.Error as err: printError(err) cursor.close() return -1 try: sql = "INSERT INTO usercalendars " \ "(UserId, CalendarId, Adminlevel, Notifications) " \ "VALUES (%s, %s, %s, %s) " cursor.execute(sql, (userId, calendarId, 3, 0)) except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close() database.commit() return calendarId
def resetTask(taskId, newTimeStamp): # Will reset isDone and timestamp of task with id taskId and all children of that task database = getDatabase() cursor = getCursor() # # get task hierarchy ids = [taskId] try: for parentId in ids: sql = "select TaskId from task where parentId = %s;" cursor.execute(sql, (parentId, )) result = cursor.fetchall() ids += [t[0] for t in result] # # update timestamp and reset IsDone #timestamp = int(time.time() * 1000) for toBeResetId in ids: sql = "update annualcycle.task set IsDone = 0 where TaskId=%s;" cursor.execute(sql, (toBeResetId, )) sql = "update annualcycle.task set timestamp = {} where TaskId=%s;" sql = sql.format(newTimeStamp) cursor.execute(sql, (toBeResetId, )) except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: database.commit() cursor.close() return True
def getCalendarOfUser(userId, calendarId): cursor = getCursor() try: sql = "select calendar.CalendarId, calendar.Name, usercalendars.Adminlevel, calendar.Public " \ "from usercalendars join calendar " \ "on UserId = %s and usercalendars.CalendarId = calendar.CalendarId and calendar.CalendarId = %s;" cursor.execute(sql, ( userId, calendarId, )) resultTuple = cursor.fetchall() if len(resultTuple) != 1: return -1 result = { "id": resultTuple[0][0], "name": resultTuple[0][1], "adminLevel": resultTuple[0][2], "public": resultTuple[0][3] } return result except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: cursor.close()
def getEventsOfCalenderOfUser(userId, calendarId): cursor = getCursor() try: sql = "select eventn.EventId, eventn.`Name`, eventn.`Start`, eventn.`End` " \ "from `user` " \ "join usercalendars on usercalendars.UserId = `user`.UserId " \ "join eventcalendar on eventcalendar.CalendarId = usercalendars.CalendarId " \ "join eventn on eventn.EventId = eventcalendar.EventId " \ "where `user`.UserId = %s and eventcalendar.CalendarId = %s;" cursor.execute(sql, ( userId, calendarId, )) result = cursor.fetchall() events = [{ "id": eventTuple[0], "name": eventTuple[1], "start": eventTuple[2], "end": eventTuple[3] } for eventTuple in result] return events except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def resetPasswordOfUser(urlEnding, newHashedPassword): cursor = getCursor() database = getDatabase() try: # get userId of user to reset password to sql = "SELECT UserId FROM annualcycle.forgot_pass_link WHERE Url = %s and Deleted = 0;" cursor.execute(sql, (urlEnding, )) result = cursor.fetchall() if len(result) != 1: return -1 userId = result[0][0] # de-activate old link sql = "UPDATE `annualcycle`.`forgot_pass_link` SET `Deleted` = '1' WHERE UserId = %s;" cursor.execute(sql, (userId, )) # reset password sql = "UPDATE `annualcycle`.`user` SET `Password` = %s WHERE `UserId` = %s;" cursor.execute(sql, (newHashedPassword, userId)) database.commit() return True except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: cursor.close()
def createResetPassworLink(userId, urlEnding): cursor = getCursor() database = getDatabase() try: sql = "INSERT INTO `annualcycle`.`forgot_pass_link` " \ "(`UserId`, `Url`, `Deleted`) VALUES (%s, %s, %s);" cursor.execute(sql, (userId, urlEnding, 0)) database.commit() return cursor.lastrowid except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def getTasksOfUser(userId): ids = getCalendarIdsOfUser(userId) if ids == -1: return -1 # load root tasks attributes = "TaskId, Name, task.Interval, Deleted, IsDone, ParentId, CalendarId, Timestamp" result = [] cursor = getCursor() try: for calendarId in ids: sql = "select {} from task where calendarId = %s and task.Deleted = 0;" sql = sql.format(attributes) cursor.execute(sql, (calendarId, )) result += cursor.fetchall() newList = [] for r in result: newList.append({"id": r[0], "name": r[1], "interval": r[2], "deleted": r[3],\ "isDone": r[4], "parentId": r[5], "calendarId": r[6], "timestamp": r[7]}) # load children of root tasks tasks = newList[:] while True: temp = [] for t in newList: sql = "select {} from task where parentId = {};" sql = sql.format(attributes, t["id"]) cursor.execute(sql) result = cursor.fetchall() for r in result: temp.append({"id": r[0], "name": r[1], "interval": r[2], "deleted": r[3],\ "isDone": r[4], "parentId": r[5], "calendarId": t["calendarId"], "timestamp": r[7]}) if len(temp) == 0: break tasks += temp newList = temp except mysql.connector.Error as err: printError(err) cursor.close() return -1 except BaseException as err: printError(err) cursor.close() return -1 cursor.close() return tasks
def getUserEmail(userId): cursor = getCursor() database = getDatabase() try: # get userId of user to reset password to sql = "SELECT Email FROM annualcycle.user WHERE UserId = %s;" cursor.execute(sql, (userId, )) email = cursor.fetchall()[0][0] return email except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def createUser(email, hashedPassword, salt, nickname): database = getDatabase() cursor = getCursor() try: sql2 = "INSERT INTO user " \ "(Email, Password, Salt, Name) " \ "VALUES (%s, %s, %s, %s) " cursor.execute(sql2, (email, hashedPassword, salt, nickname)) database.commit() userId = cursor.lastrowid return userId except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def setTaskDone(taskId): database = getDatabase() cursor = getCursor() try: sql = "update annualcycle.task set IsDone = 1 where TaskId=%s;" cursor.execute(sql, (taskId, )) database.commit() except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: cursor.close() return True
def checkEmailInUse(email): cursor = getCursor() try: sql = "SELECT Deleted " \ "FROM user " \ "WHERE Email = %s " cursor.execute(sql, (email, )) result = cursor.fetchall() for r in result: if r[0] == 0: # user with same username exists that is not deleted return False return True except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def editCalendar(newCalendarName, newIsPublic, calendarId, userId): database = getDatabase() cursor = getCursor() try: sql = "UPDATE calendar " \ "SET Name = %s, Public = %s " \ "WHERE CalendarId = %s AND CalendarId = (SELECT CalendarId " \ "FROM usercalendars " \ "WHERE CalendarId = %s AND UserId = %s AND Adminlevel >= 2) " cursor.execute( sql, (newCalendarName, newIsPublic, calendarId, calendarId, userId)) database.commit() except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close() return True
def createNewTask(userId, name, description, interval, timestamp, calendarId, parentId): database = getDatabase() cursor = getCursor() try: sql = "INSERT INTO task " \ "(Name, Description, `Interval`, Timestamp, CalendarId, ParentId) " \ "VALUES (%s, %s, %s, %s, %s, %s);" cursor.execute( sql, (name, description, interval, timestamp, calendarId, parentId)) taskId = cursor.lastrowid database.commit() return taskId except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close()
def editEvent(eventId, userId, newName, newDescription, newStart, newEnd, newInterval, newCalendarId): database = getDatabase() cursor = getCursor() try: sql = "UPDATE eventn " \ "join eventcalendar on eventcalendar.EventId = eventn.EventId " \ "join usercalendars on usercalendars.CalendarId = eventcalendar.CalendarId " \ "join `user` on `user`.UserId = usercalendars.UserId " \ "Set eventn.Name = %s, eventn.Description = %s, eventn.Start = %s, eventn.End = %s, eventn.`Interval` = %s," \ "eventcalendar.CalendarId = %s " \ "where `user`.UserId = %s and usercalendars.Adminlevel >= 2 " \ "and eventn.EventId = %s;" cursor.execute(sql, (newName, newDescription, newStart, newEnd, newInterval, newCalendarId, userId, eventId)) database.commit() except mysql.connector.Error as err: printError(err) return -1 finally: cursor.close() return True
def getAllCalendarsOfUser(userId): # TODO: rename to getAllCalendarsOfUser cursor = getCursor() try: sql = "select calendar.CalendarId, calendar.Name, usercalendars.Adminlevel, calendar.Public " \ "from usercalendars join calendar " \ "on UserId = %s and usercalendars.CalendarId = calendar.CalendarId;" cursor.execute(sql, (userId, )) result = cursor.fetchall() result = [{ "id": calendarTuple[0], "name": calendarTuple[1], "adminLevel": calendarTuple[2], "public": calendarTuple[3] } for calendarTuple in result] return result except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: cursor.close()
def getUserIdPassword(email): # If the user exists, (userId, hashedPasswordFromDatabase) is returned, # if the username does not exist, False is returned, # if an error occurs, -1 is returned cursor = getCursor() try: sql = "SELECT Password, UserId " \ "FROM user " \ "WHERE Email = %s " cursor.execute(sql, (email, )) result = cursor.fetchall() if len(result) != 1: return -1 hashedPasswordFromDatabase = result[0][0] userId = result[0][1] return (userId, hashedPasswordFromDatabase) except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: cursor.close()
def createNewEvent( name, startTime, endTime, calendarId): #TODO: remove ToDatabase from name, also add->create # TODO: security, does this user have access to this calendar? # startTime and endTime is given in iso format database = getDatabase() cursor = getCursor() # add event in database try: sql = "INSERT INTO eventn " \ "(Name, Start, End) " \ "VALUES (%s, %s, %s) " cursor.execute(sql, (name, startTime, endTime)) eventId = cursor.lastrowid except mysql.connector.Error as err: printError(err) cursor.close() return -1 except BaseException as err: printError(err) cursor.close() return -1 # add calendar event connection try: sql = "INSERT INTO eventcalendar " \ "(EventId, CalendarId) " \ "VALUES (%s, %s) " cursor.execute(sql, (eventId, calendarId)) database.commit() return eventId except mysql.connector.Error as err: printError(err) return -1 except BaseException as err: printError(err) return -1 finally: cursor.close() return True
def deleteTask(userId, rootTaskId): # load root tasks ids = [rootTaskId] cursor = getCursor() database = getDatabase() try: # get calendarId of root task sql = "SELECT task.calendarId, usercalendars.UserId FROM task " \ "JOIN usercalendars ON task.CalendarId = usercalendars.CalendarId " \ "WHERE task.taskId = %s;" cursor.execute(sql, (rootTaskId, )) result = cursor.fetchall() calendarId = result[0][0] tasksUserId = result[0][1] # correct user? if userId != tasksUserId: printError( "Cannot delete task, userId not authorized. UserId = {}. TaskId = {}." .format(userId, rootTaskId)) cursor.close() return -1 # get Ids of sub-tasks for parentId in ids: sql = "select TaskId from task where parentId = %s;" cursor.execute(sql, (parentId, )) result = cursor.fetchall() ids += [childTupple[0] for childTupple in result] # set deleted flag for delTaskId in ids: '''sql = "UPDATE task " \ "join usercalendars on usercalendars.CalendarId = %s " \ "join `user` on usercalendars.UserId = `user`.UserId = usercalendars.UserId " \ "SET task.`Deleted` = 1 WHERE `TaskId` = %s and `user`.UserId = %s;"''' sql = "UPDATE task SET task.`Deleted` = 1 WHERE TaskId = %s" cursor.execute(sql, (delTaskId, )) database.commit() except mysql.connector.Error as err: printError(err) cursor.close() return -1 except BaseException as err: printError(err) cursor.close() return -1 return True