CREATE PROCEDURE [dbo].[GetProductVAT] @ADayProduct BIT, @ARoomTypeNumber INT, @ADateStamp CHAR(8), @APacketNumber INT, @AProductNumber INT, @AAdult INT, @AChildren1 INT, @AChildren2 INT,
  @ANumberOfRooms INT, @AHotelCustomerNumber INT, @ANameListSetting INT, @APriceOverwritten BIT, @AFixedPrice BIT, 
  @AReferenceNumber INT, @AGuestNumber INT, @ASegmentNumber INT, @APointOfSaleNumber INT,
  @APriceOverwriteValue MONEY, @AProductPrice MONEY, @ADiscountValue FLOAT, @AVATOfPrice FLOAT OUTPUT AS
/*
  Created By Kent Johansen, September 2024.
  Used to get product price VAT.
  Addon?
  Discount
*/

DECLARE @LShowPrint BIT
SET @LShowPrint = 1

DECLARE @LOnePersonPaysForAll BIT
SELECT @LOnePersonPaysForAll = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'OnePersonPaysForAll'
SET @LOnePersonPaysForAll = ISNULL(@LOnePersonPaysForAll, 0)

DECLARE @LCalcPriceBasedOnOnePersonPaysForAll BIT
SELECT @LCalcPriceBasedOnOnePersonPaysForAll = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey ='CalcPriceBasedOnOnePersonPaysForAll'
SET @LCalcPriceBasedOnOnePersonPaysForAll = ISNULL(@LCalcPriceBasedOnOnePersonPaysForAll, 0)

IF @LCalcPriceBasedOnOnePersonPaysForAll = 0
  SET @LOnePersonPaysForAll = -1

CREATE TABLE #PacketWareTable --Populated by CalcPacketPriceAccExt
(
  [ResultAccountNumber] [INT] NULL,
  [DepartmentNumber] [SMALLINT] NULL,
  [PacketWarePrice] [FLOAT] NULL,
  [VatOfPacketWarePrice] [FLOAT] NULL,
  [WareGroupNumber] [FLOAT] NULL,
  [WareName] [VARCHAR](50) NULL,
  [WareType] INT NULL,
  [WareNumber] INT NULL,
  [ProductWareLinkNumber] BIGINT NULL,
  [ProductNumber] INT NULL,
  [BookedWareCount] INT NULL,
  [HotelCustomerNumber] INT NULL,
  FromTime DATETIME NULL,
  ToTime DATETIME NULL
)

DECLARE @LPackagePrice FLOAT
DECLARE @LVatByPacketValue FLOAT
DECLARE @LVATOfPrice FLOAT
DECLARE @LGetVATFrom SMALLINT
DECLARE @LWareNumber1 INT
DECLARE @LStayBalanceAccountNumber1 INT
DECLARE @LStayResultAccountNumber1 INT
DECLARE @LStayVATAccountNumber1 INT
DECLARE @LVatValueProduct1 FLOAT
DECLARE @LWareText1 VARCHAR(150)
DECLARE @LVATProduct BIT
DECLARE @LLogiWareIndex INT

SELECT @LGetVATFrom = CONVERT(INT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BookkeepingReport'

SET @AVATOfPrice = 0

EXEC CalcPacketPriceAccExt @ADayProduct, @ARoomTypeNumber, @ADateStamp, @APacketNumber, @AProductNumber, @AAdult, @AChildren1, @AChildren2,
  @ANumberOfRooms, @AHotelCustomerNumber, @LPackagePrice OUTPUT, @LVatByPacketValue OUTPUT, @ANamelistSetting, @LOnePersonPaysForAll,
  @APriceOverwritten, @AFixedPrice, 1, @AReferenceNumber, @AGuestNumber,
  @APriceoverwriteValue, @ADiscountValue, -1, 0

-- Calc VAT of Logi/Hall
IF ((@LGetVATFrom >= 0) AND (@LGetVATFrom < 3) OR (@LGetVATFrom = 4))
BEGIN
  IF @LGetVATFrom = 0 -- Get Vat From Segment
    EXEC GetVatValueAndAccountnumbers @ADayProduct, @LGetVatFrom, @ASegmentNumber, @LWareNumber1 OUTPUT, @LStayBalanceAccountNumber1 OUTPUT, @LStayResultAccountNumber1 OUTPUT, @LStayVATAccountNumber1 OUTPUT, @LVatValueProduct1 OUTPUT, @LWareText1 OUTPUT
  ELSE IF @LGetVATFrom = 1 -- Get Vat From POS
    EXEC GetVatValueAndAccountnumbers @ADayProduct, @LGetVatFrom, @APointOfSaleNumber, @LWareNumber1 OUTPUT, @LStayBalanceAccountNumber1 OUTPUT, @LStayResultAccountNumber1 OUTPUT, @LStayVATAccountNumber1 OUTPUT, @LVatValueProduct1 OUTPUT, @LWareText1 OUTPUT
  ELSE IF @LGetVATFrom = 2 -- Get Vat From Roomtype -- TODO ConferenceRoom if DayProduct
    EXEC GetVatValueAndAccountnumbers @ADayProduct, @LGetVatFrom, @ARoomTypeNumber, @LWareNumber1 OUTPUT, @LStayBalanceAccountNumber1 OUTPUT, @LStayResultAccountNumber1 OUTPUT, @LStayVATAccountNumber1 OUTPUT, @LVatValueProduct1 OUTPUT, @LWareText1 OUTPUT
  ELSE IF @LGetVATFrom = 4 -- VAT
  BEGIN
    IF @AProductNumber > 0
    BEGIN
      SELECT @LVATProduct = ExcludeVAT FROM Products WITH (NOLOCK) WHERE ProductNumber = @AProductNumber
      IF @LVATProduct IS NULL
        SET @LVATProduct = 1
      ELSE
      BEGIN
        IF @LVATProduct = 0
          SET @LVATProduct = 1
        ELSE
          SET @LVATProduct = 0
      END
    END
    ELSE
      SET @LVATProduct = 1
  
    SET @LLogiWareIndex = @LVATProduct + 1
    exec GetVatValueAndAccountnumbers @ADayProduct, @LGetVatFrom, @LLogiWareIndex, @LWareNumber1 OUTPUT, @LStayBalanceAccountNumber1 OUTPUT, @LStayResultAccountNumber1 OUTPUT, @LStayVATAccountNumber1 OUTPUT, @LVatValueProduct1 OUTPUT, @LWareText1 OUTPUT
  END
END
ELSE
  EXEC GetVatValueAndAccountnumbers @ADayProduct, @LGetVatFrom, @AProductNumber, @LWareNumber1 OUTPUT, @LStayBalanceAccountNumber1 OUTPUT, @LStayResultAccountNumber1 OUTPUT, @LStayVATAccountNumber1 OUTPUT, @LVatValueProduct1 OUTPUT, @LWareText1 OUTPUT

IF @LShowPrint = 1
BEGIN
  PRINT '@LPackagePrice'
  PRINT @LPackagePrice
  PRINT '@LVatByPacketValue'
  PRINT @LVatByPacketValue

  SELECT * FROM #PacketWareTable

  PRINT '@LVatValueProduct1'
  PRINT @LVatValueProduct1
  PRINT '@AProductPrice'
  PRINT @AProductPrice
END

SET @AProductPrice = @AProductPrice - @LPackagePrice
SET @LVATOfPrice = @AProductPrice * (1-(1/CONVERT(FLOAT, ((@LVatValueProduct1/100)+1))))

IF @LShowPrint = 1
BEGIN
  PRINT '@AProductPrice'
  PRINT @AProductPrice
  PRINT '@LVATOfPrice'
  PRINT @LVATOfPrice
END

SET @AVATOfPrice = @LVatByPacketValue + @LVATOfPrice

DROP TABLE #PacketWareTable