CREATE PROCEDURE dbo.SetOutOfOrderHall @HallNumberID INT, @StartDate DATETIME, @EndDate DATETIME, @ErrorLevel INT OUTPUT, @Note VARCHAR(8000), @OutForService BIT = 0  AS
SET NOCOUNT ON
/*
    Created By: Jacob Pedersen 24/6-2002
    
    RN 2011 1020 fix for smalldatetime conversion error.
    AV 2008 1029 fix for error when inserting notetype into inusenotextab
    AV 2008 0918 fix for not saving notes.
    JP 20050518 Use GetNextNumber instead of directly updating table
    JP 20050418 Fix for checking if hall is already booked in period
    JP 20050107 Use parameter times instead of from hotelsetup. huge rewrite was needed. good thing is that it's now easier to deal with halls in the same way as rooms so most code was copied
    JP 20050103 Insert Note
    Last Updated: JP 20040405 Out-for-Service on res -2
    Last Updated: 09/10-2002 Jacob Pedersen went from using 00:00 to 23 on the same day, to using hotel setup 07:00 to 06 on two days

    NOTE: - Last routine will get increasingly slower the more halls we set out of order

                - The get of a new linenumber is a problem if we call the sp in a transaction the identity table will be locked for the duration of the transaction. this can be fixed by always calling it outside the transaction
*/

--SET lock_timeout 50000  -- give updates and inserts a chance to complete! 

SET @OutForService = ISNULL(@OutForService, 0)

IF @EndDate=0
  SET @EndDate=@StartDate

DECLARE @ReferenceNumber INT
IF @OutForService = 1
  SET @ReferenceNumber = -2
ELSE
  SET @ReferenceNumber = 0

DECLARE @FormatedStartDate CHAR (8),
@FormatedEndDate CHAR (8),
@TimeOfArrivalForRes CHAR (5),  --Arr24HoursStarts '07:00'
@TimeOfDepartureForRes CHAR (5)  --Arr24HoursEnds '06:59'
SET @FormatedStartDate =  CONVERT (CHAR (8), @StartDate, 112)
--SET @FormatedEndDate =  CONVERT (CHAR (8), @EndDate+1, 112)
SET @FormatedEndDate =  CONVERT (CHAR (8), @EndDate, 112)
SET @TimeOfArrivalForRes =  CONVERT (CHAR (5), @StartDate, 108)
SET @TimeOfDepartureForRes =  CONVERT (CHAR (5), @EndDate, 108)

/*
SELECT @Arr24HoursStarts=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('Arr24HoursStarts')
*/

DECLARE @LineNumber INT,
@SetNote Bit,
@ArrivalDate CHAR (8),
@ArrivalTime CHAR (5),
@DepartureTime CHAR (5),
@DepartureDate CHAR (8)

IF @OutForService = 0
BEGIN
  SELECT @ErrorLevel=COUNT (*) FROM ReservationConferenceLine WITH (NOLOCK)
--  WHERE ((ArrivalDate<@FormatedEndDate AND DepartureDate>@FormatedStartDate) OR ((ArrivalDate=@FormatedEndDate AND ArrivalTime<@Arr24HoursStarts) OR (DepartureDate=@FormatedStartDate AND DepartureTime>@Arr24HoursStarts)))
/*
  WHERE ((ArrivalDate<@FormatedEndDate AND DepartureDate>@FormatedStartDate) OR ((ArrivalDate=@FormatedEndDate AND ArrivalTime<=@TimeOfDepartureForRes) OR (DepartureDate=@FormatedStartDate AND DepartureTime>=@TimeOfArrivalForRes)))
*/ -- JP 20050418 fix for period checking
  WHERE CONVERT(DATETIME,ArrivalDate,112) + CONVERT(DATETIME,ArrivalTime,108) < @EndDate AND
        CONVERT(DATETIME,DepartureDate,112) + CONVERT(DATETIME,DepartureTime,108) > @StartDate
        AND (ReferenceNumber > 0) AND
        (ConferenceRoomNumber = @HallNumberID OR ConferenceRoomNumber IN (SELECT CollectsConferenceRoomNumber FROM ConferenceRoomsXtab WITH (NOLOCK) WHERE ConferenceRoomsXtab.ConferenceRoomNumber = @HallNumberID)) AND
        ((SELECT Status FROM Reservations WITH (NOLOCK) WHERE Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber AND Reservations.GuestNumber = 0) < 7)
        AND ISNULL(SchemePattern, 0) = 0

  IF @ErrorLevel>0
    RETURN  -- Hall cannot be set out-of-order in period as it is booked

