

Public Class frmDailyAttendance
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub
    Private Sub LoadDailyAttendance()
        sqlSTR = " SELECT        Attendance.ATTN_ID, Attendance.AttendanceDate, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, WorkingHours.ShiftName, Attendance.TimeIn, Attendance.TimeOut FROM            WorkingHours LEFT OUTER JOIN " &
                 " Employee_Information ON WorkingHours.ShiftID = Employee_Information.ShiftID RIGHT OUTER JOIN Attendance ON Employee_Information.EMPLOYEE_ID = Attendance.EMPLOYEE_ID " &
                 " WHERE        (Attendance.AttendanceDate >= '" & Format(Now, "yyyy-MM-dd") & "' AND Attendance.AttendanceDate <= '" & Format(Now, "yyyy-MM-dd") & "') ORDER BY Attendance.ATTN_ID DESC "
        FillDataGrid(sqlSTR, DataGridView1)
    End Sub
    Private Sub CriteriaType()
        LoadDailyAttendance()
        If rbEmployeeCode.Checked = True Then
            lblCriteria.Text = "EMP. CODE"
            txtCriteria.Text = ""
            txtCriteria.Select()
        ElseIf rbFingerprint.Checked = True Then
            lblCriteria.Text = "Finger Hash Code"
            txtCriteria.Text = ""
            txtCriteria.Select()
        ElseIf rbAttendanceCard.Checked = True Then
            lblCriteria.Text = "Card Number"
            txtCriteria.Text = ""
            txtCriteria.Select()
        End If
    End Sub

    Private Sub rbEmployeeCode_CheckedChanged(sender As Object, e As EventArgs) Handles rbEmployeeCode.CheckedChanged
        CriteriaType()
    End Sub

    Private Sub rbFingerprint_CheckedChanged(sender As Object, e As EventArgs) Handles rbFingerprint.CheckedChanged
        CriteriaType()
    End Sub

    Private Sub rbAttendanceCard_CheckedChanged(sender As Object, e As EventArgs) Handles rbAttendanceCard.CheckedChanged
        CriteriaType()
    End Sub
    Private Sub frmDailyAttendance_Load(sender As Object, e As EventArgs) Handles Me.Load
        txtCriteria.Select()
        LoadDailyAttendance()
    End Sub

    Private Sub txtCriteria_TextChanged(sender As Object, e As EventArgs) Handles txtCriteria.TextChanged
        If txtCriteria.Text = "" Then
            txtEmpID.Text = ""
            txtEmployeesName.Text = ""
            txtFathersName.Text = ""
            txtShiftName.Text = ""
            LoadDailyAttendance()
            PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
        End If
    End Sub

    Private Sub txtCriteria_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtCriteria.KeyPress
        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Enter) Then
            btnFind.PerformClick()
        End If
    End Sub

    Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
        LoadDailyAttendance()
        If txtCriteria.Text = "" Then
            ErrorProvider1.SetError(txtCriteria, "Mandatory field.")
        Else
            If rbEmployeeCode.Checked = True Then
                ErrorProvider1.Clear()
                ExecuteSQLQuery(" SELECT        Employee_Information.EMPLOYEE_ID, Employee_Information.EmployeesName, Employee_Information.FathersName, WorkingHours.ShiftName, Employee_Information.PHOTO_FILE_NAME " &
                                " FROM            Employee_Information LEFT OUTER JOIN  WorkingHours ON Employee_Information.ShiftID = WorkingHours.ShiftID  WHERE  (Employee_Information.EMP_CODE LIKE '" + str_repl(txtCriteria.Text) + "') ")
                If sqlDT.Rows.Count > 0 Then
                    txtEmpID.Text = sqlDT.Rows(0)("EMPLOYEE_ID")
                    txtEmployeesName.Text = sqlDT.Rows(0)("EmployeesName")
                    txtFathersName.Text = sqlDT.Rows(0)("FathersName")
                    txtShiftName.Text = sqlDT.Rows(0)("ShiftName")
                    Try
                        PictureBox1.ImageLocation = Application.StartupPath + "\Upload\Employee\" + sqlDT.Rows(0)("PHOTO_FILE_NAME")
                    Catch ex As Exception
                        PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
                    End Try
                Else
                    txtEmpID.Text = ""
                    txtEmployeesName.Text = ""
                    txtFathersName.Text = ""
                    txtShiftName.Text = ""
                    PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
                    MsgBox("No Record Found.", MsgBoxStyle.Information, "Information")
                End If
            ElseIf rbFingerprint.Checked = True Then
                ErrorProvider1.Clear()
                ExecuteSQLQuery(" SELECT        Employee_Information.EMPLOYEE_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Employee_Information.FathersName, WorkingHours.ShiftName, Employee_Information.PHOTO_FILE_NAME,  " &
                                " Employee_Fingerprint.FingerHashCode  FROM            Employee_Information LEFT OUTER JOIN WorkingHours ON Employee_Information.ShiftID = WorkingHours.ShiftID LEFT OUTER JOIN " &
                                " Employee_Fingerprint ON Employee_Information.EMPLOYEE_ID = Employee_Fingerprint.EMPLOYEE_ID   WHERE  (Employee_Fingerprint.FingerHashCode LIKE '" + str_repl(txtCriteria.Text) + "') ")
                If sqlDT.Rows.Count > 0 Then
                    txtEmpID.Text = sqlDT.Rows(0)("EMPLOYEE_ID")
                    txtEmployeesName.Text = sqlDT.Rows(0)("EmployeesName")
                    txtFathersName.Text = sqlDT.Rows(0)("FathersName")
                    txtShiftName.Text = sqlDT.Rows(0)("ShiftName")
                    Try
                        PictureBox1.ImageLocation = Application.StartupPath + "\Upload\Employee\" + sqlDT.Rows(0)("PHOTO_FILE_NAME")
                    Catch ex As Exception
                        PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
                    End Try
                Else
                    txtEmpID.Text = ""
                    txtEmployeesName.Text = ""
                    txtFathersName.Text = ""
                    txtShiftName.Text = ""
                    PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
                    MsgBox("No Record Found.", MsgBoxStyle.Information, "Information")
                End If
            ElseIf rbAttendanceCard.Checked = True Then
                ErrorProvider1.Clear()
                ExecuteSQLQuery(" SELECT  Employee_Information.EMPLOYEE_ID, Employee_Information.EMP_CODE, Employee_Information.EmployeesName, Employee_Information.FathersName, WorkingHours.ShiftName, Employee_Information.PHOTO_FILE_NAME,  Employee_AttendanceCard.EmployeeCardNumber " &
                                " FROM    Employee_Information LEFT OUTER JOIN  Employee_AttendanceCard ON Employee_Information.EMPLOYEE_ID = Employee_AttendanceCard.EMPLOYEE_ID LEFT OUTER JOIN  WorkingHours ON Employee_Information.ShiftID = WorkingHours.ShiftID " &
                                " WHERE  (Employee_AttendanceCard.EmployeeCardNumber LIKE '" + str_repl(txtCriteria.Text) + "') ")
                If sqlDT.Rows.Count > 0 Then
                    txtEmpID.Text = sqlDT.Rows(0)("EMPLOYEE_ID")
                    txtEmployeesName.Text = sqlDT.Rows(0)("EmployeesName")
                    txtFathersName.Text = sqlDT.Rows(0)("FathersName")
                    txtShiftName.Text = sqlDT.Rows(0)("ShiftName")
                    Try
                        PictureBox1.ImageLocation = Application.StartupPath + "\Upload\Employee\" + sqlDT.Rows(0)("PHOTO_FILE_NAME")
                    Catch ex As Exception
                        PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
                    End Try
                Else
                    txtEmpID.Text = ""
                    txtEmployeesName.Text = ""
                    txtFathersName.Text = ""
                    txtShiftName.Text = ""
                    PictureBox1.Image = PayrollManagementSystem.My.Resources.no_image
                    MsgBox("No Record Found.", MsgBoxStyle.Information, "Information")
                End If
            End If
        End If
    End Sub

    Private Sub btnTakeAttendance_Click(sender As Object, e As EventArgs) Handles btnTakeAttendance.Click
        If txtEmpID.Text = "" Then
            ErrorProvider1.SetError(txtCriteria, "Mandatory field.")
        Else
            ErrorProvider1.Clear()
            ExecuteSQLQuery(" SELECT  * FROM   Leave1  WHERE  (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND ('" & Format(Now, "yyyy-MM-dd") & "' BETWEEN StartDate AND EndDate) ")
            If sqlDT.Rows.Count > 0 Then
                MsgBox("The employee is on leave", MsgBoxStyle.Critical, "Critical")
            Else
                ExecuteSQLQuery("SELECT  * FROM   Employee_Information  WHERE  (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ")")
                Dim ShiftID As Double = sqlDT.Rows(0)("ShiftID")
                ExecuteSQLQuery("SELECT *  FROM      WorkingHours  WHERE   (ShiftID = " & num_fltr(ShiftID) & ")")
                If sqlDT.Rows.Count > 0 Then
                    Dim StartTime As DateTime = Format(sqlDT.Rows(0)("StartTime"), "HH:mm")
                    Dim EndTime As DateTime = Format(sqlDT.Rows(0)("EndTime"), "HH:mm")
                    ExecuteSQLQuery("SELECT * FROM  Attendance  WHERE   (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (AttendanceDate = '" & Format(Now, "yyyy-MM-dd") & "') AND (Status = 'TimeOut')")
                    If sqlDT.Rows.Count > 0 Then
                        MsgBox("Attendance already taken.", MsgBoxStyle.Critical, "Critical")
                    Else
                        If rbTimeIn.Checked = True Then
                            ExecuteSQLQuery("SELECT * FROM  Attendance  WHERE   (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (AttendanceDate = '" & Format(Now, "yyyy-MM-dd") & "') AND (Status = 'TimeIn')")
                            If sqlDT.Rows.Count > 0 Then
                                MsgBox("Employee already time in.", MsgBoxStyle.Critical, "Critical")
                            Else
                                ExecuteSQLQuery(" INSERT INTO Attendance (EMPLOYEE_ID, AttendanceDate, TimeIn, TimeIn_Comment, Status) VALUES " &
                                                " (" & num_fltr(txtEmpID.Text) & ", '" & Format(Now, "yyyy-MM-dd") & "', '" & Format(Now, "HH:mm") & "',  'Employee In', 'TimeIn') ")
                                txtCriteria.Text = ""
                                txtCriteria.Select()
                                MsgBox("Time In saved successfully.", MsgBoxStyle.Information, "Information")
                            End If
                        ElseIf rbTimeOut.Checked = True Then
                            ExecuteSQLQuery(" UPDATE Attendance SET TimeOut='" & Format(Now, "HH:mm") & "', TimeOut_Comment='Employee Out', Status = 'TimeOut' " &
                                            " WHERE        (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (AttendanceDate = '" & Format(Now, "yyyy-MM-dd") & "') AND (Status = 'TimeIn') ")
                            txtCriteria.Text = ""
                            txtCriteria.Select()
                            MsgBox("Time Out saved successfully.", MsgBoxStyle.Information, "Information")
                        End If
                    End If
                Else
                    MsgBox("Working hours not found.", MsgBoxStyle.Critical, "Critical")
                End If
            End If
        End If
    End Sub
End Class