+

VB开发的ERP系统

    Dbl_FzQcye = Dbl_Qcye(1) + Dbl_Qcye(2) + Dbl_Qcye(4)
    
    If Dbl_ZcQcye > 0 Then
        Lab_Qcyesj(5).Caption = "借" & " " & Format(Dbl_ZcQcye, "##." + String(Xtjexsws, "0"))
    Else
        If Dbl_ZcQcye = 0 Then
            Lab_Qcyesj(5).Caption = "平"
        Else
            Lab_Qcyesj(5).Caption = "贷" & " " & Format(-Dbl_ZcQcye, "##." + String(Xtjexsws, "0"))
        End If
    End If
    
    If Dbl_FzQcye > 0 Then
        Lab_Qcyesj(6).Caption = "借" & " " & Format(Dbl_FzQcye, "##." + String(Xtjexsws, "0"))
    Else
        If Dbl_FzQcye = 0 Then
            Lab_Qcyesj(6).Caption = "平"
        Else
            Lab_Qcyesj(6).Caption = "贷" & " " & Format(-Dbl_FzQcye, "##." + String(Xtjexsws, "0"))
        End If
    End If
    
    '判断余额是否平衡
    If Format(Dbl_ZcQcye + Dbl_FzQcye, "0.00") = 0 Then
        Lab_Phjg.ForeColor = vbBlue
        Lab_Phjg.Caption = "试算结果:年初余额平衡"
        
        Int_SS_OK = 1
    Else
        Lab_Phjg.ForeColor = vbRed
        Lab_Phjg.Caption = "试算结果:年初余额不平衡"
        
        Int_SS_OK = 0
    End If
    
    '计算科目借贷方累计数据
    Sqlstr = "SELECT isnull(SUM(Cwzz_AccSum.Byjfljje),0) as Ljjfje,isnull(SUM(Cwzz_AccSum.Bydfljje),0) As Ljdfje FROM Cwzz_AccSum " & _
    " LEFT OUTER JOIN Cwzz_AccCode ON Cwzz_AccSum.Ccode = Cwzz_AccCode.Ccode" & _
    " Where Cwzz_AccCode.EndFlag=1 And Year=" & Int_Year & " And Period=" & Int_Period
    Set RecTemp = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
    
    Dbl_Jflj = RecTemp.Fields("Ljjfje")
    Dbl_Dflj = RecTemp.Fields("Ljdfje")
    
    Lab_Jflj.Caption = "借方累计:" & " " & Format(Dbl_Jflj, "##." + String(Xtjexsws, "0"))
    Lab_Dflj.Caption = "贷方累计:" & " " & Format(Dbl_Dflj, "##." + String(Xtjexsws, "0"))
    
    '判断借贷是否平衡
    If Abs(Dbl_Jflj - Dbl_Dflj) >= 0.01 Then
        Lab_ljjg.ForeColor = vbRed
        Lab_ljjg.Caption = "试算结果:借贷不平衡"
        
        Int_SS_OK = 0
    Else
        Lab_ljjg.ForeColor = vbBlue
        Lab_ljjg.Caption = "试算结果:借贷平衡"
        
        Int_SS_OK = 1
    End If
End Sub

