1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Catalog
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: 
 28: /**
 29:  * Catalog category model
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Catalog
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Catalog_Model_Resource_Category extends Mage_Catalog_Model_Resource_Abstract
 36: {
 37:     /**
 38:      * Category tree object
 39:      *
 40:      * @var Varien_Data_Tree_Db
 41:      */
 42:     protected $_tree;
 43: 
 44:     /**
 45:      * Catalog products table name
 46:      *
 47:      * @var string
 48:      */
 49:     protected $_categoryProductTable;
 50: 
 51:     /**
 52:      * Id of 'is_active' category attribute
 53:      *
 54:      * @var int
 55:      */
 56:     protected $_isActiveAttributeId      = null;
 57: 
 58:     /**
 59:      * Store id
 60:      *
 61:      * @var int
 62:      */
 63:     protected $_storeId                  = null;
 64: 
 65:     /**
 66:      * Class constructor
 67:      *
 68:      */
 69:     public function __construct()
 70:     {
 71:         $resource = Mage::getSingleton('core/resource');
 72:         $this->setType(Mage_Catalog_Model_Category::ENTITY)
 73:             ->setConnection(
 74:                 $resource->getConnection('catalog_read'),
 75:                 $resource->getConnection('catalog_write')
 76:             );
 77:         $this->_categoryProductTable = $this->getTable('catalog/category_product');
 78:     }
 79: 
 80:     /**
 81:      * Set store Id
 82:      *
 83:      * @param integer $storeId
 84:      * @return Mage_Catalog_Model_Resource_Category
 85:      */
 86:     public function setStoreId($storeId)
 87:     {
 88:         $this->_storeId = $storeId;
 89:         return $this;
 90:     }
 91: 
 92:     /**
 93:      * Return store id
 94:      *
 95:      * @return integer
 96:      */
 97:     public function getStoreId()
 98:     {
 99:         if ($this->_storeId === null) {
100:             return Mage::app()->getStore()->getId();
101:         }
102:         return $this->_storeId;
103:     }
104: 
105:     /**
106:      * Retrieve category tree object
107:      *
108:      * @return Varien_Data_Tree_Db
109:      */
110:     protected function _getTree()
111:     {
112:         if (!$this->_tree) {
113:             $this->_tree = Mage::getResourceModel('catalog/category_tree')
114:                 ->load();
115:         }
116:         return $this->_tree;
117:     }
118: 
119:     /**
120:      * Process category data before delete
121:      * update children count for parent category
122:      * delete child categories
123:      *
124:      * @param Varien_Object $object
125:      * @return Mage_Catalog_Model_Resource_Category
126:      */
127:     protected function _beforeDelete(Varien_Object $object)
128:     {
129:         parent::_beforeDelete($object);
130: 
131:         /**
132:          * Update children count for all parent categories
133:          */
134:         $parentIds = $object->getParentIds();
135:         if ($parentIds) {
136:             $childDecrease = $object->getChildrenCount() + 1; // +1 is itself
137:             $data = array('children_count' => new Zend_Db_Expr('children_count - ' . $childDecrease));
138:             $where = array('entity_id IN(?)' => $parentIds);
139:             $this->_getWriteAdapter()->update( $this->getEntityTable(), $data, $where);
140:         }
141:         $this->deleteChildren($object);
142:         return $this;
143:     }
144: 
145:     /**
146:      * Delete children categories of specific category
147:      *
148:      * @param Varien_Object $object
149:      * @return Mage_Catalog_Model_Resource_Category
150:      */
151:     public function deleteChildren(Varien_Object $object)
152:     {
153:         $adapter = $this->_getWriteAdapter();
154:         $pathField = $adapter->quoteIdentifier('path');
155: 
156:         $select = $adapter->select()
157:             ->from($this->getEntityTable(), array('entity_id'))
158:             ->where($pathField . ' LIKE :c_path');
159: 
160:         $childrenIds = $adapter->fetchCol($select, array('c_path' => $object->getPath() . '/%'));
161: 
162:         if (!empty($childrenIds)) {
163:             $adapter->delete(
164:                 $this->getEntityTable(),
165:                 array('entity_id IN (?)' => $childrenIds)
166:             );
167:         }
168: 
169:         /**
170:          * Add deleted children ids to object
171:          * This data can be used in after delete event
172:          */
173:         $object->setDeletedChildrenIds($childrenIds);
174:         return $this;
175:     }
176: 
177:     /**
178:      * Process category data before saving
179:      * prepare path and increment children count for parent categories
180:      *
181:      * @param Varien_Object $object
182:      * @return Mage_Catalog_Model_Resource_Category
183:      */
184:     protected function _beforeSave(Varien_Object $object)
185:     {
186:         parent::_beforeSave($object);
187: 
188:         if (!$object->getChildrenCount()) {
189:             $object->setChildrenCount(0);
190:         }
191:         if ($object->getLevel() === null) {
192:             $object->setLevel(1);
193:         }
194: 
195:         if (!$object->getId()) {
196:             $object->setPosition($this->_getMaxPosition($object->getPath()) + 1);
197:             $path  = explode('/', $object->getPath());
198:             $level = count($path);
199:             $object->setLevel($level);
200:             if ($level) {
201:                 $object->setParentId($path[$level - 1]);
202:             }
203:             $object->setPath($object->getPath() . '/');
204: 
205:             $toUpdateChild = explode('/',$object->getPath());
206: 
207:             $this->_getWriteAdapter()->update(
208:                 $this->getEntityTable(),
209:                 array('children_count'  => new Zend_Db_Expr('children_count+1')),
210:                 array('entity_id IN(?)' => $toUpdateChild)
211:             );
212: 
213:         }
214:         return $this;
215:     }
216: 
217:     /**
218:      * Process category data after save category object
219:      * save related products ids and update path value
220:      *
221:      * @param Varien_Object $object
222:      * @return Mage_Catalog_Model_Resource_Category
223:      */
224:     protected function _afterSave(Varien_Object $object)
225:     {
226:         /**
227:          * Add identifier for new category
228:          */
229:         if (substr($object->getPath(), -1) == '/') {
230:             $object->setPath($object->getPath() . $object->getId());
231:             $this->_savePath($object);
232:         }
233: 
234:         $this->_saveCategoryProducts($object);
235:         return parent::_afterSave($object);
236:     }
237: 
238:     /**
239:      * Update path field
240:      *
241:      * @param Mage_Catalog_Model_Category $object
242:      * @return Mage_Catalog_Model_Resource_Category
243:      */
244:     protected function _savePath($object)
245:     {
246:         if ($object->getId()) {
247:             $this->_getWriteAdapter()->update(
248:                 $this->getEntityTable(),
249:                 array('path' => $object->getPath()),
250:                 array('entity_id = ?' => $object->getId())
251:             );
252:         }
253:         return $this;
254:     }
255: 
256:     /**
257:      * Get maximum position of child categories by specific tree path
258:      *
259:      * @param string $path
260:      * @return int
261:      */
262:     protected function _getMaxPosition($path)
263:     {
264:         $adapter = $this->getReadConnection();
265:         $positionField = $adapter->quoteIdentifier('position');
266:         $level   = count(explode('/', $path));
267:         $bind = array(
268:             'c_level' => $level,
269:             'c_path'  => $path . '/%'
270:         );
271:         $select  = $adapter->select()
272:             ->from($this->getTable('catalog/category'), 'MAX(' . $positionField . ')')
273:             ->where($adapter->quoteIdentifier('path') . ' LIKE :c_path')
274:             ->where($adapter->quoteIdentifier('level') . ' = :c_level');
275: 
276:         $position = $adapter->fetchOne($select, $bind);
277:         if (!$position) {
278:             $position = 0;
279:         }
280:         return $position;
281:     }
282: 
283:     /**
284:      * Save category products relation
285:      *
286:      * @param Mage_Catalog_Model_Category $category
287:      * @return Mage_Catalog_Model_Resource_Category
288:      */
289:     protected function _saveCategoryProducts($category)
290:     {
291:         $category->setIsChangedProductList(false);
292:         $id = $category->getId();
293:         /**
294:          * new category-product relationships
295:          */
296:         $products = $category->getPostedProducts();
297: 
298:         /**
299:          * Example re-save category
300:          */
301:         if ($products === null) {
302:             return $this;
303:         }
304: 
305:         /**
306:          * old category-product relationships
307:          */
308:         $oldProducts = $category->getProductsPosition();
309: 
310:         $insert = array_diff_key($products, $oldProducts);
311:         $delete = array_diff_key($oldProducts, $products);
312: 
313:         /**
314:          * Find product ids which are presented in both arrays
315:          * and saved before (check $oldProducts array)
316:          */
317:         $update = array_intersect_key($products, $oldProducts);
318:         $update = array_diff_assoc($update, $oldProducts);
319: 
320:         $adapter = $this->_getWriteAdapter();
321: 
322:         /**
323:          * Delete products from category
324:          */
325:         if (!empty($delete)) {
326:             $cond = array(
327:                 'product_id IN(?)' => array_keys($delete),
328:                 'category_id=?' => $id
329:             );
330:             $adapter->delete($this->_categoryProductTable, $cond);
331:         }
332: 
333:         /**
334:          * Add products to category
335:          */
336:         if (!empty($insert)) {
337:             $data = array();
338:             foreach ($insert as $productId => $position) {
339:                 $data[] = array(
340:                     'category_id' => (int)$id,
341:                     'product_id'  => (int)$productId,
342:                     'position'    => (int)$position
343:                 );
344:             }
345:             $adapter->insertMultiple($this->_categoryProductTable, $data);
346:         }
347: 
348:         /**
349:          * Update product positions in category
350:          */
351:         if (!empty($update)) {
352:             foreach ($update as $productId => $position) {
353:                 $where = array(
354:                     'category_id = ?'=> (int)$id,
355:                     'product_id = ?' => (int)$productId
356:                 );
357:                 $bind  = array('position' => (int)$position);
358:                 $adapter->update($this->_categoryProductTable, $bind, $where);
359:             }
360:         }
361: 
362:         if (!empty($insert) || !empty($delete)) {
363:             $productIds = array_unique(array_merge(array_keys($insert), array_keys($delete)));
364:             Mage::dispatchEvent('catalog_category_change_products', array(
365:                 'category'      => $category,
366:                 'product_ids'   => $productIds
367:             ));
368:         }
369: 
370:         if (!empty($insert) || !empty($update) || !empty($delete)) {
371:             $category->setIsChangedProductList(true);
372: 
373:             /**
374:              * Setting affected products to category for third party engine index refresh
375:              */
376:             $productIds = array_keys($insert + $delete + $update);
377:             $category->setAffectedProductIds($productIds);
378:         }
379:         return $this;
380:     }
381: 
382:     /**
383:      * Get positions of associated to category products
384:      *
385:      * @param Mage_Catalog_Model_Category $category
386:      * @return array
387:      */
388:     public function getProductsPosition($category)
389:     {
390:         $select = $this->_getWriteAdapter()->select()
391:             ->from($this->_categoryProductTable, array('product_id', 'position'))
392:             ->where('category_id = :category_id');
393:         $bind = array('category_id' => (int)$category->getId());
394: 
395:         return $this->_getWriteAdapter()->fetchPairs($select, $bind);
396:     }
397: 
398:     /**
399:      * Get chlden categories count
400:      *
401:      * @param int $categoryId
402:      * @return int
403:      */
404:     public function getChildrenCount($categoryId)
405:     {
406:         $select = $this->_getReadAdapter()->select()
407:             ->from($this->getEntityTable(), 'children_count')
408:             ->where('entity_id = :entity_id');
409:         $bind = array('entity_id' => $categoryId);
410: 
411:         return $this->_getReadAdapter()->fetchOne($select, $bind);
412:     }
413: 
414:     /**
415:      * Check if category id exist
416:      *
417:      * @param int $entityId
418:      * @return bool
419:      */
420:     public function checkId($entityId)
421:     {
422:         $select = $this->_getReadAdapter()->select()
423:             ->from($this->getEntityTable(), 'entity_id')
424:             ->where('entity_id = :entity_id');
425:         $bind =  array('entity_id' => $entityId);
426: 
427:         return $this->_getReadAdapter()->fetchOne($select, $bind);
428:     }
429: 
430:     /**
431:      * Check array of category identifiers
432:      *
433:      * @param array $ids
434:      * @return array
435:      */
436:     public function verifyIds(array $ids)
437:     {
438:         if (empty($ids)) {
439:             return array();
440:         }
441: 
442:         $select = $this->_getReadAdapter()->select()
443:             ->from($this->getEntityTable(), 'entity_id')
444:             ->where('entity_id IN(?)', $ids);
445: 
446:         return $this->_getReadAdapter()->fetchCol($select);
447:     }
448: 
449:     /**
450:      * Get count of active/not active children categories
451:      *
452:      * @param Mage_Catalog_Model_Category $category
453:      * @param bool $isActiveFlag
454:      * @return int
455:      */
456:     public function getChildrenAmount($category, $isActiveFlag = true)
457:     {
458:         $storeId = Mage::app()->getStore()->getId();
459:         $attributeId = $this->_getIsActiveAttributeId();
460:         $table   = $this->getTable(array($this->getEntityTablePrefix(), 'int'));
461:         $adapter = $this->_getReadAdapter();
462:         $checkSql = $adapter->getCheckSql('c.value_id > 0', 'c.value', 'd.value');
463: 
464:         $bind = array(
465:             'attribute_id' => $attributeId,
466:             'store_id'     => $storeId,
467:             'active_flag'  => $isActiveFlag,
468:             'c_path'       => $category->getPath() . '/%'
469:         );
470:         $select = $adapter->select()
471:             ->from(array('m' => $this->getEntityTable()), array('COUNT(m.entity_id)'))
472:             ->joinLeft(
473:                 array('d' => $table),
474:                 'd.attribute_id = :attribute_id AND d.store_id = 0 AND d.entity_id = m.entity_id',
475:                 array()
476:             )
477:             ->joinLeft(
478:                 array('c' => $table),
479:                 "c.attribute_id = :attribute_id AND c.store_id = :store_id AND c.entity_id = m.entity_id",
480:                 array()
481:             )
482:             ->where('m.path LIKE :c_path')
483:             ->where($checkSql . ' = :active_flag');
484: 
485:         return $this->_getReadAdapter()->fetchOne($select, $bind);
486:     }
487: 
488:     /**
489:      * Get "is_active" attribute identifier
490:      *
491:      * @return int
492:      */
493:     protected function _getIsActiveAttributeId()
494:     {
495:         if ($this->_isActiveAttributeId === null) {
496:             $bind = array(
497:                 'catalog_category' => Mage_Catalog_Model_Category::ENTITY,
498:                 'is_active'        => 'is_active',
499:             );
500:             $select = $this->_getReadAdapter()->select()
501:                 ->from(array('a'=>$this->getTable('eav/attribute')), array('attribute_id'))
502:                 ->join(array('t'=>$this->getTable('eav/entity_type')), 'a.entity_type_id = t.entity_type_id')
503:                 ->where('entity_type_code = :catalog_category')
504:                 ->where('attribute_code = :is_active');
505: 
506:             $this->_isActiveAttributeId = $this->_getReadAdapter()->fetchOne($select, $bind);
507:         }
508: 
509:         return $this->_isActiveAttributeId;
510:     }
511: 
512:     /**
513:      * Return entities where attribute value is
514:      *
515:      * @param array|int $entityIdsFilter
516:      * @param Mage_Eav_Model_Entity_Attribute $attribute
517:      * @param mixed $expectedValue
518:      * @return array
519:      */
520:     public function findWhereAttributeIs($entityIdsFilter, $attribute, $expectedValue)
521:     {
522:         $bind = array(
523:             'attribute_id' => $attribute->getId(),
524:             'value'        => $expectedValue
525:         );
526:         $select = $this->_getReadAdapter()->select()
527:             ->from($attribute->getBackend()->getTable(), array('entity_id'))
528:             ->where('attribute_id = :attribute_id')
529:             ->where('value = :value')
530:             ->where('entity_id IN(?)', $entityIdsFilter);
531: 
532:         return $this->_getReadAdapter()->fetchCol($select, $bind);
533:     }
534: 
535:     /**
536:      * Get products count in category
537:      *
538:      * @param Mage_Catalog_Model_Category $category
539:      * @return int
540:      */
541:     public function getProductCount($category)
542:     {
543:         $productTable = Mage::getSingleton('core/resource')->getTableName('catalog/category_product');
544: 
545:         $select = $this->getReadConnection()->select()
546:             ->from(
547:                 array('main_table' => $productTable),
548:                 array(new Zend_Db_Expr('COUNT(main_table.product_id)'))
549:             )
550:             ->where('main_table.category_id = :category_id');
551: 
552:         $bind = array('category_id' => (int)$category->getId());
553:         $counts = $this->getReadConnection()->fetchOne($select, $bind);
554: 
555:         return intval($counts);
556:     }
557: 
558:     /**
559:      * Retrieve categories
560:      *
561:      * @param integer $parent
562:      * @param integer $recursionLevel
563:      * @param boolean|string $sorted
564:      * @param boolean $asCollection
565:      * @param boolean $toLoad
566:      * @return Varien_Data_Tree_Node_Collection|Mage_Catalog_Model_Resource_Category_Collection
567:      */
568:     public function getCategories($parent, $recursionLevel = 0, $sorted = false, $asCollection = false, $toLoad = true)
569:     {
570:         $tree = Mage::getResourceModel('catalog/category_tree');
571:         /* @var $tree Mage_Catalog_Model_Resource_Category_Tree */
572:         $nodes = $tree->loadNode($parent)
573:             ->loadChildren($recursionLevel)
574:             ->getChildren();
575: 
576:         $tree->addCollectionData(null, $sorted, $parent, $toLoad, true);
577: 
578:         if ($asCollection) {
579:             return $tree->getCollection();
580:         }
581:         return $nodes;
582:     }
583: 
584:     /**
585:      * Return parent categories of category
586:      *
587:      * @param Mage_Catalog_Model_Category $category
588:      * @return array
589:      */
590:     public function getParentCategories($category)
591:     {
592:         $pathIds = array_reverse(explode(',', $category->getPathInStore()));
593:         $categories = Mage::getResourceModel('catalog/category_collection')
594:             ->setStore(Mage::app()->getStore())
595:             ->addAttributeToSelect('name')
596:             ->addAttributeToSelect('url_key')
597:             ->addFieldToFilter('entity_id', array('in' => $pathIds))
598:             ->addFieldToFilter('is_active', 1)
599:             ->load()
600:             ->getItems();
601:         return $categories;
602:     }
603: 
604:     /**
605:      * Return parent category of current category with own custom design settings
606:      *
607:      * @param Mage_Catalog_Model_Category $category
608:      * @return Mage_Catalog_Model_Category
609:      */
610:     public function getParentDesignCategory($category)
611:     {
612:         $pathIds = array_reverse($category->getPathIds());
613:         $collection = $category->getCollection()
614:             ->setStore(Mage::app()->getStore())
615:             ->addAttributeToSelect('custom_design')
616:             ->addAttributeToSelect('custom_design_from')
617:             ->addAttributeToSelect('custom_design_to')
618:             ->addAttributeToSelect('page_layout')
619:             ->addAttributeToSelect('custom_layout_update')
620:             ->addAttributeToSelect('custom_apply_to_products')
621:             ->addFieldToFilter('entity_id', array('in' => $pathIds))
622:             ->addFieldToFilter('custom_use_parent_settings', 0)
623:             ->addFieldToFilter('level', array('neq' => 0))
624:             ->setOrder('level', 'DESC')
625:             ->load();
626:         return $collection->getFirstItem();
627:     }
628: 
629: 
630:     /**
631:      * Return child categories
632:      *
633:      * @param Mage_Catalog_Model_Category $category
634:      * @return Mage_Catalog_Model_Resource_Category_Collection
635:      */
636:     public function getChildrenCategories($category)
637:     {
638:         $collection = $category->getCollection();
639:         /* @var $collection Mage_Catalog_Model_Resource_Category_Collection */
640:         $collection->addAttributeToSelect('url_key')
641:             ->addAttributeToSelect('name')
642:             ->addAttributeToSelect('all_children')
643:             ->addAttributeToSelect('is_anchor')
644:             ->addAttributeToFilter('is_active', 1)
645:             ->addIdFilter($category->getChildren())
646:             ->setOrder('position', Varien_Db_Select::SQL_ASC)
647:             ->joinUrlRewrite()
648:             ->load();
649: 
650:         return $collection;
651:     }
652: 
653:     /**
654:      * Return children ids of category
655:      *
656:      * @param Mage_Catalog_Model_Category $category
657:      * @param boolean $recursive
658:      * @return array
659:      */
660:     public function getChildren($category, $recursive = true)
661:     {
662:         $attributeId  = (int)$this->_getIsActiveAttributeId();
663:         $backendTable = $this->getTable(array($this->getEntityTablePrefix(), 'int'));
664:         $adapter      = $this->_getReadAdapter();
665:         $checkSql     = $adapter->getCheckSql('c.value_id > 0', 'c.value', 'd.value');
666:         $bind = array(
667:             'attribute_id' => $attributeId,
668:             'store_id'     => $category->getStoreId(),
669:             'scope'        => 1,
670:             'c_path'       => $category->getPath() . '/%'
671:         );
672:         $select = $this->_getReadAdapter()->select()
673:             ->from(array('m' => $this->getEntityTable()), 'entity_id')
674:             ->joinLeft(
675:                 array('d' => $backendTable),
676:                 'd.attribute_id = :attribute_id AND d.store_id = 0 AND d.entity_id = m.entity_id',
677:                 array()
678:             )
679:             ->joinLeft(
680:                 array('c' => $backendTable),
681:                 'c.attribute_id = :attribute_id AND c.store_id = :store_id AND c.entity_id = m.entity_id',
682:                 array()
683:             )
684:             ->where($checkSql . ' = :scope')
685:             ->where($adapter->quoteIdentifier('path') . ' LIKE :c_path');
686:         if (!$recursive) {
687:             $select->where($adapter->quoteIdentifier('level') . ' <= :c_level');
688:             $bind['c_level'] = $category->getLevel() + 1;
689:         }
690: 
691:         return $adapter->fetchCol($select, $bind);
692:     }
693: 
694:     /**
695:      * Return all children ids of category (with category id)
696:      *
697:      * @param Mage_Catalog_Model_Category $category
698:      * @return array
699:      */
700:     public function getAllChildren($category)
701:     {
702:         $children = $this->getChildren($category);
703:         $myId = array($category->getId());
704:         $children = array_merge($myId, $children);
705: 
706:         return $children;
707:     }
708: 
709:     /**
710:      * Check is category in list of store categories
711:      *
712:      * @param Mage_Catalog_Model_Category $category
713:      * @return boolean
714:      */
715:     public function isInRootCategoryList($category)
716:     {
717:         $rootCategoryId = Mage::app()->getStore()->getRootCategoryId();
718: 
719:         return in_array($rootCategoryId, $category->getParentIds());
720:     }
721: 
722:     /**
723:      * Check category is forbidden to delete.
724:      * If category is root and assigned to store group return false
725:      *
726:      * @param integer $categoryId
727:      * @return boolean
728:      */
729:     public function isForbiddenToDelete($categoryId)
730:     {
731:         $select = $this->_getReadAdapter()->select()
732:             ->from($this->getTable('core/store_group'), array('group_id'))
733:             ->where('root_category_id = :root_category_id');
734:         $result = $this->_getReadAdapter()->fetchOne($select,  array('root_category_id' => $categoryId));
735: 
736:         if ($result) {
737:             return true;
738:         }
739:         return false;
740:     }
741: 
742:     /**
743:      * Get category path value by its id
744:      *
745:      * @param int $categoryId
746:      * @return string
747:      */
748:     public function getCategoryPathById($categoryId)
749:     {
750:         $select = $this->getReadConnection()->select()
751:             ->from($this->getEntityTable(), array('path'))
752:             ->where('entity_id = :entity_id');
753:         $bind = array('entity_id' => (int)$categoryId);
754: 
755:         return $this->getReadConnection()->fetchOne($select, $bind);
756:     }
757: 
758:     /**
759:      * Move category to another parent node
760:      *
761:      * @param Mage_Catalog_Model_Category $category
762:      * @param Mage_Catalog_Model_Category $newParent
763:      * @param null|int $afterCategoryId
764:      * @return Mage_Catalog_Model_Resource_Category
765:      */
766:     public function changeParent(Mage_Catalog_Model_Category $category, Mage_Catalog_Model_Category $newParent,
767:         $afterCategoryId = null)
768:     {
769:         $childrenCount  = $this->getChildrenCount($category->getId()) + 1;
770:         $table          = $this->getEntityTable();
771:         $adapter        = $this->_getWriteAdapter();
772:         $levelFiled     = $adapter->quoteIdentifier('level');
773:         $pathField      = $adapter->quoteIdentifier('path');
774: 
775:         /**
776:          * Decrease children count for all old category parent categories
777:          */
778:         $adapter->update(
779:             $table,
780:             array('children_count' => new Zend_Db_Expr('children_count - ' . $childrenCount)),
781:             array('entity_id IN(?)' => $category->getParentIds())
782:         );
783: 
784:         /**
785:          * Increase children count for new category parents
786:          */
787:         $adapter->update(
788:             $table,
789:             array('children_count' => new Zend_Db_Expr('children_count + ' . $childrenCount)),
790:             array('entity_id IN(?)' => $newParent->getPathIds())
791:         );
792: 
793:         $position = $this->_processPositions($category, $newParent, $afterCategoryId);
794: 
795:         $newPath          = sprintf('%s/%s', $newParent->getPath(), $category->getId());
796:         $newLevel         = $newParent->getLevel() + 1;
797:         $levelDisposition = $newLevel - $category->getLevel();
798: 
799:         /**
800:          * Update children nodes path
801:          */
802:         $adapter->update(
803:             $table,
804:             array(
805:                 'path' => new Zend_Db_Expr('REPLACE(' . $pathField . ','.
806:                     $adapter->quote($category->getPath() . '/'). ', '.$adapter->quote($newPath . '/').')'
807:                 ),
808:                 'level' => new Zend_Db_Expr( $levelFiled . ' + ' . $levelDisposition)
809:             ),
810:             array($pathField . ' LIKE ?' => $category->getPath() . '/%')
811:         );
812:         /**
813:          * Update moved category data
814:          */
815:         $data = array(
816:             'path'      => $newPath,
817:             'level'     => $newLevel,
818:             'position'  =>$position,
819:             'parent_id' =>$newParent->getId()
820:         );
821:         $adapter->update($table, $data, array('entity_id = ?' => $category->getId()));
822: 
823:         // Update category object to new data
824:         $category->addData($data);
825: 
826:         return $this;
827:     }
828: 
829:     /**
830:      * Process positions of old parent category children and new parent category children.
831:      * Get position for moved category
832:      *
833:      * @param Mage_Catalog_Model_Category $category
834:      * @param Mage_Catalog_Model_Category $newParent
835:      * @param null|int $afterCategoryId
836:      * @return int
837:      */
838:     protected function _processPositions($category, $newParent, $afterCategoryId)
839:     {
840:         $table          = $this->getEntityTable();
841:         $adapter        = $this->_getWriteAdapter();
842:         $positionField  = $adapter->quoteIdentifier('position');
843: 
844:         $bind = array(
845:             'position' => new Zend_Db_Expr($positionField . ' - 1')
846:         );
847:         $where = array(
848:             'parent_id = ?'         => $category->getParentId(),
849:             $positionField . ' > ?' => $category->getPosition()
850:         );
851:         $adapter->update($table, $bind, $where);
852: 
853:         /**
854:          * Prepare position value
855:          */
856:         if ($afterCategoryId) {
857:             $select = $adapter->select()
858:                 ->from($table,'position')
859:                 ->where('entity_id = :entity_id');
860:             $position = $adapter->fetchOne($select, array('entity_id' => $afterCategoryId));
861: 
862:             $bind = array(
863:                 'position' => new Zend_Db_Expr($positionField . ' + 1')
864:             );
865:             $where = array(
866:                 'parent_id = ?' => $newParent->getId(),
867:                 $positionField . ' > ?' => $position
868:             );
869:             $adapter->update($table,$bind,$where);
870:         } elseif ($afterCategoryId !== null) {
871:             $position = 0;
872:             $bind = array(
873:                 'position' => new Zend_Db_Expr($positionField . ' + 1')
874:             );
875:             $where = array(
876:                 'parent_id = ?' => $newParent->getId(),
877:                 $positionField . ' > ?' => $position
878:             );
879:             $adapter->update($table,$bind,$where);
880:         } else {
881:             $select = $adapter->select()
882:                 ->from($table,array('position' => new Zend_Db_Expr('MIN(' . $positionField. ')')))
883:                 ->where('parent_id = :parent_id');
884:             $position = $adapter->fetchOne($select, array('parent_id' => $newParent->getId()));
885:         }
886:         $position += 1;
887: 
888:         return $position;
889:     }
890: }
891: