ꦫꦣꦺꦤ꧀ꦄꦤ꧀ꦠꦱꦺꦤ

PATH: home / centosnipponia / public_html / nipponiacom2 / caribe / components / com_djcatalog2 / models

<?php
/**
 * @version $Id: items.php 418 2015-05-11 12:43:29Z michal $
 * @package DJ-Catalog2
 * @copyright Copyright (C) 2012 DJ-Extensions.com LTD, All rights reserved.
 * @license http://www.gnu.org/licenses GNU/GPL
 * @author url: http://dj-extensions.com
 * @author email contact@dj-extensions.com
 * @developer Michal Olczyk - michal.olczyk@design-joomla.eu
 *
 * DJ-Catalog2 is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * DJ-Catalog2 is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with DJ-Catalog2. If not, see <http://www.gnu.org/licenses/>.
 *
 */

defined ('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.modellist');

class DJCatalog2ModelItems extends JModelList {
    var $_list = null;
    var $_pagination = null;
    var $_total = null;
    var $_producers = null;
    var $_params = null;
    static $_attributes = array();
    static $_attributeOptions = array();
        
    function __construct($config = array())
    {
        parent::__construct($config);
    }
    
    protected function populateState($ordering = null, $direction = null)
    {
        $app = JFactory::getApplication();
        $params = Djcatalog2Helper::getParams();
        $this->setState('params', $params);

$filter_featured    = $params->get('featured_only', 0);
        $this->setState('filter.featured', $filter_featured);
        
        $filter_restricted    = $params->get('items_show_restricted', 0);
        $this->setState('filter.restricted', $filter_restricted);
        
        $filter_catid       = (int) $app->input->get( 'cid', '0', 'int');
        $this->setState('filter.category', $filter_catid);
        
        $filter_catalogue = $params->get('product_catalogue', false) == true ? true : false;
        $this->setState('filter.catalogue', $filter_catalogue);
        
        $filter_producerid  = (int) $app->input->get( 'pid',0, 'string' );
        $this->setState('filter.producer', $filter_producerid);
        
        $filter_index       =  $app->input->get( 'ind', false, 'string' );
        $this->setState('filter.index', ($filter_index === false) ? false : urldecode($filter_index) );
        
        $filter_owner   = (int) $app->input->get( 'aid',0, 'string' );
        $this->setState('filter.owner', $filter_owner);
        
        $filter_price_from  = $app->input->get( 'price_from',0, 'string' );
        $this->setState('filter.price_from', $filter_price_from);
        
        $filter_price_to    = $app->input->get( 'price_to',0, 'string' );
        $this->setState('filter.price_to', $filter_price_to);
        
        $this->setState('filter.parent', 0);
        
        $filters            = $app->input->get('djcf',array(),'array');
        
        $request = $app->input->getArray($_REQUEST);
        foreach($request as $param=>$value) {
            if (!array_key_exists('djcf', $request)) {
                $request['djcf'] = array();
            }
            if (strstr($param, 'f_')) {
                $qkey = substr($param, 2);
                $qval = null;
                if (is_array($value)) {
                    $qval = $value;
                } else {
                    $qval = (strstr($value,',') !== false) ? explode(',',$value) : $value;
                }
                //$qval = (strstr($value,',') !== false) ? explode(',',$value) : $value;
                unset($request[$param]);
                $request['djcf'][$qkey] = $qval;
            }
        }
        $filters = $request['djcf'];
        
        $this->setState('filter.customattribute', $filters);
        
        $searches           = $app->input->get('djcs',array(), 'array');
        $this->setState('filter.customsearch', $searches);
        
        $globalSearch       = urldecode($app->input->get( 'search','', 'string' ));
        $this->setState('filter.search', $globalSearch);
        
        $mapSearch      = urldecode($app->input->get( 'mapsearch','', 'string' ));
        $this->setState('filter.map.address', $mapSearch);
        
        $map_radius         = $app->input->get( 'ms_radius', false, 'int' );
        $this->setState('filter.map.radius', (int)$map_radius);
        
        $map_unit       = $app->input->get( 'ms_unit', false, 'string' );
        $this->setState('filter.map.unit', $map_unit);
        
        if ($mapSearch && $map_radius && $map_unit) {
            $this->setState('filter.map', true);
        }
        
        $this->setState('filter.publish_date', true);
        
        $filter_state = (int)$params->get('items_show_archived', 0);
        if ($filter_state) {
            $this->setState('filter.state', '3');
        } else {
            $this->setState('filter.state', '1');
        }
        
        $order      = $app->input->get( 'order', $params->get('items_default_order','i.ordering'), 'cmd' );
        $this->setState('list.ordering', $order);
        
        $order_dir  = $app->input->get( 'dir',  $params->get('items_default_order_dir','asc'), 'word' );
        $this->setState('list.direction', $order_dir);
        
        $order_featured = $params->get('featured_first', 0);
        $this->setState('list.ordering_featured', $order_featured);
        
        $limit      = $app->input->get( 'limit', $params->get('limit_items_show',10), 'int' );
        $this->setState('list.limit', $limit);
        
        $limitstart = $app->input->get( 'limitstart', 0, 'int' );
        $this->setState('list.start', $limitstart);
        
    }
    
