Imports System.Collections.Generic
Imports System.Drawing
Imports System.Linq
Imports System.Reflection
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.DataSourceEntities
Imports Spread.Common.DataStore.Models

Public Partial Class IncomeStatementControl
    Inherits Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl
#Region "Private variables"
    Private _businessStats As List(Of BusinessStat)
	Private _currentYearStats As List(Of BusinessStat)
	Private _dataSource As IncomeStatementDataSource
	#End Region

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

		_dataSource = New DataService().IncomeStatementDataSource
		_businessStats = _dataSource.GetAllBusinessStats()

		AddHandler Load, AddressOf IncomeStatementControl_Load
	End Sub
	#End Region

	#Region "Event handlers"
	Private Sub IncomeStatementControl_Load(sender As Object, e As System.EventArgs)
		fpSpread.ActiveSheet.OpenExcel("Template\IncomeStatement.xlsx", 0)

		SetSheetProperties(fpSpread.ActiveSheet)
		LoadBusinessStats(fpSpread.ActiveSheet, 4, DateTime.Now.Year)
	End Sub

	Private Sub ComboCell_EditingStopped(sender As Object, e As EventArgs)
		Dim year As Integer = Convert.ToInt32(fpSpread.ActiveSheet.Cells("YearCell").Value)
		ClearContents(fpSpread.ActiveSheet, 9, 4, 11, 15)
		ClearContents(fpSpread.ActiveSheet, 14, 4, 15, 15)
		ClearContents(fpSpread.ActiveSheet, 20, 4, 25, 15)
		ClearContents(fpSpread.ActiveSheet, 30, 4, 32, 15)
		LoadBusinessStats(fpSpread.ActiveSheet, 4, year)
	End Sub
	#End Region

	#Region "Private methods"
	Private Sub SetSheetProperties(sheet As SheetView)
		fpSpread.SuspendLayout()
		sheet.RowCount = 38
		sheet.ColumnCount = 16
		sheet.SheetName = "Income Statement"

		sheet.FpSpread.ButtonDrawMode = ButtonDrawModes.Always
		sheet.Cells(9, 1).Tag = "Product"
		sheet.Cells(10, 1).Tag = "Services"
		sheet.Cells(11, 1).Tag = "Other"
		sheet.Cells(14, 1).Tag = "CostOfGoods"
		sheet.Cells(15, 1).Tag = "DirectCharges"
		sheet.Cells(20, 1).Tag = "PayrollCost"
		sheet.Cells(21, 1).Tag = "AdminstrativeCost"
		sheet.Cells(22, 1).Tag = "TravelCost"
		sheet.Cells(23, 1).Tag = "MarketingCost"
		sheet.Cells(24, 1).Tag = "OutsourcingCost"
		sheet.Cells(25, 1).Tag = "UtilityCost"
		sheet.Cells(30, 1).Tag = "InterestExpense"
		sheet.Cells(31, 1).Tag = "InterestIncome"
		sheet.Cells(32, 1).Tag = "DepreciationCost"

		sheet.Protect = False
		sheet.Cells(8, 2, sheet.RowCount - 1, sheet.ColumnCount - 1).Locked = False

		AddComboSelector(sheet, 1, 3)

		fpSpread.ResumeLayout(True)
	End Sub

	' Add ComboBox cell for Year selector
	Private Sub AddComboSelector(sheet As SheetView, row As Integer, col As Integer)
        Dim comboCell As New ComboBoxCellType() With {
             .Editable = False,
             .DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
             .Items = New String() {(DateTime.Now.Year - 2).ToString(), (DateTime.Now.Year - 1).ToString(), (DateTime.Now.Year.ToString())},
             .StopEditingAfterDropDownItemSelected = True
        }
        AddHandler comboCell.EditingStopped, AddressOf ComboCell_EditingStopped

		sheet.AddSpanCell(row, col, 1, 2)
		sheet.Cells(row, col).HorizontalAlignment = CellHorizontalAlignment.Center
		sheet.Cells(row, col).VerticalAlignment = CellVerticalAlignment.Center
		sheet.Cells(row, col).Font = New Font("Segoe UI", 18, FontStyle.Regular)
		sheet.Cells(row, col).CellType = comboCell
		sheet.Cells(row, col).Value = DateTime.Now.Year.ToString()
		sheet.Cells(row, col).Tag = "YearCell"
		sheet.Cells(row, col).Locked = False
	End Sub

	' Load stats of selected year
	Private Sub LoadBusinessStats(sheet As SheetView, col As Integer, year As Integer)
		Dim [date] As DateTime = DateTime.Now.[Date]
		_currentYearStats = _businessStats.Where(Function(stat) stat.Year = year).ToList()
		If _currentYearStats.First().Year = [date].Year Then
			_currentYearStats = _currentYearStats.Where(Function(stat) stat.Month <= [date].Month).ToList()
		End If

		Dim revenueProperties As PropertyInfo() = _businessStats(0).Revenue.[GetType]().GetProperties()
		Dim expenseProperties As PropertyInfo() = _businessStats(0).Expense.[GetType]().GetProperties()
		Dim revenuePropertyNames As List(Of String) = revenueProperties.[Select](Function(prop) prop.Name).ToList()
		Dim expensePropertyNames As List(Of String) = expenseProperties.[Select](Function(prop) prop.Name).ToList()

		' Suspend layout to prevent update of layout after change in each cell
		sheet.AutoCalculation = False
		fpSpread.SuspendLayout()

        For Each stat As Object In _currentYearStats
            For i As Integer = 0 To revenueProperties.Count() - 1
                sheet.Cells(sheet.Cells(revenuePropertyNames(i)).Row.Index, col + stat.Month - 1).Value = revenueProperties(i).GetValue(stat.Revenue)
            Next

            For i As Integer = 0 To expenseProperties.Count() - 1
                If expensePropertyNames(i) = "DepreciationCost" OrElse expensePropertyNames(i) = "AmortizationCost" Then
                    Continue For
                End If
                sheet.Cells(sheet.Cells(expensePropertyNames(i)).Row.Index, col + stat.Month - 1).Value = expenseProperties(i).GetValue(stat.Expense)
            Next

            sheet.Cells(sheet.Cells("DepreciationCost").Row.Index, col + stat.Month - 1).Value = stat.Expense.DepreciationCost + stat.Expense.AmortizationCost
        Next

        ' Resume layout after all values are set
        sheet.AutoCalculation = True
		fpSpread.ResumeLayout(True)
	End Sub

	Private Sub ClearContents(sheet As SheetView, row As Integer, col As Integer, endRow As Integer, endCol As Integer)
		sheet.ClearRange(row, col, endRow - row + 1, endCol - col + 1, True)
	End Sub
	#End Region
End Class

'
'
'

Partial Class IncomeStatementControl
	''' <summary> 
	''' Required designer variable.
	''' </summary>
	Private components As System.ComponentModel.IContainer = Nothing

    ''' <summary> 
    ''' Clean up any resources being used.
    ''' </summary>
    ''' <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    Protected Sub Dispose(disposing As Boolean)
        If disposing AndAlso (components IsNot Nothing) Then
            components.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub

#Region "Component Designer generated code"

    ''' <summary> 
    ''' Required method for Designer support - do not modify 
    ''' the contents of this method with the code editor.
    ''' </summary>
    Private Sub InitializeComponent()
		Me.fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None)
		Me.fpSpread_Sheet1 = New FarPoint.Win.Spread.SheetView()
		Me.pnlMain.SuspendLayout()
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Controls.Add(Me.fpSpread)
		' 
		' fpSpread
		' 
		Me.fpSpread.AccessibleDescription = ""
		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
		' 
		' fpSpread_Sheet1
		' 
		Me.fpSpread_Sheet1.Reset()
		Me.fpSpread_Sheet1.SheetName = "Sheet1"
		' Formulas and custom names must be loaded with R1C1 reference style
		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.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' IncomeStatementControl
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "IncomeStatementControl"
		Me.pnlMain.ResumeLayout(False)
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)

	End Sub

	#End Region

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