Returns an array of record hashes with the column names as keys and
column values as values.
/**
* Perform DB query to obtain list of airport codes.
*
* @return array An array of station information. Each entry contains:
* - icao: The ICAO identifier of the location.
* - name: The human readable name of the station.
* - country: The country the station is located in (if available).
*
* @throws Horde_Service_Weather_Exception
*/
protected function _getLocations()
{
if (empty($this->_db)) {
return array();
}
$sql = 'SELECT icao, name, state, municipality, country FROM ' . $this->_tableName . ' ORDER BY country';
try {
return $this->_db->selectAll($sql);
} catch (Horde_Exception $e) {
throw new Horde_Service_Weather_Exception($e);
}
}
/**
* Returns the owners of a ticket.
*
* @param mixed integer|array $ticketId One or more ticket IDs.
*
* @return array An hash of ticket ID => owner IDs
* @throws Whups_Exception
*/
public function getOwners($ticketId)
{
if (is_array($ticketId)) {
if (!count($ticketId)) {
return array();
}
try {
$owners = $this->_db->selectAll('SELECT ticket_id AS id, ticket_owner AS owner ' . 'FROM whups_ticket_owners WHERE ticket_id IN ' . '(' . str_repeat('?, ', count($ticketId) - 1) . '?)', $ticketId);
} catch (Horde_Db_Exception $e) {
throw new Whups_Exception($e);
}
} else {
try {
$owners = $this->_db->selectAll('SELECT ticket_id as id, ticket_owner as owner ' . 'FROM whups_ticket_owners WHERE ticket_id = ?', array((int) $ticketId));
} catch (Horde_Db_Exception $e) {
throw new Whups_Exception($e);
}
}
$results = array();
foreach ($owners as $owner) {
$results[$owner['id']][] = $owner['owner'];
}
return $results;
}
/**
* Returns all previously written sync anchors for a user.
*
* @param string $user A user name.
*
* @return array A hash tree with all devices, databases and sync anchors
* from the specified user.
*/
public function getUserAnchors($user)
{
$query = 'SELECT syncml_syncpartner, syncml_db, syncml_clientanchor, ' . 'syncml_serveranchor FROM horde_syncml_anchors ' . 'WHERE syncml_uid = ?';
$values = array($user);
return $this->_db->selectAll($query, $values);
}
/**
* Returns a list of stories from the storage backend filtered by
* arbitrary criteria.
* NOTE: $criteria['channel_id'] MUST be set for this method to work.
*
* @param array $criteria
*
* @return array
*
* @see Jonah_Driver#getStories
*/
protected function _getStories($criteria, $order = Jonah::ORDER_PUBLISHED)
{
$sql = 'SELECT stories.story_id AS id, ' . 'stories.channel_id, ' . 'stories.story_author AS author, ' . 'stories.story_title AS title, ' . 'stories.story_desc AS description, ' . 'stories.story_body_type AS body_type, ' . 'stories.story_body AS body, ' . 'stories.story_url AS url, ' . 'stories.story_permalink AS permalink, ' . 'stories.story_published AS published, ' . 'stories.story_updated AS updated, ' . 'stories.story_read AS readcount ' . 'FROM jonah_stories AS stories ' . 'WHERE stories.channel_id=?';
$values = array($criteria['channel_id']);
// Apply date filtering
if (isset($criteria['updated-min'])) {
$sql .= ' AND story_updated >= ?';
$values[] = $criteria['updated-min']->timestamp();
}
if (isset($criteria['updated-max'])) {
$sql .= ' AND story_updated <= ?';
$values[] = $criteria['updated-max']->timestamp();
}
if (isset($criteria['published-min'])) {
$sql .= ' AND story_published >= ?';
$values[] = $criteria['published-min']->timestamp();
}
if (isset($criteria['published-max'])) {
$sql .= ' AND story_published <= ?';
$values[] = $criteria['published-max']->timestamp();
}
if (isset($criteria['published'])) {
$sql .= ' AND story_published IS NOT NULL';
}
// Filter by story author
if (isset($criteria['author'])) {
$sql .= ' AND stories.story_author = ?';
$values[] = $criteria['author'];
}
// Filter stories by keyword
if (isset($criteria['keywords'])) {
foreach ($criteria['keywords'] as $keyword) {
$sql .= ' AND stories.story_body LIKE ?';
$values[] = '%' . $keyword . '%';
}
}
if (isset($criteria['notkeywords'])) {
foreach ($criteria['notkeywords'] as $keyword) {
$sql .= ' AND stories.story_body NOT LIKE ?';
$values[] = '%' . $keyword . '%';
}
}
// Ensure any results are in the following story_id list.
if (!empty($criteria['ids'])) {
$sql .= ' AND stories.story_id IN (' . implode(',', array_map(function ($v) {
return '?';
}, $criteria['ids'])) . ')';
$values = array_merge($values, $criteria['ids']);
}
switch ($order) {
case Jonah::ORDER_PUBLISHED:
$sql .= ' ORDER BY story_published DESC';
break;
case Jonah::ORDER_READ:
$sql .= ' ORDER BY story_read DESC';
break;
case Jonah::ORDER_COMMENTS:
//@TODO
break;
}
$limit = 0;
if (isset($criteria['limit'])) {
$limit = $criteria['limit'];
}
if (isset($criteria['startnumber']) && isset($criteria['endnumber'])) {
$limit = min($criteria['endnumber'] - $criteria['startnumber'], $criteria['limit']);
}
$start = isset($criteria['startnumber']) ? $criteria['startnumber'] : 0;
Horde::log('SQL Query by Jonah_Driver_sql::_getStories(): ' . $sql, 'DEBUG');
if ($limit || $start != 0) {
$sql = $this->_db->addLimitOffset($sql, array('limit' => $limit, 'offset' => $start));
}
try {
$results = $this->_db->selectAll($sql, $values);
} catch (Horde_Db_Exception $e) {
throw new Jonah_Exception($e);
}
$channel = $this->_getChannel($criteria['channel_id']);
foreach ($results as &$row) {
$row['link'] = (string) $this->getStoryLink($channel, $row);
$row['tags'] = $GLOBALS['injector']->getInstance('Jonah_Tagger')->getTags($row['id'], Jonah_Tagger::TYPE_STORY);
}
return $results;
}
