product.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 = "Product"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 ProId Int 产品编号
'2 ProName Varchar 50  产品名称
'3 TypeId  Int 产品类型编号
'4 ProStyle  Varchar 50  产品规格
'5 ProUnit Varchar 10  计量单位
'6 ProPrice  Decimal 15,2  参考价格
'7 ProLow  Int 产品数量下限
'8 ProHigh Int 产品数量上限
'9 Valid Int 有效期（以天为单位）
'10  AlarmDays Int 在到达有效期前几天发出警告

Public ProId As Long
Public ProName As String
Public TypeId As Long
Public ProStyle As String
Public ProUnit As String
Public ProPrice As Single
Public ProLow As Integer
Public ProHigh As Integer
Public Valid As Integer
Public AlarmDays As Integer

Public Sub Init()
  ProId = 0
  ProName = ""
  TypeId = 0
  ProStyle = ""
  ProUnit = ""
  ProPrice = 0
  ProLow = 0
  ProHigh = 0
  Valid = 0
  AlarmDays = 0
End Sub

'删除Product数据
Public Sub Delete(ByVal TmpProId As Long)
  DB_Connect
  
  SqlStmt = "Delete FROM Product WHERE ProId=" + Trim(Str(TmpProId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetName(ByVal TmpProId As Long) As String
  If TmpProId <= 0 Then
    GetName = ""
    Exit Function
  End If
  
  DB_Connect
  
  SqlStmt = "SELECT ProName FROM Product WHERE ProId=" + Trim(Str(TmpProId))
  OdbcExt (SqlStmt)

  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetName = ""
    Exit Function
  Else
    ColVal = String(100, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetName = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function GetId(ByVal TmpName As String) As Long
  If TmpName = "" Then
    GetId = 0
    Exit Function
  End If
  DB_Connect
  
  SqlStmt = "SELECT ProId FROM Product WHERE ProName='" _
          + Trim(TmpName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetId = Val(ColVal)
  Else
    GetId = 0
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Function GetInfo(ByVal TmpProId As Long) As Boolean
  If TmpProId <= 0 Then
    Init
    GetInfo = False
    Exit Function
  End If
  
  ProId = TmpProId
  
  DB_Connect
  SqlStmt = "SELECT * FROM Product WHERE ProId=" + Trim(Str(TmpProId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Exit Function
  Else
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ProName = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    TypeId = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    ProStyle = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    ProUnit = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    ProPrice = Val(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    ProLow = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    ProHigh = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 9, 1, ColVal, Lench(ColVal), pcblench)
    Valid = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
    AlarmDays = Val(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Function In_DB(ByVal TmpProName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT ProId FROM Product WHERE ProName='" + Trim(TmpProName) + "'"
  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()
  '连接数据库
  DB_Connect
  '设置Insert语句
  SqlStmt = "INSERT INTO Product (ProName, ProStyle," _
     + "TypeId, ProUnit, ProPrice, ProLow," _
     + "ProHigh, Valid, AlarmDays)" _
     + " Values('" + Trim(ProName) + "','" + Trim(ProStyle) _
     + "'," + Trim(Str(TypeId)) + ",'" + Trim(ProUnit) + "'," _
     + Trim(ProPrice) + "," + Trim(ProLow) + "," + Trim(ProHigh) _
     + "," + Trim(Valid) + "," + Trim(AlarmDays) + ")"
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

Public Sub Load_by_Type(ByVal TmpTypeId As Integer)
  Dim i As Integer
  '初始化部门数组
  Erase Arr_Product
  ReDim Arr_Product(0)
  
  DB_Connect
  SqlStmt = "SELECT ProName FROM Product WHERE TypeId=" _
          + Trim(Str(TmpTypeId)) + " ORDER BY ProName"
  OdbcExt (SqlStmt)
  i = 0
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    '读取部门编号
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_Product(i + 1)
    Arr_Product(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function HaveType(ByVal TmpTypeId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT ProId FROM Product WHERE TypeId=" + Trim(TmpTypeId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    HaveType = True
  Else
    HaveType = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Sub Update(ByVal OriProId As Integer)
  '连接数据库
  DB_Connect
  '设置Update语句
  SqlStmt = "Update Product Set ProName='" + Trim(ProName) _
          + "',ProStyle='" + Trim(ProStyle) + "',TypeId=" _
          + Trim(TypeId) + ",ProUnit='" + Trim(ProUnit) _
          + "',ProPrice=" + Trim(ProPrice) + ",ProLow=" _
          + Trim(ProLow) + ",ProHigh=" + Trim(ProHigh) + ",Valid=" _
          + Trim(Valid) + ",AlarmDays=" + Trim(AlarmDays) _
          + " WHERE ProId=" + Trim(Str(OriProId))
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub