USP_DATAFORMTEMPLATE_ADD_COPYPREVIOUS
The save procedure used by the add dataform template "Copy previous scheduling information".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ACADEMICYEARID | uniqueidentifier | IN | Academic year |
| @SESSIONID | uniqueidentifier | IN | Session |
| @COPY_TEACHERS | bit | IN | Teachers |
| @COPY_MEETINGS | bit | IN | Meetings |
| @DELETE_CLASSES | bit | IN | Delete any existing classes |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_COPYPREVIOUS
(
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ACADEMICYEARID uniqueidentifier = null,
@SESSIONID uniqueidentifier = null,
@COPY_TEACHERS bit = null,
@COPY_MEETINGS bit = null,
@DELETE_CLASSES bit = null
)
AS
set NOCOUNT on;
if @CHANGEAGENTID is null
exec DBO.USP_CHANGEAGENT_GETORCREATECHANGEAGENT
@CHANGEAGENTID output
declare @CURRENTDATE datetime
declare @SESSIONID_COPYFROM uniqueidentifier
declare @SESSIONID_COPYTO uniqueidentifier
declare @ACADEMICYEARID_COPYTO uniqueidentifier
declare @ACADEMICYEARID_COPYFROM uniqueidentifier
declare @TT1 uniqueidentifier
declare @TT2 uniqueidentifier
declare @SCHOOLID uniqueidentifier
declare @STUDENTCOUNT int
set @CURRENTDATE = GETDATE()
set @SESSIONID_COPYFROM = @SESSIONID
set @ACADEMICYEARID_COPYFROM = @ACADEMICYEARID
select @SCHOOLID = AY.SCHOOLID
from ACADEMICYEAR AY
where AY.ID = @ACADEMICYEARID_COPYFROM
select
@SESSIONID_COPYTO = AU.SESSIONID,
@TT1 = SS1.TIMETABLEID,
@TT2 = SS2.TIMETABLEID,
@ACADEMICYEARID_COPYTO = SS1.ACADEMICYEARID
from APPUSERSESSION AU
inner join SESSION SS1
on SS1.ID = AU.SESSIONID
left join SESSION SS2
on SS2.ID = @SESSIONID_COPYFROM
where AU.ID = @CURRENTAPPUSERID
if @TT1 <> @TT2
begin
raiserror ('BBERR_SESSION_HAS_DIFFERENT_TIMETABLE',13,1);
return 1;
end
declare @CLASSCOUNT as int
exec USP_DATAFORMTEMPLATE_VIEW_COPYPREVIOUS_CLASSCOUNT
@SESSIONID_COPYFROM ,
null ,
@CLASSCOUNT output
if @CLASSCOUNT = 0
begin
raiserror ('BBERR_SESSION_HAS_NO_CLASSES',13,1);
return 1;
end ;
exec USP_DATAFORMTEMPLATE_VIEW_COPYPREVIOUS_CLASSCOUNT
@SESSIONID_COPYTO , null , @CLASSCOUNT output
if @CLASSCOUNT > 0 and @DELETE_CLASSES = 0
begin
raiserror ('BBERR_TO_SESSION_HAS_CLASSES',13,1);
return 1;
end ;
select
@STUDENTCOUNT = COUNT(SMG.ID)
from COURSE CR
inner join CLASS CL on CL.COURSEID = CR.ID
inner join CLASSMEETINGGROUP CMG on CMG.CLASSID = CL.ID
inner join STUDENTCLASSMEETINGGROUP SMG on SMG.CLASSMEETINGGROUPID = CMG.ID
inner join TERM TM on (CMG.STARTDATE = TM.STARTDATE) and (CMG.ENDDATE = TM.ENDDATE)
where CR.SCHOOLID = @SCHOOLID and TM.SESSIONID = @SESSIONID_COPYTO
if @STUDENTCOUNT > 0
begin
raiserror ('BBERR_TO_SESSION_HAS_ENROLLED_STUDENTS',13,1);
return 1 ;
end ;
declare @TERMS_MATCH bit
exec USP_SESSION_TERM_COMPARE
@SESSIONID_COPYTO ,
@SESSIONID_COPYFROM ,
@TERMS_MATCH output
if @TERMS_MATCH = 0
begin
raiserror ('BBERR_SESSION_TERMS_NO_MATCH',13,1);
return 1 ;
end ;
if @DELETE_CLASSES = 1
begin
delete from dbo.CLASS
from dbo.CLASS
inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
inner join dbo.UFN_SESSION_GETDATES(@SESSIONID_COPYTO) as SESSION_DATES
on SESSION_DATES.STARTDATE <= CLASS.STARTDATE
and SESSION_DATES.ENDDATE >= CLASS.ENDDATE
where COURSE.SCHOOLID = @SCHOOLID
end ;
declare @CREATEMEETINGS table
(
NEW_CLASSID uniqueidentifier,
ROWNUMBER int
)
declare @CLASSTOCOPY table
(
CLASSID uniqueidentifier,
CLASSMEETINGGROUPID uniqueidentifier,
ROWNUMBER int,
COPYFROM_SESSIONID uniqueidentifier,
COPYFROM_TERMID uniqueidentifier,
COPYFROM_TERMNAMECODEID uniqueidentifier,
SECTION nvarchar(20),
COURSEID uniqueidentifier,
PATTERNBLOCKID uniqueidentifier,
FACULTYID uniqueidentifier,
COPIED bit
)
insert into @CLASSTOCOPY
select
CL.ID CLASSID,
CMG.ID CLASSMEETINGGROUPID,
ROW_NUMBER() over(order by SS.ID asc) ROWNUMBER,
SS.ID COPYFROM_SESSIONID,
TM.ID COPYFROM_TERMID,
TM.TERMNAMECODEID COPYFROM_TERMNAMECODEID,
CL.SECTION SECTION,
CL.COURSEID COURSEID,
CMG.PATTERNBLOCKID,
FC.FACULTYID FACULTYID,
0
from COURSE CR
inner join COURSERESTRICTION CRT
on CRT.COURSEID = CR.ID
inner join CLASS CL
on CL.COURSEID = CR.ID
inner join CLASSMEETINGGROUP CMG
on CMG.CLASSID = CL.ID
inner join TERM TM
on (TM.STARTDATE = CMG.STARTDATE AND CL.STARTDATE = TM.STARTDATE)
inner join UFN_SESSION_GETDATES (@SESSIONID_COPYFROM) SSD
ON (CL.STARTDATE >= SSD.STARTDATE AND CL.ENDDATE <= SSD.ENDDATE )
inner join SESSION SS
on SS.ID = TM.SESSIONID
inner join ACADEMICYEAR AY
on AY.ID = SS.ACADEMICYEARID
left join FACULTYCLASSMEETINGGROUP FCMG
on FCMG.CLASSMEETINGGROUPID = CMG.ID
left join FACULTYCOURSE FC
on FCMG.FACULTYCOURSEID = FC.ID
where (CRT.STARTDATE <= CL.STARTDATE
and CRT.ENDDATE >= CL.ENDDATE)
and SS.ID = @SESSIONID_COPYFROM
and CR.SCHOOLID = AY.SCHOOLID
---LOOP THROUGH THE @CLASSTOCOPY
---RUN THROUGH DIFFERENT VALIDATIONS AND REMOVE OFFENDING ROWS
---WHAT IS LEFT, USE USP_DATAFORMTEMPLATE_ADD_CLASS TO ADD THE CLASS ONLY
declare @MAXROWID int
declare @CURRENT_CLASSID uniqueidentifier
declare @ROWNUMBER int
declare @COPYFROM_TERMNAMECODEID uniqueidentifier
declare @COPYTO_TERMID uniqueidentifier
declare @SECTION nvarchar(20)
declare @COURSEID uniqueidentifier
declare @PATTERNBLOCKID uniqueidentifier
declare @FACULTYID uniqueidentifier
declare @TEMP1 int
declare @TEMP2 int
declare @SESSION_STARTDATE date
declare @SESSION_ENDDATE date
declare @COURSENAME nvarchar(100)
declare @LENGTHINTERMS int
declare @START_DATETIME datetime
declare @END_DATETIME datetime
declare @COURSE_SKIPPED bit
declare @NEW_CLASSID uniqueidentifier
declare @NEW_CLASSMEETINGGROUPID uniqueidentifier
declare @OLD_CLASSMEETINGGROUPID uniqueidentifier
select
@ROWNUMBER = min(ROWNUMBER),
@MAXROWID = max(ROWNUMBER)
from @CLASSTOCOPY
declare @COURSES_SKIPPED table
(
COURSEID uniqueidentifier,
TERMNAMECODEID uniqueidentifier,
SKIPPED bit
)
select @SESSION_STARTDATE = STARTDATE,
@SESSION_ENDDATE = ENDDATE
from UFN_SESSION_GETDATES (@SESSIONID_COPYTO)
begin try
while @ROWNUMBER <= @MAXROWID
begin
select
@CURRENT_CLASSID = CP.CLASSID,
@OLD_CLASSMEETINGGROUPID = CP.CLASSMEETINGGROUPID,
@COPYFROM_TERMNAMECODEID = CP.COPYFROM_TERMNAMECODEID,
@PATTERNBLOCKID = CP.PATTERNBLOCKID,
@COURSEID = CP.COURSEID,
@SECTION = CP.SECTION,
@FACULTYID = CP.FACULTYID,
@COURSE_SKIPPED = 0
from @CLASSTOCOPY CP
where CP.ROWNUMBER = @ROWNUMBER
if not exists (select *
from @COURSES_SKIPPED IC
where IC.COURSEID = @COURSEID and IC.TERMNAMECODEID = @COPYFROM_TERMNAMECODEID)
begin
insert into @COURSES_SKIPPED values (@COURSEID,@COPYFROM_TERMNAMECODEID, 0)
end ;
select
@COURSE_SKIPPED = IC.SKIPPED
from @COURSES_SKIPPED IC
where IC.COURSEID = @COURSEID and IC.TERMNAMECODEID = @COPYFROM_TERMNAMECODEID
if @COURSE_SKIPPED = 1
begin
goto NEXT_ROW
end;
set @COURSE_SKIPPED = 1
---THIS VALIDATION CHECKS if THE COURSE BEING COPIED TO HAS THE SAME
-- LENGTH IN TERMS AS ONE BEING COPIED FROM.
select
@TEMP1 = LENGTHINTERMS
from COURSERESTRICTION CRC
inner join COURSE CR
on CR.ID = CRC.COURSEID
inner join ACADEMICYEAR AY
on AY.ID = @ACADEMICYEARID_COPYFROM
where CR.ID = @COURSEID
and CRC.STARTDATE >= AY.STARTDATE
and CRC.ENDDATE <= AY.ENDDATE
select
@TEMP2 = LENGTHINTERMS
from COURSERESTRICTION CRC
inner join COURSE CR
on CR.ID = CRC.COURSEID
inner join ACADEMICYEAR AY
on AY.ID = @ACADEMICYEARID_COPYTO
where CR.ID = @COURSEID
and CRC.STARTDATE >= AY.STARTDATE
and CRC.ENDDATE <= AY.ENDDATE
if @TEMP1 <> @TEMP2
begin
goto NEXT_ROW --TERMS DIFFERENT, HOP TO THE NEXT ROW
end
--CHECKING if COURSE RESTRICTIONS EXISTS...
select
@COURSENAME = COURSE.NAME,
@LENGTHINTERMS = COURSERESTRICTION.LENGTHINTERMS
from DBO.COURSE
inner join DBO.V_COURSERESTRICTION as COURSERESTRICTION
on COURSE.ID = COURSERESTRICTION.COURSEID
where COURSE.ID = @COURSEID
and COURSERESTRICTION.STARTDATE = @SESSION_STARTDATE
and COURSERESTRICTION.ENDDATE = @SESSION_ENDDATE
if @COURSENAME is null
begin
goto NEXT_ROW
end ;
--CHECK if START TERMS OF BOTH SESSIONS FOR THE COURSE MATCH, if IT DOESNT MATCH GO TO THE NEXT COURSE
exec USP_COURSERESTRICTION_TERM_COMPARE
@SESSIONID_COPYTO ,
@SESSIONID_COPYFROM ,
@COURSEID ,
@COPYFROM_TERMNAMECODEID,
@TERMS_MATCH output
if @TERMS_MATCH = 0
begin
goto NEXT_ROW
end
select
@COPYTO_TERMID = UFT.ID
from UFN_SESSION_GETTERMS (@SESSIONID_COPYTO ) UFT
where UFT.TERMNAMECODEID = @COPYFROM_TERMNAMECODEID
select
@START_DATETIME = STARTDATE
from DBO.TERM
where ID = @COPYTO_TERMID
select
@END_DATETIME = T.ENDDATE
from (select
ENDDATE,
ROW_NUMBER()
over(order by STARTDATE asc) as TERMNUMBER
from DBO.TERM
where SESSIONID = @SESSIONID_COPYTO
and STARTDATE >= @START_DATETIME) T
where T.TERMNUMBER = @LENGTHINTERMS
--INSERT THE ROW INTO CLASS AND CLASSMEETINGGROUP
--GET THE NEW CLASS ID
set @NEW_CLASSID = NEWID()
insert into DBO.CLASS
(ID,
COURSEID,
NAME,
SECTION,
CLASSSIZEMINIMUM,
CLASSSIZETARGET,
CLASSSIZEMAXIMUM,
CLASSESPERTERMMINIMUM,
CLASSESPERTERMTARGET,
CLASSESPERTERMMAXIMUM,
STARTDATE,
ENDDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
@NEW_CLASSID,
@COURSEID,
@COURSENAME,
@SECTION,
CLASSSIZEMINIMUM,
CLASSSIZETARGET,
CLASSSIZEMAXIMUM,
CLASSESPERTERMMINIMUM,
CLASSESPERTERMTARGET,
CLASSESPERTERMMAXIMUM,
@START_DATETIME,
@END_DATETIME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from DBO.COURSE
where COURSE.ID = @COURSEID
--Do we need to copy PATTERNBLOCKID ?
--1. If Year1 has a certain Pattern associated with the restriction,
-- Meetings in Year2 will get the same Pattern.
--2. If Year1 has no pattern associated with the restriction,
-- Meetings in Year2 will have no Pattern information.
--3. If Year 1 has a certain Pattern associated with the restriction
-- but Year 2 has a different pattern or no pattern,
-- Meetings in Year 2 will not have pattern association.
--@PATTERNBLOCKID has the "From" Year ID
--Get the "To" year Patternblock ID
declare @IsValidPattern int
select @IsValidPattern = COUNT(PB.PATTERNID)
from COURSERESTRICTION CRT
inner join dbo.UFN_SESSION_GETDATES(@SESSIONID_COPYTO) as SESSION_DATES
ON CRT.STARTDATE = SESSION_DATES.STARTDATE and CRT.ENDDATE = SESSION_DATES.ENDDATE
inner join dbo.PATTERNBLOCK PB on PB.PATTERNID = CRT.PATTERNID
where CRT.COURSEID = @COURSEID AND PB.ID = @PATTERNBLOCKID
if @IsValidPattern = 0
begin
SET @PATTERNBLOCKID = NULL
end
insert into DBO.CLASSMEETINGGROUP
(ID,
CLASSID,
STARTDATE,
ENDDATE,
PATTERNBLOCKID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select top ( @LENGTHINTERMS )
NEWID(),
@NEW_CLASSID,
STARTDATE,
ENDDATE,
@PATTERNBLOCKID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from DBO.TERM
where SESSIONID = @SESSIONID_COPYTO
and STARTDATE >= @START_DATETIME
order by TERM.STARTDATE
if @COPY_MEETINGS = 1
begin
insert into CLASSMEETINGTEMPLATE
(ID,
CLASSMEETINGGROUPID,
CYCLEDAYID,
STARTTIME,
ENDTIME,
TIMETABLEDAYPERIODID,
LENGTHINPERIODS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
NEWID(),
CMG.ID,
CMT.CYCLEDAYID,
CMT.STARTTIME,
CMT.ENDTIME,
CMT.TIMETABLEDAYPERIODID,
CMT.LENGTHINPERIODS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from CLASSMEETINGTEMPLATE CMT
inner join CLASSMEETINGGROUP CMG on CMG.CLASSID = @NEW_CLASSID
where CMT.CLASSMEETINGGROUPID = @OLD_CLASSMEETINGGROUPID
end
--LET'S ADD FACULTY NOW...
if (@FACULTYID is not null)
and (@COPY_TEACHERS = 1)
begin
declare @FACULTYCOURSEID uniqueidentifier
select
@FACULTYCOURSEID = ID
from DBO.FACULTYCOURSE
where FACULTYID = @FACULTYID
and COURSEID = @COURSEID
if @FACULTYCOURSEID is null
begin
set @FACULTYCOURSEID = NEWID()
insert into DBO.FACULTYCOURSE
(ID,
FACULTYID,
COURSEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
values (@FACULTYCOURSEID,
@FACULTYID,
@COURSEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE)
end
insert into DBO.FACULTYCLASSMEETINGGROUP
(ID,
FACULTYCOURSEID,
CLASSMEETINGGROUPID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select
NEWID(),
@FACULTYCOURSEID,
CLASSMEETINGGROUP.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from DBO.CLASSMEETINGGROUP
where CLASSID = @NEW_CLASSID
end
set @COURSE_SKIPPED = 0
next_row:
if @COURSE_SKIPPED = 1
begin
update @COURSES_SKIPPED
set SKIPPED = 1
where COURSEID = @COURSEID and TERMNAMECODEID = @COPYFROM_TERMNAMECODEID
end
else
begin
update @CLASSTOCOPY
set COPIED = 1
where ROWNUMBER = @ROWNUMBER
if @COPY_MEETINGS = 1
begin
set @TEMP1 = 0
select @TEMP1 = count(* )
from @CLASSTOCOPY
where COPIED = 1
insert into @CREATEMEETINGS (NEW_CLASSID,ROWNUMBER)
values (@NEW_CLASSID, @TEMP1)
end
end;
set @ROWNUMBER = @ROWNUMBER + 1
end ---WHILE
--Now, for all the added classes let's add the meetings...
set @MAXROWID = -1
select
@ROWNUMBER = min(ROWNUMBER),
@MAXROWID = max(ROWNUMBER)
from @CREATEMEETINGS
while @ROWNUMBER <= @MAXROWID
begin
select
@NEW_CLASSID = CM.NEW_CLASSID
from @CREATEMEETINGS CM
where CM.ROWNUMBER = @ROWNUMBER
exec USP_CLASSMEETING_CREATEFROMTEMPLATES
@NEW_CLASSID ,
@CHANGEAGENTID
set @ROWNUMBER = @ROWNUMBER + 1
end -- 2nd WHILE
end try
begin catch
declare @sMsg nvarchar(255)
SELECT @sMsg = ERROR_MESSAGE() ;
begin
raiserror (@sMsg,13,1);
return 1;
end ;
end catch
return 0