payroll.vb

人事管理系统（VB.NET2005+SQL Server2005）

Imports System.Data
Imports System.Text
Imports CommonDB
Imports CommonSystem


Public Class Payroll

#Region "Members"
    Private m_iPayrollId As Integer
    Private m_iEmployeeId As Integer
    Public Year As Integer
    Public Month As Integer

    Public Shared PayrollItems As PayrollItemCollection = BuildPayrollItems()
    Public Shared ItemRecordTable As DataTable = BuildRecordTable()
#End Region

#Region "Properties"
    Default Public Property Item(ByVal iItemId As Integer) As PayrollItem
        Get
            Return Payroll.PayrollItems(iItemId)
        End Get
        Set(ByVal Value As PayrollItem)
            Payroll.PayrollItems(iItemId) = Value
        End Set
    End Property

    Public Shared ReadOnly Property Items() As ArrayList
        Get
            Return Payroll.PayrollItems.Items
        End Get
    End Property

    Public Property Id() As Integer
        Get
            Return m_iPayrollId
        End Get
        Set(ByVal Value As Integer)
            m_iPayrollId = Value
        End Set
    End Property

    Public Property Employee() As Employee
        Get
            Return New Employee(m_iEmployeeId)
        End Get
        Set(ByVal Value As Employee)
            m_iEmployeeId = Value.Id
        End Set
    End Property
#End Region

#Region "Construcions"
    Public Sub New()
        m_iPayrollId = 0
        m_iEmployeeId = 0
        Year = Microsoft.VisualBasic.Year(Now())
        Month = Microsoft.VisualBasic.Month(Now()) - 1
    End Sub

    Public Sub New(ByVal iEmployeeId As Integer, ByVal iYear As Integer, ByVal iMonth As Integer)
        m_iEmployeeId = iEmployeeId
        Year = iYear
        Month = iMonth
        Try
            Me.m_iPayrollId = DbBase.DataInterface().Execute4Value("select id from payroll where employee_id = " & Me.m_iEmployeeId & " and year = " & Me.Year & " and month = " & Me.Month)
            Dim dsTemp As DataSet = DbBase.DataInterface.Execute4DS("select id,item_id,sum from payroll_item_record where payroll_id = " & Me.m_iPayrollId)
            For Each drTemp As DataRow In dsTemp.Tables(0).Rows
                Payroll.PayrollItems(drTemp("item_id")).Sum = drTemp("sum")
            Next
        Catch ex As Exception
            If ex.Message = "value unavailable!" Then
                DbBase.DataInterface.ExecuteSql("insert into payroll (employee_id,[year],[month]) values (" & Me.m_iEmployeeId & "," & Me.Year & "," & Me.Month & ")")
                Me.m_iPayrollId = DbBase.DataInterface.Execute4Value("select top 1 id from payroll order by id desc")
            Else
                Throw New Exception(ex.Message)
            End If
        End Try
        Me.CalculatePay()
    End Sub

    Public Sub New(ByVal sEmployeeNumber As String, ByVal iYear As Integer, ByVal iMonth As Integer)
        Me.New(DbBase.DataInterface.Execute4Value("select id from v_employee where number = '" & sEmployeeNumber & "'"), iYear, iMonth)
    End Sub
#End Region

