USP_REPORT_VOLUNTEERSCHEDULE

            CREATE procedure dbo.USP_REPORT_VOLUNTEERSCHEDULE(
                @VOLUNTEERID uniqueidentifier = null,
                @RANGE int,
                @FROM datetime = null,
                @TO datetime = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount on;

                begin try
                    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);

                    /* @RANGE is a legacy field left to allow for backwards compatibility*/
                    /* if @RANGE is null then use the dates passed in */

                    if @RANGE = 1 --this month

                    begin
                        select @FROM = dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_THISMONTH_LASTDAY(getdate(), 0);
                    end

                    if @RANGE = 2 --next month

                    begin
                        select @FROM = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_NEXTMONTH_LASTDAY(getdate(), 0);
                    end

                    if @RANGE = 3 --this week

                    begin
                        select @FROM = dbo.UFN_DATE_THISWEEK_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_THISWEEK_LASTDAY(getdate(), 0);
                    end

                    if @RANGE = 4 --next week

                    begin
                        select @FROM = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_NEXTWEEK_LASTDAY(getdate(), 0);
                    end

                    select    JOB.NAME as JOBNAME, 
                            JOBOCCURRENCE.DESCRIPTION as JOBOCCURRENCEDESCRIPTION, 
                            '' STARTTIME, --No longer used

                            '' ENDTIME,   --No longer used

                            VOLUNTEERLOCATIONCODE.DESCRIPTION as LOCATION, 
                            VOLUNTEERASSIGNMENT.DATE, 
                            NF.NAME as VOLUNTEERNAME,
                            case when len(JOBOCCURRENCE.STARTTIME)=4 then convert(datetime, left(JOBOCCURRENCE.STARTTIME, 2) + ':' + right(JOBOCCURRENCE.STARTTIME, 2)) else null end STARTTIMEDATE,
                            case when len(JOBOCCURRENCE.ENDTIME)=4 then convert(datetime, left(JOBOCCURRENCE.ENDTIME, 2) + ':' + right(JOBOCCURRENCE.ENDTIME, 2)) else null end ENDTIMEDATE
                    from VOLUNTEERASSIGNMENT 
                    inner join JOBOCCURRENCE on VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = JOBOCCURRENCE.ID 
                    inner join JOB on JOBOCCURRENCE.JOBID = JOB.ID 
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) NF
                    left outer join VOLUNTEERLOCATIONCODE on JOBOCCURRENCE.LOCATIONCODEID = VOLUNTEERLOCATIONCODE.ID 
                    left outer join PHONE on PHONE.CONSTITUENTID = VOLUNTEERASSIGNMENT.VOLUNTEERID and PHONE.ISPRIMARY = 1
                    where (VOLUNTEERASSIGNMENT.VOLUNTEERID = @VOLUNTEERID and VOLUNTEERASSIGNMENT.DATE between @FROM and @TO)
                    and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID)=1)
                    and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID)=1)
                    and (@ISADMIN = 1 or 
                            (
                                (@APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, VOLUNTEERASSIGNMENT.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                and
                                (@APPUSER_IN_NONSITEROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, VOLUNTEERASSIGNMENT.VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1)
                            )
                        )
                    order by DATE, STARTTIMEDATE, ENDTIMEDATE asc
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;