USE [SDS_DevSchoolDistrict]
GO


--SELECT * FROM EvaluationCriteriaByEducatorAndTask()

GO

-- =================================================================================
-- Copyright:   School Data Solutions, LLC
-- =================================================================================
ALTER FUNCTION [dbo].[EvaluationCriteriaByEducatorAndTask]()
RETURNS @OutputTable TABLE
(       -------------------------------------
        EducatorId              int,
        EducatorFirstName       varchar(100),
        EducatorLastName        varchar(100),
        EducatorPositionId      int,
        EducatorPosition        varchar(100),
        EducatorPositionLevelId int,
        EducatorPositionLevel   varchar(100),
        -------------------------------------
        TeachingTrackId                         ElementId NULL,
        TeachingTrack                           ElementName,
        TeachingTrackOrdinal                    Ordinal NULL,
        TeachingTrackIsActive                   ActiveState,
        -------------------------------------------
        TeachingEvaluationCriterionId           ElementId NULL,
        TeachingEvaluationCriterion             varchar(300),
        TeachingEvaluationCriterionAbbreviation Abbreviation,
        TeachingEvaluationCriterionDescription  varchar(2000),
        ParentTeachingEvaluationCriterionId     SelfReference,
        TeachingEvaluationCriterionOrdinalByParent Ordinal NULL,
        -------------------------------------------
        TeachingPerformanceLevelDescriptionId   ElementId NULL,
        TeachingPerformanceLevelDescription     LongDescription,
        -------------------------------------------
        TeachingPerformanceLevelId              ElementReference,
        TeachingPerformanceLevel                ElementName,
        TeachingPerformanceLevelOrdinal         Ordinal NULL,
        TeachingPerformanceLevelIsSelected      bit NULL
)       -------------------------------------------
AS
BEGIN   -- =================================================================================


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

        ---------------------------------------------
        --
        ---------------------------------------------
        DECLARE @Educators TABLE
        (       -------------------------------------------
                EducatorId              int,
                EducatorFirstName       varchar(100),
                EducatorLastName        varchar(100),
                EducatorPositionId      int,
                EducatorPosition        varchar(100),
                EducatorPositionLevelId int,
                EducatorPositionLevel   varchar(100),
                -------------------------------------------
                PRIMARY KEY (EducatorId)
        )
        ---------------------------------------------
        INSERT  @Educators
        (       -------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel
        )       -------------------------------------------
        SELECT  TOP 1
                T.TeacherId,
                P.FirstName,
                P.LastName,
                1,
                'New Teacher',
                3,
                '3rd Year'
        FROM    Teachers T
        JOIN    Persons P
          ON    T.TeacherId = P.PersonId
        ---------------------------------------------
        --SELECT * FROM @AssessmentTestPerformanceLevelsByDefinitions
        ---------------------------------------------







        ----------------------------------------------------
        --
        ----------------------------------------------------
        INSERT  @OutputTable
        (       ----------------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------
                TeachingTrackId,
                TeachingTrack,
                TeachingTrackOrdinal,
                TeachingTrackIsActive,
                -------------------------------------------
                TeachingEvaluationCriterionId,
                TeachingEvaluationCriterion,
                TeachingEvaluationCriterionAbbreviation,
                TeachingEvaluationCriterionDescription,
                ParentTeachingEvaluationCriterionId,
                TeachingEvaluationCriterionOrdinalByParent,
                -------------------------------------------
                TeachingPerformanceLevelDescriptionId,
                TeachingPerformanceLevelDescription,
                -------------------------------------------
                TeachingPerformanceLevelId,
                TeachingPerformanceLevel,
                TeachingPerformanceLevelOrdinal,
                TeachingPerformanceLevelIsSelected
        )       -------------------------------------------
        SELECT  E.EducatorId,
                E.EducatorFirstName,
                E.EducatorLastName,
                E.EducatorPositionId,
                E.EducatorPosition,
                E.EducatorPositionLevelId,
                E.EducatorPositionLevel,
                -------------------------------------
                TT.TeachingTrackId,
                TT.TeachingTrack,
                TT.TeachingTrackIsActive,
                TT.TeachingTrackOrdinal,
                -------------------------------------------
                C.TeachingEvaluationCriterionId,
                C.TeachingEvaluationCriterion,
                C.TeachingEvaluationCriterionAbbreviation,
                C.TeachingEvaluationCriterionDescription,
                C.ParentTeachingEvaluationCriterionId,
                C.TeachingEvaluationCriterionOrdinalByParent,
                -------------------------------------------
                D.TeachingPerformanceLevelDescriptionId,
                D.TeachingPerformanceLevelDescription,
                -------------------------------------------
                TPL.TeachingPerformanceLevelId,
                TPL.TeachingPerformanceLevel,
                TPL.TeachingPerformanceLevelOrdinal,
                CASE
                    WHEN(TPL.TeachingPerformanceLevelId IS NULL) THEN NULL
                    WHEN(TPL.TeachingPerformanceLevelId=3) THEN 1
                    ELSE 0
                END
                -------------------------------------------
        FROM    @Educators E
        CROSS
        JOIN    TeachingTracks TT
        JOIN    TeachingEvaluationCriteria C
          ON    TT.TeachingTrackId = C.TeachingTrackId
         AND    TT.TeachingTrackId = 1
        LEFT
        JOIN    TeachingPerformanceLevelDescriptions D
          ON    C.TeachingEvaluationCriterionId = D.TeachingEvaluationCriterionId
        LEFT
        JOIN    TeachingPerformanceLevels TPL
          ON    TPL.TeachingPerformanceLevelId = D.TeachingPerformanceLevelId

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