web-api/kpi-organization-committers.php

<?php
/*******************************************************************************
 * Copyright (c) 2012 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");
$App = new App();

$result = $App->foundation_sql("select year(min(EntryDate)) as year, month(min(EntryDate)) as month from OrganizationMemberships where EntryDate<>date(0)");
if ($row = mysql_fetch_assoc($result)) {
	$year = $result['year'];
	$month = $result['month'];
} else {
	die();
}

if (!$year) $year = '2001';
if (!$month) $month = '11';

$start = strtotime("$year-$month-01");
while ($start < time()) {
	$end = strtotime("+1 month", $start);
	
	$period = date('Ym', $start);
	$startDate = date('Y-m-d', $start);
	$endDate = date('Y-m-d', $end);
	$sql = "select count(distinct o.OrganizationId) as count
		from Organizations as o 
			join OrganizationMemberships as om 
				on o.OrganizationId=om.OrganizationId 
				and om.EntryDate < date('$endDate')
				and (om.ExpiryDate=date(0) or date('$startDate') <= om.ExpiryDate)
			join OrganizationContacts as oc on om.OrganizationId=oc.OrganizationId
			join People as p on oc.PersonId=p.PersonId and p.PersonId in (select distinct pp.PersonId from PeopleProjects as pp where pp.Relation='CM' and pp.InactiveDate is null)";
	$result = $App->foundation_sql($sql);
	while ($row = mysql_fetch_assoc($result)) {
		$count = $row['count'];
		echo "$period,$count\n";
	}
	$start = $end;
}

?>