Imports System.IO
Imports System.Text.RegularExpressions
Imports System.Data.Odbc
Imports LFSO83Lib


Public Class FieldSync

    Private Sub MenuFileExit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MenuFileExit.Click
        Me.Close()
    End Sub

    Private Sub ConfigButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ConfigButton.Click
        ConfigFileDialog.ShowDialog()
        TextBoxConfig.Text = ConfigFileDialog.FileName.ToString()
    End Sub

    Private Sub MenuFileOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MenuFileOpen.Click
        ConfigFileDialog.ShowDialog()
        TextBoxConfig.Text = ConfigFileDialog.FileName.ToString()
    End Sub

    Private Sub MenuFileNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MenuFileNew.Click
        NewConfig.ShowDialog()

    End Sub

    Private Sub Sync_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Sync.Click
        Dim ConfigFile As String
        Dim dsn As String
        Dim dsnuser As String
        Dim dsnpass As String
        Dim table As String
        Dim column As String
        Dim server As String
        Dim repository As String
        Dim user As String
        Dim pass As String
        Dim template As String
        Dim field As String
        Dim lfdsn As String
        Dim lfdsnuser As String
        Dim lfdsnpass As String


        Dim oRead As StreamReader
        oRead = File.OpenText(TextBoxConfig.Text)
        ConfigFile = oRead.ReadToEnd()

        Dim RegexObj As Regex
        RegexObj = New Regex(".*\nDSN: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        dsn = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n*.*\n*User: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        dsnuser = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n*.*\n*.*\n*Pass: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        dsnpass = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*Table: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        table = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*Column: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        column = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*Server: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        server = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*Repository: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        repository = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*User: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        user = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*Pass: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        pass = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*Template: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*")
        template = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*Field: (.+);\n*.*\n*.*\n*.*\n*.*\n*.*")
        field = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*DSN: (.+);\n*.*\n*.*\n*.*\n*.*")
        lfdsn = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*DSN User: (.+);\n*.*\n*.*\n*.*")
        lfdsnuser = RegexObj.Replace(ConfigFile, "$1")
        RegexObj = New Regex(".*\n.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*.*\n*DSN Pass: (.+);\n*.*\n*.*")
        lfdsnpass = RegexObj.Replace(ConfigFile, "$1")

        oRead.Close()

        Dim conn As OdbcConnection
        Dim sConnString As String = _
          "DSN=" & dsn & ";uid=" & dsnuser & ";pwd=" & dsnpass
        conn = New OdbcConnection(sConnString)
        conn.Open()
        Dim CmdString As String = "SELECT " & column & " FROM " & table & " ORDER BY " & column & " ASC"
        Dim Cmd As New OdbcCommand(CmdString, conn)
        Dim Reader As OdbcDataReader


        Dim lfconn As OdbcConnection
        Dim lfconnstring As String = "DSN=" & lfdsn & ";uid=" & lfdsnuser & ";pwd=" & lfdsnpass
        lfconn = New OdbcConnection(lfconnstring)
        lfconn.Open()
        Dim lfcmdstring As String = "SELECT * FROM propdef WHERE prop_name = '" & field & "'"
        Dim lfcmd As New OdbcCommand(lfcmdstring, lfconn)
        Dim lfreader As OdbcDataReader

        'Get the field ID
        lfreader = lfcmd.ExecuteReader()
        lfreader.Read()
        Dim FieldID As String = lfreader.GetValue(0)
        lfreader.Close()

        'Clear the list
        lfcmdstring = "DELETE FROM lup WHERE(prop_id = " & FieldID & ")"
        lfcmd = New OdbcCommand(lfcmdstring, lfconn)
        lfcmd.ExecuteNonQuery()

        'Set progress bar
        Reader = Cmd.ExecuteReader()
        Dim i As Integer = 0
        While Reader.Read()
            i = i + 1
        End While
        ProgressBar1.Minimum = 0
        ProgressBar1.Maximum = i
        ProgressBar1.Step = i + 1
        Reader.Close()

        'Insert new list data
        Reader = Cmd.ExecuteReader()

        i = 0
        While Reader.Read()
            If Not IsDBNull(Reader.GetValue(0)) Then
                Dim readervalue As String
                readervalue = Reader.GetValue(0)
                readervalue = readervalue.Replace("'", "")
                lfcmdstring = "INSERT INTO lup(prop_id, list_pos, list_val) VALUES(" & FieldID & ", " & i & ", '" & readervalue & "')"
                lfcmd = New OdbcCommand(lfcmdstring, lfconn)
                lfcmd.ExecuteNonQuery()
                i = i + 1
                ProgressBar1.Value = i
            End If
        End While
        MessageBox.Show(i & " fields added.", "Sync Successful!")
        Reader.Close()

        If ProgressBar1.Maximum = ProgressBar1.Value Then
            ProgressBar1.Value = 0
        End If

        conn.Close()
        lfconn.Close()

    End Sub

    Private Sub MenuOptionsView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MenuOptionsView.Click
        Process.Start("notepad.exe", TextBoxConfig.Text)
    End Sub
End Class