def display_all():
"""
:return: ((id, name, role), ...,)
"""
c.execute("SELECT name, id, role FROM Funcionario")
query_list = c.fetchall()
return query_list
def get_all(cls):
c.execute("SELECT name, id FROM Funcionario WHERE role = ?",
(cls.get_role(),))
query_list = c.fetchall()
funcionarios = []
for query in query_list:
funcionarios.append(cls(*query))
return funcionarios
def get_all_funcionarios(self):
"""
:return: ((id, name, role), ...,)
"""
c.execute("""
SELECT id, name, role FROM Funcionario WHERE id IN
(SELECT funcionario_id FROM Ramal WHERE reserva_id = ?)
""", (self.__id,))
data = c.fetchall()
return data
def add_funcionario(self, funcionario: Funcionario):
if not isinstance(funcionario, Funcionario):
raise self.NotSocioException('Apenas funcionários podem ser adicionados a ramais.')
if isinstance(funcionario, Socio):
raise self.NotSocioException('Sócios não podem ser adicionados a ramais.')
c.execute("SELECT funcionario_id FROM Ramal WHERE funcionario_id = ? AND reserva_id = ?",
(funcionario.id, self.__id,))
if c.fetchone():
raise self.AlreadyExistsException("O funcionário em questão já possui um Ramal nesta reserva.")
c.execute("SELECT reserva_id FROM Ramal WHERE reserva_id = ?", (self.__id,))
actual_length = len(c.fetchall())
if actual_length >= self.sala.vagas:
raise Sala.ExceedingLengthException('A quantidade de vagas na sala acabou.')
c.execute("INSERT INTO Ramal (funcionario_id, reserva_id) VALUES (?,?)",
(funcionario.id, self.id,))
conn.commit()
def get_by(**fields) -> List['Reserva']:
"""
:param fields: id | socio_id | sala_id | horario
:return: orm.reserva.Reserva
"""
field_str = ' AND '.join(map(lambda kv: f'{kv} = ?', fields.keys()))
field_str = 'WHERE ' + field_str if field_str != '' else ''
c.execute(f"SELECT id, socio_id, sala_id, horario FROM Reserva {field_str}",
(*fields.values(),))
data_list = c.fetchall()
reservas = []
if len(data_list) >= 1:
for data in data_list:
this_id, socio_id, sala_id, horario = data
horario = Horario.get_by_timestring(horario)
socio = Socio.get(socio_id)
sala = Sala.get(sala_id)
reservas.append(Reserva(this_id, socio, sala, horario))
return reservas
def get_by_day(day_and_month: tuple, **fields) -> List['Reserva']:
"""
:param day_and_month: (day, month,)
:param fields: id | socio_id | sala_id | horario
:return: orm.reserva.Reserva
"""
day_and_month = f'{day_and_month[0]}.{day_and_month[1]}'
field_str = ' AND '.join(map(lambda kv: f'{kv} = ?', fields.keys()))
field_str = 'AND ' + field_str if field_str != '' else ''
c.execute(f"""
SELECT id, socio_id, sala_id, horario FROM Reserva
WHERE SUBSTR(horario, 0, INSTR(horario, ',')) = ? {field_str}
""",
(day_and_month, *fields.values(),))
data_list = c.fetchall()
reservas = []
if len(data_list) >= 1:
for data in data_list:
this_id, socio_id, sala_id, horario = data
horario = Horario.get_by_timestring(horario)
socio = Socio.get(socio_id)
sala = Sala.get(sala_id)
reservas.append(Reserva(this_id, socio, sala, horario))
return reservas
def get_all(cls):
c.execute("SELECT id, spaces FROM Sala")
salas = tuple(map(lambda d: Sala(*d), c.fetchall()))
return salas