export/eca.csv.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();

$comments = isset($_GET['comments']) && $_GET['comments']=='true';

$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'
 ";

$fp = fopen('php://output', 'w');
fputcsv($fp, array('id', 'first', 'last', 'email', 'document', 'start', 'end', 'legal', 'public', 'employer', 'email2'));

query('foundation', $sql, array(), function($row) use (&$fp) {
  $cla = json_decode($row['json'], true);
  // It looks like some content is not being encoded properly and
  // json_decode is failing on newlines embedded in, e.g. the address
  // field. If decoding fails the first time, clean up whitespace
  // and try again.
  if (!$cla) $cla = json_decode(preg_replace('/[\r|\n]+/','\n',$row['json']), true);
  if ($cla) {
   fputcsv($fp, array(
    $row['id'],
    $row['first'],
    $row['last'],
    $row['email'],
    $row['document'],
    $row['start'],
    $row['end'],
    $cla['legal_name'],
    $cla['public_name'],
    $cla['employer'],
    $cla['email']
   ));
  } else {
   if ($comments) {
    fwrite($fp, "# Cannot decode ECA for {$row['id']} ({$row['start']}).");
   }
  }
});

fclose($fp);