accassi.cls

VB开发的ERP系统

    strTemSql = strTemSql & "Bydfljwb as BydfljwbHj," ' /*本月贷方累计外币合计*/
    strTemSql = strTemSql & "Byjfljsl as ByjfljslHj," ' /*本月借方累计数量合计*/
    strTemSql = strTemSql & "Bydfljsl as BydfljslHj," ' /*本月贷方累计数量合计*/
    
    strTemSql = strTemSql & "Mjje as Mjjehj," ' /* 本期借方发生额*/
    strTemSql = strTemSql & "Mjsl as Mjslhj," ' /*本期借方数量发生额*/
    strTemSql = strTemSql & "Mjwb as Mjwbhj," ' /*本期借方外币发生额*/
    strTemSql = strTemSql & "Mdje as Mdjehj," ' /* 本期贷方发生额*/
    strTemSql = strTemSql & "Mdsl as Mdslhj," ' /*本期贷方数量发生额*/
    strTemSql = strTemSql & "Mdwb as Mdwbhj " ' /*本期贷方外币发生额*/
    
    strTemSql = strTemSql & " From "
    strTemSql = strTemSql & " Cwzz_AccCode LEFT OUTER JOIN  Cwzz_AccSum ON "
    strTemSql = strTemSql & " Cwzz_AccCode.Ccode =Cwzz_AccSum.Ccode "
    strTemSql = strTemSql & " Where "
    If CodeList.count > 0 Then
        For i = FIRST_MEMBER To CodeList.count - 1
            strTemSql = strTemSql & " Cwzz_AccCode.cCode='" & CodeList.Item(i) & "' OR "
        Next
        
        strTemSql = strTemSql & " Cwzz_AccCode.cCode='" & CodeList.Item(i) & "'" '这样做是为了去除最后的“OR”
    Else
        strTemSql = strTemSql & "1=2"
    End If
    With PayRs
        .ActiveConnection = Cw_DataEnvi.DataConnect
        .Source = strTemSql
        .Open , , adOpenStatic, adLockBatchOptimistic
        Set .ActiveConnection = Nothing
        
        '删除多余的记录
        Do Until .EOF
            If !Period <> 1 Then
                .Delete
            Else
                '--------清空记录----------------
                !QcyeHj = 0
                !QcslHj = 0
                !QcwbHj = 0
                
                !Mjjehj = 0
                !Mdjehj = 0
                !Mjslhj = 0
                !Mdslhj = 0
                !Mjwbhj = 0
                !Mdwbhj = 0
                
                !ByjfljjeHj = 0
                !BydfljjeHj = 0
                !ByjfljwbHj = 0
                !BydfljwbHj = 0
                !ByjfljslHj = 0
                !BydfljslHj = 0
            End If
            '------------------------
            .MoveNext
        Loop
        If Not (.EOF And .BOF) Then
            .MoveFirst
        End If
    End With
End Sub

Private Sub GetVouchRs() '取末记帐凭证生成相加记录集
    '此过程根据“标志”来生成SQL语句
    If DEBUG_FLAG = False Then On Error Resume Next
    Dim strTemSql As String
    Dim i As Integer
    strTemSql = ""
    strTemSql = strTemSql & "SELECT cCode,"
    Select Case AccType  '此为全局变量
    Case QI_CU_YU_E '期初
        
        strTemSql = strTemSql & " (Sum(Jfje)-Sum(Dfje)) as QcyeHj,"
        strTemSql = strTemSql & " (Sum(Jfsl)-Sum(Dfsl)) as QcslHj,"
        strTemSql = strTemSql & " (Sum(wbJfje)-Sum(wbDfje)) as QcwbHj"
        strTemSql = strTemSql & " FROM Cwzz_AccVouchSub,Cwzz_AccVouchMain"
        strTemSql = strTemSql & " WHERE "
        strTemSql = strTemSql & " Cwzz_AccVouchMain.BookFlag=0 "
        strTemSql = strTemSql & " And Cwzz_AccVouchMain.VouchId=Cwzz_AccVouchSub.VouchId "
        strTemSql = strTemSql & " And Period<" & iPeriod_Begin
        strTemSql = strTemSql & " And Cwzz_AccVouchSub." & VouchCodeFeildName & " like '" & sPayCode & "%'"
        strTemSql = strTemSql & " group by cCode"
        strTemSql = strTemSql & ""
        
    Case BEN_QI_FA_SHENG_E      '本期发生
        strTemSql = strTemSql & "Sum(jfje) as Mjjehj," ' /* 本期借方发生额*/
        strTemSql = strTemSql & "Sum(jfsl) as Mjslhj," ' /*本期借方数量发生额*/
        strTemSql = strTemSql & "Sum(wbjfje) as Mjwbhj," ' /*本期借方外币发生额*/
        strTemSql = strTemSql & "Sum(dfje) as Mdjehj," ' /* 本期贷方发生额*/
        strTemSql = strTemSql & "Sum(dfsl) as Mdslhj," ' /*本期贷方数量发生额*/
        strTemSql = strTemSql & "Sum(wbdfje) as Mdwbhj " ' /*本期贷方外币发生额*/
        strTemSql = strTemSql & " FROM Cwzz_AccVouchSub,Cwzz_AccVouchMain"
        strTemSql = strTemSql & " WHERE "
        strTemSql = strTemSql & " Cwzz_AccVouchSub.VouchId=Cwzz_AccVouchMain.VouchId "
        strTemSql = strTemSql & " AND Cwzz_AccVouchMain.Period BETWEEN " & iPeriod_Begin & " AND " & iPeriod_End
        strTemSql = strTemSql & " AND Cwzz_AccVouchMain.Year=" & iPeriod_Year
        strTemSql = strTemSql & " AND " & VouchCodeFeildName & " like '" & sPayCode & "%'"
        strTemSql = strTemSql & " AND Cwzz_AccVouchMain.BookFlag=0 AND ("
        
        
    Case LEI_JI_FA_SHENG_E      '累计发生
        strTemSql = strTemSql & "Sum(jfje) as ByjfljjeHj," ' /*本月借方累计金额合计*/
        strTemSql = strTemSql & "Sum(dfje) as BydfljjeHj," ' /*本月贷方累计金额合计*/
        strTemSql = strTemSql & "Sum(jfsl) as ByjfljwbHj," ' /*本月借方累计外币合计*/
        strTemSql = strTemSql & "Sum(dfsl) as BydfljwbHj," ' /*本月贷方累计外币合计*/
        strTemSql = strTemSql & "Sum(wbjfje) as ByjfljslHj," ' /*本月借方累计数量合计*/
        strTemSql = strTemSql & "Sum(wbdfje) as BydfljslHj" ' /*本月贷方累计数量合计*/
        strTemSql = strTemSql & " FROM Cwzz_AccVouchSub,Cwzz_AccVouchMain"
        strTemSql = strTemSql & " WHERE "
        strTemSql = strTemSql & " Cwzz_AccVouchSub.VouchId=Cwzz_AccVouchMain.VouchId "
        '-----------------2001年7月26日 11：15 分修改 （bsj）--------------------------
        '问题：累计发生应为“查询期期末”以前的所有末记帐凭证，即小于“查询期期末”所有凭证
        '原程序为：“查询期期初”——“查询期期末”的所有凭证
        '此行为原程序：strTemSql = strTemSql & " AND Cwzz_AccVouchMain.Period BETWEEN " & iPeriod_Begin & " AND " & iPeriod_End
        '下一行为修改后程序：strTemSql = strTemSql & " AND Cwzz_AccVouchMain.Period <" & iPeriod_End
        strTemSql = strTemSql & " AND Cwzz_AccVouchMain.Period <" & iPeriod_End
        '---------------------修改结束-----------------------------------------
        strTemSql = strTemSql & " AND Cwzz_AccVouchMain.Year=" & iPeriod_Year
        strTemSql = strTemSql & " AND " & VouchCodeFeildName & " like '" & sPayCode & "%'"
        strTemSql = strTemSql & " AND Cwzz_AccVouchMain.BookFlag=0 AND ("
    End Select
    
    If AccType <> QiCu Then
        If CodeList.count > 0 Then
            For i = FIRST_MEMBER To CodeList.count - 1
                strTemSql = strTemSql & " Cwzz_AccVouchSub.cCode='" & CodeList.Item(i) & "' OR "
            Next
            strTemSql = strTemSql & " Cwzz_AccVouchSub.cCode='" & CodeList.Item(i) & "')" '这样做是为了去除最后的“OR”
        Else
            strTemSql = strTemSql & "1=2)"
        End If
        strTemSql = strTemSql & " Group By cCode"
    End If
    '---------------------------------------
    
    With AddRs
        If .State <> adStateClosed Then .Close
        .ActiveConnection = Cw_DataEnvi.DataConnect
        .Source = strTemSql
        .Open , , adOpenStatic, adLockBatchOptimistic
        Set .ActiveConnection = Nothing
    End With
    
