<?php

class ModelCatalogProduct extends Model
{

    public function getProducts($data = array())
    {
        $sqlGridEmpty = '';
        $filtroPrincGrade = "";//Inclui no where principal apenas da grade...
        $sqlFiltroProdPrinc = "";
        $this->load->helper('ftr_geral');
        $this->load->type('tipocomum');

        $defaultLang = (int)$this->config->get('config_language_id');

        $data = ftr_geral::ConvertCaracter($data);

        $sql = "SELECT
                (false) as IsGrid,
                IF(pkit.product_kit_id > 0, TRUE, FALSE)IsKit,
                (p.model) as model_principal,
                (null)  as product_grid_id,
                p.product_id as product_key_id,
                pd.name,
                p.*,
                (pc.description) as color,
                (ps.description) as size,
                (psf.description) as specification,
                (p.qty) as total_quantity,
                (p.qty_mkt) as total_quantity_mkt,
                (m.name) as manufacturer_name
            FROM oc_product p
            LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
            LEFT JOIN oc_manufacturer m ON (m.manufacturer_id = p.manufacturer_id)
            LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id)
            LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id)
            LEFT JOIN oc_product_specification psf ON (psf.productspecification_id = p.product_specification_id)";


        if (!empty($data['filter_category_id'])) {
            $sql .= $this->getCategoryFilter($data['filter_category_id']);
        }

        if (isset($data['filter_kit'])&& $data['filter_kit']== Type_DsimDnao::Tp_Sim ){
            $sql .=  " INNER JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        } else {
            $sql .=  " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        }

        $sqlFiltro = " WHERE pd.language_id = '$defaultLang'";
        $filtroGrade = " WHERE pd2.language_id = '$defaultLang'";

        if (isset($data['filter_kit']) && $data['filter_kit'] == Type_DsimDnao::Tp_Nao){
            $sqlFiltro .= " and pkit.product_kit_id is null "; //Não precisa filtrar a grade pois não tem produto grade que seja Kit
        }

        if (!empty($data['filter_name'])) {
            $sqlFiltro .= " AND pd.name " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content_name']);
            $filtroGrade .= " AND pd2.name " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content_name']);
        }

        if (!empty($data['filter_model'])) {
            $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:
                    $sqlFiltroProdPrinc = (isset($data['filter_content_model'])) ? " AND p.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";

                    $filtroGrade .= " AND (";
                    $filtroGrade .= (isset($data['filter_content_model'])) ? " p2.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " p2.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    //incluindo filtro de referencia da grade...
                    $filtroGrade .= (isset($data['filter_content_model'])) ? " OR pg.referencia " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " OR pg.referencia LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroGrade .= " )";


                    $filtroPrincGrade .= " AND (";
                    $filtroPrincGrade .= (isset($data['filter_content_model'])) ? " p.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    //incluindo filtro de referencia da grade...
                    $filtroPrincGrade .= (isset($data['filter_content_model'])) ? " OR pg.referencia " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " OR pg.referencia LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroPrincGrade .= " )";
                    break;
                case Type_ListCodProduct::Tp_CodProdEcommerce:
                    $sqlFiltro .= (isset($data['filter_content_model'])) ? " AND CONVERT (p.product_id, char(11))" . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND CONVERT (p.product_id, char(11)) LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroGrade .= (isset($data['filter_content_model'])) ? " AND CONVERT (p2.product_id, char(11))" . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND CONVERT (p2.product_id, char(11)) LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                case Type_ListCodProduct::Tp_CodProdFuturaServer:
                    $sqlFiltro .= (isset($data['filter_content_model'])) ? " AND CONVERT (p.id_futura, char(11))" . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND CONVERT (p.id_futura, char(11)) LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroGrade .= (isset($data['filter_content_model'])) ? " AND CONVERT (p2.id_futura, char(11))" . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND CONVERT (p2.id_futura, char(11)) LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                case Type_ListCodProduct::Tp_CodBarras:
                    $sqlFiltro .= (isset($data['filter_content_model'])) ? " AND p.codigo_barra " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p.codigo_barra LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroGrade .= (isset($data['filter_content_model'])) ? " AND p2.codigo_barra " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p2.codigo_barra LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                default:
                    $sqlFiltro .= (isset($data['filter_content_model'])) ? " AND p.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroGrade .= (isset($data['filter_content_model'])) ? " AND p2.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p2.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
            }
        }

        if (!empty($data['filter_manufacturer'])) {
            $sqlFiltro .= " AND m.name LIKE '" . $this->db->escape($data['filter_manufacturer']) . "%'";
            $filtroGrade .= " AND m2.name LIKE '" . $this->db->escape($data['filter_manufacturer']) . "%'";
        }

        if (isset($data['filter_grade']) && !is_null($data['filter_grade']) && $data['filter_grade'] == 1 && $data['filter_grade'] <> '') {
            $sqlFiltro .= " AND p.tipo in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());
            $filtroGrade .= " AND p2.tipo in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());
        }

        if (isset($data['filter_grade']) && !is_null($data['filter_grade']) && $data['filter_grade'] == 0 && $data['filter_grade'] <> '') {
            $sqlFiltro .= " AND p.tipo = 0 ";
            $filtroGrade .= " AND p2.tipo = 0 ";
        }

        if (isset($data['filter_barcod']) && !is_null($data['filter_barcod']) && $data['filter_barcod'] == 1 && $data['filter_barcod'] <> '') {
            $sqlFiltro .= " AND p.codigo_barra is not null";
            $filtroGrade .= " AND p2.codigo_barra is not null";
        }

        if (isset($data['filter_barcod']) && !is_null($data['filter_barcod']) && $data['filter_barcod'] == 0 && $data['filter_barcod'] <> '') {
            $sqlFiltro .= " AND p.codigo_barra is null";
            $filtroGrade .= " AND p2.codigo_barra is null";
        }

        if (!empty($data['filter_color'])) {
            $sqlFiltro .= " AND pc.description LIKE '" . $this->db->escape($data['filter_color']) . "%'";
            $filtroGrade .= " AND pc2.description LIKE '" . $this->db->escape($data['filter_color']) . "%'";
        }

        if (!empty($data['filter_size'])) {
            $sqlFiltro .= " AND ps.description LIKE '" . $this->db->escape($data['filter_size']) . "%'";
            $filtroGrade .= " AND ps2.description LIKE '" . $this->db->escape($data['filter_size']) . "%'";
        }

        if (!empty($data['filter_price'])) {
            $sqlFiltro .= ftr_geral::getContentSqlValue(" AND CAST(p.price AS decimal(15,2)) ", $data['filter_price'], $data['filter_content_price']);
            $filtroGrade .= ftr_geral::getContentSqlValue(" AND CAST(p2.price AS decimal(15,2)) ", $data['filter_price'], $data['filter_content_price']);
        }

        if (isset($data['filter_quantity']) && !is_null($data['filter_quantity']) && $data['filter_quantity'] <> '') {
            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Maior) {
                $sqlFiltro .= " AND p.qty > '" . $this->db->escape($data['filter_quantity']) . "'";
                $filtroGrade .= " AND p2.qty > '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_MaiorIgual) {
                $sqlFiltro .= " AND p.qty >= '" . $this->db->escape($data['filter_quantity']) . "'";
                $filtroGrade .= " AND p2.qty >= '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Igual) {
                $sqlFiltro .= " AND p.qty  = '" . $this->db->escape($data['filter_quantity']) . "'";
                $filtroGrade .= " AND p2.qty = '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_MenorIgual) {
                $sqlFiltro .= " AND p.qty <= '" . $this->db->escape($data['filter_quantity']) . "'";
                $filtroGrade .= " AND p2.qty <= '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Menor) {
                $sqlFiltro .= " AND p.qty < '" . $this->db->escape($data['filter_quantity']) . "'";
                $filtroGrade .= " AND p2.qty < '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Diferente) {
                $sqlFiltro .= " AND p.qty <> '" . $this->db->escape($data['filter_quantity']) . "'";
                $filtroGrade .= " AND p2.qty <> '" . $this->db->escape($data['filter_quantity']) . "'";
            }
        }

        if (isset($data['filter_stock']) && !is_null($data['filter_stock']) && $data['filter_stock'] == 1 && $data['filter_stock'] <> '') {
            $sqlFiltro .= " AND p.qty > 0";
            $filtroGrade .= " AND p2.qty > 0";
        }

        if (isset($data['filter_stock']) && !is_null($data['filter_stock']) && $data['filter_stock'] == 0 && $data['filter_stock'] <> '') {
            $sqlFiltro .= " AND p.qty <= 0";
            $filtroGrade .= " AND p2.qty <= 0";
        }

        $mktSqlFiltro = '';
        $mktUnionGrade = '';
        $mktFiltroGrade = '';
        $typeFilterMkt = Type_FilterMkt::getEnumList();

        $datamkt = array_filter($data, function($array){
            return (!is_null($array) && ($array <> ''));
        });

        $filtermkt = array_filter(array_map(function($array) use ($typeFilterMkt){
            return (array_key_exists($array, $typeFilterMkt));
        }, array_keys($datamkt)));

        if(isset($data['type_marketplace']) && in_array($data['type_marketplace'], array_keys(Type_MarketPlace::getEnumList())) && $filtermkt ){

            if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace']) && $data['filter_marketplace'] == 0 && $data['filter_marketplace'] <> '') {
                $exist = ' NOT EXISTS';
            }else{
                $exist = ' EXISTS';
            }

            $mktSqlFiltro.=" AND $exist (SELECT mp.product_key_id FROM oc_marketplace_product mp WHERE mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp.tipo_integracao = ".$data['type_marketplace']." AND mp.product_key_id = p.product_id " ;

            $mktUnionGrade.=" AND $exist (SELECT mp.product_key_id FROM oc_marketplace_product mp WHERE ((mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp.tipo_integracao = ".$data['type_marketplace']." AND mp.product_key_id = p.product_id)
            OR (mp.product_key_id = pg.product_grid_id and mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Grade." and mp.tipo_integracao = ".$data['type_marketplace'].")) " ;

            $mktFiltroGrade.=" AND $exist (SELECT mp2.product_key_id FROM oc_marketplace_product mp2 WHERE ((mp2.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp2.tipo_integracao = ".$data['type_marketplace']." AND mp2.product_key_id = p2.product_id)
            OR (mp2.product_key_id = pgd2.product_grid_id and mp2.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Grade." and mp2.tipo_integracao = ".$data['type_marketplace'].")) " ;

            if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace']) && $data['filter_marketplace'] == 0 && $data['filter_marketplace'] <> '') {
                $mktSqlFiltro .= " and (mp.id  is not  null)";
                $mktFiltroGrade .= " and (mp2.id is not null)";
            }else{

                if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace']) && $data['filter_marketplace'] == 1 && $data['filter_marketplace'] <> '') {
                    $mktSqlFiltro .= " and (mp.id is not null)";
                    $mktFiltroGrade .= " and (mp2.id is not null)";
                }

                if (isset($data['filter_marketId']) && !is_null($data['filter_marketId'])) {
                    $mktSqlFiltro .= " and (mp.marketId = '" . $data['filter_marketId'] . "')";
                    $mktFiltroGrade .= " and (mp2.marketId = '" . $data['filter_marketId'] . "')";
                }

                if (!empty($data['filter_ml_status'])) {
                    $mktSqlFiltro .= " and (mp.status = '" . $this->db->escape($data['filter_ml_status']) . "')";
                    $mktFiltroGrade .= " and (mp2.status = '" . $this->db->escape($data['filter_ml_status']) . "')";
                }

            }

            $mktSqlFiltro.= " ) ";

            $mktUnionGrade.= " ) ";

            $mktFiltroGrade.= " ) ";

        }

        if (isset($data['filter_status']) && !is_null($data['filter_status']) && $data['filter_status'] <> '') {
            $sqlFiltro .= " AND p.status = '" . (int)$data['filter_status'] . "'";
            $filtroGrade .= " AND p2.status = '" . (int)$data['filter_status'] . "'";
        }

        $sqlGrid = "union
                 SELECT
                     (true) as IsGrid,
                     (false) as IsKit,
                     (pg.referencia) as model_principal,
                     pg.product_grid_id as product_grid_id,
                     pgdetail.product_grid_id as product_key_id,
                     pd.name,
                     p.*,
                     (pc.description) as color,
                     (ps.description) as size,
                     (psf.description) as specification,
                     (
                       select sum(qty) from oc_product_grid_detail pgds
                       inner join oc_product ps on (ps.product_id = pgds.product_id)
                       where pgds.product_grid_id =  pg.product_grid_id and ps.qty >= 0
                     ) total_quantity,
                     (
                       select sum(qty_mkt) from oc_product_grid_detail pgds
                       inner join oc_product ps on (ps.product_id = pgds.product_id)
                       where pgds.product_grid_id =  pg.product_grid_id and ps.qty_mkt >= 0
                     ) total_quantity_mkt,
                     (m.name) as manufacturer_name
                 FROM oc_product_grid pg
                 inner join oc_product_grid_detail pgdetail on (pgdetail.product_grid_id = pg.product_grid_id)
                 inner join oc_product p on (pgdetail.product_id = p.product_id)
                 LEFT JOIN oc_product_description pd ON (pd.product_id = p.product_id)
                 LEFT JOIN oc_manufacturer m ON (m.manufacturer_id = p.manufacturer_id)
                 LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id)
                 LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id)
                 LEFT JOIN oc_product_specification psf ON (psf.productspecification_id = p.product_specification_id)";

        if (isset($data['filter_kit'])&& $data['filter_kit']== Type_DsimDnao::Tp_Sim ){
            $sqlGrid .=  " INNER JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        }else{
            $sqlGrid .=  " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        }

        if (!empty($data['filter_color'])) {
            $sqlGrid .= "LEFT JOIN oc_product_color pc2 ON (pc.productcolor_id = p.product_color_id)";
        }

        if (!empty($data['filter_size'])) {
            $sqlGrid .= "LEFT JOIN oc_product_size ps2 ON (ps.productsize_id = p.product_size_id)";
        }

        $FilterId = "";
        if (!empty($data['ids_produto'])) {
            $FilterId = " AND (p.product_id  in (" . implode(',', $data['ids_produto']) . "))";

            if (empty($data['ids_grade'])) {
                $sqlGridEmpty = " AND (pg.product_grid_id is null)";
            }
        }

        $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_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_manufacturer'])) {
            $sqlFiltrograde .= " LEFT JOIN oc_manufacturer m2 ON (m2.manufacturer_id = p2.manufacturer_id)";
        }

        if (!empty($data['filter_category_id'])) {
            $sqlFiltrograde .= $this->getCategoryFilter($data['filter_category_id']);
        }


        if (!empty($data['ids_grade'])) {
            $filtroGrade .= " AND (pgd2.product_grid_id  in (" . implode(',', $data['ids_grade']) . "))";
        }

        $sqlFiltrograde .= " $filtroGrade $mktFiltroGrade and  (pgd2.product_grid_id = pg.product_grid_id)
                              order by p2.status desc, p2.qty desc
                              limit 1
                              ) = p.product_id ";//Ordenar sempre pelo status ativo primeiro, se todos estiverem inativos, ai sim mostra inativo....

        $sqlFiltrograde .= $sqlGridEmpty;

        if (!empty($data['ids_grade'])) {
            $sqlFiltrograde .= " AND (pg.product_grid_id  in (" . implode(',', $data['ids_grade']) . "))";

            if ((empty($data['ids_produto'])) && (empty($FilterId))) {
                $FilterId .= " AND (p.product_id  is null)";
            }
        }

        $sqlGroupBy = " GROUP BY p.product_id";

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

        $sqlOrderBy = "";
      $data['sort'] = isset($data['sort']) ?  $data['sort'] : false;

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            $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:
                    $sqlOrderBy .= " ORDER BY model_principal ";
                    break;
                case Type_ListCodProduct::Tp_CodProdEcommerce:
                    $sqlOrderBy .= " ORDER BY product_id ";
                    break;
                case Type_ListCodProduct::Tp_CodProdFuturaServer:
                    $sqlOrderBy .= " ORDER BY id_futura ";
                    break;
                case Type_ListCodProduct::Tp_CodBarras:
                    $sqlOrderBy .= " ORDER BY codigo_barra ";
                    break;
                default:
                    $sqlOrderBy .= " ORDER BY " . $data['sort'];
            }

        } elseif ($this->config->get('config_produto_ordenacao_padrao') == Type_OrdemProdutos::Tp_CadastroDec || $data['sort'] == 'data_replicacao_ecommerce') {
            $sqlOrderBy .= " ORDER BY data_replicacao_ecommerce";
        } else {
            $sqlOrderBy .= " ORDER BY name";
        }

        if ((isset($data['order']) && ($data['order'] == 'DESC')) || ($this->config->get('config_produto_ordenacao_padrao') == Type_OrdemProdutos::Tp_CadastroDec)) {
            $sqlOrderBy .= " DESC";
        } else {
            $sqlOrderBy .= " ASC";
        }

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

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

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

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

        //if($this->config->get('futuragrade_status')){
        //no admin sempre trabalhar apenas com agrupamento de grade por causa da grade de marketplaces...
        $sql .=
            " " . $sqlFiltro . " $mktSqlFiltro and (p.tipo not in " . $sqlIn . ") $FilterId " . " " . $sqlFiltroProdPrinc . " " . $sqlGroupBy
            . " " . $sqlGrid
            . " " . $sqlFiltro . " $mktUnionGrade and p.tipo in " . $sqlIn
            . " and " . $sqlFiltrograde . " " . $filtroPrincGrade
            . " " . $sqlOrderBy
            . " " . $sqlLimit;
        //}else{
        //    $sql .= $sqlFiltro . ' ' . $sqlOrderBy . ' ' . $sqlLimit;
        //}



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

    public function getProduct($product_id, $marketplace = null)
    {
        $this->load->helper('ftr_validacao');
        $product_id = (int)$product_id;
        $defaultLanguage = (int)$this->config->get('config_language_id');

        $sql = "SELECT DISTINCT
            p.*,
            pd.*,
            (m.name) AS manufacturer_name,
            pkit.product_kit_id AS product_kit_id,
            (SELECT keyword FROM oc_url_alias WHERE query = 'product_id=$product_id') AS keyword,
            (pc.description)  cor,
            (ps.description)  tamanho,
            (psp.description) especificacao";

        if (ftr_validacao::validaTipo($marketplace, Type_MarketPlace::Tp_Mercadolivre)) {
            $sql .= ",mli.*,
            (mp.marketId) AS product_mercaId,
            (mp.sold_quantity) AS sold_quantity,
            (mp.status) as meli_status";
        }

        $sql .= " FROM " . DB_PREFIX . "product p
            LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)
            LEFT JOIN " . DB_PREFIX . "manufacturer m ON (m.manufacturer_id = p.manufacturer_id)
            LEFT JOIN " . DB_PREFIX . "product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'
            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 " . DB_PREFIX . "product_specification psp ON (psp.productspecification_id = p.product_specification_id)";

        if (ftr_validacao::validaTipo($marketplace, Type_MarketPlace::Tp_Mercadolivre)) {
            $sql .= " LEFT JOIN oc_marketplace_product mp ON (mp.product_key_id = p.product_id) and mp.tipo_integracao = " . $marketplace .
                " LEFT JOIN oc_mercadolivre_infos mli ON (mli.product_key_id = p.product_id)";
        } elseif (ftr_validacao::validaTipo($marketplace, Type_MarketPlace::Tp_SkyHub)) {
            $sql .= " LEFT JOIN oc_marketplace_product mp ON (mp.product_key_id = p.product_id) and mp.tipo_integracao = " . $marketplace;
        }

        $sql .= " WHERE p.product_id = '$product_id' AND pd.language_id = '$defaultLanguage'";


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

        return $query->row;
    }


    public function getProductDescriptions($product_id)
    {
        $product_description_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

        foreach ($query->rows as $result) {
            $product_description_data[$result['language_id']] = array(
                'name' => $result['name'],
                'description' => $result['description'],
                'meta_keyword' => $result['meta_keyword'],
                'meta_description' => $result['meta_description'],
                'tag' => $result['tag'],
                'description_complete_s_format' => $result['description_complete_s_format']
            );
        }

        return $product_description_data;
    }

    public function getProductTranslations($product_id)
    {
        $product_translation_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_translate WHERE product_id = '" . (int)$product_id . "'");

        foreach ($query->rows as $result) {
            $product_translation_data[$result['linguagem']] = array(
                'descricao' => $result['descricao'],
                'descricao_ecommerce' => $result['descricao_ecommerce'],
                'descricao_completa' => $result['descricao_completa'],
                'descricao_completa_sf' => $result['descricao_completa_sf'],
                'linguagem' => Type_DataLanguage::getNickFromConstValue($result['linguagem'])
            );
        }

        return $product_translation_data;
    }

    public function getProductCategories($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)){
            $product_category_data = array($result['sub_category_id'],$result['category_id']);
        }

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

        return $product_category_data;
    }

    public function getProductFilters($product_id)
    {
        $product_filter_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_filter WHERE product_id = '" . (int)$product_id . "'");

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

        return $product_filter_data;
    }

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

        $product_attribute_query = $this->db->query("SELECT attribute_id FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' GROUP BY attribute_id");

        foreach ($product_attribute_query->rows as $product_attribute) {
            $product_attribute_description_data = array();

            $product_attribute_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");

            foreach ($product_attribute_description_query->rows as $product_attribute_description) {
                $product_attribute_description_data[$product_attribute_description['language_id']] = array('text' => $product_attribute_description['text']);
            }

            $product_attribute_data[] = array(
                'attribute_id' => $product_attribute['attribute_id'],
                'product_attribute_description' => $product_attribute_description_data
            );
        }

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

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

            $product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "'");

            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'],
                    'quantity' => $product_option_value['quantity'],
                    'subtract' => $product_option_value['subtract'],
                    'price' => $product_option_value['price'],
                    'price_prefix' => $product_option_value['price_prefix'],
                    'points' => $product_option_value['points'],
                    'points_prefix' => $product_option_value['points_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'],
                'product_option_value' => $product_option_value_data,
                'option_value' => $product_option['option_value'],
                'required' => $product_option['required']
            );
        }

        return $product_option_data;
    }

    public function getProductDiscounts($product_id)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' ORDER BY quantity, priority, price");

        return $query->rows;
    }

    public function getProductSpecials($product_id)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' ORDER BY priority, price");

        return $query->rows;
    }

    public function getorderDiscountPerc($product_id)
    {
        $this->load->type('tipocomum');

        $sql = "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";


        $query = $this->db->query($sql);
        if (!$query->rows) {
            return false;
        } else {
            return $query->rows[0]['perc_discount'];
        }


    }

    public function getProductRewards($product_id)
    {
        $product_reward_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_reward WHERE product_id = '" . (int)$product_id . "'");

        foreach ($query->rows as $result) {
            $product_reward_data[$result['customer_group_id']] = array('points' => $result['points']);
        }

        return $product_reward_data;
    }

    public function getProductStores($product_id)
    {
        $product_store_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id . "'");

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

        return $product_store_data;
    }

    public function getProductLayouts($product_id)
    {
        $product_layout_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");

        foreach ($query->rows as $result) {
            $product_layout_data[$result['store_id']] = $result['layout_id'];
        }

        return $product_layout_data;
    }

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

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");

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

        return $product_related_data;
    }

    public function getProfiles($product_id)
    {
        return $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_profile` WHERE product_id = " . (int)$product_id)->rows;
    }

    private function getCategoryFilter($filter_category_id){
        $sqlCategory = '';

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

        if (!empty($filter_category_id)) {
            /* Montando o join de acordo com o tipo */
            $categoryType = $this->db->query("select tipo_grupo_subgrupo from oc_category where category_id =".$filter_category_id)->row['tipo_grupo_subgrupo'];

            switch ($categoryType) {
                case Type_NivelCategoria::Tp_Grupo :
                    $sqlCategory = " INNER JOIN oc_category p2c ON (p.category_id = p2c.category_id)
                                     INNER JOIN oc_category cp ON p2c.parent_id = cp.category_id AND p2c.parent_id = ".$filter_category_id;
                break;
                case Type_NivelCategoria::Tp_SubGrupo :
                    $sqlCategory = " INNER JOIN oc_category p2c ON (p.category_id = p2c.category_id) and p2c.category_id = ".$filter_category_id;
                break;
                case Type_NivelCategoria::Tp_Departamento :
                    $sqlCategory = "INNER JOIN oc_category p2c ON (p.category_id = p2c.category_id)
                                    INNER JOIN oc_category cp ON p2c.parent_id = cp.category_id
                                    INNER JOIN oc_category dp ON cp.parent_id = dp.category_id AND cp.parent_id =" .$filter_category_id;
                break;
            }
            return $sqlCategory;

        }
    }

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

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



        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p
                LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)";

        if (!empty($data['filter_category_id'])) {
            $sql .= $this->getCategoryFilter($data['filter_category_id']);
        }

        if (!empty($data['filter_manufacturer'])) {
            $sql .= " LEFT JOIN oc_manufacturer m ON (m.manufacturer_id = p.manufacturer_id)";
        }

        if (!empty($data['filter_color'])) {
            $sql .= " LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id)";
        }

        if (!empty($data['filter_size'])) {
            $sql .= " LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id)";
        }

        if (isset($data['filter_kit'])&& $data['filter_kit']== Type_DsimDnao::Tp_Sim ){
            $sql .=  " INNER JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        }

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

        if (isset($data['filter_kit']) && $data['filter_kit'] == Type_DsimDnao::Tp_Nao){
            $sql .=  " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
            $filtro .= " and pkit.product_kit_id is null ";
        }

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

            $filtro .= " AND pd.name " . ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content_name']);

        }
        $filtroAddGrade = "";

        if (!empty($data['filter_model'])) {
            $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:
                    $filtro .= (isset($data['filter_content_model'])) ? " AND p.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    $filtroAddGrade = (isset($data['filter_content_model'])) ? " AND pg.referencia " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND pg.referencia LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                case Type_ListCodProduct::Tp_CodProdEcommerce:
                    $filtro .= (isset($data['filter_content_model'])) ? " AND CONVERT (p.product_id, char(11))" . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND CONVERT (p.product_id, char(11)) LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                case Type_ListCodProduct::Tp_CodProdFuturaServer:
                    $filtro .= (isset($data['filter_content_model'])) ? " AND CONVERT (p.id_futura, char(11))" . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND CONVERT (p.id_futura, char(11)) LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                case Type_ListCodProduct::Tp_CodBarras:
                    $filtro .= (isset($data['filter_content_model'])) ? " AND p.codigo_barra " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p.codigo_barra LIKE '" . $this->db->escape($data['filter_model']) . "%'";
                    break;
                default:
                    $filtro .= (isset($data['filter_content_model'])) ? " AND p.model " . ftr_geral::getContentSql($this->db->escape($data['filter_model']), $data['filter_content_model']) : " AND p.model LIKE '" . $this->db->escape($data['filter_model']) . "%'";
            }
        }


        if (!empty($data['filter_manufacturer'])) {
            $filtro .= " AND m.name LIKE '" . $this->db->escape($data['filter_manufacturer']) . "%'";
        }

        if (isset($data['filter_grade']) && !is_null($data['filter_grade']) && $data['filter_grade'] == 1 && $data['filter_grade'] <> '') {
            $filtro .= " AND p.tipo in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());
        }

        if (isset($data['filter_grade']) && !is_null($data['filter_grade']) && $data['filter_grade'] == 0 && $data['filter_grade'] <> '') {
            $filtro .= " AND p.tipo = 0 ";
        }

        if (isset($data['filter_barcod']) && !is_null($data['filter_barcod']) && $data['filter_barcod'] == 1 && $data['filter_barcod'] <> '') {
            $filtro .= " AND p.codigo_barra is not null";
        }

        if (isset($data['filter_barcod']) && !is_null($data['filter_barcod']) && $data['filter_barcod'] == 0 && $data['filter_barcod'] <> '') {
            $filtro .= " AND p.codigo_barra is null";
        }

        if (!empty($data['filter_color'])) {
            $filtro .= " AND pc.description LIKE '" . $this->db->escape($data['filter_color']) . "%'";
        }

        if (!empty($data['filter_size'])) {
            $filtro .= " AND ps.description LIKE '" . $this->db->escape($data['filter_size']) . "%'";
        }

        if (!empty($data['filter_price'])) {
            $filtro .= ftr_geral::getContentSqlValue(" AND p.price ", $data['filter_price'], $data['filter_content_price']);
        }

        if (isset($data['filter_quantity']) && !is_null($data['filter_quantity']) && $data['filter_quantity'] <> '') {

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Maior) {
                $filtro .= " AND p.qty > '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_MaiorIgual) {
                $filtro .= " AND p.qty >= '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Igual) {
                $filtro .= " AND p.qty = '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_MenorIgual) {
                $filtro .= " AND p.qty <= '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Menor) {
                $filtro .= " AND p.qty < '" . $this->db->escape($data['filter_quantity']) . "'";
            }

            if ($data['filter_content_qtd'] == Type_FiltroValor::Tp_Diferente) {
                $filtro .= " AND p.qty <> '" . $this->db->escape($data['filter_quantity']) . "'";
            }

        }

        if (isset($data['filter_stock']) && !is_null($data['filter_stock']) && $data['filter_stock'] == 1 && $data['filter_stock'] <> '') {
            $filtro .= " AND p.qty > 0";
        }

        if (isset($data['filter_stock']) && !is_null($data['filter_stock']) && $data['filter_stock'] == 0 && $data['filter_stock'] <> '') {
            $filtro .= " AND p.qty <= 0";
        }


        $filtroSql = '';
        $sqlmarketplace = '';
        $typeFilterMkt = Type_FilterMkt::getEnumList();

        $datamkt = array_filter($data, function($array){
            return (!is_null($array) && ($array <> ''));
        });

        $filtermkt = array_filter(array_map(function($array) use ($typeFilterMkt){
            return (array_key_exists($array, $typeFilterMkt));
        }, array_keys($datamkt)));


        if(isset($data['type_marketplace']) && in_array($data['type_marketplace'], array_keys(Type_MarketPlace::getEnumList())) && $filtermkt ){

            if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace']) && $data['filter_marketplace'] == 0 && $data['filter_marketplace'] <> '') {
                $exist = ' NOT EXISTS';
            }else{
                $exist = ' EXISTS';
            }

            $filtroSql.=" AND $exist (SELECT mp.product_key_id FROM oc_marketplace_product mp WHERE ((mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp.tipo_integracao = ".$data['type_marketplace']." AND mp.product_key_id = p.product_id)
            OR (mp.product_key_id = pg.product_grid_id and mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Grade." and mp.tipo_integracao = ".$data['type_marketplace'].")) " ;

            $sqlmarketplace.=" AND $exist (SELECT mp.product_key_id FROM oc_marketplace_product mp WHERE ((mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp.tipo_integracao = ".$data['type_marketplace']." AND mp.product_key_id = p.product_id)
            OR (mp.product_key_id = pgd.product_grid_id and mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Grade." and mp.tipo_integracao = ".$data['type_marketplace'].")) " ;

            if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace']) && $data['filter_marketplace'] == 0 && $data['filter_marketplace'] <> '') {
                $filtroSql .= " and (mp.id  is not null)";
                $sqlmarketplace .= " and (mp.id  is not  null)";
            }else{

                if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace']) && $data['filter_marketplace'] == 1 && $data['filter_marketplace'] <> '') {
                    $filtroSql .= " and (mp.id is not null)";
                    $sqlmarketplace .= " and (mp.id is not null)";
                }

                if (isset($data['filter_marketId']) && !is_null($data['filter_marketId'])) {
                    $filtroSql .= " and (mp.marketId = '" . $data['filter_marketId'] . "')";
                    $sqlmarketplace .= " and (mp.marketId = '" . $data['filter_marketId'] . "')";
                }

                if (!empty($data['filter_ml_status'])) {
                    $filtroSql .= " and (mp.status = '" . $this->db->escape($data['filter_ml_status']) . "')";
                    $sqlmarketplace .= " and (mp.status = '" . $this->db->escape($data['filter_ml_status']) . "')";
                }

            }

            $filtroSql.= " ) ";

            $sqlmarketplace.= " ) ";

        }


        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $filtro .= " AND p.status = '" . (int)$data['filter_status'] . "'";
        }

        if (!empty($data['filter_data_replicacao_ecommerce'])) {
            $filtro .= " AND DATE(p.data_replicacao_ecommerce) BETWEEN MAKEDATE(YEAR(current_timestamp), 1) AND DATE(NOW()) ";
        }

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

        //if($this->config->get('futuragrade_status')){
        $sqlAux = " left join
                     	    (select
                   	        (pgd.product_id)product_id,
                  	        (pgd.product_grid_id)product_grid_id
                        from oc_product_grid_detail pgd
                        inner join oc_product p on (p.product_id = pgd.product_id)
                        inner join oc_product_grid pg on (pgd.product_grid_id = pg.product_grid_id)
                        inner join oc_product_description pd on (pd.product_id = p.product_id)";

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

            $sqlAux .= $this->getCategoryFilter($data['filter_category_id']);
        }

        if (!empty($data['filter_manufacturer'])) {
            $sqlAux .= " LEFT JOIN oc_manufacturer m ON (m.manufacturer_id = p.manufacturer_id)";
        }

        if (!empty($data['filter_color'])) {
            $sqlAux .= " LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id)";
        }

        if (!empty($data['filter_size'])) {
            $sqlAux .= " LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id)";
        }

        if (isset($data['filter_kit'])&& $data['filter_kit']== Type_DsimDnao::Tp_Sim ){
            $sqlAux .=  " INNER JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        } elseif (isset($data['filter_kit']) && $data['filter_kit'] == Type_DsimDnao::Tp_Nao){
            $sqlAux .=  " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'";
        }

        $sqlAux .= $filtro . " " .$filtroSql . $filtroAddGrade .
            "    group by pgd.product_grid_id"
            . ") as pgd on (pgd.product_id = p.product_id)"
            . " " . $sqlmarketplace . " "
            . $filtro . " " .$sqlmarketplace . $filtroItem;
        $sql .= $sqlAux;
        //}


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

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

    public function getTotalProductsByStockStatusId($stock_status_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE stock_status_id = '" . (int)$stock_status_id . "'");

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

    public function getTotalProductsByWeightClassId($weight_class_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE weight_class_id = '" . (int)$weight_class_id . "'");

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

    public function getTotalProductsByLengthClassId($length_class_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE length_class_id = '" . (int)$length_class_id . "'");

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

    public function getTotalProductsByManufacturerId($manufacturer_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE manufacturer_id = '" . (int)$manufacturer_id . "'");

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

    public function getTotalProductsByAttributeId($attribute_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_attribute WHERE attribute_id = '" . (int)$attribute_id . "'");

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

    public function getTotalProductsByOptionId($option_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_option WHERE option_id = '" . (int)$option_id . "'");

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

    public function getTotalProductsByLayoutId($layout_id)
    {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_layout WHERE layout_id = '" . (int)$layout_id . "'");

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

    public function getProductMercadolivre($product_id)
    {
        $query = $this->db->query("SELECT * FROM `oc_marketplace_product` WHERE product_id = " . (int)$product_id);

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


    public function getProductName($product_id)
    {

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

        return $query->row['name'];

    }

    public function getProductMercadolivreGrade($product_grid_id)
    {

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

        $sql = "select "
            . "(select distinct(pd.name) from oc_product_grid_detail pgd inner join oc_product_description pd on (pgd.product_id = pd.product_id) where pgd.product_grid_id = " . $product_grid_id . ") as name, "
            . "mp.* "
            . "from oc_product_grid_detail pgd2 "
            . "inner join oc_marketplace_product mp on(mp.product_key_id = pgd2.product_id) "
            . "where pgd2.product_grid_id = " . $product_grid_id;

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

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

    }

    public function getProductGridVariation($product_grid_id){
        $sql = "select pg.product_grid_id,
              pg.description,
              if(pgt.product_grid_template_id > 0, if(pgt.cor_utiliza = 'S', pgt.descricao_cor, NULL), 'COR') as cor,
              if(pgt.product_grid_template_id > 0, if(pgt.tamanho_utiliza = 'S', pgt.descricao_tamanho, NULL), 'TAMANHO') as tamanho,
              if(pgt.product_grid_template_id > 0, if(pgt.especificacao_utiliza = 'S', pgt.descricao_especificacao, NULL), 'ESPECIFICACAO') as especificacao,
              count(distinct pgd.product_color_id) cor_qtd,
              count(distinct pgd.product_size_id) tamanho_qtd,
              count(distinct pgd.product_specification_id) especificacao_qtd
            from oc_product_grid pg
            left join oc_product_grid_template pgt on pg.product_grid_template_id = pgt.product_grid_template_id
            inner join oc_product_grid_detail pgd on pg.product_grid_id = pgd.product_grid_id
            inner join oc_product p on p.product_id = pgd.product_id AND p.status = " . Type_Status::Tp_Habilitado . "
            where pg.product_grid_id = " . $product_grid_id . "
            group by 1,2,3,4,5";

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

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

    public function getProductGridTemplate($product_grid_id){
        $sql = "select *
            from oc_product_grid pg
            left join oc_product_grid_template pgt on pg.product_grid_template_id = pgt.product_grid_template_id
            where pg.product_grid_id = " . $product_grid_id;

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

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

}
