View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0012273mantisbttime trackingpublic2010-08-20 17:212011-12-26 07:41
Reporterclebercbr Assigned To 
PrioritynormalSeverityfeatureReproducibilityN/A
Status newResolutionopen 
Product Version1.2.2 
Summary0012273: Show time tracking per project (subproject)
Description

Is interesting show summary of time spend by project. I'd a code here that is resoling my issue, may be you can use too.

See attached file and changes in core\bugnote_api.php

Additional Information

on core\bugnote_api.php

/**

  • Returns an array of bugnote stats
  • @param int $p_project_id project id
  • @param string $p_from Starting date (yyyy-mm-dd) inclusive, if blank, then ignored.
  • @param string $p_to Ending date (yyyy-mm-dd) inclusive, if blank, then ignored.
  • @param int $p_cost cost
  • @return array array of bugnote stats

  • @access public
    */
    function bugnote_stats_get_project_array( $p_project_id, $p_from, $p_to, $p_cost ) {
    $c_project_id = db_prepare_int( $p_project_id );

    $c_to = strtotime( $p_to, SECONDS_PER_DAY - 1); // @23:59:59
    $c_from = strtotime( $p_from );

    if ( $c_to === false || $c_from === false ) {
    error_parameters( array( $p_form, $p_to ) );
    trigger_error( ERROR_GENERIC, ERROR );
    }

    $c_cost = db_prepare_double( $p_cost );

    $t_bug_table = db_get_table( 'mantis_bug_table' );
    $t_user_table = db_get_table( 'mantis_user_table' );
    $t_bugnote_table = db_get_table( 'mantis_bugnote_table' );
    $t_project_table = db_get_table( 'mantis_project_table' );

    if( !is_blank( $c_from ) ) {
    $t_from_where = " AND bn.date_submitted >= $c_from";
    } else {
    $t_from_where = '';
    }

    if( !is_blank( $c_to ) ) {
    $t_to_where = " AND bn.date_submitted <= $c_to";
    } else {
    $t_to_where = '';
    }

    if( ALL_PROJECTS != $c_project_id ) {
    $t_project_where = " AND b.project_id = '$c_project_id' AND bn.bug_id = b.id ";
    } else {
    $t_project_where = '';
    }

    $t_results = array();

    $query = "SELECT username, summary, p.name as project_name, bn.bug_id, SUM(time_tracking) AS sum_time_tracking
    FROM $t_user_table u, $t_bugnote_table bn, $t_bug_table b, $t_project_table p
    WHERE u.id = bn.reporter_id AND bn.time_tracking != 0 AND bn.bug_id = b.id
    AND p.id = b.project_id
    $t_project_where $t_from_where $t_to_where
    GROUP BY bn.bug_id, u.id, u.username, b.summary
    ORDER BY project_name";

    $result = db_query( $query );

    $t_cost_min = $c_cost / 60;

    while( $row = db_fetch_array( $result ) ) {
    $t_total_cost = $t_cost_min * $row['sum_time_tracking'];
    $row['cost'] = $t_total_cost;
    $t_results[] = $row;
    }

    return $t_results;
    }

Tagsbilling, feature, reporting
Attached Files
billing_inc.php (9,623 bytes)    
<?php
# MantisBT - a php based bugtracking system

# MantisBT is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# MantisBT is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with MantisBT.  If not, see <http://www.gnu.org/licenses/>.

/**
 * This include file prints out the bug bugnote_stats
 * $f_bug_id must already be defined
 *
 * @package MantisBT
 * @copyright Copyright (C) 2000 - 2002  Kenzaburo Ito - kenito@300baud.org
 * @copyright Copyright (C) 2002 - 2010  MantisBT Team - mantisbt-dev@lists.sourceforge.net
 * @link http://www.mantisbt.org
 */

/**
 * Requires bugnote API
 */
require_once( 'bugnote_api.php' );

if ( !config_get('time_tracking_enabled') )
	return;
?>

<a name="bugnotestats" id="bugnotestats" /><br />

<?php
	collapse_open( 'bugnotestats' );

	$t_today = date( "d:m:Y" );
	$t_date_submitted = isset( $t_bug ) ? date( "d:m:Y", $t_bug->date_submitted ) : $t_today;

	$t_bugnote_stats_from_def = $t_date_submitted;
	$t_bugnote_stats_from_def_ar = explode ( ":", $t_bugnote_stats_from_def );
	$t_bugnote_stats_from_def_d = $t_bugnote_stats_from_def_ar[0];
	$t_bugnote_stats_from_def_m = $t_bugnote_stats_from_def_ar[1];
	$t_bugnote_stats_from_def_y = $t_bugnote_stats_from_def_ar[2];

	$t_bugnote_stats_from_d = gpc_get_int('start_day', $t_bugnote_stats_from_def_d);
	$t_bugnote_stats_from_m = gpc_get_int('start_month', $t_bugnote_stats_from_def_m);
	$t_bugnote_stats_from_y = gpc_get_int('start_year', $t_bugnote_stats_from_def_y);

	$t_bugnote_stats_to_def = $t_today;
	$t_bugnote_stats_to_def_ar = explode ( ":", $t_bugnote_stats_to_def );
	$t_bugnote_stats_to_def_d = $t_bugnote_stats_to_def_ar[0];
	$t_bugnote_stats_to_def_m = $t_bugnote_stats_to_def_ar[1];
	$t_bugnote_stats_to_def_y = $t_bugnote_stats_to_def_ar[2];

	$t_bugnote_stats_to_d = gpc_get_int('end_day', $t_bugnote_stats_to_def_d);
	$t_bugnote_stats_to_m = gpc_get_int('end_month', $t_bugnote_stats_to_def_m);
	$t_bugnote_stats_to_y = gpc_get_int('end_year', $t_bugnote_stats_to_def_y);

	$f_get_bugnote_stats_button = gpc_get_string('get_bugnote_stats_button', '');
	$f_bugnote_cost = gpc_get_int( 'bugnote_cost', '' );
	$f_project_id = helper_get_current_project();
	
	if ( ON == config_get( 'time_tracking_with_billing' ) ) {
		$t_cost_col = true;
	} else {
		$t_cost_col = false;
	}

?>
<form method="post" action="<?php echo form_action_self() ?>">
<?php # CSRF protection not required here - form does not result in modifications ?>
<input type="hidden" name="id" value="<?php echo isset( $f_bug_id ) ? $f_bug_id : 0 ?>" />
<table border="0" class="width100" cellspacing="0">
<tr>
	<td class="form-title" colspan="4">
<?php
		collapse_icon( 'bugnotestats' );
?>
		<?php echo lang_get( 'time_tracking' ) ?>
	</td>
</tr>
<tr class="row-2">
        <td class="category" width="25%">
                <?php
		$t_filter = array();
		$t_filter['do_filter_by_date'] = 'on';
		$t_filter['start_day'] = $t_bugnote_stats_from_d;
		$t_filter['start_month'] = $t_bugnote_stats_from_m;
		$t_filter['start_year'] = $t_bugnote_stats_from_y;
		$t_filter['end_day'] = $t_bugnote_stats_to_d;
		$t_filter['end_month'] = $t_bugnote_stats_to_m;
		$t_filter['end_year'] = $t_bugnote_stats_to_y;
		print_filter_do_filter_by_date(true);
		?>
        </td>
</tr>
<?php if ( $t_cost_col ) { ?>
<tr class="row-1">
	<td>
		<?php echo lang_get( 'time_tracking_cost' ) ?>:
		<input type="text" name="bugnote_cost" value="<?php echo $f_bugnote_cost ?>" />
	</td>
</tr>
<?php } ?>
<tr>
        <td class="center" colspan="2">
                <input type="submit" class="button" name="get_bugnote_stats_button" value="<?php echo lang_get( 'time_tracking_get_info_button' ) ?>" />
        </td>
</tr>

