UFN_GROUP_GETCURRENTMEMBERS

Returns the current members of a constituent group including itself.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@GROUPID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@DATAFORMINSTANCEID	uniqueidentifier	IN
@INCLUDEGROUP	bit	IN
Definition

Copy


            CREATE function dbo.UFN_GROUP_GETCURRENTMEMBERS
            (
                @GROUPID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @DATAFORMINSTANCEID uniqueidentifier = null,
                @INCLUDEGROUP bit = 1
            )
            returns @members table
            (
                ID uniqueidentifier,
                NAME nvarchar(700)
            )
            as
            begin
                if @INCLUDEGROUP = 1
                    insert into @members
                    (
                        ID,
                        NAME
                    )
                    select
                        @GROUPID,
                        NF.NAME
                    from dbo.UFN_CONSTITUENT_DISPLAYNAME(@GROUPID) NF

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

                insert into @members
                (
                    ID,
                    NAME
                )
                select
                    GM.MEMBERID,
                    NF.NAME
                from dbo.GROUPMEMBER GM
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GM.MEMBERID) NF
                where
                    GM.GROUPID = @GROUPID
                    -- the GMDR.DATETO is set to the earliest time on that day, so use a strictly greater than in checks using current day

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                    and 
                    (
                    (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1) or
                    (dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @DATAFORMINSTANCEID, GM.MEMBERID) = 1)
                    )

                return
            end