Imports System.Data.OleDb
Imports System.Threading
Public Class FrmStudents

    Private Sub FrmStudents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        LoadStudentData()

    End Sub


    Private Sub dgvStudents_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvStudents.CellClick
        'when a cell in the table is clicked it populates the text and combo boxes with the corresponding values in the table
        Dim thisRow As Integer = e.RowIndex

        txtStudentID.Text = dgvStudents.Item(0, thisRow).Value
        txtFirstName.Text = dgvStudents.Item(1, thisRow).Value
        txtSurname.Text = dgvStudents.Item(2, thisRow).Value
        cmbGender.Text = dgvStudents.Item(3, thisRow).Value
        cmbHouse.Text = dgvStudents.Item(4, thisRow).Value
        cmbForm.Text = dgvStudents.Item(5, thisRow).Value

    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

        'enables the combo and text boxes to allow the use to edit the data
        txtFirstName.Enabled = True
        txtSurname.Enabled = True
        cmbGender.Enabled = True
        cmbForm.Enabled = True
        cmbHouse.Enabled = True
        'disables the datatable and edit button to reduce the risk of errors and the porgramme breaking
        dgvStudents.Enabled = False
        btnEdit.Enabled = False
        btnAdd.Enabled = False
        btnConfirm.Enabled = True
        btnCancel.Enabled = True


    End Sub

    Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
        'updates the database with the edited data once confirm is clicked
        SQL = "UPDATE TblStudents SET FirstName = '" & txtFirstName.Text & "', Surname = '" & txtSurname.Text & "', Gender = '" & cmbGender.Text & "', HouseName = '" & cmbHouse.Text & "', FormName = '" & cmbForm.Text & "' WHERE StudentID = " & txtStudentID.Text & ";"
        Cmd = New OleDbCommand(SQL, Con)
        Dim SQLResults As DataTable = PerformCRUD(Cmd)
        'reloads datatable to show new data
        LoadStudentData()
        'resets buttons
        CancelConfirmButtonsReset()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        'resets buttons
        CancelConfirmButtonsReset()

    End Sub

    Sub CancelConfirmButtonsReset()

        'resets form back to how it is when it is first loaded
        txtFirstName.Enabled = False
        txtSurname.Enabled = False
        cmbGender.Enabled = False
        cmbForm.Enabled = False
        cmbHouse.Enabled = False

        dgvStudents.Enabled = True
        btnEdit.Enabled = True
        btnAdd.Enabled = True
        btnConfirm.Enabled = False
        btnCancel.Enabled = False
        btnAddConfirm.Enabled = False
        btnAddCancle.Enabled = False

    End Sub

    Sub LoadStudentData()
        'pulls up all student data
        SQL = "SELECT * FROM TblStudents;"
        Cmd = New OleDbCommand(SQL, Con)
        Dim SQLResults As DataTable = PerformCRUD(Cmd)
        dgvStudents.DataSource = SQLResults

        LoadHouseSummary()

    End Sub

    Sub LoadHouseSummary()

        SQL = "SELECT HouseName, COUNT(StudentID) As NumberOfStudents FROM TblStudents GROUP BY HouseName;"
        Cmd = New OleDbCommand(SQL, Con)
        Dim HouseSummary As DataTable = PerformCRUD(Cmd)
        dgvHouseSummary.DataSource = HouseSummary

        'Clears existing chart data
        chartHouseSummary.Series.Clear()
        chartHouseSummary.Series.Add("SeriesHouseSummary")
        chartHouseSummary.Series("SeriesHouseSummary").ChartType = DataVisualization.Charting.SeriesChartType.Pie
        For i = 0 To HouseSummary.Rows.Count - 1
            chartHouseSummary.Series("SeriesHouseSummary").Points.AddXY(HouseSummary.Rows(i).Item(0), HouseSummary.Rows(i).Item(1))
        Next


    End Sub


    Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        'this gets enables and clears all of the textboxes so that the user can add a new student
        txtFirstName.Enabled = True
        txtSurname.Enabled = True
        cmbGender.Enabled = True
        cmbForm.Enabled = True
        cmbHouse.Enabled = True

        dgvStudents.Enabled = False
        btnEdit.Enabled = False
        btnAdd.Enabled = False
        btnAddConfirm.Enabled = True
        btnAddCancle.Enabled = True

        txtStudentID.Text = ""
        txtFirstName.Text = ""
        txtSurname.Text = ""
        cmbGender.Text = ""
        cmbHouse.Text = ""
        cmbForm.Text = ""




    End Sub

    Private Sub btnAddCancle_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddCancle.Click
        CancelConfirmButtonsReset()
    End Sub

    Private Sub btnAddConfirm_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddConfirm.Click
        'this SQL statement will add the new student to the database with the info that was typed in the text boxes
        SQL = "INSERT INTO TblStudents (FirstName, Surname, Gender, HouseName, FormName) VALUES ('" & txtFirstName.Text & "', '" & txtSurname.Text & "', '" & cmbGender.Text & "', '" & cmbHouse.Text & "', '" & cmbForm.Text & "');"
        Cmd = New OleDbCommand(SQL, Con)
        Dim SQLResults As DataTable = PerformCRUD(Cmd)

        LoadStudentData()

        CancelConfirmButtonsReset()
    End Sub

    Private Sub btnDeleteStudent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteStudent.Click
        'this will doeble check if the user is sure they want to delete the student and of they are it wil delete the student with the corresponding studentID to the one selected
        If txtStudentID.Text <> "" Then
            Dim UserResponse As MsgBoxResult = MsgBox("Are you sure that you want to delete record with student id: " & txtStudentID.Text, MsgBoxStyle.YesNo, "Delete Record?")
            If UserResponse = vbYes Then
                SQL = "DELETE * FROM tblStudents WHERE StudentID = " & txtStudentID.Text & ";"
                Cmd = New OleDbCommand(SQL, Con)
                Dim SQLResults As DataTable = PerformCRUD(Cmd)

                LoadStudentData()
            End If
        End If


    End Sub

    Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
        'this will compare the text typed into the search text box and bring up all of the students in the table that contain what was typed in thair first or last name
        SQL = "SELECT * FROM TblStudents WHERE (FirstName LIKE '%" & txtSearch.Text & "%') OR Surname LIKE '%" & txtSearch.Text & "%';"
        Cmd = New OleDbCommand(SQL, Con)
        Dim SQLResults As DataTable = PerformCRUD(Cmd)
        dgvStudents.DataSource = SQLResults

    End Sub

    Private Sub btnback_Click(sender As Object, e As EventArgs) Handles btnback.Click
        frmAdmin.Show()
        Me.Hide()



    End Sub
End Class