Programming Forums > Projects > Existing Project Development

gj15987 · Aug 18th, 2005, 10:59 AM

I need to create a basic payroll system where an employee can enter their PIN and clock in or clock out. Then the program needs to calculate how many hours they've worked.

I was just wondering how people would go about this?

I have come up with this, but i'm sure there has to be a better way.

Tbl_Employees(Employee_ID, Employee_Name, Rate_Per_Hour, DOB)

Tbl_Shift(Shift_ID, Time_In, Time_Out, Break_In, Break_Out, Date_, Employee_ID)

Right, the employee opens up the program and enters their ID. They then click clock in. This executes an sql to create a new record with the shift ID, time in, date and employee ID.

When they clock out, they again enter their number and click clock out. This will execute an sql to update a record where date=today and employee_ID=their ID. In other words find the same record from when they clocked in.

The break in and break out buttons will do the same as time in and time out, only it will add to the break fields.

Now...to work out the hours worked from this shift you basically take away time out from time in to calculate hours worked altogether. Then take away break out from break in to find how long the break was, and then take that away from the time worked.

Ok, now the bad parts about this are: the employee cannot have more than 1 break in a shift, the person cannot do 2 shifts a day because when adding the time out, it looks for the shift added today.

I have written algorithms to stop an employee from clocking in twice a day and to make sure the ID is valid by checking it with the employee table.

Now, if any of that makes sense, like I said, I'd love to hear how anyone else would do it.

I'm using Delphi to do the programming by the way.

DaWei · Aug 18th, 2005, 12:18 PM

If you want the things you say are bad parts, just handle it. When an employee clocks in, the shift is active. Make break in and out times cumulative until the shift is clocked out. When the shift is clocked out, it's dead, no reason not to activate another if an employee works two shifts. Time is cumulative. The beginning and end of a day are just conventions. I don't see the problem.

gj15987 · Aug 18th, 2005, 1:17 PM

I see what you mean. Exactly how i can manage to do that is another problem! I've only just started A level computing and i'm gettin a head start on next year so i don't have that much experience.

Can you give me a clue as to how to do this in delphi?

Thanks very much for replying anyways.

DaWei · Aug 18th, 2005, 1:38 PM

