/*
* public static List<Room> GetRoomByHotel(int idHotel)
* {
* List<Room> results = null;
*
* string connectionString = ConfigurationManager.ConnectionStrings["DatabaseDataAccess"].ConnectionString;
*
* try
* {
* using (SqlConnection cn = new SqlConnection(connectionString))
* {
* string query = @"SELECT * FROM Room WHERE IdHotel = @idHotel";
*
* SqlCommand cmd = new SqlCommand(query, cn);
* cmd.Parameters.AddWithValue("@idHotel", idHotel);
* cn.Open();
*
* using (SqlDataReader dr = cmd.ExecuteReader())
* {
* while (dr.Read())
* {
* if (results == null)
* results = new List<Room>();
*
* Room room = new Room();
*
* room.IdRoom = (int)dr["IdRoom"];
*
* room.Number = (int)dr["Number"];
*
* room.Description = (string)dr["Description"];
*
* room.Type = (int)dr["Type"];
*
* room.Price = (decimal)dr["Price"];
*
* room.HasTV = (bool)dr["HasTV"];
*
* room.HasHairDryer = (bool)dr["HasHairDryer"];
*
* room.Hotel = null;
*
* results.Add(room);
* }
* }
* }
* }
* catch (Exception e)
* {
* throw e;
* }
*
* return results;
* }
*/
public static List <Room> GetRoomsOfReservation(int reservationId)
{
List <Room> results = null;
string connectionString = ConfigurationManager.ConnectionStrings["DatabaseDataAccess"].ConnectionString;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = @"SELECT ro.* FROM Room ro
LEFT JOIN RoomReservation rr ON rr.IdRoom = ro.IdRoom
WHERE rr.IdReservation = @reservationId";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@reservationId", reservationId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (results == null)
{
results = new List <Room>();
}
Room room = new Room();
room.IdRoom = (int)dr["IdRoom"];
room.Number = (int)dr["Number"];
room.Description = (string)dr["Description"];
room.Type = (int)dr["Type"];
room.Price = (decimal)dr["Price"];
room.HasTV = (bool)dr["HasTV"];
room.HasHairDryer = (bool)dr["HasHairDryer"];
room.Hotel = HotelDB.GetHotel((int)dr["IdHotel"]);
results.Add(room);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(results);
}
public static List <Room> GetRoomsByDateHotelAndNbPerson(DateTime checkin, DateTime checkout, int idHotel, int nbPerson)
{
List <Room> rooms = new List <Room>();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "SELECT R.* FROM ROOM R " +
"LEFT OUTER JOIN Booking_has_Room BR ON R.IdRoom = BR.IdRoom " +
"LEFT OUTER JOIN BOOKING B ON B.IdBooking = BR.IdBooking " +
"INNER JOIN HOTEL H ON R.idHotel = H.idHotel " +
"WHERE @idHotel = H.IdHotel " +
"AND(B.checkin IS NULL " +
"OR(@checkin < B.BeginDate AND @checkout <= B.checkin) " +
"OR(@checkin >= B.checkout AND @checkout > B.checkout)) " +
"ORDER BY R.Type DESC; ";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@idHotel", idHotel);
cmd.Parameters.AddWithValue("@checkin", checkin);
cmd.Parameters.AddWithValue("@checkout", checkout);
cmd.Parameters.AddWithValue("@nbPerson", nbPerson);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Room room = new Room();
room.IdRoom = (int)dr["IdRoom"];
room.Number = (int)dr["Number"];
room.Description = (string)dr["Description"];
room.Type = (int)dr["Type"];
room.HasTV = (bool)dr["HasTV"];
room.HasHairDryer = (bool)dr["HasHairDryer"];
room.IdHotel = HotelDB.GetHotelfromId((int)dr["IdHotel"]);
room.Pictures = PictureDB.GetPicturesByIdRoom((int)dr["IdRoom"]);
room.Price = (decimal)dr["Price"];
rooms.Add(room);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(rooms);
}
public static List <Room> GetRoomAvalaibleLocation(DateTime CheckIN, DateTime CheckOUT, String Location)
{
List <Room> results = new List <Room>();
List <Picture> pictures = new List <Picture>();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query =
"SELECT R.* FROM ROOM R " +
"LEFT OUTER JOIN Booking_has_Room BR ON R.IdRoom = BR.IdRoom " +
"LEFT OUTER JOIN BOOKING B ON B.IdBooking = BR.IdBooking " +
"INNER JOIN HOTEL H ON R.idHotel = H.idHotel " +
"WHERE @Location = H.Location " +
"AND (B.Checkin IS NULL " +
"OR(@CheckIN < B.Checkin AND @CheckOUT <= B.Checkin) " +
"OR(@CheckIN >= B.Checkout AND @CheckOUT > B.Checkout));";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@Location", Location);
cmd.Parameters.AddWithValue("@CheckIN", CheckIN);
cmd.Parameters.AddWithValue("@CheckOUT", CheckOUT);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Room room = new Room();
room.IdRoom = (int)dr["IdRoom"];
room.Number = (int)dr["Number"];
room.Description = (string)dr["Description"];
room.Type = (int)dr["Type"];
room.Price = (decimal)dr["Price"];
room.HasTV = (bool)dr["HasTV"];
room.HasHairDryer = (bool)dr["HasHairDryer"];
room.IdHotel = HotelDB.GetHotelById((int)dr["IdHotel"]);
room.Pictures = PictureDB.GetPicturesByIdRoom((int)dr["IdRoom"]);
results.Add(room);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(results);
}
public static List <Room> GetRoomForHotel(int IdHotel)
{
List <Room> results = null;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "select * from room where IdHotel = @IdHotel";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@IdHotel", IdHotel);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (results == null)
{
results = new List <Room>();
}
Room room = new Room();
room.IdRoom = (int)dr["IdRoom"];
room.Number = (int)dr["Number"];
room.Description = (string)dr["Description"];
room.Type = (int)dr["Type"];
room.Price = (decimal)dr["Price"];
room.HasTV = (bool)dr["HasTV"];
room.HasHairDryer = (bool)dr["HasHairDryer"];
room.IdHotel = HotelDB.GetHotelById((int)dr["IdHotel"]);
room.Pictures = PictureDB.GetPicturesByIdRoom((int)dr["IdRoom"]);
results.Add(room);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(results);
}
public static Room GetRoom(int idRoom)
{
Room result = new Room();
try
{
string connectionString = ConfigurationManager.ConnectionStrings["DatabaseDataAccess"].ConnectionString;
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "SELECT * FROM Room WHERE IdRoom = @idRoom";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@idRoom", idRoom);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
result.IdRoom = (int)dr["IdRoom"];
result.Number = (int)dr["Number"];
result.Description = (string)dr["Description"];
result.Type = (int)dr["Type"];
result.Price = (decimal)dr["Price"];
result.HasTV = (bool)dr["HasTV"];
result.HasHairDryer = (bool)dr["HasHairDryer"];
result.Hotel = HotelDB.GetHotel((int)dr["IdHotel"]);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(result);
}
public static List <Room> GetRoomsByIdBooking(int IdBooking)
{
List <Room> rooms = new List <Room>();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "SELECT * FROM Room R " +
"INNER JOIN Booking_has_Room BR ON BR.IdRoom = R.IdRoom " +
"WHERE IdBooking = @IdBooking; ";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@IdBooking", IdBooking);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Room room = new Room();
room.IdRoom = (int)dr["IdRoom"];
room.Number = (int)dr["Number"];
room.Description = (string)dr["Description"];
room.Type = (int)dr["Type"];
room.Price = (decimal)dr["Price"];
room.HasTV = (bool)dr["HasTV"];
room.HasHairDryer = (bool)dr["HasHairDryer"];
room.IdHotel = HotelDB.GetHotelById((int)dr["IdHotel"]);
room.Pictures = PictureDB.GetPicturesByIdRoom((int)dr["IdRoom"]);
rooms.Add(room);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(rooms);
}
public static List <Room> GetRoomsWithNoReservationByHotel(int idHotel, DateTime arrival, DateTime departure)
{
List <Room> results = null;
string connectionString = ConfigurationManager.ConnectionStrings["DatabaseDataAccess"].ConnectionString;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = @"SELECT ro.*, re.* FROM Room ro
FULL JOIN RoomReservation rr ON ro.IdRoom = rr.IdRoom
LEFT JOIN Reservation re ON re.IdReservation = rr.IdReservation
WHERE
ro.IdHotel = @idHotel
AND (
NOT(
re.Arrival BETWEEN @arrival AND @departure
OR re.Departure BETWEEN @arrival AND @departure
OR(re.Arrival <= @arrival AND re.Departure >= @departure)
)
OR re.Arrival IS NULL
)"
;
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@idHotel", idHotel);
cmd.Parameters.AddWithValue("@arrival", arrival.ToString("yyyy-MM-dd HH:mm:ss.fff"));
cmd.Parameters.AddWithValue("@departure", departure.ToString("yyyy-MM-dd HH:mm:ss.fff"));
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (results == null)
{
results = new List <Room>();
}
Room room = new Room();
room.IdRoom = (int)dr["IdRoom"];
room.Number = (int)dr["Number"];
room.Description = (string)dr["Description"];
room.Type = (int)dr["Type"];
room.Price = (decimal)dr["Price"];
room.HasTV = (bool)dr["HasTV"];
room.HasHairDryer = (bool)dr["HasHairDryer"];
room.Hotel = HotelDB.GetHotel((int)dr["IdHotel"]);
results.Add(room);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(results);
}
