CREATE PROCEDURE dbo.CopyAllotmentRoomTypeToPeriod  @OldAllotmentNumber int, @OldRoomTypeNumber int,  @SourceDate datetime, @DestStartDate datetime, @DestEndDate datetime AS
SET NOCOUNT ON
/*
    Last Changed:
    20050613 JP: use dbo.GetCurrentCount function
*/
-- Declare all fields in AllotmentLines
Declare @AllotmentNumber Int
Declare @DateStamp Char (8)
Declare @RoomtypeNumber int
Declare @InitialCount Int
Declare @TotalChange Int
Declare @CurrentCount Int
Declare @Blocked int

Declare @FormatedSourceDate Char (10)
Declare @FormatedDestStartDate Char (10)

Declare @OrgDestStartDate datetime

Declare @CurrentDate Char (8)
Declare @EndDate Char (8)

select  @FormatedSourceDate =  Convert (Char (10), @SourceDate, 112)
select  @FormatedDestStartDate =  Convert (Char (10), @DestStartDate, 112)
Select  @CurrentDate =  Convert (Char (10), @DestStartDate, 112)
Select  @EndDate =  Convert (Char (10), @DestEndDate, 112)
Select @OrgDestStartDate= @DestStartDate

-- Declare and allocate Cursor ,Only 1 record in the cursor
Declare CopyAllotment Cursor For
/*
  Select AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,CurrentCount,TotalChange,Blocked From AllotmentLines (NOLOCK)
  Where DateStamp = @FormatedSourceDate and AllotmentNumber = @OldAllotmentNumber and RoomTypeNumber=@OldRoomTypeNumber
*/
  Select AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp),TotalChange,Blocked From AllotmentLines WITH (NOLOCK)
  Where DateStamp = @FormatedSourceDate and AllotmentNumber = @OldAllotmentNumber and RoomTypeNumber=@OldRoomTypeNumber

-- Loop from startdate to enddate
    Open CopyAllotment
    Fetch Next From CopyAllotment
    Into @AllotmentNumber, @RoomtypeNumber, @DateStamp, @initialCount, @CurrentCount, @TotalChange,@Blocked

   -- Loop through records of sourcedate
    While  (@@FETCH_STATUS = 0)
    Begin
      if @Blocked is null
        select @Blocked = 0 
      -- Insert with NEW date OR Update If Record Exists
      While @CurrentDate <= @EndDate
      Begin
          If @CurrentDate <> @FormatedSourceDate -- Do NOT insert on sourcedate
          Begin
/*
             Update AllotmentLines set TotalChange = (@InitialCount+@TotalChange-@CurrentCount) - InitialCount + CurrentCount, Blocked = @Blocked  -- initialcount  --(InitialCount + TotalChange -CurrentCount)
             Where AllotmentNumber=@AllotmentNumber and DateStamp=@CurrentDate and RoomTypeNumber=@RoomTypeNumber
*/
             Update AllotmentLines set TotalChange = (@InitialCount+@TotalChange-@CurrentCount) - InitialCount + dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp), Blocked = @Blocked
             Where AllotmentNumber=@AllotmentNumber and DateStamp=@CurrentDate and RoomTypeNumber=@RoomTypeNumber

             if @@rowcount = 0
               Insert Into AllotmentLines (AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,TotalChange,Blocked) Values (@AllotmentNumber,@RoomtypeNumber,@CurrentDate, @initialCount + @TotalChange- @CurrentCount,0,@Blocked)
          End

        Set @DestStartDate = @DestStartDate +1
        Select  @CurrentDate =  Convert (Char (8), @DestStartDate, 112)
    end
  Fetch Next From CopyAllotment  Into @AllotMentNumber,@RoomtypeNumber,@DateStamp, @initialCount, @CurrentCount, @TotalChange,@Blocked
  Select @CurrentDate =  Convert (Char (8), @FormatedDestStartDate, 112)
  Set @DestStartDate = @OrgDestStartDate
end 

Close CopyAllotment
Deallocate CopyAllotment

