EmployeeMenu

Public Class EmployeeMenu

    Private Sub btnLogout_Click(sender As Object, e As EventArgs) Handles btnLogout.Click
        con.Close()
        Me.Close()
        FormLogin.Show()
        FormLogin.Refresh()
    End Sub

    Private Sub cmbSTTitles_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbSTTitles.SelectedIndexChanged
        txtTitle.Clear()

        If cmbSTTitles.Text = "Other" Then
            txtTitle.Enabled = True     'show and enable custom title text box when other is selected
            txtTitle.Visible = True
        Else
            txtTitle.Visible = False    'hide and disable custom title text box when other isn't selected
            txtTitle.Enabled = False
        End If
    End Sub

    Private Sub EmployeeMenu_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.CenterToScreen() 'Opens form in the centre of the screen
        Dim TotalTickets As String

        Dim sql As String
        sql = "SELECT COUNT(*) FROM tblSubmitted_Ticket_Details WHERE Employee_ID = '" & UserIDDatabase & "'" 'Counts number of tickets send to display "x number Of tickets have been sent"
        db.connect()
        ds = db.sqlTotalTickets(sql)

        db.Close()

        TotalTickets = (ds.Tables("TotalTickets").Rows(0).Item(0))
        lblOverviewAmountSent.Text = TotalTickets & " tickets have been sent in total"

        LoadSentTickets()
        lstSentTicketsBubbleSort()  'Bubble sort algorithm to sort lstSentTickets listview by most recent date
        LoadLastTicket()
        LoadTicketReplies()
        lstTicketRepliesBubbleSort()    'Bubble sort algorithm to sort lstTicketReplies listview by most recent date
        con.Close()

    End Sub
    'Below are the functions to display the sent tickets, last ticket sent, and the ticket replies from the access tables into
    'three different listview controls. I have put this into a function so that I can recall it without typing out the
    'whole code again, making the code more efficient and being more time efficient for me: the programmer

    Public Function LoadSentTickets()
        Dim recordCount As Integer
        Dim XCounter As Integer = 0

        Dim sql As String = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC" 'Function to load data into listview ordered by the time
        db.connect()
        ds = db.sqltblSubmitted_Ticket_Details(sql)
        db.Close()

        recordCount = ds.Tables("tblSubmitted_Ticket_Details").Rows.Count
        lstSentTickets.Items.Clear()
        Do Until XCounter = recordCount
            lstSentTickets.Items.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(0))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(1))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(2))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(3))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(4))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(5))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(6))
            XCounter = XCounter + 1
        Loop

        Return lstSentTickets.Text
    End Function
    Public Function LoadLastTicket()
        Dim recordCount As Integer
        Dim XCounter As Integer = 0

        Dim sql As String = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM " _
        & "tblSubmitted_Ticket_Details WHERE Employee_ID = '" & UserIDDatabase & "' ORDER BY Ticket_Date DESC" 'Function to load data into listview ordered by the time
        db.connect()
        ds = db.sqltblSubmitted_Ticket_Details(sql)
        db.Close()

        recordCount = ds.Tables("tblSubmitted_Ticket_Details").Rows.Count
        If (ds.Tables("tblSubmitted_Ticket_Details").Rows.Count) = 0 Then

        Else
            lstLastTicket.Items.Clear()
            'Do Until XCounter = recordCount
            lstLastTicket.Items.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(0))
            lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(1))
            lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(2))
            lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(3))
            lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(4))
            lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(5))
            lstLastTicket.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(6))
            XCounter = XCounter + 1
            ' Loop 'No loop = no more than 1 record showing
        End If

        Return lstLastTicket.Text
    End Function
    Public Function LoadTicketReplies()

        Dim recordCount As Integer
        Dim XCounter As Integer = 0


        Dim sql As String = "SELECT Reply_ID, TechSupport_ID, Reply_Description, Reply_Date FROM tblTicket_Reply_Details " _
        & "WHERE tblTicket_Reply_Details.Employee_ID = '" & UserIDDatabase & "'"

        db.connect()
        ds = db.sqlDetails(sql)
        db.Close()

        '#Region "Add items to listview loop"
        recordCount = ds.Tables("Details").Rows.Count
        lstTicketReplies.Items.Clear()
        Do Until XCounter = recordCount
            lstTicketReplies.Items.Add(ds.Tables("Details").Rows(XCounter).Item(0))
            lstTicketReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(1))
            lstTicketReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(2))
            lstTicketReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(3))

            XCounter = XCounter + 1
        Loop
        '#End Region

        Return lstTicketReplies
    End Function

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
        '#Region "Control Variables"
        Dim Title, TitleOther, ComputerID, Department, DepartmentOther, Desc, SystemDate, TicketDeadline As String
        Dim NullDate As Date
        NullDate = #12/31/9999 11:59:59#
        Title = cmbSTTitles.Text
        TitleOther = txtTitle.Text
        Desc = txtSTDescription.Text
        SystemDate = lblSTDate.Text
        TicketDeadline = dtpDeadline.Value.ToString
        Department = cmbDepartment.Text
        DepartmentOther = txtDepartmentOther.Text
        'Declaring the controls on the submit ticket screen (variables)
        '#End Region

        '#Region "PRESENCE CHECKS FOR ALL POSSIBLE COMBINATIONS WITH SQLs"

        'if the combobox is "Other" then use the sql to include the custom text box instead of the combo box
        'but if the combolist isn't "other" or "" then it is everything else in there, so if all of the other text is filled in, the sql
        'without the custom text box is used as the items in the combobox is recognised as anything that isn't "other" and ""


        If cmbSTTitles.Text = "" Or cmbDepartment.Text = "" Or txtSTDescription.Text = "" Or dtpDeadline.Value < SystemDate Then
            MsgBox("You haven't filled in all of the required fields, please recheck your submission.") 'If something hasn't been filled in, tell the user and do not send a ticket
        ElseIf cmbSTTitles.Text = "Other" And (txtTitle.Text = "" Or txtSTDescription.Text = "" Or dtpDeadline.Value < SystemDate) Then
            MsgBox("You haven't filled in all of the required fields, please recheck your submission.")
        ElseIf cmbDepartment.Text = "Other" And (txtSTDescription.Text = "" Or dtpDeadline.Value < SystemDate) Then
            MsgBox("You haven't filled in all of the required fields, please recheck your submission.")
        ElseIf cmbSTTitles.Text = "Other" And cmbDepartment.Text = "Other" And (txtTitle.Text = "" Or txtDepartmentOther.Text = "" Or txtSTDescription.Text = "") Then
            MsgBox("You haven't filled in all of the required fields, please recheck your submission.")
        ElseIf cmbSTTitles.Text = "Other" And cmbDepartment.Text = "Other" Then


            Dim sql As String
            sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'"
            db.connect()
            ds = db.sqlComputerID(sql)
            db.Close()

            ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0))    'Navigates to value in ds array and stores it as a string variable

            Dim sql2 As String
            sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Ticket_Title, Department, Ticket_Description, Ticket_Date, Ticket_Deadline, " _
            & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & TitleOther & " ',' " & DepartmentOther & " ',' " & Desc & "','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')"
            db.connect()
            ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2)
            db.Close()

            MsgBox("Ticket sent successfully!")
            tabOverview.Show()  'Automatically navigates back to the homescreen
            ClearSendTicketFields() 'Delete the text in the fields so nothing is in them

        ElseIf cmbSTTitles.Text = "Other" Then
            Dim sql As String
            sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'"
            db.connect()
            ds = db.sqlComputerID(sql)
            db.Close()

            ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0))    'Navigates to value in ds array and stores it as a string variable

            Dim sql2 As String
            sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, " _
            & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & Department & "','" & TitleOther & "','" & Desc & "','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')"
            db.connect()
            ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2)
            db.Close()

            MsgBox("Ticket sent successfully!")
            tabOverview.Show()  'Automatically navigates back to the homescreen
            ClearSendTicketFields() 'Delete the text in the fields so nothing is in them

        ElseIf cmbDepartment.Text = "Other" Then
            Dim sql As String
            sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'"
            db.connect()
            ds = db.sqlComputerID(sql)
            db.Close()
            ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0))    'Navigates to value in ds array and stores it as a string variable

            Dim sql2 As String
            sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, " _
            & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & DepartmentOther & "','" & Title & " ',' " & Desc & "','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')"
            db.connect()
            ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2)
            db.Close()

            MsgBox("Ticket sent successfully!")
            tabOverview.Show()  'Automatically navigates back to the homescreen
            ClearSendTicketFields() 'Delete the text in the fields so nothing is in them

        Else

            Dim sql As String
            sql = "SELECT Computer_ID FROM tblEmployee_Details WHERE Employee_ID = '" & UserIDDatabase & "'"
            db.connect()
            ds = db.sqlComputerID(sql)
            db.Close()

            ComputerID = (ds.Tables("ComputerID").Rows(0).Item(0))    'Navigates to value in ds array and stores it as a string variable

            Dim sql2 As String
            sql2 = "INSERT INTO tblSubmitted_Ticket_Details (Employee_ID, Computer_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, " _
            & "Ticket_SolveDate) VALUES ('" & UserIDDatabase & "','" & ComputerID & "','" & Department & "','" & Title & "','" & Desc & "','" & SystemDate & "','" & TicketDeadline & "','" & NullDate & "')"
            db.connect()
            ds = db.sqltblSubmitted_Ticket_DetailsSql2(sql2)
            db.Close()

            MsgBox("Ticket sent successfully!")
            tabOverview.Show()  'Automatically navigates back to the homescreen
            ClearSendTicketFields() 'Delete the text in the fields so nothing is in them

        End If
        '#End Region

        LoadSentTickets()
        lstSentTicketsBubbleSort()
        LoadLastTicket()
        con.Close()

    End Sub

    Private Sub tmrDate_Tick(sender As Object, e As EventArgs) Handles tmrDate.Tick
        lblSTDate.Text = Format(Now, "dd/MM/yyyy HH:mm") 'Shows current system date - updates once per second
        lblOverviewDate.Text = "The current date is " & Format(Now, "dd/MM/yyyy HH:mm:ss") 'Shows current system date along with "the current time is" - updates once per second
    End Sub

    Private Sub btnLoadReplies_Click(sender As Object, e As EventArgs)
        LoadSentTickets()
        lstSentTicketsBubbleSort()
    End Sub

    Private Sub cmbDepartment_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbDepartment.SelectedIndexChanged
        txtDepartmentOther.Clear()

        If cmbDepartment.Text = "Other" Then
            txtDepartmentOther.Enabled = True
            txtDepartmentOther.Visible = True
        Else                                  'Department combobox will only be visible when "Other" is selected from "Area of business/department"
            txtDepartmentOther.Enabled = False
            txtDepartmentOther.Visible = False
        End If
    End Sub

    Private Sub btnMyTicketSearch_Click(sender As Object, e As EventArgs) Handles btnMyTicketSearch.Click
        SearchSentTickets()
    End Sub

    Private Sub txtTicketSearchOther_TextChanged(sender As Object, e As EventArgs) Handles txtTicketSearchOther.TextChanged
        SearchSentTickets()
    End Sub

    Private Sub cmbTicketSearch_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbTicketSearch.SelectedIndexChanged
        txtTicketSearchOther.Clear()

        Dim TicketSearch As String = cmbTicketSearch.Text
        Dim TicketSearchOther As String = txtTicketSearchOther.Text

        If cmbTicketSearch.Text = "Other" Then
            txtTicketSearchOther.Enabled = True
            txtTicketSearchOther.Visible = True
        Else
            txtTicketSearchOther.Enabled = False
            txtTicketSearchOther.Visible = False
        End If
        SearchSentTickets()
    End Sub

    Private Sub btnRefreshReplies_Click(sender As Object, e As EventArgs) Handles btnRefreshReplies.Click
        LoadTicketReplies()
        lstTicketRepliesBubbleSort()
    End Sub

    'Functions to be recalled so code is more efficient and not repeated
    Function SearchSentTickets()

        Dim sql As String
        Dim recordCount As Integer
        Dim XCounter As Integer = 0
        Dim TicketSearch As String = cmbTicketSearch.Text
        Dim TicketSearchOther As String = txtTicketSearchOther.Text


        If cmbTicketSearch.Text = "Other" Then

            sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details WHERE Ticket_Title LIKE '%" & TicketSearchOther & "%' ORDER BY Ticket_Date ASC"
        ElseIf cmbTicketSearch.Text = "All" Then
            sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC"
        Else
            sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details WHERE Ticket_Title LIKE '%" & TicketSearch & "%' ORDER BY Ticket_Date ASC"
        End If

        'listview will load records from the text in the combobox but if 'other' is selected in the combo box it will search by the textbox instead
        If cmbTicketSearch.Text = "" Then
            sql = "SELECT Ticket_ID, Department, Ticket_Title, Ticket_Description, Ticket_Date, Ticket_Deadline, Ticket_Solved FROM tblSubmitted_Ticket_Details ORDER BY Ticket_Date ASC"
        End If
        db.connect()
        ds = db.sqltblSubmitted_Ticket_Details(sql)
        db.Close()

        'If nothing is typed in, then the listview will load all records from the table
        '#Region "Add items to list view loop"

        recordCount = ds.Tables("tblSubmitted_Ticket_Details").Rows.Count
        lstSentTickets.Items.Clear()
        Do Until XCounter = recordCount
            lstSentTickets.Items.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(0))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(1))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(2))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(3))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(4))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(5))
            lstSentTickets.Items(XCounter).SubItems.Add(ds.Tables("tblSubmitted_Ticket_Details").Rows(XCounter).Item(6))
            XCounter = XCounter + 1
        Loop
        '#End Region
        con.Close() 'Closes the connection
        lstSentTicketsBubbleSort()  'After the items are re-added to the listview, sort them again using this bubble sort algorithm

        Return lstSentTickets
    End Function

    Function lstSentTicketsBubbleSort()
        'Bubble sort algorithm to sort listview by newest date first
        Dim Sorted As Boolean = False

        Do While Sorted = False 'While the list is not sorted
            Sorted = True 'The list is sorted until otherwise stated

            For ItemPosition As Integer = 0 To lstSentTickets.Items.Count - 2 'Go through each item in listv

                If CDate(lstSentTickets.Items(ItemPosition).SubItems(4).Text) < CDate(lstSentTickets.Items(ItemPosition + 1).SubItems(4).Text) Then 'If first item is smaller than next item

                    Dim tempName As String = lstSentTickets.Items(ItemPosition + 1).Text 'Set the placeholder to hold the current item
                    Dim tempSubItems(3) 'Create an array for the sub items sub item. 5 instead of 6 as array starts at 0
                    For SubItem As Integer = 1 To 4 'For each sub item in the current item
                        tempSubItems(SubItem - 1) = lstSentTickets.Items(ItemPosition + 1).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem
                    Next

                    lstSentTickets.Items(ItemPosition + 1).Text = lstSentTickets.Items(ItemPosition).Text 'Set the current item text as equal to the next item
                    For SubItem As Integer = 1 To 4 'For each sub item in the current item
                        lstSentTickets.Items(ItemPosition + 1).SubItems(SubItem).Text = lstSentTickets.Items(ItemPosition).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem
                    Next

                    lstSentTickets.Items(ItemPosition).Text = tempName 'Set the next item to be the placeholder (what the old value was)
                    For SubItem As Integer = 1 To 4 'For each subitem in the temporary subitems array
                        lstSentTickets.Items(ItemPosition).SubItems(SubItem).Text = tempSubItems(SubItem - 1) 'Set the array entry to be equal to that of its corresponding temporary subitem
                    Next
                    'This rotates / swaps the two values
                    Sorted = False 'Show that the list was not sorted and another check needs to be performed
                End If
            Next
        Loop
        Return lstSentTickets
    End Function

    Function lstTicketRepliesBubbleSort()
        'Bubble sort algorithm to sort listview by newest date first
        Dim Sorted As Boolean = False

        Do While Sorted = False 'While the list is not sorted
            Sorted = True 'The list is sorted until otherwise stated

            For ItemPosition As Integer = 0 To lstTicketReplies.Items.Count - 2 'Go through each item in listv

                If CDate(lstTicketReplies.Items(ItemPosition).SubItems(3).Text) < CDate(lstTicketReplies.Items(ItemPosition + 1).SubItems(3).Text) Then 'If first item is smaller than next item

                    Dim tempName As String = lstTicketReplies.Items(ItemPosition + 1).Text 'Set the placeholder to hold the current item
                    Dim tempSubItems(3) 'Create an array for the sub items sub item. 5 instead of 6 as array starts at 0
                    For SubItem As Integer = 1 To 3 'For each sub item in the current item
                        tempSubItems(SubItem - 1) = lstTicketReplies.Items(ItemPosition + 1).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem
                    Next

                    lstTicketReplies.Items(ItemPosition + 1).Text = lstTicketReplies.Items(ItemPosition).Text 'Set the current item text as equal to the next item
                    For SubItem As Integer = 1 To 3 'For each sub item in the current item
                        lstTicketReplies.Items(ItemPosition + 1).SubItems(SubItem).Text = lstTicketReplies.Items(ItemPosition).SubItems(SubItem).Text 'Set the array entry to be equal to that of its corresponding subitem
                    Next

                    lstTicketReplies.Items(ItemPosition).Text = tempName 'Set the next item to be the placeholder (what the old value was)
                    For SubItem As Integer = 1 To 3 'For each subitem in the temporary subitems array
                        lstTicketReplies.Items(ItemPosition).SubItems(SubItem).Text = tempSubItems(SubItem - 1) 'Set the array entry to be equal to that of its corresponding temporary subitem
                    Next
                    'This rotates / swaps the two values
                    Sorted = False 'Show that the list was not sorted and another check needs to be performed
                End If
            Next
        Loop
        Return lstTicketReplies
    End Function

    Function ClearSendTicketFields()
        txtDepartmentOther.Clear()
        cmbSTTitles.SelectedIndex = 0
        txtTitle.Clear()
        txtSTDescription.Clear()
        Return TechSupportMenu
    End Function

End Class