[nobody]

Hi, I found out I spent too much time writing SQL queries to find a document in the database, get its filepath, url, converting the matches to grid and so on, so I started creating a custom function to do that.

I created a plugin with my custom function, and would be very happy if you use it, test it and/or improve it.

The function queries the APP_DOCUMENT table and filter results based on parametres given
It returns a grid-like array, which is something like array(row)=>array('column1'=>columnValue', 'column2' =>columnValue...) with the following keys (column IDs):
filename, which is the name of the file with extension. In case of OUTPUT documents, it always returns PDF
category, which means the document tags. In case tags are used in the filter, it will return only the relevant tag
uploadDate
version,
filePath, which is the full local path to the file
url, which is the URL to the file
status, meaning if the document is active, deleted...

FUNCTION PARAMETRES
$caseId is the ID of the case. If not set, current case Id will be given. If set to "ALL", results are not filtered. If a specific case ID is given, the ID is used in a query.
$filterByDocId filters results by document Id
$filterByDocType filters input documents if "INPUT" is given. If "OUTPUT" is given, it filters output documents. If not set, results are not filtered
$filterByUser filters documents by user if uid is given. If not set, results are not filtered.
$filterByTags filters documents by tags using strpos. If not set, results are not filtered.
$filterRecentOnly shows only the most recent version of the document if the parameter is set. If not set, results are not filtered

NOTE: Even though the function allows to query documents for ALL cases, this may take very long based on the number of documents in the APP_DOCUMENT table. Therefore, make sure you use other parametres (e.g. tags) in the function to filter the table.

Code: Select all

function customFunctions_getAllCaseDocuments(
$caseId,
$filterByDocId=NULL,
$filterByDocType=NULL,
$filterByStatus=NULL,
$filterByUser=NULL, 
$filterByTags=NULL,
$filterRecentOnly=NULL

) {

$baseQuery = "SELECT A.APP_DOC_UID as documentId, A.APP_DOC_TAGS as tags, A.APP_UID as caseId, A.APP_DOC_FILENAME as filename, A.APP_DOC_TYPE as type, A.DOC_VERSION as version, A.USR_UID as userId, A.APP_DOC_CREATE_DATE as uploadDate, A.APP_DOC_STATUS as status FROM APP_DOCUMENT A WHERE";
$query = "";

if (!isset($caseId)){
$caseId = $_SESSION["APPLICATION"] ;
$query .= " A.APP_UID = '$caseId'";
} elseif($caseId === "ALL"){
$query .= " A.APP_UID LIKE '%'";
} else {
$query .= " A.APP_UID = '$caseId'";
}

if(isset($filterByDocId)){
$query .= " AND A.DOC_UID = '$filterByDocId'";
}
if(isset($filterByDocType)){
$query .= " AND A.APP_DOC_TYPE = '$filterByDocType'";
}

if(isset($filterByStatus)){
$query .= " AND A.APP_DOC_STATUS = '$filterByStatus'";
}

if(isset($filterByUser)){
$query .= " AND A.USR_UID = '$filterByUser'";
}

if(isset($filterByTags)){
$query .= " AND A.APP_DOC_TAGS LIKE '%$filterByTags%' ";
}

if(isset($filterRecentOnly)){
$baseQuery = "SELECT A.APP_DOC_UID as documentId, A.APP_DOC_TAGS as tags, A.APP_UID as caseId, A.APP_DOC_FILENAME as filename, A.APP_DOC_TYPE as type, A.DOC_VERSION as version, A.USR_UID as userId, A.APP_DOC_CREATE_DATE as uploadDate, A.APP_DOC_STATUS as status FROM APP_DOCUMENT A LEFT OUTER JOIN APP_DOCUMENT B ON A.APP_DOC_UID  = B.APP_DOC_UID AND A.DOC_VERSION < B.DOC_VERSION WHERE";
$query .= " AND B.APP_DOC_UID IS NULL";
}
$fullQuery = $baseQuery . $query;
//die(print_r($fullQuery));
$result = executeQuery($fullQuery);
//die(print_r($result));
if (!(is_array($result)) || count($result) == 0) {
 $g = new G();
 $g->SendTemporalMessageText("No such document exists!", "WARNING");
 die();
 }
$myArray = array(); 
 foreach($result as $iRow=>$iColumns){
 $filename = $iColumns['filename']; 
 $aUser = PMFInformationUser($iColumns['userId']);
 $userFullName = $aUser['firstname']." ".$aUser['lastname'];
 $status = $iColumns['status']; 
 $uploadDate = formatDate($iColumns['uploadDate'],'dd/mm/yyyy');
 $g = new G();
 $server = ($g->is_https() ? 'https://':'http://') . $_SERVER['SERVER_NAME'].':'.$_SERVER['SERVER_PORT'];
 $server .= '/sys' . @@SYS_SYS .'/'. @@SYS_LANG .'/'. @@SYS_SKIN;
 //die(print_r($server));
$g = new G();

 $version = $iColumns['version']; 
 
/* 
*now we try to get the category of the document from the document tags
* if tags have not been included in the search, the function will return all tags for the document
* if tags were included in the search, the first found corresponding tag will be returned and used as the document category
*/

if(isset($filterByTags))  {
$tagsArray = explode(",",$iColumns['tags']);
	foreach($tagsArray as $key =>$value){

		if (strpos($value,$filterByTags) !== false) {
		$category = ucfirst(strtolower($value));
		break;
}}} else {
		$category = $iColumns['tags'];
}
		
 if($iColumns['type'] ==="OUTPUT"){
 $url_prefix = '../cases/cases_ShowOutputDocument?a=';
 $ext = "pdf";
  $filename .= ".".$ext;
 $url = $url_prefix . $iColumns['documentId'] . "&ext=" . $ext . "&v=" . $version; 
 
 $filePath = PATH_DOCUMENT . $g->getPathFromUID($iColumns['caseId']).PATH_SEP . 'outdocs'.PATH_SEP.$iColumns['documentId']."_".$version.".".$ext;
 
  } else {
$url_prefix = '../cases/cases_ShowDocument?a=';
$ext =  pathinfo($filename, PATHINFO_EXTENSION);
$url =  $url_prefix . $iColumns['documentId'] . "&v=" . $version; 
 //die(print_r($url));
 $filePath = PATH_DOCUMENT . $g->getPathFromUID($iColumns['caseId']).PATH_SEP .$iColumns['documentId']."_".$version.".".$ext;
}
  //your grid needs to have columns with following IDs: filename, category, uploadedBy, uploadDate, version, filePath, url and status
 $myArray[$iRow] = array(
 "filename" => $filename,
 "category" => $category,
 "uploadedBy" =>$userFullName,
 "uploadDate" =>  $uploadDate,
 "version" =>$version,
 "filePath" =>$filePath,
 "url" => $url,
 "status" => $status
 );
 }
//die(print_r($myArray));
return $myArray;
}

EXAMPLE

If you want to send e-mail with an attachment, you may use this function like this:

Code: Select all

$documentsArray = customFunctions_getAllCaseDocuments($caseId,NULL,NULL,NULL,NULL,NULL,1); //this will return the latest version of all input and output documents for the current case)
	$annexes=$array();
		foreach(documentsArray as $row=>$column){
			$annexes[$column['filename']] = $column['filePath'];
		}
	unset($column);
	PMFSendMessage(@@APPLICATION, $from,$to,'','','Test annexes','testTemplate.html',array(),$annexes);

[amosbatto]

Nobody, That's a really useful function. Nice work.
Would you mind posting this on the pmusers wiki?
https://www.pmusers.com/index.php/Main_Page

(We haven't launched it yet, but you can sign up for an account and post your plugin there. You can't edit the principal page (because the spammers kept attacking it), but I created a page for you if you want to use it:
https://www.pmusers.com/index.php?title ... eDocuments

[nobody]

Thank you Amos, I uploaded the plugin on the pmusers page.

[amosbatto]

Cool. I reformatted the page on pmusers.com so it will match the documentation on the other pages.

Thanks again,
Amos