CREATE PROCEDURE dbo.CopyAllotmentDayToPeriod  @OldAllotmentNumber 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 (8)
Declare @FormatedDestStartDate Char (8)

Declare @OrgDestStartDate smalldatetime

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

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

-- Declare and allocate Cursor If there is 10 roomtypes then 10 records in the cursor dataset
Declare CopyAllotment Cursor For
  Select AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp),TotalChange,Blocked From AllotmentLines WITH (NOLOCK)
  Where DateStamp = @FormatedSourceDate and AllotmentNumber = @OldAllotmentNumber 
  and ( (initialcount+totalChange-dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp) > 0) or (blocked = 1))

-- Loop from startdate to enddate
    Open CopyAllotment
    Fetch Next From CopyAllotment
    Into @AllotmentNumber, @RoomtypeNumber, @DateStamp, @initialCount, @CurrentCount, @TotalChange,@Blocked
 
   Update AllotmentLines set TotalChange = TotalChange - (Initialcount + TotalChange - dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp))
   Where AllotmentNumber = @OldAllotmentnumber and DateStamp BETWEEN @CurrentDate AND @EndDate

   -- 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
             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)

            Update AllotmentLines set TotalChange = TotalChange - (Initialcount + TotalChange - dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp))
            Where AllotmentNumber = @OldAllotmentnumber and datestamp = @CurrentDate and RoomTypeNumber not in
              (Select RoomTypeNumber From AllotmentLines WITH (NOLOCK) Where DateStamp = @FormatedSourceDate and AllotmentNumber = @OldAllotmentNumber and  initialcount+totalChange-dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp) > 0)
          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

