Imports System
Imports System.Collections.Generic
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports FarPoint.Win
Imports FarPoint.Win.Spread.CellType
Imports FarPoint.Win.Spread
Imports Spread.Common.Features.Spread.Common.Features.Sample

Imports Spread.Common.DataStore.Models
Imports Spread.Common.DataStore.Spread.Common.DataStore.Models

Namespace GrapeCity.InvoiceSample
    Partial Public Class InvoiceControl
        Inherits BaseSampleControl

        Private _dataService As Spread.Common.DataStore.DataService
        Private _invoice As Invoice
        Private _headingFontBold As Font
        Private _headingFontRegular As Font
        Private _bottomBorder As ComplexBorder
        Private _themeColor As Color
        Private _borderColor As Color
        Private _numCell As NumberCellType
        Private _decimalCell As NumberCellType
        Private _currencyCell As CurrencyCellType
        Private _percentCell As PercentCellType
        Private _dateTimeCell As DateTimeCellType

        Public Sub New()
            InitializeComponent()
            InitInvoiceControl()
        End Sub

        Private Sub InitInvoiceControl()
            SuspendLayout()
            _decimalCell = New NumberCellType With {
                .DecimalPlaces = 2
            }
            _numCell = New NumberCellType With {
                .DecimalPlaces = 0
            }
            _currencyCell = New CurrencyCellType With {
                .DecimalPlaces = 2,
                .CurrencySymbol = "$",
                .ShowCurrencySymbol = False,
                .ShowSeparator = True
            }
            _percentCell = New PercentCellType With {
                .DecimalPlaces = 3,
                .FixedPoint = True,
                .LeadingZero = LeadingZero.Yes
            }
            _dateTimeCell = New DateTimeCellType With {
                .DateSeparator = "/"
            }
            _dataService = New Spread.Common.DataStore.DataService()
            Dim customers As List(Of Customer) = _dataService.JsonDataSource.GetAllCustomers()
            _comboCustomer.DropDownStyle = ComboBoxStyle.DropDownList
            _comboCustomer.DataSource = customers
            _comboCustomer.DisplayMember = "ID"
            _comboCustomer.ValueMember = "ID"
            AddHandler _comboCustomer.SelectedIndexChanged, AddressOf ComboCustomer_SelectedIndexChanged
            AddHandler _comboCustomer.Format, AddressOf ComboCustomer_Format
            Dim id As Integer = CInt(_comboCustomer.SelectedValue)
            Dim invoices As List(Of Invoice) = _dataService.JsonDataSource.GetInvoiceByCustomerID(id)
            _invoiceListBox.DataSource = invoices
            _invoiceListBox.DisplayMember = "ID"
            _invoiceListBox.ValueMember = "ID"
            _invoiceListBox.CheckOnClick = True
            _invoiceListBox.SetItemChecked(0, True)
            _headingFontBold = New Font("Segoe UI", 8.0F, FontStyle.Bold)
            _headingFontRegular = New Font("Segoe UI", 8.0F, FontStyle.Regular)
            _themeColor = Color.FromArgb(100, 162, 173)
            _borderColor = Color.FromArgb(230, 230, 230)
            _bottomBorder = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_themeColor, 2))
            AddHandler _btnOK.Click, AddressOf BtnOK_Click
            AddHandler _fpSpread.ActiveSheetChanged, AddressOf FpSpread_ActiveSheetChanged
            _fpSpread.CreateControl()
            GenerateInvoice()
            ResumeLayout()
        End Sub

        Private Sub GenerateInvoice()
            Dim items As CheckedListBox.CheckedItemCollection = _invoiceListBox.CheckedItems

            If items.Count = 0 Then
                ShowInvoiceSelectionMsgSheet()
                Return
            End If

            Dim invoices As List(Of Invoice) = items.Cast(Of Invoice)().ToList()

            AddInvoiceSheetWithProgress(invoices)
        End Sub

        Private Sub ShowInvoiceSelectionMsgSheet()
            If _invoiceListBox.CheckedItems.Count = 0 Then
                _fpSpread.Sheets.Clear()
                _fpSpread.Sheets.Add(New SheetView())
                _fpSpread.Sheets(0).Cells(5, 1).Text = "Select an invoice and click Ok button to generate."
                _fpSpread.Sheets(0).Cells(5, 1).Font = New Font("Arial", 15, FontStyle.Bold)
                _fpSpread.Sheets(0).Cells(5, 1).VerticalAlignment = CellVerticalAlignment.Center
                _fpSpread.Sheets(0).Cells(5, 1).HorizontalAlignment = CellHorizontalAlignment.Center
                _fpSpread.Sheets(0).AddSpanCell(5, 1, 5, 10)
                _btnOK.Enabled = False
            Else
                _btnOK.Enabled = True
            End If
        End Sub

        Private Async Sub AddInvoiceSheetWithProgress(ByVal invoices As List(Of Invoice))
            Await Task.Run(Sub() AddInvoiceSheets(invoices))
        End Sub

        Private Sub AddInvoiceSheets(ByVal invoices As List(Of Invoice))
            Dim count As Integer = invoices.Count
            Dim sheetCnt As Integer = 0

            If invoices IsNot Nothing AndAlso count > 0 Then

                If _fpSpread.InvokeRequired Then
                    _fpSpread.Invoke(New MethodInvoker(Sub()
                                                           _fpSpread.Sheets.Count = 1
                                                       End Sub))
                Else
                    _fpSpread.Sheets.Count = 1
                End If

                For Each invoice As Invoice In invoices
                    AddInvoiceSheet(invoice, sheetCnt)

                    If sheetCnt < count - 1 Then

                        If _fpSpread.InvokeRequired Then
                            _fpSpread.Invoke(New MethodInvoker(Sub()
                                                                   _fpSpread.Sheets.Count += 1
                                                               End Sub))
                        Else
                            _fpSpread.Sheets.Count += 1
                        End If
                    End If

                    sheetCnt += 1
                Next
            End If
        End Sub

        Private Sub AddInvoiceSheet(ByVal invoice As Invoice, ByVal index As Integer)
            Dim invoiceSheet As SheetView = New SheetView With {
                .SheetName = $"{invoice.ID}"
            }

            For Each sheet As SheetView In _fpSpread.Sheets
                If sheet.SheetName = invoiceSheet.SheetName Then Return
            Next

            invoiceSheet.Rows(0).Height = 25
            Dim row As Integer = 1, col As Integer = 0
            _invoice = _dataService.JsonDataSource.GetInvoice(invoiceSheet.SheetName)
            invoiceSheet.ColumnCount = 10
            invoiceSheet.ColumnHeaderVisible = False
            invoiceSheet.RowHeaderVisible = False

            If _fpSpread.InvokeRequired Then
                _fpSpread.Invoke(New MethodInvoker(Sub()
                                                       _fpSpread.Font = _headingFontRegular
                                                   End Sub))
            Else
                _fpSpread.Font = _headingFontRegular
            End If

            invoiceSheet.VerticalGridLine = New GridLine(GridLineType.None)
            invoiceSheet.HorizontalGridLine = New GridLine(GridLineType.None)
            invoiceSheet.Cells(row, col, row, col + 9).ForeColor = Color.White
            invoiceSheet.Cells(row, col, row, col + 9).BackColor = Color.FromArgb(53, 55, 56)
            invoiceSheet.Cells(row, col, row, col + 9).BackColor = Color.FromArgb(109, 109, 109)
            invoiceSheet.Cells(row, col, row, col + 9).VerticalAlignment = CellVerticalAlignment.Center
            invoiceSheet.Rows(row).Height = 25
            invoiceSheet.Rows(row + 1).Height = 30
            invoiceSheet.Columns(col + 0).Width = 15
            invoiceSheet.Columns(col + 1).Width = 140
            invoiceSheet.Columns(col + 2, col + 3).Width = 130
            invoiceSheet.Columns(col + 4).Width = 140
            invoiceSheet.Columns(col + 5).Width = 80
            invoiceSheet.Columns(col + 6).Width = 90
            invoiceSheet.Columns(col + 7).Width = 25
            invoiceSheet.Columns(col + 8).Width = 70
            invoiceSheet.Columns(col + 9).Width = 15
            FillHeaderInfo(invoiceSheet, invoice, row)
            Dim customer As Customer = _dataService.JsonDataSource.GetCustomer(invoice.CustomerID)
            FillCustomerInfo(invoiceSheet, customer, row + 2)
            Dim vehicle As Vehicle = _dataService.JsonDataSource.GetVehicle(invoice.VehicleID)
            FillVehicleInfo(invoiceSheet, vehicle, row + 2)
            invoiceSheet.Rows(row + 7).Height = 40
            Dim invoiceServices As List(Of InvoiceServices) = _dataService.JsonDataSource.GetInvoiceServices(invoice.ID)
            row = FillServiceInfo(invoiceSheet, invoiceServices, row + 8)
            Dim parts_row_start As Integer = row + 2
            invoiceSheet.Rows(row).Height = 30
            Dim invoiceParts As List(Of InvoiceParts) = _dataService.JsonDataSource.GetInvoiceParts(invoice.ID)
            row = FillPartsInfo(invoiceSheet, invoiceParts, row + 1)
            invoiceSheet.AddSpanCell(row, 1, 1, 3)
            invoiceSheet.Cells(row, 1).Font = New Font("Segoe UI", 8.0F, FontStyle.Bold)
            invoiceSheet.Cells(row, 1).ForeColor = Color.Gray
            invoiceSheet.Cells(row, 1).VerticalAlignment = CellVerticalAlignment.Center
            invoiceSheet.Cells(row, 1).Text = "OTHER COMMENTS"
            invoiceSheet.Cells(row, 0, row, 3).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2))
            invoiceSheet.Cells(row + 1, 1, row + 5, 3).Font = New Font("Segoe UI", 8.0F, FontStyle.Regular)
            invoiceSheet.AddSpanCell(row + 1, 1, 1, 3)
            invoiceSheet.AddSpanCell(row + 2, 1, 1, 3)
            invoiceSheet.AddSpanCell(row + 3, 1, 1, 3)
            invoiceSheet.AddSpanCell(row + 5, 1, 1, 3)
            invoiceSheet.Cells(row + 1, 1).Text = "1. Total payment due in 30 days."
            invoiceSheet.Cells(row + 2, 1).Text = "2. Please include the invoice number on your check."
            invoiceSheet.Cells(row + 3, 1).Text = "3. Please mail your check to the address listed above."
            invoiceSheet.Rows(row + 5).Height = 25
            invoiceSheet.Cells(row + 5, 1).Font = New Font("Segoe UI", 10, FontStyle.Bold)
            invoiceSheet.Cells(row + 5, 1).ForeColor = _themeColor
            invoiceSheet.Cells(row + 5, 1).Text = "Thank You For Your Business!"
            invoiceSheet.Cells(row - 2, 1, row - 2, invoiceSheet.ColumnCount - 2).Border = _bottomBorder
            invoiceSheet.Cells(row - 1, 6, row + 5, 8).VerticalAlignment = CellVerticalAlignment.Center
            invoiceSheet.Cells(row - 1, 6, row + 5, 7).Font = _headingFontBold
            invoiceSheet.Cells(row - 1, 6, row + 5, 6).ForeColor = _themeColor
            invoiceSheet.Cells(row - 1, 6).Text = "SUBTOTAL"
            invoiceSheet.Cells(row - 1, 7).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Cells(row - 1, 7).Font = New Font(invoiceSheet.Cells(row - 1, 7).Font, FontStyle.Bold)
            invoiceSheet.Cells(row - 1, 7).Text = "$"
            invoiceSheet.Cells(row - 1, 8).CellType = _currencyCell
            invoiceSheet.Cells(row - 1, 8).Font = New Font("Segoe UI", 9, FontStyle.Bold)
            invoiceSheet.Cells(row - 1, 6, row - 1, 8).BackColor = Color.LightGray
            Dim formula As String = $"H{parts_row_start + 1}"

            For i As Integer = 2 To invoiceParts.Count
                formula += $" + H{parts_row_start + i}"
            Next

            invoiceSheet.AddCustomName("PartsSubTotal", $"${ChrW(Convert.ToByte(CChar("A")) + 8)}${row - 1 + 1}", 0, 0, True, String.Empty)
            invoiceSheet.Cells(row - 1, 8).Tag = "PartsSubTotal"
            invoiceSheet.Cells(row - 1, 8).Formula = formula
            invoiceSheet.Cells(row - 1, 8).Locked = True
            invoice.TotalPartsAmount = Convert.ToDecimal(invoiceSheet.Cells(row - 1, 8).Value)
            invoiceSheet.AddCustomName("PartsTaxRate", $"${ChrW(Convert.ToByte(CChar("A")) + 8)}${row + 1}", 0, 0, True, String.Empty)
            invoiceSheet.Cells(row, 7).Tag = "PartsTaxRate"
            invoiceSheet.Cells(row, 7).Value = invoice.TaxRateParts
            invoiceSheet.Cells(row, 6).Text = "TAX RATE"
            invoiceSheet.AddSpanCell(row, 7, 1, 2)
            invoiceSheet.Cells(row, 7).Font = New Font("Segoe UI", 8.0F, FontStyle.Regular)
            invoiceSheet.Cells(row, 7).CellType = _percentCell
            invoiceSheet.Cells(row + 2, 6).Text = "TOTAL SERVICES"
            invoiceSheet.Cells(row + 2, 7).Font = New Font(invoiceSheet.Cells(row + 2, 7).Font, FontStyle.Bold)
            invoiceSheet.Cells(row + 2, 7).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Cells(row + 2, 7).Text = "$"
            invoiceSheet.Cells(row + 2, 8, row + 5, 8).CellType = _currencyCell
            invoiceSheet.Cells(row + 2, 8).Formula = invoiceSheet.Cells("ServiceSubTotal").Formula
            invoiceSheet.Cells(row + 2, 8).Font = New Font("Segoe UI", 9, FontStyle.Bold)
            invoiceSheet.Cells(row + 2, 8).Locked = True
            invoiceSheet.Cells(row + 2, 8).Tag = "TotalServiceAmount"
            invoiceSheet.AddCustomName("TotalServiceAmount", $"${ChrW(Convert.ToByte(CChar("A")) + 8)}${row + 2 + 1}", 0, 0, True, String.Empty)
            invoiceSheet.Cells(row + 3, 6).Text = "TOTAL PARTS"
            invoiceSheet.Cells(row + 3, 7).Text = "$"
            invoiceSheet.Cells(row + 3, 7).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Cells(row + 3, 7).Font = New Font(invoiceSheet.Cells(row + 3, 7).Font, FontStyle.Bold)
            invoiceSheet.Cells(row + 3, 8).Formula = invoiceSheet.Cells("PartsSubTotal").Formula
            invoiceSheet.Cells(row + 3, 8).Font = New Font("Segoe UI", 9, FontStyle.Bold)
            invoiceSheet.Cells(row + 3, 8).Locked = True
            invoiceSheet.Cells(row + 3, 8).Tag = "TotalPartsAmount"
            invoiceSheet.AddCustomName("TotalPartsAmount", $"${ChrW(Convert.ToByte(CChar("A")) + 8)}${row + 3 + 1}", 0, 0, True, String.Empty)
            invoiceSheet.Cells(row + 4, 6).Text = "TOTAL TAX"
            invoiceSheet.Cells(row + 4, 7).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Cells(row + 4, 7).Font = New Font(invoiceSheet.Cells(row + 4, 7).Font, FontStyle.Bold)
            invoiceSheet.Cells(row + 4, 7).Text = "$"
            invoiceSheet.Cells(row + 4, 8).Formula = $"({invoiceSheet.SheetName}!TotalPartsAmount * {invoiceSheet.SheetName}!PartsTaxRate) + " & $"({invoiceSheet.SheetName}!TotalServiceAmount * {invoiceSheet.SheetName}!ServiceTaxRate)"
            invoiceSheet.Cells(row + 4, 8).Font = New Font("Segoe UI", 9, FontStyle.Bold)
            invoiceSheet.Cells(row + 4, 8).Locked = True
            invoiceSheet.Cells(row + 4, 8).Tag = "TotalTax"
            invoiceSheet.AddCustomName("TotalTax", $"{invoiceSheet.SheetName}!${ChrW((Convert.ToByte(CChar("A")) + 8))}${row + 4 + 1}", 0, 0, True, String.Empty)
            invoiceSheet.Cells(row + 4, 6, row + 4, 9).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(Color.FromArgb(176, 176, 176), 1))
            invoiceSheet.Cells(row + 5, 6, row + 5, 8).BackColor = Color.LightGray
            invoiceSheet.Cells(row + 5, 6, row + 5, 8).Font = New Font("Segoe UI", 10.0F, FontStyle.Bold)
            invoiceSheet.Cells(row + 5, 6).Text = "TOTAL"
            invoiceSheet.Cells(row + 5, 8).Locked = True
            invoiceSheet.Cells(row + 5, 8).Formula = $"{invoiceSheet.SheetName}!TotalPartsAmount + {invoiceSheet.SheetName}!TotalServiceAmount + " & $"{invoiceSheet.SheetName}!TotalTax"
            invoiceSheet.Cells(row + 5, 8).Formula = $"{invoiceSheet.SheetName}!$I${invoiceSheet.Cells("TotalPartsAmount").Row.Index + 1} + " & $"{invoiceSheet.SheetName}!$I${invoiceSheet.Cells("TotalServiceAmount").Row.Index + 1} + " & $"{invoiceSheet.SheetName}!$I${invoiceSheet.Cells("TotalTax").Row.Index + 1}"
            invoiceSheet.Cells(row + 5, 8).Tag = "TotalAmount"
            invoice.TotalAmount = Convert.ToDecimal(invoiceSheet.Cells("TotalAmount").Value)
            invoiceSheet.Cells(row + 5, 6, row + 5, 9).Border = New ComplexBorder(Nothing, New ComplexBorderSide(Color.FromArgb(176, 176, 176), 1), Nothing, Nothing)
            invoiceSheet.Cells(row + 5, 7).Value = _currencyCell.CurrencySymbol
            invoiceSheet.Cells(row + 5, 7).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Rows(row + 6).Height = 25
            invoiceSheet.AddSpanCell(row + 7, 6, 1, 3)
            invoiceSheet.Cells(row + 7, 6).Font = New Font("Segoe UI", 8.0F, FontStyle.Regular)
            invoiceSheet.Cells(row + 7, 6).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Cells(row + 7, 6).Text = "Make all checks payable to:"
            invoiceSheet.AddSpanCell(row + 8, 6, 1, 3)
            invoiceSheet.Cells(row + 8, 6).Font = New Font("Segoe UI", 8.0F, FontStyle.Bold)
            invoiceSheet.Cells(row + 8, 6).ForeColor = _themeColor
            invoiceSheet.Cells(row + 8, 6).HorizontalAlignment = CellHorizontalAlignment.Center
            invoiceSheet.Cells(row + 8, 6).Text = "Your Company Name"
            invoiceSheet.Rows(row + 9).Height = 25
            invoiceSheet.Rows.Remove(row + 10, invoiceSheet.Rows.Count - row - 10)
            invoiceSheet.Columns.Add(0, 1)
            invoiceSheet.Columns(0).Width = 25
            invoiceSheet.Columns.Add(invoiceSheet.ColumnCount, 1)
            invoiceSheet.Columns(invoiceSheet.ColumnCount - 1).Width = 25
            invoiceSheet.Cells(1, 1, row + 8, 1).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 2), Nothing, Nothing, Nothing)
            invoiceSheet.Cells(1, invoiceSheet.ColumnCount - 2, row + 8, invoiceSheet.ColumnCount - 2).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2), Nothing)
            invoiceSheet.Cells(row + 9, 2, row + 9, invoiceSheet.ColumnCount - 3).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2))
            invoiceSheet.Cells(row + 9, invoiceSheet.ColumnCount - 2).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2), New ComplexBorderSide(Color.Gray, 2))
            invoiceSheet.Cells(row + 9, 1).Border = New ComplexBorder(New ComplexBorderSide(Color.Gray, 2), Nothing, Nothing, New ComplexBorderSide(Color.Gray, 2))

            If _fpSpread.InvokeRequired Then
                _fpSpread.Invoke(New MethodInvoker(Sub()
                                                       _fpSpread.Sheets(index) = invoiceSheet
                                                   End Sub))
            Else
                _fpSpread.Sheets(index) = invoiceSheet
            End If

            UpdateDataSource(invoice)
        End Sub

        Private Sub UpdateDataSource(ByVal invoice As Invoice)
            _dataService.JsonDataSource.UpdateInvoice(invoice)
        End Sub

        Private Sub FillHeaderInfo(ByVal sheet As SheetView, ByVal invoice As Invoice, ByVal start_row As Integer)
            sheet.Cells(start_row + 0, 1).Text = "INVOICE #"
            sheet.Cells(start_row + 0, 1).HorizontalAlignment = CellHorizontalAlignment.Right
            sheet.Cells(start_row + 0, 1).Locked = True
            sheet.Cells(start_row + 0, 2).TextIndent = 20
            sheet.Cells(start_row + 0, 2).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Cells(start_row + 0, 2).Text = invoice.ID
            sheet.Cells(start_row + 0, 6).Text = "DATE"
            sheet.Cells(start_row + 0, 6).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Cells(start_row + 0, 6).TextIndent = 20
            sheet.Cells(start_row + 0, 7).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Cells(start_row + 0, 7).CellType = _dateTimeCell
            sheet.Cells(start_row + 0, 7).Locked = True
            sheet.AddSpanCell(start_row + 0, 7, 1, 2)
            Dim currDate As DateTime = DateTime.Now
            Dim random As Random = New Random(currDate.Millisecond)
            Dim createdOn As DateTime = currDate.AddDays(-random.[Next](0, 5))
            sheet.Cells(start_row + 0, 7).Value = createdOn
            invoice.CreatedOn = createdOn
        End Sub

        Private Sub FillCustomerInfo(ByVal sheet As SheetView, ByVal customer As Customer, ByVal start_row As Integer)
            Dim imageCell As ImageCellType = New ImageCellType With {
                .Style = RenderStyle.StretchAndScale
            }
            sheet.AddSpanCell(start_row, 1, 5, 1)
            sheet.Cells(start_row, 1).CellType = imageCell
            Dim width As Integer = CInt(sheet.Columns(1).Width)
            Dim height As Integer = CInt((sheet.Rows(start_row).Height * 5))
            sheet.Cells(start_row, 1).Value = Image.FromFile("Images/" & customer.ID & ".png")
            sheet.AddSpanCell(start_row, 2, 1, 2)
            sheet.Cells(start_row, 2).TextIndent = 15
            sheet.AddSpanCell(start_row + 1, 2, 1, 2)
            sheet.Cells(start_row + 1, 2).TextIndent = 15
            sheet.AddSpanCell(start_row + 2, 2, 1, 2)
            sheet.Cells(start_row + 2, 2).TextIndent = 15
            sheet.AddSpanCell(start_row + 3, 2, 1, 2)
            sheet.Cells(start_row + 3, 2).TextIndent = 15
            sheet.Cells(start_row, 2).Text = customer.Name
            sheet.Cells(start_row + 1, 2).Text = customer.StreetAddress
            sheet.Cells(start_row + 2, 2).Text = $"{customer.City}, {customer.State} {customer.Zipcode}"
            Dim maskCell As MaskCellType = New MaskCellType With {
                .Mask = "\Phone: (###) ###-####"
            }
            sheet.Cells(start_row + 3, 2).CellType = maskCell
            sheet.Cells(start_row + 3, 2).Value = customer.Phone
        End Sub

        Private Sub FillVehicleInfo(ByVal sheet As SheetView, ByVal vehicle As Vehicle, ByVal start_row As Integer)
            Dim imageCell As ImageCellType = New ImageCellType With {
                .Style = RenderStyle.StretchAndScale
            }
            sheet.AddSpanCell(start_row, 4, 5, 1)
            sheet.Cells(start_row, 4).CellType = imageCell
            Dim random As Random = New Random()
            sheet.Cells(start_row, 4).Value = Image.FromFile("Images/" & vehicle.Color & ".png")
            sheet.Cells(start_row, 5).Text = "Year:"
            sheet.Cells(start_row + 1, 5).Text = "Make:"
            sheet.Cells(start_row + 2, 5).Text = "Model:"
            sheet.Cells(start_row + 3, 5).Text = "Color:"
            sheet.Cells(start_row + 4, 5).Text = "Mileage:"
            sheet.Cells(start_row, 5, start_row + 4, 5).TextIndent = 15
            sheet.Cells(start_row, 6).Text = vehicle.Year.ToString()
            sheet.Cells(start_row + 1, 6).Text = vehicle.Make
            sheet.Cells(start_row + 2, 6).Text = vehicle.Model
            sheet.Cells(start_row + 3, 6).Text = vehicle.Color
            sheet.Cells(start_row + 4, 6).Text = vehicle.Mileage.ToString()
            sheet.Cells(start_row, 6).HorizontalAlignment = CellHorizontalAlignment.Left
            sheet.Cells(start_row + 4, 6).HorizontalAlignment = CellHorizontalAlignment.Left
        End Sub

        Private Function FillServiceInfo(ByVal sheet As SheetView, ByVal invoiceServices As List(Of InvoiceServices), ByVal start_row As Integer) As Integer
            sheet.AddSpanCell(start_row, 1, 1, 6)
            sheet.Cells(start_row, 1, start_row, sheet.ColumnCount - 2).Border = _bottomBorder
            sheet.Cells(start_row, 1, start_row, 8).ForeColor = _themeColor
            sheet.Cells(start_row, 1, start_row, 8).Font = _headingFontBold
            sheet.Cells(start_row, 1).Text = "SERVICES PERFORMED"
            sheet.Cells(start_row, 1).VerticalAlignment = CellVerticalAlignment.Center
            sheet.Cells(start_row, 8).Text = "AMOUNT"
            sheet.Cells(start_row, 8).HorizontalAlignment = CellHorizontalAlignment.Right
            sheet.Cells(start_row, 8).VerticalAlignment = CellVerticalAlignment.Center
            Dim i As Integer = 1

            While i <= invoiceServices.Count
                sheet.AddSpanCell(start_row + i, 1, 1, 6)
                sheet.Cells(start_row + i, 7).CellType = _decimalCell
                sheet.Cells(start_row + i, 1).Text = invoiceServices(i - 1).Description
                sheet.Cells(start_row + i, 1).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(_borderColor, 2), New ComplexBorderSide(_borderColor, 2))
                sheet.AddSpanCell(start_row + i, 7, 1, 2)
                sheet.Cells(start_row + i, 7).Value = invoiceServices(i - 1).ServiceAmount
                sheet.Cells(start_row + i, 7).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_borderColor, 2))
                i += 1
            End While

            For j As Integer = start_row + i To start_row + i + 2 - 1
                sheet.AddSpanCell(j, 1, 1, 6)
                sheet.Rows(j).Locked = True
                sheet.Cells(j, 1).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(_borderColor, 2), New ComplexBorderSide(_borderColor, 2))
                sheet.AddSpanCell(j, 7, 1, 2)
                sheet.Cells(j, 7).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_borderColor, 2))
            Next

            sheet.AddSpanCell(start_row + i + 2, 1, 1, 8)
            sheet.Cells(start_row + i + 2, 1).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_themeColor, 2))
            sheet.Rows(start_row + i + 2).Locked = True
            sheet.Cells(start_row + i + 2, 1, start_row + i + 2, sheet.ColumnCount - 2).Border = _bottomBorder
            sheet.Cells(start_row + i + 3, 6, start_row + i + 4, 6).Font = _headingFontBold
            sheet.Cells(start_row + i + 3, 6, start_row + i + 4, 6).ForeColor = _themeColor
            sheet.Cells(start_row + i + 3, 6).Text = "SUBTOTAL"
            sheet.Cells(start_row + i + 4, 6).Text = "TAX RATE"
            sheet.Cells(start_row + i + 3, 7, start_row + i + 3, 8).Font = _headingFontBold
            sheet.Cells(start_row + i + 3, 7).Value = _currencyCell.CurrencySymbol
            sheet.Cells(start_row + i + 3, 7).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(start_row + i + 3, 8).CellType = _currencyCell
            sheet.Cells(start_row + i + 3, 6, start_row + i + 3, 8).BackColor = Color.LightGray
            Dim formula As String = $"H{start_row + 2}"

            For j As Integer = 2 To invoiceServices.Count
                formula += $" + H{start_row + 1 + j}"
            Next

            sheet.Cells(start_row + i + 3, 8).Tag = "ServiceSubTotal"
            sheet.Cells(start_row + i + 3, 8).Font = New Font("Segoe UI", 9, FontStyle.Bold)
            sheet.Cells(start_row + i + 3, 8).Formula = formula
            sheet.Cells(start_row + i + 3, 8).Locked = True
            sheet.AddCustomName("ServiceSubTotal", $"${ChrW(Convert.ToByte(CChar("A")) + 8)}${start_row + i + 3 + 1}", 0, 0, True, String.Empty)
            _invoice.TotalServiceAmount = Convert.ToDecimal(sheet.Cells(start_row + i + 3, 8).Value)
            sheet.AddSpanCell(start_row + i + 4, 7, 1, 2)
            sheet.Cells(start_row + i + 4, 7).Tag = "ServiceTaxRate"
            sheet.Cells(start_row + i + 4, 7).Value = _invoice.TaxRateServices
            sheet.AddCustomName("ServiceTaxRate", $"${ChrW(Convert.ToByte(CChar("A")) + 8)}${start_row + i + 4 + 1}", 0, 0, True, String.Empty)
            sheet.Cells(start_row + i + 4, 7).CellType = _percentCell
            Return start_row + i + 6
        End Function

        Private Function FillPartsInfo(ByVal sheet As SheetView, ByVal invoiceParts As List(Of InvoiceParts), ByVal start_row As Integer) As Integer
            sheet.Rows(start_row).Font = _headingFontBold
            sheet.Rows(start_row).ForeColor = _themeColor
            sheet.Cells(start_row, 1, start_row, sheet.ColumnCount - 2).Border = _bottomBorder
            sheet.AddSpanCell(start_row, 1, 1, 3)
            sheet.Cells(start_row, 1).Text = "PART NAME"
            sheet.Cells(start_row, 4).Text = "PART #"
            sheet.Cells(start_row, 5).Text = "QTY"
            sheet.Cells(start_row, 6).Text = "UNIT PRICE"
            sheet.Cells(start_row, 4, start_row, 6).HorizontalAlignment = CellHorizontalAlignment.Center
            sheet.Cells(start_row, 8).HorizontalAlignment = CellHorizontalAlignment.Right
            sheet.Cells(start_row, 8).Text = "AMOUNT"
            Dim i As Integer = 1

            While i <= invoiceParts.Count
                sheet.AddSpanCell(start_row + i, 1, 1, 3)
                sheet.Cells(start_row + i, 6).CellType = _numCell
                sheet.Cells(start_row + i, 7).CellType = _currencyCell
                sheet.Cells(start_row + i, 4, start_row + i, 6).HorizontalAlignment = CellHorizontalAlignment.Center
                Dim part As Parts = _dataService.JsonDataSource.GetAllParts().FirstOrDefault(Function(p) p.ID = invoiceParts(i - 1).PartsID)
                sheet.Cells(start_row + i, 1).Text = part.Name
                sheet.Cells(start_row + i, 4).Value = part.ID
                sheet.Cells(start_row + i, 5).CellType = _numCell
                sheet.Cells(start_row + i, 5).Value = invoiceParts(i - 1).Quantity
                sheet.Cells(start_row + i, 6).Value = part.UnitPrice
                sheet.Cells(start_row + i, 1, start_row + i, 6).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(_borderColor, 2), New ComplexBorderSide(_borderColor, 2))
                sheet.Cells(start_row + i, 0).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_borderColor, 2))
                sheet.Cells(start_row + i, 7, start_row + i, 9).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_borderColor, 2))
                sheet.AddSpanCell(start_row + i, 7, 1, 2)
                sheet.Cells(start_row + i, 7).Formula = $"F{start_row + i + 1} * G{start_row + i + 1}"
                sheet.Cells(start_row + i, 7).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_borderColor, 2))
                sheet.Cells(start_row + i, 7).Locked = True
                i += 1
            End While

            For j As Integer = 0 To 2 - 1
                sheet.AddSpanCell(start_row + i + j, 1, 1, 3)
                sheet.Cells(start_row + i + j, 1).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(_borderColor, 2), New ComplexBorderSide(_borderColor, 2))
                sheet.Cells(start_row + i + j, 3, start_row + i + j, 6).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(_borderColor, 2), New ComplexBorderSide(_borderColor, 2))
                sheet.Rows(start_row + i + j).Locked = True
                sheet.AddSpanCell(start_row + i + j, 7, 1, 2)
                sheet.Cells(start_row + i + j, 7, start_row + i + j, 9).Border = New ComplexBorder(Nothing, Nothing, Nothing, New ComplexBorderSide(_borderColor, 2))
                sheet.Cells(start_row + i + j, 8).Text = "-"
                sheet.Cells(start_row + i + j, 8).TextIndent = 90
            Next

            sheet.AddSpanCell(start_row + i + 2, 1, 1, 3)
            sheet.AddSpanCell(start_row + i + 2, 7, 1, 2)
            sheet.Cells(start_row + i + 2, 8).Text = "-"
            sheet.Cells(start_row + i + 2, 8).TextIndent = 90
            sheet.Cells(start_row + i + 2, 3, start_row + i + 2, 6).Border = New ComplexBorder(Nothing, Nothing, New ComplexBorderSide(_borderColor, 2), New ComplexBorderSide(_themeColor, 2))
            sheet.Rows(start_row + i + 2).Locked = True
            Return start_row + i + 4
        End Function

        Private Sub FpSpread_ActiveSheetChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim name As String = String.Empty
            If _fpSpread.ActiveSheet Is Nothing Then Return
            _fpSpread.BeginInvoke(New MethodInvoker(Sub()
                                                        name = _fpSpread.ActiveSheet.SheetName
                                                    End Sub))
            _invoice = _dataService.JsonDataSource.GetInvoice(name)
        End Sub

        Private Sub BtnOK_Click(ByVal sender As Object, ByVal e As EventArgs)
            _fpSpread.SuspendLayout()
            GenerateInvoice()
            _fpSpread.ResumeLayout(True)
        End Sub

        Private Sub ComboCustomer_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            While _invoiceListBox.CheckedItems.Count > 0
                _invoiceListBox.SetItemCheckState(_invoiceListBox.Items.IndexOf(_invoiceListBox.CheckedItems(0)), CheckState.Unchecked)
            End While

            _fpSpread.SuspendLayout()
            Dim id As Integer = CInt(_comboCustomer.SelectedValue)
            Dim invoices As List(Of Invoice) = _dataService.JsonDataSource.GetInvoiceByCustomerID(id)
            _invoiceListBox.DataSource = invoices
            _invoiceListBox.DisplayMember = "ID"
            _invoiceListBox.ValueMember = "ID"
            _invoiceListBox.SetItemCheckState(0, CheckState.Checked)
            GenerateInvoice()
            _fpSpread.ResumeLayout(True)
        End Sub

        Private Sub ComboCustomer_Format(ByVal sender As Object, ByVal e As ListControlConvertEventArgs)
            Dim customer As Customer = TryCast(e.ListItem, Customer)
            Dim value As String

            If customer.ID = -1 Then
                value = customer.Name
            Else
                value = $"ID: {customer.ID}, {customer.Name}"
            End If

            e.Value = value
        End Sub
    End Class
