CREATE PROCEDURE DBO.[GetPricePackagePartProduct] @ProductNumber INT, @StartDate DATETIME, @EndDate DATETIME, @BookedTypeNumber INT, @NumberOfRooms INT, 
  @AdultPax INT, @Child1Pax INT, @Child2Pax INT, @DiscountPercentPackage REAL = 0, @Price MONEY=-1 OUTPUT, @Tax MONEY OUTPUT, @1stDayOnly BIT=0 AS
SET NOCOUNT ON
/*
  KJ 20161125: Bug: Fix for "Wrong package info - wrong vat is calculated". Case #CR116322
  KJ 20161007: Getting packet info is optimized. Case #CR113729
--KJ 20131014: ProductPrice Rewrite.
--nb20070813 Charge stay at departure when period > 1 day updated back to rev from 20070809
--nb20070810 Charge stay at departure when period > 1 day updated
--nb20070809 Charge stay at departure when period > 1 day
  NB20041025 Created
  NOTE

*/
DECLARE @ShowPrint BIT, @Msg VARCHAR(2000)
SET @ShowPrint = 0
IF @ShowPrint = 1 
BEGIN
  PRINT 'Call SP GetPricePackagePartProduct begin --------------------------------'
  SET @Msg = 'DECLARE @ProductNumber int, @StartDate datetime, @EndDate datetime, @OldRoomTypeNumber int, @NumberOfRooms int, @AdultPax int, @Child1Pax int, @Child2Pax int, @Price MONEY, @Tax MONEY'+CHAR(13)
  SET @Msg = @Msg + ', @DiscountPercentPackage real'+CHAR(13)
  IF ISNULL( @ProductNumber, -1) = -1 
    SET @Msg = @Msg+'SET @ProductNumber = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @ProductNumber = '+CONVERT(VARCHAR,@ProductNumber)+CHAR(13)
  SET @Msg = @Msg + 'SET @StartDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@StartDate, -1), 112)+''' , 112)'+CHAR(13)
  SET @Msg = @Msg + 'SET @EndDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@EndDate, -1), 112)+''' , 112)'+CHAR(13)
  IF ISNULL( @BookedTypeNumber, -1) = -1 
    SET @Msg = @Msg+'SET @OldRoomTypeNumber = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg+'SET @OldRoomTypeNumber = '+CONVERT(VARCHAR, @BookedTypeNumber)+CHAR(13)
  IF ISNULL( @NumberOfRooms, -1) = -1 
    SET @Msg = @Msg+'SET @NumberOfRooms = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @NumberOfRooms = '+CONVERT(VARCHAR,@NumberOfRooms)+CHAR(13)
  IF ISNULL( @AdultPax, -1) = -1 
    SET @Msg = @Msg+'SET @AdultPax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @AdultPax = '+CONVERT(VARCHAR,@AdultPax)+CHAR(13)
  IF ISNULL( @Child1Pax, -1) = -1 
    SET @Msg = @Msg+'SET @Child1Pax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @Child1Pax = '+CONVERT(VARCHAR,@Child1Pax)+CHAR(13)
  IF ISNULL( @Child2Pax, -1) = -1 
    SET @Msg = @Msg+'SET @Child2Pax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @Child2Pax = '+CONVERT(VARCHAR,@Child2Pax)+CHAR(13)
  IF ISNULL( @DiscountPercentPackage, -1) = -1 
    SET @Msg = @Msg+'SET @DiscountPercentPackage = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @DiscountPercentPackage = '+CONVERT(VARCHAR,@DiscountPercentPackage)+CHAR(13)
  IF ISNULL( @Price, -1) = -1 
    SET @Msg = @Msg+'SET @Price = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @Price = '+CONVERT(VARCHAR,@Price)+CHAR(13)
  IF ISNULL( @Tax, -1) = -1 
    SET @Msg = @Msg+'SET @Tax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @Tax = '+CONVERT(VARCHAR,@Tax)+CHAR(13)
  SET @Msg = @Msg+'EXEC GetPricePackagePartProduct @ProductNumber, @StartDate, @EndDate, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @Price OUTPUT, @Tax OUTPUT'+ CHAR(13)
  SET @Msg = @Msg + 'PRINT CONVERT(varchar, @Price) --' + CONVERT(varchar, ISNULL(@Price, -1)) + CHAR(13)
  SET @Msg = @Msg + 'PRINT CONVERT(varchar, @Tax) --' + CONVERT(varchar, ISNULL(@Tax, -1))+ CHAR(13)
  PRINT @Msg
  PRINT 'Call SP GetPricePackagePartProduct end --------------------------------'
END

DECLARE @PackageAmount MONEY
DECLARE @PackageAmountVat MONEY
DECLARE @PackageTotal MONEY    SET @PackageTotal = 0
DECLARE @PackageTotalVat MONEY SET @PackageTotalVat = 0
DECLARE @CurrentDate DATETIME
DECLARE @PacketType SMALLINT
DECLARE @PacketNumber INT

if ISNULL(@AdultPax,0)=0    Set @AdultPax = 0
if ISNULL(@Child1Pax,0)=0 Set @Child1Pax = 0
if ISNULL(@Child2Pax,0)=0 Set @Child2Pax = 0

CREATE TABLE #PackageInfo
(
  View_DateStamp DATETIME,
  View_Id INT,
  View_FK_ProductNumber INT,
  View_PacketType INT
)
INSERT INTO #PackageInfo
  EXEC ProductPricePacketInfo @StartDate, @EndDate, @ProductNumber

DECLARE PackageCur Cursor READ_ONLY FAST_FORWARD FOR
  SELECT View_PacketType, View_DateStamp, ProductPricePacket.FK_PacketNumber
  FROM #PackageInfo WITH (NOLOCK)
  INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = View_Id
  WHERE FK_ProductNumber = @ProductNumber
  AND #PackageInfo.View_DateStamp >= @StartDate
  AND #PackageInfo.View_DateStamp <= @EndDate
  AND ISNULL(ProductPricePacket.FK_PacketNumber, 0) > 0
  ORDER BY #PackageInfo.View_PacketType, #PackageInfo.View_DateStamp, Id ASC
OPEN PackageCur

FETCH NEXT FROM PackageCur INTO @PacketType, @CurrentDate, @PacketNumber

WHILE (@@FETCH_STATUS=0)
BEGIN
  SET @PackageAmount = 0
  SET @PackageAmountVat = 0

  IF @CurrentDate > @StartDate AND @CurrentDate < @EndDate AND @PacketNumber > 0 AND @1stDayOnly = 0 AND @PacketType = 1
  BEGIN
    SET @PackageAmount = @Price
    IF ISNULL(@PackageAmount,0)=0 SET @PackageAmount = -1
    EXEC GetPricePackage @PacketNumber, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @PackageAmount OUTPUT, @PackageAmountVat OUTPUT
  END
  ELSE IF @CurrentDate = @StartDate AND @PacketNumber > 0 AND @PacketType = 0
  BEGIN
    SET @PackageAmount = @Price
    IF ISNULL(@PackageAmount,0)=0 SET @PackageAmount = -1
    EXEC GetPricePackage @PacketNumber, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @PackageAmount OUTPUT, @PackageAmountVat OUTPUT
  END

  IF @CurrentDate = @EndDate AND @PacketNumber > 0 AND @1stDayOnly = 0 AND @PacketType = 2
  BEGIN
    SET @PackageTotal = @PackageTotal + @PackageAmount
    SET @PackageTotalVat = @PackageTotalVat + @PackageAmountVat

    SET @PackageAmount = @Price
    IF ISNULL(@PackageAmount,0)=0 SET @PackageAmount = -1
    EXEC GetPricePackage @PacketNumber, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @PackageAmount OUTPUT, @PackageAmountVat OUTPUT
  END

  SET @PackageTotal = @PackageTotal + @PackageAmount
  SET @PackageTotalVat = @PackageTotalVat + @PackageAmountVat
  
  FETCH NEXT FROM PackageCur INTO @PacketType, @CurrentDate, @PacketNumber
END

SET @Price = @PackageTotal
SET @Tax = @PackageTotalVat

CLOSE PackageCur
DEALLOCATE PackageCur

DROP TABLE #PackageInfo