USP_ADDREQUESTSFORMULTIPLESTUDENTS_BULKPROCESS

CREATE procedure dbo.USP_ADDREQUESTSFORMULTIPLESTUDENTS_BULKPROCESS(
    @IDSETREGISTERID uniqueidentifier,
    @SCHOOLID uniqueidentifier,
    @ACADEMICYEARID uniqueidentifier,
    @SESSIONNAMECODEID uniqueidentifier,
    @CORECURRICULA xml,
    @COURSES xml,
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

    declare @SESSIONID uniqueidentifier
    declare @STARTDATE date
    declare @ENDDATE date

    if @ACADEMICYEARID = '00000000-0000-0000-0000-000000000001'
        set @ACADEMICYEARID = dbo.UFN_ACADEMICYEAR_GETCURRENT(getdate(), @SCHOOLID) 
    if @ACADEMICYEARID = '00000000-0000-0000-0000-000000000002'
        set @ACADEMICYEARID = dbo.UFN_ACADEMICYEAR_GETNEXT(getdate(), @SCHOOLID)

    select @SESSIONID = SESSION.ID
        from dbo.SESSION
        where ACADEMICYEARID = @ACADEMICYEARID and SESSIONNAMECODEID = @SESSIONNAMECODEID        

    select @STARTDATE = MIN(STARTDATE), @ENDDATE = MAX(ENDDATE)
    from dbo.SESSION 
        inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
    where SESSION.ID = @SESSIONID

    declare @STUDENT table (STUDENTID uniqueidentifier not null, STUDENT_NAME nvarchar(154) null, SCHOOLID uniqueidentifier null, SCHOOLGRADELEVELID uniqueidentifier null)

    --limit to selection, if chosen

    if (@IDSETREGISTERID is null) or (@IDSETREGISTERID = '00000000-0000-0000-0000-000000000000')
        insert into @STUDENT (STUDENTID)
        select ID from dbo.STUDENT
    else
        insert into @STUDENT (STUDENTID)
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID)

    --limit to those students with SPE's for the chosen academic year

    update STUDENT set SCHOOLID = SCHOOLGRADELEVEL.SCHOOLID, STUDENT_NAME = CONSTITUENT.NAME, SCHOOLGRADELEVELID = STUDENTPROGRESSION.SCHOOLGRADELEVELID
        from @STUDENT as STUDENT
            inner join dbo.CONSTITUENT on STUDENT.STUDENTID = CONSTITUENT.ID
            inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
            inner join dbo.SCHOOLGRADELEVEL on SCHOOLGRADELEVEL.ID = STUDENTPROGRESSION.SCHOOLGRADELEVELID
        where dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) = @ACADEMICYEARID

    delete from @STUDENT where SCHOOLGRADELEVELID is null

    declare @CORECOURSES table (CORECURRICULUMCOURSEID uniqueidentifier not null, SCHOOLID uniqueidentifier not null, SCHOOLGRADELEVELID uniqueidentifier null, TERMNAMECODEID uniqueidentifier null, STARTDATE date null, STARTTERMID uniqueidentifier null)

    --get courses to add requests for

    if @CORECURRICULA is null
        insert into @CORECOURSES (CORECURRICULUMCOURSEID, TERMNAMECODEID, SCHOOLID, SCHOOLGRADELEVELID)
        select CORECURRICULUMCOURSE.ID, COURSES.TERMNAMECODEID, CORECURRICULUM.SCHOOLID, CORECURRICULUM.SCHOOLGRADELEVELID
        from dbo.CORECURRICULUMCOURSE
            left outer join dbo.UFN_ADDREQUESTSFORMULTIPLESTUDENTS_GETCOURSES_FROMITEMLISTXML(@COURSES) as COURSES on CORECURRICULUMCOURSE.ID = COURSES.CORECURRICULUMCOURSEID
            inner join dbo.CORECURRICULUM on CORECURRICULUMCOURSE.CORECURRICULUMID = CORECURRICULUM.ID
    else
        insert into @CORECOURSES (CORECURRICULUMCOURSEID, TERMNAMECODEID, SCHOOLID, SCHOOLGRADELEVELID)
        select CORECURRICULUMCOURSE.ID, COURSES.TERMNAMECODEID, CORECURRICULUM.SCHOOLID, CORECURRICULUM.SCHOOLGRADELEVELID
        from dbo.CORECURRICULUMCOURSE
            inner join dbo.UFN_ADDREQUESTSFORMULTIPLESTUDENTS_GETCORE_FROMITEMLISTXML(@CORECURRICULA) as CORE on CORECURRICULUMCOURSE.CORECURRICULUMID = CORE.CORECURRICULUMID
            left outer join dbo.UFN_ADDREQUESTSFORMULTIPLESTUDENTS_GETCOURSES_FROMITEMLISTXML(@COURSES) as COURSES on CORECURRICULUMCOURSE.ID = COURSES.CORECURRICULUMCOURSEID
            inner join dbo.CORECURRICULUM on CORECURRICULUMCOURSE.CORECURRICULUMID = CORECURRICULUM.ID

    update CC set STARTDATE = TERM.STARTDATE, STARTTERMID = TERM.ID
        from @CORECOURSES CC
            inner join dbo.TERM on CC.TERMNAMECODEID = TERM.TERMNAMECODEID
        where TERM.SESSIONID = @SESSIONID

    --create requests

    create table #STUDENTREQUESTINFO (ID uniqueidentifier, STUDENTID uniqueidentifier not null, STUDENT_NAME nvarchar(154) collate DATABASE_DEFAULT not null, COURSEID uniqueidentifier not null, SCHOOL_NAME nvarchar(154) collate DATABASE_DEFAULT not null, SCHOOLGRADELEVEL nvarchar(10) collate DATABASE_DEFAULT not null , SCHOOLGRADELEVELSEQUENCE int not null, CLASSSTARTDATE date not null, EXCEPTION nvarchar(20) collate DATABASE_DEFAULT, COURSEINFO nvarchar(1000) collate DATABASE_DEFAULT, COURSE_NAME nvarchar(72) collate DATABASE_DEFAULT)

    insert into #STUDENTREQUESTINFO (ID, STUDENTID, STUDENT_NAME, COURSEID, SCHOOL_NAME, SCHOOLGRADELEVEL, SCHOOLGRADELEVELSEQUENCE, CLASSSTARTDATE, EXCEPTION)
        select 
            NEWID(), 
            STUDENT.STUDENTID, 
            STUDENT.STUDENT_NAME,
            CORECURRICULUMCOURSE.COURSEID,
            dbo.UFN_SCHOOL_GETNAME(STUDENT.SCHOOLID) as SCHOOL_NAME, 
            dbo.UFN_SCHOOLGRADELEVEL_GETNAME(STUDENT.SCHOOLGRADELEVELID) as SCHOOLGRADELEVEL,
            (select
                SEQUENCE
             from dbo.SCHOOLGRADELEVEL 
                 inner join dbo.GRADELEVEL 
                    on SCHOOLGRADELEVEL.GRADELEVELID = GRADELEVEL.ID
                 where SCHOOLGRADELEVEL.ID = STUDENT.SCHOOLGRADELEVELID) as SCHOOLGRADELEVELSEQUENCE,
            CC.STARTDATE, 
            EXCEPTION.EXCEPTION
        from @STUDENT as STUDENT
        cross join @CORECOURSES CC
        inner join dbo.CORECURRICULUMCOURSE 
            on CC.CORECURRICULUMCOURSEID = CORECURRICULUMCOURSE.ID
        outer apply dbo.UFN_STUDENTCOURSEREQUEST_GETEXCEPTIONTABLE(STUDENT.STUDENTID,CORECURRICULUMCOURSE.COURSEID,@SESSIONID,CC.STARTTERMID) as EXCEPTION
        where CC.STARTDATE is not null 
            and ((STUDENT.SCHOOLGRADELEVELID = CC.SCHOOLGRADELEVELID) or (STUDENT.SCHOOLID = CC.SCHOOLID and CC.SCHOOLGRADELEVELID is null))

    insert into dbo.STUDENTCOURSE (ID, STUDENTID, COURSEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            ID, STUDENTID, COURSEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE 
        from #STUDENTREQUESTINFO
        where EXCEPTION is null

    insert into dbo.STUDENTCOURSEREQUEST (ID, STARTDATE, ENDDATE, CLASSSTARTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            ID, @STARTDATE, @ENDDATE, CLASSSTARTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE 
        from #STUDENTREQUESTINFO
        where EXCEPTION is null

    declare @LOGSUCCESSSQL nvarchar(400)
    declare @LOGEXCEPTIONSQL nvarchar(1200)

    set @LOGSUCCESSSQL = N'insert into dbo.' + @SUCCESSTABLE + N' (STUDENTID, STUDENT_NAME, SCHOOLGRADELEVEL, SCHOOLGRADELEVELSEQUENCE) select STUDENTID, STUDENT_NAME, SCHOOLGRADELEVEL, SCHOOLGRADELEVELSEQUENCE from #STUDENTREQUESTINFO where EXCEPTION is null'
    exec sp_executesql @LOGSUCCESSSQL

    select @SUCCESSCOUNT = COUNT(distinct STUDENTID) from #STUDENTREQUESTINFO where EXCEPTION is null

    update #STUDENTREQUESTINFO set COURSE_NAME = (select COURSE.NAME + ' - ' + COURSE.COURSEID from dbo.COURSE where ID = #STUDENTREQUESTINFO.COURSEID),
        COURSEINFO = case 
                when EXCEPTION = 'GENDER' then 
                    (select GENDER from dbo.COURSE where ID = #STUDENTREQUESTINFO.COURSEID)
                when EXCEPTION = 'SCHOOL' then 
                    dbo.UFN_SCHOOL_GETNAME(dbo.UFN_COURSE_GETSCHOOLID(#STUDENTREQUESTINFO.COURSEID)) 
                when EXCEPTION = 'GRADE_LEVEL' then 
                    dbo.UFN_COURSE_GETGRADELEVELSTRING(#STUDENTREQUESTINFO.COURSEID) else '' end
        where EXCEPTION is not null

    set @LOGEXCEPTIONSQL = N'insert into dbo.' + @EXCEPTIONTABLE + N' (STUDENTID, STUDENT_NAME, COURSEID, COURSE_NAME, SCHOOL_NAME, SCHOOLGRADELEVEL, SCHOOLGRADELEVELSEQUENCE, ERRORMESSAGE, COURSEINFO) 
        select 
            STUDENTID, 
            STUDENT_NAME, 
            COURSEID, 
            COURSE_NAME, 
            SCHOOL_NAME, 
            SCHOOLGRADELEVEL, 
            SCHOOLGRADELEVELSEQUENCE, 
            EXCEPTION, 
            COURSEINFO 
        from #STUDENTREQUESTINFO where EXCEPTION is not null'

    exec sp_executesql @LOGEXCEPTIONSQL

    select @EXCEPTIONCOUNT = COUNT(distinct STUDENTID) from #STUDENTREQUESTINFO where EXCEPTION is not null
end