</table>
</form>
<?php
if ( !is_blank( $f_get_bugnote_stats_button ) ) {
	$t_from = "$t_bugnote_stats_from_y-$t_bugnote_stats_from_m-$t_bugnote_stats_from_d";
	$t_to = "$t_bugnote_stats_to_y-$t_bugnote_stats_to_m-$t_bugnote_stats_to_d";
	$t_bugnote_stats = bugnote_stats_get_project_array( $f_project_id, $t_from, $t_to, $f_bugnote_cost );

	if ( is_blank( $f_bugnote_cost ) || ( (double)$f_bugnote_cost == 0 ) ) {
		$t_cost_col = false;
    }

	$t_prev_id = -1;
?>
<br />
<table border="0" class="width100" cellspacing="0">
<tr class="row-category-history">
	<td class="small-caption">
		<?php echo lang_get( 'username' ) ?>
	</td>
	<td class="small-caption">
		<?php echo lang_get( 'time_tracking' ) ?>
	</td>
<?php if ( $t_cost_col) { ?>
	<td class="small-caption">
		<?php echo lang_get( 'time_tracking_cost' ) ?>
	</td>
<?php } ?>

</tr>
<?php
	$t_sum_in_minutes = 0;
	$t_user_summary = array();
	$t_project_summary = array();

	foreach ( $t_bugnote_stats as $t_item ) {
		$t_sum_in_minutes += $t_item['sum_time_tracking'];
		$t_user_summary[$t_item['username']] += $t_item['sum_time_tracking'];
		$t_project_summary[$t_item['project_name']] += $t_item['sum_time_tracking'];

		$t_item['sum_time_tracking'] = db_minutes_to_hhmm( $t_item['sum_time_tracking'] );
		if ( $t_item['bug_id'] != $t_prev_id) {
			$t_link = string_get_bug_view_link( $t_item['bug_id'] ) . ": " . string_display( $t_item['summary'] );
			echo '<tr class="row-category-history"><td colspan="4">' . $t_link . "</td></tr>";
			$t_prev_id = $t_item['bug_id'];
		}
?>
<tr <?php echo helper_alternate_class() ?>>
	<td class="small-caption">
		<?php echo $t_item['username'] ?>
	</td>
	<td class="small-caption">
		<?php echo $t_item['sum_time_tracking'] ?>
	</td>
<?php if ($t_cost_col) { ?>
	<td>
		<?php echo string_attribute( number_format( $t_item['cost'], 2 ) ); ?>
	</td>
<?php } ?>
</tr>
<?php } # end for loop
?>
<tr <?php echo helper_alternate_class() ?>>
	<td class="small-caption">
		<?php echo lang_get( 'total_time' ); ?>
	</td>
	<td class="small-caption">
		<?php echo db_minutes_to_hhmm( $t_sum_in_minutes ); ?>
	</td>
	<?php if ($t_cost_col) { ?>
	<td>
		<?php echo string_attribute( number_format( $t_sum_in_minutes * $f_bugnote_cost / 60, 2 ) ); ?>
	</td>
	<?php } ?>
</tr>
</table>

<br />
<br />

<table border="0" class="width100" cellspacing="0">
<tr class="row-category-history">
        <td class="small-caption">
                <?php echo lang_get( 'username' ) ?>
        </td>
        <td class="small-caption">
                <?php echo lang_get( 'time_tracking' ) ?>
        </td>
<?php if ( $t_cost_col) { ?>
        <td class="small-caption">
                <?php echo lang_get( 'time_tracking_cost' ) ?>
        </td>
<?php } ?>
</tr>
<?php
        foreach ( $t_user_summary as $t_username => $t_total_time ) {
?>
<tr <?php echo helper_alternate_class() ?>>
        <td class="small-caption">
                <?php echo $t_username; ?>
        </td>
        <td class="small-caption">
                <?php echo db_minutes_to_hhmm($t_total_time); ?>
        </td>
<?php if ($t_cost_col) { ?>
        <td>
                <?php echo string_attribute( number_format( $t_total_time * $f_bugnote_cost / 60, 2 ) ); ?>
        </td>
<?php } ?>
</tr>
<?php } ?>
<tr <?php echo helper_alternate_class() ?>>
        <td class="small-caption">
                <?php echo lang_get( 'total_time' ); ?>
        </td>
        <td class="small-caption">
                <?php echo db_minutes_to_hhmm( $t_sum_in_minutes ); ?>
        </td>
<?php if ($t_cost_col) { ?>
        <td>
                <?php echo string_attribute( number_format( $t_sum_in_minutes * $f_bugnote_cost / 60, 2 ) ); ?>
        </td>
<?php } ?>
</tr>
</table>

<br />
<br />

<?php
# Summary per project
?>
<table border="0" class="width100" cellspacing="0">
<tr class="row-category-history">
        <td class="small-caption">
                <?php echo lang_get( 'project_name' ) ?>
        </td>
        <td class="small-caption">
                <?php echo lang_get( 'time_tracking' ) ?>
        </td>
<?php if ( $t_cost_col) { ?>
        <td class="small-caption">
                <?php echo lang_get( 'time_tracking_cost' ) ?>
        </td>
<?php } ?>
</tr>
<?php
        foreach ( $t_project_summary as $t_project_name => $t_total_time ) {
?>
<tr <?php echo helper_alternate_class() ?>>
        <td class="small-caption">
                <?php echo $t_project_name; ?> 		

        </td>
        <td class="small-caption">
                <?php echo db_minutes_to_hhmm($t_total_time); ?>
        </td>
<?php if ($t_cost_col) { ?>
        <td>
                <?php echo string_attribute( number_format( $t_total_time * $f_bugnote_cost / 60, 2 ) ); ?>
        </td>
<?php } ?>
</tr>
<?php } ?>
<tr <?php echo helper_alternate_class() ?>>
        <td class="small-caption">
                <?php echo lang_get( 'total_time' ); ?>
        </td>
        <td class="small-caption">
                <?php echo db_minutes_to_hhmm( $t_sum_in_minutes ); ?>
        </td>
<?php if ($t_cost_col) { ?>
        <td>
                <?php echo string_attribute( number_format( $t_sum_in_minutes * $f_bugnote_cost / 60, 2 ) ); ?>
        </td>
<?php } ?>
</tr>
</table>






<?php } # end if
	collapse_closed( 'bugnotestats' );