    protected function getStoreId($id = '')
    {
        // Compile the store id.
        $id .= ':'.$this->getState('list.select');
        $id .= ':'.$this->getState('filter.featured');
        $id .= ':'.$this->getState('filter.restricted');
        
        $filter_category = $this->getState('filter.category', 0);
        if (is_array($filter_category)) {
            JArrayHelper::toInteger($filter_category);
            $filter_category = implode(',',$filter_category);
        }
        $id .= ':'.$filter_category;
        
        $filter_pks = $this->getState('filter.item_ids');
        if (is_array($filter_pks)) {
            JArrayHelper::toInteger($filter_pks);
            $filter_pks = implode(',',$filter_pks);
        }
        $id .= ':'.$filter_pks;
        
        $id .= ':'.$this->getState('filter.catalogue');
        $id .= ':'.$this->getState('filter.producer');
        $id .= ':'.$this->getState('filter.index');
        $id .= ':'.$this->getState('filter.parent');
        $id .= ':'.$this->getState('filter.price_from');
        $id .= ':'.$this->getState('filter.price_to');
        $id .= ':'.serialize($this->getState('filter.customattribute'));
        $id .= ':'.serialize($this->getState('filter.customsearch'));
        $id .= ':'.$this->getState('filter.search', '1');
        $id .= ':'.$this->getState('filter.state');
        $id .= ':'.$this->getState('filter.owner');
        $id .= ':'.$this->getState('filter.map');
        $id .= ':'.$this->getState('filter.map.address');
        $id .= ':'.$this->getState('filter.map.radius');
        $id .= ':'.$this->getState('filter.map.unit');
        $id .= ':'.$this->getState('list.ordering');
        $id .= ':'.$this->getState('list.direction');
        $id .= ':'.$this->getState('list.ordering_featured');
        $id .= ':'.$this->getState('list.limit');
        $id .= ':'.$this->getState('list.start');

return md5($this->context . ':' . $id);
    }
    protected function _getList($query, $limitstart = 0, $limit = 0)
    {
        $this->_db->setQuery($query, $limitstart, $limit);
        $result = $this->_db->loadObjectList('id');

return $result;
    }
    
    public function getItems()
    {
        // Get a storage key.
        $store = $this->getStoreId();

// Try to load the data from internal storage.
        if (isset($this->cache[$store]))
        {
            return $this->cache[$store];
        }
        // Load the list items.
        $query = $this->_getListQuery();
        //echo str_replace('#_', 'jos',(string)$query);die();
        $items = $this->_getList($query, $this->getStart(), $this->getState('list.limit'));

// Check for a database error.
        if ($this->_db->getErrorNum())
        {
            $this->setError($this->_db->getErrorMsg());
            return false;
        }

// Add the items to the internal cache.
        $this->cache[$store] = $items;
        
        $this->bindAttributes($store);
        
        return $this->cache[$store];
    }
    protected function getListQuery()
    {
        return $this->buildQuery();
    }
    
    protected function _getListCount($query)
    {
        // Use fast COUNT(*) on JDatabaseQuery objects if there no GROUP BY or HAVING clause:
        if ($query instanceof JDatabaseQuery && $query->type == 'select' && $query->group === null && $query->having === null)
        {
                
            $query = clone $query;
            $query->clear('select')->clear('order')->select('COUNT(distinct (i.id))');
            $this->_db->setQuery($query);
            //echo str_replace('#__', 'jos_', $query);die();
            return (int) $this->_db->loadResult();
        }
    
        // Otherwise fall back to inefficient way of counting all results.
        $this->_db->setQuery($query);
        $this->_db->execute();
    
        return (int) $this->_db->getNumRows();
    }
    
