proinstore.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 = "ProInStore"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 StoreProId  自动编号  产品存储编号
'2 ProId 数字 / 长整型 产品编号
'3 ProPrice  数字 / 单精度型 产品入库单价
'4 ProNum  数字 / 长整型 产品数量
'5 ClientId  数字 / 长整型 客户编号。如果入库操作类型为采购入库，则客户为供应商；如果入库操作类型为退货入库，则客户为购货商；其他情况没有客户
'6 CreateDate  文本 / 20 生产日期
'7 StoreId 数字 / 长整型 仓库编号
Public StoreProId As Long
Public ProId As Long
Public ProPrice As Single
Public ProNum As Integer
Public ClientId As Long
Public CreateDate As String
Public StoreId As Long

Public Sub Init()
  StoreProId = 0
  ProId = 0
  StoreId = 0
  ProPrice = 0
  ProNum = 0
  ClientId = 0
  CreateDate = ""
  StoreId = 0
End Sub

Public Sub DeleteZero()
  DB_Connect
  
  SqlStmt = "DELETE FROM ProInStore WHERE ProNum=0"
  OdbcExt (SqlStmt)
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpProId As Long, _
                      ByVal TmpProPrice As Single, _
                      ByVal TmpCreateDate As String, _
                      ByVal TmpStoreId As Long) As Long
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And ProPrice=" + Trim(TmpProPrice) _
          + " And CreateDate='" + Trim(CreateDate) + "' And StoreId=" _
          + Trim(TmpStoreId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetId = 0
  Else
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetId = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function GetInfo(ByVal TmpId As Long) As Boolean
  '把参数赋值到StoreProId
  StoreProId = TmpId
  '连接数据库
  DB_Connect
  '设置SQL语句
  SqlStmt = "SELECT * FROM ProInStore WHERE StoreProId=" _
          + Trim(Str(TmpId))
  '执行SQL语句
  OdbcExt (SqlStmt)
  '从结果集中读取数据
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    '没有满足条件的记录
    GetInfo = False
    Init
    Exit Function
  Else
    '产品编号
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ProId = TrimStr(ColVal)
    '产品价格
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    ProPrice = Val(ColVal)
    '产品数量
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    ProNum = Val(ColVal)
    '客户编号
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    ClientId = Val(ColVal)
    '出产日期
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    CreateDate = TrimStr(ColVal)
    '仓库编号
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    StoreId = TrimStr(ColVal)
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  GetInfo = True
  '断开连接
  DB_Disconnect
End Function

Public Function GetSumPro(ByVal TmpStoreId As Long, _
                         ByVal TmpProId As Long) As Single
  
  DB_Connect
  
  SqlStmt = "SELECT Sum(ProNum) FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And StoreId=" + Trim(TmpStoreId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetSumPro = Val(ColVal)
  Else
    GetSumPro = 0
  End If
  
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function HaveClt(ByVal TmpCltId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ClientId=" + Trim(TmpCltId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    HaveClt = True
  Else
    HaveClt = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

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

  DB_Disconnect
End Function

Public Function HaveStore(ByVal TmpStoreId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE StoreId=" + Trim(TmpStoreId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    HaveStore = True
  Else
    HaveStore = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function In_DB(ByVal TmpProId As Long, _
                      ByVal TmpProPrice As Single, _
                      ByVal TmpCreateDate As String, _
                      ByVal TmpStoreId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And ProPrice=" + Trim(TmpProPrice) _
          + " And CreateDate='" + Trim(CreateDate) + "' And StoreId=" _
          + Trim(TmpStoreId)
  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()
  DB_Connect
  
  SqlStmt = "INSERT INTO ProInStore (ProId, ProPrice, ProNum," _
     + " ClientId, CreateDate, StoreId) Values(" _
     + Trim(Str(ProId)) + "," + Trim(Str(ProPrice)) + "," _
     + Trim(Str(ProNum)) + "," + Trim(Str(ClientId)) + ",'" _
     + Trim(CreateDate) + "'," + Trim(Str(StoreId)) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Function LoadPro(ByVal TmpStoreId As Long, _
                        ByVal TmpProId As Long) As Long
  Dim i As Integer
  
  Erase Arr_StoreProId
  Erase Arr_StoreProNum
  ReDim Arr_StoreProId(0)
  ReDim Arr_StoreProNum(0)
  
  DB_Connect
  
  SqlStmt = "SELECT StoreProId, ProNum FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And StoreId=" + Trim(TmpStoreId)
  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_StoreProId(i + 1)
    Arr_StoreProId(i) = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_StoreProNum(i + 1)
    Arr_StoreProNum(i) = Val(ColVal)
  
    i = i + 1
  Loop
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Sub UpdateDiff(ByVal OriId As Long)
  DB_Connect
  
  SqlStmt = "Update ProInStore Set ProNum = ProNum + " _
          + Trim(Str(ProNum)) + " WHERE StoreProId=" _
          + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  '删除数量为0的记录
  DeleteZero
End Sub

Public Sub Update(ByVal OriId As Long)
  DB_Connect
  
  SqlStmt = "Update ProInStore Set ProNum=" _
          + Trim(Str(ProNum)) + " WHERE StoreProId=" _
          + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  '删除数量为0的记录
  DeleteZero
End Sub