stats/compute_bugs_stats.php - www.eclipse.org/projects - Git at Google

 <?php

 require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection_bugs_ro.class.php";
 require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection_dashboard_rw.class.php";
 require_once "config.php";
 require_once "groups.class.php";
 require_once "hostname.php";

 function countbugs($whereclause, $bugzilla_conn){
   $sql_info = "SELECT count(*)
                FROM bugs
                WHERE " . $whereclause;
   $rs = mysql_query($sql_info, $bugzilla_conn);
   $myrow = mysql_fetch_row($rs);
   $value = $myrow[0];

   return $value;
 }

 function total_bugs($product_id, $dbh_bugzilla){
   # Get the number of bugs today, a week ago, a month ago, six months ago
   $B0 = countbugs("product_id = $product_id",
                   $dbh_bugzilla);
   $B7 = countbugs("product_id = $product_id
                    AND creation_ts < DATE_SUB(NOW(), INTERVAL 7 DAY)",
                    $dbh_bugzilla);
   $B30 = countbugs("product_id = $product_id
                    AND creation_ts < DATE_SUB(NOW(), INTERVAL 1 MONTH)",
                    $dbh_bugzilla);
   $B180 = countbugs("product_id = $product_id
                     AND creation_ts < DATE_SUB(NOW(), INTERVAL 6 MONTH)",
                     $dbh_bugzilla);

   return array($B0,$B7,$B30,$B180);
 }

 function closed_bugs($product_id, $dbh_bugzilla){
   # Get the number of closed bugs today, etc.
   $C0 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
                   OR bug_status = 'CLOSED') AND product_id = $product_id",
                   $dbh_bugzilla);
   $C7 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
                   OR bug_status = 'CLOSED') AND product_id = $product_id
                   AND creation_ts < DATE_SUB(NOW(), INTERVAL 7 DAY)",
                   $dbh_bugzilla);
   $C30 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
                    OR bug_status = 'CLOSED') AND product_id = $product_id
                    AND creation_ts < DATE_SUB(NOW(), INTERVAL 1 MONTH)",
                    $dbh_bugzilla);
   $C180 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
                     OR bug_status = 'CLOSED') AND product_id = $product_id
                     AND creation_ts < DATE_SUB(NOW(), INTERVAL 6 MONTH)",
                     $dbh_bugzilla);

   return array($C0,$C7,$C30,$C180);
 }

 function open_bugs($product_id, $dbh_bugzilla){
   # Get the number of open bugs (P1, P2, P3 non-enhancements) today, etc.
   $O0 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
                   OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
                   AND (priority = 'P1' OR priority = 'P2'
                   OR (priority = 'P3' AND bug_severity <> 'enchancement'))
                   AND product_id = $product_id",
                   $dbh_bugzilla);
   $O7 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
                   OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
                   AND (priority = 'P1' OR priority = 'P2' OR
                   (priority = 'P3' AND bug_severity <> 'enchancement'))
                   AND product_id = $product_id
                   AND creation_ts < DATE_SUB(NOW(), INTERVAL 7 DAY)",
                   $dbh_bugzilla);
   $O30 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
                    OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
                    AND (priority = 'P1' OR priority = 'P2'
                    OR (priority = 'P3' AND bug_severity <> 'enchancement'))
                    AND product_id = $product_id
                    AND creation_ts < DATE_SUB(NOW(), INTERVAL 1 MONTH)",
                    $dbh_bugzilla);
   $O180 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
                     OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
                     AND (priority = 'P1' OR priority = 'P2'
                     OR (priority = 'P3' AND bug_severity <> 'enchancement'))
                     AND product_id = $product_id
                     AND creation_ts < DATE_SUB(NOW(), INTERVAL 6 MONTH)",
                     $dbh_bugzilla);

   return array($O0,$O7,$O30,$O180);
 }

 function delta($bugs1,$bugs2,$closed1,$closed2,$open1,$open2){
   $delta1 = $bugs1 - $bugs2;
   $delta2 = $closed1 - $closed2;
   $delta3 = $open1 - $open2;

   return array($delta1,$delta2,$delta3);
 }

 function gainloss($cdelta,$odelta){
   return $cdelta - $odelta;
 }

 function percents($delta,$gainloss){
   return round(($gainloss / $delta) * 100);
 }

 echo "<html>\n<body>\n";

 # MAIN CODE

 $dbc_bugzilla   = new DBConnectionBugs();
 $dbh_bugzilla   = $dbc_bugzilla->connect();

 $db_connection  = new DBConnectionDashboard();
 $db_handle      = $db_connection->connect();

 $mysql_date = date('Y-m-d');
 $groups = new Groups();
 $groups->loadGroups();                        # Get group info from file
 $groupids = $groups->getIds();

 while(count($groupids)){
   $id = array_pop($groupids);                 # Get group IDs from file
   $project = $groups->getProjectFromId($id);  # Get project name using the ID
   echo "<b>Processing project: $project</b><br>\n";
 $text = "Bugs information for project $project\r\n\r\n";
   ob_flush(); flush();

   $perc = array();
   $gl = array();

   // Get bugs
   $bugs = total_bugs($id, $dbh_bugzilla);
   $closed = closed_bugs($id, $dbh_bugzilla);
   $open = open_bugs($id, $dbh_bugzilla);

   // Write to log (Bugs)
   $text .= "Bugs,Today,Last week,Last month,Last six months\r\n";
   $text .= "Total,".$bugs[0].",".$bugs[1].",".$bugs[2].",".$bugs[3]."\r\n";
   $text .= "Closed,".$closed[0].",".$closed[1].",".$closed[2].",".$closed[3]."\r\n";
   $text .= "Open,".$open[0].",".$open[1].",".$open[2].",".$open[3]."\r\n\r\n";

   // Compute deltas
   $delta7 = delta($bugs[0],$bugs[1],$closed[0],$closed[1],$open[0],$open[1]);
   $delta30 = delta($bugs[0],$bugs[2],$closed[0],$closed[2],$open[0],$open[2]);
   $delta180 = delta($bugs[0],$bugs[3],$closed[0],$closed[3],$open[0],$open[3]);
   $deltas[0] = $delta7[0];
   $deltas[1] = $delta30[0];
   $deltas[2] = $delta180[0];

   // Write to log (Deltas)
   $text .= "Deltas,Last week,Last month,Last six months\r\n";
   $text .= "Total,".$delta7[0].",".$delta30[0].",".$delta180[0]."\r\n";
   $text .= "Closed,".$delta7[1].",".$delta30[1].",".$delta180[1]."\r\n";
   $text .= "Open,".$delta7[2].",".$delta30[2].",".$delta180[2]."\r\n\r\n";

   // Compute gain-loss
   $gl[0] = gainloss($delta7[1],$delta7[2]);
   $gl[1] = gainloss($delta30[1],$delta30[2]);
   $gl[2] = gainloss($delta180[1],$delta180[2]);

   // Write to log (Gain-Loss)
   $text .= "Gain-Loss,Last week,Last month,Last six months\r\n";
   $text .= ",".$gl[0].",".$gl[1].",".$gl[2]."\r\n\r\n";

   // Compute percentages
   $perc[0] = $delta7[0]?percents($delta7[0],$gl[0]):0;
   $perc[1] = $delta30[0]?percents($delta30[0],$gl[1]):0;
   $perc[2] = $delta180[0]?percents($delta180[0],$gl[2]):0;

   // Write to log (Percentages)
   $text .= "Percentages,Last week,Last month,Last six months\r\n";
   $text .= ",".$perc[0].",".$perc[1].",".$perc[2]."\r\n";

   // Store the computer stats in the DB
   $days = explode(",",BUGS_DAYS);
   $i = 0;
   while(count($days)){                # For every expected date
     $day = array_pop($days);
     updateStats($db_handle,$day,$project,$mysql_date,$bugs[0],$deltas[$i],$perc[$i],$gl[$i]);
     $i++;
   }
   updateLog($db_handle,$project,$text);
 }

 echo "\n</body>\n</html>\n";

 function updateLog($db_handle,$project,$text){
   $text = str_replace("\"","'",$text); // mysql can't handle
   $query = "SELECT * FROM ".log_table()." WHERE project = \"".$project."\"";
   $result = mysql_query($query,$db_handle);
   if ($result && mysql_num_rows($result)){
     $query = "UPDATE ".log_table()." SET bugs_text = \"".$text."\"";
     $query .= " WHERE project = \"".$project."\"";
   }else{
     $query = "INSERT INTO ".log_table()." (project, bugs_text) ";
     $query .= "VALUES(\"".$project."\",\"".$text."\")";
   }

 #echo "<pre>".$text."</pre><br>\n";
   mysql_query($query,$db_handle) or die("Error: ".mysql_error());
 }


 function updateStats($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl){
   $query = "SELECT * FROM ".stats_table()." WHERE project = \"".$project."\" AND stats_date = \"".$mysql_date."\"";
   $result = mysql_query($query,$db_handle);
   if ($result && mysql_num_rows($result))
     updateBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl);
   else
     insertBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl);
 }

 function updateBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl){
   $query = "UPDATE ".stats_table()." SET bugs_total = ".$bugs.", bugs_".$day."_delta = ".$delta.", bugs_".$day."_percentage = ".$perc.", bugs_".$day."_gainloss = ".$gl;
   $query .= " WHERE project = \"".$project."\" AND stats_date = \"".$mysql_date.
 "\"";

   echo "<pre>$query</pre>";
   mysql_query($query,$db_handle);
 }

 function insertBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl){
   $query = "INSERT INTO ".stats_table()." (project, stats_date, bugs_total, bugs_".$day."_delta, bugs_".$day."_percentage, bugs_".$day."_gainloss) VALUES (\"".$project."\",\"".$mysql_date."\",".$bugs.",".$delta.",".$perc.",".$gl.")";

   echo "<pre>$query</pre>";
   mysql_query($query,$db_handle);
 }

 $dbc_bugzilla->disconnect();
 //$db_connection->disconnect();

 $rs            = null;
 $dbh_bugzilla  = null;
 $dbc_bugzilla  = null;
 $db_handle     = null;
 $db_connection = null;

 ?>
	<?php

	require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection_bugs_ro.class.php";
	require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection_dashboard_rw.class.php";
	require_once "config.php";
	require_once "groups.class.php";
	require_once "hostname.php";

	function countbugs($whereclause, $bugzilla_conn){
	$sql_info = "SELECT count(*)
	FROM bugs
	WHERE " . $whereclause;
	$rs = mysql_query($sql_info, $bugzilla_conn);
	$myrow = mysql_fetch_row($rs);
	$value = $myrow[0];

	return $value;
	}

	function total_bugs($product_id, $dbh_bugzilla){
	# Get the number of bugs today, a week ago, a month ago, six months ago
	$B0 = countbugs("product_id = $product_id",
	$dbh_bugzilla);
	$B7 = countbugs("product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 7 DAY)",
	$dbh_bugzilla);
	$B30 = countbugs("product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 1 MONTH)",
	$dbh_bugzilla);
	$B180 = countbugs("product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 6 MONTH)",
	$dbh_bugzilla);

	return array($B0,$B7,$B30,$B180);
	}

	function closed_bugs($product_id, $dbh_bugzilla){
	# Get the number of closed bugs today, etc.
	$C0 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
	OR bug_status = 'CLOSED') AND product_id = $product_id",
	$dbh_bugzilla);
	$C7 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
	OR bug_status = 'CLOSED') AND product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 7 DAY)",
	$dbh_bugzilla);
	$C30 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
	OR bug_status = 'CLOSED') AND product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 1 MONTH)",
	$dbh_bugzilla);
	$C180 = countbugs("(bug_status = 'RESOLVED' OR bug_status = 'VERIFIED'
	OR bug_status = 'CLOSED') AND product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 6 MONTH)",
	$dbh_bugzilla);

	return array($C0,$C7,$C30,$C180);
	}

	function open_bugs($product_id, $dbh_bugzilla){
	# Get the number of open bugs (P1, P2, P3 non-enhancements) today, etc.
	$O0 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
	OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
	AND (priority = 'P1' OR priority = 'P2'
	OR (priority = 'P3' AND bug_severity <> 'enchancement'))
	AND product_id = $product_id",
	$dbh_bugzilla);
	$O7 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
	OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
	AND (priority = 'P1' OR priority = 'P2' OR
	(priority = 'P3' AND bug_severity <> 'enchancement'))
	AND product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 7 DAY)",
	$dbh_bugzilla);
	$O30 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
	OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
	AND (priority = 'P1' OR priority = 'P2'
	OR (priority = 'P3' AND bug_severity <> 'enchancement'))
	AND product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 1 MONTH)",
	$dbh_bugzilla);
	$O180 = countbugs("(bug_status = 'UNCONFIRMED' OR bug_status = 'NEW'
	OR bug_status = 'ASSIGNED' OR bug_status = 'REOPENED')
	AND (priority = 'P1' OR priority = 'P2'
	OR (priority = 'P3' AND bug_severity <> 'enchancement'))
	AND product_id = $product_id
	AND creation_ts < DATE_SUB(NOW(), INTERVAL 6 MONTH)",
	$dbh_bugzilla);

	return array($O0,$O7,$O30,$O180);
	}

	function delta($bugs1,$bugs2,$closed1,$closed2,$open1,$open2){
	$delta1 = $bugs1 - $bugs2;
	$delta2 = $closed1 - $closed2;
	$delta3 = $open1 - $open2;

	return array($delta1,$delta2,$delta3);
	}

	function gainloss($cdelta,$odelta){
	return $cdelta - $odelta;
	}

	function percents($delta,$gainloss){
	return round(($gainloss / $delta) * 100);
	}

	echo "<html>\n<body>\n";

	# MAIN CODE

	$dbc_bugzilla = new DBConnectionBugs();
	$dbh_bugzilla = $dbc_bugzilla->connect();

	$db_connection = new DBConnectionDashboard();
	$db_handle = $db_connection->connect();

	$mysql_date = date('Y-m-d');
	$groups = new Groups();
	$groups->loadGroups(); # Get group info from file
	$groupids = $groups->getIds();

	while(count($groupids)){
	$id = array_pop($groupids); # Get group IDs from file
	$project = $groups->getProjectFromId($id); # Get project name using the ID
	echo "<b>Processing project: $project</b><br>\n";
	$text = "Bugs information for project $project\r\n\r\n";
	ob_flush(); flush();

	$perc = array();
	$gl = array();

	// Get bugs
	$bugs = total_bugs($id, $dbh_bugzilla);
	$closed = closed_bugs($id, $dbh_bugzilla);
	$open = open_bugs($id, $dbh_bugzilla);

	// Write to log (Bugs)
	$text .= "Bugs,Today,Last week,Last month,Last six months\r\n";
	$text .= "Total,".$bugs[0].",".$bugs[1].",".$bugs[2].",".$bugs[3]."\r\n";
	$text .= "Closed,".$closed[0].",".$closed[1].",".$closed[2].",".$closed[3]."\r\n";
	$text .= "Open,".$open[0].",".$open[1].",".$open[2].",".$open[3]."\r\n\r\n";

	// Compute deltas
	$delta7 = delta($bugs[0],$bugs[1],$closed[0],$closed[1],$open[0],$open[1]);
	$delta30 = delta($bugs[0],$bugs[2],$closed[0],$closed[2],$open[0],$open[2]);
	$delta180 = delta($bugs[0],$bugs[3],$closed[0],$closed[3],$open[0],$open[3]);
	$deltas[0] = $delta7[0];
	$deltas[1] = $delta30[0];
	$deltas[2] = $delta180[0];

	// Write to log (Deltas)
	$text .= "Deltas,Last week,Last month,Last six months\r\n";
	$text .= "Total,".$delta7[0].",".$delta30[0].",".$delta180[0]."\r\n";
	$text .= "Closed,".$delta7[1].",".$delta30[1].",".$delta180[1]."\r\n";
	$text .= "Open,".$delta7[2].",".$delta30[2].",".$delta180[2]."\r\n\r\n";

	// Compute gain-loss
	$gl[0] = gainloss($delta7[1],$delta7[2]);
	$gl[1] = gainloss($delta30[1],$delta30[2]);
	$gl[2] = gainloss($delta180[1],$delta180[2]);

	// Write to log (Gain-Loss)
	$text .= "Gain-Loss,Last week,Last month,Last six months\r\n";
	$text .= ",".$gl[0].",".$gl[1].",".$gl[2]."\r\n\r\n";

	// Compute percentages
	$perc[0] = $delta7[0]?percents($delta7[0],$gl[0]):0;
	$perc[1] = $delta30[0]?percents($delta30[0],$gl[1]):0;
	$perc[2] = $delta180[0]?percents($delta180[0],$gl[2]):0;

	// Write to log (Percentages)
	$text .= "Percentages,Last week,Last month,Last six months\r\n";
	$text .= ",".$perc[0].",".$perc[1].",".$perc[2]."\r\n";

	// Store the computer stats in the DB
	$days = explode(",",BUGS_DAYS);
	$i = 0;
	while(count($days)){ # For every expected date
	$day = array_pop($days);
	updateStats($db_handle,$day,$project,$mysql_date,$bugs[0],$deltas[$i],$perc[$i],$gl[$i]);
	$i++;
	}
	updateLog($db_handle,$project,$text);
	}

	echo "\n</body>\n</html>\n";

	function updateLog($db_handle,$project,$text){
	$text = str_replace("\"","'",$text); // mysql can't handle
	$query = "SELECT * FROM ".log_table()." WHERE project = \"".$project."\"";
	$result = mysql_query($query,$db_handle);
	if ($result && mysql_num_rows($result)){
	$query = "UPDATE ".log_table()." SET bugs_text = \"".$text."\"";
	$query .= " WHERE project = \"".$project."\"";
	}else{
	$query = "INSERT INTO ".log_table()." (project, bugs_text) ";
	$query .= "VALUES(\"".$project."\",\"".$text."\")";
	}

	#echo "<pre>".$text."</pre><br>\n";
	mysql_query($query,$db_handle) or die("Error: ".mysql_error());
	}


	function updateStats($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl){
	$query = "SELECT * FROM ".stats_table()." WHERE project = \"".$project."\" AND stats_date = \"".$mysql_date."\"";
	$result = mysql_query($query,$db_handle);
	if ($result && mysql_num_rows($result))
	updateBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl);
	else
	insertBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl);
	}

	function updateBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl){
	$query = "UPDATE ".stats_table()." SET bugs_total = ".$bugs.", bugs_".$day."_delta = ".$delta.", bugs_".$day."_percentage = ".$perc.", bugs_".$day."_gainloss = ".$gl;
	$query .= " WHERE project = \"".$project."\" AND stats_date = \"".$mysql_date.
	"\"";

	echo "<pre>$query</pre>";
	mysql_query($query,$db_handle);
	}

	function insertBugs($db_handle,$day,$project,$mysql_date,$bugs,$delta,$perc,$gl){
	$query = "INSERT INTO ".stats_table()." (project, stats_date, bugs_total, bugs_".$day."_delta, bugs_".$day."_percentage, bugs_".$day."_gainloss) VALUES (\"".$project."\",\"".$mysql_date."\",".$bugs.",".$delta.",".$perc.",".$gl.")";

	echo "<pre>$query</pre>";
	mysql_query($query,$db_handle);
	}

	$dbc_bugzilla->disconnect();
	//$db_connection->disconnect();

	$rs = null;
	$dbh_bugzilla = null;
	$dbc_bugzilla = null;
	$db_handle = null;
	$db_connection = null;

	?>