Imports MySql.Data.Types
Imports MySql.Data.MySqlClient
Public Class Form4

    Dim ie, pr As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim issCurrentIndex As Integer = 0
    Dim iss, pre As New MySqlDataAdapter()
    Dim conn As New MySqlConnection("server=localhost" & ";user id=" & login.txtUsername.Text & "; password=" & login.txtPassword.Text & "; port=3306; database=test; pooling=false")

    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        IssueSQL()
        PrescriberSQL()
    End Sub

    Private Sub PrescriberField()
        txtprename.Text = pr.Tables(0).Rows(intCurrentIndex).Item("Name").ToString()
        txtLocation.Text = pr.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
        txtPhone.Text = pr.Tables(0).Rows(intCurrentIndex).Item("Telephone").ToString()
        txtpreid.Text = pr.Tables(0).Rows(intCurrentIndex).Item("Prescriber_ID").ToString()
    End Sub

    Private Sub IssueField()
        txtIn_Stock.Text = ie.Tables(0).Rows(issCurrentIndex).Item("In_Stock").ToString()
        txtamountgiven.Text = ie.Tables(0).Rows(issCurrentIndex).Item("Amount_Given").ToString()
        txtExpiry.Text = ie.Tables(0).Rows(issCurrentIndex).Item("Expiry").ToString()
        TxtWarning.Text = ie.Tables(0).Rows(issCurrentIndex).Item("Warning").ToString()
        txtdrug.Text = ie.Tables(0).Rows(issCurrentIndex).Item("Drug").ToString()
        txtdose.Text = ie.Tables(0).Rows(issCurrentIndex).Item("Dose").ToString()
        txtIssueId.Text = ie.Tables(0).Rows(issCurrentIndex).Item("MedicationID").ToString()

    End Sub

    Public Sub PrescriberSQL()
        pre.SelectCommand = New MySqlCommand("SELECT * FROM Prescriber")
        pre.SelectCommand.Connection = conn


        pre.UpdateCommand = New MySqlCommand("Update Prescriber SET Name =?Name, Location =?Location, Telephone =?Telephone WHERE Prescriber_ID = ?Prescriber_ID")
        pre.UpdateCommand.Connection = conn
        pre.UpdateCommand.Parameters.Add("?Name", MySqlDbType.VarChar, 50, "Name")
        pre.UpdateCommand.Parameters.Add("?Location", MySqlDbType.VarChar, 70, "Location")
        pre.UpdateCommand.Parameters.Add("?Telephone", MySqlDbType.VarChar, 70, "Telephone")
        pre.UpdateCommand.Parameters.Add("?Prescriber_ID", MySqlDbType.Int16, 5, "Prescriber_ID")

        pre.InsertCommand = New MySqlCommand("INSERT INTO Prescriber(Prescriber_ID, Name , Location, Telephone) VALUES(?Prescriber_ID,?Name ,?Location, ?Telephone)")
        pre.InsertCommand.Connection = conn
        pre.InsertCommand.Parameters.Add("?Name", MySqlDbType.VarChar, 50, "Name")
        pre.InsertCommand.Parameters.Add("?Location", MySqlDbType.VarChar, 70, "Location")
        pre.InsertCommand.Parameters.Add("?Telephone", MySqlDbType.VarChar, 70, "Telephone")
        pre.InsertCommand.Parameters.Add("?Prescriber_ID", MySqlDbType.Int16, 5, "Prescriber_ID")


        pre.DeleteCommand = New MySqlCommand("DELETE FROM Prescriber WHERE Prescriber_ID = ?Prescriber_ID")
        pre.DeleteCommand.Connection = conn
        pre.DeleteCommand.Parameters.Add("?Prescriber_ID", MySqlDbType.Int16, 5, "Prescriber_ID")

        pre.Fill(pr)

        If pr.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            PrescriberField()
        End If
    End Sub

    Public Sub IssueSQL()
        iss.SelectCommand = New MySqlCommand("SELECT * FROM Medication")
        iss.SelectCommand.Connection = conn


        iss.UpdateCommand = New MySqlCommand("Update Medication SET In_Stock =?In_Stock, Amount_Given =?Amount_Given, Expiry =?Expiry, Warning =?Warning, Drug =?Drug, Dose =?Dose   WHERE MedicationID = ?MedicationID")
        iss.UpdateCommand.Connection = conn
        iss.UpdateCommand.Parameters.Add("?In_Stock", MySqlDbType.VarChar, 40, "In_Stock")
        iss.UpdateCommand.Parameters.Add("?Amount_Given", MySqlDbType.VarChar, 40, "Amount_Given")
        iss.UpdateCommand.Parameters.Add("?Expiry", MySqlDbType.VarChar, 70, "Expiry")
        iss.UpdateCommand.Parameters.Add("?Warning", MySqlDbType.VarChar, 70, "Warning")
        iss.UpdateCommand.Parameters.Add("?Drug", MySqlDbType.VarChar, 70, "Drug")
        iss.UpdateCommand.Parameters.Add("?Dose", MySqlDbType.VarChar, 70, "Dose")
        iss.UpdateCommand.Parameters.Add("?MedicationID", MySqlDbType.Int16, 5, "MedicationID")



        iss.InsertCommand = New MySqlCommand("INSERT INTO Medication(MedicationID, Amount_Given, Expiry, Warning, Drug , Dose) VALUES(?MedicationID,?Amount_Given, ?Expiry, Warning, ?Drug ,?Dose)")
        iss.InsertCommand.Connection = conn
        iss.InsertCommand.Parameters.Add("?In_Stock", MySqlDbType.VarChar, 50, "In_Stock")
        iss.InsertCommand.Parameters.Add("?Amount_Given", MySqlDbType.VarChar, 70, "Amount_Given")
        iss.InsertCommand.Parameters.Add("?Expiry", MySqlDbType.VarChar, 70, "Expiry")
        iss.InsertCommand.Parameters.Add("?Warning", MySqlDbType.VarChar, 70, "Warning")
        iss.InsertCommand.Parameters.Add("?Drug", MySqlDbType.VarChar, 70, "Drug")
        iss.InsertCommand.Parameters.Add("?Dose", MySqlDbType.VarChar, 70, "Dose")
        iss.InsertCommand.Parameters.Add("?MedicationID", MySqlDbType.Int16, 5, "MedicationID")


        iss.DeleteCommand = New MySqlCommand("DELETE FROM Medication WHERE MedicationID = ?MedicationID")
        iss.DeleteCommand.Connection = conn
        iss.DeleteCommand.Parameters.Add("?MedicationID", MySqlDbType.Int16, 5, "MedicationID")

        iss.Fill(ie)

        If ie.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            IssueField()
        End If
    End Sub


    Private Sub IssueUpdate()
        Dim dr As DataRow
        dr = ie.Tables(0).Rows(issCurrentIndex) 'This gets a reference to the row currently being edited
        dr.BeginEdit()
        dr("Drug") = txtDrug.Text
        dr("Dose") = txtDose.Text
        dr("Issue_ID") = txtissueid.Text
        dr.EndEdit()
        iss.Update(ie)  'Ask the pattaapatpter to call the UppatteCommand and uppatte the pattabase
        ie.AcceptChanges() 'Commits the change to the pattaset.
    End Sub

    Private Sub IssueDelete()
        Dim button As DialogResult

        button = MessageBox.Show _
        ("Are you sure you want to delete this patient?", _
        "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)

        If button = Windows.Forms.DialogResult.Yes Then
            Try
                Dim dr As DataRow
                dr = ie.Tables(0).Rows(issCurrentIndex)
                dr.Delete() 'Delete the row
                iss.Update(ie)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                ie.AcceptChanges()
                IssueField()
            End Try
        Else
            'Do Nothing
        End If

    End Sub
    Private Sub IssueAdd()
        Dim dr As DataRow
        dr = ie.Tables(0).NewRow()  'Gets a reference to a new row.
        dr("Drug") = txtDrug.Text
        dr("Dose") = txtDose.Text
        dr("MedicationID") = txtIssueId.Text
        ie.Tables(0).Rows.Add(dr)
        iss.Update(ie)
        ie.AcceptChanges()
    End Sub


    Private Sub PrescriberUpdate()
        Dim dr As DataRow
        dr = pr.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
        dr.BeginEdit()
        dr("Name") = txtprename.Text
        dr("Location") = txtLocation.Text
        dr("Telephone") = txtPhone.Text
        dr("Prescriber_ID") = txtpreid.Text
        dr.EndEdit()
        pre.Update(pr)  'Ask the pattaapatpter to call the UppatteCommand and uppatte the pattabase
        pr.AcceptChanges() 'Commits the change to the pattaset.
    End Sub

    Private Sub PrescriberDelete()
        Dim button As DialogResult

        button = MessageBox.Show _
        ("Are you sure you want to delete this patient?", _
        "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)

        If button = Windows.Forms.DialogResult.Yes Then
            Try
                Dim dr As DataRow
                dr = pr.Tables(0).Rows(intCurrentIndex)
                dr.Delete() 'Delete the row
                pre.Update(pr)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                pr.AcceptChanges()
                PrescriberField()
            End Try
        Else
            'Do Nothing
        End If

    End Sub
    Private Sub PrescriberAdd()
        Dim dr As DataRow
        dr = pr.Tables(0).NewRow()  'Gets a reference to a new row.
        dr("Name") = txtprename.Text
        dr("Location") = txtLocation.Text
        dr("Telephone") = txtPhone.Text
        dr("Prescriber_ID") = txtpreid.Text
        pr.Tables(0).Rows.Add(dr)
        pre.Update(pr)
        pr.AcceptChanges()
    End Sub

    Private Sub PrescriberSearch()
            pre.SelectCommand = New MySqlCommand("SELECT * FROM Prescriber WHERE Name = ?Name")
        pre.SelectCommand.Connection = conn
        pre.SelectCommand.Parameters.Add("?Name", MySqlDbType.VarChar, 50, "Name")
        pre.SelectCommand.Parameters.Add("?Location", MySqlDbType.VarChar, 70, "Location")
        pre.SelectCommand.Parameters.Add("?Telephone", MySqlDbType.VarChar, 70, "Telephone")
        pre.SelectCommand.Parameters.Add("?Prescriber_ID", MySqlDbType.Int16, 5, "Prescriber_ID")

    pre.Fill(pr)
    End Sub

    Private Sub btnissfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnissfirst.Click
        issCurrentIndex = 0
        IssueField()
    End Sub

    Private Sub btnissprevious_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnissprevious.Click
        If issCurrentIndex > 0 Then
            issCurrentIndex = issCurrentIndex - 1
            IssueField()
        Else
            MessageBox.Show("You're already at the first record.")
        End If
    End Sub

    Private Sub btnissLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnisslast.Click
        issCurrentIndex = ie.Tables(0).Rows.Count - 1
        IssueField()
    End Sub

    Private Sub btnissnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnissnext.Click
        If issCurrentIndex < ie.Tables(0).Rows.Count - 1 Then
            issCurrentIndex = issCurrentIndex + 1
            IssueField()
        Else
            MessageBox.Show("You're already at the last record.")
        End If
    End Sub

    Private Sub btnissadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnissadd.Click
        IssueAdd()
    End Sub

    Private Sub btnissupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnissupdate.Click
        IssueUpdate()
        IssueField()
    End Sub

    Private Sub btnissdelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnissdelete.Click
        IssueDelete()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        'Since 0 is the first row
        intCurrentIndex = 0
        PrescriberField()

    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

        If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
            intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
            PrescriberField()

        Else
            MessageBox.Show("You're already at the first record.")
        End If

    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If intCurrentIndex < pr.Tables(0).Rows.Count - 1 Then 'pa.Tables(0).Rows.Count - 1 is the index for the last row
            intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
            PrescriberField()

        Else
            MessageBox.Show("You're already at the last record.")
        End If
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        intCurrentIndex = pr.Tables(0).Rows.Count - 1 'pa.Tables(0).Rows.Count - 1 is the index for the last row
        PrescriberField()

    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        PrescriberUpdate()
        PrescriberField()

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        PrescriberAdd()

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        PrescriberDelete()

    End Sub

    Private Sub txtRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRefresh.Click
        PrescriberField()
        IssueField()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    PrescriberSearch
    End Sub
End Class