UFN_SECURITY_FACULTY_GRANTED_STUDENTS

create function dbo.UFN_SECURITY_FACULTY_GRANTED_STUDENTS
(
    @FACULTYID uniqueidentifier,
    @SPECIFIEDDATE date = null,
    @INCLUDEADVISEES bit = 1,
    @INCLUDESTUDENTSINCLASSES bit = 1
)
returns @IDS TABLE (ID uniqueidentifier) as
/*
    Returns a row for every student that the given user has permissions to based on them being an advisor or a teacher of that student.

    This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.FacultyStudentRecordSecurityService
      class which implements the RecordSecurity service for Faculty/Student record security.

    As such, it assumes that a check for the following occurs outside this function:
      - if the user is ISSYSADMIN 
      - if the AppUser is linked to a Faculty record
      - if the Faculty has rights to all Students
*/
begin
    --Students Faculty advises

    if @INCLUDEADVISEES <> 0
        insert @IDs        
        select 
            EH.CONSTITUENTID
        from dbo.STUDENTADVISOR SA
            inner join dbo.STUDENTPROGRESSION SP on SA.STUDENTPROGRESSIONID = SP.ID
            inner join dbo.EDUCATIONALHISTORY EH on SP.ENROLLMENTID = EH.ID
        where SA.FACULTYID = @FACULTYID
            and ((@SPECIFIEDDATE between SP.STARTDATE and SP.ENDDATE) or (@SPECIFIEDDATE is null));

    --Students Faculty teaches

    if @INCLUDESTUDENTSINCLASSES <> 0
        insert @IDs
        select
            SC.STUDENTID
        from dbo.FACULTYCLASSMEETINGGROUP FCMG
            inner join dbo.FACULTYCOURSE FC on FCMG.FACULTYCOURSEID = FC.ID
            inner join dbo.STUDENTCLASSMEETINGGROUP SCMG on FCMG.CLASSMEETINGGROUPID = SCMG.CLASSMEETINGGROUPID
            inner join dbo.STUDENTCOURSE SC on SCMG.STUDENTCOURSEID = SC.ID
            inner join dbo.CLASSMEETINGGROUP CMG on FCMG.CLASSMEETINGGROUPID = CMG.ID and SCMG.CLASSMEETINGGROUPID = CMG.ID
        where FC.FACULTYID = @FACULTYID
            and ((@SPECIFIEDDATE between CMG.STARTDATE and CMG.ENDDATE) or (@SPECIFIEDDATE is null));

    return;
end