Imports C1.Win.C1TrueDBGrid
Imports System.IO
Imports System.IO.Stream
Imports System.Drawing
Imports System.Xml
Imports ColorCode


Public Class frmData
    Dim OnLoad As Boolean = True
    Dim data As New DB
    Dim dbco As New DB_Connection
    Private Sub frmData_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.ComboBox1.SelectedIndex = 0
        Me.ComboBox2.SelectedIndex = 0
        Me.ComboBox3.SelectedIndex = 0
        Me.ComboBox4.SelectedIndex = 0
        Me.ComboBox5.SelectedIndex = 0
        Me.ComboBox6.SelectedIndex = 0
        OnLoad = False
        Get_Data(1)
    End Sub

    Private Sub RadioButton1_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton1.CheckedChanged
        If OnLoad Then Exit Sub
        If Me.RadioButton1.Checked = True Then
            Get_Data(1)
            Me.gpEDKLog.Enabled = True
            Me.gpEDKData.Enabled = False
        Else
            Me.gpEDKLog.Enabled = False
            Me.gpEDKData.Enabled = True
        End If
    End Sub
    Private Sub RadioButton2_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton2.CheckedChanged
        If OnLoad Then Exit Sub
        If Me.RadioButton2.Checked = True Then
            Get_Data(2)
            Me.gpEDKLog.Enabled = False
            Me.gpEDKData.Enabled = True
        Else
            Me.gpEDKLog.Enabled = True
            Me.gpEDKData.Enabled = False

        End If
    End Sub

    Sub Get_Data(ByVal Typ As Integer)
        Me.C1Daten.Columns.Clear()
        data.dsDaten.Tables.Clear()
        Me.RichTextBox1.Text = ""
        Me.WebBrowser1.DocumentText = ""

        If Typ < 3 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    If C1Daten.Columns(i).DataType.Name = "DateTime" Then
                        C1Daten.Columns(i).NumberFormat = "dd.MM.yyyy HH:mm:ss.fff"
                    End If
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()

                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If

        If Typ = 3 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                sql = sql + " where rowid " + ComboBox2.Text + " " + TextBox1.Text + " "
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If

        If Typ = 4 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                Dim yy As String = Trim(Str(Year(DateTimePicker1.Value)))
                Dim dd As String = Trim(Str(Month(DateTimePicker1.Value)))
                Dim mm As String = Trim(Str(Day(DateTimePicker1.Value)))
                Dim hh As String = Trim(Str(Hour(DateTimePicker1.Value)))
                Dim mi As String = Trim(Str(Minute(DateTimePicker1.Value)))
                Dim ss As String = Trim(Str(Second(DateTimePicker1.Value)))
                While Len(mm) < 2
                    mm = "0" + mm
                End While
                While Len(dd) < 2
                    dd = "0" + dd
                End While
                While Len(hh) < 2
                    hh = "0" + hh
                End While
                While Len(mi) < 2
                    mi = "0" + mi
                End While
                While Len(ss) < 2
                    ss = "0" + ss
                End While
                sql = sql + " where insert_timestamp " + ComboBox3.Text + " '" + yy + "-" + mm + "-" + dd + " " + hh + ":" + mi + ":" + ss + "'"
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If


        If Typ = 5 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                Dim yy As String = Trim(Str(Year(DateTimePicker1.Value)))
                Dim dd As String = Trim(Str(Month(DateTimePicker1.Value)))
                Dim mm As String = Trim(Str(Day(DateTimePicker1.Value)))
                While Len(mm) < 2
                    mm = "0" + mm
                End While
                While Len(dd) < 2
                    dd = "0" + dd
                End While
                sql = sql + " where mitarbeiternr " + ComboBox4.Text + " " + Me.TextBox2.Text
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If
        If Typ = 6 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                Dim yy As String = Trim(Str(Year(DateTimePicker1.Value)))
                Dim dd As String = Trim(Str(Month(DateTimePicker1.Value)))
                Dim mm As String = Trim(Str(Day(DateTimePicker1.Value)))
                While Len(mm) < 2
                    mm = "0" + mm
                End While
                While Len(dd) < 2
                    dd = "0" + dd
                End While
                If ComboBox5.Text = "Like" Then
                    sql = sql + " where filename like '%" + TextBox3.Text + "%'"
                Else
                    sql = sql + " where filename ='" + TextBox3.Text + "'"
                End If
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If

        If Typ = 7 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                Dim yy As String = Trim(Str(Year(DateTimePicker1.Value)))
                Dim dd As String = Trim(Str(Month(DateTimePicker1.Value)))
                Dim mm As String = Trim(Str(Day(DateTimePicker1.Value)))
                While Len(mm) < 2
                    mm = "0" + mm
                End While
                While Len(dd) < 2
                    dd = "0" + dd
                End While
                sql = sql + " where tgnummer " + ComboBox6.Text + " '" + Me.TextBox4.Text + "'"
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If

        If Typ = 8 Then
            Try
                If Me.RadioButton1.Checked = True Then Typ = 1
                If Me.RadioButton2.Checked = True Then Typ = 2

                Dim sql As String

                sql = "Select "
                If IsNumeric(Me.ComboBox1.Text) Then
                    sql = sql + "top " + Me.ComboBox1.Text + " * "
                End If
                Select Case Typ
                    Case 1
                        sql = sql + " from dbo.edk_log"
                    Case 2
                        sql = sql + " from dbo.edk_data"
                End Select
                Dim yy As String = Trim(Str(Year(DateTimePicker2.Value)))
                Dim dd As String = Trim(Str(Month(DateTimePicker2.Value)))
                Dim mm As String = Trim(Str(Day(DateTimePicker2.Value)))
                Dim hh As String = Trim(Str(Hour(DateTimePicker2.Value)))
                Dim mi As String = Trim(Str(Minute(DateTimePicker2.Value)))
                Dim ss As String = Trim(Str(Second(DateTimePicker2.Value)))
                While Len(mm) < 2
                    mm = "0" + mm
                End While
                While Len(dd) < 2
                    dd = "0" + dd
                End While
                While Len(hh) < 2
                    hh = "0" + hh
                End While
                While Len(mi) < 2
                    mi = "0" + mi
                End While
                While Len(ss) < 2
                    ss = "0" + ss
                End While
                sql = sql + " where insert_timestamp >= '" + yy + "-" + mm + "-" + dd + " " + hh + ":" + mi + ":" + ss + "'"
                yy = Trim(Str(Year(DateTimePicker3.Value)))
                dd = Trim(Str(Month(DateTimePicker3.Value)))
                mm = Trim(Str(Day(DateTimePicker3.Value)))
                hh = Trim(Str(Hour(DateTimePicker3.Value)))
                mi = Trim(Str(Minute(DateTimePicker3.Value)))
                ss = Trim(Str(Second(DateTimePicker3.Value)))
                While Len(mm) < 2
                    mm = "0" + mm
                End While
                While Len(dd) < 2
                    dd = "0" + dd
                End While
                While Len(hh) < 2
                    hh = "0" + hh
                End While
                While Len(mi) < 2
                    mi = "0" + mi
                End While
                While Len(ss) < 2
                    ss = "0" + ss
                End While
                sql = sql + " and insert_timestamp <= '" + yy + "-" + mm + "-" + dd + " " + hh + ":" + mi + ":" + ss + "'"
                sql = sql + " order by rowid desc"
                data.Get_data(sql)
                Me.C1Daten.DataSource = Nothing
                Me.C1Daten.DataSource = data.dsDaten.Tables(0)
                Me.C1Daten.DataMember = data.dsDaten.Tables(0).TableName
                For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                    Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If

        Try
            For i As Integer = 0 To Me.C1Daten.Splits(0).DisplayColumns.Count - 1
                If C1Daten.Columns(i).DataType.Name = "DateTime" Then
                    C1Daten.Columns(i).NumberFormat = "dd.MM.yyyy HH:mm:ss"
                End If
                Me.C1Daten.Splits(0).DisplayColumns(i).AutoSize()

            Next
        Catch ex As Exception

        End Try

    End Sub

    Private Sub C1Daten_RowColChange(sender As Object, e As RowColChangeEventArgs) Handles C1Daten.RowColChange
        refresh_xml()
    End Sub
    Sub refresh_xml()
        Me.RichTextBox1.Text = ""
        Me.WebBrowser1.DocumentText = ""
        Try
            If Me.CheckBox1.Checked = True Then
                Dim row As Integer = Me.C1Daten.Columns("rowid").Value
                For Each r As DataRow In data.dsDaten.Tables(0).Rows
                    If r("rowid") = row Then
                        'Highlight1(r("xmldata"))
                        'Exit Sub
                        Dim s As String
                        s = UnicodeBytesToString(r("xmldata"))
                        Me.RichTextBox1.Text = s
                        Dim colorizedSourceCode As String = New CodeColorizer().Colorize(s, Languages.Xml)
                        Me.WebBrowser1.DocumentText = colorizedSourceCode
                        'Me.RichTextBox1.Text = UnicodeBytesToString(r("xmldata"))
                        'Dim s As String
                        's = UnicodeBytesToString(r("xmldata"))
                        'AddColouredText(s)
                        Exit Sub
                    End If
                Next
            End If
        Catch
        End Try

    End Sub

    Private Function UnicodeBytesToString(ByVal bytes() As Byte) As String
        Dim ms As New MemoryStream(bytes)
        Return System.Text.Encoding.ASCII.GetString(bytes)
        ms.Dispose()
        ''Return UBound(bytes)
        'Return System.Text.Encoding.Unicode.GetString(bytes)
    End Function

    Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        If Me.CheckBox1.Checked = False Then
            Me.RichTextBox1.Text = ""
            Me.WebBrowser1.DocumentText = ""
        Else
            refresh_xml()

        End If
    End Sub

    Sub Highlight1(ByVal bytes() As Byte)
        Dim ms As New MemoryStream(bytes)
        Dim reader As New XmlTextReader(ms)
        While reader.Read()


            Select Case reader.NodeType


                Case XmlNodeType.Element
                    ' The node is an element.
                    Me.RichTextBox1.SelectionColor = Color.Blue

                    Me.RichTextBox1.AppendText("<")

                    Me.RichTextBox1.SelectionColor = Color.Brown

                    Me.RichTextBox1.AppendText(reader.Name)

                    Me.RichTextBox1.SelectionColor = Color.Blue

                    Me.RichTextBox1.AppendText(">")


                    Exit Select

                Case XmlNodeType.Text
                    'Display the text in each element.
                    Me.RichTextBox1.SelectionColor = Color.Black

                    Me.RichTextBox1.AppendText(reader.Value)

                    Exit Select

                Case XmlNodeType.EndElement
                    'Display the end of the element.
                    Me.RichTextBox1.SelectionColor = Color.Blue

                    Me.RichTextBox1.AppendText("</")

                    Me.RichTextBox1.SelectionColor = Color.Brown

                    Me.RichTextBox1.AppendText(reader.Name)

                    Me.RichTextBox1.SelectionColor = Color.Blue

                    Me.RichTextBox1.AppendText(">")

                    Me.RichTextBox1.AppendText(vbLf)

                    Exit Select


            End Select
        End While

    End Sub

    Private Sub AddColouredText(strTextToAdd As String)
        'Use the RichTextBox to create the initial RTF code
        RichTextBox1.Clear()
        RichTextBox1.Text = strTextToAdd
        Dim strRTF As String = RichTextBox1.Rtf
        RichTextBox1.Clear()

        ' 
        '             * ADD COLOUR TABLE TO THE HEADER FIRST 
        '             * 


        ' Search for colour table info, if it exists (which it shouldn't)
        ' remove it and replace with our one
        Dim iCTableStart As Integer = strRTF.IndexOf("colortbl;")

        If iCTableStart <> -1 Then
            'then colortbl exists
            'find end of colortbl tab by searching
            'forward from the colortbl tab itself
            Dim iCTableEnd As Integer = strRTF.IndexOf("}"c, iCTableStart)
            strRTF = strRTF.Remove(iCTableStart, iCTableEnd - iCTableStart)

            'now insert new colour table at index of old colortbl tag
            ' CHANGE THIS STRING TO ALTER COLOUR TABLE
            strRTF = strRTF.Insert(iCTableStart, "colortbl ;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;}")
        Else

            'colour table doesn't exist yet, so let's make one
            ' find index of start of header
            Dim iRTFLoc As Integer = strRTF.IndexOf("\rtf")
            ' get index of where we'll insert the colour table
            ' try finding opening bracket of first property of header first                
            Dim iInsertLoc As Integer = strRTF.IndexOf("{"c, iRTFLoc)

            ' if there is no property, we'll insert colour table
            ' just before the end bracket of the header
            If iInsertLoc = -1 Then
                iInsertLoc = strRTF.IndexOf("}"c, iRTFLoc) - 1
            End If

            ' insert the colour table at our chosen location                
            ' CHANGE THIS STRING TO ALTER COLOUR TABLE
            strRTF = strRTF.Insert(iInsertLoc, "{\colortbl ;\red128\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;}")
        End If

        '
        '             * NOW PARSE THROUGH RTF DATA, ADDING RTF COLOUR TAGS WHERE WE WANT THEM
        '             * In our colour table we defined:
        '             * cf1 = red  
        '             * cf2 = green
        '             * cf3 = blue             
        '             * 


        For i As Integer = 0 To strRTF.Length - 1
            If strRTF(i) = "<"c Then
                'add RTF tags after symbol 
                'Check for comments tags 
                If strRTF(i + 1) = "!"c Then
                    strRTF = strRTF.Insert(i + 4, "\cf2 ")
                Else
                    strRTF = strRTF.Insert(i + 1, "\cf1 ")
                End If
                'add RTF before symbol
                strRTF = strRTF.Insert(i, "\cf3 ")

                'skip forward past the characters we've just added
                'to avoid getting trapped in the loop
                i += 6
            ElseIf strRTF(i) = ">"c Then
                'add RTF tags after character
                strRTF = strRTF.Insert(i + 1, "\cf0 ")
                'Check for comments tags
                If strRTF(i - 1) = "-"c Then
                    strRTF = strRTF.Insert(i - 2, "\cf3 ")
                    'skip forward past the 6 characters we've just added
                    i += 8
                Else
                    strRTF = strRTF.Insert(i, "\cf3 ")
                    'skip forward past the 6 characters we've just added
                    i += 6
                End If
            End If
        Next
        RichTextBox1.Rtf = strRTF
    End Sub


    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Try
            Dim x = System.Xml.Linq.XDocument.Parse(Me.RichTextBox1.Text)
            MsgBox("XML-Parser erfolgreich")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Fehlerhaftes XML")
        End Try
    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
        Me.SaveFileDialog1.ShowDialog()
        If Me.SaveFileDialog1.FileName <> "" Then
            Dim file As System.IO.StreamWriter
            file = My.Computer.FileSystem.OpenTextFileWriter(Me.SaveFileDialog1.FileName, True)
            file.Write(Me.RichTextBox1.Text)
            file.Flush()
            file.Close()

        End If
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.Leave, ComboBox1.SelectedIndexChanged
        Try
            Me.Get_Data(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Get_Data(3)
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Get_Data(4)
    End Sub

    Private Sub ComboBox5_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox5.SelectedIndexChanged

    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Get_Data(5)
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Get_Data(6)
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Get_Data(7)
    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        Get_Data(8)
    End Sub


    Private Sub DateTimePicker1_KeyDown(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles DateTimePicker1.KeyDown, DateTimePicker2.KeyDown, DateTimePicker3.KeyDown
        If e.Modifiers = System.Windows.Forms.Keys.Control AndAlso e.KeyCode = System.Windows.Forms.Keys.V Then

            Try
                sender.VALUE = System.Windows.Forms.Clipboard.GetText()
                'Me.DateTimePicker1.Value = System.Windows.Forms.Clipboard.GetText()
                'MsgBox(System.Windows.Forms.Clipboard.GetText())
            Catch ex As Exception

            End Try
        End If
    End Sub

    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        Me.Get_Data(1)
    End Sub
End Class