CREATE PROCEDURE [dbo].[OnlineBookedHalls] @AStartDateTime datetime, @AEndDateTime Datetime, @ATableSetupNumberID INT, @APax INT, @AOnlineBookTypeNumber INT,  @ACountryNumber  INT =0, @AHotelNumber INT = -1
AS

--Oprettet d 1309 2007 AV. Proc'en returnerer et dataset med bookede lokaler i det givne tidsinterval
-- Last Changed 2008 0311 FirstName and LastNAme added
-- Last Changed 2007 1126 fix for empty dataset. hall open/close time ignored
--Last changed 2007 1002 hotelnumber: we check on  conferencerooms.hotelnumber.
--Last Changed 2007 1022 referenceNumber added to result set
--Last Changed 2007 1023 result set extended with all halls which meets the criterias

  DECLARE
  @HallNumberId INT,
  @HallNumber INT,
  @OnlineBookTypeNumberID INT, 
  @HallName VARCHAR(20),
  @TranslatedHallName VARCHAR(20),
  @StartTime VARCHAR(5),
  @EndTime VARCHAR(5),
  @ArrivalDate DATETIME,
  @DepartureDate DATETIME,
  @ReferenceNumber INT,
  @FirstName VARCHAR(50),
  @LastName VARCHAR(50)

   
  SET @StartTime = CONVERT(VARCHAR(5),@AStartDateTime,108)   
  SET @EndTime = CONVERT(VARCHAR(5),@AEndDateTime,108) 


  CREATE TABLE #BookedHalls (
		HallNumberId INT NOT NULL,
 		HallNumber INT NOT NULL,
 		HallName VARCHAR(20),
                           ArrivalDate DATETIME,
                           DepartureDate DATETIME,
                           ReferenceNumber INT,
                           FirstName VARCHAR(50),
                           LastName VARCHAR(50) 

		)
  SELECT @OnlineBookTypeNumberID = OnlineBookTypeNumber FROM OnlineBookType WITH (NOLOCK) WHERE Number = @AOnlineBookTypeNumber

--Get all halls first 
  DECLARE #Hallcur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber, ConferenceRooms.Number, ConferenceRooms.Name, Translation.Name AS TranslatedHallName
  FROM ConferenceRoomsXtab WITH (NOLOCK) INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  LEFT OUTER JOIN OnlineBookTypeHallXTab WITH (NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = ConferenceRooms.ConferenceRoomNumber
  LEFT OUTER JOIN Translation ON Translation.ObjectNumber = ConferenceRooms.ConferenceRoomNumber AND Translation.Type = 4 AND CountryNumber = @ACountryNumber
  WHERE 
   ConferenceRooms.AllowFilterPicassoOnline = 1  AND
   (OnlineBookTypeHallXTab.OnlineBookTypeNumber=  @OnlineBookTypeNumberID)
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ConferenceRooms.HotelNumber = @AHotelNumber ))
--  AND (CONVERT(VARCHAR(8),OpeningTime,108) ='00:00:00' OR (CONVERT(VARCHAR(5),OpeningTime,108) <=@StartTime  AND CONVERT(VARCHAR(5), CloseTime,108) >=@EndTime))
  AND   ((@AOnlineBookTypeNumber = 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID  AND DBO.OnlineHallTableSetupOK(ConferenceRooms.ConferenceRoomNumber, @ATableSetupNumberID, @APax) = 1) OR (@AOnlineBookTypeNumber > 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID))
  OPEN #Hallcur

  FETCH NEXT FROM #Hallcur
  INTO @HallNumberId, @HallNumber, @HallName, @TranslatedHallName

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF LTRIM(RTRIM(@TranslatedHallName)) IS NOT NULL
      SET @HallName = @TranslatedHallName
    INSERT INTO #BookedHalls (HallNumberID, HallNumber, HallName) VALUES (@HallNumberID,@HallNumber, @HallName)
    FETCH NEXT FROM #Hallcur INTO @HallNumberId, @HallNumber, @HallName, @TranslatedHallName
  END

  CLOSE #Hallcur
  DEALLOCATE #Hallcur

