USP_DATAFORMTEMPLATE_ADD_SCHEDULEPROGRAMEVENTS

The save procedure used by the add dataform template "Schedule Program Events Add Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@PROGRAMID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CAPACITY	int	IN	Capacity
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@LOCATIONS	xml	IN
@RESOURCES	xml	IN	Supplies/Equipment resources
@STAFFRESOURCES	xml	IN	Staffing resources
@EVENTTIMES	xml	IN	Event times
@EVENTS	xml	IN	Event dates
Definition

Copy

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SCHEDULEPROGRAMEVENTS
                    (
                        @ID uniqueidentifier = null output,
                        @PROGRAMID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CAPACITY integer = null,
                        @NAME nvarchar(100) = '',
                        @DESCRIPTION nvarchar(255) = '',
                        @LOCATIONS xml = null,
                        @RESOURCES xml = null,
                        @STAFFRESOURCES xml = null,
                        @EVENTTIMES xml = null,
                        @EVENTS xml = null
                    )
                    as

                    set nocount on;

                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                    declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate();

                    begin try

                        if @EVENTS is null
                            raiserror('BBERR_INVALIDEVENTS', 13, 1);

                        declare @SCHEDULEID uniqueidentifier;
                        set @SCHEDULEID = newid();

                        set @ID = @PROGRAMID;

                        declare @ISPREREGISTERED bit;
                        declare @ISDAILYADMISSION bit;

                        select @ISPREREGISTERED = ISPREREGISTERED,
                            @ISDAILYADMISSION = ISDAILYADMISSION
                        from dbo.PROGRAM
                        where ID = @PROGRAMID;

                        if @ISDAILYADMISSION = 1
                            raiserror('BBERR_INVALIDPROGRAMTYPE', 13, 1);


                        declare @DUPLICATELOCATIONSEXIST bit = 0;

                        if not exists (select 1
                                        from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(locations))
                            raiserror('BBERR_INVALIDLOCATIONS', 13, 1);

                        with LOCATIONS_CTE as 
                        (
                            select 
                                T.locations.value('(EVENTLOCATIONID)[1]', 'uniqueidentifier') as ID
                            from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(locations)
                        )
                        select
                            @DUPLICATELOCATIONSEXIST = 1
                        where
                            exists
                            (
                                select 1
                                from LOCATIONS_CTE
                                group by ID
                                having count(ID) > 1
                            )

                        if @DUPLICATELOCATIONSEXIST = 1
                            raiserror('BBERR_DUPLICATELOCATIONS', 13, 1);


                        if exists
                            (select count(VOLUNTEERTYPEID)
                                from
                                (
                                    select T.staffresources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier') VOLUNTEERTYPEID
                                    from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(STAFFRESOURCES)
                                ) Result
                                group by VOLUNTEERTYPEID
                                having count(*) > 1
                            )
                            raiserror('BBERR_DUPLICATESTAFFRESOURCES', 13, 1);

                        if exists
                            (select count(RESOURCEID)
                                from
                                (
                                    select T.resources.value('(RESOURCEID)[1]', 'uniqueidentifier') RESOURCEID
                                    from @RESOURCES.nodes('/RESOURCES/ITEM') T(RESOURCES)
                                ) Result
                                group by RESOURCEID
                                having count(*) > 1
                            )
                            raiserror('BBERR_DUPLICATERESOURCES', 13, 1);

                        declare @idoc int
                        exec sp_xml_preparedocument @idoc OUTPUT, @EVENTS
                        declare @EVENTSTABLE table
                        (
                            EVENTID uniqueidentifier,
                            STARTDATE date,
                            ENDDATE date,
                            STARTTIME udt_HOURMINUTE,
                            ENDTIME udt_HOURMINUTE,
                            ISCONFLICT bit,
                            CONFLICTID uniqueidentifier,
                            PROGRAMID uniqueidentifier
                        )
                        insert into @EVENTSTABLE
                        select
                            EVENTID,
                            STARTDATE,
                            ENDDATE,
                            STARTTIME,
                            ENDTIME,
                            0,
                            null,
                            @PROGRAMID
                        from openxml (@idoc,'/EVENTS/ITEM', 2)
                        with
                            (EVENTID uniqueidentifier,
                            STARTDATE date,
                            STARTTIME udt_HOURMINUTE,
                            ENDDATE date,
                            ENDTIME udt_HOURMINUTE,
                            ISCONFLICT bit)
                        exec sp_xml_removedocument @idoc

                        declare @CONFLICTSEXIST bit = 0;

                        -- Checking Conflicts                        
                        update @EVENTSTABLE set 
                            ET.ISCONFLICT = 1,
                            ET.CONFLICTID = newid(),
                            @CONFLICTSEXIST = 1
                        from @EVENTSTABLE ET
                        inner join dbo.UFN_CONFLICTCHECK_CONFLICTSEXISTFORMULTIPLETIMES(@EVENTS, @LOCATIONS, @RESOURCES, @STAFFRESOURCES) CC
                        on ET.EVENTID = CC.EVENTID

                        set @EVENTS =
                        (
                            select EVENTID as EVENTID,
                                STARTDATE as STARTDATE,
                                ENDDATE as ENDDATE,
                                STARTTIME as STARTTIME,
                                ENDTIME as ENDTIME,
                                ISCONFLICT as ISCONFLICT,
                                CONFLICTID as CONFLICTID,
                                @PROGRAMID as PROGRAMID
                            from @EVENTSTABLE
                            for xml raw ('ITEM'), type, elements, root('EVENTS'), BINARY BASE64                        
                        );


                        if @CONFLICTSEXIST = 1
                            exec dbo.USP_PROGRAMEVENTCONFLICT_INSERTCONFLICTS @EVENTS, @SCHEDULEID, @PROGRAMID, @NAME, @CAPACITY, @DESCRIPTION, @CHANGEAGENTID, @CURRENTDATE;

                        exec dbo.USP_PROGRAMEVENT_INSERT @EVENTS, @SCHEDULEID, @PROGRAMID, @NAME, @DESCRIPTION, @CAPACITY, @CHANGEAGENTID, @CURRENTDATE;

                        exec dbo.USP_PROGRAMEVENTLOCATION_INSERTLOCATIONS @EVENTS, @LOCATIONS, @PROGRAMID, @CHANGEAGENTID, @CURRENTDATE, @CONFLICTSEXIST;

                        if @ISPREREGISTERED = 1
                            exec dbo.USP_PROGRAMEVENT_COPYPROGRAMEVENTPREFERENCES @EVENTS, @PROGRAMID, @CONFLICTSEXIST, @CHANGEAGENTID, @CURRENTDATE;

                        if @RESOURCES is not null
                            exec dbo.USP_PROGRAMEVENTRESOURCES_INSERTRESOURCES @RESOURCES, @EVENTS, @PROGRAMID, @CHANGEAGENTID, @CURRENTDATE;

                        if @STAFFRESOURCES is not null
                            exec dbo.USP_PROGRAMEVENTSTAFFRESOURCES_INSERTSTAFFRESOURCES @STAFFRESOURCES, @EVENTS, @PROGRAMID, @CHANGEAGENTID, @CURRENTDATE;

                    end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch

                    return 0