VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "ClsDepartment"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Public Dep_Id As Long
Public Dep_name As String
Public Describe As String
Public UpperId As Long

Public Sub Init()
  Dep_Id = -1
  Dep_name = ""
  Describe = -1
  UpperId = -1
End Sub

'刪除Department
Public Sub Delete(ByVal TmpId As Long)
   '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "DELETE FROM Department WHERE Dep_Id=" + Trim(Str(TmpId))
          
  '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
 
 '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Sub

Public Sub GetInfo(TmpId As Long)
  Dep_Id = TmpId
  
  '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "SELECT * FROM Department WHERE Dep_Id=" + Trim(Str(TmpId))
    '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  
  If rsDepartment.RecordCount <> 0 Then
    '讀取部門名稱
    Dep_name = rsDepartment.Fields("Dep_name")
    '讀取部門描述)
    Describe = rsDepartment.Fields("Describe")
    '讀取上一級部門的編號
    UpperId = rsDepartment.Fields("UpperId")
  Else
    Init
  End If
 '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Sub

Public Function GetName(ByVal TmpId As Long) As String
  '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "SELECT Dep_Name FROM Department WHERE Dep_Id=" _
          + Trim(Str(TmpId))
    '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  
  If rsDepartment.RecordCount <> 0 Then
   
    GetName = rsDepartment.Fields("Dep_Name")
  Else
    GetName = ""
  End If
 '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  
    '連接數(shù)據(jù)庫
  MyDB.openDB
    
  SqlStmt = "SELECT Dep_Id FROM Department ORDER BY Dep_Id"
      '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  
  i = 1
  Do While rsDepartment.EOF = False

    TmpId = rsDepartment.Fields("Dep_Id")
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
  '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
      Exit Function
    End If
  rsDepartment.MoveNext
  Loop
  '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
  
  GetNewId = i
End Function

Public Function HaveEmp(ByVal TmpDepId As Long) As Boolean
    '連接數(shù)據(jù)庫
  MyDB.openDB

  SqlStmt = "SELECT * FROM Client WHERE ClientGroup=" + Trim(Str(TmpDepId))
        '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)

  If rsDepartment.RecordCount = 0 Then
    HaveEmp = False
  Else
    HaveEmp = True
  End If
  '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
    '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "SELECT Dep_Id FROM Department WHERE UpperId=" _
          + Trim(Str(TmpUpperId))
          
    '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  
  If rsDepartment.RecordCount = 0 Then
    HaveSon = False
  Else
    HaveSon = True
  End If
  '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Function

Public Function In_DB(ByVal DepName As String, _
                      ByVal DepUpper As Long) As Boolean
    '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "SELECT Dep_Id FROM Department WHERE Dep_Name='" + Trim(DepName) _
             + "' and UpperId=" + Trim(Str(DepUpper))
    '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  
  If rsDepartment.RecordCount = 0 Then
    In_DB = False
  Else
    In_DB = True
  End If
 '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Function

Public Function Insert() As Long
  Dim Dep_Id As Long
  '生成新的編號
  Dep_Id = GetNewId
  
 '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "INSERT INTO Department VALUES(" + Trim(Str(Dep_Id)) + ",'" _
            + Trim(Dep_name) + "','" + Trim(Describe) _
            + "'," + Trim(Str(UpperId)) + ")"
            
    '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
 '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
  Insert = Dep_Id
End Function

Public Sub Load_Department_ByUpper(UpperId As Long)
  Dim i As Integer
  '初始化部門數(shù)組
  Erase Arr_DepName
  Erase Arr_DepDescribe
  Erase Arr_DepId
  ReDim Arr_DepName(0)
  ReDim Arr_DepDescribe(0)
  ReDim Arr_DepId(0)
  
 '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "SELECT Dep_Id,Dep_Name,Describe FROM Department WHERE UpperId=" _
          + Trim(Str(UpperId)) + " ORDER BY Dep_Id"
      '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  
  i = 0
  Do Until rsDepartment.EOF
    '讀取部門編號
    ReDim Preserve Arr_DepId(i + 1)
    Arr_DepId(i) = rsDepartment.Fields("Dep_Id")
    '讀取部門名稱
    ReDim Preserve Arr_DepName(i + 1)
    Arr_DepName(i) = rsDepartment.Fields("Dep_Name")
    '讀取部門描述
    ReDim Preserve Arr_DepDescribe(i + 1)
    Arr_DepDescribe(i) = rsDepartment.Fields("Describe")
    
    i = i + 1
    rsDepartment.MoveNext
  Loop
 '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Sub

'更新數(shù)據(jù)
Public Sub Update(ByVal TmpId As Long)
   '連接數(shù)據(jù)庫
  MyDB.openDB
  
  SqlStmt = "UPDATE Department SET Dep_Name='" + Trim(Dep_name) _
          + "',Describe='" + Trim(Describe) _
          + "' WHERE Dep_Id=" + Trim(Str(TmpId))
        '執(zhí)行SQL語句
  Dim Isexe As Boolean
  Dim rsDepartment As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
  '斷開與數(shù)據(jù)庫的連接
  MyDB.closeDB
End Sub