Imports FarPoint.Win
Imports FarPoint.Win.Chart
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports FarPoint.Win.Spread.Chart
Imports FarPoint.Win.Spread.Model
Imports GrapeCity.StudentDashboard.CellTypes
Imports GrapeCity.StudentDashboard.GrapeCity.StudentDashboard.Sparkline
Imports GrapeCity.StudentDashboard.Sparkline
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.DataSourceEntities
Imports Spread.Common.DataStore.Models
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq

Partial Public Class StudentDashboardControl
    Inherits Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl
#Region "Private variables"
    Private _dataSource As StudentDashboardDataSource
    Private _students As List(Of Student)
    Private _allAbsentRecords As List(Of AbsentRecords)
    Private _allTardyRecords As List(Of TardyRecords)
    Private _allScoreRecords As List(Of ScoreRecords)
    Private _termSessions As List(Of TermSession)
    Private _absentRecords As List(Of AbsentRecord)
    Private _tardyRecords As List(Of TardyRecord)
    Private _scoreRecords As List(Of ScoreRecord)

    Private _termStartDate As DateTime
    Private _termEndDate As DateTime

    Private _timelineCell As TimelineCellType
#End Region

#Region "ctor"
    Public Sub New()
        InitializeComponent()

        _dataSource = New DataService().StudentDashboardDataSource
        _students = _dataSource.GetAllStudents()
        _allAbsentRecords = _dataSource.GetAllAbsentRecords()
        _allTardyRecords = _dataSource.GetAllTardyRecords()
        _allScoreRecords = _dataSource.GetAllScoreRecords()
        _termSessions = _dataSource.GetAllTermSessions()

        AddHandler Load, AddressOf StudentDashboardControl_Load
    End Sub
#End Region

#Region "Event handlers"
    Private Sub StudentDashboardControl_Load(sender As Object, e As EventArgs)
        'fpSpread.ActiveSheet.OpenExcel(@"Template\Dashboard.xlsx", 0);
        fpSpread.OpenExcel("Template\Dashboard.xlsx")
        fpSpread.ActiveSheet.RowCount = _students.Count + 17
        fpSpread.ActiveSheet.ColumnCount = 20

        ClearData(fpSpread.ActiveSheet)
        fpSpread.Sheets.Add(New SheetView("Data"))
        fpSpread.Sheets.Add(New SheetView("Attendance"))

        fpSpread.SuspendLayout()

        AddComboSelector(fpSpread.Sheets(0), 1, 2)

        UpdateRecords()
        SetCellTypes(fpSpread.Sheets(0))
        SetStudentDataSheetProperties(fpSpread.Sheets("Data"))
        LoadStudentAttendance(fpSpread.Sheets("Attendance"))
        LoadStudentInfo(fpSpread.Sheets(0), 5, 2)
        LoadStudentData(fpSpread.Sheets("Data"))

        fpSpread.ActiveSheet.Cells(43, 6, 45, 10).Font = New Font("Segoe", 12, FontStyle.Regular)
        fpSpread.ActiveSheet.Cells(43, 6, 45, 10).HorizontalAlignment = CellHorizontalAlignment.Center
        fpSpread.ActiveSheet.AddSpanCell(43, 6, 1, 5)
        fpSpread.ActiveSheet.Cells(43, 6).Text = "Course Grade Distribution"
        fpSpread.ActiveSheet.Cells(44, 6).ForeColor = Color.Red
        fpSpread.ActiveSheet.Cells(44, 6).Text = "F"
        fpSpread.ActiveSheet.Cells(44, 7, 44, 8).ForeColor = Color.Orange
        fpSpread.ActiveSheet.Cells(44, 7).Text = "D"
        fpSpread.ActiveSheet.Cells(44, 8).Text = "C"
        fpSpread.ActiveSheet.Cells(44, 9, 44, 10).ForeColor = Color.Green
        fpSpread.ActiveSheet.Cells(44, 9).Text = "B"
        fpSpread.ActiveSheet.Cells(44, 10).Text = "A"
        fpSpread.ActiveSheet.Columns(0).Width = 0
        fpSpread.ActiveSheet.Rows(0).Height = 0

        fpSpread.ResumeLayout(True)
    End Sub

    Private Sub ComboCell_EditingStopped(sender As Object, e As EventArgs)
        UpdateRecords()

        fpSpread.SuspendLayout()

        SetStudentDataSheetProperties(fpSpread.Sheets("Data"))
        LoadStudentAttendance(fpSpread.Sheets("Attendance"))
        LoadStudentInfo(fpSpread.Sheets(0), 5, 2)
        LoadStudentData(fpSpread.Sheets("Data"))

        fpSpread.ResumeLayout(True)
    End Sub

    ' Prevent resizing and moving of PlotArea for Chart
    Private Sub Chart_ActionDoing(sender As Object, e As CancelEventArgs)
        e.Cancel = True
    End Sub
