timesheet.vb

数据库学习的绝好例子简单的数据库经典入门

Public Class TimeSheet
#Region " Variable Declarations "
    'Private variables
    Private intIndex As Integer
    Private intTotal As Integer

    Private blnEmployeeDisplay As Boolean = True
    Private blnLoading As Boolean = True

    Private strAppTitle As String
    Private strCompany As String = "Wrox"
    Private strApplication As String = "Time Tracker SQL"
    Private strUserID As String
    Private strManagerID As String

    Private objTimeSheets As WroxBusinessLogic.WBLTimeSheets
    Private objUsers As WroxBusinessLogic.WBLUsers

    Private objDataSet As Data.DataSet
    Private objTimeSheetDS As Data.DataSet
    Private objEmployees As Data.DataSet

    Private objTimeSheetDV As Data.DataView
#End Region

#Region " Load Procedures "
    Private Sub TimeSheet_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Load

        'Set the current date in the date panel in the status bar
        ToolStripDate.Text = Date.Today

        'Get the process title from the executable name
        strAppTitle = My.Application.Info.Title

        'Hide the managers menu option for regular users
        If g_strUserRole.ToLower Like "user" Then
            EmployeeTimeSheetsToolStripMenuItem.Visible = False
            blnEmployeeDisplay = True
            strUserID = g_strUserID
        Else
            blnEmployeeDisplay = False
            Call EmployeeTimeSheetsToolStripMenuItem_Click(Nothing, Nothing)
            strManagerID = g_strUserID
            Call LoadEmployees()
        End If

        'Display the users name
        lblEmployee.Text &= " " & g_strUserName

        'Load the week ending date combo boxes
        cboWeekEnding.Items.Add(GetPreviousWeekEndingDate)
        cboEmployeeWeekEnding.Items.Add(GetPreviousWeekEndingDate)
        cboWeekEnding.Items.Add(GetCurrentWeekEndingDate)
        cboEmployeeWeekEnding.Items.Add(GetCurrentWeekEndingDate)

        If g_strUserRole.ToLower Like "user" Then
            cboWeekEnding.SelectedIndex = 1
        Else
            cboWeekEnding.SelectedIndex = -1
        End If

        'Turn off the loading flag
        blnLoading = False
    End Sub

    Private Sub LoadEmployees()
        'Clear previous bindings
        cboEmployee.DataSource = Nothing
        cboEmployee.DisplayMember = String.Empty
        cboEmployee.ValueMember = String.Empty

        'Initialize a new instance of the business logic component
        Using objUsers As New WroxBusinessLogic.WBLUsers( _
            strCompany, strApplication)

            'Get the timesheet for the user
            objEmployees = objUsers.GetManagerEmployees(New Guid(strManagerID))

            'Rebind ComboBox control
            cboEmployee.DataSource = objEmployees.Tables("Employees")
            cboEmployee.DisplayMember = "EmployeeName"
            cboEmployee.ValueMember = "UserID"
        End Using
    End Sub

    Private Sub LoadTimeSheet(ByVal WeekEndingDate As ComboBox)
        'Clear previous bindings
        grdTimeSheet.DataSource = Nothing
        grdTimeSheet.DataMember = String.Empty
        grdTimeSheet.Columns.Clear()

        'Initialize a new instance of the business logic component
        Using objTimeSheets As New WroxBusinessLogic.WBLTimeSheets( _
            strCompany, strApplication)

            'Get the timesheet for the user
            objTimeSheetDS = objTimeSheets.GetTimeSheet( _
                New Guid(strUserID), WeekEndingDate.SelectedItem)

            'Set the DataView object with the data from the DataSet
            objTimeSheetDV = New Data.DataView(objTimeSheetDS.Tables("TimeSheet"))

            'Initialize a new DataRowView object
            Dim objDataRowView As Data.DataRowView = objTimeSheetDV.AddNew

            'Set the values in the columns
            objDataRowView("ProjectName") = "Total Hours"

            'Calculate and set the total hours for Monday
            intTotal = 0
            For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 1
                intTotal += objTimeSheetDS.Tables( _
                    "TimeSheet").Rows(intIndex).Item("MondayHours")
            Next
            objDataRowView("MondayHours") = intTotal

            'Calculate and set the total hours for Tuesday
            intTotal = 0
            For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 1
                intTotal += objTimeSheetDS.Tables( _
                    "TimeSheet").Rows(intIndex).Item("TuesdayHours")
            Next
            objDataRowView("TuesdayHours") = intTotal

            'Calculate and set the total hours for Wednesday
            intTotal = 0
            For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 1
                intTotal += objTimeSheetDS.Tables( _
                    "TimeSheet").Rows(intIndex).Item("WednesdayHours")
            Next
            objDataRowView("WednesdayHours") = intTotal

            'Calculate and set the total hours for Thursday
            intTotal = 0
            For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 1
                intTotal += objTimeSheetDS.Tables( _
                    "TimeSheet").Rows(intIndex).Item("ThursdayHours")
            Next
            objDataRowView("ThursdayHours") = intTotal

            'Calculate and set the total hours for Friday
            intTotal = 0
            For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 1
                intTotal += objTimeSheetDS.Tables( _
                    "TimeSheet").Rows(intIndex).Item("FridayHours")
            Next
            objDataRowView("FridayHours") = intTotal

            'Commit the changes to the row
            objDataRowView.EndEdit()

            'Disallow new records
            objTimeSheetDV.AllowNew = False

            'Bind the DataView to the DataGridView
            grdTimeSheet.AutoGenerateColumns = False
            grdTimeSheet.DataSource = objTimeSheetDV

            'Set the DataGridView properties
            grdTimeSheet.AlternatingRowsDefaultCellStyle.BackColor = _
                Color.WhiteSmoke

            'Create and add DataGridView text box columns
            Dim objColumn As New DataGridViewTextBoxColumn
            With objColumn
                .HeaderText = "Project Name"
                .DataPropertyName = "ProjectName"
                .ReadOnly = True
                .Width = 225
            End With
            grdTimeSheet.Columns.Add(objColumn)

            objColumn = New DataGridViewTextBoxColumn
            With objColumn
                .HeaderText = "Monday"
                .DataPropertyName = "MondayHours"
                .Width = 70
                If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") _
                    Or Not blnEmployeeDisplay Then
                    .ReadOnly = True
                End If
            End With
            grdTimeSheet.Columns.Add(objColumn)

            objColumn = New DataGridViewTextBoxColumn
            With objColumn
                .HeaderText = "Tuesday"
                .DataPropertyName = "TuesdayHours"
                .Width = 70
                If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") _
                    Or Not blnEmployeeDisplay Then
                    .ReadOnly = True
                End If
            End With
            grdTimeSheet.Columns.Add(objColumn)

            objColumn = New DataGridViewTextBoxColumn
            With objColumn
                .HeaderText = "Wednesday"
                .DataPropertyName = "WednesdayHours"
                .Width = 70
                If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") _
                    Or Not blnEmployeeDisplay Then
                    .ReadOnly = True
                End If
            End With
            grdTimeSheet.Columns.Add(objColumn)

            objColumn = New DataGridViewTextBoxColumn
            With objColumn
                .HeaderText = "Thursday"
                .DataPropertyName = "ThursdayHours"
                .Width = 70
                If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") _
                    Or Not blnEmployeeDisplay Then
                    .ReadOnly = True
                End If
            End With
            grdTimeSheet.Columns.Add(objColumn)

            objColumn = New DataGridViewTextBoxColumn
            With objColumn
                .HeaderText = "Friday"
                .DataPropertyName = "FridayHours"
                .Width = 70
                If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") _
                    Or Not blnEmployeeDisplay Then
                    .ReadOnly = True
                End If
            End With
            grdTimeSheet.Columns.Add(objColumn)

            'Change the locked icon if the timesheet is readonly
            If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") _
                Or Not blnEmployeeDisplay Then
                imgStatus.Image = ImageList1.Images(1)
            Else
                imgStatus.Image = ImageList1.Images(0)
            End If
        End Using
    End Sub
