Billing functionality and timesheet reports

Post about your customizations to share with others.

Moderators: Developer, Contributor

Post Reply
Chevinge
Posts: 4
Joined: 08 Sep 2008, 08:13

Billing functionality and timesheet reports

Post by Chevinge »

I have developed some extra functionality as follows:

for billing, added button called 'from last' - which automatically starts the clock with the time from your last entered note - this means that if you forgot to start the stopwatch it doesn't matter (a sort of retrospective Start).

Secondly timesheet reports:
New detailed timesheet report that shows
all individual timesheet lines with notes against a particular item
all subprojects as well as just the current project.
ability to sort by date or issue

as part of this a function to return project and subproject ids in format so one can use something like WHERE b.project_id IN ( $project_list ) in your SQL (since MySQL doesn't provide functionality to traverse a Linked list)

These are currently in my custom_functions and a new detailed_billing page

I would be happy to share them if anyone wants them, prefer to put them into the main code as I feel they are of general use, I am not about to wade through the bits and pieces involved in setting myself up just to add these two, so would prefer to provide them to someone who is already contributing for them to add. Not sure if this is reasonable or anyone wants these bits to add.
mykbaker
Posts: 16
Joined: 02 Jul 2009, 13:14

Re: Billing functionality and timesheet reports

Post by mykbaker »

I would like to see your changes. We are currently using some other addons I found in this forum. Thanks!
Nimitz1061
Posts: 12
Joined: 05 May 2009, 18:39
Contact:

Re: Billing functionality and timesheet reports

Post by Nimitz1061 »

These would certainly be of general use , and I'd also like to see them added.

David
Chevinge
Posts: 4
Joined: 08 Sep 2008, 08:13

Re: Billing functionality and timesheet reports

Post by Chevinge »

I'm not sure how to post files with the code in here - the changes are to:

billing_detail_inc.php
config_inc.php
custom_functions_inc.php
bugnote_add_inc.php

javascript\time_tracking_stopwatch.js

Whilst I am an exprienced developer, I am new to php & to Mantis, so some of this may not comform to the standards (the php seems a bit messy anyway to start with)

the custom_functions_inc contains functions for the billing - ability to get subproject information - needs separate billing files (to follow)
custom_functions_inc.php:
-----------------------------------8<-------------------------------------------

Code: Select all

<?php
                                # returns a comma separated list of project and subproject ids
                                # for use in a SQL WHERE clause 'projectid IN ($subprojectlist)'
                                # see bugnote_detail_get_project_array for example use
		function getproject_and_subprojects( $p_project_id) {
			$s_project_id_list = "";

			$s_projects_to_get = $p_project_id;
			$t_project_hierarchy_table = config_get( 'mantis_project_hierarchy_table' );

			while ($s_projects_to_get.length > 0) {
				if ($s_project_id_list.length > 0) {
					$s_project_id_list = "$s_project_id_list,";
				}
				$s_project_id_list = "$s_project_id_list $s_projects_to_get";

				$t_results = array();

				$query = "SELECT child_id FROM $t_project_hierarchy_table
					WHERE parent_id IN ( $s_projects_to_get )";
					$result = db_query( $query );

				$s_projects_to_get = "";
				foreach ( $result as $child ) {
					if ($s_projects_to_get.length > 0) {
						$s_projects_to_get = "$s_projects_to_get,";
					}
					$child_num = $child['child_id'];
					if ($child_num != "child") {
						$s_projects_to_get = "$s_projects_to_get $child_num";
					}
				}
			}

			return $s_project_id_list;
		}


        # --------------------
		# Returns an array of bugnote stats
		# $p_from - Starting date (yyyy-mm-dd) inclusive, if blank, then ignored.
		# $p_to - Ending date (yyyy-mm-dd) inclusive, if blank, then ignored.
		function bugnote_detail_get_project_array( $p_project_id, $p_from, $p_to, $p_cost, $p_order ) {
			$c_project_id =  $p_project_id ;
			$c_to = db_prepare_date( $p_to );
			$c_from = db_prepare_date( $p_from );
			$c_cost = db_prepare_double( $p_cost );

			// MySQL
			$t_bug_table = config_get( 'mantis_bug_table' );
			$t_user_table = config_get( 'mantis_user_table' );
			$t_bugnote_table = config_get( 'mantis_bugnote_table' );
			$t_bugnote_text = config_get( 'mantis_bugnote_text_table' );
			$t_project_table = config_get( 'mantis_project_table' );

			if ( $p_order == "id" ) {
				$t_order_by = "bn.bug_id, bn.date_submitted";
			} else {
				$t_order_by = "bn.date_submitted, bn.bug_id";
			}


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

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

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

			$t_results = array();

			$query = "SELECT p.name as project_name, username, summary, bn.bug_id, bn.date_submitted, bt.note, time_tracking
				FROM $t_user_table u, $t_bugnote_table bn, $t_bug_table b, $t_bugnote_text bt,$t_project_table p
				WHERE p.id = b.project_id AND u.id = bn.reporter_id AND bn.time_tracking != 0 AND bn.bug_id = b.id AND bn.bugnote_text_id = bt.id
				$t_project_where $t_from_where $t_to_where ORDER BY $t_order_by";


			$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;
		}

			function time_from_last_bugnote() {

				// MySQL
				$t_bug_table = config_get( 'mantis_bug_table' );
				$t_user_table = config_get( 'mantis_user_table' );
				$t_bugnote_table = config_get( 'mantis_bugnote_table' );
				$t_bugnote_text = config_get( 'mantis_bugnote_text_table' );

				$c_user_id = auth_get_current_user_id();

				$t_results = array();

				$query = "SELECT bn.date_submitted
					FROM  $t_bugnote_table bn
					WHERE bn.reporter_id = $c_user_id ORDER BY bn.date_submitted DESC LIMIT 1";

				$result = db_query( $query );

				$row = db_fetch_array( $result );
				return $row['date_submitted'];
			}
?>

time_tracking_stopwatch.js

Code: Select all


var time_tracking_ms = 0;
var time_tracking_running = 0;

function time_tracking_swstartstop() {
	if (time_tracking_running == 0) {
		time_tracking_running = 1;
		time_tracking_then = new Date();
		time_tracking_then.setTime(time_tracking_then.getTime() - time_tracking_ms);
		document.bugnoteadd.time_tracking_ssbutton.value = "Stop";
	} else {
		time_tracking_running = 0;
		time_tracking_now = new Date();
		time_tracking_ms = time_tracking_now.getTime() - time_tracking_then.getTime();
		document.bugnoteadd.time_tracking_ssbutton.value = "Start";
	}
}

function time_tracking_fromlast() {
	time_tracking_running = 1;
	time_tracking_then = new Date();
	time_tracking_then.setTime(Date.parse(document.bugnoteadd.last_entry.value));
	document.bugnoteadd.time_tracking_ssbutton.value = "Stop";
}

function time_tracking_swreset() {
	time_tracking_running = 0;
	time_tracking_ms = 0;
	document.bugnoteadd.time_tracking.value = "0:00:00";
	document.bugnoteadd.time_tracking_ssbutton.value = "Start";
}

function time_tracking_display() {
	setTimeout("time_tracking_display();", 1000);
	if (time_tracking_running == 1) {
		time_tracking_now = new Date();
		time_tracking_ms = time_tracking_now.getTime() - time_tracking_then.getTime();
		time_tracking_seconds = Math.round(time_tracking_ms / 1000) ;
		time_tracking_hours = Math.floor(time_tracking_seconds / 3600);
		time_tracking_left = time_tracking_seconds - (time_tracking_hours * 3600);
		time_tracking_mins = Math.floor(time_tracking_left / 60);
		time_tracking_secs = time_tracking_left - (time_tracking_mins * 60);
		if (time_tracking_secs < 10)
			time_tracking_secs = "0" + time_tracking_secs;
		if (time_tracking_mins < 10)
			time_tracking_mins = "0" + time_tracking_mins;

		document.bugnoteadd.time_tracking.value = time_tracking_hours + ":" + time_tracking_mins + ":" + time_tracking_secs;
	}
}

setTimeout("time_tracking_display();", 1000);


bugnote_add_inc.php from line 71, replace with this:

Code: Select all

<?php if ( config_get('time_tracking_enabled') ) { ?>
<?php if ( access_has_bug_level( config_get( 'time_tracking_edit_threshold' ), $f_bug_id ) ) { ?>
<tr <?php echo helper_alternate_class() ?>>
	<td class="category">
		<?php echo lang_get( 'time_tracking' ) ?>
	</td>
	<td>
		<?php if ( config_get('time_tracking_stopwatch') && ON == config_get( 'use_javascript' )) {
		$lastbugtime = time_from_last_bugnote(); ?>
		<script type="text/javascript" language="JavaScript" src="javascript/time_tracking_stopwatch.js"></script>
		<input type="hidden" name="last_entry" value="<?php echo date('r', strtotime($lastbugtime)) ?>" />
		<input type="text" name="time_tracking" size="5" value="00:00" />
		<input type="button" name="time_tracking_ssbutton" value="Start" onclick="time_tracking_swstartstop()" />
		<input type="button" name="time_tracking_reset" value="Reset" onclick="time_tracking_swreset()" />
		<input type="button" name="time_tracking_startfromlast" value="From Last" onclick="time_tracking_fromlast()" />
		(last entry <?php echo date('M d, G:i:s', strtotime($lastbugtime)) ?>)
		<?php } else { ?>
		<input type="text" name="time_tracking" size="5" value="00:00" />
		<?php } ?>
	</td>
</tr>
<?php } ?>
<?php } ?>
this will add a new button to enable the user to get teh timre from when they last submitted a bugnote (saves having to remember to press start each time!
Chevinge
Posts: 4
Joined: 08 Sep 2008, 08:13

Re: Billing functionality and timesheet reports

Post by Chevinge »

I cannot attach files as I get an error:
Could not upload attachment to ./files/11116_7dfdd0331cb3672dd88c49fd82077c17.


I am an experienced developer, though new to php and Mantis, and am (for better or worse) not about to read the full standards manual before adding a few bits for my own use. Asd a result they may differ from the standards, however particularly in the billing page the php is messy to start with - I have not tried to clean it up, just built on what was there.

I hope this is of use.

Mantis version used 1.1.6
Description:
Billing
you will need to add the billing detail page to the menu as a new link. This gives you the ability to:
a) sort the billing detail by Issue ID# or Date
b) select a project and see the billing for this and all sub-projects

