CREATE PROCEDURE DBO.AllotmentModify @OLD_AllotmentNumber int, @StartDate smalldatetime, @EndDate smalldatetime, @AllocatedTypeNumber int AS
SET NOCOUNT ON
/*
   DEPENDENSIES: GenerateAvailability as caller

    Created by: Jacob Pedersen
    Last updated by: 16-01-2003 Jacob Pedersen cursors changed to READ_ONLY FAST_FORWARD
    JP 20040428 modified. moved code to genavail plus changed update of start+end so only updated if period affected
    JP 20040510 modified. fields removed from AllotmentLines
    JP 20050614 modified: use function dbo.GetCurrentCount
    JP 20050909 bug: run-of-house does not work if missing rows in allotmentlines
    JP 20050928 OPTIMIZE: much faster when NumberOfRooms has changed by more than one. Instead of taking one room at a time. take so many we can from the other roomtypes
    JP 20060626 OPTIMIZE: cleaned up code, added dbo. prefix to all tables
    JP 20061025 OPTIMIZE: Only update if dates have changed

    NOTE:
    - This procedure CAN have problems with locking

    - Modifies an allotment so that a bookingline doesn't bring the number of available rooms on a given type, below 0!

    - Run-Of-House has been implemented!
*/

DECLARE
@AllotmentNumber 	Int,
@RoomTypeNumber 	Int,
@DateStamp 		Char (8),
@InitialCount 		SmallInt, -- allocatedtype. rich
@TotalChange 		SmallInt, -- 
@CurrentCount 		SmallInt, --

@DecInitialCount 	SmallInt, -- poor
@DecTotalChange 	SmallInt, --
@DecCurrentCount 	SmallInt, --

@Decrease		SMALLINT,
@SystemDateStr 	Char (8),
@FormatedStartDate 	Char (8),
@FormatedEndDate 	Char (8),
@CurrentDateStr 	Char (8),
@CurrentDate 		SMALLDateTime

-- Check if StartDate or EndDate before Systemdate -> change!
SELECT @SystemDateStr = SYSTEMDATE FROM dbo.System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record

SET @FormatedStartDate = Convert (Char (8), @StartDate, 112)
SET @FormatedEndDate = Convert (Char (8), @EndDate, 112)

if @FormatedStartDate < @SystemDateStr
  SET @FormatedStartDate = @SystemDateStr
if @FormatedEndDate < @SystemDateStr
  SET @FormatedEndDate = @SystemDateStr

SET @CurrentDateStr = @FormatedStartDate
SET @CurrentDate = Convert (DATETIME, @CurrentDateStr)

WHILE @CurrentDateStr < @FormatedEndDate -- OUTER WHILE
BEGIN
  SELECT @InitialCount = ISNULL(InitialCount, 0), @TotalChange = ISNULL(TotalChange, 0) FROM dbo.AllotmentLines WITH (NOLOCK)
  WHERE (AllotmentNumber = @OLD_AllotmentNumber) AND (RoomTypeNumber = @AllocatedTypeNumber) and (DateStamp = @CurrentDateStr)
  IF @@rowcount = 0
  BEGIN
    SET @InitialCount = 0
    SET @TotalChange = 0
  END

  SELECT @CurrentCount = dbo.GetCurrentCount(@OLD_AllotmentNumber,@AllocatedTypeNumber,@CurrentDateStr)

  WHILE (@InitialCount+@TotalChange-@CurrentCount < 0)  -- INNER WHILE		I+T-C < 0 for allocated type
  BEGIN 
/*
I+T-C=0
T=C-I
*/
    -- Decrease some other roomtype where I+T-C is greater than 0
    SELECT TOP 1 @DecInitialCount=InitialCount, @DecTotalChange=TotalChange, @RoomTypeNumber = RoomTypeNumber, @DecCurrentCount = dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp) FROM dbo.AllotmentLines WITH (NOLOCK)
    WHERE (DateStamp = @CurrentDateStr) and (AllotmentNumber = @OLD_AllotmentNumber) and (InitialCount + TotalChange - dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp) > 0)
    IF @@RowCount = 1
    BEGIN
      SET @Decrease = @DecInitialCount + @DecTotalChange - @DecCurrentCount

      IF @Decrease > - (@InitialCount + @TotalChange - @CurrentCount) -- We have more available than we need
        SET @Decrease = - (@InitialCount + @TotalChange - @CurrentCount)
      ELSE IF @Decrease < 1
        SET @Decrease = 1

      UPDATE dbo.AllotmentLines SET TotalChange = TotalChange - @Decrease -- we are taking all the available rooms from this type
      WHERE (DateStamp = @CurrentDateStr) and (AllotmentNumber = @OLD_AllotmentNumber) and (RoomTypeNumber = @RoomTypeNumber)

      UPDATE dbo.AllotmentLines SET TotalChange = TotalChange + @Decrease -- Give the rooms to the poor in true Robin Hood style
      WHERE (DateStamp = @CurrentDateStr) and (AllotmentNumber = @OLD_AllotmentNumber) and (RoomTypeNumber = @AllocatedTypeNumber)
      IF @@RowCount = 0
        INSERT INTO dbo.AllotmentLines (AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,TotalChange)
        VALUES (@OLD_AllotmentNumber, @AllocatedTypeNumber, @CurrentDateStr, 0, @CurrentCount)
    END
    ELSE -- @@rowcount = 0  we have no rich roomtypes to take from!
    BEGIN
      UPDATE dbo.AllotmentLines SET TotalChange = @CurrentCount - InitialCount  -- does it ever update since 0 rows are found above??
      WHERE (DateStamp = @CurrentDateStr) and (AllotmentNumber = @OLD_AllotmentNumber) and (RoomTypeNumber = @AllocatedTypeNumber)
      IF @@RowCount = 0
        INSERT INTO dbo.AllotmentLines (AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,TotalChange)
        VALUES (@OLD_AllotmentNumber, @AllocatedTypeNumber, @CurrentDateStr, 0, @CurrentCount)
    END

    SELECT @InitialCount = ISNULL(InitialCount, 0), @TotalChange = ISNULL(TotalChange, 0) FROM dbo.AllotmentLines WITH (NOLOCK)
    WHERE (AllotmentNumber = @OLD_AllotmentNumber) AND (RoomTypeNumber = @AllocatedTypeNumber) and (DateStamp = @CurrentDateStr)
    IF @@rowcount = 0
    BEGIN
      SET @InitialCount = 0
      SET @TotalChange = 0
    END

    SELECT @CurrentCount = dbo.GetCurrentCount(@OLD_AllotmentNumber, @AllocatedTypeNumber, @CurrentDateStr)
  END -- END INNER WHILE

  SET @CurrentDate = @CurrentDate +1
  SET @CurrentDateStr =  Convert (Char (8), @CurrentDate, 112)
END -- END OUTER WHILE

-- Modify START and END of the allotment! this code should die. we should never use redundant data!
SELECT @FormatedStartDate = MIN (DateStamp), @FormatedEndDate = MAX (DateStamp) FROM dbo.AllotmentLines WITH (NOLOCK) WHERE AllotmentNumber = @OLD_AllotmentNumber
/*
UPDATE dbo.Allotments SET
  StartDate = (SELECT MIN (DateStamp) FROM dbo.AllotmentLines AL (NOLOCK) WHERE AL.AllotmentNumber = Allotments.AllotmentNumber),
  EndDate =  (SELECT MAX (DateStamp) FROM dbo.AllotmentLines AL (NOLOCK) WHERE AL.AllotmentNumber = Allotments.AllotmentNumber)
WHERE AllotmentNumber = @OLD_AllotmentNumber
*/-- JP 20061025 OPT: Only update if dates have changed
UPDATE dbo.Allotments SET
  StartDate = @FormatedStartDate,
  EndDate =  @FormatedEndDate
WHERE AllotmentNumber = @OLD_AllotmentNumber AND (StartDate <> @FormatedStartDate OR EndDate <>  @FormatedEndDate)

