HRIS Payroll API

payroll.vb

Option Explicit On

Imports MySql.Data.MySqlClient

Module modPayroll

    Public Structure payslip_selectedEmployee
        Dim IndexID As Integer
        Dim PayrollID As Integer
        Dim EmployeeID As String
        Dim TransDate As Date
    End Structure

    Public Structure Payroll
        Dim PayrollID As Integer
        Dim PayrollCode As String
        Dim UsedYear As String
        Dim FirstDate As Date
        Dim SecondDate As Date
        Dim PaymentType As Integer
        Dim UsedMonth As String
        Dim Term As String
        Dim CategoryID As Integer
        Dim SetupID As Integer
    End Structure

    Public Structure Payroll_Details
        Dim IndexID As Integer
        Dim PayrollDetails As String
        Dim IsViewed As Short
    End Structure

    Public Structure Payroll_Types
        Dim PTypeID As Integer
        Dim TypeName As String
        Dim IsHonoraria As Short
        Dim InActive As Short
        Dim IsManual As Short
    End Structure

    Public Structure Payroll_Master_Transactions
        Dim TransID As Integer
        Dim TransCode As String
        Dim TransDate As String
        Dim PayrollID As Integer
        Dim CoveredFrom As Date
        Dim CoveredTo As Date
        Dim BranchID As Integer
        Dim DepartmentID As Integer
        Dim PositionID As Integer
        Dim StatusID As Integer
        Dim Gender As Integer
        Dim DivisionID As Integer
        Dim IsHonoraria As Short
        Dim TermID As Integer
        Dim PayrollTypeID As Integer
    End Structure

    Public Structure Payroll_Master
        Dim EntryID As Integer
        Dim TransDate As Date
        Dim PayrollID As Integer
        Dim EmployeeID As String
        Dim CategoryID As Integer
        Dim PositionID As Integer
        Dim DepartmentID As Integer
        Dim TaxCodeID As Integer
        Dim DailyRate As Decimal
        Dim PayrollRate As Decimal
        Dim MonthlyRate As Decimal
        Dim HourlyRate As Decimal
        Dim TotalHrsWorked As Decimal
        Dim TotalLessAbsences As Decimal
        Dim GrossIncome As Decimal
        Dim TotalMisc As Decimal
        Dim TotalDeduction As Decimal
        Dim NetPay As Decimal
        Dim YTDIncome As Decimal
        Dim YTDTax As Decimal
        Dim YTDPAGIBIG As Decimal
        Dim PostedDate As Date
        Dim Void As Short
        Dim UserID As Integer
        Dim ModifiedDate As Date
        Dim ModifiedBy As Integer
        Dim ManualEntry As Short
        Dim BranchID As Integer
        Dim StatusID As Integer
        Dim BackAccount As Decimal
        Dim TransID As Integer
        Dim DivisionID As Integer
        Dim Remarks As String
        Dim ItemCode As String
        Dim OtherIncomeTotal As Decimal
        Dim UnderPay As Decimal
        Dim OverPay As Decimal
        Dim OtherDeductionTotal As Decimal
        Dim PostedBy As Integer
        Dim IsLWOP As Short
        Dim TotalDaysWorked As Decimal
        Dim _1stQuincena As Decimal
        Dim _2ndQuincena As Decimal
    End Structure

    Public Structure Payroll_Master_Details
        Dim IndexID As Integer
        Dim TransDate As Date
        Dim PayrollID As Integer
        Dim EmployeeID As String
        Dim TransID As Integer
        Dim RefNo As Integer
        Dim AccountID As Integer
        Dim Amount As Decimal
        Dim TotalHrs As Decimal
        Dim TransRefNo As String
        Dim TransType As String
        Dim OtherItem As String
    End Structure


    Public Structure Payroll_Summary
        Dim IndexID As Integer
        Dim PayrollID As Integer
        Dim TransID As Integer
        Dim EmployeeID As String
        Dim ReferenceNo As Integer
        Dim TotalHrsWork As Integer
        Dim BasicPay As Decimal
        Dim MinLate As Decimal
        Dim Late As Decimal
        Dim DayAbsent As Decimal
        Dim Absent As Decimal
        Dim OvertimeHrs As Decimal
        Dim Overtime As Decimal
        Dim HolidayHrs As Decimal
        Dim Holiday As Decimal
        Dim SpecialHolidayHrs As Decimal
        Dim SpecialHolidays As Decimal
        Dim Sundayhrs As Decimal
        Dim Sunday As Decimal
        Dim NightDifferentialsHrs As Decimal
        Dim NightDifferentials As Decimal
        Dim NightPremiumNights As Decimal
        Dim NightPremium As Decimal

        Dim OtherIncome1 As String
        Dim OtherIncome2 As String
        Dim OtherIncome3 As String
        Dim OtherIncome4 As String
        Dim OtherIncome5 As String
        Dim OtherIncome6 As String
        Dim OtherIncome7 As String
        Dim OtherIncome8 As String
        Dim OtherIncome9 As String
        Dim OtherIncome10 As String
        Dim OtherIncome11 As String
        Dim OtherIncome12 As String
        Dim OtherIncome13 As String
        Dim OtherIncome14 As String
        Dim OtherIncome15 As String
        Dim OtherIncome16 As String
        Dim OtherIncome17 As String
        Dim OtherIncome18 As String
        Dim OtherIncome19 As String
        Dim OtherIncome20 As String
        Dim OtherIncomeTotal As Decimal
        Dim GrossIncome As Decimal

        Dim SSS_EE As Decimal
        Dim SSS_ER As Decimal
        Dim SSS_EC As Decimal
        Dim GSIS_EC As Decimal
        Dim GSIS_EE As Decimal
        Dim GSIS_ER As Decimal
        Dim PHIC_EE As Decimal
        Dim PHIC_ER As Decimal
        Dim PAGIBIG_EE As Decimal
        Dim PAGIBIG_ER As Decimal
        Dim TAX_EE As Decimal

        Dim OtherDeduction1 As String
        Dim OtherDeduction2 As String
        Dim OtherDeduction3 As String
        Dim OtherDeduction4 As String
        Dim OtherDeduction5 As String
        Dim OtherDeduction6 As String
        Dim OtherDeduction7 As String
        Dim OtherDeduction8 As String
        Dim OtherDeduction9 As String
        Dim OtherDeduction10 As String
        Dim OtherDeduction11 As String
        Dim OtherDeduction12 As String
        Dim OtherDeduction13 As String
        Dim OtherDeduction14 As String
        Dim OtherDeduction15 As String
        Dim OtherDeduction16 As String
        Dim OtherDeduction17 As String
        Dim OtherDeduction18 As String
        Dim OtherDeduction19 As String
        Dim OtherDeduction20 As String
        Dim OtherDeduction21 As String
        Dim OtherDeduction22 As String
        Dim OtherDeduction23 As String
        Dim OtherDeduction24 As String
        Dim OtherDeduction25 As String
        Dim OtherDeduction26 As String
        Dim OtherDeduction27 As String
        Dim OtherDeduction28 As String
        Dim OtherDeduction29 As String
        Dim OtherDeduction30 As String
        Dim OtherDeduction31 As String
        Dim OtherDeduction32 As String
        Dim OtherDeduction33 As String
        Dim OtherDeduction34 As String
        Dim OtherDeduction35 As String
        Dim OtherDeduction36 As String
        Dim OtherDeduction37 As String
        Dim OtherDeduction38 As String
        Dim OtherDeduction39 As String
        Dim OtherDeduction40 As String
        Dim OtherDeductionTotal As Decimal
        Dim TotalDeduction As Decimal

        Dim Miscellaneous As Decimal
        Dim NetPay As Decimal
        Dim PostedBy As Integer
        Dim DatePosted As Date
        Dim HoursPerWeek As Decimal
        Dim AccumulatedHrs As Decimal
        Dim Underpayment As Decimal
        Dim IsLWOP As Short
        Dim HalfNetPay As Short
        Dim _1stQuincena As Decimal
        Dim _2ndQuincena As Decimal
        Dim SeqNo As Integer
    End Structure

    Public Structure payroll_summary_setup
        Dim IndexID As Integer

        Dim OtherIncome1 As String
        Dim OtherIncome2 As String
        Dim OtherIncome3 As String
        Dim OtherIncome4 As String
        Dim OtherIncome5 As String
        Dim OtherIncome6 As String
        Dim OtherIncome7 As String
        Dim OtherIncome8 As String
        Dim OtherIncome9 As String
        Dim OtherIncome10 As String
        Dim OtherIncome11 As String
        Dim OtherIncome12 As String
        Dim OtherIncome13 As String
        Dim OtherIncome14 As String
        Dim OtherIncome15 As String
        Dim OtherIncome16 As String
        Dim OtherIncome17 As String
        Dim OtherIncome18 As String
        Dim OtherIncome19 As String
        Dim OtherIncome20 As String
        Dim OtherDeduction1 As String
        Dim OtherDeduction2 As String
        Dim OtherDeduction3 As String
        Dim OtherDeduction4 As String
        Dim OtherDeduction5 As String
        Dim OtherDeduction6 As String
        Dim OtherDeduction7 As String
        Dim OtherDeduction8 As String
        Dim OtherDeduction9 As String
        Dim OtherDeduction10 As String
        Dim OtherDeduction11 As String
        Dim OtherDeduction12 As String
        Dim OtherDeduction13 As String
        Dim OtherDeduction14 As String
        Dim OtherDeduction15 As String
        Dim OtherDeduction16 As String
        Dim OtherDeduction17 As String
        Dim OtherDeduction18 As String
        Dim OtherDeduction19 As String
        Dim OtherDeduction20 As String
        Dim OtherDeduction21 As String
        Dim OtherDeduction22 As String
        Dim OtherDeduction23 As String
        Dim OtherDeduction24 As String
        Dim OtherDeduction25 As String
        Dim OtherDeduction26 As String
        Dim OtherDeduction27 As String
        Dim OtherDeduction28 As String
        Dim OtherDeduction29 As String
        Dim OtherDeduction30 As String
        Dim OtherDeduction31 As String
        Dim OtherDeduction32 As String
        Dim OtherDeduction33 As String
        Dim OtherDeduction34 As String
        Dim OtherDeduction35 As String
        Dim OtherDeduction36 As String
        Dim OtherDeduction37 As String
        Dim OtherDeduction38 As String
        Dim OtherDeduction39 As String
        Dim OtherDeduction40 As String
        Dim PayrollBatchCode As Integer
        Dim PayrollPeriodID As Integer

    End Structure

    Public Enum Payment_Type
        ALWAYS = 1
        MONTHLY = 2
        SEMI_MONTHLY_1ST_CUT_OFFF = 3
        SEMI_MONTHLY_2ND_CUT_OFFF = 4
    End Enum