there are 2 custom functions required:
get list of projects and subprojects (not as efficient as it might be, but MySQL does not support self-lined hierarchies so this function runs 1 sql statement for each level of projects - since there are probably likely to be 3 or 4 levels max, this is not too high an overhead.)

get the project info given this list of project ids

Time tracking
This provides a new button on the time tracking section to start the stopwatch with the time from the most recently added bugnote by this user, it saves having to remember to start the stopwatch each time if the user is working on a number of issues consecutively.

extra code in bugnote_add_inc.php from line 71, adding new button
and new function in javascript\time_tracking_stopwatch.js

I make no warranties about this code, if you wish to do so then use it at your own risk!

billing_detail_inc.php

Code: Select all

<?php
# Mantis - a php based bugtracking system

# Copyright (C) 2000 - 2002  Kenzaburo Ito - kenito@300baud.org
# Copyright (C) 2002 - 2007  Mantis Team   - mantisbt-dev@lists.sourceforge.net

# Mantis 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.
#
# Mantis 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 Mantis.  If not, see <http://www.gnu.org/licenses/>.

	# --------------------------------------------------------
	# $Id: billing_inc.php,v 1.14.2.1 2007-10-13 22:32:29 giallu Exp $
	# --------------------------------------------------------
?>
<?php
	# This include file prints out the bug bugnote_stats

	# $f_bug_id must already be defined
