cltorg.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 = "CltOrg"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 OrgId Int 客户单位编号
'2 OrgName Varchar 200 客户单位名称
'3 TypeId  Int 所属客户类别编号
'4 Address Varchar 400 通信地址
'5 Postcode  Varchar 40  邮政编码
'6 AreaId  Int 所在地域编号
'7 Deleted Bit 删除标记

Public OrgId As Integer
Public OrgName As String
Public TypeId As Integer
Public Address As String
Public Postcode As String
Public AreaId As Long
Public Deleted As Integer

Public Sub Init()
  OrgId = 0
  OrgName = ""
  TypeId = 0
  Address = ""
  Postcode = ""
  AreaId = 0
  Deleted = 0
End Sub

'判断指定地域信息是否在客户单位中
Public Function Area_In_CltOrg(ByVal TmpAreaId As Long) As String
  DB_Connect
  
  SqlStmt = "SELECT OrgName FROM CltOrg WHERE AreaId=" _
          + Trim(Str(TmpAreaId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    Tmp_OrgName = TrimStr(ColVal)
    
    Area_In_CltOrg = Tmp_OrgName
  Else
    Area_In_CltOrg = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

'根据给定的地域计算客户数目,如果TmpAreaId=0，则计算全部单位数
Public Function CountOrg(ByVal TmpAreaId As Integer) As Integer
  '读取指定地域的信息
  MyArea.GetInfo (TmpAreaId)
  DB_Connect
'统计全部客户数据
  SqlStmt = "SELECT Distinct OrgName FROM CltOrg WHERE Deleted=0"
  If TmpAreaId = 0 Then
    SqlStmt = SqlStmt + " ORDER BY OrgName"
  Else
    If MyArea.AreaType = 4 Then   '市县
      SqlStmt = SqlStmt + " And AreaId=" + Trim(Str(TmpAreaId)) _
            + " ORDER BY OrgName"
    Else '省
      SqlStmt = "SELECT Distinct c.OrgName FROM CltOrg c, Area d" _
             + " WHERE c.AreaId=d.AreaId And d.UpperId=" + Trim(Str(TmpAreaId)) _
             + " And c.Deleted=0 ORDER BY c.OrgName"
    End If
  End If
  
  OdbcExt (SqlStmt)
  total_num = 0
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    total_num = total_num + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  CountOrg = total_num
  DB_Disconnect
End Function

Public Sub Delete(ByVal TmpOrgId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM CltOrg WHERE OrgId=" + Trim(Str(TmpOrgId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

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

  SqlStmt = "SELECT OrgId FROM CltOrg WHERE OrgName='" _
          + Trim(TmpOrg) + "'"
  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 TmpOrgId As Integer) As String
  DB_Connect
  
  SqlStmt = "SELECT OrgName FROM CltOrg WHERE OrgId =" _
          + Trim(Str(TmpOrgId))
  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 OrgId FROM CltOrg ORDER BY OrgId"
  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 TmpOrgId As String)
  OrgId = TmpOrgId
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM CltOrg WHERE OrgId=" + Trim(Str(TmpOrgId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(300, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    OrgName = TrimStr(ColVal)
    
    ColVal = String(30, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    TypeId = Val(ColVal)
    
    ColVal = String(500, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Address = TrimStr(ColVal)

    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    Postcode = TrimStr(ColVal)

    ColVal = String(30, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    AreaId = Val(ColVal)
    
    ColVal = String(100, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Deleted = Val(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Function In_DB(ByVal TmpOrg As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT * FROM CltOrg WHERE OrgName='" + Trim(TmpOrg) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    In_DB = False
  Else
    In_DB = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Sub Insert()
  OrgId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO CltOrg Values(" + Trim(Str(OrgId)) + ",'" _
          + Trim(OrgName) + "'," + Trim(Str(TypeId)) + ",'" _
          + Trim(Address) + "','" + Trim(Postcode) _
          + "'," + Trim(Str(AreaId)) + ",0)"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Sub Load_CltOrg(ByVal TmpAreaId As Long, _
                       ByVal TmpTypeId As Integer)
  Erase Arr_CltOrg
  ReDim Arr_CltOrg(0)
  
  DB_Connect

  '统计全部客户数据
  SqlStmt = "SELECT Distinct OrgName FROM CltOrg WHERE Deleted=0"
  If TmpAreaId = 0 Then     '所有地域
    If TmpTypeId = 0 Then   '所有客户类型
      SqlStmt = SqlStmt + " ORDER BY OrgName"
    Else                    '指定客户类型
      SqlStmt = SqlStmt + " And TypeId=" + Trim(Str(TmpTypeId)) _
             + " ORDER BY OrgName"
    End If
  Else                      '指定地域
    If CurArea.AreaType = 4 Then   '市县
      If TmpTypeId = 0 Then      '所有客户类型
        SqlStmt = SqlStmt + " And AreaId=" + Trim(Str(TmpAreaId)) _
            + " ORDER BY OrgName"
      Else                       '指定客户类型
         SqlStmt = SqlStmt + " And AreaId=" + Trim(Str(TmpAreaId)) _
            + " And TypeId=" + Trim(Str(TmpTypeId)) + " ORDER BY OrgName"
      End If
    Else                         '省
      If TmpTypeId = 0 Then      '所有客户类型
        SqlStmt = "SELECT Distinct c.OrgName FROM CltOrg c, Area d" _
             + " WHERE (c.AreaId=d.AreaId And d.UpperId=" + Trim(Str(TmpAreaId)) _
             + " Or c.AreaId=" + Trim(Str(TmpAreaId)) + ") And c.Deleted=0" _
             + " ORDER BY c.OrgName"
      Else                       '指定客户类型
        SqlStmt = "SELECT Distinct c.OrgName FROM CltOrg c, Area d" _
             + " WHERE (c.AreaId=d.AreaId And d.UpperId=" + Trim(Str(TmpAreaId)) _
             + " Or c.AreaId=" + Trim(Str(TmpAreaId)) + ") And c.Deleted=0" _
             + " And TypeId=" + Trim(Str(TmpTypeId)) + " ORDER BY c.OrgName"
      End If
    End If
  End If
  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_CltOrg(i + 1)
    Arr_CltOrg(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

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

'判断指定客户类别信息是否在客户数据库中
Public Function TypeId_In_CltOrg(ByVal TmpTypeId As String) As String
  DB_Connect
  
  SqlStmt = "SELECT OrgName FROM CltOrg 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)
    Tmp_OrgName = TrimStr(ColVal)
    
    TypeId_In_CltOrg = Tmp_OrgName
  Else
    TypeId_In_CltOrg = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Sub Update(ByVal OriOrgId As Long)
  DB_Connect
  
  SqlStmt = "UPDATE CltOrg set OrgName='" + Trim(OrgName) _
          + "',TypeId=" + Trim(TypeId) + ",Address='" _
          + Trim(Address) + "',Postcode='" + Trim(Postcode) _
          + "',AreaId=" + Trim(Str(AreaId)) _
          + " WHERE OrgId=" + Trim(Str(OriOrgId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub