Public Class frmListOfCashAdvance
    Private Sub LoadData()
        FillDataComboBox("SELECT   EMPLOYEE_ID, EMP_CODE  FROM   Employee_Information  ORDER BY EMP_CODE", "EMPLOYEE_ID", "EMP_CODE", cmbEmployee)
        FillDataGrid(" SELECT        Payment.PYMT_ENROLL_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Payment.EnrollDate, Payment.Comments, Payment.CashAmount " &
                     " FROM            Payment LEFT OUTER JOIN  Employee_Information ON Payment.EMPLOYEE_ID = Employee_Information.EMPLOYEE_ID ", DataGridView1)
    End Sub

    Private Sub frmListOfCashAdvance_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
    End Sub

    Private Sub btnRefresh_Click(sender As Object, e As EventArgs) Handles btnRefresh.Click
        LoadData()
    End Sub

    Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
        If rbFilterbyEmployeeAnDate.Checked = True Then
            If cmbEmployee.SelectedValue Is Nothing Or cmbEmployee.SelectedIndex = -1 Then
                ErrorProvider1.SetError(cmbEmployee, "Mandatory")
            Else
                ErrorProvider1.Clear()
                FillDataGrid(" SELECT        Payment.PYMT_ENROLL_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Payment.EnrollDate, Payment.Comments, Payment.CashAmount " &
                             " FROM            Payment LEFT OUTER JOIN  Employee_Information ON Payment.EMPLOYEE_ID = Employee_Information.EMPLOYEE_ID " &
                             " WHERE   (Payment.EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "') AND (Payment.EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') AND (Payment.EMPLOYEE_ID = " & num_fltr(cmbEmployee.SelectedValue) & ") ", DataGridView1)
            End If
        Else
            FillDataGrid(" SELECT        Payment.PYMT_ENROLL_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Payment.EnrollDate, Payment.Comments, Payment.CashAmount " &
                         " FROM            Payment LEFT OUTER JOIN  Employee_Information ON Payment.EMPLOYEE_ID = Employee_Information.EMPLOYEE_ID " &
                         " WHERE   (Payment.EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "') AND (Payment.EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') ", DataGridView1)
        End If
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.ColumnIndex = 0 Then
            If MsgBox("Are you sure you want to edit this record?", MsgBoxStyle.YesNo + MsgBoxStyle.Exclamation, "Confirm..") = MsgBoxResult.Yes Then
                With frmCash
                    .MdiParent = frmMDIParent1
                    .Show()
                    .txtPYMT_ENROLL_ID.Text = DataGridView1.CurrentRow.Cells(2).Value

                    ExecuteSQLQuery(" SELECT        Payment.PYMT_ENROLL_ID, Employee_Information.EMP_CODE, Payment.EnrollDate, Payment.Comments, Payment.CashAmount  FROM            Payment LEFT OUTER JOIN " &
                                    " Employee_Information ON Payment.EMPLOYEE_ID = Employee_Information.EMPLOYEE_ID  WHERE        (Payment.PYMT_ENROLL_ID = " & DataGridView1.CurrentRow.Cells(2).Value & ") ")
                    If sqlDT.Rows.Count > 0 Then
                        .txtEmployeesCode.Text = sqlDT.Rows(0)("EMP_CODE")
                        .txtAmount.Text = sqlDT.Rows(0)("CashAmount")
                        .txtComment.Text = sqlDT.Rows(0)("Comments")
                        .dtpEnrollDate.Value = sqlDT.Rows(0)("EnrollDate")
                    End If
                    .btnSearch.PerformClick()
                    .btnSubmit.Text = "UPDATE"
                End With
            End If
        ElseIf e.ColumnIndex = 1 Then
            If MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.YesNo + MsgBoxStyle.Exclamation, "Confirm..") = MsgBoxResult.Yes Then
                ExecuteSQLQuery("DELETE FROM Payment WHERE  PYMT_ENROLL_ID =" & DataGridView1.CurrentRow.Cells(2).Value & " ")
                LoadData()
                MsgBox("Record deleted successfully.", MsgBoxStyle.Information, "Information")
            End If
        End If
    End Sub
End Class