contact.cls

vb做的数据库 客户管理系统

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Contact"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Public Id As Long
Public CustId As Integer
Public CName As String
Public Sex As String
Public TitleId As Integer
Public Office As String
Public Memo As String
Public Input_date As String
Public Input_time As String

Public Sub Init()  '初始化成员变量
  Id = 0
  CustId = 0
  CName = ""
  Sex = ""
  TitleId = 0
  Office = ""
  Memo = ""
  Input_date = ""
  Input_time = ""
End Sub

'统计全部客户数据
Public Function CountContact() As Long
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT Count(Id) FROM Contact"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    CountContact = 0
  Else
    CountContact = rs.Fields(0)
  End If
End Function

'删除指定的联系人信息，TmpId表示记录编号
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM Contact WHERE Id=" + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub

'删除指定客户下的所有联系人信息，TmpCustId表示指定的客户单位编号
Public Sub DeleteByCustId(ByVal TmpCustId As Long)
  SqlStmt = "DELETE FROM Contact WHERE CustId=" + Trim(Str(TmpCustId))
  SQLExt (SqlStmt)
End Sub

'获取指定的客户信息，TmpId表示记录编号
Public Sub GetInfo(ByVal TmpId As Long)
  Dim rs As New ADODB.Recordset
  
  CustId = Tmp_CustId
  CName = Tmp_CName
  
  SqlStmt = "SELECT * FROM Contact WHERE Id=" + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    CustId = rs.Fields(1)
    
    CName = Trim(rs.Fields(2))
    If IsNull(rs.Fields(3)) Then
      Sex = ""
    Else
      Sex = Trim(rs.Fields(3))
    End If
    If IsNull(rs.Fields(4)) Then
      TitleId = 0
    Else
      TitleId = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      Office = ""
    Else
      Office = Trim(rs.Fields(5))
    End If

    If IsNull(rs.Fields(6)) Then
      Memo = ""
    Else
      Memo = Trim(rs.Fields(6))
    End If
    If IsNull(rs.Fields(7)) Then
      Input_date = ""
    Else
      Input_date = Trim(rs.Fields(7))
    End If
    If IsNull(rs.Fields(8)) Then
      Input_time = ""
    Else
      Input_time = Trim(rs.Fields(8))
    End If
  Else
    Init
  End If
End Sub

'判断指定的联系人记录是否存在，CustId表示指定的客户单位编号编号

Public Function In_DB(ByVal Tmp_CustId As Long, _
                      ByVal Tmp_CName As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM Contact WHERE CustId=" + Trim(Str(Tmp_CustId)) _
          + " And CName='" + Trim(Tmp_CName) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    In_DB = True
  Else
    In_DB = False
  End If
End Function

Public Sub Insert()   '插入新记录
 dd = Date
  dd = Format(dd, "yyyy-mm-dd")
  t = Time
  tt = Trim(Str(Hour(t))) + ":" + Trim(Str(Minute(t))) + ":" + Trim(Str(Second(t)))
 
  SqlStmt = "INSERT INTO Contact(CustId,CName,Sex,TitleId,Office,Memo1," _
          + "Input_date,Input_time) Values(" + Trim(Str(CustId)) + ",'" + Trim(CName) + "','" + Trim(Sex) _
          + "'," + Trim(Str(TitleId)) + ",'" + Trim(Office) + "','" _
          + Trim(Memo) + "','" + Trim(dd) _
          + "','" + Trim(tt) + "')"
  SQLExt (SqlStmt)
End Sub

Public Sub Update(ByVal OriId As Long)  '修改指定的记录
  dd = Date
  dd = Format(dd, "yyyy-mm-dd")
  t = Time
  tt = Trim(Str(Hour(t))) + ":" + Trim(Str(Minute(t))) + ":" + Trim(Str(Second(t)))
    
  SqlStmt = "UPDATE Contact SET CustId=" + Trim(Str(CustId)) _
          + ",CName='" + Trim(CName) + "',Sex='" + Trim(Sex) _
          + "',TitleId=" + Trim(Str(TitleId)) + ",Office='" _
          + Trim(Office) + "',Memo1='" + Trim(Memo) _
          + "',Input_date='" + Trim(dd) + "',Input_time='" _
          + Trim(tt) + "' WHERE Id=" + Trim(Str(OriId))
  SQLExt (SqlStmt)
End Sub

'根据联系人编号取得联系人职务名称，TmpId表示记录编号
Public Function GetTitle(ByVal TmpId As Integer) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT t.TypeName FROM Types t,Contact c " _
        + "WHERE c.Id=" + Trim(Str(TmpId)) + " AND c.TitleId=t.Id"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetTitle = Trim(rs.Fields(0))
  Else
    GetTitle = ""
  End If
End Function