CREATE FUNCTION dbo.GetRoomOwnerNumber (@Ref INT, @Guest INT)
RETURNS int

-- KJ 20060323: Owner not stored in customer table - now stored in Rooms
-- sidst rettet d. 13/4-2004 af KS : ăndret guestnumber=Guest til "in (guestnumber,0)" For splittbill

AS
BEGIN
  DECLARE @Return INT
  SELECT DISTINCT @Return = MAX(note) FROM Transactions WITH (NOLOCK) WHERE FolioNumber IN (
    SELECT FolioNumber FROM Folios WITH (NOLOCK) WHERE ReferenceNumber = @Ref AND GuestNumber IN (@Guest, 0))

  IF ISNULL(@Return, 0) > 0 
  BEGIN
    DECLARE @Tmp INT
--    SELECT @Tmp = CustomerNumber FROM Customer WITH (NOLOCK) WHERE ROwner = (SELECT RoomNumber FROM Rooms (NOLOCK) WHERE Number = @Return)
    SELECT @Tmp = CustomerNumber FROM Rooms WITH (NOLOCK) WHERE Number = @Return
    IF ISNULL(@tmp, 0) = 0 
      SET @Return = -1
  END 

  IF @Return > 0 
  BEGIN
/*
    SELECT Top 1 @tmp = CustomerNumber FROM CUSTOMER (NOLOCK) WHERE (convert(int, ROWNER) = @return) AND 
    (
    CUSTOMERNUMBER <> ( Select top 1 Link from RESERVATIONADDRESS (nolock) where  RESERVATIONADDRESS.AddressNumber in
                         (Select RESERVATIONADDRESSXTAB.AddressNumber  from RESERVATIONADDRESSXTAB (nolock)
                            where RESERVATIONADDRESSXTAB.Referencenumber = @ref and RESERVATIONADDRESSXTAB.Guestnumber IN(@GUEST,0)
                         )
                       )
    )
*/
    SELECT TOP 1 @tmp = CustomerNumber 
    FROM Rooms WITH (NOLOCK) 
    WHERE Number = @Return
    AND (CUSTOMERNUMBER <> (SELECT TOP 1 Link FROM RESERVATIONADDRESS WITH (NOLOCK) WHERE  RESERVATIONADDRESS.AddressNumber in
          (SELECT RESERVATIONADDRESSXTAB.AddressNumber FROM RESERVATIONADDRESSXTAB WITH (NOLOCK)
          WHERE RESERVATIONADDRESSXTAB.Referencenumber = @Ref AND RESERVATIONADDRESSXTAB.Guestnumber IN (@Guest, 0)))
    )

    IF ISNULL(@Tmp, 0) = 0 
      SET @Return = -2
  END

  RETURN @Return
END

