USP_DATAFORMTEMPLATE_ADD_SETUPNEXTACADEMICYEAR_COPYNEXT
The save procedure used by the add dataform template "Set up next academic year".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CONTEXTID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @ACADEMICYEARID | uniqueidentifier | IN | Academic year |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @COPYORCREATE | int | IN | Copy from previous year |
| @COPYCOURSERESTRICTION | bit | IN | Course restrictions |
| @COPYCOURSEGRADING | bit | IN | Course grading information |
| @COPYBILLINGITEMCOSTHISTORY | bit | IN | Billing item cost history |
| @TOACADEMICYEARNAME | uniqueidentifier | IN | Name |
| @SCHOOLSTARTDATE | datetime | IN | School starts |
| @SCHOOLENDDATE | datetime | IN | School ends |
| @BILLINGSTARTDATE | datetime | IN | Billing start date |
| @BILLINGENDDATE | datetime | IN | Billing end date |
| @SESSIONTERMS | xml | IN | Terms |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SETUPNEXTACADEMICYEAR_COPYNEXT
(
@ID uniqueidentifier = null output,
@CONTEXTID uniqueidentifier,
@ACADEMICYEARID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@COPYORCREATE integer = 0,
@COPYCOURSERESTRICTION bit = 0,
@COPYCOURSEGRADING bit = 0,
@COPYBILLINGITEMCOSTHISTORY bit = 0,
@TOACADEMICYEARNAME uniqueidentifier = null,
@SCHOOLSTARTDATE datetime = null,
@SCHOOLENDDATE datetime = null,
@BILLINGSTARTDATE datetime = null,
@BILLINGENDDATE datetime = null,
@SESSIONTERMS xml = null
)
as
begin
set nocount on;
--Variables
declare @SCHOOLID uniqueidentifier
declare @CURRENTDATE datetime
declare @NEWSESSIONID uniqueidentifier
declare @OLDSESSIONID uniqueidentifier
set @CURRENTDATE = getdate()
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
select @SCHOOLID = AY.SCHOOLID
from ACADEMICYEAR AY
where AY.ID = @ACADEMICYEARID
-- 1. Check if the dates are not null...
if @COPYBILLINGITEMCOSTHISTORY = 1
begin
if @BILLINGSTARTDATE is null
begin
raiserror ('BBERR_TO_BILLING_STARTDATE_ISNULL',13,1);
return 1 ;
end
if @BILLINGENDDATE is null
begin
raiserror ('BBERR_TO_BILLING_ENDDATE_ISNULL',13,1);
return 1 ;
end
end
begin try
insert into dbo.ACADEMICYEAR
(ID, SCHOOLID, STARTDATE, ENDDATE, ACADEMICYEARNAMECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BILLINGSTARTDATE,BILLINGENDDATE)
values
(@ID, @SCHOOLID, @SCHOOLSTARTDATE, @SCHOOLENDDATE, @TOACADEMICYEARNAME, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BILLINGSTARTDATE,@BILLINGENDDATE)
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
if @COPYORCREATE = 0 ---Rest of the code is only if Copying information.
begin
--Let's add sessions...
declare @SESSION_COPY table
(
ROWNUMBER int identity(1,1),
OLDSESSIONID uniqueidentifier,
NEWSESSIONID uniqueidentifier,
ACADEMICYEARID uniqueidentifier
)
declare @TERM_DATES table
(
TERMNAMECODEID uniqueidentifier,
STARTDATE date,
ENDDATE date
)
declare @session_count tinyint
declare @session_row tinyint
declare @Term_RowID int
declare @Term_CountID int
declare @child_ROW int
declare @child_COUNT int
declare @SESSIONNAMECODEID uniqueidentifier
declare @TERMNAMECODEID uniqueidentifier
declare @TERMSTARTDATE date
declare @TERMENDDATE date
declare @TIMETABLEDAYID uniqueidentifier
declare @SESSION_oldSTARTDATE date
declare @SESSION_oldENDDATE date
declare @SESSION_newSTARTDATE date
declare @SESSION_newENDDATE date
delete from @SESSION_COPY
insert into @SESSION_COPY(OLDSESSIONID, NEWSESSIONID,ACADEMICYEARID)
select SS.ID,newid(),@ID
from SESSION SS
inner join ACADEMICYEAR AY
ON SS.ACADEMICYEARID = AY.ID
where AY.SCHOOLID = @SCHOOLID AND AY.ID = @ACADEMICYEARID
insert into DBO.SESSION(
ID,ACADEMICYEARID,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED,
SESSIONNAMECODEID,MARKINGCOLUMNSETID,TIMETABLEID,
SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY)
select
SSC.NEWSESSIONID,SSC.ACADEMICYEARID,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE,
SESSIONNAMECODEID,MARKINGCOLUMNSETID,TIMETABLEID,
SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY
from @SESSION_COPY SSC
inner join SESSION SS on SS.ID = SSC.OLDSESSIONID
select @session_row=MIN(ROWNUMBER),
@session_count = MAX(ROWNUMBER)
from @SESSION_COPY
while @session_row <= @session_count
begin
select @NEWSESSIONID = NEWSESSIONID,@OLDSESSIONID=OLDSESSIONID
from @SESSION_COPY
where ROWNUMBER=@session_row
select @SESSIONNAMECODEID = SESSIONNAMECODEID
from SESSION where ID = @OLDSESSIONID
begin -- Copy Term
insert into dbo.TERM(ID,SESSIONID,STARTDATE,ENDDATE,ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED,TIMETABLEDAYID,TERMNAMECODEID,WITHDRAWALDATE)
select
NEWID(),@NEWSESSIONID,
SessionTerms.STARTDATE, SessionTerms.ENDDATE,
@CHANGEAGENTID, @CHANGEAGENTID,
@CURRENTDATE, @CURRENTDATE,
SessionTerms.TIMETABLEDAYID, TM.TERMNAMECODEID,
SessionTerms.WITHDRAWALDATE
from
(select
T.c.value('(SESSIONNAMECODEID)[1]','uniqueidentifier') as SESSIONNAMECODEID,
T.c.value('(TERMNAMECODEID)[1]','uniqueidentifier') as TERMNAMECODEID,
T.c.value('(STARTDATE)[1]','date') as STARTDATE,
T.c.value('(ENDDATE)[1]','date') as ENDDATE,
T.c.value('(WITHDRAWALDATE)[1]','date') as WITHDRAWALDATE,
T.c.value('(TIMETABLEDAYID)[1]','uniqueidentifier') as TIMETABLEDAYID
from @SESSIONTERMS.nodes('/SESSIONTERMS/ITEM') T(c)) as SessionTerms
inner join TERM tm on SessionTerms.TERMNAMECODEID = tm.TERMNAMECODEID
and tm.SESSIONID = @OLDSESSIONID
where SessionTerms.SESSIONNAMECODEID = @SESSIONNAMECODEID;
--Let's save the TERM info for use while adding Start terms for Course restrictions
delete from @TERM_DATES
insert into @TERM_DATES
select
TM.TERMNAMECODEID,SessionTerms.STARTDATE,SessionTerms.ENDDATE
from
(select
T.c.value('(TERMNAMECODEID)[1]','uniqueidentifier') as TERMNAMECODEID,
T.c.value('(STARTDATE)[1]','date') as STARTDATE,
T.c.value('(ENDDATE)[1]','date') as ENDDATE,
T.c.value('(WITHDRAWALDATE)[1]','date') as WITHDRAWALDATE,
T.c.value('(SESSIONNAMECODEID)[1]','uniqueidentifier') as SESSIONNAMECODEID
from @SESSIONTERMS.nodes('/SESSIONTERMS/ITEM') T(c)) as SessionTerms
inner join TERM tm on SessionTerms.TERMNAMECODEID = tm.TERMNAMECODEID
and tm.SESSIONID = @OLDSESSIONID
where SessionTerms.SESSIONNAMECODEID = @SESSIONNAMECODEID;
--Now add the calendar entries to the newly added terms
declare @TempTermTable table (
ROWNUMBER int identity(1,1), TERMID uniqueidentifier)
delete from @TempTermTable
insert into @TempTermTable (TERMID)
select ID from dbo.TERM where SESSIONID = @NEWSESSIONID
select @Term_RowID=MIN(ROWNUMBER),@Term_CountID=MAX(ROWNUMBER)
from @TempTermTable
declare @TermID uniqueidentifier = null
while @Term_RowID <= @Term_CountID
begin
select @TERMID = TERMID
from @TempTermTable
where ROWNUMBER = @Term_RowID
exec dbo.USP_SCHEDULEDATE_GENERATEENTRIES @TermID, null, null, null, @CHANGEAGENTID
set @Term_RowID = @Term_RowID + 1
end
end
select @SESSION_oldSTARTDATE=STARTDATE, @SESSION_oldENDDATE=ENDDATE
from UFN_SESSION_GETDATES(@OLDSESSIONID)
select @SESSION_newSTARTDATE=STARTDATE, @SESSION_newENDDATE=ENDDATE
from UFN_SESSION_GETDATES(@NEWSESSIONID)
if @COPYCOURSERESTRICTION = 1
begin
declare @newCOURSERESTRICTIONID uniqueidentifier
declare @oldCOURSERESTRICTIONID uniqueidentifier
declare @tempCOURSERESTRICTIONS table
(
ROWNUMBER int identity(1,1),
OLDCOURSERESTRICTIONID uniqueidentifier
)
delete from @tempCOURSERESTRICTIONS
insert into @tempCOURSERESTRICTIONS(OLDCOURSERESTRICTIONID)
select CRT.ID from COURSERESTRICTION CRT
INNER JOIN COURSE CR ON CRT.COURSEID = CR.ID
where (CRT.STARTDATE = @SESSION_oldSTARTDATE AND CRT.ENDDATE = @SESSION_oldENDDATE)
and CR.SCHOOLID = @SCHOOLID and CR.ISINACTIVE = 0
select @child_ROW=MIN(ROWNUMBER),@child_COUNT=MAX(ROWNUMBER)
from @tempCOURSERESTRICTIONS
while @child_ROW <= @child_COUNT
begin
select @oldCOURSERESTRICTIONID = OLDCOURSERESTRICTIONID
from @tempCOURSERESTRICTIONS
where ROWNUMBER = @child_ROW
set @newCOURSERESTRICTIONID = newid()
insert into COURSERESTRICTION
(
ID,COURSEID,
STARTDATE,ENDDATE,
LENGTHINTERMS,PATTERNID,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED
)
select
@newCOURSERESTRICTIONID, CRT.COURSEID,
@SESSION_newSTARTDATE,@SESSION_newENDDATE,
CRT.LENGTHINTERMS,CRT.PATTERNID,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE
from COURSERESTRICTION CRT
where CRT.ID = @oldCOURSERESTRICTIONID
insert into COURSERESTRICTIONSTARTTERM
(
ID,COURSERESTRICTIONID,
STARTDATE,ENDDATE,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED
)
select
NEWID(),@newCOURSERESTRICTIONID,
TD.STARTDATE,TD.ENDDATE,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE
from COURSERESTRICTION CR
inner join (select
CRT.ID,TM.TERMNAMECODEID
from COURSERESTRICTION CRT
inner join COURSERESTRICTIONSTARTTERM CRST
on CRT.ID = CRST.COURSERESTRICTIONID
inner join TERM TM
on (CRST.STARTDATE = TM.STARTDATE
and CRST.ENDDATE = TM.ENDDATE)
and TM.SESSIONID = @OLDSESSIONID
inner join COURSE CR
on (CRT.COURSEID = CR.ID)
where CR.SCHOOLID = @SCHOOLID
and CR.ISINACTIVE = 0) OLD_CR
on CR.ID = OLD_CR.ID
inner join @TERM_DATES TD
on TD.TERMNAMECODEID = OLD_CR.TERMNAMECODEID
where OLD_CR.ID = @oldCOURSERESTRICTIONID
set @child_ROW = @child_ROW + 1
end --COURSERESTRICTION/STARTERM
end --Course restrictions copy
if @COPYCOURSEGRADING = 1
begin
declare @CGRADING table
(
ROWNUMBER int identity(1,1),
CGRADINGID uniqueidentifier
)
declare @oldCOURSEGRADINGID uniqueidentifier
declare @newCOURSEGRADINGID uniqueidentifier
delete from @CGRADING
insert into @CGRADING(CGRADINGID)
select CG.ID from COURSEGRADING CG
inner join COURSE CR on CG.COURSEID = CR.ID
where (CG.STARTDATE = @SESSION_oldSTARTDATE AND CG.ENDDATE = @SESSION_oldENDDATE)
and CR.ISINACTIVE = 0
and CR.SCHOOLID = @SCHOOLID
select @child_ROW=MIN(ROWNUMBER),@child_COUNT=MAX(ROWNUMBER)
from @CGRADING
while @child_ROW <= @child_COUNT
begin
select
@oldCOURSEGRADINGID=CG.CGRADINGID,
@newCOURSEGRADINGID = NEWID()
from @CGRADING CG where CG.ROWNUMBER = @child_ROW
insert into COURSEGRADING
(
ID,COURSEID,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED,
STARTDATE,ENDDATE
)
select
@newCOURSEGRADINGID,CG.COURSEID,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE,
@SESSION_newSTARTDATE,@SESSION_newENDDATE
from COURSEGRADING CG
where CG.ID = @oldCOURSEGRADINGID
and (CG.STARTDATE = @SESSION_oldSTARTDATE
and CG.ENDDATE = @SESSION_oldENDDATE)
insert into COURSEGRADINGMARKINGCOLUMN
(
ID,COURSEGRADINGID,MARKINGCOLUMNID,GRADED,
TRANSLATIONTABLEID,VALUESALLOWEDCODE,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED
)
select NEWID(),@newCOURSEGRADINGID,CGM.MARKINGCOLUMNID, CGM.GRADED,
CGM.TRANSLATIONTABLEID,CGM.VALUESALLOWEDCODE,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE
from COURSEGRADINGMARKINGCOLUMN CGM
where CGM.COURSEGRADINGID = @oldCOURSEGRADINGID
set @child_ROW = @child_ROW + 1
end --COURSEGRADING LOOP
end --Course grading copy
set @session_row = @session_row + 1
end -- end of session loop
-- Since we copy all Billing items not limited to a certain school
-- this copy is outside the session loop
if @COPYBILLINGITEMCOSTHISTORY = 1
begin
declare @oldBILLINGITEMID uniqueidentifier
declare @newBILLINGITEMDATESID uniqueidentifier
declare @oldBILLINGITEMDATESID uniqueidentifier
declare @oldBILLINGSTARTDATE date
declare @oldBILLINGENDDATE date
declare @BILLINGITEMCOPY table
(
OLDBILLINGITEMID uniqueidentifier
)
declare @BILLINGITEMDATESCOPY table
(
ROWNUMBER int identity(1,1),
OLDBILLINGITEMDATESID uniqueidentifier
)
delete from @BILLINGITEMCOPY
delete from @BILLINGITEMDATESCOPY
select
@oldBILLINGSTARTDATE=AY.BILLINGSTARTDATE,
@oldBILLINGENDDATE=AY.BILLINGENDDATE
from ACADEMICYEAR AY
where AY.ID = @ACADEMICYEARID
-- Copy only Academic year items
insert into @BILLINGITEMCOPY(OLDBILLINGITEMID)
select DISTINCT BI.ID
from BILLINGITEM BI
left join BILLINGITEMPRICE BIP
on BIP.BILLINGITEMID = BI.ID
left join BILLINGITEMDATES BID
on BID.BILLINGITEMID = BI.ID
where BI.ISINACTIVE = 0
and (BID.DATERANGETYPECODE = 2 or BIP.DATERANGETYPECODE = 2)
-- 1. Copy BILLINGITEMPRICE
insert into BILLINGITEMPRICE
(
ID,BILLINGITEMID,
STARTDATE,ENDDATE,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED,
PRICE,DATERANGETYPECODE
)
select
NEWID(),BIC.OLDBILLINGITEMID,
@BILLINGSTARTDATE,@BILLINGENDDATE,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE,
BIP.PRICE,BIP.DATERANGETYPECODE
from BILLINGITEMPRICE BIP
inner join @BILLINGITEMCOPY BIC
on BIP.BILLINGITEMID = BIC.OLDBILLINGITEMID
where (BIP.STARTDATE = @oldBILLINGSTARTDATE and BIP.ENDDATE = @oldBILLINGENDDATE)
insert into @BILLINGITEMDATESCOPY(OLDBILLINGITEMDATESID)
select DISTINCT BID.ID
from BILLINGITEMDATES BID
inner join BILLINGITEM BI
on BID.BILLINGITEMID = BI.ID
where (BID.STARTDATE = @oldBILLINGSTARTDATE and BID.ENDDATE = @oldBILLINGENDDATE)
and (BID.STARTDATE <> @BILLINGSTARTDATE and BID.ENDDATE <> @BILLINGENDDATE)
and BID.DATERANGETYPECODE = 2 and BI.ISINACTIVE = 0
select @child_ROW=MIN(ROWNUMBER),@child_COUNT=MAX(ROWNUMBER)
from @BILLINGITEMDATESCOPY
while @child_ROW<=@child_COUNT
begin
set @newBILLINGITEMDATESID = NEWID()
select @oldBILLINGITEMDATESID = BIPC.OLDBILLINGITEMDATESID
from @BILLINGITEMDATESCOPY BIPC
where BIPC.ROWNUMBER = @child_ROW
-- 3. Copy BILLINGITEMDATES
insert into BILLINGITEMDATES
(
ID,BILLINGITEMID,
STARTDATE,ENDDATE,
ADDEDBYID,CHANGEDBYID,
DATEADDED,DATECHANGED,
DATERANGETYPECODE
)
select
@newBILLINGITEMDATESID,BID.BILLINGITEMID,
@BILLINGSTARTDATE,@BILLINGENDDATE,
@CHANGEAGENTID,@CHANGEAGENTID,
@CURRENTDATE,@CURRENTDATE,BID.DATERANGETYPECODE
from BILLINGITEMDATES BID
INNER JOIN @BILLINGITEMDATESCOPY BIDC
ON BID.ID = BIDC.OLDBILLINGITEMDATESID
where BIDC.OLDBILLINGITEMDATESID = @oldBILLINGITEMDATESID
-- 4. Copy BILLINGITEMPRICEBYGRADELEVEL
insert into BILLINGITEMPRICEBYGRADELEVEL
(
ID,BILLINGITEMDATESID,GRADELEVELID,PRICE,
DATEADDED,DATECHANGED,
ADDEDBYID,CHANGEDBYID
)
select
NEWID(),@newBILLINGITEMDATESID,
BIPG.GRADELEVELID,BIPG.PRICE,
@CURRENTDATE,@CURRENTDATE,
@CHANGEAGENTID,@CHANGEAGENTID
from BILLINGITEMPRICEBYGRADELEVEL BIPG
inner join BILLINGITEMDATES BID
on BIPG.BILLINGITEMDATESID = BID.ID
inner join @BILLINGITEMDATESCOPY BIDC
on BID.ID = BIDC.OLDBILLINGITEMDATESID
where BIDC.OLDBILLINGITEMDATESID = @oldBILLINGITEMDATESID
-- 5. Copy BILLINGITEMPRICEBYSCHOOL
insert into BILLINGITEMPRICEBYSCHOOL
(
ID,BILLINGITEMDATESID,SCHOOLID,PRICE,
DATEADDED,DATECHANGED,
ADDEDBYID,CHANGEDBYID
)
select
NEWID(),@newBILLINGITEMDATESID,
BIPS.SCHOOLID,BIPS.PRICE,
@CURRENTDATE,@CURRENTDATE,
@CHANGEAGENTID,@CHANGEAGENTID
from BILLINGITEMPRICEBYSCHOOL BIPS
inner join BILLINGITEMDATES BID
on BIPS.BILLINGITEMDATESID = BID.ID
inner join @BILLINGITEMDATESCOPY BIDC
on BID.ID = BIDC.OLDBILLINGITEMDATESID
where BIDC.OLDBILLINGITEMDATESID = @oldBILLINGITEMDATESID
set @child_ROW = @child_ROW + 1 ;
end
end ; -- COPY BILLING ITEMS
end ; -- Copy not Create
return 0 ;
end ;