frmac_detailmodeprint.frm

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

    End If

    '查询求年初数(00)、上月数(PreStartOfMonth)
    'JFSL00     JFWB00      JF00        DFSL00      DFWB00      DF00
    'JFSL01     JFWB01      JF01        DFSL01      DFWB01      DF01
    sSQL = "SELECT ljjsl00 JFSL00,ljjwb00 JFWB00,ljj00 JF00," & _
            "ljdsl00 DFSL00,ljdwb00 DFWB00,ljd00 DF00," & _
            "ljjsl" & PreStartOfMonth & " JFSL01,ljjwb" & PreStartOfMonth & _
            " JFWB01,ljj" & PreStartOfMonth & " JF01," & _
            "ljdsl" & PreStartOfMonth & " DFSL01,ljdwb" & PreStartOfMonth & _
            " DFWB01,ljd" & PreStartOfMonth & " DF01" & _
            " FROM tZW_balance" & m_sYear & _
            " WHERE kmdm='" & m_sCurSubjectCode & "'"
        
    ''计算本年累计
     With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        
      '设置年初数
        dYBJJe = .Fields("JF00").value
        dYBJSL = .Fields("JFSL00").value
        dYBJWB = .Fields("JFWB00").value
        dYBDJE = .Fields("DF00").value
        dYBDSL = .Fields("DFSL00").value
        dYBDWB = .Fields("DFWB00").value
        
       
        bnljJ = .Fields("JF01").value
        bnljD = .Fields("DF01").value
        bnljJSL = .Fields("JFSL01").value
        bnljDSL = .Fields("DFSL01").value
        bnljJWB = .Fields("JFWB01").value
        bnljDWB = .Fields("DFWB01").value
       .Close
     End With
     
    '设置起初月份
    '查询余额, 包括未复核的凭证
                
    '加上借方
    If IsEndSubject(m_sCurSubjectCode) Then
    sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                " FROM tZW_pzsj" & m_sYear & _
                " WHERE kjqj>0 and kjqj < " & Val(m_sFromMonth) & _
                " AND (xgbz=0 OR xgbz=1 ) " & _
                " AND kmdm = '" & m_sCurSubjectCode & "'" & _
                " AND fx='借'"
    Else
      sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                " FROM tZW_pzsj" & m_sYear & _
                " WHERE kjqj>0 and kjqj < " & Val(m_sFromMonth) & _
                " AND (xgbz=0 OR xgbz=1 ) " & _
                " AND kmdm like '" & m_sCurSubjectCode & "-%'" & _
                " AND fx='借'"
    End If
    With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If Not (.BOF And .EOF) Then
            bnljJ = bnljJ + IIf(IsNull(.Fields("sumJE").value), 0, .Fields("sumJE").value)
            bnljJSL = bnljJSL + IIf(IsNull(.Fields("sumSL").value), 0, .Fields("sumSL").value)
            bnljJWB = bnljJWB + IIf(IsNull(.Fields("sumWB").value), 0, .Fields("sumWB").value)
        End If
        .Close
    End With
    
    '减去贷方
    If IsEndSubject(m_sCurSubjectCode) Then
    sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
               " FROM tZW_pzsj" & m_sYear & _
               " WHERE kjqj>0 and kjqj < " & Val(m_sFromMonth) & _
               " AND ( xgbz=0 OR xgbz=1 ) " & _
               " AND kmdm = '" & m_sCurSubjectCode & "'" & _
               " AND fx='贷'"
    Else
     sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
               " FROM tZW_pzsj" & m_sYear & _
               " WHERE kjqj>0 and kjqj < " & Val(m_sFromMonth) & _
               " AND ( xgbz=0 OR xgbz=1 ) " & _
               " AND kmdm like '" & m_sCurSubjectCode & "-%'" & _
               " AND fx='贷'"
    End If
    With rstTemp
    .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If Not (.BOF And .EOF) Then
            bnljD = bnljD + IIf(IsNull(.Fields("sumJE").value), 0, .Fields("sumJE").value)
            bnljDSL = bnljDSL + IIf(IsNull(.Fields("sumSL").value), 0, .Fields("sumSL").value)
            bnljDWB = bnljDWB + IIf(IsNull(.Fields("sumWB").value), 0, .Fields("sumWB").value)
        End If
        .Close
    End With
    
    If bnljJ - bnljD > 0 Then
        sFX = "借"
    ElseIf bnljJ - bnljD = 0 Then
        sFX = "平"
    Else
        sFX = "贷"
    End If
  
  '本年累计数量余额
   dbnYESL = bnljJSL - bnljDSL
   dbnYEWB = bnljJWB - bnljDWB
   dbnYE = bnljJ - bnljD
  
  '求本年的累计发生
    bnljJ = bnljJ - dYBJJe
    bnljJSL = bnljJSL - dYBJSL
    bnljJWB = bnljJWB - dYBJWB
    bnljD = bnljD - dYBDJE
    bnljDSL = bnljDSL - dYBDSL
    bnljDWB = bnljDWB - dYBDWB
    
    PushVar dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB

'-------------------------------------------------
'从凭证表中取出记录进行处理
'取出的字段：会计期间、凭证种类、编号、日期、摘要、方向、金额、外币、汇率、制单人名
'2002.10.19 add iif
 If IsEndSubject(m_sCurSubjectCode) Then
 sSQL = "SELECT kjqj,pzzl,pzbh,pzrq,pzzy,fx,je,sl,dj,wb,hl,zdrm,xgbz,yhdz_bill" & _
            " FROM tZW_pzsj" & m_sYear & _
            " WHERE kjqj >= " & Val(m_sFromMonth) & _
            " AND kjqj <= " & Val(m_sToMonth) & _
            " AND kmdm = '" & m_sCurSubjectCode & "'" & _
            "" & _
            " ORDER BY kjqj,pzrq,pzzl,pzbh"
 Else
    sSQL = "SELECT kjqj,pzzl,pzbh,pzrq,pzzy,fx,je,sl,dj,wb,hl,zdrm,xgbz,yhdz_bill" & _
            " FROM tZW_pzsj" & m_sYear & _
            " WHERE kjqj >= " & Val(m_sFromMonth) & _
            " AND kjqj <= " & Val(m_sToMonth) & _
            " AND kmdm LIKE '" & m_sCurSubjectCode & "-%'" & _
            "" & _
            " ORDER BY kjqj,pzrq,pzzl,pzbh"
  End If
    With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If .RecordCount > 0 Then
            Call InitVariant
            .MoveFirst
            PreKjqj = .Fields("kjqj").value
            CurKjqj = .Fields("kjqj").value
            dbnYESL = 0
            dbnYEWB = 0
            dbnYE = 0
            If PreKjqj > 0 Then
                PopVar dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB
                AddYearBeginRow dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB, CurRow, lCount
            End If
            Do Until .EOF

                CurKjqj = .Fields("kjqj").value
                '与前一张凭证不在同一个月
                If CurKjqj <> PreKjqj Then
                    If PreKjqj = 0 Then
                        PopVar dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB
                        AddYearBeginRow dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB, CurRow, lCount
                    Else
                        '添加本月合计、本年累计行
                        Call AppendTotalRowsbak(PreKjqj, iPageStart)
                        If (lCount Mod ROWS_PAGE) = 0 And Not rstTemp.EOF Then
        '                            Call AppendChangePageRow(8 + 1)
                             Call AppendChangePageRow(8 + 1, iPageStart)
                        End If
                        CurRow = CurRow - 1
                        lCount = lCount - 1
                    End If
                End If

                
                '添加当前凭证记录行
                CurRow = CurRow + 1
                lCount = lCount + 1
                If (lCount Mod ROWS_PAGE) = 0 Then Call AppendChangePageRow(1, iPageStart)
                
                '合计本月、过次
                If .Fields("FX").value = "借" Then
                    dbnYESL = dbnYESL + .Fields("sl").value
                    dbnYEWB = dbnYEWB + .Fields("wb").value
                    dbnYE = dbnYE + .Fields("je").value
                    
                    bnljJ = bnljJ + .Fields("je").value
                    bnljJSL = bnljJSL + .Fields("sl").value
                    bnljJWB = bnljJWB + .Fields("wb").value
                    
                    gcJSL = gcJSL + .Fields("sl").value
                    gcJWB = gcJWB + .Fields("wb").value
                    gcJ = gcJ + .Fields("je").value
                    
                    byhjJSL = byhjJSL + .Fields("sl").value
                    byhjJWB = byhjJWB + .Fields("wb").value
                    byhjJ = byhjJ + .Fields("je").value
                Else
                    dbnYESL = dbnYESL - .Fields("sl").value
                    dbnYEWB = dbnYEWB - .Fields("wb").value
                    dbnYE = dbnYE - .Fields("je").value
                    
                    bnljD = bnljD + .Fields("je").value
                    bnljDSL = bnljDSL + .Fields("sl").value
                    bnljDWB = bnljDWB + .Fields("wb").value
                    
                    gcDSL = gcDSL + .Fields("sl").value
                    gcDWB = gcDWB + .Fields("wb").value
                    gcD = gcD + .Fields("je").value
                    
                    byhjDSL = byhjDSL + .Fields("sl").value
                    byhjDWB = byhjDWB + .Fields("wb").value
                    byhjD = byhjD + .Fields("je").value
                    
                End If
                '判断方向
                If dbnYE > 0 Then
                    sFX = "借"
                ElseIf dbnYE = 0 Then
                    sFX = "平"
                Else
                    sFX = "贷"
                End If
                
                If .Fields("FX").value = "借" Then
                    Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                        Month(.Fields("pzrq").value), Day(.Fields("pzrq").value), .Fields("pzzl").value, _
                        .Fields("pzbh").value, FormatToString(.Fields("yhdz_bill").value), Trim$("" & .Fields("pzzy").value), _
                        .Fields("dj").value, .Fields("hl").value, sFX, IIf(sFX = "借", dbnYESL, -dbnYESL), _
                        IIf(sFX = "借", dbnYEWB, -dbnYEWB), IIf(sFX = "借", dbnYE, -dbnYE), _
                        Trim$("" & .Fields("zdrm").value), dDebit_Amount:=.Fields("sl").value, _
                        dDebit_Foreign:=.Fields("wb").value, dDebit_Money:=.Fields("je").value)
                Else
                    Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                        Month(.Fields("pzrq").value), Day(.Fields("pzrq").value), .Fields("pzzl").value, _
                        .Fields("pzbh").value, FormatToString(.Fields("yhdz_bill").value), Trim$("" & .Fields("pzzy").value), _
                        .Fields("dj").value, .Fields("hl").value, sFX, IIf(sFX = "借", dbnYESL, -dbnYESL), _
                        IIf(sFX = "借", dbnYEWB, -dbnYEWB), IIf(sFX = "借", dbnYE, -dbnYE), _
                        Trim$("" & .Fields("zdrm").value), dCredit_Amount:=.Fields("sl").value, _
                        dCredit_Foreign:=.Fields("wb").value, dCredit_Money:=.Fields("je").value)
                End If
                PreKjqj = CurKjqj
               
                If CInt(.Fields("xgbz").value) <> 2 Then
                   bNotRecord = True
                Else
                   bNotRecord = False
                End If
            
                If bNotRecord Then
                    n = ROW_GRID_START + lCount - 1
                    For m = 0 To Cllr.GetCols(Cllr.GetCurSheet) - 1
