Preview
Video Player
00:00
00:00
Summarizing Project Time
8:06Grouping tasks by project is one of the most important things we can do. In fact, I think this should be the default view for our reports. We'll show a summary of the total time spent on each project.
Grouping tasks by project is one of
the most important things that we can do.
0:00
In fact, I think that should be
the default view for our reports.
0:05
We'll need to modify our
get task list function
0:09
as well as add additional
information to our reports page.
0:12
We're going to update the get task list
function to accept an optional parameter.
0:16
We'll name this parameter filter
since we'll be filtering this data
0:22
in several different ways.
0:25
The first thing we're going to do is sort.
0:27
So we need to add an order
by to our SQL statement.
0:30
Let's set a default for
the order to start with.
0:36
If our filter is not set, so on our task
list page we want to only sort by date.
0:39
So the newest entry is first.
0:45
ORDER BY date DESCENDING.
0:46
If our filter parameter is not null,
we want to change our ORDER BY.
0:53
ORDER BY projects.title ASC,
1:11
and then date DESCENDING.
1:16
Now we can add the ORDER BY to our query.
1:22
We're also going to start
accepting user data, so
1:32
let's change this to
a prepare statement instead.
1:34
And then we need to return the results.
1:52
Return $results > fetchAll,
1:58
and we use PDO FETCH ASSOC, for
2:03
associative array.
2:08
Next we need to update our report
to pass the filter parameter.
2:13
Let's go back to the reports file and
2:18
add a filter variable at the top
with the other variables.
2:19
filter = all.
2:23
Now use that filter variable to
pass to the get_task_list function.
2:27
This should sort the tasks by project,
but there isn't really any way for
2:34
us to see that since we're
not showing the project.
2:38
Let's add headers for each project.
2:42
We need a new variable
to track the project id.
2:44
Since we want this to
start with zero as well,
2:47
let's add that to the line with the total.
2:50
project_id = 0.
2:52
Now at the top of the for
each loop, we'll add a conditional
2:57
to check if the project_id is not
equal to the current project.
3:01
If project_id != item ['project_id'].
3:07
If it's not equal,
we want to set the project id and
3:19
display the project information header.
3:21
project id = item project_id.
3:24
And now we'll echo the headers.
3:35
Then we can add the project title.
3:54
Then add the column names
to the next two columns.
4:09
Date and Time.
4:15
Let's see how this looks in the browser.
4:20
Oops, let's go in and
fix that misspelling.
4:23
We have a project header now, but let's
add a total for each project as well.
4:31
Back in work spaces, let's add
another variable for project total.
4:36
At the top of the conditional
that checks the project ID,
4:45
we add a new conditional to check if
the project ID is greater than zero.
4:48
If so,
we're going to display the project total.
4:59
I've added some nice styling here for
you so
5:10
add class = project-total-label.
5:15
Colspan = 2.
5:24
Project Total.
5:28
And then for
5:33
this cell, class
5:36
= project-total-number
5:41
project_total.
5:48
We also need to reset the project total.
5:56
Finally, we need to add the current time
to the project total, and we need to do
6:06
this regardless of the project id but
after resetting the project total.
6:10
We reset our project total to zero
if we're starting a new project.
6:16
But we need to add
the task time each loop.
6:20
So after our conditional,
let's duplicate this grand total line and
6:22
change this to project total.
6:27
Now let's check this in the browser again.
6:30
This time we see a header and
a summary for all the projects.
6:34
Except the last one.
6:38
The last project does not
have a project total.
6:40
Let's go back to our work spaces.
6:43
The project summary is only shown
at the beginning of the loop,
6:45
when the project assigned to the current
task does not match the project assigned
6:49
to the last task.
6:53
We also want to show the project
summary for the last project.
6:54
We could copy and
paste this display row after the for
6:58
each loop but duplicating code
isn't usually the best idea.
7:01
Instead, we use another
function with our loop.
7:05
First, we need to move the get_task_list
out of the foreach loop.
7:09
Now we'll move this second
conditional to the end of our loop.
7:21
And we'll change the conditional.
7:32
This time we'll use the next function.
7:35
If the next project id does
not equal the current item,
7:46
project id, then we're going
to show the project summary.
7:51
Now when we preview
the script in the browser,
7:58
we see a summary of all the projects
plus the grand total at the bottom.
8:00