<?php
class ModelMercadolivreQuestions extends Model{

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

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

			$sqlFilter = '';

			if (!empty($data['filter_mercaid'])) {
				$sqlFilter.= "AND mpq.mercaId = '".$this->db->escape($data['filter_mercaid'])."' ";
			}			

			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:
								$sqlFilter .= (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:
								$sqlFilter .= (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:
								$sqlFilter .= (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:
                            $sqlFilter .= (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:
								$sqlFilter .= (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_data_start']) && !empty($data['filter_data_end'])){
				$sqlFilter .= "AND CAST(mpq.data_criacao_pergunta as DATE) BETWEEN '".$this->db->escape($data['filter_data_start'])."' AND '".$this->db->escape($data['filter_data_end'])."' ";
			}elseif(!empty($data['filter_data_start']) && empty($data['filter_data_end'])){
				$sqlFilter .= "AND CAST(mpq.data_criacao_pergunta as DATE) >= '".$this->db->escape($data['filter_data_start'])."' ";
			}elseif(empty($data['filter_data_start']) && !empty($data['filter_data_end'])){
				$sqlFilter .= "AND CAST(mpq.data_criacao_pergunta as DATE) <= '".$this->db->escape($data['filter_data_start'])."' ";
			}
			
			if (!empty($data['filter_question_status'])) {
				$sqlFilter.= "AND mpq.status_pergunta = '".$this->db->escape($data['filter_question_status'])."' ";
			}				
			
			return $sqlFilter;
	}

	public function getQuestionsList($data = array()){
		$this->load->helper('ftr_geral');

        $tipoIntegracaoMl    = Type_MarketPlace::Tp_Mercadolivre;
        $question_answered   = Type_MercadoLivreQuestionStatus::Tp_Answered;
        $question_unanswered = Type_MercadoLivreQuestionStatus::Tp_Unanswered;
        $tipo_grade          = Type_ProdutoMkt::Tp_MktProd_Grade;
        $tipo_Produto        = Type_ProdutoMkt::Tp_MktProd_Normal;
        $tipo_mercadolivre   = Type_MarketPlace::Tp_Mercadolivre;


		$sql = "SELECT 
              mpq.data_criacao_pergunta,
              mpq.status_pergunta,
              mpq.product_question_id, 
              mp.marketId, 
              pd.name, 
              (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta in ('$question_answered','$question_unanswered') and mp.marketId = mpq.mercaId) as total_questions, 
              (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta = '$question_answered'  and mp.marketId = mpq.mercaId) as respondidas, 
              (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta = '$question_unanswered'  and mp.marketId = mpq.mercaId) as nao_respondidas, 
              mp.product_key_id, 
              (p.model) model, 
              p.id_futura 
              FROM oc_marketplace_product mp 
              INNER JOIN oc_mercadolivre_product_questions mpq ON (mpq.mercaId = mp.marketId) 
              INNER JOIN oc_product p ON (p.product_id = mp.product_key_id) 
              INNER JOIN oc_product_description pd ON (pd.product_id = p.product_id) 
              WHERE NOT mpq.product_question_id IS NULL  and mp.tipo_produto = $tipo_Produto and mp.tipo_integracao = $tipo_mercadolivre";

        $sqlGroupBy =" GROUP BY mp.marketId ";

        $sqlFilter = '';

        if (!empty($data['filter_mercaid'])) {
            $sqlFilter.= "AND mpq.mercaId = '".$this->db->escape($data['filter_mercaid'])."' ";
        }

        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:
                    $sqlFilter .= (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:
                    $sqlFilter .= (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:
                    $sqlFilter .= (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:
                    $sqlFilter .= (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_data_start']) && !empty($data['filter_data_end'])){
            $sqlFilter .= "AND CAST(mpq.data_criacao_pergunta as DATE) BETWEEN '".$this->db->escape($data['filter_data_start'])."' AND '".$this->db->escape($data['filter_data_end'])."' ";
        }elseif(!empty($data['filter_data_start']) && empty($data['filter_data_end'])){
            $sqlFilter .= "AND CAST(mpq.data_criacao_pergunta as DATE) >= '".$this->db->escape($data['filter_data_start'])."' ";
        }elseif(empty($data['filter_data_start']) && !empty($data['filter_data_end'])){
            $sqlFilter .= "AND CAST(mpq.data_criacao_pergunta as DATE) <= '".$this->db->escape($data['filter_data_start'])."' ";
        }

        if (!empty($data['filter_question_status'])) {
            $sqlFilter.= "AND mpq.status_pergunta = '".$this->db->escape($data['filter_question_status'])."' ";
        }

        $sqlGrid =  "union
                         SELECT
                             mpq.data_criacao_pergunta,
                             mpq.status_pergunta,
                             mpq.product_question_id,
                             mp.marketId,
                             pd.name,
                             (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta in ('$question_answered','$question_unanswered') and mp.marketId = mpq.mercaId) as total_questions,  
                             (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta = '$question_answered'  and mp.marketId = mpq.mercaId) as respondidas,
                             (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta = '$question_unanswered'  and mp.marketId = mpq.mercaId) as nao_respondidas,  
                             mp.product_key_id,
                             (pg.referencia) model, 
                             p.id_futura
                         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)
                         INNER JOIN oc_marketplace_product mp ON (mp.product_key_id = pg.product_grid_id) and mp.tipo_integracao = $tipoIntegracaoMl
                         INNER JOIN oc_mercadolivre_product_questions mpq ON (mpq.mercaId = mp.marketId)
                         LEFT JOIN oc_product_description pd ON (pd.product_id = p.product_id)   
                         WHERE NOT mpq.product_question_id IS NULL  and mp.tipo_produto = $tipo_grade and mp.tipo_integracao = $tipo_mercadolivre                    
                         ";

		$sort_data = array(
			'sort_unanswered',
			'sort_name',
			'sort_qtd'
		);

        $sqlSort = "";
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'sort_unanswered') {
                $sqlSort = ftr_geral::sqlOrderByAdd('', "nao_respondidas");
			}elseif($data['sort'] == 'sort_name'){
                $sqlSort = ftr_geral::sqlOrderByAdd('', "name");
			}elseif($data['sort'] == 'sort_qtd'){
                $sqlSort = ftr_geral::sqlOrderByAdd('', "total_questions");
			}
		}else{
            $sqlSort = ftr_geral::sqlOrderByAdd('', "status_pergunta = '".Type_MercadoLivreQuestionStatus::Tp_Unanswered."' ");
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sqlSort .= " DESC, CAST(data_criacao_pergunta as DATE) DESC";
		} else {
            $sqlSort .=  " ASC, CAST(data_criacao_pergunta as DATE) ASC";
		}


        $sqlLimit = "";
		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'];
		}

		$sqlGroupBy = " GROUP BY mp.marketId ";


		$sql =
                $sql . " " .  $sqlFilter . " " . $sqlGroupBy ." " .
                $sqlGrid . " " . $sqlFilter . " " . $sqlGroupBy . " " . $sqlSort  . " " . $sqlLimit;


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

		return $query->rows;			

	}

	public function getQuestionsTotalList($data = array()){
        $tipo_mercadolivre   = Type_MarketPlace::Tp_Mercadolivre;
        $filter              = $this->getFilter($data);
        $sqlIn               = ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList());

	    $sql = "
                    SELECT 
                        COUNT(DISTINCT(mpq.mercaId)) as total 
                    FROM oc_marketplace_product mp 
                    left JOIN oc_mercadolivre_product_questions mpq ON (mpq.mercaId = mp.marketId) 
                    left JOIN oc_product p ON (p.product_id = mp.product_key_id) 
                    left JOIN oc_product_description pd ON (pd.product_id = p.product_id)                                         
                    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_marketplace_product mp on (mp.product_key_id = pgd.product_grid_id and mp.tipo_integracao = $tipo_mercadolivre)
                    inner join oc_product_description pd on (pd.product_id = p.product_id)
                    INNER JOIN oc_mercadolivre_product_questions mpq ON (mpq.mercaId = mp.marketId) 
                    WHERE (NOT mpq.product_question_id IS NULL) $filter and (p.tipo in $sqlIn)
                    group by pgd.product_grid_id) as pgd on (pgd.product_grid_id = mp.product_key_id)                                       
                    WHERE (NOT mpq.product_question_id IS NULL)and (mp.tipo_integracao = $tipo_mercadolivre) and ( (  (p.tipo not in $sqlIn) $filter  ) or (pgd.product_grid_id is not null))  
	    ";

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


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

	}
	
