contact.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 = "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 Mobile As String
Public Birth As String
Public Hobby 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 = ""
  Mobile = ""
  Birth = ""
  Hobby = ""
  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

Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM Contact WHERE Id=" + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub

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

'获取指定的客户信息
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
      Mobile = ""
    Else
      Mobile = Trim(rs.Fields(6))
    End If
    If IsNull(rs.Fields(7)) Then
      Birth = ""
    Else
      Birth = Trim(rs.Fields(7))
    End If
  
    If IsNull(rs.Fields(8)) Then
      Hobby = ""
    Else
      Hobby = Trim(rs.Fields(8))
    End If

    If IsNull(rs.Fields(9)) Then
      Memo = ""
    Else
      Memo = Trim(rs.Fields(9))
    End If
    If IsNull(rs.Fields(10)) Then
      Input_date = ""
    Else
      Input_date = Trim(rs.Fields(10))
    End If
    If IsNull(rs.Fields(11)) Then
      Input_time = ""
    Else
      Input_time = Trim(rs.Fields(11))
    End If
  Else
    Init
  End If
End Sub

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,Mobile,Birth,Hobby,Memo1," _
          + "Input_date,Input_time) Values(" + Trim(Str(CustId)) + ",'" + Trim(CName) + "','" + Trim(Sex) _
          + "'," + Trim(Str(TitleId)) + ",'" + Trim(Office) + "','" _
          + Trim(Mobile) + "','" + Trim(Birth) + "','" + Trim(Hobby) _
          + "','" + 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) + "',Mobile='" _
          + Trim(Mobile) + "',Birth='" + Trim(Birth) + "',Hobby='" + Trim(Hobby) _
          + "',Memo1='" + Trim(Memo) _
          + "',Input_date='" + Trim(dd) + "',Input_time='" _
          + Trim(tt) + "' WHERE Id=" + Trim(Str(OriId))
  SQLExt (SqlStmt)
End Sub

'根据联系人编号取得联系人职务名称
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