| <?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=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']; |
| $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); |
| while ($row=mysql_fetch_assoc($result)) { |
| $id = $row['id']; |
| if (!$id) continue; |
| if (!isset($data['committers'][$id])) continue; |
| addEmail($data['committers'][$id], $row['email']); |
| } |
| |
| echo json_encode($data); |
| ?> |
