CREATE PROCEDURE dbo.OnlineGetReservations4Checkout @APkCustomerHotel INT = 0 AS
--nb20180627 AutoCheckout license for hotel present check removed. Done by Ota setting
--nb20160915 AutoCheckout license for hotel present check added
--nb20160628 ReceiplessTranCount and Preferences checks added
--NB20160525 Created
  DECLARE @SystemDate DATETIME
  SELECT @SystemDate = CONVERT(DATETIME, SystemDate, 112) FROM System (NOLOCK)
  --Customers with SMS preferences  
  SELECT RR.ReferenceNumber, RR.GuestNumber, dbo.GetTransactionCount(0, RR.ReferenceNumber, RR.GuestNumber, 0, 0) AS ReceiptlessTranCount
  , dbo.GetBalance(RR.ReferenceNumber, RR.GuestNumber) AS Balance
  , RR.DepartureDate, RR.TotalPrice, RL.TotalPrice AS RoomLinePrice
  , RAG.PaymentTypeWareNumber
  , RAG.EMAIL
  , (SELECT TOP 1 Mail FROM dbo.AddressMail WITH (NOLOCK)
    WHERE FkAddress = RAG.AddressNumber AND MailType IN (0,2,3) AND ISNULL(Mail, '') <> ''
    order by MailType DESC) AS AMail
--, dbo.GetTransactionCount(0, RR.ReferenceNumber, RR.GuestNumber, 0, 0) AS TranCount
--, CASE RR.GuestNumber WHEN 0 THEN CASE ISNULL(RL.LineNumber, 0) WHEN 0 THEN ISNULL(RR.Status, 0) ELSE CASE ISNULL(RL.StatusRL,-1) WHEN -1 THEN ISNULL(RR.Status,0) ELSE CASE WHEN ISNULL(RR.Status,0) < 5 THEN ISNULL(RL.StatusRL,0) ELSE ISNULL(RR.Status,0) END END END ELSE ISNULL(RR.Status,0) END AS Status
--, RL.StatusRL
--, RM.ReservationType
--, RR.ArrivalDate, RR.METHODOFPAYMENT, RR.SideHouse, RR.GuestType AS GuestTypeNumber 
-- , CP.CustomerNumber, CP.PreferenceType, CP.PreferencePriority
  FROM dbo.Reservations RR WITH (NOLOCK) 
    INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RR.ReferenceNumber=RM.ReferenceNumber AND RR.ReferenceNumber>0 AND Status IN (5)
      AND (RR.GuestNumber=0 OR RR.GuestNumber>0 AND RR.DepartureDate<=CONVERT(VARCHAR, @SystemDate, 112))
    INNER JOIN dbo.ReservationRoomlineXtab RLX WITH (NOLOCK) ON RLX.ReferenceNumber=RR.ReferenceNumber AND RLX.GuestNumber=RR.GuestNumber 
    INNER JOIN dbo.ReservationRoomline RL WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.StatusRL IN (5)
      AND (RLX.GuestNumber>0 OR RR.GuestNumber = 0 AND RLX.GuestNumber=0 AND RL.DepartureDate<=CONVERT(VARCHAR, @SystemDate, 112))
    INNER JOIN dbo.ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.ReferenceNumber = RR.ReferenceNumber AND RAX.GuestNumber = RR.GuestNumber
    INNER JOIN dbo.ReservationAddress RAG WITH (NOLOCK) ON RAG.AddressNumber = RAX.AddressNumber AND RAG.ADDRESSTYPE = 1 --GuestAddress used
    INNER JOIN dbo.CustomerPreferences CP WITH (NOLOCK) ON CP.CustomerNumber = RAG.Link AND CP.PreferenceType = 11 --AND CP.PreferencePriority > 100
    --INNER JOIN dbo.Customer CAG WITH (NOLOCK) ON RAG.Link = CAG.CustomerNumber
    --LEFT JOIN dbo.AddressMail AM WITH (NOLOCK) ON AM.FkAddress = RAG.AddressNumber
  WHERE @APkCustomerHotel = 0 OR RM.FkCustomerHotel = @APkCustomerHotel
  UNION
  SELECT RR.ReferenceNumber, RR.GuestNumber, dbo.GetTransactionCount(0, RR.ReferenceNumber, RR.GuestNumber, 0, 0) AS ReceiptlessTranCount
  , dbo.GetBalance(RR.ReferenceNumber, RR.GuestNumber) AS Balance
  , RR.DepartureDate, RR.TotalPrice, RL.TotalPrice AS RoomLinePrice
  , RAG.PaymentTypeWareNumber
  , RAG.EMAIL
  , (SELECT TOP 1 Mail FROM dbo.AddressMail WITH (NOLOCK)
    WHERE FkAddress = RAG.AddressNumber AND MailType IN (0,2,3) AND ISNULL(Mail, '') <> ''
    order by MailType DESC) AS AMail
--, dbo.GetTransactionCount(0, RR.ReferenceNumber, RR.GuestNumber, 0, 0) AS TranCount
--, CASE RR.GuestNumber WHEN 0 THEN CASE ISNULL(RL.LineNumber, 0) WHEN 0 THEN ISNULL(RR.Status, 0) ELSE CASE ISNULL(RL.StatusRL,-1) WHEN -1 THEN ISNULL(RR.Status,0) ELSE CASE WHEN ISNULL(RR.Status,0) < 5 THEN ISNULL(RL.StatusRL,0) ELSE ISNULL(RR.Status,0) END END END ELSE ISNULL(RR.Status,0) END AS Status
--, RL.StatusRL
--, RM.ReservationType
--, RR.ArrivalDate, RR.METHODOFPAYMENT, RR.SideHouse, RR.GuestType AS GuestTypeNumber 
  FROM dbo.Reservations RR WITH (NOLOCK) 
    INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RR.ReferenceNumber=RM.ReferenceNumber AND RR.ReferenceNumber>0 AND Status IN (5)
      AND (RR.GuestNumber=0 OR RR.GuestNumber>0 AND RR.DepartureDate<=CONVERT(VARCHAR, @SystemDate, 112))
    INNER JOIN dbo.ReservationRoomlineXtab RLX WITH (NOLOCK) ON RLX.ReferenceNumber=RR.ReferenceNumber AND RLX.GuestNumber=RR.GuestNumber 
    INNER JOIN dbo.ReservationRoomline RL WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.StatusRL IN (5)
      AND (RLX.GuestNumber>0 OR RR.GuestNumber = 0 AND RLX.GuestNumber=0 AND RL.DepartureDate<=CONVERT(VARCHAR, @SystemDate, 112))
	  AND dbo.GetBalance(RR.ReferenceNumber, RR.GuestNumber) = 0
    INNER JOIN dbo.ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.ReferenceNumber = RR.ReferenceNumber AND RAX.GuestNumber = RR.GuestNumber
    INNER JOIN dbo.ReservationAddress RAG WITH (NOLOCK) ON RAG.AddressNumber = RAX.AddressNumber AND RAG.ADDRESSTYPE = 1 AND ISNULL(RAG.Link, 0) = 0--GuestAddress used
--    INNER JOIN dbo.CustomerPreferences CP WITH (NOLOCK) ON CP.CustomerNumber = RAG.Link AND CP.PreferenceType = 11 AND CP.PreferencePriority > 100
    --INNER JOIN dbo.Customer CAG WITH (NOLOCK) ON RAG.Link = CAG.CustomerNumber
    --LEFT JOIN dbo.AddressMail AM WITH (NOLOCK) ON AM.FkAddress = RAG.AddressNumber
  WHERE @APkCustomerHotel = 0 OR RM.FkCustomerHotel = @APkCustomerHotel
  ORDER BY RR.ReferenceNumber DESC
  OPTION (FAST 10)
