CREATE PROCEDURE [dbo].[GetTransactions] @FolioNumber int, @BillTypeNumber SmallInt, @LocaleId Int, @PkInvoice int, @GetDataset Bit, @Saldo Money OUTPUT AS
SET NOCOUNT ON
--nb20211026 bug ExchangeRate on Currency payments
--kj20211021 Bug Fix for "Wrong prices when exchangerete is -1 on reservation". Case #CR209941
--nb20211005 transition to use of GetTransactionNew
--nb20180820 ext common sorting with Kitchen output moved into common procedure 
--nb20180323 ext GiftTokenBalance in transaction text
--nb20160906 ext Exchange Use now reservation currency also when manual rooms/halls present
--kj 20160809: Bug: Fix for "Not able to print bill in EUR". Case #CR110257 + more
--nb20160803 Rq2930 Localization
--nb20160628 fix Tax performance fixed (hold lige tungen i munden)
--nb20160608 opt Tax performance improved
--nb20151210 bug Group field renamed to GroupIndex (dbx.updateSQL vs SQL2012 compatibility issue)
--nb20151126 bug @PriceExclVAT and @VAT var type changed from float to decimal(19,4) as mssql server truncates large numbers (103214.28 is truncated)
--nb20151027 new Prebill with content added + ReceiptNumber on transactions
--nb20140121 ext Invoice Convert
--nb20130604 bug @PriceExclVAT type changed from MONEY to FLOAT. needs to be float, money is not accurate enough 

--SelectTypeNumber 0=Statement, 1=checkout, 2=checkout(ledger)
--@ShowTransactionBasedOnTypeNumber 1= OnBill, 2 = OnFolio
--@@PkInvoice -1=AllShowOnBill; 0= AllNotPrintedBeofre >0= BelongToInvoiceId

DECLARE @LFolioNumber INT, @LPkInvoice INT, @LFkCustomerHotel INT
SET @LFolioNumber = ISNULL(@FolioNumber, 0)
SET @LPkInvoice = @PkInvoice
IF @LPkInvoice > 0 AND @LFolioNumber <= 0 
  SELECT DISTINCT @LFolioNumber=Folionumber FROM Transactions T WITH (NOLOCK) 
    INNER JOIN InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
  WHERE ITX.FkInvoice=@LPkInvoice
declare @t table(PriceXTax MONEY NOT NULL --Creating a table variable
  , PriceTax MONEY NOT NULL
  , Price MONEY NOT NULL
  , TaxPercent INT NOT NULL
  , Pieces INT NOT NULL
  , TextOverwrite VARCHAR(250) NOT NULL
  , Note VARCHAR(50) NULL
  , TransactionNumber INT NULL
  , WareNumber INT NOT NULL
  , GuestNumber INT NULL --4 grouping by receipt layout not used on client
  , Date DATETIME NOT NULL
  , OriginalDate DATETIME NOT NULL
  , LogicSortNumber SMALLINT NOT NULL
  , OrderWGRL INT NOT NULL --WareGroupSortNumberOnBill int not null,
  , OrderWRL INT NOT NULL  --WareOrderNumber int null,
  , OrderRL INT NULL
  , PriceSingle MONEY NULL
  , Summerize bit NULL
  , ProductNumber INT NULL --4 grouping by receipt layout
  , Link INT NOT NULL
  , LinkX INT NOT NULL)

insert @t --Inserting values in table variable using procedure
exec dbo.GetTransactionNew 0,@LFolioNumber,@LPkInvoice,@BillTypeNumber,@LocaleId
--retrieve additional receipt related transaction info
IF @LPkInvoice > 0 
  SELECT @LFkCustomerHotel = FkCustomerHotel FROM Invoice WITH (NOLOCK) WHERE PkInvoice = @LPkInvoice
ELSE
  SELECT @LFkCustomerHotel = HotelCustomerNumber FROM Folios WITH (NOLOCK) WHERE FolioNumber = @LFolioNumber
DECLARE @DefaultCurrencyCode char(3)
DECLARE @ExtOriginalDateInfo BIT, @ShowDepositAsWare BIT, @ShowExpensesAsWare BIT, @ShowTipsAsWare BIT  
DECLARE @HotelCurrencyNumber int
SELECT @ExtOriginalDateInfo = ISNULL(dbo.GetHotelSetupKeyValue ('ShowOriginalDateOnBill', @LFkCustomerHotel), 0)
, @ShowDepositAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowDepositAsWare', @LFkCustomerHotel), 0)
, @ShowExpensesAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowExpensesAsWare', @LFkCustomerHotel), 1)
, @ShowTipsAsWare = ISNULL(dbo.GetHotelSetupKeyValue ('ShowTipsAsWare', @LFkCustomerHotel), 0)

SELECT @HotelCurrencyNumber = CurrencyNumber FROM Customer WITH (NOLOCK) WHERE CustomerNumber = @LFkCustomerHotel
IF ISNULL(@HotelCurrencyNumber, 0) = 0
  SELECT @HotelCurrencyNumber = ISNULL(dbo.GetHotelSetupKeyValue ('HotelCurrency', @LFkCustomerHotel), 0) 
SELECT @DefaultCurrencyCode = CurrencyCode from Countries WITH (nolock) where Countrynumber = @HotelCurrencyNumber
IF ISNULL(@DefaultCurrencyCode, '') = ''
  SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) WHERE NOT CurrencyCode IS NULL ORDER BY SortNumber    

