USP_DATAFORMTEMPLATE_ADD_STUDENTSKILLLOADCATEGORIES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_STUDENTSKILLLOADCATEGORIES
(
    @ID uniqueidentifier = null output,
    @COURSEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SELECTEDSKILLCATEGORIES xml,
    @AVAILABLESKILLCATEGORIES xml = null,
    @STUDENTSKILLCOURSEMARKINGCOLUMNS xml,
    @STARTINACADEMICYEARNAMECODEID uniqueidentifier,
    @STOPAFTERACADEMICYEARNAMECODEID uniqueidentifier = null
)
as

set nocount on;

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

declare @MAXSEQUENCE integer
set @MAXSEQUENCE = (select coalesce(max(SSC.SEQUENCE), -1) from dbo.STUDENTSKILLCOURSE SSC where SSC.COURSEID=@COURSEID) 

declare @TEMPSKILLCOURSES table(
    ID uniqueidentifier,
    STUDENTSKILLID uniqueidentifier,
    SEQUENCE integer primary key identity)

insert into @TEMPSKILLCOURSES
    select newid(), STUDENTSKILL.ID 
    FROM dbo.STUDENTSKILL where STUDENTSKILLCATEGORYCODEID in 
        (
        (SELECT
        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
        FROM @SELECTEDSKILLCATEGORIES.nodes('/SELECTEDSKILLCATEGORIES/ITEM') T(c))
        )
    order by STUDENTSKILL.DATEADDED

--Get the dates

declare @STARTDATE datetime = null, @ENDDATE datetime = null

if @STARTINACADEMICYEARNAMECODEID is not null
    select @STARTDATE = ACADEMICYEAR.STARTDATE from dbo.ACADEMICYEAR 
    inner join dbo.COURSE on ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID
    where ACADEMICYEAR.ACADEMICYEARNAMECODEID = @STARTINACADEMICYEARNAMECODEID and
                COURSE.ID = @COURSEID

if @STOPAFTERACADEMICYEARNAMECODEID is not null
    select @ENDDATE = ACADEMICYEAR.ENDDATE from dbo.ACADEMICYEAR 
    inner join dbo.COURSE on ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID
    where ACADEMICYEAR.ACADEMICYEARNAMECODEID = @STOPAFTERACADEMICYEARNAMECODEID and
                COURSE.ID = @COURSEID

if (@STARTDATE is null) 
    raiserror('BBERR_STUDENTSKILLCOURSE_STARTDATE_ACADEMICYEARNOTAVAILABLE', 13, 1)

if ((@ENDDATE is null) and (@STOPAFTERACADEMICYEARNAMECODEID is not null))
    raiserror('BBERR_STUDENTSKILLCOURSE_ENDDATE_ACADEMICYEARNOTAVAILABLE', 13, 1)

--add new records

insert into dbo.STUDENTSKILLCOURSE
    (ID, COURSEID, STUDENTSKILLID, STARTDATE, ENDDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, 
        SEQUENCE)
    select ID, @COURSEID, STUDENTSKILLID, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
        @MAXSEQUENCE + TEMP.SEQUENCE
    From @TEMPSKILLCOURSES TEMP
    where not exists (Select ID from dbo.STUDENTSKILLCOURSE SSC where SSC.COURSEID = @COURSEID and SSC.STUDENTSKILLID = TEMP.STUDENTSKILLID)

--update end date where it is blank on existing matching records

update STUDENTSKILLCOURSE set ENDDATE = @ENDDATE
from dbo.STUDENTSKILLCOURSE
inner join @TEMPSKILLCOURSES TEMP on 
    TEMP.STUDENTSKILLID = STUDENTSKILLCOURSE.STUDENTSKILLID and 
    STUDENTSKILLCOURSE.COURSEID = @COURSEID and 
    STUDENTSKILLCOURSE.ENDDATE is null

declare @TEMPSKILLCOURSESMC table(
    ID uniqueidentifier, 
    SKILLCOURSEID uniqueidentifier, 
    MCID uniqueidentifier)


insert into @TEMPSKILLCOURSESMC
    select
        newid(),
        SC.ID,
        c.value('MARKINGCOLUMNDESCRIPTIONCODEID[1]', 'uniqueidentifier')
    from dbo.STUDENTSKILLCOURSE SC
    cross join @STUDENTSKILLCOURSEMARKINGCOLUMNS.nodes('/STUDENTSKILLCOURSEMARKINGCOLUMNS/ITEM') as T(c)
    where SC.COURSEID = @COURSEID and SC.STUDENTSKILLID in (select STUDENTSKILLID from @TEMPSKILLCOURSES)


--add new Marking Column records

insert into dbo.STUDENTSKILLCOURSEMARKINGCOLUMN
        (ID, STUDENTSKILLCOURSEID, MARKINGCOLUMNDESCRIPTIONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        TEMP.ID,
        TEMP.SKILLCOURSEID,
        TEMP.MCID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
 @CURRENTDATE
    from @TEMPSKILLCOURSESMC TEMP
    where not exists (Select ID from dbo.STUDENTSKILLCOURSEMARKINGCOLUMN SSCMC 
                        where SSCMC.STUDENTSKILLCOURSEID = TEMP.SKILLCOURSEID and 
                        SSCMC.MARKINGCOLUMNDESCRIPTIONCODEID = TEMP.MCID)