TimeSheets application webform

Rattlesnake · Dec 15, 2004

Hi,
I am new to ASP.Net
I am creating a TimeSheet web application for our company using ASP.Net.
I am using Sql Server 2000 as the database.

The timesheet will record the employees overtime hours for the whole month and will be filled at the end of each month.(Attached is an image of the time sheet that I need to convert into a web form)

The table structure that I have created is

Employee Table
--------------
EmployeeNumber
EmployeeName

TimeSheetMaster Table
----------------------
TimeSheetID
EmployeeNumber
Month
Year

TimeSheetDetails Table
----------------------
TimeSheetID
Day
OverTime_Hrs
OverTime_Category

I would like to know as to how I could replicate the timesheet into a webform. Shall I put a textbox for each field that the user has to input or is there any other control I could use.
Basically I need a textbox for each day of the month, where the employee will enter his Overtime Hours.
This set of textboxes will be repeated for each category of overtime as in 100%,150% and 200%

Is the database structure OK for the application I am trying to implement?

Thanks for ur help

Haak3n · Dec 15, 2004

The database structure seems good.

Personally I would:

Depending if they are loggin in or just filling it out....

Create a User Input section in which they would select the Date, Hours, Than Category.

Employee Name: [ Dropdownlist ] (not recommended, but can be done)
Date [ Textbox ]
Hours[ Textbox ]
Cat [ Dropdownlist]

[submit]

this submitting to your database.

Than another section for reports which would ask you for the Month and Employee #

Which would allow you to populate your datagrid for all your information along with an edit box to change any information.

[Datagrid]
Date Time Travel OT 150% OT200% Comp
12/1/2004 0 0 0 0 Edit
12/2/2004 4 4 0 0 Edit

Total 4 4 0 0

Nest that inside a table with other cells displaying the
Month, Employee Name, Cost, etc.

This seems how I would do things, others might suggest differently.

Rattlesnake · Dec 18, 2004

Hi,
Thanks Haak3n for the suggestion.
I know that would be the ideal solution, but we have decided that we would like the form to look similar to what it is now.
My actual problem is to display a blank form(not retrieving a record from the database) with the structure (that was attached).
When the user fills the form and submits it should enter the records according to the table structures that I have.
Is it possible to display this structure through a datalist/datagrid etc when actually there is no connection with the database. (coz initially the form is empty)

Thanks

Rattlesnake · Dec 25, 2004

Hi,
OK I have changed the database structure so that now each row will contain :
TimeSheetID-Day-150%-200%-CompensatoryDays-TravelTime.

Now I dont have any problems modifying already added TimeSheets.

The Only problem is to Add a new TimeSheet.
I want to display 31 rows(no of days in the month) and the above fields as columns.
How can I do this? (keeping in mind that the record is not added to the database yet.)

Thanks

rotsey · Dec 31, 2004

You can use a <table> to build the timesheet.

I will do it for a small fee.

rotsey

rotsey · Dec 31, 2004

Ok Rattlesnake you got me on a good day. Below is the code to build the timesheet using code.

Firstly add this bit of code to your html page

Code:

    <asp:Table id="Table2" GridLines="Both"  BorderColor="#000000" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 96px" runat="server"></asp:Table>

Then add this code to the code file.

Code:

	Dim mth As String = "February"

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		'Put user code to initialize the page here

		BuildTimesheet()

	End Sub

	Public Sub BuildTimesheet()

		Dim objTbl As Table
		Dim objRow As TableRow
		Dim objCell As TableCell
		Dim objLB As Label
		Dim objTB As TextBox
		Dim NumDays As Integer
		Dim cntr As Integer

		NumDays = Day(DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, CDate("01/" & mth & "/" & Date.Today.Year))))

		objTbl = Table2
		objTbl.CellSpacing = 0

		''---------------------------
		'' Heading Row for Day number
		''---------------------------
		objRow = New TableRow()
		objTbl.Rows.Add(objRow)
		objCell = New TableCell()


		objLB = New Label()
		objLB.Text = ""
		objLB.Width = Unit.Parse("160")
		objCell.Controls.Add(objLB)
		objRow.Cells.Add(objCell)

		For cntr = 1 To NumDays
			objLB = New Label()
			objLB.Text = cntr.ToString

			objCell = New TableCell()
			objCell.HorizontalAlign = HorizontalAlign.Center
			objCell.Controls.Add(objLB)
			objRow.Cells.Add(objCell)
		Next
		objLB = New Label()
		objLB.Text = "Total"
		objLB.Font.Bold = True
		objCell = New TableCell()
		objCell.HorizontalAlign = HorizontalAlign.Center
		objCell.Controls.Add(objLB)
		objRow.Cells.Add(objCell)

		''---------------------------
		'' Travel Time Row 
		''---------------------------
		objRow = New TableRow()
		objTbl.Rows.Add(objRow)
		objCell = New TableCell()
		objLB = New Label()
		objLB.Text = "Travel Time"
		objCell.Controls.Add(objLB)
		objRow.Cells.Add(objCell)

		For cntr = 1 To NumDays
			objTB = New TextBox()
			objTB.Width = Unit.Parse("40")
			objTB.BorderColor = Color.White
			objTB.BorderStyle = BorderStyle.None
			objTB.ID = "TT" & cntr.ToString
			objTB.Font.Bold = True
			objTB.AutoPostBack = True
			objTB.Attributes("style") = "TEXT-ALIGN: center"
			AddHandler objTB.TextChanged, AddressOf CalcTotals

			objCell = New TableCell()
			objCell.HorizontalAlign = HorizontalAlign.Center
			objCell.Controls.Add(objTB)
			objRow.Cells.Add(objCell)
		Next
		objTB = New TextBox()
		objTB.Width = Unit.Parse("60")
		objTB.BorderColor = Color.White
		objTB.BorderStyle = BorderStyle.None
		objTB.Font.Bold = True
		objTB.Attributes("style") = "TEXT-ALIGN: center"
		objTB.ID = "TTTOTAL"
		objCell = New TableCell()
		objCell.HorizontalAlign = HorizontalAlign.Center
		objCell.Controls.Add(objTB)
		objRow.Cells.Add(objCell)

		''---------------------------
		'' Over Time 150% Row 
		''---------------------------
		objRow = New TableRow()
		objTbl.Rows.Add(objRow)
		objCell = New TableCell()
		objLB = New Label()
		objLB.Text = "Overtime 150%"
		objCell.Controls.Add(objLB)
		objRow.Cells.Add(objCell)

		For cntr = 1 To NumDays
			objTB = New TextBox()
			objTB.Width = Unit.Parse("40")
			objTB.BorderColor = Color.White
			objTB.BorderStyle = BorderStyle.None
			objTB.ID = "OT150" & cntr.ToString
			objTB.Font.Bold = True
			objTB.AutoPostBack = True
			objTB.Attributes("style") = "TEXT-ALIGN: center"
			AddHandler objTB.TextChanged, AddressOf CalcTotals

			objCell = New TableCell()
			objCell.HorizontalAlign = HorizontalAlign.Center
			objCell.Controls.Add(objTB)
			objRow.Cells.Add(objCell)
		Next
		objTB = New TextBox()
		objTB.Width = Unit.Parse("60")
		objTB.BorderColor = Color.White
		objTB.BorderStyle = BorderStyle.None
		objTB.Font.Bold = True
		objTB.Attributes("style") = "TEXT-ALIGN: center"
		objTB.ID = "OT150TOTAL"
		objCell = New TableCell()
		objCell.HorizontalAlign = HorizontalAlign.Center
		objCell.Controls.Add(objTB)
		objRow.Cells.Add(objCell)

		''---------------------------
		'' Over Time 200% Row 
		''---------------------------
		objRow = New TableRow()
		objTbl.Rows.Add(objRow)
		objCell = New TableCell()
		objLB = New Label()
		objLB.Text = "Overtime 200%"
		objCell.Controls.Add(objLB)
		objRow.Cells.Add(objCell)

		For cntr = 1 To NumDays
			objTB = New TextBox()
			objTB.Width = Unit.Parse("40")
			objTB.BorderColor = Color.White
			objTB.BorderStyle = BorderStyle.None
			objTB.ID = "OT200" & cntr.ToString
			objTB.Font.Bold = True
			objTB.AutoPostBack = True
			objTB.Attributes("style") = "TEXT-ALIGN: center"
			AddHandler objTB.TextChanged, AddressOf CalcTotals

			objCell = New TableCell()
			objCell.HorizontalAlign = HorizontalAlign.Center
			objCell.Controls.Add(objTB)
			objRow.Cells.Add(objCell)
		Next
		objTB = New TextBox()
		objTB.Width = Unit.Parse("60")
		objTB.BorderColor = Color.White
		objTB.BorderStyle = BorderStyle.None
		objTB.Font.Bold = True
		objTB.Attributes("style") = "TEXT-ALIGN: center"
		objTB.ID = "OT200TOTAL"
		objCell = New TableCell()
		objCell.HorizontalAlign = HorizontalAlign.Center
		objCell.Controls.Add(objTB)
		objRow.Cells.Add(objCell)

		''---------------------------
		'' Compensatory Days Row 
		''---------------------------
		objRow = New TableRow()
		objTbl.Rows.Add(objRow)
		objCell = New TableCell()
		objLB = New Label()
		objLB.Text = "Compensatory Days"
		objCell.Controls.Add(objLB)
		objRow.Cells.Add(objCell)

		For cntr = 1 To NumDays
			objTB = New TextBox()
			objTB.Width = Unit.Parse("40")
			objTB.BorderColor = Color.White
			objTB.BorderStyle = BorderStyle.None
			objTB.ID = "CompDays" & cntr.ToString
			objTB.Font.Bold = True
			objTB.AutoPostBack = True
			objTB.Attributes("style") = "TEXT-ALIGN: center"
			AddHandler objTB.TextChanged, AddressOf CalcTotals

			objCell = New TableCell()
			objCell.HorizontalAlign = HorizontalAlign.Center
			objCell.Controls.Add(objTB)
			objRow.Cells.Add(objCell)
		Next
		objTB = New TextBox()
		objTB.Width = Unit.Parse("60")
		objTB.BorderColor = Color.White
		objTB.BorderStyle = BorderStyle.None
		objTB.Font.Bold = True
		objTB.Attributes("style") = "TEXT-ALIGN: center"
		objTB.ID = "CompDaysTotal"

		objCell = New TableCell()
		objCell.HorizontalAlign = HorizontalAlign.Center
		objCell.Controls.Add(objTB)
		objRow.Cells.Add(objCell)
	End Sub

	Public Sub CalcTotals(ByVal objSender As Object, ByVal objArgs As EventArgs)

		Dim tb As Table
		Dim ttl As Double
		Dim txt As TextBox
		Dim cntr As Integer
		Dim NumDays As Integer

		NumDays = Day(DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, CDate("01/" & mth & "/" & Date.Today.Year))))

		tb = Table2

		For cntr = 1 To NumDays
			txt = CType(tb.FindControl("TT" & cntr.ToString), TextBox)

			If txt.Text <> "" Then
				ttl += Val(txt.Text)
			End If
		Next
		txt = CType(tb.FindControl("TTTOTAL"), TextBox)
		txt.Text = ttl.ToString

		ttl = 0
		For cntr = 1 To NumDays
			txt = CType(tb.FindControl("OT150" & cntr.ToString), TextBox)

			If txt.Text <> "" Then
				ttl += Val(txt.Text)
			End If
		Next
		txt = CType(tb.FindControl("OT150TOTAL"), TextBox)
		txt.Text = ttl.ToString

		ttl = 0
		For cntr = 1 To NumDays
			txt = CType(tb.FindControl("OT200" & cntr.ToString), TextBox)

			If txt.Text <> "" Then
				ttl += Val(txt.Text)
			End If
		Next
		txt = CType(tb.FindControl("OT200TOTAL"), TextBox)
		txt.Text = ttl.ToString

		ttl = 0
		For cntr = 1 To NumDays
			txt = CType(tb.FindControl("CompDays" & cntr.ToString), TextBox)

			If txt.Text <> "" Then
				ttl += Val(txt.Text)
			End If
		Next
		txt = CType(tb.FindControl("CompDaysTOTAL"), TextBox)
		txt.Text = ttl.ToString

	End Sub

This code will build the timesheet using a <table> tag.

Let me know if you have any questions.

TimeSheets application webform

Rattlesnake

Freshman

Attachments

Haak3n

Newcomer

Rattlesnake

Freshman

Rattlesnake

Freshman

rotsey

Newcomer

rotsey

Newcomer