tools/ipkpi.php - www.eclipse.org/projects - Git at Google

 <?php
 /*******************************************************************************
  * Copyright (c) 2016 Eclipse Foundation and others.
  * All rights reserved. This program and the accompanying materials
  * are made available under the terms of the Eclipse Public License v1.0
  * which accompanies this distribution, and is available at
  * http://www.eclipse.org/legal/epl-v10.html
  *
  * Contributors:
  *    Wayne Beaton (Eclipse Foundation)- initial API and implementation
  *******************************************************************************/

 require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/app.class.php");
 require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/nav.class.php");
 require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/menu.class.php");
 $App 	= new App();
 $Nav	= new Nav();
 $Menu 	= new Menu();
 include($App->getProjectCommon());

 require_once $_SERVER['DOCUMENT_ROOT'] . '/projects/classes/Project.class.php';
 require_once $_SERVER['DOCUMENT_ROOT'] . '/projects/classes/common.php';
 require_once dirname ( __FILE__ ) . '/../classes/database.inc';

 mustBeFoundationEmployee();

 #
 # Begin: page-specific settings.  Change these.
 $pageTitle 		= "Eclipse Project KPIs";
 $pageKeywords	= "";
 $pageAuthor		= "Wayne Beaton";

 $js = "
 // Load the Visualization API and the chart package.
 google.load('visualization', '1.0', {'packages':['corechart']});
 ";

 $App->addExtraHtmlHeader("<script type=\"text/javascript\" src=\"https://www.google.com/jsapi\"></script>");
 $App->addExtraHtmlHeader("<script type=\"text/javascript\">$js</script>");

 $ago = 36;

 ob_start();

 function renderCqProcessingTime($type = 'projectcode', $age = 5, $columns = array('checkin', 'approval')) {
 	global $App;

 	$sql = "
         select
             tlp as project,
         	type, count(*) as count,
         	avg(datediff(approved, pmc)) as avg_approval,
         	stddev(datediff(approved, pmc)) as stdev_approval,
         	max(datediff(approved, pmc)) as max_approval,
         	min(datediff(approved, pmc)) as min_approval,
         	avg(datediff(checkin, pmc)) as avg_checkin,
         	stddev(datediff(checkin, pmc)) as stdev_checkin,
         	max(datediff(checkin, pmc)) as max_checkin,
         	min(datediff(checkin, pmc)) as min_checkin
         from
         	(select
                 p.name as tlp,
                 c.name as project,
         		b.bug_id,
         		if(b.short_desc regexp '\((PB( Orbit)?)\s*\d*\)', 'piggyback', kd.name) as type,
         		date(b.creation_ts) as created,
         		if (max(date(pmc.bug_when)) is null, date(b.creation_ts), max(date(pmc.bug_when))) as start,
         		max(date(pmc.bug_when)) as pmc,
         		max(date(checkin.bug_when)) as checkin,
         		max(date(approved.bug_when)) as approved
         	from bugs as b
                 join products as p on b.product_id = p.id
                 join components as c on b.component_id = c.id
         		join keywords as k on b.bug_id=k.bug_id
         		join keyworddefs as kd on k.keywordid=kd.id and kd.name in ('thirdparty', 'projectcode')
         		left join bugs_activity as pmc on b.bug_id=pmc.bug_id and pmc.added in ('PMC_Approved+')
         		left join bugs_activity as checkin on b.bug_id=checkin.bug_id and checkin.added in ('checkin', 'checkintocvs')
         		left join bugs_activity as approved on b.bug_id=approved.bug_id and approved.added in ('approved', 'approved_all_projects')
         	where p.name not in ('foundation-internal', 'IP', 'dsdp')
         	   and date(b.creation_ts) > DATE_SUB(now(), INTERVAL %age MONTH)
         	group by p.id, b.bug_id) as stats
         where type='%type'
         group by tlp, type";

 	$rows = array();
 	query('ipzilla', $sql, array('%age' => $age, '%type' => $type), function ($row) use ($rows) {
 	   $value = array($row['project']);
 	   foreach($columns as $column) $value[] = $row["avg_" . $column];
 	   $rows[] = $value;
 	});

 	$headers =  array(array('string', 'TLP'));
 	foreach($columns as $column ) $headers[] = array('number', ucfirst($column));
 	drawBarChart("CQStats$type", "Average days (CQs created in the last $age months)",
 	        $headers,
 	        $rows, 'Average time (days)', 'Top Level Project');

 }

 function renderCqCounts($type = 'projectcode', $age = 5) {
     global $App;

     $sql = "
         select
     		p.name as project,
             count(distinct b.bug_id) as count
     	from bugs as b
             join products as p on b.product_id=p.id
     		join keywords as k on b.bug_id=k.bug_id
     		join keyworddefs as kd on k.keywordid=kd.id and kd.name in ('thirdparty', 'projectcode')
         where kd.name = '%type'
     	   and date(b.creation_ts) > DATE_SUB(now(), INTERVAL %age MONTH)
     	group by p.name
     	order by p.name";

     $rows = array();
     query('ipzilla', $sql, array('%age' => $age, '%type' => $type), function ($row) use ($rows) {
      $rows[] = array($row['project'], $row['count']);
     });

     drawBarChart("CQCounts$type", "Total Count (CQs Created in the last $age months)",
             array(array('string', 'TLP'), array('number', 'CQ Count')),
             $rows, 'Number of CQ', 'Top Level Project');

 }

 function drawBarChart($id, $title, $columns, $values, $hlabel='', $vlabel= '', $width=800, $height=600) {
 	global $App;

 	$columnsJS = '';
 	foreach($columns as $column) {
 	    $type = $column[0];
 	    $label = $column[1];
 		$columnsJS .= "data.addColumn('$type','$label');";
 	}

 	$valuesJSON = json_encode($values, JSON_NUMERIC_CHECK);
 	$js = "
 		google.setOnLoadCallback(draw${id}Chart);
 		function draw${id}Chart() {
 			// Create the data table.
 			var data = new google.visualization.DataTable();
 			$columnsJS;
 			data.addRows($valuesJSON);

 			// Set chart options
 			var options = {
 				title:'$title',
 				vAxis: {title: '$vlabel'},
 				hAxis: {title: '$hlabel'},
 				curveType: 'function',
 				width:$width,
 				height:$height,
 				trendlines: { 0: {} }
 			};

 			// Instantiate and draw our chart, passing in some options.
 			var chart = new google.visualization.BarChart(document.getElementById('${id}_div'));
 			chart.draw(data, options);

 			document.getElementById('${id}_div_png').outerHTML = '<a href=\"' + chart.getImageURI() + '\">Printable version</a>';
 		}";

 	$App->addExtraHtmlHeader("<script type=\"text/javascript\">$js</script>");

 	echo "<div id=\"${id}_div\"></div>";
 	echo "<div id=\"${id}_div_png\"></div>";
 }

 ?>
 <div id="maincontent">
 <div id="midcolumn">
 <h1><?= $pageTitle ?></h1>

 <h3>Project Code Processing Averages</h3>

 <p>Say something here.</p>

 <?php renderCqProcessingTime('projectcode', $ago); ?>

 <h3>Third-party Processing Averages</h3>

 <p>Say something here.</p>

 <?php renderCqProcessingTime('thirdparty', $ago); ?>


 <h3>Piggyback Processing Averages</h3>

 <p>Say something here.</p>

 <?php renderCqProcessingTime('piggyback', $ago, array('approval')); ?>

 <h3>Project Code CQ Count</h3>

 <p>Say something here.</p>

 <?php renderCqCounts('projectcode', $ago); ?>

 <h3>Third Party CQ Count</h3>

 <p>Say something here.</p>

 <?php renderCqCounts('thirdparty', $ago); ?>

 </div>
 </div>

 <?php
 $html = ob_get_contents();
 ob_end_clean();
 $App->generatePage($theme, $Menu, $Nav, $pageAuthor, $pageKeywords, $pageTitle, $html);
 ?>
	<?php
	/*******************************************************************************
	* Copyright (c) 2016 Eclipse Foundation and others.
	* All rights reserved. This program and the accompanying materials
	* are made available under the terms of the Eclipse Public License v1.0
	* which accompanies this distribution, and is available at
	* http://www.eclipse.org/legal/epl-v10.html
	*
	* Contributors:
	* Wayne Beaton (Eclipse Foundation)- initial API and implementation
	*******************************************************************************/

	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/app.class.php");
	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/nav.class.php");
	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/menu.class.php");
	$App = new App();
	$Nav = new Nav();
	$Menu = new Menu();
	include($App->getProjectCommon());

	require_once $_SERVER['DOCUMENT_ROOT'] . '/projects/classes/Project.class.php';
	require_once $_SERVER['DOCUMENT_ROOT'] . '/projects/classes/common.php';
	require_once dirname ( __FILE__ ) . '/../classes/database.inc';

	mustBeFoundationEmployee();

	#
	# Begin: page-specific settings. Change these.
	$pageTitle = "Eclipse Project KPIs";
	$pageKeywords = "";
	$pageAuthor = "Wayne Beaton";

	$js = "
	// Load the Visualization API and the chart package.
	google.load('visualization', '1.0', {'packages':['corechart']});
	";

	$App->addExtraHtmlHeader("<script type=\"text/javascript\" src=\"https://www.google.com/jsapi\"></script>");
	$App->addExtraHtmlHeader("<script type=\"text/javascript\">$js</script>");

	$ago = 36;

	ob_start();

	function renderCqProcessingTime($type = 'projectcode', $age = 5, $columns = array('checkin', 'approval')) {
	global $App;

	$sql = "
	select
	tlp as project,
	type, count(*) as count,
	avg(datediff(approved, pmc)) as avg_approval,
	stddev(datediff(approved, pmc)) as stdev_approval,
	max(datediff(approved, pmc)) as max_approval,
	min(datediff(approved, pmc)) as min_approval,
	avg(datediff(checkin, pmc)) as avg_checkin,
	stddev(datediff(checkin, pmc)) as stdev_checkin,
	max(datediff(checkin, pmc)) as max_checkin,
	min(datediff(checkin, pmc)) as min_checkin
	from
	(select
	p.name as tlp,
	c.name as project,
	b.bug_id,
	if(b.short_desc regexp '\((PB( Orbit)?)\s\d\)', 'piggyback', kd.name) as type,
	date(b.creation_ts) as created,
	if (max(date(pmc.bug_when)) is null, date(b.creation_ts), max(date(pmc.bug_when))) as start,
	max(date(pmc.bug_when)) as pmc,
	max(date(checkin.bug_when)) as checkin,
	max(date(approved.bug_when)) as approved
	from bugs as b
	join products as p on b.product_id = p.id
	join components as c on b.component_id = c.id
	join keywords as k on b.bug_id=k.bug_id
	join keyworddefs as kd on k.keywordid=kd.id and kd.name in ('thirdparty', 'projectcode')
	left join bugs_activity as pmc on b.bug_id=pmc.bug_id and pmc.added in ('PMC_Approved+')
	left join bugs_activity as checkin on b.bug_id=checkin.bug_id and checkin.added in ('checkin', 'checkintocvs')
	left join bugs_activity as approved on b.bug_id=approved.bug_id and approved.added in ('approved', 'approved_all_projects')
	where p.name not in ('foundation-internal', 'IP', 'dsdp')
	and date(b.creation_ts) > DATE_SUB(now(), INTERVAL %age MONTH)
	group by p.id, b.bug_id) as stats
	where type='%type'
	group by tlp, type";

	$rows = array();
	query('ipzilla', $sql, array('%age' => $age, '%type' => $type), function ($row) use ($rows) {
	$value = array($row['project']);
	foreach($columns as $column) $value[] = $row["avg_" . $column];
	$rows[] = $value;
	});

	$headers = array(array('string', 'TLP'));
	foreach($columns as $column ) $headers[] = array('number', ucfirst($column));
	drawBarChart("CQStats$type", "Average days (CQs created in the last $age months)",
	$headers,
	$rows, 'Average time (days)', 'Top Level Project');

	}

	function renderCqCounts($type = 'projectcode', $age = 5) {
	global $App;

	$sql = "
	select
	p.name as project,
	count(distinct b.bug_id) as count
	from bugs as b
	join products as p on b.product_id=p.id
	join keywords as k on b.bug_id=k.bug_id
	join keyworddefs as kd on k.keywordid=kd.id and kd.name in ('thirdparty', 'projectcode')
	where kd.name = '%type'
	and date(b.creation_ts) > DATE_SUB(now(), INTERVAL %age MONTH)
	group by p.name
	order by p.name";

	$rows = array();
	query('ipzilla', $sql, array('%age' => $age, '%type' => $type), function ($row) use ($rows) {
	$rows[] = array($row['project'], $row['count']);
	});

	drawBarChart("CQCounts$type", "Total Count (CQs Created in the last $age months)",
	array(array('string', 'TLP'), array('number', 'CQ Count')),
	$rows, 'Number of CQ', 'Top Level Project');

	}

	function drawBarChart($id, $title, $columns, $values, $hlabel='', $vlabel= '', $width=800, $height=600) {
	global $App;

	$columnsJS = '';
	foreach($columns as $column) {
	$type = $column[0];
	$label = $column[1];
	$columnsJS .= "data.addColumn('$type','$label');";
	}

	$valuesJSON = json_encode($values, JSON_NUMERIC_CHECK);
	$js = "
	google.setOnLoadCallback(draw${id}Chart);
	function draw${id}Chart() {
	// Create the data table.
	var data = new google.visualization.DataTable();
	$columnsJS;
	data.addRows($valuesJSON);

	// Set chart options
	var options = {
	title:'$title',
	vAxis: {title: '$vlabel'},
	hAxis: {title: '$hlabel'},
	curveType: 'function',
	width:$width,
	height:$height,
	trendlines: { 0: {} }
	};

	// Instantiate and draw our chart, passing in some options.
	var chart = new google.visualization.BarChart(document.getElementById('${id}_div'));
	chart.draw(data, options);

	document.getElementById('${id}_div_png').outerHTML = '<a href=\"' + chart.getImageURI() + '\">Printable version</a>';
	}";

	$App->addExtraHtmlHeader("<script type=\"text/javascript\">$js</script>");

	echo "<div id=\"${id}_div\"></div>";
	echo "<div id=\"${id}_div_png\"></div>";
	}

	?>
	<div id="maincontent">
	<div id="midcolumn">
	<h1><?= $pageTitle ?></h1>

	<h3>Project Code Processing Averages</h3>

	<p>Say something here.</p>

	<?php renderCqProcessingTime('projectcode', $ago); ?>

	<h3>Third-party Processing Averages</h3>

	<p>Say something here.</p>

	<?php renderCqProcessingTime('thirdparty', $ago); ?>


	<h3>Piggyback Processing Averages</h3>

	<p>Say something here.</p>

	<?php renderCqProcessingTime('piggyback', $ago, array('approval')); ?>

	<h3>Project Code CQ Count</h3>

	<p>Say something here.</p>

	<?php renderCqCounts('projectcode', $ago); ?>

	<h3>Third Party CQ Count</h3>

	<p>Say something here.</p>

	<?php renderCqCounts('thirdparty', $ago); ?>

	</div>
	</div>

	<?php
	$html = ob_get_contents();
	ob_end_clean();
	$App->generatePage($theme, $Menu, $Nav, $pageAuthor, $pageKeywords, $pageTitle, $html);
	?>