<?php
class ModelMercadolivreMercadolivre extends Model
{

	public function getTypeShipping($data){

		$sql= "SELECT * FROM `".DB_PREFIX."mercadolivre_type_shipping` WHERE NOT status IS NULL ";
		$query = $this->db->query($sql);

		$TypeShipping=array();
		foreach ($query->rows as $result) {
					$TypeShipping[$result['code']] = array(
						'title'   => $result['title'],
						'status'	=> $result['status']
					);
			}

		return $TypeShipping;

	}

  public function getMlOrder($mercaOrderId){

    $query = $this->db->query(
			"SELECT
				mo.*
			FROM `oc_marketplace_order` mo
			inner join oc_order o on (o.order_id = mo.order_id)
			WHERE mo.mktOrderId = ".(int)$mercaOrderId
		 .  " and o.mkt_integracao_tipo = " . Type_MarketPlace::Tp_Mercadolivre);

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

	}

  public function getMercaOrderIds($order_id){
    $query = $this->db->query(
			"SELECT
				mo.mktOrderId
			FROM `oc_marketplace_order` mo
			inner join oc_order o on (o.order_id = mo.order_id)
			WHERE mo.order_id = ".(int)$order_id
		 .  " and o.mkt_integracao_tipo = " . Type_MarketPlace::Tp_Mercadolivre);

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

	}

  public function getMercadoCategory(){

	   return $this->db->query("SELECT * FROM `".DB_PREFIX."mercadolivre_category` WHERE 1 order by name asc")->rows;
	}

	 public function getMercadoCategoryOption($sel='', $option = true){

	   $parents = $this->db->query("SELECT * FROM `".DB_PREFIX."mercadolivre_category` WHERE 1 order by name asc")->rows;

	   if(!$option){
	    return $parents;
       }

	   $option='';
	   foreach($parents as $single)
	   {

			$chk='';
			if($single['mercaId']==$sel)$chk='selected';
			$option.='<option value="'.$single['mercaId'].'" '.$chk.' >'.$single['name'].'</option>';

	   }
	  return $option;
	}


	public function getMercadoType()
	{

	  $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "mercadolivre_listing_type ORDER BY code ASC");
	  $type_data=array();
		foreach ($query->rows as $result) {
					$type_data[] = array(
						'name'         => $result['name'],
						'value'          => $result['code']
					);
			}

	   return $type_data;
	}

	public function getMercadoCurrency()
	{
	      $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "currency ORDER BY title ASC");
	      $currency_data=array();
			foreach ($query->rows as $result) {
      			$currency_data[$result['code']] = array(
        			'name'         => $result['title'],
        			'value'          => $result['code']
      			);
    		}

	   return $currency_data;
	}

	public function getMercaId($mercaId){

		$sql = "SELECT mp.*,
		COALESCE(p.product_id,pgd.product_id) product_id
		FROM oc_marketplace_product  mp
		LEFT JOIN oc_product p ON p.product_id = mp.product_key_id AND mp.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Normal.
		" LEFT JOIN oc_product_grid_detail pgd ON pgd.product_grid_id = mp.product_key_id AND mp.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Grade.
		" WHERE mp.marketId = '".$mercaId."' LIMIT 1";

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

		return $query->row;

	}

  public function getMercaIdAll(){

		$mercaId = array ();

		$sql = "SELECT marketId FROM `oc_marketplace_product`";

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

		if(!empty($query->rows)){
			foreach($query->rows as $key => $value){
				$mercaId[]=$value['marketId'];
			}
		}
		return $mercaId;
	}

	public function getMLProducts($data = array ()){
		$sql = "SELECT (mp.product_key_id) AS productId, (mp.marketId) AS mercaId, (mp.url) AS url, (pd.name) AS title FROM `oc_marketplace_product` mp INNER JOIN `".DB_PREFIX."product_description` pd on (mp.product_key_id = pd.product_id)";

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

		$sql .= " WHERE mp.status = '".Type_MercadoLivreItemStatus::Tp_Active."' " ;

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

		if (isset($data['filter_category_id']) && !is_null($data['filter_category_id'])) {
            $sql .= " AND (nsub.category_id = '" . $this->db->escape($data['filter_category_id'])
            . "' OR ncat.category_id = '" . $this->db->escape($data['filter_category_id'])
            . "' OR ndep.category_id = '" . $this->db->escape($data['filter_category_id'])."')";
		}

		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 getMLTotalProducts($data = array ()){

		$sql = "SELECT count(mp.product_key_id) AS total FROM `oc_marketplace_product` mp INNER JOIN `".DB_PREFIX."product_description` pd on (mp.product_key_id = pd.product_id)";

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

		$sql .= " WHERE mp.status = '".Type_MercadoLivreItemStatus::Tp_Active."' " ;

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

		if (isset($data['filter_category_id']) && !is_null($data['filter_category_id'])) {
            $sql .= " AND (nsub.category_id = '" . $this->db->escape($data['filter_category_id'])."'
            OR ncat.category_id = '" . $this->db->escape($data['filter_category_id'])."'
            OR ndep.category_id = '" . $this->db->escape($data['filter_category_id'])."')";
		}

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

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



	public function addMlProducts($idEcommerce, $idMercadolivre, $linkMercadoLivre){
		$this->db->query("INSERT INTO oc_marketplace_product SET product_id = '" . (int) $idEcommerce. "', `marketId` = '" . $idMercadolivre . "', `status` = '".Type_MercadoLivreItemStatus::Tp_Active."', `url` = '".$linkMercadoLivre."' ");
	}

	public function alterMlProduct($idEcommerce, $mercaId){

		$this->db->query("UPDATE oc_marketplace_product SET product_id = '" . (int) $idEcommerce. "' WHERE  marketId = '".$mercaId."' ");

	}


	public function getShippingId($order_id){
		$sql = "SELECT
				mo.mktOrderId
			FROM `oc_marketplace_order` mo
			inner join oc_order o on (o.order_id = mo.order_id)
			WHERE mo.order_id = ".(int)$order_id
		 .  " and o.mkt_integracao_tipo = " . Type_MarketPlace::Tp_Mercadolivre;

			$result=$this->db->query($sql);
		if(isset($result->row['mktOrderId'])) {
		  return $result->row['mktOrderId'] ;
		}
		return  false;
	}
}