#Region "Payroll Types"

    Public Function GetPayrollTypeByID(ByVal PTypeID As Integer, ByRef PT As Payroll_Types) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM payrolltypes WHERE PTypeID='" & PTypeID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With PT
                .PTypeID = vRS("PTypeID").ToString()
                .TypeName = vRS("TypeName").ToString()
                .InActive = BooleanToInt(vRS("InActive").ToString())
                .IsHonoraria = BooleanToInt(vRS("IsHonoraria").ToString())
                .IsManual = BooleanToInt(vRS("IsManual").ToString())
            End With
            GetPayrollTypeByID = True
        Else
            GetPayrollTypeByID = False
        End If
        vRS.Close()
        con.Close()
    End Function



#End Region


#Region "Payroll"
    Public Function Add_Payroll(ByVal P As Payroll) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim sSQL As String = "INSERT INTO payroll (PayrollCode,UsedYear,FirstDate,SecondDate,PaymentTypes,UsedMonth,Term,CategoryID,SetupID) VALUES ('" & _
            P.PayrollCode & "','" & P.UsedYear & "',@FirstDate,@SecondDate,'" & P.PaymentType & "','" & P.UsedMonth & "','" & P.Term & "','" & P.CategoryID & "','" & P.SetupID & "')"
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@FirstDate", MySqlDbType.Date).Value = P.FirstDate
        com.Parameters.Add("@SecondDate", MySqlDbType.Date).Value = P.SecondDate
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Add_Payroll = True
        Exit Function
err:
        Add_Payroll = False
        DisplayErrorMsg("modPayroll", "Add_Payroll", Err.Number, Err.Description)
    End Function

    Public Function Update_Payroll(ByVal PayrollID As String, ByVal P As Payroll) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim sSQL As String = "UPDATE payroll SET PayrollCode='" & P.PayrollCode & "', UsedYear='" & P.UsedYear & "',FirstDate=@FirstDate,SecondDate=@SecondDate,PaymentTypes='" & P.PaymentType & "', UsedMonth='" & P.UsedMonth & "',Term='" & P.Term & "',CategoryID='" & P.CategoryID & "',SetupID='" & P.SetupID & "' WHERE PayrollID='" & PayrollID & "'"
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@FirstDate", MySqlDbType.Date).Value = P.FirstDate
        com.Parameters.Add("@SecondDate", MySqlDbType.Date).Value = P.SecondDate
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Update_Payroll = True
        Exit Function
err:
        Update_Payroll = False
        DisplayErrorMsg("modPayroll", "Add_Payroll", Err.Number, Err.Description)
    End Function

    Public Function PayrollExistByCode(ByVal PayrollCode As String) As Boolean
        If QueryHasRows("SELECT * FROM payroll WHERE PayrollCode = '" & PayrollCode & "' LIMIT 1") Then
            PayrollExistByCode = True
        Else
            PayrollExistByCode = False
        End If
    End Function

    Public Function GetPayrollByID(ByVal PayrollID As String, ByRef P As Payroll) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM payroll WHERE PayrollID ='" & PayrollID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With P
                .PayrollCode = vRS("PayrollCode").ToString()
                .CategoryID = vRS("CategoryID").ToString()
                .FirstDate = vRS("FirstDate").ToString()
                .SecondDate = vRS("SecondDate").ToString()
                .Term = vRS("Term").ToString()
                .UsedMonth = vRS("UsedMonth").ToString()
                .UsedYear = vRS("UsedYear").ToString()
                .PayrollID = vRS("PayrollID").ToString()
                .PaymentType = vRS("PaymentTypes").ToString()
                .SetupID = vRS("SetupID").ToString()
            End With

            GetPayrollByID = True
        Else
            GetPayrollByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function Delete_Payroll(ByVal PayrollID As String) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("DELETE FROM payroll WHERE PayrollID='" & PayrollID & "'", con)
        com.ExecuteNonQuery()
        con.Close()
        Delete_Payroll = True
        Exit Function
err:
        Delete_Payroll = False
        DisplayErrorMsg("modPayroll", "Delete_Payroll", Err.Number, Err.Description)
    End Function

    Public Function EmployeeHasPayroll(ByVal EmployeeID As String, ByVal PayrollID As Integer) As Boolean
        Dim con As New MySqlConnection
        con.ConnectionString = DB_CONNECTION_STRING
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM payroll_master WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            EmployeeHasPayroll = True
        Else
            EmployeeHasPayroll = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function EmployeePayrollIsPosted(ByVal EmployeeID As String, ByVal PayrollID As Integer) As Boolean
        Dim con As New MySqlConnection
        con.ConnectionString = DB_CONNECTION_STRING
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM payroll_master WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND PostedDate IS NOT NULL LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            EmployeePayrollIsPosted = True
        Else
            EmployeePayrollIsPosted = False
        End If
        vRS.Close()
        con.Close()
    End Function



#End Region


#Region "Payroll Master Transaction"

    Public Function Add_Payroll_Master_Transaction(ByVal P As Payroll_Master_Transactions) As Boolean
        On Error GoTo err
        Dim sSQL As String = "INSERT INTO payroll_master_transactions(TransCode,TransDate,PayrollID,CoveredFrom,CoveredTo,BranchID,DepartmentID,PositionID,StatusID,Gender,DivisionID,TermID,PayrollTypeID,IsHonoraria,CreatedBy) VALUES ('" & P.TransCode & _
            "',CURDATE(),'" & P.PayrollID & "',@CoveredFrom,@CoveredTo,'" & P.BranchID & "','" & P.DepartmentID & "','" & P.PositionID & "','" & P.StatusID & "','" & P.Gender & "','" & P.DivisionID & "','" & P.TermID & "','" & P.PayrollTypeID & "','" & P.IsHonoraria & "','" & CURRENT_USER.UserID & "')"
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@CoveredFrom", MySqlDbType.Date).Value = P.CoveredFrom
        com.Parameters.Add("@CoveredTo", MySqlDbType.Date).Value = P.CoveredTo
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Add_Payroll_Master_Transaction = True
        Exit Function
err:
        Add_Payroll_Master_Transaction = False
        DisplayErrorMsg("modPayroll", "Add_Payroll_Master_Transaction", Err.Number, Err.Description)
    End Function

    Public Function Update_Payroll_Master_Transaction(ByVal TransID As Integer, ByVal P As Payroll_Master_Transactions) As Boolean
        Dim sSQL As String = "UPDATE payroll_master_transactions SET TransCode='" & P.TransCode & "',TransDate=CURDATE(),PayrollID='" & P.PayrollID & "',CoveredFrom=@CoveredFrom,CoveredTo=@CoveredTo,BranchID='" & P.BranchID & "',DepartmentID='" & P.DepartmentID & "',PositionID='" & P.PositionID & "',StatusID='" & P.StatusID & "',Gender='" & P.Gender & "',DivisionID='" & P.DivisionID & _
                            "',TermID='" & P.TermID & "',PayrollTypeID='" & P.PayrollTypeID & "',IsHonoraria='" & P.IsHonoraria & "',LastModifiedDate=CURDATE(),LastModifiedBy='" & CURRENT_USER.UserID & "' WHERE TransID='" & TransID & "'"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@CoveredFrom", MySqlDbType.Date).Value = P.CoveredFrom
        com.Parameters.Add("@CoveredTo", MySqlDbType.Date).Value = P.CoveredTo
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Update_Payroll_Master_Transaction = True

        Exit Function
err:
        Update_Payroll_Master_Transaction = False
        DisplayErrorMsg("modPayroll", "Add_Payroll_Master_Transaction", Err.Number, Err.Description)
    End Function

    Public Function PayrollTransactionExistByCode(ByVal Code As String) As Boolean
        If QueryHasRows("SELECT * FROM payroll_master_transactions WHERE TransCode='" & Code & "' LIMIT 1") Then
            PayrollTransactionExistByCode = True
        Else
            PayrollTransactionExistByCode = False
        End If
    End Function

    Public Function DeletePayrollMasterTransaction(ByVal PayrollID As Integer, ByVal TransID As Integer) As Boolean
        If ExecuteQry("DELETE FROM payroll_master_transactions WHERE PayrollID='" & PayrollID & "' AND TransID='" & TransID & "';" & _
                      "DELETE FROM payroll_master WHERE PayrollID='" & PayrollID & "' AND TransID='" & TransID & "';" & _
                      "DELETE FROM payroll_master_details WHERE PayrollID='" & PayrollID & "' AND TransRefNo='" & TransID & "';" & _
                      "DELETE FROM payroll_summary WHERE PayrollID='" & PayrollID & "' AND TransID='" & TransID & "';") Then
            DeletePayrollMasterTransaction = True
        Else
            DeletePayrollMasterTransaction = False
        End If
    End Function

    Public Function DeleteEmployeePayrollTransaction(ByVal EmployeeID As String, ByVal PayrollID As Integer, ByVal TransID As Integer) As Boolean
        If ExecuteQry("DELETE FROM payroll_master WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "';" & _
                      "DELETE FROM payroll_master_details WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "';" & _
                      "DELETE FROM payroll_summary WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID ='" & TransID & "';" & _
                      "DELETE FROM payroll_summary_setup WHERE EmployeeID='" & EmployeeID & "' AND PayrollPeriodID='" & PayrollID & "' AND PayrollBatchCode='" & TransID & "';") Then

            DeleteEmployeePayrollTransaction = True
        Else
            DeleteEmployeePayrollTransaction = False
        End If
    End Function

    Public Function GetPayrollMasterTransactionByID(ByVal TransID As String, ByRef P As Payroll_Master_Transactions) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM payroll_master_transactions WHERE TransID='" & TransID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With P
                .BranchID = vRS("BranchID").ToString()
                .CoveredFrom = vRS("CoveredFrom").ToString()
                .CoveredTo = vRS("CoveredTo").ToString()
                .DepartmentID = vRS("DepartmentID").ToString()
                .DivisionID = vRS("DivisionID").ToString()
                .Gender = vRS("Gender").ToString()
                .IsHonoraria = BooleanToInt(vRS("IsHonoraria").ToString())
                .PayrollID = vRS("PayrollID").ToString()
                .PayrollTypeID = vRS("PayrollTypeID").ToString()
                .PositionID = vRS("PositionID").ToString()
                .StatusID = vRS("StatusID").ToString()
                .TermID = vRS("TermID").ToString()
                .TransCode = vRS("TransCode").ToString()
                '.TransDate = vRS("TransDate").ToString()
            End With
            GetPayrollMasterTransactionByID = True
        Else
            GetPayrollMasterTransactionByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

#End Region


#Region "Payroll Master"

    Public Function GetPayrollMasterByID(ByVal PayrollID As Integer, ByVal TransID As Integer) As Boolean
        If QueryHasRows("SELECT * FROM payroll_master WHERE PayrollID='" & PayrollID & "' AND TransID='" & TransID & "' LIMIT 1") Then
            GetPayrollMasterByID = True
        Else
            GetPayrollMasterByID = False
        End If
    End Function

    Public Function Add_Payroll_Master(ByVal PM As Payroll_Master, ByRef Last_Insert_ID As Integer) As Boolean
        Dim sSQL As String = "INSERT INTO payroll_master(TransDate,PayrollID,EmployeeID,CategoryID,PositionID,DepartmentID,PayrollRate,MonthlyRate,HourlyRate,DailyRate,TotalHrsWorked," & _
            "TotalLessAbsenses,GrossIncome,TotalDeduction,NetPay,BranchID,StatusID,TransID,DivisionID,TotalDaysWorked,UserID,Remarks,1stQuincena,2ndQuincena) VALUES(CURDATE(),'" & _
            PM.PayrollID & "','" & PM.EmployeeID & "','" & PM.CategoryID & "','" & PM.PositionID & "','" & PM.DepartmentID & "','" & PM.PayrollRate & "','" & PM.MonthlyRate & "','" & PM.HourlyRate & "','" & PM.DailyRate & "','" & PM.TotalHrsWorked & _
            "','" & PM.TotalLessAbsences & "','" & PM.GrossIncome & "','" & PM.TotalDeduction & "','" & PM.NetPay & "','" & PM.BranchID & "','" & PM.StatusID & "','" & PM.TransID & "','" & PM.DivisionID & "','" & PM.TotalDaysWorked & "','" & CURRENT_USER.UserID & "','" & PM.Remarks & "','" & PM._1stQuincena & "','" & PM._2ndQuincena & "');SELECT last_insert_id();"

        On Error GoTo Err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        'com.ExecuteNonQuery()
        Last_Insert_ID = CInt(com.ExecuteScalar())
        con.Close()
        Add_Payroll_Master = True
        Exit Function
Err:

        Add_Payroll_Master = False
        DisplayErrorMsg("modPayroll", "Add_Payroll_Master", Err.Number, Err.Description)
    End Function

    Public Function Update_Payroll_Master(ByVal PM As Payroll_Master) As Boolean
        Dim sSQL As String = "UPDATE payroll_master SET CategoryID='" & PM.CategoryID & "',PositionID='" & PM.PositionID & "',DepartmentID='" & PM.DepartmentID & "',PayrollRate='" & PM.PayrollRate & _
            "',MonthlyRate='" & PM.MonthlyRate & "',HourlyRate='" & PM.HourlyRate & "',DailyRate='" & PM.DailyRate & "',TotalHrsWorked='" & PM.TotalHrsWorked & "'," & _
            "TotalLessAbsenses='" & PM.TotalLessAbsences & "',GrossIncome='" & PM.GrossIncome & "',TotalDeduction='" & PM.TotalDeduction & "',NetPay='" & PM.NetPay & "',BranchID='" & PM.BranchID & _
            "',StatusID='" & PM.StatusID & "',DivisionID='" & PM.DivisionID & "',TotalDaysWorked='" & PM.TotalDaysWorked & "',Remarks='" & PM.Remarks & _
            "',1stQuincena='" & PM._1stQuincena & "',2ndQuincena='" & PM._2ndQuincena & _
            "' WHERE PayrollID='" & PM.PayrollID & "' AND EmployeeID='" & PM.EmployeeID & "' AND TransID='" & PM.TransID & "'"

        On Error GoTo Err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Update_Payroll_Master = True
        Exit Function
