CREATE PROCEDURE dbo.OnlineGetCustomerHallBookings @ACustomerNumber INT, @AStartDateTime DATETIME, @AContactpersonCustomerNumber INT = 0 AS
-- Created AV 2007 1204
--Note: @CustomerTypeNumber is now compared with AddressTypeNumber directly, If we are only dealing with guests and company's this is okay else the chain will break.....
--Last changed : AV 2007 12 12 productname
-- Last changed AV 2008 0125 @AContactpersonCustomerNumber added
DECLARE @CustomerTypeNumber INT

SELECT @CustomerTypeNumber = CustomerTypeNumber FROM Customer WITH (NOLOCK) WHERE CustomerNumber =  @ACustomerNumber

SELECT DISTINCT Reservations.Status, ReservationConfLineNumber, ReservationConferenceLine.ReferenceNumber, ReservationConferenceLine.GuestNo, 
CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) AS ArrivalDateTime,
CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) AS DepartureDateTime,
ReservationConferenceLine.ProductNumber, Products.ProductName, ReservationConferenceLine.Price, ReservationConferenceLine.TotalPrice, ConferenceRooms.Name AS HallName, ReservationConferenceLine.ConferenceRoomNumber AS HallNumberId, ConferenceRooms.Number AS HAllNumber, BookedConferenceRoomTypeNumber AS HallTypeNumber,  ConferenceRoomTypes.Type AS HallType, ReservationConferenceLine.Adults, ReservationConferenceLine.ConferenceRoomSetupNumber AS TableSetupNumber, ConferenceRoomTables.Text
FROM ReservationConferenceLine INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber AND Reservations.Status < 7
LEFT OUTER JOIN ConferenceRooms WITH (NOLOCK) On ReservationConferenceLine.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
LEFT OUTER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ReservationConferenceLine.BookedConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
LEFT OUTER JOIN ConferenceRoomTables WITH (NOLOCK) ON ReservationConferenceLine.ConferenceRoomSetupNumber = ConferenceRoomTables.ConferenceRoomTableNumber
LEFT OUTER JOIN Products WITH (NOLOCK) ON ReservationConferenceLine.Productnumber = Products.ProductNumber
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ISNULL(ReservationConferenceLine.GuestNo, 0 ) = ReservationAddressXTab.GuestNumber AND ReservationAddressXTab.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
AND ReservationAddressXTab.AddressTypeNumber = @CustomerTypeNumber
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber 
AND ( (ReservationAddress.Link = @ACustomerNumber AND @AContactpersonCustomerNumber = 0) OR (@AContactpersonCustomerNumber > 0 AND ReservationAddress.ContactpersonCustomerNumber = @AContactpersonCustomerNumber)  )  
WHERE ReservationConferenceLine.DepartureDate >= CONVERT(VARCHAR(8),@AStartDateTime, 112) AND ISNULL(ReservationConferenceLine.TableBooking, 0) = 0
ORDER BY ReservationConferenceLine.ReferenceNumber, GuestNo, ConferenceRooms.Number

