Imports Spread.Common.DataStore
Imports FarPoint.Win.Spread
Imports System.Data
Imports FarPoint.Win.Spread.Model
Imports System.Collections.Generic
Imports FarPoint.Win.Spread.CellType
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Partial Public Class GroupAggregation
        Inherits BaseSampleControl
        Private _dataSource As AccessDataSource

        Public Sub New()
            InitializeComponent()
            Name = "Aggregation and Group Footers"

            _dataSource = New DataService().AccessDataSource

            InitSpreadSheet(fpSpread.ActiveSheet)
            InitControls()

            AddHandler fpSpread.Grouped, AddressOf FpSpread_Grouped
            AddHandler fpSpread.Ungrouped, AddressOf FpSpread_Ungrouped
        End Sub

#Region "Private methods"
        Private Sub InitSpreadSheet(sheet As SheetView)
            sheet.DataSource = _dataSource.GetDataFrom("fpWine.mdb", "select OrderID, CustomerID, SalesPerson, " & "OrderDate, ShippedDate, Freight from Orders Where CustomerID <> -1 " & "order by CustomerID ASC, SalesPerson ASC").Tables(0)
            sheet.GroupBarInfo.Visible = True
            sheet.AllowGroup = True
            fpSpread.AllowColumnMove = True
            sheet.GroupFooterVisible = True

            Dim customerTable As DataTable = _dataSource.GetDataFrom("fpWine.mdb", "select ID, CompanyName from Customers").Tables(0)
            Dim ids As New List(Of String)()
            Dim names As New List(Of String)()
            For Each dataRow As DataRow In customerTable.Rows
                ids.Add(dataRow(0).ToString())
                names.Add(dataRow(1).ToString())
            Next

            Dim comboCell As New ComboBoxCellType()
            comboCell.ItemData = ids.ToArray()
            comboCell.Items = names.ToArray()
            comboCell.EditorValue = EditorValue.ItemData

            sheet.Columns(1).CellType = comboCell

            For col As Integer = 0 To sheet.ColumnCount - 1
                sheet.Columns(col).Width = sheet.GetPreferredColumnWidth(col, PreferredSizeColumnOptions.IncludeAll)
            Next

            sheet.SetMultipleRowHeights(0, sheet.RowCount, 20)
        End Sub

        Private Sub InitControls()
            comboAggregationType.DataSource = [Enum].GetValues(GetType(AggregationType))
            comboAggregationType.SelectedItem = AggregationType.Sum
            AddHandler comboAggregationType.SelectedIndexChanged, AddressOf ComboAggregationType_SelectedIndexChanged

            AddHandler chkShowFooter.CheckedChanged, AddressOf ChkShowFooter_CheckedChanged
        End Sub

        Private Sub UpdateFooterAggregation()
            Dim groupDataModel As GroupDataModel = DirectCast(fpSpread.ActiveSheet.Models.Data, GroupDataModel)
            groupDataModel.GroupFooterVisible = chkShowFooter.Checked
            For i As Integer = 0 To groupDataModel.Groups.Count - 1
                Dim g1 As Group = DirectCast(groupDataModel.Groups(i), Group)
                DirectCast(g1.GroupFooter.DataModel, IAggregationSupport).SetCellAggregationType(0, 5, CType(comboAggregationType.SelectedItem, AggregationType))
                DirectCast(g1.GroupFooter.DataModel, IAggregationSupport).SetCellAggregationFormat(0, 5, "{comboAggregationType.SelectedItem}" & ": {0:F2}")
                fpSpread.ActiveSheet.Models.Data = groupDataModel
            Next
            fpSpread.ActiveSheet.Columns(5).Width = fpSpread.ActiveSheet.Columns(5).GetPreferredWidth()
        End Sub
#End Region

#Region "Event handlers"
        Private Sub FpSpread_Ungrouped(sender As Object, e As EventArgs)
            groupBox1.Enabled = False
        End Sub

        Private Sub FpSpread_Grouped(sender As Object, e As EventArgs)
            groupBox1.Enabled = True
            UpdateFooterAggregation()
        End Sub

        Private Sub ComboAggregationType_SelectedIndexChanged(sender As Object, e As EventArgs)
            UpdateFooterAggregation()
        End Sub

        Private Sub ChkShowFooter_CheckedChanged(sender As Object, e As EventArgs)
            comboAggregationType.Enabled = chkShowFooter.Checked
            TryCast(fpSpread.ActiveSheet.Models.Data, GroupDataModel).GroupFooterVisible = chkShowFooter.Checked
            fpSpread.ActiveSheet.Columns(5).Width = fpSpread.ActiveSheet.Columns(5).GetPreferredWidth()
        End Sub
