Class ClsDepartment
    Public Dep_Id As Integer
    Public Dep_name As String
    Public Describe As String
    Public UpperId As Integer
    Public Sub Init()
        Dep_Id = -1
        Dep_name = ""
        Describe = CStr(-1)
        UpperId = -1
    End Sub

    '刪除Department
    Public Sub Delete(ByVal TmpId As Integer)
        Dim SQLString As String
        Dim MSG As String
        SQLString = "DELETE FROM Department WHERE Dep_Id=" & Trim(Str(TmpId))
        ClsOperation.DBOperate(SQLString, MSG)
    End Sub

    Public Sub GetInfo(ByRef TmpId As Integer)
        Dim SQLString As String
        Dim MSG As String
        SQLString = "SELECT * FROM Department WHERE Dep_Id=" & Trim(Str(TmpId))
        Dim DTable As DataTable = ClsOperation.DBOperate(SQLString, MSG)
        If DTable.Rows.Count >= 1 Then
            Dep_name = DTable.Rows(0)("Dep_Name") ' 讀取部門名稱
            Describe = DTable.Rows(0)("Describe") '讀取部門描述
            UpperId = DTable.Rows(0)("UpperId") '讀取上一級部門的編號
        Else
            Init()
        End If
    End Sub

    Public Function GetName(ByVal TmpId As Integer) As String
        Dim SQLString As String
        Dim MSG As String
        SQLString = "SELECT Dep_Name FROM Department WHERE Dep_Id=" & Trim(Str(TmpId))
        Dim DTable As DataTable
        DTable = ClsOperation.DBOperate(SQLString, MSG)
        If DTable.Rows.Count <> 0 Then
            GetName = DTable.Rows(0)("Dep_Name")
        Else
            GetName = ""
        End If
    End Function

    Public Function GetNewId() As Integer
        Dim i As Integer
        Dim TmpId As Integer
        Dim SQLString As String
        Dim MSG As String = ""
        Dim ColVal As String
        SQLString = "SELECT Dep_Id FROM Department ORDER BY Dep_Id"
        Dim DTable As DataTable
        DTable = ClsOperation.DBOperate(SQLString, MSG)
        Dim oRow As DataRow
        i = 1
        For Each oRow In DTable.Rows
            ColVal = oRow("Dep_Id")
            TmpId = Val(ColVal)
            If TmpId = i Then
                i = i + 1
            Else
                GetNewId = i
                Exit Function
            End If
        Next
        GetNewId = i
    End Function
    Public Function HaveEmp(ByVal TmpDepid As Integer) As Boolean
        Dim SQLString As String
        Dim MSG As String
        SQLString = "SELECT * FROM Employees WHERE Dep_id=" & Trim(Str(TmpDepid))
        Dim DTable As DataTable = ClsOperation.DBOperate(SQLString, MSG)
        If DTable.Rows.Count >= 1 Then
            HaveEmp = True
        Else
            HaveEmp = False
        End If
    End Function

    Public Function HaveSon(ByVal TmpUpperId As Integer) As Boolean
        Dim SQLString As String
        Dim MSG As String
        SQLString = "SELECT Dep_Id FROM Department WHERE UpperId=" & Trim(Str(TmpUpperId))
        Dim DTable As DataTable = ClsOperation.DBOperate(SQLString, MSG)
        If DTable.Rows.Count >= 1 Then
            HaveSon = True
        Else
            HaveSon = False
        End If
    End Function

    Public Function In_DB(ByVal DepName As String, ByVal DepUpper As Integer) As Boolean
        Dim SQLString As String
        Dim MSG As String
        SQLString = "SELECT Dep_Id FROM Department WHERE Dep_Name='" & Trim(DepName) & "' and UpperId=" & Trim(Str(DepUpper))
        Dim UserTable As DataTable = ClsOperation.DBOperate(SQLString, MSG)
        If UserTable.Rows.Count >= 1 Then    '判斷用戶是否存在
            In_DB = True
        Else
            In_DB = False
        End If
    End Function

    Public Function Insert() As Integer
        Dim Dep_Id As Integer
        '生成新的編號()
        Dep_Id = GetNewId()
        Dim SQLString As String
        Dim MSG As String
        SQLString = "INSERT INTO Department VALUES(" & Trim(Str(Dep_Id)) & ",'" & Trim(Dep_name) & "','" & Trim(Describe) & "'," & Trim(Str(UpperId)) & ")"
        ClsOperation.DBOperate(SQLString, MSG)
        Insert = Dep_Id
    End Function

    Public Sub Load_Department_ByUpper(ByRef UpperId As Integer)
        Dim i As Short
        '初始化部門數組
        Erase Arr_DepName
        Erase Arr_DepDescribe
        Erase Arr_DepId
        ReDim Arr_DepName(0)
        ReDim Arr_DepDescribe(0)
        ReDim Arr_DepId(0)
        Dim SQLString As String
        Dim MSG As String
        SQLString = "SELECT Dep_Id,Dep_Name,Describe FROM Department WHERE UpperId=" & Trim(Str(UpperId)) & " ORDER BY Dep_Id"
        Dim DTable As DataTable
        DTable = ClsOperation.DBOperate(SQLString, MSG)
        Dim oRow As DataRow
        i = 0
        For Each oRow In DTable.Rows
            ReDim Preserve Arr_DepId(i + 1)
            Arr_DepId(i) = oRow("Dep_Id")
            ReDim Preserve Arr_DepName(i + 1)
            Arr_DepName(i) = oRow("Dep_Name")   '讀取部門名稱
            ReDim Preserve Arr_DepDescribe(i + 1)
            Arr_DepDescribe(i) = oRow("Describe")    '讀取部門描述
            i = i + 1
        Next
    End Sub

    '更新數據
    Public Sub Update(ByVal TmpId As Integer)
        Dim SQLString As String
        Dim MSG As String
        SQLString = "UPDATE Department SET Dep_Name='" & Trim(Dep_name) & "',Describe='" & Trim(Describe) & "' WHERE Dep_Id=" & Trim(Str(TmpId))
        ClsOperation.DBOperate(SQLString, MSG)
    End Sub
End Class