Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports FarPoint.Win.Spread

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

Partial Public Class AgingReport
    Inherits Spread.Common.Features.Spread.Common.Features.Sample.BaseSampleControl


#Region "Fields"
    Private fpSpread As FpSpread
    Private _agingDate As DateTime
    Private _dataSource As JsonDataSource
    Private _customers As List(Of Customer)
    Private _invoices As List(Of Invoice)
#End Region

#Region "ctor"
    Public Sub New()
        InitializeComponent()
        SetupSpread()
        AddHandler Me.Load, AddressOf AgingReport_Load
    End Sub
#End Region
#Region "Event Handlers"
    Private Sub AgingReport_Load(sender As Object, e As EventArgs)
        _agingDate = DateTime.Now.[Date]
        _dataSource = New DataService().JsonDataSource
        _customers = _dataSource.GetAgingReportCustomers()
        Dim random As Random = New Random()
        Dim rangeDate As DateTime = New DateTime(2016, random.Next(8, 12), random.Next(1, 28))
        _invoices = _dataSource.GetAgingReportInvoices(rangeDate, rangeDate.AddYears(1)).Take(100).ToList()
        _invoices.ForEach(Sub(invoice) invoice.CreatedOn = invoice.CreatedOn.AddYears(5))

        fpSpread.OpenExcel("Template\AgingReport.xlsx")
        LoadInvoices(fpSpread.Sheets("Invoices"))
        SetCustomerNames(fpSpread.Sheets("Aging Report"), 8, 2)

        fpSpread.Sheets(0).Cells(4, 8).Value = New DateTime(2022, 11, 15)
        fpSpread.Sheets(1).RowCount = 110
        fpSpread.Sheets(0).ColumnCount = 15
        fpSpread.Sheets(1).ColumnCount = 15
        fpSpread.Sheets(0).Cells(7, 2, 7, 8).Locked = False
    End Sub
#End Region
#Region "Private methods"
    Private Sub LoadInvoices(sheet As SheetView)
        Dim row As Integer = 1, col As Integer = 0
        Dim query As Object = _invoices.Join(_customers, Function(inv) inv.CustomerID, Function(cust) cust.ID, Function(inv, cust) New With {
            Key .InvoiceID = inv.ID,
            Key .CustomerName = cust.Name,
            Key .InvoiceDate = inv.CreatedOn,
            Key .InvoiceAmount = inv.TotalAmount
        })

        For Each data As Object In CType(query, IEnumerable(Of Object))
            sheet.Cells(row, col).Text = CType(data.CustomerName, String)
            sheet.Cells(row, col + 1).Value = data.InvoiceID
            sheet.Cells(row, col + 2).Value = data.InvoiceDate
            sheet.Cells(row, col + 4).Value = data.InvoiceAmount
            row += 1
        Next

        ' Set Aging Date
        sheet.Cells(0, 12).Value = DateTime.Now.[Date]
        sheet.Columns(11).Width = sheet.Columns(11).GetPreferredWidth()
        sheet.Columns(12).Width = sheet.Columns(12).GetPreferredWidth()
    End Sub

    Private Sub SetCustomerNames(sheet As SheetView, row As Integer, col As Integer)
        For Each customer As Customer In _customers
            sheet.Cells(row, col).Text = customer.Name
            row += 1
        Next
    End Sub
    Private Sub SetupSpread()
        fpSpread = New FpSpread(LegacyBehaviors.None) With {
            .Dock = DockStyle.Fill
        }
        Me.pnlMain.Controls.Add(fpSpread)
    End Sub
#End Region
End Class

'
' AgingReport.Designer.vb
'

Partial Class AgingReport
	''' <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 Shadows 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.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Size = New System.Drawing.Size(878, 593)
		' 
		' pnlControls
		' 
		Me.pnlControls.Size = New System.Drawing.Size(218, 593)
		' 
		' AgingReport
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "AgingReport"
		Me.Size = New System.Drawing.Size(878, 593)
		Me.ResumeLayout(False)

	End Sub

	#End Region
End Class