Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.Models
Imports FarPoint.Win
Imports FarPoint.Win.Spread.Model
Imports FarPoint.Win.Spread.Data
Imports System.Dynamic
Imports System.IO
Imports Spread.Common.Features.Spread.Common.Features.Sample
Imports GrapeCity.EmployeeAbsenceSchedule.GrapeCity.EmployeeAbsenceSchedule

Partial Public Class AbsenceScheduleControl
    Inherits BaseSampleControl
#Region "Members"
    Private _dataService As DataService

    Private fpSpread As FpSpread
    Private cmbocellMonth As ComboBoxCellType = Nothing
    Private cmbocellYear As ComboBoxCellType = Nothing
    Private sRule As UnaryComparisonConditionalFormattingRule
    Private vRule As UnaryComparisonConditionalFormattingRule
    Private pRule As UnaryComparisonConditionalFormattingRule
    Private c1Rule As UnaryComparisonConditionalFormattingRule
    Private c2Rule As UnaryComparisonConditionalFormattingRule
    Private cf As ConditionalFormatting
    Private mainTable As New DataTable()
    Private empList As List(Of Employee) = Nothing
    Private absenceSheduleList As List(Of AbsenceSchedule) = Nothing
    Private month As List(Of String) = Nothing
    Private year As List(Of String) = Nothing
    Private selectedYr As Integer = 2018
    Private selectedMonth As Integer = 1
#End Region

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

        AddUIControls()

        AddHandler Load, AddressOf AbsenceSchedule_Load
    End Sub
#End Region

#Region "Events"
    Private Sub AbsenceSchedule_HandleDestroyed(sender As Object, e As EventArgs)
        _dataService.JsonDataSource.SaveObjectToJsonFile(Path.Combine("Data", "AbsenceSchedule.json"), absenceSheduleList)
    End Sub
    Private Sub AbsenceSchedule_Load(sender As Object, e As EventArgs)
        _dataService = New DataService()

        'set rows and columns count
        Dim colCnt As Integer = InlineAssignHelper(fpSpread.Sheets(0).Columns.Count, 35)
        Dim rowCnt As Integer = InlineAssignHelper(fpSpread.Sheets(0).Rows.Count, 7)

        'set FrozenRowCount 
        fpSpread.Sheets(0).FrozenRowCount = 7
        'set border            
        fpSpread.ActiveSheet.Rows(0, 4).Border = New LineBorder(Color.Transparent, 1)
        fpSpread.AllowCellOverflow = True
        fpSpread.AllowEditOverflow = True
        AddEventHandlers()
        SetCellText()
        SetAlignmentForSpecificCells()
        SetEditingToFalseForCells()

        empList = _dataService.JsonDataSource.GetAllEmployees()
        absenceSheduleList = _dataService.JsonDataSource.GetAbsenceScheduleList()

        SetColumnsRowsVisibility()
        SetRowsHieghtAndColumnWidth(rowCnt, colCnt)
        SetAlignmentforAllRows()
        SetCellSpans()
        SetScrollBars()
        SetColor()
        SetFont()
        AddComboBox(3, 33)
        CreateCondionalRules()
        UpdateDateHeaderAndDateTable(New DateTime(selectedYr, selectedMonth, 1), empList, absenceSheduleList)
        UpdateDataOnSpread()
        SetEditingToFalseForCells()

    End Sub
    Private Sub FpSpread_Change(sender As Object, e As ChangeEventArgs)
        If e.Row <= fpSpread.Sheets(0).Rows.Count - 2 AndAlso e.Row >= 7 AndAlso e.Column <= fpSpread.Sheets(0).Columns.Count - 3 AndAlso e.Column >= 2 Then
            fpSpread.Sheets(0).Cells(e.Row, e.Column).Text = fpSpread.Sheets(0).Cells(e.Row, e.Column).Text.Trim().ToUpper()
            SaveChangesBackToList()
        End If
    End Sub
    Private Sub FpSpread_ComboSelChange(sender As Object, e As EditorNotifyEventArgs)
        Dim _ctrlTxt As String = TryCast(e.EditingControl, FpCombo).SelectedItem.ToString()
        selectedMonth = If(month.IndexOf(_ctrlTxt) >= 0, month.IndexOf(_ctrlTxt) + 1, selectedMonth)
        selectedYr = If(year.IndexOf(_ctrlTxt) >= 0, Convert.ToInt32(year(year.IndexOf(_ctrlTxt))), selectedYr)

        UpdateDateHeaderAndDateTable(New DateTime(selectedYr, selectedMonth, 1), empList, absenceSheduleList)
        UpdateDataOnSpread()
    End Sub
