storein.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 = "StoreIn"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 StoreinId Int 入库编号
'2 StoreinType Varchar 20  入库操作类型，包括采购入库、生产入库、退货入库、退料入库等
'3 ProId   Int 入库产品编号
'4 CreateDate  Char 10 生产日期
'5 ProPrice  Decimal 15,2  入库产品单价
'6 ProNum  Int 入库产品数量
'7 ClientId  Int 客户编号。如果入库操作类型为采购入库，则客户为供应商；如果入库操作类型为退货入库，则客户为购货商；其他情况没有客户
'8 StoreId Int 仓库编号
'9 EmpName Varchar 50  经办人
'10  OptDate Char 10 入库日期
Public StoreinId As Long
Public StoreinType As String
Public ProId As String
Public CreateDate As String
Public ProPrice As Single
Public ProNum As Integer
Public ClientId As Long
Public StoreId As Long
Public EmpName As String
Public OptDate As String

Public Sub Init()
  StoreinId = 0
  StoreinType = ""
  ProId = 0
  CreateDate = ""
  ProPrice = 0
  ProNum = 0
  ClientId = 0
  StoreId = 0
  EmpName = ""
  OptDate = ""
End Sub

Public Function HaveClt(ByVal TmpCltId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreinId FROM StoreIn 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 StoreinId FROM StoreIn 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 StoreinId FROM StoreIn 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 GetInfo(ByVal TmpStoreinId As Long) As Boolean
  If TmpStoreinId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  StoreinId = TmpStoreinId
  DB_Connect
  
  SqlStmt = "SELECT * FROM StoreIn WHERE StoreinId=" _
          + Trim(Str(StoreinId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Init
    Exit Function
  Else
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    StoreinType = TrimStr(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    ProId = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    CreateDate = TrimStr(ColVal)

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

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    StoreId = Val(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 9, 1, ColVal, Lench(ColVal), pcblench)
    EmpName = TrimStr(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
    OptDate = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  '连接数据库
  DB_Connect
  '设置要执行的SQL语句
  SqlStmt = "SELECT StoreinId FROM Storein ORDER BY StoreinId"
  '执行SQL语句
  OdbcExt (SqlStmt)
  '读取SELECT语句返回的查询结果
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    ' 把读取的编号与自然数序列比较
    If TmpId = 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 Insert()
  StoreinId = GetNewId
  If CzFlag = "" Then
    CzId = StoreinId
  End If
  
  DB_Connect
  
  SqlStmt = "INSERT INTO StoreIn Values(" _
     + Trim(StoreinId) + ",'" + Trim(StoreinType) + "'," _
     + Trim(ProId) + ",'" + Trim(CreateDate) + "'," _
     + Trim(Str(ProPrice)) + "," + Trim(ProNum) + "," _
     + Trim(Str(ClientId)) + "," + Trim(Str(StoreId)) + ",'" _
     + Trim(EmpName) + "','" + Trim(OptDate) + "')"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub