USP_DATAFORMTEMPLATE_EDIT_ITINERARYITEMSTAFFRESOURCESCHEDULE
The save procedure used by the edit dataform template "ItineraryItemStaffResourceSchedule Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ASSIGNEDSTAFF | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ITINERARYITEMSTAFFRESOURCESCHEDULE(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ASSIGNEDSTAFF xml
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
declare @STATUSCODE tinyint;
select @STATUSCODE = SALESORDER.STATUSCODE
from dbo.SALESORDER
inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = SALESORDER.ID
inner join dbo.ITINERARYITEM on ITINERARYITEM.ITINERARYID = ITINERARY.ID
where ITINERARYITEM.ID = @ID
if @STATUSCODE in (1, 5)
raiserror('BBERR_INVALIDSTATUS', 13, 1);
declare @ASSIGNEDSTAFFTABLE table
(
FILLEDBYCODE tinyint,
JOBOCCURRENCEID uniqueidentifier,
VOLUNTEERASSIGNMENTID uniqueidentifier,
VOLUNTEERID uniqueidentifier,
DATE datetime,
ITINERARYITEMSTAFFRESOURCEID uniqueidentifier,
ITINERARYITEMSTAFFRESOURCEASSIGNMENTID uniqueidentifier,
CONSTITUENTID uniqueidentifier
);
insert into @ASSIGNEDSTAFFTABLE
(
FILLEDBYCODE,
JOBOCCURRENCEID,
VOLUNTEERASSIGNMENTID,
VOLUNTEERID,
DATE,
ITINERARYITEMSTAFFRESOURCEID,
ITINERARYITEMSTAFFRESOURCEASSIGNMENTID,
CONSTITUENTID
)
select
T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE',
T.c.value('(JOBOCCURRENCEID)[1]','uniqueidentifier') AS 'JOBOCCURRENCEID',
T.c.value('(VOLUNTEERASSIGNMENTID)[1]','uniqueidentifier') AS 'VOLUNTEERASSIGNMENTID',
T.c.value('(VOLUNTEERID)[1]','uniqueidentifier') AS 'VOLUNTEERID',
T.c.value('(DATE)[1]','datetime') AS 'DATE',
T.c.value('(ITINERARYITEMSTAFFRESOURCEID)[1]','uniqueidentifier') AS 'ITINERARYITEMSTAFFRESOURCEID',
T.c.value('(ITINERARYITEMSTAFFRESOURCEASSIGNMENTID)[1]','uniqueidentifier') AS 'ITINERARYITEMSTAFFRESOURCEASSIGNMENTID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID'
from @ASSIGNEDSTAFF.nodes('/ASSIGNEDSTAFF/ITEM') T(c);
update @ASSIGNEDSTAFFTABLE set
JOBOCCURRENCEID = case when JOBOCCURRENCEID = '00000000-0000-0000-0000-000000000000'then null else JOBOCCURRENCEID end,
VOLUNTEERASSIGNMENTID = case when VOLUNTEERASSIGNMENTID = '00000000-0000-0000-0000-000000000000' then null else VOLUNTEERASSIGNMENTID end,
VOLUNTEERID = case when VOLUNTEERID = '00000000-0000-0000-0000-000000000000' then null else VOLUNTEERID end,
ITINERARYITEMSTAFFRESOURCEASSIGNMENTID = case when ITINERARYITEMSTAFFRESOURCEASSIGNMENTID = '00000000-0000-0000-0000-000000000000' then null else ITINERARYITEMSTAFFRESOURCEASSIGNMENTID end,
CONSTITUENTID = case when CONSTITUENTID = '00000000-0000-0000-0000-000000000000' then null else CONSTITUENTID end;
/* First delete any records that no longer have a constituent associated with them */
/* Need to setup the context cache first so that the delete audit will work */
declare @contextCache varbinary(128);
declare @e int;
set @contextCache = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
/* First delete from the volunteer assignments */
delete from dbo.VOLUNTEERASSIGNMENT
where
exists
(
select ASSIGNEDSTAFFTABLE.VOLUNTEERASSIGNMENTID
from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
where
VOLUNTEERASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.VOLUNTEERASSIGNMENTID and
ASSIGNEDSTAFFTABLE.VOLUNTEERID is null and
ASSIGNEDSTAFFTABLE.FILLEDBYCODE = 0
);
/* Second delete the itinerary item staff resource assignments */
delete from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
where
exists
(
select ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEASSIGNMENTID
from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
where
ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEASSIGNMENTID and
ASSIGNEDSTAFFTABLE.CONSTITUENTID is null and
ASSIGNEDSTAFFTABLE.FILLEDBYCODE != 0
);
select @e=@@error;
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if @e <> 0
return 2;
/* make sure that we create volunteer and volunteer records for those that need them */
exec dbo.USP_STAFFRESOURCE_PROCESSVOLUNTEERS @ASSIGNEDSTAFF, @CHANGEAGENTID;
/* Update any existing records to have the correct people assigned. */
/* First volunteers */
update dbo.VOLUNTEERASSIGNMENT set
VOLUNTEERID = ASSIGNEDSTAFFTABLE.VOLUNTEERID,
DATE = ASSIGNEDSTAFFTABLE.DATE,
JOBOCCURRENCEID = ASSIGNEDSTAFFTABLE.JOBOCCURRENCEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.VOLUNTEERASSIGNMENT
inner join @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE on
VOLUNTEERASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.VOLUNTEERASSIGNMENTID
where
ASSIGNEDSTAFFTABLE.VOLUNTEERID is not null and
ASSIGNEDSTAFFTABLE.FILLEDBYCODE = 0;
/* Second staff and board */
update dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT set
CONSTITUENTID = ASSIGNEDSTAFFTABLE.CONSTITUENTID,
ITINERARYITEMSTAFFRESOURCEID = ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
inner join @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE on
ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEASSIGNMENTID
where
ASSIGNEDSTAFFTABLE.CONSTITUENTID is not null and
ASSIGNEDSTAFFTABLE.FILLEDBYCODE != 0;
/* Finally add any new records for volunteers, staff, or board. */
/* First add the volunteers */
insert into dbo.VOLUNTEERASSIGNMENT
(
ID,
VOLUNTEERID,
JOBOCCURRENCEID,
DATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
coalesce(ASSIGNEDSTAFFTABLE.VOLUNTEERASSIGNMENTID, newid()),
ASSIGNEDSTAFFTABLE.VOLUNTEERID,
ASSIGNEDSTAFFTABLE.JOBOCCURRENCEID,
ASSIGNEDSTAFFTABLE.DATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
where
ASSIGNEDSTAFFTABLE.VOLUNTEERID is not null and
ASSIGNEDSTAFFTABLE.FILLEDBYCODE = 0 and
(
ASSIGNEDSTAFFTABLE.VOLUNTEERASSIGNMENTID is null or
not exists
(
select VOLUNTEERASSIGNMENT.ID
from dbo.VOLUNTEERASSIGNMENT
where VOLUNTEERASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.VOLUNTEERASSIGNMENTID
)
);
/* Second add the staff/board */
insert into dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
(
ID,
CONSTITUENTID,
ITINERARYITEMSTAFFRESOURCEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
coalesce(ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEASSIGNMENTID, newid()),
ASSIGNEDSTAFFTABLE.CONSTITUENTID,
ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
where
ASSIGNEDSTAFFTABLE.CONSTITUENTID is not null and
ASSIGNEDSTAFFTABLE.FILLEDBYCODE != 0 and
(
ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEASSIGNMENTID is null or
not exists
(
select ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID
from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
where ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.ITINERARYITEMSTAFFRESOURCEASSIGNMENTID
)
);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;