<?php

class ModelCatalogProduct extends Model {

    public function getCustomFieldOptionId($pro_id, $id) {
        $result = $this->db->query("SELECT option_id FROM " . DB_PREFIX . "wk_custom_field_product_options WHERE fieldId = '" . (int) $id . "' AND product_id = '" . (int) $pro_id . "' ")->rows;
        return $result;
    }

    public function getProductCustomFields($id) {
        $result = $this->db->query("SELECT fieldId FROM " . DB_PREFIX . "wk_custom_field_product WHERE productId = '" . (int) $id . "' ")->rows;
        return $result;
    }

    public function getCustomFieldName($id) {
        $result = $this->db->query("SELECT fieldName FROM " . DB_PREFIX . "wk_custom_field_description WHERE fieldId = '" . (int) $id . "' AND language_id = '" . (int) $this->config->get('config_language_id') . "' ")->row;
        return $result['fieldName'];
    }

    public function getCustomFieldOption($id) {
        $result = $this->db->query("SELECT optionValue FROM " . DB_PREFIX . "wk_custom_field_option_description WHERE optionId = '" . (int) $id . "' AND language_id = '" . (int) $this->config->get('config_language_id') . "' ")->row;
        return $result['optionValue'];
    }

    public function updateViewed($product_id) {
        $this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int) $product_id . "'");
    }

    public function getProduct($product_id) {
        $product_array = false;
        if (is_array($product_id)) {
            $product_id = join("','", $product_id);
            $product_array = true;
        }


        $query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM ps_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,
        (SELECT price FROM ps_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special,
        (SELECT points FROM ps_product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "') AS reward,
        (SELECT ss.name FROM ps_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int) $this->config->get('config_language_id') . "') AS stock_status,
        (SELECT wcd.unit FROM ps_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int) $this->config->get('config_language_id') . "') AS weight_class,
        (SELECT lcd.unit FROM ps_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int) $this->config->get('config_language_id') . "') AS length_class,
        (SELECT AVG(rating) AS total FROM ps_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating,
        (SELECT COUNT(*) AS total FROM ps_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews,
        p.sort_order FROM ps_product p
        LEFT JOIN ps_product_description pd ON (p.product_id = pd.product_id)
        LEFT JOIN ps_product_to_store p2s ON (p.product_id = p2s.product_id)
        LEFT JOIN ps_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
        WHERE p.product_id " . ($product_array ? "IN('" . $product_id . "')" : "=" . (int) $product_id) . " AND 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 ($query->num_rows) {
            if ($query->num_rows > 1) {
                foreach ($query->rows as $query->row) {
                    if (!isset($query->row['currency_id'])) {
                        $query->row['currency_id'] = null;
                    }
                    $data[] = array(
                        'product_id' => $query->row['product_id'],
                        'name' => $query->row['name'],
                        'description' => $query->row['description'],
                        'meta_title' => $query->row['meta_title'],
                        'meta_description' => $query->row['meta_description'],
                        'meta_keyword' => $query->row['meta_keyword'], 'custom_imgtitle' => $query->row['custom_imgtitle'], 'custom_h2' => $query->row['custom_h2'], 'custom_h1' => $query->row['custom_h1'], 'custom_alt' => $query->row['custom_alt'],
                        'tag' => $query->row['tag'],
                        'model' => $query->row['model'],
                        'barcode' => $query->row['barcode'],
                        'sku' => $query->row['sku'],
                        'upc' => $query->row['upc'],
                        'ean' => $query->row['ean'],
                        'jan' => $query->row['jan'],
                        'isbn' => $query->row['isbn'],
                        'mpn' => $query->row['mpn'],
                        'location' => $query->row['location'],
                        'product_type' => $query->row['product_type'],
                        'quantity' => $query->row['quantity'],
                        'stock_status' => $query->row['stock_status'],
                        'image' => $query->row['image'],
                        'media' => $query->row['media'],
                        'pattern' => $query->row['pattern'],
                        'mask' => $query->row['mask'],
                        'manufacturer_id' => $query->row['manufacturer_id'],
                        'manufacturer' => $query->row['manufacturer'],
                        'price' => $this->currency->convert(($query->row['discount'] ? $query->row['discount'] : $query->row['price']), $this->currency->getCodeOrDefault($query->row['currency_id'], $this->session->data['currency']), $this->currency->getDefaultcurrency()),
                        'currency_id' => $query->row['currency_id'],
                        'special' => $this->currency->convert($query->row['special'], $this->currency->getCodeOrDefault($query->row['currency_id'], $this->session->data['currency']), $this->currency->getDefaultcurrency()),
                        'reward' => $query->row['reward'],
                        'points' => $query->row['points'],
                        'tax_class_id' => $query->row['tax_class_id'],
                        'date_available' => $query->row['date_available'],
                        'weight' => $query->row['weight'],
                        'weight_class_id' => $query->row['weight_class_id'],
                        'length' => $query->row['length'],
                        'width' => $query->row['width'],
                        'height' => $query->row['height'],
                        'length_class_id' => $query->row['length_class_id'],
                        'subtract' => $query->row['subtract'],
                        'rating' => round($query->row['rating']),
                        'reviews' => $query->row['reviews'] ? $query->row['reviews'] : 0,
                        'minimum' => $query->row['minimum'],
                        'sort_order' => $query->row['sort_order'],
                        'status' => $query->row['status'],
                        'date_added' => $query->row['date_added'],
                        'date_modified' => $query->row['date_modified'],
                        'viewed' => $query->row['viewed'],
                        'customer_id' => false
                    );
                }
                return $data;
            } else {
                if (!isset($query->row['currency_id'])) {
                    $query->row['currency_id'] = null;
                }
                return array(
                    'product_id' => $query->row['product_id'],
                    'name' => $query->row['name'],
                    'description' => $query->row['description'],
                    'meta_title' => $query->row['meta_title'],
                    'meta_description' => $query->row['meta_description'],
                    'meta_keyword' => $query->row['meta_keyword'], 'custom_imgtitle' => $query->row['custom_imgtitle'], 'custom_h2' => $query->row['custom_h2'], 'custom_h1' => $query->row['custom_h1'], 'custom_alt' => $query->row['custom_alt'],
                    'tag' => $query->row['tag'],
                    'model' => $query->row['model'],
                    'barcode' => $query->row['barcode'],
                    'sku' => $query->row['sku'],
                    'upc' => $query->row['upc'],
                    'ean' => $query->row['ean'],
                    'jan' => $query->row['jan'],
                    'isbn' => $query->row['isbn'],
                    'mpn' => $query->row['mpn'],
                    'location' => $query->row['location'],
                    'product_type' => $query->row['product_type'],
                    'quantity' => $query->row['quantity'],
                    'stock_status' => $query->row['stock_status'],
                    'image' => $query->row['image'],
                    'media' => $query->row['media'],
                    'pattern' => $query->row['pattern'],
                    'mask' => $query->row['mask'],
                    'manufacturer_id' => $query->row['manufacturer_id'],
                    'manufacturer' => $query->row['manufacturer'],
                    'price' => $this->currency->convert(($query->row['discount'] ? $query->row['discount'] : $query->row['price']), $this->currency->getCodeOrDefault($query->row['currency_id'], $this->session->data['currency']), $this->currency->getDefaultcurrency()),
                    'currency_id' => $query->row['currency_id'],
                    'special' => $this->currency->convert($query->row['special'], $this->currency->getCodeOrDefault($query->row['currency_id'], $this->session->data['currency']), $this->currency->getDefaultcurrency()),
                    'reward' => $query->row['reward'],
                    'points' => $query->row['points'],
                    'tax_class_id' => $query->row['tax_class_id'],
                    'date_available' => $query->row['date_available'],
                    'weight' => $query->row['weight'],
                    'weight_class_id' => $query->row['weight_class_id'],
                    'length' => $query->row['length'],
                    'width' => $query->row['width'],
                    'height' => $query->row['height'],
                    'length_class_id' => $query->row['length_class_id'],
                    'subtract' => $query->row['subtract'],
                    'rating' => round($query->row['rating']),
                    'reviews' => $query->row['reviews'] ? $query->row['reviews'] : 0,
                    'minimum' => $query->row['minimum'],
                    'sort_order' => $query->row['sort_order'],
                    'status' => $query->row['status'],
                    'date_added' => $query->row['date_added'],
                    'date_modified' => $query->row['date_modified'],
                    'viewed' => $query->row['viewed'],
                    'customer_id' => false
                );
            }
        } else {
            return false;
        }
    }

    public function getTotalProducts($data = array()) {
        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
            } else {
                $sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
            } else {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
            }
        } else {
            $sql .= " FROM " . DB_PREFIX . "product p";
        }

        $sql .= " 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) 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_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND cp.path_id = '" . (int) $data['filter_category_id'] . "'";
            } else {
                $sql .= " AND p2c.category_id = '" . (int) $data['filter_category_id'] . "'";
            }

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

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int) $filter_id;
                }

                $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
            }
        }

        if (!empty($data['brand'])) {
            $sql .= " AND p.manufacturer_id IN (" . ltrim($data['brand'], ",") . ") ";
        }

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

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

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }

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

                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

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

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

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));

                foreach ($words as $word) {
                    $implode[] = "pd.tag LIKE '%" . $this->db->escape($word) . "%'";
                }

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

            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }

            $sql .= ")";
        }

        if ($this->config->get('config_instock')) {
            $sql .= " AND p.quantity > 0 ";
        }

        if ($this->config->get('advertisement_system_status')) {
            $sql .= " AND p.product_id != '" . $this->config->get('advertisement_product_id') . "'";
        }
        if (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int) $data['filter_manufacturer_id'] . "'";
        }

        if (isset($data['product_showcase'])) {
            $sql .= " AND p.product_showcase = '" . (int) $data['product_showcase'] . "'";
        }

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

        return $query->row['total'];
    }

    public function getProducts($data = array()) {
        $sql = "SELECT p.product_id,
        (SELECT AVG(rating) AS total FROM ps_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating,
        (SELECT price FROM ps_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,
        (SELECT price FROM ps_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " FROM ps_category_path cp
                LEFT JOIN ps_product_to_category p2c ON (cp.category_id = p2c.category_id)";
            } else {
                $sql .= " FROM ps_product_to_category p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN ps_product_filter pf ON (p2c.product_id = pf.product_id)
                LEFT JOIN ps_product p ON (pf.product_id = p.product_id)";
            } else {
                $sql .= " LEFT JOIN ps_product p ON (p2c.product_id = p.product_id)";
            }
        } else {
            $sql .= " FROM ps_product p";
        }

        $sql .= " LEFT JOIN ps_product_description pd ON (p.product_id = pd.product_id)
        LEFT JOIN ps_product_to_store p2s ON (p.product_id = p2s.product_id)
        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_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND cp.path_id = '" . (int) $data['filter_category_id'] . "'";
            } else {
                $sql .= " AND p2c.category_id = '" . (int) $data['filter_category_id'] . "'";
            }

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

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int) $filter_id;
                }

                $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
            }
        }

        if (!empty($data['brand'])) {
            $sql .= " AND p.manufacturer_id IN (" . ltrim($data['brand'], ",") . ") ";
        }

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

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

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }

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

                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

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

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

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));

                foreach ($words as $word) {
                    $implode[] = "pd.tag LIKE '%" . $this->db->escape($word) . "%'";
                }

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

            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }

            $sql .= ")";
        }

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

        if (isset($data['product_showcase'])) {
            $sql .= " AND p.product_showcase = '" . (int) $data['product_showcase'] . "'";
        }

        if ($this->config->get('config_instock')) {
            $sql .= " AND p.quantity > 0 ";
        }

        $sql .= " GROUP BY p.product_id";

        $sort_data = array(
            'pd.name',
            'p.model',
            'p.quantity',
            'p.price',
            'rating',
            'p.sort_order',
            'p.date_added'
        );

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } elseif ($data['sort'] == 'p.price') {
                $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.sort_order";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.name) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.name) ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
        }

        $product_data = array();

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

        foreach ($query->rows as $result) {
            $product_ids[] = $result['product_id'];
        }

        $products = $this->getProduct($product_ids);

        foreach ($products as $product) {
            $product_data[$product['product_id']] = $product;
        }
        return $product_data;
    }

    public function getProductSpecials($data = array()) {
        $sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) 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) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) GROUP BY ps.product_id";

        $sort_data = array(
            'pd.name',
            'p.model',
            'ps.price',
            'rating',
            'p.sort_order'
        );

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.sort_order";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.name) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.name) ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
        }

        $product_data = array();

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

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



        return $product_data;
    }

    public function getLatestProducts($limit) {
        $product_data = $this->cache->get('product.latest.' . (int) $this->config->get('config_language_id') . '.' . (int) $this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int) $limit);

        if (!$product_data) {
            $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "' ORDER BY p.date_added DESC LIMIT " . (int) $limit);

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

            $this->cache->set('product.latest.' . (int) $this->config->get('config_language_id') . '.' . (int) $this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int) $limit, $product_data);
        }

        return $product_data;
    }

    public function getPopularProducts($limit) {
        $product_data = $this->cache->get('product.popular.' . (int) $this->config->get('config_language_id') . '.' . (int) $this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int) $limit);

        if (!$product_data) {
            $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "' ORDER BY p.viewed DESC, p.date_added DESC LIMIT " . (int) $limit);

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

            $this->cache->set('product.popular.' . (int) $this->config->get('config_language_id') . '.' . (int) $this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int) $limit, $product_data);
        }

        return $product_data;
    }

    public function getBestSellerProducts($limit) {
        $product_data = $this->cache->get('product.bestseller.' . (int) $this->config->get('config_language_id') . '.' . (int) $this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int) $limit);

        if (!$product_data) {
            $product_data = array();

            $query = $this->db->query("SELECT op.product_id, SUM(op.quantity) AS total FROM ps_order_product op
            LEFT JOIN ps_order o ON (op.order_id = o.order_id)
            LEFT JOIN ps_product p ON (op.product_id = p.product_id)
            LEFT JOIN ps_product_to_store p2s ON (p.product_id = p2s.product_id)
            WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int) $limit);

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

            $this->cache->set('product.bestseller.' . (int) $this->config->get('config_language_id') . '.' . (int) $this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int) $limit, $product_data);
        }

        return $product_data;
    }

    public function getProductAttributes($product_id) {
        $product_attribute_group_data = array();

        $product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM ps_product_attribute pa
        LEFT JOIN ps_attribute a ON (pa.attribute_id = a.attribute_id)
        LEFT JOIN ps_attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id)
        LEFT JOIN ps_attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id)
        WHERE pa.product_id = '" . (int) $product_id . "' AND agd.language_id = '" . (int) $this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");

        foreach ($product_attribute_group_query->rows as $product_attribute_group) {
            $product_attribute_data = array();

            $product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM ps_product_attribute pa
            LEFT JOIN ps_attribute a ON (pa.attribute_id = a.attribute_id)
            LEFT JOIN ps_attribute_description ad ON (a.attribute_id = ad.attribute_id)
            WHERE pa.product_id = '" . (int) $product_id . "' AND a.attribute_group_id = '" . (int) $product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int) $this->config->get('config_language_id') . "' AND pa.language_id = '" . (int) $this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");

            foreach ($product_attribute_query->rows as $product_attribute) {
                $product_attribute_data[] = array(
                    'attribute_id' => $product_attribute['attribute_id'],
                    'name' => $product_attribute['name'],
                    'text' => $product_attribute['text']
                );
            }

            $product_attribute_group_data[] = array(
                'attribute_group_id' => $product_attribute_group['attribute_group_id'],
                'name' => $product_attribute_group['name'],
                'attribute' => $product_attribute_data
            );
        }

        return $product_attribute_group_data;
    }

    public function getProductAttribute($product_id, $collection_attr_g_id) {
        return $this->db->query("SELECT a.attribute_id FROM ps_product_attribute pa
        LEFT JOIN ps_attribute a ON (pa.attribute_id = a.attribute_id)
        WHERE pa.product_id = '" . (int) $product_id . "' AND a.attribute_group_id = '" . $collection_attr_g_id . "' ")->row['attribute_id'];
    }

    public function getProductByAttributeId($collection_attr_g_id, $attribute_id, $product_id) {

        $product_id = $this->db->query("SELECT pa.product_id FROM ps_attribute a
            LEFT JOIN ps_product_attribute pa ON (a.attribute_id = pa.attribute_id)
            WHERE pa.attribute_id = '" . $attribute_id . "' AND a.attribute_group_id = '" . $collection_attr_g_id . "' AND pa.product_id = '" . $product_id . "' ")->row['product_id'];

        return $this->getProduct($product_id);
    }

    public function getProductAttributesByCategoryId($attribute_group_id, $category_id, $product_id, $model) {
        $sql = "SELECT ad.name, a.attribute_id, a.image, a.attribute_group_id FROM ps_product_attribute pa
        LEFT JOIN ps_attribute a ON (pa.attribute_id = a.attribute_id)
        LEFT JOIN ps_attribute_description ad ON (a.attribute_id = ad.attribute_id) ";

        $sql .= " WHERE ad.language_id = 1  ";
        $sql .= " AND a.attribute_group_id = '" . (int) $attribute_group_id . "' ";
        $sql .= " AND pa.product_id IN (SELECT p.product_id FROM ps_product_to_category p2c LEFT JOIN ps_product p ON (p2c.product_id = p.product_id) WHERE p.status = 1 AND p.model = '" . $this->db->escape($model) . "' AND p2c.category_id = '" . (int) $category_id . "' ) ";
        $sql .= " AND pa.product_id ";
        $sql .= " GROUP BY a.attribute_id ";

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

    public function getProductAttributesByOtherAttributeId($attribute_id, $attribute_group_id, $category_id, $model) {
        $sql = "SELECT p.product_id FROM ps_product_to_category p2c
            LEFT JOIN ps_product p ON (p2c.product_id = p.product_id)
            WHERE p.status = 1 AND p.model = '" . $this->db->escape($model) . "' AND p2c.category_id = '" . (int) $category_id . "' GROUP BY p.product_id ";

        $sql2 = "SELECT pa.product_id FROM ps_product_attribute pa WHERE pa.attribute_id = '" . (int) $attribute_id . "' AND pa.product_id IN (" . $sql . ") ";


        $sql = "SELECT ad.name, a.attribute_id, a.image, a.attribute_group_id, pa.product_id FROM ps_product_attribute pa
        LEFT JOIN ps_attribute a ON (pa.attribute_id = a.attribute_id)
        LEFT JOIN ps_attribute_description ad ON (a.attribute_id = ad.attribute_id) ";

        $sql .= " WHERE ad.language_id = 1  ";
        $sql .= " AND a.attribute_group_id = '" . (int) $attribute_group_id . "' ";
        $sql .= " AND pa.product_id IN (" . $sql2 . ") ";
        $sql .= " GROUP BY a.attribute_id ";

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

    public function getProductOptionValueThumb($product_id) {
        $query = $this->db->query("SELECT * FROM ps_product_option_value WHERE product_id = '" . (int) $product_id . "' ORDER BY product_option_value_id ASC ");
        foreach ($query->rows as $value) {
            if (isset($value['option_thumb_image']) AND $value['option_thumb_image']) {
                $option_thumb_image = json_decode($value['option_thumb_image'], true);
                if ($option_thumb_image) {
                    return $option_thumb_image[0];
                }
            }
        }
    }

    public function getProductOptionValueThumbs($product_id) {
        $images = array();
        $query = $this->db->query("SELECT * FROM ps_product_option_value WHERE product_id = '" . (int) $product_id . "' ORDER BY product_option_value_id ASC ");
        foreach ($query->rows as $value) {
            if (isset($value['option_thumb_image']) AND $value['option_thumb_image']) {
                $option_thumb_image = json_decode($value['option_thumb_image'], true);
                foreach ($option_thumb_image as $thumb_image) {
                    if ($thumb_image) {
                        $images[] = $thumb_image;
                    }
                }
            }
        }
        unset($images[0]);
        $images = array_unique($images);
        return $images;
    }

    public function getProductSubOptions($product_id) {
        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getCustomerGroupId();
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
        }

        $product_option_data = array();

        $product_option_query = $this->db->query("SELECT * FROM ps_product_option po
        LEFT JOIN ps_option o ON (po.option_id = o.option_id)
        LEFT JOIN ps_option_description od ON (o.option_id = od.option_id)
        WHERE po.product_id = '" . (int) $product_id . "'
        AND od.language_id = '" . (int) $this->config->get('config_language_id') . "'
        AND o.sub_option != '0'
        ORDER BY o.sort_order");

        foreach ($product_option_query->rows as $product_option) {
            $product_option_value_data = array();

            $product_option_value_query = $this->db->query("SELECT * FROM ps_product_option_value pov
            LEFT JOIN ps_option_value ov ON (pov.option_value_id = ov.option_value_id)
            LEFT JOIN ps_option_value_description ovd ON (ov.option_value_id = ovd.option_value_id)
            WHERE pov.product_id = '" . (int) $product_id . "'
            AND pov.product_option_id = '" . (int) $product_option['product_option_id'] . "'
            AND  pov.customer_group_id = '" . (int) $customer_group_id . "'
            AND ovd.language_id = '" . (int) $this->config->get('config_language_id') . "'
            AND pov.sub_option_id != '0'
            GROUP BY ov.option_value_id ORDER BY ov.sort_order  ");

            foreach ($product_option_value_query->rows as $product_option_value) {

                $product_sub_option_value_data = array();

                $product_sub_option_value_query = $this->db->query("SELECT * FROM ps_product_option_value pov
                    LEFT JOIN ps_option_value_description ovd ON (pov.sub_option_value_id = ovd.option_value_id)
                    WHERE pov.product_id = '" . (int) $product_id . "'
                    AND pov.sub_option_id != '0'
                    AND pov.product_option_id = '" . (int) $product_option['product_option_id'] . "'
                    AND pov.customer_group_id = '" . (int) $customer_group_id . "'
                    AND ovd.language_id = '" . (int) $this->config->get('config_language_id') . "'
                    AND pov.option_value_id = '" . (int) $product_option_value['option_value_id'] . "'
                    GROUP BY pov.product_option_value_id");

                foreach ($product_sub_option_value_query->rows as $product_sub_option_value) {
                    $product_sub_option_value_data[] = array(
                        'product_option_value_id' => $product_sub_option_value['product_option_value_id'],
                        'option_value_id' => $product_sub_option_value['option_value_id'],
                        'name' => $product_sub_option_value['name'],
                        'image' => $product_sub_option_value['option_thumb_image'],
                        'quantity' => $product_sub_option_value['quantity'],
                        'subtract' => $product_sub_option_value['subtract'],
                        'price' => $product_sub_option_value['price'],
                        'price_prefix' => $product_sub_option_value['price_prefix'],
                        'customer_group_id' => $product_sub_option_value['customer_group_id'],
                        'weight' => $product_sub_option_value['weight'],
                        'weight_prefix' => $product_sub_option_value['weight_prefix'],
                    );
                }

                $product_option_value_data[] = array(
                    'product_option_value_id' => $product_option_value['product_option_value_id'],
                    'option_value_id' => $product_option_value['option_value_id'],
                    'name' => $product_option_value['name'],
                    'product_sub_option_value' => $product_sub_option_value_data
                );
            }

            $product_option_data[] = array(
                'product_option_id' => $product_option['product_option_id'],
                'product_option_value' => $product_option_value_data,
                'option_id' => $product_option['option_id'],
                'sub_option' => $product_option['sub_option'],
                'name' => $product_option['name'],
                'type' => $product_option['type'],
                'value' => $product_option['value'],
                'required' => $product_option['required']
            );
        }

        return $product_option_data;
    }

    public function getProductOptions($product_id) {

        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getCustomerGroupId();
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
        }

        $product_option_data = array();

        $product_option_query = $this->db->query("SELECT * FROM ps_product_option po
        LEFT JOIN ps_option o ON (po.option_id = o.option_id)
        LEFT JOIN ps_option_description od ON (o.option_id = od.option_id)
        WHERE po.product_id = '" . (int) $product_id . "'
        AND od.language_id = '" . (int) $this->config->get('config_language_id') . "'
        AND o.sub_option = '0'
        ORDER BY o.sort_order");

        foreach ($product_option_query->rows as $product_option) {
            $product_option_value_data = array();

            $product_option_value_query = $this->db->query("SELECT * FROM ps_product_option_value pov
            LEFT JOIN ps_option_value ov ON (pov.option_value_id = ov.option_value_id)
            LEFT JOIN ps_option_value_description ovd ON (ov.option_value_id = ovd.option_value_id)
            WHERE pov.product_id = '" . (int) $product_id . "'
            AND pov.product_option_id = '" . (int) $product_option['product_option_id'] . "'
            AND  pov.customer_group_id = '" . (int) $customer_group_id . "'
            AND ovd.language_id = '" . (int) $this->config->get('config_language_id') . "'
            AND pov.sub_option_id = '0'
            ORDER BY ov.sort_order");

            foreach ($product_option_value_query->rows as $product_option_value) {
                $product_option_value_data[] = array(
                    'product_option_value_id' => $product_option_value['product_option_value_id'],
                    'option_value_id' => $product_option_value['option_value_id'],
                    'name' => $product_option_value['name'],
                    'image' => $product_option_value['option_thumb_image'],
                    'quantity' => $product_option_value['quantity'],
                    'subtract' => $product_option_value['subtract'],
                    'price' => $product_option_value['price'],
                    'price_prefix' => $product_option_value['price_prefix'],
                    'customer_group_id' => $product_option_value['customer_group_id'],
                    'weight' => $product_option_value['weight'],
                    'weight_prefix' => $product_option_value['weight_prefix']
                );
            }

            $product_option_data[] = array(
                'product_option_id' => $product_option['product_option_id'],
                'product_option_value' => $product_option_value_data,
                'option_id' => $product_option['option_id'],
                'name' => $product_option['name'],
                'type' => $product_option['type'],
                'value' => $product_option['value'],
                'required' => $product_option['required']
            );
        }

        return $product_option_data;
    }

    public function getProductDiscounts($product_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int) $product_id . "' AND customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");

        return $query->rows;
    }

    public function getFullPath($product_id) {

        $query = $this->db->query("SELECT COUNT(product_id) AS total, min(category_id) as catid FROM ps_product_to_category  WHERE product_id = '" . (int) $product_id . "' group by product_id");

        if ($query->rows) {
            $total = $query->row['total'];
        } else {
            $total = 0;
        }

        if ($total >= 1) {
            $path = array();
            $path[0] = $query->row['catid'];

            $query = $this->db->query("SELECT parent_id AS pid FROM ps_category WHERE category_id = '" . (int) $path[0] . "'");

            if ($query->rows) {
                $parent_id = $query->row['pid'];
            } else {
                $parent_id = 0;
            }

            $i = 1;
            while ($parent_id > 0) {
                $path[$i] = $parent_id;

                $query = $this->db->query("SELECT parent_id AS pid FROM ps_category WHERE category_id = '" . (int) $parent_id . "'");
                $parent_id = $query->row['pid'];
                $i++;
            }

            $path = array_reverse($path);

            $fullpath = '';

            foreach ($path as $val) {
                $fullpath .= '_' . $val;
            }

            return ltrim($fullpath, '_');
        } else {
            return false;
        }
    }

    public function getCategoryByProductId($product_id) {
        return $this->db->query("SELECT c.category_id FROM ps_product_to_category p2c
        LEFT JOIN ps_category c ON (p2c.category_id = c.category_id)
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id)
        WHERE p2c.product_id = '" . $product_id . "' GROUP BY c.category_id ")->rows;
    }

    public function getProductImages($product_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int) $product_id . "' ORDER BY sort_order ASC");

        return $query->rows;
    }

    public function getProductRelated($product_id) {
        $product_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = '" . (int) $product_id . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "'");

        foreach ($query->rows as $result) {
            $product_data[$result['related_id']] = $this->getProduct($result['related_id']);
        }

        return $product_data;
    }

    public function getProductLayoutId($product_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int) $product_id . "' AND store_id = '" . (int) $this->config->get('config_store_id') . "'");

        if ($query->num_rows) {
            return $query->row['layout_id'];
        } else {
            return 0;
        }
    }

    public function getCategories($product_id) {
        $query = $this->db->query("SELECT * FROM ps_product_to_category WHERE product_id = '" . (int) $product_id . "'");

        return $query->rows;
    }

    public function getProfile($product_id, $recurring_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "recurring r JOIN " . DB_PREFIX . "product_recurring pr ON (pr.recurring_id = r.recurring_id AND pr.product_id = '" . (int) $product_id . "') WHERE pr.recurring_id = '" . (int) $recurring_id . "' AND status = '1' AND pr.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "'");

        return $query->row;
    }

    public function getProfiles($product_id) {
        $query = $this->db->query("SELECT rd.* FROM " . DB_PREFIX . "product_recurring pr JOIN " . DB_PREFIX . "recurring_description rd ON (rd.language_id = " . (int) $this->config->get('config_language_id') . " AND rd.recurring_id = pr.recurring_id) JOIN " . DB_PREFIX . "recurring r ON r.recurring_id = rd.recurring_id WHERE pr.product_id = " . (int) $product_id . " AND status = '1' AND pr.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' ORDER BY sort_order ASC");

        return $query->rows;
    }

    public function getTotalProductSpecials() {
        $query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int) $this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))");

        if (isset($query->row['total'])) {
            return $query->row['total'];
        } else {
            return 0;
        }
    }

    public function getProductsFilterultimate($data) {
        $ultimatemegafilter_setting = $this->config->get('ultimatemegafilter_setting');

        if (VERSION == '1.5.0') {
            $ultimatemegafilter_setting = unserialize($this->config->get('ultimatemegafilter_setting'));
        }

        $customer_group_id = $this->getCustomerGroup();

        $sql = "SELECT product_id FROM(";

        $sql .= "SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added ";

        if (isset($data["sort"]) && $data["sort"] == "rating") {
            $sql .= ", (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating ";
        }

        $sql .= ", coalesce((SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1), " . "(SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1), " . "p.price) as realprice ";

        $sql .= "FROM " . DB_PREFIX . "product p";

        if ($data['option_value'] || $data['instock'])
            $sql .= " INNER JOIN " . DB_PREFIX . "product_option_value pov ON (pov.product_id=p.product_id)";

        if ($data['filter_value'])
            $sql .= " INNER JOIN " . DB_PREFIX . "product_filter pf ON (pf.product_id=p.product_id)";
        $sql .= " INNER JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id=p.product_id)";

        $sql .= " INNER JOIN " . DB_PREFIX . "product_to_store p2s ON (p2s.product_id=p.product_id)" . " INNER JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.product_id=p.product_id)";

        if ($data['attribute_value'] || $data['attr_slider']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_attribute p2a ON (p2a.product_id=p.product_id)";
        }
        if ($data['tags']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_tag p2t ON (p2t.product_id=p.product_id)";
        }
        if (isset($ultimatemegafilter_setting['subcategories'])) {
            $sql .= " INNER JOIN " . DB_PREFIX . "category c ON (p2c.category_id=c.category_id)";
        }
        if ($data['special']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_special ps ON ( ps.product_id = p.product_id )";
        }
        $sql .= " WHERE 1";
        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";

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

                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }
                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }
                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }
            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }
            if (!empty($data['filter_tag'])) {
                $sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            $sql .= ")";
        }
        if ($data['instock']) {
            $sql .= " AND p.quantity > 0 AND (pov.quantity is null OR pov.quantity > 0)";
        }

        if ($data['categories']) {
            if (isset($ultimatemegafilter_setting['subcategories'])) {
                $sql .= " AND (p2c.category_id IN (" . implode(",", $data['categories']) . ") OR c.parent_id IN (" . implode(",", $data['categories']) . "))";
            } else {
                $sql .= " AND p2c.category_id IN (" . implode(",", $data['categories']) . ")";
            }
        }
        if ($data['payment_methods']) {
            $sql .= " AND p.product_payment IN ('" . implode("', '", $data['payment_methods']) . "')";
        }
        if ($data['stock_status']) {
            $sql .= " AND p.stock_status_id IN ('" . implode("', '", $data['stock_status']) . "')";
        }
        if ($data['shipping_methods']) {
            $sql .= " AND p.product_shipping IN ('" . implode("', '", $data['shipping_methods']) . "')";
        }
        $filter_filters = array();

        if ($data['filter_value']) {
            foreach ($data['filter_value'] as $filter_value) {
                $filter_filters[] = "filter_id IN(" . implode(",", $filter_value) . ")";
            }
        }
        if ($filter_filters) {
            foreach ($filter_filters as $i => $filter_filter) {
                $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_filter pf" . $i . " WHERE pf" . $i . ".product_id=pf.product_id AND pf" . $i . "." . $filter_filter . ")";
            }
        }
        $option_filters = array();

        if ($data['option_value']) {
            foreach ($data['option_value'] as $option_value) {
                $option_filters[] = "option_value_id IN(" . implode(",", $option_value) . ")";
            }
        }
        if ($option_filters) {
            if ($ultimatemegafilter_setting['option_mode'] == 'and') {
                foreach ($option_filters as $i => $option_filter) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_option_value pov" . $i . " WHERE pov" . $i . ".product_id=pov.product_id AND pov" . $i . "." . $option_filter . ($data['instock'] ? "AND pov" . $i . ".quantity > 0" : "") . ") ";
                }
            } else {
                $sql .= " AND (" . implode(" OR ", $option_filters) . ")";
            }
        }
        if ($data['manufacturer']) {
            $sql .= " AND p.manufacturer_id IN(" . implode(", ", $data['manufacturer']) . ")";
        }
        if ($data['tags']) {
            $sql .= " AND p2t.tag IN( '" . implode("', '", array_map(array($this->db, 'escape'), $data['tags'])) . "') AND p2t.language_id='" . (int) $this->config->get('config_language_id') . "' ";
        }
        if ($data['special']) {
            $sql .= " AND ps.customer_group_id = '" . (int) $customer_group_id . "'" . " AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW( )) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW( )))";
        }
        $d = $ultimatemegafilter_setting['attr_delimeter'];

        if ($data['attribute_value']) {
            if ($ultimatemegafilter_setting['attribute_mode'] == 'and') {
                $i = 0;

                foreach ($data['attribute_value'] as $attribute_id => $values) {
                    if ($ultimatemegafilter_setting['attribute_value_mode'] == 'or') {
                        $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id=p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND (p2a" . $i . ".text = '" . implode("' OR p2a" . $i . ".text = '", array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a" . $i . ".text like '" . implode($d . "%' OR p2a" . $i . ".text like '", array_map(array($this->db, 'escape'), $values)) . $d . "%'" . " OR p2a" . $i . ".text like '%" . $d . implode("' OR p2a" . $i . ".text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a" . $i . ".text like '%" . $d . implode($d . "%' OR p2a" . $i . ".text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . $d . "%')) ";

                        $i++;
                    } else {
                        foreach ($values as $value) {
                            $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id=p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND (p2a" . $i . ".text = '" . $this->db->escape($value) . "'" . " OR p2a" . $i . ".text like '" . $this->db->escape($value) . $d . "%'" . " OR p2a" . $i . ".text like '%" . $d . $this->db->escape($value) . "'" . " OR p2a" . $i . ".text like '%" . $d . $this->db->escape($value) . $d . "%')) ";

                            $i++;
                        }
                    }
                }
            } else {
                foreach ($data['attribute_value'] as $attribute_id => $values) {
                    $attribute_filters[] = "p2a.attribute_id = " . (int) $attribute_id . " AND (p2a.text = '" . implode("' OR p2a.text = '", array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a.text like '" . implode($d . "%' OR p2a.text like '", array_map(array($this->db, 'escape'), $values)) . $d . "%'" . " OR p2a.text like '%" . $d . implode("' OR p2a.text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a.text like '%" . $d . implode($d . "%' OR p2a.text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . $d . "%')";
                }
                $sql .= " AND (" . implode(" OR ", $attribute_filters) . ")";
            }
        }
        if ($data['attr_slider']) {
            $i = 0;

            foreach ($data['attr_slider'] as $attribute_id => $values) {
                if (!isset($values['min'])) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 <= " . $values['max'] . ")) ";
                } elseif (!isset($values['max'])) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 >= " . $values['min'] . ")) ";
                } else {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 BETWEEN " . $values['min'] . " AND " . $values['max'] . ")) ";
                }
                $i++;
            }
        }
        if ($data['min_price'] >= 0) {
            $min_price = $this->currency->convert((int) $data['min_price'], $this->session->data['currency'], $this->config->get('config_currency'));

            $sql .= " AND p.price >=" . (int) ($min_price);
        }
        $sql .= " AND 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');

        $sort_data = array('pd.name' => 'name', 'p.model' => 'model', 'p.quantity' => 'quantity', 'p.price' => 'realprice', 'p.sort_order' => 'sort_order', 'p.date_added' => 'date_added', 'rating' => 'rating');

        if (isset($data['sort']) && array_key_exists($data['sort'], $sort_data)) {
            $data['sort'] = $sort_data[$data['sort']];

            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.sort_order";
        }
        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.name) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.name) ASC";
        }
        $sql .= ") as innertable WHERE 1 ";

        if ($data['min_price'] >= 0) {
            $min_price = $this->currency->convert((int) $data['min_price'], $this->session->data['currency'], $this->config->get('config_currency'));

            $sql .= "AND realprice >=" . (int) ($min_price);
        }
        if ($data['max_price'] > 0) {
            $max_price = $this->currency->convert($data['max_price'], $this->session->data['currency'], $this->config->get('config_currency'));

            $sql .= " AND realprice <=" . ($max_price);
        }
        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }
            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }
            $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
        }

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

        $product_data = array();

        if ($query->rows) {

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

    public function getPriceLimits($data) {
        $ultimatemegafilter_setting = $this->config->get('ultimatemegafilter_setting');

        if (VERSION == '1.5.0') {
            $ultimatemegafilter_setting = unserialize($this->config->get('ultimatemegafilter_setting'));
        }
        $customer_group_id = $this->getCustomerGroup();
        $sql = "SELECT max(coalesce((SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1), " . "(SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1), " . "p.price) ) AS max_price, min(coalesce((SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1), " . "(SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1), " . "p.price) ) AS min_price FROM " . DB_PREFIX . "product p" . " INNER JOIN " . DB_PREFIX . "product_to_store p2s ON (p2s.product_id=p.product_id)";
        if ($data['manufacturer'])
            $sql .= " INNER JOIN " . DB_PREFIX . "manufacturer m ON(m.manufacturer_id=p.manufacturer_id) ";
        if ($data['option_value'] || $data['instock'])
            $sql .= " INNER JOIN " . DB_PREFIX . "product_option_value pov ON (pov.product_id=p.product_id)";
        if ($data['filter_value'])
            $sql .= " INNER JOIN " . DB_PREFIX . "product_filter pf ON (pf.product_id=p.product_id)";
        if (!empty($data['filter_name']) || !empty($data['filter_tag']))
            $sql .= " INNER JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id=p.product_id)";
        if ($data['attribute_value'] || $data['attr_slider']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_attribute p2a ON (p2a.product_id=p.product_id)";
        }
        //echo $data['category_id'];
        //if($data['category_id'] !='') {
        $sql .= " INNER JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.product_id=p.product_id)";
        if (isset($ultimatemegafilter_setting['subcategories'])) {
            if ($data['category_id']) {
                $sql .= " INNER JOIN " . DB_PREFIX . "category c ON (p2c.category_id=c.category_id)";
            }
        }
        //}
        if ($data['special']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_special ps ON ( ps.product_id = p.product_id )";
        }
        $sql .= " WHERE 1";
        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";
            if (!empty($data['filter_name'])) {
                $implode = array();
                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));
                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }
                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }
                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }
            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }
            if (!empty($data['filter_tag'])) {
                $sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            $sql .= ")";
        }
        if ($data['instock']) {
            $sql .= " AND p.quantity > 0 AND (pov.quantity is null OR pov.quantity > 0)";
        }
        if ($data['categories']) {

            if (isset($ultimatemegafilter_setting['subcategories'])) {
                if ($data['category_id']) {
                    $sql .= " AND (p2c.category_id IN (" . implode(",", $data['categories']) . ") OR c.parent_id IN (" . implode(",", $data['categories']) . "))";
                } else {
                    $sql .= " AND p2c.category_id IN (" . implode(",", $data['categories']) . ")";
                }
            } else {
                $sql .= " AND p2c.category_id IN (" . implode(",", $data['categories']) . ")";
            }
        }
        if ($data['payment_methods']) {
            $sql .= " AND p.product_payment IN ('" . implode("', '", $data['payment_methods']) . "')";
        }
        if ($data['stock_status']) {
            $sql .= " AND p.stock_status_id IN ('" . implode("', '", $data['stock_status']) . "')";
        }
        if ($data['shipping_methods']) {
            $sql .= " AND p.product_shipping IN ('" . implode("', '", $data['shipping_methods']) . "')";
        }
        $filter_filters = array();
        if ($data['filter_value']) {
            foreach ($data['filter_value'] as $filter_value) {
                $filter_filters[] = "pf.filter_id IN(" . implode(",", $filter_value) . ")";
            }
        }
        if ($filter_filters)
            $sql .= " AND (" . implode(" OR ", $filter_filters) . ")";
        $option_filters = array();
        if ($data['option_value']) {
            foreach ($data['option_value'] as $option_value) {
                $option_filters[] = "option_value_id IN(" . implode(",", $option_value) . ")";
            }
        }
        if ($option_filters) {
            if ($ultimatemegafilter_setting['option_mode'] == 'and') {
                foreach ($option_filters as $i => $option_filter) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_option_value pov" . $i . " WHERE pov" . $i . ".product_id=pov.product_id AND pov" . $i . "." . $option_filter . ($data['instock'] ? "AND pov" . $i . ".quantity > 0" : "") . ") ";
                }
            } else {
                $sql .= " AND (" . implode(" OR ", $option_filters) . ")";
            }
        }
        if ($data['manufacturer']) {
            $sql .= " AND p.manufacturer_id IN(" . implode(", ", $data['manufacturer']) . ")";
        }
        $d = $ultimatemegafilter_setting['attr_delimeter'];
        if ($data['attribute_value']) {
            if ($ultimatemegafilter_setting['attribute_mode'] == 'and') {
                $i = 0;
                foreach ($data['attribute_value'] as $attribute_id => $values) {
                    if ($ultimatemegafilter_setting['attribute_value_mode'] == 'or') {
                        $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id=p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND (p2a" . $i . ".text = '" . implode("' OR p2a" . $i . ".text = '", array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a" . $i . ".text like '" . implode($d . "%' OR p2a" . $i . ".text like '", array_map(array($this->db, 'escape'), $values)) . $d . "%'" . " OR p2a" . $i . ".text like '%" . $d . implode("' OR p2a" . $i . ".text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a" . $i . ".text like '%" . $d . implode($d . "%' OR p2a" . $i . ".text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . $d . "%')) ";
                        $i++;
                    } else {
                        foreach ($values as $value) {
                            $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id=p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND (p2a" . $i . ".text = '" . $this->db->escape($value) . "'" . " OR p2a" . $i . ".text like '" . $this->db->escape($value) . $d . "%'" . " OR p2a" . $i . ".text like '%" . $d . $this->db->escape($value) . "'" . " OR p2a" . $i . ".text like '%" . $d . $this->db->escape($value) . $d . "%')) ";
                            $i++;
                        }
                    }
                }
            } else {
                foreach ($data['attribute_value'] as $attribute_id => $values) {
                    $attribute_filters[] = "p2a.attribute_id = " . (int) $attribute_id . " AND (p2a.text = '" . implode("' OR p2a.text = '", array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a.text like '" . implode($d . "%' OR p2a.text like '", array_map(array($this->db, 'escape'), $values)) . $d . "%'" . " OR p2a.text like '%" . $d . implode("' OR p2a.text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a.text like '%" . $d . implode($d . "%' OR p2a.text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . $d . "%')";
                }
                $sql .= " AND (" . implode(" OR ", $attribute_filters) . ")";
            }
        }
        if ($data['attr_slider']) {
            $i = 0;
            foreach ($data['attr_slider'] as $attribute_id => $values) {
                if (!isset($values['min'])) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 <= " . $values['max'] . ")) ";
                } elseif (!isset($values['max'])) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 >= " . $values['min'] . ")) ";
                } else {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 BETWEEN " . $values['min'] . " AND " . $values['max'] . ")) ";
                }
                $i++;
            }
        }
        $sql .= " AND p.status = '1' AND p.date_available <= NOW( ) AND p2s.store_id = " . (int) $this->config->get('config_store_id');
        if ($data['special']) {
            $sql .= " AND ps.customer_group_id = '" . (int) $customer_group_id . "'" . " AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW( )) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW( )))";
        }


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

    public function getTotalProductsFilterultimate($data) {
        $ultimatemegafilter_setting = $this->config->get('ultimatemegafilter_setting');

        if (VERSION == '1.5.0') {
            $ultimatemegafilter_setting = unserialize($this->config->get('ultimatemegafilter_setting'));
        }
        $customer_group_id = $this->getCustomerGroup();
        $sql = "SELECT count(*) as total FROM " . "(SELECT DISTINCT p.product_id, coalesce((SELECT price 		FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1), " . "(SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1), " . " p.price) as realprice " . " FROM " . DB_PREFIX . "product p";
        if ($data['manufacturer'])
            $sql .= " INNER JOIN " . DB_PREFIX . "manufacturer m ON(m.manufacturer_id=p.manufacturer_id) ";
        if ($data['option_value'] || $data['instock'])
            $sql .= " INNER JOIN " . DB_PREFIX . "product_option_value pov ON (pov.product_id=p.product_id)";
        if ($data['filter_value'])
            $sql .= " INNER JOIN " . DB_PREFIX . "product_filter pf ON (pf.product_id=p.product_id)";
        if (!empty($data['filter_name']) || !empty($data['filter_tag']))
            $sql .= " INNER JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id=p.product_id)";
        $sql .= " INNER JOIN " . DB_PREFIX . "product_to_store p2s ON (p2s.product_id=p.product_id)" . " INNER JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.product_id=p.product_id)";

        if (isset($ultimatemegafilter_setting['subcategories'])) {
            $sql .= " INNER JOIN " . DB_PREFIX . "category c ON (p2c.category_id=c.category_id)";
        }
        if ($data['attribute_value'] || $data['attr_slider']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_attribute p2a ON (p2a.product_id=p.product_id)";
        }
        if ($data['tags']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_tag p2t ON (p2t.product_id=p.product_id)";
        }
        if ($data['special']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_special ps ON ( ps.product_id = p.product_id )";
        }
        $sql .= " WHERE 1";
        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";
            if (!empty($data['filter_name'])) {
                $implode = array();
                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));
                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }
                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }
                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }
            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }
            if (!empty($data['filter_tag'])) {
                $sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }
            $sql .= ")";
        }
        if ($data['instock']) {
            $sql .= " AND p.quantity > 0 AND (pov.quantity is null OR pov.quantity > 0)";
        }
        if ($data['categories']) {
            if (isset($ultimatemegafilter_setting['subcategories'])) {
                $sql .= " AND (p2c.category_id IN (" . implode(",", $data['categories']) . ") OR c.parent_id IN (" . implode(",", $data['categories']) . "))";
            } else {
                $sql .= " AND p2c.category_id IN (" . implode(",", $data['categories']) . ")";
            }
        }
        if ($data['payment_methods']) {
            $sql .= " AND p.product_payment IN ('" . implode("', '", $data['payment_methods']) . "')";
        }
        if ($data['stock_status']) {
            $sql .= " AND p.stock_status_id IN ('" . implode("', '", $data['stock_status']) . "')";
        }
        if ($data['shipping_methods']) {
            $sql .= " AND p.product_shipping IN ('" . implode("', '", $data['shipping_methods']) . "')";
        }
        $filter_filters = array();
        if ($data['filter_value']) {
            foreach ($data['filter_value'] as $filter_value) {
                $filter_filters[] = "pf.filter_id IN(" . implode(",", $filter_value) . ")";
            }
        }
        if ($filter_filters)
            $sql .= " AND (" . implode(" OR ", $filter_filters) . ")";
        $option_filters = array();
        if ($data['option_value']) {
            foreach ($data['option_value'] as $option_value) {
                $option_filters[] = "option_value_id IN(" . implode(",", $option_value) . ")";
            }
        }
        if ($option_filters) {
            if ($ultimatemegafilter_setting['option_mode'] == 'and') {
                foreach ($option_filters as $i => $option_filter) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_option_value pov" . $i . " WHERE pov" . $i . ".product_id=pov.product_id AND pov" . $i . "." . $option_filter . ($data['instock'] ? "AND pov" . $i . ".quantity > 0" : "") . ") ";
                }
            } else {
                $sql .= " AND (" . implode(" OR ", $option_filters) . ")";
            }
        }
        if ($data['manufacturer']) {
            $sql .= " AND p.manufacturer_id IN(" . implode(", ", $data['manufacturer']) . ")";
        }
        if ($data['tags']) {
            $sql .= " AND p2t.tag IN( '" . implode("', '", array_map(array($this->db, 'escape'), $data['tags'])) . "') AND p2t.language_id='" . (int) $this->config->get('config_language_id') . "' ";
        }
        if ($data['special']) {
            $sql .= " AND ps.customer_group_id = '" . (int) $customer_group_id . "'" . " AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW( )) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW( )))";
        }
        $d = $ultimatemegafilter_setting['attr_delimeter'];
        if ($data['attribute_value']) {
            if ($ultimatemegafilter_setting['attribute_mode'] == 'and') {
                $i = 0;
                foreach ($data['attribute_value'] as $attribute_id => $values) {
                    if ($ultimatemegafilter_setting['attribute_value_mode'] == 'or') {
                        $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id=p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND (p2a" . $i . ".text = '" . implode("' OR p2a" . $i . ".text = '", array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a" . $i . ".text like '" . implode($d . "%' OR p2a" . $i . ".text like '", array_map(array($this->db, 'escape'), $values)) . $d . "%'" . " OR p2a" . $i . ".text like '%" . $d . implode("' OR p2a" . $i . ".text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a" . $i . ".text like '%" . $d . implode($d . "%' OR p2a" . $i . ".text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . $d . "%')) ";
                        $i++;
                    } else {
                        foreach ($values as $value) {
                            $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id=p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND (p2a" . $i . ".text = '" . $this->db->escape($value) . "'" . " OR p2a" . $i . ".text like '" . $this->db->escape($value) . $d . "%'" . " OR p2a" . $i . ".text like '%" . $d . $this->db->escape($value) . "'" . " OR p2a" . $i . ".text like '%" . $d . $this->db->escape($value) . $d . "%')) ";
                            $i++;
                        }
                    }
                }
            } else {
                foreach ($data['attribute_value'] as $attribute_id => $values) {
                    $attribute_filters[] = "p2a.attribute_id = " . (int) $attribute_id . " AND (p2a.text = '" . implode("' OR p2a.text = '", array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a.text like '" . implode($d . "%' OR p2a.text like '", array_map(array($this->db, 'escape'), $values)) . $d . "%'" . " OR p2a.text like '%" . $d . implode("' OR p2a.text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . "'" . " OR p2a.text like '%" . $d . implode($d . "%' OR p2a.text like '%" . $d, array_map(array($this->db, 'escape'), $values)) . $d . "%')";
                }
                $sql .= " AND (" . implode(" OR ", $attribute_filters) . ")";
            }
        }
        if ($data['attr_slider']) {
            $i = 0;
            foreach ($data['attr_slider'] as $attribute_id => $values) {
                if (!isset($values['min'])) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 <= " . $values['max'] . ")) ";
                } elseif (!isset($values['max'])) {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 >= " . $values['min'] . ")) ";
                } else {
                    $sql .= " AND EXISTS (select 1 FROM " . DB_PREFIX . "product_attribute p2a" . $i . " WHERE p2a" . $i . ".product_id = p2a.product_id AND p2a" . $i . ".attribute_id = " . (int) $attribute_id . " AND " . "(p2a" . $i . ".text * 1 BETWEEN " . $values['min'] . " AND " . $values['max'] . ")) ";
                }
                $i++;
            }
        }
        if ($data['min_price'] >= 0) {
            $min_price = $this->currency->convert((int) $data['min_price'], $this->session->data['currency'], $this->config->get('config_currency'));
            $sql .= " AND p.price >=" . (int) $min_price;
        }
        $sql .= " AND p.status = '1' AND p.date_available <= NOW( ) AND p2s.store_id = " . (int) $this->config->get('config_store_id');
        $sql .= ") as innertable WHERE 1 ";
        if ($data['min_price'] >= 0) {
            $min_price = $this->currency->convert((int) $data['min_price'], $this->session->data['currency'], $this->config->get('config_currency'));
            $sql .= " AND realprice >=" . (int) $min_price;
        }
        if ($data['max_price'] > 0) {
            $max_price = $this->currency->convert($data['max_price'], $this->session->data['currency'], $this->config->get('config_currency'));
            $sql .= " AND realprice <=" . ($max_price);
        }
        $query = $this->db->query($sql);
        $result = $query->row['total'];
        return $result;
    }

    private function getCustomerGroup() {

        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getGroupId();
            return $customer_group_id;
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
            return $customer_group_id;
        }
    }

    public function getBrands($data = array()) {

        $sql = "SELECT * FROM ps_manufacturer m ";
        $sql .= " LEFT JOIN ps_product p ON (m.manufacturer_id = p.manufacturer_id) ";

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

        if (!empty($data['filter_attribute'])) {
            $sql .= " LEFT JOIN ps_product_attribute pa ON(p.product_id = pa.product_id) ";
        }

        if (!empty($data['filter_option'])) {
            $sql .= " LEFT JOIN ps_product_option_value pov ON(p.product_id = pov.product_id) ";
        }

        if (!empty($data['search'])) {
            $sql .= " LEFT JOIN 	ps_product_description pd ON(p.product_id = pd.product_id) ";
        }

        if (!empty($data['category_id']) OR ! empty($data['search'])) {
            $sql .= " WHERE   ";
        }

        if (!empty($data['search'])) {

        }

        if (!empty($data['category_id'])) {
            $sql .= " p2c.category_id = '" . $data['category_id'] . "' ";
        }

        if (!empty($data['filter_attribute'])) {
            $attributes = '';
            foreach ($data['filter_attribute'] as $attribute) {
                $attributes .= $attribute . ',';
            }
            $attributes = rtrim($attributes, ',');
            $sql .= " AND pa.attribute_id IN (" . $attributes . ") ";
        }

        if (!empty($data['filter_option'])) {
            $options = '';
            foreach ($data['filter_option'] as $option) {
                $options .= $option . ',';
            }
            $options = rtrim($options, ',');
            $sql .= " AND pov.option_value_id IN (" . $options . ") ";
        }

        $sql .= " GROUP BY p.manufacturer_id ORDER BY m.name ASC ";

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

    public function getOptions($data = array()) {

        $sql = "";
        if (isset($data['category_id'])) {

            $sql = "SELECT o.option_id AS option_id, od.name AS name FROM " . DB_PREFIX . "option o
                LEFT JOIN " . DB_PREFIX . "option_description od ON(o.option_id = od.option_id)
                LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON(p2c.category_id = '" . $data['category_id'] . "')
                LEFT JOIN " . DB_PREFIX . "product_option po ON(po.product_id = p2c.product_id)

                ";
        }


        if (isset($data['brands']) and $data['brands']) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON(p.product_id = po.product_id)  ";
        }

        $sql .= "WHERE od.language_id='" . (int) $this->config->get('config_language_id') . "' AND o.option_id = po.option_id ";

        if (isset($data['brands']) and $data['brands']) {
            $sql .= " AND p.manufacturer_id IN (" . ltrim($data['brands'], ",") . ") ";
        }

        $sql .= " GROUP BY o.option_id ";

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

        return $query->rows;
    }

    public function getOptionValues($option_id) {
        $query = $this->db->query("SELECT ovd.name AS name, ov.option_value_id AS ov_option_id  FROM " . DB_PREFIX . "option_value ov
            LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON(ov.option_value_id = ovd.option_value_id)
            LEFT JOIN " . DB_PREFIX . "product_option_value pov ON(pov.option_value_id = ov.option_value_id)

            WHERE ovd.language_id='" . (int) $this->config->get('config_language_id') . "' AND pov.option_id='" . $option_id . "' GROUP BY ov.option_value_id
         ");

        return $query->rows;
    }

    public function getAttributeGroups($data = array()) {

        $sql = " SELECT agd.name AS name, ag.attribute_group_id as attribute_group_id  FROM " . DB_PREFIX . "attribute_group ag LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON(ag.attribute_group_id = agd.attribute_group_id)  ";

        $sql .= " LEFT JOIN " . DB_PREFIX . "attribute a ON(a.attribute_group_id = ag.attribute_group_id) ";

        $sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute pa ON(pa.attribute_id = a.attribute_id) ";

        if (isset($data['category_id']) and $data['category_id']) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON(p2c.category_id = " . $data['category_id'] . ") ";
        }

        $sql .= " WHERE agd.language_id='" . (int) $this->config->get('config_language_id') . "' ";

        if (isset($data['category_id']) and $data['category_id']) {
            $sql .= " AND p2c.category_id = '" . $data['category_id'] . "' AND pa.product_id = p2c.product_id ";
        }

        $sql .= " GROUP BY ag.attribute_group_id ";

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

        return $query->rows;
    }

    public function getAttributeValues($attribute_group_id) {

        $sql = "SELECT ad.name AS name, a.attribute_id AS attribute_id  FROM " . DB_PREFIX . "attribute a LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) ";


        $sql .= "WHERE a.attribute_group_id = '" . $attribute_group_id . "' AND ad.language_id='" . (int) $this->config->get('config_language_id') . "' ";

        $sql .= " GROUP BY a.attribute_id ";

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

    public function getFilterGroups($data = array()) {

        $sql = " SELECT fgd.name AS name, fg.filter_group_id AS filter_group_id FROM " . DB_PREFIX . "filter_group fg LEFT JOIN " . DB_PREFIX . "filter_group_description fgd ON(fg.filter_group_id = fgd.filter_group_id) ";

        $sql .= " LEFT JOIN " . DB_PREFIX . "filter f ON(f.filter_group_id = fg.filter_group_id) ";

        $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON(f.filter_id = pf.filter_id) ";

        if (isset($data['category_id']) and $data['category_id']) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON(p2c.category_id = " . $data['category_id'] . ") ";
        }

        $sql .= " WHERE fgd.language_id='" . (int) $this->config->get('config_language_id') . "' ";

        if (isset($data['category_id']) and $data['category_id']) {
            $sql .= " AND p2c.category_id = '" . $data['category_id'] . "' AND pf.product_id = p2c.product_id ";
        }

        $sql .= " GROUP BY fg.filter_group_id ";

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

    public function getFilterValues($filter_group_id) {

        $sql = "SELECT fd.name AS name, f.filter_id AS filter_id FROM " . DB_PREFIX . "filter f LEFT JOIN " . DB_PREFIX . "filter_description fd ON(f.filter_id = fd.filter_id) ";

        $sql .= " WHERE f.filter_group_id = '" . $filter_group_id . "' AND fd.language_id='" . (int) $this->config->get('config_language_id') . "' ";
        $sql .= " GROUP BY f.filter_id ";

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

    public function getProductForJsonCache($product_id) {
        return $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer,
				(SELECT price FROM ps_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,
				(SELECT price FROM ps_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special,
				(SELECT points FROM ps_product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '" . (int) $this->config->get('config_customer_group_id') . "') AS reward,
				(SELECT ss.name FROM ps_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int) $this->config->get('config_language_id') . "') AS stock_status,
				(SELECT wcd.unit FROM ps_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int) $this->config->get('config_language_id') . "') AS weight_class,
				(SELECT lcd.unit FROM ps_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int) $this->config->get('config_language_id') . "') AS length_class,
				(SELECT AVG(rating) AS total FROM ps_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating,
				(SELECT COUNT(*) AS total FROM ps_review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews,
				p.sort_order FROM ps_product p
				LEFT JOIN ps_product_description pd ON (p.product_id = pd.product_id)
				LEFT JOIN ps_product_to_store p2s ON (p.product_id = p2s.product_id)
				LEFT JOIN ps_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
				WHERE p.product_id = '" . (int) $product_id . "' AND 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') . "'")->row;
    }

    public function getCollectionColor($product_id, $model, $category_id) {
        $this->load->model('tool/image');

        $products = $this->db->query("SELECT p.product_id FROM ps_product p
        LEFT JOIN ps_product_to_category p2c ON (p.product_id = p2c.product_id)
        WHERE p.model = '" . $this->db->escape($model) . "' AND p2c.category_id = '" . (int) $category_id . "' ")->rows;

        $products_data = array();



        foreach ($products as $product) {

            if ($product['product_id'] == $product_id) {
                $showcase = true;
            } else {
                $showcase = false;
            }

            $attribute_color = $this->db->query("SELECT ad.name, a.image, a.attribute_id FROM ps_attribute a
            LEFT JOIN ps_attribute_description ad ON (a.attribute_id = ad.attribute_id)
            LEFT JOIN ps_product_attribute pa ON (a.attribute_id = pa.attribute_id)
            WHERE ad.language_id = '1' AND a.attribute_group_id = '13' AND pa.product_id = '" . $product['product_id'] . "' GROUP BY a.attribute_id ")->row;

            $p_info = $this->getProduct($product['product_id']);

            if ($this->customer->isLogged() || !$this->config->get('config_customer_price')) {
                $price = $this->currency->format($this->tax->calculate($p_info['price'], $p_info['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']);
            } else {
                $price = false;
            }

            if ((float) $p_info['special']) {
                $special = $this->currency->format($this->tax->calculate($p_info['special'], $p_info['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']);
            } else {
                $special = false;
            }

            $images = $this->getProductImages($product['product_id']);
            $data['images'] = array();
            foreach ($images as $img) {
                $data['images'][] = array(
                    'popup' => $this->model_tool_image->resize($img['image'], $this->config->get($this->config->get('config_theme') . '_image_popup_width'), $this->config->get($this->config->get('config_theme') . '_image_popup_height'), 'product_list'),
                    'popup1' => $this->model_tool_image->resize($img['image'], $this->config->get($this->config->get('config_theme') . '_image_thumb_width'), $this->config->get($this->config->get('config_theme') . '_image_thumb_height'), 'product_list'),
                    'thumb' => $this->model_tool_image->resize($img['image'], $this->config->get($this->config->get('config_theme') . '_image_additional_width'), $this->config->get($this->config->get('config_theme') . '_image_additional_height'), 'product_list')
                );
            }

            if ($p_info['image']) {
                $image = $this->model_tool_image->resize($p_info['image'], $this->config->get($this->config->get('config_theme') . '_image_product_width'), $this->config->get($this->config->get('config_theme') . '_image_product_height'), 'product_list');
            } else {
                $image = $this->model_tool_image->resize('placeholder.png', $this->config->get($this->config->get('config_theme') . '_image_product_width'), $this->config->get($this->config->get('config_theme') . '_image_product_height'));
            }

            $products_data[] = array(
                'name' => $p_info['name'],
                'show' => $showcase,
                'price' => $price,
                'special' => $special,
                'image' => $image,
                'images' => $data['images'],
                'href' => $this->url->link('product/product', 'product_id=' . $product['product_id']),
                'attribute_id' => $attribute_color['attribute_id'],
                'attribute_name' => $attribute_color['name'],
                'attribute_image' => $this->model_tool_image->resize($attribute_color['image'], 20, 20),
            );
        }

        return $products_data;

        //var_dump($ask_pr); die();

        /* $ask_attr = $this->db->query("SELECT ad.name, a.image, a.attribute_id FROM ps_attribute a
          LEFT JOIN ps_attribute_description ad ON (a.attribute_id = ad.attribute_id)
          LEFT JOIN ps_product_attribute pa ON (a.attribute_id = pa.attribute_id)
          WHERE ad.language_id = '1' AND a.attribute_group_id = '13' AND pa.product_id IN ( SELECT p.product_id FROM ps_product p
          LEFT JOIN ps_product_to_category p2c ON (p.product_id = p2c.product_id)
          WHERE p.model = '". $this->db->escape($model) ."' AND p2c.category_id = '". (int)$category_id ."'  ) GROUP BY a.attribute_id ")->rows; */
    }

}
