businessflow.bas

会计管理信息系统windows界面vb编程 会计管理信息系统windows界面vb编程

Attribute VB_Name = "BusinessFlow"
'********************帐簿输出业务逻辑**************************************
'逻辑编写：刘春宏

'成本        |资产        |负债        |所有者权益    |  损益
'________________________________________________________________
'借加贷减    |借加贷减    |借减贷加    |借减贷加      |借减贷加
'******************************************************************
'举例:   购进原材料
'原材料 现金
'借：500 贷：500
'举例:       付清帐款
'    银行存款 应付账款
'    贷：500 借：500
'*********************************************************************

'************************程序*****************************************
'程序设计: 万星
'程序编写: 万星


'设置参数：
'strRequestPara  读取传递过来的科目参数
'intSubjectID  读取传递过来的科目代码附值给它
'strSubjectName 读取传递过来的科目名称附值给它
'判断函数 IsIDorName() 判断传递过来的参数是科目代码还是科目名称
'定义变量
Public strFindCredence As String '查询在pzlz表中的凭证
Public strFindCredenceNum As String '查询有多少符合条件的凭证
Public intCredenceID As Integer '凭证号
Public strSubjectName As String '科目名称
Public intDebitMoney As Integer '借方金额
Public intLenderMoney As Integer '贷方金额
Public intBalanceMoney As Integer '余额
Public numCredence As Integer '凭证数量
Public DnyArrayCredenceID() As Integer '定义一个动态数组，存储凭证号
Public DnyArraySubjectName() As String '定义一个动态数组，存储科目名称
Public DnyArrayDebitMoney()  As Integer '定义一个动态数组，存储借方金额
Public DnyArrayLenderMoney()  As Integer '定义一个动态数组，存储贷方金额
Public DnyArrayBalanceMoney() As Integer '定义一个动态数组，存储余额

Public Sub IsIDorName(strRequestPara As String)
  If Val(strRequestPara) = 0 Then
   
    Call FindCredenceByName(strRequestPara)
  Else
    
    Call FindCredenceByID(Val(strRequestPara))
  End If
  
End Sub
'按照科目代码找到所有凭证 FindCredenceByID()
Private Sub FindCredenceByID(intSubjectID As Double)
   Dim intEachBalanceMoney As Integer
   Dim iRow As Integer
   Dim ADOrs As New Recordset
   Dim rs As New Recordset
   
   Set rs.ActiveConnection = ADOcn
   strFindCredenceNum = "select count(凭证号) as num_凭证号 from pzls where 科目代码='" + Trim(Str(intSubjectID)) + "'and 记账标志=0 "
   rs.Open strFindCredenceNum
   
   
   numCredence = rs.Fields("num_凭证号") - 1 '读取查出的凭证的数量，减1，作为动态数组上界
If numCredence = -1 Then
      MsgBox "没有符合条件的凭证"
Else
     ReDim DnyArrayCredenceID(numCredence) As Integer   '重新定义动态数组维数，存储凭证号
     ReDim DnyArraySubjectName(numCredence) As String   '重新定义动态数组维数，存储科目名称
     ReDim DnyArrayDebitMoney(numCredence) As Integer    '重新定义动态数组维数，存储借方金额
     ReDim DnyArrayLenderMoney(numCredence) As Integer   '重新定义动态数组维数，存储贷方金额
     ReDim DnyArrayBalanceMoney(numCredence) As Integer  '重新定义动态数组维数，存储余额
    
   
    rs.Close
    
   Set ADOrs.ActiveConnection = ADOcn
    strFindCredence = "select 凭证号,科目名称,借方金额,贷方金额 from pzls where 科目代码='" + Trim(Str(intSubjectID)) + "'and 记账标志=0 order by 日期"
    ADOrs.Open strFindCredence
   If Not ADOrs.EOF Then
     Do While Not ADOrs.EOF
         intCredenceID = ADOrs.Fields("凭证号")
         strSubjectName = ADOrs.Fields("科目名称")
         intDebitMoney = ADOrs.Fields("借方金额")
         intLenderMoney = ADOrs.Fields("贷方金额")
       If HowToAccount(CStr(strSubjectName)) = True Then '按不同方法计算余额
         Call AcountCostClass(intDebitMoney, intLenderMoney)
       Else
         Call AcountDebtClass(intDebitMoney, intLenderMoney)
       End If
     intEachBalanceMoney = intBalanceMoney '返回的余额值
     
           DnyArrayCredenceID(iRow) = intCredenceID
           DnyArraySubjectName(iRow) = strSubjectName
           DnyArrayDebitMoney(iRow) = intDebitMoney
           DnyArrayLenderMoney(iRow) = intLenderMoney
           DnyArrayBalanceMoney(iRow) = intEachBalanceMoney
        iRow = iRow + 1
       ADOrs.MoveNext
     Loop
 Else
   MsgBox "没有符合条件的凭证"
 End If
    ADOrs.Close
 End If
   
   
