SET NOCOUNT ON

-- Cursor paramters
DECLARE @ProductNumber INT
DECLARE @DateStamp DATETIME
DECLARE @RoomtypeNumber INT
DECLARE @ArrivalPacketNumber INT
DECLARE @StayPacketNumber INT
DECLARE @DeparturePacketNumber INT
DECLARE @AdultPrice1 SMALLMONEY
DECLARE @AdultPrice2 SMALLMONEY
DECLARE @AdultPrice3 SMALLMONEY
DECLARE @AdultPrice4 SMALLMONEY
DECLARE @AdultPrice5 SMALLMONEY
DECLARE @AdultPrice6 SMALLMONEY
DECLARE @AdultPrice7 SMALLMONEY
DECLARE @AdultPrice8 SMALLMONEY
DECLARE @AdultPrice9 SMALLMONEY
DECLARE @Children1Price1 SMALLMONEY
DECLARE @Children1Price2 SMALLMONEY
DECLARE @Children1Price3 SMALLMONEY
DECLARE @Children1Price4 SMALLMONEY
DECLARE @Children1Price5 SMALLMONEY
DECLARE @Children1Price6 SMALLMONEY
DECLARE @Children1Price7 SMALLMONEY
DECLARE @Children1Price8 SMALLMONEY
DECLARE @Children1Price9 SMALLMONEY
DECLARE @Children2Price1 SMALLMONEY
DECLARE @Children2Price2 SMALLMONEY
DECLARE @Children2Price3 SMALLMONEY
DECLARE @Children2Price4 SMALLMONEY
DECLARE @Children2Price5 SMALLMONEY
DECLARE @Children2Price6 SMALLMONEY
DECLARE @Children2Price7 SMALLMONEY
DECLARE @Children2Price8 SMALLMONEY
DECLARE @Children2Price9 SMALLMONEY
DECLARE @AdultArrivalAddon SMALLMONEY
DECLARE @AdultArrivalAddon2 SMALLMONEY
DECLARE @AdultArrivalAddon3 SMALLMONEY
DECLARE @AdultArrivalAddon4 SMALLMONEY
DECLARE @AdultArrivalAddon5 SMALLMONEY
DECLARE @AdultArrivalAddon6 SMALLMONEY
DECLARE @AdultArrivalAddon7 SMALLMONEY
DECLARE @AdultArrivalAddon8 SMALLMONEY
DECLARE @AdultArrivalAddon9 SMALLMONEY
DECLARE @AdultStayAddon SMALLMONEY
DECLARE @AdultStayAddon2 SMALLMONEY
DECLARE @AdultStayAddon3 SMALLMONEY
DECLARE @AdultStayAddon4 SMALLMONEY
DECLARE @AdultStayAddon5 SMALLMONEY
DECLARE @AdultStayAddon6 SMALLMONEY
DECLARE @AdultStayAddon7 SMALLMONEY
DECLARE @AdultStayAddon8 SMALLMONEY
DECLARE @AdultStayAddon9 SMALLMONEY
DECLARE @AdultDepartureAddon SMALLMONEY
DECLARE @AdultDepartureAddon2 SMALLMONEY
DECLARE @AdultDepartureAddon3 SMALLMONEY
DECLARE @AdultDepartureAddon4 SMALLMONEY
DECLARE @AdultDepartureAddon5 SMALLMONEY
DECLARE @AdultDepartureAddon6 SMALLMONEY
DECLARE @AdultDepartureAddon7 SMALLMONEY
DECLARE @AdultDepartureAddon8 SMALLMONEY
DECLARE @AdultDepartureAddon9 SMALLMONEY
DECLARE @Children1ArrivalAddon SMALLMONEY
DECLARE @Children1ArrivalAddon2 SMALLMONEY
DECLARE @Children1ArrivalAddon3 SMALLMONEY
DECLARE @Children1ArrivalAddon4 SMALLMONEY
DECLARE @Children1ArrivalAddon5 SMALLMONEY
DECLARE @Children1ArrivalAddon6 SMALLMONEY
DECLARE @Children1ArrivalAddon7 SMALLMONEY
DECLARE @Children1ArrivalAddon8 SMALLMONEY
DECLARE @Children1ArrivalAddon9 SMALLMONEY
DECLARE @Children1StayAddon SMALLMONEY
DECLARE @Children1StayAddon2 SMALLMONEY
DECLARE @Children1StayAddon3 SMALLMONEY
DECLARE @Children1StayAddon4 SMALLMONEY
DECLARE @Children1StayAddon5 SMALLMONEY
DECLARE @Children1StayAddon6 SMALLMONEY
DECLARE @Children1StayAddon7 SMALLMONEY
DECLARE @Children1StayAddon8 SMALLMONEY
DECLARE @Children1StayAddon9 SMALLMONEY
DECLARE @Children1DepartureAddon SMALLMONEY
DECLARE @Children1DepartureAddon2 SMALLMONEY
DECLARE @Children1DepartureAddon3 SMALLMONEY
DECLARE @Children1DepartureAddon4 SMALLMONEY
DECLARE @Children1DepartureAddon5 SMALLMONEY
DECLARE @Children1DepartureAddon6 SMALLMONEY
DECLARE @Children1DepartureAddon7 SMALLMONEY
DECLARE @Children1DepartureAddon8 SMALLMONEY
DECLARE @Children1DepartureAddon9 SMALLMONEY
DECLARE @Children2ArrivalAddon SMALLMONEY
DECLARE @Children2ArrivalAddon2 SMALLMONEY
DECLARE @Children2ArrivalAddon3 SMALLMONEY
DECLARE @Children2ArrivalAddon4 SMALLMONEY
DECLARE @Children2ArrivalAddon5 SMALLMONEY
DECLARE @Children2ArrivalAddon6 SMALLMONEY
DECLARE @Children2ArrivalAddon7 SMALLMONEY
DECLARE @Children2ArrivalAddon8 SMALLMONEY
DECLARE @Children2ArrivalAddon9 SMALLMONEY
DECLARE @Children2StayAddon SMALLMONEY
DECLARE @Children2StayAddon2 SMALLMONEY
DECLARE @Children2StayAddon3 SMALLMONEY
DECLARE @Children2StayAddon4 SMALLMONEY
DECLARE @Children2StayAddon5 SMALLMONEY
DECLARE @Children2StayAddon6 SMALLMONEY
DECLARE @Children2StayAddon7 SMALLMONEY
DECLARE @Children2StayAddon8 SMALLMONEY
DECLARE @Children2StayAddon9 SMALLMONEY
DECLARE @Children2DepartureAddon SMALLMONEY
DECLARE @Children2DepartureAddon2 SMALLMONEY
DECLARE @Children2DepartureAddon3 SMALLMONEY
DECLARE @Children2DepartureAddon4 SMALLMONEY
DECLARE @Children2DepartureAddon5 SMALLMONEY
DECLARE @Children2DepartureAddon6 SMALLMONEY
DECLARE @Children2DepartureAddon7 SMALLMONEY
DECLARE @Children2DepartureAddon8 SMALLMONEY
DECLARE @Children2DepartureAddon9 SMALLMONEY