	public function getQuestionInfo($mercaid){
        $this->load->helper('ftr_geral');
        $tipoIntegracaoMl    = Type_MarketPlace::Tp_Mercadolivre;
        $question_answered   = Type_MercadoLivreQuestionStatus::Tp_Answered;
        $question_unanswered = Type_MercadoLivreQuestionStatus::Tp_Unanswered;
        $tipo_Grade          = Type_ProdutoMkt::Tp_MktProd_Grade;
        $tipo_Produto        = Type_ProdutoMkt::Tp_MktProd_Normal;

		$sql = "SELECT DISTINCT 
					mpq.product_question_id, 
					mp.marketId, 
					pd.name, 
					(select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta in ('$question_answered','$question_unanswered') and mp.marketId = mpq.mercaId) as total_questions, 
					(select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta = '$question_answered'  and mp.marketId = mpq.mercaId) as respondidas, 
                    (mp.product_key_id) as product_id, 
					mp.url 
				FROM oc_marketplace_product mp 
				INNER JOIN oc_mercadolivre_product_questions mpq ON (mpq.mercaId = mp.marketId) 
				INNER JOIN oc_product_description pd ON (mp.product_key_id = pd.product_id) 
				WHERE mp.marketId = '$mercaid' and mp.tipo_integracao = $tipoIntegracaoMl and mp.tipo_produto = $tipo_Produto";

        $sql .= " union     
                    SELECT DISTINCT 
                        mpq.product_question_id, 
                        mp.marketId, 
                        pd.name, 
                        (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta in ('$question_answered','$question_unanswered') and mp.marketId = mpq.mercaId) as total_questions, 
                        (select count(mpq.product_question_id) from oc_mercadolivre_product_questions mpq where mpq.status_pergunta = '$question_answered'  and mp.marketId = mpq.mercaId) as respondidas, 
                        (pgd.product_id) as product_id, 
                        mp.url 
                    FROM oc_marketplace_product mp 
                    INNER JOIN oc_mercadolivre_product_questions mpq ON (mpq.mercaId = mp.marketId) 
                    inner join oc_product_grid pg on (mp.product_key_id = pg.product_grid_id)
                    inner join oc_product_grid_detail pgd on (pgd.product_grid_id = pg.product_grid_id)
                    INNER JOIN oc_product_description pd ON (pd.product_id = pgd.product_id) 
                    WHERE mp.marketId = '$mercaid' and mp.tipo_integracao = $tipoIntegracaoMl and mp.tipo_produto = $tipo_Grade
                    group by mp.marketId";

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

		if($query->rows){
			return array(
                'name'       => $query->row['name'],
                'mercaid'    => $query->row['marketId'],
                'questions'  => $query->row['total_questions'],
                'answer'     => $query->row['respondidas'],
                'product_id' => $query->row['product_id'],
                'linkml'     => $query->row['url']
			);
		}else{
			return false;
		}

	}

	public function getQuestions($data){

		$sql ="SELECT mpq.*, "
				 ."CAST(data_criacao_pergunta AS DATE) as data_criacao_pergunta_cast, "
				 ."CAST(data_criacao_resposta AS DATE) as data_criacao_resposta_cast "
				 ."FROM ".DB_PREFIX."mercadolivre_product_questions mpq WHERE NOT mpq.product_question_id IS NULL ";				 			 

		$sql .= $this->getFilter($data);	

		$sql.="ORDER BY mpq.status_pergunta = '".Type_MercadoLivreQuestionStatus::Tp_Answered."' ASC, mpq.data_criacao_pergunta DESC";		

		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 getQuestionsTotal($data){

			$sql ="SELECT COUNT(mpq.product_question_id) AS total "
					 ."FROM ".DB_PREFIX."mercadolivre_product_questions mpq WHERE NOT mpq.product_question_id IS NULL ";
	
			$sql .= $this->getFilter($data);			

			$sql.="ORDER BY mpq.status_pergunta = '".Type_MercadoLivreQuestionStatus::Tp_Answered."' ASC, mpq.data_criacao_pergunta DESC";	

			$query	= $this->db->query($sql);
	
			return $query->row['total'];
	
	}
	

}
