diff --git a/application/modules/backend/controllers/SectionController.php b/application/modules/backend/controllers/SectionController.php new file mode 100644 index 000000000..8ca945ffd --- /dev/null +++ b/application/modules/backend/controllers/SectionController.php @@ -0,0 +1,173 @@ +. + **/ +class Backend_SectionController extends Local_Controller_Action_Backend +{ + + const RESULT_OK = "OK"; + const RESULT_ERROR = "ERROR"; + const DATA_ID_NAME = 'section_id'; + + /** @var Default_Model_DbTable_ConfigStore */ + protected $_model; + protected $_modelName = 'Default_Model_DbTable_Section'; + protected $_pageTitle = 'Manage Sections'; + + public function init() + { + $this->_model = new Default_Model_DbTable_Section(); + + $this->view->pageTitle = $this->_pageTitle; + + parent::init(); + } + + public function indexAction() + { + + } + + public function createAction() + { + $jTableResult = array(); + try { + $allParams = $this->getAllParams(); + $resultWalk = array_walk($allParams, function (&$value) { + $value = strlen($value) == 0 ? null : $value; + }); + if (false === $resultWalk) { + throw new Exception('array_walk through input parameters failed.'); + } + //$newRow = $this->_model->createRow($allParams); + //$result = $newRow->save(); + $newRow = $this->_model->save($allParams); + + $jTableResult['Result'] = self::RESULT_OK; + $jTableResult['Record'] = $newRow->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 updateAction() + { + $jTableResult = array(); + try { + $values = $this->getAllParams(); + + foreach ($values as $key => $value) { + if ($value == '') { + $values[$key] = new Zend_Db_Expr('NULL'); + } + } + + $record = $this->_model->save($values); + + $jTableResult = array(); + $jTableResult['Result'] = self::RESULT_OK; + } 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); + } + + protected function initCache($store_id) + { + $modelPCat = new Default_Model_ProjectCategory(); + $modelPCat->fetchCategoryTreeForSection($store_id, true); + + $this->_model->fetchAllSectionsAndCategories(true); + $this->_model->fetchAllSectionsArray(true); + } + + public function deleteAction() + { + $dataId = (int)$this->getParam(self::DATA_ID_NAME, null); + + $this->_model->deleteId($dataId); + + $this->cacheClear($dataId); + + $jTableResult = array(); + $jTableResult['Result'] = self::RESULT_OK; + + $this->_helper->json($jTableResult); + } + + protected function cacheClear($store_id) + { + /** @var Zend_Cache_Core $cache */ + $cache = Zend_Registry::get('cache'); + $cache->remove(Default_Model_ProjectCategory::CACHE_TREE_STORE . "_{$store_id}"); + $cache->remove(Default_Model_DbTable_ConfigStore::CACHE_STORE_CONFIG . "_{$store_id}"); + $this->_model->fetchAllSectionsAndCategories(true); + $this->_model->fetchAllSectionsArray(true); + } + + public function listAction() + { + $startIndex = (int)$this->getParam('jtStartIndex'); + $pageSize = (int)$this->getParam('jtPageSize'); + $sorting = $this->getParam('jtSorting'); + $filter['name'] = $this->getParam('filter_name'); + $filter['category_id'] = $this->getParam('filter_category_id'); + + $select = $this->_model->select()->from($this->_model)->order($sorting)->limit($pageSize, $startIndex)->setIntegrityCheck(false) + ; + + foreach ($filter as $key => $value) { + if (false === empty($value)) { + $select->where("{$key} like ?", $value); + } + } + + $reports = $this->_model->fetchAll($select); + + $select = $this->_model->select()->from($this->_model)->setIntegrityCheck(false); + foreach ($filter as $key => $value) { + if (false === empty($value)) { + $select->where("{$key} like ?", $value); + } + } + $reportsAll = $this->_model->fetchAll($select->limit(null, null)->reset('columns') + ->columns(array('countAll' => new Zend_Db_Expr('count(*)')))); + + $jTableResult = array(); + $jTableResult['Result'] = self::RESULT_OK; + $jTableResult['Records'] = $reports->toArray(); + $jTableResult['TotalRecordCount'] = $reportsAll->current()->countAll; + + $this->_helper->json($jTableResult); + } + + + +} \ No newline at end of file diff --git a/application/modules/backend/controllers/SectioncategoriesController.php b/application/modules/backend/controllers/SectioncategoriesController.php new file mode 100644 index 000000000..64ea051e4 --- /dev/null +++ b/application/modules/backend/controllers/SectioncategoriesController.php @@ -0,0 +1,151 @@ +. + **/ +class Backend_SectioncategoriesController 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 Section-Categories'; + $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()); + $section_id = $this->getParam('section_id', null); + $tagmodel = new Default_Model_DbTable_SectionCategory(); + $tagmodel->updateSectionPerCategory((int)$this->getParam('project_category_id', null), $section_id); + $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->fetchTreeWithParentIdAndSections($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->fetchTreeForJTableSection($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 allsectionsAction() + { + + $result = true; + $tagmodel = new Default_Model_DbTable_Section(); + try { + $resultRows = $tagmodel->fetchAll(); + $resultForSelect = array(); + $resultForSelect[] = array('DisplayText' => '', 'Value' => null); + foreach ($resultRows as $row) { + $resultForSelect[] = array('DisplayText' => $row['name'].'['.$row['section_id'].']', 'Value' => $row['section_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/section/index.phtml b/application/modules/backend/views/scripts/section/index.phtml new file mode 100644 index 000000000..cf603e190 --- /dev/null +++ b/application/modules/backend/views/scripts/section/index.phtml @@ -0,0 +1,100 @@ +. + **/ + +?> + +
+ getCurrentMessages() as $message) : ?> +

escape($message); ?>

+ +
+
+ + + diff --git a/application/modules/backend/views/scripts/sectioncategories/index.phtml b/application/modules/backend/views/scripts/sectioncategories/index.phtml new file mode 100644 index 000000000..02a09d81a --- /dev/null +++ b/application/modules/backend/views/scripts/sectioncategories/index.phtml @@ -0,0 +1,220 @@ +. + **/ +?> + + + + + +
+ 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 e04bd315e..86f48e130 100644 --- a/application/modules/default/models/DbTable/ProjectCategory.php +++ b/application/modules/default/models/DbTable/ProjectCategory.php @@ -1,1795 +1,1904 @@ . **/ 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 bool $isActive + * @param bool $withRoot + * @param int $depth + * + * @return array + * @internal param int $pageSize + * @internal param int $startIndex + * @internal param bool $clearCache + */ + public function fetchTreeWithParentIdAndSections( + $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 `section`.name + FROM `section_category`, `section` + WHERE `section`.section_id = `section_category`.section_id and `section_category`.`project_category_id` = `pc`.`project_category_id`) AS `section_name`, + (SELECT `section`.section_id + FROM `section_category`, `section` + WHERE `section`.section_id = `section_category`.section_id and `section_category`.`project_category_id` = `pc`.`project_category_id`) AS `section_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 $cat_id + * + * @return array + */ + public function fetchTreeForJTableSection($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 $cat_id * * @return array */ public function fetchTreeForCategoryStores($cat_id) { $sql = " SELECT pc.project_category_id, pc.lft, pc.rgt, pc.title, pc.is_active, MIN(pc2.is_active) AS parent_active, count(pc.lft) - 1 AS depth, 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'], '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; "; if (APPLICATION_ENV == "development") { Zend_Registry::get('logger')->debug(__METHOD__ . ' - ' . $sql . ' - ' . json_encode($nodeId)); } $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; $mainCatArray = $this->fetchMainCatForSelect(Default_Model_DbTable_ProjectCategory::ORDERED_TITLE); $ancestors = array("catLevel-{$level}" => $mainCatArray); $level++; if (false == empty($valueCatId)) { foreach (array_keys($mainCatArray) as $element) { if($element == $valueCatId) { return $ancestors; } } $categoryAncestors = $this->fetchAncestorsAsId($valueCatId); if ($categoryAncestors) { $categoryPath = explode(',', $categoryAncestors['ancestors']); foreach ($categoryPath as $element) { $catResult = $this->fetchImmediateChildren($element, Default_Model_DbTable_ProjectCategory::ORDERED_TITLE); $ancestors["catLevel-{$level}"] = $this->prepareDataForFormSelect($catResult); $level++; } } } return $ancestors; } /** * @param $valueCatId * * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchCategoriesForFormNew($valueCatId) { $level = 0; $mainCatArray = $this->fetchMainCatForSelectNew(Default_Model_DbTable_ProjectCategory::ORDERED_TITLE); $ancestors = array("catLevel-{$level}" => $mainCatArray); $level++; if (false == empty($valueCatId)) { foreach (array_keys($mainCatArray) as $element) { if($element == $valueCatId) { return $ancestors; } } $categoryAncestors = $this->fetchAncestorsAsId($valueCatId); if ($categoryAncestors) { $categoryPath = explode(',', $categoryAncestors['ancestors']); foreach ($categoryPath as $element) { $catResult = $this->fetchImmediateChildren($element, Default_Model_DbTable_ProjectCategory::ORDERED_TITLE); $ancestors["catLevel-{$level}"] = $this->prepareDataForFormSelect($catResult); $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); /* $storeCatIds = Zend_Registry::isRegistered('store_category_list') ? Zend_Registry::get('store_category_list') : null; if(null == $storeCatIds) { $root = $this->fetchRoot(); $resultRows = $this->fetchImmediateChildren($root['project_category_id'], $orderBy); } else { $resultRows = $this->fetchImmediateChildren($storeCatIds, $orderBy, false); }*/ $resultForSelect = $this->prepareDataForFormSelect($resultRows); return $resultForSelect; } /** * @param string $orderBy * * @return array * @throws Zend_Cache_Exception * @throws Zend_Db_Statement_Exception */ public function fetchMainCatForSelectNew($orderBy = self::ORDERED_HIERARCHIC) { //$root = $this->fetchRoot(); //$resultRows = $this->fetchImmediateChildrenNew($root['project_category_id'], $orderBy); $storeCatIds = Zend_Registry::isRegistered('store_category_list') ? Zend_Registry::get('store_category_list') : null; if(null == $storeCatIds) { $root = $this->fetchRoot(); $resultRows = $this->fetchImmediateChildrenNew($root['project_category_id'], $orderBy); } else { $resultRows = $this->fetchImmediateChildrenNew($storeCatIds, $orderBy, false); } $resultForSelect = $this->prepareDataForFormSelectNew($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 int|array $nodeId * @param string $orderBy * * @return array * @throws Zend_Cache_Exception * @throws Zend_Exception */ public function fetchImmediateChildrenNew($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))) { $proCatModel = new Default_Model_ProjectCategory(); $store_config = Zend_Registry::get('store_config'); $store_id = $store_config->store_id; $rows = $proCatModel->fetchTreeForView($store_id); $children = array(); if (is_array($nodeId)) { $inQuery = implode(',', array_fill(0, count($nodeId), '?')); foreach ($rows as $row) { foreach ($nodeId as $node) { if($row['id'] == $node) { $children[] = $row; } } } } else { foreach ($rows as $row) { if($row['parent_id'] == $nodeId) { $children[] = $row; } } } 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 $resultRows * * @return array */ protected function prepareDataForFormSelectNew($resultRows) { $resultForSelect = array(); //$resultForSelect[''] = ''; foreach ($resultRows as $row) { $resultForSelect[$row['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/DbTable/Section.php b/application/modules/default/models/DbTable/Section.php new file mode 100644 index 000000000..7776c2061 --- /dev/null +++ b/application/modules/default/models/DbTable/Section.php @@ -0,0 +1,231 @@ +. + **/ +class Default_Model_DbTable_Section extends Local_Model_Table +{ + + const CACHE_STORES_CATEGORIES = 'section_categories_list'; + const CACHE_STORES_CONFIGS = 'section_list'; + const CACHE_STORE_CONFIG = 'section'; + const CACHE_STORES_CONFIGS_BY_ID = 'section_id_list'; + + protected $_keyColumnsForRow = array('section_id'); + protected $_key = 'section_id'; + protected $_name = "section"; + + /** + * @param null $id + * + * @return array + * @throws Zend_Db_Select_Exception + */ + public function fetchNamesForJTable($id = null) + { + $select = $this->select()->from($this->_name)->columns('name')->group('name'); + + $resultRows = $this->fetchAll($select); + + $resultForSelect = array(); + foreach ($resultRows as $row) { + $resultForSelect[] = array('DisplayText' => $row['name'], 'Value' => $row['section_id']); + } + + return $resultForSelect; + } + + /** + * @return array + */ + public function fetchAllSectionsAndCategories($clearCache = false) + { + /** @var Zend_Cache_Core $cache */ + $cache = Zend_Registry::get('cache'); + $cacheName = self::CACHE_STORES_CATEGORIES; + + if ($clearCache) { + $cache->remove($cacheName); + } + + if (false == ($configArray = $cache->load($cacheName))) { + $resultSet = $this->queryAllSectionsAndCategories(); + $configArray = $this->createArrayAllSectionsAndCategories($resultSet); + $cache->save($configArray, $cacheName, array(), 28800); + } + + return $configArray; + } + + /** + * @return array + */ + private function queryAllSectionsAndCategories() + { + $sql = " + SELECT + `section`.`name`, + `section_category`.`section_id`, + `section_category`.`project_category_id` + FROM + `section` + JOIN + `section_category` ON `section`.`section_id` = `section_category`.`section_id` + JOIN + `project_category` ON `project_category`.`project_category_id` = `section_category`.`project_category_id` + ORDER BY `section`.`name`,`project_category`.`title`; + "; + $resultSet = $this->_db->fetchAll($sql); + + return $resultSet; + } + + /** + * @param array $resultSetConfig + * + * @return array + */ + private function createArrayAllSectionsAndCategories($resultSetConfig) + { + $result = array(); + foreach ($resultSetConfig as $element) { + $result[$element['name']][] = $element['project_category_id']; + } + array_walk($result, create_function('&$v', '$v = (count($v) == 1)? array_pop($v): $v;')); + + return $result; + } + + + public function deleteId($dataId) + { + $sql = "DELETE FROM `section` WHERE {$this->_key} = ?"; + $this->_db->query($sql, $dataId)->execute(); +// return $this->delete(array('store_id = ?' => (int)$dataId)); + } + + public function delete($where) + { + $where = parent::_whereExpr($where); + + /** + * Build the DELETE statement + */ + $sql = "UPDATE " . parent::getAdapter()->quoteIdentifier($this->_name, true) . " SET is_active = 1, `deleted_at` = NOW() " . (($where) ? " WHERE $where" : ''); + + /** + * Execute the statement and return the number of affected rows + */ + $stmt = parent::getAdapter()->query($sql); + $result = $stmt->rowCount(); + + return $result; + } + + /** + * @param bool $clearCache + * + * @return array + * @throws Zend_Cache_Exception + * @throws Zend_Exception + */ + public function fetchAllSectionsArray($clearCache = false) + { + if (Zend_Registry::isRegistered('cache')) { + /** @var Zend_Cache_Core $cache */ + $cache = Zend_Registry::get('cache'); + $cacheName = self::CACHE_STORES_CONFIGS; + + if ($clearCache) { + $cache->remove($cacheName); + } + + if (false == ($configArray = $cache->load($cacheName))) { + $resultSet = $this->querySectionArray(); + $configArray = $this->createSectionArray($resultSet); + $cache->save($configArray, $cacheName, array(), 28800); + } + } else { + $resultSet = $this->querySectionArray(); + $configArray = $this->createSectionArray($resultSet); + } + + return $configArray; + } + + /** + * @return array + */ + private function querySectionArray() + { + $sql = "SELECT * FROM `section` ORDER BY `name`;"; + $resultSet = $this->_db->fetchAll($sql); + + return $resultSet; + } + + /** + * @param array $resultSetConfig + * @param string $key + * + * @return array + */ + private function createSectionArray($resultSetConfig, $key = 'name') + { + $result = array(); + foreach ($resultSetConfig as $element) { + $result[$element[$key]] = $element; + } + + return $result; + } + + /** + * @param bool $clearCache + * + * @return array + */ + public function fetchAllSectionByIdArray($clearCache = false) + { + if (Zend_Registry::isRegistered('cache')) { + /** @var Zend_Cache_Core $cache */ + $cache = Zend_Registry::get('cache'); + $cacheName = self::CACHE_STORES_CONFIGS_BY_ID; + + if ($clearCache) { + $cache->remove($cacheName); + } + + if (false == ($configArray = $cache->load($cacheName))) { + $resultSet = $this->querySectionArray(); + $configArray = $this->createSectionArray($resultSet, 'section_id'); + $cache->save($configArray, $cacheName, array(), 28800); + } + } else { + $resultSet = $this->querySectionArray(); + $configArray = $this->createSectionArray($resultSet, 'section_id'); + } + + return $configArray; + } + + + +} \ No newline at end of file diff --git a/application/modules/default/models/DbTable/SectionCategory.php b/application/modules/default/models/DbTable/SectionCategory.php new file mode 100644 index 000000000..0b8338090 --- /dev/null +++ b/application/modules/default/models/DbTable/SectionCategory.php @@ -0,0 +1,118 @@ +. + **/ +class Default_Model_DbTable_SectionCategory extends Local_Model_Table +{ + + protected $_keyColumnsForRow = array('section_category_id'); + protected $_key = 'section_category_id'; + protected $_name = "section_store_category"; + + /** + * @param int $dataId + */ + public function deleteId($dataId) + { + $sql = "DELETE FROM {$this->_name} WHERE {$this->_key} = ?"; + $this->_db->query($sql,$dataId)->execute(); + } + + /** + * @param int $storeId + * @return array + */ + public function fetchAllCategoriesForSection($sectionId) + { + $active = Default_Model_DbTable_ProjectCategory::CATEGORY_ACTIVE; + $notDeleted = Default_Model_DbTable_ProjectCategory::CATEGORY_NOT_DELETED; + $sql = " + SELECT pc2.project_category_id + FROM project_category AS pc, project_category AS pc2 + WHERE pc.project_category_id IN (SELECT DISTINCT csc.project_category_id + FROM section_category AS csc + JOIN project_category AS pc ON csc.project_category_id = pc.project_category_id AND pc.is_active = 1 + WHERE csc.section_id = :sectionId) + AND pc2.lft BETWEEN pc.lft AND pc.rgt + AND pc2.is_active = {$active} AND pc2.is_deleted = {$notDeleted} + ORDER BY pc2.lft; + "; + $results = $this->_db->fetchAll($sql, array('sectionId' => $sectionId)); + $values = array_map(function($row) { return $row['project_category_id']; }, $results); + return $values; + } + + /** + * @param int|array $listCatId + * @return array + */ + public function fetchSectionForCatdId($listCatId) + { + $inQuery = '?'; + if (is_array($listCatId)) { + $inQuery = implode(',', array_fill(0, count($listCatId), '?')); + } + + $sql = ' + SELECT cs.section_id, cs.section_id_name + FROM section_category as csc + join section as cs on cs.section_id = csc.section_id + where csc.project_category_id in ('.$inQuery.') + '; + + $result = $this->_db->query($sql, $listCatId)->fetchAll(); + + if (count($result) > 0) { + return $result; + } else { + return array(); + } + } + + public function fetchCatIdsForSection($section_id) + { + $sql = " + SELECT csc.project_category_id + FROM section_category AS csc + JOIN project_category AS pc ON pc.project_category_id = csc.project_category_id + WHERE csc.section_id = :section_id + AND csc.deleted_at IS NULL + ORDER BY csc.`order`, pc.title + "; + $results = $this->_db->fetchAll($sql, array('section_id' => $section_id)); + $values = array_map(function($row) { return $row['project_category_id']; }, $results); + return $values; + } + + public function updateSectionPerCategory($cat_id,$section_id=null) + { + $sql = "delete from section_category where project_category_id=:cat_id"; + $this->getAdapter()->query($sql, array('cat_id' => $cat_id)); + + if(!empty($section_id)) { + $sql = "INSERT IGNORE INTO section_category (project_category_id, section_id) VALUES ($cat_id,$section_id)"; + $this->getAdapter()->query($sql); + } + + + } + + +} \ No newline at end of file diff --git a/application/modules/default/models/ProjectCategory.php b/application/modules/default/models/ProjectCategory.php index 0a16c88bc..158ac0476 100644 --- a/application/modules/default/models/ProjectCategory.php +++ b/application/modules/default/models/ProjectCategory.php @@ -1,358 +1,398 @@ . **/ class Default_Model_ProjectCategory { const CACHE_TREE_STORE = 'store_cat_tree'; + const CACHE_TREE_SECTION = 'section_cat_tree'; /** @var string */ protected $_dataTableName; /** @var Default_Model_DbTable_ProjectCategory */ protected $_dataTable; /** * PHP 5 allows developers to declare constructor methods for classes. * Classes which have a constructor method call this method on each newly-created object, * so it is suitable for any initialization that the object may need before it is used. * * Note: Parent constructors are not called implicitly if the child class defines a constructor. * In order to run a parent constructor, a call to parent::__construct() within the child constructor is required. * * param [ mixed $args [, $... ]] * * @param string $_dataTableName * * @link http://php.net/manual/en/language.oop5.decon.php */ public function __construct($_dataTableName = 'Default_Model_DbTable_ProjectCategory') { $this->_dataTableName = $_dataTableName; $this->_dataTable = new $this->_dataTableName; } /** * @param null $store_id * * @return array * @throws Zend_Cache_Exception * @throws Zend_Exception */ public function fetchTreeForView($store_id = null) { $tags = null; if (empty($store_id)) { $store_config = Zend_Registry::get('store_config'); $store_id = $store_config->store_id; $tags = Zend_Registry::isRegistered('config_store_tags') ? Zend_Registry::get('config_store_tags') : array(); } /** @var Zend_Cache_Core $cache */ $cache = Zend_Registry::get('cache'); $cache_id = __CLASS__ . '_' . __FUNCTION__ . "_{$store_id}"; $tree = $cache->load($cache_id); if (false === $tree OR empty($tree)) { try { $rows = $this->fetchCategoryTreeWithTags($store_id, $tags); } catch (Zend_Exception $e) { Zend_Registry::get('logger')->err(__METHOD__ . ' - can not fetch categories : ' . $e->getMessage()); $modelCategories = new Default_Model_DbTable_ConfigStore(); $defaultStore = $modelCategories->fetchDefaultStoreId(); $rows = $this->fetchCategoryTreeWithTags($defaultStore->store_id, $tags); } list($rows, $tree) = $this->buildTreeForView($rows); $cache->save($tree, $cache_id, array(), 600); } return $tree; } /** * @param int|null $store_id * @param string|null $tags * * @return array * @throws Zend_Exception */ protected function fetchCategoryTreeWithTags($store_id = null, $tags = null) { if (empty($store_id)) { return array(); } Zend_Registry::get('logger')->debug(__METHOD__ . ' - ' . $store_id . ' - ' . json_encode($tags)); if (empty($tags)) { $statement = $this->_dataTable->getAdapter()->query("CALL fetchCatTreeForStore(:store_id)", array("store_id" => $store_id)); } else { $statement = $this->_dataTable->getAdapter()->query("CALL fetchCatTreeWithTagsForStore(:store_id,:tagids)", array("store_id"=>$store_id, "tagids" => implode(',',$tags))); } $result = $statement->fetchAll(); if (count($result) == 0) { throw new Zend_Exception('no categories could be found for store id: ' . $store_id); } return $result; } /** * @param array $rows * @param int|null $parent_id * * @return array */ protected function buildTreeForView($rows, $parent_id = null) { $result = array(); $rememberParent = null; while (false === empty($rows)) { $row = array_shift($rows); $result_element = array( 'id' => $row['id'], 'title' => $row['title'], 'product_count' => $row['product_count'], 'xdg_type' => $row['xdg_type'], 'name_legacy' => $row['name_legacy'], 'has_children' => $row['has_children'], 'parent_id' => $row['parent_id'] ); //has children? if ($row['has_children'] == 1) { $result_element['has_children'] = true; $rememberParent = $row['id']; list($rows, $children) = $this->buildTreeForView($rows, $rememberParent); uasort($children, function ($a, $b) {return strcasecmp($a['title'], $b['title']);}); $result_element['children'] = $children; $rememberParent = null; } $result[] = $result_element; if (isset($parent_id) AND isset($rows[0]['parent_id']) AND $parent_id != $rows[0]['parent_id']) { break; } } return array($rows, $result); } /** * @param int|null $store_id If not set, the tree for the current store will be returned * @param bool $clearCache * * @return array * @throws Zend_Cache_Exception * @throws Zend_Exception * @deprecated use fetchTreeForView */ public function fetchCategoryTreeForStore($store_id = null, $clearCache = false) { if (empty($store_id)) { $store_config = Zend_Registry::get('store_config'); $store_id = $store_config->store_id; } /** @var Zend_Cache_Core $cache */ $cache = Zend_Registry::get('cache'); $cache_id = self::CACHE_TREE_STORE . "_{$store_id}"; if ($clearCache) { $cache->remove($cache_id); } if (false === ($tree = $cache->load($cache_id))) { $modelCategoryStore = new Default_Model_DbTable_ConfigStoreCategory(); $rows = $modelCategoryStore->fetchCatIdsForStore((int)$store_id); if (count($rows) < 1) { $modelCategories = new Default_Model_DbTable_ProjectCategory(); $root = $modelCategories->fetchRoot(); $rows = $modelCategories->fetchImmediateChildrenIds($root['project_category_id'], $modelCategories::ORDERED_TITLE); $tree = $this->buildTree($rows, null, null); } else { $tree = $this->buildTree($rows, null, (int)$store_id); } $cache->save($tree, $cache_id, array(), 600); } return $tree; } + + /** + * @param int|null $store_id If not set, the tree for the current store will be returned + * @param bool $clearCache + * + * @return array + * @throws Zend_Cache_Exception + * @throws Zend_Exception + * @deprecated use fetchTreeForView + */ + public function fetchCategoryTreeForSection($section_id = null, $clearCache = false) + { + /** @var Zend_Cache_Core $cache */ + $cache = Zend_Registry::get('cache'); + $cache_id = self::CACHE_TREE_SECTION . "_{$section_id}"; + + if ($clearCache) { + $cache->remove($cache_id); + } + + if (false === ($tree = $cache->load($cache_id))) { + $modelCategoryStore = new Default_Model_DbTable_SectionCategory(); + $rows = $modelCategoryStore->fetchCatIdsForSection((int)$store_id); + + if (count($rows) < 1) { + $modelCategories = new Default_Model_DbTable_ProjectCategory(); + $root = $modelCategories->fetchRoot(); + $rows = $modelCategories->fetchImmediateChildrenIds($root['project_category_id'], $modelCategories::ORDERED_TITLE); + $tree = $this->buildTree($rows, null, null); + } else { + $tree = $this->buildTree($rows, null, null); + } + + $cache->save($tree, $cache_id, array(), 600); + } + + return $tree; + } + private function buildTree($list, $parent_id = null, $store_id = null) { if (false === is_array($list)) { $list = array($list); } $modelCategories = new Default_Model_DbTable_ProjectCategory(); $result = array(); foreach ($list as $cat_id) { $currentCategory = $modelCategories->fetchElement($cat_id); $countProduct = $this->fetchProductCount($cat_id, $store_id); $result_element = array( 'id' => $cat_id, 'title' => $currentCategory['title'], 'product_count' => $countProduct, 'xdg_type' => $currentCategory['xdg_type'], 'name_legacy' => $currentCategory['name_legacy'], 'has_children' => false ); if (isset($parent_id)) { $result_element['parent_id'] = $parent_id; } //has children? if (($currentCategory['rgt'] - $currentCategory['lft']) > 1) { $result_element['has_children'] = true; $ids = $modelCategories->fetchImmediateChildrenIds($currentCategory['project_category_id'], $modelCategories::ORDERED_TITLE); $result_element['children'] = $this->buildTree($ids, $currentCategory['project_category_id'], $store_id); } $result[] = $result_element; } return $result; } private function fetchProductCount($cat_id, $store_id = null) { $store_config = Zend_Registry::isRegistered('store_config') ? Zend_Registry::get('store_config') : null; $tagFilter = null; if($store_config) { $tagFilter = Zend_Registry::isRegistered('config_store_tags') ? Zend_Registry::get('config_store_tags') : null; }else { $tagFilter = null; } if ($tagFilter) { $sql = "SELECT count_product FROM stat_cat_prod_count WHERE project_category_id = :cat_id AND tag_id = :tags"; $bind = array('cat_id' => $cat_id, 'tags' => $tagFilter); } else { $sql = "SELECT count_product FROM stat_cat_prod_count WHERE project_category_id = :cat_id AND tag_id IS NULL"; $bind = array('cat_id' => $cat_id); } $result = $this->_dataTable->getAdapter()->fetchRow($sql, $bind); return (int)$result['count_product']; } /** * @param bool $clearCache * * @return array|false|mixed * @throws Zend_Cache_Exception * @throws Zend_Exception * * @deprecated use fetchTreeForView */ public function fetchCategoryTreeCurrentStore($clearCache = false) { $store_config = Zend_Registry::get('store_config'); $store_id = $store_config->store_id; /** @var Zend_Cache_Core $cache */ $cache = Zend_Registry::get('cache'); $cache_id = self::CACHE_TREE_STORE . "_{$store_id}"; if ($clearCache) { $cache->remove($cache_id); } if (false === ($tree = $cache->load($cache_id))) { $list_cat_id = self::fetchCatIdsForCurrentStore(); $tree = $this->buildTree($list_cat_id); $cache->save($tree, $cache_id, array(), 600); } return $tree; } /** * @return mixed|null * @throws Zend_Exception */ public static function fetchCatIdsForCurrentStore() { return Zend_Registry::isRegistered('store_category_list') ? Zend_Registry::get('store_category_list') : null; } /** * @return array * @throws Zend_Exception */ public function fetchCatNamesForCurrentStore() { $list_cat_id = self::fetchCatIdsForCurrentStore(); $sql = "SELECT project_category_id, title FROM project_category WHERE project_category_id IN (" . implode(',', $list_cat_id) . ")"; $result = $this->_dataTable->getAdapter()->fetchPairs($sql); return $result; } /** * @return array * @throws Zend_Exception */ public function fetchCatNamesForID($list_cat_id) { $sql = "SELECT title FROM project_category WHERE project_category_id IN (" . implode(',', $list_cat_id) . ") order by title " ; $results = $this->_dataTable->getAdapter()->fetchAll($sql); $values = array_map(function($row) { return $row['title']; }, $results); return $values; } /** * @return array */ public function fetchCatNames() { $sql = "SELECT project_category_id, title FROM project_category"; $result = $this->_dataTable->getAdapter()->fetchPairs($sql); return $result; } } \ No newline at end of file diff --git a/application/modules/default/plugins/AclRules.php b/application/modules/default/plugins/AclRules.php index fa76589a5..da6e25caf 100644 --- a/application/modules/default/plugins/AclRules.php +++ b/application/modules/default/plugins/AclRules.php @@ -1,410 +1,414 @@ . **/ 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_subscription')); $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_membersetting')); $this->addResource(new Zend_Acl_Resource ('default_json')); $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_gitfaq')); $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_newproducts')); $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_collection')); + $this->addResource(new Zend_Acl_Resource ('default_funding')); $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_spamkeywords')); $this->addResource(new Zend_Acl_Resource ('backend_projectclone')); + + $this->addResource(new Zend_Acl_Resource ('backend_section')); + $this->addResource(new Zend_Acl_Resource ('backend_sectioncategories')); $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_membersetting', 'default_json', 'default_ocsv1', // OCS API 'default_embedv1', // embed API 'default_productcategory', 'default_rss', 'default_support', 'default_subscription', 'default_supporterbox', 'default_plingbox', 'default_oauth', 'default_plings', 'default_gitfaq', 'default_ads', 'default_dl', 'default_stati', 'default_password', 'default_verify', 'default_login', 'default_collection' )); $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_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', 'gettaggroupsforcatajax', 'getfilesajax', 'startvideoajax', 'stopvideoajax' )); // resource default_product $this->allow(self::ROLENAME_GUEST, 'default_collection', array( 'index', 'show', 'getupdatesajax', 'updates', 'follows', 'fetch', 'search', //'startdownload', //'ppload', 'loadratings', //'loadinstallinstruction', //'getfilesajax', 'gettaggroupsforcatajax' )); // 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_COOKIEUSER, 'default_collection', array( 'add', 'rating', 'follow', 'unfollow', 'plingproject', 'followproject', 'unplingproject', 'pling', 'pay', 'dwolla', 'paymentok', 'paymentcancel', 'saveproduct', 'claim' )); $this->allow(self::ROLENAME_COOKIEUSER, 'default_membersetting', array( 'getsettings','setsettings','notification','searchmember' )); $this->allow(self::ROLENAME_MODERATOR, 'backend_project', array( 'doghnsexclude' )); $this->allow(self::ROLENAME_MODERATOR, 'default_moderation', array( 'index','list' )); $this->allow(self::ROLENAME_MODERATOR, 'default_duplicates', array( 'index' )); $this->allow(self::ROLENAME_MODERATOR, 'default_newproducts', array( 'index' )); $this->allow(self::ROLENAME_COOKIEUSER, 'default_product', array( 'edit', 'saveupdateajax', 'deleteupdateajax', 'update', 'preview', 'delete', 'unpublish', 'publish', 'verifycode', 'makerconfig', 'addpploadfile', 'updatepploadfile', 'deletepploadfile', 'deletepploadfiles', 'updatefiletag', 'getcollectionprojectsajax', 'getprojectsajax' ), new Default_Plugin_Acl_IsProjectOwnerAssertion()); // resource default_support $this->allow(self::ROLENAME_GUEST, 'default_support', array('index')); $this->allow(self::ROLENAME_COOKIEUSER, 'default_support', array('index', 'pay', 'paymentok', 'paymentcancel')); // resource default_subscription $this->allow(self::ROLENAME_GUEST, 'default_subscription', array('index')); $this->allow(self::ROLENAME_COOKIEUSER, 'default_subscription', array('index', 'pay', 'paymentok', 'paymentcancel')); // resource default_report $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', 'collections', 'news', 'activities', 'payments', 'income', 'payout', 'payouthistory', 'plings', 'plingsold', 'plingsajax', 'plingsmonthajax', 'downloadhistory', 'likes' )); $this->allow(self::ROLENAME_COOKIEUSER, 'default_tag', array('filter', 'add', 'del', 'assign', 'remove')); } } diff --git a/httpdocs/theme/backend/js/backend_nav_admin.js b/httpdocs/theme/backend/js/backend_nav_admin.js index fb2893f3e..6f58058e5 100644 --- a/httpdocs/theme/backend/js/backend_nav_admin.js +++ b/httpdocs/theme/backend/js/backend_nav_admin.js @@ -1,252 +1,262 @@ /** * ocs-webserver * * Copyright 2016 by pling GmbH. * * This file is part of ocs-webserver. * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as * published by the Free Software Foundation, either version 3 of the * License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Affero General Public License for more details. * * You should have received a copy of the GNU Affero General Public License * along with this program. If not, see . **/ /** * backend navigation with yui-lib */ var aItemData = [ { text: "General", submenu: { id: "allabout", itemdata: [ {text: "Dashboard", url: "/backend/"}, {text: "File-Browser", url: "/backend/index/filebrowser"} ] } }, { text: "Product", submenu: { id: "projects", itemdata: [ {text: "Products", url: "/backend/project"}, {text: "Clones", url: "/backend/projectclone"}, {text: "Claims", url: "/backend/claim"} ] } }, { text: "GHNS", submenu: { id: "ghns", itemdata: [ {text: "GHNS-Excluded", url: "/backend/ghnsexcluded"} ] } }, { text: "Category", submenu: { id: "category", itemdata: [ {text: "Categories", url: "/backend/categories"}, {text: "Virtual Categories", url: "/backend/vcategories"}, {text: "Category-Tag", url: "/backend/categorytag"}, {text: "Category-Tag-Group", url: "/backend/categorytaggroup"}, ] } }, { text: "User", submenu: { id: "user", itemdata: [ {text: "Users", url: "/backend/user"}, {text: "Config Paypal-Valid-Stati", url: "/backend/paypalvalidstatus"}, {text: "Generate Letter Avatar", url: "/backend/letteravatar"} ] } }, { text: "Payout", submenu: { id: "payout", itemdata: [ {text: "Member Payouts", url: "/backend/memberpayout"}, {text: "Config Payout-Stati", url: "/backend/payoutstatus"}, {text: "Member Paypal-Addresses", url: "/backend/memberpaypaladdress"} ] } }, { text: "Comment", submenu: { id: "comments", itemdata: [ {text: "Comments", url: "/backend/comments"} ] } }, { text: "Content", submenu: { id: "contents", itemdata: [ {text: "eMail-Templates", url: "/backend/mail"} ] } }, { text: "Reported", submenu: { id: "reports", itemdata: [ {text: "Comments", url: "/backend/reportcomments"}, {text: "Products", url: "/backend/reportproducts"} ] } }, { text: "Tags", submenu: { id: "tags", itemdata: [ {text: "Manage", url: "/backend/tags"} ] } }, { text: "Store", submenu: { id: "stores", itemdata: [ {text: "Config", url: "/backend/store"}, {text: "Categories", url: "/backend/storecategories"}, {text: "Virtual Categories", url: "/backend/vstorecategories"}, {text: "Init Cache", url: "/backend/store/initcache"} ] } }, + { + text: "Section", + submenu: { + id: "section", + itemdata: [ + {text: "Section", url: "/backend/section"}, + {text: "Categories", url: "/backend/sectioncategories"} + ] + } + }, { text: "Operating System", submenu: { id: "operatingsystem", itemdata: [ {text: "Config", url: "/backend/operatingsystem"} ] } }, { text: "Import", submenu: { id: "import", itemdata: [ {text: "Hive Content", url: "/backend/hive"}, {text: "Hive User", url: "/backend/hiveuser"} ] } }, { text: "Spam", submenu: { id: "spam", itemdata: [ {text: "Keywords", url: "/backend/spamkeywords"} ] } }, { text: "Account", submenu: { id: "account", itemdata: [ {text: "logout", url: "/logout"}, {text: "frontend", url: "/"} ] } } ]; $(document).ready(function () { $("body").addClass("yui-skin-sam"); var oMenuBar = new YAHOO.widget.MenuBar("ocsbackendnavigation", { lazyload: true, itemdata: aItemData }); oMenuBar.render(document.body); // Add a "show" event listener for each submenu. function onSubmenuShow() { var oIFrame, oElement, nOffsetWidth; // Keep the left-most submenu against the left edge of the browser viewport if (this.id == "allgemein") { YAHOO.util.Dom.setX(this.element, 0); oIFrame = this.iframe; if (oIFrame) { YAHOO.util.Dom.setX(oIFrame, 0); } this.cfg.setProperty("x", 0, true); } /* Need to set the width for submenus of submenus in IE to prevent the mouseout event from firing prematurely when the user mouses off of a MenuItem's text node. */ if ((this.id == "filemenu" || this.id == "editmenu") && YAHOO.env.ua.ie) { oElement = this.element; nOffsetWidth = oElement.offsetWidth; /* Measuring the difference of the offsetWidth before and after setting the "width" style attribute allows us to compute the about of padding and borders applied to the element, which in turn allows us to set the "width" property correctly. */ oElement.style.width = nOffsetWidth + "px"; oElement.style.width = (nOffsetWidth - (oElement.offsetWidth - nOffsetWidth)) + "px"; } } // Subscribe to the "show" event for each submenu oMenuBar.subscribe("show", onSubmenuShow); }); diff --git a/sql_code/20190614_add_table_section.sql b/sql_code/20190614_add_table_section.sql new file mode 100644 index 000000000..9cce52563 --- /dev/null +++ b/sql_code/20190614_add_table_section.sql @@ -0,0 +1,23 @@ +CREATE TABLE `section` ( + `section_id` INT NOT NULL AUTO_INCREMENT, + `name` VARCHAR(50) NOT NULL, + `description` VARCHAR(255) NULL, + `is_active` INT(1) UNSIGNED NULL DEFAULT '1', + `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, + `deleted_at` TIMESTAMP NULL DEFAULT NULL, + PRIMARY KEY (`section_id`) +) +COMMENT='Every section has categories, see table section_categories. And every download belongs to a category and to a section.' +COLLATE='latin1_swedish_ci' +; + +CREATE TABLE `section_category` ( + `section_category_id` INT NOT NULL AUTO_INCREMENT, + `section_id` INT NOT NULL, + `project_category_id` INT NOT NULL, + PRIMARY KEY (`section_category_id`) +) +COMMENT='every section has n categories' +COLLATE='latin1_swedish_ci' +; +