CREATE VIEW [dbo].[TExternalReservationView] AS
/*
  Create by Kent Johansen Juli 2012
  
  Changes:
  KJ 20120822: Ext Added status
  KJ 20120813: Ext Added guest email  
*/

SELECT     r.ReferenceNumber, CASE WHEN r.GuestNumber = 0 THEN rr.ArrivalDate ELSE r.ArrivalDate END AS ArrivalDate, 
                      CASE WHEN r.GuestNumber = 0 THEN rr.ArrivalTime ELSE r.ArrivalTime END AS ArrivalTime, 
                      CASE WHEN r.GuestNumber = 0 THEN rr.DepartureDate ELSE r.DepartureDate END AS DepartureDate, 
                      CASE WHEN r.GuestNumber = 0 THEN rr.DepartureTime ELSE r.DepartureTime END AS DepartureTime, 
                      CASE WHEN r.GuestNumber = 0 THEN LineProduct.ProductName ELSE ResProduct.ProductName END AS ProductName, 
                      CASE WHEN r.GuestNumber = 0 THEN LineProduct2.ProductName ELSE ResProduct2.ProductName END AS ProductName2, 
                      CASE WHEN r.GuestNumber = 0 THEN LineProduct3.ProductName ELSE ResProduct3.ProductName END AS ProductName3, 
                      CASE WHEN r.GuestNumber = 0 THEN LineProduct4.ProductName ELSE ResProduct4.ProductName END AS ProductName4, 
                      CASE WHEN r.GuestNumber = 0 THEN rr.Price ELSE r.Price END AS Price, CASE WHEN r.GuestNumber = 0 THEN rr.FirstPrice2 ELSE r.FirstPrice2 END AS Price2, 
                      CASE WHEN r.GuestNumber = 0 THEN rr.FirstPrice3 ELSE r.FirstPrice3 END AS Price3, 
                      CASE WHEN r.GuestNumber = 0 THEN rr.FirstPrice4 ELSE r.FirstPrice4 END AS Price4, ra1.LASTNAME, ra1.FIRSTNAME, dbo.ROOMTYPES.Type, ra1.Email,
                      r.Status
FROM         dbo.RESERVATIONS AS r WITH (NOLOCK) INNER JOIN
                      dbo.RESERVATIONMAIN WITH (NOLOCK) ON dbo.RESERVATIONMAIN.REFERENCENUMBER = r.ReferenceNumber INNER JOIN
                      dbo.RESERVATIONROOMLINEXTAB AS rrx WITH (NOLOCK) ON r.ReferenceNumber = rrx.ReferenceNumber AND r.GuestNumber = rrx.GuestNumber INNER JOIN
                      dbo.RESERVATIONROOMLINE AS rr WITH (NOLOCK) ON rr.LINENUMBER = rrx.LINENUMBER LEFT OUTER JOIN
                      dbo.ROOMS WITH (NOLOCK) ON dbo.ROOMS.RoomNumber = rr.RoomNumber LEFT OUTER JOIN
                      dbo.ROOMTYPES WITH (NOLOCK) ON dbo.ROOMTYPES.RoomTypeNumber = rr.BookedTypeNumber LEFT OUTER JOIN
                      dbo.RESERVATIONADDRESSXTAB AS rax1 WITH (NOLOCK) ON r.ReferenceNumber = rax1.REFERENCENUMBER AND r.GuestNumber = rax1.GUESTNUMBER AND 
                      rax1.AddressTypeNumber = 1 LEFT OUTER JOIN
                      dbo.RESERVATIONADDRESS AS ra1 WITH (NOLOCK) ON rax1.ADDRESSNUMBER = ra1.AddressNumber LEFT OUTER JOIN
                      dbo.Products AS ResProduct WITH (NOLOCK) ON ResProduct.ProductNumber = r.ProductNumber LEFT OUTER JOIN
                      dbo.Products AS LineProduct WITH (NOLOCK) ON LineProduct.ProductNumber = rr.ProductNumber LEFT OUTER JOIN
                      dbo.Products AS ResProduct2 WITH (NOLOCK) ON ResProduct2.ProductNumber = r.ProductNumber2 LEFT OUTER JOIN
                      dbo.Products AS LineProduct2 WITH (NOLOCK) ON LineProduct2.ProductNumber = rr.ProductNumber2 LEFT OUTER JOIN
                      dbo.Products AS ResProduct3 WITH (NOLOCK) ON ResProduct3.ProductNumber = r.ProductNumber3 LEFT OUTER JOIN
                      dbo.Products AS LineProduct3 WITH (NOLOCK) ON LineProduct3.ProductNumber = rr.ProductNumber3 LEFT OUTER JOIN
                      dbo.Products AS ResProduct4 WITH (NOLOCK) ON ResProduct4.ProductNumber = r.ProductNumber4 LEFT OUTER JOIN
                      dbo.Products AS LineProduct4 WITH (NOLOCK) ON LineProduct4.ProductNumber = rr.ProductNumber4
WHERE     (ISNULL(rr.NameListCreateFlag, - 1) = 0) OR
                      (rr.NameListCreateFlag = 1)