CREATE VIEW DBO.T_LegacyMultipurposeView
/* Used by Carstens code exclusivly - DO NOT MODIFY OR DELETE */
AS
select 
dbo.RESERVATIONS.ReferenceNumber, dbo.RESERVATIONS.GuestNumber, dbo.RESERVATIONS.Status, dbo.RESERVATIONS.AltResNo, dbo.RESERVATIONS.ArrivalDate, dbo.RESERVATIONS.ArrivalTime, 
dbo.RESERVATIONS.PassportNumber,dbo.RESERVATIONS.DepartureDate, dbo.RESERVATIONS.DepartureTime, dbo.RESERVATIONS.Adults, dbo.RESERVATIONS.Children1, dbo.RESERVATIONS.Children2, 
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.ROOMS.HotelNumber AS SpecHotelNumber, dbo.RESERVATIONROOMLINE.Day2 AS SpecDay2, 
dbo.RESERVATIONROOMLINE.LINENUMBER, dbo.RESERVATIONMAIN.ReservationType, dbo.RESERVATIONROOMLINE.ProductNumber AS SpecProductNumber, dbo.RESERVATIONROOMLINE.Price AS SpecPrice, dbo.RESERVATIONS.Price, 
dbo.RESERVATIONROOMLINE.TotalPrice AS SpecTotalPrice, dbo.RESERVATIONS.TotalPrice, dbo.RESERVATIONS.CREDITMAX, ra1.lastname, ra1.firstname, ra1.countrycode, roomtypes.type as AllocatedType,
dbo.RESERVATIONROOMLINE.BookedTypeNumber AS SpecBookedTypeNumber, dbo.RESERVATIONROOMLINE.RoomNumber AS SpecRoomNumberID, dbo.RESERVATIONS.POINTOFSALE, dbo.RESERVATIONS.MARKETSEGMENT,
dbo.RESERVATIONS.Day2, dbo.RESERVATIONS.ProductNumber, dbo.RESERVATIONNOTES.Text AS Note, dbo.RESERVATIONS.PURPOSECODE, dbo.RESERVATIONS.SideHouse, dbo.RESERVATIONMAIN.HOTELNUMBER,
rooms.number as SpecRoomNumber,Rooms.CleanStatus, RoomTypes_1.Type as SpecBookedType, products.productname as SpecProduct, guesttypes.text as GuestType, dbo.RESERVATIONS.METHODOFPAYMENT,
products_1.productname as Product, rooms.sidehousenumber as SideHouseNumber, allotments.name as Allotment,
(SELECT     COUNT(*) FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationRoomLineXTab.GuestNumber = 0) AS RowsOnMainRes,
(SELECT     COUNT(*) FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationRoomLineXTab.GuestNumber > 0) + (SELECT COUNT(*) FROM ReservationMeetingXTab WITH (NOLOCK) WHERE ReservationMeetingXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationMeetingXTab.GuestNumber > 0) AS NumberOfSubRes,
(SELECT     COUNT(*) FROM Reservations WITH (NOLOCK) WHERE SideHouse = 1 AND ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber AND GuestNumber IN (SELECT GuestNumber FROM ReservationRoomLineXtab WITH (NOLOCK) WHERE LineNumber = ReservationRoomLine.LineNumber)) AS SideHouseLinked,
(select sum(numberofrooms) from reservationroomline WITH (NOLOCK) inner join reservationroomlinextab WITH (nolock) on reservationroomline.linenumber = reservationroomlinextab.linenumber where reservationroomlinextab.referencenumber = reservations.referencenumber and reservationroomlinextab.guestnumber in (SELECT guestnumber FROM reservations a WITH (nolock) WHERE a.referencenumber = Reservations.ReferenceNumber AND status <= 6)) AS TotalNumberOfRooms, 
(SELECT     COUNT(*) FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE LineNumber = ReservationRoomLine.LineNumber) AS LinkedRes, 
(SELECT     LastName FROM ReservationAddress WITH (NOLOCK) inner join ReservationAddressXTab WITH (NOLOCK) on reservationaddressxtab.addressnumber = reservationaddress.addressnumber and reservationaddress.addresstype = 2 WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber) AS CompanyName,
(SELECT     LastName FROM ReservationAddress WITH (NOLOCK) inner join ReservationAddressXTab WITH (NOLOCK) on reservationaddressxtab.addressnumber = reservationaddress.addressnumber and reservationaddress.addresstype = 3 WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber) AS AgencyName,
(SELECT     LastName FROM ReservationAddress WITH (NOLOCK) inner join ReservationAddressXTab WITH (NOLOCK) on reservationaddressxtab.addressnumber = reservationaddress.addressnumber and reservationaddress.addresstype = 4 WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber) AS AgentName,
(SELECT     LastName FROM ReservationAddress WITH (NOLOCK) inner join ReservationAddressXTab WITH (NOLOCK) on reservationaddressxtab.addressnumber = reservationaddress.addressnumber and reservationaddress.addresstype = 1 WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = 0) AS GroupName
from reservationmain WITH (NOLOCK) inner join
reservations WITH (NOLOCK) on reservations.referencenumber = reservationmain.referencenumber left outer join
reservationroomlinextab WITH (NOLOCK) on reservationroomlinextab.referencenumber = reservations.referencenumber and reservationroomlinextab.guestnumber = reservations.guestnumber left outer join
reservationroomline WITH (NOLOCK) on reservationroomline.linenumber = reservationroomlinextab.linenumber left outer join
rooms WITH (NOLOCK) on rooms.roomnumber = reservationroomline.roomnumber left outer join
roomtypes WITH (NOLOCK) on roomtypes.roomtypenumber = rooms.roomtypenumber left outer join
roomtypes roomtypes_1 WITH (NOLOCK) on roomtypes_1.roomtypenumber = reservationroomline.BookedTypeNumber inner join
reservationaddressxtab rax1 WITH (NOLOCK) on rax1.referencenumber = reservations.referencenumber AND rax1.guestnumber = reservations.guestnumber inner join
reservationaddress ra1 WITH (NOLOCK) on ra1.addressnumber = rax1.addressnumber and ra1.addresstype = 1 left outer join
reservationnotes WITH (NOLOCK) on reservationnotes.referencenumber = reservations.referencenumber and reservationnotes.guestnumber = reservations.guestnumber and reservationnotes.notepage = 1 left outer join
allotments WITH (NOLOCK) on allotments.allotmentnumber = reservationmain.allotmentnumber left outer join
Products WITH (NOLOCK) on Products.ProductNumber = ReservationRoomLine.ProductNumber left outer join
Guesttypes WITH (NOLOCK) on guesttypes.TypeNumber = Reservations.GuestType left outer join
products products_1 WITH (NOLOCK) on products_1.productnumber = reservations.productnumber
WHERE     (dbo.RESERVATIONROOMLINE.NumberOfRooms > 0)

