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 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))
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))