I gave up Delphi before it even came into existence (because I didn't care for Pascal much). I'm fairly sure the DB access stuff is there, right? Would your problem be with the SQL, or the language?

gj15987 · Aug 18th, 2005, 1:42 PM

Ugh, i think I'm going to be beyond help. I think its the language. I don't have any problem with adding stuff to databases using sql. I don't understand how i make a shift "active" and how to make the time cumulative.

Ooble · Aug 18th, 2005, 2:04 PM

What's wrong with just having an 'active' field? Just set it to TRUE or FALSE!

Or, even better, just leave the 'clock_out' field blank. Then, in your program, check to see whether it's blank, and, depending on the results, clock in or out.

gj15987 · Aug 18th, 2005, 2:42 PM

Ah I can't believe I've been so dumb! I've figured out the best way to do it is to have a table:

Tbl_shift(Shift_ID, Date_, Time_In, Time_Out, Time_Worked, Employee_ID)

Time worked will be calculated from Time in and out. That way each record will have the time worked for that shift and i can add all them up for each day!

So to add up the total time worked and work out the salary i can do...

(Toggle Plain Text)

Var total_time_worked:TDateTime;
begin
Tbl_Shift.Open;
Tbl_Shift.First;
Repeat
    if Date=Desired date then 
        begin
            total_time_worked:=total_time_worked + Tbl_ShiftTime_Worked.value;
            Tbl_Shift.Next;
        end
else tbl_Shift.Next;
Until Tbl_Shift.Eof;
Balance:=Total_time_worked*Rate_of_pay;

Something like that, probably a million errors in there but i'll find them when i get round to typing it into delphi. the rate of pay etc will come from the employee table i think. Oh and i'll need to stick that inside an if to check the employee ID is the same too.

As for the clock in, I like your idea Ooble. I'll set it to find the newest record for that employee, and if there is something present in the time_out field then clock in, else, clock out!

Ah things seem so much clearer when you try to explain to others what it is you are doing!

Thank you so much DaWei for your ideas and thanks to Ooble! You've helped me before! Thanks!

I owe ya one...don't quite know when you'd ever need my help but ah well! lol.

big_k105 · Aug 18th, 2005, 2:45 PM

was this a delphi question? if so im goin to move it to the delphi forum.

gj15987 · Aug 18th, 2005, 3:01 PM

It was a project in general question, and then became a delphi topic, cus thats the language i'm using.

Infinite Recursion · Aug 18th, 2005, 3:11 PM

I was wondering why you didn't have a date field in your shift table... lol.

You may also want to split the name field in the employee table into first name and last name... it may help with reports when you want to sort them a certain way, etc.

Aug 18th, 2005, 10:59 AM	#1
gj15987 Programmer Join Date: Feb 2005 Posts: 45 Rep Power: 0	A payroll system I need to create a basic payroll system where an employee can enter their PIN and clock in or clock out. Then the program needs to calculate how many hours they've worked. I was just wondering how people would go about this? I have come up with this, but i'm sure there has to be a better way. Tbl_Employees(Employee_ID, Employee_Name, Rate_Per_Hour, DOB) Tbl_Shift(Shift_ID, Time_In, Time_Out, Break_In, Break_Out, Date_, Employee_ID) Right, the employee opens up the program and enters their ID. They then click clock in. This executes an sql to create a new record with the shift ID, time in, date and employee ID. When they clock out, they again enter their number and click clock out. This will execute an sql to update a record where date=today and employee_ID=their ID. In other words find the same record from when they clocked in. The break in and break out buttons will do the same as time in and time out, only it will add to the break fields. Now...to work out the hours worked from this shift you basically take away time out from time in to calculate hours worked altogether. Then take away break out from break in to find how long the break was, and then take that away from the time worked. Ok, now the bad parts about this are: the employee cannot have more than 1 break in a shift, the person cannot do 2 shifts a day because when adding the time out, it looks for the shift added today. I have written algorithms to stop an employee from clocking in twice a day and to make sure the ID is valid by checking it with the employee table. Now, if any of that makes sense, like I said, I'd love to hear how anyone else would do it. I'm using Delphi to do the programming by the way.

Aug 18th, 2005, 12:18 PM	#2
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	If you want the things you say are bad parts, just handle it. When an employee clocks in, the shift is active. Make break in and out times cumulative until the shift is clocked out. When the shift is clocked out, it's dead, no reason not to activate another if an employee works two shifts. Time is cumulative. The beginning and end of a day are just conventions. I don't see the problem. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Aug 18th, 2005, 1:38 PM	#4
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	I gave up Delphi before it even came into existence (because I didn't care for Pascal much). I'm fairly sure the DB access stuff is there, right? Would your problem be with the SQL, or the language? __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Aug 18th, 2005, 2:04 PM	#6
Ooble I eat cake for breakfast. Join Date: Jul 2004 Location: In my box. Posts: 4,434 Rep Power: 9	What's wrong with just having an 'active' field? Just set it to TRUE or FALSE! Or, even better, just leave the 'clock_out' field blank. Then, in your program, check to see whether it's blank, and, depending on the results, clock in or out. __________________ Me :: You :: Them

Aug 18th, 2005, 2:42 PM	#7
gj15987 Programmer Join Date: Feb 2005 Posts: 45 Rep Power: 0	Ah I can't believe I've been so dumb! I've figured out the best way to do it is to have a table: Tbl_shift(Shift_ID, Date_, Time_In, Time_Out, Time_Worked, Employee_ID) Time worked will be calculated from Time in and out. That way each record will have the time worked for that shift and i can add all them up for each day! So to add up the total time worked and work out the salary i can do... (Toggle Plain Text) Var total_time_worked:TDateTime; begin Tbl_Shift.Open; Tbl_Shift.First; Repeat if Date=Desired date then begin total_time_worked:=total_time_worked + Tbl_ShiftTime_Worked.value; Tbl_Shift.Next; end else tbl_Shift.Next; Until Tbl_Shift.Eof; Balance:=Total_time_workedRate_of_pay; Var total_time_worked:TDateTime; begin Tbl_Shift.Open; Tbl_Shift.First; Repeat if Date=Desired date then begin total_time_worked:=total_time_worked + Tbl_ShiftTime_Worked.value; Tbl_Shift.Next; end else tbl_Shift.Next; Until Tbl_Shift.Eof; Balance:=Total_time_workedRate_of_pay; Something like that, probably a million errors in there but i'll find them when i get round to typing it into delphi. the rate of pay etc will come from the employee table i think. Oh and i'll need to stick that inside an if to check the employee ID is the same too. As for the clock in, I like your idea Ooble. I'll set it to find the newest record for that employee, and if there is something present in the time_out field then clock in, else, clock out! Ah things seem so much clearer when you try to explain to others what it is you are doing! Thank you so much DaWei for your ideas and thanks to Ooble! You've helped me before! Thanks! I owe ya one...don't quite know when you'd ever need my help but ah well! lol. Last edited by gj15987; Aug 18th, 2005 at 2:57 PM.

Aug 18th, 2005, 1:17 PM	#3
gj15987 Programmer Join Date: Feb 2005 Posts: 45 Rep Power: 0	I see what you mean. Exactly how i can manage to do that is another problem! I've only just started A level computing and i'm gettin a head start on next year so i don't have that much experience. Can you give me a clue as to how to do this in delphi? Thanks very much for replying anyways.

Aug 18th, 2005, 1:42 PM	#5
gj15987 Programmer Join Date: Feb 2005 Posts: 45 Rep Power: 0	Ugh, i think I'm going to be beyond help. I think its the language. I don't have any problem with adding stuff to databases using sql. I don't understand how i make a shift "active" and how to make the time cumulative.

Aug 18th, 2005, 2:45 PM	#8
big_k105 PFO Founder Join Date: Mar 2004 Location: Fargo, ND Posts: 1,628 Rep Power: 10	was this a delphi question? if so im goin to move it to the delphi forum. __________________ BIG K aka Kyle Programming Forums Kyle K Online Please do not PM or email me programming questions. Post them in the forums instead.

Aug 18th, 2005, 3:01 PM	#9
gj15987 Programmer Join Date: Feb 2005 Posts: 45 Rep Power: 0	It was a project in general question, and then became a delphi topic, cus thats the language i'm using.

Aug 18th, 2005, 3:11 PM	#10
Infinite Recursion Programming Guru Join Date: Jul 2004 Location: United States Posts: 3,467 Rep Power: 8	I was wondering why you didn't have a date field in your shift table... lol. You may also want to split the name field in the employee table into first name and last name... it may help with reports when you want to sort them a certain way, etc. __________________ http://jasonpowers.net "There are a thousand hacking at the branches of evil to one who is striking at the root."

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Programming Forums > Projects > Existing Project Development

A payroll system