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