USP_DATALIST_VOLUNTEERSCHEDULE_CALENDAR

CREATE procedure dbo.USP_DATALIST_VOLUNTEERSCHEDULE_CALENDAR
(
    @VOLUNTEERID uniqueidentifier = null, 
    @STARTDATE date = null, 
    @ENDDATE date = null,
    @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    
        JOB.ID JOBID,
        VOLUNTEERASSIGNMENT.ID VOLUNTEERASSIGNMENTID,
        VOLUNTEERASSIGNMENT.DATE,
        JOB.NAME,
        JOBOCCURRENCE.STARTTIME,
        JOBOCCURRENCE.ENDTIME,
        (cast(@VOLUNTEERID as nvarchar(36)) + cast(JOBOCCURRENCE.ID as nvarchar(36))) ADD_MULTIPLE_ACTION,
        + CHAR(10) + 'Volunteers: ' + CHAR(10) +
        (select stuff((
            select ';' + CHAR(10) + NF.NAME
            from dbo.VOLUNTEERASSIGNMENT VA
            left outer join dbo.CONSTITUENT
                on VA.VOLUNTEERID = CONSTITUENT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
            where 
                VA.JOBOCCURRENCEID = JOBOCCURRENCE.ID and VA.DATE = VOLUNTEERASSIGNMENT.DATE 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)
                )
            order by CONSTITUENT.KEYNAME
            for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) AS DESCRIPTION
    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
    where VOLUNTEERASSIGNMENT.VOLUNTEERID = @VOLUNTEERID
            and VOLUNTEERASSIGNMENT.DATE between @STARTDATE and @ENDDATE
            and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID) = 1)
            and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID) = 1)
    order by VOLUNTEERASSIGNMENT.DATE;