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 @DebugLevel TINYINT
SET @DebugLevel = 0
IF @DebugLevel >= 1 
BEGIN
  PRINT '---- GetTransactions Pre ---'
END

DECLARE @LFolioNumber INT, @LPkInvoice INT, @LFkCustomerHotel INT, @LPkReceiptLayout SmallInt, @LLocaleId Int
SET @LFolioNumber = ISNULL(@FolioNumber, 0)
SET @LPkInvoice = @PkInvoice
SET @LPkReceiptLayout = @BillTypeNumber
SET @LLocaleId = @LocaleId
IF @LPkInvoice > 0 AND @LFolioNumber <= 0 
BEGIN
  SELECT @LFolioNumber= MIN(Folionumber) FROM Transactions T WITH (NOLOCK) 
    INNER JOIN InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkTransaction = T.TransactionNumber
  WHERE ITX.FkInvoice=@LPkInvoice
  SET @LFolioNumber = ISNULL(@LFolioNumber, 0)
END  
IF @DebugLevel >= 1 
BEGIN
  PRINT '@LFolioNumber = ' + CONVERT(NVARCHAR, ISNULL(@LFolioNumber, 0))
  PRINT '@LPkInvoice = ' + CONVERT(NVARCHAR, ISNULL(@LPkInvoice, 0))
END
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,@LPkReceiptLayout,@LLocaleId
--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 Transactions T 
    JOIN dbo.Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
    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.FolioNumber = @LFolioNumber AND T.MovedDeleted = 0 AND T.ShowOnBillFlag = 1 --AND (T.PriceOverwrite <> 0 OR W.PrintZeroPrice = 1)
) > 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

IF @DebugLevel >= 1 
BEGIN
  PRINT '@ResCurrencyRate = ' + CONVERT(NVARCHAR, ISNULL(@ResCurrencyRate, 0))
END

;WITH XtraCTEPre (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 AND TR.Payment<>17 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
), XtraCTE (TransactionPk, CurrencyCode, ExchangeRate, XRateInt, Payment, PriceSign) AS (
  SELECT TransactionPk, CurrencyCode, ExchangeRate
  , CASE WHEN ISNULL(@ResCurrencyRate, 100) = 100 THEN 100 ELSE ExchangeRate END
  , Payment, PriceSign
  FROM XtraCTEPre
), 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.Link, 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/X.XRateInt AS PriceExclVat, T.PriceTax*X.PriceSign*100/X.XRateInt AS Vat, 
  T.Price*X.PriceSign*100/X.XRateInt AS TotalPrice, T.PriceSingle*X.PriceSign*100/X.XRateInt 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 T.Link
