HRIS Employee API

employee.vb

Option Explicit On

Imports MySql.Data.MySqlClient

Module modEmployees
    Public Structure Employees
        Dim e_id As Integer
        Dim e_idno As String
        Dim e_deviceno As String
        Dim e_ssno As String
        Dim e_gsisno As String
        Dim e_pagibigno As String
        Dim e_philhealthno As String
        Dim e_tinno As String
        Dim e_title As String
        Dim e_fname As String
        Dim e_lname As String
        Dim e_mname As String
        Dim e_suffix As String
        Dim e_birthdate As Date
        Dim e_gender As String
        Dim e_civil_status As String
        Dim e_cityadd As String
        Dim e_homeadd As String
        Dim e_provincialadd As String
        Dim e_zipcode As String
        Dim e_email As String
        Dim e_fax_number As String
        Dim e_mobile_number As String
        Dim e_incentive As Decimal
        Dim e_salaryamount As Decimal
        Dim e_hourlyrate As Decimal
        Dim e_dailyrate As Decimal
        Dim e_monthlyrate As Decimal
        Dim tp_id As String
        Dim e_bloodtype As String
        Dim e_religion As String
        Dim e_dateemployed As Date
        Dim DateLastPromotion As Date
        Dim DateEnteredGovService As Date
        Dim jt_id As String
        Dim e_addedby As Integer
        Dim e_dateadded As Date
        Dim e_modifiedby As Integer
        Dim e_datemodified As Date
        Dim DeptID As String
        Dim branch_id As String
        Dim division_id As String
        Dim schedule_id As Integer
        Dim nationality_id As String
        Dim s_id As String
        Dim InActive As Short
        Dim Height As String
        Dim Weigth As String
        Dim TemplateID As String
        Dim ExemptionID As String
        Dim StepID As String
        Dim PlaceOfBirth As String
        Dim Perhomeadd As String
        Dim PerMunicipality As String
        Dim PerProvince As String
        Dim PerZipcode As String
        Dim PerTelNo As String
        Dim PersonalWeb As String
        Dim IsSSS As Short
        Dim IsPagIbig As Short
        Dim IsPh As Short
        Dim IsTin As Short
        Dim IsGSIS As Short
        Dim AppointedDate As Date
        Dim DateResigned As Date
        Dim DateRetired As Date
        Dim ConfidentialityID As String
        Dim BaccalaurateRate As String
        Dim MasteralRate As String
        Dim DoctoralRate As String
        Dim TAXRATE As String
        Dim GSISEE As String
        Dim GSISER As String
        Dim GSISEC As String
        Dim SSSEE As String
        Dim SSSER As String
        Dim SSSEC As String
        Dim PHEE As String
        Dim PHER As String
        Dim PAGIBIGEE As String
        Dim PAGIBIGER As String
        Dim SSS_FIXED As String
        Dim GSIS_FIXED As String
        Dim PH_FIXED As String
        Dim PAGIBIG_FIXED As String
        Dim TAX_FIXED As String
        Dim GSISPolicyNo As String
        Dim PRCID As String
        Dim ScheduleID As String
        Dim AccountNo As String
    End Structure


    Public Structure employee_family_background
        Dim FGID As Integer
        Dim EmployeeID As String
        Dim MotherCompany As String
        Dim MotherCompanyAddress As String
        Dim MotherTelNo As String
        Dim MotherEmail As String
        Dim EmergencyContactPerson As String
        Dim EmergencyAddress As String
        Dim EmergencyTelNo As String
        Dim EmergencyMobileNo As String
        Dim FatherEmail As String
        Dim MotherLastName As String
        Dim MotherFirstName As String
        Dim MotherMiddleName As String
        Dim MotherOccupation As String
        Dim SpouseEmployer As String
        Dim SpouseBusiness As String
        Dim SpouseBusTelNo As String
        Dim FatherLastName As String
        Dim FatherFirstName As String
        Dim FatherMiddleName As String
        Dim FatherOccupation As String
        Dim FatherCompany As String
        Dim FatherCompanyAddress As String
        Dim FatherTelNo As String
        Dim SpouseTelNo As String
        Dim SpouseEmail As String
        Dim SpouseOccupation As String
        Dim SpouseCompany As String
        Dim SpouseLastName As String
        Dim SpouseFirstName As String
        Dim SpouseMiddleName As String
        Dim SpouseRelationship As String
        Dim SpouseAddress As String
    End Structure

    Public Structure employees_children
        Dim ec_id As Integer
        Dim ec_name As String
        Dim ec_dateofbirth As Date
        Dim e_id As String
    End Structure

    Public Structure employees_edu
        Dim EntryID As Integer
        Dim employee_id As String
        Dim grade_school As String
        Dim grade_school_addres As String
        Dim grade_school_inclusive_dates As String
        Dim grade_school_year_graduated As String
        Dim grade_school_highest_grade As String
        Dim grade_school_honors As String

        Dim secondary_school As String
        Dim secondary_school_address As String
        Dim secondary_school_inclusive_dates As String
        Dim secondary_school_year_graduated As String
        Dim secondary_highest_grade As String
        Dim secondary_honors As String

        Dim college_school As String
        Dim college_school_address As String
        Dim college_school_course As String
        Dim college_school_inclusive_dates As String
        Dim college_school_year_graduated As String
        Dim college_highest_grade As String
        Dim college_honors As String

        Dim vocational_school As String
        Dim vocational_school_address As String
        Dim vocational_school_course As String
        Dim vocational_school_inclusive_dates As String
        Dim vocational_school_year_graduated As String
        Dim vocational_highest_grade As String
        Dim vocational_honors As String

        Dim graduated_school As String
        Dim graduated_school_address As String
        Dim graduated_school_course As String
        Dim graduated_school_inclusive_dates As String
        Dim graduated_school_year_graduated As String
        Dim graduated_highest_grade As String
        Dim graduated_honors As String

        Dim doctorate_school As String
        Dim doctorate_school_address As String
        Dim doctorate_school_course As String
        Dim doctorate_school_inclusive_dates As String
        Dim doctorate_school_year_graduated As String
        Dim doctorate_highest_grade As String
        Dim doctorate_honors As String
    End Structure

    Public Structure employee_schedules
        Dim IndexID As Integer
        Dim EmployeeID As String
        Dim DayID As String
        Dim TimeIn As String
        Dim TimeOut As String
        Dim BreakIn As String
        Dim BreakOut As String
    End Structure

    Public Structure civil_service_eligibility
        Dim ServiceID As Integer
        Dim TransDate As Date
        Dim EmployeeID As String
        Dim Service As String
        Dim Rating As String
        Dim ExamDate As String
        Dim ExamPlace As String
        Dim LicenseNo As String
        Dim DateRelease As String
        Dim Attachment As String
        Dim Data As Byte
        Dim UserID As String
        Dim ModifiedBy As String
        Dim ModifiedDate As Date
    End Structure

    Public Structure training_programs
        Dim IndexID As Integer
        Dim EmployeeID As String
        Dim Programs As String
        Dim InclusiveDateFrom As Date
        Dim InclusiveDateTo As Date
        Dim NumberOfHours As Decimal
        Dim Sponsore As String
        Dim WithCertificate As Short
    End Structure

    Public Structure voluntary_work_involvement
        Dim IndexID As Integer
        Dim EmployeeID As String
        Dim Organization As String
        Dim InclusiveDateFrom As Date
        Dim InclusiveDateTo As Date
        Dim Position As String
        Dim TotalHours As String
    End Structure

    Public Structure pay_other_information
        Dim IndexID As Integer
        Dim EmployeeID As String
        Dim SpecialSkill As String
        Dim Recognition As String
        Dim Association As String
    End Structure

    Public Structure employee_personal_reference
        Dim EntryID As Integer
        Dim EmployeeID As String
        Dim pr_Name As String
        Dim pr_Address As String
        Dim pr_Company As String
        Dim pr_TelNo As String
    End Structure


    Public Function GetNewEmployeeID() As String
        Dim sNewID As String

        Dim sSQL As String = "SELECT CASE WHEN (SELECT COUNT(*) FROM employees) = 0 THEN '4LOOP.001' " & _
        "ELSE CONCAT('4LOOP.',LEFT('000',(LENGTH('000') - LENGTH(CONVERT( (CONVERT( RIGHT((SELECT MAX(e_id) FROM employees),LENGTH('000')) , SIGNED) + 1) , CHAR))))," & _
                "CONVERT( (CONVERT( RIGHT((SELECT MAX(e_id) FROM employees),LENGTH('000')) , SIGNED) + 1) , CHAR)) END"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            sNewID = vRS(0).ToString()
            GetNewEmployeeID = sNewID
            Debug.Print("EmployeeID: " & sNewID)
        Else
            GetNewEmployeeID = ""
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function Add_Employee(ByVal E As Employees, ByRef last_insert_rowid As String) As Boolean
        On Error GoTo err
        Dim sSQL As String = "INSERT INTO employees(e_idno,device_no,e_title,e_fname,e_lname,e_mname,e_suffix,e_birthdate," & _
            "e_gender,e_civil_status,tp_id,e_hourlyrate,e_dailyrate,e_monthlyrate,e_salaryamount,s_id,branch_id,division_id,DeptID," & _
            "jt_id,e_dateemployed,AppointedDate,TemplateID,StepID,BaccalaurateRate,MasteralRate,DoctoralRate,ExemptionID,SSS_Fixed,IsSSS,e_ssno," & _
            "SSSEC,SSSEE,SSSER,TAX_FIXED,TAXRATE,IsTin,e_tinno,GSIS_FIXED,e_gsisno,GSISEC,GSISEE,GSISER,IsGSIS," & _
            "IsPh,e_philhealthno,PH_FIXED,PHEE,PHER," & _
            "IsPagIbig,e_pagibigno,PAGIBIG_FIXED,PAGIBIGEE,PAGIBIGER,AccountNo," & _
            "InActive,e_dateadded,e_addedby)VALUES ('" & _
            E.e_idno & "','" & E.e_deviceno & "','" & E.e_title & "','" & E.e_fname & "','" & E.e_lname & "','" & E.e_mname & "','" & E.e_suffix & "',@e_birthdate,'" & _
            E.e_gender & "','" & E.e_civil_status & "','" & E.tp_id & "','" & E.e_hourlyrate & "','" & _
            E.e_dailyrate & "','" & E.e_monthlyrate & "','" & E.e_salaryamount & "','" & E.s_id & "','" & E.branch_id & "','" & E.division_id & "','" & E.DeptID & _
            "','" & E.jt_id & "',@e_dateemployed,@AppointedDate,'" & E.TemplateID & "','" & E.StepID & "','" & _
            E.BaccalaurateRate & "','" & E.MasteralRate & "','" & E.DoctoralRate & "','" & E.ExemptionID & "','" & E.SSS_FIXED & "','" & E.IsSSS & "','" & E.e_ssno & "','" & _
            E.SSSEC & "','" & E.SSSEE & "','" & E.SSSER & "','" & E.TAX_FIXED & "','" & E.TAXRATE & "','" & E.IsTin & "','" & E.e_tinno & "','" & E.GSIS_FIXED & "','" & _
            E.e_gsisno & "','" & E.GSISEC & "','" & E.GSISEE & "','" & E.GSISER & "','" & E.IsGSIS & "','" & _
            E.IsPh & "','" & E.e_philhealthno & "','" & E.PH_FIXED & "','" & E.PHEE & "','" & E.PHER & "','" & _
            E.IsPagIbig & "','" & E.e_pagibigno & "','" & E.PAGIBIG_FIXED & "','" & E.PAGIBIGEE & "','" & E.PAGIBIGER & "','" & E.AccountNo & "','" & _
            E.InActive & "',CURDATE(),'" & CURRENT_USER.UserID & "');SELECT last_insert_id();"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@e_dateemployed", MySqlDbType.Date).Value = E.e_dateemployed
        com.Parameters.Add("@AppointedDate", MySqlDbType.Date).Value = E.AppointedDate
        com.Parameters.Add("@e_birthdate", MySqlDbType.Date).Value = E.e_birthdate
        last_insert_rowid = com.ExecuteScalar()
        com.Parameters.Clear()
        con.Close()
        Add_Employee = True
        Exit Function
