/** * 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; }