Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Windows.Forms
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports Spread.Common.DataStore
Imports Spread.Common.DataStore.DataSourceEntities
Imports Spread.Common.DataStore.Model

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

        Private _dataSource As COGSDataSource
        Private _costOfSalesCategories As List(Of String)
        Private _revenueCategories As List(Of String)
        Private _expenses As List(Of String)
        Private _date As DateTime

        Public Sub New()
            InitializeComponent()
            _dataSource = New DataService().COGSDataSource
            _costOfSalesCategories = New List(Of String)()
            _revenueCategories = New List(Of String)()

            For i As Integer = 1 To 7
                _costOfSalesCategories.Add($"Category {i}")
                _revenueCategories.Add($"Category {i}")
            Next

            _expenses = New List(Of String)() From {
                "Salary Expenses",
                "Payroll Expenses",
                "Outside services",
                "Supplies (office and operating)",
                "Repairs and maintainance",
                "Advertising",
                "Car, delivery and travel",
                "Accounting and legal",
                "Rent",
                "Telephone",
                "Utilities",
                "Insurance",
                "Taxes (real estate, etc.)",
                "Interest",
                "Depreciation",
                "Other expenses (specify)",
                "Other expenses (specify)",
                "Other expenses (specify)",
                "Misc. (unspecified)"
            }
            AddHandler Load, AddressOf Form1_Load
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
            fpSpread1.SuspendLayout()
            FpSpread1.OpenExcel("Template\COGS.xlsx")
            FpSpread1.ActiveSheet.Cells(1, 17).Tag = "FiscalDate"
            _date = New DateTime(DateTime.Now.Year - 1, 12, 31)
            FpSpread1.ActiveSheet.Cells("FiscalDate").Text = String.Format("{0:d}", _date)
            FpSpread1.ActiveSheet.Cells("FiscalDate").Locked = False
            SetupSheet(FpSpread1.ActiveSheet)
            LoadStats(FpSpread1.ActiveSheet)
            FpSpread1.ResumeLayout(True)
            AddHandler fpSpread1.Change, AddressOf FpSpread1_Change
            AddHandler fpSpread1.EditChange, AddressOf FpSpread1_EditChange
        End Sub

        Private Sub FpSpread1_EditChange(ByVal sender As Object, ByVal e As EditorNotifyEventArgs)
            If FpSpread1.ActiveSheet.Cells(e.Row, e.Column).Tag?.ToString() = "FiscalDate" Then
                FpSpread1.ActiveSheet.AutoCalculation = False
            End If
        End Sub

        Private Sub FpSpread1_Change(ByVal sender As Object, ByVal e As ChangeEventArgs)
            Dim sheet As SheetView = fpSpread1.ActiveSheet

            If sheet.Cells(e.Row, e.Column).Tag?.ToString() = "FiscalDate" Then
                Dim date1 As DateTime = DateTime.MaxValue

                If DateTime.TryParse(sheet.Cells("FiscalDate").Text, date1) AndAlso date1.Date < DateTime.Now.Date Then
                    fpSpread1.SuspendLayout()
                    ClearCells(sheet)
                    _date = date1
                    LoadStats(sheet)
                    fpSpread1.ResumeLayout(True)
                Else
                    MessageBox.Show("Date must be valid and less than current date.", "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                    AddHandler fpSpread1.Change, AddressOf FpSpread1_Change
                    sheet.Cells("FiscalDate").Value = _date
                    AddHandler fpSpread1.Change, AddressOf FpSpread1_Change
                End If

                sheet.AutoCalculation = True
            End If
        End Sub

        Private Sub SetupSheet(ByVal sheet As SheetView)
            For i As Integer = 1 To 7
                sheet.Rows(4 + i).Tag = $"Revenue_Category{i}"
                sheet.Rows(14 + i).Tag = $"CostOfSales_Category{i}"
            Next

            sheet.Rows(27).Tag = "SalaryExpenses"
            sheet.Rows(28).Tag = "PayrollExpenses"
            sheet.Rows(29).Tag = "OutsideServices"
            sheet.Rows(30).Tag = "Supplies"
            sheet.Rows(31).Tag = "Repairs"
            sheet.Rows(32).Tag = "Advertising"
            sheet.Rows(33).Tag = "CarDelivery"
            sheet.Rows(34).Tag = "Accounting"
            sheet.Rows(35).Tag = "Rent"
            sheet.Rows(36).Tag = "Telephone"
            sheet.Rows(37).Tag = "Utilities"
            sheet.Rows(38).Tag = "Insurance"
            sheet.Rows(39).Tag = "Taxes"
            sheet.Rows(40).Tag = "Interest"
            sheet.Rows(41).Tag = "Depreciation"
            sheet.Rows(42).Tag = "OtherExpenses1"
            sheet.Rows(43).Tag = "OtherExpenses2"
            sheet.Rows(44).Tag = "OtherExpenses3"
            sheet.Rows(45).Tag = "Miscellaneous"
            SetCellTypes(sheet)
            SetLabels(sheet)
        End Sub

        Private Sub SetLabels(ByVal sheet As SheetView)
            For i As Integer = 0 To 7 - 1
                sheet.Cells(5 + i, 1).Value = _revenueCategories(i)
                sheet.Cells(15 + i, 1).Value = _costOfSalesCategories(i)
            Next

            For i As Integer = 0 To _expenses.Count - 1
                sheet.Cells(27 + i, 1).Value = _expenses(i)
            Next
        End Sub

        Private Sub SetCellTypes(ByVal sheet As SheetView)
            FpSpread1.SuspendLayout()
            Dim currencyCell As CurrencyCellType = New CurrencyCellType()
            currencyCell.DecimalPlaces = 0
            Dim col As Integer = 3

            For i As Integer = 0 To 13 - 1
                sheet.Cells(5, col, 12, col).CellType = currencyCell
                sheet.Cells(15, col, 22, col).CellType = currencyCell
                sheet.Cells(24, col).CellType = currencyCell
                sheet.Cells(27, col, 46, col).CellType = currencyCell
                sheet.Cells(48, col).CellType = currencyCell
                col += 2
            Next

            FpSpread1.ResumeLayout(True)
        End Sub

        Private Sub ClearCells(ByVal sheet As SheetView)
            Dim col As Integer = 3

            For i As Integer = 0 To 12 - 1
                sheet.Cells(5, col, 11, col).ResetValue()
                sheet.Cells(15, col, 21, col).ResetValue()
                sheet.Cells(27, col, 45, col).ResetValue()
                col += 2
            Next
        End Sub

        Private Sub LoadStats(ByVal sheet As SheetView)
            For i As Integer = 0 To 12 - 1
                Dim row As Integer = 5, col As Integer = 3 + 2 * i

                Try
                    Dim date1 As DateTime
                    Dim val As Object = sheet.Cells(row - 2, col).Value

                    If TypeOf val Is Double Then
                        date1 = DateTime.FromOADate(CDbl(val))
                    ElseIf TypeOf val Is DateTime Then
                        date1 = CDate(val)
                    Else
                        date1 = Convert.ToDateTime(sheet.Cells(row - 2, col).Text)
                    End If

                    If date1.Date > DateTime.Now.Date Then Return
                    Dim stats As CostOfGoodsStats = _dataSource.GetCOGSStats(date1, 7, 7)

                    For Each categorySales As Integer In stats.CategoryRevenues
                        sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = categorySales
                    Next

                    row += 3

                    For Each costOfSales As Integer In stats.CategoryCosts
                        sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = costOfSales
                    Next

                    Dim properties = stats.Expenses.[GetType]().GetProperties(System.Reflection.BindingFlags.[Public] Or System.Reflection.BindingFlags.Instance).ToList()
                    Dim [property] As Object
                    For Each [property] In properties
                        If [property].Name = "Others" Then Continue For
                        sheet.Cells(sheet.Rows([property].Name).Index, col).Value = [property].GetValue(stats.Expenses)
                    Next

                    row = 42

                    For Each otherExpense As Integer In stats.Expenses.Others
                        sheet.Cells(Math.Min(System.Threading.Interlocked.Increment(row), row - 1), col).Value = otherExpense
                    Next

                Catch ex As InvalidCastException
                    System.Diagnostics.Debug.WriteLine(ex)
                    Continue For
                End Try
            Next
        End Sub
    End Class
End Namespace

'
'
'

Namespace GrapeCity.CostOfGoodsSold
    Partial Class CostOfGoodsControl
        ''' <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(ByVal disposing As Boolean)
            Try
                If disposing AndAlso components IsNot Nothing Then
                    components.Dispose()
                End If
            Finally
                MyBase.Dispose(disposing)
            End Try
        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.fpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None)
            Me.fpSpread1_Sheet1 = New FarPoint.Win.Spread.SheetView()
            Me.pnlMain.SuspendLayout()
            DirectCast(Me.fpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
            DirectCast(Me.fpSpread1_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            ' 
            ' pnlMain
            ' 
            Me.pnlMain.Controls.Add(Me.fpSpread1)
            ' 
            ' fpSpread
            ' 
            Me.fpSpread1.AccessibleDescription = ""
            Me.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill
            Me.fpSpread1.Location = New System.Drawing.Point(0, 0)
            Me.fpSpread1.Name = "fpSpread1"
            Me.fpSpread1.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me.fpSpread1_Sheet1})
            Me.fpSpread1.Size = New System.Drawing.Size(477, 487)
            Me.fpSpread1.TabIndex = 0
            ' 
            ' fpSpread1_Sheet1
            ' 
            Me.fpSpread1_Sheet1.Reset()
            Me.fpSpread1_Sheet1.SheetName = "Sheet1"
            ' Formulas and custom names must be loaded with R1C1 reference style
            Me.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
            Me.fpSpread1_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
            Me.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
            ' 
            ' CostOfGoodsControl
            ' 
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0F, 13.0F)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "CostOfGoodsControl"
            Me.pnlMain.ResumeLayout(False)
            DirectCast(Me.fpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
            DirectCast(Me.fpSpread1_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub

#End Region

        Private fpSpread1 As FarPoint.Win.Spread.FpSpread
        Private fpSpread1_Sheet1 As FarPoint.Win.Spread.SheetView
    End Class
End Namespace
