leasecar.cls

vb与access数据库的操作实例

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "LeaseCar"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 ContractNo 文本 50 租赁合同编号
'2 CarNo  文本 20 车辆编号
'3 CustId 文本 50 客户编号
'4 LeaseTime  文本 20 租赁时间（日常规格）
'5 LeaseMode  文本 20 租赁模式（日、周、月）
'6 Price1 数字 长整型 模式对应的价格，如果为日租赁模式，为工作日租赁价格
'7 Price2 数字 长整型 模式对应的价格，如果为日租赁模式，为周末租赁价格
'8 WorkDays 数字 整型 工作日天数或者用来表示周数、月份个数
'9 WeekEndCount 数字 整型 周末个数，如果为按周和月则保存超期小时数
'10 Deposit 数字 长整型 押金
'11 DayKM 数字 整型 日限公里数
'12 OutKM 数字 长整型 出车公里数
'13 OPrice1 数字 长整型 超公里价格
'14 OPrice2 数字 长整型 超小时价格
'15 ReturnTime 日期\时间 归还时间（日常规格）
'16 ReturnKM 数字 长整型 归还公里数
'17 OtherCost 数字 长整型  其他花销金额，包括破损的费用
'18 Rate 数字 双精度 享受的折扣
'19 Payment 数字 长整型 实际付款
'20 Total 数字 长整型 总费用
'21 Status 文本 20 状态（出租、审核、归还、确认）
'22 CreateDate 日期\时间 创建时间
'23 UserName 文本 30 经办人
'24 RealRTime 日期\时间 实际归还时间

Public ContractNo As String
Public CarNo As String
Public CustId As String
Public LeaseTime As String
Public LeaseMode As String
Public Price1 As Long
Public Price2 As Long
Public WorkDays As Integer
Public WeekEndCount As Integer
Public Deposit As Long
Public DayKM As Integer
Public OutKM As Long
Public OPrice1 As Long
Public OPrice2 As Long
Public ReturnTime As String
Public ReturnKM As Long
Public OtherCost As Long
Public Rate As Long
Public Payment As Long
Public Total As Long
Public Status As String
Public CreateDate As String
Public UserName As String
Public RealRTime As String

Public Sub Init()
  ContractNo = ""
  CarNo = ""
  CustId = ""
  LeaseTime = Date
  LeaseMode = ""
  Price1 = 0
  Price2 = 0
  WorkDays = 0
  WeekEndCount = 0
  Deposit = 0
  DayKM = 0
  OutKM = 0
  OPrice1 = 0
  OPrice2 = 0
  ReturnTime = ""
  ReturnKM = 0
  OtherCost = 0
  Rate = 1
  Payment = 0
  Total = 0
  Status = "出租"
  CreateDate = Date
  UserName = ""
  RealRTime = ""
End Sub

'删除租赁数据
Public Sub Delete(ByVal TmpCNo As String)
  SqlStmt = "Delete FROM Lease WHERE ContractNo='" + Trim(TmpCNo) + "'"
  SQLExt (SqlStmt)
End Sub

Public Function GetInfo(ByVal TmpCNo As String) As Boolean
  Dim rs As New ADODB.Recordset
  ContractNo = TmpCNo
  SqlStmt = "SELECT * FROM Lease WHERE ContractNo='" + Trim(TmpCNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True Then
    GetInfo = False
    Exit Function
  Else
    If IsNull(rs.Fields(1)) Then
      CarNo = ""
    Else
      CarNo = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      CustId = ""
    Else
      CustId = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      LeaseTime = ""
    Else
      LeaseTime = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      LeaseMode = ""
    Else
      LeaseMode = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      Price1 = 0
    Else
      Price1 = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      Price2 = 0
    Else
      Price2 = rs.Fields(6)
    End If
    If IsNull(rs.Fields(7)) Then
      WorkDays = 0
    Else
      WorkDays = rs.Fields(7)
    End If
    If IsNull(rs.Fields(8)) Then
      WeekEndCount = 0
    Else
      WeekEndCount = rs.Fields(8)
    End If

    If IsNull(rs.Fields(9)) Then
      Deposit = 0
    Else
      Deposit = rs.Fields(9)
    End If
    If IsNull(rs.Fields(10)) Then
      DayKM = 0
    Else
      DayKM = rs.Fields(10)
    End If
    
    If IsNull(rs.Fields(11)) Then
      OutKM = 0
    Else
      OutKM = rs.Fields(11)
    End If
    If IsNull(rs.Fields(12)) Then
      OPrice1 = 0
    Else
      OPrice1 = rs.Fields(12)
    End If
    If IsNull(rs.Fields(13)) Then
      OPrice2 = 0
    Else
      OPrice2 = rs.Fields(13)
    End If
    If IsNull(rs.Fields(14)) Then
      ReturnTime = ""
    Else
      ReturnTime = rs.Fields(14)
    End If
    If IsNull(rs.Fields(15)) Then
      ReturnKM = 0
    Else
      ReturnKM = rs.Fields(15)
    End If
    If IsNull(rs.Fields(16)) Then
      OtherCost = 0
    Else
      OtherCost = rs.Fields(16)
    End If
    If IsNull(rs.Fields(17)) Then
      Rate = 1
    Else
      Rate = rs.Fields(17)
    End If
    If IsNull(rs.Fields(18)) Then
      Payment = 0
    Else
      Payment = rs.Fields(18)
    End If
    If IsNull(rs.Fields(19)) Then
      Total = 0
    Else
      Total = rs.Fields(19)
    End If
    
    If IsNull(rs.Fields(20)) Then
      Status = ""
    Else
      Status = rs.Fields(20)
    End If
    If IsNull(rs.Fields(21)) Then
      CreateDate = ""
    Else
      CreateDate = rs.Fields(21)
    End If
    If IsNull(rs.Fields(22)) Then
      UserName = ""
    Else
      UserName = rs.Fields(22)
    End If
    If IsNull(rs.Fields(23)) Then
      RealRTime = ""
    Else
      RealRTime = rs.Fields(23)
    End If
   
  End If
  GetInfo = True
End Function
'判断是否已经存在此编号信息
Public Function In_DB(ByVal TmpCNo As String) As Boolean
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT * FROM Lease WHERE ContractNo='" + Trim(TmpCNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    In_DB = True
  Else
    In_DB = False
  End If
End Function

Public Sub Insert()
  '设置INSERT语句
  SqlStmt = "INSERT INTO Lease(ContractNo,CarNo,CustId,LeaseTime," & _
     "LeaseMode,Price1,Price2,Rate," & _
     "WorkDays,WeekEndCount,Deposit,DayKM,OutKM,OPrice1,OPrice2," & _
     "ReturnTime,ReturnKM,OtherCost,Payment,Total,Status,CreateDate,UserName)" & _
     " Values('" + Trim(ContractNo) + "','" + Trim(CarNo) + "','" + Trim(CustId) + "','" & _
     Trim(LeaseTime) + "','" + Trim(LeaseMode) + "'," & _
     Trim(Price1) + "," + Trim(Price2) + "," + Trim(Rate) + "," & _
     Trim(WorkDays) + "," + Trim(WeekEndCount) + "," & _
     Trim(Deposit) + "," + Trim(DayKM) + "," + Trim(OutKM) + "," & _
     Trim(OPrice1) + "," + Trim(OPrice2) + ",'" & _
     Trim(ReturnTime) + "'," + Trim(ReturnKM) + "," & _
     Trim(OtherCost) + "," + Trim(Payment) + "," + Trim(Total) + ",'" & _
     Trim(Status) + "','" + Trim(Date) + "','" + Trim(UserName) + "')"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub
'更新
Public Sub Update(ByVal TmpCNo As String)
  '设置UPDATE语句
  SqlStmt = "Update Lease Set CarNo='" + Trim(CarNo) + "'," _
     + "CustId='" + Trim(CustId) + "',LeaseTime='" + Trim(LeaseTime) _
     + "',LeaseMode='" + Trim(LeaseMode) + "',Price1=" + Trim(Price1) + "," _
     + "Price2=" + Trim(Price2) + ",WorkDays=" + Trim(WorkDays) + "," _
     + "WeekEndCount=" + Trim(WeekEndCount) + ",Deposit=" + Trim(Deposit) _
     + ",DayKM=" + Trim(DayKM) _
     + ",OutKM=" + Trim(OutKM) + ",ReturnTime='" + Trim(ReturnTime) + "'," _
     + "ReturnKM=" + Trim(ReturnKM) + ",OtherCost=" + Trim(OtherCost) + "," _
     + "Rate=" + Trim(Rate) + ",Payment=" + Trim(Payment) _
     + ",OPrice1=" + Trim(OPrice1) + ",OPrice2=" + Trim(OPrice2) + "," _
     + "Total=" + Trim(Total) + ",Status='" + Trim(Status) + "'," _
     + "UserName='" + Trim(UserName) + "' WHERE ContractNo='" + Trim(TmpCNo) + "'"
  '执行SQL语句
'  MsgBox (SqlStmt)
  SQLExt (SqlStmt)
End Sub
'更新状态
Public Sub UpdateStatus(ByVal TmpCNo As String)
  '设置UPDATE语句
  SqlStmt = "Update Lease Set Status='" + Trim(Status) + "' WHERE ContractNo='" + Trim(TmpCNo) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub

'续租状态更改
Public Sub UpdateAddStatus(ByVal TmpCNo As String)
  '设置UPDATE语句
  SqlStmt = "Update Lease Set Status='续租',ReturnTime='" + Trim(ReturnTime) + "'," _
      + "WorkDays=" + Trim(WorkDays) + ",WeekEndCount=" + Trim(WeekEndCount) + "," _
      + "Total=" + Trim(Total) + ",UserName='" + Trim(UserName) + "'" _
      + "WHERE ContractNo='" + Trim(TmpCNo) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub
'结算
Public Sub UpdateSettleAccount(ByVal TmpCNo As String)
  '设置UPDATE语句
  SqlStmt = "Update Lease Set RealRTime='" + Trim(RealRTime) + "'," _
     + "ReturnKM=" + Trim(ReturnKM) + ",OtherCost=" + Trim(OtherCost) + "," _
     + "Payment=" + Trim(Payment) + "," _
     + "Total=" + Trim(Total) + ",Status='" + Trim(Status) + "'," _
     + "UserName='" + Trim(UserName) + "' WHERE ContractNo='" + Trim(TmpCNo) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub