USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[SpotlightabilityBySpotlighterAndSpotlightee]    Script Date: 02/09/2011 18:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
*/
-- =================================================================================
-- Copyright:   School Data Solutions, LLC
-- =================================================================================
ALTER FUNCTION [dbo].[SpotlightabilityBySpotlighterAndSpotlightee]
(   @ApplicationUserId      int,
    @SpotlighteePersonId    int,
    @SchoolYearId           int = NULL
)
RETURNS @OutputTable TABLE
(       StudentIsSpotlightable bit NULL,
        TeacherIsSpotlightable bit NULL,
        PrincipalIsSpotlightable bit NULL
)
AS
BEGIN   -- =========================================================================


        ----------------------------------------------------
        -- If no School-Year passed, assume current school year
        ----------------------------------------------------
        IF(@SchoolYearId IS NULL) SET @SchoolYearId = dbo.SchoolYearIdValue(GetDate())
        ----------------------------------------------------




        --------------------------------------------------
        --
        --------------------------------------------------
        DECLARE @SpotlighterPersonId int
        --------------------------------------------------
        SELECT  @SpotlighterPersonId = PersonId
        FROM    ApplicationUsers U
        WHERE   U.ApplicationUserId = @ApplicationUserId
        --------------------------------------------------






        --------------------------------------------------
        -- Determine whether the Spotlight-ee is a Student
        --------------------------------------------------
        DECLARE @SpotlighteeIsStudent bit
        --------------------------------------------------
        IF(EXISTS(SELECT 1 FROM Students WHERE StudentId = @SpotlighteePersonId))
            SET @SpotlighteeIsStudent = 1
        --------------------------------------------------


        --------------------------------------------------
        -- Determine whether the Spotlight-ee is a Teacher
        --------------------------------------------------
        DECLARE @SpotlighteeIsTeacher bit
        --------------------------------------------------
        IF(EXISTS(SELECT 1 FROM Teachers WHERE TeacherId = @SpotlighteePersonId))
            SET @SpotlighteeIsTeacher = 1
        --------------------------------------------------

        --------------------------------------------------
        -- Determine whether the Spotlight-ee is a Principal
        --------------------------------------------------
        DECLARE @SpotlighteeIsPrincipal bit
        --------------------------------------------------
        IF(EXISTS(SELECT 1 FROM Principals WHERE PrincipalId = @SpotlighteePersonId))
            SET @SpotlighteeIsPrincipal = 1
        --------------------------------------------------






        --------------------------------------------------
        -- Determine whether the Spotlight-er is an Administrator
        --------------------------------------------------
        DECLARE @SpotlighterIsAdministrator bit
        --------------------------------------------------
        IF(EXISTS(SELECT 1 FROM Administrators WHERE AdministratorId = @SpotlighterPersonId))
            SET @SpotlighterIsAdministrator = 1
        --------------------------------------------------


        --------------------------------------------------
        -- Determine whether the Spotlight-er is a Teacher
        --------------------------------------------------
        DECLARE @SpotlighterIsTeacher bit
        --------------------------------------------------
        IF(EXISTS(SELECT 1 FROM Teachers WHERE TeacherId = @SpotlighterPersonId))
            SET @SpotlighterIsTeacher = 1
        --------------------------------------------------

        --------------------------------------------------
        -- Determine whether the Spotlight-er is a Principal
        --------------------------------------------------
        DECLARE @SpotlighterIsPrincipal bit
        --------------------------------------------------
        IF(EXISTS(SELECT 1 FROM Principals WHERE PrincipalId = @SpotlighterPersonId))
            SET @SpotlighterIsPrincipal = 1
        --------------------------------------------------



        DECLARE @StudentIsSpotlightable int
        DECLARE @TeacherIsSpotlightable int
        DECLARE @PrincipalIsSpotlightable int




        IF(@SpotlighterIsAdministrator = 1)
        BEGIN   -----------------------------------------------

                IF(@SpotlighteeIsStudent = 1) SET @StudentIsSpotlightable = 1
                IF(@SpotlighteeIsTeacher = 1) SET @TeacherIsSpotlightable = 1
                IF(@SpotlighteeIsPrincipal = 1) SET @PrincipalIsSpotlightable = 1

        END     -----------------------------------------------
        ELSE IF(@SpotlighteeIsPrincipal = 1)
        BEGIN

                IF(@SpotlighteeIsStudent = 1) SET @StudentIsSpotlightable = 1
                IF(@SpotlighteeIsTeacher = 1) SET @TeacherIsSpotlightable = 1
                IF(@SpotlighteeIsPrincipal = 1) SET @PrincipalIsSpotlightable = 0

        END     -----------------------------------------------
        ELSE IF(@SpotlighterIsTeacher = 1)
        BEGIN

                IF(@SpotlighteeIsTeacher = 1) SET @TeacherIsSpotlightable = 1
                IF(@SpotlighteeIsPrincipal = 1) SET @PrincipalIsSpotlightable = 0
                IF(@SpotlighteeIsStudent = 1)
                BEGIN
                        SET @StudentIsSpotlightable = 0

                        IF(EXISTS(  SELECT  1
                                    FROM    ClassTeachingAssignments A
                                    JOIN    Classes C
                                      ON    A.TeacherId = @SpotlighterPersonId
                                     AND    A.ClassId = C.ClassId
                                     AND    C.SchoolYearId = @SchoolYearId
                                    JOIN    StudentClassEnrollments E
                                      ON    C.ClassId = C.ClassId
                                     AND    E.StudentId = @SpotlighteePersonId
                        ))
                        BEGIN
                                SET @StudentIsSpotlightable = 1
                        END

                END


        END     -----------------------------------------------




        INSERT  @OutputTable(
                StudentIsSpotlightable,
                TeacherIsSpotlightable,
                PrincipalIsSpotlightable
        )
        VALUES  (
                @StudentIsSpotlightable,
                @TeacherIsSpotlightable,
                @PrincipalIsSpotlightable
        )

RETURN
END     -- =========================================================================