End Sub

Private Sub GetSumAssi() '生成期初、本期发生、累计发生数据
    If DEBUG_FLAG = False Then On Error Resume Next
    Dim strTemSql As String
    Dim i As Integer
    strTemSql = ""
    strTemSql = "SELECT  Cwzz_AccSumAssi.cCode," '    /* 科目代码 */
    
    Select Case AccType '此变量为全局变量
    Case QI_CU_YU_E '期初
        strTemSql = strTemSql & "Qcye as QcyeHj," ' /* 期初余额*/
        strTemSql = strTemSql & "Qcsl as QcslHj," '/*期初数量*/
        strTemSql = strTemSql & "Qcwb as QcwbHj," '/*期初外币*/
        
        
        If Right(strTemSql, 1) = "," Then strTemSql = Left(strTemSql, Len(strTemSql) - 1)
        strTemSql = strTemSql & " From "
        strTemSql = strTemSql & " Cwzz_AccSumAssi "
        strTemSql = strTemSql & " Where "
        strTemSql = strTemSql & " Period = " & iPeriod_Begin & " AND Year=" & iPeriod_Year & " AND "
        strTemSql = strTemSql & AssiCodeFeildName & " like '" & sPayCode & "%' AND ("
        
    Case LEI_JI_FA_SHENG_E     '  累计发生
        strTemSql = strTemSql & "Byjfljje as ByjfljjeHj," ' /*本月借方累计金额合计*/
        strTemSql = strTemSql & "Bydfljje as BydfljjeHj," ' /*本月贷方累计金额合计*/
        strTemSql = strTemSql & "Byjfljwb as ByjfljwbHj," ' /*本月借方累计外币合计*/
        strTemSql = strTemSql & "Bydfljwb as BydfljwbHj," ' /*本月贷方累计外币合计*/
        strTemSql = strTemSql & "Byjfljsl as ByjfljslHj," ' /*本月借方累计数量合计*/
        strTemSql = strTemSql & "Bydfljsl as BydfljslHj," ' /*本月贷方累计数量合计*/
        
        If Right(strTemSql, 1) = "," Then strTemSql = Left(strTemSql, Len(strTemSql) - 1)
        strTemSql = strTemSql & " From "
        strTemSql = strTemSql & " Cwzz_AccSumAssi "
        strTemSql = strTemSql & " Where "
        strTemSql = strTemSql & " Period < " & iPeriod_End & " AND Year=" & iPeriod_Year & " AND "
        strTemSql = strTemSql & AssiCodeFeildName & " like '" & sPayCode & "%' AND ("
        
        
    Case BEN_QI_FA_SHENG_E  '本期发生
        strTemSql = strTemSql & "Sum(Mjje) as Mjjehj," ' /* 本期借方发生额*/
        strTemSql = strTemSql & "Sum(Mjsl) as Mjslhj," ' /*本期借方数量发生额*/
        strTemSql = strTemSql & "Sum(Mjwb) as Mjwbhj," ' /*本期借方外币发生额*/
        strTemSql = strTemSql & "Sum(Mdje) as Mdjehj," ' /* 本期贷方发生额*/
        strTemSql = strTemSql & "Sum(Mdsl) as Mdslhj," ' /*本期贷方数量发生额*/
        strTemSql = strTemSql & "Sum(Mdwb) as Mdwbhj " ' /*本期贷方外币发生额*/
        
        
        If Right(strTemSql, 1) = "," Then strTemSql = Left(strTemSql, Len(strTemSql) - 1)
        strTemSql = strTemSql & " From "
        strTemSql = strTemSql & " Cwzz_AccSumAssi "
        strTemSql = strTemSql & " Where "
        strTemSql = strTemSql & " Period BETWEEN " & iPeriod_Begin & " AND " & iPeriod_End & " AND Year=" & iPeriod_Year & " AND "
        strTemSql = strTemSql & AssiCodeFeildName & " like '" & sPayCode & "%' AND ("
        
    End Select
    
    If CodeList.count > 0 Then
        For i = FIRST_MEMBER To CodeList.count - 1
            strTemSql = strTemSql & " Cwzz_AccSumAssi.cCode='" & CodeList.Item(i) & "' OR "
        Next
        strTemSql = strTemSql & " Cwzz_AccSumAssi.cCode='" & CodeList.Item(i) & "')" '这样做是为了去除最后的“OR”
    Else
        strTemSql = strTemSql & "1=2)"
    End If
    If AccType = BenQi Then
        strTemSql = strTemSql & " Group By cCode"
    End If
    With AddRs
        If .State <> adStateClosed Then .Close
        .ActiveConnection = Cw_DataEnvi.DataConnect
        .Source = strTemSql
        .Open , , adOpenStatic, adLockBatchOptimistic
        Set .ActiveConnection = Nothing
    End With
    
