USP_DATALIST_FAFPARTICIPANTSEARCH

CREATE procedure dbo.USP_DATALIST_FAFPARTICIPANTSEARCH
(
    @EVENTID      varchar(50) = '',
    @SEARCHSTRING varchar(500) = ''
)
as
set nocount on;

CREATE TABLE #TMPSEARCHTABLE
(
    [ID] uniqueidentifier primary key
)

DECLARE @PARTICIPANTSEARCHINFOS TABLE
(
    [TEMPID] int IDENTITY(1,1) primary key,
    [ID] uniqueidentifier,
    [NAME] VARCHAR(100),
    [TYPE] VARCHAR(50),
    [PAGEID] VARCHAR(50),
    [PT] VARCHAR(50),
    [PTYPE] VARCHAR(50),
    [PNAME] VARCHAR(200),
    [ISPRIVATE] BIT,
    [TEAMID] VARCHAR(50),
    [HOUSEHOLDID] VARCHAR(50),
    [ORDERID] INT
);

if LEN(@SEARCHSTRING) > 0
begin
    declare @str1 varchar(500), @str2 varchar(500), @idx int, @cnt int

    set @idx =  charindex('~', @SEARCHSTRING)
    if @idx > 0
        begin
            set @str1 = left(@SEARCHSTRING, @idx - 1)
            set @str2 = right(@SEARCHSTRING, len(@SEARCHSTRING) - @idx)    -- a backup search string in case the first string doesn't return much

            if @str2 = @str1
                set @str2 = ''
        end
    else
        begin
            set @str1 = @SEARCHSTRING
            set @str2 = ''
        end

    --initial search(es), truncated to top 10 results

      exec sp_executesql N'
            insert into #TMPSEARCHTABLE ([ID])
            select top 10 R.ID
            from dbo.CONSTITUENT C (nolock)
            inner join dbo.REGISTRANT R (nolock) on R.CONSTITUENTID = C.ID
            inner join dbo.EVENTEXTENSION (nolock) EX on EX.EVENTID = R.EVENTID
            where contains(NAME, @str1) and R.EVENTID = @EVENTID',
            N'@str1 varchar(500), @EVENTID uniqueidentifier', @str1 = @str1, @EVENTID = @EVENTID;

    --select @cnt = count(*) from #TMPSEARCHTABLE

    --if @cnt < 10 and len(@str2) > 0

    --    --exec sp_executesql N'

                --insert into #TMPSEARCHTABLE ([ID])

                --select top 10 R.ID

    --            from dbo.CONSTITUENT C (nolock)

    --            inner join dbo.REGISTRANT R (nolock) on R.CONSTITUENTID = C.ID

    --            inner join dbo.EVENTEXTENSION EX (nolock) on EX.EVENTID = R.EVENTID

    --            where contains(NAME, @str2) and R.EVENTID = @EVENTID and R.ID not in (select ID from #TMPSEARCHTABLE)--',

    --    --N'@str2 varchar(500), @EVENTID uniqueidentifier', @str2 = @str2, @EVENTID = @EVENTID;


    select @cnt = count(*) from #TMPSEARCHTABLE
    if @cnt > 0
        INSERT INTO @PARTICIPANTSEARCHINFOS ([ID], [NAME], [TYPE], [PAGEID], [PT], [PTYPE], [PNAME], [ISPRIVATE], [TEAMID], [HOUSEHOLDID], [ORDERID])
        SELECT 
            TT.ID, 
            c.NAME,
            case when x.STATUSCODE in (1,2) then 'Individual' else rl.ROLE end as Type,
            ept.PAGEID, 
            '0' as PT,
            case when x.STATUSCODE in (1,2) then null else x.TYPE end as PTYPE,
            case when x.STATUSCODE in (1,2) then null else t.NAME end as PNAME, 
            rex.ISPRIVATE As ISPRIVATE, 
            ISNULL(I.TEAMID,'00000000-0000-0000-0000-000000000000') As TEAMID,
            isnull(I.HouseholdID, '00000000-0000-0000-0000-000000000000') as HOUSEHOLDID,
            CASE WHEN x.EVENTID = @EVENTID then 1 else 2 end as ORDERID
        FROM #TMPSEARCHTABLE TT
        inner join REGISTRANT R (nolock) on R.ID = TT.ID
        inner join REGISTRANTEXTENSION rex (nolock) on R.ID = rex.REGISTRANTID
        left join TEAMFUNDRAISER f (nolock) on f.CONSTITUENTID = R.CONSTITUENTID
        left join TEAMFUNDRAISINGTEAMMEMBER tm (nolock) on f.ID = tm.TEAMFUNDRAISERID
        left join TEAMFUNDRAISINGTEAM t (nolock) on t.ID = tm.TEAMFUNDRAISINGTEAMID
        left join TEAMEXTENSION x (nolock) on x.TEAMFUNDRAISINGTEAMID = t.ID and x.EVENTID = R.EVENTID
        left join CONSTITUENT c (nolock) on R.CONSTITUENTID = c.ID
        left join FAFEVENTPAGETEMPLATE ept (nolock) on R.EVENTID = ept.EVENTID
        left join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) rl ON rl.REGISTRANTID = r.ID
        outer apply UFN_FAF_GETTEAMCOMPANYHOUSEHOLDINFO_BY_REGISTRANT(@EVENTID, R.CONSTITUENTID) I

    -------------------------


    delete from #TMPSEARCHTABLE

    exec sp_executesql N'
            insert into #TMPSEARCHTABLE ([ID])
            select top 10 TE.TEAMFUNDRAISINGTEAMID
            from dbo.CONSTITUENT C (nolock)
            inner join  dbo.TEAMEXTENSION TE (nolock) on TE.TEAMCONSTITUENTID = C.ID and TE.STATUSCODE = 0
            where TE.EVENTID = @EVENTID and contains(NAME, @str1)',
            N'@str1 varchar(500), @EVENTID uniqueidentifier', @str1 = @str1, @EVENTID = @EVENTID;


    --select @cnt = count(*) from #TMPSEARCHTABLE

    --if @cnt < 10 and len(@str2) > 0

    --    --exec sp_executesql N'

    --            insert into #TMPSEARCHTABLE ([ID])

    --            select top 10 TE.TEAMFUNDRAISINGTEAMID

    --            from dbo.CONSTITUENT C (nolock)

    --            inner join  dbo.TEAMEXTENSION TE (nolock) on TE.TEAMCONSTITUENTID = C.ID

    --            where TE.EVENTID = @EVENTID and contains(NAME, @str2) and TE.TEAMFUNDRAISINGTEAMID not in (select ID from #TMPSEARCHTABLE)--',

    --    --N'@str2 varchar(500), @EVENTID uniqueidentifier', @str2 = @str2, @EVENTID = @EVENTID;


    select @cnt = count(*) from #TMPSEARCHTABLE

    if @cnt > 0
        INSERT INTO @PARTICIPANTSEARCHINFOS ([ID], [NAME], [TYPE], [PAGEID], [PT], [PTYPE], [PNAME], [ISPRIVATE], [TEAMID], [HOUSEHOLDID], [ORDERID])
        select 
            TT.ID, 
            T.NAME, 
            X.TYPE, 
            EPT.PAGEID, 
            x.typecode as PT, 
            X2.TYPE as PTYPE, 
            T2.NAME as PNAME, 
            0 As ISPRIVATE, 
            --if it's a household and the parent is a team then put in the team id,

            case when x.TYPECODE <> 3 then T.ID else case when X2.TYPECODE = 1 then X2.TEAMFUNDRAISINGTEAMID else '00000000-0000-0000-0000-000000000000' end end as TEAMID, 
            case when X.TYPECODE = 3 then T.ID else '00000000-0000-0000-0000-000000000000' end as HOUSEHOLDID, 3 as ORDERID
        from #TMPSEARCHTABLE TT
        inner join TEAMFUNDRAISINGTEAM T (nolock) ON TT.ID = T.ID
        inner join TEAMEXTENSION X (nolock) on T.ID = X.TEAMFUNDRAISINGTEAMID
        left join TEAMFUNDRAISINGTEAM T2 (nolock) on T2.ID = T.PARENTTEAMID
        left join TEAMEXTENSION X2 (nolock) on X2.TEAMFUNDRAISINGTEAMID = T2.ID
        left join FAFEVENTPAGETEMPLATE EPT (nolock) on X.EVENTID = EPT.EVENTID
end

DROP TABLE #TMPSEARCHTABLE

SELECT T1.ID, T1.NAME, T1.TYPE, T1.PAGEID, T1.PT, T1.PTYPE, T1.PNAME, T1.ISPRIVATE, T1.TEAMID, T1.HOUSEHOLDID
FROM @PARTICIPANTSEARCHINFOS T1
INNER JOIN (select MIN(TEMPID) as TEMPID from @PARTICIPANTSEARCHINFOS group by ID) T2 on T2.TEMPID=T1.TEMPID
order by 5, 2;