AUTS_Desktop_Prod/SQLliteReading/FrmMain.vb

Imports UTS_Core.Database
Imports UTS_Core.DebugLog
Imports UTS_Core.UTSModule

Public Class FrmMain
    Implements IProcessStation

    Private _utsApp As UtsAppForm
    Private _logTable As FlexCell.Grid
    Public Enum ModifyType
        del
        Modify
    End Enum

    Public Enum colType
        ct_int
        ct_varchar
        ct_text
        ct_datetime
    End Enum

    Public g_Pcontrol As ProcessControlAPI
    Public Sub StationChanged() Implements IProcessStation.StationChanged
        My.Settings.Project = _utsApp.ProcessStation.ParentProject.Name
        My.Settings.ProcessStation = _utsApp.ProcessStation.Name
        g_Pcontrol = New ProcessControlAPI(_utsApp)
    End Sub



    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        '自动保存设置
        My.Application.SaveMySettingsOnExit = True
        '初始化UTS窗体信息,失败则关闭窗体
        If InitializeUtsApp() = False Then Return
        CheckForIllegalCrossThreadCalls = False '禁用跨线程检查，简化跨线程操作’
        Me.Text = "BC01 LogViewer (Build:" & Application.ProductVersion & ")"




        isflag = True
        _logTable = New FlexCell.Grid
        IsSaveChangesFlag = True
        GetSqliteUserTableName()
        ModifyList = New Dictionary(Of String, ModifyType)
        ' ColumnType = New Dictionary(Of String, Type)

        AddDateTimePicker()

    End Sub



    Private Function InitializeUtsApp() As Boolean
        _utsApp = UtsAppForm.CreateSingleton

        Try
            If _utsApp.IsInitialized = False Then
                _utsApp.AddStatisticsObserver(Me)
                _utsApp.Initialize()
                _utsApp.LoadStation("Phobos", "T3")
                '_utsApp.LoadStation(My.Settings.Project, My.Settings.ProcessStation)
            End If
        Catch ex As Exception
            MsgBox($"Initialize UTSForm Error:{ex.Message}")
            Close()
            Return False
        End Try

        Return True
    End Function



