frmac_detailresult.frm

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

    bnljJSL = 0
    bnljJWB = 0
    bnljD = 0
    bnljDSL = 0
    bnljDWB = 0

    fMainForm.MousePointer = vbHourglass
    
    '设置当前科目名称和代码
    m_sCurSubjectCode = arySubDetail(cboSubject.ListIndex + 1).sSubjectCode
    m_sCurSubjectName = arySubDetail(cboSubject.ListIndex + 1).sSubjectName
    If m_sCurSubjectCode = "" Then
       fMainForm.MousePointer = vbDefault
       MsgBox m_sSubjectCodeStart & "科目不存在，或者为日记账科目,不能联查明细账！", vbInformation
       Me.Hide
       Exit Sub
    End If
    
    '将CELL不可见，防止清除CELL控件内容时屏幕闪烁；
    Cllr.Visible = False

    Cllr.ResetContent
    '清除CELL控件内容后，必须重新设置CELL的行数和列数；
    Cllr.SetCols COL_END + 2, 0
    Cllr.SetRows ROW_GRID_START + ROWS_PAGE, 0
    
    '设置当前科目名称和代码
    m_sCurSubjectCode = arySubDetail(cboSubject.ListIndex + 1).sSubjectCode
    m_sCurSubjectName = arySubDetail(cboSubject.ListIndex + 1).sSubjectName
    If m_sCurSubjectCode = "" Then
      fMainForm.MousePointer = vbDefault
      MsgBox m_sSubjectCodeStart & "科目不存在，或者为日记账科目,不能联查明细账！", vbInformation
      Me.Hide
      Exit Sub
    End If
    
    '得到打印时显示的科目代码和名称以及明细账科目
    If glo.sSeparateSubject = "0" Then
        If glo.bSeparateSubject Then
            m_sPrintSubjectCode = SeparateSubject(glo.sAccountID, m_sCurSubjectCode)
        Else
            m_sPrintSubjectCode = m_sCurSubjectCode
        End If
        m_sPrintSubjectName = GetSubjectFullPath(glo.sAccountID, m_sCurSubjectCode)
    Else
         m_sPrintSubjectName = GetSubjectFullPath(glo.sAccountID, m_sCurSubjectCode)
         m_sPrintSubjectCode = m_sCurSubjectCode
    End If
    If InStr(1, m_sPrintSubjectName, "\") > 0 Then
         m_sGeneralSubjectName = Mid(m_sPrintSubjectName, 1, InStr(1, m_sPrintSubjectName, "\") - 1)
    Else
         m_sGeneralSubjectName = m_sPrintSubjectName
    End If
    '得到一个科目数量单位和外币单位
    Call GetSldwAndWbdw(m_sCurSubjectCode, m_sSldw, m_sWbdw)
    If m_sSldw <> "" Then
        m_bAmount = True
    Else
        m_bAmount = False
    End If
    If m_sWbdw <> "" Then
        m_bForeign = True
    Else
        m_bForeign = False
    End If
    
    CurRow = ROW_GRID_START - 1
    lCount = 0
    lPage = 0
    
    Set rstTemp = New ADODB.Recordset
    rstTemp.CursorLocation = adUseClient
    
    
'如果查询开始月份为一月份, 则计算上年结转余额;
'否则计算上月余额;
    If m_sFromMonth = 0 Then
        PreStartOfMonth = "00"
    Else
        PreStartOfMonth = Format(Val(m_sFromMonth) - 1, "00")
    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 - dYBJJe
        bnljD = .Fields("DF01").value - dYBDJE
        bnljJSL = .Fields("JFSL01").value - dYBJSL
        bnljDSL = .Fields("DFSL01").value - dYBDSL
        bnljJWB = .Fields("JFWB01").value - dYBJWB
        bnljDWB = .Fields("DFWB01").value - dYBDWB
       .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 ) " & _
                 IIf(m_sZY <> "", " and pzzy like '%" + m_sZY + "%'", "") & _
            IIf(StartJe <> 0, " and je >=" + CStr(StartJe), "") & _
            IIf(EndJe <> 0, " and je<=" + CStr(EndJe), "") & _
                " AND kmdm = '" & Trim(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 ) " & _
                 IIf(m_sZY <> "", " and pzzy like '%" + m_sZY + "%'", "") & _
            IIf(StartJe <> 0, " and je >=" + CStr(StartJe), "") & _
            IIf(EndJe <> 0, " and je<=" + CStr(EndJe), "") & _
                " AND kmdm like '" & Trim(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 ) " & _
                IIf(m_sZY <> "", " and pzzy like '%" + m_sZY + "%'", "") & _
            IIf(StartJe <> 0, " and je >=" + CStr(StartJe), "") & _
            IIf(EndJe <> 0, " and je<=" + CStr(EndJe), "") & _
               " AND  kmdm = '" & Trim(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 ) " & _
                IIf(m_sZY <> "", " and pzzy like '%" + m_sZY + "%'", "") & _
            IIf(StartJe <> 0, " and je >=" + CStr(StartJe), "") & _
            IIf(EndJe <> 0, " and je<=" + CStr(EndJe), "") & _
               " AND  kmdm like '" & Trim(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
    
    '本年累计数量余额
    dbnYESL = bnljJSL - bnljDSL + dYBJSL - dYBDSL
    dbnYEWB = bnljJWB - bnljDWB + dYBJWB - dYBDWB
    dbnYE = bnljJ - bnljD + dYBJJe - dYBDJE

    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 = '" & Trim(m_sCurSubjectCode) & "'" & _
            IIf(m_sZY <> "", " and pzzy like '%" + m_sZY + "%'", "") & _
            IIf(StartJe <> 0, " and je >=" + CStr(StartJe), "") & _
            IIf(EndJe <> 0, " and je<=" + CStr(EndJe), "") & _
            " ORDER BY kjqj,pzrq,pzzl,pzbh,jlhm"
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 '" & Trim(m_sCurSubjectCode) & "-%'" & _
            IIf(m_sZY <> "", " and pzzy like '%" + m_sZY + "%'", "") & _
            IIf(StartJe <> 0, " and je >=" + CStr(StartJe), "") & _
            IIf(EndJe <> 0, " and je<=" + CStr(EndJe), "") & _
            " ORDER BY kjqj,pzrq,pzzl,pzbh,jlhm"
End If
    With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly

        If .RecordCount > 0 Then
            Call InitVariant
            .MoveFirst
            PreKjqj = CInt(m_sFromMonth)
            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
                
                '如果是第一条凭证记录, 则将变量PreKjqj,CurKjqj赋值
                CurKjqj = .Fields("kjqj").value
                '与前一张凭证不在同一个月
                If CurKjqj <> PreKjqj And lCount > 0 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
                        CurRow = CurRow + 1
                        lCount = lCount + 1
                    Else
                        '添加本月合计、本年累计行
                        Call AppendTotalRowsbak(PreKjqj)
                        If (lCount Mod ROWS_PAGE) = 0 And Not rstTemp.EOF Then
    '                            Call AppendChangePageRow(8 + 1)
                             Call AppendChangePageRow(8)
                        End If
                    End If
                Else
                    CurRow = CurRow + 1
                    lCount = lCount + 1
                End If
                '添加当前凭证记录行
                If (lCount Mod ROWS_PAGE) = 0 Then Call AppendChangePageRow(1)
                
                '合计本月、过次
                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 CurKjqj > 0 Then
                    iMonth = Month(.Fields("pzrq").value)
                    iDay = Day(.Fields("pzrq").value)