USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[WidgetDataForStudentActivitiesAsXml]    Script Date: 05/03/2012 19:00:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[WidgetDataForStudentActivitiesForPrintAsXml]
(       @EntityId       int,
        @IncludeHeader  bit = 1,
        @DataViewMode varchar(200) = 'Standard',
        @EncryptIds bit = 0,
        @ApplicationUserId int,
        @ApplicationUserPersonRoleId int = 1
)
RETURNS Xml
AS
BEGIN

        /*---------------------------------------------
        -- Debugging
        ---------------------------------------------
        --SELECT    SEE.StudentId, A.ActivityId, A.ActivityTitle, A.SchoolYearId, COUNT(DISTINCT StudentId)
        --FROM  Activities A
        --JOIN  StudentEntityEnrollments SEE
        --  ON  SEE.EntityId = A.ActivityId
        ---------------------------------------------
        --SELECT dbo.WidgetDataForStudentActivitiesForPrintAsXml(10004, 1, DEFAULT, DEFAULT, 22, 1)
        ---------------------------------------------
        DECLARE @EntityId           int             SET @EntityId = 10004
        DECLARE @SchoolYearId       int             SET @SchoolYearId = 12
        DECLARE @DataViewMode       varchar(200)
        DECLARE @EncryptIds         bit
        DECLARE @ApplicationUserId  int
        DECLARE @ApplicationUserPersonRoleId int
        ---------------------------------------------------*/


        ---------------------------------------------
        -- Prepare for performance metrics
        ---------------------------------------------
        DECLARE @XmlAssemblyStartTime time
        DECLARE @XmlAssemblyEndTime time
        DECLARE @XmlAssemblyTimeInMilliseconds int
        SET @XmlAssemblyStartTime = GETDATE()
        ---------------------------------------------




        ---------------------------------------------
        -- Declare the return variable here
        ---------------------------------------------
        DECLARE @Xml Xml = '<Xml/>'
        ---------------------------------------------


        ---------------------------------------------
        -- Ensure access is valid
        ---------------------------------------------
        DECLARE @ApplicationSecurityPermissionId int

        SELECT  @ApplicationSecurityPermissionId = ApplicationSecurityPermissionId
        FROM    ApplicationUserPermissionsByApplicationUser(@EntityId,8,@ApplicationUserId,@ApplicationUserPersonRoleId,1) AUP
        JOIN    SecurableItems SI
          ON    SI.SecurableItem = 'Student Activities'
         AND    SI.MinimumApplicationSecurityPermissionId <= AUP.ApplicationSecurityPermissionId

        IF @ApplicationSecurityPermissionId IS NULL
            GOTO ALLDONE



        ----------------------------------------------------
        --
        ----------------------------------------------------
        DECLARE @SchoolYearId int = dbo.SchoolYearIdValue(NULL)
        ----------------------------------------------------



        /*---------------------------------------------
        DECLARE @StudentActivities Xml
        ---------------------------------------------
        SET @StudentActivities = (
                SELECT
                        CASE @EncryptIds
                            WHEN 1
                              THEN CAST(ActivityEncryptedIds.EncryptedId as varchar(200))
                            ELSE CAST(A.ActivityId as varchar(200))
                        END AS '@EntityId',
                        A.ActivityTitle AS '@Title',
                        A.ActivityDescription AS '@Description'
                FROM    dbo.DataViewModeActivities(NULL,@DataViewMode) A
                JOIN    StudentEntityEnrollments S_A
                  ON    A.ActivityId = S_A.EntityId
                 AND    S_A.StudentId = @EntityId
                 AND    A.SchoolYearId = @SchoolYearId
                 AND    ISNULL(S_A.StudentEntityEnrollmentStopDate,A.ActivityEndDate) >= A.ActivityEndDate
                JOIN    EncryptedIds ActivityEncryptedIds
                  ON    A.ActivityId = ActivityEncryptedIds.Id
                FOR XML PATH('StudentActivity'), ROOT('StudentActivities')
        )
        ---------------------------------------------
        --SELECT @StudentActivities AS '@StudentActivities'
        */---------------------------------------------



        ---------------------------------------------
        DECLARE @Entity Xml
        DECLARE @PersonRoleId int
        ---------------------------------------------
        SELECT  @PersonRoleId = PersonRoleId
        FROM    PersonRoles PR
        WHERE   PR.PersonRole LIKE '%Student%'
        ---------------------------------------------
        IF(@IncludeHeader = 1)
            SET @Entity = dbo.WidgetDataForEntityProfileAsXml(@EntityId, @PersonRoleId, @SchoolYearId, @DataViewMode, @EncryptIds, @ApplicationUserId, @ApplicationUserPersonRoleId)
        ---------------------------------------------
        --SELECT @Entity AS '@Entity'
        ---------------------------------------------


        ---------------------------------------------
        DECLARE @StudentActivities Xml
        ---------------------------------------------
        SET @StudentActivities = (
            SELECT  SchoolYear.*,
                    ---------------
                    StudentActivity.*
            FROM    SchoolYears SchoolYear
            JOIN    (
                    SELECT  CASE @EncryptIds
                                WHEN 1
                                  THEN CAST(ActivityEncryptedIds.EncryptedId as varchar(200))
                                ELSE CAST(A.ActivityId as varchar(200))
                            END AS 'EntityId',
                            A.ActivityTitle AS 'Title',
                            A.ActivityDescription AS 'Description',
                            A.SchoolYearId
                    FROM    dbo.DataViewModeActivities(NULL,@DataViewMode) A
                    JOIN    StudentEntityEnrollments S_A
                      ON    A.ActivityId = S_A.EntityId
                     AND    S_A.StudentId = @EntityId
                     AND    A.SchoolYearId = @SchoolYearId
                     AND    ISNULL(S_A.StudentEntityEnrollmentStopDate,A.ActivityEndDate) >= A.ActivityEndDate
                    JOIN    EncryptedIds ActivityEncryptedIds
                      ON    A.ActivityId = ActivityEncryptedIds.Id
                    --FOR XML PATH('StudentActivity'), ROOT('StudentActivities')
                    ) StudentActivity
              ON    SchoolYear.SchoolYearId = StudentActivity.SchoolYearId
            ORDER
               BY   SchoolYear.SchoolYearId DESC
            FOR XML AUTO , ROOT('SchoolYears')
        )
        ---------------------------------------------
        --SELECT @StudentActivities AS '@StudentActivities'
        ---------------------------------------------




        ---------------------------------------------
        --DECLARE @SchoolYears Xml
        ---------------------------------------------
        --SET @SchoolYears = dbo.SchoolYearsByStudentAsXml(@EntityId, @DataViewMode, @EncryptIds, @ApplicationUserId, @ApplicationUserPersonRoleId)
        ---------------------------------------------
        --SELECT @SchoolYears AS '@SchoolYears'
        ---------------------------------------------





        ---------------------------------------------
        -- Assemble output Xml
        ---------------------------------------------
        --SET @Xml = dbo.XmlAdopt(@Xml, @StudentActivities, 'Xml', 'StudentActivity')
        --SET @Xml = dbo.XmlAdopt(@Xml, @SchoolYears, 'Xml', 'SchoolYear')
        --SET @Xml = dbo.XmlNormalize(@Xml)
        ---------------------------------------------
        SET @Xml = dbo.XmlAdopt(@Xml, @StudentActivities, 'Xml', 'SchoolYears')
        SET @Xml = dbo.XmlAdopt(@Xml, @Entity, 'Xml', 'Entity')
        ---------------------------------------------


ALLDONE:
        ---------------------------------------------
        -- Track Assembly time
        ---------------------------------------------
        SET @XmlAssemblyEndTime = GETDATE()
        SET @XmlAssemblyTimeInMilliseconds = DATEDIFF(ms, @XmlAssemblyStartTime, @XmlAssemblyEndTime)
        SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
        ---------------------------------------------


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

SELECT dbo.WidgetDataForStudentActivitiesForPrintAsXml(10004, 1, DEFAULT, DEFAULT, 22, 1)