HRIS Company API

company.vb

Option Explicit On

Imports MySql.Data.MySqlClient

Module modCompany
    Public Structure Company_Info
        Dim ci_id As Integer
        Dim ci_name As String
        Dim ci_fax As String
        Dim ci_telephone As String
        Dim ci_city As String
        Dim ci_streetadd As String
        Dim ci_provincialadd As String
        Dim ci_zipcode As String
        'Dim ci_late_deduction As String
        Dim tp_id As Integer
        Dim ci_restday1 As Integer
        Dim ci_restday2 As Integer
        Dim ci_restday3 As Integer
        Dim ci_reg_ot As Decimal
        Dim ci_rd_ot As Decimal
        Dim ci_work_sp As Decimal
        Dim ci_work_sp_rd As Decimal
        Dim ci_work_rh As Decimal
        Dim ci_work_rh_rd As Decimal
        Dim ci_ssno As String
        Dim ci_gsisno As String
        Dim ci_pagibigno As String
        Dim ci_tinno As String
        Dim ci_philhealthno As String
        Dim ci_apply_break_logs As String
        Dim ci_modifiedby As Integer
        Dim ci_datemodified As Date
        Dim ci_owner As String
        Dim ci_website As String
        Dim office_code As String
    End Structure

    Public Structure Other_Companies
        Dim CompanyID As Integer
        Dim Code As String
        Dim Name As String
        Dim ShortName As String
        Dim Address As String
        Dim URL As String
        Dim Email As String
        Dim FaxNo As String
        Dim TelNo As String
        Dim InActive As Short
    End Structure

    Public Function Add_Company_Info(ByVal C As Company_Info) As Boolean
        Dim sSQL As String = "INSERT INTO `company_info`(`ci_name`,`ci_fax`,`ci_telephone`,`ci_city`,`ci_streetadd`,`ci_provincialadd`,`ci_zipcode`,`ci_ssno`,`ci_gsisno`,`ci_pagibigno`,`ci_tinno`,`ci_philhealthno`,`ci_owner`,`ci_website`,office_code)VALUES('" & _
                        C.ci_name & "','" & C.ci_fax & "','" & C.ci_telephone & "','" & C.ci_city & "','" & C.ci_streetadd & "','" & C.ci_provincialadd & "','" & C.ci_zipcode & "','" & C.ci_ssno & "','" & C.ci_gsisno & "','" & C.ci_pagibigno & "','" & C.ci_tinno & "','" & C.ci_philhealthno & "','" & C.ci_owner & "','" & C.ci_website & "','" & C.office_code & "')"

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

    Public Function Update_Company_Info(ByRef C As Company_Info) As Boolean
        Dim sSQL As String = "UPDATE `company_info` SET `ci_name` ='" & C.ci_name & "',office_code='" & C.office_code & "',`ci_fax` ='" & C.ci_fax & "',`ci_telephone` ='" & C.ci_telephone & "',`ci_city` ='" & C.ci_city & "',`ci_streetadd` = '" & C.ci_streetadd & "',`ci_provincialadd` ='" & C.ci_provincialadd & "',`ci_zipcode` ='" & C.ci_zipcode & "',`ci_ssno` ='" & C.ci_ssno & "',`ci_gsisno` ='" & C.ci_gsisno & "',`ci_pagibigno` ='" & C.ci_pagibigno & "',`ci_tinno` ='" & C.ci_tinno & "',`ci_philhealthno` = '" & C.ci_philhealthno & "',`ci_modifiedby` ='" & CURRENT_USER.UserID & "',`ci_datemodified` = CURDATE(),`ci_owner` ='" & C.ci_owner & "',`ci_website` ='" & C.ci_website & "'"

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

    Public Function Delete_Company(ByVal ci_id As Integer) As Boolean
        Dim sSQL As String = "DELETE FROM company_info WHERE ci_id='" & ci_id & "'"

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

    Public Function isCompanySetup() As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM `company_info` LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            isCompanySetup = True
        Else
            isCompanySetup = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function GetCompanyInfoByID(ByRef C As Company_Info) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM `company_info` LIMIT 1;", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With C
                .ci_apply_break_logs = vRS("ci_apply_break_logs").ToString()
                .ci_city = vRS("ci_city").ToString()
                .ci_fax = vRS("ci_fax").ToString()
                .ci_gsisno = vRS("ci_gsisno").ToString()
                .ci_id = vRS("ci_id").ToString()
                '.ci_late_deduction = vRS("ci_late_deduction").ToString()
                .ci_name = vRS("ci_name").ToString()
                .ci_owner = vRS("ci_owner").ToString()
                .ci_provincialadd = vRS("ci_provincialadd").ToString()
                '.ci_rd_ot = CDec(vRS("ci_rd_ot").ToString())
                '.ci_reg_ot = vRS("ci_reg_ot").ToString()
                .ci_telephone = vRS("ci_telephone").ToString()
                .ci_streetadd = vRS("ci_streetadd").ToString()
                .ci_zipcode = vRS("ci_zipcode").ToString()
                '.tp_id = vRS("tp_id").ToString()
                '.ci_restday1 = vRS("ci_restday1").ToString()
                '.ci_restday2 = vRS("ci_restday2").ToString()
                '.ci_restday3 = vRS("ci_restday3").ToString()
                '.ci_work_sp = vRS("ci_work_sp").ToString()
                '.ci_work_sp_rd = vRS("ci_work_sp_rd").ToString()
                '.ci_work_rh = vRS("ci_work_rh").ToString()
                '.ci_work_rh_rd = vRS("ci_work_rh_rd").ToString()
                .ci_ssno = vRS("ci_ssno").ToString()
                .ci_gsisno = vRS("ci_gsisno").ToString()
                .ci_pagibigno = vRS("ci_pagibigno").ToString()
                .ci_tinno = vRS("ci_tinno").ToString()
                .ci_philhealthno = vRS("ci_philhealthno").ToString()
                .ci_website = vRS("ci_website").ToString()
                .office_code = vRS("office_code").ToString()
            End With
            GetCompanyInfoByID = True
        Else
            GetCompanyInfoByID = False
        End If
        vRS.Close()
        con.Close()
    End Function


    '========================================================================================
    '                                      OTHER COMPANIES
    '========================================================================================


    Public Function Add_OtherCompany_Info(ByVal C As Other_Companies) As Boolean
        Dim sSQL As String = "INSERT INTO `other_companies` VALUES(null,'" & _
                        C.Code & "','" & C.Name & "','" & C.ShortName & "','" & C.Address & "','" & C.URL & "','" & C.Email & "','" & C.FaxNo & "','" & C.TelNo & "','" & C.InActive & "')"

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

    Public Function Update_OtherCompany_Info(ByRef C As Other_Companies) As Boolean
        Dim sSQL As String = "UPDATE `other_companies` SET `Code` ='" & C.Code & "',`Name` ='" & C.Name & "',`ShortName` ='" & C.ShortName & "',`Address` ='" & C.Address & "',`URL` = '" & C.URL & "',`Email` ='" & C.Email & "',`FaxNo` ='" & C.FaxNo & "',`TelNo` ='" & C.TelNo & "',`InActive` ='" & C.InActive & "' WHERE CompanyID='" & C.CompanyID & "'"

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

    Public Function Delete_OtherCompany(ByVal ci_id As Integer) As Boolean
        Dim sSQL As String = "DELETE FROM other_companies WHERE CompanyID='" & ci_id & "'"

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

    Public Function GetOtherCompanyInfoByID(ByVal ID As Integer, ByRef C As Other_Companies) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM other_companies WHERE CompanyID='" & ID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With C
                .Address = vRS("Address").ToString()
                .Code = vRS("Code").ToString()
                .CompanyID = vRS("CompanyID").ToString()
                .Email = vRS("Email").ToString()
                .FaxNo = vRS("FaxNo").ToString()
                .InActive = vRS("InActive").ToString()
                .Name = vRS("Name").ToString()
                .ShortName = vRS("ShortName").ToString()
                .TelNo = vRS("TelNo").ToString()
                .URL = vRS("URL").ToString()
            End With

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

        Exit Function
err:
        DisplayErrorMsg("modCompany", "GetOtherCompanyInfoByID", Err.Number, Err.Description)
    End Function

End Module