Public Class payment
    Public citem1 As String
    Public loc_fee_id As String
    Private Sub tbox_studid_TextChanged(sender As Object, e As EventArgs) Handles tbox_studid.TextChanged
        stud_id = tbox_studid.Text
        Dim conn = New OleDb.OleDbConnection(dbSource)
        Dim sql As String = "SELECT * FROM member WHERE stud_id LIKE '%" & stud_id & "%'"
        Dim dAdapter As OleDb.OleDbDataAdapter
        Dim dSet As New DataSet
        conn.Open()
        dAdapter = New OleDb.OleDbDataAdapter(sql, conn)
        dAdapter.Fill(dSet, "member")
        conn.Close()
        dAdapter.Dispose()
        If stud_id.Length.Equals(8) Then
            If dSet.Tables(0).Rows.Count > 0 Then
                tbox_firstname.Text = dSet.Tables(0).Rows(0).Item(1).ToString
                tbox_lastname.Text = dSet.Tables(0).Rows(0).Item(3).ToString
                cbox_status.Enabled = True
            Else
                tbox_firstname.Clear()
                tbox_lastname.Clear()
                cbox_status.Enabled = False
                ComboBox1.Enabled = False
                cbox_status.Text = ""
                ComboBox1.Text = ""
                tbox_price.Text = ""
                tbox_sem.Text = ""
                tbox_year.Text = ""
                tbox_balance.Visible = False
                lbl_balance.Visible = False
                tbox_balance.Text = ""
            End If
        Else
            tbox_firstname.Clear()
            tbox_lastname.Clear()
            ComboBox1.Enabled = False
            cbox_status.Enabled = False
            cbox_status.Text = ""
            ComboBox1.Text = ""
            tbox_price.Text = ""
            tbox_sem.Text = ""
            tbox_year.Text = ""
            tbox_balance.Text = ""
            tbox_balance.Visible = False
            lbl_balance.Visible = False
        End If
    End Sub

    Private Sub tbox_studid_KeyPress(sender As Object, e As KeyPressEventArgs) Handles tbox_studid.KeyPress
        If Asc(e.KeyChar) <> 13 AndAlso Asc(e.KeyChar) <> 8 AndAlso Not IsNumeric(e.KeyChar) Then
            MessageBox.Show("Please enter numbers only", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            e.Handled = True
        End If
    End Sub


    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sql As String = "SELECT * FROM fee WHERE detail = '" & ComboBox1.Text & "'"
        Dim conn = New OleDb.OleDbConnection(dbSource)
        Dim dAdapter As OleDb.OleDbDataAdapter
        Dim dSet As New DataSet
        conn.Open()
        dAdapter = New OleDb.OleDbDataAdapter(sql, conn)
        dAdapter.Fill(dSet, "fee")
        conn.Close()
        dAdapter.Dispose()
        tbox_price.Text = dSet.Tables(0).Rows(0).Item(2).ToString
        loc_fee_id = dSet.Tables(0).Rows(0).Item(0).ToString
        tbox_year.Text = dSet.Tables(0).Rows(0).Item(3).ToString
        tbox_sem.Text = dSet.Tables(0).Rows(0).Item(4).ToString
        If cbox_status.Text = "Balance" Then
            tbox_balance.Visible = True
            lbl_balance.Visible = True
            Dim sql1 As String = "SELECT * FROM [transaction] WHERE mem_id = '" & stud_id & "' And status = 'Balance' And transact_details = " & loc_fee_id
            Dim conn1 = New OleDb.OleDbConnection(dbSource)
            Dim dAdapter1 As OleDb.OleDbDataAdapter
            Dim dSet1 As New DataSet
            Try
                conn.Open()
                dAdapter1 = New OleDb.OleDbDataAdapter(sql1, conn1)
                dAdapter1.Fill(dSet1, "[transaction]")
                dAdapter1.Dispose()
                tbox_balance.Text = dSet1.Tables(0).Rows(0).Item(4).ToString
            Catch ex As Exception
                MessageBox.Show("Error", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                conn1.Close()
            End Try
        Else
            tbox_balance.Text = ""
            tbox_balance.Visible = False
            lbl_balance.Visible = False
        End If

    End Sub

    Private Sub tbox_pay_KeyPress(sender As Object, e As KeyPressEventArgs) Handles tbox_pay.KeyPress
        If Asc(e.KeyChar) <> 13 AndAlso Asc(e.KeyChar) <> 8 AndAlso Not IsNumeric(e.KeyChar) Then
            MessageBox.Show("Please enter numbers only", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            e.Handled = True
        End If
    End Sub

    Private Sub btn_confirm_Click(sender As Object, e As EventArgs) Handles btn_confirm.Click
        If tbox_studid.Text = "" Or ComboBox1.Text = "" Or tbox_pay.Text = "" Then
            MessageBox.Show("Please Complete the required field", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Else
            If MessageBox.Show("Are you sure you want to pay this?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.Yes Then
                    If cbox_status.Text = "Balance" Then
                        Dim stud_id, price, pay, total_val, status As String
                        stud_id = tbox_studid.Text
                        price = tbox_price.Text
                        pay = tbox_pay.Text
                        Dim cmd As OleDb.OleDbCommand
                        Dim sql As String = "SELECT * FROM [transaction] WHERE mem_id = '" & stud_id & "' And status = 'Balance' And transact_details = " & loc_fee_id
                        Dim dAdapter As OleDb.OleDbDataAdapter
                        Dim dSet As New DataSet
                        Dim conn = New OleDb.OleDbConnection(dbSource)
                        conn.Open()
                        dAdapter = New OleDb.OleDbDataAdapter(sql, conn)
                        dAdapter.Fill(dSet, "[transaction]")
                        conn.Close()
                        Dim transact_id As String = dSet.Tables(0).Rows(0).Item(0).ToString
                        Dim balance As String = dSet.Tables(0).Rows(0).Item(4).ToString
                        dAdapter.Dispose()
                        total_val = balance - pay
                        If total_val <= 0 Then
                            total_val = 0
                            status = "Paid"
                        Else
                            total_val = total_val
                            status = "Balance"
                        End If
                        Dim sql1 As String = "UPDATE [transaction] SET balance = '" & total_val & "', status = '" & status & "' WHERE transact_id = " & transact_id
                        Try
                            conn.Open()
                            cmd = New OleDb.OleDbCommand(sql1, conn)
                            cmd.ExecuteNonQuery()
                            cmd.Dispose()
                            MessageBox.Show("Successfully Process a transaction", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                            tbox_studid.Text = ""
                            ComboBox1.Text = ""
                            tbox_price.Text = ""
                            tbox_sem.Text = ""
                            tbox_year.Text = ""
                            tbox_pay.Text = ""
                        Catch ex As Exception
                            MessageBox.Show("Error", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Finally
                            conn.Close()
                        End Try
                Else
                    Dim conn1 = New OleDb.OleDbConnection(dbSource)
                    Dim dAdapter1 As OleDb.OleDbDataAdapter
                    Dim dSet1 As New DataSet
                    Dim sql2 As String = "SELECT * FROM [transaction] WHERE mem_id = '" & stud_id & "' And transact_details = " & loc_fee_id
                    conn1.Open()
                    dAdapter1 = New OleDb.OleDbDataAdapter(sql2, conn1)
                    dAdapter1.Fill(dSet1, "transaction")
                    If dSet1.Tables(0).Rows.Count > 0 Then
                        MessageBox.Show("Account is already on process!", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                        tbox_studid.Text = ""
                        tbox_price.Text = ""
                        tbox_sem.Text = ""
                        tbox_year.Text = ""
                        tbox_pay.Text = ""
                        conn1.Close()
                    Else
                        If cbox_status.Text = "Available" Then
                            Dim stud_id, price, pay, total_val, status As String
                            stud_id = tbox_studid.Text
                            price = tbox_price.Text
                            pay = tbox_pay.Text
                            total_val = price - pay
                            If total_val <= 0 Then
                                total_val = 0
                                status = "Paid"
                            Else
                                total_val = total_val
                                status = "Balance"
                            End If
                            Dim cmd As OleDb.OleDbCommand
                            Dim sql As String = "INSERT INTO [transaction] (mem_id, transact_details, price, [balance], [status]) VALUES('" & stud_id & "', '" & loc_fee_id & "', '" & price & "', '" & total_val & "', '" & status & "')"
                            Dim conn = New OleDb.OleDbConnection(dbSource)
                            Try
                                conn.Open()
                                cmd = New OleDb.OleDbCommand(sql, conn)
                                cmd.ExecuteNonQuery()
                                cmd.Dispose()
                                MessageBox.Show("Successfully Process a transaction", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                                tbox_studid.Text = ""
                                ComboBox1.Text = ""
                                tbox_price.Text = ""
                                tbox_sem.Text = ""
                                tbox_year.Text = ""
                                tbox_pay.Text = ""
                            Catch ex As Exception
                                MessageBox.Show("Error", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            Finally
                                conn.Close()
                            End Try
                        End If
                    End If
                End If
            End If
        End If
    End Sub

    Private Sub cbox_status_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbox_status.SelectedIndexChanged
        If cbox_status.Text = "" Then
            ComboBox1.Enabled = False
            tbox_balance.Visible = False
            lbl_balance.Visible = False
            tbox_balance.Text = ""
        Else
            ComboBox1.Enabled = True
            Dim status As String = cbox_status.Text
            If cbox_status.Text = "Available" Then
                status = ""
                tbox_balance.Visible = False
                lbl_balance.Visible = False
                ComboBox1.Items.Clear()
                ComboBox1.Text = ""
                tbox_price.Text = ""
                tbox_sem.Text = ""
                tbox_year.Text = ""
                Dim sql1 As String = "SELECT * FROM fee "
                Dim conn1 = New OleDb.OleDbConnection(dbSource)
                Dim data_read As OleDb.OleDbDataReader
                Dim cmd As OleDb.OleDbCommand
                conn1.Open()
                cmd = New OleDb.OleDbCommand(sql1, conn1)
                data_read = cmd.ExecuteReader()
                If data_read.HasRows Then
                    While data_read.Read
                        Dim citem = ComboBox1.Text
                        citem = data_read.GetValue(1).ToString
                        ComboBox1.Items.Add(citem)
                    End While
                End If
                conn1.Close()
                cmd.Dispose()
            Else
                If cbox_status.Text = "Balance" Then
                    status = "Balance"
                    ComboBox1.Items.Clear()
                    ComboBox1.Text = ""
                    tbox_price.Text = ""
                    tbox_sem.Text = ""
                    tbox_year.Text = ""
                    Dim sql2 As String = "SELECT * FROM [transaction] WHERE mem_id = '" & stud_id & "'"
                    Dim conn2 = New OleDb.OleDbConnection(dbSource)
                    Dim dAdapter1 As OleDb.OleDbDataAdapter
                    Dim dSet1 As New DataSet
                    conn2.Open()
                    dAdapter1 = New OleDb.OleDbDataAdapter(sql2, conn2)
                    dAdapter1.Fill(dSet1, "transaction")
                    conn2.Close()
                    If dSet1.Tables(0).Rows.Count > 0 Then
                        Dim sql1 As String = "SELECT * FROM [transaction] INNER JOIN fee ON transaction.transact_details = fee.fee_id WHERE status = '" & status & "' "
                        Dim conn1 = New OleDb.OleDbConnection(dbSource)
                        Dim data_read As OleDb.OleDbDataReader
                        Dim cmd As OleDb.OleDbCommand
                        conn1.Open()
                        cmd = New OleDb.OleDbCommand(sql1, conn1)
                        data_read = cmd.ExecuteReader
                        If data_read.HasRows Then
                            While data_read.Read
                                Dim citem = ComboBox1.Text
                                citem = data_read.GetValue(7).ToString
                                ComboBox1.Items.Add(citem)
                            End While
                        End If
                        conn1.Close()
                        cmd.Dispose()
                    End If
                End If
            End If
            
        End If
    End Sub

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

    End Sub
End Class