frmac_bookresult.frm

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

            Case "SQL"
    '            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
    '                    " FROM tZW_pzsj" & m_sYear & _
    '                    " WHERE pzrq >= '" & glo.sUnEarlierDate & "'" & _
    '                    " AND pzrq <'" & m_sFromDate & "'" & _
    '                    " AND kmdm in ('" & Join(GetChildrenKm(m_sSubjCode), "','") & "')" & _
    '                    " AND fx='贷' "
                sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                                   " FROM tZW_pzsj" & m_sYear & _
                                   " WHERE pzrq >= '" & glo.sOperateYear & "-01-01" & "'" & _
                                   " AND pzrq <'" & m_sFromDate & "'" & _
                                   " AND kmdm like '" & Trim(m_sSubjCode) & "-%'" & _
                                   " AND fx='贷' "
            Case "ORACLE"
    '            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
    '                    " FROM tZW_pzsj" & m_sYear & _
    '                    " WHERE pzrq >= TO_DATE('" & glo.sUnEarlierDate & "','YYYY-MM-DD')" & _
    '                    " AND pzrq < TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
    '                    " AND kmdm in ('" & Join(GetChildrenKm(m_sSubjCode), "','") & "')" & _
    '                    " AND fx='贷'"
                sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                                    " FROM tZW_pzsj" & m_sYear & _
                                    " WHERE pzrq >= TO_DATE('" & glo.sOperateYear & "-01-01" & "','YYYY-MM-DD')" & _
                                    " AND pzrq < TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
                                    " AND kmdm like '" & Trim(m_sSubjCode) & "-%'" & _
                                    " AND fx='贷'"
        End Select
    End If
    If m_bIncludeNotRecord Then
        sSQL = sSQL & " AND  xgbz<=2 and kjqj in (1,2,3,4,5,6,7,8,9,10,11,12)"
    Else
        sSQL = sSQL & " AND xgbz=2 and kjqj in (1,2,3,4,5,6,7,8,9,10,11,12)"
    End If
    With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If .BOF And .EOF Then
        Else
            dYE = dYE - IIf(IsNull(.Fields("sumJE").value), 0, .Fields("sumJE").value)
            dYESL = dYESL - IIf(IsNull(.Fields("sumSL").value), 0, .Fields("sumSL").value)
            dYEWB = dYEWB - IIf(IsNull(.Fields("sumWB").value), 0, .Fields("sumWB").value)
            byhjDSL = byhjDSL + IIf(IsNull(.Fields("sumSL").value), 0, .Fields("sumSL").value)
            byhjDWB = byhjDWB + IIf(IsNull(.Fields("sumWB").value), 0, .Fields("sumWB").value)
            byhjD = byhjD + IIf(IsNull(.Fields("sumJE").value), 0, .Fields("sumJE").value)
            bnljDSL = bnljDSL + byhjDSL
            bnljDWB = bnljDWB + byhjDWB
            bnljD = bnljD + byhjD
        End If
        .Close
    End With
      If IsEndSubject(m_sSubjCode) Then
           Select Case g_FLAT
        Case "SQL"
'            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
'                    " FROM tZW_pzsj" & m_sYear & _
'                    " WHERE pzrq >='" & glo.sUnEarlierDate & "'" & _
'                    " AND pzrq <'" & m_sFromDate & "'" & _
'                    " AND kmdm in ('" & Join(GetChildrenKm(m_sSubjCode), "','") & "')" & _
'                    " AND fx='借' "
            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                               " FROM tZW_pzsj" & m_sYear & _
                               " WHERE pzrq >='" & glo.sOperateYear & "-" & Mid(m_sFromDate, 6, 2) & "-01'" & _
                               " AND pzrq <'" & m_sFromDate & "'" & _
                               " AND kmdm = '" & Trim(m_sSubjCode) & "'" & _
                               " AND fx='贷' "
        Case "ORACLE"
'            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
'                    " FROM tZW_pzsj" & m_sYear & _
'                    " WHERE pzrq >= TO_DATE('" & glo.sUnEarlierDate & "','YYYY-MM-DD')" & _
'                    " AND pzrq < TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
'                    " AND kmdm in ('" & Join(GetChildrenKm(m_sSubjCode), "','") & "')" & _
'                    " AND fx='借'"
            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                               " FROM tZW_pzsj" & m_sYear & _
                               " WHERE pzrq >= TO_DATE('" & glo.sOperateYear & "-" & Mid(m_sFromDate, 6, 2) & "-01" & "','YYYY-MM-DD')" & _
                               " AND pzrq < TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
                               " AND kmdm = '" & Trim(m_sSubjCode) & "'" & _
                               " AND fx='贷'"
    End Select
      Else
            Select Case g_FLAT
        Case "SQL"
