USP_JOBOCCURRENCE_CALENDARITEMS

CREATE procedure dbo.USP_JOBOCCURRENCE_CALENDARITEMS
(
    @JOBOCCURRENCEID uniqueidentifier = null, 
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @INCLUDE_OPENINGS bit = 1
)
as
begin
    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);

    --Adjust the start and end dates to fit within the occurrence's constraints if given

    declare @RECURRENCESTARTDATE datetime;
    declare @RECURRENCEENDDATE datetime;

    select
        @RECURRENCESTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(RECURRENCESTARTDATE),
        @RECURRENCEENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(RECURRENCEENDDATE)
    from dbo.JOBOCCURRENCE
    where
        ID = @JOBOCCURRENCEID

    if @RECURRENCESTARTDATE > @STARTDATE
        set @STARTDATE = @RECURRENCESTARTDATE
    if @RECURRENCEENDDATE < @ENDDATE
        set @ENDDATE = @RECURRENCEENDDATE

    declare @DATES table ([DATE] date)

    insert into @DATES (DATE) 
    select 
        DATEADD(d,NUM,@STARTDATE)
    from 
        NUMBERS 
    where 
        DATEADD(d,NUM,@STARTDATE) <= @ENDDATE

    select    
        JOBOCCURRENCE.JOBID,
        ITEMS.ASSIGNMENTDATE,
        JOBOCCURRENCE.DESCRIPTION NAME,
        JOBOCCURRENCE.STARTTIME,
        JOBOCCURRENCE.ENDTIME,
        case 
            when @INCLUDE_OPENINGS = 0 then
                (case 
                    when ITEMS.OPENINGS = 0 then 
                        '(No openings)'
                    when ITEMS.OPENINGS < 0 then
                        '(Overbooked by ' + cast(abs(ITEMS.OPENINGS) as varchar)+ ')' end ) 
            else
                (case 
                    when ITEMS.OPENINGS = 1 then
                        '(' + cast(coalesce(ITEMS.OPENINGS, -1) as varchar) + ' Opening)'
                    else
                        '(' + cast(coalesce(ITEMS.OPENINGS, -1) as varchar) + ' Openings)' end) end
        + 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 = @JOBOCCURRENCEID and 
                ITEMS.ASSIGNMENTDATE = VA.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 (
    select JOBOCCURRENCE.VOLUNTEERSNEEDED - count(VOLUNTEERASSIGNMENT.ID) OPENINGS, DATES.ASSIGNMENTDATE
        from (
            select 
                TEMP.DATE ASSIGNMENTDATE
            from JOBOCCURRENCE
            inner join @DATES TEMP
                on dbo.UFN_JOBSCHEDULE_DATEVALID2(TEMP.DATE,JOBOCCURRENCE.TYPECODE,JOBOCCURRENCE.STARTDATE,JOBOCCURRENCE.RECURRENCESTARTDATE,JOBOCCURRENCE.ENDDATE,JOBOCCURRENCE.RECURRENCEENDDATE) = 1
                    and dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES(JOBOCCURRENCE.DAYOFWEEKCODE, TEMP.DATE) = 1
            where JOBOCCURRENCE.ID = @JOBOCCURRENCEID
            UNION 
            select
                VOLUNTEERASSIGNMENT.DATE ASSIGNMENTDATE 
            from dbo.VOLUNTEERASSIGNMENT 
            where VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = @JOBOCCURRENCEID 
                and (VOLUNTEERASSIGNMENT.DATE is null or VOLUNTEERASSIGNMENT.DATE between @STARTDATE and @ENDDATE)) DATES
        left outer join VOLUNTEERASSIGNMENT 
            on VOLUNTEERASSIGNMENT.DATE = DATES.ASSIGNMENTDATE
            and JOBOCCURRENCEID = @JOBOCCURRENCEID
        inner join dbo.JOBOCCURRENCE 
            on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
        group by JOBOCCURRENCE.VOLUNTEERSNEEDED, DATES.ASSIGNMENTDATE) ITEMS
    inner join dbo.JOBOCCURRENCE 
        on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
    where 
        (@INCLUDE_OPENINGS = 0 and ITEMS.OPENINGS <= 0) or 
        (@INCLUDE_OPENINGS = 1 and ITEMS.OPENINGS > 0);
end