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