#End Region
    End Class
End Namespace

'
'GroupAggregation.Designer.vb
'

Namespace Features
	Partial Class GroupAggregation
		''' <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.fpSpread = New FarPoint.Win.Spread.FpSpread()
			Me.fpSpread_Sheet1 = New FarPoint.Win.Spread.SheetView()
			Me.groupBox1 = New System.Windows.Forms.GroupBox()
			Me.comboAggregationType = New System.Windows.Forms.ComboBox()
			Me.label1 = New System.Windows.Forms.Label()
			Me.chkShowFooter = New System.Windows.Forms.CheckBox()
			Me.pnlMain.SuspendLayout()
			Me.pnlControls.SuspendLayout()
			DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
			DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
			Me.groupBox1.SuspendLayout()
			Me.SuspendLayout()
			' 
			' pnlMain
			' 
			Me.pnlMain.Controls.Add(Me.fpSpread)
			' 
			' pnlControls
			' 
			Me.pnlControls.Controls.Add(Me.groupBox1)
			' 
			' fpSpread
			' 
			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.Skin = FarPoint.Win.Spread.DefaultSpreadSkins.Office2016White
			Me.fpSpread.StatusBarVisible = True
			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.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
			' 
			' groupBox1
			' 
			Me.groupBox1.Controls.Add(Me.comboAggregationType)
			Me.groupBox1.Controls.Add(Me.label1)
			Me.groupBox1.Controls.Add(Me.chkShowFooter)
			Me.groupBox1.Dock = System.Windows.Forms.DockStyle.Top
			Me.groupBox1.Enabled = False
			Me.groupBox1.Location = New System.Drawing.Point(0, 0)
			Me.groupBox1.Name = "groupBox1"
			Me.groupBox1.Size = New System.Drawing.Size(157, 136)
			Me.groupBox1.TabIndex = 0
			Me.groupBox1.TabStop = False
			Me.groupBox1.Text = "Footer Settings"
			' 
			' comboAggregationType
			' 
			Me.comboAggregationType.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
			Me.comboAggregationType.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
			Me.comboAggregationType.FormattingEnabled = True
			Me.comboAggregationType.Location = New System.Drawing.Point(7, 65)
			Me.comboAggregationType.Name = "comboAggregationType"
			Me.comboAggregationType.Size = New System.Drawing.Size(121, 21)
			Me.comboAggregationType.TabIndex = 2
			' 
			' label1
			' 
			Me.label1.AutoSize = True
			Me.label1.Location = New System.Drawing.Point(7, 47)
			Me.label1.Name = "label1"
			Me.label1.Size = New System.Drawing.Size(94, 13)
			Me.label1.TabIndex = 1
			Me.label1.Text = "Aggregation Type:"
			' 
			' chkShowFooter
			' 
			Me.chkShowFooter.AutoSize = True
			Me.chkShowFooter.Checked = True
			Me.chkShowFooter.CheckState = System.Windows.Forms.CheckState.Checked
			Me.chkShowFooter.Location = New System.Drawing.Point(7, 20)
			Me.chkShowFooter.Name = "chkShowFooter"
			Me.chkShowFooter.Size = New System.Drawing.Size(118, 17)
			Me.chkShowFooter.TabIndex = 0
			Me.chkShowFooter.Text = "Show Group Footer"
			Me.chkShowFooter.UseVisualStyleBackColor = True
			' 
			' GroupAggregation
			' 
			Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.Name = "GroupAggregation"
			Me.pnlMain.ResumeLayout(False)
			Me.pnlControls.ResumeLayout(False)
			DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
			DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
			Me.groupBox1.ResumeLayout(False)
			Me.groupBox1.PerformLayout()
			Me.ResumeLayout(False)

		End Sub

		#End Region

		Private fpSpread As FarPoint.Win.Spread.FpSpread
		Private fpSpread_Sheet1 As FarPoint.Win.Spread.SheetView
		Private groupBox1 As System.Windows.Forms.GroupBox
		Private comboAggregationType As System.Windows.Forms.ComboBox
		Private label1 As System.Windows.Forms.Label
		Private chkShowFooter As System.Windows.Forms.CheckBox
	End Class
End Namespace
