<?php
class ModelUniversalFeedDriverProduct extends Model {
  private $langIdToCode = array();
  private $langIdToFullCode = array();
  private $defaultLangId = '';
  private $options = false;
  
  public function getItems($data = array(), $count = false) {
    // store_id for use with URL and multistore, set to 0 if empty
    if (!empty($data['filter_store'])) {
      $store_id = $data['filter_store'];
    } else {
      $store_id = 0;
    }
    
    if ($count) {
      $select = 'COUNT(DISTINCT p.product_id) AS total';
    } else {
      $select = 'p.*, m.name as manufacturer';
      if (isset($data['filter_language']) && $data['filter_language'] !== '') {
        $select .= ", pd.*";
      }
      
      /*
      if (empty($data['param_image_path'])) {
        //$select .= ", CONCAT('".$this->config->get('config_url')."image/', p.image) as image, p.image as image_path";
        //$select .= ", (SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = p.product_id ORDER BY sort_order ASC)";
      }
      */
    }
    
    if (!$count) {
      $select .= ", (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . ($data['filter_language'] ? $data['filter_language'] : (int)$this->config->get('config_language_id')) . "') AS stock_status\n";
    }
    
    // current special price
    if (!empty($data['special_price_group'])) {
      $special_price_group = $data['special_price_group'];
    } else {
      $special_price_group = $this->config->get('config_customer_group_id');
    }
    
    $special = '';
    //$special = ", (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$special_price_group . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special\n";
    //$special .= ", (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$special_price_group . "' AND pd2.quantity > '0' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount\n";
    
    $sql = "SELECT " . $select . $special . " FROM " . DB_PREFIX . "product p\n";
    
    if (!$count) {
      $sql .= " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)\n";
    }
    
    //if (isset($data['filter_language']) && $data['filter_language'] !== '') { // always get it to avoid error with filters
    if (!$count) {
      $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)\n";
    }
    //}
    