--Get the bookings 
  DECLARE #Rescur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber, ConferenceRooms.Number, ConferenceRooms.Name, Translation.Name AS TranslatedHallName,
  CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108),
  CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108),
  ReservationConferenceLine.ReferenceNumber, ReservationAddress.FirstName, ReservationAddress.LastName 
 FROM ReservationConferenceLine WITH (NOLOCK)  INNER JOIN RESERVATIONS WITH (NOLOCK) 
  ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenceNumber
  LEFT OUTER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  INNER JOIN CONFERENCEROOMSXTAB WITH (NOLOCK) 
  ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
  INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  LEFT OUTER JOIN OnlineBookTypeHallXTab WITH (NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = ReservationConferenceLine.ConferenceRoomNumber
  LEFT OUTER JOIN Translation ON Translation.ObjectNumber = ConferenceRooms.ConferenceRoomNumber AND Translation.Type = 4 AND CountryNumber = @ACountryNumber
  LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = ReservationAddressXTab.ReferenceNumber AND ISNULL(ReservationConferenceLine.GuestNo, 0) = ReservationAddressXTab.GuestNumber AND ReservationAddressXTab.AddressTypeNumber = 1
  LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
  WHERE 
  Reservations.GuestNumber = 0  AND 
  ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 AND Reservations.Status < 7
  AND ConferenceRooms.AllowFilterPicassoOnline = 1
  AND (OnlineBookTypeHallXTab.OnlineBookTypeNumber=  @OnlineBookTypeNumberID)
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ConferenceRooms.HotelNumber = @AHotelNumber ))
 -- AND (CONVERT(VARCHAR(8),OpeningTime,108) ='00:00:00' OR (CONVERT(VARCHAR(5),OpeningTime,108) <=@StartTime  AND CONVERT(VARCHAR(5), CloseTime,108) >=@EndTime))
  AND CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < DATEADD(MINUTE, ISNULL(ConferenceRooms.PauseInMinutes,0),@AEndDateTime )
  AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > DATEADD(MINUTE,- ISNULL(ConferenceRooms.PauseInMinutes,0),@AStartDateTime)
  AND   ((@AOnlineBookTypeNumber = 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID  AND DBO.OnlineHallTableSetupOK(ConferenceRooms.ConferenceRoomNumber, @ATableSetupNumberID, @APax) = 1) OR (@AOnlineBookTypeNumber > 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID))

  OPEN #Rescur

  FETCH NEXT FROM #Rescur
  INTO @HallNumberId, @HallNumber, @HallName, @TranslatedHallName, @ArrivalDate, @DepartureDate, @ReferenceNumber, @FirstName, @LastName

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF LTRIM(RTRIM(@TranslatedHallName)) IS NOT NULL
      SET @HallName = @TranslatedHallName
      INSERT INTO #BookedHalls (HallNumberID, HallNumber, HallName, ArrivalDate, DepartureDate, ReferenceNumber, FirstName, LastName) VALUES (@HallNumberID,@HallNumber, @HallName, @ArrivalDate, @DepartureDate, @ReferenceNumber, @FirstName, @LastName)
    FETCH NEXT FROM #Rescur INTO @HallNumberId, @HallNumber, @HallName, @TranslatedHallName, @ArrivalDate, @DepartureDate, @ReferenceNumber, @FirstName, @LastName
  END

  CLOSE #Rescur
  DEALLOCATE #Rescur

  SELECT HallNumberId, HallNumber, HallName, ArrivalDate, DepartureDate, ReferenceNumber, FirstName, LastName 
  FROM  #BookedHalls WITH (NOLOCK) 
  --ORDER BY HallNumber, ArrivalDate
  ORDER BY HallNumberID, ArrivalDate

  DROP TABLE #BookedHalls