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;
<input type="text" id="myInput" onkeyup="myFunction()" placeholder="Type Month/Date/Year" title="Type Month/Date/Year">
<table id="myTable">
<tr class="header">
<th scope="col">Date</th>
<th scope="col">Start Time</th>
<th scope="col">End Time</th>
<th scope="col">Activity</th>
</tr>
<?php foreach($activity as $user_activity) { ?>
<tr>
<td scope="row"><?php echo $user_activity['date']; ?></td>
<td><?php echo $user_activity['start_time']; ?></td>
<td><?php echo $user_activity['end_time']; ?></td>
<td><?php echo $user_activity['activity']; ?></td>
</tr>
<?php } ?>
</table>
</div>
<form method="post" action="index.php">
<div class="form-row">
<input type="hidden" name="user_id" value="<?php echo $_SESSION['id']; ?>">
<div class="form-group col-md-4">
<label for="inputEmail4">Start time</label>
<input type="time" class="form-control" id="inputEmail4" name="start_time" required>
</div>
<div class="form-group col-md-4">
<label for="inputPassword4">End Time</label>
<input type="time" class="form-control" id="inputPassword4" name="end_time" required>
</div>
</div>
<div class="form-group">
<label for="inputAddress">Activity Details</label>
<!-- <input type="text" class="form-control" name="activity"> -->
<textarea name="activity" id="" cols="30" class="form-control" rows="5"></textarea>
</div>
<button type="submit" class="btn btn-primary">Add</button>
</form>
</div>
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:
create table task_activity (id int, user_id int, start_time time, end_time time, activity varchar(255), occur_date date, primary key(id)); insert into task_activity values (1, 1, '12:00', '14:00', 'activity', '2020-07-06'); insert into task_activity values (2, 1, '8:00', '10:00', 'activity', '2020-07-03'); insert into task_activity values (3, 1, '8:00', '10:00', 'activity', '2020-06-03'); insert into task_activity values (4, 2, '8:00', '16:00', 'activity', '2020-07-06');
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:
select user_id, hour(sec_to_time(sum(time_to_sec(end_time) - time_to_sec(start_time)))) as hours_worked, count(id) as activities from task_activity where occur_date between (now() - interval 4 week) and now() group by user_id
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:
| user_id | hours_worked | activities | |---------|--------------|------------| | 1 | 4 | 2 | | 2 | 8 | 1 |
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.