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