    public function buildQuery($ignoreFilters = array()) {
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        
        $where      = $this->_buildContentWhere($ignoreFilters, $query);
        $orderby    = $this->_buildContentOrderBy($query);
        $attributes = $this->getAttributes(true);
        $textSearch = array();
        
        $filters = $this->getState('filter.customattribute');
        $searches = $this->getState('filter.customsearch');
        $globalSearch = $this->getState('filter.search');
        
        //$query->select('distinct i.*');
        $list_select = $this->getState('list.select','distinct i.*, CASE WHEN (i.special_price > 0.0 AND i.special_price < i.price) THEN i.special_price ELSE i.price END as final_price');
        $ids_only = ($list_select == 'i.id') ? true : false;
        
        if ($ids_only) {
            $list_select = 'distinct i.id';
        }
        
        $query->select($list_select);
        
        $query->from('#__djc2_items as i');
        
        if (!$ids_only) {
            $query->select('c.id as _category_id, c.name as category, c.published as publish_category, c.alias as category_alias');
        }
        
        $query->join('left','#__djc2_categories AS c ON c.id = i.cat_id');
        
        if (!$ids_only) {
            $query->select('p.id as _producer_id, p.name as producer, p.published as publish_producer, p.alias as producer_alias');
        }
        
        $query->join('left','#__djc2_producers AS p ON p.id = i.producer_id');
        
        if (!$ids_only) {
            $query->select('ua.name AS author, ua.email AS author_email');  
        }
        
        $query->join('left', '#__users AS ua ON ua.id = i.created_by');
        
        if (!$ids_only) {
            $query->select('countries.country_name');
        }
        $query->join('left', '#__djc2_countries AS countries ON countries.id = i.country');
        
        if (!$ids_only) {
            $query->select('states.name as state_name');
        }
        $query->join('left', '#__djc2_countries_states AS states ON states.id = i.state');
        
        
        /*if (!$ids_only) {
            $query->select('group_concat(distinct ic.category_id order by ic.category_id asc separator \'|\') AS categorylist');
            $query->join('left', '#__djc2_items_categories AS ic ON ic.item_id=i.id');
        }*/
        
        $globalSearch = trim(JString::strtolower( $globalSearch ));
        if (JString::substr($globalSearch,0,1) == '"' && JString::substr($globalSearch, -1) == '"') { 
            $globalSearch = JString::substr($globalSearch,1,-1);
        }
        if (JString::strlen($globalSearch) > 0 && (JString::strlen($globalSearch)) < 2 || JString::strlen($globalSearch) > 40) {
            $globalSearch = null;
        }
        
        $doTextSearch = !in_array('search', $ignoreFilters);
        if ($doTextSearch && $globalSearch) {
        	
        	$includeFalang = (bool)(Djcatalog2Helper::isFalang() && Djcatalog2Helper::isDefaultLanguage() === false);
        	if ($includeFalang) {
        		$langId = Djcatalog2Helper::getLangId();
        		$query->join('LEFT', '#__falang_content AS falc ON falc.reference_table="djc2_items" AND falc.language_id='.(int)$langId.' AND falc.reference_id = i.id');
        	}
        	
            $words = explode(' ', $globalSearch);
            $wheres = array();
            $phrase = 'all';
            foreach ($words as $word) {
                $word       = $db->quote('%'.$db->escape($word, true).'%', false);
                $wheres2    = array();
                $wheres2[]  = 'i.description LIKE '.$word;
                $wheres2[]  = 'i.intro_desc LIKE '.$word;
                $wheres2[]  = 'i.name LIKE '.$word;
                $wheres2[]  = 'p.name LIKE '.$word;
                $wheres2[]  = 'c.name LIKE '.$word;
                
                if ($includeFalang) {
                	$wheres2[] = '(falc.reference_field="name" AND falc.value LIKE '.$word.')';
                	$wheres2[] = '(falc.reference_field="description" AND falc.value LIKE '.$word.')';
                	$wheres2[] = '(falc.reference_field="intro_desc" AND falc.value LIKE '.$word.')';
                }
                
                $wheres[]   = implode(' OR ', $wheres2);
            }
            
            $textSearch[]   = '(' . implode(($phrase == 'all' ? ') AND (' : ') OR ('), $wheres) . ')';
            
            $optionsSearch = 
                 ' select i.id '
                .' from #__djc2_items as i '
                .' inner join #__djc2_items_extra_fields_values_int as efv on efv.item_id = i.id'
                .' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 '
                .' inner join #__djc2_items_extra_fields_options as efo on efo.id = efv.value and lower(efo.value) like '.$db->quote( '%'.$db->escape( $globalSearch, true ).'%', false )
                .' union '
                . 'select i.id '
                .' from #__djc2_items as i '
                .' inner join #__djc2_items_extra_fields_values_text as efv on efv.item_id = i.id'
                .' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 and lower(efv.value) like '.$db->quote( '%'.$db->escape( $globalSearch, true ).'%', false )
                ;
                
            $query->join('LEFT', '('.$optionsSearch.') AS customattribute_search ON customattribute_search.id = i.id');
            $textSearch[] = 'i.id = customattribute_search.id';
        }
        
        
        $doCustomSearch = !in_array('custom_fields', $ignoreFilters);
        
        if ($doCustomSearch) {
            $filter_unions = array();
            foreach ($attributes as $key=>$attribute) {
                $attributes[$key]->alias = str_replace('-', '_', $attribute->alias);
                
                if (!empty($filters[$attribute->alias])) {
                    $filter = $filters[$attribute->alias];
                    if ($attribute->filterable == 1) {
                        
                        if (is_scalar($filter) && strpos($filter, ',') !== false) {
                            $filter = explode(',', $filter);
                        } else if (is_scalar($filter) && strpos($filter, '-') !== false) {
                            $filter = explode('-', $filter);
                        }
                        if (is_array($filter)) {
                        	if ($attribute->type == 'text') {
                        		$attribute->filter_type = 'minmax_text';
                        	}
                        	
                            if ($attribute->filter_type == 'minmax' || $attribute->filter_type == 'minmax_text') {
                                $min = false;
                                $max = false;
                                if (count($filter) == 2) {
                                    $min = (strlen($filter[0]) > 0) ? $filter[0] : false;
                                    $max = (strlen($filter[1]) > 0) ? $filter[1] : false;
                                    
                                    if ($min === false && $max === false) {
                                        continue;
                                    }
                                    if ($min === false) {
                                        if ($attribute->type == 'text') {
                                            $filter_unions[] = '(select v.* '
                                            .' from #__djc2_items_extra_fields_values_text as v '
                                            .' where v.field_id='.$attribute->id.' and CAST(v.value AS DECIMAL(10,4)) <= '.$db->quote(floatval($max)).' group by v.item_id)';
                                        } else {
                                            $filter_unions[] = '(select v.* '
                                            .' from #__djc2_items_extra_fields_values_int as v '
                                            .' left join #__djc2_items_extra_fields_options as o on o.field_id=v.field_id and o.id = v.value '
                                            .' where v.field_id='.$attribute->id.' and CAST(o.value AS DECIMAL(10,4)) <= '.$db->quote(floatval($max)).' group by v.item_id)';
                                        }
                                        
                                    } else if ($max === false) {
                                        if ($attribute->type == 'text') {
                                            $filter_unions[] = '(select v.* '
                                            .' from #__djc2_items_extra_fields_values_text as v '
                                            .' where v.field_id='.$attribute->id.' and CAST(v.value AS DECIMAL(10,4)) >= '.$db->quote(floatval($min)).' group by v.item_id)';
                                        } else {
                                            $filter_unions[] = '(select v.* '
                                            .' from #__djc2_items_extra_fields_values_int as v '
                                            .' left join #__djc2_items_extra_fields_options as o on o.field_id=v.field_id and o.id = v.value '
                                            .' where v.field_id='.$attribute->id.' and CAST(o.value AS DECIMAL(10,4)) >= '.$db->quote(floatval($min)).' group by v.item_id)';
                                        }
                                    } else {
                                        if ($attribute->type == 'text') {
                                            $filter_unions[] = '(select v.* '
                                            .' from #__djc2_items_extra_fields_values_text as v '
                                            .' where v.field_id='.$attribute->id.' and CAST(v.value AS DECIMAL(10,4)) <= '.$db->quote(floatval($max)).' and CAST(v.value AS DECIMAL(10,4)) >= '.$db->quote(floatval($min)).' group by v.item_id)';
                                        } else {
                                            $filter_unions[] = '(select v.* '
                                            .' from #__djc2_items_extra_fields_values_int as v '
                                            .' left join #__djc2_items_extra_fields_options as o on o.field_id=v.field_id and o.id = v.value '
                                            .' where v.field_id='.$attribute->id.' and CAST(o.value AS DECIMAL(10,4)) <= '.$db->quote(floatval($max)).' and CAST(o.value AS DECIMAL(10,4)) >= '.$db->quote(floatval($min)).' group by v.item_id)';
                                        }
                                    }
                                }
                            } else {
                                if ($attribute->type == 'checkbox') {
                                    foreach($filter as $key=>$opt) {
                                        if (is_scalar($opt)) {
                                            $filter_unions[] = '(select * from #__djc2_items_extra_fields_values_int where field_id='.$attribute->id.' and value='.(int)$opt.')';
                                        }
                                    }
                                } else {
                                    $terms = array();
                                    foreach($filter as $key=>$opt) {
                                        if (is_scalar($opt)) {
                                            $terms[] = 'value = '.(int)$opt;
                                        }
                                    }
                                    if (count($terms) > 0) {
                                        $condition = implode(' OR ', $terms);
                                        $filter_unions[] = '(select * from #__djc2_items_extra_fields_values_int where field_id='.$attribute->id.' and ('.$condition.'))';
                                    }
                                }
                            }
                        } else {
                            $filter_unions[] = '(select * from #__djc2_items_extra_fields_values_int where field_id='.$attribute->id.' and value='.(int)$filter.')';
                        }
                    }
                }
                
                // Custom search
                if (!empty($searches[$attribute->alias]) && is_string($searches[$attribute->alias])) {
                	$filter = $searches[$attribute->alias];
                	if ($attribute->searchable == 1) {
                		$tblAls = 'djcs'.$attribute->id;
                		if ($attribute->type == 'checkbox' || $attribute->type == 'select' || $attribute->type == 'radio') {
                			$subQuerySearch = 
				                 ' select i.id '
				                .' from #__djc2_items as i '
				                .' inner join #__djc2_items_extra_fields_values_int as efv on efv.item_id = i.id'
				                .' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 '
				                .' inner join #__djc2_items_extra_fields_options as efo on efo.id = efv.value and lower(efo.value) like '.$db->quote( '%'.$db->escape( $searches[$attribute->alias], true ).'%', false )
				                ;
				                
				            $query->join('INNER', '('.$subQuerySearch.') AS '.$tblAls.' ON '.$tblAls.'.id = i.id');
				            
                		} else if ($attribute->type == 'calendar') {
                			$subQuerySearch = 
                			'select i.id '
                			.' from #__djc2_items as i '
                			.' inner join #__djc2_items_extra_fields_values_date as efv on efv.item_id = i.id'
                			.' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 and lower(efv.value) like '.$db->quote( '%'.$db->escape( $searches[$attribute->alias], true ).'%', false )
                			;
                			
                			$query->join('INNER', '('.$subQuerySearch.') AS '.$tblAls.' ON '.$tblAls.'.id = i.id');
                			
                		} else {
                			$subQuerySearch = 
                			'select i.id '
                			.' from #__djc2_items as i '
                			.' inner join #__djc2_items_extra_fields_values_text as efv on efv.item_id = i.id'
                			.' inner join #__djc2_items_extra_fields as ef on ef.id = efv.field_id and ef.searchable = 1 and lower(efv.value) like '.$db->quote( '%'.$db->escape( $searches[$attribute->alias], true ).'%', false )
                			;
                			
                			$query->join('INNER', '('.$subQuerySearch.') AS '.$tblAls.' ON '.$tblAls.'.id = i.id');
                		}
                	}
                }
                
            }
            if (count($filter_unions) > 0) {
                $unionQuery = 'select * from (select count(*) as c, item_id from ('.implode(' union ', $filter_unions).') as f group by f.item_id) as filter_counter where filter_counter.c='.count($filter_unions);
                $query->join('inner', '('.$unionQuery.') as filters on filters.item_id = i.id');
            }
        }
        
        if ($doTextSearch && count($textSearch)) {
            $where[] = ' ( '.implode( ' OR ', $textSearch ).' ) ';
        }
        
        if (count($where) > 0) {
            $query->where($where);
        }
        //$query->group('i.id');
        $query->order($orderby);
        //echo str_replace('#_','jos',$query).'<br/>';die();
        return $query;
    }

function _buildContentWhere($ignoreFilters = array(), &$query)
    {
        $view = JFactory::getApplication()->input->get('view');
        $db                 = JFactory::getDbo();
        
        $user = JFactory::getUser();
        $userGroups = implode(',', $user->getAuthorisedViewLevels());
        
        $params = $this->getState('params');
        if (empty($params)) {
        	$params = Djcatalog2Helper::getParams();
        }
        
        $filter_featured    = $this->getState('filter.featured');
        $filter_restricted 	= $params->get('items_show_restricted', 0);
        
        $filter_catid       = $this->getState('filter.category');
        $filter_catalogue       = $this->getState('filter.catalogue');
        $filter_producerid  = $this->getState('filter.producer');
        $filter_pks         = $this->getState('filter.item_ids');
        
        $filter_parent         = $this->getState('filter.parent', 0);
        
        $filter_price_from  = $this->getState('filter.price_from');
        $filter_price_to  = $this->getState('filter.price_to');
        
        $filter_index       =  $this->getState('filter.index', false);
        
        $filter_state       = $this->getState('filter.state', '1');
        
        $filter_owner =     $this->getState('filter.owner');
        
        $filter_map_address = $this->getState('filter.map.address', false);
        $filter_map_radius  = $this->getState('filter.map.radius', false);
        $filter_map         = ($filter_map_address != false) ? true : $this->getState('filter.map');

$where = array();
        
        ///// new
        $category_subquery = 'SELECT ic.item_id '
                            .'FROM #__djc2_items_categories AS ic '
                            .'INNER JOIN #__djc2_categories AS c ON c.id=ic.category_id '
                            .'WHERE c.published = 1';
        
        if (!$user->authorise('core.admin'))
        {
        	$category_subquery .= ' AND c.access IN (' . $userGroups . ') ';
        }
        
        
        $join_subcategories = true;
        
        if (is_array($filter_catid) && !empty($filter_catid)) {
            JArrayHelper::toInteger($filter_catid);
            $category_subquery .= ' AND category_id IN ('.implode(',',$filter_catid).')';
        } else if ((int)$filter_catid >= 0) {
            if ($filter_catalogue && is_scalar($filter_catid)) {
                $category_subquery .= ' AND ic.category_id ='.(int)$filter_catid;
            } else if ((int)$filter_catid > 0) {
                $categories = Djc2Categories::getInstance(array('state'=>'1', 'access' => $userGroups));
                if ($parent = $categories->get((int)$filter_catid) ) {
                    $childrenList = array($parent->id);
                    $parent->makeChildrenList($childrenList);
                    if ($childrenList) {
                        $cids = implode(',', $childrenList);
                        $category_subquery .= ' AND ic.category_id IN ('.$cids.')';
                    } else if ($filter_catid != 0){
                        JError::raiseError( 404, JText::_("COM_DJCATALOG2_PAGE_NOT_FOUND") );
                    }
                }
            } else {
                $join_subcategories = false;
            }
        }
        if ($join_subcategories){
            $query->join('inner', '('.$category_subquery.') as category_filter ON i.id = category_filter.item_id');
        } else {
            $query->where('c.published = 1');
        }
        
        /// ------
        
        if (!in_array('producer', $ignoreFilters) && $filter_producerid > 0) {
            $where[] = 'i.producer_id = '.(int) $filter_producerid;
        }
        
        if (!in_array('price', $ignoreFilters)) {
            if ($filter_price_from > 0) {
                $where[] = '((i.price >= '. floatval(str_replace(',', '.', $filter_price_from)).' AND i.special_price = 0) OR (i.special_price > 0 AND i.special_price >= '.floatval(str_replace(',', '.', $filter_price_from)).'))';
            }
            if ($filter_price_to > 0) {
                $where[] = '((i.price <= '. floatval(str_replace(',', '.', $filter_price_to)).' AND i.special_price = 0) OR (i.special_price > 0 AND i.special_price <= '.floatval(str_replace(',', '.', $filter_price_to)).'))';
            }
        }
        
        if (!in_array('featured', $ignoreFilters) && $filter_featured > 0) {
            $where[] = 'i.featured = 1';
        }
        
        $nullDate = $db->quote($db->getNullDate());
        $date = JFactory::getDate();
        $nowDate = $db->quote($date->toSql());
        
        if ($this->getState('filter.publish_date', true)){
            $query->where('(i.publish_up = ' . $nullDate . ' OR i.publish_up <= ' . $nowDate . ')');
            $query->where('(i.publish_down = ' . $nullDate . ' OR i.publish_down >= ' . $nowDate . ')');
        }
        
        if (!in_array('atoz', $ignoreFilters) && $filter_index !== false) {
            //$where[] = ' LOWER(i.name) LIKE '.$db->quote( $db->escape( $filter_index, true ).'%', false );
            if ($filter_index === 'num') {
                $where[] = '( i.name REGEXP '.$db->quote( '^[0-9]', false ) .')';
            } else {
                $where[] = '( LOWER(i.name) LIKE '.$db->quote( $db->escape( $filter_index, true ).'%', false ) . ' COLLATE utf8_bin ' .
                        ' OR UPPER(i.name) LIKE '.$db->quote( $db->escape( $filter_index, true ).'%', false ) .' COLLATE utf8_bin )';
            }
        }
        
        if (!in_array('item_ids', $ignoreFilters) && !empty($filter_pks) && is_array($filter_pks)) {
            JArrayHelper::toInteger($filter_pks);
            $query->join('inner', '(select id from #__djc2_items where id in ('.implode(',',$filter_pks).')) AS item_pks on item_pks.id = i.id');
        }
        
        if (!in_array('owner', $ignoreFilters) && $filter_owner > 0) {
            $where[] = 'i.created_by = '.$filter_owner;
        }
        
        if ($filter_state == '1') {
            $where[] = 'i.published = 1';
        } else if ($filter_state == '-1') {
            $where[] = 'i.published = 0';
        } else if ($filter_state == '2') {
            $where[] = 'i.published = 2';
        } else if ($filter_state == '2') {
            $where[] = '(i.published = 1 OR i.published = 2)';
        }
        
        if (!$user->authorise('core.admin') && !$filter_restricted)
        {
        	$where[] = ('i.access IN (' . $userGroups . ')');
        }
        
        if ($filter_parent !== false && (string)$filter_parent != '*') {
        	$where[] = 'i.parent_id='.(int)$filter_parent;
        }
        
        if ($filter_map) {
            $where[] = '(i.latitude IS NOT NULL AND i.longitude IS NOT NULL)';
        }
        
        if ($filter_map_address) {
            require_once JPath::clean(JPATH_ROOT.'/administrator/components/com_djcatalog2/lib/geocode.php');
            
            $filter_map_radius = $this->getState('filter.map.radius', 25);
            
            $latitude = $longitude = false;
            
            if ($coords = DJCatalog2Geocode::getLocation($filter_map_address)) {
                $latitude = (!empty($coords['lat'])) ? $coords['lat'] : false;
                $longitude = (!empty($coords['lng'])) ? $coords['lng'] : false;
            }
            
            if ($latitude != false && $longitude != false) {
                $radius_coeff = 0;
                $filter_map_unit = $this->getState('filter.map.unit', 'km');
                if ($filter_map_unit == 'km') {
                    $radius_coeff = 6371;
                } else {
                    $radius_coeff = 3958;
                }
                
                $query->having($filter_map_radius.' >= ' . '('.$radius_coeff.' * ACOS(COS( RADIANS(i.latitude) ) * COS(RADIANS('.$latitude.')) * COS(RADIANS('.$longitude.')-radians(i.longitude)) + SIN(RADIANS(i.latitude)) * SIN(RADIANS('.$latitude.'))))');
            }
        }
        
        return $where;
    }
    
    function getAttributes($all = false) {
        $idx = $all ? 0 : 1;
        if (!isset(self::$_attributes[$idx])) {
            $db = JFactory::getDbo();
            $query = $db->getQuery(true);
            //$query->select('f.*, group_concat(fo.id order by fo.ordering asc separator \'|\') as options');
            $query->select('f.*');
            $query->from('#__djc2_items_extra_fields as f');
            //$query->join('LEFT', '#__djc2_items_extra_fields_options as fo ON fo.field_id=f.id');
                
            if ($all) {
                $query->where('f.published = 1');
            } else {
                $query->where('(f.visibility = 2 or f.visibility = 3) and f.published = 1');
            }
            //$query->group('f.id');
            $query->order('f.group_id asc, f.ordering asc');
            $db->setQuery($query);
            self::$_attributes[$idx] = $db->loadObjectList('id');
            
            //TODO: is it necessary?
            if (empty(self::$_attributeOptions)) {
                $optQuery = $db->getQuery(true);
                $optQuery->select('o.id, o.field_id');
                $optQuery->from('#__djc2_items_extra_fields_options AS o');
                $optQuery->order('o.field_id asc, o.ordering asc');
                
                $db->setQuery($optQuery);
                $options = $db->loadObjectList();
                
                foreach($options as $k=>$v) {
                    if (!isset(self::$_attributeOptions[$v->field_id])) {
                        self::$_attributeOptions[$v->field_id] = array();
                    }
                    self::$_attributeOptions[$v->field_id][] = $v->id;
                }
            }
            
            foreach (self::$_attributes[$idx] as $k=>$v) {
                if (isset(self::$_attributeOptions[$k])) {
                    self::$_attributes[$idx][$k]->options = implode('|', self::$_attributeOptions[$k]);
                } else {
                    self::$_attributes[$idx][$k]->options = '';
                }
            }
            
            /*
            foreach (self::$_attributes[$idx] as $k=>$v) {
                if (!isset(self::$_attributeOptions[$k])) {
                    $db->setQuery('SELECT id FROM #__djc2_items_extra_fields_options WHERE field_id='.$k.' ORDER BY ordering ASC');
                    self::$_attributeOptions[$k] = $db->loadColumn();
                }
                self::$_attributes[$idx][$k]->options = implode('|', self::$_attributeOptions[$k]);
            }*/
        }
        return self::$_attributes[$idx];
    }

$children = $category->getChildren();
        
