<?php

final class DModelcatalogproductGrid extends Model {

    public function getProductImagesFromGridId($product_grid_id) {
        $product_grid_id = (int)$product_grid_id;
        $query = $this->db->query("SELECT pi.*
                                   FROM oc_product_image pi
                                   inner join oc_product_grid_detail pgd on (pi.product_id = pgd.product_id)
                                   where pgd.product_grid_id = $product_grid_id
                                   ORDER BY pi.sort_order, pi.id_futura, pi.product_image_id
        ");

        return $query->rows;
    }

    public function getGridDataFromProduto($product_id){
        $sql = "
            select
              pg.description,
              pg.product_grid_id
            from oc_product_grid pg
            inner join oc_product_grid_detail pgd on (pgd.product_grid_id = pg.product_grid_id)
            where pgd.product_id = $product_id
        ";
        $query = $this->db->query($sql);

        return $query->row;
    }

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

        $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 DISTINCT
            p.*,
            pd.*,
            pg.product_grid_id as product_grid_id,
            pc.description AS cor,
            psp.description AS especificacao,
            ps.description AS tamanho,
            (COALESCE(pgt.descricao_cor,'$title_cor')) color_template_description,
            (COALESCE(pgt.descricao_tamanho,'$title_size')) size_template_description,
            (COALESCE(pgt.descricao_especificacao,'$title_specification')) specification_template_description,
            (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";

        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 oc_product p
                INNER JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_id
                INNER JOIN oc_product_grid pg ON pg.product_grid_id = pgd.product_grid_id
                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 = pgd.product_color_id)
                LEFT JOIN oc_product_specification psp ON (psp.productspecification_id = pgd.product_specification_id)
                LEFT JOIN oc_product_size ps ON (ps.productsize_id = pgd.product_size_id)
                LEFT JOIN oc_product_grid_template pgt ON pg.product_grid_template_id = pgt.product_grid_template_id
                LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S'
                ";

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

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

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

    public function isProductGrid($product_id){
        $sqlIn = ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());
        $sql = "select product_id from oc_product
                where tipo in $sqlIn and product_id = $product_id";
        $query = $this->db->query($sql);

        return (Count($query->rows) > 0);
    }

  public function getGrid($product_grid_id){

    $product_grid_id = (int)$product_grid_id;
    $query = $this->db->query("SELECT * FROM oc_product_grid pg WHERE pg.product_grid_id = $product_grid_id");

    return $query->row;

  }

    public function getGrids($data){

      $sql = "
            select
              pg.description,
              pg.product_grid_id
            from oc_product_grid pg
            where pg.product_grid_id > 0 ";

      if(!empty($data['filter_name'])){
        $sql .= " AND ( pg.description ".ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content_name']);
        $sql .= " OR pg.referencia ".ftr_geral::getContentSql($this->db->escape($data['filter_name']), $data['filter_content_name']).") ";
      }

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

      return $query->rows;

    }

