public Regimen getRegimenByIdReserva(Reserva reserva)
{
Regimen regimen = null;
String connectionString = ConfigurationManager.AppSettings["BaseLocal"];
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand();
SqlDataReader reader;
sqlCommand.Parameters.AddWithValue("@idReserva", reserva.getIdReserva());
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT idRegimen FROM LOS_BORBOTONES.Reserva WHERE idReserva = @idReserva";
sqlConnection.Open();
reader = sqlCommand.ExecuteReader();
if (reader.Read())
{
int idRegimen = reader.GetInt32(reader.GetOrdinal("idRegimen"));
RepositorioRegimen repoRegimen = new RepositorioRegimen();
regimen = repoRegimen.getById(idRegimen);
}
sqlConnection.Close();
return(regimen);
}
public Reserva getIdByIdEstadia(int idEstadia)
{
Reserva reserva = null;
RepositorioRegimen repoRegimen = new RepositorioRegimen();
RepositorioHotel repoHotel = new RepositorioHotel();
RepositorioEstadia repoEstadia = new RepositorioEstadia();
RepositorioCliente repoClientes = new RepositorioCliente();
RepositorioEstadoReserva repoEstadoReserva = new RepositorioEstadoReserva();
String connectionString = ConfigurationManager.AppSettings["BaseLocal"];
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand();
SqlDataReader reader;
sqlCommand.Parameters.AddWithValue("@idEstadia", idEstadia);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM LOS_BORBOTONES.Reserva WHERE idEstadia = @idEstadia";
sqlConnection.Open();
reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
int idReserva = reader.GetInt32(reader.GetOrdinal("idReserva"));
decimal codigoReserva = reader.GetDecimal(reader.GetOrdinal("CodigoReserva"));
decimal diasAlojados = reader.GetDecimal(reader.GetOrdinal("DiasAlojados"));
DateTime fechaDesde = reader.SafeGetDateTime(reader.GetOrdinal("FechaDesde"));
DateTime fechaHasta = reader.SafeGetDateTime(reader.GetOrdinal("FechaHasta"));
DateTime fechaCreacion = reader.SafeGetDateTime(reader.GetOrdinal("FechaCreacion"));
Hotel hotel = repoHotel.getById(reader.GetInt32(reader.GetOrdinal("idHotel")));;
Regimen regimen = repoRegimen.getById(reader.GetInt32(reader.GetOrdinal("idRegimen")));
Estadia estadia = repoEstadia.getById(reader.GetInt32(reader.GetOrdinal("idEstadia")));
Cliente cliente = repoClientes.getById(reader.GetInt32(reader.GetOrdinal("idCliente")));
EstadoReserva estado = repoEstadoReserva.getByIdReserva(idReserva);
reserva = new Reserva(idReserva, hotel, estadia, regimen, cliente, codigoReserva, diasAlojados, fechaCreacion, fechaDesde, fechaHasta, estado);
}
sqlConnection.Close();
return(reserva);
}
public List <HabitacionDisponible> getHabitacionesDisponibles(DateTime fechaInicio, DateTime fechaFin, Hotel hotel, TipoHabitacion tipoHabitacion, Regimen regimen, Reserva reserva)
{
List <HabitacionDisponible> habitacionesDisponibles = new List <HabitacionDisponible>();
RepositorioRegimen repoRegimen = new RepositorioRegimen();
String connectionString = ConfigurationManager.AppSettings["BaseLocal"];
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand();
SqlDataReader reader;
RepositorioReserva repoReserva = new RepositorioReserva();
repoReserva.cancelarReservasNoShow(hotel);
sqlCommand.Parameters.AddWithValue("@fechaInicio", fechaInicio);
sqlCommand.Parameters.AddWithValue("@fechaFin", fechaFin);
sqlCommand.Parameters.AddWithValue("@idHotel", hotel.getIdHotel());
String queryTipoHab = "";
if (tipoHabitacion != null)
{
sqlCommand.Parameters.AddWithValue("@idtipoHabitacion", tipoHabitacion.getIdTipoHabitacion());
queryTipoHab = "AND HAB.idTipoHabitacion=@idtipoHabitacion ";
}
String queryModificarReservaParaTraerLaHabitacionQueYaReserve = reserva == null ? "" : "AND RES.idReserva!=" + reserva.getIdReserva();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText =
"SELECT HAB.idHabitacion,HAB.Activa,HAB.Numero,HAB.Piso,HAB.Ubicacion,Hab.Descripcion,HAB.idHotel,HAB.idTipoHabitacion,REG.idRegimen FROM LOS_BORBOTONES.Habitacion AS HAB " +
"JOIN LOS_BORBOTONES.Hotel AS HOT ON HOT.idHotel=HAB.idHotel " +
"JOIN LOS_BORBOTONES.Regimen_X_Hotel AS RXH ON RXH.idHotel=HOT.idHotel " +
"JOIN LOS_BORBOTONES.Regimen AS REG ON REG.idRegimen = RXH.idRegimen " +
"JOIN LOS_BORBOTONES.TipoHabitacion AS TIP ON TIP.idTipoHabitacion=HAB.idTipoHabitacion " +
"WHERE REG.Activo=1 " +
"AND HAB.idHotel=@idHotel " +
"AND HAB.Activa=1 " +
queryTipoHab +
"AND NOT EXISTS ( " +
"SELECT * FROM LOS_BORBOTONES.Reserva_X_Habitacion_X_Cliente AS RXHXC " +
"JOIN LOS_BORBOTONES.Reserva AS RES ON RES.idReserva = RXHXC.idReserva " +
"WHERE HAB.idHabitacion= RXHXC.idHabitacion " +
queryModificarReservaParaTraerLaHabitacionQueYaReserve +
"AND (RES.FechaDesde < @fechaFin AND @fechaInicio < RES.FechaHasta ) " +
"AND NOT EXISTS( " +
"SELECT * FROM LOS_BORBOTONES.EstadoReserva AS ESRE " +
"WHERE RES.idReserva = ESRE.idReserva " +
"AND ESRE.TipoEstado IN ('RCR','RCC','RCNS') " +
") " +
") " +
"AND NOT EXISTS (SELECT * FROM LOS_BORBOTONES.CierreTemporal AS CIE WHERE CIE.idHotel= HOT.idHotel AND CIE.FechaInicio < @fechaFin AND @fechaInicio < CIE.FechaFin) ";
if (regimen != null)
{
sqlCommand.Parameters.AddWithValue("@idRegimen", regimen.getIdRegimen());
sqlCommand.CommandText += " AND REG.idRegimen=@idRegimen ";
}
sqlCommand.CommandText += " GROUP BY HAB.idHabitacion, REG.idRegimen, HAB.Activa,HAB.Numero,HAB.Piso,HAB.Ubicacion,HAB.idHotel,HAB.idTipoHabitacion,HAB.Descripcion " +
" ORDER BY HAB.idHabitacion;";
sqlConnection.Open();
reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
int qidHabitacion = reader.GetInt32(reader.GetOrdinal("idHabitacion"));
int qidTipoHabitacion = reader.GetInt32(reader.GetOrdinal("idTipoHabitacion"));
bool qactiva = reader.GetBoolean(reader.GetOrdinal("Activa"));
int qnumero = reader.GetInt32(reader.GetOrdinal("Numero"));
int qpiso = reader.GetInt32(reader.GetOrdinal("Piso"));
int qidRegimen = reader.GetInt32(reader.GetOrdinal("idRegimen"));
String qubicacion = reader.GetString(reader.GetOrdinal("Ubicacion"));
String qdescripcion = reader.SafeGetString(reader.GetOrdinal("Descripcion"));
Regimen qregimen = repoRegimen.getById(qidRegimen);
Habitacion qhabitacion = new Habitacion(qidHabitacion, qactiva, qnumero, qpiso, qubicacion, qdescripcion);
habitacionesDisponibles.Add(new HabitacionDisponible(qhabitacion, qregimen));
}
//Cierro Primera Consulta
sqlConnection.Close();
return(habitacionesDisponibles);
}
