Location: PHPKode > projects > Time Recording System > parseResponse.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.							#
	################################################################
*/
	require("header.php");
	require_once 'dbcontrol.php';

/* 
NOTE:
	
this function takes a string with entries given in postgreSQL array format
{"str1","str2"}
and returns a XML list 
<ENTRY ID="str1"/><ENTRY ID="str2"/>
*/
	function translateEntries($identities)
	{
		if(strcmp($identities,"")==0 || strcmp($identities,"{}")==0 || strlen($identities)<=2)
			return "";
		$list = substr($identities,1,strlen($identities)-2);
		$arr = split(",",$list);
		$return = "";
		foreach($arr as $rawId)
		{
			if(strlen($rawId)<2)
				continue;
			$id = substr($rawId,1,strlen($rawId)-2);
			$return .="\t\t\t<ENTRY ID=\"".$id."\"/>\n";
		}
		return $return;
	}
	function logError($errorString)
	{
		global $privateKey;
		$filename = 'error_log';
		if(strcmp(trim($errorString),"")==0)
			return;
		if (is_writable($filename)) {
		
			if (!$handle = fopen($filename, 'a')) {
				echo "Cannot open file ($filename)";
				exit;
			}
			$line = date("Y-m-d H:m:s")."\t".$_SESSION["fname".$privateKey]."\t".$_SESSION["lname".$privateKey]."\t[ ".$errorString." ]\n\r";
			if (fwrite($handle, $line) === FALSE) {
				echo "Cannot write to file ($filename)";
				exit;
			}
			fclose($handle);
		
		} else {
			echo "The file $filename is not writable";
		}
	}
	//for debugging purposes
	function logWrite()
	{
		global $debugString, $response;
		$filename = 'log.txt';
		
		if (is_writable($filename)) {
		
		if (!$handle = fopen($filename, 'w')) {
				echo "Cannot open file ($filename)";
				exit;
		}
			if (fwrite($handle, $_POST["xmlResponse"]) === FALSE) {
				echo "Cannot write to file ($filename)";
				exit;
			}
			if (fwrite($handle, "'".$response."'") === FALSE) {
				echo "Cannot write to file ($filename)";
				exit;
			}
			if (fwrite($handle, $debugString) === FALSE) {
				echo "Cannot write to file ($filename)";
				exit;
			}
			fclose($handle);
		
		} else {
			echo "The file $filename is not writable";
		}
	}
	$tryNumber = 10;
	$dbconn = new dbcontrol();
//     logWrite();
	$status = "";
	$response = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
    
	if(!isset($_POST["xmlResponse"]))
	{
		$status = "parseerror";
		$response .= "<RESPONSE>\n";
		$response .= "\t<STATUS>".$status."</STATUS>\n";
		$response .= "</RESPONSE>\n";
		header("Content-type: application/xml");
		echo $response;
		die();
	}
	$xml = new DOMDocument();
	$xml->preserveWhiteSpace=false;
	$xml->loadXML(rawurldecode($_POST["xmlResponse"]));
	
// 	$xml->load("log.xml");
	$debugString = "";

	$requests = $xml->getElementsByTagName("REQUEST");
	if($requests->length != 0)
	{
//here is where the database gets any data related to the requested timesheet and sends it to the interface for update

		$request = $requests->item(0);
		$reqObject = $request->getAttribute("OBJECT");
		$connectionStatus = $dbconn->connectdb();
		//manage invoice code related stuff - invoice code manager access only
		if(strcmp($reqObject,"saveInvoice")==0)
		{
			//search for a match with the current user as manager
			//update details if they check
			
			$invs = $request->getElementsByTagName("INVOICECODE");
			$invoicecode = strtoupper(pg_escape_string(htmlspecialchars_decode($invs->item(0)->nodeValue)));
			$pns = $request->getElementsByTagName("PROJECTNAME");
			$projectname = strtoupper(pg_escape_string(htmlspecialchars_decode($pns->item(0)->nodeValue)));
			$deps = $request->getElementsByTagName("DEPARTMENT");
			$department = strtoupper(pg_escape_string(htmlspecialchars_decode($deps->item(0)->nodeValue)));
			
			$completed = strcmp(strtolower($request->getElementsByTagName("COMPLETED")->item(0)->nodeValue),"true")==0 ? 'true' : 'false' ;
			$taskneeded = strcmp(strtolower($request->getElementsByTagName("TASKNEEDED")->item(0)->nodeValue),"true")==0 ? 'true' : 'false';
			$requiresauthorization = strcmp(strtolower($request->getElementsByTagName("REQAUTH")->item(0)->nodeValue),"true")==0 ? 'true' : 'false';
			
			$queryString ="UPDATE tbl_invoice_code_lookup SET projectname = '".substr($projectname,0,50)."', department = '".substr($department,0,20)."', completed = ".$completed.", taskneeded = ".$taskneeded.", requiresauthorization = ".$requiresauthorization." WHERE invoicecode = '".$invoicecode."'";
			$queryResult = $dbconn->sendquery2($queryString);
			$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
			$debugString.= pg_last_error();
			switch (pg_affected_rows($queryResult))
			{
				case 0: 
					$status = "Error: The invoice code could not be found";
					break;
				case 1: 
					$status = "Invoice code details saved successfuly";
					break;
				default:
					$status = "Warning: Your action updated more than one invoice code";
			} 
		}
		if(strcmp($reqObject,"addTask")==0 || strcmp($reqObject,"saveTask")==0)
		{
			$tasks = $request->getElementsByTagName("TASKCODE");
			$taskcode = substr(strtoupper(pg_escape_string(htmlspecialchars_decode($tasks->item(0)->nodeValue))),0,30);
			$tasknames = $request->getElementsByTagName("TASKNAME");
			$taskname = substr(strtoupper(pg_escape_string(htmlspecialchars_decode($tasknames->item(0)->nodeValue))),0,50);
			//search for a match for the given task code
			$queryString ="SELECT * FROM tbl_task_code_lookup WHERE taskcode = '".$taskcode."'";
			$queryResult = $dbconn->sendquery2($queryString);
			$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
			}
			$rowNr = $dbconn->numberofrows();
			if($rowNr != 0)
			{
				//if found and reqObject == addTask, then throw error
				if(strcmp($reqObject,"addTask")==0)
				//if found and reqObject == addTask, then throw error
				if(strcmp($reqObject,"addTask")==0)
				{
					$invs = $request->getElementsByTagName("INVOICECODE");
					$invoicecode = strtoupper(pg_escape_string(htmlspecialchars_decode($invs->item(0)->nodeValue)));
					
					$queryString="SELECT groupnamerefid FROM tbl_invoice_group_matrix WHERE invoicecoderefid = '".$invoicecode."'";
					$queryResult = $dbconn->sendquery2($queryString);
					//CAUTION: DB design allows many groupnames, even though the logic of this app does not permit this
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
					}
					$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
					$groupname = $rowarray["groupnamerefid"];
					//checking if this taskcode is already assigned to this groupname
					$queryString ="SELECT * FROM tbl_task_group_matrix WHERE taskcode = '".$taskcode."' AND groupnamerefid = '".$groupname."'";
					$queryResult = $dbconn->sendquery2($queryString);
					$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
					}
					$rowNr = $dbconn->numberofrows();
					if($rowNr > 0)
						$status = "Error: taskcode '".$tasks->item(0)->nodeValue."' is already assigned to this project";
					else
					{
						$queryString="START TRANSACTION";
						$queryResult = $dbconn->sendquery2($queryString);
						if($queryResult===FALSE)
						{
							$debugString.= pg_last_error();
							logError(pg_last_error());
						}
						
						$queryString="INSERT INTO tbl_task_group_matrix (taskcoderefid,groupnamerefid) VALUES ('".$taskcode."','".$groupname."')";
						$queryResult = $dbconn->sendquery2($queryString);
						if($queryResult===FALSE)
						{
							$debugString.= pg_last_error();
							logError(pg_last_error());
							$queryString="ROLLBACK";
							$queryResult = $dbconn->sendquery2($queryString);
							$status = "Task code was not added successfuly";
						}
						else
						{
							$queryString="COMMIT";
							$queryResult = $dbconn->sendquery2($queryString);
							$status = "Task code assigned successfuly";
						}
					}
				}
				//if found and reqObject == saveTask, update data
				else
				{
					$queryString ="UPDATE tbl_task_code_lookup SET taskname = '".substr($taskname,0,500)."' WHERE taskcode = '".$taskcode."'";
					$queryResult = $dbconn->sendquery2($queryString);
					$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
					}
					$status = "Task code updated successfuly";
				}
			}
			else
			{
				//if not found and reqObject == addTask, add the new task
				if(strcmp($reqObject,"addTask")==0)
				{
					$invs = $request->getElementsByTagName("INVOICECODE");
					$invoicecode = strtoupper(pg_escape_string(htmlspecialchars_decode($invs->item(0)->nodeValue)));

					$queryString="START TRANSACTION";
					$queryResult = $dbconn->sendquery2($queryString);
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
					}
					$queryString="INSERT INTO tbl_task_code_lookup (taskcode,taskname) VALUES ('".substr($taskcode,0,30)."','".substr($taskname,0,500)."')";
					$queryResult = $dbconn->sendquery2($queryString);
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
					}
					$queryString="SELECT groupnamerefid FROM tbl_invoice_group_matrix WHERE invoicecoderefid = '".$invoicecode."'";
					$queryResult = $dbconn->sendquery2($queryString);
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
					}
					$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
					$groupname = $rowarray["groupnamerefid"];
					$queryString="INSERT INTO tbl_task_group_matrix (taskcoderefid,groupnamerefid) VALUES ('".substr($taskcode,0,30)."','".$groupname."')";
					$queryResult = $dbconn->sendquery2($queryString);
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
						$queryString="ROLLBACK";
						$queryResult = $dbconn->sendquery2($queryString);
						$status = "Task code was not added successfuly";
					}
					else
					{
						$queryString="COMMIT";
						$queryResult = $dbconn->sendquery2($queryString);
						$status = "Task code added successfuly";
					}
				}
				//if not found and reqObject == saveTask, delete the previous taskcode and then insert
				else
				{
					$status = "Error: taskcode '".$tasks->item(0)->nodeValue."' was not found in the database";
				}
			}
		}
		//employee related stuff - admin access only
		if(strcmp($reqObject,"saveEmployee")==0)
		{
			if($_SESSION["usertype".$privateKey] !=1)
				$status = "access denied";
			else
			{
				$refids = $request->getElementsByTagName("REFID");
				$refid = intval($refids->item(0)->nodeValue);
				
				$lname = strtolower(pg_escape_string($request->getElementsByTagName("LNAME")->item(0)->nodeValue));
				$fname = strtolower(pg_escape_string($request->getElementsByTagName("FNAME")->item(0)->nodeValue));
				$startdate = strtolower(pg_escape_string($request->getElementsByTagName("STARTDATE")->item(0)->nodeValue));
				$dateArr = split("/",$startdate);
				if(strlen($dateArr[1]) < 2)
					$dateArr[1] = '0'.$dateArr[1];
				if(strlen($dateArr[0]) < 2)
					$dateArr[0] = '0'.$dateArr[0];
				$usDate = $dateArr[1]."/".$dateArr[0]."/".$dateArr[2];
				$startdate = date("Y-m-d",strtotime($usDate));
				
				$linemanager = intval($request->getElementsByTagName("LINEMANAGER")->item(0)->nodeValue);//a refference id
				$username = strtolower(pg_escape_string($request->getElementsByTagName("USERNAME")->item(0)->nodeValue));
				$ldapUserName = strtolower(pg_escape_string($request->getElementsByTagName("LDAPUSERNAME")->item(0)->nodeValue));
				$email = strtolower(pg_escape_string($request->getElementsByTagName("EMAIL")->item(0)->nodeValue));
				$spinal = intval($request->getElementsByTagName("SPINAL")->item(0)->nodeValue);
				$usertype = intval($request->getElementsByTagName("USERTYPE")->item(0)->nodeValue);
				$minhours = floatval($request->getElementsByTagName("MINHOURS")->item(0)->nodeValue);
				$authorizes = strcmp($request->getElementsByTagName("AUTHORIZES")->item(0)->nodeValue,"true")==0 ? "true" : "false" ;
				$employed = strcmp($request->getElementsByTagName("EMPLOYED")->item(0)->nodeValue,"true")==0 ? "true" : "false" ;
				$variable = strcmp($request->getElementsByTagName("VARIABLE")->item(0)->nodeValue,"true")==0 ? "true" : "false" ;
				$enrolled = strcmp($request->getElementsByTagName("ENROLLED")->item(0)->nodeValue,"true")==0 ? "true" : "false" ;
				$passphrase = 'default';
				
				$queryString="START TRANSACTION";
				$queryResult = $dbconn->sendquery2($queryString);
				$ok= true;
				if($refid == -1)
				{
					$queryString = "SELECT MAX(refid) FROM tbl_staff_lookup";
					$queryResult = $dbconn->sendquery2($queryString);
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
						$ok= false;
					}
					if($dbconn->numberofrows() == 0)
						$newRefid = 0;
					else
					{	
						$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
						$newRefid = intval($rowarray["max"])+1;
					}
					$queryResult = false;
					$tries = 0;
					while(!$queryResult && $tries<$tryNumber)
					{
						if($linemanager >= 0)
							$queryString = "INSERT INTO tbl_staff_lookup (refid,fname,lname,startdate,passphrase,linemanager,username,ldap_username,spinal,authorizes,minhours,email,employed,enrolled,usertype) VALUES (".$newRefid.",'".substr($fname,0,50)."','".substr($lname,0,50)."','".$startdate."',MD5('".$passphrase."'),".$linemanager.",'".substr($username,0,50)."','".substr($ldapUserName,0,50)."',".$spinal.",".$authorizes.",".$minhours.",'".substr($email,0,50)."',".$employed.",".$enrolled.",".$usertype.")";
						else
							$queryString = "INSERT INTO tbl_staff_lookup (refid,fname,lname,startdate,passphrase,username,ldap_username,spinal,authorizes,minhours,email,employed,enrolled,usertype) VALUES (".$newRefid.",'".substr($fname,0,50)."','".substr($lname,0,50)."','".$startdate."',MD5('".$passphrase."'),'".substr($username,0,50)."','".substr($ldapUserName,0,50)."',".$spinal.",".$authorizes.",".$minhours.",'".substr($email,0,50)."',".$employed.",".$enrolled.",".$usertype.")";
						$queryResult = $dbconn->sendquery2($queryString);
						++$newRefid;
						++$tries;
					}
					$debugString .= $queryString."\n";
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
						$ok= false;
					}
					if($tries ==$tryNumber)
						$status = "dberror";
					else
						--$newRefid; //because it's incremented once after success
				}
				else
				{
					if($linemanager >= 0)
						$queryString ="UPDATE tbl_staff_lookup SET fname = '".substr($fname,0,50)."', lname = '".substr($lname,0,50)."', startdate = '".$startdate."', linemanager = ".$linemanager.",username = '".substr($username,0,50)."',ldap_username = '".substr($ldapUserName,0,50)."',spinal = ".$spinal.", authorizes = ".$authorizes.", minhours = ".$minhours.",email = '".substr($email,0,50)."', employed = ".$employed.",usertype= ".$usertype.",variable= ".$variable.",enrolled= ".$enrolled." WHERE refid = ".$refid;
					else
						$queryString ="UPDATE tbl_staff_lookup SET fname = '".substr($fname,0,50)."', lname = '".substr($lname,0,50)."', startdate = '".$startdate."', linemanager = null,username = '".substr($username,0,50)."',ldap_username = '".substr($ldapUserName,0,50)."',spinal = ".$spinal.", authorizes = ".$authorizes.", minhours = ".$minhours.",email = '".substr($email,0,50)."', employed = ".$employed.",usertype= ".$usertype.",variable= ".$variable.",enrolled= ".$enrolled." WHERE refid = ".$refid;
					$queryResult = $dbconn->sendquery2($queryString);
					$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
					if($queryResult===FALSE)
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
						$ok= false;
					}
				}
				if($ok)
				{
					$queryString="COMMIT";
					$queryResult = $dbconn->sendquery2($queryString);
					$status = "save successful";
				}
				else
				{
					$queryString="ROLLBACK";
					$queryResult = $dbconn->sendquery2($queryString);
					$status = "save UNsuccessful";
				}
			}
		}
		if(strcmp($reqObject,"deleteEmployee")==0)
		{
			$refids = $request->getElementsByTagName("REFID");
			if($_SESSION["usertype".$privateKey] !=1)
				$status = "access denied";
			else
			{
				$refidList = "(";
				for($i =0; $i< $refids->length-1; ++$i)
					$refidList .= intval($refids->item($i)->nodeValue).",";
				$refidList .= intval($refids->item($refids->length-1)->nodeValue).")";
		        
				$queryString ="DELETE FROM tbl_staff_lookup WHERE refid IN ".$refidList;
				$queryResult = $dbconn->sendquery2($queryString);
				$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
				if($queryResult===FALSE)
				{
					$debugString.= pg_last_error();
					logError(pg_last_error());
					$status = "delete UNsuccesful";
				}
				else
					$status = "delete succesful";
			}
		
			$response .= "<RESPONSE>\n";
			$response .= "\t<STATUS>".$status."</STATUS>\n";
			for($i =0; $i< $refids->length-1; ++$i)
				$response .= "\t<REFID>".$refids->item($i)->nodeValue."</REFID>\n";
			$response .= "</RESPONSE>\n";
			header("Content-type: application/xml");
			echo $response;
			die();
		}
		//this happens when discard button is pressed in option list 
		if(strcmp($reqObject,"deleteTimesheet")==0)
		{
			$refids = $request->getElementsByTagName("REFID");
			$refid = intval($refids->item(0)->nodeValue);
	
			if($refid !=-1 && checkCredentials($dbconn,$refid) != 0)
			{
				$queryString="START TRANSACTION";
				$queryResult = $dbconn->sendquery2($queryString);
				$ok = true;
				//delete part
				$queryString ="DELETE FROM tbl_office_time_sheet_entry_details WHERE officetimesheetentryrefid IN (SELECT refid FROM tbl_office_time_sheet_entry WHERE officetimesheetrefid = ".$refid.") ";
				$queryResult = $dbconn->sendquery2($queryString);
				$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
				if($queryResult===FALSE)
				{
					$debugString.= pg_last_error();
					logError(pg_last_error());
					$ok = false;
				}
				$queryString ="DELETE FROM tbl_office_time_sheet_entry WHERE officetimesheetrefid = ".$refid;
				$queryResult = $dbconn->sendquery2($queryString);
				$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
				if($queryResult===FALSE)
				{
					$debugString.= pg_last_error();
					logError(pg_last_error());
					$ok = false;
				}
				$queryString ="DELETE FROM tbl_office_time_sheet WHERE refid = ".$refid;
				$queryResult = $dbconn->sendquery2($queryString);
				$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
				if($queryResult===FALSE)
				{
					$debugString.= pg_last_error();
					logError(pg_last_error());
					$ok = false;
				}
				if($ok)
				{
					$queryString="COMMIT";
					$queryResult = $dbconn->sendquery2($queryString);
					$status = "deleted";
				}
				else
				{
					$queryString="ROLLBACK";
					$queryResult = $dbconn->sendquery2($queryString);
					$status = "delete error";
				}
			}
			else
				$status = "delete error";
		}
		if(strcmp($reqObject,"submitTimesheet")==0)
		{
			$status = "No refid or insufficient rights for this action";
			$refids = $request->getElementsByTagName("REFID");
			$refid = intval($refids->item(0)->nodeValue);
	
			$credentials = checkCredentials($dbconn,$refid);
			if($refid !=-1 && ($credentials == 3 || $credentials == 2))//if it's an admin or a line manager
			{
				$queryString ="UPDATE tbl_office_time_sheet SET submitted = true WHERE refid = ".$refid;
				$queryResult = $dbconn->sendquery2($queryString);
				$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
				if($queryResult===FALSE)
				{
					$debugString.= pg_last_error();
					logError(pg_last_error());
				}
				else
					$status = "submitted";
			}
		}
		if(in_array($reqObject,Array("saveEmployee","deleteTimesheet","saveInvoice","saveTask","addTask","submitTimesheet")))
		{
			$response .= "<RESPONSE>\n";
			$response .= "\t<STATUS>".$status."</STATUS>\n";
			if(isset($refid))
				$response .= "\t<REFID>".$refid."</REFID>\n";
			$response .= "</RESPONSE>\n";
			header("Content-type: application/xml");
			echo $response;
// 			logWrite();
			die();
		}
		//this is requested by timesheetArchive.php script
		if(strcmp($reqObject,"saveAdjustment")==0)
		{
			$status = "save failed";
			$adjustments = $request->getElementsByTagName("ADJUSTMENT");
			$adjustment = floatval($adjustments->item(0)->nodeValue);
			
			$queryString ="UPDATE tbl_staff_preferences SET toiladjustment = ".$adjustment." WHERE refid = ".$_SESSION["refid".$privateKey];
			$queryResult = $dbconn->sendquery2($queryString);
			$debugString.= $queryString." - ".pg_affected_rows($queryResult)."\n";
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
			}
			else
			{
				$status = "saved";
				$statusText = "The adjustment value has been saved";
			}
			$response .= "<RESPONSE>\n";
			$response .= "\t<STATUS>".$status."</STATUS>\n";
			$response .= "\t<STATUSTEXT>".$statusText."</STATUSTEXT>\n";
			$response .= "</RESPONSE>\n";
			header("Content-type: application/xml");
			echo $response;
			die();
		} 
		//this is called by timeTotaler.php to get the total time spent on a specific project
		if(strcmp($reqObject,"timetotal")==0)
		{
			$invs = $request->getElementsByTagName("INVOICECODE");
			$invoicecode = strtoupper(pg_escape_string(htmlspecialchars_decode($invs->item(0)->nodeValue)));
			$tsks = $request->getElementsByTagName("TASKCODE");
			$taskcode = strtoupper(pg_escape_string(htmlspecialchars_decode($tsks->item(0)->nodeValue)));
			$emps = $request->getElementsByTagName("EMPLOYEE");
			$employee = intval($emps->item(0)->nodeValue);
			
			$taskCheck = ( strcmp(strtolower($request->getElementsByTagName("TASKCHECK")->item(0)->nodeValue),"true")==0 );
			$employeeCheck = ( strcmp(strtolower($request->getElementsByTagName("EMPLOYEECHECK")->item(0)->nodeValue),"true")==0 );
			$selftime = ( strcmp(strtolower($request->getElementsByTagName("SELFTIME")->item(0)->nodeValue),"true")==0 );
			$status = "valid";
			$statusText = "Everything is fine!";
			
			if(strcmp(trim($taskcode),"")==0)
				$taskcode = "N/A";
			if($taskCheck)
			{
				$projectDetails = "a.invoicecoderefid = '".$invoicecode."' AND a.taskcoderefid = '".$taskcode."'";
			}
			else
			{
				$projectDetails = "a.invoicecoderefid = '".$invoicecode."'";
			}
			//if the user does not have administrative privileges
			if($_SESSION["usertype".$privateKey] !=1 || $selftime)
			{
				$employee = $_SESSION["refid".$privateKey];
				$employeeCheck = true;
			}
			if($employeeCheck)
				$queryString = "SELECT SUM(b.hours) AS total FROM tbl_office_time_sheet x JOIN tbl_office_time_sheet_entry a ON x.refid = a.officetimesheetrefid LEFT JOIN tbl_office_time_sheet_entry_details b ON a.refid = b.officetimesheetentryrefid WHERE x.staffrefid = ".$employee." AND ".$projectDetails." ";
			else
				$queryString = "SELECT SUM(b.hours) AS total FROM tbl_office_time_sheet_entry a LEFT JOIN tbl_office_time_sheet_entry_details b ON a.refid = b.officetimesheetentryrefid WHERE ".$projectDetails." ";
			$queryResult = $dbconn->sendquery2($queryString);
			$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
			$total =  $rowarray["total"];

			$response .= "<RESPONSE>\n";
			$response .= "\t<STATUS>".$status."</STATUS>\n";
			$response .= "\t<STATUSTEXT>".$statusText."</STATUSTEXT>\n";
			$response .= "\t<TOTAL>".$total."</TOTAL>\n";
			$response .= "</RESPONSE>\n";
			header("Content-type: application/xml");
			echo $response;
			die();
		}
