CREATE VIEW [dbo].[TTransactionView]
AS
-- nb20120502: Created
/*
State:
0= Ordinary
1= Moved
2= Deleted
3= CC transactions
4= Printed CC transactions
5= Printed
6= Kitchen Printed
*/
SELECT
  (CASE 
    WHEN (NOT BCI.TransactionNumber IS NULL) AND (T.MovedDeleted=0) THEN 
      CASE WHEN ISNULL(ITX.FkInvoice, 0) > 0 THEN 4 ELSE 3 END 
    WHEN T.MovedDeleted=0 AND ISNULL(ITX.FkInvoice, 0) > 0 AND I.ReceiptType <> 0 THEN 5 
    WHEN T.MovedDeleted=0 AND KSX.KitchenStatementNumber > 0 THEN 6
    WHEN (T.MovedDeleted=0) THEN 0 --nb20070604 Need to be placed here otherwise the transaction will be marked as moved and it can't 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 T.MovedDeleted 
    ELSE 2 END) AS State
, T.DateStamp AS Date
, WG.SortNumber AS Grp 
, W.SortNumber AS No
, W.PrintZeroPrice
, T.TextOverwrite AS Text
, T.Note
--, FLOOR(T.Pieces) As Pcs
--, CAST(T.PriceOverwrite/FLOOR(T.Pieces) AS MONEY) AS Price 
--, CAST(T.PriceOverwrite/FLOOR(T.Pieces)*ABS(FLOOR(T.Pieces)) AS MONEY) As Total
, FLOOR(T.Pieces) As Pcs--* (CASE WHEN (T.Debit = 1 AND NOT A.AccountNumber IS NULL) THEN 1 ELSE -1 END) As Pcs
, CAST(T.PriceOverwrite * (CASE WHEN (T.Debit = 1 AND NOT A.AccountNumber IS NULL) THEN 1 ELSE -1 END)/FLOOR(ABS(T.Pieces)) AS MONEY) AS Price 
, ROUND(T.PriceOverwrite * (CASE WHEN (T.Debit = 1 AND NOT A.AccountNumber IS NULL) THEN 1 ELSE -1 END),2) As Total
, ITX.FkInvoice AS Bill
, P.UserIdCode AS UserID
--, D.DepartmentNumber AS Dep
, C.LastName
--Helper fields
, T.TransactionNumber
, T.FolioNumber
, T.MovedFolioNr
, T.WareNumber
, T.HotelCustomerNumber
, T.MovedDeleted
, T.ShowOnBillFlag
, T.GlobalTransactionNumber AS MGTN
, T.ProductWareLinkNumber AS PWLN
, (CASE WHEN W.Type = 4 THEN 5 ELSE T.Payment END) AS PaymentType
, KSX.KitchenStatementNumber AS KSN
, T.GlobalTransactionNumber
, T.DepositfollowupNumber
, T.RealDateTimeforTransaction AS RealDate
, WG.Payment AS WaregroupPayment
, T.FK_DepartmentNumber AS FkDepartment
FROM FOLIOS F1 WITH (NOLOCK) 
  INNER JOIN TRANSACTIONS T WITH (NOLOCK) ON T.Folionumber = F1.Folionumber
    AND (T.GlobalTransactionNumber = T.OriginalGlobalTransactionNumber OR T.OriginalGlobalTransactionNumber NOT IN (
    SELECT DISTINCT T1.OriginalGlobalTransactionNumber FROM Transactions T1 (NOLOCK) 
    WHERE T1.FolioNumber = F1.FolioNumber AND T1.ShowOnBillFlag = 1 AND OriginalGlobalTransactionNumber IN (
      SELECT GlobalTransactionNumber FROM Transactions T2 (NOLOCK) 
      WHERE T2.FolioNumber = F1.FolioNumber AND T2.ShowOnBillFlag = 1 AND T2.MovedDeleted = 0)
      )
    )
