export/organizations.json.php

<?php
/*******************************************************************************
 * Copyright (c) 2012 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 the release information for a single project, specified
* via the "id" parameter.
*
* e.g. http://www.eclipse.org/projects/export/releases.json.php?id=technology.woolsey
*
* 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();

$sql = "SELECT 
		o.OrganizationId as id, o.SCRM_GUID as sugarCrm, o.Name1 as name, o.Comments as comments,
		r.Description as memType, m.Relation as memTypeCode, m.ExpiryDate as expiryDate,
		od.DocumentId as docId, od.EffectiveDate as docEffectiveDate, 
		od.ExpirationDate as docExpiryDate, od.Comments as docComments,
		sd.Type as type, sd.Description as description,
		oc.PersonId as personId, oc.Relation as relation, oc.Title as title, oc.Comments as contactComments 
	FROM Organizations as o
		LEFT JOIN OrganizationMemberships as m on o.OrganizationId=m.OrganizationId
		LEFT JOIN SYS_Relations as r on m.Relation=r.Relation and r.Type='ME'
		LEFT JOIN OrganizationDocuments as od on o.OrganizationId=od.OrganizationId 
		LEFT JOIN SYS_Documents as sd on od.DocumentId = sd.DocumentId
		LEFT JOIN OrganizationContacts as oc on o.OrganizationId=oc.OrganizationId";

$organizations = array();
$result = $App->foundation_sql($sql);
while ($row = mysql_fetch_assoc($result)) {
	$organizations[$row['id']]['id'] = $row['id'];
	$organizations[$row['id']]['sugarCrm'] = $row['sugarCrm'];
	$organizations[$row['id']]['name'] = $row['name'];
	$organizations[$row['id']]['typeCode'] = $row['memTypeCode'];
	$organizations[$row['id']]['type'] = $row['memType'];
	$organizations[$row['id']]['expiry'] = normalizeDate($row['expiryDate']);
	if (isset($row['docId'])) {
		$organizations[$row['id']]['documents'][$row['docId']] = array(
			'id' => $row['docId'],
			'type' => $row['type'],
			'description' => $row['description'],
			'effective' => normalizeDate($row['docEffectiveDate']),
			'expiry' => normalizeDate($row['docExpiryDate']),
			'comments' => utf8_encode($row['docComments'])
		);
	}
	if (isset($row['personId'])) {
		$organizations[$row['id']]['contacts'][$row['personId']][$row['relation']] = array(
			'id' => $row['personId'],
			'relation' => $row['relation'],
			'title' => $row['title'],
			'comments' => $row['contactComments']
		);
	}
}

function normalizeDate($date) {
	if (!$date) return null;
	if ($date == '0000-00-00') return null;
	return date('Y-m-d', strtotime($date));
}

echo json_encode($organizations);
?>