USP_DATALIST_CONSTITUENTPROFILE_VOLUNTEERJOBS

Lists all of the volunteer jobs held by a constituent

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@ISVISIBLE	bit	IN	Is visible
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILE_VOLUNTEERJOBS
(
    @CONSTITUENTID uniqueidentifier,
    @ISVISIBLE bit = 1,
    @CURRENTAPPUSERID uniqueidentifier
)
as

set nocount on;

declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)

if @ISVISIBLE = 1
BEGIN
    select
        JOB.NAME as JOB,
        JOBOCCURRENCE.DESCRIPTION as OCCURRENCE, 
        VOLUNTEERLOCATIONCODE.DESCRIPTION as LOCATION,
        Min(VOLUNTEERASSIGNMENT.DATE) as STARTDATE,
        Max(VOLUNTEERASSIGNMENT.DATE) as LATEST
    from dbo.VOLUNTEERASSIGNMENT
    inner join dbo.JOBOCCURRENCE on VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = JOBOCCURRENCE.ID
    inner join dbo.JOB on JOB.ID = JOBOCCURRENCE.JOBID
    left join dbo.VOLUNTEERLOCATIONCODE on VOLUNTEERLOCATIONCODE.ID = JOBOCCURRENCE.LOCATIONCODEID
    where VOLUNTEERASSIGNMENT.VOLUNTEERID = @CONSTITUENTID
    and (
        (@ISSYSADMIN = 1)
        or
        (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, JOB.SITEID) = 1)
    )
    group by JOB.NAME, JOBOCCURRENCE.DESCRIPTION, VOLUNTEERLOCATIONCODE.DESCRIPTION
END