diff --git a/application/modules/backend/controllers/CategorytaggroupController.php b/application/modules/backend/controllers/CategorytaggroupController.php new file mode 100755 index 000000000..27c07f0f5 --- /dev/null +++ b/application/modules/backend/controllers/CategorytaggroupController.php @@ -0,0 +1,150 @@ +. + **/ +class Backend_CategorytaggroupController extends Local_Controller_Action_Backend +{ + const RESULT_OK = "OK"; + const RESULT_ERROR = "ERROR"; + + /** @var Default_Model_DbTable_ProjectCategory */ + protected $_model; + + protected $_authMember; + + protected $_modelName = 'Default_Model_DbTable_ProjectCategory'; + + public function init() + { + parent::init(); + + $this->_model = new $this->_modelName(); + + $this->view->pageTitle = 'Manage Category-Taggroup'; + $this->view->author = $this->_authMember->username; + } + + public function indexAction() + { + + } + + public function updateAction() + { + + $jTableResult = array(); + try { + + //$this->_model->moveToParent((int)$this->getParam('project_category_id', null), (int)$this->getParam('parent', null)); + //$record = $this->_model->save($this->getAllParams()); + $tagsid = $this->getParam('tag_group_id', null); + $tagmodel = new Default_Model_TagGroup(); + $tagmodel->updateTagGroupsPerCategory((int)$this->getParam('project_category_id', null), $tagsid); + $jTableResult = array(); + $jTableResult['Result'] = self::RESULT_OK; + // $jTableResult['Record'] = $record->toArray(); + } catch (Exception $e) { + Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); + $translate = Zend_Registry::get('Zend_Translate'); + $jTableResult['Result'] = self::RESULT_ERROR; + $jTableResult['Message'] = $translate->_('Error while processing data.'); + } + + $this->_helper->json($jTableResult); + } + + + public function listAction() + { + + $startIndex = (int)$this->getParam('jtStartIndex'); + $pageSize = (int)$this->getParam('jtPageSize'); + $sorting = $this->getParam('jtSorting'); + $filter_deleted = (int)$this->getParam('filter_deleted', 1); + + $records = $this->_model->fetchTreeWithParentIdAndTagGroups($filter_deleted, null); + + $pagination = Zend_Paginator::factory($records); + $pagination->setItemCountPerPage($pageSize); + $pagination->setCurrentPageNumber(($startIndex / $pageSize) + 1); + + $jTableResult = array(); + $jTableResult['Result'] = self::RESULT_OK; + $jTableResult['Records'] = (array)$pagination->getCurrentItems(); + $jTableResult['TotalRecordCount'] = count($records); + + $this->_helper->json($jTableResult); + } + + + + public function treeAction() + { + + $result = true; + $cat_id = (int)$this->getParam('c'); + + try { + $records = $this->_model->fetchTreeForJTableStores($cat_id); + + + } catch (Exception $e) { + Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); + $result = false; + $records = array(); + } + + $jTableResult = array(); + $jTableResult['Result'] = ($result == true) ? self::RESULT_OK : self::RESULT_ERROR; + $jTableResult['Options'] = $records; + + $this->_helper->json($jTableResult); + } + + + public function alltaggroupsAction() + { + + $result = true; + $tagmodel = new Default_Model_TagGroup(); + try { + $resultRows = $tagmodel->fetchAllGroups(); + $resultForSelect = array(); + foreach ($resultRows as $row) { + $resultForSelect[] = array('DisplayText' => $row['group_name'].'['.$row['group_id'].']', 'Value' => $row['group_id']); + } + + } catch (Exception $e) { + Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); + $result = false; + $records = array(); + } + + $jTableResult = array(); + $jTableResult['Result'] = ($result == true) ? self::RESULT_OK : self::RESULT_ERROR; + $jTableResult['Options'] = $resultForSelect; + + $this->_helper->json($jTableResult); + } + + + +} \ No newline at end of file diff --git a/application/modules/backend/views/scripts/categorytaggroup/index.phtml b/application/modules/backend/views/scripts/categorytaggroup/index.phtml new file mode 100644 index 000000000..5aeb8c200 --- /dev/null +++ b/application/modules/backend/views/scripts/categorytaggroup/index.phtml @@ -0,0 +1,282 @@ +. + **/ +?> + + + + + +
+ getCurrentMessages() as $message) : ?> +

escape($message); ?>

+ + + + + + +
+ +
+
+ hide deleted records: +
+ + +
+
+
+ +
+ + + + + + \ No newline at end of file diff --git a/application/modules/default/models/DbTable/ProjectCategory.php b/application/modules/default/models/DbTable/ProjectCategory.php index 7ccd9008f..15e41af0a 100644 --- a/application/modules/default/models/DbTable/ProjectCategory.php +++ b/application/modules/default/models/DbTable/ProjectCategory.php @@ -1,1540 +1,1602 @@ . **/ class Default_Model_DbTable_ProjectCategory extends Local_Model_Table { const CATEGORY_ACTIVE = 1; const CATEGORY_INACTIVE = 0; const CATEGORY_NOT_DELETED = 0; const CATEGORY_DELETED = 1; const ORDERED_TITLE = 'title'; const ORDERED_ID = 'project_category_id'; const ORDERED_HIERARCHIC = 'lft'; protected $_keyColumnsForRow = array('project_category_id'); protected $_key = 'project_category_id'; /** * @var string */ protected $_name = "project_category"; /** * @var array */ protected $_dependentTables = array('Default_Model_DbTable_Project'); /** * @var array */ protected $_referenceMap = array( 'Category' => array( 'columns' => 'project_category_id', 'refTableClass' => 'Default_Model_Project', 'refColumns' => 'project_category_id' ) ); /** @var Zend_Cache_Core */ protected $cache; /** * @inheritDoc */ public function init() { parent::init(); // TODO: Change the autogenerated stub $this->cache = Zend_Registry::get('cache'); } /** * @return array * @deprecated */ public function getSelectList() { $selectArr = $this->_db->fetchAll('SELECT `project_category_id`, `title` FROM `project_category` WHERE `is_active`=1 AND `is_deleted`=0 ORDER BY `orderPos`'); $arrayModified = array(); $arrayModified[0] = "ProjectAddFormCatSelect"; foreach ($selectArr as $item) { $arrayModified[$item['project_category_id']] = stripslashes($item['title']); } return $arrayModified; } /** * @return array * @deprecated */ public function getInternSelectList() { $selectArr = $this->_db->fetchAll('SELECT `project_category_id`, `title` FROM `project_category` WHERE `is_deleted`=0 ORDER BY `orderPos`'); $arrayModified = array(); $arrayModified[0] = "ProjectAddFormCatSelect"; foreach ($selectArr as $item) { $arrayModified[$item['project_category_id']] = stripslashes($item['title']); } return $arrayModified; } /** * @param $status * @param $id * */ public function setStatus($status, $id) { $updateValues = array( 'is_active' => $status, 'changed_at' => new Zend_Db_Expr('Now()') ); $this->update($updateValues, 'project_category_id=' . $id); } /** * @param $id * */ public function setDelete($id) { $updateValues = array( 'is_active' => 0, 'is_deleted' => 1, 'deleted_at' => new Zend_Db_Expr('Now()') ); $this->update($updateValues, 'project_category_id=' . $id); } /** * @return Zend_Db_Table_Rowset_Abstract * @throws Zend_Cache_Exception * @deprecated */ public function fetchAllActive() { $cache = $this->cache; $cacheName = __FUNCTION__; if (!($categories = $cache->load($cacheName))) { $q = $this->select()->where('is_active = ?', 1)->where('is_deleted = ?', 0)->order('orderPos'); $categories = $this->fetchAll($q); $cache->save($categories, $cacheName); } return $categories; } /** * @param int|array $nodeId * * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchActive($nodeId) { $str = is_array($nodeId) ? implode(',', $nodeId) : $nodeId; /** @var Zend_Cache_Core $cache */ $cache = $this->cache; $cacheName = __FUNCTION__ . '_' . md5($str); if (false === ($active = $cache->load($cacheName))) { $inQuery = '?'; if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); } $sql = "SELECT *, (SELECT `project_category_id` FROM `project_category` AS `t2` WHERE `t2`.`lft` < `node`.`lft` AND `t2`.`rgt` > `node`.`rgt` AND `t2`.`is_deleted` = 0 ORDER BY `t2`.`rgt`-`node`.`rgt` ASC LIMIT 1) AS `parent` FROM {$this->_name} as node WHERE project_category_id IN ($inQuery) AND is_active = 1 "; $active = $this->_db->query($sql, $nodeId)->fetchAll(); if (count($active) == 0) { $active = array(); } $cache->save($active, $cacheName, array(), 3600); } return $active; } /** * @param int|array $nodeId * * @return array * @throws Zend_Db_Statement_Exception */ public function fetchActiveOrder($nodeId) { $inQuery = '?'; if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); } $sql = "SELECT *, (SELECT `project_category_id` FROM `project_category` AS `t2` WHERE `t2`.`lft` < `node`.`lft` AND `t2`.`rgt` > `node`.`rgt` AND `t2`.`is_deleted` = 0 ORDER BY `t2`.`rgt`-`node`.`rgt`ASC LIMIT 1) AS `parent` FROM {$this->_name} as node WHERE project_category_id IN ($inQuery) AND is_active = 1 "; $active = $this->_db->query($sql, $nodeId)->fetchAll(); if (count($active)) { return $active; } else { return array(); } } /* ------------------------ */ /* New Nested Set Functions */ /* ------------------------ */ public function setCategoryDeleted($id, $updateChildren = true) { $node = $this->findCategory($id); if (count($node->toArray()) == 0) { return false; } $this->_db->beginTransaction(); try { $this->_db->query("UPDATE {$this->_name} SET is_active = 0, is_deleted = 1, deleted_at = :del_date WHERE project_category_id = :cat_id;", array('cat_id' => $id, 'del_date'=>new Zend_Db_Expr('Now()'))); if ($updateChildren) { $this->_db->query("UPDATE {$this->_name} SET is_active = 0, is_deleted = 1, deleted_at = :del_date WHERE lft > :parent_lft AND rgt < :parent_rgt;", array('del_date'=>new Zend_Db_Expr('Now()'), 'parent_lft' => $node->lft, 'parent_rgt' => $node->rgt)); } $this->_db->commit(); } catch (Exception $e) { $this->_db->rollBack(); Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); } return $node; } /** * @param $title * * @return null|Zend_Db_Table_Row_Abstract * @throws Zend_Exception */ public function appendNewElement($title) { $root = $this->fetchRoot(); $data['rgt'] = $root->rgt - 1; $data['title'] = $title; return $this->addNewElement($data); } /** * @return null|Zend_Db_Table_Row_Abstract */ public function fetchRoot() { return $this->fetchRow('`lft` = 0'); } /** * @param array $data * * @return null|Zend_Db_Table_Row_Abstract * @throws Zend_Exception */ public function addNewElement($data) { $this->_db->beginTransaction(); try { $this->_db->query("UPDATE {$this->_name} SET rgt = rgt + 2 WHERE rgt > :param_right;", array('param_right' => $data['rgt'])); $this->_db->query("UPDATE {$this->_name} SET lft = lft + 2 WHERE lft > :param_right;", array('param_right' => $data['rgt'])); $this->_db->query(" INSERT INTO project_category (`lft`, `rgt`, `title`, `is_active`, `name_legacy`, `xdg_type`, `dl_pling_factor`, `show_description`, `source_required`) VALUES (:param_right + 1, :param_right + 2, :param_title, :param_status, :param_legacy, :param_xgd, :param_pling, :param_show_desc, :param_source);", array( 'param_right' => $data['rgt'], 'param_title' => $data['title'], 'param_status' => $data['is_active'], 'param_legacy' => $data['name_legacy'], 'param_xgd' => $data['xdg_type'], 'param_show_desc' => $data['show_description'], 'param_source' => $data['source_required'], 'param_pling' => $data['dl_pling_factor'] )); $this->_db->commit(); } catch (Exception $e) { $this->_db->rollBack(); Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); } return $this->fetchRow('lft = ' . ($data['rgt'] + 1)); } /** * @param $cat_id * * @return array */ public function fetchTreeForJTable($cat_id) { $resultRows = $this->fetchTree(false, true, 5); $resultForSelect = array(); foreach ($resultRows as $row) { if (($row['project_category_id'] == $cat_id) OR ($row['parent'] == $cat_id)) { continue; } $resultForSelect[] = array('DisplayText' => $row['title_show'], 'Value' => $row['project_category_id']); } return $resultForSelect; } /** * @param bool $isActive * @param bool $withRoot * @param int $depth * * @return array * @internal param int $pageSize * @internal param int $startIndex * @internal param bool $clearCache */ public function fetchTree( $isActive = false, $withRoot = true, $depth = null ) { $sqlActive = $isActive == true ? " parent_active = 1 AND pc.is_active = 1" : ''; $sqlRoot = $withRoot == true ? "(pc.lft BETWEEN pc2.lft AND pc2.rgt)" : "(pc.lft BETWEEN pc2.lft AND pc2.rgt) AND pc2.lft > 0"; $sqlDepth = is_null($depth) == true ? '' : " AND depth <= " . (int)$depth; $sqlHaving = $sqlActive || $sqlDepth ? "HAVING {$sqlActive} {$sqlDepth}" : ''; $sql = " SELECT `pc`.`project_category_id`, `pc`.`lft`, `pc`.`rgt`, `pc`.`title`, `pc`.`name_legacy`, `pc`.`is_active`, `pc`.`orderPos`, `pc`.`xdg_type`, `pc`.`dl_pling_factor`, `pc`.`show_description`, `pc`.`source_required`, MIN(`pc2`.`is_active`) AS `parent_active`, concat(repeat('  ',count(`pc`.`lft`) - 1), `pc`.`title`) AS `title_show`, concat(repeat('  ',count(`pc`.`lft`) - 1), IF(LENGTH(TRIM(`pc`.`name_legacy`))>0,`pc`.`name_legacy`,`pc`.`title`)) AS `title_legacy`, count(`pc`.`lft`) - 1 AS `depth`, GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path`, GROUP_CONCAT(IF(LENGTH(TRIM(`pc2`.`name_legacy`))>0,`pc2`.`name_legacy`,`pc2`.`title`) ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path_legacy` FROM `project_category` AS `pc` JOIN `project_category` AS `pc2` ON {$sqlRoot} GROUP BY pc.lft {$sqlHaving} ORDER BY pc.lft "; $tree = $this->_db->fetchAll($sql); return $tree; } /** * @param bool $isActive * @param bool $withRoot * @param int $depth * * @return array * @internal param int $pageSize * @internal param int $startIndex * @internal param bool $clearCache */ public function fetchTreeWithParentId( $isActive = true, $depth = null ) { $sqlActive = $isActive == true ? " parent_active = 1 AND pc.is_active = 1" : ''; $sqlDepth = is_null($depth) == true ? '' : " AND depth <= " . (int)$depth; $sqlHaving = $sqlActive || $sqlDepth ? "HAVING {$sqlActive} {$sqlDepth}" : ''; $sql = " SELECT `pc`.`project_category_id`, `pc`.`lft`, `pc`.`rgt`, `pc`.`title`, `pc`.`name_legacy`, `pc`.`is_active`, `pc`.`orderPos`, `pc`.`xdg_type`, `pc`.`dl_pling_factor`, `pc`.`show_description`, `pc`.`source_required`, MIN(`pc2`.`is_active`) AS `parent_active`, concat(repeat('  ',count(`pc`.`lft`) - 1), `pc`.`title`) AS `title_show`, concat(repeat('  ',count(`pc`.`lft`) - 1), IF(LENGTH(TRIM(`pc`.`name_legacy`))>0,`pc`.`name_legacy`,`pc`.`title`)) AS `title_legacy`, count(`pc`.`lft`) - 1 AS `depth`, GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path`, GROUP_CONCAT(IF(LENGTH(TRIM(`pc2`.`name_legacy`))>0,`pc2`.`name_legacy`,`pc2`.`title`) ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path_legacy`, SUBSTRING_INDEX( GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`), ',', -1) AS `parent` FROM `project_category` AS `pc` JOIN `project_category` AS `pc2` ON (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) AND `pc2`.`project_category_id` <> `pc`.`project_category_id` GROUP BY `pc`.`lft` {$sqlHaving} ORDER BY pc.lft "; $tree = $this->_db->fetchAll($sql); return $tree; } /** * @param bool $isActive * @param bool $withRoot * @param int $depth * * @return array * @internal param int $pageSize * @internal param int $startIndex * @internal param bool $clearCache */ public function fetchTreeWithParentIdAndTags( $isActive = true, $depth = null ) { $sqlActive = $isActive == true ? " parent_active = 1 AND pc.is_active = 1" : ''; $sqlDepth = is_null($depth) == true ? '' : " AND depth <= " . (int)$depth; $sqlHaving = $sqlActive || $sqlDepth ? "HAVING {$sqlActive} {$sqlDepth}" : ''; $sql = " SELECT `pc`.`project_category_id`, `pc`.`lft`, `pc`.`rgt`, `pc`.`title`, `pc`.`name_legacy`, `pc`.`is_active`, `pc`.`orderPos`, `pc`.`xdg_type`, `pc`.`dl_pling_factor`, `pc`.`show_description`, `pc`.`source_required`, MIN(`pc2`.`is_active`) AS `parent_active`, concat(repeat('  ',count(`pc`.`lft`) - 1), `pc`.`title`) AS `title_show`, concat(repeat('  ',count(`pc`.`lft`) - 1), IF(LENGTH(TRIM(`pc`.`name_legacy`))>0,`pc`.`name_legacy`,`pc`.`title`)) AS `title_legacy`, count(`pc`.`lft`) - 1 AS `depth`, GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path`, GROUP_CONCAT(IF(LENGTH(TRIM(`pc2`.`name_legacy`))>0,`pc2`.`name_legacy`,`pc2`.`title`) ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path_legacy`, SUBSTRING_INDEX( GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`), ',', -1) AS `parent`, (SELECT GROUP_CONCAT(`tag`.`tag_name`) FROM `category_tag`,`tag` WHERE `tag`.`tag_id` = `category_tag`.`tag_id` AND `category_tag`.`category_id` = `pc`.`project_category_id` GROUP BY `category_tag`.`category_id`) AS `tags_name`, (SELECT GROUP_CONCAT(`tag`.`tag_id`) FROM `category_tag`,`tag` WHERE `tag`.`tag_id` = `category_tag`.`tag_id` AND `category_tag`.`category_id` = `pc`.`project_category_id` GROUP BY `category_tag`.`category_id`) AS `tags_id` FROM `project_category` AS `pc` JOIN `project_category` AS `pc2` ON (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) AND `pc2`.`project_category_id` <> `pc`.`project_category_id` GROUP BY `pc`.`lft` {$sqlHaving} ORDER BY pc.lft "; $tree = $this->_db->fetchAll($sql); return $tree; } + + + /** + * @param bool $isActive + * @param bool $withRoot + * @param int $depth + * + * @return array + * @internal param int $pageSize + * @internal param int $startIndex + * @internal param bool $clearCache + */ + public function fetchTreeWithParentIdAndTagGroups( + $isActive = true, + $depth = null + ) { + $sqlActive = $isActive == true ? " parent_active = 1 AND pc.is_active = 1" : ''; + $sqlDepth = is_null($depth) == true ? '' : " AND depth <= " . (int)$depth; + $sqlHaving = $sqlActive || $sqlDepth ? "HAVING {$sqlActive} {$sqlDepth}" : ''; + $sql = " + SELECT + `pc`.`project_category_id`, + `pc`.`lft`, + `pc`.`rgt`, + `pc`.`title`, + `pc`.`name_legacy`, + `pc`.`is_active`, + `pc`.`orderPos`, + `pc`.`xdg_type`, + `pc`.`dl_pling_factor`, + `pc`.`show_description`, + `pc`.`source_required`, + MIN(`pc2`.`is_active`) AS `parent_active`, + concat(repeat('  ',count(`pc`.`lft`) - 1), `pc`.`title`) AS `title_show`, + concat(repeat('  ',count(`pc`.`lft`) - 1), IF(LENGTH(TRIM(`pc`.`name_legacy`))>0,`pc`.`name_legacy`,`pc`.`title`)) AS `title_legacy`, + count(`pc`.`lft`) - 1 AS `depth`, + GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, + GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path`, + GROUP_CONCAT(IF(LENGTH(TRIM(`pc2`.`name_legacy`))>0,`pc2`.`name_legacy`,`pc2`.`title`) ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path_legacy`, + SUBSTRING_INDEX( GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`), ',', -1) AS `parent`, + (SELECT GROUP_CONCAT(`tag_group`.`group_name`) + FROM `category_tag_group`,`tag_group` + WHERE `tag_group`.`group_id` = `category_tag_group`.`tag_group_id` AND `category_tag_group`.`category_id` = `pc`.`project_category_id` + GROUP BY `category_tag_group`.`category_id`) AS `tag_group_name`, + (SELECT GROUP_CONCAT(`tag_group`.`group_id`) + FROM `category_tag_group`,`tag_group` + WHERE `tag_group`.`group_id` = `category_tag_group`.`tag_group_id` AND `category_tag_group`.`category_id` = `pc`.`project_category_id` + GROUP BY `category_tag_group`.`category_id`) AS `tag_group_id` + FROM + `project_category` AS `pc` + JOIN + `project_category` AS `pc2` ON (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) AND `pc2`.`project_category_id` <> `pc`.`project_category_id` + GROUP BY `pc`.`lft` + {$sqlHaving} + ORDER BY pc.lft + + "; + + $tree = $this->_db->fetchAll($sql); + + return $tree; + } /** * @param $cat_id * * @return array */ public function fetchTreeForJTableStores($cat_id) { $sql = " SELECT pc.project_category_id, pc.lft, pc.rgt, pc.title, pc.name_legacy, pc.is_active, pc.orderPos, pc.xdg_type, pc.dl_pling_factor, pc.show_description, pc.source_required, MIN(pc2.is_active) AS parent_active, concat(repeat('  ',count(pc.lft) - 1), pc.title) AS title_show, concat(repeat('  ',count(pc.lft) - 1), IF(LENGTH(TRIM(pc.name_legacy))>0,pc.name_legacy,pc.title)) AS title_legacy, count(pc.lft) - 1 AS depth, GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft) AS ancestor_id_path, GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path, GROUP_CONCAT(IF(LENGTH(TRIM(pc2.name_legacy))>0,pc2.name_legacy,pc2.title) ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path_legacy, SUBSTRING_INDEX( GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft), ',', -1) AS parent FROM project_category AS pc JOIN 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)) GROUP BY pc.lft HAVING parent_active = 1 AND pc.is_active = 1 ORDER BY pc.lft "; $resultRows = $this->_db->fetchAll($sql); $resultForSelect = array(); foreach ($resultRows as $row) { if (($row['project_category_id'] == $cat_id) OR ($row['parent'] == $cat_id)) { continue; } $resultForSelect[] = array('DisplayText' => $row['title_show'], 'Value' => $row['project_category_id']); } return $resultForSelect; } /** * @param array $node * @param int $newLeftPosition * * @return bool * @throws Zend_Exception * @deprecated use moveTo instead */ public function moveElement($node, $newLeftPosition) { $space = $node['rgt'] - $node['lft'] + 1; $distance = $newLeftPosition - $node['lft']; $srcPosition = $node['lft']; //for backwards movement, we have to fix some values if ($distance < 0) { $distance -= $space; $srcPosition += $space; } $this->_db->beginTransaction(); try { // create space for subtree $this->_db->query("UPDATE {$this->_name} SET rgt = rgt + :space WHERE rgt >= :newLeftPosition;", array('space' => $space, 'newLeftPosition' => $newLeftPosition)); $this->_db->query("UPDATE {$this->_name} SET lft = lft + :space WHERE lft >= :newLeftPosition;", array('space' => $space, 'newLeftPosition' => $newLeftPosition)); // move tree $this->_db->query("UPDATE {$this->_name} SET lft = lft + :distance, rgt = rgt + :distance WHERE lft >= :srcPosition AND rgt < :srcPosition + :space;", array('distance' => $distance, 'srcPosition' => $srcPosition, 'space' => $space)); // remove old space $this->_db->query("UPDATE {$this->_name} SET rgt = rgt - :space WHERE rgt > :srcPosition;", array('space' => $space, 'srcPosition' => $srcPosition)); $this->_db->query("UPDATE {$this->_name} SET lft = lft - :space WHERE lft >= :srcPosition;", array('space' => $space, 'srcPosition' => $srcPosition)); // move it $this->_db->commit(); } catch (Exception $e) { $this->_db->rollBack(); Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); return false; } return true; } public function findAncestor($data) { $resultRow = $this->fetchRow("rgt = {$data['lft']} - 1"); if (($resultRow->rgt - $resultRow->lft) > 1) { $resultRow = $this->fetchRow("lft = {$resultRow->lft} - 2"); } return $resultRow; } /** * @param $data * * @return array|null * @throws Zend_Db_Statement_Exception * @throws Zend_Db_Table_Exception */ public function findPreviousSibling($data) { $parent = $this->fetchParentForId($data); $parent_category_id = $parent->project_category_id; $sql = "SELECT node.project_category_id, node.lft, node.rgt, node.title, (SELECT `project_category_id` FROM `project_category` AS `t2` WHERE `t2`.`lft` < `node`.`lft` AND `t2`.`rgt` > `node`.`rgt` ORDER BY `t2`.`rgt`-`node`.`rgt`ASC LIMIT 1) AS `parent_category_id` FROM project_category AS node, project_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.project_category_id HAVING parent_category_id = :parent_category_id ORDER BY node.lft"; $siblings = $this->_db->query($sql, array('parent_category_id' => $parent_category_id))->fetchAll(); $resultRow = null; $bufferRow = null; foreach ($siblings as $row) { if ($row['project_category_id'] != $data['project_category_id']) { $bufferRow = $row; continue; } $resultRow = $bufferRow; } return $resultRow; } /** * @param $data * * @return Zend_Db_Table_Row_Abstract * @throws Zend_Db_Statement_Exception * @throws Zend_Db_Table_Exception */ public function fetchParentForId($data) { $sql = " SELECT `title`, (SELECT `project_category_id` FROM `project_category` AS `t2` WHERE `t2`.`lft` < `node`.`lft` AND `t2`.`rgt` > `node`.`rgt` ORDER BY `t2`.`rgt`-`node`.`rgt`ASC LIMIT 1) AS `parent` FROM `project_category` AS `node` WHERE `project_category_id` = :category_id ORDER BY (`rgt`-`lft`) DESC "; $resultRow = $this->_db->query($sql, array('category_id' => $data['project_category_id']))->fetch(); return $this->find($resultRow['parent'])->current(); } /** * @param $data * * @return array|null * @throws Zend_Db_Statement_Exception * @throws Zend_Db_Table_Exception */ public function findNextSibling($data) { $parent = $this->fetchParentForId($data); $parent_category_id = $parent->project_category_id; $sql = "SELECT node.project_category_id, node.lft, node.rgt, node.title, (SELECT `project_category_id` FROM `project_category` AS `t2` WHERE `t2`.`lft` < `node`.`lft` AND `t2`.`rgt` > `node`.`rgt` ORDER BY `t2`.`rgt`-`node`.`rgt`ASC LIMIT 1) AS `parent_category_id` FROM project_category AS node, project_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.project_category_id HAVING parent_category_id = :parent_category_id ORDER BY node.lft"; $siblings = $this->_db->query($sql, array('parent_category_id' => $parent_category_id))->fetchAll(); $resultRow = null; $found = false; foreach ($siblings as $row) { if ($found == true) { $resultRow = $row; break; } if ($row['project_category_id'] == $data['project_category_id']) { $found = true; continue; } } return $resultRow; } /** * @param $data * * @return null|Zend_Db_Table_Row_Abstract */ public function findPreviousElement($data) { $resultRow = $this->fetchRow("rgt = {$data['lft']} - 1"); if (($resultRow->rgt - $resultRow->lft) > 1) { $resultRow = $this->fetchRow("lft = {$resultRow->rgt} - 2"); } return $resultRow; } /** * @param $data * * @return null|Zend_Db_Table_Row_Abstract */ public function findNextElement($data) { $resultRow = $this->fetchRow("lft = {$data['rgt']} + 1"); if (($resultRow->rgt - $resultRow->lft) > 1) { $resultRow = $this->fetchRow("lft = {$resultRow->lft} + 2"); } return $resultRow; } /** * @param string|array $nodeId * @param array $options * * @return array * @throws Zend_Exception */ public function fetchChildTree($nodeId, $options = array()) { $clearCache = false; if (isset($options['clearCache'])) { $clearCache = $options['clearCache']; unset($options['clearCache']); } /** @var Zend_Cache_Core $cache */ $cache = $this->cache; $cacheName = __FUNCTION__ . '_' . md5(serialize($nodeId) . serialize($options)); if ($clearCache) { $cache->remove($cacheName); } if (!($tree = $cache->load($cacheName))) { $extSqlWhereActive = " AND o.is_active = 1"; if (isset($options['isActive']) AND $options['isActive'] == false) { $extSqlWhereActive = ''; } $extSqlHavingDepth = ''; if (isset($options['depth'])) { $extSqlHavingDepth = " HAVING depth <= " . (int)$options['depth']; } $inQuery = '?'; if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); } $sql = "SELECT `o`.*, COUNT(`p`.`project_category_id`)-1 AS `depth`, CONCAT( REPEAT( '  ', (COUNT(`p`.`title`) - 1) ), `o`.`title`) AS `title_show`, `pc`.`product_counter` FROM `project_category` AS `n` INNER JOIN `project_category` AS `p` INNER JOIN `project_category` AS `o` LEFT JOIN (SELECT `project`.`project_category_id`, count(`project`.`project_category_id`) AS `product_counter` FROM `project` WHERE `project`.`status` = 100 AND `project`.`type_id` = 1 GROUP BY `project`.`project_category_id`) AS `pc` ON `pc`.`project_category_id` = `o`.`project_category_id` WHERE `o`.`lft` BETWEEN `p`.`lft` AND `p`.`rgt` AND `o`.`lft` BETWEEN `n`.`lft` AND `n`.`rgt` AND `n`.`project_category_id` IN ({$inQuery}) AND `o`.`lft` > `p`.`lft` AND `o`.`lft` > `n`.`lft` {$extSqlWhereActive} GROUP BY o.lft {$extSqlHavingDepth} ORDER BY o.lft; ; "; $tree = $this->_db->query($sql, $nodeId)->fetchAll(); $cache->save($tree, $cacheName); } return $tree; } /** * @param int|array $nodeId * @param bool $isActive * * @return array Set of subnodes * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchChildElements($nodeId, $isActive = true) { if (is_null($nodeId) OR $nodeId == '') { return array(); } /** @var Zend_Cache_Core $cache */ $cache = $this->cache; $cacheName = __FUNCTION__ . '_' . md5(serialize($nodeId) . (int)$isActive); if (($children = $cache->load($cacheName))) { return $children; } $inQuery = '?'; if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); } $whereActive = $isActive == true ? ' AND o.is_active = 1' : ''; $sql = " SELECT o.*, COUNT(p.project_category_id)-2 AS depth FROM project_category AS n, project_category AS p, project_category AS o WHERE o.lft BETWEEN p.lft AND p.rgt AND o.lft BETWEEN n.lft AND n.rgt AND n.project_category_id IN ({$inQuery}) {$whereActive} GROUP BY o.lft HAVING depth > 0 ORDER BY o.lft; "; $children = $this->_db->query($sql, $nodeId)->fetchAll(); $cache->save($children, $cacheName); if (count($children)) { return $children; } else { return array(); } } /** * @param int|array $nodeId * @param bool $isActive * * @return array Set of subnodes * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchChildIds($nodeId, $isActive = true) { if (empty($nodeId) OR $nodeId == '') { return array(); } /** @var Zend_Cache_Core $cache */ $cache = $this->cache; $cacheName = __FUNCTION__ . '_' . md5(serialize($nodeId) . (int)$isActive); if (false !== ($children = $cache->load($cacheName))) { return $children; } $inQuery = '?'; if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); } $whereActive = $isActive == true ? ' AND o.is_active = 1' : ''; $sql = " SELECT o.project_category_id FROM project_category AS n, project_category AS p, project_category AS o WHERE o.lft BETWEEN p.lft AND p.rgt AND o.lft BETWEEN n.lft AND n.rgt AND n.project_category_id IN ({$inQuery}) {$whereActive} GROUP BY o.lft HAVING COUNT(p.project_category_id)-2 > 0 ORDER BY o.lft; "; $children = $this->_db->query($sql, $nodeId)->fetchAll(); if (count($children)) { $result = $this->flattenArray($children); $result = $this->removeUnnecessaryValues($nodeId, $result); $cache->save($result, $cacheName); return $result; } else { return array(); } } /** * * @flatten multi-dimensional array * * @param array $array * * @return array * */ private function flattenArray(array $array) { $ret_array = array(); foreach (new RecursiveIteratorIterator(new RecursiveArrayIterator($array)) as $value) { $ret_array[] = $value; } return $ret_array; } /** * @param array $nodeId * @param array $children * * @return array */ private function removeUnnecessaryValues($nodeId, $children) { $nodeId = is_array($nodeId) ? $nodeId : array($nodeId); return array_diff($children, $nodeId); } /** * @param $nodeId * @param string $orderBy * * @return array * @throws Zend_Db_Statement_Exception */ public function fetchImmediateChildrenIds($nodeId, $orderBy = self::ORDERED_HIERARCHIC) { $sql = " SELECT `node`.`project_category_id` FROM `project_category` AS `node` WHERE `node`.`is_active` = 1 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) = ? ORDER BY `node`.`{$orderBy}`; "; $children = $this->_db->query($sql, $nodeId)->fetchAll(Zend_Db::FETCH_NUM); if (count($children)) { return $this->flattenArray($children); } else { return array(); } } /** * @param Zend_Db_Table_Row $first * @param Zend_Db_Table_Row $second * * @return \Zend_Db_Table_Row * @throws Zend_Exception * @deprecated */ public function switchElements($first, $second) { $bufferLeft = $first->lft; $bufferRight = $first->rgt; $this->_db->beginTransaction(); try { $this->_db->query("UPDATE {$this->_name} SET rgt = {$second->rgt} WHERE project_category_id = {$first->project_category_id};"); $this->_db->query("UPDATE {$this->_name} SET lft = {$second->lft} WHERE project_category_id = {$first->project_category_id};"); $this->_db->query("UPDATE {$this->_name} SET rgt = {$bufferRight} WHERE project_category_id = {$second->project_category_id};"); $this->_db->query("UPDATE {$this->_name} SET lft = {$bufferLeft} WHERE project_category_id = {$second->project_category_id};"); $this->_db->commit(); } catch (Exception $e) { $this->_db->rollBack(); Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); } $first->refresh(); return $first; } /** * @param int $returnAmount * @param int $fetchLimit * * @return array|false|mixed */ public function fetchMainCategories($returnAmount = 25, $fetchLimit = 25) { $categories = $this->fetchTree(true, false, 1); return array_slice($categories, 0, $returnAmount); } /** * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchMainCatIdsOrdered() { /** @var Zend_Cache_Core $cache */ $cache = $this->cache; $cacheName = __FUNCTION__; if (($returnValue = $cache->load($cacheName))) { return $returnValue; } $sql = " SELECT `node`.`project_category_id` FROM `project_category` AS `node` INNER JOIN `project_category` AS `parent` WHERE `node`.`lft` BETWEEN `parent`.`lft` AND `parent`.`rgt` AND `node`.`is_active` = 1 AND `node`.`is_deleted` = 0 AND `node`.`lft` > 0 GROUP BY `node`.`project_category_id` HAVING (COUNT(`parent`.`title`) - 1) = 1 ORDER BY `node`.`orderPos`, `node`.`lft`; "; $result = $this->_db->query($sql)->fetchAll(Zend_Db::FETCH_NUM); if (count($result) > 0) { $returnValue = $this->flattenArray($result); $cache->save($returnValue, $cacheName, array(), 900); return $returnValue; } else { return array(); } } /** * @return array * @throws Zend_Db_Statement_Exception */ public function fetchMainCatsOrdered() { $sql = " SELECT node.project_category_id, node.title, node.lft, node.rgt FROM project_category AS node INNER JOIN project_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.is_active = 1 AND node.is_deleted = 0 AND node.lft > 0 GROUP BY node.project_category_id HAVING (COUNT(parent.title) - 1) = 1 ORDER BY node.orderPos, node.lft; "; $result = $this->_db->query($sql)->fetchAll(); if (count($result) > 0) { return $result; } else { return array(); } } /** * @param int $cat_id * @param string $orderBy * * @return array * @throws Zend_Db_Statement_Exception */ public function fetchSubCatIds($cat_id, $orderBy = self::ORDERED_HIERARCHIC) { $sql = " SELECT node.project_category_id FROM project_category AS node INNER JOIN project_category AS parent WHERE parent.project_category_id IN (:cat_id) -- AND node.lft BETWEEN parent.lft AND parent.rgt AND node.lft > parent.lft AND node.rgt < parent.rgt AND node.is_active = 1 AND node.is_deleted = 0 AND node.lft > 0 GROUP BY node.project_category_id ORDER BY node.`{$orderBy}` ; "; $result = $this->_db->query($sql, array('cat_id' => $cat_id))->fetchAll(Zend_Db::FETCH_NUM); if (count($result) > 0) { // array_shift($result); return $this->flattenArray($result); } else { return array(); } } /** * @param int $returnAmount * @param int $fetchLimit * * @return array */ public function fetchRandomCategories($returnAmount = 5, $fetchLimit = 25) { $categories = $this->fetchTree(true, false, 1); return $this->_array_random($categories, $returnAmount); } /** * @param array $categories * @param int $count * * @return array */ protected function _array_random($categories, $count = 1) { shuffle($categories); return array_slice($categories, 0, $count); } /** * @param int $currentNodeId * @param int $newParentNodeId * @param string $position * * @return bool * @throws Zend_Db_Statement_Exception * @throws Zend_Db_Table_Exception * @throws Zend_Exception */ public function moveToParent($currentNodeId, $newParentNodeId, $position = 'top') { if ($currentNodeId <= 0) { return false; } $currentNode = $this->fetchElement($currentNodeId); $currentParentNode = $this->fetchParentForId($currentNode); if ($newParentNodeId == $currentParentNode->project_category_id) { return false; } $newParentNode = $this->fetchElement($newParentNodeId); if ($position == 'top') { return $this->moveTo($currentNode, $newParentNode['lft'] + 1); } else { return $this->moveTo($currentNode, $newParentNode['rgt']); // move to bottom otherwise } } /** * @param int $nodeId * * @return array Returns Element as array or (if empty) an array with empty values * @throws Zend_Db_Table_Exception */ public function fetchElement($nodeId) { if (is_null($nodeId) OR $nodeId == '') { return $this->createRow(); } $currentNode = $this->find($nodeId)->current(); if ($currentNode === null) { $resultValue = $this->createRow()->toArray(); } else { $resultValue = $currentNode->toArray(); } return $resultValue; } /** * @param array $node complete node data * @param int $newLeftPosition new left position for the node * * @return bool * @throws Zend_Exception */ public function moveTo($node, $newLeftPosition) { $space = $node['rgt'] - $node['lft'] + 1; $distance = $newLeftPosition - $node['lft']; $srcPosition = $node['lft']; //for backwards movement, we have to fix some values if ($distance < 0) { $distance -= $space; $srcPosition += $space; } $this->_db->beginTransaction(); try { // create space for subtree $this->_db->query("UPDATE {$this->_name} SET lft = lft + :space WHERE lft >= :newLeftPosition;", array('space' => $space, 'newLeftPosition' => $newLeftPosition)); $this->_db->query("UPDATE {$this->_name} SET rgt = rgt + :space WHERE rgt >= :newLeftPosition;", array('space' => $space, 'newLeftPosition' => $newLeftPosition)); // move tree $this->_db->query("UPDATE {$this->_name} SET lft = lft + :distance, rgt = rgt + :distance WHERE lft >= :srcPosition AND rgt < :srcPosition + :space;", array('distance' => $distance, 'srcPosition' => $srcPosition, 'space' => $space)); // remove old space $this->_db->query("UPDATE {$this->_name} SET rgt = rgt - :space WHERE rgt > :srcPosition;", array('space' => $space, 'srcPosition' => $srcPosition)); $this->_db->query("UPDATE {$this->_name} SET lft = lft - :space WHERE lft >= :srcPosition;", array('space' => $space, 'srcPosition' => $srcPosition)); // move it $this->_db->commit(); } catch (Exception $e) { $this->_db->rollBack(); Zend_Registry::get('logger')->err(__METHOD__ . ' - ' . print_r($e, true)); return false; } return true; } /** * @param $productId * * @return array */ public function fetchMainCategoryForProduct($productId) { $sql = "SELECT `pc`.`project_category_id`, `pc`.`title` FROM `project_category` AS `pc` JOIN `project` AS `p` ON `p`.`project_category_id` = `pc`.`project_category_id` WHERE `p`.`project_id` = :projectId ;"; return $this->_db->fetchAll($sql, array('projectId' => $productId)); } /** * @param $productId * * @return array * @deprecated */ public function fetchAllCategoriesForProduct($productId) { $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 FROM project AS p JOIN project_category AS pc ON p.project_category_id = pc.project_category_id 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 WHERE p.project_id = :projectId "; return $this->_db->fetchAll($sql, array('projectId' => $productId)); } /** * @param int $cat_id * * @return int|string * @throws Zend_Db_Table_Exception */ public function countSubCategories($cat_id) { $cat = $this->findCategory($cat_id); $countSubCat = (int)$cat->rgt - (int)$cat->lft - 1; if ($countSubCat < 0) { return 0; } else { return $countSubCat; } } /** * @param int $nodeId * * @return Zend_Db_Table_Row_Abstract * @throws Zend_Db_Table_Exception */ public function findCategory($nodeId) { if (is_null($nodeId) OR $nodeId == '') { return $this->createRow(); } $result = $this->find($nodeId); if (count($result) > 0) { return $result->current(); } else { return $this->createRow(); } } /** * @param $valueCatId * * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchCategoriesForForm($valueCatId) { $level = 0; $ancestors = array("catLevel-{$level}" => $this->fetchMainCatForSelect(Default_Model_DbTable_ProjectCategory::ORDERED_TITLE)); $level++; if (false == empty($valueCatId)) { $categoryAncestors = $this->fetchAncestorsAsId($valueCatId); if ($categoryAncestors) { $categoryPath = explode(',', $categoryAncestors['ancestors']); foreach ($categoryPath as $element) { $ancestors["catLevel-{$level}"] = $this->prepareDataForFormSelect($this->fetchImmediateChildren($element, Default_Model_DbTable_ProjectCategory::ORDERED_TITLE)); $level++; } } } return $ancestors; } /** * @param string $orderBy * * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchMainCatForSelect($orderBy = self::ORDERED_HIERARCHIC) { $root = $this->fetchRoot(); $resultRows = $this->fetchImmediateChildren($root['project_category_id'], $orderBy); $resultForSelect = $this->prepareDataForFormSelect($resultRows); return $resultForSelect; } /** * @param int|array $nodeId * @param string $orderBy * * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchImmediateChildren($nodeId, $orderBy = 'lft') { $str = is_array($nodeId) ? implode(',', $nodeId) : $nodeId; /** @var Zend_Cache_Core $cache */ $cache = $this->cache; $cacheName = __FUNCTION__ . '_' . md5($str . $orderBy); if (false === ($children = $cache->load($cacheName))) { $inQuery = '?'; if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); } $sql = ' 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 FROM project_category AS node WHERE node.is_active = 1 HAVING parent IN (' . $inQuery . ') ORDER BY node.' . $orderBy . ' '; $children = $this->_db->query($sql, $nodeId)->fetchAll(); if (count($children) == 0) { $children = array(); } $cache->save($children, $cacheName, array(), 3600); } return $children; } /** * @param $resultRows * * @return array */ protected function prepareDataForFormSelect($resultRows) { $resultForSelect = array(); //$resultForSelect[''] = ''; foreach ($resultRows as $row) { $resultForSelect[$row['project_category_id']] = $row['title']; } return $resultForSelect; } /** * @param $catId * * @return array|mixed */ public function fetchAncestorsAsId($catId) { $sql = ' SELECT node.title, GROUP_CONCAT(parent.project_category_id ORDER BY parent.lft) AS ancestors FROM project_category AS node LEFT JOIN project_category AS parent ON parent.lft < node.lft AND parent.rgt > node.rgt AND parent.lft > 0 WHERE node.project_category_id = :categoryId GROUP BY node.project_category_id HAVING ancestors IS NOT NULL '; $result = $this->_db->fetchRow($sql, array('categoryId' => $catId)); if ($result AND count($result) > 0) { return $result; } else { return array(); } } /** * @param $resultRows * * @return array */ protected function prepareDataForFormSelectWithTitleKey($resultRows) { $resultForSelect = array(); //$resultForSelect[''] = ''; foreach ($resultRows as $row) { $resultForSelect[$row['title']] = $row['project_category_id']; } return $resultForSelect; } /** * @deprecated */ protected function initLocalCache() { $frontendOptions = array( 'lifetime' => 3600, 'automatic_serialization' => true ); $backendOptions = array( 'cache_dir' => APPLICATION_CACHE, 'file_locking' => true, 'read_control' => true, 'read_control_type' => 'adler32', // default 'crc32' 'hashed_directory_level' => 0, 'hashed_directory_perm' => 0700, 'file_name_prefix' => 'app', 'cache_file_perm' => 700 ); $this->cache = Zend_Cache::factory( 'Core', 'File', $frontendOptions, $backendOptions ); } } \ No newline at end of file diff --git a/application/modules/default/models/TagGroup.php b/application/modules/default/models/TagGroup.php index 3f880b097..9ea8bbe0d 100644 --- a/application/modules/default/models/TagGroup.php +++ b/application/modules/default/models/TagGroup.php @@ -1,177 +1,206 @@ . * * Created: 13.09.2017 */ class Default_Model_TagGroup { /** * @inheritDoc */ public function __construct() { } public function fetchGroupHierarchy() { $sql = " SELECT tag_group.group_name, tag.tag_id, tag.tag_name FROM tag_group_item JOIN tag_group ON tag_group.group_id = tag_group_item.tag_group_id JOIN tag ON tag.tag_id = tag_group_item.tag_id "; $resultSet = $this->getAdapter()->fetchAll($sql); $optgroup = array(); foreach ($resultSet as $item) { $optgroup[$item['group_name']][$item['tag_id']] = $item['tag_name']; } return $optgroup; } + + public function fetchAllGroups() + { + $sql = " + SELECT tag_group.group_name, tag_group.group_id + FROM tag_group + "; + $resultSet = $this->getAdapter()->fetchAll($sql); + + return $resultSet; + } /** * @return Zend_Db_Adapter_Abstract */ private function getAdapter() { return Zend_Db_Table::getDefaultAdapter(); } /** * @param int $group_id * * @return array */ public function fetchGroupItems($group_id) { $sql = "SELECT tag_group_item.tag_group_item_id , tag_group_item.tag_group_id , tag.tag_id, tag.tag_name , tag.tag_fullname , tag.tag_description , tag.is_active FROM tag_group_item JOIN tag ON tag.tag_id = tag_group_item.tag_id WHERE tag_group_id = :group_id"; $resultSet = $this->getAdapter()->fetchAll($sql, array('group_id' => $group_id)); return $resultSet; } /** * @param int $group_id * @param string $tag_name * * @return array */ public function assignGroupTag($group_id, $tag_name,$tag_fullname, $tag_description,$is_active=1) { $tag_id = $this->saveTag($tag_name,$tag_fullname, $tag_description,$is_active); $group_tag_id = $this->saveGroupTag($group_id, $tag_id); $resultSet = $this->fetchOneGroupItem($group_tag_id); return $resultSet; } /** * @param string $tag_name * * @return int */ public function saveTag($tag_name,$tag_fullname, $tag_description,$is_active=1) { $tag_name = strtolower($tag_name); $sql = "SELECT tag_id FROM tag WHERE tag_name = :tagName"; $resultSet = $this->getAdapter()->fetchRow($sql, array('tagName' => $tag_name)); if (empty($resultSet)) { $this->getAdapter()->insert('tag', array('tag_name' => $tag_name, 'tag_fullname' => $tag_fullname, 'tag_description' => $tag_description,'is_active' => $is_active)); $resultId = $this->getAdapter()->lastInsertId(); } else { $resultId = $resultSet['tag_id']; } return $resultId; } /** * @param int $group_id * @param int $tag_id * * @return int */ public function saveGroupTag($group_id, $tag_id) { $sql = "SELECT tag_group_item_id FROM tag_group_item WHERE tag_group_id = :group_id AND tag_id = :tag_id"; $resultSet = $this->getAdapter()->fetchRow($sql, array('group_id' => $group_id, 'tag_id' => $tag_id)); if (empty($resultSet)) { $this->getAdapter()->insert('tag_group_item', array('tag_group_id' => $group_id, 'tag_id' => $tag_id)); $resultId = $this->getAdapter()->lastInsertId(); } else { $resultId = $resultSet['tag_group_item_id']; } return $resultId; } /** * @param int $group_item_id * * @return array|false */ public function fetchOneGroupItem($group_item_id) { $sql = "SELECT tag_group_item.tag_group_item_id , tag_group_item.tag_group_id , tag.tag_id, tag.tag_name , tag.tag_fullname , tag.tag_description , tag.is_active FROM tag_group_item JOIN tag ON tag.tag_id = tag_group_item.tag_id WHERE tag_group_item_id = :group_item_id"; $resultSet = $this->getAdapter()->fetchRow($sql, array('group_item_id' => $group_item_id)); return $resultSet; } public function updateGroupTag($tag_id, $tag_name,$tag_fullname, $tag_description,$is_active=1) { $updateValues = array( 'tag_name' =>$tag_name, 'tag_fullname' => $tag_fullname, 'tag_description' => $tag_description, 'is_active' => $is_active ); $this->getAdapter()->update('tag', $updateValues, array('tag_id = ?' => $tag_id)); } public function deleteGroupTag($groupItemId) { $this->getAdapter()->delete('tag_group_item', array('tag_group_item_id = ?' => $groupItemId)); } + + + public function updateTagGroupsPerCategory($cat_id,$taggroups) + { + $sql = "delete from category_tag_group where category_id=:cat_id"; + $this->getAdapter()->query($sql, array('cat_id' => $cat_id)); + + if($taggroups){ + $taggroup_id =explode(',', $taggroups); + $prepared_insert = + array_map(function ($id) use ($cat_id) { return "({$cat_id},{$id})"; }, + $taggroup_id); + $sql = "INSERT IGNORE INTO category_tag_group (category_id, tag_group_id) VALUES " . implode(',', + $prepared_insert); + + $this->getAdapter()->query($sql); + } + } } \ No newline at end of file diff --git a/application/modules/default/plugins/AclRules.php b/application/modules/default/plugins/AclRules.php index 11b143fd7..fc1a57f7e 100644 --- a/application/modules/default/plugins/AclRules.php +++ b/application/modules/default/plugins/AclRules.php @@ -1,339 +1,340 @@ . **/ class Default_Plugin_AclRules extends Zend_Acl { const ROLENAME_GUEST = 'guest'; const ROLENAME_COOKIEUSER = 'cookieuser'; const ROLENAME_FEUSER = 'feuser'; const ROLENAME_MODERATOR = 'moderator'; const ROLENAME_STAFF = 'staff'; const ROLENAME_ADMIN = 'admin'; const ROLENAME_SYSUSER = 'sysuser'; function __construct() { $this->addRole(new Zend_Acl_Role (self::ROLENAME_GUEST)); $this->addRole(new Zend_Acl_Role (self::ROLENAME_COOKIEUSER), self::ROLENAME_GUEST); $this->addRole(new Zend_Acl_Role (self::ROLENAME_FEUSER), self::ROLENAME_COOKIEUSER); $this->addRole(new Zend_Acl_Role (self::ROLENAME_MODERATOR), self::ROLENAME_FEUSER); $this->addRole(new Zend_Acl_Role (self::ROLENAME_STAFF), self::ROLENAME_FEUSER); $this->addRole(new Zend_Acl_Role (self::ROLENAME_ADMIN)); $this->addRole(new Zend_Acl_Role (self::ROLENAME_SYSUSER)); $this->addResource(new Zend_Acl_Resource ('default_logout')); $this->addResource(new Zend_Acl_Resource ('default_oauth')); $this->addResource(new Zend_Acl_Resource ('default_authorization')); $this->addResource(new Zend_Acl_Resource ('default_button')); $this->addResource(new Zend_Acl_Resource ('default_categories')); $this->addResource(new Zend_Acl_Resource ('default_community')); $this->addResource(new Zend_Acl_Resource ('default_content')); $this->addResource(new Zend_Acl_Resource ('default_discovery')); $this->addResource(new Zend_Acl_Resource ('default_donationlist')); $this->addResource(new Zend_Acl_Resource ('default_support')); $this->addResource(new Zend_Acl_Resource ('default_error')); $this->addResource(new Zend_Acl_Resource ('default_explore')); $this->addResource(new Zend_Acl_Resource ('default_gateway')); $this->addResource(new Zend_Acl_Resource ('default_hive')); $this->addResource(new Zend_Acl_Resource ('default_home')); $this->addResource(new Zend_Acl_Resource ('default_ocsv1')); // OCS API $this->addResource(new Zend_Acl_Resource ('default_embedv1')); // embed API $this->addResource(new Zend_Acl_Resource ('default_productcategory')); $this->addResource(new Zend_Acl_Resource ('default_productcomment')); $this->addResource(new Zend_Acl_Resource ('default_product')); $this->addResource(new Zend_Acl_Resource ('default_report')); $this->addResource(new Zend_Acl_Resource ('default_rectification')); $this->addResource(new Zend_Acl_Resource ('default_rss')); $this->addResource(new Zend_Acl_Resource ('default_settings')); $this->addResource(new Zend_Acl_Resource ('default_supporterbox')); $this->addResource(new Zend_Acl_Resource ('default_plingbox')); $this->addResource(new Zend_Acl_Resource ('default_user')); $this->addResource(new Zend_Acl_Resource ('default_widget')); $this->addResource(new Zend_Acl_Resource ('default_file')); $this->addResource(new Zend_Acl_Resource ('default_plings')); $this->addResource(new Zend_Acl_Resource ('default_spam')); $this->addResource(new Zend_Acl_Resource ('default_moderation')); $this->addResource(new Zend_Acl_Resource ('default_duplicates')); $this->addResource(new Zend_Acl_Resource ('default_misuse')); $this->addResource(new Zend_Acl_Resource ('default_credits')); $this->addResource(new Zend_Acl_Resource ('default_ads')); $this->addResource(new Zend_Acl_Resource ('default_dl')); $this->addResource(new Zend_Acl_Resource ('default_password')); $this->addResource(new Zend_Acl_Resource ('default_verify')); $this->addResource(new Zend_Acl_Resource ('default_login')); $this->addResource(new Zend_Acl_Resource ('default_stati')); $this->addResource(new Zend_Acl_Resource ('default_tag')); $this->addResource(new Zend_Acl_Resource ('backend_categories')); $this->addResource(new Zend_Acl_Resource ('backend_vcategories')); $this->addResource(new Zend_Acl_Resource ('backend_categorytag')); + $this->addResource(new Zend_Acl_Resource ('backend_categorytaggroup')); $this->addResource(new Zend_Acl_Resource ('backend_claim')); $this->addResource(new Zend_Acl_Resource ('backend_comments')); $this->addResource(new Zend_Acl_Resource ('backend_content')); $this->addResource(new Zend_Acl_Resource ('backend_faq')); $this->addResource(new Zend_Acl_Resource ('backend_hive')); $this->addResource(new Zend_Acl_Resource ('backend_hiveuser')); $this->addResource(new Zend_Acl_Resource ('backend_index')); $this->addResource(new Zend_Acl_Resource ('backend_mail')); $this->addResource(new Zend_Acl_Resource ('backend_member')); $this->addResource(new Zend_Acl_Resource ('backend_memberpayout')); $this->addResource(new Zend_Acl_Resource ('backend_memberpaypaladdress')); $this->addResource(new Zend_Acl_Resource ('backend_paypalvalidstatus')); $this->addResource(new Zend_Acl_Resource ('backend_payoutstatus')); $this->addResource(new Zend_Acl_Resource ('backend_operatingsystem')); $this->addResource(new Zend_Acl_Resource ('backend_project')); $this->addResource(new Zend_Acl_Resource ('backend_ranking')); $this->addResource(new Zend_Acl_Resource ('backend_reportcomments')); $this->addResource(new Zend_Acl_Resource ('backend_reportproducts')); $this->addResource(new Zend_Acl_Resource ('backend_search')); $this->addResource(new Zend_Acl_Resource ('backend_storecategories')); $this->addResource(new Zend_Acl_Resource ('backend_vstorecategories')); $this->addResource(new Zend_Acl_Resource ('backend_store')); $this->addResource(new Zend_Acl_Resource ('backend_tag')); $this->addResource(new Zend_Acl_Resource ('backend_user')); $this->addResource(new Zend_Acl_Resource ('backend_tags')); $this->addResource(new Zend_Acl_Resource ('backend_ghnsexcluded')); $this->addResource(new Zend_Acl_Resource ('backend_letteravatar')); $this->addResource(new Zend_Acl_Resource ('backend_group')); $this->addResource(new Zend_Acl_Resource ('backend_cdiscourse')); $this->addResource(new Zend_Acl_Resource ('backend_cgitlab')); $this->addResource(new Zend_Acl_Resource ('backend_cldap')); $this->addResource(new Zend_Acl_Resource ('backend_coauth')); $this->addResource(new Zend_Acl_Resource ('backend_cexport')); $this->addResource(new Zend_Acl_Resource ('statistics_data')); $this->allow(self::ROLENAME_GUEST, array( 'statistics_data' )); $this->allow(self::ROLENAME_GUEST, array( 'default_logout', 'default_authorization', 'default_button', 'default_categories', 'default_content', 'default_community', 'default_donationlist', 'default_error', 'default_explore', 'default_gateway', 'default_hive', 'default_home', 'default_ocsv1', // OCS API 'default_embedv1', // embed API 'default_productcategory', 'default_rss', 'default_supporterbox', 'default_plingbox', 'default_oauth', 'default_plings', 'default_ads', 'default_dl', 'default_stati', 'default_password', 'default_verify', 'default_login' )); $this->allow(self::ROLENAME_SYSUSER, array( 'default_authorization', 'default_button', 'default_categories', 'default_content', 'default_community', 'default_donationlist', 'default_error', 'default_explore', 'default_gateway', 'default_hive', 'default_home', 'default_ocsv1', // OCS API 'default_embedv1', // embed API 'default_productcategory', 'default_report', 'default_rss', 'default_supporterbox', 'default_plingbox', 'default_oauth', 'default_plings', 'default_ads', 'default_dl', 'default_stati', 'default_password' )); $this->allow(self::ROLENAME_COOKIEUSER, array( 'default_logout', 'default_productcomment', 'default_settings', 'default_support', 'default_tag', 'default_rectification' )); $this->allow(self::ROLENAME_STAFF, array( 'backend_index', 'backend_categories', 'backend_categorytag', 'backend_claim', 'backend_comments', 'backend_content', 'backend_store', 'backend_storecategories', 'backend_operatingsystem', 'backend_reportcomments', 'backend_reportproducts', 'backend_search', 'backend_group' )); $this->allow(self::ROLENAME_ADMIN); // resource access rights in detail $this->allow(self::ROLENAME_GUEST, 'backend_group', array('newgroup')); // resource default_product $this->allow(self::ROLENAME_GUEST, 'default_product', array( 'index', 'show', 'getupdatesajax', 'updates', 'follows', 'fetch', 'search', 'startdownload', 'ppload', 'loadratings', 'loadinstallinstruction' )); // resource default_product $this->allow(self::ROLENAME_SYSUSER, 'default_product', array( 'index', 'show', 'getupdatesajax', 'updates', 'follows', 'fetch', 'search', 'startdownload', 'ppload', 'loadratings' )); $this->allow(self::ROLENAME_COOKIEUSER, 'default_product', array( 'add', 'rating', 'follow', 'unfollow', 'plingproject', 'followproject', 'unplingproject', 'add', 'pling', 'pay', 'dwolla', 'paymentok', 'paymentcancel', 'saveproduct', 'claim' )); $this->allow(self::ROLENAME_MODERATOR, 'backend_project', array( 'doghnsexclude' )); $this->allow(self::ROLENAME_MODERATOR, 'default_moderation', array( 'index' )); $this->allow(self::ROLENAME_MODERATOR, 'default_duplicates', array( 'index' )); $this->allow(self::ROLENAME_COOKIEUSER, 'default_product', array( 'edit', 'saveupdateajax', 'deleteupdateajax', 'update', 'preview', 'delete', 'unpublish', 'publish', 'verifycode', 'makerconfig', 'addpploadfile', 'updatepploadfile', 'deletepploadfile', 'deletepploadfiles', 'updatepackagetype', 'updatearchitecture', ), new Default_Plugin_Acl_IsProjectOwnerAssertion()); // resource default_support $this->allow(self::ROLENAME_COOKIEUSER, 'default_support', array('index', 'pay', 'paymentok', 'paymentcancel')); // resource default_support $this->allow(self::ROLENAME_COOKIEUSER, 'default_report', array('comment', 'product', 'productfraud', 'productclone')); // resource default_widget $this->allow(self::ROLENAME_GUEST, 'default_widget', array('index', 'render')); $this->allow(self::ROLENAME_COOKIEUSER, 'default_widget', array('save', 'savedefault', 'config'), new Default_Plugin_Acl_IsProjectOwnerAssertion()); $this->allow(self::ROLENAME_COOKIEUSER, 'default_file', array( 'gitlink', 'link', ), new Default_Plugin_Acl_IsProjectOwnerAssertion()); // resource default_user $this->allow(self::ROLENAME_GUEST, 'default_home', array('baseurlajax','forumurlajax','blogurlajax','storenameajax','domainsajax', 'userdataajax', 'loginurlajax', 'metamenujs','metamenubundlejs','fetchforgit')); // resource default_user $this->allow(self::ROLENAME_GUEST, 'default_user', array('index', 'aboutme', 'share', 'report', 'about', 'tooltip', 'avatar', 'userdataajax')); $this->allow(self::ROLENAME_COOKIEUSER, 'default_user', array( 'follow', 'unfollow', 'settings', 'products', 'news', 'activities', 'payments', 'income', 'payout', 'plings', 'downloadhistory', 'likes' )); $this->allow(self::ROLENAME_COOKIEUSER, 'default_tag', array('filter', 'add', 'del', 'assign', 'remove')); } } diff --git a/sql_code/20181218_category_taggroup.sql b/sql_code/20181218_category_taggroup.sql new file mode 100644 index 000000000..f5184c1ec --- /dev/null +++ b/sql_code/20181218_category_taggroup.sql @@ -0,0 +1,8 @@ + +CREATE TABLE `category_tag_group` ( + `category_id` INT(11) NOT NULL, + `tag_group_id` INT(11) NOT NULL +) +COLLATE='latin1_swedish_ci' +ENGINE=InnoDB +; \ No newline at end of file