We had the need to create a weekly report that would list what bugs were put into resolved status and by which developer during a specified time period. It's real ugly but works and is invalubale for the DEV manager in determing metrics about his deveoplers time.
If anyone is interested i would be more then happy to post the code.
Weekly report for resloved status
Moderators: Developer, Contributor
-
skibumm101
- Posts: 40
- Joined: 07 Jun 2006, 16:33
Basically i have 2 pages report_form.php and report.php. i put both of these in the mantis root, and created a link on the summary page to goto report_form.php
i know its ugly and can be improved greatly but its a good starting point on a function we needed
here is the html form report_form.php(very simple, i will modify it in the future)
<html>
Date format MUST be as follows
YYYY-MM-DD
<form method="post" action="report.php">
<p>First Date:<input type="text" name="date_1" />Second Date:<input type="text" name="date_2" /></p>
<p><input type="submit" value="submit" /></p>
</form>
</html>
Here is the php page report.php that the form post to and displays the data
<?
$connect = mysql_connect('localhost', '***', '****');
mysql_select_db('****', $connect);
$sql1 = "SELECT * FROM mantis_bug_history_table AS A LEFT JOIN mantis_user_table AS B ON A.user_id=B.id LEFT JOIN mantis_bug_table AS C ON A.bug_id=C.id WHERE A.date_modified BETWEEN '$date_1' and '$date_2' and A.new_value = 80 Order BY A.user_id ";
?>
<table width="100%" border="1" cellpadding="4" cellspacing="0">
<tr>
<td width="110"><b>Bug ID</b></td>
<td><b> Developer</b></td>
<td><b> Date </b></td>
<td><b> Description </b></td>
</tr>
<?
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count1 = 0;
$result_1 = mysql_query($sql1, $connect);
while ($array = mysql_fetch_array($result_1)) {
$ID = $array['bug_id'];
$Name = $array['realname'];
$IP = $array['date_modified'];
$Description = $array['summary'];
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr><td bgcolor=".$row_color.">".$ID."</td><td bgcolor=".$row_color.">".$Name."</td><td bgcolor=".$row_color.">".$IP."</td><td bgcolor=".$row_color.">".$Description."</td><td bgcolor=".$row_color.">".$Definition."</td><td bgcolor=".$row_color.">".$Whom."</td></tr>";
$row_count++;
}
echo "</table>";
i know its ugly and can be improved greatly but its a good starting point on a function we needed
here is the html form report_form.php(very simple, i will modify it in the future)
<html>
Date format MUST be as follows
YYYY-MM-DD
<form method="post" action="report.php">
<p>First Date:<input type="text" name="date_1" />Second Date:<input type="text" name="date_2" /></p>
<p><input type="submit" value="submit" /></p>
</form>
</html>
Here is the php page report.php that the form post to and displays the data
<?
$connect = mysql_connect('localhost', '***', '****');
mysql_select_db('****', $connect);
$sql1 = "SELECT * FROM mantis_bug_history_table AS A LEFT JOIN mantis_user_table AS B ON A.user_id=B.id LEFT JOIN mantis_bug_table AS C ON A.bug_id=C.id WHERE A.date_modified BETWEEN '$date_1' and '$date_2' and A.new_value = 80 Order BY A.user_id ";
?>
<table width="100%" border="1" cellpadding="4" cellspacing="0">
<tr>
<td width="110"><b>Bug ID</b></td>
<td><b> Developer</b></td>
<td><b> Date </b></td>
<td><b> Description </b></td>
</tr>
<?
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count1 = 0;
$result_1 = mysql_query($sql1, $connect);
while ($array = mysql_fetch_array($result_1)) {
$ID = $array['bug_id'];
$Name = $array['realname'];
$IP = $array['date_modified'];
$Description = $array['summary'];
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr><td bgcolor=".$row_color.">".$ID."</td><td bgcolor=".$row_color.">".$Name."</td><td bgcolor=".$row_color.">".$IP."</td><td bgcolor=".$row_color.">".$Description."</td><td bgcolor=".$row_color.">".$Definition."</td><td bgcolor=".$row_color.">".$Whom."</td></tr>";
$row_count++;
}
echo "</table>";
It doesn't works
Hi,
may you send me the files?
I copy and paste the code, but I can't make it works, maybe its my mistake.
Thanks,
may you send me the files?
I copy and paste the code, but I can't make it works, maybe its my mistake.
Thanks,
-
skibumm101
- Posts: 40
- Joined: 07 Jun 2006, 16:33
Thanks a lot for sharing this. I would suggest packing the files in a zip file, creating an issue in the bugtracker and attaching the zip file to it.
I would recommend using Mantis style for this. The current implementation has the following issues:
1. It is open to SQL injection attacks. You need to use gpc_get_* in the report page and db_prepare_*() before you put the data in the query.
2. It would be better to use the database APIs rather than hard-coding the MySQL.
3. There is a configuration option for resolved bugs. It is better to use that rather than hard coding the value 80.
4. Did you consider sorting using the user name rather than user id?
I would recommend using Mantis style for this. The current implementation has the following issues:
1. It is open to SQL injection attacks. You need to use gpc_get_* in the report page and db_prepare_*() before you put the data in the query.
2. It would be better to use the database APIs rather than hard-coding the MySQL.
3. There is a configuration option for resolved bugs. It is better to use that rather than hard coding the value 80.
4. Did you consider sorting using the user name rather than user id?
Re: Weekly report for resloved status
You have to add this in the 'report.php' PHP script. So that the values sent from the HTML form by post method can be used :
$date_1= $_POST['date_1'];
$date_2= $_POST['date_2'];
so here is the full script :
<?
$connect = mysql_connect('localhost', 'username', 'password');
mysql_select_db('databasename', $connect);
$date_1= $_POST['date_1'];
$date_2= $_POST['date_2'];
// print "first date value is :".$date_1; exit;
// print "first date value is : ".$_POST['date_1']; exit;
//$sql1 = "SELECT * FROM mantis_bug_history_table AS A LEFT JOIN mantis_user_table AS B ON A.user_id=B.id LEFT JOIN mantis_bug_table AS C ON A.bug_id=C.id WHERE A.date_modified BETWEEN '$date_1' and '$date_2' and A.new_value = 80 Order BY A.user_id ";
$sql1 = "SELECT * FROM mantis_bug_history_table AS A LEFT JOIN mantis_user_table AS B ON A.user_id=B.id LEFT JOIN mantis_bug_table AS C ON A.bug_id=C.id WHERE A.date_modified BETWEEN '$date_1' and '$date_2' and A.new_value = 80 Order BY A.user_id ";
?>
<table width="100%" border="1" cellpadding="4" cellspacing="0">
<tr>
<td width="110"><b>Bug ID</b></td>
<td><b> Developer</b></td>
<td><b> Date </b></td>
<td><b> Description </b></td>
</tr>
<?
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count1 = 0;
$result_1 = mysql_query($sql1, $connect);
while ($array = mysql_fetch_array($result_1)) {
$ID = $array['bug_id'];
$Name = $array['realname'];
$IP = $array['date_modified'];
$Description = $array['summary'];
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr><td bgcolor=".$row_color.">".$ID."</td><td bgcolor=".$row_color.">".$Name."</td><td bgcolor=".$row_color.">".$IP."</td><td bgcolor=".$row_color.">".$Description."</td><td bgcolor=".$row_color.">".$Definition."</td><td bgcolor=".$row_color.">".$Whom."</td></tr>";
$row_count++;
}
echo "</table>";
Note : 80=resolved. Here are other values :
10:new
80:resolved
90:closed
20:feedback
30:acknowledged
50:assigned
40:confirmed
63:in uat
67:deployment
Hope this helps
$date_1= $_POST['date_1'];
$date_2= $_POST['date_2'];
so here is the full script :
<?
$connect = mysql_connect('localhost', 'username', 'password');
mysql_select_db('databasename', $connect);
$date_1= $_POST['date_1'];
$date_2= $_POST['date_2'];
// print "first date value is :".$date_1; exit;
// print "first date value is : ".$_POST['date_1']; exit;
//$sql1 = "SELECT * FROM mantis_bug_history_table AS A LEFT JOIN mantis_user_table AS B ON A.user_id=B.id LEFT JOIN mantis_bug_table AS C ON A.bug_id=C.id WHERE A.date_modified BETWEEN '$date_1' and '$date_2' and A.new_value = 80 Order BY A.user_id ";
$sql1 = "SELECT * FROM mantis_bug_history_table AS A LEFT JOIN mantis_user_table AS B ON A.user_id=B.id LEFT JOIN mantis_bug_table AS C ON A.bug_id=C.id WHERE A.date_modified BETWEEN '$date_1' and '$date_2' and A.new_value = 80 Order BY A.user_id ";
?>
<table width="100%" border="1" cellpadding="4" cellspacing="0">
<tr>
<td width="110"><b>Bug ID</b></td>
<td><b> Developer</b></td>
<td><b> Date </b></td>
<td><b> Description </b></td>
</tr>
<?
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count1 = 0;
$result_1 = mysql_query($sql1, $connect);
while ($array = mysql_fetch_array($result_1)) {
$ID = $array['bug_id'];
$Name = $array['realname'];
$IP = $array['date_modified'];
$Description = $array['summary'];
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr><td bgcolor=".$row_color.">".$ID."</td><td bgcolor=".$row_color.">".$Name."</td><td bgcolor=".$row_color.">".$IP."</td><td bgcolor=".$row_color.">".$Description."</td><td bgcolor=".$row_color.">".$Definition."</td><td bgcolor=".$row_color.">".$Whom."</td></tr>";
$row_count++;
}
echo "</table>";
Note : 80=resolved. Here are other values :
10:new
80:resolved
90:closed
20:feedback
30:acknowledged
50:assigned
40:confirmed
63:in uat
67:deployment
Hope this helps