timesheet.aspx.vb

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

Imports System.Data

Partial Class TimeSheet
    Inherits System.Web.UI.Page

    'Private variables and objects
    Private intIndex As Integer
    Private intTotal As Integer

    Private blnEvenRow As Boolean = True

    Private strCompany As String = "Wrox"
    Private strApplication As String = "Time Tracker SQL"

    Private dteWeekEndingDate As Date

    Private objTimeSheets As WroxBusinessLogic.WBLTimeSheets
    Private objDates As New Dates

    Private objTimeSheetDS As DataSet

    Protected Sub Page_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Load

        If IsPostBack Then
            'Get the week ending date
            dteWeekEndingDate = CType(ViewState("WeekEndingDate"), Date)
            'Get the timesheet DataSet
            objTimeSheetDS = CType(ViewState("TimeSheetDS"), DataSet)

            'Update the timesheet DataSet
            Call UpdateTimeSheetDS()
        Else
            'Set the default week ending date
            dteWeekEndingDate = CType(objDates.GetCurrentWeekEndingDate, Date)

            'Load the week ending dates in the form
            cboWeekEndingDate.Items.Add(objDates.GetPreviousWeekEndingDate)
            cboWeekEndingDate.Items.Add(objDates.GetCurrentWeekEndingDate)
            cboWeekEndingDate.SelectedIndex = 1

            'Get the timesheet
            Call GetTimeSheet()

            'Save the data between requests
            ViewState("TimeSheetDS") = objTimeSheetDS
            ViewState("WeekEndingDate") = dteWeekEndingDate
        End If
    End Sub

    Private Sub GetTimeSheet()
        '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(Request.QueryString("UserID")), dteWeekEndingDate)

            'Initialize a new DataRow object
            Dim objDataRow As DataRow = objTimeSheetDS.Tables("TimeSheet").NewRow

            'Set the values in the columns
            objDataRow.Item("ProjectName") = "Total Hours"

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

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

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

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

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

            'Add the row to the DataSet
            objTimeSheetDS.Tables("TimeSheet").Rows.Add(objDataRow)
        End Using
    End Sub

    Public Sub DisplayTimeSheet()
        'Begin table
        Response.Write("<table cellpadding=""3"" cellspacing=""0"" " & _
            "bordercolor=""Black"" border=""1""" & _
            "class=""TimeSheetTable"" >")
        Response.Write("<tr class=""TimeSheetTableHeader"">")
        Response.Write("<td nowrap style=""width:200px;"">Project Name</td>")
        Response.Write("<td style=""width:30px;"">Mon</td>")
        Response.Write("<td style=""width:30px;"">Tue</td>")
        Response.Write("<td style=""width:30px;"">Wed</td>")
        Response.Write("<td style=""width:30px;"">Thu</td>")
        Response.Write("<td style=""width:30px;"">Fri</td>")
        Response.Write("</tr>")

        'Process all rows of data
        For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 2
            WriteTimeSheetRow(objTimeSheetDS.Tables("TimeSheet").Rows(intIndex))
        Next

        'Write the total row
        blnEvenRow = Not blnEvenRow
        If blnEvenRow Then
            Response.Write("<tr class=""EvenRow"">")
        Else
            Response.Write("<tr class=""OddRow"">")
        End If
        Response.Write("<td nowrap>Total Hours</td>")
        Response.Write("<td><INPUT type=""text"" name=""txtMondayTotal""" & _
            "class=""TransparentTextBox"" readonly " & _
            "onfocus=""javascript:txtMondayHours[0].select();" & _
            "txtMondayHours[0].focus();"" value=""" & _
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "MondayHours") & """></td>")
        Response.Write("<td><INPUT type=""text"" name=""txtTuesdayTotal""" & _
            "class=""TransparentTextBox"" readonly value=""" & _
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "TuesdayHours") & """></td>")
        Response.Write("<td><INPUT type=""text"" name=""txtWednesdayTotal""" & _
            "class=""TransparentTextBox"" readonly value=""" & _
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "WednesdayHours") & """></td>")
        Response.Write("<td><INPUT type=""text"" name=""txtThursdayTotal""" & _
            "class=""TransparentTextBox"" readonly value=""" & _
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "ThursdayHours") & """></td>")
        Response.Write("<td><INPUT type=""text"" name=""txtFridayTotal""" & _
            "class=""TransparentTextBox"" readonly value=""" & _
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "FridayHours") & """></td>")

        'End the table
        Response.Write("</tr>")
        Response.Write("</table>")
    End Sub

    Private Sub WriteTimeSheetRow(ByVal objDataRow As DataRow)
        'Toggle the flag
        blnEvenRow = Not blnEvenRow
        If blnEvenRow Then
            Response.Write("<tr class=""EvenRow"">")
        Else
            Response.Write("<tr class=""OddRow"">")
        End If
        Response.Write("<td nowrap>" & objDataRow.Item("ProjectName") & _
            "</td>")
        If objTimeSheetDS.Tables("TimeSheet").Rows(0).Item("Submitted") Then
            'The data is read-only so just display it
            Response.Write("<td style=""width:30px;"">" & _
                objDataRow.Item("MondayHours") & "</td>")
            Response.Write("<td style=""width:30px;"">" & _
                objDataRow.Item("TuesdayHours") & "</td>")
            Response.Write("<td style=""width:30px;"">" & _
                objDataRow.Item("WednesdayHours") & "</td>")
            Response.Write("<td style=""width:30px;"">" & _
                objDataRow.Item("ThursdayHours") & "</td>")
            Response.Write("<td style=""width:30px;"">" & _
                objDataRow.Item("FridayHours") & "</td>")
        Else
            Response.Write("<td><input type=""textbox"" style=""width:30px;"" " & _
                "name=""txtMondayHours"" value=""" & _
                objDataRow.Item("MondayHours") & _
                """ onBlur=""javascript:Recalculate('Monday');"" " & _
                "onkeydown=""javascript:IsNumeric();""></td>")
            Response.Write("<td><input type=""textbox"" style=""width:30px;"" " & _
                "name=""txtTuesdayHours"" value=""" & _
                objDataRow.Item("TuesdayHours") & _
                """ onBlur=""javascript:Recalculate('Tuesday');"" " & _
                "onkeydown=""javascript:IsNumeric();""></td>")
            Response.Write("<td><input type=""textbox"" style=""width:30px;"" " & _
                "name=""txtWednesdayHours"" value=""" & _
                objDataRow.Item("WednesdayHours") & _
                """ onBlur=""javascript:Recalculate('Wednesday');"" " & _
                "onkeydown=""javascript:IsNumeric();""></td>")
            Response.Write("<td><input type=""textbox"" style=""width:30px;"" " & _
                "name=""txtThursdayHours"" value=""" & _
                objDataRow.Item("ThursdayHours") & _
                """ onBlur=""javascript:Recalculate('Thursday');"" " & _
                "onkeydown=""javascript:IsNumeric();""></td>")
            Response.Write("<td><input type=""textbox"" style=""width:30px;"" " & _
                "name=""txtFridayHours"" value=""" & _
                objDataRow.Item("FridayHours") & _
                """ onBlur=""javascript:Recalculate('Friday');"" " & _
                "onkeydown=""javascript:IsNumeric();""></td>")
        End If
        Response.Write("</tr>")
    End Sub

    Private Sub UpdateTimeSheetDS()
        If Request.Form("txtMondayHours") Is Nothing Then
            Exit Sub
        End If
        Dim strMondayHours() As String = _
            Split(Request.Form("txtMondayHours"), ",")
        Dim strTuesdayHours() As String = _
            Split(Request.Form("txtTuesdayHours"), ",")
        Dim strWednesdayHours() As String = _
            Split(Request.Form("txtWednesdayHours"), ",")
        Dim strThursdayHours() As String = _
            Split(Request.Form("txtThursdayHours"), ",")
        Dim strFridayHours() As String = _
            Split(Request.Form("txtFridayHours"), ",")

        'Process all rows of data minus the total row
        For intIndex = 0 To objTimeSheetDS.Tables("TimeSheet").Rows.Count - 2
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
                "MondayHours") = CType(strMondayHours(intIndex), Byte)
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
                "TuesdayHours") = CType(strTuesdayHours(intIndex), Byte)
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
                "WednesdayHours") = CType(strWednesdayHours(intIndex), Byte)
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
                "ThursdayHours") = CType(strThursdayHours(intIndex), Byte)
            objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
                "FridayHours") = CType(strFridayHours(intIndex), Byte)
        Next

        'Now process the total row - this is needed for when we build a new
        'table to post back to the client
        objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "MondayHours") = CType(Request.Form("txtMondayTotal"), Byte)
        objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "TuesdayHours") = CType(Request.Form("txtTuesdayTotal"), Byte)
        objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "WednesdayHours") = CType(Request.Form("txtWednesdayTotal"), Byte)
        objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "ThursdayHours") = CType(Request.Form("txtThursdayTotal"), Byte)
        objTimeSheetDS.Tables("TimeSheet").Rows(intIndex).Item( _
            "FridayHours") = CType(Request.Form("txtFridayTotal"), Byte)
    End Sub

    Protected Sub cboWeekEndingDate_SelectedIndexChanged( _
        ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles cboWeekEndingDate.SelectedIndexChanged

        'If the date is different...
        If ViewState("WeekEndingDate").ToString <> _
            cboWeekEndingDate.SelectedValue Then

            'Set the new date from the Web form
            dteWeekEndingDate = CType(cboWeekEndingDate.SelectedValue, Date)

            'Get the timesheet
            Call GetTimeSheet()

            'Save the data between requests
            ViewState("TimeSheetDS") = objTimeSheetDS
            ViewState("WeekEndingDate") = dteWeekEndingDate
        End If
    End Sub

    Protected Sub btnSave_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnSave.Click

        'Initialize a new instance of the business logic component
        Using objTimeSheets As New WroxBusinessLogic.WBLTimeSheets( _
            strCompany, strApplication)
            Try
                'Save the timesheet changes
                If Not objTimeSheets.SaveTimeSheet(objTimeSheetDS) Then
                    Throw New Exception("Save TimeSheet Failed")
                End If
            Catch ExceptionErr As Exception
                Response.Write(ExceptionErr.Message)
            End Try
        End Using
    End Sub

    Protected Sub btnSubmit_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnSubmit.Click

        'Initialize a new instance of the business logic component
        Using objTimeSheets As New WroxBusinessLogic.WBLTimeSheets( _
            strCompany, strApplication)
            Try
                'Submit the timesheet
                If Not objTimeSheets.SubmitTimeSheet(New Guid( _
                    objTimeSheetDS.Tables("TimeSheet").Rows(0).Item( _
                    "TimeSheetID").ToString)) Then
                    Throw New Exception("Submit TimeSheet Failed")
                End If
                'Reload the timesheet so it becomes read-only
                Call GetTimeSheet()
            Catch ExceptionErr As Exception
                Response.Write(ExceptionErr.Message)
            End Try
        End Using
    End Sub
End Class