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)