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


Namespace GrapeCity.FinancialKPI
    Public Partial Class FinancialKPIControl
        Inherits BaseSampleControl

        Private Shared _isSheetReset As Boolean
        Private _dateFormatInfo As DateTimeFormatInfo
        Private _date As DateTime
        Private _dataSource As FinancialKPIDataSource
        Private _kpiList As FinancialKPIList
        Private _targetMasterList As List(Of FinancialTargetMaster)
        Private _currentMonthTargetMaster As FinancialTargetMaster
        Private _previousMonthTargetMaster As FinancialTargetMaster
        Private _numCell As NumberCellType
        Private _currencyCell As CurrencyCellType
        Private _percentCell As PercentCellType
        Private _maskDaysCell As MaskCellType
        Private _maskActivityRatioCell As MaskCellType
        Private _maskRatioCell As MaskCellType
        Private _comboCellMonth As ComboBoxCellType
        Private _greenColor As Color
        Private _tickSymbol As String
        Private _crossSymbol As String
        Private _upSymbol As String
        Private _downSymbol As String
        Private _noChangeSymbol As String

        Public Sub New()
            InitializeComponent()
            _tickSymbol = "✓"
            _crossSymbol = "✕"
            _upSymbol = "▲"
            _downSymbol = "▼"
            _noChangeSymbol = "▬"
            _greenColor = Color.FromArgb(0, 176, 80)
            _numCell = New NumberCellType With {
                .DecimalPlaces = 0
            }
            _currencyCell = New CurrencyCellType With {
                .CurrencySymbol = "$",
                .DecimalPlaces = 0,
                .ShowSeparator = True,
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            _percentCell = New PercentCellType With {
                .DecimalPlaces = 2,
                .PercentSign = "%",
                .FocusPosition = EditorFocusCursorPosition.[End]
            }
            _maskDaysCell = New MaskCellType With {
                .NullDisplay = String.Empty,
                .MaskChar = " "c,
                .Mask = "XXX days"
            }
            _maskActivityRatioCell = New MaskCellType With {
                .NullDisplay = String.Empty,
                .MaskChar = " "c,
                .Mask = "XXXX times"
            }
            _maskRatioCell = New MaskCellType With {
                .NullDisplay = String.Empty,
                .MaskChar = " "c,
                .Mask = "XXXX:\1"
            }
            _dateFormatInfo = New CultureInfo("en-US").DateTimeFormat
            _dataSource = New DataService().FinancialKPIDataSource
            _kpiList = _dataSource.GetAllKPIs()
            _targetMasterList = _dataSource.GetAllTargets()
            AddHandler Load, AddressOf FinancialKPIControl_Load
        End Sub

        Private Sub FinancialKPIControl_Load(ByVal sender As Object, ByVal e As EventArgs)
            fpSpread.ActiveSheet.OpenExcel("Template\Financial KPIs.xlsx", 0)
            fpSpread.Sheets.Add(New SheetView("Workspace"))
            fpSpread.SuspendLayout()
            SetSheetProperties(fpSpread.Sheets(0))
            AddComboSelector(fpSpread.Sheets(0))
            LoadData(fpSpread.Sheets(0))
            SetSecondarySheetProperties(fpSpread.Sheets("Workspace"))
            fpSpread.ResumeLayout(True)
        End Sub

        Private Sub ComboCell_EditingStopped(ByVal sender As Object, ByVal e As EventArgs)
            fpSpread.SuspendLayout()
            LoadData(fpSpread.Sheets(0))
            fpSpread.ResumeLayout(True)
        End Sub

        Private Sub SetSheetProperties(ByVal sheet As SheetView)
            sheet.FpSpread.Font = New Font("Helvetica Neue", 10, FontStyle.Regular)
            sheet.RowCount = 26
            sheet.ColumnCount = 9
            sheet.FpSpread.TabStripPolicy = TabStripPolicy.Always
            sheet.FpSpread.TabStripInsertTab = False
            sheet.FpSpread.ButtonDrawMode = ButtonDrawModes.Always
            sheet.RowHeader.Visible = False
            sheet.ColumnHeader.Visible = False
            sheet.Columns(3).Visible = False
            sheet.Columns(2).Width = 450
            sheet.Columns(4, 5).Width = 180
            sheet.Columns(6, 7).Width = 40
            sheet.Columns(8).Width = 180
            Dim row As Integer = 6

            Dim item As Object
            For Each item In _kpiList.Items

                If sheet.Cells(row, 1).Text.Length = 1 Then
                    row += 1
                End If

                sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), 1).Text = item.Value.Name
            Next

            sheet.Cells(1, 0, 2, sheet.ColumnCount - 1).BackColor = Color.FromArgb(232, 217, 174)
            sheet.Cells(2, 4).Tag = "DataErrorCell"
            sheet.Cells(2, 4).Font = New Font("Segoe UI", 12, FontStyle.Regular)
            sheet.Cells(2, 4).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(2, 4).VerticalAlignment = CellVerticalAlignment.Center
            sheet.Cells(6, 3, sheet.RowCount - 1, sheet.ColumnCount - 1).ResetCellType()
            SetActualAndTargetCellTypes(sheet, 6, 4)
            SetTrendCellTypes(sheet, 6, 8)
            sheet.Protect = True
            sheet.Cells(4, 4, sheet.RowCount - 1, sheet.ColumnCount - 1).Locked = True
            SetupFormulas()
            SetupConditionalFormatting(sheet)
        End Sub

        Private Sub SetupFormulas()
            Dim mainSheet As SheetView = fpSpread.Sheets(0)

            For row As Integer = 5 To mainSheet.RowCount - 1
                Dim kpi As String = mainSheet.Cells(row, 1).Text

                If _kpiList.Items.Values.Any(Function(value) value.Name = kpi) Then
                    Dim itemPair = _kpiList.Items.Where(Function(item) item.Value.Name = kpi).First()
                    mainSheet.Cells(row, 4).Formula = $"Workspace!$C${itemPair.Key + 2}"
                    mainSheet.Cells(row, 5).Formula = $"Workspace!$D${itemPair.Key + 2}"

                    If itemPair.Value.Type = KPIType.Liability Then
                        mainSheet.Cells(row, 6).Formula = $"IF(Sheet1!$E${row} <= Sheet1!$F${row}, ""{_tickSymbol}"", ""{_crossSymbol}"")"
                    Else
                        mainSheet.Cells(row, 6).Formula = $"IF(Sheet1!$E${row} >= Sheet1!$F${row}, ""{_tickSymbol}"", ""{_crossSymbol}"")"
                    End If

                    Dim trendFormula As String = $"(Workspace!$C${itemPair.Key + 2} - Workspace!$H${itemPair.Key + 2})"
                    If itemPair.Value.Name.Contains("Revenue") Then trendFormula += $" / Workspace!$H${itemPair.Key + 2}"
                    mainSheet.Cells(row, 8).Formula = trendFormula
                End If
            Next
        End Sub

        Private Sub SetupConditionalFormatting(ByVal sheet As SheetView)
            Dim rule1 As UnaryComparisonConditionalFormattingRule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, _tickSymbol, False)
            rule1.ForeColor = Color.Green
            Dim rule2 As UnaryComparisonConditionalFormattingRule = New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, _crossSymbol, False)
            rule2.ForeColor = Color.Red
            sheet.SetConditionalFormatting(5, 6, sheet.RowCount - 4, 1, False, rule1, rule2)
        End Sub

        Private Sub SetActualAndTargetCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row, col + 1).CellType = _currencyCell
            sheet.Cells(row + 1, col, row + 3, col + 1).CellType = _percentCell
            sheet.Cells(row + 5, col, row + 5, col + 1).CellType = _maskActivityRatioCell
            sheet.Cells(row + 6, col, row + 7, col + 1).CellType = _maskDaysCell
            sheet.Cells(row + 9, col, row + 10, col + 1).CellType = _percentCell
            sheet.Cells(row + 12, col, row + 13, col + 1).CellType = _maskRatioCell
            sheet.Cells(row + 15, col, row + 19, col + 1).CellType = _percentCell
        End Sub

        Private Sub SetTrendCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row + 3, col).CellType = _percentCell
            sheet.Cells(row + 5, col).CellType = _maskActivityRatioCell
            sheet.Cells(row + 6, col, row + 7, col).CellType = _maskDaysCell
            sheet.Cells(row + 9, col, row + 10, col).CellType = _percentCell
            sheet.Cells(row + 12, col, row + 13, col).CellType = _maskRatioCell
            sheet.Cells(row + 15, col, row + 19, col).CellType = _percentCell
        End Sub

        Private Sub SetSecondarySheetProperties(ByVal sheet As SheetView)
            sheet.ColumnCount = 9
            sheet.RowCount = _kpiList.Items.Count + 2
            sheet.Columns(0, sheet.ColumnCount - 1).VerticalAlignment = CellVerticalAlignment.Center
            sheet.Columns(0, sheet.ColumnCount - 1).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Rows(0).Height = 40
            sheet.Rows(0).Font = New Font("Segoe UI", 14, FontStyle.Bold)
            sheet.Rows(0).VerticalAlignment = CellVerticalAlignment.Center
            sheet.Rows(0).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.AddSpanCell(0, 0, 1, 4)
            sheet.AddSpanCell(0, 5, 1, 4)
            sheet.Columns(0).Width = 80
            sheet.Columns(1).Width = 240
            sheet.Columns(2, 3).Width = 120
            sheet.Columns(4, 5).Width = 80
            sheet.Columns(6).Width = 240
            sheet.Columns(7, 8).Width = 120
            sheet.Columns(1).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Columns(6).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Rows(1, sheet.RowCount - 1).Height = 25
            sheet.AlternatingRows.Count = 2
            sheet.AlternatingRows(0).BackColor = Color.FromArgb(246, 246, 246)
            sheet.AlternatingRows(1).BackColor = Color.FromArgb(218, 218, 218)
            sheet.Columns(4).BackColor = Color.FromKnownColor(KnownColor.Window)
            sheet.Cells(0, 4, sheet.RowCount - 1, 4).Border = New LineBorder(Color.FromKnownColor(KnownColor.Window), 1, False, True, False, True)
            sheet.Cells(0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1).Locked = True
            sheet.Cells(2, 2, sheet.RowCount - 1, 3).Locked = False
            sheet.Cells(2, 7, sheet.RowCount - 1, 8).Locked = False
            SetSecondarySheetCellTypes(sheet, 2, 2)
            SetSecondarySheetCellTypes(sheet, 2, 7)
            LoadMonthlyData(sheet, 0, 0)
        End Sub

        Private Sub SetSecondarySheetCellTypes(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            sheet.Cells(row, col, row, col + 1).CellType = _currencyCell
            sheet.Cells(row + 1, col, row + 3, col + 1).CellType = _percentCell
            sheet.Cells(row + 4, col, row + 4, col + 1).CellType = _maskActivityRatioCell
            sheet.Cells(row + 5, col, row + 6, col + 1).CellType = _maskDaysCell
            sheet.Cells(row + 7, col, row + 8, col + 1).CellType = _percentCell
            sheet.Cells(row + 9, col, row + 10, col + 1).CellType = _maskRatioCell
            sheet.Cells(row + 11, col, row + 14, col + 1).CellType = _percentCell
        End Sub

        Private Sub AddComboSelector(ByVal sheet As SheetView)
            sheet.Cells(1, 4, 1, 8).Font = New Font(sheet.FpSpread.Font.SystemFontName, 12, FontStyle.Bold)
            sheet.Cells(1, 4, 1, 8).VerticalAlignment = CellVerticalAlignment.Center
            _comboCellMonth = New ComboBoxCellType With {
                .DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                .Editable = False,
                .Items = _dateFormatInfo.MonthNames,
                .ListWidth = 110,
                .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(1, 4).Text = "Month"
            sheet.Cells(1, 4).HorizontalAlignment = CellHorizontalAlignment.Right
            sheet.Cells(1, 5).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(1, 5).CellType = _comboCellMonth
            sheet.Cells(1, 5).Value = DateTime.Now.ToString("MMMM")
            sheet.Cells(1, 5).Locked = False
            sheet.Cells(1, 5).Tag = "MonthCell"
            sheet.AddSpanCell(1, 6, 1, 2)
            sheet.Cells(1, 6).Text = "Year"
            sheet.Cells(1, 6).HorizontalAlignment = CellHorizontalAlignment.Right
            sheet.Cells(1, 8).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(1, 8).CellType = comboCellYear
            sheet.Cells(1, 8).Value = DateTime.Now.ToString("yyyy")
            sheet.Cells(1, 8).Locked = False
            sheet.Cells(1, 8).Tag = "YearCell"
            sheet.AddSpanCell(2, 4, 1, 5)
            sheet.Cells(2, 4).Font = New Font("Segoe UI", 11, FontStyle.Bold)
            sheet.Cells(2, 4).HorizontalAlignment = CellHorizontalAlignment.Center
        End Sub

        Private Sub LoadMonthlyData(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer)
            If _currentMonthTargetMaster Is Nothing Then
                ClearData(sheet, row, col, sheet.RowCount - 1, col + 4)
            Else
                LoadMonthlyData(sheet, row, col, _currentMonthTargetMaster)
            End If

            If _previousMonthTargetMaster Is Nothing Then
                ClearData(sheet, row, col + 5, sheet.RowCount - 1, sheet.ColumnCount - 1)
            Else
                LoadMonthlyData(sheet, row, col + 5, _previousMonthTargetMaster)
            End If
        End Sub

        Private Sub LoadMonthlyData(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal targetMaster As FinancialTargetMaster)
            Dim date1 As DateTime = New DateTime(targetMaster.Year, targetMaster.Month, 1)
            sheet.Cells(row, col).BackColor = Color.FromArgb(232, 217, 174)
            sheet.Cells(row, col).Value = $"KPI data for {date1.ToString("MMM, yyyy")}"
            row += 1
            sheet.Cells(row, col, row, col + 3).BackColor = Color.DarkGray
            sheet.Cells(row, col, row, col + 3).ForeColor = Color.WhiteSmoke
            sheet.Cells(row, col).Value = "KPI ID"
            sheet.Cells(row, col + 1).Value = "KPI Name"
            sheet.Columns(col + 1).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(row, col + 2).Value = "Actual"
            sheet.Cells(row, col + 3).Value = "Target"

            For i As Integer = 1 To _kpiList.Items.Count
                sheet.Cells(row + i, col).Value = i
                sheet.Cells(row + i, col + 1).Value = _kpiList.Items(i).Name
                sheet.Cells(row + i, col + 2).Value = Math.Round(CDec(targetMaster.KPITargets(i - 1).Actual), 4)
                sheet.Cells(row + i, col + 3).Value = Math.Round(CDec(targetMaster.KPITargets(i - 1).Target), 4)
            Next
        End Sub

        Private Sub LoadSelectedMonthData()
            Dim year As Integer = Integer.Parse(fpSpread.Sheets(0).Cells("YearCell").Value.ToString())
            _comboCellMonth.Items = _dateFormatInfo.MonthNames.Take(If((year = DateTime.Now.Year), DateTime.Now.Month, 12)).ToArray()
            If fpSpread.Sheets(0).Cells("MonthCell").Text = String.Empty Then fpSpread.Sheets(0).Cells("MonthCell").Value = _comboCellMonth.Items.Last()
            Dim month As Integer = _dateFormatInfo.MonthNames.ToList().IndexOf(fpSpread.Sheets(0).Cells("MonthCell").Value.ToString()) + 1
            _date = New DateTime(year, month, 1)
            Dim prevMonthDate As DateTime = _date.AddMonths(-1)
            fpSpread.Sheets(0).Cells(5, 4).Value = _date.ToString("MMM yyyy")
            fpSpread.Sheets(0).Cells(5, 7).Value = $"vs {_date.AddMonths(-1).ToString("MMM yyyy")}"
            _currentMonthTargetMaster = _targetMasterList.Where(Function(t) t.Month = _date.Month AndAlso t.Year = _date.Year).FirstOrDefault()
            _previousMonthTargetMaster = _targetMasterList.Where(Function(t) t.Month = prevMonthDate.Month AndAlso t.Year = prevMonthDate.Year).FirstOrDefault()
            LoadMonthlyData(fpSpread.Sheets("Workspace"), 0, 0)
        End Sub

        Private Sub LoadData(ByVal sheet As SheetView)
            LoadSelectedMonthData()

            If _currentMonthTargetMaster Is Nothing Then
                sheet.ClearRange(5, 4, sheet.RowCount - 1, sheet.ColumnCount - 1, True)
                sheet.Cells("DataErrorCell").Value = $"No data available for {_date.ToString("MMM yyyy")}"
                _isSheetReset = True
                Return
            End If

            sheet.Cells("DataErrorCell").ResetValue()

            If _isSheetReset Then
                SetupFormulas()
                _isSheetReset = False
            End If

            If _previousMonthTargetMaster IsNot Nothing Then
                FillTrendColumn(sheet, 6, 8, 2, 7)
                sheet.Cells(5, 7).Value = $"vs {_date.AddMonths(-1).ToString("MMM yyyy")}"
            Else
                FillTrendColumn(sheet, 6, 8, 2, 3)
                sheet.Cells(5, 7).Value = $"vs Target"
            End If
        End Sub

        Private Sub FillTrendColumn(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal srcCol1 As Integer, ByVal srcCol2 As Integer)
            fpSpread.ResumeLayout(True)

            While row < sheet.RowCount
                Dim kpi As String = sheet.Cells(row, 1).Text

                If _kpiList.Items.Values.Any(Function(value) value.Name = kpi) Then
                    Dim value As Double = Convert.ToDouble(sheet.Cells(row, col).Value)
                    Dim kpiType As KPIType = _kpiList.Items.Where(Function(item) item.Value.Name = sheet.Cells(row, col - 7).Text).First().Value.Type

                    If kpiType = KPIType.Liability Then
                        SetCellSymbol(sheet, row, col - 1, If((value >= 0), (If(value > 0, _upSymbol, _noChangeSymbol)), _downSymbol), True)
                    Else
                        SetCellSymbol(sheet, row, col - 1, If((value >= 0), (If(value > 0, _upSymbol, _noChangeSymbol)), _downSymbol))
                    End If
                End If

                row += 1
            End While
        End Sub

        Private Sub SetCellSymbol(ByVal sheet As SheetView, ByVal row As Integer, ByVal col As Integer, ByVal symbol As String, ByVal Optional swapColors As Boolean = False)
            If symbol = _upSymbol Then
                sheet.Cells(row, col).ForeColor = If((swapColors), Color.Red, _greenColor)
            ElseIf symbol = _downSymbol Then
                sheet.Cells(row, col).ForeColor = If((swapColors), _greenColor, Color.Red)
            Else
                sheet.Cells(row, col).ForeColor = Color.DarkOrange
            End If

            sheet.Cells(row, col).Value = symbol
        End Sub

        Private Sub ClearData(ByVal sheet As SheetView, ByVal startRow As Integer, ByVal startCol As Integer, ByVal endRow As Integer, ByVal endCol As Integer)
            sheet.Cells(startRow, startCol, endRow, endCol).ResetValue()
            sheet.Cells(startRow, startCol).ResetBackColor()
            sheet.Cells(startRow + 1, startCol, startRow + 1, startCol + 3).ResetBackColor()
        End Sub
    End Class
End Namespace

'
'
'

Namespace GrapeCity.FinancialKPI
    Partial Class FinancialKPIControl
        Private components As System.ComponentModel.IContainer = Nothing

        Protected Shadows Sub Dispose(ByVal disposing As Boolean)
            If disposing AndAlso (components IsNot Nothing) Then
                components.Dispose()
            End If

            MyBase.Dispose(disposing)
        End Sub

        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()
            CType(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()

            CType(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            Me.pnlMain.Controls.Add(Me.fpSpread)
            Me.fpSpread.AllowCellOverflow = True
            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
            Me.fpSpread_Sheet1.Reset()
            Me.fpSpread_Sheet1.SheetName = "Sheet1"
            Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
            Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0F, 13.0F)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "FinancialKPIControl"
            Me.pnlMain.ResumeLayout(False)
            CType(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
            CType(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)
        End Sub

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