        foreach ($children as $k=>$v) {
            $this->countChildren($v, $categoryList);
        }
        
        $subcategories = array();
        foreach ($children as $subcategory) {
            if (array_key_exists($subcategory->id, $categoryList)) {
                $subcategories[] = $subcategory;
            }
        }
        
        return $subcategories;*/
        
        $db = JFactory::getDbo();
        $parent_id = $category->id;
        
        $children = $category->getChildren();
        
        $categories = Djc2Categories::getInstance(array('state'=>'1'));
        $subcategories = array();
        
        $category_subquery = 'SELECT ic.item_id '
                .'FROM #__djc2_items_categories AS ic '
                .'INNER JOIN #__djc2_categories AS c ON c.id=ic.category_id '
                .'WHERE c.published = 1';
        
        foreach ($children as $k=> $child) {
            if ($parent = $categories->get((int)$child->id) ) {
                $childrenList = array($parent->id);
                $parent->makeChildrenList($childrenList);
                if ($childrenList) {
                    $cids = implode(',', $childrenList);
                    $child_subquery =  $category_subquery . ' AND ic.category_id IN ('.$cids.')';
                    
                    $db->setQuery('
                            SELECT COUNT(DISTINCT i.id)
                            FROM #__djc2_items AS i
                            INNER JOIN ('.$child_subquery.') AS category_filter ON i.id = category_filter.item_id
                            WHERE i.published = 1
                            ');
                    $children[$k]->item_count = $db->loadResult();
                    $subcategories[$child->id] = $children[$k];
                }
            }
            
        }
        
        return $subcategories;
    }
    