err:
        Add_Employee = False
        DisplayErrorMsg("modEmployees", "Add_Employee", Err.Number, Err.Description)
    End Function

    Public Function Add_Employee_Family_Background(ByVal FB As employee_family_background) As Boolean
        Dim sSQL As String = "INSERT INTO employee_family_background(EmployeeID,MotherCompany,MotherCompanyAddress,MotherTelNo,MotherEmail," & _
            "EmergencyContactPerson,EmergencyAddress,EmergencyTelNo,EmergencyMobileNo," & _
            "FatherEmail,MotherLastName,MotherFirstName,MotherMiddleName,MotherOccupation,SpouseEmployer,SpouseBusiness,SpouseBusTelNo,FatherLastName,FatherFirstName," & _
            "FatherMiddleName,FatherOccupation,FatherCompany,FatherCompanyAddress,FatherTelNo,SpouseTelNo," & _
            "SpouseEmail,SpouseOccupation,SpouseCompany,SpouseLastName,SpouseFirstName,SpouseMiddleName,SpouseRelationship," & _
            "SpouseAddress) " & _
            "VALUES ('" & FB.EmployeeID & "','" & FB.MotherCompany & "','" & FB.MotherCompanyAddress & "','" & FB.MotherTelNo & "','" & FB.MotherEmail & _
            "','" & FB.EmergencyContactPerson & "','" & FB.EmergencyAddress & "','" & FB.EmergencyTelNo & "','" & FB.EmergencyMobileNo & _
            "','" & FB.FatherEmail & "','" & FB.MotherLastName & "','" & FB.MotherFirstName & "','" & FB.MotherMiddleName & "','" & FB.MotherOccupation & "','" & FB.SpouseEmployer & "','" & FB.SpouseBusiness & "','" & FB.SpouseBusTelNo & "','" & FB.FatherLastName & "','" & FB.FatherFirstName & _
            "','" & FB.FatherMiddleName & "','" & FB.FatherOccupation & "','" & FB.FatherCompany & "','" & FB.FatherCompanyAddress & "','" & FB.FatherTelNo & "','" & FB.SpouseTelNo & _
            "','" & FB.SpouseEmail & "','" & FB.SpouseOccupation & "','" & FB.SpouseCompany & "','" & FB.SpouseLastName & "','" & FB.SpouseFirstName & "','" & FB.SpouseMiddleName & "','" & FB.SpouseRelationship & _
            "','" & FB.SpouseAddress & "')"

        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_Employee_Family_Background = True
        Exit Function
