CREATE PROCEDURE [dbo].[GetTransactionFolio] @PkFolio int, @ShowDeleted BIT, @ShowMoved BIT AS
SET NOCOUNT ON
--nb20210812 Created
;WITH GtnCTE (GTN) AS (
  SELECT T.GlobalTransactionNumber
  FROM Transactions T WITH (NOLOCK)
  WHERE T.FolioNumber = @PkFolio
), MovedCountCTE (Gtn, RecCount) AS (
  SELECT T.GlobalTransactionNumber, COUNT(*) FROM dbo.Transactions T (NOLOCK)
    JOIN GtnCTE T1 ON T.GlobalTransactionNumber = T1.GTN
  WHERE T.FolioNumber= @PkFolio AND MovedFolioNr>0
  GROUP BY T.GlobalTransactionNumber
  UNION 
  SELECT T.GlobalTransactionNumber, 0 FROM dbo.Transactions T (NOLOCK)    
    JOIN GtnCTE T1 ON T.GlobalTransactionNumber = T1.GTN
  WHERE T.FolioNumber= @PkFolio AND ISNULL(MovedFolioNr, 0)=0
), TranCTE (PkTransaction) AS (
  SELECT TransactionNumber
  FROM dbo.Transactions T WITH (NOLOCK)
    JOIN MovedCountCTE WITH (NOLOCK) ON MovedCountCTE.Gtn = T.GlobalTransactionNumber
  WHERE T.FolioNumber= @PkFolio AND T.ShowOnBillFlag = 1 AND (@ShowDeleted = 1 AND T.MovedDeleted = 1 AND MovedCountCTE.RecCount<4) --kun slettede
  UNION
  SELECT TransactionNumber
  FROM dbo.Transactions T WITH (NOLOCK)
    JOIN MovedCountCTE WITH (NOLOCK) ON MovedCountCTE.Gtn = T.GlobalTransactionNumber
  WHERE T.FolioNumber= @PkFolio AND T.ShowOnBillFlag = 1 AND (@ShowMoved = 1 AND T.MovedDeleted = 1  AND MovedCountCTE.RecCount >=4)--kun flyttede
  UNION
  SELECT TransactionNumber
  FROM dbo.Transactions T WITH (NOLOCK)
    JOIN GtnCTE T1 ON T.GlobalTransactionNumber = T1.GTN
  WHERE T.FolioNumber= @PkFolio AND T.ShowOnBillFlag = 1 AND T.MovedDeleted = 0
  EXCEPT
  SELECT TSV.TransactionNumber
  FROM dbo.TSplitByRelationView TSV
    JOIN Transactions TG (NOLOCK) ON TG.GlobalTransactionNumber = TSV.MGTN
  WHERE TSV.FolioNumber = @PkFolio AND TSV.GTN <> TSV.MGTN AND TG.FolioNumber = TSV.FolioNumber AND TG.ShowOnBillFlag=1 AND TG.MovedDeleted = 0  --except when main deleted or moved to other folio
/*
), InvoiceCTE (TransactionNumber, PkInvoice, BillNumber, InvoiceNumber) AS (
  SELECT T.TransactionNumber, I.PkInvoice, I.BillNumber, I.InvoiceNumber
*/--nb20241007
), InvoiceCTE (TransactionNumber, PkInvoice, ReceiptNumber, ReceiptType) AS (
  SELECT T.TransactionNumber, I.PkInvoice, I.ReceiptNumber, I.ReceiptType

  FROM Invoice I WITH (NOLOCK)
    JOIN InvoiceTransactionXTab ITX WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
    JOIN Transactions T ON T.TransactionNumber = ITX.FkTransaction    
  WHERE T.FolioNumber = @PkFolio AND I.CancelDate IS NULL AND I.ReceiptType <> 0 AND T.ShowOnBillFlag = 1
), TranSplitGroupCTE (Pcs, Price, SplitCount, MGTN, MovedDeleted) AS (
--/*
SELECT SUM(FLOOR(TS.Pcs))
, CAST(SUM(TS.Total * SPV.SignedValue) AS MONEY)
, COUNT(*)
--*/
/*
SELECT SPV.SignedValue
, FLOOR(TS.Pcs) AS dPcs
, CAST(TS.Price * SPV.SignedValue AS MONEY) AS dTotal
*/
--helper fields
, TS.MGTN, TS.MovedDeleted
FROM dbo.TSplitByRelationView TS 
  LEFT JOIN TSignPaymentWGWView SPV (NOLOCK) ON SPV.TransactionNumber = TS.TransactionNumber
WHERE TS.FolioNumber = @PkFolio
GROUP BY TS.MGTN, TS.MovedDeleted
)
SELECT T.DateStamp AS Date
, (CASE WHEN TSG.Pcs IS NULL THEN T.Pieces ELSE TSG.Pcs END) AS Pcs
, T.Pieces--needed on reduced records by move logic
, CAST(T.PriceOverwrite/ABS(T.Pieces) * IIF(T.Debit=1, 1, -1) * SignedValue AS MONEY) AS Price
, CAST((CASE WHEN TSG.Price IS NULL THEN T.PriceOverwrite * IIF(T.Debit=1, 1, -1) * SignedValue ELSE TSG.Price END) AS MONEY) AS Total
, T.TextOverwrite AS Text
--, InvoiceCTE.BillNumber AS Bill
--, InvoiceCTE.PkInvoice AS FkInvoice
--nb20241007
, CASE IC.ReceiptType WHEN 1 THEN 'B' WHEN 2 THEN 'I' ELSE 'P' END + '_' + CONVERT(VARCHAR, IC.ReceiptNumber) AS Receipt 
, IC.PkInvoice AS FkInvoice

