예제 #1
0
/**
* 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;
 }
예제 #3
0
 /**
  * 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);
 }
예제 #4
0
 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);
 }
예제 #5
0
 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);
 }
예제 #6
0
 /**
  * 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;
 }