    protected function countChildren(&$node, &$countList) {
        $children = $node->getChildren();
        $node->item_count = (isset($countList[$node->id])) ? $countList[$node->id]->item_count : 0;
        if (count($children)) {
            foreach ($children as $child) {
                $node->item_count += $this->countChildren($child, $countList);
            }
        }
        
        return $node->item_count;
    }
    protected function makeCategoryTree( $id, $list, &$children, $level=0) {
        if (array_key_exists($id, $children)) {
            foreach ($children[$id] as $child)
            {
                $id = $child->id;

$pt = $child->parent_id;
                $list[$id] = $child;
                if (array_key_exists($id, $children)) {
                    $list[$id]->children = count( $children[$id] );
                }
                else {
                    $list[$id]->children = 0;
                }
                $list[$id]->level = $level;
                $list = $this->makeCategoryTree( $id, $list, $children, $level+1);
            }
                
        }
        return $list;
    }
    
    public function getIndexCount() {
        $cparams = Djcatalog2Helper::getParams();
        $params = new JRegistry();
        $params->merge($cparams);
        
        $letters_str = trim($cparams->get('atoz_letters', ''));
        $letters = array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');
        
        if (!empty($letters_str)) {
            $letters = explode(',', $letters_str);
        }
        
        $obj = array();
        
        $query = '';
        
        if ((int)$params->get('atoz_check_available', 0) == 1) {
            $db = JFactory::getDBO();
            $model = JModelLegacy::getInstance('Items', 'Djcatalog2Model');
            $state = $model->getState();
            //$params->set('product_catalogue', 0);
            //$params->set('limit_items_show', 0);
            $model->setState('params', $params);
            $model->setState('list.start', 0);
            $model->setState('list.limit', 0);
            $model->setState('list.select', 'i.id');
            $model->setState('filter.catalogue', false);
            
            $model->setState('filter.index', false);
            
            $model->setState('list.ordering', 'i.id');
            $model->setState('list.direction', 'asc');
            
            $items_query = $model->buildQuery();
             
            $select = $join = array();
            foreach ($letters as $letter) {
                if ($letter && $letter != ',') {
                    $tbljoin = 'table_'.$letter;
                    if ($letter == 'num') {
                        $select[] = ' count('.$tbljoin.'.id) as count_'.$letter;
                        $join[] = 'left join #__djc2_items as '.$tbljoin.' on '.$tbljoin.'.id = items.id and '.$tbljoin.'.name REGEXP \'^[0-9]\'';
                    } else {
                        $select[] = ' count('.$tbljoin.'.id) as count_'.$letter;
                        $join[] = 'left join #__djc2_items as '.$tbljoin.' on '.$tbljoin.'.id = items.id and lower('.$tbljoin.'.name) like \''.$letter.'%\'';
                    }
                }
            }
             
            $query .= 'SELECT '.implode(', ',$select).PHP_EOL.' FROM #__djc2_items as items '.PHP_EOL.implode(PHP_EOL,$join).' where items.id in ('.$items_query.')';
            $db->setQuery($query);
            $obj = $db->loadAssoc();

foreach ($obj as $k=>$v) {
                $justletter = str_replace('count_', '', $k);
                $obj[$justletter] = $v;
                unset($obj[$k]);
            }
        }
        
        if (empty($obj)) {
            $obj = array();
            foreach ($letters as $letter) {
                if ($letter && $letter != ',') {
                    $obj[$letter] = 1;
                }
            }
        }
        
        return $obj;
    }
    
