USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[AssessmentTestGroupScoresByStudentGroupAsXml]    Script Date: 02/10/2012 17:47:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      School Data Solutions
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- Copyright 2011-2012 School Data Solutions, All Rights Reserved
-- ============================================
ALTER FUNCTION [dbo].[AssessmentTestGroupScoresByStudentGroupAsXml]
(   ---------------------------------------------
    @StudentGroupId         int,
    @AssessmentTestGroupId  int,
    ---------------------------------------------
    @DataViewMode           varchar(200) = 'Standard',
    @EncryptIds             bit = 0,
    @ApplicationUserId      int = NULL,
    @ApplicationUserActivePersonRoleId int = 1
)   ---------------------------------------------
RETURNS Xml
AS
BEGIN   /*---------------------------------------------
        --SELECT    SG.StudentGroupName,
        --      SG.StudentGroupId,
        --      ATG.AssessmentTestGroup,
        --      ATG.AssessmentTestGroupId,
        --      C.StudentCount
        --FROM  (
        --      SELECT  COUNT(SATS.StudentAssessmentTestScoreId) AS StudentCount,
        --              S_G.StudentGroupId,
        --              AT_G.AssessmentTestGroupId
        --      FROM    StudentAssessmentTestScores SATS
        --      JOIN    AssessmentTests_Groups  AT_G
        --        ON    SATS.AssessmentTestId = AT_G.AssessmentTestId
        --      JOIN    Students_Groups S_G
        --        ON    SATS.StudentId = S_G.StudentId
        --      GROUP BY S_G.StudentGroupId,
        --              AT_G.AssessmentTestGroupId
        --      HAVING  COUNT(SATS.StudentAssessmentTestScoreId) > 500
        --      ) C
        --JOIN  AssessmentTestGroups ATG
        --  ON  C.AssessmentTestGroupId = ATG.AssessmentTestGroupId
        --JOIN  StudentGroups SG
        --  ON  C.StudentGroupId = SG.StudentGroupId
        ---------------------------------------------
        DECLARE @StudentGroupId int         SET @StudentGroupId = 51315
        DECLARE @AssessmentTestGroupId int  SET @AssessmentTestGroupId = 14
        DECLARE @DataViewMode varchar(200)  SET @DataViewMode = 'Standard'
        DECLARE @EncryptIds bit             SET @EncryptIds = 0
        DECLARE @ApplicationUserId  int     SET @ApplicationUserId = 3
        DECLARE @ApplicationUserActivePersonRoleId int
        --SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51392, 53, DEFAULT, 0, 1, 1)
        ---------------------------------------------*/






        ---------------------------------------------
        -- Declare the return variable here
        -- Prepare for performance metrics
        ---------------------------------------------
        DECLARE @Xml XML = '<Xml/>'
        DECLARE @StartTime time = CAST(GETDATE() as time)
        ---------------------------------------------


        ---------------------------------------------
        -- Assessment-Test-Ids
        ---------------------------------------------
        DECLARE @AssessmentTestIds TABLE (
                AssessmentTestId                int,
                AssessmentTestOrdinalByGroup    int
        )
        ---------------------------------------------
        INSERT  @AssessmentTestIds
        SELECT  AssessmentTestId,
                ROW_NUMBER() OVER(
                ORDER BY AT_G.AssessmentTestOrdinalByGroup,
                         AT_G.AssessmentTestId)
        FROM    AssessmentTests_Groups AT_G
        WHERE   AT_G.AssessmentTestGroupId = @AssessmentTestGroupId
        ---------------------------------------------
        --SELECT * FROM @AssessmentTestIds
        ---------------------------------------------








        ---------------------------------------------
        -- Student Maximum Assessment Test Scores
        ---------------------------------------------
        DECLARE @StudentMaximumAssessmentTestScores TABLE (
                StudentId                           int,
                AssessmentTestId                    int,
                StudentAssessmentTestScoreId        int,
                StudentAssessmentTestScore          money,
                AssessmentTestPerformanceLevelId    int,
                StudentAssessmentTestScoreRank      int,
                AssessmentTestOrdinalByGroup        int
        )
        INSERT  @StudentMaximumAssessmentTestScores(
                StudentId,
                AssessmentTestId,
                StudentAssessmentTestScoreId,
                StudentAssessmentTestScore,
                AssessmentTestPerformanceLevelId,
                StudentAssessmentTestScoreRank,
                AssessmentTestOrdinalByGroup
        )
        SELECT  SATS.StudentId,
                SATS.AssessmentTestId,
                SATS.StudentAssessmentTestScoreId,
                SATS.StudentAssessmentTestScore,
                SATS.AssessmentTestPerformanceLevelId,
                ROW_NUMBER() OVER(
                        PARTITION BY SATS.AssessmentTestId, SATS.StudentId
                        ORDER BY SATS.StudentAssessmentTestScore DESC,
                                 SATS.StudentAssessmentTestScoreId DESC) AS ScoreRank,
                ATI.AssessmentTestOrdinalByGroup
        FROM    StudentAssessmentTestScores SATS
        JOIN    @AssessmentTestIds ATI
          ON    SATS.AssessmentTestId = ATI.AssessmentTestId
        JOIN    Students_Groups S_G
          ON    SATS.StudentId = S_G.StudentId
         AND    S_G.StudentGroupId = @StudentGroupId
        --WHERE SATS.StudentId IN (432, 27409, 482, 54930, 54931)
        ---------------------------------------------
        --SELECT * FROM @StudentMaximumAssessmentTestScores ORDER BY StudentId, AssessmentTestId
        ---------------------------------------------



        ---------------------------------------------
        -- Student Assessment Test Scores
        ---------------------------------------------
        DECLARE @StudentAssessmentTestScores TABLE (
                StudentId                           int,
                AssessmentTestId                    int,
                StudentAssessmentTestScore          money,
                AssessmentTestOrdinalByGroup        int,
                AssessmentTestPerformanceLevelId    int,
                AssessmentTestPerformanceLevel      varchar(100),
                AssessmentTestPerformanceLevelColor varchar(6)
        )
        ---------------------------------------------
        INSERT  @StudentAssessmentTestScores (
                StudentId,
                AssessmentTestId,
                StudentAssessmentTestScore,
                AssessmentTestOrdinalByGroup,
                AssessmentTestPerformanceLevelId,
                AssessmentTestPerformanceLevel,
                AssessmentTestPerformanceLevelColor
        )
        SELECT  SATS.StudentId,
                SATS.AssessmentTestId,
                SATS.StudentAssessmentTestScore,
                ATI.AssessmentTestOrdinalByGroup,
                SATS.AssessmentTestPerformanceLevelId,
                ATPL.AssessmentTestPerformanceLevel,
                ATPL.AssessmentTestPerformanceLevelColor
        FROM    @StudentMaximumAssessmentTestScores SATS
        JOIN    @AssessmentTestIds ATI
          ON    SATS.AssessmentTestId = ATI.AssessmentTestId
         AND    SATS.StudentAssessmentTestScoreRank = 1
        JOIN    AssessmentTestPerformanceLevels ATPL
          ON    SATS.AssessmentTestPerformanceLevelId = ATPL.AssessmentTestPerformanceLevelId
        --WHERE SATS.StudentId IN (432, 27409, 482)
        ---------------------------------------------
        --SELECT * FROM @StudentAssessmentTestScores ORDER BY StudentId, AssessmentTestId
        ---------------------------------------------





        ---------------------------------------------
        -- Re-populate Assessment test id, to only get tests with score
        ---------------------------------------------
        DELETE  @AssessmentTestIds
        ---------------------------------------------
        INSERT  @AssessmentTestIds
        SELECT  AssessmentTestId,
                ROW_NUMBER() OVER(
                ORDER BY AT_G.AssessmentTestOrdinalByGroup,
                         AT_G.AssessmentTestId)
        FROM    AssessmentTests_Groups AT_G
        WHERE   AT_G.AssessmentTestGroupId = @AssessmentTestGroupId
          AND   EXISTS (
                        SELECT  1
                        FROM    @StudentAssessmentTestScores SATS
                        WHERE   SATS.AssessmentTestId = AT_G.AssessmentTestId
                )
        ---------------------------------------------
        --SELECT * FROM @AssessmentTestIds
        ---------------------------------------------




        ---------------------------------------------
        -- StudentIds
        ---------------------------------------------
        DECLARE @StudentIds TABLE (
                StudentId   int
        )
        ---------------------------------------------
        INSERT  @StudentIds(StudentId)
        SELECT  DISTINCT S.StudentId
        FROM    @StudentAssessmentTestScores S
        ---------------------------------------------
        --SELECT * FROM @StudentIds
        ---------------------------------------------





        ---------------------------------------------
        -- Students
        ---------------------------------------------
        DECLARE @Students TABLE (
                StudentId   int,
                FirstName   varchar(100),
                LastName    varchar(100),
                DisplayName varchar(300),
                OrdinalText varchar(300)
        )
        ---------------------------------------------
        INSERT  @Students(StudentId, FirstName, LastName, DisplayName, OrdinalText)
        SELECT  P.PersonId,
                P.FirstName,
                P.LastName,
                P.FirstName + ' ' + P.LastName,
                P.LastName + ', ' + P.FirstName
        FROM    @StudentIds S
        JOIN    dbo.DataViewModePersons(NULL,@DataViewMode) P
          ON    S.StudentId = P.PersonId
        ---------------------------------------------
        --SELECT * FROM @Students
        ---------------------------------------------




        ---------------------------------------------
        -- Student CROSS JOIN Tests
        ---------------------------------------------
        DECLARE @StudentsAndTests TABLE (
                StudentId                       int,
                AssessmentTestId                int,
                AssessmentTestOrdinalByGroup    int
        )
        ---------------------------------------------
        INSERT  @StudentsAndTests
        SELECT  S.StudentId,
                T.AssessmentTestId,
                T.AssessmentTestOrdinalByGroup
        FROM    @StudentIds S
        CROSS
        JOIN    @AssessmentTestIds T
        ---------------------------------------------
        --SELECT * FROM @StudentsAndTests ORDER BY StudentId, AssessmentTestOrdinalByGroup
        ---------------------------------------------





        ---------------------------------------------
        -- Student Assessment Test Scores with blanks for un-taken tests
        ---------------------------------------------
        DECLARE @StudentAssessmentTestScoresWithBlanks TABLE (
                StudentId                           int,
                AssessmentTestId                    int,
                AssessmentTestOrdinalByGroup        int,
                StudentAssessmentTestScore          money,
                AssessmentTestPerformanceLevelId    int,
                AssessmentTestPerformanceLevel      varchar(100),
                AssessmentTestPerformanceLevelColor varchar(6)
        )
        ---------------------------------------------
        INSERT  @StudentAssessmentTestScoresWithBlanks
        SELECT  SAT.StudentId,
                SAT.AssessmentTestId,
                SAT.AssessmentTestOrdinalByGroup,
                SATS.StudentAssessmentTestScore,
                SATS.AssessmentTestPerformanceLevelId,
                SATS.AssessmentTestPerformanceLevel,
                SATS.AssessmentTestPerformanceLevelColor
        FROM    @StudentsAndTests SAT
        LEFT
        JOIN    @StudentAssessmentTestScores SATS
          ON    SAT.StudentId = SATS.StudentId
         AND    SAT.AssessmentTestId = SATS.AssessmentTestId
        ---------------------------------------------








        ---------------------------------------------
        -- Assessment-Tests / Performance-Levels
        ---------------------------------------------
        DECLARE @AssessmentTests Xml -- varchar(max)
        ---------------------------------------------
        SET @AssessmentTests = (
                SELECT  AT.AssessmentTestId AS '@AssessmentTestId',
                        AssessmentTestLabel AS '@Label',
                        AssessmentTestIsPassFail AS '@IsPassFail',
                        ATG.AssessmentTestGroupId AS '@AssessmentTestGroupId',
                        ATG.AssessmentTestGroup AS '@AssessmentTestGroup',
                        HighestPossibleScore AS '@HighestPossibleScore',
                        LowestPossibleScore AS '@LowestPossibleScore',
                        MinimumPassingScore AS '@MinimumPassingScore',
                        AssessmentTestOrdinalByGroup AS '@Ordinal',
                        --AssessmentTestSubjectAreaId,
                        -------------------------
                        AssessmentTestPerformanceLevelId AS 'PerformanceLevel/@AssessmentTestPerformanceLevelId',
                        AssessmentTestPerformanceLevel AS 'PerformanceLevel/@PerformanceLevel',
                        AssessmentTestPerformanceLevelColor AS 'PerformanceLevel/@Color',
                        AssessmentTestPerformanceLevelMinimumScore AS 'PerformanceLevel/@MinimumScore',
                        AssessmentTestPerformanceLevelMaximumScore AS 'PerformanceLevel/@MaximumScore',
                        AssessmentTestPerformanceLevelOrdinalByTest AS 'PerformanceLevel/@Ordinal'
                --FROM  @AssessmentTestPerformanceLevels
                FROM    @AssessmentTestIds I
                JOIN    AssessmentTests AT
                  ON    I.AssessmentTestId = AT.AssessmentTestId
                JOIN    AssessmentTestPerformanceLevels ATPL
                  ON    AT.AssessmentTestId = ATPL.AssessmentTestId
                LEFT
                JOIN    AssessmentTestGroups ATG
                  ON    AT.CorrespondingAssessmentTestGroupId = ATG.AssessmentTestGroupId
                AND  AT.CorrespondingAssessmentTestGroupId <> @AssessmentTestGroupId
                FOR XML PATH('AssessmentTest'),ROOT('AssessmentTests')
        )
        ---------------------------------------------
        SET @AssessmentTests = dbo.XmlNormalize(@AssessmentTests)
        --SELECT @AssessmentTests AS '@AssessmentTests'
        ---------------------------------------------


        ---------------------------------------------
        -- Assessment-Test-Group
        ---------------------------------------------
        DECLARE @AssessmentTestGroup Xml -- varchar(max)
        ---------------------------------------------
        SET @AssessmentTestGroup = (
                SELECT  *
                FROM    AssessmentTestGroups AssessmentTestGroup
                WHERE   AssessmentTestGroupId = @AssessmentTestGroupId
                FOR XML AUTO, ROOT('AssessmentTestGroups')
        )
        ---------------------------------------------
        --SELECT @AssessmentTestGroup AS '@AssessmentTestGroup'
        ---------------------------------------------






        ---------------------------------------------
        -- StudentAssessmentTestScores-Levels
        ---------------------------------------------
        DECLARE @StudentAssessmentTestScoresXml Xml -- varchar(max)
        ---------------------------------------------
        SET @StudentAssessmentTestScoresXml = (
                SELECT  Student.StudentId,-- AS 'StudentId',
                        Student.FirstName,-- AS 'FirstName',
                        Student.LastName,-- AS 'LastName',
                        Student.OrdinalText,-- AS '@OrdinalText',
                        -------------------------
                        StudentAssessmentTestScore.AssessmentTestId,-- AS '@AssessmentTestId',
                        StudentAssessmentTestScore.StudentAssessmentTestScore AS 'Score',
                        StudentAssessmentTestScore.AssessmentTestPerformanceLevel AS 'PerformanceLevel',
                        StudentAssessmentTestScore.AssessmentTestPerformanceLevelColor AS 'Color',
                        StudentAssessmentTestScore.AssessmentTestOrdinalByGroup AS 'Ordinal'
                FROM    @Students Student
                JOIN    @StudentAssessmentTestScoresWithBlanks StudentAssessmentTestScore
                  ON    Student.StudentId = StudentAssessmentTestScore.StudentId
                ORDER
                   BY   Student.OrdinalText,
                        Student.StudentId,
                        StudentAssessmentTestScore.AssessmentTestOrdinalByGroup
                FOR XML AUTO,ROOT('StudentAssessmentTestScores')
        )
        ---------------------------------------------
        --SELECT @StudentAssessmentTestScoresXml AS '@StudentAssessmentTestScoresXml'
        ---------------------------------------------






        ---------------------------------------------
        -- Assemble output Xml
        ---------------------------------------------
        SET @AssessmentTestGroup = dbo.XmlAdopt(@AssessmentTestGroup, @AssessmentTests, 'AssessmentTestGroup', 'AssessmentTest')
        --SET @Xml = dbo.XmlAdopt(@Xml,@AssessmentTests,'Xml','AssessmentTest')
        SET @Xml = dbo.XmlAdopt(@Xml,@AssessmentTestGroup,'Xml','AssessmentTestGroup')
        SET @Xml = dbo.XmlAdopt(@Xml,@StudentAssessmentTestScoresXml,'Xml','StudentAssessmentTestScores')
        ---------------------------------------------



        ---------------------------------------------
        -- Track Assembly time
        ---------------------------------------------
        DECLARE @XmlAssemblyTimeInMilliseconds int = DATEDIFF(ms, @StartTime,  CAST(GETDATE() as time))
        SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
        ---------------------------------------------


        --SELECT @Xml AS '@Xml'
        ---------------------------------------------
        RETURN @Xml
END
GO