CREATE PROCEDURE DBO.SetInOrderHall @HallNumberID INT, @StartDate DATETIME, @EndDate DATETIME, @OutForService BIT = 0 AS
SET NOCOUNT ON
/*
    Created By: Jacob Pedersen 24/6-2002
    JP 20050518 Use GetNextNumber instead of directly updating table
    JP 20050107: use period times from parameters instead of hotelsetup. Huge rewrite
    JP 20050103: delete notes
    Last Updated:	JP 20040413 was setting day before in-use also 
		JP 20040405 out-for-service on res -2 
		02/01-2003 read inuse status from hotelsetup

*/

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

SET @StartDate = DATEADD(Minute, -1, @StartDate)
SET @EndDate = DATEADD(Minute, 1, @EndDate)

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

DECLARE @FormatedStartDate CHAR (8),
@FormatedEndDate CHAR (8),
@TimeOfArrivalForRes CHAR (5), --TimeOfArrivalForRes '18:00'
@TimeOfDepartureForRes CHAR (5) --TimeOfDepartureForRes '12:00'
SET @FormatedStartDate =  CONVERT (CHAR (8), @StartDate, 112)
--SET @FormatedEndDate =  CONVERT (CHAR (8), @EndDate+1, 112)  -- JP 20021009
SET @FormatedEndDate = CONVERT (CHAR (8), @EndDate, 112)
SET @TimeOfArrivalForRes = CONVERT (CHAR (5), @StartDate, 108)
SET @TimeOfDepartureForRes = CONVERT (CHAR (5), @EndDate, 108)

/*
DECLARE @Arr24HoursStarts CHAR (5)  --Arr24HoursStarts '07:00'
DECLARE @Arr24HoursEnds CHAR (5) -- '06:59'
SELECT @Arr24HoursStarts=KeyValue FROM HotelSetup (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('Arr24HoursStarts')
SELECT @Arr24HoursEnds = CONVERT (CHAR (5), DATEADD (MINUTE, -1, CONVERT (DATETIME, @Arr24HoursStarts, 108)), 108)
*/
DECLARE @LineNumber INT
DECLARE @ArrivalDate CHAR (8)
DECLARE @ArrivalTime CHAR (5)
DECLARE @DepartureDate CHAR (8)
DECLARE @DepartureTime CHAR (5)

DECLARE #HallLineCur CURSOR FORWARD_ONLY FOR

-- Find all lines within the period
/*
SELECT ReservationConfLineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime FROM ReservationConferenceLine (NOLOCK)
--WHERE ConferenceRoomNumber=@HallNumberID AND ArrivalDate<=@FormatedEndDate AND DepartureDate>=@FormatedStartDate AND ReferenceNumber=@ReferenceNumber
WHERE ConferenceRoomNumber=@HallNumberID AND ReferenceNumber=@ReferenceNumber AND
((ArrivalDate > @FormatedStartDate) OR (ArrivalDate = @FormatedStartDate AND ArrivalTime >= @TimeOfArrivalForRes)) AND -- condition 1: arrival is before existing arrival
((DepartureDate < @FormatedEndDate) OR (DepartureDate = @FormatedEndDate AND DepartureTime <= @TimeOfDepartureForRes)) -- condition 2: departure is after existing departure
*/
SELECT ReservationConfLineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime FROM ReservationConferenceLine WITH (NOLOCK)
WHERE ConferenceRoomNumber=@HallNumberID AND ReferenceNumber=@ReferenceNumber AND
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

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
print 'ad:'+@arrivaldate+' '+@arrivaltime+' - '+ @departuredate+' '+@departuretime
print 'fse:'+@FormatedStartDate+' '+@TimeOfArrivalForRes+' - '+@FormatedEndDate+' '+@TimeOfDepartureForRes
--  IF @ArrivalDate >= @FormatedStartDate AND @DepartureDate <= @FormatedEndDate  -- Exact period -> delete row. TODO: check for time
  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 'in4: extend existing period at both start and end since our period is overlapping existing:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    DELETE FROM ReservationConferenceLine 
    WHERE ReservationConfLineNumber=@LineNumber
    
    DELETE FROM InUseNoteXTab 
    WHERE ObjectType = 1 AND
		  ObjectNumber = @LineNumber 
    -- NO BREAK! because there can be more than one lines in the period!
  END
