<?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> | |