<?
/*
################################################################
# >>> 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;
?>