USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[AssessmentTestGroupScoresByStudentGroupAsXml]    Script Date: 02/09/2012 16:55:16 ******/
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 = 15
        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 xmlAssemblyTimeInMilliseconds=""></Xml>'
        DECLARE @StartTime time = CAST(GETDATE() as time)
        ---------------------------------------------


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








        ---------------------------------------------
        -- 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)
        SELECT  SATS.StudentId,
                SATS.AssessmentTestId,
                SATS.StudentAssessmentTestScore,
                ATI.AssessmentTestOrdinalByGroup
        FROM    Students_Groups S_G
        JOIN    (   SELECT  iSATS.StudentId,
                            iSATS.AssessmentTestId,
                            iSATS.AssessmentTestPerformanceLevelId,
                            iSATS.StudentAssessmentTestScoreId,
                            iSATS.StudentAssessmentTestScore,
                            ROW_NUMBER() OVER(
                                    PARTITION BY iSATS.AssessmentTestId, iSATS.StudentId
                                    ORDER BY iSATS.StudentAssessmentTestScore DESC,
                                             iSATS.StudentAssessmentTestScoreId DESC) AS ScoreRank
                    FROM    StudentAssessmentTestScores iSATS
                    JOIN    @AssessmentTestIds iATI
                      ON    iSATS.AssessmentTestId = iATI.AssessmentTestId
                ) SATS
          ON    S_G.StudentId = SATS.StudentId
         AND    S_G.StudentGroupId = @StudentGroupId
         AND    SATS.ScoreRank = 1
        JOIN    @AssessmentTestIds ATI
          ON    SATS.AssessmentTestId = ATI.AssessmentTestId
        ---------------------------------------------
        --SELECT COUNT(DISTINCT AssessmentTestId) FROM @StudentAssessmentTestScores
        ---------------------------------------------





        ---------------------------------------------
        -- Re-populate Assessment test id, to only get tests with score
        ---------------------------------------------
        DELETE  @AssessmentTestIds
        ---------------------------------------------
        INSERT  @AssessmentTestIds
        SELECT  AssessmentTestId,
                AT_G.AssessmentTestOrdinalByGroup
        FROM    AssessmentTests_Groups AT_G
        WHERE   AT_G.AssessmentTestGroupId = @AssessmentTestGroupId
          AND   EXISTS (
                        SELECT  1
                        FROM    @StudentAssessmentTestScores SATS
                        WHERE   SATS.AssessmentTestId = AT_G.AssessmentTestId
                )
        ---------------------------------------------
        --SELECT COUNT(*) 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
        ---------------------------------------------










/*      ---------------------------------------------
        --
        ---------------------------------------------
        DECLARE @AssessmentTestPerformanceLevels TABLE
        (       -------------------------------------------
                AssessmentTestPerformanceLevelId            int NOT NULL,
                AssessmentTestPerformanceLevel              varchar(50),
                AssessmentTestPerformanceLevelMinimumScore  int,
                AssessmentTestPerformanceLevelMaximumScore  int,
                AssessmentTestPerformanceLevelColor         varchar(20),
                AssessmentTestPerformanceLevelOrdinalByTest int,
                AssessmentTestPerformanceLevelMeetsStandard bit,
                AssessmentTestId                            int,
                HighestPossibleScore                        int,
                LowestPossibleScore                         int,
                MinimumPassingScore                         int,
                AssessmentTestLabel                         varchar(200),
                AssessmentTestIsPassFail                    bit,
                CorrespondingAssessmentTestGroupId          int,
                AssessmentTestOrdinalByGroup                int
                -------------------------------------------
                PRIMARY KEY (AssessmentTestPerformanceLevelId)
        )
        ---------------------------------------------
        INSERT  @AssessmentTestPerformanceLevels  (
                AssessmentTestPerformanceLevelId,
                AssessmentTestPerformanceLevel,
                AssessmentTestPerformanceLevelMinimumScore,
                AssessmentTestPerformanceLevelMaximumScore,
                AssessmentTestPerformanceLevelColor,
                AssessmentTestPerformanceLevelOrdinalByTest,
                AssessmentTestPerformanceLevelMeetsStandard,
                AssessmentTestId,
                HighestPossibleScore,
                LowestPossibleScore,
                MinimumPassingScore,
                AssessmentTestLabel,
                AssessmentTestIsPassFail,
                CorrespondingAssessmentTestGroupId,
                AssessmentTestOrdinalByGroup
        )       -------------------------------------------
        SELECT  ATPL.AssessmentTestPerformanceLevelId,
                ATPL.AssessmentTestPerformanceLevel,
                ATPL.AssessmentTestPerformanceLevelMinimumScore,
                ATPL.AssessmentTestPerformanceLevelMaximumScore,
                ATPL.AssessmentTestPerformanceLevelColor,
                ATPL.AssessmentTestPerformanceLevelOrdinalByTest,
                CASE
                    WHEN(ATPL.AssessmentTestPerformanceLevelMinimumScore >= AT.MinimumPassingScore)
                    THEN CAST(1 AS bit)
                    ELSE CAST(0 AS bit)
                END, --PL.AssessmentTestPerformanceLevelMeetsStandard
                ATPL.AssessmentTestId,
                AT.HighestPossibleScore,
                AT.LowestPossibleScore,
                AT.MinimumPassingScore,
                AT.AssessmentTestLabel,
                AT.AssessmentTestIsPassFail,
                AT.CorrespondingAssessmentTestGroupId,
                I.AssessmentTestOrdinalByGroup
        FROM    @AssessmentTestIds I
        JOIN    AssessmentTests AT
          ON    I.AssessmentTestId = AT.AssessmentTestId
        JOIN    AssessmentTestPerformanceLevels ATPL
          ON    AT.AssessmentTestId = ATPL.AssessmentTestId
        ---------------------------------------------
        --SELECT * FROM @AssessmentTestPerformanceLevels
*/      ---------------------------------------------










        ---------------------------------------------
        -- Assessment-Tests / Performance-Levels
        ---------------------------------------------
        DECLARE @AssessmentTests Xml -- varchar(max)
        ---------------------------------------------
        SET @AssessmentTests = (
                SELECT  AT.AssessmentTestId AS '@AssessmentTestId',
                        AssessmentTestLabel AS '@Label',
                        AssessmentTestIsPassFail AS '@IsPassFail',
                        CorrespondingAssessmentTestGroupId AS '@AssessmentTestGroupId',
                        HighestPossibleScore AS '@HighestPossibleScore',
                        LowestPossibleScore AS '@LowestPossibleScore',
                        MinimumPassingScore AS '@MinimumPassingScore',
                        AssessmentTestOrdinalByGroup AS '@Ordinal',
                        --AssessmentTestSubjectAreaId,
                        -------------------------
                        AssessmentTestPerformanceLevelId AS '@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
                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    @StudentAssessmentTestScores StudentAssessmentTestScore
                  ON    Student.StudentId = StudentAssessmentTestScore.StudentId
                ORDER
                   BY   Student.OrdinalText,
                        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