<?php

class ModelCatalogProduct extends Model
{

    private $filtersField = array();
    private $filtersFieldGrid = array();
    private $config_setting_search = array();

    function __construct($registry)
    {
        parent::__construct($registry);
        $this->load->type('tipocomum');
        $this->config_setting_search = ($this->config->get('config_setting_search')) ? $this->config->get('config_setting_search') : array();
        $this->filtersField = array(
            Type_SearchOption::Tp_Model => 'p.model',
            Type_SearchOption::Tp_IdEcommerce => 'p.product_id',
            Type_SearchOption::Tp_FuturaServer => 'p.id_futura',
            Type_SearchOption::Tp_CodigoBarras => 'p.codigo_barra'
        );

        $this->filtersFieldGrid = array(
            Type_SearchOption::Tp_Model => 'p2.model',
            Type_SearchOption::Tp_IdEcommerce => 'p2.product_id',
            Type_SearchOption::Tp_FuturaServer => 'p2.id_futura',
            Type_SearchOption::Tp_CodigoBarras => 'p2.codigo_barra'
        );

    }

    function getFilters()
    {
        $array = array();
        $arrayGrid = array();

        if ($this->config_setting_search) {
            foreach ($this->config_setting_search as $key => $value) {
                if (in_array($value, array_keys($this->filtersField))) {
                    $array[] = $this->filtersField[$value];
                }
                if (in_array($value, array_keys($this->filtersFieldGrid))) {
                    $arrayGrid[] = $this->filtersFieldGrid[$value];
                }
                $this->config_setting_search[$value] = $value;
            }
        }
        $this->filtersField = $array;
        $this->filtersFieldGrid = $arrayGrid;
    }


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

    public function getProductPrice($product_id)
    {
        $query = $this->db->query("SELECT p.price FROM " . DB_PREFIX . "product p WHERE p.product_id = '" . (int)$product_id . "'");
        if ($query->num_rows) {
            return $query->row['price'];
        } else {
            return false;
        }
    }

