USP_DATAFORMTEMPLATE_VIEW_EVENTJOBOCCURRENCEPAGEDATA

The load procedure used by the view dataform template "Event 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
@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
@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_EVENTJOBOCCURRENCEPAGEDATA
(
    @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,
    @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,
    @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,
        @TYPECODE = JOBOCCURRENCE.TYPECODE,
        @TYPE = JOBOCCURRENCE.TYPE,
        @DAYOFWEEK = JOBOCCURRENCE.DAYOFWEEK,
        @VOLUNTEERSNEEDED = JOBOCCURRENCE.VOLUNTEERSNEEDED,
        @COMMENTS = JOBOCCURRENCE.COMMENTS ,
        @LOCATION = LOCATION.DESCRIPTION,
        @DEPARTMENT = DEPARTMENT.DESCRIPTION,
        @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
    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,
            SCHEDULE.OPENINGS
        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;