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');
if (in_array($forge = @$_GET['forge'], $forges))
	$where[] = "pp.ProjectId like '$forge.%'";
else {
	foreach($forges as $forge)
		$where[] = "pp.ProjectId not like '$forge.%'";
}

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']);
	
	$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;

/*
 * This is a starting point only. Ideally, I'd like to capture and manage
 * the architecture council roles directly, but--since I have no means of querying
 * for ended AC relationships (we only track an effective date in the Foundation
 * database)--I can't do that right now.
 * 
 * Note that this only captures appointed members and the chair. PMC representatives
 * should be captured by the previous queries. This query needs to be extended to
 * include organization representatives.
 * 
 * TODO Extend to export 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')";

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