Err:

        Update_Payroll_Master = False
        DisplayErrorMsg("modPayroll", "Update_Payroll_Master", Err.Number, Err.Description)
    End Function


    Public Function Add_Payroll_Master_Details(ByVal PMD As Payroll_Master_Details) As Boolean
        Dim sSQL As String = "INSERT INTO payroll_master_details(TransDate,PayrollID,EmployeeID,TransID,RefNo,AccountID,Amount,TotalHrs,TransRefNo,TransType) VALUES(CURDATE(),'" & _
            PMD.PayrollID & "','" & PMD.EmployeeID & "','" & PMD.TransID & "','" & PMD.RefNo & "','" & PMD.AccountID & "','" & PMD.Amount & "','" & PMD.TotalHrs & "','" & PMD.TransRefNo & "','" & PMD.TransType & "')"

        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Add_Payroll_Master_Details = True
        Exit Function
err:
        Add_Payroll_Master_Details = False
        DisplayErrorMsg("modPayroll", "Add_Payroll_Master_Details", Err.Number, Err.Description)
    End Function

    Public Function Update_Payroll_Master_Details(ByVal PMD As Payroll_Master_Details) As Boolean
        Dim sSQL As String = "UPDATE payroll_master_details SET Amount='" & PMD.Amount & _
            "' WHERE PayrollID='" & PMD.PayrollID & "' AND EmployeeID='" & PMD.EmployeeID & "' AND TransRefNo='" & PMD.TransRefNo & "' AND AccountID='" & PMD.AccountID & "'"

        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Update_Payroll_Master_Details = True
        Exit Function
err:
        Update_Payroll_Master_Details = False
        DisplayErrorMsg("modPayroll", "Update_Payroll_Master_Details", Err.Number, Err.Description)
    End Function

#End Region


#Region "Payroll Summary"

    'NOTE: ENTRY WILL ACCOUNT AMOUNT

    Public Function GeneratePayrollReferrenceNo(ByVal ReferenceNo As String) As String
        Dim strRefNo = "#0000000" & ReferenceNo
        GeneratePayrollReferrenceNo = strRefNo
    End Function

    Public Function Add_Payroll_Summary(ByVal PS As Payroll_Summary) As Boolean
        Dim sSQL As String = "INSERT INTO payroll_summary(PayrollID,TransID,EmployeeID,ReferenceNo,GrossIncome,BasicPay,TotalDeduction,NetPay,HalfNetPay,TotalHrsWork,1stQuincena,2ndQuincena," & _
            "MinLate,Late,Absent,DayAbsent,OvertimeHrs,Overtime,HolidayHrs,Holiday,SpecialHolidayHrs,SpecialHoliday,SundayHrs,Sunday,NightDifferentialsHrs,NightDifferentials,NightPremiumNights,NightPremium," & _
            "SSS_EE,SSS_ER,SSS_EC,GSIS_EE,GSIS_ER,GSIS_EC,PHIC_EE,PHIC_ER,PAGIBIG_EE,PAGIBIG_ER,TAX_EE," & _
            "OtherIncome1,OtherIncome2,OtherIncome3,OtherIncome4,OtherIncome5," & _
            "OtherIncome6,OtherIncome7,OtherIncome8,OtherIncome9,OtherIncome10," & _
            "OtherIncome11,OtherIncome12,OtherIncome13,OtherIncome14,OtherIncome15," & _
            "OtherIncome16,OtherIncome17,OtherIncome18,OtherIncome19,OtherIncome20,OtherIncomeTotal," & _
            "OtherDeduction1,OtherDeduction2,OtherDeduction3,OtherDeduction4,OtherDeduction5," & _
            "OtherDeduction6,OtherDeduction7,OtherDeduction8,OtherDeduction9,OtherDeduction10," & _
            "OtherDeduction11,OtherDeduction12,OtherDeduction13,OtherDeduction14,OtherDeduction15," & _
            "OtherDeduction16,OtherDeduction17,OtherDeduction18,OtherDeduction19,OtherDeduction20," & _
            "OtherDeduction21,OtherDeduction22,OtherDeduction23,OtherDeduction24,OtherDeduction25," & _
            "OtherDeduction26,OtherDeduction27,OtherDeduction28,OtherDeduction29,OtherDeduction30," & _
            "OtherDeduction31,OtherDeduction32,OtherDeduction33,OtherDeduction34,OtherDeduction35," & _
            "OtherDeduction36,OtherDeduction37,OtherDeduction38,OtherDeduction39,OtherDeduction40,OtherDeductionTotal,Miscelleneous,SeqNo) VALUES ('" & _
            PS.PayrollID & "','" & PS.TransID & "','" & PS.EmployeeID & "','" & PS.ReferenceNo & "','" & PS.GrossIncome & "','" & PS.BasicPay & "','" & PS.TotalDeduction & "','" & PS.NetPay & _
            "','" & PS.HalfNetPay & "','" & PS.TotalHrsWork & "','" & PS._1stQuincena & "','" & PS._2ndQuincena & "','" & _
            PS.MinLate & "','" & PS.Late & "','" & PS.Absent & "','" & PS.DayAbsent & "','" & PS.OvertimeHrs & "','" & PS.Overtime & "','" & PS.HolidayHrs & "','" & PS.Holiday & "','" & PS.SpecialHolidayHrs & "','" & PS.SpecialHolidays & _
            "','" & PS.Sundayhrs & "','" & PS.Sunday & "','" & PS.NightDifferentialsHrs & "','" & PS.NightDifferentials & "','" & PS.NightPremiumNights & "','" & PS.NightPremium & _
            "','" & PS.SSS_EE & "','" & PS.SSS_ER & "','" & PS.SSS_EC & "','" & PS.GSIS_EE & "','" & PS.GSIS_ER & "','" & _
            PS.GSIS_EC & "','" & PS.PHIC_EE & "','" & PS.PHIC_ER & "','" & PS.PAGIBIG_EE & "','" & PS.PAGIBIG_ER & "','" & PS.TAX_EE & _
            "','" & CDec(PS.OtherIncome1) & "','" & CDec(PS.OtherIncome2) & "','" & CDec(PS.OtherIncome3) & "','" & CDec(PS.OtherIncome4) & "','" & CDec(PS.OtherIncome5) & _
            "','" & CDec(PS.OtherIncome6) & "','" & CDec(PS.OtherIncome7) & "','" & CDec(PS.OtherIncome8) & "','" & CDec(PS.OtherIncome9) & "','" & CDec(PS.OtherIncome10) & _
            "','" & CDec(PS.OtherIncome11) & "','" & CDec(PS.OtherIncome12) & "','" & CDec(PS.OtherIncome13) & "','" & CDec(PS.OtherIncome14) & "','" & CDec(PS.OtherIncome15) & _
            "','" & CDec(PS.OtherIncome16) & "','" & CDec(PS.OtherIncome17) & "','" & CDec(PS.OtherIncome18) & "','" & CDec(PS.OtherIncome19) & "','" & CDec(PS.OtherIncome20) & "','" & CDec(PS.OtherIncomeTotal) & _
            "','" & CDec(PS.OtherDeduction1) & "','" & CDec(PS.OtherDeduction2) & "','" & CDec(PS.OtherDeduction3) & "','" & CDec(PS.OtherDeduction4) & "','" & CDec(PS.OtherDeduction5) & _
            "','" & CDec(PS.OtherDeduction6) & "','" & CDec(PS.OtherDeduction7) & "','" & CDec(PS.OtherDeduction8) & "','" & CDec(PS.OtherDeduction9) & "','" & CDec(PS.OtherDeduction10) & _
            "','" & CDec(PS.OtherDeduction11) & "','" & CDec(PS.OtherDeduction12) & "','" & CDec(PS.OtherDeduction13) & "','" & CDec(PS.OtherDeduction14) & "','" & CDec(PS.OtherDeduction15) & _
            "','" & CDec(PS.OtherDeduction16) & "','" & CDec(PS.OtherDeduction17) & "','" & CDec(PS.OtherDeduction18) & "','" & CDec(PS.OtherDeduction19) & "','" & CDec(PS.OtherDeduction20) & _
            "','" & CDec(PS.OtherDeduction21) & "','" & CDec(PS.OtherDeduction22) & "','" & CDec(PS.OtherDeduction23) & "','" & CDec(PS.OtherDeduction24) & "','" & CDec(PS.OtherDeduction25) & _
            "','" & CDec(PS.OtherDeduction26) & "','" & CDec(PS.OtherDeduction27) & "','" & CDec(PS.OtherDeduction28) & "','" & CDec(PS.OtherDeduction29) & "','" & CDec(PS.OtherDeduction30) & _
            "','" & CDec(PS.OtherDeduction31) & "','" & CDec(PS.OtherDeduction32) & "','" & CDec(PS.OtherDeduction33) & "','" & CDec(PS.OtherDeduction34) & "','" & CDec(PS.OtherDeduction35) & _
            "','" & CDec(PS.OtherDeduction36) & "','" & CDec(PS.OtherDeduction37) & "','" & CDec(PS.OtherDeduction38) & "','" & CDec(PS.OtherDeduction39) & "','" & CDec(PS.OtherDeduction40) & _
            "','" & CDec(PS.OtherDeductionTotal) & "','" & CDec(PS.Miscellaneous) & "','" & PS.SeqNo & "')"

        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Add_Payroll_Summary = True
        Exit Function