#End Region

#Region "Private methods"
    ' Add Combobox selector for Term selection
    Private Sub AddComboSelector(sheet As SheetView, row As Integer, col As Integer)
        Dim items As New List(Of String)()
        For year As Integer = DateTime.Now.Year - 2 To DateTime.Now.Year
            items.Add("Term 1, " & year & "")
            If year = DateTime.Now.Year AndAlso DateTime.Now.Month < 7 Then
                Exit For
            End If
            items.Add("Term 2, " & year & "")
        Next

        Dim comboCell As New ComboBoxCellType() With {
             .DropDownOptions = DropDownOptions.ButtonAndText,
             .Editable = False,
             .StopEditingAfterDropDownItemSelected = True,
             .Items = items.ToArray()
        }
        AddHandler comboCell.EditingStopped, AddressOf ComboCell_EditingStopped

        sheet.Cells(row, col).ResetValue()
        sheet.Cells(row, col).CellType = comboCell
        sheet.Cells(row, col).Value = comboCell.Items.Last()
        sheet.Cells(row, col).Font = New Font("Segoe UI", 14, FontStyle.Regular)
        sheet.Cells(row, col).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(row, col).VerticalAlignment = CellVerticalAlignment.Center
        sheet.Cells(row, col).Tag = "TermComboCell"
    End Sub

    ' Update Record variable according to Combobox selection
    Private Sub UpdateRecords()
        Dim text As String = fpSpread.Sheets(0).Cells("TermComboCell").Text
        Dim term As Integer = Integer.Parse(text.Substring(5, 1))
        Dim year As Integer = Integer.Parse(text.Substring(8, 4))

        If term = 1 Then
            _termStartDate = New DateTime(year, 1, 1)

            _termEndDate = New DateTime(year, 6, 30)
        Else
            _termStartDate = New DateTime(year, 7, 1)
            _termEndDate = New DateTime(year, 12, 31)
        End If

        If _timelineCell IsNot Nothing Then
            _timelineCell.StartDate = _termStartDate
            _timelineCell.EndDate = _termEndDate
        End If
        Dim endDate As DateTime = _termEndDate

        ' For current year, retrieve data only upto current date
        If year = DateTime.Now.Year Then
            endDate = If(DateTime.Compare(DateTime.Now, _termEndDate) < 0, DateTime.Now, _termEndDate)
        End If
        Dim sessionID As Integer = _termSessions.Where(Function(session) session.TermNumber = term AndAlso session.Year = year).First().SessionID

        _absentRecords = _allAbsentRecords.Where(Function(r) r.SessionID = sessionID).First().StudentRecords.Where(Function(r) r.[Date] <= endDate).ToList()
        _tardyRecords = _allTardyRecords.Where(Function(r) r.SessionID = sessionID).First().StudentRecords.Where(Function(r) r.[Date] <= endDate).ToList()
        _scoreRecords = _allScoreRecords.Where(Function(r) r.SessionID = sessionID).First().StudentRecords
    End Sub

    ' Set cell types for Main sheet
    Private Sub SetCellTypes(sheet As SheetView)
        _timelineCell = New TimelineCellType(_termStartDate, _termEndDate)
        sheet.Cells(4, 14).CellType = _timelineCell
        sheet.Rows(4).Height = 30
    End Sub

    ' Load Attendance data in "Attendance" sheet
    Private Sub LoadStudentAttendance(sheet As SheetView)
        sheet.Reset()
        sheet.SheetName = "Attendance"
        sheet.RowCount = _students.Count * 2

        sheet.Columns(0, 1).Locked = True

        sheet.Rows(0, sheet.RowCount - 1).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Rows(0, sheet.RowCount - 1).VerticalAlignment = CellVerticalAlignment.Center
        sheet.Columns(0, sheet.ColumnCount - 1).Width = 100

        Dim maxColIndex As Integer = -1
        For i As Integer = 0 To _students.Count - 1
            sheet.AddSpanCell(2 * i, 0, 2, 1)
            sheet.Cells(2 * i, 0).Text = _students(i).Name
            sheet.Cells(2 * i, 1).Text = "Absent"
            sheet.Cells(2 * i + 1, 1).Text = "Tardy"

            Dim col As Integer = 2
            For Each record As Object In _absentRecords.Where(Function(r) r.StudentID = _students(i).ID)
                If col > maxColIndex Then
                    maxColIndex = col
                End If
                sheet.Cells(2 * i, System.Math.Max(System.Threading.Interlocked.Increment(col), col - 1)).Value = record.[Date]
            Next

            col = 2
            For Each record As Object In _tardyRecords.Where(Function(r) r.StudentID = _students(i).ID)
                If col > maxColIndex Then
                    maxColIndex = col
                End If
                sheet.Cells(2 * i + 1, System.Math.Max(System.Threading.Interlocked.Increment(col), col - 1)).Value = record.[Date]
            Next
        Next
        sheet.ColumnCount = maxColIndex + 1

        Dim dateCell As New DateTimeCellType() With {
             .DateSeparator = "-",
             .DateTimeFormat = DateTimeFormat.ShortDate,
             .DropDownButton = False
        }

        sheet.Cells(0, 2, sheet.RowCount - 1, sheet.ColumnCount - 1).CellType = dateCell

        ' Borders
        For i As Integer = 0 To sheet.RowCount - 1
            sheet.Cells(i, 1).Border = New LineBorder(Color.Gray, 1, False, False, True, True)
            sheet.Cells(i, 2, i, sheet.ColumnCount - 1).Border = New LineBorder(Color.Gray, 1, False, False, False, True)
            If i Mod 2 = 0 Then
                sheet.Cells(i, 0).Border = New LineBorder(Color.Gray, 2, False, False, True, True)
            Else
                sheet.Cells(i, 1).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(Color.Gray, 1), New ComplexBorderSide(Color.Gray, 2))
                sheet.Cells(i, 2, i, sheet.ColumnCount - 1).Border = New LineBorder(Color.Gray, 2, False, False, False, True)
            End If
        Next
    End Sub

    ' Clear Sparklines and data from template
    Private Sub ClearData(sheet As SheetView)
        sheet.SparklineContainer.Clear()
        sheet.Cells(5, 1, 35, 18).ResetValue()
    End Sub

    ' Set sheet properties for "Data" sheet
    Private Sub SetStudentDataSheetProperties(sheet As SheetView)
        sheet.Reset()
        sheet.SheetName = "Data"
        sheet.RowCount = _students.Count + 5
        sheet.ColumnCount = 26

        sheet.Rows(1, 2).Font = New Font("Segoe UI", 10, FontStyle.Bold)
        sheet.Columns(1, sheet.ColumnCount - 1).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Columns(0, sheet.ColumnCount - 1).VerticalAlignment = CellVerticalAlignment.Center

        ' Student Names
        sheet.Cells(1, 0).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(1, 0).Border = New LineBorder(Color.Gray, 2, True, True, True, True)
        sheet.Cells(3, 0, _students.Count + 1, 0).Border = New LineBorder(Color.Gray, 2, True, False, True, False)
        sheet.Cells(_students.Count + 2, 0).Border = New LineBorder(Color.Gray, 2, True, False, True, True)

        ' Late Assignment Count
        sheet.Columns(8).Width = 140

        ' Assignment Border
        sheet.Cells(1, 3, 1, 7).Border = New LineBorder(Color.Gray, 2, False, True, False, False)
        sheet.Cells(1, 8).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 1), New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 1))
        sheet.Cells(2, 3, 2, 7).Border = New LineBorder(Color.Gray, 1, False, False, False, True)
        sheet.Cells(2, 8, _students.Count + 1, 8).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 1), Nothing, New ComplexBorderSide(Color.Gray, 2), Nothing)
        sheet.Cells(_students.Count + 2, 3, _students.Count + 2, 7).Border = New LineBorder(Color.Gray, 2, False, False, False, True)
        sheet.Cells(_students.Count + 2, 8).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 1), Nothing, New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 2))

        ' Assessment Border
        sheet.Cells(1, 10).Border = New LineBorder(Color.Gray, 2, True, True, True, False)
        sheet.Cells(3, 10, _students.Count + 1, 10).Border = New LineBorder(Color.Gray, 2, True, False, False, False)
        sheet.Cells(2, 11, 2, 13).Border = New LineBorder(Color.Gray, 1, False, False, False, True)
        sheet.Cells(3, 14, _students.Count + 1, 14).Border = New LineBorder(Color.Gray, 2, False, False, True, False)
        sheet.Cells(_students.Count + 2, 11, _students.Count + 2, 13).Border = New LineBorder(Color.Gray, 2, False, False, False, True)

        sheet.Cells(2, 10).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 2), Nothing, Nothing, New ComplexBorderSide(Color.Gray, 1))
        sheet.Cells(2, 14).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 1))
        sheet.Cells(_students.Count + 2, 14).Border = New LineBorder(Color.Gray, 2, False, False, True, True)
        sheet.Cells(_students.Count + 2, 10).Border = New LineBorder(Color.Gray, 2, True, False, False, True)

        ' Grades
        sheet.Columns(16, 19).Width = 130

        ' Grades Border
        sheet.Cells(1, 16).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 2), Nothing, New ComplexBorderSide(Color.Gray, 1))
        sheet.Cells(1, 17, 1, 18).Border = New ComplexBorder(Nothing, New ComplexBorderSide(Color.Gray, 2), Nothing, New ComplexBorderSide(Color.Gray, 1))
        sheet.Cells(1, 19).Border = New ComplexBorder(Nothing, New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 1))

        sheet.Cells(3, 16, _students.Count + 1, 16).Border = New LineBorder(Color.Gray, 2, True, False, False, False)
        sheet.Cells(3, 19, _students.Count + 1, 19).Border = New LineBorder(Color.Gray, 2, False, False, True, False)
        sheet.Cells(_students.Count + 2, 17, _students.Count + 2, 18).Border = New LineBorder(Color.Gray, 2, False, False, False, True)
        sheet.Cells(_students.Count + 2, 16).Border = New LineBorder(Color.Gray, 2, True, False, False, True)
        sheet.Cells(_students.Count + 2, 19).Border = New LineBorder(Color.Gray, 2, False, False, True, True)

        ' Referral & Detention Count
        sheet.Columns(21, 24).Width = 100

        ' Referral & Detention Border
        sheet.Cells(1, 21).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 1), Nothing)
        sheet.Cells(2, 21).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 2), Nothing, Nothing, New ComplexBorderSide(Color.Gray, 1))
        sheet.Cells(2, 22).Border = New LineBorder(Color.Gray, 1, False, False, True, True)
        sheet.Cells(1, 23).Border = New LineBorder(Color.Gray, 2, False, True, True, False)
        sheet.Cells(2, 23).Border = New LineBorder(Color.Gray, 1, False, False, False, True)
        sheet.Cells(2, 24).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 1))

        sheet.Cells(3, 21, _students.Count + 1, 21).Border = New LineBorder(Color.Gray, 2, True, False, False, False)
        sheet.Cells(3, 22, _students.Count + 1, 22).Border = New LineBorder(Color.Gray, 1, False, False, True, False)
        sheet.Cells(3, 24, _students.Count + 1, 24).Border = New LineBorder(Color.Gray, 2, False, False, True, False)
        sheet.Cells(_students.Count + 2, 21).Border = New LineBorder(Color.Gray, 2, True, False, False, True)
        sheet.Cells(_students.Count + 2, 22).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(Color.Gray, 1), New ComplexBorderSide(Color.Gray, 2))
        sheet.Cells(_students.Count + 2, 23).Border = New LineBorder(Color.Gray, 2, False, False, False, True)
        sheet.Cells(_students.Count + 2, 24).Border = New LineBorder(Color.Gray, 2, False, False, True, True)

        ' Lock headers
        sheet.Cells(1, 0, _students.Count + 2, 0).Locked = True
        sheet.Cells(1, 3, 2, 8).Locked = True
        sheet.Cells(1, 10, 2, 14).Locked = True
        sheet.Cells(1, 16, 1, 19).Locked = True
        sheet.Cells(1, 21, 2, 24).Locked = True
    End Sub

    ' Load raw Student stats in "Data" sheet
    Private Sub LoadStudentData(sheet As SheetView)
        sheet.AddSpanCell(1, 0, 2, 3)
        sheet.Cells(1, 0).Text = "Student"

        sheet.AddSpanCell(1, 3, 1, 5)
        sheet.Cells(1, 3).Text = "Assignments"

        sheet.SetClipValue(2, 3, 1, 5, "1" & vbTab & "2" & vbTab & "3" & vbTab & "4" & vbTab & "5")

        sheet.AddSpanCell(1, 8, 2, 1)
        sheet.Cells(1, 8).Text = "Late Assignments"

        sheet.AddSpanCell(1, 10, 1, 5)
        sheet.Cells(1, 10).Text = "Assessments"
        sheet.Cells(1, 10, 2, 14).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(1, 10, 2, 14).Font = New Font("Segoe UI", 10, FontStyle.Bold)

        sheet.SetClipValue(2, 10, 1, 5, "1" & vbTab & "2" & vbTab & "3" & vbTab & "4" & vbTab & "5")

        sheet.AddSpanCell(1, 16, 2, 1)
        sheet.AddSpanCell(1, 17, 2, 1)
        sheet.AddSpanCell(1, 18, 2, 1)
        sheet.AddSpanCell(1, 19, 2, 1)
        sheet.Cells(1, 16).Text = "Current Score"
        sheet.Cells(1, 17).Text = "Current Grade"
        sheet.Cells(1, 18).Text = "Target Score"
        sheet.Cells(1, 19).Text = "Previous Term Score"

        sheet.AddSpanCell(1, 21, 1, 2)
        sheet.AddSpanCell(1, 23, 1, 2)
        sheet.Cells(1, 21).Text = "Referrals"
        sheet.Cells(1, 23).Text = "Detentions"
        sheet.SetClip(2, 21, 1, 4, "Previous Term" & vbTab & "Current Term" & vbTab & "Previous Term" & vbTab & "Current Term")

        For i As Integer = 0 To _students.Count - 1
            sheet.AddSpanCell(i + 3, 0, 1, 3)
            sheet.Cells(i + 3, 0).Text = _students(i).Name

            Dim studentRecord As ScoreRecord = _scoreRecords.Where(Function(r) r.StudentID = _students(i).ID).FirstOrDefault()
            For j As Integer = 0 To studentRecord.AssignmentScores.Count - 1
                sheet.Cells(i + 3, j + 3).Value = studentRecord.AssignmentScores(j)
                sheet.Cells(i + 3, j + 10).Value = studentRecord.AssessmentScores(j)
            Next

            sheet.Cells(i + 3, 8).Value = If(studentRecord.LateAssignmentCount = 0, String.Empty, studentRecord.LateAssignmentCount.ToString())

            sheet.Cells(i + 3, 21).Value = If(studentRecord.PreviousTermReferralCount = 0, String.Empty, studentRecord.PreviousTermReferralCount.ToString())
            sheet.Cells(i + 3, 22).Value = If(studentRecord.CurrentTermReferralCount = 0, String.Empty, studentRecord.CurrentTermReferralCount.ToString())
            sheet.Cells(i + 3, 23).Value = If(studentRecord.PreviousTermDetentionCount = 0, String.Empty, studentRecord.PreviousTermDetentionCount.ToString())
            sheet.Cells(i + 3, 24).Value = If(studentRecord.CurrentTermDetentionCount = 0, String.Empty, studentRecord.CurrentTermDetentionCount.ToString())

            sheet.Cells(i + 3, 16).CellType = New NumberCellType() With {
                 .DecimalPlaces = 0
            }
            ' Course Score = Average(Assignemnt Scores, Assessment Scores)
            sheet.Cells(i + 3, 16).Formula = "AVERAGE(Data!$D$" & i + 4 & ":$H$" & i + 4 & ", Data!$J$" & i + 4 & ":" & "$N$" & i + 4 & ")"

            ' Course Grade Scheme:
            ' Score >= 90 --> 'A'
            ' Score >= 80 --> 'B'
            ' Score => 70 --> 'C'
            ' Score => 60 --> 'D'
            ' Score < 60  --> 'F'
            sheet.Cells(i + 3, 17).Formula = "IF($Q$" & i + 4 & " >= 90, ""A"", IF($Q$" & i + 4 & " >= 80, ""B"", " & "IF($Q$" & i + 4 & " >= 70, ""C"", IF($Q$" & i + 4 & " >= 60, ""D"", ""F""))))"
            sheet.Cells(i + 3, 18).Value = studentRecord.TargetCourseScore
            sheet.Cells(i + 3, 19).Value = studentRecord.PreviousCourseScore
        Next
    End Sub

    ' Load Student Stats and Sparklines from data sheets
    Private Sub LoadStudentInfo(sheet As SheetView, row As Integer, col As Integer)
        sheet.Cells("TermComboCell").Locked = False

        For i As Integer = 0 To _students.Count - 1
            sheet.Cells(row + i, col).Value = _students(i).Name
            sheet.Cells(row + i, col + 1).Value = If(_students(i).EnglishLanguageDeficient, "E", String.Empty)
            sheet.Cells(row + i, col + 2).Value = If(_students(i).SpecialEdStudent, "S", String.Empty)

            sheet.Cells(row + i, col + 5).Formula = "Data!$Q$" & i + 4 & ""
            sheet.Cells(row + i, col + 3).Formula = "Data!$R$" & i + 4 & ""
            sheet.Cells(row + i, col + 11).Formula = "Data!$N$" & i + 4 & ""

            ' Overall Course Grade Sparkline
            Dim sparkline As New BulletSparkline(sheet, row + i, 6, New SheetCellRange(sheet, row + i, 7, 1, 1), New SheetCellRange(fpSpread.Sheets("Data"), i + 3, 18, 1, 1))
            Dim group As New BulletSparklineGroup()
            Dim setting As New BulletSparklineSetting(0, Color.FromArgb(244, 232, 217), False)
            sheet.SparklineContainer.Add(group)
            group.Setting = setting
            group.Renderer = New BulletSparklineRenderer(100, 90, 60, True)
            group.Add(sparkline)

            ' Spread Sparkline
            AddAssignmentSparkline(sheet, row + i, 9, New SheetCellRange(fpSpread.Sheets("Data"), i + 3, 3, 1, 5), New DistributionSparklineRenderer())

            ' Last Trend Sparkline
            sparkline = New BulletSparkline(sheet, row + i, 10, New SheetCellRange(sheet, row + i, 7, 1, 1), New SheetCellRange(fpSpread.Sheets("Data"), i + 3, 3, 1, 5))
            group = New BulletSparklineGroup()
            sheet.SparklineContainer.Add(group)
            group.Setting = setting
            group.Renderer = New BulletSparklineRenderer(100, 90, 60)
            group.Add(sparkline)

            ' YTD Sparkline
            sheet.AddSparkline(New SheetCellRange(fpSpread.Sheets("Data"), i + 3, 3, 1, 5), New CellRange(row + i, col + 6, 1, 1), SparklineType.Line, New ExcelSparklineSetting())

            ' Late Assignment Count
            sheet.Cells(row + i, col + 9).Formula = "Data!$I$" & i + 4 & ""

            ' Last 5 Assessments
            sheet.AddSparkline(New SheetCellRange(fpSpread.Sheets("Data"), i + 3, 9, 1, 5), New CellRange(row + i, col + 10, 1, 1), SparklineType.Line, New ExcelSparklineSetting())

            ' Referral & Detention Counts
            sheet.Cells(row + i, col + 13).Formula = "Data!$V$" & i + 4 & ""
            sheet.Cells(row + i, col + 14).Formula = "Data!$W$" & i + 4 & ""
            sheet.Cells(row + i, col + 15).Formula = "Data!$X$" & i + 4 & ""
            sheet.Cells(row + i, col + 16).Formula = "Data!$Y$" & i + 4 & ""

            AddAttendanceSparkline(sheet, row + i, 14)
        Next
        AddCharts(sheet)
    End Sub

    ' Add Charts for Absence and Tardiness
    Private Sub AddCharts(sheet As SheetView)
        sheet.Charts.Clear()

        sheet.Rows(35, 42).Height = 30

        sheet.Cells(37, 14).ResetText()
        sheet.Cells(41, 14).ResetText()

        Dim font As New Font("Segoe UI", 11, FontStyle.Bold)
        sheet.AddSpanCell(36, 12, 2, 2)
        sheet.Cells(36, 12).Font = font
        sheet.Cells(36, 12).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(36, 12).VerticalAlignment = CellVerticalAlignment.Center
        sheet.Cells(36, 12).ResetForeColor()
        sheet.Cells(36, 12).ForeColor = Color.DarkOrange
        sheet.Cells(36, 12).Text = "Absences"

        sheet.AddSpanCell(40, 12, 2, 2)
        sheet.Cells(40, 12).Font = font
        sheet.Cells(40, 12).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(40, 12).VerticalAlignment = CellVerticalAlignment.Center
        sheet.Cells(40, 12).ResetForeColor()
        sheet.Cells(40, 12).ForeColor = Color.DodgerBlue
        sheet.Cells(40, 12).Text = "Tardiness"

        AddChart(sheet, 35, 14, 38, 14, _absentRecords.[Select](Function(r) DirectCast(r, IStudentRecord)).ToList(),
            Color.DarkOrange)
        AddChart(sheet, 39, 14, 42, 14, _tardyRecords.[Select](Function(r) DirectCast(r, IStudentRecord)).ToList(),
            Color.DodgerBlue)
    End Sub

    ' Add a Chart
    Private Sub AddChart(sheet As SheetView, start_row As Integer, start_col As Integer, end_row As Integer, end_col As Integer, record As List(Of IStudentRecord),
        fillColor As Color)
        Dim width As Integer = -1, height As Integer = -1
        Dim location As Point = GetCellRangeLocation(sheet, start_row, start_col, end_row, end_col, width,
            height)
        Dim chart As New SpreadChart()
        chart.CanMove = FarPoint.Win.Spread.DrawingSpace.Moving.None
        chart.CanSize = FarPoint.Win.Spread.DrawingSpace.Sizing.None
        AddHandler chart.ActionDoing, AddressOf Chart_ActionDoing
        chart.Size = New Size(width, height)
        chart.Location = location

        Dim model As New ChartModel()
        Dim plotArea As New YPlotArea()
        plotArea.YAxes(0).MajorGridVisible = False
        plotArea.YAxes(0).AutoMajorUnit = False
        plotArea.YAxes(0).AutoMinimum = False
        plotArea.YAxes(0).Minimum = 0
        plotArea.YAxes(0).MajorUnit = 1

        plotArea.XAxis.LabelVisible = False
        plotArea.XAxis.MajorGridVisible = False
        plotArea.XAxis.MajorTickVisible = False
        Dim series As New LineSeries()
        series.PointMarker = New BuiltinMarker(MarkerShape.Dot, 0.2F)
        series.PointFill = New SolidFill(fillColor)
        series.SmoothedLine = True
        series.LineBorder = New SolidLine(fillColor, 0.5F)

        Dim [date] As DateTime = _termStartDate
        Dim endDate As DateTime = _termEndDate
        
        While [date] <= endDate
            Dim count As Integer = CType(record, IEnumerable(Of IStudentRecord)).Count(Function(r) r.[Date] = [date])
            series.Values.Add(CType(record, IEnumerable(Of IStudentRecord)).Count(Function(r) r.[Date] = [date]))
            [date] = [date].AddDays(1)
        End While
        plotArea.Series.Add(series)
        model.PlotAreas.Add(plotArea)
        chart.Model = model

        sheet.Charts.Add(chart)
    End Sub

    ' Get location and size for a Chart
    Private Function GetCellRangeLocation(sheet As SheetView, start_row As Integer, start_col As Integer, end_row As Integer, end_col As Integer, ByRef width As Integer,
        ByRef height As Integer) As Point
        Dim x As Single = 0, y As Single = 0
        For i As Integer = 0 To start_row - 1
            y += sheet.Rows(i).Height
        Next
        For i As Integer = 0 To start_col - 1
            x += sheet.Columns(i).Width
        Next

        Dim w As Single = 0, h As Single = 0
        For i As Integer = start_row To end_row
            h += sheet.Rows(i).Height
        Next
        For i As Integer = start_col To end_col
            w += sheet.Columns(i).Width
        Next
        width = CInt(Math.Truncate(w)) - 5
        height = CInt(Math.Truncate(h)) - 5

        Return New Point(CInt(Math.Truncate(x)) + 2, CInt(Math.Truncate(y)) + 2)
    End Function

    ' Add Assignment sparkline
    Private Sub AddAssignmentSparkline(sheet As SheetView, row As Integer, col As Integer, cellRange As SheetCellRange, renderer As ISparklineRenderer)
        Dim sparkline As New DistributionSparkline(sheet, row, col, cellRange)
        Dim group As New DistributionSparklineGroup()
        sheet.SparklineContainer.Add(group)
        group.Setting = New DistributionSparklineSetting()
        group.Renderer = renderer
        group.Add(sparkline)
    End Sub

    ' Add Attendance sparkline
    Private Sub AddAttendanceSparkline(sheet As SheetView, row As Integer, col As Integer)
        Dim studentName As String = TryCast(sheet.Cells(row, 2).Value, String)
        Dim rowIndex As Integer = GetAttendanceRowIndexForStudent(studentName)
        Dim studentID As Integer = _students.Where(Function(stu) stu.Name = studentName).First().ID

        Dim absentCount As Integer = _absentRecords.Where(Function(r) r.StudentID = studentID).Count()
        Dim tardyCount As Integer = _tardyRecords.Where(Function(r) r.StudentID = studentID).Count()

        Dim sparkline As New DistributionSparkline(sheet, row, col, New SheetCellRange(fpSpread.Sheets("Attendance"), rowIndex, 2, 1, absentCount), New SheetCellRange(fpSpread.Sheets("Attendance"), rowIndex + 1, 2, 1, tardyCount))
        Dim group As New DistributionSparklineGroup()
        sheet.SparklineContainer.Add(group)
        group.Setting = New DistributionSparklineSetting()
        group.Renderer = New AttendanceSparklineRenderer(_termStartDate, _termEndDate)
        group.Add(sparkline)
    End Sub

    ' Get row index of a given Student in "Attendance" sheet
    Private Function GetAttendanceRowIndexForStudent(studentName As String) As Integer
        Dim sheet As SheetView = fpSpread.Sheets("Attendance")

        For i As Integer = 0 To _students.Count - 1
            If sheet.Cells(2 * i, 0).Text = studentName Then
                Return i
            End If
        Next
        Return -1
    End Function
