VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Departs"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Depart_Id  Int 部門編號(hào)
'2 Depart_Name  Varchar 40  部門名稱
'3 Describe  Varchar 400 部門職能描述
'4 UpperId Int 上級(jí)部門編號(hào)

Public Depart_Id As Long
Public Depart_Name As String
Public Describe As String
Public UpperId As Long

Public Sub Init()
  Depart_Id = -1
  Depart_Name = ""
  Describe = ""
  UpperId = -1
End Sub

'刪除
Public Sub Delete(ByVal TmpId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Departs WHERE Depart_Id=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Sub GetInfo(TmpId As Long)
  Depart_Id = TmpId
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM Departs WHERE Depart_Id=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    '讀取部門名稱
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    Depart_Name = TrimStr(ColVal)
    '讀取部門描述
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    Describe = TrimStr(ColVal)
    '讀取上一級(jí)部門的編號(hào)
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Len(ColVal), pcblen)
    UpperId = Val(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetName(ByVal TmpId As Long) As String
  DB_Connect
  
  SqlStmt = "SELECT Depart_Name FROM Departs WHERE Depart_Id=" _
          + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetName = TrimStr(ColVal)
  Else
    GetName = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  '連接數(shù)據(jù)庫
  DB_Connect
  '設(shè)置要執(zhí)行的SQL語句
  SqlStmt = "SELECT Depart_Id FROM Departs ORDER BY Depart_Id"
  '執(zhí)行SQL語句
  OdbcExt (SqlStmt)
  '讀取SELECT語句返回的查詢結(jié)果
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '斷開與數(shù)據(jù)庫的連接
  DB_Disconnect
  '將得到的新編號(hào)作為函數(shù)的返回值
  GetNewId = i
End Function

Public Function HaveStaff(ByVal TmpDepartId As Long) As Boolean
  '連接數(shù)據(jù)庫
  DB_Connect
  '設(shè)置要執(zhí)行的SQL語句
  SqlStmt = "SELECT * FROM Staff WHERE Depart_id=" + Trim(Str(TmpDepartId))
  '執(zhí)行SQL語句
  OdbcExt (SqlStmt)
  '如果SQLFetch()函數(shù)返回SQL_NO_DATA_FOUND，則表示沒有滿足條件查詢結(jié)果
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    HaveStaff = False
  Else
    HaveStaff = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '斷開與數(shù)據(jù)庫的連接
  DB_Disconnect
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT Depart_Id FROM Departs WHERE UpperId=" _
          + Trim(Str(TmpUpperId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    HaveSon = False
  Else
    HaveSon = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Function In_DB(ByVal DepartName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT Depart_Id FROM Departs WHERE Depart_Name='" + Trim(DepartName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    In_DB = False
  Else
    In_DB = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function Insert() As Long
  Dim Depart_Id As Long
  '生成新的編號(hào)
  Depart_Id = GetNewId
  
  DB_Connect
  SqlStmt = "INSERT INTO Departs VALUES(" + Trim(Str(Depart_Id)) + ",'" _
            + Trim(Depart_Name) + "','" + Trim(Describe) _
            + "'," + Trim(Str(UpperId)) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  Insert = Depart_Id
End Function

Public Sub Load_Depart_ByUpper(UpperId As Long)
  Dim i As Integer
  '初始化部門數(shù)組
  Erase Arr_DepartName
  Erase Arr_DepartDescribe
  Erase Arr_DepartId
  ReDim Arr_DepartName(0)
  ReDim Arr_DepartDescribe(0)
  ReDim Arr_DepartId(0)
  
  DB_Connect
  SqlStmt = "SELECT Depart_Id,Depart_Name,Describe FROM Departs WHERE UpperId=" _
          + Trim(Str(UpperId)) + " ORDER BY Depart_Id"
  OdbcExt (SqlStmt)
  i = 0
  Do Until SQLFetch(Hstmt) = 100
    '讀取部門編號(hào)
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_DepartId(i + 1)
    Arr_DepartId(i) = Val(ColVal)
    '讀取部門名稱
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_DepartName(i + 1)
    Arr_DepartName(i) = TrimStr(ColVal)
    '讀取部門描述
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_DepartDescribe(i + 1)
    Arr_DepartDescribe(i) = Val(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

'更新數(shù)據(jù)
Public Sub Update(ByVal TmpId As Long)
  DB_Connect
  
  SqlStmt = "UPDATE Departs SET Depart_Name='" + Trim(Depart_Name) _
          + "',Describe='" + Trim(Describe) _
          + "' WHERE Depart_Id=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub