clsorder.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 = "clsOrder"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
 '***************************************************************
'bsOrder类模块
'CreateTime:2002-11-11 0:01:34;Editor:RZHOU
'ComputerIP:192.168.3.252;ComputerName:bluepc
'***************************************************************
Option Explicit
'*****************************************
Public m_ID As String 'DBO.bsOrder.ID;描述:;主键
Public m_OrderNo As String 'DBO.bsOrder.OrderNo;描述:;
Public m_OrderStatus As String 'DBO.bsOrder.OrderStatus;描述:;
Public m_OrderType As String 'DBO.bsOrder.OrderType;描述:;
Public m_CustomerID As String 'DBO.bsOrder.CustomerID;描述:;
Public m_Address As String 'DBO.bsOrder.Address;描述:;
Public m_Contactee As String 'DBO.bsOrder.Contactee;描述:;
Public m_Tel As String 'DBO.bsOrder.Tel;描述:;
Public m_Fax As String 'DBO.bsOrder.Fax;描述:;
Public m_Email As String 'DBO.bsOrder.Email;描述:;
Public m_Fee As Double 'DBO.bsOrder.Fee;描述:;
Public m_WarehouseID As String 'DBO.bsOrder.WarehouseID;描述:;
Public m_CommodityID As String 'DBO.bsOrder.CommodityID;描述:;
Public m_Price As Double 'DBO.bsOrder.Price;描述:;
Public m_PQty As Double 'DBO.bsOrder.PQty;描述:;
Public m_RQty As Double 'DBO.bsOrder.RQty;描述:;
Public m_PAmount As Double 'DBO.bsOrder.PAmount;描述:;
Public m_RAmount As Double 'DBO.bsOrder.RAmount;描述:;
Public m_OperatorID As String 'DBO.bsOrder.OperatorID;描述:;记录操作人的ID,从模块中的g_sUserID得到
Private m_CreateDate As Date 'DBO.bsOrder.CreateDate;描述:;记录加入的时间,在Insert的时候取值getDate(),在Update的时候不用修改
Private m_ModifyDate As Date 'DBO.bsOrder.ModifyDate;描述:;记录最后修改的时间,在Insert的时候取值getDate(),在Update的时候也取值getDate()
'*****************************************
Dim sSQL As String
'bsOrder Templet在给对象赋值时使用,可以少写不少代码...
'Public function Templet() as boolean
'On Error Resume Next
       '.m_ID=""
       '.m_OrderNo=""
       '.m_OrderStatus=""
       '.m_OrderType=""
       '.m_CustomerID=""
       '.m_Address=""
       '.m_Contactee=""
       '.m_Tel=""
       '.m_Fax=""
       '.m_Email=""
       '.m_Fee=""
       '.m_WarehouseID=""
       '.m_CommodityID=""
       '.m_Price=""
       '.m_PQty=""
       '.m_RQty=""
       '.m_PAmount=""
       '.m_RAmount=""
       '.m_OperatorID=""
       '.m_CreateDate=""
       '.m_ModifyDate=""
'End Function

'修改数据开始
'bsOrder Insert SQL
Public Function Insert() As String
   sSQL = ""
   sSQL = sSQL + " insert into bsOrder"
   sSQL = sSQL + " ("
   sSQL = sSQL + "ID,OrderNo,OrderStatus,OrderType,CustomerID,Address,Contactee,Tel,Fax,Email,Fee,WarehouseID,CommodityID,Price,PQty,RQty,PAmount,RAmount,OperatorID,CreateDate,ModifyDate"
   sSQL = sSQL + ") Values ("
   sSQL = sSQL + CheckString(m_ID) + ","
   sSQL = sSQL + CheckString(m_OrderNo) + ","
   sSQL = sSQL + CheckString(m_OrderStatus) + ","
   sSQL = sSQL + CheckString(m_OrderType) + ","
   sSQL = sSQL + CheckString(m_CustomerID) + ","
   sSQL = sSQL + CheckString(m_Address) + ","
   sSQL = sSQL + CheckString(m_Contactee) + ","
   sSQL = sSQL + CheckString(m_Tel) + ","
   sSQL = sSQL + CheckString(m_Fax) + ","
   sSQL = sSQL + CheckString(m_Email) + ","
   sSQL = sSQL + "convert(Float," + CheckString(m_Fee) + ")" + ","
   sSQL = sSQL + CheckString(m_WarehouseID) + ","
   sSQL = sSQL + CheckString(m_CommodityID) + ","
   sSQL = sSQL + "convert(Float," + CheckString(m_Price) + ")" + ","
   sSQL = sSQL + "convert(Float," + CheckString(m_PQty) + ")" + ","
   sSQL = sSQL + "convert(Float," + CheckString(m_RQty) + ")" + ","
   sSQL = sSQL + "convert(Float," + CheckString(m_PAmount) + ")" + ","
   sSQL = sSQL + "convert(Float," + CheckString(m_RAmount) + ")" + ","
   sSQL = sSQL + CheckString(g_sUserID) + ","
   sSQL = sSQL + "getDate()" + ","
   sSQL = sSQL + "getDate()"
   sSQL = sSQL + ")"
   
   Insert = sSQL + Chr(8)
