-

VB开发的ERP系统

                    .ColWidth(Coljsq) = Sjhgd * 6
                    Rec_Class1.MoveNext
                Next Coljsq
            End If
            .TextMatrix(Rowjsq, Coljsq) = "无业务类型数量"
            .TextMatrix(Rowjsq, Coljsq + 1) = "合计入库数量"
            If Not Rec_Class2.EOF Then
                Rec_Class2.MoveFirst
                For Coljsq = (Qslz + 7 + Rec_Class1.RecordCount) To (.Cols - 4)
                    .TextMatrix(Rowjsq, Coljsq) = Trim(Rec_Class2.Fields("OperType")) & "数量"
                    .ColWidth(Coljsq) = Sjhgd * 6
                    Rec_Class2.MoveNext
                Next Coljsq
            End If
            .TextMatrix(Rowjsq, (.Cols - 3)) = "无业务类型数量"
            .TextMatrix(Rowjsq, (.Cols - 2)) = "合计出库数量"
            .TextMatrix(Rowjsq, (.Cols - 1)) = "结存数量"
            .ColHidden(.Cols - 1) = True
        Next Rowjsq
        
        '数 据 网 格 高 度
        For Rowjsq = .FixedRows To .Rows - 1
            .RowHeight(Rowjsq) = Sjhgd
        Next Rowjsq
        
        '定 义 录 入 字 段 属 性
        For Coljsq = (Qslz + 4) To .Cols - 1
            .MergeCol(Coljsq) = True
            .ColAlignment(Coljsq) = flexAlignRightTop
            .FixedAlignment(Coljsq) = flexAlignGeneral
        Next Coljsq
        For Coljsq = 0 To .Cols - 1
            If .TextMatrix(0, Coljsq) = "合计入库数量" Or .TextMatrix(0, Coljsq) = "合计出库数量" Then
                .ColWidth(Coljsq) = Sjhgd * 6
            End If
        Next Coljsq
        .ColWidth(.Cols - 1) = Sjhgd * 6
        .ColWidth(.Cols - 3) = Sjhgd * 6
    End With
End Sub

Private Sub Show_Tab(Rec_Temp As Recordset)
    Dim Str_QC As String
    Dim rec_start As New Recordset
    Dim In_Count As Double
    Dim Out_Count As Double
    Dim Str_Bill As String
    Dim str_temp As String
    Dim Rec_Query As New ADODB.Recordset        '查询结果动态集
    Dim B_F1, B_F2 As Boolean
    
    Call FillGrid(Str_Class1, Str_Class2)
    Label3 = "仓库编码: " & Trim(Rec_Temp.Fields("whcode"))
    Label1 = "仓库名称: " & Trim(Rec_Temp.Fields("whname"))
    Label2 = "日期范围: " & Trim(KF_FrmOptypeSumQuery.Combo_Kjqj1.Text) & "----" & Trim(KF_FrmOptypeSumQuery.Combo_Kjqj2.Text)

        CxbbGrid.Redraw = False
        CxbbGrid.Rows = CxbbGrid.FixedRows
        jsqte = CxbbGrid.Rows
        CxbbGrid.ColHidden(CxbbGrid.Cols - fixOut - 5) = True
        CxbbGrid.ColHidden(CxbbGrid.Cols - 3) = True
        
            Str_Bill = "select a.* ,isnull((SELECT SUM(startquan) FROM KF_V_Ledger WHERE kjyear=" & Xtyear & " and period =" & Val(Mid(Trim(KF_FrmOptypeSumQuery.Combo_Kjqj1.Text), 6)) & " AND mnumber =a.mnumber and whcode='" & Trim(Rec_Temp.Fields("whcode")) & "' group by mnumber),0)as startquan " & _
                        "from (SELECT SUM(factreceiptquan)AS In_count,SUM(factissuequan)AS Out_count,mnumber,OperType, " & _
                        "(select mname from gy_material where mnumber=KF_V_InOutClass.mnumber)as mname,(select model from gy_material where KF_V_InOutClass.mnumber=gy_material.mnumber) as model, " & _
                        "(select primaryunitname from gy_material where KF_V_InOutClass.mnumber=gy_material.mnumber) as primaryunitname From KF_V_InOutClass " & _
                        "where whcode='" & Trim(Rec_Temp.Fields("whcode")) & "'  and kjyear=" & Xtyear & " and period between " & Val(Mid(Trim(KF_FrmOptypeSumQuery.Combo_Kjqj1.Text), 6)) & " and " & Val(Mid(Trim(KF_FrmOptypeSumQuery.Combo_Kjqj2.Text), 6)) & Str_Mate & "  GROUP BY mnumber, OperType)a order by mnumber"
            
            Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Str_Bill)
            Dim Sum_In As Double, Sum_Out As Double
            Sum_In = 0
            Sum_Out = 0

            If Not Rec_Query.EOF Then
                Rec_Query.MoveFirst
                CxbbGrid.AddItem ""
                CxbbGrid.TextMatrix(jsqte, Qslz) = Trim(Rec_Query.Fields("mnumber"))                '物料编码
                CxbbGrid.TextMatrix(jsqte, Qslz + 1) = Trim(Rec_Query.Fields("mname"))              '物料名称
                CxbbGrid.TextMatrix(jsqte, Qslz + 2) = Trim(Rec_Query.Fields("model"))              '规格型号
                CxbbGrid.TextMatrix(jsqte, Qslz + 3) = Trim(Rec_Query.Fields("primaryunitname"))    '计量单位
                CxbbGrid.TextMatrix(jsqte, Qslz + 4) = Val(Rec_Query.Fields("startquan"))           '期初数量
                CxbbGrid.TextMatrix(jsqte, Qslz + 4) = 0
                CxbbGrid.RowHeight(jsqte) = Sjhgd
                Do While Not Rec_Query.EOF
                    If Trim(Rec_Query.Fields("mnumber")) = Trim(CxbbGrid.TextMatrix(jsqte, Qslz)) Then      '将行转换成列
                        If Trim(Rec_Query.Fields("OperType") & "") = "" Then
                            If Val(Rec_Query.Fields("In_count")) <> 0 Then
                                CxbbGrid.ColHidden(CxbbGrid.Cols - fixOut - 5) = False
                                CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - fixOut - 5)) = Val(Rec_Query.Fields("In_count"))            '入库数量
                                Sum_In = Sum_In + Val(Rec_Query.Fields("In_count"))
                            End If
                            
                            If Val(Rec_Query.Fields("out_count")) <> 0 Then
                                CxbbGrid.ColHidden(CxbbGrid.Cols - 3) = False
                                CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - 3)) = Val(Rec_Query.Fields("Out_count"))                    '出库数量
                                Sum_Out = Sum_Out + Val(Rec_Query.Fields("Out_count"))
                            End If
                        End If
                        For Coljsq = (Qslz + 5) To CxbbGrid.Cols - 3
                            If Trim(Rec_Query.Fields("OperType")) & "数量" = Trim(CxbbGrid.TextMatrix(0, Coljsq)) Then
                                If Val(Rec_Query.Fields("In_count")) <> 0 Then
                                    CxbbGrid.TextMatrix(jsqte, Coljsq) = Val(Rec_Query.Fields("In_count"))                              '入库数量
                                    Sum_In = Sum_In + Val(Rec_Query.Fields("In_count"))
                                Else
                                    CxbbGrid.TextMatrix(jsqte, Coljsq) = Val(Rec_Query.Fields("Out_count"))                             '出库数量
                                    Sum_Out = Sum_Out + Val(Rec_Query.Fields("Out_count"))
                                End If
                            End If
                        Next
                        CxbbGrid.TextMatrix(jsqte, CxbbGrid.Cols - 2) = Sum_Out
                        CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - fixOut - 4)) = Sum_In
                        CxbbGrid.TextMatrix(jsqte, CxbbGrid.Cols - 1) = Val(CxbbGrid.TextMatrix(jsqte, Qslz + 4)) + Sum_In - Sum_Out    '结存数量

                        Rec_Query.MoveNext
                    Else
                        jsqte = jsqte + 1
                        CxbbGrid.AddItem ""
                        CxbbGrid.TextMatrix(jsqte, Qslz) = Trim(Rec_Query.Fields("mnumber"))                            '物料编码
                        CxbbGrid.TextMatrix(jsqte, Qslz + 1) = Trim(Rec_Query.Fields("mname"))                          '物料名称
                        CxbbGrid.TextMatrix(jsqte, Qslz + 2) = Trim(Rec_Query.Fields("model"))                          '规格型号
                        CxbbGrid.TextMatrix(jsqte, Qslz + 3) = Trim(Rec_Query.Fields("primaryunitname"))                '计量单位
                        CxbbGrid.TextMatrix(jsqte, Qslz + 4) = Val(Rec_Query.Fields("startquan"))                       '期初数量
                        CxbbGrid.TextMatrix(jsqte, Qslz + 4) = 0
                        CxbbGrid.RowHeight(jsqte) = Sjhgd
                        If Trim(Rec_Query.Fields("OperType") & "") = "" Then
                            If Val(Rec_Query.Fields("In_count")) <> 0 Then
                                CxbbGrid.ColHidden(CxbbGrid.Cols - fixOut - 5) = False
                                CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - fixOut - 5)) = Val(Rec_Query.Fields("In_count"))        '入库数量
                                Sum_In = Sum_In + Val(Rec_Query.Fields("In_count"))
                            End If
                            
                            If Val(Rec_Query.Fields("out_count")) <> 0 Then
                                CxbbGrid.ColHidden(CxbbGrid.Cols - 3) = False
                                CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - 3)) = Val(Rec_Query.Fields("Out_count"))                '出库数量
                                Sum_Out = Sum_Out + Val(Rec_Query.Fields("Out_count"))
                            End If
                        End If
                        For Coljsq = (Qslz + 5) To CxbbGrid.Cols - 3
                            If Trim(Rec_Query.Fields("OperType")) & "数量" = Trim(CxbbGrid.TextMatrix(0, Coljsq)) Then
                                If Val(Rec_Query.Fields("In_count")) <> 0 Then
                                    CxbbGrid.TextMatrix(jsqte, Coljsq) = Val(Rec_Query.Fields("In_count"))                          '入库数量
                                    Sum_In = Sum_In + Val(Rec_Query.Fields("In_count"))
                                Else
                                    CxbbGrid.TextMatrix(jsqte, Coljsq) = Val(Rec_Query.Fields("Out_count"))                         '出库数量
                                    Sum_Out = Sum_Out + Val(Rec_Query.Fields("Out_count"))
                                End If
                            End If
                        Next
                        CxbbGrid.TextMatrix(jsqte, CxbbGrid.Cols - 2) = Sum_Out
                        CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - fixOut - 4)) = Sum_In
                        CxbbGrid.TextMatrix(jsqte, CxbbGrid.Cols - 1) = Val(CxbbGrid.TextMatrix(jsqte, Qslz + 4)) + Sum_In - Sum_Out        '结存数量

                        Rec_Query.MoveNext
                    End If
                Loop
            Else
                Str_QC = "SELECT SUM(startquan) AS startquan, whcode, mnumber, whname, mname, primaryunitname, model" & _
                    " FROM KF_V_Ledger WHERE kjyear=" & Xtyear & " and period =" & Val(Mid(Trim(KF_FrmOptypeSumQuery.Combo_Kjqj1.Text), 6)) & " AND mnumber ='" & Trim(Rec_Mate.Fields("mnumber")) & "'" & " and whcode='" & Trim(Rec_Temp.Fields("whcode")) & "'" & _
                    " GROUP BY whcode, mnumber, whname, mname, primaryunitname, model"
                
                Set rec_start = Cw_DataEnvi.DataConnect.Execute(Str_QC)
                If Not rec_start.EOF Then
                    CxbbGrid.AddItem ""
                    CxbbGrid.TextMatrix(jsqte, Qslz) = Trim(rec_start.Fields("mnumber"))            '物料编码
                    CxbbGrid.TextMatrix(jsqte, Qslz + 1) = Trim(rec_start.Fields("mname"))          '物料名称
                    CxbbGrid.TextMatrix(jsqte, Qslz + 2) = Trim(rec_start.Fields("model"))          '规格型号
                    CxbbGrid.TextMatrix(jsqte, Qslz + 3) = Trim(rec_start.Fields("primaryunitname"))    '计量单位
                    CxbbGrid.TextMatrix(jsqte, Qslz + 4) = 0
                    CxbbGrid.TextMatrix(jsqte, Qslz + 4) = Val(rec_start.Fields("startquan"))           '期初数量
                    CxbbGrid.RowHeight(jsqte) = Sjhgd
                    CxbbGrid.TextMatrix(jsqte, CxbbGrid.Cols - 2) = Sum_Out
                    CxbbGrid.TextMatrix(jsqte, (CxbbGrid.Cols - fixOut - 4)) = Sum_In
                    CxbbGrid.TextMatrix(jsqte, CxbbGrid.Cols - 1) = Val(CxbbGrid.TextMatrix(jsqte, Qslz + 4)) + Sum_In - Sum_Out        '结存数量
                    jsqte = jsqte + 1
                End If
            End If
    Call SLGSH
    If Not SzToolbar.Buttons("Sum").Enabled Then
        Call HuiZong
    End If
    CxbbGrid.Redraw = True
End Sub
Private Sub SLGSH()         '数据格式化
    With CxbbGrid
        For hjsq = .FixedRows To .Rows - 1
            For ljsq = Qslz + 4 To .Cols - 1
                .TextMatrix(hjsq, ljsq) = Format(Trim(.TextMatrix(hjsq, ljsq)), "#,##0." + String(Xtslxsws, "0"))
                
                If Val(.TextMatrix(hjsq, ljsq)) = 0 Then
                    .TextMatrix(hjsq, ljsq) = ""
                End If
            Next ljsq
        Next hjsq
    End With
End Sub

Private Sub HuiZong()       '汇总数据
    With CxbbGrid
        If fixIn > 0 Then
            For jsq = (Qslz + 5) To (Qslz + 5 + fixIn)
                .ColHidden(jsq) = True
            Next
        End If
        If fixOut > 0 Then
            For jsq = (Qslz + 7 + fixIn) To (.Cols - 3)
                .ColHidden(jsq) = True
            Next
        End If
    
    End With
End Sub


Private Sub First()
    If Rec_WH.State = 1 Then
        If Rec_WH.RecordCount > 0 Then
            Rec_WH.MoveFirst
            Call Show_Tab(Rec_WH)
        End If
    End If
End Sub
Private Sub Last()
    If Rec_WH.State = 1 Then
        If Rec_WH.RecordCount > 0 Then
            Rec_WH.MoveLast
            Call Show_Tab(Rec_WH)
        End If
    End If
End Sub
Private Sub Prev()
    If Rec_WH.State = 1 Then
        If Rec_WH.RecordCount > 0 Then
            If Rec_WH.EOF Then
                Rec_WH.MovePrevious
            End If
            If Not Rec_WH.BOF Then
                Rec_WH.MovePrevious
                If Not Rec_WH.BOF Then
                    Call Show_Tab(Rec_WH)
                End If
            End If
        End If
    End If
End Sub
Private Sub Sub_next()
    If Rec_WH.State = 1 Then
        If Rec_WH.RecordCount > 0 Then
            If Rec_WH.BOF Then
                Rec_WH.MoveNext
            End If
            If Not Rec_WH.EOF Then
                Rec_WH.MoveNext
                If Not Rec_WH.EOF Then
                    Call Show_Tab(Rec_WH)
                End If
            End If
        End If
    End If
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 = ReportTitle
    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