
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Xml
Imports System.Text
Imports System.Windows.Forms
Imports System.IO
Imports C1.Win.FlexPivot
Imports C1.FlexPivot
Imports C1.DataEngine

Namespace QuickStart
    Partial Public Class Form1
        Inherits Form
        Private updating As Boolean
        Private sel As RadioButton
        Private conn As SqlConnection
        Private addTime As DateTime = DateTime.MinValue
        Private updateTime As DateTime = DateTime.MinValue
        Private dataPath As String = Path.Combine(System.Windows.Forms.Application.StartupPath, "Data")
        Private stream As MemoryStream
        Private initialView As String


        Public Sub New()
            ' designer support
            InitializeComponent()

            ' handle standard data table controls
            For Each ctl As Control In groupBox1.Controls
                If TypeOf ctl Is RadioButton Then
                    AddHandler ctl.Click, AddressOf DataTable_Click
                End If
            Next

            ' handle DataEngine table controls
            For Each ctl As Control In groupBox2.Controls
                If TypeOf ctl Is RadioButton Then
                    AddHandler ctl.Click, AddressOf EngineTable_Click
                End If
            Next

            ' connect to sample data
            conn = New SqlConnection(GetConnectionString())
            conn.Open()

            ' where DataEngine data is stored
            C1.DataEngine.Workspace.Init(dataPath)

            ' show update log
            AddHandler _c1FlexPivotPage.FlexPivotEngine.StartUpdating, AddressOf FlexPivotEngine_StartUpdating
            AddHandler _c1FlexPivotPage.Updated, AddressOf _c1FlexPivotPage_Updated

            ' load predefined FlexPivot views
            Dim doc = New XmlDocument()
            doc.LoadXml(My.Resources.FlexPivotViews)

            ' build menu with FlexPivot views
            Dim menuView = New C1.Win.C1Command.C1CommandMenu()
            menuView.Text = "&View"
            menuView.Image = My.Resources.Views_small
            For Each nd As XmlNode In doc.SelectNodes("FlexPivotViews/C1FlexPivot")
                If initialView Is Nothing Then
                    initialView = nd.OuterXml
                End If
                Dim cmd = New C1.Win.C1Command.C1Command()
                cmd.Text = nd.Attributes("id").Value
                cmd.UserData = nd
                AddHandler cmd.Click, AddressOf MenuView_DropDownItemClicked
                Dim link = New C1.Win.C1Command.C1CommandLink(cmd)
                menuView.CommandLinks.Add(link)
            Next

            ' add the new view menu to the toolstrip
            Dim menuLink = New C1.Win.C1Command.C1CommandLink(menuView)
            menuLink.ButtonLook = C1.Win.C1Command.ButtonLookFlags.TextAndImage
            _c1FlexPivotPage.ToolBar.CommandLinks.Insert(3, menuLink)
        End Sub

        Private Sub DataTable_Click(sender As Object, e As EventArgs)
            If updating Then
                Return
            End If

            ' add data
            Dim count As Integer = StartAddingRows(sender)
            Dim dt = GetDataTable()
            AddToDataTable(dt, count)
            EndAddingRows(count)

            ' set data
            _c1FlexPivotPage.FlexPivotPanel.FlexPivotEngine.BeginUpdate()
            _c1FlexPivotPage.DataSource = dt
            OnSetData()
            _c1FlexPivotPage.FlexPivotPanel.FlexPivotEngine.EndUpdate()
        End Sub

        Private Sub EngineTable_Click(sender As Object, e As EventArgs)
            If updating Then
                Return
            End If

            ' add data
            Dim count As Integer = StartAddingRows(sender)
            Dim tableName As String = AddToEngineTable(GetDataTable(), count)
            EndAddingRows(count)

            ' set data
            _c1FlexPivotPage.FlexPivotPanel.FlexPivotEngine.BeginUpdate()
            _c1FlexPivotPage.FlexPivotPanel.ConnectDataEngine(tableName)
            OnSetData()
            _c1FlexPivotPage.FlexPivotPanel.FlexPivotEngine.EndUpdate()
        End Sub

        Private Sub OnSetData()
            Dim fPanel = _c1FlexPivotPage.FlexPivotPanel
            If Not (initialView Is Nothing) Then
                fPanel.ViewDefinition = initialView
            Else
                ' restore the view
                If initialView Is Nothing Then
                    Using xr As System.Xml.XmlReader = System.Xml.XmlReader.Create(stream)
                        _c1FlexPivotPage.ReadXml(xr)
                    End Using
                End If
                stream.Dispose()
                stream = Nothing
            End If
            initialView = Nothing

            FillLookup("Country", My.Resources.SqlLookupCountry)
            FillLookup("Product", My.Resources.SqlLookupProduct)
            FillLookup("Customer", My.Resources.SqlLookupCustomer)
            FillLookup("Employee", My.Resources.SqlLookupEmployee)
            FillLookup("Category", My.Resources.SqlLookupCategory)
        End Sub

        Private Sub FillLookup(fieldName As String, lookupSql As String)
            Dim field As C1FlexPivotField = _c1FlexPivotPage.FlexPivotPanel.FlexPivotEngine.Fields(fieldName)
            If field.Lookup Is Nothing Then
                field.Lookup = GetLookup(conn, lookupSql)
            End If
        End Sub

        ' start log item
        Private Sub FlexPivotEngine_StartUpdating(sender As Object, e As EventArgs)
            If updateTime <> DateTime.MinValue Then
                Return
            End If
            updateTime = DateTime.Now
            listBox1.Items.Add(String.Format("Updating {0}...", GetDataType()))
            listBox1.TopIndex = listBox1.Items.Count - 1
        End Sub

        ' complete log item with calculated operation time
        Private Sub _c1FlexPivotPage_Updated(sender As Object, e As EventArgs)
            If updateTime = DateTime.MinValue Then
                Return
            End If

            Dim time As String = DateTime.Now.Subtract(updateTime).TotalSeconds.ToString("n2")

            ' update label
            Dim label As Control = sel.Parent.GetNextControl(sel, True)
            label.Text = String.Format("{0} sec.", time)

            ' update log
            listBox1.Items.RemoveAt(listBox1.Items.Count - 1)
            listBox1.Items.Add(String.Format("Updated {0}: {1} sec.", GetDataType(), time))
            listBox1.TopIndex = listBox1.Items.Count - 1
            updateTime = DateTime.MinValue
            updating = False
        End Sub

        Private Function GetDataTable() As DataTable
            Dim da = New SqlDataAdapter(My.Resources.SqlStatement, conn)
            Dim dt = New DataTable("NorthWind Sales Data")
            da.Fill(dt)
            Return dt
        End Function

        Private Function GetDataType() As String
            If _c1FlexPivotPage.DataSource IsNot Nothing Then
                Return _c1FlexPivotPage.DataSource.[GetType]().FullName
            Else
                Return "C1.DataEngine.Table"
            End If
        End Function

        ' select a predefined FlexPivot view
        Private Sub MenuView_DropDownItemClicked(sender As Object, e As C1.Win.C1Command.ClickEventArgs)
            If updating OrElse sel Is Nothing Then
                Return
            End If

            Dim nd = TryCast(e.CallerLink.Command.UserData, XmlNode)
            If nd IsNot Nothing Then
                _c1FlexPivotPage.FlexPivotPanel.ViewDefinition = nd.OuterXml
            End If
        End Sub

        ' create a dictionary from two columns data
        Private Function GetLookup(conn As SqlConnection, command As String) As Dictionary(Of Object, String)
            Dim dict = New Dictionary(Of Object, String)()
            Dim cmd As New SqlCommand(command, conn)
            Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
            While reader.Read()
                dict.Add(reader.GetValue(0), reader.GetString(1))
            End While
            reader.Close()
            Return dict
        End Function

        Private Function StartAddingRows(sender As Object) As Integer
            updating = True
            ' uncheck other radio buttons
            For Each ctl As Control In groupBox1.Controls
                If TypeOf ctl Is RadioButton AndAlso Not ctl.Equals(sender) Then
                    DirectCast(ctl, RadioButton).Checked = False
                End If
            Next
            For Each ctl As Control In groupBox2.Controls
                If TypeOf ctl Is RadioButton AndAlso Not ctl.Equals(sender) Then
                    DirectCast(ctl, RadioButton).Checked = False
                End If
            Next

            ' save current view
            stream = New MemoryStream()
            Using xw As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(stream)
                _c1FlexPivotPage.FlexPivotEngine.WriteXml(xw)
                xw.Flush()
                stream.Position = 0
            End Using

            ' check selected button
            sel = DirectCast(sender, RadioButton)
            sel.Checked = True

            ' start log
            Dim count As Integer = Integer.Parse(sel.Tag.ToString())
            addTime = DateTime.Now
            listBox1.Items.Add(String.Format("Adding {0} rows...", count.ToString("n0")))
            listBox1.TopIndex = listBox1.Items.Count - 1
            Application.DoEvents()
            Return count
        End Function

        Private Sub EndAddingRows(count As Integer)
            ' end adding log
            listBox1.Items.RemoveAt(listBox1.Items.Count - 1)
            listBox1.Items.Add(String.Format("Added {0} rows: {1} sec.", count.ToString("n0"), DateTime.Now.Subtract(addTime).TotalSeconds.ToString("n2")))
            listBox1.TopIndex = listBox1.Items.Count - 1
        End Sub

        ' generate new rows in DataTable
        Private Sub AddToDataTable(table As DataTable, count As Integer)
            ' remove column protection
            For Each col As DataColumn In table.Columns
                col.[ReadOnly] = False
            Next

            ' read data from first rows
            Dim c As Integer = table.Rows.Count
            Dim max As Integer = Math.Min(c, 5000)
            Dim rd As Object() = New Object(max - 1) {}
            Dim n As Integer
            For n = 0 To max - 1
                rd(n) = table.Rows(n).ItemArray
            Next

            ' add data to the end of the table
            Dim i As Integer = 0
            While i < count
                Dim k As Integer = 0
                While k < max AndAlso (k + i) < count
                    Dim r As DataRow = table.NewRow()
                    r.ItemArray = DirectCast(rd(k), Object())
                    table.Rows.Add(r)
                    k += 1
                End While
                i += max
            End While
        End Sub

        ' generate new rows in the C1 DataEngine table
        Private Function AddToEngineTable(table As DataTable, count As Integer) As String
            AddToDataTable(table, count)
            Dim tableName As String = Guid.NewGuid().ToString()
            C1.DataEngine.DbConnector.GetData(table, tableName)
            Return tableName
        End Function

        ' remove data generated by the C1 DataEngine
        Protected Overrides Sub OnFormClosed(e As FormClosedEventArgs)
            Directory.Delete(dataPath + "\metadata.xml", False)
            Try
                conn.Close()
                Directory.Delete(dataPath, True)
            Catch
            End Try
            MyBase.OnFormClosed(e)
        End Sub

        ' get sample DB connection string
        Private Shared Function GetConnectionString() As String
            Dim key As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Installed Versions\12.0")
            Dim ver As String = "MSSQLLocalDB"
            If key Is Nothing Then
                ver = "v11.0"
            End If
            Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "\ComponentOne Samples\Common"
            Return String.Format("Data Source=(LocalDB)\{0};AttachDbFilename={1}\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30", ver, path)
        End Function

    End Class
End Namespace
