/**
* Returns the relevant tags for the current user
*
* @return array
*/
function get_relevant_tags($oUser, $sTag)
{
$aTagList = isset($_SESSION['tagList']) ? $_SESSION['tagList'] : array();
$tagTree = array();
$documentList = '';
// Get the previous tag info:
// the list of documents that contain the tag to search within
// the tags that have already been filtered so they aren't displayed again
if (!empty($aTagList)) {
$aPrevTag = end($aTagList);
$sPrevTag = $aPrevTag['tag'];
$documentList = $aPrevTag['docs'];
$tagTree = $aPrevTag['tagTree'];
}
if (empty($sTag)) {
// If there is no tag specified then get all tags.
$aUserPermissions = KTSearchUtil::permissionToSQL($oUser, 'ktcore.permissions.read');
if (PEAR::isError($aUserPermissions)) {
return array();
}
// ensure the user has read permission on the documents
list($sWhere, $aParams, $sJoins) = $aUserPermissions;
$sql = "SELECT TW.tag, count(*) as freq\n \t\tFROM document_tags DT\n \t\tINNER JOIN tag_words TW ON DT.tag_id=TW.id\n \t\tWHERE DT.document_id in (SELECT D.id FROM documents D {$sJoins} WHERE {$sWhere} AND D.status_id = '1')\n \t\tGROUP BY TW.tag";
$tags = DBUtil::getResultArray(array($sql, $aParams));
} else {
// Create a new tag query to get the document id's associated with the tag
$oQuery = new TagQuery($oUser, $sTag);
$aOptions = array();
$aOptions['select'] = 'DISTINCT DTS.document_id';
$aQuery = $oQuery->getQuery($aOptions);
$sInnerQuery = $aQuery[0];
$aParams = $aQuery[1];
$aDocIds = DBUtil::getResultArrayKey($aQuery, 'document_id');
$sDocs = implode(',', $aDocIds);
// Make sure user not opening a new window on tag cloud filters
if (!$sDocs) {
return array();
}
// Don't display tags that have already been selected.
$tagTree[] = $sTag;
$cnt = count($tagTree);
$sIgnoreTags = '?';
for ($i = 1; $i < $cnt; $i++) {
$sIgnoreTags .= ',?';
}
// Get the tags within the documents that haven't been selected before
$sQuery = "SELECT TW.tag, count(*) as freq\n FROM document_tags DT INNER JOIN tag_words TW ON DT.tag_id=TW.id\n WHERE DT.document_id in ({$sDocs}) AND TW.tag NOT IN ({$sIgnoreTags})\n GROUP BY TW.tag";
$tags = DBUtil::getResultArray(array($sQuery, $tagTree));
if (PEAR::isError($tags)) {
echo $tags->getMessage();
}
// Add new tag to the session
if ($sPrevTag != $sTag) {
$aTagList[] = array('tag' => $sTag, 'docs' => $sDocs, 'tagTree' => $tagTree);
$_SESSION['tagList'] = $aTagList;
}
}
$aTags = array();
if ($tags) {
foreach ($tags as $tag) {
$word = $tag['tag'];
$freq = $tag['freq'];
$aTags[$word] = $freq;
}
}
return $aTags;
}
function getTransactionsMatchingQuery($oUser, $sJoinClause, $aExternalWhereClauses, $aExternalWhereParams, $aOptions = null)
{
$sSelectItems = 'DTT.name AS transaction_name, U.name AS user_name, DT.version AS version, DT.comment AS comment, DT.datetime AS datetime, D.id as document_id, DT.transaction_namespace as namespace';
$sBaseJoin = "FROM " . KTUtil::getTableName("document_transactions") . " AS DT " . "INNER JOIN " . KTUtil::getTableName("users") . " AS U ON DT.user_id = U.id " . "INNER JOIN " . KTUtil::getTableName("transaction_types") . " AS DTT ON DTT.namespace = DT.transaction_namespace " . "INNER JOIN " . KTUtil::getTableName("documents") . " AS D ON D.id = DT.document_id ";
// now we're almost at partialquery like status.
$perm_res = KTSearchUtil::permissionToSQL($oUser, 'ktcore.permissions.read');
if (PEAR::isError($perm_res)) {
return $perm_res;
}
list($sPermissionString, $aPermissionParams, $sPermissionJoin) = $perm_res;
// compile the final list
$aFinalWhere = kt_array_merge(array($sPermissionString, 'D.creator_id IS NOT NULL'), $aExternalWhereClauses, array('D.status_id = ?'));
$aFinalWhereParams = kt_array_merge($aPermissionParams, $aExternalWhereParams, array(LIVE));
if (!is_array($aOptions)) {
$aOptions = (array) $aOptions;
}
$sOrderBy = KTUtil::arrayGet($aOptions, 'orderby', 'DT.datetime DESC');
// compile these.
// NBM: do we need to wrap these in ()?
$sWhereClause = implode(' AND ', $aFinalWhere);
if (!empty($sWhereClause)) {
$sWhereClause = 'WHERE ' . $sWhereClause;
}
$sQuery = sprintf("SELECT %s %s %s %s %s ORDER BY %s", $sSelectItems, $sBaseJoin, $sPermissionJoin, $sJoinClause, $sWhereClause, $sOrderBy);
//var_dump(array($sQuery, $aFinalWhereParams));
$res = DBUtil::getResultArray(array($sQuery, $aFinalWhereParams));
//var_dump($res); exit(0);
return $res;
}
/**
* Converts a criteria set into a SQL query that (by default)
* returns the ids of documents that fulfil the criteria.
*
* $aOptions is a dictionary that can contain:
* - select - a string that contains the list of columns
* selected in the query
* - join - a string that contains join conditions to satisfy
* the select string passed or limit the documents included
*
* A list with the following elements is returned:
* - String containing the parameterised SQL query
* - Array containing the parameters for the SQL query
*/
function criteriaToQuery($aCriteriaSet, $oUser, $sPermissionName, $aOptions = null)
{
global $default;
$sSelect = KTUtil::arrayGet($aOptions, 'select', 'D.id AS document_id');
$sInitialJoin = KTUtil::arrayGet($aOptions, 'join', '');
if (is_array($sInitialJoin)) {
$aInitialJoinParams = $sInitialJoin[1];
$sInitialJoin = $sInitialJoin[0];
}
$res = KTSearchUtil::criteriaSetToSQL($aCriteriaSet);
if (PEAR::isError($res)) {
return $res;
}
list($sSQLSearchString, $aCritParams, $sCritJoinSQL) = $res;
$sToSearch = KTUtil::arrayGet($aOrigReq, 'fToSearch', 'Live');
// actually never present in this version.
$res = KTSearchUtil::permissionToSQL($oUser, $sPermissionName);
if (PEAR::isError($res)) {
// only occurs if the group has no permissions.
return $res;
} else {
list($sPermissionString, $aPermissionParams, $sPermissionJoin) = $res;
}
/*
* This is to overcome the problem where $sPermissionString (or
* even $sSQLSearchString) is empty, leading to leading or
* trailing ANDs.
*/
$aPotentialWhere = array($sPermissionString, 'SL.name = ?', "({$sSQLSearchString})");
$aWhere = array();
foreach ($aPotentialWhere as $sWhere) {
if (empty($sWhere)) {
continue;
}
if ($sWhere == "()") {
continue;
}
$aWhere[] = $sWhere;
}
$sWhere = "";
if ($aWhere) {
$sWhere = "\tWHERE " . join(" AND ", $aWhere);
}
//$sQuery = DBUtil::compactQuery("
$sQuery = sprintf("\n SELECT\n %s\n FROM\n %s AS D\n LEFT JOIN %s AS DM ON D.metadata_version_id = DM.id\n LEFT JOIN %s AS DC ON DM.content_version_id = DC.id\n INNER JOIN {$default->status_table} AS SL on D.status_id=SL.id\n %s\n %s\n %s\n %s", $sSelect, KTUtil::getTableName('documents'), KTUtil::getTableName('document_metadata_version'), KTUtil::getTableName('document_content_version'), $sInitialJoin, $sCritJoinSQL, $sPermissionJoin, $sWhere);
// GROUP BY D.id
$aParams = array();
$aParams = kt_array_merge($aParams, $aInitialJoinParams);
$aParams = kt_array_merge($aParams, $aPermissionParams);
$aParams[] = $sToSearch;
$aParams = kt_array_merge($aParams, $aCritParams);
return array($sQuery, $aParams);
}
function _getDocumentQuery($aOptions = null)
{
$oUser = User::get($_SESSION['userID']);
$res = KTSearchUtil::permissionToSQL($oUser, $this->sPermissionName);
if (PEAR::isError($res)) {
return $res;
}
list($sPermissionString, $aPermissionParams, $sPermissionJoin) = $res;
$aPotentialWhere = array($sPermissionString, 'D.folder_id = ?', 'D.status_id = ' . ARCHIVED);
$aWhere = array();
foreach ($aPotentialWhere as $sWhere) {
if (empty($sWhere)) {
continue;
}
if ($sWhere == '()') {
continue;
}
$aWhere[] = $sWhere;
}
$sWhere = '';
if ($aWhere) {
$sWhere = "\tWHERE " . join(' AND ', $aWhere);
}
$sSelect = KTUtil::arrayGet($aOptions, 'select', 'D.id');
$sQuery = sprintf('SELECT %s FROM %s AS D
LEFT JOIN %s AS DM ON D.metadata_version_id = DM.id
LEFT JOIN %s AS DC ON DM.content_version_id = DC.id
%s %s', $sSelect, KTUtil::getTableName('documents'), KTUtil::getTableName('document_metadata_version'), KTUtil::getTableName('document_content_version'), $sPermissionJoin, $sWhere);
$aParams = array();
$aParams = kt_array_merge($aParams, $aPermissionParams);
$aParams[] = $this->folder_id;
return array($sQuery, $aParams);
}
function buildQuery($aFolderIds)
{
$sFolderList = implode(', ', $aFolderIds);
// First we get any document shortcuts
$query = "SELECT linked_document_id FROM documents\n WHERE linked_document_id IS NOT NULL\n AND folder_id IN ({$sFolderList})";
$aLinkedDocIds = DBUtil::getResultArrayKey($query, 'linked_document_id');
if (PEAR::isError($aLinkedDocIds) || empty($aLinkedDocIds)) {
$sDocList = '';
} else {
$sDocList = implode(', ', $aLinkedDocIds);
}
// Get the permissions sql
$oUser = User::get($_SESSION['userID']);
$res = KTSearchUtil::permissionToSQL($oUser, $this->sPermissionName);
if (PEAR::isError($res)) {
return $res;
}
list($sPermissionString, $aPermissionParams, $sPermissionJoin) = $res;
// Create the "where" criteria
$sWhere = "WHERE {$sPermissionString} AND (D.folder_id IN ({$sFolderList})";
$sWhere .= !empty($sDocList) ? " OR D.id IN ({$sDocList}))" : ')';
$sWhere .= ' AND D.status_id = 1 AND linked_document_id IS NULL';
// Create the query
$sQuery = "SELECT DISTINCT(D.id) FROM documents AS D\n LEFT JOIN document_metadata_version AS DM ON D.metadata_version_id = DM.id\n LEFT JOIN document_content_version AS DC ON DM.content_version_id = DC.id\n {$sPermissionJoin} {$sWhere}";
return array($sQuery, $aPermissionParams);
}
/**
* Returns the relevant tags for the current user
*
* @return array
*/
function get_relevant_tags()
{
$aUserPermissions = KTSearchUtil::permissionToSQL($this->oUser, null);
if (PEAR::isError($aUserPermissions)) {
return false;
}
list($where, $params, $joins) = $aUserPermissions;
$sql = "\n \t\tSELECT\n \t\t\tTW.tag, count(*) as freq\n \t\tFROM\n \t\t\tdocument_tags DT INNER JOIN tag_words TW ON DT.tag_id=TW.id\n \t\tWHERE DT.document_id in (SELECT D.id FROM documents D {$joins} WHERE {$where} AND D.status_id = '1') GROUP BY TW.tag";
$tags = DBUtil::getResultArray(array($sql, $params));
$aTags = array();
foreach ($tags as $tag) {
$word = $tag['tag'];
$freq = $tag['freq'];
$aTags[$word] = $freq;
}
return $aTags;
}