Timeclock application

dorandoran · #1 June 19th, 2009, 04:23 AM

I have a web form where I am about to put 4 buttons (time in, lunch out, lunch in, time out). When an emploee good enough and click all 4 buttons in a day then system should only create 2 records. (time in and time out goes together in a same record)

1. Employee will come in the morning and click on Time In button.
>>> Good, Simple insert into table
2. Employee will click on Lunch Out button.
>>> need to make sure my UPDATE (not insert) is updating the moring record when employee timed in.
3. Employee will clock in upon arriving from Lunch "Lunch In".
>>> Another insert statement however I need to make sure the prior record has lunch out punched in and also calculate hours and plug it in the first record. If "lunch out" is missing then flag the record (update status column "AUDIT") and display a message (response.write "something")
4. time out. Very critical. an employee may be going home early so he/she is not taking lunch. so "time out" then should check whether employee has a record with time in only and no time out and then update the record with the new time out value. another scenerio, employee took lunch but forgot to lunch out, and came back and just did lunch in. in that case you will have 2 records both with time in value populated and time out value empty. in that case the lunch in record's timeout column will be update.

I hope this makes sense. I need few ideas where should I write these business logic. In sql server or .net (create a class with bool factor and create object out of it). LOST. PLEASE suggest.

tlhintoq · #2 June 19th, 2009, 04:43 AM

I do desktop apps, not web apps so this may be useless but...

I did a security app that included a digital camera and employee badge readers. It had to have the same sort of thing, plus photos at clock-in and out as theft prevention. No buttons, just a reader for 'in' and a reader for 'out'. It was client/server not web.

In that case the clients just reported the times to the server, to do all the heavy thinking.

dorandoran · #3 June 19th, 2009, 05:34 AM

Thanks for your respond. Can you please share some code related to the business logic I posted? Also, client wants web based due to maintenance reasons.
Again, Thanks

dorandoran · #4 June 19th, 2009, 07:26 AM

so far i have this but it has some serious glitch.

Expand|Select|Wrap|Line Numbers

 set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go
 
alter PROCEDURE [dbo].[sp_timeOut] 

    -- Add the parameters for the stored procedure here

@emp_id int,

@clocking_date datetime,

@time_out datetime,

@record_status varchar(15),

@rec_operator varchar(30)
 
AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
IF EXISTS(

select * from clocking_details

where emp_id=@emp_id and clocking_date = @clocking_date and time_out is null

)

    IF(select count(1)  from clocking_details

        where emp_id=@emp_id and 

        clocking_date = @clocking_date and time_out is null) =1

        update clocking_details

        set time_out=@time_out

        where (emp_id=@emp_id and clocking_date = @clocking_date and time_out is null)

    ELSE

    IF(select count(1)  from clocking_details

        where emp_id=@emp_id and 

        clocking_date = @clocking_date and time_out is null) >1

        update clocking_details

        set time_out=@time_out

        where DETAIL_NUMBER = (SELECT MAX(DETAIL_NUMBER) FROM CLOCKING_DETAILS

        where emp_id=@emp_id and clocking_date = @clocking_date    and time_out is null)

    RETURN
 
END

tlhintoq · #5 June 19th, 2009, 07:31 AM

Quote:

Originally Posted by tlhintoq

I do desktop apps, not web apps so this may be useless but...

I did a security app that included a digital camera and employee badge readers. It had to have the same sort of thing, plus photos at clock-in and out as theft prevention. No buttons, just a reader for 'in' and a reader for 'out'. It was client/server not web.

In that case the clients just reported the times to the server, to do all the heavy thinking.

I'm afraid not: Its production code and in place at a customer site. My boss is really particular about such things.

Besides, it probably wouldn't do much good. There is no database to query along the format you have in your example.

dorandoran · #6 June 19th, 2009, 02:16 PM

That's kool. I was just going to get an idea. But it's kool. I am progressing.

Thanks.....

Frinavale · #7 June 19th, 2009, 02:39 PM

What database management system are you using?

I would recommend creating all of your stored procedures before attempting to do the web portion of the application. You should test them thoroughly before using them in your app.

Frinavale · #8 June 19th, 2009, 02:40 PM

Quote:

Originally Posted by tlhintoq

I did a security app that included a digital camera and employee badge readers. It had to have the same sort of thing, plus photos at clock-in and out as theft prevention. No buttons, just a reader for 'in' and a reader for 'out'. It was client/server not web.

In that case the clients just reported the times to the server, to do all the heavy thinking.

I do that every day :)
For the web even :)

dorandoran · #9 June 19th, 2009, 02:47 PM

Only 2 buttons are required: PunchIn and PunchOut.

The table would look something like this.

Expand|Select|Wrap|Line Numbers

 
PunchCard

----------

Id (PK)

UserId (FK)

TimeIn

TimeOut

the logic would be simple.

Expand|Select|Wrap|Line Numbers

 
sqlCommand.CommandText = @"select count(1) from PunchCard where UserId = @user and TimeOut is null";

//set user id

var result = (int)sqlCommand.ExecuteScalar();

PunchInButton.Enabled = result == 0;

PunchOutButton.Enabled = result == 1;

r035198x · #10 June 24th, 2009, 11:32 AM

Why should there be two records?
Why not just one record for the whole process and have the four columns time in, lunch out, lunch in and time out in the table?

Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

re: Timeclock application

/bytes/development

/bytes/it

/bytes/about