Example Solutions: The Time Sheet

	Example Solutions: The Time Sheet

Introduction

A time sheet is a list that holds the time values worked by an employee or a contractor over a time period. In many companies, employees or contractors are usually asked to use a piece of paper on which they should record the necessary information for one or two weeks worth of the time they spent performing a duty. Nowadays, most companies use an electronic means of collecting the time. In an Intranet, employees may have to access an application from a shared drive, open the appropriate document, fill out their time sheet, and submit it.

A Paper Time Sheet

A paper time sheet is a physical list of values on a piece of paper. An employee would usually keep the paper that he or she can pick up at any time and modify it various times during the time period. If people from the accounting or payroll department wants to examine the time sheet of one particular employee, they would have to call the employee and request it. This is can be difficult or problematic if the employee and the company's accounting are not physically close, which is not uncommon nowadays (it is not surprising anymore for somebody located in New York to work for an employer who resides in San Francisco, or for a contractor in Adelaide to work for a company in Sydney). When the time period is over, such as at the end of the week or at the end of the two-week period, the employee can submit the time sheet. The time sheet is then sent to the accounting or payroll department.

An Electronic Time Sheet

An electronic time sheet is accessed using a computer. Usually, all employees time sheets are stored somewhere in a database on a computer. As stated earlier, an employee can access it any time, so can the accounting or payroll department. This means that, at any time, a supervisor can check the time sheet, for any reason. To make this possible, an application, namely a database is created, stored somewhere in a common computer such as a server, and given access to those who can use it.

One of the advantages of using paper time sheet is that, since the employee keeps the time sheet, there is no risk of having a duplicate time sheet. On the other hand, if you create an electronic table of time sheets, when an employee who wants to fill out his or her time sheet opens it, you need to make sure that the right time sheet is opened. In the same way, if somebody from the payroll department wants to check one particular employee's time sheet, you need to make it possible and easy to locate the right time sheet.

In the solution we are going to apply, we will create a table of employees and the time sheet they can fill out. In our time sheet, we will create a certain column, named TimeSheetCode, that will hold a unique number. We will come back to the role of this column.

Practical Learning: Introducing the Time Sheet

If you want to practice with this exercise, start Microsoft SQL Server with the SQL Server Management Studio and connect to the server

On the main menu, click File -> New -> Query With Current Connection and type the following:

-- =============================================
-- Database:     ynb1
-- For:          Yugo National Bank
-- Author:       FunctionX
-- Date Created: Monday 24 April 2007
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'ynb1'
)
DROP DATABASE ynb1
GO
CREATE DATABASE ynb1
GO
-- =========================================
-- Database: ynb1
-- For:      Yugo National Bank
-- Table:    Employees
-- =========================================
USE ynb1;
GO
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
  DROP TABLE dbo.Employees
