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