--  LEFT JOIN Departments D WITH (NOLOCK) ON D.PK_Department = T.FK_DepartmentNumber
  LEFT JOIN Accounts A WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber AND A.AccountType = 5
  LEFT JOIN Ware W WITH (NOLOCK) ON T.WareNumber = W.WareNumber 
  LEFT JOIN WAREGROUPS WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber
  LEFT JOIN Personel P WITH (NOLOCK) ON P.UserIdNumber = T.UserIdNumber
  LEFT JOIN InvoiceTransactionXTab ITX  WITH (NOLOCK) on ITX.FkTransaction = T.TransactionNumber
  LEFT JOIN Invoice I WITH (NOLOCK) ON I.PkInvoice = ITX.FkInvoice
  LEFT JOIN BillCardInfo BCI WITH (NOLOCK) ON BCI.TransactionNumber = T.TransactionNumber
  LEFT JOIN TransactionKitchenStatementXTab KSX WITH (NOLOCK) ON T.TransactionNumber = KSX.TransactionNumber 
  LEFT JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = T.HotelCustomerNumber
UNION
SELECT
  0 AS State
, DATEADD(second,1,TOG.DateStamp) AS Date
, WG.SortNumber AS Grp 
, W.SortNumber AS No
, W.PrintZeroPrice
, T.TextOverwrite AS Text
, T.Note
, SUM(FLOOR(T.Pieces)) AS Pcs
, CAST(T.PriceOverwrite/FLOOR(T.Pieces) AS MONEY) AS Price 
, CAST(SUM(T.PriceOverwrite/FLOOR(T.Pieces)*ABS(FLOOR(T.Pieces))) AS MONEY) AS Total
, ITX.FkInvoice AS Bill
, NULL AS UserID
, C.LastName
--helper fields
, NULL AS TransactionNumber
, T.FolioNumber
, NULL AS MovedFolioNr
, T.WareNumber
, T.HotelCustomerNumber
, T.MovedDeleted
, T.ShowOnBillFlag
, T.OriginalGlobalTransactionNumber AS MGTN
, T.ProductWareLinkNumber AS PWLN
, NULL AS PaymentType
, NULL AS KSN
, NULL AS GlobalTransactionNumber
, NULL AS DepositfollowupNumber
, NULL AS RealDate
, NULL AS WaregroupPayment
, NULL AS FkDepartment
FROM FOLIOS F1 WITH (NOLOCK) 
  INNER JOIN TRANSACTIONS T WITH (NOLOCK) ON T.Folionumber = F1.Folionumber
    AND T.GlobalTransactionNumber <> T.OriginalGlobalTransactionNumber AND T.OriginalGlobalTransactionNumber IN (
      SELECT DISTINCT T1.OriginalGlobalTransactionNumber FROM Transactions T1 (NOLOCK) 
      WHERE T1.FolioNumber = F1.FolioNumber AND T1.ShowOnBillFlag = 1 AND OriginalGlobalTransactionNumber IN (
        SELECT GlobalTransactionNumber FROM Transactions T2 (NOLOCK) 
        WHERE T2.FolioNumber = F1.FolioNumber AND T2.ShowOnBillFlag = 1 AND T2.MovedDeleted = 0)
) 
  INNER JOIN Transactions TOG WITH (NOLOCK) ON TOG.GlobalTransactionNumber = T.OriginalGlobalTransactionNumber AND TOG.ShowOnBillFlag = 1 
  --nb20120108 Added to prevent duplicate records when transactions moved several times away and back
  AND TOG.MovedDeleted=0
  
  LEFT JOIN Ware W WITH (NOLOCK) ON T.WareNumber = W.WareNumber 
  LEFT JOIN WAREGROUPS WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber 
  LEFT JOIN InvoiceTransactionXTab ITX  WITH (NOLOCK) on ITX.FkTransaction = T.TransactionNumber
  LEFT JOIN Invoice I WITH (NOLOCK) ON I.PkInvoice = ITX.FkInvoice
  LEFT JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = T.HotelCustomerNumber
WHERE T.ShowOnBillFlag = 1 AND T.MovedDeleted = 0
GROUP BY TOG.DateStamp, WG.SortNumber, W.SortNumber, W.PrintZeroPrice, T.TextOverwrite, T.Note, (T.PriceOverwrite/FLOOR(T.Pieces))
, ITX.FkInvoice, C.LastName
, T.FolioNumber, T.WareNumber, T.HotelCustomerNumber, T.MovedDeleted, T.ShowOnBillFlag
, T.OriginalGlobalTransactionNumber, T.ProductWareLinkNumber
