
CREATE PROCEDURE DBO.GenerateHallScheduleDataSet @StartDateTime datetime, @HallTypeNumbers VARCHAR(50), @MinutesBeforeArrival INT = 0   AS
SET NOCOUNT ON

/*
    CREATED BY Anders Vindal 2006 0113
    This proc returns a dataset containing a list of halls with information on which guest is currently in it.
    Last changed:
    AV 2006 0904 show minimum 10 lines else it looks stupid
    AV 2006 0817 new parameter @MinutesbeforeArrival
    AV 20060515 If secret meeting then print the text from hotelsetup
    AV 20060421 Take the topic from reservationconferenceline.HallSchedulerNote
*/
DECLARE

@HallNumberId INT,
@HallNumber INT,
@HallName VARCHAR(20),
@LastName VARCHAR(75),
@CurrentLastName VARCHAR(75),
@CompanyName VARCHAR(75),
@Topic VARCHAR (100),
@MeetingSecret bit,
@TextForSecretMeetings VARCHAR(50),
@ArrivalDateTime DATETIME,
@CurrentRecordCount INT

SET @TextForSecretMeetings = NULL
SELECT @TextForSecretMeetings= KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'TextForSecretMeetings'



CREATE TABLE #HallTable (
	--	HallNumberId INT NOT NULL,
-- 		HallNumber INT NOT NULL,
		HallNumberId INT,
 		HallNumber INT,
 		HallName VARCHAR(20),
		LastName VARCHAR(75),
                           CompanyName VARCHAR(75) 
          
		)

  DECLARE #HallCur CURSOR LOCAL FAST_FORWARD FOR
  SELECT ConferenceRoomNumber,Number, Name FROM ConferenceRooms WITH (NOLOCK) WHERE
  ISNULL(IsTable,0) = 0
  AND DBO.IsHallTypeInList(ConferenceRooms.ConferenceRoomTypeNumber, @HallTypeNumbers) = 1
  
  ORDER BY Number

  OPEN #HallCur

  FETCH NEXT FROM #HallCur
  INTO @HallNumberId, @HallNumber, @HallName


  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    INSERT INTO #HallTable (HallNumberID, HallNumber, HallName) VALUES (@HallNumberID,@HallNumber, @HallName)
    FETCH NEXT FROM #HallCur
    INTO @HallNumberId, @HallNumber, @HallName
  END
  CLOSE #HallCur
  DEALLOCATE #HallCur

  SELECT @CurrentRecordCount = COUNT(*) FROM #HallTable WITH (NOLOCK)
  IF @CurrentRecordCount < 10
  BEGIN
    WHILE @CurrentRecordCount <10
    BEGIN
      INSERT INTO #HallTable (HallNumberID, HallNumber, HallName) VALUES (NULL,NULL, NULL) 
      SET @CurrentRecordCount = @CurrentRecordCount + 1
    END
  END

   DECLARE #ResCur CURSOR LOCAL FAST_FORWARD FOR
   SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber,ConferenceRooms.Number, RA1.LastName AS GuestName, RA2.LastName AS CompanyName, ReservationConferenceLine.HallSchedulerNote, ReservationConferenceLine.MeetingSecret, CONVERT(DateTime,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DateTime,ReservationConferenceLine.ArrivalTime,108) AS ArrivalDateTime
   FROM ConferenceRooms WITH (NOLOCK)

   INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber =ReservationConferenceline.ConferenceRoomNumber 

   INNER JOIN Reservations WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber =reservations.ReferenceNumber and Reservations.GuestNumber = 0 and reservations.Status < 6 
   AND Reservations.ReferenceNumber > 0 

   INNER JOIN ReservationAddressXtab RAX1 WITH (NOLOCK) ON Reservations.ReferenceNumber = RAX1.ReferenceNumber AND RAX1.GuestNumber = 0
   AND RAX1.AddressTypeNumber = 1
   LEFT OUTER JOIN ReservationAddress RA1 WITH (NOLOCK) ON RAX1.AddressNumber = RA1.AddressNumber

   LEFT OUTER JOIN ReservationAddressXtab RAX2 WITH (NOLOCK) ON Reservations.ReferenceNumber = RAX2.ReferenceNumber AND RAX2.GuestNumber = 0
   AND RAX2.AddressTypeNumber = 2
   LEFT OUTER JOIN ReservationAddress RA2 WITH (NOLOCK) ON RAX2.AddressNumber = RA2.AddressNumber

  INNER JOIN ConferenceRoomsXtab WITH (NOLOCK) ON ConferenceRoomsXtab.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber

   WHERE ISNULL(istable,0) = 0 
   AND DATEADD(MINUTE,-@MinutesBeforeArrival,CONVERT(DateTime,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DateTime,ReservationConferenceLine.ArrivalTime,108))
    <= @StartDateTime
    AND CONVERT(DateTime,ReservationConferenceLine.DepartureDate,112) + CONVERT(DateTime,ReservationConferenceLine.DepartureTime,108) 
    > @StartDateTime
  AND ISNULL(SchemePattern, 0) = 0
  ORDER BY ArrivalDateTime


  OPEN #ResCur

  FETCH NEXT FROM #ResCur
  INTO @HallNumberId, @HallNumber, @LastName, @CompanyName, @Topic, @MeetingSecret, @ArrivalDateTime

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SET @CurrentLastName = NULL
    SELECT @CurrentLastName = LastName FROM #HallTable WITH (NOLOCK) WHERE HallNumberID = @HallNumberID      
    IF ISNULL(@CurrentLastName,'') = ''
    BEGIN
      IF RTRIM(LTRIM(ISNULL(@TextForSecretMeetings,''))) <> ''  AND @MeetingSecret = 1
      BEGIN
        SET @LastName = @TextForSecretMeetings
        SET @CompanyName = @TextForSecretMeetings
      END

      IF RTRIM(LTRIM(ISNULL(@Topic,''))) <> ''
        SET @LastName = @Topic


      UPDATE #HallTable SET LastName = @LastName, CompanyName = @CompanyName
      WHERE HallNumberID = @HallNumberid
    END
    FETCH NEXT FROM #ResCur
    INTO @HallNumberId, @HallNumber,@LastName, @CompanyName, @Topic, @MeetingSecret,@ArrivalDateTime
  END
  CLOSE #ResCur
  DEALLOCATE #ResCur

SELECT HallNumber, HallName, LastName, CompanyName FROM #HallTable WITH (NOLOCK)

DROP Table #HallTable

