Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
- /**
- * Description of Category
- *
- * @author fabi0
- */
- namespace Models;
- class Category {
- private $_query;
- public function __construct() {
- $this->_query = \Models\DB::getInstance();
- }
- public function getCategoryTree($category_id) {
- $sql = "
- SELECT node.category_id, node.category_name FROM categories AS node, categories AS parent
- WHERE node.category_left BETWEEN parent.category_left AND parent.category_right AND parent.category_id = :id
- ORDER BY node.category_left;";
- $params = array(
- 'param' => $category_id,
- 'type' => 1
- );
- $this->_query->query($sql, $params);
- if ($this->_query->getResult()) {
- return getResult();
- }
- return false;
- }
- public function getCategoryDepth($arrange = false, $resultType = 5) {
- $result = array();
- $sql = "SELECT category_id,category_parent,category_depth,category_name FROM categories
- ORDER BY categories.category_left;
- SELECT node.category_id, node.category_name, (COUNT(parent.category_name) - 1) AS category_depth
- FROM categories AS node,categories AS parent
- WHERE node.category_left BETWEEN parent.category_left AND parent.category_right
- GROUP BY node.category_id
- ORDER BY node.category_left;";
- $query = $this->_query->query($sql, NULL, $resultType)->getResult();
- if ($arrange) {
- foreach ($query as $value) {
- $result[$value->category_id]['category_name'] = $value->category_name;
- $result[$value->category_id]['category_depth'] = $value->category_depth;
- $result[$value->category_id]['category_parent'] = $value->category_parent;
- }
- return $result;
- }
- return $query;
- }
- public function getCategoryWithPosts() {
- $sql = "
- SELECT parent.category_id, COUNT(discussions.discussion_id) as category_posts
- FROM categories AS node ,categories AS parent,discussions
- WHERE node.category_left BETWEEN parent.category_left
- AND parent.category_right AND node.category_id = discussions.discussion_category
- GROUP BY parent.category_id
- ORDER BY node.category_left";
- return $this->_query->query($sql)->getResult();
- }
- public function getFullCategoryTreeWithPostsCount() {
- $cat1 = $this->getCategoryDepth();
- $cat2 = $this->getCategoryWithPosts();
- $newArray = array();
- foreach ($cat1 as $k => $v) {
- $newArray[$v->category_id]['category_name'] = $v->category_name;
- $newArray[$v->category_id]['category_depth'] = $v->category_depth;
- }
- foreach ($cat2 as $k => $vv) {
- $newArray[$vv->category_id]['category_posts'] = $vv->category_posts;
- }
- foreach ($newArray as $key => $value) {
- if (!isset($newArray[$key]['category_posts'])) {
- $newArray[$key]['category_posts'] = 0;
- }
- }
- return $newArray;
- }
- public function getAllPost() {
- $sql = "SELECT count(discussion_id) as all_post FROM discussions WHERE discussion_name IS NOT NULL";
- return $this->_query->query($sql)->getResult();
- }
- public function renameCategory($id, $newName) {
- $sql = "UPDATE categories SET category_name = :name WHERE category_id = :id;";
- $params = array(
- ':name' => array(
- 'param' => $newName,
- 'type' => 2
- ),
- ':id' => array(
- 'param' => $id,
- 'type' => 1
- )
- );
- return $this->_query->query($sql, $params);
- }
- public function deleteCategory($category_id) {
- $sql = "
- LOCK TABLE categories WRITE;
- SELECT @myLeft := category_left, @myRight := category_right, @myWidth := category_right - category_left + 1
- FROM categories WHERE category_id = :id;
- DELETE FROM categories WHERE category_left BETWEEN @myLeft AND @myRight;
- UPDATE categories SET category_right = category_right - @myWidth WHERE category_right > @myRight;
- UPDATE categories SET category_left = category_left - @myWidth WHERE category_left > @myRight;
- UNLOCK TABLES;";
- $params = array(
- ':id' => array(
- 'param' => $category_id,
- 'type' => 1
- )
- );
- return $this->_query->_query($sql, $params)->getResult();
- }
- public function createCategory($parent_id, $category_name) {
- $sql = "
- LOCK TABLE categories WRITE;
- SELECT @parentDepth :=category_depth FROM categories WHERE category_id = :parent;
- SELECT @myLeft := category_left FROM categories WHERE category_id = :parent;
- UPDATE categories SET category_right = category_right + 2 WHERE category_right > @myLeft;
- UPDATE categories SET category_left = category_left + 2 WHERE category_left > @myLeft;
- INSERT INTO categories(category_name, category_left, category_right, category_parent,category_depth)
- VALUES(:name, @myLeft + 1, @myLeft + 2, :parent,@parentDepth+1);
- UNLOCK TABLES;";
- $params = array(
- ':parent' => array(
- 'param' => $parent_id,
- 'type' => 1
- ),
- ':name' => array(
- 'param' => $category_name,
- 'type' => 2
- )
- );
- return $this->_query->query($sql, $params);
- }
- public function createRootCategory($category_name) {
- $sql = "
- LOCK TABLE categories WRITE;
- SELECT @myRight := MAX(category_right) FROM categories;
- INSERT INTO categories(category_name, category_left, category_right) VALUES(:name, IFNULL(@myRight + 1,1),IFNULL(@myRight + 2,2));
- UNLOCK TABLES";
- $params = array(
- ':name' => array(
- 'param' => $category_name,
- 'type' => 2
- )
- );
- return $this->_query->query($sql, $params)->getResult();
- }
- public function organizeCategories($data) {
- foreach ($data as $value) {
- $sql = "UPDATE categories SET category_parent = :parent, category_depth = :depth, category_left = :left, category_right = :right "
- . "WHERE category_id = :itemID;";
- $params = array(
- ':parent' => array(
- 'param' => (int) $value['parent_id'],
- 'type' => 1
- ),
- ':itemID' => array(
- 'param' => (int) $value['item_id'],
- 'type' => 1
- ),
- ':depth' => array(
- 'param' => ((int) $value['depth']) - 1,
- 'type' => 1
- ),
- ':left' => array(
- 'param' => ((int) $value['left']) - 1,
- 'type' => 1
- ),
- ':right' => array(
- 'param' => ((int) $value['right']) - 1,
- 'type' => 1
- )
- );
- $this->_query->query($sql, $params);
- }
- }
- public function buildTree(array $elements, $parentId = 0) {
- $branch = array();
- foreach ($elements as $element) {
- if ($element['category_parent'] == $parentId) {
- $children = $this->buildTree($elements, $element['category_id']);
- if ($children) {
- $element['child'] = $children;
- }
- $branch[] = $element;
- }
- }
- return $branch;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement