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.

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.

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.

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?

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.

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.

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


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! :D I owe ya one...don't quite know when you'd ever need my help but ah well! lol.

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

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

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.