USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[EvaluationTasksByEvaluatorAndEducator]    Script Date: 02/11/2011 14:28:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================================
-- Copyright:   School Data Solutions, LLC
-- =================================================================================
/*
    SELECT * FROM EvaluationTasksByEvaluatorAndEducator(DEFAULT, DEFAULT, DEFAULT)
    SELECT * FROM Educators
    SELECT * FROM Evaluators
*/
ALTER FUNCTION [dbo].[EvaluationTasksByEvaluatorAndEducator]
(
    @EvaluatorId int = 23215,
    @EducatorId int = 23127,
    @SchoolYearId   int = NULL
)/*
DECLARE @EvaluatorId int    SET @EvaluatorId = 23215
DECLARE @EducatorId int     SET @EducatorId = 23127
DECLARE @SchoolYearId int
DECLARE @OutputTable TABLE */
RETURNS @OutputTable TABLE
(       -------------------------------------
        EvaluatorId             int,
        EvaluatorFirstName      varchar(100),
        EvaluatorLastName       varchar(100),
        -------------------------------------------
        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,
        -------------------------------------
        EducatorEvaluationTaskId        int,
        EducatorEvaluationTask                  varchar(100),
        EducatorEvaluationTaskDueDate           XsltDate,
        EducatorEvaluationTaskIsMeeting         bit,
        EducatorEvaluationTaskOrdinal           int,
        EducatorEvaluationTaskStatus            varchar(100) -- Late, Required, Completed, In-Process
)       -------------------------------------
AS
BEGIN   -- =================================================================================



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


        ---------------------------------------------
        --
        ---------------------------------------------
        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  P.PersonId,
                P.FirstName,
                P.LastName,
                EP.EducatorPositionId,
                EP.EducatorPosition,
                L.EducatorPositionLevelId,
                L.EducatorPositionLevel
        FROM    EducatorEvaluationYears Y
        JOIN    Persons P
          ON    Y.EducatorId = @EducatorId
         AND    Y.SchoolYearId = @SchoolYearId
         AND    Y.EducatorId = P.PersonId
        JOIN    EducatorPositionLevels L
          ON    Y.EducatorPositionLevelId = L.EducatorPositionLevelId
        JOIN    EducatorPositions EP
          ON    L.EducatorPositionId = EP.EducatorPositionId
        WHERE   Y .EducatorId = @EducatorId
        ---------------------------------------------
        --SELECT * FROM @Educators
        ---------------------------------------------




        ---------------------------------------------
        --
        ---------------------------------------------
        DECLARE @EducatorPositionLevelId int
        ---------------------------------------------
        SELECT  @EducatorPositionLevelId = EducatorPositionLevelId
        FROM    @Educators
        ---------------------------------------------








        ---------------------------------------------
        --
        ---------------------------------------------
        DECLARE @Evaluators TABLE
        (       -------------------------------------------
                EvaluatorId             int,
                EvaluatorFirstName      varchar(100),
                EvaluatorLastName       varchar(100),
                -------------------------------------------
                PRIMARY KEY (EvaluatorId)
        )
        ---------------------------------------------
        INSERT  @Evaluators
        (       -------------------------------------------
                EvaluatorId,
                EvaluatorFirstName,
                EvaluatorLastName
        )       -------------------------------------------
        SELECT  P.PersonId,
                P.FirstName,
                P.LastName
        FROM    EducatorEvaluationYears Y
        JOIN    Persons P
          ON    Y.EvaluatorId = @EvaluatorId
         AND    Y.SchoolYearId = @SchoolYearId
         AND    Y.EvaluatorId = P.PersonId
         AND    Y.EducatorId = @EducatorId
        ---------------------------------------------
        --SELECT * FROM @Evaluators
        ---------------------------------------------








        ---------------------------------------------
        DECLARE @EducatorTasks TABLE
        (       -------------------------------------------
                EducatorEvaluationTaskId    int,
                EducatorEvaluationTask          varchar(100),
                EducatorEvaluationTaskDueDate   XsltDate,
                EducatorEvaluationTaskIsMeeting bit,
                EducatorEvaluationTaskCategoryId int,
                EducatorEvaluationTaskCategory  varchar(100),
                EducatorEvaluationTaskStatus    varchar(100)
                -------------------------------------------
                PRIMARY KEY (EducatorEvaluationTaskId)
        )
        ---------------------------------------------
        INSERT  @EducatorTasks
        (       -------------------------------------------
                EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory,
                EducatorEvaluationTaskStatus
        )       -------------------------------------------
        SELECT  EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                dbo.XsltDate(EducatorEvaluationTaskDueDate),
                EducatorEvaluationTaskIsMeeting,
                C.EducatorEvaluationTaskCategoryId,
                C.EducatorEvaluationTaskCategory,
                EducatorEvaluationTaskStatus
        FROM    EducatorEvaluationTasks T
        JOIN    dbo.EducatorEvaluationTaskCategories C
          ON    T.EducatorEvaluationTaskCategoryId = C.EducatorEvaluationTaskCategoryId
        JOIN    EducatorEvaluationTaskStatuses S
          ON    (DATEPART(day, EducatorEvaluationTaskDueDate) % 4) + 1 = S.EducatorEvaluationTaskStatusId
        WHERE   EducatorPositionLevelId = @EducatorPositionLevelId
          AND   SchoolYearId = @SchoolYearId
        ---------------------------------------------
        --SELECT * FROM @EducatorTasks
        ---------------------------------------------




        ----------------------------------------------------
        --
        ----------------------------------------------------
        INSERT @OutputTable
        (       -------------------------------------------
                EvaluatorId,
                EvaluatorFirstName,
                EvaluatorLastName,
                -------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------------
                EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskStatus,
                -------------------------------------------
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory
        )       -------------------------------------------
        SELECT  EvaluatorId,
                EvaluatorFirstName,
                EvaluatorLastName,
                -------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------------
                EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskStatus,
                -------------------------------------------
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory
        FROM    @Educators
        CROSS
        JOIN    @Evaluators
        CROSS
        JOIN    @EducatorTasks
        ----------------------------------------------------


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