📄 employees.cls
字号:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
END
Attribute VB_Name = "Employees"
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)
DB_Connect
SqlStmt = "Delete FROM Employees WHERE Emp_Id=" + Trim(Str(TmpEmpId))
OdbcExt (SqlStmt)
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
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
DB_Connect
SqlStmt = "SELECT Emp_Name FROM Employees WHERE Emp_Id=" + Trim(Str(TmpEmpId))
OdbcExt (SqlStmt)
If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
GetName = ""
Exit Function
Else
ColVal = String(100, 0)
Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
GetName = TrimStr(ColVal)
End If
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
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
DB_Connect
SqlStmt = "SELECT * FROM Employees WHERE Emp_Id=" + Trim(Str(TmpEmpId))
OdbcExt (SqlStmt)
If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
GetInfo = False
Exit Function
Else
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
Emp_Name = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
Sex = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
Nationality = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
Birth = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
Political_Party = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
Culture_Level = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 9, 1, ColVal, Lench(ColVal), pcblench)
Marital_Condition = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
Family_Place = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 11, 1, ColVal, Lench(ColVal), pcblench)
Id_Card = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 12, 1, ColVal, Lench(ColVal), pcblench)
PcId = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 13, 1, ColVal, Lench(ColVal), pcblench)
BadgeID = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 14, 1, ColVal, Lench(ColVal), pcblench)
Office_phone = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 15, 1, ColVal, Lench(ColVal), pcblench)
Home_phone = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 16, 1, ColVal, Lench(ColVal), pcblench)
Bp = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 17, 1, ColVal, Lench(ColVal), pcblench)
Mobile = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 18, 1, ColVal, Lench(ColVal), pcblench)
Files_Keep_Org = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 19, 1, ColVal, Lench(ColVal), pcblench)
Residence = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 20, 1, ColVal, Lench(ColVal), pcblench)
Hukou = TrimStr(ColVal)
ColVal = String(4000, 0)
Rc = SQLGetData(Hstmt, 21, 1, ColVal, Lench(ColVal), pcblench)
Postcode = TrimStr(ColVal)
ColVal = String(4000, 0)
Rc = SQLGetData(Hstmt, 22, 1, ColVal, Lench(ColVal), pcblench)
Police_Post = TrimStr(ColVal)
ColVal = String(4000, 0)
Rc = SQLGetData(Hstmt, 23, 1, ColVal, Lench(ColVal), pcblench)
Street_Office = TrimStr(ColVal)
ColVal = String(4000, 0)
Rc = SQLGetData(Hstmt, 24, 1, ColVal, Lench(ColVal), pcblench)
HireDate = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 25, 1, ColVal, Lench(ColVal), pcblench)
Working_Years = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 26, 1, ColVal, Lench(ColVal), pcblench)
Dep_Id = Val(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 27, 1, ColVal, Lench(ColVal), pcblench)
Position = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 28, 1, ColVal, Lench(ColVal), pcblench)
Title = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 29, 1, ColVal, Lench(ColVal), pcblench)
State = Val(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 30, 1, ColVal, Lench(ColVal), pcblench)
UpperId = Val(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 31, 1, ColVal, Lench(ColVal), pcblench)
Contract_Sign_Date = TrimStr(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 32, 1, ColVal, Lench(ColVal), pcblench)
Base_Wage = Val(ColVal)
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 33, 1, ColVal, Lench(ColVal), pcblench)
Base_Prize = Val(ColVal)
ColVal = String(500, 0)
Rc = SQLGetData(Hstmt, 34, 1, ColVal, Lench(ColVal), pcblench)
Memo = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 35, 1, ColVal, Lench(ColVal), pcblench)
Fillin_Person = TrimStr(ColVal)
ColVal = String(400, 0)
Rc = SQLGetData(Hstmt, 36, 1, ColVal, Lench(ColVal), pcblench)
Fillin_Time = TrimStr(ColVal)
End If
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
GetInfo = True
DB_Disconnect
End Function
Public Function GetNewId() As Long
Dim TmpId As Long
DB_Connect
SqlStmt = "SELECT Emp_Id FROM Employees ORDER BY Emp_Id"
OdbcExt (SqlStmt)
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)
DB_Disconnect
GetNewId = i
End Function
Public Sub Insert()
Emp_Id = GetNewId
DB_Connect
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) + "')"
OdbcExt (SqlStmt)
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
End Sub
Public Sub Load_by_Dep(ByVal TmpDepId As Integer)
Dim i As Integer
'初始化部门数组
Erase Arr_EmpId
ReDim Arr_EmpId(0)
DB_Connect
SqlStmt = "SELECT Emp_Id FROM Employees WHERE Dep_Id=" _
+ Trim(Str(TmpDepId)) + " ORDER BY Emp_Id"
OdbcExt (SqlStmt)
i = 0
Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
'读取部门编号
ColVal = String(40, 0)
Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
ReDim Preserve Arr_EmpId(i + 1)
Arr_EmpId(i) = Val(ColVal)
i = i + 1
Loop
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
End Sub
Public Sub Update(ByVal OriEmpId As Integer)
DB_Connect
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))
OdbcExt (SqlStmt)
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
End Sub
'更新部门职务
Public Sub Update_Transfer(ByVal OriEmpId As Integer)
DB_Connect
SqlStmt = "Update Employees Set Dep_Id=" + Trim(Dep_Id) + ",Title='" _
+ Trim(Title) + "' WHERE Emp_id=" + Trim(OriEmpId)
OdbcExt (SqlStmt)
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -