USP_DATAFORMTEMPLATE_EDIT_CYCLE
The save procedure used by the edit dataform template "Cycle 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. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @CYCLEDAYS | xml | IN | Cycle day |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CYCLE (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(255),
@CYCLEDAYS xml
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
-- handle updating the data
update dbo.CYCLE set
CYCLE.NAME = @NAME,
CYCLE.DESCRIPTION = @DESCRIPTION,
CYCLE.CHANGEDBYID = @CHANGEAGENTID,
CYCLE.DATECHANGED = @CURRENTDATE
where CYCLE.ID = @ID
declare @contextCache varbinary(128);
/* cache current context information */
set @contextCache = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID
delete
PATTERNBLOCKMEETING
from
PATTERNBLOCKMEETING
inner join
TIMETABLEDAY on TIMETABLEDAY.ID = PATTERNBLOCKMEETING.TIMETABLEDAYID
inner join
TIMETABLE on TIMETABLE.ID = TIMETABLEDAY.TIMETABLEID
where
TIMETABLE.CYCLEID = @ID
and TIMETABLEDAY.CYCLEDAYID not in (select ID from dbo.UFN_CYCLEDAY_GETCYCLEDAY_FROMITEMLISTXML(@CYCLEDAYS))
--remove empty pattern blocks
exec dbo.USP_PATTERNBLOCK_UNUSED_DELETE @CHANGEAGENTID
delete
SCHEDULEDATE
from
SCHEDULEDATE
inner join TIMETABLEDAY
on TIMETABLEDAY.ID = SCHEDULEDATE.TIMETABLEDAYID
inner join TIMETABLE
on TIMETABLE.ID = TIMETABLEDAY.TIMETABLEID
where
TIMETABLE.CYCLEID = @ID
and TIMETABLEDAY.CYCLEDAYID not in (select ID from dbo.UFN_CYCLEDAY_GETCYCLEDAY_FROMITEMLISTXML(@CYCLEDAYS))
delete
TIMETABLEDAY
from
TIMETABLEDAY
inner join
TIMETABLE on TIMETABLE.ID = TIMETABLEDAY.TIMETABLEID
where
TIMETABLE.CYCLEID = @ID
and TIMETABLEDAY.CYCLEDAYID not in (select ID from dbo.UFN_CYCLEDAY_GETCYCLEDAY_FROMITEMLISTXML(@CYCLEDAYS))
/* reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @contextCache
exec dbo.USP_CYCLEDAY_GETCYCLEDAY_UPDATEFROMXML @ID, @CYCLEDAYS, @CHANGEAGENTID;
if not exists(select ID from dbo.CYCLEDAY where CYCLEID = @ID)
raiserror('BBERR_CYCLE_ATLEASTONECYCLEDAY', 13, 1)
insert into dbo.TIMETABLEDAY
(ID, TIMETABLEID, CYCLEDAYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
newid(),
TIMETABLE.ID,
CYCLEDAY.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
CYCLEDAY
inner join CYCLE
on CYCLE.ID = CYCLEDAY.CYCLEID
inner join TIMETABLE
on TIMETABLE.CYCLEID = CYCLE.ID
where
CYCLEDAY.CYCLEID = @ID
and not exists (select TIMETABLEDAY.ID
from TIMETABLEDAY
inner join TIMETABLE on TIMETABLE.CYCLEID = @ID
where TIMETABLEDAY.CYCLEDAYID = CYCLEDAY.ID)
--recreate calendar entries
declare @TempTbl table (
[ROWID] int identity(1,1),
[TERMID] uniqueidentifier)
insert into @TempTbl (TERMID)
select
TERM.[ID]
from
TERM
inner join SESSION
on SESSION.id = TERM.SESSIONID
inner join TIMETABLE
on TIMETABLE.id = SESSION.TIMETABLEID
inner join CYCLE
on CYCLE.ID = TIMETABLE.CYCLEID
where CYCLE.ID = @ID
declare @rowId int
declare @maxRowId int
declare @TERMID uniqueidentifier = null
select
@rowId = min(ROWID),
@MaxRowId = max(ROWID)
from @TempTbl
while @RowId <= @MaxRowId
begin
select
@TERMID = TERMID
from
@TempTbl
where
ROWID = @RowId
exec dbo.USP_SCHEDULEDATE_ADJUSTENTRIES @TERMID, null, null, null, @CHANGEAGENTID
set @RowId = @RowId + 1
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;