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)