public string validarLogin()
{
try
{
SqlCommand cmd = new SqlCommand("sp_ValidarLogin", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pLogin", Ds_Prontuario);
cmd.Parameters.AddWithValue("@pSenha", Ds_Senha);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
UserLogado = new ClsLogin();
UserLogado.ID_Usuario = int.Parse(dr["ID"].ToString());
UserLogado.Nm_Usuario = dr["Nome"].ToString();
UserLogado.Ds_Prontuario = Ds_Prontuario;
UserLogado.Dt_CadastroUsuario = dr["DataCadastro"].ToString();
UserLogado.Dt_UltimoLogin = dr["UltimoLogin"].ToString();
UserLogado.ID_Permissoes = ClsUsuario.getPermissoesUsuario(UserLogado.ID_Usuario);
setDataLogin();
return("true");
}
else
{
return("false");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
}
public string InserirNovoLocador(string nome, string prontuario)
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_Locatario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Locatario", nome);
cmd.Parameters.AddWithValue("@pRA_Locatario", prontuario);
SqlDataReader dr = cmd.ExecuteReader();
string result = string.Empty;
while (dr.Read())
{
result += dr[0].ToString();
}
if (result.Length == 1)
{
return(result);
}
else if (result.Length > 1)
{
return("Digite o Nome e o Prontuario do Solicitante corretamente, pois foram encotrados mais de uma pessoa com os dados informados");
}
else
{
cmd = new SqlCommand("sp_Ins_Locatario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Locatario", nome);
cmd.Parameters.AddWithValue("@pRA_Locatario", prontuario);
dr = cmd.ExecuteReader();
if (dr.Read())
{
return(dr[0].ToString());
}
else
{
return("Falaha Ao Inserir Locatario, Contate o Administrador");
}
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Inserir()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Ins_Usuario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Usuario", Nm_Usuario);
cmd.Parameters.AddWithValue("@pDs_Prontuario", Ds_Prontuario);
cmd.Parameters.AddWithValue("@pDs_Senha", Ds_Senha);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
return(inserirPermissoesUsuario(int.Parse(dr["ID"].ToString()), ID_Permissoes));
}
else
{
return("Falha no banco de dados ao tenatr inserir funcionario/nA função ExecuteNomQuery Retornou 0 linhas afetadas quando o valor deveria ser igual ou mair que 1");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Excluir()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Del_Usuario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pId_Usuario", ID_Usuario);
if (cmd.ExecuteNonQuery() > 0)
{
return("OK");
}
else
{
return("Função ExecuteNonQuery Retornou 0 linhas alteradas, sendo que devia ter sido alterado pelo menos 1.");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
//ClsConexao conexao = new ClsConexao();
public override string Inserir()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Ins_NovoEmprestimo", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Equipamento", ID_Equipamento);
cmd.Parameters.AddWithValue("@pDt_Retirada", Convert.ToDateTime(Dt_Retirada));
cmd.Parameters.AddWithValue("@pID_Locatario_Saida", ID_Locatario_Saida);
cmd.Parameters.AddWithValue("@pID_Locador_Saida", ID_Locador_Saida);
cmd.Parameters.AddWithValue("@pDt_PrevistaDevolucao", Convert.ToDateTime(Dt_PrevistaDevolucao));
if (cmd.ExecuteNonQuery() > 0)
{
return("OK");
}
else
{
return("Procedimento NonQuery nao retornou nehuma linha alterada");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public static List <int> getPermissoesUsuario(int id)
{
ClsConexao conexao = new ClsConexao();
List <int> permissoes = new List <int>();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_PermissoesUsuario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Usuario", id);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
permissoes.Add(int.Parse(dr["ID"].ToString()));
}
conexao.desconectar();
return(permissoes);
}
catch
{
conexao.desconectar();
return(null);
}
}
public override string Consultar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_Equipamento", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Equipamento", ID_Equipamento);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
Nm_Equipamento = dr["Nome"].ToString();
Nr_Equipamento = int.Parse(dr["Numero"].ToString());
Ds_Tipo = dr["Tipo"].ToString();
return("OK");
}
else
{
return("Erro Sem Explicação");
}
}
catch (Exception ex)
{
conexao.desconectar();
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
//private ClsConexao conexao = new ClsConexao();
public override string Atualizar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Upd_Emprestimo", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Locador_Devolucao", ID_Locador_Devolucao);
cmd.Parameters.AddWithValue("@pID_Locatario_Devolucao", ID_Locatario_Devolucao);
cmd.Parameters.AddWithValue("@pDt_EfetivaDevolucao", Convert.ToDateTime(Dt_EfetivaDevolucao));
cmd.Parameters.AddWithValue("@pID_Emprestimo", ID_Emprestimo);
if (cmd.ExecuteNonQuery() > 0)
{
return("OK");
}
else
{
return("0 linhas afetas quando o valor deveria ser 1 ou mais");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public string Grid(string nome, string prontuario)
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Grid_Locatario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Locatario", nome);
cmd.Parameters.AddWithValue("@pRA_Locatario", prontuario);
SqlDataAdapter da = new SqlDataAdapter(cmd);
Ds = new DataSet();
//Dt = new DataTable();
da.Fill(Ds);
//da.Fill(Dt);
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Consultar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_Emprestimo", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Emprestimo", ID_Emprestimo);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
ID_Locador_Saida = int.Parse(dr["ID_Locador_Saida"].ToString());
ID_Locatario_Saida = int.Parse(dr["ID_Locatario_Saida"].ToString());
Dt_PrevistaDevolucao = dr["Dt_PrevistaDevolucao"].ToString();
Dt_Retirada = dr["Dt_Retirada"].ToString();
return("OK");
}
else
{
return("Data Reader Retornou vazio, quando o mesmo deveria ter dados");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Consultar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_Emprestimo", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Emprestimo", ID_Emprestimo);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
Nm_Equipamento = dr["NomeEquipamento"].ToString();
Ds_TipoItem = dr["Ds_Tipo"].ToString();
Nm_Locatario = dr["Nm_Locatario"].ToString();
RA_Locatario = dr["RA_Locatario"].ToString();
Nm_Locador = dr["Nm_Locador"].ToString();
RA_Locador = dr["RA_Locador"].ToString();
Dt_Retirada = dr["Dt_Retirada"].ToString();
Dt_PrevistaDevolucao = dr["Dt_PrevistaDevolucao"].ToString();
return("OK");
}
else
{
return("Data Reader Vazio Quando o Mesmo Deveria ter ao menos uma linha");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public string ConsultarExistente()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_EquipamentoExistente", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Equipamento", Nm_Equipamento);
cmd.Parameters.AddWithValue("@pNr_Equipamento", Nr_Equipamento);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
return("true");
}
else
{
return("false");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Consultar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_Usuario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pId_Usuario", ID_Usuario);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
dr.Read();
Nm_Usuario = dr["Nome"].ToString();
Ds_Prontuario = dr["Login"].ToString();
Ds_Senha = dr["Senha"].ToString();
Dt_CadastroUsuario = dr["DataCadastro"].ToString();
Dt_UltimoLogin = dr["UltimoLogin"].ToString();
ID_Permissoes = getPermissoesUsuario(ID_Usuario);
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Atualizar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Upd_Usuario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Usuario", Nm_Usuario);
cmd.Parameters.AddWithValue("@pDs_Prontuario", Ds_Prontuario);
cmd.Parameters.AddWithValue("@pDs_Senha", Ds_Senha);
cmd.Parameters.AddWithValue("@pId_Usuario", ID_Usuario);
if (cmd.ExecuteNonQuery() > 0)
{
return(inserirPermissoesUsuario(ID_Usuario, ID_Permissoes));
}
else
{
return("Falha no banco de dados ao tentar atualizar usuario \n A função ExecuteNomQuery retornou 0 linhas afetas quando o valor deveria ser um mais.");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Atualizar()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Upd_Equipamento", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Equipamento", Nm_Equipamento);
cmd.Parameters.AddWithValue("@pDs_Tipo", Ds_Tipo);
cmd.Parameters.AddWithValue("@pNr_Equipamento", Nr_Equipamento);
cmd.Parameters.AddWithValue("@pID_Equipamento", ID_Equipamento);
if (cmd.ExecuteNonQuery() > 0)
{
return("OK");
}
else
{
return("Erro Desconhecido");
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Grid()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Grid_Equipamento", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNm_Equipamento", Nm_Equipamento);
cmd.Parameters.AddWithValue("@pNr_Equipamento", Nr_Equipamento);
cmd.Parameters.AddWithValue("@pDs_Tipo", Ds_Tipo);
SqlDataAdapter da = new SqlDataAdapter(cmd);
Ds = new DataSet();
da.Fill(Ds);
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public string Grid(string tipo)
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Grid_Equipamento_Disponivel", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pDs_Tipo", tipo);
SqlDataAdapter da = new SqlDataAdapter(cmd);
//Ds = new DataSet();
Dt = new DataTable();
da.Fill(Dt);
return "OK";
}
catch (Exception ex)
{
return (ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Grid()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Grid_Relatorio", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
if (!string.IsNullOrWhiteSpace(Dt_SaidaInicio))
{
cmd.Parameters.AddWithValue("@pDt_SaidaInicio", Convert.ToDateTime(Dt_SaidaInicio));
cmd.Parameters.AddWithValue("@pDt_SaidaFim", Convert.ToDateTime(Dt_SaidaFim));
}
else
{
cmd.Parameters.AddWithValue("@pDt_SaidaInicio", "");
cmd.Parameters.AddWithValue("@pDt_SaidaFim", "");
}
if (!string.IsNullOrWhiteSpace(Dt_DevolucaoFim))
{
cmd.Parameters.AddWithValue("@pDt_DevolucaoInicio", Convert.ToDateTime(Dt_DevolucaoInicio));
cmd.Parameters.AddWithValue("@pDt_DevolucaoFim", Convert.ToDateTime(Dt_DevolucaoFim));
}
else
{
cmd.Parameters.AddWithValue("@pDt_DevolucaoInicio", "");
cmd.Parameters.AddWithValue("@pDt_DevolucaoFim", "");
}
cmd.Parameters.AddWithValue("@pNm_Equipamento", Nm_Equipamento);
cmd.Parameters.AddWithValue("@pNm_Locatario", Nm_Locatario);
cmd.Parameters.AddWithValue("@pNm_LocadorSaida", Nm_LocadorSaida);
cmd.Parameters.AddWithValue("@pNm_LocadorDevolucao", Nm_LocadorDevolucao);
cmd.Parameters.AddWithValue("@pDs_Status", Ds_Status);
cmd.Parameters.AddWithValue("@pID_Equipamento", ID_Equipamento);
cmd.Parameters.AddWithValue("@pID_Locador_Devolucao", ID_Locador_Devolucao);
cmd.Parameters.AddWithValue("@pID_Locador_Saida", ID_Locador_Saida);
cmd.Parameters.AddWithValue("@pID_Locatario_Saida", ID_Locatario_Saida);
SqlDataAdapter da = new SqlDataAdapter(cmd);
Dt = new DataTable();
da.Fill(Dt);
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
private string inserirPermissoesUsuario(int id, List <int> permissoes)
{
conexao = new ClsConexao();
SqlTransaction transaction;
conexao.conectar();
transaction = conexao.conexao.BeginTransaction("transPermissao");
try
{
SqlCommand cmd = new SqlCommand("sp_Del_PermissoesUsuario", transaction.Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = transaction;
cmd.Parameters.AddWithValue("@pID_Usuario", id);
cmd.ExecuteNonQuery();
for (int i = 0; i < ID_Permissoes.Count; i++)
{
cmd = new SqlCommand("sp_Ins_PermissoesUsuario", transaction.Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = transaction;
cmd.Parameters.AddWithValue("@pID_Usuario", id);
cmd.Parameters.AddWithValue("@pID_Funcionalidade", ID_Permissoes[i]);
cmd.ExecuteNonQuery();
}
transaction.Commit();
conexao.desconectar();
return("OK");
}
catch (Exception ex)
{
try
{
transaction.Rollback();
return(ex.ToString());
}
catch (Exception ex2)
{
return(ex.ToString() + ex2.ToString());
}
}
}
public override string Grid()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Grid_Log", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
Dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(Dt);
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Inserir()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Ins_Log", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pId_Usuario", ID_Usuario);
cmd.Parameters.AddWithValue("@pDs_Log", Ds_Log);
cmd.ExecuteNonQuery();
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public override string Grid()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Grid_EmprestimosAbertos", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
Ds = new DataSet();
da.Fill(Ds);
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public string getFuncionalidades()
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_Funcionalidades", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
Ds = new DataSet();
da.Fill(Ds);
conexao.desconectar();
return("OK");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}
public static bool validarProntuario(string prontuario)
{
ClsConexao conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_ValidarProntuario", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pDs_Prontuario", prontuario);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
return(false);
}
else
{
return(true);
}
}
catch
{
return(false);
}
}
public string Consultar(int id)
{
conexao = new ClsConexao();
try
{
SqlCommand cmd = new SqlCommand("sp_Sel_ID_EmprestimoAberto", conexao.conectar());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pID_Equipamento", id);
SqlDataReader dr = cmd.ExecuteReader();
//if (dr.Read())
dr.Read();
return(dr["ID"].ToString());
//else
// return "Dr Retornou vazio, quando o mesmo deveria ter dados";
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conexao.desconectar();
}
}