//this gets beyond this point only if the requested object is a time sheet
		if(strcmp($reqObject,"timesheet")!=0)
		{
			$response .= "<RESPONSE>\n";
			$response .= "\t<STATUS>request unrecognised</STATUS>\n";
			$response .= "</RESPONSE>\n";
			header("Content-type: application/xml");
			echo $response;
			die();
		}
		$refids = $request->getElementsByTagName("REFID");
		$refid = intval($refids->item(0)->nodeValue);
			
		$staffrefid = $_SESSION["refid".$privateKey];
		if(isset($_SESSION["adminCreatingTimeSheetFor".$privateKey]))
			$staffrefid = intval($_SESSION["adminCreatingTimeSheetFor".$privateKey]);
		
		$response .= "<FORM>\n";
		
		//checking if this user has access to the referenced timesheet
		if(checkCredentials($dbconn,$refid)==0)
		{
			//let's check if the user has access only to some entries of this time sheet
			$queryString="SELECT invoicecode FROM tbl_invoice_code_lookup WHERE invoicecode IN (SELECT invoicecoderefid FROM tbl_office_time_sheet_entry WHERE officetimesheetrefid = ".$refid." AND authorized = false) AND projectmanager = ".$staffrefid." AND requiresauthorization = true";
			$queryResult = $dbconn->sendquery2($queryString);
			$rowNr = $dbconn->numberofrows($queryResult);
			if($rowNr==0)
				header("Location: index2.php");
			else
			{
				$invoiceCodeList = "";
				for($i=0;$i<$rowNr;++$i)
				{
					$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
					$invoiceCodeList .= "'".$rowarray["invoicecode"]."',";
				}
				//removing the last comma
				$invoiceCodeList = substr($invoiceCodeList,0,strlen($invoiceCodeList)-1);
			}
		}
		
		if(isset($invoiceCodeList))
			$queryString="SELECT a.invoicecoderefid,a.taskcoderefid,a.colour,b.dateworked,b.ratetype,b.hours,b.ratevalue,b.identities,c.projectname FROM tbl_office_time_sheet_entry AS a LEFT JOIN tbl_office_time_sheet_entry_details AS b ON a.refid = b.officetimesheetentryrefid,tbl_invoice_code_lookup AS c WHERE c.invoicecode = a.invoicecoderefid AND a.authorized = false AND a.officetimesheetrefid = ".$refid." AND a.invoicecoderefid IN (".$invoiceCodeList.")ORDER BY a.refid";//a.invoicecoderefid ASC, a.taskcoderefid ASC, b.dateworked ASC, b.ratetype ASC";
		else
			$queryString="SELECT a.invoicecoderefid,a.taskcoderefid,a.colour,b.dateworked,b.ratetype,b.hours,b.ratevalue,b.identities,c.projectname FROM tbl_office_time_sheet_entry AS a LEFT JOIN tbl_office_time_sheet_entry_details AS b ON a.refid = b.officetimesheetentryrefid,tbl_invoice_code_lookup AS c WHERE c.invoicecode = a.invoicecoderefid AND a.officetimesheetrefid = ".$refid." ORDER BY a.refid";//a.invoicecoderefid ASC, a.taskcoderefid ASC, b.dateworked ASC, b.ratetype ASC";
		$debugString .= "\n".$queryString;
		$queryResult = $dbconn->sendquery2($queryString);
		$rowNr=pg_num_rows($queryResult);

		if($rowNr > 0)
		{
			//first row is always special!!
				$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
				$invoiceCode =  htmlspecialchars(stripslashes($rowarray["invoicecoderefid"]));
				$taskCode =  htmlspecialchars(stripslashes($rowarray["taskcoderefid"]));
				$projectName =  htmlspecialchars(stripslashes($rowarray["projectname"]));
				$colour = $rowarray["colour"];
				$date = date("d/m/Y",strtotime($rowarray["dateworked"]));
				$rateType = $rowarray["ratetype"];
				$rateValue = $rowarray["ratevalue"];
				$hours = $rowarray["hours"];
				$identities = stripslashes($rowarray["identities"]);
	
			$response .="\t<STRUCT INVOICECODE=\"".$invoiceCode."\" TASKCODE=\"".$taskCode."\" PROJECTNAME=\"".$projectName."\" COLOUR=\"".$colour."\">\n";
			$response .="\t\t<CHILDNODE";
			$response .=" DATE=\"".$date."\"";
			$response .=" RATETYPE=\"".$rateType."\"";
			$response .=" RATEVALUE=\"".$rateValue."\"";
			$response .=" HOURS=\"".$hours."\"";
			$response .=">\n";
			$response .=translateEntries($identities);
	
			$prevInvoiceCode = $invoiceCode;
			$prevTaskCode = $taskCode;
	
			for($i = 1; $i < $rowNr; ++$i)
			{
				$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
				$invoiceCode =  htmlspecialchars(stripslashes($rowarray["invoicecoderefid"]));
				$taskCode =  htmlspecialchars(stripslashes($rowarray["taskcoderefid"]));
				$projectName =  htmlspecialchars(stripslashes($rowarray["projectname"]));
				$colour = $rowarray["colour"];
				$date =  date("d/m/Y",strtotime($rowarray["dateworked"]));
				$rateType = $rowarray["ratetype"];
				$rateValue = $rowarray["ratevalue"];
				$hours = $rowarray["hours"];
				$identities = stripslashes($rowarray["identities"]);
				
				if(strcmp($invoiceCode,$prevInvoiceCode)!=0 || strcmp($taskCode,$prevTaskCode)!=0 )
				{
					$response .="\t\t</CHILDNODE>\n";
					$response .="\t</STRUCT>\n";
					$response .="\t<STRUCT INVOICECODE=\"".$invoiceCode."\" TASKCODE=\"".$taskCode."\" PROJECTNAME=\"".$projectName."\" COLOUR=\"".$colour."\">\n";
					$response .="\t\t<CHILDNODE";
					$response .=" DATE=\"".$date."\"";
					$response .=" RATETYPE=\"".$rateType."\"";
					$response .=" RATEVALUE=\"".$rateValue."\"";
					$response .=" HOURS=\"".$hours."\"";
					$response .=">\n";
					$response .= translateEntries($identities);
					$prevInvoiceCode = $invoiceCode;
					$prevTaskCode = $taskCode;
					continue;
				}
				$response .="\t\t</CHILDNODE>\n";
				$response .="\t\t<CHILDNODE";
				$response .=" DATE=\"".$date."\"";
				$response .=" RATETYPE=\"".$rateType."\"";
				$response .=" RATEVALUE=\"".$rateValue."\"";
				$response .=" HOURS=\"".$hours."\"";
				$response .=">\n";
				$response .= translateEntries($identities);
			}
			$response .="\t\t</CHILDNODE>\n";
			$response .="\t</STRUCT>\n";
		}
		$response .= "</FORM>\n";
	}
	else
	{
		if(isset($_POST["xmlResponse"]))
			$status = "received";
			
//here is where data received from the interface should be processed and added to the database
		$connectionStatus = $dbconn->connectdb();

		$forms = $xml->getElementsByTagName("FORM");
		$form = $forms->item(0);
		$final = $form->getAttribute("FINAL");
		$weekend = $form->getAttribute("SHOWWEEKEND");
		$refid = intval($form->getAttribute("REFID"));
		$startTime = $form->getAttribute("STARTTIME");
		$stopTime = $form->getAttribute("STOPTIME");
		$queryString ="START TRANSACTION";
		$queryResult = $dbconn->sendquery2($queryString);
		$ok = true;
// NOTE: this line has no effect without using the returned value!!
		checkCredentials($dbconn,$refid);
		if($refid !=-1)
		{//update = delete + insert
			 //delete part
			$queryString ="DELETE FROM tbl_office_time_sheet_entry_details WHERE officetimesheetentryrefid IN (SELECT refid FROM tbl_office_time_sheet_entry WHERE officetimesheetrefid = ".$refid.") ";
			$queryResult = $dbconn->sendquery2($queryString);
			if($queryResult===FALSE)
			{
				logError(pg_last_error());
				$ok = false;
			}
			$debugString .= $queryString." - ".pg_affected_rows($queryResult)."\n";
			$queryString ="DELETE FROM tbl_office_time_sheet_entry WHERE officetimesheetrefid = ".$refid;
			$queryResult = $dbconn->sendquery2($queryString);
			if($queryResult===FALSE)
			{
				logError(pg_last_error());
				$ok = false;
			}
			$debugString .= $queryString." - ".pg_affected_rows($queryResult)."\n";
			$queryString ="UPDATE tbl_office_time_sheet SET submitted = ".pg_escape_string($final).",showweekend = ".pg_escape_string($weekend).", starttime = '".pg_escape_string($startTime)."', stoptime = '".pg_escape_string($stopTime)."' WHERE refid = ".$refid;
			$queryResult = $dbconn->sendquery2($queryString);
			$debugString .= $queryString." - ".pg_affected_rows($queryResult)."\n";
		    	if($queryResult===FALSE)
			{
				logError(pg_last_error());
				$ok = false;
			}
		}
		else
		{//inserting a new timesheet
			
			$startTime = $form->getAttribute("STARTTIME");
			$stopTime = $form->getAttribute("STOPTIME");
			$showWeekend = $form->getAttribute("SHOWWEEKEND");
			$resolution = $form->getAttribute("RESOLUTION");
			$weekEndingDate = $form->getAttribute("WEEKENDINGDATE");
			$dateArr = split("/",$weekEndingDate);
			$usDate = $dateArr[1]."/".$dateArr[0]."/".$dateArr[2];
			$staffrefid = $_SESSION["refid".$privateKey];
			if(isset($_SESSION["adminCreatingTimeSheetFor".$privateKey]))
				$staffrefid = intval($_SESSION["adminCreatingTimeSheetFor".$privateKey]);

			$queryString="SELECT MAX(refid) FROM tbl_office_time_sheet";
			$queryResult = $dbconn->sendquery2($queryString);
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
				$ok = false;
			}
			if($dbconn->numberofrows() == 0)
				$newRefid = 0;
			else
			{	
				$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
				$newRefid = intval($rowarray["max"])+1;
			}
			$queryResult = false;
			$tries = 0;
			while(!$queryResult && $tries<$tryNumber)
			{
				$queryString = "INSERT INTO tbl_office_time_sheet (refid,staffrefid,startdate,guiresolution,starttime,stoptime,showweekend,submitted) VALUES (".$newRefid.",".$staffrefid.",'".date("Y-m-d",strtotime($usDate))."',".$resolution.",'".$startTime."','".$stopTime."',".$showWeekend.",".$final.")";
				$queryResult = $dbconn->sendquery2($queryString);
				++$newRefid;
				++$tries;
			}
			$debugString .= $queryString."\n";
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
				$ok = false;
			}
			if($tries ==$tryNumber)
				$status = "dberror";
			else
				--$newRefid; //because it's incremented once after success
			$refid=$newRefid;
		}
		//insert part
		$structs = $form->getElementsByTagName("STRUCT");
		$i = 0;
		for($i = 0; $i < $structs->length; ++$i)
		{
			$struct = $structs->item($i);
			$invoiceCode = strtoupper(pg_escape_string($struct->getAttribute("INVOICECODE")));
			$taskCode = strtoupper(pg_escape_string(trim($struct->getAttribute("TASKCODE"))));
			//this is usually blank, unless the taskcode is new to the database
			$taskName = pg_escape_string(trim($struct->getAttribute("TASKNAME")));
			$colour = $struct->getAttribute("COLOUR");
			$queryString="SELECT * FROM tbl_invoice_code_lookup WHERE invoicecode = '".$invoiceCode."'";
			$queryResult = $dbconn->sendquery2($queryString);
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
				$ok = false;
			}
			if($dbconn->numberofrows() == 0)
			{
				$status = "invoicecodeerror";
				break;
			}
			$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
			$allowedNewTaskCodes = true;
			if(strcmp($rowarray["department"],"PX")!=0 && (strlen($invoiceCode) < 3 || strcmp(substr($invoiceCode,0,3),"BID")!=0))
				$allowedNewTaskCodes = false;
			if(strcmp($rowarray["taskneeded"],"t")==0 || strcmp($taskCode,"")!=0)
			{
				$queryString="SELECT * FROM tbl_task_code_lookup WHERE taskcode = '".$taskCode."'";
				$queryResult = $dbconn->sendquery2($queryString);
				if($queryResult===FALSE)
				{
					$debugString.= pg_last_error();
					logError(pg_last_error());
					$ok = false;
				}
				if($dbconn->numberofrows() == 0)
				{
					if($allowedNewTaskCodes)
					{//for this invoice code, new task codes are allowed to be inserted
						$queryString="INSERT INTO tbl_task_code_lookup (taskcode,taskname) VALUES ('".substr($taskCode,0,30)."','".substr($taskName,0,500)."')";
						$queryResult = $dbconn->sendquery2($queryString);
						if($queryResult===FALSE)
						{
							$debugString.= pg_last_error();
							logError(pg_last_error());
							$ok = false;
						}
						$queryString="SELECT groupnamerefid FROM tbl_invoice_group_matrix WHERE invoicecoderefid = '".$invoiceCode."'";
						$queryResult = $dbconn->sendquery2($queryString);
						if($queryResult===FALSE)
						{
							$debugString.= pg_last_error();
							logError(pg_last_error());
							$ok = false;
						}
						$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
						$groupName = $rowarray["groupnamerefid"];
						$queryString="INSERT INTO tbl_task_group_matrix (taskcoderefid,groupnamerefid) VALUES ('".substr($taskCode,0,30)."','".$groupName."')";
						$queryResult = $dbconn->sendquery2($queryString);
						if($queryResult===FALSE)
						{
							$debugString.= pg_last_error();
							logError(pg_last_error());
							$ok = false;
						}
					}
					else
					{
						$debugString.= pg_last_error();
						logError(pg_last_error());
						$ok = false;
						$status = "taskcodeerror";
						break;
					}
				}
			}
			else
				$taskCode = "N/A";
			$queryString="SELECT MAX(refid) FROM tbl_office_time_sheet_entry";
			$queryResult = $dbconn->sendquery2($queryString);
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
				$ok = false;
			}
			if($dbconn->numberofrows() == 0)
				$structRefid = 0;
			else
			{
				$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
				$structRefid = intval($rowarray["max"])+1;
			}
			$queryResult = false;
			$tries = 0;
			while(!$queryResult && $tries<$tryNumber)
			{
				$queryString = "INSERT INTO tbl_office_time_sheet_entry (refid,invoicecoderefid,taskcoderefid,officetimesheetrefid,colour) VALUES (".$structRefid.",'".substr($invoiceCode,0,30)."','".substr($taskCode,0,30)."',".$refid.",'".substr($colour,0,20)."')";
				$queryResult = $dbconn->sendquery2($queryString);
				++$structRefid;
				++$tries;
			}
			$debugString .= $queryString."\n";
			if($queryResult===FALSE)
			{
				$debugString.= pg_last_error();
				logError(pg_last_error());
				$ok = false;
			}
			if($tries ==$tryNumber)
				$status = "dberror";
			else
				--$structRefid;

			$childnodes = $struct->getElementsByTagName("CHILDNODE");
			for($j = 0 ; $j < $childnodes->length ; ++$j)
			{
				$childnode = $childnodes->item($j);
				$rateType = $childnode->getAttribute("RATETYPE");
				$rateValue = $childnode->getAttribute("RATEVALUE");
				$date = $childnode->getAttribute("DATE");
				$hours = $childnode->getAttribute("HOURS");
				$dateArr = split("/",$date);
				$usDate = $dateArr[1]."/".$dateArr[0]."/".$dateArr[2];

				$queryString="SELECT MAX(refid) FROM tbl_office_time_sheet_entry_details";
				$queryResult = $dbconn->sendquery2($queryString);
				if($queryResult===FALSE)
				{
					$debugString .= pg_last_error();
					logError(pg_last_error());
					$ok = false;
				}
				//assembling the identity list string
				$identities = "{";
				$entries = $childnode->getElementsByTagName("ENTRY");
				for($k = 0; $k < $entries->length-1; ++$k)
				{
					$entry = $entries->item($k);
					$guiref = $entry->getAttribute("ID");
					$identities .= "\"".$guiref."\",";
				}
				$entry = $entries->item($entries->length-1);
				$guiref = $entry->getAttribute("ID");
				$identities .= "\"".$guiref."\"}";
				$identities = pg_escape_string($identities);

				if($dbconn->numberofrows() == 0)
					$childnodeRefid = 0;
				else
				{
					$rowarray = pg_fetch_array($queryResult,null,PGSQL_ASSOC);
					$childnodeRefid = intval($rowarray["max"])+1;
				}
				$queryResult = false;
				$tries = 0;
				while(!$queryResult && $tries<$tryNumber)
				{
					$queryString = "INSERT INTO tbl_office_time_sheet_entry_details (refid,dateworked,officetimesheetentryrefid,ratetype,hours,ratevalue,identities) VALUES (".$childnodeRefid.",'".$usDate."',".$structRefid.",'".substr($rateType,0,20)."',".$hours.",'".substr($rateValue,0,10)."','".$identities."')";
					$queryResult = $dbconn->sendquery2($queryString);
					++$childnodeRefid;
					++$tries;
				}
				$debugString .= $queryString."\n";
				if($queryResult===FALSE)
				{
					$debugString .= pg_last_error();
					logError(pg_last_error());
					$ok = false;
				}
				if($tries ==$tryNumber)
					$status = "dberror";
				else
					--$childnodeRefid;

// 				$entries = $childnode->getElementsByTagName("ENTRY");
// 				$queryString = "";
// 				for($k = 0; $k < $entries->length; ++$k)
// 				{
// 					$entry = $entries->item($k);
// 					$guiref = $entry->getAttribute("ID");
// 					$queryString .= "INSERT INTO tbl_office_time_sheet_entry_details_identities (officetimesheetentrydetailsrefid,guiref) VALUES (".$childnodeRefid.",'".$guiref."');";
// 				}
// 				$debugString .= $queryString."\n";
// 				$queryResult = $dbconn->sendquery2($queryString);
			}
		}
		if($i == $structs->length)
			$status = "saved";
		if($ok)
		{
			$queryString = "COMMIT";
			$queryResult = $dbconn->sendquery2($queryString);
			$status = "saved";
		}
		else
		{
			$queryString = "ROLLBACK";
			$queryResult = $dbconn->sendquery2($queryString);
			$status = "dberror";
		}
		
		$response .= "<RESPONSE>\n";
		$response .= "\t<STATUS>".$status."</STATUS>\n";
		$response .= "\t<REFID>".$refid."</REFID>\n";
		if(strcmp($final,"true")==0)
			$response .= "\t<FINAL>true</FINAL>\n";
		$response .= "</RESPONSE>\n";
	}
 	//logWrite();
	header("Content-type: application/xml");
	echo $response;
	

?>
Return current item: Time Recording System