err:
        Add_Employee_Family_Background = False
        DisplayErrorMsg("modFunction", "Add_Employee_Family_Background", Err.Number, Err.Description)
    End Function

    Public Function Add_Employee_Education(ByVal ED As employees_edu) As Boolean
        Dim sSQL As String = "INSERT INTO employees_edu VALUES(null,'" & ED.employee_id & _
            "','" & ED.grade_school & "','" & ED.grade_school_addres & "','" & ED.grade_school_inclusive_dates & "','" & ED.grade_school_year_graduated & "','" & ED.grade_school_highest_grade & "','" & ED.grade_school_honors & _
            "','" & ED.secondary_school & "','" & ED.secondary_school_address & "','" & ED.secondary_school_inclusive_dates & "','" & ED.secondary_school_year_graduated & "','" & ED.secondary_highest_grade & "','" & ED.secondary_honors & _
            "','" & ED.college_school & "','" & ED.college_school_address & "','" & ED.college_school_course & "','" & ED.college_school_inclusive_dates & "','" & ED.college_school_year_graduated & "','" & ED.college_highest_grade & "','" & ED.college_honors & _
            "','" & ED.vocational_school & "','" & ED.vocational_school_address & "','" & ED.vocational_school_course & "','" & ED.vocational_school_inclusive_dates & "','" & ED.vocational_school_year_graduated & "','" & ED.vocational_highest_grade & "','" & ED.vocational_honors & _
            "','" & ED.graduated_school & "','" & ED.graduated_school_address & "','" & ED.graduated_school_course & "','" & ED.graduated_school_inclusive_dates & "','" & ED.graduated_school_year_graduated & "','" & ED.graduated_highest_grade & "','" & ED.graduated_honors & _
            "','" & ED.doctorate_school & "','" & ED.doctorate_school_address & "','" & ED.doctorate_school_course & "','" & ED.doctorate_school_inclusive_dates & "','" & ED.doctorate_school_year_graduated & "','" & ED.doctorate_highest_grade & "','" & ED.doctorate_honors & "')"

        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_Employee_Education = True
        Exit Function
err:
        Add_Employee_Education = False
        DisplayErrorMsg("modFunction", "Add_Employee_Education", Err.Number, Err.Description)
    End Function


    Public Function Add_Employee_Children(ByVal EC As employees_children) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("INSERT INTO employees_children VALUES (null,'" & EC.ec_name & "',@bdate,'" & EC.e_id & "')", con)
        com.Parameters.Add("@bdate", MySqlDbType.Date).Value = EC.ec_dateofbirth
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Add_Employee_Children = True
        Exit Function
err:
        Add_Employee_Children = False
        DisplayErrorMsg("modFunction", "Add_Employee_Children", Err.Number, Err.Description)
    End Function

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

    Public Function Update_Employee_Children(ByVal EC As employees_children) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("UPDATE employees_children SET ec_name='" & EC.ec_name & "',ec_dataofbirth=@bdate WHERE ec_id='" & EC.ec_id & "'", con)
        com.Parameters.Add("@bdate", MySqlDbType.Date).Value = EC.ec_dateofbirth
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Update_Employee_Children = True
        Exit Function
