USP_DATAFORMTEMPLATE_ADD_PROMOTESTUDENTS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROMOTESTUDENTS
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @SCHOOLID uniqueidentifier = null,
    @PROMOTEFROM_ACADEMICYEARID uniqueidentifier,
    @PROMOTETO_ACADEMICYEARID uniqueidentifier,
    @UPDATEEXISTING bit = null,
    @STATUSES xml = null,
    @PROMOTIONSCHEDULEID uniqueidentifier = null,
    @FORADVISORINFORMATIONCODE tinyint = 0,
    @GRADELEVELADVISORS xml = null
)
as

set nocount on;

if @PROMOTEFROM_ACADEMICYEARID = @PROMOTETO_ACADEMICYEARID
    raiserror('BBERR_PROMOTESTUDENTS_SAMEACADEMICYEAR', 13, 1)

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try
    declare @PROMOTETO_NEXTSCHOOL_ACADEMICYEARID uniqueidentifier = (select ACADEMICYEAR.ID 
                                                                        from dbo.ACADEMICYEAR
                                                                            inner join dbo.SCHOOLGRADELEVEL on ACADEMICYEAR.SCHOOLID = SCHOOLGRADELEVEL.SCHOOLID
                                                                            inner join dbo.PROMOTIONSCHEDULE on SCHOOLGRADELEVEL.ID = PROMOTIONSCHEDULE.NEXTSCHOOLGRADELEVELID
                                                                            inner join dbo.ACADEMICYEAR AY on ACADEMICYEAR.ACADEMICYEARNAMECODEID = AY.ACADEMICYEARNAMECODEID
                                                                        where AY.ID = @PROMOTETO_ACADEMICYEARID and PROMOTIONSCHEDULE.ID = @PROMOTIONSCHEDULEID)

    declare @STUDENTPROGRESSION table (STUDENTID uniqueidentifier not null, ENROLLMENTID uniqueidentifier not null, CURRENTSTUDENTPROGRESSIONID uniqueidentifier not null, NEXTSTUDENTPROGRESSIONID uniqueidentifier null, CURRENTSCHOOLGRADELEVELID uniqueidentifier not null, NEXTSCHOOLGRADELEVELID uniqueidentifier not null, ADVISORID uniqueidentifier null, STARTDATE datetime null, ENDDATE datetime null)

    insert into @STUDENTPROGRESSION (
        STUDENTID, 
        ENROLLMENTID, 
        CURRENTSTUDENTPROGRESSIONID, 
        CURRENTSCHOOLGRADELEVELID, 
        NEXTSCHOOLGRADELEVELID, 
        ADVISORID
        )
    select 
        STUDENT.ID, 
        EDUCATIONALHISTORY.ID, 
        STUDENTPROGRESSION.ID, 
        SCHOOLGRADELEVEL.ID, 
        case GRADUATES when 0 then PROMOTIONSCHEDULEENTRY.NEXTSCHOOLGRADELEVELID else PROMOTIONSCHEDULE.NEXTSCHOOLGRADELEVELID end, 
        case @FORADVISORINFORMATIONCODE when 0 then (select TOP 1 FACULTYID from dbo.STUDENTADVISOR where STUDENTPROGRESSIONID = STUDENTPROGRESSION.ID) else ADVISORS.ADVISORID end
    from dbo.STUDENT
        inner join dbo.CONSTITUENT on STUDENT.ID = CONSTITUENT.ID
        inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
        inner join (select T.c.value('(EDUCATIONALHISTORYSTATUSID)[1]','uniqueidentifier') as EDUCATIONALHISTORYSTATUSID
                    from @STATUSES.nodes('/STATUSES/ITEM') T(c)) STATUSES 
                        on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = STATUSES.EDUCATIONALHISTORYSTATUSID
        inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
        inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        inner join dbo.PROMOTIONSCHEDULEENTRY on STUDENTPROGRESSION.SCHOOLGRADELEVELID = PROMOTIONSCHEDULEENTRY.SCHOOLGRADELEVELID
        inner join dbo.PROMOTIONSCHEDULE on PROMOTIONSCHEDULEENTRY.PROMOTIONSCHEDULEID = PROMOTIONSCHEDULE.ID
        left outer join (select T.c.value('(NEXTSCHOOLGRADELEVELID)[1]','uniqueidentifier') as NEXTSCHOOLGRADELEVELID,
                            T.c.value('(ADVISORID)[1]','uniqueidentifier') as ADVISORID
                            from @GRADELEVELADVISORS.nodes('/GRADELEVELADVISORS/ITEM') T(c)) ADVISORS
                                on ((GRADUATES = 0 and PROMOTIONSCHEDULEENTRY.NEXTSCHOOLGRADELEVELID = ADVISORS.NEXTSCHOOLGRADELEVELID) or (GRADUATES = 1 and PROMOTIONSCHEDULE.NEXTSCHOOLGRADELEVELID = ADVISORS.NEXTSCHOOLGRADELEVELID))
    where SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID
        and PROMOTIONSCHEDULE.ID = @PROMOTIONSCHEDULEID
        and dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) = @PROMOTEFROM_ACADEMICYEARID
        and ((GRADUATES = 0 and PROMOTIONSCHEDULEENTRY.NEXTSCHOOLGRADELEVELID is not null) or (GRADUATES = 1 and PROMOTIONSCHEDULE.NEXTSCHOOLGRADELEVELID is not null))

    update SP set STARTDATE = ACADEMICYEAR.STARTDATE, ENDDATE = ACADEMICYEAR.ENDDATE
        from @STUDENTPROGRESSION SP 
        inner join dbo.SCHOOLGRADELEVEL on SP.NEXTSCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        inner join dbo.ACADEMICYEAR on SCHOOLGRADELEVEL.SCHOOLID = ACADEMICYEAR.SCHOOLID 
            and ((ACADEMICYEAR.ID = @PROMOTETO_ACADEMICYEARID AND SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID) or (ACADEMICYEAR.ID = @PROMOTETO_NEXTSCHOOL_ACADEMICYEARID and SCHOOLGRADELEVEL.SCHOOLID <> @SCHOOLID))

    delete from @STUDENTPROGRESSION where STARTDATE is null

    if @UPDATEEXISTING = 1
    begin
        update STUDENTPROGRESSION set SCHOOLGRADELEVELID = SP.NEXTSCHOOLGRADELEVELID, STARTDATE = SP.STARTDATE, ENDDATE = SP.ENDDATE, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
            from dbo.STUDENTPROGRESSION
                inner join @STUDENTPROGRESSION SP on STUDENTPROGRESSION.ENROLLMENTID = SP.ENROLLMENTID
                inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
                inner join dbo.SCHOOLGRADELEVEL SGL on SP.NEXTSCHOOLGRADELEVELID = SGL.ID
            where dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) in (@PROMOTETO_ACADEMICYEARID, @PROMOTETO_NEXTSCHOOL_ACADEMICYEARID)
                and (not exists(select * from dbo.CLASSMEETINGGROUP 
                                inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                                inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                                where STUDENTCOURSE.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID and STUDENTPROGRESSION.STARTDATE <= CLASSMEETINGGROUP.STARTDATE and
                                    STUDENTPROGRESSION.ENDDATE >= CLASSMEETINGGROUP.ENDDATE) or (SCHOOLGRADELEVEL.SCHOOLID = SGL.SCHOOLID))

        delete STUDENTADVISOR 
            from dbo.STUDENTADVISOR
                inner join dbo.STUDENTPROGRESSION on STUDENTADVISOR.STUDENTPROGRESSIONID = STUDENTPROGRESSION.ID
                inner join @STUDENTPROGRESSION SP on STUDENTPROGRESSION.ENROLLMENTID = SP.ENROLLMENTID
                inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
            where dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) in (@PROMOTETO_ACADEMICYEARID, @PROMOTETO_NEXTSCHOOL_ACADEMICYEARID)

        insert into dbo.STUDENTADVISOR (STUDENTPROGRESSIONID, FACULTYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select STUDENTPROGRESSION.ID, SP.ADVISORID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from dbo.STUDENTPROGRESSION
                inner join @STUDENTPROGRESSION SP on STUDENTPROGRESSION.ENROLLMENTID = SP.ENROLLMENTID
                inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
            where dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) in (@PROMOTETO_ACADEMICYEARID, @PROMOTETO_NEXTSCHOOL_ACADEMICYEARID)
                and SP.ADVISORID is not null
    end

    update @STUDENTPROGRESSION set NEXTSTUDENTPROGRESSIONID = newid()
    from @STUDENTPROGRESSION SP
    where not exists(select STUDENTPROGRESSION.ID 
                        from dbo.STUDENTPROGRESSION 
                        where ENROLLMENTID = SP.ENROLLMENTID and
                            (((SP.STARTDATE >= dbo.STUDENTPROGRESSION.[STARTDATE]) and (SP.STARTDATE <= dbo.STUDENTPROGRESSION.[ENDDATE])) 
                                or
                            ((SP.ENDDATE >= dbo.STUDENTPROGRESSION.[STARTDATE]) and (SP.ENDDATE <= dbo.STUDENTPROGRESSION.[ENDDATE]))
                                or
                            (SP.STARTDATE < dbo.STUDENTPROGRESSION.[STARTDATE] and SP.ENDDATE > STUDENTPROGRESSION.[ENDDATE])))

    insert into dbo.STUDENTPROGRESSION (ID, ENROLLMENTID, SCHOOLGRADELEVELID, STARTDATE, ENDDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select NEXTSTUDENTPROGRESSIONID, ENROLLMENTID, NEXTSCHOOLGRADELEVELID, STARTDATE, ENDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @STUDENTPROGRESSION
    where NEXTSTUDENTPROGRESSIONID is not null

    insert into dbo.STUDENTADVISOR (STUDENTPROGRESSIONID, FACULTYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select NEXTSTUDENTPROGRESSIONID, ADVISORID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @STUDENTPROGRESSION
    where NEXTSTUDENTPROGRESSIONID is not null and ADVISORID is not null

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0