frmlastyearcarryforward.frm

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

       Case "未两清银行对账单"
        
            glo.frmProg.SetMsg "正在准备未两清银行对账单, 请稍候..."
            
            IsExistCarryForwardData = False
            adoSQL = "SELECT COUNT(*) FROM tZW_Km" & sCarryForwardYear - 1 & _
                        " WHERE IsYhz = -1"
            With adoRst
                .Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
                If .Fields(0).Value = 0 Then
                    MsgBox "未设置银行账科目, 不能结转未两清银行对账单!", vbInformation
                    .Close
                    Exit Sub
                End If
                .Close
            End With
            
            adoSQL = "SELECT A.kmdm,qyrq FROM tZW_Yhdzqyrq A,tZW_Km" & sCarryForwardYear - 1 & " B" & _
                        " WHERE rtrim(A.kmdm) = rtrim(B.kmdm) AND B.IsYhz = -1 AND B.IsEndKm = -1"
            With adoRst
                .Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
                If .RecordCount = 0 Then
                    MsgBox "银行对账尚未启用, 不能结转未两清银行对账单!", vbInformation
                    .Close
                    Exit Sub
                Else
                    ReDim sKmdm(1 To .RecordCount)
                    ReDim sYhdzqyrq(1 To .RecordCount)
                    .MoveFirst
                    i = 0
                    Do Until .EOF
                        i = i + 1
                        sKmdm(i) = Trim(.Fields("kmdm").Value)
                        If Year(.Fields("qyrq").Value) = CInt(sCarryForwardYear) - 1 Then
                            sYhdzqyrq(i) = Format(.Fields("qyrq").Value, "yyyy-mm-dd")
                        Else
                            sYhdzqyrq(i) = sCarryForwardYear - 1 & "-01-01"
                        End If
                        .MoveNext
                    Loop
                    For i = LBound(sKmdm) To UBound(sKmdm)
                        If i = LBound(sKmdm) Then
                            sYhKmdm = sKmdm(i)
                        Else
                            sYhKmdm = sYhKmdm & "," & sKmdm(i)
                        End If
                    Next i
                End If
                .Close
            End With
            
            adoCmd.CommandText = "DELETE FROM tZW_Yhdzd" & sCarryForwardYear & _
                                    " WHERE qcbz = 0 OR qcbz = 1"
            adoCmd.Execute
            
            adoSQL = "SELECT MAX(id) maxId FROM tZW_Yhdzd" & sCarryForwardYear
            With adoRst
                .Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
                If .BOF And .EOF Then
                    iMaxId = 0
                ElseIf IsNull(.Fields("maxId").Value) Then
                    iMaxId = 0
                Else
                    iMaxId = .Fields("maxId").Value
                End If
                .Close
            End With
            
            For iKmNum = LBound(sKmdm) To UBound(sKmdm)
                
                Select Case g_FLAT
                    Case "SQL"
                        adoSQL = "SELECT * FROM tZW_Yhdzd" & sCarryForwardYear - 1 & _
                                    " WHERE kmdm = '" & sKmdm(iKmNum) & _
                                    "' AND (qcbz = 1 OR (qcbz =2 AND" & _
                                    " rq >= '" & sYhdzqyrq(iKmNum) & "'))" & _
                                    " AND lqbz Is Null"
                    Case "ORACLE"
                        adoSQL = "SELECT * FROM tZW_Yhdzd" & sCarryForwardYear - 1 & _
                                    " WHERE kmdm = '" & sKmdm(iKmNum) & _
                                    "' AND (qcbz = 1 OR (qcbz = 2 AND" & _
                                    " rq >= TO_DATE('" & sYhdzqyrq(iKmNum) & "','YYYY-MM-DD')))" & _
                                    " AND lqbz Is Null"
                End Select
                With adoRst
                    .Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
                    If .RecordCount > 0 Then
                        IsExistCarryForwardData = True
                        iTotalNum = .RecordCount
                        If .RecordCount > PrecentOfYhdzd Then
                            IsBig = True
                            pgrNum = .RecordCount / PrecentOfYhdzd
                        Else
                            IsBig = False
                            pgrNum = PrecentOfYhdzd / .RecordCount
                        End If
                    
                    
                        '动态分配存放字段值
                        ReDim sSingleRecord(1 To .Fields.Count)
                        '动态分配存放记录
                        ReDim vTotalRecord(1 To .RecordCount)
                        .MoveFirst
                        j = 0
                        CurNum = 0
                        CurPgrNum = glo.frmProg.pBr.Value
                        CurNum = CurNum + 1
                        If IsBig Then
                            glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 / pgrNum)
                        Else
                            glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 * pgrNum)
                        End If
                        glo.frmProg.SetMsg "正在结转未两清银行对账单... 第" & Int(CurNum / 2) & "/" & iTotalNum & "笔"
                        Do Until .EOF
                            For i = 1 To .Fields.Count
                                If IsNull(.Fields(i - 1).Value) Then
                                    Select Case .Fields(i - 1).Type
                                        Case adChar, adVarChar
                                            sSingleRecord(i) = "Null"
                                        Case adDBTimeStamp
                                            sSingleRecord(i) = "Null"
                                        Case adDecimal, adDouble, adInteger, adCurrency, _
                                                adSingle, adSmallInt, adTinyInt, adNumeric
                                            sSingleRecord(i) = "Null"
                                    End Select
                                Else
                                    If .Fields(i - 1).Type = adDBTimeStamp Then
                                        Select Case g_FLAT
                                            Case "SQL"
                                                sSingleRecord(i) = Format(.Fields(i - 1).Value, "yyyy-mm-dd")
                                            Case "ORACLE"
                                                sSingleRecord(i) = "TO_DATE('" & Format(.Fields(i - 1).Value, "yyyy-mm-dd") & "','YYYY-MM-DD')"
                                        End Select
                                    Else
                                        '如果是第一个字段, 则设置其值为1,表示期初未两清的对账单
                                        If UCase(.Fields(i - 1).Name) = UCase("id") Then
                                            iMaxId = iMaxId + 1
                                            sSingleRecord(i) = iMaxId
                                        ElseIf UCase(.Fields(i - 1).Name) = UCase("qcbz") Then
                                            sSingleRecord(i) = 1
                                        Else
                                            sSingleRecord(i) = .Fields(i - 1).Value
                                        End If
                                    End If
                                End If
                            Next i
                            j = j + 1
                            vTotalRecord(j) = sSingleRecord
                            .MoveNext
                        Loop
                    Else
                        IsExistCarryForwardData = False
                    End If
                    .Close
                End With
                
                If IsExistCarryForwardData Then
                    For i = LBound(vTotalRecord) To UBound(vTotalRecord)
                        CurNum = CurNum + 1
                        If IsBig Then
                            glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 / pgrNum)
                        Else
                            glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 * pgrNum)
                        End If
                        glo.frmProg.SetMsg "正在结转未两清银行对账单... 第" & Int(CurNum / 2) & "/" & iTotalNum & "笔"
                        insertStr = ""
                        For j = LBound(vTotalRecord(i)) To UBound(vTotalRecord(i))
                            If j = UBound(vTotalRecord(i)) Then
                                If vTotalRecord(i)(j) = "Null" Then
                                    insertStr = insertStr & vTotalRecord(i)(j)
                                ElseIf vTotalRecord(i)(j) Like "TO_DATE*" Then
                                    insertStr = insertStr & vTotalRecord(i)(j)
                                Else
                                    insertStr = insertStr & "'" & vTotalRecord(i)(j) & "'"
                                End If
                            Else
                                If vTotalRecord(i)(j) = "Null" Then
                                    insertStr = insertStr & vTotalRecord(i)(j) & ","
                                ElseIf vTotalRecord(i)(j) Like "TO_DATE*" Then
                                    insertStr = insertStr & vTotalRecord(i)(j) & ","
                                Else
                                    insertStr = insertStr & "'" & vTotalRecord(i)(j) & "',"
                                End If
                            End If
                        Next j
                        adoCmd.CommandText = "INSERT INTO tZW_Yhdzd" & sCarryForwardYear & _
                                                " VALUES(" & insertStr & ")"
                        adoCmd.Execute
                        
                    Next i
                End If
                
                '求出银行方上年调整前余额
                
                glo.frmProg.SetMsg "正在计算银行对账单调整前余额, 请稍候..."
                Select Case g_FLAT
                    Case "SQL"
                        adoSQL = "SELECT fx,SUM(je) sumMoney FROM tZW_Yhdzd" & sCarryForwardYear - 1 & _
                                    " WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
                                    " AND (qcbz = 0 OR (qcbz = 2 AND rq >= '" & sYhdzqyrq(iKmNum) & "'))" & _
                                    " GROUP BY fx"
                    Case "ORACLE"
                        adoSQL = "SELECT fx,SUM(je) sumMoney FROM tZW_Yhdzd" & sCarryForwardYear - 1 & _
                                    " WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
                                    " AND (qcbz = 0 OR (qcbz = 2 AND" & _
                                    " rq >= TO_DATE('" & sYhdzqyrq(iKmNum) & "','YYYY-MM-DD')))" & _
                                    " GROUP BY fx"
                End Select
                dYhtzqye = 0
                With adoRst
                    .Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
                    If .BOF And .EOF Then
                        dYhtzqye = 0
                    ElseIf IsNull(.Fields("sumMoney").Value) Then
                        dYhtzqye = 0
                    Else
                        .MoveFirst
                        Do Until .EOF
                            If .Fields("fx").Value = "贷" Then
                                dYhtzqye = dYhtzqye + .Fields("sumMoney").Value
                            Else
                                dYhtzqye = dYhtzqye - .Fields("sumMoney").Value
                            End If
                            .MoveNext
                        Loop
                        iMaxId = iMaxId + 1
                        Select Case g_FLAT
                            Case "SQL"
                                adoCmd.CommandText = "INSERT INTO tZW_Yhdzd" & sCarryForwardYear & _
                                                            "(id,rq,kmdm,fx,je,qcbz) " & _
                                                     "VALUES(" & iMaxId & ",'" & _
                                                            Format(sCarryForwardYear & "-01-01", "yyyy-mm-dd") & "','" & _
                                                            sKmdm(iKmNum) & "','贷'," & _
                                                            dYhtzqye & ",0)"
                            Case "ORACLE"
                                adoCmd.CommandText = "INSERT INTO tZW_Yhdzd" & sCarryForwardYear & _
                                                            "(id,rq,kmdm,fx,je,qcbz) " & _
                                                     "VALUES(" & iMaxId & _
                                                            ",TO_DATE('" & sCarryForwardYear & "-01-01','YYYY-MM-DD'),'" & _
                                                            sKmdm(iKmNum) & "','贷'," & _
                                                            dYhtzqye & ",0)"
                        End Select
                        adoCmd.Execute
                    End If
                    .Close
                End With
            Next iKmNum
    End Select
    Exit Sub
HandleErr:
    MsgBox Err.Description, vbInformation, "提示"
    Exit Sub
End Sub


Private Sub Form_Unload(Cancel As Integer)
m_Mutex.DeleteMutexID gloSys.sSubSysID, glo.sAccountID, "mnuCarryForwordData", lMutexID
End Sub