<?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);
?>