#Region "Private Methods"
    '建立工资项列表
    Private Shared Function BuildPayrollItems() As PayrollItemCollection
        Dim aicRes As New PayrollItemCollection
        For Each drTemp As DataRow In DbBase.DataInterface().Execute4DS("select id,name,field,expression from payroll_item").Tables(0).Rows
            aicRes.Add(drTemp("id"), New PayrollItem(CInt(drTemp("id")), drTemp("name"), drTemp("field"), drTemp("expression")))
        Next
        Return aicRes
    End Function
    '建立工资记录表
    Private Shared Function BuildRecordTable() As DataTable
        Dim dtRes As New DataTable("payroll")
        dtRes.Columns.Add("月份", Type.GetType("System.String"))
        For Each oPI As PayrollItem In Payroll.PayrollItems.Items
            dtRes.Columns.Add(oPI.Name, Type.GetType("System.String"))
        Next
        Return dtRes
    End Function
    '计算员工工资
    Private Function CalculatePay() As DataTable
        Dim dsTemp As DataSet = DbBase.DataInterface.Execute4DS("SELECT payroll_level.coefficient, employee.work_age FROM payroll_level INNER JOIN employee ON payroll_level.[level] = employee.employee_level WHERE (employee.id = " & Me.m_iEmployeeId & ")")
        Dim dtTemp As DataTable = dsTemp.Tables(0)

        Dim sbCode As StringBuilder = New StringBuilder
        '定义局部变量，和表的字段名
        sbCode.Append("Dim " & dtTemp.Columns(0).Caption & " As Single = " & dtTemp.Rows(0)(0) & vbCrLf)
        sbCode.Append("Dim " & dtTemp.Columns(1).Caption & " As Single = " & dtTemp.Rows(0)(1) & vbCrLf)

        sbCode.Append("Dim dtResult As DataTable = New DataTable" & vbCrLf)
        sbCode.Append("dtResult.Columns.Add(""" & dtTemp.Columns(0).Caption & """, Type.GetType(""System.Single""))" & vbCrLf)
        sbCode.Append("dtResult.Columns.Add(""" & dtTemp.Columns(1).Caption & """, Type.GetType(""System.Single""))" & vbCrLf)

        '计算上一个月的考勤数据
        Attendance.FillLastMonthRecord(Me.Employee.Number)
        For Each oAI As AttendanceItem In Attendance.Items
            sbCode.Append("Dim " & oAI.Field & " As Single = " & oAI.Amount & vbCrLf)
            sbCode.Append("dtResult.Columns.Add(""" & oAI.Field & """, Type.GetType(""System.Single""))" & vbCrLf)
        Next
        For Each oPI As PayrollItem In Payroll.Items
            sbCode.Append("Dim " & oPI.Field & " As Single = " & oPI.Expression & vbCrLf)
            sbCode.Append("dtResult.Columns.Add(""" & oPI.Field & """, Type.GetType(""System.Single""))" & vbCrLf)
        Next
        '填充表各字段的数值
        sbCode.Append("Dim drResult As DataRow = dtResult.NewRow" & vbCrLf)
        sbCode.Append("drResult(""" & dtTemp.Columns(0).Caption & """) = " & dtTemp.Rows(0)(0) & vbCrLf)
        sbCode.Append("drResult(""" & dtTemp.Columns(1).Caption & """) = " & dtTemp.Rows(0)(1) & vbCrLf)
        For Each oAI As AttendanceItem In Attendance.Items
            sbCode.Append("drResult(""" & oAI.Field & """) = " & oAI.Field & vbCrLf)
        Next
        For Each oPI As PayrollItem In Payroll.Items
            sbCode.Append("drResult(""" & oPI.Field & """) = " & oPI.Field & vbCrLf)
        Next

        sbCode.Append("dtResult.Rows.Clear()" & vbCrLf)
        sbCode.Append("dtResult.Rows.Add(drResult)" & vbCrLf)
        sbCode.Append("Return dtResult" & vbCrLf)
        '定义DataTable保存计算得到的各项工资金额
        Dim dtPayrollItemSum As DataTable = CType(EvalProvider.Eval(sbCode.ToString), DataTable)
        For Each oPI As PayrollItem In Payroll.Items
            oPI.Sum = dtPayrollItemSum.Rows(0)(oPI.Field)
        Next
        Return dtPayrollItemSum
    End Function
#End Region

#Region "Operations"
    '发放工资
    Public Function Payoff() As Short
        If m_iPayrollId = 0 Then
            Return -1
            Exit Function
        End If
        If Not CBool(DbBase.DataInterface.Execute4Object("select already_pay from payroll where id = " & Me.m_iPayrollId & ";")) Then
            Dim iCount As Integer = Payroll.Items.Count
            Dim arrSql(iCount + 1) As String
            arrSql(0) = "update payroll set already_pay = 1 where id = " & Me.m_iPayrollId & ";"
            For i As Integer = 1 To iCount
                arrSql(i) = "insert into payroll_item_record (item_id,sum,payroll_id) values (" & CType(Payroll.Items(i), PayrollItem).Id & "," & CType(Payroll.Items(i), PayrollItem).Sum & "," & Me.m_iPayrollId & ");"
            Next
            arrSql(iCount + 1) = "insert into bank_record (sum,date,account) values (" & CType(Payroll.Items(iCount - 1), PayrollItem).Sum & "," & Today() & "," & Me.Employee.BankAccount & ");"
            Return DbBase.DataInterface.ExecuteSqls(arrSql)
        Else
            Throw New Exception("工资已经发放！")
        End If
    End Function
    '预支工资
    'Public Function Prepay(ByVal fSum As Single) As Boolean
    '    If DbBase.DataInterface.ExecuteSql("update payroll set sum = " & fSum & " where id = " & m_iPayrollId) = 0 Then
    '        Return True
    '    End If
    'End Function

    '记录各工资项的金额
    Public Sub RecordItem(ByVal iItemId As Integer, ByVal fSum As Single)
        Payroll.PayrollItems(iItemId).Sum = fSum
        Payroll.PayrollItems(iItemId).Expression = CStr(fSum)
        CalculatePay()
        FillRecord()
    End Sub
    '将各工资项的金额填充到数据表中
    Public Sub FillRecord()
        Payroll.ItemRecordTable.Rows.Clear()
        Dim drTemp As DataRow = Payroll.ItemRecordTable.NewRow
        drTemp(0) = Me.Year & "年" & Me.Month & "月"
        For Each oPI As PayrollItem In Payroll.PayrollItems.Items
            drTemp(oPI.Name) = oPI.Sum
        Next
        Payroll.ItemRecordTable.Rows.Add(drTemp)
    End Sub
    '获取员工历史工资记录
    Public Function GetHistoryRecord() As DataTable
        Return HistoryRecord(Me.Id)
    End Function

    Public Shared Function HistoryRecord(ByVal iEmployeeId As Integer) As DataTable
        Dim dtRes As DataTable = Payroll.ItemRecordTable.Clone
        For Each drPayroll As DataRow In DbBase.DataInterface.Execute4DS("select id,year,month from payroll where already_pay = 1 and employee_id = " & iEmployeeId).Tables(0).Rows
            Dim drTemp As DataRow = dtRes.NewRow
            drTemp(0) = drPayroll("year") & "年" & drPayroll("month") & "月"
            Dim dtPayrollItem As DataTable = DbBase.DataInterface.Execute4DS("SELECT payroll_item_record.[sum], payroll_item.name FROM payroll_item_record INNER JOIN payroll_item ON payroll_item_record.item_id = payroll_item.id WHERE (payroll_item_record.payroll_id = " & drPayroll("id") & ")").Tables(0)
            For Each drPayrollItem As DataRow In dtPayrollItem.Rows
                drTemp(drPayrollItem("name")) = drPayrollItem("sum")
            Next
            dtRes.Rows.Add(drTemp)
        Next
        Return dtRes
    End Function

    Public Shared Function HistoryRecord(ByVal sEmployeeNumber As String) As DataTable
        Dim iEmployeeId As Integer = DbBase.DataInterface.Execute4Value("select id from v_employee where number = '" & sEmployeeNumber & "'")
        Return HistoryRecord(iEmployeeId)
    End Function
#End Region

End Class