ViewWorkerPayroll

Imports MySql.Data.MySqlClient

Public Class frmViewWorkerRecords
    ' declaring variables
    Private getQuery As String = Nothing
    Private getCommand As MySqlCommand
    Private getReader As MySqlDataReader
    Private getDay As Date = Nothing
    Private getWorkingHours As Decimal = Nothing
    Private getStartingTimeIN As Integer = Nothing
    Private getEndingTimeOUT As Integer = Nothing
    Private getTimeInFormat As DateTime = Nothing
    Private getTimeOutFormat As DateTime = Nothing
    Private getOverTimeHours As Decimal = Nothing
    Private getLateTimeHOurs As Integer = Nothing
    Private getStartingOvertime As Integer = 17
    Private getEndingOvertime As Integer = Nothing
    Private getStartingLateTime As Integer = 8
    Private getEndingLateTime As Integer = Nothing
    Private getTimeInAMHours As Integer = Nothing
    Private getTimeOutAMHours As Integer = Nothing
    Private getTimeInPMHours As Integer = Nothing
    Private getTimeOutPMHours As Integer = Nothing
    Private lateSummary As Decimal = 0
    Private iWorkingHours = 0, iLate = 0, iOverTime As Integer = 0

    Public workerID As Integer = 0
    Public sDate As Date
    Public eDate As Date

    ' get the worker's details
    Private Sub ViewWorkerRecords()

        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 worker.worker_id ='" & _
            workerID & "' AND dtr.worker_id ='" & workerID & "' AND dtr.spdate BETWEEN '" & Format(sDate.ToString("yyyy-MM-dd")) & "' AND '" & Format(eDate.ToString("yyyy-MM-dd")) & "' ORDER BY dtr.spdate"
        getCommand = New MySqlCommand(getQuery, MySQLConnection)
        getReader = getCommand.ExecuteReader
        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"))

            If getTimeInAMHours <> 0 Then
                If getTimeInAMHours < 22140 Then 'EarlyBird to 6:09:00
                    getTimeInAMHours = 21600 'who cares? reset to 6am
                End If

                If getTimeInAMHours > 22140 And getTimeInAMHours < 23401 Then ' 6:09:01 to 6:30:01
                    getTimeInAMHours = 23400 ' reset to 6:30 am
                End If

                If getTimeInAMHours > 23401 And getTimeInAMHours < 25201 Then ' 6:30:01 to 7:00:01
                    getTimeInAMHours = 25200 ' reset to 7:00 am
                End If
            End If

            If getTimeOutAMHours > 43200 Then 'HERO?
                getTimeOutAMHours = 43200 ' reset to 12PM
            End If

            If getTimeOutPMHours <> 0 Then
                If getTimeInPMHours < 46800 Then 'EarlyBird
                    getTimeInPMHours = 46800 'who cares? reset to 1pm
                End If

                If getTimeOutPMHours > 53999 And getTimeOutPMHours < 57600 Then 'HERO? 14.59 to 16
                    getTimeOutPMHours = 54000 ' reset to 3PM
                End If

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

            'For earylOut
            iLate = If(getTimeInAMHours = 0, 0, If(getTimeInAMHours > 21599, getTimeInAMHours - 21600, 0)) + If(getTimeInPMHours = 0, 0, If(getTimeInPMHours > 21599, getTimeInPMHours - 46800, 0))
            lateSummary += iLate
            Console.WriteLine("lateSummary:" & lateSummary)


            iWorkingHours = (getTimeOutAMHours - getTimeInAMHours) + (getTimeOutPMHours - getTimeInPMHours)
            getWorkingHours += If(iWorkingHours > 28800, 28800, iWorkingHours)
            Console.WriteLine("getWorkingHours:" & getWorkingHours)


            iOverTime = If(iWorkingHours > 28800, iWorkingHours - 28800, 0) 'if working hours > 8hours then OT yan
            getOverTimeHours += iOverTime
            Console.WriteLine("Overtime:" & getOverTimeHours)


            With ListView1.Items.Add(Format(getReader.Item("spdate"), "MMM dd, yyyy"))

                .SubItems.Add(If(IsDBNull(getReader.Item("cAMIN")) = True, "-", Convert.ToDateTime(getReader.Item("AM_IN").ToString).ToString("hh:mm:ss tt")))
                .SubItems.Add(If(IsDBNull(getReader.Item("cAMOUT")) = True, "-", Convert.ToDateTime(getReader.Item("AM_OUT").ToString).ToString("hh:mm:ss tt")))
                .SubItems.Add(If(IsDBNull(getReader.Item("cPMIN")) = True, "-", Convert.ToDateTime(getReader.Item("PM_IN").ToString).ToString("hh:mm:ss tt")))
                .SubItems.Add(If(IsDBNull(getReader.Item("cPMOUT")) = True, "-", Convert.ToDateTime(getReader.Item("PM_OUT").ToString).ToString("hh:mm:ss tt")))
                .SubItems.Add(FormatNumber(iLate / 3600))
                .SubItems.Add(FormatNumber(If(iWorkingHours > 28800, 28800, iWorkingHours) / 3600))
                .SubItems.Add(FormatNumber(iOverTime / 3600))

            End With
            ' set to nothing
            getStartingTimeIN = Nothing
            getEndingTimeOUT = Nothing
            getLateTimeHOurs = Nothing


            iWorkingHours = 0
            iLate = 0
            iOverTime = 0

        End While

        getReader.Close()

        txtTotalLateHours.Text = Decimal.Round(lateSummary / 3600, 2).ToString("f2")
        txtTotalOverTimeHours.Text = Decimal.Round(getOverTimeHours / 3600, 2).ToString("f2")
        txtTotalWorkHours.Text = Decimal.Round(getWorkingHours / 3600, 2).ToString("f2")

    End Sub

    Private Sub frmViewWorkerRecords_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ViewWorkerRecords()
        Catch ex As Exception

        End Try
    End Sub
End Class