CREATE PROCEDURE [dbo].[GetProductPackagePart] @ProductNumber INT, @StartDate DATETIME, @EndDate DATETIME, @BookedTypeNumber INT, @NumberOfRooms INT, 
  @AdultPax INT, @Child1Pax INT, @Child2Pax INT, @DiscountPercentPackage REAL=0, @PricePackage MONEY=0 AS
SET NOCOUNT ON
/*
  NB20190702 Created
  NOTE: Calculates Product (package part) Price and Tax amount grouped by AccaountVatGroup
    Replaces old GetPricePackagePartProduct
  Pre: AmountTotal have to be set 0 or overwritten value
*/
DECLARE @ShowPrint BIT, @Msg VARCHAR(2000), @tPre DATETIME, @tPost DATETIME
SET @ShowPrint = 0
IF @ShowPrint = 1 
BEGIN
  SET @tPre = GETDATE()
  PRINT 'Call SP [GetProductPackagePart] begin --------------------------------'
  SET @Msg = 'DECLARE @ProductNumber int, @StartDate datetime, @EndDate datetime, @OldRoomTypeNumber int, @NumberOfRooms int, @AdultPax int, @Child1Pax int, @Child2Pax int'+CHAR(13)
  SET @Msg = @Msg + ', @DiscountPercentPackage real, @PricePackage real, @VatPackage 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( @PricePackage, -1) = -1 
    SET @Msg = @Msg+'SET @PricePackage = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @PricePackage = '+CONVERT(VARCHAR,@PricePackage)+CHAR(13)
  SET @Msg = @Msg+'EXEC [dbo].[GetProductPackagePart] @ProductNumber, @StartDate, @EndDate, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @PricePackage'+ CHAR(13)
  PRINT @Msg
  PRINT 'Call SP [GetProductPackagePart] end --------------------------------'
END

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 @PackageTotal MONEY    SET @PackageTotal = 0
DECLARE @PackageTotalVat MONEY SET @PackageTotalVat = 0
DECLARE @CurrentDate DATETIME
DECLARE @PacketType SMALLINT
DECLARE @PacketNumber INT
--nb20190930 OPT: Move the temp table up in hiearchy and store all product prices and packets for the entire reservation period
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
  IF @PacketNumber > 0 
  BEGIN
    IF @CurrentDate = @StartDate AND @PacketType = 0
      INSERT INTO #ProductContent 
        SELECT Price, Tax, 1, FkAccountTaxGroup, NULL, NULL FROM dbo.GetPackageContent(@PacketNumber, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @PricePackage)
    IF @CurrentDate > @StartDate AND @CurrentDate < @EndDate AND @PacketType = 1 or @CurrentDate = @EndDate AND @PacketType = 2
      INSERT INTO #ProductContent 
        SELECT Price, Tax, 2, FkAccountTaxGroup, NULL, NULL FROM dbo.GetPackageContent(@PacketNumber, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountPercentPackage, @PricePackage)
  END
FETCH NEXT FROM PackageCur INTO @PacketType, @CurrentDate, @PacketNumber
END
CLOSE PackageCur
DEALLOCATE PackageCur
DROP TABLE #PackageInfo
IF @ShowPrint = 1 
BEGIN
  SET @tPost = GETDATE();
  PRINT 'Time: ' + Convert(VARCHAR, DATEDIFF(millisecond, @tPre, @tPost))
END