END
ELSE
  SET @ErrorLevel = 0

---- INSERT NOTE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF (@Note <> '') AND (@Note IS NOT NULL)
	Set @SetNote = 1
ELSE
	Set @SetNote = 0

IF @SetNote = 1
BEGIN

  DECLARE @NoteNumber INT
  INSERT INTO InUseNote (Note) VALUES (@Note)
  SELECT @NoteNumber = @@identity

END


DECLARE #HallLineCur CURSOR STATIC FORWARD_ONLY FOR

SELECT ReservationConfLineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime FROM ReservationConferenceLine WITH (NOLOCK)
WHERE ReferenceNumber=@ReferenceNumber AND ConferenceRoomNumber=@HallNumberID AND
--ArrivalDate<=@FormatedEndDate AND DepartureDate>=@FormatedStartDate 
--((ArrivalDate<@FormatedEndDate AND DepartureDate>@FormatedStartDate) OR ((ArrivalDate=@FormatedEndDate AND ArrivalTime<=@TimeOfDepartureForRes) OR (DepartureDate=@FormatedStartDate AND DepartureTime>=@TimeOfArrivalForRes)))
CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108) <= @EndDate AND CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108) >= @StartDate

OPEN #HallLineCur

FETCH NEXT FROM #HallLineCur
INTO @LineNumber, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime

IF @@FETCH_STATUS = -1  -- No roomlines covers any part of the period. We need to create new roomline
BEGIN
  print '1: no confline found that covers period'
  DECLARE @NewLineNumber INT
/*
  BEGIN TRAN
    UPDATE ReservationRoomLineIdentity SET @NewLineNumber = IdentityField = IdentityField + 1  -- New LineNumber
  COMMIT TRAN
*/
  EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT
  DECLARE @HallTypeNumber INT
  SELECT @HallTypeNumber=ConferenceRoomTypeNumber FROM ConferenceRooms WITH (NOLOCK) Where ConferenceRoomNumber=@HallNumberID
  INSERT INTO ReservationConferenceLine (ReservationConfLineNumber, NumberOfRooms, BookedConferenceRoomTypeNumber, ConferenceRoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, ReferenceNumber)
--  VALUES (@NewLineNumber, 1, @HallTypeNumber, @HallNumberID, @FormatedStartDate, '00:00', @FormatedEndDate, '23:59', 0)
--  VALUES (@NewLineNumber, 1, @HallTypeNumber, @HallNumberID, @FormatedStartDate, @Arr24HoursStarts, @FormatedEndDate, CONVERT (CHAR (5), DATEADD (MINUTE, -1, CONVERT (DATETIME, @Arr24HoursStarts, 108)), 108), 0)  -- JP 20021009
--  VALUES (@NewLineNumber, 1, @HallTypeNumber, @HallNumberID, @FormatedStartDate, @Arr24HoursStarts, @FormatedEndDate, CONVERT (CHAR (5), DATEADD (MINUTE, -1, CONVERT (DATETIME, @Arr24HoursStarts, 108)), 108), @ReferenceNumber)  -- JP 20040405
  VALUES (@NewLineNumber, 1, @HallTypeNumber, @HallNumberID, @FormatedStartDate, @TimeOfArrivalForRes, @FormatedEndDate, @TimeOfDepartureForRes, @ReferenceNumber)
  
  IF @SetNote = 1
	INSERT INTO InUseNoteXTab (ObjectType, ObjectNumber, NoteNumber)
	VALUES (1, @NewLineNumber, @NoteNumber)
  
  
