public string insert()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_salvarOcorrencia");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pId_departamento", Id_departamento).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_defeito", Ds_Defeito).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_solucao", Ds_solucao).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDt_entrada", Dt_entrada).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDt_saida", Dt_saida).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_retirante", Nm_retirante).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_status", Ds_status).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_maquina", Nm_maquina).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pPatrimonio_os", Patrimonio_os).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_usuario", Id_usuario).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Ocorrência aberta com sucesso!";
getLastOcorrencia();
MandarHistorico();
}
catch (Exception ex)
{
ds_msg = "Erro ao gravar ocorrência!" + ex.Message;
}
return(ds_msg);
}
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";
}
}
public void remove()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_retirarOcorrencia");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pId_departamento", Id_departamento).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_defeito", Ds_Defeito).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_solucao", Ds_solucao).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDt_entrada", Dt_entrada).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDt_saida", Dt_saida).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_retirante", Nm_retirante).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_maquina", Nm_maquina).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_status", Ds_status).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_usuario", Id_usuario).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pPatrimonio_os", Patrimonio_os).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNr_ocorrencia", Nr_ocorrencia).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Ocorrência removida com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao atualizar ocorrência!" + ex.Message;
}
}
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);
}
//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 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 update()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_editarDept");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pNm_departamento", Nm_departamento).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_departamento", Id_departamento).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Departamento atualizado com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao atualizar Departamento!" + ex.Message;
}
}
public string delete()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_deleteResp");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pId_Usuario", Id_usuario).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_Departamento", Id_Departamento).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Responsável deletado com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao deletar responsável!" + ex.Message;
}
return(ds_msg);
}
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 string insert()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_salvarDept");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pNm_departamento", Nm_departamento).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Departamento gravado com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao gravar Departamento!" + ex.Message;
}
return(ds_msg);
}
public string insert()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_salvarProcedimento");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pDs_procedimento", Ds_Procedimento).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNr_Ocorrencia", Nr_ocorrencia).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pStatus_Atual", Status_atual).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Procedimento registrado com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao gravar Procedimento!" + ex.Message;
}
return(ds_msg);
}
public void MandarHistorico()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_salvarHistorico");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pDs_defeito", Ds_Defeito).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDt_entrada", Dt_entrada).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_maquina", Nm_maquina).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_status", Ds_status).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_usuario", Id_usuario).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNr_ocorrencia", Nr_ocorrencia).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Ocorrência salva no historico!";
}
catch (Exception ex)
{
ds_msg = "Erro ao salvar historico!" + ex.Message;
}
}
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 string Insert()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_salvarAtendimento");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pId_Departamento", Id_departamento).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_User", Id_user).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_Solicitante", Nm_solicitante).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pNm_Maquina", Nm_maquina).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pProblema", Problema).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pSolucao", Solucao).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Atendimento salvo com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao gravar Procedimento!" + ex.Message;
}
return(ds_msg);
}
public void update()
{
try
{
clsConexao instancia_conexao = new clsConexao();
MySqlCommand sql_cmd = new MySqlCommand("sp_editarUser");
sql_cmd.CommandType = CommandType.StoredProcedure;
sql_cmd.Parameters.AddWithValue("pNm_usuario", Nm_Usuario).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_login", Ds_login).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_senha", Ds_senha).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_perfil", Ds_perfil).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_dept", Id_Departamento).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pId_usuario", Id_usuario).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pemail", email).Direction = ParameterDirection.Input;
sql_cmd.Parameters.AddWithValue("pDs_ativo", Ds_ativo).Direction = ParameterDirection.Input;
instancia_conexao.CRUD(sql_cmd);
ds_msg = "Usuário atualizado com sucesso!";
}
catch (Exception ex)
{
ds_msg = "Erro ao atualizar usuário!" + ex.Message;
}
}
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());
}
}
