USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[AdministratorData]    Script Date: 02/07/2011 14:50:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================================
-- Copyright:   School Data Solutions, LLC
-- =================================================================================
/*
    SELECT * FROM EducatorsByEvaluator(1, NULL)
*/
ALTER FUNCTION [dbo].[EducatorsByEvaluator]
(
    @EvaluatorId int,
    @SchoolYearId   int = NULL
)
RETURNS @OutputTable TABLE
(       -------------------------------------
        EducatorId              int,
        EducatorFirstName       varchar(100),
        EducatorLastName        varchar(100),
        EducatorPositionId      int,
        EducatorPosition        varchar(100),
        EducatorPositionLevelId int,
        EducatorPositionLevel   varchar(100),
        -------------------------------------
        EducatorEvaluationTaskCategoryId        int,
        EducatorEvaluationTaskCategory          varchar(100),
        EducatorEvaluationTaskCategoryOrdinal   int,
        -------------------------------------
        EducatorEvaluationRequiredTaskId int,
        EducatorEvaluationTask          varchar(100),
        EducatorEvaluationTaskDueDate   XsltDate,
        EducatorEvaluationTaskIsMeeting bit,
        EducatorEvaluationTaskOrdinal   int,
        EducatorEvaluationTaskStatus    varchar(100) -- Late, Required, Completed, In-Process
)       -------------------------------------
AS
BEGIN   -- =================================================================================



--SELECT * FROM EducatorEvaluationTaskCategories
--SELECT * FROM EducatorEvaluationTaskStatuses


        ---------------------------------------------
        --
        ---------------------------------------------
        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 10
                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
        ---------------------------------------------




        ---------------------------------------------
        DECLARE @EducatorTasks TABLE
        (       -------------------------------------------
                EducatorEvaluationRequiredTaskId    int,
                EducatorEvaluationTask          varchar(100),
                EducatorEvaluationTaskDueDate   XsltDate,
                EducatorEvaluationTaskIsMeeting bit,
                EducatorEvaluationTaskCategoryId int,
                EducatorEvaluationTaskCategory  varchar(100),
                -------------------------------------------
                PRIMARY KEY (EducatorEvaluationRequiredTaskId)
        )
        ---------------------------------------------
        INSERT  @EducatorTasks
        (       -------------------------------------------
                EducatorEvaluationRequiredTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory
        )       -------------------------------------------
        SELECT  TeacherEvaluationRequiredTaskId,
                TeacherEvaluationTask,
                dbo.XsltDate(TeacherEvaluationTaskDueDate),
                TeacherEvaluationTaskIsMeeting,
                C.EducatorEvaluationTaskCategoryId,
                C.EducatorEvaluationTaskCategory
                --CASE
        FROM    TeacherEvaluationRequiredTasks T
        JOIN    dbo.EducatorEvaluationTaskCategories C
          ON    T.EducatorEvaluationTaskCategoryId = C.EducatorEvaluationTaskCategoryId
        WHERE   TeachingTrackLevelId = 3
          AND   SchoolYearId = 11
        ---------------------------------------------




        ----------------------------------------------------
        --
        ----------------------------------------------------
        INSERT @OutputTable
        (       -------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------------
                EducatorEvaluationRequiredTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory,
                EducatorEvaluationTaskStatus
        )       -------------------------------------------
        SELECT  EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------------
                EducatorEvaluationRequiredTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory,
                S.EducatorEvaluationTaskStatus
        FROM    @Educators E
        CROSS
        JOIN    @EducatorTasks T
        JOIN    EducatorEvaluationTaskStatuses S
          ON    (((T.EducatorEvaluationRequiredTaskId +  EducatorEvaluationTaskCategoryId + EducatorId) % 4) + 1)
                = S.EducatorEvaluationTaskStatusId
        ----------------------------------------------------
        --SELECT * FROM @OutputTable


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