def listar_alunos(self):
self.tree.clear() # clear tree view
query = "SELECT * FROM students ORDER BY name"
students = Sqlite.db_conn.cursor.execute(query).fetchall()
for row in students:
row = list(row)
row[1] = ~tools.CPF(row[1])
self.tree.appendItem(row[1:], iid=row[0])
# get ids of classes in which student is enrolled
# REM: do left join just to make sure we retrieve classes sorted by semester in ascending order
query = '''SELECT class_students.class_id FROM class_students
LEFT JOIN classes ON class_students.class_id = classes.id
WHERE student_id = {} ORDER BY classes.semester ASC'''.format(
row[0])
student_classes = Sqlite.db_conn.cursor.execute(query).fetchall()
for class_id in student_classes:
# get class details
query = '''SELECT classes.code, subjects.code, subjects.name, classes.semester
FROM classes LEFT JOIN subjects ON classes.subject = subjects.id
WHERE classes.id = {}'''.format(class_id[0])
classes = Sqlite.db_conn.cursor.execute(query).fetchall()
for turma in classes:
turma = [str(x) for x in turma]
semester = turma.pop()
semester_id = str(row[0]) + "." + semester
if not self.tree.exists(semester_id):
self.tree.appendItem([semester],
pos=row[0],
iid=semester_id)
str_id = str(row[0]) + "-" + str(class_id[0])
turma = turma[0:1] + [' - '.join(turma[1:])]
self.tree.appendItem(turma, pos=semester_id, iid=str_id)
def _listar_alunos(self, id_turma):
query = '''SELECT cpf, name FROM students WHERE id IN
(SELECT student_id FROM class_students WHERE class_id = {})
ORDER BY name ASC'''.format(id_turma)
order = 1
for student in Sqlite.db_conn.cursor.execute(query):
student = list(student)
student[0] = ~tools.CPF(student[0])
student.append(' ')
student = [order, ' '] + student
self.table.insert(student)
order += 1
def _set_prof_turma(self):
sel = self.tree.get_selection()
param = []
for s in sel:
if self.tree.parent(s['iid']):
continue
param.append({
'id': s['iid'],
'cpf': tools.CPF(s['text']),
'nome': s['values'][0],
'depto': s['values'][1]
})
self.turmas.set_professores(param)
def _validar_aluno(self):
erros = []
cpf = tools.CPF(self.cpf.get().strip())
if len(cpf) == 0:
erros.append("CPF em branco")
elif not cpf.valid():
erros.append("CPF inválido")
nome = self.nome.get().strip()
if len(nome) == 0:
erros.append("Nome do professor em branco")
if len(erros) > 0:
tools.aviso_erro(erros)
return None
return [cpf, nome]
def listar_professores(self):
self.tree.clear() # clear tree view
query = "SELECT * FROM professors ORDER BY name"
query1 = '''SELECT classes.code, subjects.code, subjects.name, semester, class_id FROM class_professors
LEFT JOIN classes ON class_id = classes.id
LEFT JOIN subjects ON subjects.id = classes.subject
WHERE professor_id = {}
ORDER BY semester, subjects.name ASC'''
Sqlite.db_conn.cursor.execute(query)
profs = Sqlite.db_conn.cursor.fetchall()
for row in profs:
row = list(row)
# append professor to tree view
row[1] = ~tools.CPF(row[1])
self.tree.appendItem(row[1:], iid=row[0])
# query for professor classes from db
Sqlite.db_conn.cursor.execute(query1.format(row[0]))
turmas = Sqlite.db_conn.cursor.fetchall()
for turma in turmas:
turma = list(turma)
turma = [str(x) for x in turma]
class_id = turma.pop()
semester = turma.pop()
semester_id = str(row[0]) + "." + semester
if not self.tree.exists(semester_id):
self.tree.appendItem([semester],
pos=row[0],
iid=semester_id)
# assign unique string id to child id;
# string id = parent id = professor PK (iid) + hyphen + class primary key (class_id)
str_id = str(row[0]) + "-" + str(class_id)
if self.tree.exists(str_id):
continue
# print("turma=", turma) # debug
turma = turma[0:1] + [' - '.join(turma[1:])]
self.tree.appendItem(turma, pos=semester_id, iid=str_id)
def _set_aluno(self, tupla):
self.cpf.set(~tools.CPF(tupla[0]))
self.nome.set(tupla[1])
def _set_professor(self, tupla):
self.cpf.set(~tools.CPF(tupla[0]))
self.nome.set(tupla[1])
self.depto.set(tupla[2])