GO
CREATE TABLE Employees
(
    EmployeeID int Identity(1,1) NOT NULL,
    EmployeeNumber varchar(20),
    FirstName varchar(20) NULL,
    LastName varchar(20) NOT NULL,
    Title varchar(80) NULL,
    HourlySalary smallmoney NULL,
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
GO

INSERT INTO Employees(EmployeeNumber, FirstName,
    LastName, Title, HourlySalary)
VALUES('88024', 'Matthew', 
       'Larson', 'Account Manager' , 32.15);
GO
INSERT INTO Employees(EmployeeNumber, FirstName,
    LastName, Title, HourlySalary)
VALUES('25711', 'Patricia', 
       'Katts', 'Regional Manager' , 42.15);
GO
INSERT INTO Employees(EmployeeNumber, FirstName,
    LastName, Title, HourlySalary)
VALUES('20410', 'Helene', 
       'Boileau', 'Cashier' , 12.15);
GO
INSERT INTO Employees(EmployeeNumber, FirstName,
    LastName, Title, HourlySalary)
VALUES('91272', 'Peter', 
       'Ulm', 'Head Cashier' , 22.15);
GO
INSERT INTO Employees(EmployeeNumber, FirstName,
    LastName, Title, HourlySalary)
VALUES('29475', 'Victoria', 
       'Canston', 'Cashier' , 14.25);
GO
-- =========================================
-- Database: ynb1
-- For:      Yugo National Bank
-- Table:    TimeSheets
-- =========================================
IF OBJECT_ID('dbo.TimeSheets', 'U') IS NOT NULL
  DROP TABLE dbo.TimeSheets
GO

CREATE TABLE dbo.TimeSheets
(
    TimeSheetID int identity(1,1) NOT NULL, 
    EmployeeNumber varchar(5) NOT NULL, 
    StartDate datetime NOT NULL,
    TimeSheetCode varchar(15),
    Week1Monday varchar(6),
    Week1Tuesday varchar(6),
    Week1Wednesday varchar(6),
    Week1Thursday varchar(6),
    Week1Friday varchar(6),
    Week1Saturday varchar(6),
    Week1Sunday varchar(6),
    Week2Monday varchar(6),
    Week2Tuesday varchar(6),
    Week2Wednesday varchar(6),
    Week2Thursday varchar(6),
    Week2Friday varchar(6),
    Week2Saturday varchar(6),
    Week2Sunday varchar(6),
    Notes text,
    CONSTRAINT PK_TimeSheets PRIMARY KEY(TimeSheetID)
)
GO
INSERT INTO TimeSheets(EmployeeNumber, StartDate, TimeSheetCode,
    Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday,
    Week1Friday, Week1Saturday, Week1Sunday, Week2Monday,
    Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday,
    Week2Saturday, Week2Sunday, Notes)
VALUES('20410', '2007/01/01', '2041020070101', '0.00', '8.50', 
       '9.50', '8.50', '9.00', '0.00', '0.00', '10.00', '9.50',
       '8.50', '10.50', '9.00', '0.00', '0.00', 'Nothing to signal');
GO
INSERT INTO TimeSheets(EmployeeNumber, StartDate, TimeSheetCode,
    Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday,
    Week1Friday, Week1Saturday, Week1Sunday, Week2Monday,
    Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday,
    Week2Saturday, Week2Sunday)
VALUES('88024', '2007/01/01', '8802420070101', '0.00', '4.00', 
       '6.00', '5.50', '6.50', '0.00', '0.00', '4.00', '6.00',
       '6.50', '4.00', '5.50', '0.00', '0.00');
GO
INSERT INTO TimeSheets(EmployeeNumber, StartDate, TimeSheetCode,
    Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday,
    Week1Friday, Week1Saturday, Week1Sunday, Week2Monday,
    Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday,
    Week2Saturday, Week2Sunday)
VALUES('94272', '2007/01/15', '9427220070115', '8.50', '8.00', 
       '9.00', '8.50', '9.50', '0.00', '0.00', '5.50', '6.50',
       '4.50', '6.00', '4.00', '0.00', '0.00');
GO
INSERT INTO TimeSheets(EmployeeNumber, StartDate, TimeSheetCode,
    Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday,
    Week1Friday, Week1Saturday, Week1Sunday, Week2Monday,
    Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday,
    Week2Saturday, Week2Sunday)
VALUES('88024', '2007/01/15', '8802420070115', '8.00', '8.50', 
       '9.50', '9.50', '8.50', '0.00', '0.00', '10.00', '9.00',
       '8.50', '8.00', '8.50', '0.00', '0.00');
GO
INSERT INTO TimeSheets(EmployeeNumber, StartDate, TimeSheetCode,
    Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday,
    Week1Friday, Week1Saturday, Week1Sunday, Week2Monday,
    Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday,
    Week2Saturday, Week2Sunday)
VALUES('20410', '2007/01/15', '2041020070115', '8.00', '8.00', 
       '6.00', '8.00', '6.00', '0.00', '0.00', '8.00', '8.00',
       '8.00', '8.50', '8.00', '0.00', '0.00');
GO

Press F5 to execute the statement

Time Sheet Implementation

To address our problem of an electronic time, we will create a time sheet in which two pieces of information are required: an employee's number and a starting period. After an employee has opened a time sheet:

The employee must first provide an employee number, which we will check in the Employees table. If the employee provides a valid employee number, we can continue with the time sheet. If the employee number is invalid, we will let the user know and we cannot continue with the time sheet
After the employee has provided a valid employee number, we will request the starting period. After entering a (valid) date, we will check the time. If there is a record that holds both the employee number and the start date, this means that the employee had previously worked on a time sheet and we will open that existing time sheet.

After the the employee or contractor has entered a valid employee number and a start date, we will create a number called a time sheet code, represented in the TimeSheet as the TimeSheetCode column. This number is created as follows:

0000000000000

The first 5 digits represent the employee's number. The second 4 digits represent the year of the start date. The next 2 digits represent the month, and the last 2 digits represent the day. This number must be unique so that there would not be a duplicate number throughout the time sheet.

To make sure the value of the TimeSheetCode is unique for each record, after the employee has provided a valid employee number and a start date, we will create the time sheet code and check if that number exists in the TimeSheet table already:

If that number exists already, this means that the employee has previously worked on that time sheet and he or she simply wants to verify or update it. We will then open the time values for that record and let the user view or change it
If there is no record with the specified time sheet code, we will conclude that the employee is working on a new time sheet

Practical Learning: Implementing the Time Sheet

Start Microsoft Visual Basic and create a Windows Application named YNBTimeSheet1
In the Solution Explorer, right-click Form1.vb and click Rename
Name it as TimeSheet.vb and press Enter

Design the form as follows:

Control	Text	Name	Other Properties
Label	Employee #:
TextBox		txtEmployeeNumber
Label	.	lblEmployeeNumber
Label	Start Date:
DateTimePicker		dtpStartDate
Label	End Date:
Label	.	lblEndDate
Label	Mon
Label	Tue
Label	Wed
Label	Thu
Label	Fri
Label	Sat
Label	Sun
Label	Week 1:
TextBox	0.00	txtWeek1Monday	TextAlign: Right
TextBox	0.00	txtWeek1Tuesday	TextAlign: Right
TextBox	0.00	txtWeek1Wednesday	TextAlign: Right
TextBox	0.00	txtWeek1Thursday	TextAlign: Right
TextBox	0.00	txtWeek1Friday	TextAlign: Right
TextBox	0.00	txtWeek1Saturday	TextAlign: Right
TextBox	0.00	txtWeek1Sunday	TextAlign: Right
Label	Week 2:
TextBox	0.00	txtWeek2Monday	TextAlign: Right
TextBox	0.00	txtWeek2Tuesday	TextAlign: Right
TextBox	0.00	txtWeek2Wednesday	TextAlign: Right
TextBox	0.00	txtWeek2Thursday	TextAlign: Right
TextBox	0.00	txtWeek2Friday	TextAlign: Right
TextBox	0.00	txtWeek2Saturday	TextAlign: Right
TextBox	0.00	txtWeek2Sunday	TextAlign: Right
Label	Notes
TextBox		txtNotes	Multiline: true
Button	Submit	btnSubmit
Button	Close	btnClose

Right-click the form and click View Code

Just above the first line of code, type Imports System.Data.SqlClient and press Enter

In the Class Name combo box, select txtEmployeeNumber

In the Method combo box, select Leave and implement its event as follows:

Imports System.Data.SqlClient

Public Class TimeSheet

    Dim ValidTimeSheet As Boolean
    Dim bNewRecord As Boolean
    Dim strTimeSheetCode As String

    Private Sub txtEmployeeNumber_Leave(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles txtEmployeeNumber.Leave
        Dim strSelect As String
        Dim sda As SqlDataAdapter
        Dim dsEmployees As DataSet
        Dim cmdDatabase As SqlCommand
        Dim conDatabase As SqlConnection

        Dim strFullName As String

        If txtEmployeeNumber.Text = "" Then
            ValidTimeSheet = False
            Return
        End If

        strSelect = _
		"SELECT * FROM Employees WHERE EmployeeNumber = '" & _
                txtEmployeeNumber.Text & "'"

        conDatabase = New SqlConnection( _
		"Data Source=(local)Database=ynb1" & _
         "Integrated Security=sspi")

        cmdDatabase = New SqlCommand(strSelect, conDatabase)
        dsEmployees = New DataSet
        sda = New SqlDataAdapter

        sda.SelectCommand = cmdDatabase
        sda.Fill(dsEmployees)

        Try
            Dim recEmployee As DataRow = dsEmployees.Tables(0).Rows(0)

            If recEmployee.IsNull("EmployeeNumber") Then
                ValidTimeSheet = False
                Throw New System.IndexOutOfRangeException( _
			"Bad Employee Number!")
       Else
                strFullName = _
               CStr(recEmployee("FirstName")) & _
                   " " & CStr(recEmployee("LastName"))
                lblFullName.Text = "Welcome " & strFullName
                ValidTimeSheet = True
            End If
        Catch exc As IndexOutOfRangeException
            ValidTimeSheet = False
            lblFullName.Text = ""
            MsgBox("There is no employee with that number!")
            txtEmployeeNumber.Text = ""
            btnClose.Focus()
        End Try

        dtpStartDate.Value = DateTime.Today

        txtWeek1Monday.Text = "0.00"
        txtWeek1Tuesday.Text = "0.00"
        txtWeek1Wednesday.Text = "0.00"
        txtWeek1Thursday.Text = "0.00"
        txtWeek1Friday.Text = "0.00"
        txtWeek1Saturday.Text = "0.00"
        txtWeek1Sunday.Text = "0.00"

        txtWeek2Monday.Text = "0.00"
        txtWeek2Tuesday.Text = "0.00"
        txtWeek2Wednesday.Text = "0.00"
        txtWeek2Thursday.Text = "0.00"
        txtWeek2Friday.Text = "0.00"
        txtWeek2Saturday.Text = "0.00"
        txtWeek2Sunday.Text = "0.00"

        conDatabase.Close()
    End Sub
End Class

In the Class Name combo box, select dtpStartDate

In the Method Name combo box, select CloseUp and implement the event as follows:

Private Sub dtpStartDate_CloseUp(ByVal sender As Object, _
		ByVal e As System.EventArgs) Handles dtpStartDate.CloseUp
    lblEndDate.Text = dtpStartDate.Value.AddDays(14).ToString()

    Dim iDay As Integer
    Dim strDay As String
    Dim strSQL As String
    Dim iMonth As Integer
    Dim strMonth As String
    Dim dteStart As DateTime
    Dim dsTimeSheet As DataSet
    Dim cmdTimeSheet As SqlCommand
    Dim conTimeSheet As SqlConnection
    Dim sdaTimeSheet As SqlDataAdapter

    If txtEmployeeNumber.Text = "" Then
        ValidTimeSheet = False
        Return
    End If

    dteStart = CDate(dtpStartDate.Value)
    iMonth = CInt(dteStart.Month)
    iDay = CInt(dteStart.Day)

    If iMonth < 10 Then
        strMonth = CStr(dteStart.Year) & "0" & CStr(iMonth)
    Else
        strMonth = CStr(dteStart.Year) & CStr(iMonth)
    End If

    If iDay < 10 Then
        strDay = strMonth & "0" & CStr(iDay)
    Else
        strDay = strMonth & CStr(iDay)
    End If

    strTimeSheetCode = txtEmployeeNumber.Text & strDay

    strSQL = "SELECT * FROM dbo.TimeSheets WHERE TimeSheetCode = '" & _
    		strTimeSheetCode & "'"

    conTimeSheet = _
	New SqlConnection("Data Source=(local);Database='ynb1';" & _
	     "Integrated Security=true")
    cmdTimeSheet = New SqlCommand(strSQL, conTimeSheet)

    dsTimeSheet = New DataSet("TimeSheetSet")
    sdaTimeSheet = New SqlDataAdapter
    sdaTimeSheet.SelectCommand = cmdTimeSheet
    sdaTimeSheet.Fill(dsTimeSheet)

    conTimeSheet.Close()

    Try
        Dim recTimeSheet As DataRow = dsTimeSheet.Tables(0).Rows(0)
        strTimeSheetCode = CStr(recTimeSheet("TimeSheetCode"))

        If recTimeSheet.IsNull("TimeSheetCode") Then
            Throw New System.IndexOutOfRangeException( _
		"No TimeSheet with that number exists!")
            bNewRecord = True
            Return
        Else
            txtWeek1Monday.Text = CStr(recTimeSheet("Week1Monday"))
            txtWeek1Tuesday.Text = CStr(recTimeSheet("Week1Tuesday"))
            txtWeek1Wednesday.Text = CStr(recTimeSheet("Week1Wednesday"))
            txtWeek1Thursday.Text = CStr(recTimeSheet("Week1Thursday"))
            txtWeek1Friday.Text = CStr(recTimeSheet("Week1Friday"))
            txtWeek1Saturday.Text = CStr(recTimeSheet("Week1Saturday"))
            txtWeek1Sunday.Text = CStr(recTimeSheet("Week1Sunday"))

            txtWeek2Monday.Text = CStr(recTimeSheet("Week2Monday"))
            txtWeek2Tuesday.Text = CStr(recTimeSheet("Week2Tuesday"))
            txtWeek2Wednesday.Text = CStr(recTimeSheet("Week2Wednesday"))
            txtWeek2Thursday.Text = CStr(recTimeSheet("Week2Thursday"))
            txtWeek2Friday.Text = CStr(recTimeSheet("Week2Friday"))
            txtWeek2Saturday.Text = CStr(recTimeSheet("Week2Saturday"))
            txtWeek2Sunday.Text = CStr(recTimeSheet("Week2Sunday"))

            bNewRecord = False
        End If
    Catch exc As IndexOutOfRangeException
        txtWeek1Monday.Text = "0.00"
        txtWeek1Tuesday.Text = "0.00"
        txtWeek1Wednesday.Text = "0.00"
        txtWeek1Thursday.Text = "0.00"
        txtWeek1Friday.Text = "0.00"
        txtWeek1Saturday.Text = "0.00"
        txtWeek1Sunday.Text = "0.00"

        txtWeek2Monday.Text = "0.00"
        txtWeek2Tuesday.Text = "0.00"
        txtWeek2Wednesday.Text = "0.00"
        txtWeek2Thursday.Text = "0.00"
        txtWeek2Friday.Text = "0.00"
        txtWeek2Saturday.Text = "0.00"
        txtWeek2Sunday.Text = "0.00"

        bNewRecord = True
    End Try
End Sub

In the Class Name combo box, select btnTimeSheet

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnTimeSheet_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnTimeSheet.Click
        Dim cmdTimeSheet As SqlCommand
        Dim conTimeSheet As SqlConnection

        Dim strTimeSheet As String = ""

        ' If this is new record, then create a new time sheet
        If bNewRecord = True Then
            strTimeSheet = "INSERT INTO dbo.TimeSheets(" & _
                           "TimeSheetCode, EmployeeNumber, StartDate, " & _
                           "Week1Monday, Week1Tuesday, " & _
                           "Week1Wednesday, Week1Thursday, " & _
                           "Week1Friday, Week1Saturday, Week1Sunday, " & _
                         "Week2Monday, Week2Tuesday, Week2Wednesday, " & _
                         "Week2Thursday, Week2Friday, Week2Saturday, " & _
                           "Week2Sunday, Notes) VALUES('" & _
                     strTimeSheetCode & "', '" & txtEmployeeNumber.Text & _
                     "', '" & dtpStartDate.Value.ToString("MM/dd/yyyy") & _
                         "', '" & txtWeek1Monday.Text & "', '" & _
                           txtWeek1Tuesday.Text & "', '" & _
                           txtWeek1Wednesday.Text & "', '" & _
                           txtWeek1Thursday.Text & "', '" & _
                           txtWeek1Friday.Text & "', '" & _
                           txtWeek1Saturday.Text & "', '" & _
                           txtWeek1Sunday.Text & "', '" & _
                   txtWeek2Monday.Text & "', '" & txtWeek2Tuesday.Text & _
                       "', '" & txtWeek2Wednesday.Text & "', '" & _
                   txtWeek2Thursday.Text & "', '" & txtWeek2Friday.Text & _
                           "', '" & txtWeek2Saturday.Text & "', '" & _
                     txtWeek2Sunday.Text & "', '" & txtNotes.Text & "')"
        End If

        ' If this is an existing record, then, only update it
        If bNewRecord = False Then
            strTimeSheet = "UPDATE TimeSheets SET Week1Monday = '" & _
                           txtWeek1Monday.Text & "', Week1Tuesday = '" & _
                       txtWeek1Tuesday.Text & "', Week1Wednesday = '" & _
                       txtWeek1Wednesday.Text & "', Week1Thursday = '" & _
                           txtWeek1Thursday.Text & "', Week1Friday = '" & _
                           txtWeek1Friday.Text & "', Week1Saturday = '" & _
                           txtWeek1Saturday.Text & "', Week1Sunday = '" & _
                           txtWeek1Sunday.Text & "', Week2Monday = '" & _
                           txtWeek2Monday.Text & "', Week2Tuesday = '" & _
                       txtWeek2Tuesday.Text & "', Week2Wednesday = '" & _
                       txtWeek2Wednesday.Text & "', Week2Thursday = '" & _
                       txtWeek2Thursday.Text & "', Week2Friday = '" & _
                           txtWeek2Friday.Text & "', Week2Saturday = '" & _
                           txtWeek2Saturday.Text & "', Week2Sunday = '" & _
                           txtWeek2Sunday.Text & "', Notes = '" & _
                           txtNotes.Text & "' WHERE TimeSheetCode = '" & _
                           strTimeSheetCode & "'"
        End If

        If ValidTimeSheet = True Then
            conTimeSheet = New SqlConnection( _
                     "Data Source=(local);Database='ynb1';" & _
             "Integrated Security=true")
            cmdTimeSheet = New SqlCommand(strTimeSheet, conTimeSheet)

            conTimeSheet.Open()
            cmdTimeSheet.ExecuteNonQuery()
            conTimeSheet.Close()

            MsgBox("Your time sheet has been submitted")
        Else
            MsgBox("The time sheet is not valid\n" & _
                   "either you didn't enter a valid employee number, " & _
                   "or you didn't providea valid start date\n" & _
                   "The time sheet will not be saved")
        End If
    End Sub

In the Class Name combo box, select btnClose

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClose.Click
        End
End Sub

Execute the application to test it


Home	Copyright © 2007-2009 FunctionX, Inc.