rewardtype.cls

sql的实例源码

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "RewardType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Classes 数字，主键，整型，标题：奖罚类别，默认值：1
'2 Cname 文本，字段大小：20，标题：奖罚名称，必填字段：是，允许空字符串：否
'3 Bonus  数字，双精度型，标题：奖金，必填字段：否，允许空字符串：是，默认值：0
'4 Fine  数字，双精度型，标题：罚金，必填字段：否，允许空字符串：是，默认值：0

Public Classes As Integer
Public Cname As String
Public Bonus As Double
Public Fine As Double

'初始化
Public Sub Init()
  Classes = 0
  Cname = ""
  Bonus = 0
  Fine = 0
End Sub

'判断奖罚类别名称是否存在
Public Function IsExistClass(ByVal paraCName As String) As Boolean
  Dim rs As New ADODB.Recordset
 
  SQLStmt = "SELECT Classes FROM RewardType WHERE Cname = '" + Trim(StrReplace(paraCName)) _
             + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    IsExistClass = True
  Else
    IsExistClass = False
  End If

  '断开连接
  DBDisconnect
End Function

'读取当前奖罚类别信息
Public Sub GetInfo(ByVal paraCName As String)
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  Cname = paraCName                   '奖罚类别名称
  '使用SELECT语句读取奖罚类别数据
  SQLStmt = "SELECT * FROM RewardType WHERE Cname = '" + Trim(StrReplace(paraCName)) _
          + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    Classes = rs.Fields(0)              '奖罚类别
    Bonus = rs.Fields(2)              '奖金
    Fine = rs.Fields(3)               '罚金
  Else
    Init
  End If

  '断开连接
  DBDisconnect
End Sub

'得到奖金或罚金
Public Function GetRewardWage(ByVal paraClass As Integer) As Double
  Dim rs As New ADODB.Recordset '定义结果集对象
  '使用SELECT语句读取职称数据
  SQLStmt = "SELECT * FROM RewardType WHERE Classes = " + Trim(Str(paraClass))
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    '根据paraClass判断奖罚类型
    If paraClass > 0 Then
      GetRewardWage = rs.Fields(2)
    ElseIf paraClass < 0 Then
      GetRewardWage = rs.Fields(3)
    End If
  Else
    MsgBox "无此奖罚类型", vbInformation, "信息提示"
    GetRewardWage = 0
  End If

  '断开连接
  DBDisconnect
End Function

'生成新记录的奖励编号
Public Function GenNewRewardID() As Integer
  Dim rs As New ADODB.Recordset
  '使用SELECT语句，读取最大的编号
  SQLStmt = "SELECT Max(Classes) FROM RewardType WHERE Classes > 0"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '没有奖励记录的时候，赋值1
    GenNewRewardID = 1
  Else
    '有奖励记录的时候，赋值为表中最大编号 + 1
    GenNewRewardID = rs.Fields(0) + 1
  End If

  '断开连接
  DBDisconnect
End Function

'生成新记录的处罚编号
Public Function GenNewFineID() As Integer
  Dim rs As New ADODB.Recordset
  '使用SELECT语句，读取最小的编号
  SQLStmt = "SELECT Min(Classes) FROM RewardType WHERE Classes < 0"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '没有处罚记录的时候，赋值-1
    GenNewFineID = -1
  Else
    '有处罚记录的时候，赋值为表中最大编号 - 1
    GenNewFineID = rs.Fields(0) - 1
  End If

  '断开连接
  DBDisconnect
End Function

'判断指定的奖罚类别中是否存在员工记录
Public Function HaveEmployee(ByVal paraClass As Integer) As Boolean
  Dim rs As New ADODB.Recordset
  '使用SELECT语句在EmployeeInfo表中读取Classes字段等于paraClass的记录
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE Classes = " + Trim(Str(paraClass))
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    '当有记录，则表明有员工记录，返回True
    HaveEmployee = True
  Else
    '当无记录，则表明无员工记录，返回False
    HaveEmployee = False
  End If

  '断开连接
  DBDisconnect
End Function

'插入一条记录
Public Sub Insert()
  '生成编号
  If WageItemID = 5 Then
    Classes = GenNewRewardID
    Bonus = 0
  ElseIf WageItemID = 6 Then
    Classes = GenNewFineID
    reward = 0
  End If
  '插入操作
  SQLStmt = "INSERT INTO RewardType VALUES(" + Trim(Str(Classes)) + ", '" _
            + Trim(StrReplace(Cname)) + "', " + Trim(Str(Bonus)) + ", " + Trim(Str(Fine)) + ")"
  SQLExt SQLStmt
End Sub

'修改一条记录
Public Sub Update(ByVal paraID As Integer)
  '修改操作
  SQLStmt = "UPDATE RewardType SET Cname = '" + Trim(StrReplace(Cname)) _
          + "', Bonus = " + Trim(Str(Bonus)) _
          + ", Fine = " + Trim(Str(Fine)) _
          + " WHERE Classes = " + Trim(Str(paraID))
  SQLExt SQLStmt
End Sub

'删除一条记录
Public Sub Delete(ByVal paraID As Integer)
  '删除操作
  SQLStmt = "DELETE FROM RewardType WHERE Classes = " + Trim(Str(paraID))
  SQLExt SQLStmt
End Sub