来自「VB开发的ERP系统」· 代码 · 共 741 行 · 第 1/3 页

    Dim Jsqte As Integer, FirstFlag As Boolean, OperateFlag As Boolean   '定义变量
    Dim RecTemp As New ADODB.Recordset, RecAdo As New ADODB.Recordset, recData As New ADODB.Recordset, RecAdoYear As New ADODB.Recordset '定义数据库
    Dim i As Integer   '定义网各行
    On Error Resume Next
    CxbbGrid.Clear
    SumValue = 0
    FirstFlag = False
    OperateFlag = False
    If RecTemp.State = 1 Then RecTemp.Close
    RecTemp.Open "select * from cwzz_cashflowitem order by logicid", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic  '取出现金流量项目
    CxbbGrid.Rows = 1
    CxbbGrid.Cols = 5
    CxbbGrid.FormatString = "编  码|项  目|行  次|本月数|本年累计"
    CxbbGrid.FixedAlignment(1) = flexAlignCenterCenter
    CxbbGrid.FixedAlignment(2) = flexAlignCenterCenter
    CxbbGrid.FixedAlignment(3) = flexAlignCenterCenter
    CxbbGrid.FixedAlignment(4) = flexAlignCenterCenter
    i = 1
    Do While Not RecTemp.EOF()    '循环查找数据
        If i >= CxbbGrid.Rows - 1 Then
            CxbbGrid.AddItem ""
            CxbbGrid.ColWidth(1) = 5000
            CxbbGrid.ColWidth(2) = 600
            CxbbGrid.ColWidth(3) = 2500
            CxbbGrid.ColWidth(4) = 2500
            CxbbGrid.ColAlignment(2) = flexAlignCenterCenter
        End If
        CxbbGrid.TextMatrix(i, 0) = RecTemp.Fields!CASHFLOWCODE
        CxbbGrid.TextMatrix(i, 1) = RecTemp.Fields!CASHFLOWNAME
        If RecTemp.Fields!RowNo = 0 Then
            CxbbGrid.TextMatrix(i, 2) = ""
        Else
            CxbbGrid.TextMatrix(i, 2) = RecTemp.Fields!RowNo
        End If
        If Trim(RecTemp.Fields!DataSource) = "" Then   '如果为空
            If RecAdo.State = 1 Then RecAdo.Close
            RecAdo.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Trim(RecTemp.Fields!CASHFLOWCODE) & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            If RecAdo.EOF = False Then
                If IsNull(RecAdo.Fields!fpje) = False Then
                    CxbbGrid.TextMatrix(i, 3) = Format(RecAdo.Fields!fpje, "#,###.00")
                Else
                    CxbbGrid.TextMatrix(i, 3) = ""
                End If
            End If
            If RecAdoYear.State = 1 Then RecAdoYear.Close
            RecAdoYear.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Trim(RecTemp.Fields!CASHFLOWCODE) & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period<='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            If RecAdoYear.EOF = False Then
                If IsNull(RecAdoYear.Fields!fpje) = False Then
                    CxbbGrid.TextMatrix(i, 4) = Format(RecAdoYear.Fields!fpje, "#,###.00")
                Else
                    CxbbGrid.TextMatrix(i, 4) = ""
                End If
            End If
        Else                                                         '从网格中直接取数
            SumValue = 0
            FirstFlag = False
            OperateFlag = False
            For Jsqte = 1 To Len(Trim(RecTemp.Fields!DataSource))
                If Mid(Trim(RecTemp.Fields!DataSource), Jsqte, 1) >= "0" And Mid(Trim(RecTemp.Fields!DataSource), Jsqte, 1) <= "9" Then
                    If Not FirstFlag Then
                        First = First & Mid(Trim(RecTemp.Fields!DataSource), Jsqte, 1)
                    Else
                        Second = Second & Mid(Trim(RecTemp.Fields!DataSource), Jsqte, 1)
                    End If
                Else
                    If Not OperateFlag Then
                        OperateFirst = Mid(Trim(RecTemp.Fields!DataSource), Jsqte, 1)
                    Else
                        OperateSecond = Mid(RecTemp.Fields!DataSource, Jsqte, 1)
                        
                    End If
                    If FirstFlag = False Then FirstFlag = True
                    If OperateFlag Then
                        Dim FirstFpje, SecondFpje, FirstFpjeYear, SecondFpjeYear
                        Select Case OperateFirst
                        Case "+"
                            
                            If FirstFlag Then
                                If RecAdo.State = 1 Then RecAdo.Close
                                RecAdo.Open "select * from cwzz_cashflowitem where rowno='" & First & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If RecAdo.EOF = False Then
                                    If Trim(RecAdo.Fields!CASHFLOWCODE) <> "" Then
                                        First = RecAdo.Fields!CASHFLOWCODE
                                    End If
                                End If
                                
                                If recData.State = 1 Then recData.Close
                                recData.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & First & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If recData.EOF = False Then
                                    If IsNull(recData.Fields!fpje) = False Then
                                        FirstFpje = recData.Fields!fpje
                                    End If
                                End If
                                If RecAdoYear.State = 1 Then RecAdoYear.Close
                                RecAdoYear.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & First & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period<='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If RecAdoYear.EOF = False Then
                                    If IsNull(RecAdoYear.Fields!fpje) = False Then
                                        FirstFpjeYear = RecAdoYear.Fields!fpje
                                    End If
                                End If
                                If RecAdo.State = 1 Then RecAdo.Close
                                RecAdo.Open "select * from cwzz_cashflowitem where rowno='" & Second & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                Second = RecAdo.Fields!CASHFLOWCODE
                                If recData.State = 1 Then recData.Close
                                recData.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Second & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If recData.EOF = False Then
                                    If IsNull(recData.Fields!fpje) = False Then
                                        SecondFpje = recData.Fields!fpje
                                    Else
                                        SecondFpje = 0
                                    End If
                                End If
                                If RecAdoYear.State = 1 Then RecAdoYear.Close
                                RecAdoYear.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Second & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period<='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If RecAdoYear.EOF = False Then
                                    If IsNull(RecAdoYear.Fields!fpje) = False Then
                                        SecondFpjeYear = RecAdoYear.Fields!fpje
                                    Else
                                        SecondFpjeYear = 0
                                    End If
                                End If
                                SumValue = SumValue + Val(FirstFpje) + Val(SecondFpje)
                                SumValueYear = SumValueYear + Val(FirstFpjeYear) + Val(SecondFpjeYear)
                                First = 0
                                Second = 0
                                FirstFpje = 0
                                SecondFpje = 0
                                FirstFpjeYear = 0
                                SecondFpjeYear = 0
                            End If
                            If FirstFlag = False Then FirstFlag = True
                        Case "-"
                            
                            If FirstFlag Then
                                If RecAdo.State = 1 Then RecAdo.Close
                                RecAdo.Open "select * from cwzz_cashflowitem where rowno='" & First & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If RecAdo.EOF = False Then
                                    If Trim(RecAdo.Fields!CASHFLOWCODE) <> "" Then
                                        First = RecAdo.Fields!CASHFLOWCODE
                                    End If
                                End If
                                
                                If recData.State = 1 Then recData.Close
                                recData.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & First & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If recData.EOF = False Then
                                    If IsNull(recData.Fields!fpje) = False Then
                                        FirstFpje = recData.Fields!fpje
                                    End If
                                End If
                                If RecAdoYear.State = 1 Then RecAdoYear.Close
                                RecAdoYear.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & First & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period<='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If RecAdoYear.EOF = False Then
                                    If IsNull(RecAdoYear.Fields!fpje) = False Then
                                        FirstFpjeYear = RecAdoYear.Fields!fpje
                                    End If
                                End If
                                If RecAdo.State = 1 Then RecAdo.Close
                                RecAdo.Open "select * from cwzz_cashflowitem where rowno='" & Second & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                Second = RecAdo.Fields!CASHFLOWCODE
                                If recData.State = 1 Then recData.Close
                                recData.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Second & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If recData.EOF = False Then
                                    If IsNull(recData.Fields!fpje) = False Then
                                        SecondFpje = recData.Fields!fpje
                                    Else
                                        SecondFpje = 0
                                    End If
                                End If
                                If RecAdoYear.State = 1 Then RecAdoYear.Close
                                RecAdoYear.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Second & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period<='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                                If RecAdoYear.EOF = False Then
                                    If IsNull(RecAdoYear.Fields!fpje) = False Then
                                        SecondFpjeYear = RecAdoYear.Fields!fpje
                                    Else
                                        SecondFpjeYear = 0
                                    End If
                                End If
                                SumValue = SumValue + Val(FirstFpje) - Val(SecondFpje)
                                SumValueYear = SumValueYear + Val(FirstFpjeYear) - Val(SecondFpjeYear)
                                First = 0
                                Second = 0
                                FirstFpje = 0
                                SecondFpje = 0
                                FirstFpjeYear = 0
                                SecondFpjeYear = 0
                            End If
                            If FirstFlag = False Then FirstFlag = True
                        End Select
                    End If
                    If OperateFlag Then OperateFirst = OperateSecond
                    If OperateFlag = False Then OperateFlag = True
                End If
            Next Jsqte
            If RecAdo.State = 1 Then RecAdo.Close                       '最后一行数据处理
            If Second <> 0 Then
                RecAdo.Open "select * from cwzz_cashflowitem where rowno='" & Second & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            Else
                If First <> "" Then
                    RecAdo.Open "select * from cwzz_cashflowitem where rowno='" & First & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                End If
            End If
            If RecAdo.EOF = False Then
                Second = RecAdo.Fields!CASHFLOWCODE
            End If
            If recData.State = 1 Then recData.Close
            recData.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Second & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            If recData.EOF = False Then
                If IsNull(recData.Fields!fpje) = False Then
                    SecondFpje = recData.Fields!fpje
                Else
                    SecondFpje = 0
                End If
            End If
            If RecAdoYear.State = 1 Then RecAdoYear.Close
            RecAdoYear.Open "select sum(fpje) as fpje from cwzz_cashflowdata where cashflowid='" & Second & "'and year='" & Format(Combo1.Text, "yyyy") & "' and period<='" & Format(Combo1.Text, "mm") & "'", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            If RecAdoYear.EOF = False Then
                If IsNull(RecAdoYear.Fields!fpje) = False Then
                    SecondFpjeYear = RecAdoYear.Fields!fpje
                Else
                    SecondFpjeYear = 0
                End If
            End If
            '完整数据
            If OperateFirst = "+" Then SumValue = SumValue + Val(FirstFpje) + Val(SecondFpje): SumValueYear = SumValueYear + Val(FirstFpjeYear) + Val(SecondFpjeYear)
            If OperateFirst = "-" Then SumValue = SumValue + Val(FirstFpje) - Val(SecondFpje): SumValueYear = SumValueYear + Val(FirstFpjeYear) - Val(SecondFpjeYear)
            If SumValue = 0 Then
                CxbbGrid.TextMatrix(i, 3) = ""
            Else
                CxbbGrid.TextMatrix(i, 3) = Format(SumValue, "#,###.00")
            End If
            If SumValueYear = 0 Then
                CxbbGrid.TextMatrix(i, 4) = ""
            Else
                CxbbGrid.TextMatrix(i, 4) = Format(SumValueYear, "#,###.00")
            End If
        End If
        SumValue = 0
        SumValueYear = 0
        First = 0
        Second = 0
        i = i + 1
        RecTemp.MoveNext
    Loop
End Sub