
CREATE PROCEDURE [dbo].[OnlineAvailableWares] 
  @AHallTypeNumber INT
  ,@AStartDateTime datetime
  ,@APax INT
  ,@AMinutesBeforeScheduleIsClosing INT = 0
  ,@ATripDuration INT = 210 -- default: 3 1/2 hours.
  ,@minutesToEarliestBooking INT = 0
AS
-- Returns available times for GoHigh and simplified table booking (nothing to do with wares!)
-- Calls: GetSchedulerExpressData

-- 20150107/SG: Use new Hall.OnlineBookingTimeInterval as BookingTimeInterval when pr. hall value is needed
-- 20140610/SG: Added  WITH (NOLOCK) in ooo-section. I looks like it helped the locking problems
-- 20140606/SG: Added a few "NOLOCK"s (not that is solved anything)
-- 20140207/SG: In addition to GoHigh also used for simplyfied tablebooking. Allow RESERVATIONCONFERENCELINE.ConferenceRoomNumber beeing NULL 
-- 20131008/SG: Out of order, missing hallNumber
-- 20131002/SG: CR 064685: Exclude Out Of Order periods. Error in counting PAX.
-- 20130902/SG: MS SQL 2005 kender ikke SysDateTime(), bruger GetDate() i stedet
-- 20130819/SG: Parameter @minutesToEarliestBooking added
-- 20120626/SG:  Added AND cr.AllowFilterPicassoOnline = 1 
-- 20130405/LJE: Finding availability, several lines for same time may occur - accumulate!
-- 20120824/LJE: Only update TempTable with matching HallNumber = ConferenceRoomNumber.
-- 20120817/LJE: Multiple HallNumber/ConferenceRoomNumbers may be found. Show cr.Name as HallName (not CONFERENCEROOMTYPES.Text).
-- 20110726/TB : Added Check on cancelled Reservation.
-- 20110518/LJE: By accident TempCursor was deallocated outside "IF @Found = 1" scope. This gave an error if @Found = 0.
-- 20110502/LJE: Parameter: @ATripDuration is added.
-- 20110426/LJE: Parameter: @AMinutesBeforeScheduleIsClosing is added.
-- 20110419/LJE: Only return available rows 3 hours or more from closing time.
-- 20110404/LJE: @AHallTypeNumber inserted as reference when finding @HallName.
-- 20110331/LJE: Columns added: ArrivalDateTime,DepartureDateTime,HallName,HallNumber. - and: drop table #TempTable.
-- 20110329/RN: Initial version.

