public function findTagsBy($params = array())
{
if (isset($params['user'])) {
$user = $params['user'];
}
$db = $this->getDb();
$tagTable = $db->getTable('Tag');
if ($user) {
//roles that get private tags don't leave a row in records_tags, so separate the queries
$privateRoles = unserialize(get_option('user-tags-private-roles'));
if (is_null($privateRoles)) {
$privateRoles = array();
}
if (in_array($user->role, $privateRoles)) {
$select = new Omeka_Db_Select();
$db = $this->getDb();
$select->from(array('tags' => $db->Tag), array('tags.*', 'tagCount' => 'COUNT(tags.id)'))->joinInner(array('user_records_tags' => $db->UserRecordsTags), 'user_records_tags.tag_id = tags.id', array())->group('tags.id')->where('user_records_tags.owner_id = ?', $user->id);
} else {
$select = $tagTable->getSelectForFindBy($params);
$select->joinInner(array('user_records_tags' => $db->UserRecordsTags), 'user_records_tags.tag_id = tags.id', array());
$select->where('user_records_tags.owner_id = ?', $user->id);
}
if (isset($params['record'])) {
$record = $params['record'];
$select->where('user_records_tags.record_type = ?', get_class($record));
$select->where('user_records_tags.record_id = ?', $record->id);
}
return $tagTable->fetchObjects($select);
}
//get just the tags that are associated with any user
$select = new Omeka_Db_Select();
$db = $this->getDb();
$select->from(array('tags' => $db->Tag), array('tags.*', 'tagCount' => 'COUNT(tags.id)'))->joinInner(array('user_records_tags' => $db->UserRecordsTags), 'user_records_tags.tag_id = tags.id', array())->group('tags.id');
return $tagTable->fetchObjects($select);
}
public function getSelectForExternalIds()
{
$select = new Omeka_Db_Select($this->getDb()->getAdapter());
$alias = $this->getTableAlias();
$select->from(array($alias => $this->getTableName()), "{$alias}.external_id");
return $select;
}
/**
* Add `hasSlug` parameter to records API.
*
* @param Omeka_Db_Select $select The original select.
* @param array $args Includes `params`, the API query parameters.
* @return Omeka_Db_Select The modified select.
*/
public function filterNeatlineQueryRecords($select, $args)
{
// Filter out records without slugs.
if (isset($args['params']['hasSlug'])) {
$select->where('slug IS NOT NULL');
}
return $select;
}
public function findPairsForSelectForm(array $options = array())
{
$db = $this->getDb();
$subquery = new Omeka_Db_Select();
$subquery->from(array('element_texts' => $db->ElementText), 'id');
$subquery->joinInner(array('elements' => $db->Element), 'elements.id = element_texts.element_id', array());
$subquery->joinInner(array('element_sets' => $db->ElementSet), 'element_sets.id = elements.element_set_id', array());
$subquery->where("element_sets.name = 'Dublin Core'");
$subquery->where("elements.name = 'Title'");
$subquery->where("element_texts.record_type = 'Collection'");
$subquery->where('element_texts.record_id = collections.id');
$subquery->limit(1);
$select = $this->getSelectForFindBy($options);
$select->joinLeft(array('element_texts' => $db->ElementText), "element_texts.id = ({$subquery})", array());
$select->reset(Zend_Db_Select::COLUMNS);
$select->from(array(), array('collections.id', 'element_texts.text'));
$select->order('element_texts.text');
$pairs = $db->fetchPairs($select);
foreach ($pairs as $collectionId => &$name) {
if ($name === null || $name == '') {
$name = __('[Untitled] #%s', $collectionId);
} else {
$name = strip_formatting($name);
}
}
return $pairs;
}
/**
* Apply the permissions to an SQL select object.
*
* @param Omeka_Db_Select $select
* @param string $alias Table alias to query against
* @param string $ownerColumn Optional column for checking for ownership. If
* falsy, the ownership check is skipped.
* @param string $publicColumn Optional column for storing public status. The
* column must represent "public" status as the value 1.
*/
public function apply(Omeka_Db_Select $select, $alias, $ownerColumn = 'owner_id', $publicColumn = 'public')
{
// If the current user has the 'all' permission, we don't need to do
// anything. If the 'all' permission's neither true nor false, we
// _shouldn't_ do anything, because the ACL isn't loaded.
if ($this->_allPermission || $this->_allPermission === null) {
return;
}
if ($ownerColumn && $this->_selfPermission) {
$select->where("{$alias}.{$publicColumn} = 1 OR {$alias}.{$ownerColumn} = ?", $this->_currentUser->id);
} else {
$select->where("{$alias}.{$publicColumn} = 1");
}
}
/**
* @param Omeka_Db_Select $select
* @param array $params
*/
public function applySearchFilters($select, $params)
{
$alias = $this->getTableAlias();
$paramNames = array('record_id', 'rating', 'user_id');
foreach ($paramNames as $paramName) {
if (isset($params[$paramName])) {
$select->where($alias . '.' . $paramName . ' = ?', array($params[$paramName]));
}
}
if (isset($params['sort'])) {
switch ($params['sort']) {
case 'asc':
$select->order("{$alias}.rating ASC");
break;
case 'dsc':
$select->order("{$alias}.rating DSC");
break;
}
}
}
/**
* Define filters for browse and findBy.
*
* Available filters are: "tag" or "tags", "public" and "featured". "sort"
* also adds specific sorting strategies "alpha" and "recent", but the
* normal sorting can also be used.
*
* @param Omeka_Db_Select $select
* @param array $params Key-value array of search parameters
* @return Omeka_Db_Select
*/
public function applySearchFilters($select, $params)
{
$db = $this->getDb();
foreach ($params as $paramName => $paramValue) {
switch ($paramName) {
case 'tag':
case 'tags':
$tags = explode(',', $paramValue);
$select->joinInner(array('tg' => $db->RecordsTags), 'tg.record_id = exhibits.id', array());
$select->joinInner(array('t' => $db->Tag), "t.id = tg.tag_id", array());
foreach ($tags as $k => $tag) {
$select->where('t.name = ?', trim($tag));
}
$select->where("tg.record_type = ? ", array('Exhibit'));
break;
case 'public':
$this->filterByPublic($select, $params['public']);
break;
case 'range':
$this->filterByRange($select, $params['range']);
break;
case 'featured':
$this->filterByFeatured($select, $params['featured']);
break;
}
}
return $select;
}
/**
* Can specify a range of valid Exhibit IDs or an individual ID
*
* @param Omeka_Db_Select $select
* @param string $range Example: 1-4, 75, 89
* @return void
*/
public function filterByRange($select, $range)
{
// Comma-separated expressions should be treated individually
$exprs = explode(',', $range);
// Construct a SQL clause where every entry in this array is linked by 'OR'
$wheres = array();
foreach ($exprs as $expr) {
// If it has a '-' in it, it is a range of item IDs. Otherwise it is
// a single item ID
if (strpos($expr, '-') !== false) {
list($start, $finish) = explode('-', $expr);
// Naughty naughty koolaid, no SQL injection for you
$start = (int) trim($start);
$finish = (int) trim($finish);
$wheres[] = "(exhibits.id BETWEEN {$start} AND {$finish})";
//It is a single item ID
} else {
$id = (int) trim($expr);
$wheres[] = "(exhibits.id = {$id})";
}
}
$where = join(' OR ', $wheres);
$select->where('(' . $where . ')');
}
public function feedItemCount($feedId)
{
$db = get_db();
$select = new Omeka_Db_Select();
$select->from(array('fiii' => $db->FeedImporterImportedItem), "COUNT(DISTINCT(fiii.id))");
$select->join(array('fii' => $db->FeedImporterImport), 'fii.id = fiii.import_id', array());
$select->join(array('fif' => $db->FeedImporterFeed), 'fif.id = fii.feed_id', array());
$select->where('fif.id = ?');
//echo $select;
$feedItemCount = $db->fetchOne($select, array($feedId));
return $feedItemCount;
}
/**
* This adds the joins and where clauses to respect an addon's privacy
* settings.
*
* @param Omeka_Db_Select $select The select object to modify.
* @param SolrSearch_Addon_Addon $addon The current addon. You should
* already know that this addon does have a public flag somewhere in its
* hierarchy before calling this.
*
* @return null
* @author Eric Rochester <*****@*****.**>
**/
private function _addFlag($select, $addon)
{
if (!is_null($addon->flag)) {
$table = $this->db->getTable($addon->table);
$select->where("`{$table->getTableName()}`.`{$addon->flag}`=1");
} else {
if (!is_null($addon->parentAddon)) {
$parent = $addon->parentAddon;
$table = $this->db->getTable($addon->table)->getTableName();
$ptable = $this->db->getTable($parent->table)->getTableName();
$select->join($ptable, "`{$table}`.`{$addon->parentKey}`=`{$ptable}`.`{$parent->idColumn}`", array());
$this->_addFlag($select, $parent);
}
}
}
/**
* Manipulate a Select object based on a set of criteria.
*
* @param Omeka_Db_Select $select
* @param array $params Possible parameters include:
* <ul>
* <li>record_types - array - Usually one or more of the following:
* All, Item, File</li>
* <li>sort - string - One of the following values: alpha</li>
* <li>element_set_name - string - Name of the element set to which
* results should belong.</li>
* </ul>
*/
public function applySearchFilters($select, $params)
{
$db = $this->getDb();
// Retrieve only elements matching a specific record type.
if (array_key_exists('record_types', $params)) {
$where = array();
foreach ($params['record_types'] as $recordTypeName) {
if ($recordTypeName == 'All') {
$where[] = 'element_sets.record_type IS NULL';
} else {
$where[] = 'element_sets.record_type = ' . $db->quote($recordTypeName);
}
}
$select->where('(' . join(' OR ', $where) . ')');
}
if (array_key_exists('sort', $params)) {
if ($params['sort'] == 'alpha') {
$select->order('elements.name ASC');
} else {
if ($params['sort'] == 'alphaBySet') {
$select->order('element_sets.name ASC')->order('elements.name ASC');
}
}
}
if (array_key_exists('element_set_name', $params)) {
$select->where('element_sets.name = binary ?', (string) $params['element_set_name']);
}
if (array_key_exists('element_name', $params)) {
$select->where('elements.name = binary ?', (string) $params['element_name']);
}
// Retrive results including, but not limited to, a specific item type.
if (array_key_exists('item_type_id', $params)) {
$select->joinLeft(array('item_types_elements' => $db->ItemTypesElements), 'item_types_elements.element_id = elements.id', array());
$select->where('item_types_elements.item_type_id = ? OR item_types_elements.item_type_id IS NULL', (int) $params['item_type_id']);
} else {
if (array_key_exists('exclude_item_type', $params)) {
$select->where('element_sets.name != ?', ElementSet::ITEM_TYPE_NAME);
} else {
if (array_key_exists('item_type', $params)) {
//for the API for item_types
$select->joinLeft(array('item_types_elements' => $db->ItemTypesElements), 'item_types_elements.element_id = elements.id', array());
$select->where('item_types_elements.item_type_id = ? ', (int) $params['item_type']);
}
}
}
// REST API params.
if (array_key_exists('name', $params)) {
$select->where("elements.name = ?", $params['name']);
}
if (array_key_exists('element_set', $params)) {
$select->where("elements.element_set_id = ?", $params['element_set']);
}
}
/**
* Filter SELECT statement based on items that are not tagged with a specific
* set of tags
*
* @param Zend_Db_Select
* @param array|string Set of tag names (either array or comma-delimited string)
* @return void
*/
public function filterByExcludedTags($select, $tags)
{
$db = $this->getDb();
if (!is_array($tags)) {
$tags = explode(get_option('tag_delimiter'), $tags);
}
$subSelect = new Omeka_Db_Select();
$subSelect->from(array('items' => $db->Item), 'items.id')->joinInner(array('records_tags' => $db->RecordsTags), 'records_tags.record_id = items.id AND records_tags.record_type = "Item"', array())->joinInner(array('tags' => $db->Tag), 'records_tags.tag_id = tags.id', array());
foreach ($tags as $key => $tag) {
$subSelect->where('tags.name LIKE ?', $tag);
}
$select->where('items.id NOT IN (' . $subSelect->__toString() . ')');
}
/**
* Responds to the ListSets verb.
*
* Outputs setSpec and setName for all OAI-PMH sets (Omeka collections).
*
* @todo replace with Zend_Db_Select to allow use of limit or pageLimit
*/
private function listSets()
{
$db = get_db();
if ((bool) get_option('oaipmh_repository_expose_empty_collections')) {
$collections = get_db()->getTable('Collection')->findBy(array('public' => '1'));
} else {
$select = new Omeka_Db_Select();
$select->from(array('collections' => $db->Collection))->joinInner(array('items' => $db->Item), 'collections.id = items.collection_id', array())->where('collections.public = 1')->where('items.public = 1')->group('collections.id');
$collections = get_db()->getTable('Collection')->fetchObjects($select);
}
if (count($collections) == 0) {
$this->throwError(self::OAI_ERR_NO_SET_HIERARCHY);
}
$listSets = $this->document->createElement('ListSets');
if (!$this->error) {
$this->document->documentElement->appendChild($listSets);
foreach ($collections as $collection) {
$name = metadata($collection, array('Dublin Core', 'Title')) ?: __('[Untitled]');
$elements = array('setSpec' => $collection->id, 'setName' => $name);
$set = $listSets->appendNewElementWithChildren('set', $elements);
$this->_addSetDescription($set, $collection);
}
}
}
/**
* Apply an element filter to the select object.
*
* @see self::applySearchFilters()
* @param Omeka_Db_Select $select
* @param string|array $recordType One or multiple record types.
* May be a "0" for non element change.
*/
public function filterByRecordType(Omeka_Db_Select $select, $recordType)
{
if (empty($recordType)) {
return;
}
// Reset elements to ids.
if (!is_array($recordType)) {
$recordType = array($recordType);
}
$alias = $this->getTableAlias();
// One change.
if (count($recordType) == 1) {
$select->where("`{$alias}`.`record_type` = ?", reset($recordType));
} else {
$select->where("`{$alias}`.`record_type` IN (?)", $recordType);
}
}
public function hookItemsBrowseSql($args)
{
$select = $args['select'];
$params = $args['params'];
$user = current_user();
if (!$user) {
return;
}
if (isset($params['my-tag'])) {
$tagName = $params['my-tag'];
$db = $this->_db;
$subSelect = new Omeka_Db_Select();
$subSelect->from(array('user_records_tags' => $db->UserRecordsTags), array('items.id' => 'user_records_tags.record_id'))->joinInner(array('tags' => $db->Tag), 'tags.id = user_records_tags.tag_id', array())->where('tags.name = ? AND user_records_tags.`record_type` = "Item"', trim($tagName));
$subSelect->where('user_records_tags.owner_id = ?', $user->id);
$select->where('items.id IN (' . (string) $subSelect . ')');
}
}
/**
*
* @param unknown_type $params
* newspaperIds
* states
*
*/
public function getStats($params = array())
{
$select = new Omeka_Db_Select($this->getDb()->getAdapter());
$db = $this->_db;
$select->from($db->NewspapersNewspaper, '*');
$select->join($db->NewspapersIssue, "{$db->NewspapersIssue}.newspaper_id = {$db->NewspapersNewspaper}.id", array());
$select->join($db->NewspapersFrontPage, "{$db->NewspapersFrontPage}.issue_id = {$db->NewspapersIssue}.id", array(new Zend_Db_Expr("std({$db->NewspapersFrontPage}.columns) as stdColumns"), new Zend_Db_Expr("avg({$db->NewspapersFrontPage}.columns) as avgColumns"), new Zend_Db_Expr("min({$db->NewspapersFrontPage}.columns) as minColumns"), new Zend_Db_Expr("max({$db->NewspapersFrontPage}.columns) as maxColumns"), new Zend_Db_Expr("max({$db->NewspapersFrontPage}.page_height) as maxPageHeight"), new Zend_Db_Expr("min({$db->NewspapersFrontPage}.page_height) as minPageHeight"), new Zend_Db_Expr("avg({$db->NewspapersFrontPage}.page_height) as avgPageHeight"), new Zend_Db_Expr("std({$db->NewspapersFrontPage}.page_height) as stdPageHeight"), new Zend_Db_Expr("max({$db->NewspapersFrontPage}.page_width) as maxPageWidth"), new Zend_Db_Expr("min({$db->NewspapersFrontPage}.page_width) as minPageWidth"), new Zend_Db_Expr("avg({$db->NewspapersFrontPage}.page_width) as avgPageWidth"), new Zend_Db_Expr("std({$db->NewspapersFrontPage}.page_width) as stdPageWidth")));
if (isset($params['states'])) {
$select->where("{$db->NewspapersNewspaper}.state IN (?)", $params['states']);
}
if (isset($params['newspaperIds'])) {
$select->where("{$db->NewspapersNewspaper}.id IN (?)", $params['newspaperIds']);
}
if (isset($params['columns'])) {
$select->where("{$db->NewspapersFrontPage}.columns = ?", $params['columns']);
}
if (isset($params['columns_greater_than'])) {
$select->where("{$db->NewspapersFrontPage}.columns > ", $params['columns']);
}
if (isset($params['columns_less_than'])) {
$select->where("{$db->NewspapersFrontPage}.columns < ", $params['columns']);
}
//precision is iffy, so include a range
if (isset($params['width'])) {
$floor = $params['width'] - 500;
$ceil = $params['width'] + 500;
$select->where("{$db->NewspapersFrontPage}.page_width BETWEEN {$floor} AND {$ceil}");
}
if (isset($params['height'])) {
$floor = $params['height'] - 500;
$ceil = $params['height'] + 500;
$select->where("{$db->NewspapersFrontPage}.page_height BETWEEN {$floor} AND {$ceil}");
}
$result = $this->_db->fetchAll($select);
return $result[0];
}
/**
* @internal SELECT statements should always pull a count of how many times
* the tag occurs as a tagCount field in the Tag object.
*
* @return Omeka_Db_Select
*/
public function getSelect()
{
$select = new Omeka_Db_Select();
$db = $this->getDb();
$select->from(array('tags' => $db->Tag), array('tags.*', 'tagCount' => 'COUNT(tags.id)'))->joinInner(array('records_tags' => $db->RecordsTags), 'records_tags.tag_id = tags.id', array())->group('tags.id');
return $select;
}
/**
* @internal Avoid the unnecessary expense of joining if we're just counting
* all the tags.
*/
public function getSelectForCount($params = array())
{
if (!$params) {
$select = new Omeka_Db_Select();
$db = $this->getDb();
$select->from(array('tags' => $db->Tag), array('COUNT(*)'));
} else {
$select = parent::getSelectForCount($params);
}
return $select;
}
