public void Delete(Req_exame reqExame) { try { this.AbrirConexao(); cmd = new SqlCommand(@"DELETE FROM [REQ_EXAME] WHERE [ID_PACIENTE] = @id_paciente and [ID_EXAME] = @id_exame and [DTEXAME] = @dtexame", tran.Connection, tran); cmd.Parameters.AddWithValue("@id_paciente", reqExame.id_paciente); cmd.Parameters.AddWithValue("@id_exame", reqExame.id_exame); cmd.Parameters.AddWithValue("@dtexame", reqExame.dtexame); cmd.Transaction = tran; cmd.ExecuteNonQuery(); tran.Commit(); } catch (Exception e) { tran.Rollback(); throw new Exception("Erro ao Deletar Requisição do Exame: " + e.Message); } finally { this.FecharConexao(); } }
public void Insert(Req_exame reqExame) { try { this.AbrirConexao(); if (VerificaExameConv(reqExame.id_exame, reqExame.convenio)) { cmd = new SqlCommand(@"INSERT INTO [REQ_EXAME] ([ID_PACIENTE], [ID_EXAME], [DTEXAME], [VALOR], [TIPO], [CONVENIO]) VALUES (@id_paciente, @id_exame, @dtexame, @valor, @tipo, @convenio)", tran.Connection, tran); cmd.Parameters.AddWithValue("@id_paciente", reqExame.id_paciente); cmd.Parameters.AddWithValue("@id_exame", reqExame.id_exame); cmd.Parameters.AddWithValue("@dtexame", reqExame.dtexame); cmd.Parameters.AddWithValue("@valor", reqExame.valor); cmd.Parameters.AddWithValue("@tipo", reqExame.tipo); cmd.Parameters.AddWithValue("@convenio", reqExame.convenio); cmd.Transaction = tran; cmd.ExecuteNonQuery(); tran.Commit(); } } catch (Exception e) { tran.Rollback(); throw new Exception("Erro ao Inserir Requisição do Exame: " + e.Message); } finally { this.FecharConexao(); } }
public void UPDATE(Req_exame reqExame) { try { this.AbrirConexao(); if (VerificaExameConv(reqExame.id_exame, reqExame.convenio)) { cmd = new SqlCommand(@"UPDATE [REQ_EXAME] SET [VALOR] = @valor, [TIPO] = @tipo, [CONVENIO] = @convenio WHERE [ID_PACIENTE] = @id_paciente and [ID_EXAME] = @id_exame and [DTEXAME] = @dtexame", tran.Connection, tran); cmd.Parameters.AddWithValue("@id_paciente", reqExame.id_paciente); cmd.Parameters.AddWithValue("@id_exame", reqExame.id_exame); reqExame.dtexameup = reqExame.dtexameup.Replace("/", "-"); DateTime dt = DateTime.ParseExact(reqExame.dtexameup, "dd-MM-yyyy HH:mm:ss", null); reqExame.dtexame = dt; cmd.Parameters.AddWithValue("@dtexame", reqExame.dtexame); cmd.Parameters.AddWithValue("@valor", reqExame.valor); cmd.Parameters.AddWithValue("@tipo", reqExame.tipo); cmd.Parameters.AddWithValue("@convenio", reqExame.convenio); cmd.Transaction = tran; cmd.ExecuteNonQuery(); tran.Commit(); } } catch (Exception e) { tran.Rollback(); throw new Exception("Erro ao Atualizar Requisição do Exame: " + e.Message); } finally { this.FecharConexao(); } }
public Req_exame ObterReqExame(Int32 id_paciente, Int32 id_exame, DateTime dtconsulta) { try { this.AbrirConexao(); cmd = new SqlCommand("SELECT * FROM [REQ_EXAME] WHERE [id_paciente] = @paciente and [id_exame] = @exame and [DTEXAME] = @data", tran.Connection, tran); cmd.Parameters.AddWithValue("@paciente", id_paciente); cmd.Parameters.AddWithValue("@exame", id_exame); DateTime dt = DateTime.ParseExact("01-01-0001 00:00:00", "dd-MM-yyyy HH:mm:ss", null); if (dtconsulta == dt) { dtconsulta = DateTime.ParseExact("01-01-1800 00:00:00", "dd-MM-yyyy HH:mm:ss", null); } cmd.Parameters.AddWithValue("@data", dtconsulta); Req_exame ReqExame = null; dr = cmd.ExecuteReader(); if (dr.Read()) { ReqExame = new Req_exame(); ReqExame.id_paciente = Convert.ToInt32(dr["id_paciente"]); ReqExame.id_exame = Convert.ToInt32(dr["id_exame"]); ReqExame.dtexame = Convert.ToDateTime(dr["DTEXAME"]); ReqExame.dtexameup = ReqExame.dtexame.ToString(); ReqExame.valor = Convert.ToDecimal(dr["VALOR"]); ReqExame.tipo = Convert.ToChar(dr["TIPO"]); ReqExame.convenio = Convert.ToString(dr["CONVENIO"]); } return(ReqExame); } catch (Exception e) { throw new Exception("Erro ao obter dados: " + e.Message); } finally { this.FecharConexao(); } }
public List <Req_exame> ListarReqExame(DateTime Data, String nomePaci, String nomeConv) { try { this.AbrirConexao(); string query; DateTime dt = DateTime.ParseExact("01-01-0001 00:00:00", "dd-MM-yyyy HH:mm:ss", null); if (Data == dt) { query = @"SELECT * FROM REQ_EXAME R JOIN EXAME E ON R.id_exame = E.Id JOIN PACIENTE P ON R.id_paciente = P.Id FULL OUTER JOIN CONVENIO C ON R.CONVENIO = C.Id WHERE (R.id_paciente is not null) and (R.DTEXAME >= @data) and (@nomepaci is null or P.NOME = @nomepaci) and (@nomeconv is null or E.NOME = @nomeconv) ORDER BY 3 desc;"; cmd = new SqlCommand(query, tran.Connection, tran); } else { query = @"SELECT * FROM REQ_EXAME R JOIN EXAME E ON R.id_exame = E.Id JOIN PACIENTE P ON R.id_paciente = P.Id FULL OUTER JOIN CONVENIO C ON R.CONVENIO = C.Id WHERE (R.id_paciente is not null) and (R.DTEXAME = @data) and (@nomepaci is null or P.NOME = @nomepaci) and (@nomeconv is null or E.NOME = @nomeconv) ORDER BY 3 desc;"; cmd = new SqlCommand(query, tran.Connection, tran); } if (Data == dt) { DateTime dtvalid = DateTime.ParseExact("01-01-1800 00:00:00", "dd-MM-yyyy HH:mm:ss", null); cmd.Parameters.AddWithValue("@data", dtvalid); } else { cmd.Parameters.AddWithValue("@data", Data); } //if (String.IsNullOrEmpty(Data)) //{ // Data = "01-01-1800 00:00:00"; // DateTime dt = DateTime.ParseExact(Data, "dd-MM-yyyy HH:mm:ss", null); // cmd.Parameters.AddWithValue("@data", dt); //} //else //{ // Data = Data.Replace("/", "-"); // Data = Data + " 00:00:00"; // DateTime dt = DateTime.ParseExact(Data, "dd-MM-yyyy HH:mm:ss", null); // cmd.Parameters.AddWithValue("@data", dt); //} if (String.IsNullOrEmpty(nomePaci)) { cmd.Parameters.AddWithValue("@nomepaci", DBNull.Value); } else { cmd.Parameters.AddWithValue("@nomepaci", nomePaci); } if (String.IsNullOrEmpty(nomeConv)) { cmd.Parameters.AddWithValue("@nomeconv", DBNull.Value); } else { cmd.Parameters.AddWithValue("@nomeconv", nomeConv); } dr = cmd.ExecuteReader(); List <Req_exame> List = new List <Req_exame>(); while (dr.Read()) { Req_exame ReqExame = new Req_exame(); ReqExame.id_paciente = Convert.ToInt32((dr.GetValue(0)) == DBNull.Value ? 0 : (dr.GetValue(0))); ReqExame.nomePaci = Convert.ToString((dr.GetValue(10))); ReqExame.dtexame = Convert.ToDateTime((dr.GetValue(2))); ReqExame.id_exame = Convert.ToInt32((dr.GetValue(1))); ReqExame.nomeExame = Convert.ToString((dr.GetValue(7))); ReqExame.id_convenio = Convert.ToInt32((dr.GetValue(5)).ToString() == "-1" ? 0 : (dr.GetValue(5))); ReqExame.nomeConv = Convert.ToString((dr.GetValue(20))); ReqExame.tipo = Convert.ToChar((dr.GetValue(4))); ReqExame.valor = Convert.ToDecimal((dr.GetValue(3)) == DBNull.Value ? 0.00 : (dr.GetValue(3))); List.Add(ReqExame); } return(List); } catch (Exception e) { throw new Exception("Erro ao listar as Requsições dos Exames: " + e.Message); } finally { this.FecharConexao(); } }
public ReqExameDAO() { Req_exame ReqExame = new Req_exame(); }