End Sub

Private Sub AddSingle() '相加已取得的两个记录集
    If DEBUG_FLAG = False Then On Error Resume Next
    Select Case AccType
    Case QI_CU_YU_E '期初
        PayRs!QcyeHj = PayRs!QcyeHj + IIf(IsNull(AddRs!QcyeHj), 0, AddRs!QcyeHj)
        PayRs!QcslHj = PayRs!QcslHj + IIf(IsNull(AddRs!QcslHj), 0, AddRs!QcslHj)
        PayRs!QcwbHj = PayRs!QcwbHj + IIf(IsNull(AddRs!QcwbHj), 0, AddRs!QcwbHj)
        PayRs.Update
    Case BEN_QI_FA_SHENG_E '本期发生
        PayRs!Mjjehj = PayRs!Mjjehj + IIf(IsNull(AddRs!Mjjehj), 0, AddRs!Mjjehj) ' /* 本期借方发生额*/"
        PayRs!Mdjehj = PayRs!Mdjehj + IIf(IsNull(AddRs!Mdjehj), 0, AddRs!Mdjehj) ' /*本期借方数量发生额*/"
        PayRs!Mjslhj = PayRs!Mjslhj + IIf(IsNull(AddRs!Mjslhj), 0, AddRs!Mjslhj) ' /*本期借方外币发生额*/"
        PayRs!Mdslhj = PayRs!Mdslhj + IIf(IsNull(AddRs!Mdslhj), 0, AddRs!Mdslhj) ' /* 本期贷方发生额*/"
        PayRs!Mjwbhj = PayRs!Mjwbhj + IIf(IsNull(AddRs!Mjwbhj), 0, AddRs!Mjwbhj) ' /*本期贷方数量发生额*/"
        PayRs!Mdwbhj = PayRs!Mdwbhj + IIf(IsNull(AddRs!Mdwbhj), 0, AddRs!Mdwbhj) ' /*本期贷方外币发生额*/"
        PayRs.Update
    Case LEI_JI_FA_SHENG_E '累计发生
        PayRs!ByjfljjeHj = PayRs!ByjfljjeHj + IIf(IsNull(AddRs!ByjfljjeHj), 0, AddRs!ByjfljjeHj)
        PayRs!BydfljjeHj = PayRs!BydfljjeHj + IIf(IsNull(AddRs!BydfljjeHj), 0, AddRs!BydfljjeHj)
        PayRs!ByjfljslHj = PayRs!ByjfljslHj + IIf(IsNull(AddRs!ByjfljslHj), 0, AddRs!ByjfljslHj)
        PayRs!BydfljslHj = PayRs!BydfljslHj + IIf(IsNull(AddRs!BydfljslHj), 0, AddRs!BydfljslHj)
        PayRs!ByjfljwbHj = PayRs!ByjfljwbHj + IIf(IsNull(AddRs!ByjfljwbHj), 0, AddRs!ByjfljwbHj)
        PayRs!BydfljwbHj = PayRs!BydfljwbHj + IIf(IsNull(AddRs!BydfljwbHj), 0, AddRs!BydfljwbHj)
        PayRs.Update
    End Select
