CREATE PROCEDURE DBO.[GetPriceByAccountGroup4Reservation] @ReferenceNumber INT, @GuestNumber INT, @DiscountInc BIT AS
SET NOCOUNT ON
/*
  NB20190920 Created
  NOTE: 
*/
DECLARE @DebugLevel TINYINT
SET @DebugLevel = 0
CREATE TABLE #ProductContent
(  
  Price MONEY null,
  Tax MONEY null,
  Kind TINYINT not null, --1: package arrival; 2: package stay and departure 3: product arrival; 4: product stay and departure; 5: addon; 6: Arrangement
  FkAccountTaxGroup INT null,
  ProductNumber INT null, --null used temporary as flag 4 filtering done from undone packet records (SUM = PackagePrice and PackagePriceArrival
  LineNumber INT null
  )

exec dbo.GetContentRoomLine @ReferenceNumber, @GuestNumber, @DiscountInc, 0
IF @DebugLevel > 0 
  SELECT SUM(PC.Price) AS Price, SUM(PC.Tax) AS Tax
  FROM #ProductContent PC
  WHERE PC.Kind <> 3 AND PC.Price <> 0

exec dbo.GetContentHallLine @ReferenceNumber, @GuestNumber, @DiscountInc, 0
IF @DebugLevel > 0 
  SELECT SUM(PC.Price) AS Price, SUM(PC.Tax) AS Tax
  FROM #ProductContent PC
  WHERE PC.Kind <> 3 AND PC.Price <> 0
IF @DebugLevel > 1 
  SELECT *
  FROM #ProductContent PC
  WHERE PC.Price <> 0

DECLARE @DiscountRate smallmoney, @DiscountNettoRate smallmoney, @DiscountAsNettoPrice BIT
SELECT TOP 1 @DiscountAsNettoPrice = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE 'DiscountAsNettoPrice'
IF @DiscountAsNettoPrice = 1 OR @DiscountInc = 1
BEGIN
  SELECT TOP 1 @DiscountRate = CASE WHEN (ISNULL(RA.DiscountRate, 0) = 0) THEN RA.FlatDiscountRate ELSE DiscountRate END 
  FROM dbo.ReservationAddress RA (NOLOCK) 
    INNER JOIN dbo.ReservationAddressXTab RAX (NOLOCK) ON RAX.AddressNumber = RA.AddressNumber
  WHERE RAX.ReferenceNumber = @ReferenceNumber AND RAX.GuestNumber = @GuestNumber
END
SET @DiscountRate = ISNULL(@DiscountRate * 0.01, 1) 
IF @DiscountAsNettoPrice = 1
BEGIN
  SET @DiscountNettoRate = @DiscountRate
  SET @DiscountRate = 1
END
ELSE 
  SET @DiscountNettoRate = 1
DECLARE @DefaultAccountGroup INT
SELECT TOP 1 @DefaultAccountGroup = PkAccountVatGroup
FROM AccountVatGroup (NOLOCK) WHERE Profile = 0 ORDER BY OrderNumber
INSERT INTO #ProductContent
SELECT SUM(AL.TotalPrice) AS PriceTotal
, SUM(AL.TotalPrice*(1- 1/convert(float,(dbo.GetVatPercent(AR.VATValueNumber)/100 +1)))) AS Tax
, 6, ISNULL(AR.FkAccountVatGroup, @DefaultAccountGroup) AS FkAccountVatGroup
, null, null
FROM ArrangementLines AL WITH (NOLOCK)
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = AL.WareNumber
  INNER JOIN Accounts AR WITH (NOLOCK) ON AR.AccountNumber = Ware.ResultAccountNumber
  INNER JOIN ArrangementReservationsXtab ALX WITH (NOLOCK) ON ALX.ArrangementLineNumber = AL.ArrangementLineNumber
    AND ALX.ReferenceNumber = @ReferenceNumber
    AND ALX.GuestNumber = @GuestNumber
WHERE AL.PacketItemNumber IS NULL AND NOT AL.FK_ExDepartment IS NULL--Extras
GROUP BY ISNULL(AR.FkAccountVatGroup, @DefaultAccountGroup), dbo.GetVatPercent(AR.VATValueNumber)
IF ISNULL(@DiscountRate, 1) <> 1
  INSERT INTO #ProductContent
  SELECT -Price*@DiscountRate, -Tax*@DiscountRate, 6, FkAccountTaxGroup, null, null FROM #ProductContent WHERE ProductNumber IS null and LineNumber IS null

IF @DebugLevel > 0 
  SELECT SUM(PC.Price) AS Price, SUM(PC.Tax) AS Tax
  FROM #ProductContent PC
  WHERE PC.Kind <> 3 AND PC.Price <> 0

IF @DebugLevel = 0 
  SELECT SUM(PC.Price) AS Price, SUM(PC.Tax) AS Tax, ATG.Name, ATG.Description, ATG.Profile, ATG.OrderNumber, ATG.PkAccountVatGroup 
  FROM #ProductContent PC
    INNER JOIN AccountVatGroup ATG (NOLOCK) ON ATG.PkAccountVatGroup = PC.FkAccountTaxGroup
  WHERE PC.Kind <> 3 AND PC.Price <> 0
  GROUP BY ATG.PkAccountVatGroup, ATG.Name, ATG.Description, ATG.Profile, ATG.OrderNumber 
ELSE
  SELECT PC.Price, PC.Tax, PC.Kind, PC.ProductNumber, PC.LineNumber, ATG.Name, ATG.PkAccountVatGroup 
  FROM #ProductContent PC
    INNER JOIN AccountVatGroup ATG (NOLOCK) ON ATG.PkAccountVatGroup = PC.FkAccountTaxGroup
  WHERE PC.Kind <> 3 AND PC.Price <> 0
  ORDER BY PC.LineNumber, PC.ProductNumber

DROP TABLE #ProductContent
