USP_DATALIST_FAFFUNDRAISINGGROUP

CREATE procedure dbo.USP_DATALIST_FAFFUNDRAISINGGROUP
(
        @EVENTID uniqueidentifier,
        @SEARCHSTR nvarchar(20) = '',
        @SEARCHTYPE tinyint = 4, -- 3: NFG only, 2:NFG, Company, 4:NFG, Company, Team, 1:Company and Team
        @SEARCHLEADER bit = 0,
        @MAXROWS int = 11
)
as
    set nocount on; 

  declare @ISANEVENTCAMPAIGN bit = 0, -- ASSUME: event is an event campaign, each NFG display site must match to one of event sites
                  @TEAMLABEL nvarchar(100),
                  @COMPANYLABEL nvarchar(100),
                  @TEAMMEMBERLIMIT int,
                  @COMPANYMEMBERLIMIT int,
                  @COMPANYTEAMLIMIT int,
                  @HOUSEHOLDSJOINTEAMS bit,
          @SQL nvarchar(max),
                  @PARAMDEFINITION nvarchar(700)
                  --,@NFGSQL0 nvarchar(1500) = ''

        select @ISANEVENTCAMPAIGN=case when EX.FAFPROGRAMID is not null then 1 else 0 end from dbo.EVENTEXTENSION EX(nolock) where EVENTID = @EVENTID
        select @TEAMLABEL=EL.LABELVALUE from dbo.EVENTLABEL EL (nolock) where EL.EVENTID = @EVENTID and EL.LABELNAME = 'Team'
        select @COMPANYLABEL=EL.LABELVALUE from dbo.EVENTLABEL EL (nolock) where EL.EVENTID = @EVENTID and EL.LABELNAME = 'Company'
        select         
          @TEAMMEMBERLIMIT = TEAMMEMBERSLIMIT, 
          @COMPANYTEAMLIMIT = COMPANYTEAMSLIMIT, 
          @COMPANYMEMBERLIMIT = COMPANYTEAMMEMBERSLIMIT,
          @HOUSEHOLDSJOINTEAMS = HOUSEHOLDSJOINTEAMS
        from dbo.EVENTTEAMFAFCONFIG (nolock) where EVENTID = @EVENTID

        set @SQL = 'create table #BaseList (
            ID uniqueidentifier primary key,
            NAME nvarchar(100) collate DATABASE_DEFAULT,
            TYPE nvarchar(200),
            TYPECODE tinyint,
            PARENTGROUPID uniqueidentifier,
            PARENTGROUPNAME nvarchar(100),
            AVAILABLESEARCHTYPE nvarchar(10),
            SELECTABLE bit
        )

        create table #AccumulativeList (    
            ID uniqueidentifier primary key,
            NAME char(100),
            TYPE char(200),
            TYPECODE tinyint,
            PARENTGROUPID uniqueidentifier,
            PARENTGROUPNAME nvarchar(100),
            AVAILABLESEARCHTYPE nvarchar(10),
            SELECTABLE bit
        ) '


        if @SEARCHTYPE in (1, 2, 4)
        begin
            set @SQL = @SQL + ' insert into #BaseList(ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE)
                select distinct top (@MAXROWS) TFT.ID, TFT.NAME, case TX.TYPECODE when 1 then @TEAMLABEL when 2 then @COMPANYLABEL end as [TYPE], TX.TYPECODE        
                , case when TFT.PARENTTEAMID is not null then TFT.PARENTTEAMID 
                       when NFG.ID is not null then NFG.ID 
                       else TFT.ID end as PARENTGROUPID
                , case when TFT.PARENTTEAMID is not null then pTFT.NAME 
                       when NFG.ID is not null then NFG.NAME 
                       else TFT.NAME end as PARENTGROUPNAME
                , ''Local'' AS AVAILABLESEARCHTYPE
                , 1
                from dbo.TEAMEXTENSION TX (nolock)
                join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
                left join dbo.TEAMFUNDRAISINGTEAM pTFT (nolock) on pTFT.ID = TFT.PARENTTEAMID
                left join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFGL.ID = TX.NFGCAMPAIGNLEVELID
                left join dbo.FAFNFGCAMPAIGN NFG (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID 
                where TX.EVENTID = @EVENTID and TX.STATUSCODE = 0 and TFT.NAME like ''%'' + @SEARCHSTR + ''%''
            '    

            if @SEARCHTYPE = 2 or (@SEARCHTYPE = 4 and @HOUSEHOLDSJOINTEAMS = 0)
            begin
                set @SQL = @SQL + ' and TX.TYPECODE = 2' 
            end
            else if @SEARCHTYPE = 1 or (@SEARCHTYPE = 4 and @HOUSEHOLDSJOINTEAMS = 1)
            begin
                set @SQL = @SQL + ' and TX.TYPECODE in (1,2)'
            end        

        end

        if @ISANEVENTCAMPAIGN = 1 and @SEARCHTYPE in (2,3,4)
        begin

            set @SQL = @SQL + '
                    insert into #BaseList(ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE)
                    select distinct top (@MAXROWS) NFG.ID, NFG.NAME, (select TC.DESCRIPTION from dbo.FAFNFGTYPECODE TC where TC.ID = NFG.NFGTYPECODEID) as [TYPE], 5 as TYPECODE
                    , NFG.ID as PARENTGROUPID 
                    , NFG.NAME AS PARENTGROUPNAME
                    , ''National'' AS AVAILABLESEARCHTYPE
                    , case when A.ISACCESSIBLEBYSITE = 0 or @SEARCHTYPE = 1 or (@SEARCHTYPE in (2, 4) and NFG.ALLOWOTHERSJOINFROMGROUPPAGE = 0) then 0 
                                        else 1 end as SELECTABLE
                    from dbo.EVENTEXTENSION EX (nolock) 
                    join dbo.FAFNFGCAMPAIGN NFG (nolock) on EX.FAFPROGRAMID = NFG.CAMPAIGNID 
                    left join (
            select top (@MAXROWS) NFG.ID, max(case when (DS.ID is null and DS2.ID is null) or DS2.SITEID = ES.SITEID then 1 else 0 end) as ISACCESSIBLEBYSITE
                            from dbo.EVENTEXTENSION EX
                            left join EVENTSITE ES on ES.EVENTID = EX.EVENTID                     
                            left join dbo.FAFNFGCAMPAIGN NFG (nolock) on EX.FAFPROGRAMID = NFG.CAMPAIGNID 
                            left join FAFNFGCAMPAIGNDISPLAYSITE DS on DS.NFGCAMPAIGNID = NFG.ID
                            left join FAFNFGCAMPAIGNDISPLAYSITE DS2 on DS2.NFGCAMPAIGNID = NFG.ID and DS2.SITEID = ES.SITEID 
                            where EX.EVENTID = @EVENTID and NFG.NAME like ''%'' + @SEARCHSTR + ''%'' 
                            group by NFG.ID 
          ) A on A.ID = NFG.ID
                    where  EX.EVENTID = @EVENTID and NFG.NAME like ''%'' + @SEARCHSTR + ''%'' '

            if @SEARCHTYPE = 3
                set @SQL = @SQL + '    and NFG.ALLOWCOMPANIESJOINFROMGROUPPAGE = 1'

        end    /* end is event campaign*/

        if @SEARCHLEADER = 1 and @SEARCHTYPE in (1, 2, 4)
        begin
            set @SQL = @SQL + '
                insert into #BaseList(ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE)
                    select distinct top (@MAXROWS) iTX.TEAMFUNDRAISINGTEAMID as ID, TFT.NAME, case iTX.TYPECODE when 1 then @TEAMLABEL when 2 then @COMPANYLABEL end as [TYPE], iTX.TYPECODE 
                    ,  case when TFT.PARENTTEAMID is not null then TFT.PARENTTEAMID 
                            when NFG.ID is not null then NFG.ID 
                            else TFT.ID end as PARENTGROUPID
                    ,  case when TFT.PARENTTEAMID is not null then pTFT.NAME 
                            when NFG.ID is not null then NFG.NAME 
                            else TFT.NAME end as PARENTGROUPNAME
                    , ''Local'' AS AVAILABLESEARCHTYPE
                    , 1
                      from dbo.TEAMEXTENSION iTX (nolock)
                      join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.ID = iTX.TEAMFUNDRAISINGTEAMID 
                        join TEAMFUNDRAISINGTEAMCAPTAIN TFTC (nolock) on iTX.TEAMFUNDRAISINGTEAMID = TFTC.TEAMFUNDRAISINGTEAMID
                        join CONSTITUENT c (nolock) on TFTC.CONSTITUENTID = c.ID 
                        left join dbo.TEAMFUNDRAISINGTEAM pTFT (nolock) on pTFT.ID = TFT.PARENTTEAMID
                        left join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFGL.ID = iTX.NFGCAMPAIGNLEVELID
                      left join dbo.FAFNFGCAMPAIGN NFG (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID 
                      where iTX.EVENTID = @EVENTID and iTX.TYPECODE <> 3 and iTX.STATUSCODE = 0 and (C.FIRSTNAME like ''%'' + @SEARCHSTR + ''%'' or C.KEYNAME like ''%'' + @SEARCHSTR + ''%'')
            and iTX.TEAMFUNDRAISINGTEAMID not in (select ID from #BaseList)
                    '

            if @SEARCHTYPE = 2 or (@SEARCHTYPE = 4 and @HOUSEHOLDSJOINTEAMS = 0)
            begin
                set @SQL = @SQL + ' and iTX.TYPECODE = 2 ' 
            end
            else if @SEARCHTYPE = 1 or (@SEARCHTYPE = 4 and @HOUSEHOLDSJOINTEAMS = 1)
            begin
                set @SQL = @SQL + ' and iTX.TYPECODE in (1,2)'
            end

        end

        if @SEARCHTYPE in (1,2,4)
    begin
          set @SQL = @SQL + '         
          ;with GetChildren as (
              select case when TFT.ID is not null then TFT.ID when cTX.ID is not null then cTFT.ID else B.ID end as ID, 
                  case when TFT.ID is not null then TFT.NAME when cTX.ID is not null then cTFT.NAME else B.NAME end as NAME, 
                  case when TFT.ID is not null then (case TX.TYPECODE when 1 then @TEAMLABEL when 2 then @COMPANYLABEL end)
                       when cTX.ID is not null then (case cTX.TYPECODE when 1 then @TEAMLABEL when 2 then @COMPANYLABEL end) else B.[TYPE] end as [TYPE], 
                  case when TFT.ID is not null then TX.TYPECODE when cTX.ID is not null then cTX.TYPECODE else B.TYPECODE end as TYPECODE, 
                  B.ID as PARENTGROUPID, 
                  B.NAME as PARENTGROUPNAME,
                  ''Local'' as AVAILABLESEARCHTYPE, 
                  case when @SEARCHTYPE in (1,4) then 1 when @SEARCHTYPE = 2 and TX.TYPECODE = 2 then 1 else 0 end as SELECTABLE
              from #BaseList B 
              left join dbo.FAFNFGCAMPAIGN NFG (nolock) on NFG.ID = B.ID 
              left join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFGL.NFGCAMPAIGNID = NFG.ID 
              left join dbo.TEAMFUNDRAISINGTEAM cTFT (nolock) on B.ID = cTFT.PARENTTEAMID
              left join dbo.TEAMEXTENSION cTX (nolock) on cTX.TEAMFUNDRAISINGTEAMID = cTFT.ID and cTX.EVENTID = @EVENTID and cTX.TYPECODE <> 3    and cTX.STATUSCODE = 0
              left join dbo.TEAMEXTENSION TX (nolock) on TX.NFGCAMPAIGNLEVELID = NFGL.ID and TX.TYPECODE <> 3 and TX.EVENTID = @EVENTID and TX.STATUSCODE = 0
              left join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
              where cTX.ID is not null or TX.ID is not null
          ),
          GetOtherChildren as 
          (
              select ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE 
              from GetChildren
              union
              select TFT.ID, TFT.NAME, case TX.TYPECODE when 1 then @TEAMLABEL when 2 then @COMPANYLABEL end as [TYPE], TX.TYPECODE, 
              C.ID as PARENTGROUPID, 
              C.NAME as PARENTGROUPNAME,  
              ''Local'' AS AVAILABLESEARCHTYPE, 
              case when @SEARCHTYPE in (1,4) then 1 when @SEARCHTYPE = 2 and TX.TYPECODE = 2 then 1 else 0 end AS SELECTABLE 
              from GetChildren C
              left join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.PARENTTEAMID = C.ID 
              join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFT.ID and TX.TYPECODE <> 3 and TX.EVENTID = @EVENTID and TX.STATUSCODE = 0
              where TFT.ID is not null

          )
          insert into #AccumulativeList(ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE)
          select distinct ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE 
          from GetOtherChildren 
          where ID not in(select ID from #AccumulativeList) 
          '
          if (@HOUSEHOLDSJOINTEAMS = 0 and @SEARCHTYPE = 4) or @SEARCHTYPE = 2
              set @SQL = @SQL + ' and TYPECODE <> 1'
        end

        if @SEARCHTYPE in (1,2,4)
        begin
            set @SQL = @SQL + '
            ;With GetParents as 
            (
                select distinct
                    case when TFT.ID is not null then TFT.ID when NFG.ID is not null then NFG.ID else null end ID,
                       case when TFT.ID is not null then TFT.NAME when NFG.ID is not null then NFG.NAME else null end as NAME,
                       case when TFT.ID is not null then case TX.TYPECODE when 1 then @TEAMLABEL when 2 then @COMPANYLABEL end 
                            when NFG.ID is not null then (select TC.DESCRIPTION from dbo.FAFNFGTYPECODE TC where TC.ID = NFG.NFGTYPECODEID) 
                            else null end as [TYPE],
                       case when TFT.ID is not null then TX.TYPECODE when NFG.ID is not null then 5 end as TYPECODE,
                       case when TFT.PARENTTEAMID is not null then TFT.PARENTTEAMID 
                            when NFG2.ID is not null then NFG2.ID 
                            else (case when TFT.ID is not null then TFT.ID when NFG.ID is not null then NFG.ID end) end as PARENTGROUPID,               
                       case when TFT.PARENTTEAMID is not null then pTFT.NAME 
                            when NFG2.ID is not null then NFG2.NAME 
                            else (case when TFT.ID is not null then TFT.NAME when NFG.ID is not null then NFG.NAME end) end as PARENTGROUPNAME,
                       case when TFT.ID is not null then ''Local'' when NFG.ID is not null then ''National'' end as AVAILABLESEARCHTYPE, 
                       /*case when TFT.ID is not null and TX.TYPECODE = 2 and @SEARCHTYPE in (1,2,4) then 1
                            when TFT.ID is not null and TX.TYPECODE = 1 and @SEARCHTYPE in (1,4) then 1
                            when NFG.ID is not null and @SEARCHTYPE in (2,3,4) then 1                        
                            else 0 end AS SELECTABLE */  0 as SELECTABLE
                    from #BaseList B
                    left join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = B.PARENTGROUPID and TX.EVENTID = @EVENTID
                    left join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TX.TEAMFUNDRAISINGTEAMID= TFT.ID /* current parent level */
                    left join dbo.TEAMFUNDRAISINGTEAM pTFT (nolock) on pTFT.ID = TFT.PARENTTEAMID
                    left join dbo.FAFNFGCAMPAIGNLEVEL NFGL2 (nolock) on TX.NFGCAMPAIGNLEVELID = NFGL2.ID /* next parent level if exists */
                    left join dbo.FAFNFGCAMPAIGN NFG2 (nolock) on NFG2.ID = NFGL2.NFGCAMPAIGNID'

            set @SQL = @SQL + '        
                    left join dbo.FAFNFGCAMPAIGN NFG (nolock) on NFG.ID = B.PARENTGROUPID /* parent level 1 */' 

            set @SQL = @SQL + ' where B.TYPECODE <> 5 and B.PARENTGROUPID is not null
            ),
            GetOtherParents as 
            (
                select ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE 
                from GetParents
                Union all
                select NFG.ID, NFG.NAME, (select TC.DESCRIPTION from dbo.FAFNFGTYPECODE TC where TC.ID = NFG.NFGTYPECODEID) as [TYPE],
                5 as TYPECODE, NFG.ID as PARENTGROUPID, NFG.NAME as PARENTGROUPNAME, ''National''  AVAILABLESEARCHTYPE, 0 as SELECTABLE 
                from GetParents P
                left join dbo.FAFNFGCAMPAIGN NFG (nolock) on P.PARENTGROUPID = NFG.ID
        '

            set @SQL = @SQL + ' )

            insert into #AccumulativeList(ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE)
            select distinct ID, NAME, TYPE , TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE  
            from GetOtherParents
            where ID is not null and ID not in (select ID from #AccumulativeList)' 

        end

        set @SQL = @SQL + ' insert into #BaseList(ID, NAME, TYPE, TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE)
        select ID, NAME, TYPE, TYPECODE, PARENTGROUPID, PARENTGROUPNAME, AVAILABLESEARCHTYPE, SELECTABLE
        from #AccumulativeList A where A.ID not in(select ID from #BaseList)

        select distinct top (@MAXROWS) 
        case when TYPECODE = 5 then NFGL.ID else B.ID end as ID
        ,B.NAME
        ,B.TYPE
        ,B.TYPECODE
        ,case when NFG.ID is not null then NFGL.ID else B.PARENTGROUPID end as PARENTGROUPID
        ,B.PARENTGROUPNAME
        ,B.AVAILABLESEARCHTYPE
    ,B.SELECTABLE '


        set @SQL = @SQL + ',L.LEADERS 
                      ,case when B.TYPECODE = 1 and FGSI.TOTALPARTICIPANTS >= @TEAMMEMBERLIMIT then 1 else 0 end as MEMBERLIMITREACHED
                                  ,case when B.TYPECODE = 2 and FGSI.TOTALGROUPS >= @COMPANYTEAMLIMIT then 1 else 0 end as GROUPLIMITREACHED
    from #BaseList B
        left join (
            select dbo.UDA_BUILDLIST(C.NAME) as LEADERS, iTX.ID from #BaseList iTX
                join TEAMFUNDRAISINGTEAMCAPTAIN tftc on iTX.ID = tftc.TEAMFUNDRAISINGTEAMID
                join CONSTITUENT c on tftc.CONSTITUENTID = c.ID 
              group by iTX.ID
        ) L on B.ID = L.ID
        left join dbo.FAFGROUPSUMMARYINFORMATION FGSI (nolock) on FGSI.ID = B.ID 
        left join dbo.FAFNFGCAMPAIGN NFG (nolock) on NFG.ID = B.PARENTGROUPID
        left join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFGL.NFGCAMPAIGNID = NFG.ID and NFGL.HIERARCHYPATH.GetAncestor(1) = ''/''
        order by B.PARENTGROUPNAME, B.NAME
        '    

        set @PARAMDEFINITION = N'@MAXROWS int, 
                                @SEARCHSTR nvarchar(20), 
                                @EVENTID uniqueidentifier, 
                                @SEARCHTYPE tinyint, 
                                @TEAMLABEL nvarchar(200), 
                                @COMPANYLABEL nvarchar(200),
                                @TEAMMEMBERLIMIT int,
                                @COMPANYMEMBERLIMIT int,
                                @COMPANYTEAMLIMIT int,
                                @HOUSEHOLDSJOINTEAMS bit';

        exec sp_executesql @SQL, @PARAMDEFINITION, @MAXROWS=@MAXROWS, 
                                                    @SEARCHSTR=@SEARCHSTR, 
                                                    @EVENTID=@EVENTID, 
                                                    @SEARCHTYPE=@SEARCHTYPE, 
                                                    @TEAMLABEL=@TEAMLABEL, 
                                                    @COMPANYLABEL=@COMPANYLABEL,
                                                    @TEAMMEMBERLIMIT=@TEAMMEMBERLIMIT,
                                                    @COMPANYMEMBERLIMIT =@COMPANYMEMBERLIMIT ,
                                                    @COMPANYTEAMLIMIT=@COMPANYTEAMLIMIT,
                                                    @HOUSEHOLDSJOINTEAMS=@HOUSEHOLDSJOINTEAMS