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;
}
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;
}
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;
}
/**
*
* @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;
}
/**
* 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() . ')');
}
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 . ')');
}
}
/**
* 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);
}
}
}
/**
* @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;
}
