export/updates.json.php

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

$forges = array('locationtech', 'polarsys');
$where = array();
if (in_array($forge = @$_GET['forge'], $forges))
	$where[] = "pp.ProjectId like '$forge.%'";
else {
	foreach($forges as $forge)
		$where[] = "pp.ProjectId not like '$forge.%'";
	$forge = 'eclipse';
}

$forgeWhere = implode(' and ', $where);

if (!preg_match('/^\d+$/', $ago = @$_GET['ago'])) $ago = 10;
$date = date('Y-m-d', strtotime("-$ago days"));
$where[] = "(pp.ActiveDate > date '$date' or pp.InactiveDate > date '$date')";

$where = 'where ' . implode(' and ', $where);

// Export everybody who has ever had a project relationship.
$sql = "SELECT 
	p.PersonId as id, p.FName, p.LName, p.Email, pp.ProjectId, pp.Relation, pp.ActiveDate, pp.InactiveDate  
	from People as p join PeopleProjects as pp on p.PersonId = pp.PersonId
	$where";

$people = array();

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

// Create a replacement string to remove forge qualifers.
$replace = implode('|', $forges);
while ($row=mysql_fetch_assoc($result)) {
	$id =  utf8_encode($row['id']);
	unset($ac[$id]);
	$people[$id]['id'] = $id;
	$people[$id]['first'] = $row['FName'];
	$people[$id]['last'] = $row['LName'];
	$people[$id]['email'] = $row['Email'];
	$project = utf8_encode($row['ProjectId']);
	$people[$id]['roles'][] = array(
		// Remove the forge qualifer (if any).
		'project' => preg_replace("/^($replace)\./", '', $row['ProjectId']),
		'relation' => $row['Relation'],
		'active' => date('Y-m-d' , strtotime($row['ActiveDate'])),
		'inactive' => ($date = strtotime($row['InactiveDate'])) ? date('Y-m-d' , $date) : null
	);
}

$output['debug']['sql'][] = $sql;

/*
 * Architecture council members who represent top-level projects in
 * other forges are still members of the architecture council. Here, we
 * add the representatives for the other forges.
*/
$sql = "SELECT
    p.PersonId as id, p.FName, p.LName, p.Email, pp.ProjectId, pp.Relation, pp.ActiveDate, pp.InactiveDate
    from People as p join PeopleProjects as pp on p.PersonId = pp.PersonId
    where pp.Relation = 'RA' and pp.InactiveDate is null and not($forgeWhere)";

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

while ($row=mysql_fetch_assoc($result)) {
    $id =  utf8_encode($row['id']);

    $people[$id]['id'] = $id;
    $people[$id]['first'] = $row['FName'];
    $people[$id]['last'] = $row['LName'];
    $people[$id]['email'] = $row['Email'];
    $people[$id]['roles'][] = array(
        'relation' => $row['Relation'],
        'active' => date('Y-m-d' , strtotime($row['ActiveDate']))
    );
}

$output['debug']['sql'][] = $sql;

/*
 * This is a starting point only. Ideally, I'd like to capture and manage
 * non-project-related roles (e.g. architecture council appointment) directly, 
 * but--since I have no means of querying for ended relationships (we only 
 * track an effective date in the Foundation database)--I can't do that right now.
 * 
 * Note that this only captures appointed AC members, the AC chair (PMC representatives
 * should be captured by the previous queries) and Security Team members. 
 * 
 * TODO Extend to export AC organization representatives.
 * TODO Only export changes within the specified timeframe
 */ 

$sql = "SELECT 
	p.PersonId as id, p.FName, p.LName, p.Email, pr.Relation, pr.EntryDate  
	from People as p join PeopleRelations as pr on p.PersonId = pr.PersonId
		where relation in ('EA', 'AZ', 'SecTm')";

$result = $App->foundation_sql($sql);
while ($row=mysql_fetch_assoc($result)) {
	$id =  utf8_encode($row['id']);

	$people[$id]['id'] = $id;
	$people[$id]['first'] = $row['FName'];
	$people[$id]['last'] = $row['LName'];
	$people[$id]['email'] = $row['Email'];
	$people[$id]['roles'][] = array(
		'project' => null,
		'relation' => $row['Relation'],
		'active' => date('Y-m-d' , strtotime($row['EntryDate'])),
		'inactive' => null
	);
}

$output['debug']['sql'][] = $sql;

/*
 * Organization representatives to the Architecture Council.
 */
$sql = "SELECT 
	p.PersonId as id, p.FName, p.LName, p.Email, oc.Relation  
	from People as p join OrganizationContacts as oc on p.PersonId = oc.PersonId
		where relation in ('AC')";

$result = $App->foundation_sql($sql);
while ($row=mysql_fetch_assoc($result)) {
	$id =  utf8_encode($row['id']);

	$people[$id]['id'] = $id;
	$people[$id]['first'] = $row['FName'];
	$people[$id]['last'] = $row['LName'];
	$people[$id]['email'] = $row['Email'];
	$people[$id]['roles'][] = array(
		'project' => null,
		'relation' => $row['Relation'],
		'active' => null,
		'inactive' => null
	);
}

$output['debug']['sql'][] = $sql;

$output['people'] = $people;

echo json_encode($output);
?>