-- Default values params
DECLARE @CurrentProductNumber INT
DECLARE @CurrentDateTime DATETIME
DECLARE @CurrentRoomTypeNumber INT
DECLARE @CurrentArrivalPacketNumber INT
DECLARE @CurrentStayPacketNumber INT
DECLARE @CurrentDeparturePacketNumber INT

SET @CurrentProductNumber = -1
SET @CurrentDateTime = 0
SET @CurrentRoomTypeNumber = -1
SET @CurrentArrivalPacketNumber = -1
SET @CurrentStayPacketNumber = -1
SET @CurrentDeparturePacketNumber = -1

-- Other
DECLARE @LastPackageEndDate DATETIME
DECLARE @ProductPriceBaseId INT
DECLARE @LastInsertedArrPacketId INT
DECLARE @LastInsertedStayPacketId INT
DECLARE @LastInsertedDepPacketId INT

SET @LastInsertedArrPacketId = 0
SET @LastInsertedStayPacketId = 0
SET @LastInsertedDepPacketId = 0

-- Packet
DECLARE db_cur CURSOR FOR
  SELECT DISTINCT ProductNumber, CONVERT(DATETIME, DateStamp, 112), MAX(ISNULL(ArrivalPacketNumber, 0)), MAX(ISNULL(StayPacketNumber, 0)), MAX(ISNULL(DeparturePacketNumber, 0))
  FROM ProductPrice WITH (NOLOCK)
  WHERE ISNULL(ArrivalPacketNumber, 0) + ISNULL(StayPacketNumber, 0) + ISNULL(DeparturePacketNumber, 0) > 0