END
ELSE  -- hallline(s) found
BEGIN
  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
--    IF @DepartureDate >= @FormatedStartDate AND @ArrivalDate <= @FormatedStartDate
    IF ((@FormatedStartDate < @DepartureDate) OR (@FormatedStartDate = @DepartureDate AND @TimeOfArrivalForRes <= @DepartureTime)) AND -- condition 1: new period is before end of existing one
        ((@FormatedStartDate > @ArrivalDate) OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes >= @ArrivalTime)) AND -- condition 2: new period is after start of existing one
        ((@FormatedEndDate > @DepartureDate) OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes > @DepartureTime)) -- condition 3 new period is ending after end of existing period. NB. No need for >= @DepartureTime since it would be within period
    BEGIN
      print '2: extend existing period at the end:'+convert(varchar, @LineNumber)+' '+@DepartureDate+' '+@DepartureTime
      UPDATE ReservationConferenceLine 
      SET DepartureDate=@FormatedEndDate, 
		  DepartureTime=@TimeOfDepartureForRes 
	  WHERE ReservationConfLineNumber=@LineNumber
	  
	  IF @SetNote = 1
		UPDATE InUseNoteXTab 
		SET NoteNumber = @NoteNumber 
		WHERE ObjectType = 1 AND
			  ObjectNumber = @LineNumber 
      BREAK
    END
--    ELSE IF @ArrivalDate <= @FormatedEndDate AND @DepartureDate >= @FormatedEndDate
    ELSE IF ((@FormatedEndDate > @ArrivalDate) OR (@FormatedEndDate = @ArrivalDate AND @TimeOfDepartureForRes >= @ArrivalTime)) AND -- condition 1: new period is after start of existing one
                  ((@FormatedEndDate < @DepartureDate) OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes <= @DepartureTime)) AND -- condition 2: new period is before end of existing one
                  ((@FormatedStartDate < @ArrivalDate) OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes < @ArrivalTime)) -- condition 3 new period is starting before start of existing period. NB. No need for <= @ArrivalTime since it would be within period
    BEGIN
      print '3: extend existing period at the start:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime
      UPDATE ReservationConferenceLine 
      SET ArrivalDate=@FormatedStartDate, 
		  ArrivalTime=@TimeOfArrivalForRes 
	  WHERE ReservationConfLineNumber=@LineNumber
	  
	  IF @SetNote = 1
		UPDATE InUseNoteXTab 
		SET NoteNumber = @NoteNumber 
		WHERE ObjectType = 1 AND
			  ObjectNumber = @LineNumber
      BREAK
    END
--    ELSE IF @ArrivalDate >= @FormatedStartDate AND @DepartureDate <= @FormatedEndDate
    ELSE IF ((@FormatedStartDate < @ArrivalDate) OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes < @ArrivalTime)) AND -- condition 1: arrival is before existing arrival
                  ((@FormatedEndDate > @DepartureDate) OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes > @DepartureTime)) -- condition 2: departure is after existing departure
    BEGIN
      print '4: extend existing period at both start and end since our period is overlapping existing:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
      UPDATE ReservationConferenceLine 
      SET ArrivalDate=@FormatedStartDate, 
		  ArrivalTime=@TimeOfArrivalForRes, 
		  DepartureDate=@FormatedEndDate, 
		  DepartureTime=@TimeOfDepartureForRes 
	  WHERE ReservationConfLineNumber=@LineNumber
	  
	  IF @SetNote = 1
		UPDATE InUseNoteXTab 
		SET NoteNumber = @NoteNumber 
		WHERE ObjectType = 1 AND
			  ObjectNumber = @LineNumber
      BREAK
    END
ELSE
begin
  print 'no match???'
  print convert(varchar, @LineNumber)+': '+@arrivaldate+' '+@arrivaltime+' - '+@departuredate+' '+@departuretime
  print 'new period: '+@FormatedStartDate+' '+@TimeOfArrivalForRes+' - '+@FormatedEndDate+' '+@TimeOfDepartureForRes
  print ''
end

    FETCH NEXT FROM #HallLineCur
    INTO @LineNumber, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime
  END  -- WHILE
END

CLOSE #HallLineCur
DEALLOCATE #HallLineCur

-- TODO: extend room merge routine to handle halls also since they new are virtually the same

/*
-- if we have 2 halllines which are ending and starting at the same point eg.    25-06 23 and 26-06 00:00 then we must merge these.  this is done by deleting row2 and changing departuredate on row1 to be equal to row2
DECLARE @MergeLineNumber INT,
@MergeArrivalDate CHAR (8),
@MergeArrivalTime CHAR (5),
@MergeDepartureDate CHAR (8),
@MergeDepartureTime CHAR (5),
@GoAgain SMALLINT
SET @GoAgain=1

WHILE @GoAgain = 1
BEGIN
  DECLARE #HallLineCur CURSOR FORWARD_ONLY FOR

--  SELECT ReservationConfLineNumber, ArrivalDate, DepartureDate, DepartureTime FROM ReservationConferenceLine (NOLOCK) WHERE ConferenceRoomNumber=@HallNumberID AND ReferenceNumber=0
  SELECT ReservationConfLineNumber, ArrivalDate, DepartureDate, DepartureTime FROM ReservationConferenceLine (NOLOCK) WHERE ConferenceRoomNumber=@HallNumberID AND ReferenceNumber=@ReferenceNumber -- JP 20040405

  OPEN #HallLineCur

  FETCH NEXT FROM #HallLineCur
  INTO @LineNumber, @ArrivalDate, @DepartureDate, @DepartureTime

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    SET @GoAgain = 0
--    SELECT @MergeLineNumber=ReservationConfLineNumber, @MergeArrivalDate=ArrivalDate, @MergeDepartureDate=DepartureDate FROM ReservationConferenceLine (NOLOCK) WHERE ReferenceNumber=0 AND CONVERT (DATETIME, ArrivalDate)=CONVERT (DATETIME, @DepartureDate)+1 AND ConferenceRoomNumber=@HallNumberID
    SELECT @MergeLineNumber=ReservationConfLineNumber, @MergeArrivalDate=ArrivalDate, @MergeDepartureDate=DepartureDate FROM ReservationConferenceLine (NOLOCK)
--    WHERE ReferenceNumber=0 AND CONVERT (DATETIME, ArrivalDate)=CONVERT (DATETIME, @DepartureDate) AND ConferenceRoomNumber=@HallNumberID  -- JP 20021009
    WHERE ReferenceNumber=@ReferenceNumber AND CONVERT (DATETIME, ArrivalDate)=CONVERT (DATETIME, @DepartureDate) AND ConferenceRoomNumber=@HallNumberID  -- JP 20040405
    IF @@rowcount > 0
    BEGIN
      DELETE FROM ReservationConferenceLine WHERE ReservationConfLineNumber=@MergeLineNumber

--      UPDATE ReservationConferenceLine SET DepartureDate=@MergeDepartureDate WHERE ReservationConfLineNumber=@LineNumber
      UPDATE ReservationConferenceLine SET DepartureDate=@MergeDepartureDate, DepartureTime=@MergeDepartureTime WHERE ReservationConfLineNumber=@LineNumber
      SET @GoAgain=1
      BREAK -- WHILE -- We have deleted a row. We need to check from the begining again
    END

    FETCH NEXT FROM #HallLineCur
    INTO @LineNumber, @ArrivalDate, @DepartureDate, @DepartureTime
  END  -- WHILE

  CLOSE #HallLineCur
  DEALLOCATE #HallLineCur

END -- WHILE @GoAgain

*/


SET @ErrorLevel = 0

--SET lock_timeout 0

