public void SqlCommandAtualizaReserva(SqlConnection conexao, Reserva reserva)
{
SqlCommand comando = new SqlCommand(null, conexao);
// Create and prepare an SQL statement.
comando.CommandText =
"UPDATE Reserva SET id_filial=@id_filial, id_sala=@id_sala, dt_inicio=@dt_inicio, dt_fim=@dt_fim, id_responsavel=@id_responsavel, dv_cafe=@dv_cafe, qt_cafe=@qt_cafe, nm_descricao=@nm_descricao " +
"WHERE id_reserva = @id_reserva";
SqlParameter id_filial = new SqlParameter("@id_filial", SqlDbType.UniqueIdentifier, 0);
SqlParameter id_sala = new SqlParameter("@id_sala", SqlDbType.UniqueIdentifier, 0);
SqlParameter dt_inicio = new SqlParameter("@dt_inicio", SqlDbType.DateTime, 0);
SqlParameter dt_fim = new SqlParameter("@dt_fim", SqlDbType.DateTime, 0);
SqlParameter id_responsavel = new SqlParameter("@id_responsavel", SqlDbType.UniqueIdentifier, 0);
SqlParameter dv_cafe = new SqlParameter("@dv_cafe", SqlDbType.Bit, 0);
SqlParameter qt_cafe = new SqlParameter("@qt_cafe", SqlDbType.SmallInt, 0);
SqlParameter nm_descricao = new SqlParameter("@nm_descricao", SqlDbType.Text, 200);
SqlParameter id_reserva = new SqlParameter("@id_reserva", SqlDbType.UniqueIdentifier, 0);
id_filial.Value = reserva.id_filial;
id_sala.Value = reserva.id_sala;
dt_inicio.Value = reserva.dt_inicio;
dt_fim.Value = reserva.dt_fim;
id_responsavel.Value = reserva.id_responsavel;
dv_cafe.Value = reserva.dv_cafe;
qt_cafe.Value = reserva.qt_cafe;
nm_descricao.Value = reserva.nm_descricao;
id_reserva.Value = reserva.id_reserva;
comando.Parameters.Add(id_filial);
comando.Parameters.Add(id_sala);
comando.Parameters.Add(dt_inicio);
comando.Parameters.Add(dt_fim);
comando.Parameters.Add(id_responsavel);
comando.Parameters.Add(dv_cafe);
comando.Parameters.Add(qt_cafe);
comando.Parameters.Add(nm_descricao);
comando.Parameters.Add(id_reserva);
comando.Prepare();
comando.ExecuteNonQuery();
}
public Guid SqlCommandInsereReserva(SqlConnection conexao, Reserva reserva)
{
SqlCommand comando = new SqlCommand(null, conexao);
// Create and prepare an SQL statement.
comando.CommandText =
"INSERT INTO Reserva (id_filial, id_sala, dt_inicio, dt_fim, id_responsavel, dv_cafe, qt_cafe, nm_descricao) " +
"OUTPUT INSERTED.id_reserva " +
"VALUES (@id_filial, @id_sala, @dt_inicio, @dt_fim, @id_responsavel, @dv_cafe, @qt_cafe, @nm_descricao)";
SqlParameter id_filial = new SqlParameter("@id_filial", SqlDbType.UniqueIdentifier, 0);
SqlParameter id_sala = new SqlParameter("@id_sala", SqlDbType.UniqueIdentifier, 0);
SqlParameter dt_inicio = new SqlParameter("@dt_inicio", SqlDbType.DateTime, 0);
SqlParameter dt_fim = new SqlParameter("@dt_fim", SqlDbType.DateTime, 0);
SqlParameter id_responsavel = new SqlParameter("@id_responsavel", SqlDbType.UniqueIdentifier, 0);
SqlParameter dv_cafe = new SqlParameter("@dv_cafe", SqlDbType.Bit, 0);
SqlParameter qt_cafe = new SqlParameter("@qt_cafe", SqlDbType.SmallInt, 0);
SqlParameter nm_descricao = new SqlParameter("@nm_descricao", SqlDbType.Text, 200);
id_filial.Value = reserva.id_filial;
id_sala.Value = reserva.id_sala;
dt_inicio.Value = reserva.dt_inicio;
dt_fim.Value = reserva.dt_fim;
id_responsavel.Value = reserva.id_responsavel;
dv_cafe.Value = reserva.dv_cafe;
qt_cafe.Value = reserva.qt_cafe;
nm_descricao.Value = reserva.nm_descricao;
comando.Parameters.Add(id_filial);
comando.Parameters.Add(id_sala);
comando.Parameters.Add(dt_inicio);
comando.Parameters.Add(dt_fim);
comando.Parameters.Add(id_responsavel);
comando.Parameters.Add(dv_cafe);
comando.Parameters.Add(qt_cafe);
comando.Parameters.Add(nm_descricao);
Guid id_reserva = (Guid)comando.ExecuteScalar();
return(id_reserva);
}
public Retorno Reserva(Stream res)
{
try
{
StreamReader reader = new StreamReader(res);
string JSONdata = reader.ReadToEnd();
JavaScriptSerializer jss = new JavaScriptSerializer();
Reserva reserva = jss.Deserialize <Reserva>(JSONdata);
Retorno retorno = new Retorno();
if (reserva == null)
{
retorno.CodigoRetorno = 0;
retorno.DescricaoRetorno = "Falha ao deserializar";
}
else
{
Consulta conGetReservas = new Consulta();
List <Reserva> reservasintervalo = conGetReservas.GetReservasIntervalo(reserva.id_reserva, reserva.dt_inicio, reserva.dt_fim, reserva.id_sala, reserva.id_filial);
if (reservasintervalo.Count == 0)
{
CadastroBancoDados banco = new CadastroBancoDados();
SqlConnection conexao = banco.abrirConexao();
if (reserva.id_reserva.ToString().Equals("00000000-0000-0000-0000-000000000000"))
{
retorno.guid = banco.SqlCommandInsereReserva(conexao, reserva);
retorno.CodigoRetorno = 1;
retorno.DescricaoRetorno = "Reserva inserida";
}
else
{
banco.SqlCommandAtualizaReserva(conexao, reserva);
retorno.guid = reserva.id_reserva;
retorno.CodigoRetorno = 1;
retorno.DescricaoRetorno = "Reserva atualizada";
}
banco.fecharConexao(conexao);
}
else
{
Retorno retornoint = new Retorno();
retornoint.CodigoRetorno = 0;
retornoint.DescricaoRetorno = "Já existe reunião para o horário solicitado";
return(retornoint);
}
}
return(retorno);
}
catch (Exception error)
{
Retorno retorno = new Retorno();
retorno.CodigoRetorno = 0;
retorno.DescricaoRetorno = "Ocorreu o erro:" + error.Message;
return(retorno);
}
}
public List <Reserva> SqlCommandConsultaReservaIntervalo(SqlConnection conexao, Guid id_reserva, DateTime dt_inicio, DateTime dt_fim, Guid id_sala, Guid id_filial)
{
SqlDataReader reader = null;
List <Reserva> reservas = new List <Reserva>();
try
{
SqlCommand comando = new SqlCommand(null, conexao);
comando.CommandText = "SELECT * FROM Reserva " +
"WHERE (@dt_inicio between dt_inicio and dt_fim OR " +
"@dt_fim between dt_inicio and dt_fim OR " +
"(@dt_inicio < dt_inicio AND " +
"@dt_fim > dt_fim))";
SqlParameter parametro_inicio = new SqlParameter();
parametro_inicio.ParameterName = "@dt_inicio";
parametro_inicio.Value = dt_inicio;
comando.Parameters.Add(parametro_inicio);
SqlParameter parametro_fim = new SqlParameter();
parametro_fim.ParameterName = "@dt_fim";
parametro_fim.Value = dt_fim;
comando.Parameters.Add(parametro_fim);
comando = SqlAddParametro(conexao, comando, id_reserva, "id_reserva");
comando = SqlAddParametro(conexao, comando, id_sala, "id_sala");
comando = SqlAddParametro(conexao, comando, id_filial, "id_filial");
reader = comando.ExecuteReader();
while (reader.Read())
{
Reserva reserva = new Reserva();
reserva.id_reserva = reader.GetGuid(reader.GetOrdinal("id_reserva"));
reserva.id_filial = reader.GetGuid(reader.GetOrdinal("id_filial"));
reserva.id_sala = reader.GetGuid(reader.GetOrdinal("id_sala"));
reserva.dt_inicio = Convert.ToDateTime(reader["dt_inicio"].ToString());
reserva.dt_fim = Convert.ToDateTime(reader["dt_fim"].ToString());
reserva.id_responsavel = reader.GetGuid(reader.GetOrdinal("id_responsavel"));
reserva.dv_cafe = Convert.ToBoolean(reader["dv_cafe"].ToString());
reserva.qt_cafe = Convert.ToInt16(reader["qt_cafe"].ToString());
reserva.nm_descricao = reader["nm_descricao"].ToString();
reservas.Add(reserva);
}
return(reservas);
}
catch (Exception e)
{
Reserva reserva = new Reserva();
reserva.id_reserva = new Guid();
reserva.nm_descricao = "Ocorreu o erro:" + e.Message;
reservas.Add(reserva);
return(reservas);
}
finally
{
// close reader
if (reader != null)
{
reader.Close();
}
}
}