  public function getGridTemplateList(){
    $this->load->helper('ftr_geral');
    $query = $this->db->query( "SELECT distinct
      pgt.product_grid_template_id AS template_id,
      pgt.descricao_cor AS template_cor,
      pgt.descricao_tamanho AS template_tamanho,
      pgt.descricao_especificacao AS template_especificacao,
      pc.description as descricao_cor,
      ps.description as descricao_tamanho,
      psp.description as descricao_especificacao

      FROM oc_product_grid_template pgt
      LEFT JOIN oc_product_grid_templ_color ptc ON ptc.product_grid_template_id = pgt.product_grid_template_id
      LEFT JOIN oc_product_color pc ON pc.productcolor_id = ptc.productcolor_id

      LEFT JOIN oc_product_grid_templ_size pts ON ptc.product_grid_template_id = pgt.product_grid_template_id
      LEFT JOIN oc_product_size ps ON ps.productsize_id = pts.productsize_id

      LEFT JOIN oc_product_grid_templ_spec ptsp ON ptsp.product_grid_template_id = pgt.product_grid_template_id
      LEFT JOIN oc_product_specification psp ON psp.productspecification_id = ptsp.productspecification_id

      ORDER BY template_id, pgt.descricao_cor , pgt.descricao_tamanho, pgt.descricao_especificacao, pc.description, ps.description, psp.description");
    $list_attribute = array();

    //Ajustado os atributos dessa maneira, para não fazer 3 consultas na base...
    foreach($query->rows as $row){
      $list_attribute['C'.$row['template_id']]['name']= 'C'.$row['template_id'];
      $list_attribute['C'.$row['template_id']]['label']=$row['template_cor'];
      $list_attribute['C'.$row['template_id']]['options'][]=$row['descricao_cor'];

      $list_attribute['S'.$row['template_id']]['name']= 'S'.$row['template_id'];
      $list_attribute['S'.$row['template_id']]['label']=$row['template_tamanho'];
      $list_attribute['S'.$row['template_id']]['options'][]=$row['descricao_tamanho'];

      $list_attribute['SP'.$row['template_id']]['name']= 'SP'.$row['template_id'];
      $list_attribute['SP'.$row['template_id']]['label']=$row['template_especificacao'];
      $list_attribute['SP'.$row['template_id']]['options'][]=$row['descricao_especificacao'];
    }
    foreach($list_attribute as $key => $att){
      $list_attribute[$key]['options'] = array_values(array_unique($att['options']));
    }

    return $list_attribute;
  }


    public function getProductsGrid($product_grid_id, $data = array()){
        $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');

        $filtro = '';
        $sql = "
            select
                pc.description as color_description,
                ps.description as size_description,
                psp.description as specification_description,
                (COALESCE(pgt.descricao_cor,'$title_cor')) color_template_description,
                (COALESCE(pgt.descricao_tamanho,'$title_size')) size_template_description,
                (COALESCE(pgt.descricao_especificacao,'$title_specification')) specification_template_description,
                pgt.product_grid_template_id as grid_template_id,
                pgd.product_id,
                p.codigo_barra,
                p.image,
                p.qty_mkt,
                p.qty,
                p.price,
                p.status,
                mpc.product_catalog_id,
                pd.name";

        if(!empty($data['mkt_table_price'])){
            $sql .= ", COALESCE(pdis.price, p.price) AS table_price ";
        }

        if (isset($data['filter_ml_catalog_ad'])&&!empty($data['filter_ml_catalog_ad'])){
            $sql .= ", mac.catalog_ad";
        }

        $sql.=" from oc_product_grid_detail pgd
            left join oc_product_color         pc on (pc.productcolor_id = pgd.product_color_id)
            left join oc_product_size          ps on (ps.productsize_id  = pgd.product_size_id)
            left join oc_product               p  on (pgd.product_id     = p.product_id)
            left join oc_product_description   pd  on (pd.product_id     = p.product_id)
            left join oc_product_specification psp on (psp.productspecification_id = pgd.product_specification_id)
            LEFT JOIN oc_product_grid          pg ON pg.product_grid_id = pgd.product_grid_id
            LEFT JOIN oc_product_grid_template pgt ON pg.product_grid_template_id = pgt.product_grid_template_id
            left join oc_mercadolivre_product_catalog   mpc  on (mpc.product_id     = p.product_id)";

        if(!empty($data['mkt_table_price'])){
            $sql .= " LEFT JOIN oc_product_discount pdis ON (pdis.product_id = p.product_id) AND pdis.customer_group_id = ".$data['mkt_table_price'];
        }

        if (isset($data['filter_ml_catalog_ad'])&&!empty($data['filter_ml_catalog_ad'])){
          $sql .= " LEFT JOIN oc_mercadolivre_ad_catalog mac ON (mac.marketplace_product_id = " . $data['filter_ml_catalog_ad'] . ")" .
                  " AND mac.mercadolivre_product_catalog_id = mpc.id";
        }

        $sql .=" where pgd.product_grid_id = $product_grid_id";

        if (isset($data['filter_ativos'])&&!empty($data['filter_ativos'])){
            $filtro .= ' and p.status = '.$data['filter_ativos'];
        }

        $sql = $sql.$filtro;

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

        return $query->rows;
    }


}