Bogart gData.vb

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Imports System.Console
Imports System.Windows.Forms
Imports System.IO
Imports System.Text
Imports System.Drawing

Namespace BogartMis.Cls
    Public Class DataControl
       
#Region "SQL Process  <Added by: langwang at: 2008/12/5-上午 09:46:57 on machine: WXPGNR530> "

        Public Function GetDataTable(ByVal strSQL As String, ByVal aConn As SqlClient.SqlConnection) As DataTable

            Dim netTable As DataTable
            Try
                OpenConn(aConn)
                Dim netDp As SqlClient.SqlDataAdapter
                netTable = New DataTable
                netDp = New SqlClient.SqlDataAdapter(strSQL, aConn)
                netDp.Fill(netTable)
                netDp.Dispose()
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString)
            End Try
            Return netTable
        End Function
        Public Function ExecuteCommand(ByVal strSQL As String, ByVal aConn As SqlClient.SqlConnection) As Boolean
            Dim cmd As New SqlClient.SqlCommand
            Dim i As Integer
            Try
                OpenConn(aConn)
                With cmd
                    .Connection = aConn
                    .CommandText = strSQL
                    .CommandType = CommandType.Text
                    .CommandTimeout = 60
                    i = .ExecuteNonQuery()
                End With
            Catch ex As Exception
                i = -1
            End Try

            If i >= 0 Then
                Return True
            Else
                Return False
            End If
        End Function

        Public Function ExecuteCommand(ByVal strSQL As String, ByRef objPar As String, ByRef objValues() As Object, ByVal sqlConn As SqlClient.SqlConnection) As Boolean
            Dim cmd As New SqlClient.SqlCommand
            Dim i As Integer
            Dim par As SqlClient.SqlParameter
            Dim objPars() As String = objPar.Split(",")
            OpenConn(sqlConn)
            Try
                With cmd
                    .Connection = sqlConn
                    .CommandText = strSQL
                    .CommandType = CommandType.StoredProcedure
                    .CommandTimeout = 60
                    For i = 0 To objPars.Length - 1
                        par = New SqlClient.SqlParameter(objPars(i), objValues(i))
                        .Parameters.Add(par)
                    Next
                    i = .ExecuteNonQuery()
                End With
            Catch ex As Exception
                i = -1
            End Try

            If i >= 0 Then
                Return True
            Else
                Return False
            End If
        End Function

        Public Function SelectValue(ByVal strSQL As String, ByVal aConn As SqlClient.SqlConnection, Optional ByVal DefaultValue As Object = Nothing) As Object
            Dim cmd As SqlClient.SqlCommand
            Dim o As Object = Nothing
            Try
                OpenConn(aConn)
                cmd = New SqlClient.SqlCommand
                With cmd
                    .Connection = aConn
                    .CommandText = strSQL
                    .CommandType = CommandType.Text
                    .CommandTimeout = 60
                    o = .ExecuteScalar()
                End With
            Catch ex As Exception
                o = Nothing
            Finally
                cmd.Dispose()
            End Try
            If o Is Nothing Then
                Return DefaultValue
            Else
                Return o
            End If
        End Function
#End Region


