VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "ProInStore"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 StoreProId  自動編號  產品存儲編號
'2 ProId 數字 / 長整型 產品編號
'3 ProPrice  數字 / 單精度型 產品入庫單價
'4 ProNum  數字 / 長整型 產品數量
'5 ClientId  數字 / 長整型 客戶編號。如果入庫操作類型為采購入庫，則客戶為供應商；如果入庫操作類型為退貨入庫，則客戶為購貨商；其他情況沒有客戶
'6 CreateDate  文本 / 20 生產日期
'7 StoreId 數字 / 長整型 倉庫編號
Public StoreProId As Long
Public ProId As Long
Public ProPrice As Single
Public ProNum As Integer
Public ClientId As Long
Public CreateDate As String
Public StoreId As Long

Public Sub Init()
  StoreProId = 0
  ProId = 0
  StoreId = 0
  ProPrice = 0
  ProNum = 0
  ClientId = 0
  CreateDate = ""
  StoreId = 0
End Sub

Public Sub DeleteZero()
  DB_Connect
  
  SqlStmt = "DELETE FROM ProInStore WHERE ProNum=0"
  OdbcExt (SqlStmt)
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpProId As Long, _
                      ByVal TmpProPrice As Single, _
                      ByVal TmpCreateDate As String, _
                      ByVal TmpStoreId As Long) As Long
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And ProPrice=" + Trim(TmpProPrice) _
          + " And CreateDate='" + Trim(CreateDate) + "' And StoreId=" _
          + Trim(TmpStoreId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetId = 0
  Else
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetId = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function GetInfo(ByVal TmpId As Long) As Boolean
  '把參數賦值到StoreProId
  StoreProId = TmpId
  '連接數據庫
  DB_Connect
  '設置SQL語句
  SqlStmt = "SELECT * FROM ProInStore WHERE StoreProId=" _
          + Trim(Str(TmpId))
  '執行SQL語句
  OdbcExt (SqlStmt)
  '從結果集中讀取數據
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    '沒有滿足條件的記錄
    GetInfo = False
    Init
    Exit Function
  Else
    '產品編號
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ProId = TrimStr(ColVal)
    '產品價格
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    ProPrice = Val(ColVal)
    '產品數量
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    ProNum = Val(ColVal)
    '客戶編號
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    ClientId = Val(ColVal)
    '出產日期
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    CreateDate = TrimStr(ColVal)
    '倉庫編號
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    StoreId = TrimStr(ColVal)
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  GetInfo = True
  '斷開連接
  DB_Disconnect
End Function

Public Function GetSumPro(ByVal TmpStoreId As Long, _
                         ByVal TmpProId As Long) As Single
  
  DB_Connect
  
  SqlStmt = "SELECT Sum(ProNum) FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And StoreId=" + Trim(TmpStoreId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetSumPro = Val(ColVal)
  Else
    GetSumPro = 0
  End If
  
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function HaveClt(ByVal TmpCltId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore 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 StoreProId FROM ProInStore 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 StoreProId FROM ProInStore 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 In_DB(ByVal TmpProId As Long, _
                      ByVal TmpProPrice As Single, _
                      ByVal TmpCreateDate As String, _
                      ByVal TmpStoreId As Long) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT StoreProId FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And ProPrice=" + Trim(TmpProPrice) _
          + " And CreateDate='" + Trim(CreateDate) + "' And StoreId=" _
          + Trim(TmpStoreId)
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    In_DB = False
  Else
    In_DB = True
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Sub Insert()
  DB_Connect
  
  SqlStmt = "INSERT INTO ProInStore (ProId, ProPrice, ProNum," _
     + " ClientId, CreateDate, StoreId) Values(" _
     + Trim(Str(ProId)) + "," + Trim(Str(ProPrice)) + "," _
     + Trim(Str(ProNum)) + "," + Trim(Str(ClientId)) + ",'" _
     + Trim(CreateDate) + "'," + Trim(Str(StoreId)) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Function LoadPro(ByVal TmpStoreId As Long, _
                        ByVal TmpProId As Long) As Long
  Dim i As Integer
  
  Erase Arr_StoreProId
  Erase Arr_StoreProNum
  ReDim Arr_StoreProId(0)
  ReDim Arr_StoreProNum(0)
  
  DB_Connect
  
  SqlStmt = "SELECT StoreProId, ProNum FROM ProInStore WHERE ProId=" _
          + Trim(TmpProId) + " And StoreId=" + Trim(TmpStoreId)
  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 Arr_StoreProId(i + 1)
    Arr_StoreProId(i) = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    ReDim Preserve Arr_StoreProNum(i + 1)
    Arr_StoreProNum(i) = Val(ColVal)
  
    i = i + 1
  Loop
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Sub UpdateDiff(ByVal OriId As Long)
  DB_Connect
  
  SqlStmt = "Update ProInStore Set ProNum = ProNum + " _
          + Trim(Str(ProNum)) + " WHERE StoreProId=" _
          + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  '刪除數量為0的記錄
  DeleteZero
End Sub

Public Sub Update(ByVal OriId As Long)
  DB_Connect
  
  SqlStmt = "Update ProInStore Set ProNum=" _
          + Trim(Str(ProNum)) + " WHERE StoreProId=" _
          + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  '刪除數量為0的記錄
  DeleteZero
End Sub