--  AND ProductNumber = 15
  GROUP BY ProductNumber, CONVERT(DATETIME, DateStamp, 112)
  ORDER BY ProductNumber, CONVERT(DATETIME, DateStamp, 112)
OPEN db_cur

FETCH NEXT FROM db_cur INTO @ProductNumber, @DateStamp, @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @ArrivalPacketNumber > 0
    EXEC ProductPriceConvert 0, @CurrentArrivalPacketNumber, @ArrivalPacketNumber, @CurrentProductNumber, @ProductNumber, @DateStamp, @CurrentDateTime, @LastInsertedArrPacketId OUTPUT
  IF @StayPacketNumber > 0
    EXEC ProductPriceConvert 1, @CurrentStayPacketNumber, @StayPacketNumber, @CurrentProductNumber, @ProductNumber, @DateStamp, @CurrentDateTime, @LastInsertedStayPacketId OUTPUT
  IF @DeparturePacketNumber > 0  
    EXEC ProductPriceConvert 2, @CurrentDeparturePacketNumber, @DeparturePacketNumber, @CurrentProductNumber, @ProductNumber, @DateStamp, @CurrentDateTime, @LastInsertedDepPacketId OUTPUT
  
  -- Set current values....
  SET @CurrentProductNumber = @ProductNumber
  SET @CurrentDateTime = @DateStamp
  SET @CurrentArrivalPacketNumber = @ArrivalPacketNumber
  SET @CurrentStayPacketNumber = @StayPacketNumber
  SET @CurrentDeparturePacketNumber = @DeparturePacketNumber

  FETCH NEXT FROM db_cur INTO @ProductNumber, @DateStamp, @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber
END

CLOSE db_cur
DEALLOCATE db_cur

/*
-- Prices
DECLARE db_curprice CURSOR FOR
  SELECT ProductNumber, CONVERT(DATETIME, DateStamp, 112), ISNULL(RoomtypeNumber, 0),
  ISNULL(AdultPrice1, 0), ISNULL(AdultPrice2, 0), ISNULL(AdultPrice3, 0), ISNULL(AdultPrice4, 0), ISNULL(AdultPrice5, 0), ISNULL(AdultPrice6, 0), ISNULL(AdultPrice7, 0), ISNULL(AdultPrice8, 0), ISNULL(AdultPrice9, 0), 
  Children1Price1, Children1Price2, Children1Price3, Children1Price4, Children1Price5, Children1Price6, Children1Price7, Children1Price8, Children1Price9, 
  Children2Price1, Children2Price2, Children2Price3, Children2Price4, Children2Price5, Children2Price6, Children2Price7, Children2Price8, Children2Price9, 
  AdultArrivalAddon, AdultArrivalAddon2, AdultArrivalAddon3, AdultArrivalAddon4, AdultArrivalAddon5, AdultArrivalAddon6, AdultArrivalAddon7, AdultArrivalAddon8, AdultArrivalAddon9, 
  AdultStayAddon, AdultStayAddon2, AdultStayAddon3, AdultStayAddon4, AdultStayAddon5, AdultStayAddon6, AdultStayAddon7, AdultStayAddon8, AdultStayAddon9, 
  AdultDepartureAddon, AdultDepartureAddon2, AdultDepartureAddon3, AdultDepartureAddon4, AdultDepartureAddon5, AdultDepartureAddon6, AdultDepartureAddon7, AdultDepartureAddon8, AdultDepartureAddon9, 
  Children1ArrivalAddon, Children1ArrivalAddon2, Children1ArrivalAddon3, Children1ArrivalAddon4, Children1ArrivalAddon5, Children1ArrivalAddon6, Children1ArrivalAddon7, Children1ArrivalAddon8, Children1ArrivalAddon9, 
  Children1StayAddon, Children1StayAddon2, Children1StayAddon3, Children1StayAddon4, Children1StayAddon5, Children1StayAddon6, Children1StayAddon7, Children1StayAddon8, Children1StayAddon9, 
  Children1DepartureAddon, Children1DepartureAddon2, Children1DepartureAddon3, Children1DepartureAddon4, Children1DepartureAddon5, Children1DepartureAddon6, Children1DepartureAddon7, Children1DepartureAddon8, Children1DepartureAddon9, 
  Children2ArrivalAddon, Children2ArrivalAddon2, Children2ArrivalAddon3, Children2ArrivalAddon4, Children2ArrivalAddon5, Children2ArrivalAddon6, Children2ArrivalAddon7, Children2ArrivalAddon8, Children2ArrivalAddon9, 
  Children2StayAddon, Children2StayAddon2, Children2StayAddon3, Children2StayAddon4, Children2StayAddon5, Children2StayAddon6, Children2StayAddon7, Children2StayAddon8, Children2StayAddon9, 
  Children2DepartureAddon, Children2DepartureAddon2, Children2DepartureAddon3, Children2DepartureAddon4, Children2DepartureAddon5, Children2DepartureAddon6, Children2DepartureAddon7, Children2DepartureAddon8, Children2DepartureAddon9 
  FROM ProductPrice WITH (NOLOCK)
--  where ProductNumber = 451
  ORDER BY ProductNumber, RoomTypeNumber, DateStamp
OPEN db_curprice

FETCH NEXT FROM db_curprice INTO @ProductNumber, @DateStamp, @RoomtypeNumber,
  @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9, 
  @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9, 
  @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9, 
  @AdultArrivalAddon, @AdultArrivalAddon2, @AdultArrivalAddon3, @AdultArrivalAddon4, @AdultArrivalAddon5, @AdultArrivalAddon6, @AdultArrivalAddon7, @AdultArrivalAddon8, @AdultArrivalAddon9, 
  @AdultStayAddon, @AdultStayAddon2, @AdultStayAddon3, @AdultStayAddon4, @AdultStayAddon5, @AdultStayAddon6, @AdultStayAddon7, @AdultStayAddon8, @AdultStayAddon9, 
  @AdultDepartureAddon, @AdultDepartureAddon2, @AdultDepartureAddon3, @AdultDepartureAddon4, @AdultDepartureAddon5, @AdultDepartureAddon6, @AdultDepartureAddon7, @AdultDepartureAddon8, @AdultDepartureAddon9, 
  @Children1ArrivalAddon, @Children1ArrivalAddon2, @Children1ArrivalAddon3, @Children1ArrivalAddon4, @Children1ArrivalAddon5, @Children1ArrivalAddon6, @Children1ArrivalAddon7, @Children1ArrivalAddon8, @Children1ArrivalAddon9, 
  @Children1StayAddon, @Children1StayAddon2, @Children1StayAddon3, @Children1StayAddon4, @Children1StayAddon5, @Children1StayAddon6, @Children1StayAddon7, @Children1StayAddon8, @Children1StayAddon9, 
  @Children1DepartureAddon, @Children1DepartureAddon2, @Children1DepartureAddon3, @Children1DepartureAddon4, @Children1DepartureAddon5, @Children1DepartureAddon6, @Children1DepartureAddon7, @Children1DepartureAddon8, @Children1DepartureAddon9, 
  @Children2ArrivalAddon, @Children2ArrivalAddon2, @Children2ArrivalAddon3, @Children2ArrivalAddon4, @Children2ArrivalAddon5, @Children2ArrivalAddon6, @Children2ArrivalAddon7, @Children2ArrivalAddon8, @Children2ArrivalAddon9, 
  @Children2StayAddon, @Children2StayAddon2, @Children2StayAddon3, @Children2StayAddon4, @Children2StayAddon5, @Children2StayAddon6, @Children2StayAddon7, @Children2StayAddon8, @Children2StayAddon9, 
  @Children2DepartureAddon, @Children2DepartureAddon2, @Children2DepartureAddon3, @Children2DepartureAddon4, @Children2DepartureAddon5, @Children2DepartureAddon6, @Children2DepartureAddon7, @Children2DepartureAddon8, @Children2DepartureAddon9

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Handle base table
  IF NOT EXISTS(SELECT * FROM ProductPriceBase WITH (NOLOCK) WHERE FK_RoomHallTypeNumber = @RoomtypeNumber AND FK_ProductNumber = @ProductNumber)
  BEGIN
    EXEC GetNextNumber 'PRODUCTPRICEBASEIDENTITY', 1, @ProductPriceBaseId OUTPUT
    INSERT INTO ProductPriceBase (ProductPriceBaseId, FK_RoomHallTypeNumber, FK_ProductNumber) VALUES (@ProductPriceBaseId, @RoomtypeNumber, @ProductNumber)
  END

  -- Set current values....
  SET @CurrentProductNumber = @ProductNumber
  SET @CurrentDateTime = @DateStamp
  SET @CurrentRoomTypeNumber = @RoomTypeNumber

  FETCH NEXT FROM db_curprice INTO @ProductNumber, @DateStamp, @RoomtypeNumber,
    @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9, 
    @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9, 
    @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9, 
    @AdultArrivalAddon, @AdultArrivalAddon2, @AdultArrivalAddon3, @AdultArrivalAddon4, @AdultArrivalAddon5, @AdultArrivalAddon6, @AdultArrivalAddon7, @AdultArrivalAddon8, @AdultArrivalAddon9, 
    @AdultStayAddon, @AdultStayAddon2, @AdultStayAddon3, @AdultStayAddon4, @AdultStayAddon5, @AdultStayAddon6, @AdultStayAddon7, @AdultStayAddon8, @AdultStayAddon9, 
    @AdultDepartureAddon, @AdultDepartureAddon2, @AdultDepartureAddon3, @AdultDepartureAddon4, @AdultDepartureAddon5, @AdultDepartureAddon6, @AdultDepartureAddon7, @AdultDepartureAddon8, @AdultDepartureAddon9, 
    @Children1ArrivalAddon, @Children1ArrivalAddon2, @Children1ArrivalAddon3, @Children1ArrivalAddon4, @Children1ArrivalAddon5, @Children1ArrivalAddon6, @Children1ArrivalAddon7, @Children1ArrivalAddon8, @Children1ArrivalAddon9, 
    @Children1StayAddon, @Children1StayAddon2, @Children1StayAddon3, @Children1StayAddon4, @Children1StayAddon5, @Children1StayAddon6, @Children1StayAddon7, @Children1StayAddon8, @Children1StayAddon9, 
    @Children1DepartureAddon, @Children1DepartureAddon2, @Children1DepartureAddon3, @Children1DepartureAddon4, @Children1DepartureAddon5, @Children1DepartureAddon6, @Children1DepartureAddon7, @Children1DepartureAddon8, @Children1DepartureAddon9, 
    @Children2ArrivalAddon, @Children2ArrivalAddon2, @Children2ArrivalAddon3, @Children2ArrivalAddon4, @Children2ArrivalAddon5, @Children2ArrivalAddon6, @Children2ArrivalAddon7, @Children2ArrivalAddon8, @Children2ArrivalAddon9, 
    @Children2StayAddon, @Children2StayAddon2, @Children2StayAddon3, @Children2StayAddon4, @Children2StayAddon5, @Children2StayAddon6, @Children2StayAddon7, @Children2StayAddon8, @Children2StayAddon9, 
    @Children2DepartureAddon, @Children2DepartureAddon2, @Children2DepartureAddon3, @Children2DepartureAddon4, @Children2DepartureAddon5, @Children2DepartureAddon6, @Children2DepartureAddon7, @Children2DepartureAddon8, @Children2DepartureAddon9
END

CLOSE db_curprice
DEALLOCATE db_curprice
*/