CREATE PROCEDURE [dbo].[OnlineBookedHalls2] @AStartDateTime datetime, 
                                           @AEndDateTime Datetime, 
                                           @AincludeNonOnlineHalls Bit,
                                           @ATableSetupNumberID INT, @APax INT, @AOnlineBookTypeNumber INT,  
                                           @ACountryNumber  INT =0, @AHotelNumber INT = -1,
                                           @OrderBy Varchar(15) = 'MaxPax'
AS
--Returns a combination of halls and reservations in the halls.

--2015-07-30/sg: Added RoomLineNumber to result
--2015-06-10/sg: Combined halls: Now shows the meetings in occupied (sub)halls.
--               OrderBy option
--2015-06-06/sg: Combined halls: Reservations was shown even when the combined hall should not be shown
--2015-06-03/sg: Not confirmed reservations are included (CustomerCompany.Lastname = "PicassoOnline")
--2015-05-18/sg: OnlineBookedHalls2: Used in Meeting scheduler (Calendar), based on OnlineBookedHalls
--               Non Online can be included 
--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),
  @HallTypeMaxPax int,
  @HallDescription varchar(max),
  @StartTime VARCHAR(5),
  @EndTime VARCHAR(5),
  @ArrivalDate DATETIME,
  @DepartureDate DATETIME,
  @ReferenceNumber INT,
  @RoomLineNumber INT,
  @FirstName VARCHAR(50),
  @LastName VARCHAR(50),
  @AllowedFilterValues Varchar(6),
  @PicassoFilterOnline int,
  @MeetingName VarChar(50),
  @HostOwner VarChar(50), 
  @ContactpersonCustomerNumber int,
  @Contactperson VarChar(100),
  @Payer Varchar(50),
  @CreatedByOnline int
   
  SET @StartTime = CONVERT(VARCHAR(5),@AStartDateTime,108)   
  SET @EndTime = CONVERT(VARCHAR(5),@AEndDateTime,108) 
  
  If @AincludeNonOnlineHalls = 1
  Begin
    Set @AllowedFilterValues = '1,0'
  End Else
  Begin
    Set @AllowedFilterValues = '1'
  End    

  CREATE TABLE #BookedHalls (
		HallNumberId INT NOT NULL,
 		HallNumber INT NOT NULL,
 		HallName VARCHAR(20),
 		HallMaxPax INT,               
 		HallDescription varchar(max), 
 		PicassoFilterOnline int, --1: Online. 0: Not online
        ArrivalDate DATETIME,
        DepartureDate DATETIME,
        ReferenceNumber INT,
        RoomLineNumber int,
        FirstName VARCHAR(50),
        LastName VARCHAR(50),
        MeetingName Varchar(50),
        HostOwner Varchar(50),
        ContactpersonCustomerNumber int,
        ContactPerson Varchar(100),
        Payer Varchar(50),
        CreatedByOnline int 

		)
  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,
         ConferenceRoomTypes.MaxPax, ConferenceRooms.Description,
         AllowFilterPicassoOnline
  FROM ConferenceRoomsXtab WITH (NOLOCK) 
  INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  INNER JOIN ConferenceRoomTypes(NOLOCK) ON ConferenceRooms.ConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  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 IN  (Select Number From iter_intlist_to_table(@AllowedFilterValues))  
   AND (OnlineBookTypeHallXTab.OnlineBookTypeNumber=  @OnlineBookTypeNumberID)
   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))

  OPEN #Hallcur

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

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF LTRIM(RTRIM(@TranslatedHallName)) IS NOT NULL
      SET @HallName = @TranslatedHallName
      
    --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
    
    print @HallName
      
    INSERT INTO #BookedHalls (HallNumberID, HallNumber, HallName, HallMaxPax, HallDescription, PicassoFilterOnline) 
                      VALUES (@HallNumberID,@HallNumber, @HallName, @HallTypeMaxPax, @HallDescription, @PicassoFilterOnline)
    FETCH NEXT FROM #Hallcur 
    INTO @HallNumberId, @HallNumber, @HallName, @TranslatedHallName, @HallTypeMaxPax, @HallDescription, @PicassoFilterOnline
  END

  CLOSE #Hallcur
  DEALLOCATE #Hallcur

