USP_DATALIST_EVENTPROFILEREPORT_VOLUNTEERASSIGNMENTS

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

    if @ISVISIBLE = 1
        begin
            declare @ISADMIN bit;
            declare @APPUSER_IN_NONRACROLE bit;
            declare @APPUSER_IN_NOSECGROUPROLE 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);

            declare @VOLUNTEERSNEEDED integer = (select VOLUNTEERSNEEDED from dbo.JOBOCCURRENCE where ID = @JOBOCCURRENCEID);

            declare @EVENTSTARTDATE datetime;
            declare @EVENTENDDATE datetime;

            select
                @EVENTSTARTDATE = EVENT.STARTDATE,
                @EVENTENDDATE = EVENT.ENDDATE
            from dbo.JOBOCCURRENCE
            inner join dbo.EVENT
                on JOBOCCURRENCE.EVENTID = EVENT.ID
            where JOBOCCURRENCE.ID = @JOBOCCURRENCEID;

            declare @SCHEDULE table (
                ASSIGNMENTDATE datetime
            );

            insert into @SCHEDULE
                select
                    ASSIGNMENTDATE
                from dbo.UFN_JOBOCCURRENCE_SCHEDULE(dbo.UFN_DATE_GETEARLIESTTIME(getdate()), dateadd(yy, 1, coalesce(@EVENTENDDATE, @EVENTSTARTDATE)), @JOBOCCURRENCEID, null);

            select
                VOLUNTEERASSIGNMENT.VOLUNTEERID,
                SCHEDULE.ASSIGNMENTDATE,
                NF.NAME as VOLUNTEERNAME,
                'http://www.blackbaud.com/VOLUNTEERID?VOLUNTEERID='+convert (nvarchar(36),VOLUNTEERASSIGNMENT.VOLUNTEERID) as VOLUNTEERLINK,
                @VOLUNTEERSNEEDED as TOTALNEEDED    

            from @SCHEDULE SCHEDULE                
            left outer join dbo.VOLUNTEERASSIGNMENT
                on SCHEDULE.ASSIGNMENTDATE = VOLUNTEERASSIGNMENT.DATE 
                and VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = @JOBOCCURRENCEID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) NF

            where @ISADMIN = 1
                 or @APPUSER_IN_NONRACROLE = 1
                 or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, VOLUNTEERASSIGNMENT.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1

            order by SCHEDULE.ASSIGNMENTDATE, VOLUNTEERNAME
        end