Imports System.Drawing
Imports FarPoint.Win
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports GrapeCity.Spreadsheet
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Partial Public Class CascadeSparkline
        Inherits BaseSampleControl
        Public Sub New()
            InitializeComponent()
            Name = "Cascade"
            _fpSpread.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.All
            InitCascadeSparklineWithType(_fpSpread.Sheets(0))
            InitHorizontalSparkline(_fpSpread.Sheets(1))
            InitVerticalSparkline(_fpSpread.Sheets(2))
        End Sub

#Region "Private Methods"
        Private Sub InitCascadeSparklineWithType(ByVal sheetView As SheetView)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "Item Type"
            worksheet.Cells("B1").Value = "Income Statement"
            worksheet.Cells("B1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 12,
                .Underline = GrapeCity.Spreadsheet.UnderlineStyle.Single
            })

            sheetView.Cells("B1").ForeColor = System.Drawing.Color.Gray
            worksheet.Cells("B2").Value = "Profit after tax in mUSD"
            worksheet.Cells("B3").Value = "2019 PL, AC"
            worksheet.Cells("K1").Value = "Year"
            worksheet.Cells("K1").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Right
            worksheet.Cells("K1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 10,
                .Underline = GrapeCity.Spreadsheet.UnderlineStyle.Single
            })

            worksheet.Cells("K2").Value = "2019"
            worksheet.Cells("K2").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Right
            worksheet.Cells("B4:K4").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
            worksheet.Cells("B4:K4").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("B4:K4").Font.Bold = True
            worksheet.Cells("B4:K4").Font.Underline = GrapeCity.Spreadsheet.UnderlineStyle.Single
            worksheet.Columns("A").ColumnWidth = 20
            worksheet.Columns("B").ColumnWidth = 320
            worksheet.Columns("C").ColumnWidth = 80
            worksheet.Columns("E").ColumnWidth = 300
            worksheet.Columns("F").ColumnWidth = 70
            worksheet.Columns("G").ColumnWidth = 80
            worksheet.Columns("H").ColumnWidth = 80
            worksheet.Columns("I").ColumnWidth = 300
            worksheet.Rows("4:20").RowHeight = 24

            worksheet.SetValue(3, 0, New Object(,) {
            {Nothing, Nothing, "PY", "ΔPY", "ΔPY", "ΔPY %", "AC", "ΔPL", "ΔPL", "ΔPL %", "PL"},
            {"+", "Software Revenue", 265000000, Nothing, Nothing, Nothing, 278000000, Nothing, Nothing, Nothing, 273000000},
            {"+", "Support Revenue", 87000000, Nothing, Nothing, Nothing, 90000000, Nothing, Nothing, Nothing, 87000000},
            {"+", "Consulting Revenue", 121000000, Nothing, Nothing, Nothing, 128000000, Nothing, Nothing, Nothing, 122000000},
            {"=", "Revenue", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing},
            {"-", "COGS", 122000000, Nothing, Nothing, Nothing, 128000000, Nothing, Nothing, Nothing, 104000000},
            {"=", "Gross Profit", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing},
            {"-", "Research and development expenses", 78000000, Nothing, Nothing, Nothing, 91000000, Nothing, Nothing, Nothing, 96000000},
            {"-", "Selling and general adminstrative expenses", 97000000, Nothing, Nothing, Nothing, 102000000, Nothing, Nothing, Nothing, 89000000},
            {"-", "Other operating expenses", 19000000, Nothing, Nothing, Nothing, 10000000, Nothing, Nothing, Nothing, 14000000},
            {"+", "Other financial income, net", 34000000, Nothing, Nothing, Nothing, 30000000, Nothing, Nothing, Nothing, 28000000},
            {"+", "Other operation income", 33000000, Nothing, Nothing, Nothing, 27000000, Nothing, Nothing, Nothing, 10000000},
            {"=", "Income from continuing operations before tax", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing},
            {"-", "Income tax expenses", 23000000, Nothing, Nothing, Nothing, 27000000, Nothing, Nothing, Nothing, 31000000},
            {"=", "Income from continuing operations", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing},
            {"+", "Income from discontinued operations", 6000000, Nothing, Nothing, Nothing, 25000000, Nothing, Nothing, Nothing, 20000000},
            {"=", "Net Income", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing}})

            worksheet.Rows("8").Font.Bold = True
            worksheet.Rows("10").Font.Bold = True
            worksheet.Rows("16").Font.Bold = True
            worksheet.Rows("18").Font.Bold = True
            worksheet.Rows("20").Font.Bold = True

            worksheet.Cells("C8").Formula = "SUM(C5:C7)"
            worksheet.Cells("C10").Formula = "C8-C9"
            worksheet.Cells("C16").Formula = "C10-C11-C12-C13+C14+C15"
            worksheet.Cells("C18").Formula = "C16-C17"
            worksheet.Cells("C20").Formula = "C18+C19"

            worksheet.Cells("G8").Formula = "SUM(G5:G7)"
            worksheet.Cells("G10").Formula = "G8-G9"
            worksheet.Cells("G16").Formula = "G10-G11-G12-G13+G14+G15"
            worksheet.Cells("G18").Formula = "G16-G17"
            worksheet.Cells("G20").Formula = "G18+G19"

            worksheet.Cells("K8").Formula = "SUM(K5:K7)"
            worksheet.Cells("K10").Formula = "K8-K9"
            worksheet.Cells("K16").Formula = "K10-K11-K12-K13+K14+K15"
            worksheet.Cells("K18").Formula = "K16-K17"
            worksheet.Cells("K20").Formula = "K18+K19"

            Dim numberFormat As String = "#,##0,,""M"""
            worksheet.Range("C5:C20").NumberFormat = numberFormat
            worksheet.Range("G5:G20").NumberFormat = numberFormat
            worksheet.Range("D5:D20").NumberFormat = numberFormat
            worksheet.Range("H5:H20").NumberFormat = numberFormat
            worksheet.Range("K5:K20").NumberFormat = numberFormat

            For row As Integer = 5 To 20
                worksheet.Cells($"D{row}").Formula = $"G{row}-C{row}"
                worksheet.Cells($"F{row}").Formula = $"(G{row}-C{row})/C{row}*100"
                worksheet.Cells($"H{row}").Formula = $"G{row}-K{row}"
                worksheet.Cells($"J{row}").Formula = $"(G{row}-K{row})/K{row}*100"
            Next

            worksheet.Cells("F5:F20").CellType = New NumberCellType() With {
                .DecimalPlaces = 0,
                .NegativeRed = True
            }
            worksheet.Cells("J5:J20").CellType = New NumberCellType() With {
                .DecimalPlaces = 0,
                .NegativeRed = True
            }

            worksheet.Cells("P5").Formula2 = "TEXT(D5:D20,""#,##0,,"")& ""M"""
            worksheet.Cells("Q5").Formula2 = "TEXT(H5:H20,""#,##0,,"")& ""M"""
            worksheet.Cells("E5").Formula2 = "CASCADESPARKLINE(D5:D20,,P5:P20,,,""#8CBF64"",""#D6604D"",false,A5:A20)"
            worksheet.Cells("I5").Formula2 = "CASCADESPARKLINE(H5:H20,,Q5:Q20,,,""#8CBF64"",""#D6604D"",false,A5:A20)"
            worksheet.Columns("D").Hidden = True
            worksheet.Columns("H").Hidden = True
            worksheet.Columns("P").Hidden = True
            worksheet.Columns("Q").Hidden = True
        End Sub

        Private Sub InitHorizontalSparkline(ByVal sheetView As SheetView)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "Horizontal"
            worksheet.Cells("A1:E1").Merge()
            worksheet.Rows("1").RowHeight = 30
            worksheet.Cells("A1").Value = "Budget Expense"
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 12,
                .Bold = True
            })

            sheetView.Cells("A1").BackColor = System.Drawing.Color.Gray
            sheetView.Cells("A1").ForeColor = System.Drawing.Color.White
            worksheet.Columns("A").ColumnWidth = 100
            worksheet.Columns("B").ColumnWidth = 170
            worksheet.Columns("C").ColumnWidth = 80
            worksheet.Columns("D").ColumnWidth = 80
            worksheet.Columns("E").ColumnWidth = 400
            worksheet.Rows("2:15").RowHeight = 24

            worksheet.SetValue(1, 0, New Object(,) {
            {Nothing, "Opening Balance", 950, "Σ"},
            {"11/1/2020", "Bank Interest", 20, Nothing},
            {"11/4/2020", "Rent", -390, Nothing},
            {"11/4/2020", "Utilities", -195, Nothing},
            {"11/5/2020", "Insurance", -87, Nothing},
            {"11/10/2020", "Salary", 2000, Nothing},
            {"11/11/2020", "Medical Expenses", -200, Nothing},
            {"11/11/2020", "Groceries", -850, Nothing},
            {"11/15/2020", "Cell Phones", -15, Nothing},
            {"11/16/2020", "Savings", 120, Nothing},
            {"11/19/2020", "Entertaimant", -158, Nothing},
            {"11/20/2020", "Gas", -50, Nothing},
            {"11/25/2020", "Luxuries", -100, Nothing},
            {Nothing, "Closing Balance", Nothing, Nothing}})

            worksheet.Cells("C15").Formula = "D14"
            worksheet.Cells("D3").Formula = "C2+C3"

            For row As Integer = 3 To 13
                worksheet.Cells($"D{row + 1}").Formula = $"D{row}+C{row + 1}"
            Next

            Dim currencyCellType As CurrencyCellType = New CurrencyCellType() With {
                .CurrencySymbol = "$",
                .PositiveFormat = CurrencyPositiveFormat.CurrencySymbolAfterWithSpace,
                .NegativeFormat = CurrencyNegativeFormat.SignBeforeSpaceSymbolAfter,
                .DecimalPlaces = 0
            }

            worksheet.Columns("C").CellType = currencyCellType
            worksheet.Cells("D3:D14").CellType = currencyCellType
            worksheet.Cells("A3:A14").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
            worksheet.Cells("A3:A14").CellType = New DateTimeCellType() With {
                .DateTimeFormat = DateTimeFormat.UserDefined,
                .UserDefinedFormat = "yyyy-MM-dd"
            }
            worksheet.Cells("D2").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
            worksheet.Cells("D2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A2:E2").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Bottom = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })
            worksheet.Cells("A14:E14").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Bottom = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })
            worksheet.Cells("B2:D2").Font.Bold = True
            worksheet.Cells("B2:D2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("B15:C15").Font.Bold = True
            worksheet.Cells("B15:C15").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center

            For i As Integer = 2 To 15
                worksheet.Cells($"E{i}").Formula2 = "CASCADESPARKLINE($C$2:$C$15,ROW()-1,$B$2:$B$15,,,""#8CBF64"",""#D6604D"",false)"
            Next
        End Sub

        Private Sub InitVerticalSparkline(ByVal sheetView As SheetView)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "Vertical"
            worksheet.Cells("A1:E1").Merge()
            worksheet.Rows("1").RowHeight = 30
            worksheet.Cells("A1").Value = "Inventory Audit"
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 12,
                .Bold = True
            })
            sheetView.Cells("A1").BackColor = System.Drawing.Color.Gray
            sheetView.Cells("A1").ForeColor = System.Drawing.Color.White
            worksheet.SetValue(1, 0, New Object(,) {
            {"Stock", "Damaged", "Refurbished", "Returns", "Salable Units"},
            {790, -220, 170, 83, 823}})
            worksheet.Rows("2").RowHeight = 24
            worksheet.Rows("3").RowHeight = 24
            worksheet.Rows("4").RowHeight = 300
            worksheet.Rows("3").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Left
            worksheet.Cells("A2:A3").Font.Bold = True
            worksheet.Cells("A2:A4").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Right = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })
            worksheet.Cells("E2:E3").Font.Bold = True
            worksheet.Cells("E2:E4").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Left = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })

            For c As Integer = 65 To 70 - 1
                Dim col As Char = Convert.ToChar(c)
                worksheet.Columns($"{col}").ColumnWidth = 100
                worksheet.Cells($"{col}4").Formula2 = "CASCADESPARKLINE($A$3:$E$3,COLUMN(),$A$2:$E$2,,,""#8CBF64"",""#D6604D"",true)"
            Next
        End Sub
#End Region
    End Class
End Namespace


'
'CascadeSparkline.Designer.vb
'


Namespace Features
    <Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()>
    Partial Class CascadeSparkline

        'UserControl overrides dispose to clean up the component list.
        <System.Diagnostics.DebuggerNonUserCode()>
        Protected Overloads Sub Dispose(ByVal disposing As Boolean)
            Try
                If disposing AndAlso components IsNot Nothing Then
                    components.Dispose()
                End If
            Finally
                MyBase.Dispose(disposing)
            End Try
        End Sub

        'Required by the Windows Form Designer
        Private components As System.ComponentModel.IContainer

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer.  
        'Do not modify it using the code editor.
        <System.Diagnostics.DebuggerStepThrough()>
        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(CascadeSparkline))
            Me._fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me._fpSpread_Sheet1 = Me._fpSpread.GetSheet(0)
            Me._fpSpread_Sheet2 = Me._fpSpread.GetSheet(1)
            Me._fpSpread_Sheet3 = Me._fpSpread.GetSheet(2)
            Me.pnlMain.SuspendLayout()
            CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me._fpSpread)
            '
            '_fpSpread
            '
            Me._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
            Me._fpSpread.Font = New System.Drawing.Font("Calibri", 11.0!)
            Me._fpSpread.Location = New System.Drawing.Point(0, 0)
            Me._fpSpread.Name = "_fpSpread"
            Me._fpSpread.Size = New System.Drawing.Size(477, 487)
            Me._fpSpread.TabIndex = 0
            '
            'CascadeSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "CascadeSparkline"
            Me.pnlMain.ResumeLayout(False)
            CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub

        Friend WithEvents _fpSpread As FarPoint.Win.Spread.FpSpread
        Friend WithEvents _fpSpread_Sheet1 As FarPoint.Win.Spread.SheetView
        Friend WithEvents _fpSpread_Sheet2 As FarPoint.Win.Spread.SheetView
        Friend WithEvents _fpSpread_Sheet3 As FarPoint.Win.Spread.SheetView
    End Class
End Namespace
