USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[CurrentStudentsForStudentGroupAssignment]    Script Date: 09/02/2011 18:40:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[CurrentStudentsForStudentGroupAssignment](
    @PersonId int, @StudentGroupId int = NULL
)
--DECLARE @OutputTable TABLE
RETURNS @OutputTable TABLE
(       ------------------------------------------
        StudentId           int NULL,
        Student             varchar(300) NULL,
        Ssid                varchar(20) NULL,

        LastName            Name NOT NULL,
        FirstName           Name NULL,
        MiddleName          Name NULL,
        NickName            Name NULL,
        BirthDate           BirthDate NULL,
        Gender              GenderAbbreviation NULL,

        --SchoolYear        varchar(11) NULL,
        CurrentGradeLevel   varchar(100) NULL,
        CurrentSchool       varchar(100) NULL/*,

        StudentIsEll        bit NULL,
        IsSpecialEducation  bit NULL,
        IsHighlyCapabale    bit NULL,
        IsTitleOneLap       bit NULL*/
)       ------------------------------------------
AS
BEGIN
        ----------------------------------------------------
        -- Calcualte default for SchoolYear
        ----------------------------------------------------
        DECLARE @SchoolYearId int
        SET @SchoolYearId = dbo.SchoolYearIdValue(GETDATE())
        ----------------------------------------------------

        INSERT  @OutputTable
        (       StudentId,
                Student,
                Ssid,

                LastName,
                FirstName,
                MiddleName,
                NickName,
                BirthDate,
                Gender,

            --  SchoolYear,
                CurrentGradeLevel,
                CurrentSchool/*,

                StudentIsEll,
                IsSpecialEducation,
                IsHighlyCapabale,
                IsTitleOneLap*/
        )
        SELECT  S.StudentId,
                S.Student,
                S.Ssid,
                P.LastName,
                P.FirstName,
                P.MiddleName,
                P.NickName,
                P.BirthDate,
                P.Gender,
            --  SY.SchoolYear,
                GL.GradeLevel,
                Sc.School/*,
                0 AS StudentIsEll,
                0 AS IsSpecialEducation,
                0 AS IsHighlyCapabale,
                0 AS IsTitleOneLap*/
        FROM    SpotlightableStudentsByPerson(@PersonId, DEFAULT, DEFAULT) SS
        JOIN    Students S
          ON    SS.StudentId = S.StudentId
        JOIN    Persons  P
          ON    S.StudentId = P.PersonId
        JOIN    StudentSchoolYears SSY
          ON    S.StudentId = SSY.StudentId
         AND    SSY.SchoolYearId = @SchoolYearId
        JOIN    SchoolYears SY
          ON    SSY.SchoolYearId = SY.SchoolYearId
        JOIN    Schools Sc
          ON    SSY.PredominantSchoolId = Sc.SchoolId
        JOIN    GradeLevels GL
          ON    SSY.GradeLevelId = GL.GradeLevelId
        WHERE   S.StudentId NOT IN (
                        SELECT  S_G.StudentId
                        FROM    Students_Groups S_G
                        WHERE   S_G.StudentGroupId = @StudentGroupId
                )


        ORDER
           BY   P.LastName
        ----------------------------------
        --SELECT * FROM @OutputTable
        ----------------------------------



    RETURN
END