Imports System.Runtime.InteropServices
Imports MySql.Data.MySqlClient
Public Class Fault
    Dim MySqlConn As MySqlConnection
    Dim myCommand As MySqlCommand
    Dim myAdapter As MySqlDataAdapter
    Dim myData As DataTable
    Dim SQL As String
    Dim connectionString = "server=localhost; userid=root; database=database;"

    Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
        MySqlConn = New MySqlConnection()

        MySqlConn.ConnectionString = connectionString

        Try
            MySqlConn.Open()
            MessageBox.Show("Connected to database!")
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub

    Private Sub btnReturnAll_Click(sender As Object, e As EventArgs) Handles btnReturnAll.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "Select * FROM database.fault"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub

    Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
        Dim SQL As String = "INSERT INTO database.fault (FaultID, TechnicianID, ComponentID, FaultDesc, DateLogged, FaultUrgency, Resolution, CompletionDate) Values (?,?,?,?,?,?,?,?)"

        MySqlConn = New MySqlConnection()
        MySqlConn.ConnectionString() = connectionString
        MySqlConn.Open()

        myCommand = New MySqlCommand()

        myCommand.CommandText = SQL
        myCommand.Parameters.AddWithValue("FaultID", txtFaultID.Text)
        myCommand.Parameters.AddWithValue("TechnicianID", txtTechID.Text)
        myCommand.Parameters.AddWithValue("ComponentID", txtCompID.Text)
        myCommand.Parameters.AddWithValue("FaultDesc", txtfaultDesc.Text)
        myCommand.Parameters.AddWithValue("DateLogged", txtDateLogged.Text)
        myCommand.Parameters.AddWithValue("FaultUrgency", txtFaultUrgency.Text)
        myCommand.Parameters.AddWithValue("Resolution", txtResolution.Text)
        myCommand.Parameters.AddWithValue("CompletionDate", TxtCompDate.Text)

        myCommand.Connection = MySqlConn

        If txtFaultID.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtTechID.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtCompID.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtfaultDesc.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtDateLogged.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        Else
            myCommand.ExecuteNonQuery()
        End If

        If txtFaultUrgency.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtResolution.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If TxtCompDate.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        txtFaultID.Clear()
        txtTechID.Clear()
        txtCompID.Clear()
        txtfaultDesc.Clear()
        txtDateLogged.Clear()
        txtFaultUrgency.Clear()
        txtResolution.Clear()
        TxtCompDate.Clear()
    End Sub
    <DllImport("user32.dll", CharSet:=CharSet.Auto)> _
    Private Shared Function SendMessage(ByVal hWnd As IntPtr, ByVal msg As Integer,
                                        ByVal wParam As Integer, <MarshalAs(UnmanagedType.LPWStr)> ByVal lParam As String) As Int32
    End Function

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

        SendMessage(Me.txtFaultID.Handle, &H1501, 0, "ID of Fault")
        SendMessage(Me.txtTechID.Handle, &H1501, 0, "Technicians ID Number")
        SendMessage(Me.txtCompID.Handle, &H1501, 0, "Components ID Number")
        SendMessage(Me.txtfaultDesc.Handle, &H1501, 0, "Description of Fault")
        SendMessage(Me.txtDateLogged.Handle, &H1501, 0, "Date of log (DD/MM/YYYY")
        SendMessage(Me.txtFaultUrgency.Handle, &H1501, 0, "Urgency? High, low etc")
        SendMessage(Me.txtResolution.Handle, &H1501, 0, "Resolved? How?")
        SendMessage(Me.TxtCompDate.Handle, &H1501, 0, "DD/MM/YYYY")
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        mainDataGrid.DataSource = Nothing
        mainDataGrid.Rows.Clear()
        MySqlConn.Close()
        MessageBox.Show("You have been disconnected from the database")
    End Sub



    Private Sub TechnicianToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles TechnicianToolStripMenuItem1.Click
        Me.Hide()
        Technician.Show()
    End Sub

    Private Sub RetrieveTechnicianIDToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RetrieveTechnicianIDToolStripMenuItem.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "Select technicianid FROM database.technician"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub

    Private Sub RetrieveAllWithFaultPriorityHighToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RetrieveAllWithFaultPriorityHighToolStripMenuItem.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "SELECT Technician.TechnicianID, Fault.FaultUrgency FROM Technician, Fault WHERE FaultUrgency='high'"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub

    Private Sub MultipleTableQuery1ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles MultipleTableQuery1ToolStripMenuItem.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "SELECT Component.ComponentID, Fault.DateLogged FROM Component, Fault"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub

    Private Sub MultipleTableQuery2ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles MultipleTableQuery2ToolStripMenuItem.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "SELECT Technician.TechnicianEmail, Fault.FaultDesc, Fault.Resolution FROM Technician, Fault"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub
End Class
Public Class Login
    Dim MySqlConn As MySqlConnection
    Dim myCommand As MySqlCommand
    Dim myAdapter As MySqlDataAdapter
    Dim myData As DataTable
    Dim connectionString = "server=localhost; userid=root; database=database;"
    <DllImport("user32.dll", CharSet:=CharSet.Auto)> _
    Private Shared Function SendMessage(ByVal hWnd As IntPtr, ByVal msg As Integer,
                                        ByVal wParam As Integer, <MarshalAs(UnmanagedType.LPWStr)> ByVal lParam As String) As Int32
    End Function

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


        Dim IPAddress As IPHostEntry = Dns.GetHostByName(Dns.GetHostName())
        Label2.Text = "" & IPAddress.AddressList(0).ToString()
        Label1.Text = Date.Now

        MySqlConn = New MySqlConnection()

        MySqlConn.ConnectionString = connectionString

        Try
            MySqlConn.Open()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

        SendMessage(Me.TextBox1.Handle, &H1501, 0, "Username")
        SendMessage(Me.TextBox2.Handle, &H1501, 0, "Password")
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        MySqlConn = New MySqlConnection()
        MySqlConn.ConnectionString = connectionString
        Dim Reader As MySqlDataReader


        Dim LogData As String = "INSERT INTO database.log (username, ipaddress, date) Values (?,?,?)"
        MySqlConn.Open()
        myCommand = New MySqlCommand()

        myCommand.CommandText = LogData
        myCommand.Parameters.AddWithValue("username", TextBox1.Text)
        myCommand.Parameters.AddWithValue("ipaddress", Label2.Text)
        myCommand.Parameters.AddWithValue("date", Label1.Text)
        myCommand.Connection = MySqlConn
        myCommand.ExecuteNonQuery()


        Try
            Dim Query As String
            Query = "select * from database.logins where username='" & TextBox1.Text & " ' and password ='" & TextBox2.Text & "'"
            myCommand = New MySqlCommand(Query, MySqlConn)
            Reader = myCommand.ExecuteReader

            Dim Count As Integer
            Count = 0
            While Reader.Read
                Count = Count + 1
            End While

            If Count = 1 Then
                MessageBox.Show("Connection Successful")
                Me.Hide()
                Fault.Show()
            ElseIf Count > 1 Then
                MessageBox.Show("Connection is duplicated")
            Else
                MessageBox.Show("Connection unsuccessful")

            End If

            MySqlConn.Close()

        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            MySqlConn.Dispose()
        End Try
    End Sub
End Class
Imports System.Runtime.InteropServices
Imports MySql.Data.MySqlClient
Public Class Technician
    Dim MySqlConn As MySqlConnection
    Dim myCommand As MySqlCommand
    Dim myAdapter As MySqlDataAdapter
    Dim myData As DataTable
    Dim SQL As String
    Dim connectionString = "server=localhost; userid=root; database=database;"
    Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
        MySqlConn = New MySqlConnection()

        MySqlConn.ConnectionString = connectionString

        Try
            MySqlConn.Open()
            MessageBox.Show("Connected to database!")
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub
    Private Sub btnReturnAll_Click(sender As Object, e As EventArgs) Handles btnReturnAll.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "Select * FROM database.technician"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub
    Private Sub btnDisconnect_Click(sender As Object, e As EventArgs) Handles btnDisconnect.Click
        mainDataGrid.DataSource = Nothing
        mainDataGrid.Rows.Clear()
        MySqlConn.Close()
        MessageBox.Show("You have been disconnected from the database")
    End Sub

    Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
        Dim SQL As String = "INSERT INTO database.technician (TechnicianID, TechnicianFirstName, TechnicianLastName, TechnicianTel, TechnicianEmail) Values (?,?,?,?,?)"

        MySqlConn = New MySqlConnection()
        MySqlConn.ConnectionString() = connectionString
        MySqlConn.Open()

        myCommand = New MySqlCommand()

        myCommand.CommandText = SQL
        myCommand.Parameters.AddWithValue("TechnicianID", txtTechID.Text)
        myCommand.Parameters.AddWithValue("TechnicianFirstName", txtTechFirstName.Text)
        myCommand.Parameters.AddWithValue("TechnicianLastName", txtTechLastName.Text)
        myCommand.Parameters.AddWithValue("TechnicianTel", txtTechTel.Text)
        myCommand.Parameters.AddWithValue("TechnicianEmail", txtTechEmail.Text)

        myCommand.Connection = MySqlConn

        If txtTechID.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        Else
            myCommand.ExecuteNonQuery()
        End If

        If txtTechFirstName.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtTechLastName.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtTechTel.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If

        If txtTechEmail.Text = Nothing Then
            MessageBox.Show("Error... A field was not correclty filled in!")
        End If
    End Sub

    Private Sub btnClearFields_Click(sender As Object, e As EventArgs) Handles btnClearFields.Click
        txtTechID.Clear()
        txtTechFirstName.Clear()
        txtTechLastName.Clear()
        txtTechTel.Clear()
        txtTechEmail.Clear()
    End Sub


    Private Sub FaultToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles FaultToolStripMenuItem.Click
        Me.Hide()
        Fault.Show()
    End Sub


    Private Sub RetrieveTechnicianIDToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RetrieveTechnicianIDToolStripMenuItem.Click
        myAdapter = New MySqlDataAdapter
        myCommand = New MySqlCommand
        myData = New DataTable

        SQL = "Select technicianid FROM database.technician"

        myCommand.Connection = MySqlConn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myData)

        mainDataGrid.DataSource = myData
    End Sub
End Class