UFN_SECURITY_FACULTY_GRANTED_FORM_FORSTUDENT

create function dbo.UFN_SECURITY_FACULTY_GRANTED_FORM_FORSTUDENT
(
    @FACULTYID uniqueidentifier,
    @DATFORMINSTANCEID uniqueidentifier,
    @STUDENTID uniqueidentifier
)
returns bit as
/*
    Returns true if the given user has permissions to the given form based on them being an advisor or a teacher of a 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
    declare @ISFACULTY bit;
    declare @ISADVISOR bit;
    declare @RECORDTYPE varchar(50);
    declare @CURRENTDATE datetime = getdate();

    --Do the Advisor check first since its sqls are cheaper.    

    set @ISADVISOR = dbo.UFN_FACULTY_ISADVISINGSTUDENT(@FACULTYID, @STUDENTID, @CURRENTDATE);
    if @ISADVISOR = 1
        begin
            --check the specific object - sql is duplicated in Advisor and Teacher sections. If the faculty

            --  ends up not being either, I don't want to waste the time running this sql, but it doesn't 

            --  seem worth creating yet another function for it.

            select @RECORDTYPE = RT.NAME 
            from dbo.DATAFORMINSTANCECATALOG IC
                inner join dbo.DATAFORMTEMPLATECATALOG TC on IC.DATAFORMTEMPLATECATALOGID = TC.ID
                inner join dbo.RECORDTYPE RT on TC.RECORDTYPEID = RT.ID
            where IC.ID = @DATFORMINSTANCEID;

            --If they have rights, we can return true. If they don't have rights, we can't return false since

            --  they could still have them as a Teacher.

            if upper(@RECORDTYPE) <> 'TEST SCORE'
                return 1;
        end

    --Haven't given rights yet, so check students faculty is currently teaching.

    set @ISFACULTY = dbo.UFN_FACULTY_ISTEACHINGSTUDENT(@FACULTYID, @STUDENTID, @CURRENTDATE);
    if @ISFACULTY = 1
        begin
            --check the specific object - sql is duplicated in Advisor and Teacher sections. If the faculty

            --  ends up not being either, I don't want to waste the time running this sql, but it doesn't 

            --  seem worth creating yet another function for it.

            select @RECORDTYPE = RT.NAME 
            from dbo.DATAFORMINSTANCECATALOG IC
                inner join dbo.DATAFORMTEMPLATECATALOG TC on IC.DATAFORMTEMPLATECATALOGID = TC.ID
                inner join dbo.RECORDTYPE RT on TC.RECORDTYPEID = RT.ID
            where IC.ID = @DATFORMINSTANCEID;

            if upper(@RECORDTYPE) = 'TEST SCORE'
                return 0;
            else
                return 1;
        end

    return 0;

end