orders.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 = "Orders"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1   OrderId Int 采购单号
'2   OrderType   Varchar 20  采购类型，包括采购和采购退货
'3   ProId   Int 采购商品编号
'4   ProPrice    Decimal 15,2    采购商品单价
'5   ProNum  Int 采购商品数量
'6   ProAmount   Decimal 15,2    采购商品总金额
'7   ClientId    Int 客户编号
'8   OptDate Varchar 20  采购日期
'9  EmpName Varchar 50  经办人
'10  Stored  Tinyint 出/入库标记，0－尚未提交出/入库，1－提交出/入库，2－已出/入库

Public OrderId As Long
Public OrderType As String
Public ProId As String
Public ProPrice As Single
Public ProNum As Integer
Public ProAmount As Single
Public ClientId As Long
Public EmpName As String
Public OptDate As String
Public Stored As Integer

Public Sub Init()
  OrderId = 0
  OrderType = ""
  ProId = 0
  ProPrice = 0
  ProNum = 0
  ProAmount = 0
  ClientId = 0
  EmpName = ""
  OptDate = ""
  Stored = 0
End Sub

'删除
Public Sub Delete(TmpId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Orders WHERE OrderId=" _
          + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Function HaveClt(ByVal TmpCltId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT OrderId FROM Orders 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 OrderId FROM Orders 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 GetInfo(ByVal TmpOrderId As Long) As Boolean
  If TmpOrderId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  OrderId = TmpOrderId
  DB_Connect
  
  SqlStmt = "SELECT * FROM Orders WHERE OrderId=" _
          + Trim(Str(TmpOrderId))
  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)
    OrderType = TrimStr(ColVal)

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

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

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    OptDate = TrimStr(ColVal)

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

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
    Stored = Val(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Sub Insert()
  DB_Connect
  
  SqlStmt = "INSERT INTO Orders Values( ORDERID.NEXTVAL,'" + Trim(OrderType) + "'," + Trim(ProId) _
     + "," + Trim(Str(ProPrice)) + "," + Trim(ProNum) + "," _
     + Trim(Str(ProAmount)) + "," + Trim(Str(ClientId)) + ",'" _
     + Trim(OptDate) + "','" + Trim(EmpName) + "',1)"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Sub LoadId(ByVal TmpType As String, _
                  ByVal TmpStored As Integer)
  Dim i As Integer
  
  Erase ArrId
  ReDim ArrId(0)
  
  DB_Connect
  SqlStmt = "SELECT OrderId FROM Orders WHERE OrderType='" _
          + Trim(TmpType) + "'"
  If TmpStored > 0 Then
    SqlStmt = SqlStmt + " And Stored=" + Trim(TmpStored)
  End If
  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 ArrId(i + 1)
    ArrId(i) = Val(ColVal)
    
    i = i + 1
  Loop
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'更新数据
Public Sub Update(ByVal TmpId As Long)
  DB_Connect
    
  SqlStmt = "UPDATE Orders SET OrderType='" + Trim(OrderType) _
          + "', ProId=" + Trim(Str(ProId)) + ", ProPrice=" + Trim(Str(ProPrice)) _
          + ",ProNum=" + Trim(Str(ProNum)) + ", ProAmount=" + Trim(Str(ProAmount)) _
          + ",ClientId=" + Trim(Str(ClientId)) + ", OptDate='" + Trim(OptDate) _
          + "', EmpName='" + Trim(EmpName) + "' WHERE OrderId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'更新状态数据
Public Sub UpdateStored(ByVal TmpId As Long)
  '连接数据库
  DB_Connect
  '设置UPDATE语句，设置OrderId=TmpId的记录的出/入库标记
  SqlStmt = "UPDATE Orders SET Stored=" + Trim(Str(Stored)) _
          + " WHERE OrderId=" + Trim(Str(TmpId))
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub