UFN_QUERY_RESEARCHGROUPSBYRESEARCHGROUP

CREATE function [dbo].[UFN_QUERY_RESEARCHGROUPSBYRESEARCHGROUP]
(
 @CURRENTAPPUSERID uniqueidentifier = null,
 @FAVORITESONLY bit = 0,
 @MYLISTSONLY bit = 0,
 @ACCESSEDONLY bit = 0,

 @SITEFILTERMODE tinyint = 0,
 @SITESSELECTED xml = null,
 @SECURITYFEATUREID uniqueidentifier = 'c026c5ca-caa5-4c5d-9c26-0f55cae36a3a',
 @SECURITYFEATURETYPE tinyint = 10
)
returns table
as

  return 

                select     
                    RG.ID as ID,                        
          dbo.UFN_RESEARCHGROUP_USERCANMODIFY(RG.ID, @CURRENTAPPUSERID) as USERCANMODIFY,
                    case coalesce(FAV.RESEARCHGROUPID,'00000000-0000-0000-0000-000000000000')
                      when '00000000-0000-0000-0000-000000000000' then 0 else 1 end as FAVORITEBIT, --Favorites

                    case coalesce(FAV.RESEARCHGROUPID,'00000000-0000-0000-0000-000000000000')
                      when '00000000-0000-0000-0000-000000000000' then 'catalog:Blackbaud.AppFx.ProspectResearch.Catalog.dll,Blackbaud.AppFx.ProspectResearch.Catalog.favorites_gray_star_16.png'
                        else 'catalog:Blackbaud.AppFx.ProspectResearch.Catalog.dll,Blackbaud.AppFx.ProspectResearch.Catalog.favorites_blue_star_16.png'
            end as FAVORITEPIC, --Favorites

                    RG.NAME as NAME,--List name

                    RG.NUMMEMBERS as RECORDS,--Records

                    RG.DESCRIPTION as DESCRIPTION, --Description

                    AU.USERNAME as OWNER,--Owner

                    dbo.UFN_RESEARCHGROUP_LASTWEALTHPOINTUPDATE(RG.ID) as LASTWEALTHPOINTUPDATE, --Last screening date

                    CA.USERNAME as ADDEDBY,--Added by

                    CAT.DESCRIPTION as CATEGORY,--Category

                    RG.DATEADDED as DATEADDED,--Date added

                    FUN.ID as FUNDRAISERID,
                    FUN.NAME as FUNDRAISERNAME, --Fundraiser

                    RG.LASTRE7SYNC, --Last RE7 sync date

                    RG.ALLOWREFRESH,
                    case when RG.NUMMEMBERS <= 500 and RG.NUMMEMBERS > 0 then 1 ELSE 0 end as CANASSIGNPROSPECTMANAGER,
                    case when (exists(SELECT top 1 1 FROM dbo.RESEARCHGROUPMEMBER RGM
                                        inner join dbo.RE7INTEGRATIONCONSTITUENTMAP RE7MAP on RE7MAP.ID = RGM.CONSTITUENTID
                                    where RGM.RESEARCHGROUPID = RG.ID)) then 1 else 0 end as ENABLESYNCRE7,
                    dbo.UFN_MAPPING_BUILDCONTEXTRECORDID(MAPENTITY.ID,RG.ID,null) as MAPCONTEXTID,
          RG.LASTREFRESH,
          VISIT.DATEACCESSED as LASTACCESSED,
          (select 
                dbo.UDA_BUILDLIST(SITE.NAME) 
            FROM 
                dbo.SITE 
                inner join dbo.RESEARCHGROUPSITE ON RESEARCHGROUPSITE.SITEID = SITE.ID
            where
                RESEARCHGROUPSITE.RESEARCHGROUPID = RG.ID
            ) as SITES
                --Primary Manager

                --Researcher

              from 
          dbo.RESEARCHGROUP RG
                left join
                    dbo.APPUSER AU on AU.ID = RG.OWNERID
                left join
                    dbo.CHANGEAGENT CA on CA.ID = RG.ADDEDBYID 
                left join
                    dbo.RESEARCHGROUPCATEGORYCODE CAT on CAT.ID = RG.CATEGORYCODEID
                left join
                    dbo.RESEARCHGROUPFAVORITE FAV on FAV.RESEARCHGROUPID = RG.ID and FAV.APPUSERID = @CURRENTAPPUSERID
                left join
                    dbo.CONSTITUENT FUN on FUN.ID = RG.FUNDRAISERID
        left join
          dbo.RESEARCHGROUPLASTVISITED VISIT on RG.ID = VISIT.RESEARCHGROUPID and VISIT.APPUSERID = @CURRENTAPPUSERID
                left join
                    (select RESEARCHGROUPID, MAX(DATERETRIEVED) as DATERETRIEVED 
                        from dbo.WPSEARCHHISTORY
                        where RESEARCHGROUPID is not null 
                        group by RESEARCHGROUPID) SH on RG.ID = SH.RESEARCHGROUPID
                outer apply 
                    (select case when dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('6f77d512-d0d1-444f-9b46-b8603a6fe5f1')=1
                    then N'20F0E455-384C-4E63-B3C8-DAA6A238AD7F' else N'065A6FAB-669C-4032-B616-D57254F340AE' end as ID ) as MAPENTITY
        where (isnull(@FAVORITESONLY, 0) = 0 or FAV.RESEARCHGROUPID is not null)
            and (isnull(@MYLISTSONLY, 0) = 0 or RG.OWNERID = @CURRENTAPPUSERID)
            and dbo.UFN_RESEARCHGROUP_USERCANACCESS(RG.ID, @CURRENTAPPUSERID) = 1
            and (isnull(@ACCESSEDONLY, 0) = 0 or VISIT.RESEARCHGROUPID is not null)
            and dbo.UFN_RESEARCHGROUP_USERHASSITEACCESS(RG.ID,@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) = 1
            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) 
                            )