/**
* Return the current value of the modseq. We take the MAX of the
* horde_histories table instead of the value of the horde_histories_modseq
* table to ensure we never miss an entry if we query the history system
* between the time we call nextModSeq() and the time the new entry is
* written.
*
* @param string $parent Restrict to entries a specific parent.
*
* @return integer|boolean The highest used modseq value, false if no history.
*/
public function getHighestModSeq($parent = null)
{
$sql = 'SELECT history_modseq FROM horde_histories';
if (!empty($parent)) {
$sql .= ' WHERE object_uid LIKE ' . $this->_db->quote($parent . ':%');
}
$sql .= ' ORDER BY history_modseq DESC';
$sql = $this->_db->addLimitOffset($sql, array('limit' => 1));
try {
$modseq = $this->_db->selectValue($sql);
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
if (is_null($modseq) || $modseq === false) {
try {
$modseq = $this->_db->selectValue('SELECT MAX(history_modseq) FROM horde_histories_modseq');
} catch (Horde_Db_Exception $e) {
throw new Horde_History_Exception($e);
}
if (!empty($modseq)) {
return $modseq;
} else {
return false;
}
}
return $modseq;
}
/**
*/
public function favouriteRecipients($limit, $filter = null)
{
/* Build the SQL query. */
$where = '';
if (!empty($filter)) {
$filter = array_map(array($this->_db, 'quote'), $filter);
$where = sprintf(' AND sentmail_action in (%s)', implode(', ', $filter));
}
$query = sprintf('SELECT sentmail_recipient, count(*) AS sentmail_count FROM %s WHERE sentmail_who = %s AND sentmail_success = 1%s GROUP BY sentmail_recipient ORDER BY sentmail_count DESC', $this->_params['table'], $this->_db->quote($GLOBALS['registry']->getAuth()), $where);
/* Execute the query. */
try {
$query = $this->_db->addLimitOffset($query, array('limit' => $limit));
return $this->_db->selectValues($query);
} catch (Horde_Db_Exception $e) {
return array();
}
}
/**
* Return users related to a given user along with a similarity rank.
*/
public function getSimilarUsers($args)
{
$args['userId'] = current($this->_userManager->ensureUsers($args['userId']));
$radius = isset($args['radius']) ? (int) $args['radius'] : $this->_defaultRadius;
$sql = 'SELECT others.user_id, (others.count - self.count) AS rank FROM ' . $this->_t('user_tag_stats') . ' others INNER JOIN (SELECT tag_id, count FROM ' . $this->_t('user_tag_stats') . ' WHERE user_id = ' . (int) $args['userId'] . ' AND count >= ' . $radius . ') self ON others.tag_id = self.tag_id ORDER BY rank DESC';
if (isset($args['limit'])) {
$sql = $this->_db->addLimitOffset($sql, array('limit' => $args['limit']));
}
return $this->_db->selectAssoc($sql);
}
/**
* Lists a slice of the image ids in the given gallery.
*
* @param array $params Filter parameters.
*<pre>
* integer|array 'gallery_id' - A gallery id to list images from
* integer 'offset' - The image to start listing from
* integer 'limit' - How many images to return
* array|string 'fields' - The fields to return
* string 'sort' - The field to sort by.
* array 'filter' - Additional filters. Each element is an
* array containing 'property', 'op', and
* 'value' keys. Passing 'IN' as the 'op'
* and an array as 'value' will produce a
* SQL IN conditional.
*</pre>
*
* @return array An array of images. Either an array of ids, or an array
* of field values, keyed by id.
* @throws Ansel_Exception, InvalidArgumentException
*/
public function listImages(array $params = array())
{
$params = new Horde_Support_Array($params);
if (is_array($params['fields'])) {
$field_count = count($params['fields']);
$params['fields'] = implode(', ', $params['fields']);
} elseif ($params['fields'] == '*') {
// The count is not important, as long as it's > 1
$field_count = 2;
} else {
$field_count = substr_count($params->get('fields', 'image_id'), ',') + 1;
}
if (is_array($params['sort'])) {
$params['sort'] = implode(', ', $params['sort']);
}
if (is_array($params['gallery_id'])) {
$query_where = 'WHERE gallery_id IN (' . implode(',', $params['gallery_id']) . ')';
} elseif ($params['gallery_id']) {
$query_where = 'WHERE gallery_id = ' . $params['gallery_id'];
} else {
$query_where = '';
}
if ($params['filter']) {
foreach ($params['filter'] as $filter) {
$query_where .= (!empty($query_where) ? ' AND ' : ' WHERE ') . $this->_toImageDriverName($filter['property']) . ' ' . $filter['op'] . ' ' . (is_array($filter['value']) ? '(' . implode(',', $filter['value']) . ')' : $filter['value']);
}
}
$sql = 'SELECT ' . $params->get('fields', 'image_id') . ' FROM ansel_images ' . $query_where . ' ORDER BY ' . $params->get('sort', 'image_sort');
$sql = $this->_db->addLimitOffset($sql, array('limit' => $params->get('limit', 0), 'offset' => $params->get('offset', 0)));
try {
if ($field_count > 1) {
$results = $this->_db->selectAll($sql);
$images = array();
foreach ($results as $image) {
$images[$image['image_id']] = $image;
}
return $images;
} else {
return $this->_db->selectValues($sql);
}
} catch (Horde_Db_Exception $e) {
throw new Ansel_Exception($e);
}
}
/**
* Return a list of users who have shares with the given permissions
* for the current user.
*
* @param integer $perm The level of permissions required.
* @param mixed $parent The parent share to start looking in.
* (Horde_Share_Object, share_id, or null)
* @param boolean $allLevels Return all levels, or just the direct
* children of $parent? Defaults to all levels.
* @param integer $from The user to start listing at.
* @param integer $count The number of users to return.
*
* @return array List of users.
* @throws Horde_Share_Exception
*/
public function listOwners($perm = Horde_Perms::SHOW, $parent = null, $allLevels = true, $from = 0, $count = 0)
{
$sql = 'SELECT DISTINCT(s.share_owner) ' . $this->getShareCriteria($this->_user, $perm, null, $parent, $allLevels);
if ($count) {
$sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
}
try {
$allowners = $this->_db->selectValues($sql);
} catch (Horde_Db_Exception $e) {
throw new Horde_Share_Exception($e);
}
$owners = array();
foreach ($allowners as $owner) {
if ($this->countShares($this->_user, $perm, $owner, $parent, $allLevels)) {
$owners[] = $owner;
}
}
return $owners;
}
/**
* Retrieves a set of pages matching an SQL WHERE clause.
*
* @param string $table Table to retrieve pages from.
* @param array|string $where Where clause for sql statement (without the
* 'WHERE'). If an array the 1st element is the
* clause with placeholder, the 2nd element the
* values.
* @param string $orderBy Order results by this column.
* @param integer $limit Maximum number of pages to fetch.
*
* @return array A list of page hashes.
* @throws Wicked_Exception
*/
protected function _retrieve($table, $where, $orderBy = null, $limit = null)
{
$query = 'SELECT * FROM ' . $table;
$values = array();
if (!empty($where)) {
$query .= ' WHERE ';
if (is_array($where)) {
$query .= $where[0];
$values = $where[1];
} else {
$query .= $where;
}
}
if (!empty($orderBy)) {
$query .= ' ORDER BY ' . $orderBy;
}
if (!empty($limit)) {
try {
$query = $this->_db->addLimitOffset($query, array('limit' => $limit));
} catch (Horde_Db_Exception $e) {
throw new Wicked_Exception($e);
}
}
try {
$result = $this->_db->select($query, $values);
} catch (Horde_Db_Exception $e) {
throw new Wicked_Exception($e);
}
$pages = array();
foreach ($result as $row) {
if (isset($row['page_name'])) {
$row['page_name'] = $this->_convertFromDriver($row['page_name']);
}
if (isset($row['page_text'])) {
$row['page_text'] = $this->_convertFromDriver($row['page_text']);
}
if (isset($row['change_log'])) {
$row['change_log'] = $this->_convertFromDriver($row['change_log']);
}
$pages[] = $row;
}
return $pages;
}
/**
* Searches forums for matching threads or posts.
*
* @param array $filter Hash of filter criteria:
* 'forums' => Array of forum IDs to search. If not
* present, searches all forums.
* 'keywords' => Array of keywords to search for. If not
* present, finds all posts/threads.
* 'allkeywords' => Boolean specifying whether to find all
* keywords; otherwise, wants any keyword.
* False if not supplied.
* 'message_author' => Name of author to find posts by. If not
* present, any author.
* 'searchsubjects' => Boolean specifying whether to search
* subjects. True if not supplied.
* 'searchcontents' => Boolean specifying whether to search
* post contents. False if not supplied.
* @param string $sort_by The column by which to sort.
* @param integer $sort_dir The direction by which to sort:
* 0 - ascending
* 1 - descending
* @param string $from The thread to start listing at.
* @param string $count The number of threads to return.
*
* @return array A search result hash where:
* 'results' => Array of messages.
* 'total => Total message number.
* @throws Agora_Exception
*/
public function search($filter, $sort_by = 'message_subject', $sort_dir = 0, $from = 0, $count = 0)
{
if (!isset($filter['allkeywords'])) {
$filter['allkeywords'] = false;
}
if (!isset($filter['searchsubjects'])) {
$filter['searchsubjects'] = true;
}
if (!isset($filter['searchcontents'])) {
$filter['searchcontents'] = false;
}
/* Select forums ids to search in */
$sql = 'SELECT forum_id, forum_name FROM ' . $this->_forums_table . ' WHERE ';
if (empty($filter['forums'])) {
$sql .= ' active = ? AND scope = ?';
$values = array(1, $this->_scope);
} else {
$sql .= ' forum_id IN (' . implode(',', $filter['forums']) . ')';
$values = array();
}
try {
$forums = $this->_db->selectAssoc($sql, $values);
} catch (Horde_Db_Exception $e) {
throw new Agora_Exception($e->getMessage());
}
/* Build query */
$sql = ' FROM ' . $this->_threads_table . ' WHERE forum_id IN (' . implode(',', array_keys($forums)) . ')';
if (!empty($filter['keywords'])) {
$sql .= ' AND (';
if ($filter['searchsubjects']) {
$keywords = '';
foreach ($filter['keywords'] as $keyword) {
if (!empty($keywords)) {
$keywords .= $filter['allkeywords'] ? ' AND ' : ' OR ';
}
$keywords .= 'message_subject LIKE ' . $this->_db->quote('%' . $keyword . '%');
}
$sql .= '(' . $keywords . ')';
}
if ($filter['searchcontents']) {
if ($filter['searchsubjects']) {
$sql .= ' OR ';
}
$keywords = '';
foreach ($filter['keywords'] as $keyword) {
if (!empty($keywords)) {
$keywords .= $filter['allkeywords'] ? ' AND ' : ' OR ';
}
$keywords .= 'body LIKE ' . $this->_db->quote('%' . $keyword . '%');
}
$sql .= '(' . $keywords . ')';
}
$sql .= ')';
}
if (!empty($filter['author'])) {
$sql .= ' AND message_author = ' . $this->_db->quote(Horde_String::lower($filter['author']));
}
/* Sort by result column. */
$sql .= ' ORDER BY ' . $sort_by . ' ' . ($sort_dir ? 'DESC' : 'ASC');
/* Slice directly in DB. */
if ($count) {
$total = $this->_db->selectValue('SELECT COUNT(*) ' . $sql);
$sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
}
$sql = 'SELECT message_id, forum_id, message_subject, message_author, message_timestamp ' . $sql;
try {
$messages = $this->_db->select($sql);
} catch (Horde_Db_Exception $e) {
throw new Agora_Exception($e->getMessage());
}
if (empty($messages)) {
return array('results' => array(), 'total' => 0);
}
$results = array();
$msg_url = Horde::url('messages/index.php');
$forum_url = Horde::url('threads.php');
while ($message = $messages->fetch()) {
if (!isset($results[$message['forum_id']])) {
$index = array('agora' => $message['forum_id'], 'scope' => $this->_scope);
$results[$message['forum_id']] = array('forum_id' => $message['forum_id'], 'forum_url' => $forum_url->add($index), 'forum_name' => $this->convertFromDriver($forums[$message['forum_id']]), 'messages' => array());
}
$index = array('agora' => $message['forum_id'] . '.' . $message['message_id'], 'scope' => $this->_scope);
$results[$message['forum_id']]['messages'][] = array('message_id' => $message['message_id'], 'message_subject' => htmlspecialchars($this->convertFromDriver($message['message_subject'])), 'message_author' => $message['message_author'], 'message_date' => $this->dateFormat($message['message_timestamp']), 'message_url' => $msg_url->add($index));
}
return array('results' => $results, 'total' => $total);
}
/**
* Appends +LIMIT+ and +OFFSET+ options to a SQL statement.
*
* @param string $sql SQL statement.
* @param array $options TODO
*
* @return string
*/
public function addLimitOffset($sql, $options)
{
$result = $this->_read->addLimitOffset($sql, $options);
$this->_lastQuery = $this->_write->getLastQuery();
return $result;
}
