ʰ

VB开发的ERP系统

    YHYE = 0
    '单位帐面余额
    SQL_Str = "SELECT SUM(JFJE)-SUM(DFJE) From Cwzz_RjzNotCheck Where RecType=0 And Ccode='" & Val(Combo1.Text) & "'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        DWYE = RecTemp(0)
    End If
    SQL_Str = "Select SUM(JFJE)-SUM(DFJE) From Cwzz_V_AccVouch where Ccode='" & Val(Combo1.Text) & "' " _
    & "And Convert(Char(8),Ddate,112)>=(Select Convert(Char(8),Ddate,112) From " _
    & "Cwzz_RjzNotCheck Where RecType=0 And Ccode='" & Val(Combo1.Text) & "') " _
    & "And Convert(Char(6),Ddate,112)<=(select Convert(Char(6),max(qsrq),112) from GY_kjrlb where cwzzjzbz='1')"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        DWYE = DWYE + RecTemp(0)
    End If
    '    '银行帐面余额
    SQL_Str = "SELECT SUM(DFJE)-SUM(JFJE) From Cwzz_BankBill Where (RecType=0) And Ccode='" & Val(Combo1.Text) & "'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        YHYE = RecTemp(0)
    End If
    SQL_Str = "SELECT SUM(DFJE)-SUM(JFJE) From Cwzz_BankBill Where (RecType=2) And Ccode='" & Val(Combo1.Text) & "'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        YHYE = YHYE + RecTemp(0)
    End If
    '银行调整后余额=银行帐面余额+（企业己收银行未收-企业己付银行未付）
    SQL_Str = "SELECT SUM(JFJE)-SUM(DFJE) From Cwzz_RjzNotCheck Where RecType=1 And BDelete=0 And Ccode='" & Val(Combo1.Text) & "'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        YHYE = YHYE + RecTemp(0)
    End If
    SQL_Str = "Select SUM(JFJE)-SUM(DFJE) From Cwzz_V_AccVouch where Ccode='" & Val(Combo1.Text) & "' " _
    & "And Convert(Char(8),Ddate,112)>=(Select Convert(Char(8),Ddate,112) From " _
    & "Cwzz_RjzNotCheck Where RecType=0 And Ccode='" & Val(Combo1.Text) & "') And BDelete=0 " _
    & "And Convert(Char(6),Ddate,112)<=(select Convert(Char(6),max(qsrq),112) from Gy_kjrlb where cwzzjzbz='1')"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        YHYE = YHYE + RecTemp(0)
    End If
    '单位调整后余额=银行帐面余额+（银行己收企业未收-银行己付企业未付）
    SQL_Str = "SELECT SUM(JFJE)-SUM(DFJE) From Cwzz_BankBill Where RecType=1 And BDelete=0 And Ccode='" & Val(Combo1.Text) & "'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        DWYE = DWYE + RecTemp(0)
    End If
    SQL_Str = "SELECT SUM(DFJE)-SUM(JFJE) From Cwzz_BankBill Where RecType=2 And BDelete=0 And Ccode='" & Val(Combo1.Text) & "'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        DWYE = DWYE + RecTemp(0)
    End If
    If DWYE <> YHYE Then
        MsgBox "《" + Combo1.Text + "》帐户调整余额不平，不能进行核销", 16, "百利/ERP5.0-财务总帐"
        Exit Sub
    End If
    '判断己勾对的帐是否平衡
    DWJF = 0
    DWDF = 0
    YHJF = 0
    YHDF = 0
    '银行对帐单
    SQL_Str = " Select sum(jfje),sum(dfje) From Cwzz_BankBill Where bcheckflag=1 And BDelete='0' And RecType='1' "
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        YHDF = RecTemp(0)
    End If
    If Not IsNull(RecTemp(1)) Then
        YHJF = RecTemp(1)
    End If
    
    SQL_Str = " Select sum(jfje),sum(dfje) From Cwzz_BankBill Where bcheckflag=1 And BDelete='0' And RecType='2'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        YHJF = YHJF + RecTemp(0)
    End If
    If Not IsNull(RecTemp(1)) Then
        YHDF = YHDF + RecTemp(1)
    End If
    '单位日记帐
    SQL_Str = " Select sum(jfje),sum(dfje) From cwzz_accvouchSub Where bcheckflag=1 And BDelete='0'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        DWJF = RecTemp(0)
    End If
    If Not IsNull(RecTemp(1)) Then
        DWDF = RecTemp(1)
    End If
    
    SQL_Str = " Select sum(jfje),sum(dfje) From Cwzz_RjzNotCheck Where bcheckflag=1 And BDelete='0'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not IsNull(RecTemp(0)) Then
        DWJF = DWJF + RecTemp(0)
    End If
    If Not IsNull(RecTemp(1)) Then
        DWDF = DWDF + RecTemp(1)
    End If
    
    If DWJF = YHDF And DWDF = YHJF Then
    Else
        If DWDF = DWJF Then
        Else
            MsgBox "银行自动对帐数据不平衡，不能进行核销。", 16, "百利/ERP5.0-财务总帐"
            Exit Sub
        End If
    End If
    '判断是否有可核销的数据
    Sqlstr = "Select Count(*) From Cwzz_BankBill Where BCheckFlag='1' And BDelete='0'"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
    If RecTemp.Fields(0) <= 0 Then
        MsgBox "没有可核销的数据!", vbOKOnly + 64, "百利/ERP5.0-财务总帐"
        Exit Sub
    End If
    On Error GoTo Err:
    Cw_DataEnvi.DataConnect.BeginTrans
    Msg = MsgBox("你要核销银行已经对帐的数据吗?", vbYesNo + 32, "百利/ERP5.0-财务总帐")
    If Msg = vbYes Then
        '核销银行帐
        If RecTemp.State = 1 Then RecTemp.Close
        RecTemp.Open "select * from cwzz_bankbill where bcheckflag=1", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
        Do While Not RecTemp.EOF()
            RecTemp.Fields("bdelete") = 1
            RecTemp.Update
            RecTemp.MoveNext
        Loop
        '核销日记帐
        If RecTemp.State = 1 Then RecTemp.Close
        RecTemp.Open "select * from cwzz_accvouchSub where bcheckflag=1", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
        Do While Not RecTemp.EOF()
            RecTemp.Fields("bdelete") = 1
            RecTemp.Update
            RecTemp.MoveNext
        Loop
        '核销单位未达帐
        If RecTemp.State = 1 Then RecTemp.Close
        RecTemp.Open "select * from Cwzz_RjzNotCheck where bcheckflag=1", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
        Do While Not RecTemp.EOF()
            RecTemp.Fields("bdelete") = 1
            RecTemp.Update
            RecTemp.MoveNext
        Loop
        '核销结算方式相同，票号相同，金额相反的数据
        Sqlstr = "Select * From CWZZ_V_BILL"
        Set RecDir = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
        Do While Not RecDir.EOF()
            If RecTemp.State = 1 Then RecTemp.Close
            Sqlstr = "Select * From Cwzz_Accvouchsub Where SerialID=" + CStr(RecDir.Fields("SerialID")) + " Or SerialID=" + CStr(RecDir.Fields("B_Id")) + ""
            RecTemp.Open Sqlstr, Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            Do While Not RecTemp.EOF
                RecTemp.Fields("BCheckFlag") = 1
                RecTemp.Fields("bdelete") = 1
                RecTemp.Update
                RecTemp.MoveNext
            Loop
            RecDir.MoveNext
        Loop
        MsgBox "核销完毕!", 64, "百利/ERP5.0-财务总帐"
    End If
    Cw_DataEnvi.DataConnect.CommitTrans
    Exit Sub
Err:
    Cw_DataEnvi.DataConnect.RollbackTrans
End Sub

'退出
Private Sub Command2_Click()
    Unload Me
End Sub

'显示银行科目
Private Sub Sub_ShowBill()
    Dim RecTemp As New ADODB.Recordset
    SQL_Str = "select * from cwzz_acccode where cproperty='银行' and endflag=1 order by ccode"
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(SQL_Str)
    If Not RecTemp.EOF Then
    Combo1.Text = Trim(RecTemp.Fields("ccode")) + Space(2) + Trim(RecTemp.Fields("cname"))
    Do While Not RecTemp.EOF()
        Combo1.AddItem Trim(RecTemp.Fields("ccode")) + Space(2) + Trim(RecTemp.Fields("cname"))
        RecTemp.MoveNext
    Loop
    End If
End Sub

'调入窗体
Private Sub Form_Load()
    Call Sub_ShowBill
End Sub