PHP Reporting on Hours Worked

Question

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 :)

Answer 1

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.