export/committers.json.php

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

/*
 * Export committer information from the Foundation database. The information
 * includes the name, committer id, email addresses, and project affiliations.
 *
 * e.g. http://www.eclipse.org/projects/export/committers.json.php
 *
 * INTERNAL USE ONLY: restricted to callers within the Eclipse Foundation.
 */
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");

// Allow Bitergia's test server special access (for now).
// TODO Remove special access after Bitergia's work moves inside the firewall.
if (preg_match('/146\.255\.100\.9/',$_SERVER['REMOTE_ADDR'])) {
	if (@$_SERVER["HTTPS"] != "on") {
		echo $_SERVER['REMOTE_ADDR'] . " is an invalid caller<br>\n";
		exit;
	}
} else 
	mustBeEclipseFoundationCaller();

$data = array();

$data['_comment'] = 'This data is Eclipse Foundation confidental and may only be used as authorized by the Eclipse Foundation.';

/*
 * TODO Do a better job of tracking organization affiliation.
 * 
 * Unfortunately, the Foundation database does not track dates for organization
 * affiliation. Further, we only have the ability to affiliate with one company.
 * In light of this, we do a best guess as the active/inactive dates for affilation
 * by using--when available--the EntryDate and ExpireDate for the organization 
 * membership as the active/inactive date for the committer.
 * 
 * This is far from ideal, but it's the best information that we have.
 */
$sql = "SELECT 
        p.PersonId as id, p.FName, p.LName, p.Email, pe.Email as secondary, p.comments, 
        pp.ProjectId, pp.ActiveDate, pp.InactiveDate, 
        o.OrganizationId, o.Name1 as OrganizationName,
      	om.OrganizationId is not null as isMember,
   		if (om.EntryDate='' OR om.EntryDate='0000-00-00' OR om.EntryDate IS NULL, null, om.EntryDate) as OrgActiveDate,
		if (om.ExpiryDate='' OR om.ExpiryDate='0000-00-00' OR om.ExpiryDate IS NULL, null, om.ExpiryDate) as OrgInactiveDate
    from People as p 
        join PeopleProjects as pp on p.PersonId = pp.PersonId and pp.Relation='CM' 
        left join PeopleEmails as pe on p.PersonId=pe.PersonId 
        left join OrganizationContacts as oc on p.PersonId = oc.PersonId and oc.Relation='EMPLY' 
        left join Organizations as o on oc.OrganizationId=o.OrganizationId
		left join OrganizationMemberships as om on o.OrganizationId=om.OrganizationId";
$result = $App->foundation_sql($sql);

function addEmail(&$committer, $email) {
    if (!$email) return;
    $email = utf8_encode($email);
    if (empty($committer['email'])) $committer['email'] = array();
    if (in_array($email, $committer['email'])) return;
    $committer['email'][] = $email;
}

while ($row=mysql_fetch_assoc($result)) {
    $id = utf8_encode($row['id']);
    $data['committers'][$id]['id'] = $id;
    $data['committers'][$id]['first'] = utf8_encode($row['FName']);
	$data['committers'][$id]['last'] = utf8_encode($row['LName']);
	$data['committers'][$id]['primary'] = $row['Email'];
	addEmail($data['committers'][$id], $row['Email']); 
    addEmail($data['committers'][$id], $row['secondary']);
    if (preg_match_all('/alt-email:(\S+)/i', $row['comments'], $matches)) {
        foreach($matches[1] as $email) {
            addEmail($data['committers'][$id], $email);
        }
    }
    $data['committers'][$id]['projects'][$row['ProjectId']] = array(
        'active' => $row['ActiveDate'],
        'inactive' => isset($row['InactiveDate']) ? $row['InactiveDate'] : null
    );
    if (isset($row['OrganizationId'])) {
        $data['committers'][$id]['affiliations'][$row['OrganizationId']] = array(
            'name' => $row['OrganizationName'],
            'active' => $row['OrgActiveDate'],
            'inactive' => $row['OrgInactiveDate']
        );
        
        $orgId = $row['OrganizationId'];
        $isMember = $row['isMember'];
        $data['organizations'][$orgId] = array(
        	'id' => $orgId,
        	'name' => $row['OrganizationName'],
        	'isMember' => $isMember,
        	'active' => $row['OrgActiveDate'],
        	'inactive' => $row['OrgInactiveDate'],
        	'url' => $isMember ? "http://eclipse.org/membership/showMember.php?member_id=$orgId" : null,
        	'image' => $isMember ? "http://eclipse.org/membership/scripts/get_image.php?id=$orgId&size=small" : null
        );
    }
}
echo json_encode($data);
?>