exec sp

Imports Kemet.Never.DA

Namespace Kemet.Never.BL

    Public Class Query

        Private _DBTrans As DBTransaction
        Private _errorMessage As String
        Public Sub New(ByVal DBTrans As DBTransaction)
            _DBTrans = DBTrans

        End Sub
        Public ReadOnly Property errorMessage() As String
            Get
                Return _errorMessage
            End Get
        End Property

        Public Structure Job
            Dim jobName As String
            Dim PartNumber As String
            Dim Routing As String
        End Structure

        Public Function GetJobInfo(ByVal machineName As String) As Job
            Dim jobInfo As New Job
            Try
                Dim sql As String
                Dim resultDS As DataSet
                _errorMessage = ""
                If machineName = "" Then
                    _errorMessage = "Please specify machine"
                    jobInfo.jobName = "error"
                    Return jobInfo

                End If

                sql = "select * from dbo.tbl_OracleJob where 1=1 "
                If machineName <> "" Then
                    sql &= "and jobid='" & machineName & "'"

                End If
                resultDS = _DBTrans.SQLToDataset("Job", sql)
                If resultDS.Tables.Count > 0 AndAlso resultDS.Tables(0).Rows.Count > 0 Then
                Else
                    jobInfo.jobName = "eror"
                    Return jobInfo
                End If
                With jobInfo
                    .jobName = resultDS.Tables(0).Rows(0)("jobid").ToString
                    .PartNumber = resultDS.Tables(0).Rows(0)("partnumber").ToString
                    .Routing = resultDS.Tables(0).Rows(0)("routing").ToString

                End With
                Return jobInfo

            Catch ex As Exception
                'Throw ex
                ' grant select, insert, update on apps.releasedjob to lala
                jobInfo.jobName = "eror"
                Return jobInfo
            End Try
        End Function

        Public Function GetJobInfo2(ByVal PN As String) As DataSet
            Dim strsql As String
            Dim resultDS As DataSet

            _errorMessage = ""
            Try

                If PN = "" Then

                End If

                strsql = "select jobid,partnumber,routing from apps.releasedJob where 1=1"
                If PN <> "" Then
                    strsql &= " and partnumber ='" & PN & "'"
                End If
                resultDS = _DBTrans.SQLToDataset("Job", strsql)
                Return resultDS
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Function UpdateJobStaus(ByVal jobname As String, ByRef TimeInterval As String) As Boolean
            Try

                Dim startTime As String = Date.Now.ToString("yyyy-MM-dd HH:mm:ss")
                Dim updateSql As String = _
                " update dbo.tbl_OracleJob " & _
                " set Status ='released' " & _
                "  where jobid = ' " & jobname & "'"
                _DBTrans.ExecuteSQL(updateSql)

                Dim endTime As String = Date.Now.ToString("yyyy-MM-dd HH:mm:ss")
                TimeInterval = "returnValue"
            Catch ex As Exception
                Return False

            End Try
            Return True
        End Function

        Public Function RemoveJob(ByVal jobid As String, ByVal partNumber As String) As Boolean
            Try
                _errorMessage = ""
                Dim sql As String
                Dim resultDS As New DataSet
                '  Dim resultString As String
                sql = " exec  maint.RemoveJob '" & jobid & "','" & partNumber & "'"
                _DBTrans.ExecuteSQL(sql)

            Catch ex As Exception

            End Try
        End Function
    End Class

End Namespace