Imports System.IO
Public Class frmEmployeeList
    Private Sub frmEmployeeList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadData()
    End Sub

    Private Sub LoadData()
        sqlSTR = " SELECT        Employee_Information.EMPLOYEE_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Employee_Information.FathersName, Employee_Information.Address, Employee_Information.PhoneNumber, " &
                     " Employee_Information.MobileNumber, Employee_Information.DateOfBirth, Employee_Information.PassportNo, Employee_Information.Gender, Employee_Information.MaritalStatus, Employee_Information.JobTitle, " &
                     " Department.DepartmentName, Designation.DesignationName, WorkingHours.ShiftName, Employee_Information.DateOfCommencement, Employee_Information.ACTIVE_ACCOUNT, Employee_Information.BasicSalary FROM            Employee_Information LEFT OUTER JOIN " &
                     " WorkingHours ON Employee_Information.ShiftID = WorkingHours.ShiftID LEFT OUTER JOIN Designation ON Employee_Information.DESG_ID = Designation.DESG_ID LEFT OUTER JOIN  Department ON Employee_Information.DEP_ID = Department.DEP_ID "
        FillDataGrid(sqlSTR, DataGridView1)
        ExecuteSQLQuery(sqlSTR)
        If sqlDT.Rows.Count > 0 Then
            lblCount.Text = sqlDT.Rows.Count.ToString & " Record Found."
        Else
            lblCount.Text = "No Record Found."
        End If
    End Sub

    Private Sub txtCriteria_TextChanged(sender As Object, e As EventArgs) Handles txtCriteria.TextChanged
        If cmbParameter.Text = "" Or txtCriteria.Text = "" Then
            ErrorProvider1.SetError(cmbParameter, "Mandatory field.")
            ErrorProvider1.SetError(txtCriteria, "Mandatory field.")
            LoadData()
        Else
            ErrorProvider1.Clear()
            sqlSTR = " SELECT        Employee_Information.EMPLOYEE_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Employee_Information.FathersName, Employee_Information.Address, Employee_Information.PhoneNumber, " &
                         " Employee_Information.MobileNumber, Employee_Information.DateOfBirth, Employee_Information.PassportNo, Employee_Information.Gender, Employee_Information.MaritalStatus, Employee_Information.JobTitle, " &
                         " Department.DepartmentName, Designation.DesignationName, WorkingHours.ShiftName, Employee_Information.DateOfCommencement, Employee_Information.ACTIVE_ACCOUNT, Employee_Information.BasicSalary FROM            Employee_Information LEFT OUTER JOIN " &
                         " WorkingHours ON Employee_Information.ShiftID = WorkingHours.ShiftID LEFT OUTER JOIN Designation ON Employee_Information.DESG_ID = Designation.DESG_ID LEFT OUTER JOIN  Department ON Employee_Information.DEP_ID = Department.DEP_ID " &
                         " WHERE   (Employee_Information." + cmbParameter.Text + " LIKE '%" + txtCriteria.Text + "%') ORDER BY Employee_Information.EMPLOYEE_ID"
            FillDataGrid(sqlSTR, DataGridView1)
            ExecuteSQLQuery(sqlSTR)
            If sqlDT.Rows.Count > 0 Then
                lblCount.Text = sqlDT.Rows.Count.ToString & " Record Found."
            Else
                lblCount.Text = "No Record Found."
            End If
        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 frmEmployeeDetails1
                    .MdiParent = frmMDIParent
                    .Show()
                    ExecuteSQLQuery(" SELECT * FROM Employee_Information WHERE  EMPLOYEE_ID =" & DataGridView1.CurrentRow.Cells(2).Value & " ")
                    If sqlDT.Rows.Count > 0 Then
                        If sqlDT.Rows(0)("ACTIVE_ACCOUNT") = "Y" Then
                            .chkActive.Checked = True
                        Else
                            .chkActive.Checked = False
                        End If
                        .txtEmpID.Text = sqlDT.Rows(0)("EMPLOYEE_ID")
                        .txtEmployeesCode.Text = sqlDT.Rows(0)("EMP_CODE")
                        .txtEmployeesName.Text = sqlDT.Rows(0)("EmployeesName")
                        .txtFathersName.Text = sqlDT.Rows(0)("FathersName")
                        .txtAddress.Text = sqlDT.Rows(0)("Address")
                        .txtPhoneNumber.Text = sqlDT.Rows(0)("PhoneNumber")
                        .txtMobileNumber.Text = sqlDT.Rows(0)("MobileNumber")
                        .dtpDateOfBirth.Value = sqlDT.Rows(0)("DateOfBirth")
                        .txtPassportNo.Text = sqlDT.Rows(0)("PassportNo")
                        .cmbGender.Text = sqlDT.Rows(0)("Gender")
                        .cmbMaritalStatus.Text = sqlDT.Rows(0)("MaritalStatus")
                        .txtJobTitle.Text = sqlDT.Rows(0)("JobTitle")
                        .cmbDepartment.SelectedValue = sqlDT.Rows(0)("DEP_ID")
                        .cmbDesignation.SelectedValue = sqlDT.Rows(0)("DESG_ID")
                        .cmbShift.SelectedValue = sqlDT.Rows(0)("ShiftID")
                        .dtpDateOfCommencement.Value = sqlDT.Rows(0)("DateOfCommencement")
                        .txtBasicSalary.Text = Decrypt(Base64Decode(sqlDT.Rows(0)("BasicSalary")))
                        Try
                            Dim arrImage1() As Byte
                            arrImage1 = sqlDT.Rows(0)("PHOTO_FILE_NAME")
                            Dim mstream As New System.IO.MemoryStream(arrImage1)
                            .PictureBox1.Image = Image.FromStream(mstream)

                            '.PictureBox1.Image = Application.StartupPath + "\Upload\Employee\" + sqlDT.Rows(0)("PHOTO_FILE_NAME")
                        Catch ex As Exception

                        End Try
                        .btnSave.Text = "UPDATE"
                    End If

                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 Employee_Information WHERE  EMPLOYEE_ID =" & DataGridView1.CurrentRow.Cells(2).Value & " ")
                LoadData()
                MsgBox("Record deleted successfully.", MsgBoxStyle.Information, "Information")
                If MsgBox("do you want to delete the application access linked with is employee?", MsgBoxStyle.YesNo + MsgBoxStyle.Exclamation, "Confirm..") = MsgBoxResult.Yes Then
                    ExecuteSQLQuery("DELETE FROM applicationuser WHERE  EMPLOYEE_ID =" & DataGridView1.CurrentRow.Cells(2).Value & " ")
                    LoadData()
                    MsgBox("Record deleted successfully.", MsgBoxStyle.Information, "Information")
                End If
            End If

        End If
    End Sub

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