End Namespace

'
'
'

Imports System.Drawing

Namespace GrapeCity.InvoiceSample
    Partial Class InvoiceControl
        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()
            Dim flatScrollBarRenderer3 As FarPoint.Win.Spread.FlatScrollBarRenderer = New FarPoint.Win.Spread.FlatScrollBarRenderer()
            Dim flatScrollBarRenderer4 As FarPoint.Win.Spread.FlatScrollBarRenderer = New FarPoint.Win.Spread.FlatScrollBarRenderer()
            Me._fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None)
            Me._fpSpread_Sheet1 = New FarPoint.Win.Spread.SheetView()
            Me._labelCustomer = New System.Windows.Forms.Label()
            Me._comboCustomer = New System.Windows.Forms.ComboBox()
            Me._labelInvoice = New System.Windows.Forms.Label()
            Me._invoiceListBox = New System.Windows.Forms.CheckedListBox()
            Me._btnOK = New System.Windows.Forms.Button()
            Me.pnlMain.SuspendLayout()
            Me.pnlControls.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.pnlControls.Controls.Add(Me._btnOK)
            Me.pnlControls.Controls.Add(Me._invoiceListBox)
            Me.pnlControls.Controls.Add(Me._labelInvoice)
            Me.pnlControls.Controls.Add(Me._comboCustomer)
            Me.pnlControls.Controls.Add(Me._labelCustomer)
            Me._fpSpread.AccessibleDescription = ""
            Me._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
            Me._fpSpread.HorizontalScrollBar.Buttons = New FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton")
            Me._fpSpread.HorizontalScrollBar.Name = ""
            flatScrollBarRenderer3.BackColor = System.Drawing.Color.FromArgb((CInt(((CByte((255)))))), (CInt(((CByte((255)))))), (CInt(((CByte((255)))))))
            Me._fpSpread.HorizontalScrollBar.Renderer = flatScrollBarRenderer3
            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.Skin = FarPoint.Win.Spread.DefaultSpreadSkins.Office2016White
            Me._fpSpread.StatusBarVisible = True
            Me._fpSpread.TabIndex = 0
            Me._fpSpread.TabStripPolicy = FarPoint.Win.Spread.TabStripPolicy.Always
            Me._fpSpread.VerticalScrollBar.Buttons = New FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton")
            Me._fpSpread.VerticalScrollBar.Name = ""
            flatScrollBarRenderer4.BackColor = System.Drawing.Color.FromArgb((CInt(((CByte((255)))))), (CInt(((CByte((255)))))), (CInt(((CByte((255)))))))
            Me._fpSpread.VerticalScrollBar.Renderer = flatScrollBarRenderer4
            Me._fpSpread_Sheet1.Reset()
            Me._fpSpread_Sheet1.SheetName = "Sheet1"
            Me._fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
            Me._fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.ColumnFooter.DefaultStyle.Parent = "ColumnFooterFlatOffice2016White"
            Me._fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.ColumnFooterSheetCornerStyle.Parent = "CornerFooterFlatOffice2016White"
            Me._fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.ColumnHeader.DefaultStyle.Parent = "ColumnHeaderFlatOffice2016White"
            Me._fpSpread_Sheet1.FilterBar.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.FilterBar.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.FilterBar.DefaultStyle.Parent = "FilterBarFlatOffice2016White"
            Me._fpSpread_Sheet1.FilterBarHeaderStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.FilterBarHeaderStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.FilterBarHeaderStyle.Parent = "FilterBarHeaderFlatOffice2016White"
            Me._fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.RowHeader.DefaultStyle.Parent = "RowHeaderFlatOffice2016White"
            Me._fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
            Me._fpSpread_Sheet1.SheetCornerStyle.Parent = "CornerHeaderFlatOffice2016White"
            Me._fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
            Me._labelCustomer.AutoSize = True
            Me._labelCustomer.Location = New System.Drawing.Point(6, 19)
            Me._labelCustomer.Name = "_labelCustomer"
            Me._labelCustomer.Size = New System.Drawing.Size(87, 13)
            Me._labelCustomer.TabIndex = 0
            Me._labelCustomer.Text = "Select Customer:"
            Me._comboCustomer.Anchor = (CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right)), System.Windows.Forms.AnchorStyles))
            Me._comboCustomer.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
            Me._comboCustomer.FormattingEnabled = True
            Me._comboCustomer.Location = New System.Drawing.Point(9, 36)
            Me._comboCustomer.Name = "_comboCustomer"
            Me._comboCustomer.Size = New System.Drawing.Size(133, 21)
            Me._comboCustomer.TabIndex = 1
            Me._labelInvoice.AutoSize = True
            Me._labelInvoice.Location = New System.Drawing.Point(9, 71)
            Me._labelInvoice.Name = "_labelInvoice"
            Me._labelInvoice.Size = New System.Drawing.Size(78, 13)
            Me._labelInvoice.TabIndex = 2
            Me._labelInvoice.Text = "Select Invoice:"
            Me._invoiceListBox.Anchor = (CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right)), System.Windows.Forms.AnchorStyles))
            Me._invoiceListBox.FormattingEnabled = True
            Me._invoiceListBox.Location = New System.Drawing.Point(9, 95)
            Me._invoiceListBox.Name = "_invoiceListBox"
            Me._invoiceListBox.Size = New System.Drawing.Size(133, 319)
            Me._invoiceListBox.TabIndex = 3
            Me._btnOK.Anchor = (CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right)), System.Windows.Forms.AnchorStyles))
            Me._btnOK.Location = New System.Drawing.Point(33, 426)
            Me._btnOK.Name = "_btnOK"
            Me._btnOK.Size = New System.Drawing.Size(85, 27)
            Me._btnOK.TabIndex = 4
            Me._btnOK.Text = "OK"
            Me._btnOK.UseVisualStyleBackColor = True
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0F, 13.0F)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "InvoiceControl"
            Me.pnlMain.ResumeLayout(False)
            Me.pnlControls.ResumeLayout(False)
            Me.pnlControls.PerformLayout()
            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
        Private _labelInvoice As System.Windows.Forms.Label
        Private _comboCustomer As System.Windows.Forms.ComboBox
        Private _labelCustomer As System.Windows.Forms.Label
        Private _btnOK As System.Windows.Forms.Button
        Private _invoiceListBox As System.Windows.Forms.CheckedListBox
    End Class
End Namespace