#End Region

#Region "PrivateMethods"
    Private Sub AddUIControls()
        fpSpread = New FpSpread(LegacyBehaviors.None)
        fpSpread.Sheets.Clear()
        fpSpread.Sheets.Add(New SheetView())
        fpSpread.Dock = DockStyle.Fill

        pnlMain.Controls.Add(fpSpread)
    End Sub
    Private Sub AddEventHandlers()
        AddHandler fpSpread.ComboSelChange, AddressOf FpSpread_ComboSelChange
        AddHandler fpSpread.Change, AddressOf FpSpread_Change
        AddHandler HandleDestroyed, AddressOf AbsenceSchedule_HandleDestroyed
    End Sub
    Private Sub SetEditingToFalseForCells()
        fpSpread.Sheets(0).Rows(0, 6).Locked = True
        fpSpread.Sheets(0).Columns(fpSpread.Sheets(0).ColumnCount - 2, fpSpread.Sheets(0).ColumnCount - 1).Locked = True
        fpSpread.Sheets(0).Cells(4, fpSpread.Sheets(0).ColumnCount - 2, 4, fpSpread.Sheets(0).ColumnCount - 1).Locked = False
        'Cells should be set last  
    End Sub
    Private Sub SetAlignmentForSpecificCells()
        fpSpread.Sheets(0).Cells(2, 3, 2, 3).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(2, 8, 2, 8).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(2, 13, 2, 13).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(2, 18, 2, 18).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(2, 23, 2, 23).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(6, 33, 6, 33).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(2, 3, 2, 3).CellPadding.Left = 5
        fpSpread.Sheets(0).Cells(2, 8, 2, 8).CellPadding.Left = 5
        fpSpread.Sheets(0).Cells(2, 13, 2, 13).CellPadding.Left = 5
        fpSpread.Sheets(0).Cells(2, 18, 2, 18).CellPadding.Left = 5
        fpSpread.Sheets(0).Cells(2, 23, 2, 23).CellPadding.Left = 5
        fpSpread.Sheets(0).Cells(6, 33, 6, 33).CellPadding.Left = 5
    End Sub
    Private Sub SetCellText()
        fpSpread.Sheets(0).Cells(0, 1).Text = "Employee Absence Schedule"
        fpSpread.Sheets(0).Cells(2, 1).Text = "Absence Type Key"
        fpSpread.Sheets(0).Cells(2, 2).Text = "V"
        fpSpread.Sheets(0).Cells(2, 3).Text = "Vacation"
        fpSpread.Sheets(0).Cells(2, 7).Value = "P"
        fpSpread.Sheets(0).Cells(2, 8).Text = "Personal"
        fpSpread.Sheets(0).Cells(2, 12).Value = "S"
        fpSpread.Sheets(0).Cells(2, 13).Text = "Sick"
        fpSpread.Sheets(0).Cells(2, 17).Text = "C1"
        fpSpread.Sheets(0).Cells(2, 18).Text = "Custom 1"
        fpSpread.Sheets(0).Cells(2, 22).Text = "C2"
        fpSpread.Sheets(0).Cells(2, 23).Text = "Custom 2"
        fpSpread.Sheets(0).Cells(4, 1).Text = "Summary"
        fpSpread.Sheets(0).Cells(4, 2).Text = "Dates of Absence"
    End Sub
    Private Sub SetRowsHieghtAndColumnWidth(rowCont As Integer, colCnt As Integer)
        fpSpread.Sheets(0).Rows(0).Height = 70
        fpSpread.Sheets(0).Rows(4).Height = 40
        fpSpread.Sheets(0).Columns(1).Width = 150
        fpSpread.Sheets(0).Columns(2, colCnt - 2).Width = 30
        fpSpread.Sheets(0).Columns(colCnt - 2, colCnt - 1).Width = 100
    End Sub
    Private Sub SetCellSpans()
        'Span column, rows and cells           
        fpSpread.Sheets(0).AddSpanCell(0, 1, 2, 34)
        fpSpread.Sheets(0).AddSpanCell(2, 3, 1, 4)
        fpSpread.Sheets(0).AddSpanCell(2, 8, 1, 4)
        fpSpread.Sheets(0).AddSpanCell(2, 13, 1, 4)
        fpSpread.Sheets(0).AddSpanCell(2, 18, 1, 4)
        fpSpread.Sheets(0).AddSpanCell(2, 23, 1, 4)
        fpSpread.Sheets(0).AddSpanCell(4, 2, 1, 31)
        fpSpread.Sheets(0).AddSpanCell(5, 33, 1, 2)
        fpSpread.Sheets(0).AddSpanCell(6, 33, 1, 2)
    End Sub
    Private Sub SetColumnsRowsVisibility()
        'Set visibility of headers and columns
        fpSpread.Sheets(0).ColumnHeader.Visible = False
        fpSpread.Sheets(0).RowHeader.Visible = False
        fpSpread.Sheets(0).Columns(0).Visible = False
    End Sub
    Private Sub SetScrollBars()
        'Set scrollbar
        fpSpread.HorizontalScrollBarPolicy = ScrollBarPolicy.AsNeeded
        fpSpread.VerticalScrollBarPolicy = ScrollBarPolicy.AsNeeded
    End Sub
    Private Sub SetColor()
        'set color
        fpSpread.Sheets(0).Rows(2).BackColor = Color.FromArgb(187, 189, 178)
        fpSpread.Sheets(0).Rows(3).BackColor = Color.FromArgb(198, 198, 198)
        fpSpread.Sheets(0).Rows(4).ForeColor = Color.DarkRed
        fpSpread.Sheets(0).Rows(4).BackColor = Color.FromArgb(187, 189, 178)
        fpSpread.Sheets(0).Columns(1).BackColor = Color.FromArgb(198, 198, 198)
        fpSpread.Sheets(0).Cells(3, 1).BackColor = Color.FromArgb(187, 189, 178)
        fpSpread.Sheets(0).Cells(5, 33, 6, 34).BackColor = Color.FromArgb(187, 189, 178)
        fpSpread.Sheets(0).Cells(5, 1, 6, 1).BackColor = Color.FromArgb(187, 189, 178)
        fpSpread.Sheets(0).Cells(6, 2, 6, 32).ForeColor = Color.White
        fpSpread.Sheets(0).Cells(6, 2, 6, 32).BackColor = Color.FromArgb(67, 38, 32)
        fpSpread.Sheets(0).Cells(5, 1, 6, 1).Border = New LineBorder(Color.Transparent, 1)

        'Set alternative row color
        fpSpread.Sheets(0).AlternatingRows.Count = 2
        fpSpread.Sheets(0).AlternatingRows(0).BackColor = Color.WhiteSmoke
        fpSpread.Sheets(0).AlternatingRows(1).BackColor = Color.Gainsboro
    End Sub
    Private Sub SetFont()
        'Set font
        fpSpread.Font = New Font("MS Sans Serif", 9, FontStyle.Regular)
        fpSpread.Sheets(0).Rows(0).Font = New Font("MS Sans Serif", 18, FontStyle.Bold)
        fpSpread.Sheets(0).Rows(4).Font = New Font("MS Sans Serif", 10, FontStyle.Bold)
    End Sub
    Private Sub SaveChangesBackToList()
        Dim _AddToList As New List(Of AbsenceSchedule)()
        For Each row As DataRow In mainTable.Rows
            Dim _loc As Integer = -1
            For Each item As Object In row.ItemArray
                If _loc > 0 AndAlso (item.ToString().ToLower() = "s" OrElse item.ToString().ToLower() = "v" OrElse item.ToString().ToLower() = "p" OrElse item.ToString().ToLower() = "c1" OrElse item.ToString().ToLower() = "c2") Then
                    _AddToList.Add(New AbsenceSchedule() With {
                         .EmpId = Convert.ToInt32(row(0)),
                         .StartDate = New DateTime(selectedYr, selectedMonth, _loc),
                         .EndDate = New DateTime(selectedYr, selectedMonth, _loc),
                         .Type = item.ToString()
                    })
                End If
                _loc = _loc + 1
            Next
        Next

        'Need to remove
        Dim _removeFromList As New List(Of AbsenceSchedule)()
        For Each row As DataRow In mainTable.Rows
            Dim _loc As Integer = -1
            For Each item As Object In row.ItemArray
                If _loc > 0 AndAlso (item.ToString().ToLower() <> "s" AndAlso item.ToString().ToLower() <> "v" AndAlso item.ToString().ToLower() <> "p" AndAlso item.ToString().ToLower() <> "c1" AndAlso item.ToString().ToLower() <> "c2") Then
                    _removeFromList.Add(New AbsenceSchedule() With {
                        .EmpId = Convert.ToInt32(row(0)),
                        .StartDate = New DateTime(selectedYr, selectedMonth, _loc),
                        .EndDate = New DateTime(selectedYr, selectedMonth, _loc),
                        .Type = item.ToString()
                    })
                End If
                _loc = _loc + 1
            Next
        Next

        Dim _filteredList As List(Of Employee) = empList.Where(Function(e) e.DOJ <= New DateTime(selectedYr, selectedMonth, 1) AndAlso (e.DOR Is Nothing OrElse e.DOR >= New DateTime(selectedYr, selectedMonth, 1))).ToList()
        'Update old list
        For Each absence As Object In _filteredList

            'Add update
            For Each item As Object In _AddToList.Where(Function(i) i.EmpId = absence.Id)
                Dim _alreadyAdded As AbsenceSchedule = absenceSheduleList.FirstOrDefault(Function(abs) abs.EmpId = item.EmpId AndAlso ((CType(item.StartDate, Date) - abs.StartDate).Days >= 0 AndAlso (CType(abs.EndDate, Date) - CType(item.StartDate, Date)).Days >= 0))
                If _alreadyAdded IsNot Nothing AndAlso _alreadyAdded.Type = item.Type Then
                    Continue For
                End If
                If _alreadyAdded IsNot Nothing Then
                    If _alreadyAdded.StartDate = _alreadyAdded.EndDate Then
                        Dim _contain = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.EndDate = item.StartDate.Subtract(New TimeSpan(1, 0, 0, 0)))
                        If _contain IsNot Nothing Then
                            absenceSheduleList.Remove(_alreadyAdded)
                            _contain.EndDate = item.StartDate
                        Else
                            _contain = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0)))
                            If _contain IsNot Nothing Then
                                absenceSheduleList.Remove(_alreadyAdded)
                                _contain.StartDate = item.StartDate
                            Else

                                _alreadyAdded.Type = item.Type
                            End If
                        End If
                    ElseIf _alreadyAdded.StartDate = item.StartDate Then
                        Dim _contain = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.EndDate = item.StartDate.Subtract(New TimeSpan(1, 0, 0, 0)))
                        If _contain IsNot Nothing Then
                            _contain.EndDate = item.StartDate
                        Else
                            absenceSheduleList.Add(New AbsenceSchedule() With {
                                 .EmpId = item.EmpId,
                                 .StartDate = item.StartDate,
                                 .EndDate = item.EndDate,
                                 .Type = item.Type
                            })
                        End If
                        _alreadyAdded.StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0))
                    ElseIf _alreadyAdded.EndDate = item.StartDate Then
                        Dim _contain = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0)))
                        If _contain IsNot Nothing Then
                            _contain.StartDate = item.StartDate
                        Else
                            absenceSheduleList.Add(New AbsenceSchedule() With {
                                 .EmpId = item.EmpId,
                                 .StartDate = item.StartDate,
                                .EndDate = item.EndDate,
                                 .Type = item.Type
                            })
                        End If
                        _alreadyAdded.EndDate = item.StartDate.Subtract(New TimeSpan(1, 0, 0, 0))
                    Else
                        absenceSheduleList.Add(New AbsenceSchedule() With {
                            .EmpId = _alreadyAdded.EmpId,
                            .StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0)),
                            .EndDate = _alreadyAdded.EndDate,
                            .Type = _alreadyAdded.Type
                        })
                        absenceSheduleList.Add(New AbsenceSchedule() With {
                            .EmpId = item.EmpId,
                             .StartDate = item.StartDate,
                             .EndDate = item.EndDate,
                             .Type = item.Type
                        })
                        _alreadyAdded.EndDate = item.StartDate.Subtract(New TimeSpan(1, 0, 0, 0))
                    End If
                Else
                    Dim _contain = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.EndDate = item.StartDate.Subtract(New TimeSpan(1, 0, 0, 0)))
                    If _contain IsNot Nothing Then
                        _contain.EndDate = item.StartDate
                        Dim _containNext = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0)))
                        If _containNext IsNot Nothing Then
                            _contain.EndDate = _containNext.EndDate
                            absenceSheduleList.Remove(_containNext)
                        End If
                    Else
                        _contain = absenceSheduleList.FirstOrDefault(Function(i) i.EmpId = item.EmpId AndAlso i.Type = item.Type AndAlso i.StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0)))
                        If _contain IsNot Nothing Then
                            _contain.StartDate = item.StartDate
                        Else

                            absenceSheduleList.Add(New AbsenceSchedule() With {
                                 .EmpId = item.EmpId,
                                 .StartDate = item.StartDate,
                                 .EndDate = item.EndDate,
                                 .Type = item.Type
                            })
                        End If
                    End If
                End If
            Next

            'Remove Update
            For Each item As Object In _removeFromList.Where(Function(i) i.EmpId = absence.Id)
                Dim _alreadyAdded As AbsenceSchedule = absenceSheduleList.FirstOrDefault(Function(abs) abs.EmpId = item.EmpId AndAlso item.Type <> abs.Type AndAlso ((CType(item.StartDate, Date) - abs.StartDate).Days >= 0 AndAlso (CType(abs.EndDate, Date) - CType(item.StartDate, Date)).Days >= 0))


                If _alreadyAdded IsNot Nothing Then
                    If (CType(item.StartDate, DateTime) - CType(_alreadyAdded.StartDate, DateTime)).Days >= 1 AndAlso (CType(_alreadyAdded.EndDate, DateTime) - CType(item.StartDate, DateTime)).Days >= 1 Then

                        Dim _addNew As New AbsenceSchedule() With {
                             .EmpId = _alreadyAdded.EmpId,
                             .Type = _alreadyAdded.Type,
                             .StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0)),
                             .EndDate = _alreadyAdded.EndDate
                        }
                        _alreadyAdded.EndDate = item.StartDate.Subtract(New TimeSpan(1, 0, 0, 0))
                        absenceSheduleList.Add(_addNew)
                    ElseIf (CType(item.StartDate, DateTime) - CType(_alreadyAdded.StartDate, DateTime)).Days >= 1 Then
                        _alreadyAdded.EndDate = item.EndDate.Subtract(New TimeSpan(1, 0, 0, 0))
                    ElseIf (CType(_alreadyAdded.EndDate, Date) - CType(item.EndDate, Date)).Days >= 1 Then
                        _alreadyAdded.StartDate = item.StartDate.Add(New TimeSpan(1, 0, 0, 0))
                    Else
                        absenceSheduleList.Remove(_alreadyAdded)

                    End If
                End If
            Next
        Next
    End Sub
    Private Sub SetAlignmentforAllRows()
        fpSpread.Sheets(0).Rows(-1, -1).VerticalAlignment = CellVerticalAlignment.Center
        fpSpread.Sheets(0).Rows(-1, -1).HorizontalAlignment = CellHorizontalAlignment.Center
    End Sub
    Private Sub SetRowHeight()
        Dim _rowCnt As Integer = fpSpread.Sheets(0).Rows.Count - 1
        fpSpread.Sheets(0).Rows(7, _rowCnt).Height = 32
    End Sub
    Private Sub CreateCondionalRules()

        'Condition rule for S
        sRule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "S", False)
        sRule.[Operator] = UnaryComparisonOperator.EqualTo
        sRule.Value = "S"
        sRule.BackColor = Color.FromArgb(154, 162, 124)
        sRule.FontStyle = New SpreadFontStyle(RegularBoldItalicFontStyle.Bold)

        'condition rule for V
        vRule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "V", False)
        vRule.[Operator] = UnaryComparisonOperator.EqualTo
        vRule.Value = "V"
        vRule.BackColor = Color.FromArgb(174, 96, 82)
        vRule.FontStyle = New SpreadFontStyle(RegularBoldItalicFontStyle.Bold)

        'condition rule for P
        pRule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "P", False)
        pRule.[Operator] = UnaryComparisonOperator.EqualTo
        pRule.Value = "P"
        pRule.BackColor = Color.FromArgb(185, 159, 141)
        pRule.FontStyle = New SpreadFontStyle(RegularBoldItalicFontStyle.Bold)

        'condition rule for C1
        c1Rule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "C1", False)
        c1Rule.[Operator] = UnaryComparisonOperator.EqualTo
        c1Rule.Value = "C1"
        c1Rule.BackColor = Color.FromArgb(197, 173, 99)
        c1Rule.FontStyle = New SpreadFontStyle(RegularBoldItalicFontStyle.Bold)

        'condition rule for C2
        c2Rule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, "C2", False)
        c2Rule.[Operator] = UnaryComparisonOperator.EqualTo
        c2Rule.Value = "C2"
        c2Rule.BackColor = Color.FromArgb(146, 149, 175)
        c2Rule.FontStyle = New SpreadFontStyle(RegularBoldItalicFontStyle.Bold)

    End Sub
    Private Sub SetConditionalFormatting()
        If cf IsNot Nothing Then
            cf.Clear()
            cf = Nothing
            fpSpread.ActiveSheet.Models.ConditionalFormatting.Clear()
        End If
        cf = New ConditionalFormatting(New CellRange(0, 0, fpSpread.ActiveSheet.Rows.Count, fpSpread.ActiveSheet.Columns.Count), New IConditionalFormattingRule() {sRule, vRule, pRule, c1Rule, c2Rule})
        fpSpread.ActiveSheet.Models.ConditionalFormatting.Add(cf)

    End Sub
    Private Sub UpdateFooter()
        Dim _rowCnt As Integer = fpSpread.Sheets(0).Rows.Count
        Dim _colCnt As Integer = fpSpread.Sheets(0).Columns.Count
        fpSpread.Sheets(0).Rows.Count = _rowCnt + 1

        'Update colors
        fpSpread.Sheets(0).Rows(_rowCnt).BackColor = Color.FromArgb(187, 189, 178)
        fpSpread.Sheets(0).Cells(7, 33, _rowCnt - 1, 34).BackColor = Color.FromArgb(198, 198, 198)
        fpSpread.Sheets(0).FrozenTrailingRowCount = 1
        'Update border
        fpSpread.Sheets(0).Cells(5, 33, _rowCnt, 34).Border = New LineBorder(Color.Transparent, 1)

        'Set Text for some fields
        fpSpread.Sheets(0).Cells(6, 1).Text = "Employee Name"
        fpSpread.Sheets(0).Cells(6, _colCnt - 2).Text = "Total Days"
        fpSpread.Sheets(0).Cells(_rowCnt, 1).Value = "Total Days:"

        'Set Readonly first column data cells          
        fpSpread.Sheets(0).Rows(_rowCnt).Locked = True
        For i As Integer = 7 To _rowCnt - 1
            fpSpread.Sheets(0).Cells(i, 1, i, 1).Locked = True
        Next


        'Span cells
        For i As Integer = 7 To _rowCnt
            fpSpread.Sheets(0).AddSpanCell(i, _colCnt - 2, 1, 2)
        Next
        'Set alignment
        fpSpread.Sheets(0).Cells(7, 33, _rowCnt, _colCnt - 1).HorizontalAlignment = CellHorizontalAlignment.Left
        fpSpread.Sheets(0).Cells(7, 33, _rowCnt, _colCnt - 1).CellPadding.Left = 5





    End Sub
    Private Sub UpdateFormulas(row As Integer, col As Integer)
        Dim _rowCnt As Integer = fpSpread.Sheets(0).Rows.Count
        Dim _colCnt As Integer = fpSpread.Sheets(0).Columns.Count - 2

        For _row As Integer = 0 To mainTable.Rows.Count - 2
            fpSpread.Sheets(0).Cells(_row + 7, _colCnt).Formula = "COUNTIF(C" & (_row + 8) & ":AG" & (_row + 8) & ",""S"") + COUNTIF(C" & (_row + 8) & ":AG" & (_row + 8) & ",""V"") + COUNTIF(C" & (_row + 8) & ":AG" & (_row + 8) & ",""P"") + COUNTIF(C" & (_row + 8) & ":AG" & (_row + 8) & ",""C1"") + COUNTIF(C" & (_row + 8) & ":AG" & (_row + 8) & ",""C2"")"
        Next
        For _col As Integer = 2 To mainTable.Columns.Count - 1
            fpSpread.Sheets(0).Cells(_rowCnt - 1, _col).Formula = "COUNTIF(" & fpSpread.Sheets(0).Columns(_col).Label & "8:" & fpSpread.Sheets(0).Columns(_col).Label & "" & (_rowCnt - 1) & ",""S"") + COUNTIF(" & fpSpread.Sheets(0).Columns(_col).Label & "8:" & fpSpread.Sheets(0).Columns(_col).Label & "" & (_rowCnt - 1) & ",""V"")+ COUNTIF(" & fpSpread.Sheets(0).Columns(_col).Label & "8:" & fpSpread.Sheets(0).Columns(_col).Label & "" & (_rowCnt - 1) & ",""P"")+ COUNTIF(" & fpSpread.Sheets(0).Columns(_col).Label & "8:" & fpSpread.Sheets(0).Columns(_col).Label & "" & (_rowCnt - 1) & ",""C1"")+ COUNTIF(" & fpSpread.Sheets(0).Columns(_col).Label & "8:" & fpSpread.Sheets(0).Columns(_col).Label & "" & (_rowCnt - 1) & ",""C2"")"
        Next
        fpSpread.Sheets(0).Cells(_rowCnt - 1, _colCnt).Formula = "SUM(AH7:AH" & (_rowCnt - 2) & ")"
    End Sub
    Private Sub updateCombobox(row As Integer, col As Integer)
        fpSpread.Sheets(0).Cells(row + 1, col).Text = month(selectedMonth - 1).ToString()
        fpSpread.Sheets(0).Cells(row + 1, col + 1).Text = selectedYr.ToString()

    End Sub
    Private Sub AddComboBox(row As Integer, col As Integer)

        'Add combobox for month
        fpSpread.Sheets(0).Cells(row, col).Text = "Select Month:"
        If cmbocellMonth Is Nothing Then
            cmbocellMonth = New ComboBoxCellType()
            month = New List(Of String)()
            month.AddRange(New [String]() {"January", "February", "March", "April", "May", "June",
                "July", "August", "September", "October", "November", "December"})
            cmbocellMonth.Items = month.ToArray()
            cmbocellMonth.Editable = False

            cmbocellMonth.MaxDrop = 8
        End If
        fpSpread.ButtonDrawMode = ButtonDrawModes.AlwaysPrimaryButton
        fpSpread.Sheets(0).Cells(row + 1, col).CellType = cmbocellMonth
        fpSpread.Sheets(0).Cells(row + 1, col).Text = month(selectedMonth - 1).ToString()

        'Add a combobox for year
        fpSpread.Sheets(0).Cells(row, col + 1).Text = "Select Year:"
        If cmbocellYear Is Nothing Then
            cmbocellYear = New ComboBoxCellType()
            year = New List(Of String)()
            For yr As Integer = DateTime.Now.Year - 1 To DateTime.Now.Year + 1
                year.Add(yr & "")
            Next
            cmbocellYear.Items = year.ToArray()
            cmbocellYear.Editable = False
            cmbocellYear.MaxDrop = 8
        End If

        fpSpread.Sheets(0).Cells(row + 1, col + 1).CellType = cmbocellYear
        fpSpread.Sheets(0).Cells(row + 1, col + 1).Text = selectedYr.ToString()

    End Sub
    Private Sub UpdateDataOnSpread()
        fpSpread.SuspendLayout()
        Dim _rowCnt As Integer = fpSpread.Sheets(0).Rows.Count
        If _rowCnt > 7 Then
            'Remove all data rows
            fpSpread.Sheets(0).Rows.Remove(7, _rowCnt - 7)
        End If
        If mainTable.Rows.Count > 0 Then
            fpSpread.Sheets(0).Rows.Add(7, mainTable.Rows.Count)

            Dim data As New SpreadDataBindingAdapter()
            ' Assign the datasource to a data table
            data.DataSource = mainTable
            data.Spread = fpSpread
            data.SheetName = "Sheet1"
            data.MapperInfo = New MapperInfo(7, 0, mainTable.Rows.Count - 1, 33)
            data.FillSpreadDataByDataSource()
            UpdateFooter()

            UpdateFormulas(3, 33)
        Else
            fpSpread.Sheets(0).Rows.Add(7, 1)
            fpSpread.Sheets(0).Rows(7).BackColor = Color.AliceBlue
            fpSpread.Sheets(0).Cells(7, 1).Text = "No record found."
            fpSpread.Sheets(0).AddSpanCell(7, 1, 1, 33)
        End If
        SetConditionalFormatting()
        SetRowHeight()
        SetAlignmentforAllRows()
        updateCombobox(3, 33)
        fpSpread.ResumeLayout()
    End Sub
    Private Sub UpdateDateHeaderAndDateTable(ByVal datetime As DateTime, ByVal empList As List(Of Spread.Common.DataStore.Models.Employee), ByVal absenceSheduleList As List(Of Spread.Common.DataStore.Models.AbsenceSchedule))
        mainTable = New DataTable()
        mainTable.Columns.Add("EmpId")
        mainTable.Columns.Add("Name")
        Dim _noOfDays As Integer = DateTime.DaysInMonth(datetime.Year, datetime.Month)
        fpSpread.Sheets(0).Cells(5, 1, fpSpread.Sheets(0).Rows.Count - 1, fpSpread.Sheets(0).Columns.Count - 3).Text = ""

        For day As Integer = 1 To _noOfDays

            If Not mainTable.Columns.Contains(day.ToString()) Then
                fpSpread.Sheets(0).Cells(5, day + 1).Text = New DateTime(datetime.Year, datetime.Month, day).ToString("ddd")
                fpSpread.Sheets(0).Cells(6, day + 1).Text = day.ToString()
                mainTable.Columns.Add("D" & day.ToString())
            End If
        Next

        Dim daysOfMonth As List(Of DayOfMonth) = New List(Of DayOfMonth)()

        For Each emp As Employee In empList.Where(Function(e) e.DOJ <= New DateTime(datetime.Year, datetime.Month, 1) AndAlso (e.DOR Is Nothing OrElse e.DOR >= New DateTime(datetime.Year, datetime.Month, 1)))

            For i As Integer = 1 To _noOfDays
                daysOfMonth.Add(New DayOfMonth() With {
                .EmpId = emp.Id,
                .Name = emp.Name,
                .Day = i,
                .Month = datetime.Month,
                .Year = datetime.Year,
                .Type = ""
            })
            Next
        Next

        Dim q As List(Of DayOfMonth) = (From a In daysOfMonth From b In absenceSheduleList.Where(Function(t) New DateTime(a.Year, a.Month, a.Day) >= t.StartDate AndAlso New DateTime(a.Year, a.Month, a.Day) <= t.EndDate AndAlso t.EmpId = a.EmpId).DefaultIfEmpty() Select New DayOfMonth() With {
        .EmpId = a.EmpId,
        .Name = a.Name,
        .Day = a.Day,
        .Month = a.Month,
        .Year = a.Year,
        .Type = If((b Is Nothing), "", b.Type)
    }).ToList()

        Dim grps1 As List(Of IGrouping(Of Integer, DayOfMonth)) = q.GroupBy(Function(t) t.Day).ToList()
        Dim datas As List(Of Object) = New List(Of Object)()
        grps1.ToList().ForEach(Function(t)
                                   Dim items = t.ToList()

                                   For i As Integer = 0 To items.Count - 1

                                       If datas.Count <= i Then
                                           Dim data2 As Object = New ExpandoObject()
                                           datas.Add(data2)
                                           Dim dataDictionary1 As IDictionary(Of String, Object) = CType(datas(i), IDictionary(Of String, Object))
                                           dataDictionary1.Add("EmpId", items(i).EmpId)
                                           Dim dataDictionary2 As IDictionary(Of String, Object) = CType(datas(i), IDictionary(Of String, Object))
                                           dataDictionary2.Add("Name", items(i).Name)
                                       End If

                                       Dim dataDictionary As IDictionary(Of String, Object) = CType(datas(i), IDictionary(Of String, Object))
                                       dataDictionary.Add("D" & items(i).Day.ToString(), items(i).Type)
                                   Next
                               End Function)
        Dim Data1 As Object
        For Each Data1 In datas
            Dim obj As ExpandoObject = TryCast(Data1, ExpandoObject)
            Dim _row As DataRow = mainTable.NewRow()

            Dim item As Object
            For Each item In obj.ToList()
                _row(item.Key) = item.Value
            Next

            mainTable.Rows.Add(_row)
        Next
    End Sub
    Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, value As T) As T
        target = value
        Return value
    End Function
#End Region
End Class

'
'AbsenceScheduleControl.Designer.vb
'

Partial Class AbsenceScheduleControl
	''' <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()
		components = New System.ComponentModel.Container()
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
	End Sub

	#End Region
End Class
