Skip to content
Advertisement

PHP Reporting on Hours Worked

I was wondering if anyone could help me please?

I have a PHP employee timesheet that tracks hours they have worked by entering the start and end time, code is below;

JavaScript

Is there anyway that I can generate a report and get the total hours worked for each employee in a 4 week period please?

Many thanks 🙂

Advertisement

Answer

Assuming your start and end times are time type, and date is a date type it will look something like this: http://sqlfiddle.com/#!9/69b9c4/4

Schema:

JavaScript

I create the table, and 2 users: user id: 1 has 3 work entries. 12:00 – 14:00 on 6th of July, and 8:00 – 10:00 on 3rd of July. There is also 8:00 – 10:00 entry on 3rd of June (which should not be calculated as this is older than 4 weeks). Second user id:2 has one entry. 8:00 – 16:00 on 6th of July.

Now the query:

JavaScript

Important parts:

  • group by user_id because we want to list data for each user as a single report row
  • hours_worked column: we subtract seconds of start_time from end_time, sum those values and convert back to time. Then as you asked – we only show hour value from that time span
  • where removes all dates that are not in the last 4 weeks period
  • for demonstration purposes there is also activities columns that shows how many different activities were taken into account for each user.

And the result:

JavaScript

User 1 worked 4 hours during that period (we calculated it from his 2 worklog entries, skipped one because it was too old).

User 2 worked 8 hours (calculated from a single entry).

I would prefer the table to just have 2 datetime columns: start and end but you don’t have this setup.

I also don’t take into consideration overlapping worklogs (for example the same hours for the same user logged twice the same day) but i think this is out of the scope of this question and can be validated against when adding the entries.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement