committers/categories/index.php

<?php 
/*******************************************************************************
 * Copyright (c) 2006 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:
 *    Nathan Gervais (Eclipse Foundation)- initial API and implementation
 *******************************************************************************/

	#*****************************************************************************
	#
	# sql.php
	#
	# Author: 		Nathan Gervais
	# Date:			Oct 31st, 2008
	#
	# Description:
	#    Category Meta-Data Management Tool.
	#****************************************************************************
	$documentRoot = $_SERVER['DOCUMENT_ROOT'];
	//require_once("C:\httproot\krumo\class.krumo.php");
	require_once ("includes/header.php");
	require_once ($documentRoot . "/eclipse.org-common/system/app.class.php");
	require_once ($documentRoot . "/eclipse.org-common/classes/projects/projectInfoList.class.php");
	$App = new App();
	
	
	function retrieveCategories($categoryList, $projectID, $App) {
		foreach ($categoryList as $cl)
		{
			$categoriesString .= "\"". $cl . "\", "; 	
		}
		$categoriesString = trim($categoriesString);
		$categoriesString = trim($categoriesString, ',');
		$sql = "SELECT piv.value from ProjectInfoValues as piv 
				INNER JOIN ProjectInfo as pi on pi.ProjectInfoID = piv.ProjectInfoID 
				WHERE pi.MainKey = 'categories' and piv.Value IN ($categoriesString) and pi.ProjectID = \"$projectID\" ORDER by piv.value";
		$result = $App->eclipse_sql($sql);
		while ($rr = mysql_fetch_array($result))
		{
			$categoriesFoundArray[] = $rr['value']; 	
		}
		if (count($categoriesFoundArray) == 0)
		{
			$categoriesFoundArray = array();
		}
		foreach ($categoryList as $cl){
			if (array_search($cl, $categoriesFoundArray) !== FALSE )
			{
				$categoriesDiscovered[$cl]=1;
			}
		}
		if (count($categoriesDiscovered) == 0)
		{
			$categoriesDiscovered = array();
		}		
		return $categoriesDiscovered;
	}
	
	function gatherAndUpdateRecord($categoryList, $projectID, $_POST, $App)
	{
		foreach($categoryList as $cl){
			$categoryUpdateList[$cl] = array_key_exists($cl, $_POST);
		}
		if ($_POST['other'] != ""){
			$other = $_POST['other'];
			$categoryUpdateList[$other] = TRUE;			
		}
		foreach ($categoryUpdateList as $culKey => $cul)
		{
			insertUpdate($culKey, $cul, $projectID, $App);	
		}
	}
	
	function insertUpdate($category, $value, $projectID, $App){
		if ($value == TRUE) {
			$checkSQL = "SELECT pi.ProjectInfoID from ProjectInfoValues as piv
						 INNER JOIN ProjectInfo as pi on pi.ProjectInfoID = piv.ProjectInfoID
						 WHERE pi.MainKey = 'categories' and pi.ProjectID = '$projectID' and piv.Value = '$category'";
			$result = $App->eclipse_sql($checkSQL);
			if (mysql_num_rows($result) == 0)
			{
				$insertSQL = "INSERT INTO ProjectInfo VALUES (NULL, '$projectID', 'categories', NULL)";
				$result = $App->eclipse_sql($insertSQL);
				$insertSQL = "INSERT INTO ProjectInfoValues (ProjectInfoID, SubKey, Value) VALUES (LAST_INSERT_ID(), '', '$category')";
				$result = $App->eclipse_sql($insertSQL);

				echo "$projectID - $category - Added<br/>";
			}
		}
		else {
			$checkSQL = "SELECT pi.ProjectInfoID from ProjectInfoValues as piv
						 INNER JOIN ProjectInfo as pi on pi.ProjectInfoID = piv.ProjectInfoID
						 WHERE pi.MainKey = 'categories' and pi.ProjectID = '$projectID' and piv.Value = '$category'";
			$result = $App->eclipse_sql($checkSQL);
			if (mysql_num_rows($result) == 1)
			{
			 	$ra = mysql_fetch_array($result);
			 	$ProjectInfoID = $ra['ProjectInfoID'];
			 	$deleteSQL = "DELETE FROM ProjectInfo where ProjectInfoID = $ProjectInfoID";
			 	$result = $App->eclipse_sql($deleteSQL);
			 	
			 	echo "$projectID - $category - Removed<br/>";
			}
		}
	}
	
	$distinctCatSql = "SELECT DISTINCT piv.value from ProjectInfoValues as piv 
						INNER JOIN ProjectInfo as pi on pi.ProjectInfoID = piv.ProjectInfoID 
						WHERE pi.MainKey = 'categories' ORDER by piv.value";
	$result = $App->eclipse_sql($distinctCatSql);
	while ($rr = mysql_fetch_array($result))
	{
		$categoryArray[] = $rr['value'];
	}
	
	
	
	$distinctIDSql = "SELECT DISTINCT ProjectID from ProjectInfo ORDER BY ProjectID";
	$result = $App->eclipse_sql($distinctIDSql);
	while ($rr = mysql_fetch_array($result))
	{
		$pidArray[] = $rr['ProjectID'];
	}	
	
	$ProjectInfoList = new ProjectInfoList();
	
 	
 ?>
 	<html>
 		<body>
 		<form action="<?=$_SERVER['PHP_SELF'];?>" method="post">
 			<select name="projectID">
 			<?	foreach ($pidArray as $pid)
				{
					$selected = "";
					if ($_POST['projectID'] == $pid)
					{
						$selected = "selected";
					}
					echo "<option value=\"$pid\" $selected>$pid</option>";
				} ?>
 			</select>
 			<input name="viewCat" type="submit" value="View Categories"/>
 		</form>
 		
 		<?
 		if (isset($_POST['viewCat']))
 		{
			 $retCat = retrieveCategories($categoryArray,$_POST['projectID'], $App);
			 ?><h1>Categories found for <?=$_POST['projectID'];?></h1>
			 <form action="<?=$_SERVER['PHP_SELF'];?>" method="post">
			 <input type="hidden" name="projectID" value="<?=$_POST['projectID'];?>"/>
			 <?
			 foreach ($categoryArray as $ca){ ?>
				<input type="checkbox" name="<?=$ca;?>" <? if (array_key_exists($ca, $retCat) !== FALSE) echo "checked";?>/><?=$ca;?><br/>	
			 <? } ?>
			 <input type="text" name="other"/> Other<br/>
			 <input type="submit" name="modify" value="Update Category"/>
			 </form>
			 <?
 		}
 		if (isset($_POST['modify']))
 		{
 			$projectID = $_POST['projectID'];
 			echo "Received Update Record for $projectID";
 			gatherAndUpdateRecord($categoryArray, $projectID, $_POST, $App);
 		}
 		?>
 		
 		</body>
 	</html>