<?php
class ModelMercadolivreError extends Model{

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

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

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

        $sql =
            "SELECT 
                (false) as IsGrid,
                (null)  as product_grid_id,
                p.product_id as product_id,
                p.product_id as product_key_id,
                pd.name, 
                p.*,  
                (mp.marketId) AS product_mercaId,
                mp.cod_error,
                mp.mensagem_error
            FROM oc_product p 
            LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
            LEFT JOIN oc_marketplace_product mp ON (mp.product_key_id = p.product_id AND mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp.tipo_integracao = ".Type_MarketPlace::Tp_Mercadolivre.")";

            $filterProduct = " WHERE pd.language_id = '".$defaultLang."' and (mp.cod_error is not null) ";
            $filterGrade   = " WHERE pd2.language_id = '".$defaultLang."' and (mp.cod_error is not null) ";

            if (!empty($data['filter_product_ml_id']) && !is_null($data['filter_product_ml_id'])) {
                $filterProduct .= " AND mp.marketId = '" . $this->db->escape($data['filter_product_ml_id']) . "'";
                $filterGrade   .= " AND mp2.marketId = '" . $this->db->escape($data['filter_product_ml_id']) . "'";
            }

            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:
                        $filterProduct .= (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']) . "%'" ;
                        $filterGrade   .= (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']) . "%'" ;
                        break;
                    case Type_ListCodProduct::Tp_CodProdEcommerce:
                        $filterProduct .= (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']) . "%'" ;
                        $filterGrade   .= (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:
                        $filterProduct .= (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']) . "%'" ;
                        $filterGrade   .= (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:
                        $filterProduct .= (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']) . "%'" ;
                        $filterGrade   .= (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:
                        $filterProduct .= (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']) . "%'" ;
                        $filterGrade   .= (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_name']) && !is_null($data['filter_name'])) {
                $filterProduct .= " AND pd.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
                $filterGrade   .= " AND pd2.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
            }


        $sqlGrade =
            "UNION 
                SELECT 
                    (TRUE) AS IsGrid, 
                    pg.product_grid_id AS product_grid_id, 
                    pgdetail.product_id AS product_id, 
                    pgdetail.product_grid_id AS product_key_id, 
                    pd.name, 
                    p.*, 
                    (mp.marketId) AS product_mercaId, 
                    mp.cod_error, 
                    mp.mensagem_error 
              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_marketplace_product mp ON ((mp.product_key_id = p.product_id and mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp.tipo_integracao = ".Type_MarketPlace::Tp_Mercadolivre.") or (mp.product_key_id = pg.product_grid_id and mp.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Grade." and mp.tipo_integracao = ".Type_MarketPlace::Tp_Mercadolivre.")) 
              ".$filterProduct." AND p.tipo IN ".ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList())." AND 
              (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) 
              LEFT JOIN oc_marketplace_product  mp2 ON ((mp2.product_key_id = p2.product_id and mp2.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Normal." and mp2.tipo_integracao = ".Type_MarketPlace::Tp_Mercadolivre.") or (mp2.product_key_id = pgd2.product_grid_id and mp2.tipo_produto = ".Type_ProdutoMkt::Tp_MktProd_Grade." and mp2.tipo_integracao = ".Type_MarketPlace::Tp_Mercadolivre.")) 
              ".$filterGrade." AND (pgd2.product_grid_id = pg.product_grid_id) and (mp2.cod_error is not null)
              ORDER by p2.quantity DESC 
              LIMIT 1) = p.product_id  ";


         $sql.= $filterProduct."and (p.tipo not in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList()) . ")".$sqlGrade;


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

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

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

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

		return $query->rows;

	}


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

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

		$sql = "SELECT COUNT(DISTINCT(mp.product_key_id)) as total FROM oc_marketplace_product mp "
					."INNER JOIN oc_product p ON (p.product_id = mp.product_key_id AND mp.tipo_integracao = ".Type_MarketPlace::Tp_Mercadolivre.") "
					."INNER JOIN oc_product_description pd ON (mp.product_key_id = pd.product_id) "
					."WHERE mp.date_last_error is not null ";

        if (!empty($data['filter_product_ml_id']) && !is_null($data['filter_product_ml_id'])) {
            $sql .= " AND mp.marketId = '" . $this->db->escape($data['filter_product_ml_id']) . "'";
        }

        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:
                    $sql .= (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']) . "%'" ;
                    break;
                case Type_ListCodProduct::Tp_CodProdEcommerce:
                    $sql .= (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:
                    $sql .= (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:
                    $sql .= (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:
                    $sql .= (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_name']) && !is_null($data['filter_name'])) {
            $sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
        }

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

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

	}


}
