Date index for Nov 2003

Hi,

Jeroen van Wissen wrote:

> In the reports section, I'm having difficulties adding/modifying the results ( must be optional ) in an way that
> no double activities are shown per project.
> ( Some kind of unique project + activity + person or sum(time) by activity per person)

So you want something like this:

User   | Project    | Activity   | Total(h)
-------------------------------------------
Joe    | Project X  | Design     | 140
Jack   | Project X  | Design     |  20
Joe    | Project X  | Meeting    |   4
Jack   | Project Y  | Other      |  10

In other words, the total number of hours spent per activity per project 
per user.

The dirty coded query way:

$data = $g_db->getrows("SELECT
                          person.firstname,
                          person.lastname,
                          project.name,
                          activity.name,
                          sum(time)
                       FROM
                          hours,
                          phase,
                          project,
                          activity,
                          person
                        WHERE
                          hours.phase_id = phase.phase_id
                          AND phase.project_id = project.project_id
                          AND hours.activity_id = activity.activity_id
                          AND hours.userid = person.id
                        GROUP BY
                          person.firstname,
                          person.lastname,
                          project.name,
                          activity.name");

Untested, column names might be a bit different than in this example, 
and grouping like this will go wrong when users have the same first and 
last name (you could add the id's and group by the id's to be on the 
safe side), but you get the idea.

Greetings,
Ivo