USP_DATALIST_FAFTEAMHIERARCHY

CREATE procedure dbo.USP_DATALIST_FAFTEAMHIERARCHY
(
  @EVENTID uniqueidentifier,
  @TEAMNAME nvarchar(250) = null,
  @LEADERNAME nvarchar(250) = null,
  @STATUSCODE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on; 

select TFT.ID, --hardcode this to 1000 as a short term solution for 166147

  1 as ISGROUP, 
  case TX.TYPECODE when 1 then 'catalog:Blackbaud.AppFx.FAFEvent.Catalog.dll,Blackbaud.AppFx.FAFEvent.Catalog.square_blue_12.png' 
                   when 2 then 'catalog:Blackbaud.AppFx.FAFEvent.Catalog.dll,Blackbaud.AppFx.FAFEvent.Catalog.diamond_green_12.png' 
                   when 3 then 'catalog:Blackbaud.AppFx.FAFEvent.Catalog.dll,Blackbaud.AppFx.FAFEvent.Catalog.circle_red_12.png' end as IMAGEKEY ,
  TFT.PARENTTEAMID, 
  iC.name as PARENTNAME, 
  TFT.NAME,  
  TFTCs.LEADER,
  (select AMOUNTRAISED from dbo.UFN_FAF_GROUPTOTALSROLLUP(@EVENTID,TFT.ID)) as TOTALFUNDRAISING,
  TFT.GOAL as TOTALFUNDRAISINGGOAL,
  TX.TYPE as GROUPTYPE,
  TX.STATUSCODE,
  TX.STATUS as STATUS
  from TEAMFUNDRAISINGTEAM TFT
  join TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
  left join CONSTITUENT C on TX.TEAMCONSTITUENTID = C.ID
  left join TEAMEXTENSION iTX on iTX.TEAMFUNDRAISINGTEAMID = TFT.PARENTTEAMID
  left join CONSTITUENT iC on iC.ID = iTX.TEAMCONSTITUENTID
  left join (select dbo.UDA_BUILDLIST(C.NAME) as LEADER, iTX.TEAMFUNDRAISINGTEAMID from TEAMEXTENSION iTX
      left join TEAMFUNDRAISINGTEAMCAPTAIN tftc on iTX.TEAMFUNDRAISINGTEAMID = tftc.TEAMFUNDRAISINGTEAMID
      left join CONSTITUENT c on tftc.CONSTITUENTID = c.ID 
    where iTX.EVENTID = @EVENTID 
    group by iTX.TEAMFUNDRAISINGTEAMID
      ) TFTCs on TFTCs.TEAMFUNDRAISINGTEAMID = TFT.ID
  where TX.EVENTID = @EVENTID 
    and ( TFT.NAME like @TEAMNAME + '%' OR @TEAMNAME is null) 
    and ( TFTCs.LEADER like '%' + @LEADERNAME + '%' OR @LEADERNAME is null)
    and ( TX.STATUSCODE = @STATUSCODE OR @STATUSCODE is null)
    and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(TX.EVENTID) EVENTSITE
            where EVENTSITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
           )
      )
  -- Check site security

    and (
          exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(TX.EVENTID) EVENTSITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null))) )
        )

/*
union

select TFTM.ID, 
  0 as ISGROUP,
  'RES:bullet_crystal' as IMAGEKEY,
  TX.TEAMFUNDRAISINGTEAMID AS PARENTTEAMID, 
  iC.NAME as PARENTNAME,
  C.NAME, 
  '' as CAPTAIN
  from TEAMFUNDRAISINGTEAM TFT
  join TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
  join TEAMFUNDRAISINGTEAMMEMBER TFTM on TX.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID
  join TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID
  join CONSTITUENT C on TF.CONSTITUENTID = C.ID
  left join CONSTITUENT iC on TX.TEAMCONSTITUENTID = iC.ID
  where TX.EVENTID = @EVENTID and ( TFT.NAME like '%' + @TEAMNAME + '%' OR @TEAMNAME is null )
      */