clsstorein.cls

erp中的库存管理子系统,Vb代码编写

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "ClsStoreIn"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Public StoreInID As String
Public StoreInType As String
Public Item As String
Public UnitPrice As String
Public Currency1 As String
Public Qty As String
Public StoreRoom As String
Public Location As String
Public FillinPerson As String
Public FillinTime As String
Public Sub UpdateStoreTran(ByVal OriItem As String, ByVal OriStoreRoom As String, ByVal OriLocation As String)
   
  '连接数据库
  MyDB.openDB
  
   '设置Update语句
   SqlStmt = "Update Store Set Item='" + Trim(Item) + "',StoreRoom='" + Trim(StoreRoom) + "',Location='" + Trim(Location) + "'  WHERE Item='" + Trim(OriItem) + "' and StoreRoom='" + Trim(OriStoreRoom) + "' and Location='" + Trim(OriLocation) + "'  "
  
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsStore As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStore, True)
  
  '断开与数据库的连接
  MyDB.closeDB
End Sub
Public Sub UpdateStore(ByVal OriItem As String, ByVal AddQty As Integer, ByVal OriStoreRoom As String, ByVal OriLocation As String)
   
  '连接数据库
  MyDB.openDB
  
  '设置Select语句
   SqlStmt = "select Qty from Store where Item='" + Trim(OriItem) + "' and StoreRoom='" + Trim(OriStoreRoom) + "' and Location='" + Trim(OriLocation) + "'"
  
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsQty As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsQty, False)
 
 
  Dim OriQty As Integer
  Dim NowQty As Integer
  OriQty = Trim(rsQty.Fields(0))
   
  NowQty = OriQty + AddQty
   
   '设置Update语句
   SqlStmt = "Update Store Set Qty='" + Trim(NowQty) + "' WHERE Item='" + Trim(OriItem) + "' and StoreRoom='" + Trim(OriStoreRoom) + "' and Location='" + Trim(OriLocation) + "'"
  
  '执行SQL语句
  Dim rsStore As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStore, True)
  
  '断开与数据库的连接
  MyDB.closeDB
End Sub
Public Function In_DBStore(ByVal TempItem As String, ByVal TempStoreRoom As String, ByVal TempLocation As String) As Boolean
  '连接数据库
  MyDB.openDB
  
  '设置查询语句
  SqlStmt = "SELECT Item FROM Store WHERE Item='" + Trim(TempItem) + "' and StoreRoom='" + Trim(TempStoreRoom) + "' and Location='" + Trim(TempLocation) + "'"
  
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsStore As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStore, False)
  
  '判断是否存在
  If rsStore.RecordCount <> 0 Then
    In_DBStore = True
  Else
    In_DBStore = False
  End If
  
  '断开与数据库的连接
  MyDB.closeDB
  
End Function
Public Sub InStore()
  
  '连接数据库
  MyDB.openDB
  
  '设置Insert语句
  SqlStmt = "INSERT INTO Store (Item,Qty,StoreRoom,Location)" _
     + " Values('" + Trim(Item) _
     + "','" + Trim(Qty) _
     + "','" + Trim(StoreRoom) _
     + "','" + Trim(Location) + "')"
    
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsStore As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStore, True)
  
  '断开与数据库的连接
  MyDB.closeDB
End Sub

Public Sub Insert()
  
  '连接数据库
  MyDB.openDB
  
  '设置Insert语句
  SqlStmt = "INSERT INTO StoreIn (StoreInID,StoreInType,Item,UnitPrice,Currency1,Qty,StoreRoom,Location,FillinPerson,FillinTime)" _
     + " Values('" + Trim(StoreInID) + "','" + Trim(StoreInType) + "','" + Trim(Item) + "','" + Trim(UnitPrice) _
     + "','" + Trim(Currency1) + "','" + Trim(Qty) _
     + "','" + Trim(StoreRoom) _
     + "','" + Trim(Location) + "','" + Trim(FillinPerson) + "','" + Trim(FillinTime) + "')"
    
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsStoreIn As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStoreIn, True)
  
  '断开与数据库的连接
  MyDB.closeDB
End Sub
Public Function In_DB(ByVal TempStoreInID As String) As Boolean
  '连接数据库
  MyDB.openDB
  
  '设置查询语句
  SqlStmt = "SELECT StoreInID FROM StoreIn WHERE StoreInID='" + Trim(TempStoreInID) + "'"
  
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsStoreIn As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStoreIn, False)
  
  '判断是否存在
  If rsStoreIn.RecordCount <> 0 Then
    In_DB = True
  Else
    In_DB = False
  End If
  
  '断开与数据库的连接
  MyDB.closeDB
  
End Function

Public Sub Load_Location(ByVal StoreRoom As String)
  Dim i As Long
  Erase Arr_Item
  ReDim Arr_Item(0)
  
  '连接数据库
  MyDB.openDB
  
  '设置SQL语句
  SqlStmt = "SELECT Location FROM StoreRoom where StoreRoom='" + Trim(StoreRoom) + " ' "
  
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsLocation As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsLocation, True)
  

  
  
  i = 0
  Do Until rsLocation.EOF = True
    ReDim Preserve Arr_Item(i + 1)
    Arr_Item(i) = rsLocation(0)
    i = i + 1
    rsLocation.MoveNext
  Loop
  
    '断开与数据库的连接
  MyDB.closeDB
End Sub
Public Sub Load_StoreRoom()
  Dim i As Long
  Erase Arr_Item
  ReDim Arr_Item(0)
  
  '连接数据库
  MyDB.openDB
  
  '设置SQL语句
  SqlStmt = "SELECT StoreRoom FROM StoreRoom "
  
  '执行SQL语句
  Dim Isexe As Boolean
  Dim rsStoreRoom As ADODB.Recordset
  Isexe = MyDB.exeSql(SqlStmt, rsStoreRoom, True)
  

  
  
  i = 0
  Do Until rsStoreRoom.EOF = True
    ReDim Preserve Arr_Item(i + 1)
    Arr_Item(i) = rsStoreRoom(0)
    i = i + 1
    rsStoreRoom.MoveNext
  Loop
  
    '断开与数据库的连接
  MyDB.closeDB
End Sub