#Region " Ole Process"
        Public Sub SetVesselControl(ByVal netView As DataView, ByVal VesselName As Control)
            Try
                Dim e As Control
                Dim eCbo As ComboBox
                Dim eDtp As DateTimePicker
                Dim eCHK As CheckBox
                Dim ePic As PictureBox
                Dim eNud As NumericUpDown
                For Each e In VesselName.Controls
                    If TypeOf e Is TextBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "txt" Then
                            e.Text = netView.Table.Rows(0)(e.Name)
                        End If
                    ElseIf TypeOf e Is ComboBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "cbo" Then
                            eCbo = CType(e, ComboBox)
                            If eCbo.DataSource Is Nothing Then
                                eCbo.SelectedItem = netView.Table.Rows(0)(e.Name)
                            Else
                                eCbo.SelectedValue = netView.Table.Rows(0)(e.Name)
                            End If
                        End If
                    ElseIf TypeOf e Is DateTimePicker Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "dtp" Then
                            eDtp = CType(e, DateTimePicker)
                            eDtp.Value = netView.Table.Rows(0)(e.Name)

                        End If
                    ElseIf TypeOf e Is NumericUpDown Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "nud" Then
                            eNud = CType(e, NumericUpDown)
                            eNud.Value = netView.Table.Rows(0)(e.Name)
                        End If
                    ElseIf TypeOf e Is CheckBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "chk" Then
                            eCHK = CType(e, CheckBox)
                            eCHK.Checked = CBool(netView.Table.Rows(0)(e.Name))
                        End If
                    ElseIf TypeOf e Is PictureBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "pic" Then
                            ePic = CType(e, PictureBox)
                            Dim By() As Byte = CType(netView.Table.Rows(0)(e.Name), Byte())
                            Dim ms As New MemoryStream(By)
                            ePic.Image = Image.FromStream(ms, True)
                            ms.Close()
                        End If
                    End If
                Next
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString)
            End Try
        End Sub

        Public Sub ClearVessel(ByVal VesselName As Control)
            Try
                Dim e As Control
                Dim eCbo As ComboBox
                Dim eDTP As DateTimePicker
                Dim eCHK As CheckBox
                Dim ePic As PictureBox

                For Each e In VesselName.Controls
                    If TypeOf e Is TextBox Then
                        e.Text = ""
                    ElseIf TypeOf e Is ComboBox Then
                        eCbo = CType(e, ComboBox)
                        eCbo.Text = ""
                    ElseIf TypeOf e Is DateTimePicker Then
                        eDTP = CType(e, DateTimePicker)
                        eDTP.Value = DateTime.Today
                    ElseIf TypeOf e Is CheckBox Then
                        eCHK = CType(e, CheckBox)
                        eCHK.Checked = False
                    ElseIf TypeOf e Is PictureBox Then
                        ePic = CType(e, PictureBox)
                        ePic.Image = Nothing
                    End If
                Next
            Catch ex As Exception
            End Try
        End Sub

        Public Function AddRow(ByVal netView As DataView, ByVal VesselName As Control) As Boolean
            Try
                Dim e As Control
                Dim eTxt As TextBox
                Dim eChk As CheckBox
                Dim eCbo As ComboBox
                Dim ePic As PictureBox
                Dim eDTP As DateTimePicker
                Dim eNud As NumericUpDown
                Dim netRow As DataRowView = netView.AddNew
                netRow.BeginEdit()
                For Each e In VesselName.Controls
                    If TypeOf e Is TextBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "txt" Then
                            netRow(e.Name) = e.Text
                        End If
                    ElseIf TypeOf e Is ComboBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "cbo" Then
                            eCbo = CType(e, ComboBox)
                            If eCbo.DataSource Is Nothing Then
                                netRow(e.Name) = eCbo.GetItemText(eCbo.SelectedItem)
                            Else
                                netRow(e.Name) = eCbo.GetItemText(eCbo.SelectedValue)
                            End If
                        End If
                    ElseIf TypeOf e Is NumericUpDown Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "nud" Then
                            eNud = CType(e, NumericUpDown)
                            netRow(e.Name) = eNud.Value
                        End If
                    ElseIf TypeOf e Is CheckBox Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "chk" Then
                            eChk = CType(e, CheckBox)
                            netRow(e.Name) = -CInt(eChk.Checked)
                        End If
                    ElseIf TypeOf e Is DateTimePicker Then
                        If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "dtp" Then
                            eDTP = CType(e, DateTimePicker)
                            netRow(e.Name) = eDTP.Value
                        End If
                    ElseIf TypeOf e Is PictureBox Then
                        Try
                            If Microsoft.VisualBasic.Left(e.Name.ToLower, 3).ToString <> "pic" Then
                                ePic = CType(e, PictureBox)
                                If ePic.Image Is Nothing Then
                                Else
                                    Dim ms As New MemoryStream
                                    ePic.Image.Save(ms, ePic.Image.RawFormat)
                                    Dim by() As Byte = ms.GetBuffer
                                    ms.Close()
                                    netRow(e.Name) = by
                                End If
                            End If
                        Catch
                        End Try
                    End If
                Next
                netRow.EndEdit()
                Return True
            Catch em As Exception
                Return False
            End Try
        End Function

        Public Function UpdateRow(ByVal netView As DataView, ByVal pkField As String, ByVal VesselName As Control) As Boolean
            Try
                Dim e As Control
                Dim eTxt As TextBox
                Dim eChk As CheckBox
                Dim eCbo As ComboBox
                Dim ePic As PictureBox
                Dim eDTP As DateTimePicker
                Dim eNud As NumericUpDown
                Dim netRow As DataRowView = netView.Item(0)
                netRow.BeginEdit()
                For Each e In VesselName.Controls
                    If e.Name.ToLower <> pkField.ToLower Then
                        If TypeOf e Is TextBox Then
                            If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "txt" Then
                                netRow(e.Name) = e.Text
                            End If
                        ElseIf TypeOf e Is ComboBox Then
                            If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "cbo" Then
                                eCbo = CType(e, ComboBox)
                                If eCbo.DataSource Is Nothing Then
                                    netRow(e.Name) = eCbo.GetItemText(eCbo.SelectedItem)
                                Else
                                    netRow(e.Name) = eCbo.GetItemText(eCbo.SelectedValue)
                                End If
                            End If
                        ElseIf TypeOf e Is CheckBox Then
                            If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "chk" Then
                                eChk = CType(e, CheckBox)
                                netRow(e.Name) = -CInt(eChk.Checked)
                            End If
                        ElseIf TypeOf e Is NumericUpDown Then
                            If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "nud" Then
                                eNud = CType(e, NumericUpDown)
                                netRow(e.Name) = eNud.Value
                            End If
                        ElseIf TypeOf e Is DateTimePicker Then
                            If Microsoft.VisualBasic.Left(e.Name, 3).ToLower <> "dtp" Then
                                eDTP = CType(e, DateTimePicker)
                                netRow(e.Name) = eDTP.Value
                            End If
                        ElseIf TypeOf e Is PictureBox Then
                            Try
                                If Microsoft.VisualBasic.Left(e.Name.ToLower, 3).ToString <> "pic" Then
                                    ePic = CType(e, PictureBox)
                                    If ePic.Image Is Nothing Then
                                    Else
                                        Dim ms As MemoryStream
                                        ePic.Image.Save(ms, ePic.Image.RawFormat)
                                        Dim by() As Byte = ms.GetBuffer
                                        ms.Close()
                                        netRow(e.Name) = by
                                    End If
                                End If
                            Catch
                            End Try
                        End If
                    End If
                Next
                netRow.EndEdit()
                Return True
            Catch em As Exception
                Return False
            End Try
        End Function

        'Added by SimonCheung on 2009/08/28 獲取公司Logo  
        Public Function GetCompanyLogo(ByVal aConn As OleDb.OleDbConnection) As OleDb.OleDbDataAdapter

            Dim netLogo As OleDb.OleDbDataAdapter
            If g.gDefaultCompany = "03" Then
                netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='SHS'", aConn)

            Else
                If g.gDefaultCompany = "04" Then
                    netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='Brunet'", aConn)
                Else
                    netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='Bogart'", aConn)
                End If

            End If

            netLogo.SelectCommand.CommandTimeout = 300
            Return netLogo
        End Function

        'Added by SimonCheung on 2009/10/08 獲取公司Logo  
        Public Function GetCompanyLogo(ByVal aConn As OleDb.OleDbConnection, ByVal sCompany_Code As String, ByVal sCompany_Name As String) As OleDb.OleDbDataAdapter

            Dim netLogo As OleDb.OleDbDataAdapter

            If g.gDefaultCompany = "03" Then
                If sCompany_Name.Trim.Length > 0 Then
                    netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='" & sCompany_Name & "'", aConn)
                Else
                    netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where IntRefCode='" & sCompany_Code & "'", aConn)
                    ' netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='" & IIf(sCompany_Code.Trim = "02", "BLingerie", "SHS") & "'", aConn)
                End If
            Else
                If sCompany_Name.Trim.Length > 0 Then
                    netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='" & sCompany_Name & "'", aConn)
                Else
                    netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where IntRefCode='" & sCompany_Code & "'", aConn)
                    ' netLogo = New OleDb.OleDbDataAdapter("select CompanyLogo as CMPYLOGO from CompanyProfile where CompanyCode='" & IIf(sCompany_Code.Trim = "02", "BLingerie", "Bogart") & "'", aConn)
                End If

            End If

            netLogo.SelectCommand.CommandTimeout = 300
            Return netLogo
        End Function

        'Added by Judy on 2010/12/07
        Public Function getCompanyImage(ByVal CompanyCode As String) As Boolean
            Try
                Dim rs As New ADODB.Recordset
                'Dim connok As Boolean = True
                'If sqlConn_temp.State <> ConnectionState.Open Then
                '    If Me.connSQL = False Then
                '        connok = False
                '    End If
                'End If
                'If connok = True Then
                rs.Open("select CompanyLogo from  RPTDEV.dbo.CompanyProfile where CompanyCode='" & CompanyCode.Trim.ToString & "' ", sqlAdo, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic)
                'End If
                If rs.RecordCount <= 0 Then Exit Function
                rs.MoveFirst()
                Dim by() As Byte = CType(rs.Fields("CompanyLogo").Value, Byte())
                If by Is Nothing Then Exit Function
                Dim myMem As New IO.MemoryStream(by)
                Dim myBmp As New Bitmap(myMem)
                Clipboard.SetDataObject(myBmp)
                Return True
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString)
                Return False
            End Try

        End Function

        'Added by vinson on 2012/12/25
        Public Function getCompanyAdditionalLogo(ByVal aConn As OleDb.OleDbConnection, ByVal sCompany_Code As String, ByVal sCompany_Name As String) As OleDb.OleDbDataAdapter
            Dim netLogo As OleDb.OleDbDataAdapter
            'sCompany_Name = "Brunet"
            netLogo = New OleDb.OleDbDataAdapter("select AdditionalLogo from CompanyProfile where CompanyCode='" & sCompany_Name & "'", aConn)
            netLogo.SelectCommand.CommandTimeout = 300
            Return netLogo
        End Function

        ''Added by Judy to 2010/12/10
        'Public Function InsertCmpLogo(ByVal code As String, ByVal xSheet As Excel.Worksheet, ByVal xBook As Excel.Workbook, ByVal xApp As Excel.Application) As Boolean

        '    Dim intHH As Int16
        '    With xSheet.Range("I1")
        '        intHH = .Rows.Height() - 1
        '    End With
        '    If gData.getCompanyImage(g.CmpCode) = True Then
        '        xSheet.Range("I1").Select()
        '        xSheet.Paste()
        '        xApp.Selection.ShapeRange.LockAspectRatio = True
        '        xApp.Selection.ShapeRange.Height = intHH      '按高填充
        '    End If
        '    'End If
        '    System.Windows.Forms.Clipboard.SetDataObject("")
        'End Function

        ''Added by Judy to 2010/12/07
        'Public Function connSQL() As Boolean
        '    Try
        '        sqlConn_temp.ConnectionString = "Provider=SQLOLEDB.1;data source=" & g.gSqlServer & ";initial catalog='" & g.gServerUser & "';password='" & g.gServerPassWord & "';user id='" & g.gSqlServerUser & "'"
        '        sqlConn_temp.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        '        sqlConn_temp.Open()
        '        Return True
        '    Catch ex As Exception
        '        Return False
        '    End Try
        'End Function

        'Added by simoncheung on 2010/04/22 根據Company Code 取得companyName
        Public Function GetCompanyName(ByVal C_Code As String) As String
            Dim ComanyName As String = gData.SelectValue(" select CompanyCode FROM dbo.CompanyProfile WHERE IntRefCode='" & Trim(C_Code) & "'", sqlAdo)
            If ComanyName.Trim.Length > 0 Then
                Return ComanyName
            Else
                Return " "
            End If
        End Function

        Public Function GetDataSet(ByVal SQL As String, ByVal aConn As OleDb.OleDbConnection, Optional ByVal TableName As String = Nothing) As DataSet
            Try
                Dim netDp As OleDb.OleDbDataAdapter
                Dim netDs As New DataSet
                netDp = New OleDb.OleDbDataAdapter(SQL, aConn)

                If TableName = Nothing Then
                    netDp.Fill(netDs)
                Else
                    netDp.Fill(netDs, TableName)
                End If
                netDp.Dispose()

                Return netDs
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString)
            End Try
        End Function

        Public Function GetDataView(ByVal SQL As String, ByVal aConn As OleDb.OleDbConnection, Optional ByVal TableName As String = Nothing) As DataView
            Dim netDp As OleDb.OleDbDataAdapter
            Dim netDs As New DataSet
            Try
                netDp = New OleDb.OleDbDataAdapter(SQL, aConn)

                If TableName = Nothing Then
                    netDp.Fill(netDs)
                Else
                    netDp.Fill(netDs, TableName)
                End If
                netDp.Dispose()

                Return netDs.Tables(0).DefaultView
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString)
            End Try
        End Function

        Public Function GetDataTable(ByVal SQL As String, ByVal aConn As OleDb.OleDbConnection, Optional ByVal TableName As String = Nothing) As DataTable
            Try
                Dim netDp As OleDb.OleDbDataAdapter
                Dim netTable As DataTable
                If TableName = Nothing Then
                    netTable = New DataTable
                Else
                    netTable = New DataTable(TableName)
                End If
                netDp = New OleDb.OleDbDataAdapter(SQL, aConn)
                netDp.Fill(netTable)
                netDp.Dispose()
                Return netTable
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString)
                If g.gUserId.ToUpper = "SHINYD" Then
                    MsgBox(SQL & " " & ex.ToString)
                End If
            End Try
        End Function

        Protected Overrides Sub Finalize()
            ' If Not impersonationContext Is Nothing Then impersonationContext.Undo()
            MyBase.Finalize()
        End Sub


        '檢查給定的條件記錄是否存在
        Public Function CheckRecord(ByVal strSQL As String, ByVal aConn As ADODB.Connection) As Boolean
            Try

                Dim rs As New ADODB.Recordset
                rs.Open(strSQL, aConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                If rs.RecordCount > 0 Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                Return False
            End Try
        End Function

        '返回給定條件的表第一行第一列的數據
        Public Function selectValue(ByVal strSQL As String, ByVal aConn As ADODB.Connection, Optional ByVal DefaultValue As Object = "") As Object
            Try

                Dim rs As New ADODB.Recordset
                rs.Open(strSQL, aConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                If rs.RecordCount > 0 Then
                    Return Trim(IIf(IsDBNull(rs.Fields(0).Value) = True, DefaultValue, rs.Fields(0).Value))
                Else
                    Return DefaultValue
                End If
            Catch ex As Exception
                Dim f As New IO.FileStream("errorlog.txt", IO.FileMode.OpenOrCreate)
                f.Flush()

                Dim data As [Byte]() = System.Text.Encoding.ASCII.GetBytes(ex.ToString)
                Dim i As Integer
                For i = 0 To data.GetUpperBound(0)
                    f.WriteByte(data(i))
                Next
                f.Close()
                Return DefaultValue
            End Try
        End Function

        ' <Added by: langwang at: 2008/12/5-上午 09:46:57 on machine: WXPGNR530> 
        Public Function SelectValue(ByVal strSQL As String, ByVal aConn As OleDb.OleDbConnection, Optional ByVal DefaultValue As Object = "") As String
            Try
                OpenConn(aConn)
                Dim cmd As New OleDb.OleDbCommand(strSQL, aConn)
                Dim ret As Object = cmd.ExecuteScalar()
                If ret Is Nothing Then
                    Return DefaultValue
                Else
                    Return CStr(ret)
                End If
            Catch ex As Exception
                Return DefaultValue
            End Try
        End Function
        '==================================
        'Add by shiny 2009/04/06  函數 FilterSql
        '過濾Sql關鍵符號
        '=================================
        Public Function FilterSql(ByVal StrF As Object) As String
            Try
                If IsDBNull(StrF) Then
                    Return ""
                End If

                Return Strings.Replace(StrF, "'", "''")
            Catch ex As Exception
                Return StrF
            End Try
        End Function

        Public Function selectValues(ByVal strSQL As String, ByVal aConn As OleDb.OleDbConnection, Optional ByVal DefaultValue As String = "") As String
            Try
                Dim netTable As DataTable = GetDataTable(strSQL, aConn)
                If netTable.Rows.Count > 0 Then
                    Return Trim(IIf(IsDBNull(netTable.Rows(0).Item(0)) = True, DefaultValue, netTable.Rows(0).Item(0)))
                Else
                    Return DefaultValue
                End If
            Catch ex As Exception
                Return DefaultValue
                Exit Function
            End Try
        End Function


        Public Function getClotType(ByVal CLOT As String) As String

            Dim CLOTTD As String = gData.SelectValue(" select VALUE(B.CLOTTD, '') as CLOTTD FROM IMFCLOT A LEFT JOIN IMFCLTT B ON A.SEWFTY = B.CLOTTP WHERE  A.CLOT='" & Trim(CLOT) & "'", adoConn)
            If CLOTTD.Trim.Length > 0 Then
                Return CLOTTD
            Else
                Return " "
            End If
        End Function
        'Added by Simon Cheung on 2010/09/17 Job# 1000342
        Public Function getCustPO(ByVal LOTNO As String) As String
            Dim I As Integer
            Dim TmpTab As DataTable = gData.GetDataTable("SELECT  REMLIN, REMARK FROM ORFORDR WHERE CSTORD='" & LOTNO & "' AND REMTYP = '1' ORDER BY REMLIN ", netConn)
            For I = 0 To TmpTab.Rows.Count - 1
                getCustPO = getCustPO & TmpTab.Rows(I).Item("REMARK")
            Next
            Return getCustPO
        End Function

        Public Function GetPrintTime() As String
            '1 Hour 2 year 3 minute 4 month 5 second 6 date
            Dim strPrintTime As String = Format(Now(), "HHyymmMMssdd")
            Return strPrintTime
        End Function
        Public Function getProductImage(ByVal ProductCode As String, ByVal IMGTYP As String, ByVal s As Integer, ByVal w As Integer) As Boolean
            Try
                If Not System.IO.Directory.Exists("C:\TEMP\") Then
                    System.IO.Directory.CreateDirectory("C:\TEMP\")
                End If
                Dim LogoFileName As String = "C:\TEMP\TmpProduct.jpg"
                Dim TmpLogo As Bitmap = ChangeImageSize(getProductImage(ProductCode, IMGTYP, sqlAdo), s, w)
                TmpLogo.Save(LogoFileName)
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Function getProductImage(ByVal ProductCode As String, ByVal IMGTYP As String, ByVal sqlAdo As ADODB.Connection) As Byte()
            Dim by() As Byte
            Dim rsImage As New ADODB.Recordset
            'last version product,add by vinson on 2013-03-01
            rsImage.Open("select image from ProdSketch.dbo.orximg where imgtyp='" + IMGTYP + "' and deg=(select max(deg) from ProdSketch.dbo.orximg where imgtyp='" + IMGTYP + "' and deg like '" + Mid(ProductCode, 1, 6) + "%') and seqno=(select min(seqno) from ProdSketch.dbo.orximg where imgtyp='" + IMGTYP + "' and deg=(select max(deg) from ProdSketch.dbo.orximg where imgtyp='" + IMGTYP + "' and deg like '" + Mid(ProductCode, 1, 6) + "%'))", sqlAdo)
            If rsImage.RecordCount > 0 Then
                by = CType(rsImage.Fields("image").Value, Byte())
            Else
                by = Nothing
            End If
            rsImage.Close()
            Return by
        End Function

        Public Function ChangeImageSize(ByVal byF As Byte(), Optional ByVal x_W As Int16 = 150, Optional ByVal x_H As Int16 = 150) As System.Drawing.Bitmap
            Try
                Dim ms As New IO.MemoryStream(byF)
                Dim imgT As New PictureBox
                imgT.SizeMode = PictureBoxSizeMode.AutoSize
                imgT.Image = Image.FromStream(ms)
                Dim bmp As New System.Drawing.Bitmap(x_W, x_H)
                Dim grp As Graphics = Graphics.FromImage(bmp)
                Dim blueBrush As New SolidBrush(Color.White)
                grp.FillRectangle(blueBrush, 0, 0, x_W, x_H)
                Dim intW As Single
                Dim intH As Single
                If imgT.Width > x_W Then
                    intW = x_W
                    intH = imgT.Height * (x_W / imgT.Width)
                Else
                    intW = imgT.Width
                    intH = imgT.Height
                End If
                If intH > x_H Then
                    intH = x_H
                    intW = imgT.Width * (x_H / imgT.Height)
                End If
                grp.DrawImage(imgT.Image, (x_W - intW) / 2, (x_H - intH) / 2, intW, intH)
                Return bmp
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Function changePicSize(ByVal by() As Byte, Optional ByVal x_W As Int16 = 150, Optional ByVal x_H As Int16 = 150) As Byte()
            Try
                ChangeImageSize(by, x_W, x_H).Save(Application.StartupPath & "\product.jpg")
                Dim fs As New IO.FileStream(Application.StartupPath & "\product.jpg", IO.FileMode.OpenOrCreate)
                Dim by2(fs.Length) As Byte
                fs.Read(by2, 0, fs.Length)

                fs.Close()
                Return by2
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Function changePicSize2(ByVal by() As Byte, Optional ByVal x_W As Int16 = 150, Optional ByVal x_H As Int16 = 150) As Byte()
            Try
                Dim ms2 As New IO.MemoryStream
                ChangeImageSize(by, x_W, x_H).Save(ms2, System.Drawing.Imaging.ImageFormat.Jpeg)
                Dim by2() As Byte = ms2.GetBuffer

                ms2.Close()
                Return by2
            Catch ex As Exception
                Return Nothing
            End Try
        End Function
#End Region

    End Class
End Namespace