HRIS Department API

department.vb

Option Explicit On

Imports MySql.Data.MySqlClient

Module modDepartment
    Public Structure tDepartment
        Dim d_id As Integer
        Dim d_name As String
        Dim d_addedby As Integer
        Dim d_dateadded As Date
        Dim d_modifiedby As Integer
        Dim d_datemodified As Date
        Dim d_code As String
        Dim short_name As String
        Dim d_head As String
        Dim branch_id As String
        Dim tel_no As String
        Dim fax_no As String
        Dim d_desc As String
        Dim inactive As Short
    End Structure

    Public Function Add_Department(ByVal D As tDepartment) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim sSQL As String = "INSERT INTO `departments`(`d_name`,`d_addedby`,`d_dateadded`,`d_code`,`short_name`,`d_head`,`branch_id`,`tel_no`,`fax_no`,`d_desc`,`inactive`)VALUES(@d_name,'" & _
            CURRENT_USER.UserID & "',CURDATE(),@d_code,@short_name,'" & D.d_head & "','" & D.branch_id & "','" & D.tel_no & "','" & D.fax_no & "',@d_desc,'" & D.inactive & "');"
        Dim com As New MySqlCommand(sSQL, con)

        com.Parameters.Add("@d_name", MySqlDbType.VarChar, 100).Value = D.d_name
        com.Parameters.Add("@d_code", MySqlDbType.VarChar, 100).Value = D.d_code
        com.Parameters.Add("@short_name", MySqlDbType.VarChar, 100).Value = D.short_name
        com.Parameters.Add("@d_desc", MySqlDbType.VarChar, 100).Value = D.d_desc
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Add_Department = True
        Exit Function
err:
        Add_Department = False
        DisplayErrorMsg("modDepartment", "Add_Department", Err.Number, Err.Description)
    End Function

    Public Function Update_Department(ByVal d_id As Integer, ByVal D As tDepartment) As Boolean

        On Error GoTo err

        Dim sSQL As String = "UPDATE `departments` SET `d_name` =@d_name,`d_modifiedby` ='" & CURRENT_USER.UserID & "',`d_datemodified` = CURDATE(),`d_code` =@d_code,`short_name` =@short_name,`d_head` ='" & D.d_head & "',`branch_id` ='" & D.branch_id & _
                            "',`tel_no` = '" & D.tel_no & "',`fax_no` ='" & D.fax_no & "',`d_desc` =@d_desc,`inactive` ='" & D.inactive & "' WHERE d_id='" & D.d_id & "';"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)

        com.Parameters.Add("@d_name", MySqlDbType.VarChar, 100).Value = D.d_name
        com.Parameters.Add("@d_code", MySqlDbType.VarChar, 100).Value = D.d_code
        com.Parameters.Add("@short_name", MySqlDbType.VarChar, 100).Value = D.short_name
        com.Parameters.Add("@d_desc", MySqlDbType.VarChar, 100).Value = D.d_desc
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()
        Update_Department = True
        Exit Function
err:
        Update_Department = False
        DisplayErrorMsg("modDepartment", "Update_Department", Err.Number, Err.Description)
    End Function

    Public Function Delete_Deparment(ByVal d_id As Integer) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("DELETE FROM departments WHERE d_id='" & d_id & "'", con)
        com.ExecuteNonQuery()
        con.Close()
        Delete_Deparment = True
        Exit Function
err:
        Delete_Deparment = False
        DisplayErrorMsg("modDepartment", "Delete_Department", Err.Number, Err.Description)
    End Function

    Public Function DepartmentExistByCode(ByVal DeptCode As String) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM departments WHERE d_code LIKE '%" & DeptCode & "%' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            DepartmentExistByCode = True
        Else
            DepartmentExistByCode = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function GetDepartmentByID(ByVal d_id As String, ByRef D As tDepartment) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM departments WHERE d_id='" & d_id & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With D
                .d_name = vRS("d_name").ToString()
                .d_desc = vRS("d_desc").ToString()
                .d_head = vRS("d_head").ToString()
                .d_id = vRS("d_id").ToString()
                .fax_no = vRS("fax_no").ToString()
                .inactive = vRS("inactive").ToString()
                .short_name = vRS("short_name").ToString()
                .tel_no = vRS("tel_no").ToString()
                .d_code = vRS("d_code").ToString()
                .branch_id = vRS("branch_id").ToString()
            End With
            GetDepartmentByID = True
        Else
            GetDepartmentByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function fn_DepartmentCode(ByVal DeptID 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='" & DeptID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_DepartmentCode = vRS(0).ToString()
        Else
            fn_DepartmentCode = ""
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function fn_DepartmentName(ByVal DeptID As String) As String
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT d_name FROM departments WHERE d_id='" & DeptID & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            fn_DepartmentName = vRS(0).ToString()
        Else
            fn_DepartmentName = ""
        End If
        vRS.Close()
        con.Close()
    End Function

End Module