-

VB开发的ERP系统

                            " and a.Billcode in ('1302','1303') " & _
                            " Group by a.Mnumber, a.Mname " & _
                            " ORDER BY a.Mnumber "
                End If
            Case 2          '按部门汇总
                If .Opt_Check(1).Value Then
                    SqlStr = "SELECT DeptCode, DeptName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " Group by a.DeptCode, a.DeptName " & _
                            " ORDER BY a.DeptCode "
                Else
                    SqlStr = "SELECT DeptCode, DeptName,Sum(FactissueQuan) as Quan,Sum(IssueMoney) as Money " & _
                            " From Chhs_v_InOut a " & Str_QueryCondi & _
                            " Group by a.DeptCode, a.DeptName " & _
                            " ORDER BY a.DeptCode "
                    Sqlstrfz = "SELECT DeptCode, DeptName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " and a.Billcode in ('1302','1303') " & _
                            " Group by a.DeptCode, a.DeptName " & _
                            " ORDER BY a.DeptCode "
               End If
            Case 3          '按出库类别汇总
                If .Opt_Check(1).Value Then
                    SqlStr = "SELECT InOutClassCode,InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " Group by a.InOutClassCode,a.InOutClassName " & _
                            " ORDER BY a.InOutClassCode"
                Else
                    SqlStr = "SELECT InOutClassCode,InOutClassName,Sum(FactissueQuan) as Quan,Sum(IssueMoney) as Money " & _
                            " From Chhs_v_InOut a " & Str_QueryCondi & _
                            " Group by a.InOutClassCode,a.InOutClassName " & _
                            " ORDER BY a.InOutClassCode "
                    Sqlstrfz = "SELECT InOutClassCode, InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " and a.Billcode in ('1302','1303') " & _
                            " Group by a.InOutClassCode, a.InOutClassName " & _
                            " ORDER BY a.InOutClassCode "
                End If
            Case 4          '按仓库+出库类别汇总
                If .Opt_Check(1).Value Then
                    SqlStr = "SELECT Whcode, Whname,InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " Group by a.Whcode, a.Whname,a.InOutClassName " & _
                            " ORDER BY a.Whcode "
                Else
                    SqlStr = "SELECT Whcode, Whname,InOutClassName,Sum(FactissueQuan) as Quan,Sum(IssueMoney) as Money " & _
                            " From Chhs_v_InOut a " & Str_QueryCondi & _
                            " Group by a.Whcode, a.Whname,a.InOutClassName " & _
                            " ORDER BY a.Whcode "
                    Sqlstrfz = "SELECT Whcode, Whname, InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " and a.Billcode in ('1302','1303') " & _
                            " Group by a.Whcode,a.Whname, a.InOutClassName " & _
                            " ORDER BY a.Whcode "
                End If
            Case 5          '按存货+出库类别汇总
                If .Opt_Check(1).Value Then
                    SqlStr = "SELECT Mnumber, Mname,InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " Group by a.Mnumber, a.Mname,a.InOutClassName " & _
                            " ORDER BY a.Mnumber "
                Else
                    SqlStr = "SELECT Mnumber, Mname,InOutClassName,Sum(FactissueQuan) as Quan,Sum(IssueMoney) as Money " & _
                            " From Chhs_v_InOut a " & Str_QueryCondi & _
                            " Group by a.Mnumber, a.Mname,a.InOutClassName " & _
                            " ORDER BY a.Mnumber "
                    Sqlstrfz = "SELECT Mnumber, Mname, InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " and a.Billcode in ('1302','1303') " & _
                            " Group by a.Mnumber,a.Mname, a.InOutClassName " & _
                            " ORDER BY a.Mnumber "
                End If
            Case 6          '按部门+出库类别汇总
                If .Opt_Check(1).Value Then
                    SqlStr = "SELECT DeptCode, DeptName,InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " Group by a.DeptCode, a.DeptName,a.InOutClassName " & _
                            " ORDER BY a.DeptCode "
                Else
                    SqlStr = "SELECT DeptCode, DeptName,InOutClassName,Sum(FactissueQuan) as Quan,Sum(IssueMoney) as Money " & _
                            " From Chhs_v_InOut a " & Str_QueryCondi & _
                            " Group by a.DeptCode, a.DeptName,a.InOutClassName " & _
                            " ORDER BY a.DeptCode "
                    Sqlstrfz = "SELECT DeptCode, DeptName, InOutClassName,Sum(OutQuan) as Quan,Sum(OutMoney) as Money " & _
                            " From Chhs_v_List a " & Str_QueryCondi & _
                            " and a.Billcode in ('1302','1303') " & _
                            " Group by a.DeptCode,a.DeptName, a.InOutClassName " & _
                            " ORDER BY a.DeptCode "
                End If
        End Select
    End With
    
    Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(SqlStr)
    With Rec_Query
        
        Do While Not .EOF
            '[>>自定义填充内容
            
            If CxbbGrid.FixedRows = 1 Then       '按单条件汇总
                Str = Trim(.Fields(0) & "")
                If Str = "" Then Str = "1"
                Jsqte = CxbbGrid.FindRow(Str, , 0)
                If Jsqte <= 0 Then
                    CxbbGrid.AddItem ""
                    Jsqte = CxbbGrid.Rows - 1
                End If
                CxbbGrid.TextMatrix(Jsqte, 0) = Str
                CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields(0) & "")
                CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields(1) & "")
                If .Fields("Quan") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls))) + .Fields("Quan")
                End If
                If .Fields("Money") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) + .Fields("Money")
                End If
                If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls))) <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) / Val(CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)))
                    If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls))) = 0 Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = ""
                    End If
                End If
            Else                                '按双条件汇总
                Str = Trim(.Fields(0) & "")
                If Str = "" Then Str = "1"
                Jsqte = CxbbGrid.FindRow(Str, , 0)
                If Jsqte <= 0 Then
                    CxbbGrid.AddItem ""
                    Jsqte = CxbbGrid.Rows - 1
                End If
                CxbbGrid.TextMatrix(Jsqte, 0) = Str
                CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields(0) & "")
                CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields(1) & "")
                Str = Trim(.Fields("InOutClassName") & "")
                If Str = "" Then
                    Str = "无出库类别"
                    Coljsq = Sydz("003", GridStr(), Szzls)
                Else
                    For Coljsq = Sydz("003", GridStr(), Szzls) To CxbbGrid.Cols - 2 Step 3
                        If Trim(CxbbGrid.TextMatrix(0, Coljsq)) = Str Then
                            Exit For
                        End If
                    Next Coljsq
                    If Coljsq >= CxbbGrid.Cols - 1 Then Exit Sub
                End If
                If .Fields("Quan") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Coljsq) = Val(CxbbGrid.TextMatrix(Jsqte, Coljsq)) + .Fields("Quan")
                End If
                If .Fields("Money") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Coljsq + 2) = Val(CxbbGrid.TextMatrix(Jsqte, Coljsq + 2)) + .Fields("Money")
                End If
                If Val(CxbbGrid.TextMatrix(Jsqte, Coljsq)) <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Coljsq + 1) = Val(CxbbGrid.TextMatrix(Jsqte, Coljsq + 2)) / Val(CxbbGrid.TextMatrix(Jsqte, Coljsq))
                    If Val(CxbbGrid.TextMatrix(Jsqte, Coljsq + 1)) = 0 Then
                        CxbbGrid.TextMatrix(Jsqte, Coljsq + 1) = ""
                    End If
                End If
            End If
           
            '<<]
            '设置数据行高度(Fixed)
            CxbbGrid.RowHeight(Jsqte) = Sjhgd
            
             .MoveNext
        Loop
    End With
    
    If Tjfx_OutBillSumCond.Opt_Check(0).Value Then
        Call Sub_QueryFz(Sqlstrfz)
    End If
    
    '输出合计
    With CxbbGrid
        If .Rows = .FixedRows Then Exit Sub

        '纵向合计
        .SubtotalPosition = flexSTBelow
        For Jsqte = Qslz To .Cols - 1
             If Trim(.TextMatrix(.FixedRows - 1, Jsqte)) = "数量" Or Trim(.TextMatrix(.FixedRows - 1, Jsqte)) = "金额" Then
                  .Subtotal flexSTSum, -1, Jsqte, , &HF7F3EC, , , , , True
                  If Val(.TextMatrix(.Rows - 1, Jsqte)) = 0 Then
                      .TextMatrix(.Rows - 1, Jsqte) = ""
                  End If
             End If
        Next
        Jsqte = .Rows - 1
        .RowHeight(Jsqte) = Sjhgd
        .TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = ""
        .TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = "合计 "
    End With
    
    ']以上为用户自定义部分
    
    Set Rec_Query = Nothing

