sales.cls

来自「Visual Basic + Oracle 9i数据库应用系统开发与实例（企业购」· CLS 代码 · 共 217 行

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Sales"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1   SaleId  Int 销售单号
'2   SaleType    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 SaleId As Long
Public SaleType 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()
  SaleId = 0
  SaleType = ""
  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 Sales WHERE SaleId=" _
          + 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 SaleId FROM Sales 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 SaleId FROM Sales 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 TmpSaleId As Long) As Boolean
  If TmpSaleId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  SaleId = TmpSaleId
  DB_Connect
  
  SqlStmt = "SELECT * FROM Sales WHERE SaleId=" _
          + Trim(Str(TmpSaleId))
  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)
    SaleType = 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 Sales Values(SALEID.NEXTVAL, '" + Trim(SaleType) + "'," _
          + 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 SaleId FROM Sales WHERE SaleType='" _
          + 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 Sales SET SaleType='" + Trim(SaleType) _
          + "', 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 SaleId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'更新状态数据
Public Sub UpdateStored(ByVal TmpId As Long)
  DB_Connect
    
  SqlStmt = "UPDATE Sales SET Stored=" + Trim(Str(Stored)) _
          + " WHERE SaleId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub