frmbank.frm

金算盘软件代码

'    If cboBank(Index).ListIndex = -1 Then Exit Sub
    If index = 0 Then
        If mlngAcnID = cboBank(0).ItemData(cboBank(0).ListIndex) Then Exit Sub
        mlngAcnID = cboBank(0).ItemData(cboBank(0).ListIndex)
        mlngCurID = 0
        InitCurrencyList
    Else
        If mlngCurID = cboBank(1).ItemData(cboBank(1).ListIndex) Then Exit Sub
        mlngCurID = cboBank(1).ItemData(cboBank(1).ListIndex)
    End If
    If mblnIsChanged Then
        If Not SaveData Then
            ShowMsg hwnd, "保存数据失败,本次对帐无效!", vbExclamation, "银行对帐"
            Exit Sub
        End If
    End If
    With frmCollate.msgCollate
    For i = 1 To .Rows - 1
        If .TextMatrix(i, 0) = mlngAcnID And .TextMatrix(i, 1) = mlngCurID Then
            gstrEndDate = .TextMatrix(i, 5)
            dteEnd.Text = gstrEndDate
        End If
    Next i
    End With
    cmdRefer.Enabled = False
    mnuRefer.Enabled = False
    InitCurrencyList
    cmdRefer.Value = False
    InitBillGrid
    InitBankGrid
'    RefreshGrid msgBill
'    RefreshGrid msgBank
    RefreshTitle
    CheckBalance
    chkAll_Click
End Sub

Private Sub CheckBalance()
    Dim i As Integer
    Dim dblBillDebit As Double, dblBillCredit As Double
    Dim dblBankDebit As Double, dblBankCredit As Double
    Dim dblAdaptdBank As Double, dblAdaptdBill As Double
    Dim intBillDCount As Integer, intBillCCount As Integer
    Dim intBankDCount As Integer, intBankCCount As Integer
    
    For i = 1 To msgBill.Rows - 1
        If msgBill.TextMatrix(i, 1) <> "√" Then
            If Trim(msgBill.TextMatrix(i, 6)) = "" Or Trim(msgBill.TextMatrix(i, 6)) = "0" Then
                dblBillCredit = dblBillCredit + TxtToDouble(msgBill.TextMatrix(i, 7))
                intBillCCount = intBillCCount + 1
            Else
                dblBillDebit = dblBillDebit + TxtToDouble(msgBill.TextMatrix(i, 6))
                intBillDCount = intBillDCount + 1
            End If
        End If
    Next i
    For i = 1 To msgBank.Rows - 1
        If msgBank.TextMatrix(i, 1) <> "√" Then
            If Trim(msgBank.TextMatrix(i, mintBankDebitCol)) = "" Or Trim(msgBank.TextMatrix(i, mintBankDebitCol)) = "0" Then
                dblBankCredit = dblBankCredit + TxtToDouble(msgBank.TextMatrix(i, mintBankCreditCol))
                intBankCCount = intBankCCount + 1
            Else
                dblBankDebit = dblBankDebit + TxtToDouble(msgBank.TextMatrix(i, mintBankDebitCol))
                intBankDCount = intBankDCount + 1
            End If
        End If
    Next i
    dblAdaptdBank = mdblBankBalance + dblBillCredit - dblBillDebit
    dblAdaptdBill = mdblBillBalance + dblBankDebit - dblBankCredit
    If dblBillCredit <> 0 Then
        lblBank(5).Caption = "+:银行已收企业未收:" & intBillCCount & "笔"
        lblBank(14).Caption = FormatShow(dblBillCredit, mbytCurDec)
    Else
        lblBank(5).Caption = "+:银行已收企业未收:"
        lblBank(14).Caption = ""
    End If
    If dblBillDebit <> 0 Then
        lblBank(6).Caption = "-:银行已付企业未付:" & intBillDCount & "笔"
        lblBank(15).Caption = FormatShow(dblBillDebit, mbytCurDec)
    Else
        lblBank(6).Caption = "-:银行已付企业未付:"
        lblBank(15).Caption = ""
    End If
    If dblAdaptdBank <> 0 Then
        lblBank(19).Caption = FormatShow(dblAdaptdBank, mbytCurDec)
    Else
        lblBank(19).Caption = ""
    End If
    If (dblAdaptdBank - dblAdaptdBill) > 0 Then
        lblBank(21).Caption = FormatShow(Abs(dblAdaptdBank - dblAdaptdBill), mbytCurDec)
        lblBank(22).Caption = ""
    ElseIf (dblAdaptdBank - dblAdaptdBill) < 0 Then
        lblBank(22).Caption = FormatShow(Abs(dblAdaptdBank - dblAdaptdBill), mbytCurDec)
        lblBank(21).Caption = ""
    Else
        lblBank(21).Caption = ""
        lblBank(22).Caption = ""
    End If
    If dblBankDebit <> 0 Then
        lblBank(12).Caption = "+:企业已收银行未收:" & intBankDCount & "笔"
        lblBank(17).Caption = FormatShow(dblBankDebit, mbytCurDec)
    Else
        lblBank(12).Caption = "+:企业已收银行未收:"
        lblBank(17).Caption = ""
    End If
    If dblBankCredit <> 0 Then
        lblBank(10).Caption = "-:企业已付银行未付:" & intBankCCount & "笔"
        lblBank(18).Caption = FormatShow(dblBankCredit, mbytCurDec)
    Else
        lblBank(10).Caption = "-:企业已付银行未付:"
        lblBank(18).Caption = ""
    End If
    If dblAdaptdBill <> 0 Then
        lblBank(20).Caption = FormatShow(dblAdaptdBill, mbytCurDec)
    Else
        lblBank(20).Caption = ""
    End If
End Sub

Private Sub chkAll_Click()
'    RefreshGrid msgBill
'    RefreshGrid msgBank
    If mblnIsChanged Then
        If ShowMsg(hwnd, "要保存本次对帐的结果吗?", vbQuestion + vbYesNo, "银行对帐") = vbYes Then
            SaveData
        End If
    End If
    InitBillGrid
    InitBankGrid
    SetColWidth
    If cmdRefer.Value Then cmdRefer_Click
End Sub

Private Sub cmdCollate_Click(index As Integer)
    Dim i As Integer
    
    Select Case index
    Case 0
        AutoCollate
        CheckBalance
        mblnIsChanged = True
    Case 1                                 '对帐方式设置
'        If mblnIsChanged Then
'            If ShowMsg(hwnd, "设置对帐方式要刷新显示，您要保存当前的对帐情况吗？", _
'                vbQuestion + vbYesNo, "银行对帐") = vbYes Then
'                    SaveData
'            End If
'        End If
        frmBankStyle.Show vbModal
'        cmdRefer.Enabled = False
    Case 2                                 '取消对帐
        If ShowMsg(hwnd, "要取消对帐吗?", vbQuestion + vbYesNo, "银行对帐") = vbYes Then
            For i = 1 To msgBill.Rows - 1
                If msgBill.TextMatrix(i, 1) <> "" Then
                    msgBill.TextMatrix(i, 1) = ""
                    mblnBillChan = True
'                    msgBill.RowHeight(i) = msgBill.RowHeight(0)
                End If
            Next i
            For i = 1 To msgBank.Rows - 1
                If msgBank.TextMatrix(i, 1) <> "" Then
                    msgBank.TextMatrix(i, 1) = ""
                    mblnBankChan = True
'                    msgBank.RowHeight(i) = msgBank.RowHeight(0)
                End If
            Next i
            CheckBalance
            mblnIsChanged = True
        End If
    End Select
End Sub

Private Function DataChanged(Grid As MSFlexGrid, iRow As Integer, iCol As Integer) As Boolean
    DataChanged = Grid.TextMatrix(iRow, 1) = "√" And Grid.TextMatrix(iRow, iCol) = 0 Or _
        Grid.TextMatrix(iRow, 1) = "" And Grid.TextMatrix(iRow, iCol) = 1
End Function

Private Sub cmdOK_Click(index As Integer)
    If index = 0 Then
        If Not SaveData Then
            ShowMsg hwnd, "保存数据失败,本次对帐无效!", vbExclamation, "银行对帐"
            Exit Sub
        End If
    End If
    Unload Me
End Sub

Private Function MakeWhere(Optional strWhere1 As String = "")
    Dim GridX As MSFlexGrid
    Dim intDebitCol As Integer, intCreditCol As Integer
    Dim intDateCol As Integer, intNOCol As Integer
    Dim intPayModCol As Integer
    Dim strSql As String, strSql1 As String
    Dim intDirection As Integer, dblAmount As Double
    Dim strDate As String, strNo As String, strPayMod As String
    Dim recP As rdoResultset, lngP As Long

    If mblnIsBill Then
        Set GridX = msgBill
        intDebitCol = 6
        intCreditCol = 7
        intNOCol = 5
        intPayModCol = 4
    Else
        Set GridX = msgBank
        intDebitCol = mintBankDebitCol
        intCreditCol = mintBankCreditCol
        intNOCol = 8
        intPayModCol = 7
    End If
    intDateCol = 2
    With GridX
        If TxtToDouble(.TextMatrix(mintRow, intDebitCol)) <> 0 Then
            intDirection = -1
            dblAmount = TxtToDouble(.TextMatrix(mintRow, intDebitCol))
        Else
            intDirection = 1
            dblAmount = TxtToDouble(.TextMatrix(mintRow, intCreditCol))
        End If
        strSql = "intDirection=" & intDirection & " AND dblAmount=" & dblAmount
        strWhere1 = strSql
        If gblnByDay Then
            strDate = .TextMatrix(mintRow, intDateCol)
            strSql = strSql & " AND abs(TO_DATE(strDate)-TO_DATE('" & strDate & "'))<=" & gintDiff
        End If
        Select Case gintMatchModel
        Case 2
            strNo = .TextMatrix(mintRow, intNOCol)
            strSql = strSql & " AND strCheckNumber='" & strNo & "'"
            strWhere1 = strSql
        Case 3
            strPayMod = .TextMatrix(mintRow, intPayModCol)
            strSql = strSql & " AND strPaymentMethodName='" & strPayMod & "'"
            strSql1 = "SELECT lngPaymentMethodID FROM PaymentMethod WHERE strPaymentMethodName='" & strPayMod & "'"
            Set recP = gclsBase.BaseDB.OpenResultset(strSql1, rdOpenForwardOnly)
            If Not recP.EOF Then
                lngP = recP("lngPaymentMethodID")
            Else
                lngP = 0
            End If
            recP.Close
            strWhere1 = strWhere1 & " AND lngPaymentMethodID=" & lngP
        Case 4
            strNo = .TextMatrix(mintRow, intNOCol)
            strSql = strSql & " AND strCheckNumber='" & strNo & "'"
            strPayMod = .TextMatrix(mintRow, intPayModCol)
            strSql = strSql & " AND strPaymentMethodName='" & strPayMod & "'"
            strSql1 = "SELECT lngPaymentMethodID FROM PaymentMethod WHERE strPaymentMethodName='" & strPayMod & "'"
            Set recP = gclsBase.BaseDB.OpenResultset(strSql1, rdOpenForwardOnly)
            If Not recP.EOF Then
                lngP = recP("lngPaymentMethodID")
            Else
                lngP = 0
            End If
            recP.Close
            strWhere1 = strWhere1 & " AND lngPaymentMethodID=" & lngP
        End Select
    End With
    Set GridX = Nothing
    MakeWhere = strSql
End Function

Private Sub cmdRefer_Click()
    Dim strWhere1 As String
    
    If mblnIsChanged Then
        If (mblnIsBill And mblnBankChan) Or (Not mblnIsBill And mblnBillChan) Then
            If ShowMsg(hwnd, "要保存本次对帐的结果吗?", vbQuestion + vbYesNo, "银行对帐") = vbYes Then
                SaveData
            Else
                mblnIsChanged = False
'                If mblnIsBill Then
'                    If msgBill.TextMatrix(mintRow, 1) = "√" Then
'                        msgBill.TextMatrix(mintRow, 1) = ""
'                    Else
'                        msgBill.TextMatrix(mintRow, 1) = "√"
'                    End If
'                Else
'                    If msgBank.TextMatrix(mintRow, 1) = "√" Then
'                        msgBank.TextMatrix(mintRow, 1) = ""
'                    Else
'                        msgBank.TextMatrix(mintRow, 1) = "√"
'                    End If
'                End If
            End If
        End If
    End If
    If cmdRefer.Value Then
        chkAll.Enabled = False
        If mblnIsBill Then
            InitBankGrid MakeWhere(strWhere1), strWhere1
'            For i = 1 To msgBank.Rows - 1
'                If Match(mintRow, i, False) <> 0 Then
'                    msgBank.RowHeight(i) = 0
'                Else
'                    If chkAll.Value Then
'                        msgBank.RowHeight(i) = msgBank.RowHeight(0)
'                    Else
'                        If msgBank.TextMatrix(i, 1) <> "√" Then
'                            msgBank.RowHeight(i) = msgBank.RowHeight(0)
'                        End If
'                    End If
'                End If
'            Next i
        Else
            InitBillGrid MakeWhere(strWhere1), strWhere1
'            For i = 1 To msgBill.Rows - 1
'                If Match(i, mintRow, False) <> 0 Then
'                    msgBill.RowHeight(i) = 0
'                Else
'                    If chkAll.Value Then
'                        msgBill.RowHeight(i) = msgBill.RowHeight(0)
'                    Else
'                        If msgBill.TextMatrix(i, 1) <> "√" Then
'                            msgBill.RowHeight(i) = msgBill.RowHeight(0)
'                        End If
'                    End If
'                End If
'            Next i
        End If
    Else
        chkAll.Enabled = True
        If mblnIsBill Then
            InitBankGrid
            msgBill.SetFocus
'            For i = 1 To msgBank.Rows - 1
'                If chkAll.Value Then
'                    msgBank.RowHeight(i) = msgBank.RowHeight(0)
'                Else
'                    If msgBank.TextMatrix(i, 1) <> "√" Then
'                        msgBank.RowHeight(i) = msgBank.RowHeight(0)
'                    End If
'                End If
'            Next i
        Else
            InitBillGrid
            msgBank.SetFocus
'            For i = 1 To msgBill.Rows - 1
'                If chkAll.Value Then
'                    msgBill.RowHeight(i) = msgBill.RowHeight(0)
'                Else
'                    If msgBill.TextMatrix(i, 1) <> "√" Then
'                        msgBill.RowHeight(i) = msgBill.RowHeight(0)
'                    End If
'                End If
'            Next i
        End If
    End If
    SetColWidth
'    If mblnIsBill Then
'        msgBill.SetFocus
'    Else
'        msgBank.SetFocus
'    End If
End Sub

Private Sub dteEnd_LostFocus()
    Dim i As Integer, dblBalance As Double
    Dim recX As rdoResultset, strSql As String
    
    If gstrEndDate = dteEnd.Text Then Exit Sub
    strSql = "SELECT strStartDate FROM BankInfo WHERE lngAccountID=" _
        & mlngAcnID & " AND lngCurrencyID=" & mlngCurID
    Set recX = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
    If dteEnd.Value < CDate(recX!strStartDate) Then
        ShowMsg hwnd, "对帐截止日期不能小于对帐启用日期!", vbExclamation, "银行对帐"
        dteEnd.Text = Format(recX!strStartDate, "yyyy-mm-dd")