tools/kpis/ip_cq_time_typeA.inc

<?php
/********************************************************************************
 * Copyright (c) 2018 The Eclipse Foundation
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0.
 *
 * SPDX-License-Identifier: EPL-2.0
 ********************************************************************************/
require_once dirname ( __FILE__ ) . '/../charts.inc';
function chart_ip_cq_time_typeA(ChartContext $context) {
	return ChartBuilder::named ( 'ip_cq_time_typeA' )
		->title ( "Time to Resolve Third Party \"Type A\" CQs (:start to :end)" )
		->description ( "The time required to resolve \"Type A\" third party CQs by quarter." )
		->query ('ipzilla', "
			select
				concat(year(created),'-',quarter(created)) as period,
				count(distinct id) as count,
				avg(timestampdiff(DAY,created,certified)) as average
			from (
				select
					b.bug_id as id,
					b.short_desc as summary,
					b.bug_severity as state,
					b.bug_status as status,
					b.resolution as resolution,
					date(b.creation_ts) as created,
					max(date(certified.bug_when)) as certified
				from bugs as b
					join keywords as k on b.bug_id=k.bug_id
					join keyworddefs as kd on k.keywordid=kd.id
					join bugs_activity as certified
						on b.bug_id=certified.bug_id
						and certified.added in ('license_certified')
				where
					kd.name in ('thirdparty')
					and b.creation_ts between date(':start') and date(':end')
					and b.short_desc not regexp '\((Orbit|PB( Orbit)?|ATO)\s*\d*\)'
					and b.bug_severity = 'license_certified'
				group by b.bug_id
			) as candidates
			where
				timestampdiff(DAY,created,certified) > 2
			group by concat(year(created),'-',quarter(created))
		" )
		->substitute ( ':start', $context->getStart()->format('Y-m-d') )
		->substitute ( ':end', $context->getEnd()->format('Y-m-d') )
		->column ( 'Quarter', 'period', 'string')
		->column ( 'Count', 'count', 'number' )
		->column ( 'Average', 'average', 'number' );
}
?>