def viewJobs(win):
def clickSkills():
jobId = jobIds[int(listBox.curselection()[0])]
ui.window("Job Skills", lambda win: viewSkills(win, jobId, "Job"))
def clickStatus(cursor, newStatus):
selected = int(listBox.curselection()[0])
jobId = jobIds[selected]
cursor.execute(
"""UPDATE jobs
SET status = ?, date = DATE('now')
WHERE jobId = ?
""", [newStatus, jobId])
if status.get() != newStatus:
jobIds.pop(selected)
listBox.delete(selected)
def displayJobs(cursor):
jobIds.clear()
jobs = cursor.execute(
"""SELECT jobId, date, assigneeId, customerId, boatId, description
FROM jobs
WHERE status = ?
ORDER BY date DESC
""", [status.get()]).fetchall()
jobIds.extend([job[0] for job in jobs])
ui.displayList(jobs, listBox)
def reassignJob(cursor):
jobId = jobIds[int(listBox.curselection()[0])]
# Remove assignee.
cursor.execute("UPDATE jobs SET assigneeId = ? WHERE jobId = ?",
[None, jobId])
# Get and add new assignee.
cursor.execute("UPDATE jobs SET assigneeId = ? WHERE jobId = ?",
[getAssignee(cursor, jobId), jobId])
displayJobs(cursor)
jobIds = []
# Make UI.
ui.button(win, "Add", lambda: ui.window("Add Job", addJob))
ui.button(win, "Reassign", accessDb(reassignJob))
ui.button(win, "Skills", clickSkills)
ui.button(win, "Incomplete",
accessDb(lambda cursor: clickStatus(cursor, "Incomplete")))
ui.button(win, "Complete",
accessDb(lambda cursor: clickStatus(cursor, "Complete")))
ui.button(win, "Paid",
accessDb(lambda cursor: clickStatus(cursor, "Paid")))
ui.button(win, "Refresh", accessDb(displayJobs))
status = ui.dropDown(win, ["Incomplete", "Complete", "Paid"],
lambda evt: accessDb(displayJobs)())
listBox = ui.listBox(
win, ["Job Id", "Date", "Assignee", "Customer", "Boat", "Description"],
30)
accessDb(displayJobs)()
def viewSkills(win, id, table):
def addSkill(cursor):
results = cursor.execute(
"SELECT * FROM " + table.lower() + "s WHERE " + table + "Id = ?",
[id]).fetchall()
if (len(results) != 0):
cursor.execute(
"""INSERT INTO skills(id, skill, job) VALUES(?, ?, ?)""",
[id, skill.get(), table == "Job"])
listBox.insert(ui.END, skill.get())
skill.delete(0, ui.END)
else:
raise Exception("ID no longer exists.")
def displaySkills(cursor):
skills = cursor.execute(
"SELECT skill FROM skills WHERE id = ? AND job = ?"
"", [id, table == "Job"]).fetchall()
ui.displayList(skills, listBox)
# Make UI.
skill = ui.textBox(win, 0, 0)
ui.button(win, "Add", accessDb(addSkill))
listBox = ui.listBox(win, ["Skill name"])
accessDb(displaySkills)()
win.title(table + " " + str(id))
def viewEmployees(win):
def clickSkills():
employeeId = employeeIds[int(listBox.curselection()[0])]
ui.window("Employee Skills",
lambda win: viewSkills(win, employeeId, "Employee"))
def addEmployee(cursor):
cursor.execute("""INSERT INTO employees(name) VALUES(?)""",
[name.get()])
employees = cursor.execute(
"""SELECT employeeId FROM employees""").fetchall()
employeeId = employees[len(employees) - 1][0]
employeeIds.append(employeeId)
listBox.insert(ui.END, ui.list2String([employeeId, name.get()]))
name.delete(0, ui.END)
def removeEmployee(cursor):
selected = int(listBox.curselection()[0])
employeeId = employeeIds[selected]
cursor.execute(
"UPDATE jobs SET assigneeId = NULL WHERE assigneeId = ?",
[employeeId])
cursor.execute("DELETE FROM skills WHERE id = ? AND job = 0",
[employeeId])
cursor.execute("DELETE FROM employees WHERE employeeId = ?",
[employeeId])
freeJobs = cursor.execute(
"SELECT jobId FROM jobs WHERE assigneeId IS NULL AND status = 'Incomplete'"
).fetchall()
for job in freeJobs:
cursor.execute("UPDATE jobs SET assigneeId = ? WHERE jobId = ?",
[getAssignee(cursor, job[0]), job[0]])
employeeIds.pop(selected)
listBox.delete(selected)
def displayEmployees(cursor):
employeeIds.clear()
employees = cursor.execute("SELECT employeeId, name FROM employees"
"").fetchall()
employeeIds.extend([employee[0] for employee in employees])
ui.displayList(employees, listBox)
employeeIds = []
# Make UI.
name = ui.textBox(win, 0, 0)
ui.button(win, "Add", accessDb(addEmployee))
ui.button(win, "Remove", accessDb(removeEmployee))
ui.button(win, "Skills", clickSkills)
ui.button(win, "Refresh", accessDb(displayEmployees))
listBox = ui.listBox(win, ["Employee Id", "Name"])
accessDb(displayEmployees)()
def viewHolidays(win):
def displayHolidays(cursor):
holidays = cursor.execute("""SELECT date, assigneeId, workHours
FROM jobs
WHERE status = 'Holiday'
ORDER BY date DESC
""").fetchall()
ui.displayList(holidays, listBox)
# Make UI.
ui.button(win, "Add", lambda: ui.window("Add Holiday", addHoliday))
ui.button(win, "Refresh", accessDb(displayHolidays))
listBox = ui.listBox(win, ["Date", "Employee", "Work hours"])
accessDb(displayHolidays)()
def viewJobs(win):
def clickSkills():
jobId = jobIds[int(listBox.curselection()[0])]
ui.window("Job Skills", lambda win: viewSkills(win, jobId, "Job"))
def clickStatus(cursor, newStatus):
selected = int(listBox.curselection()[0])
jobId = jobIds[selected]
cursor.execute("""UPDATE jobs
SET status = ?, date = DATE('now')
WHERE jobId = ?
""", [newStatus, jobId])
if status.get() != newStatus:
jobIds.pop(selected)
listBox.delete(selected)
def displayJobs(cursor):
jobIds.clear()
jobs = cursor.execute("""SELECT jobId, date, assigneeId, customerId, boatId, description
FROM jobs
WHERE status = ?
ORDER BY date DESC
""", [status.get()]).fetchall()
jobIds.extend([job[0] for job in jobs])
ui.displayList(jobs, listBox)
def reassignJob(cursor):
jobId = jobIds[int(listBox.curselection()[0])]
# Remove assignee.
cursor.execute("UPDATE jobs SET assigneeId = ? WHERE jobId = ?", [None, jobId])
# Get and add new assignee.
cursor.execute("UPDATE jobs SET assigneeId = ? WHERE jobId = ?", [getAssignee(cursor, jobId), jobId])
displayJobs(cursor)
jobIds = []
# Make UI.
ui.button(win, "Add", lambda: ui.window("Add Job", addJob))
ui.button(win, "Reassign", accessDb(reassignJob))
ui.button(win, "Skills", clickSkills)
ui.button(win, "Incomplete", accessDb(lambda cursor: clickStatus(cursor, "Incomplete")))
ui.button(win, "Complete", accessDb(lambda cursor: clickStatus(cursor, "Complete")))
ui.button(win, "Paid", accessDb(lambda cursor: clickStatus(cursor, "Paid")))
ui.button(win, "Refresh", accessDb(displayJobs))
status = ui.dropDown(win, ["Incomplete", "Complete", "Paid"], lambda evt: accessDb(displayJobs)())
listBox = ui.listBox(win, ["Job Id", "Date", "Assignee", "Customer", "Boat", "Description"], 30)
accessDb(displayJobs)()
def viewHolidays(win):
def displayHolidays(cursor):
holidays = cursor.execute("""SELECT date, assigneeId, workHours
FROM jobs
WHERE status = 'Holiday'
ORDER BY date DESC
""").fetchall()
ui.displayList(holidays, listBox)
# Make UI.
ui.button(win, "Add", lambda: ui.window("Add Holiday", addHoliday))
ui.button(win, "Refresh", accessDb(displayHolidays))
listBox = ui.listBox(win, ["Date", "Employee", "Work hours"])
accessDb(displayHolidays)()
def viewSkills(win, id, table):
def addSkill(cursor):
results = cursor.execute("SELECT * FROM " + table.lower() + "s WHERE " + table + "Id = ?", [id]).fetchall()
if (len(results) != 0):
cursor.execute("""INSERT INTO skills(id, skill, job) VALUES(?, ?, ?)""", [id, skill.get(), table == "Job"])
listBox.insert(ui.END, skill.get())
skill.delete(0, ui.END)
else:
raise Exception("ID no longer exists.")
def displaySkills(cursor):
skills = cursor.execute("SELECT skill FROM skills WHERE id = ? AND job = ?""", [id, table == "Job"]).fetchall()
ui.displayList(skills, listBox)
# Make UI.
skill = ui.textBox(win, 0, 0)
ui.button(win, "Add", accessDb(addSkill))
listBox = ui.listBox(win, ["Skill name"])
accessDb(displaySkills)()
win.title(table + " " + str(id))
def viewEmployees(win):
def clickSkills():
employeeId = employeeIds[int(listBox.curselection()[0])]
ui.window("Employee Skills", lambda win: viewSkills(win, employeeId, "Employee"))
def addEmployee(cursor):
cursor.execute("""INSERT INTO employees(name) VALUES(?)""", [name.get()])
employees = cursor.execute("""SELECT employeeId FROM employees""").fetchall()
employeeId = employees[len(employees) - 1][0]
employeeIds.append(employeeId)
listBox.insert(ui.END, ui.list2String([employeeId, name.get()]))
name.delete(0, ui.END)
def removeEmployee(cursor):
selected = int(listBox.curselection()[0])
employeeId = employeeIds[selected]
cursor.execute("UPDATE jobs SET assigneeId = NULL WHERE assigneeId = ?", [employeeId])
cursor.execute("DELETE FROM skills WHERE id = ? AND job = 0", [employeeId])
cursor.execute("DELETE FROM employees WHERE employeeId = ?", [employeeId])
freeJobs = cursor.execute("SELECT jobId FROM jobs WHERE assigneeId IS NULL AND status = 'Incomplete'").fetchall()
for job in freeJobs:
cursor.execute("UPDATE jobs SET assigneeId = ? WHERE jobId = ?", [getAssignee(cursor, job[0]), job[0]])
employeeIds.pop(selected)
listBox.delete(selected)
def displayEmployees(cursor):
employeeIds.clear()
employees = cursor.execute("SELECT employeeId, name FROM employees""").fetchall()
employeeIds.extend([employee[0] for employee in employees])
ui.displayList(employees, listBox)
employeeIds = []
# Make UI.
name = ui.textBox(win, 0, 0)
ui.button(win, "Add", accessDb(addEmployee))
ui.button(win, "Remove", accessDb(removeEmployee))
ui.button(win, "Skills", clickSkills)
ui.button(win, "Refresh", accessDb(displayEmployees))
listBox = ui.listBox(win, ["Employee Id", "Name"])
accessDb(displayEmployees)()
