frmac_randommulticolresult.frm

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

        .SetCellString COL_DEBIT_MONEY, i, PageNo - 1, "( 打印日期：" & Format(Date, "yyyy-mm-dd") & _
                    "    时间：" & Format(Time, "hh:MM:ss") & " )                      "
                   
    'Print YK
        .SetRows .GetRows(PageNo - 1) + 1, PageNo - 1
        i = .GetRows(PageNo - 1) - 1
        .MergeCells COL_START, i, iCol_End, i
        .SetCellAlign COL_START, i, PageNo - 1, 32
        .SetCellFont COL_START, i, PageNo - 1, .FindFontIndex("宋体", 1)
        .SetCellFontSize COL_START, i, PageNo - 1, 10
        .SetCellFontStyle COL_START, i, PageNo - 1, 0
'        .SetCellString COL_START, i, PageNo - 1, "【伊康软件】"
    End With
    Call FormatCllR ' 隐藏外币数量cll
End Sub

Public Sub ShowResult()
    Dim sSQL As String
    Dim rstTemp As ADODB.Recordset
    Dim lCount As Long                          '数据行的行数(从数据行开始计数)
    Dim lPage As Long                           '当前所在页
    Dim sTemp As String                         '临时存放摘要
    Dim PreKjqj As Integer                      '当前凭证的上一张凭证的会计期间
    Dim CurKjqj As Integer                      '当前凭证会计期间
    
    Dim sBeginMonth As String                   '查询起始月份的上一个月
    
    Dim sBegin_Balance_Direction As String      '期初余额方向
    Dim dBegin_SlBalance As Double              '期初数量余额
    Dim dBegin_WbBalance As Double              '期初外币余额
    Dim dBegin_Balance As Double                '期初金额余额
    
    Dim dBegin_SlLjJf As Double                 '期初数量借方累计
    Dim dBegin_WbLjJf As Double                 '期初外币借方累计
    Dim dBegin_LjJf As Double                   '期初金额借方累计
        
    Dim dBegin_SlLjDf As Double                 '期初数量贷方累计
    Dim dBegin_WbLjDf As Double                 '期初外币贷方累计
    Dim dBegin_LjDf As Double                   '期初金额贷方累计
        
    Dim dBegin_SlBalance_Child() As Double      '期初子栏目数量余额数组
    Dim dBegin_WbBalance_Child() As Double      '期初子栏目外币余额数组
    Dim dBegin_Balance_Child() As Double        '期初子栏目金额余额数组
    
    Dim dBegin_SlLjJf_Child() As Double         '期初子栏目数量累计借方数组
    Dim dBegin_WbLjJf_Child() As Double         '期初子栏目外币累计借方数组
    Dim dBegin_LjJf_Child() As Double           '期初子栏目金额累计借方数组
    
    Dim dBegin_SlLjDf_Child() As Double         '期初子栏目数量累计贷方数组
    Dim dBegin_WbLjDf_Child() As Double         '期初子栏目外币累计贷方数组
    Dim dBegin_LjDf_Child() As Double           '期初子栏目金额累计贷方数组
    
    Dim sBalance_Direction_SubTotal As String   '本月合计方向
    Dim dDebit_SlSubTotal As Double             '借方数量小计
    Dim dDebit_WbSubTotal As Double             '借方外币小计
    Dim dDebit_SubTotal As Double               '借方金额小计
    Dim dCredit_SlSubTotal As Double            '贷方数量小计
    Dim dCredit_WbSubTotal As Double            '贷方外币小计
    Dim dCredit_SubTotal As Double              '贷方金额小计
    Dim dBalance_SlSubTotal As Double           '金额余额小计
    Dim dBalance_WbSubTotal As Double           '金额余额小计
    Dim dBalance_SubTotal As Double             '金额余额小计
    Dim dBalance_SlSubTotal_Child() As Double   '子栏目本月数量合计数组
    Dim dBalance_WbSubTotal_Child() As Double   '子栏目本月外币合计数组
    Dim dBalance_SubTotal_Child() As Double     '子栏目本月金额合计数组
    
    Dim sBalance_Direction_Total As String      '合计余额方向
    Dim dDebit_SlTotal As Double                '借方数量合计
    Dim dDebit_WbTotal As Double                '借方外币合计
    Dim dDebit_Total As Double                  '借方金额合计
    Dim dCredit_SlTotal As Double               '贷方数量合计
    Dim dCredit_WbTotal As Double               '贷方外币合计
    Dim dCredit_Total As Double                 '贷方金额合计
    Dim dBalance_SlTotal As Double              '数量余额小计
    Dim dBalance_WbTotal As Double              '外币余额小计
    Dim dBalance_Total As Double                '金额余额小计
    Dim dBalance_SlTotal_Child() As Double      '子栏目本年数量累计数组
    Dim dBalance_WbTotal_Child() As Double      '子栏目本年外币累计数组
    Dim dBalance_Total_Child() As Double        '子栏目本年金额累计数组

    Dim dChild_SlData As Double                 '子栏目记录行的数量数据
    Dim dChild_WbData As Double                 '子栏目记录行的外币数据
    Dim dChild_Data As Double                   '子栏目记录行的金额数据

    Dim sCurrent_Balance_Direction As String    '当前记录行的余额方向
    Dim dCurrent_SlBalance As Double            '当前记录行的数量余额
    Dim dCurrent_WbBalance As Double            '当前记录行的外币余额
    Dim dCurrent_Balance As Double              '当前记录行的金额余额

    Dim iActionCols As Integer
    Dim iLocate As Integer
    Dim i As Integer
    Dim frm As frmProgress2
    Me.Visible = False
    Set frm = New frmProgress2
    frm.SetMsg "正在分析中请稍候..."
    frm.ShowProgress 0
    frm.Show , fMainForm
    DoEvents
    '得到当前账套的单位名称
    m_sEnterName = GetDWMC
    If m_sEnterName = "" Then Exit Sub
    fMainForm.MousePointer = vbHourglass
    iActionCols = (UBound(m_arrColInformation) + 1) * 3
    iCol_End = COL_ACTION_BEGIN - 1 + iActionCols
    ReDim dBegin_SlBalance_Child(0 To iActionCols)
    ReDim dBegin_WbBalance_Child(0 To iActionCols)
    ReDim dBegin_Balance_Child(0 To iActionCols)                    '期初子栏目余额数组
    ReDim dBalance_SlSubTotal_Child(0 To iActionCols)
    ReDim dBalance_WbSubTotal_Child(0 To iActionCols)
    ReDim dBalance_SubTotal_Child(0 To iActionCols)                 '子栏目本月合计数组
    ReDim dBalance_SlTotal_Child(0 To iActionCols)
    ReDim dBalance_WbTotal_Child(0 To iActionCols)
    ReDim dBalance_Total_Child(0 To iActionCols)                    '子栏目本年累计数组
    
    ReDim dBegin_SlLjJf_Child(0 To iActionCols)
    ReDim dBegin_WbLjJf_Child(0 To iActionCols)
    ReDim dBegin_LjJf_Child(0 To iActionCols)                    '子栏目本年累计数组
    ReDim dBegin_SlLjDf_Child(0 To iActionCols)
    ReDim dBegin_WbLjDf_Child(0 To iActionCols)
    ReDim dBegin_LjDf_Child(0 To iActionCols)                    '子栏目本年累计数组
    '将CELL不可见，防止清除CELL控件内容时屏幕闪烁；
    Cllr.Visible = False
    Cllr.ResetContent
    '清除CELL控件内容后，必须重新设置CELL的行数和列数；
    Cllr.SetCols iCol_End + 2, 0
    Cllr.SetRows ROW_GRID_START + ROWS_PAGE, 0
     CurRow = ROW_GRID_START - 1
    lCount = 0: lPage = 0
    Set rstTemp = New ADODB.Recordset: rstTemp.CursorLocation = adUseClient
    '如果查询开始月份为一月份, 则计算上年结转余额;
    '否则计算上月余额;
    sBeginMonth = Format(Val(m_sMonthFrom) - 1, "00")
    '得到查询语句字符串
    sSQL = GetQueryStr(m_sMonthFrom, m_sMonthTo)
    '-------------Begin---------------
    '求出期初余额
    Call GetBeginBalance(sBeginMonth, dBegin_SlBalance, dBegin_WbBalance, dBegin_Balance, dBegin_SlBalance_Child, dBegin_WbBalance_Child, dBegin_Balance_Child, _
                        dBegin_SlLjJf, dBegin_WbLjJf, dBegin_LjJf, dBegin_SlLjJf_Child, dBegin_WbLjJf_Child, dBegin_LjJf_Child, _
                        dBegin_SlLjDf, dBegin_WbLjDf, dBegin_LjDf, dBegin_SlLjDf_Child, dBegin_WbLjDf_Child, dBegin_LjDf_Child)
    '存入小计数量、外币、金额余额
    dBalance_SlSubTotal = dBegin_SlBalance: dBalance_WbSubTotal = dBegin_WbBalance: dBalance_SubTotal = dBegin_Balance
    dBalance_SlSubTotal = 0: dBalance_WbSubTotal = 0: dBalance_SubTotal = 0
    '存入合计数量、外币、金额余额
    dBalance_SlTotal = dBegin_SlBalance: dBalance_WbTotal = dBegin_WbBalance: dBalance_Total = dBegin_Balance
    '存入子科目本月合计、本年累计
    'dBalance_SlSubTotal_Child = dBegin_SlBalance_Child: dBalance_WbSubTotal_Child = dBegin_WbBalance_Child: dBalance_SubTotal_Child = dBegin_Balance_Child
    dBalance_SlTotal_Child = dBegin_SlBalance_Child: dBalance_WbTotal_Child = dBegin_WbBalance_Child: dBalance_Total_Child = dBegin_Balance_Child
    For i = LBound(m_arrColInformation) To UBound(m_arrColInformation)
        
        If m_arrColInformation(i).sColDirect = "借方" Then
            dBalance_SlTotal_Child(i * 3) = dBalance_SlTotal_Child(i * 3) - dBegin_SlLjJf_Child(i * 3) + dBegin_SlLjDf_Child(i * 3)
            dBalance_WbTotal_Child(i * 3 + 1) = dBalance_WbTotal_Child(i * 3 + 1) - dBegin_WbLjJf_Child(i * 3 + 1) + dBegin_WbLjDf_Child(i * 3 + 1)
            dBalance_Total_Child(i * 3 + 2) = dBalance_Total_Child(i * 3 + 2) - dBegin_LjJf_Child(i * 3 + 2) + dBegin_LjDf_Child(i * 3 + 2)
        ElseIf m_arrColInformation(i).sColDirect = "贷方" Then
            dBalance_SlTotal_Child(i * 3) = dBalance_SlTotal_Child(i * 3) + dBegin_SlLjJf_Child(i * 3) - dBegin_SlLjDf_Child(i * 3)
            dBalance_WbTotal_Child(i * 3 + 1) = dBalance_WbTotal_Child(i * 3 + 1) + dBegin_WbLjJf_Child(i * 3 + 1) - dBegin_WbLjDf_Child(i * 3 + 1)
            dBalance_Total_Child(i * 3 + 2) = dBalance_Total_Child(i * 3 + 2) + dBegin_LjJf_Child(i * 3 + 2) - dBegin_LjDf_Child(i * 3 + 2)
        End If
    Next
    
    
    '根据期初余额决定余额方向
    If dBegin_Balance > 0 Then
        sBegin_Balance_Direction = "借"
    ElseIf dBegin_Balance < 0 Then
        sBegin_Balance_Direction = "贷"
        dBegin_Balance = Abs(dBegin_Balance)
    Else
        sBegin_Balance_Direction = "平"
    End If
    If Format(m_sMonthFrom, "00") = "01" Then
        sTemp = "[  上  年  结  转  ]"
    Else
        sTemp = "[  期  初  余  额  ]"
    End If
    '追加期初余额行到表格
    If (dBegin_SlBalance <> 0 And m_bAmount) Or (dBegin_WbBalance <> 0 And m_bForeign) Or dBegin_Balance <> 0 Then
        frm.SetMsg "正在追加起初余额..."
        frm.ShowProgress 5
        CurRow = CurRow + 1
        lCount = lCount + 1
        Call AppendTotalRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                        Val(m_sMonthFrom), 1, _
                        "", "", sTemp, 0, 0, 0, 0, 0, 0, _
                        sBegin_Balance_Direction, _
                        dBegin_SlBalance, dBegin_WbBalance, dBegin_Balance, _
                        dBegin_SlBalance_Child, dBegin_WbBalance_Child, dBegin_Balance_Child)
    End If
    
    
    '---------------End------------
    Dim iCurrRecordRow As Integer
    iCurrRecordRow = 0
    With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If .RecordCount > 0 Then
            .MoveFirst
            '将变量PreKjqj,CurKjqj赋值
            PreKjqj = .Fields("kjqj").value
            CurKjqj = .Fields("kjqj").value
            ReDim Preserve m_sPzKey(0)
            Do Until .EOF
                iCurrRecordRow = iCurrRecordRow + 1
                frm.SetMsg "正在处理" & glo.sOperateYear & "年" & Trim(CurKjqj) & "月的数据..."
                frm.ShowProgress 5 + (iCurrRecordRow / .RecordCount) * 100
                '求出当前记录所在子栏目中的位置
                iLocate = GetLocate(Trim(.Fields(m_sAccountField).value))
                If iLocate <> -1 Then
                        m_sPzKey(UBound(m_sPzKey)) = FormatStr(Trim(.Fields("pzbh")), 4, " ") & FormatStr(Trim(.Fields("pzzl")), 4, " ") & Format(.Fields("pzrq"), "yyyy-mm-dd")
                        ReDim Preserve m_sPzKey(UBound(m_sPzKey) + 1)
                        CurRow = CurRow + 1
                        lCount = lCount + 1
                        '如果记录数等于每页可显示的行数，则换页；
                        If lCount Mod ROWS_PAGE = 0 Then
                            Call SetNewPage(lPage, lCount, _
                                          dDebit_SlTotal, dDebit_WbTotal, dDebit_Total, _
                                          dCredit_SlTotal, dCredit_WbTotal, dCredit_Total, _
                                          sCurrent_Balance_Direction, _
                                          dBalance_SlTotal, dBalance_WbTotal, dBalance_Total, _
                                          dBalance_SlTotal_Child, dBalance_WbTotal_Child, dBalance_Total_Child)
                        End If
                        CurKjqj = .Fields("kjqj").value
                        '与前一张凭证不在同一个月
                        If CurKjqj <> PreKjqj Then
                            '添加本月合计、本年累计行
                            '--------------Begin------------
                            '根据本月合计决定本月合计的方向
                            If dBalance_SubTotal > 0 Then
                                sBalance_Direction_SubTotal = "借"
                            ElseIf dBalance_SubTotal < 0 Then
                                sBalance_Direction_SubTotal = "贷"
                                dBalance_SlSubTotal = Abs(dBalance_SlSubTotal): dBalance_WbSubTotal = Abs(dBalance_WbSubTotal): dBalance_SubTotal = Abs(dBalance_SubTotal)
                            Else
                                sBalance_Direction_SubTotal = "平"
                            End If
                            '追加本月合计
                            Call AppendTotalRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, PreKjqj, 0, _
                                    "", "", "[  本  月  合  计  ]", _
                                    dDebit_SlSubTotal, dDebit_WbSubTotal, dDebit_SubTotal, _
                                    dCredit_SlSubTotal, dCredit_WbSubTotal, dCredit_SubTotal, _
                                    sBalance_Direction_SubTotal, _
                                    dBalance_SlSubTotal, dBalance_WbSubTotal, dBalance_SubTotal, _
                                    dBalance_SlSubTotal_Child, dBalance_WbSubTotal_Child, dBalance_SubTotal_Child)
                            '清空本月合计借方、贷方、余额变量