clsdepartment.cls

这是一个利用VB编写的中小企业ERP。功能比较全

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)
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "DELETE FROM Department WHERE Dep_Id=" + Trim(Str(TmpId))
    
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsDepartment As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
    
    '断开与数据库的连接
    MyDB.closeDB
End Sub

Public Sub GetInfo(TmpId As Long)
    Dep_Id = TmpId
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT * FROM Department WHERE Dep_Id=" + Trim(Str(TmpId))
    '执行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
    '断开与数据库的连接
    MyDB.closeDB
End Sub

Public Function GetName(ByVal TmpId As Long) As String
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Dep_Name FROM Department WHERE Dep_Id=" _
               + Trim(Str(TmpId))
    '执行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
    '断开与数据库的连接
    MyDB.closeDB
End Function

Public Function GetNewId() As Long
    Dim TmpId As Long
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Dep_Id FROM Department ORDER BY Dep_Id"
    '执行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
            '断开与数据库的连接
            MyDB.closeDB
            Exit Function
        End If
        rsDepartment.MoveNext
    Loop
    '断开与数据库的连接
    MyDB.closeDB
    
    GetNewId = i
End Function

Public Function HaveEmp(ByVal TmpDepId As Long) As Boolean
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT * FROM Employees WHERE Dep_id=" + Trim(Str(TmpDepId))
    '执行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
    '断开与数据库的连接
    MyDB.closeDB
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Dep_Id FROM Department WHERE UpperId=" _
               + Trim(Str(TmpUpperId))
    
    '执行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
    '断开与数据库的连接
    MyDB.closeDB
End Function

Public Function In_DB(ByVal DepName As String, _
                      ByVal DepUpper As Long) As Boolean
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Dep_Id FROM Department WHERE Dep_Name='" + Trim(DepName) _
               + "' and UpperId=" + Trim(Str(DepUpper))
    '执行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
    '断开与数据库的连接
    MyDB.closeDB
End Function

Public Function Insert() As Long
    Dim Dep_Id As Long
    '生成新的编号
    Dep_Id = GetNewId
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "INSERT INTO Department VALUES(" + Trim(Str(Dep_Id)) + ",'" _
               + Trim(Dep_name) + "','" + Trim(Describe) _
               + "'," + Trim(Str(UpperId)) + ")"
    
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsDepartment As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
    '断开与数据库的连接
    MyDB.closeDB
    Insert = Dep_Id
End Function

Public Sub Load_Department_ByUpper(UpperId As Long)
    Dim i As Integer
    '初始化部门数组
    Erase Arr_DepName
    Erase Arr_DepDescribe
    Erase Arr_DepId
    ReDim Arr_DepName(0)
    ReDim Arr_DepDescribe(0)
    ReDim Arr_DepId(0)
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Dep_Id,Dep_Name,Describe FROM Department WHERE UpperId=" _
               + Trim(Str(UpperId)) + " ORDER BY Dep_Id"
    '执行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
    '断开与数据库的连接
    MyDB.closeDB
End Sub

'更新数据

Public Sub Update(ByVal TmpId As Long)
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "UPDATE Department SET Dep_Name='" + Trim(Dep_name) _
               + "',Describe='" + Trim(Describe) _
               + "' WHERE Dep_Id=" + Trim(Str(TmpId))
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsDepartment As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsDepartment, True)
    '断开与数据库的连接
    MyDB.closeDB
End Sub