--  ELSE IF @ArrivalDate < @FormatedStartDate AND @DepartureDate > @FormatedEndDate  -- Covers whole period
  ELSE IF (@FormatedStartDate > @ArrivalDate OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes >= @ArrivalTime)) AND
                (@FormatedEndDate < @DepartureDate OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes <= @DepartureTime))
  BEGIN
print 'in3: setting inorder within an existing outoforder. splitting existing in two. shorten one and create an other: '+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    -- Decrease Roomline
--    UPDATE ReservationConferenceLine SET DepartureDate=CONVERT (CHAR (8), @StartDate, 112) WHERE ReservationConfLineNumber=@LineNumber  -- JP 20040413 was setting day before in-use also
    UPDATE ReservationConferenceLine 
    SET DepartureDate=@FormatedStartDate, 
		DepartureTime=@TimeOfArrivalForRes 
	WHERE ReservationConfLineNumber=@LineNumber
	
    -- Create new roomline with remaining period
    DECLARE @NewLineNumber INT,
		    @NoteNumber INT
		    
	SELECT @NoteNumber = ISNULL(NoteNumber, 0)
	FROM InUseNoteXTab WITH (NOLOCK) 
	WHERE ObjectType = 1 AND
		  ObjectNumber = @LineNumber 
/*
    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, @FormatedEndDate, @TimeOfDepartureForRes, @DepartureDate, @DepartureTime, @ReferenceNumber)
    
    IF @NoteNumber > 0
		INSERT INTO InUseNoteXTab (ObjectType, ObjectNumber, NoteNumber)
		VALUES (1, @NewLineNumber, @NoteNumber)
    
    BREAK -- Break is OK. There should not be any other, if MergeOOO SP has been called from SetOutOfOrder SP
  END
  ELSE 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 'in1: shortening existing period at the end:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    UPDATE ReservationConferenceLine SET DepartureDate=@FormatedStartDate, DepartureTime=@TimeOfArrivalForRes WHERE ReservationConfLineNumber=@LineNumber
--    BREAK   do not break. there might be more!
  END
  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 'in2: shortening existing period at the start:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    UPDATE ReservationConferenceLine SET ArrivalDate=@FormatedEndDate, ArrivalTime=@TimeOfDepartureForRes WHERE ReservationConfLineNumber=@LineNumber
--    BREAK   do not break. there might be more!
  END

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

CLOSE #HallLineCur
DEALLOCATE #HallLineCur
/*
---- CLEAN STATUS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Set cleanstatus if today is within period
DECLARE @SystemDateStr CHAR (8)
SELECT  @SystemDateStr = SYSTEMDATE FROM System  (NOLOCK) WHERE NUMBER = 1  -- Only one record

IF @FormatedStartDate <= @SystemDateStr AND @FormatedEndDate >= @SystemDateStr
BEGIN
  DECLARE @InuseStatus SMALLINT
  SELECT @InuseStatus=CONVERT (SMALLINT, KeyValue) FROM HotelSetup (NOLOCK) WHERE Upper (SearchKey)=Upper('InuseStatusHall')    --  TCleaningStatus = (csClean, // 0     csDirty, // 1
  if IsNull (@InuseStatus, 0) = 0
    Set @InuseStatus = 0

  UPDATE ConferenceRooms SET CleanStatusNumber=@InuseStatus WHERE ConferenceRoomNumber=@HallNumberID
END
*/

--SET lock_timeout 0

