CREATE PROCEDURE DBO.CopyWeekdayToWeekdays @AProductNumber int, @ASourceDay datetime, @ADayType INT, @ADestStartDate datetime, @ADestEndDate datetime AS
SET NOCOUNT ON
/*

 Last changed:  
 20240223: Removed ArrivalPacketNumber, StayPacketNumber and DeparturePacketNumber from ProductPrice.
 20160601 RN: Initial version
*/
BEGIN
  DECLARE @DateFirst INT
  DECLARE @PacketNumber INT
  DECLARE @StoreDestStartDate DATETIME
  DECLARE @StoreDestEndDate DATETIME
  DECLARE @FormatedSourceDate CHAR(8)

  DECLARE @FK_ProductNumberDiscounted INT

  SET @StoreDestStartDate = @ADestStartDate
  SET @StoreDestEndDate = @ADestEndDate

  SET @FormatedSourceDate = Convert (CHAR(8), @ASourceDay, 112)

  SELECT @DateFirst = @@DATEFIRST
  SET DATEFIRST 1

  SELECT *
  INTO #SourceTable
  FROM ProductPrice WITH (NOLOCK)
  WHERE ProductNumber = @AProductNumber AND DateStamp = @FormatedSourceDate

  UPDATE ProductPrice
  SET 
    AdultPrice1 = #SourceTable.AdultPrice1,
    AdultPrice2 = #SourceTable.AdultPrice2,
    AdultPrice3 = #SourceTable.AdultPrice3,
    AdultPrice4 = #SourceTable.AdultPrice4,
    AdultPrice5 = #SourceTable.AdultPrice5,
    AdultPrice6 = #SourceTable.AdultPrice6,
    AdultPrice7 = #SourceTable.AdultPrice7,
    AdultPrice8 = #SourceTable.AdultPrice8,
    AdultPrice9 = #SourceTable.AdultPrice9,
	  
    Children1Price1 = #SourceTable.Children1Price1,
    Children1Price2 = #SourceTable.Children1Price2,
    Children1Price3 = #SourceTable.Children1Price3,
    Children1Price4 = #SourceTable.Children1Price4,
    Children1Price5 = #SourceTable.Children1Price5,
    Children1Price6 = #SourceTable.Children1Price6,
    Children1Price7 = #SourceTable.Children1Price7,
    Children1Price8 = #SourceTable.Children1Price8,
    Children1Price9 = #SourceTable.Children1Price9,

    Children2Price1 = #SourceTable.Children2Price1,
    Children2Price2 = #SourceTable.Children2Price2,
    Children2Price3 = #SourceTable.Children2Price3,
    Children2Price4 = #SourceTable.Children2Price4,
    Children2Price5 = #SourceTable.Children2Price5,
    Children2Price6 = #SourceTable.Children2Price6,
    Children2Price7 = #SourceTable.Children2Price7,
    Children2Price8 = #SourceTable.Children2Price8,
    Children2Price9 = #SourceTable.Children2Price9,

	AdultArrivalAddon = #SourceTable.AdultArrivalAddon,
	AdultArrivalAddon2 = #SourceTable.AdultArrivalAddon2,
	AdultArrivalAddon3 = #SourceTable.AdultArrivalAddon3,
	AdultArrivalAddon4 = #SourceTable.AdultArrivalAddon4,
	AdultArrivalAddon5 = #SourceTable.AdultArrivalAddon5,
	AdultArrivalAddon6 = #SourceTable.AdultArrivalAddon6,
	AdultArrivalAddon7 = #SourceTable.AdultArrivalAddon7,
	AdultArrivalAddon8 = #SourceTable.AdultArrivalAddon8,
	AdultArrivalAddon9 = #SourceTable.AdultArrivalAddon9,

	AdultStayAddon = #SourceTable.AdultStayAddon,
	AdultStayAddon2 = #SourceTable.AdultStayAddon2,
	AdultStayAddon3 = #SourceTable.AdultStayAddon3,
	AdultStayAddon4 = #SourceTable.AdultStayAddon4,
	AdultStayAddon5 = #SourceTable.AdultStayAddon5,
	AdultStayAddon6 = #SourceTable.AdultStayAddon6,
	AdultStayAddon7 = #SourceTable.AdultStayAddon7,
	AdultStayAddon8 = #SourceTable.AdultStayAddon8,
	AdultStayAddon9 = #SourceTable.AdultStayAddon9,

	AdultDepartureAddon = #SourceTable.AdultDepartureAddon,
	AdultDepartureAddon2 = #SourceTable.AdultDepartureAddon2,
	AdultDepartureAddon3 = #SourceTable.AdultDepartureAddon3,
	AdultDepartureAddon4 = #SourceTable.AdultDepartureAddon4,
	AdultDepartureAddon5 = #SourceTable.AdultDepartureAddon5,
	AdultDepartureAddon6 = #SourceTable.AdultDepartureAddon6,
	AdultDepartureAddon7 = #SourceTable.AdultDepartureAddon7,
	AdultDepartureAddon8 = #SourceTable.AdultDepartureAddon8,
	AdultDepartureAddon9 = #SourceTable.AdultDepartureAddon9,

	Children1ArrivalAddOn = #SourceTable.Children1ArrivalAddon,
	Children1ArrivalAddon2 = #SourceTable.Children1ArrivalAddon2,
	Children1ArrivalAddon3 = #SourceTable.Children1ArrivalAddon3,
	Children1ArrivalAddon4 = #SourceTable.Children1ArrivalAddon4,
	Children1ArrivalAddon5 = #SourceTable.Children1ArrivalAddon5,
	Children1ArrivalAddon6 = #SourceTable.Children1ArrivalAddon6,
	Children1ArrivalAddon7 = #SourceTable.Children1ArrivalAddon7,
	Children1ArrivalAddon8 = #SourceTable.Children1ArrivalAddon8,
	Children1ArrivalAddon9 = #SourceTable.Children1ArrivalAddon9,

	Children1StayAddon = #SourceTable.Children1StayAddon,
	Children1StayAddon2 = #SourceTable.Children1StayAddon2,
	Children1StayAddon3 = #SourceTable.Children1StayAddon3,
	Children1StayAddon4 = #SourceTable.Children1StayAddon4,
	Children1StayAddon5 = #SourceTable.Children1StayAddon5,
	Children1StayAddon6 = #SourceTable.Children1StayAddon6,
	Children1StayAddon7 = #SourceTable.Children1StayAddon7,
	Children1StayAddon8 = #SourceTable.Children1StayAddon8,
	Children1StayAddon9 = #SourceTable.Children1StayAddon9,

	Children1DepartureAddon = #SourceTable.Children1DepartureAddon,
	Children1DepartureAddon2 = #SourceTable.Children1DepartureAddon2,
	Children1DepartureAddon3 = #SourceTable.Children1DepartureAddon3,
	Children1DepartureAddon4 = #SourceTable.Children1DepartureAddon4,
	Children1DepartureAddon5 = #SourceTable.Children1DepartureAddon5,
	Children1DepartureAddon6 = #SourceTable.Children1DepartureAddon6,
	Children1DepartureAddon7 = #SourceTable.Children1DepartureAddon7,
	Children1DepartureAddon8 = #SourceTable.Children1DepartureAddon8,
	Children1DepartureAddon9 = #SourceTable.Children1DepartureAddon9,

	Children2ArrivalAddOn = #SourceTable.Children2ArrivalAddon,
	Children2ArrivalAddon2 = #SourceTable.Children2ArrivalAddon2,
	Children2ArrivalAddon3 = #SourceTable.Children2ArrivalAddon3,
	Children2ArrivalAddon4 = #SourceTable.Children2ArrivalAddon4,
	Children2ArrivalAddon5 = #SourceTable.Children2ArrivalAddon5,
	Children2ArrivalAddon6 = #SourceTable.Children2ArrivalAddon6,
	Children2ArrivalAddon7 = #SourceTable.Children2ArrivalAddon7,
	Children2ArrivalAddon8 = #SourceTable.Children2ArrivalAddon8,
	Children2ArrivalAddon9 = #SourceTable.Children2ArrivalAddon9,

	Children2StayAddon = #SourceTable.Children2StayAddon,
	Children2StayAddon2 = #SourceTable.Children2StayAddon2,
	Children2StayAddon3 = #SourceTable.Children2StayAddon3,
	Children2StayAddon4 = #SourceTable.Children2StayAddon4,
	Children2StayAddon5 = #SourceTable.Children2StayAddon5,
	Children2StayAddon6 = #SourceTable.Children2StayAddon6,
	Children2StayAddon7 = #SourceTable.Children2StayAddon7,
	Children2StayAddon8 = #SourceTable.Children2StayAddon8,
	Children2StayAddon9 = #SourceTable.Children2StayAddon9,

	Children2DepartureAddon = #SourceTable.Children2DepartureAddon,
	Children2DepartureAddon2 = #SourceTable.Children2DepartureAddon2,
	Children2DepartureAddon3 = #SourceTable.Children2DepartureAddon3,
	Children2DepartureAddon4 = #SourceTable.Children2DepartureAddon4,
	Children2DepartureAddon5 = #SourceTable.Children2DepartureAddon5,
	Children2DepartureAddon6 = #SourceTable.Children2DepartureAddon6,
	Children2DepartureAddon7 = #SourceTable.Children2DepartureAddon7,
	Children2DepartureAddon8 = #SourceTable.Children2DepartureAddon8,
	Children2DepartureAddon9 = #SourceTable.Children2DepartureAddon9,
	  
	RoomtypeNumber = #SourceTable.RoomtypeNumber
	FROM #SourceTable
  WHERE ProductPrice.ProductNumber = @AProductNumber
    AND ProductPrice.RoomtypeNumber = #SourceTable.RoomtypeNumber
    AND ProductPrice.Datestamp >= CONVERT(CHAR(8), @ADestStartDate, 112)
    AND ProductPrice.DateStamp <= CONVERT(CHAR(8), @ADestEndDate, 112)
    AND (@ADayType = 0 OR POWER(2, DATEPART(dw, ProductPrice.DateStamp) - 1) & @ADayType > 0)
      --' + CAST(@DayType AS VARCHAR) + ' = ''0'' OR POWER(2, DATEPART(dw, DateStamp)-1) & ' + CAST(@DayType AS VARCHAR) + ' > 0)'

  
  DECLARE @PackageType INT

  SET @PackageType = 0 
  WHILE @PackageType <= 2
  BEGIN
    SET @PacketNumber = -1

    SELECT TOP 1 @PacketNumber = FK_PacketNumber
    FROM ProductPricePacket WITH (NOLOCK)
    WHERE StartDate >= @FormatedSourceDate 
      AND EndDate <= @FormatedSourceDate 
      AND FK_ProductNumber = @AProductNumber 
      AND PacketType = @PackageType
    ORDER BY Id DESC
 
    IF ISNULL(@PacketNumber, 0) > 0
      INSERT INTO ProductPricePacket (FK_ProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber) 
        VALUES (@AProductNumber, @PackageType, @StoreDestStartDate, @StoreDestEndDate, @PacketNumber)
 
    SET @PackageType = @PackageType + 1  
  END  

  DECLARE PacketCur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT FK_ProductNumberDiscounted
    FROM ProductProductDiscountXTab WITH (NOLOCK)
    WHERE FK_ProductNumber = @AProductNumber

  OPEN PacketCur
  FETCH NEXT FROM PacketCur INTO @FK_ProductNumberDiscounted
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SET @PackageType = 0 
    WHILE @PackageType <= 2
    BEGIN
      SET @PacketNumber = -1

      SELECT TOP 1 @PacketNumber = FK_PacketNumber
      FROM ProductPricePacket WITH (NOLOCK)
      WHERE StartDate <= @FormatedSourceDate 
        AND EndDate >= @FormatedSourceDate 
        AND FK_ProductNumber = @FK_ProductNumberDiscounted 
        AND PacketType = @PackageType
      ORDER BY Id DESC
 
      IF ISNULL(@FK_ProductNumberDiscounted, 0) > 0
        INSERT INTO ProductPricePacket (FK_ProductNumber, PacketType, StartDate, EndDate, FK_PacketNumber) VALUES (@FK_ProductNumberDiscounted, @PackageType, @StoreDestStartDate, @StoreDestEndDate, ISNULL(@PacketNumber, 0))
 
      SET @PackageType = @PackageType + 1  
    END

	UPDATE Products
    SET ProductEndDate = Convert(CHAR(8), @ADestEndDate, 112)
    WHERE ProductNumber = @FK_ProductNumberDiscounted

    FETCH NEXT FROM PacketCur INTO @FK_ProductNumberDiscounted
  END
  CLOSE PacketCur
  DEALLOCATE PacketCur
  
  SET DATEFIRST @DateFirst
END
