cclients.cls

一个客户系统,VB+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 = "CClients"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

Private mCol As Collection '局部变量，保存集合

'将一个“客户”对象加入集合
Public Sub Add(objClient As cClient)
  mCol.Add objClient, "A" & objClient.ID
  '在加入对象时，最好同时加入其“KEY”属性
  '“KEY”属性不可以是数字型，因此在前面随便加
  '一个字母，此处加了一个“A”
End Sub

Public Property Get Item(vntIndexKey As Variant) As cClient
  Set Item = mCol(vntIndexKey)
End Property

Public Property Get Count() As Long
  Count = mCol.Count
End Property

Public Sub Remove(vntIndexKey As Variant)
  mCol.Remove vntIndexKey
End Sub

Public Property Get NewEnum() As IUnknown
  '本属性允许用 For...Each 语法枚举该集合。
  Set NewEnum = mCol.[_NewEnum]
End Property

'清除集合中的全部元素
Public Sub Clear()
  '清除时应倒序清除！
  Dim i As Long
  For i = mCol.Count To 1 Step -1
    mCol.Remove i
  Next i
End Sub

Private Sub Class_Initialize()
  Set mCol = New Collection
End Sub

Private Sub Class_Terminate()
  Set mCol = Nothing
End Sub

'按条件查找客户，以集合类的方式返回
Public Function Find(Optional ByVal lngID As Long = 0, _
                     Optional ByVal strName As String = "", _
                     Optional ByVal lngTypeId As Long = 0) As CClients
                     
  '构造查询SQL
  Dim strSQL As String
  strSQL = "Select ClientInfo.*,ClientType.TypeName from ClientInfo left outer join ClientType "
  strSQL = strSQL & " ON ClientType.TypeID=ClientInfo.TypeID Where "
  
  If lngID <> 0 Then strSQL = strSQL & "ClientInfo.ClientID=" & lngID & " and "
  '如果是按名称查询，则采用“包含”的查询方法
  If strName <> "" Then
    strSQL = strSQL & "ClientInfo.Name like '%" & RealString(strName) & "%' and "
  End If
  If lngTypeId <> 0 Then
    strSQL = strSQL & "ClientInfo.TypeID=" & lngTypeId & " and "
  End If
  strSQL = strSQL & "ClientInfo.ClientId>0"
  
  On Error Resume Next
  '将查询结果加入集合类
  Dim rs As Recordset
  Set rs = g_Conn.Execute(strSQL)
  Dim i As Long
  Dim objClient As cClient
  For i = 1 To rs.RecordCount
    Set objClient = New cClient
    With objClient
      .ID = rs("ClientID").Value
      .Name = Trim(rs("Name").Value)
      .TypeName = Trim(rs("TypeName").Value)
      .TypeID = rs("TypeID").Value
      .Sex = rs("Sex").Value
      .Mobile = Trim(rs("Mobile").Value)
      .Email = Trim(rs("Email").Value)
      .OfficePhone = Trim(rs("OfficePhone").Value)
      .HomePhone = Trim(rs("HomePhone").Value)
      .Fax = Trim(rs("Fax").Value)
      .HomeAdr = Trim(rs("HomeAddress").Value)
      .MailAdr = Trim(rs("MailAddress").Value)
      .ZipCode = Trim(rs("ZipCode").Value)
      .Birthday = rs("Birthday").Value
      .Age = rs("Age").Value
      .BirthdayWarn = rs("BirthdayWarn").Value
      .Work = Trim(rs("Work").Value)
      .Position = Trim(rs("Position").Value)
      .Company = Trim(rs("Company").Value)
      .CompanySite = Trim(rs("CompanySite").Value)
      .SelfSite = Trim(rs("SelfSite").Value)
      .Likes = Trim(rs("Likes").Value)
      .Hate = Trim(rs("Hate").Value)
      .Remark = Trim(rs("Remark").Value)
      .Importance = rs("Importance").Value
      .Friendly = rs("Friendly").Value
      .Satisfaction = rs("Satisfaction").Value
    End With
    Me.Add objClient
    Set objClient = Nothing
    rs.MoveNext
  Next i
  
  Set rs = Nothing
  Set Find = Me
  Err.Clear
End Function