time in, lunch out, lunch in, time out 1

[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.

 

[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

 

[jmeckley]

this seems very labor intensive for the end user. Maybe there is a way to capture this information automatically so the user doesn't need to? Not sure.

another thought. do you really need to know whether the user is leaving for the day, or leaving for lunch. I would think all that's required is TimeIn and TimeOut. if that's the case this could simplify your process dramatically.

Only 2 buttons are required: PunchIn and PunchOut. In this scenario we do not care about why.

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;

The only hang up is what happens if the user leaves for the day and forgets to log out? Depending to the structure of the business. maybe all your employees leave at 5:00pm. If that's the case you could have a windows service running on the server as well. At 5:15 pm "force" the punch card by entering the time for all "neglected" punch cards.

or have the service send out a notification to their supervisor stating they did not log out. You would then need a workflow to "Punch Out" in the past. Maybe this is done by the supervisor so the employee cannot forge times.

There other possibilities as well, but they are all (including the options above) contingent or the requirements of the system and the business processes they will align to.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

Jason,

Man, you are good. I really need that extra pair of eyes to go through this. I will give this a try and will keep you postes.

Thanks
Do

 

[dorandoran]

Jason,

You really have been big help. I dont know how to thank you. Everything worked like a charm.

Again, Thanks a Lot...

 

[jmeckley]

wow, i wasn't expecting it to just work they were more concepts than implementations.

You really have been big help. I don't know how to thank you.

no need, when I first started programming TT was the first forum I found and I have stuck with it (habit). I'm at a new place now in my professional development and I want to give back to this community.

Here are my mentors, maybe they will help you as well.

http://www.jpboodhoo.com

his training is very intense. this is actually the course that changed my perspective on development.

http://www.ayende.com

some of his content is programatically esoteric

http://www.lostechis.com

more info on SOLID than you can imagine

http://www.codebetter.com

another great aggregation of .net minds

Books that also changed my approach to development;
Head First Design Patterns: intro to design patterns good starting point if you don't know anything about design patterns. it's not a complete solution though.
Domain Drive Design: the big blue book
C# via CLR: great technical understanding of what c# is doing.

there is more, but these are the biggest influences.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190