CREATE PROCEDURE dbo.CopyAllotmentDayToPeriod  @OldAllotmentNumber int, @AHotelCustomerNumber 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,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 = @FormatedSourceDate 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
 
  -- Update AllotmentLines set InitialCount = Initialcount - 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
			if @InitialCount = 0 AND @CurrentCount = 0
				Delete from Allotmentlines
				WHERE AllotmentNumber=@AllotmentNumber and DateStamp=@CurrentDate 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=@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
          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

