async def create(self, groups: list, data: dict, school: int,
dbi: PoolConnectionHolder):
query = '''
WITH ciclo_acad AS (
INSERT INTO ciclo_academico (fecha_comienzo, fecha_fin, escuela)
VALUES ($1, $2, $3)
RETURNING id
)
INSERT INTO horario_profesor (ciclo_id, profesor_id, dia_clase, hora_comienzo, hora_fin)
VALUES {values}
'''
values = ''
for group in groups:
values = values + await self._build_group_query(group, data)
values = values[:-2]
query = query.format(values=values)
async with dbi.acquire() as connection:
async with connection.transaction():
await connection.execute(
query, datetime.strptime(data['beginning_date'], df),
datetime.strptime(data['ending_date'], df), school)
async def do_transaction(data: list, table: str, returning: str,
dbi: PoolConnectionHolder) -> str:
columns = data[0].keys()
values = list()
for u in data:
_u = []
for c in columns:
_u.append(str(u[c]))
_u = '(\'' + '\', \''.join(_u) + '\')'
values.append(_u)
if values:
values = ', '.join(values)
query = '''
INSERT INTO {table} {columns}
VALUES {values}
RETURNING {returning};
'''.format(table=table,
columns='("' + '", "'.join(columns) + '")',
values=values,
returning=returning)
async with dbi.acquire() as connection:
async with connection.transaction():
return await connection.fetch(query)
async def _unique(self, value: str, table: str, column: str,
dbi: PoolConnectionHolder):
if not self.restricted_value.fullmatch(table):
raise ValueError
_column = self.column.fullmatch(column)
if not _column:
raise ValueError
_type = _column.group(2)
if _type:
if _type not in Cast:
raise ValueError
value = Cast[_type](value)
async with dbi.acquire() as connection:
result = await (await connection.prepare(
UNIQUE.format(table, _column.group(1)))).fetchval(value)
if result > 0:
return False
return True
async def _validate_password(name: str, value: str, pos: int, elems: list, dbi: PoolConnectionHolder,
user: int):
async with dbi.acquire() as connection:
current_password = (await (
await connection.prepare('SELECT credencial FROM usuario WHERE id = $1 LIMIT 1')
).fetchval(user)).encode('utf-8')
if not checkpw(value.encode('utf-8'), current_password):
return '{} ingresada incorrecta'.format(name)
async def update(student: int, school_term: int, dbi: PoolConnectionHolder = None,
callback=None):
statement = '''
INSERT INTO promedio_notas_ciclo (ciclo_acad_id, estudiante_id, valor)
VALUES ($2, $1, $3)
ON CONFLICT ON CONSTRAINT promedio_notas_ciclo_pkey
DO UPDATE SET valor = $3
'''
async with dbi.acquire() as connection:
await connection.execute(statement, student, school_term, await callback(student, school_term, dbi=dbi))
async def fetch_attendance_for_schedule(student: int, schedule: int,
dbi: PoolConnectionHolder):
query = '''
SELECT horario_id, fecha_registro, asistio
FROM asistencia
WHERE alumno_id = $1 AND
horario_id = $2
'''
async with dbi.acquire() as connection:
return await (await
connection.prepare(query)).fetch(student, schedule)
async def fetch_school_term(school: int, dbi: PoolConnectionHolder):
query = '''
SELECT id, fecha_comienzo, fecha_fin
FROM ciclo_academico
WHERE $1 >= fecha_comienzo AND
$1 <= fecha_fin AND
escuela = $2
LIMIT 1
'''
async with dbi.acquire() as connection:
return await (await connection.prepare(query)).fetchrow(
datetime.utcnow(), school)
async def fetch_teachers(school: int, role_id: int,
dbi: PoolConnectionHolder):
query = '''
SELECT id, rol_id, tipo_documento, nombres, apellidos, correo_electronico, nro_telefono, escuela
FROM usuario
WHERE rol_id = $1 AND
deshabilitado != TRUE AND
escuela = $2
'''
async with dbi.acquire() as connection:
return await (await
connection.prepare(query)).fetch(role_id, school)
async def school_term_exists(school_term: int, school: int,
dbi: PoolConnectionHolder):
query = '''
SELECT true
FROM ciclo_academico
WHERE id = $1 AND
escuela = $2
LIMIT 1
'''
async with dbi.acquire() as connection:
return await (await connection.prepare(query)).fetchval(
school_term, school)
async def fetch_schedules(school_term: int, dbi: PoolConnectionHolder):
query = '''
SELECT horario_profesor.id, profesor_id, nombres as profesor_nombres,
apellidos as profesor_apellidos, dia_clase, hora_comienzo,
hora_fin
FROM horario_profesor
LEFT JOIN usuario
ON usuario.id = profesor_id
WHERE ciclo_id = $1
ORDER BY usuario.nombres ASC, usuario.apellidos ASC, dia_clase ASC, hora_comienzo ASC
'''
async with dbi.acquire() as connection:
return await (await connection.prepare(query)).fetch(school_term)
async def authorize_students(data: list, dbi: PoolConnectionHolder):
query = ('''
UPDATE usuario
SET autorizado = true
WHERE id = $1;
''', '''
INSERT INTO matricula (estudiante_id, ciclo_acad_id)
VALUES ($1, $2);
''')
async with dbi.acquire() as connection:
async with connection.transaction():
for user in data:
await connection.execute(query[0], user[0])
await connection.execute(query[1], *user)
async def _validate_email(name: str, value: str, pos: int, elems: list, dbi: PoolConnectionHolder, user_id: int):
query = '''
SELECT true
FROM usuario
WHERE id != $1 AND
correo_electronico = $2
'''
async with dbi.acquire() as connection:
statement = await connection.prepare(query)
status = await statement.fetchval(user_id, value)
status = status or False
if status:
return 'El correo electrónico {} ya se encuentra en uso por otro usuario'.format(value)
async def register_attendance(data: list, dbi: PoolConnectionHolder):
async with dbi.acquire() as connection:
async with connection.transaction():
query = '''
INSERT INTO asistencia (alumno_id, horario_id, fecha_registro, observacion, asistio)
VALUES {0}
RETURNING true;
'''.format(','.join([
'({})'.format(','.join(
list(
map(
lambda x: '\'' + str(x) + '\''
if not isinstance(x, str) else '\'' + x + '\'',
v)))) for v in data
]))
return await connection.fetch(query)
async def _validate_teacher(name: str, value: str, pos: int, elems: list,
dbi: PoolConnectionHolder):
query = '''
SELECT true
FROM usuario
WHERE id = $1 AND
rol_id = 2 AND
deshabilitado = FALSE
LIMIT 1
'''
async with dbi.acquire() as connection:
teacher_exists = await (await connection.prepare(query)).fetchval(
int(value))
if not teacher_exists:
return 'El profesor seleccionado en {name} no fue encontrado o está deshabilitado'.format(
name=name)
async def _fetch_students(school: int, dbi: PoolConnectionHolder):
query = '''
SELECT usuario.id, rol_id, tipo_documento, nombres, apellidos, correo_electronico, escuela, deshabilitado,
solicitud_autorizacion.fecha_creacion, archivo_id, archivo.nombre as archivo_nombre,
archivo.ext as archivo_ext
FROM usuario
INNER JOIN solicitud_autorizacion
ON usuario.id = solicitud_autorizacion.alumno_id
INNER JOIN archivo
ON archivo.id = solicitud_autorizacion.archivo_id
WHERE rol_id=1 AND
escuela=$1 AND
autorizado=FALSE AND
deshabilitado=FALSE
ORDER BY apellidos ASC
'''
async with dbi.acquire() as connection:
stmt = await connection.prepare(query)
return await stmt.fetch(school)
async def fetch_final_grade(student: int, school_term: int, dbi: PoolConnectionHolder = None):
query = '''
WITH ciclo_academico AS (
SELECT id
FROM ciclo_academico
WHERE ciclo_academico.id = $2
LIMIT 1
)
SELECT SUM(COALESCE(nota_estudiante.valor, 0.0) * nota.porcentaje / 100.0)
FROM estructura_notas
LEFT JOIN nota
ON nota.id = estructura_notas.nota_id
LEFT JOIN nota_estudiante
ON nota_estudiante.nota_id = estructura_notas.nota_id AND
nota_estudiante.estudiante_id = $1
WHERE estructura_notas.ciclo_acad_id = (SELECT id from ciclo_academico) AND
nota_estudiante.estudiante_id = $1
LIMIT 1
'''
async with dbi.acquire() as connection:
return await (await connection.prepare(query)).fetchval(student, school_term) or Decimal(0.0)
async def _validate_role(name: str, value: str, pos: int, elems: list, dbi: PoolConnectionHolder,
user_role: int, self_role: int):
if user_role == 4 and self_role != 4:
return 'No tienes permisos suficientes para cambiar el rol de este usuario'
if int(value) == 4 and self_role != 4:
return 'No tienes permisos suficientes para asignar este rol'
async with dbi.acquire() as connection:
roles = await (await connection.prepare('''
SELECT *
FROM rol_usuario
''')).fetch()
roles = flatten(roles, {}) if roles else []
error = True
for role in roles:
if int(value) == role['id']:
error = False
if error:
return '{}: {} no existe...'.format(name, value)
async def fetch_students(school: int, dbi: PoolConnectionHolder):
query = '''
WITH ciclo_academico AS (
SELECT ciclo_academico.id
FROM ciclo_academico
WHERE ciclo_academico.fecha_comienzo <= $1 AND
ciclo_academico.fecha_fin >= $1 AND
ciclo_academico.escuela = $2
LIMIT 1
)
SELECT usuario.id, usuario.nombres, usuario.apellidos
FROM usuario
INNER JOIN matricula
ON matricula.estudiante_id = usuario.id AND
matricula.ciclo_acad_id = (SELECT id FROM ciclo_academico)
WHERE usuario.rol_id = 1 AND
usuario.escuela = $2 AND
matricula.ciclo_acad_id = (SELECT id FROM ciclo_academico)
ORDER BY apellidos ASC
'''
async with dbi.acquire() as connection:
return await (await connection.prepare(query)).fetch(
datetime.utcnow() - timedelta(hours=5), school)