Timeclock application

[dorandoran]

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]

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]

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]

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

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]

@tlhintoq
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]

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

Thanks.....

[Frinavale]

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]

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

[dorandoran]

Only 2 buttons are required: PunchIn and PunchOut.

The table would look something like this.

PunchCard
----------
Id (PK)
UserId (FK)
TimeIn
TimeOut
 

the logic would be simple.

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]

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?