CREATE PROCEDURE [dbo].[GetTransactionFolio] @PkFolio int, @ShowDeleted BIT, @ShowMoved BIT AS
SET NOCOUNT ON
--nb20210812 Created
;WITH GtnCTE (GTN) AS (
  SELECT Min(GlobalTransactionNumber)
  FROM Transactions T WITH (NOLOCK)
  WHERE FolioNumber=@PkFolio
  GROUP BY OriginalGlobalTransactionNumber
  UNION
  SELECT T.GlobalTransactionNumber
  FROM Transactions T WITH (NOLOCK)
    LEFT JOIN Transactions TS WITH (NOLOCK) ON TS.GlobalTransactionNumber = T.OriginalGlobalTransactionNumber
  WHERE T.FolioNumber=@PkFolio AND (TS.FolioNumber <> T.FolioNumber AND T.ShowOnBillFlag = 1 OR TS.MovedDeleted = 1)
), MovedCountCTE (Gtn, RecCount) AS (
  SELECT T.GlobalTransactionNumber, COUNT(*) FROM dbo.Transactions T (NOLOCK)
  WHERE T.FolioNumber= @PkFolio AND MovedFolioNr>0
  GROUP BY T.GlobalTransactionNumber
  UNION 
  SELECT T.GlobalTransactionNumber, 0 FROM dbo.Transactions T (NOLOCK)    
  WHERE T.FolioNumber= @PkFolio AND ISNULL(MovedFolioNr, 0)=0
), InvoiceCTE (TransactionNumber, PkInvoice, BillNumber, InvoiceNumber) AS (
  SELECT T.TransactionNumber, I.PkInvoice, I.BillNumber, I.InvoiceNumber
  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
)
SELECT T.DateStamp AS Date
, (CASE WHEN TV.Pcs IS NULL THEN T.Pieces ELSE TV.Pcs END) AS Pcs
/*
, CAST(T.PriceOverwrite/T.Pieces * VS.SignedValue AS MONEY) AS Price
, CAST((CASE WHEN TV.Total IS NULL THEN T.PriceOverwrite * T.Pieces / ABS(T.Pieces) * VS.SignedValue ELSE TV.Total END) AS MONEY) AS Total
*/--CR214037
, CAST(T.PriceOverwrite/ABS(T.Pieces) * VS.SignedValue AS MONEY) AS Price
, CAST((CASE WHEN TV.Total IS NULL THEN T.PriceOverwrite * VS.SignedValue ELSE TV.Total END) AS MONEY) AS Total

, T.TextOverwrite AS Text
, InvoiceCTE.BillNumber AS Bill
, InvoiceCTE.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 (TV.SplitCount = 0) THEN NULL ELSE TV.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
    WHEN (NOT BCI.TransactionNumber IS NULL) AND (T.MovedDeleted=0) THEN 3
    WHEN T.MovedDeleted=0 AND KSX.KitchenStatementNumber > 0 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
FROM Transactions T WITH (NOLOCK)
  JOIN GtnCTE WITH (NOLOCK) ON GtnCTE.GTN = T.GlobalTransactionNumber
  JOIN MovedCountCTE WITH (NOLOCK) ON MovedCountCTE.Gtn = T.GlobalTransactionNumber
  LEFT JOIN InvoiceCTE WITH (NOLOCK) ON InvoiceCTE.TransactionNumber = T.transactionNumber
  --LEFT JOIN TTransactionGoView TV WITH (NOLOCK) ON TV.FolioNumber = T.FolioNumber AND TV.MGTN = T.OriginalGlobalTransactionNumber
  LEFT JOIN TTransactionJView TV WITH (NOLOCK) ON TV.FolioNumber = T.FolioNumber AND TV.GTN = T.GlobalTransactionNumber
    AND T.ShowOnBillFlag = 1 AND T.MovedDeleted = 0

  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 TSignPaymentWGWView VS (NOLOCK) ON VS.TransactionNumber = T.TransactionNumber

WHERE T.FolioNumber= @PkFolio
  AND T.ShowOnBillFlag = 1 AND (@ShowDeleted = 1 AND @ShowMoved = 1
    OR ((@ShowDeleted = 1 OR @ShowMoved = 1) AND T.MovedDeleted = 0
    OR (@ShowDeleted = 1 AND T.ShowOnBillFlag = 1 AND T.MovedDeleted = 1 AND MovedCountCTE.RecCount<4) --kun slettede
    OR (@ShowMoved = 1 AND T.ShowOnBillFlag = 1 AND MovedCountCTE.RecCount >=4))--kun flyttede
    OR (@ShowDeleted = 0 AND @ShowMoved = 0 AND T.MovedDeleted = 0))
ORDER BY T.TransactionNumber
