USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[WidgetDataForStudentAbsencesAsXml]    Script Date: 05/04/2012 15:33:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[WidgetDataForStudentAbsencesForPrintAsXml]
(       @EntityId       int,
        @SchoolYearIdList varchar(500),
        @DataViewMode   varchar(200) = 'Standard',
        @EncryptIds     bit = 0,
        @ApplicationUserId int,
        @ApplicationUserPersonRoleId int = 1
)
RETURNS Xml
AS
BEGIN
        /*---------------------------------------------
        -- Debugging
        --SELECT SCA.StudentId, COUNT(SCA.StudentClassAbsenceId) AS AbsneceCount FROM StudentClassAbsences SCA JOIN Classes C ON SCA.ClassId = C.ClassId AND C.SchoolYearId = 12 GROUP BY SCA.StudentId ORDER BY COUNT(SCA.StudentClassAbsenceId) DESC
        ---------------------------------------------
        --SELECT dbo.WidgetDataForStudentAbsencesAsXml(49895, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
        ---------------------------------------------
        DECLARE @EntityId       int             = 7001
        DECLARE @SchoolYearIdList varchar(500)  = '5, 7, 9, 10, 11, 12'
        DECLARE @DataViewMode   varchar(200)    = 'Standard'
        DECLARE @EncryptIds     bit             = 0
        DECLARE @ApplicationUserId  int         = 22
        DECLARE @ApplicationUserPersonRoleId varchar(50)= 1
        ---------------------------------------------*/



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


        -------------------------------------------
        -- Populate @School-Year-Ids
        -------------------------------------------
        DECLARE @SchoolYearIds TABLE ( SchoolYearId int )
        -------------------------------------------
        IF(RTRIM(LTRIM(@SchoolYearIdList)) <> '' AND @SchoolYearIdList IS NOT NULL)
        BEGIN   -------------------------------------------
                INSERT  @SchoolYearIds
                SELECT  CAST(Value as int)
                FROM    dbo.SplitList(@SchoolYearIdList, ',')
                -------------------------------------------
                --SELECT * FROM @SchoolYearIds
                -------------------------------------------
        END
        ELSE
        BEGIN   -------------------------------------------
                INSERT  @SchoolYearIds
                SELECT  SchoolYearId
                FROM    SchoolYears
                -------------------------------------------
                --SELECT * FROM @SchoolYearIds
                -------------------------------------------
        END
        -------------------------------------------


        ------------------------------------
        --
        ------------------------------------
        DECLARE  @StudentAbsencesTable TABLE
        (
            AbsenceDate     date,
            ClassPeriodId   int,
            --ClassPeriod   varchar(50),
            StudentIsAbsent bit,
            StudentIsTardy  bit,
            AbsenceType     varchar(100),
        --  ClassId         int,
            SchoolYearId    int,
            RowNumber       int
        )
        ------------------------------------
        INSERT  @StudentAbsencesTable (
                AbsenceDate,
                ClassPeriodId,
                --ClassPeriod,
                StudentIsAbsent,
                StudentIsTardy,
            --  ClassId,
                SchoolYearId,
                RowNumber
        )
        SELECT  A.AbsenceDate,
                A.ClassPeriodId,
                A.StudentIsAbsent,
                A.StudentIsTardy,
                --ClassId,
                A.SchoolYearId,
                ROW_NUMBER() OVER(PARTITION BY A.AbsenceDate, A.ClassPeriodId ORDER BY A.StudentIsTardy DESC, A.StudentIsTardy DESC, A.ClassId)
        FROM    @SchoolYearIds SYID
        JOIN    StudentClassAbsences A
          ON    A.SchoolYearId = SYID.SchoolYearId
        WHERE   A.StudentId = @EntityId
        ------------------------------------
        DELETE  @StudentAbsencesTable
        WHERE   RowNumber <> 1
        ------------------------------------
        UPDATE @StudentAbsencesTable
        SET     AbsenceType = CASE
                                    WHEN(StudentIsAbsent = 1)
                                    THEN 'A'
                                    WHEN(StudentIsTardy = 1)
                                    THEN 'T'
                                    ELSE ''
                                END
        ------------------------------------
        --SELECT * FROM  @StudentAbsencesTable ORDER By AbsenceDate, ClassPeriodId
        ------------------------------------




        ------------------------------------
        --
        ------------------------------------
        DECLARE  @MaxClassPeriods TABLE
        (
            MaxClassPeriodId    int,
            SchoolYearId        int
        )
        ------------------------------------
        INSERT  @MaxClassPeriods
        SELECT  MAX(SAT.ClassPeriodId),
                SAT.SchoolYearId
        FROM    @StudentAbsencesTable SAT
        GROUP
           BY   SAT.SchoolYearId
        ------------------------------------
        --SELECT * FROM @MaxClassPeriods ORDER BY SchoolYearId
        ------------------------------------





        ------------------------------------
        --
        ------------------------------------
        DECLARE  @SchoolYearClassPeriods TABLE
        (
            ClassPeriodId   int,
            SchoolYearId    int
        )
        ------------------------------------
        DECLARE @MaxClassPeriodId int
        DECLARE @SchoolYearId int
        SELECT  @SchoolYearId = MAX(SchoolYearId)
        FROM    @MaxClassPeriods
        ------------------------------------
        WHILE(@SchoolYearId IS NOT NULL)
        BEGIN   -----------------------------------
                SELECT  @MaxClassPeriodId = MCP.MaxClassPeriodId
                FROM    @MaxClassPeriods MCP
                WHERE   MCP.SchoolYearId = @SchoolYearId
                -----------------------------------
                INSERT  @SchoolYearClassPeriods(ClassPeriodId, SchoolYearId)
                SELECT  CP.ClassPeriodId, @SchoolYearId
                FROM    ClassPeriods CP
                WHERE   CP.ClassPeriodId <= @MaxClassPeriodId
                -----------------------------------
                SELECT  @SchoolYearId = MAX(SchoolYearId)
                FROM    @MaxClassPeriods
                WHERE   SchoolYearId < @SchoolYearId
        END     -----------------------------------
        ------------------------------------
        --SELECT * FROM @SchoolYearClassPeriods
        ------------------------------------





        ------------------------------------
        --
        ------------------------------------
        DECLARE  @Days TABLE
        (
            [Date]      date,
            [Week]      int,
            [Month]     int,
            Label       varchar(100),
            SchoolYearId int,
            Ordinal     int IDENTITY(1, 1)
        )
        ------------------------------------
        INSERT  @Days([Week], [Month], [Date], Label, SchoolYearId)
        SELECT  DISTINCT
                D.[Week],
                D.[Month],
                D.[Date],
                CAST(D.[Month] as varchar(2)) + '/' + CAST(D.[Day] as varchar(2)),
                SAT.SchoolYearId
        FROM    Dates D
        JOIN    @StudentAbsencesTable SAT
          ON    D.Date = SAT.AbsenceDate
        ORDER
           BY   D.[Date] DESC
        ------------------------------------
        --SELECT * FROM @Days ORDER BY Ordinal
        ------------------------------------



        ------------------------------------
        DECLARE @StudentAbsences Xml
        -----------------------------------------------
        SET @StudentAbsences = (
                SELECT  -------------------------
                        SchoolYear.SchoolYearId,
                        SchoolYear.SchoolYear,
                        SchoolYear.SchoolYearOrdinal,
                        ---------------------------
                        [Date].[Date],
                        [Date].Label,
                        [Date].Ordinal,
                        ---------------------------
                        ClassPeriod.ClassPeriodId,
                        ClassPeriod.ClassPeriod,
                        ClassPeriod.AbsenceType
                        -------------------------
                FROM    SchoolYears SchoolYear
                JOIN    @Days [Date]
                  ON    [Date].SchoolYearId = SchoolYear.SchoolYearId
                JOIN    (
                            SELECT  SYCP.SchoolYearId,
                                    D.[Date],
                                    SYCP.ClassPeriodId,
                                    CP.ClassPeriod,
                                    SAT.AbsenceDate,
                                    SAT.AbsenceType,
                                    SAT.StudentIsAbsent,
                                    SAT.StudentIsTardy
                            FROM    SchoolYears SY
                            JOIN    @Days D
                              ON    D.SchoolYearId = SY.SchoolYearId
                            JOIN    @SchoolYearClassPeriods SYCP
                              ON    SY.SchoolYearId = SYCP.SchoolYearId
                            JOIN    ClassPeriods CP
                              ON    SYCP.ClassPeriodId = CP.ClassPeriodId
                            LEFT
                            JOIN    @StudentAbsencesTable SAT
                              ON    SAT.ClassPeriodId = SYCP.ClassPeriodId
                             AND    D.[Date] = SAT.AbsenceDate
                        ) ClassPeriod
                  ON    SchoolYear.SchoolYearId = ClassPeriod.SchoolYearId
                 AND    [Date].[Date] = ClassPeriod.[Date]
                ORDER
                   BY   SchoolYear.SchoolYearOrdinal DESC,
                        [Date].Ordinal,
                        ClassPeriod.ClassPeriodId

                FOR XML AUTO, ROOT('StudentAbsences')
        )
        -----------------------------------------------
        --SELECT @StudentAbsences AS '@StudentAbsences'
        ----------------------------------------------




        ------------------------------------
        DECLARE @SchoolYearClassPeriodsXml Xml
        -----------------------------------------------
        SET @SchoolYearClassPeriodsXml = (
                SELECT  SYCP.SchoolYearId AS '@SchoolYearId',
                        CP.ClassPeriodId AS '@ClassPeriodId',
                        CP.ClassPeriod AS '@ClassPeriod',
                        CP.ClassPeriodCode AS '@Code',
                        CP.ClassPeriodNumericalEquivalent AS '@ClassPeriodNumber',
                        CP.ClassPeriodId AS '@Ordinal'
                FROM    @SchoolYearClassPeriods SYCP
                JOIN    ClassPeriods CP
                  ON    SYCP.ClassPeriodId = CP.ClassPeriodId
                ORDER
                   BY   SYCP.SchoolYearId,
                        CP.ClassPeriodId
                FOR XML PATH('ClassPeriod'), ROOT('SchoolYearClassPeriods')
        )
        -----------------------------------------------
        --SELECT @SchoolYearClassPeriodsXml AS '@SchoolYearClassPeriodsXml'
        ----------------------------------------------





        ---------------------------------------------
        -- Assemble output Xml
        ---------------------------------------------
        SET @Xml = dbo.XmlAdopt(@Xml, @StudentAbsences, 'Xml', 'SchoolYear')
        SET @Xml = dbo.XmlJoin(@Xml, @SchoolYearClassPeriodsXml, 'SchoolYear.SchoolYearId = ClassPeriod.SchoolYearId')
        ---------------------------------------------




        ---------------------------------------------
        -- 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.WidgetDataForStudentAbsencesForPrintAsXml(7001, '5, 7, 9, 10, 11, 12', DEFAULT, 0, 22, 1)