Calculating Absences, Overtime, Late and Working Hours System

+1 John Lester Mercado · September 18, 2015
So I have a  time in and time out database suggested by someone from here. So heres my table and what I want is to add the number of absences, status if present or absent, overtime and late. Please help/images/forum/upload/2015-09-18/b771ef30a29db96a8fccd2bcb0324e89.JPG

Post a Reply

Replies

Oldest  Newest  Rating
0 Ron Butcher · September 19, 2015
This is going to have to mostly be done on the PHP or application side.  You need to define a few things first:

  1. Work schedules - is it the same for everyone?  If so this is easier.  It can be stored as a variable in the application, or put into a table in the database that can be modified later.

  2. What constitutes overtime?  Standard OT is over 8 hours a day or 40 hours in a week.

  3. Is there double time?  Standard is anything over 12 hours is considered DT.



To show all employees even if they are absent, modify your code you used in your last question to list everyone and change the JOIN to LEFT JOIN.  Everything else should stay the same.  That way any check in or check out times that are missing from the attendance_log will be considered as NULL. 

Now that you have everyone, you can use the application to determine if they are late, or absent.  As you cycle through the results, run comparisons:

while($obj = $result->fetch_object()  //  Cycling through results
{
// Other code here
if($obj->time_in > 08:00) // Be sure to format this with how your time is noted in the DB
{
// Do something different to note late employee
}
else if($obj->time_in == NULL)
{
// Do something different to note absent employee
}
else
{
// Do normal stuff
}
}

When it comes time to determine hours take the end time and subtract the start time to get the total hours.  Then determine what is normal, OT, DT.
$doubleTime = 0
$overTime = 0

$hours = $endTime - $startTime

if($hours > 12) // Double time exists
{
$doubleTime = $hours - 12 // Calculate how may double time hours
$hours -= $doubleTime // Remove double time hours so we can continue
}
if($hours > 8)
{
$overtime = $hours - 8 // Calculate how may over time hours
$hours -= $overtime // Remove over time hours so we can continue
}

echo "Double Time Hours: ".$doubleTime."\n"
echo "Over Time Hours: ".$overTime."\n"
echo "Regular Hours: ".$hours

Disclaimer:  I know my code is a bit sloppy and has syntax errors.  It is basically pseudo code that you can modify to your liking.
0 John Lester Mercado · September 25, 2015
I am not working with PHP. Can I do this in java?
0 Ron Butcher · September 26, 2015
Yes. It has been a while since I've worked with Java. The code would be similar, but you can adjust it to Java. 
0 Bram Dekker · September 27, 2015
For Java you need to create a database connection (using a JDBC driver for mysql). I would suggest you to look up the concept of DAO (Database Access Objects) if you are going to work with Java. 

Unfortunately this forum did not retain all the old data because I remember posting some code examples about this a few years ago when the forum still looked red :)
0 John Lester Mercado · September 30, 2015
I know using MySQL with Java. What I want to know is how can I make the code to identify automatically if he is absent or present when he didnt time in. And solve the late and overtime also. HELPPP
0 John Lester Mercado · September 30, 2015
I know using MySQL with Java. What I want to know is how can I make the code to identify automatically if he is absent or present when he didnt time in. And solve the late and overtime also. HELPPP
0 John Lester Mercado · September 30, 2015
I know using MySQL with Java. What I want to know is how can I make the code to identify automatically if he is absent or present when he didnt time in. And solve the late and overtime also. HELPPP
  • 1

SQL & Databases

107,197 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator