USP_DATALIST_JOBOCCURRENCESCHEDULE_CALENDAR

This datalist returns all dates for an occurrence including assignments, used by the Job Occurrence Schedule Calendar.

Parameters

Parameter	Parameter Type	Mode	Description
@JOBOCCURRENCEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CONTEXTRECORDID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



create procedure dbo.USP_DATALIST_JOBOCCURRENCESCHEDULE_CALENDAR (
    @JOBOCCURRENCEID uniqueidentifier = null, 
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @CONTEXTRECORDID uniqueidentifier = 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    coalesce(SCHEDULE.ASSIGNMENTDATE, VA.DATE),
            --JOB.NAME,

            JOBOCCURRENCE.STARTTIME,
            JOBOCCURRENCE.ENDTIME,
            'Positions: ' + cast(coalesce(SCHEDULE.VOLUNTEERSNEEDED, 0) as varchar)+ CHAR(10) + 
            'Openings: ' + cast(coalesce(SCHEDULE.OPENINGS, -1) as varchar)+ CHAR(10) + 'Volunteers: ' +
            ltrim(replace(dbo.UDA_BUILDLIST(distinct '|' + CONSTITUENT.NAME), '|', CHAR(10))) DESCRIPTION
    from dbo.[UFN_JOBOCCURRENCE_SCHEDULE](@STARTDATE,@ENDDATE,@JOBOCCURRENCEID,null) SCHEDULE
    full join dbo.VOLUNTEERASSIGNMENT VA
        on VA.JOBOCCURRENCEID = SCHEDULE.OCCURRENCEID
            and SCHEDULE.ASSIGNMENTDATE = VA.DATE
    inner join dbo.JOBOCCURRENCE on JOBOCCURRENCE.ID = coalesce(VA.JOBOCCURRENCEID, SCHEDULE.OCCURRENCEID)
    inner join dbo.JOB on JOB.ID = JOBOCCURRENCE.JOBID
    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 VA.DATE is null or VA.DATE between @STARTDATE and @ENDDATE
        and JOBOCCURRENCE.ID = @JOBOCCURRENCEID
    group by SCHEDULE.ASSIGNMENTDATE, JOB.NAME,
            JOBOCCURRENCE.STARTTIME, JOBOCCURRENCE.ENDTIME,
            SCHEDULE.VOLUNTEERSNEEDED, SCHEDULE.OPENINGS,
            VA.DATE
    order by SCHEDULE.ASSIGNMENTDATE, JOB.NAME;