--Declare @TempCount int
--select @TempCount = COUNT(*) from #BookedHalls
--print @TempCount

--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) as Arrival,
  CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) as Departure,
  ReservationConferenceLine.ReferenceNumber, RESERVATIONCONFERENCELINE.ReservationConfLineNumber, Addr1.FirstName, Addr1.LASTNAME as MeetingName, 
  Addr1.ADDRESS1 as HostOwner, Addr2.ContactpersonCustomerNumber, Cust.FIRSTNAME + ' ' + Cust.LASTNAME as Contactperson, Addr2.LASTNAME as Payer,
  CASE p.UserTypeNumber WHEN 5 THEN 1 ELSE 0 END as CreatedByOnline
   
 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 ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
--  INNER JOIN CONFERENCEROOMSXTAB WITH (NOLOCK) 
--             ON ConferenceRoomsxtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
--  INNER JOIN ConferenceRooms CRoom2 ON ConferenceRoomsxtab.CollectsConferenceRoomNumber = CRoom2.ConferenceRoomNumber

  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 Translation.CountryNumber = 1
  INNER JOIN ReservationAddressXTab XTab1 WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = XTab1.ReferenceNumber 
                                                       AND ISNULL(ReservationConferenceLine.GuestNo, 0) = XTab1.GuestNumber 
                                                       AND XTab1.AddressTypeNumber = 1
  INNER JOIN ReservationAddress Addr1 WITH (NOLOCK) ON XTab1.AddressNumber = Addr1.AddressNumber

  LEFT JOIN ReservationAddressXTab Xtab2 WITH (NOLOCK) ON ISNULL(ReservationConferenceLine.GuestNo, 0 ) = Xtab2.GuestNumber
       AND Xtab2.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
       AND Xtab2.AddressTypeNumber = 2  
  LEFT JOIN ReservationAddress Addr2 WITH (NOLOCK) ON Addr2.AddressNumber = Xtab2.AddressNumber 
  LEFT JOIN Customer cust on Addr2.ContactpersonCustomerNumber = cust.CustomerNumber 
  INNER JOIN Personel p WITH (NOLOCK) ON ReservationMain.StaffID = p.UserIDNumber
 WHERE 
  Reservations.GuestNumber = 0  AND 
  ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 AND Reservations.Status < 7
  AND  ConferenceRooms.AllowFilterPicassoOnline in (Select Number From iter_intlist_to_table(@AllowedFilterValues))
  AND (OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID)
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ConferenceRooms.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   ((@AOnlineBookTypeNumber = 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID  AND DBO.OnlineHallTableSetupOK(ConferenceRooms.ConferenceRoomNumber, @ATableSetupNumberID, @APax) = 1) OR (@AOnlineBookTypeNumber > 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID))

