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