End Sub
Private Sub Sub_QueryFz(Sql As String)                           '生成查询结果(Define)
    
    Dim Rec_Query As Recordset                  '查询结果动态集
    Dim Coljsq As Long                          '网格列计数器
    Dim Jsqte As Long                           '临时动态计数器
    Dim Str As String
    
    
    '以下为自定义部分[
    
    Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sql)
    With Rec_Query
        
        Do While Not .EOF
            '[>>自定义填充内容
            
            If CxbbGrid.FixedRows = 1 Then       '按单条件汇总
                Str = Trim(.Fields(0) & "")
                If Str = "" Then Str = "1"
                Jsqte = CxbbGrid.FindRow(Str, , 0)
                If Jsqte <= 0 Then
                    CxbbGrid.AddItem ""
                    Jsqte = CxbbGrid.Rows - 1
                End If
                CxbbGrid.TextMatrix(Jsqte, 0) = Str
                CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields(0) & "")
                CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields(1) & "")
                If .Fields("Quan") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls))) + .Fields("Quan")
                End If
                If .Fields("Money") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) + .Fields("Money")
                End If
                If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls))) <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) / Val(CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)))
                    If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls))) = 0 Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = ""
                    End If
                End If
            Else                                '按双条件汇总
                Str = Trim(.Fields(0) & "")
                If Str = "" Then Str = "1"
                Jsqte = CxbbGrid.FindRow(Str, , 0)
                If Jsqte <= 0 Then
                    CxbbGrid.AddItem ""
                    Jsqte = CxbbGrid.Rows - 1
                End If
                CxbbGrid.TextMatrix(Jsqte, 0) = Str
                CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields(0) & "")
                CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields(1) & "")
                Str = Trim(.Fields("InOutClassName") & "")
                If Str = "" Then
                    Str = "无出库类别"
                    Coljsq = Sydz("003", GridStr(), Szzls)
                Else
                    For Coljsq = Sydz("006", GridStr(), Szzls) To CxbbGrid.Cols - 2 Step 3
                        If Trim(CxbbGrid.TextMatrix(0, Coljsq)) = Str Then
                            Exit For
                        End If
                    Next Coljsq
                End If
                If .Fields("Quan") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Coljsq) = Val(CxbbGrid.TextMatrix(Jsqte, Coljsq)) + .Fields("Quan")
                End If
                If .Fields("Money") <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Coljsq + 2) = Val(CxbbGrid.TextMatrix(Jsqte, Coljsq + 2)) + .Fields("Money")
                End If
                If Val(CxbbGrid.TextMatrix(Jsqte, Coljsq)) <> 0 Then
                    CxbbGrid.TextMatrix(Jsqte, Coljsq + 1) = Val(CxbbGrid.TextMatrix(Jsqte, Coljsq + 2)) / Val(CxbbGrid.TextMatrix(Jsqte, Coljsq))
                    If Val(CxbbGrid.TextMatrix(Jsqte, Coljsq + 1)) = 0 Then
                        CxbbGrid.TextMatrix(Jsqte, Coljsq + 1) = ""
                    End If
                End If
            End If
           
            '<<]
            '设置数据行高度(Fixed)
            CxbbGrid.RowHeight(Jsqte) = Sjhgd
            
             .MoveNext
        Loop
    End With
    
    ']以上为用户自定义部分
    
    Set Rec_Query = Nothing