DECLARE @ResCurrencyRate MONEY, @ResCurrencyCode CHAR(3)
IF (SELECT COUNT(*) FROM @t T 
 JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = T.WareNumber
 JOIN Waregroups WITH (NOLOCK) ON Waregroups.WaregroupNumber = Ware.WaregroupNumber AND Waregroups.Payment IN (2,3,7,8) -- Rooms, Halls, Manuel Rooms and Manuel Halls
) > 0
BEGIN
  SELECT @ResCurrencyRate= LastCurrencyRate, @ResCurrencyCode = Countries.CurrencyCode
  FROM Folios WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = Folios.ReferenceNumber AND Reservations.GuestNumber = Folios.GuestNumber
    INNER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = Reservations.Currency
  WHERE FolioNumber = @LFolioNumber AND Folios.ReferenceNumber > 0
  IF @ResCurrencyRate <= 0 
    SET @ResCurrencyRate = NULL
END
;WITH XtraCTE (TransactionPk, CurrencyCode, ExchangeRate, Payment, PriceSign) AS (
  SELECT DISTINCT T.TransactionNumber, CT.CurrencyCode, CONVERT(float, TR.ExchangeRateOverwrite * 100) AS ExchangeRate, TR.Payment, -1 AS XSign 
  from @t T
    JOIN dbo.Transactions TR (NOLOCK) ON T.TransactionNumber = TR.TransactionNumber
    LEFT JOIN dbo.Countries CT WITH (NOLOCK) ON TR.CurrencyNumber = CT.CountryNumber 
  WHERE T.LogicSortNumber = 99 AND TR.Payment = 6 
  UNION
  SELECT DISTINCT T.TransactionNumber
  , CASE WHEN @ResCurrencyCode IS NULL THEN @DefaultCurrencyCode ELSE @ResCurrencyCode END AS CurrencyCode
  , CASE WHEN @ResCurrencyRate IS NULL THEN 100 ELSE @ResCurrencyRate END AS ExchangeRate
  , CASE WHEN W.Type=4 THEN 5 ELSE CASE WHEN @ShowTipsAsWare = 1 AND TR.Payment = 9 OR @ShowDepositAsWare = 1 AND TR.Payment=12 OR @ShowExpensesAsWare = 1 AND TR.Payment = 13 THEN 0 ELSE TR.Payment END END AS Payment
  , CASE WHEN WG.Payment = 5 THEN -1 ELSE 1 END * CASE WHEN TR.Payment>0 AND TR.Payment<>5 THEN -1 ELSE 1 END AS XSign
  FROM @t T
    JOIN dbo.Transactions TR (NOLOCK) ON T.TransactionNumber = TR.TransactionNumber
    LEFT JOIN dbo.Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
    LEFT JOIN dbo.Waregroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WaregroupNumber AND WG.Payment IN (2,3,7,8) -- Rooms, Halls, Manuel Rooms and Manuel Halls
  WHERE T.LogicSortNumber = 99 AND TR.Payment <> 6
), ReceiptCTE (TransactionPk, ReceiptType, ReceiptNumber) AS (
  SELECT DISTINCT T.TransactionNumber, I.ReceiptType, I.ReceiptNumber
  FROM @t T
    LEFT JOIN dbo.InvoiceTransactionXTab ITX WITH (NOLOCK) ON T.TransactionNumber = ITX.FkTransaction
    LEFT JOIN dbo.Invoice (NOLOCK) I ON ITX.FkInvoice = I.PkInvoice AND I.CancelDate IS NULL 
  WHERE ISNULL(I.ReceiptType, 0) <> 0 
)
--Selecting values from table variable and cte
SELECT T.TaxPercent AS VatPercent, T.Pieces, 
  CASE WHEN @ExtOriginalDateInfo = 0 THEN T.TextOverwrite ELSE T.TextOverwrite + ' - ' + RTrim(Convert(char,T.OriginalDate,104)) END AS Billtext, 
  T.TransactionNumber, T.Note, T.WareNumber, T.GuestNumber, D.DepartmentNumber AS Department, T.ProductNumber,
  CONVERT(Date, T.Date, 112) AS DateOB, CONVERT(Date, T.OriginalDate, 112) AS OriginalDateOB,
  T.OrderWGRL AS WaregroupSortNumberOnBill, T.OrderWRL AS WareOrderNumber, T.OrderRL AS OrderNumber,
  TR.ProductWareLinkNumber, TR.DateStamp, TR.OriginalDate, 
  T.PriceXTax*X.PriceSign*100/ExchangeRate AS PriceExclVat, T.PriceTax*X.PriceSign*100/ExchangeRate AS Vat, 
  T.Price*X.PriceSign*100/ExchangeRate AS TotalPrice, T.PriceSingle*X.PriceSign*100/ExchangeRate AS Price, 
  X.Payment, X.CurrencyCode, X.ExchangeRate,
  R.ReceiptType, R.ReceiptNumber
from @t T
  JOIN dbo.Transactions TR (NOLOCK) ON TR.TransactionNumber = t.TransactionNumber
  JOIN dbo.Departments D WITH (NOLOCK) ON TR.FK_DepartmentNumber = D.PK_Department
  JOIN XtraCTE X ON X.TransactionPk = T.TransactionNumber
  LEFT JOIN ReceiptCTE R ON R.TransactionPk = T.TransactionNumber
where LogicSortNumber = 99
order by Link
