/* RAW SQL QUERY WORKING PROTOTYPE.
* SELECT Room.ID, Level, BedCount, Booking.CheckIn, Booking.CheckOut, Booking.CustomerEmail
* FROM Room
* LEFT JOIN Booking
* ON Room.ID = Booking.RoomID
* WHERE Room.ID NOT IN (
* SELECT Room.ID
* FROM Room
* LEFT JOIN Booking
* ON Room.ID = Booking.RoomID
* WHERE (date('2019-09-12 00:00:00') >= date(checkIn) and date('2019-09-12 00:00:00') <= date(checkOut)) or (date('2019-09-15 00:00:00') <= date(checkOut) and date('2019-09-15 00:00:00') >= date(CheckIn))
* )
*/
public async Task <IActionResult> SearchRooms([Bind("BedCount", "CheckIn", "CheckOut")] RoomSearch searchParameters)//
{
if (searchParameters.CheckIn.DaysOfStay(searchParameters.CheckOut) < 1)
{
return(View(new Models.RoomSearch()));
}
string query = "SELECT Room.ID, Level, BedCount, Booking.CheckIn, Booking.CheckOut, Booking.CustomerEmail, Room.Price " +
"FROM Room LEFT JOIN Booking ON Room.ID = Booking.RoomID " +
"WHERE BedCount = @BEDSNEEDED AND " +
"Room.ID NOT IN( " +
"SELECT Room.ID " +
"FROM Room " +
"LEFT JOIN Booking " +
"ON Room.ID = Booking.RoomID " +
"WHERE (date(@INCOMINGSTART) >= date(checkIn) and date(@INCOMINGSTART) <= date(checkOut)) or (date(@INCOMINGEND) <= date(checkOut) and date(@INCOMINGEND) >= date(CheckIn)))";
var occupationStart = new SqliteParameter("INCOMINGSTART", searchParameters.CheckIn);
var occupationEnd = new SqliteParameter("INCOMINGEND", searchParameters.CheckOut);
var bedsNeeded = new SqliteParameter("BEDSNEEDED", searchParameters.BedCount);
var freeRooms = await _context.Room.FromSql(query, occupationStart, occupationEnd, bedsNeeded).ToListAsync();
var newSearch = new Models.RoomSearch
{
AvailableRooms = freeRooms
};
return(View("~/Views/SearchRooms/SearchRooms.cshtml", newSearch));
}
public IActionResult Search()
{
var newSearch = new Models.RoomSearch();
return(View("~/Views/SearchRooms/SearchRooms.cshtml", newSearch));
}
