Imports System.Collections.Generic
Imports System.Data
Imports System.Globalization
Imports System.Linq
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.DataSourceEntities
Imports Spread.Common.DataStore.Models
Imports Spread.Common.Features.Spread.Common.Features.Sample

Partial Public Class StudentCalendarControl
    Inherits BaseSampleControl
#Region "Fields"
    Private _dateTimeFormatInfo As DateTimeFormatInfo
    Private _dataSource As StudentCalendarDataSource
    Private _schedule As List(Of ScheduleEntry)
    Private _assignments As List(Of Assignment)
#End Region

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

        _dateTimeFormatInfo = New DateTimeFormatInfo()
        _dataSource = New DataService().StudentCalendarDataSource
        _schedule = _dataSource.GetSchedule()
        _assignments = _dataSource.GetAssignments(DateTime.Now.Year)

        AddHandler Load, AddressOf Form1_Load
        AddHandler fpSpread.Change, AddressOf FpSpread_Change
    End Sub
#End Region

#Region "Event handlers"
    Private Sub FpSpread_Change(sender As Object, e As ChangeEventArgs)
        Dim sheet As SheetView = fpSpread.Sheets(e.View.ActiveSheetIndex)
        If sheet.SheetName = "January" AndAlso e.Row = 1 AndAlso e.Column = 13 Then
            Dim isYearValid As Boolean = False
            Dim year As Integer = -1
            isYearValid = Integer.TryParse(sheet.Cells(e.Row, e.Column).Text, year)

            Dim [date] As DateTime = DateTime.MinValue
            isYearValid = DateTime.TryParse("1/1/{year}", [date])

            ' Clear Assignment and Weekly Schedule
            _assignments = _dataSource.GetAssignments(year)
            For month As Integer = 1 To 12
                If isYearValid Then
                    _assignments = _dataSource.GetAssignments(year)
                    AddAssignmentsToSheet(month)
                End If
            Next
        End If
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs)
        fpSpread.SuspendLayout()
        fpSpread.OpenExcel("Template\StudentCalendar.xlsx")
        fpSpread.TabStripInsertTab = False

        For i As Integer = 0 To fpSpread.Sheets.Count - 1
            SetSheetProperties(fpSpread.Sheets(i))
            AddCalendarToSheet(i + 1)
            AddAssignmentsToSheet(i + 1)
        Next

        fpSpread.ResumeLayout(True)
    End Sub
#End Region

#Region "Private methods"
    Private Sub SetSheetProperties(sheet As SheetView)
        sheet.ColumnHeader.Visible = False
        sheet.RowHeader.Visible = False
        sheet.HorizontalGridLine = New GridLine(GridLineType.None)
        sheet.VerticalGridLine = New GridLine(GridLineType.None)
    End Sub

    Private Sub AddCalendarToSheet(month As Integer)
        If month < 1 OrElse month > 12 Then
            Throw New ArgumentException("month must be between 1 and 12")
        End If
        Dim monthName As String = _dateTimeFormatInfo.MonthNames(month - 1)
        Dim sheet As SheetView = fpSpread.Sheets.Find(monthName)
        If sheet Is Nothing Then
            Throw New Exception("Could not find sheet for the month of {monthName}")
        End If

        Dim dateCell As New DateTimeCellType()
        dateCell.DateTimeFormat = DateTimeFormat.UserDefined
        dateCell.UserDefinedFormat = "d"

        Dim year As Integer = DateTime.Now.Year
        If month = 1 Then
            sheet.Cells(1, 13).Value = year
            sheet.Cells(1, 13).Locked = False
        End If
        sheet.Cells(1, 1).Value = month
        sheet.Cells(6, 1).Value = _dateTimeFormatInfo.AbbreviatedMonthNames(month - 1)

        Dim baseRow As Integer = 3, baseCol As Integer = 2, offset As Integer = 1
        For r As Integer = 0 To 5
            For c As Integer = 0 To 6

                sheet.Cells(baseRow + r, baseCol + c).Formula = "DATE(CALENDARYEAR," & month & ", 1) - " & "WEEKDAY(DATE(CALENDARYEAR, " & month & ", 1)) + " & offset & " "
                offset += 1
                'sheet.Cells(baseRow + r, baseCol + c).Formula = $"DATE(CALENDARYEAR, {month}, 1) - " & "WEEKDAY(DATE(CALENDARYEAR, {month}, 1)) + {offset + +}"
                sheet.Cells(baseRow + r, baseCol + c).CellType = dateCell
            Next
        Next

        AddWeeklySchedule(sheet)
    End Sub

    Private Sub AddAssignmentsToSheet(month As Integer)
        If month < 1 OrElse month > 12 Then
            Throw New ArgumentException("month must be between 1 and 12")
        End If
        Dim monthName As String = _dateTimeFormatInfo.MonthNames(month - 1)
        Dim sheet As SheetView = fpSpread.Sheets.Find(monthName)
        If sheet Is Nothing Then
            Throw New Exception("Could not find sheet for the month of {monthName}")
        End If

        sheet.Cells(3, 11, 31, 12).ResetValue()
        sheet.ClearRange(3, 11, 29, 2, True)
        Dim currentMonthAssignments As List(Of Assignment) = _assignments.Where(Function(asgnmt) asgnmt.[Date].Month = month).ToList()

        Dim baseRow As Integer = 3, col As Integer = 11
        Dim dayOfWeek__1 As DayOfWeek = DayOfWeek.Monday
        While dayOfWeek__1 <> DayOfWeek.Saturday
            Dim assignments As List(Of Assignment) = currentMonthAssignments.Where(Function(asgnmt) asgnmt.[Date].DayOfWeek = dayOfWeek__1).ToList()
            For i As Integer = 0 To Math.Min(assignments.Count, 5) - 1
                sheet.Cells(baseRow + i, col).Value = assignments(i).[Date].Day
                sheet.Cells(baseRow + i, col + 1).Value = assignments(i).Description
            Next
            dayOfWeek__1 = CType(CInt(dayOfWeek__1) + 1, DayOfWeek)
            baseRow += 6
        End While
    End Sub

    Private Sub AddWeeklySchedule(sheet As SheetView)
        Dim row As Integer = 13, col As Integer = 1
        For i As Integer = 0 To _schedule.Count - 1

            sheet.Cells(row + (i \ 5) * 2, col).Value = $" " & _schedule(i).Hour & ":00"
            sheet.Cells(row + (i \ 5) * 2 + 1, col).Value = _schedule(i).Subject

            col += If((i Mod 5 = 0), 1, 2)
            If (i + 1) Mod 5 = 0 Then
                col = 1
            End If
        Next
    End Sub
#End Region
End Class

'
'
'

Partial Class StudentCalendarControl
	''' <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 Shadows 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(FarPoint.Win.Spread.LegacyBehaviors.None)
		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
		' 
		' StudentCalendarControl
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "StudentCalendarControl"
		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
