<?php
class Category_model extends CI_Model
{
    var $output = array();

	public function __construct()
	{
		parent::__construct();
	}

	function addCategory ($data)
	{
		$this->db->insert('tbl_category', $data);
        return $this->db->insert_id();
	}

	function updateCat ($parentcat)
	{
		$this->db->set('hasChild', 1);
		$this->db->where('category_id', $parentcat);
		$this->db->update('tbl_category');
	}

	function getCat ()
	{
		$this->db->select('*');
		$this->db->from('tbl_category');
		$this->db->where('merchant_row_id', getCurrentMerchantId());
		$resultSet = $this->db->get();
		$allResult = $resultSet->result();
		return $allResult;
	}

	function getProd ()
	{
		$this->db->select('*');
		$this->db->from('tbl_product');
		$this->db->where('merchant_row_id', getCurrentMerchantId());
		$this->db->where('active_status', 1);
		$this->db->where('is_gift_certificate', 0);
		$this->db->where('is_editable', 1);
        $this->db->order_by('product_description ASC');
		$resultSet = $this->db->get();
		$allResult = $resultSet->result();
		return $allResult;
	}

	function getProdByCat ($catId, $fields = '*')
	{
		if (empty($catId))
		{
            return false;
        }

        $sql = 'SELECT ' . $fields . '
                FROM tbl_product
                WHERE category_id = ' . $catId .'
                AND merchant_row_id = ' . getCurrentMerchantId() .'
                AND active_status = 1
                AND is_editable = 1
                ORDER BY product_description ASC';

        $resultSet = $this->db->query($sql);
		return $resultSet->result();
	}

    function getProdByCatForCart($catId, $merchant_row_id, $fields = '*')
    {
        if (!empty($catId) && !empty($merchant_row_id))
        {
            $this->db->select($fields);
            $this->db->from('tbl_product');
            $this->db->where('category_id', $catId);
            $this->db->where('merchant_row_id', $merchant_row_id);
            $this->db->where('active_status', 1);
			$this->db->where('is_editable', 1);
            $this->db->order_by('product_description ASC');
            $resultSet = $this->db->get();
            $allResult = $resultSet->result();
        } else
        {
            $allResult = array();
        }
        return $allResult;

    }

