USP_DATAFORMTEMPLATE_VIEW_JOBOCCURRENCEPAGEDATA

The load procedure used by the view dataform template "Job Occurrence View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@STARTDATE	datetime	INOUT	Dates
@ENDDATE	datetime	INOUT	ENDDATE
@STARTMONTHDAY	UDT_MONTHDAY	INOUT	Dates
@ENDMONTHDAY	UDT_MONTHDAY	INOUT	ENDMONTHDAY
@STARTTIME	UDT_HOURMINUTE	INOUT	Times
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@TYPECODE	tinyint	INOUT	Typecode
@TYPE	nvarchar(25)	INOUT	Type
@DAYOFWEEK	nvarchar(15)	INOUT	Day of week
@VOLUNTEERSNEEDED	int	INOUT	Needed
@COMMENTS	nvarchar(max)	INOUT	Comments
@LOCATION	nvarchar(250)	INOUT	Location
@DEPARTMENT	nvarchar(250)	INOUT	Department
@EVENT	nvarchar(50)	INOUT	Event
@SITE	nvarchar(1024)	INOUT	Site
@SCHEDULESUMMARY	nvarchar(1024)	INOUT	Schedule
@SCHEDULESUMMARYCOLLECTION	xml	INOUT	Schedule
@MORETHANFIVE	bit	INOUT	More than five

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_JOBOCCURRENCEPAGEDATA
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @STARTDATE datetime = null output,
                    @ENDDATE datetime = null output,
                    @STARTMONTHDAY dbo.UDT_MONTHDAY = null output,
                    @ENDMONTHDAY dbo.UDT_MONTHDAY = null output,
                    @STARTTIME dbo.UDT_HOURMINUTE = null output,
                    @ENDTIME dbo.UDT_HOURMINUTE = null output,
                    @TYPECODE tinyint = null output,
                    @TYPE nvarchar(25) = null output,
                    @DAYOFWEEK nvarchar(15) = null output,
                    @VOLUNTEERSNEEDED int = null output,
                    @COMMENTS nvarchar(max) = null output,
                    @LOCATION nvarchar(250) = null output,
                    @DEPARTMENT nvarchar(250) = null output,
                    @EVENT nvarchar(50) = null output,
                    @SITE nvarchar(1024) = null output,
                    @SCHEDULESUMMARY nvarchar(1024) = null output,
                    @SCHEDULESUMMARYCOLLECTION xml = null output,
                    @MORETHANFIVE bit = null output

                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    select     @DATALOADED = 1,
                        @STARTDATE = coalesce([JOBOCCURRENCE].[STARTDATE], [JOBOCCURRENCE].[RECURRENCESTARTDATE]),
                        @ENDDATE = coalesce([JOBOCCURRENCE].[ENDDATE], [JOBOCCURRENCE].[RECURRENCEENDDATE]),
                        @STARTMONTHDAY = JOBOCCURRENCE.STARTMONTHDAY,
                        @ENDMONTHDAY = JOBOCCURRENCE.ENDMONTHDAY,
                        @STARTTIME = JOBOCCURRENCE.STARTTIME,
                        @ENDTIME = JOBOCCURRENCE.ENDTIME,
                        @TYPECODE = JOBOCCURRENCE.TYPECODE,
                        @TYPE = JOBOCCURRENCE.TYPE,
                        @DAYOFWEEK = JOBOCCURRENCE.DAYOFWEEK,
                        @VOLUNTEERSNEEDED = JOBOCCURRENCE.VOLUNTEERSNEEDED,
                        @COMMENTS = JOBOCCURRENCE.COMMENTS ,
                        @LOCATION = LOCATION.DESCRIPTION,
                        @DEPARTMENT = DEPARTMENT.DESCRIPTION,
                        @EVENT = EVENT.NAME,
                        @SITE = COALESCE((select [NAME] from dbo.SITE where ID=JOBOCCURRENCE.SITEID), '')

                from dbo.JOBOCCURRENCE
                left outer join dbo.VOLUNTEERLOCATIONCODE LOCATION
                    on JOBOCCURRENCE.LOCATIONCODEID = LOCATION.ID
                left outer join dbo.DEPARTMENTCODE DEPARTMENT
                    on JOBOCCURRENCE.DEPARTMENTCODEID = DEPARTMENT.ID
                left outer join dbo.EVENT
                    on JOBOCCURRENCE.EVENTID = EVENT.ID
                where JOBOCCURRENCE.ID = @ID;

                -- build schedule summary
                set @MORETHANFIVE= 0;
                declare @t table (
                    ASSIGNMENTDATE datetime,
                    OPENINGS int
                );

                set nocount off;

                insert into @t
                    select top 6
                        SCHEDULE.ASSIGNMENTDATE,
                        case when SCHEDULE.OPENINGS < 0 then 0 else SCHEDULE.OPENINGS end
                    from dbo.UFN_JOBOCCURRENCE_SCHEDULE(dbo.UFN_DATE_GETEARLIESTTIME(getdate()), dateadd(yy, 6, getdate()), @ID, null) SCHEDULE;

                if @@ROWCOUNT = 6
                    begin
                        set @MORETHANFIVE = 1;

                        delete from @t
                        where ASSIGNMENTDATE = (select max(ASSIGNMENTDATE) from @t);
                    end

                select
                    @SCHEDULESUMMARY = replace(dbo.UDA_BUILDLIST(convert(nvarchar(10), ASSIGNMENTDATE, 101 ) + '    Needed: ' + cast(OPENINGS  as nvarchar)), '; ',  char(10))
                from @t;

                set @SCHEDULESUMMARYCOLLECTION = (select top 6 ASSIGNMENTDATE, OPENINGS
                 FROM @t
                 for xml raw('ITEM'),type,elements,root('SCHEDULESUMMARYCOLLECTION'),BINARY BASE64)

                if @MORETHANFIVE = 1
                    set @SCHEDULESUMMARY = @SCHEDULESUMMARY + char(10) + '...'

                return 0;