public MessageResponse Excluir(long idEspaco)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
MessageResponse response = new MessageResponse();
command.CommandText = "delete from espacos where id = @id";
command.Parameters.AddWithValue("@id", idEspaco);
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
response.Success = false;
response.Message = $"Ocorreu um erro ao realizar a requisição solicitada. Erro: {ex.Message}";
return(response);
}
finally
{
connection.Dispose();
}
response.Success = true;
response.Message = "Excluído com sucesso.";
return(response);
}
public MessageResponse Cadastrar(SalasEvento se)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
MessageResponse response = new MessageResponse();
command.CommandText = "insert into salas (nome, lotacao) values (@nome, @lotacao)";
command.Parameters.AddWithValue("@nome", se.Nome);
command.Parameters.AddWithValue("@lotacao", se.Lotacao);
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
response.Success = false;
response.Message = $"Ocorreu um erro ao realizar a requisição solicitada. Erro: {ex.Message}";
return(response);
}
finally
{
connection.Dispose();
}
response.Success = true;
response.Message = "Cadastrado com sucesso.";
return(response);
}
public MessageResponse Cadastrar(EspacosCafe esp)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
MessageResponse response = new MessageResponse();
command.CommandText = "insert into espacos (lotacao, horainicial, horafinal) values (@lotacao, @horainicial, @horafinal)";
command.Parameters.AddWithValue("@lotacao", esp.Lotacao);
command.Parameters.AddWithValue("@horainicial", esp.HoraInicial);
command.Parameters.AddWithValue("@horafinal", esp.HoraFinal);
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
response.Success = false;
response.Message = $"Ocorreu um erro ao realizar a requisição solicitada. Erro: {ex.Message}";
return(response);
}
finally
{
connection.Dispose();
}
response.Success = true;
response.Message = "Cadastrado com sucesso.";
return(response);
}
private int TrazerNumeroOcupacoesSala(long idSala)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
int contador = 0;
SqlCommand command = new SqlCommand();
command.CommandText = @"select count(id) from ocupacoes where sala = @id";
command.Parameters.AddWithValue("@id", idSala);
command.Connection = connection;
try
{
connection.Open();
contador = Convert.ToInt32(command.ExecuteScalar());
}
catch
{
throw;
}
return contador;
}
public MessageResponse Atualizar(SalasEvento sala)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
MessageResponse response = new MessageResponse();
command.CommandText = "update salas set nome = @nome, lotacao = @lotacao where id = @id";
command.Parameters.AddWithValue("@id", sala.ID);
command.Parameters.AddWithValue("@nome", sala.Nome);
command.Parameters.AddWithValue("@lotacao", sala.Lotacao);
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
response.Success = false;
response.Message = $"Ocorreu um erro ao realizar a requisição solicitada. Erro: {ex.Message}";
return(response);
}
finally
{
connection.Dispose();
}
response.Success = true;
response.Message = "Excluído com sucesso.";
return(response);
}
public MessageResponse Cadastrar(Ocupacao ocupacao)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
MessageResponse response = new MessageResponse();
command.CommandText = "insert into ocupacoes (pessoa, nomepessoa, sala, espacocafe, horario) values (@pessoa, @nomepessoa, @sala, @espacocafe, @horario)";
command.Parameters.AddWithValue("@pessoa", ocupacao.Pessoa);
command.Parameters.AddWithValue("@nomepessoa", TrazerNomePessoaPorID(ocupacao.Pessoa));
command.Parameters.AddWithValue("@sala", ocupacao.Sala);
command.Parameters.AddWithValue("@espacocafe", ocupacao.EspacoCafe);
command.Parameters.AddWithValue("@horario", TrazerHorarioEspacoCafePorID(ocupacao.EspacoCafe));
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
response.Success = false;
response.Message = $"Ocorreu um erro ao realizar a requisição solicitada. Erro: {ex.Message}";
return response;
}
finally
{
connection.Dispose();
}
response.Success = true;
response.Message = "Cadastrado com sucesso.";
return response;
}
private string TrazerHorarioEspacoCafePorID(long idEspacoCafe)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
string horario = "";
command.CommandText = "select horainicial, horafinal from espacos where id = @id";
command.Parameters.AddWithValue("@id", idEspacoCafe);
command.Connection = connection;
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
horario = $"Das {Convert.ToDateTime(reader["HORAINICIAL"])} às {Convert.ToDateTime(reader["HORAFINAL"])}.";
}
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Dispose();
}
return horario;
}
private string TrazerNomePessoaPorID(long idPessoa)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
string nomePessoa = "";
command.CommandText = "select nome, sobrenome from pessoas where id = @id";
command.Parameters.AddWithValue("@id", idPessoa);
command.Connection = connection;
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
nomePessoa = $"{Convert.ToString(reader["NOME"])} {Convert.ToString(reader["SOBRENOME"])}";
}
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Dispose();
}
return nomePessoa;
}
public List <EspacosCafe> TrazerEspacosCafe()
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = @"select * from espacos";
command.Connection = connection;
List <EspacosCafe> espacos = new List <EspacosCafe>();
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["ID"]);
int lotacao = Convert.ToInt32(reader["LOTACAO"]);
DateTime horaInicial = Convert.ToDateTime(reader["HORAINICIAL"]);
DateTime horaFinal = Convert.ToDateTime(reader["HORAFINAL"]);
EspacosCafe es = new EspacosCafe(id, lotacao, horaInicial, horaFinal);
espacos.Add(es);
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return(espacos);
}
public List <Pessoa> TrazerPessoas()
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = "select * from pessoas";
command.Connection = connection;
List <Pessoa> pessoas = new List <Pessoa>();
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["ID"]);
string nome = Convert.ToString(reader["NOME"]);
string sobrenome = Convert.ToString(reader["SOBRENOME"]);
Pessoa ps = new Pessoa(id, nome, sobrenome);
pessoas.Add(ps);
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return(pessoas);
}
public List <SalasEvento> TrazerSalas()
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = @"select * from salas";
command.Connection = connection;
List <SalasEvento> salas = new List <SalasEvento>();
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["ID"]);
string nome = Convert.ToString(reader["NOME"]);
int lotacao = Convert.ToInt32(reader["LOTACAO"]);
salas.Add(new SalasEvento(id, nome, lotacao));
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return(salas);
}
public List <Pessoa> LerPorID(long id)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = "select * from pessoas where id = @id";
command.Parameters.AddWithValue("@id", id);
command.Connection = connection;
List <Pessoa> pessoas = new List <Pessoa>();
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
id = Convert.ToInt32(reader["ID"]);
string nome = Convert.ToString(reader["NOME"]);
string sobrenome = Convert.ToString(reader["SOBRENOME"]);
pessoas.Add(new Pessoa(id, nome, sobrenome));
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return(pessoas);
}
public MessageResponse Atualizar(Ocupacao ocupacao)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
MessageResponse response = new MessageResponse();
command.CommandText = "update ocupacoes set pessoa = @pessoa, nomepessoa = @nomepessoa, sala = @sala, espacocafe = @espacocafe, horario = @horario where id = @id";
command.Parameters.AddWithValue("@id", ocupacao.ID);
command.Parameters.AddWithValue("@pessoa", ocupacao.Pessoa);
command.Parameters.AddWithValue("@nomepessoa", ocupacao.NomePessoa);
command.Parameters.AddWithValue("@sala", ocupacao.Sala);
command.Parameters.AddWithValue("@espacocafe", ocupacao.EspacoCafe);
command.Parameters.AddWithValue("@horario", ocupacao.Horario);
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
response.Success = false;
response.Message = $"Ocorreu um erro ao realizar a requisição solicitada. Erro: {ex.Message}";
return response;
}
finally
{
connection.Dispose();
}
response.Success = true;
response.Message = "Atualizado com sucesso.";
return response;
}
public List <Ocupacao> TrazerEspacoCafeOcupacoes(long idEspacoCafe)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
List <Ocupacao> ocupacoes = new List <Ocupacao>();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = @"select * from ocupacoes where espacocafe = @espacocafe";
command.Parameters.AddWithValue("@espacocafe", idEspacoCafe);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["ID"]);
long pessoa = Convert.ToInt64(reader["PESSOA"]);
string nomePessoa = $"{Convert.ToString(reader["NOMEPESSOA"])}{Convert.ToString(reader["SOBRENOMEPESSOA"])}";
long espacoCafe = Convert.ToInt64(reader["ESPACOCAFE"]);
DateTime horaInicial = new DateTime(0, 0, 0, 0, 0, 0);
DateTime horaFinal = new DateTime(0, 0, 0, 0, 0, 0);
SqlConnection connection2 = new SqlConnection();
connection2.ConnectionString = connectionString;
SqlCommand command2 = new SqlCommand();
command2.CommandText = @"select horainicial, horafinal from espacos where id = @id";
command2.Parameters.AddWithValue("@id", idEspacoCafe);
try
{
connection2.Open();
SqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
horaInicial = Convert.ToDateTime(reader2["HORAINICIAL"]);
horaFinal = Convert.ToDateTime(reader2["HORAFINAL"]);
}
}
catch
{
throw;
}
string horario = (horaInicial.Minute == 0 && horaInicial.Hour == 0 && horaFinal.Minute == 0 && horaFinal.Hour == 0) ? "Sem horário cadastrado." : $"Das {horaInicial.ToString("HH:mm")} às {horaFinal.ToString("HH:mm")}";
ocupacoes.Add(new Ocupacao(id, pessoa, nomePessoa, espacoCafe, horario));
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return(ocupacoes);
}
/// <summary>
/// Se a sala cuja a ocupação está sendo cadastrada tiver 1 ocupação a mais que o resto, não permitir cadastrar
/// </summary>
/// <returns></returns>
public bool VerificarDiferencaOcupacoes(long idSala)
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = @"select id from salas where id not in (@id)";
command.Parameters.AddWithValue("@id", idSala);
command.Connection = connection;
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
long idOutraSala = Convert.ToInt64(reader["ID"]);
SqlConnection connection2 = new SqlConnection();
connection2.ConnectionString = connectionString;
SqlCommand command2 = new SqlCommand();
command2.CommandText = @"select count(id) from ocupacoes where sala = @id";
command2.Parameters.AddWithValue("@id", idOutraSala);
command2.Connection = connection2;
try
{
connection2.Open();
int outraSalaOcupacoes = Convert.ToInt32(command2.ExecuteScalar());
int salaAtualOcupacoes = TrazerNumeroOcupacoesSala(idSala);
if (salaAtualOcupacoes > outraSalaOcupacoes)
{
return false;
}
}
catch
{
throw;
}
finally
{
connection2.Dispose();
}
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return true;
}
public List<Ocupacao> TrazerOcupacoes()
{
string connectionString = Parametros.GetConnectionString();
SqlConnection connection = new SqlConnection();
SqlConnection connection2 = new SqlConnection();
connection.ConnectionString = connectionString;
SqlCommand command = new SqlCommand();
command.CommandText = @"select * from ocupacoes";
command.Connection = connection;
List<Ocupacao> ocupacoes = new List<Ocupacao>();
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["ID"]);
long pessoa = Convert.ToInt64(reader["PESSOA"]);
string nomePessoa = $"{Convert.ToString(reader["NOMEPESSOA"])}";
long espacoCafe = Convert.ToInt64(reader["ESPACOCAFE"]);
string horario = "Sem horário cadastrado.";
try
{
connection2.ConnectionString = connectionString;
SqlCommand command2 = new SqlCommand();
command2.CommandText = @"select horainicial, horafinal from espacos";
command2.Connection = connection2;
connection2.Open();
SqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
DateTime horaInicial = Convert.ToDateTime(reader2["HORAINICIAL"]);
DateTime horaFinal = Convert.ToDateTime(reader2["HORAFINAL"]);
horario = $"Das {horaInicial.ToString("HH:mm")} às {horaFinal.ToString("HH:mm")}";
}
}
catch
{
throw;
}
finally
{
connection2.Dispose();
}
ocupacoes.Add(new Ocupacao(id, pessoa, nomePessoa, espacoCafe, horario));
}
}
catch
{
throw;
}
finally
{
connection.Dispose();
}
return ocupacoes;
}