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 get_location(id):
id = str(id)
q = "SELECT location FROM SE_DB.stock WHERE itemRFID = " + id + " AND location != 'Back Room';"
c = db_util.db_open()
info = db_util.db_query(c, q)
db_util.db_close(c)
if (len(info) == 1):
return info[0][0]
else:
q = "SELECT location FROM SE_DB.stock WHERE itemRFID = " + id + " AND location = 'Back Room';"
c = db_util.db_open()
info = db_util.db_query(c, q)
db_util.db_close(c)
if (len(info) == 1):
return info[0][0]
else:
return ""
def get_password(username):
q = "SELECT password FROM SE_DB.logins WHERE username = '******';"
c = db_util.db_open()
password = db_util.db_query(c, q)
db_util.db_close(c)
if (len(password) == 1):
return password[0][0]
else:
return ""
def get_account_type(username):
q = "SELECT accountType FROM SE_DB.logins WHERE username = '******';"
c = db_util.db_open()
type = db_util.db_query(c, q)
db_util.db_close(c)
if (len(type) == 1):
return type[0][0]
else:
return ""
def get_id(username):
q = "SELECT ID FROM SE_DB.logins WHERE username = '******';"
c = db_util.db_open()
id = db_util.db_query(c, q)
db_util.db_close(c)
if (len(id) == 1):
return id[0][0]
else:
return ""
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 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 get_item_info(RFID):
if not isinstance(RFID, str):
RFID = str(RFID)
if (not RFID.isdecimal()):
return []
q = "SELECT name, price FROM SE_DB.items WHERE RFID = " + RFID + ";"
c = db_util.db_open()
info = db_util.db_query(c, q)
db_util.db_close(c)
return info
def get_id_null_hours(employeeID):
if not isinstance(employeeID, str):
employeeID = str(employeeID)
if (not employeeID.isdecimal()):
return []
q = "SELECT * FROM SE_DB.hours WHERE hours.checkOut is NULL and employeeID = " + employeeID + " LIMIT 1"
c = db_util.db_open()
hours = db_util.db_query(c, q)
db_util.db_close(c)
return hours
def get_id_employee_hours(employeeID):
if not isinstance(employeeID, str):
employeeID = str(employeeID)
if (not employeeID.isdecimal()):
return []
q = "SELECT day, hours FROM SE_DB.hoursList WHERE employeeID = " + employeeID + " ORDER BY day DESC"
c = db_util.db_open()
hours = db_util.db_query(c, q)
db_util.db_close(c)
return hours
def get_task(taskID):
if not isinstance(taskID, str):
taskID = str(taskID)
if len(taskID) < 1:
return []
q = "SELECT * FROM SE_DB.tasks WHERE taskID = " + taskID + ";"
c = db_util.db_open()
task = db_util.db_query(c, q)
db_util.db_close(c)
return task
def get_employee(employeeID):
if (not employeeID.isdecimal()):
return []
if not isinstance(employeeID, str):
employeeID = str(employeeID)
q = "SELECT lastName, firstName FROM SE_DB.employees WHERE ID = " + employeeID + ";"
c = db_util.db_open()
employee = db_util.db_query(c, q)
db_util.db_close(c)
return employee
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_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_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 get_employee_tasks(employeeID):
if (not employeeID.isdecimal()):
return []
if not isinstance(employeeID, str):
employeeID = str(employeeID)
if len(employeeID) < 1:
return []
q = "SELECT * FROM SE_DB.tasks WHERE state = \"In Progress\" AND employeeID = " + employeeID + ";"
c = db_util.db_open()
tasks = db_util.db_query(c, q)
db_util.db_close(c)
return tasks
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 get_all_item_sales():
q = "SELECT date, itemType, amountSold FROM SE_DB.salesCategories"
c = db_util.db_open()
item_sales = db_util.db_query(c, q)
db_util.db_close(c)
return item_sales
def get_user_shopping_list(user):
q = "SELECT name, price, item FROM SE_DB.userShoppingList WHERE username = \"" + user + "\""
c = db_util.db_open()
list = db_util.db_query(c, q)
db_util.db_close(c)
return list
def get_items():
q = "SELECT name, brand, price FROM SE_DB.items"
c = db_util.db_open()
info = db_util.db_query(c, q)
db_util.db_close(c)
return info
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)
def get_all_money_sales():
q = "SELECT day, totalSales FROM SE_DB.salesDaily"
c = db_util.db_open()
money_sales = db_util.db_query(c, q)
db_util.db_close(c)
return money_sales
def get_all_employee_hours():
q = "SELECT employeeID, employeeName, day, hours FROM SE_DB.hoursList ORDER BY day DESC"
c = db_util.db_open()
hours = db_util.db_query(c, q)
db_util.db_close(c)
return hours
def get_incomplete_tasks():
q = "SELECT * FROM SE_DB.tasks WHERE state = \"Incomplete\";"
c = db_util.db_open()
tasks = db_util.db_query(c, q)
db_util.db_close(c)
return tasks
def get_inprogress_tasks():
q = "SELECT * FROM SE_DB.tasks WHERE state = \"In Progress\";"
c = db_util.db_open()
tasks = db_util.db_query(c, q)
db_util.db_close(c)
return tasks
def get_latest_employee_hours():
q = "SELECT h.employeeID, h.employeeName, h.day, h.hours FROM SE_DB.hoursList h INNER JOIN (SELECT MAX(day) as maxDay FROM SE_DB.hoursList) m WHERE h.day = m.maxDay ORDER BY day DESC"
c = db_util.db_open()
hours = db_util.db_query(c, q)
db_util.db_close(c)
return hours
def get_noncompleted_tasks():
q = "SELECT * FROM SE_DB.taskList WHERE state != \"Complete\";"
c = db_util.db_open()
tasks = db_util.db_query(c, q)
db_util.db_close(c)
return tasks
def get_search_result(query):
q = "SELECT name, brand, price, RFID FROM SE_DB.items WHERE name like \"%" + query + "%\""
c = db_util.db_open()
info = db_util.db_query(c, q)
db_util.db_close(c)
return info
