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
*******************************************************************************/

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();

$output = array();

// FIXME Use the Forge class.
$forges = array();

// We build up a "where" clause bit-by-bit. The $and variable is an array
// that will contain all of the conjunctive conditions. Based on various
// bits of state, we will accumulate some number (more than one) of conjunctive
// conditions that we join together to build the "where" clause.
$and = array();

// If a forge is specified, narrow our search parameters to include only
// projects in that forge. If no forge is provided, assume that we're
// working in the "eclipse" forge.
if (in_array($forge = @$_GET['forge'], $forges)) {
	$and[] = "(pp.ProjectId like '$forge.%')";
	// Create a replacement string to remove forge qualifers.
	$replace = $forge;
} else {
	$forge = 'eclipse';
	$replace = '';
}

// If an id is provided, assume that it is the id of a single project
// and narrow the search to that. Note that #isValidProjectId should
// protect us against injection.
if (isValidProjectId($id = @$_GET['id'])) {
	$and[] = "(pp.ProjectId = '{$id}')";
}

// If a value is provided for "ago", then we limit ourselves to changes
// that have occurred at most that many days in the past. If no value
// is provided, we assume 10 as the default.
if (!preg_match('/^\d+$/', $ago = @$_GET['ago'])) $ago = 10;
$date = date('Y-m-d', strtotime("-$ago days"));

// We want all records that match either of the two conditions that follow...
$or = array();

// First, check the SYS_ModLog for entries indicating that a change has occurred
// in the PeopleProjects table. Identify those users that the log indicates have
// changed and just include all of their relationships (probably a bit of overkill,
// but doesn't hurt anything).
$or[] = "(p.PersonId in (SELECT PK1 FROM SYS_ModLog where LogTable='PeopleProjects' and ModDateTime > '{$date}'))";

// There may be cases where the active or inactive date suggests that a change
// that is not recorded in the log has occurred (or, potentially, the date of the
// log entry is different from the date recorded in the PeopleProjects table).
// Again, this might be overkill, but it's better that we do not miss anybody.
$or[] = "(pp.ActiveDate > date '$date' or pp.InactiveDate > date '$date')";

// Combine the "or" statements.
$and[] = '(' . implode(' or ', $or) . ')';

// Combine the "and" statements (the size of this array will vary).
$where = 'where ' . implode(' and ', $and);

// 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);

while ($row=mysqli_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(
		// 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
 * 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 and the AC chair (PMC representatives
 * should be captured by the previous queries).
 *
 * 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')";

$result = $App->foundation_sql($sql);
while ($row=mysqli_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=mysqli_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);
?>