USE [SDS_DevSchoolDistrict]
GO



-- =================================================================================
-- Copyright:   School Data Solutions, LLC
-- =================================================================================
ALTER FUNCTION [dbo].[PrintableStudentSpotlightData]
(
    @StudentId          RoleReference = 17024, -- 17366,--4307,-- NULL,
    @ApplicationUserId  int = NULL
)
--DECLARE @StudentId        RoleId      SET @StudentId = 22792
--DECLARE @OutputTable TABLE
RETURNS @OutputTable TABLE
(       ---------------------------------------------
        -- Student Attributes
        ---------------------------------------------
        StudentId                       PersonId,
        Student                         varchar(300),
        ---------------------------------------------
        -- Person Attributes
        ---------------------------------------------
        FirstName                       Name,
        NickName                        Name,
        MiddleName                      Name,
        LastName                        Name,
        BirthDate                       BirthDate,
        Gender                          GenderAbbreviation,
        SocialSecurityNumber            SocialSecurityNumber,
        SocialSecurityNumberChecksum    bigint,
        ContactInfoIsConfidential       YesNo,
        ImagePath                       FilePath,
        ---------------------------------------------
        -- Person Address Attributes
        ---------------------------------------------
        AddressId                       AddressReference,
        Address                         FullAddress,
        StreetAddress                   StreetAddress,
        AddressLine2                    StreetAddress,
        City                            City,
        State                           StateAbbreviation,
        ZipCode                         ZipCode,
        AddressTypeId                   TypeReference,
        CustomAddressType               CustomType,
        AddressOrdinalByPerson          Ordinal NULL,
        IsDefaultAddressByPerson        bit,
        PersonAddressIsActive           ActiveState,
        ---------------------------------------------
        -- Phone Number Attributes
        ---------------------------------------------
        PhoneNumberId                   PhoneNumberId NULL,
        PhoneNumber                     PhoneNumber,
        AreaCode                        AreaCode,
        Prefix                          PhoneNumberPrefix,
        Suffix                          PhoneNumberSuffix,
        Extension                       PhoneNumberExtension,
        PhoneNumberTypeId               TypeReference,
        CustomPhoneNumberType           CustomType,
        RawPhoneNumber                  varchar(16),
        FormattedPhoneNumber            varchar(19),
        PhoneNumberOrdinalByPerson      Ordinal NULL,
        IsDefaultPhoneNumberByPerson    bit,
        PersonPhoneNumberIsActive       ActiveState,
        ---------------------------------------------
        -- Person Email Address Attributes
        ---------------------------------------------
        EmailAddressId                  EmailAddressId NULL,
        EmailAddress                    EmailAddress,
        EmailAddressTypeId              TypeReference,
        CustomEmailAddressType          CustomType,
        EmailAddressOrdinalByPerson     Ordinal NULL,
        IsDefaultEmailAddressByPerson   bit,
        PersonEmailAddressIsActive      ActiveState,
        ---------------------------------------------
        -- Annual Schedule Attributes
        ---------------------------------------------
        SegmentedClassTermId            ElementReference,
        SegmentedClassTerm              varchar(100),
        SegmentedClassTermOrdinalByType int,
        SegmentedClassTermIsCurrent     bit,
        ----------------------------------------------
        ClassPeriod                     int,
        ---------------------------------------------
        ClassId                         ElementReference,
        ClassTitle                      varchar(100),
        ---------------------------------------------
        SchoolYearId                    int,
        SchoolYear                      varchar(50),
        ----------------------------------------------
        -- Attenance Attributes
        ---------------------------------------------
        AbsenceDate                     XsltDate,
        AbsencePeriod                   int,
        AbsenceType                     varchar(100),
        AbsenceReason                   varchar(100),
        AbsenceSchoolYearId             int,
        AbsenceSchoolYear               varchar(50),
        ---------------------------------------------
        -- Discipline Attributes
        ---------------------------------------------
        SchoolId                        int,
        DisciplineOffense               varchar(100),
        DisciplineAction                varchar(100),
        StudentDisciplineRecordId       int,
        StudentDisciplineRecord         varchar(500),
        StudentDisciplineRecordDate     XsltDate,
        DisciplineSchoolYearId          int,
        DisciplineSchoolYear            varchar(50),
        ---------------------------------------------
        -- Assessments Attributes
        ---------------------------------------------
        AssessmentTestDefinitionId          int,
        AssessmentTestLabel                 varchar(500),
        LowestPossibleScore                 int,
        MinimumPassingScore                 int,
        HighestPossibleScore                int,
        -------------------------------------------
        AssessmentTestSubjectAreaId         int,
        AssessmentTestSubjectArea           varchar(100),
        AssessmentTestSubjectAreaOrdinal    int,
        -------------------------------------------
        AssessmentTestSchoolYearId          int,
        AssessmentTestSchoolYear            varchar(100),
        AssessmentTestSchoolYearOrdinal     int,
        -------------------------------------------
        AssessmentTestPerformanceLevelId            int,
        AssessmentTestPerformanceLevel              varchar(50),
        AssessmentTestPerformanceLevelMinimumScore  int,
        AssessmentTestPerformanceLevelMaximumScore  int,
        AssessmentTestPerformanceLevelColor         varchar(20),
        -------------------------------------------
        StudentAssessmentTestScore                  int,
        StudentPassed                               bit,
        StudentAssessmentTestPerformanceLevel       varchar(20),
        StudentAssessmentTestPerformanceLevelId     int,
        StudentAssessmentTestPerformanceLevelColor  varchar(20),
        -------------------------------------------
        StudentAssessmentTestDate                   date,
        StudentAssessmentTestSchoolYear             varchar(50),
        StudentAssessmentTestSchoolYearId           int
)       ---------------------------------------------
AS
BEGIN   -- =================================================================================




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




        ----------------------------------------------------
        --
        ----------------------------------------------------
        DECLARE @Students TABLE
        (       ---------------------------------------------
                -- Person Attributes
                ---------------------------------------------
                StudentId                       RoleReference NULL,
                Student                         varchar(300),
                FirstName                       Name,
                NickName                        Name,
                MiddleName                      Name,
                LastName                        Name,
                BirthDate                       BirthDate,
                Gender                          GenderAbbreviation,
                SocialSecurityNumber            SocialSecurityNumber,
                SocialSecurityNumberChecksum    bigint,
                ContactInfoIsConfidential       YesNo,
                ImagePath                       FilePath
        )       ---------------------------------------------
        INSERT @Students (
                StudentId,
                Student,
                FirstName,
                NickName,
                MiddleName,
                LastName,
                BirthDate,
                Gender,
                SocialSecurityNumber,
                SocialSecurityNumberChecksum,
                ContactInfoIsConfidential,
                ImagePath
        )       ---------------------------------------------
        SELECT
                P.PersonId,
                P.Person,
                P.FirstName,
                P.NickName,
                P.MiddleName,
                P.LastName,
                P.BirthDate,
                P.Gender,
                P.SocialSecurityNumber,
                P.SocialSecurityNumberChecksum,
                P.ContactInfoIsConfidential,
                P.ImagePath
                ---------------------------------------------
        FROM    Persons P
        JOIN    Students St
          ON    P.PersonId = St.StudentId
        WHERE   St.StudentId = @StudentId
        ----------------------------------------------------



        DECLARE @Student varchar(300)
        SELECT  @Student = Student
        FROM    @Students






        ----------------------------------------------------
        -- Insert Person and Role data into Output Table
        ----------------------------------------------------
        INSERT @OutputTable (
                StudentId,
                Student,
                FirstName,
                NickName,
                MiddleName,
                LastName,
                BirthDate,
                Gender,
                SocialSecurityNumber,
                SocialSecurityNumberChecksum,
                ContactInfoIsConfidential,
                ImagePath
        )       ---------------------------------------------
        SELECT  P.StudentId,
                P.Student,
                P.FirstName,
                P.NickName,
                P.MiddleName,
                P.LastName,
                P.BirthDate,
                P.Gender,
                P.SocialSecurityNumber,
                P.SocialSecurityNumberChecksum,
                P.ContactInfoIsConfidential,
                P.ImagePath
        FROM    @Students P
        ----------------------------------------------------











        ----------------------------------------------------
        -- Insert Person Addresses into Output Table
        ----------------------------------------------------
        INSERT @OutputTable (
                StudentId,
                Student,
                ---------------------------------------------
                AddressId,
                Address,
                StreetAddress,
                AddressLine2,
                City,
                State,
                ZipCode,
                AddressTypeId,
                CustomAddressType,
                AddressOrdinalByPerson,
                IsDefaultAddressByPerson,
                PersonAddressIsActive
        )       ---------------------------------------------
        SELECT  P.StudentId,
                P.Student,
                ---------------------------------------------
                A.AddressId,
                A.[Address],
                A.StreetAddress,
                A.AddressLine2,
                A.City,
                A.[State],
                A.ZipCode,
                A.AddressTypeId,
                A.CustomAddressType,
                P_A.AddressOrdinalByPerson,
                P_A.IsDefaultAddressByPerson,
                P_A.PersonAddressIsActive
                ---------------------------------------------
        FROM    @Students P
        JOIN    Persons_Addresses P_A
          ON    P_A.PersonId = P.StudentId
        JOIN    Addresses A
          ON    A.AddressId = P_A.AddressId
        ----------------------------------------------------








        ----------------------------------------------------
        -- Insert Person Phone Numbers into Output Table
        ----------------------------------------------------
        INSERT @OutputTable (
                StudentId,
                Student,
                ---------------------------------------------
                PhoneNumberId,
                PhoneNumber,
                AreaCode,
                Prefix,
                Suffix,
                Extension,
                PhoneNumberTypeId,
                CustomPhoneNumberType,
                RawPhoneNumber,
                FormattedPhoneNumber,
                PhoneNumberOrdinalByPerson,
                IsDefaultPhoneNumberByPerson,
                PersonPhoneNumberIsActive
        )       ---------------------------------------------
        SELECT  P.StudentId,
                P.Student,
                ---------------------------------------------
                PN.PhoneNumberId,
                PN.PhoneNumber,
                PN.AreaCode,
                PN.Prefix,
                PN.Suffix,
                PN.Extension,
                PN.PhoneNumberTypeId,
                PN.CustomPhoneNumberType,
                PN.RawPhoneNumber,
                PN.FormattedPhoneNumber,
                P_PN.PhoneNumberOrdinalByPerson,
                P_PN.IsDefaultPhoneNumberByPerson,
                P_PN.PersonPhoneNumberIsActive
        FROM    @Students P
        JOIN    Persons_PhoneNumbers P_PN
          ON    P.StudentId = P_PN.PersonId
        JOIN    PhoneNumbers PN
          ON    P_PN.PhoneNumberId = PN.PhoneNumberId
        ----------------------------------------------------







        ----------------------------------------------------
        -- Insert Person Email Addresses into Output Table
        ----------------------------------------------------
        INSERT @OutputTable (
                StudentId,
                Student,
                ---------------------------------------------
                EmailAddressId,
                EmailAddress,
                EmailAddressTypeId,
                CustomEmailAddressType,
                EmailAddressOrdinalByPerson,
                IsDefaultEmailAddressByPerson,
                PersonEmailAddressIsActive
        )       ---------------------------------------------
        SELECT  P.StudentId,
                P.Student,
                ---------------------------------------------
                EA.EmailAddressId,
                EA.EmailAddress,
                EA.EmailAddressTypeId,
                EA.CustomEmailAddressType,
                P_EA.EmailAddressOrdinalByPerson,
                P_EA.IsDefaultEmailAddressByPerson,
                P_EA.PersonEmailAddressIsActive
                ---------------------------------------------
        FROM    @Students P
        JOIN    Persons_EmailAddresses P_EA
          ON    P_EA.PersonId = @StudentId
        JOIN    EmailAddresses EA
          ON    EA.EmailAddressId = P_EA.EmailAddressId
        ----------------------------------------------------







        ----------------------------------------------------
        -- Class Schedules
        ----------------------------------------------------
        INSERT  @OutputTable
        (       StudentId,
                Student,
                ----------------------------------------------
                ClassId,
                ClassTitle,
                ----------------------------------------------
                ClassPeriod,
                ----------------------------------------------
                SegmentedClassTermId,
                SegmentedClassTerm,
                SegmentedClassTermOrdinalByType,
                SegmentedClassTermIsCurrent,
                ----------------------------------------------
                SchoolYearId,
                SchoolYear
        )       ----------------------------------------------
        SELECT  @StudentId,
                @Student,
                ----------------------------------------------
                C.ClassId,
                C.ClassTitle,
                ----------------------------------------------
                C.ClassPeriod,
                ----------------------------------------------
                C.SegmentedClassTermId,
                C.SegmentedClassTerm,
                C.SegmentedClassTermOrdinalByType,
                C.SegmentedClassTermIsCurrent,
                ----------------------------------------------
                Y.SchoolYearId,
                Y.SchoolYear
                ----------------------------------------------
        FROM    SegmentedClassScheduleByStudentAndSchoolYear(4307, 7) C
        JOIN    SchoolYears Y
          ON    Y.SchoolYearId = 11
        --FROM  SegmentedClassScheduleByStudentAndSchoolYear(@StudentId, @SchoolYearId)
        ----------------------------------------------------
        /*
        -- Grab a student with a class schedule
        SELECT * FROM SegmentedClassScheduleByStudentAndSchoolYear(4307, 6)
        */









        ----------------------------------------------------
        -- Attendance / Absence records
        ----------------------------------------------------
        DECLARE @SchoolYearStartDate date
        DECLARE @SchoolYearEndDate date
        ----------------------------------------------------
        SET @SchoolYearStartDate = '08/01/' + CAST(@SchoolYearId-1 as varchar(2))
        SET @SchoolYearEndDate = '07/31/' + CAST(@SchoolYearId as varchar(2))
        ----------------------------------------------------
        INSERT  @OutputTable
        (       StudentId,
                Student,
                AbsenceDate,
                AbsencePeriod,
                AbsenceType,
                AbsenceReason,
                AbsenceSchoolYearId,
                AbsenceSchoolYear
        )
        SELECT
                @StudentId,
                @Student,
                dbo.XsltDate(A.AbsenceDate),
                A.AbsencePeriod,
                T.AbsenceType,
                R.AbsenceReason,
                SY.SchoolYearId,
                SY.SchoolYear
        FROM    dbo.StudentClassAttendanceRecords A
        JOIN    SchoolYears SY
          ON    AbsenceDate BETWEEN CAST('08/01/' + CAST(SY.SchoolYearId-1 as varchar(2)) as date)
                                AND CAST('07/31/' + CAST(SY.SchoolYearId as varchar(2)) as date)
        LEFT
        JOIN    AbsenceTypes T
          ON    A.AbsenceTypeId = T.AbsenceTypeId
        LEFT
        JOIN    AbsenceReasons R
          ON    A.AbsenceReasonId = R.AbsenceReasonId
        WHERE   A.StudentId = 17024 -- @StudentId
        --AND   A.SchoolYearId = 11 --@SchoolYearId
        ORDER BY A.AbsenceDate DESC
        ----------------------------------------------------
        /*
        -- Grab a student with Absences
        SELECT  StudentId, COUNT(StudentClassAttendanceRecordId)
        FROM    dbo.StudentClassAttendanceRecords A
        WHERE   A.AbsenceDate > '09/01/10'
        GROUP BY StudentId
        ORDER BY COUNT(StudentClassAttendanceRecordId) DESC
        */




        ----------------------------------------------------
        --
        ----------------------------------------------------
        INSERT  @OutputTable
        (       StudentId,
                Student,
                ----------------------------------------------
                DisciplineOffense,
                DisciplineAction,
                StudentDisciplineRecordId,
                StudentDisciplineRecord,
                StudentDisciplineRecordDate,
                DisciplineSchoolYearId,
                DisciplineSchoolYear
        )       ----------------------------------------------
        SELECT  @StudentId,
                @Student,
                ----------------------------------------------
                O.DisciplineOffense,
                A.DisciplineAction,
                R.StudentDisciplineRecordId,
                R.StudentDisciplineRecord,
                CONVERT(VARCHAR(23), R.StudentDisciplineRecordDate, 126),
                SY.SchoolYearId,
                SY.SchoolYear
        FROM    StudentDisciplineRecords R
        JOIN    SchoolYears SY
          ON    R.SchoolYearId = SY.SchoolYearId
        LEFT
        JOIN    DisciplineOffenses O
          ON    R.DisciplineOffenseId = O.DisciplineOffenseId
        LEFT
        JOIN    DisciplineActions A
          ON    R.DisciplineActionId = A.DisciplineActionId
        WHERE   R.StudentId = 44583 --- 45495 -- @StudentId
        --  AND R.SchoolYearId = 11 -- @SchoolYearId
        ----------------------------------------------------
        /*
        -- Grab a student with a class schedule
        SELECT  StudentId, COUNT(StudentDisciplineRecordId)
        FROM    StudentDisciplineRecords
        WHERE   SchoolYearId = 11
        GROUP
           BY   StudentId
        ORDER
           BY    COUNT(StudentDisciplineRecordId)  DESC
        */
        /*
        SELECT  StudentId, R.SchoolYearId, COUNT(R.StudentDisciplineRecordId)
        FROM    StudentDisciplineRecords R
        WHERE   R.SchoolYearId = 10
        GROUP
           BY   StudentId, R.SchoolYearId
        ORDER
           BY   COUNT(R.StudentDisciplineRecordId) DESC
        */



        ----------------------------------------------------
        --
        ----------------------------------------------------
        INSERT  @OutputTable
        (       StudentId,
                Student,
                ----------------------------------------------
                AssessmentTestDefinitionId,
                AssessmentTestLabel,
                LowestPossibleScore,
                MinimumPassingScore,
                HighestPossibleScore,
                -------------------------------------------
                AssessmentTestSubjectAreaId,
                AssessmentTestSubjectArea,
                AssessmentTestSubjectAreaOrdinal,
                -------------------------------------------
                AssessmentTestSchoolYearId,
                AssessmentTestSchoolYear,
                AssessmentTestSchoolYearOrdinal,
                -------------------------------------------
                AssessmentTestPerformanceLevelId,
                AssessmentTestPerformanceLevel,
                AssessmentTestPerformanceLevelMinimumScore,
                AssessmentTestPerformanceLevelMaximumScore,
                AssessmentTestPerformanceLevelColor,
                -------------------------------------------
                StudentAssessmentTestScore,
                StudentPassed,
                StudentAssessmentTestPerformanceLevel,
                StudentAssessmentTestPerformanceLevelId,
                StudentAssessmentTestPerformanceLevelColor,
                -------------------------------------------
                StudentAssessmentTestDate,
                StudentAssessmentTestSchoolYear
        )       ----------------------------------------------
        SELECT  @StudentId,
                @Student,
                ----------------------------------------------
                AssessmentTestDefinitionId,
                AssessmentTestLabel,
                LowestPossibleScore,
                MinimumPassingScore,
                HighestPossibleScore,
                -------------------------------------------
                AssessmentTestSubjectAreaId,
                AssessmentTestSubjectArea,
                AssessmentTestSubjectAreaOrdinal,
                -------------------------------------------
                SchoolYearId,
                SchoolYear,
                SchoolYearOrdinal,
                -------------------------------------------
                AssessmentTestPerformanceLevelId,
                AssessmentTestPerformanceLevel,
                AssessmentTestPerformanceLevelMinimumScore,
                AssessmentTestPerformanceLevelMaximumScore,
                AssessmentTestPerformanceLevelColor,
                -------------------------------------------
                StudentAssessmentTestScore,
                StudentPassed,
                StudentAssessmentTestPerformanceLevel,
                StudentAssessmentTestPerformanceLevelId,
                StudentAssessmentTestPerformanceLevelColor,
                -------------------------------------------
                StudentAssessmentTestDate,
                StudentAssessmentTestSchoolYear
        FROM    AssessmentTestScoresByStudent(41627) S
        WHERE   S.SchoolYearId = 10 -- @SchoolYearId
        --FROM  AssessmentTestScoresByStudent(@StudentId) S
        --WHERE S.SchoolYearId = @SchoolYearId
        ----------------------------------------------------
        /*
        -- Grab a student with a class schedule
        SELECT  StudentId, COUNT(AssessmentTestDefinitionId)
        FROM    StudentAssessmentTestScores S
        JOIN    AssessmentTests T
          ON    S.AssessmentTestId = T.AssessmentTestId
        WHERE    SchoolYearId = 10
        GROUP
           BY   StudentId
        ORDER
           BY    COUNT(AssessmentTestDefinitionId)  DESC
        */








        ----------------------------------------------------
        --SELECT * FROM @OutputTable
        ----------------------------------------------------
RETURN
END     -- =================================================================================
GO