Location: PHPKode > projects > Time Recording System > valueList.php
<?
/*
	################################################################
	# >>> Time Recording System											#
	################################################################
	# > Authors:  Lucian Pricop and David Sturtevant					#
	# > E-mail:  hide@address.com						#
	# > Date: 	 17 April 2007											#
	#																			#
	# This web application allows your staff to submit their time	#
	# sheets on line 														#
	################################################################
	#	Copyright (C) 2007  Oxford Archaeology							#
	#																			#
	# This program is free software; you can redistribute 			#
	# it and/or modify it under the terms of the GNU General 		#
	# Public License as published by the Free Software 				#
	# Foundation; either version 2 of the License, or (at your	 	#
	# option) any later version.											#
	#																			#
	# This program is distributed in the hope that it will be 		#
	# useful, but WITHOUT ANY WARRANTY; without even the 				#
	# implied warranty of MERCHANTABILITY or FITNESS FOR A 			#
	# PARTICULAR PURPOSE.  See the GNU General Public License 		#
	# for more details.														#
	#																			#
	# You should have received a copy of the GNU General 				#
	# Public License along with this program; if not, write to 		#
	# the Free Software Foundation, Inc., 59 Temple Place - 			#
	# Suite 330, Boston, MA  02111-1307, USA.							#
	################################################################
*/

	function __autoload($class_name) {
		require_once $class_name . '.php';
	}
if(isset($_GET["field"]))
	$field = htmlspecialchars(strtolower(trim($_GET["field"])));
else
    $field = "";
if(isset($_GET["query"]))
	$query = "".pg_escape_string(strtolower(trim(rawurldecode($_GET["query"]))));
else
	$query = "";
$dbconn = new dbcontrol();
$connectionStatus = $dbconn->connectdb();
// echo $connectionStatus;

