
CREATE VIEW DBO.TMultiPurposeView AS
/*
  NOTE: Is still used for MultiChange in Picasso

  Changes:
  20060626: JP: OPTIMIZE: added dbo. prefix to tables
  20050428: JP: added GuestIsSmoker
  20050216: JP: added Price again. Interface uses it
  20050127: JP: added PurposeCode field again, since MultiChange uses it
  20050103: JP: added SpecRoomNumber field again for interface since Picasso does not use this view any longer on the plans
  20041227: JP: optimized alittle (5%)
  20041209: KS:added column RESERVATIONS.AltResNo again
  20041124: JP: removed a lot of joins and implemented them in Delphi code
  20041123: JP: removed 5 unused columns
  20041123: JP: removed use of ReservationAddressXtabView and inner selects. DGI database went from 02:17 to 00:41 minutes:seconds! 72833 rows
  20041103: JP: renamed ReservationAddressXtabView
  20041004: JP: fixed problems with having no guestaddress 

  NOTES: Selecting from address for guest through 3 subqueries is done because of design problem in database (addresstype should have been in xtab). If you join you won't get any hits if no guestaddress!
*/
SELECT
dbo.Reservations.GroupName AS ResGroupName,
dbo.RESERVATIONS.AltResNo, dbo.RESERVATIONS.PURPOSECODE, 
dbo.RESERVATIONS.ReferenceNumber, dbo.RESERVATIONS.GuestNumber, dbo.RESERVATIONS.Status, CAST(dbo.RESERVATIONS.ArrivalDate AS varchar(10)) AS ArrivalDate, dbo.RESERVATIONS.ArrivalTime,
CAST(dbo.RESERVATIONS.DepartureDate AS VARCHAR(10)) AS DepartureDate, dbo.RESERVATIONS.DepartureTime, dbo.RESERVATIONS.Adults, dbo.RESERVATIONS.Children1, dbo.RESERVATIONS.Children2, 
dbo.RESERVATIONS.Price, dbo.RESERVATIONS.GuestIsSmoker,
dbo.RESERVATIONS.TotalPrice, dbo.RESERVATIONS.CREDITMAX, 
dbo.RESERVATIONS.POINTOFSALE, dbo.RESERVATIONS.MARKETSEGMENT,dbo.RESERVATIONS.METHODOFPAYMENT,
dbo.RESERVATIONS.Day2, dbo.RESERVATIONS.ProductNumber, dbo.RESERVATIONS.SideHouse, dbo.Reservations.GuestType AS GuestTypeNumber,

dbo.RESERVATIONROOMLINE.BookedTypeNumber, dbo.RESERVATIONROOMLINE.NumberOfRooms AS SpecNumberOfRooms, dbo.RESERVATIONROOMLINE.ArrivalDate AS SpecArrivalDate, dbo.RESERVATIONROOMLINE.ArrivalTime AS SpecArrivalTime, 
dbo.RESERVATIONROOMLINE.DepartureDate AS SpecDepartureDate, dbo.RESERVATIONROOMLINE.DepartureTime AS SpecDepartureTime, dbo.RESERVATIONROOMLINE.Adults AS SpecAdults, 
dbo.RESERVATIONROOMLINE.Children1 AS SpecChildren1, dbo.RESERVATIONROOMLINE.Children2 AS SpecChildren2,dbo.RESERVATIONROOMLINE.Day2 AS SpecDay2, 
dbo.RESERVATIONROOMLINE.LINENUMBER, dbo.RESERVATIONROOMLINE.ProductNumber AS SpecProductNumber, 
dbo.RESERVATIONROOMLINE.TotalPrice AS SpecTotalPrice, 
dbo.RESERVATIONROOMLINE.RoomNumber AS SpecRoomNumberID, 

dbo.RESERVATIONNOTES.Text AS Note, 
dbo.RESERVATIONMAIN.ReservationType, dbo.RESERVATIONMAIN.HOTELNUMBER,dbo.ReservationMain.AllotmentNumber,
dbo.RESERVATIONROOMLINE.Adults + dbo.RESERVATIONROOMLINE.Children1 + dbo.RESERVATIONROOMLINE.Children2 AS PAX,

rlr.Number AS SpecRoomNumber,
hlr.Number as HallRoomNumber,
0 AS SideHouseNumber, -- filled in Delphi
0 AS CleanStatus,  -- filled in Delphi
0 AS SpecBookedTypeNumber,-- filled in Delphi
'AT34567890' AS AllocatedType,  -- filled in Delphi. MUST BE 10 LONG
'BT34567890' AS SpecBookedType, -- filled in Delphi. MUST BE 10 LONG
'12345678901234567890' AS GuestType, -- filled in Delphi. MUST BE 20 LONG
'12345678901234567890' AS Product, -- filled in Delphi. MUST BE 20 LONG
'12345678901234567890' AS SpecProduct, -- filled in Delphi. MUST BE 20 LONG
'123456789012345' AS Allotment, -- filled in Delphi. MUST BE 15 LONG
(SELECT COUNT(*) FROM dbo.ReservationRoomLineXTab WITH (NOLOCK) WHERE ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationRoomLineXTab.GuestNumber = 0) AS RowsOnMainRes,
(SELECT COUNT(*) FROM dbo.ReservationRoomLineXTab WITH (NOLOCK) WHERE ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationRoomLineXTab.GuestNumber > 0) + (SELECT COUNT(*) FROM dbo.ReservationMeetingXTab WITH (NOLOCK) WHERE ReservationMeetingXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationMeetingXTab.GuestNumber > 0) AS NumberOfSubRes,
(SELECT COUNT(*) FROM dbo.Reservations WITH (NOLOCK) WHERE SideHouse = 1 AND ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber AND GuestNumber IN (SELECT GuestNumber FROM dbo.ReservationRoomLineXtab WITH (NOLOCK) WHERE LineNumber = ReservationRoomLine.LineNumber)) AS SideHouseLinked,
(select sum(numberofrooms) from dbo.reservationroomline WITH (NOLOCK) INNER JOIN dbo.reservationroomlinextab WITH (nolock) on reservationroomline.linenumber = reservationroomlinextab.linenumber where reservationroomlinextab.referencenumber = reservations.referencenumber and reservationroomlinextab.guestnumber in (SELECT guestnumber FROM dbo.reservations a WITH (nolock) WHERE a.referencenumber = Reservations.ReferenceNumber AND status <= 6)) AS TotalNumberOfRooms, 
(SELECT COUNT(*) FROM dbo.ReservationRoomLineXTab WITH (NOLOCK) WHERE LineNumber = ReservationRoomLine.LineNumber) AS LinkedRes,

RA1.LastName,
RA1.FirstName,
RA1.CountryCode,
RA2.LastName AS CompanyName,
RA3.LastName AS AgencyName,
RA4.LastName AS AgentName,
RA5.LastName AS GroupName

FROM dbo.Reservations WITH (NOLOCK)
INNER JOIN dbo.ReservationMain WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
LEFT OUTER JOIN dbo.ReservationRoomlineXtab WITH (NOLOCK) ON ReservationRoomlineXTab.ReferenceNumber = Reservations.ReferenceNumber and ReservationRoomlineXtab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN dbo.ReservationRoomline WITH (NOLOCK) ON ReservationRoomline.LineNumber = ReservationRoomlineXtab.LineNumber AND ReservationRoomline.NumberOfRooms > 0
LEFT OUTER JOIN dbo.ReservationMeetingXTab WITH (NOLOCK) ON ReservationMeetingXTab.ReferenceNumber = Reservations.ReferenceNumber and ReservationMeetingXtab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN dbo.ReservationConferenceline WITH (NOLOCK) ON ReservationConferenceline.ReservationConfLineNumber = ReservationMeetingXtab.ReservationConfLineNumber AND ReservationConferenceline.NumberOfRooms > 0

LEFT OUTER JOIN dbo.Rooms rlr WITH (NOLOCK) ON rlr.RoomNumber = ReservationRoomline.RoomNumber
LEFT OUTER JOIN dbo.Rooms hlr WITH (NOLOCK) ON hlr.RoomNumber = RESERVATIONCONFERENCELINE.ConferenceRoomNumber

LEFT OUTER JOIN dbo.ReservationAddressXTab RAX1 WITH (NOLOCK) ON Reservations.ReferenceNumber=RAX1.ReferenceNumber AND Reservations.GuestNumber=RAX1.GuestNumber AND RAX1.AddressTypeNumber=1
LEFT OUTER  JOIN dbo.ReservationAddress RA1 WITH (NOLOCK) ON RAX1.AddressNumber = RA1.AddressNumber
LEFT OUTER JOIN dbo.ReservationAddressXTab RAX2 WITH (NOLOCK) ON Reservations.ReferenceNumber=RAX2.ReferenceNumber AND Reservations.GuestNumber=RAX2.GuestNumber AND RAX2.AddressTypeNumber=2
LEFT OUTER  JOIN dbo.ReservationAddress RA2 WITH (NOLOCK) ON RAX2.AddressNumber = RA2.AddressNumber
LEFT OUTER JOIN dbo.ReservationAddressXTab RAX3 WITH (NOLOCK) ON Reservations.ReferenceNumber=RAX3.ReferenceNumber AND Reservations.GuestNumber=RAX3.GuestNumber AND RAX3.AddressTypeNumber=3
LEFT OUTER  JOIN dbo.ReservationAddress RA3 WITH (NOLOCK) ON RAX3.AddressNumber = RA3.AddressNumber
LEFT OUTER JOIN dbo.ReservationAddressXTab RAX4 WITH (NOLOCK) ON Reservations.ReferenceNumber=RAX4.ReferenceNumber AND Reservations.GuestNumber=RAX4.GuestNumber AND RAX4.AddressTypeNumber=4
LEFT OUTER  JOIN dbo.ReservationAddress RA4 WITH (NOLOCK) ON RAX4.AddressNumber = RA4.AddressNumber
LEFT OUTER JOIN dbo.ReservationAddressXTab RAX5 WITH (NOLOCK) ON Reservations.ReferenceNumber=RAX5.ReferenceNumber AND RAX5.GuestNumber=0 AND RAX5.AddressTypeNumber=1
LEFT OUTER  JOIN dbo.ReservationAddress RA5 WITH (NOLOCK) ON RAX5.AddressNumber = RA5.AddressNumber

LEFT OUTER  JOIN dbo.ReservationNotes WITH (NOLOCK) ON ReservationNotes.ReferenceNumber = Reservations.ReferenceNumber and ReservationNotes.GuestNumber = Reservations.GuestNumber and NotePage = 1


