export/stale.json.php

<?php
/*******************************************************************************
 * Copyright (c) 2015 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/cq_stats.csv.php
 *
 * INTERNAL USE ONLY: restricted to callers within the Eclipse Foundation.
 */
require_once(dirname(__FILE__) . "/../../eclipse.org-common/system/app.class.php");
require_once(dirname(__FILE__) . "/../classes/debug.php");
$App = new App();

//mustBeEclipseFoundationCaller();

function getActiveProjects($since) {
	global $App;
	
	$date = date('Y-m-d', $since);

	$sql = "
		select 
			distinct project 
		from GitRepo as gr 
			join GitCommit as gc on gr.path=gc.path 
		where gc.date > date('$date');";
		
	$result = $App->dashboard_sql($sql);
	
	$projects = array();
	
	while ($row=mysql_fetch_assoc($result)) {
		$project = $row['project'];
		do {
			$projects[$project] = $project;
		} while ($project = getParent($project));
	}
	
	// There's no need to extract the values or sort them,
	// other than it makes eyeballing the generated list
	// a little easier.
	$projects = array_values($projects);
	sort($projects);
	
	return $projects;
}

function getNewProjects($since) {
	global $App;

	$date = date('Y-m-d', $since);
	
	$sql = "
		select 
			project 
		from (
			select 
				p.ProjectId as project, min(pp.ActiveDate) as start 
			from Projects as p 
				join PeopleProjects as pp on p.ProjectId=pp.ProjectId 
			group by p.ProjectId
		) as projects
		where start > date('$date')";

	$result = $App->foundation_sql($sql);

	$projects = array();
	while ($row=mysql_fetch_assoc($result)) {
		$projects[] = $row['project'];
	}

	return $projects;
}

function getInactiveProjects($since) {
	global $App;
	
	$active = asSqlList(getActiveProjects($since));
	$new = asSqlList(getNewProjects($since));
	
	$ignore = asSqlList(
		array(
			'tools.orbit', 
			'technology.subversive',
			'foundation-internal.shared'
		)
	);
	
	$sql = "
		select 
			ProjectId as project
		from Projects 
		where 
			IsActive
			and ProjectId not in ($active)
			and ProjectId not in ($new)
			and ProjectId not in ($ignore)";

	$result = $App->foundation_sql($sql);
	
	$projects = array();
	while ($row=mysql_fetch_assoc($result)) {
		$projects[] = $row['project'];
	}
	
	return $projects;
}

function asSqlList($values) {
	return "'" . join ("','", $values) . "'";
}

function getParent($project) {
	// Not particularly efficient, but quick and dirty works here.
	$parts = explode('.', $project);
	array_pop($parts);
	return implode('.', $parts);
}

echo json_encode(getInactiveProjects(strtotime("-9 months")));
?>