Public Class frmPayroll
    Dim TotalPresentDays As Double
    Private Sub txtEmployeesCode_TextChanged(sender As Object, e As EventArgs) Handles txtEmployeesCode.TextChanged
        If txtEmployeesCode.Text = "" Then
            txtEmpID.Text = ""
            txtEmployeesName.Text = ""
            txtFathersName.Text = ""
            txtShiftName.Text = ""
        End If
    End Sub
    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        If txtEmployeesCode.Text = "" Then
            ErrorProvider1.SetError(txtEmployeesCode, "Mandatory field.")
        Else
            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 = '" + str_repl(txtEmployeesCode.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")
            Else
                txtEmpID.Text = ""
                txtEmployeesName.Text = ""
                txtFathersName.Text = ""
                txtShiftName.Text = ""
                MsgBox("No Record Found.", MsgBoxStyle.Information, "Information")
            End If
        End If
    End Sub

    Private Sub SalaryCalculation()
        txtGrossPay.Text = Val(num_fltr(txtBasicSalary.Text)) + Val(num_fltr(txtOvertime.Text)) + Val(num_fltr(txtAllowances.Text)) + Val(num_fltr(txtBonus.Text))
        txtFinalSalary.Text = Val(num_fltr(txtGrossPay.Text)) - (Val(num_fltr(txtDeduction.Text)) + Val(num_fltr(txtCashAdvance.Text)) + Val(num_fltr(txtIncomeTAX.Text)))
    End Sub

    Private Sub ClearTextField()
        dtpSalaryDate.Value = Now
        txtBasicSalary.Text = "0"
        txtOvertime.Text = "0"
        txtAllowances.Text = "0"
        txtBonus.Text = "0"
        txtGrossPay.Text = "0"
        txtDeduction.Text = "0"
        txtCashAdvance.Text = "0"
        txtIncomeTAX.Text = "0"
        txtFinalSalary.Text = "0"
        TotalPresentDays = "0"
    End Sub

    Private Sub btnFindSalary_Click(sender As Object, e As EventArgs) Handles btnFindSalary.Click
        If txtEmployeesCode.Text = "" Then
            ErrorProvider1.SetError(txtEmployeesCode, "Mandatory field.")
        Else
            ErrorProvider1.Clear()
            ClearTextField()
            'Basic Salary
            ExecuteSQLQuery("SELECT  *  FROM     Employee_Information  WHERE  (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ")")
            If sqlDT.Rows.Count > 0 Then
                txtBasicSalary.Text = sqlDT.Rows(0)("BasicSalary")
            Else
                txtBasicSalary.Text = "0"
            End If


            'Overtime Amount
            ExecuteSQLQuery("SELECT   *  FROM    Overtime  WHERE   (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "')")
            If sqlDT.Rows.Count > 0 Then
                ExecuteSQLQuery("SELECT   SUM(OvertimeRate * Overtime_Mins / 60) AS Expr1  FROM    Overtime  WHERE        (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "')")
                txtOvertime.Text = Math.Round(sqlDT.Rows(0)("Expr1"), 2)
            Else
                txtOvertime.Text = "0"
            End If

            'Allowances
            ExecuteSQLQuery("SELECT  *  FROM    Allowance   WHERE  (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') AND (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ")")
            If sqlDT.Rows.Count > 0 Then
                ExecuteSQLQuery("SELECT  SUM(AllowanceAmount) AS Expr1  FROM    Allowance   WHERE  (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') AND (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ")")
                txtAllowances.Text = Math.Round(sqlDT.Rows(0)("Expr1"), 2)
            Else
                txtAllowances.Text = "0"
            End If


            'Deduction
            ExecuteSQLQuery("SELECT   *     FROM      Deduction   WHERE    (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') AND (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ")")
            If sqlDT.Rows.Count > 0 Then
                ExecuteSQLQuery("SELECT   SUM(DeductAmount) AS Expr1    FROM      Deduction   WHERE    (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') AND (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ")")
                txtDeduction.Text = Math.Round(sqlDT.Rows(0)("Expr1"), 2)
            Else
                txtDeduction.Text = "0"
            End If

            'Cash Advance
            ExecuteSQLQuery("SELECT  *  FROM       Payment   WHERE   (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "')")
            If sqlDT.Rows.Count > 0 Then
                ExecuteSQLQuery("SELECT  SUM(CashAmount) AS Expr1  FROM       Payment   WHERE   (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (EnrollDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND EnrollDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "')")
                txtCashAdvance.Text = Math.Round(sqlDT.Rows(0)("Expr1"), 2)
            Else
                txtCashAdvance.Text = "0"
            End If

            'Total Present Days
            ExecuteSQLQuery("SELECT   *  FROM    Attendance   WHERE   (EMPLOYEE_ID = " & num_fltr(txtEmpID.Text) & ") AND (AttendanceDate >= '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "' AND AttendanceDate <= '" & Format(dtpTo.Value, "yyyy-MM-dd") & "')")
            If sqlDT.Rows.Count > 0 Then
                TotalPresentDays = sqlDT.Rows.Count
            Else
                TotalPresentDays = "0"
            End If

            SalaryCalculation()

        End If
    End Sub

    Private Sub txtBonus_TextChanged(sender As Object, e As EventArgs) Handles txtBonus.TextChanged
        SalaryCalculation()
    End Sub

    Private Sub txtIncomeTAX_TextChanged(sender As Object, e As EventArgs) Handles txtIncomeTAX.TextChanged
        SalaryCalculation()
    End Sub

    Private Sub btnPostSalary_Click(sender As Object, e As EventArgs) Handles btnPostSalary.Click
        If txtEmployeesCode.Text = "" Then
            ErrorProvider1.SetError(txtEmployeesCode, "Mandatory field.")
        Else
            ErrorProvider1.Clear()
            ExecuteSQLQuery(" INSERT INTO Payroll (EMPLOYEE_ID, SalaryDate, BasicSalary, Overtime, Allowances, Bonus, GrossPay, Deduction, CashAdvance, IncomeTAX, FinalSalary, TotalPresentDays, SalaryDateFrom, SalaryDateTo) VALUES  " &
                            " (" & num_fltr(txtEmpID.Text) & ", '" & Format(dtpSalaryDate.Value, "yyyy-MM-dd") & "', " & num_fltr(txtBasicSalary.Text) & ", " & num_fltr(txtOvertime.Text) & ", " & num_fltr(txtAllowances.Text) & ", " & num_fltr(txtBonus.Text) & ", " & num_fltr(txtGrossPay.Text) & ", " & num_fltr(txtDeduction.Text) & ", " & num_fltr(txtCashAdvance.Text) & ", " & num_fltr(txtIncomeTAX.Text) & ", " & num_fltr(txtFinalSalary.Text) & ", " & num_fltr(TotalPresentDays) & ", '" & Format(dtpFrom.Value, "yyyy-MM-dd") & "', '" & Format(dtpTo.Value, "yyyy-MM-dd") & "') ")
            ClearTextField()
            txtEmpID.Text = ""
            txtEmployeesName.Text = ""
            txtFathersName.Text = ""
            txtShiftName.Text = ""
            txtEmployeesCode.Text = ""
            MsgBox("Record has been saved successfully.", MsgBoxStyle.Information, "Information")
        End If
    End Sub

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

    End Sub
End Class