USP_REPORT_JOBOCCURRENCESCHEDULE

            CREATE procedure dbo.USP_REPORT_JOBOCCURRENCESCHEDULE(
                @JOBID uniqueidentifier = null,
                @JOBOCCURRENCEID uniqueidentifier = null,
                @DEPARTMENTID uniqueidentifier = null,
                @RANGE int = null,
                @FROM datetime = null,
                @TO datetime = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount on;

                begin try
                    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);

                    /* @RANGE is a legacy field left to allow for backwards compatibility*/
                    /* if @RANGE is null then use the dates passed in */

                    if @RANGE = 1 --this month

                    BEGIN
                        select @FROM = dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_THISMONTH_LASTDAY(getdate(), 0);
                    END

                    if @RANGE = 2 --next month

                    BEGIN
                        select @FROM = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_NEXTMONTH_LASTDAY(getdate(), 0);
                    END

                    if @RANGE = 3 --this week

                    BEGIN
                        select @FROM = dbo.UFN_DATE_THISWEEK_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_THISWEEK_LASTDAY(getdate(), 0);
                    END

                    if @RANGE = 4 --next week

                    BEGIN
                        select @FROM = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(getdate(), 0);
                        select @TO = dbo.UFN_DATE_NEXTWEEK_LASTDAY(getdate(), 0);
                    END

                    declare @RESULTS table
                    (
                        JOBNAME nvarchar(100),
                        JOBOCCURRENCEDESCRIPTION nvarchar(30),
                        LOCATION nvarchar(100),
                        DEPARTMENT nvarchar(100),
                        ASSIGNMENTDATE datetime,
                        CONSTITUENTID uniqueidentifier,
                        VOLUNTEERNAME nvarchar(200),
                        NUMBER nvarchar(100),
                        STARTTIMEDATE datetime,
                        ENDTIMEDATE datetime,
                        JOBOCCURRENCEID uniqueidentifier
                    )

                    insert into @RESULTS
                    SELECT  JOB.NAME AS JOBNAME, 
                            JOBOCCURRENCE.DESCRIPTION AS JOBOCCURRENCEDESCRIPTION, 
                            VOLUNTEERLOCATIONCODE.DESCRIPTION AS LOCATION, 
                            DEPARTMENTCODE.DESCRIPTION AS DEPARTMENT, 
                            SCHED.ASSIGNMENTDATE, 
                            VOLUNTEERASSIGNMENT.VOLUNTEERID as CONSTITUENTID,
                            null as VOLUNTEERNAME,
                            null as NUMBER,
                            case when len(JOBOCCURRENCE.STARTTIME)=4 then convert(datetime, left(JOBOCCURRENCE.STARTTIME, 2) + ':' + right(JOBOCCURRENCE.STARTTIME, 2)) else null end STARTTIMEDATE,
                            case when len(JOBOCCURRENCE.ENDTIME)=4 then convert(datetime, left(JOBOCCURRENCE.ENDTIME, 2) + ':' + right(JOBOCCURRENCE.ENDTIME, 2)) else null end ENDTIMEDATE,
                            JOBOCCURRENCE.ID as JOBOCCURRENCEID
                    FROM UFN_JOBOCCURRENCE_SCHEDULE(@FROM, @TO, @JOBOCCURRENCEID, null) SCHED
                    INNER JOIN JOBOCCURRENCE ON SCHED.OCCURRENCEID = JOBOCCURRENCE.ID 
                    INNER JOIN JOB ON JOBOCCURRENCE.JOBID = JOB.ID 
                    LEFT OUTER JOIN VOLUNTEERASSIGNMENT ON SCHED.OCCURRENCEID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID AND SCHED.ASSIGNMENTDATE = VOLUNTEERASSIGNMENT.DATE
                    LEFT OUTER JOIN VOLUNTEERLOCATIONCODE ON JOBOCCURRENCE.LOCATIONCODEID = VOLUNTEERLOCATIONCODE.ID 
                    LEFT OUTER JOIN DEPARTMENTCODE ON JOBOCCURRENCE.DEPARTMENTCODEID = DEPARTMENTCODE.ID 
                    WHERE (@DEPARTMENTID is null or JOBOCCURRENCE.DEPARTMENTCODEID = @DEPARTMENTID)
                        and (@JOBID is null or JOB.ID = @JOBID)
                        and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID)=1)
                        and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID)=1)
                    union all

                    --recurring jobs

                    SELECT  JOB.NAME AS JOBNAME, 
                            JOBOCCURRENCE.DESCRIPTION AS JOBOCCURRENCEDESCRIPTION, 
                            VOLUNTEERLOCATIONCODE.DESCRIPTION AS LOCATION, 
                            DEPARTMENTCODE.DESCRIPTION AS DEPARTMENT, 
                            VOLUNTEERASSIGNMENT.DATE as ASSIGNMENTDATE, 
                            VOLUNTEERASSIGNMENT.VOLUNTEERID as CONSTITUENTID,
                            null as VOLUNTEERNAME,
                            null as NUMBER,
                            case when len(JOBOCCURRENCE.STARTTIME)=4 then convert(datetime, left(JOBOCCURRENCE.STARTTIME, 2) + ':' + right(JOBOCCURRENCE.STARTTIME, 2)) else null end STARTTIMEDATE,
                            case when len(JOBOCCURRENCE.ENDTIME)=4 then convert(datetime, left(JOBOCCURRENCE.ENDTIME, 2) + ':' + right(JOBOCCURRENCE.ENDTIME, 2)) else null end ENDTIMEDATE,
                            JOBOCCURRENCE.ID as JOBOCCURRENCEID
                    FROM JOBOCCURRENCE
                    INNER JOIN JOB ON JOBOCCURRENCE.JOBID = JOB.ID 
                    LEFT OUTER JOIN VOLUNTEERASSIGNMENT ON JOBOCCURRENCE.ID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID
                    LEFT OUTER JOIN VOLUNTEERLOCATIONCODE ON JOBOCCURRENCE.LOCATIONCODEID = VOLUNTEERLOCATIONCODE.ID 
                    LEFT OUTER JOIN DEPARTMENTCODE ON JOBOCCURRENCE.DEPARTMENTCODEID = DEPARTMENTCODE.ID 
                    WHERE not exists (select top 1 1 from dbo.UFN_JOBOCCURRENCE_SCHEDULE(VOLUNTEERASSIGNMENT.DATE, VOLUNTEERASSIGNMENT.DATE, JOBOCCURRENCE.ID, null))
                        and (VOLUNTEERASSIGNMENT.DATE between @FROM and @TO)
                        and (@DEPARTMENTID is null or JOBOCCURRENCE.DEPARTMENTCODEID = @DEPARTMENTID)
                        and (@JOBID is null or JOB.ID = @JOBID)
                        and (@JOBOCCURRENCEID is null or JOBOCCURRENCE.ID = @JOBOCCURRENCEID)
                        and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID)=1)
                        and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID)=1)

                    update @RESULTS
                    set
                        VOLUNTEERNAME = 
                            coalesce (
                                case
                                    when CONSTITUENT.ISORGANIZATION = 1 then case CONSTITUENT.KEYNAMEPREFIX when '' then CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.KEYNAMEPREFIX end
                                    else dbo.UFN_NAMEFORMAT_08(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, null, null, null, null, null, null, null)
                                end,
                                'No assignments'
                            ),
                        NUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER)
                    from
                        @RESULTS R
                        left outer join dbo.CONSTITUENT with (nolock) on 
                            R.CONSTITUENTID = 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)
                                    )
                                )
                        left outer join PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1;

                    --Since volunteers the user doesn't have access to (due to constituent record security)

                    --  show up in the results as "No assignment" rows, we need to filter the results for

                    --  those rows.  We should only show a "No assignment" row if there are no non-"No assignment"

                    --  rows for the given job occurrence and even then, only show one such row.


                    select distinct 
                        r1.JOBNAME,
                        r1.JOBOCCURRENCEDESCRIPTION,
                        cast('' as nvarchar(1)) as STARTTIME,
                        cast('' as nvarchar(1)) as ENDTIME,
                        r1.LOCATION,
                        r1.DEPARTMENT,
                        r1.ASSIGNMENTDATE,
                        r1.VOLUNTEERNAME,
                        r1.NUMBER,
                        r1.STARTTIMEDATE,
                        r1.ENDTIMEDATE,
                        r1.JOBOCCURRENCEID
                    from @RESULTS r1
                    where VOLUNTEERNAME <> 'No assignments' 
                        or     (VOLUNTEERNAME = 'No assignments'
                            and not    exists(
                                select r2.JOBNAME 
                                from @RESULTS r2
                                where                            
                                    r2.VOLUNTEERNAME <> 'No assignments' and
                                    (r2.JOBNAME=r1.JOBNAME or (r2.JOBNAME is null and r1.JOBNAME is null)) and
                                    (r2.JOBOCCURRENCEDESCRIPTION=r1.JOBOCCURRENCEDESCRIPTION or (r2.JOBOCCURRENCEDESCRIPTION is null and r1.JOBOCCURRENCEDESCRIPTION is null)) and
                                    (r2.LOCATION=r1.LOCATION or (r2.LOCATION is null and r1.LOCATION is null)) and
                                    (r2.DEPARTMENT=r1.DEPARTMENT or (r2.DEPARTMENT is null and r1.DEPARTMENT is null)) and
                                    (r2.ASSIGNMENTDATE=r1.ASSIGNMENTDATE or (r2.ASSIGNMENTDATE is null and r1.ASSIGNMENTDATE is null)) and
                                    --Bug 3831 - AdamBu - Comparing on NUMBER allows volunteers with number to show with the 'No Assignments' row.

                                    --(r2.NUMBER=r1.NUMBER or (r2.NUMBER is null and r1.NUMBER is null)) and

                                    (r2.STARTTIMEDATE=r1.STARTTIMEDATE or (r2.STARTTIMEDATE is null and r1.STARTTIMEDATE is null)) and
                                    (r2.ENDTIMEDATE=r1.ENDTIMEDATE or (r2.ENDTIMEDATE is null and r1.ENDTIMEDATE is null)) and
                                    (r2.JOBOCCURRENCEID=r1.JOBOCCURRENCEID or (r2.JOBOCCURRENCEID is null and r1.JOBOCCURRENCEID is null))
                                    -- Don't check first and last name since those are used to compute VOLUNTEERNAME.

                                    -- There are better ways of doing this but since we have to keep whole name in for binary compatibility purposes,

                                    -- I don't think it makes sense to not get full name inside of the original select.

                            )
                        )
                    order by
                        --order by date/time first since this is supposed to be schedule

                        r1.ASSIGNMENTDATE,
                        r1.STARTTIMEDATE,
                        r1.ENDTIMEDATE,

                        --then order by the fields which are shown on the report, in the order in which they are shown

                        r1.JOBNAME,
                        r1.JOBOCCURRENCEDESCRIPTION,
                        r1.DEPARTMENT,
                        r1.LOCATION,
                        r1.VOLUNTEERNAME;
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;