frmac_indexresult.frm

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

        .SetCellFontSize COL_START, i, PageNo - 1, 10
        .SetCellFontStyle COL_START, i, PageNo - 1, 0
        .SetCellFont COL_HAPPEN_DEBIT_MONEY, i, PageNo - 1, .FindFontIndex("宋体", 1)
        .SetCellFontSize COL_HAPPEN_DEBIT_MONEY, i, PageNo - 1, 10
        .SetCellFontStyle COL_HAPPEN_DEBIT_MONEY, i, PageNo - 1, 0
        .SetCellString COL_START, i, PageNo - 1, "核算单位：" & m_sEnterName
        .SetCellString COL_HAPPEN_DEBIT_MONEY, i, PageNo - 1, "( 打印日期：" & Format(Date, "yyyy-mm-dd") & _
                    "    时间：" & Format(Time, "hh:MM:ss") & " )"
        DoRedrawCellHead COLWIDTH_MONEY
        .ShowPageBreak False
        .SetFixedRow ROW_TITLE, ROW_HEAD2
    End With

End Sub

Public Sub ShowResult()
    Dim pzzl() As String
    Dim QueryStr As String          '存放查询条件
    Dim OrderStr As String          '存放排序条件
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim d As Double
    Dim lCount As Long
    Dim lPage As Long
    
    Dim bFristKmjc As Boolean
    
    Dim bTemp As Boolean
    Dim BeginMonth As String
    Dim EndMonth As String
    
    
    Dim dBegin_Debit_Amount As Double          '期初数量余额借方
    Dim dBegin_Debit_Foreign As Double         '期初外币余额借方
    Dim dBegin_Debit_Money As Double           '期初金额余额借方
    Dim dBegin_Credit_Amount As Double         '期初数量余额贷方
    Dim dBegin_Credit_Foreign As Double        '期初外币余额贷方
    Dim dBegin_Credit_Money As Double          '期初金额余额贷方
    
    Dim dHappen_Debit_Amount() As Double          '本期借方数量
    Dim dHappen_Debit_Foreign() As Double         '本期借方外币
    Dim dHappen_Debit_Money() As Double           '本期借方金额
    Dim dHappen_Credit_Amount() As Double         '本期贷方数量
    Dim dHappen_Credit_Foreign() As Double        '本期贷方外币
    Dim dHappen_Credit_Money() As Double          '本期贷方金额
        
    Dim dEnd_Debit_Amount As Double            '期末数量余额借方
    Dim dEnd_Debit_Foreign As Double           '期末外币余额借方
    Dim dEnd_Debit_Money As Double             '期末金额余额借方
    Dim dEnd_Credit_Amount As Double           '期末数量余额贷方
    Dim dEnd_Credit_Foreign As Double          '期末外币余额贷方
    Dim dEnd_Credit_Money As Double            '期末金额余额贷方
    
    
    Dim dBegin_Debit_Amount_Total As Double          '期初数量余额借方
    Dim dBegin_Debit_Foreign_Total As Double         '期初外币余额借方
    Dim dBegin_Debit_Money_Total As Double           '期初金额余额借方
    Dim dBegin_Credit_Amount_Total As Double         '期初数量余额贷方
    Dim dBegin_Credit_Foreign_Total As Double        '期初外币余额贷方
    Dim dBegin_Credit_Money_Total As Double          '期初金额余额贷方
    
    Dim dHappen_Debit_Amount_Total() As Double          '本期借方数量
    Dim dHappen_Debit_Foreign_Total() As Double         '本期借方外币
    Dim dHappen_Debit_Money_Total() As Double           '本期借方金额
    Dim dHappen_Credit_Amount_Total() As Double         '本期贷方数量
    Dim dHappen_Credit_Foreign_Total() As Double        '本期贷方外币
    Dim dHappen_Credit_Money_Total() As Double          '本期贷方金额
        
    Dim dEnd_Debit_Amount_Total As Double            '期末数量余额借方
    Dim dEnd_Debit_Foreign_Total As Double           '期末外币余额借方
    Dim dEnd_Debit_Money_Total As Double             '期末金额余额借方
    Dim dEnd_Credit_Amount_Total As Double           '期末数量余额贷方
    Dim dEnd_Credit_Foreign_Total As Double          '期末外币余额贷方
    Dim dEnd_Credit_Money_Total As Double            '期末金额余额贷方
    
    
    '未记账凭证的数量（外币、金额）的借方金额和、贷方金额和及余额和
    Dim NotRecord_Debit_Amount() As Double, NotRecord_Debit_Foreign() As Double, NotRecord_Debit_Money() As Double
    Dim NotRecord_Credit_Amount() As Double, NotRecord_Credit_Foreign() As Double, NotRecord_Credit_Money() As Double
    'Dim NotRecord_Balance_Amount(1 To mColMonth) As Double, NotRecord_Balance_Foreign(1 To mColMonth) As Double, NotRecord_Balance_Money(1 To mColMonth) As Double
    
    Dim bFound As Boolean                    '是否存在未记账凭证
    Dim sSQL As String
    
    Dim iMaxColWidth As Integer
    
    ReDim dHappen_Debit_Amount(1 To mColMonth)
    ReDim dHappen_Debit_Foreign(1 To mColMonth)
    ReDim dHappen_Debit_Money(1 To mColMonth)
    ReDim dHappen_Credit_Amount(1 To mColMonth)
    ReDim dHappen_Credit_Foreign(1 To mColMonth)
    ReDim dHappen_Credit_Money(1 To mColMonth)
    
    ReDim dHappen_Debit_Amount_Total(1 To mColMonth)
    ReDim dHappen_Debit_Foreign_Total(1 To mColMonth)
    ReDim dHappen_Debit_Money_Total(1 To mColMonth)
    ReDim dHappen_Credit_Amount_Total(1 To mColMonth)
    ReDim dHappen_Credit_Foreign_Total(1 To mColMonth)
    ReDim dHappen_Credit_Money_Total(1 To mColMonth)
    ReDim NotRecord_Debit_Amount(1 To mColMonth), NotRecord_Debit_Foreign(1 To mColMonth), NotRecord_Debit_Money(1 To mColMonth)
    ReDim NotRecord_Credit_Amount(1 To mColMonth), NotRecord_Credit_Foreign(1 To mColMonth), NotRecord_Credit_Money(1 To mColMonth)
    
    Cllr.SetCols COL_END + (mColMonth * 6) + 2, lPage
    '得到当前账套的单位名称
    
    m_sEnterName = GetDWMC
    If m_sEnterName = "" Then
        Exit Sub
    End If
    
    fMainForm.MousePointer = vbHourglass
    
    BeginMonth = Format(Val(m_sMonthFrom) - 1, "00")
    EndMonth = Format(m_sMonthTo, "00")
    
    sSQL = "SELECT a.kmdm kmdm,a.kmmc kmmc,a.yefx yefx,b.kmlx kmlx,b.isendkm isEndkm ,a.ljjsl" + BeginMonth + _
    " Begin_Debit_Amount,a.ljjwb" + BeginMonth + " Begin_Debit_Foreign,a.ljj" + BeginMonth + " Begin_Debit_Money,a.ljdsl" + _
    BeginMonth + " Begin_Credit_Amount,a.ljdwb" + BeginMonth + " Begin_Credit_Foreign,a.ljd" + BeginMonth + " Begin_Credit_Money"
    For j = CInt(m_sMonthFrom) To CInt(m_sMonthTo)
        sSQL = sSQL + ",ljjsl" + Format(j, "00") + "-ljjsl" + Format(j - 1, "00") + " Happen_Debit_Amount" + CStr(j) + _
                ",ljjwb" + Format(j, "00") + "-ljjwb" + Format(j - 1, "00") + " Happen_Debit_Foreign" + CStr(j) + _
                ",ljj" + Format(j, "00") + "-ljj" + Format(j - 1, "00") + " Happen_Debit_Money" + CStr(j) + _
                ",ljdsl" + Format(j, "00") + "-ljdsl" + Format(j - 1, "00") + " Happen_Credit_Amount" + CStr(j) + _
                ",ljdwb" + Format(j, "00") + "-ljdwb" + Format(j - 1, "00") + " Happen_Credit_Foreign" + CStr(j) + _
                ",ljd" + Format(j, "00") + "-ljd" + Format(j - 1, "00") + " Happen_Credit_Money" + CStr(j)
    Next
    sSQL = sSQL + ",a.ljjsl" + EndMonth + _
    " End_Debit_Amount,a.ljjwb" + EndMonth + " End_Debit_Foreign,a.ljj" + EndMonth + " End_Debit_Money,a.ljdsl" + _
    EndMonth + " End_Credit_Amount,a.ljdwb" + EndMonth + " End_Credit_Foreign,a.ljd" + EndMonth + " End_Credit_Money"
