frmac_balanceresult.frm

一个用VB写的财务软件源码

    End If
   'end  ********
    If m_sBalanceFrom <> "" Then
        QueryStr = QueryStr & " a.ljj" & HappenMonthTo & "-a.ljd" & HappenMonthTo & _
                    ">=" & m_sBalanceFrom & " AND"
    End If
    If m_sBalanceTo <> "" Then
        QueryStr = QueryStr & " a.ljj" & HappenMonthTo & "-a.ljd" & HappenMonthTo & _
                    "<=" & m_sBalanceTo & " AND"
    End If
    If m_sSubjectType <> "" Then
        QueryStr = QueryStr & " b.kmlx = '" & m_sSubjectType & "' AND"
    End If
    If m_bIsEndLevelSubject Then
        QueryStr = QueryStr & " b.IsEndKm = -1"
    Else
        QueryStr = QueryStr & " b.kmjc >= " & m_iSubjectLevelFrom & _
                    " AND b.kmjc <= " & m_iSubjectLevelTo
    End If
    
    If Trim(QueryStr) = "" Then
        sSQL = Left(sSQL, Len(sSQL) - 4)
    End If
    sSQL = sSQL & QueryStr
    Select Case m_sSort
        Case "1"
            OrderStr = " ORDER BY a.kmdm,kmlx"
        Case "2"
            OrderStr = " ORDER BY a.kmdm desc,kmlx DESC"
        Case "3"
            OrderStr = " ORDER BY End_Balance_Money"
        Case "4"
            OrderStr = " ORDER BY End_Balance_Money DESC"
        Case "5"
            OrderStr = " ORDER BY kmdm,End_Balance_Money"
        Case "6"
            OrderStr = " ORDER BY kmdm desc,End_Balance_Money DESC"
    End Select
    sSQL = sSQL & OrderStr
    
    '求出未记账凭证的科目代码、方向、数量（外币、金额）借方金额和、贷方金额和及余额和
    If m_bIncludeNotRecordVoucher Then
        Call GetNotRecordData(bFound, bFoundBeforeBegin)
    End If
    
    lCount = 0
    lPage = 0
    iMaxColWidth = 0
    Set rstTemp = New ADODB.Recordset
    rstTemp.CursorLocation = adUseClient
    rstTemp.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
    bFristKmjc = False
    With rstTemp
        If Not (.EOF And .BOF) Then
            .MoveFirst
            
            Do Until .EOF
                lCount = lCount + 1
                NotRecord_Debit_Amount = 0
                NotRecord_Debit_Foreign = 0
                NotRecord_Debit_Money = 0
                NotRecord_Credit_Amount = 0
                NotRecord_Credit_Foreign = 0
                NotRecord_Credit_Money = 0
                NotRecord_Balance_Amount = 0
                NotRecord_Balance_Foreign = 0
                NotRecord_Balance_Money = 0

                BNotRecord_Debit_Amount = 0
                BNotRecord_Debit_Foreign = 0
                BNotRecord_Debit_Money = 0
                BNotRecord_Credit_Amount = 0
                BNotRecord_Credit_Foreign = 0
                BNotRecord_Credit_Money = 0
                BNotRecord_Balance_Amount = 0
                BNotRecord_Balance_Foreign = 0
                BNotRecord_Balance_Money = 0
                '求出各个科目的数量和、外币和、金额和
                If bFound Then
                    For i = LBound(NotRecordData) To UBound(NotRecordData)
                        If NotRecordData(i).sKmdm Like Trim(.Fields("kmdm").value) & "*" Then
                            If NotRecordData(i).sFx = "借" Then
                                NotRecord_Debit_Amount = NotRecord_Debit_Amount + NotRecordData(i).dSumAmount
                                NotRecord_Debit_Foreign = NotRecord_Debit_Foreign + NotRecordData(i).dSumForeign
                                NotRecord_Debit_Money = NotRecord_Debit_Money + NotRecordData(i).dSumMoney
                            Else
                                NotRecord_Credit_Amount = NotRecord_Credit_Amount + NotRecordData(i).dSumAmount
                                NotRecord_Credit_Foreign = NotRecord_Credit_Foreign + NotRecordData(i).dSumForeign
                                NotRecord_Credit_Money = NotRecord_Credit_Money + NotRecordData(i).dSumMoney
                            End If
                        End If
                    Next i
                    NotRecord_Balance_Amount = NotRecord_Debit_Amount - NotRecord_Credit_Amount
                    NotRecord_Balance_Foreign = NotRecord_Debit_Foreign - NotRecord_Credit_Foreign
                    NotRecord_Balance_Money = NotRecord_Debit_Money - NotRecord_Credit_Money
                End If
                 
                If bFoundBeforeBegin Then
                    For i = LBound(NotRecordBeforeBegin) To UBound(NotRecordBeforeBegin)
                        If NotRecordBeforeBegin(i).sKmdm Like Trim(.Fields("kmdm").value) & "*" Then
                            If NotRecordBeforeBegin(i).sFx = "借" Then
                                BNotRecord_Debit_Amount = BNotRecord_Debit_Amount + NotRecordBeforeBegin(i).dSumAmount
                                BNotRecord_Debit_Foreign = BNotRecord_Debit_Foreign + NotRecordBeforeBegin(i).dSumForeign
                                BNotRecord_Debit_Money = BNotRecord_Debit_Money + NotRecordBeforeBegin(i).dSumMoney
                            Else
                                BNotRecord_Credit_Amount = BNotRecord_Credit_Amount + NotRecordBeforeBegin(i).dSumAmount
                                BNotRecord_Credit_Foreign = BNotRecord_Credit_Foreign + NotRecordBeforeBegin(i).dSumForeign
                                BNotRecord_Credit_Money = BNotRecord_Credit_Money + NotRecordBeforeBegin(i).dSumMoney
                            End If
                        End If
                    Next i
                    BNotRecord_Balance_Amount = BNotRecord_Debit_Amount - BNotRecord_Credit_Amount
                    BNotRecord_Balance_Foreign = BNotRecord_Debit_Foreign - BNotRecord_Credit_Foreign
                    BNotRecord_Balance_Money = BNotRecord_Debit_Money - BNotRecord_Credit_Money
                End If
                sBegin_Direction = Left(.Fields("yefx").value & "", 1)
                bTemp = .Fields("isEndkm").value
                
                
                '求出该科目所有凭证的数量和、外币和、金额和
                dBegin_Debit_Money = CDbl(.Fields("Begin_Debit_Money").value & "") + BNotRecord_Debit_Money
                dBegin_Credit_Money = CDbl(.Fields("Begin_Credit_Money").value & "") + BNotRecord_Credit_Money
    
                If bTemp Then
                    If sBegin_Direction = "借" Then
                        dBegin_Balance_Amount = CDbl(.Fields("Begin_Balance_Amount").value & "")
                        dBegin_Balance_Foreign = CDbl(.Fields("Begin_Balance_Foreign").value & "")
                        dBegin_Balance_Money = CDbl(.Fields("Begin_Debit_Money").value & "") - CDbl(.Fields("Begin_Credit_Money").value & "")
                    Else
                        dBegin_Balance_Amount = -CDbl(.Fields("Begin_Balance_Amount").value & "")
                        dBegin_Balance_Foreign = -CDbl(.Fields("Begin_Balance_Foreign").value & "")
                        dBegin_Balance_Money = CDbl(.Fields("Begin_Credit_Money").value & "") - CDbl(.Fields("Begin_Debit_Money").value & "")
                    End If
                    dBegin_Balance_Amount = dBegin_Balance_Amount + BNotRecord_Balance_Amount
                    dBegin_Balance_Foreign = dBegin_Balance_Foreign + BNotRecord_Balance_Foreign
                    dBegin_Balance_Money = dBegin_Balance_Money + BNotRecord_Balance_Money
                Else
                    dBegin_Balance_Amount = 0
                    dBegin_Balance_Foreign = 0
                    If sBegin_Direction = "借" Then
                        dBegin_Balance_Money = CDbl(.Fields("Begin_Debit_Money").value & "") - CDbl(.Fields("Begin_Credit_Money").value & "")
                    Else
                        dBegin_Balance_Money = CDbl(.Fields("Begin_Credit_Money").value & "") - CDbl(.Fields("Begin_Debit_Money").value & "")
                    End If
                    dBegin_Balance_Money = dBegin_Balance_Money + BNotRecord_Balance_Money
                End If
                
                '总合计
                '2002.10.25     bian    期初相当于借方
                If GetKmJc(Trim(.Fields("kmdm").value)) = m_iSubjectLevelFrom - 1 Or bFristKmjc = False Then
                    If sBegin_Direction_Total = "" Then
                       sBegin_Direction_Total = sBegin_Direction
                    End If
                    If sBegin_Direction = "借" Then
                        dBegin_Debit_Money_Total = dBegin_Debit_Money_Total + dBegin_Balance_Money
                    Else
                        dBegin_Credit_Money_Total = dBegin_Credit_Money_Total + dBegin_Balance_Money
                    End If
                    If sBegin_Direction = "借" Then
                        dBegin_Balance_Money_Total = dBegin_Balance_Money_Total + dBegin_Balance_Money
                        dBegin_Debit_Moneys = dBegin_Debit_Moneys + dBegin_Balance_Money
                    Else
                        dBegin_Balance_Money_Total = dBegin_Balance_Money_Total - dBegin_Balance_Money
                        dBegin_Credit_Moneys = dBegin_Credit_Moneys + dBegin_Balance_Money
                    End If
                    bFristKmjc = (GetKmJc(Trim(.Fields("kmdm").value)) = m_iSubjectLevelFrom - 1)
                End If
                
                '本期发生
                If bTemp Then
                    dHappen_Debit_Amount = .Fields("Happen_Debit_Amount").value + NotRecord_Debit_Amount
                    dHappen_Debit_Foreign = .Fields("Happen_Debit_Foreign").value + NotRecord_Debit_Foreign
                    dHappen_Debit_Money = .Fields("Happen_Debit_Money").value + NotRecord_Debit_Money
                    dHappen_Credit_Amount = .Fields("Happen_Credit_Amount").value + NotRecord_Credit_Amount
                    dHappen_Credit_Foreign = .Fields("Happen_Credit_Foreign").value + NotRecord_Credit_Foreign
                    dHappen_Credit_Money = .Fields("Happen_Credit_Money").value + NotRecord_Credit_Money
                    
                    '本期累计
                    dTotal_Debit_Amount = .Fields("Total_Debit_Amount").value + NotRecord_Debit_Amount + BNotRecord_Debit_Amount
                    dTotal_Debit_Foreign = .Fields("Total_Debit_Foreign").value + NotRecord_Debit_Foreign + BNotRecord_Debit_Foreign
                    dTotal_Debit_Money = .Fields("Total_Debit_Money").value + NotRecord_Debit_Money + BNotRecord_Debit_Money
                    dTotal_Credit_Amount = .Fields("Total_Credit_Amount").value + NotRecord_Credit_Amount + BNotRecord_Credit_Amount
                    dTotal_Credit_Foreign = .Fields("Total_Credit_Foreign").value + NotRecord_Credit_Foreign + BNotRecord_Credit_Foreign
                    dTotal_Credit_Money = .Fields("Total_Credit_Money").value + NotRecord_Credit_Money + BNotRecord_Credit_Money
                Else
                    dHappen_Debit_Amount = 0
                    dHappen_Debit_Foreign = 0
                    dHappen_Debit_Money = .Fields("Happen_Debit_Money").value + NotRecord_Debit_Money
                    dHappen_Credit_Amount = 0
                    dHappen_Credit_Foreign = 0
                    dHappen_Credit_Money = .Fields("Happen_Credit_Money").value + NotRecord_Credit_Money
                    
                    '本期累计
                    dTotal_Debit_Amount = 0
                    dTotal_Debit_Foreign = 0
                    dTotal_Debit_Money = .Fields("Total_Debit_Money").value + NotRecord_Debit_Money + BNotRecord_Debit_Money
                    dTotal_Credit_Amount = 0
                    dTotal_Credit_Foreign = 0
                    dTotal_Credit_Money = .Fields("Total_Credit_Money").value + NotRecord_Credit_Money + BNotRecord_Credit_Money
                End If
                '总合计
                If GetKmJc(Trim(.Fields("kmdm").value)) = m_iSubjectLevelFrom - 1 Or bFristKmjc = False Then
                    dHappen_Debit_Money_Total = dHappen_Debit_Money_Total + dHappen_Debit_Money
                    dHappen_Credit_Money_Total = dHappen_Credit_Money_Total + dHappen_Credit_Money
                    dTotal_Debit_Money_Total = dTotal_Debit_Money_Total + dTotal_Debit_Money
                    dTotal_Credit_Money_Total = dTotal_Credit_Money_Total + dTotal_Credit_Money
                    bFristKmjc = (GetKmJc(Trim(.Fields("kmdm").value)) = m_iSubjectLevelFrom - 1)
                End If
                
                
                '计算余额
                dEnd_Debit_Money = dBegin_Debit_Money + dHappen_Debit_Money
                dEnd_Credit_Money = dBegin_Credit_Money + dHappen_Credit_Money
                If sBegin_Direction = "借" Then
                    dEnd_Balance_Amount = dHappen_Debit_Amount - dHappen_Credit_Amount + dBegin_Balance_Amount
                    dEnd_Balance_Foreign = dHappen_Debit_Foreign - dHappen_Credit_Foreign + dBegin_Balance_Foreign
                    dEnd_Balance_Money = dHappen_Debit_Money - dHappen_Credit_Money + dBegin_Balance_Money
                    If dEnd_Balance_Money > 0 And Abs(dEnd_Balance_Money) > 0.0001 Then
                       sEnd_Direction = "借"
                    ElseIf Abs(dEnd_Balance_Money) < 0.0001 Then
                       sEnd_Direction = "平"
                    Else
                       sEnd_Direction = "贷"
                       dEnd_Balance_Amount = Abs(dEnd_Balance_Amount)
                       dEnd_Balance_Foreign = Abs(dEnd_Balance_Foreign)
                       dEnd_Balance_Money = Abs(dEnd_Balance_Money)
                    End If
                ElseIf sBegin_Direction = "贷" Then
                    dEnd_Balance_Amount = dHappen_Credit_Amount - dHappen_Debit_Amount + dBegin_Balance_Amount
                    dEnd_Balance_Foreign = dHappen_Credit_Foreign - dHappen_Debit_Foreign + dBegin_Balance_Foreign
                    dEnd_Balance_Money = dHappen_Credit_Money - dHappen_Debit_Money + dBegin_Balance_Money
                    If dEnd_Balance_Money < 0 And Abs(dEnd_Balance_Money) > 0.0001 Then
                       sEnd_Direction = "借"
                       dEnd_Balance_Amount = Abs(dEnd_Balance_Amount)
                       dEnd_Balance_Foreign = Abs(dEnd_Balance_Foreign)
                       dEnd_Balance_Money = Abs(dEnd_Balance_Money)
                    ElseIf Abs(dEnd_Balance_Money) < 0.0001 Then
                       sEnd_Direction = "平"
                    Else
                       sEnd_Direction = "贷"
                    End If
                Else
                    dEnd_Balance_Amount = dTotal_Debit_Amount - dTotal_Credit_Amount
                    dEnd_Balance_Foreign = dTotal_Debit_Foreign - dTotal_Credit_Foreign
                    dEnd_Balance_Money = dTotal_Debit_Money - dTotal_Credit_Money
                     '总合计
                    If dEnd_Balance_Money > 0 And Abs(dEnd_Balance_Money) > 0.0001 Then
                       sEnd_Direction = "借"
                    ElseIf Abs(dEnd_Balance_Money) < 0.0001 Then
                       sEnd_Direction = "平"
                    Else
                       sEnd_Direction = "贷"
                     dEnd_Balance_Amount = Abs(dEnd_Balance_Amount)
                     dEnd_Balance_Foreign = Abs(dEnd_Balance_Foreign)
                     dEnd_Balance_Money = Abs(dEnd_Balance_Money)
                    End If
                End If
                If GetKmJc(Trim(.Fields("kmdm").value)) = m_iSubjectLevelFrom - 1 Or bFristKmjc = False Then
                    If sEnd_Direction = "借" Then
                        dEnd_Debit_Money_Total = dEnd_Debit_Money_Total + dEnd_Balance_Money
                        dEnd_Debit_Moneys = dEnd_Debit_Moneys + dEnd_Balance_Money
                    Else
                        dEnd_Credit_Money_Total = dEnd_Credit_Money_Total + dEnd_Balance_Money
                        dEnd_Credit_Moneys = dEnd_Credit_Moneys + dEnd_Balance_Money
                    End If
                    bFristKmjc = (GetKmJc(Trim(.Fields("kmdm").value)) = m_iSubjectLevelFrom - 1)
                End If
                If m_