Option Explicit On

Imports MySql.Data.MySqlClient

Module modPosition
    Public Structure Positions
        Dim jt_id As Integer
        Dim jt_name As String
        Dim jt_addedby As Integer
        Dim jt_dateadded As Date
        Dim jt_modifiedby As Integer
        Dim jt_datemodified As Date
        Dim jt_code As String
        Dim jt_shortname As String
        Dim jt_desc As String
        Dim jt_classid As String
        Dim jt_groupid As String
        Dim inactive As Short
    End Structure

    Public Function Add_Position(ByVal P As Positions) As Boolean
        On Error GoTo err
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim sSQL As String = "INSERT INTO `job_titles`(`jt_name`,`jt_addedby`,`jt_dateadded`,`jt_code`,`jt_shortname`,`jt_desc`,`jt_classid`,`jt_groupid`,inactive)VALUES(@jt_name,'" & CURRENT_USER.UserID & _
            "',CURDATE(),@jt_code,@jt_shortname,@jt_desc,'" & P.jt_classid & "','" & P.jt_groupid & "','" & P.inactive & "')"

        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@jt_name", MySqlDbType.VarChar).Value = P.jt_name
        com.Parameters.Add("@jt_code", MySqlDbType.VarChar).Value = P.jt_code
        com.Parameters.Add("@jt_shortname", MySqlDbType.VarChar).Value = P.jt_shortname
        com.Parameters.Add("@jt_desc", MySqlDbType.VarChar).Value = P.jt_desc
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()

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

    Public Function Update_Position(ByVal jt_id As Integer, ByVal P As Positions) As Boolean
        On Error GoTo err
        Dim sSQL As String = "UPDATE `job_titles` SET `jt_name` =@jt_name,`jt_modifiedby` ='" & CURRENT_USER.UserID & "',`jt_datemodified` =CURDATE(),`jt_code` =@jt_code,`jt_shortname` =@jt_shortname,`jt_desc` =@jt_desc,`jt_classid` ='" & P.jt_classid & "',`jt_groupid` ='" & P.jt_groupid & "',inactive='" & P.inactive & "' WHERE jt_id='" & jt_id & "';"

        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand(sSQL, con)
        com.Parameters.Add("@jt_name", MySqlDbType.VarChar).Value = P.jt_name
        com.Parameters.Add("@jt_code", MySqlDbType.VarChar).Value = P.jt_code
        com.Parameters.Add("@jt_shortname", MySqlDbType.VarChar).Value = P.jt_shortname
        com.Parameters.Add("@jt_desc", MySqlDbType.VarChar).Value = P.jt_desc
        com.ExecuteNonQuery()
        com.Parameters.Clear()
        con.Close()

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

    Public Function Delete_Position(ByVal jt_id As Integer) As Boolean
        If ExecuteQry("DELETE FROM job_titles WHERE jt_id='" & jt_id & "'") Then
            Delete_Position = True
        Else
            Delete_Position = False
        End If
    End Function

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

    Public Function GetPositionByID(ByVal jt_id As String, ByRef P As Positions) As Boolean
        Dim con As New MySqlConnection(DB_CONNECTION_STRING)
        con.Open()
        Dim com As New MySqlCommand("SELECT * FROM job_titles WHERE jt_id='" & jt_id & "' LIMIT 1", con)
        Dim vRS As MySqlDataReader = com.ExecuteReader()
        vRS.Read()
        If vRS.HasRows Then
            With P
                .jt_classid = vRS("jt_classid").ToString()
                .jt_code = vRS("jt_code").ToString()
                .jt_desc = vRS("jt_desc").ToString()
                .jt_groupid = vRS("jt_groupid").ToString()
                .jt_id = vRS("jt_id").ToString()
                .jt_shortname = vRS("jt_shortname").ToString()
                .jt_name = vRS("jt_name").ToString()
                .inactive = BooleanToInt(vRS("inactive").ToString())
            End With
            GetPositionByID = True
        Else
            GetPositionByID = False
        End If
        vRS.Close()
        con.Close()
    End Function

    Public Function CheckIfPositionHasRows(ByVal jt_id As Integer) As Boolean
        Return False
    End Function

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

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

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

End Module