def delete(id):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
cursor.execute("DELETE FROM usuario WHERE id = %s", (id, ))
except (Exception, Error) as error:
print("Error while getting data", error)
def findByEmail(email):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from usuario where email = %s"
cursor.execute(select_query, (email, ))
return buildUsuario(cursor.fetchone())
except (Exception, Error) as error:
print("Error while getting data", error)
def findByEstadoAndIdProyecto(estado, id_proyecto):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from sprints where estado = %s and id_proyecto = %s order by fecha_inicio desc limit 1"
cursor.execute(select_query, (estado, id_proyecto))
return buildSprint(cursor.fetchone())
except (Exception, Error) as error:
print("Error while getting data", error)
def findById(id):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from proyectos where id = %s"
cursor.execute(select_query, (id, ))
return buildProyecto(cursor.fetchone())
except (Exception, Error) as error:
print("Error while getting data", error)
def existSprintActivo(id_proyecto):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select count(id) from sprints where estado <> 1 and id_proyecto = %s"
cursor.execute(select_query, (id_proyecto, ))
registro = cursor.fetchone()
return True if registro[0] == 1 else False
except (Exception, Error) as error:
print("Error while getting data", error)
def existsByNombreAndApellido(nombre, apellido):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select count(id) from usuario where nombre = %s and apellido = %s"
cursor.execute(select_query, (nombre, apellido))
registro = cursor.fetchone()
return True if registro[0] == 1 else False
except (Exception, Error) as error:
print("Error while getting data", error)
def update(rol):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (rol.nombre_rol, rol.id)
cursor.execute("UPDATE roles SET nombre_rol = %s WHERE id = %s",
valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def findById(id):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from roles where id = %s"
cursor.execute(select_query, (id, ))
registro = cursor.fetchone()
return Rol(registro[0], registro[1])
except (Exception, Error) as error:
print("Error while getting data", error)
def generar_reporte_sprints(tipo_generacion, filtros):
try:
# Creamos la conexion a la base
connection = get_connection()
cursor = connection.cursor()
usuario = filtros[2]
valores = (filtros[0], filtros[1], usuario.get_id())
select_query = ("select spr.nombre as nombreSprint, pro.nombre as nombreProyecto, count(req.id) as cantidadRequerimientos, concat(usu.apellido, ', ', usu.nombre) as nombreUsuario "
"from sprints spr "
"inner join proyectos pro on pro.id = spr.id_proyecto "
"left join requerimientos req on req.id_sprint = spr.id "
"inner join usuario usu on usu.id = pro.id_lider "
"where req.fecha_fin is not null "
"and req.fecha_fin between %s and %s "
"and pro.id_lider = %s "
"group by nombreSprint, nombreProyecto "
"order by nombreUsuario")
# Ejecutamos la query para obtener los resultados del reporte
cursor.execute(select_query, valores)
registros = cursor.fetchall()
connection.close()
# Asignamos el nombre del archivo
reporte_file_name = "reporte.txt"
file = open('files/' + reporte_file_name, 'a')
# Armamos la cabecera
usuario_logueado = filtros[2]
cabecera = "USUARIO LOGUEADO: " + usuario_logueado.get_apellido() + ", " + usuario_logueado.get_nombre()
fecha_desde = filtros[0]
fecha_hasta = filtros[1]
cabecera = cabecera + "\n FECHA DESDE: " + fecha_desde + "\n FECHA HASTA: " + fecha_hasta + "\n"
file.write(cabecera)
if len(registros) > 0:
columns = "Sprint \t\t" + "Proyecto \t\t" + "Cant. Requerimientos \t\t" + "Lider \n"
file.write(columns)
for registro in registros:
detalle = registro[0] + "\t\t" + registro[1] + "\t\t" + registro[2] + "\t\t" + registro[3] + "\n"
file.write(detalle)
else:
file.write("No hay datos!\n")
# Cerramos el archivo
file.close()
if tipo_generacion == 1:
# Implica generacion en la carpeta directory del proyecto
return file
else:
# Implica ademas su envio por mail
asunto = "Envio de reporte por email"
enviar_email(usuario_logueado.get_email(), asunto, "", file)
except (Exception, Error) as error:
print("Error while getting data", error)
def findByIdLider(id_usuario):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = '''select pro.* from proyectos pro where pro.id_lider = %s '''
cursor.execute(select_query, (id_usuario, ))
proyectos, registros = [], cursor.fetchall()
[proyectos.append(buildProyecto(registro)) for registro in registros]
return proyectos
except (Exception, Error) as error:
print("Error while getting data", error)
def findByIdProyecto(id_proyecto):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from sprints where id_proyecto = %s"
cursor.execute(select_query, (id_proyecto, ))
sprints, registros = [], cursor.fetchall()
[sprints.append(buildSprint(registro)) for registro in registros]
return sprints
except (Exception, Error) as error:
print("Error while getting data", error)
def findAll():
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from usuario"
cursor.execute(select_query)
usuarios, registros = [], cursor.fetchall()
[usuarios.append(buildUsuario(registro)) for registro in registros]
return usuarios
except (Exception, Error) as error:
print("Error while getting data", error)
def findByIdProyecto(self, id_proyecto):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from usuarios_proyectos where id_proyecto = %s"
cursor.execute(select_query, (id_proyecto,))
dto_list, registros = [], cursor.fetchall()
[dto_list.append(buildUsuarioProyecto(registro)) for registro in registros]
return dto_list
except (Exception, Error) as error:
print("Error while getting data", error)
def save(usuario_proyecto):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (usuario_proyecto.usuario.id, usuario_proyecto.proyecto.id)
cursor.execute("insert into usuarios_proyectos(id, id_usuario, id_proyecto) "
"values(default, %s, %s)", valores)
return cursor.rowcount
except (Exception, Error) as error:
connection.rollback()
print("Error while getting data", error)
def save(sprint):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (sprint.nombre, sprint.fecha_inicio, sprint.fecha_fin,
sprint.obs, sprint.estado, sprint.proyecto.id)
cursor.execute(
"insert into sprints(id, nombre, fecha_inicio, fecha_fin, obs, estado, id_proyecto) "
"values(default, %s, %s, %s, %s, %s, %s)", valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def save(rol):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (rol.nombre_rol)
cursor.execute(
"INSERT INTO roles(id, nombre_rol) VALUES(default, %s)",
valores)
return cursor.rowcount
except (Exception, Error) as error:
connection.rollback()
print("Error while getting data", error)
def update(sprint):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (sprint.nombre, sprint.fecha_inicio, sprint.fecha_fin,
sprint.obs, sprint.estado, sprint.proyecto.id,
sprint.id)
cursor.execute(
"update sprints set nombre = %s, fecha_inicio = %s, fecha_fin = %s, obs = %s, estado = %s, "
"id_proyecto = %s where id = %s", valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def update(usuario):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (usuario.nombre, usuario.apellido, usuario.genero,
usuario.email, usuario.password, usuario.telefono,
usuario.rol.id, usuario.id)
cursor.execute(
"update usuario set nombre = %s, apellido = %s, genero = %s, email = %s, password = %s, "
"telefono = %s, id_rol = %s where id = %s", valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def save(usuario):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (usuario.nombre, usuario.apellido, usuario.genero,
usuario.email, usuario.password, usuario.telefono,
usuario.rol.id)
cursor.execute(
"insert into usuario(id, nombre, apellido, genero, email, password, telefono, id_rol) "
"values(default, %s, %s, %s, %s, %s, %s, %s)", valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def findByIdUsuario(id_usuario):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from requerimientos where id_usuario = %s"
cursor.execute(select_query, (id_usuario, ))
requerimientos, registros = [], cursor.fetchall()
[
requerimientos.append(builRequerimiento(registro))
for registro in registros
]
return requerimientos
except (Exception, Error) as error:
print("Error while getting data", error)
def findAll():
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from roles"
cursor.execute(select_query)
roles, registros = [], cursor.fetchall()
[
roles.append(Rol(registro[0], registro[1]))
for registro in registros
]
return roles
except (Exception, Error) as error:
print("Error while getting data", error)
def update(req):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (req.nombre, req.cant_hora, req.fecha_inicio,
req.fecha_fin, req.descripcion, req.observacion,
req.sprint.id, req.usuario.id, req.proyecto.id, req.id)
cursor.execute(
"update requerimientos set nombre = %s, cant_horas = %s, fecha_inicio = %s, fecha_fin = %s, descripcion = %s, observacion = %s, "
"id_sprint = %s, id_usuario = %s, id_proyecto = %s where id = %s",
valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def save(req):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
valores = (req.nombre, req.cant_hora, req.fecha_inicio,
req.fecha_fin, req.descripcion, req.observacion,
req.sprint.id, req.proyecto.id)
cursor.execute(
"insert into requerimientos(id, nombre, cant_horas, fecha_inicio, fecha_fin, descripcion, observacion, id_sprint, id_usuario, id_proyecto) "
"values(default, %s, %s, %s, %s, %s, %s, %s, null, %s)",
valores)
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def findByIdSprintAndFechaFinIsNull(id_sprint):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = "select * from requerimientos where id_sprint = %s and fecha_fin is null"
cursor.execute(select_query, (id_sprint, ))
requerimientos, registros = [], cursor.fetchall()
[
requerimientos.append(builRequerimiento(registro))
for registro in registros
]
return requerimientos
except (Exception, Error) as error:
print("Error while getting data", error)
def update(proyecto):
try:
with get_connection() as connection:
cursor = connection.cursor()
valores = (proyecto.nombre, proyecto.descripcion,
proyecto.cantidad_integrantes, proyecto.total_precio,
proyecto.total_horas, proyecto.lider.id, proyecto.id)
cursor.execute(
"update proyectos set nombre = %s, descripcion = %s, cantidad_integrantes = %s, total_precio = %s, total_horas = %s, "
"id_lider = %s where id = %s", valores)
connection.close()
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def save(proyecto):
try:
with get_connection() as connection:
cursor = connection.cursor()
valores = (proyecto.nombre, proyecto.descripcion,
proyecto.cantidad_integrantes, proyecto.total_precio,
proyecto.total_horas, proyecto.lider.id)
cursor.execute(
"insert into proyectos(id, nombre, descripcion, cantidad_integrantes, total_precio, total_horas, id_lider) "
"values(default, %s, %s, %s, %s, %s, %s)", valores)
#rowcount = cursor.rowcount
connection.close()
return cursor.rowcount
except (Exception, Error) as error:
print("Error while getting data", error)
def findByIdUsuarioAndLikeNombre(nombre, id_usuario):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
param = '{}%'.format(nombre)
select_query = '''select distinct pro.*
from proyectos pro
inner join usuarios_proyectos up on up.id_proyecto = pro.id
where pro.nombre like %s up.id_usuario = %s '''
cursor.execute(select_query, (param, id_usuario))
proyectos, registros = [], cursor.fetchall()
[proyectos.append(buildProyecto(registro)) for registro in registros]
return proyectos
except (Exception, Error) as error:
print("Error while getting data", error)
def findUsuarioAvailableByIdProyecto(id_proyecto):
try:
with closing(get_connection()) as connection:
cursor = connection.cursor()
select_query = (
"select * from usuario us "
"where us.id not in (select up.id_usuario "
"from usuarios_proyectos up "
"inner join usuario u on u.id = up.id_usuario "
"inner join roles r on r.id = u.id_rol "
"where up.id_proyecto = %s and r.nombre_rol <> 'ADMIN') and us.id_rol <> 1"
)
cursor.execute(select_query, (id_proyecto, ))
usuarios, registros = [], cursor.fetchall()
[usuarios.append(buildUsuario(registro)) for registro in registros]
return usuarios
except (Exception, Error) as error:
print("Error while getting data", error)