    public function getFieldGroups($model = false) {
        $params = Djcatalog2Helper::getParams();
        $db = JFactory::getDBO();
        
        if (!$model) {
        	$model = JModelLegacy::getInstance('Items', 'Djcatalog2Model');
        }

$state = $model->getState();
        
        $model->setState('params', $params);
        $model->setState('list.start', 0);
        $model->setState('list.limit', 0);
        $model->setState('list.select', 'i.id');
        //$model->setState('filter.catalogue', true);
        $model->setState('filter.catalogue', false);
        
        $model->setState('list.ordering', 'i.id');
        $model->setState('list.direction', 'asc');
        
        $items_query = $model->buildQuery();
        
        $query = $db->getQuery(true);
        $query->select('f.*');
        $query->from('#__djc2_items_extra_fields as f');
        $query->join('inner', '#__djc2_items_groups as i on i.group_id = f.group_id');
        $query->join('inner', '('.$items_query.') as iq on iq.id = i.item_id');
        $query->where('(f.visibility = 2 or f.visibility = 3) and f.published = 1 and f.separate_column = 1');
        $query->group('f.name, f.alias, f.id, f.group_id');
        $query->order('f.group_id asc, f.ordering asc');
        $db->setQuery($query);

$groups = $db->loadObjectList();
        
        return $groups;
    }
    
