CREATE FUNCTION dbo.GetTextNameLodgingTransactions (@AReferenceNumber INT, @AGuestNumber INT, @HallLine BIT, @AHotelCustomerNumber INT)  
RETURNS VARCHAR(150) AS  

/*
  Created by Kent Johansen on 5. juli 2006
  
  Returns the text for loging transactions. The setup for this is in HotelSetup -> Basic -> Products

  TNameLodgingColumn: nlcComma, nlcPeriod, nlcDash, nlcbracketStart, nlcbracketEnd, 
    nlcSpace, nlcReferenceNumber, nlcGuestNumber, nlcFirstName, nlcLastname, nlcAltResNo

  Changes:
  KJ 20191125: Ext: Added room type text
  KJ 20191107: Ext: Added room type
  KJ 20170710: Ext: Name on lodging transactions setup per hotel
  KJ 20170202: Added support for hall name list
  KJ 20060713: Added nclAltResNo
  KJ 20060707: Added Convert to ReferenceNumber and GuestNumber   - fix for "cannot convert Peter Jensen to type int"

*/

BEGIN 
  DECLARE @Result VARCHAR(150)
  DECLARE @NameLodgingColumns VARCHAR(25)
  DECLARE @Number INT
  DECLARE @GuestLastName VARCHAR(50)
  DECLARE @GuestFirstName VARCHAR(50)
  DECLARE @AltResNo VARCHAR(20)
  DECLARE @RT VARCHAR(10)
  DECLARE @RTText VARCHAR(50)

  SET @Result = ''

  SELECT TOP 1
    @GuestLastName = LastName, @GuestFirstName = FirstName, @AltResNo = AltResNo, @RT = RoomTypes.Type, @RTText = RoomTypes.Text
  FROM ReservationAddress WITH (NOLOCK) 
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
    AND ReservationAddressXTab.ReferenceNumber = @AReferenceNumber 
    AND ReservationAddressXTab.GuestNumber = @AGuestNumber 
  INNER JOIN Reservations WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber 
    AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
  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 RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
  WHERE ReservationAddress.AddressType = 1

  IF @HallLine = 1
    SET @NameLodgingColumns = dbo.GetHotelSetupKeyValue('NameHallColumns', @AHotelCustomerNumber)
  ELSE
    SET @NameLodgingColumns = dbo.GetHotelSetupKeyValue('NameLodgingColumns', @AHotelCustomerNumber)

  DECLARE db_cur CURSOR FOR
    SELECT Number FROM iter_intlist_to_table_No_Dummy(@NameLodgingColumns)
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @Number

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @Number = 0 --nlcComma
    BEGIN
      SET @Result = @Result + ','
    END
    ELSE IF @Number = 1 --nlcPeriod
    BEGIN
      SET @Result = @Result + '.'
    END
    ELSE IF @Number = 2 --nlcDash
    BEGIN
      SET @Result = @Result + '-'
    END
    ELSE IF @Number = 3 --nlcbracketStart
    BEGIN
      SET @Result = @Result + '('
    END
    ELSE IF @Number = 4 --nlcbracketEnd
    BEGIN
      SET @Result = @Result + ')'
    END
    ELSE IF @Number = 5 --nlcSpace
    BEGIN
      SET @Result = @Result + ' '
    END
    ELSE IF @Number = 6 --nlcReferenceNumber
    BEGIN
      SET @Result = @Result + CONVERT(VARCHAR(10), @AReferenceNumber)
    END
    ELSE IF @Number = 7 --nlcGuestNumber
    BEGIN
      SET @Result = @Result + CONVERT(VARCHAR(10), ISNULL(@AGuestNumber, 0))
    END
    ELSE IF @Number = 8 --nlcFirstName
    BEGIN
      IF @GuestFirstName <> ''
      SET @Result = @Result + @GuestFirstName
    END
    ELSE IF @Number = 9 --nlcLastname
    BEGIN
      IF @GuestLastName <> ''
      SET @Result = @Result + @GuestLastName
    END
    ELSE IF @Number = 10 --nlcAltResNo
    BEGIN
      IF @AltResNo <> ''
      SET @Result = @Result + @AltResNo
    END
    ELSE IF @Number = 11 --nlcRoomType
    BEGIN
      IF @RT <> ''
        SET @Result = @Result + @RT
    END
    ELSE IF @Number = 12 --nlcRoomTypeText
    BEGIN
      IF @RTText <> ''
        SET @Result = @Result + @RTText
    END

    FETCH NEXT FROM db_cur INTO @Number
  END

  DEALLOCATE db_cur

  RETURN @Result
END