#End Region
End Class

'
'
'

Partial Class StudentDashboardControl
	''' <summary> 
	''' Required designer variable.
	''' </summary>
	Private components As System.ComponentModel.IContainer = Nothing

    ''' <summary> 
    ''' Clean up any resources being used.
    ''' </summary>
    ''' <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    Protected Sub Dispose(disposing As Boolean)
        If disposing AndAlso (components IsNot Nothing) Then
            components.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub

#Region "Component Designer generated code"

    ''' <summary> 
    ''' Required method for Designer support - do not modify 
    ''' the contents of this method with the code editor.
    ''' </summary>
    Private Sub InitializeComponent()
		Me.fpSpread = New FarPoint.Win.Spread.FpSpread()
		Me.fpSpread_Sheet1 = New FarPoint.Win.Spread.SheetView()
		Me.pnlMain.SuspendLayout()
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Controls.Add(Me.fpSpread)
		' 
		' fpSpread
		' 
		Me.fpSpread.AccessibleDescription = ""
		Me.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
		Me.fpSpread.Location = New System.Drawing.Point(0, 0)
		Me.fpSpread.Name = "fpSpread"
		Me.fpSpread.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me.fpSpread_Sheet1})
		Me.fpSpread.Size = New System.Drawing.Size(477, 487)
		Me.fpSpread.TabIndex = 0
		' 
		' fpSpread_Sheet1
		' 
		Me.fpSpread_Sheet1.Reset()
		Me.fpSpread_Sheet1.SheetName = "Sheet1"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me.fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' StudentDashboardControl
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "StudentDashboardControl"
		Me.pnlMain.ResumeLayout(False)
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)

	End Sub

	#End Region

	Private fpSpread As FarPoint.Win.Spread.FpSpread
	Private fpSpread_Sheet1 As FarPoint.Win.Spread.SheetView
End Class
