CategoryDAO.inc.php

<?php
 
import ('lib.pkp.classes.context.Category');
 
class CategoryDAO extends DAO {
   function getById($categoryId, $contextId = null, $parentId = null) {
      $params = array((int) $categoryId);
      if ($contextId) $params[] = (int) $contextId;
      if ($parentId) $params[] = (int) $parentId;
 
      $result = $this->retrieve(
         'SELECT  *
         FROM  categories
         WHERE category_id = ?
         ' . ($contextId?' AND context_id = ?':'') . '
         ' . ($parentId?' AND parent_id = ?':''),
         $params
      );
 
      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = $this->_fromRow($result->GetRowAssoc(false));
      }
      $result->Close();
      return $returner;
   }
 
   function getByPath($path, $contextId) {
      $returner = null;
      $result = $this->retrieve(
         'SELECT * FROM categories WHERE path = ? AND context_id = ?',
         array((string) $path, (int) $contextId)
      );
 
      if ($result->RecordCount() != 0) {
         $returner = $this->_fromRow($result->GetRowAssoc(false));
      }
 
      $result->Close();
      return $returner;
   }
 
   function getByTitle($categoryTitle, $contextId, $locale = null) {
      $params = array('title', $categoryTitle, (int) $contextId);
      if ($locale) $params[] = $locale;
 
      $result = $this->retrieve(
         'SELECT  a.*
         FROM  categories a,
            category_settings l
         WHERE l.category_id = a.category_id AND
            l.setting_name = ? AND
            l.setting_value = ?
            AND a.context_id = ?
            ' . ($locale?' AND locale = ?':'') . '
         ORDER BY seq',
         $params
      );
 
      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = $this->_fromRow($result->GetRowAssoc(false));
      }
 
      $result->Close();
      return $returner;
   }
 
   public function getByPublicationId($publicationId) {
      $result = $this->retrieve(
         'SELECT  c.*
         FROM categories c
         INNER JOIN publication_categories pc ON (pc.category_id = c.category_id)
         WHERE pc.publication_id = ?',
         (int) $publicationId
      );
 
      return new DAOResultFactory($result, $this, '_fromRow');
   }
 
   function categoryExistsByPath($path) {
      $result = $this->retrieve(
         'SELECT COUNT(*) FROM categories WHERE path = ?', $path
      );
      $returner = isset($result->fields[0]) && $result->fields[0] == 1 ? true : false;
 
      $result->Close();
      return $returner;
   }
 
   function newDataObject() {
      return new Category();
   }
 
   function _fromRow($row) {
      $category = $this->newDataObject();
 
      $category->setId($row['category_id']);
      $category->setContextId($row['context_id']);
      $category->setParentId($row['parent_id']);
      $category->setPath($row['path']);
      $category->setImage(unserialize($row['image']));
      $category->setSequence($row['seq']);
 
      $this->getDataObjectSettings('category_settings', 'category_id', $row['category_id'], $category);
 
      HookRegistry::call('CategoryDAO::_fromRow', array(&$category, &$row));
 
      return $category;
   }
 
   function getLocaleFieldNames() {
      return array('title', 'description');
   }
 
   function getAdditionalFieldNames() {
      return array_merge(
         parent::getAdditionalFieldNames(),
         array(
            'sortOption',
         )
      );
   }
 
   function updateLocaleFields($category) {
      $this->updateDataObjectSettings(
         'category_settings', $category,
         array(
            'category_id' => $category->getId()
         )
      );
   }
 
   function insertObject($category) {
      $this->update(
         'INSERT INTO categories
            (context_id, parent_id, path, image, seq)
            VALUES
            (?, ?, ?, ?, ?)',
         array(
            (int) $category->getContextId(),
            (int) $category->getParentId(),
            $category->getPath(),
            serialize($category->getImage() ? $category->getImage() : array()),
            (int) $category->getSequence()
         )
      );
 
      $category->setId($this->getInsertId());
      $this->updateLocaleFields($category);
      return $category->getId();
   }
 
   function updateObject($category) {
      $returner = $this->update(
         'UPDATE  categories
         SET   context_id = ?,
            parent_id = ?,
            path = ?,
            image = ?,
            seq = ?
         WHERE category_id = ?',
         array(
            (int) $category->getContextId(),
            (int) $category->getParentId(),
            $category->getPath(),
            serialize($category->getImage() ? $category->getImage() : array()),
            (int) $category->getSequence(),
            (int) $category->getId()
         )
      );
      $this->updateLocaleFields($category);
      return $returner;
   }
 
   function resequenceCategories($contextId, $parentCategoryId = null) {
      $params = array((int) $contextId);
      if ($parentCategoryId) $params[] = (int) $parentCategoryId;
      $result = $this->retrieve(
         'SELECT category_id FROM categories WHERE context_id = ?' .
         ($parentCategoryId?' AND parent_id = ?':''),
         $params
      );
 
      for ($i=1; !$result->EOF; $i++) {
         list($categoryId) = $result->fields;
         $this->update(
            'UPDATE categories SET seq = ? WHERE category_id = ?',
            array(
               (int) $i,
               (int) $categoryId
            )
         );
 
         $result->MoveNext();
      }
 
      $result->Close();
   }
 
   function deleteObject($category) {
      return $this->deleteById(
         $category->getId(),
         $category->getContextId()
      );
   }
 
   function deleteById($categoryId, $contextId = null) {
      $params = array((int) $categoryId);
      if ($contextId) $params[] = (int) $contextId;
 
      $this->update(
         'DELETE FROM categories
         WHERE category_id = ?
            ' . ($contextId?' AND context_id = ?':''),
         $params
      );
 
      // If the category was deleted (this validates context_id,
      // if specified), delete any associated settings as well.
      if ($this->getAffectedRows()) {
         $this->update(
            'DELETE FROM category_settings WHERE category_id = ?',
            array((int) $categoryId)
         );
 
         // remove any monograph assignments for this category.
         $this->update(
            'DELETE FROM publication_categories WHERE category_id = ?',
            array((int) $categoryId)
         );
      }
   }
 
   function deleteByContextId($contextId) {
      $categories = $this->getByContextId($contextId);
      while ($category = $categories->next()) {
         $this->deleteObject($category, $contextId);
      }
   }
 
   public function insertPublicationAssignment($categoryId, $publicationId) {
      $this->update(
         'INSERT INTO publication_categories (category_id, publication_id)
         VALUES (?, ?)',
         array((int) $categoryId, (int) $publicationId)
      );
   }
 
   public function deletePublicationAssignments($publicationId) {
      $this->update(
         'DELETE FROM publication_categories WHERE publication_id = ?',
         array((int) $publicationId)
      );
   }
 
   function getByContextId($contextId, $rangeInfo = null) {
      // The strange ORDER BY clause is to return subcategories
      // immediately after their parent category's entry.
      $result = $this->retrieveRange(
         'SELECT  c.*
         FROM  categories c
            LEFT JOIN categories pc ON (pc.category_id = c.parent_id)
         WHERE c.context_id = ?
         ORDER BY (COALESCE((pc.seq * 8192) + pc.category_id, 0) * 8192) + CASE WHEN pc.category_id IS NULL THEN 8192 * ((c.seq * 8192) + c.category_id) ELSE c.seq END',
         array((int) $contextId)
      );
 
      return new DAOResultFactory($result, $this, '_fromRow');
   }
 
   function getCountByContextId($contextId) {
      $result = $this->retrieve(
         'SELECT  COUNT(*)
         FROM  categories
         WHERE context_id = ?',
         (int) $contextId
      );
 
      $returner = $result->fields[0];
      $result->Close();
      return $returner;
   }
 
   function getByParentId($parentId, $contextId = null, $rangeInfo = null) {
      $params = array((int) $parentId);
      if ($contextId) $params[] = (int) $contextId;
 
      $result = $this->retrieveRange(
         'SELECT  *
         FROM  categories
         WHERE parent_id = ?
         ' . ($contextId?' AND context_id = ?':'') . '
         ORDER BY seq',
         $params
      );
      return new DAOResultFactory($result, $this, '_fromRow');
   }
 
   function getInsertId() {
      return $this->_getInsertId('categories', 'category_id');
   }
}