End Sub
'按照科目名称找到所有凭证 FindCredenceByName()
Private Sub FindCredenceByName(intSubjectName As String)
   Dim intEachBalanceMoney As Integer
   Dim iRow As Integer
   Dim ADOrs As New Recordset
   Dim rs As New Recordset
   
   Set rs.ActiveConnection = ADOcn
   strFindCredenceNum = "select count(凭证号) as num_凭证号 from pzls where 科目名称='" + intSubjectName + "'and 记账标志=0 "
   rs.Open strFindCredenceNum
   
   
   numCredence = rs.Fields("num_凭证号") - 1 '读取查出的凭证的数量，减1，作为动态数组上界
If numCredence = -1 Then
      MsgBox "没有符合条件的凭证"
Else
   ReDim DnyArrayCredenceID(numCredence) As Integer   '重新定义动态数组维数，存储凭证号
   ReDim DnyArraySubjectName(numCredence) As String  '重新定义动态数组维数，存储科目名称
   ReDim DnyArrayDebitMoney(numCredence) As Integer   '重新定义动态数组维数，存储借方金额
   ReDim DnyArrayLenderMoney(numCredence) As Integer   '重新定义动态数组维数，存储贷方金额
   ReDim DnyArrayBalanceMoney(numCredence) As Integer  '重新定义动态数组维数，存储余额
   
   Set ADOrs.ActiveConnection = ADOcn
   strFindCredence = "select 凭证号,科目名称,借方金额,贷方金额 from pzls where 科目名称='" + intSubjectName + "'and 记账标志=0 order by 日期"
   ADOrs.Open strFindCredence
   
  If Not ADOrs.EOF Then
     Do While Not ADOrs.EOF
         intCredenceID = ADOrs.Fields("凭证号")
         strSubjectName = ADOrs.Fields("科目名称")
         intDebitMoney = ADOrs.Fields("借方金额")
         intLenderMoney = ADOrs.Fields("贷方金额")
       If HowToAccount(strSubjectName) = True Then '按不同方法计算余额
         Call AcountCostClass(intDebitMoney, intLenderMoney)
       Else
         Call AcountDebtClass(intDebitMoney, intLenderMoney)
       End If
     intEachBalanceMoney = intBalanceMoney '返回的余额值
     
           DnyArrayCredenceID(iRow) = intCredenceID
           DnyArraySubjectName(iRow) = strSubjectName
           DnyArrayDebitMoney(iRow) = intDebitMoney
           DnyArrayLenderMoney(iRow) = intLenderMoney
           DnyArrayBalanceMoney(iRow) = intEachBalanceMoney
        iRow = iRow + 1
       ADOrs.MoveNext
     Loop
   Else
    MsgBox "没有符合条件的凭证"
   End If
   ADOrs.Close
End If
   
   
End Sub
'判断是成本、资产类科目还是负债、所有者权益、损益类科目的函数HowToAccount(TypeOfSubject)
   Private Function HowToAccount(SubjectName As String) As Boolean
     Dim ADOrs As New Recordset
     Set ADOrs.ActiveConnection = ADOcn
     strFindSebjectType = "select 科目类别 from kmzd where 科目名称='" + SubjectName + "'"
     ADOrs.Open strFindSebjectType
     
     If ADOrs.Fields("科目类别") = 资产 Or ADOrs.Fields("科目类别") = 成本 Then
        HowToAccount = True
     Else
        HowToAccount = False
     End If
   End Function
'计算成本、资产类科目的函数 AcountCostClass()
   Private Sub AcountCostClass(DebitMoney As Integer, LenderMoney As Integer)
     intBalanceMoney = DnyArrayBalanceMoney(iRow) + DebitMoney - LenderMoney
   
   End Sub
'计算负债、所有者权益、损益类科目的函数 AcountDebtClass()
   Private Sub AcountDebtClass(DebitMoney As Integer, LenderMoney As Integer)
     intBalanceMoney = DnyArrayBalanceMoney(iRow) - DebitMoney + LenderMoney
   
   End Sub
'安全性检查
'检查数字是否太长
 Public Function checkLength(Inpara As String) As Boolean
    If Val(Inpara) > 9999999999# Then
      checkLength = False
    End If
 End Function
'防sql注入
 Public Function checkSqlFlag(Inpara As String)
    If InStr(Inpara, "'") <> 0 Then
      checkSqlFlag = Replace(Inpara, "'", "")
    Else
      checkSqlFlag = Inpara
    End If
 End Function