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
'1 CltId Int 客户编号
'2 OrgId Int 客户单位编号
'3 CltName Varchar 50  联系人姓名
'4 Sex Varchar 10  性别
'5 JobId Int 职务Id
'6 Office  Varchar 30  单位电话
'7 Mobile  Varchar 30  移动电话
'8 Home  Varchar 30  家庭电话
'9 Level Smallint  客户等级
'10  Address Varchar 200 家庭住址
'11  Postcode  Varchar 20  邮政编码
'12  Id_Card Varchar 60  身份证
'13  Deleted Smallint  删除标记
'14  Input_date  Varchar 40  录入日期
'15  Input_time  Varchar 40  录入时间
Public CltId As Integer
Public OrgId As Integer
Public CltName As String
Public Sex As String
Public JobId As Integer
Public Office As String
Public Mobile As String
Public Home As String
Public Level As Integer
Public Address As String
Public Postcode As String
Public Id_Card As String
Public Deleted As Integer
Public Input_date As String
Public Input_time As String

Public Sub Init()
  CltId = 0
  OrgId = 0
  CltName = ""
  Sex = ""
  JobId = 0
  Office = ""
  Home = ""
  Mobile = ""
  Address = ""
  Postcode = ""
  Id_Card = ""
  Deleted = 0
  Level = 0
  Input_date = ""
  Input_time = ""
End Sub

'统计全部客户数据
Public Function CountClt() As Integer
  DB_Connect
  
  SqlStmt = "SELECT Count(CltId) FROM Contact"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    CountClt = 0
  Else
    ColVal = String(30, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    CountClt = Val(ColVal)
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Sub Delete(ByVal TmpCltId As Integer)
  DB_Connect
  
  SqlStmt = "DELETE FROM Contact WHERE CltId=" + Trim(Str(TmpCltId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpOrgId As Integer, _
                      ByVal TmpName As String) As Integer
  DB_Connect

  SqlStmt = "SELECT CltId FROM Contact WHERE CltName='" _
          + Trim(TmpName) + "' And OrgId=" + Trim(Str(TmpOrgId))
  OdbcExt (SqlStmt)
  '读取结果集中的数据
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetId = Val(ColVal)
  Else
    GetId = -1
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function GetName(ByVal TmpCltId As Integer) As String
  DB_Connect
  
  SqlStmt = "SELECT CltName FROM Contact WHERE CltId =" _
          + Trim(Str(TmpCltId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetName = Val(ColVal)
  Else
    GetName = -1
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpBh As Long
  
  DB_Connect
  
  SqlStmt = "SELECT CltId FROM Contact ORDER BY CltId"
  OdbcExt (SqlStmt)
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpBh = Val(ColVal)
    If TmpBh = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
  GetNewId = i
End Function

'获取指定的客户信息
Public Sub GetInfo(ByVal TmpCltId As Integer)
  DB_Connect
  
  OrgId = Tmp_OrgId
  CltName = Tmp_CltName
  
  SqlStmt = "SELECT * FROM Contact WHERE CltId=" + Trim(Str(TmpCltId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(30, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    OrgId = Val(ColVal)
    
    ColVal = String(60, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    CltName = TrimStr(ColVal)
    
    ColVal = String(30, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Sex = TrimStr(ColVal)
    
    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    JobId = Val(ColVal)
    
    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    Office = TrimStr(ColVal)

    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Mobile = TrimStr(ColVal)
    
    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    Home = TrimStr(ColVal)

    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 9, 1, ColVal, Lench(ColVal), pcblench)
    Level = Val(ColVal)
  
    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
    Address = TrimStr(ColVal)

    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 11, 1, ColVal, Lench(ColVal), pcblench)
    Postcode = TrimStr(ColVal)

    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 12, 1, ColVal, Lench(ColVal), pcblench)
    Id_Card = TrimStr(ColVal)

    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 13, 1, ColVal, Lench(ColVal), pcblench)
    Deleted = TrimStr(ColVal)
  
    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 14, 1, ColVal, Lench(ColVal), pcblench)
    Input_date = TrimStr(ColVal)

    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 15, 1, ColVal, Lench(ColVal), pcblench)
    Input_time = TrimStr(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Function In_DB(ByVal Tmp_OrgId As Integer, _
                      ByVal Tmp_CltName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT * FROM Contact WHERE OrgId=" + Trim(Str(Tmp_OrgId)) _
          + " And CltName='" + Trim(Tmp_CltName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    In_DB = True
  Else
    In_DB = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
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)))
  
  CltId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO Contact Values(" + Trim(Str(CltId)) + "," _
          + Trim(Str(OrgId)) + ",'" + Trim(CltName) + "','" + Trim(Sex) _
          + "'," + Trim(Str(JobId)) + ",'" + Trim(Office) + "','" _
          + Trim(Mobile) + "','" + Trim(Home) + "'," + Trim(Str(Level)) _
          + ",'" + Trim(Address) + "','" + Trim(Postcode) + "','" + Trim(Id_Card) _
          + "'," + Trim(Str(Deleted)) + ",'" + Trim(dd) _
          + "','" + Trim(tt) + "')"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

'判断指定客户职务信息是否在客户数据库中
Public Function Job_In_Clt(ByVal TmpJobId As Integer) As String
  DB_Connect
  
  SqlStmt = "SELECT CltName FROM Contact WHERE JobId=" + Trim(Str(TmpJobId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    Job_In_Clt = TrimStr(ColVal)
  Else
    Job_In_Clt = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

'装入指定客户单位中的所有联系人
Public Sub LoadClt(ByVal TmpOrgId As Integer)
  Dim i As Integer
  
  Erase Arr_Contact
  ReDim Arr_Contact(0)
  If TmpOrgId <= 0 Then
    Exit Sub
  End If
  
  DB_Connect

  SqlStmt = "SELECT CltName FROM Contact WHERE OrgId=" _
            + Trim(Str(TmpOrgId)) + " ORDER BY CltName"
  OdbcExt (SqlStmt)
  i = 0
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    '读取联系人姓名
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_Contact(i + 1)
    Arr_Contact(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

'判断指定客户单位信息是否在客户数据库中
Public Function OrgId_In_Clt(ByVal TmpOrgId As String) As String
  DB_Connect
  
  SqlStmt = "SELECT CltName FROM Contact WHERE OrgId=" + Trim(Str(TmpOrgId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    OrgId_In_Clt = TrimStr(ColVal)
  Else
    OrgId_In_Clt = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Sub SetDeleted(ByVal TmpCltId As Integer, _
                      ByVal TmpDel As Integer)
  DB_Connect
  
  SqlStmt = "UPDATE Contact SET Deleted = " + Trim(Str(TmpDel)) _
          + " WHERE CltId = " + Trim(Str(TmpCltId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Sub Update(ByVal OriCltId As Integer)
  dd = Date
  dd = Format(dd, "yyyy-mm-dd")
  t = Time
  tt = Trim(Str(Hour(t))) + ":" + Trim(Str(Minute(t))) + ":" + Trim(Str(Second(t)))
  
  DB_Connect
  
  SqlStmt = "UPDATE Contact SET OrgId=" + Trim(Str(OrgId)) _
          + ",CltName='" + Trim(CltName) + "',Sex='" + Trim(Sex) _
          + "',JobId=" + Trim(Str(JobId)) + ",Office='" _
          + Trim(Office) + "',Home='" + Trim(Home) + "',Mobile='" _
          + Trim(Mobile) + "',Level=" + Trim(Str(Level)) _
          + ",Address='" + Trim(Address) + "',Postcode='" _
          + Trim(Postcode) + "',Id_Card='" + Trim(Id_Card) _
          + "',Input_date='" + Trim(dd) + "',Input_time='" _
          + Trim(tt) + "' WHERE CltId=" + Trim(Str(OriCltId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub