employeesum_function.bas

人事管理系统vb版,用于一般中小企业

Attribute VB_Name = "EmployeeSum_function"
 Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
 Dim mrc1 As New ADODB.Recordset
 Dim txtsql As String

'此函数用来计算该员工的收入总和
Public Function SumIncome(ByVal tempNumber As String)
    Dim tempSumIncome As Single   '该员工的所有收入总和
    
    txtsql = "select incomevalue from pb_employeeincome  where employeeid=" & "'" & tempNumber & "'"
    If mrc1.State = adStateOpen Then mrc1.Close
    mrc1.Open txtsql, adoConn, adOpenDynamic, adLockPessimistic, adCmdText
    If mrc1.EOF = False Then
        mrc1.MoveFirst
        Do Until mrc1.EOF
            tempSumIncome = tempSumIncome + mrc1.Fields("incomevalue")
            mrc1.MoveNext
        Loop
    End If
    
    SumIncome = tempSumIncome
End Function

'此函数用来计算该员工的支出总和
Public Function SumPayout(ByVal tempNumber As String)
    Dim tempSumPayout As Single  '该员工的所有支出总和
     
    txtsql = "select payoutvalue  from pb_employeepayout  where employeeid=" & "'" & tempNumber & "'"
    If mrc1.State = adStateOpen Then mrc1.Close
    mrc1.Open txtsql, adoConn, adOpenDynamic, adLockPessimistic, adCmdText
    If mrc1.EOF = False Then
        mrc1.MoveFirst
        Do Until mrc1.EOF
            tempSumPayout = tempSumPayout + mrc1.Fields("payoutvalue")
            mrc1.MoveNext
        Loop
    End If
    
    SumPayout = tempSumPayout
End Function

'此函数用来计算该员工个人部分的所有福利总和
Public Function SumBoon1(ByVal tempNumber As String)
    Dim tempSumBoon1 As Single    '该员工的个人部分福利总和
    
    txtsql = "select boonvalue  from pb_employeeboon  where employeeid=" & "'" & tempNumber & "'" & Space(1) & "and boontype=1"
    If mrc1.State = adStateOpen Then mrc1.Close
    mrc1.Open txtsql, adoConn, adOpenDynamic, adLockPessimistic, adCmdText
    If mrc1.EOF = False Then
        mrc1.MoveFirst
        Do Until mrc1.EOF
            tempSumBoon1 = tempSumBoon1 + mrc1.Fields("boonvalue")
            mrc1.MoveNext
        Loop
    End If
    
    SumBoon1 = tempSumBoon1
End Function


'此函数用来计算该员工公司部分的所有福利总和
Public Function SumBoon2(ByVal tempNumber As String)
    Dim tempSumBoon2 As Single    '该员工的公司部分福利总和
    
    txtsql = "select boonvalue  from pb_employeeboon  where employeeid=" & "'" & tempNumber & "'" & Space(1) & "and boontype=2"
    If mrc1.State = adStateOpen Then mrc1.Close
    mrc1.Open txtsql, adoConn, adOpenDynamic, adLockPessimistic, adCmdText
    If mrc1.EOF = False Then
        mrc1.MoveFirst
        Do Until mrc1.EOF
            tempSumBoon2 = tempSumBoon2 + mrc1.Fields("boonvalue")
            mrc1.MoveNext
        Loop
    End If
    
    SumBoon2 = tempSumBoon2
End Function


'此函数用来计算税收总和
Public Function Sumrevenue(ByVal tempNumber As String)
    Dim revenueStart As Single   '税收起征点
    Dim shouldRevenue As Single  '应税所得
    Dim cess As Single           '适用税率
    Dim takeout As Single        '扣除数


    '取出税收起征点
    txtsql = "select revenuestart  from pb_revenuestart"
    If mrc1.State = adStateOpen Then mrc1.Close
    mrc1.Open txtsql, adoConn, adOpenDynamic, adLockPessimistic, adCmdText
    If mrc1.EOF = False Then
        mrc1.MoveFirst
        revenueStart = mrc1.Fields("revenuestart")
    End If

     '算出应税所得
    shouldRevenue = SumIncome(tempNumber) - SumPayout(tempNumber) - SumBoon1(tempNumber) - revenueStart
    If shouldRevenue < 0 Then
        shouldRevenue = 0
    End If

    '从数据表中找出该应税所得在税收项目表中的范围得到"试用税率"及"扣除数"
    txtsql = "select *  from pb_revenue order by revenue_payupper"
    If mrc1.State = adStateOpen Then mrc1.Close
    mrc1.Open txtsql, adoConn, adOpenDynamic, adLockPessimistic, adCmdText
    If mrc1.EOF = False Then
        mrc1.MoveFirst
        Do Until mrc1.EOF
            If shouldRevenue > mrc1.Fields("revenue_paylower") = True Then
                mrc1.MoveNext
            Else
                cess = mrc1.Fields("revenue_cess")
                takeout = mrc1.Fields("revenue_takeout")

                Exit Do
            End If
        Loop
    End If

    Sumrevenue = shouldRevenue * cess / 100 - takeout
End Function

'此函数用来计算该员工的工资总和
Public Function sumPay(ByVal tempNumber As String)
    '该员工的工资=该员工的收入总和-支出总和-个人部分福利和-税收
    sumPay = SumIncome(tempNumber) - SumPayout(tempNumber) - SumBoon1(tempNumber) - Sumrevenue(tempNumber)
End Function