File indexing completed on 2024-06-16 05:27:02

0001 <?php
0002 
0003 /**
0004  *  ocs-webserver
0005  *
0006  *  Copyright 2016 by pling GmbH.
0007  *
0008  *    This file is part of ocs-webserver.
0009  *
0010  *    This program is free software: you can redistribute it and/or modify
0011  *    it under the terms of the GNU Affero General Public License as
0012  *    published by the Free Software Foundation, either version 3 of the
0013  *    License, or (at your option) any later version.
0014  *
0015  *    This program is distributed in the hope that it will be useful,
0016  *    but WITHOUT ANY WARRANTY; without even the implied warranty of
0017  *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
0018  *    GNU Affero General Public License for more details.
0019  *
0020  *    You should have received a copy of the GNU Affero General Public License
0021  *    along with this program.  If not, see <http://www.gnu.org/licenses/>.
0022  **/
0023 class Default_Model_DbTable_VCategory extends Local_Model_Table
0024 {
0025 
0026     const ORDERED_TITLE = 'title';
0027     const ORDERED_ID = 'v_category_id';
0028     const ORDERED_HIERARCHIC = 'lft';
0029 
0030     protected $_keyColumnsForRow = array('v_category_id','v_parent_id');
0031 
0032     protected $_key = 'v_category_id';
0033 
0034     /**
0035      * @var string
0036      */
0037     protected $_name = "v_category";
0038     /** @var  Zend_Cache_Core */
0039     protected $cache;
0040 
0041     /**
0042      * @inheritDoc
0043      */
0044     public function init()
0045     {
0046         parent::init(); // TODO: Change the autogenerated stub
0047         $this->cache = Zend_Registry::get('cache');
0048     }
0049 
0050     /**
0051      * @return array
0052      * @deprecated
0053      */
0054     public function getSelectList()
0055     {
0056         $selectArr = $this->_db->fetchAll('SELECT v_category_id, title FROM v_category ORDER BY v_category_id');
0057 
0058         $arrayModified = array();
0059 
0060         $arrayModified[0] = "ProjectAddFormCatSelect";
0061         foreach ($selectArr as $item) {
0062             $arrayModified[$item['v_category_id']] = stripslashes($item['title']);
0063         }
0064 
0065         return $arrayModified;
0066     }
0067 
0068     /**
0069      * @return array
0070      * @deprecated
0071      */
0072     public function getInternSelectList()
0073     {
0074         $selectArr = $this->_db->fetchAll('SELECT v_category_id, title FROM project_category ORDER BY v_category_id');
0075 
0076         $arrayModified = array();
0077 
0078         $arrayModified[0] = "ProjectAddFormCatSelect";
0079         foreach ($selectArr as $item) {
0080             $arrayModified[$item['v_category_id']] = stripslashes($item['title']);
0081         }
0082 
0083         return $arrayModified;
0084     }
0085     
0086     /**
0087      * @return Zend_Db_Table_Rowset_Abstract
0088      * @deprecated
0089      */
0090     public function fetchAllActive()
0091     {
0092         $cache = $this->cache;
0093         $cacheName = __FUNCTION__;
0094         if (!($categories = $cache->load($cacheName))) {
0095             $q = $this->select()
0096                 ->order('v_category_id');
0097             $categories = $this->fetchAll($q);
0098             $cache->save($categories, $cacheName);
0099         }
0100 
0101         return $categories;
0102     }
0103 
0104     /**
0105      * @param int|array $nodeId
0106      * @return array
0107      */
0108     public function fetchActive($nodeId)
0109     {
0110         $str = is_array($nodeId) ? implode(',', $nodeId) : $nodeId;
0111         /** @var Zend_Cache_Core $cache */
0112         $cache = $this->cache;
0113         $cacheName = __FUNCTION__ . '_' . md5($str);
0114 
0115         if (false === ($active = $cache->load($cacheName))) {
0116             $inQuery = '?';
0117             if (is_array($nodeId)) {
0118                 $inQuery = implode(',', array_fill(0, count($nodeId), '?'));
0119             }
0120 
0121             $sql = "SELECT *,
0122                   (SELECT
0123                       `project_category_id`
0124                        FROM
0125                          `project_category` AS `t2`
0126                        WHERE
0127                          `t2`.`lft`  < `node`.`lft` AND
0128                          `t2`.`rgt` > `node`.`rgt`
0129                          AND `t2`.`is_deleted` = 0
0130                        ORDER BY
0131                          `t2`.`rgt`-`node`.`rgt` ASC
0132                        LIMIT 1) AS `parent`
0133                 FROM {$this->_name} as node
0134                 WHERE project_category_id IN ($inQuery)
0135                 AND is_active = 1
0136                 ";
0137 
0138             $active = $this->_db->query($sql, $nodeId)->fetchAll();
0139             if (count($active) == 0) {
0140                 $active = array();
0141             }
0142             $cache->save($active, $cacheName, array(), 3600);
0143         }
0144         return $active;
0145     }
0146 
0147     /**
0148      * @param int|array $nodeId
0149      * @return array
0150      */
0151     public function fetchActiveOrder($nodeId)
0152     {
0153         $inQuery = '?';
0154         if (is_array($nodeId)) {
0155             $inQuery = implode(',', array_fill(0, count($nodeId), '?'));
0156         }
0157 
0158         $sql = "SELECT *,
0159                   (SELECT
0160                       `project_category_id`
0161                        FROM
0162                          `project_category` AS `t2`
0163                        WHERE
0164                          `t2`.`lft`  < `node`.`lft` AND
0165                          `t2`.`rgt` > `node`.`rgt`
0166                          AND `t2`.`is_deleted` = 0
0167                        ORDER BY
0168                          `t2`.`rgt`-`node`.`rgt`ASC
0169                        LIMIT
0170                          1) AS `parent`
0171                 FROM {$this->_name} as node
0172                 WHERE project_category_id IN ($inQuery)
0173                 AND is_active = 1
0174                 ";
0175 
0176         $active = $this->_db->query($sql, $nodeId)->fetchAll();
0177         if (count($active)) {
0178             return $active;
0179         } else {
0180             return array();
0181         }
0182     }
0183 
0184     /* ------------------------ */
0185     /* New Nested Set Functions */
0186     /* ------------------------ */
0187 
0188     /**
0189      * @param array $data
0190      * @return null|Zend_Db_Table_Row_Abstract
0191      */
0192     public function addNewElement($data)
0193     {
0194         $this->_db->beginTransaction();
0195         try {
0196             $this->_db->query("INSERT INTO {$this->_name} (select max(c.v_category_id)+1 as v_category_id, :param_title as title, :param_pro_cat_id as project_category_id, :param_parent as v_parent_id, NOW(), null from v_category c);",
0197                 array(
0198                     'param_title' => $data['title'],
0199                     'param_parent' => $data['v_parent_id'],
0200                     'param_pro_cat_id' => $data['project_category_id']
0201                 ));
0202             $this->_db->commit();
0203             
0204         } catch (Exception $e) {
0205             $this->_db->rollBack();
0206             Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true));
0207         }
0208 
0209         return $this->fetchRow('v_parent_id = ' . $data['v_parent_id'] . ' and title = "' . $data['title'] . '"');
0210     }
0211 
0212     /**
0213      * @return array
0214      */
0215     public function fetchTreeForJTable($cat_id)
0216     {
0217         $resultRows = $this->fetchTree(false, true, 5);
0218 
0219         $resultForSelect = array();
0220         foreach ($resultRows as $row) {
0221             if (($row['project_category_id'] == $cat_id) OR ($row['parent'] == $cat_id)) {
0222                 continue;
0223             }
0224             $resultForSelect[] = array('DisplayText' => $row['title_show'], 'Value' => $row['project_category_id']);
0225         }
0226 
0227         return $resultForSelect;
0228     }
0229 
0230     /**
0231      * @param bool $isActive
0232      * @param bool $withRoot
0233      * @param int  $depth
0234      *
0235      * @return array
0236      * @internal param int $pageSize
0237      * @internal param int $startIndex
0238      * @internal param bool $clearCache
0239      */
0240     public function fetchTree(
0241         $isActive = false,
0242         $withRoot = true,
0243         $depth = null
0244     ) {
0245         $sqlActive = $isActive == true ? " parent_active = 1 AND pc.is_active = 1" : '';
0246         $sqlRoot = $withRoot == true ? "(pc.lft BETWEEN pc2.lft AND pc2.rgt)" : "(pc.lft BETWEEN pc2.lft AND pc2.rgt) AND pc2.lft > 0";
0247         $sqlDepth = is_null($depth) == true ? '' : " AND depth <= " . (int)$depth;
0248         $sqlHaving = $sqlActive || $sqlDepth ? "HAVING {$sqlActive} {$sqlDepth}" : '';
0249         $sql = "
0250             SELECT
0251                 pc.project_category_id,
0252                 pc.lft,
0253                 pc.rgt,
0254                 pc.title,
0255                 pc.name_legacy,
0256                 pc.is_active,
0257                 pc.orderPos,
0258                 pc.xdg_type,
0259                 pc.dl_pling_factor,
0260                 pc.show_description,
0261                 MIN(pc2.is_active)                                       AS parent_active,
0262                 concat(repeat('&nbsp;&nbsp;',count(pc.lft) - 1), pc.title) AS title_show,
0263                 concat(repeat('&nbsp;&nbsp;',count(pc.lft) - 1), IF(LENGTH(TRIM(pc.name_legacy))>0,pc.name_legacy,pc.title)) AS title_legacy,
0264                 count(pc.lft) - 1                                        AS depth,
0265                 GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft)   AS ancestor_id_path,
0266                 GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path,
0267                 GROUP_CONCAT(IF(LENGTH(TRIM(pc2.name_legacy))>0,pc2.name_legacy,pc2.title) ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path_legacy
0268               FROM
0269                   project_category AS pc
0270               JOIN
0271                     project_category AS pc2 ON {$sqlRoot}
0272               GROUP BY pc.lft
0273               {$sqlHaving}
0274               ORDER BY pc.lft
0275 
0276         ";
0277 
0278         $tree = $this->_db->fetchAll($sql);
0279         return $tree;
0280     }
0281 
0282     /**
0283      * @param bool $isActive
0284      * @param bool $withRoot
0285      * @param int  $depth
0286      *
0287      * @return array
0288      * @internal param int $pageSize
0289      * @internal param int $startIndex
0290      * @internal param bool $clearCache
0291      */
0292     public function fetchTreeWithParentId(
0293         $isActive = true,
0294         $depth = null
0295     ) {
0296         
0297         /**
0298         $sql = "
0299                SELECT
0300                 vc.*,
0301                 vc2.title AS v_parent_title
0302                FROM
0303                 v_category AS vc
0304                left outer JOIN
0305                 v_category AS vc2 ON (vc.v_parent_id = vc2.v_category_id AND vc2.v_parent_id IS NULL)
0306                WHERE vc.v_parent_id IS NOT NULL
0307         ";*/
0308         $sql = "
0309                 select * from (
0310 
0311                     select      #p6.v_parent_id as parent6_id,
0312                                 #p5.v_parent_id as parent5_id,
0313                                 #p4.v_parent_id as parent4_id,
0314                                 #p3.v_parent_id as parent3_id,
0315                                 #p2.v_parent_id as parent2_id,
0316                                 #p1.v_parent_id as parent_id,
0317                                 p1.v_category_id,
0318                                 p1.project_category_id,
0319                                 p1.project_category_id as project_category_id_show,
0320                                 c.title as title_real,
0321                                 p1.v_parent_id as v_parent_id_show,
0322                                 p1.v_parent_id,
0323                                 CONCAT( 
0324                                     CASE WHEN p6.v_parent_id >= 0 then CONCAT(p6.v_parent_id,'/') else '' END, 
0325                                     CASE WHEN p5.v_parent_id >= 0 then CONCAT(p5.v_parent_id,'/') else '' END, 
0326                                     CASE WHEN p4.v_parent_id >= 0 then CONCAT(p4.v_parent_id,'/') else '' END,
0327                                     CASE WHEN p3.v_parent_id >= 0 then CONCAT(p3.v_parent_id,'/') else '' END,
0328                                     CASE WHEN p2.v_parent_id >= 0 then CONCAT(p2.v_parent_id,'/') else '' END,
0329                                     CASE WHEN p1.v_parent_id >= 0 then CONCAT(p1.v_parent_id,'/') else '' END,
0330                                     p1.v_category_id,'/'
0331                                                     ) as path,
0332                                 CONCAT( 
0333                                     CASE WHEN p6.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END, 
0334                                     CASE WHEN p5.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END, 
0335                                     CASE WHEN p4.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0336                                     CASE WHEN p3.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0337                                     CASE WHEN p2.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0338                                     CASE WHEN p1.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0339                                     p1.title
0340                                                     ) as title_show,
0341                                 p1.title
0342                     from        v_category p1
0343                     left join  project_category c on c.project_category_id = p1.project_category_id
0344                     left join   v_category p2 on p2.v_category_id = p1.v_parent_id 
0345                     left join   v_category p3 on p3.v_category_id = p2.v_parent_id 
0346                     left join   v_category p4 on p4.v_category_id = p3.v_parent_id  
0347                     left join   v_category p5 on p5.v_category_id = p4.v_parent_id  
0348                     left join   v_category p6 on p6.v_category_id = p5.v_parent_id
0349                     where       p1.v_parent_id is not null
0350                                                     AND 0 in (p1.v_parent_id, 
0351                                        p2.v_parent_id, 
0352                                        p3.v_parent_id, 
0353                                        p4.v_parent_id, 
0354                                        p5.v_parent_id, 
0355                                        p6.v_parent_id) 
0356             ) A
0357             order by path
0358             ";
0359 
0360         $tree = $this->_db->fetchAll($sql);
0361         return $tree;
0362     }
0363 
0364     public function fetchTreeForJTableStores($cat_id)
0365     {
0366         $sql = "
0367                 select v.v_category_id, v.project_category_id, v.v_parent_id, '0' as path, 'ROOT', 'ROOT' from v_category v
0368                               where v.v_category_id = 0
0369             UNION ALL 
0370 
0371                 select * from (
0372                               
0373                     select      #p6.v_parent_id as parent6_id,
0374                                 #p5.v_parent_id as parent5_id,
0375                                 #p4.v_parent_id as parent4_id,
0376                                 #p3.v_parent_id as parent3_id,
0377                                 #p2.v_parent_id as parent2_id,
0378                                 #p1.v_parent_id as parent_id,
0379                                 p1.v_category_id,
0380                                 p1.project_category_id,
0381                                 p1.v_parent_id,
0382                                 CONCAT( 
0383                                     CASE WHEN p6.v_parent_id >= 0 then CONCAT(p6.v_parent_id,'/') else '' END, 
0384                                     CASE WHEN p5.v_parent_id >= 0 then CONCAT(p5.v_parent_id,'/') else '' END, 
0385                                     CASE WHEN p4.v_parent_id >= 0 then CONCAT(p4.v_parent_id,'/') else '' END,
0386                                     CASE WHEN p3.v_parent_id >= 0 then CONCAT(p3.v_parent_id,'/') else '' END,
0387                                     CASE WHEN p2.v_parent_id >= 0 then CONCAT(p2.v_parent_id,'/') else '' END,
0388                                     CASE WHEN p1.v_parent_id >= 0 then CONCAT(p1.v_parent_id,'/') else '' END,
0389                                     p1.v_category_id,'/'
0390                                                     ) as path,
0391                                 CONCAT( 
0392                                     CASE WHEN p6.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END, 
0393                                     CASE WHEN p5.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END, 
0394                                     CASE WHEN p4.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0395                                     CASE WHEN p3.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0396                                     CASE WHEN p2.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0397                                     CASE WHEN p1.v_parent_id >= 0 then CONCAT('&nbsp;&nbsp;&nbsp;') else '' END,
0398                                     p1.title
0399                                                     ) as title_show,
0400                                 p1.title
0401                     from        v_category p1
0402                     left join   v_category p2 on p2.v_category_id = p1.v_parent_id 
0403                     left join   v_category p3 on p3.v_category_id = p2.v_parent_id 
0404                     left join   v_category p4 on p4.v_category_id = p3.v_parent_id  
0405                     left join   v_category p5 on p5.v_category_id = p4.v_parent_id  
0406                     left join   v_category p6 on p6.v_category_id = p5.v_parent_id
0407                     where       p1.v_parent_id >= 0
0408                                                     AND 0 in (p1.v_parent_id, 
0409                                        p2.v_parent_id, 
0410                                        p3.v_parent_id, 
0411                                        p4.v_parent_id, 
0412                                        p5.v_parent_id, 
0413                                        p6.v_parent_id) 
0414             ) A
0415             order by path
0416         ";
0417         $resultRows = $this->_db->fetchAll($sql);
0418 
0419         $resultForSelect = array();
0420         foreach ($resultRows as $row) {
0421             if (($row['v_category_id'] == $cat_id) OR ($row['v_parent_id'] == $cat_id)) {
0422                 continue;
0423             }
0424             $resultForSelect[] = array('DisplayText' => $row['title_show'], 'Value' => $row['v_category_id']);
0425         }
0426 
0427         return $resultForSelect;
0428     }
0429     
0430     /**
0431      * @return array
0432      */
0433     public function fetchTreeRealForJTableStores($cat_id)
0434     {
0435         $sql = "
0436                 
0437 
0438                 SELECT 
0439               null as project_category_id,
0440                     null as lft,
0441                     null as rgt,
0442                     null as title,
0443                     null as name_legacy,
0444                     null as is_active,
0445                     null as orderPos,
0446                     null as xdg_type,
0447                     null as dl_pling_factor,
0448                     null as show_description,
0449                     null as parent_active,
0450                     '' as title_show,
0451                     null as title_legacy,
0452                     null as depth,
0453                     null as ancestor_id_path,
0454                     null as ancestor_path,
0455                     null as ancestor_path_legacy,
0456                     null as parent
0457                     
0458               UNION ALL
0459 
0460                     SELECT * from (
0461               
0462                     SELECT
0463                     pc.project_category_id,
0464                     pc.lft,
0465                     pc.rgt,
0466                     pc.title,
0467                     pc.name_legacy,
0468                     pc.is_active,
0469                     pc.orderPos,
0470                     pc.xdg_type,
0471                     pc.dl_pling_factor,
0472                     pc.show_description,
0473                     MIN(pc2.is_active)                                       AS parent_active,
0474                     concat(repeat('&nbsp;&nbsp;',count(pc.lft) - 1), pc.title) AS title_show,
0475                     concat(repeat('&nbsp;&nbsp;',count(pc.lft) - 1), IF(LENGTH(TRIM(pc.name_legacy))>0,pc.name_legacy,pc.title)) AS title_legacy,
0476                     count(pc.lft) - 1                                        AS depth,
0477                     GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft)   AS ancestor_id_path,
0478                     GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path,
0479                     GROUP_CONCAT(IF(LENGTH(TRIM(pc2.name_legacy))>0,pc2.name_legacy,pc2.title) ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path_legacy,
0480                     SUBSTRING_INDEX( GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft), ',', -1) AS parent
0481                   FROM
0482                       project_category AS pc
0483                   JOIN
0484                         project_category AS pc2 ON (pc.lft BETWEEN pc2.lft AND pc2.rgt) AND (IF(pc.project_category_id <> 34,pc2.project_category_id <> pc.project_category_id,true))
0485                   GROUP BY pc.lft
0486                   HAVING parent_active = 1 AND pc.is_active = 1
0487                   ORDER BY pc.lft
0488                ) A
0489         ";
0490         $resultRows = $this->_db->fetchAll($sql);
0491 
0492         $resultForSelect = array();
0493         foreach ($resultRows as $row) {
0494             //if (($row['project_category_id'] == $cat_id) OR ($row['parent'] == $cat_id)) {
0495             //    continue;
0496             //}
0497             $resultForSelect[] = array('DisplayText' => $row['title_show'], 'Value' => $row['project_category_id']);
0498         }
0499 
0500         return $resultForSelect;
0501     }
0502 
0503     /**
0504      * @param array $node
0505      * @param int $newLeftPosition
0506      * @return bool
0507      * @throws Zend_Exception
0508      * @deprecated use moveTo instead
0509      */
0510     public function moveElement($node, $newLeftPosition)
0511     {
0512 
0513         $space = $node['rgt'] - $node['lft'] + 1;
0514         $distance = $newLeftPosition - $node['lft'];
0515         $srcPosition = $node['lft'];
0516 
0517         //for backwards movement, we have to fix some values
0518         if ($distance < 0) {
0519             $distance -= $space;
0520             $srcPosition += $space;
0521         }
0522 
0523         $this->_db->beginTransaction();
0524 
0525         try {
0526             // create space for subtree
0527             $this->_db->query("UPDATE {$this->_name} SET rgt = rgt + :space WHERE rgt >= :newLeftPosition;",
0528                 array('space' => $space, 'newLeftPosition' => $newLeftPosition));
0529             $this->_db->query("UPDATE {$this->_name} SET lft = lft + :space WHERE lft >= :newLeftPosition;",
0530                 array('space' => $space, 'newLeftPosition' => $newLeftPosition));
0531 
0532             // move tree
0533             $this->_db->query("UPDATE {$this->_name} SET lft = lft + :distance, rgt = rgt + :distance WHERE lft >= :srcPosition AND rgt < :srcPosition + :space;",
0534                 array('distance' => $distance, 'srcPosition' => $srcPosition, 'space' => $space));
0535 
0536             // remove old space
0537             $this->_db->query("UPDATE {$this->_name} SET rgt = rgt - :space WHERE rgt > :srcPosition;",
0538                 array('space' => $space, 'srcPosition' => $srcPosition));
0539             $this->_db->query("UPDATE {$this->_name} SET lft = lft - :space WHERE lft >= :srcPosition;",
0540                 array('space' => $space, 'srcPosition' => $srcPosition));
0541 
0542             // move it
0543             $this->_db->commit();
0544         } catch (Exception $e) {
0545             $this->_db->rollBack();
0546             Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true));
0547             return false;
0548         }
0549 
0550         return true;
0551 
0552     }
0553 
0554     public function findAncestor($data)
0555     {
0556         $resultRow = $this->fetchRow("rgt = {$data['lft']} - 1");
0557 
0558         if (($resultRow->rgt - $resultRow->lft) > 1) {
0559             $resultRow = $this->fetchRow("lft = {$resultRow->lft} - 2");
0560         }
0561 
0562         return $resultRow;
0563     }
0564 
0565     /**
0566      * @param $data
0567      * @return array|null
0568      */
0569     public function findPreviousSibling($data)
0570     {
0571         $parent = $this->fetchParentForId($data);
0572         $parent_category_id = $parent->project_category_id;
0573 
0574         $sql = "SELECT node.project_category_id, node.lft, node.rgt, node.title, (SELECT
0575                        `project_category_id`
0576                         FROM
0577                           `project_category` AS `t2`
0578                         WHERE
0579                           `t2`.`lft`  < `node`.`lft` AND
0580                           `t2`.`rgt` > `node`.`rgt`
0581                         ORDER BY
0582                           `t2`.`rgt`-`node`.`rgt`ASC
0583                         LIMIT
0584                           1) AS `parent_category_id`
0585                 FROM project_category AS node,
0586                      project_category AS parent
0587                 WHERE node.lft BETWEEN parent.lft AND parent.rgt
0588                 GROUP BY node.project_category_id
0589                 HAVING parent_category_id = :parent_category_id
0590                 ORDER BY node.lft";
0591 
0592         $siblings = $this->_db->query($sql, array('parent_category_id' => $parent_category_id))->fetchAll();
0593 
0594         $resultRow = null;
0595         $bufferRow = null;
0596 
0597         foreach ($siblings as $row) {
0598             if ($row['project_category_id'] != $data['project_category_id']) {
0599                 $bufferRow = $row;
0600                 continue;
0601             }
0602             $resultRow = $bufferRow;
0603         }
0604 
0605         return $resultRow;
0606     }
0607 
0608     public function fetchParentForId($data)
0609     {
0610         $sql = "
0611         SELECT title, (SELECT
0612               `project_category_id`
0613                FROM
0614                  `project_category` AS `t2`
0615                WHERE
0616                  `t2`.`lft`  < `node`.`lft` AND
0617                  `t2`.`rgt` > `node`.`rgt`
0618                ORDER BY
0619                  `t2`.`rgt`-`node`.`rgt`ASC
0620                LIMIT
0621                  1) AS `parent`
0622         FROM project_category AS node
0623         WHERE `project_category_id` = :category_id
0624         ORDER BY (rgt-lft) DESC
0625         ";
0626         $resultRow = $this->_db->query($sql, array('category_id' => $data['project_category_id']))->fetch();
0627 
0628         return $this->find($resultRow['parent'])->current();
0629     }
0630 
0631     /**
0632      * @param $data
0633      * @return array|null
0634      */
0635     public function findNextSibling($data)
0636     {
0637         $parent = $this->fetchParentForId($data);
0638         $parent_category_id = $parent->project_category_id;
0639 
0640         $sql = "SELECT node.project_category_id, node.lft, node.rgt, node.title, (SELECT
0641                        `project_category_id`
0642                         FROM
0643                           `project_category` AS `t2`
0644                         WHERE
0645                           `t2`.`lft`  < `node`.`lft` AND
0646                           `t2`.`rgt` > `node`.`rgt`
0647                         ORDER BY
0648                           `t2`.`rgt`-`node`.`rgt`ASC
0649                         LIMIT
0650                           1) AS `parent_category_id`
0651                 FROM project_category AS node,
0652                      project_category AS parent
0653                 WHERE node.lft BETWEEN parent.lft AND parent.rgt
0654                 GROUP BY node.project_category_id
0655                 HAVING parent_category_id = :parent_category_id
0656                 ORDER BY node.lft";
0657 
0658         $siblings = $this->_db->query($sql, array('parent_category_id' => $parent_category_id))->fetchAll();
0659 
0660         $resultRow = null;
0661         $found = false;
0662 
0663         foreach ($siblings as $row) {
0664             if ($found == true) {
0665                 $resultRow = $row;
0666                 break;
0667             }
0668             if ($row['project_category_id'] == $data['project_category_id']) {
0669                 $found = true;
0670                 continue;
0671             }
0672         }
0673 
0674         return $resultRow;
0675     }
0676 
0677     public function findPreviousElement($data)
0678     {
0679         $resultRow = $this->fetchRow("rgt = {$data['lft']} - 1");
0680 
0681         if (($resultRow->rgt - $resultRow->lft) > 1) {
0682             $resultRow = $this->fetchRow("lft = {$resultRow->rgt} - 2");
0683         }
0684 
0685         return $resultRow;
0686     }
0687 
0688     public function findNextElement($data)
0689     {
0690         $resultRow = $this->fetchRow("lft = {$data['rgt']} + 1");
0691 
0692         if (($resultRow->rgt - $resultRow->lft) > 1) {
0693             $resultRow = $this->fetchRow("lft = {$resultRow->lft} + 2");
0694         }
0695 
0696         return $resultRow;
0697     }
0698 
0699     /**
0700      * @param string|array $nodeId
0701      * @param array $options
0702      * @return array
0703      * @throws Zend_Exception
0704      */
0705     public function fetchChildTree($nodeId, $options = array())
0706     {
0707         $clearCache = false;
0708         if (isset($options['clearCache'])) {
0709             $clearCache = $options['clearCache'];
0710             unset($options['clearCache']);
0711         }
0712 
0713         /** @var Zend_Cache_Core $cache */
0714         $cache = $this->cache;
0715         $cacheName = __FUNCTION__ . '_' . md5(serialize($nodeId) . serialize($options));
0716 
0717         if ($clearCache) {
0718             $cache->remove($cacheName);
0719         }
0720 
0721         if (!($tree = $cache->load($cacheName))) {
0722 
0723             $extSqlWhereActive = " AND o.is_active = 1";
0724             if (isset($options['isActive']) AND $options['isActive'] == false) {
0725                 $extSqlWhereActive = '';
0726             }
0727 
0728             $extSqlHavingDepth = '';
0729             if (isset($options['depth'])) {
0730                 $extSqlHavingDepth = " HAVING depth <= " . (int)$options['depth'];
0731             }
0732 
0733             $inQuery = '?';
0734             if (is_array($nodeId)) {
0735                 $inQuery = implode(',', array_fill(0, count($nodeId), '?'));
0736             }
0737 
0738             $sql = "SELECT o.*,
0739                       COUNT(p.project_category_id)-1 AS depth,
0740                       CONCAT( REPEAT( '&nbsp;&nbsp;', (COUNT(p.title) - 1) ), o.title) AS title_show,
0741                       pc.product_counter
0742                     FROM project_category AS n
0743                     INNER JOIN project_category AS p
0744                     INNER JOIN project_category AS o
0745                     LEFT JOIN (SELECT
0746                                  project.project_category_id,
0747                                  count(project.project_category_id) AS product_counter
0748                                FROM
0749                                  project
0750                                WHERE project.status = 100 and project.type_id = 1
0751                                GROUP BY project.project_category_id) AS pc ON pc.project_category_id = o.project_category_id
0752                     WHERE o.lft BETWEEN p.lft AND p.rgt
0753                           AND o.lft BETWEEN n.lft AND n.rgt
0754                           AND n.project_category_id IN ({$inQuery})
0755                           AND o.lft > p.lft AND o.lft > n.lft
0756                           {$extSqlWhereActive}
0757                     GROUP BY o.lft
0758                     {$extSqlHavingDepth}
0759                     ORDER BY o.lft;
0760                     ;
0761                     ";
0762             $tree = $this->_db->query($sql, $nodeId)->fetchAll();
0763             $cache->save($tree, $cacheName);
0764         }
0765 
0766         return $tree;
0767     }
0768 
0769     /**
0770      * @param int|array $nodeId
0771      * @param bool $isActive
0772      * @return array Set of subnodes
0773      */
0774     public function fetchChildElements($nodeId, $isActive = true)
0775     {
0776         if (is_null($nodeId) OR $nodeId == '') {
0777             return array();
0778         }
0779 
0780         /** @var Zend_Cache_Core $cache */
0781         $cache = $this->cache;
0782         $cacheName = __FUNCTION__ . '_' . md5(serialize($nodeId) . (int)$isActive);
0783 
0784         if (($children = $cache->load($cacheName))) {
0785             return $children;
0786         }
0787 
0788         $inQuery = '?';
0789         if (is_array($nodeId)) {
0790             $inQuery = implode(',', array_fill(0, count($nodeId), '?'));
0791         }
0792         $whereActive = $isActive == true ? ' AND o.is_active = 1' : '';
0793         $sql = "
0794             SELECT o.*,
0795                    COUNT(p.project_category_id)-2 AS depth
0796                 FROM project_category AS n,
0797                      project_category AS p,
0798                      project_category AS o
0799                WHERE o.lft BETWEEN p.lft AND p.rgt
0800                  AND o.lft BETWEEN n.lft AND n.rgt
0801                  AND n.project_category_id IN ({$inQuery})
0802                  {$whereActive}
0803             GROUP BY o.lft
0804             HAVING depth > 0
0805             ORDER BY o.lft;
0806         ";
0807         $children = $this->_db->query($sql, $nodeId)->fetchAll();
0808         $cache->save($children, $cacheName);
0809         if (count($children)) {
0810             return $children;
0811         } else {
0812             return array();
0813         }
0814     }
0815 
0816     /**
0817      * @param int|array $nodeId
0818      * @param bool $isActive
0819      * @return array Set of subnodes
0820      */
0821     public function fetchChildIds($nodeId, $isActive = true)
0822     {
0823         if (empty($nodeId) OR $nodeId == '') {
0824             return array();
0825         }
0826 
0827         /** @var Zend_Cache_Core $cache */
0828         $cache = $this->cache;
0829         $cacheName = __FUNCTION__ . '_' . md5(serialize($nodeId) . (int)$isActive);
0830 
0831         if (false !== ($children = $cache->load($cacheName))) {
0832             return $children;
0833         }
0834 
0835         $inQuery = '?';
0836         if (is_array($nodeId)) {
0837             $inQuery = implode(',', array_fill(0, count($nodeId), '?'));
0838         }
0839         $whereActive = $isActive == true ? ' AND o.is_active = 1' : '';
0840         $sql = "
0841             SELECT o.project_category_id
0842                 FROM project_category AS n,
0843                      project_category AS p,
0844                      project_category AS o
0845                WHERE o.lft BETWEEN p.lft AND p.rgt
0846                  AND o.lft BETWEEN n.lft AND n.rgt
0847                  AND n.project_category_id IN ({$inQuery})
0848                  {$whereActive}
0849             GROUP BY o.lft
0850             HAVING COUNT(p.project_category_id)-2 > 0
0851             ORDER BY o.lft;
0852         ";
0853         $children = $this->_db->query($sql, $nodeId)->fetchAll();
0854         if (count($children)) {
0855             $result = $this->flattenArray($children);
0856             $result = $this->removeUnnecessaryValues($nodeId, $result);
0857             $cache->save($result, $cacheName);
0858             return $result;
0859         } else {
0860             return array();
0861         }
0862     }
0863 
0864     /**
0865      *
0866      * @flatten multi-dimensional array
0867      *
0868      * @param array $array
0869      *
0870      * @return array
0871      *
0872      */
0873     private function flattenArray(array $array)
0874     {
0875         $ret_array = array();
0876         foreach (new RecursiveIteratorIterator(new RecursiveArrayIterator($array)) as $value) {
0877             $ret_array[] = $value;
0878         }
0879         return $ret_array;
0880     }
0881 
0882     public function fetchImmediateChildrenIds($nodeId, $orderBy = self::ORDERED_HIERARCHIC)
0883     {
0884         $sql = "
0885                 SELECT node.project_category_id
0886                 FROM project_category AS node
0887                 WHERE node.is_active = 1
0888                 HAVING (SELECT parent.project_category_id FROM project_category AS parent WHERE parent.lft < node.lft AND parent.rgt > node.rgt ORDER BY parent.rgt-node.rgt LIMIT 1) = ?
0889                 ORDER BY node.`{$orderBy}`;
0890             ";
0891         $children = $this->_db->query($sql, $nodeId)->fetchAll(Zend_Db::FETCH_NUM);
0892         if (count($children)) {
0893             return $this->flattenArray($children);
0894         } else {
0895             return array();
0896         }
0897     }
0898 
0899     /**
0900      * @param Zend_Db_Table_Row $first
0901      * @param Zend_Db_Table_Row $second
0902      *
0903      * @return \Zend_Db_Table_Row
0904      * @throws Zend_Exception
0905      * @deprecated
0906      */
0907     public function switchElements($first, $second)
0908     {
0909         $bufferLeft = $first->lft;
0910         $bufferRight = $first->rgt;
0911 
0912         $this->_db->beginTransaction();
0913         try {
0914             $this->_db->query("UPDATE {$this->_name} SET rgt = {$second->rgt} WHERE project_category_id = {$first->project_category_id};");
0915             $this->_db->query("UPDATE {$this->_name} SET lft = {$second->lft} WHERE project_category_id = {$first->project_category_id};");
0916 
0917             $this->_db->query("UPDATE {$this->_name} SET rgt = {$bufferRight} WHERE project_category_id = {$second->project_category_id};");
0918             $this->_db->query("UPDATE {$this->_name} SET lft = {$bufferLeft} WHERE project_category_id = {$second->project_category_id};");
0919             $this->_db->commit();
0920         } catch (Exception $e) {
0921             $this->_db->rollBack();
0922             Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true));
0923         }
0924 
0925         $first->refresh();
0926 
0927         return $first;
0928     }
0929 
0930     /**
0931      * @param int $returnAmount
0932      * @param int $fetchLimit
0933      * @return array|false|mixed
0934      */
0935     public function fetchMainCategories($returnAmount = 25, $fetchLimit = 25)
0936     {
0937         $categories = $this->fetchTree(true, false, 1);
0938         return array_slice($categories, 0, $returnAmount);
0939     }
0940 
0941     /**
0942      * @return array
0943      */
0944     public function fetchMainCatIdsOrdered()
0945     {
0946         /** @var Zend_Cache_Core $cache */
0947         $cache = $this->cache;
0948         $cacheName = __FUNCTION__;
0949 
0950         if (($returnValue = $cache->load($cacheName))) {
0951             return $returnValue;
0952         }
0953 
0954         $sql = "
0955                 SELECT
0956                     node.project_category_id
0957                 FROM
0958                     project_category AS node
0959                 INNER JOIN
0960                     project_category AS parent
0961                 WHERE
0962                     node.lft BETWEEN parent.lft AND parent.rgt
0963                         AND node.is_active = 1
0964                         AND node.is_deleted = 0
0965                         AND node.lft > 0
0966                 GROUP BY node.project_category_id
0967                 HAVING (COUNT(parent.title) - 1) = 1
0968                 ORDER BY node.orderPos, node.lft;
0969         ";
0970 
0971         $result = $this->_db->query($sql)->fetchAll(Zend_Db::FETCH_NUM);
0972         if (count($result) > 0) {
0973             $returnValue = $this->flattenArray($result);
0974             $cache->save($returnValue, $cacheName, array(), 900);
0975             return $returnValue;
0976         } else {
0977             return array();
0978         }
0979     }
0980 
0981     /**
0982      * @return array
0983      */
0984     public function fetchMainCatsOrdered()
0985     {
0986         $sql = "
0987                 SELECT
0988                     node.project_category_id, node.title, node.lft, node.rgt
0989                 FROM
0990                     project_category AS node
0991                 INNER JOIN
0992                     project_category AS parent
0993                 WHERE
0994                     node.lft BETWEEN parent.lft AND parent.rgt
0995                         AND node.is_active = 1
0996                         AND node.is_deleted = 0
0997                         AND node.lft > 0
0998                 GROUP BY node.project_category_id
0999                 HAVING (COUNT(parent.title) - 1) = 1
1000                 ORDER BY node.orderPos, node.lft;
1001         ";
1002         $result = $this->_db->query($sql)->fetchAll();
1003         if (count($result) > 0) {
1004             return $result;
1005         } else {
1006             return array();
1007         }
1008     }
1009 
1010     /**
1011      * @param int $cat_id
1012      * @param string $orderBy
1013      * @return array
1014      */
1015     public function fetchSubCatIds($cat_id, $orderBy = self::ORDERED_HIERARCHIC)
1016     {
1017         $sql = "
1018                 SELECT
1019                     node.project_category_id
1020                 FROM
1021                     project_category AS node
1022                 INNER JOIN
1023                     project_category AS parent
1024                 WHERE
1025                     parent.project_category_id IN (:cat_id)
1026                         --  AND node.lft BETWEEN parent.lft AND parent.rgt
1027                         AND node.lft > parent.lft AND node.rgt < parent.rgt
1028                         AND node.is_active = 1
1029                         AND node.is_deleted = 0
1030                         AND node.lft > 0
1031                 GROUP BY node.project_category_id
1032                 ORDER BY node.`{$orderBy}`
1033                 ;
1034         ";
1035         $result = $this->_db->query($sql, array('cat_id' => $cat_id))->fetchAll(Zend_Db::FETCH_NUM);
1036         if (count($result) > 0) {
1037 //            array_shift($result);
1038             return $this->flattenArray($result);
1039         } else {
1040             return array();
1041         }
1042     }
1043 
1044     /**
1045      * @param int $returnAmount
1046      * @param int $fetchLimit
1047      * @return array
1048      */
1049     public function fetchRandomCategories($returnAmount = 5, $fetchLimit = 25)
1050     {
1051         $categories = $this->fetchTree(true, false, 1);
1052         return $this->_array_random($categories, $returnAmount);
1053     }
1054 
1055     /**
1056      * @param array $categories
1057      * @param int $count
1058      * @return array
1059      */
1060     protected function _array_random($categories, $count = 1)
1061     {
1062         shuffle($categories);
1063         return array_slice($categories, 0, $count);
1064     }
1065 
1066     /**
1067      * @param int $currentNodeId
1068      * @param int $newParentNodeId
1069      * @return bool
1070      */
1071     public function moveToParent($currentNodeId, $newParentNodeId, $position = 'top')
1072     {
1073         if ($currentNodeId <= 0) {
1074             return false;
1075         }
1076         $currentNode = $this->fetchElement($currentNodeId);
1077         $currentParentNode = $this->fetchParentForId($currentNode);
1078 
1079         if ($newParentNodeId == $currentParentNode->project_category_id) {
1080             return false;
1081         }
1082 
1083         $newParentNode = $this->fetchElement($newParentNodeId);
1084 
1085         if ($position == 'top') {
1086             return $this->moveTo($currentNode, $newParentNode['lft'] + 1);
1087         } else {
1088             return $this->moveTo($currentNode, $newParentNode['rgt']); // move to bottom otherwise
1089         }
1090     }
1091 
1092     /**
1093      * @param int $nodeId
1094      * @return array Returns Element as array or (if empty) an array with empty values
1095      */
1096     public function fetchElement($nodeId)
1097     {
1098         if (is_null($nodeId) OR $nodeId == '') {
1099             return $this->createRow();
1100         }
1101 
1102         $currentNode = $this->find($nodeId)->current();
1103 
1104         if ($currentNode === null) {
1105             $resultValue = $this->createRow()->toArray();
1106         } else {
1107             $resultValue = $currentNode->toArray();
1108         }
1109 
1110         return $resultValue;
1111     }
1112 
1113     /**
1114      * @param array $node complete node data
1115      * @param int $newLeftPosition new left position for the node
1116      * @return bool
1117      * @throws Zend_Exception
1118      */
1119     public function moveTo($node, $newLeftPosition)
1120     {
1121         $space = $node['rgt'] - $node['lft'] + 1;
1122         $distance = $newLeftPosition - $node['lft'];
1123         $srcPosition = $node['lft'];
1124 
1125         //for backwards movement, we have to fix some values
1126         if ($distance < 0) {
1127             $distance -= $space;
1128             $srcPosition += $space;
1129         }
1130 
1131         $this->_db->beginTransaction();
1132 
1133         try {
1134             // create space for subtree
1135             $this->_db->query("UPDATE {$this->_name} SET lft = lft + :space WHERE lft >= :newLeftPosition;",
1136                 array('space' => $space, 'newLeftPosition' => $newLeftPosition));
1137             $this->_db->query("UPDATE {$this->_name} SET rgt = rgt + :space WHERE rgt >= :newLeftPosition;",
1138                 array('space' => $space, 'newLeftPosition' => $newLeftPosition));
1139 
1140             // move tree
1141             $this->_db->query("UPDATE {$this->_name} SET lft = lft + :distance, rgt = rgt + :distance WHERE lft >= :srcPosition AND rgt < :srcPosition + :space;",
1142                 array('distance' => $distance, 'srcPosition' => $srcPosition, 'space' => $space));
1143 
1144             // remove old space
1145             $this->_db->query("UPDATE {$this->_name} SET rgt = rgt - :space WHERE rgt > :srcPosition;",
1146                 array('space' => $space, 'srcPosition' => $srcPosition));
1147             $this->_db->query("UPDATE {$this->_name} SET lft = lft - :space WHERE lft >= :srcPosition;",
1148                 array('space' => $space, 'srcPosition' => $srcPosition));
1149 
1150             // move it
1151             $this->_db->commit();
1152         } catch (Exception $e) {
1153             $this->_db->rollBack();
1154             Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true));
1155             return false;
1156         }
1157 
1158         return true;
1159     }
1160 
1161     public function fetchMainCategoryForProduct($productId)
1162     {
1163         $sql = "SELECT pc.project_category_id, pc.title
1164                 FROM project_category AS pc
1165                 JOIN project AS p ON p.project_category_id = pc.project_category_id
1166                 WHERE p.project_id = :projectId
1167                 ;";
1168         return $this->_db->fetchAll($sql, array('projectId' => $productId));
1169     }
1170 
1171     /**
1172      * @param $productId
1173      * @return array
1174      * @deprecated
1175      */
1176     public function fetchAllCategoriesForProduct($productId)
1177     {
1178         $sql = "SELECT p.project_id, pc.project_category_id AS category_id, pc.title AS category, ps.project_category_id AS sub_category_id, ps.title AS sub_category
1179                 FROM project AS p
1180                 JOIN project_category AS pc ON p.project_category_id = pc.project_category_id
1181                 LEFT JOIN (SELECT prc.project_category_id, psc.project_id, prc.title FROM project_subcategory AS psc JOIN project_category AS prc ON psc.project_sub_category_id) AS ps ON p.project_id = ps.project_id
1182                 WHERE p.project_id = :projectId
1183                 ";
1184         return $this->_db->fetchAll($sql, array('projectId' => $productId));
1185     }
1186 
1187     /**
1188      * @param int $cat_id
1189      * @return int|string
1190      */
1191     public function countSubCategories($cat_id)
1192     {
1193         $cat = $this->findCategory($cat_id);
1194 
1195         $countSubCat = (int)$cat->rgt - (int)$cat->lft - 1;
1196 
1197         if ($countSubCat < 0) {
1198             return 0;
1199         } else {
1200             return $countSubCat;
1201         }
1202     }
1203 
1204     /**
1205      * @param int $nodeId
1206      * @return Zend_Db_Table_Row_Abstract
1207      */
1208     public function findCategory($nodeId)
1209     {
1210         if (is_null($nodeId) OR $nodeId == '') {
1211             return $this->createRow();
1212         }
1213 
1214         $result = $this->find($nodeId);
1215         if (count($result) > 0) {
1216             return $result->current();
1217         } else {
1218             return $this->createRow();
1219         }
1220     }
1221 
1222     public function fetchCategoriesForForm($valueCatId)
1223     {
1224         $level = 0;
1225         $ancestors = array("catLevel-{$level}" => $this->fetchMainCatForSelect(Default_Model_DbTable_ProjectCategory::ORDERED_TITLE));
1226         $level++;
1227 
1228         if (false == empty($valueCatId)) {
1229             $categoryAncestors = $this->fetchAncestorsAsId($valueCatId);
1230             if($categoryAncestors) {
1231                 $categoryPath = explode(',', $categoryAncestors['ancestors']);
1232                 foreach ($categoryPath as $element) {
1233                     $ancestors["catLevel-{$level}"] = $this->prepareDataForFormSelect($this->fetchImmediateChildren($element,
1234                         Default_Model_DbTable_ProjectCategory::ORDERED_TITLE));
1235                     $level++;
1236                 }
1237             }
1238         }
1239 
1240         return $ancestors;
1241     }
1242 
1243     public function fetchMainCatForSelect($orderBy = self::ORDERED_HIERARCHIC)
1244     {
1245         $root = $this->fetchRoot();
1246         $resultRows = $this->fetchImmediateChildren($root['project_category_id'], $orderBy);
1247 
1248         $resultForSelect = $this->prepareDataForFormSelect($resultRows);
1249 
1250         return $resultForSelect;
1251     }
1252 
1253     /**
1254      * @param int|array $nodeId
1255      * @param string $orderBy
1256      * @return array
1257      */
1258     public function fetchImmediateChildren($nodeId, $orderBy = 'lft')
1259     {
1260         $str = is_array($nodeId) ? implode(',', $nodeId) : $nodeId;
1261         /** @var Zend_Cache_Core $cache */
1262         $cache = $this->cache;
1263         $cacheName = __FUNCTION__ . '_' . md5($str . $orderBy);
1264 
1265         if (false === ($children = $cache->load($cacheName))) {
1266             $inQuery = '?';
1267             if (is_array($nodeId)) {
1268                 $inQuery = implode(',', array_fill(0, count($nodeId), '?'));
1269             }
1270             $sql = '
1271             SELECT node.*, (SELECT parent.project_category_id FROM project_category AS parent WHERE parent.lft < node.lft AND parent.rgt > node.rgt ORDER BY parent.rgt-node.rgt LIMIT 1) AS parent
1272             FROM project_category AS node
1273             WHERE node.is_active = 1
1274             HAVING parent IN (' . $inQuery . ')
1275             ORDER BY node.' . $orderBy . '
1276             ';
1277             $children = $this->_db->query($sql, $nodeId)->fetchAll();
1278             if (count($children) == 0) {
1279                 $children = array();
1280             }
1281             $cache->save($children, $cacheName, array(), 3600);
1282         }
1283         return $children;
1284     }
1285 
1286     /**
1287      * @param $resultRows
1288      * @return array
1289      */
1290     protected function prepareDataForFormSelect($resultRows)
1291     {
1292         $resultForSelect = array();
1293         //$resultForSelect[''] = '';
1294         foreach ($resultRows as $row) {
1295             $resultForSelect[$row['project_category_id']] = $row['title'];
1296         }
1297         return $resultForSelect;
1298     }
1299 
1300     /**
1301      * @param $catId
1302      * @return array|mixed
1303      */
1304     public function fetchAncestorsAsId($catId)
1305     {
1306         $sql = '
1307         SELECT node.title, GROUP_CONCAT(parent.project_category_id ORDER BY parent.lft) AS ancestors 
1308         FROM project_category AS node
1309         LEFT JOIN project_category AS parent ON parent.lft < node.lft AND parent.rgt > node.rgt AND parent.lft > 0
1310         WHERE node.project_category_id = :categoryId
1311         GROUP BY node.project_category_id
1312         HAVING ancestors IS NOT NULL
1313         ';
1314 
1315         $result = $this->_db->fetchRow($sql, array('categoryId' => $catId));
1316 
1317         if ($result AND count($result) > 0) {
1318             return $result;
1319         } else {
1320             return array();
1321         }
1322     }
1323 
1324     /**
1325      * @param $resultRows
1326      * @return array
1327      */
1328     protected function prepareDataForFormSelectWithTitleKey($resultRows)
1329     {
1330         $resultForSelect = array();
1331         //$resultForSelect[''] = '';
1332         foreach ($resultRows as $row) {
1333             $resultForSelect[$row['title']] = $row['project_category_id'];
1334         }
1335         return $resultForSelect;
1336     }
1337 
1338     /**
1339      * @deprecated
1340      */
1341     protected function initLocalCache()
1342     {
1343         $frontendOptions = array(
1344             'lifetime' => 3600,
1345             'automatic_serialization' => true
1346         );
1347 
1348         $backendOptions = array(
1349             'cache_dir' => APPLICATION_CACHE,
1350             'file_locking' => true,
1351             'read_control' => true,
1352             'read_control_type' => 'adler32', // default 'crc32'
1353             'hashed_directory_level' => 0,
1354             'hashed_directory_perm' => 0700,
1355             'file_name_prefix' => 'app',
1356             'cache_file_perm' => 700
1357         );
1358 
1359         $this->cache = Zend_Cache::factory(
1360             'Core',
1361             'File',
1362             $frontendOptions,
1363             $backendOptions
1364         );
1365     }
1366 
1367     /**
1368      * @param array $nodeId
1369      * @param array $children
1370      *
1371      * @return array
1372      */
1373     private function removeUnnecessaryValues($nodeId, $children)
1374     {
1375         $nodeId = is_array($nodeId) ? $nodeId : array($nodeId);
1376         return array_diff($children, $nodeId);
1377     }
1378 
1379 }