CREATE PROCEDURE [dbo].[OnlineAvailableHalls] @AStartDateTime datetime
  , @AEndDateTime Datetime
  , @ATableSetupNumberID INT
  , @APax INT
  , @AOnlineBookTypeNumber INT
  , @ACountryNumber  INT =0
  , @AHotelNumber INT = -1
  , @ASearchHallNumber INT = 0
  , @ACurrentReservationConfLineNumber int = 0 --editing an exsisting res.
  , @OrderBy VarChar(15) = 'MaxPax'
AS
--Oprettet d 1406 2006 AV. Proc'en returnerer et dataset med ledige lokaler i det givne tidsinterval

--SG/2015-10-20: HallName length 20 -> 50 (as in table)
--SG/2015-09-30: Subtypes corrected.
--SG/2015-04-16: Editing: Added param @OrderBy
--SG/2015-04-16: Editing: Added param @ACurrentReservationConfLineNumber (RoomLineNumber)
--SG/2015-01-19: MaxPax fra ConferenceRoomSetup (bordopstilling). Hvis ingen bordopstilling, fra HallTypes
--SG/2014-12-08: HallDescription VARCHAR(max) = Description. Don't use notes. Added HallImages
--SG/2014-10-14: Maxpax medtages i uændret resultatstruktur. HallDescription (notes) tilføjet. Order by MaxPax, Hallnumber
--sidst rettet LJE/20110504: get SubTypes for conferenceroom/hall.
--sidst rettet 2009 0302 if we are dealing with linked halls with different onlinebooktypes associated then all booknigs werent selected which means that a hall is declared as available even thought it aint.....
--sidst rettet 2007 0913 @AOnlineBookTypeNumber er ikke længere nøglen, men blot Number kolonnen fra OnlineBookType
--sidst rettet 2007 0831 :@ASearchHallNumber added
--sidsr rettet 2007 0319 Hotelnumber param added
--Sidst retttet 2007 0307 @AhallTypeID changed to @AOnlineBookTypeNumber
--Sidst retttet 2007 0306 @AhallTypeID added
--Sidst rettet 2006 0912 tilføjet TableSetupCount
--Sidst rettet 2006 1003 tilføjet APax og stor omskriving bla med kald af UDF
--sidst rettet  2006 1102 tilføføjet CountryNumber parameter
--sidst rettet 2006 1103 tilføjet AHotelNumber parameter, det er rimmet ud indtil Nikolaj bruger det rigtigt
--sidst rettet 2006 1108 tilføjet halltypenumber til resultat datasettet
--sidst rettet 2006 1115 tilføjet halltype til resultat datasettet
  DECLARE
  @HallNumberId INT,
  @HallNumber INT,
  @HallTypeNumber INT,
  @OnlineBookTypeNumberID INT,
  @HallType VARCHAR(5),
  @HallTypeMaxPax INT,
  @HallDescription VARCHAR(max), 
  @HallName VARCHAR(50),
  @TranslatedHallName VARCHAR(50),
  @TableSetupCount INT,
  @StartTime VARCHAR(5),
  @EndTime VARCHAR(5)
  ,@SubTypeText_Single VARCHAR(100)
  ,@SubTypeText_All VARCHAR(1000)
  ,@ConferenceRooms_Number INT
   
  SET @StartTime = CONVERT(VARCHAR(5),@AStartDateTime,108)   
  SET @EndTime = CONVERT(VARCHAR(5),@AEndDateTime,108) 
  SET @SubTypeText_All = ''

  CREATE TABLE #AvailableHalls (
		HallNumberId INT NOT NULL,
 		HallNumber INT NOT NULL,
 		HallName VARCHAR(50),
        HallTypeNumber INT,
        HallType VARCHAR(5),
        TableSetupCount INT,
        MaxPax INT,
        SubTypes VARCHAR(1000),
        HallDescription VARCHAR(max),
        HallImages VARCHAR(300) -- comma separated list
		)

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

  DECLARE #HallCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT  ConferenceRooms.ConferenceRoomNumber
    , ConferenceRooms.Number
    , ConferenceRooms.Name
    , (SELECT COUNT(*) FROM dbo.ConferenceRoomSetup(NOLOCK) WHERE ConferenceRoomSetup.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber) AS TableSetupCount
    , Translation.Name AS TranslatedHallName
    , ConferenceRooms.ConferenceRoomTypeNumber
    , ConferenceRoomTypes.Type
    , ConferenceRoomTypes.MaxPax
    , ConferenceRooms.Description
  FROM ConferenceRooms(NOLOCK)
  INNER JOIN ConferenceRoomTypes(NOLOCK) ON ConferenceRooms.ConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  LEFT OUTER JOIN Translation ON Translation.ObjectNumber = ConferenceRooms.ConferenceRoomNumber AND Translation.Type = 4 AND CountryNumber = @ACountryNumber
  LEFT OUTER JOIN OnlineBookTypeHallXTab(NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = ConferenceRooms.ConferenceRoomNumber
  WHERE ISNULL(IsTable,0) = 0 
  AND AllowFilterPicassoOnline = 1
  AND (CONVERT(VARCHAR(8), IsNull(OpeningTime, '00:00:00'),108) ='00:00:00' OR (CONVERT(VARCHAR(5),OpeningTime,108) <=@StartTime  AND CONVERT(VARCHAR(5), CloseTime,108) >=@EndTime))
  AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ConferenceRooms.HotelNumber = @AHotelNumber))
  AND   ((@AOnlineBookTypeNumber = 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID  AND DBO.OnlineHallTableSetupOK(ConferenceRooms.ConferenceRoomNumber, @ATableSetupNumberID, @APax) = 1) OR (@AOnlineBookTypeNumber > 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID))
  AND ((@ASearchHallNumber=0) OR (@ASearchHallNumber > 0 AND ConferenceRooms.ConferenceRoomNumber = @ASearchHallNumber))

  OPEN #HallCur

  FETCH NEXT FROM #HallCur
  INTO @HallNumberId, @HallNumber, @HallName, @TableSetupCount, @TranslatedHallName,  @HallTypeNumber, @HallType, @HallTypeMaxPax, @HallDescription

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    -- Find SubTypes and concat to a string.
    SET @SubTypeText_All = ''
	DECLARE #SubTypeCur CURSOR FOR
	select --cr.ConferenceRoomNumber  ,cr.Number  ,cr.Name,
	  crst.Text
	from conferencerooms cr
	  ,conferenceroomsubtypes crst
	  ,conferenceroomsubtyperoomsextab crx WITH (NOLOCK)
	where 
	  cr.ConferenceRoomNumber = crx.ConferenceRoomNumber
	  and crx.ConferenceRoomSubTypeNumber = crst.ConferenceRoomSubTypeNumber
	  and cr.ConferenceRoomNumber = @HallNumberId

	OPEN  #SubTypeCur
	FETCH NEXT FROM #SubTypeCur INTO @SubTypeText_Single
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	  SET @SubTypeText_All = @SubTypeText_All + @SubTypeText_Single + ' - '
	  FETCH NEXT FROM #SubTypeCur INTO @SubTypeText_Single
	END
	CLOSE #SubTypeCur
	DEALLOCATE #SubTypeCur

    IF @SubTypeText_All != ''
    BEGIN
      SET @SubTypeText_All = SUBSTRING(@SubTypeText_All,1,LEN(@SubTypeText_All) - 2)  -- Remove last ' - ' tag.
    END


    -- Find HallName
    IF LTRIM(RTRIM(@TranslatedHallName)) IS NOT NULL
      SET @HallName = @TranslatedHallName
        
    -- Find images
    Declare @Picture VarChar(100);
    Declare @HallImg VarChar(300);
    Set @Picture = '';
    Set @HallImg = '';
    If exists (select * from dbo.sysobjects where id = object_id('OnlineHallImagesXTab'))
    Begin
      Declare #HallImages CURSOR LOCAL FAST_FORWARD FOR
      Select ImageName From OnlineHallImagesXTab Where ConferenceRoomNumber = @HallNumberId Order By SortOrder
      Open #HallImages
      Fetch Next From #HallImages Into @Picture
      WHILE (@@FETCH_STATUS <> -1)
      Begin
        If @HallImg <> ''
        Begin
          SET @HallImg = @HallImg + ',';
        End
        SET @HallImg = @HallImg + @Picture;
        Fetch Next From #HallImages Into @Picture
      End  
      Close #HallImages    
      Deallocate #HallImages
    End
    --Correct MaxPax from tablesetup, if any
    DECLARE @MaxPaxFromTableSetup INT;
    Select @MaxPaxFromTableSetup = MAX(MaxPax) 
    From ConferenceRoomSetup
    WHERE ConferenceRoomSetup.ConferenceRoomNumber = @HallNumberID
    Set @MaxPaxFromTableSetup = ISNULL(@MaxPaxFromTableSetup,0);
    If @MaxPaxFromTableSetup > 0
    Begin
      Set @HallTypeMaxPax = @MaxPaxFromTableSetup;
    End
        
    INSERT INTO #AvailableHalls 
      (HallNumberID, HallNumber, HallName, HallTypeNumber, HallType, TableSetupCount, SubTypes, MaxPax, HallDescription, HallImages) 
    VALUES 
      (@HallNumberID,@HallNumber, @HallName,  @HallTypeNumber, @HallType, @TableSetupCount, @SubTypeText_All, @HallTypeMaxPax, @HallDescription, @HallImg)

    FETCH NEXT FROM #HallCur
    INTO @HallNumberId, @HallNumber, @HallName, @TableSetupCount, @TranslatedHallName,  @HallTypeNumber, @HallType, @HallTypeMaxPax, @HallDescription
  END

  CLOSE #HallCur 
  DEALLOCATE #HallCur
  --Reserved halls
  DECLARE #Rescur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber  
  FROM ReservationConferenceLine (NOLOCK)  INNER JOIN RESERVATIONS (NOLOCK) 
  ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenceNumber
  LEFT OUTER JOIN ReservationMain(NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  INNER JOIN CONFERENCEROOMSXTAB (NOLOCK) 
  ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
  INNER JOIN ConferenceRooms(NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
--  LEFT OUTER JOIN OnlineBookTypeHallXTab(NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = ReservationConferenceLine.ConferenceRoomNumber --Removed AV 2009 0302 if we have linked halls with different onlinebooktypes then this line must be removed
  WHERE 
  Reservations.GuestNumber = 0  AND 
  ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0  AND Reservations.Status < 7
--  AND (OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID) --Removed AV 2009 0302 if we have linked halls with different onlinebooktypes then this line must be removed
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
  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 ((@ASearchHallNumber=0) OR (@ASearchHallNumber > 0 AND ReservationConferenceLine.ConferenceRoomNumber = @ASearchHallNumber))
  And ReservationConferenceLine.ReservationConfLineNumber <> @ACurrentReservationConfLineNumber --excisting line when editing
  OPEN #Rescur

  FETCH NEXT FROM #Rescur
  INTO @HallNumberId

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    DELETE FROM #AvailableHalls WHERE HallNumberID = @HallNumberID
    FETCH NEXT FROM #Rescur
    INTO @HallNumberId
  END

  CLOSE #Rescur
  DEALLOCATE #Rescur

  


  SELECT HallNumberId, HallNumber, HallName, TableSetupCount, HallTypeNumber, HallType, MaxPax, SubTypes, HallDescription, HallImages
  FROM #AvailableHalls (NOLOCK) 
  --ORDER BY MaxPax, HallNumber
  
  ORDER BY CASE @OrderBy 
           WHEN 'HallNumberID' THEN REPLACE(STR(HallNumberId, 3),' ','0')
           WHEN 'HallNumber'   THEN REPLACE(STR(HallNumber, 3),' ','0')
           WHEN 'HallName'     THEN HallName
           WHEN 'MaxPax'       Then REPLACE(STR(MaxPax, 3),' ','0')    
         END  
         ,HallNumber


  DROP TABLE #AvailableHalls
  