USE [SDS_DevSchoolDistrict]
GO
-- =============================================
-- Author:      School Data Solutions
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- Copyright 2011-2012 School Data Solutions, All Rights Reserved
-- =============================================
ALTER FUNCTION [dbo].[AssessmentTestsByAssessmentTestGroupAsXml]
(   ---------------------------------------------
    @AssessmentTestGroupId      int,
    ---------------------------------------------
    @DataViewMode       varchar(200) = 'Standard',
    @EncryptIds         bit = 0,
    @ApplicationUserId  int = NULL,
    @ApplicationUserPersonRoleId int = 1
)   ---------------------------------------------
RETURNS Xml
AS
BEGIN   /*---------------------------------------------
        --SELECT * FROM StudentGroups SG JOIN Entities E ON SG.EntityId = E.EntityId WHERE E.EntityTypeId = 1
        SELECT COUNT(AssessmentTestId), AssessmentTestGroupId FROM AssessmentTests_Groups GROUP BY AssessmentTestGroupId ORDER BY 1 DESC
        SELECT dbo.AssessmentTestsByAssessmentTestGroupAsXml(255, DEFAULT, 0, 357, 1)
        DECLARE @StudentGroupId     int = 179
        DECLARE @DataViewMode       varchar(200) = 'Standard'
        DECLARE @EncryptIds         bit = 0
        DECLARE @ApplicationUserId  int = 3
        DECLARE @ApplicationUserActivePersonRoleId int = 1
        ---------------------------------------------*/

        ---------------------------------------------
        -- Prepare for performance metrics
        ---------------------------------------------
        DECLARE @StartTime time = CAST(GETDATE() as time)
        ---------------------------------------------


        ---------------------------------------------
        -- Declare the return variable here
        ---------------------------------------------
        DECLARE @Xml XML
        SET     @Xml = '<Xml xmlAssemblyTimeInMilliseconds=""></Xml>'
        ---------------------------------------------



        ---------------------------------------------
        -- Assessment Tests
        ---------------------------------------------
        DECLARE @AssessmentTests XML
        ---------------------------------------------
        SET @AssessmentTests = (
                SELECT  T.AssessmentTestLabel AS '@AssessmentTestLabel',
                        T.AssessmentTestName AS '@AssessmentTestName',
                        AT_G.AssessmentTestOrdinalByGroup AS '@Ordinal',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(EncryptedAssessmentTestIds.EncryptedId as varchar(200))
                          ELSE   CAST(T.AssessmentTestId as varchar(200))
                        END AS '@AssessmentTestId',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(EncryptedParentAssessmentTestIds.EncryptedId as varchar(200))
                          ELSE   CAST(T.ParentAssessmentTestId as varchar(200))
                        END AS '@ParentAssessmentTestId'
                FROM    AssessmentTests T
                JOIN    AssessmentTests_Groups AT_G
                  ON    T.AssessmentTestId = AT_G.AssessmentTestId
                 AND    AT_G.AssessmentTestGroupId = @AssessmentTestGroupId
                JOIN    EncryptedIds EncryptedAssessmentTestIds
                  ON    T.AssessmentTestId = EncryptedAssessmentTestIds.Id
                LEFT
                JOIN    EncryptedIds EncryptedParentAssessmentTestIds
                  ON    T.ParentAssessmentTestId = EncryptedParentAssessmentTestIds.Id
                ORDER
                   BY   T.AssessmentTestLabel
            --FOR XML AUTO, ROOT('AssessmentTests')
            FOR XML PATH('AssessmentTest'),ROOT('AssessmentTests')
        )
        ---------------------------------------------
        --SELECT @AssessmentTests AS '@AssessmentTests'
        ---------------------------------------------






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



        ---------------------------------------------
        -- 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
SELECT dbo.AssessmentTestsByAssessmentTestGroupAsXml(255, DEFAULT, 0, 357, 1) AS 'TEST AssessmentTestsByAssessmentTestGroupAsXml'