sSQL = sSQL + " FROM tZW_balance" & glo.sOperateYear & " a , tzw_km" & glo.sOperateYear & " b WHERE a.kmdm=b.kmdm  and "
    'brb add ********
    If m_sSubjectCodeFrom <> "" Then
        QueryStr = " a.kmdm >= '" & m_sSubjectCodeFrom & "' AND "
    End If
    If m_sSubjectCodeTo <> "" Then
    If IsEndSubject(m_sSubjectCodeTo) Then
    QueryStr = QueryStr & " (a.kmdm <= '" & m_sSubjectCodeTo & "' or a.kmdm = '" & Trim(m_sSubjectCodeTo) & "' ) AND "
   
    Else
        QueryStr = QueryStr & " (a.kmdm <= '" & m_sSubjectCodeTo & "' or a.kmdm like '" & Trim(m_sSubjectCodeTo) & "-%' ) AND "
    End If
    End If
   'end  ********
    
    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"
    End Select
    sSQL = sSQL & OrderStr
    
    '求出未记账凭证的科目代码、方向、数量（外币、金额）借方金额和、贷方金额和及余额和
    If m_bIncludeNotRecordVoucher Then
        Call GetNotRecordData(bFound)
    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
                dBegin_Debit_Amount = 0
                dBegin_Debit_Foreign = 0
                dBegin_Debit_Money = 0
                dBegin_Credit_Amount = 0
                dBegin_Credit_Foreign = 0
                dBegin_Credit_Money = 0
                For j = 1 To mColMonth
                    NotRecord_Debit_Amount(j) = 0
                    NotRecord_Debit_Foreign(j) = 0
                    NotRecord_Debit_Money(j) = 0
                    NotRecord_Credit_Amount(j) = 0
                    NotRecord_Credit_Foreign(j) = 0
                    NotRecord_Credit_Money(j) = 0
                Next

                '求出各个科目的数量和、外币和、金额和
                If bFound Then
                    For i = LBound(NotRecordData) To UBound(NotRecordData)
                        If NotRecordData(i).sKmdm Like Trim(.Fields("kmdm").value) & "*" Then
                            j = NotRecordData(i).iKjqj - BeginMonth
                            If j > 0 Then
                                If NotRecordData(i).sFx = "借" Then
                                    NotRecord_Debit_Amount(j) = NotRecord_Debit_Amount(j) + NotRecordData(i).dSumAmount
                                    NotRecord_Debit_Foreign(j) = NotRecord_Debit_Foreign(j) + NotRecordData(i).dSumForeign
                                    NotRecord_Debit_Money(j) = NotRecord_Debit_Money(j) + NotRecordData(i).dSumMoney
                                Else
                                    NotRecord_Credit_Amount(j) = NotRecord_Credit_Amount(j) + NotRecordData(i).dSumAmount
                                    NotRecord_Credit_Foreign(j) = NotRecord_Credit_Foreign(j) + NotRecordData(i).dSumForeign
                                    NotRecord_Credit_Money(j) = NotRecord_Credit_Money(j) + NotRecordData(i).dSumMoney
                                End If
                            Else
                                If NotRecordData(i).sFx = "借" Then
                                    dBegin_Debit_Amount = dBegin_Debit_Amount + NotRecordData(i).dSumAmount
                                    dBegin_Debit_Foreign = dBegin_Debit_Foreign + NotRecordData(i).dSumForeign
                                    dBegin_Debit_Money = dBegin_Debit_Money + NotRecordData(i).dSumMoney
                                Else
                                    dBegin_Credit_Amount = dBegin_Credit_Amount + NotRecordData(i).dSumAmount
                                    dBegin_Credit_Foreign = dBegin_Credit_Foreign + NotRecordData(i).dSumForeign
                                    dBegin_Credit_Money = dBegin_Credit_Money + NotRecordData(i).dSumMoney
                                End If
                            End If
                        End If
                    Next i
                End If
                bTemp = .Fields("isEndkm").value
                        
                '求出该科目所有凭证的数量和、外币和、金额和
                
                '期初
                If bTemp Then
                    dBegin_Debit_Amount = dBegin_Debit_Amount + FormatToDouble(.Fields("Begin_Debit_Amount").value)
                    dBegin_Debit_Foreign = dBegin_Debit_Foreign + FormatToDouble(.Fields("Begin_Debit_Foreign").value)
                    dBegin_Debit_Money = dBegin_Debit_Money + FormatToDouble(.Fields("Begin_Debit_Money").value)
                    
                    dBegin_Credit_Amount = dBegin_Credit_Amount + FormatToDouble(.Fields("Begin_Credit_Amount").value)
                    dBegin_Credit_Foreign = dBegin_Credit_Foreign + FormatToDouble(.Fields("Begin_Credit_Foreign").value)
                    dBegin_Credit_Money = dBegin_Credit_Money + FormatToDouble(.Fields("Begin_Credit_Money").value)
                Else
                    dBegin_Debit_Amount = 0
                    dBegin_Debit_Foreign = 0
                    dBegin_Debit_Money = dBegin_Debit_Money + FormatToDouble(.Fields("Begin_Debit_Money").value)
                    
                    dBegin_Credit_Amount = 0
                    dBegin_Credit_Foreign = 0
                    dBegin_Credit_Money = dBegin_Credit_Money + FormatToDouble(.Fields("Begin_Credit_Money").value)
                End If
                '求余额
                If Abs(dBegin_Debit_Money - dBegin_Credit_Money) < 0.0001 Then
                    dBegin_Debit_Money = 0