CREATE FUNCTION dbo.GuestIsReturningCustomer (@AReferenceNumber INT, @AGuestNumber INT, @ABeforeDate CHAR(8)) RETURNS BIT AS
BEGIN
/*
  Created by Kent Johansen, October 2024
  Return true/false if company and/or guest is returning cutomer or not
*/

  DECLARE @s VARCHAR(MAX)
  DECLARE @res BIT
  DECLARE @LUseMISAsOrderBook BIT

  SELECT @LUseMISAsOrderBook = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OrderbookUseMIS'
  SET @LUseMISAsOrderBook = ISNULL(@LUseMISAsOrderBook, 0)

  SELECT @s = ISNULL(@s + ',' + CAST(Link AS VARCHAR), CAST(Link AS VARCHAR))
  FROM ReservationAddress WITH (NOLOCK)
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
    AND ReservationAddressXTab.ReferenceNumber = @AReferenceNumber
    AND ReservationAddressXTab.GuestNumber = @AGuestNumber
    AND ReservationAddressXTab.AddressTypeNumber IN (1,2)

  IF @LUseMISAsOrderBook = 1
  BEGIN
    SELECT @res = CASE WHEN ISNULL(SUM(BookedNumberOfRooms) + SUM(ActualNumberOfRooms), 0) > 0 THEN 1 ELSE 0 END
    FROM MISTable WITH (NOLOCK)
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTable.ReferenceNumber
      AND Reservations.GuestNumber = MISTable.GuestNumber
    INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
      AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
    WHERE 1=1
    AND ReservationAddress.Link IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@s))
    AND MISTable.DateStamp < @ABeforeDate
    AND MISTable.ReservationStatus IN (0,1,2,5,6)
  END
  ELSE
  BEGIN
    SELECT  @res = SUM(ISNULL(NumberOfHalls, 0)) + SUM(ISNULL(NumberOfStaysDaymeetings, 0))
    FROM CustomerOrdreBook WITH (NOLOCK)
    WHERE CustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@s))
    AND DateForValue < @ABeforeDate
  END

  RETURN @res
END