?>

<table class="width100" cellspacing="0">
<tr>
	<td class="form-title" colspan="4">
		<?php collapse_icon( 'bugnotestats' );
		echo lang_get( 'time_tracking' ) ?>
	</td>
</tr>
</table>
<?php
	collapse_end( 'bugnotestats' );
billing_inc.php (9,623 bytes)   
Screenshot-Fatura - MantisBT.png (45,337 bytes)   
Screenshot-Fatura - MantisBT.png (45,337 bytes)   

Activities

ilsaul

ilsaul

2011-08-08 11:03

reporter   ~0029396

can it be integrated?
clebercbr

clebercbr

2011-08-08 14:16

reporter   ~0029399

Of course!
Attached, see an example of this report. I use it every month to calculate time that we are investing in each project.
My version is 1.2.3, this version has a bug on billing, the billing does not show subprojects of a project (just work with selected project or ALL projects).
Any way, for me it's enough, I can get complete report of time by project.
ilsaul

ilsaul

2011-08-09 04:28

reporter   ~0029407

clebercbr, I ask to who can add code to the project, can the developper add this?

I'm interesting in a report with project and subproject, devide for person and for project.

I want to say if i'm on a project with subproject this subproject time need to insert inside of the report of the main project.
clebercbr

clebercbr

2011-08-09 08:36

reporter   ~0029410

I mean, YES!
I don't know if mantis team thinks that it is a good improve for trunk.
Anyway, feel free to use it by yourself.
dregad

dregad

2011-08-09 11:38

developer   ~0029413

clebercbr - would you be able to provide your changes as a git patch vs the latest 1.2.x code, or even better to put it on github ? If not, at least provide a regular unified diff vs 1.2.6, as there have been quite a few changes on time tracking in that release.
ilsaul

ilsaul

2011-08-10 12:04

reporter   ~0029447

Last edited: 2011-08-11 04:16

i check the sql that clebercbr do in bugnote_api.php i find some problem on it.
i try to make one with full check of user, someone can try and tell if it's correct?

SELECT u.username, u.realname, b.summary, p.name as project_name, bn.bug_id, SUM(bn.time_tracking) AS sum_time_tracking
FROM mantis_user_table u, mantis_bugnote_table bn, mantis_bug_table b, mantis_project_table p, mantis_project_hierarchy_table ph, mantis_project_user_list_table pul

WHERE u.id = bn.reporter_id
AND bn.time_tracking != 0
AND bn.bug_id = b.id
AND p.id = b.project_id
AND pul.project_id = b.project_id

-- This condition is for be sure that the project is connected to the request project
AND (b.project_id = ph.child_id OR b.project_id = ph.parent_id)