UNION
--halls occupied via collects
SELECT DISTINCT 
  ConferenceRoomsXtab.CollectsConferenceRoomNumber, CRooms2.Number, CRooms2.Name, Translation.Name AS TranslatedHallName,
  CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) as Arrival,
  CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) as Departure,
  ReservationConferenceLine.ReferenceNumber, RESERVATIONCONFERENCELINE.ReservationConfLineNumber, Addr1.FirstName, Addr1.LASTNAME as MeetingName, 
  Addr1.ADDRESS1 as HostOwner, Addr2.ContactpersonCustomerNumber, Cust.FIRSTNAME + ' ' + Cust.LASTNAME as Contactperson, Addr2.LASTNAME as Payer,
  CASE p.UserTypeNumber WHEN 5 THEN 1 ELSE 0 END as CreatedByOnline
   
 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.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  INNER JOIN ConferenceRooms CRooms2 ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = CRooms2.ConferenceRoomNumber
   
  LEFT OUTER JOIN OnlineBookTypeHallXTab WITH (NOLOCK) ON OnlineBookTypeHallXTab.HallNumber = CRooms2.ConferenceRoomNumber
  LEFT OUTER JOIN Translation ON Translation.ObjectNumber = CRooms2.ConferenceRoomNumber AND Translation.Type = 4 AND Translation.CountryNumber = 1
  INNER JOIN ReservationAddressXTab XTab1 WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = XTab1.ReferenceNumber 
                                                       AND ISNULL(ReservationConferenceLine.GuestNo, 0) = XTab1.GuestNumber 
                                                       AND XTab1.AddressTypeNumber = 1
  INNER JOIN ReservationAddress Addr1 WITH (NOLOCK) ON XTab1.AddressNumber = Addr1.AddressNumber

  LEFT JOIN ReservationAddressXTab Xtab2 WITH (NOLOCK) ON ISNULL(ReservationConferenceLine.GuestNo, 0 ) = Xtab2.GuestNumber
       AND Xtab2.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
       AND Xtab2.AddressTypeNumber = 2  
  LEFT JOIN ReservationAddress Addr2 WITH (NOLOCK) ON Addr2.AddressNumber = Xtab2.AddressNumber 
  LEFT JOIN Customer cust on Addr2.ContactpersonCustomerNumber = cust.CustomerNumber 
  INNER JOIN Personel p WITH (NOLOCK) ON ReservationMain.StaffID = p.UserIDNumber
 WHERE 
  Reservations.GuestNumber = 0  AND 
  ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 AND Reservations.Status < 7
  AND  CRooms2.AllowFilterPicassoOnline in (Select Number From iter_intlist_to_table(@AllowedFilterValues))
  AND (OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID)
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND CRooms2.HotelNumber = @AHotelNumber ))
  AND CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < DATEADD(MINUTE, ISNULL(CRooms2.PauseInMinutes,0),@AEndDateTime )
  AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > DATEADD(MINUTE,- ISNULL(CRooms2.PauseInMinutes,0),@AStartDateTime)
  AND   ((@AOnlineBookTypeNumber = 1 AND OnlineBookTypeHallXTab.OnlineBookTypeNumber = @OnlineBookTypeNumberID  AND DBO.OnlineHallTableSetupOK(CRooms2.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, @RoomLineNumber, @FirstName, @MeetingName, 
       @HostOwner, @ContactpersonCustomerNumber, @Contactperson, @Payer,
       @CreatedByOnline

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

  CLOSE #Rescur
  DEALLOCATE #Rescur

  
  -- Fill HallMaxPax into the booking-records from the halls records
  DECLARE #BookedHallsCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT HallNumberId, HallMaxPax 
  FROM #BookedHalls WITH (NOLOCK)
  WHERE ISNULL(HallMaxPax, -1) > -1
  
  OPEN #BookedHallsCur
  FETCH NEXT FROM #BookedHallsCur INTO @HallNumberId, @HallTypeMaxPax
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    UPDATE #BookedHalls Set HallMaxPax = @HallTypeMaxPax WHERE HallNumberId = @HallNumberId
  
    FETCH NEXT FROM #BookedHallsCur INTO @HallNumberId, @HallTypeMaxPax
  END
  CLOSE #BookedHallsCur
  DEALLOCATE #BookedHallsCur
  
  
  SELECT HallNumberId, HallNumber, HallName, HallMaxPax, HallDescription, PicassoFilterOnline, 
         ArrivalDate, DepartureDate, ReferenceNumber, RoomLineNumber, FirstName, LastName, MeetingName,
         HostOwner, ContactpersonCustomerNumber, ContactPerson, Payer,
         CreatedByOnline  
  FROM  #BookedHalls WITH (NOLOCK) 
  
  ORDER BY CASE @OrderBy 
             --WHEN 'HallNumberID' THEN 
             WHEN 'HallNumber'   THEN REPLACE(STR(HallNumber, 3),' ','0')
             WHEN 'HallName'     THEN HallName
             WHEN 'MaxPax'       Then REPLACE(STR(HallMaxPax, 3),' ','0')    
           END  
           ,HallNumberID, ArrivalDate
  DROP TABLE #BookedHalls
  