USP_DONOR_INFO_HELPER_GETCONSTITS

CREATE procedure [dbo].[USP_DONOR_INFO_HELPER_GETCONSTITS] (
  @DONORSEARCHTEXT nvarchar(500) output
  ,@ABORT bit output
  )
  with execute as owner
as
declare @ABORT2 bit = @ABORT
  ,@DONORSEARCHTEXT2 nvarchar(500) = @DONORSEARCHTEXT

if len(@DONORSEARCHTEXT2) > 0
begin
  declare @FULLNAME nvarchar(500) = replace(LTRIM(RTRIM(isnull(@DONORSEARCHTEXT2, ''))), '.', ' ')
    ,@KEYNAME nvarchar(100)
    ,@WORD1 nvarchar(500)
    ,@WORD2 nvarchar(500)
    ,@WORD3 nvarchar(500)
    ,@LOOKUPID nvarchar(50)
    ,@ONLYONESPECIFIED bit = 0
    ,@CNT int = 0
    ,@IDX int = 0;

  set @FULLNAME = RTRIM(LTRIM(REPLACE(REPLACE(@FULLNAME, '.', ' '), ',', ', ')))
  set @IDX = charindex(',', @FULLNAME);

  if @IDX > 0
  begin
    --a comma exists, assume everything up to it is the KEYNAME

    set @KEYNAME = RTRIM(SUBSTRING(@FULLNAME, 1, @IDX - 1))
    --remove KEYNAME from FULLNAME

    set @FULLNAME = LTRIM(SUBSTRING(@FULLNAME, @IDX + 1, len(@FULLNAME)))
  end

  --we have already processed any commas we care about, so remove all commas from the FULLNAME

  while CHARINDEX(',', @FULLNAME) > 0
    set @FULLNAME = RTRIM(LTRIM(REPLACE(@FULLNAME, ',', ' ')))

  --remove extra spaces

  while CHARINDEX('  ', @FULLNAME) > 0
    set @FULLNAME = REPLACE(@FULLNAME, '  ', ' ')
  set @IDX = charindex(' ', @FULLNAME);

  if @IDX > 0
  begin
    --a space exists, assume everything up to it is a word

    set @WORD1 = RTRIM(SUBSTRING(@FULLNAME, 1, @IDX - 1))
    --remove KEYNAME from FULLNAME

    set @FULLNAME = LTRIM(SUBSTRING(@FULLNAME, @IDX + 1, len(@FULLNAME)))
  end
  else
  begin
    set @WORD1 = @FULLNAME;
    set @FULLNAME = null;
  end

  set @IDX = charindex(' ', @FULLNAME);

  if @IDX > 0
  begin
    --a space exists, assume everything up to it is a word

    set @WORD2 = RTRIM(SUBSTRING(@FULLNAME, 1, @IDX - 1))
    --remove KEYNAME from FULLNAME

    set @FULLNAME = LTRIM(SUBSTRING(@FULLNAME, @IDX + 1, len(@FULLNAME)))
  end
  else
  begin
    set @WORD2 = @FULLNAME;
    set @FULLNAME = null;
  end

  --at this point, all that is left is the last word (there could be additional spaces, but we can't process any more words)

  set @WORD3 = @FULLNAME
  set @FULLNAME = null

  if len(@WORD1) = 0
    set @WORD1 = null

  if len(@WORD2) = 0
    set @WORD2 = null

  if len(@WORD3) = 0
    set @WORD3 = null

  if @KEYNAME is null
    if @WORD3 is not null
    begin
      set @KEYNAME = @WORD3;
      set @WORD3 = null;
    end
    else
      if @WORD2 is not null
      begin
        set @KEYNAME = @WORD2;
        set @WORD2 = null;
      end
      else
        if @WORD1 is not null
        begin
          set @KEYNAME = @WORD1;
          set @WORD1 = null;
        end

  if LEN(@KEYNAME) > 0
    set @KEYNAME = dbo.UFN_TEXTSEARCH_SANITIZE(@KEYNAME + '%', '/', 1, 0, default);

  if LEN(@WORD1) > 0
    set @WORD1 = dbo.UFN_TEXTSEARCH_SANITIZE(@WORD1 + '%', '/', 1, 0, default);

  if LEN(@WORD2) > 0
    set @WORD2 = dbo.UFN_TEXTSEARCH_SANITIZE(@WORD2 + '%', '/', 1, 0, default);
  set @DONORSEARCHTEXT2 = dbo.UFN_TEXTSEARCH_SANITIZE(@DONORSEARCHTEXT2, '/', 1, default, default);

  merge #TMP_DONOR_INFO_FILTEREDIDS as target
  using (
    --first add all constituents whose LOOKUPID or KEYNAME matches the original search text

    select [ID] = ID, [RECORDTYPE]=2, [SPID]=@@SPID
    from dbo.CONSTITUENT C with (NOLOCK)
    where (KEYNAME like @DONORSEARCHTEXT2)
      or (LOOKUPID like @DONORSEARCHTEXT2)
    ) as source
    on target.ID = source.ID
      and target.RECORDTYPE = source.RECORDTYPE
      and target.SPID = source.SPID
  when not matched
    then
      insert (
        [SPID]
        ,[ID]
        ,[RECORDTYPE]
        )
      values (
        source.SPID
        ,source.ID
        ,source.RECORDTYPE
        );

  set @CNT += @@ROWCOUNT;

  merge #TMP_DONOR_INFO_FILTEREDIDS as target
  using (
    --then add any INDIVIDUALS whose first, middle or last names match (organizations are handle by the first insert above)

    select [ID] = ID, [RECORDTYPE]=2, [SPID]=@@SPID
    from dbo.CONSTITUENT C with (NOLOCK)
    where ISORGANIZATION = 0
      and (
        (
          @WORD1 is null
          and (
            (KEYNAME like @KEYNAME)
            or (FIRSTNAME like @KEYNAME)
            or (MIDDLENAME like @KEYNAME)
            )
          )
        or (
          (KEYNAME like @KEYNAME)
          and (
            @WORD1 is null
            or FIRSTNAME like @WORD1
            )
          and (
            @WORD2 is null
            or MIDDLENAME like @WORD2
            )
          )
        )
    ) as source
    on target.ID = source.ID
      and target.RECORDTYPE = source.RECORDTYPE
      and target.SPID = source.SPID
  when not matched
    then
      insert (
        [SPID]
        ,[ID]
        ,[RECORDTYPE]
        )
      values (
        source.SPID
        ,source.ID
        ,source.RECORDTYPE
        );

  set @CNT += @@ROWCOUNT;

  if @CNT < 1
    set @ABORT2 = 1
end
else
begin
  set @DONORSEARCHTEXT2 = null;
end

set @ABORT = @ABORT2;
set @DONORSEARCHTEXT = @DONORSEARCHTEXT2;