, T.WareNumber
, T.FolioNumber
, T.MovedFolioNr
, T.UserIDNumber
, T.FK_DepartmentNumber
, T.HotelCustomerNumber
, T.TransactionNumber
, T.GlobalTransactionNumber
, T.DepositfollowupNumber
, T.RealDateTimeforTransaction AS RealDate
, T.Note
, T.ProductWareLinkNumber AS PWLN
, (CASE WHEN (TSG.SplitCount = 0) THEN NULL ELSE TSG.MGTN END) AS MGTN
, (CASE WHEN W.Type = 4 THEN 5 ELSE T.Payment END) AS PaymentType
, W.SortNumber AS No
, W.PrintZeroPrice
, WG.SortNumber AS Grp
, WG.Payment AS WaregroupPayment
, (CASE
--    WHEN NOT InvoiceCTE.PkInvoice IS NULL THEN 4
--nb20241007
    WHEN NOT IC.PkInvoice IS NULL AND IC.ReceiptType IN (1,2) THEN 4

    WHEN (NOT BCI.TransactionNumber IS NULL) AND (T.MovedDeleted=0) THEN 3
    WHEN T.MovedDeleted=0 AND KSX.KitchenStatementNumber > 0 AND NOT KS.Printed IS NULL THEN 5
    WHEN (T.MovedDeleted=0) THEN NULL --nb20070604 Need to be placed here otherwise the transaction will be marked as moved and it can not be moved further
    WHEN (select count(*) from Transactions TR WITH (nolock)
      where TR.GlobalTransactionNumber = T.GlobalTransactionNumber and TR.MovedDeleted=1 and NOT TR.MovedFolioNr IS NULL and TR.FolioNumber=T.FolioNumber) =
      (select count(*) from Transactions TR1 WITH (nolock)
        where TR1.GlobalTransactionNumber = T.GlobalTransactionNumber and TR1.MovedDeleted=1 and TR1.FolioNumber=T.FolioNumber)
      then 1
    ELSE 2 END) AS State
, KSX.KitchenStatementNumber AS KSN
/*
, VS.SignedValue
, T.PriceOverwrite
, TV.Total
, TV.Pcs AS TvPcs
, T.OriginalGlobalTransactionNumber
*/
FROM Transactions T WITH (NOLOCK)
  JOIN TranCTE WITH (NOLOCK) ON TranCTE.PkTransaction = T.TransactionNumber
--  LEFT JOIN InvoiceCTE WITH (NOLOCK) ON InvoiceCTE.TransactionNumber = T.transactionNumber
--nb20241007
  LEFT JOIN InvoiceCTE IC WITH (NOLOCK) ON IC.TransactionNumber = T.TransactionNumber

  LEFT JOIN TranSplitGroupCTE TSG WITH (NOLOCK) ON TSG.MGTN = T.GlobalTransactionNumber AND TSG.MovedDeleted = T.MovedDeleted
  LEFT JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = T.HotelCustomerNumber
  LEFT JOIN Ware W WITH (NOLOCK) ON T.WareNumber = W.WareNumber
  LEFT JOIN WAREGROUPS WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber
  LEFT JOIN BillCardInfo BCI WITH (NOLOCK) ON BCI.TransactionNumber = T.TransactionNumber
  LEFT JOIN TransactionKitchenStatementXTab KSX WITH (NOLOCK) ON T.TransactionNumber = KSX.TransactionNumber
  LEFT JOIN KitchenStatement KS (NOLOCK) ON KSX.KitchenStatementNumber = KS.KitchenStatementNumber
  LEFT JOIN TSignPaymentWGWView VS (NOLOCK) ON VS.TransactionNumber = T.TransactionNumber

ORDER BY T.TransactionNumber DESC