export/cq_summary.json.php

<?php
/*******************************************************************************
 * Copyright (c) 2014 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
*******************************************************************************/

/*
 * Export committer information from the Foundation database. The information
 * includes the name, committer id, email addresses, and project affiliations.
 *
 * e.g. http://www.eclipse.org/projects/export/cq_stats.csv.php
 *
 * INTERNAL USE ONLY: restricted to callers within the Eclipse Foundation.
 */
require_once(dirname(__FILE__) . "/../../eclipse.org-common/system/app.class.php");
$App = new App();
require_once(dirname(__FILE__) . "/../classes/common.php");
require_once(dirname(__FILE__) . "/../classes/debug.php");

mustBeEclipseFoundationCaller();

$sql = "select 
    name,
	type, count(*) as count, 
	avg(datediff(approved, start)) as avg_approval, 
	stddev(datediff(approved, start)) as stdev_approval, 
	max(datediff(approved, start)) as max_approval, 
	min(datediff(approved, start)) as min_approval,
	avg(datediff(checkin, start)) as avg_checkin, 
	stddev(datediff(checkin, start)) as stdev_checkin, 
	max(datediff(checkin, start)) as max_checkin, 
	min(datediff(checkin, start)) as min_checkin
from 
	(select 
        p.name,
		b.bug_id, 
		if(b.short_desc regexp '\((Orbit|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 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') 
	group by p.id, b.bug_id) as stats 
#where approved > DATE_SUB(now(), INTERVAL 3 MONTH) 
group by name, type";

$result = $App->ipzilla_sql($sql);

$data = array();

while ($row=mysql_fetch_assoc($result)) {
	$data[$row['name']][$row['type']] = $row;
}

echo json_encode($data);
?>