USP_DATALIST_VOLUNTEERSCHEDULE_1_1

This datalist returns all assignments for a volunteer.
Parameters

Parameter	Parameter Type	Mode	Description
@VOLUNTEERID	uniqueidentifier	IN	Volunteer
@FROM	datetime	IN	From
@TO	datetime	IN	To
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


            create procedure dbo.USP_DATALIST_VOLUNTEERSCHEDULE_1_1
            (
                @VOLUNTEERID uniqueidentifier = null, 
                @FROM datetime, 
                @TO datetime, 
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount on;

                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE bit;

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                select    VOLUNTEERASSIGNMENT.DATE,
                        JOB.NAME,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        ltrim(replace(dbo.UDA_BUILDLIST(distinct '|' + CONSTITUENT.NAME), '|', char(10))),
                        count(CONSTITUENT.ID),
                        JOBOCCURRENCE.ID,
                        JOBOCCURRENCE.VOLUNTEERSNEEDED
                from dbo.VOLUNTEERASSIGNMENT
                inner join dbo.JOBOCCURRENCE 
                    on dbo.VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = dbo.JOBOCCURRENCE.ID
                inner join dbo.JOB
                    on dbo.JOBOCCURRENCE.JOBID = dbo.JOB.ID
                left outer join dbo.VOLUNTEERASSIGNMENT VA
                    on VA.JOBOCCURRENCEID = JOBOCCURRENCE.ID
                        and VOLUNTEERASSIGNMENT.DATE = VA.DATE
                left outer join dbo.CONSTITUENT
                    on VA.VOLUNTEERID = CONSTITUENT.ID
                        and (@ISADMIN = 1 or 
                                (@APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                and
                                (@APPUSER_IN_NONSITEROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                            )
                where VOLUNTEERASSIGNMENT.VOLUNTEERID = @VOLUNTEERID
                        and VOLUNTEERASSIGNMENT.DATE between @FROM and @TO
                        and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID) = 1)
                        and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID) = 1)
                group by JOB.NAME,
                        VOLUNTEERASSIGNMENT.DATE,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        JOBOCCURRENCE.ID,
                        JOBOCCURRENCE.VOLUNTEERSNEEDED
                order by VOLUNTEERASSIGNMENT.DATE;