    function getAllProductGrid()
	{
		$currency_symbol = $_SESSION['currency_symbol'];

		$sql = 'SELECT p.product_id AS product_id_g,
					p.product_description AS product_description_g,
					(SELECT category_description FROM tbl_category WHERE category_id = p.category_id) AS category_id_g,
					CONCAT(\''.$currency_symbol.'\',p.product_price) AS product_price_g ';
        $sql .=   ' FROM tbl_product p
                    LEFT OUTER JOIN tbl_category c ON p.category_id = c.category_id
                    WHERE p.merchant_row_id = '.getCurrentMerchantId() .'
                    AND p.active_status = 1
                    AND p.is_editable = 1';
        $sql .=    ' AND p.is_gift_certificate = 0 ORDER BY p.product_description ASC';

		$sql = '('.$sql.' ) AS abc';

		$this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');

		$grid = new DataGrid('', $sql);
        $grid->per_page = $this->config->item('rows_per_page');
		if($catId!=null){
		$grid->column("<input type='checkbox' class='checkbox_checkall' />", "<#select_product#>", 'align="left" width="1%"');
		}
		$grid->column('Product Name', 'product_description_g', 'align="left" width="45%"');
		$grid->column('Product Category', 'category_id_g', 'align="left" width="30%"');
		$grid->column('Product Price', 'product_price_g', 'align="left" width="15%"');
		$grid->column('Action', 'Assign', 'align="left" width="10%"');
		$grid->build();
		return $grid->output;
	}
    function getProdByCatGrid($search){
		if(!empty($search['under_product_id']))
        {
            $search_sql .= ' AND p.product_id = \''.$search['under_product_id'].'\'';
        }
        if(!empty($search['under_Product_desc']))
        {
            $search_sql .= ' AND p.product_description LIKE \'%'.$search['under_Product_desc'].'%\'';
        }
        if(!empty($search['under_category_id']))
        {
            $search_sql .= ' AND p.category_id = \''.$search['under_category_id'].'\'';
        }
        if(!empty($search['mainCatCombo']))
        {
            $catId  =  $search['mainCatCombo'];
        }
		$currency_symbol = $_SESSION['currency_symbol'];

		$sql = 'SELECT p.product_id AS product_id_g,
					p.product_description AS product_description_g,
					(SELECT category_description FROM tbl_category WHERE category_id = p.category_id) AS category_id_g,
					CONCAT(\''.$currency_symbol.'\',p.product_price) AS product_price_g ';
		if($catId!=0){
		$sql .= ', CONCAT(\'<input type=checkbox name=checkbox[] type=checkbox value= \',p.product_id,\' />\') AS select_product ';
		}
		$sql .= ' FROM tbl_product p
				LEFT OUTER JOIN tbl_category c ON p.category_id = c.category_id
				WHERE p.merchant_row_id = '.getCurrentMerchantId() .'
				AND p.active_status = 1'.$search_sql.
				' AND p.is_editable = 1';
		if($catId!=0)
		{
        $sql .= ' AND p.category_id = ' . $catId;
		}
        $sql .=    ' AND p.is_gift_certificate = 0 ORDER BY p.product_description ASC';

		$sql = '('.$sql.' ) AS abc';

		$this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');

		$grid = new DataGrid('', $sql);

		if($catId!=0){
		$grid->column("<input type='checkbox' class='checkbox_checkall' />", "<#select_product#>", 'align="left" width="1%"');
		}
		$grid->column('Product Name', 'product_description_g', 'align="left" width="45%"');
		$grid->column('Product Category', 'category_id_g', 'align="left" width="30%"');
		$grid->column('Product Price', '<#product_price_g#>', 'align="left" width="15%"');
		if($catId!=0){
		$grid->column_detail('Unassign', 'Unassign', 'products/product_category/assign/2/<#product_id_g#>/'.$catId , 'align="left" class="link_style" width="10%" ', false , true);
		}else{
		$grid->column('Action', 'Assign');
		}
		$grid->build();
		return $grid->output;
	}
	function getProdNotByCatGrid($search)
	{
        if(!empty($search['product_id']))
        {
            $search_sql .= ' AND p.product_id = \''.$search['product_id'].'\'';
        }
        if(!empty($search['Product_desc']))
        {
            $search_sql .= ' AND p.product_description LIKE \'%'.$search['Product_desc'].'%\'';
        }
        if(!empty($search['category_id']))
        {
            $search_sql .= ' AND p.category_id = \''.$search['category_id'].'\'';
        }
        if(!empty($search['mainCatCombo']))
        {
            $catId  =  $search['mainCatCombo'];
        }

		$currency_symbol = $_SESSION['currency_symbol'];

		$sql = 'SELECT p.product_id AS product_id_g,
					p.product_description AS product_description_g,
					(SELECT category_description FROM tbl_category WHERE category_id = p.category_id) AS category_id_g,
					CONCAT(\'' . getCurrencySymbol() . '\',p.product_price) AS product_price_g ';
		if($catId!=0){
		$sql .=     ', CONCAT(\'<input type=checkbox name=checkbox[] type=checkbox value= \',p.product_id,\' />\') AS select_product ';
		}
        $sql .=   ' FROM tbl_product p
                    LEFT OUTER JOIN tbl_category c ON p.category_id = c.category_id
                    WHERE p.merchant_row_id = '.getCurrentMerchantId() .'
                    AND p.active_status = 1'.$search_sql.
                    ' AND p.is_editable = 1';
		if($catId!=0)
		{
        $sql .= ' AND p.category_id != ' . $catId;
		}
        $sql .=    ' AND p.is_gift_certificate = 0 ORDER BY p.product_description ASC';

		$sql = '('.$sql.' ) AS abc';

		$this->load->library ( 'rapyd' );
		$this->rapyd->load('datagrid');

		$grid = new DataGrid('', $sql);
        $grid->per_page = $this->config->item('rows_per_page');
		if($catId!=0){
		$grid->column("<input type='checkbox' class='checkbox_checkall' />", "<#select_product#>", 'align="left" width="1%"');
		}
		$grid->column('Product Name', 'product_description_g', 'align="left" width="45%"');
		$grid->column('Product Category', 'category_id_g', 'align="left" width="30%"');
		$grid->column('Product Price', '<#product_price_g#>', 'align="left" width="15%"');
		if($catId!=0){
		$grid->column_detail('Action', 'Assign', 'products/product_category/assign/3/<#product_id_g#>/'.$catId , 'align="left" class="link_style" width="10%" ', false , true);
		}else{
		$grid->column('Action', 'Assign');
		}
		$grid->build();
		return $grid->output;
	}
	function getProdNotByCat ($catId, $fields = '*')
	{
        $sql = 'SELECT ' . $fields . '
                    FROM tbl_product p
                    LEFT OUTER JOIN tbl_category c ON p.category_id = c.category_id
                    WHERE p.merchant_row_id = ' . getCurrentMerchantId() .'
                    AND p.active_status = 1
                    AND p.is_editable = 1
                    AND p.is_gift_certificate = 0 ';

		if(!empty($catId))
		{
            $sql .= ' AND p.category_id != ' . $catId;
		}

        $sql .= ' ORDER BY p.product_description ASC';

        $resultSet = $this->db->query($sql);
        return $resultSet->result();
	}

	function updateProdByCat ($prodId, $catId)
	{
		$this->db->set('category_id', 0);
		$this->db->where('category_id', $catId);
		$this->db->where('product_id', $prodId);
        $this->db->where('merchant_row_id', getCurrentMerchantId());
		$this->db->update('tbl_product');
		$product_count = $this->getProductCount($catId);
		$this->db->set('product_count', $product_count);
		$this->db->where('category_id', $catId);
        $this->db->where('merchant_row_id', getCurrentMerchantId());
		$this->db->update('tbl_category');
	}

    function updateProdByCatId($catId)
    {
        $this->db->set('category_id', 0);
        $this->db->where('category_id', $catId);
        $this->db->where('merchant_row_id', getCurrentMerchantId());
        $this->db->update('tbl_product');
        $product_count = $this->getProductCount($catId);
        $this->db->set('product_count', $product_count);
        $this->db->where('category_id', $catId);
        $this->db->where('merchant_row_id', getCurrentMerchantId());
        $this->db->update('tbl_category');
    }
	function updateProd ($prodId, $catId)
	{
		$this->db->select('category_id');
		$this->db->from('tbl_product');
		$this->db->where('product_row_id',$prodId);
		$res = $this->db->get();
		$result = $res->row_array();

		if($result)
		{
			$existing_category_id = $result['category_id'];
			if($existing_category_id)
			{
				$existing_product_count = $this->getProductCount($existing_category_id);

				$this->db->set('product_count', ($existing_product_count-1));
				$this->db->where('category_id', $existing_category_id);
				$this->db->update('tbl_category');
			}
		}

		$this->db->set('category_id', $catId);
		$this->db->where('product_id', $prodId);
		$this->db->update('tbl_product');
		$product_count = $this->getProductCount($catId);

		$this->db->set('product_count', $product_count);
		$this->db->where('category_id', $catId);
		$this->db->update('tbl_category');
	}

	/*it fetches the count of product under specified category*/
	function getProductCount ($catId)
	{
		$this->db->select('count(product_row_id) as PRDCNT');
		$this->db->from('tbl_product');
		$this->db->where('category_id', $catId);
		$this->db->where('active_status', 1);
		$this->db->where('is_editable', 1);
		$resultSet = $this->db->get();
		$allResult = $resultSet->row();
		return $allResult->PRDCNT;
	}

	function getAllByCatId ($catId, $merchantRowId = 0, $parentCategory = 0)
	{
        $merchantRowId = $merchantRowId ? $merchantRowId : getCurrentMerchantId();
		$this->db->select('*');
		$this->db->from('tbl_category');
		$this->db->where('category_id', $catId);
        $this->db->where('merchant_row_id', $merchantRowId);

        if($parentCategory)
        {
            $this->db->where('parent_category', $parentCategory);
        }
		$resultSet = $this->db->get();
		$allResult = $resultSet->row();
		return $allResult;
	}

	function getAllCategoriesTree($parentCategory)
	{

		$this->db->select('category_id, parent_category, hasChild');
		$this->db->from('tbl_category');
		//$this->db->where('parent_category', $parentCategory);
		$this->db->where('merchant_row_id', getCurrentMerchantId());
		$query = $this->db->get();
		$result = $query->result_array();

        $output = $this->output = array();

        if(count($result))
        {
            foreach($result as $category)
            {
                if($category['category_id'] == $parentCategory)
                {
                    $this->output[] = $category;

                    if($category['hasChild'])
                    {
                        $this->setChildren($result, $category['category_id']);
                    }
                }
            }

            foreach($this->output as $cat)
            {
                $output[] = $cat['category_id'];
            }
        }
        $this->output = array();
        return $output;
	}

    function getAllCategory($parentCategoryId = '')
    {
        $this->db->select('*');
        $this->db->from('tbl_category');
        $this->db->where('merchant_row_id', getCurrentMerchantId());
        $this->db->or_where('merchant_row_id', 0);
        
        if($parentCategoryId != '')
        {
            $this->db->where('parent_category', $parentCategoryId);
        }

        $this->db->order_by('category_description ASC');
        $query = $this->db->get();
        $result = $query->result_array();

        $this->output = array();

        foreach($result as $category)
        {
            if($category['parent_category'] == 0)
            {
                $this->output[] = $category;

                if($category['hasChild'])
                {
                    $this->setChildren($result, $category['category_id']);
                }
            }
        }

        $output = $this->output;
        $this->output = array();
        return $output;
    }

    function setChildren($haystack, $parentCategoryId)
    {
        if(is_array($haystack))
        {
            foreach($haystack as $category)
            {
                if($category['parent_category'] && $category['parent_category'] == $parentCategoryId)
                {
                    $this->output[] = $category;

                    if($category['hasChild'])
                    {
                        $this->setChildren($haystack, $category['category_id']);
                    }
                }
            }
        }
    }

    function getAllMembershipProCategory ()
    {
        $this->db->select('*');
        $this->db->from('tbl_category');
        $this->db->where('parent_category', 0);
        $this->db->where('merchant_row_id', getCurrentMerchantId());
        $this->db->where('category_description', 'Membership Pro');
        $this->db->order_by('category_description ASC');
        $resultSet2 = $this->db->get();
        $firstLevelResult = $resultSet2->result();
        $firstLevelCount = count($firstLevelResult);
        $finalArray = array();
        $chArray = array();
        for ($i = 0; $i < $firstLevelCount; $i ++)
        {
            $level1CatId = $firstLevelResult[$i]->category_id;
            array_push($finalArray, $level1CatId);
            if ($firstLevelResult[$i]->hasChild == 1)
            {
                $this->childArray = array();
                $chArray = $this->getChild($level1CatId);
                for ($k = 0; $k < count($chArray); $k ++)
                {
                    array_push($finalArray, $chArray[$k]);
                }
            }
        }
        $returnArray = $this->getReturnArray($finalArray, 0);
        return $returnArray;
    }

	function getChild ($category_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_category');
		$this->db->where('parent_category', $category_id);
		$this->db->where('merchant_row_id', getCurrentMerchantId());
		$resultSetNext = $this->db->get();
		$nextLevelResult = $resultSetNext->result();
		$childCount = count($nextLevelResult);
		for ($j = 0; $j < $childCount; $j ++)
		{
			array_push($this->childArray, $nextLevelResult[$j]->category_id);
			if ($nextLevelResult[$j]->hasChild == 1)
			{
				$this->getChild($nextLevelResult[$j]->category_id);
			}
		}
		return $this->childArray;
	}

	function getChildDescArray ($category_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_category');
		$this->db->where('parent_category', $category_id);
		$this->db->where('merchant_row_id', getCurrentMerchantId());
		$resultSetNext = $this->db->get();
		$nextLevelResult = $resultSetNext->result();
		$childCount = count($nextLevelResult);
		$childDescArray = array();
		for ($j = 0; $j < $childCount; $j ++)
		{
			$childDescArrayLower[$j] = strtolower($nextLevelResult[$j]->category_description);
			$childDescArrayUpper[$j] = strtoupper($nextLevelResult[$j]->category_description);
		}
		$childDescArray[0] = $childDescArrayLower;
		$childDescArray[1] = $childDescArrayUpper;
		return $childDescArray;
	}

	function getReturnArray ($finalArray, $returnCount = 1)
	{
		$noOfCat = count($finalArray);
		for ($m = 0; $m < $noOfCat; $m ++)
		{
			$category_id = $finalArray[$m];

            if(!$returnCount)
            {
                $this->db->select('*');
			    $this->db->from('tbl_category');
			    $this->db->where('category_id', $category_id);
			    $this->db->where('merchant_row_id', getCurrentMerchantId());
			    $resultSetNext = $this->db->get();
            }
            else
            {
                $sql = "SELECT *
                        FROM view_product_category
                        WHERE category_id = $category_id
                              AND merchant_row_id = ".getCurrentMerchantId();
                $resultSetNext = $this->db->query($sql);
            }

			$nextLevelResult = $resultSetNext->result();
			$retArray[$m]['category_id'] = $nextLevelResult[0]->category_id;
			$retArray[$m]['category_description'] = $nextLevelResult[0]->category_description;
			$retArray[$m]['parent_category'] = $nextLevelResult[0]->parent_category;
			$retArray[$m]['level'] = $nextLevelResult[0]->level;
			$retArray[$m]['hasChild'] = $nextLevelResult[0]->hasChild;
			$retArray[$m]['product_count'] = $nextLevelResult[0]->prod_count;
			$retArray[$m]['is_editable'] = $nextLevelResult[0]->is_editable;
            $retArray[$m]['create_date'] = $nextLevelResult[0]->create_date;
		}
		return $retArray;
	}

	/*this function deletes a product category*/
	function deleteCategory ($category_id)
	{
        $this->updateProdByCatId($category_id);
		/*changing in the category table*/
		$thisCategory = $this->getAllByCatId($category_id);
		$parentOfThis = $thisCategory->parent_category;
		$this->db->select('count(*) as CNTPARENT');
		$this->db->from('tbl_category');
		$this->db->where('parent_category', $parentOfThis);
		$res = $this->db->get();
		$parent = $res->row();
		$parent_count = $parent->CNTPARENT;
		if ($parent_count == 1)
		{
			$this->db->set('hasChild', 0);
			$this->db->where('category_id', $parentOfThis);
			$this->db->update('tbl_category');
		}
		/*deleting the category*/
		$this->db->where('category_id', $category_id);
		$this->db->delete('tbl_category');
	}

	function updateCategoryDesc ($newCat, $catId)
	{
		$this->db->set('category_description', $newCat);
		$this->db->where('category_id', $catId);
		return $this->db->update('tbl_category');
	}

	/*this function checks duplicacy of category description while editing*/
	function checkDuplicate ($parent_category, $category_description, $category_id = 0, $merchantRowId = 0)
	{
        $merchantRowId = $merchantRowId ? $merchantRowId : getCurrentMerchantId();

        $sql = "SELECT COUNT(category_id) AS cnt
                FROM tbl_category
                WHERE merchant_row_id = $merchantRowId
                AND parent_category = $parent_category
                AND UPPER(category_description) = " . $this->db->escape(strtoupper($category_description));

        if($category_id)
        {
            $sql .= " AND category_id != $category_id";
        }
        $res = $this->db->query($sql);

        if($res)
        {
            $row = $res->row();
            if($row)
            {
                if($row->cnt)
                {
                    return false;
                }
            }
        }
        return true;
	}

    function getAllCategoriesForAPI($merchantRowId, $parentCategory = 0)
    {
        if($merchantRowId)
        {
            $sql = 'SELECT category_id,
                           category_description,
                           IFNULL(parent_category, 0) AS parent_category,
                           level,
                           hasChild,
                           is_editable,
                           create_date,
                           modified_on
                    FROM tbl_category
                    WHERE merchant_row_id = ' . $this->db->escape($merchantRowId);

            if($parentCategory)
            {
                $sql .= ' AND parent_category= ' . $this->db->escape($parentCategory);
            }

            $query = $this->db->query($sql);
            return $query->result();
        }

        return false;
    }

    function isCategoryExistForAPI($categoryId, $merchantRowId)
    {
        $sql = 'SELECT category_id
                FROM tbl_category
                WHERE category_id = ' . $categoryId . '
                AND merchant_row_id = ' . $merchantRowId . '
                AND category_description NOT IN (\'Membership Pro\', \'Membership Lite\')';

        $query = $this->db->query($sql);

        if($query)
        {
            $row = $query->row();

            if($row && $row->category_id)
            {
                return true;
            }
        }

        return false;
    }

    function getCategoryId($info)
    {
         $category_id = 0;
         if($info['category'])
         {
             $sql = 'SELECT category_id FROM tbl_category WHERE merchant_row_id = ' . $info['merchant_row_id'] . " AND category_description = " . $this->db->escape($info['category']) ." AND parent_category = 0";
             $query = $this->db->query($sql);
             $result = $query->row_array();
             $category_id = $result['category_id'] ? $result['category_id'] : '';

             if(!$category_id)
             {
                 $data['merchant_row_id'] = $info['merchant_row_id'];
                 $data['category_description'] = $info['category'];
                 $data['level']    = 1;
                 $data['hasChild'] = 1;
                 $data['create_date'] = date('Y-m-d H:i:s');
                 $this->db->insert('tbl_category', $data);
                 $category_id = $this->db->insert_id();
             }
         }

         $category_id1 = $category_id;
         if($info['sub_category'])
         {
             $sql = 'SELECT category_id FROM tbl_category WHERE merchant_row_id = ' . $info['merchant_row_id'] . " AND category_description = " . $this->db->escape($info['sub_category']) ." AND parent_category = " . $category_id;
             $query = $this->db->query($sql);
             $result = $query->row_array();
             $category_id = $result['category_id'] ? $result['category_id'] : '';

             if(!$category_id)
             {
                 $data['merchant_row_id'] = $info['merchant_row_id'];
                 $data['category_description'] = $info['sub_category'];
                 $data['level']    = 2;
                 $data['hasChild'] = 1;
                 $data['parent_category'] = $category_id1;
                 $data['create_date'] = date('Y-m-d H:i:s');
                 $this->db->insert('tbl_category', $data);
                 $category_id = $this->db->insert_id();
             }
         }

         $category_id2 = $category_id;
         if($info['sub_sub_category'])
         {
             $sql = 'SELECT category_id FROM tbl_category WHERE merchant_row_id = ' . $info['merchant_row_id'] . " AND category_description = " . $this->db->escape($info['sub_sub_category']) ." AND parent_category = " . $category_id;
             $query = $this->db->query($sql);
             $result = $query->row_array();
             $category_id = $result['category_id'] ? $result['category_id'] : '';

             if(!$category_id)
             {
                 $data['merchant_row_id'] = $info['merchant_row_id'];
                 $data['category_description'] = $info['sub_sub_category'];
                 $data['level']    = 3;
                 $data['hasChild'] = 0;
                 $data['parent_category'] = $category_id2;
                 $data['create_date'] = date('Y-m-d H:i:s');
                 $this->db->insert('tbl_category', $data);
                 $category_id = $this->db->insert_id();
             }
         }
         return $category_id;
    }

    function updateParentCategory($categoryId, $parentCategory, $merchantRowId)
    {
        $sql = 'UPDATE tbl_category
                SET parent_category = ' . $this->db->escape($parentCategory) . '
                WHERE category_id = ' . $this->db->escape($categoryId) . '
                AND merchant_row_id = ' . $this->db->escape($merchantRowId);

        $this->db->query($sql);
    }

    function getAllCategoryWithProductCount ()
    {

    $sql="SELECT tbl_category. * , (
    SELECT count( tbl_product.product_row_id )                                           
    FROM tbl_product
    WHERE tbl_product.category_id = tbl_category.category_id and tbl_product.merchant_row_id =".getCurrentMerchantId()." and 
    tbl_product.active_status = 1 AND tbl_product.is_editable = 1 AND tbl_product.is_gift_certificate = 0 AND tbl_category.category_id NOT IN(" . $this->config->item('schedule_category_id') . ", " . $this->config->item('club_fee_category_id') . ") 
    ) AS productcount
    FROM tbl_category LEFT JOIN tbl_product ON tbl_category.category_id = tbl_product.category_id
    WHERE tbl_category.merchant_row_id =".getCurrentMerchantId()." OR tbl_category.merchant_row_id = 0 AND tbl_category.category_id NOT IN(52450, 52451)
    GROUP BY tbl_category.category_id
    ORDER BY tbl_category.category_description";

    $query = $this->db->query($sql);
    $result = $query->result_array();
    $this->output = array();
        foreach($result as $category)
        {
            if($category['parent_category'] == 0)
            {
                $this->prod_count = 0;
                if($category['hasChild'])
                {
                    $child_product_count = $this->ProductCountChildren($result, $category['category_id']);
                    $child_product_count = $this->prod_count;
                    $product_count = $child_product_count + $category['productcount'];
                    array_push($category,$product_count);
                    $this->output[] = $category;
                    $this->setChildrenWithProductCount($result, $category['category_id']);
                }
                else
                {
                    array_push($category,$category['productcount']);
                    $this->output[] = $category;

                }
            }
        }

        $output = $this->output;
        $this->output = array();
        return $output;


    }
    function ProductCountChildren($haystack, $parentCategoryId,$pcount='')
    {

     $this->prod_count = $pcount;

        if(is_array($haystack))
        {
            foreach($haystack as $category)
            {
                if($category['parent_category'] && $category['parent_category'] == $parentCategoryId)
                {

                    $total_child_product =  $this->prod_count + $category ['productcount'];


                    if($category['hasChild'])
                    {
                        $this->ProductCountChildren($haystack, $category['category_id'],$total_child_product);
                    }

                    else
                    {
                     $this->ProductCountChildren($haystack, $category['category_id'],$total_child_product);
                    }

                }


            }

        }


    }

    function setChildrenWithProductCount($haystack, $parentCategoryId)
    {
        if(is_array($haystack))
        {
            foreach($haystack as $category)
            {
                if($category['parent_category'] && $category['parent_category'] == $parentCategoryId)
                {
                  if($category['hasChild'])
                    {
                    $child_product_count = $this->ProductCountChildren($haystack, $category['category_id']);
                    $child_product_count = $this->prod_count;
                    $product_count = $child_product_count + $category['productcount'];
                    array_push($category,$product_count);
                    $this->output[] = $category;
                    $this->setChildrenWithProductCount($haystack, $category['category_id']);
                    }
                    else
                    {
                    array_push($category,$category['productcount']);
                    $this->output[] = $category;
                    }
                }
            }
        }
    }

}
?>