    public function getSortables($model = null) {
        $params = Djcatalog2Helper::getParams();
        $db = JFactory::getDBO();
        
        if (!$model) {
        	$model = JModelLegacy::getInstance('Items', 'Djcatalog2Model');
        }
        
        $model = JModelLegacy::getInstance('Items', 'Djcatalog2Model');
        $state = $model->getState();
         
        $model->setState('params', $params);
        $model->setState('list.start', 0);
        $model->setState('list.limit', 0);
        $model->setState('list.select', 'i.id');
        //$model->setState('filter.catalogue', true);
        $model->setState('filter.catalogue', false);
        
        $model->setState('list.ordering', 'i.id');
        $model->setState('list.direction', 'asc');
    
        $items_query = $model->buildQuery();
         
        $query = $db->getQuery(true);
        $query->select('f.*');
        $query->from('#__djc2_items_extra_fields as f');
        $query->join('inner', '#__djc2_items_groups as i on i.group_id = f.group_id');
        $query->join('inner', '('.$items_query.') as iq on iq.id = i.item_id');
        //$query->where('(f.visibility = 2 or f.visibility = 3) and f.published = 1 and f.sortable = 1 and (f.type = \'text\' OR f.type = \'radio\' OR f.type = \'select\' OR f.type = \'calendar\')');
        $query->where('f.published = 1 and f.sortable = 1 and (f.type = \'text\' OR f.type = \'radio\' OR f.type = \'select\' OR f.type = \'calendar\')');
        $query->group('f.name, f.alias, f.id, f.group_id');
        $query->order('f.group_id asc, f.ordering asc');
        $db->setQuery($query);
         
        $sortables = $db->loadObjectList();
         
        return $sortables;
    }
    
}

[+] ..
[+] forms
[-] index.html [edit]
[-] item.php [edit]
[-] itemform.php [edit]
[-] items.php [edit]
[-] modfrontpage.php [edit]
[-] order.php [edit]
[-] orders.php [edit]
[-] producer.php [edit]
[-] producers.php [edit]
[-] query.php [edit]
[-] .htaccess.disabled [edit]