Attribute VB_Name = "ModMain"
'強制變量聲明
Option Explicit
'定義用于存儲系統(tǒng)登陸用戶名的全局變量
Public strCurUser As String
'定義存儲格式化后的日期的全局變量
Public dteSysDate As Date
'定義存儲當(dāng)前窗體句柄的全局變量
Public intNumWindows As Integer
'定義存儲數(shù)據(jù)庫連接字符串的全局變量
Public strConnect As String

Sub main()
    '顯示系統(tǒng)登陸窗體
    frmLogin.Show vbModal
    '根據(jù)frmLoginLogin窗體的Succeeded變量值來判斷輸入的用戶名和密碼是否正確
    If Not frmLogin.LoginSucceeded Then
        '輸入的用戶名和密碼不正確，結(jié)束該系統(tǒng)的執(zhí)行
        End
    End If
    '輸入的用戶名和密碼正確,釋放系統(tǒng)登陸窗體
    Unload frmLogin
    '裝載倉庫管理系統(tǒng)的主窗體
    Load frmMain
    '顯示倉庫管理系統(tǒng)的主窗體
    frmMain.Show
    '顯示系統(tǒng)當(dāng)前時間的設(shè)置窗體
    FrmSysDate.Show vbModal
End Sub

Public Sub SetFormStu(mFrmChi As Form, mFrmFat As Form)
    mFrmChi.Top = (mFrmFat.Height - mFrmChi.Height) / 2 - 300
    mFrmChi.Left = (mFrmFat.Width - mFrmChi.Width) / 2
End Sub

Public Function OpenWindow(intTmp As Integer)
    OpenWindow = intTmp + 1
End Function

Public Function Closewindow(intTmp As Integer)
    Closewindow = intTmp - 1
End Function

Public Function Product_Status(strProNum As String) As String
    '定義返回product表和mat_head表中的物品信息的記錄的數(shù)據(jù)集對象rsProSta
    Dim rsProSta As ADODB.Recordset
    '定義返回已審核的入庫詳細信息表中記錄的數(shù)據(jù)集對象rsOrdDA
    Dim rsOrdDA As ADODB.Recordset
    '定義返回出庫詳細信息表中記錄的數(shù)據(jù)集對象rsSalDA
    Dim rsSalDA As ADODB.Recordset
    '定義存儲sql語句的變量strSQL
    Dim strSQL As String
    '定義存儲現(xiàn)有物品的數(shù)量的變量sngOrdQty和出庫數(shù)量的變量sngSalQty
    Dim sngOrdQty, sngSalQty As Single
    '定義存儲現(xiàn)有物品的價格的變量CurOrdPrice和出庫價格的變量CurSalPrice
    Dim CurOrdPrice, CurSalPrice As Currency
    '初始化數(shù)據(jù)集對象rsProSta、rsOrdDA和rsSalDA
    Set rsProSta = New ADODB.Recordset
    Set rsOrdDA = New ADODB.Recordset
    Set rsSalDA = New ADODB.Recordset
    '設(shè)置并執(zhí)行sql語句，以返回數(shù)據(jù)集對象rsProSta，使其包含物品信息
    '（物品名稱、規(guī)格型號、數(shù)量和價錢）的記錄
    strSQL = "select a.product_name,a.product_model,b.qty,b.price from " & _
    "product a,mat_head b where a.p_id=b.p_id and a.p_id='" & strProNum & "'"
    rsProSta.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
    '設(shè)置并執(zhí)行sql語句，以返回數(shù)據(jù)集對象rsOrdDA，使其包含已審核的入庫
    '詳細信息表中的strProNum指定的物品數(shù)量和價格
    strSQL = "select p_id,sum(qty) as oqty,sum(price) as oprice from " & _
    "order_detail_b where p_id='" & strProNum & "' group by p_id"
    rsOrdDA.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
    '設(shè)置并執(zhí)行sql語句，以返回數(shù)據(jù)集對象rsSalDA，使其包含出庫詳細信
    '息表中的strProNum指定的物品數(shù)量和價格
    strSQL = "select p_id,sum(qty) as sqty,sum(price) as sprice from " & _
    "sale_detail_a where p_id='" & strProNum & "' group by p_id"
    rsSalDA.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
    With rsOrdDA
        '判斷數(shù)據(jù)集rsOrdDA中的記錄是否為空
        If .RecordCount <> 0 Then
            '數(shù)據(jù)集rsOrdDA中的記錄不為空，將strProNum指定的物品數(shù)量和
            '價格分別賦值給變量sngOrdQty和CurOrdPrice
            sngOrdQty = !oqty
            CurOrdPrice = !oprice
        Else
          '數(shù)據(jù)集rsOrdDA中的記錄為空，將sngOrdQty和CurOrdPrice代表的
          '物品數(shù)量和價格分別賦值給0
            sngOrdQty = 0
            CurOrdPrice = 0
        End If
    End With
    With rsSalDA
       '判斷數(shù)據(jù)集rsSalDA中的記錄是否為空
        If .RecordCount <> 0 Then
            '數(shù)據(jù)集rsSalDA中的記錄不為空，將strProNum指定的物品數(shù)量和
            '價格分別賦值給變量sngSalQty和CurSalPrice
            sngSalQty = !sqty
            CurSalPrice = !sprice
        Else
          '數(shù)據(jù)集rsSalDA中的記錄為空，將sngSalQty和CurSalPrice代表的
          '物品數(shù)量和價格分別賦值給0
            sngSalQty = 0
            CurSalPrice = 0
        End If
    End With
  '判斷數(shù)據(jù)集rsProSta中的記錄是否為空
    If rsProSta.RecordCount = 0 Then
      '數(shù)據(jù)集rsProSta中的記錄為空,設(shè)置函數(shù)返回值為空，
      '以表示strProNum代表的物品的信息為空
        Product_Status = ""
    Else
      '數(shù)據(jù)集rsProSta中的記錄不為空,用變量strsql的值設(shè)置函數(shù)的返回值，
      '以表示strProNum代表的物品的信息
        With rsProSta
            .MoveFirst
            strSQL = !product_name & "    " & !product_model & "    數(shù)量:" & _
            sngOrdQty + !qty - sngSalQty & "    金額:" & CurOrdPrice + !price - CurSalPrice
        End With
        Product_Status = strSQL
    End If
    '關(guān)閉數(shù)據(jù)集對象rsProSta
    rsProSta.Close
    Set rsProSta = Nothing