err:
        Update_Employee_Children = False
        DisplayErrorMsg("modFunction", "Update_Employee_Children", Err.Number, Err.Description)
    End Function

    Public Function Delete_Employee_Children(ByVal EmployeeID 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 employees_children WHERE e_id='" & EmployeeID & "'", con)
        com.ExecuteNonQuery()
        con.Close()
        Delete_Employee_Children = True
        Exit Function
err:
        Delete_Employee_Children = False
        DisplayErrorMsg("modFunction", "Delete_Employee_Children", Err.Number, Err.Description)
    End Function


    Public Function Correction_Employee(ByVal e_id As String, ByVal E As Employees) As Boolean
        On Error GoTo err
        Dim sSQL As String = "UPDATE employees SET e_title ='" & E.e_title & "',e_fname='" & E.e_fname & "',e_lname='" & E.e_lname & "',e_mname='" & E.e_mname & "',e_suffix='" & E.e_suffix & "',e_birthdate=@e_birthdate," & _
            "e_gender='" & E.e_gender & "',e_civil_status='" & E.e_civil_status & "',tp_id='" & E.tp_id & "',e_hourlyrate='" & E.e_hourlyrate & "',e_dailyrate='" & E.e_dailyrate & "',e_monthlyrate='" & E.e_monthlyrate & "',e_salaryamount='" & E.e_salaryamount & _
            "',s_id='" & E.s_id & "',branch_id='" & E.branch_id & "',division_id='" & E.division_id & "',DeptID='" & E.DeptID & _
            "',jt_id='" & E.jt_id & "',e_dateemployed=@e_dateemployed,AppointedDate=@AppointedDate,TemplateID='" & E.TemplateID & "',StepID='" & E.StepID & "',BaccalaurateRate='" & E.BaccalaurateRate & _
            "',MasteralRate='" & E.MasteralRate & "',DoctoralRate='" & E.DoctoralRate & "',ExemptionID='" & E.ExemptionID & "',e_ssno='" & E.e_ssno & "',SSS_Fixed='" & E.SSS_FIXED & "',IsSSS='" & E.IsSSS & _
            "',SSSEC='" & E.SSSEC & "',SSSEE='" & E.SSSEE & "',SSSER='" & E.SSSER & "',TAX_FIXED='" & E.TAX_FIXED & "',TAXRATE='" & E.TAXRATE & "',IsTin='" & E.IsTin & "',e_tinno='" & E.e_tinno & _
            "',GSIS_FIXED='" & E.GSIS_FIXED & "',e_gsisno='" & E.e_gsisno & "',GSISEC='" & E.GSISEC & "',GSISEE='" & E.GSISEE & "',GSISER='" & E.GSISER & "',IsGSIS='" & E.IsGSIS & _
            "',IsPh='" & E.IsPh & "',e_philhealthno='" & E.e_philhealthno & "',PH_FIXED='" & E.PH_FIXED & "',PHEE='" & E.PHEE & "',PHER='" & E.PHER & _
            "',IsPagIbig='" & E.IsPagIbig & "',e_pagibigno='" & E.e_pagibigno & "',PAGIBIG_FIXED='" & E.PAGIBIG_FIXED & "',PAGIBIGEE='" & E.PAGIBIGEE & "',PAGIBIGER='" & E.PAGIBIGER & "',AccountNo='" & E.AccountNo & _
            "',InActive='" & E.InActive & "',DateResigned=@DateResigned,DateRetired=@DateRetired,e_datemodified=CURDATE(),e_modifiedby='" & CURRENT_USER.UserID & "' WHERE e_idno='" & e_id & "'"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@e_dateemployed", MySqlDbType.Date).Value = E.e_dateemployed
        com.Parameters.Add("@e_birthdate", MySqlDbType.Date).Value = E.e_birthdate
        com.Parameters.Add("@AppointedDate", MySqlDbType.Date).Value = E.AppointedDate
        com.Parameters.Add("@DateResigned", MySqlDbType.Date).Value = E.DateResigned
        com.Parameters.Add("@DateRetired", MySqlDbType.Date).Value = E.DateRetired
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()

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

    Public Function Update_Employee(ByVal e_id As String, ByVal E As Employees) As Boolean
        Dim sSQL As String = "UPDATE employees SET e_bloodtype = '" & E.e_bloodtype & _
                "',e_cityadd ='" & E.e_cityadd & "',e_email ='" & E.e_email & _
                "',e_fax_number ='" & E.e_fax_number & "',e_homeadd ='" & E.e_homeadd & _
                "',e_mobile_number = '" & E.e_mobile_number & "',e_pagibigno = '" & E.e_pagibigno & _
                "',e_provincialadd = '" & E.e_provincialadd & "',e_zipcode = '" & E.e_zipcode & _
                "',e_cityadd = '" & E.e_cityadd & _
                "',Perhomeadd='" & E.Perhomeadd & "',PerMunicipality='" & E.PerMunicipality & "',PerProvince='" & E.PerProvince & "',PerZipCode='" & E.PerZipcode & _
                "',nationality_id ='" & E.nationality_id & "',PlaceOfBirth='" & E.PlaceOfBirth & "',GSISPolicyNo='" & E.GSISPolicyNo & _
                "',e_religion = '" & E.e_religion & "',InActive='" & E.InActive & "',Height='" & E.Height & _
                "',Weight='" & E.Weigth & "',device_no='" & E.e_deviceno & "',e_datemodified=CURDATE(),e_modifiedby='" & CURRENT_USER.UserID & "' WHERE e_idno='" & e_id & "'"
        If ExecuteQry(sSQL) Then
            Update_Employee = True
        Else
            Update_Employee = False
        End If
    End Function

    Public Function Update_Employee_Family_Background(ByVal EmployeeID As String, ByVal FB As employee_family_background) As Boolean
        On Error GoTo err
        Dim sSQL As String = "UPDATE employee_family_background SET MotherCompany='" & FB.MotherCompany & "',MotherCompanyAddress='" & FB.MotherCompanyAddress & "',MotherTelNo='" & FB.MotherTelNo & "',MotherEmail='" & FB.MotherEmail & _
            "',EmergencyContactPerson='" & FB.EmergencyContactPerson & "',EmergencyAddress='" & FB.EmergencyAddress & "',EmergencyTelNo='" & FB.EmergencyTelNo & "',EmergencyMobileNo='" & FB.EmergencyMobileNo & _
            "',FatherEmail='" & FB.FatherEmail & "',MotherLastName='" & FB.MotherLastName & "',MotherFirstName='" & FB.MotherFirstName & "',MotherMiddleName='" & FB.MotherMiddleName & "',MotherOccupation='" & FB.MotherOccupation & _
            "',SpouseEmployer='" & FB.SpouseEmployer & "',SpouseBusiness='" & FB.SpouseBusiness & "',SpouseBusTelNo='" & FB.SpouseBusTelNo & "',FatherLastName='" & FB.FatherLastName & "',FatherFirstName='" & FB.FatherFirstName & _
            "',FatherMiddleName='" & FB.FatherMiddleName & "',FatherOccupation='" & FB.FatherOccupation & "',FatherCompany='" & FB.FatherCompany & "',FatherCompanyAddress='" & FB.FatherCompanyAddress & "',FatherTelNo='" & FB.FatherTelNo & _
            "',SpouseTelNo='" & FB.SpouseTelNo & "',SpouseEmail='" & FB.SpouseEmail & "',SpouseOccupation='" & FB.SpouseOccupation & "',SpouseCompany='" & FB.SpouseCompany & "',SpouseLastName='" & FB.SpouseLastName & "',SpouseFirstName='" & FB.SpouseFirstName & _
            "',SpouseMiddleName='" & FB.SpouseMiddleName & "',SpouseRelationship='" & FB.SpouseMiddleName & "',SpouseAddress='" & FB.SpouseAddress & "' WHERE EmployeeID='" & EmployeeID & "'"


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


    Public Function Update_Employee_Education(ByVal EmployeeID As String, ByVal ED As employees_edu) As Boolean
        On Error GoTo err

        Dim sSQL As String = "UPDATE employees_edu SET grade_school='" & ED.grade_school & "',grade_school_address='" & ED.grade_school_addres & "',grade_school_inclusive_dates='" & ED.grade_school_inclusive_dates & "',grade_school_year_graduated='" & ED.grade_school_year_graduated & _
            "',secondary_school='" & ED.secondary_school & "',secondary_school_address='" & ED.secondary_school_address & "',secondary_school_inclusive_dates='" & ED.secondary_school_inclusive_dates & "',secondary_school_year_graduated='" & ED.secondary_school_year_graduated & _
            "',college_school='" & ED.college_school & "',college_school_address='" & ED.college_school_address & "',college_school_course='" & ED.college_school_course & "',college_school_inclusive_dates='" & ED.college_school_inclusive_dates & "',college_school_year_graduated='" & ED.college_school_year_graduated & _
            "',vocational_school='" & ED.vocational_school & "',vocational_school_address='" & ED.vocational_school_address & "',vocational_school_course='" & ED.vocational_school_course & "',vocational_school_inclusive_dates='" & ED.vocational_school_inclusive_dates & "',vocational_school_year_graduated='" & ED.vocational_school_year_graduated & _
            "',graduated_school='" & ED.graduated_school & "',graduated_school_address='" & ED.graduated_school_address & "',graduated_school_course='" & ED.graduated_school_course & "',graduated_school_inclusive_dates='" & ED.graduated_school_inclusive_dates & "',graduated_school_year_graduated='" & ED.graduated_school_year_graduated & _
            "',doctorate_school='" & ED.doctorate_school & "',doctorate_school_address='" & ED.doctorate_school_address & "',doctorate_school_course='" & ED.doctorate_school_course & "',doctorate_school_inclusive_dates='" & ED.doctorate_school_inclusive_dates & "',doctorate_school_year_graduated='" & ED.doctorate_school_year_graduated & _
            "',grade_school_highest_grade='" & ED.grade_school_highest_grade & "',grade_school_honors='" & ED.grade_school_honors & _
            "',secondary_highest_grade='" & ED.secondary_highest_grade & "',secondary_honors='" & ED.secondary_honors & _
            "',college_highest_grade='" & ED.college_highest_grade & "',college_honors='" & ED.college_honors & _
            "',vocational_highest_grade='" & ED.vocational_highest_grade & "',vocational_honors='" & ED.vocational_honors & _
            "',graduated_highest_grade='" & ED.graduated_highest_grade & "',graduated_honors='" & ED.graduated_honors & _
            "',doctorate_highest_grade='" & ED.doctorate_highest_grade & "',doctorate_honors='" & ED.doctorate_honors & _
            "' WHERE employee_id='" & EmployeeID & "'"


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


    Public Function Update_Employee_ID(ByVal old_e_id As String, ByVal new_e_id As String) As Boolean
        If ExecuteQry("UPDATE employees SET e_idno='" & new_e_id & "' WHERE e_idno='" & old_e_id & "'") Then
            Update_Employee_ID = True
        Else
            Update_Employee_ID = False
        End If
    End Function

    Public Function Delete_Employee(ByVal e_id 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 employees WHERE e_id='" & e_id & "'", con)
        com.ExecuteNonQuery()
        con.Close()
        Delete_Employee = True
        Exit Function
err:
        Delete_Employee = False
        DisplayErrorMsg("modEmployees", "Delete_Employee", Err.Number, Err.Description)
    End Function


    Public Function GetEmployeeProfile(ByVal e_id As String, ByRef E As Employees) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM employees WHERE e_idno='" & e_id & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader
        vRS.Read()
        If vRS.HasRows Then
            With E
                .e_idno = vRS("e_idno").ToString()
                .e_lname = vRS("e_lname").ToString()
                .e_mname = vRS("e_mname").ToString()
                .e_idno = vRS("e_idno").ToString()
                .e_fname = vRS("e_fname").ToString()
                .e_gender = vRS("e_gender").ToString()
                .e_suffix = vRS("e_suffix").ToString()
                .e_title = vRS("e_title").ToString()
                .e_birthdate = vRS("e_birthdate").ToString()

                .branch_id = vRS("branch_id").ToString()
                .DeptID = vRS("DeptID").ToString()
                .division_id = vRS("division_id").ToString()
                .jt_id = vRS("jt_id").ToString()
                .s_id = vRS("s_id").ToString()
                .e_civil_status = vRS("e_civil_status").ToString()
                .tp_id = vRS("tp_id").ToString()
                .ExemptionID = vRS("ExemptionID").ToString()
                .TemplateID = vRS("TemplateID").ToString()
                .StepID = vRS("StepID").ToString()
                .ScheduleID = vRS("ScheduleID").ToString()
                .e_deviceno = vRS("device_no").ToString()

                .e_hourlyrate = vRS("e_hourlyrate").ToString()
                .e_dailyrate = vRS("e_dailyrate").ToString()
                .e_monthlyrate = vRS("e_monthlyrate").ToString()
                .e_salaryamount = vRS("e_salaryamount").ToString()
                .BaccalaurateRate = vRS("BaccalaurateRate").ToString()
                .MasteralRate = vRS("MasteralRate").ToString()
                .DoctoralRate = vRS("DoctoralRate").ToString()

                .AccountNo = vRS("AccountNo").ToString()

                .IsGSIS = vRS("IsGSIS").ToString()
                .e_gsisno = vRS("e_gsisno").ToString()
                .GSIS_FIXED = vRS("GSIS_FIXED").ToString()
                .GSISEC = vRS("GSISEC").ToString()
                .GSISEE = vRS("GSISEE").ToString()
                .GSISER = vRS("GSISER").ToString()

                .IsPagIbig = vRS("IsPagIbig").ToString()
                .e_pagibigno = vRS("e_pagibigno").ToString()
                .PAGIBIG_FIXED = vRS("PAGIBIG_FIXED").ToString()
                .PAGIBIGEE = vRS("PAGIBIGEE").ToString()
                .PAGIBIGER = vRS("PAGIBIGER").ToString()

                .IsSSS = vRS("IsSSS").ToString()
                .e_ssno = vRS("e_ssno").ToString()
                .SSS_FIXED = vRS("SSS_FIXED").ToString()
                .SSSEC = vRS("SSSEC").ToString()
                .SSSEE = vRS("SSSEE").ToString()
                .SSSER = vRS("SSSER").ToString()

                .IsPh = vRS("IsPh").ToString()
                .e_philhealthno = vRS("e_philhealthno").ToString()
                .PH_FIXED = vRS("PH_FIXED").ToString()
                .PHEE = vRS("PHEE").ToString()
                .PHER = vRS("PHER").ToString()

                .IsTin = vRS("IsTin").ToString()
                .e_tinno = vRS("e_tinno").ToString()
                .TAX_FIXED = vRS("TAX_FIXED").ToString()
                .TAXRATE = vRS("TAXRATE").ToString()

                .e_dateemployed = vRS("e_dateemployed").ToString()
                If Len(vRS("AppointedDate").ToString()) > 0 Then
                    .AppointedDate = vRS("AppointedDate").ToString()
                End If


                If Len(vRS("DateResigned").ToString()) > 1 Then
                    .DateResigned = vRS("DateResigned").ToString()
                End If

                If Len(vRS("DateRetired").ToString()) > 1 Then
                    .DateResigned = vRS("DateResigned").ToString()
                End If

                If Len(vRS("DateLastPromotion").ToString()) > 1 Then
                    .DateLastPromotion = vRS("DateLastPromotion").ToString()
                End If

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

    Public Function GetEmployeeOtherInfo(ByVal e_id As String, ByRef E As Employees) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM employees WHERE e_idno='" & e_id & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With E
                .e_bloodtype = vRS("e_bloodtype").ToString()
                .e_mobile_number = vRS("e_mobile_number").ToString()
                .e_email = vRS("e_email").ToString()
                .e_fax_number = vRS("e_fax_number").ToString()
                .nationality_id = vRS("nationality_id").ToString()
                .e_religion = vRS("e_religion").ToString()
                .Height = vRS("Height").ToString()
                .Weigth = vRS("Weight").ToString()
                .ScheduleID = vRS("ScheduleID").ToString()

                .e_homeadd = vRS("e_homeadd").ToString()
                .e_cityadd = vRS("e_cityadd").ToString()
                .e_provincialadd = vRS("e_provincialadd").ToString()
                .e_zipcode = vRS("e_zipcode").ToString()
                .e_cityadd = vRS("e_cityadd").ToString()

                .Perhomeadd = vRS("Perhomeadd").ToString()
                .PerMunicipality = vRS("PerMunicipality").ToString()
                .PerProvince = vRS("PerProvince").ToString()
                .PerTelNo = vRS("PerTelNo").ToString()
                .PerZipcode = vRS("PerZipcode").ToString()

                .GSISPolicyNo = vRS("GSISPolicyNo").ToString()
                .PlaceOfBirth = vRS("PlaceOfBirth").ToString()
            End With

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

    Public Function GetEmployeeEducation(ByVal EmployeeID As String, ByRef ED As employees_edu) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM employees_edu WHERE employee_id='" & EmployeeID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With ED
                .EntryID = vRS("EntryID").ToString()

                .grade_school = vRS("grade_school").ToString()
                .grade_school_addres = vRS("grade_school_address").ToString()
                .grade_school_inclusive_dates = vRS("grade_school_inclusive_dates").ToString()
                .grade_school_year_graduated = vRS("grade_school_year_graduated").ToString()

                .graduated_school = vRS("graduated_school").ToString()
                .graduated_school_address = vRS("graduated_school_address").ToString()
                .graduated_school_course = vRS("graduated_school_course").ToString()
                .graduated_school_inclusive_dates = vRS("graduated_school_inclusive_dates").ToString()
                .graduated_school_year_graduated = vRS("graduated_school_year_graduated").ToString()

                .secondary_school = vRS("secondary_school").ToString()
                .secondary_school_address = vRS("secondary_school_address").ToString()
                .secondary_school_inclusive_dates = vRS("secondary_school_inclusive_dates").ToString()
                .secondary_school_year_graduated = vRS("secondary_school_year_graduated").ToString()

                .vocational_school = vRS("vocational_school").ToString()
                .vocational_school_address = vRS("vocational_school_address").ToString()
                .vocational_school_course = vRS("vocational_school_course").ToString()
                .vocational_school_inclusive_dates = vRS("vocational_school_inclusive_dates").ToString()
                .vocational_school_year_graduated = vRS("vocational_school_year_graduated").ToString()

                .college_school = vRS("college_school").ToString()
                .college_school_address = vRS("college_school_address").ToString()
                .college_school_course = vRS("college_school_course").ToString()
                .college_school_inclusive_dates = vRS("college_school_inclusive_dates").ToString()
                .college_school_year_graduated = vRS("college_school_year_graduated").ToString()

                .doctorate_school = vRS("doctorate_school").ToString()
                .doctorate_school_address = vRS("doctorate_school_address").ToString()
                .doctorate_school_course = vRS("doctorate_school_course").ToString()
                .doctorate_school_inclusive_dates = vRS("doctorate_school_inclusive_dates").ToString()
                .doctorate_school_year_graduated = vRS("doctorate_school_year_graduated").ToString()

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


    Public Function GetEmployeeFamilyBackground(ByVal EmployeeID As String, ByRef FB As employee_family_background) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM employee_family_background WHERE EmployeeID='" & EmployeeID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With FB
                .FGID = vRS("FGID").ToString()
                .EmployeeID = vRS("EmployeeID").ToString()

                .EmergencyAddress = vRS("EmergencyAddress").ToString()
                .EmergencyContactPerson = vRS("EmergencyContactPerson").ToString()
                .EmergencyMobileNo = vRS("EmergencyMobileNo").ToString()
                .EmergencyTelNo = vRS("EmergencyTelNo").ToString()

                .FatherEmail = vRS("FatherEmail").ToString()
                .FatherFirstName = vRS("FatherFirstName").ToString()
                .FatherLastName = vRS("FatherLastName").ToString()
                .FatherMiddleName = vRS("FatherMiddleName").ToString()
                .FatherOccupation = vRS("FatherOccupation").ToString()
                .FatherTelNo = vRS("FatherTelNo").ToString()
                .FatherCompany = vRS("FatherCompany").ToString()
                .FatherCompanyAddress = vRS("FatherCompanyAddress").ToString()

                .MotherCompany = vRS("MotherCompany").ToString()
                .MotherCompanyAddress = vRS("MotherCompanyAddress").ToString()
                .MotherEmail = vRS("MotherEmail").ToString()
                .MotherFirstName = vRS("MotherFirstName").ToString()
                .MotherLastName = vRS("MotherLastName").ToString()
                .MotherMiddleName = vRS("MotherMiddleName").ToString()
                .MotherOccupation = vRS("MotherOccupation").ToString()
                .MotherTelNo = vRS("MotherTelNo").ToString()

                .SpouseAddress = vRS("SpouseAddress").ToString()
                .SpouseBusiness = vRS("SpouseBusiness").ToString()
                .SpouseBusTelNo = vRS("SpouseBusTelNo").ToString()
                .SpouseCompany = vRS("SpouseCompany").ToString()
                .SpouseEmail = vRS("SpouseEmail").ToString()
                .SpouseEmployer = vRS("SpouseEmployer").ToString()
                .SpouseFirstName = vRS("SpouseFirstName").ToString()
                .SpouseLastName = vRS("SpouseLastName").ToString()
                .SpouseMiddleName = vRS("SpouseMiddleName").ToString()
                .SpouseOccupation = vRS("SpouseOccupation").ToString()
                .SpouseRelationship = vRS("SpouseRelationship").ToString()
                .SpouseTelNo = vRS("SpouseTelNo").ToString()
            End With
            GetEmployeeFamilyBackground = True
        Else
            GetEmployeeFamilyBackground = False
        End If
        vRS.Close()
        con.Close()
    End Function


    Public Function EmployeeIDExistByID(ByVal E_id As String) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM employees WHERE e_idno = '" & E_id & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            EmployeeIDExistByID = True
        Else
            EmployeeIDExistByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function EmployeeEduExistByID(ByVal E_id As String) As Boolean
        If QueryHasRows("SELECT * FROM employees_edu WHERE employee_id='" & E_id & "' LIMIT 1;") Then
            EmployeeEduExistByID = True
        Else
            EmployeeEduExistByID = False
        End If
    End Function

    Public Function EmployeeFamilyBackgroundExistByID(ByVal E_id As String) As Boolean
        If QueryHasRows("SELECT * FROM employee_family_background WHERE EmployeeID='" & E_id & "' LIMIT 1;") Then
            EmployeeFamilyBackgroundExistByID = True
        Else
            EmployeeFamilyBackgroundExistByID = False
        End If
    End Function

    '=================================================================================================
    '                       EMPLOYEE CIVIL SERVICE ELIGIBILITY
    '=================================================================================================

    Public Function Add_Civil_Service_Eligibility(ByVal CSC As civil_service_eligibility) As Boolean

        Dim sSQL As String = "INSERT INTO civil_service_eligibility VALUES (null,CURDATE(),'" & CSC.EmployeeID & "','" & CSC.Service & "','" & CSC.Rating & "','" & CSC.ExamDate & "','" & CSC.ExamPlace & "','" & CSC.LicenseNo & "','" & CSC.DateRelease & "',null,null,'" & CURRENT_USER.UserID & "',null,null)"

        If ExecuteQry(sSQL) Then
            Add_Civil_Service_Eligibility = True
        Else
            Add_Civil_Service_Eligibility = False
        End If
    End Function

    Public Function Update_Civil_Service_Eligibility(ByVal CSC As civil_service_eligibility) As Boolean

        Dim sSQL As String = "UPDATE civil_service_eligibility SET Service='" & CSC.Service & "',Rating='" & CSC.Rating & "', ExamDate='" & CSC.ExamDate & _
            "',ExamPlace='" & CSC.ExamPlace & "',LicenseNo='" & CSC.LicenseNo & "',DateRelease='" & CSC.DateRelease & "','"

        If ExecuteQry(sSQL) Then
            Update_Civil_Service_Eligibility = True
        Else
            Update_Civil_Service_Eligibility = False
        End If
    End Function

    Public Function Delete_Civil_Service_Eligibility(ByVal IndexID As Integer) As Boolean
        Dim sSQL As String = "DELETE FROM civil_service_eligibility WHERE ServiceID"

        If ExecuteQry(sSQL) Then
            Delete_Civil_Service_Eligibility = True
        Else
            Delete_Civil_Service_Eligibility = False
        End If
    End Function

    Public Function GetEmployeeCivilServiceByID(ByVal IndexID As Integer, ByRef CS As civil_service_eligibility) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM civil_service_eligibility WHERE ServiceID='" & IndexID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With CS
                .EmployeeID = vRS("EmployeeID").ToString()
                .ExamDate = vRS("ExamDate").ToString()
                .ExamPlace = vRS("ExamPlace").ToString()
                .LicenseNo = vRS("LicenseNo").ToString()
                .DateRelease = vRS("DateRelease").ToString()
                .Rating = vRS("Rating").ToString()
                .Service = vRS("Service").ToString()
                .ServiceID = vRS("ServiceID").ToString()
                .UserID = vRS("UserID").ToString()
            End With
            GetEmployeeCivilServiceByID = True
        Else
            GetEmployeeCivilServiceByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

    '=================================================================================================
    '                               EMPLOYEE TRAINING PROGRAMS
    '=================================================================================================

    Public Function Add_Training_Programs(ByVal TP As training_programs) As Boolean
        On Error GoTo err
        Dim sSQL As String = "INSERT INTO training_programs VALUES(null,'" & TP.EmployeeID & "','" & TP.Programs & "',@DateFrom,@DateTo,'" & TP.NumberOfHours & "','" & TP.Sponsore & "','" & TP.WithCertificate & "')"
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.AddWithValue("@DateFrom", TP.InclusiveDateFrom)
        com.Parameters.AddWithValue("@DateTo", TP.InclusiveDateTo)
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()

        Add_Training_Programs = True

        Exit Function
err:
        Add_Training_Programs = False
        DisplayErrorMsg("modEmployees", "Add_Training_Programs", Err.Number, Err.Description)
    End Function

    Public Function Update_Training_Programs(ByVal TP As training_programs) As Boolean
        Dim sSQL As String = "UPDATE training_programs SET Programs='" & TP.Programs & "',InclusiveDateFrom=@DateFrom,InclusiveDateTo=@DateTo,NumberOfHours='" & TP.NumberOfHours & "',Sponsore='" & TP.Sponsore & "',WithCertificate='" & TP.WithCertificate & "' WHERE IndexID='" & TP.IndexID & "','"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.AddWithValue("@DateFrom", TP.InclusiveDateFrom)
        com.Parameters.AddWithValue("@DateTo", TP.InclusiveDateTo)
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()

        Update_Training_Programs = True

        Exit Function
err:
        Update_Training_Programs = False
        DisplayErrorMsg("modEmployees", "Update_Training_Programs", Err.Number, Err.Description)
    End Function

    Public Function Delete_Training_Programs(ByVal IndexID As Integer) As Boolean
        If ExecuteQry("DELETE FROM training_programs WHERE IndexID='" & IndexID & "'") Then
            Delete_Training_Programs = True
        Else
            Delete_Training_Programs = False
        End If
    End Function

    Public Function GetEmployeeTrainingProgramByID(ByVal IndexID As Integer, ByRef TP As training_programs) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM training_programs WHERE IndexID='" & IndexID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With TP
                .WithCertificate = vRS("WithCertificate").ToString()
                .Sponsore = vRS("Sponsore").ToString()
                .Programs = vRS("Programs").ToString()
                .NumberOfHours = vRS("NumberOfHours").ToString()
                .IndexID = vRS("IndexID").ToString()
                .InclusiveDateTo = vRS("InclusiveDateTo").ToString()
                .InclusiveDateFrom = vRS("InclusiveDateFrom").ToString()
                .EmployeeID = vRS("EmployeeID").ToString()
            End With
            GetEmployeeTrainingProgramByID = True
        Else
            GetEmployeeTrainingProgramByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

    '=================================================================================================
    '                               VOLUNTARY WORK INVOLVEMENT
    '=================================================================================================

    Public Function Add_Work_Involvement(ByVal VW As voluntary_work_involvement) As Boolean
        On Error GoTo err
        Dim sSQL As String = "INSERT INTO voluntary_work_involvement VALUES (null,'" & VW.EmployeeID & "','" & VW.Organization & "',@DateFrom,@DateTo,'" & VW.Position & "','" & VW.TotalHours & "')"
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.AddWithValue("@DateFrom", VW.InclusiveDateFrom)
        com.Parameters.AddWithValue("@DateTo", VW.InclusiveDateTo)
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Add_Work_Involvement = True
        Exit Function
err:
        Add_Work_Involvement = False
        DisplayErrorMsg("modEmployees", "Add_Work_Involvement", Err.Number, Err.Description)
    End Function

    Public Function Update_Work_Involvement(ByVal VW As voluntary_work_involvement) As Boolean
        On Error GoTo err
        Dim sSQL As String = "UPDATE voluntary_work_involvement SET EmployeeID='" & VW.EmployeeID & "',Organization='" & VW.Organization & "',InclusiveDateFrom=@DateFrom,InclusiveDateTo=@DateTo,Position='" & VW.Position & "',TotalHours='" & VW.TotalHours & "' WHERE IndexID='" & VW.IndexID & "'"
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.AddWithValue("@DateFrom", VW.InclusiveDateFrom)
        com.Parameters.AddWithValue("@DateTo", VW.InclusiveDateTo)
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Update_Work_Involvement = True
        Exit Function
err:
        Update_Work_Involvement = False
        DisplayErrorMsg("modEmployees", "Update_Work_Involvement", Err.Number, Err.Description)
    End Function

    Public Function Delete_Work_Involvement(ByVal IndexID As Integer) As Boolean
        If ExecuteQry("DELETE FROM voluntary_work_involvement WHERE IndexID='" & IndexID & "'") Then
            Delete_Work_Involvement = True
        Else
            Delete_Work_Involvement = False
        End If
    End Function

    Public Function GetEmployeeWorkInvolvementByID(ByVal IndexID As Integer, ByRef VW As voluntary_work_involvement) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM voluntary_work_involvement WHERE IndexID='" & IndexID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With VW
                .IndexID = vRS("IndexID").ToString()
                .EmployeeID = vRS("EmployeeID").ToString()
                .InclusiveDateFrom = vRS("InclusiveDateFrom").ToString()
                .InclusiveDateTo = vRS("InclusiveDateTo").ToString()
                .Organization = vRS("Organization").ToString()
                .Position = vRS("Position").ToString()
                .TotalHours = vRS("TotalHours").ToString()
            End With

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

    '=================================================================================================
    '                               EMPLOYEE OTHER INFORMATION
    '=================================================================================================

    Public Function Add_Employee_Other_Information(ByVal O As pay_other_information) As Boolean
        Dim sSQL As String = "INSERT INTO pay_other_information VALUES(null,'" & O.EmployeeID & "','" & O.SpecialSkill & "','" & O.Recognition & "','" & O.Association & "')"
        If ExecuteQry(sSQL) Then
            Add_Employee_Other_Information = True
        Else
            Add_Employee_Other_Information = False
        End If
    End Function

    Public Function Update_Employee_Other_Information(ByVal O As pay_other_information) As Boolean
        Dim sSQL As String = "INSERT INTO pay_other_information VALUES(null,'" & O.EmployeeID & "','" & O.SpecialSkill & "','" & O.Recognition & "','" & O.Association & "')"
        If ExecuteQry(sSQL) Then
            Update_Employee_Other_Information = True
        Else
            Update_Employee_Other_Information = False
        End If
    End Function

    Public Function Delete_Employee_Other_Information(ByVal IndexID As Integer) As Boolean
        If ExecuteQry("DELETE FROM pay_other_information WHERE IndexID='" & IndexID & "'") Then
            Delete_Employee_Other_Information = True
        Else
            Delete_Employee_Other_Information = False
        End If
    End Function

    Public Function Get_Employee_Other_Information(ByVal IndexID As Integer, ByRef O As pay_other_information) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM pay_other_information WHERE IndexID='" & IndexID & " LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With O
                .Association = vRS("Association").ToString()
                .EmployeeID = vRS("EmployeeID").ToString()
                .IndexID = vRS("IndexID").ToString()
                .Recognition = vRS("Recognition").ToString()
                .SpecialSkill = vRS("SpecialSkill").ToString()
            End With
            Get_Employee_Other_Information = True
        Else
            Get_Employee_Other_Information = False
        End If
        vRS.Close()
        con.Close()
    End Function


    '=================================================================================================
    '                               EMPLOYEE PERSONAL REFERENCE
    '=================================================================================================

    Public Function Add_Employee_Personal_Reference(ByVal pr As employee_personal_reference) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("INSERT INTO employee_personal_reference VALUES (null,'" & pr.EmployeeID & "','" & pr.pr_Name & "','" & pr.pr_Address & "','" & pr.pr_Company & "','" & pr.pr_TelNo & "')", con)
        com.ExecuteNonQuery()
        con.Close()
        Add_Employee_Personal_Reference = True
        Exit Function
err:
        Add_Employee_Personal_Reference = False
        DisplayErrorMsg("modFunction", "Add_Employee_Personal_Reference", Err.Number, Err.Description)
    End Function

    Public Function Update_Employee_Personal_Reference(ByVal pr As employee_personal_reference) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("UPDATE employee_personal_reference SET pr_Name='" & pr.pr_Name & "',pr_Address='" & pr.pr_Address & "',pr_Company='" & pr.pr_Company & "',pr_TelNo='" & pr.pr_TelNo & "' WHERE EntryID='" & pr.EntryID & "'", con)
        com.ExecuteNonQuery()
        con.Close()
        Update_Employee_Personal_Reference = True
        Exit Function
err:
        Update_Employee_Personal_Reference = False
        DisplayErrorMsg("modFunction", "Update_Employee_Personal_Reference", Err.Number, Err.Description)
    End Function

    Public Function Delete_Employee_Personal_Reference(ByVal IndexID As Integer) As Boolean
        If ExecuteQry("DELETE FROM pay_other_information WHERE IndexID='" & IndexID & "'") Then
            Delete_Employee_Personal_Reference = True
        Else
            Delete_Employee_Personal_Reference = False
        End If
    End Function

    Public Function GetEmployeePersonalReference(ByVal EntryID As Integer, ByRef pr As employee_personal_reference) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM employee_personal_reference WHERE EntryID='" & EntryID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With pr
                .pr_Address = vRS("pr_Address").ToString()
                .EntryID = vRS("EntryID").ToString()
                .pr_Company = vRS("pr_Company").ToString()
                .pr_Name = vRS("pr_Name").ToString()
                .pr_TelNo = vRS("pr_TelNo").ToString()
            End With
            GetEmployeePersonalReference = True
        Else
            GetEmployeePersonalReference = False
        End If
        vRS.Close()
        con.Close()
    End Function


    '=================================================================================================
    '                               END OF EMPLOYEE PERSONAL DATA SHEET
    '=================================================================================================


    Public Function IsEmployeeActive(ByVal EmployeeID As String) As Boolean
        If QueryHasRows("SELECT * FROM employees WHERE e_idno='" & EmployeeID & "' AND Inactive = 0 LIMIT 1;") Then
            IsEmployeeActive = True
        Else
            IsEmployeeActive = False
        End If
    End Function

    Public Function IsEmployeeResigned(ByVal EmployeeID As String) As Boolean
        If QueryHasRows("SELECT * FROM employees WHERE e_idno='" & EmployeeID & "' AND (DateResigned IS NOT NULL AND DateResigned <> '0001-01-01') LIMIT 1;") Then
            IsEmployeeResigned = True
        Else
            IsEmployeeResigned = False
        End If
    End Function

    Public Function IsEmployeeRetired(ByVal EmployeeID As String) As Boolean
        If QueryHasRows("SELECT * FROM employees WHERE e_idno='" & EmployeeID & "' AND (DateRetired IS NOT NULL AND DateRetired <> '0001-01-01') LIMIT 1;") Then
            IsEmployeeRetired = True
        Else
            IsEmployeeRetired = False
        End If
    End Function

    Public Function fn_EmployeeName(ByVal EmployeeID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT fn_EmployeeName('" & EmployeeID & "')", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_EmployeeName = vRS(0).ToString()
        Else
            fn_EmployeeName = ""
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function fn_EmployeeDepartmentCode(ByVal empID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT d_code FROM departments WHERE d_id = (Select DeptID from employees where e_idno = '" & empID & "' LIMIT 1) LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        Dim returnVal As String = String.Empty
        vRS.Read()
        If vRS.HasRows Then
            returnVal = vRS("d_code").ToString()
        Else
            returnVal = ""
        End If
        vRS.Close()
        con.Close()
        Return returnVal
    End Function

    Public Function fn_EmployeeJobPosition(ByVal empID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT jt_name FROM job_titles WHERE jt_id = (Select jt_id from employees where e_idno = '" & empID & "' LIMIT 1) LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        Dim returnVal As String = String.Empty
        vRS.Read()
        If vRS.HasRows Then
            returnVal = vRS("jt_name").ToString()
        Else
            returnVal = ""
        End If
        vRS.Close()
        con.Close()
        Return returnVal
    End Function

    Public Function fn_EmployeeJobPositionCode(ByVal empID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT jt_code FROM job_titles WHERE jt_id = (Select jt_id from employees where e_idno = '" & empID & "' LIMIT 1) LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        Dim returnVal As String = String.Empty
        vRS.Read()
        If vRS.HasRows Then
            returnVal = vRS("jt_code").ToString()
        Else
            returnVal = ""
        End If
        vRS.Close()
        con.Close()
        Return returnVal
    End Function

    Public Function fn_EmployeeBranch(ByVal empID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT branch_name FROM branch WHERE branch_id = (Select branch_id from employees where e_idno = '" & empID & "' LIMIT 1) LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        Dim returnVal As String = String.Empty
        vRS.Read()
        If vRS.HasRows Then
            returnVal = vRS("branch_name").ToString()
        Else
            returnVal = ""
        End If
        vRS.Close()
        con.Close()
        Return returnVal
    End Function

    Public Function fn_EmployeeDivision(ByVal empID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT div_name FROM divisions WHERE div_id = (Select division_id from employees where e_idno = '" & empID & "' LIMIT 1) LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        Dim returnVal As String = String.Empty
        vRS.Read()
        If vRS.HasRows Then
            returnVal = vRS("div_name")
        Else
            returnVal = ""
        End If
        vRS.Close()
        con.Close()
        Return returnVal
    End Function

    Public Function GetLastInsertEmployeeNo(ByVal Last_Insert_ID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT e_idno FROM employees WHERE e_id='" & Last_Insert_ID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader
        vRS.Read()
        If vRS.HasRows Then
            GetLastInsertEmployeeNo = vRS(0).ToString()
        Else
            GetLastInsertEmployeeNo = ""
        End If
        vRS.Close()
        con.Close()
    End Function


    Public Function fn_EmployeeDeviceNo(ByVal EmployeeID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT device_no FROM employees WHERE e_idno='" & EmployeeID & "' LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_EmployeeDeviceNo = vRS(0).ToString()
        Else
            fn_EmployeeDeviceNo = ""
        End If
        vRS.Close()
        con.Close()
    End Function


End Module