    public function getProduct($product_id, $chk = false, $allStatus = false, $tabelapreco = true)
    {

        $this->load->type('tipocomum');
        $this->load->helper('ftr_geral');

        $data_language = $this->config->get('config_data_language');
        if (!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) {
            unset($data_language);
        }


        $data = array(
            'tabela_preco_dinamico' => (ftr_geral::isIsset($this->config->get('preco_dinamico_tabelapreco')) && $tabelapreco) ? $this->config->get('preco_dinamico_tabelapreco') : false,
            'preco_dinamico_status' => (ftr_geral::isISset($this->config->get('preco_dinamico_status')) && $tabelapreco)      ? $this->config->get('preco_dinamico_status')      : false
        );

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

        $sqlIn = ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());
        $sql = "SELECT DISTINCT p.*";
        $sql .= isset($data_language) ? ",coalesce(pt.descricao_ecommerce, pd.name) as name" : ",pd.name as name";
        $sql .= "   ,if(p.tipo in $sqlIn, true, false ) isProductGrid"
            . "	  ,p.image"
            . "   ,p.codigo_barra AS codigo_barra"
            . "	  ,m.name AS manufacturer"
            . "	  ,p.id_futura AS idfutura"
            . "	  ,pkit.product_kit_id AS product_kit_id";
        $sql .= isset($data_language) ? ",coalesce(pt.descricao_completa, pd.description) as description" : " ,pd.description AS description";
        $sql .= "   ,pd.meta_description AS meta_description"
            . "   ,pd.meta_keyword  AS meta_keyword"
            . "   ,pd.tag  AS tag";
        $sql .= isset($data_language) ? " ,coalesce(pt.descricao_completa_sf, pd.description_complete_s_format) AS description_complete_s_format" : " ,pd.description_complete_s_format";
        $sql .= "   ,(SELECT m2.name FROM " . DB_PREFIX . "manufacturer m2 INNER JOIN " . DB_PREFIX . "manufacturer_to_store mts ON (m2.manufacturer_id = mts.manufacturer_id) WHERE m2.manufacturer_id = p.manufacturer_id) as manufacturer_product "
            . "		,(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) AS discount"
            . "		,(select coalesce(perc_discount, 0) perc_discount from " . DB_PREFIX . "order_discount  where (product_id = '" . (int)$product_id . "') and (tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") and (status = " . Type_PromocaoStatus::Tp_Ativo . ") and ((date_start = '0000-00-00') or (date_start <= CURDATE())) and ((date_end = '0000-00-00') or (date_end >= CURDATE())) ORDER BY perc_discount DESC LIMIT 1) as order_perc_discount"
            . " 	,(SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "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 " . DB_PREFIX . "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 " . DB_PREFIX . "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 " . DB_PREFIX . "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 " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews,"
            . " (pc.description) AS product_color_description,"
            . " (ps.description) AS product_size_description,"
            . " (pspec.description) AS product_specification_description";

        $sql .= ",(SELECT fp.pontos FROM oc_fidelity_present fp
                INNER JOIN oc_fidelity fid ON fid.fidelity_id = fp.fidelity_id
                WHERE fp.product_id = p.product_id";

        if (!$this->config->get('fidelity_empresa_id') || $this->config->get('fidelity_empresa_id') == Type_FidelityConfigs::Tp_Multiempresa) {
            $sql .= " and fid.tipo_pontuacao = " . Type_FidelityTipoPontuacao::Tp_MultiEmpresa . " order by fp.pontos LIMIT 1) as pontos";
        } else {
            $sql .= " and fid.empresaId = " . $this->config->get('fidelity_empresa_id') . " order by fp.pontos LIMIT 1) as pontos";
        }

        $sql .= "		,p.sort_order";

        if ($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']) {
            $sql .= " ,GROUP_CONCAT(cg.customer_group_id) AS list_tabela_preco_id"
                 .  " ,GROUP_CONCAT(pdc.price) AS list_tabela_preco_price";
        }

        $sql .= "	FROM oc_product p"
             .  "	LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)";
        $sql .= isset($data_language) ? " LEFT JOIN oc_product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
        $sql .= " LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id)"
            . " LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)";

        $sql .= " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S' ";

        $sql .= " LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id)";
        $sql .= " LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id)";
        $sql .= " LEFT JOIN oc_product_specification pspec ON (pspec.productspecification_id = p.product_specification_id)";

        if ($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']) {
            $sql .= " INNER JOIN " . DB_PREFIX . "product_discount pdc on (pdc.product_id = p.product_id)"
                 .  " INNER JOIN " . DB_PREFIX . "customer_group cg on (cg.customer_group_id = pdc.customer_group_id)";
        }

        $sql .= " WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'"
            . (($allStatus) ? "" : " AND p.status = '1'") . " AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'" . (($chk) ? " AND p.qty > 0" : "")
            . " AND (p.price > 0 OR (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) > 0)";


        //ESTE BLOCO TEM QUE FICAR NO FINAL DE TODOS OS WHERE POR CONTA DO HAVING QUE EXISTE
        if ($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']) {
            $tabela_preco_dinamico = implode(',', array_filter($data['tabela_preco_dinamico']));
            $sql .= " AND pdc.customer_group_id IN(" . $tabela_preco_dinamico . ") ";

            $sql .= " HAVING GROUP_CONCAT(cg.customer_group_id) is NOT NULL AND GROUP_CONCAT(pdc.price) is NOT null ";
        }

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

        if ($query->num_rows) {

            $type = ftr_geral::isIsset($this->config->get('config_product_code')) ? $this->config->get('config_product_code') : Type_ListCodProduct::Tp_CodReferencia;
            switch ($type) {
                case Type_ListCodProduct::Tp_CodReferencia:
                    $model = $query->row['model'];
                    break;
                case Type_ListCodProduct::Tp_CodProdEcommerce:
                    $model = $query->row['product_id'];
                    break;
                case Type_ListCodProduct::Tp_CodProdFuturaServer:
                    $model = $query->row['id_futura'];
                    break;
                case Type_ListCodProduct::Tp_CodBarras:
                    $model = $query->row['codigo_barra'];
                    break;
                default:
                    $model = $query->row['model'];
            }

            $price = ((float)$query->row['discount'] ? $query->row['discount'] : $query->row['price']);

            $dataPrice = array(
                'list_tabela_preco_id' => isset($query->row['list_tabela_preco_id']) ? $query->row['list_tabela_preco_id'] : false,
                'list_tabela_preco_price' => isset($query->row['list_tabela_preco_price']) ? $query->row['list_tabela_preco_price'] : false
            );

            return array(
                'product_id' => $query->row['product_id'],
                'key' => $query->row['product_id'],
                'name' => $query->row['name'],
                'description' => $query->row['description'],
                'meta_description' => $query->row['meta_description'],
                'meta_keyword' => $query->row['meta_keyword'],
                'tag' => $query->row['tag'],
                'model' => $model,
                'codigo_barra' => $query->row['codigo_barra'],
                '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'],
                'qty' => $query->row['qty'],
                'stock_status' => $query->row['stock_status'],
                'image' => $query->row['image'],
                'manufacturer_id' => $query->row['manufacturer_id'],
                'manufacturer' => $query->row['manufacturer'],
                'manufacturer_name' => $query->row['manufacturer'],//compatibilidade com modelos do mercado livre, por isso a duplicidade
                'manufacturer_product' => $query->row['manufacturer_product'],//compatibilidade com modelos do mercado livre, por isso a duplicidade
                'price' => $price,
                'tabela_preco' => (count(array_filter($dataPrice)) > 0) ? $this->getTabelaPrecoPrice($data['tabela_preco_dinamico'], $dataPrice) : false,
                'special' => ($query->row['order_perc_discount']) ? ($price - (($query->row['order_perc_discount'] * 0.01) * $price)) : 0,
                'reward' => $query->row['reward'],
                'points' => $query->row['points'],
                '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'],
                'quantity_unit' => $query->row['quantity_unit'],
                'fator_multiplicador' => ($this->config->get('config_fator_multiplicador') && ((int)$query->row['quantity_unit'] != 0)) ? (int)$query->row['quantity_unit'] : 1,
                'comp_progressiva' => $query->row['comp_progressiva'],
                'larg_progressiva' => $query->row['larg_progressiva'],
                'alt_progressiva' => $query->row['alt_progressiva'],
                'data_atualizacao_replicacao' => $query->row['data_atualizacao_replicacao'],
                'description_complete_s_format' => $query->row['description_complete_s_format'],
                'isProductGrid' => $query->row['isProductGrid'],
                'pontos' => $query->row['pontos'],
                'id_futura' => $query->row['idfutura'],
                'product_kit_id' => $query->row['product_kit_id'],
                'product_color_id' => $query->row['product_color_id'],
                'product_size_id' => $query->row['product_size_id'],
                'product_specification_id' => $query->row['product_specification_id'],
                'product_color_description' => $query->row['product_color_description'],
                'product_size_description' => $query->row['product_size_description'],
                'product_specification_description' => $query->row['product_specification_description'],
            );
        } else {
            return false;
        }
    }

    private function isEnabledAviseme()
    {
        return (
            ftr_geral::isIsset($this->config->get('avise_me_status')) && $this->config->get('avise_me_status')
            && ftr_geral::isIsset($this->config->get('config_stock_checkout')) && (!$this->config->get('config_stock_checkout'))
        );
    }


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

        $this->getFilters();//função utilizada para setar os filtros que seram usados na pesquisa by Dionis

        $data = ftr_geral::ConvertCaracter($data);
        $data['tabela_preco_dinamico'] = ftr_geral::isIsset($this->config->get('preco_dinamico_tabelapreco')) ? $this->config->get('preco_dinamico_tabelapreco') : false;
        $data['preco_dinamico_status'] = ftr_geral::isISset($this->config->get('preco_dinamico_status')) ? $this->config->get('preco_dinamico_status') : false;

        $this->load->type('tipocomum');
        $this->load->type('tipofuturaserver');
        $this->load->helper('ftr_geral');
        $filtro = '';
        $filtro2 = '';//Filtro para grage, não pode ser utilizado o $filtro por causa dos joins
        $filtrosgrade = '';

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

        //$data['filter_content'] = (ftr_geral::isIsset($this->config->get('config_setting_search_name'))) ? $this->config->get('config_setting_search_name') : Type_FilterContent::Tp_Default;

        $data_language = $this->config->get('config_data_language');
        if (!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) {
            unset($data_language);
        }

        $sql = "SELECT";

        if (!empty($data['filter_name'])) {
            if (isset($data_language)) {
                $sql .= " (match(pd.name) against (QUOTE('" . $this->db->escape($data['filter_name']) . "') in natural language mode)) as score,
                  (match(pt.descricao_ecommerce) against (QUOTE('" . $this->db->escape($data['filter_name']) . "') in natural language mode)) as score2,";
                $sql .= "if(LCASE(pd.name) = '" . utf8_strtolower($this->db->escape($data['filter_name'])) . "'
                 or LCASE(pt.descricao_ecommerce) = '" . utf8_strtolower($this->db->escape($data['filter_name'])) . "', 1, 0) as match_value,";
                $sqlOrderScore = "(CASE WHEN score > score2 then score else score2 end )";
            } else {
                $sql .= " (match(pd.name) against (QUOTE('" . $this->db->escape($data['filter_name']) . "') in natural language mode)) as score,";
                $sql .= "if(LCASE(pd.name) = '" . utf8_strtolower($this->db->escape($data['filter_name'])) . "', 1, 0) as match_value,";
                $sqlOrderScore = "score";
            }
        } elseif (!empty($data['filter_tag'])) {
            $sql .= " (match(pd.tag) against (QUOTE('" . $this->db->escape($data['filter_tag']) . "') in natural language mode)) as score,";
            $sqlOrderScore = "score";
        }

        if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
            $sql .= " (fp.pontos) fp_pontos,";
        }

        $sql .= " p.product_id,"
            . " (p.viewed)p_viewed,"
            . " (p.sort_order)p_sort_order,";
        $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as pd_name," : "pd.name as pd_name,";
        $sql .= " (p.model) p_model,"
            . " (p.qty) p_qty,"
            . " (p.date_added) p_date_added,"
            . " coalesce(p.data_replicacao_ecommerce, p.date_added, NULL) p_data_replicacao_ecommerce,"
            . "  SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") as valor_product,"
            . " (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";

        $filtro = "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') . "'"
            . " AND (p.price > 0 OR (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) > 0)";
        $filtrosgrade = "and pd2.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2.status = '1' AND p2.date_available <= NOW() and p2s2.store_id = '" . (int)$this->config->get('config_store_id') . "'";

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

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


            if (!empty($data['filter_sub_category'])) {
                $sql .= " inner join " . DB_PREFIX . "category p2c on (p2c.category_id = p.category_id) and p2c.category_id= ".$data['filter_category_id'];
                $filtro .= " AND p.category_id = '" . (int)$data['filter_category_id'] . "'";
                $filtrosgrade .= " AND p2.category_id = '" . (int)$data['filter_category_id'] . "'";
            } else {
                $sql .=
                "   INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
                    LEFT  JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
                    LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
                ";

                $filtro .=  " AND (nsub.category_id = '" . (int)$data['filter_category_id']
                            . "' OR ncat.category_id = '" . (int)$data['filter_category_id']
                            . "' OR ndep.category_id = '" . (int)$data['filter_category_id']."')";

                $filtrosgrade .= " AND (nsub2.category_id = '" . (int)$data['filter_category_id']
                            . "' OR ncat2.category_id = '" . (int)$data['filter_category_id']
                            . "' OR ndep2.category_id = '" . (int)$data['filter_category_id']."')";

            }

        }

        $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)";
        $sql .= isset($data_language) ? " LEFT JOIN " . DB_PREFIX . "product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
		$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";

        if (!empty($data['filter_order_promotion'])) {
            $sql .= " INNER JOIN " . DB_PREFIX . "order_discount od ON (od.product_id = p.product_id) ";

            $filtro .= " AND od.order_promotion_id = '" . (int)$data['filter_order_promotion'] . "'"
                . " AND (od.date_start = '0000-00-00' OR od.date_start <= CURDATE())"
                . " AND (od.date_end = '0000-00-00' OR od.date_end >= CURDATE())"
                . " and ((od.tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") OR (od.tipo_promocao = " . Type_Promocao::Tp_Brinde . "))"
                . " and (od.status        = " . Type_PromocaoStatus::Tp_Ativo . ")";

            $filtrosgrade .= " AND (od2.order_promotion_id = '" . (int)$data['filter_order_promotion'] . "')"
                . " AND (od2.date_start = '0000-00-00' OR od2.date_start <= CURDATE())"
                . " AND (od2.date_end = '0000-00-00' OR od2.date_end >= CURDATE()) "
                . " and ((od2.tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") OR (od2.tipo_promocao = " . Type_Promocao::Tp_Brinde . "))"
                . " and (od2.status        = " . Type_PromocaoStatus::Tp_Ativo . ")";
        }

        if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
            $sql .= " INNER JOIN oc_fidelity_present fp ON (p.product_id = fp.product_id AND p.qty > 0)";
        }

        if($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']){
            $sql .= " INNER JOIN " . DB_PREFIX . "product_discount pdc on (pdc.product_id = p.product_id)"
                .  " INNER JOIN " . DB_PREFIX . "customer_group cg on (cg.customer_group_id = pdc.customer_group_id)";

            $tabela_preco_dinamico = implode(',', array_filter($data['tabela_preco_dinamico']));
            $filtro .= " AND pdc.customer_group_id IN(" . $tabela_preco_dinamico . ") ";
        }

        if (($this->config->get('futurafiltrovalor_status'))) {

            if ((!empty($data['price_initial'])) && (!empty($data['price_laste']))) {
                $filtro .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") BETWEEN " . $data['price_initial'] . " and " . $data['price_laste'];
                $filtrosgrade .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") BETWEEN " . $data['price_initial'] . " and " . $data['price_laste'];
            } elseif ((!empty($data['price_initial']))) {
                $filtro .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") >= " . $data['price_initial'];
                $filtrosgrade .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") >= " . $data['price_initial'];
            } elseif ((!empty($data['price_laste']))) {
                $filtro .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") <= " . $data['price_laste'];
                $filtrosgrade .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") <= " . $data['price_laste'];
            }

            if (($this->isEnabledAviseme()) && ((!empty($data['price_initial'])) || (!empty($data['price_laste'])))) {
                $filtro .= " AND p.qty > 0 ";
                $filtrosgrade .= " AND p2.qty > 0 ";
            }
        }

        if ($this->config->get('futuragrade_status')) {
            if (!empty($data['filcolor'])) {
                $filtro .= " AND p.product_color_id in(" . $data['filcolor'] . ")";
                $filtrosgrade .= " AND p2.product_color_id in(" . $data['filcolor'] . ")";
            }

            if (!empty($data['filsize'])) {
                $filtro .= " AND p.product_size_id in(" . $data['filsize'] . ")";
                $filtrosgrade .= " AND p2.product_size_id in(" . $data['filsize'] . ")";
            }

            if (!empty($data['filspecification'])) {
                $filtro .= " AND p.product_specification_id in(" . $data['filspecification'] . ")";
                $filtrosgrade .= " AND p2.product_specification_id in(" . $data['filspecification'] . ")";
            }

            if (!empty($data['not_grid_id']) && $data['not_grid_id']) {
                $filtrosgrade .= " AND pg2.product_grid_id not in(" . $data['not_grid_id'] . ")";
            }
        }

        if ($this->config->get('fidelity_status')) {
            if (!empty($data['filrange'])) {

                $arrayFiltro = array();
                $arrayFiltroGrade = array();
                foreach (explode(',', $data['filrange']) as $key => $value) {
                    $range = explode('.', $value);
                    $arrayFiltro[] = '(fp.pontos BETWEEN ' . $range[0] . ' and ' . $range[1] . ')';
                    $arrayFiltroGrade[] .= '(fp2.pontos BETWEEN ' . $range[0] . ' and ' . $range[1] . ')';
                }

                $filtro .= ' and ' . implode(' or ', $arrayFiltro);
                $filtrosgrade .= ' and ' . implode(' or ', $arrayFiltroGrade);

            }
        }


        if (!empty($data['not_product_id']) && $data['not_product_id']) {
            $filtro .= " AND p.product_id not in(" . $data['not_product_id'] . ")";
            $filtrosgrade .= " AND p2.product_id not in(" . $data['not_product_id'] . ")";
        }

        if ($this->config->get('futurafiltromarca_status')) {
            if (!empty($data['filbrand'])) {
                $filtro .= " AND p.manufacturer_id in(" . $data['filbrand'] . ")";
                $filtrosgrade .= " AND p2.manufacturer_id in(" . $data['filbrand'] . ")";
            }
        }

        if (!empty ($data['filter_quantity'])) {
            $filtro .= " AND p.qty > 0 ";
            $filtrosgrade .= " AND p2.qty > 0 ";
        }

        if (!empty($data['filter_list_in_feeds'])) {
            $filtro .= " AND p.list_in_feeds = '".Type_DsimDnaoServer::Tp_Sim."'";
            $filtrosgrade .= " AND p2.list_in_feeds = '".Type_DsimDnaoServer::Tp_Sim."'";
        }

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

        if (!empty($data['product_ids'])) {
            $filtro .= " AND p.product_id IN (" . $data['product_ids'] . ") ";
            $filtrosgrade .= " AND p2.product_id IN (" . $data['product_ids'] . ") ";
        }

        if (!empty($data['viewed'])) {
            $filtro .= " AND p.viewed > 0 AND p.status = " . Type_Status::Tp_Habilitado;
            $filtrosgrade .= " AND p2.viewed > 0 AND p.status = " . Type_Status::Tp_Habilitado;
        }

        if (!empty($data['productsId'])) {
            $filtro .= " AND p.product_id IN (" . $data['productsId'] . ") ";
            $filtrosgrade .= " AND p2.product_id IN (" . $data['productsId'] . ") ";
        }

        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {

            $filtro .= " AND (";
            $filtrosgrade .= " AND (";

            if (!empty($data['filter_name']) && ($data['filter_content'] == Type_FilterContent::Tp_Default)) {
                $implode = array();
                $implodeGrade = array();
                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

                $word_search = '';
                foreach ($words as $word) {
                    if (strlen($word) < 3) {
                        $word_search .= ' ' . $word;
                    } else {
                        $word_search = $word;
                    }
                    $implode[] = "(pd.name LIKE '%" . $this->db->escape(trim($word_search)) . "%'" .
                    (isset($data_language) ? " OR pt.descricao_ecommerce LIKE '%" . $this->db->escape(trim($word_search)) . "%' )" : ")");
                    $implodeGrade[] = "(pd2.name LIKE '%" . $this->db->escape(trim($word_search)) . "%')";
                }

                if ($implode) {
                    $filtro .= " " . implode(" OR ", $implode) . "";
                }
                if ($implodeGrade) {
                    $filtrosgrade .= " " . implode(" OR ", $implodeGrade) . "";
                }
                if (isset($this->config_setting_search[Type_SearchOption::Tp_DescricaoCompleta])) {
                    $filtro .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                    $filtrosgrade .= " OR pd2.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

            if (!empty($data['filter_name']) && ($data['filter_content'] != Type_FilterContent::Tp_Default)) {
                $filtro .= "pd.name " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content']) .
                    (isset($data_language) ? " OR pt.descricao_ecommerce " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content']) : "");
                $filtrosgrade .= "pd2.name " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content']);
            }

            if (!empty($data['filter_name']) && isset($this->config_setting_search[Type_SearchOption::Tp_Tag])) {
                $implode = array();
                $implodeGrade = array();
                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

                $word_search = '';
                foreach ($words as $word) {
                    if (strlen($word) < 3) {
                        $word_search .= ' ' . $word;
                    } else {
                        $word_search = $word;
                    }
                    $implode[] = "pd.tag LIKE '%" . $this->db->escape(trim($word_search)) . "%' ";

                    $implodeGrade[] = "pd2.tag LIKE '%" . $this->db->escape(trim($word_search)) . "%' ";
                }

                if ($implode) {
                    $filtro .= " OR " . implode(" OR ", $implode) . "";
                }
                if ($implodeGrade) {
                    $filtrosgrade .= " OR " . implode(" OR ", $implodeGrade) . "";
                }
            }

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

            if (!empty($data['filter_tag'])) {
                $filtro .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
                $filtrosgrade .= "pd2.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
            }

            if (!empty($data['filter_name'])) {
                foreach ($this->filtersField as $value) {
                    $filtro .= " OR LCASE(" . $value . ") = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                }
                foreach ($this->filtersFieldGrid as $value) {
                    $filtrosgrade .= " OR LCASE(" . $value . ") = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                }

            }

            //Manter esse bloco por ultimo, pois faz uma copia do filtro adicionando os filtros que só estão dentro da grade//
            //O filtro2 se faz necessário pois no filtro não tem join da produto grade e grade detail
            $filtro2 = $filtro;
            if (!empty($data['filter_name'])) {
                $filtro2 .= "OR LCASE(pg.referencia)  LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
                $filtrosgrade .= "OR LCASE(pg2.referencia)  LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
            }
            //------------------------------------//

            $filtro2 .= ")";
            $filtro .= ")";
            $filtrosgrade .= ")";
        } else {
            $filtro2 = $filtro;
        }

        $sqlorder = "";
        $limit = "";

        $sort_data = array(
            'pd.name',
            'p.model',
            'p.qty',
            'p.price',
            'rating',
            'p.sort_order',
            'p.date_added',
            'p.data_replicacao_ecommerce',
            'p.viewed',
            'fp.pontos',
            'score'
        );

        if ($this->isEnabledAviseme()) {
            $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "(CASE WHEN p_qty > 0 THEN 0  ELSE 1 END) ASC");
        }

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "LCASE(" . str_replace('.', '_', $data['sort']) . ")");
            } elseif ($data['sort'] == 'p.price') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "valor_product");
            } elseif ($data['sort'] == 'p.viewed') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "(CASE WHEN p_viewed IS NOT NULL THEN p_viewed END)");
            } elseif ($data['sort'] == 'p.data_replicacao_ecommerce') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "(CASE WHEN p_data_replicacao_ecommerce IS NOT NULL THEN p_data_replicacao_ecommerce END)");
            } elseif (($data['sort'] == 'score') && (!empty($data['filter_name'])) && (!empty($data['module_request']) && ($data['module_request'] == 'produtosrelacionados'))) {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "match_value asc, " . $sqlOrderScore);
            } elseif (($data['sort'] == 'score') && (!empty($data['filter_name']))) {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "match_value desc, " . $sqlOrderScore);
            } elseif (($data['sort'] == 'score') && (!empty($data['filter_tag']))) {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, $sqlOrderScore);
            } elseif (($data['sort'] == 'fp.pontos') && (!empty($data['sort']))) {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "fp_pontos");
            } else {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "LCASE(" . str_replace('.', '_', $data['sort']) . ")");
            }
        } else {
            if ($this->config->get('config_produto_ordenacao_padrao') == Type_OrdemProdutos::Tp_CadastroDec) {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "p_data_replicacao_ecommerce");
            } else {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "pd_name");
            }
        }

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

        //Ordeno pelo modelo porque se a descrição for igual esse pode ser um critério de desempate.
        $sqlorder .= ", p_model DESC";

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

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

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

        $sqlgrade = " union"
            . " select";

        if (!empty($data['filter_name'])) {
            if (isset($data_language)) {
                $sqlgrade .= " (match(pd.name) against (QUOTE('" . $this->db->escape($data['filter_name']) . "') in natural language mode)) as score,
                  (match(pt.descricao_ecommerce) against (QUOTE('" . $this->db->escape($data['filter_name']) . "') in natural language mode)) as score2,";
                $sqlgrade .= "if(LCASE(pd.name) = '" . utf8_strtolower($this->db->escape($data['filter_name'])) . "'
                 or LCASE(pt.descricao_ecommerce) = '" . utf8_strtolower($this->db->escape($data['filter_name'])) . "', 1, 0) as match_value,";
            } else {
                $sqlgrade .= " (match(pd.name) against (QUOTE('" . $this->db->escape($data['filter_name']) . "') in natural language mode)) as score,";
                $sqlgrade .= "if(LCASE(pd.name) = '" . utf8_strtolower($this->db->escape($data['filter_name'])) . "', 1, 0) as match_value,";
            }
        } elseif (!empty($data['filter_tag'])) {
            $sqlgrade .= " (match(pd.tag) against (QUOTE('" . $this->db->escape($data['filter_tag']) . "') in natural language mode)) as score,";
        }

        if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
            $sqlgrade .= " (fp.pontos) fp_pontos,";
        }

        $sqlgrade .= " p.product_id,"
            . "			(p.viewed)p_viewed,"
            . "     (p.sort_order)p_sort_order,"
            . "pd.name as pd_name,"
            . "(p.model) p_model,"
            . "     (p.qty) p_qty,"
            . "     (p.date_added) p_date_added,"
            . "      coalesce(p.data_replicacao_ecommerce, p.date_added, NULL) p_data_replicacao_ecommerce,"
            . "  		SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") as valor_product,"
            . "     (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"
            . " from " . DB_PREFIX . "product_grid pg"
            . " inner join " . DB_PREFIX . "product_grid_detail pgd on (pgd.product_grid_id = pg.product_grid_id)"
            . " inner join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id)"
            . " inner join " . DB_PREFIX . "product_description pd on (pd.product_id = pgd.product_id)"
            . " left join  " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
        $sqlgrade .= isset($data_language) ? " LEFT JOIN " . DB_PREFIX . "product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";

        $sqlFiltrograde = " ("
            . " select p2.product_id from oc_product p2"
            . " inner join oc_product_grid_detail pgd2 on (p2.product_id = pgd2.product_id)"
            . " INNER JOIN oc_product_grid pg2 ON (pg2.product_grid_id = pgd2.product_grid_id)"
            . " inner join oc_product_description pd2  on (pd2.product_id = p2.product_id)"
            . " LEFT JOIN oc_product_to_store p2s2     on (p2.product_id = p2s2.product_id)";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sqlFiltrograde .= " inner join " . DB_PREFIX . "category p2c2 on (p2c2.category_id = p2.category_id) and p2c2.category_id=".$data['filter_category_id'];
            } else {
                $sqlFiltrograde .=
                "   INNER JOIN oc_category nsub2 ON (p2.category_id  = nsub2.category_id)
                    LEFT  JOIN oc_category ncat2 ON (nsub2.parent_id = ncat2.category_id)
                    LEFT  JOIN oc_category ndep2 ON (ncat2.parent_id = ndep2.category_id)
                ";
            }
        }

        if (!empty($data['filter_order_promotion'])) {
            $sqlFiltrograde .= " INNER JOIN " . DB_PREFIX . "order_discount od2 ON (od2.product_id = p2.product_id) ";
        }

        if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
            $sqlFiltrograde .= " INNER JOIN oc_fidelity_present fp2 ON (p2.product_id = fp2.product_id AND p2.qty > 0)";
        }

        $sqlFiltrograde .= " where (pgd2.product_grid_id = pg.product_grid_id)"
            . " " . $filtrosgrade
            . " order by IF(p2.qty > 0 AND p2.product_grid_main='S', 0, 1) ASC, p2.qty desc"
            . " limit 1"
            . " ) = p.product_id ";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sqlgrade .= " inner join " . DB_PREFIX . "category p2c on (p2c.category_id = p.category_id) and p2c.category_id= ".$data['filter_category_id'];
            } else {
                $sqlgrade .=
                "   INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
                    LEFT  JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
                    LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
                ";
            }
        }

        if (!empty($data['filter_order_promotion'])) {
            $sqlgrade .= " INNER JOIN " . DB_PREFIX . "order_discount od ON (od.product_id = p.product_id) ";
        }

        if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
            $sqlgrade .= " INNER JOIN oc_fidelity_present fp ON (p.product_id = fp.product_id AND p.qty > 0)";
        }

        if($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']){
            $sqlgrade .= " INNER JOIN " . DB_PREFIX . "product_discount pdc on (pdc.product_id = p.product_id)"
                .  " INNER JOIN " . DB_PREFIX . "customer_group cg on (cg.customer_group_id = pdc.customer_group_id)";
        }

        $sqlIn = ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());

        if ($this->config->get('futuragrade_status')) {
            $sql .=
                " " . $filtro . " and p.tipo not in " . $sqlIn
                . " " . $sqlgrade
                . " " . $filtro2 . " and p.tipo in " . $sqlIn
                . " and " . $sqlFiltrograde
                . " " . $sqlorder
                . " " . $limit;
        } else {
            $sql .= $filtro . ' ' . $sqlorder . ' ' . $limit;
        }

        $product_data = array();
        $cache = false;

        if (!empty($data['module_cached'])) {
            $cache = $this->cache->get($data['module_cached'] . '.' . md5($sql) . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'));
        }

        if (!$cache) {
            $query = $this->db->query($sql);
            foreach ($query->rows as $result) {
                $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
            }
            if (!empty($data['module_cached'])) {
                $this->cache->set($data['module_cached'] . '.' . md5($sql) . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'), $product_data);
            }
        } else {
            $product_data = $cache;
        }

        return $product_data;

    }/*Fim da get products*/

    public function getProductColor($color_id)
    {
        $sql = " select * from " . DB_PREFIX . "product_color"
            . " where productcolor_id = $color_id";

        $query = $this->db->query($sql);
        if ($query->num_rows) {
            return array(
                'primary_color' => $query->row['primary_color'],
                'secundary_color' => $query->row['secundary_color'],
                'description' => $query->row['description']
            );
        }
        return false;
    }

    public function getProductSize($size_id)
    {
        $sql = " select * from " . DB_PREFIX . "product_size"
            . " where productsize_id = $size_id";

        $query = $this->db->query($sql);
        if ($query->num_rows) {
            return array(
                'productsize_id' => $query->row['productsize_id'],
                'description' => $query->row['description']
            );
        }
        return false;
    }

    public function getProductSpecification($specification_id)
    {
        $sql = " select * from " . DB_PREFIX . "product_specification"
            . " where productspecification_id = $specification_id";

        $query = $this->db->query($sql);
        if ($query->num_rows) {
            return array(
                'productspecification_id' => $query->row['productspecification_id'],
                'description' => $query->row['description']
            );
        }
        return false;
    }

    // ---------- start add random products module ----------
    public function getRProducts($data = array())
    {
        $data_language = $this->config->get('config_data_language');
        if (!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) {
            unset($data_language);
        }

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

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

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

        $sql = "SELECT *,";
        if (isset($data_language)) {
            $sql .= "coalesce(pt.descricao_ecommerce, pd.name) as name,";
            $sql .= "coalesce(pt.descricao_completa, pd.description) as description,";
            $sql .= "coalesce(pt.descricao_completa_sf, pd.description_complete_s_format) AS description_complete_s_format,";
        }
        $sql .= "p.product_id, p.data_atualizacao_replicacao,
        (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
        FROM " . DB_PREFIX . "product p
        LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)";
        $sql .= isset($data_language) ? " LEFT JOIN " . DB_PREFIX . "product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
        $sql .= "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 (($data['filter_quantity'])) {
            $sql .= ' and p.qty > 0';
        }

        $sql .= " GROUP BY p.product_id ORDER BY Rand()";

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

        $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;
    }

    // ---------- end random products module ----------


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

        $this->load->type('tipocomum');

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

        $sql = " SELECT DISTINCT od.product_id,"
            . " (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = od.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating,"
            . " SP_GET_PRODUTO_VALORFINAL(od.product_id, " . $customer_group_id . ") as valor_product,"
            . " p.data_atualizacao_replicacao "
            . " FROM " . DB_PREFIX . "order_discount od"
            . " LEFT JOIN " . DB_PREFIX . "product p ON (od.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 ((od.tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") OR (od.tipo_promocao = " . Type_Promocao::Tp_Brinde . "))"
            . " and (od.status        = " . Type_PromocaoStatus::Tp_Ativo . ")"
            . " AND ((od.date_start = '0000-00-00' OR od.date_start <= CURDATE()) AND (od.date_end = '0000-00-00' OR od.date_end >= CURDATE()))";

        if ((!empty($data['order_promotion_id'])) && ($data['order_promotion_id'] != Type_DefaultValues::Tp_PromocaoGeral)) {
            $sql .= " AND od.order_promotion_id = " . $data['order_promotion_id'];
        }

        if (isset($data['filter_status']) && !is_null($data['filter_status']) && $data['filter_status'] == 0 && $data['filter_status'] <> '') {
            $sql .= " AND od.status = " . Type_TypeIntregationServer::Tp_ServerHabilitado;
        }

        if ($this->isEnabledAviseme()) {
            $sql .= " AND p.qty > 0 ";
        }

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

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

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= ($this->isEnabledAviseme()) ? " ORDER BY (CASE WHEN p.qty > 0 THEN 0  ELSE 1 END) ASC, LCASE(" . $data['sort'] . ")" : " ORDER BY LCASE(" . $data['sort'] . ")";
            } elseif ($data['sort'] == 'p.price') {
                $sql .= ($this->isEnabledAviseme()) ? " ORDER BY (CASE WHEN p.qty > 0 THEN 0  ELSE 1 END) ASC, valor_product" : " ORDER BY valor_product";
            } elseif ($data['sort'] == 'od.order_discount_id') {
                $sql .= ($this->isEnabledAviseme()) ? " ORDER BY (CASE WHEN p.qty > 0 THEN 0  ELSE 1 END) ASC, od.order_discount_id" : " ORDER BY od.order_discount_id";
            } elseif ($data['sort'] == 'od.date_product_added') {
                $sql .= ($this->isEnabledAviseme()) ? " ORDER BY (CASE WHEN p.qty > 0 THEN 0  ELSE 1 END) ASC, od.date_product_added" : " ORDER BY od.date_product_added";
            }
        } 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 getProductTotalSpecials($data = array())
    {

        $this->load->type('tipocomum');

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

        $sql = " SELECT DISTINCT count(od.product_id) as total,"
            . " (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = od.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating,"
            . " SP_GET_PRODUTO_VALORFINAL(od.product_id, " . $customer_group_id . ") as valor_product,"
            . " p.data_atualizacao_replicacao "
            . " FROM " . DB_PREFIX . "order_discount od"
            . " LEFT JOIN " . DB_PREFIX . "product p ON (od.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 ((od.tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") OR (od.tipo_promocao = " . Type_Promocao::Tp_Brinde . "))"
            . " and (od.status        = " . Type_PromocaoStatus::Tp_Ativo . ")"
            . " AND ((od.date_start = '0000-00-00' OR od.date_start <= CURDATE()) AND (od.date_end = '0000-00-00' OR od.date_end >= CURDATE()))";

        if ((!empty($data['order_promotion_id'])) && ($data['order_promotion_id'] != Type_DefaultValues::Tp_PromocaoGeral)) {
            $sql .= " AND od.order_promotion_id = " . $data['order_promotion_id'];
        }

        if (isset($data['filter_status']) && !is_null($data['filter_status']) && $data['filter_status'] == 0 && $data['filter_status'] <> '') {
            $sql .= " AND od.status = " . Type_TypeIntregationServer::Tp_ServerHabilitado;
        }

        if ($this->isEnabledAviseme()) {
            $sql .= " AND p.qty > 0 ";
        }

        //comentado para nao bugar o total na modules in page
        //$sql .= " GROUP BY od.product_id";

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

        return ($query->rows) ? $query->row['total'] : 0;
    }


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

        $product_data = array();
        $sql = "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;
        $query = $this->db->query($sql, true);

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

        return $product_data;
    }


    public function getBestSellerProducts($limit = false, $start = false)
    {
        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getCustomerGroupId();
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
        }

        if ($start && $limit) {
            $limit = $start . ' , ' . $limit;
        }

        $sql = "SELECT
      op.product_id,
      (pgd.product_grid_id),
      p.data_atualizacao_replicacao,
      COUNT(*) AS total
      FROM " . DB_PREFIX . "order_product op
      LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id)
      LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id)
      LEFT JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_id
      LEFT JOIN " . DB_PREFIX . "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') . "' AND p.qty > 0 AND p.tipo not in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList()) . "
      GROUP BY op.product_id

      UNION

      SELECT op2.product_id,
      (pgd2.product_grid_id),
      p2.data_atualizacao_replicacao,
      COUNT(*) AS total
      FROM oc_order_product op2
      LEFT JOIN `oc_order` o2 ON (op2.order_id = o2.order_id)
      LEFT JOIN `oc_product` p2 ON (op2.product_id = p2.product_id)
      LEFT JOIN oc_product_grid_detail pgd2 ON pgd2.product_id = p2.product_id
      LEFT JOIN oc_product_to_store p2s2 ON (p2.product_id = p2s2.product_id)
      WHERE o2.order_status_id > '0' AND p2.status = '1' AND p2.date_available <= NOW() AND p2s2.store_id = '" . (int)$this->config->get('config_store_id') . "' AND p2.qty > 0 AND p2.tipo in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList()) . "
      GROUP BY pgd2.product_grid_id


      ORDER BY total DESC" . ($limit ? " LIMIT " . $limit : "");

        $query = $this->db->query($sql, true, Type_TimeCache::Tp_Medio);

        $product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $customer_group_id . '.' . $limit);

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

            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') . '.' . $customer_group_id . '.' . $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 " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "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 " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "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 getProductOptions($product_id)
    {
        $product_option_data = array();

        $product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "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') . "' ORDER BY o.sort_order");

        foreach ($product_option_query->rows as $product_option) {
            if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
                $product_option_value_data = array();

                $product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "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 ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' 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['image'],
                        'quantity' => $product_option_value['quantity'],
                        'subtract' => $product_option_value['subtract'],
                        'price' => $product_option_value['price'],
                        'price_prefix' => $product_option_value['price_prefix'],
                        'weight' => $product_option_value['weight'],
                        'weight_prefix' => $product_option_value['weight_prefix']
                    );
                }

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

        return $product_option_data;
    }

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

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$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 getProductVideos($product_id)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_video WHERE product_id = '" . (int)$product_id . "' ORDER BY product_video_id 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 $this->config->get('config_layout_product');
        }
    }

    public function getCategories($product_id)
    {
        $product_category_data = array();

        $result = $this->db->query("
            SELECT nsub.category_id sub_category_id,ncat.category_id category_id,ndep.category_id dept_id
            FROM oc_product p
            INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
            INNER JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
            LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
            WHERE p.product_id = '" . (int)$product_id . "'")->row;

        if(isset($result)){

            if (isset($result['sub_category_id']) && ($result['sub_category_id'] !== null)){
                $product_category_data[] = $result['sub_category_id'];
            }

            if (isset($result['category_id']) && ($result['category_id'] !== null)){
                $product_category_data[] = $result['category_id'];
            }

            if (isset($result['dept_id']) && ($result['dept_id'] !== null)){
                $product_category_data[] = $result['dept_id'];
            }

        }

        return $product_category_data;
    }

    public function getTotalProducts($data = array())
    {
        $this->getFilters();//função utilizada para setar os filtros que seram usados na pesquisa by Dionis
        $data = ftr_geral::ConvertCaracter($data);

        $this->load->type('tipocomum');
        $this->load->helper('ftr_geral');

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

        $data['filter_content'] = (ftr_geral::isIsset($this->config->get('config_setting_search_name'))) ? $this->config->get('config_setting_search_name') : Type_FilterContent::Tp_Default;

        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total";
        $sql .= " FROM " . DB_PREFIX . "product p";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " INNER JOIN oc_category cp ON (cp.category_id = p.category_id) and cp.category_id=".$data['filter_category_id'];
            } else {
                $sql .=
                "   INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
                    LEFT  JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
                    LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
                ";
            }

        }

        if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
            $sql .= " INNER JOIN oc_fidelity_present fp ON (p.product_id = fp.product_id)";
        }

        $filtro = "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['not_product_id']) && $data['not_product_id']) {
            $filtro .= " AND p.product_id not in(" . $data['not_product_id'] . ")";
        }

        if ($this->config->get('futuragrade_status')) {
            if (!empty($data['filcolor'])) {
                $filtro .= " and p.product_color_id in(" . $data['filcolor'] . ")";
            }

            if (!empty($data['filsize'])) {
                $filtro .= " and p.product_size_id in(" . $data['filsize'] . ")";
            }

            if (!empty($data['filspecification'])) {
                $filtro .= " and p.product_specification_id in(" . $data['filspecification'] . ")";
            }

            if (!empty($data['filbrand'])) {
                $filtro .= " and p.manufacturer_id in(" . $data['filbrand'] . ")";
            }
        }

        $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)";

        if (($this->config->get('futurafiltrovalor_status'))) {

            if ((!empty($data['price_initial'])) && (!empty($data['price_laste']))) {
                $filtro .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") BETWEEN " . $data['price_initial'] . " and " . $data['price_laste'];
            } elseif ((!empty($data['price_initial']))) {
                $filtro .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") >= " . $data['price_initial'];
            } elseif ((!empty($data['price_laste']))) {
                $filtro .= " AND SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") <= " . $data['price_laste'];
            }

            if (($this->isEnabledAviseme()) && ((!empty($data['price_initial'])) || (!empty($data['price_laste'])))) {
                $filtro .= " AND p.qty > 0 ";
            }
        }

        if ($this->config->get('futurafiltromarca_status')) {
            if (!empty($data['filbrand'])) {
                $filtro .= " and p.manufacturer_id in(" . $data['filbrand'] . ")";
            }
        }

        if (!empty($data['filter_order_promotion'])) {
            $sql .= " INNER JOIN " . DB_PREFIX . "order_discount od ON (od.product_id = p.product_id) ";
            $filtro .= " AND (od.order_promotion_id = '" . (int)$data['filter_order_promotion'] . "')"
                . " AND (od.date_start = '0000-00-00' OR od.date_start <= CURDATE())"
                . " AND (od.date_end = '0000-00-00' OR od.date_end >= CURDATE())"
                . " and ((od.tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") OR (od.tipo_promocao = " . Type_Promocao::Tp_Brinde . "))"
                . " and (od.status        = " . Type_PromocaoStatus::Tp_Ativo . ")";
        }

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $filtro .= " AND cp.category_id = '" . (int)$data['filter_category_id'] . "'";
            } else {
                $filtro .= " AND (nsub.category_id = '" . (int)$data['filter_category_id']
                . "' OR ncat.category_id = '" . (int)$data['filter_category_id']
                . "' OR ndep.category_id = '" . (int)$data['filter_category_id']."') ";
            }
        }

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

            if (!empty($data['filter_name']) && ($data['filter_content'] == Type_FilterContent::Tp_Default)) {
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));
                $word_search = '';
                foreach ($words as $word) {
                    if (strlen($word) < 3) {
                        $word_search .= ' ' . $word;
                    } else {
                        $word_search = $word;
                    }

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

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

                if (isset($this->config_setting_search[Type_SearchOption::Tp_DescricaoCompleta])) {
                    $filtro .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

            if (!empty($data['filter_name']) && ($data['filter_content'] != Type_FilterContent::Tp_Default)) {
                $filtro .= "pd.name " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content']);
            }

            if (!empty($data['filter_name']) && isset($this->config_setting_search[Type_SearchOption::Tp_Tag])) {
                $implode = array();
                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

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

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

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

            if (!empty($data['filter_tag'])) {
                $filtro .= "pd.tag LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%'";
            }

            if (!empty($data['filter_name'])) {
                if ($this->config->get('futuragrade_status')) {
                    $filtro .= "OR LCASE(pg.referencia) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
                }
                foreach ($this->filtersField as $value) {
                    $filtro .= " OR LCASE(" . $value . ") = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                }
            }

            $filtro .= ")";
        }

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

        if (!empty($data['name']) && ($data['name'] == Type_ModuleListProduct::Tp_ProductViewedAll)) {
            $filtro .= " AND p.viewed > 0 AND p.status = " . Type_Status::Tp_Habilitado;
        }

        if (!empty($data['productsId'])) {
            $filtro .= " AND p.product_id IN (" . $data['productsId'] . ") ";
        }

        if ($this->config->get('futuragrade_status')) {
            $sqlIn = ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());
            $filtro .= " and ((p.tipo not in " . $sqlIn . ") or (pgd.product_grid_id is not null)) ";
        }

        if (!empty ($data['filter_quantity'])) {
            $filtro .= " AND p.qty > 0 ";
        }

        if ($this->config->get('futuragrade_status')) {
            $sqlAux = " left join"
                . " 	(select"
                . " 	   (pgd.product_id)product_id,"
                . "	   (pgd.product_grid_id)product_grid_id,"
                . "	   (pg.referencia)referencia"
                . "    from " . DB_PREFIX . "product_grid_detail pgd"
                . "    INNER JOIN oc_product_grid pg ON (pg.product_grid_id = pgd.product_grid_id)"
                . "    inner join oc_product p on (p.product_id = pgd.product_id and p.status = '1')"
                . "    inner join oc_product_description pd on (pd.product_id = p.product_id)"
                . " 	 LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) ";


            if (!empty($data['filter_category_id'])) {
                if (!empty($data['filter_sub_category'])) {
                    $sqlAux .= " INNER JOIN " . DB_PREFIX . "category cp ON (cp.category_id = p.category_id) and cp.category_id =".$data['filter_category_id'];
                } else {
                    $sqlAux .=
                    "   INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
                        LEFT  JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
                        LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
                    ";
                }
            }

            if (!empty($data['filter_order_promotion'])) {
                $sqlAux .= " INNER JOIN " . DB_PREFIX . "order_discount od ON (od.product_id = p.product_id) ";
            }

            if (!empty($data['fidelity']) && ($this->config->get('fidelity_status'))) {
                $sqlAux .= " INNER JOIN oc_fidelity_present fp ON (p.product_id = fp.product_id)";
            }

            $sqlAux .= " " . $filtro
                . "    group by pgd.product_grid_id"
                . ") as pgd on (pgd.product_id = p.product_id)";
            $sql .= $sqlAux;
        }

        if ($this->config->get('futuragrade_status')) {
            $sql .= " " . str_replace("pg.", "pgd.", $filtro);////troco o alias da produto grade para a detail
        } else {
            $sql .= " " . $filtro;
        }



        $query = $this->db->query($sql, true, Type_TimeCache::Tp_Medio);

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

    public function getProductdGrade($color = false, $size = false, $specification = false, $gridid = false)
    {

        $sql = "select (pgd.product_id) product_id,"
            . " (pgd.product_color_id) product_color_id,"
            . " (pgd.product_size_id)  product_size_id,"
            . " (pgd.product_specification_id) product_specification_id"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " inner join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1') ";


        $filtro = "";

        if ($color) {
            $filtro = " where pgd.product_color_id = " . $color;
        }


        if ($gridid) {
            if ($filtro == "") {
                $filtro = " where pgd.product_grid_id = " . $gridid;
            } else {
                $filtro .= " and pgd.product_grid_id = " . $gridid;
            }

        }

        /*
            O código abaixo teve que ser implementado por motivo de grade dinamica, ou seja,
            na cor AZUL por exemplo, tem tamanho 38, 39. No amarelo tem 40 e 41. Ao selecionar
            o azul no 38, e depois procurar o amarelo, não trazia retorno porque tentava
            procurar o amarelo no tamanho 38 e não encontrava. Então estou manipulando o array
            e ir removendo aquilo que não me interessa.	ByMatheus
          */

        $sql .= $filtro
            . " order by p.qty desc";


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

        $verificasize = false;
        $verificaSpecification = false;
        $product_id = false;

        //primeiro busca se tem as duas condições, que é no caso da quantidade e espeficicação estiverem certas...
        if (($size) && ($specification)) {
            foreach ($query->rows as $verif) {

                if (($size == $verif['product_size_id']) && ($specification == $verif['product_specification_id'])) {
                    $verificasize = true;
                    $verificaSpecification = true;
                    $product_id = $verif['product_id'];
                    break;
                }

            }

        }

        //já que não tem nenhum só com tamanho e espeficicação, vamos buscar se tem com TAMANHO primeiro...
        if (!($product_id) && ($size)) {
            foreach ($query->rows as $verif) {
                if (($size == $verif['product_size_id'])) {
                    $verificasize = true;
                    $product_id = $verif['product_id'];
                    break;
                }
            }

        }

        //já que não tem nenhum só com tamanho e espeficicação, vamos buscar se tem com ESPEFICICAÇÃO apenas...
        if (!($product_id) && ($specification)) {
            foreach ($query->rows as $verif) {
                if (($specification == $verif['product_specification_id'])) {
                    $verificaSpecification = true;
                    $product_id = $verif['product_id'];
                    break;
                }
            }

        }


        //se não deu certo em nenhum, pega apenas o primeiro produto, e vai perder a seleção dos itens errados...
        if (!($product_id) && ($query->rows)) {
            $product_id = $query->row['product_id'];
        }


        $retorno = array(
            'sizeexists' => $verificasize,
            'specificationexists' => $verificaSpecification,
            'product_id' => $product_id
        );

        return $retorno;

    }

    //ESSA FUNÇÃO RETORNA QUAIS ITENS O PRODUTO TEM NA GRADE.
    public function getGridHave($productid)
    {
        $language = $this->language->load('module/futuragrade');
        $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');
        $title_size = $this->language->get('heading_title_size');
        $title_cor = $this->language->get('heading_title_cor');
        $sql = " select"
            . "   (coalesce(pgd.product_grid_id, '')) 	       grid_id,"
            . "   (coalesce(pc.productcolor_id, '')) 	       color_id,"
            . "   (coalesce(ps.productsize_id, ''))  		   size_id,"
            . "   (coalesce(psp.productspecification_id, '')) specification_id,"
            . "   (COALESCE(pgt.descricao_cor,'$title_cor')) template_cor,"
            . "   (COALESCE(pgt.descricao_tamanho,'$title_size')) template_tamanho,"
            . "   (COALESCE(pgt.descricao_especificacao,'$title_specification')) template_especificacao"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " left join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1')"
            . " left join " . DB_PREFIX . "product_color          pc  on (pc.productcolor_id          = pgd.product_color_id)"
            . " left join " . DB_PREFIX . "product_size           ps  on (ps.productsize_id           = pgd.product_size_id)"
            . " left join " . DB_PREFIX . "product_specification  psp on (psp.productspecification_id = pgd.product_specification_id)"
            . " LEFT JOIN oc_product_grid pg ON pgd.product_grid_id = pg.product_grid_id"
            . " LEFT JOIN oc_product_grid_template pgt ON pgt.product_grid_template_id = pg.product_grid_template_id"
            . " where pgd.product_id = " . $productid
            . " group by productcolor_id";

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

        $retorno = array();
        if ($query->num_rows) {
            $retorno['grid_id'] = ($query->row['grid_id'] == '') ? false : $query->row['grid_id'];
            $retorno['have_color'] = ($query->row['color_id'] == '') ? false : true;
            $retorno['have_size'] = ($query->row['size_id'] == '') ? false : true;
            $retorno['have_specification'] = ($query->row['specification_id'] == '') ? false : true;

            $retorno['template_cor'] = ($query->row['template_cor'] == '') ? false : $query->row['template_cor'];
            $retorno['template_tamanho'] = ($query->row['template_tamanho'] == '') ? false : $query->row['template_tamanho'];
            $retorno['template_especificacao'] = ($query->row['template_especificacao'] == '') ? false : $query->row['template_especificacao'];
        } else {
            $retorno['grid_id'] = false;
            $retorno['have_color'] = false;
            $retorno['have_size'] = false;
            $retorno['have_specification'] = false;

            $retorno['template_cor'] = false;
            $retorno['template_tamanho'] = false;
            $retorno['template_especificacao'] = false;
        }

        return $retorno;

    }

    public function getIDProductGrid($productid)
    {
        $sql = "select product_grid_id from " . DB_PREFIX . "product_grid_detail where product_id = " . $productid;
        $query = $this->db->query($sql);

        return ($query->num_rows) ? $query->row['product_grid_id'] : false;
    }

    public function getProductsGridHaveColors($data)
    {
        $sql = " select p.product_id"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " inner join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1')"
            . " where pgd.product_grid_id = " . $data['grid_id'] . " and pgd.product_size_id is null and pgd.product_specification_id is null";


        $product_data = array();
        $query = $this->db->query($sql, true, Type_TimeCache::Tp_Medio);

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

        return ($product_data) ? $product_data : false;

    }

    public function getGridColor($gridid, $productid, $data = array())
    {

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

        $where = " where pgd.product_grid_id = " . $gridid
            . " and (p.price > 0 or (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) > 0)";

        if (!empty($data['specification'])) {
            $where .= " and (pgd.product_specification_id = " . $data['specification'] . ")";
        }

        if (!empty($data['size'])) {
            $where .= " and (pgd.product_size_id = " . $data['size'] . ")";
        }

        $join = '';
        if (!empty($data['fidelity'])) {
            $join .= " inner join oc_fidelity_present fp ON (pgd.product_id = fp.product_id) ";
        }

        $sql = " select"
            . "   (if(p.product_id = " . $productid . ", 'S', 'N')) cor_selecionada,"
            . "   (pc.productcolor_id)productcolor_id,"
            . "   (pc.description)description,"
            . "   (pc.primary_color)primary_color,"
            . "   (pc.secundary_color)secundary_color,"
            . "   (case when (sum(p.qty) > 0) then 'S' else 'N' end) as tem_estoque,"
            . "   (p.image)produto_img_princ,"
            . "   (p.data_atualizacao_replicacao)data_atualizacao_replicacao,"
            . "   (ptc.sequencia)ordem"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " inner join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1')"
            . " inner join " . DB_PREFIX . "product_color pc on (pc.productcolor_id = pgd.product_color_id)"
            . " left  join " . DB_PREFIX . "product_grid_templ_color ptc on (pc.productcolor_id = ptc.productcolor_id)"
            . $join
            . $where
            . " group by productcolor_id"
            . " order by ordem, description";

        $query = $this->db->query($sql);
        return ($query->num_rows) ? $query->rows : false;
    }

    public function getGridSelected($productid)
    {
        $language = $this->language->load('module/futuragrade');
        $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');
        $title_size = $this->language->get('heading_title_size');
        $title_cor = $this->language->get('heading_title_cor');
        $sql = " select"
            . "   (pc.productcolor_id) 			color_id,"
            . "   (pc.description)     			color_description,"
            . "   (COALESCE(pgt.descricao_cor,'$title_cor')) template_cor,"
            . "   (ps.productsize_id)  			size_id,"
            . "   (ps.description)     			size_description,"
            . "   (COALESCE(pgt.descricao_tamanho,'$title_size')) template_tamanho,"
            . "   (psp.productspecification_id)    specification_id,"
            . "   (psp.description)    			specification_description,"
            . "   (COALESCE(pgt.descricao_especificacao,'$title_specification')) template_specificacao"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " left join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1')"
            . " left join " . DB_PREFIX . "product_color          pc  on (pc.productcolor_id          = pgd.product_color_id)"
            . " left join " . DB_PREFIX . "product_size           ps  on (ps.productsize_id           = pgd.product_size_id)"
            . " left join " . DB_PREFIX . "product_specification  psp on (psp.productspecification_id = pgd.product_specification_id)"
            . " LEFT JOIN oc_product_grid pg ON pgd.product_grid_id = pg.product_grid_id"
            . " LEFT JOIN oc_product_grid_template pgt ON pgt.product_grid_template_id = pg.product_grid_template_id"
            . " where pgd.product_id = " . $productid
            . " group by productcolor_id";


        $query = $this->db->query($sql);
        return ($query->num_rows) ? $query->row : false;

    }

    public function getGridSize($gridid, $data = array())
    {

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

        $join = "";
        if (!empty($data['fidelity'])) {
            $join .= " inner join oc_fidelity_present fp ON (pgd.product_id = fp.product_id) ";
        }

        $sql = " select"
            . "   (ps.productsize_id)productsize_id,"
            . "   (case when (sum(p.qty) > 0) then 'S' else 'N' end) as tem_estoque,"
            . "   (ps.description)description,"
            . "   (pts.sequencia)ordem"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " inner join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1')"
            . " inner join " . DB_PREFIX . "product_size ps on (ps.productsize_id = pgd.product_size_id)"
            . " left  join " . DB_PREFIX . "product_grid_templ_size pts on (ps.productsize_id = pts.productsize_id)"
            . $join;

        $filtro = " where pgd.product_grid_id = " . $gridid
            . " and (p.price > 0 or (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) > 0)";

        if (!empty($data['color'])) {
            $filtro .= " and (pgd.product_color_id = " . $data['color'] . ")";

        }

        if (!empty($data['specification'])) {
            $filtro .= " and (pgd.product_specification_id = " . $data['specification'] . ")";

        }

        $groupby = " group by productsize_id";

        $ordem = " order by ordem, description";

        $sql .= $filtro . $groupby . $ordem;

        $query = $this->db->query($sql);
        return ($query->num_rows) ? $query->rows : false;
    }

    public function getGridSpecification($gridid, $data = array())
    {

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

        $join = "";
        if (!empty($data['fidelity'])) {
            $join .= " inner join oc_fidelity_present fp ON (pgd.product_id = fp.product_id) ";
        }

        $sql = " select"
            . "   (ps.productspecification_id)productspecification_id,"
            . "   (case when (sum(p.qty) > 0) then 'S' else 'N' end) as tem_estoque,"
            . "   (ps.description)description,"
            . "   (pts.sequencia)ordem"
            . " from " . DB_PREFIX . "product_grid_detail pgd"
            . " inner join " . DB_PREFIX . "product p on (p.product_id = pgd.product_id and p.status = '1')"
            . " inner join " . DB_PREFIX . "product_specification ps on (ps.productspecification_id = pgd.product_specification_id)"
            . " left  join " . DB_PREFIX . "product_grid_templ_spec pts on (ps.productspecification_id = pts.productspecification_id)"
            . $join;

        $where = " where pgd.product_grid_id = " . $gridid
            . " and (p.price > 0 or (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) > 0)";

        if (!empty($data['color'])) {
            $where .= " and (pgd.product_color_id = " . $data['color'] . ")";
        }

        if (!empty($data['size'])) {
            $where .= " and (pgd.product_size_id = " . $data['size'] . ")";
        }

        $groupby = " group by productspecification_id";

        $ordem = " order by ordem, description";

        $sql .= $where . $groupby . $ordem;

        $query = $this->db->query($sql);
        return ($query->num_rows) ? $query->rows : false;
    }

    public function getProfiles($product_id)
    {
        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getCustomerGroupId();
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
        }
        return $this->db->query("SELECT `pd`.* FROM `" . DB_PREFIX . "product_profile` `pp` JOIN `" . DB_PREFIX . "profile_description` `pd` ON `pd`.`language_id` = " . (int)$this->config->get('config_language_id') . " AND `pd`.`profile_id` = `pp`.`profile_id` JOIN `" . DB_PREFIX . "profile` `p` ON `p`.`profile_id` = `pd`.`profile_id` WHERE `product_id` = " . (int)$product_id . " AND `status` = 1 AND `customer_group_id` = " . (int)$customer_group_id . " ORDER BY `sort_order` ASC")->rows;
    }

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

        return $this->db->query("SELECT * FROM `" . DB_PREFIX . "profile` `p` JOIN `" . DB_PREFIX . "product_profile` `pp` ON `pp`.`profile_id` = `p`.`profile_id` AND `pp`.`product_id` = " . (int)$product_id . " WHERE `pp`.`profile_id` = " . (int)$profile_id . " AND `status` = 1 AND `pp`.`customer_group_id` = " . (int)$customer_group_id)->row;
    }

    public function getGradeProdutosList($data = array(), $tableFilter = '')
    {
        $language = $this->language->load('module/futuragrade');


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

        $sql = "SELECT";

        switch (strtoupper($tableFilter)) {
            case 'PRODUCT_COLOR' :
                $title_cor = $this->language->get('heading_title_cor');
                $sql .= " (p.product_color_id) product_color_id,";
                $sql .= " (COALESCE(pgt.descricao_cor,'$title_cor')) descricao_especificacao,";
                $sql .= " (pgts.sequencia) ordem, ";
                $sql .= " (pesp.description)	  description,";
                $sql .= " (pesp.primary_color) primary_color,";
                $sql .= " (pesp.secundary_color) secundary_color";
                break;
            case 'PRODUCT_SIZE' :
                $title_size = $this->language->get('heading_title_size');
                $sql .= " (p.product_size_id) product_size_id,";
                $sql .= " (COALESCE(pgt.descricao_tamanho,'$title_size')) descricao_especificacao,";
                $sql .= " (pgts.sequencia) ordem, ";
                $sql .= " (pesp.description)	  description";
                break;
            case 'PRODUCT_SPECIFICATION':
                $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');
                $sql .= " (p.product_specification_id) product_specification_id,";
                $sql .= " (COALESCE(pgt.descricao_especificacao,'$title_specification')) descricao_especificacao,";
                $sql .= " (pgts.sequencia) ordem, ";
                $sql .= " (pesp.description)	  description";
                break;
            case 'PRODUCT_MANUFACTURER' :
                //ajuste feito para funcionar com o modulo de filtro da marca
                $sql .= " (p.manufacturer_id) manufacturer_id,";
                $sql .= " (pesp.name) name";
                break;
        }

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

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " INNER JOIN " . DB_PREFIX . "category p2c ON (p2c.category_id = p.category_id) and p2c.category_id =".$data['filter_category_id'];
            } else {
                $sql .=
                "   INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
                    LEFT  JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
                    LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
                ";
            }

            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)";
            }
        }


        $sql .= " LEFT JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_id";
        $sql .= " LEFT JOIN oc_product_grid pg ON pgd.product_grid_id = pg.product_grid_id";
        $sql .= " LEFT JOIN oc_product_grid_template pgt ON (pg.product_grid_template_id = pgt.product_grid_template_id) ";

        switch (strtoupper($tableFilter)) {
            case 'PRODUCT_COLOR' :
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_color        pesp  on (pesp.productcolor_id           = p.product_color_id)";
                $sql .= " LEFT JOIN oc_product_grid_templ_color pgts ON (pgts.productcolor_id = pesp.productcolor_id)";
                break;
            case 'PRODUCT_SIZE' :
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_size           pesp  on (pesp.productsize_id            = p.product_size_id)";
                $sql .= " LEFT JOIN oc_product_grid_templ_size pgts ON (pgts.productsize_id = pesp.productsize_id)";
                break;
            case 'PRODUCT_SPECIFICATION':
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_specification  pesp on (pesp.productspecification_id  = p.product_specification_id)";
                $sql .= " LEFT JOIN oc_product_grid_templ_spec pgts ON (pgts.productspecification_id = pesp.productspecification_id)";
                break;
            case 'PRODUCT_MANUFACTURER' :
                //ajuste feito para funcionar com o modulo de filtro da marca
                $sql .= " LEFT JOIN " . DB_PREFIX . "manufacturer  pesp on (pesp.manufacturer_id  = p.manufacturer_id)";
                $sql .= " INNER JOIN " . DB_PREFIX . "manufacturer_to_store  pmants on (pmants.manufacturer_id  = pesp.manufacturer_id)";
                break;
        }

        $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') . "'";


        switch (strtoupper($tableFilter)) {
            case 'PRODUCT_COLOR' :
                $sql .= " AND (p.product_color_id is not null)";
                break;
            case 'PRODUCT_SIZE' :
                $sql .= " AND (p.product_size_id is not null)";
                break;
            case 'PRODUCT_SPECIFICATION':
                $sql .= " AND (p.product_specification_id is not null)";
                break;
            case 'PRODUCT_MANUFACTURER' :
                //ajuste feito para funcionar com o modulo de filtro da marca
                $sql .= " AND (p.manufacturer_id is not null) AND (p.manufacturer_id > 0)";
                break;
        }

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

            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
            }else{
                $sql .= " AND (nsub.category_id = '" . (int)$data['filter_category_id']
                . "' OR ncat.category_id = '" . (int)$data['filter_category_id']
                . "' OR ndep.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['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(" OR ", $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 (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
        }

        if (!empty($data['filcolor']) && strtoupper($tableFilter) <> 'PRODUCT_COLOR') {
            $sql .= " AND(p.product_color_id in(" . $data['filcolor'] . "))";
        }
        if (!empty($data['filsize']) && strtoupper($tableFilter) <> 'PRODUCT_SIZE') {
            $sql .= " AND(p.product_size_id in(" . $data['filsize'] . "))";
        }
        if (!empty($data['filspecification']) && strtoupper($tableFilter) <> 'PRODUCT_SPECIFICATION') {
            $sql .= " AND(p.product_specification_id in(" . $data['filspecification'] . "))";
        }
        if (!empty($data['filbrand']) && strtoupper($tableFilter) <> 'PRODUCT_MANUFACTURER') {
            $sql .= " AND(p.manufacturer_id in(" . $data['filbrand'] . "))";
        }

        switch (strtoupper($tableFilter)) {
            case 'PRODUCT_COLOR' :
                $sql .= " GROUP BY pgt.product_grid_template_id, p.product_color_id";
                break;
            case 'PRODUCT_SIZE' :
                $sql .= " GROUP BY pgt.product_grid_template_id, p.product_size_id";
                break;
            case 'PRODUCT_SPECIFICATION':
                $sql .= " GROUP BY pgt.product_grid_template_id, p.product_specification_id";
                break;
            case 'PRODUCT_MANUFACTURER' :
                $sql .= " GROUP BY p.manufacturer_id";
                $sql .= " order by pesp.name";
                break;
        }

        if (strtoupper($tableFilter) <> 'PRODUCT_MANUFACTURER') {
            $sql .= " ORDER BY descricao_especificacao, ordem, pesp.description";
        }



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

        return ($query->num_rows) ? $query->rows : false;
    }


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

        $sqltotal = "SELECT"
            . " COUNT(DISTINCT od.product_id) AS total"
            . " FROM " . DB_PREFIX . "order_discount od"
            . " LEFT JOIN " . DB_PREFIX . "product p ON (od.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 <= current_timestamp"
            . "   AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"
            . "   AND ((od.date_start = '0000-00-00' OR od.date_start <= CURDATE())"
            . "   and ((od.tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") OR (od.tipo_promocao = " . Type_Promocao::Tp_Brinde . "))"
            . "   and (od.status        = " . Type_PromocaoStatus::Tp_Ativo . ")"
            . "   and (od.date_end = '0000-00-00' OR od.date_end >= CURDATE()))";

        if (isset($data['order_promotion_id'])) {
            $sqltotal .= ' and od.order_promotion_id = ' . (int)$data['order_promotion_id'];
        }

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

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

    public function getProductsGoogleBase()
    {

        $this->load->type('tipocomum');
        $this->load->type('tipofuturaserver');

        $customer_group_id = $this->config->get('config_customer_group_id');

        $data_language = $this->config->get('config_data_language');
        if (!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) {
            unset($data_language);
        }

        $sql = "SELECT ";
        $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as name," : "pd.name as name,";
        $sql .= " p.*, "
            . " (pc.description) as color, "
            . " (ps.description) as size, ";
        $sql .= isset($data_language) ? "coalesce(pt.descricao_completa, pd.description) as description," : "pd.description AS description,";
        $sql .= " (m.name) as manufacturer, "
            . " pgd.product_grid_id as id_grade, "
            . " SP_GET_PRODUTO_VALORFINAL(p.product_id, " . $customer_group_id . ") as valor_product,"
            . " CASE"
            . "    WHEN (m.name IS NOT NULL) THEN m.name"
            . "    WHEN (p.codigo_barra IS NOT NULL) THEN p.codigo_barra"
            . "    WHEN (p.mpn != '') THEN p.mpn"
            . "  END AS identificador,"

            . "  nsub.category_id sub_category_id,"
            . "  nsubdes.NAME sub_category_name,"
            . "  nsub.prod_maior_idade sub_category_prod_maior_idade,"
            . "  nsub.google_category_id sub_category_google_category_id,"
			. "  ncat.category_id category_id,"
			. "  ncatdes.NAME category_name,"
			. "  ncat.prod_maior_idade category_prod_maior_idade,"
            . "  ncat.google_category_id category_google_category_id,"
			. "  ndep.category_id dept_id,"
			. "  ndepdes.NAME dept_name,"
			. "  ndep.prod_maior_idade dept_prod_maior_idade,"
			. "  (SELECT GROUP_CONCAT(pimg.image) AS image FROM oc_product_image pimg WHERE pimg.product_id = p.product_id LIMIT 10) AS other_images"

            . " FROM " . DB_PREFIX . "product p "
            . " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) "

            . " INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id) "
            . " INNER JOIN oc_category_description nsubdes ON (nsub.category_id = nsubdes.category_id) "
            . " INNER JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id) "
            . " INNER JOIN oc_category_description ncatdes ON (ncat.category_id = ncatdes.category_id) "
            . " LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id) "
            . " LEFT  JOIN oc_category_description ndepdes ON (ndep.category_id = ndepdes.category_id) ";

        $sql .= isset($data_language) ? " LEFT JOIN oc_product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
        $sql .= " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) "
            . " LEFT JOIN " . DB_PREFIX . "product_color pc ON (pc.productcolor_id = p.product_color_id) "
            . " LEFT JOIN " . DB_PREFIX . "product_size ps ON (ps.productsize_id = p.product_size_id) "
            . " LEFT JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_id"
            . " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '" . Type_Status::Tp_Habilitado . "' AND p.list_in_feeds ='".Type_DsimDnaoServer::Tp_Sim. "'";

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

        return $query->rows;
    }


    public function getLinkProductMl($product_key_id, $type_produc = Type_ProdutoMkt::Tp_MktProd_Normal)
    {
        $this->load->helper('ftr_geral');
        $this->load->dmodel('catalog/productgrid');
        $retorno = false;
        if (ftr_geral::isIsset($this->config->get('mercadolivre_status'))) {

            $statusAtivo = Type_MercadoLivreItemStatus::Tp_Active;

            if ($type_produc == Type_ProdutoMkt::Tp_MktProd_Grade) {
                //Atualiza o product_id pelo id da grade que é salvo na tabela como ligação...
                $grid = $this->dmodel_catalog_productgrid->getGridDataFromProduto($product_key_id);
                $product_key_id = $grid['product_grid_id'];
            }

            $query = $this->db->query(
                "select
                    url
                from oc_marketplace_product
                where status = '$statusAtivo'
                  and product_key_id = $product_key_id
                  and tipo_produto = $type_produc");


            if ($query->num_rows) {
                $retorno = $query->row['url'];
            }
        }
        return $retorno;
    }


    public function getProductReturn($product_id)
    {
        $data_language = $this->config->get('config_data_language');
        if (!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) {
            unset($data_language);
        }

        $sql = "SELECT DISTINCT ";
        $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as name," : "pd.name as name,";
        $sql .= "p.product_id, p.model  FROM " . DB_PREFIX . "product p"
            . " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)";
        $sql .= isset($data_language) ? " LEFT JOIN oc_product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
        $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)"
            . " WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

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

        if ($query->num_rows) {
            return array(
                'product_id' => $query->row['product_id'],
                'name' => $query->row['name'],
                'model' => $query->row['model']
            );
        } else {
            return false;
        }
    }

    public function getProductPromotion($product_id, $order_promotion_id = 0)
    {
        $this->load->type('tipocomum');

        $sql = 'SELECT (op.description) AS description, date_start AS date_start_promotion, date_end AS date_end_promotion, op.order_promotion_id, od.tipo_promocao FROM ' . DB_PREFIX . 'order_promotion op '
            . 'INNER JOIN ' . DB_PREFIX . 'order_discount od ON (op.order_promotion_id = od.order_promotion_id) '
            . 'WHERE od.product_id = ' . $product_id . ' and op.status = ' . Type_TypeIntregationServer::Tp_ServerHabilitado;

        if($order_promotion_id){
            $sql .= ' and op.order_promotion_id = ' . $order_promotion_id;
        }

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

        if ($query->rows) {
            return $query->row;
        } else {
            return false;
        }
    }

    public function getProductPromotionList()
    {
        $this->load->type('tipocomum');
        $sql = 'SELECT distinct op.order_promotion_id, (op.description) AS description, date_start, date_end
    FROM ' . DB_PREFIX . 'order_promotion op ' .
            'INNER JOIN ' . DB_PREFIX . 'order_discount od ON (op.order_promotion_id = od.order_promotion_id) ' . 'WHERE op.status = ' . Type_TypeIntregationServer::Tp_ServerHabilitado;
        $query = $this->db->query($sql);
        if ($query->rows) {
            return $query->rows;
        } else {
            return false;
        }
    }

    protected function getTabelaPrecoPrice($tabela_preco_dinamico, $list_tabela_preco = array())
    {

        if (!$tabela_preco_dinamico) {
            return false;
        }

        $this->load->helper('ftr_geral');

        $arrayTabelaPreco = array();
        $indiceTabelaPreco = array_filter($this->config->get('preco_dinamico_tabelapreco_apelido'));

        foreach ($list_tabela_preco as $key => $value) {
            $list_tabela_preco[$key] = explode(',', $value);
        }

        foreach ($list_tabela_preco['list_tabela_preco_id'] as $key => $value) {
            $arrayTabelaPreco[$indiceTabelaPreco[$value]] = $list_tabela_preco['list_tabela_preco_price'][$key];
        }


        return $arrayTabelaPreco;

    }

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

        $start  = $data['start'];
        $limit  = $data['limit'];
        $status = $data['status'];
        $data['tabela_preco_dinamico'] = ftr_geral::isIsset($this->config->get('preco_dinamico_tabelapreco')) ? $this->config->get('preco_dinamico_tabelapreco') : false;
        $data['preco_dinamico_status'] = ftr_geral::isISset($this->config->get('preco_dinamico_status')) ? $this->config->get('preco_dinamico_status') : false;

        $sql = "SELECT
                  DISTINCT p.product_id
            FROM `" . DB_PREFIX . "order_product` op
            INNER JOIN `" . DB_PREFIX . "order` o ON (o.order_id = op.order_id)
            INNER JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id)
            INNER JOIN `" . DB_PREFIX . "category` p2c ON (p.category_id = p2c.category_id)
            LEFT OUTER JOIN `" . DB_PREFIX . "product_to_store` p2s ON p.product_id = p2s.product_id";

        if($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']){
            $sql .= " INNER JOIN " . DB_PREFIX . "product_discount pdc on (pdc.product_id = p.product_id)"
                .  " INNER JOIN " . DB_PREFIX . "customer_group cg on (cg.customer_group_id = pdc.customer_group_id)";
        }

        $sql .=  " WHERE o.customer_id IN (SELECT o.customer_id FROM `" . DB_PREFIX . "order_product` op INNER JOIN `" . DB_PREFIX . "order` o ON o.order_id = op.order_id WHERE op.product_id = '" . (int)$data['product_id'] . "' GROUP BY o.customer_id) AND p.product_id != '" . (int)$data['product_id'] . "'
            AND p.date_available <= NOW()
            AND o.date_added BETWEEN (NOW() - INTERVAL 3 MONTH) AND NOW()
            AND p.status = '$status'
            AND p.quantity > 0";

        if ($data['tabela_preco_dinamico'] AND $data['preco_dinamico_status']) {
            $tabela_preco_dinamico = implode(',', array_filter($data['tabela_preco_dinamico']));
            $sql .= " AND pdc.customer_group_id IN(" . $tabela_preco_dinamico . ") ";
        }

        $sql .= " GROUP BY op.product_id
            ORDER BY COUNT(*) DESC
            LIMIT $start, $limit
            ";

        $product_data = array();
        $cache = false;

        if (!empty($data['module_cached'])) {
            $cache = $this->cache->get($data['module_cached'] . '.' . md5($sql) . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'));
        }

        if (!$cache) {
            $query = $this->db->query($sql);
            foreach ($query->rows as $result) {
                $info_product = $this->getProduct($result['product_id']);
                    $product_data[$result['product_id']] = $info_product;

            }
            if (!empty($data['module_cached'])) {
                $this->cache->set($data['module_cached'] . '.' . md5($sql) . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'), $product_data);
            }
        } else {
            $product_data = $cache;
        }

        return $product_data;

    }

    public function getProductsBrandProducts($data = array())
    {
        $start  = $data['start'];
        $limit  = $data['limit'];
        $status = $data['status'];

        $sql = "SELECT DISTINCT p.product_id FROM ".DB_PREFIX."product p
                INNER JOIN ".DB_PREFIX."manufacturer m ON (m.manufacturer_id = p.manufacturer_id)
                WHERE p.manufacturer_id
                IN (SELECT op.manufacturer_id FROM ".DB_PREFIX."product op WHERE op.product_id = ".$data['product_id'].")
                AND p.quantity > '0'
                AND p.STATUS = '1' ";

        $sql .= " GROUP BY product_id
            ORDER BY COUNT(*) DESC
            LIMIT $start, $limit
            ";

        $product_data = array();
        $cache = false;

        if (!$cache) {
            $query = $this->db->query($sql);
            foreach ($query->rows as $result) {
                $info_product = $this->getProduct($result['product_id']);
                if($info_product){
                    $product_data[$result['product_id']] = $info_product;
                }
            }
            if (!empty($data['module_cached'])) {
                $this->cache->set($data['module_cached'] . '.' . md5($sql) . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'), $product_data);
            }
        } else {
            $product_data = $cache;
        }
        return $product_data;
    }

    public function getProductsWhoSaw($data = array()){
        $start  = $data['start'];
        $limit  = $data['limit'];

        $data['tabela_preco_dinamico'] = ftr_geral::isIsset($this->config->get('preco_dinamico_tabelapreco')) ? $this->config->get('preco_dinamico_tabelapreco') : false;
        $data['preco_dinamico_status'] = ftr_geral::isISset($this->config->get('preco_dinamico_status')) ? $this->config->get('preco_dinamico_status') : false;

        $sql = "SELECT DISTINCT ". DB_PREFIX ."product_customer_view.product_id
                FROM ". DB_PREFIX ."product_customer_view
                WHERE ". DB_PREFIX ."product_customer_view.customer_id
                IN (SELECT ".DB_PREFIX."product_customer_view.customer_id FROM ". DB_PREFIX ."product_customer_view
                WHERE ".DB_PREFIX."product_customer_view.product_id = ". $data['product_id'] .") ";

        $sql .= "GROUP BY ".DB_PREFIX."product_customer_view.product_id
            LIMIT $start, $limit";

        $product_data = array();
        $cache = false;

        if (!$cache) {
            $query = $this->db->query($sql);
            foreach ($query->rows as $result) {
                if($result['product_id'] != $data['product_id']){
                    $info_product = $this->getProduct($result['product_id']);
                    if($info_product){
                        $product_data[$result['product_id']] = $info_product;
                    }
                }
            }
            if (!empty($data['module_cached'])) {
                $this->cache->set($data['module_cached'] . '.' . md5($sql) . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'), $product_data);
            }
        } else {
            $product_data = $cache;
        }

        return $product_data;
    }
}