err:
        Add_Payroll_Summary = False
        DisplayErrorMsg("modPayroll", "Add_Payroll_Summary", Err.Number, Err.Description)
    End Function

    Public Function Update_Payroll_Summary(ByVal PS As Payroll_Summary) As Boolean
        Dim sSQL As String = "UPDATE payroll_summary SET GrossIncome='" & PS.GrossIncome & "',BasicPay='" & PS.BasicPay & "',TotalDeduction='" & PS.TotalDeduction & "',NetPay='" & PS.NetPay & _
            "',HalfNetPay='" & PS.HalfNetPay & "',TotalHrsWork='" & PS.TotalHrsWork & "',Absent='" & PS.Absent & "',DayAbsent='" & PS.DayAbsent & "',Late='" & PS.Late & "',MinLate='" & PS.MinLate & "',SSS_EE='" & PS.SSS_EE & "',SSS_ER='" & PS.SSS_ER & "',SSS_EC='" & PS.SSS_EC & _
            "',GSIS_EE='" & PS.GSIS_EE & "',GSIS_ER='" & PS.GSIS_ER & "',GSIS_EC='" & PS.GSIS_EC & "',PHIC_EE='" & PS.PHIC_EE & "',PHIC_ER='" & PS.PHIC_ER & "',PAGIBIG_EE='" & PS.PAGIBIG_EE & "',PAGIBIG_ER='" & PS.PAGIBIG_ER & "',TAX_EE='" & PS.TAX_EE & _
            "',OtherIncome1='" & CDec(PS.OtherIncome1) & "',OtherIncome2='" & CDec(PS.OtherIncome2) & "',OtherIncome3='" & CDec(PS.OtherIncome3) & "',OtherIncome4='" & CDec(PS.OtherIncome4) & "',OtherIncome5='" & CDec(PS.OtherIncome5) & _
            "',OtherIncome6='" & CDec(PS.OtherIncome6) & "',OtherIncome7='" & CDec(PS.OtherIncome7) & "',OtherIncome8='" & CDec(PS.OtherIncome8) & "',OtherIncome9='" & CDec(PS.OtherIncome9) & "',OtherIncome10='" & CDec(PS.OtherIncome10) & _
            "',OtherIncome11='" & CDec(PS.OtherIncome11) & "',OtherIncome12='" & CDec(PS.OtherIncome12) & "',OtherIncome13='" & CDec(PS.OtherIncome13) & "',OtherIncome14='" & CDec(PS.OtherIncome14) & "',OtherIncome15='" & CDec(PS.OtherIncome15) & _
            "',OtherIncome16='" & CDec(PS.OtherIncome16) & "',OtherIncome17='" & CDec(PS.OtherIncome17) & "',OtherIncome18='" & CDec(PS.OtherIncome18) & "',OtherIncome19='" & CDec(PS.OtherIncome19) & "',OtherIncome20='" & CDec(PS.OtherIncome20) & "',OtherIncomeTotal='" & CDec(PS.OtherIncomeTotal) & _
            "',OtherDeduction1='" & CDec(PS.OtherDeduction1) & "',OtherDeduction2='" & CDec(PS.OtherDeduction2) & "',OtherDeduction3='" & CDec(PS.OtherDeduction3) & "',OtherDeduction4='" & CDec(PS.OtherDeduction4) & "',OtherDeduction5='" & CDec(PS.OtherDeduction5) & _
            "',OtherDeduction6='" & CDec(PS.OtherDeduction6) & "',OtherDeduction7='" & CDec(PS.OtherDeduction7) & "',OtherDeduction8='" & CDec(PS.OtherDeduction8) & "',OtherDeduction9='" & CDec(PS.OtherDeduction9) & "',OtherDeduction10='" & CDec(PS.OtherDeduction10) & _
            "',OtherDeduction11='" & CDec(PS.OtherDeduction11) & "',OtherDeduction12='" & CDec(PS.OtherDeduction12) & "',OtherDeduction13='" & CDec(PS.OtherDeduction13) & "',OtherDeduction14='" & CDec(PS.OtherDeduction14) & "',OtherDeduction15='" & CDec(PS.OtherDeduction15) & _
            "',OtherDeduction16='" & CDec(PS.OtherDeduction16) & "',OtherDeduction17='" & CDec(PS.OtherDeduction17) & "',OtherDeduction18='" & CDec(PS.OtherDeduction18) & "',OtherDeduction19='" & CDec(PS.OtherDeduction19) & "',OtherDeduction20='" & CDec(PS.OtherDeduction20) & _
            "',OtherDeduction21='" & CDec(PS.OtherDeduction21) & "',OtherDeduction22='" & CDec(PS.OtherDeduction22) & "',OtherDeduction23='" & CDec(PS.OtherDeduction23) & "',OtherDeduction24='" & CDec(PS.OtherDeduction24) & "',OtherDeduction25='" & CDec(PS.OtherDeduction25) & _
            "',OtherDeduction26='" & CDec(PS.OtherDeduction26) & "',OtherDeduction27='" & CDec(PS.OtherDeduction27) & "',OtherDeduction28='" & CDec(PS.OtherDeduction28) & "',OtherDeduction29='" & CDec(PS.OtherDeduction29) & "',OtherDeduction30='" & CDec(PS.OtherDeduction30) & _
            "',OtherDeduction31='" & CDec(PS.OtherDeduction31) & "',OtherDeduction32='" & CDec(PS.OtherDeduction32) & "',OtherDeduction33='" & CDec(PS.OtherDeduction33) & "',OtherDeduction34='" & CDec(PS.OtherDeduction34) & "',OtherDeduction35='" & CDec(PS.OtherDeduction35) & _
            "',OtherDeduction36='" & CDec(PS.OtherDeduction36) & "',OtherDeduction37='" & CDec(PS.OtherDeduction37) & "',OtherDeduction38='" & CDec(PS.OtherDeduction38) & "',OtherDeduction39='" & CDec(PS.OtherDeduction39) & "',OtherDeduction40='" & CDec(PS.OtherDeduction40) & _
            "',OtherDeductionTotal='" & CDec(PS.OtherDeductionTotal) & "',Miscelleneous='" & CDec(PS.Miscellaneous) & "',SeqNo='" & PS.SeqNo & _
            "' WHERE PayrollID='" & PS.PayrollID & "' AND TransID='" & PS.TransID & "' AND EmployeeID='" & PS.EmployeeID & "' AND ReferenceNo='" & PS.ReferenceNo & "'"

        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Update_Payroll_Summary = True
        Exit Function
err:
        Update_Payroll_Summary = False
        DisplayErrorMsg("modPayroll", "Update_Payroll_Summary", Err.Number, Err.Description)
    End Function


    Public Sub GetEmployeePayrollSummary(ByVal EmployeeID As String, ByVal PayrollID As String, ByVal TransID As String, _
                                              ByRef PS As Payroll_Summary, ByRef PSS As payroll_summary_setup)
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM payroll_summary WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With PS
                .IndexID = vRS("IndexID").ToString()
                .PayrollID = vRS("PayrollID").ToString()
                .EmployeeID = vRS("EmployeeID").ToString()
                .TransID = vRS("TransID").ToString()
                .ReferenceNo = vRS("ReferenceNo").ToString()

                .BasicPay = vRS("BasicPay").ToString()

                .Absent = vRS("Absent").ToString()
                .DayAbsent = vRS("DayAbsent").ToString()

                .MinLate = vRS("MinLate").ToString()
                .Late = vRS("Late").ToString()

                .OvertimeHrs = vRS("OvertimeHrs").ToString()
                .Overtime = vRS("Overtime").ToString()

                .Holiday = vRS("Holiday").ToString()
                .HolidayHrs = vRS("HolidayHrs").ToString()

                .SpecialHolidayHrs = vRS("SpecialHolidayHrs").ToString()
                .SpecialHolidays = vRS("SpecialHoliday").ToString()

                .NightDifferentials = vRS("NightDifferentials").ToString()
                .NightDifferentialsHrs = vRS("NightDifferentialsHrs").ToString()

                .NightPremium = vRS("NightPremium").ToString()
                .NightPremiumNights = vRS("NightPremiumNights").ToString()

                .GSIS_EC = vRS("GSIS_EC").ToString()
                .GSIS_EE = vRS("GSIS_EE").ToString()
                .GSIS_ER = vRS("GSIS_ER").ToString()

                .SSS_EC = vRS("SSS_EC").ToString()
                .SSS_EE = vRS("SSS_EE").ToString()
                .SSS_ER = vRS("SSS_ER").ToString()

                .PHIC_EE = vRS("PHIC_EE").ToString()
                .PHIC_ER = vRS("PHIC_ER").ToString()

                .PAGIBIG_EE = vRS("PAGIBIG_EE").ToString()
                .PAGIBIG_ER = vRS("PAGIBIG_ER").ToString()

                .TAX_EE = vRS("TAX_EE").ToString()

                .TotalDeduction = vRS("TotalDeduction").ToString()
                .GrossIncome = vRS("GrossIncome").ToString()
                .HalfNetPay = vRS("HalfNetPay").ToString()
                .NetPay = vRS("NetPay").ToString()

                ._1stQuincena = vRS("1stQuincena").ToString()
                ._2ndQuincena = vRS("2ndQuincena").ToString()

                '.HoursPerWeek = vRS("HoursPerWeek").ToString()
                '.TotalHrsWork = vRS("TotalHrsWork").ToString()

                .OtherIncome1 = vRS("OtherIncome1").ToString()
                .OtherIncome2 = vRS("OtherIncome2").ToString()
                .OtherIncome3 = vRS("OtherIncome3").ToString()
                .OtherIncome4 = vRS("OtherIncome4").ToString()
                .OtherIncome5 = vRS("OtherIncome5").ToString()
                .OtherIncome6 = vRS("OtherIncome6").ToString()
                .OtherIncome7 = vRS("OtherIncome7").ToString()
                .OtherIncome8 = vRS("OtherIncome8").ToString()
                .OtherIncome9 = vRS("OtherIncome9").ToString()
                .OtherIncome10 = vRS("OtherIncome10").ToString()
                .OtherIncome11 = vRS("OtherIncome11").ToString()
                .OtherIncome12 = vRS("OtherIncome12").ToString()
                .OtherIncome13 = vRS("OtherIncome13").ToString()
                .OtherIncome14 = vRS("OtherIncome14").ToString()
                .OtherIncome15 = vRS("OtherIncome15").ToString()
                .OtherIncome16 = vRS("OtherIncome16").ToString()
                .OtherIncome17 = vRS("OtherIncome17").ToString()
                .OtherIncome18 = vRS("OtherIncome18").ToString()
                .OtherIncome19 = vRS("OtherIncome19").ToString()
                .OtherIncome20 = vRS("OtherIncome20").ToString()

                .OtherIncomeTotal = vRS("OtherIncomeTotal").ToString()

                .OtherDeduction1 = vRS("OtherDeduction1").ToString()
                .OtherDeduction2 = vRS("OtherDeduction2").ToString()
                .OtherDeduction3 = vRS("OtherDeduction3").ToString()
                .OtherDeduction4 = vRS("OtherDeduction4").ToString()
                .OtherDeduction5 = vRS("OtherDeduction5").ToString()
                .OtherDeduction6 = vRS("OtherDeduction6").ToString()
                .OtherDeduction7 = vRS("OtherDeduction7").ToString()
                .OtherDeduction8 = vRS("OtherDeduction8").ToString()
                .OtherDeduction9 = vRS("OtherDeduction9").ToString()
                .OtherDeduction10 = vRS("OtherDeduction10").ToString()
                .OtherDeduction11 = vRS("OtherDeduction11").ToString()
                .OtherDeduction12 = vRS("OtherDeduction12").ToString()
                .OtherDeduction13 = vRS("OtherDeduction13").ToString()
                .OtherDeduction14 = vRS("OtherDeduction14").ToString()
                .OtherDeduction15 = vRS("OtherDeduction15").ToString()
                .OtherDeduction16 = vRS("OtherDeduction16").ToString()
                .OtherDeduction17 = vRS("OtherDeduction17").ToString()
                .OtherDeduction18 = vRS("OtherDeduction18").ToString()
                .OtherDeduction19 = vRS("OtherDeduction19").ToString()
                .OtherDeduction20 = vRS("OtherDeduction20").ToString()

                .OtherDeductionTotal = vRS("OtherDeductionTotal").ToString()

                .OtherDeduction21 = vRS("OtherDeduction21").ToString()
                .OtherDeduction22 = vRS("OtherDeduction22").ToString()
                .OtherDeduction23 = vRS("OtherDeduction23").ToString()
                .OtherDeduction24 = vRS("OtherDeduction24").ToString()
                .OtherDeduction25 = vRS("OtherDeduction25").ToString()
                .OtherDeduction26 = vRS("OtherDeduction26").ToString()
                .OtherDeduction27 = vRS("OtherDeduction27").ToString()
                .OtherDeduction28 = vRS("OtherDeduction28").ToString()
                .OtherDeduction29 = vRS("OtherDeduction29").ToString()
                .OtherDeduction30 = vRS("OtherDeduction30").ToString()
                .OtherDeduction31 = vRS("OtherDeduction31").ToString()
                .OtherDeduction32 = vRS("OtherDeduction32").ToString()
                .OtherDeduction33 = vRS("OtherDeduction33").ToString()
                .OtherDeduction34 = vRS("OtherDeduction34").ToString()
                .OtherDeduction35 = vRS("OtherDeduction35").ToString()
                .OtherDeduction36 = vRS("OtherDeduction36").ToString()
                .OtherDeduction37 = vRS("OtherDeduction37").ToString()
                .OtherDeduction38 = vRS("OtherDeduction38").ToString()
                .OtherDeduction39 = vRS("OtherDeduction39").ToString()
                .OtherDeduction40 = vRS("OtherDeduction40").ToString()

                .Miscellaneous = vRS("Miscelleneous").ToString()

                '.AccumulatedHrs = vRS("AccumulatedHrs").ToString()
            End With
        End If
        vRS.Close()

        Dim com1 As New MySqlCommand("SELECT PSS.* FROM payroll_summary PS INNER JOIN payroll_summary_setup PSS ON PS.PayrollID = PSS.PayrollPeriodID AND PS.TransID = PSS.PayrollBatchCode " & _
                                     "WHERE PS.EmployeeID='" & EmployeeID & "' AND PS.PayrollID='" & PayrollID & "' AND PS.TransID='" & TransID & "' LIMIT 1", con)
        Dim vRS1 As MySqlDataReader = com1.ExecuteReader()
        vRS1.Read()
        If vRS1.HasRows Then
            With PSS
                .IndexID = vRS1("IndexID").ToString()
                .PayrollBatchCode = vRS1("PayrollBatchCode").ToString()
                .PayrollPeriodID = vRS1("PayrollPeriodID").ToString()

                .OtherIncome1 = vRS1("OtherIncome1").ToString()
                .OtherIncome2 = vRS1("OtherIncome2").ToString()
                .OtherIncome3 = vRS1("OtherIncome3").ToString()
                .OtherIncome4 = vRS1("OtherIncome4").ToString()
                .OtherIncome5 = vRS1("OtherIncome5").ToString()
                .OtherIncome6 = vRS1("OtherIncome6").ToString()
                .OtherIncome7 = vRS1("OtherIncome7").ToString()
                .OtherIncome8 = vRS1("OtherIncome8").ToString()
                .OtherIncome9 = vRS1("OtherIncome9").ToString()
                .OtherIncome10 = vRS1("OtherIncome10").ToString()
                .OtherIncome11 = vRS1("OtherIncome11").ToString()
                .OtherIncome12 = vRS1("OtherIncome12").ToString()
                .OtherIncome13 = vRS1("OtherIncome13").ToString()
                .OtherIncome14 = vRS1("OtherIncome14").ToString()
                .OtherIncome15 = vRS1("OtherIncome15").ToString()
                .OtherIncome16 = vRS1("OtherIncome16").ToString()
                .OtherIncome17 = vRS1("OtherIncome17").ToString()
                .OtherIncome18 = vRS1("OtherIncome18").ToString()
                .OtherIncome19 = vRS1("OtherIncome19").ToString()
                .OtherIncome20 = vRS1("OtherIncome20").ToString()

                .OtherDeduction1 = vRS1("OtherDeduction1").ToString()
                .OtherDeduction2 = vRS1("OtherDeduction2").ToString()
                .OtherDeduction3 = vRS1("OtherDeduction3").ToString()
                .OtherDeduction4 = vRS1("OtherDeduction4").ToString()
                .OtherDeduction5 = vRS1("OtherDeduction5").ToString()
                .OtherDeduction6 = vRS1("OtherDeduction6").ToString()
                .OtherDeduction7 = vRS1("OtherDeduction7").ToString()
                .OtherDeduction8 = vRS1("OtherDeduction8").ToString()
                .OtherDeduction9 = vRS1("OtherDeduction9").ToString()
                .OtherDeduction10 = vRS1("OtherDeduction10").ToString()
                .OtherDeduction11 = vRS1("OtherDeduction11").ToString()
                .OtherDeduction12 = vRS1("OtherDeduction12").ToString()
                .OtherDeduction13 = vRS1("OtherDeduction13").ToString()
                .OtherDeduction14 = vRS1("OtherDeduction14").ToString()
                .OtherDeduction15 = vRS1("OtherDeduction15").ToString()
                .OtherDeduction16 = vRS1("OtherDeduction16").ToString()
                .OtherDeduction17 = vRS1("OtherDeduction17").ToString()
                .OtherDeduction18 = vRS1("OtherDeduction18").ToString()
                .OtherDeduction19 = vRS1("OtherDeduction19").ToString()
                .OtherDeduction20 = vRS1("OtherDeduction20").ToString()

                .OtherDeduction21 = vRS1("OtherDeduction21").ToString()
                .OtherDeduction22 = vRS1("OtherDeduction22").ToString()
                .OtherDeduction23 = vRS1("OtherDeduction23").ToString()
                .OtherDeduction24 = vRS1("OtherDeduction24").ToString()
                .OtherDeduction25 = vRS1("OtherDeduction25").ToString()
                .OtherDeduction26 = vRS1("OtherDeduction26").ToString()
                .OtherDeduction27 = vRS1("OtherDeduction27").ToString()
                .OtherDeduction28 = vRS1("OtherDeduction28").ToString()
                .OtherDeduction29 = vRS1("OtherDeduction29").ToString()
                .OtherDeduction30 = vRS1("OtherDeduction30").ToString()
                .OtherDeduction31 = vRS1("OtherDeduction31").ToString()
                .OtherDeduction32 = vRS1("OtherDeduction32").ToString()
                .OtherDeduction33 = vRS1("OtherDeduction33").ToString()
                .OtherDeduction34 = vRS1("OtherDeduction34").ToString()
                .OtherDeduction35 = vRS1("OtherDeduction35").ToString()
                .OtherDeduction36 = vRS1("OtherDeduction36").ToString()
                .OtherDeduction37 = vRS1("OtherDeduction37").ToString()
                .OtherDeduction38 = vRS1("OtherDeduction38").ToString()
                .OtherDeduction39 = vRS1("OtherDeduction39").ToString()
                .OtherDeduction40 = vRS1("OtherDeduction40").ToString()
            End With
        End If
        vRS1.Close()

        con.Close()
    End Sub


