public static int ValidateUser(AvencaFuncionario funcionario)
{
using (SqlConnection SqlConn = new SqlConnection(AvencaDB.ConnectionString))
{
using (System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("VALIDATE_USER", SqlConn))
{
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Parameters.AddWithValue("@Username", funcionario.Username);
sqlcomm.Parameters.AddWithValue("@Password", funcionario.Password);
SqlParameter retval = new SqlParameter("@result", SqlDbType.Int);
retval.Direction = ParameterDirection.Output;
sqlcomm.Parameters.Add(retval);
sqlcomm.ExecuteNonQuery();
SqlConn.Close();
return((int)retval.Value);
}
catch (Exception ex)
{
AvencaErrorHandler.eventLogError(ex);
}
}
}
return(0);
}
private AvencaFuncionario createFuncionario()
{
try
{
AvencaFuncionario newUser = new AvencaFuncionario(txtUsername.Text.ToLower(), txtPassword.Text);
newUser.Nome = txtNome.Text.ToUpper();
newUser.CPF = txtCPF.Text.ToUpper();
newUser.DataNascimentoStr = dtpDataNascimento.Text.ToUpper();
newUser.Email = txtEmail.Text.ToUpper();
newUser.Endereco = txtEndereco.Text.ToUpper();
newUser.HorarioEntradaStr = dtpHorarioEntrada.Text.ToUpper();
newUser.HorarioSaidaStr = dtpHorarioSaida.Text.ToUpper();
newUser.Setor = txtSetor.Text.ToUpper();
newUser.Sexo = txtSexo.Text.ToUpper();
newUser.Telefone = txtTelefone.Text.ToUpper();
newUser.IdPermissionGroup = cbGrupo.SelectedIndex;
if (newUser.AddToDB())
{
return(newUser);
}
else
{
return(null);
}
}
catch (Exception ex)
{
AvencaErrorHandler.eventLogError(ex);
}
return(null);
}
public static AvencaFuncionario FuncionarioGet(string pUsername, string pPassword)
{
AvencaFuncionario funcionario = null;
using (var connection = new SqlConnection(AvencaDB.ConnectionString))
{
using (var command = new SqlCommand())
{
try
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = string.Format("SELECT * FROM FUNCIONARIO WHERE Username = @Username AND Password = @Password");
command.Parameters.AddWithValue("@Username", pUsername);
command.Parameters.AddWithValue("@Password", AvencaPermission.HashPassword(pPassword));
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
funcionario = new AvencaFuncionario();
funcionario.Username = pUsername;
funcionario.Password = pPassword;
funcionario.Id = (int)reader["Id"];
funcionario.Nome = reader["Nome"].ToString();
funcionario.CPF = reader["CPF"].ToString();
funcionario.DataNascimentoStr = reader["DataNascimento"].ToString();
funcionario.Email = reader["Email"].ToString();
funcionario.Endereco = reader["Endereco"].ToString();
funcionario.HorarioEntradaStr = reader["HorarioEntrada"].ToString();
funcionario.HorarioSaidaStr = reader["HorarioSaida"].ToString();
funcionario.Setor = reader["Setor"].ToString();
funcionario.Sexo = reader["Sexo"].ToString();
funcionario.Telefone = reader["Telefone"].ToString();
funcionario.IdPermissionGroup = (int)reader["IdPermissionGroup"];
}
reader.Close();
}
catch (Exception ex)
{
AvencaErrorHandler.eventLogError(ex);
}
finally
{
connection.Close();
}
}
}
return(funcionario);
}
public static bool FuncionarioAdd(AvencaFuncionario newUser)
{
var res = false;
using (var connection = new SqlConnection(AvencaDB.ConnectionString))
{
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
@"INSERT INTO [dbo].[Funcionario]
(Nome,CPF,Telefone,Email,Endereco,DataNascimento,HorarioEntrada,
HorarioSaida,Sexo,Setor,Username,Password,IdPermissionGroup)
VALUES (@Nome,@CPF,@Telefone,@Email,@Endereco,@DataNascimento,@HorarioEntrada,
@HorarioSaida,@Sexo,@Setor,@Username,@Password,@IdPermissionGroup)";
command.Parameters.AddWithValue("@Nome", newUser.Nome);
command.Parameters.AddWithValue("@CPF", newUser.CPF);
command.Parameters.AddWithValue("@Telefone", newUser.Telefone);
command.Parameters.AddWithValue("@Email", newUser.Email);
command.Parameters.AddWithValue("@Endereco", newUser.Endereco);
command.Parameters.AddWithValue("@DataNascimento", newUser.DataNascimento);
command.Parameters.AddWithValue("@HorarioEntrada", newUser.HorarioEntrada);
command.Parameters.AddWithValue("@HorarioSaida", newUser.HorarioSaida);
command.Parameters.AddWithValue("@Sexo", newUser.Sexo);
command.Parameters.AddWithValue("@Setor", newUser.Setor);
command.Parameters.AddWithValue("@Username", newUser.Username);
command.Parameters.AddWithValue("@Password", newUser.Password);
command.Parameters.AddWithValue("@IdPermissionGroup", newUser.IdPermissionGroup);
try
{
connection.Open();
var recordsAffected = command.ExecuteNonQuery();
res = recordsAffected > 0;
}
catch (Exception ex)
{
AvencaErrorHandler.eventLogError(ex);
}
finally
{
connection.Close();
}
}
}
return(res);
}
public static int RequestLogin(Form frmCaller, bool isLogoff = false)
{
int userId = 0;
while (userId <= 0)
{
try
{
using (frmLogin fLogin = new frmLogin())
{
if (fLogin.ShowDialog(frmCaller, isLogoff) == DialogResult.OK)
{
Usuario = fLogin.User;
userId = ValidateUser(Usuario);
if (userId > 0)
{
if (!HasPermission(frmCaller))
{
userId = 0;
}
}
}
else
{
break;
}
}
}
catch (Exception ex)
{
AvencaErrorHandler.eventLogError(ex);
}
if (userId == 0)
{
MessageBox.Show(frmCaller, "Permissão Negada", "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
return(userId);
}
public static AvencaFuncionario GetFromDB(string pUsername, string pPassword)
{
AvencaFuncionario user = AvencaDB.FuncionarioGet(pUsername, pPassword);
return(user);
}
private void btnOk_Click(object sender, EventArgs e)
{
User = new AvencaFuncionario(txtUsername.Text.ToLower(), txtPassword.Text);
Properties.Settings.Default.LastUsername = txtUsername.Text.ToLower();
Properties.Settings.Default.Save();
}
