clttype.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 = "CltType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 TypeId  Int 客户类别ID
'2 TypeName  Varchar 100 客户类别名称
'3 SortId  Int 排序ID

Public TypeId As Integer
Public TypeName As String
Public SortId As Integer

Public Sub Init()
  TypeId = 0
  TypeName = ""
  SortId = 0
End Sub

'删除客户类别
Public Sub Delete(ByVal TmpTypeId As Integer)
  DB_Connect
  
  SqlStmt = "DELETE FROM CltType WHERE TypeId=" _
          + Trim(Str(TmpTypeId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpTypeName As String) As Integer
  DB_Connect

  SqlStmt = "SELECT TypeId FROM CltType WHERE TypeName='" _
          + Trim(TmpTypeName) + "'"
  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 TmpTypeId As Integer) As String
  DB_Connect
  
  SqlStmt = "SELECT TypeName FROM CltType WHERE TypeId =" _
          + Trim(Str(TmpTypeId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetName = TrimStr(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 TypeId FROM CltType ORDER BY TypeId"
  OdbcExt (SqlStmt)
  i = 1
  Do While SQLFetch(Hstmt) <> 100
    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 Function In_DB(ByVal TmpTypeName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT * FROM CltType WHERE TypeName='" _
          + Trim(TmpTypeName) + "'"
  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 Function Insert() As Integer
  TypeId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO CltType Values(" _
          + Trim(Str(TypeId)) + ",'" + Trim(TypeName) + "'," _
          + Trim(Str(SortId)) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  Insert = TypeId
  DB_Disconnect
End Function

Public Sub LoadCltType()
  Erase Arr_CltTypeName
  Erase Arr_CltTypeId
  ReDim Arr_CltTypeName(0)
  ReDim Arr_CltTypeId(0)
  
  DB_Connect
  SqlStmt = "SELECT * FROM CltType ORDER BY SortId"
  OdbcExt (SqlStmt)
  i = 0
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_CltTypeId(i + 1)
    Arr_CltTypeId(i) = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_CltTypeName(i + 1)
    Arr_CltTypeName(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

'更新数据
Public Sub Update(ByVal TmpTypeId As Integer)
  DB_Connect
  
  SqlStmt = "UPDATE CltType set TypeName='" + Trim(TypeName) _
          + "',SortId=" + Trim(Str(SortId)) + " WHERE TypeId=" _
          + Trim(Str(TmpTypeId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub