<?php
/*
 * simonfilters - 2.5.0 Build 0011
*/

class ModelCatalogFilter extends Model {

    public static $sent = 0;
    public static $data = null;
    public static $simonsql = '';
    public static $currentfilters=array();
    public static $debugmode=false;
    public static $language_id=0;
    public static $store_id=0;

    public function myutf8($value2convert) {
        if(function_exists('utf8_strtolower')) {
            return utf8_strtolower($value2convert);
        }else {
            return mb_strtolower($value2convert, 'UTF-8');
        }
    }

    public function getAllProductCategories() {
        $cats = array();
        $product_id = @$this->request->get["product_id"] or $product_id=0;
        if($product_id!=0) {
            $sql = "SELECT pc.category_id FROM ". DB_PREFIX ."product_to_category pc WHERE pc.product_id = '". (int)$product_id ."'";
            $query = $this->db->query($sql);
            foreach($query->rows as $row) {
                $cats[] = $row['category_id'];
            }
        }
        return $cats;
    }

    public function getCurrentIDSbyCategory() {

        $product_id = @$this->request->get["product_id"] or $product_id=0;
        $sql = "
            SELECT DISTINCT pc.product_id
            FROM ". DB_PREFIX ."product_to_category pc
            WHERE pc.category_id IN (
            SELECT category_id
            FROM ". DB_PREFIX ."product_to_category
            WHERE product_id='". (int)$product_id ."')
        ";
        $product_data = array();

        $query = $this->db->query($sql);
        foreach ($query->rows as $result) {
            $product_data[] = $result['product_id'];
        }
        return $product_data;
    }

    public function getCurrentIDS() {

        if (isset($this->request->get['filter_name'])) {
            $filter_name = $this->request->get['filter_name'];
        } else {
            $filter_name = '';
        }

        if (isset($this->request->get['filter_tag'])) {
            $filter_tag = $this->request->get['filter_tag'];
        } elseif (isset($this->request->get['filter_name'])) {
            $filter_tag = $this->request->get['filter_name'];
        } else {
            $filter_tag = '';
        }

        if (isset($this->request->get['filter_description'])) {
            $filter_description = $this->request->get['filter_description'];
        } else {
            $filter_description = '';
        }

        if (isset($this->request->get['filter_sub_category'])) {
            $filter_sub_category = $this->request->get['filter_sub_category'];
        } else {
            $filter_sub_category = '';
        }

        if (isset($this->request->get['manufacturer_id'])) {
            $manufacturer_id = $this->request->get['manufacturer_id'];
        } else {
            $manufacturer_id = "";
        }

        $data = array(
                'filter_name'         => $filter_name,
                'filter_tag'          => $filter_tag,
                'filter_description'  => $filter_description,

                'filter_sub_category' => $filter_sub_category,
                'filter_manufacturer_id' => $manufacturer_id,

        );

        switch($this->getRoute()) {
            case "product/search":

                $data['categories'] = array();

                $categories_1 = $this->model_catalog_category->getCategories(0);

                foreach ($categories_1 as $category_1) {
                    $level_2_data = array();

                    $categories_2 = $this->model_catalog_category->getCategories($category_1['category_id']);

                    foreach ($categories_2 as $category_2) {
                        $level_3_data = array();

                        $categories_3 = $this->model_catalog_category->getCategories($category_2['category_id']);

                        foreach ($categories_3 as $category_3) {
                            $level_3_data[] = array(
                                    'category_id' => $category_3['category_id'],
                                    'name'        => $category_3['name'],
                            );
                        }

                        $level_2_data[] = array(
                                'category_id' => $category_2['category_id'],
                                'name'        => $category_2['name'],
                                'children'    => $level_3_data
                        );
                    }

                    $data['categories'][] = array(
                            'category_id' => $category_1['category_id'],
                            'name'        => $category_1['name'],
                            'children'    => $level_2_data
                    );
                }
                if (isset($this->request->get['filter_category_id'])) {
                    $filter_category_id = $this->request->get['filter_category_id'];
                } else {
                    $filter_category_id = 0;
                }
                if (isset($this->request->get['filter_sub_category'])) {
                    $filter_sub_category = $this->request->get['filter_sub_category'];
                } else {
                    $filter_sub_category = '';
                }
                break;
            default:
                if (isset($this->request->get['filter_category_id'])) {
                    $filter_category_id = $this->request->get['filter_category_id'];
                } else {
                    $filter_category_id = 0;
                }
                if($filter_category_id==0)$filter_category_id=$this->getCategoryID();
                break;
        }

        $data['filter_category_id']  = $filter_category_id;

        foreach($data as $k=>$v) {
            if($v=='')unset($data[$k]);
        }

        if($this->config->get("simonfilters_dynamic")=='1') {
            $data['simonfilters']=$this->getSimonAttributesCacheData();
        }
        $cache = md5(http_build_query($data));

        $product_data = $this->cache->get('simonfilters.productids.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . $cache);
        if (!$product_data) {

            $sql = "SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";

            if (!empty($data['filter_tag'])) {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";
            }

            if (!empty($data['filter_category_id'])) {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";
            }

            $sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

            if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
                $sql .= " AND (";

                if (!empty($data['filter_name'])) {
                    $implode = array();

                    $words = explode(' ', $data['filter_name']);

                    foreach ($words as $word) {
                        if (!empty($data['filter_description'])) {

                            $implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape($this->myutf8($word)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape($this->myutf8($word)) . "%'";
                        } else {

                            $implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape($this->myutf8($word)) . "%'";
                        }
                    }

                    if ($implode) {

                        $sql .= " " . implode(" OR ", $implode) . "";
                    }
                }

                if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                    $sql .= " OR ";
                }

                if (!empty($data['filter_tag'])) {
                    $implode = array();

                    $words = explode(' ', $data['filter_tag']);

                    foreach ($words as $word) {
                        $implode[] = "LCASE(pt.tag) LIKE '%" . $this->db->escape($this->myutf8($data['filter_tag'])) . "%' AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
                    }

                    if ($implode) {
                        $sql .= " " . implode(" OR ", $implode) . "";
                    }
                }

                $sql .= ")";
            }
            if (!empty($data['filter_category_id'])) {
                if (!empty($data['filter_sub_category'])) {
                    $implode_data = array();

                    $implode_data[] = "p2c.category_id = '" . (int)$data['filter_category_id'] . "'";

                    $this->load->model('catalog/category');

                    $categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);

                    foreach ($categories as $category_id) {
                        $implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
                    }

                    $sql .= " AND (" . implode(' OR ', $implode_data) . ")";
                } else {
                    $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
                }
            }

            if (!empty($data['filter_manufacturer_id'])) {
                $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
            }

            if($this->config->get("simonfilters_dynamic")=='1') {
                $sql .= $this->getSimonAttributes();
            }

            $sql .= " GROUP BY p.product_id";
            $product_data = array();

            $query = $this->db->query($sql);
            foreach ($query->rows as $result) {
                $product_data[] = $result['product_id'];
            }
            $this->debug(__FILE__.';'.__LINE__,'currentIDS:sql:',$sql);
            $this->cache->set('simonfilters.productids.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . $cache, $product_data);
        }

        return $product_data;
    }


    /*
     * constructor
    */
    public function __construct($registry) {
        parent::__construct($registry);
        $this->load->model('setting/setting');
        $this->setting = $this->model_setting_setting->getSetting('filter');
        $this->load->model('localisation/currency');
        if($this->sent==0 && $this->isSupportedRoute()) {


            $this->language_id = (int)$this->config->get('config_language_id');
            $this->store_id = (int)$this->config->get('config_store_id');
            #$this->all_a = $this->getAllProductAttributes();

            $index = $this->getSimonIndex();
            if(isset($_GET['forcefiltersupdate'])) {
                if(isset($_GET['checkedfilters']) && $_GET['checkedfilters']!='') {
                    $this->currentfilters = $_GET['checkedfilters'];
                }else {
                    $this->currentfilters = array();
                }
                $_SESSION['simonfilters']['filters'][$index]=$this->currentfilters;
            }elseif(isset($_SESSION['simonfilters']['filters'][$index])) {
                if($this->config->get('simonfilters_persist')=='1' || isset($_GET['limit']) || isset($_GET['page'])) {
                    $this->currentfilters = $_SESSION['simonfilters']['filters'][$index];
                }else {
                    $this->currentfilters = array();
                }
            }else {
                $this->currentfilters = array();
            }

            /*
             * Debug stuff
            */
            if(defined("SIMON_DEBUG") && SIMON_DEBUG=='1')$this->debugmode=true;
            if(isset($_GET['debug'])) {
                // Comment next line if you don't want me to debug your site!
                $_SESSION['simonisdebugging']= ($_GET['debug']=='itsmesimon'?1:0);
            }
            if(isset($_SESSION['simonisdebugging']) && $_SESSION['simonisdebugging']==1) {
                $this->debugmode=true;
            }
            /*
             * Debug stuff
            */

            $this->data = $this->getFiltersByIds();
        }

    }

    public function manageCache($sql,$name) {
        $cache = md5($sql);
        $query = $this->cache->get($name. '.' . $cache);
        if (!$query) {
            $query = $this->db->query($sql);
            $this->cache->set($name .'.'. $cache, $query);
        }
        return $query;
    }

    public function getFiltersByIds() {
        $store_id = (int)$this->config->get('config_store_id');
        $language_id = $this->config->get('config_language_id');
        if($this->config->get("simonfilters_force_all")=='1') {
            $sfsids = "";
        }else {

            switch($this->getRoute()) {
                case "product/product":
                    $currentIDS = $this->getCurrentIDSbyCategory();
                    if(count($currentIDS)==0)$currentIDS[]=0;
                    $sfsids = join(',', $currentIDS);
                    break;
                default:
                    $currentIDS = $this->getCurrentIDS();
                    if(count($currentIDS)==0)$currentIDS[]=0;
                    $sfsids = join(',', $currentIDS);
                    break;
            }
        }

        $data = array();

        $activefilter_simonfilters_expanded = $this->config->get("activefilter_simonfilters_expanded");

        $activefilter_simonfilters_attribute = $this->config->get("activefilter_simonfilters_attribute");
        $activefilter_simonfilters_options = $this->config->get("activefilter_simonfilters_options");
        $activefilter_simonfilters_tags = $this->config->get("activefilter_simonfilters_tags");
        $activefilter_simonfilters_manufacturer = $this->config->get("activefilter_simonfilters_manufacturer");
        $activefilter_simonfilters_stock = $this->config->get("activefilter_simonfilters_stock");
        $activefilter_simonfilters_categories = $this->config->get("activefilter_simonfilters_categories");
        $price_config_data = $this->config->get("activefilter_simonfilters_price");
        $activefilter_simonfilters_price = isset($price_config_data[0]['a']);

        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_attribute',$activefilter_simonfilters_attribute);
        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_options',$activefilter_simonfilters_options);
        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_tags',$activefilter_simonfilters_tags);
        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_manufacturer',$activefilter_simonfilters_manufacturer);
        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_stock',$activefilter_simonfilters_stock);
        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_price',$activefilter_simonfilters_price);
        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:\$activefilter_simonfilters_categories',$activefilter_simonfilters_categories);


        /*Categories*/
        if (count($activefilter_simonfilters_categories)>0 && $this->getRoute() != 'product/category') {
            $attribute_ids = implode(',',array_keys($activefilter_simonfilters_categories));

            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                #later
                #$sql_id_products = "AND pdc.product_id IN ($sfsids)";
                $sql_id_products = "";
            }

            $sql = "
                    SELECT c.category_id, cd.name AS filter_name, 'Categories' AS filter_group_name, 1 AS group_id, '' AS total
                    FROM
                    ". DB_PREFIX ."category c
                    LEFT JOIN ". DB_PREFIX ."product_to_category pdc ON c.category_id = pdc.category_id
                    LEFT JOIN ". DB_PREFIX ."category_description cd ON c.category_id = cd.category_id AND cd.language_id=1
                    WHERE
                            c.status=1 AND c.parent_id=0 {$sql_id_products}
                    GROUP BY
                            c.category_id
                    ORDER by c.sort_order
                    ";

            $this->debug(__FILE__.';'.__LINE__,'getFiltersByIds:categories sql',$sql);
            $query = $this->manageCache($sql,'simonfilters.getCategories'. $language_id . '.' . $store_id);
            foreach($query->rows as $row) {
                $data['c'][$row['group_id']]['filter_group_name']=$row['filter_group_name'];
                $data['c'][$row['group_id']][$row['category_id']]['filter_name'][md5($row['category_id'])]=$row['filter_name'];

            }
            if($query->num_rows) {
                if(isset($activefilter_simonfilters_expanded['categories'])) {
                    $data['c'][$row['group_id']]['b']=$activefilter_simonfilters_expanded['categories'];
                }
            }
        }


        /*Attributes*/
        if (count($activefilter_simonfilters_attribute)>0) {
            $attribute_ids = implode(',',array_keys($activefilter_simonfilters_attribute));

            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                $sql_id_products = "AND pa.product_id IN ($sfsids)";
            }

            $sql = "
                SELECT
                    ad.attribute_id, ad.name AS filter_name, agd.name AS filter_group_name, ad.attribute_id as group_id, pa.text
                FROM ". DB_PREFIX ."attribute a
                    JOIN ". DB_PREFIX ."attribute_description ad ON a.attribute_id = ad.attribute_id
                    JOIN ". DB_PREFIX ."attribute_group_description agd ON agd.attribute_group_id = a.attribute_group_id
                    JOIN ". DB_PREFIX ."product_attribute pa ON a.attribute_id = pa.attribute_id
                WHERE
                    pa.text<>'' AND a.attribute_id IN($attribute_ids) AND ad.language_id='$language_id' AND agd.language_id = ad.language_id AND pa.language_id = ad.language_id
                    {$sql_id_products}
                GROUP BY a.attribute_id, ad.name, agd.name, agd.attribute_group_id, pa.text
                ORDER BY a.sort_order
                    ";
            $this->debug(__FILE__.';'.__LINE__,'getFiltersByIds:attribute sql',$sql);
            $query = $this->manageCache($sql,'simonfilters.getAttributesByIds'. $language_id . '.' . $store_id);
            $simonfilters_enable_attribute_separator = $this->config->get("simonfilters_enable_attribute_separator");

            foreach($query->rows as $row) {
                $data['a'][$row['group_id']]['filter_group_name']=$row['filter_name'];

                switch($simonfilters_enable_attribute_separator) {
                    case '1':
                        $simonfilters_attribute_separator_char = $this->config->get("simonfilters_attribute_separator_char");

                        if($simonfilters_attribute_separator_char!='') {
                            foreach(explode($simonfilters_attribute_separator_char,$row['text']) as $a) {
                                if($simonfilters_attribute_separator_char=='|')$simonfilters_attribute_separator_char='\\\\|';
                                $a = trim($a);
                                $data['a'][$row['group_id']][$row['attribute_id']]['filter_name'][md5($a)]=$a;

                                $text = $this->db->escape($a);
                                $sql = "SELECT count(pa.product_id) as total FROM ". DB_PREFIX ."product_attribute pa WHERE ";
                                $sql .= "
                                    (pa.text REGEXP '^[ ]*{$text}[ ]*{$simonfilters_attribute_separator_char}'
                                    or pa.text REGEXP '{$simonfilters_attribute_separator_char}[ ]*{$text}[ ]*{$simonfilters_attribute_separator_char}'
                                    or pa.text REGEXP '{$simonfilters_attribute_separator_char}[ ]*{$text}[ ]*$'
                                    or pa.text = '{$text}')
                                        $sql_id_products
                                    AND pa.language_id={$language_id}
                                        ";
                                $query = $this->db->query($sql);
                                $total= $query->row['total'];
                                $data['a'][$row['group_id']][$row['attribute_id']]['filter_totals'][md5($a)]=$total;
                            }
                        }else {
                            $data['a'][$row['group_id']][$row['attribute_id']]['filter_name'][md5($row['text'])]=trim($row['text']);
                            $text = trim($row['text']);
                            $sql = "SELECT count(pa.product_id) as total FROM ". DB_PREFIX ."product_attribute pa WHERE ";
                            $sql .= "
                                    (pa.text = '{$text}')
                                    $sql_id_products
                                    AND pa.language_id={$language_id}
                                    ";
                            $query = $this->db->query($sql);
                            $total= $query->row['total'];
                            $data['a'][$row['group_id']][$row['attribute_id']]['filter_totals'][md5($row['text'])]=$total;
                        }

                        break;
                    default:
                        $data['a'][$row['group_id']][$row['attribute_id']]['filter_name'][md5($row['text'])]=trim($row['text']);
                        $text = trim($row['text']);
                        $sql = "SELECT count(pa.product_id) as total FROM ". DB_PREFIX ."product_attribute pa WHERE ";
                        $sql .= "
                                    (pa.text = '{$text}')
                                $sql_id_products
                                    AND pa.language_id={$language_id}
                                ";
                        $query = $this->db->query($sql);
                        $total= $query->row['total'];
                        $data['a'][$row['group_id']][$row['attribute_id']]['filter_totals'][md5($row['text'])]=$total;
                        break;
                }



                $data['a'][$row['group_id']]['b']=$activefilter_simonfilters_attribute[$row['attribute_id']]['b'];

            }


        }

        /*Options*/
        if (count($activefilter_simonfilters_options)>0) {
            $attribute_ids = implode(',',array_keys($activefilter_simonfilters_options));

            $filter_group_name = $this->get_filter_group_name($language_id);

            #$simonfilters_option_zero_quantity
            if($this->config->get("simonfilters_option_zero_quantity")=='1') {
                $sql_option_zero_quantity = "AND pov.quantity>0";
            }else {
                $sql_option_zero_quantity = "";
            }

            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                $sql_id_products = "AND pov.product_id IN ($sfsids)";
            }

            $sql = "
                SELECT DISTINCT
                    pov.option_id as group_id ,pov.option_value_id, ov.sort_order, count(*) as total
                FROM
                    ". DB_PREFIX ."product_option_value pov
                    JOIN `". DB_PREFIX ."option` o ON pov.option_id = o.option_id
                    JOIN ". DB_PREFIX ."option_value ov on ov.option_value_id = pov.option_value_id
                WHERE
                    pov.option_id IN({$attribute_ids}) {$sql_id_products} {$sql_option_zero_quantity}
                    GROUP BY pov.option_id , pov.option_value_id
                    ";

            $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:option sql',$sql);
            $query = $this->manageCache($sql,'simonfilters.getOptionsByIds.'. $language_id . '.' . $store_id);

            foreach($query->rows as $row) {
                if(isset($row['group_id'])) {
                    if(isset($filter_group_name[$row['group_id']][$row['option_value_id']]['filter_name'])) {
                        $data['o'][$row['group_id']]['filter_group_name']=$filter_group_name[$row['group_id']][$row['option_value_id']]['filter_group_name'];
                        $data['o'][$row['group_id']][$row['option_value_id']]['filter_name'][md5($row['option_value_id'])]=$filter_group_name[$row['group_id']][$row['option_value_id']]['filter_name'];
                        $data['o'][$row['group_id']][$row['option_value_id']]['filter_totals'][md5($row['option_value_id'])]=$row['total'];
                        $data['o'][$row['group_id']]['b']=$activefilter_simonfilters_options[$row['group_id']]['b'];
                        $data['o'][$row['group_id']][$row['option_value_id']]['sort_order']=$row['sort_order'];
                    }
                }
            }


        }

        /*Tags*/

        if (count($activefilter_simonfilters_tags)>0) {
            $attribute_ids = array_keys($activefilter_simonfilters_tags);
            foreach($attribute_ids as $ak=>$av)$attribute_ids[$ak]="'". $av ."'";
            $attribute_ids = implode(',',$attribute_ids);

            $sql = "
                SELECT pt.product_tag_id
                    FROM ". DB_PREFIX ."product_tag pt
                WHERE
                    pt.tag IN (
                        SELECT pt2.tag FROM ". DB_PREFIX ."product_tag pt2 WHERE pt2.product_tag_id IN($attribute_ids) AND pt2.language_id=$language_id
                    )
                    AND pt.language_id=$language_id
                    ";


            $query = $this->manageCache($sql,'simonfilters.getTagsIdentical.'. $language_id . '.' . $store_id);

            $tags = array();
            foreach($query->rows as $row) {
                $tags[]= $row['product_tag_id'] ;
            }
            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                $sql_id_products = "AND p.product_id IN ($sfsids)";
            }

            if(count($tags)>0) {
                $tagsflat = implode(',',$tags);
                $sql = "
                SELECT pt.product_tag_id, pt.tag AS filter_name, 'Tags' AS filter_group_name, 1 AS group_id, '' AS total
                FROM ". DB_PREFIX ."product_tag pt
                JOIN ". DB_PREFIX ."product p ON pt.product_id = p.product_id
                JOIN ". DB_PREFIX ."product_to_category pc ON p.product_id = pc.product_id
                WHERE p.status=1 AND pt.product_tag_id IN({$tagsflat}) $sql_id_products AND pt.language_id={$language_id}
                GROUP BY pt.tag
                        ";
                $this->debug(__FILE__.';'.__LINE__,'getFiltersByIds:tags sql',$sql);
                $query = $this->manageCache($sql,'simonfilters.getTagsByIds.'. $language_id . '.' . $store_id);
                foreach($query->rows as $row) {
                    $data['t'][$row['group_id']]['filter_group_name']=$row['filter_group_name'];
                    $data['t'][$row['group_id']][$row['product_tag_id']]['filter_name'][md5($row['product_tag_id'])]=$row['filter_name'];
                }

                if($query->num_rows) {
                    if(isset($activefilter_simonfilters_expanded['tags'])) {
                        $data['t'][1]['b']=$activefilter_simonfilters_expanded['tags'];
                    }
                }
            }
        }


        /*manufacturers*/
        $this->load->language('module/filter');
        $manufacturerText = $this->language->get('manufacturer');

        if (count($activefilter_simonfilters_manufacturer)>0) {
            $attribute_ids = implode(',',array_keys($activefilter_simonfilters_manufacturer));

            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                $sql_id_products = "AND p.product_id IN($sfsids)";
            }

            $sql = "
                    select
                        m.name as filter_name, p.manufacturer_id, '{$manufacturerText}' as filter_group_name, 1 as group_id, '' as total
                    from
                    ". DB_PREFIX ."product p
                        JOIN ". DB_PREFIX ."manufacturer m ON p.manufacturer_id = m.manufacturer_id
                    WHERE
                        p.status=1 AND p.manufacturer_id IN($attribute_ids) {$sql_id_products}
                    GROUP BY manufacturer_id 
                    ORDER BY m.sort_order
                    ";
            $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:manufacturers sql',$sql);

            $query = $this->manageCache($sql,'simonfilters.getManufacturersByIds.'. $language_id . '.' . $store_id);

            foreach($query->rows as $row) {
                $data['m'][$row['group_id']]['filter_group_name']=$row['filter_group_name'];
                $data['m'][$row['group_id']][$row['manufacturer_id']]['filter_name'][]=$row['filter_name'];
            }
            if($query->num_rows) {
                if(isset($activefilter_simonfilters_expanded['manufacturers'])) {
                    $data['m'][1]['b']=$activefilter_simonfilters_expanded['manufacturers'];
                }
            }
        }

        /*stock*/
        $stockText = $this->language->get('stock');

        if (count($activefilter_simonfilters_stock)>0) {
            $attribute_ids = implode(',',array_keys($activefilter_simonfilters_stock));

            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                $sql_id_products = "AND p.product_id IN({$sfsids})";
            }
            $activefilter_simonfilters_stock_instock = $this->config->get("activefilter_simonfilters_stock_instock");


            $sql = "
                SELECT
                        ss.name AS filter_name, '{$stockText}' AS filter_group_name, IF(p.quantity>0, $activefilter_simonfilters_stock_instock, p.stock_status_id) AS stock_status_id, COUNT(p.product_id) AS total, 1 AS group_id
                FROM ". DB_PREFIX ."product p
                JOIN ". DB_PREFIX ."stock_status ss ON ss.stock_status_id = IF(p.quantity>0, $activefilter_simonfilters_stock_instock, p.stock_status_id) AND ss.language_id={$language_id}
                WHERE
                        p.stock_status_id IN($attribute_ids) {$sql_id_products}
                GROUP BY ss.name
                    ";

            $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:stocks sql',$sql);
            $query = $this->manageCache($sql,'simonfilters.getStockByIds.'. $language_id . '.' . $store_id);
            foreach($query->rows as $row) {
                $data['s'][$row['group_id']]['filter_group_name']=$row['filter_group_name'];
                $data['s'][$row['group_id']][$row['stock_status_id']]['filter_name'][]=$row['filter_name'];

            }
            if($query->num_rows) {
                if(isset($activefilter_simonfilters_expanded['stocks'])) {
                    $data['s'][1]['b']=$activefilter_simonfilters_expanded['stocks'];
                }
            }
        }

        /*price*/
        $priceText = $this->language->get('price');
        if ($activefilter_simonfilters_price) {

            //Needs more work. p.tax_class_id usage is a bit silly
            $activefilter_simonfilters_price_data = $this->config->get("activefilter_simonfilters_price");

            if($this->config->get("simonfilters_force_all")=="1") {
                $sql_id_products = "";
            }else {
                $sql_id_products = " WHERE p.product_id IN ($sfsids) ";
            }

            $route = $this->getRoute();
            switch($route) {
                case "product/product":
                    $catsForPriceAri = $this->getAllProductCategories();
                    $catsForPrice = join(',', $catsForPriceAri);
                    $price_filter = "WHERE p.product_id in (select pc.product_id FROM ". DB_PREFIX ."product_to_category pc WHERE pc.category_id IN(". $catsForPrice ."))";
                    break;
                case "product/category":
                    $price_filter = "WHERE p.product_id in (select pc.product_id FROM ". DB_PREFIX ."product_to_category pc WHERE pc.category_id='". $this->getCategoryID() ."')";
                    break;
                case "product/manufacturer/product":
                    $price_filter = "WHERE p.product_id in (select p.product_id FROM ". DB_PREFIX ."product p WHERE p.manufacturer_id='". $this->getManufacturerID() ."')";
                    break;
                case "product/manufacturer":
                    $price_filter = "WHERE p.product_id in (select p.product_id FROM ". DB_PREFIX ."product p WHERE p.manufacturer_id='". $this->getManufacturerID() ."')";
                    break;
                case "product/search":
                    $price_filter = "";
                    break;
                default:
                    $price_filter = "";
                    break;
            }

            $sql = "
                 (
                SELECT 'min' AS title, p.price, p.tax_class_id
                FROM ". DB_PREFIX ."product p
                    $price_filter
                ORDER BY p.price ASC
                LIMIT 1
                )UNION(
                SELECT 'min_special' AS title, p.price, 0 as tax_class_id
                FROM ". DB_PREFIX ."product_special p
                    $price_filter
                ORDER BY p.price,p.priority
                LIMIT 1
                )UNION(
                SELECT 'max' AS title, p.price, p.tax_class_id
                FROM ". DB_PREFIX ."product p
                    $price_filter
                ORDER BY p.price DESC
                LIMIT 1
                )UNION(
                SELECT 'max_special' AS title, p.price, 0 as tax_class_id
                FROM ". DB_PREFIX ."product_special p
                    $price_filter
                ORDER BY p.price DESC,p.priority
                LIMIT 1
                )
                    ";

            $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:price sql',$sql);

            $query = $this->manageCache($sql,'simonfilters.getPriceByIds.'. $language_id . '.' . $store_id);
            $rows = $query->rows;
            if (count($rows)>1) {

                $prices = array();
                if(count($rows)==4) {
                    $prices['minprice']= $this->currency->convert( $rows[0]['price'], $this->config->get('config_currency'), $this->currency->getCode()) ;
                    $prices['minprice_special']= $this->currency->convert( $rows[1]['price'], $this->config->get('config_currency'), $this->currency->getCode()) ;
                    $prices['maxprice']= $this->currency->convert( $rows[2]['price'], $this->config->get('config_currency'), $this->currency->getCode()) ;
                    $prices['maxprice_special']= $this->currency->convert( $rows[3]['price'], $this->config->get('config_currency'), $this->currency->getCode()) ;

                    if(isset($activefilter_simonfilters_price_data['special'])) {
                        if($prices['minprice_special']<$prices['minprice'])$prices['minprice']=$prices['minprice_special'];
                        if($prices['maxprice_special']>$prices['maxprice'])$prices['maxprice']=$prices['maxprice_special'];
                    }
                    $tax_min = $rows[0]['tax_class_id'];
                    $tax_max = $rows[2]['tax_class_id'];
                }else {
                    $prices['minprice']= $this->currency->convert( $rows[0]['price'], $this->config->get('config_currency'), $this->currency->getCode()) ;
                    $prices['maxprice']= $this->currency->convert( $rows[1]['price'], $this->config->get('config_currency'), $this->currency->getCode()) ;

                    $tax_min = $rows[0]['tax_class_id'];
                    $tax_max = $rows[1]['tax_class_id'];
                }

                if(isset($activefilter_simonfilters_price_data['tax'])) {
                    $prices['minprice'] = floor($this->tax->calculate($prices['minprice'], $tax_min,true));
                    $prices['maxprice'] = ceil($this->tax->calculate($prices['maxprice'], $tax_max,true));
                }
                $data['p'][1]['filter_group_name']=$priceText;
                $data['p'][1][1]['filter_name'][]='price';
                $data['p'][1]['prices']=$prices;
                #}
            }
        }

        $this->debug(__FILE__.';'.__LINE__,'getFiltersByCategory:data',$data);

        return $data;
    }

    public function isThereAnyFilterEnabled() {
        return
                (count($this->config->get("activefilter_simonfilters_attribute"))>0)
                ||(count($this->config->get("activefilter_simonfilters_options"))>0)
                ||(count($this->config->get("activefilter_simonfilters_tags"))>0)
                ||(count($this->config->get("activefilter_simonfilters_manufacturer"))>0)
                ||(count($this->config->get("activefilter_simonfilters_stock"))>0)
                ||(count($this->config->get("activefilter_simonfilters_price"))>0);
    }

    public function debug($data) {
        if($this->debugmode) {
            echo "<pre class='simondebug'>";
            foreach(func_get_args() as $arg) {
                echo ( is_array($arg)?print_r($arg,1):"<p>{$arg}</p>" );
            }
            echo "</pre>";
        }
    }

    public function getHeader($header) {
        $temp = 'HTTP_' . strtoupper(str_replace('-', '_', $header));
        if (isset($_SERVER[$temp]) && !empty($_SERVER[$temp])) {
            return $_SERVER[$temp];
        }
        if (function_exists('apache_request_headers')) {
            $headers = apache_request_headers();
            if (!empty($headers[$header])) {
                return $headers[$header];
            }
        }
        return false;
    }

    private function get_options_totals($option_value_id) {
        $totals = array();
        if(count($option_value_id)>0) {
            $store_id = (int)$this->config->get('config_store_id');
            $option_value_id_flat = implode(',',$option_value_id) ;
            $sql = "
            SELECT
            pov.option_value_id,
            count(pov.product_id) as total

            FROM
            ". DB_PREFIX ."product_option_value pov
            join ". DB_PREFIX ."product p on pov.product_id = p.product_id
            where
            pov.option_value_id in ({$option_value_id_flat})
            AND p.status=1
            group by pov.option_value_id

                    ";
            $this->debug(__FILE__.';'.__LINE__,'get_options_totals: sql',$sql);

            $cache = md5($sql);

            $option_value_id_md5 = str_replace(',','_', $option_value_id);
            $query = $this->cache->get('simonfilters.get_options_totals.'. $store_id . '.' . $cache);
            if (!$query) {
                $query = $this->db->query($sql);
                $this->cache->set('simonfilters.get_options_totals.'. $store_id . '.' . $cache, $query);
            }

            foreach($query->rows as $row) {
                $totals[$row['option_value_id']] = $row['total'];
            }
        }
        return $totals;
    }

    private function get_filter_group_name($language_id) {
        $store_id = (int)$this->config->get('config_store_id');
        $sql = "
            select
                    od.option_id
                    ,ovd.option_value_id
                    ,od.name as filter_group_name
                    ,ovd.name as filter_name
            from
                    ". DB_PREFIX ."option_description od
                    join ". DB_PREFIX ."option_value_description ovd on od.option_id = ovd.option_id
            where
                    od.language_id={$language_id}
                ";
        $this->debug(__FILE__.';'.__LINE__,'get_filter_group_name: sql',$sql);

        $cache = md5($sql);
        $query = $this->cache->get('simonfilters.get_filter_group_name.'. $language_id . '.' . $store_id . '.' . $cache);
        if (!$query) {
            $query = $this->db->query($sql);
            $this->cache->set('simonfilters.get_filter_group_name.'. $language_id . '.' . $store_id . '.' . $cache, $query);
        }
        $filter_group_name = array();
        foreach($query->rows as $row) {
            $filter_group_name[$row['option_id']][$row['option_value_id']]['filter_group_name']=$row['filter_group_name'];
            $filter_group_name[$row['option_id']][$row['option_value_id']]['filter_name']=$row['filter_name'];
        }

        return $filter_group_name;
    }

    private function getCurrencies() {
        $currencies = array();
        $results = $this->model_localisation_currency->getCurrencies();
        foreach ($results as $result) {
            if ($result['status']) {
                $currencies[] = array(
                        'code'         => $result['code']
                );
            }
        }
        return $currencies;
    }

    public function getCategoryID() {
        if (isset($this->request->get['path'])) {
            $parts = explode('_', (string)$this->request->get['path']);
        } else {
            $parts = array();
        }

        if (count($parts) > 0) {
            return $parts[count($parts)-1];
        } else {
            $product_id = isset($this->request->get["product_id"])?$this->request->get["product_id"]:0;
            if($product_id!=0) {
                $sql = "SELECT pc.category_id FROM ". DB_PREFIX ."product_to_category pc WHERE pc.product_id = '". (int)$product_id ."'";
                $query = $this->db->query($sql);
                if($query->num_rows) {
                    return $query->row['category_id'];
                }
            }

            return 0;
        }
    }

    public function getManufacturerID() {
        return (isset($this->request->get['manufacturer_id'])?$this->request->get['manufacturer_id']:0);
    }

    public function getRoute() {
        $route = isset($this->request->get['route'])?$this->request->get['route']:'homepage';
        switch($route) {
            case 'common/home':
                $route="homepage";
                break;
        }
        return $route;
    }

    public function isSupportedRoute() {
        return in_array($this->getRoute(),array(
                "homepage","product/category", "product/manufacturer/product", "product/search", "product/product"
        ));
        #return true;
    }

    public function getSimonIndex() {
        $route = $this->getRoute();
        switch($route) {
            case "product/product":
                $index = 'prod_'. $this->getCategoryID();
                break;
            case "product/category":
                $index = 'cat_'. $this->getCategoryID();
                break;
            case "product/manufacturer/product":
                $index = 'man_'. $this->getManufacturerID();
                break;
            case "product/manufacturer":
                $index = 'man_index';
                break;
            default:
                $index = 'hp';
                break;
        }
        return $index;
    }

    public function getAllProductAttributes() {
        $a = array();

        $activefilter_simonfilters_attribute = $this->config->get("activefilter_simonfilters_attribute");

        $attribute_ids = implode(',',array_keys($activefilter_simonfilters_attribute));

/*        $sql = "
                        SELECT a.attribute_id, pa.text
                        FROM ". DB_PREFIX ."attribute a
                        JOIN ". DB_PREFIX ."product_attribute pa ON a.attribute_id = pa.attribute_id
                        WHERE pa.text<>'' AND a.attribute_id IN($attribute_ids) AND pa.language_id={$this->language_id}
                        GROUP BY a.attribute_id, pa.text
                    ";
*/
        $sql = "
                        SELECT a.attribute_id, b.name as text
                        FROM ". DB_PREFIX ."attribute a,attribute_description b,
                        WHERE a.attribute_id IN($attribute_ids) 
                        and a.attribute_id=b.attribute_id
                        GROUP BY a.attribute_id
                    ";

        $query = $this->manageCache($sql,'simonfilters.getAllProductAttributes.'. $this->language_id . '.' . $this->store_id);

        foreach ($query->rows as $row) {
            $simonfilters_enable_attribute_separator = $this->config->get("simonfilters_enable_attribute_separator");
            switch($simonfilters_enable_attribute_separator) {
                case '1':
                    $simonfilters_attribute_separator_char = $this->config->get("simonfilters_attribute_separator_char");
                    if($simonfilters_attribute_separator_char!='') {
                        foreach(explode($simonfilters_attribute_separator_char,$row['text']) as $attr_item) {
                            $attr_item = trim($attr_item);
                            #if($attr_item!='')$a[$row['attribute_id']][md5($attr_item)]=$attr_item;
                            $attribute_id = $row['attribute_id'];
                            $md5_attr_item = md5($attr_item);
                            if($attr_item!='')$a["a.{$attribute_id}.{$md5_attr_item}"]=$attr_item;
                        }
                    }else {
                        $attribute_id = $row['attribute_id'];
                        $md5_text = md5(trim($row['text']));
                        if($row['text']!='')$a["a.{$attribute_id}.{$md5_text}"]=trim($row['text']);
                    }
                    break;
                default:
                    $attribute_id = $row['attribute_id'];
                    $md5_text = md5(trim($row['text']));
                    if($row['text']!='')$a["a.{$attribute_id}.{$md5_text}"]=trim($row['text']);
                    break;
            }
        }
        return $a;
    }


    /*
     *
     *
     * stuff for catalog\model\catalog\product.php
     *
     *
     *
    */

    public function getSimonAttributesCacheData() {
        return (sizeof($this->currentfilters)>0?implode(',',$this->currentfilters):'');
    }

    public function getSimonAttributes() {
        $sqlfilters = "";
        $attributeCount = 0;
        $manufacturerCount = 0;
        $route = $this->getRoute();
        $index = $this->getSimonIndex();
        $language_id = (int)$this->config->get('config_language_id');

        if($this->isSupportedRoute()) {
            if($this->sent == 0) {
                $this->simonsql = '';
                $ari = Array(
                        'a' => Array(),
                        'o' => Array(),
                        'm' => Array(),
                        's' => Array(),
                        'p' => Array()
                );

                foreach($this->currentfilters as $checkedfilter) {

                    $checkedfilterari = preg_split('/\./',$checkedfilter);
                    list($sfsfilter_type_name, $sfsfilter_id, $sfskey)=$checkedfilterari;
                    switch($sfsfilter_type_name) {
                        case 'a':
                            $a = $this->getAllProductAttributes();
                            if(array_key_exists("a.{$sfsfilter_id}.{$sfskey}", $a)) {
                                $text = $this->db->escape($a["a.{$sfsfilter_id}.{$sfskey}"]);

                                $simonfilters_enable_attribute_separator = $this->config->get("simonfilters_enable_attribute_separator");
                                $simonfilters_attribute_separator_char = $this->config->get("simonfilters_attribute_separator_char");

                                if($simonfilters_attribute_separator_char=='|')$simonfilters_attribute_separator_char='\\\\|';

                                if($simonfilters_enable_attribute_separator && $simonfilters_attribute_separator_char!='') {
                                    $ari[$sfsfilter_type_name][$sfsfilter_id][]=" (
                                        text REGEXP '^[ ]*{$text}[ ]*{$simonfilters_attribute_separator_char}'
                                        or text REGEXP '{$simonfilters_attribute_separator_char}[ ]*{$text}[ ]*{$simonfilters_attribute_separator_char}'
                                        or text REGEXP '{$simonfilters_attribute_separator_char}[ ]*{$text}[ ]*$'
                                        or text = '{$text}'
                                    )";
                                }else {
                                    $ari[$sfsfilter_type_name][$sfsfilter_id][]=" (text = '{$text}')";
                                }
                            }else {
                                $this->debug(__FILE__.';'.__LINE__,"Error",$sfsfilter_type_name.'.'.$sfsfilter_id.'.'.$sfskey);
                            }
                            break;
                        case 'o':
                            $ari[$sfsfilter_type_name][]=$sfsfilter_id;
                            break;
                        case 't':
                            $ari[$sfsfilter_type_name][]="'$sfskey'";
                            break;
                        case 'm':
                            $ari[$sfsfilter_type_name][]=$sfsfilter_id;
                            break;
                        case 's':
                            $ari['s'][]=$sfsfilter_id;
                            break;
                        case 'p':
                            $sfskeyAri = preg_split('/,/',$sfskey);
                            $ari[$sfsfilter_type_name]=$sfskeyAri;
                            break;
                        case 'c':
                            $ari[$sfsfilter_type_name][]=$sfsfilter_id;
                            break;
                    }
                }

                foreach($ari as $k => $attrAri) {
                    switch($k) {
                        case 'p':

                            if(count($attrAri)>0 && count($attrAri)==2) {
                                $defaultcurrency = $this->config->get('config_currency');
                                $currentcurrency = $this->currency->getCode();

                                $localeconv = localeconv();
                                $local_decimal_point = $localeconv['decimal_point'];
                                $attrAri[0] = preg_replace('/SFS/',$local_decimal_point ,$attrAri[0]);
                                $attrAri[1] = preg_replace('/SFS/',$local_decimal_point ,$attrAri[1]);

                                $minprice_slider = $this->currency->convert( $attrAri[0], $currentcurrency, $defaultcurrency);
                                $maxprice_slider = $this->currency->convert( $attrAri[1], $currentcurrency, $defaultcurrency);

                                $activefilter_simonfilters_price = $this->config->get("activefilter_simonfilters_price");

                                if(isset($activefilter_simonfilters_price['tax'])) {

                                    if(isset($this->data['p'])) {
                                        $minpriceclean = $this->data['p']['1']['prices']['minprice'];
                                        $maxpriceclean = $this->data['p']['1']['prices']['maxprice'];
                                    }else {
                                        $minpriceclean = $minprice_slider;
                                        $maxpriceclean = $maxprice_slider;
                                    }
                                    $minpricecleantax = $this->tax->calculate($minpriceclean, 9,true);
                                    $maxpricecleantax = $this->tax->calculate($maxpriceclean, 9,true);

                                    $this->debug(__FILE__.';'.__LINE__,"minprice_slider",$minprice_slider, "maxprice_slider", $maxprice_slider);

                                    if($minpriceclean==0)$minpriceclean=1;
                                    if($maxpriceclean==0)$maxpriceclean=1;

                                    if($minpricecleantax==0)$minpricecleantax=1;
                                    if($maxpricecleantax==0)$maxpricecleantax=1;

                                    $minprice_slider = floor($minprice_slider * $minpriceclean / $minpricecleantax);
                                    $maxprice_slider = ceil( $maxprice_slider * $maxpriceclean / $maxpricecleantax);

                                    $this->debug(__FILE__.';'.__LINE__,"minprice_slider",$minprice_slider, "maxprice_slider", $maxprice_slider);
                                    $this->debug(__FILE__.';'.__LINE__,"minpriceclean",$minpriceclean, "maxpriceclean", $maxpriceclean);

                                }
                                $this->simonsql .= "p.price BETWEEN $minprice_slider AND $maxprice_slider AND ";

                            }
                            break;
                        case 's':
                            if(count($attrAri)>0) {
                                $this->simonsql .= "(";
                                foreach($attrAri as $attr) {
                                    if($attr==$this->config->get("activefilter_simonfilters_stock_instock")) {
                                        $this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product WHERE (quantity>0)) AND ";
                                    }else {
                                        $this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product WHERE (quantity=0 AND stock_status_id = '{$attr}')) AND ";
                                    }
                                }
                                $this->simonsql=$this->simonsql!=''?preg_replace('/(AND$|AND $)/',' ',$this->simonsql):'';
                                $this->simonsql .= ") AND ";
                            }
                            break;
                        case 'a':
                            $x="";
                            foreach($attrAri as $attribute_id => $attr) {
                                $x .= "p.product_id IN (SELECT DISTINCT product_id FROM ". DB_PREFIX ."product_attribute WHERE (attribute_id='{$attribute_id}' AND (";
                                if ($this->config->get('activefilter_simonfilters_attributes_behavior')=='1') {
                                    $flatari = join($attr,' OR ');
                                    $x .= $flatari;
                                }else {
                                    $flatari = join($attr,' AND ');
                                    $x .= $flatari;
                                }
                                $x.=") )) AND ";
                            }
                            $this->simonsql .= $x;
                            break;
                        case 'o':
                            $flatari = join($attrAri,',');
                            if ($this->config->get('activefilter_simonfilters_options_behavior')=='1') {
                                if($flatari)$this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product_option_value WHERE (option_value_id IN({$flatari})) GROUP BY product_id HAVING COUNT(*)>=". count($attrAri).") AND ";
                            }else {
                                if($flatari)$this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product_option_value WHERE (option_value_id IN({$flatari}))) AND ";
                            }
                            break;
                        case 't':
                            $flatari = join($attrAri,',');
                            if($flatari)$this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product_tag pt WHERE md5(tag) IN({$flatari})) AND ";
                            break;
                        case 'm':
                            $flatari = join($attrAri,',');
                            if($flatari)$this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product WHERE (p.manufacturer_id IN({$flatari}))) AND ";
                            break;
                        case 'c':
                            $flatari = join($attrAri,',');
                            if($flatari)$this->simonsql .= "p.product_id IN (SELECT product_id FROM ". DB_PREFIX ."product_to_category ptc WHERE (ptc.category_id IN({$flatari}))) AND ";
                            break;
                    }
                }
                $this->simonsql=$this->simonsql!=''?" AND ". preg_replace('/(AND$|AND $)/',' ',$this->simonsql):'';
                $this->debug(__FILE__.';'.__LINE__,"simonsql", $this->simonsql);
            }
        }
        $this->sent = 1;

        return $this->simonsql;
    }
}

?>