public void AutenticarUser()
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_usuario, nm_usuario, ds_perfil, email, ds_ativo FROM tb_usuario WHERE ds_login = '******' AND ds_senha = '" + Ds_senha + "'";
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
if (sql_dr.Read())
{
if (Convert.ToInt32(sql_dr["ds_ativo"]) == 0)
{
ds_msg = "Usuário inativo!";
}
else
{
Id_usuario = Convert.ToInt32(sql_dr["id_usuario"].ToString());
Nm_Usuario = sql_dr["nm_usuario"].ToString();
Ds_perfil = sql_dr["ds_perfil"].ToString();
ds_msg = "";
}
}
else
{
ds_msg = "Usuario/senha inválidos";
}
}
//selecionar departamento por nome
public MySqlDataReader carregarDeptByName(string nm_departamento)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_departamento, nm_departamento FROM tb_departamento WHERE nm_departamento LIKE '%" + nm_departamento + "%' ORDER BY nm_departamento ASC";
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
return(sql_dr);
}
public MySqlDataReader getRespByIdDept(int idDept)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_usuario, id_departamento FROM tb_responsaveis WHERE id_departamento = " + idDept.ToString();
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
return(sql_dr);
}
public MySqlDataReader getList(string orderBy)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_usuario, id_departamento FROM tb_responsaveis " + orderBy;
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
return(sql_dr);
}
public MySqlDataReader getAtendimentoByDate(string month, string year)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT" +
" A.id_atendimento, A.id_departamento, A.id_user, A.nm_solicitante, A.nm_maquina, A.problema, A.solucao, A.dt_atendimento " +
" FROM tb_historico_atendimento A" +
" WHERE dt_atendimento like '" + year + "-" + month + "%'";
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
return(sql_dr);
}
public MySqlDataReader getOcorenciasByDate(string month, string year)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT" +
" O.nm_maquina, O.dt_entrada, O.dt_saida " +
" FROM tb_ocorrencia O" +
" WHERE dt_entrada like '" + year + "-" + month + "%'";
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
return(sql_dr);
}
public MySqlDataReader getUserByFiltro(string filtro, string orderBy)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_usuario, nm_usuario, ds_perfil, id_departamento, email, ds_ativo FROM tb_usuario WHERE nm_usuario LIKE '%" + filtro + "%' OR ds_perfil LIKE '%" + filtro + "%' " + orderBy;
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
/*if (sql_dr.Read())
* {
* Id_usuario = Convert.ToInt32(sql_dr["id_usuario"].ToString());
* }*/
return(sql_dr);
}
public void carregarDeptByID()
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_departamento, nm_departamento FROM tb_departamento WHERE id_departamento = " + Id_departamento + " ORDER BY nm_departamento ASC";
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
if (sql_dr.Read())
{
Id_departamento = Convert.ToInt32(sql_dr["id_departamento"].ToString());
Nm_departamento = sql_dr["nm_departamento"].ToString();
}
}
public MySqlDataReader getOcorrenciasByFiltro(string ds_filtro)
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT" +
" O.id_usuario, O.nr_ocorrencia,O.id_departamento, D.nm_departamento, O.ds_defeito, O.ds_solucao, O.dt_entrada, O.dt_saida, O.nm_retirante, O.nm_maquina, O.ds_status, U.nm_usuario" +
" FROM tb_ocorrencia O INNER JOIN tb_departamento D" +
" ON O.id_departamento = D.id_departamento " +
" INNER JOIN tb_usuario U" +
" ON O.id_usuario = U.id_usuario " + ds_filtro;
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
return(sql_dr);
}
public void getLastOcorrencia()
{
ds_msg = "";
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT" +
" O.id_usuario, O.nr_ocorrencia, O.id_departamento, O.ds_defeito, O.ds_solucao, O.dt_entrada, O.dt_saida, O.nm_retirante, O.nm_maquina, O.ds_status" +
" FROM tb_ocorrencia O " +
" ORDER BY nr_ocorrencia DESC LIMIT 1";
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
if (sql_dr.Read())
{
Id_usuario = Convert.ToInt32(sql_dr["id_usuario"].ToString());
Nr_ocorrencia = Convert.ToInt32(sql_dr["nr_ocorrencia"].ToString());
Id_departamento = Convert.ToInt32(sql_dr["id_departamento"].ToString());
Ds_Defeito = sql_dr["ds_defeito"].ToString();
Ds_solucao = sql_dr["ds_solucao"].ToString();
Dt_entrada = Convert.ToDateTime(sql_dr["dt_entrada"].ToString());
try
{
Dt_saida = Convert.ToDateTime(sql_dr["dt_saida"].ToString());
}
catch
{ }
Nm_retirante = sql_dr["nm_retirante"].ToString();
Nm_maquina = sql_dr["nm_maquina"].ToString();
Ds_status = sql_dr["ds_status"].ToString();
}
else
{
ds_msg = "Erro ao obter ocorrência!";
}
}
public void getUserById()
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand();
sql_cmd.CommandType = CommandType.Text;
string sql_query = "SELECT id_usuario, nm_usuario, ds_perfil, ds_login, ds_senha, email, ds_ativo, u.id_departamento, d.nm_departamento FROM tb_usuario u INNER JOIN tb_departamento d on u.id_departamento = d.id_departamento WHERE id_usuario = " + Id_usuario;
sql_cmd.CommandText = sql_query;
MySqlDataReader sql_dr = instancia_conexao.selecionar(sql_cmd);
if (sql_dr.Read())
{
Id_usuario = Convert.ToInt32(sql_dr["id_usuario"].ToString());
Nm_Usuario = sql_dr["nm_usuario"].ToString();
Ds_perfil = sql_dr["ds_perfil"].ToString();
Ds_login = sql_dr["ds_login"].ToString();
Ds_senha = sql_dr["ds_senha"].ToString();
email = sql_dr["email"].ToString();
Ds_ativo = Convert.ToInt32(sql_dr["ds_ativo"]);
Id_Departamento = Convert.ToInt32(sql_dr["id_departamento"].ToString());
}
}