End Function

'Public Function Sale_Status(strSalNum As String, strUnPr As String) As String
'    Dim rsSalSta As ADODB.Recordset
'    Dim strSQL As String
'    Set rsSalSta = New ADODB.Recordset
'    strSQL = "select a.product_name,a.product_model,b.qty from " & _
'    "product a,mat_detail b where a.p_id=b.p_id and a.p_id='" & strSalNum _
'    & "' and b.unit_price=ccur('" & strUnPr & "')"
'    rsSalSta.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
'    If rsSalSta.RecordCount = 0 Then
'        Sale_Status = ""
'    Else
'        With rsSalSta
'            .MoveFirst
'            strSQL = !product_name & "    " & !product_model & "    單價:" & _
'            strUnPr & "    數(shù)量:" & !qty
'        End With
'        Sale_Status = strSQL
'    End If
'    rsSalSta.Close
'    Set rsSalSta = Nothing
'End Function

Public Function SaleTooLarge(strSaleID As String, _
                            strSalPro As String, strSalQty As String) As Boolean
    '定義返回物品累計信息表中記錄的數(shù)據(jù)集對象rsMatQty
    Dim rsMatQty As ADODB.Recordset
    '定義返回已審核的入庫詳細信息表中記錄的數(shù)據(jù)集對象rsOrdDA
    Dim rsOrdDA As ADODB.Recordset
    '定義返回未審核的出庫詳細信息表中記錄的數(shù)據(jù)集對象rsSalDA
    Dim rsSalDA As ADODB.Recordset
    '定義存儲sql語句的變量strSQL
    Dim strSQL As String
    '定義存儲現(xiàn)有物品的數(shù)量的變量sngMatQty、存儲已審核的入庫
    '單中的物品數(shù)量sngOrdQty和出庫數(shù)量的變量sngSalQty
    Dim sngMatQty, sngOrdQty, sngSalQty As Single
    '初始化數(shù)據(jù)集對象rsMatQty、rsOrdDA和rsSalDA
    Set rsMatQty = New ADODB.Recordset
    Set rsOrdDA = New ADODB.Recordset
    Set rsSalDA = New ADODB.Recordset
     '設(shè)置并執(zhí)行sql語句，以返回數(shù)據(jù)集對象rsMatQty，使其包含物
     '品匯總信息表中的物品編號 strSalPro 的物品的可用數(shù)量
    strSQL = "select qty from mat_head where p_id='" & strSalPro & "'"
    rsMatQty.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
     '設(shè)置并執(zhí)行sql語句，以返回數(shù)據(jù)集對象rsOrdDA，使其包含未審核的入
     '庫詳細信息表中的物品編號 strSalPro 的物品的數(shù)量
    strSQL = "select p_id,sum(qty) as oqty from " & _
    "order_detail_b where p_id='" & strSalPro & "' group by p_id"
    rsOrdDA.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
     '設(shè)置并執(zhí)行sql語句，以返回數(shù)據(jù)集對象rsSalDA，使其包含出庫詳細
     '信息表中的物品編號 strSalPro 的物品的數(shù)量
    strSQL = "select p_id,sum(qty) as sqty from " & _
    "sale_detail_a where p_id='" & strSalPro & "' and sale_id<>'" & _
    strSaleID & "' group by p_id"
    rsSalDA.Open strSQL, DEjxc.Conjxc, adOpenStatic, adLockReadOnly
    With rsOrdDA
        '判斷數(shù)據(jù)集rsOrdDA中的記錄數(shù)是否為0
        If .RecordCount <> 0 Then
            '數(shù)據(jù)集rsOrdDA中的記錄數(shù)不為0,將strSalPro指定的物品數(shù)量賦值
            '給變量sngOrdQty,以表示入庫詳細信息表中的該種物品的數(shù)量
            sngOrdQty = !oqty
        Else
            '數(shù)據(jù)集rsOrdDA中的記錄數(shù)為0,將變量sngOrdQty置為0,以表示入庫