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, FkAccountTaxGroup INT) AS
BEGIN
/*
  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 @VatPercent FLOAT
  DECLARE @DebitType SMALLINT
  DECLARE @ProcentDebType BIT
  DECLARE @DateStr CHAR(8)
  DECLARE @RoomPriceAdult MONEY
  DECLARE @RoomPriceChildren1 MONEY
  DECLARE @RoomPriceChildren2 MONEY
  DECLARE #WareCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT DISTINCT PI.PacketItemNumber--field is required even not used else distinct will hide needed items
  , dbo.GetVatPercent((SELECT VatValueNumber From Accounts WITH (NOLOCK) WHERE Accounts.AccountNumber=W.ResultAccountNumber)) AS VatPercent
  , PI.PriceOverWrite
  , PI.DebType
  , CASE WHEN PI.DebType IN (2,5,8) THEN 1 ELSE 0 END AS ProcentDebType
  , 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, @VatPercent, @PacketPrice, @DebitType, @ProcentDebType, @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 ISNULL(@VatPercent,0) = 0 SET @VatPercent = 0 --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)
	UPDATE @PackageContent SET Price = Price + @PacketPrice, Tax = Tax + @PacketPrice*(1-(1/Convert(Float,((@VatPercent/100)+1)))) WHERE FkAccountTaxGroup = @FkAccountTaxGroup
	IF @@ROWCOUNT = 0
      INSERT INTO @PackageContent (Price, Tax, FkAccountTaxGroup) VALUES (@PacketPrice, @PacketPrice*(1-(1/Convert(Float,((@VatPercent/100)+1)))), @FkAccountTaxGroup)

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