USP_DATALIST_RESEARCHGROUPS

                CREATE procedure dbo.USP_DATALIST_RESEARCHGROUPS (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @NAME nvarchar(100) = null,
                    @ADDEDBY nvarchar(128) = null,
                    @DATEADDEDCODE smallint = null,

                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,

                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                ) as
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    declare @STARTDATE datetime;
                    if @DATEADDEDCODE = 1 --Last 7 days

                        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-6,@CURRENTDATE));
                    else if @DATEADDEDCODE = 2 --Last 30 days

                        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
                    else if @DATEADDEDCODE = 3 --Last 90 days

                        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
                    else
                        set @STARTDATE = @CURRENTDATE;

                    declare @ENDDATE datetime;
                    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

                    declare @SITES table (SITEID uniqueidentifier);
                    if @SECURITYFEATUREID is not null
                        insert into @SITES
                        select S.SITEID
                        from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) [S]  

                    select
                        RG.ID,
                        RG.NAME,
                        RG.DESCRIPTION,
                        RG.NUMMEMBERS,
                        RG.OWNERID,
                        AU.USERNAME OWNER,
                        CA.USERNAME ADDEDBY,
                        RG.DATEADDED,
                        case when (
                            RG.OTHERSCANMODIFY = 1 or
                            RG.OWNERID = @CURRENTAPPUSERID or
                            RG.OWNERID is null or
                            dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1) then 1
                            else 0
                            end USERCANMODIFY,
                        (
                            select dbo.UDA_BUILDLIST(SITE.NAME) 
                            from dbo.RESEARCHGROUPSITE
                            inner join dbo.SITE on SITE.ID = RESEARCHGROUPSITE.SITEID
                            where RESEARCHGROUPSITE.RESEARCHGROUPID = RG.ID
                        ) as [SITES]
                    from
                        dbo.RESEARCHGROUP RG
                    left join
                        dbo.APPUSER AU on AU.ID = RG.OWNERID
                    left join
                        dbo.CHANGEAGENT CA on CA.ID = RG.ADDEDBYID
                    where
                        ((@NAME is null or @NAME = '') or RG.NAME like '%' + @NAME + '%')
                        and ((@ADDEDBY is null or @ADDEDBY = '') or @ADDEDBY = CA.USERNAME)
                        and (@DATEADDEDCODE is null or not @DATEADDEDCODE in (1,2,3) or (RG.DATEADDED >= @STARTDATE and RG.DATEADDED <= @ENDDATE))
                        and (@SECURITYFEATUREID is null 
                                or exists(SELECT SITELIST.SITEID 
                                            from dbo.UFN_SITEID_MAPFROM_RESEARCHGROUPID(RG.ID) RGS
                                            inner join @SITES SITELIST 
                                            on 
                                                SITELIST.SITEID = RGS.SITEID
                                                    or 
                                                (SITELIST.SITEID is null and RGS.SITEID is null)
                                        )
                            )
                        and
                            (--Apply user-defined site filtering 

                                @SITEFILTERMODE = 0 
                                or 
                                exists (select ID from dbo.RESEARCHGROUPSITE RGS 
                                        inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) S on S.SITEID = RGS.SITEID
                                        where RGS.RESEARCHGROUPID = RG.ID) 
                            )
                    order by RG.NAME