def modify_task(taskID,
taskName=None,
description=None,
state=None,
employeeID=None,
timeCreated=None,
timeCompleted=None):
if not isinstance(taskID, str):
taskID = str(taskID)
if not isinstance(taskName, str):
taskName = str(taskName)
if not isinstance(description, str):
description = str(description)
if not isinstance(state, str):
state = str(state)
if not isinstance(employeeID, str):
employeeID = str(employeeID)
q = "UPDATE SE_DB.tasks SET taskName = \"" + taskName + "\""
q = q + ", description = \"" + description + "\""
q = q + ", state = \"" + state + "\""
q = q + ", employeeID = \"" + employeeID + "\""
q = q + ", timeCreated = \"" + timeCreated + "\""
q = q + ", timeCompleted = \"" + timeCompleted + "\""
q = q + " WHERE taskID = " + taskID + ";"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def remove_task(taskID):
if not isinstance(taskID, str):
taskID = str(taskID)
q = "DELETE FROM SE_DB.tasks WHERE taskID = " + taskID + ";"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def remove_slist_item(user, RFID):
if not isinstance(user, str):
user = str(user)
if not isinstance(RFID, str):
RFID = str(RFID)
q = "DELETE FROM SE_DB.shoppingList WHERE username = \"" + user + "\" and item = " + RFID
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def add_slist_item(user, RFID):
if not isinstance(user, str):
user = str(user)
if not isinstance(RFID, str):
RFID = str(RFID)
q = "INSERT INTO SE_DB.shoppingList(username, item) VALUES('" + user + "', '" + RFID + "');"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def add_hours(employeeID):
if not isinstance(employeeID, str):
employeeID = str(employeeID)
if (not employeeID.isdecimal()):
return
current_time = str(datetime.datetime.now())
q = "INSERT INTO SE_DB.hours (employeeID, checkIn) VALUES (" + employeeID + ", '" + current_time + "');"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def add_checkout(employeeID):
if not isinstance(employeeID, str):
employeeID = str(employeeID)
if (not employeeID.isdecimal()):
return
current_time = str(datetime.datetime.now())
q = "UPDATE SE_DB.hours SET checkOut = '" + current_time + "' WHERE hours.checkOut is NULL and employeeID = " + employeeID + " LIMIT 1;"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def add_task(taskName, description):
if not isinstance(taskName, str):
taskName = str(taskName)
if not isinstance(description, str):
description = str(description)
current_time = str(datetime.datetime.now())
q = "INSERT INTO SE_DB.tasks(taskName, description, state, timeCreated) VALUES('" + taskName
q = q + "', '" + description + "'," + "'Incomplete','" + current_time + "');"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def create_user(username,
password,
accountType,
ID="NULL",
first=None,
last=None):
q = "INSERT INTO SE_DB.logins (username, password, accountType, ID) VALUES ('" + username + "','" + password + "','" + accountType + "'," + str(
ID) + ");"
c = db_util.db_open()
db_util.db_execute(c, q)
if (first is not None and last is not None and ID != "NULL"):
q = "INSERT INTO SE_DB.employees (lastName, firstName, ID, role) VALUES ('" + first + "','" + last + "'," + str(
ID) + ",'" + accountType + "');"
db_util.db_execute(c, q)
db_util.db_close(c)
def update_task_state(taskID, new_state, employeeID):
if not isinstance(taskID, str):
taskID = str(taskID)
if not isinstance(new_state, str):
new_state = str(new_state)
if not isinstance(employeeID, str):
employeeID = str(employeeID)
q = "UPDATE SE_DB.tasks SET state = \"" + new_state + "\""
if new_state == "Complete":
current_time = str(datetime.datetime.now())
q = q + ", timeCompleted = \'" + current_time + "\'"
elif new_state == "In Progress":
q = q + ", employeeID = " + employeeID
q = q + " WHERE taskID = " + taskID + ";"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)
def randomly_assign_tasks():
q = "UPDATE SE_DB.tasks SET employeeID = (select SE_DB.hours.employeeID FROM SE_DB.hours where SE_DB.hours.checkOut is Null order by RAND() limit 1), state ='In Progress' WHERE state = 'Incomplete';"
c = db_util.db_open()
db_util.db_execute(c, q)
db_util.db_close(c)