frmpz_newbalance.frm

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

            .MoveFirst
            If glo.sOperateYear < .Fields("ModiYear").Value Then
                sNotRecordAccount = 12
            ElseIf glo.sOperateYear = .Fields("modiyear").Value Then
                sNotRecordAccount = .Fields("ModiMonth").Value
            Else
                sNotRecordAccount = 0
            End If
        End If
        .Close
        .Open "SELECT * FROM tUSU_dmjs WHERE Type='科目' ORDER BY JC", _
                glo.cnnMain, adOpenStatic, adLockReadOnly
        ReDim m_aryKmCodeLen(.RecordCount)
        i = 1
        Do Until .EOF
            If i = 1 Then
                m_aryKmCodeLen(i) = .Fields("ws").Value
            Else
                m_aryKmCodeLen(i) = m_aryKmCodeLen(i - 1) + .Fields("ws").Value
            End If
            i = i + 1
            .MoveNext
        Loop
        .Close
        'yao======
        '本期借方，贷方发生情况
        sSQL = "select je, fx, sl, wb  from tzw_pzsj" & glo.sOperateYear & _
            " where kmdm ='" & Trim("" & sCode) & "' and kjqj=" & m_iMonth
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If Not (.EOF And .BOF) Then
            .MoveFirst
            While Not .EOF
                If .Fields("fx").Value = "借" Then
                    dJe(2) = dJe(2) + Val(.Fields("je").Value & "")
                    dSl(2) = dSl(2) + Val(.Fields("sl").Value & "")
                    dWb(2) = dWb(2) + Val(.Fields("wb").Value & "")
                 Else
                    dJe(3) = dJe(3) + Val(.Fields("je").Value & "")
                    dSl(3) = dSl(3) + Val(.Fields("sl").Value & "")
                    dWb(3) = dWb(3) + Val(.Fields("wb").Value & "")
                 End If
                 .MoveNext
            Wend
        End If
        .Close
        If bIncludePz = True Then
        sSQL = "select je, wb, sl,fx  from tzw_pzsj" & glo.sOperateYear & _
            " where kmdm ='" & Trim("" & sCode) & "' and kjqj=" & m_iMonth & _
            " and pzzl='" + m_sPZZL + "' and pzbh='" + m_sPzbh + "'"
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        While Not .EOF
            If .Fields("fx").Value = "借" Then
               dJe(2) = dJe(2) - Val(.Fields("je").Value & "")
               dSl(2) = dSl(2) - Val(.Fields("sl").Value & "")
               dWb(2) = dWb(2) - Val(.Fields("wb").Value & "")
            Else
               dJe(3) = dJe(3) - Val(.Fields("je").Value & "")
               dSl(3) = dSl(3) - Val(.Fields("sl").Value & "")
               dWb(3) = dWb(3) - Val(.Fields("wb").Value & "")
            End If
            .MoveNext
          Wend
          .Close
        End If
        'get the balance from tzw_balance
        
        sTemp = Format(sNotRecordAccount, "00")
        
        '结账后该账套预装的期初余额
        sSQL = "select kmmc , ljj" & sTemp & " ljj ,ljd" & sTemp & " ljd ,ljjwb" & _
            sTemp & " ljjwb,ljdwb" & sTemp & " ljdwb,ljjsl" & sTemp & " ljjsl,ljdsl" & _
            sTemp & " ljdsl,yefx from tzw_balance" & glo.sOperateYear & " where kmdm ='" & _
            Trim("" & sCode) & "'"
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If Not (.EOF And .BOF) Then
            '已记账的期初余额
            sFxTmp = Trim("" & .Fields("yefx").Value)
            If sFxTmp = "借方" Then
                dJeTmpj = Val(.Fields("ljj").Value & "")
                dWbTmpj = Val(.Fields("ljjwb").Value & "")
                dSlTmpj = Val(.Fields("ljjsl").Value & "")
            Else
                dJeTmpd = Val(.Fields("ljd").Value & "")
                dWbTmpd = Val(.Fields("ljdwb").Value & "")
                dSlTmpd = Val(.Fields("ljdsl").Value & "")
            End If
        End If
        .Close
        
       '记账与未记账合计(含本期的）
       'get the pzsj account
       sSQL = "select je, wb, sl,fx  from tzw_pzsj" & glo.sOperateYear & _
            " where kmdm ='" & Trim("" & sCode) & "' and kjqj>" & sNotRecordAccount & _
            " and kjqj <" & (m_iMonth)
       .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If Not (.EOF And .BOF) Then
            .MoveFirst
           
          While Not .EOF
            If .Fields("fx").Value = "借" Then
               dPzJej = dPzJej + Val(.Fields("je").Value & "")
               dPzSlj = dPzSlj + Val(.Fields("sl").Value & "")
               dPzWbj = dPzWbj + Val(.Fields("wb").Value & "")
            Else
               dPzJed = dPzJed + Val(.Fields("je").Value & "")
               dPzSld = dPzSld + Val(.Fields("sl").Value & "")
               dPzWbd = dPzWbd + Val(.Fields("wb").Value & "")
            End If
            .MoveNext
          Wend
        End If
        .Close
        
        If glo.sSeparateSubject <> "0" Then
             sKmmc = GetSubjectFullPath(glo.sAccountID, Trim("" & sCode))
        Else
            '求当前科目级次
            For i = 1 To UBound(m_aryKmCodeLen)
                If m_aryKmCodeLen(i) >= Len(sCode) Then
                    iLevel = i
                    Exit For
                End If
            Next i
            '求从一级科目到当前科目的名称路径
            sKmmc = ""
            If iLevel > 1 Then
                For i = 1 To iLevel - 1
                    sCodePre = Left(Trim("" & sCode), m_aryKmCodeLen(i))
                    .Open "SELECT kmmc FROM tZW_km" & glo.sOperateYear & _
                        " WHERE kmdm='" & sCodePre & "'", glo.cnnMain, _
                            adOpenStatic, adLockReadOnly
                    If Not (.EOF And .BOF) Then
                        sKmmc = sKmmc & .Fields("kmmc").Value & "\"
                    End If
                Next i
            End If
        End If
        If Trim("" & sCode) <> "" Then
            sKmdmTmp() = Split(Trim("" & sCode), glo.sSeparateSubject)
            sKmdmTemp = sKmdmTmp(0)
        End If
        sSQL = "select sldw,wbdw from tZW_Km" & glo.sOperateYear & "  where Kmdm='" & sKmdmTemp & "'"
        .Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
        If Not (.EOF And .BOF) Then
            lbldw.Caption = "计量单位：" & Trim("" & .Fields("sldw").Value)
            lblbz.Caption = "外币币种：" & Trim("" & .Fields("wbdw").Value)
        End If
        .Close

    End With
    'calculate the current balance and start balance
    If sFxTmp = "借方" Then
        '计算期末余额
        If m_sFx = "借" Then
            dJe(6) = dJeTmpj + dPzJej - dJeTmpd - dPzJed + m_dJe
            dWb(6) = dWbTmpj + dPzWbj - dWbTmpd - dPzWbd + m_dWb
            dSl(6) = dSlTmpj + dPzSlj - dSlTmpd - dPzSld + m_dSl
        Else
            dJe(6) = dJeTmpj + dPzJej - dJeTmpd - dPzJed - m_dJe
            dWb(6) = dWbTmpj + dPzWbj - dWbTmpd - dPzWbd - m_dWb
            dSl(6) = dSlTmpj + dPzSlj - dSlTmpd - dPzSld - m_dSl
        End If
         '计算期初余额(由期末的余额冲去本期发生额
        dJe(1) = dJeTmpj + dPzJej + dJe(3) - dJeTmpd - dPzJed - dJe(2)
        dWb(1) = dWbTmpj + dPzWbj + dWb(3) - dWbTmpd - dPzWbd - dWb(2)
        dSl(1) = dSlTmpj + dPzSlj + dSl(3) - dSlTmpd - dPzSld - dSl(2)
    Else
         '计算期末余额
        If m_sFx = "贷" Then
            dJe(6) = dJeTmpd + dPzJed - dJeTmpj - dPzJej + m_dJe
            dWb(6) = dWbTmpd + dPzWbd - dWbTmpj - dPzWbj + m_dWb
            dSl(6) = dSlTmpd + dPzSld - dSlTmpj - dPzSlj + m_dSl
        Else
            dJe(6) = dJeTmpd + dPzJed - dJeTmpj - dPzJej - m_dJe
            dWb(6) = dWbTmpd + dPzWbd - dWbTmpj - dPzWbj - m_dWb
            dSl(6) = dSlTmpd + dPzSld - dSlTmpj - dPzSlj - m_dSl
        End If
         '计算期初余额(由期末的余额冲去本期发生额
        dJe(1) = dJeTmpd + dPzJed + dJe(2) - dJeTmpj - dPzJej - dJe(3)
        dWb(1) = dWbTmpd + dPzWbd + dWb(2) - dWbTmpj - dPzWbj - dWb(3)
        dSl(1) = dSlTmpd + dPzSld + dSl(2) - dSlTmpj - dPzSlj - dSl(3)
    End If
        
    If dJe(6) > 0 Then
      sFx(6) = "借"
    ElseIf dJe(6) = 0 Then
      sFx(6) = " 平"
    Else
      sFx(6) = "贷"
      dJe(6) = Abs(dJe(6))
    End If
    If dJe(1) > 0 Then
      sFx(1) = "借"
    ElseIf dJe(1) = 0 Then
      sFx(1) = " 平"
    Else
      sFx(1) = "贷"
      dJe(1) = Abs(dJe(1))
    End If
    If m_sFx = "借" Then
        dJe(2) = dJe(2) + m_dJe
        dSl(2) = dSl(2) + m_dSl
        dWb(2) = dWb(2) + m_dWb
    Else
        dJe(3) = dJe(3) + m_dJe
        dSl(3) = dSl(3) + m_dSl
        dWb(3) = dWb(3) + m_dWb
    End If
   
    '计算借方累计，贷方累计
    If sFx(1) = "借" Then
        dJe(4) = dJe(1) + dJe(2)
        dWb(4) = dWb(1) + dWb(2)
        dSl(4) = dSl(1) + dSl(2)
        dJe(5) = dJe(3)
        dWb(5) = dWb(3)
        dSl(5) = dSl(3)
    Else
        dJe(4) = dJe(2)
        dWb(4) = dWb(2)
        dSl(4) = dSl(2)
        dJe(5) = dJe(1) + dJe(3)
        dWb(5) = dWb(1) + dWb(3)
        dSl(5) = dSl(1) + dSl(3)
    End If
    
    '赋值
    lblkm.Caption = "科  目：" & sKmmc & "(" & Trim("" & sCode) & ")"
    lblkm.AutoSize = True
    For i = 1 To 6
        mFg.TextMatrix(i, 1) = sFx(i)
        mFg.TextMatrix(i, 2) = IIf(dJe(i) = 0, "", CStr(Format(dJe(i), "#0.00")))
        mFg.TextMatrix(i, 3) = IIf(dWb(i) = 0, "", CStr(Format(dWb(i), "#0.00")))
        mFg.TextMatrix(i, 4) = IIf(dSl(i) = 0, "", CStr(Format(dSl(i), "#0.00")))
    Next i
    Set rstTemp = Nothing
End Sub