frm+

注释：用VB开发的进销存系统源码

      End
      Begin Threed.SSCommand cmdPrint 
         Height          =   540
         Left            =   1170
         TabIndex        =   37
         TabStop         =   0   'False
         Top             =   0
         Width           =   1185
         _ExtentX        =   2090
         _ExtentY        =   953
         _Version        =   131073
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "打印[&P]"
         ButtonStyle     =   3
         PictureAlignment=   6
      End
      Begin Threed.SSCommand cmdQuery 
         Height          =   540
         Left            =   12
         TabIndex        =   10
         TabStop         =   0   'False
         Top             =   0
         Width           =   1188
         _ExtentX        =   2090
         _ExtentY        =   953
         _Version        =   131073
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "查询[&Q]"
         ButtonStyle     =   3
         PictureAlignment=   6
      End
      Begin Threed.SSCommand cmdExit 
         Height          =   540
         Left            =   2355
         TabIndex        =   9
         TabStop         =   0   'False
         Top             =   -15
         Width           =   1185
         _ExtentX        =   2090
         _ExtentY        =   953
         _Version        =   131073
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "宋体"
            Size            =   9
            Charset         =   134
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "退出[&X]"
         ButtonStyle     =   3
         PictureAlignment=   6
      End
   End
End
Attribute VB_Name = "frm配送中心库存查询"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

'*********************************
'           库存查询
'*********************************

Option Explicit

Public Rs As New ADODB.Recordset               '用于只打开单记录集时

Private strRptSQL As String
Private StoreFlag As Boolean


'生成查询条件
Private Function GenerateQuerySQLBySingle() As String
    Dim strTemp As String
    If Not StoreFlag Then
        sSQL = "SELECT 商品编码,品名,'双' AS 单位 ,SUM(数量) AS 数量,SUM(进价金额) AS 进价金额,SUM(含税进价金额) AS 含税进价金额 FROM 配送中心库存"
    Else
        sSQL = "SELECT 商品编码,品名,'双' AS 单位,SUM(数量) AS 数量,SUM(进价金额) AS 进价金额,SUM(含税进价金额) AS 含税进价金额 FROM 总库存"
    End If
    If txtSingleQty.Text <> "" Then
        strTemp = strTemp & " 数量 " & _
                AnalyseCondition(txtSingleQty.Text, False) & " AND "
    End If
    
    If txtSingleIncode.Text <> "" Then
        strTemp = strTemp & " 商品编码 " & _
                AnalyseCondition(txtSingleIncode.Text, True) & " AND "
    End If
    If txtSingleSname.Text <> "" Then
        strTemp = strTemp & " 品名 " & _
                AnalyseCondition(txtSingleSname.Text, True) & " AND "
    End If
    If txtSingleIamt.Text <> "" Then
        strTemp = strTemp & " 进价金额 " & _
                AnalyseCondition(txtSingleIamt.Text, False) & " AND "
    End If
    If txtIamtT.Text <> "" Then
        strTemp = strTemp & " 含税进价金额 " & _
                AnalyseCondition(txtIamtT.Text, False) & " AND "
    End If
'    If txtSingleRamt.Text <> "" Then
'        strTemp = strTemp & " 售价金额 " & _
'                AnalyseCondition(txtSingleRamt.Text, False) & " AND "
'    End If
    If Not StoreFlag Then
        sSQL = sSQL & " WHERE " & strTemp & " 经营方式='" & cmbSingleSaleStyle.Text & "'"
    Else
        sSQL = sSQL & " WHERE " & strTemp & " 数量<>1.111"
    End If
    sSQL = sSQL & " GROUP BY 商品编码,品名 ORDER BY 商品编码"
    GenerateQuerySQLBySingle = sSQL
End Function


Private Sub cmbBigType_Click()
    On Error Resume Next
    sSQL = "SELECT 本节点编码,本节点名称 FROM 商品分类表 WHERE 级别=2 AND 父节点名称='" & Trim(cmbBigType.Columns(1).Text) & "'"
    Set RsTemp = Nothing
    RsTemp.Open sSQL, Conn, adOpenStatic, adLockReadOnly
    cmbMidType.RemoveAll
    While Not RsTemp.EOF
        cmbMidType.AddItem RsTemp("本节点编码") & vbTab & Trim(RsTemp("本节点名称"))
        RsTemp.MoveNext
    Wend
End Sub

Private Sub cmbBigType_InitColumnProps()
    On Error Resume Next
    sSQL = "SELECT 本节点编码,本节点名称 FROM 商品分类表 WHERE 级别=1"
    Set RsTemp = Nothing
    RsTemp.Open sSQL, Conn, adOpenStatic, adLockReadOnly
    While Not RsTemp.EOF
        cmbBigType.AddItem RsTemp("本节点编码") & vbTab & Trim(RsTemp("本节点名称"))
        RsTemp.MoveNext
    Wend
End Sub

Private Sub cmbSaleStyle2_InitColumnProps()
    cmbSaleStyle2.AddItem "经销"
    cmbSaleStyle2.AddItem "代销"
End Sub

Private Sub cmbSingleSaleStyle_InitColumnProps()
    cmbSingleSaleStyle.AddItem "经销"
    cmbSingleSaleStyle.AddItem "代销"
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub


Private Sub cmdPrint_Click()
    Select Case tabQuery.Caption
        Case "按单品"
            grdDET.PrintData ssPrintAllRows, True, False
        Case "按分类"
            grdType.PrintData ssPrintAllRows, True, False
    End Select
End Sub

Private Sub cmdQuery_Click()
    
    Select Case tabQuery.Caption
        Case "按单品"
            If cmbSingleSaleStyle.Text = "" Then
                MsgBox "请选择销售方式!", vbExclamation, "提示窗口"
                Exit Sub
            End If
            sSQL = GenerateQuerySQLBySingle()
            strRptSQL = sSQL
            Set RsTemp = Nothing
            RsTemp.Open sSQL, Conn, adOpenStatic, adLockReadOnly
            grdDET.RemoveAll
            If RsTemp.EOF Then
                MsgBox "无匹配记录！", vbInformation, "提示窗口"
                Exit Sub
            End If
            txtSingleSumIamt.Text = ""
            txtSingleSumIamtT.Text = ""
            txtQty.Text = ""
            While Not RsTemp.EOF
                grdDET.AddItem RsTemp("商品编码") & vbTab & _
                                RsTemp("品名") & vbTab & _
                                RsTemp("单位") & vbTab & _
                                RsTemp("数量") & vbTab & _
                                RsTemp("进价金额") & vbTab & _
                                RsTemp("含税进价金额")
                txtSingleSumIamt.Text = Format(Val(txtSingleSumIamt.Text) + RsTemp("进价金额"), DecNum)
                txtSingleSumIamtT.Text = Format(Val(txtSingleSumIamtT.Text) + RsTemp("含税进价金额"), DecNum)
                txtQty.Text = Format(Val(txtQty.Text) + RsTemp("数量"), "#")
                RsTemp.MoveNext
            Wend

        Case "按分类"
            Dim MyTemp1, MyTemp2, MyTemp3, MyTemp4, MyTemp5
            
            If Not StoreFlag Then
                MyTemp4 = "配送中心库存"
            Else
                MyTemp4 = "总库存"
            End If
            If cmbSaleStyle2.Text = "" Then
                MsgBox "请选择销售方式!", vbExclamation, "提示窗口"
                Exit Sub
            End If
            
            If Not StoreFlag Then
                MyTemp5 = " 经营方式='" & cmbSaleStyle2.Text & "'"
            Else
                MyTemp5 = " 数量<>1.111"
            End If
            
            If cmbBigType.Text <> "" Then
                If cmbMidType.Text <> "" Then
                   MyTemp1 = " WHERE SUBSTRING(商品编码,3,2)='" & Trim(cmbBigType.Columns(0).Text) & Trim(cmbMidType.Columns(0).Text) & "' AND " & MyTemp5
                   sSQL = "SELECT SUBSTRING(商品编码,3,2) AS 分类编码,SUM(数量) AS 数量,SUM(进价金额) AS 进价金额,SUM(含税进价金额) AS 含税进价金额 FROM " & MyTemp4 & _
                        MyTemp1 & _
                        " GROUP BY SUBSTRING(商品编码,3,2) "
                Else
                   MyTemp1 = " WHERE SUBSTRING(商品编码,3,1)='" & Trim(cmbBigType.Columns(0).Text) & "' AND " & MyTemp5
                   sSQL = "SELECT SUBSTRING(商品编码,3,1) AS 分类编码,SUM(数量) AS 数量,SUM(进价金额) AS 进价金额,SUM(含税进价金额) AS 含税进价金额 FROM " & MyTemp4 & _
                        MyTemp1 & _
                        " GROUP BY SUBSTRING(商品编码,3,1) "
                End If
            Else
                sSQL = "SELECT SUBSTRING(商品编码,3,1) AS 分类编码,SUM(数量) AS 数量,SUM(进价金额) AS 进价金额,SUM(含税进价金额) AS 含税进价金额 FROM " & MyTemp4 & _
                        " WHERE " & MyTemp5 & _
                        " GROUP BY SUBSTRING(商品编码,3,1) "
            End If
            
            strRptSQL = sSQL
            Set RsTemp = Nothing
            RsTemp.Open sSQL, Conn, adOpenStatic, adLockReadOnly
            grdType.RemoveAll
            If RsTemp.EOF Then
                MsgBox "无匹配记录！", vbInformation, "提示窗口"
                Exit Sub
            End If
            txtIamt2.Text = ""
            txtIamt2T.Text = ""
            txtQty2.Text = ""
            While Not RsTemp.EOF
                grdType.AddItem RsTemp("分类编码") & vbTab & _
                                RsTemp("数量") & vbTab & _
                                RsTemp("进价金额") & vbTab & _
                                RsTemp("含税进价金额")
                txtIamt2.Text = Format(Val(txtIamt2.Text) + RsTemp("进价金额"), DecNum)
                txtIamt2T.Text = Format(Val(txtIamt2T.Text) + RsTemp("含税进价金额"), DecNum)
                txtQty2.Text = Format(Val(txtQty2.Text) + RsTemp("数量"), "#")
                RsTemp.MoveNext
            Wend
    End Select
    
End Sub


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        SendKeys "{TAB}"
    End If
End Sub

Private Sub Form_Load()
    Call SetFormToCenter(Me)
End Sub

Private Sub optStore_Click()
    StoreFlag = False
End Sub

Private Sub optTotalStore_Click()
    StoreFlag = True
End Sub