CREATE FUNCTION [dbo].[GetHallLineArrangement] (@HallLineNumber INT, @ArrivalDate VARCHAR(8), @ArrivalTime CHAR(5), @DepartureTime CHAR(5), @HallNumber INT)  
--Created AV 2009 0619 Used in hall time plan wellness. The mouse hint must show which treatments (Wares) the hall (or treater) has.


RETURNS VARCHAR(50) AS
BEGIN 
  DECLARE @IsStaff BIT
  DECLARE @Result VARCHAR(100)
  SET @Result = ''
  IF ISNULL(@HallNumber, 0) > 0
  BEGIN
    SELECT @IsStaff = IsStaff FROM ConferenceRooms WITH (NOLOCK) WHERE ConferenceRoomNumber = @HallNumber 

    SELECT TOP 1 @Result = Ware.WareName FROM Ware WITH (NOLOCK) INNER JOIN ArrangementLines WITH (NOLOCK) ON Ware.WareNumber = ArrangementLines.WareNumber
    INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
    LEFT OUTER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementRoomXTab.ArrangementLineNumber
    INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = ArrangementReservationsXtab.ReferenceNumber AND ArrangementReservationsXTab.GuestNumber = ISNULL(ReservationConferenceLine.GuestNo, 0)
    INNER JOIN ConferenceRooms WITH (NOLOCK) ON ReservationConferenceLine.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
    WHERE ReservationConferenceLine.ReservationConfLineNumber = @HallLineNumber AND
     ArrangementRoomXTab.ArrangementLineNumber IS NULL -- then we are sure that only manual lines is selected
     AND ArrangementLines.Date = @ArrivalDate AND ArrangementLines.Time <@DepartureTime AND ArrangementLines.DepartureTime > @ArrivalTime AND ((@IsStaff = 1 AND ArrangementLines.FK_HallNumber = @HallNumber) OR (@IsStaff = 0 AND ArrangementLines.HallNumber = @HallNumber))  
  END

  RETURN (@result)

END