'            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
'                    " FROM tZW_pzsj" & m_sYear & _
'                    " WHERE pzrq >='" & glo.sUnEarlierDate & "'" & _
'                    " AND pzrq <'" & m_sFromDate & "'" & _
'                    " AND kmdm in ('" & Join(GetChildrenKm(m_sSubjCode), "','") & "')" & _
'                    " AND fx='借' "
            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                               " FROM tZW_pzsj" & m_sYear & _
                               " WHERE pzrq >='" & glo.sOperateYear & "-" & Mid(m_sFromDate, 6, 2) & "-01'" & _
                               " AND pzrq <'" & m_sFromDate & "'" & _
                               " AND kmdm like '" & Trim(m_sSubjCode) & "-%'" & _
                               " AND fx='贷' "
        Case "ORACLE"
'            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
'                    " FROM tZW_pzsj" & m_sYear & _
'                    " WHERE pzrq >= TO_DATE('" & glo.sUnEarlierDate & "','YYYY-MM-DD')" & _
'                    " AND pzrq < TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
'                    " AND kmdm in ('" & Join(GetChildrenKm(m_sSubjCode), "','") & "')" & _
'                    " AND fx='借'"
            sSQL = "SELECT sum(je) sumJE,sum(wb) sumWB,sum(sl) sumSL" & _
                               " FROM tZW_pzsj" & m_sYear & _
                               " WHERE pzrq >= TO_DATE('" & glo.sOperateYear & "-" & Mid(m_sFromDate, 6, 2) & "-01" & "','YYYY-MM-DD')" & _
                               " AND pzrq < TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
                               " AND kmdm like '" & Trim(m_sSubjCode) & "-%'" & _
                               " AND fx='贷'"
    End Select
    End If
    If m_bIncludeNotRecord Then
        sSQL = sSQL & "  AND xgbz<=2 and kjqj in (1,2,3,4,5,6,7,8,9,10,11,12)"
    Else
        sSQL = sSQL & "  AND xgbz=2and kjqj in (1,2,3,4,5,6,7,8,9,10,11,12)"
    End If
   With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If .BOF And .EOF Then
        Else
           
            
           byhjDSL = IIf(IsNull(.Fields("sumSL").value), 0, .Fields("sumSL").value)
            byhjDWB = IIf(IsNull(.Fields("sumWB").value), 0, .Fields("sumWB").value)
            byhjD = IIf(IsNull(.Fields("sumJE").value), 0, .Fields("sumJE").value)
            
           
            
        End If
        .Close
    End With
    
    If dYE > 0 Then
        sFX = "借"
'        bnljJ = bnljJ + dYE
'        bnljJSL = bnljJSL + dYESL
'        bnljJWB = bnljJWB + dYEWB
    ElseIf dYE = 0 Then
        sFX = "平"
    Else
        sFX = "贷"
'        bnljD = bnljD + dYE
'        bnljDSL = bnljDSL + dYESL
'        bnljDWB = bnljDWB + dYEWB
    End If
    
    '加入一行
    CurRow = CurRow + 1
    lCount = lCount + 1
    If Right$(m_sFromDate, 5) = "01-01" Then
        sTemp = "[ 上  年  结  转 ]"
        Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                0, 0, "", "", "", sTemp, 0, 0, sFX, IIf(sFX = "借", dYESL, -dYESL), _
                IIf(sFX = "借", dYEWB, -dYEWB), IIf(sFX = "借", dYE, -dYE), "")
    ElseIf Right$(m_sFromDate, 2) = "01" Then
        sTemp = "[ 期  初  余  额 ]"
        Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                0, 0, "", "", "", sTemp, 0, 0, sFX, IIf(sFX = "借", dYESL, -dYESL), _
                IIf(sFX = "借", dYEWB, -dYEWB), IIf(sFX = "借", dYE, -dYE), "")
    Else
        sTemp = "[ 昨  日  余  额 ]"
        Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                0, 0, "", "", "", sTemp, 0, 0, sFX, IIf(sFX = "借", dYESL, -dYESL), _
                IIf(sFX = "借", dYEWB, -dYEWB), IIf(sFX = "借", dYE, -dYE), "")
    End If
'-------------------------------------------------
'从凭证表中取出记录进行处理
'取出的字段：会计期间、凭证种类、编号、日期、摘要、方向、金额、外币、汇率、制单人名

    For i = Val(m_sFromMonth) To Val(m_sToMonth)
        If Val(m_sFromMonth) = Val(m_sToMonth) Then
            sStr = "kjqj = " & i
        ElseIf i = Val(m_sFromMonth) Then
            sStr = "( kjqj = " & i
        ElseIf i = Val(m_sToMonth) Then
            sStr = sStr & " OR kjqj = " & i & " )"
        Else
            sStr = sStr & " OR kjqj = " & i
        End If
    Next i
    If IsEndSubject(m_sSubjCode) Then
     Select Case g_FLAT
        Case "SQL"
            sSQL = "SELECT ID,kjqj,pzzl,pzbh,pzrq,pzzy,Yhdz_bill,fx,je,sl,dj,wb,hl,zdrm,xgbz" & _
                    " FROM tZW_pzsj" & m_sYear & _
                    " WHERE " & sStr & " and pzrq >= '" & m_sFromDate & "' " & _
                    " AND pzrq <='" & m_sToDate & "'" & _
                    " AND kmdm = '" & Trim(m_sSubjCode) & "'"
        Case "ORACLE"
            sSQL = "SELECT ID,kjqj,pzzl,pzbh,pzrq,pzzy,Yhdz_bill,fx,je,sl,dj,wb,hl,zdrm,xgbz" & _
                    " FROM tZW_pzsj" & m_sYear & _
                    " WHERE " & sStr & " and pzrq >= TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
                    " AND pzrq <= TO_DATE('" & m_sToDate & "','YYYY-MM-DD')" & _
                    " AND kmdm = '" & Trim(m_sSubjCode) & "'"
    End Select
    Else
    Select Case g_FLAT
        Case "SQL"
            sSQL = "SELECT ID,kjqj,pzzl,pzbh,pzrq,pzzy,Yhdz_bill,fx,je,sl,dj,wb,hl,zdrm,xgbz" & _
                    " FROM tZW_pzsj" & m_sYear & _
                    " WHERE " & sStr & " and pzrq >= '" & m_sFromDate & "' " & _
                    " AND pzrq <='" & m_sToDate & "'" & _
                    " AND kmdm like '" & Trim(m_sSubjCode) & "-%'"
        Case "ORACLE"
            sSQL = "SELECT ID,kjqj,pzzl,pzbh,pzrq,pzzy,Yhdz_bill,fx,je,sl,dj,wb,hl,zdrm,xgbz" & _
                    " FROM tZW_pzsj" & m_sYear & _
                    " WHERE " & sStr & " and pzrq >= TO_DATE('" & m_sFromDate & "','YYYY-MM-DD')" & _
                    " AND pzrq <= TO_DATE('" & m_sToDate & "','YYYY-MM-DD')" & _
                    " AND kmdm like '" & Trim(m_sSubjCode) & "-%'"
    End Select
    End If
    If m_bIncludeNotRecord Then
        sSQL = sSQL & " AND xgbz<=2  and kjqj in (1,2,3,4,5,6,7,8,9,10,11,12)" & " ORDER BY  pzrq,pzzl,pzbh,jlhm"
    Else
'=============================2002.9.6 yao revise======================================================
'        sSQL = sSQL & " AND xgbz=2" & " ORDER BY ID pzrq,pzzl,pzbh"
        sSQL = sSQL & " AND xgbz=2 and kjqj in (1,2,3,4,5,6,7,8,9,10,11,12)" & " ORDER BY pzrq,pzzl,pzbh,jlhm"
'======================================================================================================
    End If
    
    With rstTemp
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If .RecordCount > 0 Then
            Call InitVariant
            .MoveFirst
            Do Until .EOF
                '如果是第一条凭证记录, 则将变量PreKjqj,CurKjqj赋值
                If lCount = 1 Then
                    
                    PreKjqj = .Fields("kjqj").value
                    CurKjqj = CInt(m_sFromMonth)
                    While CurKjqj < PreKjqj
                        CurRow = CurRow + 1
                        lCount = lCount + 1
                        If Abs(dYE) < 0.005 Then
                            sFX = "平"
                        ElseIf dYE > 0 Then
                            sFX = "借"
                        Else
                            sFX = "贷"
                        End If
                        Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                            CurKjqj, 0, "", "", "", "[  本  月  合  计  ]", 0, 0, sFX, _
                            IIf(sFX = "借", dYESL, -dYESL), _
                            IIf(sFX = "借", dYEWB, -dYEWB), IIf(sFX = "借", dYE, -dYE), "")
                        CurRow = CurRow + 1
                        lCount = lCount + 1
                        If Abs(dYE) < 0.005 Then
                            sFX = "平"
                        ElseIf dYE > 0 Then
                            sFX = "借"
                        Else
                            sFX = "贷"
                        End If
                        Call AppendOneRow(ROW_GRID_START + lCount - 1, CurRow - ROW_GRID_START + 1, _
                            CurKjqj, 0, "", "", "", "[  本  年  累  计  ]", 0, 0, sFX, _
                            IIf(sFX = "借", dYESL, -dYESL), _
                            IIf(sFX = "借", dYEWB, -dYEWB), IIf(sFX = "借", dYE, -dYE), "")
                        CurKjqj = CurKjqj + 1
                    Wend
                    PreKjqj = .Fields("kjqj").value
                    CurKjqj = .Fields("kjqj").value
                    PrePzrq = .Fields("pzrq").value
                    CurPzrq = .Fields("pzrq").value
                Else
                    CurKjqj = .Fields("kjqj").value
                    CurPzrq = .Fields("pzrq").value
                    
                    '与前一张凭证不在同一个月
                    If CurKjqj <> PreKjqj Then
                        CurRow = CurRow + 1
                        lCount = lCount + 1
                        Call AppendTotalRows(PrePzrq)
                        If (lCount Mod ROWS_PAGE) = 0 And Not rstTemp.EOF Then
                            Call AppendChangePageRow(2 + 4)
                            CurRow = CurRow - 1
                            lCount = lCount - 1
                        End If
                    '与前一张凭证不在同一个天