Imports MySql.Data.MySqlClient

Public Class frmWorkerPayroll
    ' declaring variables
    Private getQuery As String = Nothing
    Private getCommand As MySqlCommand
    Private getReader As MySqlDataReader
    Private getMinDate As DateTime
    Private getMaxDate As DateTime
    Private getPresentDays As Integer = Nothing
    Private getWorkerID As String = Nothing
    Private getWorkerFullname As String = Nothing
    Private getWorkerDesignation As String = Nothing
    Private getWorkingHours As Decimal = Nothing
    Private getTimeInAMHours As Integer = Nothing
    Private getTimeOutAMHours As Integer = Nothing
    Private getTimeInPMHours As Integer = Nothing
    Private getTimeOutPMHours As Integer = Nothing
    Private getDesignationAmount As Decimal = Nothing
    Private getDesignationID As Integer = Nothing
    Private getOverTimeHours As Decimal = Nothing
    Private getWorkerOverTime As Integer = Nothing
    Private getLateHours As Integer = Nothing
    Private getWorkerLateTime As Integer = Nothing
    Private loopOverTime As Integer = 17
    Private loopLateTime As Integer = 8
    Private getIncrementPayNumbers As Integer = 1
    Private passLateTime = Nothing
    Private lateSummary As Decimal = 0

    Private iWorkingHours = 0, iOverTime As Integer = 0

    ' declare variable to get decimal point
    Private getGrossPay As Decimal = Nothing
    Private getLateSummary As Decimal = Nothing
    Private getTotalDeduction As Decimal = Nothing
    Private getTotalOT As Decimal = Nothing
    Private getNetPay As Decimal = Nothing
    Private calculateTotalDeduction As Decimal = Nothing
    Private calculateNetPay As Decimal = Nothing


    'modified


    Private Sub btnSearchWorkerID_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchWorkerID.Click
        Try

            getPresentDays = Nothing
            getWorkerID = Nothing
            getWorkerFullname = Nothing
            getWorkerDesignation = Nothing
            getWorkingHours = Nothing
            getTimeInAMHours = Nothing
            getTimeOutAMHours = Nothing
            getTimeInPMHours = Nothing
            getTimeOutPMHours = Nothing
            getDesignationAmount = Nothing
            getDesignationID = Nothing
            getOverTimeHours = Nothing
            getWorkerOverTime = Nothing
            getLateHours = Nothing
            getWorkerLateTime = Nothing
            loopOverTime = 17
            loopLateTime = 8
            getIncrementPayNumbers = 1
            passLateTime = Nothing
            lateSummary = 0
            iWorkingHours = 0
            iOverTime = 0
            getGrossPay = Nothing
            getLateSummary = Nothing
            getTotalDeduction = Nothing
            getTotalOT = Nothing
            getNetPay = Nothing
            calculateTotalDeduction = Nothing
            calculateNetPay = Nothing
            ' workerAbsent()
            payWorkerID()
        Catch ex As Exception

        End Try

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            submitWorkerWage()
        Catch ex As Exception

        End Try
    End Sub


    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

        Try
            If MsgBox("Are you sure you want to close?", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

                frmMainMenu.Show()
                Me.Hide()
                frmViewWorkerRecords.Hide()

            End If
        Catch ex As Exception

        End Try

    End Sub


    ' getting the FromDate and ToDate goes here
    Public Sub payWorkerID()
        If cbSearch.SelectedIndex = -1 Then

            MsgBox("Please select to be search", MsgBoxStyle.Information)
            cbSearch.Focus()
            Exit Sub
        ElseIf txtSearchWorkerID.Text = "" Then

            If cbSearch.SelectedIndex = 0 Then

                MsgBox("Please enter worker id to be search", MsgBoxStyle.Information)
                txtSearchWorkerID.Focus()

            ElseIf cbSearch.SelectedIndex = 1 Then

                MsgBox("Please enter worker fullname to be search", MsgBoxStyle.Information)
                txtSearchWorkerID.Focus()

            End If

        Else

            If cbSearch.SelectedIndex = 0 Then
                getQuery = "SELECT worker.worker_id, worker.worker_fullname, designation.designation_name, designation.designation_amount FROM worker, designation WHERE worker.designation_id = designation.designation_id AND worker.worker_id ='" & txtSearchWorkerID.Text & "'"
            Else
                getQuery = "SELECT worker.worker_id, worker.worker_fullname, designation.designation_name, designation.designation_amount FROM worker, designation WHERE worker.designation_id = designation.designation_id AND worker.worker_fullname ='" & txtSearchWorkerID.Text & "'"
            End If


        End If


        ' getting the worker id, worker fullname and worker designation
        ' getQuery = "SELECT worker.worker_id, worker.worker_fullname, designation.designation_name, designation.designation_amount FROM worker, designation WHERE worker.designation_id = designation.designation_id AND worker.worker_id ='" & txtSearchWorkerID.Text & "'"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader


        If txtSearchWorkerID.Text = "" Then

            MsgBox("Please enter worker id to search", MsgBoxStyle.Information)
            txtSearchWorkerID.Focus()

        ElseIf getReader.Read = True Then

            ' getting the worker informations
            getWorkerID = (getReader.Item("worker_id").ToString)
            getWorkerFullname = (getReader.Item("worker_fullname").ToString)
            getWorkerDesignation = (getReader.Item("designation_name").ToString)
            getDesignationAmount = (getReader.Item("designation_amount").ToString)

            getReader.Close()

            ' getting the dtr record if the worker works
            getQuery = "SELECT dtr.worker_id FROM dtr, worker WHERE dtr.worker_id ='" & getWorkerID & "' AND worker.worker_id ='" & getWorkerID & "'"
            getCommand = New MySqlCommand(getQuery, MySQLConnection)
            getReader = getCommand.ExecuteReader

            ' worker absent
            If getReader.Read = False Then

                MsgBox(Space(15) & "Worker : " & txtSearchWorkerID.Text & vbCrLf & "Don't have an attendance records", MsgBoxStyle.Information)

                ' refresh fields
                clearTextFields()

            Else


                getReader.Close()

                ' '' shows the FromDate and ToDate
                ''getQuery = "SELECT MIN(dtr.date) as 'FromDate', MAX(dtr.date) as 'ToDate' FROM dtr, worker WHERE dtr.worker_id ='" & txtSearchWorkerID.Text & "' AND worker.worker_id ='" & txtSearchWorkerID.Text & "'"
                ''getCommand = New MySqlCommand(getQuery, MySQLConnection)
                ''getReader = getCommand.ExecuteReader

                ''If getReader.Read = True Then

                getMinDate = dtpFrom.Value
                getMaxDate = dtpTo.Value

                ''End If

                ''getReader.Close()


                ' calculate the present days
                showPresentDays()

                ' calculate the working hours
                showWorkingHours()

                '' calculate the over time hours
                'showOverTimeHours()

                '' calculate the late hours
                'showLateHours()

                '' display informations
                WorkeyPay()

                '' perform calculations
                calculateWage()

                '' refresh variables
                'refreshVariables()

            End If

            getReader.Close()

        Else

            MsgBox(Space(5) & "Worker : " & txtSearchWorkerID.Text & vbCrLf & "Sorry no data found", MsgBoxStyle.Information)

            ' refresh fields
            clearTextFields()

        End If


        getReader.Close()



    End Sub
     Function ReturnAmIn(ByVal timeSeconds As Integer) As Integer
        Dim temp As Integer = 0

        If timeSeconds < 32460 Then  '09:01:00
            temp = 30600    ' 08:30:00
        End If

        If timeSeconds < 30660 Then  '08:31:00
            temp = 30600   ' 08:30:00
        End If

        If timeSeconds < 29460 Then  '08:11:00
            temp = 28800  '8:00:00
        End If
        timeSeconds = temp
        Return timeSeconds
    End Function

    Function ReturnBreakOut(ByVal timeSeconds As Integer) As Integer
        If timeSeconds > 43200 Then  '12:00:00
            timeSeconds = 43200   ' 12:00:00
        End If

        Return timeSeconds
    End Function

    Function ReturnBreakIn(ByVal timeSeconds As Integer) As Integer
        If timeSeconds < 47460 Then  '13:11:00
            timeSeconds = 46800    ' 13:00:00
        End If

        Return timeSeconds
    End Function

    Function ReturnPmOut(ByVal timeSeconds As Integer) As Integer
        If timeSeconds > 61199 And timeSeconds < 64799 Then ' 16:59:59 18:01:00.
            timeSeconds = 61200  ' 5PM
        End If

        'If getTimeOutPMHours > 64799 And getTimeOutPMHours < 65700 Then 'HERO?  6pm to 6:30pm
        '    timeSeconds = 64800 ' reset to 6PM
        'End If
        Return timeSeconds
    End Function

    Function ReturnHalfAfterNoonIn(ByVal timeSeconds As Integer) As Integer
        If timeSeconds > 39600 And timeSeconds < 47460 Then  '11:00:00   13:11:00
            timeSeconds = 46800    ' 13:00:00
        End If
        Return timeSeconds
    End Function

    Function ReturnHalfAfterNoonOut(ByVal timeSeconds As Integer) As Integer
        If timeSeconds > 39600 And timeSeconds < 47460 Then  '11:00:00   13:11:00
            timeSeconds = 46800    ' 13:00:00
        End If
        Return timeSeconds
    End Function

    Function ReturnHalfAmOut(ByVal timeSeconds As Integer) As Integer
        If timeSeconds < 46800 Then 'less than 10Am
            timeSeconds = 43200   ' 12:00:00
        End If

        If timeSeconds > 46800 And timeSeconds < 64800 Then
            timeSeconds = 61200  ' 5PM
        End If
        Return timeSeconds
    End Function
    ' shows the time in hours and time out hours of the worker goes here
    Public Sub showWorkingHours()

        getQuery = "SELECT TIME_TO_SEC(`AM_IN`)'cAMIN',TIME_TO_SEC(`AM_OUT`)'cAMOUT',TIME_TO_SEC(`PM_IN`)'cPMIN',TIME_TO_SEC(`PM_OUT`)'cPMOUT', coalesce(TIME_TO_SEC(`OverTime`),0)'cOverTime',   `AM_IN`, `AM_OUT`, `PM_IN`, `PM_OUT`, `spdate`, `OverTime` FROM dtr, worker WHERE dtr.worker_id ='" & getWorkerID & "' AND worker.worker_id ='" & getWorkerID & "' AND dtr.spdate BETWEEN '" & Format(getMinDate.ToString("yyyy-MM-dd")) & "' AND '" & Format(getMaxDate.ToString("yyyy-MM-dd")) & "'"
        Console.WriteLine(getQuery)
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader
        lateSummary = 0
        Dim temp4 As Integer = 0

        While getReader.Read
            temp4 += 1
            ' getting the time in and time out of the worker
            getTimeInAMHours = If(IsDBNull(getReader.Item("cAMIN")) = True, 0, getReader.Item("cAMIN"))
            getTimeOutAMHours = If(IsDBNull(getReader.Item("cAMOUT")) = True, 0, getReader.Item("cAMOUT"))
            getTimeInPMHours = If(IsDBNull(getReader.Item("cPMIN")) = True, 0, getReader.Item("cPMIN"))
            getTimeOutPMHours = If(IsDBNull(getReader.Item("cPMOUT")) = True, 0, getReader.Item("cPMOUT"))

            Console.Write(" AMIN:" & getTimeInAMHours)
            Console.Write(" AMOUT:" & getTimeOutAMHours)
            Console.Write(" PMIN:" & getTimeInPMHours)
            Console.Write(" PMOUT:" & getTimeOutPMHours)

            If getTimeInAMHours <> 0 And getTimeOutAMHours <> 0 Then
                'VALID

                If getTimeInPMHours = 0 Then
                    'HALF DAY
                    Console.WriteLine("HALF DAY!")
                    If getTimeInAMHours < 28800 Then
                        'HALF DAY MORNING

                        getTimeInAMHours = returnAmIn(getTimeInAMHours)


                    Else
                        'HALFDAY AFTERNOON

                        getTimeInAMHours = ReturnHalfAfterNoonIn(getTimeInAMHours)

                    End If

                    getTimeOutAMHours = ReturnHalfAmOut(getTimeOutAMHours)



                Else
                    'WHOLE DAY
                    getTimeInAMHours = ReturnAmIn(getTimeInAMHours)
                    getTimeOutAMHours = ReturnBreakOut(getTimeOutAMHours)
                    getTimeInPMHours = ReturnBreakIn(getTimeInPMHours)
                    getTimeOutPMHours = ReturnPmOut(getTimeOutPMHours)

                End If

                If getTimeInAMHours < 36000 Then

                    lateSummary += If(getTimeInAMHours = 0, 0, If(getTimeInAMHours > 28799, getTimeInAMHours - 28800, 0)) + If(getTimeInPMHours = 0, 0, If(getTimeInPMHours > 46799, getTimeInPMHours - 46800, 0))
                Else

                    lateSummary += getTimeInAMHours - 46800
                End If

                iWorkingHours = (getTimeOutAMHours - getTimeInAMHours) + (getTimeOutPMHours - getTimeInPMHours)

                getWorkingHours += If(iWorkingHours > 28800, 28800, iWorkingHours)



                iOverTime = If(iWorkingHours > 28800, iWorkingHours - 28800, 0) 'if working hours > 8hours then OT yan

                getOverTimeHours += iOverTime

                Console.WriteLine("AMIN:" & getTimeInAMHours)
                Console.WriteLine("AMOUT:" & getTimeOutAMHours)
                Console.WriteLine("PMIN:" & getTimeInPMHours)
                Console.WriteLine("PMOUT:" & getTimeOutPMHours)
                Console.WriteLine("lateSummary:" & lateSummary)
                Console.WriteLine("getWorkingHours:" & getWorkingHours)
                Console.WriteLine("Overtime:" & getOverTimeHours)

            Else
                iWorkingHours = 0
                getWorkingHours += 0
                iOverTime = 0
                getOverTimeHours += 0

            End If


            getTimeInAMHours = Nothing
            getTimeOutAMHours = Nothing
            getTimeInPMHours = Nothing
            getTimeOutPMHours = Nothing

        End While

        getReader.Close()
        getPresentDays = temp4
        getWorkingHours = getWorkingHours / 3600
        lateSummary = lateSummary / 3600

        Console.WriteLine("SUM: {0} / {1} / {2} ", lateSummary, getWorkingHours, getPresentDays)

        txtWorkingHours.Text = getWorkingHours

        ' refresh working hours



    End Sub

    Public Sub showPresentDays()

        ' getting the present days goes here
        getQuery = "SELECT dtr.spdate FROM dtr, worker WHERE dtr.spdate BETWEEN '" & Format(getMinDate.ToString("yyyy-MM-dd")) & "' AND '" & Format(getMaxDate.ToString("yyyy-MM-dd")) & "' AND dtr.worker_id ='" & getWorkerID & "' AND worker.worker_id ='" & getWorkerID & "'"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader

        While getReader.Read

            ' calculate the present days
            getPresentDays = getPresentDays + 1

        End While

        getReader.Close()

    End Sub

    ' shows the overtime hour of the worker
    Public Sub showOverTimeHours()

        ' getting the overtime hour of the worker
        getQuery = "SELECT HOUR(dtr.time_out) as 'over_time' FROM dtr, worker WHERE dtr.time_out IS NOT NULL AND worker.worker_id ='" & getWorkerID & "' AND dtr.worker_id ='" & txtSearchWorkerID.Text & "'"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader

        While getReader.Read

            ' get the over time hour
            getOverTimeHours = (getReader.Item("over_time"))

            While loopOverTime < getOverTimeHours

                ' get the hours
                getWorkerOverTime = getWorkerOverTime + 1

                loopOverTime = loopOverTime + 1

            End While
            ' refresh loop
            loopOverTime = 17

        End While

        getReader.Close()



    End Sub

    ' shows the late hour of the worker
    Public Sub showLateHours()

        ' getting the late hour of the worker
        getQuery = "SELECT HOUR(dtr.time_in) AS 'late_hour' FROM dtr, worker WHERE dtr.time_out IS NOT NULL AND worker.worker_id ='" & getWorkerID & "' AND dtr.worker_id ='" & getWorkerID & "'"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader

        While getReader.Read

            getLateHours = (getReader.Item("late_hour"))

            ' get the late hour
            While loopLateTime < getLateHours

                ' get the hours
                getWorkerLateTime = getWorkerLateTime + 1

                loopLateTime = loopLateTime + 1

            End While

            ' passing the late value
            passLateTime = getWorkerLateTime

            ' refresh loop
            loopLateTime = 8

        End While

        getReader.Close()

    End Sub



    ' inputted deductions

    Private Sub txtSSS_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSSS.TextChanged, txtPHILHEALTH.TextChanged, txtPagIbig.TextChanged, txtLateSummary.TextChanged, txtOthers.TextChanged

        calculateTotalDeduction = Val(txtSSS.Text) + Val(txtPagIbig.Text) + Val(txtLateSummary.Text) + Val(txtOthers.Text) + Val(txtPHILHEALTH.Text)

        calculateNetPay = Val(txtGrossPay.Text) - Val(calculateTotalDeduction)

        txtTotalDeduction.Text = Decimal.Round(calculateTotalDeduction, 2).ToString("f2")

        txtNetPay.Text = Decimal.Round(calculateNetPay, 2).ToString("f2")

    End Sub

    'Private Sub txtPagIbig_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPagIbig.TextChanged

    '    calculateTotalDeduction = Val(txtSSS.Text) + Val(txtPagIbig.Text) + Val(txtLateSummary.Text) + Val(txtOthers.Text)

    '    calculateNetPay = Val(txtGrossPay.Text) - Val(calculateTotalDeduction)

    '    txtTotalDeduction.Text = Decimal.Round(calculateTotalDeduction, 2).ToString("f2")

    '    txtNetPay.Text = Decimal.Round(calculateNetPay, 2).ToString("f2")

    'End Sub

    'Private Sub txtLateSummary_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    '    calculateTotalDeduction = Val(txtSSS.Text) + Val(txtPagIbig.Text) + Val(txtLateSummary.Text) + Val(txtOthers.Text)

    '    calculateNetPay = Val(txtGrossPay.Text) - Val(calculateTotalDeduction)

    '    txtTotalDeduction.Text = Decimal.Round(calculateTotalDeduction, 2).ToString("f2")

    '    txtNetPay.Text = Decimal.Round(calculateNetPay, 2).ToString("f2")

    'End Sub


    'Private Sub txtOthers_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtOthers.TextChanged


    '    calculateTotalDeduction = Val(txtSSS.Text) + Val(txtPagIbig.Text) + Val(txtLateSummary.Text) + Val(txtOthers.Text)

    '    calculateNetPay = Val(txtGrossPay.Text) - Val(calculateTotalDeduction)

    '    txtTotalDeduction.Text = Decimal.Round(calculateTotalDeduction, 2).ToString("f2")

    '    txtNetPay.Text = Decimal.Round(calculateNetPay, 2).ToString("f2")

    'End Sub

    ' calculate the worker wage goes here
    Public Sub calculateWage()

        ' calculate the net pay
        '

        getGrossPay = (Val(txtRatePerHour.Text) * Val(txtWorkingHours.Text)) + (Val(txtOverTime.Text) * Val(txtOverTimeHours.Text))
        ' getLateSummary = getWorkerLateTime * lateSummary
        ' getTotalDeduction = Val(txtSSS.Text) + Val(txtPagIbig.Text) + Val(txtLateSummary.Text)
        lateSummary = lateSummary * Val(txtRatePerHour.Text)
        txtLateSummary.Text = Decimal.Round(lateSummary, 2).ToString("f2")
        txtGrossPay.Text = Decimal.Round(getGrossPay, 2).ToString("f2")
        txtTotalDeduction.Text = Decimal.Round(getLateSummary, 2).ToString("f2")
        getNetPay = Val(txtGrossPay.Text) - getLateSummary

        txtNetPay.Text = Decimal.Round(getNetPay, 2).ToString("f2")


    End Sub


    ' set the variable to nothing
    Public Sub refreshVariables()

        getPresentDays = Nothing
        getMinDate = Nothing
        getMaxDate = Nothing
        getWorkerID = Nothing
        getWorkerFullname = Nothing
        getWorkerDesignation = Nothing
        getWorkerOverTime = Nothing
        getWorkerLateTime = Nothing

    End Sub


    ' clear all inputted text fields
    Public Sub clearTextFields()

        txtSearchWorkerID.Clear()
        txtWorkerID.Clear()
        txtWorkerFullname.Clear()
        txtDesignation.Clear()
        txtPresentDays.Clear()
        txtWorkingHours.Clear()
        txtGrossPay.Clear()
        txtRatePerHour.Clear()
        txtPagIbig.Clear()
        txtPHILHEALTH.Clear()

        txtOthers.Clear()
        txtOverTime.Clear()
        txtOverTimeHours.Clear()
        txtLateSummary.Clear()
        txtPagIbig.Clear()
        txtSSS.Clear()
        txtTotalDeduction.Clear()
        txtNetPay.Clear()
        dtpFrom.Text = Date.Now
        dtpTo.Text = Date.Now


    End Sub


    ' assign information into textfields
    Public Sub WorkeyPay()
        Dim ot As Decimal = (getOverTimeHours / 3600)
        txtOverTimeHours.Text = Decimal.Round(ot, 2).ToString("f2")
        Console.WriteLine("HAHAHA" & Decimal.Round(ot, 2).ToString("f2"))

        txtPresentDays.Text = getPresentDays
        dtpTo.Text = getMaxDate
        dtpFrom.Text = getMinDate
        txtWorkerID.Text = getWorkerID
        txtWorkerFullname.Text = getWorkerFullname
        txtDesignation.Text = getWorkerDesignation
        txtRatePerHour.Text = Decimal.Round(getDesignationAmount, 2).ToString("f2")
        txtOverTime.Text = Decimal.Round(getDesignationAmount, 2).ToString("f2")

    End Sub

    ' increment the pay id goes here
    Public Sub incrementPayID()

        getQuery = "SELECT wage.wage_id AS 'increment_number' FROM wage ORDER BY wage.wage_id DESC LIMIT 1"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader

        While getReader.Read

            getIncrementPayNumbers = (getReader.Item("increment_number").ToString) + 1

        End While

        getReader.Close()

    End Sub


    ' get the worker wage goes here
    Public Sub submitWorkerWage()

        If txtWorkerID.Text = "" Then

            MsgBox("Please select worker ID", MsgBoxStyle.Information)
            txtSearchWorkerID.Focus()

        Else

            If MsgBox(Space(15) & "Worker ID: " & txtWorkerID.Text & vbCrLf & "Are you sure you want to pay?", MsgBoxStyle.Question + MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

                ' declare variable to hold the key
                Dim getDeductionID As Integer = Nothing

                ' get the deduction id
                getQuery = "SELECT wage.deduction_id FROM wage WHERE wage.worker_id ='" & getWorkerID & "'"
                getCommand = New MySqlCommand(getQuery, MySQLConnection)
                getReader = getCommand.ExecuteReader

                If getReader.Read = True Then

                    getDeductionID = (getReader.Item("deduction_id").ToString)

                End If

                getReader.Close()

                ' remove the previous deduction
                getQuery = "DELETE FROM deduction WHERE deduction.deduction_id ='" & getDeductionID & "'"
                getCommand = New MySqlCommand(getQuery, MySQLConnection)
                getReader = getCommand.ExecuteReader

                getReader.Close()

                ' remove the previous wage
                getQuery = "DELETE FROM wage WHERE wage.worker_id ='" & getWorkerID & "'"
                getCommand = New MySqlCommand(getQuery, MySQLConnection)
                getReader = getCommand.ExecuteReader

                getReader.Close()

                ' refresh increment id
                incrementPayID()

                ' get the wage/deduction of the worker
                getQuery = "INSERT INTO wage VALUES('" & getIncrementPayNumbers & "','" & txtPresentDays.Text & "','" & Val(txtRatePerHour.Text) * Val(txtWorkingHours.Text) & _
                    "','" & Val(txtOverTime.Text) * Val(txtOverTimeHours.Text) & "','" & Date.Today.ToString("yyyy-MM-dd") & "','" & txtWorkerID.Text & "','" & getIncrementPayNumbers & "','" & _
                    frmLogin.getStaffID & "', '" & txtOverTimeHours.Text & "','" & txtWorkingHours.Text & "','" & txtNetPay.Text & "','" & txtGrossPay.Text & "')"
                Console.WriteLine(getQuery)

                getCommand = New MySqlCommand(getQuery, MySQLConnection)

                getReader = getCommand.ExecuteReader

                getReader.Close()

                getQuery = "INSERT INTO deduction VALUES('" & getIncrementPayNumbers & "','" & txtPHILHEALTH.Text & "','" & txtSSS.Text & "','" & txtPagIbig.Text & "','" & txtLateSummary.Text & "','" & txtOthers.Text & "','" & Date.Today.ToString("yyyy-MM-dd") & "')"
                getCommand = New MySqlCommand(getQuery, MySQLConnection)
                getReader = getCommand.ExecuteReader

                getReader.Close()

                MsgBox("Successfully saved!", MsgBoxStyle.Information)

                ' remove the dtr of the worker
                deleteWorkerDTR()

                ' refresh textfields
                clearTextFields()

                ' refresh increment id
                incrementPayID()


            End If

        End If

    End Sub

    ' remove the dtr of the worker after pay
    Private Sub deleteWorkerDTR()

        ' remove the dtr
        getQuery = "DELETE FROM dtr WHERE dtr.worker_id ='" & txtWorkerID.Text & "'"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader

        getReader.Close()

    End Sub



    Private Sub btnViewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewRecord.Click
        Try
            If txtSearchWorkerID.Text = "" Then
                MsgBox("Please select worker ID", MsgBoxStyle.Information)
                txtSearchWorkerID.Focus()
                Exit Sub
            Else
                Dim f As New frmViewWorkerRecords
                f.workerID = txtWorkerID.Text
                f.sDate = dtpFrom.Value
                f.eDate = dtpTo.Value
                f.ShowDialog()
                frmViewWorkerRecords.txtTotalLateHours.Text = passLateTime
                frmViewWorkerRecords.txtTotalWorkHours.Text = txtWorkingHours.Text
                frmViewWorkerRecords.txtTotalOverTimeHours.Text = txtOverTimeHours.Text
                'frmViewWorkerRecords.Show()
            End If
        Catch ex As Exception

        End Try
    End Sub

    ' remove dtr of the worker absent
    Private Sub workerAbsent()

        getQuery = "DELETE FROM dtr WHERE dtr.time_OUT IS NULL AND dtr.worker_id ='" & getWorkerID & "'"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader

        getReader.Close()

    End Sub

    Private Sub frmWorkerPayroll_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        frmMainMenu.Show()
        Me.Hide()
        frmViewWorkerRecords.Hide()
    End Sub

    Private Sub frmWorkerPayroll_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        With txtSearchWorkerID
            .AutoCompleteCustomSource = SourceSuggestion
            .AutoCompleteMode = AutoCompleteMode.SuggestAppend
            .AutoCompleteSource = AutoCompleteSource.CustomSource
        End With
    End Sub
    Dim SourceSuggestion As New AutoCompleteStringCollection
    Private Sub cbSearch_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbSearch.SelectionChangeCommitted
        SourceSuggestion.Clear()

        If cbSearch.SelectedIndex = 0 Then
            Dim dt As New DataTable
            Dim da As New MySqlDataAdapter("SELECT worker_id FROM worker", ConnectionString)
            da.Fill(dt)

            For Each dr As DataRow In dt.Rows
                SourceSuggestion.Add(dr(0))
            Next
        ElseIf cbSearch.SelectedIndex = 1 Then
            Dim dt As New DataTable
            Dim da As New MySqlDataAdapter("SELECT worker_fullname FROM worker", ConnectionString)
            da.Fill(dt)

            For Each dr As DataRow In dt.Rows
                SourceSuggestion.Add(dr(0))
            Next
        End If
    End Sub
End Class