CREATE PROCEDURE dbo.CopyAllotmentWeekToPeriod  @OldAllotmentNumber int, @AHotelCustomerNumber 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 - 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,al.RoomTypeNumber,DateStamp,InitialCount,dbo.GetCurrentCount(AllotmentNumber,al.RoomTypeNumber,DateStamp),TotalChange,Blocked From AllotmentLines al WITH (NOLOCK)
  inner join roomtypes rt WITH (NOLOCK) on al.RoomTypeNumber = rt.RoomTypeNumber
  inner join customer c WITH (NOLOCK) on rt.HotelNumber = c.HotelNumber and c.CustomerNumber = @AHotelCustomerNumber
  Where DateStamp >= @FormatedSourceMonday and DateStamp <= @FormatedSourceSunday and AllotmentNumber = @OldAllotmentNumber
 -- and   ((initialcount+totalChange-dbo.GetCurrentCount(AllotmentNumber,al.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)
			 if @InitialCount = 0 and @CurrentCount = 0
				delete from allotmentlines
				Where AllotmentNumber=@AllotmentNumber and DateStamp=@InsertDate and RoomTypeNumber=@RoomTypeNumber
			 else
			 begin
               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
          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

