clsemployees.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 = "ClsEmployees"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Emp_Id  Int 员工编号
'2 Emp_Name  Varchar 50  姓名
'3 Photo Image 照片
'4 Sex Char 2  性别
'5 Nationality Varchar 40  民族
Public Emp_Id As Long
Public Emp_Name As String
Public Sex As String
Public Nationality As String
'6 Birth Varchar 20  生日
'7 Political_Party Varchar 40  政治面貌
'8 Culture_Level Varchar 40  文化程度
'9 Marital_Condition Varchar 20  婚姻状况
'10  Family_Place  Varchar 60  籍贯
Public Birth As String
Public Political_Party As String
Public Culture_Level As String
Public Marital_Condition As String
Public Family_Place As String
'11  Id_Card Varchar 20  身份证号
'12  PcID  Varchar 40  档案电脑号
'13  BadgeID Varchar 40  工作证号
'14  Office_phone  Varchar 30  办公电话
'15  Home_phone  Varchar 30  家庭电话
Public Id_Card As String
Public PcId As String
Public BadgeID As String
Public Office_phone As String
Public Home_phone As String
'16  Bp  Varchar 30  寻呼机号码
'17  Mobile  Varchar 30  手机号码
'18  Files_Keep_Org  Varchar 100 档案存放地
'19  Residence Varchar 100 居住地址
'20  Hukou Varchar 100 户口所在地
Public Bp As String
Public Mobile As String
Public Files_Keep_Org As String
Public Residence As String
Public Hukou As String
'21  Postcode  Varchar 10  邮政编码
'22  Police_Post Varchar 50  户口所在地派出所
'23  Street_Office Varchar 50  户口所在地街道办事处
'24  HireDate  Varchar 10  本企业工龄起始日
'25  Working_Years TinyInt 本企业工龄
Public Postcode As String
Public Police_Post As String
Public Street_Office As String
Public HireDate As String
Public Working_Years As Integer
'26  Dep_Id  Smallint  所在部门编号
'27  Position  Varchar 40  工作岗位
'28  Title Varchar 20  职务
'29  State TinyInt 员工状态（1 - 正式员工，2 - 兼职员工，3 - 离职员工，4 - 退休员工）
'30  UpperId Int 上级部门编号
Public Dep_Id As Integer
Public Position As String
Public Title As String
Public State As Integer
Public UpperId As Integer
'31  Contract_Sign_Date  Varchar 10  签订劳动合同日期
'32  Base_Wage Decimal (10,2)  基本工资
'33  Base_Prize  Decimal (10,2)  资金基数
'34  Memo  Varchar 200 备注
'35  Fillin_Person Smallint  填表人编号
'36  Fillin_Time Varchar 20  填表时间（日期+时间）
Public Contract_Sign_Date As String
Public Base_Wage As Single
Public Base_Prize As Single
Public Memo As String
Public Fillin_Person As String
Public Fillin_Time As String

Public Sub Init()
    Emp_Id = 0
    Emp_Name = ""
    Birth = ""
    Sex = ""
    Political_Party = ""
    Culture_Level = ""
    Marital_Condition = ""
    Family_Place = ""
    Id_Card = ""
    PcId = ""
    BadgeID = ""
    Office_phone = ""
    Home_phone = ""
    Bp = ""
    Mobile = ""
    Files_Keep_Org = ""
    Residence = ""
    Hukou = ""
    Postcode = ""
    Police_Post = ""
    Street_Office = ""
    HireDate = ""
    Working_Years = 0
    Dep_Id = -1
    Position = ""
    Title = ""
    State = 0
    UpperId = 0
    Contract_Sign_Date = ""
    Base_Wage = 0
    Base_Prize = 0
    Memo = ""
    Fillin_Person = -1
    Fillin_Time = ""
End Sub

'删除Employees数据

Public Sub Delete(ByVal TmpEmpId As Long)
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "Delete FROM Employees WHERE Emp_Id=" + Trim(Str(TmpEmpId))
    
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    
    '断开与数据库的连接
    MyDB.closeDB
    
    MyFam.DeleteByEmpId (TmpEmpId)
    MyExp.DeleteByEmpId (TmpEmpId)
End Sub

Public Function GetName(ByVal TmpEmpId As Long) As String
    If TmpEmpId <= 0 Then
        GetName = ""
        Exit Function
    End If
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Emp_Name FROM Employees WHERE Emp_Id=" + Trim(Str(TmpEmpId))
    
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    If rsEmployees.RecordCount = 0 Then
        GetName = ""
        Exit Function
    Else
        GetName = rsEmployees.Fields("Emp_Name")
    End If
    
    '断开与数据库的连接
    MyDB.closeDB
End Function