    if (isset($data['filter_store']) && $data['filter_store'] !== '') {
      $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)\n";
    }
    
    /*
    if (!empty($data['filter_category']) || !empty($data['filter_category_exclude'])) {
      $sql .=  " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)\n";
    }
    */
    
    // WHERE
    if (!empty($data['include_disabled'])) {
      $sql .= " WHERE 1\n";
    } else {
      $sql .= " WHERE p.status = 1\n";
    }
    
    if (!empty($data['empty_images'])) {
      $sql .= " AND p.image <> ''";
    }
    
    // languages
    $filter_language = isset($data['filter_language']) ? $data['filter_language'] : '';
    
    if (!$count) {
      if (isset($data['filter_language']) && $data['filter_language'] !== '') {
        $sql .= " AND pd.language_id = '" . (int)$data['filter_language'] . "'";
      } else {
        $lgquery = $this->db->query("SELECT DISTINCT language_id, code FROM " . DB_PREFIX . "language WHERE status = 1")->rows;
        
        foreach ($lgquery as $lang) {
          $this->langIdToCode[$lang['language_id']] = substr($lang['code'], 0, 2);
          $this->langIdToFullCode[$lang['language_id']] = $lang['code'];
          
          if ($lang['language_id'] == $this->config->get('config_language_id')) {
            $this->defaultLangId = $lang['language_id'];
          }
        }
      }
    }
    
    if (isset($data['filter_store']) && $data['filter_store'] !== '') {
      $sql .= " AND p2s.store_id = '" . (int)$data['filter_store'] . "'";
    }
    
    // conditions
    if (!empty($data['conditions'])) {
      foreach ($data['conditions'] as $condition) {
        $tableField = 'p.`' . $this->db->escape($condition['field']) . '`';
        if (in_array($condition['field'], array('name', 'description'))) {
          $tableField = 'pd.' . $this->db->escape($condition['field']);
        } else if ($condition['field'] == 'manufacturer') {
          $tableField = 'm.name';
        }
        
        $sql .= " AND ".$tableField;
        switch ($condition['comparator']) {
          case 'is_equal': default: $sql .= " = '".$this->db->escape($condition['value'])."'"; break;
          case 'is_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " NOT IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal': $sql .= " != '".$this->db->escape($condition['value'])."'"; break;
          case 'is_greater': $sql .= " > '".$this->db->escape($condition['value'])."'"; break;
          case 'is_lower': $sql .= " < '".$this->db->escape($condition['value'])."'"; break;
          case 'contain': $sql .= " LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
          case 'not_contain': $sql .= " NOT LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
        }
      }
    }
    
    if (!empty($data['conditions_or'])) {
      $sql .= " AND (";
      $first = true;
      
      foreach ($data['conditions_or'] as $condition) {
        $tableField = 'p.`' . $this->db->escape($condition['field']) . '`';
        if (in_array($condition['field'], array('name', 'description'))) {
          $tableField = 'pd.' . $this->db->escape($condition['field']);
        } else if ($condition['field'] == 'manufacturer') {
          $tableField = 'm.name';
        }
        
        if (!$first) {
          $sql .= " OR ";
        }
        
        $first = false;
        
        $sql .= $tableField;
        
        switch ($condition['comparator']) {
          case 'is_equal': default: $sql .= " = '".$this->db->escape($condition['value'])."'"; break;
          case 'is_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " NOT IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal': $sql .= " != '".$this->db->escape($condition['value'])."'"; break;
          case 'is_greater': $sql .= " > '".$this->db->escape($condition['value'])."'"; break;
          case 'is_lower': $sql .= " < '".$this->db->escape($condition['value'])."'"; break;
          case 'contain': $sql .= " LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
          case 'not_contain': $sql .= " NOT LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
        }
      }
      $sql .= ")";
    }
    
    // exclude products
    if (!empty($data['exclude_products'])) {
      foreach ($data['exclude_products'] as $condition) {
        $tableField = 'p.`' . $this->db->escape($condition['field']) . '`';
        if (in_array($condition['field'], array('name', 'description'))) {
          $tableField = 'pd.' . $this->db->escape($condition['field']);
        } else if ($condition['field'] == 'manufacturer') {
          $tableField = 'm.name';
        }
        
        $sql .= " AND ".$tableField;
        switch ($condition['comparator']) {
          case 'is_equal': default: $sql .= " != '".$this->db->escape($condition['value'])."'"; break;
          case 'is_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " NOT IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal': $sql .= " = '".$this->db->escape($condition['value'])."'"; break;
          case 'is_greater': $sql .= " < '".$this->db->escape($condition['value'])."'"; break;
          case 'is_lower': $sql .= " > '".$this->db->escape($condition['value'])."'"; break;
          case 'contain': $sql .= " NOT LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
          case 'not_contain': $sql .= " LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
        }
      }
    }
    
    if (!empty($data['exclude_products_and'])) {
      $sql .= " AND NOT (";
      $first = true;
      
      foreach ($data['exclude_products_and'] as $condition) {
        $tableField = 'p.`' . $this->db->escape($condition['field']) . '`';
        if (in_array($condition['field'], array('name', 'description'))) {
          $tableField = 'pd.' . $this->db->escape($condition['field']);
        } else if ($condition['field'] == 'manufacturer') {
          $tableField = 'm.name';
        }
        
        if (!$first) {
          $sql .= " AND ";
        }
        
        $first = false;
        
        $sql .= $tableField;
        
        switch ($condition['comparator']) {
          case 'is_equal': default: $sql .= " = '".$this->db->escape($condition['value'])."'"; break;
          case 'is_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal_list':
            $in = array();
            
            foreach (explode('|', $condition['value']) as $v) {
              $in[] = $this->db->escape($v);
            }
            
            $sql .= " NOT IN ('".implode( "', '" , array_map('addslashes', $in))."')";
          break;
          case 'is_not_equal': $sql .= " != '".$this->db->escape($condition['value'])."'"; break;
          case 'is_greater': $sql .= " > '".$this->db->escape($condition['value'])."'"; break;
          case 'is_lower': $sql .= " < '".$this->db->escape($condition['value'])."'"; break;
          case 'contain': $sql .= " LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
          case 'not_contain': $sql .= " NOT LIKE N'%".$this->db->escape($condition['value'])."%' COLLATE utf8_bin"; break;
        }
      }
      $sql .= ")";
    }
    
    if (!empty($data['filter_category'])) {
      $data['filter_category'] = implode(',', array_map('intval', (array) $data['filter_category']));
			//$sql .= " AND p2c.category_id IN (" . $data['filter_category'] . ")";
      
      if (!empty($data['filter_category_subs'])) {
        // include subcategories
        $sql .= " AND EXISTS (SELECT * FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id) WHERE p.product_id = p2c.product_id AND cp.path_id IN (" . $data['filter_category'] . "))";
			} else {
        // selected categories only
        $sql .= " AND EXISTS (SELECT * FROM " . DB_PREFIX . "product_to_category p2c WHERE p.product_id = p2c.product_id AND p2c.category_id IN (" . $data['filter_category'] . "))";
			}
    }
    
    if (!empty($data['filter_category_exclude'])) {
      $data['filter_category_exclude'] = implode(',', array_map('intval', (array) $data['filter_category_exclude']));
			//$sql .= " AND p2c.category_id NOT IN (" . $data['filter_category_exclude'] . ")"; 
			$sql .= " AND NOT EXISTS (SELECT * FROM " . DB_PREFIX . "product_to_category p2c WHERE p.product_id = p2c.product_id AND p2c.category_id IN (" . $data['filter_category_exclude'] . "))";
    }
    
    if (!empty($data['filter_manufacturer'])) {
      $data['filter_manufacturer'] = implode(',', array_map('intval', (array) $data['filter_manufacturer']));
			$sql .= " AND p.manufacturer_id IN (" . $data['filter_manufacturer'] . ")";
		}
    
    if (!empty($data['filter_manufacturer_exclude'])) {
      $data['filter_manufacturer_exclude'] = implode(',', array_map('intval', (array) $data['filter_manufacturer_exclude']));
			$sql .= " AND p.manufacturer_id NOT IN (" . $data['filter_manufacturer_exclude'] . ")";
		}
    
    if (!empty($data['filter_product'])) {
      $data['filter_product'] = implode(',', array_map('intval', (array) $data['filter_product']));
			$sql .= " AND p.product_id IN (" . $data['filter_product'] . ")";
		}
    
    if (!empty($data['filter_product_exclude'])) {
      $data['filter_product_exclude'] = implode(',', array_map('intval', (array) $data['filter_product_exclude']));
			$sql .= " AND p.product_id NOT IN (" . $data['filter_product_exclude'] . ")";
		}
    
    if (!empty($this->request->get['search'])) {
			$sql .= " AND pd.name LIKE '%" . $this->db->escape($this->request->get['search']) . "%'";
		}
    
		if (!empty($data['filter_name'])) {
			$sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
		}
    // price
    if (!empty($data['filter_price_min'])) {
			$sql .= " AND p.price >= '" . (float)$data['filter_price_min'] . "'";
		}
    
    if (!empty($data['filter_price_max'])) {
			$sql .= " AND p.price <= '" . (float)$data['filter_price_max'] . "'";
		}

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

		if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
			$sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
		}

		if (!empty($data['in_stock'])) {
			$sql .= " AND p.quantity >= '" . (int)$data['in_stock'] . "'";
		}
    
		if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
			$sql .= " AND p.quantity = '" . (int)$data['filter_quantity'] . "'";
		}

		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
		}
    
    // return count
    if ($count) {
      return $this->db->query($sql)->row['total'];
    }
    
		$sql .= " GROUP BY p.product_id";
    
		$sort_data = array(
			'pd.name',
			'p.model',
			'p.price',
			'p.quantity',
			'p.status',
			'p.sort_order',
			'p.date_added',
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY p.product_id";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
    
		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);
    
    $rows = array();
    
    foreach ($query->rows as &$row) {
      if (empty($row['image'])) {
        $row['image'] = 'no_image.jpg';
      }
      
      // fix fields with cdata
      $row['description'] = str_replace(']]>', '', $row['description']);
      $row['description'] = str_replace('<![CDATA[', '', $row['description']);
      
      $row['description'] = str_replace('&lt;![CDATA[', '', $row['description']);
      $row['description'] = str_replace(']]&gt;', '', $row['description']);
      
      $row['meta_description'] = str_replace(']]>', '', $row['meta_description']);
      $row['meta_description'] = str_replace('<![CDATA[', '', $row['meta_description']);
      
      $row['meta_description'] = str_replace('&lt;![CDATA[', '', $row['meta_description']);
      $row['meta_description'] = str_replace(']]&gt;', '', $row['meta_description']);
        
      $row['image_path'] = $row['image'];
      if (empty($data['image_path']) && !in_array(substr($row['image'], 0, 6), array('http:/', 'https:'))) {
        if (defined('HTTPS_IMAGE')) {
          $row['image'] = HTTPS_IMAGE . $row['image'];
        } else {
          $row['image'] = $this->config->get('config_url') . 'image/' . $row['image'];
        }
      }
      
      if (isset($data['filter_language']) && $data['filter_language'] === '') {
        $row += $this->getProductDescription($row['product_id']);
      } else if ($this->config->get('mlseo_enabled') && $this->config->get('mlseo_multistore') && $store_id) {
        $this->setSeoDescription($row, $store_id, $data['filter_language']);
      }
      
      // fix chinese special chars
      if ($this->config->get('univfeed_fix_chinese_chars')) {
        $row['name'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['name']);
        $row['model'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['model']);
        $row['description'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['description']);
        $row['meta_description'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['meta_description']);
      }
      
      $row['additional_images'] = $this->getProductImages($row['product_id'], empty($data['image_path']), !empty($data['addtionalImageAsArray']));
      $row['filters'] = $row['product_filter'] = $this->getProductFilters($row['product_id'], !empty($data['filterAsArray']));
      $row['product_attribute'] = $this->getProductAttributes($row['product_id'], !empty($data['attributeAsArray']));
      
      // for custom nodes
      if (!empty($data['cust_nodes'])) {
        if (in_array('{price_notax}', $data['cust_values'])) {
          $row['price_notax'] = $this->currency->format($row['price'], $data['currency'], false, !empty($data['price_format']));
        }
        
        if (in_array('{attributes}', $data['cust_values'])) {
          if ($data['format'] == 'xml') {
            
          }
        }
      }
      
      if (!empty($row['product_attribute'])) {
        if (is_array($row['product_attribute'])) {
          foreach ($row['product_attribute'] as $attr) {
            if (!empty($data['filter_language']) && isset($attr[$data['filter_language']])) {
              $row['attribute:'.$attr[$data['filter_language']]['attribute']] = $attr[$data['filter_language']]['value'];
            } else {
              
            }
          }
        } else {
          foreach (explode('|', $row['product_attribute']) as $attr) {
            list($attrGroup, $attrName, $attrVal) = explode(':', $attr);
            $row['attribute:'.str_replace(' ', '_', $attrName)] = $attrVal;
          }
        }
      }
      
      if (!empty($row['product_filter'])) {
        if (is_array($row['product_filter'])) {
          foreach ($row['product_filter'] as $attr) {
            if (!empty($data['filter_language'])) {
              $row['filter:'.$attr[$data['filter_language']]['attribute']] = $attr[$data['filter_language']]['value'];
            } else {
              
            }
          }
        } else {
          foreach (explode('|', $row['product_filter']) as $attr) {
            list($attrName, $attrVal) = explode(':', $attr);
            if (isset($row['filter:'.str_replace(' ', '_', $attrName)])) {
              $row['filter:'.str_replace(' ', '_', $attrName)] .= '|'. $attrVal;
            } else {
              $row['filter:'.str_replace(' ', '_', $attrName)] = $attrVal;
            }
          }
        }
      }
      // end custom nodes
      
      if (empty($data['optionDisabled'])) {
        $row += $this->getProductOptionsFields($row['product_id'], !empty($data['optionAsArray']), $data);
      }
      
      $this->setProductCategoryData($row['product_id'], $row, (!empty($data['category_extra_field']) ? $data['category_extra_field'] : ''));
      $row['product_category'] = $this->getProductCategories($row['product_id'], $data, !empty($data['categoryAsArray']));
      
      $row['category_shortest'] = '';
      $row['category_largest'] = '';
      
      if (empty($data['categoryAsArray'])) {
        $categories = explode('|', $row['product_category']);
      }
      
      if (count($categories)) {
        usort($categories, array($this, 'sortBySubcatNumber'));
        $row['category_shortest'] = reset($categories);
        $row['category_largest'] = end($categories);
      }
      
      if (!empty($data['shipping'])) {
        $shippingValues = is_string($data['shipping']) ? explode("\n", $data['shipping']) : $data['shipping'];
        
        $shipping_price = $shipping_time = $shipping_type = '';
        
        foreach ($shippingValues as $shippingRow) {
          $shipping = explode('=', str_replace("\r", '', $shippingRow));
          
          if (!isset($shipping[1])) continue;
          
          $shippingConditions = explode('|', html_entity_decode($shipping[0], ENT_QUOTES, 'UTF-8'));
          
          $conditionMatch = true;
          foreach ($shippingConditions as $shippingCondition) {
            if (substr($shippingCondition, 0, 1) == 'Q') {
              if (strpos($shippingCondition, '-') !== false) {
                list($valMin, $valMax) = explode('-', str_replace('Q', '', $shippingCondition));
                
                if (!($row['quantity'] >= $valMin && $row['quantity'] < $valMax)) {
                  $conditionMatch = false; break;
                }
              } else if (strpos($shippingCondition, 'Q>') !== false) {
                $cond = str_replace('Q>', '', $shippingCondition);
                
                if ($row['quantity'] < $cond) {
                  $conditionMatch = false; break;
                }
              } else if (strpos($shippingCondition, 'Q<') !== false) {
                $cond = str_replace('Q<', '', $shippingCondition);
                
                if ($row['quantity'] >= $cond) {
                  $conditionMatch = false; break;
                }
              }
            } elseif (substr($shippingCondition, 0, 1) == 'C') {
              $catList = explode('|', str_replace('C:', '', $shippingCondition));
              
              if (!in_array($row['category'], $catList)) {
                $conditionMatch = false; break;
              }
            } elseif (substr($shippingCondition, 0, 1) == 'W') {
              if (strpos($shippingCondition, '-') !== false) {
                list($valMin, $valMax) = explode('-', str_replace('W', '', $shippingCondition));
                
                if (!($row['weight'] >= $valMin && $row['weight'] < $valMax)) {
                  $conditionMatch = false; break;
                }
              } else if (strpos($shippingCondition, 'W>') !== false) {
                $cond = str_replace('W>', '', $shippingCondition);
                
                if ($row['weight'] < $cond) {
                  $conditionMatch = false; break;
                }
              } else if (strpos($shippingCondition, 'W<') !== false) {
                $cond = str_replace('W<', '', $shippingCondition);
                
                if ($row['weight'] >= $cond) {
                  $conditionMatch = false; break;
                }
              }
            } else {
              if (strpos($shippingCondition, '-') !== false) {
                list($valMin, $valMax) = explode('-', str_replace('P', '', $shippingCondition));
                
                if (!($row['price'] >= $valMin && $row['price'] < $valMax)) {
                  $conditionMatch = false; break;
                }
              } else if (strpos($shippingCondition, 'P>') !== false) {
                $cond = str_replace('P>', '', $shippingCondition);
                
                if ($row['price'] < $cond) {
                  $conditionMatch = false; break;
                }
              } else if (strpos($shippingCondition, 'P<') !== false) {
                $cond = str_replace('P<', '', $shippingCondition);
                
                if ($row['price'] >= $cond) {
                  $conditionMatch = false; break;
                }
              }
            }
          }
          
          if ($conditionMatch) {
            $shipping_price = $shipping[1];
            $shipping_time = isset($shipping[2]) ? $shipping[2] : '';
            $shipping_type = isset($shipping[3]) ? $shipping[3] : '';
            break;
          }
        }
        
        $row['shipping_price'] = $shipping_price;
        $row['shipping_time'] = $shipping_time;
        $row['shipping_type'] = $shipping_type;
      } else {
        $row['shipping_price'] = '';
        $row['shipping_time'] = '';
        $row['shipping_type'] = '';
      }
      
      $row += $this->getProductDiscounts($row['product_id'], $special_price_group);
      $row += $this->getProductSpecials($row['product_id'], $special_price_group);
      
      // skip products without special or discount
      if (!empty($data['is_special']) && empty($row['special']) && empty($row['discount'])) {
        continue;
      }
      
      if (false && $this->config->get('config_opt_comb_status')) {
        $product_option_data = array();
      
        $this->load->model('extension/module/options_combinations');
        $options_combinations = $this->model_extension_module_options_combinations->getCombinedOptions($row['product_id']);
        if ($options_combinations) {
          $product_options = $this->model_extension_module_options_combinations->getProductOptions($row['product_id']);
          
          foreach ($product_options as $product_option2) {
            foreach ($product_option2['product_option_value'] as $product_option) {
              $product_option_data[] = array(
                'id'                   => $product_option['option_value_id'],
                'value'                => !empty($product_option['name']) ? $product_option['name'] : '',
                'image'                => !empty($product_option['image']) ? $product_option['image'] : '',
                'name'                 => $product_option['name'],
                'quantity'             => !empty($product_option['quantity']) ? $product_option['quantity'] : '',
                // 'type'                 => $product_option['option_type'],
                // 'required'             => $product_option['required'],
                // 'pov_id'               => $product_option['product_option_value_id'],
                // 'subtract'             => $product_option['subtract'],
                // 'price'                => $product_option['price_prefix'] . $product_option['price'],
                // 'weight'               => $product_option['weight_prefix'] . $product_option['weight'],
              );
            }
          }
          
          $row['product_option'] = $product_option_data;
        }
      }
      
      if (!empty($data['get_coupon'])) {
        $row['coupon'] = $this->getProductCoupon($row['product_id']);
      }
      
      //$row['product_special'] = $this->getProductSpecial($row['product_id']);
      
      if (!empty($data['replaces'])) {
        foreach ($data['replaces'] as $condition) {
          switch ($condition['comparator']) {
            case 'is_equal': default: $row[$condition['field']] = ($row[$condition['field']] == $condition['value']) ? $condition['output'] : $row[$condition['field']]; break;
            case 'is_not_equal':  $row[$condition['field']] = ($row[$condition['field']] != $condition['value']) ? $condition['output'] : $row[$condition['field']]; break;
            case 'is_equal_list': $row[$condition['field']] = (in_array($condition['field'], explode('|', $condition['value']))) ? $condition['output'] : $row[$condition['field']]; break;
            case 'is_not_equal_list': $row[$condition['field']] = (!in_array($condition['field'], explode('|', $condition['value']))) ? $condition['output'] : $row[$condition['field']]; break;
            case 'is_greater':  $row[$condition['field']] = ($row[$condition['field']] > $condition['value']) ? $condition['output'] : $row[$condition['field']]; break;
            case 'is_lower':  $row[$condition['field']] = ($row[$condition['field']] < $condition['value']) ? $condition['output'] : $row[$condition['field']]; break;
            case 'contain':  $row[$condition['field']] = ($condition['value'] !== '' && strpos($row[$condition['field']], $condition['value']) !== false) ? $condition['output'] : $row[$condition['field']]; break;
            case 'not_contain':  $row[$condition['field']] = ($condition['value'] !== '' && strpos($row[$condition['field']], $condition['value']) === false) ? $condition['output'] : $row[$condition['field']]; break;
          }
        }
      }
      
      $row['udfExtraFields'] = array();
      
      // custom nodes
      if (!empty($data['cust_nodes'])) {
        // fill extra fields names
        foreach ($data['cust_nodes'] as &$node) {
          if (!$node && !empty($data['cust_names'])) {
            $node = '_'.array_shift($data['cust_names']);
          }
        }
        
        $customVals = array_combine($data['cust_nodes'], $data['cust_values']);
        
        if (preg_grep('/{link/', $data['cust_values'])) {
          $row['link'] = $this->url->link('product/product', 'product_id=' . $row['product_id']);
        }
        
        foreach ($customVals as $field => &$val) {
          // replace values
          $val = $this->replaceCustomNodes($row, $val);

          // strip unassigned options
          if (strpos($val, '{') !== false) {
            $val = preg_replace('/{option_\w+}/', '', $val);
            $val = preg_replace('/\{\w+\|([^\}]+)\}/', '', $val);
            $val = preg_replace('/\{\w+\}/', '', $val);
          }
          
          // add new field
          if (substr($field, 0, 1) == '_') {
            $row['udfExtraFields'][substr($field, 1)] = $val;
          
          // edit existing field
          } else {
            $row[$field] = $val;
          }
        }
      }
      
      if (!empty($data['option_row'])) {
        $options = array();
        
        if ($this->options === false) {
          $this->options = $this->getProductOptions($row['product_id'], true, $data);
        }
        
        if ($this->options) {
          foreach ($this->options as $k => $option) {
            // include only selected options
            if (!empty($data['option_size']) || !empty($data['option_color'])) {
              if (!(!empty($data['option_size']) && in_array($option['id'], (array) $data['option_size'])) && !(!empty($data['option_color']) && in_array($option['id'], (array) $data['option_color']))) {
                continue;
              }
            }
             
            $options[$k] = $option;
          }
        }
      }
      
      if (!empty($data['option_row']) && !empty($options)) {
        $variant_count = 0;
        
        foreach ($options as $option) {
          // include only sepcific option variants
          if (!empty($data['include_only_option_id']) && $data['include_only_option_id'] != $option['id']) {
            continue;
          }
          
          if (!empty($option['image']) && !in_array(substr($row['image'], 0, 6), array('http:/', 'https:'))) {
            if (defined('HTTPS_IMAGE')) {
              $option['image'] = HTTPS_IMAGE . $option['image'];
            } else {
              $option['image'] = $this->config->get('config_url') . 'image/' . $option['image'];
            }
          }
          
          foreach ($option as $opt_type => $opt_value) {
            $row['option_'.$opt_type] = $opt_value;
          }
          
          // include option values into current data
          $opt_model = isset($option['model']) ? $option['model'] : str_pad($variant_count+1, 2, '0', STR_PAD_LEFT);
          
          $opt_data = array(
            'orig_model' => $row['model'],
            'model' => $row['model'] . '-' . $opt_model,
            'name' => $row['name'] . ' - ' . $option['value'],
            'orig_name' => $row['name'],
            'quantity' => $option['quantity'],
            'orig_price' => $row['price'],
            'price' => (substr($option['price'], 0, 1) == '=') ? (float) substr($option['price'], 1) : $row['price'] + $option['price'],
            'special' => $row['special'] ? (substr($option['price'], 0, 1) == '=') ? (float) substr($option['price'], 1) : $row['special'] + $option['price'] : $row['special'],
            'discount' => $row['discount'] ? (substr($option['price'], 0, 1) == '=') ? (float) substr($option['price'], 1) : $row['discount'] + $option['price'] : $row['discount'],
            'image' => !empty($option['image']) ? $option['image'] : $row['image'],
            'image_path' => !empty($option['image']) ? $option['image'] : $row['image_path'],
          );
          
          $row['is_variant'] = $variant_count ? 1 : 0;
          $row['variant_count'] = $variant_count++;
          
          $this->getCustomNodes($data, $row);
          
          $optRow = $opt_data + $row;
          
          $this->setStockStatus($data, $optRow);
          
          $rows[] = $optRow;
        }
      } else if (!empty($data['language_override_row'])) {
        if (!empty($row['description_array'])) {
          foreach ($row['description_array'] as $langId => $desc) {
            //$row['override'] = $desc['language_code'];
            $row['language_id'] = $desc['language_id'];
            $row['language_code'] = $desc['language_code'];
            if (in_array(substr($desc['language_code'], 0, 2), array('en', 'fr', 'es', 'ja', 'nl', 'no', 'pt', 'tl'))) {
              $row['override'] = substr($desc['language_code'], 0, 2) . '_XX';
            } else {
              $row['override'] = substr($desc['language_code'], 0, 2) . '_' . strtoupper(substr($desc['language_code'], 3, 2) ? substr($desc['language_code'], 3, 2) : substr($desc['language_code'], 0, 2));
            }
            
            $row['currency'] = $data['lang2currency'][$langId];
            $row['name'] = $desc['name'];
            $row['description'] = $desc['description'];
            $row['meta_description'] = $desc['meta_description'];
            
            $this->getCustomNodes($data, $row);
            
            // fix chinese special chars
            if ($this->config->get('univfeed_fix_chinese_chars')) {
              $row['name'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['name']);
              $row['description'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['description']);
              $row['meta_description'] = preg_replace('~[^\P{Cc}\r\n]+~u', '', $row['meta_description']);
            }
            
            $this->setStockStatus($data, $row);
            
            $rows[] = $row;
          }
        }
      } else {
        $this->setStockStatus($data, $row);
        $this->getCustomNodes($data, $row);
        $rows[] = $row;
      }
    }
    
		return $rows;
	}
  
  public function setStockStatus(&$data, &$row) {
    if (!empty($data['stock_label'])) {
      $stockLabels = $data['stock_label'];
      $processWholesale = true;
      
      if (strpos($stockLabels, '[') !== false) {
        $processWholesale = false;
        
        $wholesaleTableCats = explode('[', $stockLabels);
        
        foreach ($wholesaleTableCats as $wholesaleTableCats) {
          if (!isset($wholesaleTableCats[1])) continue;
          
          list($catName, $catTable) = explode(']', $wholesaleTableCats);
          
          if (!empty($row['manufacturer'])) {
            if ($row['manufacturer'] == $catName || $catName == '*') {
              $stockLabels = $catTable;
              $processWholesale = true;
              break;
            }
          }
        }
      }
      
      if ($processWholesale) {
        $stockLabelValues = array_map('trim', explode("\n", $stockLabels));
        
        // if date then do not sort the array
        if (!(is_string($stockLabelValues[0]) && substr($stockLabelValues[0], 10, 1) == ':' && substr($stockLabelValues[0], 4, 1) == '-' && substr($stockLabelValues[0], 7, 1) == '-') && !(is_string($stockLabelValues[0]) && substr($stockLabelValues[0], 0, 1) == '-')) {
          natsort($stockLabelValues);
        }
        
        $lastStatus = explode(':', array_pop($stockLabelValues));
        
        $row['stock_status'] = isset($lastStatus[1]) ? $lastStatus[1] : '';
        
        foreach ($stockLabelValues as $stockLabelRow) {
          $stockLabel = explode(':', $stockLabelRow);
          
          if (is_numeric($stockLabel[0]) && isset($stockLabel[1]) && $row['quantity'] <= $stockLabel[0]) {
            $row['stock_status'] = trim($stockLabel[1]);
            break;
          } else if (is_string($stockLabel[0]) && strlen($stockLabel[0]) == 10 && substr($stockLabel[0], 4, 1) == '-' && substr($stockLabel[0], 7, 1) == '-') {
            $date = new DateTime($stockLabel[0]);
            $now = new DateTime(date('Y-m-d'));
            if ($date < $now) {
              $row['stock_status'] = trim($stockLabel[1]);
              break;
            }
          } else if (is_string($stockLabel[0]) && isset($stockLabel[1]) && $row['stock_status'] == $stockLabel[0]) {
            $row['stock_status'] = trim($stockLabel[1]);
            break;
          }
          
          if (isset($stockLabel[2])) {
            // date available?
          }
        }
        if (!isset($row['stock_status']) && !empty($stockLabelValues)) {
          $stockLabel = explode(':', end($stockLabelValues));
          if (isset($stockLabel[1])) {
            $row['stock_status'] = $stockLabel[1];
          }
        }
      }
    } else if ($row['quantity'] > 0) {
      $row['stock_status'] = 'in stock';
    }
  }
  
  public function getCustomNodes(&$data, &$row) {
    if (!empty($data['cust_nodes'])) {
      // fill extra fields names
      foreach ($data['cust_nodes'] as &$node) {
        if (!$node && !empty($data['cust_names'])) {
          $node = '_'.array_shift($data['cust_names']);
        }
      }
      
      $customVals = array_combine($data['cust_nodes'], $data['cust_values']);
      
      foreach ($customVals as $field => &$val) {
        foreach ($row as $k => $v) {
          // replace tags with no functions
          if (is_array($v)) continue;
          
          $val = str_replace('{'.$k.'}', $v, $val);
          
          // apply functions
          $val = $this->replaceFunctions($k, $v, $val);
        }
        
        // strip unassigned options
        $val = preg_replace('/{option_\w+}/', '', $val);
        $val = preg_replace('/\{\w+\|([^\}]+)\}/', '', $val);
        $val = preg_replace('/\{\w+\}/', '', $val);
        
        // add new field
        if (substr($field, 0, 1) == '_') {
          $row['udfExtraFields'][substr($field, 1)] = $val;
        
        // edit existing field
        } else {
          $row[$field] = $val;
        }
      }
    }
  }
  
  public function getProductDescription($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "' ORDER BY language_id ASC");
    
    $res = array();
    
    foreach ($query->rows as &$row) {
      if (!$row['language_id']) continue;
      
      foreach ($row as $key => $val) {
        if (!in_array($key, array('language_id', 'product_id'))) {
          if ($row['language_id'] == $this->defaultLangId) {
            $res[$key] = $val;
          }
          
          $res[$key.'_'.$this->langIdToCode[$row['language_id']]] = $val;
        }
      }
      $row['language_code'] = $this->langIdToFullCode[$row['language_id']];
      $res['description_array'][$row['language_id']] = $row;
    }
    
		return $res;
	}
  
  private function setSeoDescription(&$row, $store_id, $language_id) {
    $seoDescription = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_product_description d WHERE product_id = '" . (int)$row['product_id'] . "' AND store_id = '".(int) $store_id."' AND language_id = '".(int) $language_id."'")->row;
		
    if (!empty($seoDescription['meta_title'])) {
      $row['meta_title'] = $seoDescription['meta_title'];
    }
    
    if (!empty($seoDescription['meta_description'])) {
      $row['meta_description'] = $seoDescription['meta_description'];
    }
    
    if (!empty($seoDescription['meta_keyword'])) {
      $row['meta_keyword'] = $seoDescription['meta_keyword'];
    }
    
    if (!empty($seoDescription['image_alt'])) {
      $row['image_alt'] = $seoDescription['image_alt'];
    }
    
    if (!empty($seoDescription['image_title'])) {
      $row['image_title'] = $seoDescription['image_title'];
    }
    
    if (!empty($seoDescription['name'])) {
      $row['name'] = $seoDescription['name'];
    }
    
    if (isset($seoDescription['description']) && trim(strip_tags($seoDescription['description']))) {
      $row['description'] = $seoDescription['description'];
    }
    
    if (!empty($seoDescription['seo_h1'])) {
      $row['seo_h1'] = $seoDescription['seo_h1'];
    }
    
    if (!empty($seoDescription['seo_h2'])) {
      $row['seo_h2'] = $seoDescription['seo_h2'];
    }
    
    if (!empty($seoDescription['seo_h3'])) {
      $row['seo_h3'] = $seoDescription['seo_h3'];
    }
    
	}
  
  public function getProductImages($product_id, $full_path, $asArray = false) {
    $query = $this->db->query("SELECT image FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");
    
    if ($asArray) {
      $res = array();
      
      foreach ($query->rows as &$row) {
        if ($row['image']) {
          if (!in_array(substr($row['image'], 0, 6), array('http:/', 'https:'))) {
            if (defined('HTTPS_IMAGE')) {
              $res[] = HTTPS_IMAGE . $row['image'];
            } else {
              $res[] = $this->config->get('config_url').'image/'.$row['image'];
            }
          } else {
            $res[] = $row['image'];
          }
        }
      }
      
      return $res;
    }
    
    $res = '';
    
    foreach ($query->rows as &$row) {
      if ($row['image']) {
        $res .= $res ? '|' : '';
        
        if ($full_path && !in_array(substr($row['image'], 0, 6), array('http:/', 'https:'))) {
          if (defined('HTTPS_IMAGE')) {
            $res .= HTTPS_IMAGE . 'image/'.$row['image'];
          } else {
            $res .= $this->config->get('config_url').'image/'.$row['image'];
          }
        } else {
          $res .= $row['image'];
        }
      }
    }
    
		return $res;
	}
  
  public function getProductAttributes($product_id, $asArray = false) {
		$product_attribute_data = array();

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

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

			$product_attribute_description_query = $this->db->query(
        "SELECT pa.text, pa.language_id, ad.name, agd.name as 'group'
          FROM " . DB_PREFIX . "product_attribute pa
           LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id)
           LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (pa.attribute_id = ad.attribute_id AND pa.language_id = ad.language_id)
           LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (a.attribute_group_id = agd.attribute_group_id AND pa.language_id = agd.language_id)
          WHERE pa.product_id = '" . (int)$product_id . "'
           AND pa.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(
          'group' => $product_attribute_description['group'],
          'attribute' => $product_attribute_description['name'],
          'value' => $product_attribute_description['text'],
        );
			}

			$product_attribute_data[] = $product_attribute_description_data;
		}
    
    if ($asArray) {
      return $product_attribute_data;
    }

    $res = '';
    
    // get formatted string for CSV, take only default language
    foreach ($product_attribute_data as $langs) {
      foreach ($langs as $lang => $item) {
        if ($lang != $this->config->get('config_language_id')) continue;
        
        $res .= $res ? '|' : '';
        $res .= $item['group'] . ':' . $item['attribute'] . ':' . $item['value'];
      }
    }
    
		return $res;
	}
  
  public function getProductOptions($product_id, $asArray = false, $data = array()) {
		$product_option_data = array();
    
    $includeOptions = array();
    
    if (!empty($data['option_size'])) {
      foreach ((array) $data['option_size'] as $includeOptionId) {
        $includeOptions[] = $includeOptionId;
      }
    }
    
    if (!empty($data['option_color'])) {
      foreach ((array) $data['option_color'] as $includeOptionId) {
        $includeOptions[] = $includeOptionId;
      }
    }
    
    $includeOptionQuery = '';
    if (!empty($includeOptions)) {
      $includeOptionQuery = ' AND po.option_id IN ('.implode(',', array_filter($includeOptions)).')';
    }
    
		$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') . "'" . $includeOptionQuery);
    
		foreach ($product_option_query->rows as $product_option) {
      // do not include unselected options
      //if (!in_array($product_option['option_id'], $includeOptions)) continue;
      
			$product_option_value_data = array();

			$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON(pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON(pov.option_value_id = ovd.option_value_id AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "') WHERE pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' ORDER BY ov.sort_order ASC");

      if (!empty($product_option['value'])) {
        $product_option_data[] = array(
          'id'                   => $product_option['option_id'],
          'type'                 => $product_option['type'],
          'name'                 => $product_option['name'],
          'required'             => $product_option['required'],
          'quantity'             => '',
          'subtract'             => '',
          'value'                => $product_option['value'],
          'price'                => '',
          'weight'               => '',
        );
      } else {
        foreach ($product_option_value_query->rows as $product_option_value) {
          $product_option_data[] = array(
            'id'                   => $product_option['option_id'],
            'type'                 => $product_option['type'],
            'name'                 => $product_option['name'],
            'required'             => $product_option['required'],
            'pov_id'               => $product_option_value['product_option_value_id'],
            'quantity'             => $product_option_value['quantity'],
            'subtract'             => $product_option_value['subtract'],
            'value'                => !empty($product_option_value['name']) ? $product_option_value['name'] : $product_option['value'],
            'price'                => $product_option_value['price_prefix'] . $product_option_value['price'],
            'weight'               => $product_option_value['weight_prefix'] . $product_option_value['weight'],
            'image'                => !empty($product_option_value['image']) ? $product_option_value['image'] : '',
            'sku'                  => !empty($product_option_value['sku']) ? $product_option_value['sku'] : (!empty($product_option_value['option_sku']) ? $product_option_value['option_sku'] : ''),
            'ean'                  => !empty($product_option_value['ean']) ? $product_option_value['ean'] : (!empty($product_option_value['option_ean']) ? $product_option_value['option_ean'] : ''),
            'model'                => !empty($product_option_value['model']) ? $product_option_value['model'] : (!empty($product_option_value['option_model']) ? $product_option_value['option_model'] : ''),
          );
          /*
          $product_option_value_data[] = array(
            'option_name'             => $product_option_value['name'],
            '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'],
				'value'                => $product_option['value'],
				'price'                => $product_option_value['price_prefix'] . $product_option_value['price'],
				'required'             => $product_option['required'],
				'product_option_value' => $product_option_value_data,
			);
      */
		}
    
    // type:name:value:price:qty:subtract:weight:required
    
    $res = '';
    
    if ($asArray) {
      return $product_option_data;
    }
    
    // get formatted string for CSV, take only default language
    foreach ($product_option_data as $item) {
      $res .= $res ? '|' : '';
      $res .= $item['type'] . ':' . $item['name'] . ':' . $item['value']. ':' . $item['price'] . ':' . $item['quantity'] . ':' . $item['subtract'] . ':' . $item['weight'] . ':' . $item['required'];
    }
    
		return $res;
	}
  
  public function getProductOptionsFields($product_id, $asArray = false, $data = array()) {
		$this->options = $this->getProductOptions($product_id, true, $data);
    $option_separator = '/';
    
    $data = array(
      'product_option' => ''
    );
    
    if ($asArray) {
      $data['product_option'] = $this->options;
    }
    
    // get formatted string for CSV, take only default language
    foreach ($this->options as $item) {
      if ($asArray) {
        if (!isset($data['option:'.$item['name']])) {
          $data['option:'.$item['name']] = array();
        }
        
        $data['option:'.$item['name']][] = $item['value'];
      } else {
        $data['product_option'] .= $data['product_option'] ? '|' : '';
        $data['product_option'] .= $item['type'] . ':' . $item['name'] . ':' . $item['value']. ':' . $item['price'] . ':' . $item['quantity'] . ':' . $item['subtract'] . ':' . $item['weight'] . ':' . $item['required'];
        
        if (!isset($data['option:'.$item['name']])) {
          $data['option:'.$item['name']] = '';
        }
        
        $data['option:'.$item['name']] .= !empty($data['option:'.$item['name']]) ? $option_separator . $item['value'] : $item['value'];
      }
    }
    
    $data['options'] = &$data['product_option'];
    
		return $data;
	}
  
  public function getProductCoupon($product_id) {
    $query = $this->db->query("SELECT c.code FROM " . DB_PREFIX . "coupon_product cp LEFT JOIN " . DB_PREFIX . "coupon c ON (cp.coupon_id = c.coupon_id) WHERE cp.product_id = '". (int)$product_id."'")->row;
    
    if (!empty($query['code'])) {
      return $query['code'];
    }
    
    $categories = $this->db->query("SELECT category_id FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'")->rows;
    
    if (empty($categories)) {
      return '';
    }
    
    $cats = array();
    foreach ($categories as $category) {
      $cats[] = $category['category_id'];
    }
    
    $categories = implode(',', $cats);
    
    if (empty($categories)) {
      return '';
    }
    
    $query = $this->db->query("SELECT c.code FROM " . DB_PREFIX . "coupon_category cc LEFT JOIN " . DB_PREFIX . "coupon c ON (cc.coupon_id = c.coupon_id) WHERE cc.category_id IN (". $categories . ")")->row;
    
    if (!empty($query['code'])) {
      return $query['code'];
    }
    
    return '';
  }
  
  public function setProductCategoryData($product_id, &$row, $extra_field = false) {
    $extraSelect = '';
    
    if ($extra_field) {
      $extraSelect = $extra_field . ', ';
    }
    
    $categories = $this->db->query("
      SELECT pcd.name as parent_category, cd.name as category, c.category_id, ".$extraSelect." c.parent_id as parent_category_id
      FROM " . DB_PREFIX . "product_to_category p2c
       LEFT JOIN " . DB_PREFIX . "category c ON (p2c.category_id = c.category_id)
       LEFT JOIN " . DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "')
       LEFT JOIN " . DB_PREFIX . "category_description pcd ON (c.parent_id = pcd.category_id AND pcd.language_id = '" . (int)$this->config->get('config_language_id') . "')
      WHERE p2c.product_id = '" . (int)$product_id . "' AND c.status = 1 ORDER BY c.parent_id DESC")->row;
      
      if (empty($categories)) {
        $categories = array(
          'category' => '',
          'category_id' => '',
          'parent_category' => '',
          'parent_category_id' => '',
        );
        
        if ($extra_field) {
          $categories[] = '';
        }
      } else {
        $categories['category_link'] = $this->url->link('product/category', 'path='.$categories['category_id']);
      }
      
      $row = array_merge($row, $categories);
  }
  
  public function getProductCategories($product_id, $config = array(), $asArray = false) {
		$res = array();
    
		$categories = $this->db->query("
      SELECT pcd.name as parent_name, cd.name, c.category_id, c.parent_id
      FROM " . DB_PREFIX . "product_to_category p2c
       LEFT JOIN " . DB_PREFIX . "category c ON (p2c.category_id = c.category_id)
       LEFT JOIN " . DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "')
       LEFT JOIN " . DB_PREFIX . "category_description pcd ON (c.parent_id = pcd.category_id AND pcd.language_id = '" . (int)$this->config->get('config_language_id') . "')
      WHERE p2c.product_id = '" . (int)$product_id . "' AND c.status = 1")->rows;
      
		foreach ($categories as $key => $category) {
      $res[$key] = '';
      
			if (!$category) continue;
      
      if (!empty($category['name'])) {
        $res[$key] = $category['name'];
      }
			
			while (!empty($category['parent_id'])) {
        if ($category['category_id'] === $category['parent_id']) {
          break;
        }
        
				$res[$key] = $category['parent_name'] . '>' . $res[$key];
				$category = $this->db->query("
          SELECT pcd.name as parent_name, c.category_id, c.parent_id FROM " . DB_PREFIX . "category c
           LEFT JOIN " . DB_PREFIX . "category_description pcd ON (c.parent_id = pcd.category_id AND pcd.language_id = '" . (int)$this->config->get('config_language_id') . "')
          WHERE c.category_id = '" . $category['parent_id']. "' AND c.status = 1")->row;
			}
		}
    
    if ($asArray) {
      if (!empty($config['one_category'])) {
        $res = (array) array_shift($res);
      }
      
      return $res;
    }
    
		if (!count($res)) return '';
    
    if (!empty($config['one_category'])) {
      $returnCat = array_shift($res);
      
      foreach ($res as $cat) {
        // return largest category
        if (true) {
          if (count(explode('>', $cat)) > count(explode('>', $returnCat))) {
            $returnCat = $cat;
          }
        } else {
          if (count(explode('<', $cat)) > count(explode('>', $returnCat))) {
            $returnCat = $cat;
          }
        }
      }
      
      $res = $returnCat;
    } else {
      $res = implode('|', $res);
    }
    
    return $res;
	}
  
  public function getProductFilters($product_id, $asArray = false) {
		$query = $this->db->query("SELECT fd.name as name, fgd.name as group_name FROM " . DB_PREFIX . "product_filter pf
    LEFT JOIN " . DB_PREFIX . "filter f ON (pf.filter_id = f.filter_id)
    LEFT JOIN " . DB_PREFIX . "filter_description fd ON (pf.filter_id = fd.filter_id)
    LEFT JOIN " . DB_PREFIX . "filter_group fg ON (f.filter_group_id = fg.filter_group_id)
    LEFT JOIN " . DB_PREFIX . "filter_group_description fgd ON (f.filter_group_id = fgd.filter_group_id)
    WHERE pf.product_id = '" . (int)$product_id . "'
    AND fd.language_id = '" . (int)$this->config->get('config_language_id') . "'
    AND fgd.language_id = '" . (int)$this->config->get('config_language_id') . "'
    ORDER BY f.sort_order, fg.sort_order, f.filter_id");


    if ($asArray) {
      return $query->rows;
    }
    
    // get formatted string for CSV, take only default language
    $res = '';
    
    foreach ($query->rows as $item) {
      $res .= $res ? '|' : '';
      $res .= $item['group_name'] . ':' . $item['name'];
    }
    
		return $res;
	}
  
  public function getProductDiscounts($product_id, $special_price_group) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount pd WHERE product_id = '" . (int)$product_id . "' AND pd.quantity > '0' AND ((pd.date_start = '0000-00-00' OR pd.date_start < NOW()) AND (pd.date_end = '0000-00-00' OR pd.date_end > NOW())) ORDER BY pd.priority ASC, pd.price ASC");

    $res = array('discount' => '');
    
    // get formatted string for CSV, take only default language
    foreach ($query->rows as $item) {
      if ($item['customer_group_id'] == $special_price_group) {
        $res['discount'] = $item['price'];
      }
      
      $res['discount_'.$item['customer_group_id']] = $item['price'];
      
      //return $item['price'];
      // $res .= $res ? '|' : '';
      // $res .= $item['customer_group_id'] . ':' . $item['quantity'] . ':' . $item['priority'] . ':' . $item['price'] . ':' . $item['date_start'] . ':' . $item['date_end'];
    }
    
		return $res;
	}
  
  public function getProductSpecials($product_id, $special_price_group) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special ps WHERE product_id = '" . (int)$product_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC");

    $res = array('special' => '');
    
    foreach ($query->rows as $item) {
      if ($item['customer_group_id'] == $special_price_group && empty($res['special'])) {
        $res['special'] = $item['price'];
      }
      
      if (empty($res['special_'.$item['customer_group_id']])) {
        $res['special_'.$item['customer_group_id']] = $item['price'];
      }
      
      //return $item['price'];
      // $res .= $res ? '|' : '';
      // $res .= $item['customer_group_id'] . ':' . $item['quantity'] . ':' . $item['priority'] . ':' . $item['price'] . ':' . $item['date_start'] . ':' . $item['date_end'];
    }
    
		return $res;
	}
  
  public function getProductSpecial($product_id, $special_price_group) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' ORDER BY priority, price LIMIT 1");
    
    foreach ($query->rows as $item) {
      return $item['price'];
    }
    
	}
  
  public function getProductSpecials_($product_id, $special_price_group) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' ORDER BY priority, price");

    $res = '';
    
    // get formatted string for CSV, take only default language
    foreach ($query->rows as $item) {
      $res .= $res ? '|' : '';
      $res .= $item['customer_group_id'] . ':' . $item['priority'] . ':' . $item['price'] . ':' . $item['date_start'] . ':' . $item['date_end'];
    }
    
		return $res;
	}
  
  public function getTotalItems($data = array()) {
    return $this->getItems($data, true);
  }
  
  public function replaceFunctions($k, $v, $val) {
    if (preg_match_all('/\{'.preg_quote($k, '/').'\|([^\}]+)\}/', $val, $matches)) {
      foreach ($matches[1] as $i => $fn) {
        $functions = explode('|', $fn);
        
        foreach ($functions as $fn) {
          $params = explode(':', $fn);
          $fn = array_shift($params);
          
          if ($fn == 'replace') {
            $params[0] = str_replace(array('[colon]', '[tube]'), array(':', '|'), $params[0]);
            $params[1] = str_replace(array('[colon]', '[tube]'), array(':', '|'), $params[1]);
            
            $v = str_replace($params[0], $params[1], $v);
          } else if ($fn == 'float') {
              $dotPos = strrpos($v, '.');
              $commaPos = strrpos($v, ',');
              $sep = (($dotPos > $commaPos) && $dotPos) ? $dotPos :
                  ((($commaPos > $dotPos) && $commaPos) ? $commaPos : false);
                  
              if (!$sep) {
                $v = floatval(preg_replace("/[^0-9]/", "", $v));
              } else {
                $v = floatval(
                    preg_replace("/[^0-9]/", "", substr($v, 0, $sep)) . '.' .
                    preg_replace("/[^0-9]/", "", substr($v, $sep+1, strlen($v)))
                );
              }
          } else if ($fn == 'round') {
            $v = round($v, isset($params[0]) ? (int)$params[0] : 0);
          } else if ($fn == 'multiply') {
            $v = floatval($v) * $params[0];
          } else if ($fn == 'divide') {
            $v = floatval($v) / $params[0];
          } else if ($fn == 'add') {
            $v = floatval($v) + $params[0];
          } else if ($fn == 'subtract') {
            $v = floatval($v) - $params[0];
          } else if ($fn == 'strip_html') {
            $v = strip_tags(html_entity_decode($v, ENT_QUOTES, 'UTF-8'));
          } else if ($fn == 'truncate') {
            $v = substr($v, 0, $params[0]);
          }
        }
        
        $val = str_replace($matches[0][$i], $v, $val);
      }
    }
    
    return $val;
  }
  
  public function replaceCustomNodes(&$row, $val) {
    // replace direct tags
    if (preg_match_all('/\{(\w+)\}/', $val, $matches)) {
      foreach ($matches[1] as $tag) {
        if (isset($row[$tag])) {
          $val = str_replace('{'.$tag.'}', $row[$tag], $val);
        }
      }
    }
    
    // replace functions
    if (preg_match_all('/\{(\w+\|[^\}]+)\}/', $val, $matches)) {
      foreach ($matches[1] as $i => $fn) {
        $functions = explode('|', $fn);
        $tag = array_shift($functions);
        
        if (isset($row[$tag])) {
          $v = $row[$tag];
        }
        
        foreach ($functions as $fn) {
          $params = explode(':', $fn);
          $fn = array_shift($params);
          
          if ($fn == 'replace') {
            $v = str_replace($params[0], $params[1], $v);
          } else if ($fn == 'float') {
              $dotPos = strrpos($v, '.');
              $commaPos = strrpos($v, ',');
              $sep = (($dotPos > $commaPos) && $dotPos) ? $dotPos :
                  ((($commaPos > $dotPos) && $commaPos) ? $commaPos : false);
                  
              if (!$sep) {
                $v = floatval(preg_replace("/[^0-9]/", "", $v));
              } else {
                $v = floatval(
                    preg_replace("/[^0-9]/", "", substr($v, 0, $sep)) . '.' .
                    preg_replace("/[^0-9]/", "", substr($v, $sep+1, strlen($v)))
                );
              }
          } else if ($fn == 'round') {
            $v = round($v, isset($params[0]) ? (int)$params[0] : 0);
          } else if ($fn == 'multiply') {
            $v = floatval($v) * $params[0];
          } else if ($fn == 'divide') {
            $v = floatval($v) / $params[0];
          } else if ($fn == 'add') {
            $v = floatval($v) + $params[0];
          } else if ($fn == 'subtract') {
            $v = floatval($v) - $params[0];
          } else if ($fn == 'strip_html') {
            $v = strip_tags(html_entity_decode($v, ENT_QUOTES, 'UTF-8'));
          } else if ($fn == 'truncate') {
            $v = substr($v, 0, $params[0]);
          }
        }
        
        $val = str_replace($matches[0][$i], $v, $val);
      }
    }
    
    return $val;
  }
  
  private function sortBySubcatNumber($a, $b) {
    if (substr_count($a, '>') == substr_count($b, '>')) {
      return 0;
    }
    return (substr_count($a, '>') < substr_count($b, '>')) ? -1 : 1;
  }
}