#Region "数据库操作"


    Public Function GetSqliteTableData(sqlstr As String， Optional withKey As Boolean = True） As DataTable '
        Dim Result As New List(Of String)
        ' Dim sqlstr As String = $"SELECT * FROM TBL_{pojerid}_{stanceid}_TestLog ;"
        Dim Tablename As DataTable = SearchBarcodeExists(sqlstr， withKey)
        If IsNothing(Tablename) Then
            ' MsgBox($"查询数据失败！")
            Return Nothing
        Else
            'If Not Tablename.Columns.Contains("name") Then
            '    MsgBox($"查询数据失败！")
            '    Return Nothing
            'End If
            Return Tablename
            'For i As Integer = 0 To Tablename.Rows.Count - 1
            '    If Tablename.Rows.Item(i)("name").ToString.Contains($"TBL_{pojerid}_{stanceid}_TestLog") Then
            '        Result.Add(Tablename.Rows.Item(i)("name").ToString)
            '    End If
            'Next
            'Return Result
        End If
    End Function

    Public Sub GetSqliteUserTableName()
        Dim inquire As String = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
        Dim TableData As DataTable = GetSqliteTableData(inquire)
        Dim nodes As New List(Of TreeNode)
        If IsNothing(TableData) OrElse Not TableData.Columns.Contains("name") Then
            MsgBox($"获取用户表列表失败！")
            Return
        End If
        For i As Integer = 0 To TableData.Rows.Count - 1
            If TableData.Rows.Item(i)("name").ToString.Contains($"sqlite_") Then Continue For
            Dim pNode As TreeNode = New TreeNode(TableData.Rows.Item(i)("name").ToString) With {.Tag = i}
            nodes.Add(pNode)
        Next
        TreeViewEx1.Nodes.AddRange(nodes.ToArray)
    End Sub



    Public Shared Function SearchBarcodeExists(TableName As String， Optional withKey As Boolean = True) As DataTable
        'Dim result As Boolean = False
        Using db As New DbExecutor(UtsDb.LocalDbType, UtsDb.LocalConnString)
            db.Open()
            Dim cmd As DataTable = db.ExecuteDataTable(TableName， withKey)
            db.Close()
            If IsNothing(cmd) OrElse cmd.Rows.Count = 0 Then Return Nothing
            Return cmd
        End Using

    End Function





    Public isflag As Boolean
    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        If String.IsNullOrEmpty(queryText.Text) Then
            MsgBox("查询语句不能为空！")
            Return
        End If
        isflag = False

        Dim Tablename As DataTable = GetSqliteTableData(queryText.Text)
        TestlogTable.DataSource = Tablename
        _logTable.DataSource = Tablename.Copy
        isflag = True
    End Sub
    Public IsSaveChangesFlag As Boolean
    Private Sub SaveChanges_Click(sender As Object, e As EventArgs) Handles SaveChanges.Click
        If Not IsSaveChangesFlag Then
            If MsgBox("表格已修改,是否保存修改？", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
                Dim strInputMsg As String = InputBox("请输入修改密码保存修改", "密码确认")
                If strInputMsg <> "Cc202023abs" Then
                    MsgBox("修改密码错误，请确认密码!")
                    Return
                Else
                    If Not IsNothing(TestlogTable) Then
                        TransactionOperation()
                        MsgBox("保存修改完成！")
                        Dim nnode As New TreeNode(TableName.Text)
                        Dim isnode As New TreeNodeMouseClickEventArgs(nnode, Nothing, 0, 0, 0)
                        TreeViewEx1_DoubleClick(Nothing, isnode)
                    Else
                        IsSaveChangesFlag = True
                        ModifyList.Clear()
                        Return
                    End If

                End If

            End If
            IsSaveChangesFlag = True
            ModifyList.Clear()
        End If
    End Sub

    Public ModifyList As Dictionary(Of String， ModifyType）

    'Private Sub TestlogTable_CellChange(Sender As Object, e As FlexCell.Grid.CellChangeEventArgs) Handles TestlogTable.CellChange
    '    'Console.WriteLine($"{TestlogTable.Cell(e.Row, e.Col).Text.GetType()}==={ColumnType.Item(TestlogTable.Cell(0, e.Col).Text)}")
    '    'If TestlogTable.Cell(e.Row, e.Col).Text.GetType() = ColumnType.Item(TestlogTable.Cell(0, e.Col).Text) Then
    '    ' If TestlogTable.s Then
    '    If e.Col < 2 OrElse e.Row < 1 Then Return
    '    IsSaveChangesFlag = False
    '    Dim key As String = QueryRowIndex(e.Row)
    '    If ModifyList.ContainsKey(key) Then Return
    '    ModifyList.Add(key, ModifyType.Modify)
    '    'Else
    '    '    TestlogTable.Cell(e.Row, e.Col).Text = _logTable.Cell(e.Row, e.Col).Text
    '    'End If
    'End Sub
    'Public ColumnType As Dictionary(Of String， Type)
    Private Sub TreeViewEx1_DoubleClick(sender As Object, e As TreeNodeMouseClickEventArgs) Handles TreeViewEx1.NodeMouseDoubleClick
        'If Not IsSaveChangesFlag Then
        '    SaveChanges_Click（Nothing, Nothing）
        'Else
        isflag = False
        Dim inquire As String = $"SELECT * FROM {e.Node.Text};"
        Dim TableData As DataTable = GetSqliteTableData(inquire)
        TestlogTable.NewFile()
        'Dim coltypeinten As DataColumn
        If IsNothing(TableData) Then
            MsgBox($"获取用户表数据失败！")
            Return
        End If
        'ColumnType.Clear()
        'For coli As Integer = 0 To TableData.Columns.Count - 1
        '    coltypeinten = TableData.Columns.Item(coli)
        '    ColumnType.Add(coltypeinten.ColumnName, coltypeinten.DataType)
        'Next
        IsSaveChangesFlag = True
        ModifyList.Clear()

        TestlogTable.DataSource = TableData
        _logTable.DataSource = TableData.Copy
        Console.WriteLine(TestlogTable.Cell(1, 1).Text)
        TestlogTable.Range(1, 1, TestlogTable.Rows - 1, 1).Locked = True
        TableName.Text = e.Node.Text
        isflag = True
        'End If
    End Sub

    Private Sub Open_BC01_Table()
        isflag = False
        Dim inquire As String = $"SELECT * FROM TBL_4_6_TestLog;"
        Dim TableData As DataTable = GetSqliteTableData(inquire)
        TestlogTable.NewFile()
        'Dim coltypeinten As DataColumn
        If IsNothing(TableData) Then
            MsgBox($"获取用户表数据失败！")
            Return
        End If
        'ColumnType.Clear()
        'For coli As Integer = 0 To TableData.Columns.Count - 1
        '    coltypeinten = TableData.Columns.Item(coli)
        '    ColumnType.Add(coltypeinten.ColumnName, coltypeinten.DataType)
        'Next
        IsSaveChangesFlag = True
        ModifyList.Clear()

        TestlogTable.DataSource = TableData
        _logTable.DataSource = TableData.Copy
        Console.WriteLine(TestlogTable.Cell(1, 1).Text)
        TestlogTable.Range(1, 1, TestlogTable.Rows - 1, 1).Locked = True
        TableName.Text = "TBL_4_6_TestLog"
        isflag = True
        'End If
    End Sub

    Private Sub DeleteRow_Click(sender As Object, e As EventArgs) Handles DeleteRow.Click
        If TestlogTable.ActiveCell.Row < 2 Then Return
        IsSaveChangesFlag = False
        Dim key As String = QueryRowIndex(TestlogTable.ActiveCell.Row)

        If ModifyList.ContainsKey(key) Then
            ModifyList.Item(key) = ModifyType.del
        Else
            ModifyList.Add(key, ModifyType.del)
        End If
        TestlogTable.Range(TestlogTable.ActiveCell.Row, 0, TestlogTable.ActiveCell.Row, TestlogTable.Cols - 1).FontStrikeout = True
        TestlogTable.Range(TestlogTable.ActiveCell.Row, 0, TestlogTable.ActiveCell.Row, TestlogTable.Cols - 1).BackColor = Color.Red
    End Sub

    Public Function QueryRowIndex(rowid As Integer) As String
        For i = 1 To _logTable.Rows - 1
            If _logTable.Cell(i, 1).Text.Equals(TestlogTable.Cell(rowid, 1).Text) Then
                Return i.ToString
            End If
        Next
        Return 0.ToString
    End Function


#Region "事务操作"

    Public Function TransactionOperation() As Boolean


        Dim thetableName As String = TableName.Text
        Using db As New DbExecutor(UtsDb.LocalDbType, UtsDb.LocalConnString)
            db.Open()
            db.BeginTransaction()

            Dim cmdText As String
            Dim colNames As New List(Of String)

            For Each inten In ModifyList

                Try
                    If inten.Key.Equals("0") Then Continue For
                    cmdText = ModifyListaction(inten, db)
                    db.ExecuteNonQuery(cmdText) '执行语句
                    db.ClearDbParameter()
                Catch ex As Exception
                    IsSaveChangesFlag = False
                    MsgBox($"保存修改失败！详情：{ex}")
                    Try
                        db.RollbackTransaction()
                        db.Close()
                    Catch ex2 As Exception
                        ' Return False
                        'WriteErrorLogToFile($"Write LogQueue To Remote DB Rollback Transaction Error: {ex.Message}")
                    End Try

                    Return False
                End Try
            Next

            db.CommitTransaction()
            db.Close()
        End Using
        ModifyList.Clear()
        IsSaveChangesFlag = True
        Return True
    End Function
    Public Function ModifyListaction(inten As KeyValuePair(Of String, ModifyType), db As DbExecutor) As String
        Dim result As String

        If inten.Value = ModifyType.del Then
            result = $"DELETE FROM {TableName.Text} where {_logTable.Cell(0, 1).Text } ='{_logTable.Cell(Integer.Parse(inten.Key), 1).Text }' "
        Else
            Dim dic As Dictionary(Of String, String) = ResultModifyStr(inten.Key)
            Dim iswhere As String = $" {_logTable.Cell(0, 1).Text} ='{_logTable.Cell(Integer.Parse(inten.Key), 1).Text }'"
            result = db.CmdHelper.Update(TableName.Text, dic, iswhere)
        End If
        Return result
    End Function
    Public Function ResultModifyStr(rownum As Integer) As Dictionary(Of String, String)
        Dim dic As New Dictionary(Of String, String)
        For i As Integer = 2 To _logTable.Cols - 1
            ' Console.WriteLine(_logTable.Column(i).CellType.ToString)_logTable.DateFormat
            If _logTable.Column(i).CellType.ToString().Equals(FlexCell.CellTypeEnum.Calendar.ToString()) Then
                dic.Add(_logTable.Cell(0, i).Text, DateTime.Parse(_logTable.Cell(rownum, i).Text).ToString("s"))
                Continue For
            End If

            dic.Add(_logTable.Cell(0, i).Text, _logTable.Cell(rownum, i).Text)
        Next
        Return dic
    End Function

    Private Sub TestlogTable_CellChanging(Sender As Object, e As FlexCell.Grid.CellChangingEventArgs) Handles TestlogTable.CellChanging
        If e.Col < 2 OrElse e.Row < 1 Then Return
        IsSaveChangesFlag = False
        Dim key As String = QueryRowIndex(e.Row)
        ' TestlogTable.Range(e.Row, 1, e.Row, TestlogTable.Cols - 1).CopyData()
        Console.WriteLine(TestlogTable.Cell(e.Row, e.Col).Text)
        _logTable.Cell(Integer.Parse(key), e.Col).Text = TestlogTable.Cell(e.Row, e.Col).Text
        If ModifyList.ContainsKey(key) Then Return
        TestlogTable.Cell(e.Row, e.Col).BackColor = Color.Red
        ModifyList.Add(key, ModifyType.Modify)

    End Sub

    Dim BeginTime As New DateTimePicker
    Dim EndTime As New DateTimePicker

    Private Sub UpdateToolTip()
        Dim Time_begin As ToolStripControlHost = New ToolStripControlHost(BeginTime)
        Dim Time_End As ToolStripControlHost = New ToolStripControlHost(EndTime)
        ToolStrip2.Items.Insert(1, Time_begin)
        ToolStrip2.Items.Insert(3, Time_End)
    End Sub

    Private Sub AddDateTimePicker()
        UpdateToolTip()
    End Sub
    Private Sub TreeViewEx1_AfterSelect(sender As Object, e As TreeViewEventArgs) Handles TreeViewEx1.AfterSelect

    End Sub

    Private Sub ToolStripButton4_Click(sender As Object, e As EventArgs) Handles ToolStripButton4.Click
        Dim start_date As DateTime = ToolStrip2.Items(1).Text
        Dim end_Date As DateTime = ToolStrip2.Items(3).Text


        Dim tmpStart_Datetime As String = Format(start_date, "yyyy-MM-dd") & " 00:00:00"
        Dim tmpEnd_Datetime As String = Format(end_Date, "yyyy-MM-dd") & " 23:59:59"

        'MsgBox(tmpStart_Datetime & " , " & tmpEnd_Datetime)

        isflag = False
        'SELECT * FROM your_table WHERE datetime_column BETWEEN 'start_date' AND 'end_date';   
        'SELECT * FROM TBL_4_6_TestLog WHERE 'StartTime' BETWEEN '2023-12-12' AND '2023-12-12';
        '日期和时间应该使用YYYY-MM-DD HH:MM:SS格式的字符串表示
        Dim inquire As String = $"SELECT * FROM TBL_4_6_TestLog WHERE StartTime BETWEEN '{tmpStart_Datetime}' AND '{tmpEnd_Datetime}' ORDER BY ID DESC;"
        Dim TableData As DataTable = GetSqliteTableData(inquire)
        TestlogTable.NewFile()

        TestlogTable.AutoRedraw = False

        'Dim coltypeinten As DataColumn
        If IsNothing(TableData) Then
            MsgBox($"查无数据！")
            Return
        End If
        'ColumnType.Clear()
        'For coli As Integer = 0 To TableData.Columns.Count - 1
        '    coltypeinten = TableData.Columns.Item(coli)
        '    ColumnType.Add(coltypeinten.ColumnName, coltypeinten.DataType)
        'Next
        IsSaveChangesFlag = True
        ModifyList.Clear()

        TestlogTable.DataSource = TableData
        _logTable.DataSource = TableData.Copy
        Console.WriteLine(TestlogTable.Cell(1, 1).Text)
        TestlogTable.Range(1, 1, TestlogTable.Rows - 1, 1).Locked = True
        TestlogTable.Column(0).Width = 0

        TestlogTable.Column(1).Width = 35
        For i = 2 To 3
            TestlogTable.Column(i).Width = 20
        Next

        For i = 8 To 14
            TestlogTable.Column(i).Width = 20
        Next

        For i = 8 To TestlogTable.Cols - 1
            TestlogTable.Column(i).Width = 60
        Next


        For i = 1 To TestlogTable.Rows - 1
            If TestlogTable.Cell(i, 9).Text.Trim = "1" Then
                TestlogTable.Range(i, 1, i, TestlogTable.Cols - 1).ForeColor = Color.DarkGreen
            Else
                TestlogTable.Range(i, 1, i, TestlogTable.Cols - 1).ForeColor = Color.DarkRed
            End If
        Next

        TestlogTable.Refresh()
        TestlogTable.AutoRedraw = True


        TableName.Text = "TBL_4_6_TestLog"
        isflag = True
        'End If
    End Sub


    Private Sub ToolStripTextBox1_Click_1(sender As Object, e As EventArgs)

    End Sub

    Private Sub ToolStripButton6_Click(sender As Object, e As EventArgs)

    End Sub


    Private Sub ToolStripButton7_Click(sender As Object, e As EventArgs)

    End Sub


    Private Sub ToolStripTextBox1_Click(sender As Object, e As EventArgs) Handles ToolStripTextBox1.Click
        ToolStripTextBox1.SelectAll()
    End Sub

    Private Sub ToolStripButton7_Click_1(sender As Object, e As EventArgs) Handles ToolStripButton7.Click
        Using dlg As New SaveFileDialog
            dlg.Filter = "Excel 文件(*.xls)|*.xls|CSV 文件(*.csv)|*.csv|PDF 文件(*.pdf)|*.pdf|Flexcell 文件(*.flx)|*.flx"
            dlg.AddExtension = True
            If dlg.ShowDialog <> DialogResult.OK Then Return
            Cursor = Cursors.WaitCursor

            ApplicationLog.WriteInfoLog($"编辑页面执行流程导出中。")
            Try
                Select Case dlg.FilterIndex
                    Case 1
                        TestlogTable.ExportToExcel(dlg.FileName, True, True)
                    Case 2
                        TestlogTable.ExportToCSV(dlg.FileName, True, True)
                    Case 3
                        TestlogTable.ExportToPDF(dlg.FileName)
                    Case 4
                        TestlogTable.SaveFile(dlg.FileName)
                End Select

                ApplicationLog.WriteInfoLog($"导出完成。")
                MsgBox($"导出完成。")
            Catch ex As Exception
                ApplicationLog.WriteErrorLog($"导出失败,原因：{ex.Message}")
                MsgBox($"导出失败,原因：{ex.Message}")
            End Try
            Cursor = Cursors.Arrow
        End Using
    End Sub

    Private Sub ToolStripButton6_Click_1(sender As Object, e As EventArgs) Handles ToolStripButton6.Click
        isflag = False
        Dim dtu_SN As String = Trim(ToolStripTextBox1.Text)
        Dim inquire As String = $"SELECT * FROM TBL_4_6_TestLog WHERE DUT_SN LIKE '%{dtu_SN}%'  ORDER BY ID DESC;"
        Dim TableData As DataTable = GetSqliteTableData(inquire)
        TestlogTable.NewFile()

        TestlogTable.AutoRedraw = False

        'Dim coltypeinten As DataColumn
        If IsNothing(TableData) Then
            MsgBox($"获取用户表数据失败！")
            Return
        End If
        'ColumnType.Clear()
        'For coli As Integer = 0 To TableData.Columns.Count - 1
        '    coltypeinten = TableData.Columns.Item(coli)
        '    ColumnType.Add(coltypeinten.ColumnName, coltypeinten.DataType)
        'Next
        IsSaveChangesFlag = True
        ModifyList.Clear()

        TestlogTable.DataSource = TableData
        _logTable.DataSource = TableData.Copy
        Console.WriteLine(TestlogTable.Cell(1, 1).Text)
        TestlogTable.Range(1, 1, TestlogTable.Rows - 1, 1).Locked = True
        TestlogTable.Column(0).Width = 0
        TestlogTable.Column(1).Width = 35

        For i = 2 To 3
            TestlogTable.Column(i).Width = 20
        Next

        For i = 8 To 14
            TestlogTable.Column(i).Width = 20
        Next

        For i = 8 To TestlogTable.Cols - 1
            TestlogTable.Column(i).Width = 60
        Next


        For i = 1 To TestlogTable.Rows - 1
            If TestlogTable.Cell(i, 9).Text.Trim = "1" Then
                TestlogTable.Range(i, 1, i, TestlogTable.Cols - 1).ForeColor = Color.DarkGreen
            Else
                TestlogTable.Range(i, 1, i, TestlogTable.Cols - 1).ForeColor = Color.DarkRed
            End If
        Next

        TestlogTable.Refresh()
        TestlogTable.AutoRedraw = True

        TableName.Text = "TBL_4_6_TestLog"
        isflag = True
        'End If
    End Sub

    'Private Sub TestlogTable_SetCellText(Sender As Object, e As FlexCell.Grid.SetCellTextEventArgs) Handles TestlogTable.SetCellText
    '    If isflag Then
    '        Dim key As String = QueryRowIndex(e.Row)
    '        TestlogTable.Range(e.Row, 1, e.Row, TestlogTable.Cols - 1).CopyData()
    '        _logTable.Cell(key, e.Col).Text = TestlogTable.Cell(e.Row, e.Row).Text
    '    End If

    'End Sub

    'Private Sub TestlogTable_SetCellText(Sender As Object, e As FlexCell.Grid.SetCellTextEventArgs) Handles TestlogTable.SetCellText
    '    'If e.Col < 2 OrElse e.Row < 1 Then Return
    '    'IsSaveChangesFlag = False
    '    'Dim key As String = QueryRowIndex(e.Row)
    '    'If ModifyList.ContainsKey(key) Then Return
    '    'ModifyList.Add(key, ModifyType.Modify)
    'End Sub



#End Region




#End Region










End Class