#End Region

#Region " Menu and Toolbar Procedures "
    Private Sub exitToolStripMenuItem_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles exitToolStripMenuItem.Click

        Me.Close()
    End Sub

    Private Sub MyTimeSheetToolStripMenuItem_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles MyTimeSheetToolStripMenuItem.Click

        'Undock the Panel
        pnlManager.Dock = DockStyle.None
        'Move it out of the way
        pnlManager.Location = New Point(5000, 5000)
        'Set the Dock property to Fill
        '(this will cause the location to change to 0,0)
        pnlEmployee.Dock = DockStyle.Top
        'Set the view flag
        blnEmployeeDisplay = True
    End Sub

    Private Sub EmployeeTimeSheetsToolStripMenuItem_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles _
        EmployeeTimeSheetsToolStripMenuItem.Click

        'Undock the Panel
        pnlEmployee.Dock = DockStyle.None
        'Move it out of the way
        pnlEmployee.Location = New Point(5000, 5000)
        'Set the Dock property to Fill
        '(this will cause the location to change to 0,0)
        pnlManager.Dock = DockStyle.Top
        'Set the view flag
        blnEmployeeDisplay = False
    End Sub

    Private Sub aboutToolStripMenuItem_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles aboutToolStripMenuItem.Click

        Dim objAbout As New About
        objAbout.ShowDialog(Me)
        objAbout.Dispose()
        objAbout = Nothing
    End Sub
#End Region

#Region " Utility Functions "
    Private Function GetCurrentWeekEndingDate() As String
        GetCurrentWeekEndingDate = DateSerial( _
            Year(Now), Month(Now), DateAndTime.Day(Now) - _
            DatePart("w", Now, FirstDayOfWeek.Sunday) + 6)
    End Function

    Private Function GetPreviousWeekEndingDate() As String
        GetPreviousWeekEndingDate = DateSerial( _
            Year(Now), Month(Now), DateAndTime.Day(Now) - _
            DatePart("w", Now, FirstDayOfWeek.Sunday) - 1)
    End Function
#End Region

#Region " Timesheet Functions "
    Private Sub cboWeekEnding_SelectedIndexChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles cboWeekEnding.SelectedIndexChanged

        If Not blnEmployeeDisplay And blnLoading Then
            Exit Sub
        End If

        'Load the timesheet
        Call LoadTimeSheet(cboWeekEnding)
    End Sub

    Private Sub grdTimeSheet_CurrentCellChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles grdTimeSheet.CurrentCellChanged

        'Declare variables
        Dim objDataRowView As Data.DataRowView
        Dim objDataRowTotal As Data.DataRowView

        'Get the total row so we can update the totals
        objDataRowTotal = objTimeSheetDV.Item(objTimeSheetDV.Count - 1)

        'Recalculate Monday's total
        intTotal = 0
        For intIndex As Integer = 0 To objTimeSheetDV.Count - 2
            objDataRowView = objTimeSheetDV.Item(intIndex)
            intTotal += objDataRowView.Item("MondayHours")
        Next
        'Update Monday's total
        objDataRowTotal.Item("MondayHours") = intTotal

        'Recalculate Tuesday's total
        intTotal = 0
        For intIndex As Integer = 0 To objTimeSheetDV.Count - 2
            objDataRowView = objTimeSheetDV.Item(intIndex)
            intTotal += objDataRowView.Item("TuesdayHours")
        Next
        'Update Tuesday's total
        objDataRowTotal.Item("TuesdayHours") = intTotal

        'Recalculate Wednesday's total
        intTotal = 0
        For intIndex As Integer = 0 To objTimeSheetDV.Count - 2
            objDataRowView = objTimeSheetDV.Item(intIndex)
            intTotal += objDataRowView.Item("WednesdayHours")
        Next
        'Update Wednesday's total
        objDataRowTotal.Item("WednesdayHours") = intTotal

        'Recalculate Thursday's total
        intTotal = 0
        For intIndex As Integer = 0 To objTimeSheetDV.Count - 2
            objDataRowView = objTimeSheetDV.Item(intIndex)
            intTotal += objDataRowView.Item("ThursdayHours")
        Next
        'Update Thursday's total
        objDataRowTotal.Item("ThursdayHours") = intTotal

        'Recalculate Friday's total
        intTotal = 0
        For intIndex As Integer = 0 To objTimeSheetDV.Count - 2
            objDataRowView = objTimeSheetDV.Item(intIndex)
            intTotal += objDataRowView.Item("FridayHours")
        Next
        'Update Friday's total
        objDataRowTotal.Item("FridayHours") = intTotal

        'Commit the changes to the total row
        objDataRowTotal.EndEdit()
    End Sub

    Private Sub cboEmployee_SelectedIndexChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles cboEmployee.SelectedIndexChanged

        strUserID = objEmployees.Tables("Employees").Rows( _
            cboEmployee.SelectedIndex).Item("UserID").ToString
    End Sub

    Private Sub cboEmployeeWeekEnding_SelectedIndexChanged( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles cboEmployeeWeekEnding.SelectedIndexChanged

        If blnLoading Then
            Exit Sub
        End If

        'Load the timesheet
        Call LoadTimeSheet(cboEmployeeWeekEnding)
    End Sub
#End Region
End Class