attendance.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 AttendanceDate As Date

    Public Shared AttendanceItems As AttendanceItemCollection = Attendance.BuildAttendanceItems
    Public Shared ItemRecordTable As DataTable = Attendance.BuildRecordTable
#End Region

#Region "Properties"
    Default Public Property Item(ByVal iItemId As Integer) As AttendanceItem
        Get
            Return Attendance.AttendanceItems(iItemId)
        End Get
        Set(ByVal Value As AttendanceItem)
            Attendance.AttendanceItems(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 Shared ReadOnly Property Items() As ArrayList
        Get
            Return Attendance.AttendanceItems.Items
        End Get
    End Property
#End Region

#Region "Constructions"
    Public Sub New()
        Me.m_iAttendanceId = 0
        Me.m_iEmployeeId = 0
    End Sub

    Public Sub New(ByVal iEmployeeId As Integer, ByVal dDate As Date)
        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
                Attendance.AttendanceItems(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

    Public Sub New(ByVal sEmployeeName As String, ByVal dDate As Date)
        Me.New(DbBase.DataInterface().Execute4Value("select id from v_employee where number = '" & sEmployeeName & "'"), dDate)
    End Sub
#End Region

#Region "Private Methods"
    Private Shared Function BuildAttendanceItems() As AttendanceItemCollection
        Dim aicRes As New AttendanceItemCollection
        For Each drTemp As DataRow In DbBase.DataInterface().Execute4DS("select id,name,unit,filed from attendance_item").Tables(0).Rows
            aicRes.Add(drTemp("id"), New AttendanceItem(CInt(drTemp("id")), drTemp("name"), drTemp("unit"), drTemp("filed")))
        Next
        Return aicRes
    End Function

    Private Shared Function BuildRecordTable() As DataTable
        Dim dtRes As New DataTable("attendance")
        'm_oRecordTable.Columns.Add("员工", Type.GetType("System.String"))
        dtRes.Columns.Add("日期", Type.GetType("System.String"))
        'For Each drTemp As DataRow In DbBase.DataInterface().Execute4DS("select name from attendance_item").Tables(0).Rows
        For Each oAi As AttendanceItem In Attendance.AttendanceItems.Items
            dtRes.Columns.Add(oAi.Name & "/" & oAi.Unit, Type.GetType("System.String"))
        Next
        Return dtRes
    End Function
#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()
        Attendance.ItemRecordTable.Rows.Clear()
        Dim drTemp As DataRow = Attendance.ItemRecordTable.NewRow
        'drTemp(0) = Me.Employee.Name
        drTemp(0) = CStr(Me.AttendanceDate)
        For Each oAI As AttendanceItem In Attendance.Items
            drTemp(oAI.Name & "/" & oAI.Unit) = CStr(oAI.Amount) '& oAI.Unit
        Next
        Attendance.ItemRecordTable.Rows.Add(drTemp)
    End Sub

    'Public Function FillHistoryRecord()
    '    Dim dtRes As DataTable = Attendance.ItemRecordTable.Clone
    '    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("unit")) = drItemRecord("amount") '& drItemRecord("unit")
    '        Next
    '        dtRes.Rows.Add(drTemp)
    '    Next
    '    Return dtRes
    'End Function

    Public Shared Function MonthRecord(ByVal sEmployeeNumber As String, Optional ByVal iYear As Integer = 0, Optional ByVal iMonth As Integer = 0, Optional ByVal bWithSum As Boolean = True) As DataTable
        Dim sSql As String = "select id,attendance_date from attendance where employee_id = " & DbBase.DataInterface().Execute4Value("select id from v_employee where number = '" & sEmployeeNumber & "'")
        If iYear <> 0 And iMonth <> 0 Then
            sSql = sSql & " and (DATEPART(yyyy, attendance_date) = " & iYear & ") AND (DATEPART(m, attendance_date) = " & iMonth & ")"
        End If
        Dim dtRes As DataTable = Attendance.ItemRecordTable.Clone
        Dim dsAttendance As DataSet = DbBase.DataInterface().Execute4DS(sSql)
        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("unit")) = drItemRecord("amount") '& drItemRecord("unit")
            Next
            dtRes.Rows.Add(drTemp)
        Next
        If bWithSum Then
            Dim drSum As DataRow = dtRes.NewRow
            drSum(0) = CStr(iYear) & "年" & CStr(iMonth) & "月统计"
            For Each drTemp As DataRow In dtRes.Rows
                For i As Integer = 1 To dtRes.Columns.Count - 1
                    If drSum(i) Is DBNull.Value Then
                        drSum(i) = 0.0
                    End If
                    If drTemp(i) Is DBNull.Value Then
                        drTemp(i) = 0.0
                    End If
                    drSum(i) = CStr(CDbl(drSum(i)) + CDbl(drTemp(i)))
                Next
            Next
            dtRes.Rows.Add(drSum)
        End If
        Return dtRes
    End Function

    Public Shared Sub FillMonthRecord(ByVal sEmployeeNumber As String, Optional ByVal iYear As Integer = 0, Optional ByVal iMonth As Integer = 0)
        Dim dtTemp As DataTable = MonthRecord(sEmployeeNumber, iYear, iMonth, True)
        Dim drMonthRecord As DataRow = dtTemp.Rows(dtTemp.Rows.Count - 1)
        For Each oAI As AttendanceItem In Attendance.Items
            oAI.Amount = drMonthRecord(oAI.Name & "/" & oAI.Unit)
        Next
    End Sub

    Public Shared Function GetHistoryRecord(ByVal sEmployeeNumber As String) As DataTable
        Return MonthRecord(sEmployeeNumber, 0, 0, False)
    End Function

    Public Shared Function LastMonthRecord(ByVal sEmployeeNumber As String, Optional ByVal bWithSum As Boolean = True) As DataTable
        Dim iYear As Integer = Year(Now())
        Dim iMonth As Integer = Month(Now()) - 1
        Return MonthRecord(sEmployeeNumber, iYear, iMonth, bWithSum)
    End Function

    Public Shared Sub FillLastMonthRecord(ByVal sEmployeeNumber As String)
        Dim iYear As Integer = Year(Now())
        Dim iMonth As Integer = Month(Now()) - 1
        FillMonthRecord(sEmployeeNumber, iYear, iMonth)
    End Sub
#End Region
End Class