''                        CllR.DoSetCellColor m, N, lForeColour, lBackColour
                        Cllr.SetCellTextColor m, n, Cllr.GetCurSheet, Cllr.FindColorIndex(lForeColour, 1)
                        Cllr.SetCellBackColor m, n, Cllr.GetCurSheet, Cllr.FindColorIndex(lBackColour, 1)
                    Next m
                End If
                
                .MoveNext
            Loop
            .Close
            If PreKjqj = 0 Then
                PopVar dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB
                AddYearBeginRow dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB, CurRow, lCount
            Else
                Call AppendTotalRowsbak(PreKjqj, iPageStart)
                lCount = lCount - 1
                CurRow = CurRow - 1
            End If
        Else
            PopVar dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB
            AddYearBeginRow dbnYE, dbnYESL, dbnYEWB, bnljJ, bnljJSL, bnljJWB, bnljD, bnljDSL, bnljDWB, CurRow, lCount
        End If
    End With
'-----------------------------------------------
    Call SetGrid(lPage + 1, ROW_GRID_START + ROWS_PAGE + 1, iPageStart)
    Cllr.SetCurSheet 0
    Cllr.Visible = True
    For i = 1 To Cllr.GetTotalSheets
       Cllr.SetSheetLabel i - 1, "第" & i + iPageStart & "页"
    Next i
    '为了防止装填账页格式时触发cboaccountformat_click事件时，出现系统提示账页格式已改变；
    '所以将此程序在此调用
    Call SetAccountFormat(cboAccountFormat, m_bAmount, m_bForeign)
    fMainForm.MousePointer = vbDefault
    Me.Show
    
End Sub

Public Function ShowResult(Optional ByVal iPageStart As Integer = 0) As Integer
    Dim sSQL As String
    Dim rstTemp As ADODB.Recordset
    
    Dim sTemp As String                 '临时存放科目代码
    Dim PreStartOfMonth As String       '查询起始月份的上一个月
    Dim PreKjqj As Integer              '当前凭证的上一张凭证的会计期间
    Dim CurKjqj As Integer              '当前凭证会计期间
    Dim i As Integer
    ShowResult = iPageStart
    Cllr.ResetContent
    '得到当前账套的单位名称
    m_sEnterName = GetDWMC
    If m_sEnterName = "" Then
        
        Exit Function
    End If
    gcJ = 0
    gcD = 0
    gcJSL = 0
    gcDSL = 0
    gcJWB = 0
    gcDWB = 0