CREATE PROCEDURE dbo.CopyAllotmentWeekToPeriod  @OldAllotmentNumber int, @SourceMonday 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 @FormatedSourceMonday Char (8)
Declare @FormatedSourceSunday Char (8)
Declare @FormatedSourceDate Char (8)

Declare @FormatedDestStartDate Char (8)

Declare @OrgDestStartDate datetime

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

Declare @OffSet DateTime
Declare @InsertDate Char (8)

select  @FormatedSourceMonday =  Convert (Char (8), @SourceMonday, 112)
select  @FormatedSourceSunday =  Convert (Char (8), @SourceMonday+6, 112)
select  @FormatedDestStartDate =  Convert (Char (8), @DestStartDate, 112)
Select @OrgDestStartDate= @DestStartDate --used to store the original startdate because the DestStartDate variable is increased in the while loop
Select  @CurrentDate =  Convert (Char (8), @DestStartDate, 112)
Select  @EndDate =  Convert (Char (8), @DestEndDate, 112)
/*
Update AllotmentLines set TotalChange = TotalChange - (Initialcount + TotalChange - CurrentCount), Blocked = 0
Where AllotmentNumber = @OldAllotmentnumber and datestamp >= @CurrentDate and DateStamp <= @EndDate
*/
Update AllotmentLines set TotalChange = TotalChange - (Initialcount + TotalChange - dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp)), Blocked = 0
Where AllotmentNumber = @OldAllotmentnumber and datestamp >= @CurrentDate and DateStamp <= @EndDate

-- Declare and allocate Cursor If there is 10 roomtypes then 70 records in the cursor dataset
Declare CopyAllotment Cursor For
/*
  Select AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,CurrentCount,TotalChange,Blocked From AllotmentLines (NOLOCK)
  Where DateStamp >= @FormatedSourceMonday and DateStamp <= @FormatedSourceSunday and AllotmentNumber = @OldAllotmentNumber
  and   ((initialcount+totalChange-currentCount > 0) or (Blocked = 1) )
*/
  Select AllotmentNumber,RoomTypeNumber,DateStamp,InitialCount,dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp),TotalChange,Blocked From AllotmentLines WITH (NOLOCK)
  Where DateStamp >= @FormatedSourceMonday and DateStamp <= @FormatedSourceSunday 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

   -- 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 < @FormatedSourceMonday) or (@CurrentDate > @FormatedSourceSunday)  -- Do NOT insert on sourcedate
          Begin
             Set @Offset = Convert (DateTime, @DateStamp) - Convert (DateTime, @SourceMonday)
             Set @InsertDate =  Convert (Char (10), @DestStartDate+@OffSet, 112)
/*
             Update AllotmentLines set TotalChange = (@InitialCount+@TotalChange-@CurrentCount) - InitialCount + CurrentCount, Blocked = @Blocked
             Where AllotmentNumber=@AllotmentNumber and DateStamp=@InsertDate and RoomTypeNumber=@RoomTypeNumber
*/
             Update AllotmentLines set TotalChange = (@InitialCount+@TotalChange-@CurrentCount) - InitialCount + dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp), Blocked = @Blocked
             Where AllotmentNumber=@AllotmentNumber and DateStamp=@InsertDate and RoomTypeNumber=@RoomTypeNumber

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

        Set @DestStartDate = @DestStartDate +7
        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