Private Sub cmdExecute_Click()                               '总帐初始化完成
    Dim RecTemp As New ADODB.Recordset              '临时使用动态集
    Dim Rec_AccSum As New ADODB.Recordset           '总帐动态集
    Dim Rec_AccSumAssi As New ADODB.Recordset       '辅助帐动态集
    Dim Rec_AccVouchMain As New ADODB.Recordset     '凭证主表动态集
    Dim Rec_AccVouch As New ADODB.Recordset         '凭证子表动态集
    Dim Dtm_DDate As Date                           '凭证制单日期
    Dim Str_VouchClassCode As String                '凭证类别编码
    Dim Rowjsq As Long                              '凭证记录计数器
    Dim Lng_VouchId As Long                         '凭证ID
    
    '判断当前年度是否为第一次建帐年度,如非第一次建帐年度则不生成凭证
    Set Ztxxrec = Cw_DataEnvi.DataConnect.Execute("Select top 1 * From Gy_kjrlb Order by kjyear,period")
    
    If Ztxxrec.Fields("Kjyear") = Int_Year Then
        
        '读取凭证制单日期
        Set RecTemp = Cw_DataEnvi.DataConnect.Execute("Select Qsrq From Gy_kjrlb Where Period=1 And KjYear='" & Int_Year & "'")
        If Not RecTemp.EOF Then
            Dtm_DDate = RecTemp.Fields("Qsrq")
        End If
        
        '读取凭证类别
        Set RecTemp = Cw_DataEnvi.DataConnect.Execute("Select Top 1 VouchClassCode From Cwzz_VouchClass ")
        If Not RecTemp.EOF Then
            Str_VouchClassCode = RecTemp.Fields("VouchClassCode")
        Else
            Tsxx = "请先设置凭证类别!"
            Call Xtxxts(Tsxx, 0, 4)
            Exit Sub
        End If
        
         '显示等候信息
        Lab_Wait.Visible = True
        
        '从总帐中读取所有 末级\没有辅助核算\初始化会计年度 1会计期间\各累计发生数有一个不为零的科目数据生成凭证的一部分.
        Sqlstr = "SELECT Cwzz_AccSum.*,Cwzz_AccCode.ForeignCurrCode,Gy_ForeignCurrency.AccRate FROM Cwzz_AccSum Left JOIN Cwzz_AccCode ON " & _
        " Cwzz_AccSum.Ccode = Cwzz_AccCode.Ccode Left JOIN Gy_ForeignCurrency ON " & _
        " Cwzz_AccCode.ForeignCurrCode = Gy_ForeignCurrency.ForeignCurrCode WHERE " & _
        " (Cwzz_AccCode.EndFlag = 1 And Cwzz_AccCode.PersonFlag = 0 AND Cwzz_AccCode.CusFlag = 0 AND Cwzz_AccCode.SupplierFlag = 0 AND Cwzz_AccCode.DeptFlag = 0 AND Cwzz_AccCode.ItemFlag = 0) " & _
        "  AND (Cwzz_AccSum.Year =" & Int_Year & " AND Cwzz_AccSum.Period = 1) AND " & _
        " (Cwzz_AccSum.Byjfljje <> 0 OR Cwzz_AccSum.Bydfljje <> 0 OR Cwzz_AccSum.Byjfljsl <> 0 OR Cwzz_AccSum.Bydfljsl <> 0 OR Cwzz_AccSum.Byjfljwb <> 0 OR Cwzz_AccSum.Bydfljwb <> 0) " & _
        " Order By Cwzz_AccSum.Ccode"
        Set Rec_AccSum = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
        
        '从总帐辅助帐中读取所有 初始化会计年度 1会计期间\各累计发生数有一个不为零的科目数据生成凭证的一部分.
        Sqlstr = "SELECT Cwzz_AccSumAssi.*,Cwzz_AccCode.ForeignCurrCode,Gy_ForeignCurrency.AccRate FROM Cwzz_AccSumAssi Left JOIN Cwzz_AccCode ON " & _
        " Cwzz_AccSumAssi.Ccode = Cwzz_AccCode.Ccode Left JOIN Gy_ForeignCurrency ON " & _
        " Cwzz_AccCode.ForeignCurrCode = Gy_ForeignCurrency.ForeignCurrCode WHERE " & _
        "  (Cwzz_AccSumAssi.Year =" & Int_Year & " AND Cwzz_AccSumAssi.Period = 1) AND " & _
        " (Cwzz_AccSumAssi.Byjfljje <> 0 OR Cwzz_AccSumAssi.Bydfljje <> 0 OR Cwzz_AccSumAssi.Byjfljsl <> 0 OR Cwzz_AccSumAssi.Bydfljsl <> 0 OR Cwzz_AccSumAssi.Byjfljwb <> 0 OR Cwzz_AccSumAssi.Bydfljwb <> 0 OR " & _
        "  Cwzz_AccSumAssi.ItemByljjfsl <> 0 OR Cwzz_AccSumAssi.ItemByljdfsl <> 0) " & _
        " Order By Cwzz_AccSumAssi.Ccode"
        Set Rec_AccSumAssi = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
        
        On Error GoTo Swcwcl
        
        Cw_DataEnvi.DataConnect.BeginTrans
        
        '如果动态集不为空则生成凭证
        If (Not Rec_AccSum.EOF) And (Not Rec_AccSumAssi.EOF) Then
            
            '生成凭证主表
            If Rec_AccVouchMain.State = 1 Then Rec_AccVouchMain.Close
            Rec_AccVouchMain.Open "Select * From Cwzz_AccVouchMain Where 1=2", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            
            With Rec_AccVouchMain
                .AddNew
                .Fields("VouchID") = CreatBillID("0101")
                .Fields("Year") = Int_Year                                                                   '会计年度
                .Fields("Period") = Int_Period                                                               '会计期间
                .Fields("Ddate") = Dtm_DDate                                                                 '制单日期
                .Fields("VouchClassCode") = Str_VouchClassCode                                               '凭证类别
                .Fields("VouchNo") = 1                                                                       '凭证号
                .Fields("Doc") = 0                                                                           '附单据数
                .Fields("Bill") = Xtczy                                                                      '制单人
                .Fields("CheckFlag") = 1                                                                     '审核标志置"1"
                .Fields("BookFlag") = 1                                                                      '记帐标志置"1"
                .Update
                
                Lng_VouchId = .Fields("VouchID")
                
            End With
            
            '生成凭证子表
            Rowjsq = 0
            
            If Rec_AccVouch.State = 1 Then Rec_AccVouch.Close
            Rec_AccVouch.Open "Select * From Cwzz_AccVouchSub Where 1=2", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
            
            '1.总帐生成凭证
            Do While Not Rec_AccSum.EOF
                
                '显示当前生成凭证会计科目
                Lab_ShowCode.Caption = Trim(Rec_AccSum.Fields("Ccode"))
                Lab_ShowCode.Refresh
                
                With Rec_AccVouch
                    
                    If Rec_AccSum.Fields("Byjfljje") <> 0 Or Rec_AccSum.Fields("Byjfljsl") <> 0 Or Rec_AccSum.Fields("Byjfljwb") <> 0 Then
                        
                        Rowjsq = Rowjsq + 1
                        .AddNew
                        .Fields("SerialID") = Rowjsq
                        .Fields("VouchID") = Lng_VouchId                                                             '凭证ID
                        .Fields("Digest") = "期初录入"                                                               '摘要
                        .Fields("Ccode") = Trim(Rec_AccSum.Fields("Ccode"))                                          '会计科目
                        .Fields("Jfje") = Rec_AccSum.Fields("Byjfljje") + 0                                          '借方金额
                        .Fields("Jfsl") = Rec_AccSum.Fields("Byjfljsl") + 0                                          '借方数量
                        If Len(Trim(Rec_AccSum.Fields("ForeignCurrCode") & "")) = 0 Then
                            .Fields("ForeignCurrCode") = XtSCurrCode                                                 '原币编码
                            .Fields("AccRate") = 1                                                                   '记帐汇率
                            .Fields("Wbjfje") = Rec_AccSum.Fields("Byjfljje") + 0                                    '外币借方金额
                        Else
                            .Fields("ForeignCurrCode") = Rec_AccSum.Fields("ForeignCurrCode")                        '原币编码
                            .Fields("AccRate") = Rec_AccSum.Fields("AccRate") + 0                                    '记帐汇率
                            .Fields("Wbjfje") = Rec_AccSum.Fields("Byjfljwb") + 0                                    '外币借方金额
                        End If
                        .Update
                    End If
                    
                    If Rec_AccSum.Fields("Bydfljje") <> 0 Or Rec_AccSum.Fields("Bydfljsl") <> 0 Or Rec_AccSum.Fields("Bydfljwb") <> 0 Then
                        
                        Rowjsq = Rowjsq + 1
                        .AddNew
                        .Fields("SerialID") = Rowjsq
                        .Fields("VouchID") = Lng_VouchId                                                             '凭证ID
                        .Fields("Digest") = "期初录入"                                                               '摘要
                        .Fields("Ccode") = Trim(Rec_AccSum.Fields("Ccode"))                                          '会计科目
                        .Fields("Dfje") = Rec_AccSum.Fields("Bydfljje") + 0                                          '贷方金额
                        .Fields("Dfsl") = Rec_AccSum.Fields("Bydfljsl") + 0                                          '贷方数量
                        If Len(Trim(Rec_AccSum.Fields("ForeignCurrCode") & "")) = 0 Then
                            .Fields("ForeignCurrCode") = XtSCurrCode                                                 '原币编码
                            .Fields("AccRate") = 1                                                                   '记帐汇率
                            .Fields("WbDfje") = Rec_AccSum.Fields("Bydfljje") + 0                                    '外币贷方金额
                        Else
                            .Fields("ForeignCurrCode") = Rec_AccSum.Fields("ForeignCurrCode")                        '原币编码
                            .Fields("AccRate") = Rec_AccSum.Fields("AccRate") + 0                                    '记帐汇率
                            .Fields("WbDfje") = Rec_AccSum.Fields("Bydfljwb") + 0                                    '外币贷方金额
                        End If
                        .Update
                    End If
                    
                End With
                
                Rec_AccSum.MoveNext
            Loop
            
            '1.辅助帐生成凭证
            Do While Not Rec_AccSumAssi.EOF
                
                '显示当前生成凭证会计科目
                Lab_ShowCode.Caption = Trim(Rec_AccSumAssi.Fields("Ccode"))
                Lab_ShowCode.Refresh
                
                With Rec_AccVouch
                    
                    If Rec_AccSumAssi.Fields("Byjfljje") <> 0 Or Rec_AccSumAssi.Fields("Byjfljsl") <> 0 Or Rec_AccSumAssi.Fields("Byjfljwb") <> 0 Then
                        
                        Rowjsq = Rowjsq + 1
                        .AddNew
                        .Fields("SerialID") = Rowjsq
                        .Fields("VouchID") = Lng_VouchId                                                             '凭证ID
                        .Fields("Digest") = "期初录入"                                                               '摘要
                        .Fields("Ccode") = Trim(Rec_AccSumAssi.Fields("Ccode"))                                      '会计科目
                        .Fields("Jfje") = Rec_AccSumAssi.Fields("Byjfljje") + 0                                      '借方金额
                        .Fields("Jfsl") = Rec_AccSumAssi.Fields("Byjfljsl") + 0                                      '借方数量
                        If Len(Trim(Rec_AccSumAssi.Fields("ForeignCurrCode") & "")) = 0 Then
                            .Fields("ForeignCurrCode") = XtSCurrCode                                                 '原币编码
                            .Fields("AccRate") = 1                                                                   '记帐汇率
                            .Fields("Wbjfje") = Rec_AccSumAssi.Fields("Byjfljje") + 0                                '外币借方金额
                        Else
                            .Fields("ForeignCurrCode") = Rec_AccSumAssi.Fields("ForeignCurrCode")                    '原币编码
                            .Fields("AccRate") = Rec_AccSumAssi.Fields("AccRate") + 0                                '记帐汇率
                            .Fields("Wbjfje") = Rec_AccSumAssi.Fields("Byjfljwb") + 0                                '外币借方金额
                        End If
                        
                        .Fields("PersonCode") = Trim(Rec_AccSumAssi.Fields("PersonCode") & "")                         '职员编码
                        
                        .Fields("DeptCode") = Trim(Rec_AccSumAssi.Fields("DeptCode") & "")                             '部门编码
                        
                        .Fields("CusCode") = Trim(Rec_AccSumAssi.Fields("CusCode") & "")                               '往来客户编码
                        
                        .Fields("SupplierCode") = Trim(Rec_AccSumAssi.Fields("SupplierCode") & "")                     '往来供应商编码
                        
                        .Fields("ItemClassCode") = Trim(Rec_AccSumAssi.Fields("ItemClassCode") & "")                   '项目大类编码
                        
                        .Fields("ItemCode") = Trim(Rec_AccSumAssi.Fields("ItemCode") & "")                             '项目编码
                        
                        .Fields("ItemJfsl") = Rec_AccSumAssi.Fields("ItemByljjfsl") + 0                                '项目借方数量
                        
                        .Update
                    End If
                    
                    If Rec_AccSumAssi.Fields("Bydfljje") <> 0 Or Rec_AccSumAssi.Fields("Bydfljsl") <> 0 Or Rec_AccSumAssi.Fields("Bydfljwb") <> 0 Then
                        
                        Rowjsq = Rowjsq + 1
                        .AddNew
                        .Fields("SerialID") = Rowjsq
                        .Fields("VouchID") = Lng_VouchId                                                             '凭证ID
                        .Fields("Digest") = "期初录入"                                                               '摘要
                        .Fields("Ccode") = Trim(Rec_AccSumAssi.Fields("Ccode"))                                          '会计科目
                        .Fields("Dfje") = Rec_AccSumAssi.Fields("Bydfljje") + 0                                          '贷方金额
                        .Fields("Dfsl") = Rec_AccSumAssi.Fields("Bydfljsl") + 0                                          '贷方数量
                        If Len(Trim(Rec_AccSumAssi.Fields("ForeignCurrCode") & "")) = 0 Then
                            .Fields("ForeignCurrCode") = XtSCurrCode                                                 '原币编码
                            .Fields("AccRate") = 1                                                                   '记帐汇率
                            .Fields("WbDfje") = Rec_AccSumAssi.Fields("Bydfljje") + 0                                    '外币贷方金额
                        Else
                            .Fields("ForeignCurrCode") = Rec_AccSumAssi.Fields("ForeignCurrCode")                        '原币编码
                            .Fields("AccRate") = Rec_AccSumAssi.Fields("AccRate") + 0                                    '记帐汇率
                            .Fields("WbDfje") = Rec_AccSumAssi.Fields("Bydfljwb") + 0                                    '外币贷方金额
                        End If
                        
                        .Fields("PersonCode") = Trim(Rec_AccSumAssi.Fields("PersonCode") & "")                       '职员编码
                        
                        .Fields("DeptCode") = Trim(Rec_AccSumAssi.Fields("DeptCode") & "")                             '部门编码
                        
                        .Fields("CusCode") = Trim(Rec_AccSumAssi.Fields("CusCode") & "")                               '往来客户编码
                        
                        .Fields("SupplierCode") = Trim(Rec_AccSumAssi.Fields("SupplierCode") & "")                     '往来供应商编码
                        
                        .Fields("ItemClassCode") = Trim(Rec_AccSumAssi.Fields("ItemClassCode") & "")                   '项目大类编码
                        
                        .Fields("ItemCode") = Trim(Rec_AccSumAssi.Fields("ItemCode") & "")                             '项目编码
                        
                        .Fields("ItemDfsl") = Rec_AccSumAssi.Fields("ItemByljdfsl") + 0                                '项目借方数量
                        
                        .Update
                    End If