End Sub

Private Sub MeInit() '初始化字段名
    If DEBUG_FLAG = False Then On Error Resume Next
    Select Case PayTypes
    Case DEPT_CODE '部门
        CodeFlagFerldName = "DeptFlag"
        AssiCodeFeildName = "DeptCode"
        VouchCodeFeildName = "DeptCode"
    Case CURS_CODE '客户
        CodeFlagFerldName = "CusFlag"
        AssiCodeFeildName = "CusCode"
        VouchCodeFeildName = "CusCode"
    Case VEND_CODE '供应商
        CodeFlagFerldName = "SupplierFlag"
        AssiCodeFeildName = "SupplierCode"
        VouchCodeFeildName = "SupplierCode"
    Case PERS_CODE '个人
        CodeFlagFerldName = "PersonFlag"
        AssiCodeFeildName = "PersonCode"
        VouchCodeFeildName = "PersonCode"
    End Select
End Sub

'----------------------------------------------
Sub AddTowRs()
    '相加科目总记录集与末记帐凭证记录集
    '此模块被MakeRs调用
    '如果此记录为顶级科目，则继续对下一条记录进行累加，
    '否则追朔查询上级科目
    If DEBUG_FLAG = False Then On Error Resume Next
    
    Dim strTemParent As String
    With AddRs
        If Not (.EOF And .BOF) Then
            .MoveFirst
            Do Until .EOF
                strTemParent = Trim(!Ccode)
                If strTemParent <> "" Then SeachParent (strTemParent)
                .MoveNext
            Loop
        End If
    End With
End Sub

Sub SeachParent(strParend As String)
    '查询上级科目
    '此模块被AddTowRs 调用
    '1.保存记录书签
    '2.根据strParend在Rec_Query中找到myTemRs当前记录的上级科目位置
    '3.汇总记录，调用AddSingle
    '4.恢复书签
    
    If DEBUG_FLAG = False Then On Error Resume Next
    
    Dim myBookMark  '用于保存记录集(Rec_Query)书签
    
    With PayRs
        If Not (.BOF And .EOF) Then
            myBookMark = .Bookmark '保存记录书签
            .MoveFirst
            .Find "cCode='" & strParend & "'"
            If Not .EOF Then
                Call AddSingle
            End If
            .Bookmark = myBookMark
        End If
        
    End With
End Sub