Bogart BogartPublic.vb

Imports System.Data.SqlClient
Imports System.Data

#Region "IBogartToolbar,請勿隨便更改"


Interface IBogartToolbar
    Sub j_menuPrint()
    Sub j_LoadData()
    'Sub j_setToolsBar() 
    Sub j_ChangeRecord()
    Sub j_AddRow()
    Sub j_EditRow()
    Property EnabledPrint() As Boolean
    Sub SetToolsControl(ByVal g As myDataGrid.myDatagrid)
    Property CurrentGrid() As myDataGrid.myDatagrid
End Interface
#End Region

Module DataAccess

#Region "資料庫常用方法"
    Public SystemBackColor As Color = Color.FromArgb(190, 190, 215)
    Public SystemBackColor2003 As Color = Color.FromArgb(179, 206, 247)
    Public SystemBackColorMenu As Color = Color.FromArgb(246, 246, 246)

    Public Function ExectueSQL(ByVal strSQL As String, ByVal objConn As OleDb.OleDbConnection) As DataSet
        Dim ds As New DataSet
        Dim dap As New OleDb.OleDbDataAdapter
        Try
            dap = New OleDb.OleDbDataAdapter(strSQL, objConn)
            dap.Fill(ds)
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            dap.Dispose()
        End Try
        Return ds
    End Function
    Public Function ExectueSQL(ByVal strSQL As String, ByVal objConn As SqlClient.SqlConnection) As DataSet
        Dim ds As New DataSet
        Dim dap As New SqlClient.SqlDataAdapter
        Try
            dap = New SqlClient.SqlDataAdapter(strSQL, objConn)
            dap.Fill(ds)
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            dap.Dispose()
        End Try
        Return ds
    End Function
    Public Function ExectueSQL(ByVal SqlCmd As SqlClient.SqlCommand) As DataSet
        Dim ds As New DataSet
        Dim dap As New SqlClient.SqlDataAdapter
        Try
            dap = New SqlClient.SqlDataAdapter(SqlCmd)
            dap.Fill(ds)
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            dap.Dispose()
        End Try
        Return ds
    End Function
    Public Function ExectueSQL(ByVal SqlCmd As OleDb.OleDbCommand) As DataSet
        Dim ds As New DataSet
        Dim dap As New OleDb.OleDbDataAdapter
        Try
            dap = New OleDb.OleDbDataAdapter(SqlCmd)
            dap.Fill(ds)
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            dap.Dispose()
        End Try
        Return ds
    End Function

    Public Function ExectueCmdAS400(ByVal strSQL As String, ByVal objConn As OleDb.OleDbConnection) As Boolean
        Dim ret As Boolean = False
        Try
            Dim retCount As Integer = ExectueCmd(strSQL, objConn)
            ret = IIf(retCount >= 0, True, False)
        Catch ex As Exception
            MsgBox("ExectueCmd " & vbCrLf & ex.ToString())
            ret = False
        End Try
        Return ret
    End Function

    Public Function ExectueCmd(ByVal strSQL As String, ByVal objConn As OleDb.OleDbConnection) As Integer
        Dim cmd As OleDb.OleDbCommand
        Dim ret As Integer = 0
        Try
            cmd = New OleDb.OleDbCommand(strSQL, objConn)
            cmd.CommandType = CommandType.Text
            ret = cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("ExectueCmd:" & ex.ToString() & vbCrLf & strSQL)
            ret = -1
        Finally
            cmd.Dispose()
        End Try
        Return ret
    End Function
    Public Function ExectueCmd(ByVal strSQL As String, ByVal objConn As SqlClient.SqlConnection) As Integer
        Dim cmd As SqlClient.SqlCommand
        Dim ret As Integer
        If objConn.State = ConnectionState.Closed Then
            OpenConn(objConn)
        End If
        Try
            cmd = New SqlClient.SqlCommand(strSQL, objConn)
            ret = cmd.ExecuteNonQuery()
        Catch ex As Exception
            ret = -1
        Finally
            cmd.Dispose()
        End Try
        Return ret
    End Function
    Public Function ExectueCmd(ByVal SqlCmd As SqlClient.SqlCommand) As Integer
        Dim ret As Integer = -1
        Try
            ret = SqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            ret = -1
        Finally
            SqlCmd.Dispose()
        End Try
        Return ret
    End Function

    Public Function ExectueCmd(ByVal SqlCmd As OleDb.OleDbCommand) As Integer
        Dim ret As Integer = -1
        Try
            ret = SqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            ret = -1
        Finally
            SqlCmd.Dispose()
        End Try
        Return ret
    End Function

    Public Function CDecs(ByVal strValue As String) As Decimal
        If strValue.Trim() = "" Then
            Return 0
        Else
            Return CDec(strValue)
        End If
    End Function

    Public Function IsALL(ByVal strValue As String) As String
        strValue = strValue.Trim()
        If strValue = "" Then
            Return "All"
        Else
            Return strValue
        End If
    End Function
#End Region '請勿隨便更改

#Region "讀取Logo的方法"

    ''' <summary>
    '''     添加公用報表Logo方法
    ''' </summary>
    ''' <param name="ds" type="Data.DataSet"> 包含 Logo(Table) 的DataSet </param>
    ''' <param name="AliasName" type="String">可選參數,資料庫字段的別名,默認:CompanyLogo,不區分大小寫.</param>
    Public Sub GetLogo(ByRef ds As DataSet, Optional ByVal AliasName As String = "CompanyLogo")
        Try
            Dim dt As DataTable = GetLogo(AliasName)
            ds.Tables.Add(dt)
            ds.AcceptChanges()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    ''' <summary>
    '''     添加公用報表Logo函數
    ''' </summary>
    ''' <param name="AliasName" type="String">可選參數,資料庫字段的別名,默認:CompanyLogo,不區分大小寫.</param>
    ''' <param name="tabName" type="String">可選參數,返回 Logo的表的名稱,默認:Logo,分大小寫.</param>
    ''' <returns> A System.Data.DataTable value </returns>
    Public Function GetLogo(Optional ByVal AliasName As String = "CompanyLogo", Optional ByVal tabName As String = "Logo") As DataTable
        Try
            Dim strSQL As String = ""
            Dim strCompanyCode As String = ""
            If tabName.Trim = "" Then
                tabName = "Logo"
            End If

            If AliasName.Trim = "" Then
                AliasName = "companylogo"
            End If
            Select Case g.gLocation
                Case LocationType.CHINA_SHENZHEN
                    strCompanyCode = "SHS"
                Case LocationType.THAILAND
                    strCompanyCode = "Thailand"
                Case LocationType.HONGKONG
                    strCompanyCode = "AFT"
                Case LocationType.BRUNE
                    strCompanyCode = "Brunet"
                Case Else
                    strCompanyCode = "Bogart"
            End Select
            Dim dt As DataTable = GetLogo(strCompanyCode, AliasName, tabName)
            Return dt.Copy
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Function
    ''' <summary>
    '''     添加公用報表Logo函數
    ''' </summary>
    ''' <param name="strCompanyCode" type="String">公司編號,即:Logo的編號</param>  
    ''' <param name="AliasName" type="String">資料庫字段的別名,不區分大小寫.</param>
    ''' <param name="tabName" type="String"> 返回 Logo的表的名稱,分大小寫.</param>
    ''' <returns> A System.Data.DataTable value </returns>
    Public Function GetLogo(ByVal strCompanyCode As String, _
                              ByVal AliasName As String, _
                              ByVal tabName As String) As DataTable
        Try
            Dim strSQL As String = ""
            Dim dt As DataTable = Nothing
            If tabName.Trim = "" And AliasName.Trim = "" Then
                Return dt
            End If
            If AliasName.ToLower = "companylogo" Then
                strSQL = "select CompanyLogo  from CompanyProfile where CompanyCode='" & strCompanyCode & "'"
            Else
                strSQL = "select CompanyLogo as " & AliasName & "  from CompanyProfile where CompanyCode='" & strCompanyCode & "'"
            End If
            dt = DataAccess.ExectueSQL(strSQL, SqlConnect).Tables(0)
            dt.TableName = tabName
            Return dt.Copy
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Function
#End Region '請勿隨便更改

    Public tmp_PHFRMT As DataTable

    Public Sub SetCheckBox(ByVal MyGrid As myDataGrid.myDatagrid, ByVal strColName As String)
        strColName = strColName.Trim
        If strColName = "" Then
            Exit Sub
        End If
        Dim items As C1.Win.C1TrueDBGrid.ValueItems = MyGrid.Columns(strColName).ValueItems

        With items
            ' we're going to translate values - the datasource needs to hold at least 3 states
            .Translate = True
            ' each click will cycle thru the various checkbox states
            .CycleOnClick = True
            .Presentation = C1.Win.C1TrueDBGrid.PresentationEnum.CheckBox
            ' now associate underlying db values with the checked state
            .Values.Clear()
            .Values.Add(New C1.Win.C1TrueDBGrid.ValueItem("0", False)) ' unchecked
            .Values.Add(New C1.Win.C1TrueDBGrid.ValueItem("1", True)) ' checked
            ''.Values.Add(New C1.Win.C1TrueDBGrid.ValueItem("2", "INDETERMINATE")) ' indeterminate state
        End With
    End Sub

    ''' <summary>
    ''' 返回本地IP
    ''' </summary> 
    Public Function IPAdress() As String
        Return System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList(0).ToString().Trim()
    End Function

    Public Function ToDataTable(ByVal MyGrid As myDataGrid.myDatagrid) As DataTable
        Dim ObjDataTable As DataTable
        Try
            If TypeOf (MyGrid.DataSource) Is DataView Then
                Dim obDataView As DataView = CType(MyGrid.DataSource, DataView)
                ObjDataTable = obDataView.Table.Clone()
                Dim idx As Integer = 0
                Dim strColNames(ObjDataTable.Columns.Count) As String
                For Each col As DataColumn In ObjDataTable.Columns
                    strColNames(idx) = col.ColumnName
                    idx += 1
                Next
                Dim IEnrator As IEnumerator = obDataView.GetEnumerator()
                While IEnrator.MoveNext()
                    Dim drv As DataRowView = CType(IEnrator.Current, DataRowView)
                    Dim dr As DataRow = ObjDataTable.NewRow
                    Try
                        For Each strName As String In strColNames
                            dr(strName) = drv(strName)
                        Next
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
                    ObjDataTable.Rows.Add(dr)
                End While
            ElseIf TypeOf (MyGrid.DataSource) Is DataTable Then
                ObjDataTable = CType(MyGrid.DataSource, DataTable)
            End If
        Catch oE As System.Exception
            ObjDataTable = Nothing
            ErrorMsg.Show(oE)
        End Try
        Return ObjDataTable
    End Function
    ''' <summary>
    '''     返回一個字符串
    ''' </summary>
    ''' <param name="o" type="Object"> </param>
    ''' <returns> A System.String value </returns>
    Public Function Trims(ByVal o As Object) As String
        If TypeOf (o) Is DBNull Then
            Return ""
        Else
            Return Convert.ToString(o).Trim()
        End If
    End Function

End Module