USP_DATALIST_CONSTITUENTPROFILEDASHBOARDVOLUNTEERGENERAL

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDVOLUNTEERGENERAL
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier
                    )
                    as
                        set nocount on;

                        declare @ISADMIN bit;
                        declare @APPUSER_IN_NONRACROLE bit;
                        declare @APPUSER_IN_NOSECGROUPROLE bit;

                        set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);                  
                        if @ISADMIN <> 1 begin 
                            set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                            if @APPUSER_IN_NONRACROLE <> 1
                                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                        end

                        if @ISVISIBLE = 1
                        begin
                            declare @STARTDATE as datetime
                            select @STARTDATE = (select min(DATEFROM) from dbo.VOLUNTEERDATERANGE where CONSTITUENTID = @CONSTITUENTID)

                            select
                                NF.NAME as SPONSOR,
                                VOLUNTEER.EMERGENCYCONTACTNAME,
                                VOLUNTEER.EMERGENCYCONTACTPHONE,
                                @STARTDATE as STARTDATE,
                                datediff(year, @STARTDATE, getdate()) as YEARSSERVICE,
                                coalesce((select sum(HOURSWORKED) from dbo.TIMESHEET where VOLUNTEERID = @CONSTITUENTID), 0) as HOURSWORKED
                            from dbo.VOLUNTEER
                            left outer join 
                                dbo.CONSTITUENT on CONSTITUENT.ID = VOLUNTEER.SPONSORID
                                and (@ISADMIN = 1 or 
                                    @APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, VOLUNTEER.SPONSORID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                            where
                                VOLUNTEER.ID = @CONSTITUENTID;
                        end