USE [SDS_DevSchoolDistrict]
GO

ALTER FUNCTION [dbo].[EvaluationTasksByEducatorEvaluationYear]
(@EducatorEvaluationYearId int = 1)
--DECLARE @EducatorEvaluationYearId int SET @EducatorEvaluationYearId = 1
--DECLARE @OutputTable TABLE
RETURNS @OutputTable TABLE
(       -------------------------------------
        EducatorEvaluationYearId    int,
        -------------------------------------
        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
        EducatorEvaluationTaskMayBeCompletedByEducator  bit,
        EducatorEvaluationTaskMayBeCompletedByEvaluator bit,
        -------------------------------------
        EducatorEvaluationCompletedTaskId               [dbo].[ElementId] NULL,
        EducatorEvaluationTaskCompletionDate            [date] NULL,
        EducatorEvaluationTaskScheduledDate             [date] NULL,
        EducatorEvaluationTaskScheduledTime             [time] NULL,
        EducatorEvaluationTaskScheduledLocation         varchar(500) NULL,
        EducatorEvaluationCompletedTaskNotes            varchar(max) NULL,
        EducatorEvaluationTaskStatusId                  [dbo].[ElementReference] NULL
)       -------------------------------------
AS
BEGIN   -- =================================================================================



        ---------------------------------------------
        --
        ---------------------------------------------
        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.EducatorEvaluationYearId = @EducatorEvaluationYearId
         AND    Y.EducatorId = P.PersonId
        JOIN    EducatorPositionLevels L
          ON    Y.EducatorPositionLevelId = L.EducatorPositionLevelId
        JOIN    EducatorPositions EP
          ON    L.EducatorPositionId = EP.EducatorPositionId
        ---------------------------------------------
        -- SELECT * FROM @Educators
        ---------------------------------------------




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




        ---------------------------------------------
        --
        ---------------------------------------------
        DECLARE @SchoolYearId int
        ---------------------------------------------
        SELECT  @SchoolYearId =Y. SchoolYearId
        FROM    EducatorEvaluationYears Y
        WHERE   Y.EducatorEvaluationYearId = @EducatorEvaluationYearId
        ---------------------------------------------
        -- SELECT  @SchoolYearId AS '@SchoolYearId'
        ---------------------------------------------






        ---------------------------------------------
        --
        ---------------------------------------------
        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.EducatorEvaluationYearId = @EducatorEvaluationYearId
         AND    Y.EvaluatorId = P.PersonId
        ---------------------------------------------
        -- SELECT * FROM @Evaluators
        ---------------------------------------------





        DECLARE @Today date
        SET @Today = GETDATE()





        ---------------------------------------------
        DECLARE @EducatorTasks TABLE
        (       -------------------------------------------
                EducatorEvaluationTaskId    int,
                EducatorEvaluationTask          varchar(100),
                EducatorEvaluationTaskDueDate   XsltDate,
                EducatorEvaluationTaskIsMeeting bit,
                EducatorEvaluationTaskCategoryId int,
                EducatorEvaluationTaskCategory  varchar(100),
                EducatorEvaluationTaskStatus    varchar(100),
                EducatorEvaluationTaskMayBeCompletedByEducator      bit,
                EducatorEvaluationTaskMayBeCompletedByEvaluator bit,
                -------------------------------------
                EducatorEvaluationCompletedTaskId               [dbo].[ElementId] NULL,
                EducatorEvaluationTaskCompletionDate            [date] NULL,
                EducatorEvaluationTaskScheduledDate             [date] NULL,
                EducatorEvaluationTaskScheduledTime             [time] NULL,
                EducatorEvaluationTaskScheduledLocation         varchar(500) NULL,
                EducatorEvaluationCompletedTaskNotes            varchar(max) NULL,
                EducatorEvaluationYearId                        [dbo].[ElementReference] NULL,
                EducatorEvaluationTaskStatusId                  [dbo].[ElementReference] NULL
                -------------------------------------------
                PRIMARY KEY (EducatorEvaluationTaskId)
        )
        ---------------------------------------------
        INSERT  @EducatorTasks
        (       -------------------------------------------
                EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory,
                EducatorEvaluationTaskStatus,
                EducatorEvaluationTaskMayBeCompletedByEducator,
                EducatorEvaluationTaskMayBeCompletedByEvaluator,
                -------------------------------------------
                EducatorEvaluationCompletedTaskId,
                EducatorEvaluationTaskCompletionDate,
                EducatorEvaluationTaskScheduledDate,
                EducatorEvaluationTaskScheduledTime,
                EducatorEvaluationTaskScheduledLocation,
                EducatorEvaluationCompletedTaskNotes,
                EducatorEvaluationYearId,
                EducatorEvaluationTaskStatusId
        )       -------------------------------------------
        SELECT  T.EducatorEvaluationTaskId,
                T.EducatorEvaluationTask,
                dbo.XsltDate(T.EducatorEvaluationTaskDueDate),
                T.EducatorEvaluationTaskIsMeeting,
                C.EducatorEvaluationTaskCategoryId,
                C.EducatorEvaluationTaskCategory,
                CASE
                    WHEN(CT.EducatorEvaluationTaskCompletionDate IS NOT NULL) THEN 'Completed'
                    WHEN(@Today > T.EducatorEvaluationTaskDueDate) THEN 'Late'
                    WHEN(CT.EducatorEvaluationCompletedTaskId IS NOT NULL) THEN 'In-Process'
                    ELSE 'Required'
                END ,
                T.EducatorEvaluationTaskMayBeCompletedByEducator,
                T.EducatorEvaluationTaskMayBeCompletedByEvaluator,
                -------------------------------------------
                CT.EducatorEvaluationCompletedTaskId,
                CT.EducatorEvaluationTaskCompletionDate,
                CT.EducatorEvaluationTaskScheduledDate,
                CT.EducatorEvaluationTaskScheduledTime,
                CT.EducatorEvaluationTaskScheduledLocation,
                CT.EducatorEvaluationCompletedTaskNotes,
                CT.EducatorEvaluationYearId,
                CT.EducatorEvaluationTaskStatusId
        FROM    EducatorEvaluationTasks T
        LEFT
        JOIN    dbo.EducatorEvaluationTaskCategories C
          ON    T.EducatorEvaluationTaskCategoryId = C.EducatorEvaluationTaskCategoryId
        LEFT
        JOIN    EducatorEvaluationCompletedTasks CT
          ON    T.EducatorEvaluationTaskId = CT.EducatorEvaluationTaskId
         AND    CT.EducatorEvaluationYearId = @EducatorEvaluationYearId
        WHERE   EducatorPositionLevelId = @EducatorPositionLevelId
          AND   SchoolYearId = @SchoolYearId
        ---------------------------------------------
        -- SELECT * FROM @EducatorTasks
        ---------------------------------------------




        ----------------------------------------------------
        --
        ----------------------------------------------------
        INSERT @OutputTable
        (       -------------------------------------------
                EducatorEvaluationYearId,
                -------------------------------------------
                EvaluatorId,
                EvaluatorFirstName,
                EvaluatorLastName,
                -------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------------
                EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskStatus,
                EducatorEvaluationTaskMayBeCompletedByEducator,
                EducatorEvaluationTaskMayBeCompletedByEvaluator,
                -------------------------------------------
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory,
                -------------------------------------------
                EducatorEvaluationCompletedTaskId,
                EducatorEvaluationTaskCompletionDate,
                EducatorEvaluationTaskScheduledDate,
                EducatorEvaluationTaskScheduledTime,
                EducatorEvaluationTaskScheduledLocation,
                EducatorEvaluationCompletedTaskNotes,
                EducatorEvaluationTaskStatusId
        )       -------------------------------------------
        SELECT  @EducatorEvaluationYearId,
                -------------------------------------------
                EvaluatorId,
                EvaluatorFirstName,
                EvaluatorLastName,
                -------------------------------------------
                EducatorId,
                EducatorFirstName,
                EducatorLastName,
                EducatorPositionId,
                EducatorPosition,
                EducatorPositionLevelId,
                EducatorPositionLevel,
                -------------------------------------------
                EducatorEvaluationTaskId,
                EducatorEvaluationTask,
                EducatorEvaluationTaskDueDate,
                EducatorEvaluationTaskIsMeeting,
                EducatorEvaluationTaskStatus,
                EducatorEvaluationTaskMayBeCompletedByEducator,
                EducatorEvaluationTaskMayBeCompletedByEvaluator,
                -------------------------------------------
                EducatorEvaluationTaskCategoryId,
                EducatorEvaluationTaskCategory,
                -------------------------------------------
                EducatorEvaluationCompletedTaskId,
                EducatorEvaluationTaskCompletionDate,
                EducatorEvaluationTaskScheduledDate,
                EducatorEvaluationTaskScheduledTime,
                EducatorEvaluationTaskScheduledLocation,
                EducatorEvaluationCompletedTaskNotes,
                EducatorEvaluationTaskStatusId
        FROM    @Educators
        CROSS
        JOIN    @Evaluators
        CROSS
        JOIN    @EducatorTasks
        ----------------------------------------------------
        -- SELECT * FROM @EducatorTasks
        ---------------------------------------------


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