Public Function GetInfo(ByVal TmpEmpId As Long) As Boolean
    If TmpEmpId <= 0 Then
        Init
        GetInfo = False
        Exit Function
    End If
    
    Emp_Id = TmpEmpId
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT * FROM Employees WHERE Emp_Id=" + Trim(Str(TmpEmpId))
    
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    If rsEmployees.RecordCount = 0 Then
        GetInfo = False
        '断开与数据库的连接
        MyDB.closeDB
        Exit Function
    Else
        Emp_Name = rsEmployees.Fields("Emp_NAME")
        
        Sex = rsEmployees.Fields("Sex")
        
        Nationality = rsEmployees.Fields("Nationality")
        
        Birth = rsEmployees.Fields("Birth")
        
        
        Political_Party = rsEmployees.Fields("Political_Party")
        
        Culture_Level = rsEmployees.Fields("Culture_Level")
        
        
        Marital_Condition = rsEmployees.Fields("Marital_Condition")
        
        
        Family_Place = rsEmployees.Fields("Family_Place")
        
        
        Id_Card = rsEmployees.Fields("Id_Card")
        
        
        PcId = rsEmployees.Fields("PcId")
        
        
        BadgeID = rsEmployees.Fields("BadgeID")
        
        
        Office_phone = rsEmployees.Fields("Office_phone")
        
        
        Home_phone = rsEmployees.Fields("Home_phone")
        
        
        Bp = rsEmployees.Fields("Bp")
        
        
        Mobile = rsEmployees.Fields("Mobile")
        
        
        Files_Keep_Org = rsEmployees.Fields("Files_Keep_Org")
        
        
        Residence = rsEmployees.Fields("Residence")
        
        Hukou = rsEmployees.Fields("Hukou")
        
        
        Postcode = rsEmployees.Fields("Postcode")
        
        
        Police_Post = rsEmployees.Fields("Police_Post")
        
        
        Street_Office = rsEmployees.Fields("Street_Office")
        
        
        HireDate = rsEmployees.Fields("HireDate")
        
        
        Working_Years = rsEmployees.Fields("Working_Years")
        
        
        Dep_Id = rsEmployees.Fields("Dep_Id")
        
        
        Position = rsEmployees.Fields("Position")
        
        Title = rsEmployees.Fields("Title")
        
        
        State = rsEmployees.Fields("State")
        
        
        
        UpperId = rsEmployees.Fields("UpperId")
        
        
        Contract_Sign_Date = rsEmployees.Fields("Contract_Sign_Date")
        
        
        Base_Wage = rsEmployees.Fields("Base_Wage")
        
        
        Base_Prize = rsEmployees.Fields("Base_Prize")
        
        
        Memo = rsEmployees.Fields("Memo")
        
        Fillin_Person = rsEmployees.Fields("Fillin_Person")
        
        
        Fillin_Time = rsEmployees.Fields("Fillin_Time")
    End If
    
    
    GetInfo = True
    '断开与数据库的连接
    MyDB.closeDB
End Function

Public Function GetNewId() As Long
    Dim TmpId As Long
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "SELECT Emp_Id FROM Employees ORDER BY Emp_Id"
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    i = 1
    Do While rsEmployees.EOF = False
        
        TmpId = rsEmployees.Fields("Emp_Id")
        If TmpId = i Then
            i = i + 1
        Else
            GetNewId = i
            '断开与数据库的连接
            MyDB.closeDB
            Exit Function
        End If
        rsEmployees.MoveNext
    Loop
    '断开与数据库的连接
    MyDB.closeDB
    
    GetNewId = i
End Function

