CREATE FUNCTION [dbo].[GetPackageContent] (@PackageNumber int, @Date Datetime, @ProductNumber int, @RoomTypeNumber int, @NumberOfRooms int, @Adult int, @Children1 int, @Children2 int, @DiscountPercent Float = 0, @PriceOverwrite MONEY = 0) 
RETURNS @PackageContent TABLE (Price MONEY, Tax MONEY, TaxPercent SMALLMONEY, FkAccountTaxGroup INT) AS
BEGIN
/*
--nb20230629 MenuWare tax calc support added, Table valued functions (tvf) returning few rows are performance ok https://learn.microsoft.com/en-us/archive/blogs/psssql/query-performance-and-multi-statement-table-valued-functions
--NB20190701 Created
--NOTE: Calculates package Price and Tax amount grouped by AccaountVatGroup
*/
IF (@PackageNumber>0)
BEGIN
  DECLARE @PackagesplitAfterReduction BIT
  SELECT @PackagesplitAfterReduction =  CONVERT(BIT, KeyValue) FROM Hotelsetup WITH (NOLOCK) WHERE Searchkey ='PackagesplitAfterReduction'
  IF ISNULL(@PackagesplitAfterReduction,0) =0 SET @PackagesplitAfterReduction = 0

  DECLARE @FkAccountTaxGroupDefault INT
  SELECT TOP 1 @FkAccountTaxGroupDefault = PkAccountVatGroup
  FROM AccountVatGroup (NOLOCK) WHERE Profile = 0 ORDER BY OrderNumber
 
  DECLARE @ProductFixedPrice BIT
  DECLARE @ProductDay BIT
  IF @ProductNumber>0
    SELECT @ProductFixedPrice = FixedPrice, @ProductDay  = DayProduct FROM Products WITH (NOLOCK) WHERE ProductNumber=@ProductNumber
  ELSE
    SET @ProductFixedPrice = 0
  IF ISNULL(@ProductFixedPrice, 0) = 0 SET @ProductFixedPrice=0
  IF ISNULL(@ProductDay,0)=0 SET @ProductDay=0

  DECLARE @FkAccountTaxGroup INT
  DECLARE @PacketItemNumber INT
  DECLARE @PacketPrice FLOAT
  DECLARE @TaxPercent FLOAT
  DECLARE @DebitType SMALLINT
  DECLARE @ProcentDebType BIT
  DECLARE @DateStr CHAR(8)
  DECLARE @RoomPriceAdult MONEY
  DECLARE @RoomPriceChildren1 MONEY
  DECLARE @RoomPriceChildren2 MONEY
  --nb20230704
  DECLARE @WareNumber INT
  DECLARE @IsMenuWare BIT

  DECLARE #WareCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT DISTINCT PI.PacketItemNumber--field is required even not used else distinct will hide needed items
  , ISNULL(dbo.GetVatPercent(A.VatValueNumber), 0)
  , PI.PriceOverWrite
  , PI.DebType
  , CASE WHEN PI.DebType IN (2,5,8) THEN 1 ELSE 0 END AS ProcentDebType
  , W.WareNumber
  , A.FkAccountVatGroup
  FROM PacketItem_New PI WITH (NOLOCK)
    INNER JOIN PacketItem_NewDepartmentXTab pdx WITH (NOLOCK) ON pdx.PacketItemNumber = PI.PacketItemNumber AND pdx.Type = 1 -- DepartmentServiceOverwrite
	INNER JOIN Ware W (nolock) ON PI.WareNumber = W.WareNumber
	INNER JOIN Accounts A (NOLOCK) ON W.ResultAccountNumber = A.AccountNumber
  WHERE PI.PacketNumber=@PackageNumber AND ISNULL(PI.PriceOverWrite, 0) <> 0 
  ORDER BY ProcentDebType
  OPEN #WareCur
  FETCH NEXT FROM #WareCur
  INTO @PacketItemNumber, @TaxPercent, @PacketPrice, @DebitType, @ProcentDebType, @WareNumber, @FkAccountTaxGroup
  SET @DateStr = CONVERT (CHAR(8), @Date, 112)
  IF NOT(ISNULL(@Priceoverwrite,0)=0 OR @Priceoverwrite=-1)
    SET @Priceoverwrite = NULL
  IF (ISNULL(@Priceoverwrite,0)=0)
  BEGIN
    SET @RoomPriceAdult = 0
    SET @RoomPriceChildren1= 0
    SET @RoomPriceChildren2= 0
    SELECT @RoomPriceAdult = Adult, @RoomPriceChildren1 = ChildPax1, @RoomPriceChildren2 = ChildPax2 
	FROM dbo.GetRoomPrice (@ProductNumber, @DateStr, @RoomTypeNumber, @Adult, @Children1, @Children2, -1, 0, 0, 0)
  END
  ELSE
  BEGIN
    SET @RoomPriceAdult = @Priceoverwrite
    SET @RoomPriceChildren1 = @Priceoverwrite
    SET @RoomPriceChildren2 = 0
  END
  DECLARE @FixAmountSum FLOAT  SET @FixAmountSum = 0
  DECLARE @Pieces SMALLINT
  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    IF ISNULL(@FkAccountTaxGroup,0)=0 SET @FkAccountTaxGroup = @FkAccountTaxGroupDefault
    IF ISNULL(@PacketPrice,0)=0 SET @PacketPrice = 0 -- safe
    IF ISNULL(@DebitType,0)=0 SET @DebitType = 1 -- safe
    IF (@DebitType=1)
    BEGIN
      IF (@Adult)=0
        SET @PacketPrice=0
      SET @Pieces = 1
    END
    ELSE
    IF (@DebitType=3)
    BEGIN
      IF (@Adult)=0
      BEGIN
        SET @PacketPrice=0
        SET @Pieces = 1
      END
      ELSE
      BEGIN
        SET @PacketPrice=@PacketPrice*@Adult
        SET @Pieces = @Adult
      END
    END
    ELSE
    IF (@DebitType=2)
    BEGIN
      IF (@ProductDay=0) OR (@ProductDay=1 AND @ProductFixedPrice=1)
      BEGIN
        IF (@ProductDay=0)
          SET @RoomPriceAdult = @RoomPriceAdult / @NumberOfRooms 
        SET @Pieces = @NumberOfRooms
      END
      ELSE
        SET @Pieces = @Adult
      IF (@Adult)=0
        SET @PacketPrice=0
      ELSE
      BEGIN
         IF @PackagesplitAfterReduction = 0
           SET @PacketPrice=@RoomPriceAdult*(@PacketPrice/100)
         ELSE
           SET @PacketPrice=(@RoomPriceAdult - @FixAmountSum)*(@PacketPrice/100)
         IF (@ProductDay=0) OR (@ProductDay=1 AND ISNULL(@Priceoverwrite,0)=0)
          SET @PacketPrice = @PacketPrice * @Pieces
         IF @DiscountPercent>0
          SET @PacketPrice = ROUND(@PacketPrice *(1-(1/Convert(Float,((@DiscountPercent/100)+1)))),2)
      END
    END
    ELSE
    IF (@DebitType=4)
    BEGIN
      SET @Pieces = 1
      IF (@Children1)=0
        SET @PacketPrice=0
    END
    ELSE
    IF (@DebitType=5)
    BEGIN
      IF (@Children1)=0
      BEGIN
        SET @PacketPrice=0
        SET @Pieces = 1
      END
      ELSE
      BEGIN
        SET @PacketPrice=@PacketPrice*@Children1
        SET @Pieces = @Children1
      END
    END
    ELSE
    IF (@DebitType=6)
    BEGIN
      IF (@ProductDay=0) OR (@ProductDay=1 AND @ProductFixedPrice=1)
        SET @Pieces = @NumberOfRooms
      ELSE
        SET @Pieces = @Children1
      IF (@Children1)=0
        SET @PacketPrice=0
      ELSE
      BEGIN
         IF @PackagesplitAfterReduction = 0
           SET @PacketPrice=@RoomPriceChildren1*(@PacketPrice/100)
         ELSE
           SET @PacketPrice=(@RoomPriceChildren1 - @FixAmountSum)*(@PacketPrice/100)
         IF (@ProductDay=0) OR (@ProductDay=1 AND ISNULL(@Priceoverwrite,0)=0)
           SET @PacketPrice = @PacketPrice * @Pieces
         IF @DiscountPercent>0 
          SET @PacketPrice = ROUND(@PacketPrice *(1-(1/Convert(Float,((@DiscountPercent/100)+1)))),2)
      END
    END
    ELSE
    IF (@DebitType=7)
    BEGIN
      SET @Pieces = 1
      IF (@Children2)=0
        SET @PacketPrice=0
    END
    ELSE
    IF (@DebitType=8)
    BEGIN
      IF (@Children2)=0
      BEGIN
        SET @PacketPrice=0
        SET @Pieces = 1
      END
      ELSE
      BEGIN
        SET @PacketPrice=@PacketPrice*@Children2
        SET @Pieces = @Children2
      END
    END
    ELSE
    IF (@DebitType=9)
    BEGIN
      IF (@ProductDay=0) OR (@ProductDay=1 AND @ProductFixedPrice=1)
        SET @Pieces = @NumberOfRooms
      ELSE
        SET @Pieces = @Children2
      IF (@Children2)=0
        SET @PacketPrice=0
      ELSE
      BEGIN
        IF @PackagesplitAfterReduction = 0
          SET @PacketPrice=@RoomPriceChildren2*(@PacketPrice/100)
        ELSE
          SET @PacketPrice=(@RoomPriceChildren2 - @FixAmountSum)*(@PacketPrice/100)
        IF (@ProductDay=0) OR (@ProductDay=1 AND ISNULL(@Priceoverwrite,0)=0)
          SET @PacketPrice = @PacketPrice * @Pieces
        IF @DiscountPercent>0
          SET @PacketPrice = ROUND(@PacketPrice *(1-(1/Convert(Float,((@DiscountPercent/100)+1)))),2)
      END
    END
    IF @PackagesplitAfterReduction = 1
    BEGIN
      IF NOT(@DebitType IN (3,6,7))
        SET @FixAmountSum = @FixAmountSum + @PacketPrice
    END
    SET @PacketPrice = ROUND(CONVERT(MONEY,@PacketPrice * @NumberOfRooms),2)
    IF (@NumberOfRooms<0)
    BEGIN
      SET @PacketPrice = ABS(@PacketPrice)
      SET @Pieces = -1*@Pieces
    END
    SET @PacketPrice = ROUND(@PacketPrice,2)

	--nb20230704
    SELECT @IsMenuWare = dbo.IsWareMenu (@WareNumber, @PacketPrice, @Pieces)
    IF @IsMenuWare = 1
    BEGIN
	  DECLARE @MenuWareName VARCHAR(50), @MenuPricePercent MONEY, @ItemPrice MONEY, @MenuInPercent BIT
      DECLARE mWareCur CURSOR FAST_FORWARD READ_ONLY FOR
      SELECT w.WareName, mwx.Procentage, 
        ISNULL(dbo.GetVatPercent(A.VatValueNumber), 0), ISNULL(A.FkAccountVatGroup, @FkAccountTaxGroupDefault), mw.InPercent
      FROM Ware w WITH (NOLOCK)          
        JOIN MenuWareXTab mwx WITH (NOLOCK) ON w.WareNumber = mwx.WareNumber AND mwx.MenuNumber = @WareNumber AND ISNULL(w.MarkAsDeleted , 0) = 0
        JOIN ACCOUNTS A WITH (NOLOCK) ON w.ResultAccountNumber = A.AccountNumber
        JOIN MenuWare mw WITH (NOLOCK) ON mw.FK_MenuNumber = mwx.MenuNumber
      OPEN mWareCur
      FETCH NEXT FROM mWareCur INTO @MenuWareName, @MenuPricePercent, @TaxPercent, @FkAccountTaxGroup, @MenuInPercent          
      WHILE @@FETCH_STATUS = 0
      BEGIN
        IF @MenuInPercent = 1
          SET @ItemPrice = ((@PacketPrice*@MenuPricePercent)/100.0)
        ELSE
          SET @ItemPrice = @MenuPricePercent*@Pieces
        SET @ItemPrice = ABS(@ItemPrice)
  	    UPDATE @PackageContent SET Price = Price + @ItemPrice, Tax = Tax + @ItemPrice*(1-(1/Convert(Float,((@TaxPercent/100)+1)))) WHERE FkAccountTaxGroup = @FkAccountTaxGroup AND TaxPercent = @TaxPercent
	    IF @@ROWCOUNT = 0
          INSERT INTO @PackageContent (Price, Tax, TaxPercent, FkAccountTaxGroup) VALUES (@ItemPrice, @ItemPrice*(1-(1/Convert(Float,((@TaxPercent/100)+1)))), @TaxPercent, @FkAccountTaxGroup)
        FETCH NEXT FROM mWareCur INTO @MenuWareName, @MenuPricePercent, @TaxPercent, @FkAccountTaxGroup, @MenuInPercent
	  END	  
      CLOSE mWareCur
      DEALLOCATE mWareCur
	END
	ELSE
	BEGIN

	  UPDATE @PackageContent SET Price = Price + @PacketPrice, Tax = Tax + @PacketPrice*(1-(1/Convert(Float,((@TaxPercent/100)+1)))) WHERE FkAccountTaxGroup = @FkAccountTaxGroup AND TaxPercent = @TaxPercent
	  IF @@ROWCOUNT = 0
        INSERT INTO @PackageContent (Price, Tax, TaxPercent, FkAccountTaxGroup) VALUES (@PacketPrice, @PacketPrice*(1-(1/Convert(Float,((@TaxPercent/100)+1)))), @TaxPercent, @FkAccountTaxGroup)

    END
    FETCH NEXT FROM #WareCur
    INTO @PacketItemNumber, @TaxPercent, @PacketPrice, @DebitType, @ProcentDebType, @WareNumber, @FkAccountTaxGroup
  END
  CLOSE #WareCur
  DEALLOCATE #WareCur
END
  RETURN
END