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 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 clickSkills():
jobId = jobIds[int(listBox.curselection()[0])]
ui.window("Job Skills", lambda win: viewSkills(win, jobId, "Job"))
def viewMenu(win):
ui.button(win, "Jobs", lambda: ui.window("Jobs", viewJobs))
ui.button(win, "Employees", lambda: ui.window("Employees", viewEmployees))
ui.button(win, "Holidays", lambda: ui.window("Holidays", viewHolidays))
win.grid_columnconfigure(0, minsize=300)
def clickSkills():
employeeId = employeeIds[int(listBox.curselection()[0])]
ui.window("Employee Skills",
lambda win: viewSkills(win, employeeId, "Employee"))
FROM (""" + queryB + """) AS b
LEFT JOIN (""" + queryC + """) AS c
ON b.employeeId = c.assigneeId
ORDER BY workHours ASC
"""
return cursor.execute(queryD, jobSkills).fetchone()[0]
# Start-up code.
DB_LOCATION = "narrowboats.db"
# Acceptable exceptions.
DB_EXCEPTIONS = {}
DB_EXCEPTIONS["limitHours"] = "Invalid number of work hours."
DB_EXCEPTIONS["limitPrice"] = "Invalid price."
DB_EXCEPTIONS["limitStatus"] = "Invalid status."
DB_EXCEPTIONS[
"formatDate"] = "Invalid date - must be in the format YYYY-MM-DD."
DB_EXCEPTIONS[
"limitDate"] = "Invalid date - the date must be after the present."
DB_EXCEPTIONS["foreign key"] = "Invalid ID."
DB_EXCEPTIONS["No description entered."] = "No description entered."
DB_EXCEPTIONS["No customer entered."] = "No customer entered."
DB_EXCEPTIONS["No boat entered."] = "No boat entered."
DB_EXCEPTIONS["No work hours entered."] = "No work hours entered."
DB_EXCEPTIONS["No employee entered."] = "No employee entered."
# Show User Interface.
ui.window("Menu", lambda win: viewMenu(win))
def clickSkills():
employeeId = employeeIds[int(listBox.curselection()[0])]
ui.window("Employee Skills", lambda win: viewSkills(win, employeeId, "Employee"))
def clickSkills():
jobId = jobIds[int(listBox.curselection()[0])]
ui.window("Job Skills", lambda win: viewSkills(win, jobId, "Job"))
def viewMenu(win):
ui.button(win, "Jobs", lambda: ui.window("Jobs", viewJobs))
ui.button(win, "Employees", lambda: ui.window("Employees", viewEmployees))
ui.button(win, "Holidays", lambda: ui.window("Holidays", viewHolidays))
win.grid_columnconfigure(0, minsize=300)
GROUP BY assigneeId
"""
queryD = """SELECT employeeId, (CASE WHEN workHours > 0 THEN workHours ELSE 0 END) - (b.skills * 4) AS workHours
FROM (""" + queryB + """) AS b
LEFT JOIN (""" + queryC + """) AS c
ON b.employeeId = c.assigneeId
ORDER BY workHours ASC
"""
return cursor.execute(queryD, jobSkills).fetchone()[0]
# Start-up code.
DB_LOCATION = "narrowboats.db"
# Acceptable exceptions.
DB_EXCEPTIONS = {}
DB_EXCEPTIONS["limitHours"] = "Invalid number of work hours."
DB_EXCEPTIONS["limitPrice"] = "Invalid price."
DB_EXCEPTIONS["limitStatus"] = "Invalid status."
DB_EXCEPTIONS["formatDate"] = "Invalid date - must be in the format YYYY-MM-DD."
DB_EXCEPTIONS["limitDate"] = "Invalid date - the date must be after the present."
DB_EXCEPTIONS["foreign key"] = "Invalid ID."
DB_EXCEPTIONS["No description entered."] = "No description entered."
DB_EXCEPTIONS["No customer entered."] = "No customer entered."
DB_EXCEPTIONS["No boat entered."] = "No boat entered."
DB_EXCEPTIONS["No work hours entered."] = "No work hours entered."
DB_EXCEPTIONS["No employee entered."] = "No employee entered."
# Show User Interface.
ui.window("Menu", lambda win: viewMenu(win))