End Function
'bsOrder Delete SQL
Public Function Delete() As String
    sSQL = ""
    sSQL = sSQL + " Delete bsOrder where OrderNo=" + CheckString(m_OrderNo)
    Delete = sSQL + Chr(8)
End Function
Public Function BatchSubmit(ByVal sTempSQL As String) As Boolean
Dim sqlArr() As String
Dim i As Integer
sqlArr = Split(sTempSQL, Chr(8))
'On Error GoTo err:
    g_cn.BeginTrans
    For i = 0 To UBound(sqlArr)
        If Len(Trim(sqlArr(i))) > 0 Then
            Call g_cn.Execute(Trim(sqlArr(i)))
        End If
    Next i
    g_cn.CommitTrans
    BatchSubmit = True
    'Add Log*********************'
    g_System.AddLog "bs_InOrder", sSQL
    GoTo Dispose
Err:
    g_cn.RollbackTrans
    Err.Clear
    BatchSubmit = False
    GoTo Dispose
Dispose:
    
End Function
'bsOrder Fill By ID SQL ,根据ID填充本对象
'参数  iID:对象的ID值
Public Function FillByID(ByVal sID As String) As Boolean
Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
    sSQL = ""
    sSQL = sSQL + " Select * from bsOrder where ID=" + CheckString(sID)
    rsTemp.Open sSQL, g_cn
      If rsTemp.RecordCount > 0 Then
         m_ID = rsTemp.Fields("ID").value
         m_OrderNo = rsTemp.Fields("OrderNo").value
         m_OrderType = rsTemp.Fields("OrderType").value
         m_CustomerID = rsTemp.Fields("CustomerID").value
         m_Address = rsTemp.Fields("Address").value
         m_Contactee = rsTemp.Fields("Contactee").value
         m_Tel = rsTemp.Fields("Tel").value
         m_Fax = rsTemp.Fields("Fax").value
         m_Email = rsTemp.Fields("Email").value
         m_Fee = rsTemp.Fields("Fee").value
         m_WarehouseID = rsTemp.Fields("WarehouseID").value
         m_CommodityID = rsTemp.Fields("CommodityID").value
         m_Price = rsTemp.Fields("Price").value
         m_PQty = rsTemp.Fields("PQty").value
         m_RQty = rsTemp.Fields("RQty").value
         m_PAmount = rsTemp.Fields("PAmount").value
         m_RAmount = rsTemp.Fields("RAmount").value
         m_OperatorID = rsTemp.Fields("OperatorID").value
         m_CreateDate = rsTemp.Fields("CreateDate").value
         m_ModifyDate = rsTemp.Fields("ModifyDate").value
         FillByID = True
      Else
         FillByID = False
      End If
End Function
'bsOrder get List Recordset SQL,根据条件返回本数据库的记录集
'参数  sFields:查找到的记录集的字段列表;sWhere:找到记录的条件;sOrder:记录集的排序字段及方式
Public Function GetList(Optional ByVal sFields As String = "*", Optional ByVal sWhere As String = "", Optional ByVal sOrder As String = "") As Recordset
Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
    sSQL = ""
    sSQL = sSQL + " Select " + sFields + " from bsOrder"
    If Len(sWhere) > 0 Then
       sSQL = sSQL + " where " + sWhere
    End If
    If Len(sOrder) > 0 Then
       sSQL = sSQL + " Order by " + sOrder
    End If
    rsTemp.Open sSQL, g_cn
    Set GetList = rsTemp
