USP_STUDENTSKILL_ADDCOURSES

CREATE procedure dbo.USP_STUDENTSKILL_ADDCOURSES
(
    @STUDENTSKILLS xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @STARTINACADEMICYEARNAMECODEID uniqueidentifier,
    @STOPAFTERACADEMICYEARNAMECODEID uniqueidentifier = null,
    @STUDENTSKILLMARKINGCOLUMNS xml = null,
    @STUDENTSKILLCOURSES xml = null
)
as
begin

    set nocount on;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate()

    declare @skillcourse table(ID uniqueidentifier, COURSEID uniqueidentifier, STUDENTSKILLID uniqueidentifier, STARTDATE date, ENDDATE date, MAXSEQUENCE integer, STUDENTSKILLSEQUENCE integer)
    declare @skillcourseMC table(ID uniqueidentifier, SKILLCOURSEID uniqueidentifier, MCID uniqueidentifier)

    -- add skill to all selected courses

    insert into @skillcourse 
        (ID, COURSEID, STUDENTSKILLID, STUDENTSKILLSEQUENCE)
        select
            newid(),
            c.value('COURSEID[1]', 'uniqueidentifier'),
            S.STUDENTSKILLID,
            S.SEQUENCE
        from @STUDENTSKILLCOURSES.nodes('/STUDENTSKILLCOURSES/ITEM') as T(c)        
        cross join
            (select c.value('ID[1]', 'uniqueidentifier') 'STUDENTSKILLID', 
                c.value('SEQUENCE[1]', 'integer') 'SEQUENCE'
                from @STUDENTSKILLS.nodes('/STUDENTSKILLS/ITEM') as T(c)) S

    -- get max sequence currently on each course

    update SC
        set MAXSEQUENCE = 
            (select coalesce(max(STUDENTSKILLCOURSE.SEQUENCE), -1) from dbo.STUDENTSKILLCOURSE where SC.COURSEID = STUDENTSKILLCOURSE.COURSEID)
        from @skillcourse SC        

    -- get start date based on school and selected code table entry

    update SC
        set STARTDATE =
            ACADEMICYEAR.STARTDATE
        from
            @skillcourse SC
        inner join
            dbo.COURSE on SC.COURSEID = COURSE.ID
        inner join
            dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
        where
            ACADEMICYEAR.ACADEMICYEARNAMECODEID = @STARTINACADEMICYEARNAMECODEID

    -- get end date based on school and selected code table entry

    if (@STOPAFTERACADEMICYEARNAMECODEID is not null)
    begin
        update SC
        set ENDDATE =
            ACADEMICYEAR.ENDDATE
        from
            @skillcourse SC
        inner join
            dbo.COURSE on SC.COURSEID = COURSE.ID
        inner join
            dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
        where
            ACADEMICYEAR.ACADEMICYEARNAMECODEID = @STOPAFTERACADEMICYEARNAMECODEID
     end       

     if exists (select ID from @skillcourse where STARTDATE is null)
        raiserror('BBERR_STUDENTSKILLCOURSE_STARTDATE_ACADEMICYEARNOTAVAILABLE', 13, 1)

     if exists (select ID from @skillcourse where ENDDATE is null) and (@STOPAFTERACADEMICYEARNAMECODEID is not null)
        raiserror('BBERR_STUDENTSKILLCOURSE_ENDDATE_ACADEMICYEARNOTAVAILABLE', 13, 1)

    -- and add MC's to all skill courses just created

    if exists(select ID from @skillcourse)
    begin
        insert into @skillcourseMC
            select
                newid(),
                SC.ID,
                c.value('MARKINGCOLUMNDESCRIPTIONCODEID[1]', 'uniqueidentifier')
            from @skillcourse SC
                cross join @STUDENTSKILLMARKINGCOLUMNS.nodes('/STUDENTSKILLMARKINGCOLUMNS/ITEM') as T(c)
    end

    if (select count(*) from @skillcourse) > 0 and (select count(*) from @skillcourseMC) = 0
        raiserror('BBERR_STUDENTSKILLCOURSE_MARKINGCOLUMNS_REQUIRED', 13, 1)

    insert into dbo.STUDENTSKILLCOURSE
        (ID, COURSEID, STUDENTSKILLID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE, SEQUENCE)
    select
        SC.ID,
        SC.COURSEID,
        SC.STUDENTSKILLID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE,
        SC.STARTDATE, 
        SC.ENDDATE,
        SC.MAXSEQUENCE + SC.STUDENTSKILLSEQUENCE
    from @skillcourse SC

    insert into dbo.STUDENTSKILLCOURSEMARKINGCOLUMN
        (ID, STUDENTSKILLCOURSEID, MARKINGCOLUMNDESCRIPTIONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        SCMC.ID,
        SCMC.SKILLCOURSEID,
        SCMC.MCID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
    from @skillcourseMC SCMC

end