Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class lookupTickets
Private Sub viewTickets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.WindowState = FormWindowState.Maximized
Dim query As String
Dim wrapper As New dbWrapper
Dim list As ArrayList = wrapper.populateEmployees()
Me.cmbComputer.Items.Add("ALL")
Me.cmbRequester.Items.Add("ALL")
Me.cmbTech.Items.Add("ALL")
Me.cmbPriority.Items.Add("ALL")
Me.cmbPriority.Items.Add("VP Level")
Me.cmbPriority.Items.Add("Critical")
Me.cmbPriority.Items.Add("Normal")
Me.cmbPriority.Items.Add("As Time Permits")
Me.cmbStatus.Items.Add("ALL")
Me.cmbStatus.Items.Add("Active")
Me.cmbStatus.Items.Add("Completed")
Me.cmbStatus.Items.Add("On Hold")
For i As Integer = 0 To list.Count - 1
Me.cmbRequester.Items.Add(list(i))
Next i
list = wrapper.populateTechs()
For i As Integer = 0 To list.Count - 1
Me.cmbTech.Items.Add(list(i))
Next i
list = wrapper.populateComputers()
For i As Integer = 0 To list.Count - 1
Me.cmbComputer.Items.Add(list(i))
Next i
Me.cmbTech.SelectedIndex = 0
Me.cmbStatus.SelectedIndex = 0
Me.cmbRequester.SelectedIndex = 0
Me.cmbPriority.SelectedIndex = 0
Me.cmbComputer.SelectedIndex = 0
Me.dgvTickets.BorderStyle = BorderStyle.None
Me.dgvTickets.AutoGenerateColumns = True
query = "SELECT TicketID,StartDate,Summary,NetworkName,Status,Priority FROM Ticket"
showInventory()
End Sub
Private Sub showInventory()
Dim dt As New DataTable
Dim wrapper As New dbWrapper
Dim da As OleDbDataAdapter
da = wrapper.getDataAdapter(generateQuery)
da.Fill(dt)
da.Dispose()
wrapper.closeConn()
Me.dgvTickets.DataSource = dt
Me.dgvTickets.Columns.Item(0).Width = 50
Me.dgvTickets.Columns.Item(1).Width = 65
Me.dgvTickets.Columns.Item(2).Width = 290
End Sub
Private Function generateQuery() As String
Dim query As String
Dim comp As String
Dim req As Integer
Dim tech As Integer
Dim stat As String
Dim prior As String
Dim strReq As String = "%"
Dim strTech As String = "%"
If Me.cmbComputer.Text = "ALL" Then
comp = "%"
Else
comp = Me.cmbComputer.Text
End If
If Me.cmbPriority.Text = "ALL" Then
prior = "%"
Else
prior = Me.cmbPriority.Text
End If
If Me.cmbRequester.Text = "ALL" Then
req = -1
Else
Dim wrapper As New dbWrapper
Dim reader As OleDbDataReader
Dim name() As String
name = Me.cmbRequester.Text.Split
reader = wrapper.retreiveQuery("SELECT EmployeeID FROM Emplotees WHERE FirstName='" + name(0) + "' AND LastName='" + name(1) + "'")
req = CInt(reader.GetValue(0))
End If
If Me.cmbStatus.Text = "ALL" Then
stat = "%"
Else
stat = Me.cmbStatus.Text
End If
If Me.cmbTech.Text = "ALL" Then
tech = -1
Else
Dim wrapper As New dbWrapper
Dim reader As OleDbDataReader
Dim name() As String
name = Me.cmbRequester.Text.Split
reader = wrapper.retreiveQuery("SELECT EmployeeID FROM Emplotees WHERE FirstName='" + name(0) + "' AND LastName='" + name(1) + "'")
tech = CInt(reader.GetValue(0))
End If
If tech <> -1 AndAlso req <> -1 Then
query = "SELECT TicketID,StartDate,Summary,NetworkName,Status,Priority FROM Ticket WHERE NetworkName LIKE '" + comp + "' AND Priority LIKE '" + prior
query += "' AND EmployeeID LIKE " + req.ToString + " AND Status LIKE '" + stat + "' AND Technician LIKE " + tech.ToString
MsgBox("1 - " + query)
ElseIf tech >= 0 AndAlso req = -1 Then
query = "SELECT TicketID,StartDate,Summary,NetworkName,Status,Priority FROM Ticket WHERE NetworkName LIKE '" + comp + "' AND Priority LIKE '" + prior
query += "' AND EmployeeID LIKE '" + strReq + "' AND Status LIKE '" + stat + "' AND Technician LIKE " + tech.ToString
MsgBox("2 - " + query)
ElseIf req >= 0 AndAlso tech = -1 Then
query = "SELECT TicketID,StartDate,Summary,NetworkName,Status,Priority FROM Ticket WHERE NetworkName LIKE '" + comp + "' AND Priority LIKE '" + prior
query += "' AND EmployeeID LIKE " + req.ToString + " AND Status LIKE '" + stat + "' AND Technician LIKE '" + strTech + "'"
MsgBox("3 - " + query)
Else
query = "SELECT TicketID,StartDate,Summary,NetworkName,Status,Priority FROM Ticket WHERE NetworkName LIKE '" + comp + "' AND Priority LIKE '" + prior
query += "' AND EmployeeID LIKE '" + strReq + "' AND Status LIKE '" + stat + "' AND Technician LIKE '" + strTech + "'"
MsgBox("4 - " + query)
End If
Return query
End Function
Private Sub dgvTickets_CellMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvTickets.CellMouseDoubleClick
Dim showTicket As New showTicket(CInt(Me.dgvTickets.CurrentRow.Cells.Item(0).Value))
showTicket.Show()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
'Private Sub cmbStatus_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbStatus.SelectedIndexChanged
' showInventory()
'End Sub
'Private Sub cmbRequester_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbRequester.SelectedIndexChanged
' showInventory()
'End Sub
'Private Sub cmbComputer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbComputer.SelectedIndexChanged
' showInventory()
'End Sub
'Private Sub cmbTech_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbTech.SelectedIndexChanged
' showInventory()
'End Sub
'Private Sub cmbPriority_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbPriority.SelectedIndexChanged
' showInventory()
'End Sub
End Class