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; }