#End Region


#Region "Payroll Summary Setup"

    'NOTE: ENTRY WILL ACCOUNT ID

    Public Function Add_Payroll_Summary_Setup(ByVal PSS As payroll_summary_setup) As Boolean
        Dim sSQL As String = "INSERT INTO payroll_summary_setup VALUES (null,'" & PSS.OtherIncome1 & _
            "','" & PSS.OtherIncome2 & "','" & PSS.OtherIncome3 & "','" & PSS.OtherIncome4 & _
            "','" & PSS.OtherIncome5 & "','" & PSS.OtherIncome6 & "','" & PSS.OtherIncome7 & _
            "','" & PSS.OtherIncome8 & "','" & PSS.OtherIncome9 & "','" & PSS.OtherIncome10 & _
            "','" & PSS.OtherIncome11 & "','" & PSS.OtherIncome12 & "','" & PSS.OtherIncome13 & _
            "','" & PSS.OtherIncome14 & "','" & PSS.OtherIncome15 & "','" & PSS.OtherIncome16 & _
            "','" & PSS.OtherIncome17 & "','" & PSS.OtherIncome18 & "','" & PSS.OtherIncome19 & _
            "','" & PSS.OtherIncome20 & _
            "','" & PSS.OtherDeduction1 & "','" & PSS.OtherDeduction2 & "','" & PSS.OtherDeduction3 & _
            "','" & PSS.OtherDeduction4 & "','" & PSS.OtherDeduction5 & "','" & PSS.OtherDeduction6 & _
            "','" & PSS.OtherDeduction7 & "','" & PSS.OtherDeduction8 & "','" & PSS.OtherDeduction9 & _
            "','" & PSS.OtherDeduction10 & "','" & PSS.OtherDeduction11 & "','" & PSS.OtherDeduction12 & _
            "','" & PSS.OtherDeduction13 & "','" & PSS.OtherDeduction14 & "','" & PSS.OtherDeduction15 & _
            "','" & PSS.OtherDeduction16 & "','" & PSS.OtherDeduction17 & "','" & PSS.OtherDeduction18 & _
            "','" & PSS.OtherDeduction19 & "','" & PSS.OtherDeduction20 & _
            "','" & PSS.OtherDeduction21 & "','" & PSS.OtherDeduction22 & "','" & PSS.OtherDeduction23 & _
            "','" & PSS.OtherDeduction24 & "','" & PSS.OtherDeduction25 & "','" & PSS.OtherDeduction26 & _
            "','" & PSS.OtherDeduction27 & "','" & PSS.OtherDeduction28 & "','" & PSS.OtherDeduction29 & _
            "','" & PSS.OtherDeduction30 & "','" & PSS.OtherDeduction31 & "','" & PSS.OtherDeduction32 & _
            "','" & PSS.OtherDeduction33 & "','" & PSS.OtherDeduction34 & "','" & PSS.OtherDeduction35 & _
            "','" & PSS.OtherDeduction36 & "','" & PSS.OtherDeduction37 & "','" & PSS.OtherDeduction38 & _
            "','" & PSS.OtherDeduction39 & "','" & PSS.OtherDeduction40 & _
            "','" & PSS.PayrollBatchCode & "','" & PSS.PayrollPeriodID & "')"

        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Add_Payroll_Summary_Setup = True
        Exit Function
err:
        Add_Payroll_Summary_Setup = False
        DisplayErrorMsg("modPayroll", "Add_Payroll_Summary_Setup", Err.Number, Err.Description)
    End Function


    Public Function Update_Payroll_Summary_Setup(ByVal PSS As payroll_summary_setup) As Boolean

        Dim sSQL As String = "UPDATE payroll_summary_setup SET OtherIncome1='" & PSS.OtherIncome1 & _
           "',OtherIncome2='" & PSS.OtherIncome2 & "',OtherIncome3='" & PSS.OtherIncome3 & "',OtherIncome4='" & PSS.OtherIncome4 & _
           "',OtherIncome5='" & PSS.OtherIncome5 & "',OtherIncome6='" & PSS.OtherIncome6 & "',OtherIncome7='" & PSS.OtherIncome7 & _
           "',OtherIncome8='" & PSS.OtherIncome8 & "',OtherIncome9='" & PSS.OtherIncome9 & "',OtherIncome10='" & PSS.OtherIncome10 & _
           "',OtherIncome11='" & PSS.OtherIncome11 & "',OtherIncome12='" & PSS.OtherIncome12 & "',OtherIncome13='" & PSS.OtherIncome13 & _
           "',OtherIncome14='" & PSS.OtherIncome14 & "',OtherIncome15='" & PSS.OtherIncome15 & "',OtherIncome16='" & PSS.OtherIncome16 & _
           "',OtherIncome17='" & PSS.OtherIncome17 & "',OtherIncome18='" & PSS.OtherIncome18 & "',OtherIncome19='" & PSS.OtherIncome19 & _
           "',OtherIncome20='" & PSS.OtherIncome20 & _
           "',OtherDeduction1='" & PSS.OtherDeduction1 & "',OtherDeduction2='" & PSS.OtherDeduction2 & "',OtherDeduction3='" & PSS.OtherDeduction3 & _
           "',OtherDeduction4='" & PSS.OtherDeduction4 & "',OtherDeduction5='" & PSS.OtherDeduction5 & "',OtherDeduction6='" & PSS.OtherDeduction6 & _
           "',OtherDeduction7='" & PSS.OtherDeduction7 & "',OtherDeduction8='" & PSS.OtherDeduction8 & "',OtherDeduction9='" & PSS.OtherDeduction9 & _
           "',OtherDeduction10='" & PSS.OtherDeduction10 & "',OtherDeduction11='" & PSS.OtherDeduction11 & "',OtherDeduction12='" & PSS.OtherDeduction12 & _
           "',OtherDeduction13='" & PSS.OtherDeduction13 & "',OtherDeduction14='" & PSS.OtherDeduction14 & "',OtherDeduction15='" & PSS.OtherDeduction15 & _
           "',OtherDeduction16='" & PSS.OtherDeduction16 & "',OtherDeduction17='" & PSS.OtherDeduction17 & "',OtherDeduction18='" & PSS.OtherDeduction18 & _
           "',OtherDeduction19='" & PSS.OtherDeduction19 & "',OtherDeduction20='" & PSS.OtherDeduction20 & _
           "',OtherDeduction21='" & PSS.OtherDeduction21 & "',OtherDeduction22='" & PSS.OtherDeduction22 & "',OtherDeduction23='" & PSS.OtherDeduction23 & _
            "',OtherDeduction24='" & PSS.OtherDeduction24 & "',OtherDeduction24='" & PSS.OtherDeduction25 & "',OtherDeduction26='" & PSS.OtherDeduction26 & _
            "',OtherDeduction27='" & PSS.OtherDeduction27 & "',OtherDeduction28='" & PSS.OtherDeduction28 & "',OtherDeduction29='" & PSS.OtherDeduction29 & _
            "',OtherDeduction30='" & PSS.OtherDeduction30 & "',OtherDeduction31='" & PSS.OtherDeduction31 & "',OtherDeduction32='" & PSS.OtherDeduction32 & _
            "',OtherDeduction33='" & PSS.OtherDeduction33 & "',OtherDeduction34='" & PSS.OtherDeduction34 & "',OtherDeduction35='" & PSS.OtherDeduction35 & _
            "',OtherDeduction36='" & PSS.OtherDeduction36 & "',OtherDeduction37='" & PSS.OtherDeduction37 & "',OtherDeduction38='" & PSS.OtherDeduction38 & _
            "',OtherDeduction39='" & PSS.OtherDeduction39 & "',OtherDeduction40='" & PSS.OtherDeduction40 & _
           "' WHERE PayrollBatchCode='" & PSS.PayrollBatchCode & "' AND PayrollPeriodID='" & PSS.PayrollPeriodID & "'"

        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.ExecuteNonQuery()
        con.Close()
        Update_Payroll_Summary_Setup = True
        Exit Function
err:
        Update_Payroll_Summary_Setup = False
        DisplayErrorMsg("modPayroll", "Update_Payroll_Summary_Setup", Err.Number, Err.Description)
    End Function

#End Region


#Region "Payroll Posting"


    Public Function IsPayrollTransactionPosted(ByVal PayrollID As Integer, ByVal TransID As Integer) As Boolean
        If QueryHasRows("SELECT * FROM payroll_master WHERE PayrollID='" & PayrollID & "' AND TransID='" & TransID & "' AND PostedDate IS NOT NULL AND PostedBy IS NOT NULL") Then
            IsPayrollTransactionPosted = True
        Else
            IsPayrollTransactionPosted = False
        End If
    End Function

    Public Function PostPayrollPerEmployee(ByVal EmployeeID As String, ByVal PayrollID As Integer, ByVal TransID As Integer) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()

        Dim com As New MySqlCommand("UPDATE payroll_master SET PostedDate=CURDATE(), PostedBy='" & CURRENT_USER.UserID & "' WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "'", con)
        com.ExecuteNonQuery()

        Dim cmd As New MySqlCommand("UPDATE payroll_summary SET DatePosted=CURDATE(), PostedBy='" & CURRENT_USER.UserID & "' WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "'", con)
        cmd.ExecuteNonQuery()

        con.Close()

        PostPayrollPerEmployee = True
        Exit Function
err:
        PostPayrollPerEmployee = False
        DisplayErrorMsg("modPayroll", "PostPayrollPerEmployee", Err.Number, Err.Description)
    End Function



    Public Function UnPostPayrollPerEmployee(ByVal EmployeeID As String, ByVal PayrollID As Integer, ByVal TransID As Integer) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()

        Dim com As New MySqlCommand("UPDATE payroll_master SET PostedDate=null, PostedBy=null WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "'", con)
        com.ExecuteNonQuery()

        Dim cmd As New MySqlCommand("UPDATE payroll_summary SET DatePosted=null, PostedBy=null WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "' AND TransID='" & TransID & "'", con)
        cmd.ExecuteNonQuery()

        con.Close()

        UnPostPayrollPerEmployee = True
        Exit Function
err:
        UnPostPayrollPerEmployee = False
        DisplayErrorMsg("modPayroll", "PostPayrollPerEmployee", Err.Number, Err.Description)
    End Function
#End Region


#Region "Payslip"

    Public Function Add_PaySlip_SelectedEmployee(ByVal A As payslip_selectedEmployee) As Boolean
        On Error GoTo err
        If ExecuteQry("INSERT INTO payslip_selectedEmployee(PayrollID,EmployeeID,TransDate) VALUES ('" & A.PayrollID & "','" & A.EmployeeID & "',CURDATE())") Then
            Add_PaySlip_SelectedEmployee = True
        Else
            Add_PaySlip_SelectedEmployee = False
        End If
        Exit Function
err:
        DisplayErrorMsg("modPayroll", "Add_PaySlip_SelectedEmployee", Err.Number, Err.Description)
    End Function

    Public Function Delete_PayslipSelectedEmployee(ByVal EmployeeID As String, ByVal PayrollID As String) As Boolean
        On Error GoTo err
        If ExecuteQry("DELETE FROM payslip_selectedemployee WHERE EmployeeID='" & EmployeeID & "' AND PayrollID='" & PayrollID & "'") Then
            Delete_PayslipSelectedEmployee = True
        Else
            Delete_PayslipSelectedEmployee = False
        End If
        Exit Function
err:
        DisplayErrorMsg("modPayroll", "Delete_PayslipSelectedEmployee", Err.Number, Err.Description)
    End Function

    Public Function Truncate_PayslipSelectedEmployee(ByVal PayrollID As String) As Boolean
        On Error GoTo err
        If ExecuteQry("DELETE FROM payslip_selectedemployee WHERE PayrollID='" & PayrollID & "'") Then
            Truncate_PayslipSelectedEmployee = True
        Else
            Truncate_PayslipSelectedEmployee = False
        End If
        Exit Function
err:
        DisplayErrorMsg("modPayroll", "Truncate_PayslipSelectedEmployee", Err.Number, Err.Description)
    End Function

#End Region


    Public Function fn_PayrollCode(ByVal PayrollID As Integer) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT PayrollCode FROM payroll WHERE PayrollID='" & PayrollID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_PayrollCode = vRS(0).ToString()
        Else
            fn_PayrollCode = ""
        End If
        vRS.Close()
        con.Close()
    End Function


    Public Function fn_PayrollType(ByVal TypeID As Integer) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT TypeName FROM payrolltypes WHERE pTypeID ='" & TypeID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_PayrollType = vRS(0).ToString()
        Else
            fn_PayrollType = ""
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function fn_PaymentTypes(ByVal EntryID As Integer) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT PaymentTerm FROM paymenttypes WHERE PaymentID ='" & EntryID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_PaymentTypes = vRS(0).ToString()
        Else
            fn_PaymentTypes = ""
        End If
        vRS.Close()
        con.Close()
    End Function


End Module