End Sub


Private Sub bbyl(bbylte As Boolean)                    '报表打印预览
    
    Dim Bbzbt$, Bbxbt() As String, bbxbtzzxs() As Integer, Bbxbtgs As Integer
    Dim Bbbwh() As String, Bbbwhzzxs() As Integer, Bbbwhgs As Integer
    Bbxbtgs = 1                                          '报 表 小 标 题 行 数
    Bbbwhgs = 0                                          '报 表 表 尾 行 数
    ReDim Bbxbt(1 To Bbxbtgs)
    ReDim bbxbtzzxs(1 To Bbxbtgs)
    If Bbbwhgs <> 0 Then
        ReDim Bbbwh(1 To Bbbwhgs)
        ReDim Bbbwhzzxs(1 To Bbbwhgs)
    End If
    Bbzbt = Lab_Title(0)
    Bbxbt(1) = ""
    bbxbtzzxs(1) = 0                                     '报表行组织形式(0-居左 1-居中 2-居右)
    Call Scyxsjb(CxbbGrid)                               '生成报表数据
    Call Scdybb(Dyymctbl, Bbzbt, Bbxbt(), bbxbtzzxs(), Bbxbtgs, Bbbwh(), Bbbwhzzxs(), Bbbwhgs, bbylte)
    If Not bbylte Then
        Unload DY_Tybbyldy
    End If

End Sub