export/eca.json.php

<?php
/*******************************************************************************
 * Copyright (c) 2017 Eclipse Foundation
 *
 * This program and the accompanying materials are made available under
 * the terms of the Eclipse Public License v2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0
 *******************************************************************************/
require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/app.class.php");

require_once($_SERVER['DOCUMENT_ROOT'] . '/projects/classes/database.inc');
require_once($_SERVER['DOCUMENT_ROOT'] . '/projects/classes/common.php');
require_once($_SERVER['DOCUMENT_ROOT'] . '/projects/classes/debug.php');

mustBeEclipseFoundationCaller();

$App = new App();
$sql = "
  select
   p.PersonId as id,
   p.FName as first,
   p.LName as last,
   p.Email as email,
   sd.Description as document,
   EffectiveDate as start,
   ExpirationDate as end,
   ScannedDocumentBLOB as json
  from People as p
   join PeopleDocuments as pd on p.PersonId=pd.PersonId
   join SYS_Documents as sd on pd.DocumentId=sd.DocumentId
  where sd.description in ('Eclipse Contributor Agreement', 'Contributor License Agreement')
   and pd.ScannedDocumentMime = 'application/json'
 ";

$records = array();
query('foundation', $sql, array(), function($row) use (&$records) {
  $cla = json_decode($row['json'], true);
  if ($cla) {
   $id = $row['id'];
   $records[$id] = array(
     'id' => $id,
     'first' => $row['first'],
     'last' => $row['last'],
     'email' => $row['email'],
   );
   $records[$id]['eca'][] = array(
      'document' => $row['document'],
      'start' => $row['start'],
      'end' => $row['end'],
      'legal_name' => $cla['legal_name'],
      'public_name' => $cla['public_name'],
      'employer' => $cla['employer'],
      'email' => $cla['email']
    );
  }
});

echo json_encode($records);