def Delete(cls, dependency_id):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "DELETE FROM dependency WHERE Id = %s"
cursor.execute(sql, (dependency_id))
connection.commit()
finally:
connection.close()
def Delete(cls, location_id):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "DELETE FROM location WHERE Id = %s"
cursor.execute(sql, (location_id))
connection.commit()
finally:
connection.close()
def Add(cls, location):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """INSERT INTO `location` (`ScheduleId`, `Name`, `Lat`, `Long`)
VALUES (%s, %s, %s, %s)"""
cursor.execute(sql, (location.ScheduleId, location.Name, location.Lat, location.Long))
connection.commit()
finally:
connection.close()
def Update(cls, location):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """UPDATE `location` SET `Name` = %s, `ScheduleId` = %s, `Lat` = %s, `Long` = %s
WHERE Id = %s"""
cursor.execute(sql, (location.Name, location.ScheduleId, location.Lat, location.Long, location.Id))
connection.commit()
finally:
connection.close()
def GetById(cls, activityId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM activity WHERE Id=%s"
cursor.execute(sql, (str(activityId)))
result = cursor.fetchone()
activity = None if result is None else Activity(**result)
return activity
finally:
connection.close()
def GetByLocationId(cls, locationId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM activity WHERE LocationId=%s"
cursor.execute(sql, (str(locationId)))
results = cursor.fetchmany(cursor.rowcount)
activityList = [Activity(**result) for result in results]
return activityList
finally:
connection.close()
def GetByScheduleId(cls, ScheduleId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM location WHERE ScheduleId=%s"
cursor.execute(sql, (str(ScheduleId)))
results = cursor.fetchmany(cursor.rowcount)
locationList = [Location(**result) for result in results]
return locationList
finally:
connection.close()
def GetById(cls, Id):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM location WHERE Id=%s"
cursor.execute(sql, (str(Id)))
result = cursor.fetchone()
location = None if result is None else Location(**result)
return location
finally:
connection.close()
def GetById(cls, dependencyId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM dependency WHERE Id=%s"
cursor.execute(sql, (str(dependencyId)))
result = cursor.fetchone()
dependency = None if result is None else Dependency(**result)
return dependency
finally:
connection.close()
def Update(cls, dependency):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """UPDATE `dependency` SET `ActivityId` = %s, `PredActivityId` = %s, `TypeId` = %s, `Length` = %s
WHERE Id = %s"""
cursor.execute(
sql, (dependency.ActivityId, dependency.PredActivityId,
dependency.TypeId, dependency.Length, dependency.Id))
connection.commit()
finally:
connection.close()
def DeleteSuccessors(cls, activityId, position):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "DELETE dependency FROM dependency \
INNER JOIN activity ON dependency.PredActivityId = activity.Id \
WHERE dependency.ActivityId = %s AND activity.Pos > %s"
cursor.execute(sql, (activityId, position))
connection.commit()
finally:
connection.close()
def Add(cls, dependency):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """INSERT INTO `dependency` (`ActivityId`, `PredActivityId`, `TypeId`, `Length`) VALUES (%s,
%s, %s, %s) """
cursor.execute(
sql, (dependency.ActivityId, dependency.PredActivityId,
dependency.TypeId, dependency.Length))
connection.commit()
finally:
connection.close()
def GetStatusTypes(cls):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT Id, Name FROM status_type"
cursor.execute(sql)
results = cursor.fetchmany(cursor.rowcount)
# Convert list of dicts to list of classes
statusTypeList = [StatusType(**result) for result in results]
return statusTypeList
finally:
connection.close()
def Update(cls, activity):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """UPDATE `activity` SET `ScheduleId` = %s, `LocationId` = %s, `ActivityTypeId` = %s, `Name` = %s,
`Duration` = %s
WHERE Id = %s"""
cursor.execute(sql, (activity.ScheduleId, activity.LocationId,
activity.ActivityTypeId, activity.Name,
activity.Duration, activity.Id))
connection.commit()
finally:
connection.close()
def GetDependencyTypes(cls):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM dependency_type"
cursor.execute(sql)
results = cursor.fetchmany(cursor.rowcount)
dependencyTypeList = [
DependencyType(**result) for result in results
]
return dependencyTypeList
finally:
connection.close()
def GetPredecessors(cls, activityId, scheduleId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT * FROM activity
WHERE ScheduleId = %s AND
activity.Pos < (SELECT pos FROM activity WHERE activity.Id = %s)"""
cursor.execute(sql, (str(scheduleId), str(activityId)))
results = cursor.fetchmany(cursor.rowcount)
activityList = [Activity(**result) for result in results]
return activityList
finally:
connection.close()
def GetAll(cls):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT schedule.*, status_type.Name AS StatusName FROM schedule
INNER JOIN status_type ON status_type.Id = schedule.StatusTypeId
ORDER BY schedule.Name"""
cursor.execute(sql)
results = cursor.fetchmany(cursor.rowcount)
scheduleList = [Schedule(**result) for result in results]
return scheduleList
finally:
connection.close()
def GetByScheduleId(cls, scheduleId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT dependency.* FROM dependency
INNER JOIN activity ON activity.id = dependency.ActivityId
WHERE activity.ScheduleId = %s"""
cursor.execute(sql, (str(scheduleId)))
results = cursor.fetchmany(cursor.rowcount)
dependencyList = [Dependency(**result) for result in results]
return dependencyList
finally:
connection.close()
def GetPredByActivityId(cls, activityId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT *
FROM dependency
WHERE dependency.PredActivityId = %s"""
cursor.execute(sql, (str(activityId)))
results = cursor.fetchmany(cursor.rowcount)
dependencyList = [Dependency(**result) for result in results]
return dependencyList
finally:
connection.close()
def UpdatePositions(cls, scheduleId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SET @pos:=0"
cursor.execute(sql)
sql = """UPDATE activity
SET Pos=@pos:=@pos+1
WHERE ScheduleId = %s
ORDER BY Pos"""
cursor.execute(sql, (scheduleId))
connection.commit()
finally:
connection.close()
def GetByLatLong(self, ActivityTypeId, lat, long):
latRounded = round(lat, 1)
longRounded = round(long, 1)
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM parameter WHERE ActivityTypeId=%s AND Lat=%s AND parameter.Long=%s"
cursor.execute(
sql,
(str(ActivityTypeId), str(latRounded), str(longRounded)))
result = cursor.fetchone()
parameter = None if result is None else Parameter(**result)
return parameter
finally:
connection.close()
def GetActivityTypes(cls):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT * FROM activity_type
ORDER BY pos"""
cursor.execute(sql)
results = cursor.fetchmany(cursor.rowcount)
# Convert list of dicts to list of classes
activityTypeList = [
ActivityType(**result) for result in results
]
return activityTypeList
finally:
connection.close()
def GetByActivityId(cls, activityId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT dependency.*, dependency_type.Name AS DependencyName, activity.Name AS PredessesorName
FROM dependency
INNER JOIN dependency_type ON dependency.TypeId = dependency_type.Id
INNER JOIN activity ON dependency.PredActivityId = activity.Id
WHERE dependency.ActivityId = %s"""
cursor.execute(sql, (str(activityId)))
results = cursor.fetchmany(cursor.rowcount)
dependencyList = [Dependency(**result) for result in results]
return dependencyList
finally:
connection.close()
def Update(cls, schedule):
connection = Common.getconnection()
schedule = ScheduleService.CheckWorkingDays(schedule)
try:
with connection.cursor() as cursor:
sql = """UPDATE `schedule` SET `Name` = %s, `StartDate` = %s, `WorkingDay0` = %s, `WorkingDay1` = %s,
`WorkingDay2` = %s, `WorkingDay3` = %s, `WorkingDay4` = %s, `WorkingDay5` = %s,
`WorkingDay6` = %s, `StatusTypeId` = %s, `StatusDate` = %s WHERE Id = %s """
cursor.execute(
sql,
(schedule.Name, schedule.StartDate, schedule.WorkingDay0,
schedule.WorkingDay1, schedule.WorkingDay2,
schedule.WorkingDay3, schedule.WorkingDay4,
schedule.WorkingDay5, schedule.WorkingDay6,
schedule.StatusTypeId, schedule.StatusDate, schedule.Id))
connection.commit()
finally:
connection.close()
def Add(cls, schedule):
connection = Common.getconnection()
schedule = ScheduleService.CheckWorkingDays(schedule)
try:
with connection.cursor() as cursor:
sql = """INSERT INTO `schedule` (`Name`, `StartDate`, `WorkingDay0`, `WorkingDay1`, `WorkingDay2`,
`WorkingDay3`, `WorkingDay4`, `WorkingDay5`, `WorkingDay6`, `StatusTypeId`, `StatusDate`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """
cursor.execute(
sql,
(schedule.Name, schedule.StartDate, schedule.WorkingDay0,
schedule.WorkingDay1, schedule.WorkingDay2,
schedule.WorkingDay3, schedule.WorkingDay4,
schedule.WorkingDay5, schedule.WorkingDay6,
schedule.StatusTypeId, schedule.StatusDate))
connection.commit()
finally:
connection.close()
def GetByScheduleId(cls, scheduleId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = """SELECT activity.*, activity_type.Name AS ActivityTypeName, location.Name AS LocationName
FROM activity
INNER JOIN activity_type ON activity.ActivityTypeId = activity_type.Id
INNER JOIN location ON activity.LocationId = location.Id
WHERE activity.ScheduleId=%s
ORDER BY pos"""
cursor.execute(sql, (str(scheduleId)))
results = cursor.fetchmany(cursor.rowcount)
# Convert list of dicts to list of classes
activityList = [Activity(**result) for result in results]
return activityList
finally:
connection.close()
def GetById(cls, uid):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM schedule WHERE Id=%s"
cursor.execute(sql, (str(uid)))
result = cursor.fetchone()
schedule = None if result is None else Schedule(**result)
schedule = cls.__GetWorkingDays(schedule)
schedule.StartDateDisplay = schedule.StartDate.strftime(
"%d/%m/%Y")
if schedule.StatusDate is not None:
schedule.StatusDateDisplay = schedule.StatusDate.strftime(
"%d/%m/%Y")
return schedule
finally:
connection.close()
def SetNewPos(cls, newPosId, activityId, scheduleId):
newPos = 0
if newPosId != 0:
activity = cls.GetById(newPosId)
newPos = activity.Pos + 0.5
else:
newPos = 0.5
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "UPDATE activity SET Pos=%s where Id = %s"
cursor.execute(sql, (newPos, activityId))
connection.commit()
finally:
connection.close()
cls.UpdatePositions(scheduleId)
cls.DeleteSuccessors(activityId, int(newPos + 0.5))
def Add(cls, activity, scheduleId):
connection = Common.getconnection()
try:
with connection.cursor() as cursor:
sql = "SET @top:= (SELECT MAX(Pos) + 1 FROM activity WHERE scheduleId = %s)"
cursor.execute(sql, (scheduleId))
sql = "SET @top = IF(@TOP IS NULL, 1, @TOP)"
cursor.execute(sql)
sql = """INSERT INTO `activity` (`Name`, `Duration`, `ScheduleId`, `LocationId`, `ActivityTypeId`,
`Pos`)
VALUES (%s, %s, %s, %s, %s, @top)"""
cursor.execute(
sql,
(activity.Name, activity.Duration, activity.ScheduleId,
activity.LocationId, activity.ActivityTypeId))
connection.commit()
return cursor.lastrowid
finally:
connection.close()
def GetSuccessors(cls, activityId, newPosId):
connection = Common.getconnection()
newPos = 0
if newPosId != 0:
activity = cls.GetById(newPosId)
newPos = activity.Pos
try:
with connection.cursor() as cursor:
sql = """SELECT dependency.* FROM dependency
INNER JOIN activity ON activity.Id = dependency.PredActivityId
WHERE dependency.ActivityId = %s AND activity.Pos > %s"""
cursor.execute(sql, (activityId, newPos))
results = cursor.fetchmany(cursor.rowcount)
dependencyList = [
Dependency.Dependency(**result) for result in results
]
return dependencyList
finally:
connection.close()