BEGIN
  DECLARE @Found INT
  DECLARE @TotalNumberOfPax INT
  DECLARE @AOpeningDateTime datetime
  DECLARE @ACloseDatetime datetime
  DECLARE @TimeInterval INT
  DECLARE @TempDateTime datetime
  DECLARE @TimeIntervalFromHall INT; 
  DECLARE @PaxTotal INT
  DECLARE @ConferenceRoomNumber INT 
  DECLARE @ArrivalTime CHAR(5)
  DECLARE @ArrivalDate CHAR(8)
  DECLARE @HallName VARCHAR(50)
  DECLARE @ArrivalDateTime datetime
  DECLARE @DepartureDateTime datetime
  DECLARE @HallNumber INT
  DECLARE @ConferenceRoomName VARCHAR(50)  -- "Bane 1"
  DECLARE @FirstBookingTime dateTime;
  SET @FirstBookingTime = DateAdd(minute, @minutesToEarliestBooking, GetDate());
  
  CREATE TABLE #TempTable (
    PAX INT,
    AvailPax INT,
    MaxPax INT,
    ArrivalTime CHAR(5),
    ArrivalDate CHAR(8),
    ArrivalDateTime datetime,
    DepartureDateTime datetime,
    HallName VARCHAR(50),
    HallNumber INT  -- 22, 24, 26, 28 , ...
  )
  
  EXEC dbo.GetSchedulerExpressData @AStartDateTime
    , @AHallTypeNumber
	, @Found OUTPUT
	, @TotalNumberOfPax OUTPUT
	, @AOpeningDateTime OUTPUT
	, @ACloseDateTime OUTPUT
	
  IF @Found = 1
  BEGIN
    SET @TempDateTime = @AOpeningDateTime
    -- Only one hall per HallType is allowed
	SELECT @HallName = Name, @TimeIntervalFromHall = OnlineBookingTimeInterval 
	  FROM ConferenceRooms WITH (NOLOCK) 
	  WHERE AllowFilterPicassoOnline = 1 AND ConferenceRoomTypeNumber = @AHallTypeNumber;
	IF ISNULL(@TimeIntervalFromHall, 0) = 0
	BEGIN
	  SELECT @TimeInterval = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OnlineBookingTimeInterval' 
	  IF ISNULL(@TimeInterval, 0) = 0
      BEGIN
        SET @TimeInterval = 30; --default
      END
    END ELSE
    BEGIN
	  SET @TimeInterval = @TimeIntervalFromHall
    END   
    WHILE (@TempDateTime <= @ACloseDateTime)
    BEGIN
      IF @TempDateTime >= @FirstBookingTime
      BEGIN
		 SELECT @ArrivalDateTime = CONVERT(datetime,CONVERT(CHAR(8),@AOpeningDateTime, 112) 
		   + ' ' + CONVERT(CHAR(5),@TempDateTime,108))
		   
		 --SELECT @DepartureDateTime = DATEADD(minute,@TimeInterval,@ArrivalDateTime)
		 SELECT @DepartureDateTime = DATEADD(minute,@ATripDuration,@ArrivalDateTime) -- I.e. Dep.time is 3.5 hours/210 min from arr.time.
		
		   DECLARE #ConferenceRoomCursor CURSOR FAST_FORWARD READ_ONLY FOR 
			 SELECT cr.ConferenceRoomNumber,cr.[Name]
			 FROM ConferenceRoomTypes crt,ConferenceRooms cr WITH (NOLOCK)
			 WHERE crt.ConferenceRoomTypeNumber = cr.ConferenceRoomTypeNumber 
			   AND crt.ConferenceRoomTypeNumber = @AHallTypeNumber
			   AND cr.Istable is NULL --22,24,26,28
				   AND cr.AllowFilterPicassoOnline = 1 
		   OPEN #ConferenceRoomCursor
		   FETCH NEXT FROM #ConferenceRoomCursor
		   INTO @HallNumber,@ConferenceRoomName
		   WHILE (@@FETCH_STATUS = 0)
		   BEGIN
				 INSERT INTO #TempTable (
					PAX, AvailPax , MaxPax, ArrivalTime, ArrivalDate
				   , ArrivalDateTime,DepartureDateTime, HallName, HallNumber
				  ) VALUES (
					0, @TotalNumberOfPax, @TotalNumberOfPax, CONVERT(CHAR(5),@TempDateTime,108)
					, CONVERT(CHAR(8),@AOpeningDateTime, 112)
					,@ArrivalDateTime 
					,@DepartureDateTime
					,@ConferenceRoomName--@HallName
					,@HallNumber
				  )
			   FETCH NEXT FROM #ConferenceRoomCursor
			   INTO @HallNumber,@ConferenceRoomName
		  END
		  CLOSE #ConferenceRoomCursor
		  DEALLOCATE #ConferenceRoomCursor
		END
      SET @TempDateTime = DATEADD(minute, @TimeInterval, @TempDateTime)  
    END  
    
    DECLARE #TempCursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT 
      SUM(ISNULL(ReservationConferenceLine.Adults, 0)) +
      SUM(ISNULL(RESERVATIONCONFERENCELINE.Children1, 0))+
      SUM(ISNULL(RESERVATIONCONFERENCELINE.Children2, 0)) as Pax,
      RESERVATIONCONFERENCELINE.ArrivalTime, 
      RESERVATIONCONFERENCELINE.ArrivalDate,
      RESERVATIONCONFERENCELINE.ConferenceRoomNumber
    FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.Referencenumber = ReservationConferenceLine.Referencenumber -- TB20110726
    WHERE RESERVATIONCONFERENCELINE.ArrivalDate = CONVERT(char(8), @AOpeningDateTime, 112)
      AND RESERVATIONCONFERENCELINE.ArrivalTime >= CONVERT(char(5), @AOpeningDateTime, 108)
      AND RESERVATIONCONFERENCELINE.ArrivalTime <= CONVERT(char(5), @ACloseDatetime, 108)
      AND ReservationConferenceLine.BookedConferenceRoomTypeNumber = @AHallTypeNumber
      AND GuestNumber = 0 -- TB20110726
      AND Status < 7  -- TB20110726
    GROUP BY RESERVATIONCONFERENCELINE.ArrivalTime, RESERVATIONCONFERENCELINE.ArrivalDate
    ,RESERVATIONCONFERENCELINE.ConferenceRoomNumber
    
    OPEN #TempCursor
    FETCH NEXT FROM #TempCursor
    INTO @PaxTotal, @ArrivalTime, @ArrivalDate
      ,@ConferenceRoomNumber 
      
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      --Her opdateres PAX, når tiderne passer præcis sammen. Ændres opsætningen, så tiderne bliver forskudt, ramler det.
      UPDATE #TempTable
      SET 
        AvailPax = (@TotalNumberOfPax - (PAX + @PaxTotal)),
        PAX = PAX + @PaxTotal,
        MaxPax = @TotalNumberOfPax
      WHERE ArrivalTime = @ArrivalTime AND ArrivalDate = @ArrivalDate      
        AND (HallNumber = @ConferenceRoomNumber  or ISNULL(@ConferenceRoomNumber, 0) = 0) --tablebooking: @ConferenceRoomNumber er null
      
      FETCH NEXT FROM #TempCursor
      INTO @PaxTotal, @ArrivalTime, @ArrivalDate
        ,@ConferenceRoomNumber 
    END

    CLOSE #TempCursor
    DEALLOCATE #TempCursor

    --Out of order: RESERVATIONCONFERENCELINE with ReferenceNumber=0
    DECLARE @OooArr DateTime;
    DECLARE @OooDep DateTime;
    DECLARE @OooHallNo Int;
    --OOO-perioder
    DECLARE #TempCursor CURSOR FAST_FORWARD READ_ONLY FOR
      SELECT CAST(ArrivalDate + ' ' + ArrivalTime AS DateTime) AS OooArr, 
             CAST(DepartureDate + ' ' + DepartureTime AS DateTime) AS OooDep,
             ConferenceRoomNumber
      FROM RESERVATIONCONFERENCELINE  WITH (NOLOCK)
      WHERE ReferenceNumber = 0 
    OPEN #TempCursor
    FETCH NEXT FROM #TempCursor INTO @OooArr, @OooDep, @OooHallNo
    --
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      DELETE FROM #TempTable
      WHERE ((ArrivalDateTime BETWEEN @OooArr AND @OooDep) OR
            (DepartureDateTime BETWEEN @OooArr AND @OooDep)) AND
            HallNumber = @OooHallNo

      FETCH NEXT FROM #TempCursor INTO @OooArr, @OooDep, @OooHallNo
    END   
    CLOSE #TempCursor
    DEALLOCATE #TempCursor 
    
    
END
  
  SELECT * FROM #TempTable WITH (NOLOCK) 
  WHERE AvailPax >= @APax 
    AND ArrivalDateTime <= DATEADD(minute,-@AMinutesBeforeScheduleIsClosing,@ACloseDatetime) --@LastArrivalDateTime)
  
  DROP TABLE #TempTable  
  
END
