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);
        }
Exemple #2
0
        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);
        }
Exemple #4
0
        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;
        }
Exemple #5
0
        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);
        }
Exemple #6
0
        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;
        }
Exemple #7
0
        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;
        }
Exemple #8
0
        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);
        }
Exemple #10
0
        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);
        }
Exemple #11
0
        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);
        }
Exemple #12
0
        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);
        }
Exemple #13
0
        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);
        }
Exemple #15
0
        /// <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;
        }
Exemple #16
0
        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;
        }