USP_DATALIST_FUNDRAISINGGROUPMERGEDATABASIC

CREATE procedure dbo.USP_DATALIST_FUNDRAISINGGROUPMERGEDATABASIC
(
  @EVENTID uniqueidentifier,
  @GROUPID uniqueidentifier,
  @RECIPIENTCONSTITUENTID uniqueidentifier = null -- constituent leader ID if applicable

)
  as
      set nocount on;

  declare @FGPageUrl nvarchar(500), @TYPECODE int, @dashboardURL nvarchar(500), 
          @IsLeader bit = 0,
          @FGPageUrlWithOutTracking nvarchar(500)

  --Get typeCode of group

  select @TYPECODE=TYPECODE from TEAMEXTENSION where TEAMFUNDRAISINGTEAMID=@GROUPID 

  select @IsLeader=1 from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where TEAMFUNDRAISINGTEAMID = @GROUPID and CONSTITUENTID = @RECIPIENTCONSTITUENTID

select @FGPageUrl=dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@GROUPID,@EVENTID,TYPECODE)
    from TEAMEXTENSION 
    where TEAMFUNDRAISINGTEAMID=@GROUPID
        and TEAMEXTENSION.EVENTID=@EVENTID 

set @FGPageUrlWithOutTracking = @FGPageUrl

  -- If it is the only parameter I need ? instead of & for a valid URL

    IF CHARINDEX( '?',  @FGPageUrl ) = 0 
        begin
            declare @personalpageid int
            select @personalpageid=VALUE from dbo.CMSSITESETTING
            where CMSSITESETTING.ENUMID = 30 and CMSSITESETTING.CLIENTSITESID in (select CLIENTSITESID from dbo.EVENTEXTENSION where EVENTID = @EVENTID) 
            set @FGPageUrl=(case when RIGHT(@FGPageUrl,1)='/' then LEFT(@FGPageUrl,len(@FGPageUrl)-1) else @FGPageUrl end) + '?ch=1&frmfaf=1'
        end
    ELSE
          set @FGPageUrl=(case when RIGHT(@FGPageUrl,1)='/' then LEFT(@FGPageUrl,len(@FGPageUrl)-1) else @FGPageUrl end) + '&ch=1&frmfaf=1'

  select @dashboardURL = S2.VALUE + '?pid=' + S1.VALUE + '&tab=' + cast(@TYPECODE as nvarchar(2))
    from EVENTEXTENSION EX 
    left join CMSSITESETTING S1 on EX.CLIENTSITESID = S1.CLIENTSITESID and S1.ENUMID = 38
    left join CMSSITESETTING S2 on EX.CLIENTSITESID = S2.CLIENTSITESID and S2.ENUMID = 10
    where EX.EVENTID = @EVENTID

  select 
    FGID = TeamC.LookupID
  , FGName = T.NAME
  , FGLeaderName = C.NAME
  , FGTypeName = TE.[Type]
  , FGPageUrl =@FGPageUrl
  , FGDashboardURL=@dashboardURL
  ,    FGPageUrlWithOutTracking = @FGPageUrlWithOutTracking
  from dbo.TEAMFUNDRAISINGTEAM T
  left outer join dbo.TEAMEXTENSION TE on T.ID = TE.TEAMFUNDRAISINGTEAMID 
  left outer join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC on T.ID = TC.TEAMFUNDRAISINGTEAMID and (@IsLeader=0 or (TC.CONSTITUENTID = @RECIPIENTCONSTITUENTID and @IsLeader = 1))
  left outer join dbo.CONSTITUENT C on C.ID = TC.CONSTITUENTID
  left join dbo.CONSTITUENT TeamC ON TeamC.ID=TE.TEAMCONSTITUENTID
  where T.ID = @GROUPID