Simple TimeClock for Employees, accessing a MS SQL Server database, with the ability to import timesheets and create import profiles.
## Deliverables
1. Simple TimeClock for Employees, accessing a MS SQL Server database
2. Log/update entries into the TIMECLOCK table
3. When clocking in, check the TIMECLOCK_CONFIG table and log a new row into the TIMECLOCK_DATA table
4. When clocking out, set the "CLOCKED_OUT" flag in the current record in the TIMECLOCK_DATA data
5. Do not allow shifts longer than 24 hours. If user clocks in without previously clocking out, automatically clock out of the previous row by setting the "CLOCKED_OUT" flag but not the TIME_OUT field
6. Support importing of timeclock data into the TIMECLOCK_DATA table (most of the work in the project is here).
We would like to support barcode scanners and card swipe systems. Most of these systems have the capability of exporting the data to a text file. So we need to be able to import timeclock data from the text file into the TIMECLOCK_DATA table.
The user should be able to setup different profiles for importing. They need to be able to create one or more import profiles.
The system should have the capability of importing from a delimited text file, fixed length text file, xml file, or an excel file. The user should be able to set up as many profiles as they would like. Each profile should have the following options:
o? Profile name
o? Import type. ??" This would be set to one of the following:
? i.? Delimited text file (CSV)
? ii.? Fixed length text file.
? iii.? Xml file.
? iv.? Excel file.
o? Import schedule. ??" This would allow the user to specify how the import is initiated:
? i.? Manually, by pressing a button. When the user presses the button they should be able to specify the path to the file and the profile to use when importing the file.
? ii.? Directory watch. The user can specify a directory for the software to watch. If any file is found in that directory then it will automatically be imported.
? iii.? Scheduled import. ??" The user can set up a schedule to specify when the import should be automatically started. The user would also need to set the path to the file for this option. If the file does not exist when the system tried to import the file then the user should be alerted that the file could not be found and be given the option to specify an alternate location to import the file from.
o? Import definition ??"This would allow the user to set up the definition of the file. This is also where the user would map every field in the import to a field in the software.
o? Log file. ??" All import activity should be logged. This would allow the user to set the location of the log file. If the user does not specify a location then a file with the name “[login to view URL]? should be created in the application directory. All logged activity should be appended to this file. It should not ever be overwritten.
8. Create Import Profile Creation Dialog, that will allow user to create a scheduler import "profile", specify the Profile name, type, schedule, and definition. All this information will be stored in the timeclock_profiles table.
Bonus - support fingerprint scanners as a source for clocking in/out a plus. Bonus given if you have done this before and you can easily add support for this. This is not a neccessity for completion of the project.
I've included a sample image but your result DOES NOT HAVE TO LOOK LIKE THIS. This is just an example of what components need to be there, from the same dialog. Clicking on the "new" button launches the create profile dialog.
Some schema details:
Here are the table schemas you can use as a starting point:
CREATE TABLE [dbo].[timeclock_config](
? [USER_ID] [int] NOT NULL,
? [PASSWORD] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
? [IMPORT_PROFILE] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[timeclock_data](
? [USER_ID] [int] NOT NULL,
? [TIMESTAMP] [timestamp] NOT NULL,
? [TIME_IN] [datetime] NOT NULL,
? [TIME_OUT] [datetime] NULL,
? [CLOCKED_OUT] [bit] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[timeclock_profiles](
? [IMPORT_PROFILE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
? [IMPORT_TYPE] [int] NOT NULL,
? [IMPORT_DEFINITION] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
? [IMPORT_SCHEDULE] [int] NOT NULL,
? [IMPORT_SCHEDULE_TIME] [smalldatetime] NULL,
? [IMPORT_SCHEDULE_INTERVAL] [int] NULL,
? [IMPORT_DIRECTORY] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]