Public Sub Insert()
    Emp_Id = GetNewId
    
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "INSERT INTO Employees (Emp_Id, Emp_Name, Sex, Nationality," _
               + "Birth, Political_Party, Culture_Level, Marital_Condition," _
               + "Family_Place, Id_Card, PcID, BadgeId, " _
               + "Office_Phone, Home_Phone, Bp, Mobile, " _
               + "Files_Keep_Org, Residence, Hukou, Postcode," _
               + "Police_Post, Street_Office, HireDate, Working_Years," _
               + "Dep_Id, Position,Title,State,UpperId, Contract_Sign_Date, Base_Wage," _
               + "Base_Prize, Memo, Fillin_Person, Fillin_Time)" _
               + " Values(" + Trim(Str(Emp_Id)) + ",'" + Trim(Emp_Name) _
               + "','" + Trim(Sex) + "','" + Trim(Nationality) + "','" _
               + Trim(Birth) + "','" + Trim(Political_Party) + "','" _
               + Trim(Culture_Level) + "','" + Trim(Marital_Condition) _
               + "','" + Trim(Family_Place) + "','" + Trim(Id_Card) + "','" _
               + Trim(PcId) + "','" + Trim(BadgeID) + "','" + Trim(Office_phone) + "','" _
               + Trim(Home_phone) + "','" + Trim(Bp) + "','" + Trim(Mobile) _
               + "','" + Trim(Files_Keep_Org) + "','" + Trim(Residence) + "','" _
               + Trim(Hukou) + "','" + Trim(Postcode) + "','" + Trim(Police_Post) _
               + "','" + Trim(Street_Office) + "','" + Trim(HireDate) + "'," + Trim(Working_Years) + "," _
               + Trim(Str(Dep_Id)) + ",'" + Trim(Position) + "','" _
               + Trim(Title) + "'," + Trim(Str(State)) + "," + Trim(Str(UpperId)) _
               + ",'" + Trim(Contract_Sign_Date) + "'," + Trim(Str(Base_Wage)) + "," + Trim(Str(Base_Prize)) _
               + ",'" + Trim(Memo) + "','" + Trim(Fillin_Person) + "','" + Trim(Fillin_Time) + "')"
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    '断开与数据库的连接
    MyDB.closeDB
End Sub

Public Sub Load_by_Dep(ByVal TmpDepId As Integer)
    Dim i As Integer
    '初始化部门数组
    Erase Arr_EmpId
    ReDim Arr_EmpId(0)
    
    '连接数据库
    MyDB.openDB
    SqlStmt = "SELECT Emp_Id FROM Employees WHERE Dep_Id=" _
               + Trim(Str(TmpDepId)) + " ORDER BY Emp_Id"
    
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    i = 0
    Do Until rsEmployees.EOF
        '读取部门编号
        
        ReDim Preserve Arr_EmpId(i + 1)
        Arr_EmpId(i) = rsEmployees.Fields("Emp_Id")
        
        i = i + 1
        rsEmployees.MoveNext
    Loop
    
    '断开与数据库的连接
    MyDB.closeDB
End Sub

Public Sub Update(ByVal OriEmpId As Integer)
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "Update Employees Set Emp_Name='" + Trim(Emp_Name) _
               + "',Sex='" + Trim(Sex) + "',Nationality='" + Trim(Nationality) _
               + "',Birth='" + Trim(Birth) + "',Political_Party='" + Trim(Political_Party) _
               + "',Culture_Level='" + Trim(Culture_Level) _
               + "',Marital_Condition='" + Trim(Marital_Condition) _
               + "',Family_Place='" + Trim(Family_Place) + "',Id_Card='" + Trim(Id_Card) + "',PcID='" _
               + Trim(PcId) + "',BadgeID='" + Trim(BadgeID) _
               + "',Office_phone='" + Trim(Office_phone) _
               + "',Home_phone='" + Trim(Home_phone) + "',Bp='" _
               + Trim(Bp) + "',Mobile='" + Trim(Mobile) + "',Files_Keep_Org='" + Trim(Files_Keep_Org) _
               + "',Residence='" + Trim(Residence) + "',Hukou='" _
               + Trim(Hukou) + "',Postcode='" + Trim(Postcode) _
               + "',Police_Post='" + Trim(Police_Post) _
               + "',Street_Office='" + Trim(Street_Office) _
               + "',HireDate='" + Trim(HireDate) + "',Working_Years=" _
               + Trim(Working_Years) + ",Dep_Id=" + Trim(Str(Dep_Id)) _
               + ",Position='" + Trim(Position) + "',Title='" _
               + Trim(Title) + "',State=" + Trim(Str(State)) + ",UpperId=" _
               + Trim(Str(UpperId)) + ",Contract_Sign_Date='" _
               + Trim(Contract_Sign_Date) + "',Base_Wage=" _
               + Trim(Str(Base_Wage)) + ",Base_Prize=" + Trim(Str(Base_Prize)) _
               + ",Memo='" + Trim(Memo) + "',Fillin_Person='" _
               + Trim(Fillin_Person) + "',Fillin_Time='" + Trim(Fillin_Time) _
               + "' WHERE Emp_Id=" + Trim(Str(OriEmpId))
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    '断开与数据库的连接
    MyDB.closeDB
End Sub

'更新部门职务

Public Sub Update_Transfer(ByVal OriEmpId As Integer)
    '连接数据库
    MyDB.openDB
    
    SqlStmt = "Update Employees Set Dep_Id=" + Trim(Dep_Id) + ",Title='" _
               + Trim(Title) + "' WHERE Emp_id=" + Trim(OriEmpId)
    '执行SQL语句
    Dim Isexe As Boolean
    Dim rsEmployees As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsEmployees, True)
    
    '断开与数据库的连接
    MyDB.closeDB
End Sub