customer.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 = "Customer"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Public CustId As Integer
Public CustName As String
Public CustTypeId As Integer
Public Address As String
Public Postcode As String
Public AreaId As Long
Public BussTypeId As Integer
Public EmpId As Integer

Public Sub Init()
  CustId = 0
  CustName = ""
  CustTypeId = 0
  Address = ""
  Postcode = ""
  AreaId = 0
  BussTypeId = 0
  EmpId = 0
End Sub

'判断指定地域信息是否在客户单位中
Public Function Area_In_Customer(ByVal TmpAreaId As Long) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT CustName FROM Customer WHERE AreaId=" _
          + Trim(Str(TmpAreaId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    Tmp_CustName = Trim(rs.Fields(0))
    Area_In_Customer = Tmp_CustName
  Else
    Area_In_Customer = ""
  End If
End Function

'根据给定的地域计算客户数目,如果TmpAreaId=0，则计算全部单位数
Public Function CountCustomer(ByVal TmpAreaId As Integer) As Integer
  Dim rs As New ADODB.Recordset
  '读取指定地域的信息
  MyArea.GetInfo (TmpAreaId)
  
'统计全部客户数据
  SqlStmt = "SELECT * FROM Customer"
  If TmpAreaId = 0 Then
    SqlStmt = SqlStmt + " ORDER BY CustName"
  Else
    If MyArea.AreaType = 4 Then   '市县
      SqlStmt = SqlStmt + " WHERE AreaId=" + Trim(Str(TmpAreaId)) _
            + " ORDER BY CustName"
    Else '省
      SqlStmt = "SELECT Distinct c.CustName FROM Customer c, Area a" _
             + " WHERE c.AreaId=a.AreaId And a.UpperId=" + Trim(Str(TmpAreaId)) _
             + " ORDER BY c.CustName"
    End If
  End If
  
  Set rs = QueryExt(SqlStmt)
  total_num = 0
  Do Until rs.EOF
    total_num = total_num + 1
    rs.MoveNext
  Loop
  CountCustomer = total_num
End Function

Public Sub Delete(ByVal TmpCustId As Long)
  SqlStmt = "DELETE FROM Customer WHERE CustId=" + Trim(Str(TmpCustId))
  SQLExt (SqlStmt)
End Sub


Public Function GetName(ByVal TmpCustId As Integer) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT CustName FROM Customer WHERE CustId =" _
          + Trim(Str(TmpCustId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetName = Trim(rs.Fields(0))
  Else
    GetName = -1
  End If
End Function


'获取指定的客户单位信息
Public Sub GetInfo(ByVal TmpCustId As Integer)
  Dim rs As New ADODB.Recordset
  CustId = TmpCustId
  
  SqlStmt = "SELECT * FROM Customer WHERE CustId=" + Trim(Str(TmpCustId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    CustName = Trim(rs.Fields(1))
    If IsNull(rs.Fields(2)) Then
      CustTypeId = 0
    Else
      CustTypeId = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      Address = ""
    Else
      Address = Trim(rs.Fields(3))
    End If
    If IsNull(rs.Fields(4)) Then
      Postcode = ""
    Else
      Postcode = Trim(rs.Fields(4))
    End If
    If IsNull(rs.Fields(5)) Then
      AreaId = 0
    Else
      AreaId = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      BussTypeId = 0
    Else
      BussTypeId = rs.Fields(6)
    End If
    If IsNull(rs.Fields(7)) Then
      EmpId = 0
    Else
      EmpId = rs.Fields(7)
    End If
  Else
    Init
  End If
End Sub

Public Function In_DB(ByVal TmpOrg As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM Customer WHERE CustName='" + Trim(TmpOrg) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    In_DB = False
  Else
    In_DB = True
  End If
End Function

Public Sub Insert()
  SqlStmt = "INSERT INTO Customer(CustName,CustTypeId,Address,Postcode,AreaId,BussTypeId,EmpId)" _
          + " Values('" + Trim(CustName) + "'," _
          + Trim(Str(CustTypeId)) + ",'" + Trim(Address) _
          + "','" + Trim(Postcode) + "'," + Trim(Str(AreaId)) _
          + "," + Trim(BussTypeId) + "," + Trim(EmpId) + ")"
  SQLExt (SqlStmt)
End Sub

Public Sub Update(ByVal OriCustId As Long)
  SqlStmt = "UPDATE Customer set CustName='" + Trim(CustName) _
          + "',CustTypeId=" + Trim(CustTypeId) + ",Address='" _
          + Trim(Address) + "',Postcode='" + Trim(Postcode) _
          + "',AreaId=" + Trim(Str(AreaId)) + ",BussTypeId=" _
           + Trim(BussTypeId) + ",EmpId=" + Trim(EmpId) _
          + " WHERE CustId=" + Trim(Str(OriCustId))
  SQLExt (SqlStmt)
End Sub