$response = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$response.= "<RESPONSE>\n";
switch($field)
{
	case "invoicecode":
		$queryString="SELECT invoicecode,projectname,completed FROM tbl_invoice_code_lookup WHERE invoicecode LIKE '".$query."%' OR invoicecode LIKE '".strtoupper($query)."%' ORDER BY invoicecode ASC";
		$queryResult = $dbconn->sendquery2($queryString);
		$rowNr=pg_num_rows($queryResult);
		for($i=0;$i<$rowNr && $i<100;++$i)//will return only the first 100 matches
		{
			$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
			$response .= "\t<RESULT>\n";
			foreach($rowarray as $key => $value)
				$response .= "\t\t<".strtoupper($key).">".htmlspecialchars(stripslashes($value))."</".strtoupper($key).">\n";
			$response .= "\t</RESULT>\n";
		}
		
		break;
	case "taskcode":
		if(isset($_GET["limit"]) && intval($_GET["limit"])>0)
			$limit = intval($_GET["limit"]);
		else
			$limit = 30;
		$invoicecode = pg_escape_string(urldecode(strtolower(trim($_GET["invoicecode"]))));
		$queryString="SELECT tsk.taskcode,tsk.taskname,i.taskneeded FROM tbl_invoice_code_lookup i LEFT JOIN tbl_invoice_group_matrix g ON i.invoicecode = g.invoicecoderefid LEFT JOIN tbl_invoice_task_group it ON g.groupnamerefid = it.groupname LEFT JOIN tbl_task_group_matrix t ON it.groupname = t.groupnamerefid LEFT JOIN tbl_task_code_lookup tsk ON t.taskcoderefid = tsk.taskcode WHERE i.invoicecode LIKE '".strtoupper($invoicecode)."' AND ( tsk.taskcode LIKE '".$query."%' OR tsk.taskcode LIKE '".strtoupper($query)."%') ORDER BY t.taskcoderefid ASC";
		$queryResult = $dbconn->sendquery2($queryString);
		$rowNr=pg_num_rows($queryResult);
		
		for($i=0;$i<$rowNr && $i<$limit;++$i)//will return only the first $limit matches
		{
			$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
			if(strcmp(trim($query),"")==0 && floatval($rowarray["taskcode"]) != 0 )
			{//if an empty query string was given, I won't provide the numeric taskcodes.
				continue;
			}
			if(strcmp($rowarray["taskcode"],"") == 0 || (strcmp($rowarray["taskneeded"],"t") == 0 && strcmp($rowarray["taskcode"],"N/A") == 0))
				continue;
			$response .= "\t<RESULT>\n";
			foreach($rowarray as $key => $value)
				$response .= "\t\t<".strtoupper($key).">".htmlspecialchars(stripslashes($value))."</".strtoupper($key).">\n";
			$response .= "\t</RESULT>\n";
		}
		break;
	case "users":
		include("header.php");
		if(isset($_SESSION["usertype".$privateKey]) && $_SESSION["usertype".$privateKey] !=1)
			break;
		$orderBy = "go to default";
		$orderDir = "ascending";
		if(isset($_GET["by"]) && isset($_GET["dir"]))
		{
			$orderBy = htmlspecialchars(strtolower(trim($_GET["by"])));
			$orderDir = htmlspecialchars(strtolower(trim($_GET["dir"])));
		}
		if(isset($_GET["showunemployed"]) && strcmp($_GET["showunemployed"],"true")==0)
			$showunemployed = "";
		else
			$showunemployed = " employed = true AND ";
		$orderString = " ORDER BY";
		switch($orderBy)
		{
			case "lname":
				$orderString .= " lname";
				$orderString.= " ".strtoupper($orderDir);
				break;
			case "fname":
				$orderString .= " fname";
				$orderString.= " ".strtoupper($orderDir);  
				break;
			case "lname-fname":
				$orderString .= " lname";
				$orderString.= " ".strtoupper($orderDir);
				$orderString .= ", fname";
				$orderString.= " ".strtoupper($orderDir);
				break;
			case "fname-lname":
				$orderString .= " fname";
				$orderString.= " ".strtoupper($orderDir);
				$orderString .= ", lname";
				$orderString.= " ".strtoupper($orderDir);
				break;
			case "startdate":
				$orderString .= " startdate";
				$orderString.= " ".strtoupper($orderDir);
				break;
  			default:
				$orderString = " ORDER BY lname ASC, fname ASC";
		}
		$queryString="SELECT refid,fname,lname FROM tbl_staff_lookup WHERE ".$showunemployed." ( lower(lname) LIKE '".$query."%' OR lower(fname) LIKE '".$query."%' OR lower(username) LIKE '".$query."%' OR lower(email) LIKE '".$query."%') ".$orderString;
		$queryResult = $dbconn->sendquery2($queryString);
		$rowNr=pg_num_rows($queryResult);
		for($i=0;$i<$rowNr;++$i)
		{
			$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
			$response .= "\t<RESULT>\n";
			foreach($rowarray as $key => $value)
				$response .= "\t\t<".strtoupper($key).">".htmlspecialchars(stripslashes($value))."</".strtoupper($key).">\n";
			$response .= "\t</RESULT>\n";
		}
		break;
	case "managers":
		include("header.php");
		if(isset($_SESSION["usertype".$privateKey]) && $_SESSION["usertype".$privateKey] !=1)
			break;
		$queryString="SELECT refid,lname,fname FROM tbl_staff_lookup WHERE employed =  true AND lname IS NOT NULL AND fname IS NOT NULL ORDER BY lname ASC,fname ASC";
		$queryResult = $dbconn->sendquery2($queryString);
		$rowNr=pg_num_rows($queryResult);
		for($i=0;$i<$rowNr;++$i)
		{
			$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
			$response .= "\t<RESULT>\n";
			foreach($rowarray as $key => $value)
				$response .= "\t\t<".strtoupper($key).">".htmlspecialchars(stripslashes($value))."</".strtoupper($key).">\n";
			$response .= "\t</RESULT>\n";
		}
		break;
 	default: ;// nothing
}
$response.= "</RESPONSE>";
header("Content-type: application/xml");
echo $response;
?>
Return current item: Time Recording System