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

mustBeEclipseFoundationCaller();

$data = array();

$data['_comment'] = 'This data is Eclipse Foundation confidential 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.
 *
 * Bug 484377 There are some cases where the organization may have more than
 * one entry in the database (e.g. they changed membership classes). In these
 * cases, it is probable that only one of the records will contain an empty
 * inactive date, and so--depending on the order returned by the sort--we may
 * incorrectly list an inactive date for a company. The query gets a little
 * twisted to account for this.
 *
 * There's two nested queries here. The first makes the data from the organization
 * tables content consistent for our purposes. If, for example, an inactive date
 * is null (or otherwise empty), a very large value (Captain Kirk's birthday) is
 * used in place to allow for a max query in the outer scope. That very large value
 * is replaced will null before it is used in the query result.
 *
 * 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,
        a.CCode as CCode,
        pp.ProjectId, pp.ActiveDate, pp.InactiveDate,
        org.id as OrganizationId, org.name as OrganizationName,
        org.isMember as isMember,
        org.active as OrgActiveDate,
        org.inactive as OrgInactiveDate
    from People as p
        left join PeopleProjects as pp on p.PersonId = pp.PersonId and pp.Relation='CM'
        left join
            (select
                pa.PersonId,
                upper(CCode) as CCode
            from PeopleAddresses as pa
            join
                (select
                   PersonId,
                   max(AddressId) as AddressId
                from PeopleAddresses group by PersonId) as id
            on pa.PersonId=id.PersonId and pa.AddressId=id.AddressId) as a on p.PersonId=a.PersonId
        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 (
            select
                id, name,
                min(active) as active,
                if(date(max(inactive))=date('2233-03-22'),null,max(inactive)) as inactive,
                active is not null and date(max(inactive))=date('2233-03-22') as isMember
            from (
                select
                    o.OrganizationId as id, o.Name1 as name,
                    if (om.EntryDate='' OR om.EntryDate='0000-00-00' OR om.EntryDate IS NULL, null, om.EntryDate) as active,
                    if (om.ExpiryDate='' OR om.ExpiryDate='0000-00-00' OR om.ExpiryDate IS NULL, date('2233-03-22'), om.ExpiryDate) as inactive
                from Organizations as o
                    left join OrganizationMemberships as om on o.OrganizationId=om.OrganizationId
                ) as orgs
            group by id
        ) as org on oc.OrganizationId=org.id
	where OrganizationId is not null or ProjectId is not null";
$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=mysqli_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'];
	$data['committers'][$id]['country'] = $row['CCode'];
	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);
        }
    }
    if (isset($row['ProjectId'])) {
	    $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
        );
    }
}

$sql = "select user_uid as id, user_mail as email from users_emailhistory";
$result = $App->eclipse_sql($sql);
if (!$result) {
	$data['_errors'][] = 'Error encountered while querying history table.';
} else {
	$count = 0;
	while ($row=mysqli_fetch_assoc($result)) {
	    $id = $row['id'];
	    if (!$id) continue;
	    if (!isset($data['committers'][$id])) continue;
	    $count++;
	    addEmail($data['committers'][$id], $row['email']);
	}
	$data['_errors'][] = "Includes {$count} historical email addresses.";
}

echo json_encode($data);
?>