CREATE VIEW dbo.TConfRoomAvailabilityView
AS
/*
  Last Changed
  RN 2013-09-09 CR063921, Added ISNULL stuff for SchemePattern
  RN 2013-07-30 Added Children2 column to view
  BA 2009-08-11 Plenum added to the selection (V4.1+)
  AV 2007 1101 reservationmain,origintype added
  nb20071016 bug Syntax error fixed + optimization
  2006 1128 UserTypeNumber added
  2005-08-24 JP fixed HotelNumber for outoforder, AddressType number done in correct table for speed
*/
SELECT HL.Plenum, R.Status AS MainStatus, HL.ReservationConfLineNumber, HL.BookedConferenceRoomTypeNumber, HL.NumberOfRooms, 
  HL.ArrivalDate, HL.ArrivalTime, HL.DepartureDate, HL.DepartureTime, HL.tablebooking, HL.HallLinkNumber, 
  HL.Price, HL.TotalPrice, HL.PriceOverWritten, HL.ConferenceRoomSetupNumber, HL.ProductNumber, HL.PackageNumber, 
  HL.Adults, HL.Children1, HL.Children2, HL.ReferenceNumber, HT.Text AS TableSetup, HL.ConferenceRoomNumber, ISNULL(HL.SchemePattern,0) AS SchemePattern, HL.SchemeWeekDays,
  HL.TableStatus, HL.GuestNo, HL.AllocationLocked, HL.MeetingSecret,
  CT.HotelNumber,
  --(CASE WHEN HL.ConferenceRoomNumber>0 THEN H.HotelNumber ELSE RM.HotelNumber /*maybe this should be from the type?*/ END) AS HotelNumber,
   RM.OriginType,
  1 AS AddressType, -- JP NOTE you were inner joining with ReservationAddress on AddressType=1, what do you think it is?
  HX.CollectsConferenceRoomNumber, P.ProductName, H.Number AS ScreenNumber, 0 AS UserTypeNumber,
  A.Lastname, 
  RA2.LastName AS CompanyName, RA3.LastName AS AgencyName, RA4.LastName AS AgentName,
 (SELECT Number FROM dbo.ConferenceRooms WITH (NOLOCK) where ConferenceRoomNumber= HX.CollectsConferenceRoomNumber) as ScreenHall
 ,ConferenceRoomStatusType.StatusLetter
 ,CT.ResourceType

,(SELECT TOP 1 Products.ProductName
from ReservationReservationXtab WITH (NOLOCK)
INNER JOIN RESERVATIONCONFERENCELINE WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReferenceNumber = ReservationReservationXtab.FK_ReferenceNumber
INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = RESERVATIONCONFERENCELINE.BookedConferenceRoomTypeNumber
INNER JOIN Products WITH (NOLOCK) ON products.ProductNumber = RESERVATIONCONFERENCELINE.ProductNumber
WHERE FK_ReferenceNumberSub = HL.ReferenceNumber
AND CONFERENCEROOMTYPES.ResourceType = 16) AS ProductCourseName

,(SELECT TOP 1 Products.Description
from ReservationReservationXtab WITH (NOLOCK)
INNER JOIN RESERVATIONCONFERENCELINE WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReferenceNumber = ReservationReservationXtab.FK_ReferenceNumber
INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = RESERVATIONCONFERENCELINE.BookedConferenceRoomTypeNumber
INNER JOIN Products WITH (NOLOCK) ON products.ProductNumber = RESERVATIONCONFERENCELINE.ProductNumber
WHERE FK_ReferenceNumberSub = HL.ReferenceNumber
  AND CONFERENCEROOMTYPES.ResourceType = 16) AS ProductCourseDescription

  ,(SELECT RESERVATIONADDRESS.LASTNAME
  FROM RESERVATIONADDRESS WITH (NOLOCK)
  INNER JOIN ReservationReservationXtab RRX WITH (NOLOCK) ON RRX.FK_ReferenceNumberSub = HL.ReferenceNumber
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER
    AND RESERVATIONADDRESSXTAB.REFERENCENUMBER = RRX.FK_ReferenceNumber
    AND RESERVATIONADDRESSXTAB.GuestNumber = 0
    AND RESERVATIONADDRESSXTAB.AddressTypeNumber = 1) AS CourseArrName

  ,(SELECT SUM(ReservationMeetingXTab.Adults + ReservationMeetingXTab.Children + ReservationMeetingXTab.Children2)
  FROM ReservationMeetingXTab WITH (NOLOCK)
  INNER JOIN Reservations WITH (NOLOCK) ON ReservationMeetingXTab.ReferenceNumber = Reservations.ReferenceNumber AND ReservationMeetingXTab.GuestNumber = Reservations.GuestNumber
  WHERE ReservationMeetingXTab.ReferenceNumber = HL.ReferenceNumber
  AND Reservations.Status NOT IN (7,8)) AS PaxCount

,(SELECT ReservationAddress.COUNTRYCODE
FROM RESERVATIONADDRESS WITH (NOLOCK)
INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.ADDRESSNUMBER = RESERVATIONADDRESS.AddressNumber
WHERE RESERVATIONADDRESSXTAB.ReferenceNumber = HL.ReferenceNumber
  AND RESERVATIONADDRESSXTAB.GUESTNUMBER = 0
  AND RESERVATIONADDRESSXTAB.AddressTypeNumber = 1) AS CountryCode

  ,ISNULL((
  SELECT SUM(ReservationMeetingXtab.Adults + ReservationMeetingXtab.Children + ReservationMeetingXtab.Children2) AS TotalPax
  FROM RESERVATIONS WITH (NOLOCK)
  INNER JOIN ReservationReservationXtab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationReservationXtab.FK_ReferenceNumberSub
  INNER JOIN ReservationMeetingXTab WITH (NOLOCK) ON ReservationMeetingXTab.ReferenceNumber = RESERVATIONS.ReferenceNumber AND ReservationMeetingXTab.GuestNumber = RESERVATIONS.GuestNumber
  INNER JOIN RESERVATIONCONFERENCELINE WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.ReservationConfLineNumber = ReservationMeetingXTab.ReservationConfLineNumber
  INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON RESERVATIONCONFERENCELINE.BookedConferenceRoomTypeNumber = CONFERENCEROOMTYPES.ConferenceRoomTypeNumber
  WHERE ReservationReservationXtab.FK_ReferenceNumber = HL.ReferenceNumber
    AND Reservations.Status <= 6
    AND Reservations.GuestNumber > 0
    AND CONFERENCEROOMTYPES.ResourceType = 16
  ),0) AS TotalPaxCount

  ,(SELECT Reservations.Status
  FROM ReservationReservationXtab WITH (NOLOCK)
  INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationReservationXtab.FK_ReferenceNumber AND Reservations.GuestNumber = 0
    AND FK_ReferenceNumberSub = HL.ReferenceNumber
  ) AS MainReservationStatus

  ,(SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumberSub = HL.ReferenceNumber) AS MainReferenceNumber

  ,ISNULL((SELECT CAST(1 AS Bit) FROM ReservationReservationXtab WITH (NOLOCK)
  WHERE FK_ReferenceNumber = FK_ReferenceNumberSub
    AND FK_ReferenceNumberSub = HL.ReferenceNumber
  ),0) AS IsMainReservation

  ,(SELECT ReservationAddress.LASTNAME
  FROM RESERVATIONADDRESS WITH (NOLOCK)
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.ADDRESSNUMBER = RESERVATIONADDRESS.AddressNumber
  WHERE RESERVATIONADDRESSXTAB.ReferenceNumber = HL.ReferenceNumber
    AND RESERVATIONADDRESSXTAB.GUESTNUMBER = 0
    AND RESERVATIONADDRESSXTAB.AddressTypeNumber = 2 -- atCompany
  ) AS HallPlanCompanyName
FROM RESERVATIONS R WITH (NOLOCK)
  INNER JOIN dbo.RESERVATIONCONFERENCELINE HL WITH (NOLOCK) ON R.ReferenceNumber = HL.ReferenceNumber AND R.GUESTNUMBER = 0 
    AND (ISNULL(HL.BookedConferenceRoomTypeNumber, 0) > 0) AND ISNULL(SchemePattern,0) = 0 -- IS NULL
  LEFT JOIN dbo.RESERVATIONADDRESSXTAB AX WITH (NOLOCK) ON R.ReferenceNumber = AX.REFERENCENUMBER AND R.GuestNumber = AX.GUESTNUMBER AND AX.AddressTypeNumber=1
  LEFT JOIN dbo.RESERVATIONADDRESS A WITH (NOLOCK) ON  AX.ADDRESSNUMBER=A.AddressNumber
  LEFT JOIN dbo.CONFERENCEROOMS H WITH (NOLOCK) ON HL.ConferenceRoomNumber = H.ConferenceRoomNumber
  LEFT JOIN dbo.CONFERENCEROOMSXTAB HX WITH (NOLOCK) ON HL.ConferenceRoomNumber = HX.Conferenceroomnumber 
  LEFT JOIN dbo.CONFERENCEROOMTABLES HT WITH (NOLOCK) ON HL.ConferenceRoomSetupNumber = HT.ConferenceRoomTableNumber 
  INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber=R.Referencenumber 
  LEFT JOIN dbo.products P WITH (NOLOCK) ON P.productnumber = HL.Productnumber
  LEFT JOIN dbo.ReservationAddressXTab RAX2 WITH (NOLOCK) ON R.ReferenceNumber=RAX2.ReferenceNumber AND R.GuestNumber=RAX2.GuestNumber AND RAX2.AddressTypeNumber=2
  LEFT JOIN dbo.ReservationAddress RA2 WITH (NOLOCK) ON RAX2.AddressNumber = RA2.AddressNumber
  LEFT JOIN dbo.ReservationAddressXTab RAX3 WITH (NOLOCK) ON R.ReferenceNumber=RAX3.ReferenceNumber AND R.GuestNumber=RAX3.GuestNumber AND RAX3.AddressTypeNumber=3
  LEFT JOIN dbo.ReservationAddress RA3 WITH (NOLOCK) ON RAX3.AddressNumber = RA3.AddressNumber
  LEFT JOIN dbo.ReservationAddressXTab RAX4 WITH (NOLOCK) ON R.ReferenceNumber=RAX4.ReferenceNumber AND R.GuestNumber=RAX4.GuestNumber AND RAX4.AddressTypeNumber=4
  LEFT JOIN dbo.ReservationAddress RA4 WITH (NOLOCK) ON RAX4.AddressNumber = RA4.AddressNumber
  LEFT JOIN dbo.ResvConfLineConfRmStatusXtab WITH (NOLOCK) ON dbo.ResvConfLineConfRmStatusXtab.FK_ReservationConferenceLineNumber = HL.ReservationConfLineNumber
  LEFT JOIN dbo.ConferenceRoomStatusType WITH (NOLOCK) ON dbo.ConferenceRoomStatusType.ConferenceRoomStatusTypeID = dbo.ResvConfLineConfRmStatusXtab.FK_ConferenceRoomStatusTypeID
  LEFT JOIN dbo.ConferenceRoomTypes CT WITH (NOLOCK) ON CT.ConferenceRoomTypeNumber = HL.BookedConferenceRoomTypeNumber

