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