?>
<?php
	$t_core_path = config_get( 'core_path' );

	require_once( 'custom_functions_inc.php' );
	require_once( $t_core_path.'bugnote_api.php' );
?>
<?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_sort_order = gpc_get_string( 'sort_order', 'id' );
	$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 $_SERVER['PHP_SELF']; ?>">
<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" size="7" maxlength="7" name="bugnote_cost" value="<?php echo $f_bugnote_cost ?>" />
		Order <select name="sort_order">
		<option <?php if ($f_sort_order == 'id') {echo 'selected="selected"'; } ?> >id</option>
		<option <?php if ($f_sort_order == 'date') {echo 'selected="selected"'; } ?> >date</option>
		</select>
	</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_detail_get_project_array( $f_project_id, $t_from, $t_to, $f_bugnote_cost, $f_sort_order );

	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"> </td>
	<td class="small-caption"> </td>
	<td class="small-caption">Time</td>
	<td class="small-caption">Notes</td>
<?php if ( $t_cost_col) { ?>
	<td align="right" class="small-caption">
		<?php echo lang_get( 'time_tracking_cost' ) ?>
	</td>
<?php } ?>

</tr>

<?php
	$t_sum_in_minutes = 0;
	$t_sum_item_in_minutes = 0;
	$t_prev_id = -1;

	foreach ( $t_bugnote_stats as $t_item ) {
		$t_link = string_get_bug_view_link( $t_item['bug_id'] ) . ": " . string_display( $t_item['summary'] );
		if ( ($f_sort_order == 'id' && $t_item['bug_id'] != $t_prev_id) || ($f_sort_order == 'date' && substr($t_item['date_submitted'], 0, 10) != $t_prev_id )) {
			if ( $t_prev_id != -1) {
?>
				<tr>
					<td class="small-caption"><?php echo $t_item['username'] ?></td>
					<td class="small-caption"><b>Total for <?php echo $t_prev_id ?></b></td>
					<td class="small-caption"><b><?php echo db_minutes_to_hhmm($t_sum_item_in_minutes) ?></b></td>
					<td></td>
				<?php if ($t_cost_col) { ?>
					<td class="small-caption" align="right" ><b>&#163;
						<?php echo string_attribute( number_format( $t_sum_item_in_minutes * $f_bugnote_cost / 60, 2 ) ); ?>
					</b></td>
				<?php } ?>
				</tr>
<?php			}
			$t_sum_item_in_minutes = 0;

			if ( $f_sort_order == 'id') {
				echo '<tr class="row-category-history"><td colspan="5">' . $t_link . "</td></tr>";
				$t_prev_id = $t_item['bug_id'];
			} else {
				echo '<tr class="row-category-history"><td>' . substr($t_item['date_submitted'], 0, 10) . "</td><td></td><td></td><td></td><td></td></tr>";
				$t_prev_id = substr($t_item['date_submitted'], 0, 10);
			}
		}
	$t_sum_in_minutes += $t_item['time_tracking'];
	$t_sum_item_in_minutes += $t_item['time_tracking'];
?>
		<tr>
			<td class="small-caption"><?php echo $t_item['username'] ?></td>
			<td class="small-caption">
				<?php if ( $f_sort_order == "id") {
							echo $t_item['date_submitted'];
						} else {
							echo $t_link;
						} ?>
			</td>
			<td class="small-caption"><?php echo db_minutes_to_hhmm($t_item['time_tracking']) ?></td>
			<td class="small-caption"><?php echo $t_item['note'] ?></td>
			<td></td>
		</tr>

<?php } # end for loop
?>

<tr>
	<td class="small-caption"><b><?php echo $t_item['username'] ?></b></td>
	<td class="small-caption"><b>Total for <?php echo $t_prev_id ?></b></td>
	<td class="small-caption"><b><?php echo db_minutes_to_hhmm($t_sum_item_in_minutes) ?></b></td>
	<td></td>
<?php if ($t_cost_col) { ?>
	<td class="small-caption" align="right"><b>&#163;
		<?php echo string_attribute( number_format( $t_sum_item_in_minutes * $f_bugnote_cost / 60, 2 ) ); ?>
	</b></td>
<?php } ?>
</tr>

<tr <?php echo helper_alternate_class() ?>>
	<td class="small-caption"><b><?php echo lang_get( 'total_time' ); ?></td>
	<td></td>
	<td class="small-caption"><b><?php echo db_minutes_to_hhmm( $t_sum_in_minutes ); ?></b></td>
<?php if ($t_cost_col) { ?>
	<td></td>
	<td class="small-caption" align="right"><b>&#163;
		<?php echo string_attribute( number_format( $t_sum_in_minutes * $f_bugnote_cost / 60, 2 ) ); ?>
	</b></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_detail_page.php

Code: Select all

<?php
# Mantis - a php based bugtracking system

# Copyright (C) 2000 - 2002  Kenzaburo Ito - kenito@300baud.org
# Copyright (C) 2002 - 2007  Mantis Team   - mantisbt-dev@lists.sourceforge.net

# Mantis 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.
#
# Mantis 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 Mantis.  If not, see <http://www.gnu.org/licenses/>.

	# --------------------------------------------------------
	# $Id: billingpage.php,v 1.1.2.1 2007-10-13 22:32:30 giallu Exp $
	# --------------------------------------------------------
?>
<?php
	require_once( 'core.php' );

	$t_core_path = config_get( 'core_path' );
?>
<?php
/*
	compress_enable();
*/
?>
<?php html_page_top1( lang_get( 'time_tracking_billing_link' )  ) ?>
<?php html_page_top2() ?>

<br />

<?php
	$t_mantis_dir = dirname( __FILE__ ) . DIRECTORY_SEPARATOR;
?>
	<!-- Jump to Bugnote add form -->
<?php
	# Work break-down
	include( $t_mantis_dir . 'billing_detail_inc.php' );

	html_page_bottom1( __FILE__ );
?>
hannesjo
Posts: 11
Joined: 15 Sep 2009, 14:07

Re: Billing functionality and timesheet reports

Post by hannesjo »

thank u very much for sharing! one question: is it also possible to show date_submitted of an ticket? the date when the ticket was opened.
jhuertas
Posts: 2
Joined: 16 Jul 2011, 21:22

Re: Billing functionality and timesheet reports

Post by jhuertas »

Hello how are you?
I am a functional consultant (not technical) and was using this feature with version 1.1.1. I upgraded to 1.2.5 and now I get this error:
Fatal error: Call to undefined function db_prepare_date () in / var/www/mantis125/custom_functions_inc.php on line 45
Can you help please?
Thank you very much, best regards.
Post Reply