proinstore.cls

Visual Basic + Oracle 9i数据库应用系统开发与实例（企业购销存管理系统）界面简洁,数据库完整,还不错~适合学生利用和使用!

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  Int 商品存储编号
'2   ProId   Int 商品编号
'3   ProPrice    Decimal 15,2    商品入库单价
'4   ProNum  Int 商品数量
'5   StoreId Int 仓库编号

Public StoreProId As Long
Public ProId As Long
Public ProPrice As Single
Public ProNum As Integer
Public StoreId As Long

Public Sub Init()
  StoreProId = 0
  ProId = 0
  StoreId = 0
  ProPrice = 0
  ProNum = 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 TmpStoreId As Long) As Long
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And ProPrice=" + Trim(TmpProPrice) _
          + " 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
  If TmpId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  StoreProId = TmpId
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM ProInStore WHERE StoreProId=" _
          + Trim(Str(TmpId))
  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)
    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 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 TmpStoreId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And ProPrice=" + Trim(TmpProPrice) _
          + " 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 Values(STOREPROID.NEXTVAL," + Trim(Str(ProId)) + "," _
     + Trim(Str(ProPrice)) + "," + Trim(Str(ProNum)) + "," _
     + 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