public bool Update(DataGridObject obj)
{
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
try
{
command.CommandText = "update defeito SET Peca = @Peca , Solucao = @Solucao, DescricaoDefeito = @Sintoma , Causa = @Causa where Id = @Id;";
command.Parameters.AddWithValue("@Peca", obj.Peca);
command.Parameters.AddWithValue("@Solucao", obj.Solucao);
command.Parameters.AddWithValue("@Sintoma", obj.Sintoma);
command.Parameters.AddWithValue("@Causa", obj.Causa);
command.Parameters.AddWithValue("@Id", obj.Defeito_Id);
command.ExecuteNonQuery();
conexao.Close();
statusOK = true;
}
catch (Exception e)
{
statusOK = false;
conexao.Close();
throw e;
}
return(statusOK);
}
public void Create(String modelo)
{
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
try
{
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "INSERT INTO modelo (Modelo) VALUE (@modelo);";
command.Parameters.AddWithValue("@modelo", modelo);
command.ExecuteNonQuery();
conexao.Close();
}
catch (Exception e)
{
conexao.Close();
throw e;
}
}
public int Create(Defeito defeito)
{
int id = 0;
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
if (String.IsNullOrEmpty(defeito.Causa) && String.IsNullOrEmpty(defeito.Solucao) && String.IsNullOrEmpty(defeito.Peca))
{
defeito.Causa = "default";
defeito.Peca = "default";
defeito.Solucao = "default";
}
try
{
command.CommandText = "INSERT INTO defeito (Peca,Solucao,DescricaoDefeito,Causa) VALUES (@Peca,@Solucao,@Descricao,@Causa);";
command.Parameters.AddWithValue("@Peca", defeito.Peca);
command.Parameters.AddWithValue("@Solucao", defeito.Solucao);
command.Parameters.AddWithValue("@Descricao", defeito.DescricaoDefeito);
command.Parameters.AddWithValue("@Causa", defeito.Causa);
command.ExecuteNonQuery();
command.CommandText = "SELECT LAST_INSERT_ID() as id;";
MySqlDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (dr.HasRows)
{
if (dr.Read())
{
id = Convert.ToInt32(dr["id"]);
}
}
conexao.Close();
}
catch (Exception e)
{
conexao.Close();
throw e;
}
return(id);
}
public int Create(Equipamento equip)
{
int id = 0;
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
try
{
command.CommandText = "INSERT INTO Equipamento (Marca_Id,TipoEquipamento_Id,Modelo_Id) VALUE (@marcaID,@TipoID,@ModeloID);";
command.Parameters.AddWithValue("@marcaID", equip.marca.Id);
command.Parameters.AddWithValue("@TipoID", equip.tipo.Id);
command.Parameters.AddWithValue("@ModeloID", equip.modelo.Id);
command.ExecuteNonQuery();
command.CommandText = "SELECT LAST_INSERT_ID() as id;";
MySqlDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (dr.HasRows)
{
if (dr.Read())
{
id = Convert.ToInt32(dr["id"]);
}
}
conexao.Close();
}
catch (Exception e)
{
conexao.Close();
throw e;
}
return(id);
}
public List <Modelo> ReadAll()
{
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
List <Modelo> listModelo = null;
try
{
listModelo = new List <Modelo>();
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "SELECT * FROM modelo";
MySqlDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (dr.HasRows)
{
Modelo m = new Modelo();
while (dr.Read())
{
m.Id = (int)dr["Id"];
m.Nome = (string)dr["Modelo"];
listModelo.Add(m);
m = new Modelo();
}
}
}
catch (Exception e)
{
listModelo = null;
throw e;
}
return(listModelo);
}
public bool Create(int equipId, int defeitoID, string obs)
{
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
try
{
if (obs.Equals(""))
{
obs = "default";
}
command.CommandText = "INSERT INTO equipamento_has_defeito (Equipamento_Id,Defeito_Id,observacao) VALUES (@equipId,@defeitoId,@obs);";
command.Parameters.AddWithValue("@equipId", equipId);
command.Parameters.AddWithValue("@defeitoId", defeitoID);
command.Parameters.AddWithValue("@obs", obs);
command.ExecuteNonQuery();
conexao.Close();
statusOk = true;
}
catch (Exception e)
{
statusOk = false;
conexao.Close();
throw e;
}
return(statusOk);
}
public List <DataGridObject> readByCriteria(String criteria)
{
MySqlConnection conexao = MySQLConn.GetInstancia().GetConexao();
conexao.Open();
List <DataGridObject> listObjs = null;
try
{
listObjs = new List <DataGridObject>();
MySqlCommand command = new MySqlCommand();
command.Connection = conexao;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "SELECT tb.Id as Equipamento_Id,tb.Marca as Marca ,tb.Tipo as Tipo, tb.Modelo as Modelo, equipamento_has_defeito.Observacao as Obs, defeito.Id as Defeito_Id ," +
" defeito.DescricaoDefeito as Sintoma ,defeito.Peca as Peca,defeito.Causa as Causa ,defeito.Solucao as Solucao FROM " +
"(SELECT equipamento.Id as Id, modelo.Modelo as Modelo,marca.Nome as Marca,tipoequipamento.Tipo as Tipo FROM equipamento " +
"join modelo on equipamento.Modelo_Id = modelo.Id JOIN marca ON equipamento.Marca_Id = marca.Id " +
"JOIN tipoequipamento ON equipamento.TipoEquipamento_Id = tipoequipamento.Id where modelo.Modelo = @criteria) " +
"tb join equipamento_has_defeito ON tb.Id = equipamento_has_defeito.Equipamento_Id JOIN defeito ON equipamento_has_defeito.Defeito_Id = defeito.Id;";
command.Parameters.AddWithValue("@criteria", criteria);
MySqlDataReader dr = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (dr.HasRows)
{
DataGridObject dto = new DataGridObject();
while (dr.Read())
{
dto.Causa = (string)dr["Causa"];
dto.Defeito_Id = Convert.ToInt32(dr["Defeito_Id"]);
dto.EquipId = Convert.ToInt32(dr["Equipamento_Id"]);
dto.Marca = (string)dr["Marca"];
dto.Modelo = (string)dr["Modelo"];
dto.Observacao = (string)dr["Obs"];
dto.Peca = (string)dr["Peca"];
dto.Sintoma = (string)dr["Sintoma"];
dto.Solucao = (string)dr["Solucao"];
dto.Tipo = (string)dr["Tipo"];
listObjs.Add(dto);
dto = new DataGridObject();
}
}
}
catch (Exception e)
{
listObjs = null;
throw e;
}
return(listObjs);
}