cltjob.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 = "CltJob"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 JobId Int 客户职务编号
'2 Job Varchar 100 客户职务名称
'3 SortId  Int 排序编号
'4 TypeId  Int 客户职务属于何种类别

Public JobId As Integer
Public JobName As String
Public SortId As Integer
Public TypeId As Integer

'删除客户职务
Public Sub Delete(ByVal TmpJobId As String)
  DB_Connect
  
  SqlStmt = "DELETE FROM CltJob WHERE JobId=" + Trim(Str(TmpJobId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpJob As String, _
                      ByVal TmpTypeId As Integer) As Integer
  DB_Connect

  SqlStmt = "SELECT JobId FROM CltJob WHERE JobName='" _
          + Trim(TmpJob) + "' And TypeId=" + Trim(Str(TmpTypeId))
  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 TmpJobId As Integer) As String
  DB_Connect
  
  SqlStmt = "SELECT JobName FROM CltJob 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)
    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 JobId FROM CltJob ORDER BY JobId"
  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 Function In_DB(ByVal TmpJob As String, _
                      ByVal TmpTypeId As Integer) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT * FROM CltJob WHERE JobName='" _
          + Trim(TmpJob) + "' and TypeId=" + Trim(TmpTypeId)
  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
  JobId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO CltJob values(" + Trim(Str(JobId)) _
          + ",'" + Trim(JobName) + "'," + Trim(Str(SortId)) + "," _
          + Trim(TypeId) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  Insert = JobId
  DB_Disconnect
End Function

Public Sub LoadCltJob(Tmp_TypeId As Integer)
  Dim i As Integer
  
  Erase Arr_CltJob
  Erase Arr_CltJobId
  ReDim Arr_CltJob(0)
  ReDim Arr_CltJobId(0)
  
  DB_Connect

  If Tmp_TypeId = 0 Then '全部客户职务
    SqlStmt = "SELECT * FROM CltJob ORDER BY TypeId,SortId"
  Else                   '指定客户类型对应的客户职务
    SqlStmt = "SELECT * FROM CltJob WHERE TypeId=" _
            + Trim(Str(Tmp_TypeId)) + " ORDER BY SortId"
  End If
  OdbcExt (SqlStmt)
  i = 0
  Do While SQLFetch(Hstmt) <> 100
    '读取客户职务编号
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_CltJobId(i + 1)
    Arr_CltJobId(i) = Val(ColVal)
    
    '读取客户职务名称
    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_CltJob(i + 1)
    Arr_CltJob(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Sub Update(ByVal TmpJobId As String)
  DB_Connect
  
  SqlStmt = "UPDATE CltJob SET JobName='" + Trim(JobName) _
          + "',SortId=" + Trim(Str(SortId)) _
          + " WHERE JobId=" + Trim(Str(TmpJobId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub