def from_active_directory_student(ad_student): if not ad_student: return None c = g_server_connection.db_connection.cursor() c.execute("SELECT * FROM Student WHERE GUID = ?", (ad_student.guid_str, )) db_student = c.fetchone() if not db_student: # Student in active directory but not inventory system return Student(ad_student) tablet_guid = None # Search for the tablet assigned to this student c.execute("SELECT * FROM StudentTabletLink WHERE StudentGUID = ?", (ad_student.guid_str, )) link = c.fetchone() if link: tablet_guid = link[1] equipment_receipt = db_student[6] if len(db_student) >= 7 else None return Student(ad_student, db_student[1], db_student[2], db_student[3], db_student[4], db_student[5], equipment_receipt, tablet_guid)
def __import_excel(self): not_found = [] tablets_not_found = [] import xlrd wb = xlrd.open_workbook('excel_db.xls') sheet = wb.sheets()[0] for row in range(sheet.nrows): if row == 0: continue first_name = sheet.cell(row, 0).value last_name = sheet.cell(row, 1).value tablet_pcsb = sheet.cell(row, 3).value.replace("-", "") tablet_serial = sheet.cell(row, 4).value tablet_device = "%i" % sheet.cell(row, 5).value student = Student.from_name(first_name + " " + last_name) if len(first_name) > 0 and not student: # Name couldn't be found print("Student", first_name + " " + last_name, "not found in Active Directory!") not_found.append(first_name + " " + last_name) tablet = Tablet.from_pcsb_tag(tablet_pcsb) if tablet: print("Tablet from excel sheet:", tablet_device, tablet_serial) tablet.device_model = tablet_device tablet.serial = tablet_serial tablet.update() if student: print("\tStudent on tablet:", student.first_name + " " + student.last_name) student.tablet_guid = tablet.guid student.update_link() else: print("\tNo student on tablet") elif len(tablet_pcsb) > 0: print("Can't find tablet", tablet_pcsb) tablets_not_found.append(tablet_pcsb) if student: student.update() self.db_connection.commit() import_errors_out = open('excel_import_errors.txt', 'w') import_errors_out.write("Students not found in active directory:\n") for name in not_found: import_errors_out.write("\t" + name + "\n") import_errors_out.write("Tablets not found in active directory:\n") for t in tablets_not_found: import_errors_out.write("\t" + t + "\n") import_errors_out.flush() import_errors_out.close()
def __send_user(self, guid, connections): dg = core.Datagram() dg.add_uint16(MSG_SERVER_UPDATE_USER) student = Student.from_guid(guid) if not student: return student.write_datagram(dg) self.send(dg, connections)
def __build_student_db_from_ad(self): """Builds a database of students from entries in Active Directory.""" c = self.db_connection.cursor() c.execute("DELETE FROM Student") all_students = Student.get_ad_cat_student_list() for ad_student in all_students: print(ad_student) c.execute("INSERT INTO Student VALUES (?,?,?,?,?,?)", (ad_student.guid_str, 0, 0, 0, "$0.00", "")) self.db_connection.commit()
def handle_update_user(self, dgi): student = Student.from_datagram(dgi) userView = self.ui.tabletView_2 row = self.find_table_guid_row(userView, student.guid) if row is None: print("Error: tried to update user row but couldn't find the row") return self.update_student_row(row, dgi, student) if self.user_table_generated: self.update_student_row_ui(row, student)
def __sync_user_db(self): """Makes sure our local database contains all of the Active Directory users.""" print("Syncing local user database with Active Directory...") c = self.db_connection.cursor() all_students = Student.get_ad_cat_student_list() for ad_student in all_students: # Check for an entry c.execute("SELECT * FROM Student WHERE GUID = ?", (ad_student.guid_str, )) student = c.fetchone() if not student: # Doesn't exist, make a default entry. c.execute("INSERT INTO Student VALUES (?,?,?,?,?,?,?)", (ad_student.guid_str, 0, 0, 0, "$0.00", "", 0)) print("Added new student", ad_student.cn) # Now search for students in our local database that no longer exist in Active Directory. c.execute("SELECT * FROM Student") db_users = c.fetchall() for db_user in db_users: guid = db_user[0] student = Student.from_guid(guid) if not student: # Removed student c.execute("DELETE FROM Student WHERE GUID = ?", (guid, )) print("Removed deleted student", guid) # Remove their tablet link c.execute( "SELECT FROM StudentTabletLink WHERE StudentGUID = ?", (guid, )) link = c.fetchone() if link: c.execute( "DELETE FROM StudentTabletLink WHERE StudentGUID = ?", (guid, )) print("\tRemoved student/tablet link for removed student") self.db_connection.commit() print("Done")
def __send_all_users(self, connections): dg = core.Datagram() dg.add_uint16(MSG_SERVER_GET_ALL_USERS_RESP) student_dg = core.Datagram() all_students = Student.get_ad_cat_student_list() num_students = 0 for ad_student in all_students: student = Student.from_active_directory_student(ad_student) if not student: continue student.write_datagram(student_dg) num_students += 1 dg.add_uint16(num_students) dg.append_data(student_dg.get_message()) self.send(dg, connections)
def handle_net_assistants_resp(self, dgi): num_hws = dgi.get_uint32() for i in range(num_hws): hw = Student.from_datagram(dgi) self.hws.append(hw) hwnames = [] for hw in self.hws: self.ui.hwCombo.addItem(hw.name) hwnames.append(hw.name) completer = QtWidgets.QCompleter(hwnames) completer.setCompletionMode(QtWidgets.QCompleter.InlineCompletion) completer.setCaseSensitivity(QtCore.Qt.CaseInsensitive) self.ui.hwCombo.setEditable(True) self.ui.hwCombo.setCompleter(completer) self.please_wait_dialog.done(0) self.please_wait_dialog = None self.__reset()
def __check_link_singularities(self): """ Make sure that all student/tablet links reference valid students and tablets. If a link references a nonexistent student or tablet, the link will be removed. """ c = self.db_connection.cursor() c.execute("SELECT * FROM StudentTabletLink") links = c.fetchall() for link in links: student_guid = link[0] tablet_guid = link[1] # Ensure both the student and tablet exist student = Student.from_guid(student_guid) tablet = Tablet.from_guid(tablet_guid) if (not student) or (not tablet): c.execute( "DELETE FROM StudentTabletLink WHERE StudentGUID = ? AND TabletGUID = ?", (student_guid, tablet_guid)) print( "Deleting student/tablet link that references nonexistent student or tablet." ) self.db_connection.commit()
def from_name(name_str): try: ad_student = pyad.from_cn(name_str) except: ad_student = None return Student.from_active_directory_student(ad_student)
def __handle_datagram_netassistant(self, connection, client, dgi, msg_type): if msg_type == MSG_CLIENT_GET_ALL_TABLETS: dg = core.Datagram() dg.add_uint16(MSG_SERVER_GET_ALL_TABLETS_RESP) tablet_dg = core.Datagram() num_tablets = 0 all_tablets = Tablet.get_ad_tablet_list() for ad_tablet in all_tablets: tablet = Tablet.from_active_directory_tablet(ad_tablet) if not tablet: continue tablet.write_datagram(tablet_dg) num_tablets += 1 dg.add_uint16(num_tablets) dg.append_data(tablet_dg.get_message()) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_GET_ALL_USERS: self.__send_all_users(connection) elif msg_type == MSG_CLIENT_EDIT_USER: guid = dgi.get_string() print("Received edit user request for guid:", guid) dg = core.Datagram() dg.add_uint16(MSG_SERVER_EDIT_USER_RESP) if not guid in self.users_being_edited and client.user_editing is None: dg.add_uint8(1) dg.add_string(guid) client.user_editing = guid self.users_being_edited.append(guid) else: # User is already being edited by another client dg.add_uint8(0) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_EDIT_TABLET: guid = dgi.get_string() dg = core.Datagram() dg.add_uint16(MSG_SERVER_EDIT_TABLET_RESP) if not guid in self.tablets_being_edited and client.tablet_editing is None: dg.add_uint8(1) dg.add_string(guid) client.tablet_editing = guid self.tablets_being_edited.append(guid) else: # Tablet is already being edited by another client dg.add_uint8(0) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_FINISH_EDIT_TABLET: ret = dgi.get_uint8() mod_tablet = BaseTablet.from_datagram(dgi) guid = mod_tablet.guid if guid in self.tablets_being_edited and client.tablet_editing == guid: if ret: mod_tablet.__class__ = Tablet # hack mod_tablet.update() c = self.db_connection.cursor() issues_dg = core.Datagram() issues_dg.add_uint16(MSG_SERVER_UPDATE_ISSUE) num_issues = dgi.get_uint16() issues_dg.add_uint16(num_issues) for i in range(num_issues): issue = Issue.from_datagram(dgi) issue.write_database(c) issue.write_datagram(issues_dg) steps_dg = core.Datagram() steps_dg.add_uint16(MSG_SERVER_UPDATE_ISSUE_STEP) num_steps = dgi.get_uint16() steps_dg.add_uint16(num_steps) for i in range(num_steps): step = IssueStep.from_datagram(dgi) step.write_database(c) step.write_datagram(steps_dg) tablet_dg = core.Datagram() tablet_dg.add_uint16(MSG_SERVER_UPDATE_TABLET) mod_tablet.write_datagram(tablet_dg) # Send the updated data to all net clients. netconns = self.get_all_client_connections( CLIENT_NET_ASSISTANT) self.send(issues_dg, netconns) self.send(steps_dg, netconns) self.send(tablet_dg, netconns) self.db_connection.commit() print("Done editing tablet", guid) client.tablet_editing = None self.tablets_being_edited.remove(guid) else: print( "Suspicious: finished editing a tablet that wasn't being edited" ) elif msg_type == MSG_CLIENT_GET_ALL_ISSUES: c = self.db_connection.cursor() c.execute("SELECT * FROM TabletIssue") issues = c.fetchall() num_issues = len(issues) dg = core.Datagram() dg.add_uint16(MSG_SERVER_GET_ALL_ISSUES_RESP) dg.add_uint32(num_issues) for i in range(num_issues): data = issues[i] issue = Issue(*data) issue.write_datagram(dg) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_GET_ALL_ISSUE_STEPS: c = self.db_connection.cursor() c.execute("SELECT * FROM TabletIssueStep") steps = c.fetchall() num_steps = len(steps) dg = core.Datagram() dg.add_uint16(MSG_SERVER_GET_ALL_ISSUE_STEPS_RESP) dg.add_uint32(num_steps) for i in range(num_steps): data = steps[i] step = IssueStep(*data) step.write_datagram(dg) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_GET_ALL_LINKS: c = self.db_connection.cursor() c.execute("SELECT * FROM StudentTabletLink") links = c.fetchall() num_links = len(links) dg = core.Datagram() dg.add_uint16(MSG_SERVER_GET_ALL_LINKS_RESP) dg.add_uint32(num_links) for i in range(num_links): data = links[i] link = StudentTabletLink(*data) link.write_datagram(dg) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_FINISH_EDIT_USER: guid = dgi.get_string() if guid in self.users_being_edited and client.user_editing == guid: print("Done editing user", guid) client.user_editing = None self.users_being_edited.remove(guid) ret = dgi.get_uint8() if ret: pcsb_agreement = dgi.get_uint8() cat_agreement = dgi.get_uint8() insurance = dgi.get_uint8() insurance_amt = dgi.get_string() tablet_pcsb = dgi.get_string() equipment_receipt = dgi.get_uint8() error = 0 student = Student.from_guid(guid) has_new_insurance = False if insurance and not student.insurance_paid: has_new_insurance = True insurance_date = dgi.get_string() print("New insurance date", insurance_date) student.pcsb_agreement = pcsb_agreement student.cat_agreement = cat_agreement student.equipment_receipt = equipment_receipt student.insurance_paid = insurance student.insurance_amount = insurance_amt if not insurance: student.date_of_insurance = "" elif has_new_insurance: student.date_of_insurance = insurance_date if len(tablet_pcsb) > 0: tablet = Tablet.from_pcsb_tag(tablet_pcsb) if not tablet: error = 1 else: student.tablet_guid = Tablet.from_pcsb_tag( tablet_pcsb).guid else: student.tablet_guid = None student.update() dg = core.Datagram() dg.add_uint16(MSG_SERVER_FINISH_EDIT_USER_RESP) if error == 0: try: student.update_link() dg.add_uint8(1) except Exception as e: error = 2 if error == 1: dg.add_uint8(0) dg.add_string( "There was an error assigning the tablet: bad PCSB Tag" ) elif error == 2: dg.add_uint8(0) dg.add_string( "There was an error assigning the tablet: already assigned" ) self.writer.send(dg, connection) self.__send_user( guid, self.get_all_client_connections(CLIENT_NET_ASSISTANT)) # Send the updated link, if there is one c = self.db_connection.cursor() c.execute( "SELECT * FROM StudentTabletLink WHERE StudentGUID = ?", (student.guid, )) link = c.fetchone() if link: dg = core.Datagram() dg.add_uint16(MSG_SERVER_UPDATE_LINK) dg.add_uint16(1) slink = StudentTabletLink(*link) slink.write_datagram(dg) self.send( dg, self.get_all_client_connections( CLIENT_NET_ASSISTANT)) else: print( "Suspicious: finished editing a user that wasn't being edited" )
def from_guid(guid_str): try: ad_student = pyad.from_guid(guid_str) except: ad_student = None return Student.from_active_directory_student(ad_student)
def __handle_datagram_student(self, connection, client, dgi, msg_type): if msg_type == MSG_CLIENT_LOOKUP_TABLET: pcsb_tag = dgi.get_string() dg = core.Datagram() dg.add_uint16(MSG_SERVER_LOOKUP_TABLET_RESP) tablet = Tablet.from_pcsb_tag(pcsb_tag) if not tablet: print("Can't find tablet with PCSB Tag %s" % pcsb_tag) dg.add_uint8(0) self.writer.send(dg, connection) return if not tablet.student_guid: student_name = "" student_email = "" student_grade = "" else: student = Student.from_guid(tablet.student_guid) student_name = student.first_name + " " + student.last_name student_grade = student.grade student_email = student.email if not student_grade: student_grade = "" if not student_email: student_email = "" dg.add_uint8(1) tablet.write_datagram(dg) dg.add_string(student_name) dg.add_string(student_grade) dg.add_string(student_email) self.writer.send(dg, connection) elif msg_type == MSG_CLIENT_SUBMIT_ISSUE: pcsb_tag = dgi.get_string() tablet = Tablet.from_pcsb_tag(pcsb_tag) if not tablet: print("Can't submit issue, pcsb tag %s not found" % pcsb_tag) return incident_desc = dgi.get_string() incident_date = dgi.get_string() problem_desc = dgi.get_string() hwguid = dgi.get_string() c = self.db_connection.cursor() issue = Issue(-1, tablet.guid, incident_desc, problem_desc, incident_date, 0, "", "", 2, "", 0, hwguid, "", 0) issue.write_database(c) self.db_connection.commit() print( "Submitting:\n\t%s\n\t%s\n\t%s\n\t%s\n\t%s" % (pcsb_tag, incident_desc, incident_date, problem_desc, hwguid)) dg = core.Datagram() dg.add_uint16(MSG_SERVER_UPDATE_ISSUE) dg.add_uint16(1) issue.write_datagram(dg) self.send(dg, self.get_all_client_connections(CLIENT_NET_ASSISTANT)) elif msg_type == MSG_CLIENT_REQ_NET_ASSISTANTS: hws = Student.get_ad_net_assistants() # Borg doesn't want this selectable in the issue dropdown. for hw in hws: if hw.name == "CAT network assistant": hws.remove(hw) break dg = core.Datagram() dg.add_uint16(MSG_SERVER_NET_ASSISTANTS_RESP) num_hws = len(hws) dg.add_uint32(num_hws) for i in range(num_hws): hw = Student.from_active_directory_student(hws[i]) hw.write_datagram(dg) self.writer.send(dg, connection)
def update_student_row(self, i, dgi, student = None): if not student: student = Student.from_datagram(dgi) utils.list_add_replace(self.students, student)