/administrator/components/com_zoo/tables/item.php
PHP | 492 lines | 230 code | 82 blank | 180 comment | 25 complexity | abf3bb4cf8d64dfefeab8379aecba95f MD5 | raw file
- <?php
- /**
- * @package ZOO Component
- * @file item.php
- * @version 2.3.0 December 2010
- * @author YOOtheme http://www.yootheme.com
- * @copyright Copyright (C) 2007 - 2011 YOOtheme GmbH
- * @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPLv2 only
- */
-
- /*
- Class: TableApplication
- The table class for items.
- */
- class ItemTable extends YTable {
-
- protected function __construct() {
- parent::__construct('Item', ZOO_TABLE_ITEM, 'id');
-
- }
-
- /*
- Function: save
- Override. Save object to database table.
-
- Returns:
- Boolean.
- */
- public function save($object) {
-
- if (!is_string($object->type) || empty($object->type)) {
- throw new ItemTableException('Invalid type id');
- }
-
- if ($object->name == '') {
- throw new ItemTableException('Invalid name');
- }
-
- if ($object->alias == '' || $object->alias != YString::sluggify($object->alias)) {
- throw new ItemTableException('Invalid slug');
- }
-
- if (ItemHelper::checkAliasExists($object->alias, $object->id)) {
- throw new ItemTableException('Alias already exists, please choose a unique alias');
- }
-
- // first save to get id
- if (empty($object->id)) {
- parent::save($object);
- }
-
- // init vars
- $db = $this->getDBO();
- $search_data = array();
- $element_data = array();
-
- foreach ($object->getElements() as $id => $element) {
-
- // get element data
- $element_data[] = $element->toXML();
-
- // get search data
- if ($data = $element->getSearchData()) {
- $search_data[] = "(".$object->id.", ".$db->quote($id).", ".$db->quote($data).")";
- }
- }
-
- // set element data
- $object->elements = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n<elements>\n".implode("\n", $element_data)."\n</elements>";
-
- // delete old search data
- $query = "DELETE FROM ".ZOO_TABLE_SEARCH
- ." WHERE item_id = ".(int) $object->id;
- $db->query($query);
-
- // insert new search data
- if (count($search_data)) {
- $query = "INSERT INTO ".ZOO_TABLE_SEARCH
- ." VALUES ".implode(", ", $search_data);
- $db->query($query);
- }
-
- // save tags
- YTable::getInstance('tag')->save($object->id, $object->getTags());
-
- return parent::save($object);
- }
-
- /*
- Function: delete
- Override. Delete object from database table.
-
- Returns:
- Boolean.
- */
- public function delete($object) {
-
- // get database
- $db = $this->getDBO();
-
- // delete item to category relations
- $query = "DELETE FROM ".ZOO_TABLE_CATEGORY_ITEM
- ." WHERE item_id = ".(int) $object->id;
- $db->query($query);
-
- // delete related comments
- $query = "DELETE FROM ".ZOO_TABLE_COMMENT
- ." WHERE item_id = ".(int) $object->id;
- $db->query($query);
-
- // delete related search data rows
- $query = "DELETE FROM ".ZOO_TABLE_SEARCH
- ." WHERE item_id = ". (int) $object->id;
- $db->query($query);
-
- // delete related rating data rows
- $query = "DELETE FROM ".ZOO_TABLE_RATING
- ." WHERE item_id = ". (int) $object->id;
- $db->query($query);
-
- return parent::delete($object);
- }
-
- /*
- Function: hit
- Increment item hits.
-
- Returns:
- Boolean.
- */
- public function hit($object) {
-
- // get database
- $db = $this->getDBO();
- $key = $this->getKeyName();
-
- // increment hits
- if ($object->$key) {
- $query = "UPDATE ".$this->getTableName()
- ." SET hits = (hits + 1)"
- ." WHERE $key = ".(int) $object->$key;
- $db->query($query);
- $object->hits++;
- return true;
- }
-
- return false;
- }
-
- /*
- Function: getByType
- Method to get types related items.
-
- Parameters:
- $type_id - Type
-
- Returns:
- Array - Items
- */
- public function getByType($type_id, $application_id = false){
-
- // get database
- $db = $this->getDBO();
-
- $query = "SELECT a.*"
- ." FROM ".$this->getTableName()." AS a"
- ." WHERE a.type = ".$db->Quote($type_id)
- .($application_id !== false ? " AND a.application_id = ".(int) $application_id : "");
-
- return $this->_queryObjectList($query);
- }
-
- /*
- Function: getApplicationItemCount
- Method to get application related item count.
-
- Parameters:
- $application_id - Application id
-
- Returns:
- Int
- */
- public function getApplicationItemCount($application_id) {
- $query = "SELECT count(a.id) AS item_count"
- ." FROM ".$this->getTableName()." AS a"
- ." WHERE a.application_id = ".(int) $application_id;
-
- return (int) $this->_queryResult($query);
- }
-
- /*
- Function: getTypeItemCount
- Method to get types related item count.
-
- Parameters:
- $type - Type
-
- Returns:
- Int
- */
- public function getTypeItemCount($type){
-
- // get database
- $db = $this->getDBO();
-
- $group = $type->getApplication()->getGroup();
- $query = "SELECT count(a.id) AS item_count"
- ." FROM ".$this->getTableName()." AS a"
- ." JOIN ".ZOO_TABLE_APPLICATION." AS b ON a.application_id = b.id"
- ." WHERE a.type = ".$db->Quote($type->id)
- ." AND b.application_group = ".$db->Quote($group);
-
- return (int) $this->_queryResult($query);
-
- }
-
- /*
- Function: findAll
- Method to retrieve all items.
-
- Parameters:
- $application_id - Application id
- $published - Get published items only
- $user - check access level of this user, else current user is used
- $options - additional options
-
- Returns:
- Array - Array of items
- */
- public function findAll($application_id = false, $published = false, $user = null, $options = array()) {
-
- // get database
- $db = $this->getDBO();
-
- // get user from session, if not set
- if (empty($user)) {
- $user = JFactory::getUser();
- }
-
- // get user access id
- $access_id = $user->get('aid', 0);
-
- // get date
- $date = JFactory::getDate();
- $now = $db->Quote($date->toMySQL());
- $null = $db->Quote($db->getNullDate());
-
- // set query options
- $conditions =
- ($application_id !== false ? "application_id = ".(int) $application_id : "")
- ." AND access <= ".(int) $access_id
- .($published == true ? " AND state = 1"
- ." AND (publish_up = ".$null." OR publish_up <= ".$now.")"
- ." AND (publish_down = ".$null." OR publish_down >= ".$now.")": "");
-
- return $this->find('all', array_merge(compact('conditions'), $options));
- }
-
- /*
- Function: getByCharacter
- Method to retrieve all items starting with a certain character.
-
- Parameters:
- $application_id - Application id
- $char - Character(s)
- $not_in - Use not in for matching multiple characters
-
- Returns:
- Array - Array of items
- */
- public function getByCharacter($application_id, $char, $not_in = false, $published = false, $user = null, $orderby = "", $offset = 0, $limit = 0){
-
- // get database
- $db = $this->getDBO();
-
- // get user from session, if not set
- if (empty($user)) {
- $user = JFactory::getUser();
- }
-
- // get user access id
- $access_id = $user->get('aid', 0);
-
- $date = JFactory::getDate();
- $now = $db->Quote($date->toMySQL());
- $null = $db->Quote($db->getNullDate());
-
- // escape and quote character array
- if (is_array($char)) {
- foreach ($char as $key => $val) {
- $char[$key] = "'".$db->getEscaped($val)."'";
- }
- }
-
- $query = "SELECT a.*"
- ." FROM ".ZOO_TABLE_CATEGORY_ITEM." AS ci"
- ." JOIN ".$this->getTableName()." AS a ON a.id = ci.item_id"
- ." WHERE a.application_id = ".(int) $application_id
- ." AND a.access <= ".(int) $access_id
- .($published == true ? " AND a.state = 1"
- ." AND (a.publish_up = ".$null." OR a.publish_up <= ".$now.")"
- ." AND (a.publish_down = ".$null." OR a.publish_down >= ".$now.")": "")
- ." AND BINARY LOWER(LEFT(a.name, 1)) ".(is_array($char) ? ($not_in ? "NOT" : null)." IN (".implode(",", $char).")" : " = '".$db->getEscaped($char)."'")
- ." ORDER BY a.priority DESC ".($orderby != "" ? ", ".$orderby : "")
- .(($limit ? " LIMIT ".(int)$offset.",".(int)$limit : ""));
-
- return $this->_queryObjectList($query);
- }
-
- /*
- Function: getByTag
- Method to retrieve all items matching a certain tag.
-
- Parameters:
- $tag - Tag name
- $application_id - Application id
-
- Returns:
- Array - Array of items
- */
- public function getByTag($application_id, $tag, $published = false, $user = null, $orderby = "", $offset = 0, $limit = 0){
-
- // get database
- $db = $this->getDBO();
-
- // get user from session, if not set
- if (empty($user)) {
- $user = JFactory::getUser();
- }
-
- // get user access id
- $access_id = $user->get('aid', 0);
-
- // get dates
- $date = JFactory::getDate();
- $now = $db->Quote($date->toMySQL());
- $null = $db->Quote($db->getNullDate());
-
- $query = "SELECT a.*"
- ." FROM ".$this->getTableName()." AS a "
- ." LEFT JOIN ".ZOO_TABLE_TAG." AS b ON a.id = b.item_id"
- ." WHERE a.application_id = ".(int) $application_id
- ." AND b.name = '".$db->getEscaped($tag)."'"
- ." AND a.access <= ".(int) $access_id
- .($published == true ? " AND a.state = 1"
- ." AND (a.publish_up = ".$null." OR a.publish_up <= ".$now.")"
- ." AND (a.publish_down = ".$null." OR a.publish_down >= ".$now.")": "")
- ." GROUP BY a.id"
- ." ORDER BY a.priority DESC ".($orderby != "" ? ", ".$orderby : "")
- .(($limit ? " LIMIT ".(int)$offset.",".(int)$limit : ""));
-
- return $this->_queryObjectList($query);
- }
-
- /*
- Function: getFromCategory
- Method to retrieve all items of a category.
-
- Parameters:
- $category_id - Category id(s)
-
- Returns:
- Array - Array of items
- */
- public function getFromCategory($application_id, $category_id, $published = false, $user = null, $orderby = "", $offset = 0, $limit = 0){
-
- // get database
- $db = $this->getDBO();
-
- // get user from session, if not set
- if (empty($user)) {
- $user = JFactory::getUser();
- }
-
- // get user access id
- $access_id = $user->get('aid', 0);
-
- // get dates
- $date = JFactory::getDate();
- $now = $db->Quote($date->toMySQL());
- $null = $db->Quote($db->getNullDate());
-
- $query = "SELECT a.*"
- ." FROM ".$this->getTableName()." AS a"
- ." LEFT JOIN ".ZOO_TABLE_CATEGORY_ITEM." AS b ON a.id = b.item_id"
- ." WHERE a.application_id = ".(int) $application_id
- ." AND b.category_id ".(is_array($category_id) ? " IN (".implode(",", $category_id).")" : " = ".(int) $category_id)
- ." AND a.access <= ".(int) $access_id
- .($published == true ? " AND a.state = 1"
- ." AND (a.publish_up = ".$null." OR a.publish_up <= ".$now.")"
- ." AND (a.publish_down = ".$null." OR a.publish_down >= ".$now.")": "")
- ." GROUP BY a.id"
- ." ORDER BY a.priority DESC ".($orderby != "" ? ", ".$orderby : "")
- .(($limit ? " LIMIT ".(int)$offset.",".(int)$limit : ""));
-
- return $this->_queryObjectList($query);
- }
-
- /*
- Function: search
- Method to retrieve all items matching search data.
-
- Parameters:
- $search_string - the search string
- $app_id - specify an application id to limit the search scope
-
- Returns:
- Array - Array of items
- */
- public function search($search_string, $app_id = 0) {
-
- // get database
- $db = $this->getDBO();
- $db_search = $db->Quote('%'.$db->getEscaped( $search_string, true ).'%', false);
-
- $query = "SELECT a.*"
- ." FROM ".$this->getTableName()." AS a"
- ." LEFT JOIN ".ZOO_TABLE_SEARCH." AS b ON a.id = b.item_id"
- ." WHERE (LOWER(b.value) LIKE LOWER(" . $db_search . ")"
- ." OR LOWER(a.name) LIKE LOWER(" . $db_search . "))"
- . (empty($app_id) ? "" : " AND application_id = " . $app_id)
- ." AND a.searchable=1"
- ." GROUP BY a.id";
-
- return $this->_queryObjectList($query);
- }
-
- /*
- Function: searchElements
- Method to retrieve all items matching search data.
-
- Parameters:
- $elements_array - key = element_name, value = search string
- $app_id - specify an application id to limit the search scope
-
- Returns:
- Array - Array of items
- */
- public function searchElements($elements_array, $app_id = 0) {
-
- // get database
- $db = $this->getDBO();
-
- $i = 0;
- $join = array();
- $where = array();
- foreach ($elements_array as $name => $search_string) {
- $as = "table" . $i;
- $db_name = $db->Quote($db->getEscaped( $name, true ), false);
- $db_search = $db->Quote('%'.$db->getEscaped( $search_string, true ).'%', false);
- $join[] = " LEFT JOIN ".ZOO_TABLE_SEARCH." AS " . $as . " ON a.id = " . $as . ".item_id";
- $where[] = $as.".element_id = ".$db_name." AND LOWER(".$as.".value) LIKE LOWER(".$db_search.")";
- $i++;
- }
-
- $query = "SELECT a.*"
- ." FROM ".$this->getTableName()." AS a "
- . implode(" ", $join)
- ." WHERE "
- . implode(" AND ", $where)
- . (empty($app_id) ? "" : " AND application_id = " . $app_id)
- ." AND a.searchable=1"
- ." GROUP BY a.id";
-
- return $this->_queryObjectList($query);
- }
-
- /*
- Function: getUsers
- Method to get users of items
-
- Parameters:
- $app_id - Application id
-
- Returns:
- Array - Array of items
- */
- public function getUsers($app_id) {
- $query = 'SELECT DISTINCT u.id, u.name'
- .' FROM '.$this->getTableName().' AS i'
- .' JOIN #__users AS u ON i.created_by = u.id'
- . ((empty($app_id)) ? "" : " WHERE i.application_id = ".$app_id);
-
- return $this->getDBO()->queryObjectList($query, 'id');
- }
-
- }
-
- /*
- Class: ItemTableException
- */
- class ItemTableException extends YException {}