CREATE VIEW DBO.TFolioTransactionsView
AS
-- kj20201012: Added SumWithoutVAT. Used for owner calculation report
-- nb20121219: Invoice
-- nb20120502: Optimization to avoid a join in Delphi and MovedFromText changed to be empty if nothing was moved
-- nb20120402: Show all transactions independant on relations to other tables
-- KJ20110111: Owner calculation can contain / in Note
-- nb20101222: Added helperfields for MasterLogin user for better overview of Bill Layout templates
-- KJ20090206: Added TransactionPayment
-- nb20081003 Optimized show of moved transactions
-- nb20080805 Duplicate records caused by overlapping bill series removed distinct and last disabled and not usable join 
-- KJ 20080630 Bug: Fix for transactions shown multible times (Ontime case #2727)
--nb20080610 reengeniered not instal. are domino
--nb20080421 ext: handle moved and deleted transactions changed to take care of compound ware transactions
-- TL 20071129: BUG: Fix for double transactions when having bill number clash between hotels (Added join condition on HotelCustomerNumber between Invoices and Folios)
--nb20070410 FK_DepartmentNumber
--20061101 NB Added Tranfer as ware logic
--20060331 NB Added BillRelation for BillDate
--20051221 NB Added Hotel name displayed field
--20051207 NB Added HotelCustomerNumber Field
--20041206 NB Added select distinct and some new kitchenstatementnumber to prevent same transaction twis
--20040928 NB Changed field now generated by combination of PrinterNumber on ware and Changed on kitchentransaction 
--20040916 NB added KitchenTransaction field to find out if it is a kitchen transaction
--20040831 KS added 'isnull' to select ProductWareLinkNumber
--20040721 KS Changed "FROM TransactionKitchenStatementXTab RIGHT OUTER JOIN" to "FROM TransactionKitchenStatementXTab (NOLOCK) RIGHT OUTER JOIN"
/*
'0': Ordinary
'1': Moved
'2': Deleted
'3': CC transactions

*/
SELECT
 (CASE 
    WHEN (NOT BCI.TransactionNumber IS NULL) AND (T.MovedDeleted=0) THEN 3
    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 Mark
, CASE 
    WHEN NOT (
      SELECT TOP 1 ITX.FkInvoice FROM InvoiceTransactionXTab ITX WITH (NOLOCK)
        LEFT JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
      WHERE ITX.FkTransaction = T.TransactionNumber AND I.CancelDate IS NULL AND I.ReceiptType <> 0 
      ORDER BY I.PkInvoice DESC) IS NULL THEN 1
    WHEN KSX.KitchenStatementNumber > 0 THEN 2
    ELSE 0 END AS TransPrinted
, T.DateStamp AS TransDate
, T.RealDateTimeforTransaction AS RealDate
, FLOOR(T.Pieces) As Pieces
, WG.SortNumber AS WaregroupSortNumber 
, W.SortNumber AS WareSortNumber
, W.PrintZeroPrice
, T.TextOverwrite AS TransText
, T.Note
, T.PriceOverwrite AS Amount 
, T.PriceOverwrite As TotalPrice
, 
( 
  SELECT SUM(CASE WHEN TransactionsVATSum.Pieces < 0 THEN TransactionsVATSum.PriceOverwrite*-1 ELSE TransactionsVATSum.PriceOverwrite END)
  FROM Transactions AS TransactionsVATSum WITH (NOLOCK)
  INNER JOIN Accounts AccountsVATSum WITH (NOLOCK) ON AccountsVATSum.AccountNumber = TransactionsVATSum.AccountNumber
  WHERE TransactionsVATSum.ProductWareLinkNumber = T.ProductWareLinkNumber
  AND TransactionsVATSum.FolioNumber = F1.FolioNumber
  AND AccountsVATSum.AccountType = 3
  AND T.Payment = 0
) AS SumWithoutVAT
, T.FolioNumber
, T.GlobalTransactionNumber
, T.TransactionNumber
, (CASE WHEN W.Type = 4 THEN 5 ELSE T.Payment END) AS PaymentTypeNumber
, WG.WareGroupNumber AS WaregroupNumber
, T.WareNumber
, CONVERT(VARCHAR, T.MovedFolioNr) AS MovedFromText --Edited In delphi
, (SELECT TOP 1 CASE I.ReceiptType WHEN 1 THEN 'B' WHEN 2 THEN 'I' ELSE 'P' END + '_' + CONVERT(VARCHAR, I.ReceiptNumber) FROM InvoiceTransactionXTab ITX WITH (NOLOCK)
    LEFT JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
  WHERE ITX.FkTransaction = T.TransactionNumber AND I.CancelDate IS NULL
  ORDER BY I.PkInvoice DESC) AS Receipt 
, T.OriginalDate
, T.HotelCustomerNumber
, isnull(T.ProductWareLinkNumber,0) as ProductWareLinkNumber
, CAST(CASE WHEN WG.Payment IN (2,3) THEN 1 ELSE 0 END AS BIT) AS IsProductTransaction
, T.CurrencyNumber AS CountryNumber
, T.DepositFollowupNumber
, T.Debit
, T.FK_DepartmentNumber AS FK_Department
, T.MovedFolioNr
, T.GuestNumber
, T.ShowOnBillFlag
, T.MovedDeleted
, W.DiscountValue
, WG.Payment AS WaregroupPayment
, KSX.Note AS NoteText
, KSX.KitchenStatementNumber
, RA.AddressNumber AS MovedFolioAddressNumber
, RA.FIRSTNAME AS MovedFolioAddressFirstName
, RA.LASTNAME AS MovedFolioAddressLastName
, F.ReferenceNumber AS MovedFolioReferenceNumber
, F.GuestNumber AS MovedFolioGuestNumber
, Cast((CASE WHEN (W.PrinterNumber > 0 OR KSX.Changed = 1) THEN 1 ELSE 0 END) AS Bit) AS KitchenChanged
, KSX.TransactionNumber AS KitchenTransactionNumber
, P.UserIdCode AS UserName
, C.LastName
, (SELECT TOP 1 ITX.FkInvoice FROM InvoiceTransactionXTab ITX WITH (NOLOCK)
    LEFT JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
  WHERE ITX.FkTransaction = T.TransactionNumber AND I.CancelDate IS NULL
  ORDER BY I.PkInvoice DESC) AS FkInvoice
, (SELECT TOP 1 I.CreatedDate FROM InvoiceTransactionXTab ITX WITH (NOLOCK)
    LEFT JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
  WHERE ITX.FkTransaction = T.TransactionNumber AND I.CancelDate IS NULL
  ORDER BY I.PkInvoice DESC) AS BillDate

, TR.RoomTypeNumber
FROM BillCardInfo BCI WITH (NOLOCK) 
  RIGHT JOIN TRANSACTIONS T WITH (NOLOCK) 
  LEFT JOIN Ware W WITH (NOLOCK) ON T.WareNumber = W.WareNumber 
  LEFT JOIN WAREGROUPS WG WITH (NOLOCK) ON W.WareGroupNumber = WG.WareGroupNumber ON 
    BCI.TransactionNumber = T.TransactionNumber 
  LEFT JOIN TransactionKitchenStatementXTab KSX WITH (NOLOCK) ON T.TransactionNumber = KSX.TransactionNumber 
  INNER JOIN FOLIOS F1 WITH (NOLOCK) ON T.Folionumber = F1.Folionumber
  LEFT JOIN FOLIOS F WITH (NOLOCK) ON T.MovedFolioNr = F.FolioNumber 
  LEFT JOIN RESERVATIONADDRESS RA WITH (NOLOCK) ON F.ReservationAddressNumber = RA.AddressNumber AND 
    F.ReservationAddressNumber = RA.AddressNumber
  LEFT JOIN Personel P WITH (NOLOCK) ON P.UserIdNumber = T.UserIdNumber
  LEFT JOIN Customer C WITH (NOLOCK) ON C.CustomerNumber = T.HotelCustomerNumber
  LEFT JOIN dbo.ROOMS TR WITH (NOLOCK) ON CAST(TR.Number AS VARCHAR(50)) = T.Note AND C.HotelNumber = TR.HotelNumber
