USP_REPORT_DAILYSCHEDULE

            CREATE procedure dbo.USP_REPORT_DAILYSCHEDULE(
                @TO datetime,
                @FROM datetime,
                @DEPARTMENTID uniqueidentifier = 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);

                    set @FROM = dbo.[UFN_DATE_GETEARLIESTTIME](@FROM);
                    set @TO = dbo.[UFN_DATE_GETLATESTTIME](@TO);


                    SELECT  JOB.NAME AS JOBNAME, 
                            JOBOCCURRENCE.DESCRIPTION AS JOBOCCURRENCEDESCRIPTION, 
                            '' STARTTIME, 
                            '' ENDTIME, 
                            DEPARTMENTCODE.DESCRIPTION AS DEPARTMENT, 
                            VOLUNTEERASSIGNMENT.DATE ASSIGNMENTDATE, 
                            NF.NAME AS VOLUNTEERNAME,
                            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
                    FROM VOLUNTEERASSIGNMENT
                    INNER JOIN JOBOCCURRENCE ON JOBOCCURRENCE.ID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID 
                    INNER JOIN JOB ON JOBOCCURRENCE.JOBID = JOB.ID 
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(VOLUNTEERASSIGNMENT.VOLUNTEERID) NF
                    LEFT OUTER JOIN DEPARTMENTCODE ON JOBOCCURRENCE.DEPARTMENTCODEID = DEPARTMENTCODE.ID 
                    WHERE VOLUNTEERASSIGNMENT.DATE between @FROM and @TO
                        and (@DEPARTMENTID is null or JOBOCCURRENCE.DEPARTMENTCODEID = @DEPARTMENTID)
                        and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID)=1)
                        and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID)=1)
                        and (@ISADMIN = 1 or 
                                (
                                    (@APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, VOLUNTEERASSIGNMENT.VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                    and
                                    (@APPUSER_IN_NONSITEROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, VOLUNTEERASSIGNMENT.VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1)
                                )
                            )
                    ORDER BY ASSIGNMENTDATE, STARTTIME, ENDTIME, VOLUNTEERNAME;

                end try

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

                return 0;