Imports System.Data.OracleClient
Imports System.Configuration 'must import in order to access the Web.Config file
Public Class DataHandler
Inherits System.ComponentModel.Component
#Region " Component Designer generated code "
Public Sub New(ByVal Container As System.ComponentModel.IContainer)
MyClass.New()
'Required for Windows.Forms Class Composition Designer support
Container.Add(Me)
End Sub
Public Sub New()
MyBase.New()
'This call is required by the Component Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
strConnection = ConfigurationSettings.AppSettings("ConnectionString")
objConnection.ConnectionString = strConnection
End Sub
'Component overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Component Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Component Designer
'It can be modified using the Component Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
components = New System.ComponentModel.Container
End Sub
#End Region
Private strConnection As String
Private objConnection As New OracleConnection
Private strSQL As String
Public Function getLoginInfo(ByVal strPass As String) As DataSet
'Returns the data from the PrintCheckUsers table needed to
'verify login information when user logs in.
Dim SQL As String
'SQL = "SELECT USERNAME, PASSWORD, DEPARTMENT" & _
' " FROM PRINTCHECKUSERS" & _
' " WHERE USERNAME=:USERNAME"
SQL = "SELECT DEPARTMENT" & _
" FROM PRINTCHECKUSERS" & _
" WHERE PASSWORD=:PASSWORD"
Dim objCommand As New OracleCommand(SQL, objConnection)
Dim objDataAdapter As New OracleDataAdapter(objCommand)
Dim objDataSet As New DataSet
objCommand.CommandType = CommandType.Text
objCommand.Parameters.Add(":PASSWORD", SqlDbType.VarChar)
objCommand.Parameters(":PASSWORD").Value = strPass
' Try
objConnection.Open()
objDataAdapter.Fill(objDataSet)
objConnection.Close()
Return objDataSet
' Catch ex As Exception
'End Try
End Function
Public Function getDepartments() As DataSet
'Returns the departments listed in the PrintCheck table
Dim SQL As String
SQL = "SELECT DISTINCT DEPARTMENT" & _
" FROM PRINTCHECK"
Dim objCommand As New OracleCommand(SQL, objConnection)
Dim objDataAdapter As New OracleDataAdapter(objCommand)
Dim objDataSet As New DataSet
objCommand.CommandType = CommandType.Text
Try
objConnection.Open()
objDataAdapter.Fill(objDataSet)
objConnection.Close()
Return objDataSet
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
Public Function getChecksToBePrinted(ByVal strLOB As String) As DataSet
'Returns the data from the PrintChecks table needed to
'download the print Overpayment checks.
Dim SQL As String
'SQL = "SELECT *" & _
' " FROM PRINTCHECK" & _
' " WHERE (DOWNLOADTIME IS NULL) AND (DEPARTMENT=:DEPARTMENT)"
SQL = "SELECT *" & _
" FROM PRINTCHECK" & _
" WHERE (DOWNLOADTIME IS NULL) AND DEPARTMENT = :DEPARTMENT" & _
" ORDER BY UPPER(REQUESTER)"
Dim objCommand As New OracleCommand(SQL, objConnection)
Dim objDataAdapter As New OracleDataAdapter(objCommand)
Dim objDataSet As New DataSet
objCommand.CommandType = CommandType.Text
objCommand.Parameters.Add(":DEPARTMENT", SqlDbType.VarChar)
objCommand.Parameters(":DEPARTMENT").Value = strLOB
Try
objConnection.Open()
objDataAdapter.Fill(objDataSet)
objConnection.Close()
Return objDataSet
Catch ex As Exception
End Try
End Function
Public Function myData() As DataSet
Dim SQL As String
SQL = "SELECT COMPANY, TRANCODE, GLACCOUNT, CENTER, EFFECTIVEDATE, AMOUNT, DESC1, DESC2, DESC3" & _
" FROM REPORTLS.GLUPLOAD"
Dim objCommand As New OracleCommand(SQL, objConnection)
Dim objDataAdapter As New OracleDataAdapter(objCommand)
Dim objDataSet As New DataSet
objCommand.Parameters.Clear()
objCommand.CommandType = CommandType.Text
objConnection.Open()
objDataAdapter.Fill(objDataSet)
objConnection.Close()
Return objDataSet
End Function
Public Function getGLData() As DataSet
'Returns the data from the GL Upload table needed to
'download the GL Upload Logsheet.
Dim SQL As String
SQL = "SELECT COMPANY, TRANCODE, GLACCOUNT, CENTER, EFFECTIVEDATE, AMOUNT, DESC1, DESC2, DESC3" & _
" FROM REPORTLS.GLUPLOAD"
Dim objCommand As New OracleCommand(SQL, objConnection)
Dim objDataAdapter As New OracleDataAdapter(objCommand)
Dim objDataSet As New DataSet
objCommand.Parameters.Clear()
objCommand.CommandType = CommandType.Text
Try
objConnection.Open()
objDataAdapter.Fill(objDataSet)
objConnection.Close()
Return objDataSet
Catch ex As Exception
End Try
End Function
Public Function TimeStampPrintedChks(ByVal strDepartment As String)
'Returns the data from the PrintChecks table needed to
'download the print Overpayment checks.
Dim SQL As String
SQL = "UPDATE PRINTCHECK" & _
" SET DOWNLOADTIME=SYSDATE" & _
" WHERE (DOWNLOADTIME IS NULL) AND DEPARTMENT = :DEPARTMENT"
Dim objCommand As New OracleCommand(SQL, objConnection)
Dim objDataAdapter As New OracleDataAdapter(objCommand)
Dim objDataSet As New DataSet
objCommand.CommandType = CommandType.Text
objCommand.Parameters.Add(":DEPARTMENT", SqlDbType.VarChar)
objCommand.Parameters(":DEPARTMENT").Value = strDepartment
Try
objConnection.Open()
objDataAdapter.Fill(objDataSet)
objConnection.Close()
Return objDataSet
Catch ex As Exception
End Try
End Function
End Class
