﻿Imports System
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.CellType
Imports System.Globalization
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.Models
Imports FarPoint.Win.Spread
Imports FarPoint.Win
Imports GrapeCity.BusinessKPI.Sparkline
Imports FarPoint.Win.Spread.Chart
Imports Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl
Imports GrapeCity.BusinessKPI.GrapeCity.BusinessKPI.Sparkline

Namespace GrapeCity.BusinessKPI
    Partial Public Class BusinessKPI
        Inherits Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl


        Private _circularSymbol As String
        Private _themeColor As Color
        Private _dataSource As KPIDataSource
        Private _trendTypes As String()
        Private _kpiList As KPIList
        Private _targetMasterList As List(Of TargetMaster)
        Private _currentYearMasterList As List(Of TargetMaster)
        Private _previousYearMasterList As List(Of TargetMaster)
        Private _exSetting As ExcelSparklineSetting
        Private _numCell As NumberCellType
        Private _decimalCell As NumberCellType
        Private _currencyCell As CurrencyCellType
        Private _percentCell As PercentCellType
        Private comboCellMonth As ComboBoxCellType = Nothing
        Private _dateFormatInfo As DateTimeFormatInfo
        Friend WithEvents FpSpread1 As FpSpread
        Friend WithEvents FpSpread1_Sheet1 As SheetView
        Private _date As DateTime

        Public Sub New()
            InitializeComponent()
            _circularSymbol = "●"
            _themeColor = Color.FromArgb(30, 122, 197)
            _dataSource = New DataService().KPIDataSource
            _kpiList = _dataSource.GetAllKPIs()
            _targetMasterList = _dataSource.GetAllTargets()
            _dateFormatInfo = New CultureInfo("en-US").DateTimeFormat
            _trendTypes = New String() {"ytd", "ytd", "%", "%", "ytd", "%", "yoy", "yoy", "%", "%"}
            _exSetting = New ExcelSparklineSetting With {
                .DisplayHidden = True
            }
            _numCell = New NumberCellType With {
                .DecimalPlaces = 0
            }
            _decimalCell = New NumberCellType With {
                .DecimalPlaces = 2,
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            _currencyCell = New CurrencyCellType With {
                .CurrencySymbol = "$",
                .DecimalPlaces = 2,
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            _percentCell = New PercentCellType With {
                .DecimalPlaces = 2,
                .PercentSign = "%",
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            AddHandler Me.Load, AddressOf BusinessKPI_Load
        End Sub

        Private Sub BusinessKPI_Load(ByVal sender As Object, ByVal e As EventArgs)
            SetSheetProperties(FpSpread1.ActiveSheet)
            SetDataSheetColors(FpSpread1.ActiveSheet, 15, 0)
            AddComboSelector(FpSpread1.ActiveSheet, 1, 2)
            LoadSelectedMetrics()
            SetDataSheetProperties(FpSpread1.ActiveSheet, 15, 0)
            LoadDataSheet(FpSpread1.ActiveSheet, 19, 0)
            LoadKPIList(FpSpread1.ActiveSheet, 4, 2)
            LoadMetricMeasures(FpSpread1.ActiveSheet, 4, 5)
            LoadMetrics(FpSpread1.ActiveSheet, 4, 9)
            LoadTargetSparklines(FpSpread1.ActiveSheet, 4, 11)
            LoadTrendSparklines(FpSpread1.ActiveSheet, 4, 6)
            LoadCircularIcons(FpSpread1.ActiveSheet, 4, 1)
        End Sub

        Private Sub ComboCell_EditingStopped(ByVal sender As Object, ByVal e As EventArgs)
            LoadSelectedMetrics()
            LoadYearlyDataHeaders(FpSpread1.ActiveSheet, 16, 0)
            LoadDataSheet(FpSpread1.ActiveSheet, 19, 0)
            LoadMetrics(FpSpread1.ActiveSheet, 4, 9)

            If _currentYearMasterList.Where(Function(l) l.Month = _date.Month).Any() Then
                LoadTargetSparklines(FpSpread1.ActiveSheet, 4, 11)
                LoadTrendSparklines(FpSpread1.ActiveSheet, 4, 6)
                LoadCircularIcons(FpSpread1.ActiveSheet, 4, 1)
            End If
        End Sub

        Private Sub SetDataSheetProperties(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Rows(row).Height = 40
            sheet.Rows(row).Font = New Font("Segoe UI", 14, FontStyle.Bold)
            sheet.Cells(row, col).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.AddSpanCell(row, col, 1, sheet.ColumnCount)
            sheet.Rows(row + 17).Height = 40
            sheet.Rows(row + 17).Font = New Font("Segoe UI", 14, FontStyle.Bold)
            sheet.Cells(row + 17, col).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.AddSpanCell(row + 17, col, 1, sheet.ColumnCount)
            sheet.AlternatingRows.Count = 2
            sheet.AlternatingRows(0).BackColor = Color.FromArgb(246, 246, 246)
            sheet.AlternatingRows(1).BackColor = Color.FromArgb(245, 245, 222)
            sheet.Cells(row + 4, col + 1, row + 15, sheet.ColumnCount - 1).Locked = False
            sheet.Cells(row + 21, col + 1, row + 32, sheet.ColumnCount - 1).Locked = False
            LoadYearlyDataHeaders(sheet, row + 1, col)
            AddSpanCellsToFirstColumn(sheet, 16, 0, 31)
            AddSpanCellsToFirstColumn(sheet, 33, 0, 47)
            SetDataSheetCellTypes(sheet, row + 4, col + 2)
            SetDataSheetCellTypes(sheet, row + 21, col + 2)
            sheet.Rows(14).BackColor = Color.White
            sheet.Rows(14).Border = New LineBorder(Color.White, 2, True, False, True, False)
            sheet.Rows(31).BackColor = Color.White
            sheet.Rows(31).Border = New LineBorder(Color.White, 2, True, False, True, False)
        End Sub

        Private Sub SetDataSheetCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row + 11, col + 1).CellType = _numCell
            sheet.Cells(row, col + 2, row + 11, col + 3).CellType = _currencyCell
            sheet.Cells(row, col + 4, row + 11, col + 5).CellType = _percentCell
            sheet.Cells(row, col + 6, row + 11, col + 7).CellType = _percentCell
            sheet.Cells(row, col + 8, row + 11, col + 9).CellType = _currencyCell
            sheet.Cells(row, col + 10, row + 11, col + 11).CellType = _percentCell
            sheet.Cells(row, col + 12, row + 11, col + 13).CellType = _numCell
            sheet.Cells(row, col + 14, row + 11, col + 15).CellType = _currencyCell
            sheet.Cells(row, col + 16, row + 11, col + 19).CellType = _percentCell
        End Sub

        Private Sub LoadDataSheet(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            LoadYearlyData(sheet, row, col, _currentYearMasterList, New DateTime(_date.Year, 1, 1))
            LoadYearlyData(sheet, row + 17, col, _previousYearMasterList, New DateTime(_date.Year - 1, 1, 1))
        End Sub

        Private Sub LoadYearlyDataHeaders(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            LoadYearlyDataHeaders(sheet, row, col, New DateTime(_date.Year, 1, 1))
            LoadYearlyDataHeaders(sheet, row + 17, col, New DateTime(_date.Year - 1, 1, 1))
        End Sub

        Private Sub SetDataSheetColors(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Rows(row).BackColor = _themeColor
            sheet.Rows(row, row + 3).ForeColor = Color.WhiteSmoke
            sheet.Rows(row + 1, row + 3).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 4, col, row + 15, 0).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 4, col, row + 15, 0).ForeColor = Color.WhiteSmoke
            sheet.Rows(row + 17).BackColor = _themeColor
            sheet.Rows(row + 17, row + 20).ForeColor = Color.WhiteSmoke
            sheet.Rows(row + 18, row + 20).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 21, col, row + 32, 0).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 21, col, row + 32, 0).ForeColor = Color.WhiteSmoke
        End Sub

        Private Sub AddSpanCellsToFirstColumn(ByVal sheet As SheetView, ByVal startRow As Integer, ByVal col As Integer, ByVal endRow As Integer)
            For i As Integer = startRow To endRow
                sheet.AddSpanCell(i, col, 1, 2)
            Next
        End Sub

        Private Sub LoadYearlyDataHeaders(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal startDate As DateTime)
            sheet.Cells(row, col).Value = "KPI ID"
            sheet.Cells(row + 1, col).Value = "KPI Name"

            For i As Integer = 1 To _kpiList.Items.Count
                sheet.AddSpanCell(row, col + i * 2, 1, 2)
                sheet.Cells(row, col + i * 2).Value = i
                sheet.AddSpanCell(row + 1, col + i * 2, 1, 2)
                sheet.Cells(row + 1, col + i * 2).Value = _kpiList.Items(i)
                sheet.Cells(row + 2, col + i * 2).Value = "Actual"
                sheet.Cells(row + 2, col + i * 2 + 1).Value = "Target"
            Next
        End Sub

        Private Sub LoadYearlyData(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal targetMasterList As List(Of TargetMaster), ByVal startDate As DateTime)
            sheet.Cells(row, col, row + 11, sheet.ColumnCount - 1).ResetValue()

            If targetMasterList.Count = 0 Then
                Return
            End If

            sheet.Cells(row - 4, col).Value = $"KPI Data for the year {startDate.Year}"
            Dim date1 As DateTime = startDate
            Dim _rowCount As Integer = 0

            While date1.Year = startDate.Year
                sheet.Cells(row + _rowCount, col).Value = date1.ToString("MMM, yyyy")
                _rowCount += 1
                date1 = date1.AddMonths(1)
            End While

            _rowCount = row

            For Each targetMaster As TargetMaster In targetMasterList
                Dim _colCount As Integer = 1

                For Each target As KPIMetrics In targetMaster.KPITargets
                    sheet.Cells(_rowCount, col + _colCount * 2).Value = targetMaster.KPITargets(_colCount - 1).Actual
                    sheet.Cells(_rowCount, col + _colCount * 2 + 1).Value = targetMaster.KPITargets(_colCount - 1).Target
                    _colCount += 1
                Next

                _rowCount += 1
            Next
        End Sub

        Private Sub SetSheetProperties(ByVal sheet As SheetView)
            sheet.ColumnHeader.Visible = False
            sheet.RowHeader.Visible = False
            sheet.RowCount = 48
            sheet.ColumnCount = 22
            sheet.Columns(0, 1).Width = 50
            sheet.Columns(2, 20).Width = 87
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).VerticalAlignment = CellVerticalAlignment.Center
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).Locked = True
            sheet.AddSpanCell(0, 0, 2, 12)
            sheet.Cells(0, 0).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Cells(0, 0).Text = "Business KPI"
            sheet.Cells(0, 0).Font = New Font("Segoe UI", 16, FontStyle.Bold)
            sheet.Rows(0, 2).BackColor = _themeColor
            sheet.Rows(0, 2).ForeColor = Color.WhiteSmoke
            sheet.Cells(0, 0, 2, sheet.ColumnCount - 1).Border = New ComplexBorder(New ComplexBorderSide(_themeColor, 2), New ComplexBorderSide(_themeColor, 2), New ComplexBorderSide(_themeColor, 2), New ComplexBorderSide(_themeColor, 2))
            sheet.AddSpanCell(0, 12, 1, 2)
            sheet.Cells(0, 12).Value = "Select Month & Year"
            sheet.Cells(0, 12).Font = New Font("Segoe UI", 12, FontStyle.Bold)
            sheet.Cells(0, 12).ForeColor = Color.WhiteSmoke
            sheet.Rows(0, sheet.RowCount - 1).Height = 30
            sheet.Cells(3, 0).ColumnSpan = 5
            sheet.Cells(3, 0).Value = "Business KPIs"
            sheet.Cells(3, 5).Value = "Metric"
            sheet.Cells(3, 6).ColumnSpan = 3
            sheet.Cells(3, 6).Value = "Last Year"
            sheet.Cells(3, 9).Tag = "MonthYearCell"
            sheet.Cells(3, 10).Value = "Target"
            sheet.Cells(3, 11).ColumnSpan = 4
            sheet.Cells(3, 11).Value = "% vs. Target"
            sheet.Cells(3, 0, 3, 14).Border = New LineBorder(Color.Black, 1, False, True, False, True)
            sheet.Cells(4, 1, 13, 1).Font = New Font("Segoe UI", 16, FontStyle.Regular)

            For i As Integer = 0 To _kpiList.Items.Count - 1
                sheet.AddSpanCell(4 + i, 6, 1, 3)
                sheet.AddSpanCell(4 + i, 11, 1, 4)
            Next

            SetCellTypes(sheet, 4, 9)
        End Sub

        Private Sub SetCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row + 9, col + 1).CellType = _percentCell
        End Sub

        Private Sub AddComboSelector(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Rows(row).Height = 25
            comboCellMonth = New ComboBoxCellType With {
                .DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                .Editable = False,
                .Items = _dateFormatInfo.MonthNames.Take(DateTime.Now.Month).ToArray(),
                .StopEditingAfterDropDownItemSelected = True
            }
            AddHandler comboCellMonth.EditingStopped, AddressOf ComboCell_EditingStopped
            Dim comboCellYear As ComboBoxCellType = New ComboBoxCellType With {
                .DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                .Editable = False,
                .Items = New String() {(DateTime.Now.Year - 2).ToString(), (DateTime.Now.Year - 1).ToString(), DateTime.Now.Year.ToString()},
                .StopEditingAfterDropDownItemSelected = True
            }
            AddHandler comboCellYear.EditingStopped, AddressOf ComboCell_EditingStopped
            sheet.Cells(row, col + 10, row, col + 11).BackColor = Color.WhiteSmoke
            sheet.Cells(row, col + 10, row, col + 11).ForeColor = Color.Black
            sheet.Cells(row, col + 10).CellType = comboCellMonth
            sheet.Cells(row, col + 10).Value = DateTime.Now.ToString("MMMM")
            sheet.Cells(row, col + 10).Tag = "MonthCell"
            sheet.Cells(row, col + 10).Locked = False
            sheet.Cells(row, col + 11).CellType = comboCellYear
            sheet.Cells(row, col + 11).Value = DateTime.Now.ToString("yyyy")
            sheet.Cells(row, col + 11).Tag = "YearCell"
            sheet.Cells(row, col + 11).Locked = False
            sheet.Rows(row + 1).Height = 10
            sheet.AddSpanCell(row + 1, col + 1, 1, 3)
            sheet.Cells(row + 1, col + 1).ForeColor = Color.WhiteSmoke
            sheet.Cells(row + 1, col + 1).Font = New Font("Segoe UI", 9, FontStyle.Bold)
        End Sub

        Private Sub LoadTrendSparklines(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim srcRowStart, srcRowEnd As Integer

            If _previousYearMasterList.Count = 0 Then
                sheet.Cells(row - 1, col).Value = "This Year (12 months)"
                srcRowStart = 19
                srcRowEnd = 30
            Else
                sheet.Cells(row - 1, col).Value = "Last Year (12 months)"
                srcRowStart = 36
                srcRowEnd = 47
            End If

            For i As Integer = 0 To _kpiList.Items.Count - 1
                AddTrendSparkline(sheet, row + i, col, srcRowStart, srcRowEnd)
            Next
        End Sub

        Private Sub AddTrendSparkline(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal srcRowStart As Integer, ByVal srcRowEnd As Integer)
            Dim group As ExcelSparklineGroup = New ExcelSparklineGroup()
            Dim setting As ExcelSparklineSetting = New ExcelSparklineSetting()
            Dim sparkline As ExcelSparkline = New ExcelSparkline()
            sheet.SparklineContainer.Add(group)
            group.Renderer = New LineSparklineRenderer()
            group.Setting = setting
            sparkline.Formula = $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + (row - 3) * 2)).ToString()}${srcRowStart + 1}:" & $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + (row - 3) * 2)).ToString()}${srcRowEnd + 1}"
            setting.ShowNegative = True
            sparkline.Row = row
            sparkline.Column = col
            group.Add(sparkline)
        End Sub

        Private Sub LoadTargetSparklines(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For i As Integer = 0 To _kpiList.Items.Count - 1
                AddTargetSparkline(sheet, row + i, col)
            Next
        End Sub

        Private Sub AddTargetSparkline(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim sparkline As TargetSparkline = New TargetSparkline(sheet, row, col, New SheetCellRange(sheet, row, col - 2, 1, 1), New SheetCellRange(sheet, row, col - 1, 1, 1))
            Dim group As TargetSparklineGroup = New TargetSparklineGroup()
            Dim setting As TargetSparklineSetting = New TargetSparklineSetting()
            sheet.SparklineContainer.Add(group)
            group.Setting = setting
            group.Renderer = New TargetSparklineRenderer()
            group.Add(sparkline)
        End Sub

        Private Sub LoadMetricMeasures(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For Each trendType As String In _trendTypes
                sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = trendType
            Next
        End Sub

        Private Sub LoadKPIList(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For Each pair As KeyValuePair(Of Integer, String) In _kpiList.Items
                sheet.Cells(row, col - 2).Value = pair.Key
                sheet.AddSpanCell(row, col, 1, 3)
                sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = pair.Value
            Next
        End Sub

        Private Sub LoadSelectedMetrics()
            Dim year As Integer = Integer.Parse(FpSpread1.Sheets(0).Cells("YearCell").Value.ToString())
            comboCellMonth.Items = _dateFormatInfo.MonthNames.Take((If(DateTime.Now.Year = year, DateTime.Now.Month, 12))).ToArray()

            If FpSpread1.Sheets(0).Cells("MonthCell").Text = "" Then
                FpSpread1.Sheets(0).Cells("MonthCell").Value = comboCellMonth.Items.Last()
            End If

            Dim month As Integer = _dateFormatInfo.MonthNames.ToList().IndexOf(FpSpread1.Sheets(0).Cells("MonthCell").Value.ToString()) + 1
            _date = New DateTime(year, month, 1)
            _currentYearMasterList = _targetMasterList.Where(Function(t) t.Year = year).OrderBy(Function(t) t.Month).ToList()
            _previousYearMasterList = _targetMasterList.Where(Function(t) t.Year = year - 1).OrderBy(Function(t) t.Month).ToList()
            FpSpread1.Sheets(0).Cells("MonthYearCell").Value = $"{_date.ToString("MMM")}/{_date.ToString("yyyy")}"
            FpSpread1.ActiveSheet.Rows(31, 47).Visible = (_previousYearMasterList.Count <> 0)
        End Sub

        Private Sub ClearPreviousData(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(2, 3).ResetValue()
            sheet.Cells(row, col, row + _kpiList.Items.Count - 1, col + 1).ResetValue()
            sheet.ClearSparklines(New FarPoint.Win.Spread.Model.CellRange(row, col - 3, 10, 1))
            sheet.ClearSparklines(New FarPoint.Win.Spread.Model.CellRange(row, col + 2, 10, 1))
        End Sub

        Private Sub LoadMetrics(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            ClearPreviousData(sheet, row, col)

            If Not _currentYearMasterList.Where(Function(m) m.Year = _date.Year AndAlso m.Month = _date.Month).Any() Then
                sheet.Cells(2, 3).Value = $"No data available for {_date.ToString("MMM, yyyy")}"
                Return
            End If

            sheet.Cells(2, 3).Value = String.Empty
            LoadActualAndTargetMetrics(sheet, row, col)
        End Sub

        Private Sub LoadActualAndTargetMetrics(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim previousYearTarget As TargetMaster = _previousYearMasterList.Where(Function(p) p.Month = _date.Month).FirstOrDefault()

            For i As Integer = 1 To _trendTypes.Count()
                Dim actualFormula As String = String.Empty
                Dim targetFormula As String = String.Empty

                If _trendTypes(i - 1) = "%" Then
                    sheet.Cells(row + i - 1, col - 2).Value = "%"
                    actualFormula = $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}${19 + _date.Month}"
                    targetFormula = $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}${19 + _date.Month}"
                ElseIf _trendTypes(i - 1) = "ytd" OrElse previousYearTarget Is Nothing Then
                    sheet.Cells(row + i - 1, col - 2).Value = "ytd"
                    targetFormula = $"SUM(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}${19 + _date.Month}) / SUM(Sheet1!$" & $"{(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$" & $"{31})"
                    actualFormula = $"SUM(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2)).ToString()}${19 + _date.Month}) / SUM(Sheet1!$" & $"{(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$" & $"{31})"
                Else
                    sheet.Cells(row + i - 1, col - 2).Value = "yoy"
                    actualFormula = $"(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}$" & $"{19 + _date.Month} - Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}${36 + _date.Month}) / " & $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}${36 + _date.Month}"
                    targetFormula = $"(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}$" & $"{19 + _date.Month} - Sheet1!{(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}${36 + _date.Month}) / " & $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}${36 + _date.Month}"
                End If

                sheet.Cells(row + i - 1, col).Formula = actualFormula
                sheet.Cells(row + i - 1, col + 1).Formula = targetFormula
            Next
        End Sub

        Private Sub LoadCircularIcons(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For i As Integer = 0 To _kpiList.Items.Count - 1
                UpdateCircularIcon(sheet, row + i, col)
            Next
        End Sub

        Private Sub UpdateCircularIcon(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim actual As Double = 0, target As Double = 0

            If Not Double.TryParse(sheet.Cells(row, col + 8).Value.ToString(), actual) Then
                actual = 1
                sheet.Cells(row, col + 8).Value = 1
            End If

            If Not Double.TryParse(sheet.Cells(row, col + 9).Value.ToString(), target) Then
                target = 1
                sheet.Cells(row, col + 9).Value = 1
            End If

            sheet.Cells(row, col).Value = _circularSymbol

            If actual < 0.95 * target Then
                sheet.Cells(row, col).ForeColor = Color.Orange
            ElseIf actual >= 0.95 * target AndAlso actual <= 1.05 * target AndAlso actual <> 0 AndAlso target <> 0 Then
                sheet.Cells(row, col).ForeColor = Color.Yellow
            Else
                sheet.Cells(row, col).Value = String.Empty
            End If
        End Sub

        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(BusinessKPI))
            Me.FpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me.FpSpread1_Sheet1 = Me.FpSpread1.GetSheet(0)
            Me.pnlMain.SuspendLayout()
            CType(Me.FpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me.FpSpread1)
            '
            'FpSpread1
            '
            Me.FpSpread1.AccessibleDescription = ""
            Me.FpSpread1.Dock = DockStyle.Fill
            Me.FpSpread1.Location = New System.Drawing.Point(2, 3)
            Me.FpSpread1.Name = "FpSpread1"
            Me.FpSpread1.Size = New System.Drawing.Size(474, 483)
            Me.FpSpread1.TabIndex = 0
            '
            'BusinessKPI
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.Name = "BusinessKPI"
            Me.pnlMain.ResumeLayout(False)
            CType(Me.FpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub
    End Class
End Namespace

'
'
'

Imports System
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.CellType
Imports System.Globalization
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.Models
Imports FarPoint.Win.Spread
Imports FarPoint.Win
Imports GrapeCity.BusinessKPI.Sparkline
Imports FarPoint.Win.Spread.Chart
Imports Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl
Imports GrapeCity.BusinessKPI.GrapeCity.BusinessKPI.Sparkline

Namespace GrapeCity.BusinessKPI
    Partial Public Class BusinessKPI
        Inherits Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl


        Private _circularSymbol As String
        Private _themeColor As Color
        Private _dataSource As KPIDataSource
        Private _trendTypes As String()
        Private _kpiList As KPIList
        Private _targetMasterList As List(Of TargetMaster)
        Private _currentYearMasterList As List(Of TargetMaster)
        Private _previousYearMasterList As List(Of TargetMaster)
        Private _exSetting As ExcelSparklineSetting
        Private _numCell As NumberCellType
        Private _decimalCell As NumberCellType
        Private _currencyCell As CurrencyCellType
        Private _percentCell As PercentCellType
        Private comboCellMonth As ComboBoxCellType = Nothing
        Private _dateFormatInfo As DateTimeFormatInfo
        Friend WithEvents FpSpread1 As FpSpread
        Friend WithEvents FpSpread1_Sheet1 As SheetView
        Private _date As DateTime

        Public Sub New()
            InitializeComponent()
            _circularSymbol = "●"
            _themeColor = Color.FromArgb(30, 122, 197)
            _dataSource = New DataService().KPIDataSource
            _kpiList = _dataSource.GetAllKPIs()
            _targetMasterList = _dataSource.GetAllTargets()
            _dateFormatInfo = New CultureInfo("en-US").DateTimeFormat
            _trendTypes = New String() {"ytd", "ytd", "%", "%", "ytd", "%", "yoy", "yoy", "%", "%"}
            _exSetting = New ExcelSparklineSetting With {
                .DisplayHidden = True
            }
            _numCell = New NumberCellType With {
                .DecimalPlaces = 0
            }
            _decimalCell = New NumberCellType With {
                .DecimalPlaces = 2,
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            _currencyCell = New CurrencyCellType With {
                .CurrencySymbol = "$",
                .DecimalPlaces = 2,
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            _percentCell = New PercentCellType With {
                .DecimalPlaces = 2,
                .PercentSign = "%",
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            AddHandler Me.Load, AddressOf BusinessKPI_Load
        End Sub

        Private Sub BusinessKPI_Load(ByVal sender As Object, ByVal e As EventArgs)
            SetSheetProperties(FpSpread1.ActiveSheet)
            SetDataSheetColors(FpSpread1.ActiveSheet, 15, 0)
            AddComboSelector(FpSpread1.ActiveSheet, 1, 2)
            LoadSelectedMetrics()
            SetDataSheetProperties(FpSpread1.ActiveSheet, 15, 0)
            LoadDataSheet(FpSpread1.ActiveSheet, 19, 0)
            LoadKPIList(FpSpread1.ActiveSheet, 4, 2)
            LoadMetricMeasures(FpSpread1.ActiveSheet, 4, 5)
            LoadMetrics(FpSpread1.ActiveSheet, 4, 9)
            LoadTargetSparklines(FpSpread1.ActiveSheet, 4, 11)
            LoadTrendSparklines(FpSpread1.ActiveSheet, 4, 6)
            LoadCircularIcons(FpSpread1.ActiveSheet, 4, 1)
        End Sub

        Private Sub ComboCell_EditingStopped(ByVal sender As Object, ByVal e As EventArgs)
            LoadSelectedMetrics()
            LoadYearlyDataHeaders(FpSpread1.ActiveSheet, 16, 0)
            LoadDataSheet(FpSpread1.ActiveSheet, 19, 0)
            LoadMetrics(FpSpread1.ActiveSheet, 4, 9)

            If _currentYearMasterList.Where(Function(l) l.Month = _date.Month).Any() Then
                LoadTargetSparklines(FpSpread1.ActiveSheet, 4, 11)
                LoadTrendSparklines(FpSpread1.ActiveSheet, 4, 6)
                LoadCircularIcons(FpSpread1.ActiveSheet, 4, 1)
            End If
        End Sub

        Private Sub SetDataSheetProperties(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Rows(row).Height = 40
            sheet.Rows(row).Font = New Font("Segoe UI", 14, FontStyle.Bold)
            sheet.Cells(row, col).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.AddSpanCell(row, col, 1, sheet.ColumnCount)
            sheet.Rows(row + 17).Height = 40
            sheet.Rows(row + 17).Font = New Font("Segoe UI", 14, FontStyle.Bold)
            sheet.Cells(row + 17, col).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.AddSpanCell(row + 17, col, 1, sheet.ColumnCount)
            sheet.AlternatingRows.Count = 2
            sheet.AlternatingRows(0).BackColor = Color.FromArgb(246, 246, 246)
            sheet.AlternatingRows(1).BackColor = Color.FromArgb(245, 245, 222)
            sheet.Cells(row + 4, col + 1, row + 15, sheet.ColumnCount - 1).Locked = False
            sheet.Cells(row + 21, col + 1, row + 32, sheet.ColumnCount - 1).Locked = False
            LoadYearlyDataHeaders(sheet, row + 1, col)
            AddSpanCellsToFirstColumn(sheet, 16, 0, 31)
            AddSpanCellsToFirstColumn(sheet, 33, 0, 47)
            SetDataSheetCellTypes(sheet, row + 4, col + 2)
            SetDataSheetCellTypes(sheet, row + 21, col + 2)
            sheet.Rows(14).BackColor = Color.White
            sheet.Rows(14).Border = New LineBorder(Color.White, 2, True, False, True, False)
            sheet.Rows(31).BackColor = Color.White
            sheet.Rows(31).Border = New LineBorder(Color.White, 2, True, False, True, False)
        End Sub

        Private Sub SetDataSheetCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row + 11, col + 1).CellType = _numCell
            sheet.Cells(row, col + 2, row + 11, col + 3).CellType = _currencyCell
            sheet.Cells(row, col + 4, row + 11, col + 5).CellType = _percentCell
            sheet.Cells(row, col + 6, row + 11, col + 7).CellType = _percentCell
            sheet.Cells(row, col + 8, row + 11, col + 9).CellType = _currencyCell
            sheet.Cells(row, col + 10, row + 11, col + 11).CellType = _percentCell
            sheet.Cells(row, col + 12, row + 11, col + 13).CellType = _numCell
            sheet.Cells(row, col + 14, row + 11, col + 15).CellType = _currencyCell
            sheet.Cells(row, col + 16, row + 11, col + 19).CellType = _percentCell
        End Sub

        Private Sub LoadDataSheet(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            LoadYearlyData(sheet, row, col, _currentYearMasterList, New DateTime(_date.Year, 1, 1))
            LoadYearlyData(sheet, row + 17, col, _previousYearMasterList, New DateTime(_date.Year - 1, 1, 1))
        End Sub

        Private Sub LoadYearlyDataHeaders(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            LoadYearlyDataHeaders(sheet, row, col, New DateTime(_date.Year, 1, 1))
            LoadYearlyDataHeaders(sheet, row + 17, col, New DateTime(_date.Year - 1, 1, 1))
        End Sub

        Private Sub SetDataSheetColors(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Rows(row).BackColor = _themeColor
            sheet.Rows(row, row + 3).ForeColor = Color.WhiteSmoke
            sheet.Rows(row + 1, row + 3).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 4, col, row + 15, 0).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 4, col, row + 15, 0).ForeColor = Color.WhiteSmoke
            sheet.Rows(row + 17).BackColor = _themeColor
            sheet.Rows(row + 17, row + 20).ForeColor = Color.WhiteSmoke
            sheet.Rows(row + 18, row + 20).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 21, col, row + 32, 0).BackColor = Color.FromArgb(150, 150, 150)
            sheet.Cells(row + 21, col, row + 32, 0).ForeColor = Color.WhiteSmoke
        End Sub

        Private Sub AddSpanCellsToFirstColumn(ByVal sheet As SheetView, ByVal startRow As Integer, ByVal col As Integer, ByVal endRow As Integer)
            For i As Integer = startRow To endRow
                sheet.AddSpanCell(i, col, 1, 2)
            Next
        End Sub

        Private Sub LoadYearlyDataHeaders(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal startDate As DateTime)
            sheet.Cells(row, col).Value = "KPI ID"
            sheet.Cells(row + 1, col).Value = "KPI Name"

            For i As Integer = 1 To _kpiList.Items.Count
                sheet.AddSpanCell(row, col + i * 2, 1, 2)
                sheet.Cells(row, col + i * 2).Value = i
                sheet.AddSpanCell(row + 1, col + i * 2, 1, 2)
                sheet.Cells(row + 1, col + i * 2).Value = _kpiList.Items(i)
                sheet.Cells(row + 2, col + i * 2).Value = "Actual"
                sheet.Cells(row + 2, col + i * 2 + 1).Value = "Target"
            Next
        End Sub

        Private Sub LoadYearlyData(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal targetMasterList As List(Of TargetMaster), ByVal startDate As DateTime)
            sheet.Cells(row, col, row + 11, sheet.ColumnCount - 1).ResetValue()

            If targetMasterList.Count = 0 Then
                Return
            End If

            sheet.Cells(row - 4, col).Value = $"KPI Data for the year {startDate.Year}"
            Dim date1 As DateTime = startDate
            Dim _rowCount As Integer = 0

            While date1.Year = startDate.Year
                sheet.Cells(row + _rowCount, col).Value = date1.ToString("MMM, yyyy")
                _rowCount += 1
                date1 = date1.AddMonths(1)
            End While

            _rowCount = row

            For Each targetMaster As TargetMaster In targetMasterList
                Dim _colCount As Integer = 1

                For Each target As KPIMetrics In targetMaster.KPITargets
                    sheet.Cells(_rowCount, col + _colCount * 2).Value = targetMaster.KPITargets(_colCount - 1).Actual
                    sheet.Cells(_rowCount, col + _colCount * 2 + 1).Value = targetMaster.KPITargets(_colCount - 1).Target
                    _colCount += 1
                Next

                _rowCount += 1
            Next
        End Sub

        Private Sub SetSheetProperties(ByVal sheet As SheetView)
            sheet.ColumnHeader.Visible = False
            sheet.RowHeader.Visible = False
            sheet.RowCount = 48
            sheet.ColumnCount = 22
            sheet.Columns(0, 1).Width = 50
            sheet.Columns(2, 20).Width = 87
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).VerticalAlignment = CellVerticalAlignment.Center
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).Locked = True
            sheet.AddSpanCell(0, 0, 2, 12)
            sheet.Cells(0, 0).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Cells(0, 0).Text = "Business KPI"
            sheet.Cells(0, 0).Font = New Font("Segoe UI", 16, FontStyle.Bold)
            sheet.Rows(0, 2).BackColor = _themeColor
            sheet.Rows(0, 2).ForeColor = Color.WhiteSmoke
            sheet.Cells(0, 0, 2, sheet.ColumnCount - 1).Border = New ComplexBorder(New ComplexBorderSide(_themeColor, 2), New ComplexBorderSide(_themeColor, 2), New ComplexBorderSide(_themeColor, 2), New ComplexBorderSide(_themeColor, 2))
            sheet.AddSpanCell(0, 12, 1, 2)
            sheet.Cells(0, 12).Value = "Select Month & Year"
            sheet.Cells(0, 12).Font = New Font("Segoe UI", 12, FontStyle.Bold)
            sheet.Cells(0, 12).ForeColor = Color.WhiteSmoke
            sheet.Rows(0, sheet.RowCount - 1).Height = 30
            sheet.Cells(3, 0).ColumnSpan = 5
            sheet.Cells(3, 0).Value = "Business KPIs"
            sheet.Cells(3, 5).Value = "Metric"
            sheet.Cells(3, 6).ColumnSpan = 3
            sheet.Cells(3, 6).Value = "Last Year"
            sheet.Cells(3, 9).Tag = "MonthYearCell"
            sheet.Cells(3, 10).Value = "Target"
            sheet.Cells(3, 11).ColumnSpan = 4
            sheet.Cells(3, 11).Value = "% vs. Target"
            sheet.Cells(3, 0, 3, 14).Border = New LineBorder(Color.Black, 1, False, True, False, True)
            sheet.Cells(4, 1, 13, 1).Font = New Font("Segoe UI", 16, FontStyle.Regular)

            For i As Integer = 0 To _kpiList.Items.Count - 1
                sheet.AddSpanCell(4 + i, 6, 1, 3)
                sheet.AddSpanCell(4 + i, 11, 1, 4)
            Next

            SetCellTypes(sheet, 4, 9)
        End Sub

        Private Sub SetCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row + 9, col + 1).CellType = _percentCell
        End Sub

        Private Sub AddComboSelector(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Rows(row).Height = 25
            comboCellMonth = New ComboBoxCellType With {
                .DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                .Editable = False,
                .Items = _dateFormatInfo.MonthNames.Take(DateTime.Now.Month).ToArray(),
                .StopEditingAfterDropDownItemSelected = True
            }
            AddHandler comboCellMonth.EditingStopped, AddressOf ComboCell_EditingStopped
            Dim comboCellYear As ComboBoxCellType = New ComboBoxCellType With {
                .DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                .Editable = False,
                .Items = New String() {(DateTime.Now.Year - 2).ToString(), (DateTime.Now.Year - 1).ToString(), DateTime.Now.Year.ToString()},
                .StopEditingAfterDropDownItemSelected = True
            }
            AddHandler comboCellYear.EditingStopped, AddressOf ComboCell_EditingStopped
            sheet.Cells(row, col + 10, row, col + 11).BackColor = Color.WhiteSmoke
            sheet.Cells(row, col + 10, row, col + 11).ForeColor = Color.Black
            sheet.Cells(row, col + 10).CellType = comboCellMonth
            sheet.Cells(row, col + 10).Value = DateTime.Now.ToString("MMMM")
            sheet.Cells(row, col + 10).Tag = "MonthCell"
            sheet.Cells(row, col + 10).Locked = False
            sheet.Cells(row, col + 11).CellType = comboCellYear
            sheet.Cells(row, col + 11).Value = DateTime.Now.ToString("yyyy")
            sheet.Cells(row, col + 11).Tag = "YearCell"
            sheet.Cells(row, col + 11).Locked = False
            sheet.Rows(row + 1).Height = 10
            sheet.AddSpanCell(row + 1, col + 1, 1, 3)
            sheet.Cells(row + 1, col + 1).ForeColor = Color.WhiteSmoke
            sheet.Cells(row + 1, col + 1).Font = New Font("Segoe UI", 9, FontStyle.Bold)
        End Sub

        Private Sub LoadTrendSparklines(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim srcRowStart, srcRowEnd As Integer

            If _previousYearMasterList.Count = 0 Then
                sheet.Cells(row - 1, col).Value = "This Year (12 months)"
                srcRowStart = 19
                srcRowEnd = 30
            Else
                sheet.Cells(row - 1, col).Value = "Last Year (12 months)"
                srcRowStart = 36
                srcRowEnd = 47
            End If

            For i As Integer = 0 To _kpiList.Items.Count - 1
                AddTrendSparkline(sheet, row + i, col, srcRowStart, srcRowEnd)
            Next
        End Sub

        Private Sub AddTrendSparkline(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal srcRowStart As Integer, ByVal srcRowEnd As Integer)
            Dim group As ExcelSparklineGroup = New ExcelSparklineGroup()
            Dim setting As ExcelSparklineSetting = New ExcelSparklineSetting()
            Dim sparkline As ExcelSparkline = New ExcelSparkline()
            sheet.SparklineContainer.Add(group)
            group.Renderer = New LineSparklineRenderer()
            group.Setting = setting
            sparkline.Formula = $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + (row - 3) * 2)).ToString()}${srcRowStart + 1}:" & $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + (row - 3) * 2)).ToString()}${srcRowEnd + 1}"
            setting.ShowNegative = True
            sparkline.Row = row
            sparkline.Column = col
            group.Add(sparkline)
        End Sub

        Private Sub LoadTargetSparklines(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For i As Integer = 0 To _kpiList.Items.Count - 1
                AddTargetSparkline(sheet, row + i, col)
            Next
        End Sub

        Private Sub AddTargetSparkline(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim sparkline As TargetSparkline = New TargetSparkline(sheet, row, col, New SheetCellRange(sheet, row, col - 2, 1, 1), New SheetCellRange(sheet, row, col - 1, 1, 1))
            Dim group As TargetSparklineGroup = New TargetSparklineGroup()
            Dim setting As TargetSparklineSetting = New TargetSparklineSetting()
            sheet.SparklineContainer.Add(group)
            group.Setting = setting
            group.Renderer = New TargetSparklineRenderer()
            group.Add(sparkline)
        End Sub

        Private Sub LoadMetricMeasures(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For Each trendType As String In _trendTypes
                sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = trendType
            Next
        End Sub

        Private Sub LoadKPIList(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For Each pair As KeyValuePair(Of Integer, String) In _kpiList.Items
                sheet.Cells(row, col - 2).Value = pair.Key
                sheet.AddSpanCell(row, col, 1, 3)
                sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = pair.Value
            Next
        End Sub

        Private Sub LoadSelectedMetrics()
            Dim year As Integer = Integer.Parse(FpSpread1.Sheets(0).Cells("YearCell").Value.ToString())
            comboCellMonth.Items = _dateFormatInfo.MonthNames.Take((If(DateTime.Now.Year = year, DateTime.Now.Month, 12))).ToArray()

            If FpSpread1.Sheets(0).Cells("MonthCell").Text = "" Then
                FpSpread1.Sheets(0).Cells("MonthCell").Value = comboCellMonth.Items.Last()
            End If

            Dim month As Integer = _dateFormatInfo.MonthNames.ToList().IndexOf(FpSpread1.Sheets(0).Cells("MonthCell").Value.ToString()) + 1
            _date = New DateTime(year, month, 1)
            _currentYearMasterList = _targetMasterList.Where(Function(t) t.Year = year).OrderBy(Function(t) t.Month).ToList()
            _previousYearMasterList = _targetMasterList.Where(Function(t) t.Year = year - 1).OrderBy(Function(t) t.Month).ToList()
            FpSpread1.Sheets(0).Cells("MonthYearCell").Value = $"{_date.ToString("MMM")}/{_date.ToString("yyyy")}"
            FpSpread1.ActiveSheet.Rows(31, 47).Visible = (_previousYearMasterList.Count <> 0)
        End Sub

        Private Sub ClearPreviousData(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(2, 3).ResetValue()
            sheet.Cells(row, col, row + _kpiList.Items.Count - 1, col + 1).ResetValue()
            sheet.ClearSparklines(New FarPoint.Win.Spread.Model.CellRange(row, col - 3, 10, 1))
            sheet.ClearSparklines(New FarPoint.Win.Spread.Model.CellRange(row, col + 2, 10, 1))
        End Sub

        Private Sub LoadMetrics(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            ClearPreviousData(sheet, row, col)

            If Not _currentYearMasterList.Where(Function(m) m.Year = _date.Year AndAlso m.Month = _date.Month).Any() Then
                sheet.Cells(2, 3).Value = $"No data available for {_date.ToString("MMM, yyyy")}"
                Return
            End If

            sheet.Cells(2, 3).Value = String.Empty
            LoadActualAndTargetMetrics(sheet, row, col)
        End Sub

        Private Sub LoadActualAndTargetMetrics(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim previousYearTarget As TargetMaster = _previousYearMasterList.Where(Function(p) p.Month = _date.Month).FirstOrDefault()

            For i As Integer = 1 To _trendTypes.Count()
                Dim actualFormula As String = String.Empty
                Dim targetFormula As String = String.Empty

                If _trendTypes(i - 1) = "%" Then
                    sheet.Cells(row + i - 1, col - 2).Value = "%"
                    actualFormula = $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}${19 + _date.Month}"
                    targetFormula = $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}${19 + _date.Month}"
                ElseIf _trendTypes(i - 1) = "ytd" OrElse previousYearTarget Is Nothing Then
                    sheet.Cells(row + i - 1, col - 2).Value = "ytd"
                    targetFormula = $"SUM(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}${19 + _date.Month}) / SUM(Sheet1!$" & $"{(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$" & $"{31})"
                    actualFormula = $"SUM(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2)).ToString()}${19 + _date.Month}) / SUM(Sheet1!$" & $"{(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$20:" & $"${(Chr(Convert.ToByte(CChar("A")) + i * 2 + 1)).ToString()}$" & $"{31})"
                Else
                    sheet.Cells(row + i - 1, col - 2).Value = "yoy"
                    actualFormula = $"(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}$" & $"{19 + _date.Month} - Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}${36 + _date.Month}) / " & $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i)).ToString()}${36 + _date.Month}"
                    targetFormula = $"(Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}$" & $"{19 + _date.Month} - Sheet1!{(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}${36 + _date.Month}) / " & $"Sheet1!${(Chr(Convert.ToByte(CChar("A")) + 2 * i + 1)).ToString()}${36 + _date.Month}"
                End If

                sheet.Cells(row + i - 1, col).Formula = actualFormula
                sheet.Cells(row + i - 1, col + 1).Formula = targetFormula
            Next
        End Sub

        Private Sub LoadCircularIcons(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            For i As Integer = 0 To _kpiList.Items.Count - 1
                UpdateCircularIcon(sheet, row + i, col)
            Next
        End Sub

        Private Sub UpdateCircularIcon(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            Dim actual As Double = 0, target As Double = 0

            If Not Double.TryParse(sheet.Cells(row, col + 8).Value.ToString(), actual) Then
                actual = 1
                sheet.Cells(row, col + 8).Value = 1
            End If

            If Not Double.TryParse(sheet.Cells(row, col + 9).Value.ToString(), target) Then
                target = 1
                sheet.Cells(row, col + 9).Value = 1
            End If

            sheet.Cells(row, col).Value = _circularSymbol

            If actual < 0.95 * target Then
                sheet.Cells(row, col).ForeColor = Color.Orange
            ElseIf actual >= 0.95 * target AndAlso actual <= 1.05 * target AndAlso actual <> 0 AndAlso target <> 0 Then
                sheet.Cells(row, col).ForeColor = Color.Yellow
            Else
                sheet.Cells(row, col).Value = String.Empty
            End If
        End Sub

        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(BusinessKPI))
            Me.FpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me.FpSpread1_Sheet1 = Me.FpSpread1.GetSheet(0)
            Me.pnlMain.SuspendLayout()
            CType(Me.FpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me.FpSpread1)
            '
            'FpSpread1
            '
            Me.FpSpread1.AccessibleDescription = ""
            Me.FpSpread1.Dock = DockStyle.Fill
            Me.FpSpread1.Location = New System.Drawing.Point(2, 3)
            Me.FpSpread1.Name = "FpSpread1"
            Me.FpSpread1.Size = New System.Drawing.Size(474, 483)
            Me.FpSpread1.TabIndex = 0
            '
            'BusinessKPI
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.Name = "BusinessKPI"
            Me.pnlMain.ResumeLayout(False)
            CType(Me.FpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub
    End Class
End Namespace
