package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Calendar;
import java.util.Date;
import modelo.Usuario;
import util.ConnectionFactory;
public class UsuarioDao {
private Connection connection;
public UsuarioDao() {
this.connection = new ConnectionFactory().getConnection();
}
public List<Usuario> getLista() {
try {
List<Usuario> usuarios = new ArrayList<Usuario>();
PreparedStatement stmt = this.connection.prepareStatement("select * from usuarios");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Usuario usuario = new Usuario();
usuario.setId(rs.getString("id"));
usuario.setNomeDoUsuario(rs.getString("nome"));
usuario.setLogin(rs.getString("login"));
usuario.setSenha(rs.getString("senha"));
usuario.setPerfil(rs.getString("perfil"));
usuario.setDataAcesso(rs.getString("data_acesso"));
usuarios.add(usuario);
}
rs.close();
stmt.close();
return usuarios;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public int contarRegistros(){
try {
PreparedStatement stmt = this.connection.prepareStatement("select count(id) as Total from usuarios");
ResultSet rs = stmt.executeQuery();
rs.next();
int total = rs.getInt("Total");
return total;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void inserir(Usuario novo) {
try {
String query = "insert into usuarios (nome, login, senha, perfil, status)" + " values (?, ?, ?, ?,?)";
PreparedStatement stmt = this.connection.prepareStatement(query);
stmt.setString (1, novo.getNomeDoUsuario());
stmt.setString(2, novo.getLogin());
stmt.setString(3, novo.getSenha());
stmt.setString(4, novo.getPerfil());
stmt.setString(5,"Ativo");
stmt.execute();
stmt.close();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public int quantidadeInativo(){
try {
PreparedStatement stmt = this.connection.prepareStatement("select * from usuarios");
ResultSet rs = stmt.executeQuery();
int dias = 2;
int contador = 0;
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String data_acesso = formatter.format( new Date() );
while (rs.next()) {
String data2 = data_acesso.toString();
String dia2[] = data2.split("-");
String data1 = rs.getString("data_acesso");
String dia1[] = data1.split("-");
int soma = Integer.parseInt(dia2[2])-Integer.parseInt(dia1[2]);
if(soma >= dias){
contador +=1;
}
}
rs.close();
stmt.close();
return contador;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void atualizarHoraAcesso(Date data_acesso, String id){
try {
String query = "update usuarios "
+ "set "
+ "data_acesso = '"+data_acesso+"' WHERE id = '"+id+"'";
PreparedStatement stmt = this.connection.prepareStatement(query);
stmt.execute();
stmt.close();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public boolean atualizarUsuario(Usuario user){
try {
String query = "update usuarios "
+ "set "
+ "nome = '"+user.getNomeDoUsuario()+"',"
+ "login = '"+user.getLogin()+"',"
+ "senha = '"+user.getSenha()+"',"
+ "perfil = '"+user.getPerfil()+"',"
+ "data_acesso = NOW(),"
+ "status = 'Ativo' WHERE id = '"+user.getId()+"'";
PreparedStatement stmt = this.connection.prepareStatement(query);
stmt.execute();
stmt.close();
connection.close();
return true;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public boolean redefinirSenha(Usuario user){
try {
String query = "update usuarios "
+ "set "
+ "senha = '123456'"
+ "WHERE id = '"+user.getId()+"'";
PreparedStatement stmt = this.connection.prepareStatement(query);
System.out.println(query);
stmt.execute();
stmt.close();
connection.close();
return true;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public boolean alterarSenha(String nova_senha,Usuario user){
try {
String query = "update usuarios "
+ "set "
+ "senha = '"+nova_senha+"'"
+ "WHERE id = '"+user.getId()+"'";
PreparedStatement stmt = this.connection.prepareStatement(query);
stmt.execute();
stmt.close();
connection.close();
return true;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}