End Function
'bsOrder get Recordset By ID SQL,根据ID返回本数据库的记录集
'参数  iID:对象的ID值;sFields:查找到的记录集的字段列表;sOrder:记录集的排序字段及方式
Public Function GetRecordsetByID(ByVal sID As String, Optional ByVal sFields As String = "*", Optional ByVal sOrder As String = "") As ADODB.Recordset
Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
    sSQL = ""
    sSQL = sSQL + " Select " + sFields + " from bsOrder where ID=" + CheckString(sID)
    If Len(sOrder) > 0 Then
       sSQL = sSQL + " Order by " + sOrder
    End If
    rsTemp.Open sSQL, g_cn
    Set GetRecordsetByID = rsTemp
End Function

Public Function BuildCalculateQtySQL() As String
Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
    sSQL = "Select ID,WarehouseID,CommodityID,PQty,OrderType from bsOrder where OrderNo=" + CheckString(m_OrderNo)
    rsTemp.Open sSQL, g_cn
    
    sSQL = ""
    Select Case rsTemp.Fields("OrderType").value
        Case "销售订单"
            Do While Not rsTemp.EOF
                sSQL = sSQL + "Exec UpdateCalInventory " + CheckString(rsTemp.Fields("WarehouseID").value, ",") + CheckString(rsTemp.Fields("CommodityID").value, ",") + "-" + CStr(rsTemp.Fields("PQty").value) + "," + CheckString(g_sUserID) + Chr(8)
            rsTemp.MoveNext
            Loop
        Case "采购订单"
            Do While Not rsTemp.EOF
                sSQL = sSQL + "Exec UpdateCalInventory " + CheckString(rsTemp.Fields("WarehouseID").value, ",") + CheckString(rsTemp.Fields("CommodityID").value, ",") + CStr(rsTemp.Fields("PQty").value) + "," + CheckString(g_sUserID) + Chr(8)
            rsTemp.MoveNext
            Loop
    End Select
    Set rsTemp = Nothing
    BuildCalculateQtySQL = sSQL
End Function
Public Function BuildWarehouseOrderSQL() As String
Dim sSQLTemp As String
Dim objWHOrder As New clsWarehouseOrder
Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
    sSQL = "Select ID,OrderNo,WarehouseID,CommodityID,PQty,OrderType from bsOrder where OrderNo=" + CheckString(m_OrderNo)
    rsTemp.Open sSQL, g_cn
    sSQL = ""
    Select Case rsTemp.Fields("OrderType").value
        Case "销售订单"
            Do While Not rsTemp.EOF
                With objWHOrder
                    .m_CommodityID = rsTemp.Fields("CommodityID").value
                    .m_ID = newGUID()
                    .m_OperatorID = g_sUserID
                    .m_OrderNo = rsTemp.Fields("OrderNo").value
                    .m_OrderType = "销售出库单"
                    .m_PAmount = rsTemp.Fields("PQty").value
                    .m_RAmount = 0
                    .m_WarehouseOutID = rsTemp.Fields("WarehouseID").value
                    .m_WarehouseOrderNo = rsTemp.Fields("OrderNo").value
                    sSQL = sSQL + .Insert()
                End With
            rsTemp.MoveNext
            Loop
        Case "采购订单"
            Do While Not rsTemp.EOF
                With objWHOrder
                    .m_CommodityID = rsTemp.Fields("CommodityID").value
                    .m_ID = newGUID()
                    .m_OperatorID = g_sUserID
                    .m_OrderNo = rsTemp.Fields("OrderNo").value
                    .m_OrderType = "采购入库单"
                    .m_PAmount = rsTemp.Fields("PQty").value
                    .m_RAmount = 0
                    .m_WarehouseInID = rsTemp.Fields("WarehouseID").value
                    .m_WarehouseOrderNo = rsTemp.Fields("OrderNo").value
                    sSQL = sSQL + .Insert()
                End With
            rsTemp.MoveNext
            Loop
    End Select
    Set rsTemp = Nothing
    sSQLTemp = sSQL
    BuildWarehouseOrderSQL = sSQLTemp + BuildCalculateQtySQL()
End Function

Public Function AppSQL() As String
    AppSQL = "Update bsOrder set OrderStatus='已审批' where OrderNo=" + CheckString(m_OrderNo) + Chr(8)
End Function