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_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 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_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_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 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_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_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 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_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_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_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_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_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_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_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
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 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_all_tasks():
q = "SELECT * FROM SE_DB.taskList;"
c = db_util.db_open()
tasks = db_util.db_query(c, q)
db_util.db_close(c)
return tasks