-- $t_project_where = changed in
AND ph.parent_id = 1
$t_from_where
$t_to_where

-- This condition is for see if the user have access to the project and subproject
-- $user_request = Who make the request of this page
-- $threshold_user_level = user that request the page if have the true level in the project (e.g. 70)
AND ((pul.user_id = $user_request AND pul.access_level >= $threshold_user_level)

-- This condition is for see if the project is public and user have enough level for see the datail time tracking on the project and subproject
--10 = public project
-- $threshold_public_level = level that the user need to have to access to the data of the project (e.g. 70)
OR (p.view_state = 10 AND u.access_level >= $threshold_public_level))

GROUP BY bn.bug_id, u.id, u.username, b.summary
ORDER BY p.name, bn.bug_id

P.S.: the variable $user_request, $threshold_user_level, $threshold_public_level don't exist in mantis i use for mean need to use something instead.
clebercbr

clebercbr

2011-08-10 15:59

reporter   ~0029452

Last edited: 2011-08-10 16:27

Sorry guys, I can't help you, I don't know mantis core deeply. Anyway, I tested ilsaul code and I had problems in my 1.2.3 version.

"AND ph.parent_id = 1"
I cut off this line because SQL return was always empty

"AND ((pul.user_id = $user_request AND pul.access_level >= $threshold_user_level) OR (p.view_state = 10 AND u.access_level >= $threshold_public_level))"
I cut off these lines because following error message ":You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND pul.access_level >= )
OR (p.view_state = 10 AND u.access_level >= ))"

Without that the SQL run, but result is wrong anyway. The list of notes and time sum is not correct. Also, this code cannot treat a more complex tree of projects, like big_project > child_1 > child_of_child_1
ilsaul

ilsaul

2011-08-11 04:21

reporter   ~0029461

Last edited: 2011-08-11 04:27

clebercbr the problem is some date that need to correct.
I give u a traslation of my query:

SELECT u.username, u.realname, b.summary, p.name as project_name, bn.bug_id, SUM(bn.time_tracking) AS sum_time_tracking
FROM mantis_user_table u, mantis_bugnote_table bn, mantis_bug_table b, mantis_project_table p, mantis_project_hierarchy_table ph, mantis_project_user_list_table pul

WHERE u.id = bn.reporter_id
AND bn.time_tracking != 0
AND bn.bug_id = b.id
AND p.id = b.project_id
AND pul.project_id = b.project_id
AND (b.project_id = ph.child_id OR b.project_id = ph.parent_id)

AND ph.parent_id = 1
$t_from_where
$t_to_where

AND ((pul.user_id = 2 AND pul.access_level >= 70)

OR (p.view_state = 10 AND u.access_level >= 70))
GROUP BY bn.bug_id, u.id, u.username, b.summary
ORDER BY p.name, bn.bug_id

2 important thing
1) "ph.parent_id = 1" replace 1 with your project id
2) "pul.user_id = 2" replace 2 with the user id that are watch the report

i don't have the database scheme of 1.2.3 if u have problem u must check if field exist in the table.
e.g.: ph.parent_id it mean
table: mantis_project_hierarchy_table
field: parent_id

if exist that part, it can not have problem.
my query is for version 1.2.6.
AbsolutelyFreeWeb

AbsolutelyFreeWeb

2011-12-24 04:43

reporter   ~0030686

clebercbr, did you do as dregad asked? has it made it into core?
dregad

dregad

2011-12-24 09:14

developer   ~0030698

Nothing has been added to core to date, as I do not have time to do the 3-way merge and then review & test the changes against latest version. Since clebercbr apparently does not know how to do it, maybe you or someone else can volunteer ...
AbsolutelyFreeWeb

AbsolutelyFreeWeb

2011-12-26 02:51

reporter   ~0030702

I've put grouping per issue and per user into the plugin. With a migration script we could retire the entire core time reporting soon ^^ I'll add project grouping too.
AbsolutelyFreeWeb

AbsolutelyFreeWeb

2011-12-26 07:41

reporter   ~0030704

Last edited: 2011-12-29 04:43

this feature is now officially included in the time tracking plugin
find it here https://github.com/mantisbt-plugins/timetracking