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 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, t hey 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 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.

1. If you want to practice with this exercise, start Microsoft SQL Server with the SQL Server Management Studio and connect to the server
2. 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

3. Press F5 to execute the statement

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:

1. 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
2. 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

1. Start Microsoft Visual C++ and create a Windows Forms Application named YNBTimeSheet1
2. Before implementing our time sheet, 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

3. Click the txtEmployeeNumber text box to select it
4. In the Properties window, click the Events button and generate the event of the Leave field
5. Return to the form and click the dtpStartDate DateTimePicker
6. In the Events section of the Properties window, generate the CloseUp event
7. Return to the form and double-click the Submit button
8. Return to the form and double-click the Close button
9. Change the file as follows: