attendance.1.vb

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

Imports System
Imports System.Data
Imports CommonDB

' 考勤记录类
Public Class Attendance

#Region "Members"
    Private m_iAttendanceId As Integer
    Public m_iEmployeeId As Integer
    'Public Number As String
    Public AttendanceDate As Date
    Private m_oItems As AttendanceItemCollection
    Private m_oRecordTable As DataTable
#End Region

#Region "Properties"
    Default Public Property Item(ByVal iItemId As Integer) As AttendanceItem
        Get
            Return CType(m_oItems(iItemId), AttendanceItem)
        End Get
        Set(ByVal Value As AttendanceItem)
            m_oItems(iItemId) = Value
        End Set
    End Property

    Public ReadOnly Property Id() As Integer
        Get
            Return m_iAttendanceId
        End Get
    End Property

    Public Property Employee() As Employee
        Get
            Return New Employee(CStr(DbBase.DataInterface().Execute4Object("select number from v_employee where id = " & m_iEmployeeId)))
        End Get
        Set(ByVal Value As Employee)
            m_iEmployeeId = Value.Id
        End Set
    End Property

    Public ReadOnly Property Items() As ArrayList
        Get
            Return m_oItems.Items
        End Get
    End Property

    Public ReadOnly Property RecordTable() As DataTable
        Get
            Return m_oRecordTable
        End Get
    End Property
#End Region

#Region "Constructions"
    Public Sub New()
        Me.m_iAttendanceId = 0
        Me.m_iEmployeeId = 0
        Me.m_oItems = New AttendanceItemCollection
        Dim dsTemp As DataSet = DbBase.DataInterface().Execute4DS("select id,name,unit from attendance_item")
        For Each drTemp As DataRow In dsTemp.Tables(0).Rows
            m_oItems.Add(drTemp("id"), New AttendanceItem(CInt(drTemp("id")), drTemp("name"), drTemp("unit")))
        Next
        dsTemp.Dispose()
        BuildRecordTable()
    End Sub

    Public Sub New(ByVal iEmployeeId As Integer, ByVal dDate As Date)
        Me.New()
        Me.m_iEmployeeId = iEmployeeId
        Me.AttendanceDate = dDate
        Try
            Me.m_iAttendanceId = DbBase.DataInterface().Execute4Value("select id from attendance where employee_id = " & Me.m_iEmployeeId & " and attendance_date = " & Me.AttendanceDate)
            Dim dsTemp As DataSet = DbBase.DataInterface().Execute4DS("select id,item_id,amount from attendance_item_record where attendance_id = " & Me.m_iAttendanceId)
            For Each drTemp As DataRow In dsTemp.Tables(0).Rows
                Me(drTemp("item_id")).Amount = drTemp("amount")
            Next
        Catch ex As Exception
            If ex.Message = "value unavailable！" Then
                DbBase.DataInterface().ExecuteSql("insert into attendance (employee_id,attendance_date) values (" & Employee.Id & "," & AttendanceDate & ")")
                Me.m_iAttendanceId = DbBase.DataInterface().Execute4Value("select top 1 id from attendance order by id desc")
                Exit Sub
            Else
                Throw New Exception(ex.Message)
            End If
        End Try
    End Sub
#End Region

#Region "Private Methods"
    Private Sub BuildRecordTable()
        m_oRecordTable = New DataTable("attendance")
        'm_oRecordTable.Columns.Add("员工", Type.GetType("System.String"))
        m_oRecordTable.Columns.Add("日期", Type.GetType("System.String"))
        For Each oAI As AttendanceItem In Me.Items
            m_oRecordTable.Columns.Add(oAI.Name, Type.GetType("System.String"))
        Next
    End Sub
#End Region

#Region "Operations"
    ' 考勤
    Public Function CheckAttendance() As Short
        Dim iRes As Integer = 0
        If m_iAttendanceId = 0 Then
            iRes = DbBase.DataInterface().ExecuteSql("insert into attendance (employee_id,attendance_date) values (" & Employee.Id & ",'" & AttendanceDate & "')")
            m_iAttendanceId = DbBase.DataInterface().Execute4Value("select top 1 id from attendance order by id desc")
        End If
        Dim iItemRecordId As Integer
        For Each oAI As AttendanceItem In Items
            If oAI.Amount <> 0.0 Then
                Try
                    iItemRecordId = DbBase.DataInterface().Execute4Value("select id from attendance_item_record where item_id = " & oAI.Id & " and attendance_id = " & Me.Id)
                    iRes = iRes + DbBase.DataInterface().ExecuteSql("update attendance_item_record set amount = " & oAI.Amount & " where item_id = " & oAI.Id & " and attendance_id = " & Me.Id)
                Catch ex As Exception
                    If ex.Message = "value unavailable!" Then
                        iRes = iRes + DbBase.DataInterface().ExecuteSql("insert into attendance_item_record (item_id,amount,attendance_id) values (" & oAI.Id & "," & oAI.Amount & "," & Id & ")")
                    Else
                        Throw New Exception(ex.Message)
                    End If
                End Try
            End If
        Next
        Return iRes
    End Function

    Public Sub RecordItem(ByVal iItemId As Integer, ByVal fAmount As Single)
        Item(iItemId).Amount = fAmount
        FillRecord()
    End Sub

    Public Sub FillRecord()
        m_oRecordTable.Rows.Clear()
        Dim drTemp As DataRow = m_oRecordTable.NewRow
        'drTemp(0) = Me.Employee.Name
        drTemp(0) = CStr(Me.AttendanceDate)
        Dim i As Integer = 1
        For Each oAI As AttendanceItem In Me.Items
            drTemp(i) = CStr(oAI.Amount) & oAI.Unit
            i = i + 1
        Next
        m_oRecordTable.Rows.Add(drTemp)
    End Sub

    Public Function GetHistoryRecord() As DataTable
        Dim dtRes As DataTable = m_oRecordTable.Clone
        dtRes.Rows.Clear()
        Dim dsAttendance As DataSet = DbBase.DataInterface().Execute4DS("select id,attendance_date from attendance where employee_id = " & Me.Employee.Id)
        For Each drAttendance As DataRow In dsAttendance.Tables(0).Rows
            Dim drTemp As DataRow = dtRes.NewRow
            drTemp(0) = drAttendance("attendance_date")
            Dim dsItemRecord As DataSet = DbBase.DataInterface().Execute4DS("SELECT attendance_item_record.amount, attendance_item.name, attendance_item.unit FROM attendance_item_record INNER JOIN attendance_item ON attendance_item_record.item_id = attendance_item.id where attendance_item_record.attendance_id = " & drAttendance("id"))
            For Each drItemRecord As DataRow In dsItemRecord.Tables(0).Rows
                drTemp(drItemRecord("name")) = drItemRecord("amount") & drItemRecord("unit")
            Next
            dtRes.Rows.Add(drTemp)
        Next
        Return dtRes
    End Function

    Public Shared Function HistoryRecord(ByVal iEmployeeId As Integer) As DataTable
        '
    End Function
#End Region
End Class ' END CLASS DEFINITION Attendance